-- ============================================
-- Create Supplier Reviews Tables
-- TripAdvisor-style review system
-- ============================================

-- Table: supplier_reviews
CREATE TABLE IF NOT EXISTS `supplier_reviews` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `orderId` INT NOT NULL,
  `supplierId` INT NOT NULL,
  `customerId` INT NOT NULL,
  `inquiryId` INT NULL,
  `rating` INT NOT NULL COMMENT 'Overall rating from 1 to 5 stars',
  `reviewTitle` VARCHAR(200) NULL COMMENT 'Short title for the review',
  `reviewText` TEXT NOT NULL COMMENT 'Detailed review text',
  `ratingPrice` INT NULL COMMENT 'Price rating (1-5)',
  `ratingQuality` INT NULL COMMENT 'Quality rating (1-5)',
  `ratingService` INT NULL COMMENT 'Service rating (1-5)',
  `ratingDelivery` INT NULL COMMENT 'Delivery rating (1-5)',
  `photos` JSON NULL COMMENT 'Array of photo URLs',
  `helpfulCount` INT DEFAULT 0 COMMENT 'Number of helpful votes',
  `supplierResponse` TEXT NULL COMMENT 'Supplier response to the review',
  `supplierResponseDate` DATETIME NULL COMMENT 'Date when supplier responded',
  `isVerified` BOOLEAN DEFAULT FALSE COMMENT 'Whether this is a verified purchase review',
  `isPublished` BOOLEAN DEFAULT TRUE COMMENT 'Whether review is published',
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_order_review` (`orderId`),
  KEY `idx_supplier_reviews_supplier` (`supplierId`),
  KEY `idx_supplier_reviews_customer` (`customerId`),
  KEY `idx_supplier_reviews_rating` (`rating`),
  CONSTRAINT `fk_review_order` FOREIGN KEY (`orderId`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_supplier` FOREIGN KEY (`supplierId`) REFERENCES `suppliers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_customer` FOREIGN KEY (`customerId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_inquiry` FOREIGN KEY (`inquiryId`) REFERENCES `inquiries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: review_helpful (for "Was this review helpful?" votes)
CREATE TABLE IF NOT EXISTS `review_helpful` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `reviewId` INT NOT NULL,
  `userId` INT NOT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_review_helpful` (`reviewId`, `userId`),
  KEY `idx_helpful_review` (`reviewId`),
  KEY `idx_helpful_user` (`userId`),
  CONSTRAINT `fk_helpful_review` FOREIGN KEY (`reviewId`) REFERENCES `supplier_reviews` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_helpful_user` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add index for better query performance
CREATE INDEX `idx_reviews_supplier_published` ON `supplier_reviews` (`supplierId`, `isPublished`, `createdAt`);
CREATE INDEX `idx_reviews_rating_published` ON `supplier_reviews` (`rating`, `isPublished`);