CREATE TABLE IF NOT EXISTS `ProductName` (
  `id` INT(10) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `langId` INT(10) UNSIGNED NOT NULL,
  `translation` VARCHAR(255) NOT NULL,
  INDEX `prodName_name`(`name`)
);

Create or replace view vBluesealProductIncomplete as
SELECT
  `p`.`id`                                              AS `id`,
  `p`.`productVariantId`                                AS `productVariantId`,
  `sp`.`shopId`                                         AS `shopId`,
  `s`.`name`                                            AS `shop`,
  concat(`p`.`itemno`, ' # ', `pv`.`name`)              AS `code`,
  `p`.`productSeasonId`                                 AS `season`,
  `p`.`externalId`                                      AS `externalId`,
  `pb`.`name`                                           AS `brand`,
  sum(`pk`.`stockQty`)                                  AS `totalQty`,
  `ps`.`name`                                           AS `status`,
  `p`.`creationDate`                                    AS `creationDate`,
  concat_ws(' ', if(isnull(`p`.`productSizeGroupId`), 'E400', NULL), if(isnull(`p`.`productSeasonId`), 'E410', NULL),
            if((`p`.`productBrandId` = 1), 'E420', NULL), if(isnull(`p`.`productSheetPrototypeId`), 'E430', NULL),
            if(isnull(`pc`.`productColorGroupId`), 'E440', NULL), if(isnull(`pp`.`productId`), 'E450', NULL),
            if(isnull(`pk`.`productId`), 'E460', NULL), if(isnull(`pn`.`name`), 'E470', NULL),
            if(isnull(`pd`.`productId`), 'E480', NULL)) AS `problems`
FROM ((((((((((`Product` `p`
  LEFT JOIN `ShopHasProduct` `sp` ON (((`p`.`id` = `sp`.`productId`) AND (`p`.`productVariantId` = `sp`.`productVariantId`))))
  LEFT JOIN `ProductHasProductColorGroup` `pc` ON (((`pc`.`productId` = `p`.`id`) AND (`pc`.`productVariantId` = `p`.`productVariantId`))))
  LEFT JOIN `ProductNameTranslation` `pn` ON (((`pn`.`productId` = `p`.`id`) AND (`pn`.`productVariantId` = `p`.`productVariantId`))))
  LEFT JOIN `ProductBrand` `pb` ON ((`p`.`productBrandId` = `pb`.`id`)))
  LEFT JOIN `Shop` `s` ON ((`s`.`id` = `sp`.`shopId`)))
  LEFT JOIN `ProductVariant` `pv` ON ((`p`.`productVariantId` = `pv`.`id`)))
  LEFT JOIN `ProductStatus` `ps` ON ((convert(`ps`.`id` USING utf8) = `p`.`productStatusId`)))
  LEFT JOIN `ProductSku` `pk` ON (((`p`.`id` = `pk`.`productId`) AND (`p`.`productVariantId` = `pk`.`productVariantId`))))
  LEFT JOIN `ProductHasProductPhoto` `pp` ON (((`p`.`id` = `pp`.`productId`) AND (`p`.`productVariantId` = `pp`.`productVariantId`))))
  LEFT JOIN `ProductDescription` `pd` ON (((`p`.`id` = `pd`.`productId`) AND (`p`.`productVariantId` = `pd`.`productVariantId`))))
WHERE ((`ps`.`code` NOT IN ('C', 'N')) AND
       (`pn`.`langId` = 1) AND
       (`pn`.`name` = '') AND (
         isnull(`pc`.`productColorGroupId`) OR
         isnull(`p`.`productSizeGroupId`) OR
         isnull(`p`.`productSeasonId`) OR
         (`p`.`productBrandId` = 1) OR
         isnull(`p`.`productSheetPrototypeId`) OR
         ((`ps`.`code` IN ('W', 'G')) AND
          (`p`.`dummyPicture` <> '0000000000.jpg'))))
GROUP BY `p`.`id`, `p`.`productVariantId`
HAVING (`totalQty` > 0)
ORDER BY `p`.`creationDate` DESC;