-- Part 1 of 2 (split from the original 0036 for isolated debugging —
-- three prior paste attempts left the live function byte-identical to
-- the original 0010 version, cause unconfirmed). This file is ONLY the
-- CREATE OR REPLACE FUNCTION statement — no guard, no policy change,
-- nothing else in this paste, so a rollback of any other statement
-- cannot possibly take this one down with it.
--
-- Explicitly confirmed decision: is_admin accounts (today, only Edwin)
-- may approve/reject/reverse/pay their OWN invoice — self-approval
-- stays fully blocked for everyone else (Quinty included). See the
-- full rationale in the original 0036 design (now split across this
-- file + 0036b + a still-pending policy-only follow-up for
-- invoices_update_approver, not yet re-split out).
create or replace function public.invoices_restrict_owner_status_transitions()
returns trigger
language plpgsql
as $func$
begin
  if new.agent_id = public.current_agent_id()
     and new.status is distinct from old.status
     and not (
       (old.status = 'draft' and new.status = 'submitted')
       or (old.status = 'submitted' and new.status = 'draft')
       or (old.status = 'rejected' and new.status = 'submitted')
     )
     -- Self-approval exception (deliberate): an is_admin owner is
     -- exempt from the pairwise-transition restriction entirely.
     and not exists (
       select 1 from public.profiles where id = auth.uid() and is_admin = true
     )
  then
    raise exception 'Not an allowed status transition for the invoice owner: % -> %', old.status, new.status;
  end if;

  return new;
end;
$func$;