-- Features: a per-project feature tracker shown below Page Reviews.
-- Same Status + Review (signoff) + Notes columns as pages, but the first column
-- is a free-text description (like a General Issue) instead of staging/live URLs.
--
-- Feature notes/comments reuse the existing notes + comments tables: notes gains
-- a nullable feature_id, and a note now belongs to exactly one of a page OR a
-- feature. This keeps the whole notes/threads/screenshots UI shared.

create table if not exists features (
  id           uuid primary key default gen_random_uuid(),
  project_id   uuid not null references projects (id) on delete cascade,
  description  text not null default '',
  status       text not null default 'open'
                 constraint features_status_check
                 check (status in ('open', 'creating', 'finalizing', 'revision pending', 'revising', 'drafted', 'completed')),
  signoff      text not null default 'not ready'
                 constraint features_signoff_check
                 check (signoff in ('not ready', 'ready for review', 'revision not ready', 'revision needed', 'approved')),
  position     integer not null default 0,
  author_id    uuid,
  author_email text,
  created_at   timestamptz not null default now()
);
create index if not exists features_project_id_idx on features (project_id);

-- Make notes polymorphic: a note hangs off a page OR a feature (exactly one).
alter table notes alter column page_id drop not null;
alter table notes add column if not exists feature_id uuid references features (id) on delete cascade;
create index if not exists notes_feature_id_idx on notes (feature_id);

do $$ begin
  alter table notes add constraint notes_parent_check
    check ((page_id is not null) <> (feature_id is not null));
exception when duplicate_object then null;
end $$;

-- Author stamping (reuses the shared set_author() defined in 0007).
drop trigger if exists features_set_author on features;
create trigger features_set_author
  before insert on features
  for each row execute function set_author();

-- RLS: any authenticated user has full access (same model as pages/notes).
alter table features enable row level security;
drop policy if exists "authenticated full access" on features;
create policy "authenticated full access" on features
  for all to authenticated using (true) with check (true);

-- Realtime so multiple reviewers stay in sync.
do $$ begin
  alter publication supabase_realtime add table features;
exception when duplicate_object then null;
end $$;
