<?php

namespace App\Services;

use App\Models\BelowSixtyDecisionModel;
use App\Models\ConfigurationModel;
use App\Models\ParentMeetingScheduleModel;
use App\Models\StudentClassModel;
use App\Models\StudentDecisionModel;
use App\Models\StudentModel;
use App\Models\UserModel;
use App\Services\NavbarService;
use App\Support\Enrollment\DeliberationDecision;
use CodeIgniter\Events\Events;

class BelowSixtyService
{
    protected $db;
    protected $configModel;
    protected $studentModel;
    protected $studentClassModel;
    protected $parentMeetingModel;
    protected $userModel;
    protected string $schoolYear = '';
    protected string $semester = '';

    public function __construct(
        \CodeIgniter\Database\BaseConnection $db,
        ConfigurationModel $configModel,
        StudentModel $studentModel,
        StudentClassModel $studentClassModel,
        ParentMeetingScheduleModel $parentMeetingModel,
        UserModel $userModel,
        string $schoolYear = '',
        string $semester = ''
    ) {
        $this->db = $db;
        $this->configModel = $configModel;
        $this->studentModel = $studentModel;
        $this->studentClassModel = $studentClassModel;
        $this->parentMeetingModel = $parentMeetingModel;
        $this->userModel = $userModel;
        $this->schoolYear = $schoolYear;
        $this->semester = $semester;
    }

    public function setTerm(string $schoolYear, string $semester): void
    {
        $this->schoolYear = $schoolYear;
        $this->semester = $semester;
    }

public function belowSixtyPage(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

$schoolYear = (string) ($params['school_year'] ?? $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 ['kind' => 'view', 'view' => 'grading/below_sixty', 'data' => [
    'rows'           => $rows,
    'semester'       => $semester,
    'schoolYear'     => $schoolYear,
    'schoolYears'    => $schoolYears,
    'isYearMode'     => $isYearMode,
    'canViewGrading' => $canViewGrading,
]];
}

public function editBelowSixtyEmail(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

    $studentId = (int)($get['student_id'] ?? null);
    $semester = trim((string)($get['semester'] ?? null));
    $schoolYear = trim((string)($get['school_year'] ?? null));

    if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Missing student or term.'];
    }

    $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
    if (empty($row)) {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => '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 ['kind' => 'view', 'view' => 'grading/below_sixty_email_editor', 'data' => [
        'studentId' => $studentId,
        'studentName' => $studentName,
        'semester' => $semester,
        'schoolYear' => $schoolYear,
        'subject' => $subject,
        'html' => $html,
    ]];
}

public function sendBelowSixtyEmail(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

    $studentId = (int)($post['student_id'] ?? null);
    $semester = trim((string)($post['semester'] ?? null));
    $schoolYear = trim((string)($post['school_year'] ?? null));
    $subjectInput = trim((string)($post['subject'] ?? null));
    $htmlInput = (string)(($post['html'] ?? null) ?? '');

    if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Missing student or term.'];
    }

    $row = $this->fetchBelowSixtyEmailRow($studentId, $schoolYear, $semester);
    if (empty($row)) {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => '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 ['kind' => 'flash', 'redirect' => 'back', 'type' => 'status', 'message' => 'Email sent to parent(s).'];
}

public function updateBelowSixtyStatus(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

    $studentId = (int)($post['student_id'] ?? null);
    $semester = trim((string)($post['semester'] ?? null));
    $schoolYear = trim((string)($post['school_year'] ?? null));
    $status = trim((string)($post['status'] ?? null));
    $note = trim((string)($post['note'] ?? null));

    if ($studentId <= 0 || $semester === '' || $schoolYear === '' || $status === '') {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Missing required status data.'];
    }

    $status = ucfirst(strtolower($status));
    if (!in_array($status, ['Open', 'Closed'], true)) {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => '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 ['kind' => 'flash', 'redirect' => $redirectUrl, 'type' => 'error', 'message' => 'Failed to update status.'];
    }

    return ['kind' => 'flash', 'redirect' => $redirectUrl, 'type' => 'status', 'message' => 'Status updated.'];
}

