<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Carbon\Carbon;
use Modules\Reporting\Entities\InboundReport;
use Modules\Platform\Core\Helper\CompanySettings;
use Modules\Platform\Core\Helper\SettingsHelper;

class RestoreInboundReports extends Command
{
    protected $signature = 'reports:inbound:restore
                            {--date= : Single date (Y-m-d) to scan for missing inbound calls}
                            {--start-date= : Start date (Y-m-d) of a range}
                            {--end-date= : End date (Y-m-d) of a range}
                            {--dry-run : Preview what would be restored without inserting anything}';

    protected $description = 'Compare calls vs inbound_reports and restore inbound calls missing a report record (reconstructs from the calls table only)';

    private const REFERENCE_LIMIT = 500;

    private const MIN_TENANT_REFERENCE_ROWS = 25;

    // =========================================================================

    public function handle(): int
    {
        [$startDate, $endDate] = $this->resolveDateRange();

        if (!$startDate || !$endDate) {
            $this->error('Provide a date (--date=Y-m-d) or a range (--start-date= --end-date=).');
            return Command::FAILURE;
        }

        if ($startDate->gt($endDate)) {
            $this->error('Start date must be before or equal end date.');
            return Command::FAILURE;
        }

        $dryRun = (bool) $this->option('dry-run');
        if ($dryRun) {
            $this->warn('DRY-RUN MODE — nothing will be inserted.');
        }

        $start = $startDate->format('Y-m-d');
        $end   = $endDate->format('Y-m-d');

        $this->info("Comparing inbound calls in {$start} → {$end} against inbound_reports...");

        $candidates = $this->collectCandidates($start, $end);

        $this->line('');
        $count = count($candidates['calls']);
        $this->line("  Distinct inbound calls found (by linkedid): {$count}");
        $this->line("  Already present in inbound_reports (skipped): " . count($candidates['present']));
        $this->line("  Missing from inbound_reports (candidates): " . count($candidates['missing']));

        if (empty($candidates['missing'])) {
            $this->line('');
            $this->info('No missing inbound calls found — nothing to do.');
            return Command::SUCCESS;
        }

        if (!$dryRun) {
            $this->line('');
            $confirm = $this->confirm(
                "Restore " . count($candidates['missing']) . " missing inbound call report record(s)?",
                false
            );
            if (!$confirm) {
                $this->info('Aborted — no records were changed.');
                return Command::SUCCESS;
            }
        }

        $this->line('');
        $this->info($dryRun ? 'Previewing reconstruction (no inserts)...' : 'Reconstructing missing reports...');

        $result = $this->restore($candidates, $dryRun);

        $verb = $dryRun ? 'would be restored' : 'restored';
        $this->line('');
        $this->line("Summary — found: {$result['found']} | already present: {$result['already']} | missing: {$result['missing']}");
        $this->line("    {$result['restored']} record(s) {$verb}");
        $this->line("    {$result['skipped']} could not be reconstructed (no data in the calls table)");

        if (!$dryRun && CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
            Log::info('Inbound reports restore completed', [
                'date'     => $start === $end ? $start : "{$start} / {$end}",
                'found'    => $result['found'],
                'present'  => $result['already'],
                'missing'  => $result['missing'],
                'restored' => $result['restored'],
                'skipped'  => $result['skipped'],
            ]);
        }

        return Command::SUCCESS;
    }

    // =========================================================================
    //  Candidate collection
    // =========================================================================

