# le tabelle da replicare sono `productSheetActual` e `product

CREATE TABLE `ProductSheetModelPrototype` (
  `id` INT(10) unsigned auto_increment,
  `code` VARCHAR(12) NOT NULL ,
  `productSheetPrototypeId` INT(10) UNSIGNED,
  `name` VARCHAR(60) not null unique,
  `productName` VARCHAR(60) not NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_psmp_psp` FOREIGN KEY (`productSheetPrototypeId`) REFERENCES `ProductSheetPrototype`(`id`)
);

CREATE TABLE `ProductSheetModelActual` (
  `productSheetModelPrototypeId` INT(10) unsigned,
  `productDetailLabelId` INT(10) unsigned,
  `productDetailId` INT(10) unsigned,
  PRIMARY KEY(`productSheetModelPrototypeId`,`productDetailLabelId`, `productDetailId`),
  CONSTRAINT `fk_psma_pdl` FOREIGN KEY (`productDetailLabelId`) REFERENCES ProductDetailLabel(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_psma_pd` FOREIGN KEY (`productDetailId`) REFERENCES ProductDetail(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE `ProductSheetModelPrototypeHasProductCategory` (
`productSheetModelPrototypeId` INT(10) UNSIGNED,
`productCategoryId` INT(10) UNSIGNED,
PRIMARY KEY (`productSheetModelPrototypeId`, `productCategoryId`),
CONSTRAINT `fk_psmphp_psmp` FOREIGN KEY (`productSheetModelPrototypeId`) REFERENCES ProductSheetPrototype(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT `fk_psmphp_pc` FOREIGN KEY (`productCategoryId`) REFERENCES  `ProductCategory`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);

DROP TABLE `ProductSheetModelPrototypeHasProductCategory`;
drop Table `ProductSheetModelActual`;
drop Table `ProductSheetModelPrototype`;

ALTER TABLE barbagalloshop_dev.ProductSheetModelPrototypeHasProductCategory DROP PRIMARY KEY;


# view della lista

CREATE OR REPLACE VIEW vBluesealModelList AS
  SELECT
    `p`.`id`          as `id`,
    `p`.`name`        AS `name`,
    `p`.`code`        AS `code`,
    `p`.`productName` AS `productName`,
    `psp`.`name`      AS `prototypeName`,
    GROUP_CONCAT( DISTINCT `pct`.`name`)  AS `categories`,
    GROUP_CONCAT(DISTINCT `pdlt`.`name`) AS `labels`,
    GROUP_CONCAT(DISTINCT `pdt`.`name`) AS `details`
    FROM (((((((((
    `ProductSheetModelPrototype` as `p` JOIN `ProductSheetPrototype` as `psp` on `p`.`productSheetPrototypeId` = `psp`.`id`)
      LEFT JOIN `ProductSheetModelActual` as `a` ON `p`.`id` = `a`.`productSheetModelPrototypeId`)
      JOIN `ProductDetailLabel` as `pdl` on `a`.`productDetailLabelId` = `pdl`.`id`)
      LEFT JOIN `ProductDetailLabelTranslation` as `pdlt` ON `pdlt`.productDetailLabelId = `pdl`.`id`)
      JOIN `ProductDetail` as `pd` ON `pd`.`id` = `a`.`productDetailId`)
      LEFT JOIN `ProductDetailTranslation` as `pdt` on `pdt`.`productDetailId` = `pd`.`id`)
      LEFT JOIN `ProductSheetModelPrototypeHasProductCategory` as `php` ON `php`.`productSheetModelPrototypeId` = `p`.`id`)
      JOIN `ProductCategory` as `pc` ON `pc`.`id` = `php`.`productCategoryId`)
      LEFT JOIN `ProductCategoryTranslation` as `pct` ON `pct`.`productCategoryId` = `pc`.`id`)
      WHERE `pct`.`langId` = 1 AND `pdt`.langId = 1
      GROUP BY `p`.`id`;

