--liquibase formatted sql

--changeset cyril:create bom & process table
CREATE TABLE `bom`
(
    `id`                INT          NOT NULL AUTO_INCREMENT,
    `created`           DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `createdBy`         VARCHAR(30) NULL DEFAULT NULL,
    `version`           INT          NOT NULL DEFAULT '0',
    `modified`          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modifiedBy`        VARCHAR(30) NULL DEFAULT NULL,
    `deleted`           TINYINT(1) NOT NULL DEFAULT '0',
    `code`              VARCHAR(30)  NOT NULL,
    `name`              VARCHAR(30)  NOT NULL,
    `description`       VARCHAR(100) NOT NULL,
    `outputQty`         DECIMAL(14, 2) NULL,
    `uomId`             INT NULL,
    `m18Id`             INT NULL,
    `m18LastModifyDate` DATETIME NULL,
    CONSTRAINT pk_bom PRIMARY KEY (id),
    CONSTRAINT `FK_BOM_ON_UOMID` FOREIGN KEY (`uomId`) REFERENCES `uom_conversion` (`id`)
);

CREATE TABLE `bom_process`
(
    `id`         INT      NOT NULL AUTO_INCREMENT,
    `created`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `createdBy`  VARCHAR(30) NULL DEFAULT NULL,
    `version`    INT      NOT NULL DEFAULT '0',
    `modified`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modifiedBy` VARCHAR(30) NULL DEFAULT NULL,
    `deleted`    TINYINT(1) NOT NULL DEFAULT '0',
    `processId`  INT      NOT NULL,
    `uomId`      INT      NOT NULL,
    `seqNo`      INT      NOT NULL,
    `bomId`      INT      NOT NULL,
    CONSTRAINT pk_bom_process PRIMARY KEY (id),
    CONSTRAINT `FK_BOM_PROCESS_ON_PROCESSID` FOREIGN KEY (`processId`) REFERENCES `process` (`id`),
    CONSTRAINT `FK_BOM_PROCESS_ON_UOMID` FOREIGN KEY (`uomId`) REFERENCES `uom_conversion` (`id`),
    CONSTRAINT `FK_BOM_PROCESS_ON_BOMID` FOREIGN KEY (`bomId`) REFERENCES `bom` (`id`)
);

CREATE TABLE `bom_material`
(
    `id`                INT            NOT NULL AUTO_INCREMENT,
    `created`           DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `createdBy`         VARCHAR(30) NULL DEFAULT NULL,
    `version`           INT            NOT NULL DEFAULT '0',
    `modified`          DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modifiedBy`        VARCHAR(30) NULL DEFAULT NULL,
    `deleted`           TINYINT(1) NOT NULL DEFAULT '0',
    `itemId`            INT            NOT NULL,
    `isConsumable`      TINYINT(1) NOT NULL DEFAULT '0',
    `qty`               DECIMAL(14, 2) NOT NULL,
    `uomId`             INT            NOT NULL,
    `bomId`             INT            NOT NULL,
    `m18Id`             INT            NOT NULL,
    `m18LastModifyDate` DATETIME       NOT NULL,
    CONSTRAINT pk_bom_material PRIMARY KEY (id),
    CONSTRAINT `FK_BOM_MATERIAL_ON_ITEMID` FOREIGN KEY (`itemId`) REFERENCES `items` (`id`),
    CONSTRAINT `FK_BOM_MATERIAL_ON_UOMID` FOREIGN KEY (`uomId`) REFERENCES `uom_conversion` (`id`),
    CONSTRAINT `FK_BOM_MATERIAL_ON_BOMID` FOREIGN KEY (`bomId`) REFERENCES `bom` (`id`)
);

CREATE TABLE `bom_process_material`
(
    `id`            INT      NOT NULL AUTO_INCREMENT,
    `created`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `createdBy`     VARCHAR(30) NULL DEFAULT NULL,
    `version`       INT      NOT NULL DEFAULT '0',
    `modified`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `modifiedBy`    VARCHAR(30) NULL DEFAULT NULL,
    `deleted`       TINYINT(1) NOT NULL DEFAULT '0',
    `bomProcessId`  INT      NOT NULL,
    `bomMaterialId` INT      NOT NULL,
    CONSTRAINT pk_bom_process_material PRIMARY KEY (id),
    CONSTRAINT `FK_BOM_PROCESS_MATERIAL_ON_BOMPROCESSID` FOREIGN KEY (`bomProcessId`) REFERENCES `bom_process` (`id`),
    CONSTRAINT `FK_BOM_PROCESS_MATERIAL_ON_BOMMATERIALID` FOREIGN KEY (`bomMaterialId`) REFERENCES `bom_material` (`id`)
);