CREATE TABLE UK_TRANSACTION_INITIATION (
  ID        VARCHAR2(255)  NOT NULL,
  REQUEST    CLOB NOT NULL,
  TIMESTAMP TIMESTAMP      DEFAULT CURRENT_TIMESTAMP NOT NULL,
  STATUS    VARCHAR2(64)   DEFAULT 0 NOT NULL,
  CLIENT_ID VARCHAR2(255)  NOT NULL,
  IDEMPOTENT_KEY VARCHAR2(64) NOT NULL ,
  STATUS_UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  SPEC_VERSION VARCHAR2(64)   NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT ENSURE_JSON_REQUEST_TRANS CHECK (REQUEST IS JSON)
)
/
CREATE TABLE UK_ACCOUNT_INITIATION (
  ID        VARCHAR2(255)  NOT NULL,
  REQUEST    CLOB NOT NULL,
  CREATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  STATUS    VARCHAR2(64)   NOT NULL,
  CLIENT_ID VARCHAR2(255)  NOT NULL,
  STATUS_UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  SPEC_VERSION VARCHAR2(64)   NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT ENSURE_JSON_REQUEST_ACCOUNT CHECK (REQUEST IS JSON)
)
/
CREATE TABLE UK_COF_INITIATION (
  ID        VARCHAR2(255)  NOT NULL,
  REQUEST    CLOB NOT NULL,
  CREATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  STATUS    VARCHAR2(64)   NOT NULL,
  CLIENT_ID VARCHAR2(255)  NOT NULL,
  STATUS_UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  SPEC_VERSION VARCHAR2(64)   NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT ENSURE_JSON_REQUEST_FUNDS_CONF CHECK (REQUEST IS JSON)
)
/
CREATE TABLE UK_TRANSACTION_CONSENT_BINDING (
  USER_ID           VARCHAR2(255) NOT NULL,
  CONSENT_ID        VARCHAR2(255) NOT NULL,
  DEBTOR_ACCOUNT    VARCHAR2(255) NOT NULL,
  TIMESTAMP         TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  COLLECTION_METHOD VARCHAR2(64),
  PRIMARY KEY (USER_ID, CONSENT_ID),
  CONSTRAINT FK_CONSENT_BINDING FOREIGN KEY (CONSENT_ID) REFERENCES UK_TRANSACTION_INITIATION (ID)
    ON DELETE CASCADE
)
/
CREATE TABLE UK_ACCOUNT_CONSENT_BINDING (
  USER_ID           VARCHAR2(255) NOT NULL,
  CONSENT_ID        VARCHAR2(255) NOT NULL,
  ACCOUNT_ID        VARCHAR2(255) NOT NULL,
  TIMESTAMP         TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  COLLECTION_METHOD VARCHAR2(64),
  PRIMARY KEY (USER_ID, CONSENT_ID,ACCOUNT_ID),
  CONSTRAINT FK_ACCOUNT_CONSENT_BINDING FOREIGN KEY (CONSENT_ID) REFERENCES UK_ACCOUNT_INITIATION (ID)
    ON DELETE CASCADE
)
/
CREATE TABLE UK_COF_CONSENT_BINDING (
  USER_ID           VARCHAR2(255) NOT NULL,
  CONSENT_ID        VARCHAR2(255) NOT NULL,
  ACCOUNT_ID        VARCHAR2(255) NOT NULL,
  TIMESTAMP         TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  COLLECTION_METHOD VARCHAR2(64),
  PRIMARY KEY (USER_ID, CONSENT_ID,ACCOUNT_ID),
  CONSTRAINT FK_COF_CONSENT_BINDING FOREIGN KEY (CONSENT_ID) REFERENCES UK_COF_INITIATION (ID)
    ON DELETE CASCADE
)
/
CREATE TABLE UK_ACCOUNT_CONSENT_REV_HISTORY (
  CONSENT_ID VARCHAR2(255) NOT NULL,
  REVOCATION_USER VARCHAR2(255) NOT NULL,
  REVOCATION_REASON VARCHAR2(2048) NOT NULL,
  PRIMARY KEY (CONSENT_ID),
  CONSTRAINT FK_CONSENT_REVOCATION_HISTORY FOREIGN KEY (CONSENT_ID) REFERENCES UK_ACCOUNT_INITIATION (ID)
    ON DELETE CASCADE
)
/
CREATE TABLE UK_COF_CONSENT_REV_HISTORY (
  CONSENT_ID VARCHAR2(255) NOT NULL,
  REVOCATION_USER VARCHAR2(255) NOT NULL,
  REVOCATION_REASON VARCHAR2(2048) NOT NULL,
  PRIMARY KEY (CONSENT_ID),
  CONSTRAINT FK_COF_REVOCATION_HISTORY FOREIGN KEY (CONSENT_ID) REFERENCES UK_COF_INITIATION (ID)
    ON DELETE CASCADE
)
/
CREATE TABLE OB_MULTI_AUTHORIZATION (
  ID  INT,
  CONSENT_ID VARCHAR2(255) NOT NULL,
  CLIENT_ID  VARCHAR2(255) NOT NULL,
  SELECTED_ACCOUNT  VARCHAR2(255) NOT NULL,
  AUTHORIZATION_EXPIRY_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  STATUS VARCHAR2(255) NOT NULL,
  NUMBER_OF_TOTAL_AUTHORIZATIONS INTEGER DEFAULT 0,
  CREATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  STATUS_UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT OB_MULTI_AUTH_SEQUENCE PRIMARY KEY (ID)
)
/
CREATE SEQUENCE OB_MULTI_AUTH_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/
CREATE OR REPLACE TRIGGER MULTI_AUTHORIZATION_TRIGGER
                    BEFORE INSERT
                    ON OB_MULTI_AUTHORIZATION
                    FOR EACH ROW
                     BEGIN
                       SELECT OB_MULTI_AUTH_SEQUENCE.nextval INTO :NEW.ID FROM dual;
           		       END;
/
CREATE TABLE OB_MULTI_AUTHORIZATION_USER (
  ID  INTEGER DEFAULT 0,
  USER_ID VARCHAR2(255) NOT NULL,
  STATUS  VARCHAR2(255) NOT NULL,
  CREATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  CONSTRAINT PK_OB_MULTI_AUTHORIZATION_USER PRIMARY KEY(ID,USER_ID) ,
  CONSTRAINT FK_ID_OB_MULTI_AUTH_USER  FOREIGN KEY (ID) REFERENCES OB_MULTI_AUTHORIZATION (ID)
    ON DELETE CASCADE
)
/
CREATE TABLE UK_CONSENT_REV (
  USER_ID           VARCHAR2(255) NOT NULL,
  CONSENT_ID        VARCHAR2(255) NOT NULL,
  ACCOUNT_ID        VARCHAR2(255) NOT NULL,
  TIMESTAMP         TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  COLLECTION_METHOD VARCHAR2(64),
  PRIMARY KEY (USER_ID, CONSENT_ID,ACCOUNT_ID)
)
/