-- =============================================
-- Base de données : ECOLANUM - Concours d'Admission
-- Version : 1.0
-- Date : Décembre 2025
-- =============================================

-- Création de la base de données
CREATE DATABASE IF NOT EXISTS ecolanum_admissions CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ecolanum_admissions;

-- =============================================
-- Table : formations
-- Formations proposées par l'école
-- =============================================
CREATE TABLE formations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE NOT NULL,
    nom VARCHAR(100) NOT NULL,
    niveau_entree ENUM('Bac', 'Bac+1', 'Bac+2', 'Bac+3', 'Bac+4/5') NOT NULL,
    duree_annees TINYINT UNSIGNED NOT NULL,
    places_disponibles INT UNSIGNED NOT NULL DEFAULT 0,
    cout_annuel DECIMAL(10,2) NOT NULL COMMENT 'Frais de scolarité annuels en €',
    description TEXT,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =============================================
-- Table : sessions_concours
-- Sessions annuelles ou semestrielles du concours
-- =============================================
CREATE TABLE sessions_concours (
    id INT AUTO_INCREMENT PRIMARY KEY,
    annee_scolaire VARCHAR(9) NOT NULL COMMENT 'Ex: 2025-2026',
    date_debut_candidature DATE NOT NULL,
    date_fin_candidature DATE NOT NULL,
    date_epreuves DATE,
    date_resultats DATE,
    statut ENUM('planifiee', 'en_cours', 'cloturee', 'annulee') DEFAULT 'planifiee',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =============================================
-- Table : candidats
-- Informations personnelles des candidats
-- =============================================
CREATE TABLE candidats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_candidat VARCHAR(20) UNIQUE NOT NULL COMMENT 'Ex: ECO-2026-0487',
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    date_naissance DATE NOT NULL,
    genre ENUM('M', 'F', 'Autre') DEFAULT NULL,
    nationalite VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    telephone VARCHAR(20) NOT NULL,
    adresse TEXT,
    ville VARCHAR(100),
    code_postal VARCHAR(10),
    pays VARCHAR(50) DEFAULT 'France',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_nom_prenom (nom, prenom),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- =============================================
-- Table : documents
-- Documents uploadés par les candidats (CV, lettre, bulletins...)
-- =============================================
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidature_id INT NOT NULL,
    type_document ENUM('cv', 'lettre_motivation', 'bulletins', 'releve_notes', 'piece_identite', 'autre') NOT NULL,
    nom_fichier VARCHAR(255) NOT NULL,
    chemin_fichier VARCHAR(500) NOT NULL COMMENT 'Chemin sur le serveur ou URL',
    taille_octets BIGINT UNSIGNED NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    date_upload DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (candidature_id) REFERENCES candidatures(id) ON DELETE CASCADE,

    INDEX idx_type (type_document),
    INDEX idx_candidature (candidature_id)
) ENGINE=InnoDB;

-- =============================================
-- Table : logs_activites
-- Suivi des actions importantes (optionnel mais recommandé)
-- =============================================
CREATE TABLE logs_activites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    utilisateur_id INT NULL,
    action VARCHAR(100) NOT NULL COMMENT 'Ex: connexion, modification_statut, telechargement_document',
    description TEXT,
    ip_address VARCHAR(45),
    date_action DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (utilisateur_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- =============================================
-- Données d'exemple (à adapter ou supprimer en production)
-- =============================================

INSERT INTO formations (code, nom, niveau_entree, duree_annees, places_disponibles, cout_annuel, description) VALUES
('BTS-DD', 'BTS Développement Digital', 'Bac', 2, 45, 6500.00, 'Formation en développement web et applications'),
('BACH-CYBER', 'Bachelor Cybersécurité', 'Bac+1', 3, 30, 8500.00, 'Expertise en sécurité informatique et ethical hacking'),
('LP-MARKDIG', 'Licence Pro Marketing Digital', 'Bac+2', 1, 35, 7200.00, 'Spécialisation en stratégie digitale et e-commerce'),
('MASTER-IA', 'Master Intelligence Artificielle', 'Bac+3', 2, 25, 9500.00, 'Formation avancée en IA et machine learning');

INSERT INTO sessions_concours (annee_scolaire, date_debut_candidature, date_fin_candidature, date_epreuves, date_resultats, statut) VALUES
('2025-2026', '2025-10-01', '2025-12-20', '2026-01-15', '2026-02-01', 'en_cours'),
('2026-2027', '2026-01-05', '2026-03-31', '2026-04-20', '2026-05-15', 'planifiee');

-- =============================================
-- Fin du script
-- =============================================

-- Pour vérifier les contraintes :
SHOW TABLES;
DESCRIBE candidatures;

CREATE TABLE etudiants (
    id INT NOT NULL,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    -- 0: Admissions, 1: Students, 2: Presentiel, 3: Distanciel
    status_id TINYINT NOT NULL, 
    date_inscription DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, status_id) -- La colonne de partition doit être dans la clé primaire
)
PARTITION BY RANGE (status_id) (
    PARTITION p0 VALUES LESS THAN (1), -- Uniquement 0 (Admissions)
    PARTITION p1 VALUES LESS THAN (2), -- Uniquement 1 (Students)
    PARTITION p2 VALUES LESS THAN (3), -- Uniquement 2 (Presentiel)
    PARTITION p3 VALUES LESS THAN (4)  -- Uniquement 3 (Distanciel)
);


SELECT PARTITION_NAME, TABLE_ROWS 
FROM information_schema.partitions 
WHERE TABLE_NAME = 'etudiants' AND TABLE_SCHEMA = 'ecolanum_db';


ALTER TABLE etudiants ADD CONSTRAINT fk_cand_etud FOREIGN KEY(id_candidat) REFERENCES candidats(id_candidat)
on DELETE CASCADE
on UPDATE CASCADE;