<?php

namespace App\Services;

use Carbon\Carbon;
use App\Models\Pop;
use App\Models\Client;
use App\Models\Balance;
use App\Models\Packages;
use App\Models\Radcheck;
use App\Models\Reseller;
use App\Models\SubPackage;
use App\Models\Clientsinfo;
use App\Models\BillGenerate;
use App\Services\ExpireCheck;
use App\Models\CustomerAccount;
use App\Services\PriceCalculate;
use Illuminate\Support\Facades\DB;
use Brian2694\Toastr\Facades\Toastr;
use Illuminate\Support\Facades\Auth;
use App\Models\ReselleBalanceLogReport;
use App\Http\Controllers\BillGenerateController;
use App\Http\Controllers\Clients\IdEnableDisableController;
use App\Models\ClientSource;
use Exception;
use Illuminate\Support\Str;
use Intervention\Image\Facades\Image;
use Illuminate\Support\Facades\Storage;

class ClientServices
{

    static function clientUpdate($request, $client)
    {

        $pop = Pop::with('nas')->find($request->pop_id);
        $nas = $pop->nas->nasname;

        $client->pop_id = $request->pop_id;
        $client->userid = $request->isStatic ? $request->static_ip :  $request->userid;
        $client->mac    = $request->mac;
        $client->password = $request->password;
        // if ($request->payment_dadeline) {
        $client->payment_dadeline = $request->payment_dadeline;
        // }
        $client->parmanent_discount = $request->parmanent_discount;
        $client->apply_bandwidth_limit = $request->apply_bandwidth_limit ? 1 : 0;
        $client->bandwidth_limit = $request->bandwidth_limit;
        $client->ethernet_port = $request->ethernet_port;
        $client->distribution_point = $request->distribution_point;
        $client->connection_type  = $request->connection_type;
        $client->customer_type  = $request->customer_type;
        $client->customer_code  = $request->customer_code;
        $client->ipv6 = $request->ipv6 ?? null;




        if ($pop->bill_generate == 'yes' && !checkAPI()) {

            if (Carbon::parse($client->expire_date)->addDay($request->payment_dadeline) > today()) {

                DB::table('radcheck')
                    ->where('username', $client->userid)
                    ->update([
                        'op'        => ':='
                    ]);
            } else {


                if ($pop->experity_check == 'No') {
                    DB::table('radcheck')
                        ->where('username', $client->userid)
                        ->update([
                            'op'        => '!='
                        ]);
                }
            }
        }




        if (Client::where('customer_id', $request->customer_id)->where('id', '!=', $client->id)->count() < 1) {
            if ($request->customer_id) {
                $client->customer_id = $request->customer_id;
            }
        } else {
            Toastr::error('Duplicate Customer ID');
            return redirect()->back();
        }
        if ($request->required_cable) {

            $client->required_cable = $request->required_cable ?? 0;
        }
        $client->ip_address = $request->ip_address;
        $client->ignoreMKCheck  = $request->ignoreMkCheck ? 1 : 0;
        $client->marketed_by  = $request->marketed_by;
        $client->box_id  = $request->box_id;
        if ($request->is_free == 1) {
            $expire_date = Carbon::parse(date(1 . '-M-' . 2100))->format('d-M-Y');
            $client->expire_date = date('Y-m-d H:i:s', strtotime($expire_date));
            $client->billing_cycle = date('d', strtotime($expire_date));
            $client->clients_status = "active";
        }
        if ($request->is_free == 0 && $client->is_free == 1) {
            $expire_date = Carbon::parse(today())->format('d-M-Y');
            $client->expire_date = date('Y-m-d H:i:s', strtotime($expire_date));
            $client->billing_cycle = date('d', strtotime($expire_date));
        }
        $client->is_free  = $request->is_free;

        $client->remote_access = $request->remote_access;
        $client->remote_access_username = $request->remote_access_username;
        $client->remote_access_password = $request->remote_access_password;
        $client->remote_access_port = $request->remote_access_port;

        if (globalPermission('manage-line-man')) {
            $client->line_man_id = $request->line_man_id ?? null;
        }



        $client->client_payment_amount = globalPermission('setClientPaymentAmountToClient')
            ? (($pop->bill_generate == 'no')
                ? $request->client_payment_amount
                : 0)
            : 0;

        if ($request->source_name) {
            $clientSorce = ClientSource::find($request->source_name);
            if ($clientSorce->name == 'Existing Client Reference') {
                $client->client_id = $request->client_id;
            }else{
                $client->client_id = null;
            }
        }

        if(auth()->user()->hasPermissionTo('change-marketing-employee-in-client-edit')){
            $client->marketing_employee_id = $request->marketing_employee_id;
        }

        $client->save();

        if (!checkAPI()) {

            if ($client->client_approval == 'approved' && $client->ip_address != null && filter_var($client->ip_address, FILTER_VALIDATE_IP)) {


                DB::table('radreply')->insert([
                    'username'  => $client->userid,
                    'attribute' => 'Framed-IP-Address',
                    'op'        => ':=',
                    'value'     => $client->ip_address
                ]);

                DB::table('radreply')->insert([
                    'username'  => $client->userid,
                    'attribute' => 'Service-Type',
                    'op'        => ':=',
                    'value'     => 'Framed-User'
                ]);
            }

            if (!filter_var($client->ip_address, FILTER_VALIDATE_IP)) {
                DB::table('radreply')->where('username', $client->userid)->delete();
            }
        }




        DB::table('clientsinfo')
            ->updateOrInsert(
                [
                    'client_id' => $client->id
                ],
                [
                    'clients_name'  => $request->client_name,
                    'father_name'   => $request->father_name,
                    'mother_name'   => $request->mother_name,
                    'flat_no'       => $request->flat_no,
                    'building_name' => $request->building_name,
                    'occupation'    => $request->occupation,
                    'company_name'  => $request->company_name,
                    'designation'   => $request->designation,
                    'building_no'   => $request->building_no,
                    'road_name'     => $request->road_name ?? '',
                    'road_no'       => $request->road_no ?? '',
                    'block_sector'  => $request->block_sector ?? '',
                    'area'          => $request->area ?? '',
                    'division'      => $request->division ?? '',
                    'district'      => $request->district ?? '',
                    'upazila'         => $request->upazila ?? '',
                    'thana'         => $request->thana ?? '',
                    'contact_no'    => $request->contact_no,
                    'national_id'   => $request->national_id,
                    'email'         => $request->email,
                    'passport_no'   => $request->passport_no,
                    'owner_name'    => $request->owner_name,
                    'owner_number'  => $request->owner_contact,
                    'olt_pon'       => $request->olt_pon,
                    // 'otc'           => $request->otc,
                    'remarks'       => $request->remarks,
                    'dob'           => $request->dob,
                    'cable_type'    => $request->cable_type,
                    'other_contact' =>  $request->other_contact,
                    'box'           => $request->box,
                    'joinDate'      => $request->joinDate,
                    'client_latitude'      => $request->client_latitude,
                    'client_longitude'      => $request->client_longitude,
                    'cat_type'          => $request->cat_type,
                    'cat_length'        => $request->cat_length,
                    'olt_no'            => $request->olt_no,
                    'pon_port'          => $request->pon_port,
                    'onu_serial'        => $request->onu_serial,
                    'from_cable'        => $request->from_cable,
                    'to_cable'          => $request->to_cable,
                    'from_cable2'        => $request->from_cable2,
                    'to_cable2'          => $request->to_cable2,
                    'cable_id'          => $request->cable_id,
                    'customer_package_name' => $request->customer_package_name,
                    'btrc_package_bandwidth' => $request->btrc_package_bandwidth,
                    'source_name' => $request->source_name,
                    "map_box_id" => $request->map_box_id

                ]

            );

        $clientsinfo = Clientsinfo::where('client_id', $client->id)->first();

        if ($request->customer_agrement != null) {
            $image = $request->file('customer_agrement');
            $fileName = time() . '.' . 'jpg';

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'customer_agrement/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }


            // Save filename to database
            $clientsinfo->customer_agrement = $fileName;
        }
        if ($request->customer_profile_picture != null) {
            $image = $request->file('customer_profile_picture');
            $fileName = time() . '.' . 'jpg';

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'customer_profile_picture/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->customer_profile_picture = $fileName;
        }

        if ($request->hasFile('profilePicture')) {
            $image = $request->file('profilePicture');
            $fileName = time() . '.' . 'jpg';

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'profilePicture/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->profilePicture = $fileName;
        }

        if ($request->picture != null) {
            $image = $request->file('picture');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'picture/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }


            // Save filename to database
            $clientsinfo->picture = $fileName;
        }

        if ($request->client_door != null) {
            $image = $request->file('client_door');
            $fileName = time() . '.' . 'jpg';

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'client_door/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }


            // Save filename to database
            $clientsinfo->client_door = $fileName;
        }

        if ($request->onu_router_info != null) {
            $image = $request->file('onu_router_info');
            $fileName = time() . '.' . 'jpg';

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'onu_router_info/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->onu_router_info = $fileName;
        }
        if (checkSettings('national_id_picture') == 'enable') {
            if ($request->nid_image != null) {
                $image = $request->file('nid_image');
                $fileName = time() . '.' . 'jpg';
                $img = Image::make($image->getRealPath());
                $quality = 90;
                $maxSize = 50 * 1024; // 50 KB
                $encoded = (string) $img->encode('jpg', $quality);
                // Reduce quality until size is under 50 KB or quality too low
                while (strlen($encoded) > $maxSize && $quality > 10) {
                    $quality -= 5;
                    $encoded = (string) $img->encode('jpg', $quality);
                }
                // Save image to storage
                $path = 'nationalId/' . $fileName;
                // Save to disk (S3 or local)
                $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
                Storage::disk($disk)->put($path, $encoded);
                // If stored in S3 and you want it to be public
                if ($disk === 's3') {
                    Storage::disk('s3')->setVisibility($path, 'public');
                }
                // Save filename to database
                $clientsinfo->nid_image = $fileName;
            }
        }

        $clientsinfo->save();

        if (!checkAPI()) {

            ClientServices::updateRadiusTable($request->userid, $request->password, $client, $nas, $request->olduserid);
        }



        return true;
    }



