-- 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(2048),
  GRANT_TYPES VARCHAR(1024),
  PKCE_MANDATORY CHAR(1) DEFAULT '0',
  PKCE_SUPPORT_PLAIN CHAR(1) DEFAULT '0',
  APP_STATE VARCHAR (25) DEFAULT 'ACTIVE',
  USER_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
  APP_ACCESS_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
  REFRESH_TOKEN_EXPIRE_TIME BIGINT DEFAULT 84600,
  ID_TOKEN_EXPIRE_TIME BIGINT DEFAULT 3600,
  CONSTRAINT CONSUMER_KEY_CONSTRAINT UNIQUE (CONSUMER_KEY),
  PRIMARY KEY (ID)
);

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(2048),
  SCOPE VARCHAR(2048),
  AUTHORIZED VARCHAR(128),
  OAUTH_VERIFIER VARCHAR(512),
  AUTHZ_USER VARCHAR(512),
  TENANT_ID INTEGER DEFAULT -1,
  PRIMARY KEY (REQUEST_TOKEN),
  FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);

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),
  IDP_ID INTEGER DEFAULT -1 NOT NULL,
  TOKEN_BINDING_REF VARCHAR (32) DEFAULT 'NONE',
  PRIMARY KEY (TOKEN_ID),
  FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
  CONSTRAINT CON_APP_KEY UNIQUE (CONSUMER_KEY_ID,AUTHZ_USER,TENANT_ID,USER_DOMAIN,USER_TYPE,TOKEN_SCOPE_HASH,
                                 TOKEN_STATE,TOKEN_STATE_ID,IDP_ID,TOKEN_BINDING_REF)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_TOKEN_BINDING]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_TOKEN_BINDING (
            TOKEN_ID VARCHAR (255),
            TOKEN_BINDING_TYPE VARCHAR (32),
            TOKEN_BINDING_REF VARCHAR (32),
            TOKEN_BINDING_VALUE VARCHAR (1024),
            TENANT_ID INTEGER DEFAULT -1,
            PRIMARY KEY (TOKEN_ID),
            FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_ACCESS_TOKEN_AUDIT]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_ACCESS_TOKEN_AUDIT (
            TOKEN_ID VARCHAR (255),
            ACCESS_TOKEN VARCHAR(2048),
            REFRESH_TOKEN VARCHAR(2048),
            CONSUMER_KEY_ID INTEGER,
            AUTHZ_USER VARCHAR (100),
            TENANT_ID INTEGER,
            USER_DOMAIN VARCHAR(50),
            USER_TYPE VARCHAR (25),
            GRANT_TYPE VARCHAR (50),
            TIME_CREATED DATETIME,
            REFRESH_TOKEN_TIME_CREATED DATETIME,
            VALIDITY_PERIOD BIGINT,
            REFRESH_TOKEN_VALIDITY_PERIOD BIGINT,
            TOKEN_SCOPE_HASH VARCHAR(32),
            TOKEN_STATE VARCHAR(25),
            TOKEN_STATE_ID VARCHAR (128) ,
            SUBJECT_IDENTIFIER VARCHAR(255),
            ACCESS_TOKEN_HASH VARCHAR(512),
            REFRESH_TOKEN_HASH VARCHAR(512),
            INVALIDATED_TIME DATETIME,
            IDP_ID INTEGER DEFAULT -1 NOT NULL
);

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(2048),
  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),
  IDP_ID INTEGER DEFAULT -1 NOT NULL,
  PRIMARY KEY (CODE_ID),
  FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_AUTHZ_CODE_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_AUTHZ_CODE_SCOPE (
    CODE_ID VARCHAR (255),
    SCOPE VARCHAR (60),
    TENANT_ID INTEGER DEFAULT -1,
    PRIMARY KEY (CODE_ID, SCOPE),
    FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_DEVICE_FLOW]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_DEVICE_FLOW (
    CODE_ID VARCHAR(255),
    DEVICE_CODE VARCHAR(255),
    USER_CODE VARCHAR(25),
    CONSUMER_KEY_ID INTEGER,
    LAST_POLL_TIME DATETIME NOT NULL,
    EXPIRY_TIME DATETIME NOT NULL,
    TIME_CREATED DATETIME NOT NULL,
    POLL_TIME BIGINT,
    STATUS VARCHAR (25) DEFAULT 'PENDING',
    AUTHZ_USER VARCHAR (100),
    TENANT_ID INTEGER,
    USER_DOMAIN VARCHAR(50),
    IDP_ID INTEGER,
    PRIMARY KEY (DEVICE_CODE),
    UNIQUE (CODE_ID),
    UNIQUE (USER_CODE),
    FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_DEVICE_FLOW_SCOPES]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_DEVICE_FLOW_SCOPES (
    ID INTEGER NOT NULL IDENTITY,
    SCOPE_ID VARCHAR(255),
    SCOPE VARCHAR(255),
    PRIMARY KEY (ID),
    FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_DEVICE_FLOW(CODE_ID) ON DELETE CASCADE
);

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,
  SCOPE_TYPE VARCHAR(255) NOT NULL,
  PRIMARY KEY (SCOPE_ID),
  UNIQUE (NAME, TENANT_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) NOT NULL,
  BINDING_TYPE VARCHAR(255) NOT NULL,
  FOREIGN KEY (SCOPE_ID) REFERENCES IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
  UNIQUE (SCOPE_ID, SCOPE_BINDING, BINDING_TYPE)
);

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)
);

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)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_SESSION_APP_INFO]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_SESSION_APP_INFO (
  SESSION_ID VARCHAR (100) NOT NULL,
  SUBJECT VARCHAR (100) NOT NULL,
  APP_ID INTEGER NOT NULL,
  INBOUND_AUTH_TYPE VARCHAR (255) NOT NULL,
  PRIMARY KEY (SESSION_ID, SUBJECT, APP_ID, INBOUND_AUTH_TYPE)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_SESSION_META_DATA]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_SESSION_META_DATA (
  SESSION_ID VARCHAR (100) NOT NULL,
  PROPERTY_TYPE VARCHAR (100) NOT NULL,
  VALUE VARCHAR (255) NOT NULL,
  PRIMARY KEY (SESSION_ID, PROPERTY_TYPE, VALUE)
);

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)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_USER]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_USER (
	USER_ID VARCHAR(255) NOT NULL,
	USER_NAME VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	DOMAIN_NAME VARCHAR(255) NOT NULL,
	IDP_ID INTEGER NOT NULL,
	PRIMARY KEY (USER_ID),
	CONSTRAINT USER_STORE_CONSTRAINT UNIQUE (USER_NAME, TENANT_ID, DOMAIN_NAME, IDP_ID));

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_USER_SESSION_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_USER_SESSION_MAPPING (
	USER_ID VARCHAR(255) NOT NULL,
	SESSION_ID VARCHAR(255) NOT NULL,
	CONSTRAINT USER_SESSION_STORE_CONSTRAINT UNIQUE (USER_ID, SESSION_ID));

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',
  UUID CHAR(36),
  IMAGE_URL VARCHAR(1024),
  ACCESS_URL VARCHAR(1024),
  IS_DISCOVERABLE CHAR(1) DEFAULT '0',

  PRIMARY KEY (ID),
  CONSTRAINT APPLICATION_NAME_CONSTRAINT UNIQUE(APP_NAME, TENANT_ID),
  CONSTRAINT APPLICATION_UUID_CONSTRAINT UNIQUE(UUID)
);


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].[SP_TEMPLATE]') AND TYPE IN (N'U'))
CREATE TABLE SP_TEMPLATE (
  ID          INTEGER NOT NULL IDENTITY,
  TENANT_ID  INTEGER  NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  DESCRIPTION VARCHAR(1023),
  CONTENT VARBINARY(MAX) DEFAULT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT SP_TEMPLATE_CONSTRAINT UNIQUE (TENANT_ID, NAME)
);


