<?php
namespace App\Console\Commands;

use App\Models\RubricaUniversale;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;

class GesconRepairNominativiContactsCommand extends Command
{
    protected $signature = 'gescon:repair-nominativi-contacts
        {--stabile= : Codice stabile da bonificare}
        {--limit=5000 : Max persone da analizzare}
        {--apply : Applica realmente le modifiche}';

    protected $description = 'Bonifica persone/rubrica dei nominativi materializzati adottando telefoni e recapiti dai duplicati rubrica piu ricchi.';

    public function handle(): int
    {
        if (! Schema::hasTable('persone') || ! Schema::hasTable('persone_unita_relazioni') || ! Schema::hasTable('unita_immobiliari') || ! Schema::hasTable('stabili') || ! Schema::hasTable('rubrica_universale')) {
            $this->error('Tabelle richieste non disponibili.');
            return self::FAILURE;
        }

        $apply   = (bool) $this->option('apply');
        $limit   = max(1, min((int) ($this->option('limit') ?? 5000), 50000));
        $stabile = trim((string) ($this->option('stabile') ?? ''));

        $query = DB::table('persone_unita_relazioni as pur')
            ->join('persone as p', 'p.id', '=', 'pur.persona_id')
            ->join('unita_immobiliari as ui', 'ui.id', '=', 'pur.unita_id')
            ->join('stabili as s', 's.id', '=', 'ui.stabile_id')
            ->select([
                'p.id as persona_id',
                'p.nome',
                'p.cognome',
                'p.ragione_sociale',
                'p.telefono_principale',
                'p.telefono_secondario',
                'p.email_principale',
                'p.email_pec',
                'p.note',
                's.id as stabile_id',
                's.codice_stabile',
                's.amministratore_id',
            ])
            ->distinct()
            ->orderBy('s.codice_stabile')
            ->orderBy('p.id')
            ->limit($limit);

        if ($stabile !== '') {
            $trimmed = ltrim($stabile, '0');
            $query->where(function ($builder) use ($stabile, $trimmed): void {
                $builder->where('s.codice_stabile', $stabile);
                if ($trimmed !== '' && $trimmed !== $stabile) {
                    $builder->orWhere('s.codice_stabile', $trimmed);
                }
            });
        }

        $rows = $query->get();
        if ($rows->isEmpty()) {
            $this->info('Nessuna persona da bonificare.');
            return self::SUCCESS;
        }

        $stats = [
            'processed'       => 0,
            'persona_updated' => 0,
            'rubrica_updated' => 0,
            'no_match'        => 0,
            'phone_conflicts' => 0,
        ];

        foreach ($rows as $row) {
            $stats['processed']++;

            $identityLabel = trim((string) ($row->ragione_sociale ?: trim(($row->nome ?? '') . ' ' . ($row->cognome ?? ''))));
            $tokenKey      = $this->normalizeTokenSetKey($identityLabel);
            if ($tokenKey === null) {
                $stats['no_match']++;
                continue;
            }

            $rubriche = RubricaUniversale::query()
                ->when((int) ($row->amministratore_id ?? 0) > 0, fn($builder) => $builder->where(function ($query) use ($row): void {
                    $query->where('amministratore_id', (int) $row->amministratore_id)
                        ->orWhereNull('amministratore_id');
                }))
                ->get()
                ->filter(fn(RubricaUniversale $rubrica): bool => $this->normalizeTokenSetKey($this->buildRubricaIdentityLabel($rubrica)) === $tokenKey)
                ->sortByDesc(fn(RubricaUniversale $rubrica): int => $this->scoreRubrica($rubrica))
                ->values();

            if ($rubriche->isEmpty()) {
                $stats['no_match']++;
                continue;
            }

            /** @var RubricaUniversale $best */
            $best               = $rubriche->first();
            $canonicalRubricaId = $this->extractRubricaIdFromPersonaNote((string) ($row->note ?? ''));
            $canonicalRubrica   = $canonicalRubricaId > 0 ? RubricaUniversale::query()->find($canonicalRubricaId) : null;
            if (! $canonicalRubrica instanceof RubricaUniversale) {
                $canonicalRubrica = $best;
            }

            $personaUpdates = $this->buildPersonaUpdates($row, $best, $stats);
            $rubricaUpdates = $this->buildRubricaUpdates($canonicalRubrica, $best);

            if ($apply && $personaUpdates !== []) {
                try {
                    DB::table('persone')->where('id', (int) $row->persona_id)->update($personaUpdates + ['updated_at' => now()]);
                    $stats['persona_updated']++;
                } catch (\Throwable $e) {
                    $this->warn('Skip persona #' . (int) $row->persona_id . ': ' . $e->getMessage());
                }
            } elseif (! $apply && $personaUpdates !== []) {
                $stats['persona_updated']++;
            }

            if ($apply && $rubricaUpdates !== []) {
                $canonicalRubrica->fill($rubricaUpdates);
                $canonicalRubrica->save();
                $stats['rubrica_updated']++;
            } elseif (! $apply && $rubricaUpdates !== []) {
                $stats['rubrica_updated']++;
            }

            foreach ($rubriche as $candidateRubrica) {
                if (! $candidateRubrica instanceof RubricaUniversale || (int) $candidateRubrica->id === (int) $best->id) {
                    continue;
                }

                $candidateUpdates = $this->buildRubricaUpdates($candidateRubrica, $best);
                if ($apply && $candidateUpdates !== []) {
                    $candidateRubrica->fill($candidateUpdates);
                    $candidateRubrica->save();
                    $stats['rubrica_updated']++;
                } elseif (! $apply && $candidateUpdates !== []) {
                    $stats['rubrica_updated']++;
                }
            }
        }

        $mode = $apply ? 'Bonifica eseguita' : 'Dry-run bonifica';
        $this->info($mode . ': ' . json_encode($stats, JSON_UNESCAPED_UNICODE));

        return self::SUCCESS;
    }

