<?php

namespace App\Http\Controllers\Clients;

use Carbon\Carbon;
use App\Models\Pop;
use App\Models\Area;
use App\Models\Client;
use App\Models\Packages;
use App\Models\Reseller;
use App\Jobs\ClientSyncJob;
use App\Models\Clientsinfo;
use Illuminate\Http\Request;
use App\Models\ClientReferer;
use App\Models\CustomerAccount;
use App\Models\ResellerSmsGetway;
use App\Models\CompanyInformation;
use Illuminate\Support\Facades\DB;
use App\Exports\SearchResultExport;
use App\Http\Controllers\Controller;
use Brian2694\Toastr\Facades\Toastr;
use Maatwebsite\Excel\Facades\Excel;
use App\Classes\MikrotikService\Mikrotik;
use Illuminate\Contracts\Session\Session;
use Illuminate\Http\Resources\MergeValue;
use App\Classes\MikrotikService\SyncWithMk;
use App\Services\RadiusClientSync;
use Illuminate\Database\Eloquent\Collection;
use Facade\Ignition\Support\Packagist\Package;
use Illuminate\Support\Facades\Auth;

/*miktotik*/
use RouterOS\Client as routerClient;
use RouterOS\Query;

class ClientsSearchConntroller extends Controller
{

    public function __construct()
    {
        $this->middleware('permission:bulk-date-extended', ['only' => ['bulkExpariyDateExtended']]);
    }


    public function search(request $request)
    {
        if (request()->ajax()) {

            $uid = auth()->user()->id;
            $con = $request->search;

            $pop_ids = Client::pop_ids();

            $permissions = auth()->user()->getAllPermissions()->pluck('name')->toArray();

            $list = Client::list();

            if ($request->id) {
                $list->where('id', $request->id);
            }

            if ($request->username) {
                $list->where('userid', 'like', '%' . $request->username . '%');
            }

            if ($request->customerCode) {
                $list->where('customer_code', 'like', '%' . $request->customerCode . '%');
            }

            if ($request->contact) {
                $list->whereHas('clientsinfo', function ($q) use ($request) {
                    $q->where('contact_no', 'like', '%' . $request->contact . '%');
                });
            }

            if ($request->box) {
                $list->whereHas('clientsinfo', function ($q) use ($request) {
                    $q->where('box', 'like', '%' . $request->box . '%');
                });
            }

            if ($request->area) {
                $list->whereHas('clientsinfo', function ($q) use ($request) {
                    $q->where('area', 'like', '%' . $request->area . '%');
                });
            }
            if ($request->customername) {
                $list->whereHas('clientsinfo', function ($q) use ($request) {
                    $q->where('clients_name', 'like', '%' . $request->customername . '%')
                        ->orWhere('father_name', 'like', '%' . $request->customername . '%')
                        ->orWhere('mother_name', 'like', '%' . $request->customername . '%')
                        ->orWhere('flat_no', 'like', '%' . $request->customername . '%')
                        ->orWhere('building_no', 'like', '%' . $request->customername . '%')
                        ->orWhere('building_name', 'like', '%' . $request->customername . '%')
                        ->orWhere('other_contact', 'like', '%' . $request->customername . '%')
                        ->orWhere('road_no', 'like', '%' . $request->customername . '%')
                        ->orWhere('block_sector', 'like', '%' . $request->customername . '%')
                        ->orWhere('road_name', 'like', '%' . $request->customername . '%')
                        ->orWhere('owner_name', 'like', '%' . $request->customername . '%')
                        ->orWhere('national_id', 'like', '%' . $request->customername . '%');
                });
            }

            $list = $list->take(100)->get();


            if (checkAPI()) {
                $online = mikrotikOnlineAndOfflineUsers();
            } else {
                $online = Client::online($list->pluck('userid'));
            }

            return view('clients.customer_search', [
                'list' => $list,
                'online' => $online,
                'permissions' => $permissions,
            ]);
        }
    }

    public function clientSearch()
    {
        // dd('comes');
        $confday = config('app.billing_cycle');


        $data = [
            'page_title' => 'Client Search',
            'packages' => userPackages(),
            'confday' => config('app.billing_cycle')
        ];
        if (checkSettings('select_option_for_box_and_ponsetting') == 'enable') {
            $data['tjBoxes'] = \App\Models\map\TjBox::orderBy('name', 'asc')->get();
            $data['pons'] = \App\Models\PON::orderBy('name', 'asc')->get();
        }
        return view('clients.clientSearch', $data);
    }