IF NOT EXISTS(SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_AUTH_WAIT_STATUS]') AND TYPE IN (N'U'))
CREATE TABLE IDN_AUTH_WAIT_STATUS (
  ID              INTEGER IDENTITY       NOT NULL,
  TENANT_ID       INTEGER                NOT NULL,
  LONG_WAIT_KEY   VARCHAR(255)           NOT NULL,
  WAIT_STATUS     CHAR(1) NOT NULL DEFAULT '1',
  TIME_CREATED    DATETIME,
  EXPIRE_TIME     DATETIME,
  PRIMARY KEY (ID),
  CONSTRAINT IDN_AUTH_WAIT_STATUS_KEY UNIQUE (LONG_WAIT_KEY)
);

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),
    IMAGE_URL                  VARCHAR(1024),
    UUID                       CHAR(36) NOT NULL,
    UNIQUE (TENANT_ID, NAME),
    UNIQUE (UUID)
  );

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,
  ASSOCIATION_ID CHAR(36) NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE(IDP_USER_ID, TENANT_ID, IDP_ID),
  FOREIGN KEY (IDP_ID) REFERENCES IDP(ID) ON DELETE CASCADE
);

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].[FIDO2_DEVICE_STORE]') AND TYPE IN (N'U'))
CREATE TABLE FIDO2_DEVICE_STORE (
  TENANT_ID INTEGER,
  DOMAIN_NAME VARCHAR(255) NOT NULL,
  USER_NAME VARCHAR(45) NOT NULL,
  TIME_REGISTERED DATETIME,
  USER_HANDLE VARCHAR(64) NOT NULL,
  CREDENTIAL_ID VARCHAR(200) NOT NULL,
  PUBLIC_KEY_COSE VARCHAR(1024) NOT NULL,
  SIGNATURE_COUNT BIGINT,
  USER_IDENTITY VARCHAR(512) NOT NULL,
  DISPLAY_NAME VARCHAR(255),
  IS_USERNAMELESS_SUPPORTED CHAR(1) DEFAULT '0',
  PRIMARY KEY (CREDENTIAL_ID, USER_HANDLE)
);

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 NOT NULL,
  CLAIM_URI VARCHAR (255) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
  CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
);

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) ,
  ASSERTION VARBINARY(MAX) ,
  PRIMARY KEY (ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_SAML2_ARTIFACT_STORE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_SAML2_ARTIFACT_STORE (
  ID INTEGER NOT NULL IDENTITY,
  SOURCE_ID VARCHAR(255) NOT NULL,
  MESSAGE_HANDLER VARCHAR(255) NOT NULL,
  AUTHN_REQ_DTO VARBINARY(MAX) NOT NULL,
  SESSION_ID VARCHAR(255) NOT NULL,
  INIT_TIMESTAMP DATETIME NOT NULL,
  EXP_TIMESTAMP DATETIME NOT NULL,
  ASSERTION_ID VARCHAR(255),
  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)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OIDC_SCOPE_CLAIM_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE IDN_OIDC_SCOPE_CLAIM_MAPPING (
  ID INTEGER IDENTITY,
  SCOPE_ID INTEGER NOT NULL,
  EXTERNAL_CLAIM_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (SCOPE_ID) REFERENCES  IDN_OAUTH2_SCOPE(SCOPE_ID) ON DELETE CASCADE,
  FOREIGN KEY (EXTERNAL_CLAIM_ID) REFERENCES  IDN_CLAIM(ID) ON DELETE CASCADE,
  UNIQUE (SCOPE_ID, EXTERNAL_CLAIM_ID)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_FUNCTION_LIBRARY]') AND TYPE IN (N'U'))
CREATE TABLE IDN_FUNCTION_LIBRARY (
	NAME VARCHAR(255) NOT NULL,
	DESCRIPTION VARCHAR(1023),
	TYPE VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	DATA VARBINARY(MAX) NOT NULL,
	PRIMARY KEY (TENANT_ID,NAME)
);

CREATE TABLE IDN_OAUTH2_CIBA_AUTH_CODE (
    AUTH_CODE_KEY CHAR (36),
    AUTH_REQ_ID CHAR (36),
    ISSUED_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSUMER_KEY VARCHAR(255),
    LAST_POLLED_TIME DATETIME NOT NULL,
    POLLING_INTERVAL INTEGER,
    EXPIRES_IN  INTEGER,
    AUTHENTICATED_USER_NAME VARCHAR(255),
    USER_STORE_DOMAIN VARCHAR(100),
    TENANT_ID INTEGER,
    AUTH_REQ_STATUS VARCHAR (100) DEFAULT 'REQUESTED',
    IDP_ID INTEGER,
    UNIQUE(AUTH_REQ_ID),
    PRIMARY KEY (AUTH_CODE_KEY),
    FOREIGN KEY (CONSUMER_KEY) REFERENCES IDN_OAUTH_CONSUMER_APPS(CONSUMER_KEY) ON DELETE CASCADE
);

CREATE TABLE IDN_OAUTH2_CIBA_REQUEST_SCOPES (
    AUTH_CODE_KEY  CHAR (36),
    SCOPE VARCHAR (255),
    FOREIGN KEY (AUTH_CODE_KEY) REFERENCES IDN_OAUTH2_CIBA_AUTH_CODE(AUTH_CODE_KEY) ON DELETE CASCADE
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_FED_AUTH_SESSION_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE IDN_FED_AUTH_SESSION_MAPPING (
	IDP_SESSION_ID VARCHAR(255) NOT NULL,
	SESSION_ID VARCHAR(255) NOT NULL,
	IDP_NAME VARCHAR(255) NOT NULL,
	AUTHENTICATOR_ID VARCHAR(255),
	PROTOCOL_TYPE VARCHAR(255),
	TIME_CREATED DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (IDP_SESSION_ID)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CONFIG_TYPE]')
AND TYPE IN (N'U'))
CREATE  TABLE IDN_CONFIG_TYPE (
    ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    CONSTRAINT TYPE_NAME_CONSTRAINT UNIQUE (NAME)
);

INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('9ab0ef95-13e9-4ed5-afaf-d29bed62f7bd', 'IDP_TEMPLATE', 'Template type to uniquely identify IDP templates'),
('3c4ac3d0-5903-4e3d-aaca-38df65b33bfd', 'APPLICATION_TEMPLATE', 'Template type to uniquely identify Application templates'),
('8ec6dbf1-218a-49bf-bc34-0d2db52d151c', 'CORS_CONFIGURATION', 'A resource type to keep the tenant CORS configurations');

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CONFIG_RESOURCE]')
AND TYPE IN (N'U'))
CREATE TABLE IDN_CONFIG_RESOURCE (
    ID VARCHAR(255) NOT NULL,
    TENANT_ID INT NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    CREATED_TIME DATETIME NOT NULL,
    LAST_MODIFIED DATETIME NOT NULL,
    HAS_FILE BIT DEFAULT 0 NOT NULL,
    HAS_ATTRIBUTE BIT DEFAULT 0 NOT NULL,
    TYPE_ID VARCHAR(255) NOT NULL,
    UNIQUE (NAME, TENANT_ID, TYPE_ID),
    PRIMARY KEY (ID)
);

