-- database.sql - Script de création de la base de données

CREATE DATABASE IF NOT EXISTS qcm_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE qcm_database;

-- Table des questions
CREATE TABLE IF NOT EXISTS questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    niveau VARCHAR(50) NOT NULL,
    theme VARCHAR(100) NOT NULL,
    question TEXT NOT NULL,
    image VARCHAR(255),
    reponse_a TEXT,
    reponse_b TEXT,
    reponse_c TEXT,
    reponse_d TEXT,
    reponses_correctes VARCHAR(10) NOT NULL COMMENT 'Ex: A,B,C,D',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_niveau (niveau),
    INDEX idx_theme (theme)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des résultats
CREATE TABLE IF NOT EXISTS resultats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    niveau VARCHAR(50) NOT NULL,
    score INT NOT NULL,
    total INT NOT NULL DEFAULT 40,
    pourcentage DECIMAL(5,2) NOT NULL,
    date_passage DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_nom (nom),
    INDEX idx_date (date_passage),
    INDEX idx_niveau (niveau)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des réponses détaillées (optionnel - pour analyse)
CREATE TABLE IF NOT EXISTS reponses_detaillees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    resultat_id INT NOT NULL,
    question_id INT NOT NULL,
    reponse_utilisateur VARCHAR(10),
    est_correcte BOOLEAN NOT NULL,
    FOREIGN KEY (resultat_id) REFERENCES resultats(id) ON DELETE CASCADE,
    FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE ON UPDATE CASCADE,
    INDEX idx_resultat (resultat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
