-- Migration: Mise à jour des tables espace d'échange
-- Date: 2025-01-XX
-- Description: Met à jour la structure des tables pour simplifier (un seul titre/contenu) et ajouter catégorie

SET @dbname = DATABASE();

-- Vérifier si les colonnes existent et les modifier
SET @tablename = 'espace_echange_messages';

-- Ajouter la colonne categorie si elle n'existe pas
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'categorie')
  ) > 0,
  'SELECT "La colonne categorie existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `categorie` enum(\'Message\',\'Probleme_technique\',\'Probleme_texte\',\'Question\',\'Autre\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT \'Message\' AFTER `contenu_ar`;')
));
PREPARE alterIfExists1 FROM @preparedStatement1;
EXECUTE alterIfExists1;
DEALLOCATE PREPARE alterIfExists1;

-- Ajouter la colonne titre si elle n'existe pas (nouvelle structure)
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'titre')
  ) > 0,
  'SELECT "La colonne titre existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `titre` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL AFTER `id`;')
));
PREPARE alterIfExists2 FROM @preparedStatement2;
EXECUTE alterIfExists2;
DEALLOCATE PREPARE alterIfExists2;

-- Ajouter la colonne contenu si elle n'existe pas (nouvelle structure)
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'contenu')
  ) > 0,
  'SELECT "La colonne contenu existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `contenu` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL AFTER `titre`;')
));
PREPARE alterIfExists3 FROM @preparedStatement3;
EXECUTE alterIfExists3;
DEALLOCATE PREPARE alterIfExists3;

-- Migrer les données si les anciennes colonnes existent
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'titre_fr')
  ) > 0,
  CONCAT('UPDATE `', @tablename, '` SET `titre` = COALESCE(`titre_fr`, `titre_ar`, \'\'), `contenu` = COALESCE(`contenu_fr`, `contenu_ar`, \'\') WHERE `titre` IS NULL OR `titre` = \'\';'),
  'SELECT "Pas de migration nécessaire." AS message;'
));
PREPARE migrateData FROM @preparedStatement4;
EXECUTE migrateData;
DEALLOCATE PREPARE migrateData;

-- Supprimer les anciennes colonnes si elles existent
SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'titre_fr')
  ) > 0,
  CONCAT('ALTER TABLE `', @tablename, '` DROP COLUMN `titre_fr`, DROP COLUMN `titre_ar`, DROP COLUMN `contenu_fr`, DROP COLUMN `contenu_ar`;'),
  'SELECT "Les anciennes colonnes n\'existent pas." AS message;'
));
PREPARE dropOldColumns FROM @preparedStatement5;
EXECUTE dropOldColumns;
DEALLOCATE PREPARE dropOldColumns;

-- Mettre à jour la table commentaires
SET @tablename2 = 'espace_echange_commentaires';

-- Ajouter la colonne contenu si elle n'existe pas
SET @preparedStatement6 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (COLUMN_NAME = 'contenu')
  ) > 0,
  'SELECT "La colonne contenu existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename2, '` ADD COLUMN `contenu` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL AFTER `message_id`;')
));
PREPARE alterIfExists6 FROM @preparedStatement6;
EXECUTE alterIfExists6;
DEALLOCATE PREPARE alterIfExists6;

-- Migrer les données des commentaires
SET @preparedStatement7 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (COLUMN_NAME = 'contenu_fr')
  ) > 0,
  CONCAT('UPDATE `', @tablename2, '` SET `contenu` = COALESCE(`contenu_fr`, `contenu_ar`, \'\') WHERE `contenu` IS NULL OR `contenu` = \'\';'),
  'SELECT "Pas de migration nécessaire." AS message;'
));
PREPARE migrateCommentData FROM @preparedStatement7;
EXECUTE migrateCommentData;
DEALLOCATE PREPARE migrateCommentData;

-- Supprimer les anciennes colonnes des commentaires
SET @preparedStatement8 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
      AND (COLUMN_NAME = 'contenu_fr')
  ) > 0,
  CONCAT('ALTER TABLE `', @tablename2, '` DROP COLUMN `contenu_fr`, DROP COLUMN `contenu_ar`;'),
  'SELECT "Les anciennes colonnes n\'existent pas." AS message;'
));
PREPARE dropOldCommentColumns FROM @preparedStatement8;
EXECUTE dropOldCommentColumns;
DEALLOCATE PREPARE dropOldCommentColumns;