<?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 DigitalPayment
{
    
    
    public function digitalDueFeesSummary(Request $request)
    {
        // dd($request->all()); 

        $student_id_no = $request->studentId;

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