    /**
     * Compare `calls` with `inbound_reports` for a date period and list the
     * inbound calls (by linkedid) that are missing a report record.
     *
     * The `calls` table is the source of truth for "a call happened": every
     * hangup event recorded by agihangupaction creates a `calls` row carrying
     * the canonical `linkedid`. Each inbound call is supposed to produce exactly
     * one row in `inbound_reports` (joined on `linkedid`). When real-time
     * processing fails or is missed, the call stays only in `calls`.
     */
    private function collectCandidates(string $startDate, string $endDate): array
    {
        $from = Carbon::parse($startDate)->startOfDay()->format('Y-m-d H:i:s');
        $to   = Carbon::parse($endDate)->endOfDay()->format('Y-m-d H:i:s');

        $incomingDirectionIds = DB::table('calls_dict_direction')
            ->where('name', 'Incoming')
            ->whereNull('deleted_at')
            ->pluck('id')
            ->all();

        if (empty($incomingDirectionIds)) {
            return ['calls' => [], 'present' => [], 'missing' => []];
        }

        // All inbound `calls` rows within the period (soft-deleted excluded),
        // de-duplicated by linkedid — one call may produce several `calls` rows
        // (one per hangup/transfer leg), the canonical identifier is the linkedid.
        $rows = DB::table('calls')
            ->select(['id', 'linkedid', 'call_date', 'phone_number', 'owned_by_id'])
            ->whereIn('direction_id', $incomingDirectionIds)
            ->whereNull('deleted_at')
            ->whereNotNull('linkedid')
            ->where('linkedid', '!=', '')
            ->whereBetween('call_date', [$from, $to])
            ->orderBy('call_date', 'asc')
            ->get();

        $callsByLinkedid = [];
        foreach ($rows as $row) {
            $callsByLinkedid[$row->linkedid] ??= $row;
        }

        $linkedids = array_keys($callsByLinkedid);

        // Calls already covered by an inbound report — skipped to avoid duplicates.
        $presentSet = [];
        if (!empty($linkedids)) {
            $presentSet = DB::table('inbound_reports')
                ->whereIn('linkedid', $linkedids)
                ->pluck('linkedid')
                ->unique()
                ->flip()
                ->all();
        }

        $calls = [];
        foreach ($callsByLinkedid as $linkedid => $row) {
            $calls[] = [
                'linkedid'      => $linkedid,
                'call_id'       => $row->id,
                'call_date'     => $row->call_date,
                'caller_number' => $row->phone_number,
                'user_id'       => $row->owned_by_id,
            ];
        }

        $missing = array_values(array_filter($calls, fn($c) => !isset($presentSet[$c['linkedid']])));

        return [
            'calls'   => $calls,
            'present' => array_keys($presentSet),
            'missing' => $missing,
        ];
    }

    // =========================================================================
    //  Restore / preview
    // =========================================================================

    /**
     * Restore the missing inbound report records.
     *
     * @param array $candidates Result of collectCandidates()
     * @return array{found:int, already:int, missing:int, restored:int, skipped:int, dry_run:bool}
     */
    private function restore(array $candidates, bool $dryRun): array
    {
        $found   = count($candidates['calls']);
        $already = count($candidates['present']);
        $missing = count($candidates['missing']);

        $restored = 0;
        $skipped  = 0;
        $skipDuplicates = 0;

        foreach ($candidates['missing'] as $entry) {
            $linkedid = $entry['linkedid'];

            $verb   = $dryRun ? 'would restore' : 'restored';
            $result = $dryRun
                ? $this->previewInboundCall($linkedid)
                : $this->restoreInboundCall($linkedid);

            if ($result === null) {
                $skipped++;
                $this->line("  [skip]     linkedid={$linkedid} call_time=" . ($entry['call_date'] ?? 'n/a') . " → no data in calls table");
                continue;
            }

            if (!isset($result['inserted'])) {
                // Dry-run preview only — the row was rebuilt but not inserted.
                $restored++;
                $this->line(sprintf(
                    "  [%s] linkedid=%s call_time=%s → status=%s hangup=%s agent=%s caller=%s",
                    $verb,
                    $linkedid,
                    $result['call_time'] ?? ($entry['call_date'] ?? 'n/a'),
                    $result['call_status'] ?? 'n/a',
                    $result['event'] ?? ($result['hangup_type'] ?? 'n/a'),
                    $result['agent'] ?? 'n/a',
                    $result['caller_number'] ?? 'n/a'
                ));
                continue;
            }

            // Real restore: a row was actually inserted (or skipped as a duplicate).
            if ($result['inserted'] === false) {
                $skipDuplicates++;
                $this->line("  [skip]     linkedid={$linkedid} call_time=" . ($entry['call_date'] ?? 'n/a') . " → report already exists (no insert)");
                continue;
            }

            $restored++;
            $this->line(sprintf(
                "  [%s] linkedid=%s call_time=%s → status=%s hangup=%s agent=%s caller=%s",
                $verb,
                $linkedid,
                $result['call_time'] ?? ($entry['call_date'] ?? 'n/a'),
                $result['call_status'] ?? 'n/a',
                $result['event'] ?? ($result['hangup_type'] ?? 'n/a'),
                $result['agent'] ?? 'n/a',
                $result['caller_number'] ?? 'n/a'
            ));
        }

        return [
            'found'       => $found,
            'already'     => $already,
            'missing'     => $missing,
            'restored'    => $restored,
            'skipped'     => $skipped,
            'skip_dupl'   => $skipDuplicates,
            'dry_run'     => $dryRun,
        ];
    }

