0001_init.sql

-- MathWiz initial schema.
-- Creates a `public.stats` row per authenticated user with RLS restricting
-- each user to their own row, and an auth-user trigger that auto-inserts a
-- stats row on sign-up.

create table if not exists public.stats (
  user_id        uuid primary key references auth.users(id) on delete cascade,
  display_name   text,
  high_score     int  not null default 0,
  total_xp       int  not null default 0,
  correct_streak int  not null default 0,
  updated_at     timestamptz not null default now()
);

alter table public.stats enable row level security;

drop policy if exists "stats_own_row" on public.stats;
create policy "stats_own_row"
  on public.stats
  for all
  using  (auth.uid() = user_id)
  with check (auth.uid() = user_id);

-- Trigger: create a stats row whenever a new auth.users row is inserted.
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.stats (user_id, display_name)
  values (
    new.id,
    coalesce(
      new.raw_user_meta_data->>'display_name',
      split_part(new.email, '@', 1)
    )
  )
  on conflict (user_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();