-- Review portal schema (Phase 4).
-- Idempotent where practical so it can be re-run safely during setup.

-- ---------------------------------------------------------------------------
-- Tables
-- ---------------------------------------------------------------------------

create table if not exists projects (
  id         uuid primary key default gen_random_uuid(),
  slug       text not null unique,
  name       text not null,
  created_at timestamptz not null default now()
);

create table if not exists pages (
  id          uuid primary key default gen_random_uuid(),
  project_id  uuid not null references projects (id) on delete cascade,
  page_name   text not null,
  staging_url text,
  live_url    text,
  status      text not null default 'open'
                constraint pages_status_check
                check (status in ('open', 'creating', 'finalizing', 'revision pending', 'revising', 'drafted', 'completed')),
  signoff     text not null default 'not ready'
                constraint pages_signoff_check
                check (signoff in ('not ready', 'ready for review', 'revision not ready', 'revision needed', 'approved')),
  position    integer not null default 0,
  created_at  timestamptz not null default now()
);
create index if not exists pages_project_id_idx on pages (project_id);

create table if not exists status_logs (
  id              uuid primary key default gen_random_uuid(),
  page_id         uuid not null references pages (id) on delete cascade,
  previous_status text,
  new_status      text not null,
  changed_at      timestamptz not null default now()
);
create index if not exists status_logs_page_id_idx on status_logs (page_id);

create table if not exists notes (
  id         uuid primary key default gen_random_uuid(),
  page_id    uuid not null references pages (id) on delete cascade,
  content    text not null default '',
  -- Storage object key (private bucket). The app derives a signed URL from this.
  image_path text,
  created_at timestamptz not null default now()
);
create index if not exists notes_page_id_idx on notes (page_id);

-- ---------------------------------------------------------------------------
-- Status-change logging trigger.
-- Guarantees a status_logs row is written whenever pages.status changes,
-- regardless of which client made the change (the spec's "backend captures it").
-- security definer so the insert bypasses RLS.
-- ---------------------------------------------------------------------------

create or replace function log_status_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if new.status is distinct from old.status then
    insert into status_logs (page_id, previous_status, new_status)
    values (new.id, old.status, new.status);
  end if;
  return new;
end;
$$;

drop trigger if exists pages_status_change on pages;
create trigger pages_status_change
  after update on pages
  for each row
  execute function log_status_change();

-- ---------------------------------------------------------------------------
-- Row Level Security: any authenticated (logged-in) user has full access.
-- anon (logged-out) has none. Role-based rules (client vs dev) come later.
-- ---------------------------------------------------------------------------

alter table projects    enable row level security;
alter table pages       enable row level security;
alter table status_logs enable row level security;
alter table notes       enable row level security;

drop policy if exists "authenticated full access" on projects;
create policy "authenticated full access" on projects
  for all to authenticated using (true) with check (true);

drop policy if exists "authenticated full access" on pages;
create policy "authenticated full access" on pages
  for all to authenticated using (true) with check (true);

-- status_logs: read-only to clients; writes happen via the trigger (definer).
drop policy if exists "authenticated read logs" on status_logs;
create policy "authenticated read logs" on status_logs
  for select to authenticated using (true);

drop policy if exists "authenticated full access" on notes;
create policy "authenticated full access" on notes
  for all to authenticated using (true) with check (true);

-- ---------------------------------------------------------------------------
-- Private storage bucket for pasted screenshots + its access policies.
-- ---------------------------------------------------------------------------

insert into storage.buckets (id, name, public)
values ('note-images', 'note-images', false)
on conflict (id) do nothing;

drop policy if exists "authenticated read note-images" on storage.objects;
create policy "authenticated read note-images" on storage.objects
  for select to authenticated using (bucket_id = 'note-images');

drop policy if exists "authenticated upload note-images" on storage.objects;
create policy "authenticated upload note-images" on storage.objects
  for insert to authenticated with check (bucket_id = 'note-images');

-- ---------------------------------------------------------------------------
-- Realtime: broadcast row changes so multiple reviewers stay in sync.
-- ---------------------------------------------------------------------------

do $$ begin
  alter publication supabase_realtime add table pages;
exception when duplicate_object then null;
end $$;

do $$ begin
  alter publication supabase_realtime add table notes;
exception when duplicate_object then null;
end $$;
