schema.sql

-- ============================================================
--  Supabase / PostgreSQL DDL Schema
-- ============================================================
--  Run this in the Supabase SQL Editor.
--  It creates the four core tables, indexes, the leaderboard
--  view, Row Level Security policies, and seed sample data.
-- ============================================================

-- Required for gen_random_uuid()
create extension if not exists "pgcrypto";

-- ------------------------------------------------------------
-- Drop (idempotent re-runs during development)
-- ------------------------------------------------------------
drop view   if exists public.leaderboard cascade;
drop table  if exists public.submissions cascade;
drop table  if exists public.test_cases  cascade;
drop table  if exists public.problems    cascade;
drop table  if exists public.users       cascade;

-- ------------------------------------------------------------
-- users
-- ------------------------------------------------------------
create table public.users (
  id          uuid        primary key default gen_random_uuid(),
  username    text        not null unique,
  created_at  timestamptz not null default now()
);

-- ------------------------------------------------------------
-- problems
-- ------------------------------------------------------------
create table public.problems (
  id           bigint generated by default as identity primary key,
  title        text   not null,
  description  text   not null default '',
  input_desc   text   not null default '',
  output_desc  text   not null default '',
  created_at   timestamptz not null default now()
);

-- ------------------------------------------------------------
-- test_cases
-- ------------------------------------------------------------
create table public.test_cases (
  id          bigint generated by default as identity primary key,
  problem_id  bigint  not null references public.problems(id) on delete cascade,
  stdin       text    not null default '',
  stdout      text    not null default '',
  is_hidden   boolean not null default false,
  created_at  timestamptz not null default now()
);

create index test_cases_problem_id_idx on public.test_cases(problem_id);

-- ------------------------------------------------------------
-- submissions
-- ------------------------------------------------------------
create table public.submissions (
  id          uuid        primary key default gen_random_uuid(),
  user_id     uuid        not null references public.users(id)    on delete cascade,
  problem_id  bigint      not null references public.problems(id) on delete cascade,
  code        text        not null,
  bytes       integer     not null,
  verdict     text        not null check (verdict in ('AC','WA','TLE','RE','CE','PENDING')),
  created_at  timestamptz not null default now()
);

create index submissions_problem_id_idx on public.submissions(problem_id);
create index submissions_user_id_idx    on public.submissions(user_id);
create index submissions_rank_idx       on public.submissions(problem_id, bytes asc, created_at asc);

-- ------------------------------------------------------------
-- leaderboard view
--   One best (shortest) accepted submission per user per problem.
--   Rank 1: fewest bytes  Rank 2: earliest submission time.
-- ------------------------------------------------------------
create view public.leaderboard as
with accepted as (
  select
    s.id,
    s.user_id,
    s.problem_id,
    s.bytes,
    s.created_at,
    row_number() over (
      partition by s.user_id, s.problem_id
      order by s.bytes asc, s.created_at asc
    ) as user_best_rank
  from public.submissions s
  where s.verdict = 'AC'
)
select
  a.problem_id,
  a.user_id,
  u.username,
  a.bytes,
  a.created_at,
  rank() over (
    partition by a.problem_id
    order by a.bytes asc, a.created_at asc
  ) as rank
from accepted a
join public.users u on u.id = a.user_id
where a.user_best_rank = 1
order by a.problem_id asc, a.bytes asc, a.created_at asc;

-- ------------------------------------------------------------
-- Row Level Security
-- ------------------------------------------------------------
alter table public.users       enable row level security;
alter table public.problems    enable row level security;
alter table public.test_cases  enable row level security;
alter table public.submissions enable row level security;

-- Public read for users, problems, submissions.
create policy "users_public_read"
  on public.users for select using (true);

create policy "problems_public_read"
  on public.problems for select using (true);

create policy "submissions_public_read"
  on public.submissions for select using (true);

-- IMPORTANT: test_cases are NEVER readable by the anon client.
-- Only the server (service_role key) bypasses RLS to fetch them.
-- No SELECT policy is created for test_cases, so anon access is denied.

-- ------------------------------------------------------------
-- Seed data
-- ------------------------------------------------------------
insert into public.users (username) values
  ('golf_master'),
  ('byte_ninja'),
  ('stack_wizard');

insert into public.problems (title, description, input_desc, output_desc) values
  (
    'Echo',
    'Read a line from standard input and print it back exactly as-is.',
    'A single line of text.',
    'The same line of text.'
  ),
  (
    'Sum Two Integers',
    'Read two space-separated integers and print their sum.',
    'Two integers a and b separated by a single space.',
    'A single integer: a + b.'
  ),
  (
    'Reverse String',
    'Read a string and print it reversed.',
    'A single line containing a string.',
    'The reversed string.'
  );

-- Test cases for problem 1 (Echo)
insert into public.test_cases (problem_id, stdin, stdout, is_hidden) values
  (1, 'hello', 'hello', false),
  (1, 'GolfScript', 'GolfScript', false),
  (1, 'deep sea', 'deep sea', true);

-- Test cases for problem 2 (Sum Two Integers)
insert into public.test_cases (problem_id, stdin, stdout, is_hidden) values
  (2, '2 3', '5', false),
  (2, '10 20', '30', false),
  (2, '-5 8', '3', true),
  (2, '1000000 2000000', '3000000', true);

-- Test cases for problem 3 (Reverse String)
insert into public.test_cases (problem_id, stdin, stdout, is_hidden) values
  (3, 'abc', 'cba', false),
  (3, 'golf', 'flog', false),
  (3, 'racecar', 'racecar', true);

-- ============================================================
--  End of schema
-- ============================================================