ALTER TABLE `Product`
  ADD COLUMN `qty` TINYINT(4) UNSIGNED NOT NULL
  AFTER `productSheetPrototypeId`,
  ADD INDEX `qty` (`qty`);

DROP TRIGGER ProductSku_after_update;
CREATE TRIGGER `ProductSku_after_update` AFTER UPDATE ON `ProductSku` FOR EACH ROW
  BEGIN
    IF NEW.stockQty != OLD.stockQty
    THEN
      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;

DROP TRIGGER ProductSku_after_insert;
CREATE TRIGGER `ProductSku_after_insert` AFTER INSERT ON `ProductSku` FOR EACH ROW
  BEGIN
    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;

UPDATE Product p, (SELECT
                     ifnull(sum(stockQty), 0) AS qty,
                     ps.productId,
                     ps.productVariantId
                   FROM ProductSku ps
                   GROUP BY productId, productVariantId) qu
SET p.qty = qu.qty
WHERE p.id = qu.productId AND p.productVariantId = qu.productVariantId;

/*OLD
SELECT
  `Product`.`id`                                                 AS `product`,
  `Product`.`productVariantId`                                   AS `variant`,
  `ProductSku`.`shopId`                                          AS `shop`,
  `Tag`.`id`                                                     AS `tag`,
  min(`ProductSku`.`price`)                                      AS `price`,
  `Product`.`sortingPriorityId`                                  AS `productPriority`,
  `Tag`.`sortingPriorityId`                                      AS `tagPriority`,
  `Product`.`productBrandId`                                     AS `brand`,
  `ProductSku`.`productSizeId`                                   AS `size`,
  (CASE WHEN isnull(`ProductHasProductColorGroup`.`productColorGroupId`)
    THEN 1
   ELSE `ProductHasProductColorGroup`.`productColorGroupId` END) AS `color`,
  `ProductHasProductCategory`.`productCategoryId`                AS `category`,
  `ProductCategory`.`lft`                                        AS `lft`,
  max(`ProductSku`.`stockQty`)                                   AS `maxQty`,
  `Product`.`creationDate`                                       AS `creation`
FROM ((((((((`Product`
  JOIN `Tag`) JOIN `ProductStatus`) JOIN `ProductSku`) JOIN `ProductHasTag`) JOIN `ProductSize`) JOIN
  `ProductCategory`) JOIN `ProductHasProductCategory`) LEFT JOIN `ProductHasProductColorGroup`
    ON (((`Product`.`id` = `ProductHasProductColorGroup`.`productId`) AND
         (`Product`.`productVariantId` = `ProductHasProductColorGroup`.`productVariantId`))))
WHERE (
  (`ProductHasTag`.`productId` = `Product`.`id`) AND (`ProductHasTag`.`productVariantId` = `Product`.`productVariantId`)
  AND (`Product`.`productStatusId` = `ProductStatus`.`id`) AND (`ProductStatus`.`isVisible` = 1) AND
  (`ProductHasTag`.`tagId` = `Tag`.`id`) AND (`Product`.`id` = `ProductSku`.`productId`) AND (`ProductSku`.`price` > 0)
  AND (`Product`.`productVariantId` = `ProductSku`.`productVariantId`) AND
  (`ProductSize`.`id` = `ProductSku`.`productSizeId`) AND (`ProductHasProductCategory`.`productId` = `Product`.`id`) AND
  (`ProductHasProductCategory`.`productVariantId` = `Product`.`productVariantId`) AND
  (`ProductCategory`.`id` = `ProductHasProductCategory`.`productCategoryId`) AND (`ProductCategory`.`id` <> 1))
GROUP BY `Product`.`id`, `Product`.`productVariantId`, `ProductHasProductCategory`.`productCategoryId`,
  `ProductSku`.`price`, `Tag`.`id`
HAVING ((`maxQty` > 0) AND (`ProductSku`.`price` > 0))
ORDER BY `Tag`.`sortingPriorityId`, `Product`.`sortingPriorityId`;
*/

create or replace view vProductSortingView as
SELECT
  `Product`.`id`                                                 AS `product`,
  `Product`.`productVariantId`                                   AS `variant`,
  `ProductSku`.`shopId`                                          AS `shop`,
  `Tag`.`id`                                                     AS `tag`,
  min(`ProductSku`.`price`)                                      AS `price`,
  `Product`.`sortingPriorityId`                                  AS `productPriority`,
  `Tag`.`sortingPriorityId`                                      AS `tagPriority`,
  `Product`.`productBrandId`                                     AS `brand`,
  `ProductSku`.`productSizeId`                                   AS `size`,
  (CASE WHEN isnull(`ProductHasProductColorGroup`.`productColorGroupId`)
    THEN 1
   ELSE `ProductHasProductColorGroup`.`productColorGroupId` END) AS `color`,
  `ProductHasProductCategory`.`productCategoryId`                AS `category`,
  `ProductCategory`.`lft`                                        AS `lft`,
  max(`ProductSku`.`stockQty`)                                   AS `maxQty`,
  `Product`.`creationDate`                                       AS `creation`
FROM ((((((((`Product`
  JOIN `Tag`) JOIN `ProductStatus`) JOIN `ProductSku`) JOIN `ProductHasTag`) JOIN `ProductSize`) JOIN
  `ProductCategory`) JOIN `ProductHasProductCategory`) LEFT JOIN `ProductHasProductColorGroup`
    ON (((`Product`.`id` = `ProductHasProductColorGroup`.`productId`) AND
         (`Product`.`productVariantId` = `ProductHasProductColorGroup`.`productVariantId`))))
WHERE (
        (`ProductHasTag`.`productId` = `Product`.`id`) AND (`ProductHasTag`.`productVariantId` = `Product`.`productVariantId`)
        AND (`Product`.`productStatusId` = `ProductStatus`.`id`) AND (`ProductStatus`.`isVisible` = 1) AND
        (`ProductHasTag`.`tagId` = `Tag`.`id`) AND (`Product`.`id` = `ProductSku`.`productId`) AND (`ProductSku`.`price` > 0)
        AND (`Product`.`productVariantId` = `ProductSku`.`productVariantId`) AND
        (`ProductSize`.`id` = `ProductSku`.`productSizeId`) AND (`ProductHasProductCategory`.`productId` = `Product`.`id`) AND
        (`ProductHasProductCategory`.`productVariantId` = `Product`.`productVariantId`) AND
        (`ProductCategory`.`id` = `ProductHasProductCategory`.`productCategoryId`) AND (`ProductCategory`.`id` <> 1))
      and Product.qty > 0
GROUP BY `Product`.`id`, `Product`.`productVariantId`, `ProductHasProductCategory`.`productCategoryId`,
  `ProductSku`.`price`, `Tag`.`id`
HAVING (`ProductSku`.`price` > 0)
ORDER BY `Tag`.`sortingPriorityId`, `Product`.`sortingPriorityId`;