CREATE TABLE TBL_ORDERS
(
    DOCUMENT_ID          VARCHAR(255) NOT NULL,
    DOCUMENT_DT          TIMESTAMP(6) NOT NULL,
    DOCUMENT_VERSION     INTEGER      NOT NULL,
    CUSTOMER_NAME        VARCHAR(255),
    ORDER_TOTAL_AMOUNT   NUMERIC(38, 2),
    ORDER_TOTAL_QUANTITY INTEGER,
    ORDER_DATE_TIME      TIMESTAMP(6),
    PRIMARY KEY (DOCUMENT_ID)
);

CREATE TABLE TBL_ITEMS
(
    ITEM_ID          VARCHAR(255) NOT NULL,
    ITEM_DESCRIPTION VARCHAR(255),
    ITEM_LINE_NUMBER INTEGER,
    ITEM_AMOUNT      NUMERIC(38, 2),
    ITEM_QUANTITY    INTEGER,
    DOCUMENT_ID      VARCHAR(255) NOT NULL,
    PRIMARY KEY (ITEM_ID, DOCUMENT_ID)
);

CREATE TABLE TBL_ORDERS_DOC_METADATA
(
    DOCUMENT_ID      VARCHAR(255) NOT NULL,
    MD_C_DATE        DATE,
    MD_C_DATE_TIME   TIMESTAMP(6),
    MD_C_OP          VARCHAR(255),
    MD_C_START_OP    VARCHAR(255),
    MD_C_STATE       VARCHAR(255),
    MD_C_NAME        VARCHAR(71),
    MD_C_USERNAME    VARCHAR(35),
    MD_DATE          DATE,
    MD_DATE_TIME     TIMESTAMP(6),
    MD_OP            VARCHAR(255),
    MD_START_OP      VARCHAR(255),
    MD_STATE         VARCHAR(255),
    MD_NAME          VARCHAR(71),
    MD_USERNAME      VARCHAR(35),
    MD_P_DATE        DATE,
    MD_P_DATE_TIME   TIMESTAMP(6),
    MD_P_OP          VARCHAR(255),
    MD_P_START_OP    VARCHAR(255),
    MD_P_STATE       VARCHAR(255),
    MD_P_NAME        VARCHAR(71),
    MD_P_USERNAME    VARCHAR(35),
    MD_STATES        VARCHAR(3999),
    MD_TAGS          VARCHAR(3999),
    DATA_DOCUMENT_ID VARCHAR(255),
    PRIMARY KEY (DOCUMENT_ID)
);

CREATE TABLE TBL_ORDERS_DOC_AUDIT
(
    DOCUMENT_VERSION  INTEGER       NOT NULL,
    DOCUMENT_ID       VARCHAR(255)  NOT NULL,
    DOCUMENT_CHECKSUM VARCHAR(3999) NOT NULL,
    MD_C_DATE         DATE,
    MD_C_DATE_TIME    TIMESTAMP(6),
    MD_C_OP           VARCHAR(255),
    MD_C_START_OP     VARCHAR(255),
    MD_C_STATE        VARCHAR(255),
    MD_C_NAME         VARCHAR(71),
    MD_C_USERNAME     VARCHAR(35),
    MD_DATE           DATE,
    MD_DATE_TIME      TIMESTAMP(6),
    MD_OP             VARCHAR(255),
    MD_START_OP       VARCHAR(255),
    MD_STATE          VARCHAR(255),
    MD_NAME           VARCHAR(71),
    MD_USERNAME       VARCHAR(35),
    DOCUMENT_DATA     OID           NOT NULL,
    MD_P_DATE         DATE,
    MD_P_DATE_TIME    TIMESTAMP(6),
    MD_P_OP           VARCHAR(255),
    MD_P_START_OP     VARCHAR(255),
    MD_P_STATE        VARCHAR(255),
    MD_P_NAME         VARCHAR(71),
    MD_P_USERNAME     VARCHAR(35),
    DOCUMENT_SIZE     BIGINT        NOT NULL,
    PRIMARY KEY (DOCUMENT_ID, DOCUMENT_VERSION)
);

ALTER TABLE IF EXISTS TBL_ORDERS_DOC_METADATA
    DROP CONSTRAINT IF EXISTS IDX_ORDERS_UK;

ALTER TABLE IF EXISTS TBL_ORDERS_DOC_METADATA
    ADD CONSTRAINT IDX_ORDERS_UK UNIQUE (DATA_DOCUMENT_ID);

ALTER TABLE IF EXISTS TBL_ITEMS
    ADD CONSTRAINT FK_DID2 FOREIGN KEY (DOCUMENT_ID) REFERENCES TBL_ORDERS;

ALTER TABLE IF EXISTS TBL_ORDERS_DOC_METADATA
    ADD CONSTRAINT FK_DID2 FOREIGN KEY (DATA_DOCUMENT_ID) REFERENCES TBL_ORDERS;
