DO $$
DECLARE f record; d text;
BEGIN
  FOR f IN
    SELECT p.oid, p.proname FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname='public' AND p.proname IN ('calculate_gross_profit','calculate_monthly_profit','calculate_safe_distribution','next_number')
  LOOP
    d := pg_get_functiondef(f.oid);
    IF f.proname = 'next_number' THEN
      d := replace(d, 'PERFORM public.require_access(''dashboard'');',
        'IF auth.uid() IS NULL THEN RAISE EXCEPTION ''Authentication required''; END IF;');
    ELSE
      d := replace(d, 'PERFORM public.require_access(''reports'');',
        'IF NOT public.can_access(''reports'') THEN RAISE EXCEPTION ''You do not have permission to view reports''; END IF;');
    END IF;
    EXECUTE d;
  END LOOP;
END $$;

DO $$
DECLARE f record;
  allowed text[] := ARRAY['calculate_gross_profit','calculate_monthly_profit','calculate_safe_distribution','next_number'];
BEGIN
  FOR f IN SELECT p.oid::regprocedure sig, p.proname FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace
    WHERE n.nspname='public' AND p.proname = ANY(allowed)
  LOOP
    EXECUTE format('REVOKE ALL ON FUNCTION %s FROM PUBLIC', f.sig);
    EXECUTE format('REVOKE ALL ON FUNCTION %s FROM anon', f.sig);
    EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO authenticated', f.sig);
    EXECUTE format('GRANT EXECUTE ON FUNCTION %s TO service_role', f.sig);
  END LOOP;
END $$;