START TRANSACTION;

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

    ALTER TABLE `IDSRules` ADD `WhereToDeploy` int NOT NULL DEFAULT 0;

    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` = '20240601153701_upgradefrom_2.6.0_to_2.7.0') THEN

    ALTER TABLE `Feeds` ADD `Clusters` longtext CHARACTER SET utf8mb4 NULL;

    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` = '20240601153701_upgradefrom_2.6.0_to_2.7.0') THEN

    ALTER TABLE `Feeds` ADD `WhereToDeploy` int NOT NULL DEFAULT 0;

    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` = '20240601153701_upgradefrom_2.6.0_to_2.7.0') THEN

    CREATE TABLE `IDSRuleCluster` (
        `ClusterId` bigint NOT NULL,
        `IDSRuleId` bigint NOT NULL,
        CONSTRAINT `PK_IDSRuleCluster` PRIMARY KEY (`ClusterId`, `IDSRuleId`),
        CONSTRAINT `FK_IDSRuleCluster_Cluster_ClusterId` FOREIGN KEY (`ClusterId`) REFERENCES `Clusters` (`Id`) ON DELETE CASCADE,
        CONSTRAINT `FK_IDSRuleCluster_IDSRule_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` = '20240601153701_upgradefrom_2.6.0_to_2.7.0') THEN

    CREATE INDEX `IX_IDSRuleCluster_IDSRuleId` ON `IDSRuleCluster` (`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` = '20240601153701_upgradefrom_2.6.0_to_2.7.0') THEN

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

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

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

COMMIT;


