-- Migration: Créer table partenaires_categories et modifier partenaires
-- Date: 2025-01-XX
-- Description: 
--   1. Crée la table partenaires_categories pour gérer les catégories de partenaires
--   2. Ajoute categorie_id à la table partenaires
--   3. Supprime les colonnes description_fr et description_ar de partenaires

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

-- Étape 1: Créer la table partenaires_categories
CREATE TABLE IF NOT EXISTS `partenaires_categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nom_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Étape 2: Ajouter la colonne categorie_id à la table partenaires (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_id')
  ) > 0,
  'SELECT "La colonne categorie_id existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `categorie_id` int NULL DEFAULT NULL AFTER `type_partenaire`;')
));
PREPARE alterIfExists1 FROM @preparedStatement1;
EXECUTE alterIfExists1;
DEALLOCATE PREPARE alterIfExists1;

-- Étape 3: Ajouter l'index sur categorie_id (s'il n'existe pas)
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (INDEX_NAME = 'idx_categorie')
  ) > 0,
  'SELECT "L\'index idx_categorie existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD INDEX `idx_categorie` (`categorie_id`);')
));
PREPARE alterIfExists2 FROM @preparedStatement2;
EXECUTE alterIfExists2;
DEALLOCATE PREPARE alterIfExists2;

-- Étape 4: Ajouter la contrainte de clé étrangère (si elle n'existe pas)
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (CONSTRAINT_NAME = 'partenaires_ibfk_categorie')
  ) > 0,
  'SELECT "La contrainte partenaires_ibfk_categorie existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `partenaires_ibfk_categorie` FOREIGN KEY (`categorie_id`) REFERENCES `partenaires_categories` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfExists3 FROM @preparedStatement3;
EXECUTE alterIfExists3;
DEALLOCATE PREPARE alterIfExists3;

-- Étape 5: Supprimer les colonnes description (si elles existent)
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'description_fr')
  ) > 0,
  CONCAT('ALTER TABLE `', @tablename, '` DROP COLUMN `description_fr`;'),
  'SELECT "La colonne description_fr n\'existe pas." AS message;'
));
PREPARE dropColumn1 FROM @preparedStatement4;
EXECUTE dropColumn1;
DEALLOCATE PREPARE dropColumn1;

SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'description_ar')
  ) > 0,
  CONCAT('ALTER TABLE `', @tablename, '` DROP COLUMN `description_ar`;'),
  'SELECT "La colonne description_ar n\'existe pas." AS message;'
));
PREPARE dropColumn2 FROM @preparedStatement5;
EXECUTE dropColumn2;
DEALLOCATE PREPARE dropColumn2;