-- Migration: 00007_create_functions
-- Description: Create utility functions and triggers
-- Author: Auto-generated
-- Date: 2025-12-15

-- ============================================
-- UPDATE TIMESTAMP FUNCTION
-- ============================================
-- Automatically updates updated_at column on row update.
-- Applied to all tables with updated_at column.

CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- USERS: NORMALIZE INSERT + PROTECT SENSITIVE FIELDS
-- ============================================
-- Prevent clients from setting/modifying server-controlled fields like
-- subscription/verification/ban state.
--
-- Note: We allow requests without JWT context (e.g. SQL editor/migrations).
CREATE OR REPLACE FUNCTION normalize_and_enforce_users()
RETURNS TRIGGER AS $$
BEGIN
  -- Allow privileged/admin contexts (no JWT claims) and service role.
  IF auth.role() IS NULL OR auth.role() = 'service_role' THEN
    RETURN NEW;
  END IF;

  IF TG_OP = 'INSERT' THEN
    -- Force server-controlled fields to safe defaults on client insert.
    NEW.email_verified := FALSE;
    NEW.phone_verified := FALSE;
    NEW.id_verified := FALSE;
    NEW.id_verification_status := 'not_started';
    NEW.id_verified_at := NULL;

    NEW.subscription_tier := 'free';
    NEW.subscription_expires_at := NULL;
    NEW.stripe_customer_id := NULL;

    NEW.is_banned := FALSE;
    NEW.ban_reason := NULL;
    NEW.banned_at := NULL;

    RETURN NEW;
  END IF;

  -- Enforce that the profile email always matches the authenticated email.
  IF NEW.email IS DISTINCT FROM OLD.email AND NEW.email IS DISTINCT FROM (auth.jwt() ->> 'email') THEN
    RAISE EXCEPTION 'users.email must match authenticated email';
  END IF;

  -- Prevent updates to protected fields from non-service-role clients.
  IF NEW.email_verified IS DISTINCT FROM OLD.email_verified
    OR NEW.phone_verified IS DISTINCT FROM OLD.phone_verified
    OR NEW.id_verified IS DISTINCT FROM OLD.id_verified
    OR NEW.id_verification_status IS DISTINCT FROM OLD.id_verification_status
    OR NEW.id_verified_at IS DISTINCT FROM OLD.id_verified_at
    OR NEW.subscription_tier IS DISTINCT FROM OLD.subscription_tier
    OR NEW.subscription_expires_at IS DISTINCT FROM OLD.subscription_expires_at
    OR NEW.stripe_customer_id IS DISTINCT FROM OLD.stripe_customer_id
    OR NEW.is_banned IS DISTINCT FROM OLD.is_banned
    OR NEW.ban_reason IS DISTINCT FROM OLD.ban_reason
    OR NEW.banned_at IS DISTINCT FROM OLD.banned_at
  THEN
    RAISE EXCEPTION 'Not allowed to update protected user fields';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

DROP TRIGGER IF EXISTS trigger_users_profile_protection ON users;
CREATE TRIGGER trigger_users_profile_protection
  BEFORE INSERT OR UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION normalize_and_enforce_users();

-- Apply to all tables with updated_at
CREATE TRIGGER trigger_users_updated_at
  BEFORE UPDATE ON users 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_reviews_updated_at
  BEFORE UPDATE ON reviews 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_responses_updated_at
  BEFORE UPDATE ON responses 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_moderation_queue_updated_at
  BEFORE UPDATE ON moderation_queue 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_verification_records_updated_at
  BEFORE UPDATE ON verification_records 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trigger_subscriptions_updated_at
  BEFORE UPDATE ON subscriptions 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at();


-- ============================================
-- AUTO-ADD REVIEW TO MODERATION QUEUE
-- ============================================
-- When a review is submitted (status = pending_review),
-- automatically add it to the moderation queue.
-- Pro users get faster SLA (2 hours vs 4 hours).

