<?php

namespace App\Models;

use Carbon\Carbon;
use App\Helpers\Helper;
use Laravel\Sanctum\HasApiTokens;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use Spatie\Permission\Traits\HasRoles;
use Illuminate\Notifications\Notifiable;
use Spatie\Permission\Models\Permission;
use Spatie\Activitylog\Contracts\Activity;
use Spatie\Activitylog\Traits\LogsActivity;
use Illuminate\Database\Eloquent\SoftDeletes;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Foundation\Auth\User as Authenticatable;

class User extends Authenticatable
{
    use HasApiTokens, HasFactory, Notifiable, HasRoles, SoftDeletes, LogsActivity;

    protected $fillable = [
        'id',
        'name',
        'code',
        'branch_id',
        'currency_id',
        'nickname',
        'gender',
        'email',
        'password',
        'contact_number',
        'photo',
        'attach_file',
        'status',
        'last_seen',
        'address',
        'type',
        'date_start_working',
        'type_document',
        'id_card',
        'place_of_birth',
        'date_of_birth',
        'create_card_date',
        'expired_card_date',
        'work_place',
        'institution',
        'position',
        'province_id',
        'district_id',
        'commune_id',
        'village_id'
    ];

    protected static $logAttributes = [
        'id',
        'name',
        'code',
        'branch_id',
        'currency_id',
        'nickname',
        'gender',
        'email',
        'password',
        'contact_number',
        'photo',
        'attach_file',
        'status',
        'last_seen',
        'address',
        'type',
        'date_start_working',
        'type_document',
        'id_card',
        'place_of_birth',
        'date_of_birth',
        'create_card_date',
        'expired_card_date',
        'work_place',
        'institution',
        'position',
        'province_id',
        'district_id',
        'commune_id',
        'village_id'
    ];

    protected static $recordEvents = ['deleted', 'created', 'updated'];
    protected static $logName = 'User Model';
    protected static $logOnlyDirty = true;

    public function getDescriptionForEvent(string $eventName): string
    {
        return "This user has been {$eventName}";
    }

    public function tapActivity(Activity $activity, string $eventName)
    {
        $activity->ip = request()->ip();
        $activity->agent = request()->header('user-agent');
    }

    public function scopeSearch($query, $searchText)
    {
        if (isset($searchText)) {
            $query->Name($searchText);
            $query->Email($searchText);
            $query->ContactNumber($searchText);
        }
        return $query;
    }

    public function scopeName($query,$searchText)
    {
        return $query->where("name","LIKE","$searchText%");
    }
    public function scopeEmail($query, $searchText)
    {
        $query->orWhere("email", "LIKE", "$searchText%");
    }
    public function scopeContactNumber($query, $searchText)
    {
        $query->orWhere("contact_number", "LIKE", "$searchText%");
    }

    protected $hidden = [
        'password',
        'remember_token',
    ];

    protected $casts = [
        'email_verified_at' => 'datetime',
    ];

    public function getAllPermissionsAttribute()
    {
        $permissions = [];
        foreach (Permission::all() as $permission) {
            if (Auth::user()->can($permission->name)) {
                $permissions[] = $permission->name;
            }
        }
        return $permissions;
    }
    public function loan()
    {
        return $this->hasMany(Loan::class, 'co_id','id');
    }
    public function userHasCO(){
        return $this->hasMany(Group::class, 'user_id','id');
    }
    public function village()
    {
        return $this->belongsTo(Village::class, 'village_id');
    }
    public function commune()
    {
        return $this->belongsTo(Commune::class, 'commune_id');
    }
    public function district()
    {
        return $this->belongsTo(District::class, 'district_id');
    }
    public function province()
    {
        return $this->belongsTo(Province::class, 'province_id');
    }
    public function userDetails(){
        return $this->hasMany(UserBranch::class,'user_id');
    }

