-- Self-service agent invoice profile page (replaces the admin-only
-- /settings/invoice-profiles table for editing) needs agents to be able
-- to set their OWN hourly_rate_usd / invoice_prefix, with NO approval
-- step. Live-tested against a disposable non-admin test user before
-- writing this migration (created, probed, deleted — no real user or
-- data touched) and confirmed: invoice_profiles_update_own (0012)
-- already lets a self-user write their own row in general, but the
-- 0030 trigger unconditionally blocks hourly_rate_usd/invoice_prefix
-- changes from anyone without rate_and_schedule write permission --
-- including the row's own agent. That trigger was working exactly as
-- designed; this migration deliberately narrows it.
--
-- *** THIS IS A DELIBERATE, EXPLICITLY CONFIRMED PRODUCT DECISION, NOT
-- *** A SECURITY REGRESSION OR OVERSIGHT. The real risk this reopens —
-- *** an agent can set their own pay rate and have it flow straight
-- *** into their own invoice generation with no approval/review step
-- *** at all, the EXACT hole 0030 was originally written to close --
-- *** was explicitly raised and explicitly accepted. Do not "fix" this
-- *** later by re-adding an approval gate or reverting to admin-only
-- *** without confirming that's an intentional policy change, not a
-- *** rediscovery of 0030's original concern. See the matching comment
-- *** in src/app/settings/invoice-profile/page.js.
--
-- What stays the same: RLS itself is untouched (invoice_profiles_update_own
-- /_insert_own still scope every self-write to `agent_id =
-- current_agent_id()` — an agent still cannot touch anyone else's row,
-- confirmed live in the same test), and billing admins
-- (rate_and_schedule write) keep full access to every agent's row
-- exactly as before. Only the trigger's rate-field gate changes: it now
-- also allows the row's own agent through, instead of ONLY
-- rate_and_schedule holders.
create or replace function public.invoice_profiles_restrict_rate_fields()
returns trigger language plpgsql as $$
begin
  if public.has_permission('rate_and_schedule', need_write => true) then
    return new;
  end if;

  -- Self-service exception (this migration): the row's own agent may
  -- set their own hourly_rate_usd/invoice_prefix. RLS already confines
  -- this branch to the actor's own row (own-row UPDATE/INSERT policies,
  -- 0012) — this check just mirrors that at the trigger layer too,
  -- defense-in-depth, same pattern as the invoices content-lock trigger.
  if new.agent_id = public.current_agent_id() then
    return new;
  end if;

  if tg_op = 'INSERT' then
    if new.hourly_rate_usd is not null or coalesce(new.invoice_prefix, '') <> '' then
      raise exception 'Only rate_and_schedule write access (or the agent themself) can set hourly_rate_usd/invoice_prefix';
    end if;
  elsif tg_op = 'UPDATE' then
    if new.hourly_rate_usd is distinct from old.hourly_rate_usd
       or new.invoice_prefix is distinct from old.invoice_prefix then
      raise exception 'Only rate_and_schedule write access (or the agent themself) can change hourly_rate_usd/invoice_prefix';
    end if;
  end if;
  return new;
end;
$$;

do $migration_guard$
declare
  trigger_exists boolean;
begin
  select exists (
    select 1 from pg_trigger where tgname = 'trg_invoice_profiles_restrict_rate_fields'
  ) into trigger_exists;
  if not trigger_exists then
    raise exception 'Aborting 0035: trg_invoice_profiles_restrict_rate_fields trigger is missing (expected to already exist from 0030)';
  end if;
end $migration_guard$;