    static function updateRadiusTable($username, $password, $client, $nas, $oldusername = '')
    {

        $result = DB::table('radcheck')->where('username', $oldusername)->get();

        if (!empty($client->ip_address)) {

            DB::table('radreply')
                ->where('username', $client->userid)
                ->where('attribute', 'Framed-IP-Address')
                ->update(['op' => ':=', 'value' => $client->ip_address]);

            DB::table('radreply')
                ->where('username', $client->userid)
                ->where('attribute', 'Service-Type')
                ->update(['op' => ':=', 'value' => 'Framed-User']);
        }



        if (!empty($result)) {

            DB::table('radcheck')
                ->where('username', $oldusername)
                ->where('attribute', 'NAS-IP-Address')
                ->update(['value' => $nas]);


            DB::table('radcheck')
                ->where('username', $oldusername)
                ->where('attribute', 'Cleartext-Password')
                ->update(['value' => $password]);

            DB::table('radcheck')
                ->where('username', $oldusername)
                ->where('attribute', 'Cleartext-Password')
                ->update(['username' => $username]);

            DB::table('radcheck')
                ->where('username', $oldusername)
                ->where('attribute', 'NAS-IP-Address')
                ->update(['username' => $username]);

            DB::table('radusergroup')
                ->where('username', $oldusername)
                ->update(['username' => $username]);
        } else {

            $user = $client;

            if ($user->clients_status == 'active') {

                $package = Packages::find($user->package_id);

                DB::table('radcheck')->insert([
                    'username' => $username,
                    'attribute' => 'Cleartext-Password',
                    'op'        => ':=',
                    'value'     => $password
                ]);

                DB::table('radcheck')->insert([
                    'username' => $username,
                    'attribute' => 'NAS-IP-Address',
                    'op'        => '==',
                    'value'     => $nas
                ]);

                DB::table('radusergroup')
                    ->insert([
                        'username'  => $username,
                        'groupname' => $package->id,
                        'priority' => 1
                    ]);
            }
        }

        $pop = Pop::find($client->pop_id);



        if ($pop->experity_check == 'Yes') {

            if ($client->expire_date > today()) {


                DB::table('radcheck')
                    ->where('username', $client->userid)
                    ->where('attribute', 'Cleartext-Password')
                    ->update(['op' => ':=']);


                DB::table('radcheck')
                    ->where('username', $client->userid)
                    ->where('attribute', 'NAS-IP-Address')
                    ->update(['op' => ':=']);
            }
        }
    }

