<?php
namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class GesconSyncOrdinariaPreventivoCommand extends Command
{
    protected $signature = 'gescon:sync-ordinaria-preventivo
        {stabile_code : Codice stabile legacy, es. 0023}
        {year : Anno gestione, es. 2025}
        {--stabile-id= : ID stabile NetGescon se gia noto}
        {--gestione-id= : ID gestione contabile ordinaria se gia noto}';

    protected $description = 'Sincronizza in modo rigoroso preventivo ordinario, tabelle millesimali e dettagli da gescon_import per uno stabile/anno specifici';

    public function handle(): int
    {
        $stableCode = trim((string) $this->argument('stabile_code'));
        $year       = (int) $this->argument('year');

        if ($stableCode === '' || $year < 1900) {
            $this->error('Parametri non validi.');

            return self::FAILURE;
        }

        $stableId   = $this->resolveStableId($stableCode);
        $gestioneId = $this->resolveGestioneId($stableId, $year);
        $legacyVoci = $this->loadLegacyVoci($stableCode, $year);

        if ($legacyVoci->isEmpty()) {
            $this->error('Nessuna voce legacy ordinaria trovata per stabile ' . $stableCode . ' anno ' . $year . '.');

            return self::FAILURE;
        }

        $legacyHeaders = $this->loadLegacyTableHeaders($stableCode);
        if ($legacyHeaders === []) {
            $this->error('Nessuna intestazione tabella legacy trovata per stabile ' . $stableCode . '.');

            return self::FAILURE;
        }

        $tableCodes    = collect(array_keys($legacyHeaders))->values();
        $legacyDetails = $this->loadLegacyDettagli($stableCode, $year, $tableCodes);
        $this->assertLegacyVociHaveKnownTables($legacyVoci, $tableCodes);

        DB::transaction(function () use ($stableCode, $stableId, $year, $gestioneId, $legacyVoci, $legacyDetails, $tableCodes, $legacyHeaders): void {
            $this->ensureLegacyUnitCoverage($stableCode, $stableId, $legacyDetails);
            $tableMap = $this->syncTabelleMillesimali($stableId, $year, $tableCodes, $legacyDetails, $legacyHeaders);
            $this->syncDettagliMillesimali($stableId, $year, $legacyDetails, $tableMap);
            $this->syncVociSpesa($stableId, $gestioneId, $year, $legacyVoci, $tableMap);
        });

        $totalBudget = (float) DB::table('voci_spesa')
            ->where('stabile_id', $stableId)
            ->where('gestione_contabile_id', $gestioneId)
            ->sum('importo_default');

        $this->info('Sync completato per stabile ' . $stableCode . ' anno ' . $year . '.');
        $this->line('Gestione ordinaria: #' . $gestioneId);
        $this->line('Voci spesa sincronizzate: ' . DB::table('voci_spesa')->where('stabile_id', $stableId)->where('gestione_contabile_id', $gestioneId)->count());
        $this->line('Tabelle millesimali sincronizzate: ' . DB::table('tabelle_millesimali')->where('stabile_id', $stableId)->where('anno_gestione', $year)->count());
        $this->line('Totale preventivo importato: ' . number_format($totalBudget, 2, '.', ''));

        return self::SUCCESS;
    }

    private function resolveStableId(string $stableCode): int
    {
        $optionStableId = $this->option('stabile-id');
        if (is_numeric($optionStableId) && (int) $optionStableId > 0) {
            return (int) $optionStableId;
        }

        $query  = DB::table('stabili');
        $stable = $query
            ->where('codice_stabile', $stableCode)
            ->orWhere('codice_stabile', ltrim($stableCode, '0'))
            ->orderBy('id')
            ->first(['id']);

        if (! $stable) {
            throw new \RuntimeException('Stabile NetGescon non trovato per codice ' . $stableCode . '.');
        }

        return (int) $stable->id;
    }

    private function resolveGestioneId(int $stableId, int $year): int
    {
        $optionGestioneId = $this->option('gestione-id');
        if (is_numeric($optionGestioneId) && (int) $optionGestioneId > 0) {
            return (int) $optionGestioneId;
        }

        $gestione = DB::table('gestioni_contabili')
            ->where('stabile_id', $stableId)
            ->where('anno_gestione', $year)
            ->where('tipo_gestione', 'ordinaria')
            ->orderByDesc('gestione_attiva')
            ->orderByDesc('id')
            ->first(['id']);

        if (! $gestione) {
            throw new \RuntimeException('Gestione ordinaria non trovata per stabile #' . $stableId . ' anno ' . $year . '.');
        }

        return (int) $gestione->id;
    }

    private function loadLegacyVoci(string $stableCode, int $year): Collection
    {
        return DB::connection('gescon_import')
            ->table('voc_spe')
            ->where('cod_stabile', $stableCode)
            ->where('legacy_year', $year)
            ->where(function ($query): void {
                $query->where('v_ors', 'O')->orWhereNull('v_ors');
            })
            ->orderBy('tabella')
            ->orderBy('cod')
            ->get([
                'cod',
                'descriz',
                'tabella',
                'preventivo_euro',
                'consuntivo_euro',
                'perc_proprietario',
                'perc_inquilino',
                'note',
                'v_ors',
            ]);
    }

    private function loadLegacyDettagli(string $stableCode, int $year, Collection $tableCodes): Collection
    {
        return DB::connection('gescon_import')
            ->table('dett_tab')
            ->where('cod_stabile', $stableCode)
            ->where('legacy_year', $year)
            ->whereIn('cod_tab', $tableCodes->all())
            ->orderBy('cod_tab')
            ->orderBy('id_cond')
            ->orderBy('cond_inquil')
            ->get([
                'cod_tab',
                'id_cond',
                'cond_inquil',
                'mm',
                'prev_euro',
                'cons_euro',
            ]);
    }

    /**
     * @return array<string, object>
     */
    private function loadLegacyTableHeaders(string $stableCode): array
    {
        if (! Schema::connection('gescon_import')->hasTable('tabelle_millesimali')) {
            return [];
        }

        $columns    = Schema::connection('gescon_import')->getColumnListing('tabelle_millesimali');
        $codeColumn = in_array('cod_tabella', $columns, true)
            ? 'cod_tabella'
            : (in_array('cod_tab', $columns, true) ? 'cod_tab' : null);

        if (! $codeColumn) {
            return [];
        }

        $query = DB::connection('gescon_import')->table('tabelle_millesimali');
        if (in_array('cod_stabile', $columns, true)) {
            $query->where('cod_stabile', $stableCode);
        }

        if (in_array('nord', $columns, true)) {
            $query
                ->orderByRaw('CASE WHEN nord IS NULL THEN 1 ELSE 0 END')
                ->orderBy('nord');
        }

        $rows = $query
            ->orderBy($codeColumn)
            ->get();

        $headers = [];

        foreach ($rows as $row) {
            $code = strtoupper(trim((string) ($row->{$codeColumn} ?? '')));
            if ($code === '' || isset($headers[$code])) {
                continue;
            }

            $headers[$code] = $row;
        }

        return $headers;
    }

    private function assertLegacyVociHaveKnownTables(Collection $legacyVoci, Collection $tableCodes): void
    {
        $knownTables = array_fill_keys($tableCodes->all(), true);
        $unknown     = $legacyVoci
            ->pluck('tabella')
            ->filter(fn($value): bool => is_string($value) && trim($value) !== '')
            ->map(fn(string $value): string => trim($value))
            ->reject(fn(string $value): bool => isset($knownTables[$value]))
            ->unique()
            ->values()
            ->all();

        if ($unknown !== []) {
            throw new \RuntimeException('Tabelle legacy referenziate dalle voci ma assenti nelle intestazioni archive: ' . implode(', ', $unknown) . '.');
        }
    }

    private function ensureLegacyUnitCoverage(string $stableCode, int $stableId, Collection $legacyDetails): void
    {
        $legacyIds = $legacyDetails
            ->pluck('id_cond')
            ->filter(fn($value): bool => is_string($value) && trim($value) !== '')
            ->map(fn(string $value): string => trim($value))
            ->unique()
            ->values();

        $existingIds = DB::table('unita_immobiliari')
            ->where('stabile_id', $stableId)
            ->whereNull('deleted_at')
            ->whereIn('legacy_cond_id', $legacyIds->all())
            ->pluck('legacy_cond_id')
            ->map(fn($value): string => trim((string) $value))
            ->all();

        $existingSet = array_fill_keys($existingIds, true);

        foreach ($legacyIds as $legacyId) {
            if (isset($existingSet[$legacyId])) {
                continue;
            }

            $sourceRow = $this->resolveCondominSourceRow($stableCode, $legacyId);
            $this->createMissingLegacyUnit($stableCode, $stableId, $legacyId, $sourceRow);
        }
    }

    private function resolveCondominSourceRow(string $stableCode, string $legacyId): object
    {
        $idCondRows = DB::connection('gescon_import')
            ->table('condomin')
            ->where('cod_stabile', $stableCode)
            ->where('id_cond', $legacyId)
            ->orderByDesc('legacy_year')
            ->orderByDesc('id')
            ->get();

        if ($idCondRows->isNotEmpty()) {
            return $idCondRows->first();
        }

        $codCondRows = DB::connection('gescon_import')
            ->table('condomin')
            ->where('cod_stabile', $stableCode)
            ->where('cod_cond', $legacyId)
            ->orderByDesc('legacy_year')
            ->orderByDesc('id')
            ->get();

        if ($codCondRows->isNotEmpty()) {
            return $codCondRows->first();
        }

        throw new \RuntimeException('Impossibile risolvere la fonte condomin legacy per id_cond ' . $legacyId . '.');
    }

    private function createMissingLegacyUnit(string $stableCode, int $stableId, string $legacyId, object $sourceRow): void
    {
        $internal = trim((string) ($sourceRow->interno ?? ''));
        if ($internal === '') {
            throw new \RuntimeException('Interno mancante per il legacy id ' . $legacyId . '.');
        }

        $scale       = trim((string) ($sourceRow->scala ?? ''));
        $scale       = $scale !== '' ? strtoupper($scale) : 'A';
        $floor       = $this->normalizeFloor($sourceRow->piano ?? null);
        $displayName = trim((string) (($sourceRow->cognome ?? '') . ' ' . ($sourceRow->nome ?? '')));
        if ($displayName === '') {
            $displayName = trim((string) ($sourceRow->nom_cond ?? ''));
        }

        $code = $this->buildUnitCode($stableCode, $scale, $internal, $legacyId);

        if (DB::table('unita_immobiliari')->where('codice_unita', $code)->exists()) {
            throw new \RuntimeException('Codice unita gia presente durante il backfill rigoroso: ' . $code . '.');
        }

        DB::table('unita_immobiliari')->insert([
            'stabile_id'          => $stableId,
            'codice_unita'        => $code,
            'denominazione'       => $displayName !== '' ? $displayName : ('Unita legacy ' . $legacyId),
            'scala'               => $scale,
            'piano'               => $floor,
            'interno'             => $internal,
            'legacy_cond_id'      => $legacyId,
            'tipo_unita'          => 'abitazione',
            'stato_occupazione'   => 'occupata_proprietario',
            'stato_conservazione' => 'buono',
            'millesimi_generali'  => 0,
            'attiva'              => true,
            'unita_demo'          => false,
            'created_at'          => now(),
            'updated_at'          => now(),
        ]);
    }

    /**
     * @return array<string, int>
     */
    private function syncTabelleMillesimali(int $stableId, int $year, Collection $tableCodes, Collection $legacyDetails, array $legacyHeaders): array
    {
        $existingRows = DB::table('tabelle_millesimali')
            ->where('stabile_id', $stableId)
            ->get(['id', 'codice_tabella', 'nome_tabella']);

        $existingByCode = [];
        foreach ($existingRows as $existingRow) {
            foreach ([
                strtoupper(trim((string) ($existingRow->codice_tabella ?? ''))),
                strtoupper(trim((string) ($existingRow->nome_tabella ?? ''))),
            ] as $existingCode) {
                if ($existingCode === '' || isset($existingByCode[$existingCode])) {
                    continue;
                }

                $existingByCode[$existingCode] = (int) $existingRow->id;
            }
        }

        $tableIdsToRefresh = [];
        foreach ($tableCodes as $tableCode) {
            $normalizedCode = strtoupper(trim((string) $tableCode));
            $existingId     = $existingByCode[$normalizedCode] ?? null;
            if ($existingId) {
                $tableIdsToRefresh[] = $existingId;
            }
        }

        if ($tableIdsToRefresh !== []) {
            DB::table('dettaglio_millesimi')
                ->whereIn('tabella_millesimale_id', $tableIdsToRefresh)
                ->delete();
        }

        $detailSummary = $legacyDetails
            ->groupBy('cod_tab')
            ->map(function (Collection $rows): array {
                return [
                    'total_mm' => (float) $rows->sum(fn($row): float => is_numeric($row->mm ?? null) ? (float) $row->mm : 0.0),
                    'rows'     => $rows->count(),
                ];
            });

        $tableMap  = [];
        $sortOrder = 1;

        foreach ($tableCodes as $tableCode) {
            $normalizedCode = strtoupper(trim((string) $tableCode));
            $summary        = $detailSummary->get($tableCode, ['total_mm' => 0.0, 'rows' => 0]);
            $header         = $legacyHeaders[$normalizedCode] ?? null;
            $rawCalcolo     = $header->tipo_calcolo ?? $header->calcolo ?? null;
            $calcolo        = $this->normalizeLegacyCalcolo($rawCalcolo);
            $tipoLegacy     = $this->normalizeLegacyTipo($header->tipologia ?? $header->tipo ?? null) ?? 'O';
            $tipoTabella    = $this->resolveTipoTabellaFromLegacy($tipoLegacy, $calcolo, $normalizedCode);
            $tipoCalcoloDb  = in_array($calcolo, ['millesimi', 'parti', 'fisso'], true)
                ? $calcolo
                : ($normalizedCode === 'ACQUA' ? 'parti' : 'millesimi');
            $denominazione = trim((string) ($header->denominazione ?? $header->descrizione ?? $tableCode));
            $ordine        = null;

            foreach (['nord', 'ordinamento'] as $orderKey) {
                if (isset($header->{$orderKey}) && is_numeric($header->{$orderKey})) {
                    $ordine = (int) $header->{$orderKey};
                    break;
                }
            }

            $sortValue = $ordine ?? $sortOrder;

            $payload = [
                'stabile_id'             => $stableId,
                'anno_gestione'          => $year,
                'nome_tabella'           => $tableCode,
                'denominazione'          => $denominazione !== '' ? $denominazione : $tableCode,
                'codice_tabella'         => $tableCode,
                'legacy_codice'          => $tableCode,
                'ordine_visualizzazione' => $sortValue,
                'tipo_tabella'           => $tipoTabella,
                'tipo_calcolo'           => $tipoCalcoloDb,
                'attiva'                 => true,
                'ordinamento'            => $sortValue,
                'nord'                   => $sortValue,
                'totale_millesimi'       => $summary['total_mm'],
                'meta_legacy'            => json_encode([
                    'strict_source' => 'gescon_import',
                    'legacy_year'   => $year,
                    'cod_tab'       => $normalizedCode,
                    'tipo'          => $tipoLegacy,
                    'calcolo'       => is_string($rawCalcolo) && trim($rawCalcolo) !== '' ? strtoupper(trim($rawCalcolo)) : null,
                    'detail_rows'   => $summary['rows'],
                ]),
                'updated_at'             => now(),
            ];

            $existingId = $existingByCode[$normalizedCode] ?? null;

            if ($existingId) {
                DB::table('tabelle_millesimali')
                    ->where('id', $existingId)
                    ->update($payload);

                $tableMap[$tableCode] = (int) $existingId;
            } else {
                $payload['created_at'] = now();
                DB::table('tabelle_millesimali')->insert($payload);
                $tableMap[$tableCode] = (int) DB::table('tabelle_millesimali')
                    ->where('stabile_id', $stableId)
                    ->where('codice_tabella', $tableCode)
                    ->value('id');
            }

            $sortOrder++;
        }

        $this->pruneUnusedTablesOutsideLegacySet($stableId, $tableCodes);

        return $tableMap;
    }

    private function pruneUnusedTablesOutsideLegacySet(int $stableId, Collection $tableCodes): void
    {
        $allowedCodes = $tableCodes
            ->map(fn($value): string => strtoupper(trim((string) $value)))
            ->filter(fn(string $value): bool => $value !== '')
            ->values()
            ->all();

        $rows = DB::table('tabelle_millesimali')
            ->where('stabile_id', $stableId)
            ->get(['id', 'codice_tabella', 'nome_tabella']);

        foreach ($rows as $row) {
            $code = strtoupper(trim((string) ($row->codice_tabella ?: $row->nome_tabella ?: '')));
            if ($code === '' || in_array($code, $allowedCodes, true)) {
                continue;
            }

            $tableId = (int) $row->id;
            $hasVoci = DB::table('voci_spesa')
                ->where('stabile_id', $stableId)
                ->where('tabella_millesimale_default_id', $tableId)
                ->exists();

            $hasDettagli = DB::table('dettaglio_millesimi')
                ->where('tabella_millesimale_id', $tableId)
                ->exists();

            if ($hasVoci || $hasDettagli) {
                continue;
            }

            DB::table('tabelle_millesimali')
                ->where('id', $tableId)
                ->delete();
        }
    }

    /**
     * @param array<string, int> $tableMap
     */
    private function syncDettagliMillesimali(int $stableId, int $year, Collection $legacyDetails, array $tableMap): void
    {
        $tableIds = array_values($tableMap);
        if ($tableIds === []) {
            return;
        }

        DB::table('dettaglio_millesimi')
            ->whereIn('tabella_millesimale_id', $tableIds)
            ->delete();

        $groupedRows = $legacyDetails
            ->groupBy(function ($row): string {
                return implode('|', [
                    trim((string) ($row->cod_tab ?? '')),
                    trim((string) ($row->id_cond ?? '')),
                    strtoupper(trim((string) ($row->cond_inquil ?? ''))),
                ]);
            });

        foreach ($groupedRows as $groupKey => $rows) {
            [$tableCode, $legacyId, $role] = explode('|', $groupKey);
            $tableId                       = $tableMap[$tableCode] ?? null;
            if (! $tableId) {
                throw new \RuntimeException('Tabella millesimale non risolta per codice ' . $tableCode . '.');
            }

            $unitId = DB::table('unita_immobiliari')
                ->where('stabile_id', $stableId)
                ->whereNull('deleted_at')
                ->where('legacy_cond_id', $legacyId)
                ->value('id');

            if (! $unitId) {
                throw new \RuntimeException('Unita NetGescon non risolta per legacy_cond_id ' . $legacyId . '.');
            }

            $millesimi  = (float) $rows->sum(fn($row): float => is_numeric($row->mm ?? null) ? (float) $row->mm : 0.0);
            $valorePrev = (float) $rows->sum(fn($row): float => is_numeric($row->prev_euro ?? null) ? (float) $row->prev_euro : 0.0);
            $valoreCons = (float) $rows->sum(fn($row): float => is_numeric($row->cons_euro ?? null) ? (float) $row->cons_euro : 0.0);

            DB::table('dettaglio_millesimi')->insert([
                'tabella_millesimale_id' => $tableId,
                'unita_immobiliare_id'   => (int) $unitId,
                'millesimi'              => $millesimi,
                'valore_prev'            => $valorePrev,
                'valore_cons'            => $valoreCons,
                'ruolo_legacy'           => $role !== '' ? $role : null,
                'legacy_interno'         => $legacyId,
                'partecipa'              => true,
                'note'                   => null,
                'legacy_payload'         => json_encode([
                    'strict_source' => 'gescon_import',
                    'legacy_year'   => $year,
                    'cod_tab'       => $tableCode,
                    'id_cond'       => $legacyId,
                    'cond_inquil'   => $role !== '' ? $role : null,
                ]),
                'created_at'             => now(),
                'updated_at'             => now(),
            ]);
        }
    }

    /**
     * @param array<string, int> $tableMap
     */
    private function syncVociSpesa(int $stableId, int $gestioneId, int $year, Collection $legacyVoci, array $tableMap): void
    {
        DB::table('voci_spesa')
            ->where('stabile_id', $stableId)
            ->where('gestione_contabile_id', $gestioneId)
            ->delete();

        $sortOrder = 1;

        foreach ($legacyVoci as $voce) {
            $code = trim((string) ($voce->cod ?? ''));
            if ($code === '') {
                continue;
            }

            $tableCode = trim((string) ($voce->tabella ?? ''));
            $tableId   = $tableCode !== '' ? ($tableMap[$tableCode] ?? null) : null;

            if ($tableCode !== '' && ! $tableId) {
                throw new \RuntimeException('Tabella legacy non risolta per la voce ' . $code . ': ' . $tableCode . '.');
            }

            $payload = [
                'stabile_id'                     => $stableId,
                'gestione_contabile_id'          => $gestioneId,
                'codice'                         => $code,
                'legacy_codice'                  => $code,
                'descrizione'                    => trim((string) ($voce->descriz ?? '')),
                'categoria'                      => 'ordinaria',
                'tipo_gestione'                  => 'ordinaria',
                'tabella_millesimale_default_id' => $tableId,
                'importo_default'                => is_numeric($voce->preventivo_euro ?? null) ? (float) $voce->preventivo_euro : 0.0,
                'importo_consuntivo'             => is_numeric($voce->consuntivo_euro ?? null) ? (float) $voce->consuntivo_euro : 0.0,
                'percentuale_condomino'          => is_numeric($voce->perc_proprietario ?? null) ? (float) $voce->perc_proprietario : 0.0,
                'percentuale_inquilino'          => is_numeric($voce->perc_inquilino ?? null) ? (float) $voce->perc_inquilino : 0.0,
                'attiva'                         => true,
                'ordinamento'                    => $sortOrder,
                'nord'                           => $sortOrder,
                'tipo'                           => 'ordinaria',
                'legacy_tipo'                    => trim((string) ($voce->v_ors ?? 'O')) ?: 'O',
                'note'                           => trim((string) ($voce->note ?? '')) !== '' ? trim((string) ($voce->note ?? '')) : null,
                'meta_legacy'                    => json_encode([
                    'strict_source' => 'gescon_import',
                    'legacy_year'   => $year,
                    'cod_tab'       => $tableCode !== '' ? $tableCode : null,
                ]),
                'updated_at'                     => now(),
            ];

            $payload['created_at'] = now();
            DB::table('voci_spesa')->insert($payload);

            $sortOrder++;
        }
    }

    private function normalizeFloor(mixed $rawFloor): int
    {
        $value = strtoupper(trim((string) $rawFloor));

        return match ($value) {
            '', 'T', 'PT', 'R', 'PR' => 0,
            'S', 'PS', '-1' => -1,
            default => is_numeric($value) ? (int) $value : 0,
        };
    }

    private function buildUnitCode(string $stableCode, string $scale, string $internal, string $legacyId): string
    {
        $normalizedStable   = trim($stableCode);
        $normalizedScale    = trim($scale) !== '' ? trim($scale) : 'A';
        $normalizedInternal = strtoupper(trim(preg_replace('/\s+/', '', $internal) ?? $internal));

        return $normalizedStable . '-' . $normalizedScale . '-' . $normalizedInternal . '-C' . $legacyId;
    }

    private function normalizeLegacyCalcolo(?string $raw): ?string
    {
        $value = strtolower(trim((string) ($raw ?? '')));
        if ($value === '') {
            return null;
        }

        return match ($value) {
            'm'     => 'millesimi',
            'a', 'c' => 'a_consumo',
            'x'     => 'conguagli',
            'p'     => 'personali',
            default => $value,
        };
    }

    private function normalizeLegacyTipo(?string $raw): ?string
    {
        $value = strtoupper(trim((string) ($raw ?? '')));
        if ($value === '') {
            return null;
        }

        return match ($value) {
            'O', 'ORD', 'ORDINARIA'      => 'O',
            'R', 'RISC', 'RISCALDAMENTO' => 'R',
            'S', 'STRA', 'STRAORDINARIA' => 'S',
            default => $value,
        };
    }

    private function resolveTipoTabellaFromLegacy(?string $tipoLegacy, ?string $calcolo, ?string $codice): string
    {
        $normalizedCalcolo = strtolower(trim((string) ($calcolo ?? '')));
        $normalizedCode    = strtoupper(trim((string) ($codice ?? '')));

        if ($normalizedCode === 'ACQUA' || in_array($normalizedCalcolo, ['a_consumo', 'acqua', 'consumo', 'c', 'a'], true)) {
            return 'custom';
        }

        return match ($tipoLegacy) {
            'R'     => 'riscaldamento',
            default => 'custom',
        };
    }
}