ALTER TABLE IDN_CONFIG_RESOURCE ADD CONSTRAINT TYPE_ID_FOREIGN_CONSTRAINT FOREIGN KEY (TYPE_ID) REFERENCES
IDN_CONFIG_TYPE (ID) ON DELETE CASCADE ON UPDATE CASCADE;

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CONFIG_ATTRIBUTE]')
AND TYPE IN (N'U'))
CREATE TABLE IDN_CONFIG_ATTRIBUTE (
    ID VARCHAR(255) NOT NULL,
    RESOURCE_ID VARCHAR(255) NOT NULL,
    ATTR_KEY VARCHAR(255) NOT NULL,
    ATTR_VALUE VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    UNIQUE (RESOURCE_ID, ATTR_KEY)
);
ALTER TABLE IDN_CONFIG_ATTRIBUTE ADD CONSTRAINT RESOURCE_ID_ATTRIBUTE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID)
REFERENCES IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CONFIG_FILE]')
AND TYPE IN (N'U'))
CREATE TABLE  IDN_CONFIG_FILE (
    ID VARCHAR(255) NOT NULL,
    VALUE VARBINARY(MAX) NULL,
    NAME VARCHAR(255) NULL,
    RESOURCE_ID VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID)
);
ALTER TABLE IDN_CONFIG_FILE ADD CONSTRAINT RESOURCE_ID_FILE_FOREIGN_CONSTRAINT FOREIGN KEY (RESOURCE_ID) REFERENCES
IDN_CONFIG_RESOURCE (ID) ON DELETE CASCADE ON UPDATE CASCADE;

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_REMOTE_FETCH_CONFIG]') AND TYPE IN (N'U'))
CREATE TABLE IDN_REMOTE_FETCH_CONFIG (
    ID VARCHAR(255) NOT NULL,
    TENANT_ID	INTEGER	 NOT NULL,
    IS_ENABLED CHAR(1) NOT NULL,
    REPO_MANAGER_TYPE  VARCHAR(255) NOT NULL,
    ACTION_LISTENER_TYPE VARCHAR(255) NOT NULL,
    CONFIG_DEPLOYER_TYPE VARCHAR(255) NOT NULL,
    REMOTE_FETCH_NAME VARCHAR(255),
    REMOTE_RESOURCE_URI VARCHAR(255) NOT NULL,
    ATTRIBUTES_JSON TEXT NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT UC_REMOTE_RESOURCE_TYPE UNIQUE (TENANT_ID, CONFIG_DEPLOYER_TYPE)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_REMOTE_FETCH_REVISIONS]') AND TYPE IN (N'U'))
CREATE TABLE IDN_REMOTE_FETCH_REVISIONS (
    ID VARCHAR(255) NOT NULL,
    CONFIG_ID VARCHAR(255) NOT NULL,
    FILE_PATH VARCHAR(255) NOT NULL,
    FILE_HASH VARCHAR(255),
    DEPLOYED_DATE DATETIME,
    LAST_SYNC_TIME DATETIME,
    DEPLOYMENT_STATUS VARCHAR(255),
    ITEM_NAME 	VARCHAR(255),
    DEPLOY_ERR_LOG TEXT,
    PRIMARY KEY (ID),
    FOREIGN KEY (CONFIG_ID) REFERENCES IDN_REMOTE_FETCH_CONFIG(ID) ON DELETE CASCADE,
    CONSTRAINT UC_REVISIONS UNIQUE (CONFIG_ID, ITEM_NAME)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_USER_FUNCTIONALITY_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE IDN_USER_FUNCTIONALITY_MAPPING (
	ID VARCHAR(255) NOT NULL,
	USER_ID VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
	IS_FUNCTIONALITY_LOCKED BIT NOT NULL,
	FUNCTIONALITY_UNLOCK_TIME BIGINT NOT NULL,
	FUNCTIONALITY_LOCK_REASON VARCHAR(1023),
	FUNCTIONALITY_LOCK_REASON_CODE VARCHAR(255),
	PRIMARY KEY (ID),
	CONSTRAINT IDN_USER_FUNCTIONALITY_MAPPING_CONSTRAINT UNIQUE (TENANT_ID, USER_ID, FUNCTIONALITY_ID)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_USER_FUNCTIONALITY_PROPERTY]') AND TYPE IN (N'U'))
CREATE TABLE IDN_USER_FUNCTIONALITY_PROPERTY (
	ID VARCHAR(255) NOT NULL,
	USER_ID VARCHAR(255) NOT NULL,
	TENANT_ID INTEGER NOT NULL,
	FUNCTIONALITY_ID VARCHAR(255) NOT NULL,
	PROPERTY_NAME VARCHAR(255),
	PROPERTY_VALUE VARCHAR(255),
	PRIMARY KEY (ID),
	CONSTRAINT IDN_USER_FUNCTIONALITY_PROPERTY_CONSTRAINT UNIQUE (USER_ID, TENANT_ID, FUNCTIONALITY_ID, PROPERTY_NAME)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CORS_ORIGIN]')
AND TYPE IN (N'U'))
CREATE TABLE IDN_CORS_ORIGIN (
    ID                INT           NOT NULL IDENTITY,
    TENANT_ID         INT           NOT NULL,
    ORIGIN            VARCHAR(2048) NOT NULL,
    UUID              CHAR(36)      NOT NULL,

    PRIMARY KEY (ID),
    UNIQUE (TENANT_ID, ORIGIN),
    UNIQUE (UUID)
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CORS_ASSOCIATION]')
AND TYPE IN (N'U'))
CREATE TABLE IDN_CORS_ASSOCIATION (
    IDN_CORS_ORIGIN_ID INT NOT NULL,
    SP_APP_ID          INT NOT NULL,

    PRIMARY KEY (IDN_CORS_ORIGIN_ID, SP_APP_ID),
    FOREIGN KEY (IDN_CORS_ORIGIN_ID) REFERENCES IDN_CORS_ORIGIN (ID) ON DELETE CASCADE,
    FOREIGN KEY (SP_APP_ID) REFERENCES SP_APP (ID) ON DELETE CASCADE
);

