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.
  --
  
  -- MYSQL DATABASE SCRIPTS --
  
  DROP DATABASE IF EXISTS WSO2AM_COMMON_DB;
  DROP DATABASE IF EXISTS WSO2AM_APIMGT_DB;
  DROP DATABASE IF EXISTS WSO2AM_STAT_DB;
  GO
  
  CREATE DATABASE WSO2AM_COMMON_DB;
  CREATE DATABASE WSO2AM_APIMGT_DB;
  CREATE DATABASE WSO2AM_STAT_DB;
  GO
  
  USE WSO2AM_COMMON_DB;
  GO
  
  --create table REG_CLUSTER_LOCK
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CLUSTER_LOCK]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_CLUSTER_LOCK (
               REG_LOCK_NAME VARCHAR (20),
               REG_LOCK_STATUS VARCHAR (20),
               REG_LOCKED_TIME DATETIME,
               REG_TENANT_ID INTEGER DEFAULT 0,
               PRIMARY KEY (REG_LOCK_NAME)
  );
  
  --create table REG_LOG
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_LOG]') AND TYPE IN (N'U'))
  
  CREATE TABLE REG_LOG (
               REG_LOG_ID INTEGER IDENTITY(1,1) NOT NULL,
               REG_PATH VARCHAR (2000),
               REG_USER_ID VARCHAR (31) NOT NULL,
               REG_LOGGED_TIME DATETIME 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)
  );
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_LOG_IND_BY_REG_LOGTIME')
  DROP INDEX REG_LOG.REG_LOG_IND_BY_REG_LOGTIME
  CREATE INDEX REG_LOG_IND_BY_REG_LOGTIME ON REG_LOG(REG_LOGGED_TIME, REG_TENANT_ID);
  
  --create table regpath
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_PATH]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_PATH(
               REG_PATH_ID INTEGER IDENTITY(1,1) NOT NULL,
               REG_PATH_VALUE VARCHAR(895) 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)
  );
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_PATH_IND_BY_PATH_VALUE')
  DROP INDEX REG_PATH.REG_PATH_IND_BY_PATH_VALUE
  CREATE INDEX REG_PATH_IND_BY_PATH_VALUE ON REG_PATH(REG_PATH_VALUE, REG_TENANT_ID);
  
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_PATH_IND_BY_PARENT_ID')
  DROP INDEX REG_PATH.REG_PATH_IND_BY_PARENT_ID
  CREATE INDEX REG_PATH_IND_BY_PARENT_ID ON REG_PATH(REG_PATH_PARENT_ID, REG_TENANT_ID);
  --create table regcontent
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CONTENT]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_CONTENT (
               REG_CONTENT_ID INTEGER IDENTITY(1,1) NOT NULL,
               REG_CONTENT_DATA VARBINARY(MAX),
               REG_TENANT_ID INTEGER DEFAULT 0,
               CONSTRAINT PK_REG_CONTENT PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  );
  
  --create table REG_CONTENT_HISTORY
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_CONTENT_HISTORY]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_CONTENT_HISTORY (
               REG_CONTENT_ID INTEGER NOT NULL,
               REG_CONTENT_DATA VARBINARY(MAX),
               REG_DELETED   SMALLINT,
               REG_TENANT_ID INTEGER DEFAULT 0,
               CONSTRAINT PK_REG_CONTENT_HISTORY PRIMARY KEY(REG_CONTENT_ID, REG_TENANT_ID)
  );
  
  
  --create table REG_RESOURCE
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_RESOURCE (
              REG_PATH_ID         INTEGER NOT NULL,
              REG_NAME            VARCHAR(256),
              REG_VERSION          INTEGER IDENTITY(1,1) NOT NULL,
              REG_MEDIA_TYPE      VARCHAR(500),
              REG_CREATOR         VARCHAR(31) NOT NULL,
              REG_CREATED_TIME    DATETIME NOT NULL,
              REG_LAST_UPDATOR    VARCHAR(31),
              REG_LAST_UPDATED_TIME   DATETIME NOT NULL,
              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)
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_FK_BY_PATH_ID]') AND PARENT_OBJECT_ID = OBJECT_ID(N'DBO.[REG_RESOURCE]'))
  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);
  
  --This foriegn key constrainst is maintained from the code level
  --IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_FK_BY_CONTENT_ID]') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE]'))
  --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);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_NAME')
  DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_NAME
  CREATE INDEX REG_RESOURCE_IND_BY_NAME ON REG_RESOURCE(REG_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_PATH_ID_NAME')
  DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_PATH_ID_NAME
  CREATE INDEX REG_RESOURCE_IND_BY_PATH_ID_NAME ON REG_RESOURCE(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_UUID')
  DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_UUID
  CREATE INDEX REG_RESOURCE_IND_BY_UUID ON REG_RESOURCE(REG_UUID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_TENANT')
  DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_TENANT
  CREATE INDEX REG_RESOURCE_IND_BY_TENANT ON REG_RESOURCE(REG_TENANT_ID, REG_UUID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_IND_BY_TYPE')
  DROP INDEX REG_RESOURCE.REG_RESOURCE_IND_BY_TYPE
  CREATE INDEX REG_RESOURCE_IND_BY_TYPE ON REG_RESOURCE(REG_TENANT_ID, REG_MEDIA_TYPE);
  
  --create table REG_RESOURCE_HISTORY
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_HISTORY]') AND TYPE IN (N'U'))
  CREATE TABLE  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    DATETIME NOT NULL,
              REG_LAST_UPDATOR    VARCHAR(31),
              REG_LAST_UPDATED_TIME  DATETIME NOT NULL,
              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)
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HIST_FK_BY_PATHID]') AND parent_object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HISTORY]'))
  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);
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HIST_FK_BY_CONTENT_ID]') AND parent_object_id = OBJECT_ID(N'[dbo].[REG_RESOURCE_HISTORY]'))
  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);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_HISTORY_IND_BY_NAME')
  DROP INDEX REG_RESOURCE_HISTORY.REG_RESOURCE_HISTORY_IND_BY_NAME
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_NAME ON REG_RESOURCE_HISTORY(REG_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME')
  DROP INDEX REG_RESOURCE_HISTORY.REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME
  CREATE INDEX REG_RESOURCE_HISTORY_IND_BY_PATH_ID_NAME ON REG_RESOURCE_HISTORY(REG_PATH_ID, REG_NAME, REG_TENANT_ID);
  
  --create table REG_COMMENT
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_COMMENT]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_COMMENT (
              REG_ID      INTEGER IDENTITY(1,1) NOT NULL,
              REG_COMMENT_TEXT      VARCHAR(500) NOT NULL,
              REG_USER_ID           VARCHAR(31) NOT NULL,
              REG_COMMENTED_TIME    DATETIME NOT NULL,
              REG_TENANT_ID INTEGER DEFAULT 0,
              CONSTRAINT PK_REG_COMMENT PRIMARY KEY(REG_ID, REG_TENANT_ID)
  );
  
  --create table REG_RESOURCE_COMMENT
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_COMMENT]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_RESOURCE_COMMENT (
              REG_COMMENT_ID          INTEGER NOT NULL,
              REG_VERSION             INTEGER DEFAULT 0,
              REG_PATH_ID             INTEGER,
              REG_RESOURCE_NAME       VARCHAR(256),
              REG_TENANT_ID INTEGER DEFAULT 0
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_COMMENT_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_COMMENT'))
  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);
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_COMMENT_FK_BY_COMMENT_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_COMMENT'))
  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);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME')
  DROP INDEX REG_RESOURCE_COMMENT.REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_COMMENT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_COMMENT_IND_BY_VERSION')
  DROP INDEX REG_RESOURCE_COMMENT.REG_RESOURCE_COMMENT_IND_BY_VERSION
  CREATE INDEX REG_RESOURCE_COMMENT_IND_BY_VERSION ON REG_RESOURCE_COMMENT(REG_VERSION, REG_TENANT_ID);
  
  --create table  REG_RATING
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RATING]') AND TYPE IN (N'U'))
  CREATE TABLE REG_RATING (
              REG_ID      INTEGER IDENTITY(1,1) NOT NULL,
              REG_RATING        INTEGER NOT NULL,
              REG_USER_ID       VARCHAR(31) NOT NULL,
              REG_RATED_TIME    DATETIME NOT NULL,
              REG_TENANT_ID INTEGER DEFAULT 0,
              CONSTRAINT PK_REG_RATING PRIMARY KEY(REG_ID, REG_TENANT_ID)
  );
  
  --create table REG_RESOURCE_RATING
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_RATING]') AND TYPE IN (N'U'))
  CREATE TABLE  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
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING'))
  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);
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING_FK_BY_RATING_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[dbo].REG_RESOURCE_RATING'))
  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);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME')
  DROP INDEX REG_RESOURCE_RATING.REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_RATING(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_RATING_IND_BY_VERSION')
  DROP INDEX REG_RESOURCE_RATING.REG_RESOURCE_RATING_IND_BY_VERSION
  CREATE INDEX REG_RESOURCE_RATING_IND_BY_VERSION ON REG_RESOURCE_RATING(REG_VERSION, REG_TENANT_ID);
  
  --create table  REG_TAG
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_TAG]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_TAG (
              REG_ID         INTEGER IDENTITY(1,1) NOT NULL,
              REG_TAG_NAME       VARCHAR(500) NOT NULL,
              REG_USER_ID        VARCHAR(31) NOT NULL,
              REG_TAGGED_TIME    DATETIME NOT NULL,
              REG_TENANT_ID INTEGER DEFAULT 0,
              CONSTRAINT PK_REG_TAG PRIMARY KEY(REG_ID, REG_TENANT_ID)
  );
  
  
  
  --create table  REG_RESOURCE_TAG
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_TAG]') AND TYPE IN (N'U'))
  CREATE TABLE   REG_RESOURCE_TAG (
              REG_TAG_ID              INTEGER NOT NULL,
              REG_VERSION             INTEGER DEFAULT 0,
              REG_PATH_ID             INTEGER,
              REG_RESOURCE_NAME       VARCHAR(256),
              REG_TENANT_ID INTEGER DEFAULT 0
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG'))
  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);
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG_FK_BY_TAG_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_TAG'))
  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);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME')
  DROP INDEX REG_RESOURCE_TAG.REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_TAG(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_TAG_IND_BY_VERSION')
  DROP INDEX REG_RESOURCE_TAG.REG_RESOURCE_TAG_IND_BY_VERSION
  CREATE INDEX REG_RESOURCE_TAG_IND_BY_VERSION ON REG_RESOURCE_TAG(REG_VERSION, REG_TENANT_ID);
  
  --CREATE TABLE REG_PROPERTY
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE REG_PROPERTY (
              REG_ID        INTEGER IDENTITY(1,1) NOT NULL,
              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)
  );
  
  --CREATE TABLE REG_RESOURCE_PROPERTY
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_RESOURCE_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE  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
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY'))
  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);
  
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY_FK_BY_TAG_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_RESOURCE_PROPERTY'))
  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);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME')
  DROP INDEX REG_RESOURCE_PROPERTY.REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_RESOURCE_PROPERTY(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_RESOURCE_PROPERTY_IND_BY_VERSION')
  DROP INDEX REG_RESOURCE_PROPERTY.REG_RESOURCE_PROPERTY_IND_BY_VERSION
  CREATE INDEX REG_RESOURCE_PROPERTY_IND_BY_VERSION ON REG_RESOURCE_PROPERTY(REG_VERSION, REG_TENANT_ID);
  
  --CREATE TABLE  REG_ASSOCIATION
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_ASSOCIATION]') AND TYPE IN (N'U'))
  CREATE TABLE  REG_ASSOCIATION (
              REG_ASSOCIATION_ID  INTEGER IDENTITY(1,1) NOT NULL,
              REG_SOURCEPATH VARCHAR (2000) NOT NULL,
              REG_TARGETPATH VARCHAR (2000) NOT NULL,
              REG_ASSOCIATION_TYPE VARCHAR (2000) NOT NULL,
              REG_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (REG_ASSOCIATION_ID, REG_TENANT_ID)
  );
  
  --CREATE TABLE  REG_SNAPSHOT
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[REG_SNAPSHOT]') AND TYPE IN (N'U'))
  CREATE TABLE REG_SNAPSHOT (
              REG_SNAPSHOT_ID     INTEGER IDENTITY(1,1) NOT NULL,
              REG_PATH_ID            INTEGER NOT NULL,
              REG_RESOURCE_NAME            VARCHAR (256),
              REG_RESOURCE_VIDS     VARBINARY(MAX) NOT NULL,
              REG_TENANT_ID INTEGER DEFAULT 0,
              CONSTRAINT PK_REG_SNAPSHOT PRIMARY KEY(REG_SNAPSHOT_ID, REG_TENANT_ID)
  );
  IF NOT EXISTS (SELECT * FROM SYS.FOREIGN_KEYS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].REG_SNAPSHOT_FK_BY_PATH_ID') AND PARENT_OBJECT_ID = OBJECT_ID(N'[DBO].REG_SNAPSHOT'))
  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);
  
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME')
  DROP INDEX REG_SNAPSHOT.REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME
  CREATE INDEX REG_SNAPSHOT_IND_BY_PATH_ID_AND_RESOURCE_NAME ON REG_SNAPSHOT(REG_PATH_ID, REG_RESOURCE_NAME, REG_TENANT_ID);
  
  -- ################################
  -- USER MANAGER TABLES
  -- ################################
  
  --CREATE TABLE   UM_TENANT_
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_TENANT]') AND TYPE IN (N'U'))
  CREATE TABLE UM_TENANT (
  			UM_ID INTEGER IDENTITY(1,1) NOT NULL,
  			UM_DOMAIN_NAME VARCHAR(255) NOT NULL,
  	        UM_EMAIL VARCHAR(255),
              UM_ACTIVE BIT DEFAULT 0,
  	        UM_CREATED_DATE DATETIME NOT NULL,
  	        UM_USER_CONFIG VARBINARY(MAX),
  			PRIMARY KEY (UM_ID),
  			UNIQUE(UM_DOMAIN_NAME));
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'INDEX_UM_TENANT_UM_DOMAIN_NAME')
  DROP INDEX UM_TENANT.INDEX_UM_TENANT_UM_DOMAIN_NAME
  CREATE INDEX INDEX_UM_TENANT_UM_DOMAIN_NAME ON UM_TENANT (UM_DOMAIN_NAME);
  
  --CREATE TABLE   UM_USER
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER]') AND TYPE IN (N'U'))
  CREATE TABLE  UM_USER (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               UM_USER_NAME VARCHAR(255) NOT NULL,
               UM_USER_PASSWORD VARCHAR(255) NOT NULL,
               UM_SALT_VALUE VARCHAR(31),
               UM_REQUIRE_CHANGE BIT DEFAULT 0,
               UM_CHANGED_TIME DATETIME NOT NULL,
               UM_TENANT_ID INTEGER DEFAULT 0,
               PRIMARY KEY (UM_ID, UM_TENANT_ID),
               UNIQUE(UM_USER_NAME, UM_TENANT_ID)
  );
  
  --CREATE TABLE   UM_DOMAIN
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_DOMAIN]') AND TYPE IN (N'U'))
  CREATE TABLE UM_DOMAIN(
              UM_DOMAIN_ID INTEGER IDENTITY(1,1) NOT NULL,
              UM_DOMAIN_NAME VARCHAR(255),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_DOMAIN_ID, UM_TENANT_ID)
  );
  
  
  --CREATE TABLE   UM_SYSTEM_USER
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_SYSTEM_USER]') AND TYPE IN (N'U'))
  CREATE TABLE UM_SYSTEM_USER (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               UM_USER_NAME VARCHAR(255) NOT NULL,
               UM_USER_PASSWORD VARCHAR(255) NOT NULL,
               UM_SALT_VALUE VARCHAR(31),
               UM_REQUIRE_CHANGE  BIT DEFAULT 0,
               UM_CHANGED_TIME DATETIME NOT NULL,
               UM_TENANT_ID INTEGER DEFAULT 0,
               PRIMARY KEY (UM_ID, UM_TENANT_ID),
               UNIQUE(UM_USER_NAME, UM_TENANT_ID)
  );
  
  
  --CREATE TABLE   UM_USER_ATTRIBUTE
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER_ATTRIBUTE]') AND TYPE IN (N'U'))
  CREATE TABLE  UM_USER_ATTRIBUTE (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
  			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));
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'UM_USER_ID_INDEX')
  DROP INDEX UM_USER_ATTRIBUTE.UM_USER_ID_INDEX
  CREATE INDEX UM_USER_ID_INDEX ON UM_USER_ATTRIBUTE(UM_USER_ID);
  
  
  --CREATE TABLE   UM_ROLE
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE UM_ROLE (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               UM_ROLE_NAME VARCHAR(255) NOT NULL,
               UM_TENANT_ID INTEGER DEFAULT 0,
  		     UM_SHARED_ROLE BIT DEFAULT 0,
               PRIMARY KEY (UM_ID, UM_TENANT_ID),
               UNIQUE(UM_ROLE_NAME, UM_TENANT_ID)
  );
  
  
  --CREATES TABLE UM_MODULE
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_MODULE]') AND TYPE IN (N'U'))
  CREATE TABLE UM_MODULE(
  	UM_ID INTEGER  IDENTITY(1,1) NOT NULL,
  	UM_MODULE_NAME VARCHAR(100),
  	UNIQUE(UM_MODULE_NAME),
  	PRIMARY KEY(UM_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_MODULE_ACTIONS]') AND TYPE IN (N'U'))
  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
  );
  
  
  --CREATE TABLE UM_PERMISSION
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_PERMISSION]') AND TYPE IN (N'U'))
  CREATE TABLE  UM_PERMISSION (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               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)
  );
  
  IF EXISTS (SELECT name FROM sysindexes WHERE name = 'INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION')
  DROP INDEX UM_PERMISSION.INDEX_UM_PERMISSION_UM_RESOURCE_ID_UM_ACTION
  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
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_ROLE_PERMISSION]') AND TYPE IN (N'U'))
  CREATE TABLE  UM_ROLE_PERMISSION (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               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)
  );
  
  --CREATE TABLE UM_USER_PERMISSION
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER_PERMISSION]') AND TYPE IN (N'U'))
  CREATE TABLE  UM_USER_PERMISSION (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               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,
               UNIQUE (UM_PERMISSION_ID, UM_USER_NAME, UM_TENANT_ID),
               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)
  );
  
  -- create table UM_USER_ROLE
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_USER_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE  UM_USER_ROLE (
               UM_ID INTEGER IDENTITY(1,1) NOT NULL,
               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)
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_SHARED_USER_ROLE]') AND TYPE IN (N'U'))
  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
  );
  
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_ACCOUNT_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE UM_ACCOUNT_MAPPING(
  	UM_ID INTEGER IDENTITY(1,1),
  	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)
  );
  
  
  -- create table UM_DIALECT
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_DIALECT]') AND TYPE IN (N'U'))
  CREATE TABLE UM_DIALECT(
         	    UM_ID INTEGER IDENTITY(1, 1),
              UM_DIALECT_URI VARCHAR(255),
              UM_TENANT_ID INTEGER DEFAULT 0,
              UNIQUE(UM_DIALECT_URI, UM_TENANT_ID),
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  );
  
  -- create table UM_CLAIM
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_CLAIM]') AND TYPE IN (N'U'))
  CREATE TABLE UM_CLAIM(
         	UM_ID INTEGER IDENTITY(1, 1),
              UM_DIALECT_ID INTEGER,
              UM_CLAIM_URI VARCHAR(255),
              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)
  );
  
  -- create table UM_PROFILE_CONFIG
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_PROFILE_CONFIG]') AND TYPE IN (N'U'))
  CREATE TABLE UM_PROFILE_CONFIG(
         	UM_ID INTEGER IDENTITY(1, 1),
              UM_DIALECT_ID INTEGER,
              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)
  );
  
  -- create table UM_CLAIM_BEHAVIOR
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_CLAIM_BEHAVIOR]') AND TYPE IN (N'U'))
  CREATE TABLE UM_CLAIM_BEHAVIOR(
         	    UM_ID INTEGER IDENTITY(1, 1),
              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)
  );
  
  -- create table UM_HYBRID_ROLE
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE UM_HYBRID_ROLE(
              UM_ID INTEGER IDENTITY(1, 1),
              UM_ROLE_NAME VARCHAR(255),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  );
  
  -- create table UM_HYBRID_USER_ROLE
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_USER_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE UM_HYBRID_USER_ROLE(
              UM_ID INTEGER IDENTITY(1, 1) NOT NULL,
              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)
  );
  -- create table UM_SYSTEM_ROLE
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_SYSTEM_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE UM_SYSTEM_ROLE(
              UM_ID INTEGER IDENTITY(1, 1) NOT NULL,
              UM_ROLE_NAME VARCHAR(255),
              UM_TENANT_ID INTEGER DEFAULT 0,
              PRIMARY KEY (UM_ID, UM_TENANT_ID)
  );
  
  IF EXISTS (SELECT name FROM sysindexes WHERE name = 'SYSTEM_ROLE_IND_BY_RN_TI')
  DROP INDEX UM_SYSTEM_ROLE.SYSTEM_ROLE_IND_BY_RN_TI
  CREATE INDEX SYSTEM_ROLE_IND_BY_RN_TI ON UM_SYSTEM_ROLE(UM_ROLE_NAME, UM_TENANT_ID);
  
  -- create table UM_SYSTEM_USER_ROLE
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_SYSTEM_USER_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE UM_SYSTEM_USER_ROLE(
              UM_ID INTEGER IDENTITY(1, 1),
              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)
  );
  
  -- create table UM_HYBRID_USER_ROLE
  IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[UM_HYBRID_REMEMBER_ME]') AND TYPE IN (N'U'))
  CREATE TABLE UM_HYBRID_REMEMBER_ME(
              UM_ID INTEGER IDENTITY(1, 1),
  			UM_USER_NAME VARCHAR(255) NOT NULL,
  			UM_COOKIE_VALUE VARCHAR(1024),
  			UM_CREATED_TIME DATETIME,
              UM_TENANT_ID INTEGER DEFAULT 0,
  			PRIMARY KEY (UM_ID, UM_TENANT_ID)
  );
  GO
  
  USE WSO2AM_APIMGT_DB;
  GO
  
  -- Start of IDN Tables --
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_BASE_TABLE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_BASE_TABLE (
    PRODUCT_NAME VARCHAR(20),
    PRIMARY KEY (PRODUCT_NAME)
  );
  
  INSERT INTO IDN_BASE_TABLE values ('WSO2 Identity Server');
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH_CONSUMER_APPS]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OAUTH_CONSUMER_APPS (
    ID INTEGER IDENTITY,
    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 3600000,
    APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600000,
    REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600000,
    ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600000,
    CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
    PRIMARY KEY (ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_SCOPE_VALIDATORS]') AND TYPE IN (N'U'))
  CREATE TABLE 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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH1A_REQUEST_TOKEN]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OAUTH1A_REQUEST_TOKEN (
    REQUEST_TOKEN VARCHAR(512),
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH1A_ACCESS_TOKEN]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OAUTH1A_ACCESS_TOKEN (
    ACCESS_TOKEN VARCHAR(512),
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_ACCESS_TOKEN]') AND TYPE IN (N'U'))
  CREATE TABLE 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 DATETIME,
    REFRESH_TOKEN_TIME_CREATED DATETIME,
    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)
  );
  
  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);
  
  
  IF EXISTS (SELECT NAME FROM SYSINDEXES WHERE NAME = 'IDX_AT_CK_AU')
  DROP INDEX IDN_OAUTH2_ACCESS_TOKEN.IDX_AT_CK_AU
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_AUTHORIZATION_CODE]') AND TYPE IN (N'U'))
  CREATE TABLE 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 DATETIME,
    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
  );
  
  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);
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_ACCESS_TOKEN_SCOPE]') AND TYPE IN (N'U'))
  CREATE TABLE 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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_SCOPE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OAUTH2_SCOPE (
    SCOPE_ID INTEGER IDENTITY,
    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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_SCOPE_BINDING]') AND TYPE IN (N'U'))
  CREATE TABLE 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,
    PRIMARY KEY (SCOPE_ID, SCOPE_BINDING)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_RESOURCE_SCOPE]') AND TYPE IN (N'U'))
  CREATE TABLE 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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_SCIM_GROUP]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_SCIM_GROUP (
    ID INTEGER IDENTITY,
    TENANT_ID INTEGER NOT NULL,
    ROLE_NAME VARCHAR(255) NOT NULL,
    ATTR_NAME VARCHAR(1024) NOT NULL,
    ATTR_VALUE VARCHAR(1024),
    PRIMARY KEY (ID)
  );
  
  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);
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OPENID_REMEMBER_ME]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OPENID_REMEMBER_ME (
    USER_NAME VARCHAR(255) NOT NULL,
    TENANT_ID INTEGER DEFAULT 0,
    COOKIE_VALUE VARCHAR(1024),
    CREATED_TIME DATETIME,
    PRIMARY KEY (USER_NAME, TENANT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OPENID_USER_RPS]') AND TYPE IN (N'U'))
  CREATE TABLE 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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OPENID_ASSOCIATIONS]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OPENID_ASSOCIATIONS (
    HANDLE VARCHAR(255) NOT NULL,
    ASSOC_TYPE VARCHAR(255) NOT NULL,
    EXPIRE_IN DATETIME NOT NULL,
    MAC_KEY VARCHAR(255) NOT NULL,
    ASSOC_STORE VARCHAR(128) DEFAULT 'SHARED',
    TENANT_ID INTEGER DEFAULT -1,
    PRIMARY KEY (HANDLE)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_STS_STORE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_STS_STORE (
    ID INTEGER IDENTITY,
    TOKEN_ID VARCHAR(255) NOT NULL,
    TOKEN_CONTENT VARBINARY(MAX) NOT NULL,
    CREATE_DATE DATETIME NOT NULL,
    EXPIRE_DATE DATETIME NOT NULL,
    STATE INTEGER DEFAULT 0,
    PRIMARY KEY (ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_IDENTITY_USER_DATA]') AND TYPE IN (N'U'))
  CREATE TABLE 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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_IDENTITY_META_DATA]') AND TYPE IN (N'U'))
  CREATE TABLE 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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_THRIFT_SESSION]') AND TYPE IN (N'U'))
  CREATE TABLE 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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_SESSION_STORE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_AUTH_SESSION_STORE (
    SESSION_ID VARCHAR (100) NOT NULL,
    SESSION_TYPE VARCHAR(100) NOT NULL,
    OPERATION VARCHAR(10) NOT NULL,
    SESSION_OBJECT VARBINARY(MAX),
    TIME_CREATED BIGINT,
    TENANT_ID INTEGER DEFAULT -1,
    EXPIRY_TIME BIGINT,
    PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
  );
  
  CREATE INDEX IDX_IDN_AUTH_SESSION_STORE_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_TEMP_SESSION_STORE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_AUTH_TEMP_SESSION_STORE (
    SESSION_ID VARCHAR (100) NOT NULL,
    SESSION_TYPE VARCHAR(100) NOT NULL,
    OPERATION VARCHAR(10) NOT NULL,
    SESSION_OBJECT VARBINARY(MAX),
    TIME_CREATED BIGINT,
    TENANT_ID INTEGER DEFAULT -1,
    EXPIRY_TIME BIGINT,
    PRIMARY KEY (SESSION_ID, SESSION_TYPE, TIME_CREATED, OPERATION)
  );
  
  CREATE INDEX IDX_IDN_AUTH_TEMP_SESSION_STORE_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_APP]') AND TYPE IN (N'U'))
  CREATE TABLE SP_APP (
    ID INTEGER NOT NULL IDENTITY,
    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),
    CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID)
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_METADATA]') AND TYPE IN (N'U'))
  CREATE TABLE SP_METADATA (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_INBOUND_AUTH]') AND TYPE IN (N'U'))
  CREATE TABLE SP_INBOUND_AUTH (
    ID INTEGER NOT NULL IDENTITY,
    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),
    CONSTRAINT APPLICATION_ID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_AUTH_STEP]') AND TYPE IN (N'U'))
  CREATE TABLE SP_AUTH_STEP (
    ID INTEGER NOT NULL IDENTITY,
    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),
    CONSTRAINT APPLICATION_ID_CONSTRAINT_STEP FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_FEDERATED_IDP]') AND TYPE IN (N'U'))
  CREATE TABLE SP_FEDERATED_IDP (
    ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    AUTHENTICATOR_ID INTEGER NOT NULL,
    PRIMARY KEY (ID, AUTHENTICATOR_ID),
    CONSTRAINT STEP_ID_CONSTRAINT FOREIGN KEY (ID) REFERENCES SP_AUTH_STEP (ID) ON DELETE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_CLAIM_DIALECT]') AND TYPE IN (N'U'))
  CREATE TABLE SP_CLAIM_DIALECT (
    ID INTEGER NOT NULL IDENTITY,
    TENANT_ID INTEGER NOT NULL,
    SP_DIALECT VARCHAR (512) NOT NULL,
    APP_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT DIALECTID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_CLAIM_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE SP_CLAIM_MAPPING (
    ID INTEGER NOT NULL IDENTITY,
    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),
    CONSTRAINT CLAIMID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_ROLE_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE SP_ROLE_MAPPING (
    ID INTEGER NOT NULL IDENTITY,
    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),
    CONSTRAINT ROLEID_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_REQ_PATH_AUTHENTICATOR]') AND TYPE IN (N'U'))
  CREATE TABLE SP_REQ_PATH_AUTHENTICATOR (
    ID INTEGER NOT NULL IDENTITY,
    TENANT_ID INTEGER NOT NULL,
    AUTHENTICATOR_NAME VARCHAR (255) NOT NULL ,
    APP_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT REQ_AUTH_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_PROVISIONING_CONNECTOR]') AND TYPE IN (N'U'))
  CREATE TABLE SP_PROVISIONING_CONNECTOR (
    ID INTEGER NOT NULL IDENTITY,
    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),
    CONSTRAINT PRO_CONNECTOR_APPID_CONSTRAINT FOREIGN KEY (APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
  );
  
  IF NOT EXISTS(SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_AUTH_SCRIPT]') AND TYPE IN (N'U'))
  CREATE TABLE SP_AUTH_SCRIPT (
    ID         INTEGER IDENTITY NOT NULL,
    TENANT_ID  INTEGER          NOT NULL,
    APP_ID     INTEGER          NOT NULL,
    TYPE       VARCHAR(255)     NOT NULL,
    CONTENT    VARBINARY(MAX)    DEFAULT NULL,
    IS_ENABLED CHAR(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP]') AND TYPE IN (N'U'))
  CREATE TABLE IDP (
    ID INTEGER IDENTITY,
    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 VARBINARY(MAX),
    CERTIFICATE VARBINARY(MAX),
    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',
    PRIMARY KEY (ID),
    DISPLAY_NAME VARCHAR(255),
    UNIQUE (TENANT_ID, NAME)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_ROLE]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_ROLE (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_ROLE_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_ROLE_MAPPING (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_CLAIM]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_CLAIM (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_CLAIM_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_CLAIM_MAPPING (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_AUTHENTICATOR]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_AUTHENTICATOR (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_METADATA]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_METADATA (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_AUTHENTICATOR_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_AUTHENTICATOR_PROPERTY (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_PROVISIONING_CONFIG]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_PROVISIONING_CONFIG (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_PROV_CONFIG_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_PROV_CONFIG_PROPERTY (
    ID INTEGER IDENTITY,
    TENANT_ID INTEGER,
    PROVISIONING_CONFIG_ID INTEGER,
    PROPERTY_KEY VARCHAR(255) NOT NULL,
    PROPERTY_VALUE VARCHAR(2048),
    PROPERTY_BLOB_VALUE VARBINARY(MAX),
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_PROVISIONING_ENTITY]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_PROVISIONING_ENTITY (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDP_LOCAL_CLAIM]') AND TYPE IN (N'U'))
  CREATE TABLE IDP_LOCAL_CLAIM (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_ASSOCIATED_ID]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_ASSOCIATED_ID (
    ID INTEGER IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_USER_ACCOUNT_ASSOCIATION]') AND TYPE IN (N'U'))
  CREATE TABLE 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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[FIDO_DEVICE_STORE]') AND TYPE IN (N'U'))
  CREATE TABLE FIDO_DEVICE_STORE (
    TENANT_ID INTEGER,
    DOMAIN_NAME VARCHAR(255) NOT NULL,
    USER_NAME VARCHAR(45) NOT NULL,
    TIME_REGISTERED DATETIME,
    KEY_HANDLE VARCHAR(200) NOT NULL,
    DEVICE_DATA VARCHAR(2048) NOT NULL,
    PRIMARY KEY (TENANT_ID, DOMAIN_NAME, USER_NAME, KEY_HANDLE)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_REQUEST]') AND TYPE IN (N'U'))
  CREATE TABLE WF_REQUEST (
    UUID VARCHAR (45),
    CREATED_BY VARCHAR (255),
    TENANT_ID INTEGER DEFAULT -1,
    OPERATION_TYPE VARCHAR (50),
    CREATED_AT DATETIME,
    UPDATED_AT DATETIME,
    STATUS VARCHAR (30),
    REQUEST VARBINARY(MAX),
    PRIMARY KEY (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_BPS_PROFILE]') AND TYPE IN (N'U'))
  CREATE TABLE 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),
    TENANT_ID INTEGER DEFAULT -1,
    PRIMARY KEY (PROFILE_NAME, TENANT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_WORKFLOW]') AND TYPE IN (N'U'))
  CREATE TABLE 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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_WORKFLOW_ASSOCIATION]') AND TYPE IN (N'U'))
  CREATE TABLE WF_WORKFLOW_ASSOCIATION(
    ID INTEGER NOT NULL IDENTITY ,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_WORKFLOW_CONFIG_PARAM]') AND TYPE IN (N'U'))
  CREATE TABLE 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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_REQUEST_ENTITY_RELATIONSHIP]') AND TYPE IN (N'U'))
  CREATE TABLE 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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[WF_WORKFLOW_REQUEST_RELATION]') AND TYPE IN (N'U'))
  CREATE TABLE WF_WORKFLOW_REQUEST_RELATION(
    RELATIONSHIP_ID VARCHAR (45),
    WORKFLOW_ID VARCHAR (45),
    REQUEST_ID VARCHAR (45),
    UPDATED_AT DATETIME,
    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
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_RECOVERY_DATA]') AND TYPE IN (N'U'))
  CREATE TABLE 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 DATETIME NOT NULL,
    REMAINING_SETS VARCHAR(2500) DEFAULT NULL,
    PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
    UNIQUE(CODE)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_PASSWORD_HISTORY_DATA]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_PASSWORD_HISTORY_DATA (
    ID INTEGER NOT NULL IDENTITY ,
    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 DATETIME NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH),
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_DIALECT]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_DIALECT (
    ID INTEGER NOT NULL IDENTITY,
    DIALECT_URI VARCHAR (255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM (
    ID INTEGER NOT NULL IDENTITY,
    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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_MAPPED_ATTRIBUTE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_MAPPED_ATTRIBUTE (
    ID INTEGER NOT NULL IDENTITY,
    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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_PROPERTY (
    ID INTEGER NOT NULL IDENTITY,
    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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_MAPPING (
    ID INTEGER NOT NULL IDENTITY,
    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 NO ACTION ,
    CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_SAML2_ASSERTION_STORE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_SAML2_ASSERTION_STORE (
    ID INTEGER NOT NULL IDENTITY,
    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)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OIDC_JTI]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OIDC_JTI  (
    JWT_ID VARCHAR(255) NOT NULL,
    EXP_TIME DATETIME NOT NULL,
    TIME_CREATED DATETIME NOT NULL,
    PRIMARY KEY (JWT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OIDC_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OIDC_PROPERTY (
    ID INTEGER NOT NULL IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OIDC_REQ_OBJECT_REFERENCE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OIDC_REQ_OBJECT_REFERENCE (
    ID INTEGER NOT NULL IDENTITY,
    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),
    FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OIDC_REQ_OBJECT_CLAIMS]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OIDC_REQ_OBJECT_CLAIMS (
    ID INTEGER NOT NULL IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OIDC_REQ_OBJ_CLAIM_VALUES]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
    ID INTEGER NOT NULL IDENTITY,
    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
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CERTIFICATE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CERTIFICATE (
               ID INTEGER IDENTITY,
               NAME VARCHAR(100),
               CERTIFICATE_IN_PEM VARBINARY(MAX),
               TENANT_ID INTEGER DEFAULT 0,
               PRIMARY KEY(ID),
               CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID)
  );
  
  -- Start of CONSENT-MGT Tables --
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_PII_CATEGORY]') AND TYPE IN (N'U'))
  CREATE TABLE CM_PII_CATEGORY (
    ID           INTEGER      NOT NULL  IDENTITY,
    NAME         VARCHAR(255) NOT NULL,
    DESCRIPTION  VARCHAR(1023),
    DISPLAY_NAME VARCHAR(255),
    IS_SENSITIVE INTEGER      NOT NULL,
    TENANT_ID    INTEGER DEFAULT '-1234',
    CONSTRAINT CM_PII_CATEGORY_CNT UNIQUE (NAME, TENANT_ID),
    PRIMARY KEY (ID)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_RECEIPT]') AND TYPE IN (N'U'))
  CREATE TABLE CM_RECEIPT (
    CONSENT_RECEIPT_ID  VARCHAR(255) NOT NULL,
    VERSION             VARCHAR(255) NOT NULL,
    JURISDICTION        VARCHAR(255) NOT NULL,
    CONSENT_TIMESTAMP   DATETIME    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)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_PURPOSE]') AND TYPE IN (N'U'))
  CREATE TABLE CM_PURPOSE (
    ID          INTEGER      NOT NULL  IDENTITY,
    NAME        VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023),
    TENANT_ID   INTEGER DEFAULT '-1234',
    CONSTRAINT CM_PURPOSE_CNT UNIQUE (NAME, TENANT_ID),
    PRIMARY KEY (ID)
  );
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_PURPOSE_CATEGORY]') AND TYPE IN (N'U'))
  CREATE TABLE CM_PURPOSE_CATEGORY (
    ID          INTEGER      NOT NULL  IDENTITY,
    NAME        VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023),
    TENANT_ID   INTEGER DEFAULT '-1234',
    CONSTRAINT CM_PURPOSE_CATEGORY_CNT UNIQUE (NAME, TENANT_ID),
    PRIMARY KEY (ID)
  );
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_RECEIPT_SP_ASSOC]') AND TYPE IN (N'U'))
  CREATE TABLE CM_RECEIPT_SP_ASSOC (
    ID                 INTEGER      NOT NULL  IDENTITY,
    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',
    CONSTRAINT CM_RECEIPT_SP_ASSOC_CNT UNIQUE (CONSENT_RECEIPT_ID, SP_NAME, SP_TENANT_ID),
    FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID),
    PRIMARY KEY (ID)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_SP_PURPOSE_ASSOC]') AND TYPE IN (N'U'))
  CREATE TABLE CM_SP_PURPOSE_ASSOC (
    ID                     INTEGER      NOT NULL  IDENTITY,
    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),
    CONSTRAINT CM_SP_PURPOSE_ASSOC_CNT UNIQUE (RECEIPT_SP_ASSOC, PURPOSE_ID),
    FOREIGN KEY (RECEIPT_SP_ASSOC) REFERENCES CM_RECEIPT_SP_ASSOC (ID),
    FOREIGN KEY (PURPOSE_ID) REFERENCES CM_PURPOSE (ID),
    PRIMARY KEY (ID)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_SP_PURPOSE_PURPOSE_CAT_ASSC]') AND TYPE IN (N'U'))
  CREATE TABLE CM_SP_PURPOSE_PURPOSE_CAT_ASSC (
    SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
    PURPOSE_CATEGORY_ID INTEGER NOT NULL,
    CONSTRAINT CM_SP_PURPOSE_PURPOSE_CAT_ASSC_CNT UNIQUE (SP_PURPOSE_ASSOC_ID, PURPOSE_CATEGORY_ID),
    FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID),
    FOREIGN KEY (PURPOSE_CATEGORY_ID) REFERENCES CM_PURPOSE_CATEGORY (ID)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_PURPOSE_PII_CAT_ASSOC]') AND TYPE IN (N'U'))
  CREATE TABLE CM_PURPOSE_PII_CAT_ASSOC (
    PURPOSE_ID         INTEGER NOT NULL,
    CM_PII_CATEGORY_ID INTEGER NOT NULL,
    CONSTRAINT CM_PURPOSE_PII_CAT_ASSOC_CNT UNIQUE (PURPOSE_ID, CM_PII_CATEGORY_ID)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_SP_PURPOSE_PII_CAT_ASSOC]') AND TYPE IN (N'U'))
  CREATE TABLE CM_SP_PURPOSE_PII_CAT_ASSOC (
    SP_PURPOSE_ASSOC_ID INTEGER NOT NULL,
    PII_CATEGORY_ID     INTEGER NOT NULL,
    VALIDITY            VARCHAR(1023),
    CONSTRAINT CM_SP_PURPOSE_PII_CAT_ASSOC_CNT UNIQUE (SP_PURPOSE_ASSOC_ID, PII_CATEGORY_ID),
    FOREIGN KEY (PII_CATEGORY_ID) REFERENCES CM_PII_CATEGORY (ID),
    FOREIGN KEY (SP_PURPOSE_ASSOC_ID) REFERENCES CM_SP_PURPOSE_ASSOC (ID)
  );
  
  IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[CM_CONSENT_RECEIPT_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE CM_CONSENT_RECEIPT_PROPERTY (
    CONSENT_RECEIPT_ID VARCHAR(255)  NOT NULL,
    NAME               VARCHAR(255)  NOT NULL,
    VALUE              VARCHAR(1023) NOT NULL,
    CONSTRAINT CM_CONSENT_RECEIPT_PROPERTY_CNT UNIQUE (CONSENT_RECEIPT_ID, NAME),
    FOREIGN KEY (CONSENT_RECEIPT_ID) REFERENCES CM_RECEIPT (CONSENT_RECEIPT_ID)
  );
  
  INSERT INTO CM_PURPOSE (NAME, DESCRIPTION, TENANT_ID) values ('DEFAULT', 'For core functionalities of the product', '-1234');
  
  INSERT INTO CM_PURPOSE_CATEGORY (NAME, DESCRIPTION, TENANT_ID) VALUES ('DEFAULT','For core functionalities of the product', '-1234');
  
  -- End of CONSENT-MGT Tables --
  
  -- Start of API-Mgt Tables --
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SUBSCRIBER]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_SUBSCRIBER (
      SUBSCRIBER_ID INTEGER IDENTITY(1,1),
      USER_ID VARCHAR(50) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      EMAIL_ADDRESS VARCHAR(256) NULL,
      DATE_SUBSCRIBED DATETIME NOT NULL,
      CREATED_BY VARCHAR(100),
      CREATED_TIME DATETIME,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME DATETIME,
      PRIMARY KEY (SUBSCRIBER_ID),
      UNIQUE (TENANT_ID,USER_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_APPLICATION]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_APPLICATION (
      APPLICATION_ID INTEGER IDENTITY(1,1),
      NAME VARCHAR(100) COLLATE Latin1_General_CS_AS,
      SUBSCRIBER_ID INTEGER,
      APPLICATION_TIER VARCHAR(50) DEFAULT 'Unlimited',
      CALLBACK_URL VARCHAR(512),
      DESCRIPTION VARCHAR(512),
      APPLICATION_STATUS VARCHAR(50) DEFAULT 'APPROVED',
      GROUP_ID VARCHAR(100),
      CREATED_BY VARCHAR(100),
      CREATED_TIME DATETIME,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME DATETIME,
      UUID VARCHAR(256),
      TOKEN_TYPE VARCHAR(10),
      FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON UPDATE CASCADE,
      PRIMARY KEY(APPLICATION_ID),
      UNIQUE (NAME,SUBSCRIBER_ID),
      UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API (
      API_ID INTEGER IDENTITY(1,1),
      API_PROVIDER VARCHAR(200),
      API_NAME VARCHAR(200),
      API_VERSION VARCHAR(30),
      CONTEXT VARCHAR(256),
      CONTEXT_TEMPLATE VARCHAR(256),
      API_TIER VARCHAR(256),
      CREATED_BY VARCHAR(100),
      CREATED_TIME DATETIME,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME DATETIME,
      PRIMARY KEY(API_ID),
      UNIQUE (API_PROVIDER,API_NAME,API_VERSION)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_URL_MAPPING]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API_URL_MAPPING (
      URL_MAPPING_ID INTEGER IDENTITY(1,1),
      API_ID INTEGER NOT NULL,
      HTTP_METHOD VARCHAR(20) NULL,
      AUTH_SCHEME VARCHAR(50) NULL,
      URL_PATTERN VARCHAR(512) NULL,
      THROTTLING_TIER varchar(512) DEFAULT NULL,
      MEDIATION_SCRIPT VARBINARY(MAX),
      PRIMARY KEY (URL_MAPPING_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SUBSCRIPTION]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_SUBSCRIPTION (
      SUBSCRIPTION_ID INTEGER IDENTITY(1,1),
      TIER_ID VARCHAR(50),
      API_ID INTEGER,
      LAST_ACCESSED DATETIME NULL,
      APPLICATION_ID INTEGER,
      SUB_STATUS VARCHAR(50),
      SUBS_CREATE_STATE VARCHAR(50) DEFAULT 'SUBSCRIBE',
      CREATED_BY VARCHAR(100),
      CREATED_TIME DATETIME,
      UPDATED_BY VARCHAR(100),
      UPDATED_TIME DATETIME,
      UUID VARCHAR(256),
      FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE,
      PRIMARY KEY (SUBSCRIPTION_ID),
      UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SUBSCRIPTION_KEY_MAPPING]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_SUBSCRIPTION_KEY_MAPPING (
      SUBSCRIPTION_ID INTEGER,
      ACCESS_TOKEN VARCHAR(512),
      KEY_TYPE VARCHAR(512) NOT NULL,
      FOREIGN KEY(SUBSCRIPTION_ID) REFERENCES AM_SUBSCRIPTION(SUBSCRIPTION_ID) ON UPDATE CASCADE,
      PRIMARY KEY(SUBSCRIPTION_ID,ACCESS_TOKEN)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_APPLICATION_KEY_MAPPING]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_APPLICATION_KEY_MAPPING (
      APPLICATION_ID INTEGER,
      CONSUMER_KEY VARCHAR(512),
      KEY_TYPE VARCHAR(512) NOT NULL,
      STATE VARCHAR(30) NOT NULL,
      CREATE_MODE VARCHAR(30) DEFAULT 'CREATED',
      FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE,
      PRIMARY KEY(APPLICATION_ID,KEY_TYPE)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_APPLICATION_REGISTRATION]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_APPLICATION_REGISTRATION (
      REG_ID INTEGER IDENTITY(1,1),
      SUBSCRIBER_ID INTEGER,
      WF_REF VARCHAR(255) NOT NULL,
      APP_ID INTEGER,
      TOKEN_TYPE VARCHAR(30),
      TOKEN_SCOPE VARCHAR(1500) DEFAULT 'default',
      INPUTS VARCHAR(1000),
      ALLOWED_DOMAINS VARCHAR(256),
      VALIDITY_PERIOD BIGINT,
      UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE),
      FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON DELETE NO ACTION,
      FOREIGN KEY(APP_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE ON DELETE NO ACTION,
      PRIMARY KEY (REG_ID)
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_LC_EVENT]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API_LC_EVENT (
      EVENT_ID INTEGER IDENTITY(1,1),
      API_ID INTEGER NOT NULL,
      PREVIOUS_STATE VARCHAR(50),
      NEW_STATE VARCHAR(50) NOT NULL,
      USER_ID VARCHAR(50) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      EVENT_DATE DATETIME NOT NULL,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE,
      PRIMARY KEY (EVENT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_APP_KEY_DOMAIN_MAPPING]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_APP_KEY_DOMAIN_MAPPING (
     CONSUMER_KEY VARCHAR(512),
     AUTHZ_DOMAIN VARCHAR(255) DEFAULT 'ALL',
     PRIMARY KEY (CONSUMER_KEY,AUTHZ_DOMAIN)
  
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_COMMENTS]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API_COMMENTS (
      COMMENT_ID INTEGER IDENTITY(1,1),
      COMMENT_TEXT VARCHAR(512),
      COMMENTED_USER VARCHAR(255),
      DATE_COMMENTED DATETIME NOT NULL,
      API_ID INTEGER NOT NULL,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE,
      PRIMARY KEY (COMMENT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_RATINGS]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API_RATINGS (
      RATING_ID INTEGER IDENTITY(1,1),
      API_ID INTEGER,
      RATING INTEGER,
      SUBSCRIBER_ID INTEGER,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE,
      FOREIGN KEY(SUBSCRIBER_ID) REFERENCES AM_SUBSCRIBER(SUBSCRIBER_ID) ON UPDATE CASCADE,
      PRIMARY KEY (RATING_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_TIER_PERMISSIONS]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_TIER_PERMISSIONS (
      TIER_PERMISSIONS_ID INTEGER IDENTITY(1,1),
      TIER VARCHAR(50) NOT NULL,
      PERMISSIONS_TYPE VARCHAR(50) NOT NULL,
      ROLES VARCHAR(512) NOT NULL,
      TENANT_ID INTEGER NOT NULL,
      PRIMARY KEY(TIER_PERMISSIONS_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_EXTERNAL_STORES]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_EXTERNAL_STORES (
      APISTORE_ID INTEGER IDENTITY(1,1),
      API_ID INTEGER,
      STORE_ID VARCHAR(255) NOT NULL,
      STORE_DISPLAY_NAME VARCHAR(255) NOT NULL,
      STORE_ENDPOINT VARCHAR(255) NOT NULL,
      STORE_TYPE VARCHAR(255) NOT NULL,
      FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE,
      PRIMARY KEY (APISTORE_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_WORKFLOWS]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_WORKFLOWS(
      WF_ID INTEGER IDENTITY(1,1),
      WF_REFERENCE VARCHAR(255) NOT NULL,
      WF_TYPE VARCHAR(255) NOT NULL,
      WF_STATUS VARCHAR(255) NOT NULL,
      WF_CREATED_TIME DATETIME DEFAULT GETDATE(),
      WF_UPDATED_TIME DATETIME DEFAULT GETDATE(),
      WF_STATUS_DESC VARCHAR(1000),
      TENANT_ID INTEGER,
      TENANT_DOMAIN VARCHAR(255),
      WF_EXTERNAL_REFERENCE VARCHAR(255) NOT NULL UNIQUE,
      PRIMARY KEY (WF_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_SCOPES]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API_SCOPES (
     API_ID  INTEGER NOT NULL,
     SCOPE_ID  INTEGER NOT NULL,
     FOREIGN KEY (API_ID) REFERENCES AM_API (API_ID) ON DELETE CASCADE ON UPDATE CASCADE,
     FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE (SCOPE_ID) ON DELETE CASCADE ON UPDATE CASCADE,
     PRIMARY KEY (API_ID, SCOPE_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_DEFAULT_VERSION]') AND TYPE IN (N'U'))
  
  CREATE TABLE AM_API_DEFAULT_VERSION (
              DEFAULT_VERSION_ID INTEGER NOT NULL IDENTITY,
              API_NAME VARCHAR(256) NOT NULL ,
              API_PROVIDER VARCHAR(256) NOT NULL ,
              DEFAULT_API_VERSION VARCHAR(30) ,
              PUBLISHED_DEFAULT_API_VERSION VARCHAR(30) ,
              PRIMARY KEY (DEFAULT_VERSION_ID)
  );
  
  
  CREATE INDEX IDX_SUB_APP_ID ON AM_SUBSCRIPTION (APPLICATION_ID, SUBSCRIPTION_ID);
  
  
  
  CREATE TABLE  AM_ALERT_TYPES (
              ALERT_TYPE_ID INTEGER NOT NULL IDENTITY,
              ALERT_TYPE_NAME VARCHAR(255) NOT NULL ,
  	          STAKE_HOLDER VARCHAR(10) NOT NULL,
              PRIMARY KEY (ALERT_TYPE_ID)
  );
  
  CREATE TABLE  AM_ALERT_TYPES_VALUES (
                ALERT_TYPE_ID INTEGER,
                USER_NAME VARCHAR(255) NOT NULL ,
  	      STAKE_HOLDER VARCHAR(100) NOT NULL ,
  	      CONSTRAINT AM_ALERT_TYPES_VALUES_CONST UNIQUE (ALERT_TYPE_ID,USER_NAME,STAKE_HOLDER)
  );
  
  CREATE TABLE AM_ALERT_EMAILLIST (
  	     EMAIL_LIST_ID INTEGER NOT NULL IDENTITY,
               USER_NAME VARCHAR(255) NOT NULL ,
  	     STAKE_HOLDER VARCHAR(100) NOT NULL ,
  	     CONSTRAINT AM_ALERT_EMAILLIST_CONST UNIQUE (EMAIL_LIST_ID,USER_NAME,STAKE_HOLDER),
               PRIMARY KEY (EMAIL_LIST_ID)
  );
  
  CREATE TABLE  AM_ALERT_EMAILLIST_DETAILS (
                EMAIL_LIST_ID INTEGER,
  	      EMAIL VARCHAR(255),
  	      CONSTRAINT AM_ALERT_EMAILLIST_DETAILS_CONST UNIQUE (EMAIL_LIST_ID,EMAIL)
  );
  
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('abnormalResponseTime', 'publisher');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('abnormalBackendTime', 'publisher');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('abnormalRequestsPerMin', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('RequestPatternChanged', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('UnusualIPAccessAlert', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('FrequentTierHittingAlert', 'subscriber');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('AbnormalTierUsage', 'publisher');
  INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('healthAvailabilityPerMin', 'publisher');
  
  
  
  -- AM Throttling tables --
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_POLICY_SUBSCRIPTION]') AND TYPE IN (N'U'))
  CREATE TABLE AM_POLICY_SUBSCRIPTION (
              POLICY_ID INTEGER IDENTITY(1,1),
              NAME VARCHAR(512) NOT NULL,
              DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
              TENANT_ID INTEGER NOT NULL,
              DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
              QUOTA_TYPE VARCHAR(25) NOT NULL,
              QUOTA INTEGER NOT NULL,
              QUOTA_UNIT VARCHAR(10) NULL,
              UNIT_TIME INTEGER NOT NULL,
              TIME_UNIT VARCHAR(25) NOT NULL,
              RATE_LIMIT_COUNT INTEGER NULL DEFAULT NULL,
              RATE_LIMIT_TIME_UNIT VARCHAR(25) NULL DEFAULT NULL,
              IS_DEPLOYED BIT NOT NULL DEFAULT 0,
  			CUSTOM_ATTRIBUTES VARBINARY(MAX) DEFAULT NULL,
              STOP_ON_QUOTA_REACH BIT NOT NULL DEFAULT 0,
              BILLING_PLAN VARCHAR(20) NOT NULL,
              UUID VARCHAR(256),
              PRIMARY KEY (POLICY_ID),
              UNIQUE (NAME, TENANT_ID),
              UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_POLICY_APPLICATION]') AND TYPE IN (N'U'))
  CREATE TABLE AM_POLICY_APPLICATION (
              POLICY_ID INTEGER IDENTITY(1,1),
              NAME VARCHAR(512) NOT NULL,
              DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
              TENANT_ID INTEGER NOT NULL,
              DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
              QUOTA_TYPE VARCHAR(25) NOT NULL,
              QUOTA INTEGER NOT NULL,
              QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
              UNIT_TIME INTEGER NOT NULL,
              TIME_UNIT VARCHAR(25) NOT NULL,
              IS_DEPLOYED BIT NOT NULL DEFAULT 0,
  			CUSTOM_ATTRIBUTES VARBINARY(MAX) DEFAULT NULL,
  			      UUID VARCHAR(256),
              PRIMARY KEY (POLICY_ID),
              UNIQUE  (NAME, TENANT_ID),
              UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_POLICY_HARD_THROTTLING]') AND TYPE IN (N'U'))
  CREATE TABLE AM_POLICY_HARD_THROTTLING (
              POLICY_ID INTEGER IDENTITY(1,1),
              NAME VARCHAR(512) NOT NULL,
              TENANT_ID INTEGER NOT NULL,
              DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
              QUOTA_TYPE VARCHAR(25) NOT NULL,
              QUOTA INTEGER NOT NULL,
              QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
              UNIT_TIME INTEGER NOT NULL,
              TIME_UNIT VARCHAR(25) NOT NULL,
              IS_DEPLOYED BIT NOT NULL DEFAULT 0,
              PRIMARY KEY (POLICY_ID),
              UNIQUE  (NAME, TENANT_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_THROTTLE_POLICY]') AND TYPE IN (N'U'))
  CREATE TABLE AM_API_THROTTLE_POLICY (
              POLICY_ID INTEGER IDENTITY(1,1),
              NAME VARCHAR(512) NOT NULL,
              DISPLAY_NAME VARCHAR(512) NULL DEFAULT NULL,
              TENANT_ID INTEGER NOT NULL,
              DESCRIPTION VARCHAR (1024),
              DEFAULT_QUOTA_TYPE VARCHAR(25) NOT NULL,
              DEFAULT_QUOTA INTEGER NOT NULL,
              DEFAULT_QUOTA_UNIT VARCHAR(10) NULL,
              DEFAULT_UNIT_TIME INTEGER NOT NULL,
              DEFAULT_TIME_UNIT VARCHAR(25) NOT NULL,
              APPLICABLE_LEVEL VARCHAR(25) NOT NULL,
              IS_DEPLOYED BIT NOT NULL DEFAULT 0,
              UUID VARCHAR(256),
              PRIMARY KEY (POLICY_ID),
              UNIQUE  (NAME, TENANT_ID),
              UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_CONDITION_GROUP]') AND TYPE IN (N'U'))
  CREATE TABLE AM_CONDITION_GROUP (
              CONDITION_GROUP_ID INTEGER IDENTITY(1,1),
              POLICY_ID INTEGER NOT NULL,
              QUOTA_TYPE VARCHAR(25),
              QUOTA INTEGER NOT NULL,
              QUOTA_UNIT VARCHAR(10) NULL DEFAULT NULL,
              UNIT_TIME INTEGER NOT NULL,
              TIME_UNIT VARCHAR(25) NOT NULL,
              DESCRIPTION VARCHAR (1024) NULL DEFAULT NULL,
              PRIMARY KEY (CONDITION_GROUP_ID),
              FOREIGN KEY (POLICY_ID) REFERENCES AM_API_THROTTLE_POLICY(POLICY_ID) ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_QUERY_PARAMETER_CONDITION]') AND TYPE IN (N'U'))
  CREATE TABLE AM_QUERY_PARAMETER_CONDITION (
              QUERY_PARAMETER_ID INTEGER IDENTITY(1,1),
              CONDITION_GROUP_ID INTEGER NOT NULL,
              PARAMETER_NAME VARCHAR(255) DEFAULT NULL,
              PARAMETER_VALUE VARCHAR(255) DEFAULT NULL,
  	    	IS_PARAM_MAPPING BIT DEFAULT 1,
              PRIMARY KEY (QUERY_PARAMETER_ID),
              FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_HEADER_FIELD_CONDITION]') AND TYPE IN (N'U'))
  CREATE TABLE AM_HEADER_FIELD_CONDITION (
              HEADER_FIELD_ID INTEGER IDENTITY(1,1),
              CONDITION_GROUP_ID INTEGER NOT NULL,
              HEADER_FIELD_NAME VARCHAR(255) DEFAULT NULL,
              HEADER_FIELD_VALUE VARCHAR(255) DEFAULT NULL,
  	    	IS_HEADER_FIELD_MAPPING BIT DEFAULT 1,
              PRIMARY KEY (HEADER_FIELD_ID),
              FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_JWT_CLAIM_CONDITION]') AND TYPE IN (N'U'))
  CREATE TABLE AM_JWT_CLAIM_CONDITION (
              JWT_CLAIM_ID INTEGER IDENTITY(1,1),
              CONDITION_GROUP_ID INTEGER NOT NULL,
              CLAIM_URI VARCHAR(512) DEFAULT NULL,
              CLAIM_ATTRIB VARCHAR(1024) DEFAULT NULL,
  	    IS_CLAIM_MAPPING BIT DEFAULT 1,
              PRIMARY KEY (JWT_CLAIM_ID),
              FOREIGN KEY (CONDITION_GROUP_ID) REFERENCES AM_CONDITION_GROUP(CONDITION_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_IP_CONDITION]') AND TYPE IN (N'U'))
  CREATE TABLE AM_IP_CONDITION (
    AM_IP_CONDITION_ID INTEGER IDENTITY(1,1),
    STARTING_IP VARCHAR(45) NULL,
    ENDING_IP VARCHAR(45) NULL,
    SPECIFIC_IP VARCHAR(45) NULL,
    WITHIN_IP_RANGE BIT DEFAULT 1,
    CONDITION_GROUP_ID INT NULL,
    PRIMARY KEY (AM_IP_CONDITION_ID),
    FOREIGN KEY (CONDITION_GROUP_ID)
      REFERENCES AM_CONDITION_GROUP (CONDITION_GROUP_ID)   ON DELETE CASCADE ON UPDATE CASCADE);
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_POLICY_GLOBAL]') AND TYPE IN (N'U'))
  CREATE TABLE AM_POLICY_GLOBAL (
              POLICY_ID INTEGER IDENTITY(1,1),
              NAME VARCHAR(512) NOT NULL,
              KEY_TEMPLATE VARCHAR(512) NOT NULL,
              TENANT_ID INTEGER NOT NULL,
              DESCRIPTION VARCHAR(1024) NULL DEFAULT NULL,
              SIDDHI_QUERY VARBINARY(MAX) DEFAULT NULL,
              IS_DEPLOYED BIT NOT NULL DEFAULT 0,
              UUID VARCHAR(256),
              PRIMARY KEY (POLICY_ID),
              UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_THROTTLE_TIER_PERMISSIONS]') AND TYPE IN (N'U'))
  CREATE TABLE AM_THROTTLE_TIER_PERMISSIONS (
    THROTTLE_TIER_PERMISSIONS_ID INTEGER IDENTITY(1,1),
    TIER VARCHAR(50) NULL,
    PERMISSIONS_TYPE VARCHAR(50) NULL,
    ROLES VARCHAR(512) NULL,
    TENANT_ID INTEGER NULL,
    PRIMARY KEY (THROTTLE_TIER_PERMISSIONS_ID));
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_BLOCK_CONDITIONS]') AND TYPE IN (N'U'))
  CREATE TABLE AM_BLOCK_CONDITIONS (
    CONDITION_ID INTEGER IDENTITY(1,1),
    TYPE varchar(45) DEFAULT NULL,
    VALUE varchar(512) DEFAULT NULL,
    ENABLED varchar(45) DEFAULT NULL,
    DOMAIN varchar(45) DEFAULT NULL,
    UUID VARCHAR(256),
    PRIMARY KEY (CONDITION_ID),
    UNIQUE (UUID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_CERTIFICATE_METADATA]') AND TYPE IN (N'U'))
  CREATE TABLE AM_CERTIFICATE_METADATA (
    TENANT_ID INTEGER NOT NULL,
    ALIAS VARCHAR(45) NOT NULL,
    END_POINT VARCHAR(100) NOT NULL,
    CONSTRAINT PK_ALIAS PRIMARY KEY (ALIAS),
    CONSTRAINT END_POINT_CONSTRAINT UNIQUE (END_POINT)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_APPLICATION_GROUP_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE AM_APPLICATION_GROUP_MAPPING (
      APPLICATION_ID INTEGER NOT NULL,
      GROUP_ID VARCHAR(512),
      TENANT VARCHAR(255),
      PRIMARY KEY (APPLICATION_ID,GROUP_ID,TENANT),
      FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON DELETE CASCADE ON UPDATE CASCADE
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_USAGE_UPLOADED_FILES]') AND TYPE IN (N'U'))
  CREATE TABLE AM_USAGE_UPLOADED_FILES (
    TENANT_DOMAIN VARCHAR(255) NOT NULL,
    FILE_NAME VARCHAR(255) NOT NULL,
    FILE_TIMESTAMP DATETIME DEFAULT GETDATE(),
    FILE_PROCESSED INTEGER DEFAULT 0,
    FILE_CONTENT VARBINARY(MAX) DEFAULT NULL,
    PRIMARY KEY (TENANT_DOMAIN, FILE_NAME, FILE_TIMESTAMP)
  );
  
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_LC_PUBLISH_EVENTS]') AND TYPE IN (N'U'))
  CREATE TABLE AM_API_LC_PUBLISH_EVENTS (
      ID INTEGER NOT NULL IDENTITY,
      TENANT_DOMAIN VARCHAR(255) NOT NULL,
      API_ID VARCHAR(500) NOT NULL,
      EVENT_TIME DATETIME DEFAULT GETDATE(),
      PRIMARY KEY (ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_APPLICATION_ATTRIBUTES]') AND TYPE IN (N'U'))
  CREATE TABLE AM_APPLICATION_ATTRIBUTES (
    APPLICATION_ID INTEGER NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    VALUE VARCHAR(1024) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (APPLICATION_ID,NAME),
    FOREIGN KEY (APPLICATION_ID) REFERENCES AM_APPLICATION (APPLICATION_ID) ON DELETE CASCADE ON UPDATE CASCADE
  ) ;
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_LABELS]') AND TYPE IN (N'U'))
  CREATE TABLE AM_LABELS (
    LABEL_ID VARCHAR(50),
    NAME VARCHAR(255),
    DESCRIPTION VARCHAR(1024),
    TENANT_DOMAIN VARCHAR(255),
    UNIQUE (NAME,TENANT_DOMAIN),
    PRIMARY KEY (LABEL_ID)
  );
  
  IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_LABEL_URLS]') AND TYPE IN (N'U'))
  CREATE TABLE AM_LABEL_URLS (
    LABEL_ID VARCHAR(50),
    ACCESS_URL VARCHAR(255),
    PRIMARY KEY (LABEL_ID,ACCESS_URL),
    FOREIGN KEY (LABEL_ID) REFERENCES AM_LABELS(LABEL_ID) ON UPDATE CASCADE ON DELETE CASCADE
  );
  -- End of API-MGT Tables --
  
  
  --Permance indexes start--
  
  create index IDX_ITS_LMT on IDN_THRIFT_SESSION (LAST_MODIFIED_TIME);
  create index IDX_IOAT_UT on IDN_OAUTH2_ACCESS_TOKEN (USER_TYPE);
  create index IDX_AAI_CTX on AM_API (CONTEXT);
  create index IDX_AAKM_CK on AM_APPLICATION_KEY_MAPPING (CONSUMER_KEY);
  create index IDX_AAUM_AI on AM_API_URL_MAPPING (API_ID);
  create index IDX_AAUM_TT on AM_API_URL_MAPPING (THROTTLING_TIER);
  create index IDX_AATP_DQT on AM_API_THROTTLE_POLICY (DEFAULT_QUOTA_TYPE);
  create index IDX_ACG_QT on AM_CONDITION_GROUP (QUOTA_TYPE);
  create index IDX_APS_QT on AM_POLICY_SUBSCRIPTION (QUOTA_TYPE);
  create index IDX_AS_AITIAI on AM_SUBSCRIPTION (API_ID,TIER_ID,APPLICATION_ID);
  create index IDX_APA_QT on AM_POLICY_APPLICATION (QUOTA_TYPE);
  create index IDX_AA_AT_CB on AM_APPLICATION (APPLICATION_TIER,CREATED_BY);
  
  -- Performance indexes end--
  GO
  