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 `ProductSheetActual`.`productVariantId`
                                         FROM `ProductSheetActual`), 's', 'n'), '/',
           if(`p`.`productVariantId` IN (SELECT `ProductHasProductPhoto`.`productVariantId`
                                         FROM `ProductHasProductPhoto`), 's',
              'n'))                                                                      AS `detailPhoto`,
    pc.id                                                                                AS `slug`,
    `pcg`.`name`                                                                         AS `colorGroup`,
    cast(`psk`.`isOnSale` AS CHAR CHARSET utf8)                                          AS `isOnSale`,
    ((((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`
  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 `ProductSku` `psk`
      ON (((`p`.`id` = `psk`.`productId`) AND (`p`.`productVariantId` = `psk`.`productVariantId`)))) LEFT JOIN
    (`ProductHasProductCategory` `ppc`
      JOIN `ProductCategory` `pc` ON ((`ppc`.`productCategoryId` = `pc`.`id`)))
      ON (((`p`.`id` = `ppc`.`productId`) AND (`p`.`productVariantId` = `ppc`.`productVariantId`)))) LEFT JOIN
    (`ProductHasTag` `pht`
      JOIN `Tag` `t` ON ((`pht`.`tagId` = `t`.`id`)))
      ON (((`pht`.`productId` = `p`.`id`) AND (`pht`.`productVariantId` = `p`.`productVariantId`)))) LEFT JOIN
    (`ProductHasProductColorGroup` `phcg`
      JOIN `ProductColorGroup` `pcg` ON (((`phcg`.`productColorGroupId` = `pcg`.`id`) AND (`pcg`.`langId` = 1))))
      ON (((`phcg`.`productId` = `p`.`id`) AND (`phcg`.`productVariantId` = `p`.`productVariantId`)))) 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` `pnt`
      ON (((`p`.`id` = `pnt`.`productId`) AND (`p`.`productVariantId` = `pnt`.`productVariantId`) AND
           (`pnt`.`langId` = 1))));

create or replace view vBluesealMarketplaceProductList as
SELECT
  concat(`p`.`id`, '-', `p`.`productVariantId`)           AS `code`,
  `p`.`id`                                                AS `productId`,
  `p`.`productVariantId`                                  AS `productVariantId`,
  `p`.`itemno`                                            AS `itemno`,
  concat(`pss`.`name`, `pss`.`year`)                      AS `season`,
  `pb`.`name`                                             AS `brand`,
  `p`.`creationDate`                                      AS `creationDate`,
  concat(ifnull(`m`.`name`, ''),' - ', ifnull(`ma`.`name`, '')) AS `marketplaceAccountName`,
  `s`.`name`                                              AS `shop`,
  `s`.`id`                                                AS `shopId`,
  `mahp`.`marketplaceProductId`                           AS `marketplaceProductId`,
  `mahp`.`marketplaceId`                                  AS `marketplaceId`,
  `mahp`.`marketplaceAccountId`                           AS `marketplaceAccountId`,
  `mahp`.`fee`                                            AS `fee`,
  phpc.productCategoryId                                  as category,
  if(mahp.marketplaceAccountId is null, '',
     concat_ws(',',if(mahp.isToWork = 0, 'lavorato',''),
                   if(mahp.hasError = 1, 'errore', ''),
                   if(mahp.isDeleted = 1, 'cancellato', ''))) as status
FROM `Product` `p`
  JOIN `ProductStatus` `ps` ON ((`p`.`productStatusId` = `ps`.`id`))
  JOIN `ShopHasProduct` `shp` ON (((`p`.`id` = `shp`.`productId`) AND (`p`.`productVariantId` = `shp`.`productVariantId`)))
  JOIN `Shop` `s` ON ((`s`.`id` = `shp`.`shopId`))
  JOIN `ProductSeason` `pss` ON ((`pss`.`id` = `p`.`productSeasonId`))
  JOIN `ProductBrand` `pb` ON ((`p`.`productBrandId` = `pb`.`id`))
  JOIN ProductHasProductCategory phpc on p.id = phpc.productId and p.productVariantId = phpc.productVariantId
  LEFT JOIN (`MarketplaceAccountHasProduct` `mahp`
    JOIN `MarketplaceAccount` `ma` ON `ma`.`marketplaceId` = `mahp`.`marketplaceId` AND `ma`.`id` = `mahp`.`marketplaceAccountId`
    JOIN `Marketplace` `m` ON `m`.`id` = `ma`.`marketplaceId`)
  ON `mahp`.`productId` = `p`.`id` AND `mahp`.`productVariantId` = `p`.`productVariantId`
WHERE (((`ps`.`isReady` = 1) AND (`p`.`qty` > 0)) OR (`m`.`id` IS NOT NULL))