<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Symfony\Component\Process\Process;

class ImportCondominMirror0021 extends Command
{
    protected $signature = 'gescon:import-mirror-0021
                            {--stabile=0021 : Codice dello stabile pilot}
                            {--refresh : Svuota la tabella mirror prima di importare}';

    protected $description = 'Importa in modalità append-only lossless la tabella condomin dello stabile 0021 nella staging mirror (condomin_mirror)';

    public function handle(): int
    {
        $stabileCode = str_pad((string) $this->option('stabile'), 4, '0', STR_PAD_LEFT);
        $ammCode = 'HWFGITXK';

        if ($this->option('refresh')) {
            DB::connection('gescon_import')->table('condomin_mirror')->truncate();
            $this->info("Tabella gescon_import.condomin_mirror svuotata.");
        }

        $baseDirs = [
            "/mnt/gescon-archives/gescon/{$stabileCode}",
            storage_path("app/amministratori/{$ammCode}/legacy/{$stabileCode}")
        ];

        $targetDir = null;
        foreach ($baseDirs as $dir) {
            if (is_dir($dir)) {
                $targetDir = $dir;
                break;
            }
        }

        if (! $targetDir) {
            $this->error("Directory MDB non trovata per lo stabile {$stabileCode}");
            return Command::FAILURE;
        }

        $fileConfigs = [];
        $globMatches = glob("{$targetDir}/*/singolo_anno.mdb");
        if (is_array($globMatches)) {
            sort($globMatches);
            foreach ($globMatches as $mdbPath) {
                $year = basename(dirname($mdbPath));
                $rel  = "{$stabileCode}/{$year}/singolo_anno.mdb";
                $fileConfigs[$year] = ['year' => $year, 'rel' => $rel, 'path' => $mdbPath];
            }
        }

        $perFileCounts = [];
        $totalMdbRows = 0;

        foreach ($fileConfigs as $key => $cfg) {
            if (! file_exists($cfg['path'])) {
                $this->warn("File non trovato: {$cfg['path']}");
                continue;
            }

            $rows = $this->extractMdbCondominRows($cfg['path']);
            $count = count($rows);
            $totalMdbRows += $count;
            $perFileCounts[$key] = [
                'rel' => $cfg['rel'],
                'year' => $cfg['year'],
                'mdb_rows' => $count,
                'imported' => 0
            ];

            $rowNum = 0;
            foreach ($rows as $r) {
                $rowNum++;
                $provHash = hash('sha256', "{$ammCode}|{$stabileCode}|{$cfg['rel']}|{$cfg['year']}|condomin|{$rowNum}");

                // Extract real MDB interno column (int or interno) without reconstructing from id_cond/cod_cond
                $rawInterno = isset($r['int']) ? (string)$r['int'] : (isset($r['interno']) ? (string)$r['interno'] : '');
                $rawScala = isset($r['scala']) ? (string)$r['scala'] : '';
                $rawPiano = isset($r['piano']) ? (string)$r['piano'] : '';

                $idCond = isset($r['id_cond']) ? (string)$r['id_cond'] : null;
                $codCond = isset($r['cod_cond']) ? (string)$r['cod_cond'] : null;
                $cognome = isset($r['cognome']) ? (string)$r['cognome'] : null;
                $nome = isset($r['nome']) ? (string)$r['nome'] : null;
                $nomCond = isset($r['nom_cond']) ? (string)$r['nom_cond'] : null;
                $codFisc = isset($r['cond_cod_fisc']) ? (string)$r['cond_cod_fisc'] : (isset($r['codice_fiscale']) ? (string)$r['codice_fiscale'] : null);
                $tel = isset($r['tel1']) ? (string)$r['tel1'] : (isset($r['telefono']) ? (string)$r['telefono'] : null);
                $cell = isset($r['cell_cond']) ? (string)$r['cell_cond'] : (isset($r['cellulare']) ? (string)$r['cellulare'] : null);
                $email = isset($r['e_mail_condomino']) ? (string)$r['e_mail_condomino'] : (isset($r['email']) ? (string)$r['email'] : null);
                $pec = isset($r['pec_condomino']) ? (string)$r['pec_condomino'] : (isset($r['pec']) ? (string)$r['pec'] : null);

                $proprietario = isset($r['proprietario']) ? (string)$r['proprietario'] : null;
                $inquilino = isset($r['inquilino']) ? (string)$r['inquilino'] : (isset($r['inquil_nome']) ? (string)$r['inquil_nome'] : null);

                $subPrima = isset($r['subentro_prima_cera']) ? (string)$r['subentro_prima_cera'] : null;
                $subAdesso = isset($r['subentro_adesso_ce']) ? (string)$r['subentro_adesso_ce'] : null;
                $subDal = isset($r['subentrato_dal']) ? (string)$r['subentrato_dal'] : null;
                $attivoAl = isset($r['attivo_fino_al']) ? (string)$r['attivo_fino_al'] : null;
                $inquilDal = isset($r['inquil_dal']) ? (string)$r['inquil_dal'] : null;
                $inquilAl = isset($r['inquil_al']) ? (string)$r['inquil_al'] : null;

                $dirReale = isset($r['diritto_reale']) ? (string)$r['diritto_reale'] : null;
                $dirGodimento = isset($r['diritto_godimento']) ? (string)$r['diritto_godimento'] : null;
                $percDir = isset($r['perc_diritto_reale']) ? (string)$r['perc_diritto_reale'] : null;

                $millProp = isset($r['millesimi_proprieta']) && is_numeric($r['millesimi_proprieta']) ? (float)$r['millesimi_proprieta'] : null;
                $millRisc = isset($r['millesimi_riscaldamento']) && is_numeric($r['millesimi_riscaldamento']) ? (float)$r['millesimi_riscaldamento'] : null;
                $millAsc = isset($r['millesimi_ascensore']) && is_numeric($r['millesimi_ascensore']) ? (float)$r['millesimi_ascensore'] : null;

                $record = [
                    'amministratore_code' => $ammCode,
                    'cod_stabile' => $stabileCode,
                    'source_file' => $cfg['rel'],
                    'source_year' => $cfg['year'],
                    'source_table' => 'condomin',
                    'source_row' => $rowNum,
                    'provenance_hash' => $provHash,
                    'id_cond' => $idCond,
                    'cod_cond' => $codCond,
                    'scala' => trim($rawScala) !== '' ? trim($rawScala) : null,
                    'interno' => trim($rawInterno) !== '' ? trim($rawInterno) : null,
                    'piano' => trim($rawPiano) !== '' ? trim($rawPiano) : null,
                    'cognome' => $cognome,
                    'nome' => $nome,
                    'nom_cond' => $nomCond,
                    'codice_fiscale' => $codFisc,
                    'telefono' => $tel,
                    'cellulare' => $cell,
                    'email' => $email,
                    'pec' => $pec,
                    'millesimi_proprieta' => $millProp,
                    'millesimi_riscaldamento' => $millRisc,
                    'millesimi_ascensore' => $millAsc,
                    'proprietario' => $proprietario,
                    'inquilino' => $inquilino,
                    'subentro_prima_cera' => $subPrima,
                    'subentro_adesso_ce' => $subAdesso,
                    'subentrato_dal' => $subDal,
                    'attivo_fino_al' => $attivoAl,
                    'inquil_dal' => $inquilDal,
                    'inquil_al' => $inquilAl,
                    'diritto_reale' => $dirReale,
                    'diritto_godimento' => $dirGodimento,
                    'perc_diritto_reale' => $percDir,
                    'legacy_payload' => json_encode($r, JSON_UNESCAPED_UNICODE),
                    'created_at' => now(),
                    'updated_at' => now(),
                ];

                DB::connection('gescon_import')->table('condomin_mirror')->updateOrInsert(
                    ['provenance_hash' => $provHash],
                    $record
                );

                $perFileCounts[$key]['imported']++;
            }

            $unitMapByIdCond = [];
            $unitMapByCodCond = [];

            foreach ($rows as $r) {
                $rawInterno = isset($r['int']) ? (string)$r['int'] : (isset($r['interno']) ? (string)$r['interno'] : '');
                $rawScala   = isset($r['scala']) ? (string)$r['scala'] : '';
                $rawPiano   = isset($r['piano']) ? (string)$r['piano'] : '';
                $idCond     = isset($r['id_cond']) ? (string)$r['id_cond'] : null;
                $codCond    = isset($r['cod_cond']) ? (string)$r['cod_cond'] : null;

                if ($idCond) {
                    $unitMapByIdCond[$idCond] = ['scala' => $rawScala, 'interno' => $rawInterno, 'piano' => $rawPiano];
                }
                if ($codCond) {
                    $unitMapByCodCond[$codCond] = ['scala' => $rawScala, 'interno' => $rawInterno, 'piano' => $rawPiano];
                }
            }

            // Extract and mirror comproprietari if present in MDB
            $compRows = $this->extractMdbTableRows($cfg['path'], 'comproprietari');
            $compNum = 0;
            foreach ($compRows as $c) {
                $compNum++;
                $provHash = hash('sha256', "{$ammCode}|{$stabileCode}|{$cfg['rel']}|{$cfg['year']}|comproprietari|{$compNum}");

                $cIdCond  = isset($c['id_cond']) ? (string)$c['id_cond'] : null;
                $cCodCond = isset($c['ex_cod_cond']) ? (string)$c['ex_cod_cond'] : (isset($c['cod_cond']) ? (string)$c['cod_cond'] : null);

                $unitInfo = ($cIdCond && isset($unitMapByIdCond[$cIdCond]))
                    ? $unitMapByIdCond[$cIdCond]
                    : null;

                $rawScala   = $unitInfo['scala'] ?? (isset($c['ex_scala']) ? (string)$c['ex_scala'] : '');
                $rawInterno = $unitInfo['interno'] ?? (isset($c['ex_int']) ? (string)$c['ex_int'] : '');
                $rawPiano   = $unitInfo['piano'] ?? '';

                $nomCond = isset($c['nom_cond']) ? (string)$c['nom_cond'] : (isset($c['Detraz_cess_nome']) ? (string)$c['Detraz_cess_nome'] : null);
                $codFisc = isset($c['Cond_cod_fisc']) ? (string)$c['Cond_cod_fisc'] : (isset($c['Detraz_cess_CF']) ? (string)$c['Detraz_cess_CF'] : null);
                $tel     = isset($c['tel1']) ? (string)$c['tel1'] : null;
                $cell    = isset($c['Cell_cond']) ? (string)$c['Cell_cond'] : null;
                $email   = isset($c['E_mail_condomino']) ? (string)$c['E_mail_condomino'] : null;
                $pec     = isset($c['PEC_compr']) ? (string)$c['PEC_compr'] : null;
                $dirReale= isset($c['Diritto_reale']) && trim((string)$c['Diritto_reale']) !== '' ? (string)$c['Diritto_reale'] : (isset($c['Descriz']) ? (string)$c['Descriz'] : 'Comproprietà');
                $percDir = isset($c['Perc_Diritto_reale']) && is_numeric($c['Perc_Diritto_reale']) ? (string)$c['Perc_Diritto_reale'] : null;

                $recordComp = [
                    'amministratore_code'     => $ammCode,
                    'cod_stabile'             => $stabileCode,
                    'source_file'             => $cfg['rel'],
                    'source_year'             => $cfg['year'],
                    'source_table'            => 'comproprietari',
                    'source_row'              => $compNum,
                    'provenance_hash'         => $provHash,
                    'id_cond'                 => $cIdCond,
                    'cod_cond'                => $cCodCond,
                    'scala'                   => trim($rawScala) !== '' ? trim($rawScala) : null,
                    'interno'                 => trim($rawInterno) !== '' ? trim($rawInterno) : null,
                    'piano'                   => trim($rawPiano) !== '' ? trim($rawPiano) : null,
                    'cognome'                 => null,
                    'nome'                    => null,
                    'nom_cond'                => $nomCond,
                    'codice_fiscale'          => $codFisc,
                    'telefono'                => $tel,
                    'cellulare'               => $cell,
                    'email'                   => $email,
                    'pec'                     => $pec,
                    'millesimi_proprieta'     => null,
                    'millesimi_riscaldamento' => null,
                    'millesimi_ascensore'     => null,
                    'proprietario'            => $nomCond,
                    'inquilino'               => null,
                    'subentro_prima_cera'     => null,
                    'subentro_adesso_ce'      => null,
                    'subentrato_dal'          => null,
                    'attivo_fino_al'          => null,
                    'inquil_dal'              => null,
                    'inquil_al'               => null,
                    'diritto_reale'           => $dirReale,
                    'diritto_godimento'       => null,
                    'perc_diritto_reale'      => $percDir,
                    'legacy_payload'          => json_encode($c, JSON_UNESCAPED_UNICODE),
                    'created_at'              => now(),
                    'updated_at'              => now(),
                ];

                DB::connection('gescon_import')->table('condomin_mirror')->updateOrInsert(
                    ['provenance_hash' => $provHash],
                    $recordComp
                );
            }
        }

        $stagingRows = DB::connection('gescon_import')->table('condomin_mirror')->where('cod_stabile', $stabileCode)->count();

        $duplicates = DB::connection('gescon_import')->select("
            SELECT provenance_hash, COUNT(*) as cnt
            FROM condomin_mirror
            WHERE cod_stabile = ?
            GROUP BY provenance_hash
            HAVING cnt > 1
        ", [$stabileCode]);

        $duplicateCount = count($duplicates);
        $collisionCount = $stagingRows < $totalMdbRows ? ($totalMdbRows - $stagingRows) : 0;

        $this->info("=== FASE 1A MIRROR STAGING 0021 COMPLETATA ===");
        $this->line("MDB_ROWS_TOTAL: {$totalMdbRows}");
        $this->line("STAGING_ROWS: {$stagingRows}");
        $this->line("COLLISION_COUNT: {$collisionCount}");
        $this->line("DUPLICATE_PROVENANCE_COUNT: {$duplicateCount}");

        foreach ($perFileCounts as $k => $info) {
            $this->line("PER_FILE: {$info['rel']} | YEAR: {$info['year']} | MDB: {$info['mdb_rows']} | STAGING: {$info['imported']}");
        }

        return Command::SUCCESS;
    }

    private function extractMdbCondominRows(string $mdbPath): array
    {
        return $this->extractMdbTableRows($mdbPath, 'condomin');
    }

    private function extractMdbTableRows(string $mdbPath, string $tableName): array
    {
        $proc = new Process(['mdb-export', $mdbPath, $tableName]);
        $proc->run();

        if (! $proc->isSuccessful()) {
            return [];
        }

        $csvContent = $proc->getOutput();
        $stream = fopen('php://memory', 'r+');
        fwrite($stream, $csvContent);
        rewind($stream);

        $header = fgetcsv($stream);
        if (! $header) {
            fclose($stream);
            return [];
        }

        $rows = [];
        while (($data = fgetcsv($stream)) !== false) {
            if (count($data) === count($header)) {
                $rows[] = array_combine($header, $data);
            }
        }

        fclose($stream);
        return $rows;
    }
}