text: --
  -- Copyright 2018 WSO2 Inc. (http://wso2.org)
  --
  -- Licensed under the Apache License, Version 2.0 (the "License");
  -- you may not use this file except in compliance with the License.
  -- You may obtain a copy of the License at
  --
  --     http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.
  --
  
  DROP DATABASE IF EXISTS WSO2IS_REG_DB;
  DROP DATABASE IF EXISTS WSO2IS_USER_DB;
  DROP DATABASE IF EXISTS WSO2IS_IDENTITY_DB;
  DROP DATABASE IF EXISTS WSO2IS_BPS_DB;
  DROP DATABASE IF EXISTS WSO2IS_CONSENT_DB;
  DROP DATABASE IF EXISTS WSO2_CLUSTER_DB;
  DROP DATABASE IF EXISTS IS_ANALYTICS_DB;
  DROP DATABASE IF EXISTS WSO2_CARBON_DB;
  DROP DATABASE IF EXISTS WSO2_PERSISTENCE_DB;
  DROP DATABASE IF EXISTS WSO2_METRICS_DB;
  DROP DATABASE IF EXISTS WSO2_STATUS_DASHBOARD_DB;
  
  
  CREATE DATABASE WSO2IS_REG_DB;
  CREATE DATABASE WSO2IS_USER_DB;
  CREATE DATABASE WSO2IS_IDENTITY_DB;
  CREATE DATABASE WSO2IS_BPS_DB;
  CREATE DATABASE WSO2IS_CONSENT_DB;
  CREATE DATABASE WSO2_CLUSTER_DB;
  CREATE DATABASE IS_ANALYTICS_DB;
  CREATE DATABASE WSO2_CARBON_DB;
  CREATE DATABASE WSO2_PERSISTENCE_DB;
  CREATE DATABASE WSO2_METRICS_DB;
  CREATE DATABASE WSO2_STATUS_DASHBOARD_DB;
  
  USE WSO2IS_REG_DB;
  
  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)
  )ENGINE INNODB;
  
  CREATE INDEX REG_PATH_IND_BY_PATH_VALUE USING HASH ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID);
  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),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_DOMAIN_ID, 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_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_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),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  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),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE(UM_ROLE_NAME, UM_TENANT_ID);
  
  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;
  
  USE WSO2IS_USER_DB;
  
  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)
  )ENGINE INNODB;
  
  CREATE INDEX REG_PATH_IND_BY_PATH_VALUE USING HASH ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID);
  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),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_DOMAIN_ID, 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_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_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),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  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),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  )ENGINE INNODB;
  
  CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE(UM_ROLE_NAME, UM_TENANT_ID);
  
  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;
  
  USE WSO2IS_IDENTITY_DB;
  
  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(1024),
              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(1024),
              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),
              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)
  )ENGINE INNODB;
  
  CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_STATE, USER_TYPE);
  
  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_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN);
  
  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
  );
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_AUTHORIZATION_CODE (
              CODE_ID VARCHAR (255),
              AUTHORIZATION_CODE VARCHAR(2048),
              CONSUMER_KEY_ID INTEGER,
              CALLBACK_URL VARCHAR(1024),
              SCOPE VARCHAR(2048),
              AUTHZ_USER VARCHAR (100),
              TENANT_ID INTEGER,
              USER_DOMAIN VARCHAR(50),
              TIME_CREATED 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),
              PRIMARY KEY (CODE_ID),
              FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  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 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,
              PRIMARY KEY (SCOPE_ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OAUTH2_SCOPE_BINDING (
              SCOPE_ID INTEGER NOT NULL,
              SCOPE_BINDING VARCHAR(255),
              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 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);
  
  
  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 INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);
  
  
  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 INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);
  
  
  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',
          PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  ALTER TABLE SP_APP ADD CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID);
  
  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 INDEX IDX_SP_TEMPLATE ON SP_TEMPLATE (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),
  			PRIMARY KEY (ID),
  			UNIQUE (TENANT_ID, NAME)
  )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,
              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 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(255) NOT NULL,
    USER_DOMAIN VARCHAR(127) 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,
    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) ,
    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 (
              ID INTEGER NOT NULL AUTO_INCREMENT,
              NAME VARCHAR(255) NOT NULL,
              TENANT_ID INTEGER DEFAULT -1,
              PRIMARY KEY (ID)
  )ENGINE INNODB;
  
  CREATE TABLE IF NOT EXISTS IDN_OIDC_SCOPE_CLAIM_MAPPING (
              ID INTEGER NOT NULL AUTO_INCREMENT,
              SCOPE_ID INTEGER,
              EXTERNAL_CLAIM_ID INTEGER,
              PRIMARY KEY (ID),
              FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OIDC_SCOPE(ID) ON DELETE CASCADE,
              FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE
  )ENGINE INNODB;
  
  CREATE INDEX IDX_AT_SI_ECI ON IDN_OIDC_SCOPE_CLAIM_MAPPING(SCOPE_ID, EXTERNAL_CLAIM_ID);
  
  USE WSO2IS_BPS_DB;
  
  --
  -- Licensed to the Apache Software Foundation (ASF) under one
  -- or more contributor license agreements.  See the NOTICE file
  -- distributed with this work for additional information
  -- regarding copyright ownership.  The ASF licenses this file
  -- to you under the Apache License, Version 2.0 (the
  -- "License"); you may not use this file except in compliance
  -- with the License.  You may obtain a copy of the License at
  --
  --    http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing,
  -- software distributed under the License is distributed on an
  -- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  -- KIND, either express or implied.  See the License for the
  -- specific language governing permissions and limitations
  -- under the License.
  --
  
  --
  -- BPEL Related SQL Scripts
  --
  
  
  create table ODE_SCHEMA_VERSION (VERSION integer);
  insert into ODE_SCHEMA_VERSION values (6);
  -- Apache ODE - SimpleScheduler Database Schema
  --
  -- MySQL scripts by Maciej Szefler.
  --
  --
  DROP TABLE IF EXISTS ODE_JOB;
  
  CREATE TABLE ODE_JOB (
    jobid CHAR(64)  NOT NULL DEFAULT '',
    ts BIGINT  NOT NULL DEFAULT 0,
    nodeid char(64)  NULL,
    scheduled int  NOT NULL DEFAULT 0,
    transacted int  NOT NULL DEFAULT 0,
  
    instanceId BIGINT,
    mexId varchar(255),
    processId varchar(255),
    type varchar(255),
    channel varchar(255),
    correlatorId varchar(255),
    correlationKeySet varchar(255),
    retryCount int,
    inMem int,
    detailsExt blob(4096),
  
    PRIMARY KEY(jobid),
    INDEX IDX_ODE_JOB_TS(ts),
    INDEX IDX_ODE_JOB_NODEID(nodeid)
  )
  ENGINE=innodb;
  
  COMMIT;
  
  CREATE TABLE TASK_ATTACHMENT (ATTACHMENT_ID BIGINT NOT NULL, MESSAGE_EXCHANGE_ID VARCHAR(255), PRIMARY KEY (ATTACHMENT_ID)) ENGINE=innodb;
  CREATE TABLE ODE_ACTIVITY_RECOVERY (ID BIGINT NOT NULL, ACTIONS VARCHAR(255), ACTIVITY_ID BIGINT, CHANNEL VARCHAR(255), DATE_TIME DATETIME, DETAILS TEXT, INSTANCE_ID BIGINT, REASON VARCHAR(255), RETRIES INTEGER, PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE ODE_CORRELATION_SET (CORRELATION_SET_ID BIGINT NOT NULL, CORRELATION_KEY VARCHAR(255), NAME VARCHAR(255), SCOPE_ID BIGINT, PRIMARY KEY (CORRELATION_SET_ID)) ENGINE=innodb;
  CREATE TABLE ODE_CORRELATOR (CORRELATOR_ID BIGINT NOT NULL, CORRELATOR_KEY VARCHAR(255), PROC_ID BIGINT, PRIMARY KEY (CORRELATOR_ID)) ENGINE=innodb;
  CREATE TABLE ODE_CORSET_PROP (ID BIGINT NOT NULL, CORRSET_ID BIGINT, PROP_KEY VARCHAR(255), PROP_VALUE VARCHAR(255), PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE ODE_EVENT (EVENT_ID BIGINT NOT NULL, DETAIL VARCHAR(255), DATA BLOB, SCOPE_ID BIGINT, TSTAMP DATETIME, TYPE VARCHAR(255), INSTANCE_ID BIGINT, PROCESS_ID BIGINT, PRIMARY KEY (EVENT_ID)) ENGINE=innodb;
  CREATE TABLE ODE_FAULT (FAULT_ID BIGINT NOT NULL, ACTIVITY_ID INTEGER, DATA TEXT, MESSAGE VARCHAR(4000), LINE_NUMBER INTEGER, NAME VARCHAR(255), PRIMARY KEY (FAULT_ID)) ENGINE=innodb;
  CREATE TABLE ODE_MESSAGE (MESSAGE_ID BIGINT NOT NULL, DATA TEXT, HEADER TEXT, TYPE VARCHAR(255), MESSAGE_EXCHANGE_ID VARCHAR(255), PRIMARY KEY (MESSAGE_ID)) ENGINE=innodb;
  CREATE TABLE ODE_MESSAGE_EXCHANGE (MESSAGE_EXCHANGE_ID VARCHAR(255) NOT NULL, CALLEE VARCHAR(255), CHANNEL VARCHAR(255), CORRELATION_ID VARCHAR(255), CORRELATION_KEYS VARCHAR(255), CORRELATION_STATUS VARCHAR(255), CREATE_TIME DATETIME, DIRECTION INTEGER, EPR TEXT, FAULT VARCHAR(255), FAULT_EXPLANATION VARCHAR(255), OPERATION VARCHAR(255), PARTNER_LINK_MODEL_ID INTEGER, PATTERN VARCHAR(255), PIPED_ID VARCHAR(255), PORT_TYPE VARCHAR(255), PROPAGATE_TRANS BIT, STATUS VARCHAR(255), SUBSCRIBER_COUNT INTEGER, CORR_ID BIGINT, PARTNER_LINK_ID BIGINT, PROCESS_ID BIGINT, PROCESS_INSTANCE_ID BIGINT, REQUEST_MESSAGE_ID BIGINT, RESPONSE_MESSAGE_ID BIGINT, PRIMARY KEY (MESSAGE_EXCHANGE_ID)) ENGINE=innodb;
  CREATE TABLE ODE_MESSAGE_ROUTE (MESSAGE_ROUTE_ID BIGINT NOT NULL, CORRELATION_KEY VARCHAR(255), GROUP_ID VARCHAR(255), ROUTE_INDEX INTEGER, PROCESS_INSTANCE_ID INTEGER, ROUTE_POLICY VARCHAR(16), CORR_ID BIGINT, PRIMARY KEY (MESSAGE_ROUTE_ID)) ENGINE=innodb;
  CREATE TABLE ODE_MEX_PROP (ID BIGINT NOT NULL, MEX_ID VARCHAR(255), PROP_KEY VARCHAR(255), PROP_VALUE VARCHAR(2000), PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE ODE_PARTNER_LINK (PARTNER_LINK_ID BIGINT NOT NULL, MY_EPR TEXT, MY_ROLE_NAME VARCHAR(255), MY_ROLE_SERVICE_NAME VARCHAR(255), MY_SESSION_ID VARCHAR(255), PARTNER_EPR TEXT, PARTNER_LINK_MODEL_ID INTEGER, PARTNER_LINK_NAME VARCHAR(255), PARTNER_ROLE_NAME VARCHAR(255), PARTNER_SESSION_ID VARCHAR(255), SCOPE_ID BIGINT, PRIMARY KEY (PARTNER_LINK_ID)) ENGINE=innodb;
  CREATE TABLE ODE_PROCESS (ID BIGINT NOT NULL, GUID VARCHAR(255), PROCESS_ID VARCHAR(255), PROCESS_TYPE VARCHAR(255), VERSION BIGINT, PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE ODE_PROCESS_INSTANCE (ID BIGINT NOT NULL, DATE_CREATED DATETIME, EXECUTION_STATE BLOB, FAULT_ID BIGINT, LAST_ACTIVE_TIME DATETIME, LAST_RECOVERY_DATE DATETIME, PREVIOUS_STATE SMALLINT, SEQUENCE BIGINT, INSTANCE_STATE SMALLINT, INSTANTIATING_CORRELATOR_ID BIGINT, PROCESS_ID BIGINT, ROOT_SCOPE_ID BIGINT, PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE ODE_SCOPE (SCOPE_ID BIGINT NOT NULL, MODEL_ID INTEGER, SCOPE_NAME VARCHAR(255), SCOPE_STATE VARCHAR(255), PROCESS_INSTANCE_ID BIGINT, PARENT_SCOPE_ID BIGINT, PRIMARY KEY (SCOPE_ID)) ENGINE=innodb;
  CREATE TABLE ODE_XML_DATA (XML_DATA_ID BIGINT NOT NULL, DATA TEXT, IS_SIMPLE_TYPE BIT, NAME VARCHAR(255), SCOPE_ID BIGINT, PRIMARY KEY (XML_DATA_ID)) ENGINE=innodb;
  CREATE TABLE ODE_XML_DATA_PROP (ID BIGINT NOT NULL, XML_DATA_ID BIGINT, PROP_KEY VARCHAR(255), PROP_VALUE VARCHAR(255), PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID TINYINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY KEY (ID)) ENGINE=innodb;
  CREATE TABLE STORE_DU (NAME VARCHAR(255) NOT NULL, DEPLOYDT DATETIME, DEPLOYER VARCHAR(255), DIR VARCHAR(255), PRIMARY KEY (NAME)) ENGINE=innodb;
  CREATE TABLE STORE_PROCESS (PID VARCHAR(255) NOT NULL, STATE VARCHAR(255), TYPE VARCHAR(255), VERSION BIGINT, DU VARCHAR(255), PRIMARY KEY (PID)) ENGINE=innodb;
  CREATE TABLE STORE_PROCESS_PROP (id BIGINT NOT NULL, PROP_KEY VARCHAR(255), PROP_VAL VARCHAR(255), PRIMARY KEY (id)) ENGINE=innodb;
  CREATE TABLE STORE_PROC_TO_PROP (PROCESSCONFDAOIMPL_PID VARCHAR(255), ELEMENT_ID BIGINT) ENGINE=innodb;
  CREATE TABLE STORE_VERSIONS (id BIGINT NOT NULL, VERSION BIGINT, PRIMARY KEY (id)) ENGINE=innodb;
  CREATE INDEX I_D_TASK_ATTACMENT ON TASK_ATTACHMENT (MESSAGE_EXCHANGE_ID);
  CREATE INDEX I_D_CTVRY_INSTANCE ON ODE_ACTIVITY_RECOVERY (INSTANCE_ID);
  CREATE INDEX I_D_CR_ST_SCOPE ON ODE_CORRELATION_SET (SCOPE_ID);
  CREATE INDEX I_D_CRLTR_PROCESS ON ODE_CORRELATOR (PROC_ID);
  CREATE INDEX I_D_CRPRP_CORRSET ON ODE_CORSET_PROP (CORRSET_ID);
  CREATE INDEX I_OD_VENT_INSTANCE ON ODE_EVENT (INSTANCE_ID);
  CREATE INDEX I_OD_VENT_PROCESS ON ODE_EVENT (PROCESS_ID);
  CREATE INDEX I_OD_MSSG_MESSAGEEXCHANGE ON ODE_MESSAGE (MESSAGE_EXCHANGE_ID);
  CREATE INDEX I_D_MSHNG_CORRELATOR ON ODE_MESSAGE_EXCHANGE (CORR_ID);
  CREATE INDEX I_D_MSHNG_PARTNERLINK ON ODE_MESSAGE_EXCHANGE (PARTNER_LINK_ID);
  CREATE INDEX I_D_MSHNG_PROCESS ON ODE_MESSAGE_EXCHANGE (PROCESS_ID);
  CREATE INDEX I_D_MSHNG_PROCESSINST ON ODE_MESSAGE_EXCHANGE (PROCESS_INSTANCE_ID);
  CREATE INDEX I_D_MSHNG_REQUEST ON ODE_MESSAGE_EXCHANGE (REQUEST_MESSAGE_ID);
  CREATE INDEX I_D_MSHNG_RESPONSE ON ODE_MESSAGE_EXCHANGE (RESPONSE_MESSAGE_ID);
  CREATE INDEX I_D_MS_RT_CORRELATOR ON ODE_MESSAGE_ROUTE (CORR_ID);
  CREATE INDEX I_D_MS_RT_PROCESSINST ON ODE_MESSAGE_ROUTE (PROCESS_INSTANCE_ID);
  CREATE INDEX I_D_MXPRP_MEX ON ODE_MEX_PROP (MEX_ID);
  CREATE INDEX I_D_PRLNK_SCOPE ON ODE_PARTNER_LINK (SCOPE_ID);
  CREATE INDEX I_D_PRTNC_FAULT ON ODE_PROCESS_INSTANCE (FAULT_ID);
  CREATE INDEX I_D_PRTNC_INSTANTIATINGCORRELATOR ON ODE_PROCESS_INSTANCE (INSTANTIATING_CORRELATOR_ID);
  CREATE INDEX I_D_PRTNC_PROCESS ON ODE_PROCESS_INSTANCE (PROCESS_ID);
  CREATE INDEX I_D_PRTNC_ROOTSCOPE ON ODE_PROCESS_INSTANCE (ROOT_SCOPE_ID);
  CREATE INDEX I_OD_SCOP_PARENTSCOPE ON ODE_SCOPE (PARENT_SCOPE_ID);
  CREATE INDEX I_OD_SCOP_PROCESSINSTANCE ON ODE_SCOPE (PROCESS_INSTANCE_ID);
  CREATE INDEX I_D_XM_DT_SCOPE ON ODE_XML_DATA (SCOPE_ID);
  CREATE INDEX I_D_XMPRP_XMLDATA ON ODE_XML_DATA_PROP (XML_DATA_ID);
  CREATE INDEX I_STR_CSS_DU ON STORE_PROCESS (DU);
  CREATE INDEX I_STR_PRP_ELEMENT ON STORE_PROC_TO_PROP (ELEMENT_ID);
  CREATE INDEX I_STR_PRP_PROCESSCONFDAOIMPL_PID ON STORE_PROC_TO_PROP (PROCESSCONFDAOIMPL_PID);
  
  
  
  --
  -- Human Task Related SQL Scripts
  --
  
  
  CREATE TABLE HT_DEADLINE (id BIGINT NOT NULL, DEADLINE_DATE DATETIME NOT NULL, DEADLINE_NAME VARCHAR(255) NOT NULL, STATUS_TOBE_ACHIEVED VARCHAR(255) NOT NULL, TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_DEPLOYMENT_UNIT (id BIGINT NOT NULL, CHECKSUM VARCHAR(255) NOT NULL, DEPLOYED_ON DATETIME, DEPLOY_DIR VARCHAR(255) NOT NULL, NAME VARCHAR(255) NOT NULL, PACKAGE_NAME VARCHAR(255) NOT NULL, STATUS VARCHAR(255) NOT NULL, TENANT_ID BIGINT NOT NULL, VERSION BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_EVENT (id BIGINT NOT NULL, EVENT_DETAILS VARCHAR(255), NEW_STATE VARCHAR(255), OLD_STATE VARCHAR(255), EVENT_TIMESTAMP DATETIME NOT NULL, EVENT_TYPE VARCHAR(255) NOT NULL, EVENT_USER VARCHAR(255) NOT NULL, TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_GENERIC_HUMAN_ROLE (GHR_ID BIGINT NOT NULL, GHR_TYPE VARCHAR(255), TASK_ID BIGINT, PRIMARY KEY (GHR_ID)) ENGINE = innodb;
  CREATE TABLE HT_HUMANROLE_ORGENTITY (HUMANROLE_ID BIGINT, ORGENTITY_ID BIGINT) ENGINE = innodb;
  CREATE TABLE HT_JOB (id BIGINT NOT NULL, JOB_DETAILS VARCHAR(4000), JOB_NAME VARCHAR(255), NODEID VARCHAR(255), SCHEDULED VARCHAR(1) NOT NULL, TASKID BIGINT NOT NULL, JOB_TIME BIGINT NOT NULL, TRANSACTED VARCHAR(1) NOT NULL, JOB_TYPE VARCHAR(255) NOT NULL, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_MESSAGE (MESSAGE_ID BIGINT NOT NULL, MESSAGE_DATA LONGTEXT, MESSAGE_HEADER LONGTEXT, MESSAGE_TYPE VARCHAR(255), MESSAGE_NAME VARCHAR(512), TASK_ID BIGINT, PRIMARY KEY (MESSAGE_ID)) ENGINE = innodb;
  CREATE TABLE HT_ORG_ENTITY (ORG_ENTITY_ID BIGINT NOT NULL, ORG_ENTITY_NAME VARCHAR(255), ORG_ENTITY_TYPE VARCHAR(255), PRIMARY KEY (ORG_ENTITY_ID)) ENGINE = innodb;
  CREATE TABLE HT_PRESENTATION_ELEMENT (id BIGINT NOT NULL, PE_CONTENT VARCHAR(2000), XML_LANG VARCHAR(255), PE_TYPE VARCHAR(31), CONTENT_TYPE VARCHAR(255), TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_PRESENTATION_PARAM (id BIGINT NOT NULL, PARAM_NAME VARCHAR(255), PARAM_TYPE VARCHAR(255), PARAM_VALUE VARCHAR(2000), TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_TASK (id BIGINT NOT NULL, ACTIVATION_TIME DATETIME, COMPLETE_BY_TIME DATETIME, CREATED_ON DATETIME, ESCALATED VARCHAR(1), EXPIRATION_TIME DATETIME, TASK_NAME VARCHAR(255) NOT NULL, PACKAGE_NAME VARCHAR(255) NOT NULL, PRIORITY INTEGER NOT NULL, SKIPABLE VARCHAR(1), START_BY_TIME DATETIME, STATUS VARCHAR(255) NOT NULL, STATUS_BEFORE_SUSPENSION VARCHAR(255), TASK_DEF_NAME VARCHAR(255) NOT NULL, TASK_VERSION BIGINT NOT NULL, TENANT_ID INTEGER NOT NULL, TASK_TYPE VARCHAR(255) NOT NULL, UPDATED_ON DATETIME, FAILURE_MESSAGE BIGINT, INPUT_MESSAGE BIGINT, OUTPUT_MESSAGE BIGINT, PARENTTASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_TASK_ATTACHMENT (id BIGINT NOT NULL, ACCESS_TYPE VARCHAR(255), ATTACHED_AT DATETIME, CONTENT_TYPE VARCHAR(255), ATTACHMENT_NAME VARCHAR(255), ATTACHMENT_VALUE VARCHAR(255), TASK_ID BIGINT, ATTACHED_BY BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_TASK_COMMENT (id BIGINT NOT NULL, COMMENT_TEXT VARCHAR(4000), COMMENTED_BY VARCHAR(100), COMMENTED_ON DATETIME, MODIFIED_BY VARCHAR(100), MODIFIED_ON DATETIME, TASK_ID BIGINT, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE TABLE HT_VERSIONS (id BIGINT NOT NULL, TASK_VERSION BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE = innodb;
  CREATE INDEX I_HT_DDLN_TASK ON HT_DEADLINE (TASK_ID);
  CREATE INDEX I_HT_VENT_TASK ON HT_EVENT (TASK_ID);
  CREATE INDEX I_HT_G_RL_TASK ON HT_GENERIC_HUMAN_ROLE (TASK_ID);
  CREATE INDEX I_HT_HTTY_ELEMENT ON HT_HUMANROLE_ORGENTITY (ORGENTITY_ID);
  CREATE INDEX I_HT_HTTY_HUMANROLE_ID ON HT_HUMANROLE_ORGENTITY (HUMANROLE_ID);
  CREATE INDEX I_HT_MSSG_TASK ON HT_MESSAGE (TASK_ID);
  CREATE INDEX I_HT_PMNT_DTYPE ON HT_PRESENTATION_ELEMENT (PE_TYPE);
  CREATE INDEX I_HT_PMNT_TASK ON HT_PRESENTATION_ELEMENT (TASK_ID);
  CREATE INDEX I_HT_PPRM_TASK ON HT_PRESENTATION_PARAM (TASK_ID);
  CREATE INDEX I_HT_TASK_FAILUREMESSAGE ON HT_TASK (FAILURE_MESSAGE);
  CREATE INDEX I_HT_TASK_INPUTMESSAGE ON HT_TASK (INPUT_MESSAGE);
  CREATE INDEX I_HT_TASK_OUTPUTMESSAGE ON HT_TASK (OUTPUT_MESSAGE);
  CREATE INDEX I_HT_TASK_PARENTTASK ON HT_TASK (PARENTTASK_ID);
  CREATE INDEX I_HT_TMNT_ATTACHEDBY ON HT_TASK_ATTACHMENT (ATTACHED_BY);
  CREATE INDEX I_HT_TMNT_TASK ON HT_TASK_ATTACHMENT (TASK_ID);
  CREATE INDEX I_HT_TMNT_TASK1 ON HT_TASK_COMMENT (TASK_ID);
  
  --
  -- Attachment Management Related SQL Scripts
  --
  CREATE TABLE ATTACHMENT (
  	id BIGINT NOT NULL AUTO_INCREMENT,
  	CREATED_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  	ATTACHMENT_NAME VARCHAR(255) NOT NULL,
  	CREATED_BY VARCHAR(255) NOT NULL,
  	CONTENT_TYPE VARCHAR(255) NOT NULL,
  	ATTACHMENT_URL VARCHAR(2048) NOT NULL,
  	ATTACHMENT_CONTENT BLOB,
  	PRIMARY KEY (id));
  
  -- CREATE INDEX I_ATTACHMENT_URL ON ATTACHMENT (ATTACHMENT_URL);
  
  
  --
  -- B4P Related SQL Scripts
  --
  CREATE TABLE HT_COORDINATION_DATA (MESSAGE_ID VARCHAR(255) NOT NULL, PROCESS_INSTANCE_ID VARCHAR(255), PROTOCOL_HANDlER_URL VARCHAR(255) NOT NULL, TASK_ID VARCHAR(255), PRIMARY KEY (MESSAGE_ID)) ENGINE = innodb;
  
  USE WSO2IS_CONSENT_DB;
  
  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');
  
  USE WSO2_METRICS_DB;
  
  --
  -- Copyright 2015 WSO2 Inc. (http://wso2.org)
  --
  -- Licensed under the Apache License, Version 2.0 (the "License");
  -- you may not use this file except in compliance with the License.
  -- You may obtain a copy of the License at
  --
  --     http://www.apache.org/licenses/LICENSE-2.0
  --
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.
  --
  
  CREATE TABLE IF NOT EXISTS METRIC_GAUGE (
      ID BIGINT AUTO_INCREMENT PRIMARY KEY,
      SOURCE VARCHAR(255) NOT NULL,
      TIMESTAMP BIGINT NOT NULL,
      NAME VARCHAR(255) NOT NULL,
      VALUE VARCHAR(100) NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS METRIC_COUNTER (
      ID BIGINT AUTO_INCREMENT PRIMARY KEY,
      SOURCE VARCHAR(255) NOT NULL,
      TIMESTAMP BIGINT NOT NULL,
      NAME VARCHAR(255) NOT NULL,
      COUNT BIGINT NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS METRIC_METER (
      ID BIGINT AUTO_INCREMENT PRIMARY KEY,
      SOURCE VARCHAR(255) NOT NULL,
      TIMESTAMP BIGINT NOT NULL,
      NAME VARCHAR(255) NOT NULL,
      COUNT BIGINT NOT NULL,
      MEAN_RATE DECIMAL NOT NULL,
      M1_RATE DECIMAL NOT NULL,
      M5_RATE DECIMAL NOT NULL,
      M15_RATE DECIMAL NOT NULL,
      RATE_UNIT VARCHAR(50) NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS METRIC_HISTOGRAM (
      ID BIGINT AUTO_INCREMENT PRIMARY KEY,
      SOURCE VARCHAR(255) NOT NULL,
      TIMESTAMP BIGINT NOT NULL,
      NAME VARCHAR(255) NOT NULL,
      COUNT BIGINT NOT NULL,
      MAX DECIMAL NOT NULL,
      MEAN DECIMAL NOT NULL,
      MIN DECIMAL NOT NULL,
      STDDEV DECIMAL NOT NULL,
      P50 DECIMAL NOT NULL,
      P75 DECIMAL NOT NULL,
      P95 DECIMAL NOT NULL,
      P98 DECIMAL NOT NULL,
      P99 DECIMAL NOT NULL,
      P999 DECIMAL NOT NULL
  );
  
  CREATE TABLE IF NOT EXISTS METRIC_TIMER (
      ID BIGINT AUTO_INCREMENT PRIMARY KEY,
      SOURCE VARCHAR(255) NOT NULL,
      TIMESTAMP BIGINT NOT NULL,
      NAME VARCHAR(255) NOT NULL,
      COUNT BIGINT NOT NULL,
      MAX DECIMAL NOT NULL,
      MEAN DECIMAL NOT NULL,
      MIN DECIMAL NOT NULL,
      STDDEV DECIMAL NOT NULL,
      P50 DECIMAL NOT NULL,
      P75 DECIMAL NOT NULL,
      P95 DECIMAL NOT NULL,
      P98 DECIMAL NOT NULL,
      P99 DECIMAL NOT NULL,
      P999 DECIMAL NOT NULL,
      MEAN_RATE DECIMAL NOT NULL,
      M1_RATE DECIMAL NOT NULL,
      M5_RATE DECIMAL NOT NULL,
      M15_RATE DECIMAL NOT NULL,
      RATE_UNIT VARCHAR(50) NOT NULL,
      DURATION_UNIT VARCHAR(50) NOT NULL
  );
  
  CREATE INDEX IDX_TIMESTAMP_GAUGE ON METRIC_GAUGE (TIMESTAMP);
  CREATE INDEX IDX_TIMESTAMP_COUNTER ON METRIC_COUNTER (TIMESTAMP);
  CREATE INDEX IDX_TIMESTAMP_METER ON METRIC_METER (TIMESTAMP);
  CREATE INDEX IDX_TIMESTAMP_HISTOGRAM ON METRIC_HISTOGRAM (TIMESTAMP);
  CREATE INDEX IDX_TIMESTAMP_TIMER ON METRIC_TIMER (TIMESTAMP);
  