Security, Permissions & Disaster Recovery.

-- Enforce strict tenant isolation & shift supervisor insert rights
ALTER TABLE greige_production_runs ENABLE ROW LEVEL SECURITY;

-- 1. Inactive employees have zero access regardless of role
CREATE POLICY "deny_inactive_users"
ON greige_production_runs
AS RESTRICTIVE
TO authenticated
USING (
  (SELECT status FROM auth_profiles WHERE id = auth.uid()) = 'active'
);

-- 2. Shift supervisors can only insert and read data for their assigned shift
CREATE POLICY "supervisors_active_shift_only"
ON greige_production_runs
FOR INSERT
TO authenticated
WITH CHECK (
  auth.jwt() ->> 'role' = 'shift_supervisor'
  AND shift_id = (SELECT current_active_shift FROM factory_state LIMIT 1)
  AND logged_by = auth.uid()
);

-- 3. Only the finance director can approve and close the ledger
CREATE POLICY "finance_director_close_ledger"
ON gl_accounting_closures
FOR ALL
TO authenticated
USING (
  auth.jwt() ->> 'role' = 'finance_director'
  AND (SELECT two_factor_verified FROM auth_profiles WHERE id = auth.uid()) = true
);