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


}