-- --------------------------- INDEX CREATION -----------------------------
-- IDN_OAUTH2_ACCESS_TOKEN --
CREATE INDEX IDX_TC ON IDN_OAUTH2_ACCESS_TOKEN(TIME_CREATED);
CREATE INDEX IDX_ATH ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN_HASH);
CREATE INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_TI_UD ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, TOKEN_STATE, USER_DOMAIN);
CREATE INDEX IDX_AT_AU_TID_UD_TS_CKID ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_STATE, CONSUMER_KEY_ID);
CREATE INDEX IDX_AT_AT ON IDN_OAUTH2_ACCESS_TOKEN (AUTHZ_USER) INCLUDE (ACCESS_TOKEN);
CREATE INDEX IDX_AT_AU_CKID_TS_UT ON IDN_OAUTH2_ACCESS_TOKEN(AUTHZ_USER, CONSUMER_KEY_ID, TOKEN_STATE, USER_TYPE);
CREATE INDEX IDX_AT_RTH ON IDN_OAUTH2_ACCESS_TOKEN(REFRESH_TOKEN_HASH);
CREATE INDEX IDX_AT_RT ON IDN_OAUTH2_ACCESS_TOKEN (AUTHZ_USER) INCLUDE (REFRESH_TOKEN);
CREATE INDEX IDX_AT_CKID_AU_TID_UD_TSH_TS ON IDN_OAUTH2_ACCESS_TOKEN(CONSUMER_KEY_ID, AUTHZ_USER, TENANT_ID, USER_DOMAIN, TOKEN_SCOPE_HASH, TOKEN_STATE);

-- IDN_OAUTH2_AUTHORIZATION_CODE --
CREATE INDEX IDX_AUTHORIZATION_CODE_HASH ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHORIZATION_CODE_HASH, CONSUMER_KEY_ID);
CREATE INDEX IDX_AUTHORIZATION_CODE_AU_TI ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER, TENANT_ID, USER_DOMAIN, STATE);
CREATE INDEX IDX_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE(CONSUMER_KEY_ID);
CREATE INDEX IDX_AC_TID ON IDN_OAUTH2_AUTHORIZATION_CODE(TOKEN_ID);
CREATE INDEX IDX_AC_AC_CKID ON IDN_OAUTH2_AUTHORIZATION_CODE (AUTHZ_USER) INCLUDE (AUTHORIZATION_CODE, CONSUMER_KEY_ID);

-- IDN_SCIM_GROUP --
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME);
CREATE INDEX IDX_IDN_SCIM_GROUP_TI_RN_AN ON IDN_SCIM_GROUP (TENANT_ID, ROLE_NAME, ATTR_NAME);

-- IDN_AUTH_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_SESSION_TIME ON IDN_AUTH_SESSION_STORE (TIME_CREATED);

-- IDN_AUTH_TEMP_SESSION_STORE --
CREATE INDEX IDX_IDN_AUTH_TMP_SESSION_TIME ON IDN_AUTH_TEMP_SESSION_STORE (TIME_CREATED);

-- IDN_OIDC_SCOPE_CLAIM_MAPPING --
CREATE INDEX IDX_AT_SI_ECI ON IDN_OIDC_SCOPE_CLAIM_MAPPING(SCOPE_ID, EXTERNAL_CLAIM_ID);

-- IDN_OAUTH2_SCOPE --
CREATE INDEX IDX_SC_TID ON IDN_OAUTH2_SCOPE(TENANT_ID);

-- IDN_OAUTH2_SCOPE_BINDING --
CREATE INDEX IDX_SB_SCPID ON IDN_OAUTH2_SCOPE_BINDING(SCOPE_ID);

-- IDN_OIDC_REQ_OBJECT_REFERENCE --
CREATE INDEX IDX_OROR_TID ON IDN_OIDC_REQ_OBJECT_REFERENCE(TOKEN_ID);

-- IDN_OAUTH2_ACCESS_TOKEN_SCOPE --
CREATE INDEX IDX_ATS_TID ON IDN_OAUTH2_ACCESS_TOKEN_SCOPE(TOKEN_ID);

-- SP_TEMPLATE --
CREATE INDEX IDX_SP_TEMPLATE ON SP_TEMPLATE (TENANT_ID, NAME);

-- IDN_AUTH_USER --
CREATE INDEX IDX_AUTH_USER_UN_TID_DN ON IDN_AUTH_USER (USER_NAME, TENANT_ID, DOMAIN_NAME);
CREATE INDEX IDX_AUTH_USER_DN_TOD ON IDN_AUTH_USER (DOMAIN_NAME, TENANT_ID);

-- IDN_AUTH_USER_SESSION_MAPPING --
CREATE INDEX IDX_USER_ID ON IDN_AUTH_USER_SESSION_MAPPING (USER_ID);
CREATE INDEX IDX_SESSION_ID ON IDN_AUTH_USER_SESSION_MAPPING (SESSION_ID);

-- IDN_OAUTH_CONSUMER_APPS --
CREATE INDEX IDX_OCA_UM_TID_UD_APN ON IDN_OAUTH_CONSUMER_APPS(USERNAME,TENANT_ID,USER_DOMAIN, APP_NAME);

-- IDX_SPI_APP --
CREATE INDEX IDX_SPI_APP ON SP_INBOUND_AUTH(APP_ID);

