CREATE TABLE SM_STATE_DEFINITION(
    STATE_ID    NUMBER NOT NULL,
    WORKFLOW_ID  NUMBER NOT NULL,
    STATE_NAME  VARCHAR2(256) NOT NULL,
    EMAIL_TO           VARCHAR2(2048),
    PRIMARY KEY(STATE_ID),
    FOREIGN KEY(WORKFLOW_ID) REFERENCES SM_WORKFLOW_DEFINITION(WORKFLOW_ID)
);

CREATE UNIQUE INDEX I_SM_STATE_DEFINITION ON SM_STATE_DEFINITION(WORKFLOW_ID, STATE_NAME);

CREATE TABLE SM_WORKFLOW_STATE(
    STATE_ID  NUMBER NOT NULL,
    NEXT_STATE_ID NUMBER,
    ACTION VARCHAR2(512),
    FOREIGN KEY(STATE_ID) REFERENCES STATE_DEFINITION(STATE_ID)
    FOREIGN KEY(NEXT_STATE_ID) REFERENCES STATE_DEFINITION(STATE_ID)
);

CREATE TABLE SM_WORKFLOW_DEFINITION(
    WORKFLOW_ID  NUMBER NOT NULL,
    WORKFLOW_TYPE VARCHAR2(256) NOT NULL,
    EMAIL_NOTIFICATION CHAR(1) DEFAULT 'N',
    EMAIL_SUBJECT      VARCHAR2(1024),
    EMAIL_CONTENT      VARCHAR2(2048),
    PRIMARY KEY(WORKFLOW_ID)
);


CREATE TABLE SM_STATE_HISTORY (
    ITEM_TYPE VARCHAr2(256) not null,
    ITEM_ID VARCHAR2(256) NOT NULL,
    STATE_ID NUMBER NOT NULL,
    NOTES   VARCHAR2(2048),
    USERID  VARCHAR2(100) NOT NULL,
    STATE_ACTION VARCHAR2(100) NOT NULL,
    USER_SUBSCRIPTION_NOTIFICATION VARCHAR2(100),
    INSERT_TS  TIMESTAMP NOT NULL,
    FOREIGN KEY(STATE_ID) REFERENCES STATE_DEFINITION(STATE_ID)
);

CREATE INDEX I_SM_STATE_HISTORY ON SM_STATE_HISTORY(ITEM_TYPE, ITEM_ID);


CREATE TABLE SM_VERSION (
    ITEM_TYPE VARCHAR2(256) NOT NULL,
    ITEM_ID   VARCHAR2(256) NOT NULL,
    VERSION_NUMBER NUMBER NOT NULL,
    CREATED_TS DATETIME NOT NULL,
    PRIMARY KEY (ITEM_TYPE, VERSION_NUMBER)
);

CREATE UNIQUE INDEX I_SM_VERSION ON SM_VERSION(ITEM_TYPE, ITEM_ID);