<?php

namespace Modules\Reporting\Services;

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 AnsweredCallsService
{
    /**
     * Generate answered calls report
     */
    public function generateDailyAnsweredCalls($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 answered calls report for date {$date}");
            }
            $startTime = microtime(true);

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

            $count = $this->processAnsweredCalls($answeredCalls, $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} answered calls for date {$date}");
            }
            return $count;

        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::error("Error generating answered calls report: " . $e->getMessage());
            }
            return 0;
        }
    }
    
    private function deleteExistingAnsweredCalls($date)
    {
        return InboundReport::where('report_date', $date)
            ->whereIn('event', ['COMPLETECALLER', 'COMPLETEAGENT'])
            ->delete();
    }
    
    private function fetchAnsweredCalls($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: linkedids whose completion fell on this day ──────────────
        // First collect the callids of COMPLETE events, then map them to linkedids
        // via CDR so that the linkedid (constant across all transfer legs) is used
        // as the canonical call identifier from this point forward.
        $emit('Step 1/6 — Fetching completed linkedIDs...');
        $completingCallids = DB::table('asteriskcdrdb.queuelog')
            ->whereIn('event', ['COMPLETECALLER', 'COMPLETEAGENT'])
            ->whereBetween('time', [$startOfDay, $endOfDay])
            ->pluck('callid')
            ->unique()
            ->values()
            ->all();

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

        $linkedids = DB::table('asteriskcdrdb.cdr')
            ->whereIn('uniqueid', $completingCallids)
            ->where('disposition', 'ANSWERED')
            ->pluck('linkedid')
            ->unique()
            ->values()
            ->all();

        if (empty($linkedids)) {
            $emit('No ANSWERED calls found for this date.');
            return [];
        }
        $emit('Step 1/6 — Found ' . count($linkedids) . ' linkedIDs');

        // ── Step 2: All ANSWERED CDR rows for those linkedids ────────────────
        // Loads every leg of each call (original + all transfer legs).
        // Root leg (uniqueid = linkedid) carries the original src/did.
        $emit('Step 2/6 — Fetching CDR records...');
        $allCdrRows = DB::table('asteriskcdrdb.cdr')
            ->select(['uniqueid', 'linkedid', 'did', 'src', 'dstchannel', 'disposition'])
            ->whereIn('linkedid', $linkedids)
            ->where('disposition', 'ANSWERED')
            ->get();

        // Root leg carries the original caller's src/did
        $rootCdrByLinkedid = $allCdrRows
            ->filter(fn($r) => $r->uniqueid === $r->linkedid)
            ->keyBy('linkedid');

        // Fallback when the root leg itself is not ANSWERED
        $firstCdrByLinkedid = $allCdrRows
            ->groupBy('linkedid')
            ->map(fn($rows) => $rows->first());

        // Indexed by callid for completing-segment dstchannel lookup
        $cdrByCallid = $allCdrRows->keyBy('uniqueid');

        $emit('Step 2/6 — ' . $allCdrRows->count() . ' CDR rows across ' . count($linkedids) . ' linkedIDs');

        // Build callid → linkedid map from already-fetched CDR rows.
        // All subsequent queuelog queries use whereIn(callid) — no JOIN needed,
        // since queuelog.callid is indexed, whereas filtering by linkedid requires a JOIN.
        $callidToLinkedid = $allCdrRows->pluck('linkedid', 'uniqueid')->all();
        $answeredCallids  = array_keys($callidToLinkedid);

        // ── Step 3: Last COMPLETE event per linkedid ─────────────────────────
        $emit('Step 3/6 — Fetching queue completion events...');
        $completeEvents = DB::table('asteriskcdrdb.queuelog')
            ->select(['callid', 'agent', 'event', 'data2', 'data5'])
            ->whereIn('callid', $answeredCallids)
            ->whereIn('event', ['COMPLETECALLER', 'COMPLETEAGENT'])
            ->orderBy('time', 'desc')
            ->orderBy('id', 'desc')
            ->get()
            ->groupBy(fn($row) => $callidToLinkedid[$row->callid])
            ->map(fn($rows) => $rows->first()); // last COMPLETE event per linkedid

        // ── Step 4: First ENTERQUEUE event per linkedid ──────────────────────
        $emit('Step 4/6 — Fetching enter-queue events...');
        $enterEvents = DB::table('asteriskcdrdb.queuelog')
            ->select(['callid', 'time', 'queuename'])
            ->whereIn('callid', $answeredCallids)
            ->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

        // Queue descriptions keyed by queuename
        $queueNames = $enterEvents->pluck('queuename')->unique()->filter()->values()->all();
        $queueDescriptions = DB::table('asterisk.queues_config')
            ->select(['extension', 'descr'])
            ->whereIn('extension', $queueNames)
            ->get()
            ->keyBy('extension');

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

        $ringtimesArr = [];
        foreach ($ringtimeRows as $row) {
            $lid = $callidToLinkedid[$row->callid] ?? null;
            if ($lid) {
                $ringtimesArr[$lid] = ($ringtimesArr[$lid] ?? 0) + (int)($row->ringtime ?? 0);
            }
        }

        // ── Step 6: Transfer counts per linkedid ─────────────────────────────
        // A transfer is either:
        //   (a) a Dial row that has a prior Dial on the same linkedid (agent-to-agent / direct)
        //   (b) a Queue row whose uniqueid differs from the linkedid (queue-to-queue transfer)
        $emit('Step 6/6 — Fetching transfer counts...');
        $transfers = DB::table('asteriskcdrdb.cdr as transfer_cdr')
            ->select(['transfer_cdr.linkedid', DB::raw('COUNT(*) AS transfers_count')])
            ->whereIn('transfer_cdr.linkedid', $linkedids)
            ->whereBetween('transfer_cdr.calldate', [$windowStart, $windowEnd])
            ->where(function ($q) {
                // (a) Direct extension transfer: Dial after another Dial,
                //     but NOT already covered by a queue-to-queue transfer row
                //     at or before the same timestamp (mirrors getTransfers $coveredByQueue).
                $q->where(function ($q2) {
                    $q2->where('transfer_cdr.lastapp', 'Dial')
                        ->whereExists(function ($q3) {
                            $q3->select(DB::raw(1))
                                ->from('asteriskcdrdb.cdr as c2')
                                ->whereColumn('c2.linkedid', 'transfer_cdr.linkedid')
                                ->where('c2.lastapp', 'Dial')
                                ->whereColumn('c2.calldate', '<', 'transfer_cdr.calldate');
                        })
                        ->whereNotExists(function ($q4) {
                            $q4->select(DB::raw(1))
                                ->from('asteriskcdrdb.cdr as c3')
                                ->whereColumn('c3.linkedid', 'transfer_cdr.linkedid')
                                ->where('c3.lastapp', 'Queue')
                                ->whereColumn('c3.uniqueid', '!=', 'c3.linkedid')
                                ->whereColumn('c3.calldate', '<=', 'transfer_cdr.calldate');
                        });
                })
                // (b) Queue row that belongs to a different channel (queue-to-queue)
                ->orWhere(function ($q2) {
                    $q2->where('transfer_cdr.lastapp', 'Queue')
                        ->whereColumn('transfer_cdr.uniqueid', '!=', 'transfer_cdr.linkedid');
                });
            })
            ->groupBy('transfer_cdr.linkedid')
            ->get()
            ->keyBy('linkedid');

        // ── Step 7: Surveys per linkedid ─────────────────────────────────────
        $surveys = DB::table('surveys')
            ->select(['linkedid', 'customer_choice'])
            ->whereIn('linkedid', $linkedids)
            ->get()
            ->keyBy('linkedid');

        // ── Merge everything in PHP ───────────────────────────────────────────
        $emit('Merging results...');
        $results = [];
        foreach ($linkedids as $linkedid) {
            $complete = $completeEvents[$linkedid] ?? null;
            $enter    = $enterEvents[$linkedid]    ?? null;

            // Root CDR leg (uniqueid = linkedid) carries src/did;
            // fall back to any ANSWERED leg for this linkedid.
            $rootCdr = $rootCdrByLinkedid[$linkedid] ?? $firstCdrByLinkedid[$linkedid] ?? null;

            if (!$rootCdr || !$complete || !$enter) {
                continue;
            }

            // dstchannel from the completing segment's CDR row (for user_id extraction)
            $completingCdr = $cdrByCallid[$complete->callid] ?? $rootCdr;

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

            $results[] = (object) [
                'callid'            => $complete->callid,
                'linkedid'          => $linkedid,
                'agent'             => $complete->agent,
                'event'             => $complete->event,
                'data2'             => $complete->data2,
                'data5'             => $complete->data5,
                'dstchannel'        => $completingCdr->dstchannel,
                'src'               => $rootCdr->src,
                'did'               => $rootCdr->did,
                'disposition'       => $rootCdr->disposition,
                'call_time'         => $enter->time,
                'enter_queuename'   => $queuename,
                'queue_description' => $queueDescr,
                'ringtime'          => $ringtimesArr[$linkedid] ?? 0,
                'transfers_count'   => $transfers[$linkedid]->transfers_count ?? 0,
                'survey'            => $surveys[$linkedid]->customer_choice ?? null,
            ];
        }

        $emit('Merge complete — ' . count($results) . ' records ready');
        return $results;
    }
    
    private function processAnsweredCalls($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'             => $item->agent,
                'hangup_type'       => $item->event,
                'ringtime'          => $item->ringtime ?? 0,
                'talktime'          => $item->data2 ?? 0,
                'caller_number'     => $this->normalizePhoneNumber($item->src),
                'did'               => $item->did,
                'transfers_count'   => $item->transfers_count ?? 0,
                'survey'            => $item->survey,
                'user_id'           => $this->extractUserIdFromAgent($item->dstchannel),
                'enter_queuename'   => $item->enter_queuename,
                'event'             => $item->event,
                'data5'             => $item->data5,
                'call_status'       => 'answered',
                'report_date'       => $date,
                'created_at'        => $now,
                'updated_at'        => $now,
            ];
        }

        $emit('Deleting existing records...');
        $this->deleteExistingAnsweredCalls($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 answered call.
     *
     * Uses the same optimised 4-query pipeline as ProcessInboundCallJob:
     *   Query 1 — all CDR legs (determines transfers + root src/did)
     *   Query 2 — queue events (ENTER / COMPLETE / RING)
     *   Query 3 — queue description (PK lookup)
     *   Query 4 — survey
     */
    public function processSingleAnsweredCall(string $linkedid): InboundReport|false
    {
        try {
            // ── Query 1: All CDR legs ─────────────────────────────────────
            $cdrRows = DB::table('asteriskcdrdb.cdr')
                ->select(['uniqueid', 'did', 'src', 'dstchannel', 'disposition', 'lastapp', 'calldate'])
                ->where('linkedid', $linkedid)
                ->orderBy('calldate', 'asc')
                ->get();

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

            // ── Single pass 1: CDR analysis ───────────────────────────────
            $rootAnsweredLeg    = null;
            $firstAnsweredLeg   = null;
            $answeredCallids    = [];
            $dstchannelByCallid = [];
            $transferCount      = 0;
            $seenDial           = false;
            $seenQueueTransfer  = false;

            foreach ($cdrRows as $leg) {
                if ($leg->disposition === 'ANSWERED') {
                    if ($leg->uniqueid === $linkedid) {
                        $rootAnsweredLeg = $leg;
                    }
                    if ($firstAnsweredLeg === null) {
                        $firstAnsweredLeg = $leg;
                    }
                    $answeredCallids[]                  = $leg->uniqueid;
                    $dstchannelByCallid[$leg->uniqueid] = $leg->dstchannel;
                }

                // (b) Queue-to-queue transfer.
                if ($leg->lastapp === 'Queue' && $leg->uniqueid !== $linkedid) {
                    $transferCount++;
                    $seenQueueTransfer = true;
                    continue;
                }

                // (a) Agent-to-agent transfer (Dial after prior Dial, uncovered).
                if ($leg->lastapp === 'Dial') {
                    if ($seenDial && ! $seenQueueTransfer) {
                        $transferCount++;
                    }
                    $seenDial = true;
                }
            }

            if (empty($answeredCallids)) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    Log::info("[AnsweredCallsService] No ANSWERED CDR legs for {$linkedid}");
                }
                return false;
            }

            $rootLeg = $rootAnsweredLeg ?? $firstAnsweredLeg;

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

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

            // ── Single pass 2: Queue-event analysis ───────────────────────
            $firstEnter    = null;
            $firstComplete = null;
            $lastComplete  = null;
            $ringtime      = 0;
            $talktime      = 0;

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

                    case 'COMPLETECALLER':
                    case 'COMPLETEAGENT':
                        if ($firstComplete === null && $row->callid === $firstEnter->callid) {
                            $firstComplete = $row;
                            $ringtime = (int) ($row->data1 ?? 0);
                            $talktime    += (int) ($row->data2 ?? 0);
                        }
                        $lastComplete = $row;
                        break;

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

            if ($lastComplete === null || $firstEnter === null || $firstComplete === null) {
                if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                    Log::info("[AnsweredCallsService] Missing COMPLETE or ENTERQUEUE for {$linkedid}");
                }
                return false;
            }

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

            // ── Query 4: Survey ───────────────────────────────────────────
            $survey = DB::table('surveys')
                ->where('linkedid', $linkedid)
                ->value('customer_choice');

            // ── Assemble ──────────────────────────────────────────────────
            $dstchannel = $dstchannelByCallid[$firstComplete->callid]
                ?? ($rootLeg->dstchannel ?? null);

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

            $userId = null;
            if ($dstchannel && preg_match('/\d+/', $dstchannel, $m)) {
                $userId = (int) $m[0];
            }

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

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

            $report = InboundReport::create([
                'linkedid'          => $linkedid,
                'callid'            => $lastComplete->callid,
                'call_time'         => $callTime,
                'queue_description' => $queueDescr,
                'agent'             => $firstComplete->agent,
                'hangup_type'       => $lastComplete->event,
                'ringtime'          => $ringtime,
                'talktime'          => $talktime,
                'caller_number'     => $callerNumber,
                'did'               => $rootLeg->did,
                'transfers_count'   => $transferCount,
                'survey'            => $survey,
                'user_id'           => $userId,
                'enter_queuename'   => $firstEnter->queuename,
                'event'             => $lastComplete->event,
                'data5'             => $lastComplete->data5,
                'call_status'       => 'answered',
                'report_date'       => $reportDate,
            ]);

            CallLogSyncService::sync($linkedid, 'inbound', [
                'phone_number' => $callerNumber,
                'call_date'    => $callTime,
                'duration'     => $ringtime + $talktime,
                'talktime'     => $talktime,
                'ringtime'     => $ringtime,
                'user_id'      => $userId,
                'queue'        => $queueDescr,
            ]);

            return $report;

        } catch (\Exception $e) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::error("[AnsweredCallsService] Error processing single answered 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 answered calls batch: " . $e->getMessage());
            }
        }
    }
    
    private function extractUserIdFromAgent($agent)
    {
        if (!$agent) return null;
        if (preg_match('/\d+/', $agent, $matches)) {
            return $matches[0];
        }
        return null;
    }

    private function normalizePhoneNumber($number)
    {
        if (!$number) return $number;

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

        return $number;
    }
}