-- =====================================================================
--  QUIZ SPORT & NANTES — schéma Supabase (PostgreSQL)
--  À exécuter dans : Supabase Dashboard > SQL Editor > New query
--  Le script est idempotent : il peut être rejoué sans risque.
-- =====================================================================

-- ---------------------------------------------------------------------
-- 1. TABLES
-- ---------------------------------------------------------------------

create table if not exists public.questions (
  id            bigint generated always as identity primary key,
  question_text text        not null,
  position      integer     not null default 0,    -- ordre d'affichage
  active        boolean     not null default true, -- statut actif / inactif
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);

create table if not exists public.answers (
  id          bigint generated always as identity primary key,
  question_id bigint  not null references public.questions (id) on delete cascade,
  answer_text text    not null,
  is_correct  boolean not null default false,
  position    integer not null default 0
);

create table if not exists public.participants (
  id             uuid        primary key default gen_random_uuid(),
  first_name     text        not null,
  last_name      text        not null,
  email          text,
  accepted_rules boolean     not null default false,
  ip             text,
  user_agent     text,
  created_at     timestamptz not null default now()
);

create table if not exists public.quiz_attempts (
  id              uuid        primary key default gen_random_uuid(),
  participant_id  uuid        not null references public.participants (id) on delete cascade,
  -- jeton secret remis au navigateur : il ne permet QUE de jouer sa propre partie
  token           text        not null unique,
  -- instantané des questions tirées : une modification en cours de partie ne casse rien
  question_ids    bigint[]    not null,
  total_questions integer     not null,
  current_index   integer     not null default 0,
  correct_count   integer,
  score           integer,
  start_time      timestamptz not null default now(),  -- horloge SERVEUR
  end_time        timestamptz,                          -- posé par trigger
  duration_ms     integer,                              -- calculé par trigger
  status          text        not null default 'in_progress'
                  check (status in ('in_progress', 'finished', 'abandoned')),
  created_at      timestamptz not null default now()
);

create table if not exists public.quiz_attempt_answers (
  id          bigint generated always as identity primary key,
  attempt_id  uuid    not null references public.quiz_attempts (id) on delete cascade,
  question_id bigint  not null references public.questions (id) on delete cascade,
  answer_id   bigint  references public.answers (id) on delete set null,
  is_correct  boolean not null default false,
  elapsed_ms  integer not null default 0,
  answered_at timestamptz not null default now(),
  -- une question ne peut être répondue qu'une seule fois (anti double-soumission)
  constraint quiz_attempt_answers_unique unique (attempt_id, question_id)
);

