<?php

namespace App\Classes\Accounting;

use App\Models\Client;
use Illuminate\Support\Facades\DB;


class Accounting extends ActiveServers
{

    public function __construct()
    {
        parent::__construct();
    }

    public function lastOnlineOfSingleUser($userid)
    {
        $last_online = null;
        $latest_acctupdatetime = null;

        $this->runQuery(function ($server) use (&$latest_acctupdatetime, &$last_online, &$userid) {

            $log = $server->table('radacct')
                ->where('username', $userid)
                ->whereNull('acctstoptime')
                ->orderBy('acctupdatetime', 'DESC')
                ->first();

            if ($log && $log->acctupdatetime) {
                $log_acctupdatetime = strtotime($log->acctupdatetime);

                if (is_null($latest_acctupdatetime) || $log_acctupdatetime > $latest_acctupdatetime) {
                    $last_online = $log;
                    $latest_acctupdatetime = $log_acctupdatetime;
                }
            }
        });



        return $last_online;
    }

    public function setAllOnlineUserOfflineToSoftware()
    {
        $this->runQuery(function ($server) {
            $server->table('radacct')
                ->whereNull('acctstoptime')
                ->update(['acctstoptime' => now()]);
        });
    }


    public function setSingleUserOfflineToSoftware($userid)
    {

        $this->runQuery(function ($server) use (&$userid) {
            $server->table('radacct')
                ->where('username', $userid)
                ->whereNull('acctstoptime')
                ->delete();
        });
    }

    public function totalUserMac()
    {
        $all_user_mac = [];

        $this->runQuery(function ($server) use (&$all_user_mac) {
            $logs = $server->table('radacct')
                ->select('username', 'callingstationid')
                ->whereNotNull('callingstationid')
                ->distinct()
                ->get()          // full rows
                ->toArray();     // array of stdClass

            $all_user_mac = array_merge($all_user_mac, $logs);
        });

        // Remove duplicates using callingstationid
        // $unique = [];

        // foreach ($all_user_mac as $row) {
        //     $unique[$row->callingstationid] = $row;
        // }

        return array_values($all_user_mac); // full unique rows
    }

