-- ============================================================
-- M/M Contest Scheduler — Supabase Schema
-- ============================================================
-- Instructions:
--   1. Go to your Supabase project → SQL Editor → New query
--   2. Paste this entire file and click Run
-- ============================================================

create table if not exists contests (
  id           varchar(6)   primary key,
  name         text         not null default 'Contest',
  callsign     text         not null default '',
  start_date   text         not null default '2025-01-01',
  start_time   text         not null default '00:00',
  duration_h   integer      not null default 48,
  shift_h      real         not null default 2,
  max_con      integer      not null default 3,
  min_off      integer      not null default 1,
  dead_band    integer      not null default 15,
  solar        text         not null default 'avg',
  custom_rates jsonb,
  goal_rates   jsonb,
  director_pin text         not null,
  created_at   timestamptz  default now()
);

create table if not exists stations (
  id          bigserial   primary key,
  contest_id  varchar(6)  not null references contests(id) on delete cascade,
  name        text        not null,
  band        text        not null,
  sort_order  integer     not null default 0
);

create table if not exists operators (
  id          bigserial   primary key,
  contest_id  varchar(6)  not null references contests(id) on delete cascade,
  callsign    text        not null,
  skill       text        not null default 'intermediate',
  color_idx   integer     not null default 0
);

create table if not exists schedule (
  id          bigserial   primary key,
  contest_id  varchar(6)  not null references contests(id) on delete cascade,
  station_id  bigint      not null references stations(id) on delete cascade,
  shift_index integer     not null,
  value       text,
  constraint  schedule_unique unique (station_id, shift_index)
);

-- Disable RLS — auth is PIN/callsign based at the application level.
-- Contest schedule data is not sensitive. Tighten this if you want.
alter table contests  disable row level security;
alter table stations  disable row level security;
alter table operators disable row level security;
alter table schedule  disable row level security;

-- Enable real-time replication for live grid updates
alter publication supabase_realtime add table schedule;
alter publication supabase_realtime add table operators;

-- ============================================================
-- AUTO-DELETE: contests expire 5 days after contest ends
-- ============================================================
-- The contest end time is derived from start_date + start_time + duration_h.
-- A pg_cron job runs once daily at 03:00 UTC and deletes any contest
-- whose end time + 5 days is in the past.
-- Cascade deletes automatically remove stations, operators, and schedule rows.
-- ============================================================

-- Enable the pg_cron extension (already available on all Supabase projects)
create extension if not exists pg_cron;

-- Helper function: compute the UTC end timestamp of a contest
create or replace function contest_end_time(
  p_start_date text,
  p_start_time text,
  p_duration_h integer
)
returns timestamptz
language sql
immutable
as $$
  select (p_start_date || 'T' || p_start_time || ':00Z')::timestamptz
       + (p_duration_h || ' hours')::interval;
$$;

-- Grant cron access to the postgres role (required by Supabase)
grant usage on schema cron to postgres;

-- Schedule the cleanup job: run daily at 03:00 UTC
-- Deletes contests that ended more than 5 days ago (cascade handles all child rows)
select cron.schedule(
  'contest-auto-delete',           -- job name (unique, safe to re-run)
  '0 3 * * *',                     -- cron expression: 03:00 UTC every day
  $$
    delete from contests
    where contest_end_time(start_date, start_time, duration_h)
          + interval '5 days' < now();
  $$
);