<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportBoralesgamuwaBanks extends Command
{
    protected $signature = 'boralesgamuwa:import-banks
                            {--step=all : Which step to run: banks|branches|all}
                            {--force : Re-import even if records exist}';

    protected $description = 'Import banks and bank branches from boralesgamuwa.xlsx';

    private int $branchId  = 1; // Head Office
    private int $countryId = 1; // Sri Lanka

    public function handle(): int
    {
        $step = $this->option('step');

        if ($step === 'banks' || $step === 'all') {
            $this->importBanks();
        }

        if ($step === 'branches' || $step === 'all') {
            $this->importBankBranches();
        }

        return Command::SUCCESS;
    }

    // ─────────────────────────────────────────────
    // Import Banks
    // ─────────────────────────────────────────────

    private function importBanks(): void
    {
        $this->info('Importing banks…');
        $spreadsheet = IOFactory::load(public_path('boralesgamuwa.xlsx'));
        $sheet = $spreadsheet->getSheetByName('bank');

        $inserted = 0;
        $skipped  = 0;

        $bar = $this->output->createProgressBar($sheet->getHighestRow() - 1);
        $bar->start();

        for ($row = 2; $row <= $sheet->getHighestRow(); $row++) {
            $rawName   = trim((string) $sheet->getCell('A' . $row)->getValue());
            $code      = trim((string) $sheet->getCell('B' . $row)->getValue());
            $swiftCode = trim((string) $sheet->getCell('C' . $row)->getValue());

            if (! $rawName) {
                $bar->advance();
                continue;
            }

            $displayName = $this->cleanBankName($rawName);

            if (! $this->option('force') && DB::table('banks')->where('swift_code', $swiftCode)->exists()) {
                $skipped++;
                $bar->advance();
                continue;
            }

            DB::table('banks')->updateOrInsert(
                ['swift_code' => $swiftCode],
                [
                    'branch_id'  => $this->branchId,
                    'name'       => $displayName,
                    'code'       => $code,
                    'country_id' => $this->countryId,
                    'swift_code' => $swiftCode,
                    'status'     => 1,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]
            );
            $inserted++;
            $bar->advance();
        }

        $bar->finish();
        $this->newLine();
        $this->info("Banks → inserted/updated: {$inserted} | skipped: {$skipped}");
    }

    // ─────────────────────────────────────────────
    // Import Bank Branches
    // ─────────────────────────────────────────────

    private function importBankBranches(): void
    {
        $this->info('Building city cache…');

        // Build city lookup: lowercase name → city id (prefer exact match)
        $cityCache = [];
        DB::table('cities')->get(['id', 'name'])->each(function ($c) use (&$cityCache) {
            $key = strtolower(trim($c->name));
            if (! isset($cityCache[$key])) {
                $cityCache[$key] = $c->id;
            }
        });

        // Build bank lookup: swift_code → bank id
        $bankCache = [];
        DB::table('banks')->get(['id', 'swift_code'])->each(function ($b) use (&$bankCache) {
            $bankCache[trim($b->swift_code)] = $b->id;
        });

        // Fallback city for unresolvable branch names (Colombo)
        $fallbackCityId = $cityCache['colombo'] ?? DB::table('cities')->first()->id;

        // Get or create a dedicated fallback city "Other"
        $otherCityId = $this->ensureCity('Other', $cityCache, $fallbackCityId);

        $this->info('Parsing branch sheet…');
        $spreadsheet = IOFactory::load(public_path('boralesgamuwa.xlsx'));
        $sheet       = $spreadsheet->getSheetByName('branch');
        $maxRow      = $sheet->getHighestRow();
        $maxCol      = $sheet->getHighestColumn();

        // Row 1 = bank SWIFT codes per column
        $swiftMap = [];
        foreach ($sheet->getRowIterator(1, 1) as $r) {
            foreach ($r->getCellIterator('A', $maxCol) as $c) {
                $v = trim((string) $c->getValue());
                if ($v) $swiftMap[$c->getColumn()] = $v;
            }
        }

        $total    = 0;
        $inserted = 0;
        $skipped  = 0;
        $unmatched = [];

        // Count total cells to process for progress bar
        $totalCells = 0;
        for ($row = 2; $row <= $maxRow; $row++) {
            foreach ($swiftMap as $col => $swift) {
                $val = trim((string) $sheet->getCell($col . $row)->getValue());
                if ($val) $totalCells++;
            }
        }

        $this->info("Processing {$totalCells} bank branch entries…");
        $bar = $this->output->createProgressBar($totalCells);
        $bar->start();

        for ($row = 2; $row <= $maxRow; $row++) {
            foreach ($swiftMap as $col => $swift) {
                $cell = trim((string) $sheet->getCell($col . $row)->getValue());

                if (! $cell) continue;

                // Parse "CityName-BranchCode"
                $lastDash   = strrpos($cell, '-');
                if ($lastDash !== false) {
                    $cityPart = trim(substr($cell, 0, $lastDash));
                    $code     = trim(substr($cell, $lastDash + 1));
                } else {
                    $cityPart = $cell;
                    $code     = '';
                }

                $bankId = $bankCache[$swift] ?? null;

                // Fix BANK719 → BANK7719 (typo in Excel row 1)
                if (! $bankId && $swift === 'BANK719') {
                    $bankId = $bankCache['BANK7719'] ?? null;
                }

                if (! $bankId) {
                    $bar->advance();
                    continue;
                }

                // Skip duplicates
                if (! $this->option('force') &&
                    DB::table('bank_branches')
                        ->where('bank_id', $bankId)
                        ->where('name', $cell)
                        ->exists()
                ) {
                    $skipped++;
                    $bar->advance();
                    continue;
                }

                // Resolve city_id
                $cityId = $this->resolveCity($cityPart, $cityCache, $otherCityId, $unmatched);

                DB::table('bank_branches')->insert([
                    'bank_id'      => $bankId,
                    'name'         => $cell,
                    'branch_code'  => $code ?: null,
                    'city_id'      => $cityId,
                    'routing_code' => null,
                    'status'       => 1,
                    'created_at'   => now(),
                    'updated_at'   => now(),
                ]);

                $inserted++;
                $total++;
                $bar->advance();
            }
        }

        $bar->finish();
        $this->newLine();
        $this->info("Bank branches → inserted: {$inserted} | skipped (dup): {$skipped}");

        $uniqueUnmatched = array_unique($unmatched);
        if ($uniqueUnmatched) {
            $this->warn(count($uniqueUnmatched) . ' city names could not be matched (used "Other" city):');
            foreach (array_slice($uniqueUnmatched, 0, 30) as $u) {
                $this->line("  {$u}");
            }
            if (count($uniqueUnmatched) > 30) {
                $this->line('  … and ' . (count($uniqueUnmatched) - 30) . ' more.');
            }
        }
    }

    // ─────────────────────────────────────────────
    // Helpers
    // ─────────────────────────────────────────────

    /**
     * Resolve a city part string to a city_id.
     * Tries exact → partial → fallback.
     */
    private function resolveCity(string $cityPart, array &$cityCache, int $fallbackId, array &$unmatched): int
    {
        $key = strtolower($cityPart);

        // 1. Exact match
        if (isset($cityCache[$key])) {
            return $cityCache[$key];
        }

        // 2. Try stripping common suffixes / prefixes
        $normalized = preg_replace('/\s+(sp|csp|pbc|2nd|city|town|main|bazzar|bazaar|branch|second|new town|super|minicom|ext\.office|ext|no\.\d|#?\d+)$/i', '', $cityPart);
        $normalized = trim($normalized);
        $normKey    = strtolower($normalized);

        if ($normKey !== $key && isset($cityCache[$normKey])) {
            return $cityCache[$normKey];
        }

        // 3. Partial scan – city name starts with the extracted part
        foreach ($cityCache as $cachedName => $cachedId) {
            if (str_starts_with($cachedName, $normKey) || str_starts_with($normKey, $cachedName)) {
                $cityCache[$key] = $cachedId; // cache for next time
                return $cachedId;
            }
        }

        // 4. Unresolved – record it and use fallback
        $unmatched[] = $cityPart;
        $cityCache[$key] = $fallbackId;
        return $fallbackId;
    }

    /**
     * Ensure a city named $name exists and return its ID.
     */
    private function ensureCity(string $name, array &$cityCache, int $fallbackStateId): int
    {
        $key = strtolower($name);
        if (isset($cityCache[$key])) {
            return $cityCache[$key];
        }

        // Get or create under Western Province (id=1 state if exists)
        $stateId = DB::table('states')->where('country_id', $this->countryId)->value('id') ?? 1;

        $id = DB::table('cities')->insertGetId([
            'country_id' => $this->countryId,
            'state_id'   => $stateId,
            'name'       => $name,
            'status'     => 1,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

        $cityCache[$key] = $id;
        return $id;
    }

    /**
     * Convert Excel raw bank name to a clean display name.
     * "Bank_of_Ceylon_7010" → "Bank of Ceylon"
     */
    private function cleanBankName(string $raw): string
    {
        // Remove trailing _XXXX numeric code
        $clean = preg_replace('/_\d+$/', '', $raw);
        // Replace underscores with spaces
        $clean = str_replace('_', ' ', $clean);
        // Trim
        return trim($clean);
    }
}
