-- ============================================================
-- SUBIIE PLATFORM — SUPABASE SCHEMA + ROW-LEVEL SECURITY
-- Run this ONCE in Supabase → SQL Editor → New query → Run.
-- Then use the "Supabase Setup" page in the portal to seed data.
-- ============================================================

create extension if not exists pgcrypto;

-- ---------- Profiles: maps auth users to a role + scope ----------
create table if not exists public.profiles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  role text not null check (role in ('owner','school','sub')),
  name text,
  email text,
  school text,      -- for role='school': the school they administer
  sub_name text,    -- for role='sub': their display name in the sub pool
  subiie_id text unique
);

-- Helper functions (security definer so RLS policies can read profiles)
create or replace function public.subiie_role() returns text
language sql stable security definer set search_path = public as
$$ select role from public.profiles where user_id = auth.uid() $$;

create or replace function public.subiie_school() returns text
language sql stable security definer set search_path = public as
$$ select school from public.profiles where user_id = auth.uid() $$;

create or replace function public.subiie_sub_name() returns text
language sql stable security definer set search_path = public as
$$ select sub_name from public.profiles where user_id = auth.uid() $$;

-- ---------- Data tables ----------
-- Pattern: id + optional school scope column + jsonb payload.
-- The payload shape matches the portal's data layer exactly.

