-- Migration: 00018_update_indexing_preference_rpc
-- Description: Add RPC function to update user's indexing preference and bulk-update their reviews
-- Author: Agent
-- Date: 2024-12-22

-- ============================================
-- UPDATE INDEXING PREFERENCE FUNCTION
-- ============================================
-- Updates the user's google_indexing_enabled preference and bulk-updates
-- all of their reviews' is_indexable field to match.
--
-- Behavior:
-- - When enabled=false: Sets user pref OFF, sets ALL author's reviews is_indexable=false
-- - When enabled=true: Sets user pref ON, sets ALL author's reviews is_indexable=true
--   (restores indexability for past posts when re-enabling)
--
-- This ensures:
-- 1. New reviews inherit the preference via trigger (see below)
-- 2. Existing reviews are bulk-updated to match the new preference
-- 3. Complete privacy opt-out when disabled

CREATE OR REPLACE FUNCTION update_indexing_preference(enabled BOOLEAN)
RETURNS VOID AS $$
DECLARE
  user_id UUID := auth.uid();
BEGIN
  -- Validate user is authenticated
  IF user_id IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;

  -- Update user's preference
  UPDATE users
  SET 
    google_indexing_enabled = enabled,
    updated_at = NOW()
  WHERE id = user_id;

  -- Bulk-update all of the user's reviews to match the preference
  UPDATE reviews
  SET 
    is_indexable = enabled,
    updated_at = NOW()
  WHERE author_id = user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

-- Grant execute permission to authenticated users
GRANT EXECUTE ON FUNCTION update_indexing_preference(BOOLEAN) TO authenticated;

COMMENT ON FUNCTION update_indexing_preference(BOOLEAN) IS 
'Updates user google_indexing_enabled and bulk-updates all their reviews is_indexable to match';


-- ============================================
-- SET REVIEW INDEXABLE FROM AUTHOR PREFERENCE
-- ============================================
-- When a new review is created, inherit the author's indexing preference.
-- This ensures new reviews respect the user's privacy setting.

CREATE OR REPLACE FUNCTION set_review_indexable_from_author()
RETURNS TRIGGER AS $$
DECLARE
  author_indexing_enabled BOOLEAN;
BEGIN
  -- Get author's indexing preference
  SELECT google_indexing_enabled INTO author_indexing_enabled
  FROM users
  WHERE id = NEW.author_id;

  -- Set is_indexable based on author's preference (default true if not found)
  NEW.is_indexable := COALESCE(author_indexing_enabled, TRUE);
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

-- Apply trigger on review insert
DROP TRIGGER IF EXISTS trigger_set_review_indexable ON reviews;
CREATE TRIGGER trigger_set_review_indexable
  BEFORE INSERT ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION set_review_indexable_from_author();

COMMENT ON FUNCTION set_review_indexable_from_author() IS 
'Sets new review is_indexable based on author google_indexing_enabled preference';

