<?php

namespace App\Controllers\View;

use App\Models\StudentModel;
use App\Models\StudentClassModel;
use App\Models\ConfigurationModel;
use CodeIgniter\Controller;
use CodeIgniter\Events\Events;
use App\Models\HomeworkModel;
use App\Models\QuizModel;
use App\Models\ProjectModel;
use App\Models\MidtermExamModel;
use App\Models\FinalExamModel;
use App\Models\SemesterScoreModel; // Assuming you use test_avg here
use App\Models\ScoreCommentModel;
use App\Models\TeacherClassModel;
use App\Models\UserModel;
use App\Models\ClassSectionModel;
use App\Models\CalendarModel;
use App\Models\AttendanceRecordModel;
use App\Services\Calculators\AttendanceCalculator;
use RuntimeException;
use Config\Services;
use App\Models\ParentMeetingScheduleModel;
use App\Models\CurrentFlagModel;
use App\Models\PlacementLevelModel;
use App\Models\PlacementBatchModel;
use App\Models\PlacementScoreModel;
use App\Models\GradingLockModel;
use App\Models\BelowSixtyDecisionModel;
use App\Models\StudentDecisionModel;
use App\Services\NavbarService;

//use App\Models\ScoreModel;


class GradingController extends Controller
{
    protected $semesterScoreService;
    protected $db;
    protected $configModel;
    protected $homeworkModel;
    protected $userModel;
    protected $schoolYear;
    protected $semester;
    protected $studentClassModel;
    protected $studentModel;
    protected $teacherClassModel;
    protected $classSection;
    protected $attendanceCalculator;
    protected $parentMeetingModel;
    protected $placementLevelModel;
    protected $placementBatchModel;
    protected $placementScoreModel;
    protected $gradingLockModel;

    public function __construct()
    {
        // Initialize models
        $this->teacherClassModel = new TeacherClassModel();
        $this->homeworkModel = new HomeworkModel();
        $this->userModel = new UserModel();
        $this->studentClassModel = new StudentClassModel();
        $this->studentModel      = new StudentModel();
        $this->configModel = new ConfigurationModel();
        $this->db = \Config\Database::connect();
        $this->schoolYear = $this->configModel->getConfig('school_year');
        $this->semester = $this->configModel->getConfig('semester');
        $this->classSection = new ClassSectionModel();
        $this->attendanceCalculator = new AttendanceCalculator(
            new AttendanceRecordModel(),
            $this->configModel,
            new CalendarModel()
        );
        $this->parentMeetingModel = new ParentMeetingScheduleModel();
        $this->placementLevelModel = new PlacementLevelModel();
        $this->placementBatchModel = new PlacementBatchModel();
        $this->placementScoreModel = new PlacementScoreModel();
        $this->gradingLockModel = new GradingLockModel();

        // Log the service initialization
        log_message('debug', 'Initializing SemesterScoreService');

        $this->semesterScoreService = service('semesterScoreService');
    }

    public function show($type, $classSectionId, $studentId)
    {
        $scoreModel = $this->getModelByType($type);
        $studentModel = new StudentModel();
        $configModel = new ConfigurationModel();

        $schoolYear = $configModel->getConfig('school_year');
        $semester = $configModel->getConfig('semester');

        $student = $studentModel->find($studentId);
        $scores = $scoreModel->where([
            'student_id' => $studentId,
            'semester' => $semester,
            'school_year' => $schoolYear
        ])->findAll();
        $scoresLocked = false;
        $classSectionIdInt = (int) ($classSectionId ?? 0);
        if ($classSectionIdInt > 0) {
            $scoresLocked = $this->gradingLockModel->isLocked($classSectionIdInt, $semester, $schoolYear);
        }

        return view("grading/{$type}", [
            'student' => $student,
            'scores' => $scores,
            'type' => $type,
            'classSectionId' => $classSectionId, // ✅ pass it manually
            'semester' => $semester, // ✅ Pass semester to the view
            'scoresLocked' => $scoresLocked,
        ]);
    }

    private function getModelByType($type)
    {
        return match ($type) {
            'homework' => new HomeworkModel(),
            'quiz' => new QuizModel(),
            'project' => new ProjectModel(),
            'midterm' => new MidtermExamModel(),
            'final' => new FinalExamModel(),
            'test' => new SemesterScoreModel(), // Assuming you use test_avg here
            'comments' => new ScoreCommentModel(),
            default => throw new \InvalidArgumentException("Invalid type: $type"),
        };
    }

    public function update()
    {
        $type = $this->request->getPost('type');
        $studentId = $this->request->getPost('student_id');
        $classSectionId = $this->request->getPost('class_section_id');
        $configModel = new ConfigurationModel();
        $studentModel = new StudentModel();

        $schoolYear = $configModel->getConfig('school_year');
        $semester = $configModel->getConfig('semester');

        $model = $this->getModelByType($type);
        $classSectionIdInt = (int) ($classSectionId ?? 0);
        if ($classSectionIdInt > 0 && $this->isScoresLocked($classSectionIdInt, $semester, $schoolYear)) {
            return redirect()->back()->with('error', 'Scores are locked for this class. Unlock to edit.');
        }

        if (in_array($type, ['homework', 'quiz', 'project'])) {
            $scoreIds = $this->request->getPost('score_ids');
            $scores = $this->request->getPost('scores');
            $comments = $this->request->getPost('comments');

            foreach ($scoreIds as $i => $id) {
                $model->update($id, [
                    'score' => $scores[$i],
                    'comment' => $comments[$i] ?? null,
                    'updated_at' => utc_now()
                ]);
            }
        } elseif (in_array($type, ['midterm', 'final', 'test'])) {
            $score = $this->request->getPost('score');

            $data = [
                'score' => $score,
                'updated_at' => utc_now()
            ];

            $existing = $model->where([
                'student_id' => $studentId,
                'class_section_id' => $classSectionId,
                'semester' => $semester,
                'school_year' => $schoolYear
            ])->first();

            if ($existing) {
                $model->update($existing['id'], $data);
            } else {
                $data += [
                    'student_id' => $studentId,
                    'class_section_id' => $classSectionId,
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                    'created_at' => utc_now()
                ];
                $model->insert($data);
            }
        } elseif ($type === 'comments') {
            $comment = $this->request->getPost('comment');

            $model->where([
                'student_id' => $studentId,
                'semester' => $semester,
                'school_year' => $schoolYear
            ])->delete(); // Remove existing comments of this type (optional)

            $model->insert([
                'student_id' => $studentId,
                'score_type' => 'general',
                'semester' => $semester,
                'school_year' => $schoolYear,
                'comment' => $comment,
                'commented_by' => session()->get('user_id'),
                'created_at' => utc_now()
            ]);
        }

        $studentTeacherInfo = $studentModel->getStudentInfoByClassSectionId($classSectionId, $semester, $schoolYear);
        // Call the updateScoresForStudents method
        try {

            $this->semesterScoreService->updateScoresForStudents($studentTeacherInfo, $semester, $schoolYear);
        } catch (RuntimeException $e) {
            // Handle error
        }
        return redirect()->back()->with('status', 'Scores updated successfully.');
    }

    public function grading()
    {
        $schoolYear = (string) $this->schoolYear;
        $configuredSemester = (string) $this->semester;
        $requestedClassId = (int) ($this->request->getGet('class_id') ?? 0);
        $semesterOptions = $this->getSemestersForSchoolYear($schoolYear, $configuredSemester);
        $session = session();
        $requestedSemester = $this->normalizeSemesterInput($this->request->getGet('semester'));
        $sessionSemester = $this->normalizeSemesterInput($session->get('grading_selected_semester'));
        $effectiveRequested = $requestedSemester ?? $sessionSemester;
        $semester = trim($this->resolveSemesterSelection($effectiveRequested, $semesterOptions, $configuredSemester));
        if ($semester === '') {
            $semester = $configuredSemester !== '' ? $configuredSemester : ($semesterOptions[0] ?? 'Fall');
        }
        if (!in_array($semester, $semesterOptions, true)) {
            $semesterOptions[] = $semester;
        }
        $semesterOptions = array_values(array_unique($semesterOptions));
        $session->set('grading_selected_semester', $semester);

        $this->ensureParentReleaseKeyExists('Fall');
        $this->ensureParentReleaseKeyExists('Spring');
        $scoresReleased = $this->getParentScoresReleasedForSemester($semester);
        $scoresReleasedFall = $this->getParentScoresReleasedForSemester('Fall');
        $scoresReleasedSpring = $this->getParentScoresReleasedForSemester('Spring');

        // Refresh PTAP/semester scores for the requested class (if provided) so values are present.
        if ($requestedClassId > 0 && $this->semesterScoreService !== null) {
            $sectionIds = $this->classSection
                ->select('class_section_id')
                ->where('class_id', $requestedClassId)
                ->findAll();

            $sectionIds = array_values(array_filter(array_map(
                static fn($row) => (int)($row['class_section_id'] ?? 0),
                $sectionIds
            ), static fn($id) => $id > 0));

            foreach ($sectionIds as $sectionId) {
                $studentTeacherInfo = $this->studentModel->getStudentInfoByClassSectionId(
                    $sectionId,
                    $semester,
                    $schoolYear
                );
                if (empty($studentTeacherInfo)) {
                    continue;
                }
                try {
                    $this->semesterScoreService->updateScoresForStudents($studentTeacherInfo, $semester, $schoolYear);
                } catch (\Throwable $e) {
                    log_message(
                        'error',
                        'GradingController::grading score refresh failed for section '
                        . $sectionId . ': ' . $e->getMessage()
                    );
                }
            }
        }

        // Normalize the semester text for safe comparison
        $semEsc = $this->db->escape($semester);
        $yrEsc  = $this->db->escape($schoolYear);

        $rows = $this->buildGradingRows($semEsc, $yrEsc, $schoolYear, $semester);

        // Preload quiz/homework/project/participation/midterm score counts to distinguish true zeros from empty scores
        $quizCounts = [];
        $homeworkCounts = [];
        $projectCounts = [];
        $participationCounts = [];
        $midtermCounts = [];
        if (!empty($rows)) {
            $sectionIds = [];
            $studentIds = [];
            foreach ($rows as $r) {
                $sid = (int) ($r['student_id'] ?? 0);
                $sec = (int) ($r['section_id'] ?? 0);
                if ($sid > 0) $studentIds[$sid] = true;
                if ($sec > 0) $sectionIds[$sec] = true;
            }
            $sectionIds = array_keys($sectionIds);
            $studentIds = array_keys($studentIds);

            if (!empty($sectionIds) && !empty($studentIds)) {
                $quizRows = $this->db->table('quiz')
                    ->select('student_id, class_section_id, COUNT(*) AS cnt')
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->whereIn('class_section_id', $sectionIds)
                    ->whereIn('student_id', $studentIds)
                    ->where('score IS NOT NULL', null, false)
                    ->groupBy('student_id, class_section_id')
                    ->get()->getResultArray();

                foreach ($quizRows as $qr) {
                    $sec = (int) ($qr['class_section_id'] ?? 0);
                    $sid = (int) ($qr['student_id'] ?? 0);
                    if ($sec > 0 && $sid > 0) {
                        $quizCounts[$sec][$sid] = (int) ($qr['cnt'] ?? 0);
                    }
                }

                $hwRows = $this->db->table('homework')
                    ->select('student_id, class_section_id, COUNT(*) AS cnt')
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->whereIn('class_section_id', $sectionIds)
                    ->whereIn('student_id', $studentIds)
                    ->where('score IS NOT NULL', null, false)
                    ->groupBy('student_id, class_section_id')
                    ->get()->getResultArray();

                foreach ($hwRows as $hr) {
                    $sec = (int) ($hr['class_section_id'] ?? 0);
                    $sid = (int) ($hr['student_id'] ?? 0);
                    if ($sec > 0 && $sid > 0) {
                        $homeworkCounts[$sec][$sid] = (int) ($hr['cnt'] ?? 0);
                    }
                }

                $projectRows = $this->db->table('project')
                    ->select('student_id, class_section_id, COUNT(*) AS cnt')
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->whereIn('class_section_id', $sectionIds)
                    ->whereIn('student_id', $studentIds)
                    ->where('score IS NOT NULL', null, false)
                    ->groupBy('student_id, class_section_id')
                    ->get()->getResultArray();

                foreach ($projectRows as $pr) {
                    $sec = (int) ($pr['class_section_id'] ?? 0);
                    $sid = (int) ($pr['student_id'] ?? 0);
                    if ($sec > 0 && $sid > 0) {
                        $projectCounts[$sec][$sid] = (int) ($pr['cnt'] ?? 0);
                    }
                }

                $participationRows = $this->db->table('participation')
                    ->select('student_id, class_section_id, COUNT(*) AS cnt')
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->whereIn('class_section_id', $sectionIds)
                    ->whereIn('student_id', $studentIds)
                    ->where('score IS NOT NULL', null, false)
                    ->groupBy('student_id, class_section_id')
                    ->get()->getResultArray();

                foreach ($participationRows as $par) {
                    $sec = (int) ($par['class_section_id'] ?? 0);
                    $sid = (int) ($par['student_id'] ?? 0);
                    if ($sec > 0 && $sid > 0) {
                        $participationCounts[$sec][$sid] = (int) ($par['cnt'] ?? 0);
                    }
                }

                $midtermRows = $this->db->table('midterm_exam')
                    ->select('student_id, class_section_id, COUNT(*) AS cnt')
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->whereIn('class_section_id', $sectionIds)
                    ->whereIn('student_id', $studentIds)
                    ->where('score IS NOT NULL', null, false)
                    ->groupBy('student_id, class_section_id')
                    ->get()->getResultArray();

                foreach ($midtermRows as $mr) {
                    $sec = (int) ($mr['class_section_id'] ?? 0);
                    $sid = (int) ($mr['student_id'] ?? 0);
                    if ($sec > 0 && $sid > 0) {
                        $midtermCounts[$sec][$sid] = (int) ($mr['cnt'] ?? 0);
                    }
                }
            }
        }

        // If any section is missing PTAP or semester score, refresh and reload once
        $sectionsNeedingRefresh = [];
        foreach ($rows as $r) {
            $sectionId = (int) ($r['section_id'] ?? 0);
            if ($sectionId <= 0) continue;
            $ptapMissing = $r['ss_ptap_score'] === null;
            $semMissing  = $r['ss_semester_score'] === null;
            if ($ptapMissing || $semMissing) {
                $sectionsNeedingRefresh[$sectionId] = true;
            }
        }

        if (!empty($sectionsNeedingRefresh) && $this->semesterScoreService !== null) {
            foreach (array_keys($sectionsNeedingRefresh) as $sectionId) {
                $studentTeacherInfo = $this->studentModel->getStudentInfoByClassSectionId(
                    $sectionId,
                    $semester,
                    $schoolYear
                );
                if (empty($studentTeacherInfo)) {
                    continue;
                }
                try {
                    $this->semesterScoreService->updateScoresForStudents($studentTeacherInfo, $semester, $schoolYear);
                } catch (\Throwable $e) {
                    log_message(
                        'error',
                        'GradingController::grading refresh missing scores for section '
                        . $sectionId . ': ' . $e->getMessage()
                    );
                }
            }
            // Reload rows after refresh
            $rows = $this->buildGradingRows($semEsc, $yrEsc, $schoolYear, $semester);
        }

        // Build structures keyed by BUSINESS section id
        $grades            = []; // class_id => [ ['class_section_id','class_section_name'], ... ]
        $studentsBySection = []; // section_id => [ students... ]

        foreach ($rows as $r) {
            $sectionId   = (int) ($r['section_id'] ?? 0);   // BUSINESS id
            $classId     = (int) ($r['class_id'] ?? 0);
            $sectionName = (string) ($r['class_section_name'] ?? '');
            if ($sectionId <= 0 || $classId <= 0) continue;

            if (!isset($grades[$classId])) $grades[$classId] = [];
            $exists = false;
            foreach ($grades[$classId] as $s) {
                if ((int)$s['class_section_id'] === $sectionId) {
                    $exists = true;
                    break;
                }
            }
            if (!$exists) {
                $grades[$classId][] = [
                    'class_section_id'   => $sectionId,
                    'class_section_name' => $sectionName,
                ];
            }

            $sid = (int) ($r['student_id'] ?? 0);
            if ($sid <= 0) continue;

            $ptapScore     = $r['ss_ptap_score'] ?? null;
            $semesterScore = $r['ss_semester_score'] ?? null;

            $attendanceScore = $this->calculateAttendanceScoreForStudent(
                $sid,
                $semester,
                $schoolYear,
                $sectionId
            );
            if ($attendanceScore === null) {
                $rawAttendance = $r['ss_attendance_score'] ?? null;
                if ($rawAttendance !== null && $rawAttendance !== '') {
                    $attendanceScore = round((float) $rawAttendance, 2);
                }
            }
            $homeworkAvg = isset($r['ss_homework_avg']) && $r['ss_homework_avg'] !== '' ? round((float) $r['ss_homework_avg'], 2) : null;
            if ($homeworkAvg !== null && (float) $homeworkAvg === 0.0) {
                $hwCount = (int) ($homeworkCounts[$sectionId][$sid] ?? 0);
                if ($hwCount === 0) {
                    $homeworkAvg = null;
                }
            }

            $projectAvg = isset($r['ss_project_avg']) && $r['ss_project_avg'] !== '' ? round((float) $r['ss_project_avg'], 2) : null;
            if ($projectAvg !== null && (float) $projectAvg === 0.0) {
                $prCount = (int) ($projectCounts[$sectionId][$sid] ?? 0);
                if ($prCount === 0) {
                    $projectAvg = null;
                }
            }

            $participationScore = isset($r['ss_participation_score']) && $r['ss_participation_score'] !== '' ? round((float) $r['ss_participation_score'], 2) : null;
            if ($participationScore !== null && (float) $participationScore === 0.0) {
                $pCount = (int) ($participationCounts[$sectionId][$sid] ?? 0);
                if ($pCount === 0) {
                    $participationScore = null;
                }
            }

            $midtermExam = isset($r['ss_midterm_exam_score']) && $r['ss_midterm_exam_score'] !== '' ? round((float) $r['ss_midterm_exam_score'], 2) : null;
            if ($midtermExam !== null && (float) $midtermExam === 0.0) {
                $mCount = (int) ($midtermCounts[$sectionId][$sid] ?? 0);
                if ($mCount === 0) {
                    $midtermExam = null;
                }
            }

            $quizAvg = isset($r['ss_quiz_avg']) && $r['ss_quiz_avg'] !== '' ? round((float) $r['ss_quiz_avg'], 2) : null;
            if ($quizAvg !== null && (float) $quizAvg === 0.0) {
                $quizCount = (int) ($quizCounts[$sectionId][$sid] ?? 0);
                if ($quizCount === 0) {
                    $quizAvg = null;
                }
            }

            $studentsBySection[$sectionId][] = [
                'id'             => $sid,
                'school_id'      => $r['school_id'] ?? null,
                'firstname'      => $r['firstname'] ?? null,
                'lastname'       => $r['lastname'] ?? null,
                'class_id'       => $classId,
                'ptap'           => is_null($ptapScore) ? null : round((float) $ptapScore, 2),
                'semester_score' => is_null($semesterScore) ? null : round((float) $semesterScore, 2),
                'attendance'     => $attendanceScore,
                'homework_avg'   => $homeworkAvg,
                'project_avg'    => $projectAvg,
                'quiz_avg'       => $quizAvg,
                'participation'  => $participationScore,
                'midterm_exam'   => $midtermExam,
                'final_exam'     => isset($r['ss_final_exam_score']) && $r['ss_final_exam_score'] !== '' ? round((float) $r['ss_final_exam_score'], 2) : null,
                'matched_biz_csid' => $r['matched_biz_csid'] ?? null,
                'matched_pk_csid'  => $r['matched_pk_csid'] ?? null,
                'placement_level' => $r['placement_level'] ?? null,
            ];
        }

        $scoreLocks = [];
        $lockSectionIds = [];
        foreach ($grades as $sections) {
            foreach ($sections as $section) {
                $sid = (int) ($section['class_section_id'] ?? 0);
                if ($sid > 0) {
                    $lockSectionIds[$sid] = true;
                }
            }
        }
        $lockSectionIds = array_keys($lockSectionIds);
        if (!empty($lockSectionIds)) {
            $lockRows = $this->gradingLockModel
                ->whereIn('class_section_id', $lockSectionIds)
                ->where('semester', $semester)
                ->where('school_year', $schoolYear)
                ->findAll();
            foreach ($lockRows as $row) {
                $scoreLocks[(int) ($row['class_section_id'] ?? 0)] = !empty($row['is_locked']);
            }
        }

        return view('grading/grading_main', [
            'grades'            => $grades,
            'studentsBySection' => $studentsBySection,
            'semester'          => $semester,
            'schoolYear'        => $schoolYear,
            'requestedClassId'  => $requestedClassId,
            'semesterOptions'   => $semesterOptions,
            'scoresReleased'    => $scoresReleased,
            'scoresReleasedFall' => $scoresReleasedFall,
            'scoresReleasedSpring' => $scoresReleasedSpring,
            'scoreLocks'        => $scoreLocks,
        ]);
    }

