-- Migration: Création des tables pour le suivi des points de blocage et solutions préconisées
-- Date: 2025-01-XX
-- Description: Crée les tables pour stocker les obstacles/blocages, leurs traitements et responsables

SET @dbname = DATABASE();

-- Table principale des blocages
SET @tablename1 = 'convention_blocages';
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename1)
  ) > 0,
  'SELECT "La table convention_blocages existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename1, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `convention_id` int NOT NULL COMMENT "ID de la convention",
    `numero` int NOT NULL COMMENT "Numéro de l\'obstacle",
    `description` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Description de l\'obstacle",
    `causes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Causes de l\'obstacle",
    `statut` ENUM(\'En cours\',\'Fermé\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Statut de l\'obstacle",
    `ordre` int NOT NULL DEFAULT 0 COMMENT "Ordre d\'affichage",
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_convention`(`convention_id` ASC) USING BTREE,
    INDEX `idx_ordre`(`convention_id` ASC, `ordre` ASC) USING BTREE,
    CONSTRAINT `convention_blocages_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists1 FROM @preparedStatement1;
EXECUTE createIfNotExists1;
DEALLOCATE PREPARE createIfNotExists1;

-- Table des traitements pour chaque blocage
SET @tablename2 = 'convention_blocage_traitements';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename2)
  ) > 0,
  'SELECT "La table convention_blocage_traitements existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename2, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `blocage_id` int NOT NULL COMMENT "ID du blocage",
    `traitement` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Traitement proposé",
    `ordre` int NOT NULL DEFAULT 0 COMMENT "Ordre d\'affichage",
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_blocage`(`blocage_id` ASC) USING BTREE,
    CONSTRAINT `convention_blocage_traitements_ibfk_1` FOREIGN KEY (`blocage_id`) REFERENCES `convention_blocages` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists2 FROM @preparedStatement2;
EXECUTE createIfNotExists2;
DEALLOCATE PREPARE createIfNotExists2;

-- Table des responsables pour chaque blocage
SET @tablename3 = 'convention_blocage_responsables';
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename3)
  ) > 0,
  'SELECT "La table convention_blocage_responsables existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename3, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `blocage_id` int NOT NULL COMMENT "ID du blocage",
    `partenaire_id` int NOT NULL COMMENT "ID du partenaire responsable",
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_blocage_responsable`(`blocage_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_blocage`(`blocage_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `convention_blocage_responsables_ibfk_1` FOREIGN KEY (`blocage_id`) REFERENCES `convention_blocages` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `convention_blocage_responsables_ibfk_2` FOREIGN KEY (`partenaire_id`) REFERENCES `partenaires` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists3 FROM @preparedStatement3;
EXECUTE createIfNotExists3;
DEALLOCATE PREPARE createIfNotExists3;

-- Message de confirmation
SELECT "Migration 012: Création des tables pour le suivi des blocages terminée avec succès." AS message;