<?php

namespace App\Services\Accounting;

use Carbon\Carbon;
use Illuminate\Http\Request;
use App\Constants\Accounting;
use App\Models\Hostel\RoomType;
use App\Models\Hostel\HostelRoom;
use App\Models\Accounting\FeeType;
use App\Models\Accounting\Invoice;
use App\Models\Transport\Stoppage;
use Illuminate\Support\Facades\DB;
use App\Models\Accounting\ClassFee;
use App\Models\Student\StudentInfo;
use App\Models\Hostel\StudentHostel;
use App\Models\Academic\AcademicYear;
use App\Models\Academic\StudentClass;
use App\Models\Accounting\StudentFee;
use App\Models\Accounting\InvoiceItem;
use App\Models\Scopes\BranchFilterScope;
use App\Models\Accounting\StudentDiscount;
use App\Models\Transport\StudentTransport;

class FeeSearchService
{
    public function getStudentFees(Request $request)
    {
        $studentIdNo = $request->student_id_no;
        $yearId = $request->year_id;
        $selectedMonth = $request->month ?? [date('n')];
        $years = AcademicYear::Latest()->pluck('year', 'id');

        // Extract student details
        $studentDetails = $studentIdNo ? $this->getStudentDetails($studentIdNo) : null;

        $studentInfoId = $studentDetails->id ?? null;

        if (!$studentDetails) {
            return [
                'student_details' => null,
                'years' => $years,
            ];
        }

        $student_name = "$studentDetails->first_name $studentDetails->last_name ($studentIdNo)";
        $classId = $studentDetails->student_class_id;

        // Extract selected fee types
        $selectedFeeTypes = $this->getSelectedFeeTypes($studentInfoId, $classId, $selectedMonth, $yearId);

        $grandTotal = collect($selectedFeeTypes)->sum('total_amount');

        $totalDue = collect($selectedFeeTypes)
            ->where('payment_status', 'Due')
            ->sum('total_amount');

        $totalDueMonth = collect($selectedFeeTypes)
            ->where('payment_status', 'Due')
            ->count();  
            
            // dd($totalDueMonth);

        $studentDiscount = StudentDiscount::withoutGlobalScope(BranchFilterScope::class)->where('student_id', $studentDetails->id)->first();


        // Extract transport fee
        $transport_fee = $this->getTransportFee($studentDetails->id);

        $transportFeeType = '';
        
        if($transport_fee){
            $transportFeeType = FeeType::where('code', Accounting::TRANSPORT)->first();
            $grandTotal += $transport_fee->monthly_cost * count($selectedMonth);
            $totalDue  += $transport_fee->monthly_cost * $totalDueMonth;
        }

        // Extract hostel fee
        $hostel_fee = $this->getHostelFee($studentDetails->id);

        $hostelFeeType = '';

        if($hostel_fee){
            $hostelFeeType = FeeType::where('code', Accounting::HOSTEL)->first();
            $grandTotal += $hostel_fee->cost * count($selectedMonth);
            $totalDue  += $hostel_fee->cost * $totalDueMonth;
        }

        // dd($totalDue);

        $totalDiscount = $studentDiscount ? calculateDiscount($totalDue, $studentDiscount->discount_type, $studentDiscount->amount, $totalDueMonth) : 0;
        
        $discount = $this->getDiscount($studentDetails->id);
        // dd($discount);
        // Extract invoices
        $invoices = $this->getInvoices($studentDetails->id, $yearId);
        // Extract invoice fee type IDs
        $invoiceFeeTypeIds = $this->getInvoiceFeeTypeIds($invoices);

        $paymentMethods = Accounting::PAYMENT_METHOD;
        // dd($transport_fee);

        return [
            'student_name' => "$student_name",
            'student_id_no' => $studentIdNo,
            'years' => $years,
            'selectedFeeTypes' => $selectedFeeTypes,
            'transportFeeType' => $transportFeeType,
            'hostelFeeType' => $hostelFeeType,
            'student_details' => $studentDetails,
            'discount' => $discount,
            'grandTotal' => $grandTotal,
            'totalDue' => $totalDue,
            'totalDiscount' => $totalDiscount,
            'invoice_fee_type_id' => $invoiceFeeTypeIds,
            'year_id' => $yearId,
            'selectedMonth' => $selectedMonth,
            'transport_fee' => $transport_fee,
            'hostel_fee' => $hostel_fee,
            'paymentMethods' => $paymentMethods,
        ];
    }