    public function toggleScoreLock()
    {
        $classSectionId = (int) ($this->request->getPost('class_section_id') ?? 0);
        $semester = trim((string) ($this->request->getPost('semester') ?? $this->semester));
        $schoolYear = trim((string) ($this->request->getPost('school_year') ?? $this->schoolYear));

        if ($classSectionId <= 0 || $semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing class section or term.');
        }

        $existing = $this->gradingLockModel->getLock($classSectionId, $semester, $schoolYear);
        $userId = (int) (session()->get('user_id') ?? 0);

        if (!empty($existing) && !empty($existing['is_locked'])) {
            $this->gradingLockModel->update($existing['id'], [
                'is_locked' => 0,
                'locked_by' => null,
                'locked_at' => null,
            ]);
            return redirect()->back()->with('status', 'Scores unlocked for this class.');
        }

        if (!empty($existing)) {
            $this->gradingLockModel->update($existing['id'], [
                'is_locked' => 1,
                'locked_by' => $userId > 0 ? $userId : null,
                'locked_at' => utc_now(),
            ]);
        } else {
            $this->gradingLockModel->insert([
                'class_section_id' => $classSectionId,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'is_locked' => 1,
                'locked_by' => $userId > 0 ? $userId : null,
                'locked_at' => utc_now(),
            ]);
        }

        return redirect()->back()->with('status', 'Scores locked for this class.');
    }

    public function lockAllScores()
    {
        $semester = trim((string) ($this->request->getPost('semester') ?? $this->semester));
        $schoolYear = trim((string) ($this->request->getPost('school_year') ?? $this->schoolYear));

        if ($semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing semester or school year.');
        }

        $sectionRows = $this->classSection
            ->select('class_section_id')
            ->groupBy('class_section_id')
            ->findAll();

        $sectionIds = array_values(array_filter(array_map(
            static fn($row) => (int) ($row['class_section_id'] ?? 0),
            $sectionRows
        ), static fn($id) => $id > 0));

        if (empty($sectionIds)) {
            return redirect()->back()->with('error', 'No class sections found to lock.');
        }

        $existingLocks = $this->gradingLockModel
            ->whereIn('class_section_id', $sectionIds)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->findAll();

        $existingBySection = [];
        foreach ($existingLocks as $row) {
            $sid = (int) ($row['class_section_id'] ?? 0);
            if ($sid > 0) {
                $existingBySection[$sid] = $row;
            }
        }

        $userId = (int) (session()->get('user_id') ?? 0);
        $now = utc_now();
        $insertRows = [];

        foreach ($sectionIds as $sid) {
            if (!empty($existingBySection[$sid])) {
                if (!empty($existingBySection[$sid]['is_locked'])) {
                    continue;
                }
                $this->gradingLockModel->update($existingBySection[$sid]['id'], [
                    'is_locked' => 1,
                    'locked_by' => $userId > 0 ? $userId : null,
                    'locked_at' => $now,
                ]);
                continue;
            }

            $insertRows[] = [
                'class_section_id' => $sid,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'is_locked' => 1,
                'locked_by' => $userId > 0 ? $userId : null,
                'locked_at' => $now,
                'created_at' => $now,
                'updated_at' => $now,
            ];
        }

        if (!empty($insertRows)) {
            $this->gradingLockModel->insertBatch($insertRows);
        }

        return redirect()->back()->with('status', 'Scores locked for all classes.');
    }

    private function isScoresLocked(int $classSectionId, string $semester, string $schoolYear): bool
    {
        return $this->gradingLockModel->isLocked($classSectionId, $semester, $schoolYear);
    }

    public function updatePlacementLevel()
    {
        $studentId = (int) ($this->request->getPost('student_id') ?? 0);
        $levelRaw = trim((string) ($this->request->getPost('placement_level') ?? ''));
        $schoolYear = (string) ($this->request->getPost('school_year') ?? $this->schoolYear);

        if ($studentId <= 0 || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing student or school year.');
        }

        $level = $levelRaw === '' ? null : (int) $levelRaw;
        if ($level !== null && !in_array($level, [1, 2, 3], true)) {
            return redirect()->back()->with('error', 'Invalid placement level.');
        }

        $existing = $this->placementLevelModel
            ->where('student_id', $studentId)
            ->first();

        if ($level === null) {
            if ($existing) {
                $this->placementLevelModel->delete($existing['id']);
            }
            return redirect()->back()->with('status', 'Placement level cleared.');
        }

        $payload = [
            'student_id' => $studentId,
            'level' => $level,
            'updated_by' => session()->get('user_id'),
        ];

        if ($existing) {
            $this->placementLevelModel->update($existing['id'], $payload);
        } else {
            $payload['created_by'] = session()->get('user_id');
            $this->placementLevelModel->insert($payload);
        }

        return redirect()->back()->with('status', 'Placement level updated.');
    }

    public function placement()
    {
        $classSectionId = (int) ($this->request->getGet('class_section_id') ?? 0);
        $schoolYear = (string) ($this->request->getGet('school_year') ?? $this->schoolYear);
        $placementTest = (string) ($this->request->getGet('placement_test') ?? '');
        $openFlag = (string) ($this->request->getGet('open') ?? '');

        if ($classSectionId <= 0 || $schoolYear === '') {
            $showStudents = ($placementTest !== '' && $openFlag === '1');
            $students = $showStudents ? $this->fetchActiveStudentsWithSection($schoolYear) : [];
            $batches = $this->fetchPlacementBatches($schoolYear);
            $batchDetails = $this->fetchPlacementBatchDetails($batches, $schoolYear);
            return view('grading/placement_index', [
                'schoolYear' => $schoolYear,
                'students' => $students,
                'batches' => $batches,
                'batchDetails' => $batchDetails,
                'placementTest' => $placementTest,
                'showStudents' => $showStudents,
            ]);
        }

        $sectionName = $this->classSection->getClassSectionNameBySectionId($classSectionId) ?? '';
        $classId = $this->classSection->getClassId($classSectionId);

        $students = $this->studentModel->getStudentInfoByClassSectionId($classSectionId, null, $schoolYear);
        $studentIds = array_values(array_filter(array_map(
            static fn($row) => (int) ($row['student_id'] ?? 0),
            $students
        ), static fn($id) => $id > 0));

        $levels = [];
        if (!empty($studentIds)) {
            $rows = $this->placementLevelModel
                ->whereIn('student_id', $studentIds)
                ->findAll();
            foreach ($rows as $row) {
                $levels[(int) $row['student_id']] = $row['level'] ?? null;
            }
        }

        return view('grading/placement', [
            'classSectionId' => $classSectionId,
            'classSectionName' => $sectionName,
            'classId' => $classId,
            'schoolYear' => $schoolYear,
            'students' => $students,
            'levels' => $levels,
        ]);
    }

    public function updatePlacementLevels()
    {
        $classSectionId = (int) ($this->request->getPost('class_section_id') ?? 0);
        $schoolYear = (string) ($this->request->getPost('school_year') ?? $this->schoolYear);
        $levels = $this->request->getPost('placement_level') ?? [];

        if ($classSectionId <= 0 || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing class section or school year.');
        }

        $students = $this->studentModel->getStudentInfoByClassSectionId($classSectionId, null, $schoolYear);
        $validIds = array_values(array_filter(array_map(
            static fn($row) => (int) ($row['student_id'] ?? 0),
            $students
        ), static fn($id) => $id > 0));

        $validSet = array_flip($validIds);
        $existingRows = [];
        if (!empty($validIds)) {
            $rows = $this->placementLevelModel
                ->whereIn('student_id', $validIds)
                ->findAll();
            foreach ($rows as $row) {
                $existingRows[(int) $row['student_id']] = $row;
            }
        }

        $userId = session()->get('user_id');
        foreach ($levels as $studentIdRaw => $levelRaw) {
            $studentId = (int) $studentIdRaw;
            if (!isset($validSet[$studentId])) {
                continue;
            }
            $levelRaw = trim((string) $levelRaw);
            $level = $levelRaw === '' ? null : (int) $levelRaw;
            if ($level !== null && !in_array($level, [1, 2, 3], true)) {
                continue;
            }

            if ($level === null) {
                if (isset($existingRows[$studentId])) {
                    $this->placementLevelModel->delete($existingRows[$studentId]['id']);
                }
                continue;
            }

            $payload = [
                'student_id' => $studentId,
                'level' => $level,
                'updated_by' => $userId,
            ];

            if (isset($existingRows[$studentId])) {
                $this->placementLevelModel->update($existingRows[$studentId]['id'], $payload);
            } else {
                $payload['created_by'] = $userId;
                $this->placementLevelModel->insert($payload);
            }
        }

        return redirect()->back()->with('status', 'Placement levels updated.');
    }

    public function updatePlacementLevelsAll()
    {
        $schoolYear = (string) ($this->request->getPost('school_year') ?? $this->schoolYear);
        $placementTest = (string) ($this->request->getPost('placement_test') ?? '');
        $levels = $this->request->getPost('placement_level') ?? [];

        if ($schoolYear === '' || $placementTest === '') {
            return redirect()->back()->with('error', 'Missing placement test or school year.');
        }

        $students = $this->fetchActiveStudentsWithSection($schoolYear);
        $validIds = array_values(array_filter(array_map(
            static fn($row) => (int) ($row['student_id'] ?? 0),
            $students
        ), static fn($id) => $id > 0));

        $validSet = array_flip($validIds);
        $userId = session()->get('user_id');

        $batchId = $this->placementBatchModel->insert([
            'placement_test' => $placementTest,
            'school_year' => $schoolYear,
            'created_by' => $userId,
            'updated_by' => $userId,
        ]);

        if (!$batchId) {
            return redirect()->back()->with('error', 'Unable to create placement batch.');
        }

        $savedCount = 0;
        foreach ($levels as $studentIdRaw => $levelRaw) {
            $studentId = (int) $studentIdRaw;
            if (!isset($validSet[$studentId])) {
                continue;
            }
            $levelRaw = trim((string) $levelRaw);
            if ($levelRaw === '') {
                continue;
            }
            $level = (int) $levelRaw;
            if ($level < 0 || $level > 100) {
                continue;
            }

            $this->placementScoreModel->insert([
                'batch_id' => (int) $batchId,
                'student_id' => $studentId,
                'score' => $level,
                'created_by' => $userId,
                'updated_by' => $userId,
            ]);
            $savedCount++;
        }

        if ($savedCount === 0) {
            $this->placementBatchModel->delete((int) $batchId);
            return redirect()->back()->with('error', 'No scores entered. Batch not saved.');
        }

        return redirect()->to(base_url('grading/placement'))
            ->with('status', 'Placement batch saved.');
    }

    public function editPlacementBatch(int $batchId)
    {
        $batch = $this->placementBatchModel->find($batchId);
        if (!$batch) {
            return redirect()->to(base_url('grading/placement'))
                ->with('error', 'Placement batch not found.');
        }

        $schoolYear = (string) ($batch['school_year'] ?? $this->schoolYear);
        $students = $this->fetchActiveStudentsWithSection($schoolYear);
        $scores = $this->fetchPlacementScoresForBatch($batchId);

        return view('grading/placement_batch', [
            'batch' => $batch,
            'students' => $students,
            'scores' => $scores,
        ]);
    }

