<?php

class ExamGradingService
{
    private const AUTO_GRADED_TYPES = ['mcq_single', 'mcq_multiple', 'true_false', 'matching', 'fill_blanks'];
    private const MANUAL_TYPES = ['essay', 'short_answer', 'code'];

    public static function hasColumn(PDO $db, string $table, string $column): bool
    {
        $stmt = $db->prepare("SHOW COLUMNS FROM `$table` LIKE ?");
        $stmt->execute([$column]);
        return (bool) $stmt->fetch();
    }

    public static function boolValue($value): int
    {
        return $value ? 1 : 0;
    }

    public static function normalizeAnswer($answer)
    {
        if (is_string($answer)) {
            $trimmed = trim($answer);
            if ($trimmed === '') {
                return null;
            }
            $decoded = json_decode($trimmed, true);
            if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
                return $decoded;
            }
            return $trimmed;
        }

        if (is_array($answer)) {
            return array_map(static function ($value) {
                return is_string($value) ? trim($value) : $value;
            }, $answer);
        }

        return $answer;
    }

    public static function answerIsEmpty($answer): bool
    {
        $answer = self::normalizeAnswer($answer);
        if ($answer === null || $answer === '') {
            return true;
        }
        if (is_array($answer)) {
            return empty(array_filter($answer, static fn($value) => $value !== null && $value !== ''));
        }
        return false;
    }

    public static function encodeAnswer($answer): array
    {
        if ($answer === null || $answer === '') {
            return ['content' => '', 'type' => 'text'];
        }

        if (is_array($answer)) {
            return [
                'content' => json_encode($answer, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                'type' => 'json'
            ];
        }

        if (is_numeric($answer)) {
            return ['content' => (string) $answer, 'type' => 'numeric'];
        }

        return ['content' => (string) $answer, 'type' => 'text'];
    }

    public static function getExamQuestions(PDO $db, int $examId): array
    {
        $stmt = $db->prepare("
            SELECT eq.id as exam_question_id, eq.question_id, eq.sort_order, eq.points, eq.time_limit, eq.section_name,
                   q.id, q.title, q.content, q.question_type, q.difficulty, q.points as default_points,
                   q.time_limit as question_time_limit, q.category_id, q.course_id, q.instructor_id,
                   q.tags, q.explanation, q.hints, q.metadata, q.correct_answer, q.status
            FROM exam_questions eq
            JOIN questions q ON q.id = eq.question_id
            WHERE eq.exam_id = ?
            ORDER BY eq.sort_order, eq.question_id
        ");
        $stmt->execute([$examId]);
        $questions = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($questions as &$question) {
            $question['points'] = floatval($question['points']);
            $question['default_points'] = floatval($question['default_points']);
            $question['tags'] = self::decodeJson($question['tags']);
            $question['hints'] = self::decodeJson($question['hints']);
            $question['metadata'] = self::decodeJson($question['metadata']);
            $question['options'] = [];
        }
        unset($question);

        if (!$questions) {
            return [];
        }

        $ids = array_column($questions, 'question_id');
        $placeholders = implode(',', array_fill(0, count($ids), '?'));
        $stmt = $db->prepare("
            SELECT qo.*, q.question_type
            FROM question_options qo
            JOIN questions q ON q.id = qo.question_id
            WHERE qo.question_id IN ($placeholders)
            ORDER BY qo.question_id, qo.sort_order, qo.id
        ");
        $stmt->execute($ids);
        $options = $stmt->fetchAll(PDO::FETCH_ASSOC);

        foreach ($options as $option) {
            foreach ($questions as &$question) {
                if ((int) $question['question_id'] === (int) $option['question_id']) {
                    $option['is_correct'] = (bool) $option['is_correct'];
                    $question['options'][] = $option;
                    break;
                }
            }
            unset($question);
        }

        return $questions;
    }

    public static function getAttempt(PDO $db, int $attemptId): ?array
    {
        $timeColumn = self::hasColumn($db, 'exam_attempts', 'time_spent_seconds') ? 'ea.time_spent_seconds' : 'ea.time_spent';
        $stmt = $db->prepare("
            SELECT ea.*, e.title as exam_title, e.description as exam_description, e.instructions,
                   e.passing_score, e.total_points, e.time_limit, e.max_attempts, e.show_results,
                   e.show_answers, e.allow_review, e.shuffle_questions, e.shuffle_options,
                   c.id as course_id, c.title as course_title,
                   $timeColumn as time_spent,
                   u.username as student_username,
                   u.email as student_email,
                   up.first_name,
                   up.last_name,
                   up.avatar
            FROM exam_attempts ea
            JOIN exams e ON e.id = ea.exam_id
            LEFT JOIN courses c ON c.id = e.course_id
            JOIN users u ON u.id = ea.student_id
            LEFT JOIN user_profiles up ON up.user_id = u.id
            WHERE ea.id = ?
        ");
        $stmt->execute([$attemptId]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    public static function getAttemptAnswers(PDO $db, int $attemptId): array
    {
        $attempt = self::getAttempt($db, $attemptId);
        if (!$attempt) {
            return [];
        }

        $answers = self::decodeJson($attempt['answers'] ?? '{}');
        if (!is_array($answers)) {
            $answers = [];
        }

        $stmt = $db->prepare("
            SELECT question_id, answer_content, answer_type
            FROM student_answers
            WHERE attempt_id = ?
            ORDER BY question_id
        ");
        $stmt->execute([$attemptId]);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $content = $row['answer_content'] ?? '';
            if ($row['answer_type'] === 'json' && $content !== '') {
                $decoded = json_decode($content, true);
                $answers[$row['question_id']] = is_array($decoded) ? $decoded : $content;
            } else {
                $answers[$row['question_id']] = $content;
            }
        }

        return $answers;
    }

    public static function gradeExam(PDO $db, int $examId, array $answers, float $passingScore): array
    {
        $questions = self::getExamQuestions($db, $examId);
        $scoreData = self::gradeQuestions($questions, $answers);
        $scoreData['is_passed'] = $scoreData['max_score'] > 0 && ($scoreData['percentage'] >= $passingScore);
        return $scoreData;
    }

    public static function gradeQuestions(array $questions, array $answers): array
    {
        $totalScore = 0.0;
        $maxScore = 0.0;
        $questionScores = [];
        $needsManualReview = false;

        foreach ($questions as $question) {
            $questionId = (int) $question['question_id'];
            $points = floatval($question['points']);
            $maxScore += $points;
            $answer = self::normalizeAnswer($answers[$questionId] ?? null);

            if (self::answerIsEmpty($answer)) {
                $questionScores[$questionId] = [
                    'question_id' => $questionId,
                    'points' => 0.0,
                    'max_points' => $points,
                    'is_correct' => false,
                    'grading_method' => 'auto',
                    'needs_manual_review' => false
                ];
                continue;
            }

            if (in_array($question['question_type'], self::MANUAL_TYPES, true)) {
                $needsManualReview = true;
                $questionScores[$questionId] = [
                    'question_id' => $questionId,
                    'points' => 0.0,
                    'max_points' => $points,
                    'is_correct' => null,
                    'grading_method' => 'manual',
                    'needs_manual_review' => true
                ];
                continue;
            }

            $score = self::gradeObjectiveQuestion($question, $answer);
            $questionScores[$questionId] = [
                'question_id' => $questionId,
                'points' => round($score, 2),
                'max_points' => $points,
                'is_correct' => $score >= $points,
                'grading_method' => 'auto',
                'needs_manual_review' => false
            ];
            $totalScore += $score;
        }

        $percentage = $maxScore > 0 ? round(($totalScore / $maxScore) * 100, 2) : 0.0;

        return [
            'score' => round($totalScore, 2),
            'max_score' => round($maxScore, 2),
            'percentage' => $percentage,
            'is_passed' => false,
            'needs_manual_review' => $needsManualReview,
            'question_scores' => $questionScores
        ];
    }

    public static function gradeObjectiveQuestion(array $question, $studentAnswer): float
    {
        $studentAnswer = self::normalizeAnswer($studentAnswer);
        $points = floatval($question['points']);
        $options = $question['options'] ?? [];

        if (self::answerIsEmpty($studentAnswer)) {
            return 0.0;
        }

        return match ($question['question_type']) {
            'mcq_single', 'true_false' => self::gradeSingleChoice($options, $studentAnswer, $points),
            'mcq_multiple' => self::gradeMultipleChoice($options, $studentAnswer, $points),
            'matching' => self::gradeMatching($options, $studentAnswer, $points),
            'fill_blanks' => self::gradeFillBlanks($question, $studentAnswer, $points),
            default => 0.0
        };
    }

    private static function gradeSingleChoice(array $options, $studentAnswer, float $points): float
    {
        foreach ($options as $option) {
            if ((string) $option['id'] === (string) $studentAnswer && (bool) $option['is_correct']) {
                return $points;
            }
        }
        return 0.0;
    }

    private static function gradeMultipleChoice(array $options, array $studentAnswer, float $points): float
    {
        $correctOptions = array_values(array_filter($options, static fn($option) => (bool) $option['is_correct']));
        if (empty($correctOptions)) {
            return empty($studentAnswer) ? $points : 0.0;
        }

        $selectedCorrect = 0;
        foreach ($studentAnswer as $selectedId) {
            foreach ($correctOptions as $correctOption) {
                if ((string) $selectedId === (string) $correctOption['id']) {
                    $selectedCorrect++;
                    break;
                }
            }
        }

        return round(($selectedCorrect / count($correctOptions)) * $points, 2);
    }

    private static function gradeMatching(array $options, array $studentAnswer, float $points): float
    {
        $correctMatches = 0;
        $totalMatches = 0;

        foreach ($options as $option) {
            $leftId = (string) $option['id'];
            if (!isset($studentAnswer[$leftId]) || $studentAnswer[$leftId] === '') {
                continue;
            }

            $totalMatches++;
            $correctValue = $option['match_value'] ?? $option['option_value'] ?? null;
            $studentValue = (string) $studentAnswer[$leftId];

            if ($correctValue !== null && ((string) $correctValue === $studentValue || self::optionIdMatchesValue($options, $correctValue) === $studentValue)) {
                $correctMatches++;
            }
        }

        return $totalMatches > 0 ? round(($correctMatches / $totalMatches) * $points, 2) : 0.0;
    }

    private static function optionIdMatchesValue(array $options, string $value): ?string
    {
        foreach ($options as $option) {
            if ((string) ($option['option_value'] ?? '') === $value || (string) ($option['match_value'] ?? '') === $value) {
                return (string) $option['id'];
            }
        }
        return null;
    }

    private static function gradeFillBlanks(array $question, array $studentAnswer, float $points): float
    {
        $explanation = trim((string) ($question['explanation'] ?? ''));
        $metadata = is_array($question['metadata']) ? $question['metadata'] : [];
        $correctAnswers = [];

        if (!empty($metadata['accepted_answers']) && is_array($metadata['accepted_answers'])) {
            $correctAnswers = $metadata['accepted_answers'];
        } elseif ($explanation !== '') {
            $correctAnswers = array_map('trim', preg_split('/\s*,\s*/', $explanation));
        }

        if (empty($correctAnswers)) {
            return 0.0;
        }

        $correctCount = 0;
        foreach ($studentAnswer as $answer) {
            $answer = strtolower(trim((string) $answer));
            foreach ($correctAnswers as $correctAnswer) {
                if (is_array($correctAnswer)) {
                    foreach ($correctAnswer as $accepted) {
                        if ($answer === strtolower(trim((string) $accepted))) {
                            $correctCount++;
                            break 2;
                        }
                    }
                } elseif ($answer === strtolower(trim((string) $correctAnswer))) {
                    $correctCount++;
                    break;
                }
            }
        }

        return round(($correctCount / count($correctAnswers)) * $points, 2);
    }

    public static function saveStudentAnswers(PDO $db, int $examId, int $studentId, int $attemptId, array $answers, array $scoreData, int $timeSpent): void
    {
        $questions = self::getExamQuestions($db, $examId);
        $questionScores = $scoreData['question_scores'] ?? [];
        $ipAddress = $_SERVER['REMOTE_ADDR'] ?? '';
        $browserInfo = json_encode([
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
            'accept_language' => $_SERVER['HTTP_ACCEPT_LANGUAGE'] ?? ''
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $deviceInfo = json_encode([
            'platform' => $_SERVER['HTTP_SEC_CH_UA_PLATFORM'] ?? '',
            'mobile' => $_SERVER['HTTP_SEC_CH_UA_MOBILE'] ?? ''
        ], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
        $sessionId = session_id();
        $clientTimezone = $_SERVER['HTTP_X_TIMEZONE'] ?? 'UTC';

        $db->beginTransaction();
        try {
            foreach ($questions as $question) {
                $questionId = (int) $question['question_id'];
                $answer = self::normalizeAnswer($answers[$questionId] ?? null);
                $encoded = self::encodeAnswer($answer);
                $answerContent = $encoded['content'];
                $answerType = $encoded['type'];
                $scoreInfo = $questionScores[$questionId] ?? [
                    'points' => 0.0,
                    'max_points' => floatval($question['points']),
                    'is_correct' => false,
                    'grading_method' => 'auto',
                    'needs_manual_review' => false
                ];

                $isManual = in_array($question['question_type'], self::MANUAL_TYPES, true);
                $isCorrect = $scoreInfo['is_correct'];
                $score = $isManual ? 0.0 : floatval($scoreInfo['points'] ?? 0.0);
                $isGraded = !$isManual;
                $status = $isManual ? 'pending_manual_review' : 'submitted';
                $gradingStatus = $isManual ? 'pending' : 'auto_graded';
                $gradingMethod = $isManual ? 'manual' : 'auto';

                $answerLength = mb_strlen($answerContent, 'UTF-8');
                $wordCount = $answerContent ? preg_match_all('/\S+/u', $answerContent) : 0;
                $answerHash = md5($answerContent);

                $stmt = $db->prepare("
                    INSERT INTO student_answers (
                        exam_id, attempt_id, student_id, question_id,
                        answer_content, answer_type, answer_hash, answer_length,
                        word_count, char_count, is_correct, score, max_score,
                        feedback, is_graded, graded_by, graded_at, grading_method,
                        grading_status, status, is_draft, is_final, time_spent_seconds,
                        ip_address, browser_info, device_info, source, session_id,
                        client_timezone, first_save_at, last_save_at, save_count,
                        submitted_at, started_at, created_at, updated_at
                    ) VALUES (
                        ?, ?, ?, ?, ?, ?, ?, ?,
                        ?, ?, ?, ?, ?, NULL,
                        ?, NULL, NULL, ?,
                        ?, ?, 0, 1, ?,
                        ?, ?, ?, ?, ?,
                        ?, ?, NOW(), NOW(), 1,
                        NOW(), NOW(), NOW(), NOW()
                    )
                    ON DUPLICATE KEY UPDATE
                        answer_content = VALUES(answer_content),
                        answer_type = VALUES(answer_type),
                        answer_hash = VALUES(answer_hash),
                        answer_length = VALUES(answer_length),
                        word_count = VALUES(word_count),
                        char_count = VALUES(char_count),
                        is_correct = VALUES(is_correct),
                        score = VALUES(score),
                        max_score = VALUES(max_score),
                        is_graded = VALUES(is_graded),
                        grading_method = VALUES(grading_method),
                        grading_status = VALUES(grading_status),
                        status = VALUES(status),
                        is_draft = VALUES(is_draft),
                        is_final = VALUES(is_final),
                        time_spent_seconds = VALUES(time_spent_seconds),
                        ip_address = VALUES(ip_address),
                        browser_info = VALUES(browser_info),
                        device_info = VALUES(device_info),
                        source = VALUES(source),
                        session_id = VALUES(session_id),
                        client_timezone = VALUES(client_timezone),
                        last_save_at = NOW(),
                        save_count = save_count + 1,
                        submitted_at = VALUES(submitted_at),
                        updated_at = NOW()
                ");

                $stmt->execute([
                    $examId, $attemptId, $studentId, $questionId,
                    $answerContent, $answerType, $answerHash, $answerLength,
                    $wordCount, $answerLength, $isCorrect === null ? null : self::boolValue($isCorrect), $score, floatval($question['points']),
                    self::boolValue($isGraded), self::boolValue($isFinal = !$isManual), $timeSpent,
                    $ipAddress, $browserInfo, $deviceInfo, 'web', $sessionId,
                    $clientTimezone
                ]);
            }

            $db->commit();
        } catch (Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public static function getSubmission(PDO $db, int $attemptId, int $viewerId, string $role): array
    {
        $attempt = self::getAttempt($db, $attemptId);
        if (!$attempt) {
            throw new Exception('Submission not found');
        }

        $stmt = $db->prepare("SELECT instructor_id FROM exams WHERE id = ?");
        $stmt->execute([$attempt['exam_id']]);
        $instructorId = (int) $stmt->fetchColumn();

        if ($role !== 'admin' && !($role === 'instructor' && $instructorId === $viewerId) && !($role === 'student' && (int) $attempt['student_id'] === $viewerId)) {
            throw new Exception('Access denied');
        }

        $stmt = $db->prepare("
            SELECT id, username, email
            FROM users
            WHERE id = ?
        ");
        $stmt->execute([(int) $attempt['student_id']]);
        $student = $stmt->fetch(PDO::FETCH_ASSOC) ?: ['id' => $attempt['student_id'], 'username' => '', 'email' => ''];

        $answers = self::getAttemptAnswers($db, $attemptId);
        $questions = self::getExamQuestions($db, (int) $attempt['exam_id']);

        $stmt = $db->prepare("
            SELECT *
            FROM exam_question_grades
            WHERE attempt_id = ?
        ");
        $stmt->execute([$attemptId]);
        $grades = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $grade) {
            $grades[(int) $grade['question_id']] = $grade;
        }

        $stmt = $db->prepare("
            SELECT *
            FROM student_answers
            WHERE attempt_id = ?
            ORDER BY question_id
        ");
        $stmt->execute([$attemptId]);
        $answerRows = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $answerRows[(int) $row['question_id']] = $row;
        }

        foreach ($questions as &$question) {
            $questionId = (int) $question['question_id'];
            $question['student_answer'] = self::normalizeAnswer($answers[$questionId] ?? null);
            $question['grade'] = $grades[$questionId] ?? null;
            $question['answer_record'] = $answerRows[$questionId] ?? null;
            $question['earned_points'] = isset($question['grade']) ? floatval($question['grade']['points_earned']) : 0.0;
            $question['feedback'] = $question['grade']['feedback'] ?? ($question['answer_record']['feedback'] ?? '');
        }
        unset($question);

        return [
            'attempt' => $attempt,
            'student' => $student,
            'questions' => $questions,
            'answers' => $answers
        ];
    }

    public static function getGradingQueue(PDO $db, int $viewerId, string $role, ?int $examId = null, string $status = 'pending'): array
    {
        $params = [];
        $where = [];

        if ($role === 'instructor') {
            $where[] = 'e.instructor_id = ?';
            $params[] = $viewerId;
        }

        if ($examId) {
            $where[] = 'e.id = ?';
            $params[] = $examId;
        }

        if ($status === 'pending') {
            $where[] = "(ea.status IN ('submitted','pending_manual_review','completed','timed_out') AND (ea.graded_at IS NULL OR EXISTS (
                SELECT 1 FROM exam_questions eq2
                JOIN questions q2 ON q2.id = eq2.question_id
                WHERE eq2.exam_id = e.id AND q2.question_type IN ('essay','short_answer','code')
                AND NOT EXISTS (
                    SELECT 1 FROM exam_question_grades eqg
                    WHERE eqg.attempt_id = ea.id AND eqg.question_id = eq2.question_id
                )
            )))";
        } elseif ($status === 'graded') {
            $where[] = 'ea.graded_at IS NOT NULL';
        }

        $whereSql = $where ? 'WHERE ' . implode(' AND ', $where) : '';
        $stmt = $db->prepare("
            SELECT
                ea.id as attempt_id,
                ea.exam_id,
                ea.student_id,
                ea.status,
                ea.completed_at,
                ea.started_at,
                ea.time_spent_seconds,
                ea.score,
                ea.max_score,
                ea.percentage,
                ea.is_passed,
                ea.graded_at,
                ea.graded_by,
                e.title as exam_title,
                u.username as student_name,
                u.email as student_email,
                up.first_name,
                up.last_name,
                COUNT(DISTINCT eq.question_id) as total_questions,
                SUM(CASE WHEN q.question_type IN ('essay','short_answer','code') THEN 1 ELSE 0 END) as manual_questions
            FROM exam_attempts ea
            JOIN exams e ON e.id = ea.exam_id
            JOIN users u ON u.id = ea.student_id
            LEFT JOIN user_profiles up ON up.user_id = u.id
            LEFT JOIN exam_questions eq ON eq.exam_id = e.id
            LEFT JOIN questions q ON q.id = eq.question_id
            $whereSql
            GROUP BY ea.id
            ORDER BY COALESCE(ea.completed_at, ea.started_at) DESC
        ");
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public static function saveManualGrades(PDO $db, int $attemptId, array $questionScores, array $questionFeedback, int $gradedBy, string $overallFeedback = ''): array
    {
        $attempt = self::getAttempt($db, $attemptId);
        if (!$attempt) {
            throw new Exception('Attempt not found');
        }

        $questions = self::getExamQuestions($db, (int) $attempt['exam_id']);
        $answers = self::getAttemptAnswers($db, $attemptId);
        $totalScore = 0.0;
        $maxScore = 0.0;
        $hadManualGrade = false;

        $db->beginTransaction();
        try {
            foreach ($questions as $question) {
                $questionId = (int) $question['question_id'];
                $maxPoints = floatval($question['points']);
                $maxScore += $maxPoints;

                if (isset($questionScores[(string) $questionId]) || isset($questionScores[$questionId])) {
                    $earned = min(floatval($questionScores[(string) $questionId] ?? $questionScores[$questionId] ?? 0), $maxPoints);
                    $hadManualGrade = true;
                } elseif (in_array($question['question_type'], self::AUTO_GRADED_TYPES, true)) {
                    $earned = self::gradeObjectiveQuestion($question, $answers[$questionId] ?? null);
                } else {
                    $earned = 0.0;
                }

                $isCorrect = $earned >= $maxPoints;
                $feedback = $questionFeedback[(string) $questionId] ?? ($questionFeedback[$questionId] ?? '');
                $gradingMethod = in_array($question['question_type'], self::MANUAL_TYPES, true) && isset($questionScores[(string) $questionId]) ? 'manual' : 'auto';

                $stmt = $db->prepare("
                    INSERT INTO exam_question_grades (
                        attempt_id, question_id, points_earned, max_points,
                        feedback, is_correct, graded_by, graded_at,
                        grading_method, grading_status, grade_comments, partial_credit,
                        created_at, updated_at
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?, ?, ?, NOW(), NOW())
                    ON DUPLICATE KEY UPDATE
                        points_earned = VALUES(points_earned),
                        max_points = VALUES(max_points),
                        feedback = VALUES(feedback),
                        is_correct = VALUES(is_correct),
                        graded_by = VALUES(graded_by),
                        graded_at = NOW(),
                        grading_method = VALUES(grading_method),
                        grading_status = VALUES(grading_status),
                        grade_comments = VALUES(grade_comments),
                        partial_credit = VALUES(partial_credit),
                        updated_at = NOW()
                ");
                $stmt->execute([
                    $attemptId, $questionId, $earned, $maxPoints,
                    $feedback, self::boolValue($isCorrect), $gradedBy,
                    $gradingMethod, $gradingMethod === 'manual' ? 'manually_graded' : 'auto_graded', $feedback, 0.0
                ]);

                $stmt = $db->prepare("
                    UPDATE student_answers
                    SET score = ?,
                        max_score = ?,
                        is_correct = ?,
                        feedback = ?,
                        is_graded = 1,
                        graded_by = ?,
                        graded_at = NOW(),
                        grading_method = ?,
                        grading_status = ?,
                        grade_comments = ?,
                        partial_credit = ?,
                        status = 'graded',
                        is_draft = 0,
                        is_final = 1,
                        updated_at = NOW()
                    WHERE attempt_id = ? AND question_id = ?
                ");
                $stmt->execute([
                    $earned, $maxPoints, self::boolValue($isCorrect), $feedback,
                    $gradedBy, $gradingMethod, $gradingMethod === 'manual' ? 'manually_graded' : 'auto_graded',
                    $feedback, 0.0, $attemptId, $questionId
                ]);

                $totalScore += $earned;
            }

            $percentage = $maxScore > 0 ? round(($totalScore / $maxScore) * 100, 2) : 0.0;
            $isPassed = $percentage >= floatval($attempt['passing_score']);

            $stmt = $db->prepare("
                UPDATE exam_attempts
                SET score = ?,
                    max_score = ?,
                    percentage = ?,
                    is_passed = ?,
                    feedback = ?,
                    status = 'graded',
                    graded_by = ?,
                    graded_at = NOW(),
                    updated_at = NOW()
                WHERE id = ?
            ");
            $stmt->execute([
                round($totalScore, 2), round($maxScore, 2), $percentage, self::boolValue($isPassed),
                $overallFeedback, $gradedBy, $attemptId
            ]);

            $db->commit();

            return [
                'score' => round($totalScore, 2),
                'max_score' => round($maxScore, 2),
                'percentage' => $percentage,
                'passed' => $isPassed,
                'had_manual_grade' => $hadManualGrade
            ];
        } catch (Throwable $e) {
            $db->rollBack();
            throw $e;
        }
    }

    public static function getGradingStats(PDO $db, int $viewerId, string $role): array
    {
        $params = [];
        $where = $role === 'instructor' ? 'WHERE e.instructor_id = ?' : '';
        if ($role === 'instructor') {
            $params[] = $viewerId;
        }

        $stmt = $db->prepare("
            SELECT
                COUNT(DISTINCT CASE WHEN ea.status IN ('submitted','pending_manual_review','completed','timed_out') AND ea.graded_at IS NULL THEN ea.id END) as pending_count,
                COUNT(DISTINCT CASE WHEN ea.graded_at IS NOT NULL THEN ea.id END) as graded_count,
                COUNT(DISTINCT CASE WHEN ea.status = 'in_progress' THEN ea.id END) as in_progress_count
            FROM exam_attempts ea
            JOIN exams e ON e.id = ea.exam_id
            $where
        ");
        $stmt->execute($params);
        $stats = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

        return [
            'pending' => (int) ($stats['pending_count'] ?? 0),
            'graded' => (int) ($stats['graded_count'] ?? 0),
            'in_progress' => (int) ($stats['in_progress_count'] ?? 0)
        ];
    }

    public static function decodeJson($value)
    {
        if ($value === null || $value === '') {
            return [];
        }
        if (is_array($value)) {
            return $value;
        }
        $decoded = json_decode($value, true);
        return json_last_error() === JSON_ERROR_NONE ? $decoded : [];
    }
}
