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

try {
    $pdo = getDB();

    // Create exam_reviews table
    $pdo->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 CHECK (rating >= 1 AND rating <= 5),
            review_title VARCHAR(255),
            review_text TEXT,
            is_verified BOOLEAN DEFAULT FALSE, -- completed the exam
            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,
            FOREIGN KEY (exam_id) REFERENCES exams(id) ON DELETE CASCADE,
            FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
            UNIQUE KEY unique_exam_review (exam_id, student_id),
            INDEX idx_exam (exam_id),
            INDEX idx_student (student_id),
            INDEX idx_rating (rating),
            INDEX idx_status (status)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Add index for better performance
    $pdo->exec("CREATE INDEX idx_exam_reviews_exam_rating ON exam_reviews(exam_id, rating)");
    $pdo->exec("CREATE INDEX idx_exam_reviews_created ON exam_reviews(created_at)");

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