-- Fixes a real access-control bug found in conversation: Dominic and
-- Andrew each hold a single permissions row (feature_key='invoicing',
-- can_view=true, can_write=true). Because 'invoicing' is the one
-- feature_key currently gating schedule, invoice_settings, rate_history,
-- qpi_qualifications, AND the invoices table/approve-reject-reverse-pay
-- UI all together, that one row also currently grants them full invoice
-- view+approval access, invoice_settings write, and full
-- qpi_qualifications access (view/insert/update/delete qualifications).
-- They should never have had any of that. Legacy kept these two roles
-- strictly separate: INVOICE_BILLING_ADMINS (Edwin/Dominic/Andrew — rate
-- + schedule only) vs. INVOICE_APPROVERS (Quinty/Edwin only — actual
-- invoice access).
--
-- Full audit of every has_permission('invoicing', ...) call site (13
-- checks: schedule x2, invoice_settings x1, rate_history x3, invoices x3
-- incl. the content-edit-lock trigger function, qpi_qualifications x4)
-- plus both client-side UI gates (schedule/page.js, invoices/[id]/page.js)
-- done in conversation before writing this — see chat log for the full
-- table-by-table, page-by-page list. schedule/page.js's client-side gate
-- is updated in the same commit as this migration to check
-- 'rate_and_schedule' instead of 'invoicing'.
--
-- Fix, in order below:
--   1. New feature_key 'rate_and_schedule'.
--   2. schedule + rate_history write policies move onto it.
--      invoice_settings, qpi_qualifications, and invoices are NOT
--      touched by this migration at all — still gated on 'invoicing'.
--   3. has_permission()'s is_broad_reviewer clause (0016) now excludes
--      BOTH 'invoicing' and 'rate_and_schedule' (was 'invoicing' only) —
--      per explicit instruction, Berry should have zero access to
--      anything invoicing-adjacent, only view access to genuinely
--      separate future features.
--   4. Grant Dominic + Andrew 'rate_and_schedule' (can_view=true,
--      can_write=true).
--   5. Delete Dominic + Andrew's 'invoicing' row entirely — zero
--      invoicing access, matching legacy exactly. This also correctly
--      revokes their invoice_settings write and qpi_qualifications
--      access as a confirmed, intended side effect, not a silent one.
--
-- ANOTHER CONFIRMED CONSEQUENCE, found while writing this, not just
-- Dominic/Andrew: Quinty currently holds only an 'invoicing' can_write
-- row (no is_admin, no grant here of 'rate_and_schedule') — under the
-- OLD shared gate this also gave her schedule/rate_history write, which
-- she loses once schedule/rate_history move to 'rate_and_schedule',
-- since this migration does not grant her that new feature_key. Per
-- legacy, Quinty was only ever an INVOICE_APPROVER, never an
-- INVOICE_BILLING_ADMIN — so losing schedule/rate access here is the
-- correct consequence of properly separating the two roles, not a bug.
-- Called out explicitly since it wasn't named in the original bug
-- report (which only named Dominic/Andrew gaining invoice access).
--
-- Self-verification guards at the end abort (raise, rolling back the
-- whole transaction) if the end state isn't exactly this.

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

insert into public.feature_registry (feature_key, display_name, display_order) values
  ('rate_and_schedule', 'Rate & Schedule', 2)
on conflict (feature_key) do nothing;

-- ============================================================
-- 2. has_permission() — is_broad_reviewer clause now excludes
-- 'rate_and_schedule' too, not just 'invoicing'. Everything else
-- (the can_view fix from 0018, the is_admin bypass) is unchanged.
-- ============================================================

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')
    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. schedule — both write policies move off 'invoicing'
-- ============================================================

drop policy if exists "schedule_insert" on public.schedule;
create policy "schedule_insert"
on public.schedule
for insert
to authenticated
with check (public.has_permission('rate_and_schedule', need_write => true));

