<?php

namespace App\Http\Controllers;

use App\Classes\BillgenerateUpdate;
use App\Jobs\BillGenerateJob;
use App\Jobs\BillGenerateUpdateJob;
use Exception;
use Carbon\Carbon;
use App\Models\Pop;
use App\Models\Client;
use App\Models\Balance;
use App\Models\Reseller;
use App\Models\Billpayment;
use App\Models\Clientsinfo;
use App\Models\BillGenerate;
use Illuminate\Http\Request;
use App\Models\CustomerAccount;
use App\Models\OtcPaymentLog;
use App\Services\ClientServices;
use Illuminate\Support\Facades\DB;
use Brian2694\Toastr\Facades\Toastr;
use App\Services\GenerateMonthlyBill;
use App\Models\ReselleBalanceLogReport;
use App\Models\ResellerCommissionReference;
use App\Models\SubResellerBalanceLogReport;
use Brian2694\Toastr\Toastr as ToastrToastr;

class BillGenerateController extends Controller
{

    public function monthlyBillFix(request $request, $id, $authId = null)
    {


        DB::beginTransaction();
        try {

            $description = '';

            $bill = BillGenerate::find($id);

            if (checkSettings('otc_payment_with_generale_payment') == 'enable' && $bill->billing_type == 'otc') {
                OtcPaymentLog::where('bill_id', $bill->id)->delete();
            }

            if ($bill->billing_type == 'otc') {
                $client = Client::with('pops', 'pops.reseller')->withTrashed()->where('id', $bill->client_id)->first();

                // dd($client);


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

                if ($client_info->otc_due != $bill->bill_amount) {
                    // dd($client_info);
                    Toastr::error("Please delete the otc payment from payment history of this bill generate");
                    return back();
                } else {

                    (new ReselleBalanceLogReport)->resellerBalanceAdjust($client, $bill->bill_amount, 'OTC Delete amount : ' . $bill->bill_amount);

                    if ($client->pops->subreseller == 'yes') {
                        (new SubResellerBalanceLogReport)->subResellerBalanceAdjust($client, $bill->bill_amount, 'OTC Delete amount : ' . $bill->bill_amount);
                    }

                    $description = 'OTC Delete amount : ' . $bill->bill_amount;

                    $client_info->otc = 0;
                    $client_info->otc_due = 0;
                    $client_info->save();
                }
            } elseif ($bill->billing_type == 'new') {

                $client = Client::with('pops', 'pops.reseller')->withTrashed()->where('id', $bill->client_id)->first();

                (new ReselleBalanceLogReport)->resellerBalanceAdjust($client, $bill->bill_amount, 'New Customer Entry Delete; amount : ' . $bill->bill_amount);

                if ($client->pops->subreseller == 'yes') {
                    (new SubResellerBalanceLogReport)->subResellerBalanceAdjust($client, $bill->bill_amount, 'New Customer Entry Delete; amount : ' . $bill->bill_amount);
                }

                $description = 'New Delete amount : ' . $bill->bill_amount;
            } else {
                $description = $bill->billing_type . ' bill deleted: ' . $bill->bill_amount;
            }

            $clientAccount = CustomerAccount::where('client_id', $bill->client_id)->first();

            $clientAccount->decrement('dueAmount', $bill->bill_amount);
            $clientAccount->decrement('totalBillAmount', $bill->bill_amount);


            $bill->description = $description . ' | Previous description : ' . $bill->description;
            $bill->bill_amount = 0;
            $bill->paid_amount = 0;
            $bill->parmanent_discount = 0;
            $bill->billing_type = 'deleted';
            $bill->deleted_at = Carbon::now();
            $bill->deleted_by = auth()->user()->id ?? $authId;
            $bill->save();

            $reseller_commission = ResellerCommissionReference::where('bill_generate_id', $bill->id)->first();
            if ($reseller_commission && $reseller_commission->bill_generate_id != null) {
                $reseller_commission->actions = $reseller_commission->actions . " bill deleted";
                $reseller_commission->received_amount = 0;
                $reseller_commission->save();
            }

            DB::commit();
            if(!globalPermission('payment-bill-by-bill')){
                BillGenerateUpdateJob::dispatch($bill->client_id);
            }

            return redirect()->back();
        } catch (\Exception $e) {

            dd($e);
            Toastr::error("some Problem Happen");
            DB::rollback();
        }
    }

