-- Migration: Ajout du champ session_id à la table conventions
-- Date: 2025-01-XX
-- Description: Remplace date_session par session_id pour lier la convention à une session

SET @dbname = DATABASE();
SET @tablename = 'conventions';
SET @column = 'session_id';

-- Vérifier et ajouter session_id
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @column)
  ) > 0,
  'SELECT "La colonne session_id existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @column, '` INT NULL DEFAULT NULL COMMENT "Session CRCS" AFTER `decision_id`, ADD INDEX `idx_session`(`', @column, '` ASC) USING BTREE, ADD CONSTRAINT `conventions_ibfk_session` FOREIGN KEY (`', @column, '`) REFERENCES `sessions_crcs` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Message de confirmation
SELECT "Migration 005: Ajout du champ session_id terminée avec succès." AS message;