-- Migration: Création des tables de relations pour programmations
-- Date: 2025-01-XX
-- Description: Crée toutes les tables de relations nécessaires pour programmations
--              (identique aux tables de conventions mais pour programmations)

SET @dbname = DATABASE();

-- Table programmation_programmes
SET @tablename1 = 'programmation_programmes';
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename1)
  ) > 0,
  'SELECT "La table programmation_programmes existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename1, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `programme_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_programme`(`programmation_id` ASC, `programme_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_programme`(`programme_id` ASC) USING BTREE,
    CONSTRAINT `programmation_programmes_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_programmes_ibfk_2` FOREIGN KEY (`programme_id`) REFERENCES `programmes_pdr` (`id`) ON DELETE RESTRICT 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 programmation_prefectures
SET @tablename2 = 'programmation_prefectures';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename2)
  ) > 0,
  'SELECT "La table programmation_prefectures existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename2, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `prefecture_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_prefecture`(`programmation_id` ASC, `prefecture_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_prefecture`(`prefecture_id` ASC) USING BTREE,
    CONSTRAINT `programmation_prefectures_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_prefectures_ibfk_2` FOREIGN KEY (`prefecture_id`) REFERENCES `prefectures` (`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;

-- Table programmation_communes
SET @tablename3 = 'programmation_communes';
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename3)
  ) > 0,
  'SELECT "La table programmation_communes existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename3, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `commune_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_commune`(`programmation_id` ASC, `commune_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_commune`(`commune_id` ASC) USING BTREE,
    CONSTRAINT `programmation_communes_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_communes_ibfk_2` FOREIGN KEY (`commune_id`) REFERENCES `communes` (`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;

-- Table programmation_secteurs_activites
SET @tablename4 = 'programmation_secteurs_activites';
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename4)
  ) > 0,
  'SELECT "La table programmation_secteurs_activites existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename4, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `secteur_activite_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_secteur`(`programmation_id` ASC, `secteur_activite_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_secteur`(`secteur_activite_id` ASC) USING BTREE,
    CONSTRAINT `programmation_secteurs_activites_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_secteurs_activites_ibfk_2` FOREIGN KEY (`secteur_activite_id`) REFERENCES `secteurs_activites` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists4 FROM @preparedStatement4;
EXECUTE createIfNotExists4;
DEALLOCATE PREPARE createIfNotExists4;

-- Table programmation_domaines_competences
SET @tablename5 = 'programmation_domaines_competences';
SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename5)
  ) > 0,
  'SELECT "La table programmation_domaines_competences existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename5, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `domaine_competence_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_domaine`(`programmation_id` ASC, `domaine_competence_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_domaine`(`domaine_competence_id` ASC) USING BTREE,
    CONSTRAINT `programmation_domaines_competences_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_domaines_competences_ibfk_2` FOREIGN KEY (`domaine_competence_id`) REFERENCES `domaines_competences` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists5 FROM @preparedStatement5;
EXECUTE createIfNotExists5;
DEALLOCATE PREPARE createIfNotExists5;

-- Table programmation_maitres_ouvrage
SET @tablename6 = 'programmation_maitres_ouvrage';
SET @preparedStatement6 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename6)
  ) > 0,
  'SELECT "La table programmation_maitres_ouvrage existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename6, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_maitre_ouvrage`(`programmation_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `programmation_maitres_ouvrage_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_maitres_ouvrage_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 createIfNotExists6 FROM @preparedStatement6;
EXECUTE createIfNotExists6;
DEALLOCATE PREPARE createIfNotExists6;

-- Table programmation_maitres_ouvrage_delegues
SET @tablename7 = 'programmation_maitres_ouvrage_delegues';
SET @preparedStatement7 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename7)
  ) > 0,
  'SELECT "La table programmation_maitres_ouvrage_delegues existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename7, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_programmation_maitre_ouvrage_delegue`(`programmation_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `programmation_maitres_ouvrage_delegues_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_maitres_ouvrage_delegues_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 createIfNotExists7 FROM @preparedStatement7;
EXECUTE createIfNotExists7;
DEALLOCATE PREPARE createIfNotExists7;

