<?php

namespace App\Services;

use App\Controllers\View\InvoiceController;
use App\Models\ClassSectionModel;
use App\Models\EnrollmentModel;
use App\Models\InvoiceModel;
use App\Models\RefundModel;
use App\Models\StudentClassModel;
use App\Models\StudentModel;
use App\Models\StudentSectionDistributionDraftModel;
use App\Models\UserModel;
use App\Support\Enrollment\DeliberationDecision;
use App\Support\Enrollment\EnrollmentEligibility;
use CodeIgniter\Events\Events;

class EnrollmentWithdrawalService
{
    protected $db;
    protected $studentModel;
    protected $enrollmentModel;
    protected $studentClassModel;
    protected $classSectionModel;
    protected $userModel;
    protected $invoiceModel;
    protected $refundModel;
    protected string $schoolYear = '';
    protected string $semester = '';

    public function __construct(
        \CodeIgniter\Database\BaseConnection $db,
        StudentModel $studentModel,
        EnrollmentModel $enrollmentModel,
        StudentClassModel $studentClassModel,
        ClassSectionModel $classSectionModel,
        UserModel $userModel,
        InvoiceModel $invoiceModel,
        RefundModel $refundModel
    ) {
        $this->db = $db;
        $this->studentModel = $studentModel;
        $this->enrollmentModel = $enrollmentModel;
        $this->studentClassModel = $studentClassModel;
        $this->classSectionModel = $classSectionModel;
        $this->userModel = $userModel;
        $this->invoiceModel = $invoiceModel;
        $this->refundModel = $refundModel;
    }

public function buildRoster(string $selectedYear, string $semester): array
{
    $this->schoolYear = $selectedYear;
    $this->semester = $semester;
    $this->syncReviewDecisionEnrollments($selectedYear);

        $students = $this->studentModel->getStudentsWithClassAndEnrollment($selectedYear);

        $removedPriorStatuses = $this->removedPriorYearStudentStatuses($selectedYear);
        service('studentYearStatus')->attachToStudents($students, $selectedYear);

        foreach ($students as &$s) {
            // ===== Ensure IDs needed by the modal =====
            $s['student_id'] = (int)($s['id'] ?? 0);
            $priorRemovedStatus = $removedPriorStatuses[$s['student_id']] ?? null;
            $s['removed_previous_year'] = $priorRemovedStatus !== null ? 'Yes' : 'No';
            $s['prior_removed_status'] = $priorRemovedStatus;

            // Prefer parent_id; fallback to secondparent_user_id if present
            if (empty($s['parent_id']) && !empty($s['secondparent_user_id'])) {
                $s['parent_id'] = (int)$s['secondparent_user_id'];
            } else {
                $s['parent_id'] = (int)($s['parent_id'] ?? 0);
            }

            // ===== Parent display + sort keys (keep existing behavior) =====
            $pf = trim((string)($s['parent_firstname'] ?? ''));
            $pl = trim((string)($s['parent_lastname']  ?? ''));

            // Fallback: if only a single full name exists
            if ($pf === '' && $pl === '' && !empty($s['parent_fullname'])) {
                $parts = preg_split('/\s+/', trim((string)$s['parent_fullname']), 2);
                $pf = $parts[0] ?? '';
                $pl = $parts[1] ?? '';
            }

            $s['parent_label'] = trim($pf . ' ' . $pl);
            $s['parent_sort']  = trim(($pl !== '' ? $pl : $pf) . ' ' . $pf);

            if ($s['parent_label'] === '') {
                $s['parent_label'] = 'Unknown Parent';
                $s['parent_sort']  = 'ZZZ Unknown Parent';
            }

            // ===== New-student flags (year-scoped) =====
            $s['is_new'] = (int) ($s['is_new'] ?? 1) === 1 ? 1 : 0;
            $s['new_student'] = $s['is_new'] === 1 ? 'Yes' : 'No';

            // ===== Admission override =====
            // Enrollment status for selected year
            $statusForYear = $this->enrollmentModel->getEnrollmentStatus((int)$s['student_id'], $selectedYear);
            if (!empty($priorRemovedStatus)) {
                $s['enrollment_status'] = $priorRemovedStatus;
            } elseif (!empty($statusForYear)) {
                $s['enrollment_status'] = $statusForYear;
            } elseif (($s['admission_status'] ?? null) === 'denied') {
                $s['enrollment_status'] = 'denied';
            } else {
                $s['enrollment_status'] = 'admission under review';
                $s['admission_status'] = 'pending';
            }

            // ===== Class section name for the selected year =====
            $name = $this->studentClassModel->getClassSectionsByStudentId((int)$s['student_id'], $selectedYear);
            $s['class_section'] = $name ?: 'Class not Assigned';
            $calculatedAge = EnrollmentEligibility::ageOnSeptemberFirst($s['dob'] ?? null, $selectedYear);
            $s['age'] = $calculatedAge ?? ($s['age'] ?? null);

            // ===== Sortable registration date (for data-order in view) =====
            $s['registration_date_order'] = !empty($s['registration_date'])
                ? date('Y-m-d', strtotime($s['registration_date']))
                : '';
        }
        unset($s); // break reference

        // ===== Sort by parent, then student (lastname, firstname) =====
        usort($students, function (array $a, array $b) {
            $pa = $a['parent_sort'] ?? '';
            $pb = $b['parent_sort'] ?? '';
            if (strcasecmp($pa, $pb) === 0) {
                $la = $a['lastname']  ?? '';
                $lb = $b['lastname']  ?? '';
                $cmp = strcasecmp($la, $lb);
                if ($cmp !== 0) return $cmp;
                return strcasecmp($a['firstname'] ?? '', $b['firstname'] ?? '');
            }
            return strcasecmp($pa, $pb);
        });

        $classes = $this->enrollmentClassOptions((string)$selectedYear);

    return [
        'students' => $students,
        'classes' => $classes,
        'selectedYear' => $selectedYear,
    ];
}

/**
 * Show only newly registered students, with contact modal support.
 *
 * Route example:
 *   $routes->get('admin/enrollment/new-students', 'EnrollmentController::showNewStudents', ['filter' => 'auth:view_new_students']);
 */
public function newStudents(string $schoolYear): array
{
    $rows = $this->studentModel->getStudentsWithParentsAndEmergency($schoolYear, 1);

    $newStudents = [];
    foreach ($rows as $r) {
        $r['new_student']   = 'Yes';
        $classSection = $this->studentClassModel->getClassSectionsByStudentId($r['id'], $schoolYear);
        $enrollmentstatus = $this->enrollmentModel->getEnrollmentStatus($r['id'], $schoolYear);
        // robust default
        $r['class_section'] = (isset($classSection) && trim((string)$classSection) !== '')
            ? $classSection
            : 'Class not Assigned';

        $r['is_new'] = (int) ($r['is_new'] ?? 1) === 1 ? 1 : 0;
        $r['new_student'] = $r['is_new'] === 1 ? "Yes" : "No";
        $r['modalIdContact'] = 'contact_' . (int)($r['id'] ?? 0);
        $r['enrollment_status'] = $enrollmentstatus;

        // ✅ Format registration_date for display
        if (!empty($r['registration_date'])) {
            try {
                $r['registration_date'] = (new \DateTime($r['registration_date']))->format('Y-m-d');
            } catch (\Throwable $e) {
                $r['registration_date'] = '';
            }
        }

        // Age comes directly from DB (already stored in students.age)
        $newStudents[] = $r;
    }

    return [
        'new_students' => $newStudents,
        'total_new'    => count($newStudents),
    ];
}

//update enrollment status
public function updateStatuses(?array $enrollmentStatuses, string $schoolYear, string $semester, ?int $performedBy): array
{
    $enrollmentStatusService = \Config\Services::enrollmentStatus(false);
    $performedBy = $performedBy ?: ((int) (session()->get('user_id') ?? 0) ?: null);
    $this->schoolYear = $schoolYear;
    $this->semester = $semester;

    if (empty($enrollmentStatuses)) {
        return ['ok' => false, 'message' => 'No enrollment statuses were submitted.'];
    }

    $this->db->transStart();

    try {
        $errors = [];

        // For batching emails: parent -> status -> [students...]
        $groupsByParentStatus = []; // [parent_id][status][] = ['student_id'=>, 'student_name'=>]
        $parentInfo           = []; // [parent_id] = ['user_id','email','firstname','lastname']
        $withdrawalPreviewEnrollmentIds = []; // enrollment_id => parent_id
        $refundAmountByParent = []; // parent_id => preview amount for notification context

        $validStatuses = EnrollmentStatusService::VALID_STATUSES;
        $transitionService = service('enrollmentTransition');
        $advanceStatuses = $transitionService->statusesRequiringEnrollmentEligibility();

        foreach ($enrollmentStatuses as $studentId => $newEnrollmentStatus) {
            if (!in_array($newEnrollmentStatus, $validStatuses, true)) {
                $errors[] = "Invalid enrollment status '$newEnrollmentStatus' for student ID $studentId.";
                continue;
            }

            // Map admission_status based on the desired new status (needed for create-or-update)
            if ($newEnrollmentStatus === 'denied') {
                $admissionStatus = 'denied';
            } elseif (in_array($newEnrollmentStatus, ['enrolled', 'payment pending'], true)) {
                $admissionStatus = 'accepted';
            } else {
                $admissionStatus = 'pending';
            }

            // Current enrollment row
            $enrollmentRow = $this->db->table('enrollments')
                ->where('student_id', $studentId)
                ->where('school_year', $this->schoolYear)
                ->get()
                ->getRowArray();

            // If no enrollment found for this student/year, create one so invoice generation has data
            if (!$enrollmentRow) {
                $stu = $this->studentModel->find((int)$studentId) ?? [];
                $parentId = (int)($stu['parent_id'] ?? ($stu['secondparent_user_id'] ?? 0));
                if (!$parentId) {
                    $errors[] = "No parent ID found for student ID $studentId.";
                    continue;
                }

                if (in_array($newEnrollmentStatus, $advanceStatuses, true)) {
                    $advance = $transitionService->evaluateEnrollmentAdvance($parentId, (int) $studentId, $this->schoolYear, 'admin');
                    if (! $transitionService->adminMayAdvanceEnrollment($advance)) {
                        $transitionService->logEnrollmentBlock($advance, 'admin_updateStatuses_create', $parentId, $performedBy);
                        $errors[] = "Student ID $studentId cannot be advanced to '$newEnrollmentStatus': "
                            . (string) ($advance['primary_parent_message'] ?? 'Enrollment eligibility check failed.');
                        continue;
                    }
                }

                $isWithdrawn = in_array($newEnrollmentStatus, ['withdrawn', 'refund pending', 'withdraw under review'], true) ? 1 : 0;

                $result = $enrollmentStatusService->upsertStatus([
                    'student_id'        => (int)$studentId,
                    'parent_id'         => $parentId,
                    'school_year'       => (string)$this->schoolYear,
                    'semester'          => (string)$this->semester,
                    'enrollment_date'   => local_date(utc_now(), 'Y-m-d'),
                    'is_withdrawn'      => $isWithdrawn,
                    'enrollment_status' => $newEnrollmentStatus,
                    'admission_status'  => $admissionStatus,
                    'created_at'        => utc_now(),
                    'updated_at'        => utc_now(),
                ], $performedBy, 'admin_enrollment_withdrawal_handler');

                if ((int) ($result['id'] ?? 0) <= 0) {
                    $errors[] = "Failed to create enrollment for student ID $studentId.";
                    continue;
                }

                // Group this newly-created change for notifications
                $studentRow  = $this->studentModel->find($studentId) ?? [];
                $studentName = trim(($studentRow['firstname'] ?? '') . ' ' . ($studentRow['lastname'] ?? '')) ?: "Student #{$studentId}";

                if (!isset($parentInfo[$parentId])) {
                    $p = $this->userModel->find($parentId) ?? [];
                    $parentInfo[$parentId] = [
                        'user_id'   => $p['id']        ?? $parentId,
                        'email'     => $p['email']     ?? null,
                        'firstname' => $p['firstname'] ?? '',
                        'lastname'  => $p['lastname']  ?? '',
                    ];
                }
                $groupsByParentStatus[$parentId][$newEnrollmentStatus][] = [
                    'student_id'   => (int) $studentId,
                    'student_name' => $studentName,
                ];

                if ($newEnrollmentStatus === 'refund pending') {
                    $withdrawalPreviewEnrollmentIds[(int) $result['id']] = $parentId;
                }

                log_message('info', "Created enrollment for student ID {$studentId} with status {$newEnrollmentStatus} and admission {$admissionStatus}.");
                if (in_array($newEnrollmentStatus, ['payment pending', 'enrolled'], true)) {
                    $this->applyDistributionDraftToStudentClass((int)$studentId, (string)$this->schoolYear);
                }
                continue; // go to next student
            }

            $oldStatus = $enrollmentRow['enrollment_status'] ?? null;
            $parentId  = $enrollmentRow['parent_id'] ?? null;

            if (!$parentId) {
                $errors[] = "No parent ID found for student ID $studentId.";
                continue;
            }

            // admissionStatus computed above

            if ($oldStatus === $newEnrollmentStatus) {
                $enrollmentStatusService->upsertStatus([
                    'id'                => (int) $enrollmentRow['id'],
                    'student_id'        => (int) $studentId,
                    'parent_id'         => (int) $parentId,
                    'school_year'       => (string) $this->schoolYear,
                    'semester'          => (string) ($enrollmentRow['semester'] ?? $this->semester),
                    'enrollment_status' => $newEnrollmentStatus,
                    'admission_status'  => $admissionStatus,
                    'updated_at'        => utc_now(),
                ], $performedBy, 'admin_enrollment_status_repair');
                log_message('debug', "No status change for student {$studentId} ({$oldStatus}); repaired activity flag.");
                if (in_array($newEnrollmentStatus, ['payment pending', 'enrolled'], true)) {
                    $this->applyDistributionDraftToStudentClass((int)$studentId, (string)$this->schoolYear);
                }
                continue;
            }

            if ($oldStatus !== $newEnrollmentStatus && in_array($newEnrollmentStatus, $advanceStatuses, true)) {
                $advance = $transitionService->evaluateEnrollmentAdvance((int) $parentId, (int) $studentId, $this->schoolYear, 'admin');
                if (! $transitionService->adminMayAdvanceEnrollment($advance)) {
                    $transitionService->logEnrollmentBlock($advance, 'admin_updateStatuses', (int) $parentId, $performedBy);
                    $errors[] = "Student ID $studentId cannot be advanced to '$newEnrollmentStatus': "
                        . (string) ($advance['primary_parent_message'] ?? 'Enrollment eligibility check failed.');
                    continue;
                }
            }

            $result = $enrollmentStatusService->upsertStatus([
                'id'                => (int) $enrollmentRow['id'],
                'student_id'        => (int) $studentId,
                'parent_id'         => (int) $parentId,
                'school_year'       => (string) $this->schoolYear,
                'semester'          => (string) ($enrollmentRow['semester'] ?? $this->semester),
                'enrollment_status' => $newEnrollmentStatus,
                'admission_status'  => $admissionStatus,
                'updated_at'        => utc_now(),
            ], $performedBy, 'admin_enrollment_withdrawal_handler');

            if ((int) ($result['id'] ?? 0) <= 0) {
                $errors[] = "Failed to update enrollment for student ID $studentId.";
                continue;
            }

            log_message('info', "Updated enrollment for student ID $studentId: {$oldStatus} → {$newEnrollmentStatus} (admission: {$admissionStatus})");
            if (in_array($newEnrollmentStatus, ['payment pending', 'enrolled'], true)) {
                $this->applyDistributionDraftToStudentClass((int)$studentId, (string)$this->schoolYear);
            }

            // Student name
            $studentRow  = $this->studentModel->find($studentId);
            $studentName = trim(($studentRow['firstname'] ?? '') . ' ' . ($studentRow['lastname'] ?? '')) ?: "Student #{$studentId}";

            // Cache parent info once
            if (!isset($parentInfo[$parentId])) {
                $p = $this->userModel->find($parentId) ?? [];
                $parentInfo[$parentId] = [
                    'user_id'   => $p['id']        ?? $parentId, // assuming parent_id == user_id
                    'email'     => $p['email']     ?? null,
                    'firstname' => $p['firstname'] ?? '',
                    'lastname'  => $p['lastname']  ?? '',
                ];
            }

            // Group by parent & status for minimal emails
            $groupsByParentStatus[$parentId][$newEnrollmentStatus][] = [
                'student_id'   => (int) $studentId,
                'student_name' => $studentName,
            ];

            // Mark for refund calc
            if ($newEnrollmentStatus === 'refund pending') {
                $withdrawalPreviewEnrollmentIds[(int) $enrollmentRow['id']] = (int) $parentId;
            }
        }

        $this->db->transComplete();

        if (!$this->db->transStatus()) {
            return ['ok' => false, 'message' => 'A database error occurred. Changes were rolled back.'];
        }

        foreach ($withdrawalPreviewEnrollmentIds as $enrollmentId => $pid) {
            try {
                $calculation = service('withdrawalFinancial')->preview((int) $enrollmentId, $performedBy);
                $refundAmountByParent[(int) $pid] = ((int) ($calculation['new_refund_request_cents'] ?? 0)) / 100;
            } catch (\Throwable $e) {
                $errors[] = 'Withdrawal calculation preview failed for enrollment #' . (int) $enrollmentId . ': ' . $e->getMessage();
            }
        }

        // === AFTER COMMIT: fire specific events, batched per parent/status ===
        $eventMap = [
            'admission under review' => 'admissionUnderReview',
            'review & decision'      => 'admissionUnderReview',
            'payment pending'        => 'paymentPending',
            'enrolled'               => 'studentEnrolled',
            'withdraw under review'  => 'withdrawUnderReview',
            'refund pending'         => 'refundPending',
            'withdrawn'              => 'withdrawn',
            'denied'                 => 'denied',
            'waitlist'               => 'waitlist',
        ];

        foreach ($groupsByParentStatus as $pid => $byStatus) {
            // Common parent data
            $p = $parentInfo[$pid] ?? ['user_id' => $pid, 'email' => null, 'firstname' => '', 'lastname' => ''];

            // Fetch invoice once for this parent (for payment pending email data if needed)
            $invoice = $this->invoiceModel->where('parent_id', $pid)
                ->where('school_year', $this->schoolYear)
                ->orderBy('created_at', 'DESC')
                ->first();

            foreach ($byStatus as $status => $studentsArr) {
                if (empty($eventMap[$status])) {
                    continue; // unknown mapping
                }

                // Build second arg: student list
                $studentData = [];
                foreach ($studentsArr as $s) {
                    $studentData[] = ['name' => $s['student_name'], 'student_id' => $s['student_id']];
                }

                // Parent payload (first arg)
                $parentData = [
                    'user_id'     => $p['user_id'],
                    'email'       => $p['email'],
                    'firstname'   => $p['firstname'],
                    'lastname'    => $p['lastname'],
                    'school_year' => $this->schoolYear,
                    'portalLink'  => base_url('/login'),
                ];

                // Enrich with status-specific fields
                if ($status === 'payment pending') {
                    if ($invoice) {
                        $parentData['amount']   = (float) ($invoice['balance'] ?? $invoice['amount_due'] ?? $invoice['total_amount'] ?? 0);
                        $parentData['due_date'] = $invoice['due_date'] ?? null;
                    }
                } elseif ($status === 'refund pending') {
                    $parentData['amount'] = $refundAmountByParent[$pid] ?? null;
                }

                $eventName = $eventMap[$status];

                log_message('info', "Triggering event '{$eventName}' for parent {$pid} with " . count($studentData) . " student(s).");
                Events::trigger($eventName, $parentData, $studentData);
            }
        }

        // === Server-side safety net: generate/update invoices for parents whose statuses require it ===
        try {
            $needsInvoiceFor = ['payment pending', 'enrolled'];
            $invCtl = new InvoiceController();
            foreach ($groupsByParentStatus as $pid => $byStatus) {
                $statuses = array_keys($byStatus);
                $requires = array_intersect($statuses, $needsInvoiceFor);
                if (!empty($requires)) {
                    // Best-effort; ignore response object
                    try {
                        $invCtl->generateInvoice((string)$pid);
                    } catch (\Throwable $e) {
                        log_message('error', 'Invoice fallback generation failed for parent {pid}: {err}', ['pid' => $pid, 'err' => $e->getMessage()]);
                    }
                }
            }
        } catch (\Throwable $e) {
            log_message('error', 'Invoice fallback block error: ' . $e->getMessage());
        }

        if (!empty($errors)) {
            return ['ok' => false, 'message' => implode(' ', $errors)];
        }

        return ['ok' => true, 'message' => 'Enrollment statuses updated and notifications sent.'];
    } catch (\Throwable $e) {
        $this->db->transRollback();
        log_message('error', 'Enrollment withdrawal error: ' . $e->getMessage());
        return ['ok' => false, 'message' => 'An unexpected error occurred while processing enrollments.'];
    }
}

private function getPreviousSchoolYear(string $schoolYear): string
{
    $schoolYear = trim($schoolYear);
    if ($schoolYear === '') {
        return '';
    }

    if (preg_match('/^(\d{4})\s*-\s*(\d{4})$/', $schoolYear, $m)) {
        return ((int)$m[1] - 1) . '-' . ((int)$m[2] - 1);
    }

    if (preg_match('/^(\d{4})\s*-\s*(\d{2})$/', $schoolYear, $m)) {
        $start = (int)$m[1] - 1;
        $end = (int)$m[2] - 1;
        if ($end < 0) {
            $end += 100;
        }
        return sprintf('%04d-%02d', $start, $end);
    }

    if (preg_match('/^\d{4}$/', $schoolYear)) {
        return (string)((int)$schoolYear - 1);
    }

    return '';
}

private function getSchoolYearStartYear(string $schoolYear): ?int
{
    $schoolYear = trim($schoolYear);
    if ($schoolYear === '') {
        return null;
    }

    if (preg_match('/^(\d{4})\s*-\s*(\d{4})$/', $schoolYear, $m)) {
        return (int)$m[1];
    }

    if (preg_match('/^(\d{4})\s*-\s*(\d{2})$/', $schoolYear, $m)) {
        return (int)$m[1];
    }

    if (preg_match('/^\d{4}$/', $schoolYear)) {
        return (int)$schoolYear;
    }

    return null;
}

public function syncReviewDecisionEnrollments(string $selectedYear): void
{
    $selectedYear = trim($selectedYear);
    $sourceYear = $this->getPreviousSchoolYear($selectedYear);
    if ($selectedYear === '' || $sourceYear === '' || ! $this->db->tableExists('enrollments')) {
        return;
    }

    $studentIds = $this->sourceYearStudentIds($sourceYear);
    if ($studentIds === []) {
        return;
    }

    $transitionService = service('enrollmentTransition');
    $now = utc_now();

    foreach ($studentIds as $studentId) {
        try {
            $evaluation = $transitionService->evaluate((int) $studentId, $sourceYear, $selectedYear, 'parent');
        } catch (\Throwable $e) {
            log_message('error', 'Review & Decision enrollment sync evaluation failed for student {studentId}: {message}', [
                'studentId' => $studentId,
                'message'   => $e->getMessage(),
            ]);
            continue;
        }

        if (! $this->needsReviewDecisionEnrollment($evaluation)) {
            continue;
        }

        $student = $this->studentModel->find((int) $studentId);
        if (! is_array($student)) {
            continue;
        }

        $parentId = (int) ($student['parent_id'] ?? ($student['secondparent_user_id'] ?? 0));
        if ($parentId <= 0) {
            log_message('warning', 'Review & Decision enrollment sync skipped student {studentId}: no parent ID.', [
                'studentId' => $studentId,
            ]);
            continue;
        }

        $existing = $this->db->table('enrollments')
            ->select('id, enrollment_status')
            ->where('student_id', (int) $studentId)
            ->where('school_year', $selectedYear)
            ->orderBy('updated_at', 'DESC')
            ->orderBy('id', 'DESC')
            ->limit(1)
            ->get()
            ->getRowArray();

        if ($existing !== null) {
            $existingStatus = (string) ($existing['enrollment_status'] ?? '');
            if ($existingStatus === 'review & decision' || ! in_array($existingStatus, ['', 'admission under review'], true)) {
                continue;
            }
        }

        $payload = [
            'student_id'                 => (int) $studentId,
            'parent_id'                  => $parentId,
            'school_year'                => $selectedYear,
            'semester'                   => (string) $this->semester,
            'source_school_year'         => $sourceYear,
            'deliberation_decision'      => $evaluation['deliberation_decision'] ?? null,
            'source_grade_id'            => $evaluation['source_grade_id'] ?? null,
            'assigned_grade_id'          => $evaluation['assigned_grade_id'] ?? null,
            'source_class_section_id'    => $evaluation['source_class_section_id'] ?? null,
            'assigned_class_section_id'  => $evaluation['assigned_class_section_id'] ?? null,
            'placement_status'           => $evaluation['placement_status'] ?? 'not_created',
            'age_reference_date'         => $evaluation['age_reference_date'] ?? null,
            'age_on_reference_date'      => $evaluation['age_on_reference_date'] ?? null,
            'adult_student'              => ! empty($evaluation['adult_student']) ? 1 : 0,
            'parent_enrollment_allowed'  => ! empty($evaluation['parent_enrollment_allowed']) ? 1 : 0,
            'student_self_enrollment_allowed' => ! empty($evaluation['student_self_enrollment_allowed']) ? 1 : 0,
            'exception_required'         => 1,
            'exception_reason'           => implode(', ', array_filter(array_column($evaluation['flags'] ?? [], 'flag_type'))) ?: implode(' ', array_map('strval', $evaluation['blockers'] ?? [])),
            'enrollment_date'            => local_date(utc_now(), 'Y-m-d'),
            'enrollment_status'          => 'review & decision',
            'admission_status'           => 'pending',
            'is_withdrawn'               => 0,
            'updated_at'                 => $now,
        ];
        $payload = $this->filterEnrollmentPayloadByColumns($payload);

        if ($existing !== null) {
            $payload['id'] = (int) $existing['id'];
        } else {
            $payload['created_at'] = $now;
        }

        \Config\Services::enrollmentStatus(false)->upsertStatus(
            $this->filterEnrollmentPayloadByColumns($payload),
            (int) (session()->get('user_id') ?? 0) ?: null,
            'admin_review_decision_enrollment'
        );
    }
}

private function needsReviewDecisionEnrollment(array $evaluation): bool
{
    $decision = (string) ($evaluation['deliberation_decision'] ?? '');

    if (in_array($decision, [
        DeliberationDecision::EXPELLED,
        DeliberationDecision::WITHDRAWN,
        DeliberationDecision::DEFERRED_DECISION,
    ], true)) {
        return true;
    }

    $hasSourceAssignment = (int) ($evaluation['source_class_section_id'] ?? 0) > 0
        || (int) ($evaluation['source_grade_id'] ?? 0) > 0;

    return $decision === ''
        && $hasSourceAssignment
        && array_filter($evaluation['blockers'] ?? []) !== [];
}

private function sourceYearStudentIds(string $sourceYear): array
{
    $studentIds = [];

    foreach (['student_class', 'enrollments', 'student_decisions'] as $table) {
        if (! $this->db->tableExists($table) || ! $this->db->fieldExists('student_id', $table)) {
            continue;
        }

        $yearColumn = match ($table) {
            'student_class', 'student_decisions' => 'school_year',
            default => 'school_year',
        };

        if (! $this->db->fieldExists($yearColumn, $table)) {
            continue;
        }

        $rows = $this->db->table($table)
            ->select('student_id')
            ->where($yearColumn, $sourceYear)
            ->where('student_id IS NOT NULL', null, false)
            ->get()
            ->getResultArray();

        foreach ($rows as $row) {
            $studentId = (int) ($row['student_id'] ?? 0);
            if ($studentId > 0) {
                $studentIds[$studentId] = true;
            }
        }
    }

    return array_keys($studentIds);
}

private function filterEnrollmentPayloadByColumns(array $payload): array
{
    foreach (array_keys($payload) as $column) {
        if (! $this->db->fieldExists($column, 'enrollments')) {
            unset($payload[$column]);
        }
    }

    return $payload;
}

private function enrollmentClassOptions(string $selectedYear): array
{
    $select = ['id', 'class_section_id', 'class_section_name'];
    $hasSchoolYear = $this->db->fieldExists('school_year', 'classSection');
    $hasSemester = $this->db->fieldExists('semester', 'classSection');

    if ($hasSchoolYear) {
        $select[] = 'school_year';
    }
    if ($hasSemester) {
        $select[] = 'semester';
    }

    $query = $this->classSectionModel
        ->select(implode(', ', $select))
        ->orderBy('class_section_name', 'ASC');

    if ($hasSchoolYear && $selectedYear !== '') {
        $query->where('school_year', $selectedYear);
    }
    if ($hasSemester) {
        $query->where('semester', (string)$this->semester);
    }

    $classes = $query->findAll();

    if (! empty($classes) || (! $hasSchoolYear && ! $hasSemester)) {
        return $classes;
    }

    return $this->classSectionModel
        ->select('id, class_section_id, class_section_name')
        ->orderBy('class_section_name', 'ASC')
        ->findAll();
}

private function removedPriorYearStudentStatuses(string $selectedYear): array
{
    $selectedStartYear = $this->getSchoolYearStartYear($selectedYear);
    if ($selectedStartYear === null || ! $this->db->tableExists('enrollments')) {
        return [];
    }

    $select = ['student_id', 'school_year'];
    $hasIsWithdrawn = $this->db->fieldExists('is_withdrawn', 'enrollments');
    $hasEnrollmentStatus = $this->db->fieldExists('enrollment_status', 'enrollments');
    $hasAdmissionStatus = $this->db->fieldExists('admission_status', 'enrollments');

    if ($hasIsWithdrawn) {
        $select[] = 'is_withdrawn';
    }
    if ($hasEnrollmentStatus) {
        $select[] = 'enrollment_status';
    }
    if ($hasAdmissionStatus) {
        $select[] = 'admission_status';
    }

    if (! $hasIsWithdrawn && ! $hasEnrollmentStatus && ! $hasAdmissionStatus) {
        return [];
    }

    $builder = $this->db->table('enrollments')
        ->select(implode(', ', $select))
        ->where('student_id IS NOT NULL', null, false)
        ->where('school_year IS NOT NULL', null, false)
        ->groupStart();

    $hasRemovalCondition = false;
    if ($hasIsWithdrawn) {
        $builder->orWhere('is_withdrawn', 1);
        $hasRemovalCondition = true;
    }

    if ($hasEnrollmentStatus) {
        $builder->orWhereIn('enrollment_status', ['withdrawn', 'denied']);
        $hasRemovalCondition = true;
    }

    if ($hasAdmissionStatus) {
        $builder->orWhere('admission_status', 'denied');
        $hasRemovalCondition = true;
    }

    $builder->groupEnd();
    if (! $hasRemovalCondition) {
        return [];
    }

    $removedPriorStatuses = [];
    foreach ($builder->get()->getResultArray() as $row) {
        $rowYear = $this->getSchoolYearStartYear((string)($row['school_year'] ?? ''));
        $studentId = (int)($row['student_id'] ?? 0);
        if ($studentId <= 0 || $rowYear === null || $rowYear >= $selectedStartYear) {
            continue;
        }

        $status = $this->priorRemovedEnrollmentStatus($row);
        if ($status === null) {
            continue;
        }

        if (
            !isset($removedPriorStatuses[$studentId])
            || $rowYear > (int)$removedPriorStatuses[$studentId]['year']
        ) {
            $removedPriorStatuses[$studentId] = [
                'year' => $rowYear,
                'status' => $status,
            ];
        }
    }

    $statusByStudentId = [];
    foreach ($removedPriorStatuses as $studentId => $row) {
        $statusByStudentId[(int)$studentId] = (string)$row['status'];
    }

    return $statusByStudentId;
}

private function priorRemovedEnrollmentStatus(array $row): ?string
{
    $enrollmentStatus = strtolower(trim((string)($row['enrollment_status'] ?? '')));
    $admissionStatus = strtolower(trim((string)($row['admission_status'] ?? '')));

    if ($enrollmentStatus === 'denied' || $admissionStatus === 'denied') {
        return 'denied';
    }

    if ($enrollmentStatus === 'withdrawn' || (int)($row['is_withdrawn'] ?? 0) === 1) {
        return 'withdrawn';
    }

    return null;
}

private function priorYearStudentIds(string $selectedYear): array
{
    $selectedStartYear = $this->getSchoolYearStartYear($selectedYear);
    if ($selectedStartYear === null) {
        return [];
    }

    $studentIds = [];
    foreach (['enrollments', 'student_class'] as $table) {
        if (! $this->db->tableExists($table) || ! $this->db->fieldExists('school_year', $table)) {
            continue;
        }

        $rows = $this->db->table($table)
            ->select('student_id, school_year')
            ->where('student_id IS NOT NULL', null, false)
            ->where('school_year IS NOT NULL', null, false)
            ->get()
            ->getResultArray();

        foreach ($rows as $row) {
            $rowStartYear = $this->getSchoolYearStartYear((string) ($row['school_year'] ?? ''));
            $studentId = (int) ($row['student_id'] ?? 0);
            if ($studentId > 0 && $rowStartYear !== null && $rowStartYear < $selectedStartYear) {
                $studentIds[$studentId] = true;
            }
        }
    }

    return $studentIds;
}

private function applyDistributionDraftToStudentClass(int $studentId, string $year): void
{
    try {
        $draftModel = new StudentSectionDistributionDraftModel();
        $draft = $draftModel->where('student_id', $studentId)
            ->where('school_year', $year)
            ->where('status', 'pending')
            ->first();

        if (!$draft) {
            return;
        }

        $enrollment = $this->db->table('enrollments')
            ->select('class_section_id')
            ->where('student_id', $studentId)
            ->where('school_year', $year)
            ->orderBy('updated_at', 'DESC')
            ->orderBy('id', 'DESC')
            ->limit(1)
            ->get()
            ->getRowArray();
        if ((int)($enrollment['class_section_id'] ?? 0) > 0) {
            return;
        }

        $targetSectionId = (int)($draft['class_section_id'] ?? 0);
        if ($targetSectionId <= 0) {
            return;
        }

        $studentClass = new StudentClassModel();
        $exists = $studentClass->where('student_id', $studentId)
            ->where('school_year', $year)
            ->first();

        $payload = [
            'student_id'       => $studentId,
            'class_section_id' => $targetSectionId,
            'school_year'      => $year,
            'updated_by'       => (int)(session()->get('user_id') ?? 0) ?: null,
            'updated_at'       => utc_now(),
        ];

        if ($exists) {
            $studentClass->update((int)$exists['id'], $payload);
        } else {
            $payload['created_at'] = utc_now();
            $studentClass->insert($payload);
        }

        $this->db->table('enrollments')
            ->where('student_id', $studentId)
            ->where('school_year', $year)
            ->whereIn('enrollment_status', ['payment pending', 'enrolled'])
            ->update([
                'class_section_id' => $targetSectionId,
                'updated_at'       => utc_now(),
            ]);

        $this->db->table('promotion_queue')
            ->where('student_id', $studentId)
            ->where('school_year_to', $year)
            ->update([
                'to_class_section_id' => $targetSectionId,
                'status'              => 'applied',
                'updated_by'          => (int)(session()->get('user_id') ?? 0) ?: null,
                'updated_at'          => utc_now(),
            ]);

        $draftModel->update((int)$draft['id'], [
            'status'     => 'applied',
            'applied_at' => utc_now(),
            'updated_at' => utc_now(),
        ]);
    } catch (\Throwable $e) {
        log_message('error', 'applyDistributionDraftToStudentClass failed: ' . $e->getMessage());
    }
}
}