schema.sql

-- Run in the Supabase SQL editor (free tier).

create table if not exists public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  email text,
  created_at timestamptz not null default now()
);

create table if not exists public.lesson_progress (
  user_id uuid not null references auth.users (id) on delete cascade,
  lesson_key text not null,
  status text not null check (status in ('not_started', 'in_progress', 'completed')),
  score numeric not null default 0,
  completed_at timestamptz,
  updated_at timestamptz not null default now(),
  primary key (user_id, lesson_key)
);

create table if not exists public.attempt_history (
  id uuid primary key,
  user_id uuid not null references auth.users (id) on delete cascade,
  lesson_key text not null,
  exercise_id text not null,
  correct boolean not null,
  created_at timestamptz not null default now()
);

alter table public.profiles enable row level security;
alter table public.lesson_progress enable row level security;
alter table public.attempt_history 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);

create policy "lesson_progress_select_own" on public.lesson_progress
  for select using (auth.uid() = user_id);
create policy "lesson_progress_insert_own" on public.lesson_progress
  for insert with check (auth.uid() = user_id);
create policy "lesson_progress_update_own" on public.lesson_progress
  for update using (auth.uid() = user_id);
create policy "lesson_progress_delete_own" on public.lesson_progress
  for delete using (auth.uid() = user_id);

create policy "attempt_history_select_own" on public.attempt_history
  for select using (auth.uid() = user_id);
create policy "attempt_history_insert_own" on public.attempt_history
  for insert with check (auth.uid() = user_id);
create policy "attempt_history_update_own" on public.attempt_history
  for update using (auth.uid() = user_id);
create policy "attempt_history_delete_own" on public.attempt_history
  for delete using (auth.uid() = user_id);

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, email)
  values (new.id, new.email)
  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();