-- Migration: 00019_keyword_scanner
-- Description: Auto-flag content containing harassment/doxxing keywords
-- Author: Auto-generated
-- Date: 2025-12-22

-- ============================================
-- ADD AUTO_FLAGGED COLUMNS TO RESPONSES
-- ============================================
-- Reviews already have these columns; add to responses for parity.

ALTER TABLE responses
ADD COLUMN IF NOT EXISTS auto_flagged BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS flagged_keywords TEXT[];

COMMENT ON COLUMN responses.auto_flagged IS 'Whether content was auto-flagged by keyword scanner';
COMMENT ON COLUMN responses.flagged_keywords IS 'Keywords that triggered auto-flag';

-- ============================================
-- MODERATION KEYWORDS TABLE
-- ============================================
-- Configurable keyword list for auto-flagging.
-- Categories allow different handling (e.g., threat vs harassment).

CREATE TABLE IF NOT EXISTS moderation_keywords (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  keyword TEXT NOT NULL UNIQUE,
  category TEXT NOT NULL DEFAULT 'harassment' CHECK (category IN ('harassment', 'doxxing', 'threat', 'spam', 'other')),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index for active keyword lookups
CREATE INDEX IF NOT EXISTS idx_moderation_keywords_active 
  ON moderation_keywords(is_active, category) 
  WHERE is_active = TRUE;

-- RLS: Only service role can manage keywords
ALTER TABLE moderation_keywords ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Service role can manage moderation keywords"
  ON moderation_keywords FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

COMMENT ON TABLE moderation_keywords IS 'Configurable keywords for auto-flagging content';

-- Apply updated_at trigger
CREATE TRIGGER trigger_moderation_keywords_updated_at
  BEFORE UPDATE ON moderation_keywords
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at();

-- ============================================
-- KEYWORD SCANNER FUNCTION
-- ============================================
-- Scans content for flagged keywords (case-insensitive).
-- Sets auto_flagged = true and populates flagged_keywords array.

CREATE OR REPLACE FUNCTION scan_content_for_keywords()
RETURNS TRIGGER AS $$
DECLARE
  content_to_scan TEXT;
  matched_keywords TEXT[] := '{}';
  keyword_record RECORD;
BEGIN
  -- Get the content field to scan
  content_to_scan := LOWER(NEW.content);
  
  -- Scan against all active keywords
  FOR keyword_record IN 
    SELECT keyword FROM moderation_keywords WHERE is_active = TRUE
  LOOP
    -- Word boundary match to avoid false positives (e.g., "ass" in "class")
    -- Uses \m and \M for word boundaries in PostgreSQL regex
    IF content_to_scan ~* ('\m' || keyword_record.keyword || '\M') THEN
      matched_keywords := array_append(matched_keywords, keyword_record.keyword);
    END IF;
  END LOOP;
  
  -- Set flags if any keywords matched
  IF array_length(matched_keywords, 1) > 0 THEN
    NEW.auto_flagged := TRUE;
    NEW.flagged_keywords := matched_keywords;
  ELSE
    NEW.auto_flagged := FALSE;
    NEW.flagged_keywords := NULL;
  END IF;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, auth, extensions;

COMMENT ON FUNCTION scan_content_for_keywords() IS 'Auto-flags content containing moderation keywords';

-- ============================================
-- APPLY SCANNER TO REVIEWS
-- ============================================
-- Runs BEFORE INSERT so auto_flagged is set before moderation queue trigger.

DROP TRIGGER IF EXISTS trigger_scan_review_keywords ON reviews;
CREATE TRIGGER trigger_scan_review_keywords
  BEFORE INSERT ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION scan_content_for_keywords();

-- ============================================
-- APPLY SCANNER TO RESPONSES
-- ============================================

DROP TRIGGER IF EXISTS trigger_scan_response_keywords ON responses;
CREATE TRIGGER trigger_scan_response_keywords
  BEFORE INSERT ON responses
  FOR EACH ROW
  EXECUTE FUNCTION scan_content_for_keywords();

-- ============================================
-- UPDATE RESPONSE MODERATION QUEUE TRIGGER
-- ============================================
-- Now reads auto_flagged to set priority (matching reviews behavior).

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,
    auto_flagged,
    flagged_keywords,
    sla_deadline
  ) VALUES (
    'response',
    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;

-- ============================================
-- SEED INITIAL KEYWORD LIST
-- ============================================
-- Harassment, doxxing, and threat signals.
-- Lowercase for case-insensitive matching.

INSERT INTO moderation_keywords (keyword, category) VALUES
  -- Harassment keywords
  ('kill yourself', 'threat'),
  ('kys', 'threat'),
  ('kill you', 'threat'),
  ('murder you', 'threat'),
  ('die', 'threat'),
  ('hope you die', 'threat'),
  ('gonna kill', 'threat'),
  ('death threat', 'threat'),
  
  -- Doxxing signals
  ('ssn', 'doxxing'),
  ('social security', 'doxxing'),
  ('home address', 'doxxing'),
  ('where you live', 'doxxing'),
  ('found your address', 'doxxing'),
  ('i know where', 'doxxing'),
  ('your address is', 'doxxing'),
  ('license plate', 'doxxing'),
  ('bank account', 'doxxing'),
  ('credit card', 'doxxing'),
  
  -- Harassment terms
  ('kill her', 'harassment'),
  ('kill him', 'harassment'),
  ('beat her', 'harassment'),
  ('beat him', 'harassment'),
  ('rape', 'harassment'),
  ('assault', 'harassment'),
  ('stalk', 'harassment'),
  ('stalking', 'harassment'),
  ('harass', 'harassment'),
  ('revenge porn', 'harassment'),
  ('nude photos', 'harassment'),
  ('leaked nudes', 'harassment'),
  
  -- Slurs and hate speech (abbreviated for safety)
  ('n-word', 'harassment'),
  ('f-word slur', 'harassment'),
  ('retard', 'harassment'),
  
  -- Spam signals
  ('click here', 'spam'),
  ('free money', 'spam'),
  ('crypto', 'spam'),
  ('bitcoin', 'spam'),
  ('telegram', 'spam'),
  ('whatsapp', 'spam'),
  ('cashapp', 'spam'),
  ('venmo me', 'spam')
ON CONFLICT (keyword) DO NOTHING;

-- ============================================
-- GRANT RPC ACCESS (if needed)
-- ============================================
-- No public RPCs needed; keyword management is service_role only.


