<?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\User\Entities\User;
use Modules\Platform\User\Entities\Group;
use Carbon\Carbon;
use Carbon\Carbonite;
use Carbon\CarbonPeriod;
use Carbon\CarbonInterval;
use Illuminate\Support\Facades\Auth;
use App\Charts\CallsOverallChart;
use App\CDR;
use Illuminate\Http\Request;
use DataTables;
use DB;
use Illuminate\Support\Facades\Log;
use Modules\Reporting\Entities\InboundReport;
use Modules\Reporting\Entities\OutboundReport;

// use Illuminate\Foundation\Auth\User;

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

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

    // public function missed(Request $request)
    // {
    //     $user = Auth::user();
    //     $company_groups = Group::where('company_id',$user->company->id ?? 1)->pluck('id');

    //     // Set default time range
    //     $conds = $this->SettingsHelper->defaultConds();

    //     // Process date range from the request
    //     if ($request->has('daterange')) {
    //         try {
    //             $range = explode(' - ', $request->daterange);
    //             $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()) {
    //         // Step 1: Get all callids with ABANDON or EXITWITHTIMEOUT events
    //         $missedCallIds = DB::table('asteriskcdrdb.queuelog')
    //         ->select('callid')
    //         ->whereIn('event', ['ABANDON', 'EXITWITHTIMEOUT'])
    //         ->whereBetween('time', [$conds['from'], $conds['to']])
    //         ->whereIn('queuename', $company_groups)
    //         ->whereNotIn('callid', function($q) {
    //             $q->select('callid')
    //             ->from('asteriskcdrdb.queuelog')
    //             ->whereIn('event', ['COMPLETECALLER', 'COMPLETEAGENT']);
    //         })
    //         ->groupBy('callid')
    //         ->pluck('callid');


    //         if ($missedCallIds->isEmpty()) {
    //             return Datatables::of(collect())->make(true);
    //         }

    //         // Step 2: First ENTERQUEUE per call
    //         $firstEnterQueue = DB::table('asteriskcdrdb.queuelog')
    //             ->select('callid', DB::raw('MIN(id) as first_id'))
    //             ->whereIn('callid', $missedCallIds)
    //             ->where('event', 'ENTERQUEUE')
    //             ->groupBy('callid');

    //         // Step 3: Total duration per call (sum of data3)
    //         $totalDurations = DB::table('asteriskcdrdb.queuelog')
    //             ->select('callid', DB::raw('SUM(CAST(data3 AS UNSIGNED)) as total_duration'))
    //             ->whereIn('callid', $missedCallIds)
    //             ->whereNotNull('data3')
    //             ->groupBy('callid');

    //         // Step 4: Main query
    //         $data = DB::table('asteriskcdrdb.queuelog as q')
    //             ->select([
    //                 'q.id',
    //                 'q.time',
    //                 'q.callid',
    //                 'q.queuename',
    //                 'qc.descr',
    //                 'q.agent',
    //                 'ab.event',
    //                 'ab.status',
    //                 'durations.total_duration as duration',
    //                 'q.data1',
    //                 'q.data2 as numder',
    //                 'q.data3',
    //                 'q.data5'
    //             ])
    //             ->join('asterisk.queues_config as qc', 'qc.extension', '=', 'q.queuename')
    //             ->joinSub($firstEnterQueue, 'feq', function ($join) {
    //                 $join->on('q.id', '=', 'feq.first_id');
    //             })
    //             ->leftJoin(DB::raw('(
    //                 SELECT q1.callid, q1.event, q1.data3, q1.status
    //                 FROM asteriskcdrdb.queuelog q1
    //                 JOIN (
    //                     SELECT callid, MAX(id) as max_id
    //                     FROM asteriskcdrdb.queuelog
    //                     WHERE event IN (\'ABANDON\', \'EXITWITHTIMEOUT\')
    //                     GROUP BY callid
    //                 ) q2 ON q1.callid = q2.callid AND q1.id = q2.max_id
    //             ) as ab'), 'ab.callid', '=', 'q.callid')
    //             ->leftJoinSub($totalDurations, 'durations', function ($join) {
    //                 $join->on('durations.callid', '=', 'q.callid');
    //             })
    //             ->whereIn('q.callid', $missedCallIds)
    //             ->orderBy('q.callid');

    //         // Apply user access restrictions
    //         if ($user->access_to_all_entity != '1' && $user->groups->isNotEmpty()) {
    //             $data = $data->whereIn('q.queuename', $user->groups->pluck('id')->toArray());
    //         }

    //         return Datatables::of($data)
    //             ->addIndexColumn()
    //             ->addColumn('action', function($row){
    //                 if (empty($row->data5)) {
    //                     $btn = '<span onclick="missedLink(event,`'.$row->numder.'`)" class="edit btn btn-danger btn-sm" style="color:white">Missed</span>';
    //                 } else {
    //                     $user = User::find($row->data5);
    //                     if ($user) {
    //                         $btn = '<span value="'.$row->numder.'" class="edit btn btn-success btn-sm" style="cursor: default;color:white;padding-left: 16px;padding-right: 16px;">Done by '.$user->name.'</span>';
    //                     } else {
    //                         $btn = '<span value="'.$row->numder.'" class="edit btn btn-success btn-sm" style="cursor: default;color:white;padding-left: 16px;padding-right: 16px;">Done</span>';
    //                     }
    //                 }
    //                 return $btn;
    //             })
    //             ->addColumn('ring_details', function($row){
    //                 return '<button class="btn btn-info btn-sm" onclick="showRingDetails(`'.$row->callid.'`)">Details</button>';
    //             })
    //             ->editColumn('time', function($row) {
    //                 return Carbon::parse($row->time)->format('Y-m-d h:i:s A');
    //             })
    //             ->rawColumns(['action', 'ring_details'])
    //             ->make(true);
    //     }

    //     $agent_names = User::pluck('name','id')->toArray();


    //     // Prepare and display the interface for non-AJAX requests
    //     $filter_area = view('reporting::widgets.filter_area', [
    //         'conds' => $conds,
    //         'submit_route' => route('reporting.missed')
    //     ]);

    //     return view('reporting::missed_calls', [
    //         'filter_area' => $filter_area,
    //         'conds' => $conds,
    //         'agent_names' => $agent_names,
    //     ]);
    // }

    public function missed(Request $request)
    {
        $user = Auth::user();
        $company_groups = $user->accessibleQueueIds();

        // Set default time range
        $conds = $this->SettingsHelper->defaultConds();

        // Process date range from the request
        if ($request->has('daterange')) {
            try {
                $range = explode(' - ', $request->daterange);
                $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', 'missed')
                ->whereIn('enter_queuename', $company_groups);

            // Apply filters
            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(caller_number)'), 'LIKE', '%' . $searchValue . '%')
                        ->orWhere(DB::raw('LOWER(did)'), 'LIKE', '%' . $searchValue . '%');
                });
            }

            // Apply user access restrictions
            if ($user->access_to_all_entity != '1' && $user->groups->isNotEmpty()) {
                $data = $data->whereIn('enter_queuename', $user->accessibleQueueIds());
            }

            return Datatables::of($data)
                ->addIndexColumn()
                ->addColumn('action', function ($row) use ($user) {
                    if (empty($row->data5)) {
                        $btn = '<span onclick="missedLink(event,`' . $row->caller_number . '`)" class="edit btn btn-danger btn-sm" style="color:white">Missed</span>';
                    } else {
                        $user = User::find($row->data5);
                        if ($user) {
                            $btn = '<span value="' . $row->caller_number . '" class="edit btn btn-success btn-sm" style="cursor: default;color:white;padding-left: 16px;padding-right: 16px;">Done by ' . $user->name . '</span>';
                        } else {
                            $btn = '<span value="' . $row->caller_number . '" class="edit btn btn-success btn-sm" style="cursor: default;color:white;padding-left: 16px;padding-right: 16px;">Done</span>';
                        }
                    }
                    return $btn;
                })
                ->addColumn('ring_details', function ($row) {
                    return '<button class="btn btn-info btn-sm" onclick="showRingDetails(`' . $row->callid . '`)">Details</button>';
                })
                ->editColumn('call_time', function ($row) {
                    return Carbon::parse($row->call_time)->format('Y-m-d h:i:s A');
                })
                ->rawColumns(['action', 'ring_details'])
                ->make(true);
        }

        $agent_names = User::pluck('name', 'id')->toArray();

        // Prepare and display the interface for non-AJAX requests
        $filter_area = view('reporting::widgets.filter_area', [
            'conds' => $conds,
            'submit_route' => route('reporting.missed')
        ]);

        return view('reporting::missed_calls', [
            'filter_area' => $filter_area,
            'conds' => $conds,
            'agent_names' => $agent_names,
        ]);
    }

    /**
     * Helper function to format seconds to HH:MM:SS
     */
    private function formatSeconds($seconds)
    {
        if (!$seconds) return '00:00:00';

        $hours = floor($seconds / 3600);
        $minutes = floor(($seconds % 3600) / 60);
        $secs = $seconds % 60;

        return sprintf('%02d:%02d:%02d', $hours, $minutes, $secs);
    }


    public function getRingDetails($callid)
    {
        // 1. Resolve the missed call from inbound_reports
        $missedCall = InboundReport::where('callid', $callid)
            ->whereIn('hangup_type', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->first();

        if (!$missedCall) {
            return response()->json([
                'ring_details'              => [],
                'inbound_calls'             => [],
                'outbound_calls'            => [],
                'ring_details_count'        => 0,
                'inbound_calls_count'       => 0,
                'outbound_calls_count'      => 0,
                'active_missed_calls'       => [],
                'active_missed_calls_count' => 0,
            ]);
        }

        $startTime = Carbon::parse($missedCall->call_time)->format('Y-m-d H:i:s');
        $endTime   = Carbon::parse($missedCall->call_time)->addSeconds($missedCall->ringtime ?? 0)->format('Y-m-d H:i:s');

        // 2. Per-agent ring attempt details for the main call (granular queuelog data —
        //    no equivalent stored in inbound_reports, so queuelog is still used here)
        $ringDetails = DB::table('asteriskcdrdb.queuelog')
            ->select('agent', 'event', 'data1', 'time')
            ->where('callid', $callid)
            ->whereIn('event', ['RINGNOANSWER', 'RINGCANCELED'])
            ->where('data1', '>', 1000)
            ->orderBy('time', 'asc')
            ->get();

        // 3. Concurrent missed calls — from inbound_reports
        $activeMissedCalls = InboundReport::whereIn('hangup_type', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->where('callid', '!=', $callid)
            ->where('call_time', '>=', $startTime)
            ->where('call_time', '<=', $endTime)
            ->orderBy('call_time', 'asc')
            ->get()
            ->map(fn($r) => [
                'agent'  => $r->agent,
                'event'  => $r->hangup_type,
                'data1'  => ($r->ringtime ?? 0) * 1000, // match ms format expected by frontend
                'time'   => Carbon::parse($r->call_time)->format('Y-m-d H:i:s'),
            ]);

        // 4. Concurrent answered inbound calls — from inbound_reports
        $inboundCalls = InboundReport::whereIn('hangup_type', ['COMPLETECALLER', 'COMPLETEAGENT'])
            ->where('call_time', '<=', $endTime)
            ->whereRaw('DATE_ADD(call_time, INTERVAL (ringtime + talktime) SECOND) >= ?', [$startTime])
            ->orderBy('call_time', 'asc')
            ->get()
            ->map(fn($r) => [
                'enter_time'     => Carbon::parse($r->call_time)->format('Y-m-d H:i:s'),
                'agent'          => $r->agent,
                'src'            => $r->caller_number,
                'did'            => $r->did,
                'total_duration' => ($r->ringtime ?? 0) + ($r->talktime ?? 0),
            ]);

        // 5. Concurrent outbound calls — from outbound_reports
        $outboundCalls = OutboundReport::where('call_time', '<=', $endTime)
            ->whereRaw('DATE_ADD(call_time, INTERVAL duration SECOND) >= ?', [$startTime])
            ->whereIn('disposition', ['ANSWERED', 'BUSY', 'NO ANSWER', 'FAILED'])
            ->orderBy('call_time', 'asc')
            ->get()
            ->map(fn($r) => [
                'calldate'    => Carbon::parse($r->call_time)->format('Y-m-d H:i:s'),
                'agent_name'  => $r->agent_name,
                'dst'         => $r->called_number,
                'src'         => $r->src,
                'duration'    => $r->duration,
                'disposition' => $r->disposition,
            ]);

        return response()->json([
            'ring_details'              => $ringDetails,
            'inbound_calls'             => $inboundCalls,
            'outbound_calls'            => $outboundCalls,
            'ring_details_count'        => count($ringDetails),
            'inbound_calls_count'       => count($inboundCalls),
            'outbound_calls_count'      => count($outboundCalls),
            'active_missed_calls'       => $activeMissedCalls,
            'active_missed_calls_count' => count($activeMissedCalls),
        ]);
    }


    function missedDone($num)
    {
        try {
            $userId = Auth::id();

            /*
         * Normalize the input.
         *
         * Examples:
         * +201002601172
         * 201002601172
         * 00201002601172
         * 01002601172
         * +20 100 260 1172
         * 010-026-01172
         */
            $originalNum = $num;

            // Remove spaces, -, (, ), etc. but keep +
            $num = preg_replace('/[^0-9+]/', '', $num);

            $numbers = [];

            /*
         * Add original format.
         */
            if (!empty($num)) {
                $numbers[] = $num;
            }

            /*
         * Remove + for processing.
         *
         * +201002601172
         *      ↓
         * 201002601172
         */
            $cleanNum = ltrim($num, '+');

            /*
         * ==========================================================
         * CASE 1:
         * 0020XXXXXXXXXX
         *
         * Example:
         * 00201002601172
         *
         * Generate:
         * 00201002601172
         * 201002601172
         * +201002601172
         * 01002601172
         * ==========================================================
         */
            if (str_starts_with($cleanNum, '0020')) {

                $international = substr($cleanNum, 2);

                $numbers[] = $international;
                $numbers[] = '+' . $international;

                // 201002601172 -> 01002601172
                $numbers[] = '0' . substr($international, 2);
            }

            /*
         * ==========================================================
         * CASE 2:
         * +20XXXXXXXXXX
         * 20XXXXXXXXXX
         *
         * Example:
         * +201002601172
         * 201002601172
         *
         * Generate:
         * +201002601172
         * 201002601172
         * 00201002601172
         * 01002601172
         * ==========================================================
         */ elseif (str_starts_with($cleanNum, '20')) {

                $international = $cleanNum;

                $numbers[] = $international;
                $numbers[] = '+' . $international;
                $numbers[] = '00' . $international;

                // 201002601172 -> 01002601172
                $numbers[] = '0' . substr($international, 2);
            }

            /*
         * ==========================================================
         * CASE 3:
         * Egyptian local number
         *
         * Example:
         * 01002601172
         *
         * Generate:
         * 01002601172
         * 201002601172
         * +201002601172
         * 00201002601172
         * ==========================================================
         */ elseif (preg_match('/^01[0-9]{9}$/', $cleanNum)) {

                $international = '20' . substr($cleanNum, 1);

                $numbers[] = $cleanNum;
                $numbers[] = $international;
                $numbers[] = '+' . $international;
                $numbers[] = '00' . $international;
            }

            /*
         * Remove duplicate values and empty values.
         */
            $numbers = array_values(
                array_unique(
                    array_filter($numbers)
                )
            );

            /*
         * Log the generated formats.
         */
            Log::info('missedDone phone formats', [
                'original' => $originalNum,
                'clean' => $cleanNum,
                'numbers' => $numbers,
                'userId' => $userId,
            ]);

            /*
         * ==========================================================
         * QUEUELOG
         * ==========================================================
         */
            $placeholders = implode(
                ',',
                array_fill(0, count($numbers), '?')
            );

            $sql1 = "UPDATE asteriskcdrdb.queuelog
            SET data5 = ?
            WHERE event = 'ENTERQUEUE'
            AND data2 IN ($placeholders)
            AND (data5 IS NULL OR data5 = '')";

            $bindings1 = array_merge(
                [$userId],
                $numbers
            );

            Log::info('missedDone queuelog SQL', [
                'sql' => $sql1,
                'bindings' => $bindings1,
            ]);

            DB::update($sql1, $bindings1);

            /*
         * ==========================================================
         * INBOUND REPORTS
         * ==========================================================
         */

            $sql2 = "UPDATE inbound_reports
            SET data5 = ?
            WHERE event IN ('ABANDON', 'EXITWITHTIMEOUT')
            AND caller_number IN ($placeholders)
            AND (data5 IS NULL OR data5 = '')";

            $bindings2 = array_merge(
                [$userId],
                $numbers
            );

            Log::info('missedDone inbound_reports SQL', [
                'sql' => $sql2,
                'bindings' => $bindings2,
            ]);

            DB::update($sql2, $bindings2);

            return true;
        } catch (\Throwable $th) {
            throw $th;
        }
    }

    public function callStats(Request $request)
    {
        $user = Auth::user();
        $company_groups = collect($user->accessibleQueueIds());

        // Default time range
        $conds = $this->SettingsHelper->defaultConds();

        if ($request->has('daterange')) {
            try {
                $range = explode(' - ', $request->daterange);
                $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');
            }
        }

        // Base query against inbound_reports — one row per missed call, already filtered
        $baseQuery = InboundReport::whereBetween('call_time', [$conds['from'], $conds['to']])
            ->whereIn('hangup_type', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->whereIn('enter_queuename', $company_groups->toArray());

        if ($user->access_to_all_entity != '1' && $user->groups->isNotEmpty()) {
            $baseQuery->whereIn('enter_queuename', $user->accessibleQueueIds());
        }

        // Status counts (ABANDON vs EXITWITHTIMEOUT)
        $statusCounts = (clone $baseQuery)
            ->selectRaw('hangup_type as status, COUNT(*) as count')
            ->groupBy('hangup_type')
            ->orderByRaw('COUNT(*) DESC')
            ->get();

        // Reason counts (the call_status / reason field per missed call)
        $reasonCounts = (clone $baseQuery)
            ->selectRaw('COALESCE(NULLIF(status, \'\'), \'Unknown\') as status, COUNT(*) as count')
            ->groupBy('status')
            ->orderByRaw('COUNT(*) DESC')
            ->get();

        $statusData = [
            'labels' => $statusCounts->pluck('status')->toArray(),
            'counts' => $statusCounts->pluck('count')->toArray()
        ];

        $reasonData = [
            'labels' => $reasonCounts->map(function ($item) {
                return $item->status ?? 'Unknown';
            })->toArray(),
            'counts' => $reasonCounts->pluck('count')->toArray()
        ];

        return response()->json([
            'status_data' => $statusData,
            'reason_data' => $reasonData
        ]);
    }
}