-- Migration: 00003_create_reviews
-- Description: Create reviews table - the core content table for dating reviews
-- Author: Auto-generated
-- Date: 2025-12-15

-- ============================================
-- REVIEWS TABLE
-- ============================================
-- The core content table - reviews written about dating subjects.
-- Reviews are written by authenticated users about people they've dated.
-- Subject gender determines which feed the review appears in.

CREATE TABLE reviews (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- Author (the person writing the review)
  author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Subject info (the person being reviewed)
  subject_first_name TEXT NOT NULL,
  subject_last_name TEXT NOT NULL,
  subject_age INTEGER NOT NULL CHECK (subject_age >= 18 AND subject_age <= 120),
  subject_gender gender NOT NULL,  -- Determines which feed this appears in
  
  -- Review content
  content TEXT NOT NULL CHECK (char_length(content) >= 50 AND char_length(content) <= 500),
  rating INTEGER CHECK (rating >= 1 AND rating <= 5),  -- Optional star rating
  
  -- Location context
  city TEXT,
  state TEXT,
  country TEXT DEFAULT 'US',
  
  -- Status & moderation
  status review_status DEFAULT 'pending_review',
  auto_flagged BOOLEAN DEFAULT FALSE,
  flagged_keywords TEXT[],  -- Keywords that triggered auto-flag
  rejection_reason TEXT,
  
  -- For Google indexing
  slug TEXT UNIQUE,  -- URL-friendly identifier: "john-smith-abc123"
  is_indexable BOOLEAN DEFAULT TRUE,  -- Can be set to false by subject
  
  -- Metrics
  view_count INTEGER DEFAULT 0,
  report_count INTEGER DEFAULT 0,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  published_at TIMESTAMPTZ,  -- When approved
  
  -- Full-text search
  search_vector TSVECTOR GENERATED ALWAYS AS (
    setweight(to_tsvector('english', subject_first_name), 'A') ||
    setweight(to_tsvector('english', subject_last_name), 'A') ||
    setweight(to_tsvector('english', coalesce(city, '')), 'B') ||
    setweight(to_tsvector('english', content), 'C')
  ) STORED
);

-- ============================================
-- INDEXES
-- ============================================
CREATE INDEX idx_reviews_author ON reviews(author_id);
CREATE INDEX idx_reviews_status ON reviews(status);
CREATE INDEX idx_reviews_subject_gender ON reviews(subject_gender);
CREATE INDEX idx_reviews_subject_name ON reviews(subject_first_name, subject_last_name);
CREATE INDEX idx_reviews_location ON reviews(city, state);
CREATE INDEX idx_reviews_published_at ON reviews(published_at DESC);
CREATE INDEX idx_reviews_search ON reviews USING GIN(search_vector);
CREATE INDEX idx_reviews_slug ON reviews(slug) WHERE slug IS NOT NULL;

-- Composite index for feed queries (gender + status + published date)
CREATE INDEX idx_reviews_feed ON reviews(subject_gender, status, published_at DESC)
  WHERE status = 'approved';

-- ============================================
-- SLUG GENERATION FUNCTION
-- ============================================
-- Generate URL-friendly slug on insert: "firstname-lastname-abc12345"
CREATE OR REPLACE FUNCTION generate_review_slug()
RETURNS TRIGGER AS $$
BEGIN
  NEW.slug := lower(
    regexp_replace(NEW.subject_first_name, '[^a-zA-Z0-9]', '', 'g') || '-' ||
    regexp_replace(NEW.subject_last_name, '[^a-zA-Z0-9]', '', 'g') || '-' ||
    substr(NEW.id::text, 1, 8)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_generate_review_slug
  BEFORE INSERT ON reviews
  FOR EACH ROW
  EXECUTE FUNCTION generate_review_slug();

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

-- Anyone can view approved, indexable reviews (for feed/search)
CREATE POLICY "Anyone can view approved public reviews"
  ON reviews FOR SELECT
  USING (
    status = 'approved' AND
    is_indexable = TRUE
  );

-- Authors can view their own reviews (any status)
CREATE POLICY "Authors can view own reviews"
  ON reviews FOR SELECT
  USING (author_id = auth.uid());

-- Authenticated users with verified email can create reviews
CREATE POLICY "Verified users can create reviews"
  ON reviews FOR INSERT
  WITH CHECK (
    auth.uid() = author_id AND
    -- Email verification is authoritative in Supabase Auth and is present in JWT claims.
    (auth.jwt() ->> 'email_confirmed_at') IS NOT NULL AND
    -- Basic ban enforcement (MVP): banned users cannot create new reviews.
    EXISTS (
      SELECT 1
      FROM users u
      WHERE u.id = auth.uid()
        AND u.is_banned = FALSE
    )
  );

-- Authors can update their own draft/rejected reviews
CREATE POLICY "Authors can update own draft reviews"
  ON reviews FOR UPDATE
  USING (
    author_id = auth.uid() AND 
    status IN ('draft', 'rejected')
  )
  WITH CHECK (author_id = auth.uid());

-- Authors can delete their own reviews
CREATE POLICY "Authors can delete own reviews"
  ON reviews FOR DELETE
  USING (author_id = auth.uid());

-- Service role can manage all reviews (for moderation)
CREATE POLICY "Service role can manage all reviews"
  ON reviews FOR ALL
  USING (auth.role() = 'service_role')
  WITH CHECK (auth.role() = 'service_role');

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON TABLE reviews IS 'Core content table - reviews about people in the dating scene';
COMMENT ON COLUMN reviews.subject_gender IS 'Determines which gender-specific feed this review appears in';
COMMENT ON COLUMN reviews.slug IS 'URL-friendly identifier for SEO: firstname-lastname-uuid8';
COMMENT ON COLUMN reviews.is_indexable IS 'Can be disabled by subject to remove from search engines';
COMMENT ON COLUMN reviews.search_vector IS 'Full-text search vector for name and content search';
