-- Create helper function to check AAL2 (Authenticator Assurance Level 2)
CREATE OR REPLACE FUNCTION public.has_aal2()
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT COALESCE((auth.jwt()->>'aal')::text = 'aal2', false);
$$;

-- Update admin_settings policies to require MFA
DROP POLICY IF EXISTS "Superusers can manage settings" ON public.admin_settings;
CREATE POLICY "Superusers can manage settings with MFA" 
ON public.admin_settings
FOR ALL
USING (
  is_superuser(auth.uid())
  AND public.has_aal2()
);

-- Update user_roles policies to require MFA for role management
DROP POLICY IF EXISTS "Admins can insert user roles" ON public.user_roles;
CREATE POLICY "Admins can insert user roles with MFA" 
ON public.user_roles
FOR INSERT
WITH CHECK (
  (has_role(auth.uid(), 'admin'::app_role) OR is_superuser(auth.uid()))
  AND public.has_aal2()
);

DROP POLICY IF EXISTS "Admins can delete user roles" ON public.user_roles;
CREATE POLICY "Admins can delete user roles with MFA" 
ON public.user_roles
FOR DELETE
USING (
  (has_role(auth.uid(), 'admin'::app_role) OR is_superuser(auth.uid()))
  AND public.has_aal2()
);

-- Update content_submissions moderation policies to require MFA
DROP POLICY IF EXISTS "Moderators can update content submissions" ON public.content_submissions;
CREATE POLICY "Moderators can update submissions with MFA" 
ON public.content_submissions
FOR UPDATE
USING (
  is_moderator(auth.uid())
  AND public.has_aal2()
);

DROP POLICY IF EXISTS "Moderators can delete content submissions" ON public.content_submissions;
CREATE POLICY "Moderators can delete submissions with MFA" 
ON public.content_submissions
FOR DELETE
USING (
  is_moderator(auth.uid())
  AND public.has_aal2()
);

-- Update submission_items policies to require MFA
DROP POLICY IF EXISTS "Moderators can update submission items" ON public.submission_items;
CREATE POLICY "Moderators can update submission items with MFA" 
ON public.submission_items
FOR UPDATE
USING (
  is_moderator(auth.uid())
  AND public.has_aal2()
);

-- Update reports policies to require MFA
DROP POLICY IF EXISTS "Moderators can update reports" ON public.reports;
CREATE POLICY "Moderators can update reports with MFA" 
ON public.reports
FOR UPDATE
USING (
  is_moderator(auth.uid())
  AND public.has_aal2()
);

-- Update admin_audit_log policies to require MFA
DROP POLICY IF EXISTS "Admins can insert audit log" ON public.admin_audit_log;
CREATE POLICY "Admins can insert audit log with MFA" 
ON public.admin_audit_log
FOR INSERT
WITH CHECK (
  is_moderator(auth.uid())
  AND public.has_aal2()
);

-- Update profiles policies for sensitive operations
DROP POLICY IF EXISTS "Admins can update any profile" ON public.profiles;
CREATE POLICY "Admins can update any profile with MFA" 
ON public.profiles
FOR UPDATE
USING (
  (auth.uid() = user_id) OR 
  ((has_role(auth.uid(), 'admin'::app_role) OR is_superuser(auth.uid())) AND public.has_aal2())
);