-- Coaching Notes — ported from legacy state.agentNotes[agentId] = [...]
-- (JavaScript.html:5153-5293). Private manager-to-agent feedback: an
-- agent sees only notes addressed to them and can reply; any admin can
-- send/view/delete any agent's notes. Two tables instead of legacy's
-- nested replies array, so replies get their own rows/RLS.
--
-- agent_notes.category on the reply side is deliberately DROPPED, not
-- carried over: legacy's reply always hardcodes category:'Acknowledgement'
-- (JavaScript.html:5680 — there is no UI to pick a different one), so
-- storing a constant on every row conveys nothing. Simplification, not a
-- functional deviation.
--
-- *** DESIGN DECISION FLAGGED FOR CONFIRMATION, not yet run ***
-- Admin-side access (send/view-all/delete) is gated on `is_admin`
-- directly, NOT a new has_permission()-delegatable feature_key like
-- 'performance'/'invoicing'. Reasoning: has_permission()'s
-- is_broad_reviewer clause (0016) auto-grants VIEW access to any
-- feature except invoicing/rate_and_schedule to broad reviewers (Berry)
-- — appropriate for aggregate performance numbers, but these are
-- PRIVATE 1:1 feedback notes. Introducing a delegatable feature_key
-- here would silently sweep every agent's private coaching notes into
-- that broad-reviewer bypass, which legacy never does (legacy's gate is
-- bare `state.session.role === 'admin'`, no broader exposure
-- mechanism exists there at all). is_admin-only is the more
-- conservative choice, matching legacy's actual behavior exactly. If
-- coaching should instead be delegatable to specific non-Edwin admins
-- (the way invoicing/performance are), that's a real, separate decision
-- — flag it and this gets redesigned around feature_registry/permissions
-- instead, excluded from the broad-reviewer bypass the same way
-- invoicing/rate_and_schedule already are (0016/0020).

create table if not exists public.agent_notes (
  id          uuid primary key default gen_random_uuid(),
  agent_id    text not null,     -- recipient; free text, matches project convention
  subject     text,
  body        text not null,
  sent_by     text,              -- denormalized sender display (email/name), no names table
  created_at  timestamptz not null default now()
);

create table if not exists public.agent_note_replies (
  id          uuid primary key default gen_random_uuid(),
  note_id     uuid not null references public.agent_notes(id) on delete cascade,
  reply_text  text not null,
  created_at  timestamptz not null default now()
);

create index if not exists agent_notes_agent_id_idx on public.agent_notes (agent_id);
create index if not exists agent_note_replies_note_id_idx on public.agent_note_replies (note_id);

alter table public.agent_notes enable row level security;
alter table public.agent_note_replies enable row level security;

create policy "agent_notes_select_own_or_admin"
on public.agent_notes
for select
to authenticated
using (
  agent_id = public.current_agent_id()
  or exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

create policy "agent_notes_insert_admin"
on public.agent_notes
for insert
to authenticated
with check (
  exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

create policy "agent_notes_delete_admin"
on public.agent_notes
for delete
to authenticated
using (
  exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

-- Replies: the recipient agent can reply to their own note; admins can
-- read/delete any reply (matches legacy's admin view showing all
-- replies inline). No reply UPDATE at all in legacy (replies are
-- append-only once submitted) — none added here either.

create policy "agent_note_replies_select_own_or_admin"
on public.agent_note_replies
for select
to authenticated
using (
  exists (
    select 1 from public.agent_notes n
    where n.id = note_id
      and (n.agent_id = public.current_agent_id() or exists (select 1 from public.profiles where id = auth.uid() and is_admin = true))
  )
);

create policy "agent_note_replies_insert_own"
on public.agent_note_replies
for insert
to authenticated
with check (
  exists (
    select 1 from public.agent_notes n
    where n.id = note_id and n.agent_id = public.current_agent_id()
  )
);

create policy "agent_note_replies_delete_admin"
on public.agent_note_replies
for delete
to authenticated
using (
  exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
);

do $migration_guard$
declare
  notes_policy_count int;
  replies_policy_count int;
begin
  select count(*) into notes_policy_count from pg_policies where schemaname = 'public' and tablename = 'agent_notes';
  select count(*) into replies_policy_count from pg_policies where schemaname = 'public' and tablename = 'agent_note_replies';
  if notes_policy_count <> 3 then
    raise exception 'Aborting 0028: expected 3 policies on agent_notes, found %', notes_policy_count;
  end if;
  if replies_policy_count <> 3 then
    raise exception 'Aborting 0028: expected 3 policies on agent_note_replies, found %', replies_policy_count;
  end if;
end $migration_guard$;