<?php

namespace App\Http\Controllers\Admin;


use App\Models\Loan;
use App\Models\User;
use App\Models\Branch;
use App\Helpers\Helper;
use Illuminate\Support\Arr;
use Illuminate\Http\Request;
use App\Models\AccReceivables;
use App\Helpers\CustomDateFilter;
use App\Models\LoanLossProvision;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use App\Http\Resources\LoanResource;
use App\Models\AccReceivableDetails;
use App\Http\Resources\OverdueResource;
use App\Http\Resources\SummaryResource;
use App\Http\Resources\LoanLateResource;
use App\Http\Resources\CollectedResource;
use App\Http\Resources\ShowPaymentResource;
use App\Http\Resources\CollectionLoanResource;
use App\Http\Resources\LoanCollectionResource;
use App\Http\Resources\CashierReceivedResource;
use App\Http\Resources\LoanLossProvisionResource;
use App\Http\Resources\SumTotalClientPaymentResource;
use App\Http\Resources\SummaryProductivityByBranchResource;

class ReportController extends Controller
{

    function __construct()
    {
        $this->middleware('permission:Co Received Report|Cashier Received Report', ['only' => ['DailyPayment']]);
        $this->middleware('permission:Credit Officer Report', ['only' => ['CoReport']]);
        $this->middleware('permission:Borrow Report', ['only' => ['BorrowedReport']]);
        $this->middleware('permission:Loan Pay Off Report', ['only' => ['CompletedReport']]);
        $this->middleware('permission:Overdue Report', ['only' => ['OverdueReport']]);
        $this->middleware('permission:Loan Repayment Report', ['only' => ['CollectionReport']]);
        $this->middleware('permission:Collected Report', ['only' => ['CollectedReport']]);
        $this->middleware('permission:Summary Co Report', ['only' => ['SummaryCoReport']]);
        $this->middleware('permission:Collect Good List', ['only' => ['CollectGoodLoan']]);
        $this->middleware('permission:Collect Late List|Collect Write Off List', ['only' => ['CollectLateLoan']]);
        $this->middleware('permission:Daily Report', ['only' => ['DailyReport']]);
        $this->middleware('permission:Loan Arrear Report', ['only' => ['LateReport']]);
        // $this->middleware('permission:Summary Productivity By Branch', ['only' => ['SummaryProductivityByBranch']]);
    }

