-- Tighten invoices_update_owner's effective behavior: the owner should only
-- be able to perform these three specific status transitions on their own
-- invoice:
--   draft     -> submitted  (submit)
--   submitted -> draft      (retract)
--   rejected  -> submitted  (resubmit after rejection)
-- Not free movement within {draft, submitted, rejected} as the current
-- USING/WITH CHECK set-membership check on invoices_update_owner (0008)
-- actually allows. That looseness is exactly how the gap found while
-- testing 0009 happened: submitted -> rejected is set-membership-valid, so
-- ANY owner (not just approvers) could self-reject their own invoice,
-- fully bypassing invoices_update_approver, and unaffected by 0009's fix
-- since that fix only touched the approver policy.
--
-- Why a trigger instead of editing invoices_update_owner's SQL directly:
-- RLS's USING clause sees the OLD row and WITH CHECK sees the NEW row.
-- There is no way to write "old.status = X and new.status = Y" as a pair
-- within policy expressions, since neither clause has both row versions at
-- once. This is the identical limitation that made the content-edit lock
-- in 0008 (invoices_restrict_content_edit_when_submitted) a trigger rather
-- than a policy. invoices_update_owner is left as-is (it remains a
-- correct, necessary coarse gate: agent must own the row, and neither old
-- nor new status can be approved/paid); this trigger adds the precise
-- pairwise restriction on top of it.
--
-- Scope/exemption: this only restricts the OWNER path. It keys off
-- "NEW.agent_id = current_agent_id()", i.e. "is this literally my own
-- invoice", not "do I hold is_invoice_approver anywhere". That distinction
-- matters: someone who is both owner and approver (Edwin) must still be
-- blocked from self-rejecting via this path, exactly like anyone else who
-- owns the invoice. Real approver actions on OTHER people's invoices never
-- trigger this at all, since NEW.agent_id will not equal the approver's
-- own current_agent_id() in that case (or the approver has no agent_id at
-- all, in which case the comparison is never true either).

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')
     )
  then
    raise exception 'Not an allowed status transition for the invoice owner: % -> %', old.status, new.status;
  end if;

  return new;
end;
$func$;

create trigger trg_invoices_restrict_owner_status_transitions
  before update on public.invoices
  for each row
  execute function public.invoices_restrict_owner_status_transitions();