-- ============ module-scoped RLS ============
DO $$
DECLARE
  r record;
  mapping text[][] := ARRAY[
    ['customers','customers'],
    ['vendors','vendors'],
    ['bills','bills'],
    ['bill_items','bills'],
    ['bill_payments','bills'],
    ['expenses','expenses'],
    ['expense_categories','expenses'],
    ['owners','owners'],
    ['owner_investments','owners'],
    ['owner_withdrawals','owners'],
    ['profit_distributions','profit-distribution'],
    ['profit_distribution_items','profit-distribution'],
    ['sms_logs','sms'],
    ['sms_templates','sms'],
    ['bank_accounts','cash-bank'],
    ['bank_transactions','cash-bank'],
    ['bank_reconciliations','cash-bank'],
    ['cash_accounts','cash-bank'],
    ['bookings','bookings'],
    ['projects','projects'],
    ['jobs','projects'],
    ['rooms','rooms'],
    ['studios','studios'],
    ['equipment','studios'],
    ['packages','services'],
    ['services','services'],
    ['products','products'],
    ['leave_types','leave']
  ];
  i int;
  tbl text;
  mdl text;
BEGIN
  FOR i IN 1 .. array_length(mapping, 1) LOOP
    tbl := mapping[i][1];
    mdl := mapping[i][2];

    FOR r IN
      SELECT policyname FROM pg_policies
      WHERE schemaname = 'public' AND tablename = tbl
        AND (qual = 'true' OR with_check = 'true')
    LOOP
      EXECUTE format('DROP POLICY %I ON public.%I', r.policyname, tbl);
    END LOOP;

    EXECUTE format(
      'CREATE POLICY %I ON public.%I FOR SELECT TO authenticated USING (public.can_access(%L))',
      tbl || '_module_select', tbl, mdl);
    EXECUTE format(
      'CREATE POLICY %I ON public.%I FOR INSERT TO authenticated WITH CHECK (public.can_edit(%L))',
      tbl || '_module_insert', tbl, mdl);
    EXECUTE format(
      'CREATE POLICY %I ON public.%I FOR UPDATE TO authenticated USING (public.can_edit(%L)) WITH CHECK (public.can_edit(%L))',
      tbl || '_module_update', tbl, mdl, mdl);
    EXECUTE format(
      'CREATE POLICY %I ON public.%I FOR DELETE TO authenticated USING (public.can_edit(%L))',
      tbl || '_module_delete', tbl, mdl);
  END LOOP;
END $$;

-- ============ settings: read for signed-in, write admin only ============
DROP POLICY IF EXISTS "settings all" ON public.settings;
CREATE POLICY settings_read ON public.settings
  FOR SELECT TO authenticated USING (true);
CREATE POLICY settings_admin_write ON public.settings
  FOR ALL TO authenticated USING (public.is_admin()) WITH CHECK (public.is_admin());

-- ============ audit logs: non-forgeable inserts, scoped reads ============
DROP POLICY IF EXISTS "audit insert" ON public.audit_logs;
DROP POLICY IF EXISTS "audit read" ON public.audit_logs;
CREATE POLICY audit_logs_insert_self ON public.audit_logs
  FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid());
CREATE POLICY audit_logs_read ON public.audit_logs
  FOR SELECT TO authenticated USING (public.can_access('audit-log'));

-- ============ branding storage bucket: admin-only writes ============
DROP POLICY IF EXISTS branding_insert ON storage.objects;
DROP POLICY IF EXISTS branding_update ON storage.objects;
DROP POLICY IF EXISTS branding_delete ON storage.objects;
CREATE POLICY branding_insert ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'branding' AND public.is_admin());
CREATE POLICY branding_update ON storage.objects
  FOR UPDATE TO authenticated
  USING (bucket_id = 'branding' AND public.is_admin())
  WITH CHECK (bucket_id = 'branding' AND public.is_admin());
CREATE POLICY branding_delete ON storage.objects
  FOR DELETE TO authenticated
  USING (bucket_id = 'branding' AND public.is_admin());

-- ============ lock down SECURITY DEFINER function execution ============
DO $$
DECLARE
  f record;
  allowed_authenticated 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_authenticated) 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 $$;