-- Migration: Création de la table session_mediatheque
-- Date: 2025-01-XX
-- Description: Crée la table session_mediatheque pour stocker les fichiers de la médiathèque liés aux sessions

SET @dbname = DATABASE();
SET @tablename = 'session_mediatheque';
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
  ) > 0,
  'SELECT "La table session_mediatheque existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `session_id` int NOT NULL COMMENT "ID de la session",
    `nom_document` VARCHAR(255) NOT NULL COMMENT "Nom original du document",
    `chemin_fichier` VARCHAR(500) NOT NULL COMMENT "Chemin relatif du fichier sur le serveur",
    `taille_fichier` INT NULL DEFAULT NULL COMMENT "Taille du fichier en octets",
    `type_mime` VARCHAR(100) NULL DEFAULT NULL COMMENT "Type MIME du fichier",
    `uploaded_by` INT NOT NULL COMMENT "ID de l\'utilisateur qui a téléchargé le fichier",
    `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `idx_session`(`session_id` ASC) USING BTREE,
    INDEX `idx_uploaded_by`(`uploaded_by` ASC) USING BTREE,
    CONSTRAINT `session_mediatheque_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions_crcs` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `session_mediatheque_ibfk_2` 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 createIfNotExists FROM @preparedStatement;
EXECUTE createIfNotExists;
DEALLOCATE PREPARE createIfNotExists;

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