<?php

namespace Modules\Reporting\Http\Controllers;

use Modules\Platform\Companies\Datatables\Scope\CurrentCompanyScope;
use Modules\Platform\Core\Http\Controllers\AppBaseController;
use Modules\Platform\Core\Helper\SettingsHelper;
use Modules\Platform\Core\Helper\UserHelper;
use Modules\Calls\Entities\DirectionType;
use Modules\Platform\User\Entities\User;
use Modules\Calls\Entities\Call;
use Modules\Platform\Core\Datatable\ActivityLogDataTable;
use Carbon\Carbon;
use Carbon\Carbonite;
use Carbon\CarbonPeriod;
use Carbon\CarbonInterval;
use App\Charts\CallsOverallChart;
use App\Networks;
use App\CDR;
use Auth;
use Illuminate\Http\Request;
use DataTables;
use DB;
use Illuminate\Support\Facades\Response;
use Modules\Platform\User\Entities\Group;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Symfony\Component\Process\Process;
use Modules\Platform\Core\Helper\CompanySettings;

// use Illuminate\Foundation\Auth\User;

class ReportingController extends AppBaseController
{
    protected $moduleName = 'reporting';
    protected $SettingsHelper;
    protected $UserHelper;

    public function __construct()
    {
        parent::__construct();
        $this->SettingsHelper = new SettingsHelper();
        $this->UserHelper = new UserHelper();
    }
    public function index()
    {
        // if(!file_exists($dir.'/cookies')){

        // }
        $user                                             = auth()->user();
        $company_users                                  = User::where('company_id', $user->company_id)->count();

        // Queues the current user is allowed to access (same permission logic
        // used across the app).
        $accessibleQueueIds = $user->accessibleQueueIds();

        $queues = Group::whereIn('id', $accessibleQueueIds)
            ->orderBy('id')
            ->get(['id', 'name']);

        $selectedQueue = (int) request()->query('queue');
        if ($selectedQueue > 0 && !in_array($selectedQueue, $accessibleQueueIds)) {
            $selectedQueue = 0;
        }

        $users = $this->UserHelper->getCompanyUsers();
        if ($user->access_to_all_entity != '1') {
            $users = User::whereHas('groups', function ($q) {
                $q->whereIn(
                    'groups.id',
                    auth()->user()->accessibleQueueIds()
                );
            })
                ->whereDoesntHave('roles', function ($q) {
                    $q->where('name', 'company_manager');
                })
                ->visibleInReports()
                ->get();
        }

        if ($selectedQueue > 0) {
            $allowedUserIds = $users->pluck('id');
            $users = User::whereIn('id', $allowedUserIds)
                ->whereHas('groups', function ($q) use ($selectedQueue) {
                    $q->where('groups.id', $selectedQueue);
                })
                ->get();
        }

        $view = view('reporting::index');
        $widgets                = [
            [
                'name'          => 'Agents Online',
                'bg_color'      => 'purple',
                'coll_class'    => 'col-md-2',
                'icon'          => 'group',
                'counter'       => 0
            ],
            [
                'name'          => 'Busy Agents',
                'bg_color'      => 'red',
                'coll_class'    => 'col-md-2',
                'icon'          => 'phone_in_talk',
                'counter'       => 0
            ],
            [
                'name'          => 'Agents On Pause',
                'bg_color'      => 'pink',
                'coll_class'    => 'col-md-2',
                'icon'          => 'coffee',
                'counter'       => 0
            ],
            [
                'name'          => 'Current Inbound',
                'bg_color'      => 'cyan',
                'coll_class'    => 'col-md-2',
                'icon'          => 'download',
                'counter'       => 0
            ],
            [
                'name'          => 'Current Outbound',
                'bg_color'      => 'green',
                'coll_class'    => 'col-md-2',
                'icon'          => 'upload',
                'counter'       => 0
            ],
            [
                'name'          => 'Waiting Calls',
                'bg_color'      => 'orange',
                'coll_class'    => 'col-md-2',
                'icon'          => 'timer',
                'counter'       => 0
            ],
            [
                'name'          => 'Avg Ringtime In',
                'bg_color'      => 'indigo',
                'coll_class'    => 'col-md-2',
                'icon'          => 'timer',
                'counter'       => 0
            ],
            [
                'name'          => 'Avg Talktime In',
                'bg_color'      => 'brown',
                'coll_class'    => 'col-md-2',
                'icon'          => 'phone',
                'counter'       => 0
            ],
            [
                'name'          => 'Missed Calls In',
                'bg_color'      => 'red',
                'coll_class'    => 'col-md-2',
                'icon'          => 'close',
                'counter'       => 0,
                'url'           => route('reporting.missed')
            ],
            [
                'name'          => 'Answered In',
                'bg_color'      => 'light-green',
                'coll_class'    => 'col-md-2',
                'icon'          => 'check',
                'counter'       => 0,
                'url'           => route('reporting.ccinbound')
            ],
            [
                'name'          => 'Total Inbound',
                'bg_color'      => 'pink',
                'coll_class'    => 'col-md-2',
                'icon'          => 'download',
                'counter'       => 0
            ],
            [
                'name'          => 'Total Agents',
                'bg_color'      => 'teal',
                'coll_class'    => 'col-md-2',
                'icon'          => 'group',
                'counter'       => 0
            ],
            // [
            //     'name'          => 'No Answered Out',
            //     'bg_color'      => 'deep-orange',
            //     'coll_class'    => 'col-md-2',
            //     'icon'          => 'call_missed_outgoing',
            //     'counter'       => 0
            // ],
        ];
        if ($user->can('reporting.sla')) {
            $slaWidget = [
                'name' => 'SLA',
                'bg_color' => 'indigo',
                'coll_class' => 'col-md-2',
                'icon' => 'check_circle',
                'counter' => 0,
            ];

            array_splice($widgets, 11, 0, [$slaWidget]); // 11 = index 12
        }

        if ($user->can('reporting.dashboard.voicemails')) {
            $slaWidget = [
                'name' => 'Voicemails',
                'bg_color' => 'indigo',
                'coll_class' => 'col-md-2',
                'icon' => 'mic',
                'counter' => 0,
            ];

            array_splice($widgets, 11, 0, [$slaWidget]); // 11 = index 12
        }

        if ($user->can('autodialer')) {
            $slaWidget = [
                'name' => 'Autodialer Calls',
                'bg_color' => 'teal',
                'coll_class' => 'col-md-2',
                'icon' => 'autorenew',
                'counter' => 0,
            ];

            array_splice($widgets, 11, 0, [$slaWidget]); // 11 = index 12
        }

        if ($user->can('reporting.outbound.details')) {

            // $widgets = array_filter($widgets, function ($widget) {
            //     return $widget['name'] !== 'No Answered Out';
            // });

            $widgets = array_values($widgets);

            $averageTalk = [
                'name'          => 'Avg Talktime Out',
                'bg_color'      => 'deep-purple',
                'coll_class'    => 'col-md-2',
                'icon'          => 'call_end',
                'counter'       => 0
            ];

            $noAnswerWidget = [
                'name' => 'No Answer',
                'bg_color' => 'red',
                'coll_class' => 'col-md-2',
                'icon' => 'phone_missed',
                'counter' => 0,
            ];

            $busyWidget = [
                'name' => 'Busy',
                'bg_color' => 'orange',
                'coll_class' => 'col-md-2',
                'icon' => 'phone_paused',
                'counter' => 0,
            ];

            $failedWidget = [
                'name' => 'Failed',
                'bg_color' => 'grey',
                'coll_class' => 'col-md-2',
                'icon' => 'close',
                'counter' => 0,
            ];

            $AnswerWidget = [
                'name'          => 'Answered out',
                'bg_color'      => 'lime',
                'coll_class'    => 'col-md-2',
                'icon'          => 'call_received',
                'counter'       => 0
            ];

            $totalOut = [
                'name'          => 'Total Outbound',
                'bg_color'      => 'purple',
                'coll_class'    => 'col-md-2',
                'icon'          => 'call_made',
                'counter'       => 0,
                'url'           => route('reporting.ccoutbound')
            ];

            array_splice($widgets, 12, 0, [$averageTalk, $failedWidget, $busyWidget, $noAnswerWidget, $AnswerWidget, $totalOut]);
        }

        $view->with('widgets', $widgets);
        $view->with('users', $users);
        $view->with('queues', $queues);
        $view->with('selectedQueue', $selectedQueue);
        $view->with('whatsAppCountryCode', SettingsHelper::normalizeWhatsAppCountryCode(CompanySettings::get(SettingsHelper::S_COMPANY_WHATSAPP_COUNTRY_CODE)));
        return $view;
    }


