<?php
namespace Models;

use Core\Model;

class Mark extends Model {
    protected string $table = 'marks';

    public static function computeGrade(float $score, float $outOf = 100): array {
        // CBC/CBE Competency levels
        $pct = $outOf > 0 ? ($score / $outOf) * 100 : 0;
        return match(true) {
            $pct >= 75 => ['grade'=>'EE', 'points'=>4, 'remarks'=>'Exceeding Expectations'],
            $pct >= 50 => ['grade'=>'ME', 'points'=>3, 'remarks'=>'Meeting Expectations'],
            $pct >= 25 => ['grade'=>'AE', 'points'=>2, 'remarks'=>'Approaching Expectations'],
            default    => ['grade'=>'BE', 'points'=>1, 'remarks'=>'Below Expectations'],
        };
    }

    public static function pointsToGrade(float $pts): string {
        return match(true) {
            $pts >= 3.5 => 'EE',
            $pts >= 2.5 => 'ME',
            $pts >= 1.5 => 'AE',
            default     => 'BE',
        };
    }

    public function upsert(int $studentId, int $examId, int $subjectId, float $score, int $enteredBy): void {
        $grade = self::computeGrade($score);
        $existing = $this->db->fetch(
            "SELECT id FROM marks WHERE student_id=? AND exam_id=? AND subject_id=?",
            [$studentId, $examId, $subjectId]
        );
        $data = array_merge($grade, [
            'score'      => $score,
            'entered_by' => $enteredBy,
            'updated_at' => date('Y-m-d H:i:s'),
        ]);
        if ($existing) {
            $this->db->update('marks', $data, 'id=?', [$existing['id']]);
        } else {
            $data['student_id']  = $studentId;
            $data['exam_id']     = $examId;
            $data['subject_id']  = $subjectId;
            $data['created_at']  = date('Y-m-d H:i:s');
            $this->db->insert('marks', $data);
        }
    }

    public function getStreamResults(int $streamId, int $examId): array {
        $rows = $this->db->fetchAll(
            "SELECT m.*, u.name AS student_name, s.assessment_no,
                    sub.name AS subject_name, sub.id AS subject_id
             FROM marks m
             JOIN students s ON s.id = m.student_id
             JOIN users u ON u.id = s.user_id
             JOIN subjects sub ON sub.id = m.subject_id
             WHERE s.stream_id=? AND m.exam_id=?
             ORDER BY u.name", [$streamId, $examId]
        );
        $grouped = [];
        foreach ($rows as $row) {
            $sid = $row['student_id'];
            if (!isset($grouped[$sid])) {
                $grouped[$sid] = [
                    'student_id'   => $sid,
                    'student_name' => $row['student_name'],
                    'assessment_no' => $row['assessment_no'],
                    'marks'        => [],
                    'total_points' => 0,
                ];
            }
            $grouped[$sid]['marks'][$row['subject_id']] = $row;
            $grouped[$sid]['total_points'] += (int)$row['points'];
        }
        usort($grouped, fn($a,$b) => $b['total_points'] <=> $a['total_points']);
        foreach ($grouped as $i => &$item) {
            $cnt = count($item['marks']);
            $item['position']   = $i + 1;
            $item['mean_grade'] = $cnt > 0 ? self::pointsToGrade($item['total_points'] / $cnt) : 'N/A';
        }
        return $grouped;
    }

    public function getStudentMarks(int $studentId, int $examId): array {
        return $this->db->fetchAll(
            "SELECT m.*, sub.name AS subject_name
             FROM marks m JOIN subjects sub ON sub.id = m.subject_id
             WHERE m.student_id=? AND m.exam_id=?", [$studentId, $examId]
        );
    }

    public function getStudentExamMarks(int $studentId): array {
        return $this->db->fetchAll(
            "SELECT m.*, sub.name AS subject_name, e.name AS exam_name, e.type AS exam_type
             FROM marks m
             JOIN subjects sub ON sub.id = m.subject_id
             JOIN exams e ON e.id = m.exam_id
             WHERE m.student_id=? AND e.is_published=1
             ORDER BY e.id DESC", [$studentId]
        );
    }
}

