# CREATE OR REPLACE VIEW vBluesealProductNameList AS
#   SELECT
#     #`pc`.`slug`                                          AS `slug`,
#     `pnt`.`productId`                                                     AS `productid`,
#     `pnt`.`productVariantId`                                              AS `productVariantId`,
#     `pnt`.`langId`                                                        AS `langId`,
#     group_concat(DISTINCT concat(`p`.`id`, '-', `p`.`productVariantId`)) AS `code`,
#     group_concat(DISTINCT `pc`.`slug` SEPARATOR ',')                     AS `slug`,
#     `pnt`.`name`                                                          AS `name`,
#     count(`pn`.`productId`)                                              AS `count`
#
#   FROM ((`ProductNameTranslation` AS `pnt` LEFT JOIN `ProductName` AS `pn` ON
#     JOIN `Product` `p` ON `pn`.productId = `p`.`id` AND `pn`.`productVariantId` = `p`.`productVariantId`)
#     LEFT JOIN (`ProductHasProductCategory` `phpc`
#       JOIN `ProductCategory` `pc` ON (`phpc`.`productCategoryId` = `pc`.`id`))
#       ON (((`p`.`id` = `phpc`.`productId`) AND (`p`.`productVariantId` = `phpc`.`productVariantId`)))
#     JOIN `ProductStatus`)
#   WHERE `ProductStatus`.`id` = `p`.`productStatusId` AND `ProductStatus`.`code` IN ('I', 'P', 'A') AND `pn`.`langId` = 1
#   GROUP BY `pnt`.`name`;
# #`pn`.`productId`, `pn`.`productVariantId`
#
#
#
# CREATE OR REPLACE VIEW vBluesealProductNameList AS
#   SELECT * FROM vBluesealProductNameListSubQuery;

CREATE OR REPLACE VIEW vBluesealProductNameList AS

  SELECT
  `pn`.`id` as `id`,
  `pnt`.`productId` as `productId`,
  `pnt`.`productVariantId` as `productVariantId`,
  `pn`.`name` as `name`,
  `pn`.`langId` as `langId`,
  0 as `count`
  FROM (((`ProductName` as `pn`
    JOIN `ProductNameTranslation` as `pnt` on `pn`.`name` = `pnt`.`name` AND `pn`.`langId` = `pnt`.`langId`)
    LEFT JOIN `Product` `p` ON (`pnt`.`productId` = `p`.`id` AND `pnt`.`productVariantId` = `p`.`productVariantId`))
    LEFT JOIN (`ProductHasProductCategory` `phpc`
    JOIN `ProductCategory` `pc` ON `phpc`.`productCategoryId` = `pc`.`id`)
    ON (`p`.`id` = `phpc`.`productId`) AND (`p`.`productVariantId` = `phpc`.`productVariantId`)
    JOIN `ProductStatus` ON `ProductStatus`.`id` = `p`.`productStatusId`) WHERE `pn`.`langId` = 1 AND `ProductStatus`.`code` IN ('P', 'A');

CREATE OR REPLACE VIEW vBluesealProductNameNewList AS
  SELECT
    `pn`.`id` as `id`,
    `pnt`.`productId` as `productId`,
    `pnt`.`productVariantId` as `productVariantId`,
    `pn`.`name` as `name`,
    `pn`.`langId` as `langId`,
    0 as `count`
  FROM ((((`ProductName` as `pn`
    JOIN `ProductNameTranslation` as `pnt` on `pn`.`name` = `pnt`.`name` AND `pn`.`langId` = `pnt`.`langId`)
    JOIN `Product` `p` ON (`pnt`.`productId` = `p`.`id` AND `pnt`.`productVariantId` = `p`.`productVariantId`))
    JOIN `ProductSku` as `ps` ON `ps`.`productId` = `p`.`id` AND `ps`.`productVariantId` = `p`.`productVariantId`)
    JOIN (`ProductHasProductCategory` `phpc`
      JOIN `ProductCategory` `pc` ON `phpc`.`productCategoryId` = `pc`.`id`)
      ON (`p`.`id` = `phpc`.`productId`) AND (`p`.`productVariantId` = `phpc`.`productVariantId`)
    JOIN `ProductStatus` ON `ProductStatus`.`id` = `p`.`productStatusId`)
  WHERE `pn`.`langId` = 1 AND `pnt`.`langId` = 1
        AND `ps`.`stockQty` > 0 AND `p`.`dummyPicture` IS NOT NULL
        AND `p`.`productStatusId` in (5,6,11)


SELECT
  *
FROM ((((`ProductName` as `pn`
  JOIN `ProductNameTranslation` as `pnt` on `pn`.`name` = `pnt`.`name` AND `pn`.`langId` = `pnt`.`langId`)
  JOIN `Product` `p` ON (`pnt`.`productId` = `p`.`id` AND `pnt`.`productVariantId` = `p`.`productVariantId`))
  JOIN `ProductSku` as `ps` ON `ps`.`productId` = `p`.`id` AND `ps`.`productVariantId` = `p`.`productVariantId`)
  JOIN (`ProductHasProductCategory` `phpc`
    JOIN `ProductCategory` `pc` ON `phpc`.`productCategoryId` = `pc`.`id`)
    ON (`p`.`id` = `phpc`.`productId`) AND (`p`.`productVariantId` = `phpc`.`productVariantId`)
  JOIN `ProductStatus` ON `ProductStatus`.`id` = `p`.`productStatusId`)
WHERE `pn`.`langId` = 1 AND `pnt`.`langId` = 1
      AND `ps`.`stockQty` > 0 AND `p`.`dummyPicture` IS NOT NULL
      AND `p`.`productStatusId` in (5,6,11)