    static function clientCreate($request, $expire)
    {
        $ex =  date('Y-m-d H:i:s', strtotime($expire['exp_date']));
        $cable = abs($request->from_cable - $request->to_cable);

        $date = Carbon::now();
        $code = $date->format('YmdHis') . $date->format('v') . Str::random(6);

        $client =  Client::create([
            'pop_id'            => $request->pop_id,
            'userid'            => $request->isStatic ? $request->static_ip :  $request->userid,
            'package_id'        => $expire['reseller_package_id'],
            'sub_package_id'    => $expire['sub_reseller_package_id'] ?? null,
            'mac'               => $request->mac,
            'ip_address'        => $request->static_ip,
            'password'          => $request->isStatic ? "RandomPassword" : $request->password,
            'billing_cycle'     => $request->billing_cycle,
            'created_by'        => $request->created_by,
            'required_cable'    => $cable,
            'expire_date'       => $ex,
            'created_by'        => Auth::user()->id,
            'marketed_by'       => $request->marketed_by ?? 0,
            'ignoreMKCheck'     => $request->ignoreMkCheck ? 1 : 0,
            'apply_bandwidth_limit'     => $request->apply_bandwidth_limit ? 1 : 0,
            'bandwidth_limit'     => $request->bandwidth_limit,
            'distribution_point'     => $request->distribution_point,
            'connection_type'     => $request->connection_type,
            'customer_type'     => $request->customer_type,
            'ethernet_port'     => $request->ethernet_port,
            'isStatic'          => $request->isStatic ? 1 : 0,
            'slug'              => $code,
            'customer_code'     => $request->customer_code ?? null,
            'is_free'           => $request->is_free ?? 0,
            'client_payment_amount'           => $request->client_payment_amount ?? 0,
            'ipv6'              => $request->ipv6 ?? null,
            'remote_access'     => $request->remote_access ?? 'No',
            'remote_access_username' => $request->remote_access_username ?? null,
            'remote_access_password' => $request->remote_access_password ?? null,
            'remote_access_port' => $request->remote_access_port ?? null,
            'line_man_id' => $request->line_man_id ?? null,
        ]);



        if (!empty($request->customer_id)) {

            if (Client::where('customer_id', $request->customer_id)->count() < 1) {
                $client->customer_id = $request->customer_id;
            } else {
                Toastr::error('Duplicate Customer ID');
                return redirect()->back();
            }
        } else {
            $client->customer_id = $client->id;
        }

        if (globalPermission('free-day-to-create-customer') && $request->free_day_to_create_customer != null && $request->free_day_to_create_customer > 0) {
            $client->free_day_to_create_customer = $request->free_day_to_create_customer;
        }

        if ($request->source_name) {
            $clientSorce = ClientSource::find($request->source_name);
            if ($clientSorce->name == 'Existing Client Reference') {
                $client->client_id = $request->client_id;
            }
        }

        $client->save();

        $result = Clientsinfo::create([
            'client_id'         => $client->id,
            'clients_name'      => $request->client_name,
            'occupation'        => $request->occupation,
            'company_name'      => $request->company_name,
            'designation'       => $request->designation,
            'father_name'       => $request->father_name,
            'mother_name'       => $request->mother_name,
            'flat_no'           => $request->flat_no,
            'building_name'     => $request->building_name,
            'building_no'       => $request->building_no,
            'road_no'           => $request->road_no ?? '',
            'road_name'         => $request->road_name ?? '',
            'block_sector'      => $request->block_sector ?? '',
            'area'              => $request->area ?? '',
            'division'          => $request->division ?? '',
            'district'          => $request->district ?? '',
            'upazila'           => $request->upazila ?? '',
            'thana'             => $request->thana ?? '',
            'contact_no'        => $request->contact_no,
            'national_id'       => $request->national_id,
            'email'             => $request->email,
            'passport_no'       => $request->passport_no,
            'owner_name'        => $request->owner_name,
            'owner_number'      => $request->onwer_number,
            'olt_pon'           => $request->olt_pon,
            'otc'               => $request->otc ?? 0,
            'remarks'           => $request->remarks,
            'dob'               => $request->dob,
            'cable_type'        => $request->cable_type,
            'other_contact'     => $request->other_contact,
            'otc_due'           => checkSettings('otc-auto-due') == 'enable' ? $request->otc : 0,
            'box'               => $request->box,
            'joinDate'          => $request->joinDate ?? today(),
            'client_latitude'   => $request->client_latitude,
            'client_longitude'  => $request->client_longitude,
            'cat_type'          => $request->cat_type,
            'cat_length'        => $request->cat_length,
            'olt_no'            => $request->olt_no,
            'pon_port'          => $request->pon_port,
            'onu_serial'        => $request->onu_serial,
            'from_cable'        => $request->from_cable,
            'to_cable'          => $request->to_cable,
            'from_cable2'        => $request->from_cable2,
            'to_cable2'          => $request->to_cable2,
            'cable_id'          => $request->cable_id,
            'customer_package_name' => $request->customer_package_name,
            'btrc_package_bandwidth' => $request->btrc_package_bandwidth,
            'source_name' => $request->source_name,
            "map_box_id" => $request->map_box_id
        ]);

        $clientsinfo = Clientsinfo::where('client_id', $client->id)->first();
        // dd($request->all());
        if ($request->hasFile('customer_agrement')) {
            $image = $request->file('customer_agrement');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until under 50KB or quality < 10
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            $path = 'customer_agrement/' . $fileName;

            // Choose disk based on settings
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';

            // Save to disk (S3 or local)
            Storage::disk($disk)->put($path, $encoded);

            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename in DB
            $clientsinfo->customer_agrement = $fileName;
        }
        if ($request->customer_profile_picture != null) {
            $image = $request->file('customer_profile_picture');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'customer_profile_picture/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename in DB
            $clientsinfo->customer_profile_picture = $fileName;
        }


        if ($request->hasFile('profilePicture')) {
            $image = $request->file('profilePicture');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'profilePicture/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->profilePicture = $fileName;
        }

        if ($request->picture != null) {
            $image = $request->file('picture');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'picture/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->picture = $fileName;
        }


        if ($request->client_door != null) {
            $image = $request->file('client_door');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'client_door/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->client_door = $fileName;
        }

        if ($request->onu_router_info != null) {
            $image = $request->file('onu_router_info');
            $fileName = time() . '.' . $image->getClientOriginalExtension();

            $img = Image::make($image->getRealPath());

            $quality = 90;
            $maxSize = 50 * 1024; // 50 KB
            $encoded = (string) $img->encode('jpg', $quality);

            // Reduce quality until size is under 50 KB or quality too low
            while (strlen($encoded) > $maxSize && $quality > 10) {
                $quality -= 5;
                $encoded = (string) $img->encode('jpg', $quality);
            }

            // Save image to storage
            $path = 'onu_router_info/' . $fileName;
            // Save to disk (S3 or local)
            $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
            Storage::disk($disk)->put($path, $encoded);
            // If stored in S3 and you want it to be public
            if ($disk === 's3') {
                Storage::disk('s3')->setVisibility($path, 'public');
            }

            // Save filename to database
            $clientsinfo->onu_router_info = $fileName;
        }
        if (checkSettings('national_id_picture') == 'enable') {
            if ($request->nid_image != null) {
                $image = $request->file('nid_image');
                $fileName = time() . '.' . $image->getClientOriginalExtension();

                $img = Image::make($image->getRealPath());

                $quality = 90;
                $maxSize = 50 * 1024; // 50 KB
                $encoded = (string) $img->encode('jpg', $quality);

                // Reduce quality until size is under 50 KB or quality too low
                while (strlen($encoded) > $maxSize && $quality > 10) {
                    $quality -= 5;
                    $encoded = (string) $img->encode('jpg', $quality);
                }

                // Save image to storage
                $path = 'nationalId/' . $fileName;
                // Save to disk (S3 or local)
                $disk = checkSettings('store_image_to_s3') == 'enable' ? 's3' : 'public';
                Storage::disk($disk)->put($path, $encoded);
                // If stored in S3 and you want it to be public
                if ($disk === 's3') {
                    Storage::disk('s3')->setVisibility($path, 'public');
                }

                // Save filename to database
                $clientsinfo->nid_image = $fileName;
            }
        }

        $clientsinfo->save();

        return $client->id;
    }




