ALTER TABLE `Shipment`
  ADD COLUMN `fromAddressId` INT UNSIGNED NULL AFTER `declaredValue`,
  ADD COLUMN `toAddressId` INT UNSIGNED NULL AFTER `fromAddressId`,
  ADD COLUMN `note` varchar(255) null after `toAddressId`,
  ADD CONSTRAINT `fk_from_addressBook` FOREIGN KEY (`fromAddressId`) REFERENCES `AddressBook` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  ADD CONSTRAINT `fk_to_addressBook` FOREIGN KEY (`toAddressId`) REFERENCES `AddressBook` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

CREATE TABLE `ShopHasAddressBook` (
  `shopId` INT(10) UNSIGNED NOT NULL,
  `addressBookId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`shopId`, `addressBookId`),
  INDEX `FK2i_shab_addressBookId` (`addressBookId`),
  INDEX `FK1i_shab_shopId` (`shopId`),
  CONSTRAINT `FK1_shab_shopId` FOREIGN KEY (`shopId`) REFERENCES `Shop` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK2_shab_addressBookId` FOREIGN KEY (`addressBookId`) REFERENCES `AddressBook` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
  ENGINE=InnoDB
;

ALTER TABLE `AddressBook`
  ADD COLUMN `name` VARCHAR(50) NULL AFTER `id`;

ALTER TABLE `Shipment`
  ALTER `shipmentDate` DROP DEFAULT;
ALTER TABLE `Shipment`
  CHANGE COLUMN `shipmentDate` `predictedShipmentDate` DATETIME NOT NULL AFTER `trackingNumber`,
  ADD COLUMN `shipmentDate` DATETIME NOT NULL AFTER `predictedShipmentDate`,
  ADD COLUMN `predictedDeliveryDate` DATETIME NOT NULL AFTER `shipmentDate`,
  CHANGE COLUMN `arrivalDate` `deliveryDate` DATETIME NULL DEFAULT NULL AFTER `predictedDeliveryDate`;

ALTER TABLE `Shipment`
ALTER `shipmentDate` DROP DEFAULT,
ALTER `predictedDeliveryDate` DROP DEFAULT;
ALTER TABLE `Shipment`
  CHANGE COLUMN `shipmentDate` `shipmentDate` DATETIME NULL AFTER `predictedShipmentDate`,
  CHANGE COLUMN `predictedDeliveryDate` `predictedDeliveryDate` DATETIME NULL AFTER `shipmentDate`,
  CHANGE COLUMN `creationDate` `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `deliveryDate`;

/** implementazione cancellazione spedizioni e tempistiche */
ALTER TABLE `Shipment`
    add COLUMN  `cancellationDate` DATETIME NULL,
    ADD COLUMN `shipmentFaultId` TINYINT UNSIGNED NULL,
  ADD CONSTRAINT `fk_shipmentFault_shipment` FOREIGN KEY (`shipmentFaultId`) REFERENCES `ShipmentFault`(`id`)
  ON UPDATE NO ACTION ON DELETE NO ACTION;

CREATE TABLE `ShipmentFault` (
  `id` TINYINT UNSIGNED PRIMARY KEY,
  `code` VARCHAR(50) NULL,
  `description` varchar(256) NULL,
  `userType` varchar(64) NOT NULL
);

CREATE TABLE `OrderLineStatistics` (
  `orderLineId` INT(10) UNSIGNED,
  `orderId` INT(10) UNSIGNED,
  `friendResponseDays` TINYINT UNSIGNED,
  `friendTimeDays` TINYINT UNSIGNED,
  `carrierFromFriendDays` TINYINT UNSIGNED,
  PRIMARY KEY (`orderLineId`, `orderId`),
  CONSTRAINT `fk_OrderLineFriendStats_OrderLine` FOREIGN KEY (`orderLineId`, `orderId`) REFERENCES `OrderLine` (`id`, `orderId`)
    ON UPDATE NO ACTION ON DELETE NO ACTION
);