-- Migration: 00022_tiered_access_browse
-- Description: Implement tiered access: email-verified users can browse (read) reviews;
--              ID-verified users get full access (respond, write reviews).
--              App-level blur handles content restriction for non-ID-verified users.
-- Date: 2025-12-22

-- ============================================
-- HELPER FUNCTIONS
-- ============================================

-- Helper: is user email-verified and not banned? (minimum tier for browsing)
CREATE OR REPLACE FUNCTION is_email_verified_user()
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
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

-- Helper: gender gating for email-verified users (browse tier)
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;

-- ============================================
-- REVIEWS: relax read policy to email-verified (browse tier)
-- ============================================

-- Drop the strict ID-verified read policy
DROP POLICY IF EXISTS "Active ID-verified users can view gender-gated approved reviews" ON reviews;

-- Create new browse-tier policy: email-verified users can read approved reviews
-- Note: App-level blur handles content restriction for non-ID-verified users
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)
  );

-- Keep the strict policy for creating reviews (ID-verified + active required)
-- (Already exists from 00012, no change needed)

-- ============================================
-- RESPONSES: relax read policy to email-verified (browse tier)
-- ============================================

-- Drop the strict ID-verified read policy for responses
DROP POLICY IF EXISTS "Active ID-verified users can view approved responses" ON responses;

-- Create new browse-tier policy for reading responses
CREATE POLICY "Email-verified users can view approved responses"
  ON responses FOR SELECT
  USING (
    status = 'approved'
    AND is_email_verified_user()
  );

-- Keep the strict policy for creating responses (ID-verified + active required)
-- (Already exists from 00012, no change needed)

-- ============================================
-- RPC FUNCTIONS: grant execute to authenticated users
-- (They will be filtered by RLS on the tables they query)
-- ============================================

-- Note: These should already be granted to authenticated from 00012,
-- but ensure they're accessible for browse-tier users
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;
GRANT EXECUTE ON FUNCTION increment_review_views(UUID) TO authenticated;
GRANT EXECUTE ON FUNCTION increment_review_view(UUID) TO authenticated;

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON FUNCTION is_email_verified_user() IS 'Returns true if current user is email-verified and not banned (browse tier)';
COMMENT ON FUNCTION can_browse_subject_gender(gender) IS 'Returns true if email-verified user can browse reviews of the given subject gender';

