<?php

namespace Modules\Api\Http\Controllers;


use Modules\Api\Http\Controllers\Base\CrudApiController;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
use Modules\Platform\Core\Helper\SettingsHelper;
use Modules\Platform\Core\Helper\UserHelper;
use Modules\Platform\Core\Helper\CompanySettings;
use Symfony\Component\Process\Process;
use Illuminate\Support\Facades\Validator;
use Modules\Platform\User\Entities\User;
use Modules\Platform\User\Entities\Group;


// use Modules\Api\Http\Requests\HomeApiRequest;

/**
 * Home API Controller
 *
 * Class HomeApiController
 *
 * @package Modules\Api\Http\Controllers
 */
class ReportingApiController extends CrudApiController
{

    // Entity Class
    // protected $entityClass = Home::class;

    // Module name

    // Language pattern
    // protected $languageFile = 'home::home';

    protected $with = [];

    protected $permissions = [
        'browse' => 'reporting.browse',
        'create' => 'reporting.create',
        'update' => 'reporting.update',
        'destroy' => 'reporting.destroy',
    ];

    protected $storeRequest  = \Illuminate\Http\Request::class;
    protected $updateRequest = \Illuminate\Http\Request::class;

    protected $moduleName = 'reporting';
    protected $SettingsHelper;
    protected $UserHelper;

    public function __construct()
    {
        parent::__construct();
        $this->SettingsHelper = new SettingsHelper();
        $this->UserHelper = new UserHelper();
    }

