-- Migration: Créer la table entity_annulations
-- Date: 2025-01-XX
-- Description: Crée la table pour stocker les annulations d'entités (conventions, programmations, avenants) avec notes et fichiers

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

-- Vérifier si la table existe déjà
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename)
  ) > 0,
  'SELECT "La table entity_annulations existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `entity_type` ENUM(\'Convention\', \'Convention_Specifique\', \'Convention_Cadre\', \'Programmation\', \'Avenant\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `entity_id` int NOT NULL,
    `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `annule_par` int NOT NULL,
    `date_annulation` timestamp NOT NULL DEFAULT current_timestamp(),
    `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_entity` (`entity_type`, `entity_id`) USING BTREE,
    INDEX `idx_annule_par` (`annule_par`) USING BTREE,
    INDEX `idx_date_annulation` (`date_annulation`) USING BTREE,
    CONSTRAINT `fk_entity_annulations_user` FOREIGN KEY (`annule_par`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists FROM @preparedStatement;
EXECUTE createIfNotExists;
DEALLOCATE PREPARE createIfNotExists;

-- Créer la table pour les fichiers d'annulation
SET @tablename2 = 'entity_annulation_documents';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename2)
  ) > 0,
  'SELECT "La table entity_annulation_documents existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename2, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `annulation_id` int NOT NULL,
    `nom_document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `chemin_fichier` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `taille_fichier` bigint NULL DEFAULT NULL,
    `type_mime` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `uploaded_by` int NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_annulation` (`annulation_id`) USING BTREE,
    INDEX `idx_uploaded_by` (`uploaded_by`) USING BTREE,
    CONSTRAINT `fk_annulation_documents_annulation` FOREIGN KEY (`annulation_id`) REFERENCES `entity_annulations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `fk_annulation_documents_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT 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;

-- Message de confirmation
SELECT "Migration 060: Création de la table entity_annulations et entity_annulation_documents terminée avec succès." AS message;