-- Migration: 00008_fix_get_feed_reviews_gender
-- Description: Fix get_feed_reviews() to use correct gender enum values
-- Author: Dirt
-- Date: 2025-12-16

-- The `gender` enum is: 'man', 'woman'
-- Previous versions of this function compared against 'male'/'female', which is invalid.

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
  -- Show reviews about the "opposite" gender:
  -- Women see reviews about men, men see reviews about 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;


