DO $$
DECLARE
  m text[][] := ARRAY[
    ['approve_distribution','profit-distribution'],
    ['create_distribution_proposal','profit-distribution'],
    ['execute_distribution','profit-distribution'],
    ['create_expense','expenses'],
    ['pay_bill','bills'],
    ['post_bill','bills'],
    ['post_journal_entry','accounting'],
    ['next_number','dashboard'],
    ['record_owner_investment','owners'],
    ['record_owner_withdrawal','owners'],
    ['calculate_gross_profit','reports'],
    ['calculate_monthly_profit','reports'],
    ['calculate_safe_distribution','reports']
  ];
  i int;
  f record;
  newdef text;
BEGIN
  FOR i IN 1 .. array_length(m, 1) LOOP
    FOR f IN
      SELECT p.oid FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE n.nspname = 'public' AND p.proname = m[i][1] AND p.prokind = 'f'
    LOOP
      newdef := regexp_replace(
        pg_get_functiondef(f.oid),
        E'\nBEGIN\n',
        E'\nBEGIN\n  PERFORM public.require_access(' || quote_literal(m[i][2]) || E');\n'
      );
      EXECUTE newdef;
    END LOOP;
  END LOOP;
END $$;

-- keep execution grants consistent after replacement
DO $$
DECLARE
  f record;
  allowed text[] := ARRAY[
    'allocate_payment','approve_distribution','approve_expense_claim','approve_invoice',
    'approve_payroll_run','calculate_gross_profit','calculate_monthly_profit',
    'calculate_safe_distribution','create_distribution_proposal','create_expense',
    'create_invoice_from_booking','create_transfer','execute_distribution',
    'generate_payroll_run','next_number','pay_bill','pay_payroll_run','post_bill',
    'post_invoice','post_journal_entry','record_booking_cost','record_customer_payment',
    'record_owner_investment','record_owner_withdrawal','record_staff_advance',
    'reject_expense_claim','reject_invoice','reverse_journal_entry','submit_expense_claim',
    'submit_invoice_for_approval','void_invoice','is_admin','has_role','can_access',
    'can_edit','current_portal_customer_id'
  ];
BEGIN
  FOR f IN
    SELECT p.oid::regprocedure AS sig, p.proname
    FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public' AND p.prokind = 'f'
  LOOP
    EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC', f.sig);
    EXECUTE format('REVOKE ALL ON FUNCTION %s FROM anon', f.sig);
    IF f.proname = ANY (allowed) THEN
      EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO authenticated', f.sig);
    ELSE
      EXECUTE format('REVOKE ALL ON FUNCTION %s FROM authenticated', f.sig);
    END IF;
    EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO service_role', f.sig);
  END LOOP;
END $$;