-- ============================================================
-- Migration 005: Public Reviews (non-member reviewers)
-- ============================================================

ALTER TABLE reviews
  MODIFY COLUMN reviewer_id INT UNSIGNED NULL,
  ADD COLUMN IF NOT EXISTS title VARCHAR(255) AFTER reviewer_org_id,
  ADD COLUMN IF NOT EXISTS reviewer_name VARCHAR(255) AFTER title,
  ADD COLUMN IF NOT EXISTS reviewer_email VARCHAR(255) AFTER reviewer_name,
  ADD COLUMN IF NOT EXISTS reviewer_company VARCHAR(255) AFTER reviewer_email,
  ADD COLUMN IF NOT EXISTS source ENUM('member','public') DEFAULT 'member' AFTER reviewer_company,
  ADD COLUMN IF NOT EXISTS moderation_notes TEXT AFTER source;

-- Drop the old UNIQUE KEY that required reviewer_id (it prevents public reviews from same org)
ALTER TABLE reviews DROP INDEX IF EXISTS unique_review;

-- New unique key: member reviews still unique per org+user, public reviews not constrained
ALTER TABLE reviews ADD UNIQUE KEY IF NOT EXISTS unique_member_review (org_id, reviewer_id);

-- Add index for moderation queue
ALTER TABLE reviews ADD INDEX IF NOT EXISTS idx_status_source (status, source);
