CREATE TABLE items (
  `id` BIGINT(13) UNSIGNED ZEROFILL NOT NULL,
  PRIMARY KEY (id)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = latin1;

CREATE TABLE suppliers (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id)
);

CREATE TABLE supplierItems (
  `id`                 INT(10) UNSIGNED              NOT NULL AUTO_INCREMENT,
  `itemId`             BIGINT(13) UNSIGNED ZEROFILL  NOT NULL,
  `supplierId`         INT(10) UNSIGNED              NOT NULL DEFAULT '0',
  `price`              DECIMAL(10, 2)                NOT NULL DEFAULT '0.00',
  `dateCreated`        INT(10) UNSIGNED              NOT NULL DEFAULT '0',
  `dateUpdated`        INT(10) UNSIGNED              NOT NULL DEFAULT '0',
  `supplierItemId`     VARCHAR(32)                   NOT NULL,
  `stock`              MEDIUMINT(9)                  NOT NULL DEFAULT '-1',
  `deliveryTime`       SMALLINT(6)                            DEFAULT '-1',
  `fillingRate`        DECIMAL(3, 2)                 NOT NULL DEFAULT '1.00',
  `status`             ENUM ('available', 'expired') NOT NULL DEFAULT 'available',
  `restricted`         TINYINT(1)                    NOT NULL DEFAULT '0',
  `flag`               TINYINT(1)                    NOT NULL DEFAULT '0',
  `deliveryFailedDate` INT(10)                       NOT NULL DEFAULT '0',
  `dateExpired`        INT(10) UNSIGNED                       DEFAULT NULL,
  `rentalPrice`        DECIMAL(10, 2)                NOT NULL DEFAULT '0.00',
  `rentalRestricted`   TINYINT(1)                    NOT NULL DEFAULT '0',
  `forceRetailPrice`   DECIMAL(10, 2)                NOT NULL DEFAULT '0.00',
  `sourceData`         TEXT                          NOT NULL,
  `discountFactor`     DECIMAL(7, 6) UNSIGNED        NOT NULL DEFAULT '1.000000',
  `supplierMediaId`    VARCHAR(20)                   NOT NULL,
  `supplierGenreId`    VARCHAR(20)                   NOT NULL,
  `minimumQuantity`    SMALLINT(5) UNSIGNED          NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `supplierItemId` (`supplierItemId`, `supplierId`),
  KEY `itemId` (`itemId`),
  KEY `supplierId` (`supplierId`, `status`, `stock`),
  CONSTRAINT FOREIGN KEY (`supplierId`) REFERENCES `suppliers` (`id`),
  CONSTRAINT FOREIGN KEY (`itemId`) REFERENCES `items` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 63839220
  DEFAULT CHARSET = latin1;

DELIMITER //
CREATE TRIGGER `beforeTrigger`
BEFORE INSERT ON `supplierItems`
FOR EACH ROW
  BEGIN

    IF NEW.status = 'expired'
    THEN
      SET NEW.dateExpired = UNIX_TIMESTAMP();
    END IF;

    IF NOT NEW.dateUpdated
    THEN
      SET NEW.dateUpdated = UNIX_TIMESTAMP();
    END IF;

  END
//

DELIMITER ;

INSERT INTO items VALUES (9781234567890);
INSERT INTO suppliers VALUES (10);

INSERT INTO supplierItems (itemId, supplierId, supplierItemId) VALUES (9781234567890, 10, 12435);
INSERT INTO supplierItems (itemId, supplierId, supplierItemId) VALUES (9788711721780, 99, 67890);