    static function customerinfo($id)
    {

        $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('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',
                'packages.package_name',
                'pops.popname',
                'pops.subreseller',
                'sub_packages.name as sub_package_name',
                'sub_packages.rate as sub_package_rate',
                'clientsinfo.clients_name',
                'clientsinfo.contact_no',
                'clientsinfo.father_name',
                'clientsinfo.mother_name',
                'clientsinfo.area',
                'clientsinfo.email',
                'clientsinfo.national_id',
                'packages.package_rate'
            );

        $list->where('clients.id', $id);

        return $list;
    }




    static function customerList($condition = '', $id = '')
    {

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

        $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('customer_accounts', 'customer_accounts.client_id', '=', 'clients.id')
            ->leftJoin('employees', 'employees.id', '=', 'clients.marketing_employee_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',
                'packages.package_name',
                'pops.popname',
                'pops.bill_generate',
                'pops.subreseller',
                'sub_packages.name as sub_package_name',
                'sub_packages.rate as sub_package_rate',
                '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',
                'employees.name as marketing_employee_name',
                'employees.mobile_office as marketing_employee_phone',
            );

        // $list->where('deleted_at', null);

        if (auth()->user()->hasRole(['Reseller Admin', 'Reseller', 'Reseller Technician'])) {

            $reseller_ids = DB::table('reseller_user')->where('user_id', $uid)->pluck('reseller_id')->toArray();

            $pop_ids = Pop::with('reseller', 'nas')->whereIn('reseller_id', $reseller_ids)->pluck('id')->toArray();

            $list->whereIn('pops.id', $pop_ids);
        } elseif (auth()->user()->hasRole(['Sub Reseller', 'Sub Reseller Technician'])) {

            $pop_ids = DB::table('pop_user')->where('user_id', $uid)->pluck('pop_id')->toArray();
            $list->whereIn('pops.id', $pop_ids);
        }

        if ($condition === 'newjoin') {

            $list->whereMonth('clients.created_at', '=', date('m'))
                ->whereYear('clients.created_at', '=', date('Y'))
                ->orderBy('clients.id', 'desc');
        } elseif ($condition === 'singleUser') {
            $list->where('clients.id', $id);
        } elseif ($condition === 'pendingCustomer') {

            $list->whereClient_approval('pending')->where('deleted_at', null);
        } elseif ($condition === 'generateBill') {

            $list->whereRaw('customer_accounts.totalPaid < customer_accounts.totalBillAmount')
                ->where('clients.clients_status', '!=', 'deactive')
                ->orderby('pops.popname', 'asc')
                ->orderby('clients.expire_date', 'asc');
        } elseif ($condition === 'byZone') {

            $list->where('pops.id', $id)
                ->where('clients.clients_status', '!=', 'deactive')
                ->orderby('clients.expire_date', 'asc');
        } elseif ($condition === 'deactivated') {

            $list->whereClients_status('deactive')->whereClient_approval('approved');
        } elseif ($condition === 'disabled') {

            $list->whereClients_status('disable');
        } elseif ($condition === 'activated') {

            $list->whereClients_status('active');
        } elseif ($condition === 'expired') {

            $list->whereClients_status('expired');
        } elseif ($condition === 'finalPendingCustomer') {

            $list->whereClient_approval('approved')->where('first_approval', 'Pending');
        } else {

            $list->where('clients_status', '!=', 'deactive')->orderby('clients.id', 'asc');
        }


        return $list;
    }


