-- Migration: 00032_update_search_reviews_return
-- Description: Return full review rows from search_reviews RPC
-- Author: Codex
-- Date: 2025-01-15

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,
  author_id UUID,
  subject_first_name TEXT,
  subject_last_name TEXT,
  subject_age INTEGER,
  subject_gender gender,
  content TEXT,
  rating INTEGER,
  flag review_flag,
  photo_urls TEXT[],
  city TEXT,
  state TEXT,
  country TEXT,
  slug TEXT,
  is_indexable BOOLEAN,
  view_count INTEGER,
  created_at TIMESTAMPTZ,
  updated_at TIMESTAMPTZ,
  published_at TIMESTAMPTZ,
  status review_status
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    r.id,
    r.author_id,
    r.subject_first_name,
    r.subject_last_name,
    r.subject_age,
    r.subject_gender,
    r.content,
    r.rating,
    r.flag,
    r.photo_urls,
    r.city,
    r.state,
    r.country,
    r.slug,
    r.is_indexable,
    r.view_count,
    r.created_at,
    r.updated_at,
    r.published_at,
    r.status
  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
    ts_rank(r.search_vector, websearch_to_tsquery('english', search_query)) DESC,
    r.published_at DESC
  LIMIT result_limit
  OFFSET result_offset;
END;
$$ LANGUAGE plpgsql STABLE;
