--liquibase formatted sql

--changeset cyril:purchase order
CREATE TABLE `purchase_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(30) NOT NULL,
    `supplierId`            INT NULL,
    `orderDate`             DATETIME NULL,
    `estimatedArrivalDate` DATETIME NULL,
    `completeDate`          DATETIME NULL,
    `status`                VARCHAR(10) NOT NULL DEFAULT 'pending',
    `m18DataLogId`          INT         NOT NULL,
    CONSTRAINT pk_purchase_order PRIMARY KEY (id),
    CONSTRAINT FK_PURCHASE_ORDER_ON_SUPPLIERID FOREIGN KEY (supplierId) REFERENCES shop (id),
    CONSTRAINT FK_PURCHASE_ORDER_ON_M18DATALOGID FOREIGN KEY (m18DataLogId) REFERENCES m18_data_log (id)
);

CREATE TABLE `purchase_order_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',
    `itemId`          INT         NOT NULL,
    `itemNo`          VARCHAR(20) NOT NULL,
    `uomId`           INT         NULL,
    `purchaseOrderId` INT         NOT NULL,
    `qty`             DECIMAL(14, 2) NULL,
    `price`           DECIMAL(14, 2) NULL,
    `priceUnit`       VARCHAR(5) NULL,
    `status`          VARCHAR(10) NOT NULL DEFAULT 'pending',
    `m18DataLogId`    INT         NOT NULL,
    CONSTRAINT pk_purchase_order_line PRIMARY KEY (id),
    CONSTRAINT FK_PURCHASE_ORDER_LINE_ON_ITEMID FOREIGN KEY (itemId) REFERENCES items (id),
--     CONSTRAINT FK_PURCHASE_ORDER_LINE_ON_UOMID FOREIGN KEY (uomId) REFERENCES uom_conversion (id),
    CONSTRAINT FK_PURCHASE_ORDER_LINE_ON_PURCHASEORDERID FOREIGN KEY (purchaseOrderId) REFERENCES purchase_order (id),
    CONSTRAINT FK_PURCHASE_ORDER_LINE_ON_M18DATALOGID FOREIGN KEY (m18DataLogId) REFERENCES m18_data_log (id)
);