IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_USER_CONSENT]')
AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_USER_CONSENT (
    ID INTEGER IDENTITY,
    USER_ID VARCHAR(255) NOT NULL,
    APP_ID CHAR(36) NOT NULL,
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    CONSENT_ID VARCHAR(255) NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (APP_ID) REFERENCES SP_APP(UUID) ON DELETE CASCADE,
    UNIQUE (USER_ID, APP_ID, TENANT_ID),
    UNIQUE (CONSENT_ID)
);

IF NOT  EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(N'[DBO].[IDN_OAUTH2_USER_CONSENTED_SCOPES]')
AND TYPE IN (N'U'))
CREATE TABLE IDN_OAUTH2_USER_CONSENTED_SCOPES (
    ID INTEGER IDENTITY,
    CONSENT_ID VARCHAR(255) NOT NULL,
    TENANT_ID INTEGER NOT NULL DEFAULT -1,
    SCOPE VARCHAR(255) NOT NULL,
    CONSENT BIT DEFAULT 1 NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (CONSENT_ID) REFERENCES IDN_OAUTH2_USER_CONSENT(CONSENT_ID) ON DELETE CASCADE,
    UNIQUE (CONSENT_ID, SCOPE)
);