    public function updatePlacementBatch(int $batchId)
    {
        $batch = $this->placementBatchModel->find($batchId);
        if (!$batch) {
            return redirect()->to(base_url('grading/placement'))
                ->with('error', 'Placement batch not found.');
        }

        $schoolYear = (string) ($batch['school_year'] ?? $this->schoolYear);
        $levels = $this->request->getPost('placement_level') ?? [];

        $students = $this->fetchActiveStudentsWithSection($schoolYear);
        $validIds = array_values(array_filter(array_map(
            static fn($row) => (int) ($row['student_id'] ?? 0),
            $students
        ), static fn($id) => $id > 0));

        $validSet = array_flip($validIds);
        $existing = $this->fetchPlacementScoresForBatch($batchId);

        $userId = session()->get('user_id');
        foreach ($levels as $studentIdRaw => $scoreRaw) {
            $studentId = (int) $studentIdRaw;
            if (!isset($validSet[$studentId])) {
                continue;
            }
            $scoreRaw = trim((string) $scoreRaw);
            if ($scoreRaw === '') {
                if (isset($existing[$studentId])) {
                    $this->placementScoreModel->delete($existing[$studentId]['id']);
                }
                continue;
            }
            $score = (int) $scoreRaw;
            if ($score < 0 || $score > 100) {
                continue;
            }

            if (isset($existing[$studentId])) {
                $this->placementScoreModel->update($existing[$studentId]['id'], [
                    'score' => $score,
                    'updated_by' => $userId,
                ]);
            } else {
                $this->placementScoreModel->insert([
                    'batch_id' => $batchId,
                    'student_id' => $studentId,
                    'score' => $score,
                    'created_by' => $userId,
                    'updated_by' => $userId,
                ]);
            }
        }

        $this->placementBatchModel->update($batchId, [
            'updated_by' => $userId,
        ]);

        return redirect()->to(base_url('grading/placement'))
            ->with('status', 'Placement batch updated.');
    }

    private function fetchActiveStudentsWithSection(string $schoolYear): array
    {
        return $this->db->table('students s')
            ->select('s.id AS student_id, s.school_id, s.firstname, s.lastname, sc.class_section_id, cs.class_section_name, c.class_name')
            ->join('student_class sc', 'sc.student_id = s.id AND sc.school_year = ' . $this->db->escape($schoolYear), 'left')
            ->join('`classSection` cs', 'cs.class_section_id = sc.class_section_id', 'left')
            ->join('classes c', 'c.id = cs.class_id', 'left')
            ->where('s.is_active', 1)
            ->orderBy('s.lastname', 'ASC')
            ->orderBy('s.firstname', 'ASC')
            ->get()
            ->getResultArray();
    }

    private function fetchPlacementBatches(string $schoolYear): array
    {
        return $this->placementBatchModel
            ->where('school_year', $schoolYear)
            ->orderBy('created_at', 'DESC')
            ->findAll();
    }

    private function fetchPlacementBatchDetails(array $batches, string $schoolYear): array
    {
        if (empty($batches)) {
            return [];
        }

        $batchIds = array_values(array_filter(array_map(
            static fn($row) => (int) ($row['id'] ?? 0),
            $batches
        ), static fn($id) => $id > 0));

        if (empty($batchIds)) {
            return [];
        }

        $rows = $this->db->table('placement_scores ps')
            ->select('ps.batch_id, ps.score, s.school_id, s.firstname, s.lastname, cs.class_section_name, c.class_name')
            ->join('students s', 's.id = ps.student_id', 'inner')
            ->join('student_class sc', 'sc.student_id = s.id AND sc.school_year = ' . $this->db->escape($schoolYear), 'left')
            ->join('`classSection` cs', 'cs.class_section_id = sc.class_section_id', 'left')
            ->join('classes c', 'c.id = cs.class_id', 'left')
            ->whereIn('ps.batch_id', $batchIds)
            ->where('s.is_active', 1)
            ->orderBy('ps.batch_id', 'ASC')
            ->orderBy('s.lastname', 'ASC')
            ->orderBy('s.firstname', 'ASC')
            ->get()
            ->getResultArray();

        $details = [];
        foreach ($rows as $row) {
            $bid = (int) ($row['batch_id'] ?? 0);
            if ($bid <= 0) continue;
            $details[$bid][] = $row;
        }

        return $details;
    }

    private function fetchPlacementScoresForBatch(int $batchId): array
    {
        $rows = $this->placementScoreModel
            ->where('batch_id', $batchId)
            ->findAll();
        $scores = [];
        foreach ($rows as $row) {
            $scores[(int) $row['student_id']] = $row;
        }
        return $scores;
    }

public function belowSixty()
{
    $schoolYear = $this->currentSchoolYearName((string) $this->schoolYear);

    // This page is Fall only.
    $semester   = 'fall';
    $isYearMode = false;

    $schoolYears = $this->getSchoolYearsForScores($schoolYear);

    /*
     * Use your existing below-60 fetcher.
     * Do NOT query below_sixty_status. That table does not exist.
     */
    $rows = $this->fetchBelowSixtyRows($schoolYear, $semester);

    /*
     * Hard guard:
     * Keep only Fall semester rows with semester_score < 60.
     * This prevents whole-year rows or accidental other semester rows
     * from sneaking into this Fall-only page.
     */
    $rows = array_values(array_filter($rows, static function ($row) {
        $semesterValue = strtolower(trim((string)($row['semester'] ?? 'fall')));
        $scoreRaw      = $row['semester_score'] ?? null;

        if ($semesterValue !== '' && $semesterValue !== 'fall') {
            return false;
        }

        if (!is_numeric($scoreRaw)) {
            return false;
        }

        return (float)$scoreRaw < 60;
    }));

    foreach ($rows as &$row) {
        $row['status'] = $row['status'] ?? 'Open';
        $row['note']   = $row['note'] ?? '';
    }

    unset($row);

    $canViewGrading = $this->userHasMenuUrl('grading');

    return view('grading/below_sixty', [
        'rows'           => $rows,
        'semester'       => $semester,
        'schoolYear'     => $schoolYear,
        'schoolYears'    => $schoolYears,
        'isYearMode'     => $isYearMode,
        'canViewGrading' => $canViewGrading,
    ]);
}

    private function currentSchoolYearName(?string $fallback = null): string
    {
        try {
            return service('schoolYearContext')->resolve($this->request)->yearName();
        } catch (\Throwable) {
            return trim((string) ($fallback ?? ''));
        }
    }

    public function editBelowSixtyEmail()
    {
        $studentId = (int)$this->request->getGet('student_id');
        $semester = trim((string)$this->request->getGet('semester'));
        $schoolYear = trim((string)$this->request->getGet('school_year'));

        if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing student or term.');
        }

        $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
        if (empty($row)) {
            return redirect()->back()->with('error', 'Student record not found for the selected term.');
        }

        $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        $subject = $this->buildBelowSixtySubject($studentName, $semester, $schoolYear);
        $parentName = $this->fetchBelowSixtyParentName($studentId);

        $scores = [
            'homework_avg' => $row['homework_avg'] ?? null,
            'project_avg' => $row['project_avg'] ?? null,
            'participation_score' => $row['participation_score'] ?? null,
            'test_avg' => $row['test_avg'] ?? null,
            'ptap_score' => $row['ptap_score'] ?? null,
            'attendance_score' => $row['attendance_score'] ?? null,
            'midterm_exam_score' => $row['midterm_exam_score'] ?? null,
            'semester_score' => $row['semester_score'] ?? null,
        ];

        $emailData = [
            'title'              => $subject,
            'parent_name'        => $parentName,
            'student_name'       => $studentName !== '' ? $studentName : 'your student',
            'class_section_name' => $row['class_section_name'] ?? '',
            'semester'           => $semester,
            'school_year'        => $schoolYear,
            'scores'             => $scores,
            'comment'            => $row['comment'] ?? '',
            'sent_at'            => utc_now(),
        ];

        $html = view('emails/below_sixty_performance', $emailData, ['saveData' => true]);

