-- Migration: 00026_create_comments
-- Description: Create comments table for community discussion on reviews with gender-gated RLS policies
-- Date: 2025-12-23
--
-- Comments allow opposite-gender users to discuss reviews.
-- Review subjects can also comment via Right of Reply (if ID-verified).

-- ============================================
-- COMMENT STATUS ENUM
-- ============================================
CREATE TYPE comment_status AS ENUM (
  'pending_review',  -- Submitted, awaiting moderation
  'approved',        -- Live and visible
  'rejected',        -- Rejected by moderator
  'removed'          -- Removed after report/appeal
);

-- ============================================
-- COMMENTS TABLE
-- ============================================
CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- Links
  review_id UUID NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
  author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  parent_comment_id UUID REFERENCES comments(id) ON DELETE CASCADE, -- For nested replies (optional)
  
  -- Content
  content TEXT NOT NULL CHECK (char_length(content) >= 10 AND char_length(content) <= 500),
  
  -- Status
  status comment_status DEFAULT 'pending_review',
  rejection_reason TEXT,
  
  -- Right of Reply indicator (true if author is verified subject)
  is_subject_response BOOLEAN DEFAULT FALSE,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  published_at TIMESTAMPTZ
);

-- ============================================
-- INDEXES
-- ============================================
CREATE INDEX idx_comments_review ON comments(review_id);
CREATE INDEX idx_comments_author ON comments(author_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id) WHERE parent_comment_id IS NOT NULL;
CREATE INDEX idx_comments_status ON comments(status);

-- Index for approved comments (most common query)
CREATE INDEX idx_comments_approved ON comments(review_id, status)
  WHERE status = 'approved';

-- Index for fetching comments by review (ordered by published date)
CREATE INDEX idx_comments_review_published ON comments(review_id, published_at DESC)
  WHERE status = 'approved';

-- ============================================
-- HELPER FUNCTIONS
-- ============================================

-- Check if current user can comment on reviews with the given subject gender
-- (opposite gender requirement)
CREATE OR REPLACE FUNCTION can_comment_on_subject_gender(subject_gender gender)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM users u
    WHERE u.id = auth.uid()
      AND u.is_banned = FALSE
      AND u.email_verified = TRUE
      AND (
        (u.gender = 'man' AND subject_gender = 'woman')
        OR (u.gender = 'woman' AND subject_gender = 'man')
      )
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

-- Check if current user is the verified subject of a review
-- This checks if the user's verified name/age matches the review subject
CREATE OR REPLACE FUNCTION is_verified_subject_of_review(check_review_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM users u
    INNER JOIN reviews r ON r.id = check_review_id
    WHERE u.id = auth.uid()
      AND u.is_banned = FALSE
      AND u.id_verified = TRUE
      AND u.access_status = 'active'
      AND LOWER(TRIM(u.first_name)) = LOWER(TRIM(r.subject_first_name))
      AND LOWER(TRIM(u.last_name)) = LOWER(TRIM(r.subject_last_name))
      AND u.date_of_birth IS NOT NULL
      AND EXTRACT(YEAR FROM AGE(u.date_of_birth)) = r.subject_age
  );
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, auth, extensions;

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

-- Anyone can view approved comments
CREATE POLICY "Anyone can view approved comments"
  ON comments FOR SELECT
  USING (status = 'approved');

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

-- Opposite-gender users can comment (gender-gated)
CREATE POLICY "Opposite-gender users can comment"
  ON comments FOR INSERT
  WITH CHECK (
    auth.uid() = author_id
    AND can_comment_on_subject_gender(
      (SELECT subject_gender FROM reviews WHERE id = review_id)
    )
  );

-- Review subjects can comment via Right of Reply (if ID-verified)
CREATE POLICY "Subject can comment via Right of Reply"
  ON comments FOR INSERT
  WITH CHECK (
    auth.uid() = author_id
    AND is_verified_subject_of_review(review_id)
  );

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

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

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

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON TABLE comments IS 'Community comments on reviews - gender-gated discussion';
COMMENT ON COLUMN comments.parent_comment_id IS 'For nested replies (optional threading)';
COMMENT ON COLUMN comments.is_subject_response IS 'True if author is verified subject (Right of Reply)';
COMMENT ON FUNCTION can_comment_on_subject_gender(gender) IS 'Returns true if current user can comment on reviews of the given subject gender (opposite gender)';
COMMENT ON FUNCTION is_verified_subject_of_review(UUID) IS 'Returns true if current user is the verified subject of the review (name/age match + ID-verified)';

-- Grant EXECUTE permission for RPC calls
GRANT EXECUTE ON FUNCTION is_verified_subject_of_review(UUID) TO anon, authenticated;