public function scheduleBelowSixtyPage(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

    $studentId = (int)(($get['student_id'] ?? null) ?? 0);
    $semester = trim((string)(($get['semester'] ?? null) ?? ''));
    $schoolYear = trim((string)(($get['school_year'] ?? null) ?? ''));

    if ($studentId <= 0 || $semester === '' || $schoolYear === '') {
        return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Missing student or term.'];
    }

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

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

public function saveBelowSixtyMeeting(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

    $studentId = (int)($post['student_id'] ?? null);
    $semester = trim((string)($post['semester'] ?? null));
    $schoolYear = trim((string)($post['school_year'] ?? null));
    $date = trim((string)($post['date'] ?? null));
    $time = trim((string)($post['time'] ?? null));
    $notes = trim((string)($post['notes'] ?? null));

    if ($studentId <= 0 || $semester === '' || $schoolYear === '' || $date === '') {
        return ['kind' => 'flash', 'redirect' => 'back', 'withInput' => true, 'type' => 'error', 'message' => 'Missing required fields.'];
    }

    $context = $this->fetchBelowSixtyMeetingContext($studentId, $schoolYear, $semester);
    if (empty($context)) {
        return ['kind' => 'flash', 'redirect' => 'back', 'withInput' => true, 'type' => 'error', 'message' => '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 ['kind' => 'flash', 'redirect' => base_url('grading/below-60') . ($query ? ('?' . $query) : ''), 'type' => 'status', 'message' => 'Meeting scheduled and added to calendars.'];
    }

    return ['kind' => 'flash', 'redirect' => 'back', 'withInput' => true, 'type' => 'error', 'message' => 'Failed to schedule the meeting.'];
}

 public function belowSixtyDecisionsPage(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

$configuredYear = (string) ($params['school_year'] ?? $this->schoolYear);
$isReadonly = (bool) ($params['is_readonly'] ?? false);

$schoolYear = trim((string)(($get['school_year'] ?? null) ?? ''));

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',
        's.is_active',
        'cs.class_section_name',
        'e.enrollment_status',
        'e.is_withdrawn',
        'LOWER(TRIM(ss.semester)) AS sem_key',
        'ss.semester_score',
    ])
    ->join('students s', 's.id = ss.student_id', 'inner')
    ->join('enrollments e', 'e.student_id = s.id AND e.school_year = ' . $this->db->escape($schoolYear), 'left')
    ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
    ->groupStart()
        ->where('s.is_active', 1)
        ->orWhere("LOWER(TRIM(e.enrollment_status)) IN ('denied','withdrawn','waitlist')", null, false)
        ->orWhere('e.is_withdrawn', 1)
    ->groupEnd()
    ->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 ['kind' => 'view', 'view' => 'grading/below_sixty_decisions', 'data' => [
    'rows'           => $rows,
    'semester'       => $semester,
    'schoolYear'     => $schoolYear,
    'schoolYears'    => $schoolYears,
    'canViewGrading' => $canViewGrading,
    'isEditable'     => ! $isReadonly,
]];
}

public function saveBelowSixtyDecision(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

$configuredYear = (string) ($params['school_year'] ?? $this->schoolYear);
$studentId  = (int)(($post['student_id'] ?? null) ?? 0);
$semester   = strtolower(trim((string)(($post['semester'] ?? null) ?? 'year')));
$schoolYear = trim((string)(($post['school_year'] ?? null) ?? ''));
$decision   = trim((string)(($post['decision'] ?? null) ?? ''));
$notes      = trim((string)(($post['notes'] ?? null) ?? ''));

if ($studentId <= 0 || $schoolYear === '') {
    return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Missing student or school year.'];
}

if ($schoolYear !== $configuredYear) {
    return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Selected school year does not match the submitted decision.'];
}

$schoolYearContext = service('schoolYearContext')->forYearName($schoolYear);
service('schoolYearWriteGuard')->assertWritable($schoolYearContext);

// 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 ['kind' => 'flash', 'redirect' => base_url('grading/below-60/decisions') . '?' . $query, 'type' => 'status', 'message' => 'Decision saved and certificate decision updated.'];
}

public function studentDecisionDetails(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

    $studentId  = (int)($get['student_id'] ?? null);
    $schoolYear = trim((string)($get['school_year'] ?? null));

    if ($studentId <= 0 || $schoolYear === '') {
        return ['kind' => 'json', 'status' => 400, 'data' => ['error' => 'Missing student or school year.']];
    }

    return ['kind' => 'json', 'status' => 200, 'data' => [
        'semesters' => $this->fetchAllSemestersForStudent($studentId, $schoolYear),
    ]];
}

