-- Migration: Création des tables pour les présidents des commissions permanentes et vices-présidents
-- Date: 2025-01-XX
-- Description: Crée les tables pour gérer les catégories et les présidents/vices-présidents

-- Table pour les catégories
DROP TABLE IF EXISTS `presidents_categories`;
CREATE TABLE `presidents_categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nom_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom_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_nom_fr`(`nom_fr` ASC) USING BTREE,
  INDEX `idx_nom_ar`(`nom_ar` ASC) USING BTREE,
  CONSTRAINT `presidents_categories_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;

-- Table pour les présidents et vices-présidents
DROP TABLE IF EXISTS `presidents_commissions`;
CREATE TABLE `presidents_commissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `nom_complet_fr` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nom_complet_ar` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domaine_competence_id` int NOT NULL,
  `categorie_id` int NOT 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 `domaine_competence_id`(`domaine_competence_id` ASC) USING BTREE,
  INDEX `categorie_id`(`categorie_id` ASC) USING BTREE,
  INDEX `created_by`(`created_by` ASC) USING BTREE,
  CONSTRAINT `presidents_commissions_ibfk_1` FOREIGN KEY (`domaine_competence_id`) REFERENCES `domaines_competences` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `presidents_commissions_ibfk_2` FOREIGN KEY (`categorie_id`) REFERENCES `presidents_categories` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `presidents_commissions_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;