    private function getStudentDetails($studentIdNo)
    {
        return StudentInfo::with('branch', 'shift', 'section', 'studentClass')
            ->where('student_id_no', $studentIdNo)
            ->first();
    }

    private function getSelectedFeeTypes($studentInfoId, $classId, $months, $yearId)
    {
        // $query = ClassFee::with(['feeType'])
        //     ->where('student_class_id', $classId)
        //     ->where('academic_year_id', $yearId);

       

        // if ($months) {
        //     $query->whereIn('month', $months);
        // }
        // $results = $query->get();
        // dd($months);
        ///new

        $studentInfo = StudentInfo::where('id',$studentInfoId)->first();
        // $rows = DB::select("
        //         SELECT 
        //             t1.student_class_id,
        //             t1.academic_year_id,
        //             t1.branch_id,
        //             t1.month,
        //             MONTHNAME(STR_TO_DATE(t1.month, '%m')) AS month_name,
        //             t2.id AS fee_type_id,
        //             t2.code AS fee_type_name,
        //             t1.amount AS fee_type_amount,
        //             CASE 
        //                 WHEN EXISTS (
        //                     SELECT 1
        //                     FROM invoice_items t3
        //                     WHERE t3.student_id = $studentInfo->id
        //                     AND t3.fee_type_id = t1.fee_type_id
        //                     AND t3.academic_year_id = t1.academic_year_id
        //                     AND t3.month = t1.month
        //                 ) THEN 'Paid'
        //                 ELSE 'Due'
        //             END AS payment_status
        //         FROM class_fees t1
        //         LEFT JOIN fee_types t2 ON t1.fee_type_id = t2.id
        //         WHERE t1.student_class_id = $studentInfo->student_class_id
        //         AND t1.academic_year_id = $yearId 
        //         AND t1.branch_id = $studentInfo->branch_id
        //         AND t1.month = $months
        //         ORDER BY t1.month, t2.code
        //     ");



        $placeholders = implode(',', array_fill(0, count($months), '?'));

        $rows = DB::select("
            SELECT 
                t1.student_class_id,
                t1.academic_year_id,
                t1.branch_id,
                t1.month,
                MONTHNAME(STR_TO_DATE(t1.month, '%m')) AS month_name,
                t2.id AS fee_type_id,
                t2.code AS fee_type_name,
                t1.amount AS fee_type_amount,
                CASE 
                    WHEN EXISTS (
                        SELECT 1
                        FROM invoice_items t3
                        WHERE t3.student_id = ?
                        AND t3.fee_type_id = t1.fee_type_id
                        AND t3.academic_year_id = t1.academic_year_id
                        AND t3.month = t1.month
                    ) THEN 'Paid'
                    ELSE 'Due'
                END AS payment_status
            FROM class_fees t1
            LEFT JOIN fee_types t2 ON t1.fee_type_id = t2.id
            WHERE t1.student_class_id = ?
            AND t1.academic_year_id = ?
            AND t1.branch_id = ?
            AND t1.month IN ($placeholders)
            ORDER BY t1.month, t2.code
        ", array_merge(
            [$studentInfo->id],
            [
                $studentInfo->student_class_id,
                $yearId,
                $studentInfo->branch_id,
            ],
            $months
        ));

        $grouped = [];

        foreach ($rows as $row) {
            $student_class_id = $row->student_class_id;
            $academic_year_id = $row->academic_year_id;
            $branch_id = $row->branch_id;
            $month = $row->month; // use month number as key to avoid duplication
            $month_name = $row->month_name;
            $payment_status = $row->payment_status;

            if (!isset($grouped[$month])) {
                $grouped[$month] = [
                    'student_class_id' => $student_class_id,
                    'academic_year_id' => $academic_year_id,
                    'branch_id' => $branch_id,
                    'month' => $month,
                    'month_name' => $month_name,
                    'total_amount' => 0,
                    'payment_status' => $payment_status,
                    'fee_types' => [],
                ];
            }

            $grouped[$month]['fee_types'][] = [
                'fee_type_id' => $row->fee_type_id,
                'fee_type_name' => $row->fee_type_name,
                'fee_type_amount' => (float) $row->fee_type_amount,
            ];

            $grouped[$month]['total_amount'] += (float) $row->fee_type_amount;
        }