public function previewBelowSixtyDecisionEmail(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

$studentId  = (int)(($get['student_id'] ?? null) ?? 0);
$schoolYear = trim((string)(($get['school_year'] ?? null) ?? ''));

if ($studentId <= 0 || $schoolYear === '') {
    return ['kind' => 'json', 'status' => 200, 'data' => [
        '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 ['kind' => 'json', 'status' => 200, 'data' => [
        'error' => 'Student not found.',
    ]];
}

if (empty($context['decision_row']) || trim((string)($context['decision_row']['decision'] ?? '')) === '') {
    return ['kind' => 'json', 'status' => 200, 'data' => [
        '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 ['kind' => 'json', 'status' => 200, 'data' => [
    'ok'       => true,
    'subject'  => $subject,
    'html'     => $html,
    'decision' => $decision,
    'score'    => $yearScore,
]];
}

public function sendBelowSixtyDecisionEmail(array $params = [])
{
    $get = $params['get'] ?? [];
    $post = $params['post'] ?? [];

$studentId    = (int)(($post['student_id'] ?? null) ?? 0);
$schoolYear   = trim((string)(($post['school_year'] ?? null) ?? ''));
$subjectInput = trim((string)(($post['subject'] ?? null) ?? ''));
$htmlInput    = (string)(($post['html'] ?? null) ?? '');

if ($studentId <= 0 || $schoolYear === '') {
    return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => '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 ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => 'Student not found.'];
}

if (empty($context['decision_row']) || trim((string)($context['decision_row']['decision'] ?? '')) === '') {
    return ['kind' => 'flash', 'redirect' => 'back', 'type' => 'error', 'message' => '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 ['kind' => 'flash', 'redirect' => base_url('grading/below-60/decisions') . '?' . $query, 'type' => 'status', 'message' => 'Decision email sent to parent(s).'];
}

public 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('MAX(s.is_active) AS is_active', false)
            ->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)
            ->select('MAX(e.enrollment_status) AS enrollment_status, MAX(e.is_withdrawn) AS is_withdrawn', false)
            ->join('students s', 's.id = ss.student_id', 'inner')
            ->join('enrollments e', 'e.student_id = s.id AND e.school_year = ' . $this->db->escape($schoolYear), 'left')
            ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
            ->groupStart()
                ->where('s.is_active', 1)
                ->orWhere("LOWER(TRIM(e.enrollment_status)) IN ('denied','withdrawn','waitlist')", null, false)
                ->orWhere('e.is_withdrawn', 1)
            ->groupEnd()
            ->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',
            's.is_active',
            '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',
            'e.enrollment_status',
            'e.is_withdrawn',
        ])
        ->join('students s', 's.id = ss.student_id', 'inner')
        ->join('enrollments e', 'e.student_id = s.id AND e.school_year = ' . $this->db->escape($schoolYear), 'left')
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->groupStart()
            ->where('s.is_active', 1)
            ->orWhere("LOWER(TRIM(e.enrollment_status)) IN ('denied','withdrawn','waitlist')", null, false)
            ->orWhere('e.is_withdrawn', 1)
        ->groupEnd()
        ->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;
}

public 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('enrollments e', 'e.student_id = s.id AND e.school_year = ' . $this->db->escape($schoolYear), 'left')
        ->join('classSection cs', 'cs.class_section_id = ss.class_section_id', 'left')
        ->where('ss.school_year', $schoolYear)
        ->where('ss.student_id', $studentId)
        ->groupStart()
            ->where('s.is_active', 1)
            ->orWhere("LOWER(TRIM(e.enrollment_status)) IN ('denied','withdrawn','waitlist')", null, false)
            ->orWhere('e.is_withdrawn', 1)
        ->groupEnd()
        ->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;
}

public 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);
}

public 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;
}

public 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;
}

public 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'] ?? ''),
    ];
}

public 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 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 normalizeSemesterInput(?string $semester): ?string
{
    if (!is_string($semester)) {
        return null;
    }
    $trimmed = trim($semester);
    return $trimmed === '' ? null : $trimmed;
}

public 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] ?? '';
}

public 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));
}

public 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));
}

public 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;
}

/**
 * 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).
 */
public 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.
 */
public 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 */
public 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 */
public 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;
}
}