-- Migration: Modifier les responsables des conventions pour utiliser presidents_commissions
-- Date: 2025-01-XX
-- Description: Modifie les colonnes vice_president_crcs_id et president_commission_permanente_id
--              pour référencer presidents_commissions au lieu de partenaires, et ajoute
--              la colonne secretaire_president_id

SET @dbname = DATABASE();

-- Supprimer les anciennes contraintes et index
SET @tablename = 'conventions';
SET @column1 = 'vice_president_crcs_id';
SET @column2 = 'president_commission_permanente_id';
SET @column3 = 'secretaire_president_id';

-- Supprimer les contraintes existantes si elles existent
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = 'conventions_ibfk_vice_president')
  ) > 0,
  'ALTER TABLE `conventions` DROP FOREIGN KEY `conventions_ibfk_vice_president`;',
  'SELECT "La contrainte conventions_ibfk_vice_president n''existe pas." AS message;'
));
PREPARE dropFK1 FROM @preparedStatement1;
EXECUTE dropFK1;
DEALLOCATE PREPARE dropFK1;

SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = 'conventions_ibfk_president_commission')
  ) > 0,
  'ALTER TABLE `conventions` DROP FOREIGN KEY `conventions_ibfk_president_commission`;',
  'SELECT "La contrainte conventions_ibfk_president_commission n''existe pas." AS message;'
));
PREPARE dropFK2 FROM @preparedStatement2;
EXECUTE dropFK2;
DEALLOCATE PREPARE dropFK2;

-- Ajouter la colonne secretaire_president_id si elle n'existe pas
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @column3)
  ) > 0,
  'SELECT "La colonne secretaire_president_id existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column3, '` int NULL DEFAULT NULL COMMENT "Secrétaire du président" AFTER `president_commission_permanente_id`, ADD INDEX `idx_secretaire_president`(`', @column3, '` ASC) USING BTREE;')
));
PREPARE alterIfNotExists FROM @preparedStatement3;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Ajouter les nouvelles contraintes vers presidents_commissions
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = 'conventions_ibfk_vice_president_pc')
  ) > 0,
  'SELECT "La contrainte conventions_ibfk_vice_president_pc existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `conventions_ibfk_vice_president_pc` FOREIGN KEY (`', @column1, '`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE addFK1 FROM @preparedStatement4;
EXECUTE addFK1;
DEALLOCATE PREPARE addFK1;

SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = 'conventions_ibfk_president_commission_pc')
  ) > 0,
  'SELECT "La contrainte conventions_ibfk_president_commission_pc existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `conventions_ibfk_president_commission_pc` FOREIGN KEY (`', @column2, '`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE addFK2 FROM @preparedStatement5;
EXECUTE addFK2;
DEALLOCATE PREPARE addFK2;

SET @preparedStatement6 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = 'conventions_ibfk_secretaire_president')
  ) > 0,
  'SELECT "La contrainte conventions_ibfk_secretaire_president existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `conventions_ibfk_secretaire_president` FOREIGN KEY (`', @column3, '`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE addFK3 FROM @preparedStatement6;
EXECUTE addFK3;
DEALLOCATE PREPARE addFK3;

-- Faire la même chose pour convention_specifique
SET @tablename2 = 'convention_specifique';

-- Supprimer les anciennes contraintes
SET @preparedStatement7 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (CONSTRAINT_NAME = 'convention_specifique_ibfk_5')
  ) > 0,
  'ALTER TABLE `convention_specifique` DROP FOREIGN KEY `convention_specifique_ibfk_5`;',
  'SELECT "La contrainte convention_specifique_ibfk_5 n''existe pas." AS message;'
));
PREPARE dropFK3 FROM @preparedStatement7;
EXECUTE dropFK3;
DEALLOCATE PREPARE dropFK3;

SET @preparedStatement8 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (CONSTRAINT_NAME = 'convention_specifique_ibfk_6')
  ) > 0,
  'ALTER TABLE `convention_specifique` DROP FOREIGN KEY `convention_specifique_ibfk_6`;',
  'SELECT "La contrainte convention_specifique_ibfk_6 n''existe pas." AS message;'
));
PREPARE dropFK4 FROM @preparedStatement8;
EXECUTE dropFK4;
DEALLOCATE PREPARE dropFK4;

-- Ajouter la colonne secretaire_president_id si elle n'existe pas
SET @preparedStatement9 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (COLUMN_NAME = @column3)
  ) > 0,
  'SELECT "La colonne secretaire_president_id existe déjà dans convention_specifique." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD COLUMN `', @column3, '` int NULL DEFAULT NULL COMMENT "Secrétaire du président" AFTER `president_commission_permanente_id`, ADD INDEX `idx_secretaire_president`(`', @column3, '` ASC) USING BTREE;')
));
PREPARE alterIfNotExists2 FROM @preparedStatement9;
EXECUTE alterIfNotExists2;
DEALLOCATE PREPARE alterIfNotExists2;

-- Ajouter les nouvelles contraintes vers presidents_commissions
SET @preparedStatement10 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (CONSTRAINT_NAME = 'convention_specifique_ibfk_vice_president_pc')
  ) > 0,
  'SELECT "La contrainte convention_specifique_ibfk_vice_president_pc existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD CONSTRAINT `convention_specifique_ibfk_vice_president_pc` FOREIGN KEY (`', @column1, '`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE addFK4 FROM @preparedStatement10;
EXECUTE addFK4;
DEALLOCATE PREPARE addFK4;

SET @preparedStatement11 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (CONSTRAINT_NAME = 'convention_specifique_ibfk_president_commission_pc')
  ) > 0,
  'SELECT "La contrainte convention_specifique_ibfk_president_commission_pc existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD CONSTRAINT `convention_specifique_ibfk_president_commission_pc` FOREIGN KEY (`', @column2, '`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE addFK5 FROM @preparedStatement11;
EXECUTE addFK5;
DEALLOCATE PREPARE addFK5;

SET @preparedStatement12 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (CONSTRAINT_NAME = 'convention_specifique_ibfk_secretaire_president')
  ) > 0,
  'SELECT "La contrainte convention_specifique_ibfk_secretaire_president existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD CONSTRAINT `convention_specifique_ibfk_secretaire_president` FOREIGN KEY (`', @column3, '`) REFERENCES `presidents_commissions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE addFK6 FROM @preparedStatement12;
EXECUTE addFK6;
DEALLOCATE PREPARE addFK6;

-- Message de confirmation
SELECT "Migration 035: Modification des responsables des conventions terminée avec succès." AS message;