-- Migration: 00004_create_responses
-- Description: Create responses table for verified subject replies to reviews
-- Author: Auto-generated
-- Date: 2025-12-15

-- ============================================
-- RESPONSES TABLE
-- ============================================
-- Verified responses from review subjects.
-- Only ID-verified users can respond to reviews about themselves.
-- One response per review per user.

CREATE TABLE responses (
  id UUID PRIMARY KEY DEFAULT extensions.gen_random_uuid(),
  
  -- Links
  review_id UUID NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
  responder_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Content
  content TEXT NOT NULL CHECK (char_length(content) >= 10 AND char_length(content) <= 500),
  
  -- Status
  status response_status DEFAULT 'pending_review',
  rejection_reason TEXT,
  
  -- Verification proof (responder must be ID-verified)
  verified_as_subject BOOLEAN DEFAULT FALSE,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  published_at TIMESTAMPTZ,
  
  -- Ensure only one response per review per user
  UNIQUE(review_id, responder_id)
);

-- ============================================
-- INDEXES
-- ============================================
CREATE INDEX idx_responses_review ON responses(review_id);
CREATE INDEX idx_responses_responder ON responses(responder_id);
CREATE INDEX idx_responses_status ON responses(status);

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

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

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

-- Responders can view their own responses (any status)
CREATE POLICY "Responders can view own responses"
  ON responses FOR SELECT
  USING (responder_id = auth.uid());

-- ID-verified users can create responses
CREATE POLICY "ID-verified users can create responses"
  ON responses FOR INSERT
  WITH CHECK (
    auth.uid() = responder_id AND
    EXISTS (SELECT 1 FROM users WHERE id = auth.uid() AND id_verified = TRUE)
  );

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

-- Responders can delete their own responses
CREATE POLICY "Responders can delete own responses"
  ON responses FOR DELETE
  USING (responder_id = auth.uid());

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

-- ============================================
-- COMMENTS
-- ============================================
COMMENT ON TABLE responses IS 'Verified responses from review subjects';
COMMENT ON COLUMN responses.verified_as_subject IS 'Whether responder has been verified as the review subject';
COMMENT ON CONSTRAINT responses_review_id_responder_id_key ON responses IS 'Only one response per review per user';
