-- Migration: 00025_remove_nonbinary_gender_options
-- Description: Remove non_binary and prefer_not_to_say from gender enum
-- Date: 2025-12-23
-- 
-- Gender is now determined/verified during ID verification and cannot be
-- changed by users. Only 'man' and 'woman' are valid options.

-- ============================================
-- PRE-FLIGHT CHECK: Ensure no users have removed gender values
-- ============================================
DO $$
DECLARE
  invalid_user_count INTEGER;
  invalid_review_count INTEGER;
BEGIN
  -- Check for users with non_binary or prefer_not_to_say
  SELECT COUNT(*) INTO invalid_user_count
  FROM users
  WHERE gender IN ('non_binary', 'prefer_not_to_say');
  
  IF invalid_user_count > 0 THEN
    RAISE EXCEPTION 'Cannot migrate: % user(s) have gender set to non_binary or prefer_not_to_say. Please manually review and update these users before running this migration.', invalid_user_count;
  END IF;
  
  -- Check for reviews with non_binary or prefer_not_to_say subject_gender
  SELECT COUNT(*) INTO invalid_review_count
  FROM reviews
  WHERE subject_gender IN ('non_binary', 'prefer_not_to_say');
  
  IF invalid_review_count > 0 THEN
    RAISE EXCEPTION 'Cannot migrate: % review(s) have subject_gender set to non_binary or prefer_not_to_say. Please manually review and update these reviews before running this migration.', invalid_review_count;
  END IF;
END $$;

-- ============================================
-- DROP DEPENDENCIES ON gender TYPE
-- ============================================
-- Must drop functions, views, and policies before altering column type

-- Drop functions that use gender type
DROP FUNCTION IF EXISTS search_reviews(TEXT, gender, TEXT, TEXT, INTEGER, INTEGER) CASCADE;
DROP FUNCTION IF EXISTS get_feed_reviews(gender, INTEGER, INTEGER) CASCADE;
DROP FUNCTION IF EXISTS can_view_subject_gender(gender) CASCADE;
DROP FUNCTION IF EXISTS can_browse_subject_gender(gender) CASCADE;
DROP FUNCTION IF EXISTS author_gender_matches_subject(UUID, gender) CASCADE;

-- Drop views that depend on subject_gender (from migration 00023)
DROP VIEW IF EXISTS reviews_public CASCADE;
DROP VIEW IF EXISTS reviews_author CASCADE;

-- Drop policies that depend on subject_gender
DROP POLICY IF EXISTS "Email-verified users can browse gender-gated approved reviews" ON reviews;
DROP POLICY IF EXISTS "Active ID-verified users can create opposite-gender reviews" ON reviews;

-- ============================================
-- CREATE NEW ENUM TYPE
-- ============================================
-- PostgreSQL doesn't support removing enum values, so we create a new enum
CREATE TYPE gender_v2 AS ENUM ('man', 'woman');

-- ============================================
-- ALTER COLUMNS TO USE NEW ENUM TYPE
-- ============================================

-- Users table
ALTER TABLE users 
  ALTER COLUMN gender TYPE gender_v2 
  USING gender::text::gender_v2;

-- Reviews table
ALTER TABLE reviews 
  ALTER COLUMN subject_gender TYPE gender_v2 
  USING subject_gender::text::gender_v2;

-- ============================================
-- DROP OLD ENUM AND RENAME NEW ONE
-- ============================================
DROP TYPE gender;
ALTER TYPE gender_v2 RENAME TO gender;

-- ============================================
-- RECREATE VIEWS THAT DEPEND ON subject_gender
-- ============================================
-- Recreate views (from migration 00023)

-- Reviews public view
CREATE VIEW reviews_public AS
SELECT
  id,
  author_id,
  subject_first_name,
  subject_last_name,
  subject_age,
  subject_gender,
  content,
  rating,
  flag,
  city,
  state,
  country,
  slug,
  is_indexable,
  view_count,
  created_at,
  updated_at,
  published_at,
  status
FROM reviews;

GRANT SELECT ON reviews_public TO anon, authenticated;

COMMENT ON VIEW reviews_public IS 'Public-safe view of reviews that excludes internal moderation fields (auto_flagged, flagged_keywords, rejection_reason, report_count)';

-- Reviews author view
CREATE VIEW reviews_author AS
SELECT
  id,
  author_id,
  subject_first_name,
  subject_last_name,
  subject_age,
  subject_gender,
  content,
  rating,
  flag,
  city,
  state,
  country,
  slug,
  is_indexable,
  view_count,
  created_at,
  updated_at,
  published_at,
  status,
  rejection_reason  -- Authors should see this for their own rejected reviews
FROM reviews;

GRANT SELECT ON reviews_author TO authenticated;

COMMENT ON VIEW reviews_author IS 'View for authors to see their own reviews including rejection_reason';

-- ============================================
-- RECREATE FUNCTIONS THAT USE gender TYPE
-- ============================================
-- Functions must be recreated BEFORE policies that depend on them
-- Recreate functions (from migrations 00007, 00012, 00022, 00027)

