<?php
$host = 'localhost:3306';
$dbname = 'gqdcvggs_izhak';
$username = 'gqdcvggs';
$password = 'imaors_management.346980*#@-onlyforcpanel;forchange';
$charset = 'utf8mb4';

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

// Connexion PDO
try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=$charset", $username, $password, $options);
} catch (PDOException $e) {
    die("Erreur de connexion à la base de données : " . $e->getMessage());
}

// Création des tables si elles n'existent pas
function createTables($pdo) {
    // Table utilisateurs
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        role ENUM('eleve', 'prof') NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Table matières
    $pdo->exec("CREATE TABLE IF NOT EXISTS matieres (
        id INT AUTO_INCREMENT PRIMARY KEY,
        nom VARCHAR(100) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // Table notes
    $pdo->exec("CREATE TABLE IF NOT EXISTS notes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        matiere_id INT NOT NULL,
        titre VARCHAR(255) NOT NULL,
        description TEXT,
        image_url VARCHAR(255),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (matiere_id) REFERENCES matieres(id) ON DELETE CASCADE
    )");

    // Table commentaires
    $pdo->exec("CREATE TABLE IF NOT EXISTS commentaires (
        id INT AUTO_INCREMENT PRIMARY KEY,
        note_id INT NOT NULL,
        user_id INT NOT NULL,
        contenu TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )");

    // Table examens
    $pdo->exec("CREATE TABLE IF NOT EXISTS examens (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        titre VARCHAR(255) NOT NULL,
        date_debut DATETIME NOT NULL,
        date_fin DATETIME NOT NULL,
        debut_preparation DATETIME NOT NULL,
        fin_preparation DATETIME,
        nombre_examens_blancs INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )");

    // Table matières d'examen
    $pdo->exec("CREATE TABLE IF NOT EXISTS examen_matieres (
        id INT AUTO_INCREMENT PRIMARY KEY,
        examen_id INT NOT NULL,
        matiere_id INT NOT NULL,
        FOREIGN KEY (examen_id) REFERENCES examens(id) ON DELETE CASCADE,
        FOREIGN KEY (matiere_id) REFERENCES matieres(id) ON DELETE CASCADE
    )");
    
    // Table examens blancs
    $pdo->exec("CREATE TABLE IF NOT EXISTS examens_blancs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        examen_id INT NOT NULL,
        titre VARCHAR(255) NOT NULL,
        date DATETIME NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (examen_id) REFERENCES examens(id) ON DELETE CASCADE
    )");
    
    // Table notes des examens blancs
    $pdo->exec("CREATE TABLE IF NOT EXISTS examen_blanc_notes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        examen_blanc_id INT NOT NULL,
        matiere_id INT NOT NULL,
        note DECIMAL(5,2) NOT NULL,
        max_note DECIMAL(5,2) NOT NULL DEFAULT 100.00,
        pourcentage DECIMAL(5,2) AS (note * 100 / max_note),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (examen_blanc_id) REFERENCES examens_blancs(id) ON DELETE CASCADE,
        FOREIGN KEY (matiere_id) REFERENCES matieres(id) ON DELETE CASCADE
    )");
}

// Créer les tables
createTables($pdo);