CREATE OR REPLACE VIEW vBluesealProductList AS
  SELECT
    concat(`p`.`id`, '-', `pv`.`id`)                                                                      AS `code`,
    `p`.`id`                                                                                              AS `id`,
    `p`.`productVariantId`                                                                                AS `productVariantId`,
    concat(`pse`.`name`, ' ', `pse`.`year`)                                                               AS `season`,
    `pse`.`isActive`                                                                                      AS `isActive`,
    concat(`p`.`itemno`, ' # ', `pv`.`name`)                                                              AS `cpf`,
    `s`.`name`                                                                                            AS `shop`,
    concat(ifnull(`p`.`externalId`, ''), '-', ifnull(`dp`.`extId`, ''), '-', ifnull(`ds`.`extSkuId`,''))  AS `externalId`,
    `pb`.`name`                                                                                           AS `brand`,
    `ps`.`name`                                                                                           AS `status`,
    `p`.`creationDate`                                                                                    AS `creationDate`,
    `s`.`id`                                                                                              AS `shopId`,
    concat(
        if(`p`.`productVariantId` IN (SELECT `productVariantId` FROM `ProductSheetActual`), 's', 'n'),
        '/',
        if(`p`.`productVariantId` IN (SELECT `productVariantId` FROM `ProductHasProductPhoto`), 's', 'n')
    )                                                                                                     AS `detailPhoto`,
    concat(`pc`.`slug`, ' ', `pnt`.`name`)                                                                     AS `slug`,
    `pcg`.`name`                                                                                          AS `colorGroup`,
    cast(`psk`.`isOnSale` AS CHAR)                                                                        AS `isOnSale`,
    /*if((`psk`.`isOnSale` = 1), `psk`.`price`,
       `psk`.`salePrice`)                                                                                 AS `currentPrice`,
    if((`pse`.`isActive` = 0), `s`.`pastSeasonMultiplier`,
       if((`psk`.`isOnSale` = 1), `s`.`saleMultiplier`,
          `s`.`currentSeasonMultiplier`))                                                                 AS `multiplier`,
    (`psk`.`value` + ((`psk`.`value` * if((`pse`.`isActive` = 0), `s`.`pastSeasonMultiplier`,
                                          if((`psk`.`isOnSale` = 1), `s`.`saleMultiplier`,
                                             `s`.`currentSeasonMultiplier`))) /
                      100))                                                                               AS `friendRevenue`,*/
    ((((if((`psk`.`isOnSale` = 1), `psk`.`price`, `psk`.`salePrice`) / 1.22) - (`psk`.`value` + ((`psk`.`value` * if(
        (`pse`.`isActive` = 0), `s`.`pastSeasonMultiplier`,
        if((`psk`.`isOnSale` = 1), `s`.`saleMultiplier`, `s`.`currentSeasonMultiplier`))) / 100))) /
      (if((`psk`.`isOnSale` = 1), `psk`.`price`, `psk`.`salePrice`) / 1.22)) * 100)                       AS `mup`,
    if( `p`.`qty` > 0, 'sì', 'no')                                                                        AS `available`,
  `pc`.id as `categoryId`
  FROM ((((((((((((`Product` `p`
    JOIN `ProductSeason` `pse` ON ((`p`.`productSeasonId` = `pse`.`id`))) JOIN `ProductVariant` `pv`
      ON ((`p`.`productVariantId` = `pv`.`id`))) JOIN `ProductBrand` `pb` ON ((`p`.`productBrandId` = `pb`.`id`))) JOIN
    `ProductStatus` `ps` ON ((`ps`.`id` = `p`.`productStatusId`))) JOIN `ShopHasProduct` `sp`
      ON (((`p`.`id` = `sp`.`productId`) AND (`p`.`productVariantId` = `sp`.`productVariantId`)))) JOIN `Shop` `s`
      ON ((`s`.`id` = `sp`.`shopId`))) # LEFT JOIN `ProductHasProductPhoto` as `php` on `php`.`productVariantId` = `p`.`productVariantId`)
      #LEFT JOIN `ProductSheetActual` as `psa` ON `p`.`productVariantId` = `psa`.`productVariantId`)
    LEFT JOIN `ProductSku` `psk` ON (((`p`.`id` = `psk`.`productId`) AND (`p`.`productVariantId` = `psk`.`productVariantId`))))
    LEFT JOIN`ProductHasProductCategory` `ppc`
      ON (((`p`.`id` = `ppc`.`productId`) AND (`p`.`productVariantId` = `ppc`.`productVariantId`)))) JOIN
    `ProductCategory` `pc` ON ((`ppc`.`productCategoryId` = `pc`.`id`))) LEFT JOIN `ProductHasTag` `pht`
      ON (((`pht`.`productId` = `p`.`id`) AND (`pht`.`productVariantId` = `p`.`productVariantId`)))) JOIN `Tag` `t`
      ON ((`pht`.`tagId` = `t`.`id`))) LEFT JOIN `ProductHasProductColorGroup` `phcg`
      ON (((`phcg`.`productId` = `p`.`id`) AND (`phcg`.`productVariantId` = `p`.`productVariantId`)))) JOIN
    `ProductColorGroup` `pcg` ON ((`phcg`.`productColorGroupId` = `pcg`.`id`)) LEFT JOIN (`DirtyProduct` `dp`
       JOIN `DirtySku` `ds` ON ((`dp`.`id` = `ds`.`dirtyProductId`))) ON (`sp`.`productId` = `dp`.`productId`
                                                                           AND `sp`.`productVariantId` = `dp`.`productVariantId` AND
                                                                           `sp`.`shopId` = `dp`.`shopId`)
    LEFT JOIN `ProductNameTranslation` as `pnt` on `p`.`id` = `p`.`id` AND `p`.`productVariantId` = `pnt`.`productVariantId`
  WHERE (`pcg`.`langId` = 1 AND `pnt`.`langId` = 1);