ALTER TABLE `ShopHasProduct`
  ADD COLUMN `productSizeGroupId` INT(10) UNSIGNED NULL
  AFTER `shopId`,
  ADD CONSTRAINT `fk_shopHasProduct_productSizeGroupId` FOREIGN KEY (`productSizeGroupId`) REFERENCES `ProductSizeGroup` (`id`)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

UPDATE ShopHasProduct shp
  JOIN Product p ON shp.productId = p.id AND shp.productVariantId = p.productVariantId
SET shp.productSizeGroupId = p.productSizeGroupId;

DELETE FROM Sidebar
WHERE pageId = 13;

/*ALTER TABLE `ProductSku`
  ADD INDEX `productId_productVariantId_shopId` (`productId`, `productVariantId`, `shopId`);

ALTER TABLE `ProductSizeGroup`
  ADD INDEX `macroName` (`macroName`);
*/
CREATE TABLE `ProductPublicSku` (
  `productId`        INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  `productSizeId`    INT(10) UNSIGNED NOT NULL,
  `stockQty`         SMALLINT(6)      NOT NULL DEFAULT '0',
  `price`            FLOAT UNSIGNED   NOT NULL DEFAULT '0',
  `salePrice`        FLOAT UNSIGNED   NOT NULL DEFAULT '0',
  PRIMARY KEY (`productId`, `productVariantId`, `productSizeId`),
  INDEX `fk_productPublicSku_productSize` (`productSizeId`),
  CONSTRAINT `fk_productPublicSku_product` FOREIGN KEY (`productId`, `productVariantId`) REFERENCES `Product` (`id`, `productVariantId`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
  CONSTRAINT `fk_productPublicSku_productSize` FOREIGN KEY (`productSizeId`) REFERENCES `ProductSize` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf16_bin'
  ENGINE = InnoDB;

ALTER TABLE `CartLine`
  ADD CONSTRAINT `FK2_CartLine_ProductPublicSku` FOREIGN KEY (`productId`, `productVariantId`, `productSizeId`) REFERENCES `ProductPublicSku` (`productId`, `productVariantId`, `productSizeId`)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

ALTER TABLE `CartLine`
  DROP COLUMN `shopId`;

CREATE TABLE `ProductSizeMacroGroup` (
  `id`                        INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`                      VARCHAR(75)      NOT NULL,
  `defaultProductSizeGroupId` INT(10) UNSIGNED NULL     DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `FK1_productSizeMacroGroup_productSizeGroup` (`defaultProductSizeGroupId`),
  CONSTRAINT `FK1_productSizeMacroGroup_productSizeGroup` FOREIGN KEY (`defaultProductSizeGroupId`) REFERENCES `ProductSizeGroup` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
);

ALTER TABLE `ProductSizeGroup`
  ADD COLUMN `productSizeMacroGroupId` INT(10) UNSIGNED NULL DEFAULT NULL
  AFTER `id`,
  ADD CONSTRAINT `FK1_productSizeMacroGroup_id` FOREIGN KEY (`productSizeMacroGroupId`) REFERENCES `ProductSizeMacroGroup` (`id`)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

INSERT INTO ProductSizeMacroGroup (name) SELECT DISTINCT macroName
                                         FROM ProductSizeGroup;

UPDATE ProductSizeGroup psg
  JOIN ProductSizeMacroGroup psmg ON psg.macroName = psmg.name
SET psg.productSizeMacroGroupId = psmg.id;

ALTER TABLE `ProductSizeGroup`
  DROP COLUMN `macroName`;

INSERT INTO ProductPublicSku
  SELECT
    p.id,
    p.productVariantId,
    psghpsPub.productSizeId,
    sum(stockQty)      AS stockQty,
    max(psk.price)     AS price,
    min(psk.salePrice) AS salePrice
  FROM
    Product p
    JOIN ProductSizeGroup psgPub ON p.productSizeGroupId = psgPub.id
    JOIN ProductSizeGroupHasProductSize psghpsPub ON psgPub.id = psghpsPub.productSizeGroupId
    JOIN ProductSku psk ON psk.productId = p.id AND psk.productVariantId = p.productVariantId
    JOIN ShopHasProduct shp
      ON (psk.productId, psk.productVariantId, psk.shopId) = (shp.productId, shp.productVariantId, shp.shopId)
    JOIN ProductSizeGroup psgPri
      ON shp.productSizeGroupId = psgPri.id AND psgPub.productSizeMacroGroupId = psgPri.productSizeMacroGroupId
    JOIN ProductSizeGroupHasProductSize psghpsPri
      ON psgPri.id = psghpsPri.productSizeGroupId AND psghpsPri.position = psghpsPub.position AND
         psghpsPri.productSizeId = psk.productSizeId
  GROUP BY p.id, p.productVariantId, psghpsPub.productSizeId
  ORDER BY psk.productId, psk.productVariantId, psghpsPri.position
ON DUPLICATE KEY
UPDATE ProductPublicSku.stockQty = stockQty,
  ProductPublicSku.price         = if(ProductPublicSku.price = 0, price, ProductPublicSku.price),
  ProductPublicSku.salePrice     = if(ProductPublicSku.salePrice = 0, salePrice, ProductPublicSku.salePrice);

DROP TRIGGER IF EXISTS `ProductSku_after_insert`;
CREATE TRIGGER `ProductSku_after_insert`
AFTER INSERT ON `ProductSku`
FOR EACH ROW
  BEGIN
    INSERT INTO ProductPublicSku
      SELECT *
      FROM (SELECT
              p.id,
              p.productVariantId,
              psghpsPub.productSizeId,
              sum(stockQty)      AS stockQty,
              max(psk.price)     AS price,
              min(psk.salePrice) AS salePrice
            FROM
              Product p
              JOIN ProductSizeGroup psgPub ON p.productSizeGroupId = psgPub.id
              JOIN ProductSizeGroupHasProductSize psghpsPub ON psgPub.id = psghpsPub.productSizeGroupId
              JOIN ProductSku psk ON (p.id, p.productVariantId) = (psk.productId, psk.productVariantId)
              JOIN ShopHasProduct shp
                ON (psk.productId, psk.productVariantId, psk.shopId) = (shp.productId, shp.productVariantId, shp.shopId)
              JOIN ProductSizeGroup psgPri ON shp.productSizeGroupId = psgPri.id AND
                                              psgPub.productSizeMacroGroupId = psgPri.productSizeMacroGroupId
              JOIN ProductSizeGroupHasProductSize psghpsPri
                ON psgPri.id = psghpsPri.productSizeGroupId AND psghpsPri.position = psghpsPub.position AND
                   psghpsPri.productSizeId = psk.productSizeId
            WHERE (p.id, p.productVariantId) = (NEW.productId, NEW.productVariantId)
            GROUP BY p.id, p.productVariantId, psghpsPub.productSizeId) s2
    ON DUPLICATE KEY
    UPDATE ProductPublicSku.stockQty = s2.stockQty,
      ProductPublicSku.price         = if(ProductPublicSku.price = 0, s2.price, ProductPublicSku.price),
      ProductPublicSku.salePrice     = if(ProductPublicSku.salePrice = 0, s2.salePrice, ProductPublicSku.salePrice);

    UPDATE Product p, (SELECT SUM(ps.stockQty) AS qty
                       FROM ProductSku ps
                       WHERE
                         ps.productId = NEW.productId AND
                         ps.productVariantId = NEW.productVariantId) q1
    SET p.qty = q1.qty
    WHERE p.id = NEW.productId AND p.productVariantId = NEW.productVariantId;
  END;

DROP TRIGGER IF EXISTS `ProductSku_after_update`;
CREATE TRIGGER `ProductSku_after_update`
AFTER UPDATE ON `ProductSku`
FOR EACH ROW
  BEGIN
    IF NEW.stockQty != OLD.stockQty OR NEW.price != OLD.price OR NEW.salePrice != OLD.salePrice
    THEN
      INSERT INTO ProductPublicSku
        SELECT *
        FROM (SELECT
                p.id,
                p.productVariantId,
                psghpsPub.productSizeId,
                sum(stockQty)      AS stockQty,
                max(psk.price)     AS price,
                min(psk.salePrice) AS salePrice
              FROM
                Product p
                JOIN ProductSizeGroup psgPub ON p.productSizeGroupId = psgPub.id
                JOIN ProductSizeGroupHasProductSize psghpsPub ON psgPub.id = psghpsPub.productSizeGroupId
                JOIN ProductSku psk ON (p.id, p.productVariantId) = (psk.productId, psk.productVariantId)
                JOIN ShopHasProduct shp
                  ON (psk.productId, psk.productVariantId, psk.shopId) =
                     (shp.productId, shp.productVariantId, shp.shopId)
                JOIN ProductSizeGroup psgPri ON shp.productSizeGroupId = psgPri.id AND
                                                psgPub.productSizeMacroGroupId = psgPri.productSizeMacroGroupId
                JOIN ProductSizeGroupHasProductSize psghpsPri
                  ON psgPri.id = psghpsPri.productSizeGroupId AND psghpsPri.position = psghpsPub.position AND
                     psghpsPri.productSizeId = psk.productSizeId
              WHERE (p.id, p.productVariantId) = (NEW.productId, NEW.productVariantId)
              GROUP BY p.id, p.productVariantId, psghpsPub.productSizeId) s2
      ON DUPLICATE KEY
      UPDATE ProductPublicSku.stockQty = s2.stockQty,
        ProductPublicSku.price         = if(ProductPublicSku.price = 0, s2.price, ProductPublicSku.price),
        ProductPublicSku.salePrice     = if(ProductPublicSku.salePrice = 0, s2.salePrice, ProductPublicSku.salePrice);

      UPDATE Product p, (SELECT SUM(ps.stockQty) AS qty
                         FROM ProductSku ps
                         WHERE
                           ps.productId = NEW.productId AND
                           ps.productVariantId = NEW.productVariantId) q1
      SET p.qty = q1.qty
      WHERE p.id = NEW.productId AND p.productVariantId = NEW.productVariantId;
    END IF;
  END;