    public function userBranchSelect()
    {
        return Branch::get()->map(function($value){
            $selected = array_search($value->id, array_column($this->userDetails->toArray(), 'branch_id'));
            return [
                'id'      => $value->id,
                'name_en' => $value->name_en,
                'name_km' => $value->name_km,
                'selected'=> (string)$selected ==""?false:true,
            ];
        });
    }

    public function getsuminterestAttribute()
    {
        return Loan::where('co_id',$this->id)
        ->where(function($query){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',2);
            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);
            }
        })
        ->whereHas('co', function ($query) {
            $query->where('status',1);
        })
        ->whereHas('client', function ($query) {
            $query->where('status',1);
        })
        ->sum('total_interest');
    }
    public function getsumriskAttribute()
    {
        return Loan::where('co_id',$this->id)
        ->where(function($query){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',2);
            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);
            }
        })
        ->whereHas('co', function ($query) {
            $query->where('status',1);
        })
        ->whereHas('client', function ($query) {
            $query->where('status',1);
        })
        ->sum('total_risk');
    }
    public function getsummaintenanceAttribute()
    {
        return Loan::where('co_id',$this->id)
        ->where(function($query){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',2);
            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);
            }
        })
        ->whereHas('co', function ($query) {
            $query->where('status',1);
        })
        ->whereHas('client', function ($query) {
            $query->where('status',1);
        })
        ->sum('total_maintenance');
    }
    public function getpaidriskAttribute()
    {
        return AccReceivableDetails::where('co_id',$this->id)
        ->where(function($query){
            $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()->client_id ==[]){
                $query->whereIn('client_id',request()->client_id);
            }
            if(!request()->co_id ==[])
            {
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('paid_risk');
    }
    public function getpaidmaintenanceAttribute()
    {
        return AccReceivableDetails::where('co_id',$this->id)
        ->where(function($query){
            $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()->client_id ==[]){
                $query->whereIn('client_id',request()->client_id);
            }
            if(!request()->co_id ==[])
            {
                $query->whereIn('co_id',request()->co_id);
            }
        })
         ->whereHas('co', function ($query) {
            $query->where('status',1);
        })
        ->whereHas('client', function ($query) {
            $query->where('status',1);
        })
        ->sum('paid_maintenance');
    }
    public function getbalanceinterestAttribute()
    {
        return Loan::where('co_id',$this->id)
        ->where(function($query){
            $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);
            }
        })
        ->sum('balance_interest');
    }

    public function getbalanceriskAttribute()
    {
        return Loan::where('co_id',$this->id)
        ->where(function($query){
            $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);
            }
        })
        ->sum('balance_risk');
    }
    public function getbalancemaintenanceAttribute()
    {
        return Loan::where('co_id',$this->id)
        ->where(function($query){
            $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);
            }
        })
        ->sum('balance_maintenance');
    }

    // ***************** Summary Co Report ***************** //

    // ចំនួនបញ្ចេញ
    public function getTotalClientAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('loan_status_id',[2,3,5,7]);
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->count();
    }
    // អតិថិជនថ្មី
    public function getTotalClientNewAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('loan_status_id',[2,3,5,7]);
            $query->whereNotIn('client_id',[DB::raw("SELECT client_id FROM loans WHERE loan_status_id IN (2,3,5,7)
                GROUP BY client_id HAVING COUNT(client_id) >1 ")
            ]);
            if(!request()->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]) {
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->count();
    }
    // ទឹកប្រាក់បញ្ចេញ
    public function getsumprincipalAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',2);
            if(!request()->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('loan_out');
    }
    // ទឹកប្រាក់បញ្ចេញអតិ.ថ្មី
    public function getSumClientNewAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereIn('loan_status_id',[2,3,5,7]);
            $query->whereNotIn('client_id', [
                DB::raw("SELECT client_id FROM loans
                WHERE loan_status_id IN (2,3,5,7)
                GROUP BY client_id HAVING COUNT(client_id) >1 ")
            ]);
            if(!request()->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('loan_out');
    }
    // ចំនួនសកម្ម
    public function getClientActiveAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',2);
            $query->groupBy('client_id');
            if(!request()->date==[]) {
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->count();
    }
    // ចំនួនអសកម្ម
    public function getClientInactiveAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',3);
            $query->groupBy('client_id');
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->count();
    }
    // ចំនួនបង់ផ្តាច់
    public function getClientCompleteAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->where('loan_status_id',3);
            $query->groupBy('client_id');
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(completed_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->count();
    }
    // ទឹកប្រាក់ក្នុងដៃអតិថិជន
    public function getbalanceprincipalAttribute()
    {

        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        $reschedules=  Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereNotIn('loan_status_id',[1,4,6]);
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('reschedule_amount');

        $principal=  Loan::where('co_id',$this->id)->where(function($query) use($month){
            $query->whereIn('branch_id',Helper::byBranch());
            $query->where('currency_id',Helper::byCurrency());
            $query->whereNotIn('loan_status_id',[1,4,6]);
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(installment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(installment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('loan_out');

        $paid_principal = AccReceivableDetails::where('co_id',$this->id)->where(function($query) use($month) {
            $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()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('paid_principal');
        $total = ($reschedules+$principal)-$paid_principal;
        return $total;
    }

    public function funAccReceivables()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        $data = AccReceivables::where('co_id',$this->id)
        ->whereIn('branch_id',Helper::byBranch())
        ->where('currency_id',Helper::byCurrency())
        ->where('payment_date','<',date('Y-m-d'))
        ->whereIn('payment_status_id',[2,3])
        ->where(function ($query) use($month){
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(payment_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(payment_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        });
        return $data;
    }
    public function joinAccWithAccDetails()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        $data = AccReceivables::leftjoin('acc_receivable_details','acc_receivable_details.acc_id','acc_receivables.id')
        ->whereNull('acc_receivable_details.deleted_at')
        ->where('acc_receivables.co_id',$this->id)
        ->whereIn('acc_receivables.branch_id',Helper::byBranch())
        ->where('acc_receivables.currency_id',Helper::byCurrency())
        ->where('acc_receivables.payment_date','<',date('Y-m-d'))
        ->whereIn('acc_receivables.payment_status_id',[2,3])
        ->where(function ($query) use($month){
            if(!request()->date==[]){
                $query->whereBetween(DB::raw("DATE_FORMAT(acc_receivable_details.received_date, '%Y-%m-%d')"), request()->date);
            }
            if(!request()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(acc_receivable_details.received_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('acc_receivables.co_id',request()->co_id);
            }
        });
        return $data;
    }
    // ចំនួនយឺត
    public function getcountdaylateAttribute()
    {
        return $this->funAccReceivables()->groupBy('client_id')->get()->count();
    }
    // ទឹកប្រាក់យឺត
    public function getprincipallateAttribute()
    {
        $AccReceivables = $this->funAccReceivables()->sum('principal');
        $Detail = $this->joinAccWithAccDetails()->sum('acc_receivable_details.paid_principal');
        return $AccReceivables - $Detail;
    }
    // ការប្រាក់បានប្រមូល
    public function getpaidinterestAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return AccReceivableDetails::where('co_id',$this->id)->where(function($query) use($month){
            $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()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('paid_interest');
    }
    // ប្រាក់ដើមបានប្រមូល
    public function getpaidprincipalAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return AccReceivableDetails::where('co_id',$this->id)->where(function($query) use($month){
            $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()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('paid_principal');
    }
    // ប្រាក់ពិន័យបានប្រមូល
    public function getpaidPenaltyAttribute()
    {
        $month = empty(request()->month)?null:date('Y-m',strtotime(request()->month));
        return AccReceivableDetails::where('co_id',$this->id)->where(function($query) use($month) {
            $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()->month ==[]){
                $query->where(DB::raw("DATE_FORMAT(received_date, '%Y-%m')"),'<=',$month);
            }
            if(!request()->co_id ==[]){
                $query->whereIn('co_id',request()->co_id);
            }
        })
        ->sum('paid_penalty');
    }
    
}
