-- Migration: 00028_fix_security_definer_views
-- Description: Fix security definer views - recreate views to ensure they run with invoker privileges
-- Author: Security Fix
-- Date: 2025-01-XX

-- ============================================
-- FIX: Remove SECURITY DEFINER from views
-- ============================================
-- Views should run with the privileges of the querying user (invoker),
-- not the view creator. This ensures RLS policies are properly enforced.
--
-- We drop and recreate the views, ensuring they're owned by postgres role
-- and that RLS policies from base tables are properly inherited.

-- ============================================
-- REVIEWS: PUBLIC-SAFE VIEW
-- ============================================
DROP VIEW IF EXISTS reviews_public CASCADE;

-- Use security_invoker (PostgreSQL 15+) to ensure view runs with invoker privileges
-- If PostgreSQL version < 15, this clause will be ignored but view will still work
CREATE VIEW reviews_public
WITH (security_invoker = true) 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;

-- Ensure view is owned by postgres role (default, but explicit for security)
ALTER VIEW reviews_public OWNER TO postgres;

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)
-- ============================================
DROP VIEW IF EXISTS reviews_author CASCADE;

CREATE VIEW reviews_author
WITH (security_invoker = true) 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;

-- Ensure view is owned by postgres role
ALTER VIEW reviews_author OWNER TO postgres;

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

-- ============================================
-- RESPONSES: PUBLIC-SAFE VIEW
-- ============================================
DROP VIEW IF EXISTS responses_public CASCADE;

CREATE VIEW responses_public
WITH (security_invoker = true) 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;

-- Ensure view is owned by postgres role
ALTER VIEW responses_public OWNER TO postgres;

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

-- ============================================
-- RESPONSES: RESPONDER VIEW (includes rejection_reason)
-- ============================================
DROP VIEW IF EXISTS responses_responder CASCADE;

CREATE VIEW responses_responder
WITH (security_invoker = true) 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;

-- Ensure view is owned by postgres role
ALTER VIEW responses_responder OWNER TO postgres;

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