    static function customerSearch($con = '', $id = '')
    {


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

        $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('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',
                'packages.package_name',
                'pops.popname',
                'pops.subreseller',
                'sub_packages.name as sub_package_name',
                'sub_packages.rate as sub_package_rate',
                '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'
            );

        // $list = DB::table('clientsview');

        if (auth()->user()->hasRole(['Reseller Admin', 'Reseller', 'Reseller Technician'])) {

            $reseller_ids = DB::table('reseller_user')->where('user_id', $uid)->pluck('reseller_id')->toArray();

            $pop_ids = Pop::with('reseller', 'nas')->whereIn('reseller_id', $reseller_ids)->pluck('id')->toArray();

            $list->whereIn('pops.id', $pop_ids);
        } elseif (auth()->user()->hasRole(['Sub Reseller', 'Sub Reseller Technician'])) {

            $pop_ids = DB::table('pop_user')->where('user_id', $uid)->pluck('pop_id')->toArray();
            $list->whereIn('pops.id', $pop_ids);
        } elseif (!auth()->user()->hasRole(['Admin', 'Support Manager', 'Accounts Manager', 'Accounts Executive', 'Billing Man'])) {
            $list->whereIn('pops.id', []);
        }

        if ($con != '') {

            // dd($con);



            // $list->whereRaw("clients_name like '%$con%'");

            // dd($list->toSql());
            $list->where(function ($q) use ($con) {
                return $q->orWhereRaw("clients_name like '%$con'")
                    ->orWhereRaw("userid like '%$con%'")
                    ->orWhereRaw("popname like '%$con%'")
                    ->orWhereRaw("clients_status like '%$con%'")
                    ->orWhereRaw("flat_no like '%$con%'")
                    ->orWhereRaw("area like '%$con%'")
                    ->orWhereRaw("district like '%$con%'")
                    ->orWhereRaw("contact_no like '%$con%'")
                    ->orWhereRaw("package_name like '%$con%'")
                    ->orWhereRaw("building_name like '%$con%'")
                    ->orWhereRaw("block_sector like '%$con%'")
                    ->orWhereRaw("billing_cycle like '%$con%'");
            });
        }






        return $list;
    }






