<?php

namespace App\Models;

use Carbon\Carbon;
use Carbon\CarbonPeriod;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use App\Models\Accounting\ChartOfAccount;
use Illuminate\Database\Eloquent\SoftDeletes;

class Loan extends Model
{
    use SoftDeletes;

    /**
     * The attributes that are mass assignable.
     *
     * @var array<int, string>
     */
    protected $fillable = [
        'id',
        'client_id',
        'co_id',
        'branch_id',
        'currency_id',
        'completed_date',
        'installment_date',
        'start_date',
        'expired_date',
        'write_off_date',
        'reschedule_amount',
        'balance_maintenance',
        'insurance',
        'total_insurance',
        'level_loan',
        'chart_of_acc_id',
        'term_condi_id',
        'loan_code',
        'loan_out',
        'interest',
        'total_interest',
        'operating_service',
        'total_operating_service',
        'service',
        'total_service',
        'service_collection',
        'total_collection',
        'loan_receive',
        'amount_client_receive',
        'balance_principal',
        'balance_interest',
        'balance_operating_service',
        'loan_balance',
        'paid_principal',
        'paid_interest',
        'paid_operating_service',
        'loan_in',
        'total_penalty',
        'paid_maintenance',
        'desc',
        'term_type',
        'credit_type',
        'period',
        'condition_pay',
        'loan_status_id',
        'closing_date',
        'approved_by',
        'closing_by',
        'type_loan',
        'maintenance',
        'total_maintenance',
        'saving',
        'total_saving',
        'total_waiv',
        'client_type'
    ];

    public function currency()
    {
        return $this->belongsTo(Loan::class, 'currency_id');
    }
    public function loan_status()
    {
        return $this->belongsTo(LoanStatus::class, 'loan_status_id');
    }
    public function co()
    {
        return $this->belongsTo(User::class, 'co_id');
    }
    public function collateralDetails()
    {
        return $this->hasMany(CollateralDetail::class,'loan_id');
    }
    public function disburstVia()
    {
        return $this->belongsTo(ChartOfAccount::class, 'chart_of_acc_id');
    }

    public function client()
    {
        return $this->belongsTo(Client::class, 'client_id');
    }
    public function termcondition()
    {
        return $this->belongsTo(TermCondition::class, 'term_condi_id');
    }
    public function getsumprincipalAttribute()
    {
        return $this->where('co_id',$this->co_id)->sum('loan_out');
    }
    public function getsuminterestAttribute()
    {
        return $this->where('co_id',$this->co_id)->sum('total_interest');
    }
    public function getsumriskAttribute()
    {
        return $this->where('co_id',$this->co_id)->sum('total_risk');
    }

    public function branchs()
    {
        return $this->belongsTo(Branch::class, 'branch_id');
    }
    public function getcountdaylateAttribute()
    {
        $late = AccReceivables::where('co_id',$this->co_id)->where('payment_date','<',date('Y-m-d'))->whereIn('payment_status_id',[2,3])->groupBy('client_id')->get()->count();
        $amount = AccReceivables::where('co_id',$this->co_id)->where('payment_date','<',date('Y-m-d'))->whereIn('payment_status_id',[2,3])->sum('principal');
        return array(
            'late' => $late,
            'amount' => $amount,
        );
    }

    public function accReceivables()
    {
        return $this->hasMany(AccReceivables::class,'loan_id','id');
    }

    public function funAccReceivables(){
        $data = AccReceivables::where('loan_id',$this->id)
        ->where('client_id',$this->client_id)
        ->whereIn('payment_status_id',[2,3])
        ->where(function($qeury){
            if(request()->date!=''){
                $qeury->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',request()->date);
            } 
            else if(request()->type_collect_late ==1){ // Late Report
                $qeury->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<',date('Y-m-d'));
            }
            else{
                $qeury->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
            }
        });
        return $data;
    }

    public function getfirstIDAttribute()
    {
        return $this->funAccReceivables()->min('id');
    }

    public function getcurrentPaymentAttribute()
    {
      return $this->funAccReceivables()->where('id',$this->first_id)->sum('unreceived');
    }

    public function getlatePaymentAttribute()
    {
      return $this->funAccReceivables()->sum('unreceived');
    }

    // public function gettotallateAttribute()
    // {
    //     $acc = AccReceivables::find($this->first_id);
    //     if($acc){
    //         $now = Carbon::now();
    //         $end = Carbon::parse($acc->payment_date);
    //         $total_late = $now->diff($end)->days;
    //         return $total_late;
    //     }
    // }

    public function getdateLateAttribute()
    {
        return AccReceivables::find($this->first_id);
    }

