CREATE TABLE `product_questions` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `productID` INT NOT NULL,
  `userID` INT NOT NULL,
  `text` VARCHAR(1000) NOT NULL,
  `answersCount` INT UNSIGNED NOT NULL DEFAULT 0,
  `hidden` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  KEY `idx_product_questions_product` (`productID`, `createdAt`),
  CONSTRAINT `fk_product_questions_product` FOREIGN KEY (`productID`) REFERENCES `products` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_product_questions_user` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `question_answers` (
  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `questionID` INT NOT NULL,
  `userID` INT NOT NULL,
  `text` VARCHAR(1000) NOT NULL,
  `isAdminReply` TINYINT(1) NOT NULL DEFAULT 0,
  `hidden` TINYINT(1) NOT NULL DEFAULT 0,
  `createdAt` DATETIME NOT NULL,
  `updatedAt` DATETIME NOT NULL,
  KEY `idx_question_answers_question` (`questionID`, `createdAt`),
  CONSTRAINT `fk_question_answers_question` FOREIGN KEY (`questionID`) REFERENCES `product_questions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_question_answers_user` FOREIGN KEY (`userID`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
