-- Coaching Notes admin-write actions were gated on bare is_admin (0028),
-- with an explicit design note flagging that a delegatable feature_key
-- was deliberately deferred: introducing one without also excluding
-- 'coaching' from has_permission()'s is_broad_reviewer bypass (0016)
-- would silently sweep every agent's PRIVATE 1:1 coaching notes into
-- Berry's broad-reviewer auto-view clause, which legacy never did.
--
-- Real request now: delegate coaching management to Andrew specifically
-- (can_view=true, can_write=true), without granting him is_admin or
-- touching any of his other grants. This migration:
--   1. Adds feature_key 'coaching' to feature_registry.
--   2. Redefines has_permission() to exclude 'coaching' from the
--      is_broad_reviewer bypass too, alongside invoicing/rate_and_schedule
--      -- re-confirming the exact concern flagged in 0028 stays closed.
--   3. Moves agent_notes' 3 policies and agent_note_replies' 2 admin-
--      facing policies off bare is_admin onto
--      has_permission('coaching', need_write => true) OR is_admin (write)
--      / has_permission('coaching') OR is_admin (view). has_permission()
--      already has its own internal is_admin bypass, so the explicit
--      "OR is_admin" below is redundant but kept for the same
--      self-documenting reason existing call sites do it.
--   4. agent_note_replies_insert_own is untouched -- that's the agent's
--      own reply-to-your-own-note path, unrelated to admin/coaching-
--      manager write access.
--   5. Grants Andrew a real permissions row: 'coaching', can_view=true,
--      can_write=true. Nothing else about his profile/permissions
--      changes -- no is_admin, no other feature_key.

-- ============================================================
-- 1. New feature_key
-- ============================================================

insert into public.feature_registry (feature_key, display_name, display_order) values
  ('coaching', 'Coaching', 4)
on conflict (feature_key) do nothing;

-- ============================================================
-- 2. has_permission() -- is_broad_reviewer clause now also excludes
-- 'coaching', not just 'invoicing'/'rate_and_schedule'.
-- ============================================================

create or replace function public.has_permission(feature text, need_write boolean default false)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1 from public.permissions
    where user_id = auth.uid()
      and feature_key = feature
      and can_view
      and (not need_write or can_write)
  )
  or exists (
    select 1 from public.profiles where id = auth.uid() and is_admin = true
  )
  or (
    not need_write
    and feature not in ('invoicing', 'rate_and_schedule', 'coaching')
    and exists (
      select 1 from public.profiles where id = auth.uid() and is_broad_reviewer = true
    )
  );
$$;

grant execute on function public.has_permission(text, boolean) to authenticated;

-- ============================================================
-- 3. agent_notes -- select/insert/delete off bare is_admin
-- ============================================================

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

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

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

-- ============================================================
-- 4. agent_note_replies -- select/delete (admin-facing) off bare
-- is_admin. insert_own (agent replying to their own note) untouched.
-- ============================================================

drop policy if exists "agent_note_replies_select_own_or_admin" on public.agent_note_replies;
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 public.has_permission('coaching')
        or exists (select 1 from public.profiles where id = auth.uid() and is_admin = true)
      )
  )
);

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

-- ============================================================
-- 5. Grant Andrew 'coaching' -- can_view=true, can_write=true.
-- Nothing else about his profile/permissions changes.
-- ============================================================

insert into public.permissions (user_id, feature_key, can_view, can_write) values
  ('fd91642c-71ec-4716-b275-efc08564a625', 'coaching', true, true) -- Andrew
on conflict (user_id, feature_key) do update
  set can_view = true, can_write = true;

-- ============================================================
-- Self-verification guards
-- ============================================================

do $migration_guard$
declare
  notes_policy_count int;
  replies_policy_count int;
  andrew_perm_count int;
  feature_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 0038: expected 3 policies on agent_notes, found %', notes_policy_count;
  end if;
  if replies_policy_count <> 3 then
    raise exception 'Aborting 0038: expected 3 policies on agent_note_replies, found %', replies_policy_count;
  end if;

  select count(*) into feature_count from public.feature_registry where feature_key = 'coaching';
  if feature_count <> 1 then
    raise exception 'Aborting 0038: expected feature_registry to have exactly 1 coaching row, found %', feature_count;
  end if;

  select count(*) into andrew_perm_count from public.permissions
    where user_id = 'fd91642c-71ec-4716-b275-efc08564a625' and feature_key = 'coaching' and can_view = true and can_write = true;
  if andrew_perm_count <> 1 then
    raise exception 'Aborting 0038: expected Andrew to have exactly 1 coaching permissions row with view+write, found %', andrew_perm_count;
  end if;
end $migration_guard$;