-- CURAS Research Database schema
-- Run once in the Supabase SQL Editor (Dashboard -> SQL Editor -> New query -> Run).
-- The application talks to Supabase only with the service_role key from the server.
-- Row Level Security is enabled with no policies, so the public anon key can neither
-- read nor write these tables.

create extension if not exists pgcrypto;

create table if not exists public.listings (
  id                       uuid primary key default gen_random_uuid(),
  status                   text not null default 'published'
                             check (status in ('published', 'retired')),
  faculty_name             text not null,
  faculty_profile_url      text,
  researchworks_url        text,
  project_title            text,
  keywords                 text,
  description              text,
  methods                  text,
  opportunity_type         text,
  term_availability        text,
  commitment               text,
  standing                 text,
  prerequisites            text,
  areas_of_study           text,
  mentorship               text,
  previous_student_work    text,
  faculty_note             text,
  application_deadline     text,
  application_requirements text,
  updated_on               date not null default current_date,
  source                   text not null default 'form'
                             check (source in ('excel_import', 'form', 'admin')),
  submission_id            uuid,            -- the submission that created this listing, if any
  retired_at               timestamptz,
  created_at               timestamptz not null default now(),
  updated_at               timestamptz not null default now()
);

create table if not exists public.submissions (
  id                       uuid primary key default gen_random_uuid(),
  kind                     text not null check (kind in ('new', 'update')),
  listing_id               uuid references public.listings(id) on delete set null,
  submitter_name           text not null,
  submitter_netid          text not null,
  submitter_email          text,
  faculty_name             text not null,
  faculty_profile_url      text,
  researchworks_url        text,
  project_title            text,
  keywords                 text,
  description              text,
  methods                  text,
  opportunity_type         text,
  term_availability        text,
  commitment               text,
  standing                 text,
  prerequisites            text,
  areas_of_study           text,
  mentorship               text,
  previous_student_work    text,
  faculty_note             text,
  application_deadline     text,
  application_requirements text,
  status                   text not null default 'pending'
                             check (status in ('pending', 'approved', 'rejected')),
  admin_note               text,
  reviewed_at              timestamptz,
  published_at             timestamptz,     -- set once the approved listing has been written
  created_at               timestamptz not null default now()
);

create index if not exists listings_status_idx    on public.listings (status);
create unique index if not exists listings_submission_id_idx on public.listings (submission_id)
  where submission_id is not null;
create index if not exists submissions_status_idx on public.submissions (status, created_at desc);

-- Keep updated_at current on every change.
create or replace function public.set_updated_at() returns trigger
language plpgsql as $$
begin
  new.updated_at = now();
  return new;
end $$;

drop trigger if exists listings_set_updated_at on public.listings;
create trigger listings_set_updated_at
  before update on public.listings
  for each row execute function public.set_updated_at();

-- Lock the tables down. The server uses the service_role key, which bypasses RLS.
alter table public.listings    enable row level security;
alter table public.submissions enable row level security;
revoke all on table public.listings    from anon, authenticated;
revoke all on table public.submissions from anon, authenticated;