CREATE OR REPLACE VIEW vBluesealProductFusion AS
SELECT
  `p`.`id`                                            AS `id`,
  `p`.`productVariantId`                              AS `productVariantId`,
  concat(`p`.`id`, '-', `p`.`productVariantId`)       AS `code`,
  group_concat(DISTINCT `Shop`.`title` SEPARATOR ',') AS `shops`,
  concat(`p`.`itemno`, ' # ', `pv`.`name`)            AS `cpf`,
  `pv`.`name`                                         AS `variant`,
  `Tag`.`id`                                          AS `tag`,
  `ProductStatus`.`name`                              AS `status`,
  `p`.`sortingPriorityId`                             AS `productPriority`,
  min(`Tag`.`sortingPriorityId`)                      AS `tagPriority`,
  `ProductBrand`.`name`                               AS `brand`,
  sum(`ProductSku`.`stockQty`)                        AS `totalQty`,
  `p`.`creationDate`                                  AS `creation`
FROM ((((((((`Product` `p`
  JOIN `ProductVariant` `pv`) JOIN `Tag`) JOIN `ProductSku`) JOIN `ProductHasTag`) JOIN `ProductBrand`) JOIN
  `ProductStatus`) JOIN `Shop`) LEFT JOIN `ProductHasProductPhoto`
    ON (((`p`.`id` = `ProductHasProductPhoto`.`productId`) AND
         (`p`.`productVariantId` = `ProductHasProductPhoto`.`productVariantId`))))
WHERE ((`ProductHasTag`.`productId` = `p`.`id`) AND (`p`.`productVariantId` = `pv`.`id`) AND
       (`ProductSku`.`shopId` = `Shop`.`id`) AND (`ProductHasTag`.`productVariantId` = `p`.`productVariantId`) AND
       (`ProductStatus`.`id` = `p`.`productStatusId`) AND (`ProductStatus`.`code` IN ('A', 'P', 'I')) AND
       (`ProductHasTag`.`tagId` = `Tag`.`id`) AND (`p`.`id` = `ProductSku`.`productId`) AND (`ProductSku`.`price` > 0)
       AND (`p`.`productVariantId` = `ProductSku`.`productVariantId`) AND (`ProductBrand`.`id` = `p`.`productBrandId`))
GROUP BY `p`.`id`, `p`.`productVariantId`, `ProductSku`.`price`
HAVING (`totalQty` >= 0)