IF NOT EXISTS (SELECT * FROM SYS.indexes WHERE name = 'IDX_TK_VALUE_TYPE' and object_id = OBJECT_ID('IDN_OAUTH2_TOKEN_BINDING'))
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 -----------------------------

-- IDN_OAUTH2_ACCESS_TOKEN --
IF EXISTS (SELECT *  FROM sys.indexes WHERE name='IDX_AT_CK_AU') begin DROP INDEX IDX_AT_CK_AU ON IDN_OAUTH2_ACCESS_TOKEN; end;

DROP INDEX IDX_AT_AU_TID_UD_TS_CKID ON IDN_OAUTH2_ACCESS_TOKEN;

DROP INDEX IDX_AT_AU_CKID_TS_UT ON IDN_OAUTH2_ACCESS_TOKEN;

-- IDN_OIDC_PROPERTY --
DROP INDEX IDX_IOP_TID_CK ON IDN_OIDC_PROPERTY;

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

IF NOT EXISTS ( SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[SP_SHARED_APP]')
AND TYPE IN (N'U'))
CREATE TABLE SP_SHARED_APP(
    ID INTEGER IDENTITY(1,1) 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),
    FOREIGN KEY (SHARED_APP_ID) REFERENCES SP_APP(UUID),
    UNIQUE (MAIN_APP_ID, OWNER_ORG_ID, SHARED_ORG_ID),
    UNIQUE (SHARED_APP_ID)
);

-- Trigger SP_SHARED_APP delete by UUID on SP_APP deletion by UUID --
CREATE TRIGGER SP_APP_DELETE_TRIGGER ON SP_APP INSTEAD OF DELETE AS
BEGIN
    DELETE FROM SP_SHARED_APP WHERE MAIN_APP_ID IN (SELECT UUID FROM deleted)
    DELETE FROM SP_SHARED_APP WHERE SHARED_APP_ID IN (SELECT UUID FROM deleted)
    DELETE FROM SP_APP WHERE UUID IN (SELECT UUID FROM deleted)
END;
