<?php
require_once 'config/database.php';

try {
    $db = getDB();

    echo "Creating question_analytics table...\n";

    $sql = "
    CREATE TABLE IF NOT EXISTS question_analytics (
        question_id INT NOT NULL,
        exam_id INT NOT NULL,
        date DATE NOT NULL,
        times_used INT DEFAULT 0,
        correct_answers INT DEFAULT 0,
        incorrect_answers INT DEFAULT 0,
        success_rate DECIMAL(5,2) DEFAULT 0,
        average_time INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY (question_id, exam_id, date),
        FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
        FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
        INDEX idx_question (question_id),
        INDEX idx_exam (exam_id),
        INDEX idx_date (date)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";

    $db->exec($sql);
    echo "✓ question_analytics table created successfully\n";

    echo "\nMigration completed successfully!\n";

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
    exit(1);
}
?>