CREATE TABLE `product_content` (
  `product_id` INT NOT NULL PRIMARY KEY,
  `desc` LONGTEXT NULL,
  `desc_en` LONGTEXT NULL,
  `desc_de` LONGTEXT NULL,
  `desc_it` LONGTEXT NULL,
  `desc_fr` LONGTEXT NULL,
  `specifications` JSON NULL,
  `variations` JSON NULL,
  CONSTRAINT `fk_product_content_product` FOREIGN KEY (`product_id`)
    REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

INSERT IGNORE INTO `product_content` (`product_id`, `desc`, `desc_en`, `desc_de`, `desc_it`, `desc_fr`, `specifications`, `variations`)
SELECT `id`, `desc`, `desc_en`, `desc_de`, `desc_it`, `desc_fr`,
       CASE WHEN `specifications` IS NULL THEN NULL WHEN JSON_VALID(`specifications`) THEN `specifications` ELSE NULL END,
       `variations`
FROM `products`;

UPDATE `product_content`
SET `specifications` = CAST(JSON_UNQUOTE(`specifications`) AS JSON)
WHERE JSON_TYPE(`specifications`) = 'STRING' AND JSON_VALID(JSON_UNQUOTE(`specifications`));

ALTER TABLE `product_content` ADD FULLTEXT KEY `ft_desc` (`desc`, `desc_en`, `desc_de`, `desc_it`, `desc_fr`);

ALTER TABLE `products` ADD FULLTEXT KEY `ft_name` (`name`, `keywords`);
