CREATE TABLE `product_reviews` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `productID` INT NOT NULL,
  `userID` INT NOT NULL,
  `rating` TINYINT UNSIGNED NOT NULL,
  `title` VARCHAR(150) NULL,
  `text` TEXT NOT NULL,
  `images` JSON NULL,
  `variantLabel` VARCHAR(64) NULL,
  `verifiedPurchase` TINYINT(1) NOT NULL DEFAULT 0,
  `likesCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `commentsCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  UNIQUE KEY `uniq_product_reviews_user_product` (`productID`, `userID`),
  KEY `idx_product_reviews_product` (`productID`, `createdAt`),
  CONSTRAINT `fk_product_reviews_product` FOREIGN KEY (`productID`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_product_reviews_user` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `review_likes` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `reviewID` INT NOT NULL,
  `userID` INT NOT NULL,
  `createdAt` DATETIME NOT NULL,
  UNIQUE KEY `uniq_review_like` (`reviewID`, `userID`),
  CONSTRAINT `fk_review_likes_review` FOREIGN KEY (`reviewID`) REFERENCES `product_reviews` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_likes_user` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `review_comments` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `reviewID` INT NOT NULL,
  `userID` INT NOT NULL,
  `parentID` INT NULL,
  `text` VARCHAR(1000) NOT NULL,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  KEY `idx_review_comments_review` (`reviewID`, `createdAt`),
  CONSTRAINT `fk_review_comments_review` FOREIGN KEY (`reviewID`) REFERENCES `product_reviews` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_comments_user` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_comments_parent` FOREIGN KEY (`parentID`) REFERENCES `review_comments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
