<?php

namespace App\Http\Controllers;

use App\Models\Pop;
use App\Models\Client;
use App\Models\Income;
use App\Models\Reseller;
use App\Models\User_log;
use App\Models\IncomeHead;
use App\Models\Billpayment;
use App\Models\CompanyInformation;
use App\Models\ReselleBalanceLogReport;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Facades\Mail;

class Dashboard extends Controller
{

    protected $bill_type = [
        'new' => 1,
        'otc' => 2,
        'billing_cycle_change' => 3,
        'package_change' => 4,
        'monthly' => 5,
        'reactive' => 6,
    ];

    public function __construct()
    {
        $this->middleware('permission:show-dashboard', ['only' => ['destroy']]);
    }

    public function portalInvoice()
    {

        $portal_api         = customer_panel_api();
        $url                = $portal_api->customer_panel_url . '/api/v1/client-invoice/' . $portal_api->customer_panel_secret;
        $pay_url            = $portal_api->customer_panel_url . '/customer-invoice/' . $portal_api->customer_panel_secret;

        return view('dashboard.portal_invoice', [
            'url' => $url,
            'pay_url' => $pay_url,
        ]);
    }

    public function portalInvoicePrint($id, $txnID, $date, $amount, $exp, $type)
    {
        // dd($id, $txnID, $amount);
        $carbonDate = Carbon::parse($date);
        $carbonExp = Carbon::parse($exp);

        return view('dashboard.portalInvPrint', [
            'inv_id' => $id,
            'txnID' => $txnID,
            'date' => $carbonDate,
            'amount' => $amount,
            'company_info' => CompanyInformation::latest()->first(),
            'exp_date' => $carbonExp,
            'type' => $type,
        ]);
    }

    public function dashboardBill()
    {
        $bill_pay = Billpayment::whereBetween('created_at', [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()])
            ->where(function ($q) {
                $q->whereNull('income_id');
            })
            ->get();

        foreach ($bill_pay as $bill) {

            if ($bill->description == 'Customer Package Change Bill Collection') {
                $type = 4;
            } else {
                $type = 3;
            }

            $income_type = IncomeHead::find($type);
            $income =   Income::create([
                'name' => $income_type->name,
                'date' => now(),
                'amount' => $bill->paid_amount,
                'description' => 'Bill Payment for customer : ' . Client::find($bill->client_id)->userid,
                'incomeHead' => $type
            ]);
            $bill->income_id = $income->id;
            $bill->save();
        }
    }

    function index()
    {

        $pop = Pop::get();
        // dd($pop);
        $resellers = Reseller::resellerList()->where('id', $pop->reseller_id);
        // dd($resellers);
        $page_title = 'Dashboard';
        return view('dashboard.index', [
            'page_title' => $page_title,
            'resellers' => $resellers->paginate(1000)
        ]);
    }

    public function sendmail()
    {
        Mail::raw('It works', function ($message) {
            $message->to(request('email'))
                ->subject('hello test');
        });
        return redirect()->route('dashboard');
    }

    public function findTotalPaid($type, $paidList)
    {

        $id = $this->bill_type[$type];

        if (!empty($paidList)) {
            return $paidList->where('income_head_id', $id)->sum('paid_total');
        }
    }

    public function findTotalPaidId($type, $paidList)
    {
        $id = $this->bill_type[$type];

        if (!empty($paidList)) {
            return $paidList->where('income_head_id', $id)->sum('total_id');
        }
    }


