-- Modificare prima le tabelle preesistenti

/*ALTER TABLE "OrderLine"
  DROP orderLineFriendPaymentStatusId,
  DROP orderLineFriendPaymentDate;

DROP TABLE OrderLineFriendPaymentStatus;
*/


-- MySQL Workbench Forward Engineering


-- ------------------------------------------------------
-- Table `AddressBook`
-- ------------------------------------------------------

CREATE TABLE IF NOT EXISTS `AddressBook` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `subject` VARCHAR(1024) NOT NULL,
  `address` VARCHAR(1024) NOT NULL,
  `extra` VARCHAR(1024) NULL,
  `city` VARCHAR(256) NOT NULL,
  `countryId` INT(10) UNSIGNED NOT NULL,
  `postcode` VARCHAR(20) NOT NULL,
  `phone` varchar(30) NULL,
  `cellphone` VARCHAR(30) NULL,
  `vatNumber` VARCHAR(30) NULL,
  `province` varchar(2) NULL,
  `checksum` char(32) NULL
);


-- -----------------------------------------------------
-- Alter `Shop`
-- -----------------------------------------------------

ALTER TABLE Shop ADD addressBookId INT(10) UNSIGNED NULL  AFTER owner;
ALTER TABLE Shop ADD CONSTRAINT `fk_Shop_addressBook1`
  FOREIGN KEY (`addressBookId`)
  REFERENCES `AddressBook` (`id`);

-- -----------------------------------------------------
-- Table `barbagalloshop_dev`.`InvoiceType`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InvoiceType` (
  `id` TINYINT UNSIGNED NOT NULL,
  `code` VARCHAR(60) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `isShop` TINYINT NOT NULL DEFAULT 0,
  `isActive` TINYINT(1) NOT NULL COMMENT 'in 1 incasso, 0 pagamenti',
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `barbagalloshop_dev`.`InvoiceSectional`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InvoiceSectional` (
  `id` SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,
  `invoiceTypeId` TINYINT UNSIGNED NOT NULL,
  `userAddressRecipientId` INT(10) UNSIGNED NULL,
  `shopRecipientId` INT(10) UNSIGNED NULL,
  `code` VARCHAR(6) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`invoiceTypeId`, `userId`, `shopId`, `code`),
  CONSTRAINT `fk_InvoiceSectional_InvoiceTypeId`
  FOREIGN KEY (`invoiceTypeId`)
  REFERENCES `InvoiceType`(`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_InvoiceSectional_shopId`
  FOREIGN KEY (`shopId`)
  REFERENCES `Shop`(`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_InvoiceSectional_UserId`
  FOREIGN KEY (`userId`)
  REFERENCES `User`(`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Document` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` INT(10) UNSIGNED NOT NULL,
  `userAddressRecipientId` INT(10) UNSIGNED NULL,
  `shopRecipientId` INT(10) UNSIGNED NULL,
  `number` VARCHAR(99) NOT NULL,
  `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `invoiceTypeId` TINYINT UNSIGNED NOT NULL,
  `paymentDate` TIMESTAMP,
  `paydAmount` DECIMAL(9,2),
  `paymentExpectedDate` TIMESTAMP,
  `note` VARCHAR(1024) NULL,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `carrierId` INT(10) UNSIGNED,
  `totalWithVat` FLOAT UNSIGNED NOT NULL,
  `year` YEAR NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_InvoiceNew_InvoiceType1_idx` (`invoiceTypeId` ASC),
  CONSTRAINT `fk_invoiceNew_User`
  FOREIGN KEY (`userId`)
  REFERENCES  `User` (`id`),
  CONSTRAINT `fk_InvoiceNew_InvoiceType1`
  FOREIGN KEY (`invoiceTypeId`)
  REFERENCES `InvoiceType` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_invoiceNew_Carrier1`
  FOREIGN KEY (`carrierId`)
  REFERENCES `Carrier` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `InvoiceNumber`
-- ------------------------------------------------------

CREATE TABLE IF NOT EXISTS `InvoiceNumber` (
  `invoiceId` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `invoiceNumber` INT UNSIGNED NOT NULL,
  `invoiceSectionalId` SMALLINT UNSIGNED NOT NULL,
  `year` YEAR NOT NULL,
  CONSTRAINT `fk_InvoiceNumber_sectional` FOREIGN KEY (invoiceSectionalId) REFERENCES InvoiceSectional (id)
    ON DELETE NO ACTION ON UPDATE NO ACTION
)
  ENGINE = InnoDB
  DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `InvoiceBin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InvoiceBin` (
  `invoiceId` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `fileName` VARCHAR(100) NOT NULL,
  `bin` MEDIUMBLOB NOT NULL,
  CONSTRAINT `fk_InvoiceBin_InvoiceNew1`
  FOREIGN KEY (`invoiceId`)
  REFERENCES Document (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `InvoiceLine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InvoiceLine` (
  `id` INT(10) UNSIGNED AUTO_INCREMENT NOT NULL,
  `invoiceId` INT(10) UNSIGNED NOT NULL,
  `description` VARCHAR(256) NOT NULL,
  `priceNoVat` FLOAT NOT NULL,
  `vat` INT NOT NULL,
  `price` FLOAT NOT NULL,
  PRIMARY KEY (`id`, `invoiceId`),
  CONSTRAINT `fk_InvoiceLine_InvoiceNew1`
  FOREIGN KEY (`invoiceId`)
  REFERENCES Document (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `InvoiceLineHasOrderLine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `InvoiceLineHasOrderLine` (
  `invoiceLineId` INT(10) UNSIGNED NOT NULL,
  `invoiceLineInvoiceId` INT(10) UNSIGNED NOT NULL,
  `orderLineId` INT(10) UNSIGNED NOT NULL,
  `orderLineOrderId` INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`invoiceLineId`, `invoiceLineInvoiceId`, `orderLineId`, `orderLineOrderId`),
  INDEX `fk_InvoiceLine_has_OrderLine_OrderLine1_idx` (`orderLineId` ASC, `orderLineOrderId` ASC),
  INDEX `fk_InvoiceLine_has_OrderLine_InvoiceLine1_idx` (`invoiceLineId` ASC, `invoiceLineInvoiceId` ASC),
  CONSTRAINT `fk_InvoiceLine_has_OrderLine_InvoiceLine1`
  FOREIGN KEY (`invoiceLineId` , `invoiceLineInvoiceId`)
  REFERENCES `InvoiceLine` (`id` , `invoiceId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_InvoiceLine_has_OrderLine_OrderLine1`
  FOREIGN KEY (`orderLineId` , `orderLineOrderId`)
  REFERENCES `OrderLine` (`id` , `orderId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;

-- TRIGGER

/*CREATE TRIGGER `invoiceNumber_BEFORE_INSERT` BEFORE INSERT ON `InvoiceNumber` FOR EACH ROW
BEGIN
  SELECT COALESCE(MAX(`invoiceNumber`) + 1, 1) INTO @`numb` FROM `InvoiceNumber`
  WHERE `invoiceSectionalId` = NEW.`invoiceSectionalId`;
  SET NEW.`invoiceNumber` = @`numb`;
END;*/

CREATE TRIGGER `invoiceLine_BEFORE_INSERT` BEFORE INSERT ON `InvoiceLine` FOR EACH ROW
  BEGIN
    SELECT COALESCE(MAX(`id`) + 1, 1) INTO @`idRow` FROM `InvoiceLine`
    WHERE `invoiceId` = NEW.`invoiceId`;
    SET NEW.`id` = @`idRow`;
  END;

