database.sql

-- Base de données
CREATE DATABASE IF NOT EXISTS lms_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE lms_db;

-- =============================================================================
-- NETTOYAGE : suppression des anciennes tables (y compris les tables legacy)
-- L'ordre de suppression est INVERSE de l'ordre de création pour respecter
-- les contraintes de clés étrangères (on supprime d'abord les tables enfants).
-- IF EXISTS évite une erreur si la table n'existe pas encore.
-- =============================================================================
DROP TABLE IF EXISTS certificates;
DROP TABLE IF EXISTS student_answers;
DROP TABLE IF EXISTS student_progress;
DROP TABLE IF EXISTS quiz_questions;
DROP TABLE IF EXISTS quizzes;
-- Tables legacy de l'ancien schéma (quiz monolithique + tentatives)
DROP TABLE IF EXISTS quiz_attempts;
DROP TABLE IF EXISTS quiz;
DROP TABLE IF EXISTS lessons;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS modules;
DROP TABLE IF EXISTS users;

-- Table users : les 3 rôles, AUCUN compte par défaut (inscription via l'interface)
-- Nous stockons le mot de passe via bcrypt pour qu'en cas de vol de BDD, les données restent sécurisées.
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  mot_de_passe VARCHAR(255) NOT NULL,  -- hash bcrypt via password_hash()
  role ENUM('promoteur','enseignant','etudiant') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table modules : créés par le promoteur
-- Un module est une collection de cours, ce qui permet de regrouper les connaissances.
CREATE TABLE modules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titre VARCHAR(255) NOT NULL,
  description TEXT,
  created_by INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Table courses : créés par l'enseignant, liés à un module
-- ON DELETE CASCADE évite de conserver des cours orphelins si le module ou l'enseignant est supprimé.
CREATE TABLE courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titre VARCHAR(255) NOT NULL,
  description TEXT,
  module_id INT NOT NULL,
  enseignant_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE,
  FOREIGN KEY (enseignant_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table lessons : une leçon = un fichier (pdf ou vidéo). Le quiz est OPTIONNEL et séparé.
CREATE TABLE lessons (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titre VARCHAR(255) NOT NULL,
  type ENUM('pdf','video') NOT NULL,
  chemin_fichier VARCHAR(500) NOT NULL,
  course_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

-- Table quizzes : un quiz est un objet indépendant lié à UNE leçon (optionnel)
-- UNIQUE sur lesson_id : au maximum 1 quiz par leçon. La relation est strictement 1 à 1 ou 1 à 0.
CREATE TABLE quizzes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  lesson_id INT NOT NULL UNIQUE,
  titre VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
);

-- Table quiz_questions : N questions par quiz (N >= 1)
-- L'ordre permet de garantir un parcours d'apprentissage logique voulu par le professeur.
CREATE TABLE quiz_questions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  quiz_id INT NOT NULL,
  question TEXT NOT NULL,
  option_a TEXT NOT NULL,
  option_b TEXT NOT NULL,
  option_c TEXT NOT NULL,
  bonne_reponse ENUM('A','B','C') NOT NULL,
  ordre INT DEFAULT 0,  -- pour afficher les questions dans l'ordre voulu
  FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE CASCADE
);

-- Table student_progress : une ligne par (étudiant, leçon)
-- Centralise les performances de l'étudiant, utile pour de rapides calculs de progression sans relire 10 tables.
CREATE TABLE student_progress (
  id INT AUTO_INCREMENT PRIMARY KEY,
  etudiant_id INT NOT NULL,
  lesson_id INT NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  score_pourcentage DECIMAL(5,2) DEFAULT 0.00,
  completed_at TIMESTAMP NULL,
  UNIQUE KEY unique_progress (etudiant_id, lesson_id),
  FOREIGN KEY (etudiant_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (lesson_id) REFERENCES lessons(id) ON DELETE CASCADE
);

-- Table student_answers : réponses individuelles aux questions
-- UNIQUE sur (etudiant_id, question_id) empêche de fausser les stats en répondant de multiples fois.
CREATE TABLE student_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  etudiant_id INT NOT NULL,
  question_id INT NOT NULL,
  reponse_donnee ENUM('A','B','C') NOT NULL,
  est_correcte BOOLEAN NOT NULL,
  answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_answer (etudiant_id, question_id),
  FOREIGN KEY (etudiant_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (question_id) REFERENCES quiz_questions(id) ON DELETE CASCADE
);

-- Table certificates : délivrés quand toutes les leçons d'un module sont validées
-- La clé unique évite qu'un système automatisé redélivre par erreur le même certificat à l'infini.
CREATE TABLE certificates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  etudiant_id INT NOT NULL,
  module_id INT NOT NULL,
  delivered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_cert (etudiant_id, module_id),
  FOREIGN KEY (etudiant_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
);

/*
=== FLUX DE DONNÉES — LMS ===

FLUX PROMOTEUR :
  users (role=promoteur) → crée modules → voit les certificats délivrés

FLUX ENSEIGNANT :
  users (role=enseignant) → crée courses (lié à un module)
                          → crée lessons (upload fichier PDF/vidéo)
                          → (optionnel) crée un quiz pour la leçon
                          → ajoute N questions au quiz

FLUX ÉTUDIANT :
  users (role=etudiant)  → voit tous les courses
                         → clique sur un course → charge les lessons via AJAX
                         → si leçon sans quiz  : mark_lesson_viewed.php → student_progress (completed=TRUE, score=100%)
                         → si leçon avec quiz  : affiche toutes les questions
                                              → soumet toutes les réponses d'un coup
                                              → submit_quiz.php calcule score = (correctes/total)*100
                                              → student_progress mis à jour
                         → si TOUTES les leçons du module complétées → certificat généré

CALCUL PROGRESSION MODULE :
  progression% = (leçons complétées par l'étudiant dans le module) / (total leçons du module) * 100
*/