<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Carbon\Carbon;
use App\Models\User;
use App\Models\Candidate;
use App\Models\CandidateApplication;
// use App\Models\ContactInfo;
use App\Models\JobRole;
use App\Models\Profession;
use App\Models\Experience;
use App\Models\Education;
use App\Models\Skill;
use App\Models\CandidateLanguage;
use App\Models\CandidateExam;
use App\Models\Webinars;
use App\Models\IndianUniversity;
use App\Models\Universities;
use App\Models\Diploma_course;
use App\Models\InterestedCandidatesWebinar;
use App\Models\Intake;
use App\Models\Partner;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
use App\Models\Source;
use App\Models\UserTimeline;
use App\Models\Countries;
use App\Models\State;
use App\Models\Admin;
use App\Models\Documents;
use App\Models\Followup;
use Modules\Location\Entities\Country;
use App\Models\Service;
use App\Helpers\AdminHelper;

class LeadManagementController extends Controller
{
  
    public function index(Request $request)
    {   

        // dd($request->all());

        // dd($request->all());
        $first_time = 0;
        // dd(checkleadManagerEnvRole());
        if(empty($request->all())){
            
            $first_time = 1;
            if (checkleadManagerEnvRole()) {
                
                $request->merge(['assign_to' => Auth::user()->id]);
            }
            if (checkRmEnvRole()) {
                $request->merge(['assign_to' => Auth::user()->id,'assigned_rm' => Auth::user()->id]);
            }

        }elseif(count($request->all()) === 1 && isset($request["page"])){
            $first_time = 1;
        }

       
        abort_if(!userCan('leadmgt.view'), 403);
        ///////////////////////////intakeString////////////////////////////////////////////////
        $intake = [];
        

        ///////////////////////////intakeString////////////////////////////////////////////////

        // $query = Candidate::with(['user' => function ($q) {
        //     $q->whereNull('deleted_at'); 
        // }], 'followup')->latest('user.assign_date', 'nulls last') ;
        
        // // $query->latest('user.assign_date');
        // // $query->latest();

        // $query->whereHas('user', function ($q) {
        //     $q->whereNotNull('assign_to')
        //         ->where('assign_to', Auth::user()->id);
        // });
        
        if (empty($request->keyword)) {

            // if (!empty($request->input('intake_month')) && !empty($request->input('intake_years'))) {
            //     $intake_month = $request->input('intake_month');
            //     $intake_years = $request->input('intake_years');
            
            //     foreach ($intake_years as $year) {
            //         foreach ($intake_month as $month) {
            //             $yearShort = substr($year, -2);
            //             $intake[] = $month . "'" . $yearShort;
            //         }
            //     }
            
            // }
           
              
            $query = Candidate::with(['user' => function ($q) {
                $q->whereNull('deleted_at');

                
            }], 'followup')
            
            ->whereHas('user', function ($q) use ($request,$first_time) {
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(is_array($request->input('assign_to'))){

                        // if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                        // }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        // $q->whereNull('assign_to');
                        // }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        //     $q->where('assign_to', $request->input('assign_to'));
                        // }else{
                        //     if (checkleadManagerEnvRole()) {
                        //         $q->where('assign_to', Auth::user()->id);
                        //     }
                        //     if (checkRmEnvRole()) {
                        //         $q->where(function ($qr) {
                        //             $qr->where('assign_to', Auth::user()->id)
                        //               ->orWhere('assigned_rm', Auth::user()->id);
                        //         });
                        //     }
                        // }

                        if(is_array($request->input('assign_to'))){

                        }
                        $assignTo = $request->input('assign_to');
                        
                        if (in_array("all", $assignTo)) {
                            
                        } elseif (!empty($assignTo) && in_array("na", $assignTo)) {
                            $q->whereNull('assign_to');
                        } elseif (!empty($assignTo)) {
                            $q->whereIn('assign_to', $assignTo);
                        } else {
                            
                            if (checkleadManagerEnvRole()) {
                                $q->where('assign_to', Auth::user()->id);
                            }
                            if (checkRmEnvRole()) {
                                $q->where(function ($qr) {
                                    $qr->where('assign_to', Auth::user()->id)
                                        ->orWhere('assigned_rm', Auth::user()->id);
                                });
                            }
                        }
                    }elseif(@$first_time == 0){
                        
                    }else{
                        
                        if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                        }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        $q->whereNull('assign_to');
                        }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                            $q->where('assign_to', $request->input('assign_to'));
                        }else{
                            
                            if (checkleadManagerEnvRole()) {
                                $q->where('assign_to', Auth::user()->id);
                            }
                            if (checkRmEnvRole()) {
                                $q->where(function ($qr) {
                                    $qr->where('assign_to', Auth::user()->id)
                                        ->orWhere('assigned_rm', Auth::user()->id);
                                });
                            }
                        }
                    }
                }else{
                        
                    $q->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            });

            // dd($query->toSql());
            if(is_array($request->input('country_id'))){
                $query->whereIn('country_id',$request->country_id);
            }
            if(is_array($request->input('state_id'))){
                $query->orwhereIn('state_id',$request->state_id);
            }
            if(is_array($request->input('city_id'))){
                $query->orwhereIn('city_id',$request->city_id);
            }

            $totalCount = $query->count();
            

            if ($request->keyword && $request->keyword != null) {

                $query->whereHas('user', function ($q) use ($request) {

                    $q->where('name', 'LIKE', "%$request->keyword%")
                        ->orWhere('email', 'LIKE', "%$request->keyword%")
                        ->orWhere('id', $request->keyword)
                        ->orWhere('phone', 'LIKE', "%$request->keyword%");
                });
            }

            if (!empty($request->input('daterange'))) {
               
                $query->whereHas('user', function ($q) use ($request) {
                $dates = explode(' - ', $request->input('daterange'));
                $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay();
                $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay();
                $q->whereBetween('assign_date', [$startDate, $endDate]);
                });
            }

            
            if (!empty($request->input('daterangeCreated'))) {            
                $query->whereHas('user', function ($q) use ($request) {
                    $dates = explode(' - ', $request->input('daterangeCreated'));
                    $startDate = date('Y-m-d H:i:s', strtotime(trim($dates[0])));
                    $endDate = date('Y-m-d H:i:s', strtotime(trim($dates[1])));
                    $q->whereBetween('created_at', [$startDate, $endDate]);
                });
            }
            
           
            // if (!empty($request->input('daterangeCreated'))) {
               
            //     $query->whereHas('user', function ($q) use ($request) {
            //     $dates = explode(' - ', $request->input('daterangeCreated'));
            //     $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay();
            //     $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay();
            //     $q->whereBetween('created_at', [$startDate, $endDate]);
            //     });
            // }
            

            if(is_array($request->input('preferred_country'))){

                    $query->whereHas('destination', function ($q) use ($request) {
                        $q->whereNotNull('preferred_country');
                        if(in_array("all", $request->input('preferred_country'))){
                            $preferredCountries = Countries::get()->pluck('id')->toArray();
                            
                        }else{
                            $preferredCountries = $request->input('preferred_country');
                        }
                        $q->where(function ($query) use ($preferredCountries) {
                            $data_find = 0;
                            foreach ($preferredCountries as $country) {
                                $query->orWhere('preferred_country', 'like', '%'.",".$country."," . '%');
                                $query->orWhere('preferred_country', 'like', '%'.$country."," .'%');
                                $query->orWhere('preferred_country', 'like', '%'.",".$country.'%');
                                $query->orWhere('preferred_country', '=', $country);
                            }
                        });
                    });
            }else{
                if (!empty($request->input('preferred_country'))) {
                    $query->whereHas('destination', function ($q) use ($request) {
                        $preferredCountries = $request->input('preferred_country', []);
                        $q->whereNotNull('preferred_country');
                        
                        foreach ($preferredCountries as $country) {
                            $q->where(function ($innerQuery) use ($country) {
                             $innerQuery->whereRaw("FIND_IN_SET($country,preferred_country)");
                                
                            });
                        }
                    });
                }
            }

            
            // if (!empty($request->input('preferred_country'))) {
            //     $query->whereHas('destination', function ($q) use ($request) {
            //         $preferredCountries = $request->input('preferred_country', []);
            //         $q->whereNotNull('preferred_country');
                    
            //         foreach ($preferredCountries as $country) {
            //             $q->where(function ($innerQuery) use ($country) {
            //              $innerQuery->whereRaw("FIND_IN_SET($country,preferred_country)");
                            
            //             });
            //         }
            //     });
            // }
               
            if (is_array($request->input('student_category'))) {
                if(in_array("all", $request->input('student_category'))){
                    $query->whereIn('student_category', ['Gold','Silver','Bronze']);
                }else{
                    $query->whereIn('student_category', $request->student_category);;
                }
                
            }else{
                if(!empty($request->input('student_category'))){
                    $query->where('student_category', $request->student_category);
                }
               
            }

            // if (!empty($request->input('student_category'))) {
               
            //     $query->where('student_category', $request->student_category);
            // }

            $query->whereHas('user', function ($q) use ($request) {
                if (is_array($request->input('lead_type'))) {
                    if(in_array("all", $request->input('lead_type'))){
                        $q->whereIn('lead_type', ['Hot','Cold','Warm','Not Eligible']);
                    }elseif(in_array("na", $request->input('lead_type'))){
                        $q->where('lead_type','=', null);
                        $q->orwhere('lead_type','=', "");
                        $q->orwhereIn('lead_type', $request->lead_type);
                    
                    }else{
                        $q->whereIn('lead_type', $request->lead_type);
                    }
                }else{
                    if(!empty($request->input('lead_type'))){
                        $q->where('lead_type', $request->lead_type);
                    }
                }

                if (is_array($request->input('stages'))) {
                    
                    if(in_array("all", $request->input('stages'))){
                        $q->whereIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Work Visa','Student backed out / Dropped','Test Preparation']);
                        
                    }elseif(in_array("na", $request->input('stages'))){
                        $q->where('stages','=',null);
                        $q->orwhere('stages','=',"");
                        $q->orwhereIn('stages', $request->stages);
                    }else{
                        $q->whereIn('stages', $request->stages);
                    }
                    
                }else{
                    if (!empty($request->input('stages'))) {
                        if (!empty($request->input('stages')) && $request->input('stages') == "na") {
                            
                            $q->whereNotIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Work Visa','Student backed out / Dropped','Test Preparation'])
                            ->orWhereNull('stages');
                            
                        }elseif (!empty($request->input('stages'))) {
                            $q->where('stages', $request->stages);
                        
                        }
                    }
                }


                // if (!empty($request->input('lead_type'))) {
                //     $q->where('lead_type', $request->lead_type);
                // }

                // if(is_array($request->input('stages'))){
                //     $q->whereIn('stages', $request->stages);
                // }


                // if (!empty($request->input('stages')) && $request->input('stages') == "na") {
                //     $q->whereNotIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Work Visa','Student backed out / Dropped','Test Preparation'])
                //     ->orWhereNull('stages');
                // }elseif (!empty($request->input('stages'))) {
                //     $q->where('stages', $request->stages);
                // }

                if(is_array($request->input('source_value'))){
                    if(in_array("all", $request->input('source_value'))){
                        $q->where('source',"!=", null);
                        $q->where('source',"!=", "");
                        
                    }elseif(in_array("na", $request->input('source_value'))){
                        $q->where('source',"=", null);
                        $q->orwhere('source',"=", "");
                    }else{
                        $source_values = $request->input('source_value');
                        $q->where(function($query) use ($source_values) {
                            foreach ($source_values as $source_value) {
                                $query->orWhereRaw("FIND_IN_SET(?, source)", [$source_value]);
                            }
                        });
                    }
                    
                    
                }
                if ($request->filled('TATfiter') && $request->input('TATfiter') === "crossed") {
                    $q->whereNotNull('TAT')
                      ->where('TAT', '<', 0);
                }
            });

            if (!empty($request->input('followUp'))) {
                $followupValue = $request->input('followUp');
                if ($followupValue == "no_followUp") {

                    $query->whereHas('user', function ($q){
                        $q->where('stages', '!=', 'Student backed out / Dropped')
                        ->orWhereNull('stages');
                    });
                
                    $query->whereDoesntHave('followup', function ($q) use ($request){
                        if(userCan('admin.assign_list') || userCan('admin.assign')){
                            // if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                            // }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                            //     $q->whereNull('assign_to');
                            // }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                            //     $q->where('assign_to', $request->input('assign_to'));
                            // }else{
                            //     if (checkleadManagerEnvRole()) {
                            //         $q->where('assign_to', Auth::user()->id);
                            //     }
                             
                            // }
                            
                            if(is_array($request->input('assign_to'))){
                                $assignTo = $request->input('assign_to');
                                if (in_array("all", $assignTo)) {
                                } elseif (!empty($assignTo) && in_array("na", $assignTo)) {
                                    $q->whereNull('assign_to');
                                } elseif (!empty($assignTo)) {
                                    $q->whereIn('assign_to', $assignTo);
                                } else {
                                    if (checkleadManagerEnvRole()) {
                                        $q->where('assign_to', Auth::user()->id);
                                    }
                                }   
                            }elseif(@$first_time == 1){
                        
                            }
                            else{
                                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                                        $q->whereNull('assign_to');
                                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                                        $q->where('assign_to', $request->input('assign_to'));
                                    }else{
                                        if (checkleadManagerEnvRole()) {
                                            $q->where('assign_to', Auth::user()->id);
                                        }
                                     
                                    }
                            }
                        }else{
                           
                            // $q->whereNotNull('assign_to')
                            // ->where('assign_to', Auth::user()->id); 
                        }

                    });
                    
                    
                } elseif ($followupValue == "all") {
                $query->whereHas('followup', function ($q) {
                        $q->whereNotNull('id');
                    });
                } elseif ($followupValue == "inactive") {
                    $nowDate = Carbon::now();
                    // dd($nowDate);
                    $tenDaysAgo = Carbon::now()->subDays(7);
                    $query->whereHas('user', function ($q){
                        $q->where('stages', '!=', 'Student backed out / Dropped');
                    
                    });
                    $query->whereHas('followup', function ($q) use ($nowDate, $tenDaysAgo) {
                            // $q->whereNotNull('id');
                            $q->whereNotNull('id')
                            ->where('close_status', 0)
                            ->Where('datetime', '<=', $nowDate)
                            // ->whereNotBetween('created_at',[$tenDaysAgo, $nowDate->subDays(1)]);
                            ->where('created_at','<=', $tenDaysAgo);
                            // ->orWhere('created_at', '<', $tenDaysAgo);
                          

                        });
                }
            
            }


            // if (!empty($intake)) {
            //     $query->whereHas('destination', function ($q) use ($intake) {
            //         $q->whereNotNull('intake');
            //         $intakeConditions = [];
            
            //         foreach ($intake as $intakeValue) {
            //             // Format each value correctly within FIND_IN_SET
            //             $intakeValueFormatted = str_replace("'", "''", $intakeValue);
            //             $intakeConditions[] = "FIND_IN_SET('$intakeValueFormatted', intake)";
            //         }
            
            //         $q->where(function ($innerQuery) use ($intakeConditions) {
            //             // Join the conditions with OR
            //             $innerQuery->orWhereRaw(implode(' OR ', $intakeConditions));
            //         });
            //     });
            // }

            // if(!empty($request->input('intake_years')) || !empty($request->input('intake_month'))) {
            //     $query->whereHas('destination', function ($q) use ($request) {
                    
            //         if (!empty($request->input('intake_years'))) {
            //             $intakeYears = $request->input('intake_years');
            //             $q->where(function($query) use ($intakeYears) {
            //                 foreach ($intakeYears as $year) {
            //                     $query->orWhereRaw("FIND_IN_SET(?, intake_years)", [$year]);
            //                 }
            //             });
            //         }

            //         if (!empty($request->input('intake_month'))) {
            //             $intake_months = $request->input('intake_month');
            //             $q->where(function($query) use ($intake_months) {
            //                 foreach ($intake_months as $intake_month) {
            //                     $query->orWhereRaw("FIND_IN_SET(?, intake_months)", [$intake_month]);
            //                 }
            //             });
            //         }
        
                    
            //     });
            // }


            if (is_array($request->input('intake_years'))) {
                if (in_array("all", $request->input('intake_years'))) {
                    $intake_years = [];
                    for ($i = 2022; $i <= date('Y') + 3; $i++) {
                        $intake_years[] = $i;
                    }
                } else {
                    $intake_years = $request->input('intake_years');
                }
            
                $query->whereHas('destination', function ($q) use ($intake_years) {
                    $q->where(function ($query) use ($intake_years) {
                        foreach ($intake_years as $year) {
                            $query->orWhereRaw("FIND_IN_SET(?, intake_years)", [$year]); 
                        }
                    });
                });
            }

            if (is_array($request->input('intake_month'))) {
                if (in_array("all", $request->input('intake_month'))) {
                    $intake_month = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'july', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
                } else {
                    $intake_month = $request->input('intake_month');
                }
            
                $query->whereHas('destination', function ($q) use ($intake_month) {
                    $q->where(function ($query) use ($intake_month) {
                        foreach ($intake_month as $month) {
                            $query->orWhereRaw("FIND_IN_SET(?, intake_months)", [$month]); 
                        }
                    });
                });
            }

            
            // if(!empty($request->input('payment_status'))) {
            //     $query->whereHas('orders', function ($q) use ($request) {
                    
            //         $q->where('status', $request->input('payment_status'));
                    
            //     });
            // }
               
            if(!empty($request->input('payment_status'))) {
                if($request->input('payment_status') == "all"){
                    $query->whereHas('orders', function ($q) use ($request) {
                        $q->where('status', '!=', null);
                    });
                }elseif( $request->input('payment_status') == "unpaid_with_order" || $request->input('payment_status') == "paid"){
                    
                    $query->whereHas('orders', function ($q) use ($request) {
                    
                        if($request->input('payment_status')  == "unpaid_with_order"){
                            $status = "unpaid";
    
                            $q->where('status', '=', $status)
                            ->whereNotExists(function ($query) use ($status) {
                                $query->select(DB::raw(1))
                                    ->from('orders')
                                    ->whereRaw('candidates.id = orders.candidate_id')
                                    ->where('status', '<>', $status);
                            });
                        }else if($request->input('payment_status')  == "paid"){
                            $q->where('status', $request->input('payment_status'));
                        }
                       
                    });
                }else{
                    $query->whereDoesntHave('orders', function ($q) use ($request) {

                    });
                }

                
            }


            $query->whereHas('user', function ($q) use ($request,$first_time) {

                // if(userCan('candidate.assignedRm')){
                //     if(!empty($request->input('assigned_rm')) && $request->input('assigned_rm') == "na"){
                //         $q->whereNull('assigned_rm')
                //         ->orWhere('assigned_rm', 0);
                //     }elseif(!empty($request->input('assigned_rm')) && $request->input('assigned_rm') != "na"){
                //         $q->where('assigned_rm', $request->input('assigned_rm'));
                //     }
                // }

                if (userCan('candidate.assignedRm') || userCan('candidate.assignedRm_list')) {
                    
                
                    if(is_array($request->input('assigned_rm'))){
                        if (in_array("na", $request->input('assigned_rm'))) {
                            $q->whereNull('assigned_rm')
                            ->orWhere('assigned_rm', 0);
                        }
                        elseif(in_array("all", $request->input('assigned_rm'))){
                            $q->where('assigned_rm',"!=" ,null);
                            $q->where('assigned_rm',"!=" ,"");
                        }else{
                            $q->whereIn('assigned_rm', $request->input('assigned_rm'));
                        }
                    }
                }

                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(is_array($request->input('assign_to'))){

                    
                        // if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                        // }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        //     $q->whereNull('assign_to');
                        // }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        //     $q->where('assign_to', $request->input('assign_to'));
                        // }else{
                        //     if (checkleadManagerEnvRole()) {
                        //         $q->where('assign_to', Auth::user()->id);
                        //     }
                        //     if (checkRmEnvRole()) {
                        //         $q->where(function ($qr) {
                        //             $qr->where('assign_to', Auth::user()->id)
                        //             ->orWhere('assigned_rm', Auth::user()->id);
                        //         });
                        //     }
                        // }
                        $assignTo = $request->input('assign_to');
                        
                        if (in_array("all", $assignTo)) {
                            
                        } elseif (!empty($assignTo) && in_array("na", $assignTo)) {
                            $q->whereNull('assign_to');
                        } elseif (!empty($assignTo)) {
                            $q->whereIn('assign_to', $assignTo);
                        } else {
                            
                            if (checkleadManagerEnvRole()) {
                                $q->where('assign_to', Auth::user()->id);
                            }
                            if (checkRmEnvRole()) {
                                $q->where(function ($qr) {
                                    $qr->where('assign_to', Auth::user()->id)
                                        ->orWhere('assigned_rm', Auth::user()->id);
                                });
                            }
                        }
                    
                    }elseif(@$first_time == 0){
                        
                    }
                    else{

                        if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                        }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                            $q->whereNull('assign_to');
                        }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                            $q->where('assign_to', $request->input('assign_to'));
                        }else{
                            
                            if (checkleadManagerEnvRole()) {
                                $q->where('assign_to', Auth::user()->id);
                            }
                            if (checkRmEnvRole()) {
                                $q->where(function ($qr) {
                                    $qr->where('assign_to', Auth::user()->id)
                                    ->orWhere('assigned_rm', Auth::user()->id);
                                });
                            }
                        }
                    }
                }else{
                    $q->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            });
            
            
            if(!empty($request->input('values'))){
                
                $query->whereHas('user', function ($q) use ($request) {

                if($request->input('values') == "inquiry"){
                    $q->whereIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested']);
                }
                if($request->input('values') == "application"){
                    $q->whereIn('stages', ['Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending']);
                }
                if($request->input('values') == "offer"){
                    $q->whereIn('stages', ['Offer Issued','Offer Accepted','Offer Rejected']);
                }
                if($request->input('values') == "visa"){
                    $q->whereIn('stages', ['Visa Rejected','Visa Accepted','Visa In Process']);
                }
                if($request->input('values') == "feePayment"){
                    $q->whereIn('stages', ['Initial Fee / Tuition Fee Deposit']);
                }
                if($request->input('values') == "defered"){
                    $q->whereIn('stages', ['Student Defered']);
                }
                if($request->input('values') == "dropped"){
                    $q->whereIn('stages', ['Student backed out / Dropped']);
                }
                if($request->input('values') == "workVisa"){
                    $q->whereIn('stages', ['Work Visa']);
                }
                
                if($request->input('values') == "notDropped"){
                    $q->where('stages', '!=', 'Student backed out / Dropped')
                    ->orWhereNull('stages');
                }
                if($request->input('values') == "testPreparation"){
                    $q->whereIn('stages', ['Test Preparation']);
                }
                if($request->input('values') == "na"){
                    $q->whereNotIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Work Visa','Student backed out / Dropped','Test Preparation'])
                    ->orWhereNull('stages');
                    // $q->whereNotIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Work Visa','Student backed out / Dropped','Test Preparation'])->orWhereNull('stages');
                }

                if($request->input('values') == "na_work_visa"){
                    
                    $q->whereNotIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Student backed out / Dropped','Test Preparation'])
                    ->orWhereNull('stages');
                    
                    // $q->whereNotIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested','Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending','Offer Issued','Offer Accepted','Offer Rejected','Visa Rejected','Visa Accepted','Visa In Process','Initial Fee / Tuition Fee Deposit','Student Defered','Work Visa','Student backed out / Dropped','Test Preparation'])->orWhereNull('stages');
                }

                });
            }

            
            if (is_array(@$request->assign_from)) {
                
               
                if(in_array("All", $request->assign_from)){
                    $query->whereHas('user', function ($q){
                        $q->whereNotNull('assign_from');
                    });
                
                }else{
                    $query->whereHas('user', function ($q) use ($request){
                        $q->whereIn('assign_from', $request->assign_from);
                    });
                }
            }else{
                
                if (!empty(@$request->assign_from)) {
                   
                    if(@$request->assign_from == "All"){
                        $query->whereHas('user', function ($q){
                            $q->whereNotNull('assign_from');
                        });
                    
                    }else{
                        $query->whereHas('user', function ($q) use ($request){
                            $q->where('assign_from', $request->assign_from);
                        });
                    }
                }
            }
            

            // if (!empty(@$request->assign_from)) {
            //     if(in_array("All", $request->assign_from)){
            //         $query->whereHas('user', function ($q){
            //             $q->whereNotNull('assign_from');
            //         });
                
            //     }else{
            //         $query->whereHas('user', function ($q) use ($request){
            //             $q->whereIn('assign_from', $request->assign_from);
            //         });
            //     }
            // }


            if (!empty($request->input('daterangeCreated')) && !empty($request->input('daterange'))) {
                $query->orderByDesc(function ($q) {
                    $q->select('created_at')
                        ->from('users')
                        ->whereColumn('candidates.user_id', 'users.id')
                        ->limit(1);
                });
            }elseif (!empty($request->input('daterangeCreated'))) {
                $query->orderByDesc(function ($q) {
                    $q->select('created_at')
                        ->from('users')
                        ->whereColumn('candidates.user_id', 'users.id')
                        ->limit(1);
                });
            }elseif (!empty($request->input('daterange'))) {
                $query->orderByDesc(function ($q) {
                    $q->select('assign_date')
                        ->from('users')
                        ->whereColumn('candidates.user_id', 'users.id')
                        ->orderBy('assign_date', 'desc')
                        ->limit(1);
                });
            }else{
                $query->orderByDesc(function ($q) {
                    $q->select('created_at')
                        ->from('users')
                        ->whereColumn('candidates.user_id', 'users.id')
                        ->orderBy('created_at', 'desc')
                        ->limit(1);
                });
            }

           
        }else{
            

            if($request->keyword != "ad:"){

            }else{
                
            }


            $query = Candidate::with(['user' => function ($q) {
                $q->whereNull('deleted_at')->latest('assign_date');
            }], 'followup');


            if (checkleadManagerEnvRole()) {
                $query->orderByDesc(function ($q) {
                    $q->select('assign_date')
                        ->from('users')
                        ->whereColumn('candidates.user_id', 'users.id')
                        ->orderBy('assign_date', 'desc')
                        ->limit(1);
                });
            }else{
                $query->orderByDesc(function ($q) {
                    $q->select('created_at')
                        ->from('users')
                        ->whereColumn('candidates.user_id', 'users.id')
                        ->orderBy('created_at', 'desc')
                        ->limit(1);
                });
            }

            
            $totalCount = $query->count();

            
            $stringToCheck = $request->keyword;
            if (strpos($stringToCheck, "ad:") !== false) {
               
                $stringToCheck = str_replace("ad:", "", $stringToCheck);
                $query->Where('add_name', 'LIKE', "%$stringToCheck%")->orWhere('campaign_name', 'LIKE', "%$stringToCheck%");
            }else{
                $query->whereHas('user', function ($q) use ($request) {
                
                    $q->where('name', 'LIKE', "%$request->keyword%")
                    ->orWhere('email', 'LIKE', "%$request->keyword%")
                    ->orWhere('id', $request->keyword)
                    ->orWhere('phone', 'LIKE', "%$request->keyword%");
                
            });
            }
            
          
        }
        session_start();
        @$followUp = @$_SESSION['followUp'];
        @$values = @$_SESSION['values'];
        
        if(!empty($request->input('followUp')) && @$followUp != $request->input('followUp')){
          
            $request->merge(['page' => 1]);
        }
        else{
            
            if(@$values != $request->input('values')){
                // dd(@$last_value);
                $request->merge(['page' => 1]);
            }
    
        }
        

        $totalCandidatesCount = $query->count();
        
        $_SESSION['followUp'] = @$request->input('followUp');
        $_SESSION['values'] = @$request->input('values');
        
        
        $candidates = $query->paginate(10)->withQueryString();
        // dd($candidates);
        $main_intake = [];
        //  $main_intake = Intake::whereIn('status', [1])->pluck('name')->toArray();  
        //  $sourcesData = Source::distinct('name')->with("source")->where("parent_id",null)->get();
        $countries = Country::all();

          
        $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
        $admin_list = Admin::where('id', '!=', 1)
            ->whereHas('roles', function ($query) use ($roleEnvNames) {
                $query->whereIn('name', $roleEnvNames);
            })
            ->get()
            ->toArray();

        $role_AVAILABLE_RM_ROLES = explode(',', env('AVAILABLE_RM_ROLES', 'Operation Manager'));
        $admin_list_Operation = Admin::where('id', '!=', 1)
        ->whereHas('roles', function ($query) use ($role_AVAILABLE_RM_ROLES) {
            $query->whereIn('name', $role_AVAILABLE_RM_ROLES);
        })
        ->get()
        ->toArray();
        $sourcesData = Source::distinct('name')->with("source")->where("parent_id",null)->get();
        $services = Service::select('id', 'name')->get();

        
     return view('admin.lead-management.index', compact('candidates','totalCandidatesCount','totalCount','countries','admin_list','admin_list_Operation','sourcesData','services','first_time'));

    
    }

    public function leadManagementCountssss(Request $request)
    {
        
        abort_if(!userCan('leadmgt.view'), 403);
        $endDateF = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
        $roleNames = Auth()->user()->getRoleNames()->toArray();
        $leadManagerEnvRole = env('LEAD_MANAGER_ROLE', 'Lead Manager');

        $adminRoleNames = auth('admin')->user()->getRoleNames()->toArray();
        $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
    
    
         $queryFollowup = Followup::where('datetime', '<=', $endDateF)
         ->where('close_status', 0)
         ->where(function ($queryFollowup) use ($request, $roleNames, $leadManagerEnvRole) {

            if(is_array($request->input('assign_to'))){
                // if(userCan('admin.assign_list') || userCan('admin.assign')){
                //     if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                //     }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                //          $queryFollowup->whereNull('assign_to');
                //     }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                //          $queryFollowup->where('assign_to', $request->input('assign_to'));
                //     }else{
                //         if (checkleadManagerEnvRole()) {
                //             $queryFollowup->where('assign_to', Auth::user()->id);
                //         }
                    
                //     }
                
                // }else{
                //     $queryFollowup->where('assign_to', Auth::user()->id);
                // }
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    $assignTo = $request->input('assign_to');
                    
                    if (in_array("all", $assignTo)) {
                        
                    } elseif (!empty($assignTo) && in_array("na", $assignTo)) {
                        $queryFollowup->whereNull('assign_to');
                    } elseif (!empty($assignTo)) {
                        $queryFollowup->whereIn('assign_to', $assignTo);
                    } else {
                        
                        if (checkleadManagerEnvRole()) {
                            $queryFollowup->where('assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $queryFollowup->where(function ($qr) {
                                $queryFollowup->where('assign_to', Auth::user()->id)
                                    ->orWhere('assigned_rm', Auth::user()->id);
                            });
                        }
                    }
                }else{
                    $queryFollowup->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            }
         })->whereHas('user', function ($q) use ($request, $roleNames, $leadManagerEnvRole) {
                 // if (!empty($request->input('assign_to')) && $request->input('assign_to') != "na") {
                 //     $q->where('assign_to', $request->input('assign_to'));
                 // } else {
                 //     if (Auth::user()->id && in_array($leadManagerEnvRole, $roleNames)) {
                 //         $q->where('assign_to', Auth::user()->id);
                 //     }
                 // }
         })->get();


         $inquiryButtonCount = 0;
         $applicationButtonCount = 0;
         $offerButtonCount = 0;
         $feePaymentButtonCount = 0;
         $deferedButtonCount = 0;
         $droppedButtonCount = 0;
         $visaButtonCount = 0;
         $workVisaButtonCount = 0;

         $testPreparationButtonCount = 0;
         
         $inquiryPendingCount = 0;
         $applicationPendingCount = 0;
         $offerPendingCount = 0;
         $visaPendingCount = 0;
         $workVisaPendingCount = 0;
         $deferedPendingCount = 0;
         $droppedPendingCount = 0;

         $inquiryPendingCount = $queryFollowup->whereIn('user.stages', ['Inquiry','Counselling','University to be suggested','Universities suggested'])->count();

         $applicationPendingCount = $queryFollowup->whereIn('user.stages', ['Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending'])->count();

         $offerPendingCount = $queryFollowup->whereIn('user.stages', ['Offer Issued','Offer Accepted','Offer Rejected'])->count();

         $droppedPendingCount = $queryFollowup->whereIn('user.stages', ['Student backed out / Dropped'])->count();

         $feePaymentPendingCount = $queryFollowup->whereIn('user.stages', ['Initial Fee / Tuition Fee Deposit'])->count();

         $deferedPendingCount = $queryFollowup->whereIn('user.stages', ['Student Defered'])->count();

         $visaPendingCount = $queryFollowup->whereIn('user.stages', ['Visa Rejected','Visa Accepted','Visa In Process'])->count();

         $workVisaPendingCount = $queryFollowup->whereIn('user.stages', ['Work Visa'])->count();

         $query = Candidate::with('user');

        $query->whereHas('user', function ($q) use ($request) {
            if(is_array($request->input('assign_to'))){

                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    $assignTo = $request->input('assign_to');
                    
                    if (in_array("all", $assignTo)) {
                        
                    } elseif (!empty($assignTo) && in_array("na", $assignTo)) {
                        $q->whereNull('assign_to');
                    } elseif (!empty($assignTo)) {
                        $q->whereIn('assign_to', $assignTo);
                    } else {
                        
                        if (checkleadManagerEnvRole()) {
                            $q->where('assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $q->where(function ($qr) {
                                $q->where('assign_to', Auth::user()->id)
                                    ->orWhere('assigned_rm', Auth::user()->id);
                            });
                        }
                    }
                }else{
                    $q->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            }
            
        });

        $results = $query->get();
        $myNonfollowedcount = 0;
        $myNonfollowedcount = $query->whereHas('user', function ($q){
            $q->where('stages', '!=', 'Student backed out / Dropped')
             ->orWhereNull('stages');
        })->whereDoesntHave('followup', function ($query) use ($request){
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $query->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $query->where('assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $query->where('assign_to', Auth::user()->id);
                    }
                   
                }
            }else{
                $query->where('admin_id', Auth::user()->id);
            }
           
        })->count();

      
        $inactiveFollowCount = 0;
        $inactiveFollowCount = Candidate::with('followup')->whereHas('user', function ($q) use ($request) {
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){
                    $q->where('stages', '!=', 'Student backed out / Dropped');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $q->where('stages', '!=', 'Student backed out / Dropped');
                    $q->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $q->where('stages', '!=', 'Student backed out / Dropped');
                    $q->where('assign_to', $request->input('assign_to'));
                }else{
                    $q->where('stages', '!=', 'Student backed out / Dropped');
                    if (checkleadManagerEnvRole()) {
                        $q->where('assign_to', Auth::user()->id);
                    }
                    if (checkRmEnvRole()) {
                        $q->where(function ($qr) {
                            $qr->where('assign_to', Auth::user()->id)
                              ->orWhere('assigned_rm', Auth::user()->id);
                        });
                    }
                }
          
            }else{
                $q->where('stages', '!=', 'Student backed out / Dropped');
                $q->whereNotNull('assign_to')
                ->where('assign_to', Auth::user()->id); 
            }
            
        })->whereHas('followup', function ($q) {
            $nowDate =  Carbon::now();
            $tenDaysAgo =  Carbon::now()->subDays(7);
            $q->where(function ($query) use ($nowDate, $tenDaysAgo) {
                $query->where('close_status', 0)
                    ->Where(function ($query) use ($nowDate, $tenDaysAgo) {
                        $query->where('datetime', '<=', $nowDate)
                            ->where('created_at','<=', $tenDaysAgo);
                    });
            });
        })->count();
    

        $stageCounts = [
            'Inquiry' => 0,
            'Counselling' => 0,
            'Documents Received' => 0,
            'Document pending' => 0,
            'University to be suggested' => 0,
            'Universities suggested' => 0,
            'Application ready to submit' => 0,
            'Application submitted' => 0,
            'Application Rejected' => 0,
            'Application resubmitted' => 0,
            'Admission Letter Pending' => 0,
            // 'Offer Issued Conditional / Unconditional' => 0,
            // 'Conditional Offer Received' => 0,
            // 'Un-conditional Offer Received' => 0,
            'Offer Issued' => 0,
            // 'Offer Issued Unconditional' => 0,
            'Offer Accepted' => 0,
            'Offer Rejected' => 0,
            'Student backed out / Dropped' => 0,
            'Initial Fee / Tuition Fee Deposit' => 0,
            'Visa Rejected' => 0,
            'Visa Accepted' => 0,
            'Visa In Process' => 0,
            'Work Visa' => 0,
            'Student Defered' => 0,
            'Admitted' => 0,
            'Test Preparation' => 0,
        ];

        foreach ($results as $result) {
            $userStages = $result->user->stages;
            if (isset($stageCounts[$userStages])) {
                $stageCounts[$userStages]++;
            }
        }
        
        
        // Access the counts for each stage
        $inquiryButtonCount = $stageCounts['Inquiry'] + $stageCounts['Counselling'] + $stageCounts['University to be suggested'] + $stageCounts['Universities suggested'];
        $applicationButtonCount = $stageCounts['Application ready to submit'] + $stageCounts['Application submitted'] + $stageCounts['Application Rejected'] + $stageCounts['Application resubmitted'] + $stageCounts['Admission Letter Pending'];
        $offerButtonCount = $stageCounts['Offer Issued'] + $stageCounts['Offer Accepted'] + $stageCounts['Offer Rejected'];
        $droppedButtonCount = $stageCounts['Student backed out / Dropped'];
        $feePaymentButtonCount = $stageCounts['Initial Fee / Tuition Fee Deposit'];
        $deferedButtonCount = $stageCounts['Student Defered'];
        
        $visaButtonCount = $stageCounts['Visa Rejected'] + $stageCounts['Visa Accepted'] + $stageCounts['Visa In Process'];
        $workVisaButtonCount = $stageCounts['Work Visa'];
        $testPreparationButtonCount = $stageCounts['Test Preparation'];
        
        
        $responseData = [
            'inquiryButtonCount' => $inquiryButtonCount,
            'applicationButtonCount' => $applicationButtonCount,
            'offerButtonCount' => $offerButtonCount,
            'feePaymentButtonCount' => $feePaymentButtonCount,
            'deferedButtonCount' => $deferedButtonCount,
            'droppedButtonCount' => $droppedButtonCount,
            'visaButtonCount' => $visaButtonCount,
            'workVisaButtonCount' => $workVisaButtonCount,
            'inquiryPendingCount' => $inquiryPendingCount,
            'applicationPendingCount' => $applicationPendingCount,
            'offerPendingCount' => $offerPendingCount,
            'visaPendingCount' => $visaPendingCount,
            'workVisaPendingCount' => $workVisaPendingCount,
            'feePaymentPendingCount' => $feePaymentPendingCount,
            'droppedPendingCount' => $droppedPendingCount,
            'deferedPendingCount' => $deferedPendingCount,
            'myNonfollowedcount' => $myNonfollowedcount,
            'inactiveFollowCount' => $inactiveFollowCount,
            'testPreparationButtonCount' => $testPreparationButtonCount,
            
        ];
        return response()->json($responseData);
    }


    public function leadManagementCounts(Request $request)
    {
       
        $inactiveFollowCount = 0;
        $myNonfollowedcount = 0;
        $inquiryButtonCount = 0;
        $applicationButtonCount = 0;
        $offerButtonCount = 0;
        $feePaymentButtonCount = 0;
        $deferedButtonCount = 0;
        $droppedButtonCount = 0;
        $visaButtonCount = 0;
        $workVisaButtonCount = 0;
        $testPreparationButtonCount = 0;
        $inquiryPendingCount = 0;
        $applicationPendingCount = 0;
        $offerPendingCount = 0;
        $visaPendingCount = 0;
        $workVisaPendingCount = 0;
        $deferedPendingCount = 0;
        $droppedPendingCount = 0;
        $myNonfollowedcount = 0;
        $feePaymentPendingCount = 0;
        $crossedTATCount = 0;
   
        
        abort_if(!userCan('leadmgt.view'), 403);
        $endDateF = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
        $roleNames = Auth()->user()->getRoleNames()->toArray();
        $leadManagerEnvRole = env('LEAD_MANAGER_ROLE', 'Lead Manager');

        $adminRoleNames = auth('admin')->user()->getRoleNames()->toArray();
        $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
   

        $baseQuery = Followup::
        where('close_status', 0)
        ->where('datetime', '<=', $endDateF)
        ->join('users', 'users.id', 'followups.user_id')
        ->join('candidates', 'candidates.user_id', 'users.id')
        ->whereNull('users.deleted_at')
        ->whereNull('candidates.deleted_at')
        ->groupBy('users.stages')
        ->select('users.stages',DB::raw('COUNT(*) as count'),'followups.user_id', 'followups.id', 'followups.assign_to');

            if (is_array($request->input('assign_to'))) {
            $assignTo = $request->input('assign_to');

            if (userCan('admin.assign_list') || userCan('admin.assign')) {
                if(is_array($request->input('assign_to'))){
                    if (in_array("all", $assignTo)) {
                        // No additional condition
                    } elseif (in_array("na", $assignTo)) {
                        $baseQuery->whereNull('followups.assign_to');
                    } elseif (!empty($assignTo)) {
                        $baseQuery->whereIn('followups.assign_to', $assignTo);
                    } else {
                        if (checkleadManagerEnvRole()) {
                            $baseQuery->where('followups.assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $baseQuery->where(function ($qr) {
                                $qr->where('followups.assign_to', Auth::user()->id)
                                    ->orWhere('followups.assigned_rm', Auth::user()->id);
                            });
                        }
                    }
                }else{
                    if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $q->whereNull('assign_to');
                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        $q->where('assign_to', $request->input('assign_to'));
                    }else{
                        
                        if (checkleadManagerEnvRole()) {
                            $q->where('assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $q->where(function ($qr) {
                                $qr->where('assign_to', Auth::user()->id)
                                    ->orWhere('assigned_rm', Auth::user()->id);
                            });
                        }
                    }
                }
            } else {
                $baseQuery->whereNotNull('followups.assign_to')->where('followups.assign_to', Auth::user()->id);
            }
        }

        $result = $baseQuery->get()->toArray();
        
        foreach ($result as $item) {
            if ($item['stages'] == "Inquiry" || $item['stages'] == "Counselling" || $item['stages'] == "University to be suggested" || $item['stages'] == "Universities suggested") {
                $inquiryPendingCount = $inquiryPendingCount + $item['count'];
            } elseif ($item['stages'] == "Application ready to submit" || $item['stages'] == "Application submitted" || $item['stages'] == "Application Rejected" || $item['stages'] == "Application resubmitted" || $item['stages'] == "Admission Letter Pending") {
                $applicationPendingCount = $applicationPendingCount + $item['count'];
            } elseif ($item['stages'] == "Offer Issued" || $item['stages'] == "Offer Accepted" || $item['stages'] == "Offer Rejected") {
                $offerPendingCount = $offerPendingCount + $item['count'];
            } elseif ($item['stages'] == "Student backed out / Dropped") {
                $droppedPendingCount = $droppedPendingCount + $item['count'];
            } elseif ($item['stages'] == "Initial Fee / Tuition Fee Deposit") {
                $feePaymentPendingCount = $feePaymentPendingCount + $item['count'];
            } elseif ($item['stages'] == "Student Defered") {
                $deferedPendingCount = $deferedPendingCount + $item['count'];
            } elseif ($item['stages'] == "Visa Rejected" || $item['stages'] == "Visa Accepted" || $item['stages'] == "Visa In Process") {
                $visaPendingCount = $visaPendingCount + $item['count'];
            } elseif ($item['stages'] == "Work Visa") {
                $workVisaPendingCount = $workVisaPendingCount + $item['count'];
            } 
        }
 
        $result = User::select('stages', DB::raw('COUNT(*) as count'))
        ->join('candidates','candidates.user_id','=','users.id')
        ->groupBy('stages')->where('users.deleted_at',"=",null);

        if (is_array($request->input('assign_to'))) {
            $assignTo = $request->input('assign_to');

            if (userCan('admin.assign_list') || userCan('admin.assign')) {
                if (in_array("all", $assignTo)) {
                    // No additional condition
                } elseif (in_array("na", $assignTo)) {
                    $result->whereNull('users.assign_to');
                } elseif (!empty($assignTo)) {
                    $result->whereIn('users.assign_to', $assignTo);
                } else {
                    if (checkleadManagerEnvRole()) {
                        $result->where('users.assign_to', Auth::user()->id);
                    }
                    if (checkRmEnvRole()) {
                        $result->where(function ($qr) {
                            $qr->where('users.assign_to', Auth::user()->id)
                                ->orWhere('users.assigned_rm', Auth::user()->id);
                        });
                    }
                }
            } else {
                $result->whereNotNull('users.assign_to')->where('users.assign_to', Auth::user()->id);
            }
        }

 
        $result = $result->get()->toArray();
        $total = 0;

       $crossedTATCount = User::join('candidates', 'candidates.user_id', 'users.id')->whereNotNull('TAT')->where('TAT', '<', 0)->count();
      
        foreach ($result as $item) {
            if ($item['stages'] == "Inquiry" || $item['stages'] == "Counselling" || $item['stages'] == "University to be suggested" || $item['stages'] == "Universities suggested") {
                $inquiryButtonCount = $inquiryButtonCount + $item['count'];
            } elseif ($item['stages'] == "Application ready to submit" || $item['stages'] == "Application submitted" || $item['stages'] == "Application Rejected" || $item['stages'] == "Application resubmitted" || $item['stages'] == "Admission Letter Pending") {
                $applicationButtonCount = $applicationButtonCount + $item['count'];
            } elseif ($item['stages'] == "Offer Issued" || $item['stages'] == "Offer Accepted" || $item['stages'] == "Offer Rejected") {
                $offerButtonCount = $offerButtonCount + $item['count'];
            } elseif ($item['stages'] == "Student backed out / Dropped") {
                $droppedButtonCount = $droppedButtonCount + $item['count'];
            } elseif ($item['stages'] == "Initial Fee / Tuition Fee Deposit") {
                $feePaymentButtonCount = $feePaymentButtonCount + $item['count'];
            } elseif ($item['stages'] == "Student Deferred") {
                $deferredButtonCount = $deferredButtonCount + $item['count'];
            } elseif ($item['stages'] == "Visa Rejected" || $item['stages'] == "Visa Accepted" || $item['stages'] == "Visa In Process") {
                $visaButtonCount = $visaButtonCount + $item['count'];
            } elseif ($item['stages'] == "Work Visa") {
                $workVisaButtonCount = $workVisaButtonCount + $item['count'];
            } elseif ($item['stages'] == "Test Preparation") {
                $testPreparationButtonCount = $testPreparationButtonCount + $item['count'];
            }
            elseif ($item['stages'] == "Student Defered") {
                $deferedButtonCount = $deferedButtonCount + $item['count'];
            }
            $total = $total+$item['count'];
        }
        $total_active_leads = $total-$droppedButtonCount;
        // $inquiryCount = $this->stageCounts('Inquiry') ?? 0;
        // $counsellingCount = $this->stageCounts('Counselling') ?? 0;
        // $universityToBeSuggestedCount = $this->stageCounts('University to be suggested') ?? 0;
        // $universitiesSuggestedCount = $this->stageCounts('Universities suggested') ?? 0;

        // $applicationReadyCount = $this->stageCounts('Application ready to submit') ?? 0;
        // $applicationSubmittedCount = $this->stageCounts('Application submitted') ?? 0;
        // $applicationRejectedCount = $this->stageCounts('Application Rejected') ?? 0;
        // $applicationResubmitCount = $this->stageCounts('Application resubmitted') ?? 0;
        // $admissionletterPanding = $this->stageCounts('Admission Letter Pending') ?? 0;
        
        // $offerCounts = $this->stageCounts('Offer Issued') ?? 0;
        // $offerAcceptedCount = $this->stageCounts('Offer Accepted') ?? 0;
        // $offerRejectedCount = $this->stageCounts('Offer Rejected') ?? 0;

        // $backedOutDroppedCount = $this->stageCounts('Student backed out / Dropped') ?? 0;

        // $feeDepositCount = $this->stageCounts('Initial Fee / Tuition Fee Deposit') ?? 0;
        
        // $deferCount = $this->stageCounts('Student Defered') ?? 0;

        // $visaCounts = $this->stageCounts('Visa Rejected') ?? 0;
        // $visaAcceptedCount = $this->stageCounts('Visa Accepted') ?? 0;
        // $visaInProgressCount = $this->stageCounts('Visa In Process') ?? 0;
        // $workVisaCount = $this->stageCounts('Work Visa') ?? 0;
        // $testPreparationCount = $this->stageCounts('Test Preparation') ?? 0;


        // $inquiryButtonCount = $inquiryCount+$counsellingCount+$universityToBeSuggestedCount+$universitiesSuggestedCount;
        // $applicationButtonCount = $applicationReadyCount+$applicationSubmittedCount+$applicationRejectedCount+$applicationResubmitCount+$admissionletterPanding;
        // $stageCounts = $applicationResubmitCount+$admissionletterPanding;
        // $offerButtonCount = $offerCounts+$offerAcceptedCount+$offerRejectedCount;
        // $droppedButtonCount = $backedOutDroppedCount;
        // $feePaymentButtonCount = $feeDepositCount;
        // $deferedButtonCount = $deferCount;
        // $visaButtonCount = $visaCounts+$visaAcceptedCount+$visaInProgressCount;
        // $workVisaButtonCount = $workVisaCount;
        // $testPreparationButtonCount = $testPreparationCount;
       

        // $users_count = User::select('users.id')
        // ->join('candidates', 'candidates.user_id', '=', 'users.id')
        // ->where(function ($query) use ($request) {
        //     $query->where('users.stages', '!=', 'Student backed out / Dropped')
        //         ->orWhereNull('users.stages');

        //     if (is_array($request->input('assign_to'))) {
        //         $assignTo = $request->input('assign_to');

        //         if (userCan('admin.assign_list') || userCan('admin.assign')) {
        //             if (in_array("all", $assignTo)) {
        //                 // No additional condition
        //             } elseif (in_array("na", $assignTo)) {
        //                 $query->whereNull('users.assign_to');
        //             } elseif (!empty($assignTo)) {
        //                 $query->whereIn('users.assign_to', $assignTo);
        //             } else {
        //                 if (checkleadManagerEnvRole()) {
        //                     $query->where('users.assign_to', Auth::user()->id);
        //                 }
        //                 if (checkRmEnvRole()) {
        //                     $query->where(function ($query) {
        //                         $query->where('users.assign_to', Auth::user()->id)
        //                             ->orWhere('users.assigned_rm', Auth::user()->id);
        //                     });
        //                 }
        //             }
        //         } else {
        //             $query->whereNotNull('users.assign_to')->where('users.assign_to', Auth::user()->id);
        //         }
        //     }
        // })
        // ->get()
        // ->toArray();

        // $ids = array_column($users_count, 'id');

        // $followupCount = Followup::select('user_id')
        // ->whereIn('user_id', $ids)
        // ->groupBy('user_id')
        // ->get()->count();
        
        // $myNonfollowedcount = count($users_count)-$followupCount; 


        // $inactiveFollowCount = 0;
        // $inactiveFollowCount = Candidate::with('followup')->whereHas('user', function ($q) use ($request) {
        //     if(userCan('admin.assign_list') || userCan('admin.assign')){
        //         if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){
        //             $q->where('stages', '!=', 'Student backed out / Dropped');
        //         }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
        //             $q->where('stages', '!=', 'Student backed out / Dropped');
        //             $q->whereNull('assign_to');
        //         }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
        //             $q->where('stages', '!=', 'Student backed out / Dropped');
        //             $q->where('assign_to', $request->input('assign_to'));
        //         }else{
        //             $q->where('stages', '!=', 'Student backed out / Dropped');
        //             if (checkleadManagerEnvRole()) {
        //                 $q->where('assign_to', Auth::user()->id);
        //             }
        //             if (checkRmEnvRole()) {
        //                 $q->where(function ($qr) {
        //                     $qr->where('assign_to', Auth::user()->id)
        //                       ->orWhere('assigned_rm', Auth::user()->id);
        //                 });
        //             }
        //         }
          
        //     }else{
        //         $q->where('stages', '!=', 'Student backed out / Dropped');
        //         $q->whereNotNull('assign_to')
        //         ->where('assign_to', Auth::user()->id); 
        //     }
            
        // })->whereHas('followup', function ($q) {
        //     $nowDate =  Carbon::now();
        //     $tenDaysAgo =  Carbon::now()->subDays(7);
        //     $q->where(function ($query) use ($nowDate, $tenDaysAgo) {
        //         $query->where('close_status', 0)
        //             ->Where(function ($query) use ($nowDate, $tenDaysAgo) {
        //                 $query->where('datetime', '<=', $nowDate)
        //                     ->where('created_at','<=', $tenDaysAgo);
        //             });
        //     });
        // })->count();
        

        $responseData = [
            'inquiryButtonCount' => $inquiryButtonCount,
            'applicationButtonCount' => $applicationButtonCount,
            'offerButtonCount' => $offerButtonCount,
            'feePaymentButtonCount' => $feePaymentButtonCount,
            'deferedButtonCount' => $deferedButtonCount,
            'droppedButtonCount' => $droppedButtonCount,
            'visaButtonCount' => $visaButtonCount,
            'workVisaButtonCount' => $workVisaButtonCount,
            'inquiryPendingCount' => $inquiryPendingCount,
            'applicationPendingCount' => $applicationPendingCount,
            'offerPendingCount' => $offerPendingCount,
            'visaPendingCount' => $visaPendingCount,
            'workVisaPendingCount' => $workVisaPendingCount,
            'feePaymentPendingCount' => $feePaymentPendingCount,
            'droppedPendingCount' => $droppedPendingCount,
            'deferedPendingCount' => $deferedPendingCount,
            'total_active_leads' => $total_active_leads,
            'testPreparationButtonCount' => $testPreparationButtonCount,
            'crossedTATCount' => $crossedTATCount,
        ];
        return response()->json($responseData);

    }

    public function ignore_notfollowps_leads(Request $request){
        $myNonfollowedcount = 0;
        $inactiveFollowCount = 0;


        $query = Candidate::with('user');
        
        $query->whereHas('user', function ($q) use ($request) {

            
            if(is_array($request->input('assign_to'))){

                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    $assignTo = $request->input('assign_to');
                    
                    if (in_array("all", $assignTo)) {
                        $q->where('assign_to',"!=",null);
                    } elseif (!empty($assignTo) && in_array("na", $assignTo)) {
                        $q->whereNull('assign_to');
                    } elseif (!empty($assignTo)) {
                        $q->whereIn('assign_to', $assignTo);
                    } else {
                        
                        if (checkleadManagerEnvRole()) {
                            $q->where('assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $q->where(function ($qr) {
                                $q->where('assign_to', Auth::user()->id)
                                    ->orWhere('assigned_rm', Auth::user()->id);
                            });
                        }
                    }
                }else{
                    $q->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            }
            
        });

        $results = $query->get();
        
        
        $myNonfollowedcount = $query->whereHas('user', function ($q){
            $q->where('stages', '!=', 'Student backed out / Dropped')
             ->orWhereNull('stages');
        })->whereDoesntHave('followup', function ($query) use ($request){
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $query->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $query->where('assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $query->where('assign_to', Auth::user()->id);
                    }
                   
                }
            }else{
                $query->where('admin_id', Auth::user()->id);
            }
           
        })->count();

      
        $inactiveFollowCount = 0;
        $inactiveFollowCount = Candidate::with('followup')->whereHas('user', function ($q) use ($request) {
            $q->where('stages', '!=', 'Student backed out / Dropped');
            
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                
                if(is_array($request->input('assign_to'))){
                    
                    if(in_array("all", $request->input('assign_to'))){
                       
                    }elseif(in_array("na", $request->input('assign_to'))){
                       
                        $q->whereNull('assign_to');
                    }else{
                        
                        $q->where('assign_to', $request->input('assign_to'));
                    }
                }else{
                    if(!empty($request->input('assign_to'))){
                        if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){
                            $q->where('stages', '!=', 'Student backed out / Dropped');
                        }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                            $q->where('stages', '!=', 'Student backed out / Dropped');
                            $q->whereNull('assign_to');
                        }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                            $q->where('stages', '!=', 'Student backed out / Dropped');
                            $q->where('assign_to', $request->input('assign_to'));
                        }else{
                            $q->where('stages', '!=', 'Student backed out / Dropped');
                            if (checkleadManagerEnvRole()) {
                                $q->where('assign_to', Auth::user()->id);
                            }
                            if (checkRmEnvRole()) {
                                $q->where(function ($qr) {
                                    $qr->where('assign_to', Auth::user()->id)
                                    ->orWhere('assigned_rm', Auth::user()->id);
                                });
                            }
                        }
                    }
                        
                }
          
            }else{
                
                $q->whereNotNull('assign_to')
                ->where('assign_to', Auth::user()->id); 
            }
          
        })->whereHas('followup', function ($q) {
            $nowDate =  Carbon::now();
            $tenDaysAgo =  Carbon::now()->subDays(7);
            $q->where(function ($query) use ($nowDate, $tenDaysAgo) {
                $query->where('close_status', 0)
                    ->Where(function ($query) use ($nowDate, $tenDaysAgo) {
                        $query->where('datetime', '<=', $nowDate)
                            ->where('created_at','<=', $tenDaysAgo);
                    });
            });
        })->count();

        $responseData = [
            'myNonfollowedcount' => $myNonfollowedcount,
            'inactiveFollowCount' => $inactiveFollowCount,
            
        ];
        
        return response()->json($responseData);
    }


    public function myfollowup(Request $request)
    {

        // dd($request->all());
        abort_if(!userCan('followup.view'), 403);

        $searchName = $request->input('keyword');
        $comment = $request->input('comment');
        $type = $request->input('type');
        $datetimeD = $request->input('datetimeD');
        $daterange = $request->input('daterange');

        $endDateF = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');

        $statusFollowup = $request->input('statusFollowup');
        $priority = $request->input('priority');
        $closeStatus = $request->input('closeStatus');
        // $admin_created_by = Auth::user()->id;
        $admin_created_by = null;
        if(!empty($request->input('created_by')) && $request->input('created_by') != "NULL" && $request->input('created_by') != "NUll"){
            $admin_created_by = $request->input('created_by');
        }
        $assign_to = NULL;

        if(!empty($request->input('assign_to'))){
            $assign_to = $request->input('assign_to');
        }

        $student_category = $request->input('student_category');
        $lead_type = $request->input('lead_type');
        $stages = $request->input('stages');
        $subStages = $request->input('subStages');
       

        // $latestFollowups = Followup::select('user_id', DB::raw('MAX(created_at) as latest_created_at'))
        // ->groupBy('user_id');

        $query = Followup::select('followups.*')
        ->with('user', 'candidate')
        ->whereHas('user', function ($query) {
            $query->whereNull('deleted_at');
        });

        $assign_from = $request->input('assign_from');

        $requestValues = $request->input('values');

    
        if($searchName || $assign_from || $student_category || $lead_type || $stages || $subStages || $comment || $type || $datetimeD || $daterange || $statusFollowup || $priority || $closeStatus || $admin_created_by || $assign_to || $requestValues){
            // if ($statusFollowup) {
            //     if ($statusFollowup == "Not Connected") {
            //         $query->where('follow_up_status', $statusFollowup);
            //     }
            //     if ($statusFollowup == "Connected") {
            //         $query->where('follow_up_status', $statusFollowup);
            //     }
            // }
            // if(userCan('admin.assign')){
            //     $assign_to = NULL;
            // }else{
            //     $assign_to = Auth::user()->id;
            // }
            // if(!empty($request->input('assign_to'))){
            //     $assign_to = $request->input('assign_to');
            // }

            
            // if ($comment) {
            //     $query->where('comment', 'like', '%' . $comment . '%');
            // }
            // if ($type) {
            //     $query->where('type', $type);
            // }

            // if ($closeStatus != null) {
            //     $query->where('close_status', $closeStatus);
            // }
            
            if ($daterange) {
                $dates = explode(' - ', $daterange);
                $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->format('Y-m-d H:i:s');
                $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->format('Y-m-d H:i:s');
        
                $query->whereBetween('datetime', [$startDate, $endDate]);

            }
        
            if ($datetimeD) {
                $dates = explode(' - ', $datetimeD);
                $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->format('Y-m-d H:i:s');
                $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->format('Y-m-d H:i:s');
        
                $query->whereBetween('datetime', [$startDate, $endDate]);
            }else{
                $endDate = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
                $startDate = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $query->where('datetime', '<=',$endDate)->where('close_status', 0);
                // $query->whereBetween('datetime', [$startDate, $endDate]);
            }

            if ($priority) {
                $query->Where('priority', 'like', '%' . $priority . '%');
            }

            if ($searchName) {
                $query->whereHas('user', function ($userQuery) use ($searchName) {
                    $userQuery->where('name', 'LIKE', "%$searchName%")
                    ->orWhere('email', 'LIKE', "%$searchName%")
                    ->orWhere('id', $searchName)
                    ->orWhere('phone', 'LIKE', "%$searchName%");
                });
            }

            if (!empty($student_category)) {
                $query->whereHas('candidate', function ($q) use ($student_category) {
                    $q->where('student_category', $student_category);
                });
               
            }
            if (!empty($lead_type)) {
                $query->whereHas('user', function ($q) use ($lead_type) {
                    $q->where('lead_type', $lead_type);
                });
               
            }
            if (!empty($stages)) {
                $query->whereHas('user', function ($q) use ($stages) {
                    $q->where('stages', $stages);
                });
               
            }


             if($requestValues || $subStages){
                    // Define an empty array to store the sub-stages
                    $subStagesArray = [];

                    if ($requestValues == 'inquiry') {
                        $subStagesArray = ['Inquiry', 'Counselling', 'University to be suggested', 'Universities suggested'];
                    } elseif ($requestValues == 'application') {
                        $subStagesArray = ['Application ready to submit', 'Application submitted', 'Application Rejected', 'Application resubmitted', 'Admission Letter Pending'];
                    } elseif ($requestValues == 'offer') {
                        $subStagesArray = ['Offer Issued','Offer Accepted','Offer Rejected'];
                    } elseif ($requestValues == 'dropped') {
                        $subStagesArray = ['Student backed out / Dropped'];
                    } elseif ($requestValues == 'feePayment') {
                        $subStagesArray = ['Initial Fee / Tuition Fee Deposit'];
                    } elseif ($requestValues == 'visa') {
                        $subStagesArray = ['Visa Rejected', 'Visa Accepted', 'Visa In Process', 'Work Visa'];
                    } elseif ($requestValues == 'defered') {
                        $subStagesArray = ['Student Defered'];
                    }

                    $getSubStagesArray = [];

                    if (!empty($subStagesArray)) {
                        $stagesArray = get_stages_array();
                        foreach ($subStagesArray as $subStage) {
                            foreach ($stagesArray as $stage) {
                                if ($stage['name'] == $subStage) {
                                    $getSubStagesArray[] = $stage['name'];
                                }
                            }
                        }
                    } else {
                        $index = 0;
                        $getSubStagesArray = array_column(get_stages_array(), 'name');
                    }

                    if (!empty($subStages) && in_array($subStages, $getSubStagesArray)) {
                        // dd("yes");
                        $query->whereHas('user', function ($q) use ($subStages) {
                            $q->where('stages', $subStages);
                        });
                        
                    }
             }
         

            if (!empty($admin_created_by) && $admin_created_by != "NULL") {
            
                $query->where('admin_id', $admin_created_by);
                
            }

            if (!empty(@$assign_from)) {
                if(@$assign_from && $assign_from == "All"){
                    $query->whereHas('user', function ($q){
                        $q->whereNotNull('assign_from');
                    });
                   
                }else{
                    $query->whereHas('user', function ($q) use ($assign_from){
                        $q->where('assign_from', $assign_from);
                    });
                }
    
            }
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $query->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $query->where('assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $query->where('assign_to', Auth::user()->id);
                    }
                    
                }
            
            }else{
                $query->where('assign_to', Auth::user()->id);
            }
           
        }else{
            $endDate = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            $startDate = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
            $query->where('datetime', '<=',$endDate)->where('close_status', 0);
            
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $query->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $query->where('assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $query->where('assign_to', Auth::user()->id);
                    }
                }
            
            }else{
                $query->where('assign_to', Auth::user()->id);
            }
           
            
        }


        $query->where('close_status', 0);
        $queres =  $query->get();

        if(!empty($request->input('values'))){
            $query->whereHas('user', function ($q) use ($request) {
            if($request->input('values') == "inquiry"){
               $q->whereIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested']);
            }
            if($request->input('values') == "application"){
               $q->whereIn('stages', ['Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending']);
            }
            if($request->input('values') == "offer"){
               $q->whereIn('stages', ['Offer Issued','Offer Accepted','Offer Rejected']);
            }
            if($request->input('values') == "visa"){
                $q->whereIn('stages', ['Visa Rejected','Visa Accepted','Visa In Process','Work Visa']);
            }
            if($request->input('values') == "workVisa"){
                $q->whereIn('stages', ['Work Visa']);
            }
            if($request->input('values') == "feePayment"){
               $q->whereIn('stages', ['Initial Fee / Tuition Fee Deposit']);
            }
            if($request->input('values') == "defered"){
                $q->whereIn('stages', ['Student Defered']);
            }
            if($request->input('values') == "dropped"){
               $q->whereIn('stages', ['Student backed out / Dropped']);
            }
            if($request->input('values') == "hot"){
                $q->where('lead_type', "Hot");
            }
            if($request->input('values') == "cold"){
                $q->where('lead_type', "Cold");
            }
            if($request->input('values') == "nr"){
                $q->where('lead_type', "Warm");
            }

            });
            
        }
        
        
        $followups = $query->oldest('datetime')->paginate(15)->withQueryString()->appends($request->query());
        $count = $followups->total();


        $leadManagerEnvRole = env('LEAD_MANAGER_ROLE', 'Lead Manager');
        $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
        $admin_list_assign = Admin::where('id', '!=', 1)
        ->whereHas('roles', function ($query) use ($roleEnvNames) {
            $query->whereIn('name', $roleEnvNames);
        })
        ->get()
        ->toArray();

        $admin_list = Admin::where('id', '!=', 1)
            ->whereHas('roles', function ($query) use ($roleEnvNames) {
                $query->whereIn('name', $roleEnvNames);
            })
            ->get();

            $roleNames = Auth()->user()->getRoleNames()->toArray();
      
            $followupDataButton = Followup::where('datetime', '<=', $endDateF)
            ->where('close_status', 0)
            ->where(function ($query) use ($request, $roleNames, $leadManagerEnvRole) {
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        $query->whereNull('assign_to');
                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        $query->where('assign_to', $request->input('assign_to'));
                    }else{
                        if (checkleadManagerEnvRole()) {
                            $query->where('assign_to', Auth::user()->id);
                        }
                    
                    }
                
                }
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        $query->whereNull('assign_to');
                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        $query->where('assign_to', $request->input('assign_to'));
                    }else{
                        if (checkleadManagerEnvRole()) {
                            $query->where('assign_to', Auth::user()->id);
                        }
                     
                    }
                }else{
                    $query->where('assign_to', Auth::user()->id);
                }
            })->whereHas('user', function ($q) use ($request, $roleNames, $leadManagerEnvRole) {
                    // if (!empty($request->input('assign_to')) && $request->input('assign_to') != "na") {
                    //     $q->where('assign_to', $request->input('assign_to'));
                    // } else {
                    //     if (Auth::user()->id && in_array($leadManagerEnvRole, $roleNames)) {
                    //         $q->where('assign_to', Auth::user()->id);
                    //     }
                    // }
            })->get();
         
            $inquiryPendingCount = 0;
            $applicationPendingCount = 0;
            $offerPendingCount = 0;
            $visaPendingCount = 0;
            $feePaymentPendingCount = 0;
            $deferedPendingCount = 0;
            $droppedPendingCount = 0;
   
            $inquiryPendingCount = $followupDataButton->whereIn('user.stages', ['Inquiry','Counselling','University to be suggested','Universities suggested'])->count();
   
            $applicationPendingCount = $followupDataButton->whereIn('user.stages', ['Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending'])->count();
   
            $offerPendingCount = $followupDataButton->whereIn('user.stages', ['Offer Issued','Offer Accepted','Offer Rejected'])->count();
   
            $droppedPendingCount = $followupDataButton->whereIn('user.stages', ['Student backed out / Dropped'])->count();
   
            $feePaymentPendingCount = $followupDataButton->whereIn('user.stages', ['Initial Fee / Tuition Fee Deposit'])->count();

            $deferedPendingCount = $followupDataButton->whereIn('user.stages', ['Student Defered'])->count();
   
            $visaPendingCount = $followupDataButton->whereIn('user.stages', ['Visa Rejected','Visa Accepted','Visa In Process','Work Visa'])->count();

            
            $pendingFollowUpCount = Followup::where('datetime', '<=', $endDateF)
            ->where('close_status', 0)
            ->where(function ($query) use ($request, $roleNames, $leadManagerEnvRole) {
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        $query->whereNull('assign_to');
                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        $query->where('assign_to', $request->input('assign_to'));
                    }else{
                        if (checkleadManagerEnvRole()) {
                            $query->where('assign_to', Auth::user()->id);
                        }
                    }
                
                }else{
                    $query->where('assign_to', Auth::user()->id);
                }
            })->whereHas('user', function ($q) use ($request, $roleNames, $leadManagerEnvRole) {
                // if (!empty($request->input('assign_to')) && $request->input('assign_to') != "na") {
                //     $q->where('assign_to', $request->input('assign_to'));
                // } else {
                //     if (Auth::user()->id && in_array($leadManagerEnvRole, $roleNames)) {
                //         $q->where('assign_to', Auth::user()->id);
                //     }
                // }
            })->count();


            $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
            $admin_list = Admin::where('id', '!=', 1)
                ->whereHas('roles', function ($query) use ($roleEnvNames) {
                    $query->whereIn('name', $roleEnvNames);
                })
                ->get()
                ->toArray();
    
   
        return view('admin.lead-management.myfollowup', compact('followups', 'count','pendingFollowUpCount','admin_list',
        'inquiryPendingCount','applicationPendingCount','offerPendingCount','visaPendingCount','feePaymentPendingCount','droppedPendingCount','droppedPendingCount'
        ));
    }


    public function leaddashboard()
    {
        abort_if(!userCan('leadmgt.view'), 403);

        $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
        $agents = Admin::where('id', '!=', 1)
            ->whereHas('roles', function ($query) use ($roleEnvNames) {
                $query->whereIn('name', $roleEnvNames);
            })
            ->get(['id', 'name']);

        return view('admin.lead-management.leaddashboard', compact('agents'));
    }

    
    // public function getMapData(Request $request)
    // {
    //     abort_if(!userCan('leadmgt.map'), 403);
    //     $selectMonth = $request->input('monthSelect');
    //     $yearSelect = $request->input('yearSelect');
    //     $startDate = Carbon::create($yearSelect, $selectMonth, 1)->startOfDay();
    //     $endDate = $startDate->copy()->endOfMonth()->endOfDay();
    //     $countryMapData = User::whereBetween('created_at', [$startDate, $endDate])->orderBy("created_at")->groupBy('email')->get(['country']);
    //     $dashboardData = [];
    //     foreach ($countryMapData as $wd) {
    //         $index = $wd->country;
    //         if (isset($dashboardData[$index])) {
    //             $dashboardData[$index]['count']++;
    //         } else {
    //             $coords = $this->getCoordsForCountry($index);
    //             if ($coords) {
    //                 $dashboardData[$index] = [
    //                     'name' => $index,
    //                     'coords' => [$coords['latitude'], $coords['longitude']],
    //                     'count' => 1,
    //                 ];
    //             } 
    //             // else {
    //             //     $dashboardData[$index] = [
    //             //         'name' => $index,
    //             //         'coords' => [29, 77],
    //             //         'count' => 1,
    //             //     ];
    //             // }
    //         }
    //     }
    //     $leads = array_values($dashboardData);

    //     return response()->json($leads);
    // }


    public function getMapData(Request $request)
    {
        abort_if(!userCan('leadmgt.map'), 403);
        $selectMonth = $request->input('monthSelect');
        $yearSelect = $request->input('yearSelect');
        $startDate = Carbon::create($yearSelect, $selectMonth, 1)->startOfDay();
        $endDate = $startDate->copy()->endOfMonth()->endOfDay();
       
        $countryMapData = Candidate::where('country_id',103)
            ->whereNotNull('state_id')
            ->whereBetween('created_at', [$startDate, $endDate])
            ->orderBy('created_at')
            ->get(['state_id']);
        $dashboardData = [];
        foreach ($countryMapData as $wd) {
            $index = $wd->state_id;
            if (isset($dashboardData[$index])) {
                $dashboardData[$index]['count']++;
            } else {
                $coords = $this->getCoordsForState($index);
                if ($coords) {
                    $dashboardData[$index] = [
                        'code' => $coords['code'],
                        'name' => $coords['name'],
                        'count' => 1,
                    ];
                } 
              
            }
        }
        $leads = array_values($dashboardData);
        // dd( $leads );
        return response()->json($leads);
    }

    public function getCityData(Request $request)
    {
        abort_if(!userCan('leadmgt.city'), 403);

        $requestData = $request->all();
        $result = $this->getStartEndDate($requestData);
        $startDate = $result['start_date'];
        $endDate = $result['end_date'];
        $cityData = Candidate::whereBetween('created_at', [$startDate, $endDate])->with(['passportCity' => function ($q)  {
            $q->select('id','name');
         
        }])->orderBy("created_at")->groupBy('user_id')->get();
        $dashboardData = [];
        // foreach ($cityData as $wd) {
        //     $index = date('d F', strtotime($wd->created_at));

        //     if (isset($dashboardData[$index][$wd->city_id])) {
        //         $dashboardData[$index][$wd->city_id]++;
               
        //     } else {
        //         $dashboardData[$index][$wd->city_id] = 1;
        //     }
        // }

        foreach ($cityData as $wd) {
            $index = date('d F', strtotime($wd->created_at));
        
            if (isset($wd->passportCity->name)) {
                $cityName = $wd->passportCity->name;
        
                if (isset($dashboardData[$index][$cityName])) {
                    $dashboardData[$index][$cityName]++;
                } else {
                    $dashboardData[$index][$cityName] = 1;
                }
            } else {
                if (isset($dashboardData[$index][$wd->city_id])) {
                    $dashboardData[$index][$wd->city_id]++;
                } else {
                    $dashboardData[$index][$wd->city_id] = 1;
                }
            }
        }
       
        return response()->json($dashboardData);
    }

    public function getStateData(Request $request)
    {
        abort_if(!userCan('leadmgt.state'), 403);

        $requestData = $request->all();
        $result = $this->getStartEndDate($requestData);
        $startDate = $result['start_date'];
        $endDate = $result['end_date'];
        // $cityData = Candidate::whereBetween('created_at', [$startDate, $endDate])->orderBy("created_at")->groupBy('user_id')->get();
        $cityData = Candidate::whereBetween('created_at', [$startDate, $endDate])->with(['passportState' => function ($q)  {
            $q->select('id','name');
         
        }])->orderBy("created_at")->groupBy('user_id')->get();
        $dashboardData = [];
        // foreach ($cityData as $wd) {
        //     $index = date('d F', strtotime($wd->created_at));

        //     if (isset($dashboardData[$index][$wd->state_id])) {
        //         $dashboardData[$index][$wd->state_id]++;
        //     } else {
        //         $dashboardData[$index][$wd->state_id] = 1;
        //     }
        // }
        foreach ($cityData as $wd) {
            $index = date('d F', strtotime($wd->created_at));
        
            if (isset($wd->passportState->name)) {
                $stateName = $wd->passportState->name;
        
                if (isset($dashboardData[$index][$stateName])) {
                    $dashboardData[$index][$stateName]++;
                } else {
                    $dashboardData[$index][$stateName] = 1;
                }
            } else {
                if (isset($dashboardData[$index][$wd->state_id])) {
                    $dashboardData[$index][$wd->state_id]++;
                } else {
                    $dashboardData[$index][$wd->state_id] = 1;
                }
            }
        }
        return response()->json($dashboardData);
    }
    public function getCountryData(Request $request)
    {
        abort_if(!userCan('leadmgt.country'), 403);

        $requestData = $request->all();
        $result = $this->getStartEndDate($requestData);
        $startDate = $result['start_date'];
        $endDate = $result['end_date'];
        $cityData = User::whereBetween('created_at', [$startDate, $endDate])->orderBy("created_at")->groupBy('email')->get();
        $dashboardData = [];
        foreach ($cityData as $wd) {
            $index = date('d F', strtotime($wd->created_at));

            if (isset($dashboardData[$index][$wd->country])) {
                $dashboardData[$index][$wd->country]++;
            } else {
                $dashboardData[$index][$wd->country] = 1;
            }
        }
        return response()->json($dashboardData);
    }

    public function getAgentDataByAdmin(Request $request)
    {
        abort_if(!userCan('leadmgt.agent'), 403);

        $requestData = $request->all();
        $result = $this->getStartEndDate($requestData);
        $startDate = $result['start_date'];
        $endDate = $result['end_date'];
        $agent_ID = $request->agent_ID;
        $timeLineData = UserTimeline::where('auth_id', $agent_ID)->where("stages", "!=", "Assign To")->whereBetween('created_at', [$startDate, $endDate])->orderBy("created_at")->get();
        $getStagesArray = get_stages_array();
        $stages = [];
        foreach ($getStagesArray as $stage) {
            $stages[] = $stage['name'];
        }
        $dashboardData = [];
        foreach ($timeLineData as $wd) {
            $index = date('d F', strtotime($wd->created_at));
            if (!isset($dashboardData[$index])) {
                foreach ($stages as $stage) {
                    if (!isset($dashboardData[$index][$stage])) {
                        $dashboardData[$index][$stage] = 0;
                    }
                }
            }
            if (isset($dashboardData[$index][$wd->stages])) {
                $dashboardData[$index][$wd->stages]++;
            } else {
                $dashboardData[$index][$wd->stages] = 1;
            }
        }
        return response()->json($dashboardData);
    }

    public function getStartEndDate($requestData)
    {
        $weekNumber = $requestData['weekSelect'];
        $selectMonth = $requestData['monthSelect'];
        $yearSelect = $requestData['yearSelect'];
        if ($weekNumber == 1) {
            $startDate = date("Y-m-d H:i:s", strtotime("1-" . $selectMonth . "-" . $yearSelect));
            $endDate = date("Y-m-d 23:59:59", strtotime("7-" . $selectMonth . "-" . $yearSelect));
        } elseif ($weekNumber == 2) {
            $startDate = date("Y-m-d H:i:s", strtotime("8-" . $selectMonth . "-" . $yearSelect));
            $endDate = date("Y-m-d 23:59:59", strtotime("14-" . $selectMonth . "-" . $yearSelect));
        } elseif ($weekNumber == 3) {
            $startDate = date("Y-m-d H:i:s", strtotime("15-" . $selectMonth . "-" . $yearSelect));
            $endDate = date("Y-m-d 23:59:59", strtotime("21-" . $selectMonth . "-" . $yearSelect));
        } elseif ($weekNumber == 4) {
            $startDate = date("Y-m-d H:i:s", strtotime("22-" . $selectMonth . "-" . $yearSelect));
            $endDate = date("Y-m-d 23:59:59", strtotime("31-" . $selectMonth . "-" . $yearSelect));
        } else {
            $startDate = Carbon::now()->startOfWeek();
            $endDate = Carbon::now()->endOfWeek();
        }
        return [
            'start_date' => $startDate,
            'end_date' => $endDate
        ];
    }

    public function getCoordsForCountry($countryName)
    {
        $country = Countries::where('name', $countryName)->first();
        if ($country) {
            return ['latitude' => $country->latitude, 'longitude' => $country->longitude];
        } else {
            return null;
        }
    }

    public function getCoordsForState($id)
    {
        // $state = State::select('id','name','country_id','iso2','country_code')->where('country_id',103)->where('id', $id)->first();
        $state = State::find($id, ['id', 'name', 'country_id', 'iso2', 'country_code']);
        if ($state && $state->country_code && $state->iso2) {
            return [
                'name' => $state->name,
                'code' => $state->country_code . '-' . $state->iso2,
                'id' => $state->id
            ];
        } else {
            return null;
        }
    }

    public function myfollowupcalender(Request $request)
    {
        return view('admin.lead-management.myfollowupcalender');
    }
    public function followupCalendarEvent(Request $request)
    {
        // dd( $request->all());
        // if ($request->ajax()) {
       
            $searchName = $request->input('keyword');
            $type = $request->input('type');
            $student_category = $request->input('student_category');
            $lead_type = $request->input('lead_type');
            $stages = $request->input('stages');
            $subStages = $request->input('subStages');
            $values = $request->input('bValues');

            $query = Followup::query();

            $query->select([
                'followups.id',
                'users.name AS title',
                'users.id AS userID',
                'candidates.id AS candidatesID',
                'users.email AS userEmail',
                'users.phone AS userPhone',
                'users.country_code AS userCountryCode',
                'users.country AS userCountry',
                'followups.comment',
                'followups.type',
                'followups.priority',
                'followups.followup_up',
                'followups.datetime',
                'followups.created_at',
                'followups.admin_id',
                'followups.assign_to',
                'followups.datetime AS start',
                'followups.datetime AS end',
                // 'followups.datetime AS allDay'
            ]);
            $query->where('followups.close_status', 0);
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $query->whereNull('followups.assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $query->where('followups.assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $query->where('followups.assign_to', Auth::user()->id);
                    }
                }
            
            }else{
                $query->where('followups.assign_to', auth()->user()->id);
            }

            $query->leftJoin('users', function ($join) {
                $join->on('users.id', '=', 'followups.user_id');
            });
            $query->leftJoin('candidates', function ($join) {
                $join->on('candidates.user_id', '=', 'followups.user_id');
            });

            if (request('start')) {
                $startDate = Carbon::parse(request('start'));
                $query->whereDate('followups.datetime', '>=', $startDate->format('Y-m-d'));
            }

            if (request('end')) {
                $toDate = Carbon::parse(request('end'));
                $query->whereDate('followups.datetime', '<=', $toDate->format('Y-m-d'));
            }

            if (!empty($searchName)) {
                $query->whereHas('user', function ($userQuery) use ($searchName) {
                    $userQuery->where('name', 'LIKE', "%$searchName%")
                    ->orWhere('email', 'LIKE', "%$searchName%")
                    ->orWhere('id', $searchName)
                    ->orWhere('phone', 'LIKE', "%$searchName%");
                });
            }


            if (!empty($student_category)) {
                $query->whereHas('candidate', function ($q) use ($student_category) {
                    $q->where('student_category', $student_category);
                });
               
            }
            if (!empty($lead_type)) {
                $query->whereHas('user', function ($q) use ($lead_type) {
                    $q->where('lead_type', $lead_type);
                });
               
            }
            if (!empty($stages)) {
                $query->whereHas('user', function ($q) use ($stages) {
                    $q->where('stages', $stages);
                });
               
            }

            if (!empty($subStages)) {
                $query->whereHas('user', function ($q) use ($subStages) {
                    $q->where('stages', $subStages);
                });
               
            }

            if(!empty($values)){
                $query->whereHas('user', function ($q) use ($values) {
                if($values == "inquiry"){
                   $q->whereIn('stages', ['Inquiry','Counselling','University to be suggested','Universities suggested']);
                }
                if($values == "application"){
                   $q->whereIn('stages', ['Application ready to submit', 'Application submitted','Application Rejected','Application resubmitted','Admission Letter Pending']);
                }
                if($values == "offer"){
                   $q->whereIn('stages', ['Offer Issued','Offer Accepted','Offer Rejected']);
                }
                if($values == "visa"){
                    $q->whereIn('stages', ['Visa Rejected','Visa Accepted','Visa In Process','Work Visa']);
                }
                if($values == "feePayment"){
                   $q->whereIn('stages', ['Initial Fee / Tuition Fee Deposit']);
                }
                if($values == "defered"){
                    $q->whereIn('stages', ['Student Defered']);
                }
                if($values == "dropped"){
                   $q->whereIn('stages', ['Student backed out / Dropped']);
                }
                if($values == "hot"){
                    $q->where('lead_type', "Hot");
                }
                if($values == "cold"){
                    $q->where('lead_type', "Cold");
                }
                if($values == "nr"){
                    $q->where('lead_type', "Warm");
                }
    
                });
                
            }

            // Subquery to calculate eventCount
            // $query->addSelect([
            //     DB::raw('(SELECT COUNT(*) FROM followups WHERE assign_to = ' . auth()->user()->id . ' AND DATE(datetime) = DATE(followups.datetime) AND followups.close_status = 0) as eventCount')
            // ]);

            $data = $query->get()->toArray();
        

            $formattedData = [];

            foreach ($data as $item) {
               
               
                $formattedDatetime = date('Y-m-d H:i:s', strtotime($item['datetime']));
            
                $formattedItem = [
                    'id' => $item['id'],
                    'title' => $item['title'],
                    'userID' => $item['userID'],
                    'candidatesID' => $item['candidatesID'],
                    'userEmail' => $item['userEmail'],
                    'userPhone' => $item['userPhone'],
                    'userCountryCode' => $item['userCountryCode'],
                    'userCountry' => $item['userCountry'],
                    'comment' => $item['comment'],
                    'type' => $item['type'],
                    'priority' => $item['priority'],
                    'followup_up' => $item['followup_up'],
                    'datetime' => dateMonthTimeFormat($item['datetime']),
                    'created_at' => dateMonthTimeFormat($item['created_at']),
                    'admin_id' => adminIdToNameGet($item['admin_id']),
                    'assign_to' => adminIdToNameGet($item['assign_to']),
                    'start' => $item['start'],
                    'end' => $item['end'],
                ];
            
                // Push the formatted item into the result array
                $formattedData[] = $formattedItem;
            }

        
            return response()->json($formattedData);
        // }
    }


    public function leadManagementDashboard()
    {
        abort_if(!userCan('leadmgt.view'), 403);

        $roleEnvNames = explode(',', env('AVAILABLE_LEAD_ROLES', 'Lead Manager,Operation Manager'));
        $admin_list = Admin::where('id', '!=', 1)
        ->whereHas('roles', function ($query) use ($roleEnvNames) {
            $query->whereIn('name', $roleEnvNames);
        })
        ->get()
        ->toArray();

            $startDate = Carbon::now()->startOfMonth();

            // Get the last day of the current month
            $endDate = Carbon::now()->endOfMonth();

            // Format the dates as "MM/DD/YYYY" and create the date range string
            $dateRange = $startDate->format('m/d/Y') . ' - ' . $endDate->format('m/d/Y');
            // dd( $agents);
        return view('admin.lead-management.leadManagementDashboard', compact('admin_list','dateRange'));
    }

    public function getStageWiseReportData(Request $request)
    {
        abort_if(!userCan('leadmgt.view'), 403);
    
         $inquiryButtonCount = 0;
         $applicationButtonCount = 0;
         $offerButtonCount = 0;
         $feePaymentButtonCount = 0;
         $deferedButtonCount = 0;
         $droppedButtonCount = 0;
         $visaButtonCount = 0;

         $daterange = $request->input('daterange');

         $encodedDaterange = urlencode($daterange);
        $url = env('APP_URL');


        if(userCan('admin.assign_list') || userCan('admin.assign')){
            if(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                $assign_to = $request->input('assign_to');
            }else{
                $assign_to = NULL;
            }
        
        }else{
            $assign_to = Auth::user()->id;
        }

        $query = Candidate::with('user');

            
        $query->whereHas('user', function ($q) use ($request) {
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $q->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $q->where('assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $q->where('assign_to', Auth::user()->id);
                    }
                    if (checkRmEnvRole()) {
                        $q->where(function ($qr) {
                            $qr->where('assign_to', Auth::user()->id)
                              ->orWhere('assigned_rm', Auth::user()->id);
                        });
                    }
                }
            }else{
                $q->whereNotNull('assign_to')
                ->where('assign_to', Auth::user()->id); 
            }
                            
        });

        // dd($daterange);

        if ($daterange) {
            // $dates = explode(' - ', $daterange);
            // $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->format('Y-m-d H:i:s');
            // $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->format('Y-m-d H:i:s');
            // $query->whereBetween('created_at', [$startDate, $endDate]);

            $query->whereHas('user', function ($q) use ($request) {
            $dates = explode(' - ', $request->input('daterange'));
            $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay();
            $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay();
            $q->whereBetween('assign_date', [$startDate, $endDate]);
            });
    
            
        }else{
            // $startDate = Carbon::now()->startOfMonth();
            // $endDate = Carbon::now()->endOfMonth();
            // $query->whereBetween('created_at', [$startDate, $endDate]);
            $query->whereHas('user', function ($q) use ($request) {
                $startDate = Carbon::now()->startOfMonth();
                $endDate = Carbon::now()->endOfMonth();
            $q->whereBetween('assign_date', [$startDate, $endDate]);
            });
        }

        $results = $query->get();

        $stageCounts = [
            'Inquiry' => 0,
            'Counselling' => 0,
            'Documents Received' => 0,
            'Document pending' => 0,
            'University to be suggested' => 0,
            'Universities suggested' => 0,
            'Application ready to submit' => 0,
            'Application submitted' => 0,
            'Application Rejected' => 0,
            'Application resubmitted' => 0,
            'Admission Letter Pending' => 0,
            // 'Offer Issued Conditional / Unconditional' => 0,
            // 'Conditional Offer Received' => 0,
            // 'Un-conditional Offer Received' => 0,
            'Offer Issued' => 0,
            // 'Offer Issued Unconditional' => 0,
            'Offer Accepted' => 0,
            'Offer Rejected' => 0,
            'Student backed out / Dropped' => 0,
            'Initial Fee / Tuition Fee Deposit' => 0,
            'Visa Rejected' => 0,
            'Visa Accepted' => 0,
            'Visa In Process' => 0,
            'Work Visa' => 0,
            'Student Defered' => 0,
            'Admitted' => 0,
            'Test Preparation' => 0,
        ];

        foreach ($results as $result) {
            $userStages = $result->user->stages;
            if (isset($stageCounts[$userStages])) {
                $stageCounts[$userStages]++;
            }
        }

        // Access the counts for each stage
        $inquiryButtonCount = $stageCounts['Inquiry'] + $stageCounts['Counselling'] + $stageCounts['University to be suggested'] + $stageCounts['Universities suggested'];

        $applicationButtonCount = $stageCounts['Application ready to submit'] + $stageCounts['Application submitted'] + $stageCounts['Application Rejected'] + $stageCounts['Application resubmitted'] + $stageCounts['Admission Letter Pending'];
        $offerButtonCount = $stageCounts['Offer Issued'] + $stageCounts['Offer Accepted'] + $stageCounts['Offer Rejected'];
        $droppedButtonCount = $stageCounts['Student backed out / Dropped'];
        $feePaymentButtonCount = $stageCounts['Initial Fee / Tuition Fee Deposit'];
        $deferedButtonCount = $stageCounts['Student Defered'];
        $visaButtonCount = $stageCounts['Visa Rejected'] + $stageCounts['Visa Accepted'] + $stageCounts['Visa In Process'];
        
        $testPreparationButtonCount = $stageCounts['Test Preparation'];
        // $visaButtonCount = $stageCounts['Visa Rejected'] + $stageCounts['Visa Accepted'] + $stageCounts['Visa In Process'] + $stageCounts['Work Visa'];

        // $inquiryButtonCount = [
        //     'inquiryButtonCount' => $inquiryButtonCount,

        //     'Inquiry' => $stageCounts['Inquiry'],
        //     'InquiryPercentage' => calculatePercentage($stageCounts['Inquiry'], $inquiryButtonCount),

        //     'Counselling' => $stageCounts['Counselling'],
        //     'CounsellingPercentage' => calculatePercentage($stageCounts['Counselling'], $inquiryButtonCount),

        //     'DocumentsReceived' => $stageCounts['Documents Received'],
        //     'DocumentsReceivedPercentage' => calculatePercentage($stageCounts['Documents Received'], $inquiryButtonCount),

        //     'DocumentPendingCounts' => $stageCounts['Document pending'],
        //     'DocumentPendingPercentage' => calculatePercentage($stageCounts['Document pending'], $inquiryButtonCount),

        //     'UniversityToBeSuggestedCounts' => $stageCounts['University to be suggested'],
        //     'UniversityToBeSuggestedPercentage' => calculatePercentage($stageCounts['University to be suggested'], $inquiryButtonCount),

        //     'UniversitiesSuggestedCounts' => $stageCounts['Universities suggested'],
        //     'UniversitiesSuggestedPercentage' => calculatePercentage($stageCounts['Universities suggested'], $inquiryButtonCount),

        // ];

        $data = [];
        $data[0] = [
            "type" => "Inquiry Stage",
            "count" => $inquiryButtonCount,
            'link' => $inquiryButtonCount != 0
            ? $url . "lead-management?values=inquiry&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            "datas" => [
                [
                    "name" => "Inquiry",
                    "count" => $stageCounts['Inquiry'],
                    "percentage" => calculatePercentage($stageCounts['Inquiry'], $inquiryButtonCount),
                    'link' =>  $stageCounts['Inquiry'] != 0
                    ? $url . "lead-management?stages=Inquiry&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                   
                ],
                [
                    "name" => "Counselling",
                    "count" => $stageCounts['Counselling'],
                    "percentage" => calculatePercentage($stageCounts['Counselling'], $inquiryButtonCount),
                    'link' =>  $stageCounts['Counselling'] != 0
                    ? $url . "lead-management?stages=Counselling&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    "name" => "University to be suggested",
                    "count" => $stageCounts['University to be suggested'],
                    "percentage" => calculatePercentage($stageCounts['University to be suggested'], $inquiryButtonCount),
                    'link' =>  $stageCounts['University to be suggested'] != 0
                    ? $url . "lead-management?stages=University+to+be+suggested&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    "name" => "Universities suggested",
                    "count" => $stageCounts['Universities suggested'],
                    "percentage" => calculatePercentage($stageCounts['Universities suggested'], $inquiryButtonCount),
                    'link' =>  $stageCounts['Universities suggested'] != 0
                    ? $url . "lead-management?stages=Universities+suggested&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
            ],
        ];

        $data[1] = [
            "type" => "Application Stage",
            "count" => $applicationButtonCount,
            'link' => $applicationButtonCount != 0
            ? $url . "lead-management?values=application&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Application ready to submit',
                    'count' => $stageCounts['Application ready to submit'],
                    'percentage' => calculatePercentage($stageCounts['Application ready to submit'], $applicationButtonCount),
                    'link' =>  $stageCounts['Application ready to submit'] != 0
                    ? $url . "lead-management?stages=Application+ready+to+submit&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Application submitted',
                    'count' => $stageCounts['Application submitted'],
                    'percentage' => calculatePercentage($stageCounts['Application submitted'], $applicationButtonCount),
                    'link' =>  $stageCounts['Application submitted'] != 0
                    ? $url . "lead-management?stages=Application+submitted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Application Rejected',
                    'count' => $stageCounts['Application Rejected'],
                    'percentage' => calculatePercentage($stageCounts['Application Rejected'], $applicationButtonCount),
                    'link' =>  $stageCounts['Application Rejected'] != 0
                    ? $url . "lead-management?stages=Application+Rejected&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Application resubmitted',
                    'count' => $stageCounts['Application resubmitted'],
                    'percentage' => calculatePercentage($stageCounts['Application resubmitted'], $applicationButtonCount),
                    'link' =>  $stageCounts['Application resubmitted'] != 0
                    ? $url . "lead-management?stages=Application+resubmitted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Admission Letter Pending',
                    'count' => $stageCounts['Admission Letter Pending'],
                    'percentage' => calculatePercentage($stageCounts['Admission Letter Pending'], $applicationButtonCount),
                    'link' =>  $stageCounts['Admission Letter Pending'] != 0
                    ? $url . "lead-management?stages=Admission+Letter+Pending&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
            ],
        ];
        
        $data[2] = [
            "type" => "Offer Stage",
            "count" => $offerButtonCount,
            'link' => $offerButtonCount != 0
            ? $url . "lead-management?values=offer&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Offer Issued',
                    'count' => $stageCounts['Offer Issued'],
                    'percentage' => calculatePercentage($stageCounts['Offer Issued'], $offerButtonCount),
                    'link' =>  $stageCounts['Offer Issued'] != 0
                    ? $url . "lead-management?stages=Offer+Issued&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Offer Accepted',
                    'count' => $stageCounts['Offer Accepted'],
                    'percentage' => calculatePercentage($stageCounts['Offer Accepted'], $offerButtonCount),
                    'link' =>  $stageCounts['Offer Accepted'] != 0
                    ? $url . "lead-management?stages=Offer+Accepted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Offer Rejected',
                    'count' => $stageCounts['Offer Rejected'],
                    'percentage' => calculatePercentage($stageCounts['Offer Rejected'], $offerButtonCount),
                    'link' =>  $stageCounts['Offer Rejected'] != 0
                    ? $url . "lead-management?stages=Offer+Rejected&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
            ],
        ];

        $data[3] = [
            "type" => "Dropped Application",
            "count" => $droppedButtonCount,
            'link' => $droppedButtonCount != 0
            ? $url . "lead-management?values=dropped&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Student backed out / Dropped',
                    'count' => $stageCounts['Student backed out / Dropped'],
                    'percentage' => calculatePercentage($stageCounts['Student backed out / Dropped'], $droppedButtonCount),
                    'link' =>  $stageCounts['Student backed out / Dropped'] != 0
                    ? $url . "lead-management?stages=Student+backed+out+%2F+Dropped&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
            ],
        ];
        $data[4] = [
            "type" => "Fee Payment",
            "count" => $feePaymentButtonCount,
            'link' => $feePaymentButtonCount != 0
            ? $url . "lead-management?values=feePayment&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Initial Fee / Tuition Fee Deposit',
                    'count' => $stageCounts['Initial Fee / Tuition Fee Deposit'],
                    'percentage' => calculatePercentage($stageCounts['Initial Fee / Tuition Fee Deposit'], $feePaymentButtonCount),
                    'link' =>  $stageCounts['Initial Fee / Tuition Fee Deposit'] != 0
                    ? $url . "lead-management?stages=Initial+Fee+%2F+Tuition+Fee+Deposit&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
            ],
        ];
        
        $data[5] = [
            "type" => "Deferred Application",
            "count" => $deferedButtonCount,
            'deferedButtonCount' => $deferedButtonCount,
            'link' => $deferedButtonCount != 0
            ? $url . "lead-management?values=defered&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Student Defered',
                    'count' => $stageCounts['Student Defered'],
                    'percentage' => calculatePercentage($stageCounts['Student Defered'], $deferedButtonCount),
                    'link' =>  $stageCounts['Student Defered'] != 0
                    ? $url . "lead-management?stages=Student+Defered&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
            ],
        ];

        $data[6] = [
            "type" => "Visa Stage",
            "count" => $visaButtonCount,
            'link' => $visaButtonCount != 0
            ? $url . "lead-management?values=visa&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Visa Rejected',
                    'count' => $stageCounts['Visa Rejected'],
                    'percentage' => calculatePercentage($stageCounts['Visa Rejected'], $visaButtonCount),
                    'link' =>  $stageCounts['Visa Rejected'] != 0
                    ? $url . "lead-management?stages=Visa+Rejected&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Visa Accepted',
                    'count' => $stageCounts['Visa Accepted'],
                    'percentage' => calculatePercentage($stageCounts['Visa Accepted'], $visaButtonCount),
                    'link' =>  $stageCounts['Visa Accepted'] != 0
                    ? $url . "lead-management?stages=Visa+Accepted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Visa In Process',
                    'count' => $stageCounts['Visa In Process'],
                    'percentage' => calculatePercentage($stageCounts['Visa In Process'], $visaButtonCount),
                    'link' =>  $stageCounts['Visa In Process'] != 0
                    ? $url . "lead-management?stages=Visa+In+Process&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                // [
                //     'name' => 'Work Visa',
                //     'count' => $stageCounts['Work Visa'],
                //     'percentage' => calculatePercentage($stageCounts['Work Visa'], $visaButtonCount),
                //     'link' =>  $stageCounts['Work Visa'] != 0
                //     ? $url . "lead-management?stages=Work+Visa&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                //     : null,
                // ],
            ],
        ];

        $data[7] = [
            "type" => "Test Preparation",
            "count" => $testPreparationButtonCount,
            'link' => $testPreparationButtonCount != 0
            ? $url . "lead-management?values=testPreparation&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'Test Preparation',
                    'count' => $stageCounts['Test Preparation'],
                    'percentage' => calculatePercentage($stageCounts['Test Preparation'], $testPreparationButtonCount),
                    'link' =>  $stageCounts['Test Preparation'] != 0
                    ? $url . "lead-management?stages=Test+Preparation&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
           
            ],
        ];

        $data[8] = [
            "type" => "NA",
            "count" => ($results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount)),
            'link' => ($results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount)) != 0
            ? $url . "lead-management?values=na_work_visa&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
            : null,
            'datas' => [
                [
                    'name' => 'NA',
                    'count' => $results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount)-$stageCounts['Work Visa'],
                    'percentage' => calculatePercentage($results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount)-$stageCounts['Work Visa'],($results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount))),
                    'link' =>  $results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount) != 0
                    ? $url . "lead-management?stages=na&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                [
                    'name' => 'Work Visa',
                    'count' => $stageCounts['Work Visa'] ,
                    'percentage' => calculatePercentage($stageCounts['Work Visa'], $results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount)),
                    'link' =>  $results->count() - ($inquiryButtonCount + $applicationButtonCount + $offerButtonCount + $feePaymentButtonCount + $deferedButtonCount + $droppedButtonCount + $visaButtonCount + $testPreparationButtonCount) != 0
                    ? $url . "lead-management?stages=Work Visa&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null,
                ],
                
                
            ],
        ];
        
        $responseData = [
      
            'data' => $data,
            'total' => $results->count(),
        ];
        return response()->json($responseData);

    }


    public function getTypeWiseReportData(Request $request)
    {
        abort_if(!userCan('leadmgt.view'), 403);

        $daterange = $request->input('daterange');

        $encodedDaterange = urlencode($daterange);
        $url = env('APP_URL');

        if(userCan('admin.assign_list') || userCan('admin.assign')){
            if(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                $assign_to = $request->input('assign_to');
            }else{
                $assign_to = NULL;
            }
        
        }else{
            $assign_to = Auth::user()->id;
        }

        $query = Candidate::with('user');

        $query->whereHas('user', function ($q) use ($request) {
            if(userCan('admin.assign_list') || userCan('admin.assign')){
                if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                    $q->whereNull('assign_to');
                }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                    $q->where('assign_to', $request->input('assign_to'));
                }else{
                    if (checkleadManagerEnvRole()) {
                        $q->where('assign_to', Auth::user()->id);
                    }
                    if (checkRmEnvRole()) {
                        $q->where(function ($qr) {
                            $qr->where('assign_to', Auth::user()->id)
                              ->orWhere('assigned_rm', Auth::user()->id);
                        });
                    }
                }
            
            }else{
                $q->whereNotNull('assign_to')
                ->where('assign_to', Auth::user()->id); 
            }
        });

        if ($daterange) {
            // $dates = explode(' - ', $daterange);
            // $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->format('Y-m-d H:i:s');
            // $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->format('Y-m-d H:i:s');
    
            // $query->whereBetween('created_at', [$startDate, $endDate]);

            $query->whereHas('user', function ($q) use ($request) {
            $dates = explode(' - ', $request->input('daterange'));
            $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay();
            $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay();
            $q->whereBetween('assign_date', [$startDate, $endDate]);
            });
        }else{
            // $startDate = Carbon::now()->startOfMonth();
            // $endDate = Carbon::now()->endOfMonth();
            // $query->whereBetween('created_at', [$startDate, $endDate]);
            $query->whereHas('user', function ($q) use ($request) {
                $startDate = Carbon::now()->startOfMonth();
                $endDate = Carbon::now()->endOfMonth();
            $q->whereBetween('assign_date', [$startDate, $endDate]);
            });
            
        }

        $results = $query->get();
        $total = $results->count();
        $data = [];
        $data = [
            'TypeWise' => [
                [
                    'name' => 'Hot',
                    'count' => $results->where('user.lead_type', 'Hot')->count(),
                    'percentage' => calculatePercentage($results->where('user.lead_type', 'Hot')->count(), $total),
                    'link' => $results->where('user.lead_type', 'Hot')->count() != 0
                    ? $url . "lead-management?lead_type=Hot&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null
                   
                ],
                [
                    'name' => 'Cold',
                    'count' => $results->where('user.lead_type', 'Cold')->count(),
                    'percentage' => calculatePercentage($results->where('user.lead_type', 'Cold')->count(), $total),
                    'link' => $results->where('user.lead_type', 'Cold')->count() != 0
                    ? $url . "lead-management?lead_type=Cold&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null
                ],
                [
                    'name' => 'Warm',
                    'count' => $results->where('user.lead_type', 'Warm')->count(),
                    'percentage' => calculatePercentage($results->where('user.lead_type', 'Warm')->count(), $total),
                    'link' => $results->where('user.lead_type', 'Warm')->count() != 0
                    ? $url . "lead-management?lead_type=Warm&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null
                ],
            ],

            'CategoryWise' => [
                [
                    'name' => 'Gold',
                    'count' => $results->where('student_category', 'Gold')->count(),
                    'percentage' => calculatePercentage($results->where('student_category', 'Gold')->count(), $total),
                    'link' => $results->where('student_category', 'Gold')->count() != 0
                    ? $url . "lead-management?student_category=Gold&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null
                ],
                [
                    'name' => 'Silver',
                    'count' => $results->where('student_category', 'Silver')->count(),
                    'percentage' => calculatePercentage($results->where('student_category', 'Silver')->count(), $total),
                    'link' => $results->where('student_category', 'Silver')->count() != 0
                    ? $url . "lead-management?student_category=Silver&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null
                ],
                [
                    'name' => 'Bronze',
                    'count' => $results->where('student_category', 'Bronze')->count(),
                    'percentage' => calculatePercentage($results->where('student_category', 'Bronze')->count(), $total),
                    'link' => $results->where('student_category', 'Bronze')->count() != 0
                    ? $url . "lead-management?student_category=Bronze&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
                    : null
                ],
            ],
        ];
        
       // dd($data);
        $responseData = [
            'data' => $data,
            'total' => $total,
        ];
        // dd($responseData);
        return response()->json($responseData);

    }

    public function getCountryWiseReportData(Request $request)
    {
        abort_if(!userCan('leadmgt.view'), 403);


        $daterange = $request->input('daterange');

        $encodedDaterange = urlencode($daterange);
        $url = env('APP_URL');

        
        if(userCan('admin.assign_list') || userCan('admin.assign')){
            if(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                $assign_to = $request->input('assign_to');
            }else{
                $assign_to = NULL;
            }
        
        }else{
            $assign_to = Auth::user()->id;
        }

        if ($daterange) {
            $dates = explode(' - ', $daterange);
            $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->toDateTimeString();
            $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->toDateTimeString();
        } else {
            $startDate = Carbon::now()->startOfMonth()->toDateTimeString();
            $endDate = Carbon::now()->endOfMonth()->toDateTimeString();
        }
        
        $query = Candidate::with('destination')
            ->whereHas('user', function ($q) use ($request) {
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        $q->whereNull('assign_to');
                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        $q->where('assign_to', $request->input('assign_to'));
                    }else{
                        if (checkleadManagerEnvRole()) {
                            $q->where('assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $q->where(function ($qr) {
                                $qr->where('assign_to', Auth::user()->id)
                                  ->orWhere('assigned_rm', Auth::user()->id);
                            });
                        }
                    }
          
                }else{
                    $q->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            })
            ->whereHas('user', function ($q) use ($startDate,$endDate) {
            $q->whereBetween('assign_date', [$startDate, $endDate]);
            })
            ->get()
            ->toArray();
            
        $countries = Countries::all('id', 'name'); // Retrieve the list of countries from your database
        
        $preferredCountries = [];

        foreach ($query as $candidate) {
            if (!empty($candidate['destination'][0]['preferred_country'])) {
                $jsonString = str_replace(['"', '[', ']'], '', $candidate['destination'][0]['preferred_country']);
                $preferredCountryIds = explode(",", $jsonString);

                // Format the intake value for display
                $intake = $candidate['destination'][0]['intake'] ?? null;
                $formattedIntake = str_replace("'", " '", $intake);

                foreach ($preferredCountryIds as $countryId) {
                    $country = $countries->firstWhere('id', $countryId);
                    if ($country) {
                        $preferredCountries[] = [
                            'id' => $country->id,
                            'name' => $country->name,
                            'intake' => $formattedIntake,
                        ];
                    }
                }
            }
        }

        $totalCandidates = count($query);
        
        $countryCounts = array_count_values(array_column($preferredCountries, 'name'));
        // dd($countryCounts);
        $data['countryWise'] = [];
        // $mainIntake = Intake::whereIn('status', [1])->pluck('name')->toArray();

        foreach ($countryCounts as $country => $count) {
            $countryID = $countries->firstWhere('name', $country)->id; // Get the country ID by searching for the country name
            $percentage = calculatePercentage($count, $totalCandidates);
            $data['countryWise'][] = [
                'name' => $country,
                'count' => $count,
                'percentage' => $percentage,
                'link' => $url . "lead-management?preferred_country[]=" . $countryID . "&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to,
            ];
        }
        
        // foreach ($countryCounts as $country => $count) {
        //     $percentage = calculatePercentage($count, $totalCandidates);
        //     $data['countryWise'][] = [
        //         'name' => $country,
        //         'count' => $count,
        //         'percentage' => $percentage,
        //         'link' => $url."lead-management?preferred_country[]=".$countryID."&daterange=".$encodedDaterange."&assign_to=".Auth::user()->id,
        //     ];
        // }

        $responseData = [
            'data' => $data,
            'total' => $totalCandidates,
        ];
        return response()->json($responseData);
    }


