<?php
namespace App\Services\GesconImport;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

class StabileEnrichmentService
{
    /**
     * Enrich stabile domain row with additional legacy metadata (catasto + banca inline) reading Stabili.mdb.
     * Non distruttivo: aggiorna solo colonne esistenti e vuote.
     */
    public function enrich(string $legacyCode, string $basePath = '/mnt/gescon-archives/gescon'): array
    {
        $result = ['legacy' => $legacyCode, 'catasto' => false, 'banca_inline' => false, 'mdb_found' => false];
        if (! $legacyCode) {
            return $result;
        }

        if (! Schema::hasTable('stabili')) {
            return $result;
        }

        // Trova record stabile di dominio
        $stabile = DB::table('stabili')->where(function ($q) use ($legacyCode) {
            if (Schema::hasColumn('stabili', 'codice_stabile')) {
                $q->orWhere('codice_stabile', $legacyCode);
            }

            $q->orWhere('denominazione', $legacyCode); // fallback
        })->first();
        if (! $stabile) {
            return $result;
        }

        $mdbPath = $this->resolveMdbPath($basePath);
        if (! is_file($mdbPath) || ! is_readable($mdbPath)) {
            return $result;
        }

        $result['mdb_found'] = true;

        $row = $this->extractRow($mdbPath, $legacyCode);
        if (! $row) {
            return $result;
        }

        $updates = [];
        // Catasto fields mapping heuristic (legacy → logical field)
        $mapCatasto = [
            'foglio'               => ['foglio', 'ac_foglio', 'foglio_catasto'],
            'particella'           => ['particella', 'ac_particella', 'ac_partic1', 'ac_partic_1', 'ac_particella1', 'ac_particella_1', 'mappale', 'catasto_particella'],
            'subalterno'           => ['sub', 'subalterno', 'ac_sub', 'ac_subalterno'],
            'sezione'              => ['sezione', 'ac_sezione', 'sez'],
            'categoria'            => ['categoria_catastale', 'categoria', 'cat_categoria'],
            'classe'               => ['classe', 'cat_classe'],
            'consistenza'          => ['consistenza', 'cat_consistenza'],
            'rendita_catastale'    => ['rendita_catastale', 'rendita', 'cat_rendita'],
            'superficie_catastale' => ['superficie_catastale', 'superficie'],
        ];
        $targetColumnAliases = [
            'foglio'               => ['foglio', 'foglio_catasto'],
            'particella'           => ['particella', 'particella_catasto', 'mappale'],
            'subalterno'           => ['subalterno'],
            'sezione'              => ['sezione'],
            'categoria'            => ['categoria_catastale', 'categoria'],
            'classe'               => ['classe', 'classe_catastale'],
            'consistenza'          => ['consistenza', 'consistenza_catastale'],
            'rendita_catastale'    => ['rendita_catastale'],
            'superficie_catastale' => ['superficie_catastale'],
        ];
        foreach ($mapCatasto as $logical => $candidates) {
            $column = $this->resolveStabileColumn($targetColumnAliases[$logical] ?? [$logical]);
            if (! $column) {
                continue;
            }
            $current = $stabile->$column ?? null;
            if (! empty($current) && $current !== 'ND') {
                continue;
            }
            foreach ($candidates as $cand) {
                $value = $row[$cand] ?? null;
                if ($value === null || $value === '') {
                    continue;
                }
                $updates[$column] = $value;
                break;
            }
        }
        if (! empty(array_intersect(array_keys($updates), array_filter(array_map(function ($aliases) {
            foreach ($aliases as $alias) {
                if (Schema::hasColumn('stabili', $alias)) {
                    return $alias;
                }
            }
            return null;
        }, $targetColumnAliases))))) {
            $result['catasto'] = true;
        }

        // Codice catastale del comune (H501 per Roma, ecc.)
        $codiceComuneColumn = $this->resolveStabileColumn(['codice_comune_catasto', 'codice_catastale_comune']);
        if ($codiceComuneColumn && empty($stabile->$codiceComuneColumn)) {
            $cc = $row['cod_comune'] ?? ($row['codice_catastale'] ?? ($row['cod_catastale'] ?? null));
            if ($cc && is_string($cc) && trim($cc) !== '') {
                $updates[$codiceComuneColumn] = strtoupper(trim($cc));
                $result['catasto']            = true;
            }
        }

        // Inline banca (solo se tabella banche non esiste o manca un record principale)
        $ibanColumn = $this->resolveStabileColumn(['iban_condominio', 'iban_principale', 'iban']);
        if ($ibanColumn) {
            $currentIban = $stabile->$ibanColumn ?? null;
            if (empty($currentIban)) {
                $ibanKey = $this->firstAvailable($row, ['iban_condominio', 'iban', 'iban1', 'iban_1']);
                if ($ibanKey && ! empty($row[$ibanKey])) {
                    $updates[$ibanColumn]   = trim($row[$ibanKey]);
                    $result['banca_inline'] = true;
                }
            }
        }

        if (! empty($updates)) {
            $updates['updated_at'] = now();
            DB::table('stabili')->where('id', $stabile->id)->update($updates);
        }
        return $result + ['updated' => array_keys($updates)];
    }

