/* Navicat Premium Dump SQL Source Server : localhost Source Server Type : MySQL Source Server Version : 100432 (10.4.32-MariaDB) Source Host : localhost:3306 Source Schema : crcs_tracking Target Server Type : MySQL Target Server Version : 100432 (10.4.32-MariaDB) File Encoding : 65001 Date: 11/11/2025 21:13:00 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for avenants -- ---------------------------- DROP TABLE IF EXISTS `avenants`; CREATE TABLE `avenants` ( `id` int NOT NULL AUTO_INCREMENT, `numero_avenant` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `convention_mere_id` int NOT NULL, `convention_mere_type` enum('Cadre','Specifique','Generique') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `decision_id` int NULL DEFAULT NULL, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `statut` enum('En cours de signature','En cours de Visa','Visé') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'En cours de signature', `date_signature` date NULL DEFAULT NULL, `date_visa` date NULL DEFAULT NULL, `date_visionne` date NULL DEFAULT NULL, `document_convention_votee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `fiche_technique` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `phase` int NULL DEFAULT 1, `created_by` int NOT 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 `numero_avenant`(`numero_avenant` ASC) USING BTREE, INDEX `decision_id`(`decision_id` ASC) USING BTREE, INDEX `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_numero`(`numero_avenant` ASC) USING BTREE, INDEX `idx_convention_mere`(`convention_mere_id` ASC, `convention_mere_type` ASC) USING BTREE, INDEX `idx_statut`(`statut` ASC) USING BTREE, CONSTRAINT `avenants_ibfk_1` FOREIGN KEY (`decision_id`) REFERENCES `decisions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT, CONSTRAINT `avenants_ibfk_2` FOREIGN KEY (`created_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; -- ---------------------------- -- Records of avenants -- ---------------------------- -- ---------------------------- -- Table structure for axes_pdr -- ---------------------------- DROP TABLE IF EXISTS `axes_pdr`; CREATE TABLE `axes_pdr` ( `id` int NOT NULL AUTO_INCREMENT, `plan_id` int NULL DEFAULT NULL, `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `code`(`code` ASC) USING BTREE, INDEX `idx_plan`(`plan_id` ASC) USING BTREE, CONSTRAINT `axes_pdr_ibfk_1` FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of axes_pdr -- ---------------------------- INSERT INTO `axes_pdr` VALUES (1, NULL, 'Axe1', 'Développement économique et compétitivité', 'التنمية الاقتصادية والتنافسية', NULL, NULL, '2025-11-07 16:04:24'); INSERT INTO `axes_pdr` VALUES (2, NULL, 'Axe2', 'Développement social et humain', 'التنمية الاجتماعية والبشرية', NULL, NULL, '2025-11-07 16:04:24'); INSERT INTO `axes_pdr` VALUES (3, NULL, 'Axe3', 'Aménagement du territoire et développement durable', 'تهيئة المجال والتنمية المستدامة', NULL, NULL, '2025-11-07 16:04:24'); INSERT INTO `axes_pdr` VALUES (4, NULL, 'Axe4', 'Gouvernance et modernisation', 'الحكامة والتحديث', NULL, NULL, '2025-11-07 16:04:24'); -- ---------------------------- -- Table structure for communes -- ---------------------------- DROP TABLE IF EXISTS `communes`; CREATE TABLE `communes` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nom_fr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nom_ar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `prefecture_id` int NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `code`(`code` ASC) USING BTREE, INDEX `idx_prefecture`(`prefecture_id` ASC) USING BTREE, CONSTRAINT `communes_ibfk_1` FOREIGN KEY (`prefecture_id`) REFERENCES `prefectures` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of communes -- ---------------------------- INSERT INTO `communes` VALUES (1, 'CAS01', 'Casablanca-Anfa', 'الدار البيضاء-أنفا', 1, '2025-11-07 16:04:24'); INSERT INTO `communes` VALUES (2, 'CAS02', 'Casablanca-Hay Hassani', 'الدار البيضاء-حي الحسني', 1, '2025-11-07 16:04:24'); INSERT INTO `communes` VALUES (3, 'MOH01', 'Mohammedia', 'المحمدية', 2, '2025-11-07 16:04:24'); INSERT INTO `communes` VALUES (4, 'SET01', 'Settat', 'سطات', 3, '2025-11-07 16:04:24'); INSERT INTO `communes` VALUES (5, 'BER01', 'Benslimane', 'بنسليمان', 4, '2025-11-07 16:04:24'); -- ---------------------------- -- Table structure for contribution_annuelle -- ---------------------------- DROP TABLE IF EXISTS `contribution_annuelle`; CREATE TABLE `contribution_annuelle` ( `id` int NOT NULL AUTO_INCREMENT, `contribution_partenaire_id` int NOT NULL, `annee` int NOT NULL, `montant` decimal(15, 2) NULL DEFAULT 0.00, `verse` tinyint(1) NULL DEFAULT 0, `date_versement` date 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_annee`(`annee` ASC) USING BTREE, CONSTRAINT `contribution_annuelle_ibfk_1` FOREIGN KEY (`contribution_partenaire_id`) REFERENCES `contribution_partenaire` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of contribution_annuelle -- ---------------------------- -- ---------------------------- -- Table structure for contribution_partenaire -- ---------------------------- DROP TABLE IF EXISTS `contribution_partenaire`; CREATE TABLE `contribution_partenaire` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NULL DEFAULT NULL, `convention_specifique_id` int NULL DEFAULT NULL, `avenant_id` int NULL DEFAULT NULL, `projet_id` int NULL DEFAULT NULL, `entity_type` enum('Convention_Cadre','Convention_Specifique','Avenant','Projet') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `partenaire_id` int NOT NULL, `montant_global` decimal(15, 2) NULL DEFAULT 0.00, `contribution_crcs` decimal(15, 2) NULL DEFAULT 0.00, `nombre_annees` int 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, INDEX `convention_cadre_id`(`convention_cadre_id` ASC) USING BTREE, INDEX `convention_specifique_id`(`convention_specifique_id` ASC) USING BTREE, INDEX `avenant_id`(`avenant_id` ASC) USING BTREE, INDEX `projet_id`(`projet_id` ASC) USING BTREE, INDEX `partenaire_id`(`partenaire_id` ASC) USING BTREE, INDEX `idx_entity`(`entity_type` ASC, `convention_cadre_id` ASC, `convention_specifique_id` ASC, `avenant_id` ASC, `projet_id` ASC) USING BTREE, CONSTRAINT `contribution_partenaire_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contribution_partenaire_ibfk_2` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contribution_partenaire_ibfk_3` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contribution_partenaire_ibfk_4` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contribution_partenaire_ibfk_5` 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; -- ---------------------------- -- Records of contribution_partenaire -- ---------------------------- -- ---------------------------- -- Table structure for contributions -- ---------------------------- DROP TABLE IF EXISTS `contributions`; CREATE TABLE `contributions` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NULL DEFAULT NULL, `convention_specifique_id` int NULL DEFAULT NULL, `avenant_id` int NULL DEFAULT NULL, `projet_id` int NULL DEFAULT NULL, `entity_type` enum('Convention_Cadre','Convention_Specifique','Avenant','Projet') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `quote_part_crcs` decimal(15, 2) NULL DEFAULT 0.00, `quote_part_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `quote_part_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `quote_part_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `quote_part_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `quote_part_autres` decimal(15, 2) NULL DEFAULT 0.00, `total_global` decimal(15, 2) NULL DEFAULT 0.00, `annee_2022_crcs` decimal(15, 2) NULL DEFAULT 0.00, `annee_2022_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `annee_2022_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `annee_2022_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `annee_2022_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `annee_2022_autres` decimal(15, 2) NULL DEFAULT 0.00, `annee_2023_crcs` decimal(15, 2) NULL DEFAULT 0.00, `annee_2023_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `annee_2023_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `annee_2023_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `annee_2023_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `annee_2023_autres` decimal(15, 2) NULL DEFAULT 0.00, `annee_2024_crcs` decimal(15, 2) NULL DEFAULT 0.00, `annee_2024_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `annee_2024_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `annee_2024_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `annee_2024_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `annee_2024_autres` decimal(15, 2) NULL DEFAULT 0.00, `annee_2025_crcs` decimal(15, 2) NULL DEFAULT 0.00, `annee_2025_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `annee_2025_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `annee_2025_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `annee_2025_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `annee_2025_autres` decimal(15, 2) NULL DEFAULT 0.00, `annee_2026_crcs` decimal(15, 2) NULL DEFAULT 0.00, `annee_2026_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `annee_2026_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `annee_2026_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `annee_2026_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `annee_2026_autres` decimal(15, 2) NULL DEFAULT 0.00, `annee_2027_crcs` decimal(15, 2) NULL DEFAULT 0.00, `annee_2027_partenaire1` decimal(15, 2) NULL DEFAULT 0.00, `annee_2027_partenaire2` decimal(15, 2) NULL DEFAULT 0.00, `annee_2027_partenaire3` decimal(15, 2) NULL DEFAULT 0.00, `annee_2027_partenaire4` decimal(15, 2) NULL DEFAULT 0.00, `annee_2027_autres` decimal(15, 2) NULL DEFAULT 0.00, `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 `convention_cadre_id`(`convention_cadre_id` ASC) USING BTREE, INDEX `convention_specifique_id`(`convention_specifique_id` ASC) USING BTREE, INDEX `avenant_id`(`avenant_id` ASC) USING BTREE, INDEX `projet_id`(`projet_id` ASC) USING BTREE, INDEX `idx_entity`(`entity_type` ASC, `convention_cadre_id` ASC, `convention_specifique_id` ASC, `avenant_id` ASC, `projet_id` ASC) USING BTREE, CONSTRAINT `contributions_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contributions_ibfk_2` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contributions_ibfk_3` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `contributions_ibfk_4` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of contributions -- ---------------------------- -- ---------------------------- -- Table structure for convention_axes -- ---------------------------- DROP TABLE IF EXISTS `convention_axes`; CREATE TABLE `convention_axes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `axe_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_axe`(`convention_id` ASC, `axe_id` ASC) USING BTREE, INDEX `axe_id`(`axe_id` ASC) USING BTREE, CONSTRAINT `convention_axes_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_axes_ibfk_2` FOREIGN KEY (`axe_id`) REFERENCES `axes_pdr` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_axes -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre`; CREATE TABLE `convention_cadre` ( `id` int NOT NULL AUTO_INCREMENT, `numero_convention` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `decision_id` int NOT NULL, `plan_id` int NULL DEFAULT NULL, `hors_pdr` tinyint(1) NULL DEFAULT 0, `date_session` date NULL DEFAULT NULL, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nature` enum('Matérielle','Immatérielle') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `type_convention` enum('Cadre','Specifique','Avenant') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'Cadre', `statut` enum('Votée','En cours de signature','En cours de Visa','Visé') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'Votée', `date_signature` date NULL DEFAULT NULL, `date_visa` date NULL DEFAULT NULL, `date_visionne` date NULL DEFAULT NULL, `document_convention_votee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `fiche_technique` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `phase` int NULL DEFAULT 1, `created_by` int NOT 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 `numero_convention`(`numero_convention` ASC) USING BTREE, INDEX `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_numero`(`numero_convention` ASC) USING BTREE, INDEX `idx_decision`(`decision_id` ASC) USING BTREE, INDEX `idx_statut`(`statut` ASC) USING BTREE, INDEX `idx_type`(`type_convention` ASC) USING BTREE, INDEX `idx_plan`(`plan_id` ASC) USING BTREE, INDEX `idx_hors_pdr`(`hors_pdr` ASC) USING BTREE, CONSTRAINT `convention_cadre_ibfk_1` FOREIGN KEY (`decision_id`) REFERENCES `decisions` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_ibfk_3` FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_cadre -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_axes -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_axes`; CREATE TABLE `convention_cadre_axes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `axe_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_axe`(`convention_cadre_id` ASC, `axe_id` ASC) USING BTREE, INDEX `axe_id`(`axe_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_axes_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_axes_ibfk_2` FOREIGN KEY (`axe_id`) REFERENCES `axes_pdr` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_cadre_axes -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_communes -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_communes`; CREATE TABLE `convention_cadre_communes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `commune_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_commune`(`convention_cadre_id` ASC, `commune_id` ASC) USING BTREE, INDEX `commune_id`(`commune_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_communes_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_communes -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_domaines_competences -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_domaines_competences`; CREATE TABLE `convention_cadre_domaines_competences` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `domaine_competence_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_cadre_domaine`(`convention_cadre_id` ASC, `domaine_competence_id` ASC) USING BTREE, INDEX `idx_convention_cadre`(`convention_cadre_id` ASC) USING BTREE, INDEX `idx_domaine`(`domaine_competence_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_domaines_competences_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_domaines_competences -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_maitres_ouvrage -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_maitres_ouvrage`; CREATE TABLE `convention_cadre_maitres_ouvrage` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `partenaire_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_cadre_mo`(`convention_cadre_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `idx_convention_cadre`(`convention_cadre_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_maitres_ouvrage_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_maitres_ouvrage -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_maitres_ouvrage_delegues -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_maitres_ouvrage_delegues`; CREATE TABLE `convention_cadre_maitres_ouvrage_delegues` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `partenaire_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_cadre_mod`(`convention_cadre_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `idx_convention_cadre`(`convention_cadre_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_maitres_ouvrage_delegues_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_maitres_ouvrage_delegues -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_mo -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_mo`; CREATE TABLE `convention_cadre_mo` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `mo_id` int NOT NULL, `type_mo` enum('MO','MOD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MO', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_mo`(`convention_cadre_id` ASC, `mo_id` ASC) USING BTREE, INDEX `fk_convention_cadre_mo_partenaire`(`mo_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_mo_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_convention_cadre_mo_partenaire` FOREIGN KEY (`mo_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; -- ---------------------------- -- Records of convention_cadre_mo -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_prefectures -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_prefectures`; CREATE TABLE `convention_cadre_prefectures` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `prefecture_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_prefecture`(`convention_cadre_id` ASC, `prefecture_id` ASC) USING BTREE, INDEX `prefecture_id`(`prefecture_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_prefectures_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_prefectures -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_programmes -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_programmes`; CREATE TABLE `convention_cadre_programmes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `programme_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_cadre_programme`(`convention_cadre_id` ASC, `programme_id` ASC) USING BTREE, INDEX `idx_convention_cadre`(`convention_cadre_id` ASC) USING BTREE, INDEX `idx_programme`(`programme_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_programmes_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_programmes -- ---------------------------- -- ---------------------------- -- Table structure for convention_cadre_secteurs_activites -- ---------------------------- DROP TABLE IF EXISTS `convention_cadre_secteurs_activites`; CREATE TABLE `convention_cadre_secteurs_activites` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NOT NULL, `secteur_activite_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_cadre_secteur`(`convention_cadre_id` ASC, `secteur_activite_id` ASC) USING BTREE, INDEX `idx_convention_cadre`(`convention_cadre_id` ASC) USING BTREE, INDEX `idx_secteur`(`secteur_activite_id` ASC) USING BTREE, CONSTRAINT `convention_cadre_secteurs_activites_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_cadre_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; -- ---------------------------- -- Records of convention_cadre_secteurs_activites -- ---------------------------- -- ---------------------------- -- Table structure for convention_communes -- ---------------------------- DROP TABLE IF EXISTS `convention_communes`; CREATE TABLE `convention_communes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `commune_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_commune`(`convention_id` ASC, `commune_id` ASC) USING BTREE, INDEX `commune_id`(`commune_id` ASC) USING BTREE, CONSTRAINT `convention_communes_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_communes -- ---------------------------- -- ---------------------------- -- Table structure for convention_contribution_annuelle -- ---------------------------- DROP TABLE IF EXISTS `convention_contribution_annuelle`; CREATE TABLE `convention_contribution_annuelle` ( `id` int NOT NULL AUTO_INCREMENT, `contribution_partenaire_id` int NOT NULL, `annee` int NOT NULL, `montant` decimal(15, 2) NULL DEFAULT 0.00, `date_versement` date NULL DEFAULT NULL, `est_verse` tinyint(1) NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_contrib_annee`(`contribution_partenaire_id` ASC, `annee` ASC) USING BTREE, CONSTRAINT `convention_contribution_annuelle_ibfk_1` FOREIGN KEY (`contribution_partenaire_id`) REFERENCES `convention_contribution_partenaire` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_contribution_annuelle -- ---------------------------- -- ---------------------------- -- Table structure for convention_contribution_partenaire -- ---------------------------- DROP TABLE IF EXISTS `convention_contribution_partenaire`; CREATE TABLE `convention_contribution_partenaire` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `partenaire_id` int NOT NULL, `contribution_crcs` decimal(15, 2) NULL DEFAULT 0.00, `contribution_totale` decimal(15, 2) NULL DEFAULT 0.00, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_partenaire_contrib`(`convention_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `partenaire_id`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_contribution_partenaire_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_contribution_partenaire -- ---------------------------- -- ---------------------------- -- Table structure for convention_documents -- ---------------------------- DROP TABLE IF EXISTS `convention_documents`; CREATE TABLE `convention_documents` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NULL DEFAULT NULL, `convention_specifique_id` int NULL DEFAULT NULL, `convention_id` int NULL DEFAULT NULL, `avenant_id` int NULL DEFAULT NULL, `entity_type` enum('Convention_Cadre','Convention_Specifique','Convention','Avenant') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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` int 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 `convention_cadre_id`(`convention_cadre_id` ASC) USING BTREE, INDEX `convention_specifique_id`(`convention_specifique_id` ASC) USING BTREE, INDEX `convention_id`(`convention_id` ASC) USING BTREE, INDEX `avenant_id`(`avenant_id` ASC) USING BTREE, INDEX `uploaded_by`(`uploaded_by` ASC) USING BTREE, INDEX `idx_entity`(`entity_type` ASC, `convention_cadre_id` ASC, `convention_specifique_id` ASC, `convention_id` ASC, `avenant_id` ASC) USING BTREE, CONSTRAINT `convention_documents_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_documents_ibfk_2` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_documents_ibfk_3` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_documents_ibfk_4` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_documents_ibfk_5` 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; -- ---------------------------- -- Records of convention_documents -- ---------------------------- -- ---------------------------- -- Table structure for convention_domaines_competences -- ---------------------------- DROP TABLE IF EXISTS `convention_domaines_competences`; CREATE TABLE `convention_domaines_competences` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `domaine_competence_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_domaine`(`convention_id` ASC, `domaine_competence_id` ASC) USING BTREE, INDEX `idx_convention`(`convention_id` ASC) USING BTREE, INDEX `idx_domaine`(`domaine_competence_id` ASC) USING BTREE, CONSTRAINT `convention_domaines_competences_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_domaines_competences -- ---------------------------- -- ---------------------------- -- Table structure for convention_maitres_ouvrage -- ---------------------------- DROP TABLE IF EXISTS `convention_maitres_ouvrage`; CREATE TABLE `convention_maitres_ouvrage` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `partenaire_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_mo`(`convention_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `idx_convention`(`convention_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_maitres_ouvrage_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_maitres_ouvrage -- ---------------------------- -- ---------------------------- -- Table structure for convention_maitres_ouvrage_delegues -- ---------------------------- DROP TABLE IF EXISTS `convention_maitres_ouvrage_delegues`; CREATE TABLE `convention_maitres_ouvrage_delegues` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `partenaire_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_mod`(`convention_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `idx_convention`(`convention_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_maitres_ouvrage_delegues_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_maitres_ouvrage_delegues -- ---------------------------- -- ---------------------------- -- Table structure for convention_mo -- ---------------------------- DROP TABLE IF EXISTS `convention_mo`; CREATE TABLE `convention_mo` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `mo_id` int NOT NULL, `type_mo` enum('MO','MOD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MO', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_mo`(`convention_id` ASC, `mo_id` ASC) USING BTREE, INDEX `fk_convention_mo_partenaire`(`mo_id` ASC) USING BTREE, CONSTRAINT `convention_mo_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_convention_mo_partenaire` FOREIGN KEY (`mo_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; -- ---------------------------- -- Records of convention_mo -- ---------------------------- -- ---------------------------- -- Table structure for convention_partenaire -- ---------------------------- DROP TABLE IF EXISTS `convention_partenaire`; CREATE TABLE `convention_partenaire` ( `id` int NOT NULL AUTO_INCREMENT, `convention_cadre_id` int NULL DEFAULT NULL, `convention_specifique_id` int NULL DEFAULT NULL, `avenant_id` int NULL DEFAULT NULL, `projet_id` int NULL DEFAULT NULL, `partenaire_id` int NOT NULL, `mo_id` int NULL DEFAULT NULL, `mod_id` int NULL DEFAULT NULL, `entity_type` enum('Convention_Cadre','Convention_Specifique','Avenant','Projet') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, INDEX `convention_cadre_id`(`convention_cadre_id` ASC) USING BTREE, INDEX `convention_specifique_id`(`convention_specifique_id` ASC) USING BTREE, INDEX `avenant_id`(`avenant_id` ASC) USING BTREE, INDEX `projet_id`(`projet_id` ASC) USING BTREE, INDEX `partenaire_id`(`partenaire_id` ASC) USING BTREE, INDEX `mo_id`(`mo_id` ASC) USING BTREE, INDEX `mod_id`(`mod_id` ASC) USING BTREE, INDEX `idx_entity`(`entity_type` ASC, `convention_cadre_id` ASC, `convention_specifique_id` ASC, `avenant_id` ASC, `projet_id` ASC) USING BTREE, CONSTRAINT `convention_partenaire_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_partenaire_ibfk_2` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_partenaire_ibfk_3` FOREIGN KEY (`avenant_id`) REFERENCES `avenants` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_partenaire_ibfk_4` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_partenaire_ibfk_5` FOREIGN KEY (`partenaire_id`) REFERENCES `partenaires` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `convention_partenaire_ibfk_6` FOREIGN KEY (`mo_id`) REFERENCES `maitres_ouvrage` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT, CONSTRAINT `convention_partenaire_ibfk_7` FOREIGN KEY (`mod_id`) REFERENCES `maitres_ouvrage` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_partenaire -- ---------------------------- -- ---------------------------- -- Table structure for convention_prefectures -- ---------------------------- DROP TABLE IF EXISTS `convention_prefectures`; CREATE TABLE `convention_prefectures` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `prefecture_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_prefecture`(`convention_id` ASC, `prefecture_id` ASC) USING BTREE, INDEX `prefecture_id`(`prefecture_id` ASC) USING BTREE, CONSTRAINT `convention_prefectures_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_prefectures -- ---------------------------- -- ---------------------------- -- Table structure for convention_programmes -- ---------------------------- DROP TABLE IF EXISTS `convention_programmes`; CREATE TABLE `convention_programmes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `programme_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_programme`(`convention_id` ASC, `programme_id` ASC) USING BTREE, INDEX `idx_convention`(`convention_id` ASC) USING BTREE, INDEX `idx_programme`(`programme_id` ASC) USING BTREE, CONSTRAINT `convention_programmes_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_programmes -- ---------------------------- -- ---------------------------- -- Table structure for convention_secteurs_activites -- ---------------------------- DROP TABLE IF EXISTS `convention_secteurs_activites`; CREATE TABLE `convention_secteurs_activites` ( `id` int NOT NULL AUTO_INCREMENT, `convention_id` int NOT NULL, `secteur_activite_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_secteur`(`convention_id` ASC, `secteur_activite_id` ASC) USING BTREE, INDEX `idx_convention`(`convention_id` ASC) USING BTREE, INDEX `idx_secteur`(`secteur_activite_id` ASC) USING BTREE, CONSTRAINT `convention_secteurs_activites_ibfk_1` FOREIGN KEY (`convention_id`) REFERENCES `conventions` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_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; -- ---------------------------- -- Records of convention_secteurs_activites -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique`; CREATE TABLE `convention_specifique` ( `id` int NOT NULL AUTO_INCREMENT, `numero_convention` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `convention_cadre_id` int NOT NULL, `date_session` date NULL DEFAULT NULL, `decision_id` int NULL DEFAULT NULL, `plan_id` int NULL DEFAULT NULL, `hors_pdr` tinyint(1) NULL DEFAULT 0, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nature` enum('Matérielle','Immatérielle') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `statut` enum('Votée','En cours de signature','En cours de Visa','Visé') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'Votée', `date_signature` date NULL DEFAULT NULL, `date_visa` date NULL DEFAULT NULL, `date_visionne` date NULL DEFAULT NULL, `document_convention_votee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `fiche_technique` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `phase` int NULL DEFAULT 1, `created_by` int NOT 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 `numero_convention`(`numero_convention` ASC) USING BTREE, INDEX `decision_id`(`decision_id` ASC) USING BTREE, INDEX `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_numero`(`numero_convention` ASC) USING BTREE, INDEX `idx_convention_cadre`(`convention_cadre_id` ASC) USING BTREE, INDEX `idx_statut`(`statut` ASC) USING BTREE, INDEX `idx_plan`(`plan_id` ASC) USING BTREE, INDEX `idx_hors_pdr`(`hors_pdr` ASC) USING BTREE, CONSTRAINT `convention_specifique_ibfk_1` FOREIGN KEY (`convention_cadre_id`) REFERENCES `convention_cadre` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_ibfk_2` FOREIGN KEY (`decision_id`) REFERENCES `decisions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_ibfk_4` FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_specifique -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_axes -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_axes`; CREATE TABLE `convention_specifique_axes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `axe_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_specifique_axe`(`convention_specifique_id` ASC, `axe_id` ASC) USING BTREE, INDEX `axe_id`(`axe_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_axes_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_axes_ibfk_2` FOREIGN KEY (`axe_id`) REFERENCES `axes_pdr` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of convention_specifique_axes -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_communes -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_communes`; CREATE TABLE `convention_specifique_communes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `commune_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_specifique_commune`(`convention_specifique_id` ASC, `commune_id` ASC) USING BTREE, INDEX `commune_id`(`commune_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_communes_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_communes -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_domaines_competences -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_domaines_competences`; CREATE TABLE `convention_specifique_domaines_competences` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `domaine_competence_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_specifique_domaine`(`convention_specifique_id` ASC, `domaine_competence_id` ASC) USING BTREE, INDEX `idx_convention_specifique`(`convention_specifique_id` ASC) USING BTREE, INDEX `idx_domaine`(`domaine_competence_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_domaines_competences_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_domaines_competences -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_maitres_ouvrage -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_maitres_ouvrage`; CREATE TABLE `convention_specifique_maitres_ouvrage` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `partenaire_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_specifique_mo`(`convention_specifique_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `idx_convention_specifique`(`convention_specifique_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_maitres_ouvrage_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_maitres_ouvrage -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_maitres_ouvrage_delegues -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_maitres_ouvrage_delegues`; CREATE TABLE `convention_specifique_maitres_ouvrage_delegues` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `partenaire_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_specifique_mod`(`convention_specifique_id` ASC, `partenaire_id` ASC) USING BTREE, INDEX `idx_convention_specifique`(`convention_specifique_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_maitres_ouvrage_delegues_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_maitres_ouvrage_delegues -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_mo -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_mo`; CREATE TABLE `convention_specifique_mo` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `mo_id` int NOT NULL, `type_mo` enum('MO','MOD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MO', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_specifique_mo`(`convention_specifique_id` ASC, `mo_id` ASC) USING BTREE, INDEX `fk_convention_specifique_mo_partenaire`(`mo_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_mo_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `fk_convention_specifique_mo_partenaire` FOREIGN KEY (`mo_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; -- ---------------------------- -- Records of convention_specifique_mo -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_prefectures -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_prefectures`; CREATE TABLE `convention_specifique_prefectures` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `prefecture_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_specifique_prefecture`(`convention_specifique_id` ASC, `prefecture_id` ASC) USING BTREE, INDEX `prefecture_id`(`prefecture_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_prefectures_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_prefectures -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_programmes -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_programmes`; CREATE TABLE `convention_specifique_programmes` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `programme_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_specifique_programme`(`convention_specifique_id` ASC, `programme_id` ASC) USING BTREE, INDEX `idx_convention_specifique`(`convention_specifique_id` ASC) USING BTREE, INDEX `idx_programme`(`programme_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_programmes_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_programmes -- ---------------------------- -- ---------------------------- -- Table structure for convention_specifique_secteurs_activites -- ---------------------------- DROP TABLE IF EXISTS `convention_specifique_secteurs_activites`; CREATE TABLE `convention_specifique_secteurs_activites` ( `id` int NOT NULL AUTO_INCREMENT, `convention_specifique_id` int NOT NULL, `secteur_activite_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_convention_specifique_secteur`(`convention_specifique_id` ASC, `secteur_activite_id` ASC) USING BTREE, INDEX `idx_convention_specifique`(`convention_specifique_id` ASC) USING BTREE, INDEX `idx_secteur`(`secteur_activite_id` ASC) USING BTREE, CONSTRAINT `convention_specifique_secteurs_activites_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `convention_specifique_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; -- ---------------------------- -- Records of convention_specifique_secteurs_activites -- ---------------------------- -- ---------------------------- -- Table structure for conventions -- ---------------------------- DROP TABLE IF EXISTS `conventions`; CREATE TABLE `conventions` ( `id` int NOT NULL AUTO_INCREMENT, `numero_convention` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `type_convention` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `decision_id` int NULL DEFAULT NULL, `plan_id` int NULL DEFAULT NULL, `hors_pdr` tinyint(1) NULL DEFAULT 0, `date_session` date NULL DEFAULT NULL, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nature` enum('Matérielle','Immatérielle') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `statut` enum('Votée','En cours de signature','En cours de Visa','Visé') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'Votée', `date_signature` date NULL DEFAULT NULL, `date_visa` date NULL DEFAULT NULL, `date_visionne` date NULL DEFAULT NULL, `document_convention_votee` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `fiche_technique` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `phase` int NULL DEFAULT 1, `created_by` int NOT 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 `numero_convention`(`numero_convention` ASC) USING BTREE, INDEX `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_numero`(`numero_convention` ASC) USING BTREE, INDEX `idx_type`(`type_convention` ASC) USING BTREE, INDEX `idx_decision`(`decision_id` ASC) USING BTREE, INDEX `idx_statut`(`statut` ASC) USING BTREE, INDEX `idx_plan`(`plan_id` ASC) USING BTREE, INDEX `idx_hors_pdr`(`hors_pdr` ASC) USING BTREE, CONSTRAINT `conventions_ibfk_1` FOREIGN KEY (`decision_id`) REFERENCES `decisions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT, CONSTRAINT `conventions_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `conventions_ibfk_3` FOREIGN KEY (`plan_id`) REFERENCES `plans_pdr` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of conventions -- ---------------------------- -- ---------------------------- -- Table structure for decisions -- ---------------------------- DROP TABLE IF EXISTS `decisions`; CREATE TABLE `decisions` ( `id` int NOT NULL AUTO_INCREMENT, `numero_decision` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `session_id` int NOT NULL, `date_decision` date NOT NULL, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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 `numero_decision`(`numero_decision` ASC) USING BTREE, INDEX `idx_numero`(`numero_decision` ASC) USING BTREE, INDEX `idx_session`(`session_id` ASC) USING BTREE, CONSTRAINT `decisions_ibfk_1` FOREIGN KEY (`session_id`) REFERENCES `sessions_crcs` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of decisions -- ---------------------------- INSERT INTO `decisions` VALUES (1, '25/10/001', 2, '2025-11-21', 'Decision 1 de x', 'Decision 1 de x', '2025-11-08 15:26:33', '2025-11-08 15:26:33'); INSERT INTO `decisions` VALUES (2, '22/2026', 2, '2025-11-21', 'decision 2 de session 25/10', 'decision 2 de session 25/10', '2025-11-08 15:27:59', '2025-11-08 15:27:59'); -- ---------------------------- -- Table structure for domaines_competences -- ---------------------------- DROP TABLE IF EXISTS `domaines_competences`; CREATE TABLE `domaines_competences` ( `id` int NOT NULL AUTO_INCREMENT, `intitule_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `created_by` int NOT 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 `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_intitule_fr`(`intitule_fr` ASC) USING BTREE, INDEX `idx_intitule_ar`(`intitule_ar` ASC) USING BTREE, CONSTRAINT `domaines_competences_ibfk_1` FOREIGN KEY (`created_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; -- ---------------------------- -- Records of domaines_competences -- ---------------------------- -- ---------------------------- -- Table structure for login_attempts -- ---------------------------- DROP TABLE IF EXISTS `login_attempts`; CREATE TABLE `login_attempts` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `ip_address` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `attempt_time` timestamp NOT NULL DEFAULT current_timestamp(), `success` tinyint(1) NULL DEFAULT 0, PRIMARY KEY (`id`) USING BTREE, INDEX `idx_username_time`(`username` ASC, `attempt_time` ASC) USING BTREE, INDEX `idx_ip_time`(`ip_address` ASC, `attempt_time` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 12 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of login_attempts -- ---------------------------- INSERT INTO `login_attempts` VALUES (1, 'admin', '::1', '2025-11-07 16:04:36', 1); INSERT INTO `login_attempts` VALUES (2, 'admin', '::1', '2025-11-07 16:34:35', 1); INSERT INTO `login_attempts` VALUES (3, 'admin', '::1', '2025-11-07 17:05:04', 1); INSERT INTO `login_attempts` VALUES (4, 'admin', '::1', '2025-11-07 17:18:42', 1); INSERT INTO `login_attempts` VALUES (5, 'admin', '::1', '2025-11-07 19:25:51', 1); INSERT INTO `login_attempts` VALUES (6, 'admin', '::1', '2025-11-08 14:07:45', 1); INSERT INTO `login_attempts` VALUES (7, 'admin', '::1', '2025-11-08 15:23:14', 1); INSERT INTO `login_attempts` VALUES (8, 'admin', '::1', '2025-11-08 16:29:52', 1); INSERT INTO `login_attempts` VALUES (9, 'admin', '::1', '2025-11-10 11:17:00', 1); INSERT INTO `login_attempts` VALUES (10, 'admin', '127.0.0.1', '2025-11-10 11:44:08', 1); INSERT INTO `login_attempts` VALUES (11, 'admin', '127.0.0.1', '2025-11-11 14:01:32', 1); -- ---------------------------- -- Table structure for maitres_ouvrage -- ---------------------------- DROP TABLE IF EXISTS `maitres_ouvrage`; CREATE TABLE `maitres_ouvrage` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `denomination_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `denomination_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `type_mo` enum('MO','MOD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `code`(`code` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of maitres_ouvrage -- ---------------------------- INSERT INTO `maitres_ouvrage` VALUES (1, 'MO001', 'Agence Urbaine de Casablanca', 'الوكالة الحضرية للدار البيضاء', 'MO', '2025-11-07 16:04:24'); INSERT INTO `maitres_ouvrage` VALUES (2, 'MOD001', 'Région Casablanca-Settat', 'جهة الدار البيضاء-سطات', 'MOD', '2025-11-07 16:04:24'); INSERT INTO `maitres_ouvrage` VALUES (3, 'MO002', 'Agence Urbaine de Settat', 'الوكالة الحضرية لسطات', 'MO', '2025-11-07 16:04:24'); -- ---------------------------- -- Table structure for partenaires -- ---------------------------- DROP TABLE IF EXISTS `partenaires`; CREATE TABLE `partenaires` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `denomination_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `denomination_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `type_partenaire` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `code`(`code` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of partenaires -- ---------------------------- INSERT INTO `partenaires` VALUES (1, 'P001', 'Ministère de l\'Intérieur', 'وزارة الداخلية', 'Ministère', '2025-11-07 16:04:24', NULL, NULL); INSERT INTO `partenaires` VALUES (2, 'P002', 'Ministère de l\'Équipement', 'وزارة التجهيز', 'Ministère', '2025-11-07 16:04:24', NULL, NULL); INSERT INTO `partenaires` VALUES (3, 'P003', 'Commune de Casablanca', 'جماعة الدار البيضاء', 'Collectivité', '2025-11-07 16:04:24', 'd', 'fe'); INSERT INTO `partenaires` VALUES (4, 'P004', 'Commune de Settat', 'جماعة سطات', 'Collectivité', '2025-11-07 16:04:24', NULL, NULL); -- ---------------------------- -- Table structure for permissions -- ---------------------------- DROP TABLE IF EXISTS `permissions`; CREATE TABLE `permissions` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `name_fr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `name_ar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `category` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `code`(`code` ASC) USING BTREE, INDEX `idx_code`(`code` ASC) USING BTREE, INDEX `idx_category`(`category` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of permissions -- ---------------------------- INSERT INTO `permissions` VALUES (1, 'view_convention_cadre', 'Voir Convention Cadre', 'عرض الإطار الاتفاقية', 'Permet de consulter les conventions cadres', 'يسمح ب consultation الإطار الاتفاقيات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (2, 'view_convention_specifique', 'Voir Convention Spécifique', 'عرض اتفاقية محددة', 'Permet de consulter les conventions spécifiques', 'يسمح ب consultation الاتفاقيات المحددة', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (3, 'view_avenant', 'Voir Avenant', 'عرض التعديل', 'Permet de consulter les avenants', 'يسمح ب consultation التعديلات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (4, 'view_projet', 'Voir Projet', 'عرض المشروع', 'Permet de consulter les projets', 'يسمح ب consultation المشاريع', 'projet', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (5, 'view_session', 'Voir Session', 'عرض الجلسة', 'Permet de consulter les sessions', 'يسمح ب consultation الجلسات', 'session', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (6, 'view_decision', 'Voir Décision', 'عرض القرار', 'Permet de consulter les décisions', 'يسمح ب consultation القرارات', 'decision', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (7, 'view_partenaire', 'Voir Partenaire', 'عرض الشريك', 'Permet de consulter les partenaires', 'يسمح ب consultation الشركاء', 'partenaire', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (8, 'edit_convention_cadre', 'Modifier Convention Cadre', 'تعديل الإطار الاتفاقية', 'Permet de modifier les conventions cadres', 'يسمح ب تعديل الإطار الاتفاقيات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (9, 'edit_convention_specifique', 'Modifier Convention Spécifique', 'تعديل اتفاقية محددة', 'Permet de modifier les conventions spécifiques', 'يسمح ب تعديل الاتفاقيات المحددة', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (10, 'edit_avenant', 'Modifier Avenant', 'تعديل التعديل', 'Permet de modifier les avenants', 'يسمح ب تعديل التعديلات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (11, 'edit_projet', 'Modifier Projet', 'تعديل المشروع', 'Permet de modifier les projets', 'يسمح ب تعديل المشاريع', 'projet', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (12, 'edit_session', 'Modifier Session', 'تعديل الجلسة', 'Permet de modifier les sessions', 'يسمح ب تعديل الجلسات', 'session', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (13, 'edit_decision', 'Modifier Décision', 'تعديل القرار', 'Permet de modifier les décisions', 'يسمح ب تعديل القرارات', 'decision', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (14, 'edit_partenaire', 'Modifier Partenaire', 'تعديل الشريك', 'Permet de modifier les partenaires', 'يسمح ب تعديل الشركاء', 'partenaire', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (15, 'create_convention_cadre', 'Créer Convention Cadre', 'إنشاء الإطار الاتفاقية', 'Permet de créer des conventions cadres', 'يسمح ب إنشاء الإطار الاتفاقيات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (16, 'create_convention_specifique', 'Créer Convention Spécifique', 'إنشاء اتفاقية محددة', 'Permet de créer des conventions spécifiques', 'يسمح ب إنشاء الاتفاقيات المحددة', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (17, 'create_avenant', 'Créer Avenant', 'إنشاء التعديل', 'Permet de créer des avenants', 'يسمح ب إنشاء التعديلات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (18, 'create_projet', 'Créer Projet', 'إنشاء المشروع', 'Permet de créer des projets', 'يسمح ب إنشاء المشاريع', 'projet', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (19, 'create_session', 'Créer Session', 'إنشاء الجلسة', 'Permet de créer des sessions', 'يسمح ب إنشاء الجلسات', 'session', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (20, 'create_decision', 'Créer Décision', 'إنشاء القرار', 'Permet de créer des décisions', 'يسمح ب إنشاء القرارات', 'decision', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (21, 'create_partenaire', 'Créer Partenaire', 'إنشاء الشريك', 'Permet de créer des partenaires', 'يسمح ب إنشاء الشركاء', 'partenaire', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (22, 'delete_convention_cadre', 'Supprimer Convention Cadre', 'حذف الإطار الاتفاقية', 'Permet de supprimer les conventions cadres', 'يسمح ب حذف الإطار الاتفاقيات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (23, 'delete_convention_specifique', 'Supprimer Convention Spécifique', 'حذف اتفاقية محددة', 'Permet de supprimer les conventions spécifiques', 'يسمح ب حذف الاتفاقيات المحددة', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (24, 'delete_avenant', 'Supprimer Avenant', 'حذف التعديل', 'Permet de supprimer les avenants', 'يسمح ب حذف التعديلات', 'convention', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (25, 'delete_projet', 'Supprimer Projet', 'حذف المشروع', 'Permet de supprimer les projets', 'يسمح ب حذف المشاريع', 'projet', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (26, 'delete_session', 'Supprimer Session', 'حذف الجلسة', 'Permet de supprimer les sessions', 'يسمح ب حذف الجلسات', 'session', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (27, 'delete_decision', 'Supprimer Décision', 'حذف القرار', 'Permet de supprimer les décisions', 'يسمح ب حذف القرارات', 'decision', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (28, 'delete_partenaire', 'Supprimer Partenaire', 'حذف الشريك', 'Permet de supprimer les partenaires', 'يسمح ب حذف الشركاء', 'partenaire', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (29, 'manage_users', 'Gérer les Utilisateurs', 'إدارة المستخدمين', 'Permet de gérer les utilisateurs', 'يسمح ب إدارة المستخدمين', 'admin', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (30, 'manage_permissions', 'Gérer les Permissions', 'إدارة الصلاحيات', 'Permet de gérer les permissions', 'يسمح ب إدارة الصلاحيات', 'admin', '2025-11-07 16:33:08'); INSERT INTO `permissions` VALUES (31, 'view_dashboard', 'Voir Dashboard', 'عرض لوحة التحكم', 'Permet d\'accéder au tableau de bord', 'يسمح ب الوصول إلى لوحة التحكم', 'admin', '2025-11-07 16:33:08'); -- ---------------------------- -- Table structure for plans_pdr -- ---------------------------- DROP TABLE IF EXISTS `plans_pdr`; CREATE TABLE `plans_pdr` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 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 `code`(`code` ASC) USING BTREE, INDEX `idx_code`(`code` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of plans_pdr -- ---------------------------- -- ---------------------------- -- Table structure for prefectures -- ---------------------------- DROP TABLE IF EXISTS `prefectures`; CREATE TABLE `prefectures` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nom_fr` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nom_ar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `code`(`code` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of prefectures -- ---------------------------- INSERT INTO `prefectures` VALUES (1, 'CAS', 'Casablanca', 'الدار البيضاء', '2025-11-07 16:04:24'); INSERT INTO `prefectures` VALUES (2, 'MOH', 'Mohammedia', 'المحمدية', '2025-11-07 16:04:24'); INSERT INTO `prefectures` VALUES (3, 'SET', 'Settat', 'سطات', '2025-11-07 16:04:24'); INSERT INTO `prefectures` VALUES (4, 'BER', 'Benslimane', 'بنسليمان', '2025-11-07 16:04:24'); -- ---------------------------- -- Table structure for programmes_pdr -- ---------------------------- DROP TABLE IF EXISTS `programmes_pdr`; CREATE TABLE `programmes_pdr` ( `id` int NOT NULL AUTO_INCREMENT, `code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `axe_id` int NOT 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_code_axe`(`code` ASC, `axe_id` ASC) USING BTREE, INDEX `idx_axe`(`axe_id` ASC) USING BTREE, INDEX `idx_code`(`code` ASC) USING BTREE, CONSTRAINT `programmes_pdr_ibfk_1` FOREIGN KEY (`axe_id`) REFERENCES `axes_pdr` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of programmes_pdr -- ---------------------------- -- ---------------------------- -- Table structure for projet_axes -- ---------------------------- DROP TABLE IF EXISTS `projet_axes`; CREATE TABLE `projet_axes` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `axe_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_projet_axe`(`projet_id` ASC, `axe_id` ASC) USING BTREE, INDEX `axe_id`(`axe_id` ASC) USING BTREE, CONSTRAINT `projet_axes_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `projet_axes_ibfk_2` FOREIGN KEY (`axe_id`) REFERENCES `axes_pdr` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of projet_axes -- ---------------------------- -- ---------------------------- -- Table structure for projet_communes -- ---------------------------- DROP TABLE IF EXISTS `projet_communes`; CREATE TABLE `projet_communes` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `commune_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_projet_commune`(`projet_id` ASC, `commune_id` ASC) USING BTREE, INDEX `commune_id`(`commune_id` ASC) USING BTREE, CONSTRAINT `projet_communes_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `projet_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; -- ---------------------------- -- Records of projet_communes -- ---------------------------- -- ---------------------------- -- Table structure for projet_documents -- ---------------------------- DROP TABLE IF EXISTS `projet_documents`; CREATE TABLE `projet_documents` ( `id` int NOT NULL AUTO_INCREMENT, `projet_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, `document_type` enum('etude_faisabilite','pvs_conseils','documents_foncier','autre') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'autre', `chemin_fichier` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `taille_fichier` int 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 `uploaded_by`(`uploaded_by` ASC) USING BTREE, INDEX `idx_projet`(`projet_id` ASC) USING BTREE, INDEX `idx_document_type`(`document_type` ASC) USING BTREE, CONSTRAINT `projet_documents_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `projet_documents_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; -- ---------------------------- -- Records of projet_documents -- ---------------------------- -- ---------------------------- -- Table structure for projet_maitres_ouvrage -- ---------------------------- DROP TABLE IF EXISTS `projet_maitres_ouvrage`; CREATE TABLE `projet_maitres_ouvrage` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `partenaire_id` int NOT NULL, `type_mo` enum('MO','MOD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_projet_mo`(`projet_id` ASC, `partenaire_id` ASC, `type_mo` ASC) USING BTREE, INDEX `idx_projet`(`projet_id` ASC) USING BTREE, INDEX `idx_partenaire`(`partenaire_id` ASC) USING BTREE, CONSTRAINT `projet_maitres_ouvrage_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `projet_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; -- ---------------------------- -- Records of projet_maitres_ouvrage -- ---------------------------- -- ---------------------------- -- Table structure for projet_mo -- ---------------------------- DROP TABLE IF EXISTS `projet_mo`; CREATE TABLE `projet_mo` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `mo_id` int NOT NULL, `type_mo` enum('MO','MOD') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'MO', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_projet_mo`(`projet_id` ASC, `mo_id` ASC) USING BTREE, INDEX `fk_projet_mo_partenaire`(`mo_id` ASC) USING BTREE, CONSTRAINT `fk_projet_mo_partenaire` FOREIGN KEY (`mo_id`) REFERENCES `partenaires` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `projet_mo_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of projet_mo -- ---------------------------- -- ---------------------------- -- Table structure for projet_phases_jalons -- ---------------------------- DROP TABLE IF EXISTS `projet_phases_jalons`; CREATE TABLE `projet_phases_jalons` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `type_phase` enum('Jalon','Phase','Etude') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Phase', `intitule_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `delai_realisation_mois` decimal(5, 2) NULL DEFAULT NULL, `date_demarrage` date NULL DEFAULT NULL, `date_fin` date NULL DEFAULT NULL, `cout_execution` decimal(15, 2) NULL DEFAULT NULL, `ordre` int NOT 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, INDEX `idx_projet`(`projet_id` ASC) USING BTREE, INDEX `idx_ordre`(`projet_id` ASC, `ordre` ASC) USING BTREE, CONSTRAINT `projet_phases_jalons_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of projet_phases_jalons -- ---------------------------- -- ---------------------------- -- Table structure for projet_planning_global -- ---------------------------- DROP TABLE IF EXISTS `projet_planning_global`; CREATE TABLE `projet_planning_global` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `delai_global_mois` int NULL DEFAULT NULL, `date_demarrage` date NULL DEFAULT NULL, `date_fin` date 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_projet_planning`(`projet_id` ASC) USING BTREE, CONSTRAINT `projet_planning_global_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of projet_planning_global -- ---------------------------- -- ---------------------------- -- Table structure for projet_prefectures -- ---------------------------- DROP TABLE IF EXISTS `projet_prefectures`; CREATE TABLE `projet_prefectures` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `prefecture_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_projet_prefecture`(`projet_id` ASC, `prefecture_id` ASC) USING BTREE, INDEX `prefecture_id`(`prefecture_id` ASC) USING BTREE, CONSTRAINT `projet_prefectures_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `projet_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; -- ---------------------------- -- Records of projet_prefectures -- ---------------------------- -- ---------------------------- -- Table structure for projet_programmes -- ---------------------------- DROP TABLE IF EXISTS `projet_programmes`; CREATE TABLE `projet_programmes` ( `id` int NOT NULL AUTO_INCREMENT, `projet_id` int NOT NULL, `programme_id` int NOT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_projet_programme`(`projet_id` ASC, `programme_id` ASC) USING BTREE, INDEX `idx_projet`(`projet_id` ASC) USING BTREE, INDEX `idx_programme`(`programme_id` ASC) USING BTREE, CONSTRAINT `projet_programmes_ibfk_1` FOREIGN KEY (`projet_id`) REFERENCES `projets` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `projet_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; -- ---------------------------- -- Records of projet_programmes -- ---------------------------- -- ---------------------------- -- Table structure for projets -- ---------------------------- DROP TABLE IF EXISTS `projets`; CREATE TABLE `projets` ( `id` int NOT NULL AUTO_INCREMENT, `numero_projet` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `convention_specifique_id` int NULL DEFAULT NULL, `decision_id` int NULL DEFAULT NULL, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `statut` enum('En cours de signature','En cours de Visa','Visé') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'En cours de signature', `phase` int NULL DEFAULT 1, `created_by` int NOT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP, `latitude` decimal(10, 8) NULL DEFAULT NULL, `longitude` decimal(11, 8) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `numero_projet`(`numero_projet` ASC) USING BTREE, INDEX `decision_id`(`decision_id` ASC) USING BTREE, INDEX `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_numero`(`numero_projet` ASC) USING BTREE, INDEX `idx_convention_specifique`(`convention_specifique_id` ASC) USING BTREE, INDEX `idx_statut`(`statut` ASC) USING BTREE, INDEX `idx_projets_coords`(`latitude` ASC, `longitude` ASC) USING BTREE, CONSTRAINT `projets_ibfk_1` FOREIGN KEY (`convention_specifique_id`) REFERENCES `convention_specifique` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT, CONSTRAINT `projets_ibfk_2` FOREIGN KEY (`decision_id`) REFERENCES `decisions` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT, CONSTRAINT `projets_ibfk_3` FOREIGN KEY (`created_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; -- ---------------------------- -- Records of projets -- ---------------------------- -- ---------------------------- -- Table structure for secteurs_activites -- ---------------------------- DROP TABLE IF EXISTS `secteurs_activites`; CREATE TABLE `secteurs_activites` ( `id` int NOT NULL AUTO_INCREMENT, `intitule_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `intitule_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `description_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `description_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `created_by` int NOT 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 `created_by`(`created_by` ASC) USING BTREE, INDEX `idx_intitule_fr`(`intitule_fr` ASC) USING BTREE, INDEX `idx_intitule_ar`(`intitule_ar` ASC) USING BTREE, CONSTRAINT `secteurs_activites_ibfk_1` FOREIGN KEY (`created_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; -- ---------------------------- -- Records of secteurs_activites -- ---------------------------- -- ---------------------------- -- Table structure for sessions_crcs -- ---------------------------- DROP TABLE IF EXISTS `sessions_crcs`; CREATE TABLE `sessions_crcs` ( `id` int NOT NULL AUTO_INCREMENT, `numero_session` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `date_session` date NOT NULL, `intitule_fr` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `intitule_ar` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, `document` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `numero_session`(`numero_session` ASC) USING BTREE, INDEX `idx_numero`(`numero_session` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of sessions_crcs -- ---------------------------- INSERT INTO `sessions_crcs` VALUES (2, '25/10', '2025-11-21', 'Session du 25', 'Session du 25', NULL, '2025-11-08 15:25:44'); -- ---------------------------- -- Table structure for user_permissions -- ---------------------------- DROP TABLE IF EXISTS `user_permissions`; CREATE TABLE `user_permissions` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `permission_code` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `granted` tinyint(1) NULL DEFAULT 1, `created_at` timestamp NOT NULL DEFAULT current_timestamp(), PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unique_user_permission`(`user_id` ASC, `permission_code` ASC) USING BTREE, INDEX `idx_user_id`(`user_id` ASC) USING BTREE, INDEX `idx_permission_code`(`permission_code` ASC) USING BTREE, CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT, CONSTRAINT `user_permissions_ibfk_2` FOREIGN KEY (`permission_code`) REFERENCES `permissions` (`code`) ON DELETE CASCADE ON UPDATE RESTRICT ) ENGINE = InnoDB AUTO_INCREMENT = 32 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user_permissions -- ---------------------------- INSERT INTO `user_permissions` VALUES (1, 1, 'create_avenant', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (2, 1, 'create_convention_cadre', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (3, 1, 'create_convention_specifique', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (4, 1, 'create_decision', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (5, 1, 'create_partenaire', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (6, 1, 'create_projet', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (7, 1, 'create_session', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (8, 1, 'delete_avenant', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (9, 1, 'delete_convention_cadre', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (10, 1, 'delete_convention_specifique', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (11, 1, 'delete_decision', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (12, 1, 'delete_partenaire', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (13, 1, 'delete_projet', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (14, 1, 'delete_session', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (15, 1, 'edit_avenant', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (16, 1, 'edit_convention_cadre', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (17, 1, 'edit_convention_specifique', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (18, 1, 'edit_decision', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (19, 1, 'edit_partenaire', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (20, 1, 'edit_projet', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (21, 1, 'edit_session', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (22, 1, 'manage_permissions', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (23, 1, 'manage_users', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (24, 1, 'view_avenant', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (25, 1, 'view_convention_cadre', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (26, 1, 'view_convention_specifique', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (27, 1, 'view_dashboard', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (28, 1, 'view_decision', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (29, 1, 'view_partenaire', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (30, 1, 'view_projet', 1, '2025-11-07 16:33:08'); INSERT INTO `user_permissions` VALUES (31, 1, 'view_session', 1, '2025-11-07 16:33:08'); -- ---------------------------- -- Table structure for users -- ---------------------------- DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, `nom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `prenom` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `full_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `nom_ar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `prenom_ar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `phone` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL, `role` enum('admin','dri','user') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'user', `created_at` timestamp NOT NULL DEFAULT current_timestamp(), `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP, `last_login` timestamp NULL DEFAULT NULL, `is_active` tinyint(1) NULL DEFAULT 1, PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `username`(`username` ASC) USING BTREE, INDEX `idx_username`(`username` ASC) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of users -- ---------------------------- INSERT INTO `users` VALUES (1, 'admin', '$2y$10$hISG5FiQc206iwseqsRUIe7a5/mXRP8BpYEPWraXQZmvJT0hxH/W.', 'admin@crcs.ma', 'ER RAISSI', 'Aiman', 'Aiman ER RAISSI', 'الريسي', 'ايمن', '0639049117', 'admin', '2025-11-07 16:04:24', '2025-11-11 14:01:32', '2025-11-11 14:01:32', 1); SET FOREIGN_KEY_CHECKS = 1;