<?php

namespace App\Http\Resources;

use App\Helpers\CustomDateFilter;
use App\Models\AccReceivables;
use App\Models\Loan;
use Illuminate\Http\Resources\Json\JsonResource;
use Illuminate\Support\Facades\DB;

class SummaryProductivityByBranchResource extends JsonResource
{
  
    public function funLoanOut($branchID,$type_loan)
    {
        return Loan::where('branch_id',$branchID)
            ->where(function($query) use($type_loan){
                if(!request()->branch_id ==[]){
                    $query->whereIn('branch_id',request()->branch_id);
                }
                if($type_loan==null){
                    $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), CustomDateFilter::thisMonth());
                }
            });
    }

    public function funDataAccReceivables($branch_id,$type_late)
    {

        $data = Loan::where('branch_id', $branch_id)
            ->where(function($query) use($type_late){
                $query->where('loan_status_id',2);
                if(!request()->branch_id ==[]){
                    $query->whereIn('branch_id',request()->branch_id);
                }
                $condition = '';
                switch ($type_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 )) <= 15';
                        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 )) >= 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 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 )) >=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 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 )) > 60';
                        break;
                    default:
                        $condition = '1=0';
                        break;
                }
                $query->whereHas('accReceivables', function ($subQuery) use ($condition) {
                    $subQuery->whereRaw($condition);
                    $subQuery->whereIn('payment_status_id',[2,3]);
                });
            })
            ->orderBy('id','desc');
        return $data;
    }

    public function loanResource($branch_id,$type_late)
    {
        $data = $this->funDataAccReceivables($branch_id,$type_late)->get();
        return LoanLateResource::collection($data);
    }
    
    public function toArray($request)
    {
        $client_disburst  = $this->funLoanOut($this->id,null)->whereIn('loan_status_id',[2,3])->count();
        $loan_disburst    = $this->funLoanOut($this->id,null)->whereIn('loan_status_id',[2,3])->sum('loan_out');
        $client_insurance = $this->funLoanOut($this->id,null)->whereIn('loan_status_id',[2,3])->where('insurance','>',0)->count();
        $loan_insurance   = $this->funLoanOut($this->id,null)->whereIn('loan_status_id',[2,3])->where('insurance','>',0)->sum('loan_out');
        $client_portfolio = $this->funLoanOut($this->id,1)->where('loan_status_id',2)->count();
        $loan_portfolio   = $this->funLoanOut($this->id,1)->where('loan_status_id',2)->sum('balance_principal');

        // NPL AMONUT LATE <=15
        $client_npl_less15= $this->funDataAccReceivables($this->id,1)->count();
        $npl_amount_less15= $this->loanResource($this->id,1)->sum('principal_late');
        // NPL AMONUT LATE >15
        $client_npl_larger15= $this->funDataAccReceivables($this->id,2)->count();
        $npl_amount_larger15= $this->loanResource($this->id,2)->sum('principal_late');
        // NPL AMONUT LATE >30
        $client_npl_larger30= $this->funDataAccReceivables($this->id,3)->count();
        $npl_amount_larger30= $this->loanResource($this->id,3)->sum('principal_late');
        // $balance_principal_larger30 = $this->loanResource($this->id,3)->sum('balance_principal');
        // NPL AMONUT LATE >60
        $client_npl_larger60= $this->funDataAccReceivables($this->id,4)->count();
        $npl_amount_larger60= $this->loanResource($this->id,4)->sum('principal_late');
        // $balance_principal_larger60 = $this->loanResource($this->id,4)->sum('balance_principal');

        $total_npl = $npl_amount_less15 + $npl_amount_larger15 + $npl_amount_larger30 + $npl_amount_larger60;
        $total_client_npl = $client_npl_less15 + $client_npl_larger15 + $client_npl_larger30 + $client_npl_larger60;

        return [
            'branch_code'       => $this->branch_code,
            'name_en'           => $this->name_en,
            'client_disburst'   => $client_disburst??0,
            'loan_disburst'     => $loan_disburst??0,
            'client_insurance'  => $client_insurance??0,
            'loan_insurance'    => $loan_insurance??0,
            'client_portfolio'  => $client_portfolio??0,
            'loan_portfolio'    => $loan_portfolio??0,
            // NPL AMONUT LATE <=15
            'client_npl_less15'   => $client_npl_less15,
            'npl_amount_less15'   => $npl_amount_less15,
            'npl_less15_percent'  => $npl_amount_less15>0?round(($npl_amount_less15/$loan_portfolio)*100,2):0,
            // NPL AMONUT LATE >15
            'client_npl_larger15' => $client_npl_larger15,
            'npl_amount_larger15' => $npl_amount_larger15,
            'npl_larger15_percent'=> $npl_amount_larger15>0?round(($npl_amount_larger15/$loan_portfolio)*100,2):0,
            // NPL AMONUT LATE >30
            'client_npl_larger30' => $client_npl_larger30,
            'npl_amount_larger30' => $npl_amount_larger30,
            'npl_larger30_percent'=> $npl_amount_larger30>0?round(($npl_amount_larger30/$loan_portfolio)*100,2):0,
            // NPL AMONUT LATE >60
            'client_npl_larger60' => $client_npl_larger60,
            'npl_amount_larger60' => $npl_amount_larger60,
            'npl_larger60_percent'=> $npl_amount_larger60>0?round(($npl_amount_larger60/$loan_portfolio)*100,2):0,
            // Total NPL
            'total_client_npl'    => $total_client_npl??0,
            'total_npl_amount'    => $total_npl??0,
            'npl_total_percent'   => $total_npl>0?round(($total_npl/$loan_portfolio)*100,2):0,
        ];
    }
}