    static function getListOfExpiredCustomer()
    {

        $lists = Client::with('packages')
            ->where('expire_date', '<', now()->addDay(7))
            ->where('clients_status', '=', 'active')
            ->get();



        $client_ids = array();

        foreach ($lists as $list) {
            $client_ids[] = $list->id;
        }

        foreach ($lists as $list) {
            $bill = BillGenerate::where('client_id', '=', $list->id)
                ->where('billing_type', '=', 'monthly')
                ->where('due_date', '!=', $list->expire_date)
                ->count();



            if ($bill < 1) {
                $package_rate = $list->packages->package_rate;
                CustomerAccount::updateCustomrAccount($list->id, $package_rate, 0, 0, $package_rate);
                BillGenerateController::entryBillGenerate($list->id, $list->packages->package_rate, 'Monthly Bill Generate', 'monthly', $list->expire_date);
            }
        }
    }


    public function expire($pop, $package, $billing_cycle)
    {


        if ($pop != '' && $package != '' && $billing_cycle != '') {


            $curBalance =   $this->getResellerBalance('reseller', $pop->reseller_id);

            $dt = Carbon::now();

            $data['exp']            = date($billing_cycle . '-M-Y');
            $data['exp_total']      = Carbon::parse(Carbon::now())->diffInDays($data['exp']);

            if ($dt->day > $billing_cycle) {

                $data['exp']        = Carbon::parse($data['exp'])->addMonth()->format('d-M-Y');
                $data['exp_total']  = Carbon::parse(Carbon::now())->diffInDays($data['exp']);
            } elseif ($dt->day == $billing_cycle) {
                $data['exp']        = Carbon::parse($data['exp'])->addMonth()->format('d-M-Y');
                $data['exp_total']  = Carbon::parse(Carbon::now())->diffInDays($data['exp']);
            }


            $data['exp_total']++;


            $price = Packages::find($package)->package_rate;

            $cost = $this->priceCalculate($dt->daysInMonth, $price, $data['exp_total']);

            // if($cost > $curBalance){
            //     return redirect()->back()->with('error_message','Not Enough Balance');
            // }

            if (config('app.priceShoForClientCreate') == false) {
                $data = array(
                    'cost' => $price,
                    'exp_date' => $data['exp'],
                    'total_date' => $data['exp_total'],
                    'curBalance' => 0
                );
            } else {
                $data = array(
                    'cost' => $cost,
                    'exp_date' => $data['exp'],
                    'total_date' => $data['exp_total'],
                    'curBalance' => $curBalance
                );
            }


            return $data;
        } else {
            return '';
        }
    }




