CREATE OR REPLACE PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW
BEGIN ATOMIC
    IF EXISTS(SELECT * FROM SYSCAT.TABLES WHERE TABNAME='IDN_OAUTH2_DEVICE_FLOW')
    THEN
        IF NOT EXISTS(SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='IDN_OAUTH2_DEVICE_FLOW' AND COLNAME='QUANTIFIER')
        THEN
            BEGIN
                DECLARE const_name VARCHAR(128);
                DECLARE STMT VARCHAR(200);
                SELECT INDNAME into const_name from SYSCAT.INDEXES WHERE TABNAME='IDN_OAUTH2_DEVICE_FLOW' AND COLNAMES='+USER_CODE';
                SET STMT = 'ALTER TABLE IDN_OAUTH2_DEVICE_FLOW DROP CONSTRAINT ' ||  const_name;
                PREPARE S1 FROM STMT;
                EXECUTE S1;
            END;
            EXECUTE IMMEDIATE 'ALTER TABLE IDN_OAUTH2_DEVICE_FLOW ADD COLUMN QUANTIFIER INTEGER DEFAULT 0 NOT NULL';
            EXECUTE IMMEDIATE 'ALTER TABLE IDN_OAUTH2_DEVICE_FLOW ADD CONSTRAINT USRCDE_QNTFR_CONSTRAINT UNIQUE (USER_CODE, QUANTIFIER)';
        END IF;
    END IF;
END
/

CALL ALTER_IDN_OAUTH2_DEVICE_FLOW
/

DROP PROCEDURE ALTER_IDN_OAUTH2_DEVICE_FLOW
/

UPDATE IDP_METADATA SET NAME = 'account.lock.handler.lock.on.max.failed.attempts.enable'
WHERE NAME = 'account.lock.handler.enable'
/

CREATE TABLE IDN_SECRET_TYPE (
    ID VARCHAR(255) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    CONSTRAINT SECRET_TYPE_NAME_CONSTRAINT UNIQUE (NAME)
)
/

CREATE TABLE IDN_SECRET (
    ID VARCHAR(255) NOT NULL,
    TENANT_ID INT NOT NULL,
    SECRET_NAME VARCHAR(255) NOT NULL,
    SECRET_VALUE VARCHAR(8000) NOT NULL,
    CREATED_TIME TIMESTAMP NOT NULL,
    LAST_MODIFIED TIMESTAMP NOT NULL,
    TYPE_ID       VARCHAR(255) NOT NULL,
    DESCRIPTION VARCHAR(1023) NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (TYPE_ID) REFERENCES IDN_SECRET_TYPE(ID) ON DELETE CASCADE,
    UNIQUE (SECRET_NAME, TENANT_ID, TYPE_ID)
)
/

INSERT INTO IDN_SECRET_TYPE (ID, NAME, DESCRIPTION) VALUES
('1358bdbf-e0cc-4268-a42c-c3e0960e13f0', 'ADAPTIVE_AUTH_CALL_CHOREO', 'Secret type to uniquely identify secrets relevant to callChoreo adaptive auth function')
/

INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION)
SELECT '669b99ca-cdb0-44a6-8cae-babed3b585df', 'Publisher', 'A resource type to keep the event publisher configurations' FROM "SYSIBM".SYSDUMMY1
WHERE NOT EXISTS (SELECT * FROM IDN_CONFIG_TYPE WHERE id = '669b99ca-cdb0-44a6-8cae-babed3b585df' OR name = 'Publisher')
/

INSERT INTO IDN_CONFIG_TYPE (ID, NAME, DESCRIPTION) VALUES
('73f6d9ca-62f4-4566-bab9-2a930ae51ba8', 'BRANDING_PREFERENCES', 'A resource type to keep the tenant branding preferences'),
('899c69b2-8bf7-46b5-9666-f7f99f90d6cc', 'fido-config', 'A resource type to store FIDO authenticator related preferences')
/