CREATE OR REPLACE FUNCTION add_review_to_moderation_queue()
RETURNS TRIGGER AS $$
DECLARE
  author_is_pro BOOLEAN;
  deadline_hours INTEGER;
BEGIN
  -- Check if author is Pro (shorter SLA)
  SELECT subscription_tier IN ('pro_monthly', 'pro_yearly') INTO author_is_pro
  FROM users WHERE id = NEW.author_id;
  
  deadline_hours := CASE WHEN author_is_pro THEN 2 ELSE 4 END;
  
  INSERT INTO moderation_queue (
    item_type,
    item_id,
    priority,
    auto_flagged,
    flagged_keywords,
    sla_deadline
  ) VALUES (
    'review',
    NEW.id,
    CASE WHEN NEW.auto_flagged THEN 10 ELSE 0 END,
    NEW.auto_flagged,
    NEW.flagged_keywords,
    NOW() + (deadline_hours || ' hours')::INTERVAL
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

CREATE TRIGGER trigger_review_to_modqueue
  AFTER INSERT ON reviews
  FOR EACH ROW
  WHEN (NEW.status = 'pending_review')
  EXECUTE FUNCTION add_review_to_moderation_queue();


-- ============================================
-- AUTO-ADD RESPONSE TO MODERATION QUEUE
-- ============================================
-- When a response is submitted, add to moderation queue.

CREATE OR REPLACE FUNCTION add_response_to_moderation_queue()
RETURNS TRIGGER AS $$
DECLARE
  responder_is_pro BOOLEAN;
  deadline_hours INTEGER;
BEGIN
  -- Check if responder is Pro (shorter SLA)
  SELECT subscription_tier IN ('pro_monthly', 'pro_yearly') INTO responder_is_pro
  FROM users WHERE id = NEW.responder_id;
  
  deadline_hours := CASE WHEN responder_is_pro THEN 2 ELSE 4 END;
  
  INSERT INTO moderation_queue (
    item_type,
    item_id,
    priority,
    sla_deadline
  ) VALUES (
    'response',
    NEW.id,
    0,  -- Responses start at normal priority
    NOW() + (deadline_hours || ' hours')::INTERVAL
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

CREATE TRIGGER trigger_response_to_modqueue
  AFTER INSERT ON responses
  FOR EACH ROW
  WHEN (NEW.status = 'pending_review')
  EXECUTE FUNCTION add_response_to_moderation_queue();


-- ============================================
-- INCREMENT REPORT COUNT ON REVIEW
-- ============================================
-- When a report is submitted, increment the report_count on the review.
-- Also add to moderation queue if threshold reached.

CREATE OR REPLACE FUNCTION handle_new_report()
RETURNS TRIGGER AS $$
BEGIN
  -- Increment report count on the review
  IF NEW.item_type = 'review' THEN
    UPDATE reviews 
    SET report_count = report_count + 1
    WHERE id = NEW.item_id;
  END IF;
  
  -- Add to moderation queue with elevated priority
  INSERT INTO moderation_queue (
    item_type,
    item_id,
    priority,
    sla_deadline
  ) VALUES (
    'report',
    NEW.id,
    5,  -- Reported items get priority 5
    NOW() + INTERVAL '4 hours'
  );
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

CREATE TRIGGER trigger_handle_new_report
  AFTER INSERT ON reports
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_report();


-- ============================================
-- SET PUBLISHED_AT ON REVIEW APPROVAL
-- ============================================
-- When a review is approved, set the published_at timestamp.

CREATE OR REPLACE FUNCTION set_review_published_at()
RETURNS TRIGGER AS $$
BEGIN
  -- Only set published_at when transitioning to 'approved'
  IF NEW.status = 'approved' AND (OLD.status IS NULL OR OLD.status != 'approved') THEN
    NEW.published_at = NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_review_published_at
  BEFORE UPDATE ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION set_review_published_at();


-- ============================================
-- SET PUBLISHED_AT ON RESPONSE APPROVAL
-- ============================================
-- When a response is approved, set the published_at timestamp.

CREATE OR REPLACE FUNCTION set_response_published_at()
RETURNS TRIGGER AS $$
BEGIN
  -- Only set published_at when transitioning to 'approved'
  IF NEW.status = 'approved' AND (OLD.status IS NULL OR OLD.status != 'approved') THEN
    NEW.published_at = NOW();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_response_published_at
  BEFORE UPDATE ON responses
  FOR EACH ROW
  EXECUTE FUNCTION set_response_published_at();


-- ============================================
-- UPDATE USER ID_VERIFIED STATUS
-- ============================================
-- When a verification record is marked as verified,
-- update the user's id_verified status.

CREATE OR REPLACE FUNCTION update_user_verification_status()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.status = 'verified' AND (OLD.status IS NULL OR OLD.status != 'verified') THEN
    UPDATE users
    SET 
      id_verified = TRUE,
      id_verification_status = 'verified',
      id_verified_at = NOW()
    WHERE id = NEW.user_id;
  ELSIF NEW.status = 'failed' THEN
    UPDATE users
    SET id_verification_status = 'failed'
    WHERE id = NEW.user_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

CREATE TRIGGER trigger_update_user_verification
  AFTER UPDATE ON verification_records
  FOR EACH ROW
  EXECUTE FUNCTION update_user_verification_status();


-- ============================================
-- UPDATE USER SUBSCRIPTION FROM SUBSCRIPTIONS TABLE
-- ============================================
-- When a subscription is created or updated,
-- sync the tier to the users table.

CREATE OR REPLACE FUNCTION sync_user_subscription()
RETURNS TRIGGER AS $$
BEGIN
  -- Only update if this is the user's most recent active subscription
  IF NEW.is_active = TRUE THEN
    UPDATE users
    SET 
      subscription_tier = NEW.tier,
      subscription_expires_at = NEW.expires_at
    WHERE id = NEW.user_id;
  ELSIF NEW.is_active = FALSE AND OLD.is_active = TRUE THEN
    -- Subscription deactivated, reset to free
    UPDATE users
    SET 
      subscription_tier = 'free',
      subscription_expires_at = NULL
    WHERE id = NEW.user_id
    AND NOT EXISTS (
      SELECT 1 FROM subscriptions 
      WHERE user_id = NEW.user_id 
      AND is_active = TRUE 
      AND id != NEW.id
    );
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

CREATE TRIGGER trigger_sync_user_subscription
  AFTER INSERT OR UPDATE ON subscriptions
  FOR EACH ROW
  EXECUTE FUNCTION sync_user_subscription();


-- ============================================
-- HELPER FUNCTION: SEARCH REVIEWS
-- ============================================
-- Full-text search function for the search feature.

CREATE OR REPLACE FUNCTION search_reviews(
  search_query TEXT,
  filter_gender gender DEFAULT NULL,
  filter_city TEXT DEFAULT NULL,
  filter_state TEXT DEFAULT NULL,
  result_limit INTEGER DEFAULT 20,
  result_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
  id UUID,
  subject_first_name TEXT,
  subject_last_name TEXT,
  subject_age INTEGER,
  subject_gender gender,
  content TEXT,
  rating INTEGER,
  city TEXT,
  state TEXT,
  view_count INTEGER,
  published_at TIMESTAMPTZ,
  relevance REAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    r.id,
    r.subject_first_name,
    r.subject_last_name,
    r.subject_age,
    r.subject_gender,
    r.content,
    r.rating,
    r.city,
    r.state,
    r.view_count,
    r.published_at,
    ts_rank(r.search_vector, websearch_to_tsquery('english', search_query)) AS relevance
  FROM reviews r
  WHERE 
    r.status = 'approved'
    AND r.is_indexable = TRUE
    AND r.search_vector @@ websearch_to_tsquery('english', search_query)
    AND (filter_gender IS NULL OR r.subject_gender = filter_gender)
    AND (filter_city IS NULL OR r.city ILIKE filter_city)
    AND (filter_state IS NULL OR r.state ILIKE filter_state)
  ORDER BY relevance DESC, r.published_at DESC
  LIMIT result_limit
  OFFSET result_offset;
END;
$$ LANGUAGE plpgsql STABLE;


-- ============================================
-- HELPER FUNCTION: GET FEED REVIEWS
-- ============================================
-- Get reviews for the feed, filtered by gender.
-- Returns reviews about the "opposite" gender:
-- - Women see reviews about men
-- - Men see reviews about women

CREATE OR REPLACE FUNCTION get_feed_reviews(
  viewer_gender gender,
  page_limit INTEGER DEFAULT 20,
  page_offset INTEGER DEFAULT 0
)
RETURNS TABLE (
  id UUID,
  subject_first_name TEXT,
  subject_last_name TEXT,
  subject_age INTEGER,
  subject_gender gender,
  content TEXT,
  rating INTEGER,
  city TEXT,
  state TEXT,
  view_count INTEGER,
  published_at TIMESTAMPTZ,
  slug TEXT
) AS $$
DECLARE
  target_gender gender;
BEGIN
  -- Determine which gender's reviews to show
  -- Returns opposite gender: women see men, men see women
  target_gender := CASE
    WHEN viewer_gender = 'woman' THEN 'man'
    WHEN viewer_gender = 'man' THEN 'woman'
    ELSE NULL
  END;
  
  RETURN QUERY
  SELECT 
    r.id,
    r.subject_first_name,
    r.subject_last_name,
    r.subject_age,
    r.subject_gender,
    r.content,
    r.rating,
    r.city,
    r.state,
    r.view_count,
    r.published_at,
    r.slug
  FROM reviews r
  WHERE 
    r.status = 'approved'
    AND r.is_indexable = TRUE
    AND (target_gender IS NULL OR r.subject_gender = target_gender)
  ORDER BY r.published_at DESC
  LIMIT page_limit
  OFFSET page_offset;
END;
$$ LANGUAGE plpgsql STABLE;


-- ============================================
-- HELPER FUNCTION: INCREMENT VIEW COUNT
-- ============================================
-- Increment view count for a review.
-- Called when a user views a review detail.

-- Primary RPC used by the iOS app (`ReviewService.trackView`)
CREATE OR REPLACE FUNCTION increment_review_views(review_id UUID)
RETURNS VOID AS $$
BEGIN
  UPDATE reviews
  SET view_count = view_count + 1
  WHERE id = review_id
    AND status = 'approved';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

-- Backwards-compatible alias (older name used in early drafts)
CREATE OR REPLACE FUNCTION increment_review_view(review_id UUID)
RETURNS VOID AS $$
BEGIN
  PERFORM increment_review_views(review_id);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

-- Allow API clients to call safe RPCs.
-- (Supabase PostgREST requires EXECUTE privilege on functions.)
GRANT EXECUTE ON FUNCTION search_reviews(TEXT, gender, TEXT, TEXT, INTEGER, INTEGER) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION get_feed_reviews(gender, INTEGER, INTEGER) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION increment_review_views(UUID) TO anon, authenticated;
GRANT EXECUTE ON FUNCTION increment_review_view(UUID) TO anon, authenticated;


-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON FUNCTION update_updated_at() IS 'Automatically updates updated_at timestamp';
COMMENT ON FUNCTION add_review_to_moderation_queue() IS 'Auto-adds submitted reviews to moderation queue';
COMMENT ON FUNCTION add_response_to_moderation_queue() IS 'Auto-adds submitted responses to moderation queue';
COMMENT ON FUNCTION search_reviews(TEXT, gender, TEXT, TEXT, INTEGER, INTEGER) IS 'Full-text search for reviews';
COMMENT ON FUNCTION get_feed_reviews(gender, INTEGER, INTEGER) IS 'Get paginated feed reviews for a user gender';
COMMENT ON FUNCTION increment_review_views(UUID) IS 'Increment view count when review is viewed';
COMMENT ON FUNCTION increment_review_view(UUID) IS 'Alias for increment_review_views(UUID)';
