<?php
/**
 * Exam Service Class
 * 
 * Provides comprehensive functionality for managing exam attempts,
 * submissions, and grading workflows. This class handles:
 * - Starting, saving, and submitting exam attempts
 * - Time limit calculations and enforcement
 * - Student answer tracking and grading
 * - Both automatic and manual grading workflows
 * - Complete exam submission retrieval
 */

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

class ExamService {
    private $db;
    
    public function __construct() {
        $this->db = getDB();
    }
    
    // ===========================================================
    // ATTEMPT MANAGEMENT
    // ===========================================================
    
    /**
     * Start a new exam attempt for a student
     */
    public function startAttempt($examId, $studentId, $examDetails = null) {
        // Get exam details if not provided
        if ($examDetails === null) {
            $examDetails = $this->getExamDetails($examId);
            if (!$examDetails) {
                throw new Exception('Exam not found');
            }
        }
        
        // Check for existing in-progress attempt
        $existingAttempt = $this->getActiveAttempt($examId, $studentId);
        if ($existingAttempt) {
            // Check if time has expired
            if ($this->isTimeExpired($existingAttempt)) {
                // Auto-submit the expired attempt
                $this->autoSubmitExpiredAttempt($existingAttempt['id']);
                // Start fresh attempt
            } else {
                return [
                    'success' => true,
                    'resumed' => true,
                    'attempt_id' => $existingAttempt['id'],
                    'attempt_number' => $existingAttempt['attempt_number'],
                    'time_remaining' => $this->getTimeRemaining($existingAttempt)
                ];
            }
        }
        
        // Check attempt limits
        $attemptCount = $this->getAttemptCount($examId, $studentId);
        $maxAttempts = intval($examDetails['max_attempts'] ?? 0);
        
        if ($maxAttempts > 0 && $attemptCount >= $maxAttempts) {
            throw new Exception('Maximum attempts exceeded');
        }
        
        // Calculate time limit
        $timeLimitMinutes = $examDetails['time_limit'] ?? null;
        $expiresAt = null;
        
        if ($timeLimitMinutes) {
            $expiresAt = date('Y-m-d H:i:s', strtotime("+{$timeLimitMinutes} minutes"));
        }
        
        // Create new attempt
        $stmt = $this->db->prepare("
            INSERT INTO exam_attempts (
                exam_id, student_id, attempt_number, status, started_at, expires_at,
                time_limit_minutes, answers, ip_address, browser_info, exam_version,
                randomized_questions, max_tab_switches, device_info
            ) VALUES (?, ?, ?, 'in_progress', NOW(), ?, ?, '{}', ?, ?, ?, ?, ?, ?)
        ");
        
        $ipAddress = $_SERVER['REMOTE_ADDR'] ?? '';
        $browserInfo = json_encode([
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
            'accept_language' => $_SERVER['HTTP_ACCEPT_LANGUAGE'] ?? ''
        ]);
        $examVersion = $examDetails['version'] ?? '1.0';
        $randomize = ($examDetails['randomize_questions'] ?? 0) == 1;
        $maxTabSwitches = $examDetails['max_tab_switches'] ?? 0;
        
        $attemptNumber = $attemptCount + 1;
        
        $stmt->execute([
            $examId, $studentId, $attemptNumber, $expiresAt, $timeLimitMinutes,
            $ipAddress, $browserInfo, $examVersion, $randomize, $maxTabSwitches
        ]);
        
        $attemptId = $this->db->lastInsertId();
        
        return [
            'success' => true,
            'resumed' => false,
            'attempt_id' => $attemptId,
            'attempt_number' => $attemptNumber,
            'time_limit_minutes' => $timeLimitMinutes,
            'expires_at' => $expiresAt,
            'time_remaining' => $timeLimitMinutes ? $timeLimitMinutes * 60 : null
        ];
    }
    
    /**
     * Get active (in-progress) attempt for an exam
     */
    public function getActiveAttempt($examId, $studentId) {
        $stmt = $this->db->prepare("
            SELECT *, time_spent_seconds as time_spent FROM exam_attempts
            WHERE exam_id = ? AND student_id = ? AND status = 'in_progress'
            ORDER BY started_at DESC LIMIT 1
        ");
        $stmt->execute([$examId, $studentId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get attempt by ID
     */
    public function getAttemptById($attemptId) {
        $stmt = $this->db->prepare("
            SELECT ea.*, e.title as exam_title, e.time_limit, e.passing_score,
                   e.total_points, e.max_attempts,
                   ea.time_spent_seconds as time_spent
            FROM exam_attempts ea
            JOIN exams e ON ea.exam_id = e.id
            WHERE ea.id = ?
        ");
        $stmt->execute([$attemptId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get total number of attempts for an exam
     */
    public function getAttemptCount($examId, $studentId) {
        $stmt = $this->db->prepare("
            SELECT COUNT(*) as count FROM exam_attempts
            WHERE exam_id = ? AND student_id = ?
        ");
        $stmt->execute([$examId, $studentId]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return intval($result['count']);
    }
    
    /**
     * Check if attempt time has expired
     */
    public function isTimeExpired($attempt) {
        if (empty($attempt['expires_at'])) {
            return false;
        }
        return strtotime($attempt['expires_at']) < time();
    }
    
    /**
     * Get remaining time in seconds
     */
    public function getTimeRemaining($attempt) {
        if (empty($attempt['expires_at'])) {
            return null;
        }
        $remaining = strtotime($attempt['expires_at']) - time();
        return max(0, $remaining);
    }
    
    /**
     * Auto-submit an expired attempt
     */
    public function autoSubmitExpiredAttempt($attemptId) {
        $attempt = $this->getAttemptById($attemptId);
        if (!$attempt || $attempt['status'] !== 'in_progress') {
            return false;
        }
        
        // Get current answers
        $answers = json_decode($attempt['answers'] ?? '{}', true) ?: [];
        
        // Calculate time spent
        $timeSpent = $attempt['time_spent'] ?? 0;
        
        // Update attempt as auto-submitted
        $stmt = $this->db->prepare("
            UPDATE exam_attempts
            SET status = 'completed',
                completed_at = NOW(),
                time_spent_seconds = ?,
                is_time_expired = TRUE,
                auto_submitted = TRUE,
                is_flagged = TRUE
            WHERE id = ?
        ");
        $stmt->execute([$timeSpent, $attemptId]);
        
        // Save student answers
        $this->saveStudentAnswers($attempt['exam_id'], $attempt['student_id'], $attemptId, $answers, $timeSpent);
        
        // Create submission record
        $this->createSubmissionRecord($attemptId, $attempt['exam_id'], $attempt['student_id'], $answers, $timeSpent, true);
        
        return true;
    }
    
    // ===========================================================
    // SAVE & SUBMISSION
    // ===========================================================
    
    /**
     * Save attempt progress
     */
    public function saveProgress($attemptId, $answers, $timeSpent = 0) {
        $attempt = $this->getAttemptById($attemptId);
        if (!$attempt || $attempt['status'] !== 'in_progress') {
            throw new Exception('No active attempt found');
        }
        
        // Check time expiration
        if ($this->isTimeExpired($attempt)) {
            $this->autoSubmitExpiredAttempt($attemptId);
            throw new Exception('Time expired - attempt auto-submitted');
        }
        
        // Merge with existing answers
        $existingAnswers = json_decode($attempt['answers'] ?? '{}', true) ?: [];
        $mergedAnswers = array_merge($existingAnswers, $answers);
        
        // Update attempt
        $stmt = $this->db->prepare("
            UPDATE exam_attempts
            SET answers = ?, time_spent_seconds = ?, last_accessed = NOW()
            WHERE id = ? AND status = 'in_progress'
        ");
        $stmt->execute([json_encode($mergedAnswers), $timeSpent, $attemptId]);
        
        // Save individual answers to student_answers table
        foreach ($answers as $questionId => $answerContent) {
            $this->saveOrUpdateStudentAnswer(
                $attempt['exam_id'],
                $attempt['student_id'],
                $attemptId,
                $questionId,
                $answerContent,
                'saved'
            );
        }
        
        return [
            'success' => true,
            'message' => 'Progress saved',
            'time_remaining' => $this->getTimeRemaining($attempt)
        ];
    }
    
    /**
     * Submit an exam attempt
     */
    public function submitExam($attemptId, $answers, $timeSpent = 0, $examDetails = null) {
        $attempt = $this->getAttemptById($attemptId);
        if (!$attempt || $attempt['status'] !== 'in_progress') {
            throw new Exception('No active attempt found');
        }
        
        // Get exam details if not provided
        if ($examDetails === null) {
            $examDetails = $this->getExamDetails($attempt['exam_id']);
        }
        
        // Calculate time-based constraints
        $timeLimit = intval($attempt['time_limit_minutes'] ?? 0) * 60;
        $isWithinTimeLimit = ($timeSpent <= $timeLimit) || ($timeLimit === 0);
        $submittedEarly = ($timeSpent < $timeLimit) && ($timeLimit > 0);
        
        // Merge answers
        $existingAnswers = json_decode($attempt['answers'] ?? '{}', true) ?: [];
        $mergedAnswers = array_merge($existingAnswers, $answers);
        
        // Calculate score (auto-graded questions only)
        $scoreData = $this->calculateScore($attempt['exam_id'], $mergedAnswers, $examDetails['passing_score'] ?? 50);
        
        // Begin transaction
        $this->db->beginTransaction();
        
        try {
            // Update attempt status
            $stmt = $this->db->prepare("
                UPDATE exam_attempts
                SET status = 'completed',
                    completed_at = NOW(),
                    time_spent_seconds = ?,
                    answers = ?,
                    score = ?,
                    max_score = ?,
                    percentage = ?,
                    is_passed = ?,
                    is_time_expired = ?,
                    submitted_early = ?,
                    last_accessed = NOW()
                WHERE id = ?
            ");
            
            $stmt->execute([
                $timeSpent,
                json_encode($mergedAnswers),
                $scoreData['score'],
                $scoreData['max_score'],
                $scoreData['percentage'],
                $scoreData['is_passed'] ? 1 : 0,
                $this->isTimeExpired($attempt) ? 1 : 0,
                $submittedEarly ? 1 : 0,
                $attemptId
            ]);
            
            // Save student answers with full details
            $this->saveStudentAnswers($attempt['exam_id'], $attempt['student_id'], $attemptId, $mergedAnswers, $timeSpent, $scoreData);
            
            // Create submission record
            $this->createSubmissionRecord(
                $attemptId,
                $attempt['exam_id'],
                $attempt['student_id'],
                $mergedAnswers,
                $timeSpent,
                $this->isTimeExpired($attempt),
                $isWithinTimeLimit
            );
            
            $this->db->commit();
            
            return [
                'success' => true,
                'message' => 'Exam submitted successfully',
                'score' => $scoreData['score'],
                'max_score' => $scoreData['max_score'],
                'percentage' => $scoreData['percentage'],
                'passed' => $scoreData['is_passed'],
                'within_time_limit' => $isWithinTimeLimit,
                'submitted_early' => $submittedEarly,
                'time_spent' => $timeSpent,
                'time_limit' => $timeLimit
            ];
            
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
    
    /**
     * Mark an attempt as abandoned
     */
    public function abandonAttempt($attemptId, $reason = 'manual') {
        $attempt = $this->getAttemptById($attemptId);
        if (!$attempt || $attempt['status'] !== 'in_progress') {
            throw new Exception('No active attempt found');
        }
        
        $stmt = $this->db->prepare("
            UPDATE exam_attempts
            SET status = 'abandoned',
                abandoned_at = NOW(),
                abandonment_reason = ?,
                last_accessed = NOW()
            WHERE id = ?
        ");
        $stmt->execute([$reason, $attemptId]);
        
        // Log abandonment
        $stmt = $this->db->prepare("
            INSERT INTO exam_attempt_abandonment_log (
                attempt_id, exam_id, student_id, abandonment_type,
                last_answer_data, time_spent_seconds, browser_info, ip_address
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        ");
        
        $stmt->execute([
            $attemptId,
            $attempt['exam_id'],
            $attempt['student_id'],
            $reason,
            $attempt['answers'],
            $attempt['time_spent'] ?? 0,
            $attempt['browser_info'],
            $attempt['ip_address']
        ]);
        
        return ['success' => true, 'message' => 'Attempt abandoned'];
    }
    
    // ===========================================================
    // STUDENT ANSWERS
    // ===========================================================
    
    /**
     * Save or update a student's answer
     */
    public function saveOrUpdateStudentAnswer($examId, $studentId, $attemptId, $questionId, $answerContent, $status = 'in_progress') {
        // Determine answer type
        $answerType = $this->determineAnswerType($answerContent);
        
        // Calculate metadata
        $answerLength = is_array($answerContent) ? 0 : strlen($answerContent);
        $wordCount = is_array($answerContent) ? 0 : str_word_count($answerContent);
        
        // Check if answer exists
        $stmt = $this->db->prepare("
            SELECT id, answer_content FROM student_answers
            WHERE exam_id = ? AND student_id = ? AND attempt_id = ? AND question_id = ?
        ");
        $stmt->execute([$examId, $studentId, $attemptId, $questionId]);
        $existing = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($existing) {
            // Update existing answer
            $stmt = $this->db->prepare("
                UPDATE student_answers
                SET answer_content = ?,
                    answer_type = ?,
                    answer_length = ?,
                    word_count = ?,
                    status = ?,
                    is_draft = ?,
                    last_save_at = NOW(),
                    save_count = save_count + 1,
                    answer_hash = MD5(?)
                WHERE id = ?
            ");
            
            $content = is_array($answerContent) ? json_encode($answerContent) : $answerContent;
            $stmt->execute([
                $content,
                $answerType,
                $answerLength,
                $wordCount,
                $status,
                ($status === 'in_progress') ? 1 : 0,
                $content,
                $existing['id']
            ]);
            
            return $existing['id'];
        } else {
            // Insert new answer
            $stmt = $this->db->prepare("
                INSERT INTO student_answers (
                    exam_id, student_id, attempt_id, question_id,
                    answer_content, answer_type, answer_length, word_count,
                    status, is_draft, first_save_at, last_save_at, save_count,
                    answer_hash, source
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW(), 1, MD5(?), 'web')
            ");
            
            $content = is_array($answerContent) ? json_encode($answerContent) : $answerContent;
            $stmt->execute([
                $examId, $studentId, $attemptId, $questionId,
                $content, $answerType, $answerLength, $wordCount,
                $status, ($status === 'in_progress') ? 1 : 0, $content
            ]);
            
            return $this->db->lastInsertId();
        }
    }
    
    /**
     * Save all student answers from submission
     */
    public function saveStudentAnswers($examId, $studentId, $attemptId, $answers, $timeSpent = 0, $scoreData = null) {
        // Get exam questions for max score
        $questions = $this->getExamQuestions($examId);
        $questionPoints = [];
        foreach ($questions as $q) {
            $questionPoints[$q['id']] = $q['points'];
        }
        
        // Get existing answers
        $stmt = $this->db->prepare("
            SELECT question_id, answer_content FROM student_answers
            WHERE attempt_id = ? AND question_id IN (" . implode(',', array_keys($answers)) . ")
        ");
        $stmt->execute([$attemptId]);
        $existingAnswers = [];
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $existingAnswers[$row['question_id']] = $row['answer_content'];
        }
        
        foreach ($answers as $questionId => $answerContent) {
            $content = is_array($answerContent) ? json_encode($answerContent) : $answerContent;
            $answerType = $this->determineAnswerType($answerContent);
            $answerLength = is_array($answerContent) ? 0 : strlen($content);
            $wordCount = is_array($answerContent) ? 0 : str_word_count($content);
            $maxScore = $questionPoints[$questionId] ?? 0;
            
            // Check if answer already exists
            if (isset($existingAnswers[$questionId])) {
                // Update
                $stmt = $this->db->prepare("
                    UPDATE student_answers
                    SET answer_content = ?,
                        answer_type = ?,
                        answer_length = ?,
                        word_count = ?,
                        status = 'submitted',
                        is_draft = 0,
                        is_final = 1,
                        submitted_at = NOW(),
                        last_save_at = NOW(),
                        time_spent_seconds = ?,
                        answer_hash = MD5(?),
                        max_score = ?
                    WHERE exam_id = ? AND student_id = ? AND attempt_id = ? AND question_id = ?
                ");
                $stmt->execute([
                    $content, $answerType, $answerLength, $wordCount,
                    $timeSpent, $content, $maxScore,
                    $examId, $studentId, $attemptId, $questionId
                ]);
            } else {
                // Insert
                $stmt = $this->db->prepare("
                    INSERT INTO student_answers (
                        exam_id, student_id, attempt_id, question_id,
                        answer_content, answer_type, answer_length, word_count,
                        status, is_draft, is_final, max_score,
                        submitted_at, time_spent_seconds, answer_hash, source
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'submitted', 0, 1, ?, NOW(), ?, ?, 'web')
                ");
                $stmt->execute([
                    $examId, $studentId, $attemptId, $questionId,
                    $content, $answerType, $answerLength, $wordCount,
                    $maxScore, $timeSpent, $content
                ]);
            }
        }
    }
    
    /**
     * Determine answer type based on content
     */
    private function determineAnswerType($content) {
        if (is_array($content)) {
            return 'json';
        }
        if (is_numeric($content)) {
            return 'numeric';
        }
        if (in_array(strtolower($content), ['true', 'false', 'yes', 'no'])) {
            return 'boolean';
        }
        return 'text';
    }
    
    // ===========================================================
    // SUBMISSION RECORDS
    // ===========================================================
    
    /**
     * Create submission record
     */
    public function createSubmissionRecord($attemptId, $examId, $studentId, $answers, $timeSpent, $isAutoSubmitted = false, $isWithinTimeLimit = true) {
        // Get question count
        $questions = $this->getExamQuestions($examId);
        $questionsCount = count($questions);
        
        // Count answers
        $answersCount = count(array_filter($answers, function($v) { return $v !== null && $v !== '' && $v !== []; }));
        $unansweredCount = $questionsCount - $answersCount;
        
        // Get time limit
        $attempt = $this->getAttemptById($attemptId);
        $timeLimitSeconds = intval($attempt['time_limit_minutes'] ?? 0) * 60;
        
        $stmt = $this->db->prepare("
            INSERT INTO exam_submissions (
                attempt_id, exam_id, student_id, submission_data,
                ip_address, user_agent, is_auto_submitted, time_spent_seconds,
                time_limit_seconds, is_within_time_limit, answers_count,
                questions_count, unanswered_count, processed_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
        ");
        
        $stmt->execute([
            $attemptId, $examId, $studentId, json_encode($answers),
            $_SERVER['REMOTE_ADDR'] ?? '',
            $_SERVER['HTTP_USER_AGENT'] ?? '',
            $isAutoSubmitted ? 1 : 0,
            $timeSpent,
            $timeLimitSeconds,
            $isWithinTimeLimit ? 1 : 0,
            $answersCount,
            $questionsCount,
            $unansweredCount
        ]);
        
        return $this->db->lastInsertId();
    }
    
    // ===========================================================
    // SCORING & GRADING
    // ===========================================================
    
    /**
     * Calculate exam score
     */
    public function calculateScore($examId, $answers, $passingScore = 50) {
        // Get exam questions with correct answers
        $questions = $this->getExamQuestions($examId);
        
        $totalScore = 0;
        $maxScore = 0;
        
        foreach ($questions as $question) {
            $maxScore += $question['points'];
            $studentAnswer = $answers[$question['id']] ?? null;
            
            if ($studentAnswer === null || $studentAnswer === '') {
                continue;
            }
            
            $points = 0;
            
            // Auto-grade based on question type
            if (in_array($question['question_type'], ['mcq_single', 'true_false'])) {
                $points = $this->gradeMcqSingle($question, $studentAnswer);
            } elseif ($question['question_type'] === 'mcq_multiple') {
                $points = $this->gradeMcqMultiple($question, $studentAnswer);
            } elseif ($question['question_type'] === 'fill_blanks') {
                $points = $this->gradeFillBlanks($question, $studentAnswer);
            } elseif ($question['question_type'] === 'matching') {
                $points = $this->gradeMatching($question, $studentAnswer);
            }
            // Essay, short_answer, and code require manual grading
            
            $totalScore += $points;
            
            // Update student answer with auto-grade info
            $this->updateAnswerGradeInfo($examId, $question['id'], $points, $question['points'], $points > 0);
        }
        
        $percentage = $maxScore > 0 ? round(($totalScore / $maxScore) * 100, 2) : 0;
        
        return [
            'score' => round($totalScore, 2),
            'max_score' => $maxScore,
            'percentage' => $percentage,
            'is_passed' => $percentage >= $passingScore
        ];
    }
    
    /**
     * Grade single choice MCQ
     */
    private function gradeMcqSingle($question, $studentAnswer) {
        $options = $question['options'] ?? [];
        foreach ($options as $option) {
            if ($option['id'] == $studentAnswer && $option['is_correct']) {
                return $question['points'];
            }
        }
        return 0;
    }
    
    /**
     * Grade multiple choice MCQ
     */
    private function gradeMcqMultiple($question, $studentAnswer) {
        if (!is_array($studentAnswer)) {
            return 0;
        }
        
        $options = $question['options'] ?? [];
        $correctOptions = array_filter($options, fn($opt) => $opt['is_correct']);
        $selectedCorrect = 0;
        
        foreach ($studentAnswer as $selectedId) {
            foreach ($correctOptions as $correctOption) {
                if ($correctOption['id'] == $selectedId) {
                    $selectedCorrect++;
                    break;
                }
            }
        }
        
        $totalCorrect = count($correctOptions);
        return $totalCorrect > 0 ? ($selectedCorrect / $totalCorrect) * $question['points'] : 0;
    }
    
    /**
     * Grade fill in the blanks
     */
    private function gradeFillBlanks($question, $studentAnswer) {
        if (!is_array($studentAnswer)) {
            return 0;
        }
        
        $correctAnswers = array_map('trim', explode(',', $question['explanation'] ?? ''));
        $correctCount = 0;
        
        foreach ($studentAnswer as $index => $answer) {
            $correctIndex = $index - 1;
            if (isset($correctAnswers[$correctIndex]) &&
                strtolower(trim($answer)) === strtolower($correctAnswers[$correctIndex])) {
                $correctCount++;
            }
        }
        
        $totalBlanks = count($correctAnswers);
        return $totalBlanks > 0 ? ($correctCount / $totalBlanks) * $question['points'] : 0;
    }
    
    /**
     * Grade matching questions
     */
    private function gradeMatching($question, $studentAnswer) {
        if (!is_array($studentAnswer)) {
            return 0;
        }
        
        $options = $question['options'] ?? [];
        $correctMatches = 0;
        $totalMatches = 0;
        
        foreach ($options as $option) {
            if ($option['is_correct']) {
                $totalMatches++;
                if (isset($studentAnswer[$option['id']]) &&
                    $studentAnswer[$option['id']] == $option['id']) {
                    $correctMatches++;
                }
            }
        }
        
        return $totalMatches > 0 ? ($correctMatches / $totalMatches) * $question['points'] : 0;
    }
    
    /**
     * Update answer grade information
     */
    private function updateAnswerGradeInfo($examId, $questionId, $points, $maxPoints, $isCorrect) {
        $stmt = $this->db->prepare("
            UPDATE student_answers
            SET is_correct = ?,
                score = ?,
                max_score = ?,
                is_graded = ?,
                grading_method = 'auto',
                grading_status = 'auto_graded',
                graded_at = NOW()
            WHERE exam_id = ? AND question_id = ?
            ORDER BY id DESC LIMIT 1
        ");
        
        $stmt->execute([
            $isCorrect ? 1 : 0,
            $points,
            $maxPoints,
            1,
            $examId,
            $questionId
        ]);
    }
    
    /**
     * Grade individual answer manually
     */
    public function gradeAnswer($answerId, $score, $feedback = '', $gradedBy, $gradingMethod = 'manual') {
        // Get answer details
        $stmt = $this->db->prepare("
            SELECT sa.*, q.points as max_points
            FROM student_answers sa
            JOIN questions q ON sa.question_id = q.id
            WHERE sa.id = ?
        ");
        $stmt->execute([$answerId]);
        $answer = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$answer) {
            throw new Exception('Answer not found');
        }
        
        // Cap score at max points
        $score = min(floatval($score), floatval($answer['max_points']));
        $isCorrect = $score >= $answer['max_points'];
        
        // Update answer
        $stmt = $this->db->prepare("
            UPDATE student_answers
            SET score = ?,
                feedback = ?,
                is_correct = ?,
                is_graded = TRUE,
                graded_by = ?,
                graded_at = NOW(),
                grading_method = ?,
                grading_status = 'manually_graded',
                grade_comments = ?
            WHERE id = ?
        ");
        
        $stmt->execute([
            $score,
            $feedback,
            $isCorrect ? 1 : 0,
            $gradedBy,
            $gradingMethod,
            $feedback,
            $answerId
        ]);
        
        // Save to exam_question_grades
        $stmt = $this->db->prepare("
            INSERT INTO exam_question_grades (
                attempt_id, question_id, points_earned, max_points,
                feedback, is_correct, graded_by, graded_at, grading_method
            ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), ?)
            ON DUPLICATE KEY UPDATE
                points_earned = VALUES(points_earned),
                feedback = VALUES(feedback),
                is_correct = VALUES(is_correct),
                graded_by = VALUES(graded_by),
                graded_at = NOW(),
                grading_method = VALUES(grading_method)
        ");
        
        $stmt->execute([
            $answer['attempt_id'],
            $answer['question_id'],
            $score,
            $answer['max_points'],
            $feedback,
            $isCorrect ? 1 : 0,
            $gradedBy,
            $gradingMethod
        ]);
        
        return ['success' => true, 'score' => $score, 'max_score' => $answer['max_points']];
    }
    
    /**
     * Bulk grade answers for an attempt
     */
    public function bulkGradeAttempt($attemptId, $questionScores, $gradedBy) {
        $attempt = $this->getAttemptById($attemptId);
        if (!$attempt) {
            throw new Exception('Attempt not found');
        }
        
        $this->db->beginTransaction();
        
        try {
            $totalScore = 0;
            $maxScore = 0;
            
            foreach ($questionScores as $questionId => $data) {
                $score = floatval($data['score'] ?? 0);
                $feedback = $data['feedback'] ?? '';
                
                // Get max points for question
                $stmt = $this->db->prepare("
                    SELECT sa.id, q.points as max_points
                    FROM student_answers sa
                    JOIN questions q ON sa.question_id = q.id
                    WHERE sa.attempt_id = ? AND sa.question_id = ?
                ");
                $stmt->execute([$attemptId, $questionId]);
                $answer = $stmt->fetch(PDO::FETCH_ASSOC);
                
                if ($answer) {
                    $maxScore += $answer['max_points'];
                    $totalScore += min($score, $answer['max_points']);
                    
                    $this->gradeAnswer($answer['id'], $score, $feedback, $gradedBy, 'manual');
                }
            }
            
            // Update attempt overall score
            $percentage = $maxScore > 0 ? round(($totalScore / $maxScore) * 100, 2) : 0;
            $passingScore = $attempt['passing_score'] ?? 50;
            $isPassed = $percentage >= $passingScore;
            
            $stmt = $this->db->prepare("
                UPDATE exam_attempts
                SET score = ?,
                    max_score = ?,
                    percentage = ?,
                    is_passed = ?,
                    graded_at = NOW(),
                    graded_by = ?
                WHERE id = ?
            ");
            
            $stmt->execute([
                $totalScore,
                $maxScore,
                $percentage,
                $isPassed ? 1 : 0,
                $gradedBy,
                $attemptId
            ]);
            
            $this->db->commit();
            
            return [
                'success' => true,
                'score' => $totalScore,
                'max_score' => $maxScore,
                'percentage' => $percentage,
                'passed' => $isPassed
            ];
            
        } catch (Exception $e) {
            $this->db->rollBack();
            throw $e;
        }
    }
    
    // ===========================================================
    // RETRIEVAL METHODS
    // ===========================================================
    
    /**
     * Get complete exam submission with all answers and grades
     */
    public function getCompleteSubmission($attemptId) {
        $attempt = $this->getAttemptById($attemptId);
        if (!$attempt) {
            throw new Exception('Attempt not found');
        }
        
        // Get student answers
        $stmt = $this->db->prepare("
            SELECT sa.*, q.content as question_content, q.question_type, q.points as max_points
            FROM student_answers sa
            JOIN questions q ON sa.question_id = q.id
            WHERE sa.attempt_id = ?
            ORDER BY sa.question_id
        ");
        $stmt->execute([$attemptId]);
        $answers = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get question grades
        $stmt = $this->db->prepare("
            SELECT * FROM exam_question_grades
            WHERE attempt_id = ?
        ");
        $stmt->execute([$attemptId]);
        $grades = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Organize grades by question_id
        $gradesByQuestion = [];
        foreach ($grades as $grade) {
            $gradesByQuestion[$grade['question_id']] = $grade;
        }
        
        // Merge grades into answers
        foreach ($answers as &$answer) {
            $qid = $answer['question_id'];
            $answer['grade'] = $gradesByQuestion[$qid] ?? null;
        }
        
        // Get submission record
        $stmt = $this->db->prepare("
            SELECT * FROM exam_submissions WHERE attempt_id = ?
        ");
        $stmt->execute([$attemptId]);
        $submission = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return [
            'attempt' => $attempt,
            'answers' => $answers,
            'submission' => $submission,
            'total_questions' => count($answers),
            'answered_questions' => count(array_filter($answers, fn($a) => !empty($a['answer_content']))),
            'graded_questions' => count(array_filter($answers, fn($a) => $a['is_graded']))
        ];
    }
    
    /**
     * Get attempts by student
     */
    public function getAttemptsByStudent($studentId, $examId = null) {
        $sql = "
            SELECT ea.*, e.title as exam_title, e.passing_score,
                   ea.time_spent_seconds as time_spent
            FROM exam_attempts ea
            JOIN exams e ON ea.exam_id = e.id
            WHERE ea.student_id = ?
        ";
        $params = [$studentId];
        
        if ($examId) {
            $sql .= " AND ea.exam_id = ?";
            $params[] = $examId;
        }
        
        $sql .= " ORDER BY ea.started_at DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get attempts by exam
     */
    public function getAttemptsByExam($examId, $status = null) {
        $sql = "
            SELECT ea.*, u.username, u.email,
                   ea.time_spent_seconds as time_spent
            FROM exam_attempts ea
            JOIN users u ON ea.student_id = u.id
            WHERE ea.exam_id = ?
        ";
        $params = [$examId];
        
        if ($status) {
            $sql .= " AND ea.status = ?";
            $params[] = $status;
        }
        
        $sql .= " ORDER BY ea.started_at DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get answers by attempt
     */
    public function getAnswersByAttempt($attemptId) {
        $stmt = $this->db->prepare("
            SELECT sa.*, q.content as question_content, q.question_type
            FROM student_answers sa
            JOIN questions q ON sa.question_id = q.id
            WHERE sa.attempt_id = ?
            ORDER BY sa.question_id
        ");
        $stmt->execute([$attemptId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get answers by question across attempts
     */
    public function getAnswersByQuestion($questionId, $examId = null) {
        $sql = "
            SELECT sa.*, ea.student_id, ea.attempt_number, u.username
            FROM student_answers sa
            JOIN exam_attempts ea ON sa.attempt_id = ea.id
            JOIN users u ON ea.student_id = u.id
            WHERE sa.question_id = ?
        ";
        $params = [$questionId];
        
        if ($examId) {
            $sql .= " AND sa.exam_id = ?";
            $params[] = $examId;
        }
        
        $sql .= " ORDER BY sa.submitted_at DESC";
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get ungraded answers for an exam
     */
    public function getUngradedAnswers($examId) {
        $stmt = $this->db->prepare("
            SELECT sa.*, q.question_type, q.content as question_content,
                   ea.student_id, ea.attempt_number, u.username
            FROM student_answers sa
            JOIN questions q ON sa.question_id = q.id
            JOIN exam_attempts ea ON sa.attempt_id = ea.id
            JOIN users u ON ea.student_id = u.id
            WHERE sa.exam_id = ? AND sa.is_graded = 0
            AND sa.status IN ('submitted', 'pending_review')
            AND q.question_type IN ('essay', 'short_answer', 'code')
            ORDER BY sa.submitted_at ASC
        ");
        $stmt->execute([$examId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get pending manual grading count
     */
    public function getPendingGradingCount($examId = null, $instructorId = null) {
        $sql = "
            SELECT COUNT(*) as count
            FROM student_answers sa
            JOIN exam_attempts ea ON sa.attempt_id = ea.id
            JOIN exams e ON ea.exam_id = e.id
            WHERE sa.is_graded = 0
            AND sa.status IN ('submitted', 'pending_review')
            AND ea.status = 'completed'
        ";
        $params = [];
        
        if ($examId) {
            $sql .= " AND sa.exam_id = ?";
            $params[] = $examId;
        }
        
        if ($instructorId) {
            $sql .= " AND e.instructor_id = ?";
            $params[] = $instructorId;
        }
        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return intval($result['count']);
    }
    
    // ===========================================================
    // HELPER METHODS
    // ===========================================================
    
    /**
     * Get exam details
     */
    private function getExamDetails($examId) {
        $stmt = $this->db->prepare("
            SELECT * FROM exams WHERE id = ?
        ");
        $stmt->execute([$examId]);
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
    
    /**
     * Get exam questions with options
     */
    private function getExamQuestions($examId) {
        $stmt = $this->db->prepare("
            SELECT q.*, eq.points, eq.order_index
            FROM exam_questions eq
            JOIN questions q ON eq.question_id = q.id
            WHERE eq.exam_id = ?
            ORDER BY eq.order_index
        ");
        $stmt->execute([$examId]);
        $questions = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Get options for each question
        foreach ($questions as &$question) {
            if (in_array($question['question_type'], ['mcq_single', 'mcq_multiple', 'matching'])) {
                $stmt = $this->db->prepare("
                    SELECT * FROM question_options WHERE question_id = ?
                ");
                $stmt->execute([$question['id']]);
                $question['options'] = $stmt->fetchAll(PDO::FETCH_ASSOC);
            }
        }
        
        return $questions;
    }
}
?>
