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

$db = getDB();
$messages = [];

function tableExists(PDO $db, string $table): bool {
    $stmt = $db->prepare('SHOW TABLES LIKE ?');
    $stmt->execute([$table]);
    return (bool) $stmt->fetchColumn();
}

function columnExists(PDO $db, string $table, string $column): bool {
    if (!tableExists($db, $table)) {
        return false;
    }

    $stmt = $db->prepare("SHOW COLUMNS FROM `$table` LIKE ?");
    $stmt->execute([$column]);
    return (bool) $stmt->fetch();
}

function indexExists(PDO $db, string $table, string $index): bool {
    if (!tableExists($db, $table)) {
        return false;
    }

    $stmt = $db->prepare("SHOW INDEX FROM `$table` WHERE Key_name = ?");
    $stmt->execute([$index]);
    return (bool) $stmt->fetch();
}

function addColumn(PDO $db, string $table, string $column, string $definition, ?string $after = null): void {
    if (!tableExists($db, $table)) {
        return;
    }

    if (!columnExists($db, $table, $column)) {
        $sql = "ALTER TABLE `$table` ADD COLUMN `$column` $definition";
        if ($after !== null) {
            $sql .= " AFTER `$after`";
        }
        $db->exec($sql);
    }
}

function addIndex(PDO $db, string $table, string $index, string $definition): void {
    if (!tableExists($db, $table) || indexExists($db, $table, $index)) {
        return;
    }

    $db->exec("ALTER TABLE `$table` ADD INDEX `$index` ($definition)");
}

function addUnique(PDO $db, string $table, string $index, string $definition): void {
    if (!tableExists($db, $table) || indexExists($db, $table, $index)) {
        return;
    }

    $db->exec("ALTER TABLE `$table` ADD UNIQUE KEY `$index` ($definition)");
}