    /**
     * Reconstruct and insert a single missing inbound call by linkedid.
     *
     * Builds the inbound_reports row directly from the `calls` table and
     * inserts it. Returns the row with an `inserted` flag; null when there is
     * nothing to restore.
     */
    private function restoreInboundCall(string $linkedid): ?array
    {
        $linkedid = trim($linkedid);

        if ($linkedid === '') {
            return null;
        }

        $row = $this->reconstructInboundReport($linkedid);

        if ($row === null) {
            return null;
        }

        // Final duplicate guard — double check before inserting.
        if (InboundReport::where('linkedid', $linkedid)->exists()) {
            if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
                Log::info("[RestoreInboundReports] Report already exists for {$linkedid} — skipping.");
            }
            return [...$row, 'inserted' => false];
        }

        InboundReport::create($row);

        if (CompanySettings::get(SettingsHelper::S_COMPANY_CONSOLE_DEBUG_LOGS)) {
            Log::info("[RestoreInboundReports] Restored inbound report for {$linkedid}", $row);
        }

        return [...$row, 'inserted' => true];
    }

    /**
     * Preview what restoreInboundCall() would produce by rebuilding the row
     * without inserting it.
     */
    private function previewInboundCall(string $linkedid): ?array
    {
        $linkedid = trim($linkedid);

        if ($linkedid === '') {
            return null;
        }

        return $this->reconstructInboundReport($linkedid);
    }

    // =========================================================================
    //  Reconstruction (from the calls table only)
    // =========================================================================

    /**
     * Build the inbound_reports row for a linkedid from the `calls` record.
     *
     * Field mapping (no CDR/queuelog is read anywhere):
     *   - linkedid / callid      ← calls.linkedid (calls carries no separate callid)
     *   - call_time              ← calls.call_date
     *   - ringtime / talktime    ← calls.ringtime / calls.talktime
     *   - caller_number          ← calls.phone_number (kept as stored, e.g. +20221282799)
     *   - company_id             ← calls.company_id (the tenant of the call)
     *   - user_id / agent        ← calls.owned_by_id (+ users.name lookup)
     *   - survey                 ← calls.survey_choice
     *   - call_status            ← calls.status_id (answered vs missed)
     *   - queue_description / enter_queuename / did / hangup_type / event
     *                            ← most frequent values among the latest
     *                              inbound_reports records for the same tenant
     *                              (computed at runtime, nothing hardcoded)
     *   - transfers_count        ← 0 (calls stores no transfer information)
     *   - data5 / status         ← null (no corresponding column in calls)
     */
    private function reconstructInboundReport(string $linkedid): ?array
    {
        $call = DB::table('calls')
            ->where('linkedid', $linkedid)
            ->whereNull('deleted_at')
            ->orderBy('call_date', 'asc')
            ->orderBy('id', 'asc')
            ->first([
                'id',
                'linkedid',
                'phone_number',
                'hangup',
                'ringtime',
                'talktime',
                'owned_by_id',
                'call_date',
                'company_id',
                'status_id',
                'is_survey',
                'survey_choice',
            ]);

        if ($call === null) {
            return null;
        }

        $profile = $this->referenceProfile($call->company_id !== null ? (int) $call->company_id : null);

        $callStatus = $this->resolveCallStatus($call->status_id, $call->talktime);

        $hangupType = $profile[$callStatus === 'answered' ? 'hangup_answered' : 'hangup_missed'];
        if ($hangupType === null) {
            $hangupType = $this->statusLabel($call->status_id);
        }

        $agent = 'NONE';
        if ($call->owned_by_id !== null) {
            $agent = DB::table('users')->where('id', $call->owned_by_id)->value('name') ?? (string) $call->owned_by_id;
        }

        return $this->fillRow([
            'linkedid'          => $call->linkedid,
            'callid'            => $call->linkedid,
            'call_time'         => $call->call_date,
            'queue_description' => $profile['queue_description'],
            'agent'             => $agent,
            'hangup_type'       => $hangupType,
            'ringtime'          => max(0, (int) $call->ringtime),
            'talktime'          => max(0, (int) $call->talktime),
            'transfers_count'   => 0,
            'caller_number'     => $this->normalizePhone($call->phone_number) ?? '',
            'did'               => $this->normalizePhone($profile['did']) ?? '',
            'survey'            => $call->survey_choice ?: null,
            'company_id'        => (int) $call->company_id,
            'user_id'           => $call->owned_by_id !== null ? (int) $call->owned_by_id : null,
            'enter_queuename'   => $profile['enter_queuename'],
            'event'             => $hangupType,
            'data5'             => null,
            'status'            => null,
            'call_status'       => $callStatus,
        ]);
    }

    // =========================================================================
    //  Dynamic tenant reference profile
    // =========================================================================

    /**
     * Compute the most frequently used inbound_reports values for a tenant from
     * the latest records, so the restored row inherits the client's own mapping
     * instead of fixed values.
     *
     * The reference set is the latest REFERENCE_LIMIT records (by insertion id).
     * When the tenant has enough records of its own (>= MIN_TENANT_REFERENCE_ROWS)
     * the sample is scoped to that tenant's company_id; otherwise the global
     * latest sample is used. Each field falls back to the global sample when the
     * tenant-scoped sample has no value for it.
     */
    private function referenceProfile(?int $companyId): array
    {
        $globalRows = DB::table('inbound_reports')
            ->select(['id', 'company_id', 'did', 'queue_description', 'enter_queuename', 'hangup_type', 'call_status'])
            ->orderBy('id', 'desc')
            ->limit(self::REFERENCE_LIMIT)
            ->get();

        $rows = $globalRows;
        if ($companyId !== null && $companyId > 0) {
            $scoped = $globalRows->filter(fn($row) => (int) $row->company_id === $companyId);
            if ($scoped->count() >= self::MIN_TENANT_REFERENCE_ROWS) {
                $rows = $scoped;
            }
        }

        $answered = fn() => $rows->filter(fn($row) => $row->call_status === 'answered');
        $globalAnswered = fn() => $globalRows->filter(fn($row) => $row->call_status === 'answered');
        $missed = fn() => $rows->filter(fn($row) => $row->call_status === 'missed');
        $globalMissed = fn() => $globalRows->filter(fn($row) => $row->call_status === 'missed');

        return [
            'did'               => $this->modeOrDefault($rows, $globalRows, 'did', ''),
            'queue_description' => $this->modeOrDefault($rows, $globalRows, 'queue_description', ''),
            'enter_queuename'   => $this->modeOrDefault($rows, $globalRows, 'enter_queuename', ''),
            'hangup_answered'   => $this->modeOrDefault($answered(), $globalAnswered(), 'hangup_type', null),
            'hangup_missed'     => $this->modeOrDefault($missed(), $globalMissed(), 'hangup_type', null),
        ];
    }

    /**
     * Most frequent non-empty value of a column in the primary sample, falling
     * back to the secondary sample, then to $default.
     */
    private function modeOrDefault($primary, $fallback, string $column, $default): ?string
    {
        $value = $this->modalValue(collect($primary), $column);
        if ($value === null) {
            $value = $this->modalValue(collect($fallback), $column);
        }

        return $value ?? $default;
    }

    /**
     * Most frequently occurring non-empty value of a column within a sample.
     */
    private function modalValue($rows, string $column): ?string
    {
        $counts = [];
        foreach ($rows as $row) {
            $value = $row->$column;
            if ($value === null || $value === '') {
                continue;
            }
            $key = (string) $value;
            $counts[$key] = ($counts[$key] ?? 0) + 1;
        }

        if (empty($counts)) {
            return null;
        }

        arsort($counts);

        return (string) array_key_first($counts);
    }

    /**
     * Map a calls.status_id to an inbound report outcome using the dictionary
     * name (dynamic data, no status ids are hardcoded).
     *
     * "Up - ANSWERED" / "Up - BUSY" are treated as answered; every other status
     * is missed. A missing/empty status name falls back to the talktime signal.
     */
    private function resolveCallStatus(?int $statusId, $talktime = 0): string
    {
        $name = DB::table('calls_dict_status')
            ->where('id', $statusId)
            ->whereNull('deleted_at')
            ->value('name');

        if ($name !== null && trim($name) !== '') {
            return preg_match('/^Up\s*-\s*(ANSWERED|BUSY)\s*$/i', trim($name))
                ? 'answered'
                : 'missed';
        }

        return (int) $talktime > 0 ? 'answered' : 'missed';
    }

    /**
     * Readable status label derived from calls_dict_status.name:
     * "Up - NO ANSWER" → "NO ANSWER", "Up - ANSWERED" → "ANSWERED".
     */
    private function statusLabel(?int $statusId): string
    {
        if ($statusId === null) {
            return '';
        }

        $name = DB::table('calls_dict_status')
            ->where('id', $statusId)
            ->whereNull('deleted_at')
            ->value('name');

        if ($name === null || trim($name) === '') {
            return '';
        }

        $label = preg_replace('/^Up\s*-\s*/i', '', trim($name)) ?? '';

        return $label;
    }

    // =========================================================================
    //  Row assembly & helpers
    // =========================================================================

    /**
     * Apply the common inbound_reports mapping shared by every record type.
     */
    private function fillRow(array $fields): array
    {
        $callTime   = $fields['call_time'];
        $reportDate = Carbon::parse($callTime)->format('Y-m-d');

        return [
            'report_date' => $reportDate,
        ] + $fields;
    }

    /**
     * Normalize a phone number into local Egyptian format: an international
     * "+20" prefix is removed.
     *
     *   +201012345678 → 1012345678
     *   +20221282799  → 221282799
     *   +12125550088  → +12125550088 (not an Egyptian number, left untouched)
     */
    private function normalizePhone(?string $number): ?string
    {
        if ($number === null) {
            return null;
        }

        $number = trim($number);

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

        return $number;
    }

    // =========================================================================
    //  Date resolution
    // =========================================================================

    private function resolveDateRange(): array
    {
        if ($date = $this->option('date')) {
            $day = Carbon::parse($date);
            return [$day, $day];
        }

        $start = $this->option('start-date') ? Carbon::parse($this->option('start-date')) : null;
        $end   = $this->option('end-date') ? Carbon::parse($this->option('end-date')) : null;

        if ($start && $end) {
            return [$start, $end];
        }

        if ($start || $end) {
            $start = $start ?? Carbon::today();
            $end   = $end ?? Carbon::today();
            return [$start, $end];
        }

        $today = Carbon::today();
        return [$today, $today];
    }
}