-- IDN_OIDC_PROPERTY --
CREATE INDEX IDX_IOP_TID_CK ON IDN_OIDC_PROPERTY(TENANT_ID,CONSUMER_KEY);

-- IDN_FIDO2_PROPERTY --
CREATE INDEX IDX_FIDO2_STR ON FIDO2_DEVICE_STORE(USER_NAME, TENANT_ID, DOMAIN_NAME, CREDENTIAL_ID, USER_HANDLE);

-- IDN_ASSOCIATED_ID --
CREATE INDEX IDX_AI_DN_UN_AI ON IDN_ASSOCIATED_ID(DOMAIN_NAME, USER_NAME, ASSOCIATION_ID);

-- IDN_OAUTH2_TOKEN_BINDING --
CREATE INDEX IDX_IDN_AUTH_BIND ON IDN_OAUTH2_TOKEN_BINDING (TOKEN_BINDING_REF);

-- IDN_FED_AUTH_SESSION_MAPPING --
CREATE INDEX IDX_FEDERATED_AUTH_SESSION_ID ON IDN_FED_AUTH_SESSION_MAPPING (SESSION_ID);

-- IDN_REMOTE_FETCH_REVISIONS --
CREATE INDEX IDX_REMOTE_FETCH_REVISION_CONFIG_ID ON IDN_REMOTE_FETCH_REVISIONS (CONFIG_ID);

-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_SP_APP_ID ON IDN_CORS_ASSOCIATION (SP_APP_ID);

-- IDN_CORS_ASSOCIATION --
CREATE INDEX IDX_CORS_ORIGIN_ID ON IDN_CORS_ASSOCIATION (IDN_CORS_ORIGIN_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),
  PURPOSE_GROUP VARCHAR(255) NOT NULL,
  GROUP_TYPE    VARCHAR(255) NOT NULL,
  TENANT_ID     INTEGER DEFAULT '-1234',
  CONSTRAINT CM_PURPOSE_CNT UNIQUE (NAME, TENANT_ID, PURPOSE_GROUP, GROUP_TYPE),
  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,
  IS_MANDATORY       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, PURPOSE_GROUP, GROUP_TYPE, TENANT_ID) VALUES ('DEFAULT', 'For core functionalities of the product', 'DEFAULT', 'SP', '-1234');

INSERT INTO CM_PURPOSE_CATEGORY (NAME, DESCRIPTION, TENANT_ID) VALUES ('DEFAULT','For core functionalities of the product', '-1234');
-- End of CONSENT-MGT Tables --

-- UMA tables --
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_RESOURCE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_RESOURCE (
  ID INTEGER IDENTITY NOT NULL,
  RESOURCE_ID VARCHAR(255),
  RESOURCE_NAME VARCHAR(255),
  TIME_CREATED DATETIME NOT NULL,
  RESOURCE_OWNER_NAME VARCHAR(255),
  CLIENT_ID VARCHAR(255),
  TENANT_ID INTEGER DEFAULT -1234,
  USER_DOMAIN VARCHAR(50),
  PRIMARY KEY (ID)
);

CREATE INDEX IDX_RID ON IDN_UMA_RESOURCE (RESOURCE_ID);

CREATE INDEX IDX_USER ON IDN_UMA_RESOURCE (RESOURCE_OWNER_NAME, USER_DOMAIN);

CREATE INDEX IDX_USER_RID ON IDN_UMA_RESOURCE (RESOURCE_ID, RESOURCE_OWNER_NAME, USER_DOMAIN, CLIENT_ID);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_RESOURCE_META_DATA]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_RESOURCE_META_DATA (
  ID INTEGER IDENTITY NOT NULL,
  RESOURCE_IDENTITY INTEGER NOT NULL,
  PROPERTY_KEY VARCHAR(40),
  PROPERTY_VALUE VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_RESOURCE_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_RESOURCE_SCOPE (
  ID INTEGER IDENTITY NOT NULL,
  RESOURCE_IDENTITY INTEGER NOT NULL,
  SCOPE_NAME VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (RESOURCE_IDENTITY) REFERENCES IDN_UMA_RESOURCE (ID) ON DELETE CASCADE
);

CREATE INDEX IDX_RS ON IDN_UMA_RESOURCE_SCOPE (SCOPE_NAME);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_PERMISSION_TICKET]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_PERMISSION_TICKET (
  ID INTEGER IDENTITY NOT NULL,
  PT VARCHAR(255) NOT NULL,
  TIME_CREATED DATETIME NOT NULL,
  EXPIRY_TIME DATETIME NOT NULL,
  TICKET_STATE VARCHAR(25) DEFAULT 'ACTIVE',
  TENANT_ID INTEGER     DEFAULT -1234,
  TOKEN_ID VARCHAR(255),
  PRIMARY KEY (ID)
);

CREATE INDEX IDX_PT ON IDN_UMA_PERMISSION_TICKET (PT);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_PT_RESOURCE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_PT_RESOURCE (
  ID INTEGER IDENTITY NOT NULL,
  PT_RESOURCE_ID INTEGER NOT NULL,
  PT_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_ID) REFERENCES IDN_UMA_PERMISSION_TICKET (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_RESOURCE (ID)
);

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_UMA_PT_RESOURCE_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE IDN_UMA_PT_RESOURCE_SCOPE (
  ID INTEGER IDENTITY NOT NULL,
  PT_RESOURCE_ID INTEGER NOT NULL,
  PT_SCOPE_ID INTEGER NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (PT_RESOURCE_ID) REFERENCES IDN_UMA_PT_RESOURCE (ID) ON DELETE CASCADE,
  FOREIGN KEY (PT_SCOPE_ID) REFERENCES IDN_UMA_RESOURCE_SCOPE (ID)
);


-- 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_MONETIZATION_USAGE]') AND TYPE IN (N'U'))

CREATE TABLE AM_MONETIZATION_USAGE (
	ID VARCHAR(100) NOT NULL,
	STATE VARCHAR(50) NOT NULL,
	STATUS VARCHAR(50) NOT NULL,
	STARTED_TIME VARCHAR(50) NOT NULL,
	PUBLISHED_TIME VARCHAR(50) NOT NULL,
	PRIMARY KEY(ID)
);

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_UUID VARCHAR(256),
    API_PROVIDER VARCHAR(200),
    API_NAME VARCHAR(200),
    API_VERSION VARCHAR(30),
    CONTEXT VARCHAR(256),
    CONTEXT_TEMPLATE VARCHAR(256),
    API_TIER VARCHAR(256),
    API_TYPE VARCHAR(10),
    CREATED_BY VARCHAR(100),
    CREATED_TIME DATETIME,
    UPDATED_BY VARCHAR(100),
    UPDATED_TIME DATETIME,
    STATUS VARCHAR(30),
    PRIMARY KEY(API_ID),
    UNIQUE (API_PROVIDER,API_NAME,API_VERSION),
    UNIQUE (API_UUID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GRAPHQL_COMPLEXITY]') AND TYPE IN (N'U'))

