-- Migration: Modifier axes_pdr pour supprimer description et permettre relation many-to-many avec plans
-- Date: 2025-01-XX
-- Description: 
--   1. Supprime les colonnes description_fr et description_ar
--   2. Supprime la colonne plan_id et sa contrainte FK
--   3. Crée une table de liaison axe_plan_pdr pour relation many-to-many
--   4. Migre les données existantes de plan_id vers la nouvelle table

-- Étape 1: Créer la table de liaison pour la relation many-to-many
CREATE TABLE IF NOT EXISTS `axe_plan_pdr` (
  `id` int NOT NULL AUTO_INCREMENT,
  `axe_id` int NOT NULL,
  `plan_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_axe_plan` (`axe_id`, `plan_id`),
  KEY `idx_axe` (`axe_id`),
  KEY `idx_plan` (`plan_id`),
  CONSTRAINT `axe_plan_pdr_ibfk_1` FOREIGN KEY (`axe_id`) REFERENCES `axes_pdr` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `axe_plan_pdr_ibfk_2` FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Étape 2: Migrer les données existantes (si un axe a un plan_id, créer une relation)
INSERT INTO `axe_plan_pdr` (`axe_id`, `plan_id`)
SELECT `id`, `plan_id`
FROM `axes_pdr`
WHERE `plan_id` IS NOT NULL
ON DUPLICATE KEY UPDATE `axe_id` = `axe_id`;

-- Étape 3: Supprimer la contrainte de clé étrangère plan_id
SET @constraint_name = (
    SELECT CONSTRAINT_NAME 
    FROM information_schema.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'axes_pdr' 
    AND COLUMN_NAME = 'plan_id' 
    AND CONSTRAINT_NAME != 'PRIMARY'
    LIMIT 1
);

SET @sql = IF(@constraint_name IS NOT NULL, 
    CONCAT('ALTER TABLE `axes_pdr` DROP FOREIGN KEY `', @constraint_name, '`'),
    'SELECT "No foreign key constraint found for plan_id"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Étape 4: Supprimer l'index sur plan_id
SET @index_name = (
    SELECT INDEX_NAME 
    FROM information_schema.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'axes_pdr' 
    AND COLUMN_NAME = 'plan_id' 
    AND INDEX_NAME != 'PRIMARY'
    LIMIT 1
);

SET @sql = IF(@index_name IS NOT NULL, 
    CONCAT('ALTER TABLE `axes_pdr` DROP INDEX `', @index_name, '`'),
    'SELECT "No index found for plan_id"'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Étape 5: Supprimer la colonne plan_id
ALTER TABLE `axes_pdr` DROP COLUMN IF EXISTS `plan_id`;

-- Étape 6: Supprimer les colonnes description
ALTER TABLE `axes_pdr` DROP COLUMN IF EXISTS `description_fr`;
ALTER TABLE `axes_pdr` DROP COLUMN IF EXISTS `description_ar`;