        $final = array_values($grouped);

        return $final;
    }



    private function getDiscount($studentId)
    {
        return StudentDiscount::where('student_id', $studentId)->first();
    }

    private function getTransportFee($studentId)
    {
        // $stoppage = '';
        // $student_transport = StudentTransport::where('student_id', $studentId)->first();
        // if($student_transport){
        //     $stoppage_id = $student_transport->stoppage_id;
        //     $stoppage = Stoppage::where('id', '=', $stoppage_id)->first();
        // }

        $student_transport = StudentTransport::where('student_id', $studentId)->first();
        return $student_transport;
    }

    private function getHostelFee($studentId)
    {
        $student_hostel = StudentHostel::where('student_id', $studentId)->first();
        // $room_type = '';
        // if($student_hostel){
        //     $hostel_room_id = $student_hostel->hostel_room_id;
        //     $hostel_room = HostelRoom::where('id', $hostel_room_id)->first();
        //     $room_type = RoomType::where('id', $hostel_room->room_type_id)->first();
        // }
        return $student_hostel;
    }

    private function getInvoices($studentId, $yearId)
    {
        return Invoice::where('student_info_id', $studentId)
            ->where('academic_year_id', $yearId)
            ->where('status', 'Paid')
            ->get();
    }

    private function getInvoiceFeeTypeIds($invoices)
    {
        $invoiceFeeTypeIds = [];
        foreach ($invoices as $invoice) {
            $invoiceItems = InvoiceItem::where('invoice_id', $invoice->id)->get();
            foreach ($invoiceItems as $invoiceItem) {
                array_push($invoiceFeeTypeIds, $invoiceItem->fee_type_id);
            }
        }
        return $invoiceFeeTypeIds;
    }

    public function filterDueFeesSummaryOLD(Request $request)
    {
        // dd($request->all());

        if (empty($request->all())) {
            return [
                'dueFees' => collect(),
                'student_name' => '',
            ];
        }

        $class_id = null;
        $student_id_no = $request->query('student_id_no');
        $student_class = $request->query('student_class');
        $month = $request->query('month');
        $section = $request->query('section');
        $studentInfo = [];
        if($student_id_no){
            $studentInfo = StudentInfo::where('student_id_no', $student_id_no)->first();
            $class_id = $request->query('section') ?? $studentInfo->student_class_id;
        }

        $academicYear = $request->query('academic_year');
        $currentYear = Carbon::now()->year;
        $fromDate = $month ? $currentYear . '-' . $month . '-01' : $currentYear . '-01-01';
        $toDate = $month ? $currentYear . '-' . $month . '-' . Carbon::now()->endOfMonth()->format('d') : $currentYear . '-12-31';
        $branch_id = auth()->user()->branch_id;


        
    
        $academicYear = $year ?? getDefaultAcademicYearValue();
     
        $year_id = AcademicYear::where('year', $academicYear)->value('id');

        $months = $month ? range($month, $month) : range(1, date('n'));

        $students = DB::table('student_infos as si')
            ->join('student_classes as sc', 'si.student_class_id', '=', 'sc.id')
            ->when($student_id_no, fn($q) => $q->where('si.student_id_no', $student_id_no))
            ->when($class_id, fn($q) => $q->where('si.student_class_id', $class_id))
            ->when($section, fn($q) => $q->where('si.section_id', $section))
            ->where('si.branch_id', $branch_id)
            ->select([
                'si.id as student_id',
                'si.name',
                'si.student_id_no',
                'sc.class_name',
                'si.student_class_id'
            ])
            ->get();

        $feeSummary = collect();

        foreach ($students as $student) {
            foreach ($months as $month) {
                $total = DB::table('class_fees')
                    ->where('branch_id', $branch_id)
                    ->where('academic_year_id', $year_id)
                    ->where('student_class_id', $student->student_class_id)
                    ->where('month', $month)
                    ->sum('amount');

                $paid = DB::table('student_fees')
                    ->where('branch_id', $branch_id)
                    ->where('academic_year_id', $year_id)
                    ->where('student_id', $student->student_id)
                    ->where('month', $month)
                    ->sum('total_amount');

                // Show row if any fee exists or any amount was paid
                if ($total == 0 && $paid == 0) {
                    continue;
                }

                $feeSummary->push((object)[
                    'student_id' => $student->student_id,
                    'name' => $student->name,
                    'student_id_no' => $student->student_id_no,
                    'class_name' => $student->class_name,
                    'month' => $month,
                    'total_amount' => number_format($total, 2, '.', ''),
                    'paid_amount' => number_format($paid, 2, '.', ''),
                ]);
            }
        }

        // dd($feeSummary);

        $student_name = "";
        if($student_id_no && $feeSummary->count() > 0){
            $student = $feeSummary->first();
            $student_name = "$student->name ($student_id_no)";
        }

        // dd($feeSummary, $student_name);
        return [
            'dueFees' => $feeSummary,
            'student_name' => $student_name,
        ];
        

        // old
        if (empty($request->all())) {
            return [
                'dueFees' => collect(),
                'student_name' => '',
            ];
        }

        
        $student_id_no = $request->query('student_id_no');
        $student_class = $request->query('student_class');
        $month = $request->query('month');
        $section = $request->query('section');
        $studentInfo = [];
        if($student_id_no){
            $studentInfo = StudentInfo::where('student_id_no', $student_id_no)->first();
        }

        $academicYear = $request->query('academic_year');
        $currentYear = Carbon::now()->year;
        $fromDate = $month ? $currentYear . '-' . $month . '-01' : $currentYear . '-01-01';
        $toDate = $month ? $currentYear . '-' . $month . '-' . Carbon::now()->endOfMonth()->format('d') : $currentYear . '-12-31';
        $branch_id = auth()->user()->branch_id;


        $classFeesQuery = DB::table('class_fees')
            ->join('fee_types as ft', 'class_fees.fee_type_id', '=', 'ft.id')
            ->join('student_classes as sc', 'class_fees.student_class_id', '=', 'sc.id')
            ->join('student_infos as si', 'class_fees.student_class_id', '=', 'si.student_class_id')
            ->join('sections as sec', 'si.section_id', '=', 'sec.id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('si.id', '=', 'ii.student_id')
                    ->whereColumn('class_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereNotIn('ft.code', ['Hostel', 'Transport'])
            ->when($studentInfo, function ($q) use ($studentInfo) {
                $q->where('si.id', '=', $studentInfo->id);
            })
            ->when($month, function ($q) use ($month) {
                $q->whereIn('class_fees.month', [$month]);
            })
            ->where('si.branch_id', $branch_id)
            ->where('class_fees.branch_id', $branch_id)
            ->where('class_fees.academic_year_id', $academicYear)
            ->when($student_class, function ($q) use ($student_class) {
                $q->where('class_fees.student_class_id', '=', $student_class);
            })
            ->when($section, function ($q) use ($section) {
                $q->where('si.section_id', '=', $section);
            })
            ->groupBy('si.id', 'si.name', 'si.student_id_no', 'sc.class_name')
            ->select([
                'si.id',
                'si.name',
                'si.student_id_no',
                'sc.class_name',
                DB::raw('SUM(class_fees.amount) as total_assigned_fee'),
                DB::raw('COALESCE(SUM(ii.amount), 0) as total_paid_fee'),
                DB::raw('SUM(class_fees.amount) - COALESCE(SUM(ii.amount), 0) as total_due_fee')
            ]);
        
        // Student-specific fees query (hostel and transport)
        $studentFeesQuery = DB::table('student_fees')
            ->join('fee_types as ft', 'student_fees.fee_type_id', '=', 'ft.id')
            ->join('student_infos as si', 'si.id', '=', 'student_fees.student_id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('student_fees.student_id', '=', 'ii.student_id')
                    ->whereColumn('student_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->join('student_classes as sc', 'si.student_class_id', '=', 'sc.id')
            ->whereIn('ft.code', ['Hostel', 'Transport'])   
            ->when($studentInfo, function ($q) use ($studentInfo) {
                $q->where('student_fees.student_id', '=', $studentInfo->id);
            })
            ->when($student_class, function ($q) use ($student_class) {
                $q->where('si.student_class_id', '=', $student_class);
            })
            ->when($section, function ($q) use ($section) {
                $q->where('si.section_id', '=', $section);
            })
            ->where('student_fees.status', 'active')
            ->groupBy('si.id', 'si.name', 'si.student_id_no', 'sc.class_name')
            ->select([
                'si.id',
                'si.name',
                'si.student_id_no',
                'sc.class_name',
                DB::raw('SUM(student_fees.amount) as total_assigned_fee'),
                DB::raw('COALESCE(SUM(ii.amount), 0) as total_paid_fee'),
                DB::raw('SUM(student_fees.amount) - COALESCE(SUM(ii.amount), 0) as total_due_fee')
            ]);

        // Combine both queries using UNION ALL
        $finalQuery = $classFeesQuery->unionAll($studentFeesQuery);
        
        // Get the total summary
        $feeSummary = DB::query()
            ->fromSub($finalQuery, 'combined_fees')
            ->groupBy('id', 'name', 'student_id_no', 'class_name')
            ->select([
                'id',
                'name',
                'student_id_no', 
                'class_name',
                DB::raw('SUM(total_assigned_fee) as total_assigned_fee'),
                DB::raw('SUM(total_paid_fee) as total_paid_fee'),
                DB::raw('SUM(total_due_fee) as total_due_fee')
            ])
            ->get();
        // dd($finalQuery, $feeSummary->count());
        $student_name = "";
        if($student_id_no && $feeSummary->count() > 0){
            $student = $feeSummary->first();
            $student_name = "$student->name ($student_id_no)";
        }

        // dd($feeSummary, $student_name);
        return [
            'dueFees' => $feeSummary,
            'student_name' => $student_name,
        ];
    }
    
    public function filterDueFeesSummary(Request $request)
    {
        // dd($request->all()); Accounting/DigitalPayment

        if (empty($request->all())) {
            return [
                'dueFees' => collect(),
                'student_name' => '',
            ];
        }

        $class_id = null;
        $student_id_no = $request->query('student_id_no');
        $student_class = $request->query('student_class');
        $month = $request->query('month');
        $section = $request->query('section');
        $studentInfo = [];
        if($student_id_no){
            $studentInfo = StudentInfo::where('student_id_no', $student_id_no)->first();
            $class_id = $request->query('section') ?? $studentInfo->student_class_id;
        }

        $academicYear = $request->query('academic_year');
        $currentYear = Carbon::now()->year;
        $fromDate = $month ? $currentYear . '-' . $month . '-01' : $currentYear . '-01-01';
        $toDate = $month ? $currentYear . '-' . $month . '-' . Carbon::now()->endOfMonth()->format('d') : $currentYear . '-12-31';
        $branch_id = auth()->user()->branch_id;
    
        $academicYear = $year ?? getDefaultAcademicYearValue();
     
        $year_id = AcademicYear::where('year', $academicYear)->value('id');

        $months = $month ? range($month, $month) : range(1, date('n'));

        $students = DB::table('student_infos as si')
            ->join('student_classes as sc', 'si.student_class_id', '=', 'sc.id')
            ->when($student_id_no, fn($q) => $q->where('si.student_id_no', $student_id_no))
            ->when($class_id, fn($q) => $q->where('si.student_class_id', $class_id))
            ->when($section, fn($q) => $q->where('si.section_id', $section))
            ->where('si.branch_id', $branch_id)
            ->select([
                'si.id as student_id',
                'si.name',
                'si.student_id_no',
                'sc.class_name',
                'si.student_class_id'
            ])
            ->get();

        $feeSummary = collect();

        foreach ($students as $student) {
            foreach ($months as $month) {
                $total = DB::table('class_fees')
                    ->where('branch_id', $branch_id)
                    ->where('academic_year_id', $year_id)
                    ->where('student_class_id', $student->student_class_id)
                    ->where('month', $month)
                    ->sum('amount');

                $paid = DB::table('student_fees')
                    ->where('branch_id', $branch_id)
                    ->where('academic_year_id', $year_id)
                    ->where('student_id', $student->student_id)
                    ->where('month', $month)
                    ->sum('total_amount');

                // Show row if any fee exists or any amount was paid
                if ($total == 0 && $paid == 0) {
                    continue;
                }

                $feeSummary->push((object)[
                    'student_id' => $student->student_id,
                    'name' => $student->name,
                    'student_id_no' => $student->student_id_no,
                    'class_name' => $student->class_name,
                    'month' => $month,
                    'total_amount' => number_format($total, 2, '.', ''),
                    'paid_amount' => number_format($paid, 2, '.', ''),
                ]);
            }
        }

        // dd($feeSummary);

        $student_name = "";
        if($student_id_no && $feeSummary->count() > 0){
            $student = $feeSummary->first();
            $student_name = "$student->name ($student_id_no)";
        }

        // dd($feeSummary, $student_name);
        return [
            'dueFees' => $feeSummary,
            'student_name' => $student_name,
        ];
    }

    public function filterDueFees(Request $request)
    {
        $student_id_no = $request->query('student_id_no');
        $class_id = $request->query('student_class');
        $section = $request->query('section');
        $academicYear = getDefaultAcademicYearValue();
        $fromDate = $academicYear . '-01-01';
        $toDate = $academicYear . '-12-31';

        $branch_id = auth()->user()->branch_id;
        $studentInfo = [];
        if($student_id_no){
            $studentInfo = StudentInfo::where('student_id_no', $student_id_no)->first();
        }

        $classFeesQuery = DB::table('class_fees')
            ->join('fee_types as ft', 'class_fees.fee_type_id', '=', 'ft.id')
            ->join('student_classes as sc', 'class_fees.student_class_id', '=', 'sc.id')
            ->join('student_infos as si', 'sc.id', '=', 'si.student_class_id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('si.id', '=', 'ii.student_id')
                    ->whereColumn('class_fees.fee_type_id', 'ii.fee_type_id')
                    ->where('ii.payment_date', '>=', $fromDate)
                    ->where('ii.payment_date', '<=', $toDate);
            })
            ->whereNotIn('ft.code', ['Hostel', 'Transport'])
            ->when($student_id_no, function ($q) use ($student_id_no) {
                $q->where('si.student_id_no', '=', $student_id_no);
            })
            ->when($class_id, function ($q) use ($class_id) {
                $q->where('class_fees.student_class_id', '=', $class_id);
            })
            ->when($section, function ($q) use ($section) {
                $q->where('si.section_id', '=', $section);
            })
            ->where('class_fees.branch_id', $branch_id)
            ->groupBy('ft.id', 'ft.title', 'ft.month', 'ft.code', 'class_fees.amount')
            ->havingRaw('class_fees.amount - COALESCE(SUM(ii.amount), 0) > 0')
            ->select([
                'ft.id as fee_type_id',
                'ft.title',
                'ft.month',
                'ft.code',
                'class_fees.amount as assigned_fee',
                DB::raw('COALESCE(SUM(ii.amount), 0) as paid_fee'),
                DB::raw('class_fees.amount - COALESCE(SUM(ii.amount), 0) as due_fee')
            ]);

        // Student-specific fees query for due fees
        $studentFeesQuery = DB::table('student_fees')
                ->join('fee_types as ft', 'student_fees.fee_type_id', '=', 'ft.id')
                ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                    $join->on('student_fees.student_id', '=', 'ii.student_id')
                        ->whereColumn('student_fees.fee_type_id', 'ii.fee_type_id')
                        ->where('ii.payment_date', '>=', $fromDate)
                        ->where('ii.payment_date', '<=', $toDate);
                })
                ->whereIn('ft.code', ['Hostel', 'Transport'])
                ->when($studentInfo, function ($q) use ($studentInfo) {
                    $q->where('student_fees.student_id', '=', $studentInfo->id);
                })
                ->where('student_fees.status', 'active')
                ->groupBy('ft.id', 'ft.title', 'ft.month', 'ft.code', 'student_fees.amount')
                ->havingRaw('student_fees.amount - COALESCE(SUM(ii.amount), 0) > 0')
                ->select([
                    'ft.id as fee_type_id',
                    'ft.title',
                    'ft.month',
                    'ft.code',
                    'student_fees.amount as assigned_fee',
                    DB::raw('COALESCE(SUM(ii.amount), 0) as paid_fee'),
                    DB::raw('student_fees.amount - COALESCE(SUM(ii.amount), 0) as due_fee')
                ]);
                $dueFees = $classFeesQuery->union($studentFeesQuery)->get();
            
        
        
        $student_name = "";
        
        if($student_id_no){
            $student = $dueFees->first();
    
            $student_name = "$student?->name ($student_id_no)";
        }
    
        return [
            'dueFees' => $dueFees,
            'student_name' => $student_name,
        ];
    }


    public function getFeesSummaryByDate($student, $fromDate, $toDate)
    {
        $classFeesQuery = ClassFee::join('fee_types as ft', 'class_fees.fee_type_id', '=', 'ft.id')
            ->join('student_classes as sc', 'class_fees.student_class_id', '=', 'sc.id')
            ->join('student_infos as si', 'sc.id', '=', 'si.student_class_id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('si.id', '=', 'ii.student_id')
                    ->whereColumn('class_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereNotIn('ft.code', ['Hostel', 'Transport'])
            ->where('si.student_id_no', $student->student_id_no)
            ->select([
                DB::raw('SUM(class_fees.amount) as total_assigned_fee'),
                DB::raw('COALESCE(SUM(ii.amount), 0) as total_paid_fee'),
                DB::raw('SUM(class_fees.amount) - COALESCE(SUM(ii.amount), 0) as total_due_fee')
            ]);

        // Student-specific fees query (hostel and transport)
        $studentFeesQuery = StudentFee::join('fee_types as ft', 'student_fees.fee_type_id', '=', 'ft.id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('student_fees.student_id', '=', 'ii.student_id')
                    ->whereColumn('student_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereIn('ft.code', ['Hostel', 'Transport'])
            ->where('student_fees.student_id', $student->student_id)
            ->where('student_fees.status', 'active')
            ->select([
                DB::raw('SUM(student_fees.amount) as total_assigned_fee'),
                DB::raw('COALESCE(SUM(ii.amount), 0) as total_paid_fee'),
                DB::raw('SUM(student_fees.amount) - COALESCE(SUM(ii.amount), 0) as total_due_fee')
            ]);

        // Combine both queries using UNION ALL
        $finalQuery = $classFeesQuery->unionAll($studentFeesQuery);

        // Get the total summary
        $feeSummary = DB::query()
            ->fromSub($finalQuery, 'combined_fees')
            ->select([
                DB::raw('SUM(total_assigned_fee) as total_assigned_fee'),
                DB::raw('SUM(total_paid_fee) as total_paid_fee'),
                DB::raw('SUM(total_due_fee) as total_due_fee')
            ])
            ->first();

        return $feeSummary;
    }

    
    public function getFeesSummaryByMonth($student, $month)
    {
        $fromDate = date('Y-m-01', strtotime($month));
        $toDate = date('Y-m-t', strtotime($month));

        $classFeesQuery = ClassFee::join('fee_types as ft', 'class_fees.fee_type_id', '=', 'ft.id')
            ->join('student_classes as sc', 'class_fees.student_class_id', '=', 'sc.id')
            ->join('student_infos as si', 'sc.id', '=', 'si.student_class_id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('si.id', '=', 'ii.student_id')
                    ->whereColumn('class_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereNotIn('ft.code', ['Hostel', 'Transport'])
            ->where('si.student_id_no', $student->student_id_no)
            ->whereIn('class_fees.month', [$month])
            ->select([
                DB::raw('SUM(class_fees.amount) as total_assigned_fee'),
                DB::raw('COALESCE(SUM(ii.amount), 0) as total_paid_fee'),
                DB::raw('SUM(class_fees.amount) - COALESCE(SUM(ii.amount), 0) as total_due_fee')
            ]);

        // Student-specific fees query (hostel and transport)
        $studentFeesQuery = StudentFee::join('fee_types as ft', 'student_fees.fee_type_id', '=', 'ft.id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('student_fees.student_id', '=', 'ii.student_id')
                    ->whereColumn('student_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereIn('ft.code', ['Hostel', 'Transport'])
            ->where('student_fees.student_id', $student->student_id)
            ->where('student_fees.status', 'active')
            ->select([
                DB::raw('SUM(student_fees.amount) as total_assigned_fee'),
                DB::raw('COALESCE(SUM(ii.amount), 0) as total_paid_fee'),
                DB::raw('SUM(student_fees.amount) - COALESCE(SUM(ii.amount), 0) as total_due_fee')
            ]);

        // Combine both queries using UNION ALL
        $finalQuery = $classFeesQuery->unionAll($studentFeesQuery);

        // Get the total summary
        $feeSummary = DB::query()
            ->fromSub($finalQuery, 'combined_fees')
            ->select([
                DB::raw('SUM(total_assigned_fee) as total_assigned_fee'),
                DB::raw('SUM(total_paid_fee) as total_paid_fee'),
                DB::raw('SUM(total_due_fee) as total_due_fee')
            ])
            ->first();

        return $feeSummary;
    }

    public function getStudentDueFees($studentInfo, $month)
    {

        $academicYear = getDefaultAcademicYearValue();
        // Ensure $month is two digits (e.g., '01' for January)
        $month = str_pad($month, 2, '0', STR_PAD_LEFT);
        
        // Build the dates
        $fromDate = $academicYear . '-' . $month . '-01';
        $toDate = $academicYear . '-' . $month . '-' . date('t', strtotime("$academicYear-$month-01"));

        // Class-wide fees query for due fees
        $classFeesQuery = ClassFee::join('fee_types as ft', 'class_fees.fee_type_id', '=', 'ft.id')
            ->join('student_classes as sc', 'class_fees.student_class_id', '=', 'sc.id')
            ->join('student_infos as si', 'sc.id', '=', 'si.student_class_id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('si.id', '=', 'ii.student_id')
                    ->whereColumn('class_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereNotIn('ft.code', ['Hostel', 'Transport'])
            ->where('si.student_id_no', $studentInfo->student_id_no)
            ->groupBy('ft.id', 'ft.title', 'ft.month', 'ft.code', 'class_fees.amount', 'ii.payment_date')
            ->havingRaw('class_fees.amount - COALESCE(SUM(ii.amount), 0) > 0')
            ->select([
                'ft.id as fee_type_id',
                'ft.title',
                'ft.month',
                'ft.code',
                'class_fees.amount as assigned_fee',
                DB::raw('COALESCE(SUM(ii.amount), 0) as paid_fee'),
                DB::raw('class_fees.amount - COALESCE(SUM(ii.amount), 0) as due_fee'),
            ]);

        // Student-specific fees query for due fees
        $studentFeesQuery = StudentFee::join('fee_types as ft', 'student_fees.fee_type_id', '=', 'ft.id')
            ->leftJoin('invoice_items as ii', function ($join) use ($fromDate, $toDate) {
                $join->on('student_fees.student_id', '=', 'ii.student_id')
                    ->whereColumn('student_fees.fee_type_id', 'ii.fee_type_id')
                    ->whereBetween('ii.payment_date', [$fromDate, $toDate]);
            })
            ->whereIn('ft.code', ['Hostel', 'Transport'])
            ->where('student_fees.student_id', $studentInfo->id)
            ->where('student_fees.status', 'active')
            ->groupBy('ft.id', 'ft.title', 'ft.month', 'ft.code', 'student_fees.amount', 'ii.payment_date')
            ->havingRaw('student_fees.amount - COALESCE(SUM(ii.amount), 0) > 0')
            ->select([
                'ft.id as fee_type_id',
                'ft.title',
                'ft.month',
                'ft.code',
                'student_fees.amount as assigned_fee',
                DB::raw('COALESCE(SUM(ii.amount), 0) as paid_fee'),
                DB::raw('student_fees.amount - COALESCE(SUM(ii.amount), 0) as due_fee'),
            ]);

        // Union the two queries
        $finalQuery = $classFeesQuery->unionAll($studentFeesQuery);

        // Execute the query and return results
        return DB::table(DB::raw("({$finalQuery->toSql()}) as fees"))
            ->mergeBindings($finalQuery->getQuery())
            ->select('*')
            ->get();
    }

    public function getRelatedData()
    {
        return [
            'classes' => StudentClass::pluck('class_name', 'id'),
            'years' => AcademicYear::pluck('year','id'),
        ];
    }
}
