-- Profiles table: one row per auth.users user, created automatically by a
-- trigger that reads the sign-up metadata. Users can view/update only their
-- own row; admins use the service role to bypass RLS.

create table if not exists public.profiles (
  id uuid primary key references auth.users(id) on delete cascade,
  display_name text not null,
  age int not null check (age >= 13 and age <= 120),
  gender text not null check (gender in ('male','female','non-binary','prefer-not-to-say')),
  country text not null,
  state text not null,
  bio text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists profiles_created_at_idx on public.profiles (created_at desc);
create index if not exists profiles_country_idx on public.profiles (country);

alter table public.profiles enable row level security;

drop policy if exists "profiles_select_own" on public.profiles;
drop policy if exists "profiles_update_own" on public.profiles;
drop policy if exists "profiles_insert_own" on public.profiles;

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);

-- Auto-create a profile row when a new auth user is created. Pulls values
-- from raw_user_meta_data so the sign-up form can set them atomically.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, display_name, age, gender, country, state)
  values (
    new.id,
    coalesce(nullif(new.raw_user_meta_data ->> 'display_name', ''), split_part(new.email, '@', 1)),
    coalesce(nullif(new.raw_user_meta_data ->> 'age', '')::int, 18),
    coalesce(nullif(new.raw_user_meta_data ->> 'gender', ''), 'prefer-not-to-say'),
    coalesce(nullif(new.raw_user_meta_data ->> 'country', ''), 'Unknown'),
    coalesce(nullif(new.raw_user_meta_data ->> 'state', ''), 'Unknown')
  )
  on conflict (id) do nothing;
  return new;
end;
$$;

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.handle_new_user();

-- Keep updated_at fresh
create or replace function public.handle_updated_at()
returns trigger
language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

drop trigger if exists profiles_updated_at on public.profiles;
create trigger profiles_updated_at
  before update on public.profiles
  for each row
  execute function public.handle_updated_at();

-- Call sessions log for admin analytics. A row is inserted when two peers
-- are matched together (from match_user / check_match) and updated with
-- ended_at + duration_seconds when either side leaves.
create table if not exists public.call_sessions (
  id uuid primary key default gen_random_uuid(),
  room_id uuid not null,
  user_a uuid references auth.users(id) on delete set null,
  user_b uuid references auth.users(id) on delete set null,
  started_at timestamptz not null default now(),
  ended_at timestamptz,
  duration_seconds int
);

create index if not exists call_sessions_started_at_idx on public.call_sessions (started_at desc);
create index if not exists call_sessions_room_idx on public.call_sessions (room_id);