CREATE TABLE `MarketplaceAccount` (
  `id`            INT(10) UNSIGNED NOT NULL,
  `marketplaceId` INT(10) UNSIGNED NOT NULL,
  `name`          VARCHAR(50)      NOT NULL,
  `config`        TEXT             NULL,
  PRIMARY KEY (`id`, `marketplaceId`),
  CONSTRAINT `fk_marketplaceId_marketplace_id` FOREIGN KEY (`marketplaceId`) REFERENCES `Marketplace` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;

CREATE TABLE `MarketplaceAccountHasProduct` (
  `productId`            INT(10) UNSIGNED    NOT NULL,
  `productVariantId`     INT(10) UNSIGNED    NOT NULL,
  `marketplaceId`        INT(10) UNSIGNED    NOT NULL,
  `marketplaceAccountId` INT(10) UNSIGNED    NOT NULL,
  `marketplaceProductId` VARCHAR(50)         NULL,
  `priceModifier`        INT(10)             NULL,
  `isToWork`             TINYINT(1) UNSIGNED NOT NULL  DEFAULT 1,
   hasError              TINYINT(1) UNSIGNED NOT NULL  DEFAULT 0,
  `isRevised`            TINYINT(1) UNSIGNED NOT NULL  DEFAULT 0,
  `creationDate`         TIMESTAMP           NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  `lastUpdate`           TIMESTAMP           NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `insertionDate`        TIMESTAMP           NULL,
  `publishDate`          TIMESTAMP           NULL,
  `lastRevised`          TIMESTAMP           NULL,
   lastResponse text null
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;


ALTER TABLE `MarketplaceAccountHasProduct`
  ADD PRIMARY KEY (`productId`, `productVariantId`, `marketplaceId`, `marketplaceAccountId`),
  ADD CONSTRAINT `fk_productInMarketplace_product_id` FOREIGN KEY (`productId`, `productVariantId`) REFERENCES `Product` (`id`, `productVariantId`)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION,
  ADD CONSTRAINT `fk_productInMarketplace_marketplaceAccount_id` FOREIGN KEY (`marketplaceId`, `marketplaceAccountId`) REFERENCES `MarketplaceAccount` (`marketplaceId`, `id`)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

ALTER TABLE Marketplace
  ADD icon VARCHAR(100) NULL;

CREATE TABLE `MarketplaceAccountCategory` (
  `marketplaceId`         INT(10) UNSIGNED    NOT NULL,
  `marketplaceAccountId`  INT(10) UNSIGNED    NOT NULL,
  `marketplaceCategoryId` VARCHAR(50)         NOT NULL,
  `name`                  VARCHAR(50)         NOT NULL,
  `path`                  VARCHAR(255)        NOT NULL,
  isRelevant              TINYINT(1) UNSIGNED NOT NULL DEFAULT 1,
  `etc`                   TEXT                NULL,
  PRIMARY KEY (`marketplaceId`, `marketplaceAccountId`, `marketplaceCategoryId`)
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;
ALTER TABLE `MarketplaceAccountCategory`
  ADD CONSTRAINT `fk_marketplaceAccountCategory_id_account_marketplaceAccount_id` FOREIGN KEY (`marketplaceId`, `marketplaceAccountId`) REFERENCES `MarketplaceAccount` (`marketplaceId`,`id`)
  ON UPDATE NO ACTION
  ON DELETE NO ACTION;

create TABLE `ProductCategoryHasMarketplaceAccountCategory` (
  `productCategoryId`            INT(10) UNSIGNED NOT NULL,
  `marketplaceId`                INT(10) UNSIGNED NOT NULL,
  `marketplaceAccountId`         INT(10) UNSIGNED NOT NULL,
  `marketplaceAccountCategoryId` VARCHAR(50)      NOT NULL,
  PRIMARY KEY (`productCategoryId`, `marketplaceId`, `marketplaceAccountId`, `marketplaceAccountCategoryId`),
  CONSTRAINT `fk_productCategoryHasMarketplace_categoryId_productcategory_id` FOREIGN KEY (`productCategoryId`) REFERENCES `ProductCategory` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION,
  CONSTRAINT `fk_productCategoryHasMarketplace_marketplaceCategory` FOREIGN KEY (`marketplaceId`, `marketplaceAccountId`, `marketplaceAccountCategoryId`) REFERENCES `MarketplaceAccountCategory` (`marketplaceId`, `marketplaceAccountId`, `marketplaceCategoryId`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;

INSERT INTO MarketplaceAccountCategory (marketplaceId, marketplaceAccountId, marketplaceCategoryId, name, path, isRelevant)
SELECT marketplaceId, 2, marketplaceCategoryId, marketplaceCategoryName, marketplaceCategoryPath, isRelevant FROM MarketplaceCategoryLookup WHERE marketplaceId = 3;

CREATE OR REPLACE VIEW vBluesealMarketplaceCategory AS
  SELECT
    `m`.`id`    AS `marketplaceId`,
    ma.id as marketplaceAccountId,
    `mac`.`marketplaceCategoryId`                        AS `marketplaceCategoryId`,
    m.name as marketplace,
    ma.name as marketplaceAccount,
    mac.name as marketplaceAccountCategory,
    mac.path as marketplaceAccountPath,
    isRelevant,
    `pc`.`slug`                                         AS `internalCategory`,
    `mac`.`name`                      AS `marketplaceCategory`
  FROM ((`Marketplace` `m`
    JOIN `MarketplaceAccount` `ma` ON ((`m`.`id` = `ma`.`marketplaceId`)))
    join MarketplaceAccountCategory mac on ma.marketplaceId = mac.marketplaceId and ma.id = mac.marketplaceAccountId
    left join ProductCategoryHasMarketplaceAccountCategory pchmac on mac.marketplaceAccountId = pchmac.marketplaceAccountId
                                                                     and mac.marketplaceId = pchmac.marketplaceAccountId
                                                                     and mac.marketplaceCategoryId = pchmac.marketplaceAccountCategoryId
    left JOIN `ProductCategory` `pc`
      ON ((`pc`.`id` = `pchmac`.productCategoryId)));
