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

try {
    $db = getDB();
    
    echo "Creating exam_question_grades table...\n";
    
    $sql = "
    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,
        feedback TEXT,
        graded_by INT,
        graded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        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,
        UNIQUE KEY unique_attempt_question (attempt_id, question_id),
        INDEX idx_attempt (attempt_id),
        INDEX idx_question (question_id),
        INDEX idx_graded_by (graded_by)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ";
    
    $db->exec($sql);
    echo "✓ exam_question_grades table created successfully\n";
    
    echo "\nMigration completed successfully!\n";
    
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage() . "\n";
    exit(1);
}
?>