DROP PROCEDURE IF EXISTS add_or_alter_pk_column;

DELIMITER $$
CREATE PROCEDURE add_or_alter_pk_column(db_table_name VARCHAR(100))
BEGIN
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=db_table_name) THEN
        IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=db_table_name AND COLUMN_KEY='PRI') THEN
            BEGIN
                SELECT COLUMN_NAME INTO @pk_column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=db_table_name AND COLUMN_KEY='PRI';
                SET @s = CONCAT('ALTER TABLE ', db_table_name, ' RENAME COLUMN ', @pk_column_name, ' TO ID');
            END;
        ELSE
            BEGIN
                SET @s = CONCAT('ALTER TABLE ', db_table_name, ' ADD ID INTEGER NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (ID)');
            END;
        END IF;
       	PREPARE stmt FROM @s;
        EXECUTE stmt;
    END IF;
END $$
DELIMITER ;

CALL add_or_alter_pk_column('IDN_OAUTH2_ACCESS_TOKEN_AUDIT');
CALL add_or_alter_pk_column('IDN_OAUTH2_SCOPE_BINDING');
CALL add_or_alter_pk_column('IDN_AUTH_USER_SESSION_MAPPING');
CALL add_or_alter_pk_column('IDN_OAUTH2_CIBA_REQUEST_SCOPES');

-- Add CONSENTED_TOKEN column if it doesn't exist.
DROP PROCEDURE IF EXISTS ALTER_IDN_OAUTH2_ACCESS_TOKEN;

DELIMITER $$
CREATE PROCEDURE ALTER_IDN_OAUTH2_ACCESS_TOKEN()
BEGIN
    IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='IDN_OAUTH2_ACCESS_TOKEN') THEN
        IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='IDN_OAUTH2_ACCESS_TOKEN' AND COLUMN_NAME='CONSENTED_TOKEN') THEN
            ALTER TABLE `IDN_OAUTH2_ACCESS_TOKEN` ADD COLUMN `CONSENTED_TOKEN` VARCHAR(6);
        END IF;
    END IF;
END $$
DELIMITER ;

CALL ALTER_IDN_OAUTH2_ACCESS_TOKEN();

ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING DROP PRIMARY KEY;
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD ID INTEGER NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (ID);
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD TENANT_ID INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE IDN_FED_AUTH_SESSION_MAPPING ADD UNIQUE(IDP_SESSION_ID, TENANT_ID);

DROP PROCEDURE IF EXISTS add_or_alter_pk_column;
