-- liquibase formatted sql
-- changeset cyril:job planning log

CREATE TABLE `job_planning_log`
(
    `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,
    `lastLogId`   INT            NULL,
    CONSTRAINT pk_job_planning_log PRIMARY KEY (id),
    CONSTRAINT `FK_JOB_PLANNING_LOG_ON_BOMID` FOREIGN KEY (`bomId`) REFERENCES `bom` (`id`),
    CONSTRAINT `FK_JOB_PLANNING_LOG_ON_APPROVERID` FOREIGN KEY (`approverId`) REFERENCES `user` (`id`),
    CONSTRAINT `FK_JOB_PLANNING_LOG_ON_LASTLOGID` FOREIGN KEY (`lastLogId`) REFERENCES `job_planning_log` (`id`)
);