<?php
namespace App\Console\Commands;

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

class GesconFornitoriDedupCommand extends Command
{
    protected $signature = 'gescon:fornitori-dedup
        {--apply : Applica la bonifica}
        {--limit= : Limita il numero di gruppi elaborati}
        {--admin-id= : In caso di parita preferisce il fornitore di questo amministratore}';

    protected $description = 'Bonifica i doppioni fornitori mantenendo un record canonico per identita e salvando gli alias cod_forn legacy.';

    /** @var array<int, int> */
    private array $referenceCounts = [];

    /** @var array<int, array{table:string,column:string}> */
    private array $referenceColumns = [];

    public function handle(): int
    {
        if (! Schema::hasTable('fornitori')) {
            $this->error('Tabella fornitori non disponibile.');
            return self::FAILURE;
        }

        $apply            = (bool) $this->option('apply');
        $limit            = $this->option('limit') ? max(1, (int) $this->option('limit')) : null;
        $preferredAdminId = $this->option('admin-id') !== null ? max(0, (int) $this->option('admin-id')) : null;

        $groups = $this->loadDuplicateGroups($limit);
        if ($groups === []) {
            $this->info('Nessun doppione fornitore rilevato.');
            return self::SUCCESS;
        }

        $this->referenceColumns = $this->discoverSupplierReferenceColumns();
        $this->referenceCounts  = $this->loadSupplierReferenceCounts();

        $stats = [
            'groups'         => 0,
            'duplicates'     => 0,
            'merged'         => 0,
            'deleted'        => 0,
            'moved_links'    => 0,
            'skipped_delete' => 0,
            'errors'         => 0,
        ];

        $previewRows = [];

        foreach ($groups as $groupKey => $suppliers) {
            $stats['groups']++;
            $stats['duplicates'] += count($suppliers) - 1;

            $master     = $this->pickMaster($suppliers, $preferredAdminId);
            $duplicates = array_values(array_filter($suppliers, fn(Fornitore $supplier): bool => (int) $supplier->id !== (int) $master->id));

            $previewRows[] = [
                'group'     => $groupKey,
                'keep'      => '#' . (int) $master->id . ' ' . trim((string) ($master->ragione_sociale ?? '')),
                'keep_code' => $master->cod_forn ?: '—',
                'drop'      => implode(', ', array_map(fn(Fornitore $supplier): string => '#' . (int) $supplier->id, $duplicates)),
            ];

            if (! $apply) {
                continue;
            }

            try {
                DB::transaction(function () use ($master, $duplicates, &$stats): void {
                    $masterModel      = Fornitore::query()->findOrFail((int) $master->id);
                    $duplicatesModels = Fornitore::query()
                        ->whereIn('id', array_map(fn(Fornitore $supplier): int => (int) $supplier->id, $duplicates))
                        ->get();

                    if ($duplicatesModels->isEmpty()) {
                        return;
                    }

                    $masterPayload = $this->buildMasterPayload($masterModel, $duplicatesModels->all());
                    $masterModel->fill($masterPayload);
                    if ($masterModel->isDirty()) {
                        $masterModel->save();
                    }

                    foreach ($duplicatesModels as $duplicate) {
                        $stats['moved_links'] += $this->moveSupplierReferences((int) $duplicate->id, (int) $masterModel->id);

                        if ($this->hasRemainingReferences((int) $duplicate->id)) {
                            $stats['skipped_delete']++;
                            continue;
                        }

                        $duplicate->delete();
                        $stats['deleted']++;
                        $stats['merged']++;
                    }
                });
            } catch (\Throwable $exception) {
                $stats['errors']++;
                $this->warn('Gruppo ' . $groupKey . ' non bonificato: ' . $exception->getMessage());
            }
        }

        $this->table(['Gruppo', 'Canonico', 'cod_forn', 'Duplicati'], array_slice($previewRows, 0, 20));
        $this->line(json_encode($stats, JSON_UNESCAPED_UNICODE));
        $this->info($apply ? 'Bonifica fornitori completata.' : 'Dry-run bonifica fornitori completato.');

        return self::SUCCESS;
    }

