alrahma_sunday_school_api/app/Models/Enrollment.php

<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;

class Enrollment extends BaseModel
{
    protected $table = 'enrollments';

    // ✅ legacy: useTimestamps = true
    public $timestamps = true;

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

    protected $casts = [
        'student_id' => 'integer',
        'class_section_id' => 'integer',
        'parent_id' => 'integer',
        'is_withdrawn' => 'boolean',

        // change to 'datetime' if your columns are DATETIME
        'enrollment_date' => 'date',
        'withdrawal_date' => 'date',
    ];

    /* Optional relationships */
    public function student()
    {
        return $this->belongsTo(Student::class, 'student_id');
    }

    public function classSection()
    {
        return $this->belongsTo(ClassSection::class, 'class_section_id');
    }

    public function parent()
    {
        return $this->belongsTo(User::class, 'parent_id');
    }

    /* =========================
     * legacy method equivalents
     * ========================= */

    /**
     * Get all enrolled students (full student info) for a given parent.
     * Returns a collection of Student rows.
     */
    public static function getEnrolledStudents(int $parentId, ?string $schoolYear = null, ?string $semester = null)
    {
        $q = DB::table('enrollments')
            ->join('students', 'students.id', '=', 'enrollments.student_id')
            ->select('students.*')
            ->where('enrollments.parent_id', $parentId);

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

        if (! empty($semester)) {
            $q->where('enrollments.semester', $semester);
        }

        return $q->get();
    }

    /**
     * Get student basic info (ID, name, grade) for a given parent
     * where status is enrolled or payment pending.
     */
    public static function getEnrolledStudentDetails(int $parentId, ?string $schoolYear = null): array
    {
        $q = DB::table('enrollments')
            ->join('students', 'students.id', '=', 'enrollments.student_id')
            ->select('students.id', 'students.firstname', 'students.lastname', 'students.grade_level')
            ->where('enrollments.parent_id', $parentId)
            ->where(function ($w) {
                $w->where('enrollments.enrollment_status', 'enrolled')
                    ->orWhere('enrollments.enrollment_status', 'payment pending');
            });

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

        return $q->get()->map(fn ($r) => (array) $r)->all();
    }

    /**
     * Get the latest enrollment_status for a student in a given school year.
     */
    public static function getEnrollmentStatus(int $studentId, string $schoolYear): ?string
    {
        $row = static::query()
            ->select('enrollment_status')
            ->where('student_id', $studentId)
            ->where('school_year', $schoolYear)
            ->orderByDesc('updated_at')
            ->orderByDesc('enrollment_date')
            ->orderByDesc('id')
            ->first();

        return $row?->enrollment_status;
    }
}