        return view('grading/below_sixty_email_editor', [
            'studentId' => $studentId,
            'studentName' => $studentName,
            'semester' => $semester,
            'schoolYear' => $schoolYear,
            'subject' => $subject,
            'html' => $html,
        ]);
    }

    public function sendBelowSixtyEmail()
    {
        $studentId = (int)$this->request->getPost('student_id');
        $semester = trim((string)$this->request->getPost('semester'));
        $schoolYear = trim((string)$this->request->getPost('school_year'));
        $subjectInput = trim((string)$this->request->getPost('subject'));
        $htmlInput = (string)($this->request->getPost('html') ?? '');

        if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing student or term.');
        }

        $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
        if (empty($row)) {
            return redirect()->back()->with('error', 'Student record not found for the selected term.');
        }

        $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        $subject = $subjectInput !== ''
            ? $subjectInput
            : $this->buildBelowSixtySubject($studentName, $semester, $schoolYear);
        $scores = [
            'homework_avg' => $row['homework_avg'] ?? null,
            'project_avg' => $row['project_avg'] ?? null,
            'participation_score' => $row['participation_score'] ?? null,
            'test_avg' => $row['test_avg'] ?? null,
            'ptap_score' => $row['ptap_score'] ?? null,
            'attendance_score' => $row['attendance_score'] ?? null,
            'midterm_exam_score' => $row['midterm_exam_score'] ?? null,
            'semester_score' => $row['semester_score'] ?? null,
        ];

        $payload = [
            'student_id' => $studentId,
            'student_name' => $studentName,
            'class_section_name' => $row['class_section_name'] ?? '',
            'semester' => $semester,
            'school_year' => $schoolYear,
            'scores' => $scores,
            'comment' => $row['comment'] ?? '',
            'subject' => $subject,
        ];

        if (trim($htmlInput) !== '') {
            $payload['html'] = $htmlInput;
        }

        Events::trigger('below60.email', $payload);

        return redirect()->back()->with('status', 'Email sent to parent(s).');
    }

    public function updateBelowSixtyStatus()
    {
        $studentId = (int)$this->request->getPost('student_id');
        $semester = trim((string)$this->request->getPost('semester'));
        $schoolYear = trim((string)$this->request->getPost('school_year'));
        $status = trim((string)$this->request->getPost('status'));
        $note = trim((string)$this->request->getPost('note'));

        if ($studentId <= 0 || $semester === '' || $schoolYear === '' || $status === '') {
            return redirect()->back()->with('error', 'Missing required status data.');
        }

        $status = ucfirst(strtolower($status));
        if (!in_array($status, ['Open', 'Closed'], true)) {
            return redirect()->back()->with('error', 'Invalid status.');
        }

        $flagModel = new CurrentFlagModel();
        $semKey = strtolower(trim($semester));
        $redirectUrl = base_url('grading/below-60');
        $query = http_build_query([
            'semester' => $semester,
            'school_year' => $schoolYear,
        ]);
        if ($query !== '') {
            $redirectUrl .= '?' . $query;
        }

        $existing = $flagModel
            ->where('student_id', $studentId)
            ->where('flag', 'grade')
            ->where('school_year', $schoolYear)
            ->where('LOWER(TRIM(semester))', $semKey)
            ->first();

        $userId = (int)(session()->get('user_id') ?? 0) ?: null;
        $now = utc_now();
        $ok = true;

        if ($existing) {
            $data = [
                'flag_state' => $status,
                'flag_datetime' => $now,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'updated_at' => $now,
            ];
            if ($status === 'Open') {
                $data['updated_by_open'] = $userId;
                if ($note !== '') {
                    $prev = (string)($existing['open_description'] ?? '');
                    $data['open_description'] = trim($prev . PHP_EOL . $note);
                }
            } else {
                $data['updated_by_closed'] = $userId;
                if ($note !== '') {
                    $prev = (string)($existing['close_description'] ?? '');
                    $data['close_description'] = trim($prev . PHP_EOL . $note);
                }
            }
            $ok = (bool) $flagModel->update((int)$existing['id'], $data);
        } else {
            $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
            $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
            $grade = (string)($row['class_section_name'] ?? '');
            $data = [
                'student_id' => $studentId,
                'student_name' => $studentName !== '' ? $studentName : 'Student',
                'grade' => $grade,
                'flag' => 'grade',
                'flag_datetime' => $now,
                'flag_state' => $status,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'updated_at' => $now,
            ];
            if ($status === 'Open') {
                $data['updated_by_open'] = $userId;
                if ($note !== '') $data['open_description'] = $note;
            } else {
                $data['updated_by_closed'] = $userId;
                if ($note !== '') $data['close_description'] = $note;
            }
            $ok = (bool) $flagModel->insert($data);
        }

        if (!$ok) {
            log_message('error', 'updateBelowSixtyStatus failed', [
                'student_id' => $studentId,
                'semester' => $semester,
                'school_year' => $schoolYear,
                'status' => $status,
                'errors' => $flagModel->errors(),
            ]);
            return redirect()->to($redirectUrl)->with('error', 'Failed to update status.');
        }

        return redirect()->to($redirectUrl)->with('status', 'Status updated.');
    }

    public function scheduleBelowSixty()
    {
        $studentId = (int)($this->request->getGet('student_id') ?? 0);
        $semester = trim((string)($this->request->getGet('semester') ?? ''));
        $schoolYear = trim((string)($this->request->getGet('school_year') ?? ''));

        if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing student or term.');
        }

        $context = $this->fetchBelowSixtyMeetingContext($studentId, $schoolYear, $semester);
        if (empty($context)) {
            return redirect()->back()->with('error', 'Student record not found for the selected term.');
        }

        return view('grading/schedule_meeting', [
            'studentId' => $studentId,
            'studentName' => $context['student_name'],
            'parentName' => $context['parent_name'],
            'classSection' => $context['class_section_name'],
            'semester' => $semester,
            'schoolYear' => $schoolYear,
        ]);
    }

    public function saveBelowSixtyMeeting()
    {
        $studentId = (int)$this->request->getPost('student_id');
        $semester = trim((string)$this->request->getPost('semester'));
        $schoolYear = trim((string)$this->request->getPost('school_year'));
        $date = trim((string)$this->request->getPost('date'));
        $time = trim((string)$this->request->getPost('time'));
        $notes = trim((string)$this->request->getPost('notes'));

        if ($studentId <= 0 || $semester === '' || $schoolYear === '' || $date === '') {
            return redirect()->back()->withInput()->with('error', 'Missing required fields.');
        }

        $context = $this->fetchBelowSixtyMeetingContext($studentId, $schoolYear, $semester);
        if (empty($context)) {
            return redirect()->back()->withInput()->with('error', 'Student record not found for the selected term.');
        }

        $studentName = $context['student_name'];
        $parentName = $context['parent_name'];
        $classSection = $context['class_section_name'];
        $timeLabel = $time !== '' ? (' ' . $time) : '';

        $title = 'Parent Meeting: ' . $parentName . ' — ' . $studentName;
        if ($time !== '') {
            $title .= ' (' . $time . ')';
        }

        $descriptionParts = [];
        $descriptionParts[] = 'Student: ' . $studentName;
        $descriptionParts[] = 'Parent: ' . $parentName;
        if ($classSection !== '') {
            $descriptionParts[] = 'Section: ' . $classSection;
        }
        $descriptionParts[] = 'Date/Time: ' . $date . $timeLabel;
        if ($notes !== '') {
            $descriptionParts[] = 'Notes: ' . $notes;
        }
        $description = implode("\n", $descriptionParts);

        $data = [
            'student_id' => $studentId,
            'parent_user_id' => $context['parent_user_id'] ?? null,
            'parent_name' => $parentName,
            'student_name' => $studentName,
            'class_section_name' => $classSection,
            'date' => $date,
            'time' => $time !== '' ? $time : null,
            'notes' => $notes !== '' ? $notes : null,
            'semester' => $semester,
            'school_year' => $schoolYear,
            'status' => 'scheduled',
            'created_by' => (int)(session()->get('user_id') ?? 0) ?: null,
        ];

        $ok = $this->parentMeetingModel->insert($data);
        if ($ok) {
            $query = http_build_query([
                'semester' => $semester,
                'school_year' => $schoolYear,
            ]);
            return redirect()->to(base_url('grading/below-60') . ($query ? ('?' . $query) : ''))
                ->with('status', 'Meeting scheduled and added to calendars.');
        }

        return redirect()->back()->withInput()->with('error', 'Failed to schedule the meeting.');
    }

    public function toggleParentScoresRelease()
    {
        $semester = (string) ($this->request->getPost('semester') ?? '');
        if ($semester === '') {
            $semester = (string) (session()->get('grading_selected_semester') ?? $this->semester);
        }
        $configKey = $this->getParentReleaseKey($semester) ?? 'parent_scores_released';

        $releaseScoresRaw = (string) ($this->configModel->getConfig($configKey) ?? '');
        $scoresReleased = in_array(strtolower(trim($releaseScoresRaw)), ['1', 'true', 'yes', 'y', 'on'], true);
        $nextValue = $scoresReleased ? '0' : '1';

        $ok = $this->configModel->setConfigValueByKey($configKey, $nextValue);
        log_message('info', 'toggleParentScoresRelease', [
            'semester' => $semester,
            'config_key' => $configKey,
            'prev' => $releaseScoresRaw,
            'next' => $nextValue,
            'ok' => $ok,
        ]);

        if ($ok) {
            $msg = $scoresReleased
                ? 'Parent exam/semester scores are now hidden.'
                : 'Parent exam/semester scores are now released.';
            $msg .= ' (' . $configKey . '=' . $nextValue . ')';
            return redirect()->back()->with('status', $msg);
        }

        return redirect()->back()->with('error', 'Unable to update the scores release flag.');
    }

    private function getParentReleaseKey(string $semester): ?string
    {
        $norm = strtolower(trim((string) $semester));
        if ($norm === 'fall') {
            return 'parent_scores_released_fall';
        }
        if ($norm === 'spring') {
            return 'parent_scores_released_spring';
        }
        return null;
    }

    private function getParentScoresReleasedForSemester(string $semester): bool
    {
        $key = $this->getParentReleaseKey($semester);
        $raw = $key ? $this->configModel->getConfig($key) : null;
        $raw = (string) ($raw ?? '');
        return in_array(strtolower(trim($raw)), ['1', 'true', 'yes', 'y', 'on'], true);
    }

    private function ensureParentReleaseKeyExists(string $semester): void
    {
        $key = $this->getParentReleaseKey($semester);
        if (!$key) {
            return;
        }
        if ($this->configModel->getConfigValueByKey($key) === null) {
            $this->configModel->setConfigValueByKey($key, '0');
        }
    }

    public function refreshSemesterScores()
    {
        $classSectionId = (int) ($this->request->getPost('class_section_id') ?? 0);
        if ($classSectionId <= 0) {
            return redirect()->back()->with('error', 'Missing class section.');
        }

        $requestedSemester = (string) ($this->request->getPost('semester') ?? '');
        $requestedYear = (string) ($this->request->getPost('school_year') ?? '');
        $semester = $this->normalizeSemesterInput($requestedSemester) ?? $requestedSemester;
        if ($semester === '') {
            $semester = (string) $this->semester;
        }
        $schoolYear = trim($requestedYear) !== '' ? trim($requestedYear) : (string) $this->schoolYear;

        $studentTeacherInfo = $this->studentModel->getStudentInfoByClassSectionId(
            $classSectionId,
            $semester,
            $schoolYear
        );
        if (empty($studentTeacherInfo)) {
            return redirect()->back()->with('error', 'No students found for this class/term.');
        }

        try {
            $this->semesterScoreService->updateScoresForStudents($studentTeacherInfo, $semester, $schoolYear);
            $this->refreshAttendanceComments($classSectionId, $semester, $schoolYear);
        } catch (\Throwable $e) {
            log_message('error', 'refreshSemesterScores failed: ' . $e->getMessage());
            return redirect()->back()->with('error', 'Refresh failed. Check logs.');
        }

        return redirect()->back()->with('status', 'Semester scores refreshed for this class/term.');
    }

    private function refreshAttendanceComments(int $classSectionId, string $semester, string $schoolYear): void
    {
        helper('attendance_comment');

        $scoreModel = new SemesterScoreModel();
        $commentModel = new ScoreCommentModel();

        $scoreRows = $scoreModel
            ->select(['student_id', 'attendance_score'])
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->findAll();

        if (empty($scoreRows)) {
            return;
        }

        $studentIds = array_values(array_unique(array_map(
            static fn($row) => (int) ($row['student_id'] ?? 0),
            $scoreRows
        )));
        $studentIds = array_values(array_filter($studentIds, static fn($id) => $id > 0));
        if (empty($studentIds)) {
            return;
        }

        $students = $this->studentModel
            ->select(['id', 'firstname'])
            ->whereIn('id', $studentIds)
            ->findAll();
        $nameMap = [];
        foreach ($students as $st) {
            $nameMap[(int)$st['id']] = (string) ($st['firstname'] ?? '');
        }

        $existing = $commentModel
            ->where('score_type', 'attendance')
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->whereIn('student_id', $studentIds)
            ->findAll();
        $existingByStudent = [];
        foreach ($existing as $row) {
            $existingByStudent[(int)$row['student_id']] = $row;
        }

        foreach ($scoreRows as $row) {
            $sid = (int) ($row['student_id'] ?? 0);
            if ($sid <= 0) {
                continue;
            }
            $score = isset($row['attendance_score']) ? (float) $row['attendance_score'] : null;
            if ($score === null) {
                continue;
            }
            $auto = attendance_comment_from_score($score, $nameMap[$sid] ?? '');
            if ($auto === null) {
                continue;
            }

            if (isset($existingByStudent[$sid])) {
                $commentModel->update($existingByStudent[$sid]['id'], [
                    'comment' => $auto,
                ]);
            } else {
                $commentModel->insert([
                    'student_id'       => $sid,
                    'class_section_id' => $classSectionId,
                    'score_type'       => 'attendance',
                    'semester'         => $semester,
                    'school_year'      => $schoolYear,
                    'comment'          => $auto,
                    'commented_by'     => null,
                    'created_at'       => utc_now(),
                ]);
            }
        }
    }

    /**
     * Build grading rows with PTAP and semester scores (business + pk join).
     *
     * @param string $semEsc Escaped semester string for SQL
     * @param string $yrEsc  Escaped school year string for SQL
     * @param string $schoolYear Raw school year value for filtering student_class
     * @return array
     */
    private function buildGradingRows(string $semEsc, string $yrEsc, string $schoolYear, string $semesterRaw): array
    {
        $builder = $this->db->table('student_class sc')
            ->select([
                'cs.id                         AS section_pk',
                'cs.class_section_id           AS section_id',      // BUSINESS id used in URLs
                'cs.class_id                   AS class_id',        // 13=KG, 1..11=Grade N, 12=Youth
                'cs.class_section_name',
                's.id                          AS student_id',
                's.school_id',
                's.firstname',
                's.lastname',
                'pl.level                    AS placement_level',

                // Prefer business-id match; fall back to pk match
                'COALESCE(ss_b.ptap_score, ss_p.ptap_score)          AS ss_ptap_score',
                'COALESCE(ss_b.semester_score, ss_p.semester_score)  AS ss_semester_score',
                'COALESCE(ss_b.attendance_score, ss_p.attendance_score) AS ss_attendance_score',
                'COALESCE(ss_b.homework_avg, ss_p.homework_avg)      AS ss_homework_avg',
                'COALESCE(ss_b.project_avg, ss_p.project_avg)        AS ss_project_avg',
                'COALESCE(ss_b.quiz_avg, ss_p.quiz_avg)              AS ss_quiz_avg',
                'COALESCE(ss_b.participation_score, ss_p.participation_score) AS ss_participation_score',
                'COALESCE(ss_b.midterm_exam_score, ss_p.midterm_exam_score)   AS ss_midterm_exam_score',
                'COALESCE(ss_b.final_exam_score, ss_p.final_exam_score)       AS ss_final_exam_score',
                // helpful to debug what matched:
                'ss_b.class_section_id        AS matched_biz_csid',
                'ss_p.class_section_id        AS matched_pk_csid'
            ])
            ->distinct()
            ->join('`classSection` cs', 'cs.class_section_id = sc.class_section_id', 'left')
            ->join('students s', 's.id = sc.student_id', 'inner')
            ->join(
                'placement_levels pl',
                'pl.student_id = s.id',
                'left'
            )

            // business-id join
            ->join(
                'semester_scores ss_b',
                "ss_b.student_id = s.id
             AND ss_b.class_section_id = sc.class_section_id
             AND LOWER(ss_b.semester) = LOWER(TRIM({$semEsc}))
             AND ss_b.school_year = {$yrEsc}",
                'left'
            )
            // pk join
            ->join(
                'semester_scores ss_p',
                "ss_p.student_id = s.id
             AND ss_p.class_section_id = cs.id
             AND LOWER(ss_p.semester) = LOWER(TRIM({$semEsc}))
             AND ss_p.school_year = {$yrEsc}",
                'left'
            )
            ->where('sc.school_year', $schoolYear)
            ->where('s.is_active', 1); // Exclude removed/inactive students

        return $builder
            ->orderBy('cs.class_id', 'ASC')
            ->orderBy('cs.class_section_name', 'ASC')
            ->orderBy('s.lastname', 'ASC')
            ->orderBy('s.firstname', 'ASC')
            ->get()->getResultArray();
    }

    private function calculateAttendanceScoreForStudent(int $studentId, string $semester, string $schoolYear, ?int $classSectionId = null): ?float
    {
        try {
            $result = $this->attendanceCalculator->calculate($studentId, $semester, $schoolYear, $classSectionId);
            $score = $result['attendance_score'] ?? null;
            if ($score === null || $score === '') {
                return null;
            }
            return round((float) $score, 2);
        } catch (\Throwable $e) {
            log_message(
                'error',
                'GradingController::calculateAttendanceScoreForStudent failed for '
                . "student {$studentId}: " . $e->getMessage()
            );
        }
        return null;
    }

    private function normalizeSemesterInput(?string $semester): ?string
    {
        if (!is_string($semester)) {
            return null;
        }
        $trimmed = trim($semester);
        return $trimmed === '' ? null : $trimmed;
    }

    private function resolveSemesterSelection(?string $requestedSemester, array $semesterOptions, ?string $fallbackSemester): string
    {
        if ($requestedSemester !== null) {
            foreach ($semesterOptions as $option) {
                if (strcasecmp($option, $requestedSemester) === 0) {
                    return $option;
                }
            }
            return $requestedSemester;
        }
        if ($fallbackSemester !== null && $fallbackSemester !== '') {
            foreach ($semesterOptions as $option) {
                if (strcasecmp($option, $fallbackSemester) === 0) {
                    return $option;
                }
            }
            return $fallbackSemester;
        }
        return $semesterOptions[0] ?? '';
    }

    private function getSemestersForSchoolYear(string $schoolYear, ?string $fallbackSemester = null): array
    {
        $rows = $this->db->table('semester_scores')
            ->select('DISTINCT semester', false)
            ->where('semester IS NOT NULL', null, false)
            ->where('semester != ""', null, false)
            ->where('school_year', $schoolYear)
            ->orderBy('semester', 'ASC')
            ->get()
            ->getResultArray();

        $semesters = [];
        foreach ($rows as $row) {
            $value = trim((string) ($row['semester'] ?? ''));
            if ($value === '') continue;
            $semesters[] = $value;
        }

        if (empty($semesters)) {
            $semesters = ['Fall', 'Spring'];
        }

        if ($fallbackSemester !== null && $fallbackSemester !== '' && !in_array($fallbackSemester, $semesters, true)) {
            array_unshift($semesters, $fallbackSemester);
        }

        return array_values(array_unique($semesters));
    }

    private function getSchoolYearsForScores(?string $fallback = null): array
    {
        $schoolYears = [];
        try {
            $rows = $this->db->table('semester_scores')
                ->select('DISTINCT school_year', false)
                ->where('school_year IS NOT NULL', null, false)
                ->where('school_year != ""', null, false)
                ->orderBy('school_year', 'DESC')
                ->get()
                ->getResultArray();
            foreach ($rows as $row) {
                $val = (string)($row['school_year'] ?? '');
                if ($val !== '') $schoolYears[] = $val;
            }
        } catch (\Throwable $e) {
        }
        try {
            $rows2 = $this->db->table('student_class')
                ->select('DISTINCT school_year', false)
                ->where('school_year IS NOT NULL', null, false)
                ->where('school_year != ""', null, false)
                ->orderBy('school_year', 'DESC')
                ->get()
                ->getResultArray();
            foreach ($rows2 as $row) {
                $val = (string)($row['school_year'] ?? '');
                if ($val !== '' && !in_array($val, $schoolYears, true)) $schoolYears[] = $val;
            }
        } catch (\Throwable $e) {
        }
        if ($fallback && !in_array($fallback, $schoolYears, true)) {
            array_unshift($schoolYears, $fallback);
        }
        return array_values(array_unique($schoolYears));
    }

    private function fetchBelowSixtyRows(string $schoolYear, string $semester): array
    {
        $isYearMode = strtolower(trim($semester)) === 'year';

        if ($isYearMode) {
            $rows = $this->db->table('semester_scores ss')
                ->select('s.id AS student_id')
                ->select('s.school_id')
                ->select('s.firstname')
                ->select('s.lastname')
                ->select('cs.class_section_name')
                ->select("'year' AS semester", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.homework_avg END) AS fall_homework_avg", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.homework_avg END) AS spring_homework_avg", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.project_avg END) AS fall_project_avg", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.project_avg END) AS spring_project_avg", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.participation_score END) AS fall_participation_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.participation_score END) AS spring_participation_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN COALESCE(ss.test_avg, ss.quiz_avg) END) AS fall_test_avg", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN COALESCE(ss.test_avg, ss.quiz_avg) END) AS spring_test_avg", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.ptap_score END) AS fall_ptap_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.ptap_score END) AS spring_ptap_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.attendance_score END) AS fall_attendance_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.attendance_score END) AS spring_attendance_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.midterm_exam_score END) AS fall_midterm_exam_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.midterm_exam_score END) AS spring_midterm_exam_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.final_exam_score END) AS fall_final_exam_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.final_exam_score END) AS spring_final_exam_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.semester_score END) AS fall_score", false)
                ->select("MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.semester_score END) AS spring_score", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.semester_score END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.semester_score END)
                ) / 2 AS semester_score", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.homework_avg END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.homework_avg END)
                ) / 2 AS homework_avg", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.project_avg END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.project_avg END)
                ) / 2 AS project_avg", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.participation_score END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.participation_score END)
                ) / 2 AS participation_score", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN COALESCE(ss.test_avg, ss.quiz_avg) END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN COALESCE(ss.test_avg, ss.quiz_avg) END)
                ) / 2 AS test_avg", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.ptap_score END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.ptap_score END)
                ) / 2 AS ptap_score", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.attendance_score END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.attendance_score END)
                ) / 2 AS attendance_score", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.midterm_exam_score END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.midterm_exam_score END)
                ) / 2 AS midterm_exam_score", false)
                ->select("(
                    MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'fall' THEN ss.final_exam_score END)
                    + MAX(CASE WHEN LOWER(TRIM(ss.semester)) = 'spring' THEN ss.final_exam_score END)
                ) / 2 AS final_exam_score", false)
                ->join('students s', 's.id = ss.student_id', 'inner')
                ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
                ->where('s.is_active', 1)
                ->where('ss.school_year', $schoolYear)
                ->where('ss.semester_score IS NOT NULL', null, false)
                ->where("LOWER(TRIM(ss.semester)) IN ('fall', 'spring')", null, false)
                ->groupBy('s.id, s.school_id, s.firstname, s.lastname, ss.class_section_id, cs.class_section_name')
                ->having('fall_score IS NOT NULL', null, false)
                ->having('spring_score IS NOT NULL', null, false)
                ->having('semester_score <', 60)
                ->orderBy('s.lastname', 'ASC')
                ->orderBy('s.firstname', 'ASC')
                ->get()
                ->getResultArray();

            foreach ($rows as &$row) {
                $row['comment'] = '';
                $row['status'] = 'Open';
                $row['note'] = '';
            }
            unset($row);

            return $rows;
        }

        $semesterKey = 'fall';
        $builder = $this->db->table('semester_scores ss')
            ->select([
                's.id AS student_id',
                's.school_id',
                's.firstname',
                's.lastname',
                'cs.class_section_name',
                'ss.semester',
                'ss.homework_avg',
                'ss.project_avg',
                'ss.participation_score',
                'COALESCE(ss.test_avg, ss.quiz_avg) AS test_avg',
                'ss.ptap_score',
                'ss.attendance_score',
                'ss.midterm_exam_score',
                'ss.final_exam_score',
                'ss.semester_score',
            ])
            ->join('students s', 's.id = ss.student_id', 'inner')
            ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
            ->where('s.is_active', 1)
            ->where('ss.school_year', $schoolYear)
            ->where('ss.semester_score IS NOT NULL', null, false)
            ->where('ss.semester_score <', 60)
            ->where("LOWER(TRIM(ss.semester))", $semesterKey);

        $rows = $builder
            ->orderBy('s.lastname', 'ASC')
            ->orderBy('s.firstname', 'ASC')
            ->orderBy('ss.semester', 'ASC')
            ->get()
            ->getResultArray();

        if (empty($rows)) {
            return [];
        }

        $studentIds = array_values(array_unique(array_map(
            static fn($r) => (int)($r['student_id'] ?? 0),
            $rows
        )));
        $studentIds = array_values(array_filter($studentIds, static fn($id) => $id > 0));

        $commentMap = [];
        if (!empty($studentIds)) {
            $commentRows = $this->db->table('score_comments')
                ->select('student_id, semester, comment, created_at')
                ->where('score_type', 'general')
                ->where('school_year', $schoolYear)
                ->whereIn('student_id', $studentIds)
                ->where("LOWER(TRIM(semester))", $semesterKey)
                ->orderBy('created_at', 'DESC')
                ->get()
                ->getResultArray();
            foreach ($commentRows as $row) {
                $sid = (int)($row['student_id'] ?? 0);
                $sem = strtolower(trim((string)($row['semester'] ?? '')));
                $key = $sid . '_' . $sem;
                if ($sid > 0 && !isset($commentMap[$key])) {
                    $commentMap[$key] = (string)($row['comment'] ?? '');
                }
            }
        }

        $statusMap = [];
        $noteMap = [];
        if (!empty($studentIds)) {
            $flagRows = $this->db->table('current_flag')
                ->select('student_id, semester, flag_state, open_description, close_description')
                ->where('flag', 'grade')
                ->where('school_year', $schoolYear)
                ->whereIn('student_id', $studentIds)
                ->where("LOWER(TRIM(semester))", $semesterKey)
                ->get()
                ->getResultArray();
            foreach ($flagRows as $row) {
                $sid = (int)($row['student_id'] ?? 0);
                if ($sid <= 0) continue;
                $sem = strtolower(trim((string)($row['semester'] ?? '')));
                $key = $sid . '_' . $sem;
                $statusMap[$key] = (string)($row['flag_state'] ?? '');
                $openNote = trim((string)($row['open_description'] ?? ''));
                $closeNote = trim((string)($row['close_description'] ?? ''));
                $noteMap[$key] = [
                    'open' => $openNote,
                    'closed' => $closeNote,
                ];
            }
        }

        foreach ($rows as &$row) {
            $sid = (int)($row['student_id'] ?? 0);
            $sem = strtolower(trim((string)($row['semester'] ?? '')));
            $key = $sid . '_' . $sem;
            $row['comment'] = $commentMap[$key] ?? '';
            $flagState = strtolower(trim((string)($statusMap[$key] ?? '')));
            $row['status'] = ($flagState === 'closed' || $flagState === 'canceled') ? 'Closed' : 'Open';
            $noteBag = $noteMap[$key] ?? ['open' => '', 'closed' => ''];
            $rawNote = $row['status'] === 'Closed' ? (string)$noteBag['closed'] : (string)$noteBag['open'];
            if ($rawNote !== '') {
                $lines = preg_split('/\R/', $rawNote);
                $lines = array_values(array_filter(array_map('trim', $lines), static fn($val) => $val !== ''));
                $row['note'] = $lines ? end($lines) : '';
            } else {
                $row['note'] = '';
            }
        }
        unset($row);

        return $rows;
    }


    private function fetchBelowSixtyEmailRow(int $studentId, string $schoolYear, string $semester): array
    {
        $semesterKey = strtolower(trim($semester));
        $row = $this->db->table('semester_scores ss')
            ->select([
                's.id AS student_id',
                's.firstname',
                's.lastname',
                'cs.class_section_name',
                'ss.homework_avg',
                'ss.project_avg',
                'ss.participation_score',
                'COALESCE(ss.test_avg, ss.quiz_avg) AS test_avg',
                'ss.ptap_score',
                'ss.attendance_score',
                'ss.midterm_exam_score',
                'ss.semester_score',
            ])
            ->join('students s', 's.id = ss.student_id', 'inner')
            ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
            ->where('ss.school_year', $schoolYear)
            ->where('ss.student_id', $studentId)
            ->where('s.is_active', 1)
            ->where("LOWER(TRIM(ss.semester))", $semesterKey)
            ->get()
            ->getRowArray();

        if (!$row) return [];

        $commentRow = $this->db->table('score_comments')
            ->select('comment')
            ->where('score_type', 'general')
            ->where('school_year', $schoolYear)
            ->where("LOWER(TRIM(semester))", $semesterKey)
            ->where('student_id', $studentId)
            ->orderBy('created_at', 'DESC')
            ->get()
            ->getRowArray();
        $row['comment'] = (string)($commentRow['comment'] ?? '');

        return $row;
    }

    private function userHasMenuUrl(string $needle): bool
    {
        $needle = strtolower(trim($needle));
        if ($needle === '') {
            return false;
        }

        $rawRole = session()->get('role');
        $roles = is_array($rawRole) ? $rawRole : [$rawRole ?? 'guest'];
        $roles = array_values(array_filter(array_map('strval', $roles)));
        if (empty($roles)) {
            return false;
        }

        $service = new NavbarService();
        $menu = $service->getMenuForRoles($roles);
        if (empty($menu)) {
            return false;
        }

        $normalize = static function (string $url) use ($needle): string {
            $url = strtolower(trim($url));
            if ($url === '') return '';
            $url = preg_replace('#^https?://[^/]+/#i', '', $url);
            $url = ltrim($url, '/');
            return $url;
        };

        $target = $normalize($needle);
        $stack = $menu;
        while (!empty($stack)) {
            $node = array_shift($stack);
            if (!empty($node['url'])) {
                $url = $normalize((string)$node['url']);
                if ($url !== '' && $url === $target) {
                    return true;
                }
            }
            if (!empty($node['children']) && is_array($node['children'])) {
                foreach ($node['children'] as $child) {
                    $stack[] = $child;
                }
            }
        }

        return false;
    }

    private function fetchAllSemestersForStudent(int $studentId, string $schoolYear): array
    {
        $rows = $this->db->table('semester_scores ss')
            ->select([
                'ss.semester',
                'cs.class_section_name',
                'ss.homework_avg',
                'ss.project_avg',
                'ss.participation_score',
                'COALESCE(ss.test_avg, ss.quiz_avg) AS test_avg',
                'ss.ptap_score',
                'ss.attendance_score',
                'ss.midterm_exam_score',
                'ss.final_exam_score',
                'ss.semester_score',
            ])
            ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
            ->where('ss.student_id', $studentId)
            ->where('ss.school_year', $schoolYear)
            ->orderBy('ss.semester', 'ASC')
            ->get()
            ->getResultArray();

        $semesters = [];
        foreach ($rows as $sr) {
            $sem = ucfirst(strtolower(trim((string)($sr['semester'] ?? ''))));
            $semesters[$sem] = [
                'semester'            => $sem,
                'class_section_name'  => $sr['class_section_name'] ?? '',
                'homework_avg'        => $sr['homework_avg'] ?? null,
                'project_avg'         => $sr['project_avg'] ?? null,
                'participation_score' => $sr['participation_score'] ?? null,
                'test_avg'            => $sr['test_avg'] ?? null,
                'ptap_score'          => $sr['ptap_score'] ?? null,
                'attendance_score'    => $sr['attendance_score'] ?? null,
                'midterm_exam_score'  => $sr['midterm_exam_score'] ?? null,
                'final_exam_score'    => $sr['final_exam_score'] ?? null,
                'semester_score'      => $sr['semester_score'] ?? null,
                'comments'            => [],
            ];
        }

        if (!empty($semesters)) {
            $commentRows = $this->db->table('score_comments')
                ->select('semester, score_type, comment, created_at')
                ->where('student_id', $studentId)
                ->where('school_year', $schoolYear)
                ->where('comment IS NOT NULL', null, false)
                ->where('comment !=', '')
                ->orderBy('semester', 'ASC')
                ->orderBy('score_type', 'ASC')
                ->orderBy('created_at', 'DESC')
                ->get()
                ->getResultArray();

            foreach ($commentRows as $c) {
                $sem  = ucfirst(strtolower(trim((string)($c['semester'] ?? ''))));
                $type = strtolower(trim((string)($c['score_type'] ?? 'general')));
                if (isset($semesters[$sem]) && !isset($semesters[$sem]['comments'][$type])) {
                    $semesters[$sem]['comments'][$type] = (string)($c['comment'] ?? '');
                }
            }
        }

        return array_values($semesters);
    }

    private function fetchBelowSixtyParentName(int $studentId): string
    {
        $parentName = 'Parent/Guardian';
        try {
            $rows = $this->db->query(
                "SELECT u.firstname, u.lastname
                   FROM family_students fs
                   JOIN family_guardians fg ON fg.family_id = fs.family_id
                   JOIN users u ON u.id = fg.user_id
                  WHERE fs.student_id = ?
                  ORDER BY fg.is_primary DESC, u.lastname, u.firstname
                  LIMIT 1",
                [$studentId]
            )->getResultArray();
            if (!empty($rows[0])) {
                $candidate = trim((string)($rows[0]['firstname'] ?? '') . ' ' . (string)($rows[0]['lastname'] ?? ''));
                if ($candidate !== '') {
                    $parentName = $candidate;
                }
            }
        } catch (\Throwable $e) {
        }
        return $parentName;
    }

    private function buildBelowSixtySubject(string $studentName, string $semester, string $schoolYear): string
    {
        $subject = 'Student Performance Alert';
        if ($studentName !== '') {
            $subject .= ' — ' . $studentName;
        }
        if ($semester !== '' || $schoolYear !== '') {
            $subject .= ' (' . trim($semester . ' ' . $schoolYear) . ')';
        }
        return $subject;
    }

    private function fetchBelowSixtyMeetingContext(int $studentId, string $schoolYear, string $semester): array
    {
        $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
        if (empty($row)) {
            return [];
        }

        $db = $this->db;
        $parentName = 'Parent/Guardian';
        $parentUserId = null;
        try {
            $pRows = $db->query(
                "SELECT u.id AS user_id, u.firstname, u.lastname
                   FROM family_students fs
                   JOIN family_guardians fg ON fg.family_id = fs.family_id
                   JOIN users u ON u.id = fg.user_id
                  WHERE fs.student_id = ?
                  ORDER BY fg.is_primary DESC, u.lastname, u.firstname
                  LIMIT 1",
                [$studentId]
            )->getResultArray();
            if (!empty($pRows[0])) {
                $parentUserId = (int)($pRows[0]['user_id'] ?? 0) ?: null;
                $parentName = trim((string)($pRows[0]['firstname'] ?? '') . ' ' . (string)($pRows[0]['lastname'] ?? ''));
                if ($parentName === '') {
                    $parentName = 'Parent/Guardian';
                }
            }
        } catch (\Throwable $e) {
        }

        // Legacy fallback: students.parent_id
        if ($parentUserId === null) {
            try {
                $srow = $db->query(
                    "SELECT s.parent_id, u.firstname, u.lastname
                       FROM students s
                       LEFT JOIN users u ON u.id = s.parent_id
                      WHERE s.id = ?
                      LIMIT 1",
                    [$studentId]
                )->getRowArray();
                if (!empty($srow)) {
                    $parentUserId = (int)($srow['parent_id'] ?? 0) ?: null;
                    $fallbackName = trim((string)($srow['firstname'] ?? '') . ' ' . (string)($srow['lastname'] ?? ''));
                    if ($fallbackName !== '') {
                        $parentName = $fallbackName;
                    }
                }
            } catch (\Throwable $e) {
            }
        }

        $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        return [
            'student_name' => $studentName !== '' ? $studentName : 'Student',
            'parent_name' => $parentName,
            'parent_user_id' => $parentUserId,
            'class_section_name' => (string)($row['class_section_name'] ?? ''),
        ];
    }


    /**
     * Parses a classSection name into a (classId, label) pair compatible with your Attendance view.
     * - KG   -> (0, 'KG')
     * - Youth-> (13, 'Youth')
     * - Grade N -> (N, 'Grade N')
     * Fallback: tries to extract first number; else returns (99, original).
     */
    private function parseClassIdFromSectionName(string $name): array
    {
        $n = trim($name);

        // Common patterns used in your data
        if (preg_match('/\bKG\b/i', $n)) {
            return [0, 'KG'];
        }
        if (preg_match('/\bYouth\b/i', $n)) {
            return [13, 'Youth'];
        }
        if (preg_match('/Grade\s*(\d+)/i', $n, $m)) {
            $g = (int)$m[1];
            return [$g, 'Grade ' . $g];
        }

        // Fallback: any number present becomes the grade id
        if (preg_match('/(\d+)/', $n, $m2)) {
            $g = (int)$m2[1];
            return [$g, 'Grade ' . $g];
        }

        // Last fallback
        return [99, $n];
    }




    /**
     * Resolve class_section_id for a student in the current term.
     */
    private function resolveClassSectionIdForStudent(int $studentId): int
    {
        $row = $this->db->table('student_class')
            ->select('class_section_id')
            ->where('student_id', $studentId)
            ->where('school_year', $this->schoolYear)
            ->where('semester', $this->semester)
            ->get()->getRow();

        return $row ? (int)$row->class_section_id : 0;
    }

    /** Simple slugifier used as array keys for tabs */
    private function slugify(string $s): string
    {
        $s = strtolower($s);
        $s = preg_replace('/[^a-z0-9]+/i', '-', $s);
        return trim($s, '-');
    }

    /** Rank: KG first, numbers ascending, Youth last */
    private function rankOf(string $name): int
    {
        $k = strtolower(trim($name));
        if ($k === 'kg' || $k === 'kindergarten') return -100;
        if ($k === 'youth') return 100000;
        if (preg_match('/\d+/', $k, $m)) return (int)$m[0];
        return 50000;
    }

 public function belowSixtyDecisions()
{
    $configuredYear = (string) $this->schoolYear;

    $schoolYear = trim((string)($this->request->getGet('school_year') ?? ''));

    if ($schoolYear === '') {
        $schoolYear = $configuredYear;
    }

    // This page is whole-year only.
    $semester = 'year';

    $schoolYears = $this->getSchoolYearsForScores($schoolYear);

    $db = $this->db;

    /*
     * Whole-year score source:
     * Fall semester_score + Spring semester_score / 2
     *
     * Only students with BOTH Fall and Spring scores are included.
     * Only students with year_score < 60 are listed.
     */
    $scoreRows = $db->table('semester_scores ss')
        ->select([
            's.id AS student_id',
            's.firstname',
            's.lastname',
            's.age',
            's.school_id',
            'cs.class_section_name',
            'LOWER(TRIM(ss.semester)) AS sem_key',
            'ss.semester_score',
        ])
        ->join('students s', 's.id = ss.student_id', 'inner')
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->where('s.is_active', 1)
        ->where('ss.school_year', $schoolYear)
        ->whereIn('LOWER(TRIM(ss.semester))', ['fall', 'spring'])
        ->where('ss.semester_score IS NOT NULL', null, false)
        ->orderBy('cs.class_section_name', 'ASC')
        ->orderBy('s.lastname', 'ASC')
        ->orderBy('s.firstname', 'ASC')
        ->get()
        ->getResultArray();

    $studentMap = [];

    foreach ($scoreRows as $row) {
        $sid = (int)($row['student_id'] ?? 0);

        if ($sid <= 0) {
            continue;
        }

        if (!isset($studentMap[$sid])) {
            $studentMap[$sid] = [
                'student_id'         => $sid,
                'school_id'          => $row['school_id'] ?? '',
                'firstname'          => $row['firstname'] ?? '',
                'lastname'           => $row['lastname'] ?? '',
                'age'                => $row['age'] ?? null,
                'class_section_name' => $row['class_section_name'] ?? '',
                'fall_score'         => null,
                'spring_score'       => null,
                'year_score'         => null,
            ];
        }

        $semKey = strtolower(trim((string)($row['sem_key'] ?? '')));
        $score  = is_numeric($row['semester_score']) ? (float)$row['semester_score'] : null;

        if ($score === null) {
            continue;
        }

        if ($semKey === 'fall') {
            $studentMap[$sid]['fall_score'] = $score;
        } elseif ($semKey === 'spring') {
            $studentMap[$sid]['spring_score'] = $score;
        }
    }

    $rows = [];

    foreach ($studentMap as $sid => $student) {
        $fall   = $student['fall_score'];
        $spring = $student['spring_score'];

        // Whole-year result requires both semesters.
        if ($fall === null || $spring === null) {
            continue;
        }

        $yearScore = round(($fall + $spring) / 2, 2);

        if ($yearScore >= 60) {
            continue;
        }

        $student['year_score'] = $yearScore;
        $rows[$sid] = $student;
    }

    $studentIds = array_keys($rows);

    /*
     * Load saved below-60 manual decisions.
     * These are year-level decisions now.
     */
    $decisionMap = [];

    if (!empty($studentIds)) {
        $belowDecModel = new BelowSixtyDecisionModel();

        $decisionRows = $belowDecModel
            ->whereIn('student_id', $studentIds)
            ->where('semester', 'year')
            ->where('school_year', $schoolYear)
            ->findAll();

        foreach ($decisionRows as $d) {
            $sid = (int)($d['student_id'] ?? 0);

            if ($sid > 0) {
                $decisionMap[$sid] = $d;
            }
        }
    }

    foreach ($rows as $sid => &$row) {
        $row['decision']       = $decisionMap[$sid]['decision'] ?? '';
        $row['decision_notes'] = $decisionMap[$sid]['notes'] ?? '';
    }

    unset($row);

    /*
     * Load final generated whole-year decisions from student_decisions.
     * This table is now year-based, so do NOT filter by semester.
     */
    $finalDecisionMap = [];

    if (!empty($studentIds)) {
        $finalRows = $db->table('student_decisions')
            ->whereIn('student_id', $studentIds)
            ->where('school_year', $schoolYear)
            ->get()
            ->getResultArray();

        foreach ($finalRows as $fr) {
            $sid = (int)($fr['student_id'] ?? 0);

            if ($sid > 0) {
                $finalDecisionMap[$sid] = $fr;
            }
        }
    }

    foreach ($rows as $sid => &$row) {
        $row['consolidated_decision'] = $finalDecisionMap[$sid]['decision'] ?? null;

        if (
            isset($finalDecisionMap[$sid]['year_score'])
            && $finalDecisionMap[$sid]['year_score'] !== ''
            && is_numeric($finalDecisionMap[$sid]['year_score'])
        ) {
            $row['year_score'] = round((float)$finalDecisionMap[$sid]['year_score'], 2);
        }
    }

    unset($row);

    /*
     * Load certificate numbers.
     */
    $certMap = [];

    if (!empty($studentIds)) {
        $certRows = $db->table('certificate_records')
            ->select('student_id, certificate_number, issued_at')
            ->where('school_year', $schoolYear)
            ->whereIn('student_id', $studentIds)
            ->orderBy('issued_at', 'DESC')
            ->get()
            ->getResultArray();

        foreach ($certRows as $cr) {
            $sid = (int)($cr['student_id'] ?? 0);

            if ($sid > 0 && !isset($certMap[$sid])) {
                $certMap[$sid] = (string)($cr['certificate_number'] ?? '');
            }
        }
    }

    foreach ($rows as $sid => &$row) {
        $row['certificate_number'] = $certMap[$sid] ?? '';
    }

    unset($row);

    // Re-index for the view.
    $rows = array_values($rows);

    $canViewGrading = $this->userHasMenuUrl('grading');

    return view('grading/below_sixty_decisions', [
        'rows'           => $rows,
        'semester'       => $semester,
        'schoolYear'     => $schoolYear,
        'schoolYears'    => $schoolYears,
        'canViewGrading' => $canViewGrading,
    ]);
}