    static function withoutCheckBillAndLogBalanceForPackageChange($customer, $package_id, $pop)
    {
        // pop billable false
        // expire check

        $expire = ExpireCheck::expire($pop, $package_id, $customer->billing_cycle); //checking current user expire information
        $reseller_id = $pop->reseller_id; //getting current user reseller id



        // combining the current and requested package id
        $package_list[] = $customer->package_id;
        $package_list[] = $package_id;

        $package = Packages::whereIn('id', $package_list)->get(); //get 2 package info for log the information


        $customerAccountUpdateCost = $package->find($package_id)->package_rate - $package->find($customer->package_id)->package_rate; //calculating the balance for customer package change

        //message setup
        $action = "Package Changed. From Package: " . $package[0]['package_name'] .
            " To : " . $package[1]['package_name'] .
            " Full Month cost BDT " . $expire['cost'];


        // client update
        Client::where('id', $customer->id)
            ->update([
                'package_id' => $package_id
            ]);

        $pop = Pop::with('reseller')->find($customer->pop_id);

        // reseller balance log report
        ReselleBalanceLogReport::create([
            'reseller_id' => $reseller_id,
            'client_id'   => $customer->id,
            'action'      => $action,
            'amount'      => $expire['cost'],
            'remarks'     => ' BY ' . Auth::user()->email,
            'uniqueId'     => createUUID(),
            'pop_id'       => $customer->pop_id,
            'commission_amount' => getCommissionForResellerRecharge($customer->id, $expire['cost']),
            'reseller_commission_percentage' => $pop->reseller->commission_percentage,
            'pop_commission_percentage' => $pop->commission_percentage,
        ]);

        // radius table updte for package change
        DB::table('radusergroup')
            ->where('username', '=', $customer->userid)
            ->update([
                'groupname' => $package_id
            ]);


        //update customer accunt balance
        CustomerAccount::updateCustomrAccount($customer->id, $customerAccountUpdateCost, 0, 0, $customerAccountUpdateCost);





        // update customer bill report
        BillGenerate::whereClient_id($customer->id)
            ->whereDue_date(date('Y-m-d', strtotime($customer->expire_date)))
            ->update([
                'bill_amount' =>  $package->find($package_id)->package_rate
            ]);

        return 'success';
    }

    // public function resellerBalanceLogForPackageChange($user,$package_id)
    //   {
    //       $pop = Pop::find($user->pop_id);

    //       if($pop->billable == 'yes'){
    //           $this->checkBillAndLogBalanceForPackageChange($user,$package_id,$pop);
    //       }elseif($pop->billable == 'no'){
    //           $this->withoutCheckBillAndLogBalanceForPackageChange($user,$package_id,$pop);
    //       }


    //   }










    public function recharge($r)
    {
        $user = Client::find($r->recharge);



        $now = Carbon::now();
        $expire = Carbon::parse($user->expire_date);
        echo $now;
        echo '<br>';
        echo $user->expire_date;
        echo '<br>';

        $month = $now->diffInMonths($expire);

        echo $month . "<br>";


        if ($month > 0 && $expire > $now) {
            $expire = $now;
            $next = Carbon::parse(date($user->billing_cycle . '-M-Y'));
            $totalDay = $next->diffInDays($expire);
        } else {
            $expire = $expire;
            $next = Carbon::parse($expire)->addMonth();
            $totalDay = $next->diffInDays($expire);
        }



        $pop              = Pop::with('reseller')->find($user->pop_id);
        $package          = Packages::find($user->package_id);
        $resellerBalance  = $this->getResellerBalance('reseller', $pop->reseller_id);
        $totalCost        = $package->package_rate;



        if ($resellerBalance > $totalCost) {


            $reseller_id = $pop->reseller_id;


            $action = "Recharge For 1 Month. From Date: " . $expire .
                " To : " . $next .
                " . For package " . $package->package_name .
                " Total " . $totalDay .
                " days cost BDT " . $totalCost;


            Client::where('id', $user->id)
                ->update(
                    [
                        'expire_date'  => Carbon::createFromFormat('Y-m-d H:i:s', $next)
                    ]
                );

            ReselleBalanceLogReport::create(
                [
                    'reseller_id' => $reseller_id,
                    'client_id'   => $user->id,
                    'action'      => $action,
                    'amount'      => $totalCost,
                    'remarks'     => ' BY ' . Auth::user()->email,
                    'uniqueId'     => createUUID(),
                    'pop_id'       => $user->pop_id,
                    'commission_amount' => getCommissionForResellerRecharge($user->id, $totalCost),
                    'reseller_commission_percentage' => $pop->reseller->commission_percentage,
                    'pop_commission_percentage' => $pop->commission_percentage,
                ]
            );
            Balance::balanceDeductForUserRecharge('reseller', $reseller_id, $totalCost);
            $this->clientExpire($user->id);
            $this->activeRadcheck($user->userid);

            return 'success';
        } else {
            return 'error';
        }
    }


