create extension if not exists "pgcrypto";

-- ============================================================================
-- PROFILES TABLE
-- Mirrors auth.users and stores additional profile info (username, avatar, role)
-- ============================================================================
create table if not exists public.profiles (
  id uuid primary key references auth.users on delete cascade,
  email text,
  username text,
  avatar_url text,
  role text default 'user',
  created_at timestamptz default now(),
  updated_at timestamptz default now()
);

-- ============================================================================
-- LICENSES TABLE
-- Stores license keys tied to a profile
-- ============================================================================
create table if not exists public.licenses (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references public.profiles(id) on delete cascade,
  product text not null,
  key text unique not null,
  active boolean default true,
  created_at timestamptz default now(),
  expires_at timestamptz,
  
  constraint license_product_not_empty check (product != '')
);

create index if not exists idx_licenses_user_id on public.licenses(user_id);
create index if not exists idx_licenses_key on public.licenses(key);

-- ============================================================================
-- ROW LEVEL SECURITY (RLS) POLICIES
-- ============================================================================

-- Profiles: Users can only read/write their own profile
alter table public.profiles enable row level security;

create policy "profiles_select_own" on public.profiles
  for select
  using (auth.uid() = id);

create policy "profiles_insert_own" on public.profiles
  for insert
  with check (auth.uid() = id);

create policy "profiles_update_own" on public.profiles
  for update
  using (auth.uid() = id)
  with check (auth.uid() = id);

create policy "profiles_delete_own" on public.profiles
  for delete
  using (auth.uid() = id);

-- Licenses: Users can only access their own licenses
alter table public.licenses enable row level security;

create policy "licenses_select_own" on public.licenses
  for select
  using (auth.uid() = user_id);

create policy "licenses_insert_own" on public.licenses
  for insert
  with check (auth.uid() = user_id);

create policy "licenses_update_own" on public.licenses
  for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

create policy "licenses_delete_own" on public.licenses
  for delete
  using (auth.uid() = user_id);

-- ============================================================================
-- HELPER FUNCTION: Safely extract Discord user info from OAuth metadata
-- Called by trigger to populate profile on first auth
-- ============================================================================
create or replace function public.extract_discord_username()
returns trigger as $$
declare
  username_value text;
  avatar_hash text;
  provider_user_id text;
  avatar_url_value text;
begin
  -- Discord OAuth stores data in raw_user_meta_data->user
  -- Structure: { "user": { "id": "...", "username": "...", "avatar": "..." } }
  
  -- Extract username from nested user object
  username_value := (new.raw_user_meta_data -> 'user' ->> 'username');
  if username_value is null then
    username_value := new.email; -- Fallback to email if no username
  end if;
  
  -- Extract avatar hash and construct CDN URL
  avatar_hash := (new.raw_user_meta_data -> 'user' ->> 'avatar');
  provider_user_id := (new.raw_user_meta_data -> 'user' ->> 'id');
  
  if avatar_hash is not null and provider_user_id is not null then
    avatar_url_value := 'https://cdn.discordapp.com/avatars/' || provider_user_id || '/' || avatar_hash;
  else
    avatar_url_value := null;
  end if;
  
  -- Insert into profiles (use insert...on conflict to handle existing rows)
  insert into public.profiles (id, email, username, avatar_url, role, created_at, updated_at)
  values (
    new.id,
    new.email,
    username_value,
    avatar_url_value,
    'user',
    now(),
    now()
  )
  on conflict (id) do update
  set
    email = new.email,
    username = coalesce(excluded.username, new.email),
    avatar_url = avatar_url_value,
    updated_at = now();
  
  return new;
end;
$$ language plpgsql security definer;

-- ============================================================================
-- TRIGGER: Create profile when user signs up via Discord OAuth
-- Fires after a new user is created in auth.users
-- ============================================================================
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row
  execute function public.extract_discord_username();