-- Migration: Étendre la table avenants avec tous les champs d'identification
-- Date: 2025-01-XX
-- Description: Ajoute tous les champs d'identification nécessaires pour copier les données
--              des conventions/programmations vers les avenants

SET @dbname = DATABASE();
SET @tablename = 'avenants';

-- 1. Ajouter plan_id
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'plan_id')
  ) > 0,
  'SELECT "La colonne plan_id existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `plan_id` INT NULL DEFAULT NULL AFTER `decision_id`,
   ADD INDEX `idx_plan` (`plan_id`),
   ADD CONSTRAINT `fk_avenants_plan` 
     FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 2. Ajouter hors_pdr
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'hors_pdr')
  ) > 0,
  'SELECT "La colonne hors_pdr existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `hors_pdr` TINYINT(1) NULL DEFAULT 0 AFTER `plan_id`,
   ADD INDEX `idx_hors_pdr` (`hors_pdr`);')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 3. Ajouter date_session
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'date_session')
  ) > 0,
  'SELECT "La colonne date_session existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `date_session` DATE NULL DEFAULT NULL AFTER `hors_pdr`;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 4. Ajouter nature
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'nature')
  ) > 0,
  'SELECT "La colonne nature existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `nature` ENUM(\'Matérielle\', \'Immatérielle\') 
   CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL 
   AFTER `date_session`;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 5. Ajouter type_convention (pour les conventions normales)
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'type_convention')
  ) > 0,
  'SELECT "La colonne type_convention existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `type_convention` VARCHAR(100) NULL DEFAULT NULL 
   AFTER `nature`,
   ADD INDEX `idx_type_convention` (`type_convention`);')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 6. Ajouter delai_realisation_mois
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'delai_realisation_mois')
  ) > 0,
  'SELECT "La colonne delai_realisation_mois existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `delai_realisation_mois` DECIMAL(5, 2) NULL DEFAULT NULL 
   AFTER `type_convention`;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 7. Ajouter session_id
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'session_id')
  ) > 0,
  'SELECT "La colonne session_id existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `session_id` INT NULL DEFAULT NULL 
   AFTER `delai_realisation_mois`,
   ADD INDEX `idx_session` (`session_id`),
   ADD CONSTRAINT `fk_avenants_session` 
     FOREIGN KEY (`session_id`) REFERENCES `sessions_crcs` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 8. Ajouter vice_president_crcs_id
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'vice_president_crcs_id')
  ) > 0,
  'SELECT "La colonne vice_president_crcs_id existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `vice_president_crcs_id` INT NULL DEFAULT NULL 
   AFTER `session_id`,
   ADD INDEX `idx_vice_president` (`vice_president_crcs_id`),
   ADD CONSTRAINT `fk_avenants_vice_president` 
     FOREIGN KEY (`vice_president_crcs_id`) REFERENCES `presidents_commissions` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 9. Ajouter president_commission_permanente_id
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'president_commission_permanente_id')
  ) > 0,
  'SELECT "La colonne president_commission_permanente_id existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `president_commission_permanente_id` INT NULL DEFAULT NULL 
   AFTER `vice_president_crcs_id`,
   ADD INDEX `idx_president_commission` (`president_commission_permanente_id`),
   ADD CONSTRAINT `fk_avenants_president_commission` 
     FOREIGN KEY (`president_commission_permanente_id`) REFERENCES `presidents_commissions` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 10. Ajouter secretaire_president_id
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'secretaire_president_id')
  ) > 0,
  'SELECT "La colonne secretaire_president_id existe déjà dans avenants." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` 
   ADD COLUMN `secretaire_president_id` INT NULL DEFAULT NULL 
   AFTER `president_commission_permanente_id`,
   ADD INDEX `idx_secretaire_president` (`secretaire_president_id`),
   ADD CONSTRAINT `fk_avenants_secretaire_president` 
     FOREIGN KEY (`secretaire_president_id`) REFERENCES `presidents_commissions` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Message de confirmation
SELECT "Migration 056: Extension de la table avenants avec les champs d'identification terminée avec succès." AS message;