-- Migration: Ajout des champs de suivi des conventions spécifiques après vote jusqu'au VISA ou rejet
-- Date: 2025-01-XX
-- Description: Ajoute les champs nécessaires pour le suivi des conventions spécifiques après vote

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

-- Ajouter les champs de suivi si ils n'existent pas
SET @column1 = 'date_envoi_premiere_signature';
SET @column2 = 'date_derniere_signature_partenaires';
SET @column3 = 'date_signature_crcs';
SET @column4 = 'date_envoi_wilaya_visa';
SET @column5 = 'date_retour_mi';
SET @column6 = 'decision_mi';
SET @column7 = 'motif_rejet_mi';
SET @column8 = 'pdf_courrier_mi';

-- Date d'envoi pour la première signature
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column1)
  ) > 0,
  'SELECT "La colonne date_envoi_premiere_signature existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column1, '` DATE NULL DEFAULT NULL COMMENT "Date d\'envoi pour la première signature" AFTER `date_visionne`;')
));
PREPARE alterIfNotExists1 FROM @preparedStatement1;
EXECUTE alterIfNotExists1;
DEALLOCATE PREPARE alterIfNotExists1;

-- Date de dernière signature partenaires
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column2)
  ) > 0,
  'SELECT "La colonne date_derniere_signature_partenaires existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column2, '` DATE NULL DEFAULT NULL COMMENT "Date de dernière signature partenaires" AFTER `', @column1, '`;')
));
PREPARE alterIfNotExists2 FROM @preparedStatement2;
EXECUTE alterIfNotExists2;
DEALLOCATE PREPARE alterIfNotExists2;

-- Date de signature CRCS
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 date_signature_crcs existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column3, '` DATE NULL DEFAULT NULL COMMENT "Date de signature CRCS" AFTER `', @column2, '`;')
));
PREPARE alterIfNotExists3 FROM @preparedStatement3;
EXECUTE alterIfNotExists3;
DEALLOCATE PREPARE alterIfNotExists3;

-- Date envoie à la Wilaya pour Visa
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column4)
  ) > 0,
  'SELECT "La colonne date_envoi_wilaya_visa existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column4, '` DATE NULL DEFAULT NULL COMMENT "Date envoie à la Wilaya pour Visa" AFTER `', @column3, '`;')
));
PREPARE alterIfNotExists4 FROM @preparedStatement4;
EXECUTE alterIfNotExists4;
DEALLOCATE PREPARE alterIfNotExists4;

-- Date retour MI
SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column5)
  ) > 0,
  'SELECT "La colonne date_retour_mi existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column5, '` DATE NULL DEFAULT NULL COMMENT "Date retour MI (Ministère d\'Intérieur)" AFTER `', @column4, '`;')
));
PREPARE alterIfNotExists5 FROM @preparedStatement5;
EXECUTE alterIfNotExists5;
DEALLOCATE PREPARE alterIfNotExists5;

-- Décision MI
SET @preparedStatement6 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column6)
  ) > 0,
  'SELECT "La colonne decision_mi existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column6, '` ENUM(\'Visée\', \'rejetée\') NULL DEFAULT NULL COMMENT "Décision MI (Ministère d\'Intérieur)" AFTER `', @column5, '`;')
));
PREPARE alterIfNotExists6 FROM @preparedStatement6;
EXECUTE alterIfNotExists6;
DEALLOCATE PREPARE alterIfNotExists6;

-- Motif du rejet MI
SET @preparedStatement7 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column7)
  ) > 0,
  'SELECT "La colonne motif_rejet_mi existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column7, '` TEXT NULL DEFAULT NULL COMMENT "Motif du rejet par le MI" AFTER `', @column6, '`;')
));
PREPARE alterIfNotExists7 FROM @preparedStatement7;
EXECUTE alterIfNotExists7;
DEALLOCATE PREPARE alterIfNotExists7;

-- PDF courrier du MI
SET @preparedStatement8 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename) AND (COLUMN_NAME = @column8)
  ) > 0,
  'SELECT "La colonne pdf_courrier_mi existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column8, '` VARCHAR(255) NULL DEFAULT NULL COMMENT "PDF courrier du MI" AFTER `', @column7, '`;')
));
PREPARE alterIfNotExists8 FROM @preparedStatement8;
EXECUTE alterIfNotExists8;
DEALLOCATE PREPARE alterIfNotExists8;

-- Message de confirmation
SELECT "Migration 040: Ajout des champs de suivi des conventions spécifiques terminée avec succès." AS message;