-- Migration: Créer table sous_prefectures et modifier communes
-- Date: 2025-01-XX
-- Description: 
--   1. Crée la table sous_prefectures pour gérer les sous-préfectures
--   2. Ajoute sous_prefecture_id à la table communes (optionnel, car toutes les préfectures n'ont pas de sous-préfectures)

SET @dbname = DATABASE();

-- Étape 1: Créer la table sous_prefectures
CREATE TABLE IF NOT EXISTS `sous_prefectures` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom_fr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom_ar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `prefecture_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE INDEX `code`(`code`),
  INDEX `idx_prefecture`(`prefecture_id`),
  CONSTRAINT `sous_prefectures_ibfk_1` FOREIGN KEY (`prefecture_id`) REFERENCES `prefectures` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Étape 2: Ajouter la colonne sous_prefecture_id à la table communes (si elle n'existe pas)
SET @tablename = 'communes';
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = 'sous_prefecture_id')
  ) > 0,
  'SELECT "La colonne sous_prefecture_id existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `sous_prefecture_id` int NULL DEFAULT NULL AFTER `prefecture_id`;')
));
PREPARE alterIfExists1 FROM @preparedStatement1;
EXECUTE alterIfExists1;
DEALLOCATE PREPARE alterIfExists1;

-- Étape 3: Ajouter l'index sur sous_prefecture_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_sous_prefecture')
  ) > 0,
  'SELECT "L\'index idx_sous_prefecture existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD INDEX `idx_sous_prefecture` (`sous_prefecture_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 = 'communes_ibfk_sous_prefecture')
  ) > 0,
  'SELECT "La contrainte communes_ibfk_sous_prefecture existe déjà." AS message;',
  CONCAT('ALTER TABLE `', @tablename, '` ADD CONSTRAINT `communes_ibfk_sous_prefecture` FOREIGN KEY (`sous_prefecture_id`) REFERENCES `sous_prefectures` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT;')
));
PREPARE alterIfExists3 FROM @preparedStatement3;
EXECUTE alterIfExists3;
DEALLOCATE PREPARE alterIfExists3;