-- Drops profiles.is_invoice_reviewer / profiles.is_invoice_approver and the
-- public.is_invoice_reviewer() / public.is_invoice_approver() SQL functions
-- (all from 0008), now that 0018 migrated every remaining live RLS
-- policy and trigger function off them onto has_permission(), and both a
-- database-side re-check (below) and an app-code grep of the entire src/
-- tree (done in conversation, not re-run here — see chat log) came back
-- with zero live references. public.current_agent_id() is untouched: it's
-- unrelated to these two flags and still actively used throughout
-- invoices/qpi_qualifications RLS.
--
-- Two DO-block guards below re-verify the DB side immediately before
-- dropping anything, in the same transaction: if anything still
-- references either name, the whole migration aborts (raises before any
-- drop statement runs) rather than silently breaking something. These are
-- the corrected, safe forms of the two verification queries from this
-- session — the first version of the second query mis-evaluated
-- pg_get_functiondef() against pg_proc rows outside the intended filter
-- (Postgres does not guarantee WHERE-clause predicate evaluation order),
-- which crashed on the built-in array_agg aggregate. Here it's restricted
-- to prokind = 'f' (plain functions only) inside a subquery, so
-- pg_get_functiondef() is never called on an aggregate's oid.

do $$
begin
  if exists (
    select 1 from pg_policies
    where schemaname = 'public'
      and (
        qual ilike '%is_invoice_reviewer%' or qual ilike '%is_invoice_approver%'
        or with_check ilike '%is_invoice_reviewer%' or with_check ilike '%is_invoice_approver%'
      )
  ) then
    raise exception 'Aborting 0019: a policy still references is_invoice_reviewer/is_invoice_approver — do not drop yet';
  end if;
end $$;

do $$
declare
  bad_count integer;
begin
  select count(*) into bad_count
  from (
    select p.proname, pg_get_functiondef(p.oid) as def
    from pg_proc p
    join pg_namespace n on n.oid = p.pronamespace
    where n.nspname = 'public'
      and p.prokind = 'f'
      and p.proname not in ('is_invoice_reviewer', 'is_invoice_approver')
  ) fn_defs
  where def ilike '%is_invoice_reviewer(%' or def ilike '%is_invoice_approver(%';

  if bad_count > 0 then
    raise exception 'Aborting 0019: % other function(s) still call is_invoice_reviewer()/is_invoice_approver()', bad_count;
  end if;
end $$;

-- ============================================================
-- Guards passed — safe to drop. Functions first (their bodies reference
-- the columns; dropping columns first would leave a function whose body
-- silently references a now-missing column until next called), then the
-- columns themselves.
-- ============================================================

drop function if exists public.is_invoice_reviewer();
drop function if exists public.is_invoice_approver();

alter table public.profiles
  drop column if exists is_invoice_reviewer,
  drop column if exists is_invoice_approver;