-- liquibase formatted sql

-- changeset cyril:create bom & process table
CREATE TABLE `purchase_quotation`
(
    `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,
    `expiryDate`        DATETIME     NULL,
    `effectiveDate`     DATETIME     NULL,
    `shopId`            INT          NULL,
    `currencyId`        INT          NULL,
    `remarks`           VARCHAR(500) NULL,
    `m18Id`             INT          NULL,
    `m18LastModifyDate` DATETIME     NULL,
    CONSTRAINT pk_purchase_quotation PRIMARY KEY (id),
    CONSTRAINT `FK_PURCHASE_QUOTATION_ON_SHOPID` FOREIGN KEY (`shopId`) REFERENCES `shop` (`id`),
    CONSTRAINT `FK_PURCHASE_QUOTATION_ON_CURRENCYID` FOREIGN KEY (`currencyId`) REFERENCES `currency` (`id`)
);

CREATE TABLE `purchase_quotation_line`
(
    `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',
    `purchaseQuotationId` INT         NULL,
    `itemId`              INT         NULL,
    `code`                VARCHAR(30) NULL,
    `description`         VARCHAR(300) NULL,
    `uomId`               INT         NULL,
    `m18Id`               INT         NULL,
    `m18LastModifyDate`   DATETIME    NULL,
    CONSTRAINT pk_purchase_quotation_line PRIMARY KEY (id),
    CONSTRAINT `FK_PURCHASE_QUOTATION_LINE_ON_PURCHASEQUOTATIONID` FOREIGN KEY (`purchaseQuotationId`) REFERENCES `purchase_quotation` (`id`),
    CONSTRAINT `FK_PURCHASE_QUOTATION_LINE_ON_ITEMID` FOREIGN KEY (`itemId`) REFERENCES `items` (`id`),
    CONSTRAINT `FK_PURCHASE_QUOTATION_LINE_ON_UOMID` FOREIGN KEY (`uomId`) REFERENCES `uom_conversion` (`id`)
);