<?php
// ─── INITIALISATION DES TABLES ────────────────────────────────────────────────
// Exécuter UNE SEULE FOIS sur le serveur pour créer les tables :
// http://tonsite.com/api/init_db.php
// Supprimer ou renommer ce fichier ensuite pour la sécurité.

require 'db.php';

$sqls = [];

// Table users
$sqls[] = "CREATE TABLE IF NOT EXISTS users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(30)  NOT NULL UNIQUE,
    email      VARCHAR(150) DEFAULT NULL,
    password   VARCHAR(255) NOT NULL,
    best_global INT         NOT NULL DEFAULT 0,
    played     INT          NOT NULL DEFAULT 0,
    created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    last_seen  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table cat_stats (stats par catégorie)
$sqls[] = "CREATE TABLE IF NOT EXISTS cat_stats (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    user_id    INT         NOT NULL,
    category   VARCHAR(80) NOT NULL,
    good       INT         NOT NULL DEFAULT 0,
    bad        INT         NOT NULL DEFAULT 0,
    played_cat INT         NOT NULL DEFAULT 0,
    best_score INT         NOT NULL DEFAULT 0,
    UNIQUE KEY uq_user_cat (user_id, category),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table mistakes (fautes enregistrées)
$sqls[] = "CREATE TABLE IF NOT EXISTS mistakes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    user_id     INT          NOT NULL,
    category    VARCHAR(80)  NOT NULL,
    question    TEXT         NOT NULL,
    user_answer VARCHAR(255) NOT NULL,
    correct     VARCHAR(255) NOT NULL,
    explanation TEXT,
    date        TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

// Table daily_history (question du jour)
$sqls[] = "CREATE TABLE IF NOT EXISTS daily_history (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT  NOT NULL,
    day     INT  NOT NULL,
    correct TINYINT(1) NOT NULL,
    date    TIMESTAMP  DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_day (user_id, day),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";

$errors = [];
foreach ($sqls as $sql) {
    try {
        $pdo->exec($sql);
    } catch (PDOException $e) {
        $errors[] = $e->getMessage();
    }
}

header('Content-Type: application/json');
if (empty($errors)) {
    echo json_encode(['message' => 'Toutes les tables créées avec succès ✅']);
} else {
    http_response_code(500);
    echo json_encode(['message' => 'Erreurs lors de la création', 'errors' => $errors]);
}
