CREATE TABLE `CartType` (
  `id` TINYINT(3) UNSIGNED NOT NULL,
  `code` ENUM('CART','CART_MERGED','CART_ORDERED','WISH','TRN') NOT NULL,
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
)
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
;

CREATE TABLE `Cart` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `orderPaymentMethodId` INT(10) UNSIGNED NULL DEFAULT NULL,
  `couponId` INT(10) UNSIGNED NULL DEFAULT NULL,
  `userId` INT(10) UNSIGNED NULL DEFAULT NULL,
  `cartTypeId` TINYINT(4) UNSIGNED NOT NULL DEFAULT '1',
  `billingAddressId` INT(10) UNSIGNED NULL DEFAULT NULL,
  `shipmentAddressId` INT(10) UNSIGNED NULL DEFAULT NULL,
  `lastUpdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `couponId` (`couponId`),
  INDEX `fk_Cart_OrderPaymentMethod1_idx` (`orderPaymentMethodId`),
  INDEX `fk_Cart_Coupon1_idx` (`couponId`),
  INDEX `fk_Cart_User1_idx` (`userId`),
  INDEX `fk_billingAddressId` (`billingAddressId`),
  INDEX `fk_shipmentAddressId` (`shipmentAddressId`),
  INDEX `cartStatusId` (`cartTypeId`),
  CONSTRAINT `fk_Cart_CartType` FOREIGN KEY (`cartTypeId`) REFERENCES `CartType` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_Cart_Coupon1` FOREIGN KEY (`couponId`) REFERENCES `Coupon` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_Cart_billingAddressId` FOREIGN KEY (`billingAddressId`) REFERENCES `UserAddress` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_Cart_shipmentAddressId` FOREIGN KEY (`shipmentAddressId`) REFERENCES `UserAddress` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_Cart_userId_User_id` FOREIGN KEY (`userId`) REFERENCES `User` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `fk_Cartr_OrderPaymentMethod1` FOREIGN KEY (`orderPaymentMethodId`) REFERENCES `OrderPaymentMethod` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  COLLATE='utf8_general_ci'
  ENGINE=InnoDB
  AUTO_INCREMENT=7
;

CREATE TABLE `CartHistory` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cartId` INT(10) UNSIGNED NOT NULL,
  `event` VARCHAR(255) NOT NULL,
  `description` TEXT NULL,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `cartId`),
  INDEX `fk_cartHistory_cart` (`cartId`),
  CONSTRAINT `fk_cartHistory_cart` FOREIGN KEY (`cartId`) REFERENCES `Cart` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  ENGINE=InnoDB
;

