-- liquibase formatted sql
-- changeset cyril:job order

CREATE TABLE `job_order`
(
    `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(100)   NOT NULL,
    `bomId`            INT            NOT NULL,
    `planStart`        DATETIME       NULL,
    `actualStart`      DATETIME       NULL,
    `planEnd`          DATETIME       NULL,
    `actualEnd`        DATETIME       NULL,
    `reqQty`           DECIMAL(14, 2) NULL,
    `actualQty`        DECIMAL(14, 2) NULL,
    `status`           VARCHAR(100)   NOT NULL default 'planning',
    `remarks`          VARCHAR(500)   NULL,
    `type`             VARCHAR(20)    NOT NULL default 'rough',
    `approverId`       INT            NULL,
    `jobPlanningLogId` INt            NULL,
    CONSTRAINT pk_job_order PRIMARY KEY (id),
    CONSTRAINT `FK_JOB_ORDER_ON_BOMID` FOREIGN KEY (`bomId`) REFERENCES `bom` (`id`),
    CONSTRAINT `FK_JOB_ORDER_ON_APPROVERID` FOREIGN KEY (`approverId`) REFERENCES `user` (`id`),
    CONSTRAINT `FK_JOB_ORDER_ON_JOBPLANNINGLOGID` FOREIGN KEY (`jobPlanningLogId`) REFERENCES `job_planning_log` (`id`)
);

CREATE TABLE `job_order_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',
    `joId`       INT          NOT NULL,
    `processId`  INT          NOT NULL,
    `startTime`  DATETIME     NULL,
    `endTime`    DATETIME     NULL,
    `status`     VARCHAR(20)  NULL,
    `seqNo`      INT          NULL,
    `remarks`    VARCHAR(500) NULL,
    CONSTRAINT pk_job_order_process PRIMARY KEY (id),
    CONSTRAINT `FK_JOB_ORDER_PROCESS_ON_JOID` FOREIGN KEY (`joId`) REFERENCES `job_order` (`id`),
    CONSTRAINT `FK_JOB_ORDER_PROCESS_ON_PROCESSID` FOREIGN KEY (`processId`) REFERENCES `process` (`id`)
);

CREATE TABLE `job_order_process_detail`
(
    `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',
    `jopId`          INT          NOT NULL,
    `equipmentId`    INT          NOT NULL,
    `operatorId`     INT          NOT NULL,
    `operatingStart` DATETIME     NULL,
    `operatingEnd`   DATETIME     NULL,
    `remarks`        VARCHAR(500) NULL,
    CONSTRAINT pk_job_order_process_detail PRIMARY KEY (id),
    CONSTRAINT `FK_JOB_ORDER_PROCESS_DETAIL_ON_JOPID` FOREIGN KEY (`jopId`) REFERENCES `job_order_process` (`id`),
    CONSTRAINT `FK_JOB_ORDER_PROCESS_DETAIL_ON_EQUIPMENTID` FOREIGN KEY (`equipmentId`) REFERENCES `equipment` (`id`),
    CONSTRAINT `FK_JOB_ORDER_PROCESS_DETAIL_ON_OPERATORID` FOREIGN KEY (`operatorId`) REFERENCES `user` (`id`)
);

CREATE TABLE `job_order_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',
    `jopId`              INT            NOT NULL,
    `itemId`             INT            NOT NULL,
    `qty`                DECIMAL(14, 2) NOT NULL,
    `uomId`              INT            NOT NULL,
    `inventoryLotLineId` INT            NULL,
    `joProcessDetailId`  INT            NOT NULL,
    CONSTRAINT pk_job_order_material PRIMARY KEY (id),
    CONSTRAINT `FK_JOB_ORDER_MATERIAL_ON_JOPID` FOREIGN KEY (`jopId`) REFERENCES `job_order_process` (`id`),
    CONSTRAINT `FK_JOB_ORDER_MATERIAL_ON_ITEMID` FOREIGN KEY (`itemId`) REFERENCES `items` (`id`),
    CONSTRAINT `FK_JOB_ORDER_MATERIAL_ON_UOMID` FOREIGN KEY (`uomId`) REFERENCES `uom_conversion` (`id`),
    CONSTRAINT `FK_JOB_ORDER_MATERIAL_ON_LOTLINEID` FOREIGN KEY (`inventoryLotLineId`) REFERENCES `inventory_lot_line` (`id`),
    CONSTRAINT `FK_JOB_ORDER_MATERIAL_ON_JOPROCESSDETAILID` FOREIGN KEY (`joProcessDetailId`) REFERENCES `job_order_process_detail` (`id`)
);