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

try {
    $pdo = getDB();

    // Create exam_access table to track purchased exam access
    $pdo->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') NOT NULL 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
    ");

    // Add indexes for better performance
    $pdo->exec("CREATE INDEX idx_exam_access_student ON exam_access (student_id)");
    $pdo->exec("CREATE INDEX idx_exam_access_exam ON exam_access (exam_id)");
    $pdo->exec("CREATE INDEX idx_exam_access_payment ON exam_access (payment_status)");

    echo "Exam access migration applied successfully.";
} catch (PDOException $e) {
    echo "Error applying exam access migration: " . $e->getMessage();
}
?>