    private function buildRubricaIdentityLabel(RubricaUniversale $rubrica): string
    {
        return trim(implode(' ', array_filter([
            (string) ($rubrica->nome ?? ''),
            (string) ($rubrica->cognome ?? ''),
            (string) ($rubrica->ragione_sociale ?? ''),
        ])));
    }

    private function normalizeTokenSetKey(?string $value): ?string
    {
        $tokens = preg_split('/\s+/', Str::upper(trim((string) $value))) ?: [];
        $tokens = array_values(array_filter(array_map(function (string $token): string {
            return (string) preg_replace('/[^A-Z0-9]+/u', '', $token);
        }, $tokens)));

        if ($tokens === []) {
            return null;
        }

        sort($tokens, SORT_STRING);

        return implode('|', $tokens);
    }

    private function normalizePhone(?string $value): ?string
    {
        $normalized = preg_replace('/[^\d+]/', '', (string) ($value ?? ''));

        return is_string($normalized) && $normalized !== '' ? $normalized : null;
    }

    private function normalizeEmail(?string $value): ?string
    {
        $normalized = strtolower(trim((string) ($value ?? '')));
        $normalized = preg_replace('/\s+/', '', $normalized);

        return is_string($normalized) && $normalized !== '' ? $normalized : null;
    }

    private function scoreRubrica(RubricaUniversale $rubrica): int
    {
        $score = 0;

        foreach (['telefono_cellulare', 'telefono_ufficio', 'telefono_casa', 'email', 'pec', 'codice_fiscale', 'partita_iva', 'riferimento_stabile', 'riferimento_unita'] as $field) {
            if (filled($rubrica->{$field})) {
                $score += in_array($field, ['telefono_cellulare', 'email', 'codice_fiscale', 'partita_iva'], true) ? 10 : 4;
            }
        }

        return $score;
    }

    private function extractRubricaIdFromPersonaNote(string $note): int
    {
        if (preg_match('/rubrica_id=(\d+)/', $note, $matches)) {
            return (int) ($matches[1] ?? 0);
        }

        return 0;
    }

    /**
     * @param  object  $row
     * @return array<string, mixed>
     */
    private function buildPersonaUpdates(object $row, RubricaUniversale $best, array &$stats): array
    {
        $updates = [];

        $bestMobile = $this->normalizePhone((string) ($best->telefono_cellulare ?: $best->telefono_ufficio ?: $best->telefono_casa));
        $bestOffice = $this->normalizePhone((string) ($best->telefono_ufficio ?: $best->telefono_casa));
        $bestEmail  = $this->normalizeEmail((string) ($best->email ?? ''));
        $bestPec    = $this->normalizeEmail((string) ($best->pec ?? ''));

        if (! filled($row->telefono_principale) && $bestMobile !== null && ! $this->phoneExistsOnOtherPersona($bestMobile, (int) $row->persona_id)) {
            $updates['telefono_principale'] = $bestMobile;
        } elseif (! filled($row->telefono_principale) && $bestMobile !== null) {
            $stats['phone_conflicts']++;
        }
        if (! filled($row->telefono_secondario) && $bestOffice !== null && $bestOffice !== ($updates['telefono_principale'] ?? $row->telefono_principale)) {
            $updates['telefono_secondario'] = $bestOffice;
        }
        if (! filled($row->email_principale) && $bestEmail !== null) {
            $updates['email_principale'] = $bestEmail;
        }
        if (! filled($row->email_pec) && $bestPec !== null) {
            $updates['email_pec'] = $bestPec;
        }

        return $updates;
    }

    private function phoneExistsOnOtherPersona(string $phone, int $personaId): bool
    {
        return DB::table('persone')
            ->where('telefono_principale', $phone)
            ->where('id', '!=', $personaId)
            ->exists();
    }

    /**
     * @return array<string, mixed>
     */
    private function buildRubricaUpdates(RubricaUniversale $canonical, RubricaUniversale $best): array
    {
        $updates = [];

        foreach (['telefono_cellulare', 'telefono_ufficio', 'telefono_casa', 'email', 'pec', 'codice_fiscale', 'partita_iva', 'prefisso_cellulare', 'telefono_cellulare_nazionale'] as $field) {
            if (! filled($canonical->{$field}) && filled($best->{$field})) {
                $updates[$field] = $best->{$field};
            }
        }

        return $updates;
    }
}