<?php

namespace Database\Seeders;

use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;

class Nuts3PostalCodesSeeder extends Seeder
{
    /**
     * Run the database seeds.
     */
    public function run(): void
    {
        $csvFile = base_path('json/PCODE_2025_NUTS-2024_v2.0.csv');

        if (!File::exists($csvFile)) {
            $this->command->error("CSV file not found: {$csvFile}");
            return;
        }

        $this->command->info('Reading CSV file...');

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

        // Skip header row
        fgetcsv($handle, 0, ';');

        $batch = [];
        $batchSize = 1000;
        $count = 0;

        DB::table('nuts3_postal_codes')->truncate();

        while (($row = fgetcsv($handle, 0, ';')) !== false) {
            if (count($row) < 2) {
                continue;
            }

            $nuts3Code = trim($row[0], " \t\n\r\0\x0B'");
            $postalCode = trim($row[1], " \t\n\r\0\x0B'");
            $postalCode = preg_replace('/\s+/', '', $postalCode);

            // Extract country code (first 2 characters of NUTS3 code)
            $countryCode = substr($nuts3Code, 0, 2);

            // Specific country cases rules:
            switch ($countryCode) {
                case 'IE': // Ireland - only first 3 characters of postal code are relevant
                    $postalCode = substr($postalCode, 0, 3);
                    break;
                case 'PT': // Portugal - only first 8 characters of postal code are relevant
                    $postalCode = substr($postalCode, 0, 8);
                    break;
                // Add more country-specific rules as needed
            }

            $batch[] = [
                'nuts3_code' => $nuts3Code,
                'postal_code' => $postalCode,
                'country_code' => $countryCode,
            ];

            $count++;

            if (count($batch) >= $batchSize) {
                DB::table('nuts3_postal_codes')->insert($batch);
                $batch = [];
                $this->command->info("Inserted {$count} records...");
            }
        }

        // Insert remaining records
        if (!empty($batch)) {
            DB::table('nuts3_postal_codes')->insert($batch);
        }

        fclose($handle);

        $this->command->info("Successfully imported {$count} postal codes.");
    }
}