    public function clientSearchReport11(request $r)
    {



        dd($r->all());

        $reseller = Reseller::resellerList();

        $reseller_ids = $reseller->where('id', $r->reseller)->pluck('id')->toArray();

        $pop = Pop::conditionalList();
        $pop_ids = $pop->pluck('id')->toArray();


        $list = DB::table('clients')
            ->leftJoin('clientsinfo', 'clientsinfo.client_id', '=', 'clients.id')
            ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            ->leftJoin('sub_packages', 'sub_packages.id', '=', 'clients.sub_package_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->leftJoin('customer_accounts', 'customer_accounts.client_id', '=', 'clients.id')

            ->select(
                'clients.*',
                'clientsinfo.*',
                'clients.created_at as created_at',
                'customer_accounts.dueAmount',
                'customer_accounts.totalPaid',
                'customer_accounts.totalDiscount',
                'customer_accounts.totalBillAmount',
                'clientsinfo.flat_no',
                'clientsinfo.building_name',
                'clientsinfo.road_no',
                'clientsinfo.block_sector',
                'pops.bill_generate',
                'packages.package_name',
                'pops.popname',
                'clientsinfo.clients_name',
                'clientsinfo.contact_no',
                'clientsinfo.father_name',
                'clientsinfo.mother_name',
                'clientsinfo.area',
                'clientsinfo.email',
                'clientsinfo.national_id',
                'packages.package_rate',
                'clients.id as id',
                'sub_packages.name as sub_package_name',
                'sub_packages.rate as sub_package_rate'
            )->where('client_approval', 'approved');




        $resellerlist = Reseller::resellerList()->get();
        $reseller_ids = $resellerlist->pluck('id')->toArray();


        $poplist = Pop::conditionalList();
        $pop_ids = $poplist->pluck('id')->toArray();




        $uid = auth()->id();


        if ($r->value != '') {


            $list->where('clients.userid', $r->value)
                ->whereIn('pops.id', $pop_ids);
        } else {
            // $list->where('clients.clients_status', '!=', 'deactive');

            if ($r->reseller == 'all') {
                $list->whereIn('pops.reseller_id', $reseller_ids);
            } else {
                $list->where('pops.reseller_id', $r->reseller);
            }

            if ($r->pop == 'all') {
                $list->whereIn('pops.id', $pop_ids);
            } else {
                $list->where('pops.id', $r->pop);
            }

            if ($r->billing_cycle != 'all') {
                $list->where('clients.billing_cycle', $r->billing_cycle);
            }

            if ($r->user_state != 'all') {
                $list->where('clients.clients_status', $r->user_state);
            }

            if ($r->recharge_state != 'all') {
                $list->where('clients.clients_status', $r->recharge_state);
            }
        }


        $result = $list->get();

        //   dd($result);

        $packages = '';

        if ($list == null) {
            return '<p class="text-center bg-gray-50 p-2">No User Found</p>';
        } else {
            return view('clients.userListForRechargeSearch', [
                'list' => $result,
                'reseller' => $reseller,
                'pop' => $pop,
                'packages_count' => $packages

            ]);
        }

        //   return $list->get();
    }

    public function clientSearchReport(Request $r)
    {



        if ($r->page) {
            $condition = $r->session()->get('customer_search_result');
            foreach ($condition as $key => $val) {
                $r->request->add([$key => $val]);
            }
        } else {
            $r->session()->put('customer_search_result', $r->all());
        }

        $reseller = userResellers();
        $pop = userPops();
        $pop_ids = $pop->pluck('id')->toArray();

        // $list = Client::with('packages:id,package_name,package_rate,client_payment_amount', 'customerAccount',)
        //     ->with('pops:id,popname,reseller_id,bill_generate,subreseller,experity_check')
        //     ->with('subpack:id,name,rate,client_payment_amount')
        //     ->with('clientsinfo:id,client_id,clients_name,contact_no,area,flat_no,building_no,road_no,block_sector,otc,owner_name,owner_number,remarks,dob,cable_type,road_name,otc_due,other_contact,box,joinDate,client_latitude,client_longitude,building_name,building_no,cat_length')
        //     ->selectRaw('crm_status,id,down_bandwidth,up_bandwidth,pop_id,billing_cycle,package_id,userid,password,ip_address,expire_date,payment_dadeline,created_by,client_approval,created_at,clients_status,sub_package_id,customer_id,marketed_by,deleted_at,parmanent_discount,ignoreMKCheck,required_cable,customer_code,client_payment_amount,un_recharge_amount')
        //     ->where('client_approval', 'approved')
        //     ->whereIn('pop_id', userPops()->pluck('id'));

        $list = Client::with([
            'packages:id,package_name,package_rate,client_payment_amount',
            'customerAccount',
            'pops:id,popname,reseller_id,bill_generate,subreseller,experity_check',
            'subpack:id,name,rate,client_payment_amount',
            'clientsinfo:id,client_id,clients_name,contact_no,area,flat_no,building_no,road_no,block_sector,otc,owner_name,owner_number,remarks,dob,cable_type,road_name,otc_due,other_contact,box,joinDate,client_latitude,client_longitude,building_name,building_no,cat_length,map_box_id,onu_serial',
        ])
            ->select([
                'crm_status',
                'id',
                'down_bandwidth',
                'up_bandwidth',
                'pop_id',
                'billing_cycle',
                'package_id',
                'userid',
                'password',
                'ip_address',
                'expire_date',
                'payment_dadeline',
                'created_by',
                'client_approval',
                'created_at',
                'clients_status',
                'sub_package_id',
                'customer_id',
                'marketed_by',
                'deleted_at',
                'parmanent_discount',
                'ignoreMKCheck',
                'required_cable',
                'customer_code',
                'slug',
                'client_payment_amount',
                'un_recharge_amount',
                'billing_start_date',
                'auto_deactive',
                'isStatic',
                'experity_check',
                'customer_account_balance_reseller',
                'mac'
            ])
            ->where('client_approval', 'approved')
            ->whereIn('pop_id', userPops()->pluck('id'));


        $reseller_customer_sms = false;

        $list->when(filled($r->value), function ($query) use ($r) {
            return $query->where('userid', 'like', '%' . $r->value . '%');
        });

        $list->when(filled($r->contact), function ($query) use ($r) {
            return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                $subQuery->where('contact_no', 'like', '%' . $r->contact . '%');
            });
        });

