<?php

declare(strict_types=1);

namespace App\Models;

use App\Core\Model;
use PDO;

/**
 * Exam Model
 *
 * Handles examination definitions with course relationships,
 * question retrieval, and result lookups.
 */
class Exam extends Model
{
    /**
     * The database table associated with the model.
     */
    protected string $table = 'exams';

    /**
     * Get all published exams with their associated course name.
     *
     * @return array<int, array<string, mixed>> Array of published exams with course info
     */
    public function getPublished(): array
    {
        $sql = sprintf(
            'SELECT e.*, c.name AS course_name, c.category AS course_category
             FROM `%s` e
             LEFT JOIN `courses` c ON e.course_id = c.id
             WHERE e.is_published = :published
             ORDER BY e.created_at DESC',
            $this->table
        );

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

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

    /**
     * Get an exam with all its questions.
     *
     * Returns the exam details along with an array of questions
     * ordered by sort_order.
     *
     * @param int $id The exam ID
     * @return array<string, mixed> Associative array with exam data and 'questions' key
     */
    public function getWithQuestions(int $id): array
    {
        // Fetch the exam with course info
        $sql = sprintf(
            'SELECT e.*, c.name AS course_name
             FROM `%s` e
             LEFT JOIN `courses` c ON e.course_id = c.id
             WHERE e.id = :id
             LIMIT 1',
            $this->table
        );

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

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

        if ($exam === false) {
            return [];
        }

        // Fetch the questions for this exam
        $questionSql = 'SELECT * FROM `exam_questions`
                        WHERE `exam_id` = :exam_id
                        ORDER BY `sort_order` ASC';

        $qStmt = $this->db->prepare($questionSql);
        $qStmt->execute([':exam_id' => $id]);

        $exam['questions'] = $qStmt->fetchAll(PDO::FETCH_ASSOC) ?: [];

        return $exam;
    }

    /**
     * Get an exam result for a specific student.
     *
     * @param int $examId The exam ID
     * @param int $studentId The student ID
     * @return array<string, mixed>|null The exam result or null if not found
     */
    public function getResult(int $examId, int $studentId): ?array
    {
        $sql = 'SELECT er.*, e.title AS exam_title, e.total_marks AS exam_total_marks,
                       e.pass_marks AS exam_pass_marks, c.name AS course_name
                FROM `exam_results` 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';

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

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

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