CreateEmvsTables.sql

-- EMVS Simulation Lab database schema.
-- This script creates the local Oracle tables used by the EMVS simulation application.

-- Drop tables in dependency order if you need to recreate the schema.
-- DROP TABLE EMVS_OPERATION_PACK_ERRORS CASCADE CONSTRAINTS;
-- DROP TABLE EMVS_OPERATION_LOG CASCADE CONSTRAINTS;
-- DROP TABLE EMVS_PACKS CASCADE CONSTRAINTS;
-- DROP TABLE EMVS_BATCHES CASCADE CONSTRAINTS;
-- DROP TABLE EMVS_PRODUCT_ALTERNATE_CODES CASCADE CONSTRAINTS;
-- DROP TABLE EMVS_PRODUCT_TARGET_MARKETS CASCADE CONSTRAINTS;
-- DROP TABLE EMVS_PRODUCTS CASCADE CONSTRAINTS;

CREATE TABLE EMVS_PRODUCTS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_CODE VARCHAR2(50) NOT NULL,
    PRODUCT_CODE_SCHEME VARCHAR2(10) NOT NULL,
    NAME NVARCHAR2(100) NOT NULL,
    FORM VARCHAR2(200) NOT NULL,
    STRENGTH VARCHAR2(30) NOT NULL,
    DOSES_PER_PACK NUMBER(10) NOT NULL,
    PACK_TYPE VARCHAR2(100) NOT NULL,
    EFFECTIVE_DATE TIMESTAMP NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT UQ_EMVS_PRODUCTS_CODE UNIQUE (PRODUCT_CODE, PRODUCT_CODE_SCHEME),
    CONSTRAINT CK_EMVS_PRODUCTS_DOSES CHECK (DOSES_PER_PACK > 0)
);

CREATE TABLE EMVS_PRODUCT_TARGET_MARKETS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_ID NUMBER NOT NULL,
    MARKET_CODE VARCHAR2(2) NOT NULL,
    WHITELIST_FLAG NUMBER(1) NOT NULL,

    CONSTRAINT FK_EMVS_TARGET_MARKETS_PRODUCT
        FOREIGN KEY (PRODUCT_ID)
        REFERENCES EMVS_PRODUCTS (ID)
        ON DELETE CASCADE,

    CONSTRAINT CK_EMVS_TARGET_MARKETS_FLAG
        CHECK (WHITELIST_FLAG IN (0, 1)),

    CONSTRAINT UQ_EMVS_TARGET_MARKET
        UNIQUE (PRODUCT_ID, MARKET_CODE)
);

CREATE TABLE EMVS_PRODUCT_ALTERNATE_CODES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_ID NUMBER NOT NULL,
    ALTERNATE_PRODUCT_CODE VARCHAR2(50) NOT NULL,
    ALTERNATE_PRODUCT_CODE_SCHEME VARCHAR2(10) NOT NULL,

    CONSTRAINT FK_EMVS_ALT_CODES_PRODUCT
        FOREIGN KEY (PRODUCT_ID)
        REFERENCES EMVS_PRODUCTS (ID)
        ON DELETE CASCADE,

    CONSTRAINT UQ_EMVS_ALT_CODE
        UNIQUE (
            PRODUCT_ID,
            ALTERNATE_PRODUCT_CODE,
            ALTERNATE_PRODUCT_CODE_SCHEME
        )
);

CREATE TABLE EMVS_BATCHES (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    PRODUCT_ID NUMBER NOT NULL,
    BATCH_ID VARCHAR2(20) NOT NULL,
    BATCH_EXPIRY_DATE CHAR(6) NOT NULL,
    DOSES_PER_PACK NUMBER(10) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT FK_EMVS_BATCHES_PRODUCT
        FOREIGN KEY (PRODUCT_ID)
        REFERENCES EMVS_PRODUCTS (ID)
        ON DELETE CASCADE,

    CONSTRAINT UQ_EMVS_BATCH
        UNIQUE (PRODUCT_ID, BATCH_ID, BATCH_EXPIRY_DATE),

    CONSTRAINT CK_EMVS_BATCH_EXPIRY
        CHECK (REGEXP_LIKE(BATCH_EXPIRY_DATE, '^[0-9]{6}$')),

    CONSTRAINT CK_EMVS_BATCH_DOSES
        CHECK (DOSES_PER_PACK > 0)
);

CREATE TABLE EMVS_PACKS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    BATCH_DB_ID NUMBER NOT NULL,
    SERIAL_NUMBER VARCHAR2(100) NOT NULL,
    STATE VARCHAR2(50) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT FK_EMVS_PACKS_BATCH
        FOREIGN KEY (BATCH_DB_ID)
        REFERENCES EMVS_BATCHES (ID)
        ON DELETE CASCADE,

    CONSTRAINT UQ_EMVS_PACK
        UNIQUE (BATCH_DB_ID, SERIAL_NUMBER)
);

CREATE TABLE EMVS_OPERATION_LOG (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    OPERATION_NAME VARCHAR2(100) NOT NULL,
    REQUEST_SUMMARY VARCHAR2(500),
    RESPONSE_SUMMARY VARCHAR2(500),
    REQUEST_PAYLOAD CLOB,
    RESPONSE_PAYLOAD CLOB,
    MESSAGE_ERROR_CODE VARCHAR2(20),
    PACK_ERROR_CODE VARCHAR2(20),
    IS_SUCCESS NUMBER(1) NOT NULL,
    CREATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

    CONSTRAINT CK_EMVS_OPERATION_LOG_SUCCESS
        CHECK (IS_SUCCESS IN (0, 1))
);

CREATE TABLE EMVS_OPERATION_PACK_ERRORS (
    ID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    OPERATION_LOG_ID NUMBER NOT NULL,
    SERIAL_NUMBER VARCHAR2(100) NOT NULL,
    ERROR_CODE VARCHAR2(20) NOT NULL,
    ERROR_MESSAGE VARCHAR2(200) NOT NULL,

    CONSTRAINT FK_EMVS_PACK_ERRORS_LOG
        FOREIGN KEY (OPERATION_LOG_ID)
        REFERENCES EMVS_OPERATION_LOG (ID)
        ON DELETE CASCADE
);