-- Migration: 00032_fetch_review_details
-- Description: Add RPC to fetch review detail data in a single call
-- Author: Agent
-- Date: 2026-01-19

-- ============================================
-- FETCH REVIEW DETAILS FUNCTION
-- ============================================
-- Returns approved responses, approved comments (optional),
-- and whether the viewer has already responded.

CREATE OR REPLACE FUNCTION fetch_review_details(
  p_review_id UUID,
  p_user_id UUID DEFAULT NULL,
  p_include_comments BOOLEAN DEFAULT TRUE
)
RETURNS JSON AS $$
  SELECT json_build_object(
    'responses', COALESCE(
      (
        SELECT json_agg(row_to_json(response_rows) ORDER BY published_at DESC)
        FROM (
          SELECT
            id,
            review_id,
            responder_id,
            content,
            status,
            verified_as_subject,
            created_at,
            updated_at,
            published_at
          FROM responses
          WHERE review_id = p_review_id
            AND status = 'approved'
        ) response_rows
      ),
      '[]'::json
    ),
    'comments', CASE
      WHEN p_include_comments THEN COALESCE(
        (
          SELECT json_agg(row_to_json(comment_rows) ORDER BY published_at DESC)
          FROM (
            SELECT
              id,
              review_id,
              author_id,
              parent_comment_id,
              content,
              status,
              is_subject_response,
              created_at,
              updated_at,
              published_at
            FROM comments
            WHERE review_id = p_review_id
              AND status = 'approved'
          ) comment_rows
        ),
        '[]'::json
      )
      ELSE '[]'::json
    END,
    'has_responded', CASE
      WHEN p_user_id IS NULL THEN FALSE
      ELSE EXISTS (
        SELECT 1
        FROM responses
        WHERE review_id = p_review_id
          AND responder_id = p_user_id
      )
    END
  );
$$ LANGUAGE SQL STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

GRANT EXECUTE ON FUNCTION fetch_review_details(UUID, UUID, BOOLEAN) TO authenticated;

COMMENT ON FUNCTION fetch_review_details(UUID, UUID, BOOLEAN) IS
'Fetches review responses, optional comments, and whether the viewer has responded.';
