<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * ExamResult Model
 *
 * Handles student exam attempt results, including score calculation,
 * student-specific and exam-specific result lookups, attempt counting,
 * and multi-attempt support (max 3 attempts per exam).
 */
class ExamResult extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'exam_results';

    /**
     * Get all exam results for a specific student.
     *
     * @param int $studentId The student ID
     * @return array<int, array<string, mixed>> Array of exam result records
     */
    public function getByStudent(int $studentId): array
    {
        $sql = sprintf(
            'SELECT er.*, e.title AS exam_title, e.total_marks AS exam_total_marks,
                    c.name AS course_name
             FROM `%s` er
             LEFT JOIN `exams` e ON er.exam_id = e.id
             LEFT JOIN `courses` c ON e.course_id = c.id
             WHERE er.student_id = :student_id
             ORDER BY er.created_at DESC',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':student_id' => $studentId]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Get all exam results for a specific exam.
     *
     * @param int $examId The exam ID
     * @return array<int, array<string, mixed>> Array of exam result records
     */
    public function getByExam(int $examId): array
    {
        $sql = sprintf(
            'SELECT er.*, u.name AS student_name, s.enrollment_no
             FROM `%s` er
             LEFT JOIN `students` s ON er.student_id = s.id
             LEFT JOIN `users` u ON s.user_id = u.id
             WHERE er.exam_id = :exam_id
             ORDER BY er.percentage DESC',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':exam_id' => $examId]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Count the number of attempts a student has made for a specific exam.
     *
     * @param int $examId The exam ID
     * @param int $studentId The student ID
     * @return int Number of attempts
     */
    public function countAttempts(int $examId, int $studentId): int
    {
        $sql = sprintf(
            'SELECT COUNT(*) AS cnt FROM `%s` WHERE `exam_id` = :exam_id AND `student_id` = :student_id',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':exam_id' => $examId, ':student_id' => $studentId]);

        return (int) ($stmt->fetch()['cnt'] ?? 0);
    }

    /**
     * Get the latest attempt for a student on a specific exam.
     *
     * @param int $examId The exam ID
     * @param int $studentId The student ID
     * @return array<string, mixed>|null The latest attempt result or null
     */
    public function getLatestAttempt(int $examId, int $studentId): ?array
    {
        $sql = sprintf(
            'SELECT er.*, e.title AS exam_title, e.total_marks AS exam_total_marks,
                    e.pass_marks AS exam_pass_marks, e.duration_minutes, c.name AS course_name
             FROM `%s` er
             LEFT JOIN `exams` e ON er.exam_id = e.id
             LEFT JOIN `courses` c ON e.course_id = c.id
             WHERE er.exam_id = :exam_id AND er.student_id = :student_id
             ORDER BY er.created_at DESC
             LIMIT 1',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':exam_id' => $examId, ':student_id' => $studentId]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result !== false ? $result : null;
    }

    /**
     * Get all attempts for a student on a specific exam, ordered by attempt number.
     *
     * @param int $examId The exam ID
     * @param int $studentId The student ID
     * @return array<int, array<string, mixed>> Array of attempt records
     */
    public function getAttemptsByExamAndStudent(int $examId, int $studentId): array
    {
        $sql = sprintf(
            'SELECT er.*, e.title AS exam_title
             FROM `%s` er
             LEFT JOIN `exams` e ON er.exam_id = e.id
             WHERE er.exam_id = :exam_id AND er.student_id = :student_id
             ORDER BY er.attempt_number ASC',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':exam_id' => $examId, ':student_id' => $studentId]);

        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }

    /**
     * Find a specific exam result by its ID with exam details.
     *
     * @param int $id The result ID
     * @return array<string, mixed>|null The result record or null
     */
    public function findWithExam(int $id): ?array
    {
        $sql = sprintf(
            'SELECT er.*, e.title AS exam_title, e.total_marks AS exam_total_marks,
                    e.pass_marks AS exam_pass_marks, e.duration_minutes, c.name AS course_name
             FROM `%s` er
             LEFT JOIN `exams` e ON er.exam_id = e.id
             LEFT JOIN `courses` c ON e.course_id = c.id
             WHERE er.id = :id
             LIMIT 1',
            $this->table
        );

        $stmt = $this->db->prepare($sql);
        $stmt->execute([':id' => $id]);

        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result !== false ? $result : null;
    }

    /**
     * Calculate the exam score from submitted answers.
     *
     * Compares each submitted answer against the correct answer from
     * the exam_questions table and calculates total obtained marks,
     * percentage, and pass/fail status.
     *
     * @param int $examId The exam ID
     * @param array<string, string> $answers Associative array of question_id => selected_answer ('a','b','c','d')
     * @return array{obtained: int, total: int, percentage: float, status: string, details: array<int, array{question_id: int, selected: string, correct: string, is_correct: bool, marks: int}>}
     */
    public function calculateScore(int $examId, array $answers): array
    {
        // Fetch all questions for this exam
        $questionSql = 'SELECT `id`, `correct_answer`, `marks` FROM `exam_questions`
                        WHERE `exam_id` = :exam_id ORDER BY `sort_order` ASC';

        $qStmt = $this->db->prepare($questionSql);
        $qStmt->execute([':exam_id' => $examId]);
        $questions = $qStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        // Fetch exam pass marks
        $examSql = 'SELECT `total_marks`, `pass_marks` FROM `exams` WHERE `id` = :id LIMIT 1';
        $eStmt = $this->db->prepare($examSql);
        $eStmt->execute([':id' => $examId]);
        $exam = $eStmt->fetch(PDO::FETCH_ASSOC);

        $totalMarks = (int) ($exam['total_marks'] ?? 0);
        $passMarks = (int) ($exam['pass_marks'] ?? 0);
        $obtained = 0;
        $details = [];

        foreach ($questions as $question) {
            $questionId = (int) $question['id'];
            $correctAnswer = $question['correct_answer'];
            $marks = (int) $question['marks'];
            $selected = $answers[$questionId] ?? '';

            $isCorrect = ($selected === $correctAnswer);
            $earnedMarks = $isCorrect ? $marks : 0;
            $obtained += $earnedMarks;

            $details[] = [
                'question_id' => $questionId,
                'selected'    => $selected,
                'correct'     => $correctAnswer,
                'is_correct'  => $isCorrect,
                'marks'       => $marks,
            ];
        }

        $percentage = $totalMarks > 0 ? round(($obtained / $totalMarks) * 100, 2) : 0.00;
        $status = $obtained >= $passMarks ? 'pass' : 'fail';

        return [
            'obtained'   => $obtained,
            'total'      => $totalMarks,
            'percentage' => $percentage,
            'status'     => $status,
            'details'    => $details,
        ];
    }
}