    public function blacklist(Request $request)
    {
        if ($request->ajax()) {
            $process = new Process([
                'asterisk',
                '-rx',
                "database show blacklist"
            ]);
            $process->run();
            $results = $process->getOutput();
            preg_match_all('/\/blacklist\/([^:]+)\s*:\s*([^\/]+)/', $results, $matches, PREG_SET_ORDER);
            $data = [];
            foreach ($matches as $match) {
                $key = trim($match[1]);
                $value = trim($match[2]);

                if ($key !== 'dest' && $value !== 'app-blackhole,hangup,1') {
                    if (strpos($value, 'results found.') !== false) {
                        $descParts = explode("\n", $value);
                        $value = $descParts[0];
                    }
                    $value = trim($value);
                    if ($value == '1') {
                        $value = NULL;
                    }
                    $Object = [
                        'number' => $key,
                        'description' => $value,
                    ];
                    $data[] = $Object;
                }
            }

            return Datatables::of($data)
                ->addColumn('action', function ($data) {
                    $btn = '<a href="' . route('reporting.blacklist.delete', $data['number']) . '" class="edit btn btn-danger btn-sm" style="color:white">Delete</a>';
                    return $btn;
                })
                ->rawColumns(['action'])
                ->make(true);
        }
        return view('reporting::blacklist');
    }
    function blacklistcreate(Request $request)
    {
        return view('reporting::blacklistcreate');
    }
    function blacklistadd(Request $request)
    {
        if (empty($request->number)) {

            flash('Number/CallerID cannot be blank!')->error();
        } elseif ($request->desc == '1') {

            flash('Description not allowed, reserved character!')->error();
        } else {

            $num = trim($request->number);
            $desc = $request->desc ?? 1;

            // Remove spaces, dashes, etc.
            $num = preg_replace('/[^\d+]/', '', $num);

            // Create the two versions
            if (str_starts_with($num, '+2')) {
                // +201012345678 -> 01012345678
                $localNum = substr($num, 2);

                // +2 + local number
                $internationalNum = $num;
            } else {
                // 01012345678 -> +201012345678
                $localNum = $num;
                $internationalNum = '+2' . $num;
            }

            // Add local number
            $process = new Process([
                'asterisk',
                '-rx',
                "database put blacklist {$localNum} {$desc}"
            ]);

            $process->run();

            // Add international number
            $process = new Process([
                'asterisk',
                '-rx',
                "database put blacklist {$internationalNum} {$desc}"
            ]);

            $process->run();

            flash("$num Blocked Successfully")->success();
        }

        return redirect()->route('reporting.blacklist.create');
    }


    function blacklistdelete($num)
    {
        if (isset($num)) {

            $num = trim($num);

            // Remove spaces, dashes, etc.
            $num = preg_replace('/[^\d+]/', '', $num);

            // Determine both versions
            if (str_starts_with($num, '+2')) {

                // +201012345678
                $localNum = substr($num, 2);
                $internationalNum = $num;
            } else {

                // 01012345678
                $localNum = $num;
                $internationalNum = '+2' . $num;
            }

            // Delete local number
            $process = new Process([
                'asterisk',
                '-rx',
                "database del blacklist {$localNum}"
            ]);

            $process->run();

            // Delete international number
            $process = new Process([
                'asterisk',
                '-rx',
                "database del blacklist {$internationalNum}"
            ]);

            $process->run();

            flash("$num UNBlocked Successfully")->success();
        }

        return redirect()->route('reporting.blacklist');
    }

    public function insights()
    {
        $user           = \Auth::user();
        $week_ago       = \Carbon\Carbon::today()->subDays(7);
        $today          = \Carbon\Carbon::today();

        $view = view('reporting::insights');
        $sql_day_concat_string          = 'CONCAT(DATE_FORMAT(created_at,"%Y"),"-",DATE_FORMAT(created_at,"%m"),"-",DATE_FORMAT(created_at,"%d")) AS the_day';
        $sql_day_concat_string_group    = str_replace(' AS the_day', '', $sql_day_concat_string);

        $in_out_7_days                  = Call::select(
            'direction_id',
            DB::raw($sql_day_concat_string),
            DB::raw('COUNT(*) AS count'),
            DB::raw('SUM(duration) AS total_duration')
        )
            ->groupBy(DB::raw($sql_day_concat_string_group), 'direction_id')
            ->whereBetween('created_at', [$week_ago->format('Y-m-d 00:00:00'), $today->format('Y-m-d 23:59:59')])
            ->get();
        $in_out_7_days_key_day          = [];
        $directions                     = DirectionType::pluck('name', 'id');
        foreach ($in_out_7_days as $one) {
            $direction                  = $directions[$one->direction_id];
            $day                        = $one->the_day;
            $total_duration             = $one->total_duration;
            $count                      = $one->count;
            // $in_out_7_days_key_day[$day][$direction]    = ['count' => $count,'talktime' => $this->SettingsHelper->format_time($one->total_duration)];
            $in_out_7_days_key_day[$day][$direction]    = ['count' => $count, 'talktime' => $one->total_duration];
        }

        $in_out_7_days_data             = [];
        $period                         = CarbonPeriod::create($week_ago, $today);
        $chart_labels                   = [];
        $incoming_counts                = [];
        $outgoing_counts                = [];
        $incoming_talktime              = [];
        $outgoing_talktime              = [];
        foreach ($period as $date) {

            $formatted                  = $date->format('Y-m-d');
            $chart_labels[]             = $formatted;
            $incoming_counts[]          = $in_out_7_days_key_day[$formatted]['Incoming']['count'] ?? 0;
            $outgoing_counts[]          = $in_out_7_days_key_day[$formatted]['Outgoing']['count'] ?? 0;
            $incoming_talktime[]          = $in_out_7_days_key_day[$formatted]['Incoming']['talktime'] ?? 0;
            $outgoing_talktime[]          = $in_out_7_days_key_day[$formatted]['Outgoing']['talktime'] ?? 0;
            // $in_out_7_days_data[$formatted]['Incoming']['count']         = $in_out_7_days_key_day[$formatted]['Incoming']['count'] ?? 0;
            // $in_out_7_days_data[$formatted]['Incoming']['talktime']         = $in_out_7_days_key_day[$formatted]['Incoming']['talktime'] ?? 0;
            // $in_out_7_days_data[$formatted]['Outgoing']['count']         = $in_out_7_days_key_day[$formatted]['Outgoing']['count'] ?? 0;
            // $in_out_7_days_data[$formatted]['Outgoing']['talktime']         = $in_out_7_days_key_day[$formatted]['Outgoing']['talktime'] ?? 0;
        }
        // Charts deployment
        $overall_chart                  = new CallsOverallChart;
        $overall_chart->labels($chart_labels);
        $overall_chart->dataset('Incoming', 'bar', $incoming_counts)
            ->color('#36A2EB')
            ->backgroundColor('#36A2EB80');
        $overall_chart->dataset('Outgoing', 'bar', $outgoing_counts)
            ->color('#cc65fe')
            ->backgroundColor('#cc65fe80');
        $view->with('overall_chart', $overall_chart);

        $duration_chart                  = new CallsOverallChart;
        $duration_chart->labels($chart_labels);
        $duration_chart->dataset('Incoming', 'line', $incoming_talktime)
            ->color('#36A2EB')
            ->backgroundColor('#36A2EB80');
        $duration_chart->dataset('Outgoing', 'line', $outgoing_talktime)
            ->color('#cc65fe')
            ->backgroundColor('#cc65fe80');
        $view->with('duration_chart', $duration_chart);

        return $view;
    }

    // public function performance(Request $request)
    // {
    //     $user = Auth::user();
    //     $company_groups = Group::where('company_id', $user->company->id ?? 1)->pluck('id');
    //     $admins = User::where('company_id', $user->company_id)
    //         ->whereHas('roles', function ($q) {
    //             $q->where('name', 'company_manager');
    //         })
    //         ->pluck('name')
    //         ->toArray();

    //     $conds = $this->SettingsHelper->defaultConds();
    //     if ($request->has('daterange')) {
    //         $range = explode(' - ', $request->daterange);
    //         try {
    //             $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
    //             $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
    //         } catch (\Exception $e) {
    //             $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
    //             $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
    //         }
    //     }

    //     $totalRingSub = \DB::table('asteriskcdrdb.queuelog')
    //         ->select('callid', \DB::raw("
    //             SUM(
    //                 CASE 
    //                     WHEN event IN ('EXITWITHTIMEOUT', 'ABANDON') THEN CAST(data3 AS UNSIGNED)
    //                     WHEN event IN ('COMPLETECALLER', 'COMPLETEAGENT') THEN CAST(data1 AS UNSIGNED)
    //                     ELSE 0
    //                 END
    //             ) as total_ring_duration
    //         "))
    //         ->groupBy('callid');

    //     // Subquery for latest events
    //     $latestEventsSub = \DB::table('asteriskcdrdb.queuelog')
    //         ->select(\DB::raw('MAX(id) as latest_id'))
    //         ->whereIn('event', ['COMPLETECALLER', 'COMPLETEAGENT', 'ABANDON', 'EXITWITHTIMEOUT'])
    //         ->whereBetween('time', [$conds['from'], $conds['to']])
    //         ->groupBy('callid');

    //     // Subquery for first enter queue
    //     $enterqueueSub = \DB::table('asteriskcdrdb.queuelog')
    //         ->select('callid', 'queuename')
    //         ->where('event', 'ENTERQUEUE')
    //         ->whereIn('queuename', $company_groups)
    //         ->groupBy('callid');