public function getTargetsDataApplication(Request $request)
{
  
   
        if(userCan('admin.assign_list') || userCan('admin.assign')){
            if(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                $assign_to = $request->input('assign_to');
            }else{
                $assign_to = NULL;
            }
        
        }else{
            $assign_to = Auth::user()->id;
        }

        $applicationSubmitted = 0;
        $applicationSuccessful = 0;
        $visaSubmitted = 0;
        $visaSuccessful = 0;

        $daterange = $request->input('daterange');

        $encodedDaterange = urlencode($daterange);
        $url = env('APP_URL');
        
        if ($daterange) {
            $dates = explode(' - ', $daterange);
            $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->toDateTimeString();
            $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->toDateTimeString();
        } else {
            $startDate = Carbon::now()->startOfMonth()->toDateTimeString();
            $endDate = Carbon::now()->endOfMonth()->toDateTimeString();
        }
        
        // $results = UserTimeline::join('users', 'user_timeline.user_id', '=', 'users.id')
        //     // ->whereBetween('user_timeline.created_at', [$startDate, $endDate])
        //     ->whereNotNull('users.assign_to')
        //     ->where('users.assign_to', Auth::user()->id)
        //     ->whereIn('user_timeline.stages', ['Visa In Process', 'Work Visa'])
        //     ->get();
        //     // ->whereIn('user_timeline.stages', ['Visa In Process', 'Work Visa'])
        //     // ->get();
        //     // $results = $query->get();

        // $results = UserTimeline::select('user_timeline.user_id', 'candidates.id as candidateID',  'user_timeline.stages as stages', 'user_timeline.id as id', 'users.name as name', 'users.assign_to as assign_to')
        // ->join('users', 'user_timeline.user_id', '=', 'users.id')
        // ->join('candidates', 'candidates.user_id', '=', 'users.id')
        // ->leftJoin('candidate_applications', 'candidate_applications.id', '=', 'candidates.id')
        // ->whereNotNull('users.assign_to')
        // ->where('users.assign_to', Auth::user()->id)
        // ->whereBetween('user_timeline.created_at', [$startDate, $endDate])
        // ->whereIn('user_timeline.stages', ['Application submitted','Admitted','Visa In Process','Visa Accepted','Visa In Process','Work Visa'])
        // ->groupBy(['user_timeline.user_id', 'user_timeline.stages'])
        // ->get();

        $results = UserTimeline::select('user_timeline.user_id', 'candidates.id as candidateID', 'user_timeline.stages as stages', 'user_timeline.id as id', 'users.name as name', 'users.assign_to as assign_to')
        ->join('users', 'user_timeline.user_id', '=', 'users.id')
        ->join('candidates', 'candidates.user_id', '=', 'users.id')
        ->leftJoin('candidate_applications', 'candidate_applications.id', '=', 'candidates.id')
        ->whereNotNull('users.assign_to')
        ->where('users.assign_to', Auth::user()->id)
        ->whereBetween('user_timeline.created_at', [$startDate, $endDate])
        ->whereIn('user_timeline.stages', ['Application submitted', 'Admitted', 'Visa In Process', 'Visa Accepted', 'Visa In Process', 'Work Visa'])
        ->groupBy(['user_timeline.user_id', 'user_timeline.stages'])
        ->get();


        $totalLeads = $results->count();

        $stageCounts = [
            'Inquiry' => 0,
            'Counselling' => 0,
            'Documents Received' => 0,
            'Document pending' => 0,
            'University to be suggested' => 0,
            'Universities suggested' => 0,
            'Application ready to submit' => 0,
            'Application submitted' => 0,
            'Application Rejected' => 0,
            'Application resubmitted' => 0,
            'Admission Letter Pending' => 0,
            // 'Offer Issued Conditional / Unconditional' => 0,
            // 'Conditional Offer Received' => 0,
            // 'Un-conditional Offer Received' => 0,
            'Offer Issued' => 0,
            // 'Offer Issued Unconditional' => 0,
            'Offer Accepted' => 0,
            'Offer Rejected' => 0,
            'Student backed out / Dropped' => 0,
            'Initial Fee / Tuition Fee Deposit' => 0,
            'Visa Rejected' => 0,
            'Visa Accepted' => 0,
            'Visa In Process' => 0,
            'Work Visa' => 0,
            'Student Defered' => 0,
            'Admitted' => 0,
        ];

        foreach ($results as $result) {
         
            $userStages = $result->stages;
            if (isset($stageCounts[$userStages])) {
                $stageCounts[$userStages]++;
            }
        }
        
        $applicationSubmitted = $stageCounts['Application submitted'];
        $applicationSuccessful = $stageCounts['Admitted'];
        $applicationSubmittedLink =   $stageCounts['Application submitted'] != 0
        ? $url . "lead-management?stages=Application+submitted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
        : "#";
        $applicationSuccessfulLink =   $stageCounts['Admitted'] != 0
        ? $url . "lead-management?stages=Admitted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
        : "#";
     

        $visaSubmitted = $stageCounts['Visa In Process'];
        $visaSuccessful = $stageCounts['Visa Accepted'];
        $visaSubmittedLink =   $stageCounts['Visa In Process'] != 0
        ? $url . "lead-management?stages=Visa+In+Process+submitted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
        : "#";
        $visaSuccessfulLink =   $stageCounts['Visa Accepted'] != 0
        ? $url . "lead-management?stages=Visa+Accepted&daterange=" . $encodedDaterange . "&assign_to=" . $assign_to
        : "#";


        $applicationSuccessful_UserDetails = 0;
        $applicationSubmitted_UserDetails = 0;
        $visaSuccessful_UserDetails = 0;
        $visaSubmitted_UserDetails = 0;

       if ($totalLeads > 0) {

        $documentReceived = $results->whereIn('stages', ['University to be suggested','Application ready to submit'])->groupBy('user_id')->count();
        $applicationSubmitted = $results->where('stages', 'Application submitted')->groupBy('user_id')->count();
        $uniOfferReceived = $results->whereIn('stages', ['Offer Issued','Offer Accepted','Offer Rejected'])->groupBy('user_id')->count();
        $uniOfferAccepted = $results->where('stages', 'Admitted')->groupBy('user_id')->count();

        //    $applicationSubmitted_UserDetails = $results->where('stages', 'Application submitted')->groupBy('user_id')->toArray();
        //    $applicationSubmitted_UserDetails = $results->where('stages', 'Application submitted')->groupBy('user_timeline.user_id')->all();
        // $applicationSubmitted_UserDetails = collect($results)->where('stages', 'Application submitted')->groupBy('user_id')
        // ->map(function ($groupedItems) {
        //     return $groupedItems->first(); 
        // })
        // ->toArray(); 

        $applicationSuccessful_UserDetails = collect($results)->whereIn('stages', ['Admitted'])->groupBy('user_id')
        ->map(function ($groupedItems) {
            return $groupedItems->first(); 
        })
        ->toArray(); 
        

        // $visaSubmitted_UserDetails = collect($results)
        // ->whereIn('stages', ['Visa In Process'])
        // ->groupBy('user_id')
        // ->map(function ($groupedItems) {
        //     $count = CandidateApplication::leftJoin('candidates', 'candidates.id', '=', 'candidate_applications.candidate_id')
        //         ->whereIn('candidates.id', $groupedItems->pluck('candidateID'))
        //         ->where('candidate_applications.status', 'Visa In Process')
        //         ->count();
    
        //     // Add the count to the first item in the group
        //     $groupedItems->first()->visaCount = $count;
    
        //     return $groupedItems->first();
        // })
        // ->toArray();

        // $applicationSubmitted_UserDetails = collect($results)
        // ->where('stages', 'Application submitted')
        // ->groupBy('user_id')
        // ->map(function ($groupedItems) {
        //     // Extract candidate IDs from grouped items
    
    
        //     // Add the count to each item in the group
        //     $groupedItems->each(function ($item){
        //         $candidateIds = $item->user_id;
        //                 // Load candidate_applications relationship with a specific status
        //                 $count = CandidateApplication::whereIn('candidate_id', $candidateIds)
        //                     ->where('status', 'Application submitted to the Associate')
        //                     ->count();
        //         $item->visaCount = $count;
        //     });
    
        //     return $groupedItems;
        // })
        // ->toArray();

        // $applicationSubmitted_UserDetails = collect($results)->where('stages', 'Application submitted')->groupBy('user_id')
        // ->map(function ($groupedItems) {

        //     $groupedItems->each(function ($item){
        //         $candidateIds = $item->user_id;
        //                 // Load candidate_applications relationship with a specific status
        //                 $count = CandidateApplication::whereIn('candidate_id', $candidateIds)
        //                     ->where('status', 'Application submitted to the Associate')
        //                     ->count();
        //         $item->visaCount = $count;
        //     });

        //     return $groupedItems->first(); 
        // })
        // ->toArray(); 

        $applicationSubmitted_UserDetails = collect($results)
        ->where('stages', 'Application submitted')
        ->groupBy('user_id')
        ->map(function ($groupedItems) {
            $groupedItems->each(function ($item){
                $candidateIds = $item->user_id;
    
                // Load candidate_applications relationship with a specific status
                $count = CandidateApplication::where('candidate_id', $candidateIds)
                    ->where('status', 'Application submitted to the Associate')
                    ->count();
                $item->applicationcount = $count;
            });
            return $groupedItems;
        })
        ->flatten(1)
        ->toArray();
    
        // $applicationSubmitted_UserDetails = collect($results)
        // ->where('stages', 'Application submitted')
        // ->groupBy('user_id')
        // ->map(function ($groupedItems) {
        //     $groupedItems->each(function ($item){
        //         $candidateIds = $item->user_id;

        //                 // Load candidate_applications relationship with a specific status
        //         $count = CandidateApplication::where('candidate_id', $candidateIds)
        //             ->where('status', 'Application submitted to the Associate')
        //             ->count();
        //         $item->applicationcount = $count;
        //     });
        //     return $groupedItems;
        //     // return $groupedItems->first(); 
        // })
        // ->toArray();
// dd( $applicationSubmitted_UserDetails);

  
        $visaSubmitted_UserDetails = collect($results)->whereIn('stages', ['Visa In Process'])->groupBy('user_id')
        ->map(function ($groupedItems) {
            return $groupedItems->first(); 
        })
        ->toArray(); 

        $visaSuccessful_UserDetails = collect($results)->whereIn('stages', ['Visa Accepted'])->groupBy('user_id')
        ->map(function ($groupedItems) {
            return $groupedItems->first(); 
        })
        ->toArray(); 
       }
       
        $responseData = [
            'applicationSubmitted' => $applicationSubmitted,
            'applicationSuccessful' => $applicationSuccessful,
            'visaSubmitted' => $visaSubmitted,
            'visaSuccessful' => $visaSuccessful,
            'applicationSuccessfulLink' => $applicationSuccessfulLink,
            'applicationSubmittedLink' => $applicationSubmittedLink,
            'visaSuccessfulLink' => $visaSuccessfulLink,
            'visaSubmittedLink' => $visaSubmittedLink,
            'applicationSubmitted_UserDetails' => $applicationSubmitted_UserDetails,
            'applicationSuccessful_UserDetails' => $applicationSuccessful_UserDetails,
            'visaSuccessful_UserDetails' => $visaSuccessful_UserDetails,
            'visaSubmitted_UserDetails' => $visaSubmitted_UserDetails,
          
        ];
        
        // dd($responseData);

    return response()->json($responseData);
}


