ALTER TABLE IDN_OAUTH_CONSUMER_APPS ADD APP_STATE VARCHAR (25) DEFAULT 'ACTIVE';
CREATE INDEX IDX_AT ON IDN_OAUTH2_ACCESS_TOKEN(ACCESS_TOKEN);
ALTER TABLE SP_APP ADD ENABLE_AUTHORIZATION CHAR(1) DEFAULT '0';
ALTER TABLE SP_INBOUND_AUTH ADD INBOUND_CONFIG_TYPE VARCHAR(255) DEFAULT NULL;
ALTER TABLE SP_CLAIM_MAPPING ADD IS_MANDATORY VARCHAR(128) DEFAULT '0';
ALTER TABLE SP_PROVISIONING_CONNECTOR ADD RULE_ENABLED CHAR(1) NOT NULL DEFAULT '0';
ALTER TABLE IDP_PROVISIONING_CONFIG ADD IS_RULES_ENABLED CHAR(1) NOT NULL DEFAULT '0';


IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_RECOVERY_DATA]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_RECOVERY_DATA (
    USER_NAME VARCHAR(255) NOT NULL,
    USER_DOMAIN VARCHAR(127) NOT NULL,
    TENANT_ID INTEGER DEFAULT -1,
    CODE VARCHAR(255) NOT NULL,
    SCENARIO VARCHAR(255) NOT NULL,
    STEP VARCHAR(127) NOT NULL,
    TIME_CREATED DATETIME NOT NULL,
    REMAINING_SETS VARCHAR(2500) DEFAULT NULL,
    PRIMARY KEY(USER_NAME, USER_DOMAIN, TENANT_ID, SCENARIO,STEP),
    UNIQUE(CODE)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_PASSWORD_HISTORY_DATA]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_PASSWORD_HISTORY_DATA (
    ID INTEGER NOT NULL IDENTITY ,
    USER_NAME   VARCHAR(255) NOT NULL,
    USER_DOMAIN VARCHAR(127) NOT NULL,
    TENANT_ID   INTEGER DEFAULT -1,
    SALT_VALUE  VARCHAR(255),
    HASH        VARCHAR(255) NOT NULL,
    TIME_CREATED DATETIME NOT NULL,
    PRIMARY KEY (ID),
    UNIQUE (USER_NAME,USER_DOMAIN,TENANT_ID,SALT_VALUE,HASH),
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_DIALECT]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_DIALECT (
    ID INTEGER NOT NULL IDENTITY,
    DIALECT_URI VARCHAR (255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    CONSTRAINT DIALECT_URI_CONSTRAINT UNIQUE (DIALECT_URI, TENANT_ID)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM (
    ID INTEGER NOT NULL IDENTITY,
    DIALECT_ID INTEGER,
    CLAIM_URI VARCHAR (255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (DIALECT_ID) REFERENCES IDN_CLAIM_DIALECT(ID) ON DELETE CASCADE,
    CONSTRAINT CLAIM_URI_CONSTRAINT UNIQUE (DIALECT_ID, CLAIM_URI, TENANT_ID)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_MAPPED_ATTRIBUTE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_MAPPED_ATTRIBUTE (
    ID INTEGER NOT NULL IDENTITY,
    LOCAL_CLAIM_ID INTEGER,
    USER_STORE_DOMAIN_NAME VARCHAR (255) NOT NULL,
    ATTRIBUTE_NAME VARCHAR (255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
    CONSTRAINT USER_STORE_DOMAIN_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, USER_STORE_DOMAIN_NAME, TENANT_ID)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_PROPERTY]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_PROPERTY (
    ID INTEGER NOT NULL IDENTITY,
    LOCAL_CLAIM_ID INTEGER,
    PROPERTY_NAME VARCHAR (255) NOT NULL,
    PROPERTY_VALUE VARCHAR (255) NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
    CONSTRAINT PROPERTY_NAME_CONSTRAINT UNIQUE (LOCAL_CLAIM_ID, PROPERTY_NAME, TENANT_ID)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_CLAIM_MAPPING]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_CLAIM_MAPPING (
    ID INTEGER NOT NULL IDENTITY,
    EXT_CLAIM_ID INTEGER NOT NULL,
    MAPPED_LOCAL_CLAIM_ID INTEGER NOT NULL,
    TENANT_ID INTEGER NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (EXT_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE CASCADE,
    FOREIGN KEY (MAPPED_LOCAL_CLAIM_ID) REFERENCES IDN_CLAIM(ID) ON DELETE NO ACTION,
    CONSTRAINT EXT_TO_LOC_MAPPING_CONSTRN UNIQUE (EXT_CLAIM_ID, TENANT_ID)
  );

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_SAML2_ASSERTION_STORE]') AND TYPE IN (N'U'))
  CREATE TABLE IDN_SAML2_ASSERTION_STORE (
    ID INTEGER NOT NULL IDENTITY,
    SAML2_ID  VARCHAR(255),
    SAML2_ISSUER  VARCHAR(255),
    SAML2_SUBJECT  VARCHAR(255),
    SAML2_SESSION_INDEX  VARCHAR(255),
    SAML2_AUTHN_CONTEXT_CLASS_REF  VARCHAR(255),
    SAML2_ASSERTION  VARCHAR(4096),
    PRIMARY KEY (ID)
  );