        $list->when(filled($r->area), function ($query) use ($r) {
            return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                $subQuery->where('area', 'like', '%' . $r->area . '%');
            });
        });

        if (checkSettings('select_option_for_box_and_ponsetting') == 'enable') {
            $list->when(filled($r->map_box_id), function ($query) use ($r) {
                return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                    $subQuery->where('map_box_id', $r->map_box_id);
                });
            });
            $list->when(filled($r->onu_serial), function ($query) use ($r) {
                return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                    $subQuery->where('onu_serial', $r->onu_serial);
                });
            });
        } else {
            $list->when(filled($r->box), function ($query) use ($r) {
                return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                    $subQuery->where('box', 'like', '%' . $r->box . '%');
                });
            });
        }

        $list->when(filled($r->cable_type), function ($query) use ($r) {
            return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                $subQuery->where('cable_type', 'like', '%' . $r->cable_type . '%');
            });
        });

        $list->when(filled($r->user_id), function ($query) use ($r) {
            return $query->where('id', $r->user_id);
        });

        $list->when(filled($r->code), function ($query) use ($r) {
            return $query->where('customer_code', $r->code);
        });

        $list->when(filled($r->name), function ($query) use ($r) {
            return $query->whereHas('clientsinfo', function ($subQuery) use ($r) {
                $subQuery->where('clients_name', 'like', '%' . $r->name . '%');
            });
        });

        $list->when(filled($r->billing_cycle) && $r->billing_cycle !== 'all', function ($query) use ($r) {
            return $query->where('billing_cycle', $r->billing_cycle);
        });

        $list->when(filled($r->user_state) && $r->user_state !== 'all', function ($query) use ($r) {
            return $query->where('clients_status', $r->user_state);
        });



        if (filled($r->expire_date_from) && filled($r->expire_date_to)) {
            $start = Carbon::parse($r->expire_date_from)->startOfDay();
            $end   = Carbon::parse($r->expire_date_to)->endOfDay();

            $list->whereBetween('expire_date', [$start, $end]);
        } else if (filled($r->expire_date_from)) {
            $list->whereDate('expire_date', Carbon::parse($r->expire_date_from)->format('Y-m-d'));
        } else if (filled($r->expire_date_to)) {
            $list->whereDate('expire_date', Carbon::parse($r->expire_date_to)->format('Y-m-d'));
        }




        $list->when(filled($r->static_ip), function ($query) use ($r) {
            return $query->where('ip_address', $r->static_ip);
        });

        if ($r->check_ip === 'on') {
            $list->whereNotNull('ip_address')->where('ip_address', '!=', '');
        }

        if ($r->reseller !== 'all') {
            if ($r->pop !== 'all') {
                $pop_ids = $pop->where('id', $r->pop)->where('reseller_id', $r->reseller)->pluck('id')->toArray();
                $list->whereIn('pop_id', $pop_ids);
            } else {
                $pop_ids = $pop->where('reseller_id', $r->reseller)->pluck('id')->toArray();
                $list->whereIn('pop_id', $pop_ids);
            }

            $reseller_sme_getway = ResellerSmsGetway::with('reseller')->where('reseller_id', $r->reseller)->first();
            if ($reseller_sme_getway) {
                $customer_recharge = collect(json_decode($reseller_sme_getway->sms_formate))->where('sms_type', 'other_reseller_customer_recharge')->first();
                if ($customer_recharge && ($customer_recharge->send_sms ?? '') === 'Yes') {
                    $reseller_customer_sms = true;
                }
            }

            $sms_setting = json_decode(getSmsMessage()->where('tamplate_name', 'reseller_customer_recharge')->first()->tamplate_body, true);
            if (is_array($sms_setting) && ($sms_setting['sendsms'] ?? 'No') === 'Yes') {
                $reseller_customer_sms = true;
            }
        }

        $list->when(filled($r->mother_package), function ($query) use ($r) {
            return $query->where('package_id', $r->mother_package);
        });

        $packages = '';
        $billingCycle = '';

        if ($r->finance == 'withDue') {
            $list->whereHas('customerAccount', function ($q) use ($r) {
                $q->where('dueAmount', '>', 0);
            });
        } else if ($r->finance == 'withoutDue') {
            $list->whereHas('customerAccount', function ($q) use ($r) {
                $q->where('dueAmount', '<=', 0);
            });
        }

        if ($r->experity_check == 'vip') {
            $list->where('experity_check', 'No');
        } else if ($r->experity_check == 'non_vip') {
            $list->where('experity_check', 'Yes');
        }

        if ($r->usage) {
            $total_mb = $r->usage * 1024;
            $list->whereRaw('(IFNULL(down_bandwidth,0) + IFNULL(up_bandwidth,0)) >= ?', [$total_mb]);
        }


        if ($r->remote_access == 'on') {
            $list->where('remote_access', 'Yes');
        }



        $list = $list->get();

        $r->session()->put('customer_search_result_ids', $list->pluck('id')->toArray());

        if (auth()->user()->hasRole(['Sub Reseller', 'Sub Reseller Technician'])) {
            if ($list && $list->count()) {
                foreach ($list->groupBy('subpack.name')->map->count() as $key => $value) {
                    $packages .= "<span class=\"badge badge-info\" style=\"margin: 4px; padding: 6px 10px; display: inline-block;\"> $key : $value </span> ";
                }

                foreach ($list->groupBy('billing_cycle')->map->count()->sortKeys() as $key => $value) {
                    $billingCycle .= "<span class=\"badge badge-success\" style=\"margin: 4px; padding: 6px 10px; display: inline-block;\">$key : $value</span>";
                }
            }
        } else {
            if ($list && $list->count()) {
                foreach ($list->groupBy('packages.package_name')->map->count() as $key => $value) {
                    $packages .= "<span class=\"badge badge-info\" style=\"margin: 4px; padding: 6px 10px; display: inline-block;\"> $key : $value </span> ";
                }

                foreach ($list->groupBy('billing_cycle')->map->count()->sortKeys() as $key => $value) {
                    $billingCycle .= "<span class=\"badge badge-success\" style=\"margin: 4px; padding: 6px 10px; display: inline-block;\">$key : $value</span>";
                }
            }
        }



        if ($list == null) {
            return '<p class="text-center bg-gray-50 p-2">No User Found</p>';
        } else {
            $limit = Auth::user()->minimum_recharge_day;

            return view('clients.search.search', [
                'list' => $list,
                'reseller' => $reseller,
                'pop' => $pop,
                'packages_count' =>  $packages,
                'billing_count' =>  $billingCycle,
                'status' => $this->searchResultStatus($list),
                'confday' => config('app.billing_cycle'),
                'reseller_customer_sms' => $reseller_customer_sms,
                'minimum_recharge' => $limit,
                'package_limit' => Packages::find($r->mother_package)->limite_quantity ?? 0,
            ]);
        }
    }

    public function searchResultStatus($list)
    {
        $status  = $list->groupBy('clients_status')->map->count();

        $st = 'Total User: ' . $list->count();

        if (isset($status['active'])) {
            $st .= ' | Active User: ' . $status['active'];
        } else {
            $st .= ' | Active User: 0';
        }

        if (isset($status['deactive'])) {
            $st .= ' | Deactive User: ' . $status['deactive'];
        } else {
            $st .= ' | Deactive User: 0';
        }

        if (isset($status['disable'])) {
            $st .= ' | Disable User: ' . $status['disable'];
        } else {
            $st .= ' | Disable User: 0';
        }

        if (isset($status['expired'])) {
            $st .= ' | Expired User: ' . $status['expired'];
        } else {
            $st .= ' | Expired User: 0';
        }
        return $st;
    }

    public function allClientDownloadExcel(Request $r)
    {
        if (!auth()->user()->can('allClientDownloadExcel')) {
            abort(403, 'Unauthorized action.');
        }
        $pop = Pop::conditionalList();
        $pop_ids = $pop->pluck('id')->toArray();

        // $list = Client::list()->whereIn('pop_id', $pop_ids);

        $list = Client::with([
            'packages:id,package_name,package_rate,client_payment_amount',
            'customerAccount',
            'pops:id,popname,reseller_id,bill_generate,subreseller,experity_check',
            'subpack:id,name,rate,client_payment_amount',
            'clientsinfo:id,client_id,clients_name,contact_no,area,flat_no,building_no,road_no,block_sector,otc,owner_name,owner_number,remarks,dob,cable_type,road_name,otc_due,other_contact,box,joinDate,client_latitude,client_longitude,building_name,building_no,cat_length'
        ])
            ->select([
                'crm_status',
                'id',
                'down_bandwidth',
                'up_bandwidth',
                'pop_id',
                'billing_cycle',
                'package_id',
                'userid',
                'password',
                'ip_address',
                'expire_date',
                'payment_dadeline',
                'created_by',
                'client_approval',
                'created_at',
                'clients_status',
                'sub_package_id',
                'customer_id',
                'marketed_by',
                'deleted_at',
                'parmanent_discount',
                'ignoreMKCheck',
                'required_cable',
                'customer_code',
                'slug',
                'client_payment_amount',
                'un_recharge_amount',
                'billing_start_date',
                'auto_deactive',
                'isStatic',
                'experity_check',
                'remote_access'
            ])
            ->where('client_approval', 'approved')
            ->whereIn('pop_id', userPops()->pluck('id'));


        $reseller_customer_sms = false;
        if ($r->value != '') {
            $list->where('userid', 'like', '%' . $r->value . '%');
        } else {

            $list->when($r->contact != null, function ($q) use ($r) {

                return $q->whereHas('clientsinfo', function ($q) use ($r) {
                    $q->where('contact_no', 'like', '%' . $r->contact . '%');
                });
            });

            $list->when($r->reseller != null && $r->pop != null && $r->area != null, function ($q) use ($r) {

                return $q->where('pop_id', $r->pop)
                    ->whereHas('clientsinfo', function ($q) use ($r) {
                        $q->where('area', 'like', '%' . $r->area . '%');
                    });
            });

            $list->when($r->box != null, function ($q) use ($r) {

                return $q->whereHas('clientsinfo', function ($q) use ($r) {
                    $q->where('box', 'like', '%' . $r->box . '%');
                });
            });

            $list->when($r->cable_type != null, function ($q) use ($r) {

                return $q->whereHas('clientsinfo', function ($q) use ($r) {
                    $q->where('cable_type', 'like', '%' . $r->cable_type . '%');
                });
            });

            if ($r->user_id != null) {
                $list->where('id', $r->user_id);
            }

            if ($r->code != null) {
                $list->where('customer_code', $r->code);
            }

            if ($r->name != null) {
                $list->whereHas('clientsinfo', function ($q) use ($r) {
                    $q->where('clients_name', 'like', '%' . $r->name . '%');
                });
            }

            if ($r->billing_cycle != 'all') {
                $list->where('billing_cycle', $r->billing_cycle);
            }



            if ($r->user_state) {
                $list->where('clients_status', $r->user_state);
            }

            if ($r->expire_date) {
                // dd($r->expire_date);
                $list->whereDate('expire_date', Carbon::parse($r->expire_date)->format('Y-m-d'));
            }


            if ($r->expire_date_from && $r->expire_date_to) {
                $start = Carbon::parse($r->expire_date_from)->startOfDay();
                $end   = Carbon::parse($r->expire_date_to)->endOfDay();

                $list->whereBetween('expire_date', [$start, $end]);
            } else if ($r->expire_date_from) {
                $list->whereDate('expire_date', Carbon::parse($r->expire_date_from)->format('Y-m-d'));
            } else if ($r->expire_date_to) {
                $list->whereDate('expire_date', Carbon::parse($r->expire_date_to)->format('Y-m-d'));
            }

            if ($r->static_ip) {
                $list->where('ip_address', $r->static_ip);
            }

            if ($r->check_ip == "on") {
                $list->whereNotNull('ip_address')
                    ->where('ip_address', '!=', '');
            }
            // if ($r->recharge_state != 'all') {
            //     $list->where('clients_status', $r->recharge_state);
            // }



            if ($r->reseller != 'all') {

                if ($r->pop != 'all') {
                    $pop_ids = $pop->where('id', $r->pop)->where('reseller_id', $r->reseller)->pluck('id')->toArray();
                    $list->whereIn('pop_id', $pop_ids);
                } else {
                    $pop_ids = $pop->where('reseller_id', $r->reseller)->pluck('id')->toArray();
                    $list->whereIn('pop_id', $pop_ids);
                }

                $reseller_sme_getway = ResellerSmsGetway::with('reseller')->where('reseller_id', $r->reseller)->first();
                if ($reseller_sme_getway) {
                    $customer_recharge = collect(json_decode($reseller_sme_getway->sms_formate))->where('sms_type', 'other_reseller_customer_recharge')->first();
                    if ($customer_recharge->send_sms == "Yes") {
                        $reseller_customer_sms = true;
                    }
                }

                $sms_setting = json_decode(getSmsMessage()->where('tamplate_name', 'reseller_customer_recharge')->first()->tamplate_body, true);
                if ($sms_setting["sendsms"] == "Yes") {
                    $reseller_customer_sms = true;
                }
            }




            $list->when($r->mother_package != null, function ($q) use ($r) {

                return $q->where('package_id', $r->mother_package);
            });
        }

        $packages = '';

        if ($r->finance == 'withDue') {
            $list->whereHas('customerAccount', function ($q) use ($r) {
                $q->where('dueAmount', '>', 0);
            });
        } else if ($r->finance == 'withoutDue') {
            $list->whereHas('customerAccount', function ($q) use ($r) {
                $q->where('dueAmount', '<=', 0);
            });
        }

        $usage = false;

        if ($r->usage) {
            $usage = true;
            $total_mb = $r->usage * 1024;
            $list->whereRaw('(IFNULL(down_bandwidth,0) + IFNULL(up_bandwidth,0)) >= ?', [$total_mb]);
        }

        if ($r->remote_access == 'on') {
            $list->where('remote_access', 'Yes');
        }



        $result = new Collection();

        $list->orderBy('id')->chunk(500, function ($list) use (&$result) {
            foreach ($list as $l) {
                $result->push((object)$l);
            }
        });

        return Excel::download(new SearchResultExport($result, $usage), 'customers.csv');
    }


    public function clientSearchReportPrint(Request $request)
    {

        $r = $request->session()->get('customer_search_result');
        $usage = false;

        if ($r) {
            $reseller = Reseller::resellerList();
            $pop = Pop::poplist();

            $resultIds = $request->session()->get('customer_search_result_ids', null);
            $useStoredIds = $request->session()->has('customer_search_result_ids');

            if ($useStoredIds) {
                $usage = !empty($r['usage']);
                $ids = is_array($resultIds) ? $resultIds : [];

                if (empty($ids)) {
                    $result = new Collection();
                } else {
                    $result = Client::with([
                        'packages:id,package_name,package_rate,client_payment_amount',
                        'customerAccount',
                        'pops:id,popname,reseller_id,bill_generate,subreseller,experity_check',
                        'subpack:id,name,rate,client_payment_amount',
                        'clientsinfo:id,client_id,clients_name,contact_no,area,flat_no,building_no,road_no,block_sector,otc,owner_name,owner_number,remarks,dob,cable_type,road_name,otc_due,other_contact,box,joinDate,client_latitude,client_longitude,building_name,building_no,cat_length'
                    ])
                        ->select([
                            'crm_status',
                            'id',
                            'down_bandwidth',
                            'up_bandwidth',
                            'pop_id',
                            'billing_cycle',
                            'package_id',
                            'userid',
                            'password',
                            'ip_address',
                            'expire_date',
                            'payment_dadeline',
                            'created_by',
                            'client_approval',
                            'created_at',
                            'clients_status',
                            'sub_package_id',
                            'customer_id',
                            'marketed_by',
                            'deleted_at',
                            'parmanent_discount',
                            'ignoreMKCheck',
                            'required_cable',
                            'customer_code',
                            'slug',
                            'client_payment_amount',
                            'un_recharge_amount',
                            'billing_start_date',
                            'auto_deactive',
                            'isStatic',
                            'experity_check',
                            'customer_account_balance_reseller',
                            'mac'
                        ])
                        ->where('client_approval', 'approved')
                        ->whereIn('id', $ids)
                        ->orderBy('id')
                        ->get();
                }
            } else {
                $pop_ids = $pop->pluck('id')->toArray();
                $list = Client::list();

                if ($r['value'] != '') {
                    $list->where('userid', 'like', '%' . $r['value'] . '%')->whereIn('pop_id', $pop_ids);
                } else {

                    $list->when($r['contact'] != null, function ($q) use ($r) {

                        return $q->whereHas('clientsinfo', function ($q) use ($r) {
                            $q->where('contact_no', 'like', '%' . $r['contact'] . '%');
                        });
                    });


                    if ($r['billing_cycle'] != 'all') {
                        $list->where('billing_cycle', $r['billing_cycle']);
                    }

                    if (isset($r['user_state']) && $r['user_state'] !== 'all') {
                        $list->where('clients_status', $r['user_state']);
                    }

                    if (isset($r['area'])) {
                        $list->whereHas('clientsinfo', function ($q) use ($r) {
                            $q->where('area',  $r['area']);
                        });
                    }

                    if (isset($r['user_id'])) {
                        $list->where('id', $r['user_id']);
                    }

                    if (isset($r['static_ip'])) {
                        $list->where('ip_address', $r['static_ip']);
                    }

                    if (isset($r['check_ip']) && $r['check_ip'] === 'on') {
                        $list->whereNotNull('ip_address')
                            ->where('ip_address', '!=', '');
                    }


                    if ($r['reseller'] != 'all') {

                        if ($r['pop'] != 'all') {
                            $pop_ids = $pop->where('id', $r['pop'])->where('reseller_id', $r['reseller'])->pluck('id')->toArray();
                            $list->whereIn('pop_id', $pop_ids);
                        } else {
                            $pop_ids = $pop->where('reseller_id', $r['reseller'])->pluck('id')->toArray();
                            $list->whereIn('pop_id', $pop_ids);
                        }
                    }


                    if ($r['expire_date_from'] && $r['expire_date_to']) {
                        $start = Carbon::parse($r['expire_date_from'])->startOfDay();
                        $end   = Carbon::parse($r['expire_date_to'])->endOfDay();

                        $list->whereBetween('expire_date', [$start, $end]);
                    } else if ($r['expire_date_from']) {
                        $list->whereDate('expire_date', Carbon::parse($r['expire_date_from'])->format('Y-m-d'));
                    } else if ($r['expire_date_to']) {
                        $list->whereDate('expire_date', Carbon::parse($r['expire_date_to'])->format('Y-m-d'));
                    }

                    if (isset($r['mother_package'])) {
                        $list->when($r['mother_package'] != null, function ($q) use ($r) {
                            return $q->where('package_id', $r['mother_package']);
                        });
                    }

                    if ($r['finance'] == 'withDue') {
                        $list->whereHas('customerAccount', function ($q) use ($r) {
                            $q->where('dueAmount', '>', 0);
                        });
                    } else if ($r['finance'] == 'withoutDue') {
                        $list->whereHas('customerAccount', function ($q) use ($r) {
                            $q->where('dueAmount', '<=', 0);
                        });
                    }

                    if (isset($r['usage'])) {
                        $usage = true;
                        $total_mb = $r['usage'] * 1024;
                        $list->whereRaw('(IFNULL(down_bandwidth,0) + IFNULL(up_bandwidth,0)) >= ?', [$total_mb]);
                    }

                    if (isset($r['remote_access']) && $r['remote_access'] === 'on') {
                        $list->where('remote_access', 'Yes');
                    }
                }

                $result = new Collection();
                $list->orderBy('id')->chunk(500, function ($chunk) use (&$result) {
                    foreach ($chunk as $l) {
                        $result->push((object)$l);
                    }
                });
            }

            $packages = '';

            if ($request->excel == "yes") {
                $data = $result;
                return Excel::download(new SearchResultExport($data, $usage), 'customers.csv');
            } else {

                if ($result->isEmpty()) {
                    return '<p class="text-center bg-gray-50 p-2">No User Found</p>';
                }

                return view('clients.search.print', [
                    'list' => $result,
                    'reseller' => $reseller,
                    'pop' => $pop,
                    'packages_count' => $packages,
                    'price' => $request->price,

                ]);
            }
        } else {
            return redirect()->route('clientSearch');
        }
    }


    public function bulkExpariyDateExtended()
    {

        $list = Reseller::resellerList()->where('reseller_type', 'own')->get();

        $confday = config('app.billing_cycle');
        return view('clients.bulkExpaireExtended.search', [
            'page_title' => 'Client Search',
            'reseller' => $list,
            'packages' => Packages::select('id', 'package_name')->get(),
            'confday' => config('app.billing_cycle')
        ]);
    }

    public function bulkExpariyDateExtendedSearch(Request $request)
    {
        $all_own_reseller = Reseller::resellerList()->where('reseller_type', 'own')->pluck('id')->toArray();
        $all_pops = Pop::conditionalList()->whereIn('reseller_id', $all_own_reseller)->pluck('id')->toArray();
        $clients = Client::list()->with('customerAccount')->whereHas('customerAccount', function ($query) {
            $query->where('dueAmount', '>', 0);
        })->whereIn('pop_id', $all_pops);
        $company_information = CompanyInformation::latest()->first();


        if ($request->reseller != 'all' && $request->pop == 'all') {
            $pops = Pop::conditionalList()->whereIn('reseller_id', $request->reseller)->pluck('id')->toArray();
            $clients = $clients->whereIn('pop_id', $pops);

            if ($request->billing_cycle == 'all') {

                if ($request->expire_date != null) {
                    $exp_date = date('Y-m-d', strtotime($request->expire_date));
                    $clients->where('expire_date', 'like', $exp_date . '%');
                }

                $data = [
                    'clients' => $clients->get(),
                    'company_information' => $company_information
                ];
                return view('clients.bulkExpaireExtended.result', $data);
            } else {
                $clients = $clients->where('billing_cycle', $request->billing_cycle);
                $data = [
                    'clients' => $clients->get(),
                    'company_information' => $company_information
                ];
                return view('clients.bulkExpaireExtended.result', $data);
            }
        } else if ($request->reseller != 'all' && $request->pop != 'all') {
            $clients = $clients->where('pop_id', $request->pop);

            if ($request->billing_cycle == 'all') {

                if ($request->expire_date != null) {
                    $exp_date = date('Y-m-d', strtotime($request->expire_date));
                    $clients->where('expire_date', 'like', $exp_date . '%');
                }

                $data = [
                    'clients' => $clients->get(),
                    'company_information' => $company_information
                ];

                return view('clients.bulkExpaireExtended.result', $data);
            } else {
                $clients = $clients->where('billing_cycle', $request->billing_cycle);
                $data = [
                    'clients' => $clients->get(),
                    'company_information' => $company_information
                ];
                return view('clients.bulkExpaireExtended.result', $data);
            }
        } else {

            if ($request->billing_cycle == 'all') {

                if ($request->expire_date != null) {
                    $exp_date = date('Y-m-d', strtotime($request->expire_date));
                    $clients->where('expire_date', 'like', $exp_date . '%');
                }

                $data = [
                    'clients' => $clients->get(),
                    'company_information' => $company_information
                ];
                return view('clients.bulkExpaireExtended.result', $data);
            } else {
                $clients = $clients->where('billing_cycle', $request->billing_cycle);
                $data = [
                    'clients' => $clients->get(),
                    'company_information' => $company_information
                ];
                return view('clients.bulkExpaireExtended.result', $data);
            }
        }
    }

    public function CustomerPaymentDadeline(Request $request)
    {
        if ($request->ids == null) {
            Toastr::error("Please Select At Least One Client");
            return redirect()->back();
        }

        $company_information = CompanyInformation::latest()->first();

        if ($company_information->max_extended_day < $request->day || $request->day <= 0) {
            Toastr::error("Please Enter Valid Day");
            return redirect()->back();
        }

        Client::whereIn('id', $request->ids)->update(['payment_dadeline' => $request->day]);

        $clients = Client::with('clientsinfo')->whereIn('id', $request->ids)->get();
        foreach ($clients as $client) {
            try {
                if ($client->clientsinfo->contact_no != null) {
                    sendSmsAfterExtended($client->id, $request->day);
                }
            } catch (\Exception $e) {
            }

            try {
                if (checkAPI()) {
                    (new SyncWithMk)->syncSingleClient($client->id);
                } else {
                    (new RadiusClientSync)->syncSingleRadiusClient($client->id);
                }
            } catch (\Exception $e) {
            }
        }



        Toastr::success("Bulk Expariy Date Extended Successfull");
        return redirect()->route('bulk-expariy-date-extended');
    }

    public function recentClientExport(Request $request)
    {
        $r = $request->session()->get('newClientSearchReport');

        if ($r) {

            if (($r['from_date'] != null) && ($r['to_date'] != null)) {

                $from = $r['from_date'] == '' ? today() : Carbon::parse($r['from_date'])->format('Y-m-d 00:00:00');
                $to   = $r['to_date'] == '' ? today() : Carbon::parse($r['to_date'])->format('Y-m-d 23:59:59');
                $list = Client::list()->whereBetween('created_at', [$from, $to]);


                if ($r['reseller'] != 'null' && $r['pop'] == 'all') {

                    if($r['reseller'] == 'all'){
                        $pop_ids = userPops()->pluck('id')->toArray();
                        $list->whereIn('pop_id', $pop_ids);
                    }else{
                        $pop_ids = userPops()->whereIn('reseller_id', userResellers()->where('id', $r['reseller'])->pluck('id'))->pluck('id')->toArray();
                        $list->whereIn('pop_id', $pop_ids);
                    }

                } elseif ($r['reseller'] != 'null' && $r['pop'] != 'null') {
                    $list->where('pop_id', $r['pop']);
                }

                // Handle single or multiple marketed_by values from session data
                $marketedBy = (array)($r['marketed_by'] ?? []);
                $marketedBy = array_filter($marketedBy, function ($value) {
                    return $value !== 'null' && $value !== null && $value !== '';
                });
                if (!empty($marketedBy)) {
                    $list->whereIn('marketed_by', $marketedBy);
                }
                $free_day_to_create_customer = false;
                if(globalPermission('free-day-to-create-customer') && isset($r['free_day_to_create_customer']) && $r['free_day_to_create_customer'] == 1){
                    $list->where('free_day_to_create_customer', '>=', 0);
                    $free_day_to_create_customer = true;
                }

                // Handle single or multiple source_name values from session data
                $sourceNames = (array)($r['source_name'] ?? []);
                $sourceNames = array_filter($sourceNames, function ($value) {
                    return $value !== 'null' && $value !== null && $value !== '';
                });
                if (!empty($sourceNames)) {
                    $list->whereHas('clientsinfo', function ($q) use ($sourceNames) {
                        $q->whereIn('source_name', $sourceNames);
                    });
                }

                $result = new Collection();
                $list->orderBy('id')->chunk(500, function ($list) use (&$result) {
                    foreach ($list as $l) {
                        $result->push((object)$l);
                    }
                });

                $data[] = '';
                $data = $result;
                return Excel::download(new SearchResultExport($data, false, $free_day_to_create_customer), 'customers.csv');
            }
        }
    }

    public function getCustomerOnLineStatus($clientUserId)
    {

        $user = Client::with("pop.nas")->where('id', '=', $clientUserId)->get();


        $clinetName = $user[0]->userid;


        $mikrotick_info = $user[0]->pop->nas;
        $mk = new Mikrotik($mikrotick_info->nasname, $mikrotick_info->mikrotick_user, $mikrotick_info->mikrotick_user_password, $mikrotick_info->mikrotick_port ? (int)$mikrotick_info->mikrotick_port : 8728);

        $routerIP = $mikrotick_info->nasname;
        $username = $mikrotick_info->mikrotick_user;

        $password = $mikrotick_info->mikrotick_user_password;
        $mikrotikPort = (int)$mikrotick_info->mikrotick_port;

        $client = new routerClient([
            'host'     => $routerIP,
            'user'     => $username,
            'pass'     => $password,
            'port'     => $mikrotikPort ? $mikrotikPort : 8728,
            'ssl'      => false, // Set to true if using SSL
        ]);


        $query = (new Query('/ppp/active/print'))
            ->where('name', $clinetName);

        $response = $client->query($query)->read();



        if (count($response) !== 0) {

            return response()->json([
                'ip_address' => $response[0]['address']
            ]);
        } else {

            return response()->json([
                'ip_address' => "Offline"
            ]);
        }
    }
}