function addForeignKey(PDO $db, string $table, string $constraint, string $column, string $referenceTable, string $referenceColumn, string $onDelete): void {
    if (!tableExists($db, $table) || !tableExists($db, $referenceTable)) {
        return;
    }

    $stmt = $db->prepare("
        SELECT CONSTRAINT_NAME
        FROM information_schema.KEY_COLUMN_USAGE
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = ?
          AND CONSTRAINT_NAME = ?
    ");
    $stmt->execute([$table, $constraint]);
    if ($stmt->fetch()) {
        return;
    }

    $db->exec("ALTER TABLE `$table` ADD CONSTRAINT `$constraint` FOREIGN KEY (`$column`) REFERENCES `$referenceTable`(`$referenceColumn`) ON DELETE $onDelete");
}

function alterEnum(PDO $db, string $table, string $column, array $values, string $default): void {
    if (!tableExists($db, $table) || !columnExists($db, $table, $column)) {
        return;
    }

    $enum = 'ENUM(\'' . implode('\',\'', $values) . '\')';
    $db->exec("ALTER TABLE `$table` MODIFY COLUMN `$column` $enum NOT NULL DEFAULT '$default'");
}

try {

    $db->exec("
        CREATE TABLE IF NOT EXISTS question_categories (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            slug VARCHAR(100) UNIQUE NOT NULL,
            description TEXT,
            parent_id INT NULL,
            color VARCHAR(20) DEFAULT '#007bff',
            is_active BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (parent_id) REFERENCES question_categories(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS questions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(500) NOT NULL,
            content LONGTEXT NOT NULL,
            correct_answer TEXT NULL,
            question_type ENUM('mcq_single','mcq_multiple','true_false','essay','short_answer','matching','code','fill_blanks') NOT NULL,
            difficulty ENUM('easy','medium','hard') DEFAULT 'medium',
            points DECIMAL(5,2) DEFAULT 1.00,
            time_limit INT NULL,
            category_id INT NULL,
            course_id INT NULL,
            instructor_id INT NOT NULL,
            tags LONGTEXT NULL,
            explanation TEXT NULL,
            hints LONGTEXT NULL,
            metadata LONGTEXT NULL,
            status ENUM('draft','pending_review','approved','rejected','archived') DEFAULT 'draft',
            review_status ENUM('not_submitted','pending','approved','needs_revision') DEFAULT 'not_submitted',
            usage_count INT DEFAULT 0,
            success_rate DECIMAL(5,2) DEFAULT 0.00,
            is_shared BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (category_id) REFERENCES question_categories(id) ON DELETE SET NULL,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
            FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    addColumn($db, 'questions', 'correct_answer', 'TEXT NULL', 'content');

    $db->exec("
        CREATE TABLE IF NOT EXISTS question_options (
            id INT AUTO_INCREMENT PRIMARY KEY,
            question_id INT NOT NULL,
            option_text LONGTEXT NOT NULL,
            option_value VARCHAR(255) NULL,
            match_value VARCHAR(255) NULL,
            is_correct BOOLEAN DEFAULT FALSE,
            sort_order INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    addIndex($db, 'question_options', 'idx_question_options_question', 'question_id');
    addIndex($db, 'question_options', 'idx_question_options_sort_order', 'sort_order');
    addIndex($db, 'question_options', 'idx_question_options_correct', 'is_correct');

    $db->exec("
        CREATE TABLE IF NOT EXISTS exams (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(255) NOT NULL,
            slug VARCHAR(255) UNIQUE NOT NULL,
            description LONGTEXT NULL,
            short_description TEXT NULL,
            course_id INT NULL,
            instructor_id INT NOT NULL,
            exam_type ENUM('quiz','midterm','final','practice','certification') DEFAULT 'quiz',
            total_points DECIMAL(6,2) DEFAULT 0.00,
            passing_score DECIMAL(5,2) DEFAULT 50.00,
            time_limit INT NULL,
            max_attempts INT DEFAULT 1,
            shuffle_questions BOOLEAN DEFAULT FALSE,
            shuffle_options BOOLEAN DEFAULT FALSE,
            show_results BOOLEAN DEFAULT TRUE,
            show_answers BOOLEAN DEFAULT FALSE,
            allow_review BOOLEAN DEFAULT TRUE,
            start_date TIMESTAMP NULL,
            end_date TIMESTAMP NULL,
            is_proctored BOOLEAN DEFAULT FALSE,
            proctoring_settings LONGTEXT NULL,
            security_settings LONGTEXT NULL,
            instructions LONGTEXT NULL,
            status ENUM('draft','published','archived') DEFAULT 'draft',
            is_template BOOLEAN DEFAULT FALSE,
            price DECIMAL(10,2) DEFAULT 0.00,
            currency VARCHAR(3) DEFAULT 'USD',
            is_free BOOLEAN DEFAULT TRUE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE SET NULL,
            FOREIGN KEY (instructor_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    foreach ([
        ['short_description', 'TEXT NULL', 'description'],
        ['exam_type', "ENUM('quiz','midterm','final','practice','certification') DEFAULT 'quiz'", 'instructor_id'],
        ['total_points', 'DECIMAL(6,2) DEFAULT 0.00', 'exam_type'],
        ['shuffle_questions', 'BOOLEAN DEFAULT FALSE', 'max_attempts'],
        ['shuffle_options', 'BOOLEAN DEFAULT FALSE', 'shuffle_questions'],
        ['show_results', 'BOOLEAN DEFAULT TRUE', 'shuffle_options'],
        ['show_answers', 'BOOLEAN DEFAULT FALSE', 'show_results'],
        ['allow_review', 'BOOLEAN DEFAULT TRUE', 'show_answers'],
        ['is_proctored', 'BOOLEAN DEFAULT FALSE', 'end_date'],
        ['proctoring_settings', 'LONGTEXT NULL', 'is_proctored'],
        ['security_settings', 'LONGTEXT NULL', 'proctoring_settings'],
        ['instructions', 'LONGTEXT NULL', 'security_settings'],
        ['is_template', 'BOOLEAN DEFAULT FALSE', 'status'],
        ['price', 'DECIMAL(10,2) DEFAULT 0.00', 'is_free'],
        ['currency', "VARCHAR(3) DEFAULT 'USD'", 'price'],
        ['is_free', 'BOOLEAN DEFAULT TRUE', 'currency']
    ] as $column) {
        addColumn($db, 'exams', $column[0], $column[1], $column[2]);
    }

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_questions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            question_id INT NOT NULL,
            sort_order INT DEFAULT 0,
            points DECIMAL(5,2) DEFAULT 1.00,
            time_limit INT NULL,
            is_required BOOLEAN DEFAULT TRUE,
            section_name VARCHAR(100) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_exam_question (exam_id, question_id),
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    foreach (['time_limit', 'is_required', 'section_name'] as $column) {
        addColumn($db, 'exam_questions', $column, $column === 'time_limit' ? 'INT NULL' : ($column === 'is_required' ? 'BOOLEAN DEFAULT TRUE' : 'VARCHAR(100) NULL'));
    }

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_attempts (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            student_id INT NOT NULL,
            attempt_number INT DEFAULT 1,
            status ENUM('in_progress','completed','submitted','graded','timed_out','abandoned','pending_manual_review','pending_review') DEFAULT 'in_progress',
            started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            completed_at TIMESTAMP NULL,
            time_spent INT DEFAULT 0,
            time_spent_seconds INT DEFAULT 0,
            score DECIMAL(6,2) DEFAULT 0.00,
            max_score DECIMAL(6,2) DEFAULT 0.00,
            percentage DECIMAL(5,2) DEFAULT 0.00,
            is_passed BOOLEAN DEFAULT FALSE,
            answers LONGTEXT NULL,
            feedback TEXT NULL,
            ip_address VARCHAR(45) NULL,
            browser_info LONGTEXT NULL,
            last_accessed TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            graded_at TIMESTAMP NULL,
            graded_by INT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_exam_student_attempt (exam_id, student_id, attempt_number),
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (graded_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    alterEnum($db, 'exam_attempts', 'status', ['in_progress','completed','submitted','graded','timed_out','abandoned','pending_manual_review','pending_review'], 'in_progress');
    addColumn($db, 'exam_attempts', 'time_spent_seconds', 'INT DEFAULT 0', 'time_spent');
    addColumn($db, 'exam_attempts', 'last_accessed', 'TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP', 'browser_info');

    $db->exec("
        CREATE TABLE IF NOT EXISTS student_answers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            attempt_id INT NULL,
            student_id INT NOT NULL,
            question_id INT NOT NULL,
            answer_content LONGTEXT NULL,
            answer_type VARCHAR(20) DEFAULT 'text',
            answer_hash VARCHAR(64) NULL,
            answer_length INT DEFAULT 0,
            word_count INT DEFAULT 0,
            char_count INT DEFAULT 0,
            is_correct BOOLEAN NULL,
            score DECIMAL(10,2) DEFAULT 0.00,
            max_score DECIMAL(10,2) DEFAULT 0.00,
            feedback TEXT NULL,
            is_graded BOOLEAN DEFAULT 0,
            graded_by INT NULL,
            graded_at TIMESTAMP NULL,
            grading_method ENUM('auto','manual','ai','hybrid') NULL,
            grading_completed_at TIMESTAMP NULL,
            partial_credit DECIMAL(5,2) DEFAULT 0.00,
            rubric_score DECIMAL(10,2) DEFAULT 0.00,
            rubric_id INT NULL,
            grade_comments TEXT NULL,
            status ENUM('in_progress','saved','submitted','graded','reviewed','pending_review','pending_manual_review') DEFAULT 'in_progress',
            is_draft BOOLEAN DEFAULT 1,
            is_final BOOLEAN DEFAULT 0,
            is_flagged BOOLEAN DEFAULT 0,
            flagged_at TIMESTAMP NULL,
            needs_review BOOLEAN DEFAULT 0,
            review_comments TEXT NULL,
            source VARCHAR(20) DEFAULT 'web',
            session_id VARCHAR(100) NULL,
            client_timezone VARCHAR(50) DEFAULT 'UTC',
            grading_status ENUM('pending','auto_graded','manually_graded','partially_graded','regraded') DEFAULT 'pending',
            auto_grade_eligible BOOLEAN DEFAULT 1,
            time_spent_seconds INT NULL,
            first_save_at TIMESTAMP NULL,
            last_save_at TIMESTAMP NULL,
            save_count INT DEFAULT 0,
            submitted_at TIMESTAMP NULL,
            started_at TIMESTAMP NULL,
            ip_address VARCHAR(45) NULL,
            browser_info LONGTEXT NULL,
            device_info LONGTEXT NULL,
            version_number INT DEFAULT 1,
            previous_answer_id INT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY exam_attempt_question (exam_id, attempt_id, question_id),
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (attempt_id) REFERENCES exam_attempts(id) ON DELETE SET NULL,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
            FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
            FOREIGN KEY (graded_by) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY (previous_answer_id) REFERENCES student_answers(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_question_grades (
            id INT AUTO_INCREMENT PRIMARY KEY,
            attempt_id INT NOT NULL,
            question_id INT NOT NULL,
            points_earned DECIMAL(10,2) NOT NULL DEFAULT 0,
            max_points DECIMAL(10,2) DEFAULT 0.00,
            feedback TEXT NULL,
            is_correct BOOLEAN NULL,
            graded_by INT NULL,
            graded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            grading_method ENUM('auto','manual','ai','hybrid') DEFAULT 'auto',
            grading_status ENUM('pending','auto_graded','manually_graded','partially_graded','regraded') DEFAULT 'auto_graded',
            grade_comments TEXT NULL,
            partial_credit DECIMAL(5,2) DEFAULT 0.00,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_attempt_question (attempt_id, question_id),
            FOREIGN KEY (attempt_id) REFERENCES exam_attempts(id) ON DELETE CASCADE,
            FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
            FOREIGN KEY (graded_by) REFERENCES users(id) ON DELETE SET NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    foreach ([
        ['max_points', 'DECIMAL(10,2) DEFAULT 0.00', 'points_earned'],
        ['is_correct', 'BOOLEAN NULL', 'max_points'],
        ['grading_method', "ENUM('auto','manual','ai','hybrid') DEFAULT 'auto'", 'graded_at'],
        ['grading_status', "ENUM('pending','auto_graded','manually_graded','partially_graded','regraded') DEFAULT 'auto_graded'", 'grading_method'],
        ['grade_comments', 'TEXT NULL', 'grading_status'],
        ['partial_credit', 'DECIMAL(5,2) DEFAULT 0.00', 'grade_comments']
    ] as $column) {
        addColumn($db, 'exam_question_grades', $column[0], $column[1], $column[2]);
    }

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_access (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            student_id INT NOT NULL,
            payment_status ENUM('pending','paid','free') DEFAULT 'free',
            payment_amount DECIMAL(10,2) DEFAULT 0.00,
            currency VARCHAR(3) DEFAULT 'USD',
            access_granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_exam_student (exam_id, student_id),
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_reviews (
            id INT AUTO_INCREMENT PRIMARY KEY,
            exam_id INT NOT NULL,
            student_id INT NOT NULL,
            rating INT NOT NULL,
            review_title VARCHAR(255) NULL,
            review_text TEXT NULL,
            is_verified BOOLEAN DEFAULT FALSE,
            helpful_votes INT DEFAULT 0,
            status ENUM('pending','approved','rejected') DEFAULT 'pending',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY unique_exam_review (exam_id, student_id),
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS question_tags (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(50) UNIQUE NOT NULL,
            slug VARCHAR(50) UNIQUE NOT NULL,
            color VARCHAR(20) DEFAULT '#6c757d',
            description TEXT NULL,
            usage_count INT DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS question_tag_assignments (
            question_id INT NOT NULL,
            tag_id INT NOT NULL,
            assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (question_id, tag_id),
            FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
            FOREIGN KEY (tag_id) REFERENCES question_tags(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS question_reviews (
            id INT AUTO_INCREMENT PRIMARY KEY,
            question_id INT NOT NULL,
            reviewer_id INT NOT NULL,
            review_status ENUM('approved','needs_revision','rejected') NOT NULL,
            comments TEXT NULL,
            reviewed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
            FOREIGN KEY (reviewer_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS question_analytics (
            question_id INT NOT NULL,
            exam_id INT NOT NULL,
            course_id INT 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,
            difficulty_rating DECIMAL(3,2) DEFAULT 0.00,
            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,
            FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    addColumn($db, 'question_analytics', 'success_rate', 'DECIMAL(5,2) DEFAULT 0', 'incorrect_answers');

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_submissions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            attempt_id INT NOT NULL,
            exam_id INT NOT NULL,
            student_id INT NOT NULL,
            submission_data LONGTEXT NULL,
            ip_address VARCHAR(45) NULL,
            user_agent TEXT NULL,
            is_auto_submitted BOOLEAN DEFAULT 0,
            time_spent_seconds INT DEFAULT 0,
            time_limit_seconds INT DEFAULT 0,
            is_within_time_limit BOOLEAN DEFAULT 1,
            answers_count INT DEFAULT 0,
            questions_count INT DEFAULT 0,
            unanswered_count INT DEFAULT 0,
            processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (attempt_id) REFERENCES exam_attempts(id) ON DELETE CASCADE,
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS exam_attempt_abandonment_log (
            id INT AUTO_INCREMENT PRIMARY KEY,
            attempt_id INT NOT NULL,
            exam_id INT NOT NULL,
            student_id INT NOT NULL,
            abandonment_type VARCHAR(50) DEFAULT 'manual',
            last_answer_data LONGTEXT NULL,
            time_spent_seconds INT DEFAULT 0,
            browser_info LONGTEXT NULL,
            ip_address VARCHAR(45) NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (attempt_id) REFERENCES exam_attempts(id) ON DELETE CASCADE,
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    addIndex($db, 'exam_attempts', 'idx_exam_attempts_last_accessed', 'last_accessed');
    addIndex($db, 'exam_attempts', 'idx_exam_attempts_exam_student_status', 'exam_id, student_id, status');
    addIndex($db, 'student_answers', 'idx_student_answers_attempt_question', 'attempt_id, question_id');
    addIndex($db, 'student_answers', 'idx_student_answers_exam_status', 'exam_id, status');
    addIndex($db, 'student_answers', 'idx_student_answers_graded', 'is_graded');
    addIndex($db, 'exam_question_grades', 'idx_exam_question_grades_attempt', 'attempt_id');
    addIndex($db, 'exam_question_grades', 'idx_exam_question_grades_question', 'question_id');

} catch (Throwable $e) {
    http_response_code(500);
    echo "Functional examination schema migration failed: " . $e->getMessage();
    exit;
}

$messages[] = 'Functional examination schema migration applied successfully.';

foreach ($messages as $message) {
    echo $message . PHP_EOL;
}
