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

try {
    // 1. Create Classes Table
    $pdo->exec("CREATE TABLE IF NOT EXISTS classes (
        class_id INT AUTO_INCREMENT PRIMARY KEY,
        class_name VARCHAR(100) NOT NULL UNIQUE
    ) ENGINE=InnoDB");
    echo "Classes table created.<br>";

    // 2. Update Users Table
    // Check if class_id exists
    $stmt = $pdo->query("SHOW COLUMNS FROM users LIKE 'class_id'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE users ADD COLUMN class_id INT DEFAULT NULL");
        $pdo->exec("ALTER TABLE users ADD CONSTRAINT fk_user_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL");
        echo "Added class_id to users.<br>";
    }

    // 3. Update Questions Table
    $stmt = $pdo->query("SHOW COLUMNS FROM questions LIKE 'option_e'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE questions ADD COLUMN option_e VARCHAR(255) DEFAULT NULL AFTER option_d");
        $pdo->exec("ALTER TABLE questions ADD COLUMN image_path VARCHAR(255) DEFAULT NULL AFTER question_text");
        $pdo->exec("ALTER TABLE questions ADD COLUMN session VARCHAR(20) DEFAULT NULL");
        $pdo->exec("ALTER TABLE questions ADD COLUMN term VARCHAR(20) DEFAULT NULL");
        // Update correct_answer enum to include 'E'
        $pdo->exec("ALTER TABLE questions MODIFY COLUMN correct_answer ENUM('A', 'B', 'C', 'D', 'E') NOT NULL");
        echo "Updated questions table (Option E, Image, Session, Term).<br>";
    }

    // 4. Update Exams Table
    $stmt = $pdo->query("SHOW COLUMNS FROM exams LIKE 'class_id'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("ALTER TABLE exams ADD COLUMN class_id INT DEFAULT NULL");
        $pdo->exec("ALTER TABLE exams ADD COLUMN subject_id INT DEFAULT NULL"); // Make explicit
        $pdo->exec("ALTER TABLE exams ADD COLUMN randomize_questions TINYINT(1) DEFAULT 1");
        $pdo->exec("ALTER TABLE exams ADD CONSTRAINT fk_exam_class FOREIGN KEY (class_id) REFERENCES classes(class_id) ON DELETE SET NULL");
        // We already have subject_id in questions, but exams might need to be linked to a subject explicitly for selection
        // Check if subject_id constraint needs to be added (if column didn't exist)
        // Note: prompt said 'Questions' table has subject_id. Exams table didn't have it in original prompt schema.
        $pdo->exec("ALTER TABLE exams ADD CONSTRAINT fk_exam_subject FOREIGN KEY (subject_id) REFERENCES subjects(subject_id) ON DELETE SET NULL");
        echo "Updated exams table (Class, Subject, Randomize).<br>";
    }

    // 5. Create Exam Assignments Table (for checkbox selection)
    $pdo->exec("CREATE TABLE IF NOT EXISTS exam_assignments (
        assignment_id INT AUTO_INCREMENT PRIMARY KEY,
        exam_id INT NOT NULL,
        user_id INT NOT NULL,
        assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (exam_id) REFERENCES exams(exam_id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
        UNIQUE KEY unique_assignment (exam_id, user_id)
    ) ENGINE=InnoDB");
    echo "Exam assignments table created.<br>";

} catch (PDOException $e) {
    echo "Schema update failed: " . $e->getMessage();
}