    public function activeRadcheck($userid)
    {
        DB::table('radcheck')
            ->where('username', '=', $userid)
            ->update([
                'op' => ':='
            ]);
    }

    public function clientExpire($id)
    {
        $curDate = Carbon::now();

        Client::whereid($id)
            ->where('expire_date', '>=', $curDate)

            ->update([
                'clients_status' => 'active'
            ]);
    }



    public function getResellerBalance($type, $id)
    {
        $balance = Balance::whereType($type)
            ->whereType_id($id)
            ->first();

        if ($balance !== null) {
            return $balance->amount;
        } else {
            return 0;
        }
    }

    public function priceCalculate($price, $total)
    {
        return round(($price / 30) * $total);
    }

    public function getUserDetails($type, $username)
    {
        if (!empty($type) && !empty($username)) {

            $list = Client::with('clientsinfo', 'pop', 'packages')
                ->where($type, $username)
                ->get();


            return $list;
        }
    }

    public function getUserListForReseller($reseller)
    {
        $pop_list = Pop::whereReseller_id($reseller)->get('id');

        $id = array();
        foreach ($pop_list as $p) {
            $id[] = $p->id;
        }
        $list =  Client::whereIn('pop_id', $id)->get();

        return $list;
    }


    public function queryCheck($r, $curDate)
    {
        $query = '';

        if ($r->billing_cycle != 'all' && $r->user_state != 'all' && $r->recharge_state != 'all') {


            $query .=  "billing_cycle = $r->billing_cycle AND
                  clients_status = '$r->user_state' AND ";

            if ($r->recharge_state == 'active') {
                $query .= "expire_date < '$curDate' ";
            } elseif ($r->recharge_state == 'deactive') {
                $query .= " ( expire_date > '$curDate' or expire_date is null )";
            }
        } elseif ($r->billing_cycle != 'all' && $r->user_state != 'all' && $r->recharge_state == 'all') {
            $query .=  "billing_cycle = $r->billing_cycle AND
                  clients_status = '$r->user_state' ";
        } elseif ($r->billing_cycle != 'all' && $r->user_state == 'all' && $r->recharge_state != 'all') {
            $query .=  "billing_cycle = $r->billing_cycle AND ";
            if ($r->recharge_state == 'active') {
                $query .= "expire_date < '$curDate' ";
            } elseif ($r->recharge_state == 'deactive') {
                $query .= " ( expire_date > '$curDate' or expire_date is null )";
            }
        } elseif ($r->billing_cycle == 'all' && $r->user_state != 'all' && $r->recharge_state != 'all') {
            $query .=  "clients_status = '$r->user_state' AND ";
            if ($r->recharge_state == 'active') {
                $query .= "expire_date < '$curDate' ";
            } elseif ($r->recharge_state == 'deactive') {
                $query .= " ( expire_date > '$curDate' or expire_date is null )";
            }
        } elseif ($r->billing_cycle != 'all' && $r->user_state == 'all' && $r->recharge_state == 'all') {
            $query .=  "billing_cycle = $r->billing_cycle ";
        } elseif ($r->billing_cycle == 'all' && $r->user_state != 'all' && $r->recharge_state == 'all') {
            $query .=  "clients_status = '$r->user_state' ";
        } elseif ($r->billing_cycle == 'all' && $r->user_state == 'all' && $r->recharge_state != 'all') {
            if ($r->recharge_state == 'active') {
                $query .= "expire_date < '$curDate' ";
            } elseif ($r->recharge_state == 'deactive') {
                $query .= " ( expire_date > '$curDate' or expire_date is null )";
            }
        }

        return $query;
    }


    public function clientSearchReportNew($r)
    {
        $list  = Client::with('clientsinfo', 'packages', 'sub_packages', 'pops', 'resellers', 'customer_accounts')->get();
    }

    public function clientSearchReport($r)
    {




        $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);
            }
        }








        return $list->get();
    }



    public function getUserListAll()
    {
        return Client::all();
    }
}