    /** @return array<string, array<int, Fornitore>> */
    private function loadDuplicateGroups(?int $limit = null): array
    {
        $suppliers = Fornitore::query()->get([
            'id',
            'amministratore_id',
            'cod_forn',
            'old_id',
            'rubrica_id',
            'ragione_sociale',
            'partita_iva',
            'codice_fiscale',
            'email',
            'pec',
            'telefono',
            'cellulare',
            'iban',
            'codice_univoco',
            'fe_features',
            'operational_config',
            'note',
        ]);

        $groups = [];
        foreach ($suppliers as $supplier) {
            $groupKey = $this->buildIdentityKey($supplier);
            if ($groupKey === null) {
                continue;
            }

            $groups[$groupKey] ??= [];
            $groups[$groupKey][] = $supplier;
        }

        $groups = array_filter($groups, fn(array $items): bool => count($items) > 1);
        ksort($groups);

        if ($limit !== null) {
            $groups = array_slice($groups, 0, $limit, true);
        }

        return $groups;
    }

    private function buildIdentityKey(Fornitore $supplier): ?string
    {
        $rubricaId = (int) ($supplier->rubrica_id ?? 0);
        if ($rubricaId > 0) {
            return 'rubrica:' . $rubricaId;
        }

        $piva = $this->normalizeNumericIdentity($supplier->partita_iva);
        if ($piva !== null) {
            return 'piva:' . $piva;
        }

        $cf = $this->normalizeAlphaNumericIdentity($supplier->codice_fiscale);
        if ($cf !== null) {
            return 'cf:' . $cf;
        }

        return null;
    }

    /** @param array<int, Fornitore> $suppliers */
    private function pickMaster(array $suppliers, ?int $preferredAdminId): Fornitore
    {
        $best      = null;
        $bestScore = null;

        foreach ($suppliers as $supplier) {
            $score = 0;

            if ((int) ($supplier->cod_forn ?? 0) > 0) {
                $score += 2000;
            }
            if ((int) ($supplier->rubrica_id ?? 0) > 0) {
                $score += 400;
            }
            if ($preferredAdminId !== null && (int) ($supplier->amministratore_id ?? 0) === $preferredAdminId) {
                $score += 300;
            }
            if (! empty($supplier->fe_features)) {
                $score += 150;
            }
            if (! empty($supplier->operational_config)) {
                $score += 100;
            }

            foreach (['partita_iva', 'codice_fiscale', 'email', 'pec', 'telefono', 'cellulare', 'iban', 'codice_univoco'] as $field) {
                if (trim((string) ($supplier->{$field} ?? '')) !== '') {
                    $score += 20;
                }
            }

            $score += ((int) ($this->referenceCounts[(int) $supplier->id] ?? 0)) * 25;

            if ($best === null || $bestScore === null || $score > $bestScore || ($score === $bestScore && (int) $supplier->id < (int) $best->id)) {
                $best      = $supplier;
                $bestScore = $score;
            }
        }

        return $best ?? $suppliers[0];
    }