    public function deleteMonthlyBill()
    {


        $ids = DB::table('clients')
            ->where('clients_status', 'deactive')
            ->pluck('id');


        $ids = DB::table('bill_generates')
            ->whereBetween('created_at', ['2023-02-01 00:00:00.000000', '2023-02-28 23:59:59.000000'])
            ->where('billing_type', 'monthly')
            ->groupBy('client_id')
            ->havingRaw('count(client_id) > 1')
            ->pluck('client_id');


        $list = DB::table('bill_generates')
            ->whereBetween('created_at', ['2023-02-01 00:00:00.000000', '2023-02-28 23:59:59.000000'])
            ->where('billing_type', 'monthly')
            ->whereIn('client_id', $ids)
            ->get()
            ->groupBy('client_id')
            // unset 1st item in each group
            ->map(function ($item) {
                $item->shift();
                return $item;
            });


        foreach ($list as $l) {

            $l = $l[0];
            (new \App\Http\Controllers\BillGenerateController)->monthlyBillFix(request(), $l->id);
        }

    }



    public function query()
    {
        return DB::table('clients')
            ->leftJoin('clientsinfo', 'clientsinfo.client_id', '=', 'clients.id')
            ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('customer_accounts', 'customer_accounts.client_id', '=', 'clients.id')
            //            ->leftJoin('bill_generates', 'bill_generates.client_id', '=', 'clients.id')
            ->select(
                'clients.*',
                'otc_due',
                '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',
                'clientsinfo.clients_name',
                'clientsinfo.contact_no',
                'clientsinfo.father_name',
                'clientsinfo.mother_name',
                'clientsinfo.area',
                'clientsinfo.email',
                'clientsinfo.national_id',
                'packages.package_rate',
                //                'billing_type',
                //                'due_date',
                //                'bill_generates.created_at as bill_create_date',
                //                'bill_generates.bill_amount as billamount'
            )
            ->where('pops.bill_generate', 'yes')
            ->where('clients.clients_status', '!=', 'deactive');
    }

    public function billCollection()
    {
        if (!auth()->user()->hasPermissionTo('bill-collection')) {
            abort(404);
        }

        $list = Client::list()
            ->whereHas('customerAccount', function ($query) {
                $query->where('dueAmount', '>', 0);
            });

        return view('billing.pendingBill', [
            'page_title' => 'Customer Bill Collection List',
            'list' => $list->get()
        ]);
    }

    function generateMonthlyBill(Request $request)
    {

        if ($request->isMethod('post')) {

            $bill_month = Carbon::createFromDate($request->year, $request->month, 1)->firstOfMonth();

            $pops_id = Pop::where('bill_generate', 'yes')->pluck('id')->toArray();

            Client::with('packages', 'generatedBill')
                ->where('clients_status', '!=', 'deactive')
                ->where('clients_status', '!=', 'close')
                ->where('is_free', '!=', '1')
                ->whereIn('pop_id', $pops_id)
                ->select('id', 'expire_date', 'package_id', 'parmanent_discount')
                ->chunk(100, function ($clients) use ($bill_month) {
                    BillGenerateJob::dispatch($clients, $bill_month);
                });

            Toastr::success('Bill generate Started');
        }


        return view('billing.generateMonthlyBill', [
            'page_title' => 'Generate Monthly BIll',
        ]);
    }





