CREATE OR REPLACE VIEW vBluesealProductSales AS
  SELECT
    `p`.`id`                                             AS `id`,
    `p`.`productVariantId`                               AS `productVariantId`,
    `pc`.`slug`                                          AS `slug`,
    concat(`p`.`id`, '-', `p`.`productVariantId`)        AS `code`,
    group_concat(DISTINCT `Shop`.`title` SEPARATOR ',') AS `shops`,
    concat(`p`.`itemno`, ' # ', `pv`.`name`)             AS `CPF`,
    concat(`ps`.`name`, ' ',ps.year)                     AS `season`,
    `pv`.`name`                                          AS `variant`,
    `ProductStatus`.`name`                               AS `status`,
    `p`.`sortingPriorityId`                              AS `productPriority`,
    min(`Tag`.`sortingPriorityId`)                       AS `tagPriority`,
    `ProductBrand`.`name`                                AS `brand`,
    sum(`ProductSku`.`stockQty`)                         AS `totalQty`,
    max(`ProductSku`.`price`)                            AS `price`,
    max(`ProductSku`.`salePrice`)                        AS `salePrice`,
    cast(max(`ProductSku`.`isOnSale`) as char)           AS `isOnsale`
  FROM ((((((((((`Product` `p`
    JOIN `ProductVariant` `pv`)
    LEFT JOIN (`ProductHasProductCategory` `phpc`
    LEFT JOIN `ProductCategory` `pc` ON ((`phpc`.`productCategoryId` = `pc`.`id`)))
      ON (((`p`.`id` = `phpc`.`productId`) AND (`p`.`productVariantId` = `phpc`.`productVariantId`))))
    LEFT JOIN `ProductHasTag` on `ProductHasTag`.`productVariantId` = `p`.`productVariantId`)
    LEFT JOIN `Tag` ON (`ProductHasTag`.`tagId` = `Tag`.`id`) ) JOIN
    `ProductSku`) JOIN `ProductBrand`) JOIN `ProductStatus`) JOIN `Shop`) LEFT JOIN
    `ProductHasProductPhoto` ON (((`p`.`id` = `ProductHasProductPhoto`.`productId`) AND
                                  (`p`.`productVariantId` = `ProductHasProductPhoto`.`productVariantId`)))) JOIN
    `ProductSeason` `ps` ON ((`p`.`productSeasonId` = `ps`.`id`)))
  WHERE ((`ProductHasTag`.`productId` = `p`.`id`) AND (`p`.`productVariantId` = `pv`.`id`) AND
         (`ProductSku`.`shopId` = `Shop`.`id`) AND
         (`ProductStatus`.`id` = `p`.`productStatusId`) AND `ProductStatus`.`code` IN ('A','P','I') 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`
  HAVING (`totalQty` >= 0)