    public function blacklist(Request $request)
    {
        try {
            $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;
                    }
                    
                    $data[] = [
                        'number' => $key,
                        'description' => $value,
                    ];
                }
            }

            $perPage = $request->get('per_page', 10);
            $currentPage = $request->get('page', 1);
            $offset = ($currentPage - 1) * $perPage;
            $paginatedData = array_slice($data, $offset, $perPage);
            
            $paginator = new \Illuminate\Pagination\LengthAwarePaginator(
                $paginatedData,
                count($data),
                $perPage,
                $currentPage,
                ['path' => $request->url(), 'query' => $request->query()]
            );

            return $this->respond(true, [
                'data' => $paginator,
                'total' => count($data)
            ], [], 'Blacklist retrieved successfully');

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Failed to retrieve blacklist', 500);
        }
    }

    public function blacklistAdd(Request $request)
    {
        try {
            $validator = Validator::make($request->all(), [
                'number' => 'required',
                'desc' => 'not_in:1'
            ], [
                'number.required' => 'Number/CallerID cannot be blank!',
                'desc.not_in' => 'Description not allowed, reserved character!'
            ]);

            if ($validator->fails()) {
                return $this->respond(false, [], $validator->errors()->all(), 'Validation Error', 422);
            }

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

            \Log::debug("Adding to blacklist: number={$num}, desc={$desc}");

            $safeNum  = escapeshellarg($num);
            $safeDesc = escapeshellarg($desc);
            $command  = "asterisk -rx " . escapeshellarg("database put blacklist {$num} {$desc}");
            $results  = shell_exec($command);

            if (strpos($results, 'Updated database successfully') !== false) {
                return $this->respond(true, ['number' => $num], [], "$num Blocked Successfully");
            }

            return $this->respond(false, [], ['error' => $results], 'Failed to add to blacklist', 500);

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Internal Server Error', 500);
        }
    }

    public function performance(Request $request)
    {
        try {
            $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');
                }
            }

            // Same data source as web controller: inbound_reports
            $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 (same company-level exclusion as web)
            $excludedQueues = CompanySettings::get('report_excluded_groups');
            if (!empty($excludedQueues) && is_array($excludedQueues)) {
                $performance_data->whereNotIn('enter_queuename', $excludedQueues);
            }

            // Apply user permissions (same as web)
            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%");
                }
            }

            // Optional narrowing filter (documented for the API); always within accessible queues
            if ($request->filled('group_ext')) {
                $performance_data->where('enter_queuename', $request->group_ext);
            }

            $performance_data = $performance_data->get();

            $report = $this->getReport($performance_data, $conds);

            // agent_names: name → id  (used to join with missed)
            $agent_names_query = User::where('company_id', $user->company->id ?? 1)->visibleInReports();
            if ($user->access_to_all_entity != '1') {
                $agent_names_query = $agent_names_query->whereHas('groups', function ($q) use ($user) {
                    $q->whereIn('groups.id', $user->accessibleQueueIds());
                });
            }
            $agent_names_by_name = $agent_names_query->pluck('id', 'name')->toArray();

            // Agent codes (users.code) — same field and source as Outbound Performance's
            // agent_code; mirrors the web performance() $agent_codes (code keyed by user id).
            // Same visibleInReports() + accessible-queue scope as $agent_names_query.
            $agent_codes_by_id = (clone $agent_names_query)->pluck('code', 'id')->toArray();

            // Same extension set the web controller passes to getMissedCalls()
            $company_extensions = User::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());
                });
            }
            $company_extensions = $company_extensions->visibleInReports()->pluck('id')->toArray();

            $missed = $this->getMissedCalls($conds, $company_extensions);

            // Build per-agent combined array (same as blade view)
            $agents = [];
            foreach ($report['agents'] ?? [] as $agent_name => $one_agent) {
                $agent_id          = $agent_names_by_name[$agent_name] ?? null;
                $missed_data       = $agent_id ? ($missed[$agent_id] ?? []) : [];
                $inbound_ring_time = $missed_data['inbound_ring_time'] ?? 0;
                $agent_calls       = $one_agent['agent_calls'] ?: 1; // avoid division by zero

                $agents[] = [
                    'agent_id'         => $agent_id,
                    'agent_name'       => $agent_name,
                    'agent_calls'      => $one_agent['agent_calls'],
                    'inbound_rejected' => $missed_data['inbound_rejected'] ?? 0,
                    'inbound_noanswer' => $missed_data['inbound_noanswer'] ?? 0,
                    'avg_ring_time'    => $this->SettingsHelper->format_time((int) ($inbound_ring_time / $agent_calls)),
                    'total_talk_time'  => $this->SettingsHelper->format_time($one_agent['agent_talk_time']),
                    'avg_talk_time'    => $this->SettingsHelper->format_time((int) ($one_agent['agent_talk_time'] / $agent_calls)),
                    'agent_hangup'     => $one_agent['agent_hangup'],
                    'customer_hangup'  => $one_agent['customer_hangup'],
                    // Added from the web implementation (additive; existing keys above unchanged)
                    'avg_handle_time'  => $this->SettingsHelper->format_time((int) ($one_agent['aht'] ?? 0)),
                    'agent_transfers'  => $one_agent['agent_transfers'] ?? 0,
                    'transferred_out'  => $one_agent['transferred_out'] ?? 0,
                    'transferred_in'   => $one_agent['transferred_in'] ?? 0,
                    // Additive field (same users.code source as Outbound Performance);
                    // existing keys above unchanged.
                    'agent_code'       => $agent_id ? ($agent_codes_by_id[$agent_id] ?? null) : null,
                ];
            }

            return $this->respond(true, [
                'summary' => [
                    'overall_total_calls'    => $report['overall_total_calls']    ?? 0,
                    'overall_answered_calls' => $report['overall_answered_calls'] ?? 0,
                    'overall_dropped_calls'  => $report['overall_dropped_calls']  ?? 0,
                    'overall_failover_calls' => $report['overall_failover_calls'] ?? 0,
                    'overall_talk_time'      => $report['overall_talk_time']      ?? '00:00:00',
                    'average_ring_time'      => $report['average_ring_time']      ?? '00:00:00',
                    'average_call_time'      => $report['average_call_time']      ?? '00:00:00',
                    'service_level'          => $report['service_level']          ?? [],
                    // Added from the web implementation (additive; existing keys above unchanged)
                    'overall_ring_time'       => $this->SettingsHelper->format_time((int) ($report['overall_ring_time'] ?? 0)),
                    'overall_total_transfers' => $report['overall_total_transfers'] ?? 0,
                    'service_level_over'      => $report['service_level_over']      ?? 0,
                ],
                'agents'  => $agents,
                'queues'  => $report['resultsForQueues'] ?? [],
                'filters' => [
                    'daterange' => $conds['from'] . ' - ' . $conds['to'],
                    'group_ext' => $request->group_ext ?? null,
                ],
            ], [], 'Performance report retrieved successfully');

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Failed to generate performance report', 500);
        }
    }

    /**
     * Exact mirror of Modules\Reporting\Http\Controllers\ReportingController::getReport().
     * Keep both implementations in sync when changing business logic.
     */
    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)
            )));

            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 getMissedCalls($conds = [], $extensions = [])
    {
        if (empty($extensions)) {
            return [];
        }

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

        // Fetch raw rows — PHP groups/aggregates, avoiding heavy SQL filesort 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 outPerformance(Request $request)
    {
        try {
            $user  = Auth::user();
            $db    = config('database.connections.mysql.database');
            $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');
                }
            }

            // Same company-user scope as web (role/reporting permission aware)
            $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();

            // Shared aggregate columns (used in both by_date and by_agent queries)
            $agentColumns = [
                DB::raw('cdr.cnum as agent_ext'),
                DB::raw('COUNT(*) as total_calls'),
                DB::raw('SUM(IF(cdr.disposition = "ANSWERED", 1, 0)) as answered_calls'),
                DB::raw('SUM(IF(cdr.disposition = "NO ANSWER", 1, 0)) as no_answer_calls'),
                DB::raw('SUM(IF(cdr.disposition = "BUSY", 1, 0)) as busy_calls'),
                DB::raw('SUM(IF(cdr.disposition = "FAILED", 1, 0)) as failed_calls'),
                DB::raw('SEC_TO_TIME(SUM(cdr.duration)) as total_duration'),
                DB::raw('SEC_TO_TIME(FLOOR(AVG(cdr.duration))) as avg_duration'),
                DB::raw('SEC_TO_TIME(SUM(cdr.billsec)) as total_talk_time'),
                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 avg_talk_time'),
                DB::raw("{$db}.users.name as agent_name"),
            ];

            // By Date: one row per agent per day
            $byDateQuery = DB::table('asteriskcdrdb.cdr')
                ->leftJoin("{$db}.users", "{$db}.users.id", 'cdr.cnum')
                ->select(array_merge([DB::raw('DATE(cdr.calldate) as call_date')], $agentColumns))
                ->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');

            // By Agent: one row per agent (totals)
            $byAgentQuery = DB::table('asteriskcdrdb.cdr')
                ->leftJoin("{$db}.users", "{$db}.users.id", 'cdr.cnum')
                ->select(array_merge($agentColumns, [DB::raw("{$db}.users.code as agent_code")]))
                ->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');

            // Summary totals
            $summaryQuery = DB::table('asteriskcdrdb.cdr')
                ->leftJoin("{$db}.users", "{$db}.users.id", 'cdr.cnum')
                ->select([
                    DB::raw('COUNT(DISTINCT cdr.cnum) as agents_count'),
                    DB::raw('COUNT(*) as total_calls'),
                    DB::raw('SUM(IF(cdr.disposition = "ANSWERED", 1, 0)) as answered_calls'),
                    DB::raw('SUM(IF(cdr.disposition = "NO ANSWER", 1, 0)) as no_answer_calls'),
                    DB::raw('SUM(IF(cdr.disposition = "BUSY", 1, 0)) as busy_calls'),
                    DB::raw('SUM(IF(cdr.disposition = "FAILED", 1, 0)) as failed_calls'),
                    DB::raw('SEC_TO_TIME(SUM(cdr.duration)) as total_duration'),
                    DB::raw('SEC_TO_TIME(FLOOR(AVG(cdr.duration))) as avg_duration'),
                    DB::raw('SEC_TO_TIME(SUM(cdr.billsec)) as total_talk_time'),
                    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 avg_talk_time'),
                ])
                ->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)) {
                $byDateQuery->where('cdr.cnum', $request->user_ext);
                $byAgentQuery->where('cdr.cnum', $request->user_ext);
                $summaryQuery->where('cdr.cnum', $request->user_ext);
            }

            return $this->respond(true, [
                'summary'  => $summaryQuery->first(),
                'by_agent' => $byAgentQuery->get(),
                'by_date'  => $byDateQuery->get(),
                'filters'  => [
                    'daterange' => $conds['from'] . ' - ' . $conds['to'],
                    'user_ext'  => $request->user_ext ?? null,
                ],
            ], [], 'Outbound performance report retrieved successfully');

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Failed to generate outbound performance report', 500);
        }
    }

    /**
     * GET /api/reporting/insights
     *
     * Returns 7-day overview of incoming vs outgoing call counts and talktime per day.
     * Mirrors ReportingController::insights().
     */
    public function insights()
    {
        try {
            $week_ago = Carbon::today()->subDays(7);
            $today    = Carbon::today();

            $sql_day = 'CONCAT(DATE_FORMAT(created_at,"%Y"),"-",DATE_FORMAT(created_at,"%m"),"-",DATE_FORMAT(created_at,"%d")) AS the_day';
            $sql_day_group = str_replace(' AS the_day', '', $sql_day);

            $in_out_7_days = DB::table('calls')
                ->select(
                    'direction_id',
                    DB::raw($sql_day),
                    DB::raw('COUNT(*) AS count'),
                    DB::raw('SUM(duration) AS total_duration')
                )
                ->groupBy(DB::raw($sql_day_group), 'direction_id')
                ->whereBetween('created_at', [$week_ago->format('Y-m-d 00:00:00'), $today->format('Y-m-d 23:59:59')])
                ->get();

            // Map direction IDs to names
            $directions = DB::table('direction_types')->pluck('name', 'id');

            $keyed = [];
            foreach ($in_out_7_days as $row) {
                $direction = $directions[$row->direction_id] ?? 'Unknown';
                $keyed[$row->the_day][$direction] = [
                    'count'    => $row->count,
                    'talktime' => $row->total_duration,
                ];
            }

            $chart_labels      = [];
            $incoming_counts   = [];
            $outgoing_counts   = [];
            $incoming_talktime = [];
            $outgoing_talktime = [];

            $period = \Carbon\CarbonPeriod::create($week_ago, $today);
            foreach ($period as $date) {
                $formatted           = $date->format('Y-m-d');
                $chart_labels[]      = $formatted;
                $incoming_counts[]   = $keyed[$formatted]['Incoming']['count']   ?? 0;
                $outgoing_counts[]   = $keyed[$formatted]['Outgoing']['count']   ?? 0;
                $incoming_talktime[] = $keyed[$formatted]['Incoming']['talktime'] ?? 0;
                $outgoing_talktime[] = $keyed[$formatted]['Outgoing']['talktime'] ?? 0;
            }

            return $this->respond(true, [
                'labels'            => $chart_labels,
                'incoming_counts'   => $incoming_counts,
                'outgoing_counts'   => $outgoing_counts,
                'incoming_talktime' => $incoming_talktime,
                'outgoing_talktime' => $outgoing_talktime,
                'period'            => [
                    'from' => $week_ago->format('Y-m-d'),
                    'to'   => $today->format('Y-m-d'),
                ],
            ], [], 'Insights retrieved successfully');

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Failed to retrieve insights', 500);
        }
    }

    /**
     * GET /api/reporting/rejected-calls
     *
     * Returns inbound rejected (BUSY) calls for a specific user/extension.
     * Mirrors ReportingController::getRejectedCalls().
     *
     * Filters (query params):
     *  - user_id : Extension/user id (dst in CDR). Required.
     *  - from    : Start datetime "YYYY-MM-DD HH:mm:ss". Required.
     *  - to      : End datetime "YYYY-MM-DD HH:mm:ss". Required.
     */
    public function getRejectedCalls(Request $request)
    {
        try {
            $userId = $request->user_id;
            $from   = $request->from;
            $to     = $request->to;

            if (!$userId || !$from || !$to) {
                return $this->respond(false, [], ['error' => 'user_id, from and to are required'], 'Bad Request', 422);
            }

            $rejectedCalls = DB::table('asteriskcdrdb.cdr')
                ->select(
                    DB::raw('calldate as call_time'),
                    DB::raw('src as caller_number'),
                    '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();

            return $this->respond(true, [
                'data'    => $rejectedCalls,
                'count'   => $rejectedCalls->count(),
                'filters' => compact('userId', 'from', 'to'),
            ], [], 'Rejected calls retrieved successfully');

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Failed to retrieve rejected calls', 500);
        }
    }

    /**
     * GET /api/reporting/no-answer-calls
     *
     * Returns inbound no-answer/failed calls for a specific user/extension.
     * Mirrors ReportingController::getNoAnswerCalls().
     *
     * Filters (query params):
     *  - user_id : Extension/user id (dst in CDR). Required.
     *  - from    : Start datetime "YYYY-MM-DD HH:mm:ss". Required.
     *  - to      : End datetime "YYYY-MM-DD HH:mm:ss". Required.
     */
    public function getNoAnswerCalls(Request $request)
    {
        try {
            $userId = $request->user_id;
            $from   = $request->from;
            $to     = $request->to;

            if (!$userId || !$from || !$to) {
                return $this->respond(false, [], ['error' => 'user_id, from and to are required'], 'Bad Request', 422);
            }

            $noAnswerCalls = DB::table('asteriskcdrdb.cdr')
                ->select(
                    DB::raw('calldate as call_time'),
                    DB::raw('src as caller_number'),
                    '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();

            return $this->respond(true, [
                'data'    => $noAnswerCalls,
                'count'   => $noAnswerCalls->count(),
                'filters' => compact('userId', 'from', 'to'),
            ], [], 'No-answer calls retrieved successfully');

        } catch (\Throwable $th) {
            return $this->respond(false, [], ['error' => $th->getMessage()], 'Failed to retrieve no-answer calls', 500);
        }
    }

}