public function saveBelowSixtyDecision()
{
    $studentId  = (int)($this->request->getPost('student_id') ?? 0);
    $semester   = strtolower(trim((string)($this->request->getPost('semester') ?? 'year')));
    $schoolYear = trim((string)($this->request->getPost('school_year') ?? ''));
    $decision   = trim((string)($this->request->getPost('decision') ?? ''));
    $notes      = trim((string)($this->request->getPost('notes') ?? ''));

    if ($studentId <= 0 || $schoolYear === '') {
        return redirect()->back()->with('error', 'Missing student or school year.');
    }

    // This decision page should feed certificate decisions as whole-year decisions.
    // Force year mode here so certificate logic receives final year decision.
    $semester = 'year';

    $db = \Config\Database::connect();

    /*
     * 1. Save/update the manual decision in below_sixty_decisions.
     * This keeps your below-60 page history working.
     */
    $belowModel = new \App\Models\BelowSixtyDecisionModel();

    $existingBelow = $belowModel
        ->where('student_id', $studentId)
        ->where('semester', $semester)
        ->where('school_year', $schoolYear)
        ->first();

    $belowPayload = [
        'student_id'  => $studentId,
        'semester'    => $semester,
        'school_year' => $schoolYear,
        'decision'    => $decision,
        'notes'       => $notes,
    ];

    if ($existingBelow) {
        $belowModel->update((int)$existingBelow['id'], $belowPayload);
    } else {
        $belowModel->insert($belowPayload);
    }

    /*
     * 2. Calculate the student's whole-year score.
     * Certificate page uses student_decisions.year_score.
     */
    $scoreRows = $db->table('semester_scores ss')
        ->select([
            'LOWER(TRIM(ss.semester)) AS sem_key',
            'ss.semester_score',
            'cs.class_section_name',
        ])
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->where('ss.student_id', $studentId)
        ->where('ss.school_year', $schoolYear)
        ->whereIn('LOWER(TRIM(ss.semester))', ['fall', 'spring'])
        ->where('ss.semester_score IS NOT NULL', null, false)
        ->orderBy('ss.updated_at', 'DESC')
        ->orderBy('ss.id', 'DESC')
        ->get()
        ->getResultArray();

    $fallScore = null;
    $springScore = null;
    $classSectionName = null;

    foreach ($scoreRows as $sr) {
        $semKey = strtolower(trim((string)($sr['sem_key'] ?? '')));
        $score  = is_numeric($sr['semester_score'] ?? null) ? (float)$sr['semester_score'] : null;

        if ($classSectionName === null && !empty($sr['class_section_name'])) {
            $classSectionName = (string)$sr['class_section_name'];
        }

        if ($semKey === 'fall' && $fallScore === null) {
            $fallScore = $score;
        }

        if ($semKey === 'spring' && $springScore === null) {
            $springScore = $score;
        }
    }

    if ($fallScore !== null && $springScore !== null) {
        $yearScore = round(($fallScore + $springScore) / 2, 2);
    } elseif ($fallScore !== null) {
        $yearScore = round($fallScore, 2);
    } elseif ($springScore !== null) {
        $yearScore = round($springScore, 2);
    } else {
        $yearScore = null;
    }

    /*
     * 3. Sync into student_decisions.
     * This is the part your certificate page needs.
     *
     * student_decisions is now year-based:
     * - no semester
     * - no semester_score
     * - uses year_score
     */
    $source = $decision === '' ? 'pending' : 'manual';

    $studentDecisionPayload = [
        'student_id'          => $studentId,
        'school_year'         => $schoolYear,
        'class_section_name'  => $classSectionName,
        'year_score'          => $yearScore,
        'decision'            => $decision !== '' ? $decision : null,
        'source'              => $source,
        'notes'               => $notes !== '' ? $notes : null,
        'generated_by'        => (int)(session()->get('user_id') ?? 0) ?: null,
    ];

    $existingStudentDecision = $db->table('student_decisions')
        ->where('student_id', $studentId)
        ->where('school_year', $schoolYear)
        ->get()
        ->getRowArray();

    if ($existingStudentDecision) {
        $db->table('student_decisions')
            ->where('id', (int)$existingStudentDecision['id'])
            ->update($studentDecisionPayload);
    } else {
        $db->table('student_decisions')
            ->insert($studentDecisionPayload);
    }

    $query = http_build_query([
        'semester'    => 'year',
        'school_year' => $schoolYear,
    ]);

    return redirect()
        ->to(base_url('grading/below-60/decisions') . '?' . $query)
        ->with('status', 'Decision saved and certificate decision updated.');
}

    public function studentDecisionDetails()
    {
        $studentId  = (int)$this->request->getGet('student_id');
        $schoolYear = trim((string)$this->request->getGet('school_year'));

        if ($studentId <= 0 || $schoolYear === '') {
            return $this->response->setJSON(['error' => 'Missing student or school year.'])->setStatusCode(400);
        }

        return $this->response->setJSON([
            'semesters' => $this->fetchAllSemestersForStudent($studentId, $schoolYear),
        ]);
    }

