<?php

namespace App\Console\Commands;

use Illuminate\Console\Attributes\Description;
use Illuminate\Console\Attributes\Signature;
use Illuminate\Console\Command;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Throwable;

#[Signature('postcodes:import {--file= : Path to ONSPD CSV file}')]
#[Description('Import UK postcodes (ONSPD) into the local postcodes and outcodes tables')]
final class ImportPostcodes extends Command
{
    private const int CHUNK_SIZE = 1000;

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

        if ($file === null || ! is_readable($file)) {
            $this->error('--file is required and must be a readable path to an ONSPD CSV.');

            return self::FAILURE;
        }

        $handle = fopen($file, 'r');

        if ($handle === false) {
            $this->error("Unable to open {$file}.");

            return self::FAILURE;
        }

        $header = fgetcsv($handle);

        if ($header === false) {
            $this->error('CSV is empty.');
            fclose($handle);

            return self::FAILURE;
        }

        $headerCounts = array_count_values(array_map('strtolower', $header));
        $columns = array_change_key_case(array_flip($header), CASE_LOWER);

        $pcdColumn = null;

        foreach (['pcd', 'pcds', 'pcd7', 'pcd8'] as $candidate) {
            if (isset($columns[$candidate])) {
                $pcdColumn = $candidate;
                break;
            }
        }

        if ($pcdColumn === null) {
            $this->error('Missing required postcode column (expected one of: pcd, pcds, pcd7, pcd8).');
            fclose($handle);

            return self::FAILURE;
        }

        foreach ([$pcdColumn, 'lat', 'long'] as $required) {
            if (($headerCounts[$required] ?? 0) > 1) {
                $this->error("Column '{$required}' appears more than once — refusing to import.");
                fclose($handle);

                return self::FAILURE;
            }
        }

        foreach (['lat', 'long'] as $required) {
            if (! isset($columns[$required])) {
                $this->error("Missing required column '{$required}'.");
                fclose($handle);

                return self::FAILURE;
            }
        }

        $hasDoterm = isset($columns['doterm']);

        // Stream into a staging table first. Only swap into the live
        // postcodes / outcodes tables once the full CSV has been consumed —
        // a mid-stream failure leaves production data untouched.
        Schema::dropIfExists('postcodes_staging');
        Schema::create('postcodes_staging', function (Blueprint $table): void {
            $table->string('postcode', 7);
            $table->string('outcode', 4);
            $table->decimal('lat', 10, 7);
            $table->decimal('lng', 10, 7);
        });

        $buffer = [];
        $imported = 0;

        try {
            while (($row = fgetcsv($handle)) !== false) {
                if ($hasDoterm && trim((string) ($row[$columns['doterm']] ?? '')) !== '') {
                    continue;
                }

                $lat = trim((string) ($row[$columns['lat']] ?? ''));
                $lng = trim((string) ($row[$columns['long']] ?? ''));

                if ($lat === '' || $lng === '') {
                    continue;
                }

                $pcd = strtoupper(preg_replace('/\s+/', '', (string) $row[$columns[$pcdColumn]]));

                if ($pcd === '' || strlen($pcd) < 5) {
                    continue;
                }

                $buffer[] = [
                    'postcode' => $pcd,
                    'outcode' => substr($pcd, 0, strlen($pcd) - 3),
                    'lat' => (float) $lat,
                    'lng' => (float) $lng,
                ];

                if (count($buffer) >= self::CHUNK_SIZE) {
                    DB::table('postcodes_staging')->insert($buffer);
                    $imported += count($buffer);
                    $buffer = [];
                }
            }

            if ($buffer !== []) {
                DB::table('postcodes_staging')->insert($buffer);
                $imported += count($buffer);
            }

            // Swap: empty live tables, copy from staging, derive outcodes.
            DB::table('outcodes')->truncate();
            DB::table('postcodes')->truncate();
            DB::statement(
                'INSERT INTO postcodes (postcode, outcode, lat, lng)
                 SELECT postcode, outcode, lat, lng FROM postcodes_staging'
            );
            DB::statement(
                'INSERT INTO outcodes (outcode, lat, lng)
                 SELECT outcode, AVG(lat), AVG(lng) FROM postcodes GROUP BY outcode'
            );
        } catch (Throwable $e) {
            $this->error('Import failed — live tables left untouched: '.$e->getMessage());

            return self::FAILURE;
        } finally {
            fclose($handle);
            Schema::dropIfExists('postcodes_staging');
        }

        $this->info("Imported {$imported} postcodes.");
        $this->info('Derived '.DB::table('outcodes')->count().' outcode centroids.');

        return self::SUCCESS;
    }
}