-- Lumbridge Bench — Supabase schema.
--
-- Supabase holds ONLY the mutable parts: the manual suggestion inbox and accounts.
-- Measurements are not here. They live in results/*.json in git, so the
-- leaderboard cannot drift from what was actually recorded and there is no
-- migration path to get wrong. See docs/DECISIONS.md.

create schema if not exists bench;

-- ---------------------------------------------------------------------------
-- accounts: invite-gated signup
-- ---------------------------------------------------------------------------

create table if not exists bench.invite_codes (
  code        text primary key,
  max_uses    int  not null default 1000,
  uses        int  not null default 0,
  active      bool not null default true,
  created_at  timestamptz not null default now()
);

create table if not exists bench.profiles (
  id          uuid primary key references auth.users(id) on delete cascade,
  handle      text unique,
  invite_code text references bench.invite_codes(code),
  created_at  timestamptz not null default now()
);

-- ---------------------------------------------------------------------------
-- submissions: compatibility table used as a manual suggestion inbox
-- ---------------------------------------------------------------------------

create type bench.submission_status as enum (
  'queued', 'running', 'complete', 'rejected', 'failed'
);

create table if not exists bench.submissions (
  id           uuid primary key default gen_random_uuid(),
  hf_ref       text not null,
  params_b     numeric,
  notes        text,
  contact      text,
  status       bench.submission_status not null default 'queued',
  anonymous    bool not null default true,
  submitted_by uuid references auth.users(id) on delete set null,
  -- run_id links to the results/*.json produced for this submission. Not a
  -- foreign key: results live in git, not in this database, by design.
  run_id       text,
  reject_reason text,
  created_at   timestamptz not null default now(),
  updated_at   timestamptz not null default now()
);

-- One pending suggestion per model. The legacy `queued` state means awaiting
-- owner review; it does not authorize downloads or execution.
create unique index if not exists submissions_pending_ref
  on bench.submissions (hf_ref)
  where status in ('queued', 'running');

create index if not exists submissions_status_created
  on bench.submissions (status, created_at desc);

-- ---------------------------------------------------------------------------
-- RLS
-- ---------------------------------------------------------------------------

alter table bench.submissions  enable row level security;
alter table bench.profiles     enable row level security;
alter table bench.invite_codes enable row level security;

-- Suggestions and contact details are private. There is intentionally no public
-- select policy on this table; selected aggregate Bench results live in git.

create policy submissions_anon_insert on bench.submissions
  for insert with check (
    status = 'queued'
    and (submitted_by is null or submitted_by = auth.uid())
  );

create policy profiles_self_read on bench.profiles
  for select using (id = auth.uid());

create policy profiles_self_write on bench.profiles
  for update using (id = auth.uid());

-- Invite codes are never client-readable; validation happens server-side with
-- the service role. A readable invite table is an open signup form.
create policy invite_codes_no_read on bench.invite_codes
  for select using (false);

-- ---------------------------------------------------------------------------
-- seed
-- ---------------------------------------------------------------------------

insert into bench.invite_codes (code, max_uses)
values ('115karti', 1000)
on conflict (code) do nothing;