-- search_reviews function (from 00007)
CREATE OR REPLACE FUNCTION search_reviews(
  search_query TEXT,
  filter_gender gender DEFAULT NULL,
  filter_city TEXT DEFAULT NULL,
  filter_state TEXT DEFAULT NULL,
  result_limit INTEGER DEFAULT 20,
  result_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
  id UUID,
  subject_first_name TEXT,
  subject_last_name TEXT,
  subject_age INTEGER,
  subject_gender gender,
  content TEXT,
  rating INTEGER,
  city TEXT,
  state TEXT,
  view_count INTEGER,
  published_at TIMESTAMPTZ,
  relevance REAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    r.id,
    r.subject_first_name,
    r.subject_last_name,
    r.subject_age,
    r.subject_gender,
    r.content,
    r.rating,
    r.city,
    r.state,
    r.view_count,
    r.published_at,
    ts_rank(r.search_vector, websearch_to_tsquery('english', search_query)) AS relevance
  FROM reviews r
  WHERE 
    r.status = 'approved'
    AND r.is_indexable = TRUE
    AND r.search_vector @@ websearch_to_tsquery('english', search_query)
    AND (filter_gender IS NULL OR r.subject_gender = filter_gender)
    AND (filter_city IS NULL OR r.city ILIKE filter_city)
    AND (filter_state IS NULL OR r.state ILIKE filter_state)
  ORDER BY relevance DESC, r.published_at DESC
  LIMIT result_limit
  OFFSET result_offset;
END;
$$ LANGUAGE plpgsql STABLE;

-- get_feed_reviews function (from 00007)
CREATE OR REPLACE FUNCTION get_feed_reviews(
  viewer_gender gender,
  page_limit INTEGER DEFAULT 20,
  page_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
  id UUID,
  subject_first_name TEXT,
  subject_last_name TEXT,
  subject_age INTEGER,
  subject_gender gender,
  content TEXT,
  rating INTEGER,
  city TEXT,
  state TEXT,
  view_count INTEGER,
  published_at TIMESTAMPTZ,
  slug TEXT
) AS $$
DECLARE
  target_gender gender;
BEGIN
  -- Determine which gender's reviews to show
  -- Returns opposite gender: women see men, men see women
  target_gender := CASE
    WHEN viewer_gender = 'woman' THEN 'man'
    WHEN viewer_gender = 'man' THEN 'woman'
    ELSE NULL
  END;
  
  RETURN QUERY
  SELECT 
    r.id,
    r.subject_first_name,
    r.subject_last_name,
    r.subject_age,
    r.subject_gender,
    r.content,
    r.rating,
    r.city,
    r.state,
    r.view_count,
    r.published_at,
    r.slug
  FROM reviews r
  WHERE 
    r.status = 'approved'
    AND r.is_indexable = TRUE
    AND (target_gender IS NULL OR r.subject_gender = target_gender)
  ORDER BY r.published_at DESC
  LIMIT page_limit
  OFFSET page_offset;
END;
$$ LANGUAGE plpgsql STABLE;

-- can_view_subject_gender function (from 00012)
CREATE OR REPLACE FUNCTION can_view_subject_gender(subject_gender gender)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM users u
    WHERE u.id = auth.uid()
      AND u.is_banned = FALSE
      AND u.id_verified = TRUE
      AND u.access_status = 'active'
      AND (
        (u.gender = 'man' AND subject_gender = 'woman')
        OR (u.gender = 'woman' AND subject_gender = 'man')
      )
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

-- can_browse_subject_gender function (from 00022)
CREATE OR REPLACE FUNCTION can_browse_subject_gender(subject_gender gender)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM users u
    WHERE u.id = auth.uid()
      AND u.is_banned = FALSE
      AND u.email_verified = TRUE
      AND (
        (u.gender = 'man' AND subject_gender = 'woman')
        OR (u.gender = 'woman' AND subject_gender = 'man')
      )
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

-- author_gender_matches_subject function (from 00027)
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;

-- Grant execute permissions (from 00012, 00022)
GRANT EXECUTE ON FUNCTION search_reviews(TEXT, gender, TEXT, TEXT, INTEGER, INTEGER) TO authenticated;
GRANT EXECUTE ON FUNCTION get_feed_reviews(gender, INTEGER, INTEGER) TO authenticated;

-- ============================================
-- UPDATE FUNCTION COMMENTS
-- ============================================
COMMENT ON FUNCTION search_reviews(TEXT, gender, TEXT, TEXT, INTEGER, INTEGER) IS 'Full-text search for reviews';
COMMENT ON FUNCTION get_feed_reviews(gender, INTEGER, INTEGER) IS 'Get reviews for the feed, filtered by gender. For man viewers shows woman subjects and vice versa.';
COMMENT ON FUNCTION can_view_subject_gender(gender) IS 'Returns true if the current user (man/woman) can view reviews of the given subject gender (opposite gender).';
COMMENT ON FUNCTION can_browse_subject_gender(gender) IS 'Returns true if email-verified user can browse reviews of the given subject gender (opposite gender).';
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)';

-- ============================================
-- RECREATE POLICIES THAT DEPEND ON subject_gender
-- ============================================
-- Recreate the browse policy (from migration 00022)
CREATE POLICY "Email-verified users can browse gender-gated approved reviews"
  ON reviews FOR SELECT
  USING (
    status = 'approved'
    AND is_indexable = TRUE
    AND can_browse_subject_gender(subject_gender)
  );

-- Recreate the insert policy (from migration 00027)
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)
  );

