<?php
require_once 'includes/init.php';

try {
    // Check if column exists
    $stmt = $pdo->prepare("SHOW COLUMNS FROM exams LIKE 'allowed_attempts'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN allowed_attempts INT DEFAULT 1");
        echo "Added allowed_attempts to exams table.<br>";
    } else {
        echo "allowed_attempts column already exists.<br>";
    }

    // Add exam_type if missing
    $stmt = $pdo->prepare("SHOW COLUMNS FROM exams LIKE 'exam_type'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN exam_type ENUM('test','exam') NOT NULL DEFAULT 'test'");
        echo "Added exam_type to exams table.<br>";
    } else {
        echo "exam_type column already exists.<br>";
    }

    // Add total_score if missing
    $stmt = $pdo->prepare("SHOW COLUMNS FROM exams LIKE 'total_score'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN total_score INT NOT NULL DEFAULT 20");
        echo "Added total_score to exams table.<br>";
    } else {
        echo "total_score column already exists.<br>";
    }

    // Add scheduling columns if missing
    $stmt = $pdo->prepare("SHOW COLUMNS FROM exams LIKE 'start_at'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN start_at DATETIME DEFAULT NULL");
        echo "Added start_at to exams table.<br>";
    } else {
        echo "start_at column already exists.<br>";
    }
    $stmt = $pdo->prepare("SHOW COLUMNS FROM exams LIKE 'end_at'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN end_at DATETIME DEFAULT NULL");
        echo "Added end_at to exams table.<br>";
    } else {
        echo "end_at column already exists.<br>";
    }
    
    // Add question_type to questions if missing
    $stmt = $pdo->prepare("SHOW COLUMNS FROM questions LIKE 'question_type'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE questions ADD COLUMN question_type ENUM('single_choice','multiple_choice','true_false') NOT NULL DEFAULT 'single_choice' AFTER term");
        echo "Added question_type to questions table.<br>";
    } else {
        echo "question_type column already exists.<br>";
    }
    
    // Create student_activity_log table if missing
    $stmt = $pdo->prepare("SHOW TABLES LIKE 'student_activity_log'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("
            CREATE TABLE student_activity_log (
                log_id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                activity VARCHAR(50) NOT NULL,
                exam_id INT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX idx_user_created (user_id, created_at),
                CONSTRAINT fk_log_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
                CONSTRAINT fk_log_exam FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE SET NULL
            ) ENGINE=InnoDB
        ");
        echo "Created student_activity_log table.<br>";
    } else {
        echo "student_activity_log table already exists.<br>";
    }
    
    // Create per-student schedule table
    $stmt = $pdo->prepare("SHOW TABLES LIKE 'student_exam_schedule'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("
            CREATE TABLE student_exam_schedule (
                schedule_id INT AUTO_INCREMENT PRIMARY KEY,
                exam_id INT NOT NULL,
                user_id INT NOT NULL,
                slot_start DATETIME NOT NULL,
                slot_end DATETIME NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_exam_user (exam_id, user_id),
                CONSTRAINT fk_ses_exam FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE CASCADE,
                CONSTRAINT fk_ses_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
            ) ENGINE=InnoDB
        ");
        echo "Created student_exam_schedule table.<br>";
    } else {
        echo "student_exam_schedule table already exists.<br>";
    }

    // Add layout_preset to exams if missing
    $stmt = $pdo->prepare("SHOW COLUMNS FROM exams LIKE 'layout_preset'");
    $stmt->execute();
    if (!$stmt->fetch()) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN layout_preset ENUM('compact','reading') DEFAULT 'compact' AFTER total_score");
        echo "Added layout_preset to exams table.<br>";
    } else {
        echo "layout_preset column already exists.<br>";
    }

    echo "Database updates applied successfully.";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>