-- Administrateurs : liste blanche adossée à Supabase Auth.
-- Être authentifié ne suffit pas, il faut être présent dans cette table.
create table if not exists public.admins (
  user_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.settings (
  key        text primary key,
  value      text,
  updated_at timestamptz not null default now()
);

-- ---------------------------------------------------------------------
-- 2. INDEX (classement instantané)
-- ---------------------------------------------------------------------

create index if not exists questions_order_idx
  on public.questions (active, position, id);

create index if not exists answers_question_idx
  on public.answers (question_id, position, id);

-- Le classement officiel : score DESC, temps ASC, heure de fin ASC
create index if not exists quiz_attempts_ranking_idx
  on public.quiz_attempts (score desc, duration_ms asc, end_time asc)
  where status = 'finished';

create index if not exists quiz_attempts_participant_idx
  on public.quiz_attempts (participant_id);

create index if not exists quiz_attempts_token_idx
  on public.quiz_attempts (token);

create index if not exists quiz_attempt_answers_attempt_idx
  on public.quiz_attempt_answers (attempt_id);

create index if not exists participants_name_idx
  on public.participants (lower(last_name), lower(first_name));

-- Au plus UNE bonne réponse par question.
create unique index if not exists answers_single_correct_idx
  on public.answers (question_id)
  where is_correct;

-- ---------------------------------------------------------------------
-- 3. TRIGGERS — le score et le temps sont calculés PAR LA BASE
--    Le navigateur ne peut donc ni les envoyer, ni les modifier.
-- ---------------------------------------------------------------------

-- 3.a Horodatage serveur de chaque réponse + temps écoulé depuis le départ.
create or replace function public.stamp_attempt_answer()
returns trigger
language plpgsql
as $$
declare
  attempt_start timestamptz;
begin
  select qa.start_time into attempt_start
    from public.quiz_attempts qa
   where qa.id = new.attempt_id;

  new.answered_at := now();
  new.elapsed_ms  := greatest(0, (extract(epoch from (now() - attempt_start)) * 1000)::integer);
  return new;
end;
$$;

drop trigger if exists trg_stamp_attempt_answer on public.quiz_attempt_answers;
create trigger trg_stamp_attempt_answer
  before insert or update on public.quiz_attempt_answers
  for each row execute function public.stamp_attempt_answer();

-- 3.b Clôture d'une participation : end_time, duration_ms et score sont
--     recalculés ici, à partir des réponses réellement enregistrées.
create or replace function public.finalize_quiz_attempt()
returns trigger
language plpgsql
as $$
declare
  correct integer;
begin
  if new.status = 'finished' and old.status is distinct from 'finished' then
    select count(*)::integer
      into correct
      from public.quiz_attempt_answers qaa
     where qaa.attempt_id = old.id
       and qaa.is_correct;

    new.end_time      := now();
    new.duration_ms   := greatest(0, (extract(epoch from (now() - old.start_time)) * 1000)::integer);
    new.correct_count := correct;
    new.score         := correct;
  end if;

  -- Le temps de départ ne peut jamais être réécrit après coup.
  new.start_time := old.start_time;
  return new;
end;
$$;

drop trigger if exists trg_finalize_quiz_attempt on public.quiz_attempts;
create trigger trg_finalize_quiz_attempt
  before update on public.quiz_attempts
  for each row execute function public.finalize_quiz_attempt();

-- 3.c updated_at automatique sur les questions.
create or replace function public.touch_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at := now();
  return new;
end;
$$;

drop trigger if exists trg_questions_updated_at on public.questions;
create trigger trg_questions_updated_at
  before update on public.questions
  for each row execute function public.touch_updated_at();

-- ---------------------------------------------------------------------
-- 4. VUE CLASSEMENT
--    Tri officiel : score DESC, durée ASC, heure de fin ASC
-- ---------------------------------------------------------------------

create or replace view public.leaderboard as
select
  row_number() over (
    order by qa.score desc, qa.duration_ms asc, qa.end_time asc
  ) as rank,
  qa.id           as attempt_id,
  p.id            as participant_id,
  p.last_name,
  p.first_name,
  p.email,
  qa.score,
  qa.correct_count,
  qa.total_questions,
  qa.duration_ms,
  qa.start_time,
  qa.end_time
from public.quiz_attempts qa
join public.participants p on p.id = qa.participant_id
where qa.status = 'finished';

-- ---------------------------------------------------------------------
-- 5. ROW LEVEL SECURITY
--    Principe : RLS activée partout, AUCUNE policy pour le rôle `anon`.
--    -> un navigateur muni de la clé publique ne peut rien lire ni écrire.
--    -> les participants passent exclusivement par l'API Node (service_role).
--    -> les administrateurs authentifiés (Supabase Auth + table admins)
--       peuvent lire les résultats et gérer les questions.
-- ---------------------------------------------------------------------

alter table public.questions             enable row level security;
alter table public.answers               enable row level security;
alter table public.participants          enable row level security;
alter table public.quiz_attempts         enable row level security;
alter table public.quiz_attempt_answers  enable row level security;
alter table public.admins                enable row level security;
alter table public.settings              enable row level security;

-- Force la RLS même pour le propriétaire des tables (défense en profondeur).
alter table public.answers              force row level security;
alter table public.quiz_attempts        force row level security;
alter table public.quiz_attempt_answers force row level security;

-- Helper : l'utilisateur connecté est-il un administrateur déclaré ?
create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as $$
  select exists (
    select 1 from public.admins a where a.user_id = auth.uid()
  );
$$;

revoke all on function public.is_admin() from public;
grant execute on function public.is_admin() to authenticated;

-- 5.a Questions & réponses : gérées uniquement par les administrateurs.
--     (les bonnes réponses ne sortent JAMAIS vers un participant)
drop policy if exists "admins_manage_questions" on public.questions;
create policy "admins_manage_questions" on public.questions
  for all to authenticated
  using (public.is_admin())
  with check (public.is_admin());

drop policy if exists "admins_manage_answers" on public.answers;
create policy "admins_manage_answers" on public.answers
  for all to authenticated
  using (public.is_admin())
  with check (public.is_admin());

-- 5.b Participants & participations : lecture / suppression admin uniquement.
drop policy if exists "admins_read_participants" on public.participants;
create policy "admins_read_participants" on public.participants
  for select to authenticated
  using (public.is_admin());

drop policy if exists "admins_delete_participants" on public.participants;
create policy "admins_delete_participants" on public.participants
  for delete to authenticated
  using (public.is_admin());

drop policy if exists "admins_read_attempts" on public.quiz_attempts;
create policy "admins_read_attempts" on public.quiz_attempts
  for select to authenticated
  using (public.is_admin());

drop policy if exists "admins_delete_attempts" on public.quiz_attempts;
create policy "admins_delete_attempts" on public.quiz_attempts
  for delete to authenticated
  using (public.is_admin());

drop policy if exists "admins_read_attempt_answers" on public.quiz_attempt_answers;
create policy "admins_read_attempt_answers" on public.quiz_attempt_answers
  for select to authenticated
  using (public.is_admin());

-- 5.c Réglages : lecture / écriture admin.
drop policy if exists "admins_manage_settings" on public.settings;
create policy "admins_manage_settings" on public.settings
  for all to authenticated
  using (public.is_admin())
  with check (public.is_admin());

-- 5.d Table admins : un admin voit la liste, personne ne s'y ajoute seul.
drop policy if exists "admins_read_admins" on public.admins;
create policy "admins_read_admins" on public.admins
  for select to authenticated
  using (public.is_admin());

-- 5.e La vue classement suit les droits de son propriétaire : on la restreint.
revoke all on public.leaderboard from anon, authenticated;
grant select on public.leaderboard to authenticated;

-- 5.f Aucun droit résiduel pour le rôle anonyme.
revoke all on public.questions, public.answers, public.participants,
              public.quiz_attempts, public.quiz_attempt_answers,
              public.settings, public.admins
  from anon;

-- ---------------------------------------------------------------------
-- 6. RÉGLAGES PAR DÉFAUT
-- ---------------------------------------------------------------------

insert into public.settings (key, value) values
  ('quiz_name',        'QUIZ SPORT & NANTES'),
  ('quiz_subtitle',    'Testez vos connaissances. Soyez rapide. Soyez le meilleur.'),
  ('intro_text',       'Répondez au maximum de questions correctement en un minimum de temps.'),
  ('outro_text',       'Le classement final sera communiqué à la fin du jeu.'),
  ('rules_text',       'Une seule participation par personne. Le gagnant est celui qui totalise le plus de bonnes réponses ; en cas d''égalité, le temps le plus court départage. Les données collectées servent uniquement à établir le classement du jeu.'),
  ('prize_label',      'Un bon d''achat de 50 € chez Decathlon'),
  ('questions_count',  '10'),
  ('quiz_status',      'open'),
  ('allow_replay',     '0')
on conflict (key) do nothing;