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`,
    concat(`p`.`itemno`, ' # ', `pv`.`name`)         AS `cpf`,
    `s`.`name`                                       AS `shop`,
    `p`.`externalId`                                 AS `externalId`,
    `pb`.`name`                                      AS `brand`,
    `ps`.`name`                                      AS `status`,
    `p`.`creationDate`                               AS `creationDate`,
    `s`.`id`                                         AS `shopId`,
    `pht`.`slug`                                       AS `tag`,
    `pc`.`slug`                                      AS `slug`,
    `dp`.`extId`                                     AS `extId`,
    `ds`.`extSkuId`                                  AS `extSkuId`,
    `pcg`.`name`                                      AS `colorGroup`

  FROM ((((((((((((((`Product` `p`
    JOIN `ProductSeason` `pse` ON `p`.`productSeasonId` = `pse`.`id`)
    JOIN `ProductVariant` `pv`) JOIN `ProductBrand` `pb`) JOIN `ProductStatus` `ps`) JOIN `ShopHasProduct` `sp`) JOIN
    `Shop` `s`) JOIN `ProductCategory` `pc`) JOIN `ProductHasProductCategory` `ppc`)
    #start join color group
    left join `ProductHasProductColorGroup` as `phcg` on `phcg`.`productId` = `p`.`id`
                                                         AND `phcg`.`productVariantId` = `p`.`productVariantId`
             ) JOIN `ProductColorGroup` as `pcg` ON `phcg`.`productColorGroupId` = `pcg`.`id`)
    #end color group
    LEFT JOIN `vBluesealProductGroupedTag` as `pht`
      ON (((`p`.`id` = `pht`.`productId`) AND (`p`.`productVariantId` = `pht`.`productVariantId`))))) LEFT JOIN `DirtyProduct` `dp`
      ON (((`p`.`id` = `dp`.`productId`) AND (`p`.`productVariantId` = `dp`.`productVariantId`) AND
           (`sp`.`shopId` = `dp`.`shopId`)))) LEFT JOIN `DirtySku` `ds` ON ((`dp`.`id` = `ds`.`dirtyProductId`)))
  WHERE ((`p`.`productVariantId` = `pv`.`id`) AND (`p`.`productBrandId` = `pb`.`id`) AND
         (((`p`.`id` = `sp`.`productId`) AND (`p`.`productVariantId` = `sp`.`productVariantId`)) OR
          (isnull(`sp`.`productId`) AND isnull(`sp`.`productVariantId`))) AND (`sp`.`shopId` = `s`.`id`) AND
         (`ps`.`id` = `p`.`productStatusId`) AND (`pc`.`id` = `ppc`.`productCategoryId`) AND
         (((`p`.`id` = `ppc`.`productId`) AND (`p`.`productVariantId` = `ppc`.`productVariantId`)) OR
          (isnull(`ppc`.`productId`) AND isnull(`ppc`.`productVariantId`)))) AND `pcg`.`langId` = 1;


CREATE OR REPLACE VIEW vBluesealOrders AS
  SELECT
    `o`.`id`                                               AS `id`,
    concat(`ud`.`name`, ' ', `ud`.`surname`)               AS `user`,
    `ud`.`name`                                            AS `name`,
    `ud`.`surname`                                         AS `surname`,
    `u`.`email`                                            AS `email`,
    `o`.`orderDate`                                        AS `orderDate`,
    `o`.`lastUpdate`                                       AS `lastUpdate`,
    concat(`ol`.`productId`, '-', `ol`.`productVariantId`) AS `product`,
    `s`.`title`                                            AS `shop`,
    `os`.`title`                                           AS `status`,
    `o`.`status`                                           AS `statusCode`,
    `opm`.`name`                                           AS `payment`,
    `ols`.`title`                                          AS `orderLineStatus`,
    `pb`.name                                              AS productBrand
  FROM ((((((((((`Order` `o`
    JOIN `User` `u`) JOIN `UserDetails` `ud`) JOIN `OrderPaymentMethod` `opm`) JOIN `OrderStatus` `os`) JOIN
    `OrderStatusTranslation` `oshl`) JOIN `OrderLine` `ol`) JOIN `Shop` `s`) JOIN `OrderLineStatus` `ols`) JOIN
    Product p) JOIN ProductBrand pb)
  WHERE ((`o`.`userId` = `u`.`id`) AND (`ud`.`userId` = `u`.`id`) AND (`o`.`orderPaymentMethodId` = `opm`.`id`) AND
         (`o`.`status` = `os`.`code`) AND (`o`.`status` LIKE 'ORD%') AND (`oshl`.`orderStatusId` = `os`.`id`) AND
         (`ol`.`orderId` = `o`.`id`) AND (`s`.`id` = `ol`.`shopId`) AND ol.productId = p.id AND
         ol.productVariantId = p.productVariantId AND p.productBrandId = pb.id AND
  (`ol`.`status` = convert(`ols`.`code` USING utf8)));