<?php

namespace App\Http\Controllers\Api\V1;

use App\Models\ApiToken;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Accounting\ClassFee;
use App\Http\Controllers\Controller;

class StudentPaymentController extends Controller
{
    public function create(Request $request){
        $student_id = $request->query('student_id');
        $api_token = $request->query('api_token');
        $student_infos = DB::table('student_infos')->where('student_id_no', $student_id)->select('user_id as student_sn','student_id_no','first_name','last_name','mobile_no','guardian_mobile')->orderBy('id','desc')->first();
        $api = ApiToken::where('api_token',$api_token)->first();

        if(!empty($student_infos) && !empty($api)){
            $user = DB::table('users')->where('id', $student_infos->student_sn)->select('id as sn','first_name','last_name','phone_number')->first();
            $student_id_no = $student_infos->student_id_no;

            $dueFees = ClassFee::join('fee_types as ft', 'class_fees.fee_type_id', '=', 'ft.id')
                    ->join('student_classes as sc', 'class_fees.class_id', '=', 'sc.id')
                    ->join('student_infos as si', 'sc.id', '=', 'si.student_class_id')
                    ->leftJoin('invoice_items as ii', function ($join) {
                        $join->on('si.id', '=', 'ii.student_id')
                            ->whereColumn('class_fees.fee_type_id', 'ii.fee_type_id');
                    })
                    ->leftJoin('student_hostels as sh', 'si.id', '=', 'sh.student_id')
                    ->leftJoin('student_transports as st', 'si.id', '=', 'st.student_id')
                    ->select([
                        'si.id',
                        'si.first_name',
                        'si.last_name',
                        'si.student_id_no',
                        'sc.class_name',
                        'class_fees.fee_type_id',
                        'ft.title',
                        'ft.code',
                        'sh.cost',
                        'st.monthly_cost',
                        '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')
                    ])
                    ->where(function ($query) use ($student_id_no) {
                        $query->when($student_id_no, function ($q) use ($student_id_no) {
                            $q->where('si.student_id_no', '=', $student_id_no);
                        });
                     })
                    ->where(function ($query) {
                        $query->where(function ($query) {
                            $query->where('ft.code', '=', 'HOSTEL')
                                  ->whereNotNull('sh.student_id');
                        })
                        ->orWhere(function ($query) {
                            $query->where('ft.code', '=', 'TRANSPORT')
                                  ->whereNotNull('st.student_id');
                        })
                        ->orWhereNotIn('ft.code', ['HOSTEL', 'TRANSPORT']);
                    })
                    ->groupBy('si.id', 'class_fees.fee_type_id', 'class_fees.amount')
                    ->orderBy('paid_fee', 'DESC')
                    ->get();
            return response()->json([
                'dueFees' => $dueFees,
                'student_infos' => $student_infos,
                // 'user' => $user,
                'success' => 'Resource founded.',
            ], 200);
        }
        return response()->json([
            'error' => 'Resource not found.'
        ], 404);
    }
}