CREATE TABLE AM_GRAPHQL_COMPLEXITY (
    UUID VARCHAR(256),
    API_ID INTEGER NOT NULL,
    TYPE VARCHAR(256),
    FIELD VARCHAR(256),
    COMPLEXITY_VALUE INTEGER,
    REVISION_UUID VARCHAR(255),
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY(UUID)
);

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),
    REVISION_UUID VARCHAR(255),
    PRIMARY KEY (URL_MAPPING_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_RESOURCE_SCOPE_MAPPING]') AND TYPE IN (N'U'))

CREATE TABLE AM_API_RESOURCE_SCOPE_MAPPING (
    SCOPE_NAME VARCHAR(255) NOT NULL,
    URL_MAPPING_ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
    PRIMARY KEY(SCOPE_NAME, URL_MAPPING_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SECURITY_AUDIT_UUID_MAPPING]') AND TYPE IN (N'U'))

CREATE TABLE AM_SECURITY_AUDIT_UUID_MAPPING (
    API_ID INTEGER NOT NULL,
    AUDIT_UUID VARCHAR(255) NOT NULL,
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION,
    PRIMARY KEY (API_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_PRODUCT_MAPPING]') AND TYPE IN (N'U'))

CREATE TABLE AM_API_PRODUCT_MAPPING (
	API_PRODUCT_MAPPING_ID INTEGER IDENTITY(1,1),
	API_ID INTEGER,
    URL_MAPPING_ID INTEGER,
    REVISION_UUID VARCHAR(255),
	FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
	FOREIGN KEY (URL_MAPPING_ID) REFERENCES AM_API_URL_MAPPING(URL_MAPPING_ID) ON DELETE CASCADE,
	PRIMARY KEY(API_PRODUCT_MAPPING_ID)
);

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),
    TIER_ID_PENDING 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 (
    UUID VARCHAR(100),
    APPLICATION_ID INTEGER,
    CONSUMER_KEY VARCHAR(512),
    KEY_TYPE VARCHAR(512) NOT NULL,
    STATE VARCHAR(30) NOT NULL,
    CREATE_MODE VARCHAR(30) DEFAULT 'CREATED',
    KEY_MANAGER VARCHAR(100),
    APP_INFO VARBINARY(MAX) DEFAULT NULL,
    FOREIGN KEY(APPLICATION_ID) REFERENCES AM_APPLICATION(APPLICATION_ID) ON UPDATE CASCADE,
    PRIMARY KEY(APPLICATION_ID,KEY_TYPE,KEY_MANAGER)
);

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,
    KEY_MANAGER VARCHAR(255) NOT NULL,
    UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER),
    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 VARCHAR(255) NOT NULL,
    COMMENT_TEXT VARCHAR(512),
    CREATED_BY VARCHAR(255),
    CREATED_TIME DATETIME NOT NULL,
    UPDATED_TIME DATETIME DEFAULT NULL,
    API_ID INTEGER,
    PARENT_COMMENT_ID VARCHAR(255) DEFAULT NULL,
    ENTRY_POINT VARCHAR(20),
    CATEGORY VARCHAR(20) DEFAULT 'general',
    FOREIGN KEY(API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE,
    FOREIGN KEY(PARENT_COMMENT_ID) REFERENCES AM_API_COMMENTS(COMMENT_ID) ON DELETE 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 VARCHAR(255) NOT NULL,
    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,
    LAST_UPDATED_TIME DATETIME,
    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,
    WF_METADATA VARBINARY(MAX) DEFAULT NULL,
    WF_PROPERTIES VARBINARY(MAX) DEFAULT NULL,
    PRIMARY KEY (WF_ID)
);

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,
    KEY_MANAGER VARCHAR(255) NOT NULL,
    UNIQUE (SUBSCRIBER_ID,APP_ID,TOKEN_TYPE,KEY_MANAGER),
    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_SHARED_SCOPE]') AND TYPE IN (N'U'))

CREATE TABLE AM_SHARED_SCOPE (
   NAME VARCHAR(255),
   UUID VARCHAR (256),
   TENANT_ID INTEGER,
   PRIMARY KEY (UUID)
);

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 ,
	      PRIMARY KEY (ALERT_TYPE_ID,USER_NAME,STAKE_HOLDER),
	      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),
	      PRIMARY KEY (EMAIL_LIST_ID,EMAIL),
	      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 ('AbnormalRequestPattern', 'subscriber');
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('UnusualIPAccess', 'subscriber');
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('FrequentTierLimitHitting', 'subscriber');
INSERT INTO AM_ALERT_TYPES (ALERT_TYPE_NAME, STAKE_HOLDER) VALUES ('ApiHealthMonitor', 'publisher');



-- AM Throttling tables --
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),
            MONETIZATION_PLAN VARCHAR(25) NULL DEFAULT NULL,
            FIXED_RATE VARCHAR(15) NULL DEFAULT NULL,
            BILLING_CYCLE VARCHAR(15) NULL DEFAULT NULL,
            PRICE_PER_REQUEST VARCHAR(15) NULL DEFAULT NULL,
            CURRENCY VARCHAR(15) NULL DEFAULT NULL,
            MAX_COMPLEXITY INTEGER NOT NULL DEFAULT 0,
            MAX_DEPTH INTEGER NOT NULL DEFAULT 0,
            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(255) NOT NULL,
  END_POINT VARCHAR(255) NOT NULL,
  CERTIFICATE VARBINARY(MAX) DEFAULT NULL,
  CONSTRAINT PK_ALIAS PRIMARY KEY (ALIAS)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_CLIENT_CERTIFICATE]') AND TYPE IN (N'U'))
CREATE TABLE AM_API_CLIENT_CERTIFICATE (
    TENANT_ID INTEGER NOT NULL,
    ALIAS VARCHAR(45) NOT NULL,
    API_ID INTEGER NOT NULL,
    CERTIFICATE VARBINARY(MAX) NOT NULL,
    REMOVED BIT NOT NULL DEFAULT 0,
    TIER_NAME VARCHAR(512),
    REVISION_UUID VARCHAR(255) NOT NULL DEFAULT 'Current API',
    PRIMARY KEY (ALIAS, TENANT_ID, REMOVED, REVISION_UUID),
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE
);

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
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'AM_SYSTEM_APPS') AND TYPE IN (N'U'))
CREATE TABLE AM_SYSTEM_APPS (
    ID INTEGER IDENTITY,
    NAME VARCHAR(50) NOT NULL,
    CONSUMER_KEY VARCHAR(512) NOT NULL,
    CONSUMER_SECRET VARCHAR(512) NOT NULL,
    TENANT_DOMAIN VARCHAR(255) DEFAULT 'carbon.super',
    CREATED_TIME DATETIME2(6) DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (CONSUMER_KEY),
    PRIMARY KEY (ID)
);

