-- Replace per-entry comments with a single org-wide comment board.
-- The user wants @mentions visible to anyone the next time they log in,
-- not pinned to one row.


create table if not exists public.comments (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  author_id uuid references auth.users(id) on delete set null,
  body text not null check (length(trim(body)) > 0),
  resolved_at timestamptz,
  resolved_by uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now()
);

create index if not exists idx_comments_org_created
  on public.comments(org_id, created_at desc);
create index if not exists idx_comments_org_unresolved
  on public.comments(org_id, created_at desc)
  where resolved_at is null;

alter table public.comments enable row level security;

drop policy if exists "comments_org_select" on public.comments;
create policy "comments_org_select"
  on public.comments for select
  to authenticated
  using (public.is_org_member(org_id));

drop policy if exists "comments_org_insert" on public.comments;
create policy "comments_org_insert"
  on public.comments for insert
  to authenticated
  with check (public.is_org_member(org_id) and author_id = auth.uid());

-- Anyone in the org can flip resolved_at on any comment.
drop policy if exists "comments_org_update" on public.comments;
create policy "comments_org_update"
  on public.comments for update
  to authenticated
  using (public.is_org_member(org_id))
  with check (public.is_org_member(org_id));

-- Authors can delete their own; super admins can delete any.
drop policy if exists "comments_author_or_admin_delete" on public.comments;
create policy "comments_author_or_admin_delete"
  on public.comments for delete
  to authenticated
  using (author_id = auth.uid() or public.current_user_is_super_admin());

comment on table public.comments is
  'Org-wide comment / note board. Free-text body, optional @mentions, resolvable.';