public function previewBelowSixtyDecisionEmail()
{
    $studentId  = (int)($this->request->getGet('student_id') ?? 0);
    $schoolYear = trim((string)($this->request->getGet('school_year') ?? ''));

    if ($studentId <= 0 || $schoolYear === '') {
        return $this->response->setJSON([
            'error' => 'Missing student or school year.',
        ]);
    }

    /*
     * Whole-year decision email.
     * Do NOT query semester_scores.semester = "year".
     * The Details button uses fetchAllSemestersForStudent(), so this email does too.
     */
    $context = $this->getBelowSixtyDecisionEmailContext($studentId, $schoolYear);

    if (empty($context['student'])) {
        return $this->response->setJSON([
            'error' => 'Student not found.',
        ]);
    }

    if (empty($context['decision_row']) || trim((string)($context['decision_row']['decision'] ?? '')) === '') {
        return $this->response->setJSON([
            'error' => 'No saved decision found for this student. Save a decision first.',
        ]);
    }

    $studentName      = (string)($context['student_name'] ?? 'Student');
    $classSectionName = (string)($context['class_section_name'] ?? '');
    $decisionRow      = $context['decision_row'];

    $decision = trim((string)($decisionRow['decision'] ?? ''));
    $notes    = trim((string)($decisionRow['notes'] ?? ''));

    $fallScore   = $context['fall_score'] ?? null;
    $springScore = $context['spring_score'] ?? null;
    $yearScore   = $context['year_score'] ?? null;

    /*
     * Same data used by the Details modal.
     */
    $allSemesters = $context['all_semesters'] ?? [];

    if (empty($allSemesters)) {
        $allSemesters = $this->fetchAllSemestersForStudent($studentId, $schoolYear);
    }

    $fallText = $fallScore !== null
        ? number_format((float)$fallScore, 2)
        : 'N/A';

    $springText = $springScore !== null
        ? number_format((float)$springScore, 2)
        : 'N/A';

    $yearText = $yearScore !== null
        ? number_format((float)$yearScore, 2)
        : 'N/A';

    $subject = 'Whole Year Academic Decision — ' . $studentName . ' (' . $schoolYear . ')';

    $html = '
        <p>Dear Parent/Guardian,</p>

        <p>
            This message is regarding <strong>' . esc($studentName) . '</strong>
            for the <strong>' . esc($schoolYear) . '</strong> school year.
        </p>

        <p>
            Class: <strong>' . esc($classSectionName !== '' ? $classSectionName : 'N/A') . '</strong><br>
            Fall Score: <strong>' . esc($fallText) . '</strong><br>
            Spring Score: <strong>' . esc($springText) . '</strong><br>
            Whole-Year Score: <strong>' . esc($yearText) . '</strong><br>
            Decision: <strong>' . esc($decision) . '</strong>
        </p>
    ';

    if ($notes !== '') {
        $html .= '
            <p>
                <strong>Decision Notes:</strong><br>
                ' . nl2br(esc($notes)) . '
            </p>
        ';
    }

    /*
     * Add the exact Details-button score/comment content into the email.
     */
    $html .= $this->buildDecisionEmailDetailsHtml($allSemesters);

    $html .= '
        <p>
            Please contact the school administration if you have any questions.
        </p>

        <p>
            Regards,<br>
            Al Rahma Sunday School
        </p>
    ';

    return $this->response->setJSON([
        'ok'       => true,
        'subject'  => $subject,
        'html'     => $html,
        'decision' => $decision,
        'score'    => $yearScore,
    ]);
}

private function buildDecisionEmailDetailsHtml(array $semesters): string
{
    if (empty($semesters)) {
        return '
            <p><strong>Detailed Scores and Comments</strong></p>
            <p>No detailed score data found.</p>
        ';
    }

    $scoreLabels = [
        'homework_avg'        => 'Homework Avg',
        'project_avg'         => 'Project Avg',
        'participation_score' => 'Participation',
        'test_avg'            => 'Test Avg',
        'ptap_score'          => 'PTAP Score',
        'attendance_score'    => 'Attendance',
        'midterm_exam_score'  => 'Midterm Score',
        'final_exam_score'    => 'Final Exam',
        'semester_score'      => 'Semester Score',
    ];

    $commentTypeLabels = [
        'general'            => 'General',
        'attendance'         => 'Attendance',
        'attendance_comment' => 'Attendance',
        'midterm'            => 'Midterm',
        'final'              => 'Final Exam',
        'ptap'               => 'PTAP',
    ];

    $html = '
        <hr>
        <p><strong>Detailed Scores and Comments</strong></p>
    ';

    foreach ($semesters as $sem) {
        $semesterName = trim((string)($sem['semester'] ?? ''));

        if ($semesterName === '') {
            $semesterName = 'Semester';
        }

        $classSectionName = trim((string)($sem['class_section_name'] ?? ''));

        $html .= '
            <p style="margin-top:16px;margin-bottom:6px;">
                <strong>' . esc($semesterName) . ' Semester</strong>';

        if ($classSectionName !== '') {
            $html .= ' — ' . esc($classSectionName);
        }

        $html .= '
            </p>

            <table border="1"
                   cellpadding="6"
                   cellspacing="0"
                   style="border-collapse:collapse;width:100%;margin-bottom:10px;">
                <thead>
                    <tr>
                        <th align="left" style="background:#f2f2f2;">Item</th>
                        <th align="right" style="background:#f2f2f2;">Score</th>
                    </tr>
                </thead>
                <tbody>
        ';

        $hasScoreRow = false;

        foreach ($scoreLabels as $key => $label) {
            if (!array_key_exists($key, $sem)) {
                continue;
            }

            $value = $sem[$key];

            if ($value === null || $value === '') {
                continue;
            }

            $scoreText = is_numeric($value)
                ? number_format((float)$value, 2)
                : (string)$value;

            $fontWeight = $key === 'semester_score' ? 'font-weight:bold;' : '';

            $html .= '
                <tr>
                    <td>' . esc($label) . '</td>
                    <td align="right" style="' . $fontWeight . '">' . esc($scoreText) . '</td>
                </tr>
            ';

            $hasScoreRow = true;
        }

        if (!$hasScoreRow) {
            $html .= '
                <tr>
                    <td colspan="2">No scores recorded.</td>
                </tr>
            ';
        }

        $html .= '
                </tbody>
            </table>
        ';

        $comments = $sem['comments'] ?? [];

        if (is_array($comments) && !empty($comments)) {
            $deduped = [];
            $seen    = [];

            foreach ($comments as $type => $text) {
                $text = trim((string)$text);

                if ($text === '') {
                    continue;
                }

                $label = $commentTypeLabels[$type] ?? (string)$type;
                $key   = $label . '|' . $text;

                if (isset($seen[$key])) {
                    continue;
                }

                $seen[$key] = true;
                $deduped[] = [
                    'label' => $label,
                    'text'  => $text,
                ];
            }

            if (!empty($deduped)) {
                $html .= '
                    <p style="margin:8px 0 4px;"><strong>Comments</strong></p>
                ';

                foreach ($deduped as $comment) {
                    $html .= '
                        <p style="margin:4px 0 8px;padding:8px;background:#f8f9fa;border-left:4px solid #999;">
                            <strong>' . esc($comment['label']) . ':</strong><br>
                            ' . nl2br(esc($comment['text'])) . '
                        </p>
                    ';
                }
            }
        }
    }

    return $html;
}