    /** @param array<int, Fornitore> $duplicates */
    private function buildMasterPayload(Fornitore $master, array $duplicates): array
    {
        $payload = [];

        foreach (['rubrica_id', 'partita_iva', 'codice_fiscale', 'email', 'pec', 'telefono', 'cellulare', 'iban', 'codice_univoco'] as $field) {
            if (trim((string) ($master->{$field} ?? '')) !== '') {
                continue;
            }

            foreach ($duplicates as $duplicate) {
                $value = $duplicate->{$field} ?? null;
                if (trim((string) $value) !== '') {
                    $payload[$field] = $value;
                    break;
                }
            }
        }

        $masterFeFeatures        = is_array($master->fe_features ?? null) ? $master->fe_features : [];
        $masterOperationalConfig = is_array($master->operational_config ?? null) ? $master->operational_config : [];

        $legacyCodFornAliases = [];
        $legacyOldIdAliases   = [];
        $mergedSupplierIds    = [];

        if ((int) ($master->cod_forn ?? 0) > 0) {
            $legacyCodFornAliases[] = (int) $master->cod_forn;
        }
        if ((int) ($master->old_id ?? 0) > 0) {
            $legacyOldIdAliases[] = (int) $master->old_id;
        }

        foreach ($duplicates as $duplicate) {
            if ((int) ($duplicate->cod_forn ?? 0) > 0) {
                $legacyCodFornAliases[] = (int) $duplicate->cod_forn;
            }
            if ((int) ($duplicate->old_id ?? 0) > 0) {
                $legacyOldIdAliases[] = (int) $duplicate->old_id;
            }

            $mergedSupplierIds[]     = (int) $duplicate->id;
            $masterFeFeatures        = $this->mergeConfigRecursive($masterFeFeatures, is_array($duplicate->fe_features ?? null) ? $duplicate->fe_features : []);
            $masterOperationalConfig = $this->mergeConfigRecursive($masterOperationalConfig, is_array($duplicate->operational_config ?? null) ? $duplicate->operational_config : []);
        }

        $legacyCodFornAliases = array_values(array_unique(array_filter(array_map('intval', array_merge(
            $legacyCodFornAliases,
            array_map('intval', (array) ($masterOperationalConfig['legacy_cod_forn_aliases'] ?? []))
        )))));
        $legacyOldIdAliases = array_values(array_unique(array_filter(array_map('intval', array_merge(
            $legacyOldIdAliases,
            array_map('intval', (array) ($masterOperationalConfig['legacy_old_id_aliases'] ?? []))
        )))));
        $mergedSupplierIds = array_values(array_unique(array_filter(array_map('intval', array_merge(
            $mergedSupplierIds,
            array_map('intval', (array) ($masterOperationalConfig['merged_supplier_ids'] ?? []))
        )))));

        $masterOperationalConfig['legacy_cod_forn_aliases'] = $legacyCodFornAliases;
        $masterOperationalConfig['legacy_old_id_aliases']   = $legacyOldIdAliases;
        $masterOperationalConfig['merged_supplier_ids']     = $mergedSupplierIds;

        $payload['fe_features']        = $masterFeFeatures;
        $payload['operational_config'] = $masterOperationalConfig;

        if (trim((string) ($master->note ?? '')) === '' && $duplicates !== []) {
            $payload['note'] = 'Fornitore canonico dopo bonifica duplicati.';
        }

        return $payload;
    }

    /** @return array<int, array{table:string,column:string}> */
    private function discoverSupplierReferenceColumns(): array
    {
        $database = DB::getDatabaseName();
        $rows     = DB::select(
            "SELECT table_name, column_name
               FROM information_schema.columns
              WHERE table_schema = ?
                AND table_name <> 'fornitori'
                AND column_name IN ('fornitore_id', 'default_fornitore_id', 'assegnato_a_fornitore_id', 'fornitore_esterno_id')
              ORDER BY table_name, column_name",
            [$database]
        );

        return array_values(array_filter(array_map(function (object $row): ?array {
            $table  = (string) ($row->TABLE_NAME ?? $row->table_name ?? '');
            $column = (string) ($row->COLUMN_NAME ?? $row->column_name ?? '');
            if ($table === '' || $column === '') {
                return null;
            }

            return ['table' => $table, 'column' => $column];
        }, $rows)));
    }

    /** @return array<int, int> */
    private function loadSupplierReferenceCounts(): array
    {
        $counts = [];
        foreach ($this->discoverSupplierReferenceColumns() as $reference) {
            $table  = $reference['table'];
            $column = $reference['column'];
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $column)) {
                continue;
            }

            $rows = DB::table($table)
                ->selectRaw($column . ' as supplier_id, COUNT(*) as aggregate_count')
                ->whereNotNull($column)
                ->groupBy($column)
                ->get();

