<?php

namespace App\Http\Controllers;

use App\Classes\Accounting\Accounting;
use App\Classes\MikrotikService\SyncWithMk;
use App\Models\BillGenerate;
use App\Models\Billpayment;
use App\Models\Client;
use App\Models\Packages;
use App\Models\Pop;
use App\Models\Reseller;
use App\Models\User;
use App\Services\ExpirationService;
use Brian2694\Toastr\Facades\Toastr;
use Carbon\Carbon;
use Facade\Ignition\Support\Packagist\Package;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ReportController extends Controller
{
    public function dueAmountCheckByMonth()
    {
        $start = now()->startOfMonth()->format('Y-m-d 00:00:00');
        $end = now()->endOfMonth()->format('Y-m-d 23:59:59');
        $client = Client::list()->with([
            'paymentBill' => function ($query) use ($start, $end) {
                $query
                    ->whereBetween('created_at', [$start, $end]);
            },
            'generatedBill' => function ($query) use ($start, $end) {
                $query
                    ->whereBetween('created_at', [$start, $end]);
            },
            'clientsinfo',
            'pop'
        ]);

        $data = [
            'clients' => $client->get(),
            'start' => now()->startOfMonth(),
            'end' => now()->endOfMonth(),

        ];

        return view('report.dueAmountCheckByMonth.index', $data);
    }

    public function dueAmountCheckByMonthSearch(Request $request)
    {

        if (isset($request->reseller_id)) {
            if (isset($request->pop_id) && $request->pop_id == 'all') {
                $pop_id = Pop::where('reseller_id', $request->reseller_id)->pluck('id');
            } else {
                $pop_id = Pop::where('id', $request->pop_id)->pluck('id');
            }
        } else {

            $reseller_id = Reseller::list()->where('reseller_type', '==', 'own')->pluck('id');
            $pop_id = Pop::whereIn('reseller_id', $reseller_id)->pluck('id');
        }

        $start = Carbon::parse($request->from_date)->format('Y-m-d 00:00:00');
        $end = Carbon::parse($request->to_date)->format('Y-m-d 23:59:59');

        $client = Client::list()->with([
            'paymentBill' => function ($query) use ($start, $end) {
                $query
                    ->whereBetween('created_at', [$start, $end]);
            },
            'generatedBill' => function ($query) use ($start, $end) {
                $query
                    ->whereBetween('created_at', [$start, $end]);
            },
            'clientsinfo',
            'pop'
        ])->whereIn('pop_id', $pop_id);

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

        return view('report.dueAmountCheckByMonth.result', $data);
    }


    public function duplicateMoneyReceipt()
    {
        //get all bill payment where money receipt number is duplicate
        $billPayments = DB::select(DB::raw("SELECT money_receipt_number, GROUP_CONCAT(client_id) as clients_id, COUNT(*) AS count_money_receipt_number
        FROM bill_payments
        GROUP BY money_receipt_number
        HAVING count_money_receipt_number > 1;"));

        $data = [
            'billPayments' => $billPayments
        ];
        return view('report.duplicateMoneyRecipt.list', $data);
    }

    public function expireButOnline()
    {
        if (!auth()->user()->hasPermissionTo('expire-but-online-customers')) {
            Toastr::error("Not enough permission");
            return redirect()->back();
        }
        $pop = Pop::where('experity_check', '!=', 'Yes')->get();
        $clients = Client::where('clients_status', 'expired')->whereIn('pop_id', $pop->pluck('id'))->pluck('userid')->toArray();

        $accounting = new Accounting();


        $l = $accounting->totalOnlineUserWithInformation($clients);

        $data = [
            'clients' => $l
        ];
        return view('report.expireButOnline.list', $data);
    }


    public function expireButnotDisconectProciede()
    {
        $pop = Pop::where('experity_check', '!=', 'Yes')->get();
        $clients = Client::where('clients_status', 'expired')->whereIn('pop_id', $pop->pluck('id'))->pluck('userid')->toArray();

        $accounting = new Accounting();

        $l = $accounting->totalOnlineUserWithInformation($clients);

        foreach ($l as $key => $value) {
            $client = Client::where('userid', $value->username)->first();
            $check_expire = (new SyncWithMk())->isDisable($client);

            if ($check_expire) {
                if (globalPermission('RadiusExpiration')) {
                    (new ExpirationService())->syncExpiration($value->username);
                }
                try {
                    SessionRefreshController::userDisconnectProcess($value->username);
                } catch (\Exception $e) {
                }
            }
        }
        return redirect()->back();
    }

    public function packageUploadDownload()
    {
        // $data = [
        //     'packages' => DB::table('packages')->get()
        // ];

        $packages_with_upload_download_from_client = DB::select(DB::raw("SELECT count(*) as count_row, SUM(c.up_bandwidth+c.down_bandwidth) as total_up_down_load,
        SUM(c.up_bandwidth) as up_load, SUM(c.down_bandwidth) as down_load, p.package_name as package_name, p.limite_quantity as limit_quantity FROM clients as c
        LEFT JOIN packages as p on p.id = c.package_id
        WHERE deleted_at is null GROUP BY c.package_id
        having count_row >1
        ORDER BY `total_up_down_load` desc;"));
        // dd($packages_with_upload_download_from_client);
        // return view('report.packageUploadDownload.list', $data);

        $packages = Packages::get();
        $data = [
            'packages' => $packages,
            'packages_with_upload_download_from_clients' => $packages_with_upload_download_from_client
        ];
        return view('packages.updownReport.list', $data);
    }

    public function searchWithPackageUploadDownLoad(Request $request)
    {
        if ($request->reseller != 'all') {
            if ($request->pop != 'all') {
                $pop = Pop::where('id', $request->pop)->pluck('id');
            } else {
                $pop = Pop::where('reseller_id', $request->reseller)->pluck('id');
            }
        } else {

            $pop = Pop::list()->pluck('id');
        }
        // dd($request->all());
        // dd($request->package_id);
        if ($request->package_id == null) {
            $clients = Client::list()->whereIn('pop_id', $pop)->orderBy('down_bandwidth', 'desc')->get();
        } else {
            $clients = Client::list()->where('package_id', $request->package_id)->whereIn('pop_id', $pop)->orderBy('down_bandwidth', 'desc')->get();
        }
        // $clients = Client::list()->where('package_id',$request->package_id)->orderBy('down_bandwidth','desc')->get();
        $data = [
            'list' => $clients,
            'package_limit' => Packages::find($request->package_id)->limite_quantity ?? 0,
        ];
        // dd($data);
        return view('packages.updownReport.result', $data);
    }

    public function permanentDiscountList()
    {
        $client = Client::list()->where('parmanent_discount', '>', 0)->get();
        $data = [
            'clients' => $client
        ];
        //    dd($client->toArray());
        return view('report.permanentDiscountList', $data);
    }

    public function runningMonthDue()
    {
        $from = Carbon::parse(now()->startOfMonth())->format('Y-m-d 00:00:00');
        $end = Carbon::parse(now()->lastOfMonth())->format('Y-m-d 23:59:59');

        $payment_list = Billpayment::whereBetween('created_at', [$from, $end])->pluck('client_id');

        $running_month_due_client = BillGenerate::whereBetween('created_at', [$from, $end])->where('billing_type', 'monthly')->whereNotIn('client_id', $payment_list)->pluck('client_id');


        $pop_list = Pop::conditionalList()->where('bill_generate', 'yes')->pluck('id');
        $clients  = Client::with('packages', 'pop', 'clientsinfo', 'customerAccount', 'subpack', 'thisMonthBill')
            ->whereHas('customerAccount', function ($query) {
                $query->where('dueAmount', '>', 0);
            })
            ->whereIn('pop_id', $pop_list)
            ->whereIn('id', $running_month_due_client);

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


        return view('report.runningDue.report', $data);
    }

    public function disconnectAllExpireOnlineClient()
    {
        $pop = Pop::where('experity_check', '=', 'Yes')->get();
        $clients = Client::where('clients_status', 'expired')->whereIn('pop_id', $pop->pluck('id'))->pluck('userid')->toArray();

        $accounting = new Accounting();

        $accounting = $accounting->totalOnlineUserWithInformation($clients);

        foreach ($accounting as $key => $value) {


            $check_expire_ip_expire = str_starts_with($value->framedipaddress, '172.');
            if ($check_expire_ip_expire) {
                // dd($value);

                try {
                    SessionRefreshController::userDisconnectProcess($value->username);
                } catch (\Exception $e) {
                }
            }
        }
        return redirect()->back();
    }

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

        // Ensure proper querying
        $clients = Client::query()->whereBetween('created_at', [$start, $end])->get();


        // Get users who have the "Reseller" role
        $users = User::with(['resellers', 'roles'])
            ->whereHas('roles', function ($q) {
                $q->where('name', 'Reseller');
            })
            ->get();

        $data = [
            'clients' => $clients,
            'users' => $users,
            'start' => $start,
            'end' => $end,
            'from' => now()->startOfMonth(),
            'to' => now()->endOfMonth()
        ];
        return view('report.marketingNewClient.search', $data);
    }

    public function kameReportSearch(Request $request)
    {
        $start = Carbon::parse($request->from)->format('Y-m-d 00:00:00');
        $end = Carbon::parse($request->to)->format('Y-m-d 23:59:59');

        // Ensure proper querying
        $clients = Client::query()->whereBetween('created_at', [$start, $end])->get();


        // Get users who have the "Reseller" role
        $users = User::with(['resellers', 'roles'])
            ->whereHas('roles', function ($q) {
                $q->where('name', 'Reseller');
            })
            ->get();

        $data = [
            'clients' => $clients,
            'users' => $users,
            'start' => $start,
            'end' => $end,
            'from' => now(),
            'to' => now()->endOfMonth()
        ];
        return view('report.marketingNewClient.result', $data);
    }

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

        // Ensure proper querying
        $clients = Client::query()->whereBetween('created_at', [$start, $end])->get();


        // Get users who have the "Reseller" role
        $users = User::with(['resellers', 'roles'])
            ->whereHas('roles', function ($q) {
                $q->where('name', 'Reseller');
            })
            ->where('id', $userId)
            ->first();

         

        $data = [
            'clients' => $clients,
            'users' => $users,
            'start' => $start,
            'end' => $end,
            'from' => now()->startOfMonth(),
            'to' => now()->endOfMonth()
        ];
        return view('report.marketingNewClient.details.search', $data);
    }
}