CREATE TABLE `CartLine` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `cartId` INT(10) UNSIGNED NOT NULL,
  `productId` INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  `productSizeId` INT(10) UNSIGNED NOT NULL,
  `shopId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `cartId`),
  INDEX `cartId` (`cartId`),
  INDEX `productId_productVariantId_shopId_productSizeId` (`productId`, `productVariantId`, `productSizeId`, `shopId`),
  CONSTRAINT `FK1_CartLine_Cart_id` FOREIGN KEY (`cartId`) REFERENCES `Cart` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK2_CartLine_ProductSku` FOREIGN KEY (`productId`, `productVariantId`, `productSizeId`, `shopId`) REFERENCES `ProductSku` (`productId`, `productVariantId`, `productSizeId`, `shopId`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  ENGINE=InnoDB
;
DROP TRIGGER IF EXISTS CartLine_BEFORE_INSERT;
CREATE TRIGGER `CartLine_BEFORE_INSERT` BEFORE INSERT ON `CartLine` FOR EACH ROW BEGIN
  SELECT COALESCE(MAX(`id`) + 1, 1) INTO @`id` FROM CartLine WHERE `cartId` = NEW.`cartId`;
  SET NEW.`id` = @`id`;
  UPDATE Cart set lastUpdate = CURRENT_TIMESTAMP WHERE Cart.id = NEW.`cartId`;
END;

DROP TRIGGER IF EXISTS CartLine_BEFORE_DELETE;
CREATE TRIGGER `CartLine_BEFORE_DELETE` BEFORE DELETE ON `CartLine` FOR EACH ROW BEGIN
  UPDATE Cart set lastUpdate = CURRENT_TIMESTAMP WHERE Cart.id = OLD.`cartId`;
END;

DROP TRIGGER IF EXISTS CartLine_BEFORE_UPDATE;
CREATE TRIGGER `CartLine_BEFORE_UPDATE` BEFORE UPDATE ON `CartLine` FOR EACH ROW BEGIN
  UPDATE Cart set lastUpdate = CURRENT_TIMESTAMP WHERE Cart.id = NEW.`cartId`;
END;

CREATE TABLE `UserSessionHasCart` (
  `userSessionId` INT(10) UNSIGNED NOT NULL,
  `cartId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`userSessionId`, `cartId`),
  INDEX `userSessionId` (`userSessionId`),
  INDEX `cartId` (`cartId`),
  CONSTRAINT `FK1_UserSessionHasCart_UserSession` FOREIGN KEY (`userSessionId`) REFERENCES `UserSession` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK2_UserSessionHasCart_Cart` FOREIGN KEY (`cartId`) REFERENCES `Cart` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  ENGINE=InnoDB
;

INSERT INTO CartType (id, code, name) VALUES (1, 'CART', 'Carrello');
INSERT INTO CartType (id, code, name) VALUES (2, 'CART_MERGED', 'Carrello Mergiato');
INSERT INTO CartType (id, code, name) VALUES (3, 'CART_ORDERED', 'Carrello Ordinato');
INSERT INTO CartType (id, code, name) VALUES (4, 'WISH', 'WishList');
INSERT INTO CartType (id, code, name) VALUES (5, 'TRN', 'Trasformazione');

DROP TABLE UserSessionHasOrder;

set FOREIGN_KEY_CHECKS = 0;
DELETE ol, oh, o
FROM `Order` o
  LEFT JOIN OrderLine ol ON o.id = ol.orderId
  LEFT JOIN OrderHistory oh on o.id = oh.orderId
WHERE o.status IN ('CRT', 'CRT_MRG');
set FOREIGN_KEY_CHECKS = 1;

ALTER TABLE `Order`
  ADD COLUMN `cartId` INT(10) UNSIGNED NULL DEFAULT NULL AFTER `userId`,
  DROP COLUMN `marketplaceId`,
  DROP COLUMN `marketplaceAccountId`,
  DROP FOREIGN KEY `fk_order_marketplaceAccount_ids`,
  ADD CONSTRAINT `fk_cartId_Cart_id` FOREIGN KEY (`cartId`) REFERENCES `Cart` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE `OrderLine`
  CHANGE COLUMN `lastUpdate` `lastUpdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `creationDate`;

ALTER TABLE `Order`
  CHANGE COLUMN `lastUpdate` `lastUpdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `paymentDate`;

DROP TRIGGER IF EXISTS OrderLine_BEFORE_INSERT;
CREATE TRIGGER `OrderLine_BEFORE_INSERT` BEFORE INSERT ON `OrderLine` FOR EACH ROW BEGIN
  SELECT COALESCE(MAX(`id`) + 1, 1) INTO @`id` FROM OrderLine WHERE `orderId` = NEW.`orderId`;
  SET NEW.`id` = @`id`;
  UPDATE `Order` set lastUpdate = CURRENT_TIMESTAMP where `Order`.id = NEW.orderId;
END;

DROP TRIGGER IF EXISTS OrderLine_BEFORE_DELETE;
CREATE TRIGGER `OrderLine_BEFORE_DELETE` BEFORE DELETE ON `OrderLine` FOR EACH ROW BEGIN
  UPDATE `Order` set lastUpdate = CURRENT_TIMESTAMP WHERE `Order`.id = OLD.`orderId`;
END;

DROP TRIGGER IF EXISTS OrderLine_BEFORE_UPDATE;
CREATE TRIGGER `OrderLine_BEFORE_UPDATE` BEFORE UPDATE ON `OrderLine` FOR EACH ROW BEGIN
  UPDATE `Order` set lastUpdate = CURRENT_TIMESTAMP WHERE `Order`.id = NEW.`orderId`;
END;