    //     // Performance data query
    //     $performance_data = \DB::table('asteriskcdrdb.queuelog as q')
    //         ->joinSub($latestEventsSub, 'latest', function ($join) {
    //             $join->on('q.id', '=', 'latest.latest_id');
    //         })
    //         ->leftJoinSub($enterqueueSub, 'first_enter', function ($join) {
    //             $join->on('q.callid', '=', 'first_enter.callid');
    //         })
    //         ->leftJoinSub($totalRingSub, 'tr', function ($join) {
    //             $join->on('q.callid', '=', 'tr.callid');
    //         })
    //         ->select(
    //             'q.id',
    //             'q.time',
    //             'q.callid',
    //             'first_enter.queuename as queuename',
    //             'q.agent',
    //             'q.event',
    //             'q.data1',
    //             'q.data2',
    //             'q.data3',
    //             'q.data4',
    //             'q.data5',
    //             'tr.total_ring_duration'
    //         )
    //         ->orderBy('first_enter.queuename', 'asc');

    //     // Filters
    //     if(isset($request->group_ext)){
    //         // $users_ids = User::join('group_user',config('database.connections.mysql.database').'.users.id','group_user.user_id')
    //         // ->where('group_id', $request->group_ext)->pluck('name')->toArray();
    //         $performance_data->where('first_enter.queuename', $request->group_ext);
    //     }
    //     $excludedQueues = CompanySettings::get('report_excluded_groups');
    //     if (!empty($excludedQueues) && is_array($excludedQueues)) {
    //         $performance_data->whereNotIn('first_enter.queuename', $excludedQueues);
    //     }
    //     if(isset($request->user_ext)){
    //         $performance_data->where('agent', $request->user_ext);
    //     }
    //     if($user->roles[0]->name != 'company_manager'){
    //         // $request->group_ext = $user->groups[0]->id;
    //         $performance_data->whereNotIn('agent', $admins);
    //     }
    //     $performance_data = $performance_data->orderBy('queuename')->get();
    //     // return $performance_data;
    //     // return $this->getReport($performance_data);
    //     $report                         = $this->getReport($performance_data, $conds);
    //     $sl_chart_data                  = [];
    //     foreach($report['service_level'] as $val){
    //         $sl_chart_data[] = $val;
    //     }
    //     $sl_chart = new CallsOverallChart;
    //     $sl_labels = ["0:5","6:10","11:15","16:20","21:25","26:30","31:35","36:40","41:up"];
    //     $sl_chart->labels($sl_labels);
    //     $sl_chart->dataset('sl', 'doughnut', $sl_chart_data)
    //     ->backgroundColor([
    //         'rgb(54, 245, 70)',
    //         'rgb(160, 242, 167)',
    //         'rgb(153, 173, 154)',
    //         'rgb(219, 172, 83)',
    //         'rgb(179, 126, 96)',
    //         'rgb(173, 107, 90)',
    //         'rgb(201, 81, 60)',
    //         'rgb(217, 56, 28)',
    //         'rgb(97, 15, 1)'
    //     ]);
    //     // dd($report['service_level']);
    //     // $sl_chart->dataset('Incoming', 'bar', $report['service_level'])
    //     //     ->color('#36A2EB')
    //     //     ->backgroundColor('#36A2EB80');
    //     // foreach($report['service_level'] as $key => $val){
    //     //     $sl_chart->dataset($key, 'pie', [$val]);
    //     // }


    //     $company_extensions                     = User::where('company_id',$user->company->id ?? 1)->pluck('id')->toArray();
    //     $agent_names                            = User::where('company_id',$user->company->id ?? 1);
    //     $groups                                 = Group::where('company_id',$user->company->id ?? 1);
    //     // if($user->roles[0]->name == 'user'){
    //     //     $agent_names = $agent_names->where('id', $user->id);
    //     //     $groups = $user->groups;
    //     // }
    //     $agents_options = [
    //         'Agents' => $agent_names->pluck('name','name')->toArray()
    //     ];
    //     $groups_options = [
    //         'Groups' => $groups->pluck('name','id')->toArray()
    //     ];
    //     $agent_names = $agent_names->pluck('id','name')->toArray();
    //     $missed = $this->getMissedCalls($conds, $company_extensions);

    //     $filter_area                            = view('reporting::widgets.filter_area');
    //     $filter_area->with('conds',$conds);
    //     // $filter_area->with('agents_options',$agents_options);
    //     // $filter_area->with('groups_options',$groups_options);
    //     $filter_area->with('user_ext',$request->user_ext);
    //     $filter_area->with('group_ext',$request->group_ext);
    //     $filter_area->with('submit_route',route('reporting.ccperformance'));
    //     $view = view('reporting::performance');
    //     $view->with('sl_chart', $sl_chart)
    //         ->with('report', $report)
    //         ->with('missed', $missed)
    //         ->with('conds', $conds)
    //         ->with('filter_area', $filter_area)
    //         ->with('agent_names', $agent_names);

    //     return $view;
    // }

    public function performance(Request $request)
    {
        $user = Auth::user();
        $company_groups = $user->accessibleQueueIds();
        $admins = User::where('company_id', $user->company_id)
            ->whereHas('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->pluck('name')
            ->toArray();

        $conds = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }


        $performance_data = \DB::table('inbound_reports')
            ->select(
                'id',
                'user_id',
                'call_time as time',
                'callid',
                'enter_queuename as queuename',
                'agent',
                'event',
                'ringtime',
                'talktime',
                'transfers_count',
            )
            ->whereBetween('call_time', [$conds['from'], $conds['to']])
            ->whereIn('enter_queuename', $company_groups)
            ->orderBy('queuename', 'asc');

        // Filters
        $excludedQueues = CompanySettings::get('report_excluded_groups');
        if (!empty($excludedQueues) && is_array($excludedQueues)) {
            $performance_data->whereNotIn('enter_queuename', $excludedQueues);
        }

        // Apply user permissions
        if ($user->roles[0]->name != 'company_manager') {
            $performance_data = $performance_data->whereNotIn('agent', $admins);
        }
        if ($user->access_to_all_entity != '1') {
            $performance_data = $performance_data->whereIn('enter_queuename', $user->accessibleQueueIds());
            if ($user->roles[0]->name == 'user') {
                $performance_data = $performance_data->where('agent', 'LIKE', "%$user->name%");
            }
        }

        $performance_data = $performance_data->orderBy('queuename')->get();
        // return $performance_data;
        // return $this->getReport($performance_data);
        $report                         = $this->getReport($performance_data, $conds);
        $sl_chart_data                  = [];
        foreach ($report['service_level'] as $val) {
            $sl_chart_data[] = $val;
        }
        $sl_chart = new CallsOverallChart;
        $sl_labels = ["0:5", "6:10", "11:15", "16:20", "21:25", "26:30", "31:35", "36:40", "41:up"];
        $sl_chart->labels($sl_labels);
        $sl_chart->dataset('sl', 'doughnut', $sl_chart_data)
            ->backgroundColor([
                'rgb(54, 245, 70)',
                'rgb(160, 242, 167)',
                'rgb(153, 173, 154)',
                'rgb(219, 172, 83)',
                'rgb(179, 126, 96)',
                'rgb(173, 107, 90)',
                'rgb(201, 81, 60)',
                'rgb(217, 56, 28)',
                'rgb(97, 15, 1)'
            ]);
        // dd($report['service_level']);
        // $sl_chart->dataset('Incoming', 'bar', $report['service_level'])
        //     ->color('#36A2EB')
        //     ->backgroundColor('#36A2EB80');
        // foreach($report['service_level'] as $key => $val){
        //     $sl_chart->dataset($key, 'pie', [$val]);
        // }


        $company_extensions                     = User::where('company_id', $user->company->id ?? 1);
        $agent_names                            = User::where('company_id', $user->company->id ?? 1);
        $groups                                 = Group::where('company_id', $user->company->id ?? 1);
        if ($user->access_to_all_entity != '1') {
            $company_extensions = $company_extensions->whereHas('groups', function ($q) use ($user) {
                $q->whereIn('groups.id', $user->accessibleQueueIds());
            });
            $agent_names = $agent_names->whereHas('groups', function ($q) use ($user) {
                $q->whereIn('groups.id', $user->accessibleQueueIds());
            });
        }
        $company_extensions = $company_extensions->visibleInReports()->pluck('id')->toArray();
        $agent_names = $agent_names->visibleInReports();
        // if($user->roles[0]->name == 'user'){
        //     $agent_names = $agent_names->where('id', $user->id);
        //     $groups = $user->groups;
        // }
        $agents_options = [
            'Agents' => $agent_names->pluck('name', 'name')->toArray()
        ];
        $groups_options = [
            'Groups' => $groups->pluck('name', 'id')->toArray()
        ];
        $agent_codes = $agent_names->pluck('code', 'id')->toArray();
        $agent_names = $agent_names->pluck('id', 'name')->toArray();

        $missed = $this->getMissedCalls($conds, $company_extensions);
        $filter_area                            = view('reporting::widgets.filter_area');
        $filter_area->with('conds', $conds);
        // $filter_area->with('agents_options',$agents_options);
        // $filter_area->with('groups_options',$groups_options);
        $filter_area->with('user_ext', $request->user_ext);
        $filter_area->with('group_ext', $request->group_ext);
        $filter_area->with('submit_route', route('reporting.ccperformance'));
        $view = view('reporting::performance');
        $view->with('sl_chart', $sl_chart)
            ->with('report', $report)
            ->with('missed', $missed)
            ->with('conds', $conds)
            ->with('filter_area', $filter_area)
            ->with('agent_names', $agent_names)
            ->with('agent_codes', $agent_codes)
            ->with('user_code_enabled', (bool) CompanySettings::get(SettingsHelper::S_COMPANY_USER_CODE_ENABLED));

        return $view;
    }


