-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Table `StorehouseOperationCause`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `StorehouseOperationCause` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(90) NOT NULL,
  `description` VARCHAR(255) NULL,
  `sign` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`id`))
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `Storehouse`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Storehouse` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shopId` INT(10) UNSIGNED NOT NULL,
  `name` VARCHAR(45) NULL,
  `countryId` INT(10) UNSIGNED NULL,
  `city` VARCHAR(45) NULL,
  `address` VARCHAR(255) NULL,
  `number` VARCHAR(45) NULL,
  `phone` VARCHAR(20) NULL,
  `mail` VARCHAR(95) NULL,
  PRIMARY KEY (`id`, `shopId`),
  INDEX `fk_Storehouse_Shop1_idx` (`shopId` ASC),
  INDEX `fk_Storehouse_Country1_idx` (`countryId` ASC),
  CONSTRAINT `fk_Storehouse_Shop1`
  FOREIGN KEY (`shopId`)
  REFERENCES `Shop` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Storehouse_Country1`
  FOREIGN KEY (`countryId`)
  REFERENCES `Country` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `StorehouseOperation`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `StorehouseOperation` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `shopId` INT(10) UNSIGNED NOT NULL,
  `storehouseId` INT(10) UNSIGNED NOT NULL,
  `storehouseOperationCauseId` INT(10) UNSIGNED NOT NULL,
  `userId` INT(10) UNSIGNED NOT NULL,
  `notes` VARCHAR(255) NULL,
  `operationDate` TIMESTAMP NULL,
  `creationDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `shopId`, `storehouseId`),
  INDEX `fk_StorehouseOperation_StockHandlingCause1_idx` (`storehouseOperationCauseId` ASC),
  INDEX `fk_StorehouseOperation_User1_idx` (`userId` ASC),
  INDEX `fk_StorehouseOperation_Storehouse1_idx` (`storehouseId` ASC, `shopId` ASC),
  CONSTRAINT `fk_StorehouseOperation_StockHandlingCause1`
  FOREIGN KEY (`storehouseOperationCauseId`)
  REFERENCES `StorehouseOperationCause` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_StorehouseOperation_User1`
  FOREIGN KEY (`userId`)
  REFERENCES `User` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_StorehouseOperation_Storehouse1`
  FOREIGN KEY (`storehouseId` , `shopId`)
  REFERENCES `Storehouse` (`id` , `shopId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;



-- -----------------------------------------------------
-- Table `StorehouseOperationLine`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `StorehouseOperationLine` (
  `storehouseOperationId` INT(10) UNSIGNED NOT NULL,
  `shopId` INT(10) UNSIGNED NOT NULL,
  `storehouseId` INT(10) UNSIGNED NOT NULL,
  `productId` INT(10) UNSIGNED NOT NULL,
  `productVariantId` INT(10) UNSIGNED NOT NULL,
  `productSizeId` INT(10) UNSIGNED NOT NULL,
  `qty` INT NOT NULL,
  PRIMARY KEY (`storehouseOperationId`, `shopId`, `storehouseId`, `productId`, `productVariantId`, `productSizeId`),
  INDEX `fk_StorehouseOperation_idx` (`storehouseOperationId` ASC, `shopId` ASC, `storehouseId` ASC),
  INDEX `fk_StorehouseOperationLine_ProductSku1_idx` (`productId` ASC, `productVariantId` ASC, `productSizeId` ASC),
  CONSTRAINT `fk_StorehouseOperation`
  FOREIGN KEY (`storehouseOperationId` , `shopId` , `storehouseId`)
  REFERENCES `StorehouseOperation` (`id` , `shopId` , `storehouseId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_StorehouseOperationLine_ProductSku1`
  FOREIGN KEY (`productId` , `productVariantId` , `productSizeId`)
  REFERENCES `ProductSku` (`productId` , `productVariantId` , `productSizeId`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
  ENGINE = InnoDB;




CREATE TRIGGER `StorehouseOperation_BEFORE_INSERT` BEFORE INSERT ON `StorehouseOperation` FOR EACH ROW BEGIN
  SELECT COALESCE(MAX(`id`) + 1, 1) INTO @v FROM Storehouse WHERE `shopId` = NEW.`shopId`;
  SET NEW.`id` = @v;
END;
