-- Migration: Ajouter archived_by_avenant_id à toutes les tables sources
-- Date: 2025-01-XX
-- Description: Ajoute la colonne archived_by_avenant_id pour gérer l'archivage des entités par les avenants
--              Permet de savoir si une convention/programmation/avenant est archivée sans utiliser de statut

SET @dbname = DATABASE();

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

-- 2. Ajouter archived_by_avenant_id à la table conventions
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = 'conventions')
      AND (COLUMN_NAME = 'archived_by_avenant_id')
  ) > 0,
  'SELECT "La colonne archived_by_avenant_id existe déjà dans conventions." AS message;',
  'ALTER TABLE `conventions` 
   ADD COLUMN `archived_by_avenant_id` INT NULL DEFAULT NULL,
   ADD INDEX `idx_archived_by_avenant` (`archived_by_avenant_id`),
   ADD CONSTRAINT `fk_conventions_archived_by_avenant` 
     FOREIGN KEY (`archived_by_avenant_id`) REFERENCES `avenants` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 3. Ajouter archived_by_avenant_id à la table convention_specifique
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = 'convention_specifique')
      AND (COLUMN_NAME = 'archived_by_avenant_id')
  ) > 0,
  'SELECT "La colonne archived_by_avenant_id existe déjà dans convention_specifique." AS message;',
  'ALTER TABLE `convention_specifique` 
   ADD COLUMN `archived_by_avenant_id` INT NULL DEFAULT NULL,
   ADD INDEX `idx_archived_by_avenant` (`archived_by_avenant_id`),
   ADD CONSTRAINT `fk_convention_specifique_archived_by_avenant` 
     FOREIGN KEY (`archived_by_avenant_id`) REFERENCES `avenants` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 4. Ajouter archived_by_avenant_id à la table programmations
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = 'programmations')
      AND (COLUMN_NAME = 'archived_by_avenant_id')
  ) > 0,
  'SELECT "La colonne archived_by_avenant_id existe déjà dans programmations." AS message;',
  'ALTER TABLE `programmations` 
   ADD COLUMN `archived_by_avenant_id` INT NULL DEFAULT NULL,
   ADD INDEX `idx_archived_by_avenant` (`archived_by_avenant_id`),
   ADD CONSTRAINT `fk_programmations_archived_by_avenant` 
     FOREIGN KEY (`archived_by_avenant_id`) REFERENCES `avenants` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- 5. Ajouter archived_by_avenant_id à la table avenants (pour permettre les avenants sur avenants)
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = 'avenants')
      AND (COLUMN_NAME = 'archived_by_avenant_id')
  ) > 0,
  'SELECT "La colonne archived_by_avenant_id existe déjà dans avenants." AS message;',
  'ALTER TABLE `avenants` 
   ADD COLUMN `archived_by_avenant_id` INT NULL DEFAULT NULL,
   ADD INDEX `idx_archived_by_avenant` (`archived_by_avenant_id`),
   ADD CONSTRAINT `fk_avenants_archived_by_avenant` 
     FOREIGN KEY (`archived_by_avenant_id`) REFERENCES `avenants` (`id`) 
     ON DELETE SET NULL ON UPDATE RESTRICT;'
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Message de confirmation
SELECT "Migration 054: Ajout de archived_by_avenant_id à toutes les tables terminée avec succès." AS message;