BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
  BEGIN END;
  EXECUTE IMMEDIATE 'DROP INDEX IDX_TK_VALUE_TYPE';
END
/

CREATE INDEX IDX_TK_VALUE_TYPE ON IDN_OAUTH2_TOKEN_BINDING(TOKEN_BINDING_VALUE, TOKEN_BINDING_TYPE)
/

-- --------------------------- INDEX CREATION -----------------------------

-- IDN_OIDC_PROPERTY --
CREATE INDEX IDX_IOP_CK ON IDN_OIDC_PROPERTY(CONSUMER_KEY)
/

-- --------------------------- REMOVE UNUSED INDICES -----------------------------

BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
  BEGIN END;
  -- IDN_OAUTH2_ACCESS_TOKEN --
  EXECUTE IMMEDIATE 'DROP INDEX IDX_AT_CK_AU';
  EXECUTE IMMEDIATE 'DROP INDEX IDX_AT_AU_TID_UD_TS_CKID';
  EXECUTE IMMEDIATE 'DROP INDEX IDX_AT_AU_CKID_TS_UT';
  -- IDN_OIDC_PROPERTY --
  EXECUTE IMMEDIATE 'DROP INDEX IDX_IOP_TID_CK';
END
/

------------------------ ORGANIZATION MANAGEMENT TABLES -------------------------

CREATE TABLE IF NOT EXISTS SP_SHARED_APP (
	ID INTEGER NOT NULL,
	MAIN_APP_ID CHAR(36) NOT NULL,
	OWNER_ORG_ID CHAR(36) NOT NULL,
	SHARED_APP_ID CHAR(36) NOT NULL,
	SHARED_ORG_ID CHAR(36) NOT NULL,
	PRIMARY KEY (ID),
	FOREIGN KEY (MAIN_APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE,
	FOREIGN KEY (SHARED_APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE,
	UNIQUE (MAIN_APP_ID, OWNER_ORG_ID, SHARED_ORG_ID),
	UNIQUE (SHARED_APP_ID)
)
/

CREATE SEQUENCE SP_SHARED_APP_SEQUENCE START WITH 1 INCREMENT BY 1 NOCACHE
/

CREATE TRIGGER SP_SHARED_APP_TRIG NO CASCADE
    BEFORE INSERT
    ON SP_SHARED_APP
    REFERENCING NEW AS NEW
    FOR EACH ROW MODE DB2SQL
        BEGIN ATOMIC
            SET (NEW.ID) = (NEXTVAL FOR SP_SHARED_APP_SEQUENCE);
        END
/
