-- Create a function to check if the current user (Clerk or Supabase) owns a profile ID
CREATE OR REPLACE FUNCTION public.check_user_owns_profile(profile_id uuid)
RETURNS boolean AS $$
BEGIN
  RETURN (
    -- Case 1: Clerk user ID matches profiles.clerk_user_id
    EXISTS (
      SELECT 1 FROM profiles 
      WHERE id = profile_id 
      AND clerk_user_id = get_clerk_user_id() 
      AND clerk_user_id IS NOT NULL
    )
    OR
    -- Case 2: Supabase auth.uid() matches profile_id
    (auth.uid() = profile_id AND auth.uid() IS NOT NULL)
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Update user_consents policies
DROP POLICY IF EXISTS "Users can view their own consents" ON user_consents;
CREATE POLICY "Users can view their own consents" ON user_consents
FOR SELECT TO public USING (check_user_owns_profile(user_id) OR is_admin());

DROP POLICY IF EXISTS "Users can insert their own consents" ON user_consents;
CREATE POLICY "Users can insert their own consents" ON user_consents
FOR INSERT TO public WITH CHECK (check_user_owns_profile(user_id));

DROP POLICY IF EXISTS "Users can update their own consents" ON user_consents;
CREATE POLICY "Users can update their own consents" ON user_consents
FOR UPDATE TO public USING (check_user_owns_profile(user_id));

-- Update audit_logs policies
DROP POLICY IF EXISTS "Users can view their own audit logs" ON audit_logs;
CREATE POLICY "Users can view their own audit logs" ON audit_logs
FOR SELECT TO public USING (check_user_owns_profile(user_id) OR is_admin());

-- Allow public to insert audit logs (we'll filter in the function if needed)
DROP POLICY IF EXISTS "Only service_role inserts audit logs" ON audit_logs;
CREATE POLICY "Everyone can insert audit logs" ON audit_logs
FOR INSERT TO public WITH CHECK (true);

-- Update data_export_requests policies
DROP POLICY IF EXISTS "Users can view their own export requests" ON data_export_requests;
CREATE POLICY "Users can view their own export requests" ON data_export_requests
FOR SELECT TO public USING (check_user_owns_profile(user_id) OR is_admin());

DROP POLICY IF EXISTS "Users can create their own export requests" ON data_export_requests;
CREATE POLICY "Users can create their own export requests" ON data_export_requests
FOR INSERT TO public WITH CHECK (check_user_owns_profile(user_id));

-- Update account_deletion_requests policies
DROP POLICY IF EXISTS "Users can view their own deletion requests" ON account_deletion_requests;
CREATE POLICY "Users can view their own deletion requests" ON account_deletion_requests
FOR SELECT TO public USING (check_user_owns_profile(user_id) OR is_admin());

DROP POLICY IF EXISTS "Users can create their own deletion requests" ON account_deletion_requests;
CREATE POLICY "Users can create their own deletion requests" ON account_deletion_requests
FOR INSERT TO public WITH CHECK (check_user_owns_profile(user_id));

DROP POLICY IF EXISTS "Users can update their own deletion requests" ON account_deletion_requests;
CREATE POLICY "Users can update their own deletion requests" ON account_deletion_requests
FOR UPDATE TO public USING (check_user_owns_profile(user_id));
