<?php

namespace Modules\Reporting\Http\Controllers;

use Modules\Platform\Core\Http\Controllers\AppBaseController;
use Modules\Platform\Core\Helper\SettingsHelper;
use Modules\Platform\Core\Helper\UserHelper;
use Modules\Platform\User\Entities\User;
use Modules\Platform\User\Entities\Group;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use App\Charts\CallDist;
use Illuminate\Http\Request;
use DataTables;
use DB;
use Illuminate\Support\Facades\Response;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use ZipArchive;
use Modules\Platform\Core\Helper\CompanySettings;
use Illuminate\Support\Facades\Storage;
use Modules\Reporting\Entities\InboundReport;
use App\Models\CallAiAnalysis;
use Modules\Reporting\Http\Controllers\Concerns\RendersAnalysisAction;


class InboundController extends AppBaseController
{
    use RendersAnalysisAction;

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

    public function inbound(Request $request)
    {
        $conds = $this->SettingsHelper->defaultConds();
        $user = Auth::user();

        // Get admins for filtering if needed
        $admins = User::where('company_id', $user->company_id)
            ->whereHas('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->pluck('id')
            ->toArray();

        $company_groups = $user->accessibleQueueIds();
        
        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');
            }
        }
        
        if ($request->ajax()) {
            $data = InboundReport::whereBetween('call_time', [$conds['from'], $conds['to']])
                ->where('call_status', 'answered')
                ->whereIn('enter_queuename', $company_groups);
                
            
            // Apply filters
            if(isset($request->did_ext)) {
                $data = $data->where('did', $request->did_ext);
            }

            if ($request->transfers_only == '1') {
                $data = $data->where('transfers_count', '>', 0);
            }
            
            if(!empty($request->search['value'])) {
                $searchValue = strtolower(trim($request->search['value']));
                $data = $data->where(function ($query) use ($searchValue) {
                    $query->orWhere(DB::raw('LOWER(queue_description)'), 'LIKE', '%' . $searchValue . '%')
                        ->orWhere(DB::raw('LOWER(agent)'), 'LIKE', '%' . $searchValue . '%')
                        ->orWhere(DB::raw('LOWER(event)'), 'LIKE', '%' . $searchValue . '%')
                        ->orWhere(DB::raw('LOWER(caller_number)'), 'LIKE', '%' . $searchValue . '%');
                });
            }
            
            // Apply user permissions
            if($user->roles[0]->name != 'company_manager'){
                $data = $data->whereNotIn('agent', $admins);
            }
            if($user->access_to_all_entity != '1'){
                $data = $data->whereIn('enter_queuename', $user->accessibleQueueIds());
                if($user->roles[0]->name == 'user'){
                    $data = $data->where('agent', 'LIKE', "%$user->name%");
                }
            }
            // The AI analysis state for each row, resolved in the same statement
            // (correlated subquery) so a page of calls never fans out into one
            // query per row.
            $data = $data->addSelect([
                'ai_analysis_status' => CallAiAnalysis::query()
                    ->select('status')
                    ->whereColumn('call_ai_analyses.linkedid', 'inbound_reports.linkedid')
                    ->orderByDesc('id')
                    ->limit(1),
            ]);

            return Datatables::of($data)
                ->addIndexColumn()
            // Internal alias only: do not ship it to the browser.
            ->removeColumn('ai_analysis_status')
                ->addColumn('network', function($row){
                    return '<a href="javascript:void(0)" class="edit btn btn-primary btn-sm" style="color:white">Download</a>';
                })
                ->addColumn('transfer_info', function($row){
                    if ($row->transfers_count > 0) {
                        $btn = '<button class="btn btn-info btn-sm view-transfer" 
                                data-linkedid="' . $row->linkedid . '"
                                data-toggle="modal" data-target="#transferModal">
                                View Transfers (' . $row->transfers_count . ')
                            </button>';
                    } else {
                        $btn = '<span class="text-muted">No Transfers</span>';
                    }
                    return $btn;
                })
                ->addColumn('action', function($record){
                    $btn = '';
                    if(\Auth::user()->hasPermissionTo('reporting.listen')) {
                        $display_url = route('reporting.listen.page', ['id' => $record->linkedid]);
                        $btn = '<a href="' . $display_url . '" onclick="missedLink(event, ' . $record->linkedid . ')" target="_blank" class="edit btn btn-info btn-sm" style="color:white">Listen</a>';

                        if (empty($record->data5) || is_null($record->data5) || $record->data5 == 0) {
                        } elseif ($record->data5 == 1) {
                            $btn = '<a href="' . $display_url . '" target="_blank" class="edit btn btn-success btn-sm" style="color:white">Listen</a>';
                        }
                    }

                    // AI summary: Submit while there is no analysis, the stored
                    // analysis once it exists (requirement: a link styled as a
                    // button through to the analysis page).
                    $analysis = $this->analysisActionHtml((string) $record->linkedid, $record->ai_analysis_status ?? null, 'inbound');
                    $action = trim($btn . ' ' . $analysis);

                    return $action !== '' ? $action : 'Not Avail';
                })
                ->editColumn('call_time', function($row) {
                    return date('Y-m-d h:i:s A', strtotime($row->call_time));
                })
                ->rawColumns(['action', 'transfer_info', 'network'])
                ->make(true);
        }
        
