text: DROP DATABASE IF EXISTS openbank_am_configdb;
  CREATE DATABASE openbank_am_configdb;
  
  DROP DATABASE IF EXISTS openbank_apimgt_statsdb;
  CREATE DATABASE openbank_apimgt_statsdb;
  
  DROP DATABASE IF EXISTS openbank_apimgtdb;
  CREATE DATABASE openbank_apimgtdb;
  
  DROP DATABASE IF EXISTS openbank_openbankingdb;
  CREATE DATABASE openbank_openbankingdb;
  
  DROP DATABASE IF EXISTS openbank_govdb;
  CREATE DATABASE openbank_govdb;
  
  DROP DATABASE IF EXISTS openbank_iskm_configdb;
  CREATE DATABASE openbank_iskm_configdb;
  
  DROP DATABASE IF EXISTS openbank_userdb;
  CREATE DATABASE openbank_userdb;
  
  DROP DATABASE IF EXISTS openbank_geolocationdb;
  CREATE DATABASE openbank_geolocationdb;
  
  DROP DATABASE IF EXISTS openbank_ob_reporting_statsdb;
  CREATE DATABASE openbank_ob_reporting_statsdb;
  
  DROP DATABASE IF EXISTS openbank_ob_reporting_summarizeddb;
  CREATE DATABASE openbank_ob_reporting_summarizeddb;
  
  /*
  Create tables for openbank_geolocationdb
  */
  
  USE openbank_geolocationdb;
  
  CREATE TABLE BLOCKS (
  	network_cidr varchar(45) DEFAULT NULL,
  	network int(10) unsigned DEFAULT NULL,
    	broadcast int(10) unsigned DEFAULT NULL,
   	geoname_id int(11) DEFAULT NULL,
    	registered_country_geoname_id int(11) DEFAULT NULL,
    	represented_country_geoname_id int(11) DEFAULT NULL,
    	is_anonymous_proxy tinyint(3) DEFAULT '0',
    	is_satellite_provider tinyint(1) DEFAULT '0',
    	postal_code varchar(45) DEFAULT NULL,
    	latitude float DEFAULT NULL,
    	longitude float DEFAULT NULL,
  	network_blocks varchar(45) DEFAULT NULL
  );
  
  CREATE INDEX idx_blocks_network ON BLOCKS (network);
  
  CREATE INDEX idx_blocks_broadcast ON BLOCKS (broadcast);
  
  CREATE INDEX idx_blocks_network_blocks ON BLOCKS (network_blocks);
  
  CREATE TABLE LOCATION (
    	geoname_id int(10) unsigned NOT NULL,
    	locale_code varchar(10) DEFAULT NULL,
    	continent_code varchar(10) DEFAULT NULL,
    	continent_name varchar(20) DEFAULT NULL,
    	country_iso_code varchar(10) DEFAULT NULL,
    	country_name varchar(45) DEFAULT NULL,
    	subdivision_1_iso_code varchar(10) DEFAULT NULL,
    	subdivision_1_name varchar(1000) DEFAULT NULL,
    	subdivision_2_iso_code varchar(10) DEFAULT NULL,
    	subdivision_2_name varchar(1000) DEFAULT NULL,
    	city_name varchar(1000) DEFAULT NULL,
    	metro_code int(11) DEFAULT NULL,
    	time_zone varchar(10) DEFAULT NULL,
    	PRIMARY KEY (`geoname_id`)
  );
  
  CREATE TABLE IP_LOCATION (
    	ip varchar(100) NOT NULL,
    	country_name varchar(200) DEFAULT NULL,
    	city_name varchar(200) DEFAULT NULL,
    	PRIMARY KEY (`ip`)
  );
  
  
  /*
  Create tables for openbank_apimgtdb
  */
  
  USE openbank_apimgtdb;
  
  -- Start of IDENTITY Tables--
  
  CREATE TABLE IF NOT EXISTS IDN_BASE_TABLE (
      PRODUCT_NAME VARCHAR(20),
      PRIMARY KEY (PRODUCT_NAME)
  )ENGINE INNODB;
  
  INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server');
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH_CONSUMER_APPS (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      CONSUMER_KEY VARCHAR(255),
      CONSUMER_SECRET VARCHAR(2048),
      USERNAME VARCHAR(255),
      TENANT_ID INTEGER DEFAULT 0,
      USER_DOMAIN VARCHAR(50),
      APP_NAME VARCHAR(255),
      OAUTH_VERSION VARCHAR(128),
      CALLBACK_URL VARCHAR(2048),
      GRANT_TYPES VARCHAR (1024),
      PKCE_MANDATORY CHAR(1) DEFAULT '0',
      PKCE_SUPPORT_PLAIN CHAR(1) DEFAULT '0',
      APP_STATE VARCHAR (25) DEFAULT 'ACTIVE',
      USER_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
      APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
      REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600,
      ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
      CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_VALIDATORS (
      APP_ID INTEGER NOT NULL,
      SCOPE_VALIDATOR VARCHAR (128) NOT NULL,
      PRIMARY KEY (APP_ID,SCOPE_VALIDATOR),
      FOREIGN KEY (APP_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_REQUEST_TOKEN (
      REQUEST_TOKEN VARCHAR(255),
      REQUEST_TOKEN_SECRET VARCHAR(512),
      CONSUMER_KEY_ID INTEGER,
      CALLBACK_URL VARCHAR(2048),
      SCOPE VARCHAR(2048),
      AUTHORIZED VARCHAR(128),
      OAUTH_VERIFIER VARCHAR(512),
      AUTHZ_USER VARCHAR(512),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (REQUEST_TOKEN),
      FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH1A_ACCESS_TOKEN (
      ACCESS_TOKEN VARCHAR(255),
      ACCESS_TOKEN_SECRET VARCHAR(512),
      CONSUMER_KEY_ID INTEGER,
      SCOPE VARCHAR(2048),
      AUTHZ_USER VARCHAR(512),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (ACCESS_TOKEN),
      FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN (
      TOKEN_ID VARCHAR (255),
      ACCESS_TOKEN VARCHAR(2048),
      REFRESH_TOKEN VARCHAR(2048),
      CONSUMER_KEY_ID INTEGER,
      AUTHZ_USER VARCHAR (100),
      TENANT_ID INTEGER,
      USER_DOMAIN VARCHAR(50),
      USER_TYPE VARCHAR (25),
      GRANT_TYPE VARCHAR (50),
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REFRESH_TOKEN_TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      VALIDITY_PERIOD BIGINT,
      REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
      TOKEN_SCOPE_HASH VARCHAR(32),
      TOKEN_STATE VARCHAR(25) DEFAULT 'ACTIVE',
      TOKEN_STATE_ID VARCHAR (128) DEFAULT 'NONE',
      SUBJECT_IDENTIFIER VARCHAR(255),
      ACCESS_TOKEN_HASH VARCHAR(512),
      REFRESH_TOKEN_HASH VARCHAR(512),
      IDP_ID INTEGER DEFAULT -1 NOT NULL,
      TOKEN_BINDING_REF VARCHAR (32) DEFAULT 'NONE',
      PRIMARY KEY (TOKEN_ID),
      FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
      CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,
                                     TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_TOKEN_BINDING (
      TOKEN_ID VARCHAR (255),
      TOKEN_BINDING_TYPE VARCHAR (32),
      TOKEN_BINDING_REF VARCHAR (32),
      TOKEN_BINDING_VALUE VARCHAR (1024),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (TOKEN_ID),
      FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_AUDIT (
      TOKEN_ID VARCHAR (255),
      ACCESS_TOKEN VARCHAR(2048),
      REFRESH_TOKEN VARCHAR(2048),
      CONSUMER_KEY_ID INTEGER,
      AUTHZ_USER VARCHAR (100),
      TENANT_ID INTEGER,
      USER_DOMAIN VARCHAR(50),
      USER_TYPE VARCHAR (25),
      GRANT_TYPE VARCHAR (50),
      TIME_CREATED TIMESTAMP NULL,
      REFRESH_TOKEN_TIME_CREATED TIMESTAMP NULL,
      VALIDITY_PERIOD BIGINT,
      REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
      TOKEN_SCOPE_HASH VARCHAR(32),
      TOKEN_STATE VARCHAR(25),
      TOKEN_STATE_ID VARCHAR (128) ,
      SUBJECT_IDENTIFIER VARCHAR(255),
      ACCESS_TOKEN_HASH VARCHAR(512),
      REFRESH_TOKEN_HASH VARCHAR(512),
      INVALIDATED_TIME TIMESTAMP NULL,
      IDP_ID INTEGER DEFAULT -1 NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHORIZATION_CODE (
      CODE_ID VARCHAR (255),
      AUTHORIZATION_CODE VARCHAR(2048),
      CONSUMER_KEY_ID INTEGER,
      CALLBACK_URL VARCHAR(2048),
      SCOPE VARCHAR(2048),
      AUTHZ_USER VARCHAR (100),
      TENANT_ID INTEGER,
      USER_DOMAIN VARCHAR(50),
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      VALIDITY_PERIOD BIGINT,
      STATE VARCHAR (25) DEFAULT 'ACTIVE',
      TOKEN_ID VARCHAR(255),
      SUBJECT_IDENTIFIER VARCHAR(255),
      PKCE_CODE_CHALLENGE VARCHAR(255),
      PKCE_CODE_CHALLENGE_METHOD VARCHAR(128),
      AUTHORIZATION_CODE_HASH VARCHAR(512),
      IDP_ID INTEGER DEFAULT -1 NOT NULL,
      PRIMARY KEY (CODE_ID),
      FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHZ_CODE_SCOPE(
      CODE_ID   VARCHAR(255),
      SCOPE     VARCHAR(60),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (CODE_ID, SCOPE),
      FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE (CODE_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW (
      CODE_ID VARCHAR(255),
      DEVICE_CODE VARCHAR(255),
      USER_CODE VARCHAR(25),
      CONSUMER_KEY_ID INTEGER,
      LAST_POLL_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      EXPIRY_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      POLL_TIME BIGINT,
      STATUS VARCHAR (25) DEFAULT 'PENDING',
      AUTHZ_USER VARCHAR (100),
      TENANT_ID INTEGER,
      USER_DOMAIN VARCHAR(50),
      IDP_ID INTEGER,
      PRIMARY KEY (DEVICE_CODE),
      UNIQUE (CODE_ID),
      UNIQUE (USER_CODE),
      FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_DEVICE_FLOW_SCOPES (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      SCOPE_ID VARCHAR(255),
      SCOPE VARCHAR(255),
      PRIMARY KEY (ID),
      FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_DEVICE_FLOW(CODE_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_ACCESS_TOKEN_SCOPE (
      TOKEN_ID VARCHAR (255),
      TOKEN_SCOPE VARCHAR (60),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (TOKEN_ID, TOKEN_SCOPE),
      FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE (
      SCOPE_ID INTEGER NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(255) NOT NULL,
      DISPLAY_NAME VARCHAR(255) NOT NULL,
      DESCRIPTION VARCHAR(512),
      TENANT_ID INTEGER NOT NULL DEFAULT -1,
      SCOPE_TYPE VARCHAR(255) NOT NULL,
      PRIMARY KEY (SCOPE_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_BINDING (
      SCOPE_ID INTEGER NOT NULL,
      SCOPE_BINDING VARCHAR(255) NOT NULL,
      BINDING_TYPE VARCHAR(255) NOT NULL,
      FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_RESOURCE_SCOPE (
      RESOURCE_PATH VARCHAR(255) NOT NULL,
      SCOPE_ID INTEGER NOT NULL,
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (RESOURCE_PATH),
      FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_SCIM_GROUP (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      ROLE_NAME VARCHAR(255) NOT NULL,
      ATTR_NAME VARCHAR(1024) NOT NULL,
      ATTR_VALUE VARCHAR(1024),
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OPENID_REMEMBER_ME (
      USER_NAME VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER DEFAULT 0,
      COOKIE_VALUE VARCHAR(1024),
      CREATED_TIME TIMESTAMP,
      PRIMARY KEY (USER_NAME, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OPENID_USER_RPS (
      USER_NAME VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER DEFAULT 0,
      RP_URL VARCHAR(255) NOT NULL,
      TRUSTED_ALWAYS VARCHAR(128) DEFAULT 'FALSE',
      LAST_VISIT DATE NOT NULL,
      VISIT_COUNT INTEGER DEFAULT 0,
      DEFAULT_PROFILE_NAME VARCHAR(255) DEFAULT 'DEFAULT',
      PRIMARY KEY (USER_NAME, TENANT_ID, RP_URL)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OPENID_ASSOCIATIONS (
      HANDLE VARCHAR(255) NOT NULL,
      ASSOC_TYPE VARCHAR(255) NOT NULL,
      EXPIRE_IN TIMESTAMP NOT NULL,
      MAC_KEY VARCHAR(255) NOT NULL,
      ASSOC_STORE VARCHAR(128) DEFAULT 'SHARED',
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (HANDLE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_STS_STORE (
      ID INTEGER AUTO_INCREMENT,
      TOKEN_ID VARCHAR(255) NOT NULL,
      TOKEN_CONTENT BLOB(1024) NOT NULL,
      CREATE_DATE TIMESTAMP NOT NULL,
      EXPIRE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATE INTEGER DEFAULT 0,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_IDENTITY_USER_DATA (
      TENANT_ID INTEGER DEFAULT -1234,
      USER_NAME VARCHAR(255) NOT NULL,
      DATA_KEY VARCHAR(255) NOT NULL,
      DATA_VALUE VARCHAR(2048),
      PRIMARY KEY (TENANT_ID, USER_NAME, DATA_KEY)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_IDENTITY_META_DATA (
      USER_NAME VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER DEFAULT -1234,
      METADATA_TYPE VARCHAR(255) NOT NULL,
      METADATA VARCHAR(255) NOT NULL,
      VALID VARCHAR(255) NOT NULL,
      PRIMARY KEY (TENANT_ID, USER_NAME, METADATA_TYPE,METADATA)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_THRIFT_SESSION (
      SESSION_ID VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(255) NOT NULL,
      CREATED_TIME VARCHAR(255) NOT NULL,
      LAST_MODIFIED_TIME VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (SESSION_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_STORE (
      SESSION_ID VARCHAR (100) NOT NULL,
      SESSION_TYPE VARCHAR(100) NOT NULL,
      OPERATION VARCHAR(10) NOT NULL,
      SESSION_OBJECT BLOB,
      TIME_CREATED BIGINT,
      TENANT_ID INTEGER DEFAULT -1,
      EXPIRY_TIME BIGINT,
      PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_TEMP_SESSION_STORE (
      SESSION_ID VARCHAR (100) NOT NULL,
      SESSION_TYPE VARCHAR(100) NOT NULL,
      OPERATION VARCHAR(10) NOT NULL,
      SESSION_OBJECT BLOB,
      TIME_CREATED BIGINT,
      TENANT_ID INTEGER DEFAULT -1,
      EXPIRY_TIME BIGINT,
      PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_USER (
      USER_ID VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      DOMAIN_NAME VARCHAR(255) NOT NULL,
      IDP_ID INTEGER NOT NULL,
      PRIMARY KEY (USER_ID),
      CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID)
  );
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_USER_SESSION_MAPPING (
      USER_ID VARCHAR(255) NOT NULL,
      SESSION_ID VARCHAR(255) NOT NULL,
      CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID)
  );
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_APP_INFO (
      SESSION_ID VARCHAR (100) NOT NULL,
      SUBJECT VARCHAR (100) NOT NULL,
      APP_ID INTEGER NOT NULL,
      INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
      PRIMARY KEY (SESSION_ID, SUBJECT, APP_ID, INBOUND_AUTH_TYPE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_SESSION_META_DATA (
      SESSION_ID VARCHAR (100) NOT NULL,
      PROPERTY_TYPE VARCHAR (100) NOT NULL,
      VALUE VARCHAR (255) NOT NULL,
      PRIMARY KEY (SESSION_ID, PROPERTY_TYPE, VALUE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS SP_APP (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      APP_NAME VARCHAR (255) NOT NULL ,
      USER_STORE VARCHAR (255) NOT NULL,
      USERNAME VARCHAR (255) NOT NULL ,
      DESCRIPTION VARCHAR (1024),
      ROLE_CLAIM VARCHAR (512),
      AUTH_TYPE VARCHAR (255) NOT NULL,
      PROVISIONING_USERSTORE_DOMAIN VARCHAR (512),
      IS_LOCAL_CLAIM_DIALECT CHAR(1) DEFAULT '1',
      IS_SEND_LOCAL_SUBJECT_ID CHAR(1) DEFAULT '0',
      IS_SEND_AUTH_LIST_OF_IDPS CHAR(1) DEFAULT '0',
      IS_USE_TENANT_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
      IS_USE_USER_DOMAIN_SUBJECT CHAR(1) DEFAULT '1',
      ENABLE_AUTHORIZATION CHAR(1) DEFAULT '0',
      SUBJECT_CLAIM_URI VARCHAR (512),
      IS_SAAS_APP CHAR(1) DEFAULT '0',
      IS_DUMB_MODE CHAR(1) DEFAULT '0',
      UUID CHAR(36),
      IMAGE_URL VARCHAR(1024),
      ACCESS_URL VARCHAR(1024),
      IS_DISCOVERABLE CHAR(1) DEFAULT '0',
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID);
  ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_UUID_CONSTRAINT UNIQUE(UUID);
  
  CREATE TABLE IF NOT EXISTS SP_METADATA (
      ID INTEGER AUTO_INCREMENT,
      SP_ID INTEGER,
      NAME VARCHAR(255) NOT NULL,
      VALUE VARCHAR(255) NOT NULL,
      DISPLAY_NAME VARCHAR(255),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (ID),
      CONSTRAINT SP_METADATA_CONSTRAINT UNIQUE (SP_ID, NAME),
      FOREIGN KEY (SP_ID) REFERENCES SP_APP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS SP_INBOUND_AUTH (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      INBOUND_AUTH_KEY VARCHAR (255),
      INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
      INBOUND_CONFIG_TYPE VARCHAR (255) NOT NULL,
      PROP_NAME VARCHAR (255),
      PROP_VALUE VARCHAR (1024) ,
      APP_ID INTEGER NOT NULL,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_INBOUND_AUTH
      ADD CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_AUTH_STEP (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      STEP_ORDER INTEGER DEFAULT 1,
      APP_ID INTEGER NOT NULL ,
      IS_SUBJECT_STEP CHAR(1) DEFAULT '0',
      IS_ATTRIBUTE_STEP CHAR(1) DEFAULT '0',
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_AUTH_STEP
      ADD CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_FEDERATED_IDP (
      ID INTEGER NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      AUTHENTICATOR_ID INTEGER NOT NULL,
      PRIMARY KEY (ID, AUTHENTICATOR_ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_FEDERATED_IDP
      ADD CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_CLAIM_DIALECT (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      SP_DIALECT VARCHAR (512) NOT NULL,
      APP_ID INTEGER NOT NULL,
      PRIMARY KEY (ID)
  );
  
  ALTER TABLE SP_CLAIM_DIALECT
      ADD CONSTRAINT DIALECTID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_CLAIM_MAPPING (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      IDP_CLAIM VARCHAR (512) NOT NULL ,
      SP_CLAIM VARCHAR (512) NOT NULL ,
      APP_ID INTEGER NOT NULL,
      IS_REQUESTED VARCHAR(128) DEFAULT '0',
      IS_MANDATORY VARCHAR(128) DEFAULT '0',
      DEFAULT_VALUE VARCHAR(255),
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_CLAIM_MAPPING
      ADD CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_ROLE_MAPPING (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      IDP_ROLE VARCHAR (255) NOT NULL ,
      SP_ROLE VARCHAR (255) NOT NULL ,
      APP_ID INTEGER NOT NULL,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_ROLE_MAPPING
      ADD CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_REQ_PATH_AUTHENTICATOR (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
      APP_ID INTEGER NOT NULL,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_REQ_PATH_AUTHENTICATOR
      ADD CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE IF NOT EXISTS SP_PROVISIONING_CONNECTOR (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID INTEGER NOT NULL,
      IDP_NAME VARCHAR (255) NOT NULL ,
      CONNECTOR_NAME VARCHAR (255) NOT NULL ,
      APP_ID INTEGER NOT NULL,
      IS_JIT_ENABLED CHAR(1) NOT NULL DEFAULT '0',
      BLOCKING CHAR(1) NOT NULL DEFAULT '0',
      RULE_ENABLED CHAR(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_PROVISIONING_CONNECTOR
      ADD CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE;
  
  CREATE TABLE SP_AUTH_SCRIPT (
      ID         INTEGER AUTO_INCREMENT NOT NULL,
      TENANT_ID  INTEGER                NOT NULL,
      APP_ID     INTEGER                NOT NULL,
      TYPE       VARCHAR(255)           NOT NULL,
      CONTENT    BLOB    DEFAULT NULL,
      IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE IF NOT EXISTS SP_TEMPLATE (
      ID         INTEGER AUTO_INCREMENT NOT NULL,
      TENANT_ID  INTEGER                NOT NULL,
      NAME VARCHAR(255) NOT NULL,
      DESCRIPTION VARCHAR(1023),
      CONTENT BLOB DEFAULT NULL,
      PRIMARY KEY (ID),
      CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME)
  );
  
  CREATE TABLE IF NOT EXISTS IDN_AUTH_WAIT_STATUS (
      ID              INTEGER AUTO_INCREMENT NOT NULL,
      TENANT_ID       INTEGER                NOT NULL,
      LONG_WAIT_KEY   VARCHAR(255)           NOT NULL,
      WAIT_STATUS     CHAR(1) NOT NULL DEFAULT '1',
      TIME_CREATED    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      EXPIRE_TIME     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (ID),
      CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY)
  );
  
  CREATE TABLE IF NOT EXISTS IDP (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER,
      NAME VARCHAR(254) NOT NULL,
      IS_ENABLED CHAR(1) NOT NULL DEFAULT '1',
      IS_PRIMARY CHAR(1) NOT NULL DEFAULT '0',
      HOME_REALM_ID VARCHAR(254),
      IMAGE MEDIUMBLOB,
      CERTIFICATE BLOB,
      ALIAS VARCHAR(254),
      INBOUND_PROV_ENABLED CHAR (1) NOT NULL DEFAULT '0',
      INBOUND_PROV_USER_STORE_ID VARCHAR(254),
      USER_CLAIM_URI VARCHAR(254),
      ROLE_CLAIM_URI VARCHAR(254),
      DESCRIPTION VARCHAR (1024),
      DEFAULT_AUTHENTICATOR_NAME VARCHAR(254),
      DEFAULT_PRO_CONNECTOR_NAME VARCHAR(254),
      PROVISIONING_ROLE VARCHAR(128),
      IS_FEDERATION_HUB CHAR(1) NOT NULL DEFAULT '0',
      IS_LOCAL_CLAIM_DIALECT CHAR(1) NOT NULL DEFAULT '0',
      DISPLAY_NAME VARCHAR(255),
      IMAGE_URL VARCHAR(1024),
      UUID CHAR(36) NOT NULL,
      PRIMARY KEY (ID),
      UNIQUE (TENANT_ID, NAME),
      UNIQUE (UUID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_ROLE (
      ID INTEGER AUTO_INCREMENT,
      IDP_ID INTEGER,
      TENANT_ID INTEGER,
      ROLE VARCHAR(254),
      PRIMARY KEY (ID),
      UNIQUE (IDP_ID, ROLE),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_ROLE_MAPPING (
      ID INTEGER AUTO_INCREMENT,
      IDP_ROLE_ID INTEGER,
      TENANT_ID INTEGER,
      USER_STORE_ID VARCHAR (253),
      LOCAL_ROLE VARCHAR(253),
      PRIMARY KEY (ID),
      UNIQUE (IDP_ROLE_ID, TENANT_ID, USER_STORE_ID, LOCAL_ROLE),
      FOREIGN KEY (IDP_ROLE_ID) REFERENCES IDP_ROLE(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_CLAIM (
      ID INTEGER AUTO_INCREMENT,
      IDP_ID INTEGER,
      TENANT_ID INTEGER,
      CLAIM VARCHAR(254),
      PRIMARY KEY (ID),
      UNIQUE (IDP_ID, CLAIM),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_CLAIM_MAPPING (
      ID INTEGER AUTO_INCREMENT,
      IDP_CLAIM_ID INTEGER,
      TENANT_ID INTEGER,
      LOCAL_CLAIM VARCHAR(253),
      DEFAULT_VALUE VARCHAR(255),
      IS_REQUESTED VARCHAR(128) DEFAULT '0',
      PRIMARY KEY (ID),
      UNIQUE (IDP_CLAIM_ID, TENANT_ID, LOCAL_CLAIM),
      FOREIGN KEY (IDP_CLAIM_ID) REFERENCES IDP_CLAIM(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER,
      IDP_ID INTEGER,
      NAME VARCHAR(255) NOT NULL,
      IS_ENABLED CHAR (1) DEFAULT '1',
      DISPLAY_NAME VARCHAR(255),
      PRIMARY KEY (ID),
      UNIQUE (TENANT_ID, IDP_ID, NAME),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_METADATA (
      ID INTEGER AUTO_INCREMENT,
      IDP_ID INTEGER,
      NAME VARCHAR(255) NOT NULL,
      VALUE VARCHAR(255) NOT NULL,
      DISPLAY_NAME VARCHAR(255),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (ID),
      CONSTRAINT IDP_METADATA_CONSTRAINT UNIQUE (IDP_ID, NAME),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_AUTHENTICATOR_PROPERTY (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER,
      AUTHENTICATOR_ID INTEGER,
      PROPERTY_KEY VARCHAR(255) NOT NULL,
      PROPERTY_VALUE VARCHAR(2047),
      IS_SECRET CHAR (1) DEFAULT '0',
      PRIMARY KEY (ID),
      UNIQUE (TENANT_ID, AUTHENTICATOR_ID, PROPERTY_KEY),
      FOREIGN KEY (AUTHENTICATOR_ID) REFERENCES IDP_AUTHENTICATOR(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_CONFIG (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER,
      IDP_ID INTEGER,
      PROVISIONING_CONNECTOR_TYPE VARCHAR(255) NOT NULL,
      IS_ENABLED CHAR (1) DEFAULT '0',
      IS_BLOCKING CHAR (1) DEFAULT '0',
      IS_RULES_ENABLED CHAR (1) DEFAULT '0',
      PRIMARY KEY (ID),
      UNIQUE (TENANT_ID, IDP_ID, PROVISIONING_CONNECTOR_TYPE),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_PROV_CONFIG_PROPERTY (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER,
      PROVISIONING_CONFIG_ID INTEGER,
      PROPERTY_KEY VARCHAR(255) NOT NULL,
      PROPERTY_VALUE VARCHAR(2048),
      PROPERTY_BLOB_VALUE BLOB,
      PROPERTY_TYPE CHAR(32) NOT NULL,
      IS_SECRET CHAR (1) DEFAULT '0',
      PRIMARY KEY (ID),
      UNIQUE (TENANT_ID, PROVISIONING_CONFIG_ID, PROPERTY_KEY),
      FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_PROVISIONING_ENTITY (
      ID INTEGER AUTO_INCREMENT,
      PROVISIONING_CONFIG_ID INTEGER,
      ENTITY_TYPE VARCHAR(255) NOT NULL,
      ENTITY_LOCAL_USERSTORE VARCHAR(255) NOT NULL,
      ENTITY_NAME VARCHAR(255) NOT NULL,
      ENTITY_VALUE VARCHAR(255),
      TENANT_ID INTEGER,
      ENTITY_LOCAL_ID VARCHAR(255),
      PRIMARY KEY (ID),
      UNIQUE (ENTITY_TYPE, TENANT_ID, ENTITY_LOCAL_USERSTORE, ENTITY_NAME, PROVISIONING_CONFIG_ID),
      UNIQUE (PROVISIONING_CONFIG_ID, ENTITY_TYPE, ENTITY_VALUE),
      FOREIGN KEY (PROVISIONING_CONFIG_ID) REFERENCES IDP_PROVISIONING_CONFIG(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDP_LOCAL_CLAIM (
      ID INTEGER AUTO_INCREMENT,
      TENANT_ID INTEGER,
      IDP_ID INTEGER,
      CLAIM_URI VARCHAR(255) NOT NULL,
      DEFAULT_VALUE VARCHAR(255),
      IS_REQUESTED VARCHAR(128) DEFAULT '0',
      PRIMARY KEY (ID),
      UNIQUE (TENANT_ID, IDP_ID, CLAIM_URI),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_ASSOCIATED_ID (
      ID INTEGER AUTO_INCREMENT,
      IDP_USER_ID VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER DEFAULT -1234,
      IDP_ID INTEGER NOT NULL,
      DOMAIN_NAME VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(255) NOT NULL,
      ASSOCIATION_ID CHAR(36) NOT NULL,
      PRIMARY KEY (ID),
      UNIQUE(IDP_USER_ID, TENANT_ID, IDP_ID),
      FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_USER_ACCOUNT_ASSOCIATION (
      ASSOCIATION_KEY VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER,
      DOMAIN_NAME VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(255) NOT NULL,
      PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS FIDO_DEVICE_STORE (
      TENANT_ID INTEGER,
      DOMAIN_NAME VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(45) NOT NULL,
      TIME_REGISTERED TIMESTAMP,
      KEY_HANDLE VARCHAR(200) NOT NULL,
      DEVICE_DATA VARCHAR(2048) NOT NULL,
      PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME, KEY_HANDLE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS FIDO2_DEVICE_STORE (
      TENANT_ID INTEGER,
      DOMAIN_NAME VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(45) NOT NULL,
      TIME_REGISTERED TIMESTAMP,
      USER_HANDLE VARCHAR(64) NOT NULL,
      CREDENTIAL_ID VARCHAR(200) NOT NULL,
      PUBLIC_KEY_COSE VARCHAR(1024) NOT NULL,
      SIGNATURE_COUNT BIGINT,
      USER_IDENTITY VARCHAR(512) NOT NULL,
      DISPLAY_NAME VARCHAR(255),
      IS_USERNAMELESS_SUPPORTED CHAR(1) DEFAULT '0',
      PRIMARY KEY (CREDENTIAL_ID, USER_HANDLE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_REQUEST (
      UUID VARCHAR (45),
      CREATED_BY VARCHAR (255),
      TENANT_ID INTEGER DEFAULT -1,
      OPERATION_TYPE VARCHAR (50),
      CREATED_AT TIMESTAMP,
      UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      STATUS VARCHAR (30),
      REQUEST BLOB,
      PRIMARY KEY (UUID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_BPS_PROFILE (
      PROFILE_NAME VARCHAR(45),
      HOST_URL_MANAGER VARCHAR(255),
      HOST_URL_WORKER VARCHAR(255),
      USERNAME VARCHAR(45),
      PASSWORD VARCHAR(1023),
      CALLBACK_HOST VARCHAR (45),
      CALLBACK_USERNAME VARCHAR (45),
      CALLBACK_PASSWORD VARCHAR (255),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (PROFILE_NAME, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_WORKFLOW(
      ID VARCHAR (45),
      WF_NAME VARCHAR (45),
      DESCRIPTION VARCHAR (255),
      TEMPLATE_ID VARCHAR (45),
      IMPL_ID VARCHAR (45),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_WORKFLOW_ASSOCIATION(
      ID INTEGER NOT NULL AUTO_INCREMENT,
      ASSOC_NAME VARCHAR (45),
      EVENT_ID VARCHAR(45),
      ASSOC_CONDITION VARCHAR (2000),
      WORKFLOW_ID VARCHAR (45),
      IS_ENABLED CHAR (1) DEFAULT '1',
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY(ID),
      FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_WORKFLOW_CONFIG_PARAM(
      WORKFLOW_ID VARCHAR (45),
      PARAM_NAME VARCHAR (45),
      PARAM_VALUE VARCHAR (1000),
      PARAM_QNAME VARCHAR (45),
      PARAM_HOLDER VARCHAR (45),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (WORKFLOW_ID, PARAM_NAME, PARAM_QNAME, PARAM_HOLDER),
      FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_REQUEST_ENTITY_RELATIONSHIP(
      REQUEST_ID VARCHAR (45),
      ENTITY_NAME VARCHAR (255),
      ENTITY_TYPE VARCHAR (50),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY(REQUEST_ID, ENTITY_NAME, ENTITY_TYPE, TENANT_ID),
      FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS WF_WORKFLOW_REQUEST_RELATION(
      RELATIONSHIP_ID VARCHAR (45),
      WORKFLOW_ID VARCHAR (45),
      REQUEST_ID VARCHAR (45),
      UPDATED_AT TIMESTAMP,
      STATUS VARCHAR (30),
      TENANT_ID INTEGER DEFAULT -1,
      PRIMARY KEY (RELATIONSHIP_ID),
      FOREIGN KEY (WORKFLOW_ID) REFERENCES WF_WORKFLOW(ID)ON DELETE CASCADE,
      FOREIGN KEY (REQUEST_ID) REFERENCES WF_REQUEST(UUID)ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_RECOVERY_DATA (
      USER_NAME VARCHAR(255) NOT NULL,
      USER_DOMAIN VARCHAR(127) NOT NULL,
      TENANT_ID INTEGER DEFAULT -1,
      CODE VARCHAR(255) NOT NULL,
      SCENARIO VARCHAR(255) NOT NULL,
      STEP VARCHAR(127) NOT NULL,
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REMAINING_SETS VARCHAR(2500) DEFAULT NULL,
      PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
      UNIQUE(CODE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_PASSWORD_HISTORY_DATA (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      USER_NAME   VARCHAR(127) NOT NULL,
      USER_DOMAIN VARCHAR(50) NOT NULL,
      TENANT_ID   INTEGER DEFAULT -1,
      SALT_VALUE  VARCHAR(255),
      HASH        VARCHAR(255) NOT NULL,
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY(ID),
      UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_CLAIM_DIALECT (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      DIALECT_URI VARCHAR (255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_CLAIM (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      DIALECT_ID INTEGER NOT NULL,
      CLAIM_URI VARCHAR (255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
      CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPED_ATTRIBUTE (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      LOCAL_CLAIM_ID INTEGER,
      USER_STORE_DOMAIN_NAME VARCHAR (255) NOT NULL,
      ATTRIBUTE_NAME VARCHAR (255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
      CONSTRAINT USER_STORE_DOMAIN_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, USER_STORE_DOMAIN_NAME, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_CLAIM_PROPERTY (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      LOCAL_CLAIM_ID INTEGER,
      PROPERTY_NAME VARCHAR (255) NOT NULL,
      PROPERTY_VALUE VARCHAR (255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
      CONSTRAINT PROPERTY_NAME_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, PROPERTY_NAME, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_CLAIM_MAPPING (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      EXT_CLAIM_ID INTEGER NOT NULL,
      MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
      FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
      CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS  IDN_SAML2_ASSERTION_STORE (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      SAML2_ID  VARCHAR(255) ,
      SAML2_ISSUER  VARCHAR(255) ,
      SAML2_SUBJECT  VARCHAR(255) ,
      SAML2_SESSION_INDEX  VARCHAR(255) ,
      SAML2_AUTHN_CONTEXT_CLASS_REF  VARCHAR(255) ,
      SAML2_ASSERTION  VARCHAR(4096) ,
      ASSERTION BLOB ,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IDN_SAML2_ARTIFACT_STORE (
      ID INT(11) NOT NULL AUTO_INCREMENT,
      SOURCE_ID VARCHAR(255) NOT NULL,
      MESSAGE_HANDLER VARCHAR(255) NOT NULL,
      AUTHN_REQ_DTO BLOB NOT NULL,
      SESSION_ID VARCHAR(255) NOT NULL,
      EXP_TIMESTAMP TIMESTAMP NOT NULL,
      INIT_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      ASSERTION_ID VARCHAR(255),
      PRIMARY KEY (`ID`)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OIDC_JTI (
      JWT_ID VARCHAR(255) NOT NULL,
      EXP_TIME TIMESTAMP NOT NULL ,
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
      PRIMARY KEY (JWT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS  IDN_OIDC_PROPERTY (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      TENANT_ID  INTEGER,
      CONSUMER_KEY  VARCHAR(255) ,
      PROPERTY_KEY  VARCHAR(255) NOT NULL,
      PROPERTY_VALUE  VARCHAR(2047) ,
      PRIMARY KEY (ID),
      FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_REFERENCE (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      CONSUMER_KEY_ID INTEGER ,
      CODE_ID VARCHAR(255) ,
      TOKEN_ID VARCHAR(255) ,
      SESSION_DATA_KEY VARCHAR(255),
      PRIMARY KEY (ID),
      FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
      FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE,
      FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJECT_CLAIMS (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      REQ_OBJECT_ID INTEGER,
      CLAIM_ATTRIBUTE VARCHAR(255) ,
      ESSENTIAL CHAR(1) NOT NULL DEFAULT '0' ,
      VALUE VARCHAR(255) ,
      IS_USERINFO CHAR(1) NOT NULL DEFAULT '0',
      PRIMARY KEY (ID),
      FOREIGN KEY (REQ_OBJECT_ID) REFERENCES IDN_OIDC_REQ_OBJECT_REFERENCE (ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      REQ_OBJECT_CLAIMS_ID INTEGER ,
      CLAIM_VALUES VARCHAR(255) ,
      PRIMARY KEY (ID),
      FOREIGN KEY (REQ_OBJECT_CLAIMS_ID) REFERENCES  IDN_OIDC_REQ_OBJECT_CLAIMS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_CERTIFICATE (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(100),
      CERTIFICATE_IN_PEM BLOB,
      TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY(ID),
      CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OIDC_SCOPE_CLAIM_MAPPING (
      ID INTEGER NOT NULL AUTO_INCREMENT,
      SCOPE_ID INTEGER NOT NULL,
      EXTERNAL_CLAIM_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
      FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
      UNIQUE (SCOPE_ID, EXTERNAL_CLAIM_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_FUNCTION_LIBRARY (
  	NAME VARCHAR(255) NOT NULL,
  	DESCRIPTION VARCHAR(1023),
  	TYPE VARCHAR(255) NOT NULL,
  	TENANT_ID INTEGER NOT NULL,
  	DATA BLOB NOT NULL,
  	PRIMARY KEY (TENANT_ID,NAME)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_CIBA_AUTH_CODE (
      AUTH_CODE_KEY CHAR (36),
      AUTH_REQ_ID CHAR (36),
      ISSUED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      CONSUMER_KEY VARCHAR(255),
      LAST_POLLED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      POLLING_INTERVAL INTEGER,
      EXPIRES_IN  INTEGER,
      AUTHENTICATED_USER_NAME VARCHAR(255),
      USER_STORE_DOMAIN VARCHAR(100),
      TENANT_ID INTEGER,
      AUTH_REQ_STATUS VARCHAR (100) DEFAULT 'REQUESTED',
      IDP_ID INTEGER,
      UNIQUE(AUTH_REQ_ID),
      PRIMARY KEY (AUTH_CODE_KEY),
      FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_CIBA_REQUEST_SCOPES (
      AUTH_CODE_KEY CHAR (36),
      SCOPE VARCHAR (255),
      FOREIGN KEY (AUTH_CODE_KEY) REFERENCES IDN_OAUTH2_CIBA_AUTH_CODE(AUTH_CODE_KEY) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_FED_AUTH_SESSION_MAPPING (
      IDP_SESSION_ID VARCHAR(255) NOT NULL,
      SESSION_ID VARCHAR(255) NOT NULL,
      IDP_NAME VARCHAR(255) NOT NULL,
      AUTHENTICATOR_ID VARCHAR(255),
      PROTOCOL_TYPE VARCHAR(255),
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (IDP_SESSION_ID)
  )ENGINE INNODB;
  
  -- --------------------------- INDEX CREATION -----------------------------
  -- IDN_OAUTH2_ACCESS_TOKEN --
  CREATE INDEX IDX_TC ON IDN_OAUTH2_ACCESS_TOKEN(TIME_CREATED);
  CREATE INDEX IDX_ATH ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN_HASH);
  CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_STATE, USER_TYPE);
  CREATE INDEX IDX_AT_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN);
  CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);
  CREATE INDEX IDX_AT_AU_CKID_TS_UT ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);
  CREATE INDEX IDX_AT_RTH ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN_HASH);
  CREATE INDEX IDX_AT_CKID_AU_TID_UD_TSH_TS ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE);
  
  -- IDN_OAUTH2_AUTHORIZATION_CODE --
  CREATE INDEX IDX_AUTHORIZATION_CODE_HASH ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHORIZATION_CODE_HASH, CONSUMER_KEY_ID);
  CREATE INDEX IDX_AUTHORIZATION_CODE_AU_TI ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER, TENANT_ID, USER_DOMAIN, STATE);
  CREATE INDEX IDX_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(CONSUMER_KEY_ID);
  CREATE INDEX IDX_AC_TID ON IDN_OAUTH2_AUTHORIZATION_CODE(TOKEN_ID);
  
  -- IDN_SCIM_GROUP --
  CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME);
  CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME(500));
  
  -- IDN_AUTH_SESSION_STORE --
  CREATE INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);
  
  -- IDN_AUTH_TEMP_SESSION_STORE --
  CREATE INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);
  
  -- IDN_OIDC_SCOPE_CLAIM_MAPPING --
  CREATE INDEX IDX_AT_SI_ECI ON IDN_OIDC_SCOPE_CLAIM_MAPPING(SCOPE_ID, EXTERNAL_CLAIM_ID);
  
  -- IDN_OAUTH2_SCOPE --
  CREATE INDEX IDX_SC_TID ON IDN_OAUTH2_SCOPE(TENANT_ID);
  
  -- IDN_OAUTH2_SCOPE_BINDING --
  CREATE INDEX IDX_SB_SCPID ON IDN_OAUTH2_SCOPE_BINDING(SCOPE_ID);
  
  -- IDN_OIDC_REQ_OBJECT_REFERENCE --
  CREATE INDEX IDX_OROR_TID ON IDN_OIDC_REQ_OBJECT_REFERENCE(TOKEN_ID);
  
  -- IDN_OAUTH2_ACCESS_TOKEN_SCOPE --
  CREATE INDEX IDX_ATS_TID ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE(TOKEN_ID);
  
  -- SP_TEMPLATE --
  CREATE INDEX IDX_SP_TEMPLATE ON SP_TEMPLATE (TENANT_ID, NAME);
  
  -- IDN_AUTH_USER --
  CREATE INDEX IDX_AUTH_USER_UN_TID_DN ON IDN_AUTH_USER (USER_NAME, TENANT_ID, DOMAIN_NAME);
  CREATE INDEX IDX_AUTH_USER_DN_TOD ON IDN_AUTH_USER (DOMAIN_NAME, TENANT_ID);
  
  -- IDN_AUTH_USER_SESSION_MAPPING --
  CREATE INDEX IDX_USER_ID ON IDN_AUTH_USER_SESSION_MAPPING (USER_ID);
  CREATE INDEX IDX_SESSION_ID ON IDN_AUTH_USER_SESSION_MAPPING (SESSION_ID);
  
  -- IDN_OAUTH_CONSUMER_APPS --
  CREATE INDEX IDX_OCA_UM_TID_UD_APN ON IDN_OAUTH_CONSUMER_APPS(USERNAME,TENANT_ID,USER_DOMAIN, APP_NAME);
  
  -- IDX_SPI_APP --
  CREATE INDEX IDX_SPI_APP ON SP_INBOUND_AUTH(APP_ID);
  
  -- IDN_OIDC_PROPERTY --
  CREATE INDEX IDX_IOP_TID_CK ON IDN_OIDC_PROPERTY(TENANT_ID,CONSUMER_KEY);
  
  -- IDN_FIDO2_PROPERTY --
  CREATE INDEX IDX_FIDO2_STR ON FIDO2_DEVICE_STORE(USER_NAME, TENANT_ID, DOMAIN_NAME, CREDENTIAL_ID, USER_HANDLE);
  
  -- IDN_ASSOCIATED_ID --
  CREATE INDEX IDX_AI_DN_UN_AI ON IDN_ASSOCIATED_ID(DOMAIN_NAME, USER_NAME, ASSOCIATION_ID);
  
  -- IDN_OAUTH2_TOKEN_BINDING --
  CREATE INDEX IDX_IDN_AUTH_BIND ON IDN_OAUTH2_TOKEN_BINDING (TOKEN_BINDING_REF);
  
  -- IDN_FED_AUTH_SESSION_MAPPING --
  CREATE INDEX IDX_FEDERATED_AUTH_SESSION_ID ON IDN_FED_AUTH_SESSION_MAPPING (SESSION_ID);
  
  -- End of IDENTITY Tables--
  
  -- Start of CONSENT-MGT Tables --
  
  CREATE TABLE CM_PII_CATEGORY (
      ID           INTEGER AUTO_INCREMENT,
      NAME         VARCHAR(255) NOT NULL,
      DESCRIPTION  VARCHAR(1023),
      DISPLAY_NAME VARCHAR(255),
      IS_SENSITIVE INTEGER      NOT NULL,
      TENANT_ID    INTEGER DEFAULT '-1234',
      UNIQUE KEY (NAME, TENANT_ID),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE CM_RECEIPT (
      CONSENT_RECEIPT_ID  VARCHAR(255) NOT NULL,
      VERSION             VARCHAR(255) NOT NULL,
      JURISDICTION        VARCHAR(255) NOT NULL,
      CONSENT_TIMESTAMP   TIMESTAMP    NOT NULL,
      COLLECTION_METHOD   VARCHAR(255) NOT NULL,
      LANGUAGE            VARCHAR(255) NOT NULL,
      PII_PRINCIPAL_ID    VARCHAR(255) NOT NULL,
      PRINCIPAL_TENANT_ID INTEGER DEFAULT '-1234',
      POLICY_URL          VARCHAR(255) NOT NULL,
      STATE               VARCHAR(255) NOT NULL,
      PII_CONTROLLER      VARCHAR(2048) NOT NULL,
      PRIMARY KEY (CONSENT_RECEIPT_ID)
  );
  
  CREATE TABLE CM_PURPOSE (
      ID            INTEGER AUTO_INCREMENT,
      NAME          VARCHAR(255) NOT NULL,
      DESCRIPTION   VARCHAR(1023),
      PURPOSE_GROUP VARCHAR(255) NOT NULL,
      GROUP_TYPE    VARCHAR(255) NOT NULL,
      TENANT_ID     INTEGER DEFAULT '-1234',
      UNIQUE KEY (NAME, TENANT_ID, PURPOSE_GROUP, GROUP_TYPE),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE CM_PURPOSE_CATEGORY (
      ID          INTEGER AUTO_INCREMENT,
      NAME        VARCHAR(255) NOT NULL,
      DESCRIPTION VARCHAR(1023),
      TENANT_ID   INTEGER DEFAULT '-1234',
      UNIQUE KEY (NAME, TENANT_ID),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE CM_RECEIPT_SP_ASSOC (
      ID                 INTEGER AUTO_INCREMENT,
      CONSENT_RECEIPT_ID VARCHAR(255) NOT NULL,
      SP_NAME            VARCHAR(255) NOT NULL,
      SP_DISPLAY_NAME    VARCHAR(255),
      SP_DESCRIPTION     VARCHAR(255),
      SP_TENANT_ID       INTEGER DEFAULT '-1234',
      UNIQUE KEY (CONSENT_RECEIPT_ID, SP_NAME, SP_TENANT_ID),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE CM_SP_PURPOSE_ASSOC (
      ID                     INTEGER AUTO_INCREMENT,
      RECEIPT_SP_ASSOC       INTEGER      NOT NULL,
      PURPOSE_ID             INTEGER      NOT NULL,
      CONSENT_TYPE           VARCHAR(255) NOT NULL,
      IS_PRIMARY_PURPOSE     INTEGER      NOT NULL,
      TERMINATION            VARCHAR(255) NOT NULL,
      THIRD_PARTY_DISCLOSURE INTEGER      NOT NULL,
      THIRD_PARTY_NAME       VARCHAR(255),
      UNIQUE KEY (RECEIPT_SP_ASSOC, PURPOSE_ID),
      PRIMARY KEY (ID)
  );
  
  CREATE TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC (
      SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
      PURPOSE_CATEGORY_ID INTEGER NOT NULL,
      UNIQUE KEY (SP_PURPOSE_ASSOC_ID, PURPOSE_CATEGORY_ID)
  );
  
  CREATE TABLE CM_PURPOSE_PII_CAT_ASSOC (
      PURPOSE_ID         INTEGER NOT NULL,
      CM_PII_CATEGORY_ID INTEGER NOT NULL,
      IS_MANDATORY       INTEGER NOT NULL,
      UNIQUE KEY (PURPOSE_ID, CM_PII_CATEGORY_ID)
  );
  
  CREATE TABLE CM_SP_PURPOSE_PII_CAT_ASSOC (
      SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
      PII_CATEGORY_ID     INTEGER NOT NULL,
      VALIDITY            VARCHAR(1023),
      UNIQUE KEY (SP_PURPOSE_ASSOC_ID, PII_CATEGORY_ID)
  );
  
  CREATE TABLE CM_CONSENT_RECEIPT_PROPERTY (
      CONSENT_RECEIPT_ID VARCHAR(255)  NOT NULL,
      NAME               VARCHAR(255)  NOT NULL,
      VALUE              VARCHAR(1023) NOT NULL,
      UNIQUE KEY (CONSENT_RECEIPT_ID, NAME)
  );
  
  ALTER TABLE CM_RECEIPT_SP_ASSOC
      ADD CONSTRAINT CM_RECEIPT_SP_ASSOC_fk0 FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID);
  
  ALTER TABLE CM_SP_PURPOSE_ASSOC
      ADD CONSTRAINT CM_SP_PURPOSE_ASSOC_fk0 FOREIGN KEY (RECEIPT_SP_ASSOC) REFERENCES CM_RECEIPT_SP_ASSOC (ID);
  
  ALTER TABLE CM_SP_PURPOSE_ASSOC
      ADD CONSTRAINT CM_SP_PURPOSE_ASSOC_fk1 FOREIGN KEY (PURPOSE_ID) REFERENCES CM_PURPOSE (ID);
  
  ALTER TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC
      ADD CONSTRAINT CM_SP_P_P_CAT_ASSOC_fk0 FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID);
  
  ALTER TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC
      ADD CONSTRAINT CM_SP_P_P_CAT_ASSOC_fk1 FOREIGN KEY (PURPOSE_CATEGORY_ID) REFERENCES CM_PURPOSE_CATEGORY (ID);
  
  ALTER TABLE CM_SP_PURPOSE_PII_CAT_ASSOC
      ADD CONSTRAINT CM_SP_P_PII_CAT_ASSOC_fk0 FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID);
  
  ALTER TABLE CM_SP_PURPOSE_PII_CAT_ASSOC
      ADD CONSTRAINT CM_SP_P_PII_CAT_ASSOC_fk1 FOREIGN KEY (PII_CATEGORY_ID) REFERENCES CM_PII_CATEGORY (ID);
  
  ALTER TABLE CM_CONSENT_RECEIPT_PROPERTY
      ADD CONSTRAINT CM_CONSENT_RECEIPT_PRT_fk0 FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID);
  
  INSERT INTO CM_PURPOSE (NAME, DESCRIPTION, PURPOSE_GROUP, GROUP_TYPE, TENANT_ID) VALUES ('DEFAULT', 'For core functionalities of the product', 'DEFAULT', 'SP', '-1234');
  
  INSERT INTO CM_PURPOSE_CATEGORY (NAME, DESCRIPTION, TENANT_ID) VALUES ('DEFAULT','For core functionalities of the product', '-1234');
  -- End of CONSENT-MGT Tables --
  
  -- Start of API-MGT Tables --
  CREATE TABLE IF NOT EXISTS AM_SUBSCRIBER (
      SUBSCRIBER_ID INTEGER AUTO_INCREMENT,
      USER_ID VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      EMAIL_ADDRESS VARCHAR(256) NULL,
      DATE_SUBSCRIBED TIMESTAMP NOT NULL,
      PRIMARY KEY (SUBSCRIBER_ID),
      CREATED_BY VARCHAR(100),
      CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UNIQUE (TENANT_ID,USER_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_APPLICATION (
      APPLICATION_ID INTEGER AUTO_INCREMENT,
      NAME VARCHAR(100),
      SUBSCRIBER_ID INTEGER,
      APPLICATION_TIER VARCHAR(50) DEFAULT 'Unlimited',
      CALLBACK_URL VARCHAR(512),
      DESCRIPTION VARCHAR(512),
      APPLICATION_STATUS VARCHAR(50) DEFAULT 'APPROVED',
      GROUP_ID VARCHAR(100),
      CREATED_BY VARCHAR(100),
      CREATED_TIME TIMESTAMP,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UUID VARCHAR(256),
      TOKEN_TYPE VARCHAR(10),
      FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY(APPLICATION_ID),
      UNIQUE (NAME,SUBSCRIBER_ID),
      UNIQUE (UUID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API (
      API_ID INTEGER AUTO_INCREMENT,
      API_PROVIDER VARCHAR(200),
      API_NAME VARCHAR(200),
      API_VERSION VARCHAR(30),
      CONTEXT VARCHAR(256),
      CONTEXT_TEMPLATE VARCHAR(256),
      API_TIER VARCHAR(256),
      API_TYPE VARCHAR(10),
      CREATED_BY VARCHAR(100),
      CREATED_TIME TIMESTAMP,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY(API_ID),
      UNIQUE (API_PROVIDER,API_NAME,API_VERSION)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_URL_MAPPING (
      URL_MAPPING_ID INTEGER AUTO_INCREMENT,
      API_ID INTEGER NOT NULL,
      HTTP_METHOD VARCHAR(20) NULL,
      AUTH_SCHEME VARCHAR(50) NULL,
      URL_PATTERN VARCHAR(512) NULL,
      THROTTLING_TIER varchar(512) DEFAULT NULL,
      MEDIATION_SCRIPT BLOB,
      PRIMARY KEY (URL_MAPPING_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_SECURITY_AUDIT_UUID_MAPPING (
      API_ID INTEGER NOT NULL,
      AUDIT_UUID VARCHAR(255) NOT NULL,
      FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (API_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_PRODUCT_MAPPING (
  	API_PRODUCT_MAPPING_ID INTEGER AUTO_INCREMENT,
  	API_ID INTEGER,
  	URL_MAPPING_ID INTEGER,
  	FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
  	FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
  	PRIMARY KEY(API_PRODUCT_MAPPING_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_SUBSCRIPTION (
      SUBSCRIPTION_ID INTEGER AUTO_INCREMENT,
      TIER_ID VARCHAR(50),
      API_ID INTEGER,
      LAST_ACCESSED TIMESTAMP NULL,
      APPLICATION_ID INTEGER,
      SUB_STATUS VARCHAR(50),
      SUBS_CREATE_STATE VARCHAR(50) DEFAULT 'SUBSCRIBE',
      CREATED_BY VARCHAR(100),
      CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      UUID VARCHAR(256),
      FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (SUBSCRIPTION_ID),
      UNIQUE (UUID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_SUBSCRIPTION_KEY_MAPPING (
      SUBSCRIPTION_ID INTEGER,
      ACCESS_TOKEN VARCHAR(512),
      KEY_TYPE VARCHAR(512) NOT NULL,
      FOREIGN KEY(SUBSCRIPTION_ID) REFERENCES AM_SUBSCRIPTION(SUBSCRIPTION_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY(SUBSCRIPTION_ID,ACCESS_TOKEN)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_APPLICATION_KEY_MAPPING (
      APPLICATION_ID INTEGER,
      CONSUMER_KEY VARCHAR(255),
      KEY_TYPE VARCHAR(512) NOT NULL,
      STATE VARCHAR(30) NOT NULL,
      CREATE_MODE VARCHAR(30) DEFAULT 'CREATED',
      FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY(APPLICATION_ID,KEY_TYPE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_LC_EVENT (
      EVENT_ID INTEGER AUTO_INCREMENT,
      API_ID INTEGER NOT NULL,
      PREVIOUS_STATE VARCHAR(50),
      NEW_STATE VARCHAR(50) NOT NULL,
      USER_ID VARCHAR(255) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      EVENT_DATE TIMESTAMP NOT NULL,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (EVENT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE AM_APP_KEY_DOMAIN_MAPPING (
      CONSUMER_KEY VARCHAR(255),
      AUTHZ_DOMAIN VARCHAR(255) DEFAULT 'ALL',
      PRIMARY KEY (CONSUMER_KEY,AUTHZ_DOMAIN)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_COMMENTS (
      COMMENT_ID VARCHAR(255) NOT NULL,
      COMMENT_TEXT VARCHAR(512),
      COMMENTED_USER VARCHAR(255),
      DATE_COMMENTED TIMESTAMP NOT NULL,
      API_ID INTEGER,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (COMMENT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_RATINGS (
      RATING_ID VARCHAR(255) NOT NULL,
      API_ID INTEGER,
      RATING INTEGER,
      SUBSCRIBER_ID INTEGER,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (RATING_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_TIER_PERMISSIONS (
      TIER_PERMISSIONS_ID INTEGER AUTO_INCREMENT,
      TIER VARCHAR(50) NOT NULL,
      PERMISSIONS_TYPE VARCHAR(50) NOT NULL,
      ROLES VARCHAR(512) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY(TIER_PERMISSIONS_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_EXTERNAL_STORES (
      APISTORE_ID INTEGER AUTO_INCREMENT,
      API_ID INTEGER,
      STORE_ID VARCHAR(255) NOT NULL,
      STORE_DISPLAY_NAME VARCHAR(255) NOT NULL,
      STORE_ENDPOINT VARCHAR(255) NOT NULL,
      STORE_TYPE VARCHAR(255) NOT NULL,
      LAST_UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (APISTORE_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_WORKFLOWS(
      WF_ID INTEGER AUTO_INCREMENT,
      WF_REFERENCE VARCHAR(255) NOT NULL,
      WF_TYPE VARCHAR(255) NOT NULL,
      WF_STATUS VARCHAR(255) NOT NULL,
      WF_CREATED_TIME TIMESTAMP,
      WF_UPDATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
      WF_STATUS_DESC VARCHAR(1000),
      TENANT_ID INTEGER,
      TENANT_DOMAIN VARCHAR(255),
      WF_EXTERNAL_REFERENCE VARCHAR(255) NOT NULL,
      PRIMARY KEY (WF_ID),
      UNIQUE (WF_EXTERNAL_REFERENCE)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_APPLICATION_REGISTRATION (
      REG_ID INT AUTO_INCREMENT,
      SUBSCRIBER_ID INT,
      WF_REF VARCHAR(255) NOT NULL,
      APP_ID INT,
      TOKEN_TYPE VARCHAR(30),
      TOKEN_SCOPE VARCHAR(1500) DEFAULT 'default',
      INPUTS VARCHAR(7500),
      ALLOWED_DOMAINS VARCHAR(256),
      VALIDITY_PERIOD BIGINT,
      UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE),
      FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      FOREIGN KEY(APP_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE ON DELETE RESTRICT,
      PRIMARY KEY (REG_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_SCOPES (
      API_ID  INTEGER NOT NULL,
      SCOPE_ID  INTEGER NOT NULL,
      FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE ON UPDATE CASCADE,
      FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE ON UPDATE CASCADE,
      PRIMARY KEY (API_ID, SCOPE_ID)
  )ENGINE = INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_API_DEFAULT_VERSION (
      DEFAULT_VERSION_ID INT AUTO_INCREMENT,
      API_NAME VARCHAR(256) NOT NULL ,
      API_PROVIDER VARCHAR(256) NOT NULL ,
      DEFAULT_API_VERSION VARCHAR(30) ,
      PUBLISHED_DEFAULT_API_VERSION VARCHAR(30) ,
      PRIMARY KEY (DEFAULT_VERSION_ID)
  )ENGINE = INNODB;
  
  CREATE INDEX IDX_SUB_APP_ID ON AM_SUBSCRIPTION (APPLICATION_ID, SUBSCRIPTION_ID);
  
  CREATE TABLE IF NOT EXISTS AM_MONETIZATION_USAGE (
      ID VARCHAR(100) NOT NULL,
      STATE VARCHAR(50) NOT NULL,
      STATUS VARCHAR(50) NOT NULL,
      STARTED_TIME VARCHAR(50) NOT NULL,
      PUBLISHED_TIME VARCHAR(50) NOT NULL,
      PRIMARY KEY(ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_ALERT_TYPES (
      ALERT_TYPE_ID INTEGER AUTO_INCREMENT,
      ALERT_TYPE_NAME VARCHAR(255) NOT NULL ,
      STAKE_HOLDER VARCHAR(100) NOT NULL,
      PRIMARY KEY (ALERT_TYPE_ID)
  )ENGINE = INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_ALERT_TYPES_VALUES (
      ALERT_TYPE_ID INTEGER,
      USER_NAME VARCHAR(255) NOT NULL ,
      STAKE_HOLDER VARCHAR(100) NOT NULL ,
      PRIMARY KEY (ALERT_TYPE_ID,USER_NAME,STAKE_HOLDER)
  )ENGINE = INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_ALERT_EMAILLIST (
      EMAIL_LIST_ID INTEGER AUTO_INCREMENT,
      USER_NAME VARCHAR(255) NOT NULL ,
      STAKE_HOLDER VARCHAR(100) NOT NULL ,
      PRIMARY KEY (EMAIL_LIST_ID,USER_NAME,STAKE_HOLDER)
  )ENGINE = INNODB;
  
  CREATE TABLE IF NOT EXISTS  AM_ALERT_EMAILLIST_DETAILS (
      EMAIL_LIST_ID INTEGER,
      EMAIL VARCHAR(255),
      PRIMARY KEY (EMAIL_LIST_ID,EMAIL)
  )ENGINE = INNODB;
  
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalResponseTime', 'publisher');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalBackendTime', 'publisher');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalRequestsPerMin', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalRequestPattern', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('UnusualIPAccess', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('FrequentTierLimitHitting', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('ApiHealthMonitor', 'publisher');
  
  
  
  -- AM Throttling tables --
  
  CREATE TABLE IF NOT EXISTS AM_POLICY_SUBSCRIPTION (
      POLICY_ID INT(11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(512) NOT NULL,
      DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
      TENANT_ID INT(11) NOT NULL,
      DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
      QUOTA_TYPE VARCHAR(25) NOT NULL,
      QUOTA INT(11) NOT NULL,
      QUOTA_UNIT VARCHAR(10) NULL,
      UNIT_TIME INT(11) NOT NULL,
      TIME_UNIT VARCHAR(25) NOT NULL,
      RATE_LIMIT_COUNT INT(11) NULL DEFAULT NULL,
      RATE_LIMIT_TIME_UNIT VARCHAR(25) NULL DEFAULT NULL,
      IS_DEPLOYED TINYINT(1) NOT NULL DEFAULT 0,
      CUSTOM_ATTRIBUTES BLOB DEFAULT NULL,
      STOP_ON_QUOTA_REACH BOOLEAN NOT NULL DEFAULT 0,
      BILLING_PLAN VARCHAR(20) NOT NULL,
      UUID VARCHAR(256),
      MONETIZATION_PLAN VARCHAR(25) NULL DEFAULT NULL,
      FIXED_RATE VARCHAR(15) NULL DEFAULT NULL,
      BILLING_CYCLE VARCHAR(15) NULL DEFAULT NULL,
      PRICE_PER_REQUEST VARCHAR(15) NULL DEFAULT NULL,
      CURRENCY VARCHAR(15) NULL DEFAULT NULL,
      PRIMARY KEY (POLICY_ID),
      UNIQUE INDEX AM_POLICY_SUBSCRIPTION_NAME_TENANT (NAME, TENANT_ID),
      UNIQUE (UUID)
  )ENGINE = InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_POLICY_APPLICATION (
      POLICY_ID INT(11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(512) NOT NULL,
      DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
      TENANT_ID INT(11) NOT NULL,
      DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
      QUOTA_TYPE VARCHAR(25) NOT NULL,
      QUOTA INT(11) NOT NULL,
      QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
      UNIT_TIME INT(11) NOT NULL,
      TIME_UNIT VARCHAR(25) NOT NULL,
      IS_DEPLOYED TINYINT(1) NOT NULL DEFAULT 0,
      CUSTOM_ATTRIBUTES BLOB DEFAULT NULL,
      UUID VARCHAR(256),
      PRIMARY KEY (POLICY_ID),
      UNIQUE INDEX APP_NAME_TENANT (NAME, TENANT_ID),
      UNIQUE (UUID)
  )ENGINE = InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_POLICY_HARD_THROTTLING (
      POLICY_ID INT(11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(512) NOT NULL,
      TENANT_ID INT(11) NOT NULL,
      DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
      QUOTA_TYPE VARCHAR(25) NOT NULL,
      QUOTA INT(11) NOT NULL,
      QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
      UNIT_TIME INT(11) NOT NULL,
      TIME_UNIT VARCHAR(25) NOT NULL,
      IS_DEPLOYED TINYINT(1) NOT NULL DEFAULT 0,
      PRIMARY KEY (POLICY_ID),
      UNIQUE INDEX POLICY_HARD_NAME_TENANT (NAME, TENANT_ID)
  )ENGINE = InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_API_THROTTLE_POLICY (
      POLICY_ID INT(11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(512) NOT NULL,
      DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
      TENANT_ID INT(11) NOT NULL,
      DESCRIPTION VARCHAR (1024),
      DEFAULT_QUOTA_TYPE VARCHAR(25) NOT NULL,
      DEFAULT_QUOTA INTEGER NOT NULL,
      DEFAULT_QUOTA_UNIT VARCHAR(10) NULL,
      DEFAULT_UNIT_TIME INTEGER NOT NULL,
      DEFAULT_TIME_UNIT VARCHAR(25) NOT NULL,
      APPLICABLE_LEVEL VARCHAR(25) NOT NULL,
      IS_DEPLOYED TINYINT(1) NOT NULL DEFAULT 0,
      UUID VARCHAR(256),
      PRIMARY KEY (POLICY_ID),
      UNIQUE INDEX API_NAME_TENANT (NAME, TENANT_ID),
      UNIQUE (UUID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_CONDITION_GROUP (
      CONDITION_GROUP_ID INTEGER NOT NULL AUTO_INCREMENT,
      POLICY_ID INTEGER NOT NULL,
      QUOTA_TYPE VARCHAR(25),
      QUOTA INTEGER NOT NULL,
      QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
      UNIT_TIME INTEGER NOT NULL,
      TIME_UNIT VARCHAR(25) NOT NULL,
      DESCRIPTION VARCHAR (1024) NULL DEFAULT NULL,
      PRIMARY KEY (CONDITION_GROUP_ID),
      FOREIGN KEY (POLICY_ID) REFERENCES AM_API_THROTTLE_POLICY(POLICY_ID) ON DELETE CASCADE ON UPDATE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_QUERY_PARAMETER_CONDITION (
      QUERY_PARAMETER_ID INTEGER NOT NULL AUTO_INCREMENT,
      CONDITION_GROUP_ID INTEGER NOT NULL,
      PARAMETER_NAME VARCHAR(255) DEFAULT NULL,
      PARAMETER_VALUE VARCHAR(255) DEFAULT NULL,
      IS_PARAM_MAPPING BOOLEAN DEFAULT 1,
      PRIMARY KEY (QUERY_PARAMETER_ID),
      FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_HEADER_FIELD_CONDITION (
      HEADER_FIELD_ID INTEGER NOT NULL AUTO_INCREMENT,
      CONDITION_GROUP_ID INTEGER NOT NULL,
      HEADER_FIELD_NAME VARCHAR(255) DEFAULT NULL,
      HEADER_FIELD_VALUE VARCHAR(255) DEFAULT NULL,
      IS_HEADER_FIELD_MAPPING BOOLEAN DEFAULT 1,
      PRIMARY KEY (HEADER_FIELD_ID),
      FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_JWT_CLAIM_CONDITION (
      JWT_CLAIM_ID INTEGER NOT NULL AUTO_INCREMENT,
      CONDITION_GROUP_ID INTEGER NOT NULL,
      CLAIM_URI VARCHAR(512) DEFAULT NULL,
      CLAIM_ATTRIB VARCHAR(1024) DEFAULT NULL,
      IS_CLAIM_MAPPING BOOLEAN DEFAULT 1,
      PRIMARY KEY (JWT_CLAIM_ID),
      FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_IP_CONDITION (
      AM_IP_CONDITION_ID INT NOT NULL AUTO_INCREMENT,
      STARTING_IP VARCHAR(45) NULL,
      ENDING_IP VARCHAR(45) NULL,
      SPECIFIC_IP VARCHAR(45) NULL,
      WITHIN_IP_RANGE BOOLEAN DEFAULT 1,
      CONDITION_GROUP_ID INT NULL,
      PRIMARY KEY (AM_IP_CONDITION_ID),
      INDEX fk_AM_IP_CONDITION_1_idx (CONDITION_GROUP_ID ASC),  CONSTRAINT fk_AM_IP_CONDITION_1    FOREIGN KEY (CONDITION_GROUP_ID)
      REFERENCES AM_CONDITION_GROUP (CONDITION_GROUP_ID)   ON DELETE CASCADE ON UPDATE CASCADE)
  ENGINE = InnoDB;
  
  
  CREATE TABLE IF NOT EXISTS AM_POLICY_GLOBAL (
      POLICY_ID INT(11) NOT NULL AUTO_INCREMENT,
      NAME VARCHAR(512) NOT NULL,
      KEY_TEMPLATE VARCHAR(512) NOT NULL,
      TENANT_ID INT(11) NOT NULL,
      DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
      SIDDHI_QUERY BLOB DEFAULT NULL,
      IS_DEPLOYED TINYINT(1) NOT NULL DEFAULT 0,
      UUID VARCHAR(256),
      PRIMARY KEY (POLICY_ID),
      UNIQUE (UUID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_THROTTLE_TIER_PERMISSIONS (
      THROTTLE_TIER_PERMISSIONS_ID INT NOT NULL AUTO_INCREMENT,
      TIER VARCHAR(50) NULL,
      PERMISSIONS_TYPE VARCHAR(50) NULL,
      ROLES VARCHAR(512) NULL,
      TENANT_ID INT(11) NULL,
      PRIMARY KEY (THROTTLE_TIER_PERMISSIONS_ID))
  ENGINE = InnoDB;
  
  CREATE TABLE `AM_BLOCK_CONDITIONS` (
      `CONDITION_ID` int(11) NOT NULL AUTO_INCREMENT,
      `TYPE` varchar(45) DEFAULT NULL,
      `VALUE` varchar(512) DEFAULT NULL,
      `ENABLED` varchar(45) DEFAULT NULL,
      `DOMAIN` varchar(45) DEFAULT NULL,
      `UUID` VARCHAR(256),
      PRIMARY KEY (`CONDITION_ID`),
      UNIQUE (`UUID`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS `AM_CERTIFICATE_METADATA` (
      `TENANT_ID` INT(11) NOT NULL,
      `ALIAS` VARCHAR(255) NOT NULL,
      `END_POINT` VARCHAR(255) NOT NULL,
      CONSTRAINT PK_ALIAS PRIMARY KEY (`ALIAS`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS `AM_API_CLIENT_CERTIFICATE` (
      `TENANT_ID` INT(11) NOT NULL,
      `ALIAS` VARCHAR(45) NOT NULL,
      `API_ID` INTEGER NOT NULL,
      `CERTIFICATE` BLOB NOT NULL,
      `REMOVED` BOOLEAN NOT NULL DEFAULT 0,
      `TIER_NAME` VARCHAR (512),
      FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE ON UPDATE CASCADE,
      PRIMARY KEY (`ALIAS`, `TENANT_ID`, `REMOVED`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_APPLICATION_GROUP_MAPPING (
      APPLICATION_ID INTEGER NOT NULL,
      GROUP_ID VARCHAR(512) NOT NULL,
      TENANT VARCHAR(255),
      PRIMARY KEY (APPLICATION_ID,GROUP_ID,TENANT),
      FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_USAGE_UPLOADED_FILES (
      TENANT_DOMAIN varchar(255) NOT NULL,
      FILE_NAME varchar(255) NOT NULL,
      FILE_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      FILE_PROCESSED tinyint(1) DEFAULT FALSE,
      FILE_CONTENT MEDIUMBLOB DEFAULT NULL,
      PRIMARY KEY (TENANT_DOMAIN, FILE_NAME, FILE_TIMESTAMP)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_API_LC_PUBLISH_EVENTS (
      ID INTEGER(11) NOT NULL AUTO_INCREMENT,
      TENANT_DOMAIN VARCHAR(500) NOT NULL,
      API_ID VARCHAR(500) NOT NULL,
      EVENT_TIME TIMESTAMP NOT NULL,
      PRIMARY KEY (ID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_APPLICATION_ATTRIBUTES (
      APPLICATION_ID int(11) NOT NULL,
      NAME varchar(255) NOT NULL,
      VALUE varchar(1024) NOT NULL,
      TENANT_ID int(11) NOT NULL,
      PRIMARY KEY (APPLICATION_ID,NAME),
      FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION (APPLICATION_ID) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_LABELS (
      LABEL_ID VARCHAR(50),
      NAME VARCHAR(255),
      DESCRIPTION VARCHAR(1024),
      TENANT_DOMAIN VARCHAR(255),
      UNIQUE (NAME,TENANT_DOMAIN),
      PRIMARY KEY (LABEL_ID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_LABEL_URLS (
      LABEL_ID VARCHAR(50),
      ACCESS_URL VARCHAR(255),
      PRIMARY KEY (LABEL_ID,ACCESS_URL),
      FOREIGN KEY (LABEL_ID) REFERENCES AM_LABELS(LABEL_ID) ON UPDATE CASCADE ON DELETE CASCADE
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_SYSTEM_APPS (
      ID INTEGER AUTO_INCREMENT,
      NAME VARCHAR(50) NOT NULL,
      CONSUMER_KEY VARCHAR(512) NOT NULL,
      CONSUMER_SECRET VARCHAR(512) NOT NULL,
      CREATED_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      TENANT_DOMAIN VARCHAR(255) DEFAULT 'carbon.super',
      UNIQUE (CONSUMER_KEY),
      PRIMARY KEY (ID)
   ) ENGINE=InnoDB;
  
  -- BotDATA Email table --
  CREATE TABLE IF NOT EXISTS AM_NOTIFICATION_SUBSCRIBER (
      UUID VARCHAR(255),
      CATEGORY VARCHAR(255),
      NOTIFICATION_METHOD VARCHAR(255),
      SUBSCRIBER_ADDRESS VARCHAR(255) NOT NULL,
      PRIMARY KEY(UUID, SUBSCRIBER_ADDRESS)
  ) ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS AM_REVOKED_JWT (
      UUID VARCHAR(255) NOT NULL,
      SIGNATURE VARCHAR(2048) NOT NULL,
      EXPIRY_TIMESTAMP BIGINT NOT NULL,
      TENANT_ID INTEGER DEFAULT -1,
      TOKEN_TYPE VARCHAR(15) DEFAULT 'DEFAULT',
      TIME_CREATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (UUID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_API_CATEGORIES (
      UUID VARCHAR(50),
      NAME VARCHAR(255),
      DESCRIPTION VARCHAR(1024),
      TENANT_ID INTEGER DEFAULT -1,
      UNIQUE (NAME,TENANT_ID),
      PRIMARY KEY (UUID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS AM_USER (
      USER_ID VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(255) NOT NULL,
      PRIMARY KEY(USER_ID)
  ) ENGINE=InnoDB;
  -- End of API-MGT Tables --
  
  -- UMA tables --
  CREATE TABLE IF NOT EXISTS IDN_UMA_RESOURCE (
      ID INTEGER AUTO_INCREMENT NOT NULL,
      RESOURCE_ID VARCHAR(255),
      RESOURCE_NAME VARCHAR(255),
      TIME_CREATED TIMESTAMP NOT NULL,
      RESOURCE_OWNER_NAME VARCHAR(255),
      CLIENT_ID VARCHAR(255),
      TENANT_ID INTEGER DEFAULT -1234,
      USER_DOMAIN VARCHAR(50),
      PRIMARY KEY (ID)
  );
  
  CREATE INDEX IDX_RID ON IDN_UMA_RESOURCE (RESOURCE_ID);
  
  CREATE INDEX IDX_USER ON IDN_UMA_RESOURCE (RESOURCE_OWNER_NAME, USER_DOMAIN);
  
  CREATE TABLE IF NOT EXISTS IDN_UMA_RESOURCE_META_DATA (
      ID INTEGER AUTO_INCREMENT NOT NULL,
      RESOURCE_IDENTITY INTEGER NOT NULL,
      PROPERTY_KEY VARCHAR(40),
      PROPERTY_VALUE VARCHAR(255),
      PRIMARY KEY (ID),
      FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE IF NOT EXISTS IDN_UMA_RESOURCE_SCOPE (
      ID INTEGER AUTO_INCREMENT NOT NULL,
      RESOURCE_IDENTITY INTEGER NOT NULL,
      SCOPE_NAME VARCHAR(255),
      PRIMARY KEY (ID),
      FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
  );
  
  CREATE INDEX IDX_RS ON IDN_UMA_RESOURCE_SCOPE (SCOPE_NAME);
  
  CREATE TABLE IF NOT EXISTS IDN_UMA_PERMISSION_TICKET (
      ID INTEGER AUTO_INCREMENT NOT NULL,
      PT VARCHAR(255) NOT NULL,
      TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      EXPIRY_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      TICKET_STATE VARCHAR(25) DEFAULT 'ACTIVE',
      TENANT_ID INTEGER DEFAULT -1234,
      PRIMARY KEY (ID)
  );
  
  CREATE INDEX IDX_PT ON IDN_UMA_PERMISSION_TICKET (PT);
  
  CREATE TABLE IF NOT EXISTS IDN_UMA_PT_RESOURCE (
      ID INTEGER AUTO_INCREMENT NOT NULL,
      PT_RESOURCE_ID INTEGER NOT NULL,
      PT_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (PT_ID) REFERENCES IDN_UMA_PERMISSION_TICKET (ID) ON DELETE CASCADE,
      FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE IF NOT EXISTS IDN_UMA_PT_RESOURCE_SCOPE (
      ID INTEGER AUTO_INCREMENT NOT NULL,
      PT_RESOURCE_ID INTEGER NOT NULL,
      PT_SCOPE_ID INTEGER NOT NULL,
      PRIMARY KEY (ID),
      FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_PT_RESOURCE (ID) ON DELETE CASCADE,
      FOREIGN KEY (PT_SCOPE_ID) REFERENCES IDN_UMA_RESOURCE_SCOPE (ID) ON DELETE CASCADE
  );
  
  CREATE TABLE IF NOT EXISTS AM_USER (
      USER_ID VARCHAR(255) NOT NULL,
      USER_NAME VARCHAR(255) NOT NULL,
      PRIMARY KEY(USER_ID)
  );
  
  -- Performance indexes start--
  
  create index IDX_ITS_LMT on IDN_THRIFT_SESSION (LAST_MODIFIED_TIME);
  create index IDX_IOAT_UT on IDN_OAUTH2_ACCESS_TOKEN (USER_TYPE);
  create index IDX_AAI_CTX on AM_API (CONTEXT);
  create index IDX_AAKM_CK on AM_APPLICATION_KEY_MAPPING (CONSUMER_KEY);
  create index IDX_AAUM_AI on AM_API_URL_MAPPING (API_ID);
  create index IDX_AAPM_AI on AM_API_PRODUCT_MAPPING (API_ID);
  create index IDX_AAUM_TT on AM_API_URL_MAPPING (THROTTLING_TIER);
  create index IDX_AATP_DQT on AM_API_THROTTLE_POLICY (DEFAULT_QUOTA_TYPE);
  create index IDX_ACG_QT on AM_CONDITION_GROUP (QUOTA_TYPE);
  create index IDX_APS_QT on AM_POLICY_SUBSCRIPTION (QUOTA_TYPE);
  create index IDX_AS_AITIAI on AM_SUBSCRIPTION (API_ID,TIER_ID,APPLICATION_ID);
  create index IDX_APA_QT on AM_POLICY_APPLICATION (QUOTA_TYPE);
  create index IDX_AA_AT_CB on AM_APPLICATION (APPLICATION_TIER,CREATED_BY);
  
  -- Performance indexes end--
  
  /*
  Create tables for openbank_openbankingdb
  */
  
  USE openbank_openbankingdb;
  
  CREATE TABLE UK_TRANSACTION_INITIATION (
      ID        VARCHAR(255)  NOT NULL,
      REQUEST   JSON NOT NULL,
      TIMESTAMP DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATUS    VARCHAR(64)   NOT NULL DEFAULT 0,
      CLIENT_ID VARCHAR(255)  NOT NULL,
      IDEMPOTENT_KEY VARCHAR(64) NOT NULL ,
      STATUS_UPDATED_TIMESTAMP DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      SPEC_VERSION VARCHAR(64)   NOT NULL,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE UK_ACCOUNT_INITIATION (
      ID        VARCHAR(255)  NOT NULL,
      REQUEST   JSON NOT NULL,
      CREATED_TIMESTAMP DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATUS    VARCHAR(64)   NOT NULL DEFAULT 0,
      CLIENT_ID VARCHAR(255)  NOT NULL,
      STATUS_UPDATED_TIMESTAMP DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
      SPEC_VERSION VARCHAR(64) NOT NULL,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE UK_COF_INITIATION (
      ID        VARCHAR(255)  NOT NULL,
      REQUEST   JSON NOT NULL,
      CREATED_TIMESTAMP DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATUS    VARCHAR(64)   NOT NULL DEFAULT 0,
      CLIENT_ID VARCHAR(255)  NOT NULL,
      STATUS_UPDATED_TIMESTAMP DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
      SPEC_VERSION VARCHAR(64)   NOT NULL,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE UK_TRANSACTION_CONSENT_BINDING (
      USER_ID           VARCHAR(255) NOT NULL,
      CONSENT_ID        VARCHAR(255) NOT NULL,
      DEBTOR_ACCOUNT    VARCHAR(255) NOT NULL,
      TIMESTAMP         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
      COLLECTION_METHOD VARCHAR(64),
      PRIMARY KEY (USER_ID, CONSENT_ID),
      CONSTRAINT FK_CONSENT_ID_UK_TRANSACTION_CONSENT_BINDING FOREIGN KEY (CONSENT_ID) REFERENCES UK_TRANSACTION_INITIATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UK_TRANSACTION_FILE (
      CONSENT_ID        VARCHAR(255) NOT NULL,
      CLIENT_ID         VARCHAR(255) NOT NULL,
      PAYMENT_FILE      MEDIUMBLOB   NOT NULL,
      IDEMPOTENT_KEY    VARCHAR(64)  NOT NULL,
      TIMESTAMP         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (CONSENT_ID),
      CONSTRAINT FK_CONSENT_ID_UK_TRANSACTION_FILE FOREIGN KEY (CONSENT_ID) REFERENCES UK_TRANSACTION_INITIATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UK_ACCOUNT_CONSENT_BINDING (
      USER_ID           VARCHAR(255) NOT NULL,
      CONSENT_ID        VARCHAR(255) NOT NULL,
      ACCOUNT_ID        VARCHAR(255) NOT NULL,
      TIMESTAMP         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
      COLLECTION_METHOD VARCHAR(64),
      PRIMARY KEY (USER_ID, CONSENT_ID,ACCOUNT_ID),
      CONSTRAINT FK_CONSENT_ID_UK_ACCOUNT_CONSENT_BINDING FOREIGN KEY (CONSENT_ID) REFERENCES UK_ACCOUNT_INITIATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UK_COF_CONSENT_BINDING (
      USER_ID           VARCHAR(255) NOT NULL,
      CONSENT_ID        VARCHAR(255) NOT NULL,
      ACCOUNT_ID        VARCHAR(255) NOT NULL,
      TIMESTAMP         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
      COLLECTION_METHOD VARCHAR(64),
      PRIMARY KEY (USER_ID, CONSENT_ID, ACCOUNT_ID),
      CONSTRAINT FK_CONSENT_ID_UK_COF_CONSENT_BINDING FOREIGN KEY (CONSENT_ID) REFERENCES UK_COF_INITIATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UK_ACCOUNT_CONSENT_REV_HISTORY (
      CONSENT_ID VARCHAR(255) NOT NULL,
      REVOCATION_USER VARCHAR(255) NOT NULL,
      REVOCATION_REASON VARCHAR(2048) NOT NULL,
      PRIMARY KEY (CONSENT_ID),
      CONSTRAINT FK_CONSENT_ID_UK_ACCOUNT_CONSENT_REVOCATION_HISTORY FOREIGN KEY (CONSENT_ID) REFERENCES UK_ACCOUNT_INITIATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UK_COF_CONSENT_REV_HISTORY (
      CONSENT_ID VARCHAR(255) NOT NULL,
      REVOCATION_USER VARCHAR(255) NOT NULL,
      REVOCATION_REASON VARCHAR(2048) NOT NULL,
      PRIMARY KEY (CONSENT_ID),
      CONSTRAINT FK_CONSENT_ID_UK_FUNDS_CONFIRMATION_CONSENT_REVOCATION_HISTORY FOREIGN KEY (CONSENT_ID) REFERENCES UK_COF_INITIATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS OB_MULTI_AUTHORIZATION (
      ID INTEGER AUTO_INCREMENT,
      CONSENT_ID VARCHAR(255) NOT NULL,
      CLIENT_ID  VARCHAR(255) NOT NULL,
      SELECTED_ACCOUNT  VARCHAR(255) NOT NULL,
      AUTHORIZATION_EXPIRY_DATE TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATUS VARCHAR(255) NOT NULL,
      NUMBER_OF_TOTAL_AUTHORIZATIONS INTEGER DEFAULT 0,
      CREATED_TIMESTAMP  TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATUS_UPDATED_TIMESTAMP  TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS OB_MULTI_AUTHORIZATION_USER (
      ID  INTEGER DEFAULT 0,
      USER_ID VARCHAR(255) NOT NULL,
      STATUS  VARCHAR(255) NOT NULL,
      CREATED_TIMESTAMP TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (ID,USER_ID),
      CONSTRAINT FK_ID_OB_MULTI_AUTHORIZATION_USER FOREIGN KEY (ID) REFERENCES OB_MULTI_AUTHORIZATION (ID)
      ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UK_CONSENT_REV (
      ID INTEGER AUTO_INCREMENT,
      USER_ID           VARCHAR(255) NOT NULL,
      CONSENT_ID        VARCHAR(255) NOT NULL,
      ACCOUNT_ID        VARCHAR(255) NOT NULL,
      TIMESTAMP         TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
      COLLECTION_METHOD VARCHAR(64),
      PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS OB_CONSENT_METADATA (
      CONSENT_ID VARCHAR(255) NOT NULL,
      CONSENT_TYPE VARCHAR(255) NOT NULL,
      METADATA_KEY VARCHAR(255) NOT NULL,
      METADATA_VALUE VARCHAR(255) NOT NULL,
      PRIMARY KEY(CONSENT_ID, CONSENT_TYPE, METADATA_KEY)
  )ENGINE INNODB;
  
   CREATE TABLE IF NOT EXISTS UK_NOTIFICATION_SUBSCRIPTION (
      SUBSCRIPTION_ID int(10) NOT NULL AUTO_INCREMENT,
      CLIENT_ID varchar(50) NOT NULL,
      REQUEST varchar(1000) NOT NULL,
      CALLBACK_URL varchar(100),
      TIMESTAMP timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      SPEC_VERSION varchar(10) NOT NULL,
      STATUS varchar(10) NOT NULL,
      PRIMARY KEY(SUBSCRIPTION_ID)
   ) ENGINE=InnoDB;
  
   CREATE TABLE IF NOT EXISTS UK_NOTIFICATION_SUBS_EVENTS (
      SUBSCRIPTION_ID int(10) NOT NULL,
      EVENT_TYPE varchar(100) NOT NULL,
      PRIMARY KEY(SUBSCRIPTION_ID, EVENT_TYPE),
      FOREIGN KEY (SUBSCRIPTION_ID) REFERENCES UK_NOTIFICATION_SUBSCRIPTION(SUBSCRIPTION_ID)
   ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS UK_NOTIFICATION (
      NOTIFICATION_ID varchar(36) NOT NULL,
      CLIENT_ID varchar(50) NOT NULL,
      RESOURCE_ID varchar(50) NOT NULL,
      RESOURCE_TYPE varchar(50) NOT NULL,
      VERSION varchar(10) NOT NULL,
      STATUS varchar(10) NOT NULL,
      UPDATED_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (NOTIFICATION_ID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS UK_NOTIFICATION_EVENT (
      ID int(11) NOT NULL AUTO_INCREMENT,
      NOTIFICATION_ID varchar(36) NOT NULL,
      EVENT_TYPE varchar(200) NOT NULL,
      EVENT varchar(1000) NULL DEFAULT NULL,
      PRIMARY KEY (ID),
      CONSTRAINT FK_NotificationEvent FOREIGN KEY (NOTIFICATION_ID) REFERENCES UK_NOTIFICATION(NOTIFICATION_ID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE IF NOT EXISTS UK_NOTIFICATION_ERROR (
      NOTIFICATION_ID varchar(36) NOT NULL,
      ERROR_CODE varchar(10) NOT NULL,
      DESCRIPTION varchar(100) NOT NULL,
      PRIMARY KEY (NOTIFICATION_ID),
      CONSTRAINT FK_NotificationError FOREIGN KEY (NOTIFICATION_ID) REFERENCES UK_NOTIFICATION(NOTIFICATION_ID)
  ) ENGINE=InnoDB;
  
  CREATE TABLE  IF NOT EXISTS OB_DCR_DETAILS (
      ID VARCHAR(255) NOT NULL,
      TPP_NAME VARCHAR(255) NOT NULL,
      REQUEST_HEADER JSON NOT NULL,
      REQUEST JSON NOT NULL,
      CLIENT_ID VARCHAR(255) NOT NULL,
      SOFTWARE_ENV VARCHAR(255) NOT NULL,
      APPLICATION_ID INT NOT NULL,
      SOFTWARE_ID VARCHAR(255) NOT NULL,
      SPEC_VERSION VARCHAR(64) NOT NULL,
      CREATED_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      STATUS VARCHAR(64) NOT NULL,
      CLID_ISSUED_AT INT NOT NULL,
      STATUS_UPDATED_TIMESTAMP TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`ID`)
  )ENGINE INNODB;
  
  /*
  Create tables for openbank_govdb
  */
  
  USE openbank_govdb;
  
  CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
      REG_LOCK_NAME VARCHAR (20),
      REG_LOCK_STATUS VARCHAR (20),
      REG_LOCKED_TIME TIMESTAMP,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOCK_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_LOG (
      REG_LOG_ID INTEGER AUTO_INCREMENT,
      REG_PATH VARCHAR (750),
      REG_USER_ID VARCHAR (31) NOT NULL,
      REG_LOGGED_TIME TIMESTAMP NOT NULL,
      REG_ACTION INTEGER NOT NULL,
      REG_ACTION_DATA VARCHAR (500),
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
  
  -- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
  -- See CARBON-5917.
  
  CREATE TABLE IF NOT EXISTS REG_PATH(
      REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_VALUE VARCHAR(750) NOT NULL,
      REG_PATH_PARENT_ID INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
      CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT (
      REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_CONTENT_DATA LONGBLOB,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
      REG_CONTENT_ID INTEGER NOT NULL,
      REG_CONTENT_DATA LONGBLOB,
      REG_DELETED   SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_DELETED         SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_COMMENT (
      REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
      REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
      REG_USER_ID           VARCHAR(31) NOT NULL,
      REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
      REG_COMMENT_ID          INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_RATING (
      REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_RATING        INTEGER NOT NULL,
      REG_USER_ID       VARCHAR(31) NOT NULL,
      REG_RATED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
      REG_RATING_ID           INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
  
  
  CREATE TABLE IF NOT EXISTS REG_TAG (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_TAG_NAME       VARCHAR(500) NOT NULL,
      REG_USER_ID        VARCHAR(31) NOT NULL,
      REG_TAGGED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
      REG_TAG_ID              INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_PROPERTY (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_NAME       VARCHAR(100) NOT NULL,
      REG_VALUE        VARCHAR(1000),
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
      REG_PROPERTY_ID         INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
  
  -- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
  -- SRC_PATH_ID     INTEGER,
  -- SRC_RESOURCE_NAME    VARCHAR(256),
  -- SRC_VERSION     INTEGER,
  -- TGT_PATH_ID     INTEGER,
  -- TGT_RESOURCE_NAME    VARCHAR(256),
  -- TGT_VERSION     INTEGER
  -- )ENGINE INNODB;
  --
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
  
  
  
  CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
      REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
      REG_SOURCEPATH VARCHAR (750) NOT NULL,
      REG_TARGETPATH VARCHAR (750) NOT NULL,
      REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
      REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_ID            INTEGER NOT NULL,
      REG_RESOURCE_NAME      VARCHAR(255),
      REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  ALTER TABLE REG_SNAPSHOT
      ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  
  
  -- ################################
  -- USER MANAGER TABLES
  -- ################################
  
  CREATE TABLE UM_TENANT (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_EMAIL VARCHAR(255),
      UM_ACTIVE BOOLEAN DEFAULT FALSE,
      UM_CREATED_DATE TIMESTAMP NOT NULL,
      UM_USER_CONFIG LONGBLOB,
      PRIMARY KEY (UM_ID),
      UNIQUE(UM_DOMAIN_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE UM_DOMAIN(
      UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
      UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
                      ON UM_TENANT (UM_DOMAIN_NAME);
  
  CREATE TABLE UM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_MODULE(
      UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
      UM_MODULE_NAME VARCHAR(100),
      UNIQUE(UM_MODULE_NAME),
      PRIMARY KEY(UM_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_MODULE_ACTIONS(
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_MODULE_ID INTEGER NOT NULL,
      PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
      FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_RESOURCE_ID VARCHAR(255) NOT NULL,
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_MODULE_ID INTEGER DEFAULT 0,
      UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
  
  CREATE TABLE UM_ROLE_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
  CREATE TABLE UM_USER_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
  CREATE TABLE UM_USER_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SHARED_USER_ROLE(
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_USER_TENANT_ID INTEGER NOT NULL,
      UM_ROLE_TENANT_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
      FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_ACCOUNT_MAPPING(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER NOT NULL,
      UM_USER_STORE_DOMAIN VARCHAR(100),
      UM_ACC_LINK_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
      FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_USER_ATTRIBUTE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ATTR_NAME VARCHAR(255) NOT NULL,
      UM_ATTR_VALUE VARCHAR(1024),
      UM_PROFILE_ID VARCHAR(255),
      UM_USER_ID INTEGER,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
  
  CREATE TABLE UM_DIALECT(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_URI VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_CLAIM(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_CLAIM_URI VARCHAR(255) NOT NULL,
      UM_DISPLAY_TAG VARCHAR(255),
      UM_DESCRIPTION VARCHAR(255),
      UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
      UM_MAPPED_ATTRIBUTE VARCHAR(255),
      UM_REG_EX VARCHAR(255),
      UM_SUPPORTED SMALLINT,
      UM_REQUIRED SMALLINT,
      UM_DISPLAY_ORDER INTEGER,
      UM_CHECKED_ATTRIBUTE SMALLINT,
      UM_READ_ONLY SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_PROFILE_CONFIG(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_PROFILE_NAME VARCHAR(255),
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PROFILE_ID INTEGER,
      UM_CLAIM_ID INTEGER,
      UM_BEHAVIOUR SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
      FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
      PRIMARY KEY(UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_HYBRID_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
  
  CREATE TABLE UM_HYBRID_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_HYBRID_REMEMBER_ME(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_COOKIE_VALUE VARCHAR(1024),
      UM_CREATED_TIME TIMESTAMP,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_UUID_DOMAIN_MAPPER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_DOMAIN_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID),
      UNIQUE (UM_USER_ID),
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE INDEX UUID_DM_UID_TID ON UM_UUID_DOMAIN_MAPPER(UM_USER_ID, UM_TENANT_ID);
  
  /*
  Create tables for openbank_iskm_configdb
  */
  
  USE openbank_iskm_configdb;
  
  CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
      REG_LOCK_NAME VARCHAR (20),
      REG_LOCK_STATUS VARCHAR (20),
      REG_LOCKED_TIME TIMESTAMP,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOCK_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_LOG (
      REG_LOG_ID INTEGER AUTO_INCREMENT,
      REG_PATH VARCHAR (750),
      REG_USER_ID VARCHAR (31) NOT NULL,
      REG_LOGGED_TIME TIMESTAMP NOT NULL,
      REG_ACTION INTEGER NOT NULL,
      REG_ACTION_DATA VARCHAR (500),
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
  
  -- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
  -- See CARBON-5917.
  
  CREATE TABLE IF NOT EXISTS REG_PATH(
      REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_VALUE VARCHAR(750) NOT NULL,
      REG_PATH_PARENT_ID INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
      CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT (
      REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_CONTENT_DATA LONGBLOB,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
      REG_CONTENT_ID INTEGER NOT NULL,
      REG_CONTENT_DATA LONGBLOB,
      REG_DELETED   SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_DELETED         SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_COMMENT (
      REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
      REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
      REG_USER_ID           VARCHAR(31) NOT NULL,
      REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
      REG_COMMENT_ID          INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_RATING (
      REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_RATING        INTEGER NOT NULL,
      REG_USER_ID       VARCHAR(31) NOT NULL,
      REG_RATED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
      REG_RATING_ID           INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
  
  
  CREATE TABLE IF NOT EXISTS REG_TAG (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_TAG_NAME       VARCHAR(500) NOT NULL,
      REG_USER_ID        VARCHAR(31) NOT NULL,
      REG_TAGGED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
      REG_TAG_ID              INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_PROPERTY (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_NAME       VARCHAR(100) NOT NULL,
      REG_VALUE        VARCHAR(1000),
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
      REG_PROPERTY_ID         INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
  
  -- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
  -- SRC_PATH_ID     INTEGER,
  -- SRC_RESOURCE_NAME    VARCHAR(256),
  -- SRC_VERSION     INTEGER,
  -- TGT_PATH_ID     INTEGER,
  -- TGT_RESOURCE_NAME    VARCHAR(256),
  -- TGT_VERSION     INTEGER
  -- )ENGINE INNODB;
  --
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
  
  
  
  CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
      REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
      REG_SOURCEPATH VARCHAR (750) NOT NULL,
      REG_TARGETPATH VARCHAR (750) NOT NULL,
      REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
      REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_ID            INTEGER NOT NULL,
      REG_RESOURCE_NAME      VARCHAR(255),
      REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  ALTER TABLE REG_SNAPSHOT
      ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  
  
  -- ################################
  -- USER MANAGER TABLES
  -- ################################
  
  CREATE TABLE UM_TENANT (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_EMAIL VARCHAR(255),
      UM_ACTIVE BOOLEAN DEFAULT FALSE,
      UM_CREATED_DATE TIMESTAMP NOT NULL,
      UM_USER_CONFIG LONGBLOB,
      PRIMARY KEY (UM_ID),
      UNIQUE(UM_DOMAIN_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE UM_DOMAIN(
      UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
      UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
                      ON UM_TENANT (UM_DOMAIN_NAME);
  
  CREATE TABLE UM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_MODULE(
      UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
      UM_MODULE_NAME VARCHAR(100),
      UNIQUE(UM_MODULE_NAME),
      PRIMARY KEY(UM_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_MODULE_ACTIONS(
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_MODULE_ID INTEGER NOT NULL,
      PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
      FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_RESOURCE_ID VARCHAR(255) NOT NULL,
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_MODULE_ID INTEGER DEFAULT 0,
      UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
  
  CREATE TABLE UM_ROLE_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
  CREATE TABLE UM_USER_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
  CREATE TABLE UM_USER_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SHARED_USER_ROLE(
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_USER_TENANT_ID INTEGER NOT NULL,
      UM_ROLE_TENANT_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
      FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_ACCOUNT_MAPPING(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER NOT NULL,
      UM_USER_STORE_DOMAIN VARCHAR(100),
      UM_ACC_LINK_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
      FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_USER_ATTRIBUTE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ATTR_NAME VARCHAR(255) NOT NULL,
      UM_ATTR_VALUE VARCHAR(1024),
      UM_PROFILE_ID VARCHAR(255),
      UM_USER_ID INTEGER,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
  
  CREATE TABLE UM_DIALECT(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_URI VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_CLAIM(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_CLAIM_URI VARCHAR(255) NOT NULL,
      UM_DISPLAY_TAG VARCHAR(255),
      UM_DESCRIPTION VARCHAR(255),
      UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
      UM_MAPPED_ATTRIBUTE VARCHAR(255),
      UM_REG_EX VARCHAR(255),
      UM_SUPPORTED SMALLINT,
      UM_REQUIRED SMALLINT,
      UM_DISPLAY_ORDER INTEGER,
      UM_CHECKED_ATTRIBUTE SMALLINT,
      UM_READ_ONLY SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_PROFILE_CONFIG(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_PROFILE_NAME VARCHAR(255),
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PROFILE_ID INTEGER,
      UM_CLAIM_ID INTEGER,
      UM_BEHAVIOUR SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
      FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
      PRIMARY KEY(UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_HYBRID_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
  
  CREATE TABLE UM_HYBRID_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_HYBRID_REMEMBER_ME(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_COOKIE_VALUE VARCHAR(1024),
      UM_CREATED_TIME TIMESTAMP,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_UUID_DOMAIN_MAPPER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_DOMAIN_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID),
      UNIQUE (UM_USER_ID),
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE INDEX UUID_DM_UID_TID ON UM_UUID_DOMAIN_MAPPER(UM_USER_ID, UM_TENANT_ID);
  
  /*
  Create tables for openbank_userdb
  */
  
  USE openbank_userdb;
  
  CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
      REG_LOCK_NAME VARCHAR (20),
      REG_LOCK_STATUS VARCHAR (20),
      REG_LOCKED_TIME TIMESTAMP,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOCK_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_LOG (
      REG_LOG_ID INTEGER AUTO_INCREMENT,
      REG_PATH VARCHAR (750),
      REG_USER_ID VARCHAR (31) NOT NULL,
      REG_LOGGED_TIME TIMESTAMP NOT NULL,
      REG_ACTION INTEGER NOT NULL,
      REG_ACTION_DATA VARCHAR (500),
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
  
  -- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
  -- See CARBON-5917.
  
  CREATE TABLE IF NOT EXISTS REG_PATH(
      REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_VALUE VARCHAR(750) NOT NULL,
      REG_PATH_PARENT_ID INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
      CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT (
      REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_CONTENT_DATA LONGBLOB,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
      REG_CONTENT_ID INTEGER NOT NULL,
      REG_CONTENT_DATA LONGBLOB,
      REG_DELETED   SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_DELETED         SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_COMMENT (
      REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
      REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
      REG_USER_ID           VARCHAR(31) NOT NULL,
      REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
      REG_COMMENT_ID          INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_RATING (
      REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_RATING        INTEGER NOT NULL,
      REG_USER_ID       VARCHAR(31) NOT NULL,
      REG_RATED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
      REG_RATING_ID           INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
  
  
  CREATE TABLE IF NOT EXISTS REG_TAG (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_TAG_NAME       VARCHAR(500) NOT NULL,
      REG_USER_ID        VARCHAR(31) NOT NULL,
      REG_TAGGED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
      REG_TAG_ID              INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_PROPERTY (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_NAME       VARCHAR(100) NOT NULL,
      REG_VALUE        VARCHAR(1000),
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
      REG_PROPERTY_ID         INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
  
  -- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
  -- SRC_PATH_ID     INTEGER,
  -- SRC_RESOURCE_NAME    VARCHAR(256),
  -- SRC_VERSION     INTEGER,
  -- TGT_PATH_ID     INTEGER,
  -- TGT_RESOURCE_NAME    VARCHAR(256),
  -- TGT_VERSION     INTEGER
  -- )ENGINE INNODB;
  --
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
  
  
  
  CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
      REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
      REG_SOURCEPATH VARCHAR (750) NOT NULL,
      REG_TARGETPATH VARCHAR (750) NOT NULL,
      REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
      REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_ID            INTEGER NOT NULL,
      REG_RESOURCE_NAME      VARCHAR(255),
      REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  ALTER TABLE REG_SNAPSHOT
      ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  
  
  -- ################################
  -- USER MANAGER TABLES
  -- ################################
  
  CREATE TABLE UM_TENANT (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_EMAIL VARCHAR(255),
      UM_ACTIVE BOOLEAN DEFAULT FALSE,
      UM_CREATED_DATE TIMESTAMP NOT NULL,
      UM_USER_CONFIG LONGBLOB,
      PRIMARY KEY (UM_ID),
      UNIQUE(UM_DOMAIN_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE UM_DOMAIN(
      UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
      UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
                      ON UM_TENANT (UM_DOMAIN_NAME);
  
  CREATE TABLE UM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_MODULE(
      UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
      UM_MODULE_NAME VARCHAR(100),
      UNIQUE(UM_MODULE_NAME),
      PRIMARY KEY(UM_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_MODULE_ACTIONS(
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_MODULE_ID INTEGER NOT NULL,
      PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
      FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_RESOURCE_ID VARCHAR(255) NOT NULL,
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_MODULE_ID INTEGER DEFAULT 0,
      UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
  
  CREATE TABLE UM_ROLE_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
  CREATE TABLE UM_USER_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
  CREATE TABLE UM_USER_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SHARED_USER_ROLE(
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_USER_TENANT_ID INTEGER NOT NULL,
      UM_ROLE_TENANT_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
      FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_ACCOUNT_MAPPING(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER NOT NULL,
      UM_USER_STORE_DOMAIN VARCHAR(100),
      UM_ACC_LINK_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
      FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_USER_ATTRIBUTE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ATTR_NAME VARCHAR(255) NOT NULL,
      UM_ATTR_VALUE VARCHAR(1024),
      UM_PROFILE_ID VARCHAR(255),
      UM_USER_ID INTEGER,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
  
  CREATE TABLE UM_DIALECT(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_URI VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_CLAIM(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_CLAIM_URI VARCHAR(255) NOT NULL,
      UM_DISPLAY_TAG VARCHAR(255),
      UM_DESCRIPTION VARCHAR(255),
      UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
      UM_MAPPED_ATTRIBUTE VARCHAR(255),
      UM_REG_EX VARCHAR(255),
      UM_SUPPORTED SMALLINT,
      UM_REQUIRED SMALLINT,
      UM_DISPLAY_ORDER INTEGER,
      UM_CHECKED_ATTRIBUTE SMALLINT,
      UM_READ_ONLY SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_PROFILE_CONFIG(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_PROFILE_NAME VARCHAR(255),
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PROFILE_ID INTEGER,
      UM_CLAIM_ID INTEGER,
      UM_BEHAVIOUR SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
      FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
      PRIMARY KEY(UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_HYBRID_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
  
  CREATE TABLE UM_HYBRID_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_HYBRID_REMEMBER_ME(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_COOKIE_VALUE VARCHAR(1024),
      UM_CREATED_TIME TIMESTAMP,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_UUID_DOMAIN_MAPPER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_DOMAIN_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID),
      UNIQUE (UM_USER_ID),
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE INDEX UUID_DM_UID_TID ON UM_UUID_DOMAIN_MAPPER(UM_USER_ID, UM_TENANT_ID);
  
  /*
  Create tables for openbank_am_configdb
  */
  
  USE openbank_am_configdb;
  
  CREATE TABLE IF NOT EXISTS REG_CLUSTER_LOCK (
      REG_LOCK_NAME VARCHAR (20),
      REG_LOCK_STATUS VARCHAR (20),
      REG_LOCKED_TIME TIMESTAMP,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOCK_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_LOG (
      REG_LOG_ID INTEGER AUTO_INCREMENT,
      REG_PATH VARCHAR (750),
      REG_USER_ID VARCHAR (31) NOT NULL,
      REG_LOGGED_TIME TIMESTAMP NOT NULL,
      REG_ACTION INTEGER NOT NULL,
      REG_ACTION_DATA VARCHAR (500),
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_LOG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_LOG_IND_BY_REGLOG USING HASH ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
  
  -- The REG_PATH_VALUE should be less than 767 bytes, and hence was fixed at 750.
  -- See CARBON-5917.
  
  CREATE TABLE IF NOT EXISTS REG_PATH(
      REG_PATH_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_VALUE VARCHAR(750) NOT NULL,
      REG_PATH_PARENT_ID INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PATH PRIMARY KEY(REG_PATH_ID, REG_TENANT_ID),
      CONSTRAINT UNIQUE_REG_PATH_TENANT_ID UNIQUE (REG_PATH_VALUE,REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_PATH_IND_BY_PATH_PARENT_ID USING HASH ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT (
      REG_CONTENT_ID INTEGER NOT NULL AUTO_INCREMENT,
      REG_CONTENT_DATA LONGBLOB,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_CONTENT_HISTORY (
      REG_CONTENT_ID INTEGER NOT NULL,
      REG_CONTENT_DATA LONGBLOB,
      REG_DELETED   SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE
      ADD CONSTRAINT REG_RESOURCE_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_NAME USING HASH ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_IND_BY_UUID USING HASH ON REG_RESOURCE(REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TENAN USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
  CREATE INDEX REG_RESOURCE_IND_BY_TYPE USING HASH ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_HISTORY (
      REG_PATH_ID         INTEGER NOT NULL,
      REG_NAME            VARCHAR(256),
      REG_VERSION         INTEGER NOT NULL,
      REG_MEDIA_TYPE      VARCHAR(500),
      REG_CREATOR         VARCHAR(31) NOT NULL,
      REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_LAST_UPDATOR    VARCHAR(31),
      REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      REG_DESCRIPTION     VARCHAR(1000),
      REG_CONTENT_ID      INTEGER,
      REG_DELETED         SMALLINT,
      REG_TENANT_ID INTEGER DEFAULT 0,
      REG_UUID VARCHAR(100) NOT NULL,
      CONSTRAINT PK_REG_RESOURCE_HISTORY PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_PATHID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_HISTORY
      ADD CONSTRAINT REG_RESOURCE_HIST_FK_BY_CONTENT_ID FOREIGN KEY (REG_CONTENT_ID, REG_TENANT_ID) REFERENCES REG_CONTENT_HISTORY (REG_CONTENT_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME USING HASH ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME USING HASH ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_COMMENT (
      REG_ID        INTEGER NOT NULL AUTO_INCREMENT,
      REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
      REG_USER_ID           VARCHAR(31) NOT NULL,
      REG_COMMENTED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_COMMENT (
      REG_COMMENT_ID          INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_COMMENT
      ADD CONSTRAINT REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID FOREIGN KEY (REG_COMMENT_ID, REG_TENANT_ID) REFERENCES REG_COMMENT (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION USING HASH ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_RATING (
      REG_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_RATING        INTEGER NOT NULL,
      REG_USER_ID       VARCHAR(31) NOT NULL,
      REG_RATED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_RATING (
      REG_RATING_ID           INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_RATING
      ADD CONSTRAINT REG_RESOURCE_RATING_FK_BY_RATING_ID FOREIGN KEY (REG_RATING_ID, REG_TENANT_ID) REFERENCES REG_RATING (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION USING HASH ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
  
  
  CREATE TABLE IF NOT EXISTS REG_TAG (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_TAG_NAME       VARCHAR(500) NOT NULL,
      REG_USER_ID        VARCHAR(31) NOT NULL,
      REG_TAGGED_TIME    TIMESTAMP NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_TAG (
      REG_TAG_ID              INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_TAG
      ADD CONSTRAINT REG_RESOURCE_TAG_FK_BY_TAG_ID FOREIGN KEY (REG_TAG_ID, REG_TENANT_ID) REFERENCES REG_TAG (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION USING HASH ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
  
  CREATE TABLE IF NOT EXISTS REG_PROPERTY (
      REG_ID         INTEGER NOT NULL AUTO_INCREMENT,
      REG_NAME       VARCHAR(100) NOT NULL,
      REG_VALUE        VARCHAR(1000),
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_PROPERTY PRIMARY KEY(REG_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_RESOURCE_PROPERTY (
      REG_PROPERTY_ID         INTEGER NOT NULL,
      REG_VERSION             INTEGER,
      REG_PATH_ID             INTEGER,
      REG_RESOURCE_NAME       VARCHAR(256),
      REG_TENANT_ID INTEGER DEFAULT 0
  )ENGINE INNODB;
  
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  ALTER TABLE REG_RESOURCE_PROPERTY
      ADD CONSTRAINT REG_RESOURCE_PROPERTY_FK_BY_TAG_ID FOREIGN KEY (REG_PROPERTY_ID, REG_TENANT_ID) REFERENCES REG_PROPERTY (REG_ID, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION USING HASH ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
  
  -- CREATE TABLE IF NOT EXISTS REG_ASSOCIATIONS (
  -- SRC_PATH_ID     INTEGER,
  -- SRC_RESOURCE_NAME    VARCHAR(256),
  -- SRC_VERSION     INTEGER,
  -- TGT_PATH_ID     INTEGER,
  -- TGT_RESOURCE_NAME    VARCHAR(256),
  -- TGT_VERSION     INTEGER
  -- )ENGINE INNODB;
  --
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_SRC_PATH_ID FOREIGN KEY (SRC_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- ALTER TABLE REG_ASSOCIATIONS ADD CONSTRAINT REG_ASSOCIATIONS_FK_BY_TGT_PATH_ID FOREIGN KEY (TGT_PATH_ID) REFERENCES REG_PATH (PATH_ID);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_VERSION ON REG_ASSOCIATIONS(SRC_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_VERSION ON REG_ASSOCIATIONS(TGT_VERSION);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_SRC_RESOURCE_NAME ON REG_ASSOCIATIONS(SRC_RESOURCE_NAME);
  -- CREATE INDEX REG_ASSOCIATIONS_IND_BY_TGT_RESOURCE_NAME ON REG_ASSOCIATIONS(TGT_RESOURCE_NAME);
  
  
  
  CREATE TABLE IF NOT EXISTS REG_ASSOCIATION (
      REG_ASSOCIATION_ID INTEGER AUTO_INCREMENT,
      REG_SOURCEPATH VARCHAR (750) NOT NULL,
      REG_TARGETPATH VARCHAR (750) NOT NULL,
      REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS REG_SNAPSHOT (
      REG_SNAPSHOT_ID     INTEGER NOT NULL AUTO_INCREMENT,
      REG_PATH_ID            INTEGER NOT NULL,
      REG_RESOURCE_NAME      VARCHAR(255),
      REG_RESOURCE_VIDS     LONGBLOB NOT NULL,
      REG_TENANT_ID INTEGER DEFAULT 0,
      CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME USING HASH ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  ALTER TABLE REG_SNAPSHOT
      ADD CONSTRAINT REG_SNAPSHOT_FK_BY_PATH_ID FOREIGN KEY (REG_PATH_ID, REG_TENANT_ID) REFERENCES REG_PATH (REG_PATH_ID, REG_TENANT_ID);
  
  
  -- ################################
  -- USER MANAGER TABLES
  -- ################################
  
  CREATE TABLE UM_TENANT (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_EMAIL VARCHAR(255),
      UM_ACTIVE BOOLEAN DEFAULT FALSE,
      UM_CREATED_DATE TIMESTAMP NOT NULL,
      UM_USER_CONFIG LONGBLOB,
      PRIMARY KEY (UM_ID),
      UNIQUE(UM_DOMAIN_NAME)
  )ENGINE INNODB;
  
  CREATE TABLE UM_DOMAIN(
      UM_DOMAIN_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID),
      UNIQUE(UM_DOMAIN_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE UNIQUE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME
      ON UM_TENANT (UM_DOMAIN_NAME);
  
  CREATE TABLE UM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_USER_PASSWORD VARCHAR(255) NOT NULL,
      UM_SALT_VALUE VARCHAR(31),
      UM_REQUIRE_CHANGE BOOLEAN DEFAULT FALSE,
      UM_CHANGED_TIME TIMESTAMP NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_USER_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_SHARED_ROLE BOOLEAN DEFAULT FALSE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_MODULE(
      UM_ID INTEGER  NOT NULL AUTO_INCREMENT,
      UM_MODULE_NAME VARCHAR(100),
      UNIQUE(UM_MODULE_NAME),
      PRIMARY KEY(UM_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_MODULE_ACTIONS(
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_MODULE_ID INTEGER NOT NULL,
      PRIMARY KEY(UM_ACTION, UM_MODULE_ID),
      FOREIGN KEY (UM_MODULE_ID) REFERENCES UM_MODULE(UM_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_RESOURCE_ID VARCHAR(255) NOT NULL,
      UM_ACTION VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_MODULE_ID INTEGER DEFAULT 0,
      UNIQUE(UM_RESOURCE_ID,UM_ACTION, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION ON UM_PERMISSION (UM_RESOURCE_ID, UM_ACTION, UM_TENANT_ID);
  
  CREATE TABLE UM_ROLE_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_PERMISSION_ID, UM_ROLE_NAME, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_ROLE_ID)
  CREATE TABLE UM_USER_PERMISSION (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PERMISSION_ID INTEGER NOT NULL,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_IS_ALLOWED SMALLINT NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_PERMISSION_ID, UM_TENANT_ID) REFERENCES UM_PERMISSION(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  -- REMOVED UNIQUE (UM_PERMISSION_ID, UM_USER_ID)
  CREATE TABLE UM_USER_ROLE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_ID, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_ROLE(UM_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SHARED_USER_ROLE(
      UM_ROLE_ID INTEGER NOT NULL,
      UM_USER_ID INTEGER NOT NULL,
      UM_USER_TENANT_ID INTEGER NOT NULL,
      UM_ROLE_TENANT_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_ID,UM_ROLE_ID,UM_USER_TENANT_ID, UM_ROLE_TENANT_ID),
      FOREIGN KEY(UM_ROLE_ID,UM_ROLE_TENANT_ID) REFERENCES UM_ROLE(UM_ID,UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY(UM_USER_ID,UM_USER_TENANT_ID) REFERENCES UM_USER(UM_ID,UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE TABLE UM_ACCOUNT_MAPPING(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER NOT NULL,
      UM_USER_STORE_DOMAIN VARCHAR(100),
      UM_ACC_LINK_ID INTEGER NOT NULL,
      UNIQUE(UM_USER_NAME, UM_TENANT_ID, UM_USER_STORE_DOMAIN, UM_ACC_LINK_ID),
      FOREIGN KEY (UM_TENANT_ID) REFERENCES UM_TENANT(UM_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_USER_ATTRIBUTE (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ATTR_NAME VARCHAR(255) NOT NULL,
      UM_ATTR_VALUE VARCHAR(1024),
      UM_PROFILE_ID VARCHAR(255),
      UM_USER_ID INTEGER,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY (UM_USER_ID, UM_TENANT_ID) REFERENCES UM_USER(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
  
  CREATE TABLE UM_DIALECT(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_URI VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_CLAIM(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_CLAIM_URI VARCHAR(255) NOT NULL,
      UM_DISPLAY_TAG VARCHAR(255),
      UM_DESCRIPTION VARCHAR(255),
      UM_MAPPED_ATTRIBUTE_DOMAIN VARCHAR(255),
      UM_MAPPED_ATTRIBUTE VARCHAR(255),
      UM_REG_EX VARCHAR(255),
      UM_SUPPORTED SMALLINT,
      UM_REQUIRED SMALLINT,
      UM_DISPLAY_ORDER INTEGER,
      UM_CHECKED_ATTRIBUTE SMALLINT,
      UM_READ_ONLY SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE(UM_DIALECT_ID, UM_CLAIM_URI, UM_TENANT_ID,UM_MAPPED_ATTRIBUTE_DOMAIN),
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_PROFILE_CONFIG(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_DIALECT_ID INTEGER NOT NULL,
      UM_PROFILE_NAME VARCHAR(255),
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_DIALECT_ID, UM_TENANT_ID) REFERENCES UM_DIALECT(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_CLAIM_BEHAVIOR(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_PROFILE_ID INTEGER,
      UM_CLAIM_ID INTEGER,
      UM_BEHAVIOUR SMALLINT,
      UM_TENANT_ID INTEGER DEFAULT 0,
      FOREIGN KEY(UM_PROFILE_ID, UM_TENANT_ID) REFERENCES UM_PROFILE_CONFIG(UM_ID,UM_TENANT_ID),
      FOREIGN KEY(UM_CLAIM_ID, UM_TENANT_ID) REFERENCES UM_CLAIM(UM_ID,UM_TENANT_ID),
      PRIMARY KEY(UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_HYBRID_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX UM_ROLE_NAME_IND ON UM_HYBRID_ROLE(UM_ROLE_NAME);
  
  CREATE TABLE UM_HYBRID_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UM_DOMAIN_ID INTEGER,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID, UM_DOMAIN_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_HYBRID_ROLE(UM_ID, UM_TENANT_ID) ON DELETE CASCADE,
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_ROLE_NAME VARCHAR(255) NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID),
      UNIQUE(UM_ROLE_NAME,UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE UM_SYSTEM_USER_ROLE(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255),
      UM_ROLE_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      UNIQUE (UM_USER_NAME, UM_ROLE_ID, UM_TENANT_ID),
      FOREIGN KEY (UM_ROLE_ID, UM_TENANT_ID) REFERENCES UM_SYSTEM_ROLE(UM_ID, UM_TENANT_ID),
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  
  CREATE TABLE UM_HYBRID_REMEMBER_ME(
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_NAME VARCHAR(255) NOT NULL,
      UM_COOKIE_VALUE VARCHAR(1024),
      UM_CREATED_TIME TIMESTAMP,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS UM_UUID_DOMAIN_MAPPER (
      UM_ID INTEGER NOT NULL AUTO_INCREMENT,
      UM_USER_ID VARCHAR(255) NOT NULL,
      UM_DOMAIN_ID INTEGER NOT NULL,
      UM_TENANT_ID INTEGER DEFAULT 0,
      PRIMARY KEY (UM_ID),
      UNIQUE (UM_USER_ID),
      FOREIGN KEY (UM_DOMAIN_ID, UM_TENANT_ID) REFERENCES UM_DOMAIN(UM_DOMAIN_ID, UM_TENANT_ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE INDEX UUID_DM_UID_TID ON UM_UUID_DOMAIN_MAPPER(UM_USER_ID, UM_TENANT_ID);
  