public function sendBelowSixtyDecisionEmail()
{
    $studentId    = (int)($this->request->getPost('student_id') ?? 0);
    $schoolYear   = trim((string)($this->request->getPost('school_year') ?? ''));
    $subjectInput = trim((string)($this->request->getPost('subject') ?? ''));
    $htmlInput    = (string)($this->request->getPost('html') ?? '');

    if ($studentId <= 0 || $schoolYear === '') {
        return redirect()->back()->with('error', 'Missing student or school year.');
    }

    /*
     * Whole-year decision email.
     * Do NOT use sendBelowSixtyEmail(), because that one is semester-score based.
     */
    $semester = 'year';

    $context = $this->getBelowSixtyDecisionEmailContext($studentId, $schoolYear);

    if (empty($context['student'])) {
        return redirect()->back()->with('error', 'Student not found.');
    }

    if (empty($context['decision_row']) || trim((string)($context['decision_row']['decision'] ?? '')) === '') {
        return redirect()->back()->with('error', 'No saved decision found for this student.');
    }

    $studentName      = $context['student_name'];
    $classSectionName = $context['class_section_name'];
    $decisionRow      = $context['decision_row'];

    $subject = $subjectInput !== ''
        ? $subjectInput
        : 'Whole Year Academic Decision — ' . $studentName . ' (' . $schoolYear . ')';

    $payload = [
        'student_id'         => $studentId,
        'student_name'       => $studentName,
        'class_section_name' => $classSectionName,
        'semester'           => 'year',
        'school_year'        => $schoolYear,
        'decision'           => (string)($decisionRow['decision'] ?? ''),
        'notes'              => (string)($decisionRow['notes'] ?? ''),
        'subject'            => $subject,
        'scores'             => [
            'fall_score'   => $context['fall_score'],
            'spring_score' => $context['spring_score'],
            'year_score'   => $context['year_score'],
        ],
        'all_semesters'      => $context['all_semesters'],
    ];

    if (trim($htmlInput) !== '') {
        $payload['html'] = $htmlInput;
    }

    Events::trigger('below60.decision_email', $payload);

    $query = http_build_query([
        'semester'    => 'year',
        'school_year' => $schoolYear,
    ]);

    return redirect()
        ->to(base_url('grading/below-60/decisions') . '?' . $query)
        ->with('status', 'Decision email sent to parent(s).');
}


private function getBelowSixtyDecisionEmailContext(int $studentId, string $schoolYear): array
{
    $student = $this->db->table('students s')
        ->select([
            's.id',
            's.firstname',
            's.lastname',
            's.is_active',
        ])
        ->where('s.id', $studentId)
        ->get()
        ->getRowArray();

    /*
     * Do not require is_active = 1 here.
     * You were getting "Student not found" even though the student ID exists.
     * If the record exists, let the email preview work.
     */
    if (!$student) {
        return [
            'student'            => null,
            'decision_row'       => null,
            'student_name'       => '',
            'class_section_name' => '',
            'fall_score'         => null,
            'spring_score'       => null,
            'year_score'         => null,
            'all_semesters'      => [],
        ];
    }

    $studentName = trim((string)($student['firstname'] ?? '') . ' ' . (string)($student['lastname'] ?? ''));

    if ($studentName === '') {
        $studentName = 'Student';
    }

    $decisionRow = $this->db->table('below_sixty_decisions')
        ->where('student_id', $studentId)
        ->where('school_year', $schoolYear)
        ->where('semester', 'year')
        ->get()
        ->getRowArray();

    $scoreRows = $this->db->table('semester_scores ss')
        ->select([
            'LOWER(TRIM(ss.semester)) AS sem_key',
            'ss.semester',
            'ss.semester_score',
            'ss.class_section_id',
            'cs.class_section_name',
        ])
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->where('ss.student_id', $studentId)
        ->where('ss.school_year', $schoolYear)
        ->whereIn('LOWER(TRIM(ss.semester))', ['fall', 'spring'])
        ->where('ss.semester_score IS NOT NULL', null, false)
        ->orderBy('ss.updated_at', 'DESC')
        ->orderBy('ss.id', 'DESC')
        ->get()
        ->getResultArray();

    $fallScore        = null;
    $springScore      = null;
    $classSectionName = '';

    foreach ($scoreRows as $sr) {
        $semKey = strtolower(trim((string)($sr['sem_key'] ?? '')));
        $score  = is_numeric($sr['semester_score'] ?? null) ? (float)$sr['semester_score'] : null;

        if ($classSectionName === '' && !empty($sr['class_section_name'])) {
            $classSectionName = (string)$sr['class_section_name'];
        }

        if ($score === null) {
            continue;
        }

        if ($semKey === 'fall' && $fallScore === null) {
            $fallScore = $score;
        }

        if ($semKey === 'spring' && $springScore === null) {
            $springScore = $score;
        }
    }

    if ($classSectionName === '') {
        $enrollment = $this->db->table('student_class sc')
            ->select('cs.class_section_name')
            ->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left')
            ->where('sc.student_id', $studentId)
            ->where('sc.school_year', $schoolYear)
            ->orderBy('sc.id', 'DESC')
            ->get()
            ->getRowArray();

        $classSectionName = (string)($enrollment['class_section_name'] ?? '');
    }

    if ($fallScore !== null && $springScore !== null) {
        $yearScore = round(($fallScore + $springScore) / 2, 2);
    } elseif ($fallScore !== null) {
        $yearScore = round($fallScore, 2);
    } elseif ($springScore !== null) {
        $yearScore = round($springScore, 2);
    } else {
        $yearScore = null;
    }

    return [
        'student'            => $student,
        'decision_row'       => $decisionRow,
        'student_name'       => $studentName,
        'class_section_name' => $classSectionName,
        'fall_score'         => $fallScore,
        'spring_score'       => $springScore,
        'year_score'         => $yearScore,
        'all_semesters'      => $this->fetchAllSemestersForStudent($studentId, $schoolYear),
    ];
}

    public function previewDecisionEmail()
    {
        $studentId  = (int)$this->request->getGet('student_id');
        $semester   = trim((string)$this->request->getGet('semester'));
        $schoolYear = trim((string)$this->request->getGet('school_year'));

        if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
            return $this->response->setJSON(['error' => 'Missing student or term.'])->setStatusCode(400);
        }

        $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
        if (empty($row)) {
            return $this->response->setJSON(['error' => 'Student not found.'])->setStatusCode(404);
        }

        $decisionModel = new BelowSixtyDecisionModel();
        $decisionRow = $decisionModel
            ->where('student_id', $studentId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->first();

        $decision    = (string)($decisionRow['decision'] ?? '');
        $notes       = (string)($decisionRow['notes'] ?? '');
        $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        $parentName  = $this->fetchBelowSixtyParentName($studentId);

        $subject = 'Academic Decision';
        if ($studentName !== '') $subject .= ' — ' . $studentName;
        if ($semester !== '' || $schoolYear !== '') {
            $subject .= ' (' . trim($semester . ' ' . $schoolYear) . ')';
        }

        $scores = [
            'homework_avg'        => $row['homework_avg'] ?? null,
            'project_avg'         => $row['project_avg'] ?? null,
            'participation_score' => $row['participation_score'] ?? null,
            'test_avg'            => $row['test_avg'] ?? null,
            'ptap_score'          => $row['ptap_score'] ?? null,
            'attendance_score'    => $row['attendance_score'] ?? null,
            'midterm_exam_score'  => $row['midterm_exam_score'] ?? null,
            'semester_score'      => $row['semester_score'] ?? null,
        ];

        // Fetch all semesters' scores + comments for the email
        $allSemesters = $this->fetchAllSemestersForStudent($studentId, $schoolYear);

        $html = view('emails/below_sixty_decision', [
            'title'              => $subject,
            'parent_name'        => $parentName,
            'student_name'       => $studentName !== '' ? $studentName : 'your student',
            'class_section_name' => $row['class_section_name'] ?? '',
            'semester'           => $semester,
            'school_year'        => $schoolYear,
            'decision'           => $decision,
            'notes'              => $notes,
            'scores'             => $scores,
            'all_semesters'      => array_values($allSemesters),
        ], ['saveData' => true]);

        return $this->response->setJSON([
            'subject'    => $subject,
            'html'       => $html,
            'student_id' => $studentId,
        ]);
    }

    public function editDecisionEmail()
    {
        $studentId  = (int)$this->request->getGet('student_id');
        $semester   = trim((string)$this->request->getGet('semester'));
        $schoolYear = trim((string)$this->request->getGet('school_year'));

        if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing student or term.');
        }

        $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
        if (empty($row)) {
            return redirect()->back()->with('error', 'Student record not found for the selected term.');
        }

        $decisionModel = new BelowSixtyDecisionModel();
        $decisionRow = $decisionModel
            ->where('student_id', $studentId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->first();

        $decision = (string)($decisionRow['decision'] ?? '');
        $notes    = (string)($decisionRow['notes'] ?? '');

        $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        $parentName  = $this->fetchBelowSixtyParentName($studentId);

        $subject = 'Academic Decision';
        if ($studentName !== '') $subject .= ' — ' . $studentName;
        if ($semester !== '' || $schoolYear !== '') {
            $subject .= ' (' . trim($semester . ' ' . $schoolYear) . ')';
        }

        $scores = [
            'homework_avg'        => $row['homework_avg'] ?? null,
            'project_avg'         => $row['project_avg'] ?? null,
            'participation_score' => $row['participation_score'] ?? null,
            'test_avg'            => $row['test_avg'] ?? null,
            'ptap_score'          => $row['ptap_score'] ?? null,
            'attendance_score'    => $row['attendance_score'] ?? null,
            'midterm_exam_score'  => $row['midterm_exam_score'] ?? null,
            'semester_score'      => $row['semester_score'] ?? null,
        ];

        $html = view('emails/below_sixty_decision', [
            'title'              => $subject,
            'parent_name'        => $parentName,
            'student_name'       => $studentName !== '' ? $studentName : 'your student',
            'class_section_name' => $row['class_section_name'] ?? '',
            'semester'           => $semester,
            'school_year'        => $schoolYear,
            'decision'           => $decision,
            'notes'              => $notes,
            'scores'             => $scores,
        ], ['saveData' => true]);

        return view('grading/below_sixty_decision_email_editor', [
            'studentId'   => $studentId,
            'studentName' => $studentName,
            'semester'    => $semester,
            'schoolYear'  => $schoolYear,
            'subject'     => $subject,
            'html'        => $html,
            'decision'    => $decision,
        ]);
    }

    public function sendDecisionEmail()
    {
        $studentId   = (int)$this->request->getPost('student_id');
        $semester    = trim((string)$this->request->getPost('semester'));
        $schoolYear  = trim((string)$this->request->getPost('school_year'));
        $subjectInput= trim((string)$this->request->getPost('subject'));
        $htmlInput   = (string)($this->request->getPost('html') ?? '');

        if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
            return redirect()->back()->with('error', 'Missing student or term.');
        }

        $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
        if (empty($row)) {
            return redirect()->back()->with('error', 'Student record not found for the selected term.');
        }

        $decisionModel = new BelowSixtyDecisionModel();
        $decisionRow = $decisionModel
            ->where('student_id', $studentId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->first();

        $studentName = trim((string)($row['firstname'] ?? '') . ' ' . (string)($row['lastname'] ?? ''));
        $subject = $subjectInput !== '' ? $subjectInput : ('Academic Decision — ' . $studentName . ' (' . trim($semester . ' ' . $schoolYear) . ')');

        $allSemesters = $this->fetchAllSemestersForStudent($studentId, $schoolYear);

        $payload = [
            'student_id'         => $studentId,
            'student_name'       => $studentName,
            'class_section_name' => $row['class_section_name'] ?? '',
            'semester'           => $semester,
            'school_year'        => $schoolYear,
            'decision'           => (string)($decisionRow['decision'] ?? ''),
            'notes'              => (string)($decisionRow['notes'] ?? ''),
            'subject'            => $subject,
            'all_semesters'      => $allSemesters,
            'scores' => [
                'homework_avg'        => $row['homework_avg'] ?? null,
                'project_avg'         => $row['project_avg'] ?? null,
                'participation_score' => $row['participation_score'] ?? null,
                'test_avg'            => $row['test_avg'] ?? null,
                'ptap_score'          => $row['ptap_score'] ?? null,
                'attendance_score'    => $row['attendance_score'] ?? null,
                'midterm_exam_score'  => $row['midterm_exam_score'] ?? null,
                'semester_score'      => $row['semester_score'] ?? null,
            ],
        ];

        if (trim($htmlInput) !== '') {
            $payload['html'] = $htmlInput;
        }

        \CodeIgniter\Events\Events::trigger('below60.decision_email', $payload);

        $query = http_build_query(['semester' => $semester, 'school_year' => $schoolYear]);
        return redirect()->to(base_url('grading/below-60/decisions') . ($query ? '?' . $query : ''))
            ->with('status', 'Decision email sent to parent(s).');
    }

