-- Fixes a real, currently-live gap in invoice_profiles' RLS (0012):
-- ONLY self policies exist (select/insert/update all gated on
-- `agent_id = current_agent_id()`) — there is no admin policy at all,
-- and critically, `invoice_profiles_update_own`'s blanket row-level
-- check lets an agent write to EVERY column on their own row, including
-- `hourly_rate_usd` and `invoice_prefix` — which invoiceLogic.js
-- (computeMonthlyBase, :377-379) reads as a direct PAY-RATE OVERRIDE
-- that takes precedence over rate_history/invoice_settings. As shipped,
-- any agent could set their own hourly rate via a direct API call and
-- have it flow straight into their own invoice generation. Confirmed
-- live: the table currently has 0 rows, so this hasn't been exploited,
-- but the door has been open since 0012.
--
-- Fix, in two parts (RLS alone can't do column-level splits):
--   1. Add admin/rate_and_schedule policies (select/insert/update) —
--      "same category as rate_history" per explicit instruction, not
--      'invoicing'. This is also what the new admin rate-override form
--      needs to actually read/write any agent's row.
--   2. A BEFORE INSERT OR UPDATE trigger that blocks hourly_rate_usd/
--      invoice_prefix from being set or changed by anyone WITHOUT
--      has_permission('rate_and_schedule', need_write => true) —
--      independent of which RLS policy let the statement through in
--      the first place. This is what actually closes the hole: an
--      agent can still self-service their own address/bank/personal
--      fields (existing self policies untouched), but can no longer
--      touch the two rate-override fields at all, even via a crafted
--      direct request. Same trigger-based column-guard pattern already
--      used by invoices_restrict_content_edit_when_submitted (0008/0018).
--
-- SELECT is gated the same as UPDATE (has_permission('rate_and_schedule',
-- need_write => true)), not left fully open like rate_history's own
-- read policy — deliberately more conservative, since this table also
-- holds personal PII (address, bank details, SSS/Pag-IBIG numbers) that
-- rate_history doesn't carry at all.

create or replace function public.invoice_profiles_restrict_rate_fields()
returns trigger language plpgsql as $$
begin
  if not public.has_permission('rate_and_schedule', need_write => true) then
    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 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 can change hourly_rate_usd/invoice_prefix';
      end if;
    end if;
  end if;
  return new;
end;
$$;

drop trigger if exists trg_invoice_profiles_restrict_rate_fields on public.invoice_profiles;
create trigger trg_invoice_profiles_restrict_rate_fields
  before insert or update on public.invoice_profiles
  for each row execute function public.invoice_profiles_restrict_rate_fields();

create policy "invoice_profiles_select_admin"
on public.invoice_profiles
for select
to authenticated
using (public.has_permission('rate_and_schedule', need_write => true));

create policy "invoice_profiles_insert_admin"
on public.invoice_profiles
for insert
to authenticated
with check (public.has_permission('rate_and_schedule', need_write => true));

create policy "invoice_profiles_update_admin"
on public.invoice_profiles
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));

do $migration_guard$
declare
  policy_count int;
  trigger_exists boolean;
begin
  select count(*) into policy_count from pg_policies where schemaname = 'public' and tablename = 'invoice_profiles';
  if policy_count <> 6 then
    raise exception 'Aborting 0030: expected 6 policies on invoice_profiles (3 self + 3 admin), found %', policy_count;
  end if;
  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 0030: trg_invoice_profiles_restrict_rate_fields trigger was not created';
  end if;
end $migration_guard$;