-- Learning flow and assessment storage schema
-- Run this script in Supabase SQL Editor.

create table if not exists learning_progress (
  user_id uuid primary key references auth.users(id) on delete cascade,
  current_step text not null default 'registered'
    check (current_step in ('registered', 'pre_test_done', 'training_in_progress', 'post_test_done', 'completed')),
  pre_test_completed_at timestamptz,
  training_started_at timestamptz,
  training_completed_at timestamptz,
  post_test_completed_at timestamptz,
  updated_at timestamptz not null default timezone('utc', now())
);

create table if not exists assessment_attempts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  phase text not null check (phase in ('pre', 'post')),
  score integer not null default 0,
  total_questions integer not null default 0,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists assessment_answers (
  id uuid primary key default gen_random_uuid(),
  attempt_id uuid not null references assessment_attempts(id) on delete cascade,
  question_id text not null,
  answer_value text not null,
  is_correct boolean not null default false,
  points integer not null default 0,
  created_at timestamptz not null default timezone('utc', now())
);

alter table learning_progress enable row level security;
alter table assessment_attempts enable row level security;
alter table assessment_answers enable row level security;

drop policy if exists "Users can read own learning progress" on learning_progress;
create policy "Users can read own learning progress" on learning_progress
  for select using (auth.uid() = user_id);

drop policy if exists "Users can insert own learning progress" on learning_progress;
create policy "Users can insert own learning progress" on learning_progress
  for insert with check (auth.uid() = user_id);

drop policy if exists "Users can update own learning progress" on learning_progress;
create policy "Users can update own learning progress" on learning_progress
  for update using (auth.uid() = user_id);

drop policy if exists "Users can read own attempts" on assessment_attempts;
create policy "Users can read own attempts" on assessment_attempts
  for select using (auth.uid() = user_id);

drop policy if exists "Users can insert own attempts" on assessment_attempts;
create policy "Users can insert own attempts" on assessment_attempts
  for insert with check (auth.uid() = user_id);

drop policy if exists "Users can read own answers" on assessment_answers;
create policy "Users can read own answers" on assessment_answers
  for select using (
    exists (
      select 1
      from assessment_attempts a
      where a.id = assessment_answers.attempt_id
        and a.user_id = auth.uid()
    )
  );

drop policy if exists "Users can insert own answers" on assessment_answers;
create policy "Users can insert own answers" on assessment_answers
  for insert with check (
    exists (
      select 1
      from assessment_attempts a
      where a.id = assessment_answers.attempt_id
        and a.user_id = auth.uid()
    )
  );