    public function getActualPaymentDateAttribute()
    {
        return $this->funAccReceivables()->max('payment_date');
    }

    // Late Report

    public function funDataAccReceivables()
    {
        $data = AccReceivables::where('loan_id',$this->id)
        ->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<',date('Y-m-d'))
        ->where('loan_id',$this->id)
        ->whereIn('payment_status_id',[2,3]);
        return $data;
    }

    public function joinAccWithAccDetails()
    {
        $data = AccReceivables::leftjoin('acc_receivable_details','acc_receivable_details.acc_id','acc_receivables.id')
        ->where(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"),'<',date('Y-m-d'))
        ->whereNull('acc_receivable_details.deleted_at')
        ->where('acc_receivables.loan_id',$this->id)
        ->whereIn('acc_receivables.payment_status_id',[2,3]);
        return $data;
    }

    public function getprincipallateAttribute()
  {
    $AccReceivables = $this->funDataAccReceivables()->sum('principal');
    $Detail = $this->joinAccWithAccDetails()->sum('acc_receivable_details.paid_principal');
    return $AccReceivables - $Detail;
  }

  public function getinterestlateAttribute()
  {
    $AccReceivables = $this->funDataAccReceivables()->sum('interest_per_month');
    $Detail = $this->joinAccWithAccDetails()->sum('acc_receivable_details.paid_interest');
    return $AccReceivables - $Detail;
  }

  public function getoperatingservicelateAttribute()
  {
    $AccReceivables = $this->funDataAccReceivables()->sum('operating_service');
    $Detail = $this->joinAccWithAccDetails()->sum('acc_receivable_details.paid_operating_service');
    return $AccReceivables-$Detail;
  }

  public function getreceivedlateAttribute()
  {
    return $this->funDataAccReceivables()->sum('received');
  }

  public function getunreceivedlateAttribute()
  {
    return $this->funDataAccReceivables()->sum('unreceived');
  }

  public function gettotallateAttribute()
    {

        // $acc = AccReceivables::find($this->first_id);
        // $now = Carbon::now();
        // $end = Carbon::parse($acc->payment_date);
        // $total_late = $now->diff($end)->days;

        // 2025-10-01
        $array = Holiday::select('date')->get();
        $holidayDates = json_decode(json_encode($array));

        $holiday = array_map(function($value) {
            return Carbon::parse($value->date)->format('Y-m-d');
        }, $holidayDates);

        $acc = AccReceivables::find($this->first_id);

        // date range
        // 0 = sun, 1 = mon, 2 = Tues, 3 = wen, 4 = thurs, 5 = fri, 6 = sat
        $mondayToFriday = [0,6];
        $mondayToSaturday = [0];
        $mondayToThursday = [0,5,6];
        $now = Carbon::now();

        $total_late = 0;
        if($acc){
            $end = Carbon::parse($acc->payment_date);
        if ($end->isBefore($now)) {   // only count if payment_date < now

            if($this->term_condi_id== 5){ // សៅរ៍-អាទិត្យ
                $period = CarbonPeriod::create($end, $now);
            }else{
                $period = CarbonPeriod::create($end->addDay(), $now);
            }

            foreach ($period as $date) {
                if($this->term_condi_id==1){ // Monday-Friday
                    if (!in_array($date->format('Y-m-d'), $holiday)&&!in_array($date->dayOfWeek, $mondayToFriday)) {
                        $total_late++;
                    }
                }
                else if($this->term_condi_id==2){ // Monday-Saturday
                    if (!in_array($date->format('Y-m-d'), $holiday)&&!in_array($date->dayOfWeek, $mondayToSaturday)) {
                        $total_late++;
                    }
                }
                else if($this->term_condi_id==3){ //Monday-Sunday
                    if (!in_array($date->format('Y-m-d'), $holiday)) {
                        $total_late++;
                    }
                }
                else if($this->term_condi_id==4){ //ចន្ទ័-ព្រហស្បត្តិ៍
                    if (!in_array($date->format('Y-m-d'), $holiday)&&!in_array($date->dayOfWeek, $mondayToThursday)) {
                        $total_late++;
                    }
                }
                else{ // សៅរ៍-អាទិត្យ
                    if (in_array($date->dayOfWeek, [0, 6]) && !in_array($date->toDateString(), $holiday)) {
                        $total_late++;
                    }
                }
            }
        }
        return $total_late;
        }
    }

//   public function getcountLateAttribute()
//   {
//       $acc = AccReceivables::find($this->first_id);
//       $now = Carbon::now();
//       $end = Carbon::parse($acc->payment_date);
//       $total_late = $now->diff($end)->days;
//       return $total_late;
//   }
}
