ALTER TABLE `MarketplaceAccountCategory`
  ALTER `path` DROP DEFAULT;
ALTER TABLE `MarketplaceAccountCategory`
  CHANGE COLUMN `path` `path` VARCHAR(255) NULL AFTER `name`;


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`,
  concat(psg.locale, ' - ',
         `psg`.`macroName`)                                                                             AS `productSizeGroup`,
  `p`.`creationDate`                                                                                    AS `creationDate`,
  `s`.`id`                                                                                              AS `shopId`,
  if((SELECT count(*) FROM `ProductSheetActual` WHERE productId = p.id AND productVariantId = p.productVariantId) > 2, 'sì', 'no')
                                                                                                        AS hasDetails,
  if(p.dummyPicture is null OR p.dummyPicture = 'bs-dummy-16-9.png', 'no', 'sì') as `dummy`,
  if((p.id, `p`.`productVariantId`) IN (SELECT
                                          ProductHasProductPhoto.productId,
                                          `ProductHasProductPhoto`.`productVariantId`
                                        FROM `ProductHasProductPhoto`), 'sì', 'no')                     AS hasPhotos,
  `pc`.`id`                                                                                             AS categoryId,
  `pcg`.`name`                                                                                          AS `colorGroup`,
  `psk`.`isOnSale`                                                                                      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 `hasQty`,
  t.name as tags
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 ProductSizeGroup psg ON p.productSizeGroupId = psg.id
  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 `TagTranslation` `t` ON ((`pht`.`tagId` = `t`.`tagId`)))
    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))));