            foreach ($rows as $row) {
                $supplierId = is_numeric($row->supplier_id ?? null) ? (int) $row->supplier_id : 0;
                if ($supplierId <= 0) {
                    continue;
                }

                $counts[$supplierId] = (int) ($counts[$supplierId] ?? 0) + (int) ($row->aggregate_count ?? 0);
            }
        }

        return $counts;
    }

    private function moveSupplierReferences(int $fromSupplierId, int $toSupplierId): int
    {
        if ($fromSupplierId <= 0 || $toSupplierId <= 0 || $fromSupplierId === $toSupplierId) {
            return 0;
        }

        $moved = 0;
        foreach ($this->referenceColumns as $reference) {
            $table  = $reference['table'];
            $column = $reference['column'];

            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $column)) {
                continue;
            }

            if ($table === 'fornitore_stabile_impostazioni' && $column === 'fornitore_id') {
                $moved += $this->mergeFornitoreStabileImpostazioni($fromSupplierId, $toSupplierId);
                continue;
            }

            $moved += (int) DB::table($table)
                ->where($column, $fromSupplierId)
                ->update([$column => $toSupplierId]);
        }

        return $moved;
    }

    private function mergeFornitoreStabileImpostazioni(int $fromSupplierId, int $toSupplierId): int
    {
        if (! Schema::hasTable('fornitore_stabile_impostazioni')) {
            return 0;
        }

        $moved = 0;
        $rows  = DB::table('fornitore_stabile_impostazioni')
            ->where('fornitore_id', $fromSupplierId)
            ->get();

        foreach ($rows as $row) {
            $target = DB::table('fornitore_stabile_impostazioni')
                ->where('stabile_id', (int) $row->stabile_id)
                ->where('fornitore_id', $toSupplierId)
                ->first();

            if (! $target) {
                $moved += (int) DB::table('fornitore_stabile_impostazioni')
                    ->where('id', (int) $row->id)
                    ->update(['fornitore_id' => $toSupplierId]);
                continue;
            }

            if (Schema::hasColumn('fornitore_stabile_impostazioni', 'meta')) {
                $sourceMeta = json_decode((string) ($row->meta ?? '[]'), true);
                $targetMeta = json_decode((string) ($target->meta ?? '[]'), true);
                $mergedMeta = $this->mergeConfigRecursive(is_array($targetMeta) ? $targetMeta : [], is_array($sourceMeta) ? $sourceMeta : []);

                DB::table('fornitore_stabile_impostazioni')
                    ->where('id', (int) $target->id)
                    ->update([
                        'meta'       => json_encode($mergedMeta, JSON_UNESCAPED_UNICODE),
                        'updated_at' => now(),
                    ]);
            }

            DB::table('fornitore_stabile_impostazioni')->where('id', (int) $row->id)->delete();
            $moved++;
        }

        return $moved;
    }

    private function hasRemainingReferences(int $supplierId): bool
    {
        foreach ($this->referenceColumns as $reference) {
            $table  = $reference['table'];
            $column = $reference['column'];
            if (! Schema::hasTable($table) || ! Schema::hasColumn($table, $column)) {
                continue;
            }

            if (DB::table($table)->where($column, $supplierId)->exists()) {
                return true;
            }
        }

        return false;
    }

    /** @param array<string, mixed> $master
     *  @param array<string, mixed> $duplicate
     *  @return array<string, mixed>
     */
    private function mergeConfigRecursive(array $master, array $duplicate): array
    {
        foreach ($duplicate as $key => $value) {
            if (! array_key_exists($key, $master)) {
                $master[$key] = $value;
                continue;
            }

            if (is_array($master[$key]) && is_array($value)) {
                $master[$key] = $this->mergeConfigRecursive($master[$key], $value);
            }
        }

        return $master;
    }

    private function normalizeNumericIdentity(mixed $value): ?string
    {
        $digits = preg_replace('/\D+/', '', trim((string) $value)) ?? '';
        if ($digits === '' || preg_match('/^0+$/', $digits) === 1) {
            return null;
        }

        return strlen($digits) < 11 ? str_pad($digits, 11, '0', STR_PAD_LEFT) : $digits;
    }

    private function normalizeAlphaNumericIdentity(mixed $value): ?string
    {
        $normalized = strtoupper(trim((string) $value));
        $normalized = preg_replace('/\s+/', '', $normalized) ?? '';
        if ($normalized === '' || in_array($normalized, ['0', '00', '000', 'ND', 'N/D', 'NULL'], true)) {
            return null;
        }

        return $normalized;
    }
}