        $view = view('reporting::inbound');

        // The DID filter is read from the FreePBX `asterisk` database, which does
        // not exist on every box (a bare-asterisk box has no `incoming` table) and
        // the application user is not granted it everywhere. A missing dropdown
        // must not take the whole report down with it.
        try {
            $did_options = DB::table('asterisk.incoming')
                ->where('extension', 'like', '002022%')
                ->pluck('extension', 'extension')
                ->toArray();
        } catch (\Throwable $e) {
            $did_options = [];
        }

        $did_options = [
        'dids' => $did_options
        ];

        $agent_names = User::where('company_id', Auth::user()->company_id)
            ->visibleInReports()
            ->pluck('name','id')->toArray();
    
        $filter_area = view('reporting::widgets.filter_area');
        $filter_area->with('conds',$conds);
        $filter_area->with('did_options',$did_options);
        $filter_area->with('did_ext',$request->did_ext);
        $filter_area->with('submit_route',route('reporting.ccinbound'));
        
        $view->with('filter_area',$filter_area);
        $view->with('conds',$conds);
        $view->with('agent_names',$agent_names);
        $view->with('did_ext',$request->did_ext);

        return $view;
    }

    // Fetch transfer details for the modal
    public function getTransfers($linkedid)
    {
        try {
            // All CDR rows for this call, ordered chronologically
            $cdrRows = DB::table('asteriskcdrdb.cdr')
                ->where('linkedid', $linkedid)
                ->orderBy('calldate', 'asc')
                ->orderBy('sequence', 'asc')
                ->get();

            if ($cdrRows->isEmpty()) {
                return response()->json(['success' => true, 'transfers' => [], 'count' => 0]);
            }

            // The initial agent answer = first ANSWERED Dial (the agent who originally picked up)
            $initialDial = $cdrRows->where('lastapp', 'Dial')->where('disposition', 'ANSWERED')->first();
            if (!$initialDial) {
                return response()->json(['success' => true, 'transfers' => [], 'count' => 0]);
            }
            $initialUniqueId = $initialDial->uniqueid;

            // ── 1. Queue-to-queue transfers ────────────────────────────────────
            // CDR rows where lastapp='Queue' AND uniqueid != linkedid
            // (the original queue call has uniqueid = linkedid)
            $queueTransferRows = $cdrRows
                ->where('lastapp', 'Queue')
                ->filter(fn($r) => $r->uniqueid !== $linkedid)
                ->sortBy('calldate')
                ->values();

            $knownUniqueIds = [$linkedid, $initialUniqueId];
            $transfers = [];

            foreach ($queueTransferRows as $qt) {
                $queueExt = trim(explode(',', $qt->lastdata)[0] ?? '');

                // The transfer agent comes from a NEW channel (different uniqueid)
                // that dials after the Queue row was created
                $agentDial = $cdrRows
                    ->where('lastapp', 'Dial')
                    ->filter(fn($r) =>
                        !in_array($r->uniqueid, $knownUniqueIds) &&
                        $r->calldate >= $qt->calldate
                    )
                    ->sortBy('calldate')
                    ->first();

                if ($agentDial) {
                    $knownUniqueIds[] = $agentDial->uniqueid;
                }

                $transfers[] = [
                    '_type'       => 'queue',
                    'calldate'    => $qt->calldate,
                    'queue_ext'   => $queueExt,
                    'agent_ext'   => $agentDial?->dst,
                    'disposition' => $agentDial?->disposition ?? 'NO ANSWER',
                    'duration'    => $agentDial?->duration ?? 0,
                ];
            }

            // ── 2. Direct extension transfers ─────────────────────────────────
            // Additional Dial rows within the SAME uniqueid as the initial agent answer.
            // These appear when the agent directly transfers to another extension
            // without going through a queue.
            $initialChannelDials = $cdrRows
                ->where('lastapp', 'Dial')
                ->where('uniqueid', $initialUniqueId)
                ->sortBy('calldate')
                ->values()
                ->slice(1); // skip the initial answer itself

            foreach ($initialChannelDials as $dialRow) {
                // Skip if already covered by a queue transfer (queue row at same time)
                $coveredByQueue = $queueTransferRows
                    ->filter(fn($qt) => $qt->calldate <= $dialRow->calldate)
                    ->isNotEmpty();

                if ($coveredByQueue) {
                    continue;
                }

                // Extract the actual extension from dstchannel (e.g. PJSIP/998-xxx → 998)
                $ext = null;
                if (!empty($dialRow->dstchannel)) {
                    preg_match('/[\/:](\d{3,6})[-@]/', $dialRow->dstchannel, $m);
                    $ext = $m[1] ?? null;
                }
                $ext = $ext ?? $dialRow->dst;
                if (substr($ext, 0, 2) == '99' && strlen($ext) > 4) {
                    $ext = substr($ext, 2);
                }

                $transfers[] = [
                    '_type'       => 'direct',
                    'calldate'    => $dialRow->calldate,
                    'queue_ext'   => null,
                    'agent_ext'   => $ext,
                    'disposition' => $dialRow->disposition,
                    'duration'    => $dialRow->duration ?? 0,
                ];
            }

            if (empty($transfers)) {
                return response()->json(['success' => true, 'transfers' => [], 'count' => 0]);
            }

            usort($transfers, fn($a, $b) => strcmp($a['calldate'], $b['calldate']));

            // ── Enrich: queue descriptions ─────────────────────────────────────
            $queueExts = array_values(array_filter(array_unique(array_column($transfers, 'queue_ext'))));
            $queueDescriptions = [];
            if (!empty($queueExts)) {
                $queueDescriptions = DB::table('asterisk.queues_config')
                    ->whereIn('extension', $queueExts)
                    ->pluck('descr', 'extension')
                    ->toArray();
            }

            // ── Enrich: agent names ────────────────────────────────────────────
            $agentExts = array_values(array_filter(array_unique(array_column($transfers, 'agent_ext'))));
            $agentNames = [];
            if (!empty($agentExts)) {
                $agentNames = User::whereIn('id', $agentExts)
                    ->pluck('name', 'id')
                    ->toArray();
            }

            // ── Build response ─────────────────────────────────────────────────
            $result = array_map(fn($t) => [
                'calldate'       => $t['calldate'],
                'queue_ext'      => $t['queue_ext'],
                'queue_name'     => $t['queue_ext']
                                    ? ($queueDescriptions[$t['queue_ext']] ?? $t['queue_ext'])
                                    : 'Internal Transfer',
                'user_extension' => $t['agent_ext'],
                'user_name'      => $t['agent_ext']
                                    ? ($agentNames[$t['agent_ext']] ?? $t['agent_ext'])
                                    : null,
                'disposition'    => $t['disposition'],
                'duration'       => $t['duration'],
            ], $transfers);

            return response()->json([
                'success'   => true,
                'transfers' => array_values($result),
                'count'     => count($result),
            ]);

        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error fetching transfer data: ' . $e->getMessage()
            ]);
        }
    }

    public function listenFile($id)
    {
        $record = \DB::table('asteriskcdrdb.cdr') 
            ->where('linkedid', $id) 
            ->where('recordingfile', 'like', "%$id%")
            ->first();

        if (!$record) {
            abort(404);
        }

        $calldate = date('Y/m/d', strtotime($record->calldate));
        $record_name = $record->recordingfile;

        if (is_null($record_name) || empty($record_name)) {
            abort(404);
        }

        $file_path = "/var/spool/asterisk/monitor/" . $calldate . '/' . $record_name;

        if (file_exists($file_path)) {
            return response()->file($file_path);
        }

        if (CompanySettings::get('S_COMPANY_S3_STORAGE')) {
            $remotePath = "/calls/{$calldate}/{$record_name}";

            try {
                if (!Storage::disk('s3')->exists($remotePath)) {
                    abort(404);
                }

                $fileContent = Storage::disk('s3')->get($remotePath);
                $fileSize = Storage::disk('s3')->size($remotePath);

                return response($fileContent, 200, [
                    'Content-Type' => 'audio/wav',
                    'Content-Length' => $fileSize,
                    'Accept-Ranges' => 'bytes',
                    'Content-Disposition' => 'inline'
                ]);

            } catch (\Exception $e) {
                abort(404);
            }
        }

        abort(404);
    }

    public function listenPageFile($id)
    {
        $record = \DB::table('asteriskcdrdb.cdr') 
            ->where('linkedid', $id) 
            ->where('recordingfile', 'like', "%$id%")
            ->first();

        if (!$record) {
            abort(404);
        }

        $calldate = date('Y/m/d', strtotime($record->calldate));
        $record_name = $record->recordingfile;

        if (is_null($record_name) || empty($record_name)) {
            abort(404);
        }

        $file_path = "/var/spool/asterisk/monitor/" . $calldate . '/' . $record_name;

        $allowDownload = (bool) auth()->user()->allow_download_recording;

        if (!file_exists($file_path)) {
            if (CompanySettings::get('S_COMPANY_S3_STORAGE')) {
                $remotePath = "/calls/{$calldate}/{$record_name}";

                if(Storage::disk('s3')->exists($remotePath)) {
                    $view = view('reporting::playRecoed');
                    $view->with('record_id', $id);
                    $view->with('record_name', $record_name);
                    $view->with('allow_download', $allowDownload);

                    return $view;

                } else {
                    abort(404);
                }
            }else {
                abort(404);
            }
        }else {
            $view = view('reporting::playRecoed');
            $view->with('record_id', $id);
            $view->with('record_name', $record_name);
            $view->with('allow_download', $allowDownload);

            return $view;
        }
    }


    public function excelReport(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) {
                $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();
        $admins = User::where('company_id', $user->company_id)
            ->whereHas('roles', function ($q) {
                $q->where('name', 'company_manager');
            })
            ->pluck('id')
            ->toArray();
        $company_groups = $user->accessibleQueueIds();
        $data = InboundReport::whereBetween('call_time', [$conds['from'], $conds['to']])
            ->whereIn('hangup_type', ['COMPLETECALLER','COMPLETEAGENT'])
            ->whereIn('enter_queuename', $company_groups);
        // Apply filters
        if(isset($request->did_ext)) {
            $data = $data->where('did', $request->did_ext);
        }
        
        if(!empty($request->search['value'])) {
            $searchValue = strtolower(trim($request->search['value']));
            $data = $data->where(function ($query) use ($searchValue) {
                $query->orWhere(DB::raw('LOWER(queue_description)'), 'LIKE', '%' . $searchValue . '%')
                    ->orWhere(DB::raw('LOWER(agent)'), 'LIKE', '%' . $searchValue . '%')
                    ->orWhere(DB::raw('LOWER(event)'), 'LIKE', '%' . $searchValue . '%')
                    ->orWhere(DB::raw('LOWER(caller_number)'), 'LIKE', '%' . $searchValue . '%');
            });
        }
        
        // Apply user permissions
        if($user->roles[0]->name != 'company_manager') {
            $data = $data->whereNotIn('agent', $admins);
        }

        if($user->access_to_all_entity != '1') {
            $data = $data->whereIn('enter_queuename', $user->accessibleQueueIds());
            if($user->roles[0]->name == 'user') {
                $data = $data->where('agent', 'LIKE', "%$user->name%");
            }
        }

        $data = $data->orderBy('call_time', 'DESC')->get();

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

        $callback = function () use ($data) {
            $excel = new Spreadsheet();

            $sheet = $excel->getActiveSheet();
            $sheet->setCellValue('A1', 'Time');
            $sheet->setCellValue('B1', 'Queue');
            $sheet->setCellValue('C1', 'Agent');
            $sheet->setCellValue('D1', 'Hangup');
            $sheet->setCellValue('E1', 'Ringtime');
            $sheet->setCellValue('F1', 'Talktime');
            $sheet->setCellValue('G1', 'Number');
            $sheet->setCellValue('H1', 'DID/Channel');
            $sheet->setCellValue('I1', 'Transfers');

            $user = Auth::user();
            $hasSurveyPermission = $user->hasPermissionTo('reporting.survey');
            if ($hasSurveyPermission) {
                $sheet->setCellValue('J1', 'Survey');
            }

            $headerStyle = ['font' => ['bold' => true]];
            $sheet->getStyle('A1:' . ($hasSurveyPermission ? 'J1' : 'I1'))->applyFromArray($headerStyle);

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

            $rowNum = 2;
            foreach ($data as $row) {

            $agent = $row->agent;
            $extension = null;
            if (preg_match('/Local\/(\d+)@/', $agent, $matches)) {
                $extension = $matches[1];
            }
            $agent = $extension ? ($agent_names[$extension] ?? $agent) : $agent;
            
            $sheet->setCellValue('A' . $rowNum, date('Y-m-d H:i:s', strtotime($row->call_time)));
            $sheet->setCellValue('B' . $rowNum, $row->queue_description);
            $sheet->setCellValue('C' . $rowNum, $agent);
            $sheet->setCellValue('D' . $rowNum, $row->event);
            $sheet->setCellValue('E' . $rowNum, gmdate('H:i:s', $row->ringtime));
            $sheet->setCellValue('F' . $rowNum, gmdate('H:i:s', $row->talktime));
            $sheet->setCellValue('G' . $rowNum, $row->caller_number);
            $sheet->setCellValue('H' . $rowNum, $row->did);
            $sheet->setCellValue('I' . $rowNum, $row->transfers_count);
            if ($hasSurveyPermission) {
                $sheet->setCellValue('J' . $rowNum, $row->survey);
            }
            $rowNum++;
            }

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

        return Response::stream($callback, 200, $headers);
    }
    
    function inDone($id) {
        try {
            DB::update("update asteriskcdrdb.queuelog set data5 = 1 where id = $id");
            DB::update("update inbound_reports set data5 = 1 where linkedid = $id");
            return true;
        } catch (\Throwable $th) {
            throw $th;
        }
    }

    function inDist(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();
        $filter = [
            'conds' => $conds,
            // Group (queue) filter — same name and semantics as the Inbound
            // report API (group_ext narrows to one accessible queue).
            'filter_queue' => $request->group_ext,
        ];
        $data = $this->getInDistribution($filter);
        $view = view('reporting::inDist');
        // tables
            $monthsDatatables = Datatables::of($data['months_table'])
                ->addIndexColumn()
                ->addColumn('offered', function($row){
                    return $row['answord'] + $row['missed'];
                })
                ->addColumn('rate', function($row){
                    $total = $row['answord'] + $row['missed'];
                    
                    return $total > 0
                        ? round(($row['answord'] / $total) * 100, 1) . ' %'
                        : '0 %';
                })
            ->make(true);
            $monthsDatatablesJson = json_encode($monthsDatatables->getData()->data);
            $view->with('monthsDatatablesJson',$monthsDatatablesJson);
            // 
            $daysDatatables = Datatables::of($data['days_table'])
                ->addIndexColumn()
                ->addColumn('offered', function($row){
                    return $row['answord'] + $row['missed'];
                })
                ->addColumn('rate', function($row){
                    $total = $row['answord'] + $row['missed'];

                    return $total > 0
                        ? round(($row['answord'] / $total) * 100, 1) . ' %'
                        : '0 %';
                })
            ->make(true);
            $daysDatatablesJson = json_encode($daysDatatables->getData()->data);
            $view->with('daysDatatablesJson',$daysDatatablesJson);
            // 
            $hourDatatables = Datatables::of($data['hourse_table'])
                ->addIndexColumn()
                ->addColumn('offered', function($row){
                    return $row['answord'] + $row['missed'];
                })
                ->addColumn('rate', function($row){
                    $total = $row['answord'] + $row['missed'];

                    return $total > 0
                        ? round(($row['answord'] / $total) * 100, 1) . ' %'
                        : '0 %';

                })
            ->make(true);
            $hourDatatablesJson = json_encode($hourDatatables->getData()->data);
            $view->with('hourDatatablesJson',$hourDatatablesJson);
        // /////////////////////////////////////////////////////

        // charts
            $view->with('MonthDist', new CallDist([
                'labels' => array_values($data['months_processed']),
                'answered' => array_values($data['answered_by_month']),
                'missed' => array_values($data['unans_by_month']),
                'type' => 'in',
            ]));
            $view->with('dayDist', new CallDist([
                'labels' => array_values($data['days_processed']),
                'answered' => array_values($data['answered_by_day']),
                'missed' => array_values($data['unans_by_day']),
                'type' => 'in',
            ]));
            // Build the hourly chart from the exact same rows the data table
            // uses, so both always share the same hour order and values.
            $view->with('hourseDist', new CallDist([
                'labels'   => array_column($data['hourse_table'], 'hourse'),
                'answered' => array_column($data['hourse_table'], 'answord'),
                'missed'   => array_column($data['hourse_table'], 'missed'),
                'type' => 'in',
            ]));
        ////////////////////////////////////////


        // Groups filter options — same UI pattern as the Attendance report
        // (filter_area widget select) and the same permission scope.
        $groups = Group::where('company_id',$user->company->id ?? 1)->pluck('name','id')->toArray();
        if ($user->access_to_all_entity != '1') {
            $groups = Group::whereIn('id', $user->accessibleQueueIds())->pluck('name','id')->toArray();
            if ($user->roles[0]->name == 'user') {
                $groups = null;
            }
        }
        $groups_options = [
            'Groups' => $groups
        ];

        $filter_area = view('reporting::widgets.filter_area');
        $filter_area->with('conds',$conds);
        $filter_area->with('groups_options',$groups_options);
        $filter_area->with('group_ext',$request->group_ext);
        $filter_area->with('submit_route',route('reporting.ccinDist'));

        $view->with('filter_area',$filter_area);
        return $view;
    }

    public function getInDistribution($data = array()){
        $conds = $data['conds'];
        $user = Auth::user();
        // Fetch once and reuse: accessibleQueueIds() runs permission and group
        // queries on every call.
        $company_groups = $user->accessibleQueueIds();

        // Classification relies on call_status ('answered' / 'missed') written by
        // the same services that feed the Inbound and Inbound & Missed reports,
        // instead of mapping queuelog event names. Rows with any other status
        // ('unknown' / NULL on legacy rows) are not counted.
        $query = DB::table('inbound_reports')
            ->select(
                'call_time as time',
                'enter_queuename as queuename',
                'agent',
                'call_status'
            )
            ->whereBetween('call_time', [$conds['from'], $conds['to']])
            ->whereIn('enter_queuename', $company_groups)
            ->whereIn('call_status', ['answered', 'missed']);

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

		if (!empty($data['filter_queue'])) {
            // (array) cast: group_ext arrives as a single scalar id from the
            // filter form, and whereIn() requires an array (a scalar would
            // fatal in cleanBindings()). Also accepts an array unchanged.
            $query = $query->whereIn('enter_queuename', (array) $data['filter_queue']);
        }
        if($user->roles[0]->name != 'company_manager'){
            // Lazy: only queried when the exclusion is actually applied.
            $admins = User::where('company_id', $user->company_id)
                ->whereHas('roles', function ($q) {
                    $q->where('name', 'company_manager');
                })
                ->pluck('name')
                ->toArray();
            $query = $query->whereNotIn('agent', $admins);
        }
        if($user->access_to_all_entity != '1'){
            // Queue restriction is already enforced above via $company_groups
            // (same accessibleQueueIds() result) — only the per-agent
            // restriction remains here.
            if($user->roles[0]->name == 'user'){
                $query = $query->where('agent', 'LIKE', "%$user->name%");
            }
        }

		$return_array = [
            'days_processed' => [],
            'hours_processed' => [],
            'months_processed' => [],
            'months_table' => [],
            'days_table' => [],
            'hourse_table' => [],
            'answered_by_month' => [],
            'answered_by_day' => [],
            'answered_by_hour' => [],
            'unans_by_month' => [],
            'unans_by_day' => [],
            'unans_by_hour' => [],

        ];
        $results = $query->orderBy('time', 'ASC')->get();
		if ($results->isNotEmpty()) {
            $countMonths = 0;
            $pervMonth = NULL;
            $countDays = 0;
            $pervDay = NULL;
			foreach($results as $row){
				// get days_processed
                $time_exploded = explode(" ", $row->time);
                $one_day = trim($time_exploded[0]);
                if (!in_array($one_day, $return_array['days_processed'])) {
                    if ($countDays != 0) {
                        @$return_array['days_table'][] = [
                            'day' => $pervDay,
                            'answord' => $return_array['answered_by_day'][$pervDay] ?? 0,
                            'missed' => $return_array['unans_by_day'][$pervDay] ?? 0,
                        ];
                    }
                    $return_array['days_processed'][] = $one_day;
                    $pervDay = $one_day;
                    $countDays = $countDays + 1;
                }
                
				// get hours_processed
                $hour_exploded = explode(":", @$time_exploded[1]);
                $one_hour = $hour_exploded[0];
                if (!in_array($one_hour, $return_array['hours_processed'])) {
                    $return_array['hours_processed'][] = $one_hour;
                }

                // get months_processed
                $month_year_label = Carbon::parse($row->time)->format('m-Y');
                if (!in_array($month_year_label, $return_array['months_processed'])) {
                    if ($countMonths != 0) {
                        @$return_array['months_table'][] = [
                            'month' => $pervMonth,
                            'answord' => $return_array['answered_by_month'][$pervMonth] ?? 0,
                            'missed' => $return_array['unans_by_month'][$pervMonth] ?? 0,
                        ];
                    }
                    $return_array['months_processed'][] = $month_year_label;
                    $pervMonth = $month_year_label;
                    $countMonths = $countMonths + 1;
                }
                // Classify by call_status
                if ($row->call_status === 'missed') {
                    @$return_array['unans_by_day'][$one_day]++;
                    @$return_array['unans_by_hour'][$one_hour]++;
                    @$return_array['unans_by_month'][$month_year_label]++;
                }
				if ($row->call_status === 'answered') {
                    @$return_array['answered_by_day'][$one_day]++;
                    @$return_array['answered_by_hour'][$one_hour]++;
                    @$return_array['answered_by_month'][$month_year_label]++;
				}

                
			}
            @$return_array['months_table'][] = [
                'month' => $month_year_label,
                'answord' => $return_array['answered_by_month'][$month_year_label] ?? 0,
                'missed' => $return_array['unans_by_month'][$month_year_label] ?? 0,
            ];
            @$return_array['days_table'][] = [
                'day' => $one_day,
                'answord' => $return_array['answered_by_day'][$one_day] ?? 0,
                'missed' => $return_array['unans_by_day'][$one_day] ?? 0,
            ];
            // Sort hours numerically (earliest -> latest) BEFORE building the
            // table so the chart and the data table share the exact same order.
            sort($return_array['hours_processed'], SORT_NUMERIC);

            foreach($return_array['hours_processed'] as $hour){
                @$return_array['hourse_table'][] = [
                    'hourse' => $hour,
                    'answord' => $return_array['answered_by_hour'][$hour] ?? 0,
                    'missed' => $return_array['unans_by_hour'][$hour] ?? 0,
                ];
            }
		}
		return $return_array;
	}
}