    public function billSheet(Request $request)
    {
        $start  = now()->firstOfMonth();
        $end    = now()->endOfMonth();

        if ($request->isMethod('post')) {;
            $date = Carbon::createFromDate('2023', $request->selectedValue, 1);
            $request->session()->put('invoice_pdf_print_date', $date);
        }

        if ($request->session()->get('invoice_pdf_print_date')) {
            $start  = Carbon::parse($request->session()->get('invoice_pdf_print_date'))->firstOfMonth();
            $end    = Carbon::parse($request->session()->get('invoice_pdf_print_date'))->lastOfMonth();
        }
        $resellers = Reseller::list()->where('reseller_type', 'own');
        $popes = Pop::whereIn('reseller_id', $resellers->pluck('id'))->get();
        $type = $request->query('type');

        $criteria = $request->query('search');

        $list = DB::table('clients')
            ->leftJoin('clientsinfo', 'clientsinfo.client_id', '=', 'clients.id')
            ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('customer_accounts', 'customer_accounts.client_id', '=', 'clients.id')
            ->leftJoin('bill_generates', 'bill_generates.client_id', '=', 'clients.id')
            ->select(
                'clients.*',
                'otc_due',
                'customer_accounts.dueAmount',
                'customer_accounts.totalPaid',
                'customer_accounts.totalDiscount',
                'customer_accounts.totalBillAmount',
                'clientsinfo.flat_no',
                'clientsinfo.owner_name',
                'clientsinfo.building_no',
                'clientsinfo.building_name',
                'clientsinfo.road_no',
                'clientsinfo.road_name',
                'clientsinfo.block_sector',
                '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',
                'billing_type',
                'due_date',
                'bill_generates.created_at as bill_create_date',
                'bill_generates.bill_amount as billamount',
                'clientsinfo.box',
                'clientsinfo.joinDate',
            )
            ->whereIn('clients.pop_id', $popes->pluck('id'))
            ->where('pops.bill_generate', 'yes')
            ->where('billing_type', 'monthly')
            ->whereBetween('bill_generates.created_at', [$start, $end])
            ->where('clients.clients_status', '!=', 'deactive')
            ->orderBy('clients.userid', 'asc');


        if ($criteria == 'area') {


            if ($type == 'all' || $type == null) {

                $list->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('pops.id', 'asc');
            } else {

                $list->where('clientsinfo.area', 'like', "$type")
                    ->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('pops.id', 'asc');
            }
        } elseif ($criteria == 'pop') {

            if ($type == 'all' || $type == null) {

                $list->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('pops.id', 'asc');
            } else {

                $list->where('pops.id', $type)
                    ->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('pops.id', 'asc');
            }
        } elseif ($criteria == 'resellers') {

            if ($type == 'all' || $type == null) {

                $list->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('pops.id', 'asc');
            } else {

                $list->where('pops.reseller_id', $type)
                    ->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('pops.id', 'asc');
            }
        } elseif ($criteria == 'username') {
            if ($type == 'all' || $type == null) {

                $list->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('clients.id', 'asc');
            } else {

                $list->where('clients.userid', 'like', '%' . $type . '%')
                    ->whereMonth('bill_generates.created_at', '=', date('m'))
                    ->whereYear('bill_generates.created_at', '=', date('Y'))
                    ->orderby('clients.expire_date', 'asc')
                    ->orderby('clients.id', 'asc');
            }
        }


        // select DISTINCT area from clientsinfo left join clients on clients.id=clientsinfo.client_id
        //  left join pops on pops.id=clients.pop_id left join resellers on resellers.id=pops.id where resellers.reseller_type='own'


        $areas = DB::table('clientsinfo')
            ->leftJoin('clients', 'clients.id', '=', 'clientsinfo.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->select('area')
            ->distinct()
            ->where('resellers.reseller_type', 'own')
            ->whereIn('clients.pop_id', $popes->pluck('id'))
            ->get();




        if ($request->query('print') == true && $request->query('due') == 'yes') {

            $reseller =  Reseller::resellerList()->where('reseller_type', 'own')->get();
            return view('billing.printBillSheet', [
                'page_title'    => 'Customer Bill List',
                'list'          => $list->get(),
                'poplist'       => Pop::all(),
                'reseller'      =>  $reseller,
                'due'           => $request->due,
            ]);
        } elseif ($request->query('print') == true && $request->query('due') == 'no') {

            $reseller =  Reseller::resellerList()->where('reseller_type', 'own')->get();
            return view('billing.printBillSheet', [
                'page_title'    => 'Customer Bill List',
                'list'          => $list->get(),
                'poplist'       => Pop::all(),
                'reseller'      =>  $reseller,
                'due'           => $request->due,
            ]);
        } else {
            $reseller =  Reseller::resellerList()->where('reseller_type', 'own')->get();
            return view('billing.billSheet', [
                'page_title'    => 'Customer Bill List',
                'list'          => $list->get(),
                'poplist'       => $popes,
                // 'poplist'       => Pop::whereHas('reseller', function ($query) {
                //     $query->where('reseller_type', 'own');
                // })->get(),
                'areas'         => $areas,
                'reseller'      =>  $reseller,
            ]);
        }
    }

    public function addBill(Request $request)
    {

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

        DB::beginTransaction();

        try {
            CustomerAccount::updateCustomrAccount($request->client_id, $request->amount, 0, 0, $request->amount);
            $dueAmount = DB::table('customer_accounts')->where('client_id', $request->client_id)->first();
            $client_id_time = $request->id . $request->billing_type . '_' . now()->format('Y-m-d H:i:s');

            BillGenerate::create([
                'description'           => $request->description,
                'bill_amount'           => $request->amount,
                'client_id'             => $request->client_id,
                'billing_type'          => $request->billing_type,
                'due_date'              => today(),
                'due_amount'            => $dueAmount->dueAmount,
                'parmanent_discount'    => 0,
                'package_name'          => '',
                'package_rate'          => '',
                'client_id_time'        => $client_id_time,
                'added_by'              => auth()->user()->id,
            ]);

            DB::commit();
            BillGenerateUpdateJob::dispatch($request->client_id);
            Toastr::success('Bill Added Successfully', 'success');
            return redirect()->back();
        } catch (\Throwable $th) {
            // dd($th);

            DB::rollBack();

            Toastr::error('something is worng', 'error');
            return redirect()->back();
        }
    }


    public function billgenerate(Request $request)
    {
        $client = Client::with('packages', 'clientsinfo', 'pops')
            ->whereId($request->id)
            ->select('id', 'expire_date', 'package_id', 'userid', 'parmanent_discount', 'pop_id')
            ->first();

        if ($client) {
            $status = (new GenerateMonthlyBill)->generate($client);
        }

        $exp = Carbon::parse(Client::find($request->id)->expire_date);

        if ($exp >= today()) {

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



        if ($status == 'success') {
            Toastr::success('Bill generate Success', 'success');
            $bill_generate = BillGenerate::where('client_id', $client->id)->orderBy('created_at', 'desc')->first();

            if ($client->pops->sms_send == 'yes' && $client->clientsinfo->contact_no != null) {
                try {

                    sendSmsAfterBillgenerate($bill_generate->id);
                } catch (\Exception $e) {
                }
            }

            if (checkSettings('singleInvoice') == 'enable') {
                $setting = json_decode(emailSetting()->where('tamplate_name', 'bill_generate_eamil')->first()->email_body);
                if ($setting->sendemail == 'Yes') {
                    try {
                        (new CustomerInvoiceController)->sendInvoice($client->id);
                    } catch (\Exception $e) {
                    }
                }
            }
            return redirect()->back();
        } else {

            if ($status == 'error_expire_date_next_month') {
                Toastr::error('Expire Date is in next month!!', 'error');
                return redirect()->back();
            }

            Toastr::error('Sorry Something is worng!! or Bill already Generated Previously', 'error');
            return redirect()->back();
        }
    }


    static function updateExpireAndCustomer($client, $package_rate)
    {
        if (getBillingType() == 'day_to_day') {
            if (Carbon::parse($client->expire_date) < today()) {
                Client::where('id', $client->id)
                    ->update([
                        'expire_date'    => Carbon::parse(today())->addDay(29),
                        'clients_status' => 'active'
                    ]);
            } else if (Carbon::parse($client->expire_date) == today()) {
                Client::where('id', $client->id)
                    ->update([
                        'expire_date'    => Carbon::parse($client->expire_date)->addDay(30),
                        'clients_status' => 'active'
                    ]);
            }
        } else {
            if (Carbon::parse($client->expire_date) < today()->endOfMonth()) {

                Client::where('id', $client->id)
                    ->update([
                        'expire_date'    => Carbon::parse($client->expire_date)->addMonth(),
                        'clients_status' => 'active'
                    ]);
            }
        }


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

        CustomerAccount::updateCustomrAccount($client->id, ($package_rate), 0, 0, $package_rate);
    }



    static function entryBillGenerate($id, $amount, $description, $type, $due_date, $payment_discount = 0, $parmanent_discount = 0, $package_name = null, $package_rate = null, $request_month = null)
    {


        $dueAmount = DB::table('customer_accounts')->where('client_id', $id)->first();

        if ($type == 'otc') {
            $auto = '_otc';
        } elseif ($type == 'new') {
            $auto = '_new';
        } elseif ($type == 'reactive') {
            $auto = '_reactive';
        } else {
            $auto = '';
        }

        $client_id_time = $id . $auto . '_' . now()->format('Y-m-d H:i');

        $bill                   = new BillGenerate();
        $bill->description      = $description;
        $bill->bill_amount      = $amount;
        $bill->client_id        = $id;
        $bill->billing_type     = $type;
        $bill->due_date         = date('Y-m-d', strtotime($due_date));
        $bill->due_amount       = $dueAmount->dueAmount;
        $bill->parmanent_discount = $parmanent_discount;
        $bill->package_name     = $package_name;
        $bill->package_rate     = $package_rate;
        $bill->client_id_time   = $client_id_time;
        $bill->created_at       = $request_month ?? now();
        $bill->updated_at       = $request_month ?? now();
        $bill->save();
        return $bill;
    }




    public function billsheetPrint(Request $request)
    {
        $type = $request->query('type');
        $list = $this->query();
        if ($type == 'all' || $type == null) {
            $list->whereMonth('bill_generates.created_at', '=', date('m'))
                ->whereYear('bill_generates.created_at', '=', date('Y'))
                ->orderby('clients.expire_date', 'asc');
        } else {
            $list->where('pops.id', $type)
                ->whereMonth('bill_generates.created_at', '=', date('m'))
                ->whereYear('bill_generates.created_at', '=', date('Y'))
                ->orderby('clients.expire_date', 'asc');
        }
        return view('billing.printBillSheet', [
            'page_title'    => 'Customer Bill List',
            'list'          => $list->get(),
            'poplist'       => Pop::all()
        ]);
    }


    public function billCollectionPrint()
    {
        $list = Client::list()
            ->whereHas('customerAccount', function ($query) {
                $query->where('dueAmount', '>', 0);
            });

        return view('billing.printPendingBill', [
            'page_title' => 'Customer Bill Collection List',
            'list' => $list->get()
        ]);
    }




    public function severalPrint($id)
    {
        $list = DB::table('clients')
            ->leftJoin('clientsinfo', 'clientsinfo.client_id', '=', 'clients.id')
            ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('customer_accounts', 'customer_accounts.client_id', '=', 'clients.id')
            // ->leftJoin('bill_generates','bill_generates.client_id','=','clients.id')
            ->select(
                'clients.*',
                'customer_accounts.dueAmount',
                'customer_accounts.totalPaid',
                'customer_accounts.totalDiscount',
                'customer_accounts.totalBillAmount',
                'clientsinfo.flat_no',
                'clientsinfo.building_no',
                'clientsinfo.building_name',
                'clientsinfo.road_no',
                'clientsinfo.block_sector',
                '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'
            )
            ->where('clients.clients_status', '!=', 'deactive')
            ->where('clients.id', '=', $id);

        $list->whereRaw('customer_accounts.dueAmount > 0')
            ->orderby('pops.popname', 'asc')
            ->orderby('clients.expire_date', 'asc');

        return view('billing.printPendingBill', [
            'page_title' => 'Customer Bill Collection List',
            'list' => $list->get()
        ]);
    }


    public function monthlyPaidCustomer()
    {

        $db = Billpayment::select('bill_payments.id', 'bill_payments.paid_amount', 'bill_payments.client_id', 'bill_payments.created_at')
            ->orderBy("created_at", "desc")
            ->whereYear('created_at', Carbon::now()->year)
            ->whereMonth('created_at', Carbon::now()->month);

        $sales = $db->groupBy('bill_payments.id', 'bill_payments.paid_amount', 'bill_payments.client_id', 'bill_payments.created_at', DB::raw("date(created_at)"))->get();
        $data["sales"] = $sales;

        return view("billing.monthlyPaid", [
            'data' => $data,
            'sales' => $sales

        ]);
    }

    public function printMonthlyPaid()
    {
        $db = Billpayment::select('bill_payments.id', 'bill_payments.paid_amount', 'bill_payments.client_id', 'bill_payments.created_at')
            ->orderBy("created_at", "desc")
            ->whereYear('created_at', Carbon::now()->year)
            ->whereMonth('created_at', Carbon::now()->month);

        $sales = $db->groupBy('bill_payments.id', 'bill_payments.paid_amount', 'bill_payments.client_id', 'bill_payments.created_at', DB::raw("date(created_at)"))->get();
        $data["sales"] = $sales;

        return view("billing.printMonthlyPaid", [
            // 'data' => $data,
            'sales' => $sales

        ]);
    }

    public function allcustomerbill()
    {
        $pops_id = Pop::where('bill_generate', 'yes')->pluck('id')->toArray();

        $all_clients = Client::whereIn('pop_id', $pops_id)->get();
        DB::beginTransaction();
        try {
            foreach ($all_clients as $c) {
                $client = Client::with('packages')->whereId($c->id)->select('id', 'expire_date', 'package_id', 'userid', 'parmanent_discount')->first();

                if ($client) {

                    $status = (new GenerateMonthlyBill)->generate($client);
                }

                $exp = Carbon::parse(Client::find($c->id)->expire_date);


                if ($exp >= today()) {

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

            DB::commit();
            Toastr::success("all generate Successfull");
            return back();
        } catch (Exception $e) {
            DB::rollBack();
            Toastr::error("some Problem Happen");
            return back();
        }
    }

    public function billGenerateReport()
    {
        $resellers = Reseller::list()->where('reseller_type', 'own');
        $start = Carbon::now()->startOfMonth();
        $end = today();


        $popes = Pop::whereIn('reseller_id', $resellers->pluck('id'))->get();
        $clients = Client::withTrashedList()->whereIn('pop_id', $popes->pluck('id'));

        $bill_generate_type = BillGenerate::select('billing_type')->distinct()->get();


        $start_format = $start->format('Y-m-d 00:00:00');
        $end_format = $end->format('Y-m-d 23:59:59');
        $bill_generates = BillGenerate::with('clients', 'clientsinfo')->whereIn('client_id', $clients->pluck('id'))
            ->whereBetween('created_at', [$start_format, $end_format])
            ->get();
        $data = [
            'resellers' => $resellers,
            'start' => $start,
            'end' => $end,
            'bill_generates' => $bill_generates,
            'bill_generate_type' => $bill_generate_type,
        ];

        return view('billGenerateReport.search', $data);
    }

    public function billGenerateSearch(Request $request)
    {
        // dd("comes");
        $start = Carbon::parse($request->from_date)->startOfDay()->format('Y-m-d 00:00:00');
        $end = Carbon::parse($request->to_date)->endOfDay()->format('Y-m-d 23:59:59');


        if ($request->user_name) {
            $user = Client::withTrashedList()->where('userid', $request->user_name)->first();
            if ($user) {
                $bill_generates = BillGenerate::with('clients', 'clientsinfo')->where('client_id', $user->id)->get();
                $data = [

                    'bill_generates' => $bill_generates,

                ];
                return view('billGenerateReport.billgenerateSearchReport', $data);
            } else {
                $data = [

                    'bill_generates' => [],

                ];
                return view('billGenerateReport.billgenerateSearchReport', $data);
            }
        } else {

            if ($request->reseller == "all") {
                $resellers = Reseller::list()->where('reseller_type', 'own');
            } else {
                $resellers = Reseller::list()->where('id', $request->reseller);
            }
            if ($request->pop == "all") {
                $popes = Pop::whereIn('reseller_id', $resellers->pluck('id'))->get();
                $clients = Client::withTrashedList()->whereIn('pop_id', $popes->pluck('id'));
            } else {
                $popes = Pop::where('id', $request->pop)->get();
                $clients = Client::withTrashedList()->whereIn('pop_id', $popes->pluck('id'));
            }
            if ($request->payment_type == "all") {
                $bill_generates = BillGenerate::with('clients', 'clientsinfo')
                    ->whereIn('client_id', $clients->pluck('id'))
                    ->whereBetween('created_at', [$start, $end])
                    ->get();
                    $data = [

                        'bill_generates' => $bill_generates,

                    ];
                    return view('billGenerateReport.billgenerateSearchReport', $data);
            } else {
                $bill_generates = BillGenerate::with('clients', 'clientsinfo')
                    ->whereIn('client_id', $clients->pluck('id'))
                    ->whereBetween('created_at', [$start, $end])
                    ->where('billing_type', $request->payment_type)
                    ->get();
                    $data = [

                        'bill_generates' => $bill_generates,

                    ];
                    return view('billGenerateReport.billgenerateSearchReport', $data);
            }

            // $bill_generates = BillGenerate::with('clients', 'clientsinfo')->whereIn('client_id', $clients->pluck('id'))->whereBetween('created_at', [$start, $end])->get();
            // dd($bill_generates);
            // return $bill_generates;
        }
    }

    public function missingBillGenerate()
    {
        $start = Carbon::now()->startOfMonth();
        $end = Carbon::now()->endOfMonth();
        $start_format = $start->format('Y-m-d 00:00:00');
        $end_format = $end->format('Y-m-d 23:59:59');


        $clients = Client::with('generatedBill', 'clientsinfo', 'pops')
            ->whereHas('pops', function ($query) {
                $query->where('bill_generate', '=', 'yes');
            })
            ->where(function ($query) {
                $query->where('clients.clients_status', '=', 'active')
                    ->orWhere('clients.clients_status', '=', 'expired');
            })
            ->whereNull('clients.deleted_at')
            ->whereNotIn('clients.id', function ($query) use ($start_format, $end_format) {
                $query->select('client_id')
                    ->from('bill_generates')
                    ->whereBetween('created_at', [$start_format, $end_format])
                    ->where('billing_type', 'LIKE', 'monthly');
            })
            ->get();
        $data = [
            'clients' => $clients,
            'start' => $start,
            'end' => $end,
            'reseller' => Reseller::resellerList()->get(),
        ];

        return view('billGenerateReport.missingBillgenrate', $data);
    }

    public function reseultMissingBillGenerate(Request $request)
    {
        // dd($request->all());
        // $start = $request->from_date;
        // $end = $request->to_date;
        $start_format = Carbon::parse($request->from_date)->format('Y-m-d 00:00:00');
        $end_format = Carbon::parse($request->to_date)->format('Y-m-d 23:59:59');


        $clients = Client::with('generatedBill', 'clientsinfo', 'pops')
            ->whereHas('pops', function ($query) {
                $query->where('bill_generate', '=', 'yes');
            })
            ->where(function ($query) {
                $query->where('clients.clients_status', '=', 'active')
                    ->orWhere('clients.clients_status', '=', 'expired');
            })
            ->whereNull('clients.deleted_at')
            ->whereNotIn('clients.id', function ($query) use ($start_format, $end_format) {
                $query->select('client_id')
                    ->from('bill_generates')
                    ->whereBetween('created_at', [$start_format, $end_format])
                    ->where('billing_type', 'LIKE', 'monthly');
            });

            if ($request->pop_id != null) {
                $clients->whereHas('pops', function ($query) use ($request) {
                    $query->where('id', $request->pop_id);
                });
            } else {
                if ($request->reseller_id != null) {
                    $clients->whereHas('pops.reseller', function ($query) use ($request) {
                        $query->where('id', $request->reseller_id);
                    });
                }
            }

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

        return view('billGenerateReport.resultMissingBillGenerate', $data);
    }
}