    public function popWiseMonthlyBillReport($pop_ids, $monthly_discount)
    {
        $start = today()->firstOfMonth();
        $end = today()->endOfMonth();

        // Precompute discounts for quick lookups
        $discountMap = $monthly_discount->pluck('total_discount', 'pop_id')->toArray();

        $result = DB::table('bill_generates')
            ->leftJoin('clients', 'clients.id', '=', 'bill_generates.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->selectRaw("
                pops.popname,
                pops.id as pop_id,
                pops.reseller_id,
                SUM(bill_generates.bill_amount) as total_bill,
                SUM(bill_generates.paid_amount) as total_paid,
                COUNT(DISTINCT CASE WHEN bill_generates.paid_amount != bill_generates.bill_amount THEN bill_generates.client_id END) as not_paid,
                COUNT(DISTINCT bill_generates.client_id) as total_row
            ")
            ->whereBetween('bill_generates.created_at', [$start, $end])
            ->where('bill_generates.bill_amount', '>', 0)
            ->where('pops.bill_generate', 'yes')
            ->whereIn('clients.pop_id', $pop_ids)
            ->groupBy('pops.id', 'pops.popname', 'pops.reseller_id')
            ->get()
            ->map(function ($item) use ($discountMap) {
                $item->total_discount = $discountMap[$item->pop_id] ?? 0; // Use precomputed discounts
                return $item;
            });

        return $result;
    }

    public function popWiseMonthlyBillPayment($pop_ids)
    {
        $start = today()->firstOfMonth();
        $end = today()->endOfMonth();

        $result = DB::table('bill_payments')
            ->leftJoin('clients', 'clients.id', '=', 'bill_payments.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->selectRaw("
        pops.popname,
        pops.id as pop_id,
        pops.reseller_id,
        SUM(bill_payments.paid_amount) as total_paid,
        COUNT(DISTINCT bill_payments.client_id) as total_row
    ")
            ->whereBetween('bill_payments.created_at', [$start, $end])
            ->where('bill_payments.paid_amount', '>', 0)
            ->where('pops.bill_generate', 'yes')
            ->whereIn('clients.pop_id', $pop_ids)
            ->groupBy('pops.id', 'pops.popname', 'pops.reseller_id')
            ->get();

        return $result;
    }

    public function monthlyDiscount($pop_ids)
    {
        $startOfMonth = today()->startOfMonth();
        $endOfMonth = today()->endOfMonth()->addDay(); // Add the extra day for the end of the month

        $result = DB::table('bill_payments')
            ->join('clients', 'clients.id', '=', 'bill_payments.client_id')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->where('pops.bill_generate', 'yes')
            ->whereBetween('bill_payments.created_at', [$startOfMonth, $endOfMonth])
            ->whereIn('clients.pop_id', $pop_ids)
            ->select('pops.popname', 'clients.pop_id', 'pops.reseller_id', DB::raw('sum(bill_payments.discount_amount) as total_discount'))
            ->groupBy('pops.id', 'pops.popname', 'clients.pop_id', 'pops.reseller_id') // Explicitly group by necessary columns
            ->get();


        return $result;
    }

    public function todayBillCollection($pop_ids)
    {
        $start_of_to_day = Carbon::parse(today())->format('Y-m-d 00:00:00');
        $end_of_to_day = Carbon::parse(today())->format('Y-m-d 23:59:59');
        return DB::table('bill_payments')
            ->leftJoin('clients', 'clients.id', '=', 'bill_payments.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->where('pops.bill_generate', 'yes')
            ->whereBetween('bill_payments.created_at', [$start_of_to_day, $end_of_to_day])
            ->whereIn('pop_id', $pop_ids)
            ->selectRaw('sum(paid_amount) as total_paid')
            ->first()
            ->total_paid;
    }

    public function thisMonthBillCollection($pop_ids)
    {
        $start_of_to_day = Carbon::parse(today()->startOfMonth())->format('Y-m-d 00:00:00');
        $end_of_to_day = Carbon::parse(today()->endOfMonth())->format('Y-m-d 23:59:59');

        return DB::table('bill_payments')
            ->leftJoin('clients', 'clients.id', '=', 'bill_payments.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->where('pops.bill_generate', 'yes')
            ->whereBetween('bill_payments.created_at', [$start_of_to_day, $end_of_to_day])
            ->whereIn('pop_id', $pop_ids)
            ->selectRaw('sum(paid_amount) as total_paid')
            ->first()
            ->total_paid;
    }


    public function totalDue($pop_ids)
    {
        return DB::table('bill_generates')
            ->selectRaw('sum(bill_amount) - sum(paid_amount) as totalDue')
            ->leftJoin('clients', 'clients.id', '=', 'bill_generates.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->where('pops.bill_generate', 'yes')
            ->whereIn('pops.id', $pop_ids)
            // ->whereNull('clients.deleted_at')
            ->first()
            ->totalDue;
    }

    public function localApproximateBillAmount($pop_ids)
    {
        $localApproxBill = DB::table('clients')
            ->select(DB::raw('clients.pop_id,reseller_id,resellers.name,package_id,package_rate,count(clients.id) as total_client'))
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->where('pops.bill_generate', 'yes')
            ->where('package_rate', '>', 0)
            ->where('clients.deleted_at', '=', NULL)
            ->whereIn('clients.clients_status', ['active', 'expired', 'disable', 'deactive'])
            ->whereIn('clients.pop_id', $pop_ids)
            ->groupBy('package_id', 'reseller_id')
            ->get()
            ->groupBy('reseller_id');

        return $localApproxBill;
    }

    public function resellerWiseOtherReport($pop_ids)
    {
        $start = today()->firstOfMonth();
        $end = today()->endOfMonth();


        $otherResellerMonthlyBillCollection = DB::table('reselle_balance_log_reports')
            ->select(DB::raw('sum(amount) as total,clients.pop_id,pops.reseller_id,resellers.name'))
            ->leftJoin('clients', 'clients.id', '=', 'reselle_balance_log_reports.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereIn('clients.pop_id', $pop_ids)
            ->where('clients.deleted_at', '=', NULL)
            ->where('resellers.reseller_type', 'other')
            ->whereBetween('reselle_balance_log_reports.created_at', [$start, $end])
            ->groupBy('resellers.id')
            ->get()
            ->groupBy('reseller_id');

        // dd($otherResellerMonthlyBillCollection);

        $otherResellerPreviousMonthlyBillCollection = DB::table('reselle_balance_log_reports')
            ->select(DB::raw('sum(amount) as total,clients.pop_id,pops.reseller_id,resellers.name'))
            ->leftJoin('clients', 'clients.id', '=', 'reselle_balance_log_reports.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereIn('clients.pop_id', $pop_ids)
            ->where('clients.deleted_at', '=', NULL)
            ->where('resellers.reseller_type', 'other')
            ->whereBetween('reselle_balance_log_reports.created_at', [$start->subMonth(), $end->subMonth()])
            ->groupBy('resellers.id')
            ->get()
            ->groupBy('reseller_id');

        // dd($otherResellerPreviousMonthlyBillCollection);


        $resellerWiseOtherReport = DB::table('clients')
            ->select(DB::raw('clients.pop_id,reseller_id,resellers.name,package_id,package_rate,count(clients.id) as total_client'))
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->where('pops.bill_generate', 'no')
            ->where('package_rate', '>', 0)
            ->where('clients.deleted_at', '=', NULL)
            ->whereIn('clients.clients_status', ['active', 'expired', 'disable'])
            ->whereIn('clients.pop_id', $pop_ids)
            ->groupBy('package_id', 'reseller_id')
            ->get()
            ->groupBy('reseller_id')
            ->map(function ($item, $key) use ($otherResellerMonthlyBillCollection, $otherResellerPreviousMonthlyBillCollection) {
                if (array_key_exists($key, $otherResellerMonthlyBillCollection->toArray())) {
                    $item->monthly_bill_collection = $otherResellerMonthlyBillCollection[$key]->sum('total');
                } else {
                    $item->monthly_bill_collection = 0;
                }
                if (array_key_exists($key, $otherResellerPreviousMonthlyBillCollection->toArray())) {
                    $item->previous_monthly_bill_collection = $otherResellerPreviousMonthlyBillCollection[$key]->sum('total');
                } else {
                    $item->previous_monthly_bill_collection = 0;
                }
                return $item;
            });

        // dd($resellerWiseOtherReport);

        return $resellerWiseOtherReport;
    }

    public function dashboard()
    {

        $resellers = Reseller::list();
        $pops = Pop::list();
        $pop_ids = Pop::list()->pluck('id');


        $start = now()->firstOfMonth();
        $end = now()->endOfMonth();
        $resellersDashboard = $resellers;

        $monthly_discount = $this->monthlyDiscount($pop_ids);

        $popWiseBillReport = $this->popWiseMonthlyBillReport($pop_ids, $monthly_discount);
        $popWisePayment = $this->popWiseMonthlyBillPayment($pop_ids);



        $temp = [];

        $popWiseCount = [];



        $clientCount = DB::table('clients')
            ->select(DB::raw('count(*) as total,clients_status,client_approval'))
            ->whereIn('pop_id', $pop_ids)
            ->whereNull('deleted_at')
            ->groupBy('clients_status', 'client_approval')
            ->get();

        $deleteClients = Client::list()->onlyTrashed()->count();

        $freeClient = Client::list()->where('is_free', 1)->count();

        $allClient = $clientCount->sum('total');


        $closeClient    = DB::table('clients')->whereIn('pop_id', $pop_ids)->where('deleted_at', '!=', null)->count();
        $newClients     = DB::table('clients')->whereIn('pop_id', $pop_ids)->where('client_approval', 'approved')->whereNull('deleted_at')->whereBetween('created_at', [today()->startOfMonth(), today()->endOfMonth()])->count();
        $thisMonthDeactive = 0; //$this->thisMonthDeactive($pop_ids);


        $currentMonthBillGenerate  = $popWiseBillReport->sum('total_bill');

        // dd($popWiseBillReport,$popWiseBillReport->sum('total_paid'));
        $currentMonthDue = round($popWiseBillReport->sum('total_bill') - $popWiseBillReport->sum('total_paid'));

        $totalDue = $this->totalDue($pop_ids);

        $previousDue = $totalDue - $currentMonthDue;

        $resellerBill = $this->resellerBillInfo();


        // other reseller collection

        $otherResellerBill = $this->resellerBillInfoThisMonth();
        // last 12 month customer summery

        $clientSummary = DB::table('client_summaries')
            ->select('month_year', 'total_active_clients')
            ->orderBy('id', 'desc')
            ->limit(12)
            ->get();

        $today = Carbon::parse(today()->format('Y-m-d 00:00:00'));

        $thenextday = Carbon::parse(today()->addDays(2)->format('Y-m-d 23:59:59'));


        $resultsClientExpireWise = DB::table('clients')
            ->select(DB::raw('DATE(expire_date) as expire_day'), DB::raw('COUNT(*) as total'))
            ->whereIn('pop_id', $pop_ids)
            ->where('client_approval', 'approved')
            ->whereBetween('expire_date', [$today, $thenextday])
            ->groupBy(DB::raw('DATE(expire_date)'))
            ->pluck('total', 'expire_day');


        $clientExpireWise = [];
        $clientExpireWise[0] = $resultsClientExpireWise[today()->format('Y-m-d')] ?? 0;
        $clientExpireWise[1] = $resultsClientExpireWise[today()->addDays(1)->format('Y-m-d')] ?? 0;
        $clientExpireWise[2] = $resultsClientExpireWise[today()->addDays(2)->format('Y-m-d')] ?? 0;


        $popException = Pop::where('experity_check', 'Yes')->count();


        return view('dashboard.new.index', [
            'page_title' => 'Dashboard',
            'resellerDashboard' => $resellersDashboard,
            'resellers' => $resellers,
            // 'monthlyOthersCollection' => $monthlyOthersCollection,
            // 'array' => $array,
            'temp' => $temp,
            'clientCount' => $clientCount,
            'deleteClients' => $deleteClients,
            'allClient' => $allClient,
            'closeClient' => $closeClient,
            'newClients' => $newClients,
            'thisMonthDeactive' => $thisMonthDeactive,
            'thisMonthBillGenerate' => $currentMonthBillGenerate,
            'thisMonthBillPaid' => $popWiseBillReport->sum('total_paid'),
            'previous_due' => $previousDue,
            'total_due' => $totalDue,
            'currentMonthDue' => $currentMonthDue,
            'currentMonthDiscount' => $monthly_discount->sum('total_discount'),
            'todayBillCollection' => $this->todayBillCollection($pop_ids),
            'pops' => $pops,
            'popWiseCount' => $popWiseCount,
            // 'popWiseOtherCollection' => $popWiseOtherCollection,
            'popWiseBillReport' => $popWiseBillReport,
            'resellerWiseOtherReport' => $this->resellerWiseOtherReport($pop_ids),
            'freeClient' => $freeClient,
            // 'paymentReport' => $paymentReport,
            'resellerCurrentMonthBill' => $resellerBill['reseller_bill'],
            'resellerCurrentMonthCollection' => $resellerBill['reseller_collection'],
            'resellerCurrentMonthDue' => $resellerBill['reseller_bill'] - $resellerBill['reseller_collection'],
            'otherResellerBillingInfo' => $otherResellerBill,
            'clientSummary' => $clientSummary,
            'localApproxBill' => $this->localApproximateBillAmount($pop_ids),
            'clientExpireWise' => $clientExpireWise,
            'popException' => $popException,
            'thisMonthBillCollection' => $this->thisMonthBillCollection($pop_ids),
            'popWisePayment' => $popWisePayment,
        ]);
    }

    public function getPaymentReport()
    {

        $resellers = Reseller::list();
        $pops = Pop::list();
        $pop_ids = Pop::list()->pluck('id');

        // dd($pop_ids);


        $paymentReport = Billpayment::with('clients', 'clientsinfo', 'employee')->orderBy('id', 'desc')->limit(10)->get();



        return view('dashboard.new.paymentReport', compact('paymentReport'));
    }


    public function getTokenReport()
    {
        $start = now()->firstOfMonth();
        $end = now()->endOfMonth();
        $token = $this->tokenCount($start, $end);
        $createLink = route('add-token');
        return view('dashboard.token', compact('token', 'createLink'));
    }

    public function tokenCount($start, $end)
    {
        $start_of_month = Carbon::parse(now()->firstOfMonth())->format('Y-m-d 00:00:00');
        $end_of_month = Carbon::parse(now()->endOfMonth())->format('Y-m-d 23:59:59');

        $todayTokenCreate = DB::select("select COUNT(*) as total,createdBy from tokens where date(created_at) = CURDATE() GROUP BY createdBy;");
        $todayTokenClose  = DB::select("select COUNT(*) as total,closeUserId from tokens where date(created_at) = CURDATE() and closeUserId is not null GROUP BY closeUserId;");
        $tokenCreate = DB::select("select COUNT(*) as total,createdBy from tokens where date(created_at) = CURDATE()-1 GROUP BY createdBy;");
        $tokenClose  = DB::select("select COUNT(*) as total,closeUserId from tokens where date(created_at) = CURDATE()-1 and closeUserId is not null GROUP BY closeUserId;");
        $tokenAssign = DB::select("select COUNT(*) as total,assignToken from tokens where date(close_time) = CURDATE()-1 GROUP BY assignToken;");
        $yestardayCreated = DB::select("select COUNT(*) as total from tokens where date(created_at) = CURDATE()-1");
        $yestardayClosed = DB::select("select COUNT(*) as total from tokens where date(created_at) = CURDATE()-1 and closeUserId is not null");

        $monthlyCreated = DB::select("select COUNT(*) as total from tokens where date(created_at) BETWEEN '$start' AND '$end'");
        $monthlyClosed = DB::select("select COUNT(*) as total from tokens where date(created_at) BETWEEN '$start' AND '$end' and closeUserId is not null");
        // $monthlyPending = DB::select("select COUNT(*) as total from tokens where closeUserId is null");
        $avgCloseTime = DB::select("select (sum(TIMESTAMPDIFF(HOUR, created_at, close_time))) / COUNT(*) as avg_time from tokens WHERE `created_at` BETWEEN '$start_of_month' AND '$end_of_month' AND close_time is NOT null");
        $monthlyPending = DB::select("select COUNT(*) as total from tokens where date(created_at) BETWEEN '$start' AND '$end' and closeUserId is null");
        $todayPending = DB::select("select COUNT(*) as total from tokens where date(created_at) = CURDATE() and closeUserId is null");
        $yesterdayPending = DB::select("select COUNT(*) as total from tokens where date(created_at) = CURDATE() - INTERVAL 1 DAY and closeUserId is null");
        $lastWeekPending = DB::select("select COUNT(*) as total from tokens where date(created_at) BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE() and closeUserId is null");

        $users = user_list();
        $userWiseTokenReport = [];
        foreach ($users as $user) {
            foreach ($tokenCreate as $tcreate) {
                if ($tcreate->createdBy == $user->id) {
                    $userWiseTokenReport[$user->id]['tokenCreate'] = $tcreate->total;
                }
            }
            foreach ($tokenClose as $tclose) {
                if ($tclose->closeUserId == $user->id) {
                    $userWiseTokenReport[$user->id]['tokenClose'] = $tclose->total;
                }
            }
            if (isset($userWiseTokenReport[$user->id]['tokenCreate']) ||  isset($userWiseTokenReport[$user->id]['tokenClose'])) {
                $userWiseTokenReport[$user->id]['name'] = $user->name;
            }
        }

        $employees = employee_list();
        $employWiseTokenReport = [];
        foreach ($employees as $employee) {
            foreach ($tokenAssign as $tassign) {
                if ($tassign->assignToken == $employee->id) {
                    $employWiseTokenReport[$employee->id]['assignToken'] = $tassign->total;
                }
            }
            if (isset($employWiseTokenReport[$employee->id]['assignToken'])) {
                $employWiseTokenReport[$employee->id]['name'] = $employee->name;
            }
        }

        $popTokens = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d'))->groupBy('pops.id')->get();

        $closeToken = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d'))
            ->whereNotNull('tokens.closeUserId')->groupBy('pops.id')->get();

        $pendingToken = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d'))
            ->whereNull('tokens.closeUserId')->groupBy('pops.id')->get();

        $popName = Pop::list();
        $areaName = [];

        foreach ($popName as $pop) {
            foreach ($popTokens as $popToken) {
                if ($popToken->id == $pop->id) {
                    $areaName[$pop->id]['total'] = $popToken->total;
                }
            }
            foreach ($pendingToken as $p) {
                if ($p->id == $pop->id) {
                    $areaName[$pop->id]['ptotal'] = $p->total;
                }
            }
            foreach ($closeToken as $ctoken) {
                if ($ctoken->id == $pop->id) {
                    $areaName[$pop->id]['ctotal'] = $ctoken->total;
                }
            }
            if (isset($areaName[$pop->id]['total'])) {
                $areaName[$pop->id]['name'] = $pop->popname;
            }
        }

        $areaForLastdayTokens = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d', strtotime('-1 day')))
            // ->whereNull('tokens.closeUserId')
            ->groupBy('pops.id')->get();

        $lastdayTotalTokens = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d', strtotime('-1 day')))
            ->groupBy('pops.id')->get();

        $lastDayPendingTokens = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d', strtotime('-1 day')))
            ->whereNull('tokens.closeUserId')->groupBy('pops.id')->get();

        $lastDayClosedTokens = DB::table('tokens')
            ->select(DB::raw('COUNT(*) as total, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d', strtotime('-1 day')))
            ->whereNotNull('tokens.closeUserId')->groupBy('pops.id')->get();

        $lastDayPendingTokenDate = DB::table('tokens')
            ->select(DB::raw('tokens.created_at as lastday, pops.id, pops.popname'))
            ->join('clients', 'clients.id', '=', 'tokens.clientId')
            ->join('pops', 'pops.id', '=', 'clients.pop_id')
            ->join('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->whereDate('tokens.created_at', '=', date('Y-m-d', strtotime('-1 day')))
            ->whereNull('tokens.closeUserId')
            ->groupBy('pops.id')
            ->get();

        $prevoiusDayToken = [];

        foreach ($popName as $pop) {
            foreach ($areaForLastdayTokens as $areaForLastdayToken) {
                if ($areaForLastdayToken->id == $pop->id) {
                    $prevoiusDayToken[$pop->id]['total'] = $areaForLastdayToken->total;
                }
            }

            foreach ($lastdayTotalTokens as $lastdayTotalToken) {
                if ($lastdayTotalToken->id == $pop->id) {
                    $prevoiusDayToken[$pop->id]['lastdayTotalToken'] = $lastdayTotalToken->total;
                }
            }
            foreach ($lastDayPendingTokens as $lastpToken) {
                if ($lastpToken->id == $pop->id) {
                    $prevoiusDayToken[$pop->id]['lastpToken'] = $lastpToken->total;
                }
            }
            foreach ($lastDayClosedTokens as $lastCToken) {
                if ($lastCToken->id == $pop->id) {
                    $prevoiusDayToken[$pop->id]['lastCToken'] = $lastCToken->total;
                }
            }

            foreach ($lastDayPendingTokenDate as $lastpTokenDate) {
                if ($lastpTokenDate->id == $pop->id) {
                    $prevoiusDayToken[$pop->id]['lastpTokenDate'] = $lastpTokenDate->lastday;
                }
            }

            if (isset($prevoiusDayToken[$pop->id]['total'])) {
                $prevoiusDayToken[$pop->id]['name'] = $pop->popname;
            }
        }

        return  [
            'todayCreated'          => collect($todayTokenCreate),
            'todayClosed'           => collect($todayTokenClose),
            'yestardayCreated'      => collect($yestardayCreated),
            'yestardayClosed'       => collect($yestardayClosed),
            'monthlyCreated'        => collect($monthlyCreated),
            'monthlyClosed'         => collect($monthlyClosed),
            'userWiseTokenReport' => collect($userWiseTokenReport),
            'employWiseTokenReport' => collect($employWiseTokenReport),
            'areaName'              => collect($areaName),
            'prevoiusDayToken'      => collect($prevoiusDayToken),
            'monthlyPending'          => collect($monthlyPending),
            'avgCloseTime'          => collect($avgCloseTime),
            'todayPending'          => collect($todayPending),
            'yesterdayPending'      => collect($yesterdayPending),
            'lastWeekPending'       => collect($lastWeekPending),
        ];
    }

    public function currentMonthAdvancedPayment($pop_ids, $total = false)
    {
        $query = DB::table('bill_generates')
            ->select('resellers.name as reseller_name', 'resellers.id as reseller_id', 'pops.popname as pop_name', 'pops.id as pop_id', DB::raw('sum(bill_amount) as totalAdvanced'), 'billing_type', DB::raw('count(*) as total_id'))
            ->leftJoin('clients', 'clients.id', '=', 'bill_generates.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->where(function ($q) use ($total) {
                if ($total == false) {
                    $q->whereBetween('bill_generates.created_at', [today()->firstOfMonth(), today()->endOfMonth()]);
                }
            })
            ->where('due_amount', '<=', 0)
            // ->whereNull('clients.deleted_at')
            ->where('pops.bill_generate', 'yes')
            ->whereIn('pop_id', $pop_ids)
            ->groupBy('billing_type', 'resellers.id', 'pops.id')
            ->orderBy('resellers.id', 'asc')
            ->get();
        return $query;
    }




    public function previousDue()
    {
        $totalPreviousBill = DB::table('bill_generates')->where('created_at', '<', today()->firstOfMonth())->sum('bill_amount');
        $totalPreviousPaid = DB::table('bill_payments')->where('created_at', '<', today()->firstOfMonth())->select(DB::raw('sum(paid_amount) + sum(discount_amount) as total_paid'))->first();

        return $totalPreviousBill - $totalPreviousPaid->total_paid;
    }


    public function previousBillGenerate()
    {
        return DB::table('bill_generates')->where('created_at', '<', today()->firstOfMonth())->sum('bill_amount');
    }

    public function previousBillPaid()
    {
        return DB::table('bill_payments')->where('created_at', '<', today()->firstOfMonth())->select(DB::raw('sum(paid_amount) as totalPreviousPaid, sum(discount_amount) as totalPreviouslyDiscount'))->first();
    }


    public function thisMonthDeactive($pop_ids)
    {
        $deactive_from_users_log = User_log::selectRaw('MAX(id),client_id,created_at,log_type,user_id')
            ->where('log_type', 'id_deactive')
            ->whereBetween('created_at', [today()->firstOfMonth(), today()->endOfMonth()])
            ->groupBy('client_id')
            ->pluck('client_id');

        return Client::whereIn('id', $deactive_from_users_log)->whereIn('pop_id', $pop_ids)->where('clients_status', 'deactive')->count();
    }

    public function BillGenerate($pop_ids, $total = false)
    {



        $query = DB::table('bill_generates')
            ->select('resellers.name as reseller_name', 'resellers.id as reseller_id', 'pops.popname as pop_name', 'pops.id as pop_id', DB::raw('sum(bill_amount) as bill_total'), 'billing_type', DB::raw('count(*) as total_id'))
            ->leftJoin('clients', 'clients.id', '=', 'bill_generates.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->where(function ($q) use ($total) {
                if ($total == false) {
                    $q->whereBetween('bill_generates.created_at', [today()->firstOfMonth(), today()->endOfMonth()]);
                }
            })
            ->where('clients.deleted_at', null)
            ->whereIn('pop_id', $pop_ids)
            ->where('pops.bill_generate', 'yes')
            ->groupBy('billing_type', 'resellers.id', 'pops.id')
            ->orderBy('resellers.id', 'asc')
            ->get();

        return $query;
    }



    public function BillPayment($pop_ids, $total = '')
    {
        $query = DB::table('bill_payments')
            ->leftJoin('incomes', 'incomes.id', '=', 'bill_payments.income_id')
            ->leftJoin('income_heads', 'income_heads.id', '=', 'incomes.incomeHead')
            ->leftJoin('clients', 'clients.id', '=', 'bill_payments.client_id')
            ->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
            ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
            ->where(function ($q) use ($total) {
                if ($total == false) {
                    $q->whereBetween('bill_payments.created_at', [today()->startOfMonth(), today()->endOfMonth()]);
                }
            })
            ->whereIn('pop_id', $pop_ids)
            ->select('resellers.name as reseller_name', 'resellers.id as reseller_id', 'pops.popname as pop_name', 'pops.id as pop_id', DB::raw('sum(paid_amount) as paid_total'), DB::raw('sum(discount_amount) as total_discount'), 'income_heads.id as income_head_id', DB::raw('count(*) as total_id'))
            ->where('pops.bill_generate', 'yes')
            ->where('clients.deleted_at', null)
            ->groupBy('income_heads.id', 'resellers.id', 'pops.id')
            ->get();

        return $query;
    }

    public function closedCustomerDue()
    {
        $total = DB::table('customer_accounts')
            ->leftJoin('clients', 'clients.id', '=', 'customer_accounts.client_id')
            ->leftJoin('pops', 'clients.pop_id', '=', 'pops.id')
            ->select(DB::raw('sum(dueAmount) as total_close_due'))
            ->where('clients.client_approval', 'Approved')
            ->where('pops.bill_generate', 'yes')
            ->where('clients.deleted_at', null)
            ->where('dueAmount', '>', 0)
            ->first();


        if ($total->total_close_due != null) {
            return $total->total_close_due;
        } else {
            return 0;
        }
    }

    public function totalCustomerBalanceAdjustment($type = '')
    {
        $total = DB::table('customer_balance_adjustment')
            ->leftJoin('clients', 'clients.id', '=', 'customer_balance_adjustment.client_id')
            ->leftJoin('pops', 'clients.pop_id', '=', 'pops.id')
            ->where(function ($q) use ($type) {
                if ($type == 'previous') {
                    $q->whereDate('customer_balance_adjustment.created_at', '<', today()->firstOfMonth());
                }
            })
            ->select(DB::raw('sum(amount) as total_adjustment'))
            ->where('pops.bill_generate', 'yes')
            ->whereNull('clients.deleted_at')
            ->first();
        return $total->total_adjustment;
    }

    public function totalAdvanced()
    {
        $total = DB::table('customer_accounts')
            ->leftJoin('clients', 'clients.id', '=', 'customer_accounts.client_id')
            ->leftJoin('pops', 'clients.pop_id', '=', 'pops.id')
            ->select(DB::raw('sum(dueAmount) as total_advanced'))
            ->where('clients.client_approval', 'approved')
            ->where('pops.bill_generate', 'yes')
            ->whereNull('clients.deleted_at')
            ->where('dueAmount', '<=', 0)
            ->first();
        if ($total->total_advanced != null) {
            return $total->total_advanced;
        } else {
            return 0;
        }
    }

    public function resellerBillInfo()
    {
        if (checkSettings('local_client_and_reseller_client_bill') == 'enable' && auth()->user()->hasRole('Admin')) {
            $total_active_customer_bill_other_reseller = DB::select("
            SELECT SUM(packages.package_rate) as total_bill
            FROM clients as c
            LEFT JOIN pops ON c.pop_id = pops.id
            LEFT JOIN packages ON c.package_id = packages.id
            WHERE c.deleted_at IS NULL
            AND c.clients_status = 'active'
            AND pops.bill_generate = 'no';
            ");

            // To retrieve the sum value:
            $totalBill = $total_active_customer_bill_other_reseller[0]->total_bill ?? 0;

            $from = today()->startOfMonth();
            $to = today()->endOfMonth();

            $other_reseller_total_bill_collection_this_month = DB::select("
                SELECT SUM(amount) as total_amount
                FROM reselle_balance_log_reports AS rblr
                LEFT JOIN resellers ON rblr.reseller_id = resellers.id
                WHERE resellers.reseller_type = 'other'
                AND rblr.created_at BETWEEN ? AND ?;
            ", [$from, $to]);



            $totalAmount = $other_reseller_total_bill_collection_this_month[0]->total_amount ?? 0;
            // dd($totalAmount);

            $data = [
                'reseller_bill' => round($totalBill ?? 0, 2),
                'reseller_collection' => round($totalAmount ?? 0, 2),
            ];
            // dd($data);
            return $data;
        } else {
            $data = [
                'reseller_bill' => 0,
                'reseller_collection' => 0,
            ];
            return $data;
        }
    }

    public function resellerBillInfoThisMonth()
    {
        $start = Carbon::parse(now()->firstOfMonth())->format('Y-m-d 00:00:00');
        $end = Carbon::parse(now()->endOfMonth())->format('Y-m-d 23:59:59');

        $to_day_start = Carbon::now()->format('Y-m-d 00:00:00');
        $to_day_end = Carbon::now()->format('Y-m-d 23:59:59');

        $resellerIds = Reseller::list()->where('reseller_type', '=', 'other')->pluck('id');
        $total_bill_this_month = DB::table('reseller_bill_generates')
            ->whereIn('reseller_id', $resellerIds)
            ->whereBetween('created_at', [$start, $end])
            ->sum('amount');
        // dd($total_bill_this_month,$resellerIds);
        $total_bill_paid_this_month = DB::table('reseller_bill_generates')
            ->whereIn('reseller_id', $resellerIds)
            ->whereBetween('created_at', [$start, $end])
            ->sum('paid_amount');
        $total_due_amont =  DB::table('reseller_bill_generates')
            ->whereIn('reseller_id', $resellerIds)

            ->sum('due_amount');
        $total_discount_this_month = DB::table('reseller_bill_payments')
            ->whereIn('reseller_id', $resellerIds)
            ->whereBetween('created_at', [$start, $end])
            ->sum('discount_amount');
        // dd($total_discount_this_month);
        $to_day_bill_collection = DB::table('reseller_bill_payments')
            ->whereIn('reseller_id', $resellerIds)
            ->whereBetween('created_at', [$to_day_start, $to_day_end])
            ->sum('paid_amount');
        $this_month_bill_collection = DB::table('reseller_bill_payments')
            ->whereIn('reseller_id', $resellerIds)
            ->whereBetween('created_at', [$start, $end])
            ->sum('paid_amount');
        // dd($total_due_amont);

        return [
            'total_bill_this_month' => $total_bill_this_month,
            'total_bill_paid_this_month' => $total_bill_paid_this_month,
            'total_due_amount_this_month' => $total_bill_this_month - $total_bill_paid_this_month,
            'total_due_amount' => $total_due_amont,
            'previous_due' => $total_due_amont - ($total_bill_this_month - $total_bill_paid_this_month),
            'total_discount_this_month' => $total_discount_this_month,
            'to_day_bill_collection' => $to_day_bill_collection,
            'this_month_bill_collection' => $this_month_bill_collection,
        ];
    }
}