    public function DailyPayment(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = AccReceivableDetails::where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            if(!$request->client_id ==[]) {
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]) {
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->status_approve==''){
                $query->where('status',$request->status_approve);
            }
        })
        ->orderby('received_date','desc')
        ->orderby('id','desc');

        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CollectedResource::collection($data->get());
        } else{
            return CollectedResource::collection($data->paginate($per_page));
        }
    }
    public function loanNotClosingReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Loan::where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('co_id',Helper::group());
            $query->where('loan_status_id',2);
            $query->whereNull('closing_date');
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
        })
        ->orderBy('installment_date','desc');
        // return $data->paginate($per_page);
        if($request->per_page=="ALL" || $request->pagin == 'true'){
            return LoanResource::collection($data->get());
        } else {
            return LoanResource::collection($data->paginate($per_page));
        }
    }
    
    public function RejectLoanReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Loan::where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('co_id',Helper::group());
            $query->where('loan_status_id',10);
            $query->whereNull('deleted_at');
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
        })
        ->orderBy('installment_date','desc');
        // return $data->paginate($per_page);
        if($request->per_page=="ALL" || $request->pagin == 'true'){
            return LoanResource::collection($data->get());
        } else {
            return LoanResource::collection($data->paginate($per_page));
        }
    }
    
    public function SumTotalClientPayment(Request $request)
    {
        $per_page = $request->per_page == null ? 50 : $request->per_page; 
        $data = AccReceivableDetails::select(
            'acc_receivable_details.*',
            DB::raw('SUM(acc_receivable_details.paid_principal) as sumPrincipal'),
            DB::raw('SUM(acc_receivable_details.paid_interest) as sumInterest'),
            DB::raw('SUM(acc_receivable_details.paid_operating_service) as sumOperatService'),
            DB::raw('SUM(acc_receivable_details.received) as sumReceived'),
            DB::raw('SUM(acc_receivable_details.paid_penalty) as sumPaidPenalty'),
            DB::raw('SUM(acc_receivable_details.waiv_penalty) as sumWaivPenalty')
        )
        ->where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->client_id ==[]) {
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]) {
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->status==''){
                $query->where('status',$request->status);  
            }
            if(!$request->status_approve==''){
                $query->where('status',$request->status_approve);
            }
            if(!$request->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date);
            }
        })
        ->orderby('received_date','desc')
        ->orderby('id','desc')
        ->groupBy('loan_id','client_id','received_date');

        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CashierReceivedResource::collection($data->get());
        } else{
            return CashierReceivedResource::collection($data->paginate($per_page));
        }
    }
    public function paymentNotClosingReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = AccReceivableDetails::where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereNull('closing_date');
            if(!$request->client_id ==[]) {
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]) { 
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->status_approve==''){
                $query->where('status',$request->status_approve);
            }
            if(!$request->date ==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date ?? CustomDateFilter::thisDay());
            }
        })
        ->orderby('received_date','desc')
        ->orderby('id','desc');

        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CollectedResource::collection($data->get());
        } else{
            return CollectedResource::collection($data->paginate($per_page));
        }
    }

    public function cashierReceived(Request $request)
    {
        $per_page = $request->per_page == null ? 50 : $request->per_page; 
        $data = AccReceivableDetails::select(
            'acc_receivable_details.*',
            DB::raw('SUM(acc_receivable_details.paid_principal) as sumPrincipal'),
            DB::raw('SUM(acc_receivable_details.paid_interest) as sumInterest'),
            DB::raw('SUM(acc_receivable_details.paid_operating_service) as sumOperatService'),
            DB::raw('SUM(acc_receivable_details.received) as sumReceived'),
            DB::raw('SUM(acc_receivable_details.paid_penalty) as sumPaidPenalty'),
            DB::raw('SUM(acc_receivable_details.waiv_penalty) as sumWaivPenalty')
        )
        ->where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->client_id ==[]) {
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]) {
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->status==''){
                $query->where('status',$request->status);  
            }
            if(!$request->status_approve==''){
                $query->where('status',$request->status_approve);
            }
            if(!$request->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date);
            }
        })
        // ->whereHas('loan',function($query){
        //     $query->where('client_type',1);
        // })
        ->orderby('received_date','desc')
        ->orderby('id','desc')
        ->groupBy('loan_id','client_id','chart_of_acc_id','received_date');

        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CashierReceivedResource::collection($data->get());
        } else{
            return CashierReceivedResource::collection($data->paginate($per_page));
        }
    }


    public function CoReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Loan::where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date ==''){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"),'<=',$request->as_of_date);
            }
            if(!$request->client_id ==[]) {
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]) {
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }if(!$request->term_condi_id == []){
            $query->whereIn('term_condi_id', $request->term_condi_id);
            }
            if(!$request->loan_status_id ==[]){
                $query->whereNotIn('loan_status_id',[1,4,7,8,9]);
            } else{
                $query->where('loan_status_id',2);
            }
            // if(!$request->loan_status_id ==[]) {
            //     $query->whereIn('loan_status_id',$request->loan_status_id);
            // } else{
            //     $query->whereIn('loan_status_id',[2,3]);
            // }
        });
        if($request->per_page=="ALL" || $request->pagin == 'true'){
            return LoanResource::collection($data->get());
        } else {
            return LoanResource::collection($data->paginate($per_page));
        }
    }



    public function BorrowedReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Loan::where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('loan_status_id',[2,5]);
            $query->where('client_type',1);
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date ==''){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"),'<=',$request->as_of_date);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        });
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return LoanResource::collection($data->get());
        } else {
            return LoanResource::collection($data->paginate($per_page));
        }
    }


    public function CompletedReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Loan::where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',3);
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(completed_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date ==''){
                $query->where(DB::raw("DATE_FORMAT(completed_date, '%Y-%m-%d')"),'<=',$request->as_of_date);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        });
        if($request->per_page=="ALL" || $request->pagin == 'true'){
            return LoanResource::collection($data->get());
        } else {
            return LoanResource::collection($data->paginate($per_page));
        }
    }



    public function OverdueReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = AccReceivables::select(
            "acc_receivables.*",
            DB::raw('Max(payment_date) as max_payment')
        )
        ->where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]) {
                $query->whereIn('co_id',$request->co_id);
            }
        })
        ->whereHas('loan', function ($query) use($request){
            $query->where('loan_status_id',2);
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        })
        ->groupBy('loan_id')
        ->having('max_payment','<',date('Y-m-d'));
        if($request->per_page=="ALL") {
            return OverdueResource::collection($data->get());
        } else {
            return OverdueResource::collection($data->paginate($per_page));
        }
    }

    public function CollectionReport(Request $request)
    {
        $per_page  = $request->per_page == null?25: $request->per_page;
        $data = AccReceivables::where(function($query) use($request) {
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('payment_status_id',[2,3]);
            if(!$request->date ==''){
                $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',$request->date);
            } else{
                $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
        })
        ->whereHas('loan',function($query) use($request){
            $query->whereIn('loan_status_id',[2,5]);
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        })
        ->orderBy('payment_date','asc')
        ->orderBy('id','asc')
        ->groupby('loan_id');

        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return LoanCollectionResource::collection($data->get());
        } else{
            return LoanCollectionResource::collection($data->paginate($per_page));
        }
    }



    public function CollectedReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = AccReceivableDetails::where(function($query) use ($request){
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date ==''){
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m-%d')"),'<=',$request->as_of_date);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
        })
        ->whereHas('loan',function($query) use($request){
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        });
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CollectedResource::collection($data->get());
        } else {
            return CollectedResource::collection($data->paginate($per_page));
        }
    }



    public function SummaryCoReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = User::where('type',2)
        ->whereIn('id',Helper::group())
        ->whereIn('branch_id',Helper::byBranch())
        ->where('status',1)
        ->where(function($query) use($request){
            if(!$request->co_id==[]){
                $query->whereIn('id',$request->co_id);
            }
        });
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return SummaryResource::collection($data->get());
        } else {
            return SummaryResource::collection($data->paginate($per_page));
        }
    }



    public function SummaryProductivityByBranch(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Branch::where(function($query) use($request){
            if(!$request->branch_id==[]){
                $query->whereIn('id',$request->branch_id);
            }
        });
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return SummaryProductivityByBranchResource::collection($data->get());
        } else {
            return SummaryProductivityByBranchResource::collection($data->paginate($per_page));
        }
    }



    public function funGroupAccReceivable(Request $request)
    {
        $data = AccReceivables::where(function($query) use($request) {
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('payment_status_id',[2,3]);
            $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->village_id ==[]){
                $query->whereHas('client',function($vil) use($request){
                    $vil->whereIn('village_id',$request->village_id);
                });
            }
        })
        ->whereHas('loan',function($query) use($request) {
            $query->whereIn('loan_status_id',[2,5]);
            $query->where('client_type',$request->client_type);
        })
        ->orderBy('payment_date','asc')
        ->orderBy('id','asc')
        ->groupby('loan_id');
        return $data;
    }

    public function CollectGoodLoan(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = $this->funGroupAccReceivable($request)->where(function($query) use($request){
            if(!$request->date ==''){
                $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',$request->date);
            } else{
                $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
            }
        });
        // ->where('payment_date','<=',date('Y-m-d'));
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CollectionLoanResource::collection($data->get());
        } else{ 
            return CollectionLoanResource::collection($data->paginate($per_page));
        }
    }

    public function CollectLateLoan(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = $this->funGroupAccReceivable($request)->where('payment_date','<',date('Y-m-d'));
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return CollectionLoanResource::collection($data->get());
        } else{
            return CollectionLoanResource::collection($data->paginate($per_page));
        }
    }

    public function DailyReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = AccReceivables::where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('payment_status_id',[2,3]);
            $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),date('Y-m-d'));
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
        })
        ->whereHas('loan',function($query){
            $query->where('loan_status_id',2);
        })
        ->orderby('payment_date','ASC');
        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return ShowPaymentResource::collection($data->get());
        } else{
            return ShowPaymentResource::collection($data->paginate($per_page));
        }
    }


    public function LateReport(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data = Loan::where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('co_id',Helper::group());
            $query->whereIn('loan_status_id',[2,5]);
            if(!$request->client_id ==[]){
                $query->whereIn('client_id',$request->client_id);
            }
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
            if (isset($request->loan_day_late)) {
                $condition = '';
                switch ($request->loan_day_late) {
                    case 1:
                        $condition = 'DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) >= 1
                                    AND DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) <= 7';
                        break;
                    case 2:
                        $condition = 'DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) >= 8
                                    AND DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) <= 15';
                        break;
                    case 3:
                        $condition = 'DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) >=16
                                      AND DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) <= 30';
                        break;
                    case 4:
                        $condition = 'DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) >= 31
                                        AND DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) <= 60';
                        break;
                    case 5:
                        $condition = 'DATEDIFF(NOW(), (SELECT MIN(payment_date) FROM acc_receivables WHERE acc_receivables.loan_id = loans.id AND acc_receivables.payment_status_id IN (2,3) AND acc_receivables.deleted_at IS NULL )) > 60';
                        break;
                    default:
                        $condition = '1=0';
                        break;
                }
                $query->whereHas('accReceivables', function ($subQuery) use ($condition) {
                    $subQuery->whereRaw($condition);
                    $subQuery->whereIn('payment_status_id',[2,3]);
                });
            }
            else{
                $query->whereHas('accReceivables', function ($query){
                    $query->where('payment_date','<',date('Y-m-d'));
                    $query->whereIn('payment_status_id',[2,3]);
                });
            }
        })
        ->orderBy('id','desc');

        if($request->per_page=="ALL" || $request->pagin == 'true'){
            return LoanLateResource::collection($data->get());
        } else {
            return LoanLateResource::collection($data->paginate($per_page));
        }
    }



    public function SummaryLoanLossProvision(Request $request)
    {
        $per_page = $request->per_page == null?25: $request->per_page;
        $data  = LoanLossProvision::select(
            DB::raw('SUM(loan_out) as total_loan_out'),
            DB::raw('SUM(loan_balance) as total_loan_balance'),
            DB::raw('SUM(amount_late) as total_amount_late'),
            DB::raw('SUM(amount_loss_provision) as total_amount_loss_provision'),
            'type_loan_loss_provision',
            'loss_provision_date',
            'co_id'
        )
        ->where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('co_id',Helper::group());
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(loss_provision_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date==[]){
                $query->where(DB::raw("DATE_FORMAT(loss_provision_date, '%Y-%m-%d')"), $request->as_of_date);
            }
        })
        ->whereHas('loan',function($query) use($request){
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        })
        ->groupby('type_loan_loss_provision');

        if($request->per_page=="ALL" || $request->pagin == 'true') {
            return LoanLossProvisionResource::collection($data->get());
        } else{
            return LoanLossProvisionResource::collection($data->paginate($per_page));
        }
        return $data;
    }

    public function callLoanLossProvision($type_loan_loss_provision, $term_type,$request)
    {
        $data = LoanLossProvision::select(
            DB::raw('SUM(loan_out) as total_loan_out'),
            DB::raw('SUM(loan_balance) as total_loan_balance'),
            DB::raw('SUM(amount_late) as total_amount_late'),
            DB::raw('SUM(amount_loss_provision) as total_amount_loss_provision'),
            'type_loan_loss_provision',
            'loss_provision_date',
            'client_id',
            'co_id',
        )
        ->where('type_loan_loss_provision',$type_loan_loss_provision)
        ->whereHas('loan',function($query) use( $term_type){
            if(! $term_type ==[]) {
                $query->whereIn('term_type', $term_type);
            }
        })
        ->where(function($query) use($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(loss_provision_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date ==''){
                $query->where(DB::raw("DATE_FORMAT(loss_provision_date, '%Y-%m-%d')"),'<=',$request->as_of_date);
            }
        })
        ->groupby('loan_id')
        ->orderby('id','ASC');
        return $data;
    }

    public function LoanLossProvisionClassific(Request $request){
        $per_page = $request->per_page == null?25: $request->per_page;
        $loan_loss_provision = LoanLossProvision::select('type_loan_loss_provision')
        ->where(function($query) use ($request){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('co_id',Helper::group());
            if(!$request->co_id ==[]){
                $query->whereIn('co_id',$request->co_id);
            }
            if(!$request->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(loss_provision_date, '%Y-%m-%d')"), $request->date);
            }
            if(!$request->as_of_date ==''){
                $query->where(DB::raw("DATE_FORMAT(loss_provision_date, '%Y-%m-%d')"),'<=',$request->as_of_date);
            }
        })
        ->whereHas('loan',function($query) use($request){
            if(!$request->term_type ==[]) {
                $query->whereIn('term_type',$request->term_type);
            }
        })
        ->orderby('type_loan_loss_provision','ASC')
        ->groupby('type_loan_loss_provision')
        ->paginate($per_page);

        $pag_json = json_encode($loan_loss_provision);
        $paginate = json_decode($pag_json);

        $data = $loan_loss_provision->map(function($value) use($request){
            $loan_loss_provision = $this->funSumLoanLossProvision($value->type_loan_loss_provision,$request->term_type,$request);
            return [
                'type_loan_loss_provision' => $value->type_loan_loss_provision,
                'sum_total_loan_out'       => array_sum(Arr::pluck($loan_loss_provision,'total_loan_out')),
                'sum_total_loan_balance'   => array_sum(Arr::pluck($loan_loss_provision,'total_loan_balance')),
                'sum_total_amount_late'    => array_sum(Arr::pluck($loan_loss_provision,'total_amount_late')),
                'sum_total_amount_loss_provision' => array_sum(Arr::pluck($loan_loss_provision,'total_amount_loss_provision')),
                'details' => $this->callLoanLossProvision($value->type_loan_loss_provision,$request->term_type,$request)
                // LoanLossProvision::select(
                //         DB::raw('SUM(loan_out) as total_loan_out'),
                //         DB::raw('SUM(loan_balance) as total_loan_balance'),
                //         DB::raw('SUM(amount_late) as total_amount_late'),
                //         DB::raw('SUM(amount_loss_provision) as total_amount_loss_provision'),
                //         'type_loan_loss_provision',
                //         'loss_provision_date',
                //         'client_id',
                //         'co_id',
                //     )
                //     ->where('type_loan_loss_provision',$value->type_loan_loss_provision)
                //     ->whereHas('loan',function($query) use($request){
                //         if(!$request->term_type ==[]) {
                //             $query->whereIn('term_type',$request->term_type);
                //         }
                //     })
                //     ->where(function($query){
                //         $query->whereIn('branch_id',Helper::byBranch());
                //         $query->where('currency_id',Helper::byCurrency());
                //     })
                //     ->groupby('loan_id')
                //     ->orderby('id','ASC')
                    ->get()
                    ->map(function($val){
                    return [
                        'client_code'        => $val->client->code,
                        'client_name'        => $val->client->name,
                        'co_name'            => $val->co->name,
                        'total_loan_out'     => $val->total_loan_out,
                        'total_loan_balance' => $val->total_loan_balance,
                        'total_amount_late'  => $val->total_amount_late,
                        'total_amount_loss_provision' => $val->total_amount_loss_provision,
                    ];
                }),

            ];
        });
        $res = [
            'data'  => $data,
            'links' => [
                'first'=> $paginate->first_page_url,
                'last' => $paginate->last_page_url,
                'next' => $paginate->next_page_url,
                'prev' => $paginate->prev_page_url,
            ],
            'meta'   => [
                'current_page'=> $loan_loss_provision->currentPage(),
                'last_page' => $loan_loss_provision->lastPage(),
                'per_page' => $loan_loss_provision->perPage(),
                'total'  => $loan_loss_provision->total(),
                'from' => $paginate->from,
                'to' => $paginate->to,
            ],
        ];
        return response()->json($res, 200);
    }

    private function funSumLoanLossProvision($type_loan_loss_provision, $term_type,$request)
    {
        return $this->callLoanLossProvision($type_loan_loss_provision, $term_type,$request)->get()->map(function($value){
            return [
                'total_loan_out'     => $value['total_loan_out'],
                'total_loan_balance' => $value['total_loan_balance'],
                'total_amount_late'  => $value['total_amount_late'],
                'total_amount_loss_provision' => $value['total_amount_loss_provision'],
            ];
        });
    }
}
