-- Migration: 00012_access_gating_rls
-- Description: Tighten RLS so content is only accessible to active + ID-verified users; enforce gender-gated viewing
-- Date: 2025-12-21

-- Helper predicate: is the current user allowed to access content?
CREATE OR REPLACE FUNCTION is_active_id_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.id_verified = TRUE
      AND u.access_status = 'active'
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

-- Helper predicate: does viewer match opposite-gender gating for this subject_gender?
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;

-- ============================================
-- REVIEWS: replace public read with gated read
-- ============================================
DROP POLICY IF EXISTS "Anyone can view approved public reviews" ON reviews;

CREATE POLICY "Active ID-verified users can view gender-gated approved reviews"
  ON reviews FOR SELECT
  USING (
    status = 'approved'
    AND is_indexable = TRUE
    AND can_view_subject_gender(subject_gender)
  );

-- Tighten create policy to require active+id_verified (not just email verified)
DROP POLICY IF EXISTS "Verified users can create reviews" ON reviews;

CREATE POLICY "Active ID-verified users can create reviews"
  ON reviews FOR INSERT
  WITH CHECK (
    auth.uid() = author_id
    AND is_active_id_verified_user()
  );

-- ============================================
-- RESPONSES: replace public read with gated read
-- ============================================
DROP POLICY IF EXISTS "Anyone can view approved responses" ON responses;

CREATE POLICY "Active ID-verified users can view approved responses"
  ON responses FOR SELECT
  USING (
    status = 'approved'
    AND is_active_id_verified_user()
  );

-- Create response requires active+id_verified (not only id_verified)
DROP POLICY IF EXISTS "ID-verified users can create responses" ON responses;

CREATE POLICY "Active ID-verified users can create responses"
  ON responses FOR INSERT
  WITH CHECK (
    auth.uid() = responder_id
    AND is_active_id_verified_user()
  );

-- ============================================
-- REPORTS: require active+id_verified to report
-- ============================================
DROP POLICY IF EXISTS "Authenticated users can create reports" ON reports;

CREATE POLICY "Active ID-verified users can create reports"
  ON reports FOR INSERT
  WITH CHECK (
    auth.uid() = reporter_id
    AND is_active_id_verified_user()
  );

-- ============================================
-- RPC EXECUTE PRIVILEGES: remove anon access
-- ============================================
REVOKE EXECUTE ON FUNCTION search_reviews(TEXT, gender, TEXT, TEXT, INTEGER, INTEGER) FROM anon;
REVOKE EXECUTE ON FUNCTION get_feed_reviews(gender, INTEGER, INTEGER) FROM anon;
REVOKE EXECUTE ON FUNCTION increment_review_views(UUID) FROM anon;
REVOKE EXECUTE ON FUNCTION increment_review_view(UUID) FROM anon;

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;


