<?php

namespace Modules\Reporting\Services;

use App\Jobs\SendMissedCallSmsJob;
use Modules\CallLogs\Service\CallLogSyncService;
use Modules\Reporting\Entities\InboundReport;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Carbon\Carbon;
use Modules\Platform\Core\Helper\CompanySettings;
use Modules\Platform\Core\Helper\SettingsHelper;

class MissedCallsService
{
    /**
     * Generate missed calls report
     */
    public function generateDailyMissedCalls($date = null, callable $output = null)
    {
        $emit = $output ?? static fn() => null;

        try {
            $date = $date ?? Carbon::yesterday()->format('Y-m-d');
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::info("Starting missed calls report for date {$date}");
            }
            $startTime = microtime(true);

            $missedCalls = $this->fetchMissedCalls($date, $emit);
            $fetched = count($missedCalls);
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                \Log::info("Missed calls fetched: {$fetched}");
            }

            $count = $this->processMissedCalls($missedCalls, $date, $emit);

            $elapsed = round(microtime(true) - $startTime, 1);
            $emit("Done — {$count} records ({$elapsed}s)");

            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::info("Successfully generated {$count} missed calls for date {$date}");
            }
            return $count;

        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::error("Error generating missed calls report: " . $e->getMessage());
            }
            return 0;
        }
    }
    
    private function deleteExistingMissedCalls($date)
    {
        return InboundReport::where('report_date', $date)
            ->whereIn('event', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->delete();
    }
    
    private function fetchMissedCalls($date, callable $emit = null)
    {
        $emit ??= static fn() => null;

        $startOfDay  = Carbon::parse($date)->startOfDay()->format('Y-m-d H:i:s');
        $endOfDay    = Carbon::parse($date)->endOfDay()->format('Y-m-d H:i:s');
        $windowStart = Carbon::parse($date)->subDay()->startOfDay()->format('Y-m-d H:i:s');
        $windowEnd   = Carbon::parse($date)->addDay()->endOfDay()->format('Y-m-d H:i:s');

        // ── Step 1: Find truly missed linkedids ────────────────────────────────────
        // Replaced correlated NOT EXISTS (re-scans queuelog per row) with 4 fast
        // index-only queries + PHP array_diff.
        $emit('Step 1/6 — Fetching missed linkedids...');

        // 1a: callids that had ABANDON/EXITWITHTIMEOUT on the target date (uses time index)
        $abandonedCallids = DB::table('asteriskcdrdb.queuelog')
            ->whereIn('event', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->whereBetween('time', [$startOfDay, $endOfDay])
            ->distinct()
            ->pluck('callid')
            ->all();

        if (empty($abandonedCallids)) {
            $emit('No missed calls found for this date.');
            return [];
        }

        // 1b: Map those callids → linkedids via CDR (uniqueid is PK — fast)
        $callid2lid = DB::table('asteriskcdrdb.cdr')
            ->whereIn('uniqueid', $abandonedCallids)
            ->pluck('linkedid', 'uniqueid')
            ->all();

        $potentialLinkedids = array_values(array_unique($callid2lid));

        if (empty($potentialLinkedids)) {
            $emit('No CDR records found for abandoned calls.');
            return [];
        }

        // 1c: All callids sharing those linkedids (covers transfer legs)
        //     Also builds the callid→linkedid map reused in Steps 3-5.
        $callidToLinkedid = DB::table('asteriskcdrdb.cdr')
            ->whereIn('linkedid', $potentialLinkedids)
            ->pluck('linkedid', 'uniqueid')  // [callid => linkedid]
            ->all();

        $allCallidsFull = array_keys($callidToLinkedid);

        // 1d: Which of those callids have a COMPLETE event? (callid indexed in queuelog)
        $completedCallids = !empty($allCallidsFull)
            ? DB::table('asteriskcdrdb.queuelog')
                ->whereIn('callid', $allCallidsFull)
                ->whereIn('event', ['COMPLETECALLER', 'COMPLETEAGENT'])
                ->distinct()
                ->pluck('callid')
                ->all()
            : [];

        // 1e: Map completed callids → linkedids, then subtract from potential set
        if (!empty($completedCallids)) {
            $completedLinkedids = array_values(array_unique(
                array_intersect_key($callidToLinkedid, array_flip($completedCallids))
            ));
            $missedLinkedids = array_values(array_diff($potentialLinkedids, $completedLinkedids));
        } else {
            $missedLinkedids = $potentialLinkedids;
        }

        if (empty($missedLinkedids)) {
            $emit('No missed calls found for this date (all were eventually answered).');
            return [];
        }
        $emit('Step 1/6 — Found ' . count($missedLinkedids) . ' missed linkedids');

        // ── Step 2: CDR data + refined callid→linkedid map for missed calls only ─
        // Single query; also rebuilds the map scoped to $missedLinkedids so that
        // answered-but-abandoned legs are excluded from Steps 3-5.
        $emit('Step 2/6 — Fetching CDR records...');
        $allCdr = DB::table('asteriskcdrdb.cdr')
            ->select(['uniqueid', 'linkedid', 'src', 'did', 'dstchannel', 'disposition'])
            ->whereIn('linkedid', $missedLinkedids)
            ->get();

        // Rebuild the callid→linkedid map scoped to only the missed linkedids
        $callidToLinkedid = $allCdr->pluck('linkedid', 'uniqueid')->all();
        $allCallids       = array_keys($callidToLinkedid);

        // Root CDR leg (uniqueid = linkedid) carries original src/did
        $cdrRows = $allCdr
            ->filter(fn($r) => $r->uniqueid === $r->linkedid)
            ->keyBy('linkedid');

        // Fallback: if root CDR is missing, use any CDR row for that linkedid
        $missing = array_diff($missedLinkedids, $cdrRows->keys()->all());
        if (!empty($missing)) {
            $fallback = $allCdr
                ->filter(fn($r) => in_array($r->linkedid, $missing))
                ->groupBy('linkedid')
                ->map(fn($rows) => $rows->first());
            $cdrRows = $cdrRows->merge($fallback);
        }

        if ($cdrRows->isEmpty()) {
            $emit('No CDR records found for missed calls.');
            return [];
        }
        $emit('Step 2/6 — ' . count($missedLinkedids) . ' calls, ' . count($allCallids) . ' CDR legs');

        // ── Step 3: First ENTERQUEUE event per linkedid ───────────────────────────
        // whereIn(callid) — no JOIN; queuelog.callid is always indexed.
        $emit('Step 3/6 — Fetching enter-queue events...');
        $enterEvents = DB::table('asteriskcdrdb.queuelog')
            ->select(['callid', 'time', 'queuename','data5'])
            ->whereIn('callid', $allCallids)
            ->where('event', 'ENTERQUEUE')
            ->whereBetween('time', [$windowStart, $windowEnd])
            ->orderBy('time', 'asc')
            ->orderBy('id', 'asc')
            ->get()
            ->groupBy(fn($row) => $callidToLinkedid[$row->callid])
            ->map(fn($rows) => $rows->first()); // first ENTERQUEUE per linkedid

        $queueNames = $enterEvents->pluck('queuename')->unique()->filter()->values()->all();
        $queueDescriptions = DB::table('asterisk.queues_config')
            ->select(['extension', 'descr'])
            ->whereIn('extension', $queueNames)
            ->get()
            ->keyBy('extension');
        $emit('Step 3/6 — ' . $enterEvents->count() . ' enter-queue events retrieved');

        // ── Step 4: Last ABANDON/EXITWITHTIMEOUT event per linkedid ──────────────
        $emit('Step 4/6 — Fetching final abandon/exit events...');
        $finalEvents = DB::table('asteriskcdrdb.queuelog')
            ->select(['callid', 'event', 'status'])
            ->whereIn('callid', $allCallids)
            ->whereIn('event', ['ABANDON', 'EXITWITHTIMEOUT'])
            ->orderBy('id', 'desc')
            ->get()
            ->groupBy(fn($row) => $callidToLinkedid[$row->callid])
            ->map(fn($rows) => $rows->first()); // last event per linkedid
        $emit('Step 4/6 — Final events retrieved');

        // ── Step 5: Ringtime per callid (no JOIN) then aggregate per linkedid in PHP
        $emit('Step 5/6 — Fetching ringtimes...');
        $ringtimeRows = DB::table('asteriskcdrdb.queuelog')
            ->select([
                'callid',
                DB::raw("SUM(CASE
                    WHEN event IN ('ABANDON','EXITWITHTIMEOUT','RINGNOANSWER') THEN CAST(data3 AS UNSIGNED)
                    ELSE 0
                END) AS ringtime"),
            ])
            ->whereIn('callid', $allCallids)
            ->whereIn('event', ['ABANDON', 'EXITWITHTIMEOUT', 'RINGNOANSWER'])
            ->whereBetween('time', [$windowStart, $windowEnd])
            ->groupBy('callid')
            ->get();

        $ringtimes = [];
        foreach ($ringtimeRows as $row) {
            $lid = $callidToLinkedid[$row->callid] ?? null;
            if ($lid) {
                $ringtimes[$lid] = ($ringtimes[$lid] ?? 0) + (int)($row->ringtime ?? 0);
            }
        }
        $emit('Step 5/6 — Ringtimes retrieved');

        // ── Step 6: Merge everything in PHP ────────────────────────────────────────
        $emit('Merging results...');
        $results = [];
        foreach ($missedLinkedids as $linkedid) {
            $cdr        = $cdrRows[$linkedid]     ?? null;
            $enter      = $enterEvents[$linkedid] ?? null;
            $finalEvent = $finalEvents[$linkedid] ?? null;

            if (!$cdr || !$enter || !$finalEvent) {
                continue; // skip incomplete records
            }

            $queuename  = $enter->queuename ?? null;
            $queueDescr = $queueDescriptions[$queuename]->descr ?? null;

            $results[] = (object) [
                'callid'            => $linkedid,   // linkedid is the canonical call identifier
                'linkedid'          => $linkedid,
                'src'               => $cdr->src,
                'did'               => $cdr->did,
                'dstchannel'        => $cdr->dstchannel ?? null,
                'call_time'         => $enter->time,
                'enter_queuename'   => $queuename,
                'queue_description' => $queueDescr,
                'event'             => $finalEvent->event,
                'ringtime'          => $ringtimes[$linkedid] ?? 0,
                'data5'             => $enter->data5 ?? null,
                'status'            => $finalEvent->status ?? null,
            ];
        }

        $emit('Merge complete — ' . count($results) . ' records ready');
        return $results;
    }
    
    private function processMissedCalls($data, $date, callable $output = null)
    {
        $emit = $output ?? static fn() => null;
        $now = now();
        $reports = [];

        foreach ($data as $item) {
            $reports[] = [
                'linkedid'          => $item->linkedid,
                'callid'            => $item->callid,
                'call_time'         => $item->call_time,
                'queue_description' => $item->queue_description,
                'agent'             => 'NONE',
                'hangup_type'       => $item->event,
                'ringtime'          => $item->ringtime ?? 0,
                'talktime'          => 0,
                'caller_number'     => $this->normalizePhoneNumber($item->src),
                'did'               => $item->did,
                'transfers_count'   => 0,
                'survey'            => null,
                'user_id'           => null,
                'enter_queuename'   => $item->enter_queuename,
                'event'             => $item->event,
                'data5'             => $item->data5 ?? null,
                'status'            => $item->status ?? null,
                'call_status'       => 'missed',
                'report_date'       => $date,
                'created_at'        => $now,
                'updated_at'        => $now,
            ];
        }

        $emit('Deleting existing records...');
        $this->deleteExistingMissedCalls($date);

        $batches = array_chunk($reports, 1000);
        $totalBatches = count($batches);
        foreach ($batches as $i => $batch) {
            $emit(sprintf('Inserting batch %d/%d (%s records)...', $i + 1, $totalBatches, number_format(count($batch))));
            $this->insertBatch($batch);
        }

        return count($reports);
    }
    
    /**
     * Process single missed call.
     *
     * Uses the same optimised 3-query pipeline as ProcessInboundCallJob:
     *   Query 1 — all CDR legs (determines callids, src/did)
     *   Query 2 — queue events (ENTER / ABANDON / RINGNOANSWER + guard for COMPLETE)
     *   Query 3 — queue description (PK lookup)
     */
    public function processSingleMissedCall(string $linkedid): InboundReport|false
    {
        try {
            $linkedid = trim($linkedid);

            // ── Query 1: All CDR legs ─────────────────────────────────────
            $cdrRows = DB::table('asteriskcdrdb.cdr')
                ->select(['uniqueid', 'did', 'src', 'disposition'])
                ->where('linkedid', $linkedid)
                ->orderBy('calldate', 'asc')
                ->get();

            if ($cdrRows->isEmpty()) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    Log::warning("[MissedCallsService] No CDR rows for {$linkedid}");
                }
                return false;
            }

            $allCallids = $cdrRows->pluck('uniqueid')->all();
            $rootCdr    = $cdrRows->firstWhere('uniqueid', $linkedid) ?? $cdrRows->first();

            // ── Query 2: Queue events ─────────────────────────────────────
            $queueRows = DB::table('asteriskcdrdb.queuelog')
                ->select(['callid', 'event', 'time', 'queuename', 'status', 'agent', 'data3', 'data5'])
                ->whereIn('callid', $allCallids)
                ->whereIn('event', [
                    'ENTERQUEUE',
                    'COMPLETECALLER', 'COMPLETEAGENT',
                    'ABANDON', 'EXITWITHTIMEOUT', 'RINGNOANSWER',
                ])
                ->orderBy('time', 'asc')
                ->orderBy('id', 'asc')
                ->get();

            if ($queueRows->isEmpty()) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    Log::info("[MissedCallsService] No queue events for {$linkedid}");
                }
                return false;
            }

            // ── Single pass: event analysis ────────────────────────────────
            $firstEnter  = null;
            $lastFinal   = null;
            $ringtime    = 0;
            $hasComplete = false;

            foreach ($queueRows as $row) {
                switch ($row->event) {
                    case 'ENTERQUEUE':
                        if ($firstEnter === null) {
                            $firstEnter = $row;
                        }
                        break;

                    case 'COMPLETECALLER':
                    case 'COMPLETEAGENT':
                        $hasComplete = true;
                        break;

                    case 'ABANDON':
                    case 'EXITWITHTIMEOUT':
                        $lastFinal = $row;    // last one wins
                        $ringtime += (int) ($row->data3 ?? 0);
                        break;

                    case 'RINGNOANSWER':
                        $ringtime += (int) ($row->data3 ?? 0);
                        break;
                }
            }

            // Guard: if any leg completed, this is an answered call — skip
            if ($hasComplete) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    Log::info("[MissedCallsService] Call {$linkedid} has a COMPLETE event — not a missed call.");
                }
                return false;
            }

            if ($firstEnter === null || $lastFinal === null) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    Log::info("[MissedCallsService] Missing ENTERQUEUE or final event for {$linkedid}");
                }
                return false;
            }

            // ── Query 3: Queue description ────────────────────────────────
            $queueDescr = DB::table('asterisk.queues_config')
                ->where('extension', $firstEnter->queuename)
                ->value('descr');

            // ── Write ─────────────────────────────────────────────────────
            $callTime   = $firstEnter->time;
            $reportDate = Carbon::parse($callTime)->format('Y-m-d');

            InboundReport::where('linkedid', $linkedid)->delete();

            $report = InboundReport::create([
                'linkedid'          => $linkedid,
                'callid'            => $linkedid,
                'call_time'         => $callTime,
                'queue_description' => $queueDescr,
                'agent'             => $lastFinal->agent ?? 'NONE',
                'hangup_type'       => $lastFinal->event,
                'status'            => $lastFinal->status ?? '',
                'ringtime'          => $ringtime,
                'talktime'          => 0,
                'caller_number'     => $this->normalizePhoneNumber($rootCdr->src),
                'did'               => $rootCdr->did,
                'transfers_count'   => 0,
                'survey'            => null,
                'user_id'           => null,
                'enter_queuename'   => $firstEnter->queuename,
                'event'             => $lastFinal->event,
                'data5'             => $firstEnter->data5 ?? null,
                'call_status'       => 'missed',
                'report_date'       => $reportDate,
            ]);

            $callerNumber = $rootCdr->src;
            if ($callerNumber !== null && str_starts_with($callerNumber, '+2')) {
                $callerNumber = substr($callerNumber, 2);
            }

            CallLogSyncService::sync($linkedid, 'missed', [
                'phone_number' => $callerNumber,
                'call_date'    => $callTime,
                'duration'     => $ringtime,
                'talktime'     => 0,
                'ringtime'     => $ringtime,
                'queue'        => $queueDescr,
            ]);

            $smsMessage = CompanySettings::get(SettingsHelper::S_MISSED_CALLS_SMS_MESSAGE);
            $missedAt = Carbon::parse($callTime);
            if (
                $smsMessage !== null
                && trim((string) $smsMessage) !== ''
                && !empty($report->caller_number)
                && $missedAt->between(now()->startOfDay(), now(), true)
            ) {
                SendMissedCallSmsJob::dispatch($linkedid);
            }

            return $report;

        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::error("[MissedCallsService] Error processing single missed call [{$linkedid}]: " . $e->getMessage());
            }
            return false;
        }
    }

    private function insertBatch($reports)
    {
        try {
            DB::table('inbound_reports')->insert($reports);
        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::error("Failed to bulk insert missed calls batch: " . $e->getMessage());
            }
        }
    }
    
    private function normalizePhoneNumber($number)
    {
        if (!$number) return $number;

        if (str_starts_with($number, '+2')) {
            return substr($number, 2);
        }

        return $number;
    }
}