    private function extractRow(string $mdbPath, string $legacyCode): ?array
    {
        try {
            $binTables = trim((string) @shell_exec('command -v mdb-tables')) ?: '/usr/bin/mdb-tables';
            $binExport = trim((string) @shell_exec('command -v mdb-export')) ?: '/usr/bin/mdb-export';
            if (! is_file($mdbPath) || ! is_readable($mdbPath)) {
                return null;
            }

            $tables          = @shell_exec(sprintf('%s -1 %s 2>/dev/null', escapeshellarg($binTables), escapeshellarg($mdbPath))) ?: '';
            $candidateTables = [];
            foreach (['Stabili', 'stabili', 'Condomin', 'condomin'] as $t) {
                if (preg_match('/(^|\n)' . preg_quote($t, '/') . '(\n|$)/', $tables)) {
                    $candidateTables[] = $t;
                }

            }
            $candidateTables = array_unique($candidateTables);
            foreach ($candidateTables as $table) {
                $tmp = tempnam(sys_get_temp_dir(), 'mdbcsv_');
                $cmd = sprintf(
                    '%s -D %s -d %s -q %s -R "\\n" %s %s > %s 2>/dev/null',
                    escapeshellarg($binExport),
                    escapeshellarg('%Y-%m-%d'),
                    escapeshellarg('|'),
                    escapeshellarg('"'),
                    escapeshellarg($mdbPath),
                    escapeshellarg($table),
                    escapeshellarg($tmp)
                );
                @shell_exec($cmd);
                if (! is_file($tmp) || filesize($tmp) === 0) {
                    @unlink($tmp);
                    continue;
                }
                $fh = fopen($tmp, 'r');
                if (! $fh) {
                    @unlink($tmp);
                    continue;
                }
                $headers = fgetcsv($fh, 0, '|');
                if (! $headers) {
                    fclose($fh);
                    @unlink($tmp);
                    continue;
                }
                $headers = array_map(fn($h) => strtolower(trim((string) $h)), $headers);
                while (($cols = fgetcsv($fh, 0, '|')) !== false) {
                    if (count($cols) !== count($headers)) {
                        continue;
                    }

                    $row = @array_combine($headers, $cols) ?: [];
                    if (! $row) {
                        continue;
                    }

                    $codeFields = ['cod_stabile', 'codice', 'codice_stabile', 'stabile', 'cod', 'nome_directory', 'codice_directory', 'internet_cod_stab'];
                    foreach ($codeFields as $cf) {
                        if (isset($row[$cf]) && trim($row[$cf]) === $legacyCode) {
                            fclose($fh);
                            @unlink($tmp);
                            return $row;
                        }
                    }
                }
                fclose($fh);
                @unlink($tmp);
            }
        } catch (\Throwable $e) {
            Log::warning('StabileEnrichmentService extractRow error: ' . $e->getMessage());
        }
        return null;
    }

    private function firstAvailable(array $row, array $keys): ?string
    {
        foreach ($keys as $k) {
            if (isset($row[$k]) && trim((string) $row[$k]) !== '') {
                return $k;
            }

        }
        return null;
    }

    private function resolveStabileColumn(array $candidates): ?string
    {
        foreach ($candidates as $col) {
            if (Schema::hasColumn('stabili', $col)) {
                return $col;
            }
        }
        return null;
    }

    private function resolveMdbPath(string $basePath): string
    {
        $basePath   = rtrim($basePath, '/');
        $candidates = [
            $basePath . '/dbc/Stabili.mdb',
            $basePath . '/dbc/stabili.mdb',
            $basePath . '/Stabili.mdb',
            $basePath . '/stabili.mdb',
        ];

        foreach ($candidates as $candidate) {
            if (is_file($candidate) && is_readable($candidate)) {
                return $candidate;
            }
        }

        return $candidates[0];
    }
}