    public function totalOnlineUser()
    {
        $all_online_user = [];

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$all_online_user) {
            $logs = $server->table('radacct')
                ->whereNull('acctstoptime')
                ->pluck('username')
                ->toArray(); // Ensure it's an array

            // Merge the result with the main array of online users
            $all_online_user = array_merge($all_online_user, $logs);
        });

        // Return the count of unique online users
        return array_unique($all_online_user);
    }

    public function totalOnlineUserWithInformation($chunk)
    {
        $chunk = is_array($chunk) ? $chunk : explode(',', $chunk);

        // Initialize an empty collection for merged results
        $mergedResults = collect();

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$mergedResults, $chunk) {
            // $logs = $server->table('radacct')
            //     ->select(
            //         'clientsinfo.clients_name',
            //         'radacct.radacctid',
            //         'radacct.username',
            //         'radacct.nasipaddress',
            //         'radacct.nasporttype',
            //         'radacct.acctstarttime',
            //         'radacct.acctupdatetime',
            //         'radacct.callingstationid',
            //         'radacct.framedipaddress',
            //         'radacct.acctterminatecause',

            //     )
            //     ->leftJoin('clients', 'radacct.username', '=', 'clients.userid')
            //     ->leftJoin('clientsinfo', 'clients.id', '=', 'clientsinfo.client_id') // Fixed alias
            //     ->whereNull('radacct.acctstoptime')
            //     ->whereIn('radacct.username', $chunk)
            //     ->orderBy('radacct.acctupdatetime', 'desc') // Fetch latest records first
            //     ->get();

            $logs = $server->table('radacct')
                ->select(
                    'radius.clientsinfo.clients_name',
                    'radacct.radacctid',
                    'radacct.username',
                    'radacct.nasipaddress',
                    'radacct.nasporttype',
                    'radacct.acctstarttime',
                    'radacct.acctupdatetime',
                    'radacct.callingstationid',
                    'radacct.framedipaddress',
                    'radacct.acctterminatecause'
                )
                ->leftJoin('radius.clients', function ($join) {
                    $join->on(DB::raw('radacct.username COLLATE utf8mb4_unicode_ci'), '=', DB::raw('radius.clients.userid COLLATE utf8mb4_unicode_ci'));
                })
                ->leftJoin('radius.clientsinfo', 'radius.clients.id', '=', 'radius.clientsinfo.client_id')
                ->whereNull('radacct.acctstoptime')
                ->whereIn('radacct.username', $chunk)
                ->orderBy('radacct.acctupdatetime', 'desc')
                ->get();


            // Merge results
            $mergedResults = $mergedResults->merge($logs);
        });

        // Get unique users based on latest acctupdatetime
        $uniqueResult = $mergedResults
            ->groupBy('username')
            ->map(fn($group) => $group->sortByDesc('acctupdatetime')->first())
            ->values(); // Reset array keys

        return $uniqueResult;
    }


    public function OnlineUsers($clientList)
    {
        $all_on_line_user = [];

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$all_on_line_user, &$clientList) {
            $logs = $server->table('radacct')
                ->select('username')
                ->whereNull('acctstoptime')
                ->whereIn('username', $clientList)
                ->pluck('username')
                ->toArray(); // Ensure it's an array

            // Merge the result with the main array of online users
            $all_on_line_user = array_merge($all_on_line_user, $logs);
        });

        // Return the count of unique online users
        return array_unique($all_on_line_user);
    }

    public function latestRecords($list)
    {
        $latestRecords = collect();

        // Fetch data from multiple servers
        $this->runQuery(function ($server) use (&$latestRecords, &$list) {
            // Query to fetch data from the server
            $logs = $server->table('radacct')
                ->whereIn('username', $list)
                ->orderBy('username') // Order by username (optional)
                ->orderByDesc('acctupdatetime') // Order by update time descending
                ->get();

            // Merge results
            $latestRecords = $latestRecords->merge($logs);
        });

        // Order by 'acctupdatetime' descending and ensure unique usernames
        $latestRecords = $latestRecords
            ->sortByDesc('acctupdatetime') // Sort by acctupdatetime descending
            ->unique('username'); // Ensure unique records by username

        return $latestRecords->values(); // Reset keys before returning
    }



    public function lastRecord($username, $max_radacctid)
    {
        $record = null;

        // Running query to check both databases
        $this->runQuery(function ($server) use (&$record, &$username, &$max_radacctid) {
            // Query the database
            $result = $server->table('radacct')
                ->where('username', $username)
                ->where('radacctid', $max_radacctid)
                ->first();

            // If record is found, store it and break the loop (if multiple databases)
            if ($result) {
                $record = $result;
                return false; // This breaks out of the loop in runQuery (if applicable)
            }
        });

        // Return the found record (or null if not found)
        return $record;
    }


    public function singleUserLog($userid, $from, $to)
    {
        $total_upload = 0;
        $total_download = 0;

        // Running query to fetch data
        $this->runQuery(function ($server) use (&$total_upload, &$total_download, $userid, $from, $to) {
            $logs = $server->table('radacct')
                ->selectRaw('SUM(acctinputoctets) as sum_of_upload, SUM(acctoutputoctets) as sum_of_download')
                ->where('username', $userid)
                ->whereBetween('acctstarttime', [$from, $to])
                ->first();

            if ($logs) {
                $total_upload += $logs->sum_of_upload ?? 0; // Handle null values
                $total_download += $logs->sum_of_download ?? 0; // Handle null values
            }
        });

        return [
            'upload' => $total_upload,
            'download' => $total_download,
        ];
    }

    public function singleUserLogRecord($userid, $from, $to)
    {
        $mergedResults = collect();

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$mergedResults, &$userid, &$from, &$to) {
            $logs = $server->table('radacct')
                ->select('acctuniqueid', 'acctoutputoctets', 'acctinputoctets', 'radacctid', 'acctstoptime', 'username', 'nasipaddress', 'nasporttype', 'acctstarttime', 'acctupdatetime', 'callingstationid', 'framedipaddress', 'acctterminatecause')
                ->where('username', $userid)
                ->whereBetween('acctstarttime', [$from, $to])
                ->orderBy('radacctid', 'desc')
                ->get();


            // Merge the result with the main collection of online users
            $mergedResults = $mergedResults->merge($logs);
        });

        return $mergedResults;
    }




    public function singleUserDisconnect($userid)
    {

        $mergedLogs = collect();

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$mergedLogs, &$userid) {
            $logs = $server->table('radacct')
                ->select('nasipaddress')
                ->whereNull('acctstoptime')
                ->where('username', $userid)
                ->get(); // Ensure it's an array


            // Merge the result with the main collection of online users
            $mergedLogs = $mergedLogs->merge($logs);
        });

        if ($mergedLogs->count() > 0) {
            foreach ($mergedLogs as $radacctLog) {
                $nas = DB::table('nas')->select('nasname', 'secret')->where('nasname', $radacctLog->nasipaddress)->first();
                if ($nas) {

                    $userDisconnect = new UserDisconnect($userid, $nas->nasname, $nas->secret);
                    $userDisconnect->disconnect();
                }
            }

            $client = Client::with('pop:id,nas_id', 'pop.nas:id,nasname,secret')->where('userid', $userid, ['id,pop_id'])->first();

            $userDisconnect = new UserDisconnect($userid, $client->pop->nas->nasname, $client->pop->nas->secret);
            $userDisconnect->disconnect();
        } else {
            $client = Client::with('pop:id,nas_id', 'pop.nas:id,nasname,secret')->where('userid', $userid, ['id,pop_id'])->first();

            $userDisconnect = new UserDisconnect($userid, $client->pop->nas->nasname, $client->pop->nas->secret);
            $userDisconnect->disconnect();
        }

        $this->runQuery(function ($server) use (&$userid) {
            // Fetch logs with null acctstoptime for the given username
            $logs = $server->table('radacct')
                ->select('radacctid', 'nasipaddress') // Include radacctid for updates
                ->whereNull('acctstoptime')
                ->where('username', $userid)
                ->get();

            // Update acctstoptime for each fetched record
            if ($logs->isNotEmpty()) {
                $server->table('radacct')
                    ->whereIn('radacctid', $logs->pluck('radacctid'))
                    ->update(['acctstoptime' => now()]);
            }
        });
    }

    public function setAcctStopTimeNow($userid)
    {
        $this->runQuery(function ($server) use ($userid) {
            $server->table('radacct')
                ->where('username', $userid)
                ->whereNull('acctstoptime')
                ->update(['acctstoptime' => now()]); // Correct update syntax
        });
    }

    public function allOnlineIdNotUpdateOverFiveMinute()
    {
        $all_on_line_user = [];

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$all_on_line_user) {
            $online_clients = $server->table('radacct')
                ->whereNull('acctstoptime')
                ->whereRaw('round(acctsessiontime/acctinterval) is null')
                ->whereRaw('TIMESTAMPDIFF(MINUTE,acctupdatetime,now()) > 5')
                ->whereRaw('acctstarttime < now() + INTERVAL 1 MINUTE')
                ->pluck('username')
                ->toArray(); // Ensure it's an array

            // Merge the result with the main array of online users
            $all_on_line_user = array_merge($all_on_line_user, $online_clients);
        });

        // Return the count of unique online users
        return array_unique($all_on_line_user);
    }

    public function setAcctStopTimeNowToAListOfCustomer($list)
    {
        $this->runQuery(function ($server) use (&$list) {
            $server->table('radacct')
                ->whereIn('username', $list)
                ->whereNull('acctstoptime')
                ->update(['acctstoptime' => now()]); // Correct update syntax
        });
    }

    public function resetAcctStopTimeWithHibruLogic()
    {
        $this->runQuery(function ($server) {
            $server->table('radacct')
                ->whereRaw('acctupdatetime >= (NOW() - INTERVAL 5 MINUTE) and acctstoptime is not null and acctstoptime <= ( NOW() - INTERVAL 10 MINUTE) AND acctstarttime < NOW() + INTERVAL 2 MINUTE')
                ->update([
                    'acctstoptime' => null
                ]);
        });
    }

    public function nullIpLogsUserNames()
    {
        $all_null_users = [];

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$all_null_users) {
            $null_users = $server->table('radacct')
                ->select('username')
                ->whereNull('acctstoptime')
                ->where('framedipaddress', '0.0.0.0')
                ->pluck('username')
                ->toArray();

            // Merge the result with the main array of online users
            $all_null_users = array_merge($all_null_users, $null_users);
        });

        // Return the count of unique online users
        return array_unique($all_null_users);
    }


    public function allDuplicateOnlineUser()
    {
        $mergedResults = collect();

        // Fetch online users
        $this->runQuery(function ($server) use (&$mergedResults) {
            $logs = $server->table('radacct')
                ->select('radacctid', 'username', 'nasipaddress', 'nasporttype', 'acctstarttime', 'acctupdatetime', 'callingstationid', 'framedipaddress', 'acctterminatecause')
                ->whereNull('acctstoptime')
                ->orderBy('radacctid', 'desc')
                ->get();

            $mergedResults = $mergedResults->merge($logs);
        });

        // Find duplicate users
        $duplicateUsers = $mergedResults->groupBy('username')
            ->filter(function ($group) {
                return $group->count() > 1; // Keep groups with more than 1 entry
            })
            ->keys(); // Get only the usernames

        return $duplicateUsers; // Return or process the list of duplicate usernames
    }

    public function deleteSessionIfStartTimeIsInFuture()
    {
        $this->runQuery(function ($server) {
            $server->table('radacct')
                ->where('acctstarttime', '>', now())
                ->delete(); // Correct update syntax
        });
    }


    public function allFiveMinuteOverSecondLogic()
    {
        $all_on_line_user = [];

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$all_on_line_user) {
            $online_clients = $server->table('radacct')
                ->whereNull('acctstoptime')
                ->whereRaw('round(TIMESTAMPDIFF(MINUTE,acctupdatetime,now())) > 5')
                ->pluck('username')
                ->toArray();

            // Merge the result with the main array of online users
            $all_on_line_user = array_merge($all_on_line_user, $online_clients);
        });

        // Return the count of unique online users
        return array_unique($all_on_line_user);
    }

    public function deleteSingleUserFromRadAcct($userId)
    {
        $this->runQuery(function ($server) use ($userId) {
            $server->table('radacct')
                ->where('username', $userId)
                ->whereNull('acctstoptime')
                ->delete();
        });
    }

    public function allOnLineClients()
    {
        $all_on_line_user = [];

        // Running query to fetch online users
        $this->runQuery(function ($server) use (&$all_on_line_user) {
            $logs = $server->table('radacct')
                ->select('username')
                ->whereNull('acctstoptime')
                ->pluck('username')
                ->toArray(); // Ensure it's an array

            // Merge the result with the main array of online users
            $all_on_line_user = array_merge($all_on_line_user, $logs);
        });

        // Return the count of unique online users
        return array_unique($all_on_line_user);
    }
}
