-- Migration: 00005_create_moderation
-- Description: Create moderation_queue table for content review workflow
-- Author: Auto-generated
-- Date: 2025-12-15

-- ============================================
-- MODERATION QUEUE TABLE
-- ============================================
-- Tracks all items requiring moderator review.
-- Items are auto-added when reviews/responses are submitted.
-- Priority system: flagged (10) > reported (5) > normal (0)

CREATE TABLE moderation_queue (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- What's being moderated
  item_type TEXT NOT NULL CHECK (item_type IN ('review', 'response', 'report')),
  item_id UUID NOT NULL,  -- References reviews.id, responses.id, or reports.id
  
  -- Priority & status
  priority INTEGER DEFAULT 0,  -- Higher = more urgent (flagged = 10, reported = 5, normal = 0)
  is_resolved BOOLEAN DEFAULT FALSE,
  
  -- Auto-moderation results
  auto_flagged BOOLEAN DEFAULT FALSE,
  flagged_keywords TEXT[],
  risk_score INTEGER DEFAULT 0 CHECK (risk_score >= 0 AND risk_score <= 100),  -- 0-100, higher = riskier
  
  -- Assignment
  assigned_to UUID REFERENCES users(id),
  assigned_at TIMESTAMPTZ,
  
  -- Resolution
  action_taken moderation_action,
  action_note TEXT,
  resolved_by UUID REFERENCES users(id),
  resolved_at TIMESTAMPTZ,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- SLA tracking
  sla_deadline TIMESTAMPTZ  -- 4 hours for normal, 2 hours for Pro users
);

-- ============================================
-- INDEXES
-- ============================================
-- Primary query: unresolved items by priority
CREATE INDEX idx_modqueue_unresolved ON moderation_queue(is_resolved, priority DESC, created_at ASC)
  WHERE is_resolved = FALSE;

-- Find assigned items
CREATE INDEX idx_modqueue_assigned ON moderation_queue(assigned_to)
  WHERE assigned_to IS NOT NULL;

-- Look up by item
CREATE INDEX idx_modqueue_item ON moderation_queue(item_type, item_id);

-- SLA monitoring
CREATE INDEX idx_modqueue_sla ON moderation_queue(sla_deadline)
  WHERE is_resolved = FALSE;

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================
ALTER TABLE moderation_queue ENABLE ROW LEVEL SECURITY;

-- Only service role / admins can access moderation queue
-- Moderator access is handled via Edge Functions for security
CREATE POLICY "Service role can manage moderation queue"
  ON moderation_queue FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON TABLE moderation_queue IS 'Queue of items awaiting moderator review';
COMMENT ON COLUMN moderation_queue.priority IS 'Higher = more urgent. Flagged=10, Reported=5, Normal=0';
COMMENT ON COLUMN moderation_queue.risk_score IS 'Auto-moderation risk assessment 0-100';
COMMENT ON COLUMN moderation_queue.sla_deadline IS '4 hours for free users, 2 hours for Pro';