-- BotDATA Email table--
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_NOTIFICATION_SUBSCRIBER]') AND TYPE IN (N'U'))
CREATE TABLE AM_NOTIFICATION_SUBSCRIBER (
    UUID VARCHAR(255),
    CATEGORY VARCHAR(255),
    NOTIFICATION_METHOD VARCHAR(255),
    SUBSCRIBER_ADDRESS VARCHAR(255) NOT NULL,
    PRIMARY KEY(UUID, SUBSCRIBER_ADDRESS)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_REVOKED_JWT]') AND TYPE IN (N'U'))
CREATE TABLE AM_REVOKED_JWT (
    UUID VARCHAR(255) NOT NULL,
    SIGNATURE VARCHAR(2048) NOT NULL,
    EXPIRY_TIMESTAMP BIGINT NOT NULL,
    TENANT_ID INTEGER DEFAULT -1,
    TOKEN_TYPE VARCHAR(15) DEFAULT 'DEFAULT',
    TIME_CREATED DATETIME DEFAULT GETDATE(),
    PRIMARY KEY (UUID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_CATEGORIES]') AND TYPE IN (N'U'))
CREATE TABLE AM_API_CATEGORIES (
  UUID VARCHAR(50),
  NAME VARCHAR(255),
  DESCRIPTION VARCHAR(1024),
  TENANT_ID INTEGER DEFAULT -1,
  UNIQUE (NAME,TENANT_ID),
  PRIMARY KEY (UUID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_USER]') AND TYPE IN (N'U'))
CREATE TABLE AM_USER (
  USER_ID VARCHAR(255) NOT NULL,
  USER_NAME VARCHAR(255) NOT NULL,
  PRIMARY KEY(USER_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_KEY_MANAGER]') AND TYPE IN (N'U'))
CREATE TABLE AM_KEY_MANAGER (
  UUID VARCHAR(50) NOT NULL,
  NAME VARCHAR(100) NULL,
  DISPLAY_NAME VARCHAR(100) NULL,
  DESCRIPTION VARCHAR(256) NULL,
  TYPE VARCHAR(45) NULL,
  CONFIGURATION VARBINARY(MAX) NULL,
  ENABLED BIT DEFAULT 1,
  TENANT_DOMAIN VARCHAR(100) NULL,
  PRIMARY KEY (UUID),
  UNIQUE (NAME,TENANT_DOMAIN)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GW_PUBLISHED_API_DETAILS]') AND TYPE IN (N'U'))
CREATE TABLE AM_GW_PUBLISHED_API_DETAILS (
  API_ID varchar(255) NOT NULL,
  TENANT_DOMAIN varchar(255),
  API_PROVIDER varchar(255),
  API_NAME varchar(255),
  API_VERSION varchar(255),
  API_TYPE varchar(50),
  PRIMARY KEY (API_ID)
);

-- AM_GW_PUBLISHED_API_DETAILS & AM_GW_API_ARTIFACTS are independent tables for Artifact synchronizer feature which --
-- should not have any referential integrity constraints with other tables in AM database--
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GW_API_ARTIFACTS]') AND TYPE IN (N'U'))
CREATE TABLE  AM_GW_API_ARTIFACTS (
  API_ID varchar(255) NOT NULL,
  REVISION_ID varchar(255) NOT NULL,
  ARTIFACT VARBINARY(MAX),
  TIME_STAMP DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (REVISION_ID, API_ID),
  FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GW_API_DEPLOYMENTS]') AND TYPE IN (N'U'))
CREATE TABLE AM_GW_API_DEPLOYMENTS (
  API_ID VARCHAR(255) NOT NULL,
  REVISION_ID VARCHAR(255) NOT NULL,
  LABEL VARCHAR(255) NOT NULL,
  PRIMARY KEY (REVISION_ID, API_ID,LABEL),
  FOREIGN KEY (API_ID) REFERENCES AM_GW_PUBLISHED_API_DETAILS(API_ID) ON UPDATE CASCADE ON DELETE NO ACTION
) ;
-- Tenant Themes Table --
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_TENANT_THEMES]') AND TYPE IN (N'U'))
CREATE TABLE AM_TENANT_THEMES (
  TENANT_ID INTEGER NOT NULL,
  THEME VARBINARY(MAX) NOT NULL,
  PRIMARY KEY (TENANT_ID)
);
-- End of API-MGT Tables --

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SCOPE]') AND TYPE IN (N'U'))
CREATE TABLE AM_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,
  SCOPE_TYPE VARCHAR(255) NOT NULL,
  PRIMARY KEY (SCOPE_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SCOPE_BINDING]') AND TYPE IN (N'U'))
CREATE TABLE AM_SCOPE_BINDING (
  SCOPE_ID INTEGER NOT NULL,
  SCOPE_BINDING VARCHAR(255) NOT NULL,
  BINDING_TYPE VARCHAR(255) NOT NULL,
  FOREIGN KEY (SCOPE_ID) REFERENCES AM_SCOPE(SCOPE_ID) ON DELETE CASCADE
);

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SCOPE_BINDING]') AND TYPE IN (N'U'))
CREATE TABLE AM_REVISION (
  ID INTEGER NOT NULL,
  API_UUID VARCHAR(256) NOT NULL,
  REVISION_UUID VARCHAR(255) NOT NULL,
  DESCRIPTION VARCHAR(255),
  CREATED_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CREATED_BY VARCHAR(255),
  PRIMARY KEY (ID, API_UUID),
  UNIQUE(REVISION_UUID)
)ENGINE INNODB;

IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_DEPLOYMENT_REVISION_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE AM_DEPLOYMENT_REVISION_MAPPING (
  NAME VARCHAR(255) NOT NULL,
  VHOST VARCHAR(255) NULL,
  REVISION_UUID VARCHAR(255) NOT NULL,
  DISPLAY_ON_DEVPORTAL BIT DEFAULT 0,
  DEPLOYED_TIME DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (NAME, REVISION_UUID),
  FOREIGN KEY (REVISION_UUID) REFERENCES AM_REVISION(REVISION_UUID) ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE INNODB;

-- Gateway Environments Table --
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GATEWAY_ENVIRONMENT]') AND TYPE IN (N'U'))
CREATE TABLE AM_GATEWAY_ENVIRONMENT (
  ID INTEGER IDENTITY,
  UUID VARCHAR(45) NOT NULL,
  NAME VARCHAR(255) NOT NULL,
  TENANT_DOMAIN VARCHAR(255),
  DISPLAY_NAME VARCHAR(255) NULL,
  DESCRIPTION VARCHAR(1023) NULL,
  UNIQUE (NAME, TENANT_DOMAIN),
  UNIQUE (UUID),
  PRIMARY KEY (ID)
)ENGINE INNODB;

-- Virtual Hosts Table --
IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_GW_VHOST]') AND TYPE IN (N'U'))
CREATE TABLE AM_GW_VHOST (
  GATEWAY_ENV_ID INTEGER,
  HOST VARCHAR(255) NOT NULL,
  HTTP_CONTEXT VARCHAR(255) NULL,
  HTTP_PORT VARCHAR(5) NOT NULL,
  HTTPS_PORT VARCHAR(5) NOT NULL,
  WS_PORT VARCHAR(5) NOT NULL,
  WSS_PORT VARCHAR(5) NOT NULL,
  FOREIGN KEY (GATEWAY_ENV_ID) REFERENCES AM_GATEWAY_ENVIRONMENT(ID) ON UPDATE CASCADE ON DELETE CASCADE,
  PRIMARY KEY (GATEWAY_ENV_ID, HOST)
)ENGINE INNODB;

-- Service Catalog Tables --
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_SERVICE_CATALOG]') AND TYPE IN (N'U'))
CREATE TABLE AM_SERVICE_CATALOG (
  UUID VARCHAR(36) NOT NULL,
  SERVICE_KEY VARCHAR(100) NOT NULL,
  MD5 VARCHAR(100) NOT NULL,
  SERVICE_NAME VARCHAR(255) NOT NULL,
  DISPLAY_NAME VARCHAR(255) NOT NULL,
  SERVICE_VERSION VARCHAR(30) NOT NULL,
  SERVICE_URL VARCHAR(2048) NOT NULL,
  TENANT_ID INTEGER NOT NULL,
  DEFINITION_TYPE VARCHAR(20),
  DEFINITION_URL VARCHAR(2048),
  DESCRIPTION VARCHAR(1024),
  SECURITY_TYPE VARCHAR(50),
  MUTUAL_SSL_ENABLED BIT DEFAULT 0,
  CREATED_TIME DATETIME NULL,
  LAST_UPDATED_TIME DATETIME NULL,
  CREATED_BY VARCHAR(255),
  UPDATED_BY VARCHAR(255),
  SERVICE_DEFINITION VARBINARY(MAX) NOT NULL,
  METADATA VARBINARY(MAX) NOT NULL,
  PRIMARY KEY (UUID),
  CONSTRAINT SERVICE_KEY_TENANT UNIQUE(SERVICE_KEY, TENANT_ID),
  CONSTRAINT SERVICE_NAME_VERSION_TENANT UNIQUE (SERVICE_NAME, SERVICE_VERSION, TENANT_ID)
);

-- Webhooks --
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_WEBHOOKS_SUBSCRIPTION]') AND TYPE IN (N'U'))
CREATE TABLE AM_WEBHOOKS_SUBSCRIPTION (
    WH_SUBSCRIPTION_ID INTEGER IDENTITY,
    API_UUID VARCHAR(255) NOT NULL,
    APPLICATION_ID VARCHAR(20) NOT NULL,
    TENANT_DOMAIN VARCHAR(255) NOT NULL,
    HUB_CALLBACK_URL VARCHAR(1024) NOT NULL,
    HUB_TOPIC VARCHAR(255) NOT NULL,
    HUB_SECRET VARCHAR(2048),
    HUB_LEASE_SECONDS INTEGER,
    UPDATED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EXPIRY_AT BIGINT,
    DELIVERED_AT TIMESTAMP NULL,
    DELIVERY_STATE INTEGER,
    PRIMARY KEY (WH_SUBSCRIPTION_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_WEBHOOKS_UNSUBSCRIPTION]') AND TYPE IN (N'U'))
CREATE TABLE AM_WEBHOOKS_UNSUBSCRIPTION (
    API_UUID VARCHAR(255) NOT NULL,
    APPLICATION_ID VARCHAR(20) NOT NULL,
    TENANT_DOMAIN VARCHAR(255) NOT NULL,
    HUB_CALLBACK_URL VARCHAR(1024) NOT NULL,
    HUB_TOPIC VARCHAR(255) NOT NULL,
    HUB_SECRET VARCHAR(2048),
    HUB_LEASE_SECONDS INTEGER,
    ADDED_AT TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[AM_API_SERVICE_MAPPING]') AND TYPE IN (N'U'))
CREATE TABLE AM_API_SERVICE_MAPPING (
    API_ID INTEGER NOT NULL,
    SERVICE_KEY VARCHAR(256) NOT NULL,
    MD5 VARCHAR(100) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (API_ID, SERVICE_KEY),
    FOREIGN KEY (API_ID) REFERENCES AM_API(API_ID) ON DELETE CASCADE
);

--Performance indexes start--

create index IDX_ITS_LMT on IDN_THRIFT_SESSION (LAST_MODIFIED_TIME);
create index IDX_IOAT_UT on IDN_OAUTH2_ACCESS_TOKEN (USER_TYPE);
create index IDX_AAI_CTX on AM_API (CONTEXT);
create index IDX_AAKM_CK on AM_APPLICATION_KEY_MAPPING (CONSUMER_KEY);
create index IDX_AAUM_AI on AM_API_URL_MAPPING (API_ID);
create index IDX_AAPM_AI on AM_API_PRODUCT_MAPPING (API_ID);
create index IDX_AAUM_TT on AM_API_URL_MAPPING (THROTTLING_TIER);
create index IDX_AATP_DQT on AM_API_THROTTLE_POLICY (DEFAULT_QUOTA_TYPE);
create index IDX_ACG_QT on AM_CONDITION_GROUP (QUOTA_TYPE);
create index IDX_APS_QT on AM_POLICY_SUBSCRIPTION (QUOTA_TYPE);
create index IDX_AS_AITIAI on AM_SUBSCRIPTION (API_ID,TIER_ID,APPLICATION_ID);
create index IDX_APA_QT on AM_POLICY_APPLICATION (QUOTA_TYPE);
create index IDX_AA_AT_CB on AM_APPLICATION (APPLICATION_TIER,CREATED_BY);

-- Performance indexes end--

