<?php

namespace App\Models;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Support\Facades\DB;

class AccReceivables extends Model
{
  use SoftDeletes;
  protected $table = 'acc_receivables';
  protected $fillable = [
    'id',
    'loan_id',
    'client_id',
    'co_id',
    'branch_id',
    'currency_id',
    'num',
    'principal',
    'interest_per_month',
    'operating_service',
    'maintenance_per_month',
    'payment_per_month',
    'payment_date',
    'received_date',
    'loan_balance',
    'penalty',
    'received',
    'unreceived',
    'open_close',
    'waiv_penalty',
    'payment_status_id',
  ];

  public function currency()
  {
    return $this->belongsTo(Currency::class, 'currency_id');
  }

  public function loan()
  {
    return $this->belongsTo(Loan::class, 'loan_id');
  }

  public function payment_status()
  {
    return $this->belongsTo(PaymentStatus::class, 'payment_status_id');
  }

  public function co()
  {
    return $this->belongsTo(User::class, 'co_id');
  }

  public function client()
  {
    return $this->belongsTo(Client::class, 'client_id');
  }

  public function countlate()
  {
    return AccReceivables::where('loan_id',$this->loan_id)->whereIn('payment_status_id',[2,3])->count();
  }

  public function branchs()
  {
    return $this->belongsTo(Branch::class, 'branch_id');
  }

  public function accReceivableDetails()
  {
    return $this->hasMany(AccReceivableDetails::class,'acc_id','id');
  }

  public function funAccReceivables()
  {
    $data = AccReceivables::where(function($query){
      if(request()->type_collection == 1){
        // if(!request()->date ==''){
        //   // $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',request()->date);
        //   $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),request()->date);
        // }
        if(!request()->date == []){
          $query->whereBetween(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'));
        }
      }
      else if(request()->type_collect_late==3){
        $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d')); // Collect Late with today
      }
      else{
        $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<',date('Y-m-d')); // Late Collection
      }
    })
    ->where('loan_id',$this->loan_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(function($query){
        if(request()->type_collection == 1){
          // if(!request()->date ==''){
          //   // $query->where(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"),'<=',request()->date);
          //   $query->where(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"),request()->date);
          // }
          if(!request()->date == []){
            $query->whereBetween(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"), request()->date);
          }
          else if(request()->type_collect_late==3){
            $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d')); // Collect Late with today
          }
          else{
            $query->where(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"),'<=',date('Y-m-d'));
          }
        }
        else{
          $query->where(DB::raw("DATE_FORMAT(acc_receivables.payment_date, '%Y-%m-%d')"),'<',date('Y-m-d')); // Late Collection
        }
    })
    ->whereNull('acc_receivable_details.deleted_at')
    ->where('acc_receivables.loan_id',$this->loan_id)
    ->whereIn('acc_receivables.payment_status_id',[2,3]);
    return $data;
  }

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

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

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

  public function getmaintenancelateAttribute()
  {
    $AccReceivables = $this->funAccReceivables()->sum('maintenance_per_month');
    $Detail = $this->joinAccWithAccDetails()->sum('acc_receivable_details.paid_maintenance');
    return $AccReceivables-$Detail;
  }

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

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

  public function getdatelateAttribute()
  {
    return $this->funAccReceivables()->where('client_id',$this->client_id)->min('payment_date');
  }

  public function getLastDateLateAttribute()
  {
    return $this->funAccReceivables()->where('client_id',$this->client_id)->where('payment_date','<',date('Y-m-d'))->max('payment_date');
  }

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

  public function getLastReceivedDateAttribute()
  {
    return $this->funAccReceivables()->where('client_id',$this->client_id)->max('received_date');
  }

  public function getfirstIdDatelateAttribute()
  {
    return $this->funAccReceivables()->where('client_id',$this->client_id)->min('id');
  }

  public function gettotallateAttribute()
  {
    $total_late = 0;
    $now = Carbon::now();
    $acc = AccReceivables::find($this->first_id_date_late);
    if($acc!=null) {
      $payment_date = Carbon::parse(date('Y-m-d', strtotime($acc->payment_date)));
      $today        = date('Y-m-d');
      $diff_date    = Carbon::parse(date('Y-m-d', strtotime($today)));
      $interval     = $payment_date->diff($diff_date);
      $total_late   = intval($interval->format("%R%a"));
      return array(
        'total_late'   => $acc->payment_date>$now?0:$total_late,
        'total_penalty'=> 0,
        'start_date'   => $acc->payment_date>$now?'':$acc->payment_date,
      );
    }
    else{
      return '';
    }
  }
}