-- Table programmation_partenaire
SET @tablename8 = 'programmation_partenaire';
SET @preparedStatement8 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename8)
  ) > 0,
  'SELECT "La table programmation_partenaire existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename8, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    `date_signature` date NULL DEFAULT NULL,
    `entity_type` enum(\'Programmation\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT \'Programmation\',
    `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_programmation_partenaire`(`programmation_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `programmation_partenaire_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_partenaire_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 createIfNotExists8 FROM @preparedStatement8;
EXECUTE createIfNotExists8;
DEALLOCATE PREPARE createIfNotExists8;

-- Table programmation_contribution_partenaire
SET @tablename9 = 'programmation_contribution_partenaire';
SET @preparedStatement9 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename9)
  ) > 0,
  'SELECT "La table programmation_contribution_partenaire existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename9, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    `contribution_totale` decimal(15, 2) NULL DEFAULT NULL,
    `est_engagement` tinyint(1) NULL DEFAULT 0,
    `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_programmation_partenaire_contrib`(`programmation_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `programmation_contribution_partenaire_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_contribution_partenaire_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 createIfNotExists9 FROM @preparedStatement9;
EXECUTE createIfNotExists9;
DEALLOCATE PREPARE createIfNotExists9;

-- Table programmation_contribution_annuelle
SET @tablename10 = 'programmation_contribution_annuelle';
SET @preparedStatement10 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename10)
  ) > 0,
  'SELECT "La table programmation_contribution_annuelle existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename10, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `contribution_partenaire_id` int NOT NULL,
    `annee` int NOT NULL,
    `montant` decimal(15, 2) NULL DEFAULT NULL,
    `date_versement` date NULL DEFAULT NULL,
    `est_verse` tinyint(1) NULL DEFAULT 0,
    `est_engage` tinyint(1) NULL DEFAULT 0,
    `preuve_versement` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `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_contribution_annee`(`contribution_partenaire_id` ASC, `annee` ASC) USING BTREE,
    INDEX `idx_contribution`(`contribution_partenaire_id` ASC) USING BTREE,
    CONSTRAINT `programmation_contribution_annuelle_ibfk_1` FOREIGN KEY (`contribution_partenaire_id`) REFERENCES `programmation_contribution_partenaire` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists10 FROM @preparedStatement10;
EXECUTE createIfNotExists10;
DEALLOCATE PREPARE createIfNotExists10;

-- Table programmation_indicateurs_performance
SET @tablename11 = 'programmation_indicateurs_performance';
SET @preparedStatement11 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename11)
  ) > 0,
  'SELECT "La table programmation_indicateurs_performance existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename11, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `indicateur_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `indicateur_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `objectif` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `realise` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `ordre` int NOT NULL DEFAULT 0,
    `entity_type` enum(\'Programmation\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT \'Programmation\',
    `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_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_ordre`(`programmation_id` ASC, `ordre` ASC) USING BTREE,
    CONSTRAINT `programmation_indicateurs_performance_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists11 FROM @preparedStatement11;
EXECUTE createIfNotExists11;
DEALLOCATE PREPARE createIfNotExists11;

-- Table programmation_comites
SET @tablename12 = 'programmation_comites';
SET @preparedStatement12 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename12)
  ) > 0,
  'SELECT "La table programmation_comites existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename12, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `type_comite` enum(\'suivi\',\'pilotage\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `fonction_president` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `frequence_reunion` int NULL DEFAULT NULL,
    `entity_type` enum(\'Programmation\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT \'Programmation\',
    `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_programmation_comite`(`programmation_id` ASC, `type_comite` ASC) USING BTREE,
    INDEX `idx_programmation`(`programmation_id` ASC) USING BTREE,
    CONSTRAINT `programmation_comites_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists12 FROM @preparedStatement12;
EXECUTE createIfNotExists12;
DEALLOCATE PREPARE createIfNotExists12;

-- Table programmation_comite_reunions
SET @tablename13 = 'programmation_comite_reunions';
SET @preparedStatement13 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename13)
  ) > 0,
  'SELECT "La table programmation_comite_reunions existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename13, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `comite_id` int NOT NULL,
    `ordre_reunion` int NOT NULL,
    `date_previsionnelle` date NULL DEFAULT NULL,
    `date_effective` date NULL DEFAULT NULL,
    `pv_comite` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
    `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_comite`(`comite_id` ASC) USING BTREE,
    INDEX `idx_ordre`(`comite_id` ASC, `ordre_reunion` ASC) USING BTREE,
    UNIQUE INDEX `unique_comite_ordre`(`comite_id` ASC, `ordre_reunion` ASC) USING BTREE,
    CONSTRAINT `programmation_comite_reunions_ibfk_1` FOREIGN KEY (`comite_id`) REFERENCES `programmation_comites` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists13 FROM @preparedStatement13;
EXECUTE createIfNotExists13;
DEALLOCATE PREPARE createIfNotExists13;

-- Table programmation_documents
SET @tablename14 = 'programmation_documents';
SET @preparedStatement14 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename14)
  ) > 0,
  'SELECT "La table programmation_documents existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename14, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `programmation_id` int NOT NULL,
    `nom_document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `type_document` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 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,
    `entity_type` enum(\'Programmation\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT \'Programmation\',
    `uploaded_by` int NULL DEFAULT NULL,
    `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_programmation`(`programmation_id` ASC) USING BTREE,
    INDEX `idx_type`(`type_document` ASC) USING BTREE,
    CONSTRAINT `programmation_documents_ibfk_1` FOREIGN KEY (`programmation_id`) REFERENCES `programmations` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `programmation_documents_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT
  ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;')
));
PREPARE createIfNotExists14 FROM @preparedStatement14;
EXECUTE createIfNotExists14;
DEALLOCATE PREPARE createIfNotExists14;

-- Message de confirmation
SELECT "Migration 048: Création des tables de relations pour programmations terminée avec succès." AS message;