public function allDecisions()
{
    $configuredYear = (string)$this->schoolYear;

    $schoolYear = trim((string)($this->request->getGet('school_year') ?? ''));
    if ($schoolYear === '') {
        $schoolYear = $configuredYear;
    }

    $schoolYears = $this->getSchoolYearsForScores($schoolYear);

    // Load saved YEAR decisions for this school year.
    // New structure:
    // one row per student per school_year
    // uses year_score, not semester_score
    // does not use semester = 'year'
    $decModel = new StudentDecisionModel();

    $saved = $decModel
        ->where('school_year', $schoolYear)
        ->findAll();

    $savedMap = [];
    foreach ($saved as $s) {
        $sid = (int)($s['student_id'] ?? 0);
        if ($sid > 0) {
            $savedMap[$sid] = $s;
        }
    }

    // Fetch Fall and Spring semester scores per student.
    // These raw semester scores are only used to calculate the final year_score.
    $allScoreRows = $this->db->table('semester_scores ss')
        ->select([
            's.id AS student_id',
            's.school_id',
            's.firstname',
            's.lastname',
            's.gender',
            'ss.class_section_id',
            'cs.class_section_name',
            'LOWER(TRIM(ss.semester)) AS sem_key',
            'ss.semester_score',
        ])
        ->join('students s', 's.id = ss.student_id', 'inner')
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->where('s.is_active', 1)
        ->where('ss.school_year', $schoolYear)
        ->whereIn('LOWER(TRIM(ss.semester))', ['fall', 'spring'])
        ->where('ss.semester_score IS NOT NULL', null, false)
        ->orderBy('cs.class_section_name', 'ASC')
        ->orderBy('s.lastname', 'ASC')
        ->orderBy('s.firstname', 'ASC')
        ->get()
        ->getResultArray();

    // Group Fall/Spring scores by student.
    $studentMap = [];

    foreach ($allScoreRows as $sr) {
        $sid = (int)($sr['student_id'] ?? 0);

        if ($sid <= 0) {
            continue;
        }

        if (!isset($studentMap[$sid])) {
            $studentMap[$sid] = [
                'school_id'          => $sr['school_id'] ?? '',
                'firstname'          => $sr['firstname'] ?? '',
                'lastname'           => $sr['lastname'] ?? '',
                'gender'             => $sr['gender'] ?? '',
                'class_section_id'   => (int)($sr['class_section_id'] ?? 0),
                'class_section_name' => $sr['class_section_name'] ?? '',
                'fall_score'         => null,
                'spring_score'       => null,
            ];
        }

        $semKey = strtolower(trim((string)($sr['sem_key'] ?? '')));
        $val    = is_numeric($sr['semester_score']) ? (float)$sr['semester_score'] : null;

        if ($semKey === 'fall') {
            $studentMap[$sid]['fall_score'] = $val;
        } elseif ($semKey === 'spring') {
            $studentMap[$sid]['spring_score'] = $val;
        }
    }

    // Pull below-60 manual decisions for this school year.
    // Used only when the calculated year_score is below 60.
    $belowDecModel = new BelowSixtyDecisionModel();

    $belowRows = $belowDecModel
        ->where('school_year', $schoolYear)
        ->findAll();

    $belowMap = [];

    foreach ($belowRows as $b) {
        $sid = (int)($b['student_id'] ?? 0);

        if ($sid <= 0) {
            continue;
        }

        // Keep the first non-empty decision found for this student.
        if (!isset($belowMap[$sid]) || trim((string)($belowMap[$sid]['decision'] ?? '')) === '') {
            $belowMap[$sid] = $b;
        }
    }

    // Build final display rows.
    $rows = [];

    foreach ($studentMap as $sid => $info) {
        $fall   = $info['fall_score'];
        $spring = $info['spring_score'];

        if ($fall !== null && $spring !== null) {
            $yearScore = round(($fall + $spring) / 2, 2);
        } elseif ($fall !== null) {
            $yearScore = round((float)$fall, 2);
        } elseif ($spring !== null) {
            $yearScore = round((float)$spring, 2);
        } else {
            $yearScore = null;
        }

        if (isset($savedMap[$sid])) {
            $savedRow = $savedMap[$sid];

            $decision = trim((string)($savedRow['decision'] ?? ''));
            $source   = trim((string)($savedRow['source'] ?? 'pending'));
            $notes    = (string)($savedRow['notes'] ?? '');

            if (isset($savedRow['year_score']) && $savedRow['year_score'] !== '' && is_numeric($savedRow['year_score'])) {
                $yearScore = round((float)$savedRow['year_score'], 2);
            }
        } elseif ($yearScore !== null && $yearScore >= 60) {
            $decision = 'Pass';
            $source   = 'auto';
            $notes    = '';
        } elseif ($yearScore !== null && isset($belowMap[$sid])) {
            $decision = trim((string)($belowMap[$sid]['decision'] ?? ''));
            $source   = $decision !== '' ? 'manual' : 'pending';
            $notes    = (string)($belowMap[$sid]['notes'] ?? '');
        } else {
            $decision = '';
            $source   = 'pending';
            $notes    = '';
        }

        $rows[] = [
            'student_id'         => $sid,
            'school_id'          => $info['school_id'],
            'firstname'          => $info['firstname'],
            'lastname'           => $info['lastname'],
            'gender'             => $info['gender'] ?? '',
            'class_section_id'   => (int)($info['class_section_id'] ?? 0),
            'class_section_name' => $info['class_section_name'],
            'fall_score'         => $fall,
            'spring_score'       => $spring,
            'year_score'         => $yearScore,
            'decision'           => $decision,
            'source'             => $source,
            'notes'              => $notes,
            'saved'              => isset($savedMap[$sid]),
            'is_trophy'          => false,
        ];
    }

    $rowsByClass = [];

    foreach ($rows as $index => $row) {
        $classSectionId = (int)($row['class_section_id'] ?? 0);

        if ($classSectionId <= 0) {
            continue;
        }

        $rowsByClass[$classSectionId][] = $index;
    }

    foreach ($rowsByClass as $classIndexes) {
        $scores = [];

        foreach ($classIndexes as $rowIndex) {
            $yearScore = $rows[$rowIndex]['year_score'] ?? null;

            if (is_numeric($yearScore)) {
                $scores[] = (float)$yearScore;
            }
        }

        $thresholdInfo = $this->calculateTrophyThreshold($scores, 75.0);
        $threshold = $thresholdInfo['threshold'];

        if ($threshold === null) {
            continue;
        }

        foreach ($classIndexes as $rowIndex) {
            $yearScore = $rows[$rowIndex]['year_score'] ?? null;

            $rows[$rowIndex]['is_trophy'] = is_numeric($yearScore) && (float)$yearScore >= $threshold;
        }
    }

    $generated = !empty($saved);

    return view('grading/all_decisions', [
        'rows'        => $rows,
        'schoolYear'  => $schoolYear,
        'schoolYears' => $schoolYears,
        'generated'   => $generated,
    ]);
}


public function generateAllDecisions()
{
    $schoolYear = trim((string)$this->request->getPost('school_year'));

    if ($schoolYear === '') {
        return redirect()->back()->with('error', 'Missing school year.');
    }

    // Fetch Fall and Spring scores per student.
    $allScoreRows = $this->db->table('semester_scores ss')
        ->select([
            's.id AS student_id',
            's.firstname',
            's.lastname',
            'cs.class_section_name',
            'LOWER(TRIM(ss.semester)) AS sem_key',
            'ss.semester_score',
        ])
        ->join('students s', 's.id = ss.student_id', 'inner')
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->where('s.is_active', 1)
        ->where('ss.school_year', $schoolYear)
        ->whereIn('LOWER(TRIM(ss.semester))', ['fall', 'spring'])
        ->where('ss.semester_score IS NOT NULL', null, false)
        ->get()
        ->getResultArray();

    if (empty($allScoreRows)) {
        return redirect()->back()->with('error', 'No semester scores found for this school year.');
    }

    // Group Fall/Spring scores by student.
    $studentMap = [];

    foreach ($allScoreRows as $sr) {
        $sid = (int)($sr['student_id'] ?? 0);

        if ($sid <= 0) {
            continue;
        }

        if (!isset($studentMap[$sid])) {
            $studentMap[$sid] = [
                'firstname'          => $sr['firstname'] ?? '',
                'lastname'           => $sr['lastname'] ?? '',
                'class_section_name' => $sr['class_section_name'] ?? '',
                'fall_score'         => null,
                'spring_score'       => null,
            ];
        }

        $semKey = strtolower(trim((string)($sr['sem_key'] ?? '')));
        $val    = is_numeric($sr['semester_score']) ? (float)$sr['semester_score'] : null;

        if ($semKey === 'fall') {
            $studentMap[$sid]['fall_score'] = $val;
        } elseif ($semKey === 'spring') {
            $studentMap[$sid]['spring_score'] = $val;
        }
    }

    // Pull below-60 manual decisions for this school year.
    $belowDecModel = new BelowSixtyDecisionModel();

    $belowRows = $belowDecModel
        ->where('school_year', $schoolYear)
        ->findAll();

    $belowMap = [];

    foreach ($belowRows as $b) {
        $sid = (int)($b['student_id'] ?? 0);

        if ($sid <= 0) {
            continue;
        }

        if (!isset($belowMap[$sid]) || trim((string)($belowMap[$sid]['decision'] ?? '')) === '') {
            $belowMap[$sid] = $b;
        }
    }

    // Load existing year decisions so we update instead of duplicating.
    // New structure: one row per student per school_year.
    $decModel = new StudentDecisionModel();

    $existing = $decModel
        ->where('school_year', $schoolYear)
        ->findAll();

    $existingMap = [];

    foreach ($existing as $e) {
        $sid = (int)($e['student_id'] ?? 0);

        if ($sid > 0) {
            $existingMap[$sid] = $e;
        }
    }

    $userId     = (int)(session()->get('user_id') ?? 0) ?: null;
    $savedCount = 0;

    foreach ($studentMap as $sid => $info) {
        $fall   = $info['fall_score'];
        $spring = $info['spring_score'];

        if ($fall !== null && $spring !== null) {
            $yearScore = round(($fall + $spring) / 2, 2);
        } elseif ($fall !== null) {
            $yearScore = round((float)$fall, 2);
        } elseif ($spring !== null) {
            $yearScore = round((float)$spring, 2);
        } else {
            continue;
        }

        if ($yearScore >= 60) {
            $decision = 'Pass';
            $source   = 'auto';
            $notes    = null;
        } elseif (isset($belowMap[$sid]) && trim((string)($belowMap[$sid]['decision'] ?? '')) !== '') {
            $decision = trim((string)$belowMap[$sid]['decision']);
            $source   = 'manual';
            $notes    = trim((string)($belowMap[$sid]['notes'] ?? ''));
            $notes    = $notes !== '' ? $notes : null;
        } else {
            $decision = null;
            $source   = 'pending';
            $notes    = null;
        }

        // Important fix:
        // use year_score, not semester_score.
        // do not save semester = 'year'.
        $payload = [
            'student_id'         => $sid,
            'school_year'        => $schoolYear,
            'class_section_name' => $info['class_section_name'] ?? null,
            'year_score'         => $yearScore,
            'decision'           => $decision,
            'source'             => $source,
            'notes'              => $notes,
            'generated_by'       => $userId,
        ];

        if (isset($existingMap[$sid])) {
            $decModel->update((int)$existingMap[$sid]['id'], $payload);
        } else {
            $decModel->insert($payload);
        }

        $savedCount++;
    }

    $query = http_build_query(['school_year' => $schoolYear]);

    return redirect()->to(base_url('grading/decisions') . '?' . $query)
        ->with('status', "Decisions generated for {$savedCount} students.");
}

    private function calculateTrophyThreshold(array $scores, float $percentile = 75.0): array
    {
        $scores = array_values(array_filter(
            $scores,
            static fn ($value): bool => is_numeric($value) && $value !== null
        ));
        $scores = array_map('floatval', $scores);
        sort($scores);

        $count = count($scores);

        if ($count === 0) {
            return ['threshold' => null, 'winners' => 0, 'method' => 'empty'];
        }

        $minWinners = 3;
        $maxWinners = max($minWinners, (int) floor($count * (1 - $percentile / 100)));

        $threshold = $this->empiricalTrophyPercentile($scores, $percentile);
        $winners = $this->countScoresAtOrAbove($scores, $threshold);

        if ($winners < $minWinners) {
            $target = min($minWinners, $count);
            $descending = array_reverse($scores);
            $threshold = $descending[$target - 1];
            $winners = $this->countScoresAtOrAbove($scores, $threshold);

            return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'min3_reduced'];
        }

        if ($winners <= $maxWinners) {
            return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'empirical_percentile'];
        }

        $result = $this->capTrophyThresholdByRank($scores, $maxWinners);

        if ($result['winners'] < $minWinners) {
            $target = min($minWinners, $count);
            $descending = array_reverse($scores);
            $threshold = $descending[$target - 1];
            $winners = $this->countScoresAtOrAbove($scores, $threshold);

            return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'min3_after_cap'];
        }

        return $result;
    }

    private function capTrophyThresholdByRank(array $sortedScores, int $max): array
    {
        $descending = array_reverse($sortedScores);
        $threshold = $descending[$max - 1];
        $winners = $this->countScoresAtOrAbove($sortedScores, $threshold);

        if ($winners <= $max) {
            return ['threshold' => $threshold, 'winners' => $winners, 'method' => 'capped_25pct'];
        }

        $uniqueHigherScores = array_values(array_unique(array_filter(
            $sortedScores,
            static fn ($score): bool => $score > $threshold
        )));
        sort($uniqueHigherScores);

        foreach ($uniqueHigherScores as $candidate) {
            $winnerCount = $this->countScoresAtOrAbove($sortedScores, $candidate);

            if ($winnerCount <= $max) {
                return ['threshold' => $candidate, 'winners' => $winnerCount, 'method' => 'capped_25pct'];
            }
        }

        return ['threshold' => $sortedScores[0], 'winners' => count($sortedScores), 'method' => 'all_equal'];
    }

    private function empiricalTrophyPercentile(array $sortedScores, float $percentile): float
    {
        $count = count($sortedScores);

        if ($count === 0) {
            return 0.0;
        }

        $index = ($percentile / 100.0) * ($count - 1);
        $lower = (int) floor($index);
        $upper = (int) ceil($index);

        if ($lower === $upper) {
            return $sortedScores[$lower];
        }

        return $sortedScores[$lower] + ($index - $lower) * ($sortedScores[$upper] - $sortedScores[$lower]);
    }

    private function countScoresAtOrAbove(array $scores, float $threshold): int
    {
        return count(array_filter(
            $scores,
            static fn ($score): bool => $score >= $threshold
        ));
    }

    public function getScoreComment()
    {
        // Get all students for the current semester and school year
        $studentClassEntries = $this->studentClassModel
            ->where('semester', $this->semester)
            ->where('school_year', $this->schoolYear)
            ->findAll();

        // Group student IDs
        $studentIds = array_map(fn($entry) => $entry['student_id'], $studentClassEntries);

        // Fetch all scores and comments for the students
        $scoresAndComments = $this->getAllScoresAndComments($studentIds, $this->semester, $this->schoolYear);

        return $scoresAndComments;
    }

    /**
     * Fetch all scores and comments for a list of students based on semester and school year.
     *
     * @param array $studentIds List of student IDs.
     * @param string $semester Current semester (e.g., 'fall', 'spring').
     * @param string $schoolYear Current school year (e.g., '2025-2026').
     * @return array
     */
    private function getAllScoresAndComments($studentIds, $semester, $schoolYear)
    {
        // Validate input parameters
        if (empty($studentIds) || !is_array($studentIds)) {
            return [];
        }

        if (empty($this->semester) || empty($this->schoolYear)) {
            throw new \InvalidArgumentException('Semester and school year must be provided');
        }

        // Initialize models
        $models = [
            'final_exam' => new FinalExamModel(),
            'homework' => new HomeworkModel(),
            'midterm' => new MidtermExamModel(),
            'project' => new ProjectModel(),
            'quiz' => new QuizModel(),
            'comments' => new ScoreCommentModel(),
            'semester_scores' => new SemesterScoreModel(),
            'student' => new StudentModel(),
            'student_class' => new StudentClassModel(),
            'teacher_class' => new TeacherClassModel(),
            'config' => new ConfigurationModel(),
            'attendance' => new AttendanceRecordModel()
        ];

        // Get semester days configuration
        $semesterKey = strtolower($semester) === 'fall' ? 'total_semester1_days' : 'total_semester2_days';
        $totalSemesterDays = $models['config']->getConfig($semesterKey) ?? 0;

        // Common query conditions
        $conditions = [
            'semester' => $this->semester,
            'school_year' => $this->schoolYear
        ];

        // Fetch all student data first
        $students = $models['student']->whereIn('id', $studentIds)
            ->where('school_year', $this->schoolYear)
            ->findAll();

        if (empty($students)) {
            return [];
        }

        // Initialize result array with student data
        $allScores = [];
        foreach ($students as $student) {
            $className = $models['student_class']->getClassSectionsByStudentId($student['id'], $this->schoolYear);
            $updatedBy = $models['teacher_class']->getTeacherIdByClassSection($className, $this->semester, $this->schoolYear);

            $allScores[$student['id']] = [
                'school_id' => $student['school_id'],
                'firstname' => $student['firstname'],
                'lastname' => $student['lastname'],
                'class_name' => $className,
                //'teacherId' => $updatedBy,
                'comments' => [] // Initialize empty comments array
            ];
        }

        // Fetch and process attendance data
        foreach ($studentIds as $studentId) {
            if (!isset($allScores[$studentId])) continue;

            $absences = $models['attendance']->getTotalAbsences($studentId, $this->semester, $this->schoolYear);
            $attendance = min((($totalSemesterDays - $absences + 1) / $totalSemesterDays) * 100, 100);

            $allScores[$studentId]['attendance'] = [
                'score' => round($attendance, 2),
                'absences' => $absences,
                'total_days' => $totalSemesterDays
            ];
        }

        // Fetch and process all score types
        $scoreTypes = [
            'final_exam' => $models['final_exam'],
            'homework' => $models['homework'],
            'midterm' => $models['midterm'],
            'project' => $models['project'],
            'quiz' => $models['quiz'],
            'semester_score' => $models['semester_scores']
        ];

        foreach ($scoreTypes as $type => $model) {
            $scores = $model->whereIn('student_id', $studentIds)
                ->where($conditions)
                ->findAll();

            foreach ($scores as $score) {
                if ($type === 'semester_score') {
                    $allScores[$score['student_id']][$type] = [
                        'homework_avg' => $score['homework_avg'],
                        'quiz_avg' => $score['quiz_avg'],
                        'project_avg' => $score['project_avg'],
                        'midterm_exam_score' => $score['midterm_exam_score'],
                        'final_exam_score' => $score['final_exam_score'],
                        'attendance_score' => $score['attendance_score'],
                        'participation_score' => $score['participation_score'],
                        'ptap_score' => $score['ptap_score'],
                        'test_avg' => $score['test_avg'],
                        'semester_score' => $score['semester_score'],
                        'semester' => $score['semester'],
                        'school_year' => $score['school_year']
                    ];
                } else {
                    $allScores[$score['student_id']][$type] = $score;
                }
            }
        }

        // Fetch and process comments
        $comments = $models['comments']->whereIn('student_id', $studentIds)
            ->where($conditions)
            ->findAll();

        foreach ($comments as $comment) {
            $allScores[$comment['student_id']]['comments'][] = $comment;
        }

        return $allScores;
    }
}
