<?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;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\ClientUploadDownloadExport;

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

    public function searchWithPackageUploadDownLoad(Request $request)
    {
        if ($request->reseller != 'all') {
            if ($request->pop != 'all') {
                // Get user accessible POPs and filter by specific POP
                $pop = userPops()->where('id', $request->pop)->pluck('id');
            } else {
                // Get user accessible POPs and filter by reseller
                $pop = userPops()->where('reseller_id', $request->reseller)->pluck('id');
            }
        } else {
            $pop = Pop::list()->pluck('id');
        }
        // dd($request->all());
        // dd($request->package_id);
        $minimumUsage = $request->get('minimum_usage');

        if ($request->package_id == null) {
            $clients = Client::list()->whereIn('pop_id', $pop);
        } else {
            $clients = Client::list()->where('package_id', $request->package_id)->whereIn('pop_id', $pop);
        }

        if ($minimumUsage && is_numeric($minimumUsage)) {
            $minimumUsage = $minimumUsage * 1024; // Convert GB to MB
            $clients = $clients->whereRaw('(IFNULL(clients.down_bandwidth,0) + IFNULL(clients.up_bandwidth,0)) >= ?', [$minimumUsage]);
        }

        $clients = $clients->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 getClientsByPackageDataTable(Request $request)
    {
        // dd($request->all());
        // Get DataTables parameters
        $start = $request->get('start', 0);
        $length = $request->get('length', 25);
        $searchValue = $request->get('search')['value'] ?? '';

        // Get ordering information
        $orderColumn = isset($request->get('order')[0]['column']) ? (int)$request->get('order')[0]['column'] : 10;
        $orderDir = isset($request->get('order')[0]['dir']) ? $request->get('order')[0]['dir'] : 'desc';

        // Column mapping
        $columns = ['userid', 'clients_name', 'contact_no', 'clients_status', 'package_name', 'popname', 'reseller_name', 'expire_date', 'down_bandwidth', 'up_bandwidth', 'total', 'bandwidth_limit'];
        $orderBy = isset($columns[$orderColumn]) ? $columns[$orderColumn] : 'down_bandwidth';

        // Get filter parameters
        $packageId = $request->get('package_id');
        $reseller = $request->get('reseller');
        $pop = $request->get('pop');

        $minimumUsage = $request->get('minimum_usage');
        // Validate and convert minimum usage
        $minimumUsageMB = null;
        if (!empty($minimumUsage) && is_numeric($minimumUsage) && $minimumUsage > 0) {
            $minimumUsageMB = (float)$minimumUsage * 1024; // Convert GB to MB
        }

        // Build POP filter
        if (!empty($reseller) && $reseller != 'all') {
            if (!empty($pop) && $pop != 'all') {
                // Get user accessible POPs and filter by specific POP
                $popIds = userPops()->where('id', $pop)->pluck('id');
            } else {
                // Get user accessible POPs and filter by reseller
                $popIds = userPops()->where('reseller_id', $reseller)->pluck('id');
            }
        } else {
            $popIds = Pop::list()->pluck('id');
        }

        // Build base query with relationships
        $baseQuery = Client::list()
            ->with(['clientsinfo', 'packages', 'pop.reseller'])
            ->whereIn('pop_id', $popIds);

        if ($packageId) {
            $baseQuery->where('package_id', $packageId);
        }

        // Apply minimum usage filter to base query
        if ($minimumUsageMB !== null) {
            $baseQuery->whereRaw('(COALESCE(clients.down_bandwidth, 0) + COALESCE(clients.up_bandwidth, 0)) >= ?', [$minimumUsageMB]);
        }

        // Get total count
        $recordsTotal = $baseQuery->count();

        // Build filtered query
        $query = Client::list()
            ->with(['clientsinfo', 'packages', 'pop.reseller'])
            ->whereIn('pop_id', $popIds);

        if ($packageId) {
            $query->where('package_id', $packageId);
        }

        // Apply minimum usage filter to filtered query
        if ($minimumUsageMB !== null) {
            $query->whereRaw('(COALESCE(clients.down_bandwidth, 0) + COALESCE(clients.up_bandwidth, 0)) >= ?', [$minimumUsageMB]);
        }

        // Apply search filter
        if (!empty($searchValue)) {
            $query->where(function ($q) use ($searchValue) {
                $q->where('userid', 'like', '%' . $searchValue . '%')
                    ->orWhereHas('clientsinfo', function ($q) use ($searchValue) {
                        $q->where('clients_name', 'like', '%' . $searchValue . '%')
                            ->orWhere('contact_no', 'like', '%' . $searchValue . '%');
                    })
                    ->orWhereHas('packages', function ($q) use ($searchValue) {
                        $q->where('package_name', 'like', '%' . $searchValue . '%');
                    })
                    ->orWhereHas('pop', function ($q) use ($searchValue) {
                        $q->where('popname', 'like', '%' . $searchValue . '%')
                            ->orWhereHas('reseller', function ($q) use ($searchValue) {
                                $q->where('name', 'like', '%' . $searchValue . '%');
                            });
                    })
                    ->orWhere('clients_status', 'like', '%' . $searchValue . '%')
                    ->orWhere('expire_date', 'like', '%' . $searchValue . '%');
            });
        }

        // Get filtered count
        $recordsFiltered = $query->count();

        // Apply ordering
        if ($orderBy === 'total') {
            // For total column, order by sum of down_bandwidth + up_bandwidth
            $query->orderByRaw('(clients.down_bandwidth + clients.up_bandwidth) ' . $orderDir);
        } elseif ($orderBy === 'clients_name' || $orderBy === 'contact_no') {
            // For related columns, use join
            $query->leftJoin('clientsinfo', 'clientsinfo.client_id', '=', 'clients.id')
                ->orderBy('clientsinfo.' . $orderBy, $orderDir);
        } elseif ($orderBy === 'package_name') {
            $query->leftJoin('packages', 'packages.id', '=', 'clients.package_id')
                ->orderBy('packages.package_name', $orderDir);
        } elseif ($orderBy === 'popname') {
            $query->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
                ->orderBy('pops.popname', $orderDir);
        } elseif ($orderBy === 'reseller_name') {
            $query->leftJoin('pops', 'pops.id', '=', 'clients.pop_id')
                ->leftJoin('resellers', 'resellers.id', '=', 'pops.reseller_id')
                ->orderBy('resellers.name', $orderDir);
        } else {
            $query->orderBy('clients.' . $orderBy, $orderDir);
        }

        // Apply pagination
        $clients = $query->skip($start)->take($length)->get();

        // Format data for DataTables
        $data = [];
        foreach ($clients as $client) {
            $total = ($client->down_bandwidth ?? 0) + ($client->up_bandwidth ?? 0);
            $data[] = [
                'userid' => $client->userid ?? '',
                'clients_name' => $client->clientsinfo->clients_name ?? '',
                'contact_no' => $client->clientsinfo->contact_no ?? '',
                'clients_status' => $client->clients_status ?? '',
                'package_name' => $client->packages->package_name ?? '',
                'popname' => $client->pop->popname ?? '',
                'reseller_name' => $client->pop->reseller->name ?? '',
                'expire_date' => $client->expire_date ?? '',
                'down_bandwidth' => $client->down_bandwidth ?? 0,
                'up_bandwidth' => $client->up_bandwidth ?? 0,
                'total' => $total,
                'bandwidth_limit' => $client->bandwidth_limit ?? '',
            ];
        }

        return response()->json([
            'draw' => intval($request->get('draw')),
            'recordsTotal' => $recordsTotal,
            'recordsFiltered' => $recordsFiltered,
            'data' => $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);
    }

    public function marketingCustomerSalesReport()
    {
        $data = [
            'from' => now()->startOfMonth(),
            'to' => now()->endOfMonth()
        ];

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

    public function kamSalesReportSearch(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()->withTrashed()->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.marketingSalesClient.result', $data);
    }

    public function exportClientsByPackage(Request $request)
    {
        // Get filter parameters
        $packageId = $request->get('package_id');
        $reseller = $request->get('reseller');
        $pop = $request->get('pop');
        $minimumUsage = $request->get('minimum_usage');

        // Validate and convert minimum usage
        $minimumUsageMB = null;
        if (!empty($minimumUsage) && is_numeric($minimumUsage) && $minimumUsage > 0) {
            $minimumUsageMB = (float)$minimumUsage * 1024; // Convert GB to MB
        }

        // Build POP filter
        if (!empty($reseller) && $reseller != 'all') {
            if (!empty($pop) && $pop != 'all') {
                // Get user accessible POPs and filter by specific POP
                $popIds = userPops()->where('id', $pop)->pluck('id');
            } else {
                // Get user accessible POPs and filter by reseller
                $popIds = userPops()->where('reseller_id', $reseller)->pluck('id');
            }
        } else {
            $popIds = Pop::list()->pluck('id');
        }

        // Build query with relationships
        $query = Client::list()
            ->with(['clientsinfo', 'packages', 'pop.reseller'])
            ->whereIn('pop_id', $popIds);

        if ($packageId) {
            $query->where('package_id', $packageId);
        }

        // Apply minimum usage filter
        if ($minimumUsageMB !== null) {
            $query->whereRaw('(COALESCE(clients.down_bandwidth, 0) + COALESCE(clients.up_bandwidth, 0)) >= ?', [$minimumUsageMB]);
        }

        // Order by total bandwidth descending
        $query->orderByRaw('(COALESCE(clients.down_bandwidth, 0) + COALESCE(clients.up_bandwidth, 0)) DESC');

        // Get all data (no pagination for export)
        $clients = $query->get();

        // Generate filename
        $filename = 'client_upload_download_report_' . date('Y-m-d_His') . '.xlsx';

        return Excel::download(new ClientUploadDownloadExport($clients), $filename);
    }
}