    public function outPerformance(Request $request)
    {
        $conds = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        $user = Auth::user();
        $company_users = $this->UserHelper->getCompanyUsers();
        if ($user->access_to_all_entity != '1') {
            $company_users = User::whereHas('groups', function ($q) {
                $q->whereIn(
                    'groups.id',
                    auth()->user()->accessibleQueueIds()
                );
            })
            ->whereDoesntHave('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->get();
        }
        $company_users = $company_users->pluck('id')->toArray();

        $performance_data = \DB::table('asteriskcdrdb.cdr')
            ->leftJoin(config('database.connections.mysql.database') . '.users', config('database.connections.mysql.database') . '.users.id', 'cdr.cnum')
            ->select(
                'cdr.cnum',
                DB::raw('DATE(cdr.calldate) as call_date'),
                DB::raw('COUNT(*) as total_calls'),
                DB::raw('SEC_TO_TIME(SUM(cdr.duration)) as total_duration'),
                DB::raw('SEC_TO_TIME(SUM(cdr.billsec)) as total_billsec'),
                DB::raw('SUM(IF(cdr.disposition = "ANSWERED", 1, 0)) as answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "NO ANSWER", 1, 0)) as no_answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "FAILED", 1, 0)) as failed_calls'),
                DB::raw('SUM(IF(cdr.disposition = "BUSY", 1, 0)) as busy_calls'),
                DB::raw('SEC_TO_TIME(FLOOR(AVG(cdr.duration))) as average_duration'),
                DB::raw('SEC_TO_TIME(FLOOR(SUM(CASE WHEN cdr.disposition = "ANSWERED" THEN cdr.billsec ELSE 0 END) / NULLIF(SUM(IF(cdr.disposition = "ANSWERED", 1, 0)), 0))) as average_billsec'),
                DB::raw(config('database.connections.mysql.database') . '.users.name as userName'),
            )
            ->whereBetween('cdr.calldate', [$conds['from'], $conds['to']])
            ->where('cdr.dcontext', 'from-internal')
            ->where('cdr.lastapp', 'Dial')
            ->whereIn('cdr.disposition', ['ANSWERED', 'BUSY', 'NO ANSWER', 'FAILED'])
            ->whereIn('cdr.cnum', $company_users)
            ->whereColumn('cdr.uniqueid', 'cdr.linkedid')
            ->groupBy('cdr.cnum', 'call_date')
            ->orderBy('call_date');
        if (isset($request->user_ext)) {
            $performance_data->where('cdr.cnum', $request->user_ext);
        }
        $performance_data = $performance_data->get();
        // return $performance_data;

        // Get agent-only grouped performance data
        $performance_data_by_agent = \DB::table('asteriskcdrdb.cdr')
            ->leftJoin(config('database.connections.mysql.database') . '.users', config('database.connections.mysql.database') . '.users.id', 'cdr.cnum')
            ->select(
                'cdr.cnum',
                DB::raw('COUNT(*) as total_calls'),
                DB::raw('SEC_TO_TIME(SUM(cdr.duration)) as total_duration'),
                DB::raw('SEC_TO_TIME(SUM(cdr.billsec)) as total_billsec'),
                DB::raw('SUM(IF(cdr.disposition = "ANSWERED", 1, 0)) as answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "NO ANSWER", 1, 0)) as no_answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "FAILED", 1, 0)) as failed_calls'),
                DB::raw('SUM(IF(cdr.disposition = "BUSY", 1, 0)) as busy_calls'),
                DB::raw('SEC_TO_TIME(FLOOR(AVG(cdr.duration))) as average_duration'),
                DB::raw('SEC_TO_TIME(FLOOR(SUM(CASE WHEN cdr.disposition = "ANSWERED" THEN cdr.billsec ELSE 0 END) / NULLIF(SUM(IF(cdr.disposition = "ANSWERED", 1, 0)), 0))) as average_billsec'),
                DB::raw(config('database.connections.mysql.database') . '.users.name as userName'),
                DB::raw(config('database.connections.mysql.database') . '.users.code as userCode'),
            )
            ->whereBetween('cdr.calldate', [$conds['from'], $conds['to']])
            ->where('cdr.dcontext', 'from-internal')
            ->where('cdr.lastapp', 'Dial')
            ->whereIn('cdr.disposition', ['ANSWERED', 'BUSY', 'NO ANSWER', 'FAILED'])
            ->whereIn('cdr.cnum', $company_users)
            ->whereColumn('cdr.uniqueid', 'cdr.linkedid')
            ->groupBy('cdr.cnum')
            ->orderBy('cdr.cnum');
        if (isset($request->user_ext)) {
            $performance_data_by_agent->where('cdr.cnum', $request->user_ext);
        }
        $performance_data_by_agent = $performance_data_by_agent->get();

        $performance_total = \DB::table('asteriskcdrdb.cdr')
            ->leftJoin(config('database.connections.mysql.database') . '.users', config('database.connections.mysql.database') . '.users.id', 'cdr.cnum')
            ->select(
                DB::raw('COUNT(DISTINCT cdr.cnum) as total_users'),
                DB::raw('COUNT(*) as total_calls'),
                DB::raw('SEC_TO_TIME(SUM(cdr.duration)) as total_duration'),
                DB::raw('SEC_TO_TIME(SUM(cdr.billsec)) as total_billsec'),
                DB::raw('SUM(IF(cdr.disposition = "ANSWERED", 1, 0)) as answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "NO ANSWER", 1, 0)) as no_answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "FAILED", 1, 0)) as failed_calls'),
                DB::raw('SUM(IF(cdr.disposition = "BUSY", 1, 0)) as busy_calls'),
                DB::raw('SEC_TO_TIME(FLOOR(AVG(cdr.duration))) as average_duration'),
                DB::raw('SEC_TO_TIME(FLOOR(SUM(CASE WHEN cdr.disposition = "ANSWERED" THEN cdr.billsec ELSE 0 END) / NULLIF(SUM(IF(cdr.disposition = "ANSWERED", 1, 0)), 0))) as average_billsec'),
            )
            ->whereBetween('cdr.calldate', [$conds['from'], $conds['to']])
            ->where('cdr.dcontext', 'from-internal')
            ->where('cdr.lastapp', 'Dial')
            ->whereIn('cdr.disposition', ['ANSWERED', 'BUSY', 'NO ANSWER', 'FAILED'])
            ->whereIn('cdr.cnum', $company_users)
            ->whereColumn('cdr.uniqueid', 'cdr.linkedid');
        if (isset($request->user_ext)) {
            $performance_total->where('cdr.cnum', $request->user_ext);
        }
        $performance_total = $performance_total->first();
        $agent_names = $this->UserHelper->getCompanyUsers();
        $agents_options = [
            'Agents' => $agent_names->pluck('name', 'id')->toArray()
        ];
        $agent_names = $agent_names->pluck('id', 'name')->toArray();

        $filter_area = view('reporting::widgets.filter_area');
        $filter_area->with('conds', $conds);
        $filter_area->with('agents_options', $agents_options);
        $filter_area->with('user_ext', $request->user_ext);
        $filter_area->with('submit_route', route('reporting.ccoutPerformance'));
        $view = view('reporting::outPerformance');
        $view->with('conds', $conds);
        $view->with('performance_data', $performance_data);
        $view->with('performance_data_by_agent', $performance_data_by_agent);
        $view->with('performance_total', $performance_total);
        $view->with('filter_area', $filter_area);
        $view->with('agent_names', $agent_names);
        $view->with('user_code_enabled', (bool) CompanySettings::get(SettingsHelper::S_COMPANY_USER_CODE_ENABLED));
        return $view;
    }

    private function getReport($rows = array(), $conds = array())
    {
        if (!empty($rows)) {
            $results                         = array();
            $overall_total_calls             = 0;
            $overall_answered_calls         = 0;
            $overall_talk_time                 = 0;
            $overall_ring_time                 = 0;
            $overall_dropped_calls          = 0;
            $overall_failover_calls         = 0;
            $overall_total_transfers        = 0;
            $resultsForQueues                 = [];
            $overall_total_callsForQueue     = 0;
            $overall_answered_callsForQueue    = 0;
            $overall_talk_timeForQueue         = 0;
            $overall_ring_timeForQueue         = 0;
            $overall_dropped_callsForQueue  = 0;
            $overall_failover_callsForQueue = 0;
            $overall_transfers_countForQueue = 0;
            $return_array                     = array();
            $service_level_array             = array(
                '0_to_5'                    => 0,
                '6_to_10'                    => 0,
                '11_to_15'                    => 0,
                '16_to_20'                    => 0,
                '21_to_25'                    => 0,
                '26_to_30'                    => 0,
                '31_to_35'                    => 0,
                '36_to_40'                    => 0,
                '41_to_up'                    => 0
            );

            // Add counter for answered calls with ringtime > 10
            $service_level_ringe_time = CompanySettings::get('service_level_ringe_time');
            $service_level_ringe_time = empty($service_level_ringe_time) ? 1000 : $service_level_ringe_time;
            $answered_over_10 = 0;

            // Initialize $oldQueue to avoid undefined variable error
            $oldQueue = NULL;

            // Initialize arrays for transfer counts
            $transferred_out = [];
            $transferred_in = [];

            $agent_names = User::where('company_id', Auth::user()->company->id ?? 1)->visibleInReports()->pluck('name', 'id')->toArray();
            $group_names = Group::pluck('name', 'id')->toArray();
            // Moving this outside the loop to avoid redundant calls VERY CRITICAL for performance
            $lastKey = $rows->keys()->last();
            // 

            foreach ($rows as $index => $row) {
                if ($oldQueue == NULL) {
                    $oldQueue = $row->queuename;
                }
                $isLast = $index === $lastKey;
                $time                         = $row->time;
                $queuename                     = $row->queuename;
                $agent                         = $row->agent;
                $event                         = $row->event;
                $data1                         = $row->ringtime;
                $data2                         = $row->talktime;
                $transfers                  = $row->transfers_count ?? 0;

                $extension = null;
                if (preg_match('/Local\/(\d+)@/', $agent, $matches)) {
                    $extension = $matches[1];
                }

                $agent = $extension ? ($agent_names[$extension] ?? $agent) : $agent;

                if (empty($results[$agent])) {
                    $results[$agent] = array(
                        'agent_calls' => 0,
                        'agent_ring_time' => 0,
                        'agent_talk_time' => 0,
                        'agent_hangup' => 0,
                        'customer_hangup' => 0,
                        'transferred_out' => 0,  // New column
                        'transferred_in' => 0,    // New column
                        'agent_transfers' => 0,
                    );
                }

                $results[$agent]['agent_calls']++;
                $results[$agent]['agent_ring_time']        += $data1;
                $results[$agent]['agent_talk_time']        += $data2;
                $results[$agent]['agent_transfers']     += $transfers;

                // Add AHT calculation
                $results[$agent]['aht'] = $results[$agent]['agent_calls'] > 0
                    ? ($results[$agent]['agent_talk_time'] / $results[$agent]['agent_calls']) + ($results[$agent]['agent_talk_time'] / $results[$agent]['agent_calls'])
                    : 0;

                if ($event == 'COMPLETEAGENT') {
                    $results[$agent]['agent_hangup']++;
                } elseif ($event == 'COMPLETECALLER') {
                    $results[$agent]['customer_hangup']++;
                }

                if ($event == 'COMPLETECALLER' or $event == 'COMPLETEAGENT') {
                    $overall_answered_calls++;
                    if ($data1 >= 0 and $data1 <= 5) {
                        $service_level_array['0_to_5']++;
                    } elseif ($data1 >= 6 and $data1 <= 10) {
                        $service_level_array['6_to_10']++;
                    } elseif ($data1 >= 11 and $data1 <= 15) {
                        $service_level_array['11_to_15']++;
                    } elseif ($data1 >= 16 and $data1 <= 20) {
                        $service_level_array['16_to_20']++;
                    } elseif ($data1 >= 21 and $data1 <= 25) {
                        $service_level_array['21_to_25']++;
                    } elseif ($data1 >= 26 and $data1 <= 30) {
                        $service_level_array['26_to_30']++;
                    } elseif ($data1 >= 31 and $data1 <= 35) {
                        $service_level_array['31_to_35']++;
                    } elseif ($data1 >= 36 and $data1 <= 40) {
                        $service_level_array['36_to_40']++;
                    } elseif ($data1 >= 41) {
                        $service_level_array['41_to_up']++;
                    }
                    // Count answered calls with data1 > 10
                    if ($data1 <= $service_level_ringe_time) {
                        $answered_over_10++;
                    }
                }
                if ($event == 'ABANDON') {
                    $overall_dropped_calls++;
                }
                if ($event == 'EXITWITHTIMEOUT') {
                    $overall_failover_calls++;
                }

                $overall_total_calls++;
                $overall_ring_time             += $data1;
                $overall_talk_time             += $data2;
                $overall_total_transfers    += $transfers;

                if ($oldQueue == $row->queuename) {
                    if ($event == 'COMPLETECALLER' or $event == 'COMPLETEAGENT') {
                        $overall_answered_callsForQueue++;
                    }
                    if ($event == 'ABANDON') {
                        $overall_dropped_callsForQueue++;
                    }
                    if ($event == 'EXITWITHTIMEOUT') {
                        $overall_failover_callsForQueue++;
                    }
                    $overall_total_callsForQueue++;
                    $overall_ring_timeForQueue             += $data1;
                    $overall_talk_timeForQueue             += $data2;
                    $overall_transfers_countForQueue    += $transfers;
                    if ($isLast) {
                        $resultsForQueues[] = [
                            'overall_total_callsForQueue'        => $overall_total_callsForQueue,
                            'overall_answered_callsForQueue'     => $overall_answered_callsForQueue,
                            'overall_dropped_callsForQueue'     => $overall_dropped_callsForQueue,
                            'overall_failover_callsForQueue'    => $overall_failover_callsForQueue,
                            'overall_ring_timeForQueue'         => $overall_ring_timeForQueue,
                            'overall_talk_timeForQueue'         => $this->SettingsHelper->format_time($overall_talk_timeForQueue),
                            'average_ring_timeForQueue'         => @$this->SettingsHelper->format_time(ceil($overall_ring_timeForQueue / $overall_total_callsForQueue)),
                            'average_call_timeForQueue'         => @$this->SettingsHelper->format_time(ceil($overall_talk_timeForQueue / $overall_total_callsForQueue)),
                            'queueExt'                          => $oldQueue,
                            'queueName'                         => $group_names[$oldQueue] ?? null,
                            'overall_transfers_countForQueue'   => $overall_transfers_countForQueue,
                        ];
                        $overall_total_callsForQueue     = 0;
                        $overall_answered_callsForQueue    = 0;
                        $overall_talk_timeForQueue         = 0;
                        $overall_ring_timeForQueue         = 0;
                        $overall_dropped_callsForQueue  = 0;
                        $overall_failover_callsForQueue = 0;
                        $overall_transfers_countForQueue = 0;
                    }
                } else {
                    $resultsForQueues[] = [
                        'overall_total_callsForQueue'        => $overall_total_callsForQueue,
                        'overall_answered_callsForQueue'     => $overall_answered_callsForQueue,
                        'overall_dropped_callsForQueue'     => $overall_dropped_callsForQueue,
                        'overall_failover_callsForQueue'    => $overall_failover_callsForQueue,
                        'overall_ring_timeForQueue'         => $overall_ring_timeForQueue,
                        'overall_talk_timeForQueue'         => $this->SettingsHelper->format_time($overall_talk_timeForQueue),
                        'average_ring_timeForQueue'         => @$this->SettingsHelper->format_time(ceil($overall_ring_timeForQueue / $overall_total_callsForQueue)),
                        'average_call_timeForQueue'         => @$this->SettingsHelper->format_time(ceil($overall_talk_timeForQueue / $overall_total_callsForQueue)),
                        'queueExt'                          => $oldQueue,
                        'queueName'                         => $group_names[$oldQueue] ?? null,
                        'overall_transfers_countForQueue'   => $overall_transfers_countForQueue,
                    ];
                    $overall_total_callsForQueue     = 0;
                    $overall_answered_callsForQueue    = 0;
                    $overall_talk_timeForQueue         = 0;
                    $overall_ring_timeForQueue         = 0;
                    $overall_dropped_callsForQueue  = 0;
                    $overall_failover_callsForQueue = 0;
                    $overall_transfers_countForQueue = 0;
                    $oldQueue = $row->queuename;
                    if ($event == 'COMPLETECALLER' or $event == 'COMPLETEAGENT') {
                        $overall_answered_callsForQueue++;
                    }
                    if ($event == 'ABANDON') {
                        $overall_dropped_callsForQueue++;
                    }
                    if ($event == 'EXITWITHTIMEOUT') {
                        $overall_failover_callsForQueue++;
                    }
                    $overall_total_callsForQueue++;
                    $overall_ring_timeForQueue             += $data1;
                    $overall_talk_timeForQueue             += $data2;
                    $overall_transfers_countForQueue    += $transfers;
                    if ($isLast) {
                        $resultsForQueues[] = [
                            'overall_total_callsForQueue'        => $overall_total_callsForQueue,
                            'overall_answered_callsForQueue'     => $overall_answered_callsForQueue,
                            'overall_dropped_callsForQueue'     => $overall_dropped_callsForQueue,
                            'overall_failover_callsForQueue'    => $overall_failover_callsForQueue,
                            'overall_ring_timeForQueue'         => $overall_ring_timeForQueue,
                            'overall_talk_timeForQueue'         => $this->SettingsHelper->format_time($overall_talk_timeForQueue),
                            'average_ring_timeForQueue'         => @$this->SettingsHelper->format_time(ceil($overall_ring_timeForQueue / $overall_total_callsForQueue)),
                            'average_call_timeForQueue'         => @$this->SettingsHelper->format_time(ceil($overall_talk_timeForQueue / $overall_total_callsForQueue)),
                            'queueExt'                          => $oldQueue,
                            'queueName'                         => $group_names[$oldQueue] ?? null,
                            'overall_transfers_countForQueue'   => $overall_transfers_countForQueue,
                        ];
                        $overall_total_callsForQueue     = 0;
                        $overall_answered_callsForQueue    = 0;
                        $overall_talk_timeForQueue         = 0;
                        $overall_ring_timeForQueue         = 0;
                        $overall_dropped_callsForQueue  = 0;
                        $overall_failover_callsForQueue = 0;
                        $overall_transfers_countForQueue = 0;
                    }
                }
            }

            $agent_names_by_name = User::where('company_id', Auth::user()->company->id ?? 1)->visibleInReports()->pluck('id', 'name')->toArray();

            // Bulk transfer queries — 2 queries total instead of 2×N
            $agent_ids = array_values(array_filter(array_map(
                fn($name) => $agent_names_by_name[$name] ?? null,
                array_keys($results)
            )));
            // if (!empty($agent_ids)) {
            //     $bulk_transferred_out = $this->getBulkTransferredOutCalls($agent_ids, $conds['from'], $conds['to']);
            //     $bulk_transferred_in  = $this->getBulkTransferredInCalls($agent_ids, $conds['from'], $conds['to']);
            //     foreach ($results as $agent_name => &$agent_data) {
            //         $agent_id = $agent_names_by_name[$agent_name] ?? null;
            //         if ($agent_id) {
            //             $agent_data['transferred_out'] = $bulk_transferred_out[$agent_id] ?? 0;
            //             $agent_data['transferred_in']  = $bulk_transferred_in[$agent_id] ?? 0;
            //         }
            //     }
            //     unset($agent_data);
            // }

            unset($results['NONE']);
            $return_array                     = array(
                'overall_total_calls'        => $overall_total_calls,
                'overall_answered_calls'     => $overall_answered_calls,
                'overall_dropped_calls'     => $overall_dropped_calls,
                'overall_failover_calls'    => $overall_failover_calls,
                'overall_ring_time'         => $overall_ring_time,
                'overall_talk_time'         => $this->SettingsHelper->format_time($overall_talk_time),
                'average_ring_time' => @$this->SettingsHelper->format_time(
                    $overall_total_calls > 0 ? ceil($overall_ring_time / $overall_total_calls) : 0
                ),
                'average_call_time' => @$this->SettingsHelper->format_time(
                    $overall_answered_calls > 0 ? ceil($overall_talk_time / $overall_answered_calls) : 0
                ),
                'service_level'             => $service_level_array,
                'agents'                     => $results,
                'resultsForQueues'          => $resultsForQueues,
                'overall_total_transfers'   => $overall_total_transfers,
                // Add new service_level key as requested
                'service_level_over' => ($overall_total_calls > 0) ? round(($answered_over_10 / $overall_total_calls) * 100, 2) : 0
            );
            return $return_array;
        }
    }


    private function getBulkTransferredOutCalls(array $agent_ids, $from, $to): array
    {
        try {
            return DB::table('asteriskcdrdb.cdr as cdr')
                ->selectRaw('cdr.dst as agent_id, COUNT(*) as cnt')
                ->whereBetween('cdr.calldate', [$from, $to])
                ->where('cdr.lastapp', 'Dial')
                ->whereIn('cdr.dst', $agent_ids)
                ->whereExists(function ($query) {
                    $query->select(DB::raw(1))
                        ->from('asteriskcdrdb.cdr as c2')
                        ->whereColumn('c2.linkedid', 'cdr.linkedid')
                        ->where('c2.disposition', 'ANSWERED')
                        ->where('c2.dcontext', 'from-queue-exten-only')
                        ->whereColumn('c2.calldate', '<', 'cdr.calldate');
                })
                ->groupBy('cdr.dst')
                ->get()
                ->pluck('cnt', 'agent_id')
                ->toArray();
        } catch (\Exception $e) {
            return [];
        }
    }

    private function getBulkTransferredInCalls(array $agent_ids, $from, $to): array
    {
        try {
            $expr = "SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(cdr.lastdata, '/', -1), '@', 1), ':', -1)";
            return DB::table('asteriskcdrdb.cdr as cdr')
                ->selectRaw("$expr as agent_id, COUNT(*) as cnt")
                ->whereBetween('cdr.calldate', [$from, $to])
                ->where('cdr.lastapp', 'Dial')
                ->whereExists(function ($query) {
                    $query->select(DB::raw(1))
                        ->from('asteriskcdrdb.cdr as c2')
                        ->whereColumn('c2.linkedid', 'cdr.linkedid')
                        ->where('c2.disposition', 'ANSWERED')
                        ->where('c2.dcontext', 'from-queue-exten-only')
                        ->whereColumn('c2.calldate', '<', 'cdr.calldate');
                })
                ->whereIn(DB::raw($expr), $agent_ids)
                ->groupBy(DB::raw($expr))
                ->get()
                ->pluck('cnt', 'agent_id')
                ->toArray();
        } catch (\Exception $e) {
            return [];
        }
    }


    // private function getMissedCalls($conds = array(),$extensions = array()){
    //     if(is_array($extensions) && !empty($extensions)){
    //         $from                   = $conds['from'];
    //         $to                     = $conds['to'];
    //         $impl                   = implode(',',$extensions);
    //         $results                 = \DB::table('asteriskcdrdb.cdr')
    //         ->select(
    //             \DB::raw('CASE WHEN cnum IN('.$impl.') THEN cnum WHEN dst IN('.$impl.') THEN dst ELSE "0" END AS "extension"'),
    //             \DB::raw('SUM(CASE WHEN dst IN('.$impl.') THEN 1 ELSE 0 END) AS "inbound_total"'),
    //             \DB::raw('SUM(CASE WHEN ( dst IN('.$impl.') AND disposition = "ANSWERED") THEN 1 ELSE 0 END) AS "inbound_completed"'),
    //             \DB::raw('COUNT(DISTINCT CASE WHEN (dst IN('.$impl.') AND (disposition = "NO ANSWER" OR disposition = "FAILED") AND duration > 2) THEN CONCAT(calldate, src, dst) ELSE NULL END) AS "inbound_noanswer"'),
    //             \DB::raw('COUNT(DISTINCT CASE WHEN (dst IN('.$impl.') AND disposition = "BUSY" AND duration > 2) THEN CONCAT(calldate, src, dst) ELSE NULL END) AS "inbound_rejected"'),
    //             \DB::raw('SUM(CASE WHEN dst IN('.$impl.') THEN duration ELSE 0 END) AS "inbound_duration"'),
    //             \DB::raw('SUM(CASE WHEN cnum IN('.$impl.') THEN 1 ELSE 0 END) AS "outbound"'),
    //             \DB::raw('SUM(CASE WHEN cnum IN('.$impl.') THEN duration ELSE 0 END) AS "outbound_duration"')
    //         )
    //         ->whereBetween('calldate',[$from,$to])
    //         ->where(function($query) use ($extensions) {
    //             $query->whereIn('cnum', $extensions);
    //             $query->orWhereIn('dst', $extensions);
    //         })
    //         ->groupBy('extension')
    //         ->get();

    //         if(!is_null($results)){
    //             $return_array 			= array();
    //             foreach($results as $one){
    //                 $return_array[$one->extension]			= array(
    //                     'inbound_noanswer' 						=> $one->inbound_noanswer,
    //                     'inbound_rejected' 						=> $one->inbound_rejected,
    //                     'outbound' 								=> $one->outbound,
    //                     'outbound_duration' 					=> $one->outbound_duration
    //                 );
    //             }
    //             return $return_array;
    //         }
    //         return false;
    //     }
    // }

    private function getMissedCalls($conds = [], $extensions = [])
    {
        if (empty($extensions)) {
            return [];
        }

        $from = $conds['from'];
        $to   = $conds['to'];

        // Fetch raw rows with only the columns we need — no GROUP BY or aggregation in SQL.
        // PHP handles the grouping, which avoids expensive filesort/tmp-table operations on CDR.
        $rows = \DB::table('asteriskcdrdb.cdr')
            ->select('dst', 'disposition', 'duration', 'billsec')
            ->whereBetween('calldate', [$from, $to])
            ->whereIn('dst', $extensions)
            ->get();

        $return_array = [];

        foreach ($rows as $row) {
            $ext = $row->dst;

            if (!isset($return_array[$ext])) {
                $return_array[$ext] = [
                    'inbound_noanswer'  => 0,
                    'inbound_rejected'  => 0,
                    'inbound_ring_time' => 0,
                ];
            }

            $duration = (int) $row->duration;
            $billsec  = (int) $row->billsec;

            if (($row->disposition === 'NO ANSWER' || $row->disposition === 'FAILED') && $duration > 2) {
                $return_array[$ext]['inbound_noanswer']++;
            }

            if ($row->disposition === 'BUSY' && $duration > 2) {
                $return_array[$ext]['inbound_rejected']++;
            }

            $return_array[$ext]['inbound_ring_time'] += ($duration - $billsec);
        }

        return $return_array;
    }

    public function getRejectedCalls(Request $request)
    {
        $userId = $request->user_id;
        $from = $request->from;
        $to = $request->to;

        $rejectedCalls = $this->getRejectedCallsData($userId, $from, $to);

        return response()->json([
            'success' => true,
            'data' => $rejectedCalls
        ]);
    }

    public function getNoAnswerCalls(Request $request)
    {
        $userId = $request->user_id;
        $from = $request->from;
        $to = $request->to;

        $noAnswerCalls = $this->getNoAnswerCallsData($userId, $from, $to);

        return response()->json([
            'success' => true,
            'data' => $noAnswerCalls
        ]);
    }

    public function exportRejectedCalls(Request $request)
    {
        $userId = $request->user_id;
        $from = $request->from;
        $to = $request->to;
        $rows = $this->getRejectedCallsData($userId, $from, $to);

        return $this->exportCallDetailsSheet($rows, $from, $to, $userId, 'Rejected Calls Details', 'rejected_calls_details.xlsx');
    }

    public function exportNoAnswerCalls(Request $request)
    {
        $userId = $request->user_id;
        $from = $request->from;
        $to = $request->to;
        $rows = $this->getNoAnswerCallsData($userId, $from, $to);

        return $this->exportCallDetailsSheet($rows, $from, $to, $userId, 'No Answer Calls Details', 'no_answer_calls_details.xlsx');
    }

    private function getRejectedCallsData($userId, $from, $to)
    {
        return \DB::table('asteriskcdrdb.cdr')
            ->select('calldate', 'src', 'disposition', \DB::raw('SEC_TO_TIME(duration) as duration'))
            ->where('dst', $userId)
            ->whereBetween('calldate', [$from, $to])
            ->where('disposition', 'BUSY')
            ->where('duration', '>', 2)
            ->distinct()
            ->orderBy('calldate', 'desc')
            ->get();
    }

    private function getNoAnswerCallsData($userId, $from, $to)
    {
        return \DB::table('asteriskcdrdb.cdr')
            ->select('calldate', 'src', 'disposition', \DB::raw('SEC_TO_TIME(duration) as duration'))
            ->where('dst', $userId)
            ->whereBetween('calldate', [$from, $to])
            ->where(function ($query) {
                $query->where('disposition', 'NO ANSWER')
                    ->orWhere('disposition', 'FAILED');
            })
            ->where('duration', '>', 2)
            ->distinct()
            ->orderBy('calldate', 'desc')
            ->get();
    }

    private function exportCallDetailsSheet($rows, $from, $to, $userId, $title, $filename)
    {
        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=' . $filename,
        ];

        $callback = function () use ($rows, $from, $to, $userId, $title) {
            $excel = new Spreadsheet();
            $sheet = $excel->getActiveSheet();

            $sheet->setCellValue('A1', $title);
            $sheet->setCellValue('A2', 'Ext.');
            $sheet->setCellValue('B2', $userId);
            $sheet->setCellValue('C2', 'From');
            $sheet->setCellValue('D2', $from);
            $sheet->setCellValue('E2', 'To');
            $sheet->setCellValue('F2', $to);
            $sheet->setCellValue('A4', 'Time');
            $sheet->setCellValue('B4', 'Number');
            $sheet->setCellValue('C4', 'Duration');
            $sheet->setCellValue('D4', 'Disposition');

            $data = $rows->map(function ($row) {
                return [
                    $row->calldate,
                    $row->src,
                    $row->duration,
                    $row->disposition,
                ];
            });

            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '818181']
                ],
            ];
            $center = [
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ];

            $sheet->mergeCells('A1:F1');
            $sheet->getStyle('A1:F1')->applyFromArray($headerStyle);
            $sheet->getStyle('A4:D4')->applyFromArray($headerStyle);
            $sheet->fromArray($data->toArray(), null, 'A5');
            $sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($center);

            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };

        return Response::stream($callback, 200, $headers);
    }

    function agent_sheet(Request $request)
    {
        $conds                      = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        $agents = json_decode($request->agents);
        $missed = json_decode($request->missed);
        $agent_names = json_decode($request->agent_names);
        $agent_codes = json_decode($request->agent_codes, true) ?: [];
        $user_code_enabled = (bool) $request->input('user_code_enabled');
        // return $request;

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=users.xlsx',
        ];
        $callback = function () use ($agents, $missed, $agent_names, $agent_codes, $user_code_enabled, $conds) {
            $excel = new Spreadsheet();
            $sheet = $excel->getActiveSheet();

            // Header columns mirror the "Agents Performance" table (Code is optional)
            $headerCols = ['Ext.'];
            if ($user_code_enabled) {
                $headerCols[] = 'Code';
            }
            $headerCols = array_merge($headerCols, [
                'Agent Name',
                'Answered Calls',
                'Rejected Attempts',
                'No Answer Cycles',
                'Avg ring time',
                'Total talk time',
                'Avg call time',
                'Transfers',
                'Agent Hangup',
                'Customer Hangup',
            ]);

            $lastColumnIndex = count($headerCols);
            $lastColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($lastColumnIndex);
            $midColumnIndex = (int) ceil($lastColumnIndex / 2);
            $midColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($midColumnIndex);
            $toStartColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($midColumnIndex + 1);

            $sheet->setCellValue('A1', 'From');
            $sheet->setCellValue($toStartColumn . '1', 'To');
            $sheet->setCellValue('A2', $conds['from']);
            $sheet->setCellValue($toStartColumn . '2', $conds['to']);
            $sheet->mergeCells('A1:' . $midColumn . '1');
            $sheet->mergeCells($toStartColumn . '1:' . $lastColumn . '1');
            $sheet->mergeCells('A2:' . $midColumn . '2');
            $sheet->mergeCells($toStartColumn . '2:' . $lastColumn . '2');

            $sheet->fromArray($headerCols, null, 'A3');

            $data = collect($agents)->map(function ($data, $agent_name) use ($missed, $agent_names, $agent_codes, $user_code_enabled) {
                $ext = $agent_names->$agent_name ?? null;

                $inboundRejected = $missed->$ext->inbound_rejected ?? null;
                $inboundNoanswer = $missed->$ext->inbound_noanswer ?? null;
                $inboundRingTime = $missed->$ext->inbound_ring_time ?? null;
                $code = ($ext !== null && isset($agent_codes[$ext])) ? $agent_codes[$ext] : '';

                $row = [$ext];
                if ($user_code_enabled) {
                    $row[] = $code;
                }

                return array_merge($row, [
                    $agent_name,
                    $data->agent_calls,
                    $inboundRejected,
                    $inboundNoanswer,
                    $data->agent_calls > 0 ? $this->SettingsHelper->format_time($inboundRingTime / $data->agent_calls) : 0,
                    $this->SettingsHelper->format_time($data->agent_talk_time),
                    $data->agent_calls > 0 ? $this->SettingsHelper->format_time($data->agent_talk_time / $data->agent_calls) : 0,
                    $data->agent_transfers ?? 0,
                    $data->agent_hangup,
                    $data->customer_hangup,
                ]);
            });
            $sheet = $excel->getActiveSheet();
            // Apply header cell styles
            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '818181']
                ],
            ];
            $center = [
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ];
            $sheet->getStyle('A1:' . $lastColumn . '1')->applyFromArray($headerStyle);
            $sheet->getStyle('A3:' . $lastColumn . '3')->applyFromArray($headerStyle);
            $sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($center);

            $sheet->fromArray($data->toArray(), null, 'A4');
            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };
        return Response::stream($callback, 200, $headers);
    }
    function queue_sheet(Request $request)
    {
        $conds                      = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        $resultsForQueues = json_decode($request->resultsForQueues);
        // return $request;

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=users.xlsx',
        ];
        $callback = function () use ($resultsForQueues, $conds) {
            $excel = new Spreadsheet();

            $excel->getActiveSheet()->setCellValue('A1', 'From');
            $excel->getActiveSheet()->setCellValue('E1', 'To');
            $excel->getActiveSheet()->setCellValue('A2', $conds['from']);
            $excel->getActiveSheet()->setCellValue('E2', $conds['to']);
            $excel->getActiveSheet()->mergeCells('A1:D1');
            $excel->getActiveSheet()->mergeCells('E1:I1');
            $excel->getActiveSheet()->mergeCells('A2:D2');
            $excel->getActiveSheet()->mergeCells('E2:I2');
            // 9
            $excel->getActiveSheet()->setCellValue('A3', 'Ext.');
            $excel->getActiveSheet()->setCellValue('B3', 'Queue Name');
            $excel->getActiveSheet()->setCellValue('C3', 'Offered Calls');
            $excel->getActiveSheet()->setCellValue('D3', 'Answered Calls');
            $excel->getActiveSheet()->setCellValue('E3', 'Missed Calls');
            // $excel->getActiveSheet()->setCellValue('E3', 'Missed Numbers');
            $excel->getActiveSheet()->setCellValue('F3', 'Avg ring time');
            $excel->getActiveSheet()->setCellValue('G3', 'Avg call time');
            $excel->getActiveSheet()->setCellValue('H3', 'Total talk time');
            $excel->getActiveSheet()->setCellValue('I3', 'Transfers');
            $data = collect($resultsForQueues)->map(function ($data) {
                return [
                    $data->queueExt,
                    $data->queueName,
                    $data->overall_total_callsForQueue,
                    $data->overall_answered_callsForQueue,
                    $data->overall_total_callsForQueue - $data->overall_answered_callsForQueue,
                    // $data->overall_dropped_callsForQueue,
                    $data->average_ring_timeForQueue,
                    $data->average_call_timeForQueue,
                    $data->overall_talk_timeForQueue,
                    $data->overall_transfers_countForQueue ?? 0,
                ];
            });
            $sheet = $excel->getActiveSheet();
            // Apply header cell styles
            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '818181']
                ],
            ];
            $center = [
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ];
            $sheet->getStyle('A1:I1')->applyFromArray($headerStyle);
            $sheet->getStyle('A3:I3')->applyFromArray($headerStyle);
            $sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($center);

            $sheet->fromArray($data->toArray(), null, 'A4');
            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };
        return Response::stream($callback, 200, $headers);
    }

    function out_agent_sheet(Request $request)
    {
        $conds = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }

        $performance_data = json_decode($request->input('performance_data'));

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=users.xlsx',
        ];

        $callback = function () use ($performance_data, $conds) {
            $excel = new Spreadsheet();

            $excel->getActiveSheet()->setCellValue('A1', 'From');
            $excel->getActiveSheet()->setCellValue('G1', 'To');
            $excel->getActiveSheet()->setCellValue('A2', $conds['from']);
            $excel->getActiveSheet()->setCellValue('G2', $conds['to']);
            $excel->getActiveSheet()->mergeCells('A1:F1');
            $excel->getActiveSheet()->mergeCells('G1:L1');
            $excel->getActiveSheet()->mergeCells('A2:F2');
            $excel->getActiveSheet()->mergeCells('G2:L2');

            // Header columns mirror the "Agents Performance (By Date)" table (Date is the first column)
            $excel->getActiveSheet()->fromArray([
                'Date',
                'Ext.',
                'Agent Name',
                'Total',
                'ANSWERED',
                'NO ANSWER',
                'BUSY',
                'FAILED',
                'Total Duration',
                'Avg duration',
                'Total talk time',
                'Avg talk time',
            ], null, 'A3');

            $data = collect($performance_data)->map(function ($data) {
                return [
                    $data->call_date,
                    $data->cnum,
                    $data->userName,
                    $data->total_calls,
                    $data->answered_calls,
                    $data->no_answered_calls,
                    $data->busy_calls,
                    $data->failed_calls,
                    $data->total_duration,
                    $data->average_duration,
                    $data->total_billsec,
                    $data->average_billsec,
                ];
            });

            $sheet = $excel->getActiveSheet();
            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '818181']
                ],
            ];
            $center = [
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ];
            $sheet->getStyle('A1:L1')->applyFromArray($headerStyle);
            $sheet->getStyle('A3:L3')->applyFromArray($headerStyle);
            $sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($center);

            $sheet->fromArray($data->toArray(), null, 'A4');
            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };

        return Response::stream($callback, 200, $headers);
    }

    function out_agent_by_agent_sheet(Request $request)
    {
        $conds = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }

        $performance_data = json_decode($request->input('performance_data'));
        $user_code_enabled = (bool) $request->input('user_code_enabled');

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=users.xlsx',
        ];

        $callback = function () use ($performance_data, $user_code_enabled, $conds) {
            $excel = new Spreadsheet();
            $sheet = $excel->getActiveSheet();

            // Header columns mirror the "Agents Performance" table (Code is optional)
            $headerCols = ['Ext.'];
            if ($user_code_enabled) {
                $headerCols[] = 'Code';
            }
            $headerCols = array_merge($headerCols, [
                'Agent Name',
                'Total',
                'ANSWERED',
                'NO ANSWER',
                'BUSY',
                'FAILED',
                'Total Duration',
                'Avg duration',
                'Total talk time',
                'Avg talk time',
            ]);

            $lastColumnIndex = count($headerCols);
            $lastColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($lastColumnIndex);
            $midColumnIndex = (int) ceil($lastColumnIndex / 2);
            $midColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($midColumnIndex);
            $toStartColumn = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($midColumnIndex + 1);

            $sheet->setCellValue('A1', 'From');
            $sheet->setCellValue($toStartColumn . '1', 'To');
            $sheet->setCellValue('A2', $conds['from']);
            $sheet->setCellValue($toStartColumn . '2', $conds['to']);
            $sheet->mergeCells('A1:' . $midColumn . '1');
            $sheet->mergeCells($toStartColumn . '1:' . $lastColumn . '1');
            $sheet->mergeCells('A2:' . $midColumn . '2');
            $sheet->mergeCells($toStartColumn . '2:' . $lastColumn . '2');

            $sheet->fromArray($headerCols, null, 'A3');

            $data = collect($performance_data)->map(function ($data) use ($user_code_enabled) {
                $row = [$data->cnum];
                if ($user_code_enabled) {
                    $row[] = $data->userCode ?? '';
                }

                return array_merge($row, [
                    $data->userName,
                    $data->total_calls,
                    $data->answered_calls,
                    $data->no_answered_calls,
                    $data->busy_calls,
                    $data->failed_calls,
                    $data->total_duration,
                    $data->average_duration,
                    $data->total_billsec,
                    $data->average_billsec,
                ]);
            });

            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '818181']
                ],
            ];
            $center = [
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ];
            $sheet->getStyle('A1:' . $lastColumn . '1')->applyFromArray($headerStyle);
            $sheet->getStyle('A3:' . $lastColumn . '3')->applyFromArray($headerStyle);
            $sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($center);

            $sheet->fromArray($data->toArray(), null, 'A4');
            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };

        return Response::stream($callback, 200, $headers);
    }

    function out_summary_sheet(Request $request)
    {
        $conds                      = $this->SettingsHelper->defaultConds();
        if ($request->has('daterange')) {
            $range = explode(' - ', $request->daterange);
            try {
                $conds['from'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[0])->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::createFromFormat('Y-m-d H:i:s', $range[1])->format('Y-m-d H:i:s');
            } catch (\Exception $e) {
                // Fallback to default conditions if parsing fails
                $conds['from'] = Carbon::now()->startOfDay()->format('Y-m-d H:i:s');
                $conds['to'] = Carbon::now()->endOfDay()->format('Y-m-d H:i:s');
            }
        }
        $performance_total = json_decode($request->performance_total);

        $headers = [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename=users.xlsx',
        ];
        $callback = function () use ($performance_total, $conds) {
            $excel = new Spreadsheet();

            $excel->getActiveSheet()->setCellValue('A1', 'From');
            $excel->getActiveSheet()->setCellValue('B1', 'To');
            $excel->getActiveSheet()->setCellValue('A2', $conds['from']);
            $excel->getActiveSheet()->setCellValue('B2', $conds['to']);

            $excel->getActiveSheet()->setCellValue('A3', 'All Calls');
            $excel->getActiveSheet()->setCellValue('A4', 'Answered');
            $excel->getActiveSheet()->setCellValue('A5', 'NO ANSWER');
            $excel->getActiveSheet()->setCellValue('A6', 'BUSY');
            $excel->getActiveSheet()->setCellValue('A7', 'FAILED');
            $excel->getActiveSheet()->setCellValue('A8', 'Total Duration');
            $excel->getActiveSheet()->setCellValue('A9', 'Avg Duration');
            $excel->getActiveSheet()->setCellValue('A10', 'Total talk time');
            $excel->getActiveSheet()->setCellValue('A11', 'Avg talk time');
            $excel->getActiveSheet()->setCellValue('A12', 'Agents Processed');

            $excel->getActiveSheet()->setCellValue('B3', "$performance_total->total_calls");
            $excel->getActiveSheet()->setCellValue('B4', "$performance_total->answered_calls");
            $excel->getActiveSheet()->setCellValue('B5', "$performance_total->no_answered_calls");
            $excel->getActiveSheet()->setCellValue('B6', "$performance_total->busy_calls");
            $excel->getActiveSheet()->setCellValue('B7', "$performance_total->failed_calls");
            $excel->getActiveSheet()->setCellValue('B8', "$performance_total->total_duration");
            $excel->getActiveSheet()->setCellValue('B9', "$performance_total->average_duration");
            $excel->getActiveSheet()->setCellValue('B10', "$performance_total->total_billsec");
            $excel->getActiveSheet()->setCellValue('B11', "$performance_total->average_billsec");
            $excel->getActiveSheet()->setCellValue('B12', "$performance_total->total_users");

            $sheet = $excel->getActiveSheet();
            // Apply header cell styles
            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => [
                    'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
                    'startColor' => ['rgb' => '818181']
                ],
            ];
            $center = [
                'alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER],
            ];
            $sheet->getStyle('A1:B1')->applyFromArray($headerStyle);
            $sheet->getStyle('A3:A12')->applyFromArray($headerStyle);
            $sheet->getStyle($sheet->calculateWorksheetDimension())->applyFromArray($center);

            // $sheet->fromArray($data->toArray(), null, 'A3');
            $excel = IOFactory::createWriter($excel, 'Xlsx');
            $excel->save('php://output');
        };
        return Response::stream($callback, 200, $headers);
    }
}