<?php
/**
 * Migration Script: Populate student_answers from existing exam_attempts
 * 
 * This script migrates existing exam answers from exam_attempts.answers JSON
 * to the student_answers table so they can be viewed in the grading interface.
 */

require_once 'config/database.php';

echo "<h1>Migrating student_answers from existing exam attempts</h1>";
echo "<pre style='background: #f5f5f5; padding: 20px; border-radius: 5px;'>";

$db = getDB();

// Get all completed exam attempts that have answers
$stmt = $db->query("
    SELECT ea.id as attempt_id, ea.exam_id, ea.student_id, ea.answers, ea.completed_at,
           e.title as exam_title
    FROM exam_attempts ea
    JOIN exams e ON ea.exam_id = e.id
    WHERE ea.answers IS NOT NULL AND ea.answers != '' AND ea.answers != '{}'
    ORDER BY ea.id DESC
");

$attempts = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (empty($attempts)) {
    echo "No exam attempts with answers found.\n";
} else {
    echo "Found " . count($attempts) . " exam attempt(s) with answers to migrate.\n\n";
    
    $totalMigrated = 0;
    
    foreach ($attempts as $attempt) {
        $attemptId = $attempt['attempt_id'];
        $examId = $attempt['exam_id'];
        $studentId = $attempt['student_id'];
        
        echo "Processing Attempt ID: {$attemptId} - Exam: {$attempt['exam_title']}\n";
        
        // Get exam questions
        $stmt2 = $db->prepare("
            SELECT eq.question_id, eq.points, q.question_type
            FROM exam_questions eq
            JOIN questions q ON eq.question_id = q.id
            WHERE eq.exam_id = ?
        ");
        $stmt2->execute([$examId]);
        $questions = $stmt2->fetchAll(PDO::FETCH_ASSOC);
        
        if (empty($questions)) {
            echo "  ⚠️ No questions found for exam {$examId}, skipping...\n";
            continue;
        }
        
        // Decode the answers JSON
        $answers = json_decode($attempt['answers'], true);
        if (!$answers || !is_array($answers)) {
            echo "  ⚠️ No valid answers found in JSON, skipping...\n";
            continue;
        }
        
        // Get client metadata
        $ipAddress = '';
        
        // Delete any existing answers for this attempt
        $stmt2 = $db->prepare("DELETE FROM student_answers WHERE exam_id = ? AND student_id = ? AND attempt_id = ?");
        $stmt2->execute([$examId, $studentId, $attemptId]);
        
        $inserted = 0;
        
        foreach ($questions as $question) {
            $questionId = $question['question_id'];
            $maxPoints = floatval($question['points']);
            $questionType = $question['question_type'];
            $studentAnswer = $answers[$questionId] ?? null;
            
            if ($studentAnswer === null || $studentAnswer === '') {
                continue; // Skip unanswered questions
            }
            
            // Determine answer content and type
            if (is_array($studentAnswer)) {
                $answerContent = json_encode($studentAnswer);
                $answerType = 'json';
            } else {
                $answerContent = $studentAnswer;
                $answerType = in_array($questionType, ['mcq_single', 'true_false']) ? 'numeric' : 'text';
            }
            
            // Determine status
            $status = 'submitted';
            $isGraded = 0;
            $gradingMethod = 'auto';
            $isCorrect = null;
            $score = 0;
            
            // Auto-grade objective questions
            if (in_array($questionType, ['mcq_single', 'mcq_multiple', 'true_false', 'matching', 'fill_blanks'])) {
                $gradingMethod = 'auto';
                $isGraded = 1;
            } else {
                // Manual grading needed
                $gradingMethod = 'manual';
                $isGraded = 0;
            }
            
            // Insert into student_answers
            try {
                $stmt2 = $db->prepare("
                    INSERT INTO student_answers (
                        exam_id, student_id, attempt_id, question_id,
                        answer_content, answer_type, is_correct, score, max_score,
                        is_graded, grading_method, time_spent_seconds,
                        ip_address, submitted_at, started_at,
                        status, is_draft, is_final, source
                    ) VALUES (
                        ?, ?, ?, ?,
                        ?, ?, ?, ?, ?,
                        ?, ?, 0,
                        ?, NOW(), NOW(),
                        ?, 0, 1, 'web'
                    )
                ");
                
                $stmt2->execute([
                    $examId,
                    $studentId,
                    $attemptId,
                    $questionId,
                    $answerContent,
                    $answerType,
                    $isCorrect,
                    $score,
                    $maxPoints,
                    $isGraded,
                    $gradingMethod,
                    $ipAddress,
                    $status
                ]);
                
                $inserted++;
            } catch (PDOException $e) {
                echo "  Error inserting answer for question {$questionId}: " . $e->getMessage() . "\n";
            }
        }
        
        echo "  Migrated {$inserted} answer(s)\n";
        $totalMigrated += $inserted;
    }
    
    echo "\n=== Migration Complete ===\n";
    echo "Total answers migrated: {$totalMigrated}\n";
}

echo "</pre>";

// Show final counts
echo "<h3>Summary</h3>";
$stmt = $db->query("SELECT COUNT(*) as total FROM exam_attempts WHERE answers IS NOT NULL AND answers != ''");
$totalAttempts = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

$stmt = $db->query("SELECT COUNT(*) as total FROM student_answers");
$totalAnswers = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

echo "<p>Total exam attempts with answers: {$totalAttempts}</p>";
echo "<p>Total student_answers records now: {$totalAnswers}</p>";

if ($totalAnswers > 0) {
    echo "<p style='color: green; font-weight: bold;'>✅ Migration successful!</p>";
}
?>
