-- Migration: Ajout de la table convention_achevement
-- Date: 2025-01-XX
-- Description: Crée la table convention_achevement pour stocker les informations d'achèvement des conventions (notes, remarques, date, utilisateur).

SET @dbname = DATABASE();
SET @tablename = 'convention_achevement';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
  ) > 0,
  'SELECT "La table convention_achevement existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `convention_id` int NOT NULL COMMENT "ID de la convention",
    `notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT "Notes et remarques sur l\'achèvement",
    `date_achevement` date NOT NULL COMMENT "Date d\'achèvement de la convention",
    `acheve_par` int NOT NULL COMMENT "ID de l\'utilisateur qui a marqué la convention comme achevée",
    `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 KEY `idx_convention_unique` (`convention_id`) USING BTREE,
    INDEX `idx_acheve_par`(`acheve_par` ASC) USING BTREE,
    CONSTRAINT `convention_achevement_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `convention_achevement_ibfk_2` FOREIGN KEY (`acheve_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;

-- Message de confirmation
SELECT "Migration 039: Création de la table convention_achevement terminée avec succès." AS message;