alrahma_sunday_school/app/Models/EnrollmentModel.php

<?php

namespace App\Models;

use CodeIgniter\Model;

class EnrollmentModel extends Model
{
    protected $table = 'enrollments';
    protected $primaryKey = 'id';

    protected $allowedFields = [
        'student_id',
        'class_section_id',
        'parent_id',
        'school_year',
        'enrollment_date',
        'enrollment_status',
        'withdrawal_date',
        'is_withdrawn',
        'admission_status',
        'semester',
        'created_at',
        'updated_at'
    ];

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

    protected $validationRules = [
        'student_id'        => 'required|integer',
        'class_section_id'  => 'permit_empty|integer',
        'parent_id'         => 'required|integer',
        'school_year'       => 'required|string|max_length[25]',
        'enrollment_date'   => 'required|valid_date',
        'withdrawal_date'   => 'permit_empty|valid_date',
        'is_withdrawn'      => 'permit_empty|in_list[0,1]',
        'enrollment_status' => 'required|in_list[admission under review,payment pending,enrolled,withdraw under review,refund pending,withdrawn]',
        'admission_status'  => 'required|in_list[pending,accepted,denied]',
        'semester'          => 'permit_empty|string|max_length[25]',
    ];

    protected $validationMessages = [
        'student_id' => [
            'required' => 'Student ID is required',
            'integer'  => 'Student ID must be an integer',
        ],
        'class_section_id' => [
            'integer' => 'Class Section ID must be an integer',
        ],
        'parent_id' => [
            'required' => 'Parent ID is required',
            'integer'  => 'Parent ID must be an integer',
        ],
        'school_year' => [
            'required'    => 'School year is required',
            'string'      => 'School year must be a string',
            'max_length'  => 'School year must not exceed 25 characters',
        ],
        'enrollment_date' => [
            'required'   => 'Enrollment date is required',
            'valid_date' => 'Enrollment date must be a valid date',
        ],
        'withdrawal_date' => [
            'valid_date' => 'Withdrawal date must be a valid date',
        ],
        'is_withdrawn' => [
            'in_list' => 'Withdrawal status must be 0 (not withdrawn) or 1 (withdrawn)',
        ],
        'enrollment_status' => [
            'required' => 'Enrollment status is required',
            'in_list'  => 'Enrollment status must be one of: admission under review, payment pending, enrolled, withdraw under review, refund pending, withdrawn',
        ],
        'admission_status' => [
            'required' => 'Admission status is required',
            'in_list'  => 'Admission status must be one of: pending, accepted, denied',
        ],
        'semester' => [
            'max_length' => 'Semester must not exceed 25 characters',
        ],
    ];

    protected $skipValidation = false;

    /**
     * Get all enrolled students (full student info) for a given parent.
     */
    public function getEnrolledStudents(int $parentId, string $schoolYear = null, string $semester = null): array
    {
        $builder = $this->select('students.*')
                        ->join('students', 'students.id = enrollments.student_id')
                        ->where('enrollments.parent_id', $parentId);

        if ($schoolYear) {
            $builder->where('enrollments.school_year', $schoolYear);
        }

        if ($semester) {
            $builder->where('enrollments.semester', $semester);
        }

        return $builder->findAll();
    }

    /**
     * Get student basic info (ID, name, grade) for a given parent where status is enrolled or Payment pending.
     */
    public function getenrolledStudentDetails(int $parentId, string $schoolYear = null): array
    {
        $builder = $this->db->table('enrollments')
            ->select('students.id, students.firstname, students.lastname, students.grade_level')
            ->join('students', 'students.id = enrollments.student_id')
            ->where('enrollments.parent_id', $parentId)
            ->groupStart()
                ->where('enrollments.enrollment_status', 'enrolled')
                ->orWhere('enrollments.enrollment_status', 'payment pending')
            ->groupEnd();

        if ($schoolYear) {
            $builder->where('enrollments.school_year', $schoolYear);
        }

        return $builder->get()->getResultArray();
    }

    /**
 * Get the latest enrollment_status for a student in a given school year.
 *
 * @param int    $studentId
 * @param string $schoolYear  e.g. "2025-2026"
 * @return string|null        e.g. "enrolled", "pending", "denied", or null if not found
 */
public function getEnrollmentStatus(int $studentId, string $schoolYear): ?string
{
    $row = $this->select('enrollment_status')
        ->where('student_id', $studentId)
        ->where('school_year', $schoolYear)
        // prefer most recently updated, then most recent enrollment_date, then newest id
        ->orderBy('updated_at', 'DESC')
        ->orderBy('enrollment_date', 'DESC')
        ->orderBy('id', 'DESC')
        ->first();

    return $row['enrollment_status'] ?? null;
}

}