create table if not exists public.schools          (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.subs             (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.assignments      (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.bills            (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.announcements    (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.support_tickets  (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.tech_tickets     (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.applications     (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.tax_records      (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.discipline_cases (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.intake_requests  (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.onboarding       (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.legal_docs       (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.content_library  (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.portal_admins    (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.sub_feedback     (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
create table if not exists public.week_absences    (id bigint generated always as identity primary key, school text, data jsonb not null, created_at timestamptz default now());
-- Aggregates / derived views stored as JSON (analytics, dashboards, settings…)
create table if not exists public.snapshots        (key text primary key, data jsonb not null, updated_at timestamptz default now());

-- ---------- Row-level security ----------
alter table public.profiles enable row level security;
create policy "read own profile" on public.profiles for select using (user_id = auth.uid());
create policy "owner reads all profiles" on public.profiles for select using (public.subiie_role() = 'owner');
create policy "owner manages profiles" on public.profiles for all using (public.subiie_role() = 'owner') with check (public.subiie_role() = 'owner');

do $$
declare t text;
begin
  foreach t in array array['schools','subs','assignments','bills','announcements','support_tickets','tech_tickets','applications','tax_records','discipline_cases','intake_requests','onboarding','legal_docs','content_library','portal_admins','sub_feedback','week_absences','snapshots']
  loop
    execute format('alter table public.%I enable row level security', t);
    -- Owners (Subiie HQ): full access to everything
    execute format('create policy "owner full access" on public.%I for all using (public.subiie_role() = ''owner'') with check (public.subiie_role() = ''owner'')', t);
  end loop;
end $$;

-- Everyone signed in can read shared reference data
create policy "authed read schools"       on public.schools       for select using (auth.role() = 'authenticated');
create policy "authed read announcements" on public.announcements for select using (auth.role() = 'authenticated');
create policy "authed read snapshots"     on public.snapshots     for select using (auth.role() = 'authenticated');
create policy "authed read content"       on public.content_library for select using (auth.role() = 'authenticated');

-- School admins: scoped to their own school
create policy "school reads own assignments"  on public.assignments for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school posts assignments"      on public.assignments for insert with check (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school updates own assignments" on public.assignments for update using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads sub pool"         on public.subs for select using (public.subiie_role() = 'school');
create policy "school reads own bills"        on public.bills for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own tech tickets" on public.tech_tickets for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school files tech tickets"     on public.tech_tickets for insert with check (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school files support tickets"  on public.support_tickets for insert with check (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own support"      on public.support_tickets for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own feedback"     on public.sub_feedback for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school writes feedback"        on public.sub_feedback for insert with check (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own absences"     on public.week_absences for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own intake"       on public.intake_requests for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school updates own intake"     on public.intake_requests for update using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own legal"        on public.legal_docs for select using (public.subiie_role() = 'school' and school = public.subiie_school());
create policy "school reads own admins"       on public.portal_admins for select using (public.subiie_role() = 'school' and school = public.subiie_school());

-- Substitutes: see the assignment board, claim jobs, apply, see own tax records
create policy "sub reads assignments"  on public.assignments for select using (public.subiie_role() = 'sub');
create policy "sub claims assignments" on public.assignments for update using (public.subiie_role() = 'sub');
create policy "sub applies"            on public.applications for insert with check (auth.role() = 'authenticated' or auth.role() = 'anon');
create policy "sub reads own tax"      on public.tax_records for select using (public.subiie_role() = 'sub' and data->>'name' = public.subiie_sub_name());
create policy "sub reads own record"   on public.subs for select using (public.subiie_role() = 'sub' and data->>'name' = public.subiie_sub_name());
create policy "sub updates own record" on public.subs for update using (public.subiie_role() = 'sub' and data->>'name' = public.subiie_sub_name());

-- ---------- Demo accounts (password for all three: subiie-demo) ----------
-- If this block errors on your project, create the three users instead in
-- Authentication → Users → Add user, then run just the INSERT INTO profiles below.
do $$
declare
  uid_owner uuid := gen_random_uuid();
  uid_school uuid := gen_random_uuid();
  uid_sub uuid := gen_random_uuid();
begin
  if not exists (select 1 from auth.users where email = 'owner@demo.subiie.com') then
    insert into auth.users (instance_id, id, aud, role, email, encrypted_password, email_confirmed_at, raw_app_meta_data, raw_user_meta_data, created_at, updated_at, confirmation_token, recovery_token, email_change_token_new, email_change, email_change_token_current, phone_change, phone_change_token, reauthentication_token)
    values
      ('00000000-0000-0000-0000-000000000000', uid_owner,  'authenticated', 'authenticated', 'owner@demo.subiie.com',  crypt('subiie-demo', gen_salt('bf')), now(), '{"provider":"email","providers":["email"]}', '{"name":"Elena Marsh"}', now(), now(), '', '', '', '', '', '', '', ''),
      ('00000000-0000-0000-0000-000000000000', uid_school, 'authenticated', 'authenticated', 'school@demo.subiie.com', crypt('subiie-demo', gen_salt('bf')), now(), '{"provider":"email","providers":["email"]}', '{"name":"Devon Carter"}', now(), now(), '', '', '', '', '', '', '', ''),
      ('00000000-0000-0000-0000-000000000000', uid_sub,    'authenticated', 'authenticated', 'sub@demo.subiie.com',    crypt('subiie-demo', gen_salt('bf')), now(), '{"provider":"email","providers":["email"]}', '{"name":"Dana Whitfield"}', now(), now(), '', '', '', '', '', '', '', '');
    insert into auth.identities (id, user_id, provider_id, identity_data, provider, last_sign_in_at, created_at, updated_at)
    values
      (gen_random_uuid(), uid_owner,  uid_owner::text,  jsonb_build_object('sub', uid_owner::text,  'email', 'owner@demo.subiie.com'),  'email', now(), now(), now()),
      (gen_random_uuid(), uid_school, uid_school::text, jsonb_build_object('sub', uid_school::text, 'email', 'school@demo.subiie.com'), 'email', now(), now(), now()),
      (gen_random_uuid(), uid_sub,    uid_sub::text,    jsonb_build_object('sub', uid_sub::text,    'email', 'sub@demo.subiie.com'),    'email', now(), now(), now());
  end if;
  insert into public.profiles (user_id, role, name, email, school, sub_name, subiie_id)
  select u.id, v.role, v.name, u.email, v.school, v.sub_name, v.subiie_id
  from (values
    ('owner@demo.subiie.com',  'owner',  'Elena Marsh',    null::text,                   null::text,       'SB-OWNER-001'),
    ('school@demo.subiie.com', 'school', 'Devon Carter',   'St. Ambrose Preparatory',    null,             'SB-SCH-001'),
    ('sub@demo.subiie.com',    'sub',    'Dana Whitfield', null,                         'Dana Whitfield', 'SB-SUB-001')
  ) as v(email, role, name, school, sub_name, subiie_id)
  join auth.users u on u.email = v.email
  on conflict (user_id) do nothing;
end $$;