public function getIntakeCountryWiseReportData(Request $request)
{
    abort_if(!userCan('leadmgt.view'), 403);


    $daterange = $request->input('daterange');

    if ($daterange) {
        $dates = explode(' - ', $daterange);
        $startDate = Carbon::createFromFormat('m/d/Y', $dates[0])->startOfDay()->toDateTimeString();
        $endDate = Carbon::createFromFormat('m/d/Y', $dates[1])->endOfDay()->toDateTimeString();
    } else {
        $startDate = Carbon::now()->startOfMonth()->toDateTimeString();
        $endDate = Carbon::now()->endOfMonth()->toDateTimeString();
    }
    
//     $query = Candidate::with('destination')
//     ->whereHas('user', function ($q) {
//         $q->whereNotNull('assign_to')
//             ->where('assign_to', Auth::user()->id);
//     })
//     ->whereBetween('created_at', [$startDate, $endDate])
//     ->get();

//     $totalCandidates = $query->count();
//     // $mainIntake = Intake::where('status', 1)->pluck('name')->toArray();
//     $countries = Country::where('status', 1)->pluck("name",'id')->toArray();

//     // $countryNames = array_column($countries, 'name'); // Extract 'name' column

//     // $intakeWiseData = [
//     //     'countryNames' => $countryNames,
//     //     'intake' => [],
//     // ];

//     // foreach ($mainIntake as $intakeValue) {
//     //     $intakeData = [
//     //         'name' => $intakeValue,
//     //         'data' => [],
//     //     ];

//     //     foreach ($countries as $country) {
//     //         $intakeData['data'][] = [
//     //             'id' => $country['id'],
//     //             'name' => $country['name'],
//     //             'count' => 0,
//     //             'link' => "candidate?preferred_country[]=" . $country['id'] . "&intake=" . $intakeValue . "&daterange=" . $daterange . "&assign_to=" . $assign_to,
//     //         ];
//     //     }

//     //     $intakeWiseData['intake'][] = $intakeData;
//     // }

//     $intakeWiseData = [];
//     $avialableIntakes = [];
//     $avialableCountries = [];

//     foreach ($query as $candidate) {
//         $intake = $candidate->destination[0]['intake'] ?? null;
//         $preferredCountries = $candidate->destination[0]['preferred_country'] ?? NUll;

//         if (!empty($intake) && !empty($preferredCountries)) {
//             $intakes = explode(",", $intake);
//             $preferredCountryIds = explode(",", str_replace(['"', '[', ']'], '', $preferredCountries));

//             foreach ($intakes as $intakeValue) {
//                 // dd($preferredCountryIds);
//                 // if (in_array($intakeValue, $mainIntake) && !empty($preferredCountryIds)) {
//                     if(!in_array($intakeValue,$avialableIntakes)) array_push($avialableIntakes,$intakeValue);
//                     foreach ($preferredCountryIds as $countryID) {
//                         // if (in_array($countryID, array_column($countries, 'id'))) {
//                             // Find the index of the intake value
//                             // $rowKey = array_search($intakeValue, array_column($intakeWiseData['intake'], 'name'));
//                             // if ($rowKey !== false) {
//                                 // foreach ($intakeWiseData['intake'][$rowKey]['data'] as &$entry) {
//                                 //     if ($entry['id'] == $countryID) {
//                                 //         $entry['count']++;
//                                 //     }
//                                 // }
//                             // }
//                         // }
                        
//                         if(!in_array($countryID,$avialableCountries)) array_push($avialableCountries,$countryID);
//                         if(isset($intakeWiseData[$intakeValue][$countryID])){
//                             $intakeWiseData[$intakeValue][$countryID]++;
//                         }else{
//                             $intakeWiseData[$intakeValue][$countryID] = 1;
//                         }
//                     }
//                 // } cvb
//             }
//         }
//     }

// // dd($countries);
// //     dd($data);
    
    
// //     dd($data);
//     // dd($avialableCountries);
//     $intakeWiseDatas = [];
//      foreach ($intakeWiseData as $key => $intakeValue) {
//         // dd($intakeValue);
//         $intakeData = [
//             'name' => $key,
//             'data' => [],
//         ];

//         foreach ($intakeValue as $keycountry => $country) {
//             // dd($country);
//             foreach ($avialableCountries as $avialableCountrie) {
//                  if($avialableCountrie == $keycountry){
//                     $intakeData['data'][] = [
//                         'id' => $keycountry,
//                         'count' => $country,
//                         'link' => "candidate?preferred_country[]=" . $keycountry . "&intake=" . $key . "&daterange=" . $daterange . "&assign_to=" . $assign_to,
//                     ];

//                  }else{
//                     $intakeData['data'][] = [
//                         'id' => $avialableCountrie,
//                         'count' => "NA",
//                         'link' => "NA",
//                     ];
//                  }
//             }
//             // dd($keycountry);
//             // $intakeData['data'][] = [
//             //     'id' => $keycountry,
//             //     'count' => $country,
//             //     'link' => "candidate?preferred_country[]=" . $keycountry . "&intake=" . $key . "&daterange=" . $daterange . "&assign_to=" . $assign_to,
//             // ];
//         }

//         $intakeWiseDatas['intake'][] = $intakeData;
//     }
//     dd($intakeWiseDatas);
//     $responseData = [
//         'intakeWiseData' => $intakeWiseData,
//         'countries' => $countries,
//         'avialableCountries' => $avialableCountries,
//         'avialableIntakes' => $avialableIntakes,
//         'total' => $totalCandidates,
//     ];


        if(userCan('admin.assign_list') || userCan('admin.assign')){
            if(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                $assign_to = $request->input('assign_to');
            }else{
                $assign_to = NULL;
            }

        }else{
            $assign_to = Auth::user()->id;
        }

        $query = Candidate::with('destination')
            ->whereHas('user', function ($q) use ($request) {
                if(userCan('admin.assign_list') || userCan('admin.assign')){
                    if(!empty($request->input('assign_to')) && $request->input('assign_to') == "all"){

                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') == "na"){
                        $q->whereNull('assign_to');
                    }elseif(!empty($request->input('assign_to')) && $request->input('assign_to') != "na"){
                        $q->where('assign_to', $request->input('assign_to'));
                    }else{
                        if (checkleadManagerEnvRole()) {
                            $q->where('assign_to', Auth::user()->id);
                        }
                        if (checkRmEnvRole()) {
                            $q->where(function ($qr) {
                                $qr->where('assign_to', Auth::user()->id)
                                  ->orWhere('assigned_rm', Auth::user()->id);
                            });
                        }
                    }
                }else{
                    $q->whereNotNull('assign_to')
                    ->where('assign_to', Auth::user()->id); 
                }
            })
            ->whereHas('user', function ($q) use ($startDate,$endDate) {
                $q->whereBetween('assign_date', [$startDate, $endDate]);
                })
            ->get();

        $totalCandidates = $query->count();

        // dd($query);

        $intakeWiseData = [];
        $availableIntakes = [];
        $availableCountries = [];

        $url = env('APP_URL');

        foreach ($query as $candidate) {
            // $intake = $candidate->destination[0]['intake'] ?? null;
            $intakeMonths = $candidate->destination[0]['intake_months'] ?? null;
            $intakeYears = $candidate->destination[0]['intake_years'] ?? null;
            $preferredCountries = $candidate->destination[0]['preferred_country'] ?? null;

            if (!empty($intakeMonths) && !empty($intakeYears) && !empty($preferredCountries)) {
        
                $intake = [];
              
                if (!empty($intakeMonths) && !empty($intakeYears)) {
                    // dd($intakeMonths);
                    $intake_month = explode(",", $intakeMonths);
                    $intake_years = explode(",", $intakeYears);
                
                    foreach ($intake_years as $year) {
                        foreach ($intake_month as $month) {
                            $yearShort = substr($year, -2);
                            $intake[] = $month . "'" . $yearShort;
                        }
                    }
                
                }
                // dd($intake);
                // $intakes = explode(",", $intake);
                $intakes = $intake;
                $preferredCountryIds = explode(",", str_replace(['"', '[', ']'], '', $preferredCountries));
                
                foreach ($intakes as $intakeValue) {
                    if (!in_array($intakeValue, $availableIntakes)) {
                        $availableIntakes[] = $intakeValue;
                    }

                    foreach ($preferredCountryIds as $countryID) {
                        if (!in_array($countryID, $availableCountries)) {
                            $availableCountries[] = $countryID;
                        }

                        if (isset($intakeWiseData[$intakeValue][$countryID])) {
                            $intakeWiseData[$intakeValue][$countryID]++;
                        } else {
                            $intakeWiseData[$intakeValue][$countryID] = 1;
                        }
                    }
                }
            }
        }

        $availableCountries = Country::whereIn('id', $availableCountries)->pluck('name', 'id')->toArray();

        $intakeWiseDatas = [
            'intake' => [],
        ];
        // dd($daterange);
        $encodedDaterange = urlencode($daterange);

        // dd($encodedDaterange);
        // dd($daterange);
        foreach ($availableIntakes as $intakeValue) {
            $intakeData = [
                'name' => $intakeValue,
                'data' => [],
            ];

            foreach ($availableCountries as $countryID => $countryName) {


                    if (!empty($intakeValue)) {
                        $intakeParts = explode("'", $intakeValue);
                        $formattedIntake = "&intake_month=&intake_years=";
                        if (count($intakeParts) == 2) {
                            $intake_month = $intakeParts[0];
                            $intake_years = "20".$intakeParts[1];

                            $formattedIntake = "&intake_month%5B%5D=" . $intake_month . "&intake_years%5B%5D=" . $intake_years;
                        }
                    } 
                $count = $intakeWiseData[$intakeValue][$countryID] ?? "NA";

                $intakeData['data'][] = [
                    'id' => $countryID,
                    'name' => $countryName,
                    'count' => $count,
                    'link' => $url."lead-management?preferred_country[]=".$countryID."&daterange=".$encodedDaterange.$formattedIntake."&assign_to=".$assign_to,

                ];
            }

            $intakeWiseDatas['intake'][] = $intakeData;
        }

        $responseData = [
            'intakeWiseData' => $intakeWiseDatas,
            'availableCountries' => $availableCountries,
            'total' => $totalCandidates,
        ];


        // dd($intakeWiseDatas['intake']);
        return response()->json($responseData);
    
    
}

public function leadTypeUpdate(Request $request, $id)
{
    try {
        $user = User::findOrFail($id);
        $user->update(['lead_type' => $request->lead_type]);

        return response()->json(['message' => 'Lead type updated successfully']);
    } catch (\Exception $e) {
    
        return response()->json(['error' => 'Failed to update lead type. Please try again.'], 500);
    }
}

public function destroy($id)
{
    abort_if(!userCan('candidate.delete'), 403);
    try {
        $candidate = Candidate::find($id);
        $user = User::FindOrFail($candidate->user_id);
        $user->delete();

        $candidate->delete();
        return response()->json(['success' => true, 'status' => 1]);
    } catch (\Exception $e) {
    
        return response()->json(['error' => 'something went wrong.'], 500);
    }
}


public function allTatUpdate(Request $request)
{
    abort_if(!userCan('candidate.delete'), 403);
    try {
    
        AdminHelper::allTatUpdates();
    
        return redirect()->route('admin.leadManagement')->with('success', 'TAT Time Update Successfully!');

        } catch (\Throwable $th) {
            return redirect()->back()->withInput()->with('error', config('app.debug') ? $th->getMessage() : 'Something went wrong');
        }
}
}