START TRANSACTION;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    CREATE TABLE `ApiKeys` (
        `Id` bigint NOT NULL AUTO_INCREMENT,
        `Name` varchar(255) CHARACTER SET utf8mb4 NULL,
        `Value` varchar(255) CHARACTER SET utf8mb4 NULL,
        `IsEnabled` tinyint(1) NOT NULL,
        `CreatedBy` longtext CHARACTER SET utf8mb4 NULL,
        `CreatedAt` datetime(6) NOT NULL,
        `UpdatedAt` datetime(6) NOT NULL,
        `LastUsedAt` datetime(6) NULL,
        CONSTRAINT `PK_ApiKeys` PRIMARY KEY (`Id`)
    ) CHARACTER SET=utf8mb4;

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    CREATE TABLE `RuleSources` (
        `Id` bigint NOT NULL AUTO_INCREMENT,
        `Name` varchar(255) CHARACTER SET utf8mb4 NULL,
        `CreatedAt` datetime(6) NOT NULL,
        `UpdatedAt` datetime(6) NULL,
        `IDSRuleId` bigint NOT NULL,
        CONSTRAINT `PK_RuleSources` PRIMARY KEY (`Id`),
        CONSTRAINT `FK_RuleSources_IDSRules_IDSRuleId` FOREIGN KEY (`IDSRuleId`) REFERENCES `IDSRules` (`Id`) ON DELETE CASCADE
    ) CHARACTER SET=utf8mb4;

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    CREATE UNIQUE INDEX `IX_ApiKeys_Name` ON `ApiKeys` (`Name`);

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    CREATE UNIQUE INDEX `IX_ApiKeys_Value` ON `ApiKeys` (`Value`);

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    CREATE INDEX `IX_RuleSources_IDSRuleId` ON `RuleSources` (`IDSRuleId`);

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    CREATE UNIQUE INDEX `IX_RuleSources_Name_IDSRuleId` ON `RuleSources` (`Name`, `IDSRuleId`);

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

DROP PROCEDURE IF EXISTS MigrationsScript;
DELIMITER //
CREATE PROCEDURE MigrationsScript()
BEGIN
    IF NOT EXISTS(SELECT 1 FROM `__EFMigrationsHistory` WHERE `MigrationId` = '20240429184537_upgradefrom_2.5.0_to_2.6.0') THEN

    INSERT INTO `__EFMigrationsHistory` (`MigrationId`, `ProductVersion`)
    VALUES ('20240429184537_upgradefrom_2.5.0_to_2.6.0', '8.0.4');

    -- mark all hosts as having stale config
    UPDATE `Hosts` SET `NeedsConfigUpdate`='1';

    INSERT INTO `database_version_history` (`Version`, `UpdatedAt`) 
	VALUES ('2.6.0', NOW());

    END IF;
END //
DELIMITER ;
CALL MigrationsScript();
DROP PROCEDURE MigrationsScript;

COMMIT;


