-- Migration: 00027_enforce_opposite_gender_reviews
-- Description: Add RLS constraint enforcing author.gender != subject_gender for reviews
-- Date: 2025-12-23
-- 
-- This enforces the gender-segregation rule at the database level:
-- Women can only review men, men can only review women.

-- ============================================
-- HELPER FUNCTION: CHECK AUTHOR GENDER MATCHES SUBJECT
-- ============================================
-- Returns true if the author's gender matches the subject's gender
-- (which would violate the opposite-gender rule)
CREATE OR REPLACE FUNCTION author_gender_matches_subject(
    p_author_id UUID,
    p_subject_gender gender
) RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (
        SELECT 1 FROM users 
        WHERE id = p_author_id 
        AND gender = p_subject_gender
    );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

-- ============================================
-- UPDATE INSERT POLICY
-- ============================================
-- Replace the existing INSERT policy to include opposite-gender check
-- Note: This policy may have already been created by migration 00025, so drop it first
DROP POLICY IF EXISTS "Active ID-verified users can create reviews" ON reviews;
DROP POLICY IF EXISTS "Active ID-verified users can create opposite-gender reviews" ON reviews;

CREATE POLICY "Active ID-verified users can create opposite-gender reviews"
  ON reviews FOR INSERT
  WITH CHECK (
    auth.uid() = author_id
    AND is_active_id_verified_user()
    AND NOT author_gender_matches_subject(author_id, subject_gender)
  );

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON FUNCTION author_gender_matches_subject(UUID, gender) IS 
  'Returns true if the author''s gender matches the subject gender (violates opposite-gender rule)';

