-- Project-level "General Issues" plus a synced user list for the "Action To"
-- assignee picker.

-- ---------------------------------------------------------------------------
-- profiles: a readable mirror of auth.users so the app can list assignable
-- users (auth.users isn't queryable with the anon/authenticated key).
-- ---------------------------------------------------------------------------
create table if not exists profiles (
  id    uuid primary key references auth.users (id) on delete cascade,
  email text
);

alter table profiles enable row level security;

drop policy if exists "authenticated read profiles" on profiles;
create policy "authenticated read profiles" on profiles
  for select to authenticated using (true);

create or replace function handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, email)
  values (new.id, new.email)
  on conflict (id) do update set email = excluded.email;
  return new;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function handle_new_user();

-- Backfill existing users.
insert into public.profiles (id, email)
select id, email from auth.users
on conflict (id) do nothing;

-- ---------------------------------------------------------------------------
-- issues
-- ---------------------------------------------------------------------------
create table if not exists issues (
  id             uuid primary key default gen_random_uuid(),
  project_id     uuid not null references projects (id) on delete cascade,
  category       text not null default 'Revision'
                   check (category in ('Revision', 'Bug', 'Question', 'Live Changelog')),
  content        text not null default '',
  image_path     text,
  status         text not null default 'Open'
                   check (status in ('Open', 'Addressing', 'Ready for Review',
                     'Revision Required', 'Address Later', 'FYI Only', 'Resolved')),
  action_to      text[] not null default '{}',
  prev_action_to text[] not null default '{}',
  author_id      uuid,
  author_email   text,
  created_at     timestamptz not null default now()
);
create index if not exists issues_project_id_idx on issues (project_id);

alter table issues enable row level security;

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

-- Reuse the shared authorship trigger (sets author_id + author_email on insert).
drop trigger if exists issues_set_author on issues;
create trigger issues_set_author
  before insert on issues
  for each row execute function set_author();

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