<?php

namespace App\Models;

use CodeIgniter\Model;

class StudentGradeModel extends Model
{
    protected $table            = 'student_grades';
    protected $primaryKey       = 'id';
    protected $useAutoIncrement = true;
    protected $returnType       = 'array';
    protected $useSoftDeletes   = false;
    protected $allowedFields    = [
        'student_id', 'classroom_id', 'academic_period_id',
        'school_subject_id', 'score', 'import_batch',
    ];

    protected $useTimestamps = true;
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';

    /**
     * Ambil data leger untuk satu kelas + periode.
     */
    public function getLegerByClassroom($classroomId, $periodId)
    {
        $db = \Config\Database::connect();

        $students = $db->table('student_academic_histories as sah')
            ->select('students.id, students.nis, students.nisn, students.full_name, students.gender')
            ->join('students', 'students.id = sah.student_id')
            ->where('sah.classroom_id', $classroomId)
            ->where('sah.academic_period_id', $periodId)
            ->where('students.deleted_at', null)
            ->orderBy('students.full_name', 'ASC')
            ->get()->getResultArray();

        if (empty($students)) {
            return ['subject_codes' => [], 'students' => []];
        }

        $studentIds = array_column($students, 'id');

        $grades = $db->table('student_grades as sg')
            ->select('sg.student_id, sg.school_subject_id, sg.score,
                      ss.code as subject_code, ss.name as subject_name, ss.sort_order')
            ->join('school_subjects as ss', 'ss.id = sg.school_subject_id')
            ->where('sg.academic_period_id', $periodId)
            ->whereIn('sg.student_id', $studentIds)
            ->orderBy('ss.sort_order', 'ASC')
            ->get()->getResultArray();

        $subjectMap = [];
        foreach ($grades as $g) {
            $sid = $g['school_subject_id'];
            if (!isset($subjectMap[$sid])) {
                $subjectMap[$sid] = [
                    'id'         => $sid,
                    'code'       => $g['subject_code'],
                    'name'       => $g['subject_name'],
                    'sort_order' => $g['sort_order'],
                ];
            }
        }
        usort($subjectMap, fn($a, $b) => $a['sort_order'] - $b['sort_order']);

        $gradeMap = [];
        foreach ($grades as $g) {
            $gradeMap[$g['student_id']][$g['school_subject_id']] = $g['score'];
        }

        $result = [];
        foreach ($students as $student) {
            $studentGrades = $gradeMap[$student['id']] ?? [];
            $total         = array_sum($studentGrades);
            $result[]      = [
                'student' => $student,
                'grades'  => $studentGrades,
                'total'   => $total,
            ];
        }

        return [
            'subject_codes' => array_values($subjectMap),
            'students'      => $result,
        ];
    }

    /**
     * Bulk save semua nilai sekaligus dalam 1 query INSERT ... ON DUPLICATE KEY UPDATE.
     * Jauh lebih cepat dari upsert satu per satu (512 query → 1 query).
     *
     * $rows = [
     *   ['student_id'=>1, 'classroom_id'=>2, 'academic_period_id'=>3,
     *    'school_subject_id'=>4, 'score'=>96.0, 'import_batch'=>'LEGER-...'],
     *   ...
     * ]
     */
    public function bulkSave(array $rows): bool
    {
        if (empty($rows)) return true;

        $now = date('Y-m-d H:i:s');
        $db  = \Config\Database::connect();

        $placeholders = [];
        $binds        = [];

        foreach ($rows as $r) {
            $placeholders[] = '(?, ?, ?, ?, ?, ?, ?, ?)';
            $binds[] = $r['student_id'];
            $binds[] = $r['classroom_id'];
            $binds[] = $r['academic_period_id'];
            $binds[] = $r['school_subject_id'];
            $binds[] = $r['score'];
            $binds[] = $r['import_batch'];
            $binds[] = $now; // created_at
            $binds[] = $now; // updated_at
        }

        $sql = "INSERT INTO student_grades
                    (student_id, classroom_id, academic_period_id, school_subject_id,
                     score, import_batch, created_at, updated_at)
                VALUES " . implode(', ', $placeholders) . "
                ON DUPLICATE KEY UPDATE
                    score        = VALUES(score),
                    import_batch = VALUES(import_batch),
                    updated_at   = VALUES(updated_at)";

        return $db->query($sql, $binds);
    }

    /**
     * Hapus semua nilai untuk kelas + periode tertentu (untuk re-import)
     */
    public function deleteByClassroomAndPeriod($classroomId, $periodId)
    {
        return $this->where('classroom_id', $classroomId)
                    ->where('academic_period_id', $periodId)
                    ->delete();
    }

    /**
     * Ambil nilai satu siswa untuk satu periode (untuk halaman detail siswa)
     */
    public function getByStudent($studentId, $periodId)
    {
        return $this->select('student_grades.*, school_subjects.code, school_subjects.name as subject_name, school_subjects.sort_order')
                    ->join('school_subjects', 'school_subjects.id = student_grades.school_subject_id')
                    ->where('student_id', $studentId)
                    ->where('academic_period_id', $periodId)
                    ->orderBy('school_subjects.sort_order', 'ASC')
                    ->findAll();
    }
}