-- Migration: 00023_rls_audit_fixes
-- Description: Security fixes from RLS audit - hide sensitive fields from public queries
-- Author: Security Audit
-- Date: 2024-12-22

-- ============================================
-- REVIEWS: PUBLIC-SAFE VIEW
-- ============================================
-- This view omits sensitive moderation fields that should not be exposed to clients.
-- Clients should query this view instead of the base reviews table for read operations.
--
-- EXCLUDED FIELDS:
-- - auto_flagged: Internal moderation flag
-- - flagged_keywords: Keywords that triggered auto-moderation (reveals detection logic)
-- - rejection_reason: Internal moderator notes
-- - report_count: Internal metric that could reveal reporting patterns

CREATE OR REPLACE VIEW reviews_public AS
SELECT
  id,
  author_id,
  subject_first_name,
  subject_last_name,
  subject_age,
  subject_gender,
  content,
  rating,
  flag,
  city,
  state,
  country,
  slug,
  is_indexable,
  view_count,
  created_at,
  updated_at,
  published_at,
  status
FROM reviews;

-- Views inherit RLS from base table, so all existing policies apply
GRANT SELECT ON reviews_public TO anon, authenticated;

COMMENT ON VIEW reviews_public IS 'Public-safe view of reviews that excludes internal moderation fields (auto_flagged, flagged_keywords, rejection_reason, report_count)';

-- ============================================
-- REVIEWS: AUTHOR VIEW (includes rejection_reason for authors)
-- ============================================
-- Authors need to see rejection_reason to understand why their review was rejected.
-- This view is meant for fetching the author's own reviews only.
-- RLS ensures they can only see their own rows anyway.

CREATE OR REPLACE VIEW reviews_author AS
SELECT
  id,
  author_id,
  subject_first_name,
  subject_last_name,
  subject_age,
  subject_gender,
  content,
  rating,
  flag,
  city,
  state,
  country,
  slug,
  is_indexable,
  view_count,
  created_at,
  updated_at,
  published_at,
  status,
  rejection_reason  -- Authors should see this for their own rejected reviews
FROM reviews;

GRANT SELECT ON reviews_author TO authenticated;

COMMENT ON VIEW reviews_author IS 'View for authors to see their own reviews including rejection_reason';

-- ============================================
-- RESPONSES: PUBLIC-SAFE VIEW
-- ============================================
-- Omits rejection_reason which is internal moderation data.
-- verified_as_subject is kept as it may be useful for displaying verification badge.

CREATE OR REPLACE VIEW responses_public AS
SELECT
  id,
  review_id,
  responder_id,
  content,
  status,
  verified_as_subject,
  created_at,
  updated_at,
  published_at
  -- EXCLUDED: rejection_reason
FROM responses;

GRANT SELECT ON responses_public TO anon, authenticated;

COMMENT ON VIEW responses_public IS 'Public-safe view of responses that excludes internal moderation fields (rejection_reason)';

-- ============================================
-- RESPONSES: RESPONDER VIEW (includes rejection_reason)
-- ============================================
-- Responders need to see rejection_reason to understand why their response was rejected.

CREATE OR REPLACE VIEW responses_responder AS
SELECT
  id,
  review_id,
  responder_id,
  content,
  status,
  verified_as_subject,
  rejection_reason,  -- Responders should see this for their own rejected responses
  created_at,
  updated_at,
  published_at
FROM responses;

GRANT SELECT ON responses_responder TO authenticated;

COMMENT ON VIEW responses_responder IS 'View for responders to see their own responses including rejection_reason';

-- ============================================
-- COMMENTS
-- ============================================
-- These views provide a defense-in-depth approach to data security:
-- 1. RLS policies control which ROWS are visible
-- 2. Views control which COLUMNS are visible
-- 
-- Client code should:
-- - Use reviews_public for feed/search operations
-- - Use reviews_author for "My Reviews" screens (includes rejection_reason)
-- - Use responses_public for displaying responses on reviews
-- - Use responses_responder for "My Responses" screens (includes rejection_reason)

