-- ============================================================================
-- ApexMedics Hospital Limited — Supabase schema (safe to re-run any time)
-- Run once in: Supabase Dashboard -> SQL Editor -> New query -> Run
--
-- Security model
--   * Visitors (anon key)  : read published content, submit job applications.
--   * Admin   (signed in)  : full create / read / update / delete on everything.
--   * Applications (CVs, licence numbers, phone numbers) are NEVER readable
--     by the public — only by a signed-in administrator.
-- ============================================================================

-- ---------- TABLES ----------------------------------------------------------
create table if not exists public.hospital_pages (
  slug          text primary key,
  title         text not null default '',
  headline      text,
  hero_lead     text,
  alert_text    text,
  about_kicker  text,
  about_heading text,
  about_lead    text,
  about_body    text,
  hours         text,
  location      text,
  phone         text,
  email         text,
  image         text,
  features      text,
  updated_at    timestamptz not null default now()
);

create table if not exists public.hospital_services (
  id          text primary key,
  title       text not null,
  category    text not null default 'clinical',
  slug        text not null,
  description text,
  doctor      text,
  hours       text,
  image       text,
  features    text,
  active      boolean not null default true,
  updated_at  timestamptz not null default now()
);

create table if not exists public.hospital_jobs (
  id            text primary key,
  ref           text,
  title         text not null,
  category      text not null default 'medical',
  department    text,
  type          text,
  qualification text,
  license       text,
  experience    text,
  description   text,
  duties        text,
  active        boolean not null default true,
  updated_at    timestamptz not null default now()
);

create table if not exists public.hospital_job_applications (
  id             text primary key,
  job_id         text,
  job_title      text not null,
  full_name      text not null,
  phone          text not null,
  email          text not null,
  licensing_body text,
  license_number text,
  qualification  text,
  experience     text,
  notice_period  text,
  summary        text,
  cv_filename    text,
  cv_data        text,
  status         text not null default 'Pending Review',
  applied_at     timestamptz not null default now()
);

-- ---------- MIGRATIONS FOR EXISTING PROJECTS --------------------------------
alter table public.hospital_jobs add column if not exists ref    text;
alter table public.hospital_jobs add column if not exists duties text;
alter table public.hospital_job_applications add column if not exists cv_data text;

create index if not exists hospital_apps_applied_idx on public.hospital_job_applications (applied_at desc);
create index if not exists hospital_apps_status_idx  on public.hospital_job_applications (status);

-- ---------- ROW LEVEL SECURITY ----------------------------------------------
alter table public.hospital_pages            enable row level security;
alter table public.hospital_services         enable row level security;
alter table public.hospital_jobs             enable row level security;
alter table public.hospital_job_applications enable row level security;

-- Remove every older / permissive policy so nothing public can write or read PII
do $$
declare r record;
begin
  for r in
    select policyname, tablename from pg_policies
    where schemaname = 'public'
      and tablename in ('hospital_pages','hospital_services','hospital_jobs','hospital_job_applications')
  loop
    execute format('drop policy if exists %I on public.%I', r.policyname, r.tablename);
  end loop;
end $$;

-- PAGES: public read, admin write
create policy "pages_public_read"  on public.hospital_pages for select to anon, authenticated using (true);
create policy "pages_admin_insert" on public.hospital_pages for insert to authenticated with check (true);
create policy "pages_admin_update" on public.hospital_pages for update to authenticated using (true) with check (true);
create policy "pages_admin_delete" on public.hospital_pages for delete to authenticated using (true);

-- SERVICES: public sees active only, admin sees and manages all
create policy "services_public_read"  on public.hospital_services for select to anon using (active = true);
create policy "services_admin_read"   on public.hospital_services for select to authenticated using (true);
create policy "services_admin_insert" on public.hospital_services for insert to authenticated with check (true);
create policy "services_admin_update" on public.hospital_services for update to authenticated using (true) with check (true);
create policy "services_admin_delete" on public.hospital_services for delete to authenticated using (true);

-- JOBS: public sees open vacancies only, admin manages all
create policy "jobs_public_read"  on public.hospital_jobs for select to anon using (active = true);
create policy "jobs_admin_read"   on public.hospital_jobs for select to authenticated using (true);
create policy "jobs_admin_insert" on public.hospital_jobs for insert to authenticated with check (true);
create policy "jobs_admin_update" on public.hospital_jobs for update to authenticated using (true) with check (true);
create policy "jobs_admin_delete" on public.hospital_jobs for delete to authenticated using (true);

-- APPLICATIONS: anyone may submit (new, pending, size-limited); only admin may read / manage
create policy "apps_public_submit" on public.hospital_job_applications
  for insert to anon, authenticated
  with check (
    status = 'Pending Review'
    and length(full_name) between 2 and 200
    and length(coalesce(cv_data, '')) < 9000000
  );
create policy "apps_admin_read"   on public.hospital_job_applications for select to authenticated using (true);
create policy "apps_admin_update" on public.hospital_job_applications for update to authenticated using (true) with check (true);
create policy "apps_admin_delete" on public.hospital_job_applications for delete to authenticated using (true);

-- ---------- TABLE PRIVILEGES -------------------------------------------------
revoke all on public.hospital_job_applications from anon;
grant insert on public.hospital_job_applications to anon;
grant select, insert, update, delete on all tables in schema public to authenticated;
grant select on public.hospital_pages, public.hospital_services, public.hospital_jobs to anon;

-- ============================================================================
-- AFTER RUNNING THIS SCRIPT:
--   1. Authentication -> Users -> "Add user" -> create your admin email + password
--      (tick "Auto Confirm User"). That is the login for /admin.html.
--   2. Authentication -> Providers -> Email: turn OFF "Allow new users to sign up"
--      so nobody else can create an account.
-- ============================================================================