drop policy if exists "schedule_update" on public.schedule;
create policy "schedule_update"
on public.schedule
for update
to authenticated
using (public.has_permission('rate_and_schedule', need_write => true))
with check (public.has_permission('rate_and_schedule', need_write => true));

-- ============================================================
-- 4. rate_history — all three write policies move off 'invoicing'
-- ============================================================

drop policy if exists "rate_history_insert" on public.rate_history;
create policy "rate_history_insert"
on public.rate_history
for insert
to authenticated
with check (public.has_permission('rate_and_schedule', need_write => true));

drop policy if exists "rate_history_update" on public.rate_history;
create policy "rate_history_update"
on public.rate_history
for update
to authenticated
using (public.has_permission('rate_and_schedule', need_write => true))
with check (public.has_permission('rate_and_schedule', need_write => true));

drop policy if exists "rate_history_delete" on public.rate_history;
create policy "rate_history_delete"
on public.rate_history
for delete
to authenticated
using (public.has_permission('rate_and_schedule', need_write => true));

-- ============================================================
-- 5. invoice_settings, qpi_qualifications, invoices — UNCHANGED, not
-- referenced anywhere below on purpose: invoice_settings_update,
-- qpi_qualifications_{select,insert,update,delete}_approver,
-- invoices_select_reviewer_or_approver, invoices_update_approver, and
-- invoices_restrict_content_edit_when_submitted() all stay exactly as
-- they were, gated on has_permission('invoicing', ...).
-- ============================================================

-- ============================================================
-- 6. Grant Dominic + Andrew rate_and_schedule, delete their invoicing row
-- ============================================================

insert into public.permissions (user_id, feature_key, can_view, can_write) values
  ('c04f7a4a-ca51-4d6e-b146-f35e51b57e01', 'rate_and_schedule', true, true), -- Dominic
  ('fd91642c-71ec-4716-b275-efc08564a625', 'rate_and_schedule', true, true)  -- Andrew
on conflict (user_id, feature_key) do update
  set can_view = true, can_write = true;

delete from public.permissions
where feature_key = 'invoicing'
  and user_id in (
    'c04f7a4a-ca51-4d6e-b146-f35e51b57e01', -- Dominic
    'fd91642c-71ec-4716-b275-efc08564a625'  -- Andrew
  );

-- ============================================================
-- 7. Self-verification guards
-- ============================================================

do $$
declare
  bad_count integer;
begin
  select count(*) into bad_count
  from public.permissions
  where feature_key = 'invoicing'
    and user_id in (
      'c04f7a4a-ca51-4d6e-b146-f35e51b57e01',
      'fd91642c-71ec-4716-b275-efc08564a625'
    );
  if bad_count > 0 then
    raise exception 'Aborting 0020: Dominic/Andrew still have an invoicing permissions row after the delete (% rows)', bad_count;
  end if;
end $$;

do $$
declare
  good_count integer;
begin
  select count(*) into good_count
  from public.permissions
  where feature_key = 'rate_and_schedule'
    and can_view = true and can_write = true
    and user_id in (
      'c04f7a4a-ca51-4d6e-b146-f35e51b57e01',
      'fd91642c-71ec-4716-b275-efc08564a625'
    );
  if good_count <> 2 then
    raise exception 'Aborting 0020: expected exactly 2 rate_and_schedule rows (Dominic+Andrew) with can_view/can_write=true, found %', good_count;
  end if;
end $$;

do $$
declare
  bad_count integer;
begin
  select count(*) into bad_count
  from pg_policies
  where schemaname = 'public'
    and tablename in ('schedule', 'rate_history')
    and (qual ilike '%''invoicing''%' or with_check ilike '%''invoicing''%');
  if bad_count > 0 then
    raise exception 'Aborting 0020: % schedule/rate_history polic(ies) still reference invoicing', bad_count;
  end if;
end $$;