-- Migration: Ajout des comités et responsables pour les conventions
-- Date: 2025-01-XX
-- Description: Crée la table convention_comites pour stocker les comités (suivi et pilotage)
--              et ajoute les champs pour vice président CRCS et président commission permanente.

-- Vérifier si la table convention_comites existe déjà
SET @dbname = DATABASE();
SET @tablename = 'convention_comites';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
  ) > 0,
  'SELECT "La table convention_comites existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `convention_id` int NOT NULL,
    `type_comite` enum(\'suivi\',\'pilotage\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT "Type de comité",
    `fonction_president` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Fonction du président",
    `frequence_reunion` int NULL DEFAULT NULL COMMENT "Fréquence de réunion (nombre par an)",
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_convention_comite`(`convention_id` ASC, `type_comite` ASC) USING BTREE,
    INDEX `idx_convention`(`convention_id` ASC) USING BTREE,
    CONSTRAINT `convention_comites_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists FROM @preparedStatement;
EXECUTE createIfNotExists;
DEALLOCATE PREPARE createIfNotExists;

-- Ajouter les champs vice président et président commission dans la table conventions
SET @column1 = 'vice_president_crcs_id';
SET @column2 = 'president_commission_permanente_id';
SET @tablename2 = 'conventions';

-- Vérifier et ajouter vice_president_crcs_id
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (COLUMN_NAME = @column1)
  ) > 0,
  'SELECT "La colonne vice_president_crcs_id existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD COLUMN `', @column1, '` int NULL DEFAULT NULL COMMENT "Vice président du CRCS selon domaine de compétence" AFTER `delai_realisation_mois`, ADD INDEX `idx_vice_president`(`', @column1, '` ASC) USING BTREE, ADD CONSTRAINT `conventions_ibfk_vice_president` FOREIGN KEY (`', @column1, '`) REFERENCES `partenaires` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement2;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Vérifier et ajouter president_commission_permanente_id
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (COLUMN_NAME = @column2)
  ) > 0,
  'SELECT "La colonne president_commission_permanente_id existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD COLUMN `', @column2, '` int NULL DEFAULT NULL COMMENT "Président de la commission permanente" AFTER `vice_president_crcs_id`, ADD INDEX `idx_president_commission`(`', @column2, '` ASC) USING BTREE, ADD CONSTRAINT `conventions_ibfk_president_commission` FOREIGN KEY (`', @column2, '`) REFERENCES `partenaires` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists2 FROM @preparedStatement3;
EXECUTE alterIfNotExists2;
DEALLOCATE PREPARE alterIfNotExists2;

-- Message de confirmation
SELECT "Migration 003: Ajout des comités et responsables terminée avec succès." AS message;