-- Migration: Création des tables de relations pour avenants
-- Date: 2025-01-XX
-- Description: Crée toutes les tables de relations nécessaires pour avenants
--              (programmes, prefectures, communes, secteurs, domaines, partenaires, etc.)

SET @dbname = DATABASE();

-- Table avenant_programmes
SET @tablename1 = 'avenant_programmes';
SET @preparedStatement1 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename1)
  ) > 0,
  'SELECT "La table avenant_programmes existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename1, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `programme_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_programme`(`avenant_id` ASC, `programme_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_programme`(`programme_id` ASC) USING BTREE,
    CONSTRAINT `avenant_programmes_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_prefectures
SET @tablename2 = 'avenant_prefectures';
SET @preparedStatement2 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename2)
  ) > 0,
  'SELECT "La table avenant_prefectures existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename2, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `prefecture_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_prefecture`(`avenant_id` ASC, `prefecture_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_prefecture`(`prefecture_id` ASC) USING BTREE,
    CONSTRAINT `avenant_prefectures_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_communes
SET @tablename3 = 'avenant_communes';
SET @preparedStatement3 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename3)
  ) > 0,
  'SELECT "La table avenant_communes existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename3, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `commune_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_commune`(`avenant_id` ASC, `commune_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_commune`(`commune_id` ASC) USING BTREE,
    CONSTRAINT `avenant_communes_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_secteurs_activites
SET @tablename4 = 'avenant_secteurs_activites';
SET @preparedStatement4 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename4)
  ) > 0,
  'SELECT "La table avenant_secteurs_activites existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename4, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `secteur_activite_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_secteur`(`avenant_id` ASC, `secteur_activite_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_secteur`(`secteur_activite_id` ASC) USING BTREE,
    CONSTRAINT `avenant_secteurs_activites_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_domaines_competences
SET @tablename5 = 'avenant_domaines_competences';
SET @preparedStatement5 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename5)
  ) > 0,
  'SELECT "La table avenant_domaines_competences existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename5, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `domaine_competence_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_domaine`(`avenant_id` ASC, `domaine_competence_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_domaine`(`domaine_competence_id` ASC) USING BTREE,
    CONSTRAINT `avenant_domaines_competences_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_maitres_ouvrage
SET @tablename6 = 'avenant_maitres_ouvrage';
SET @preparedStatement6 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename6)
  ) > 0,
  'SELECT "La table avenant_maitres_ouvrage existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename6, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_maitre_ouvrage`(`avenant_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `avenant_maitres_ouvrage_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_maitres_ouvrage_delegues
SET @tablename7 = 'avenant_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 avenant_maitres_ouvrage_delegues existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename7, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX `unique_avenant_maitre_ouvrage_delegue`(`avenant_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `avenant_maitres_ouvrage_delegues_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_partenaire
SET @tablename8 = 'avenant_partenaire';
SET @preparedStatement8 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename8)
  ) > 0,
  'SELECT "La table avenant_partenaire existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename8, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `partenaire_id` int NOT NULL,
    `date_signature` date NULL DEFAULT NULL,
    `entity_type` enum(\'Avenant\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT \'Avenant\',
    `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_avenant_partenaire`(`avenant_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `avenant_partenaire_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_contribution_partenaire
SET @tablename9 = 'avenant_contribution_partenaire';
SET @preparedStatement9 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename9)
  ) > 0,
  'SELECT "La table avenant_contribution_partenaire existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename9, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_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_avenant_partenaire_contrib`(`avenant_id` ASC, `partenaire_id` ASC) USING BTREE,
    INDEX `idx_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE,
    CONSTRAINT `avenant_contribution_partenaire_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 avenant_contribution_annuelle
SET @tablename10 = 'avenant_contribution_annuelle';
SET @preparedStatement10 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename10)
  ) > 0,
  'SELECT "La table avenant_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 `avenant_contribution_annuelle_ibfk_1` FOREIGN KEY (`contribution_partenaire_id`) REFERENCES `avenant_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 avenant_comites
SET @tablename11 = 'avenant_comites';
SET @preparedStatement11 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename11)
  ) > 0,
  'SELECT "La table avenant_comites existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename11, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_id` int NOT NULL,
    `type_comite` enum(\'suivi\',\'pilotage\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `fonction_president` int NULL DEFAULT NULL,
    `frequence_reunion` 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_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_type`(`type_comite` ASC) USING BTREE,
    CONSTRAINT `avenant_comites_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_comites_ibfk_2` FOREIGN KEY (`fonction_president`) REFERENCES `partenaires` (`id`) ON DELETE SET NULL 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 avenant_documents
SET @tablename12 = 'avenant_documents';
SET @preparedStatement12 = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
    WHERE (TABLE_SCHEMA = @dbname) AND (TABLE_NAME = @tablename12)
  ) > 0,
  'SELECT "La table avenant_documents existe déjà." AS message;',
  CONCAT('CREATE TABLE `', @tablename12, '` (
    `id` int NOT NULL AUTO_INCREMENT,
    `avenant_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(\'Avenant\') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT \'Avenant\',
    `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_avenant`(`avenant_id` ASC) USING BTREE,
    INDEX `idx_type`(`type_document` ASC) USING BTREE,
    CONSTRAINT `avenant_documents_ibfk_1` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
    CONSTRAINT `avenant_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 createIfNotExists11 FROM @preparedStatement11;
EXECUTE createIfNotExists11;
DEALLOCATE PREPARE createIfNotExists11;

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