DROP INDEX IF EXISTS IDX_AT;
DROP INDEX IF EXISTS IDX_AUTHORIZATION_CODE;
ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ALTER COLUMN REFRESH_TOKEN TYPE VARCHAR(2048);
ALTER TABLE IDN_OAUTH2_ACCESS_TOKEN ALTER COLUMN ACCESS_TOKEN TYPE VARCHAR(2048);
ALTER TABLE IDN_OAUTH2_AUTHORIZATION_CODE ALTER COLUMN AUTHORIZATION_CODE TYPE VARCHAR(2048);
ALTER TABLE IDN_OAUTH_CONSUMER_APPS ALTER COLUMN CONSUMER_SECRET TYPE VARCHAR(2048);

DROP TABLE IF EXISTS IDN_OAUTH2_SCOPE_VALIDATORS;
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
);
DROP TABLE IF EXISTS SP_AUTH_SCRIPT;
DROP SEQUENCE IF EXISTS SP_AUTH_SCRIPT_SEQ;
CREATE SEQUENCE SP_AUTH_SCRIPT_SEQ;
CREATE TABLE SP_AUTH_SCRIPT (
  ID         INTEGER      NOT NULL DEFAULT NEXTVAL('SP_AUTH_SCRIPT_SEQ'),
  TENANT_ID  INTEGER      NOT NULL,
  APP_ID     INTEGER      NOT NULL,
  TYPE       VARCHAR(255) NOT NULL,
  CONTENT    BYTEA                 DEFAULT NULL,
  IS_ENABLED BOOLEAN               DEFAULT FALSE,
  PRIMARY KEY (ID)
);
DROP TABLE IF EXISTS IDN_OIDC_JTI;
CREATE TABLE IDN_OIDC_JTI (
  JWT_ID VARCHAR(255) NOT NULL,
  EXP_TIME TIMESTAMP NOT NULL,
  TIME_CREATED TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (JWT_ID)
);

DROP TABLE IF EXISTS IDN_OIDC_PROPERTY;
DROP SEQUENCE IF EXISTS IDN_OIDC_PROPERTY_SEQ;
CREATE SEQUENCE IDN_OIDC_PROPERTY_SEQ;
CREATE TABLE IDN_OIDC_PROPERTY (
  ID INTEGER DEFAULT NEXTVAL('IDN_OIDC_PROPERTY_SEQ'),
  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
);
DROP TABLE IF EXISTS IDN_OIDC_REQ_OBJECT_REFERENCE;
DROP SEQUENCE IF EXISTS IDN_OIDC_REQUEST_OBJECT_REF_SEQ;
CREATE SEQUENCE IDN_OIDC_REQUEST_OBJECT_REF_SEQ;
CREATE TABLE IDN_OIDC_REQ_OBJECT_REFERENCE (
  ID INTEGER DEFAULT NEXTVAL('IDN_OIDC_REQUEST_OBJECT_REF_SEQ'),
  CONSUMER_KEY_ID INTEGER ,
  CODE_ID VARCHAR(255) ,
  TOKEN_ID VARCHAR(255) ,
  SESSION_DATA_KEY VARCHAR(255),
  PRIMARY KEY (ID),
  FOREIGN KEY (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID) ON DELETE CASCADE,
  FOREIGN KEY (TOKEN_ID) REFERENCES IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) ON DELETE CASCADE,
  FOREIGN KEY (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) ON DELETE CASCADE
);

DROP TABLE IF EXISTS IDN_OIDC_REQ_OBJECT_CLAIMS;
DROP SEQUENCE IF EXISTS IDN_OIDC_REQ_OBJECT_CLAIMS_SEQ;
CREATE SEQUENCE IDN_OIDC_REQ_OBJECT_CLAIMS_SEQ;
CREATE TABLE IDN_OIDC_REQ_OBJECT_CLAIMS (
  ID INTEGER DEFAULT NEXTVAL('IDN_OIDC_REQ_OBJECT_CLAIMS_SEQ'),
  REQ_OBJECT_ID INTEGER,
  CLAIM_ATTRIBUTE VARCHAR(255) ,
  ESSENTIAL BOOLEAN ,
  VALUE VARCHAR(255) ,
  IS_USERINFO BOOLEAN,
  PRIMARY KEY (ID),
  FOREIGN KEY (REQ_OBJECT_ID) REFERENCES IDN_OIDC_REQ_OBJECT_REFERENCE (ID) ON DELETE CASCADE
);

DROP TABLE IF EXISTS IDN_OIDC_REQ_OBJ_CLAIM_VALUES;
DROP SEQUENCE IF EXISTS IDN_OIDC_REQ_OBJECT_CLAIM_VALUES_SEQ;
CREATE SEQUENCE IDN_OIDC_REQ_OBJECT_CLAIM_VALUES_SEQ;
CREATE TABLE IDN_OIDC_REQ_OBJ_CLAIM_VALUES (
  ID INTEGER DEFAULT NEXTVAL('IDN_OIDC_REQ_OBJECT_CLAIM_VALUES_SEQ'),
  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
);

DROP TABLE IF EXISTS IDN_CERTIFICATE;
DROP SEQUENCE IF EXISTS IDN_CERTIFICATE_PK_SEQ;
CREATE SEQUENCE IDN_CERTIFICATE_PK_SEQ;
CREATE TABLE IDN_CERTIFICATE (
            ID INTEGER DEFAULT NEXTVAL('IDN_CERTIFICATE_PK_SEQ'),
            NAME VARCHAR(100),
            CERTIFICATE_IN_PEM BYTEA,
            TENANT_ID INTEGER DEFAULT 0,
            CONSTRAINT CERTIFICATE_UNIQUE_KEY UNIQUE (NAME, TENANT_ID),
            PRIMARY KEY (ID)
);
