<?php

namespace App\Console\Commands;

use App\Models\Currency;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;

class SyncCurrencies extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:sync-currencies {--force : Force sync even if API call fails}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Sync all currencies and country details from openexchangerates.org and cache them in Redis.';

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        ini_set('memory_limit', '512M');
        $appId = config('services.openexchangerates.app_id');

        $this->info('Fetching currencies from Open Exchange Rates...');

        $namesResponse = null;
        $ratesResponse = null;

        if ($appId) {
            try {
                $namesResponse = Http::timeout(10)->get('https://openexchangerates.org/api/currencies.json');
                $ratesResponse = Http::timeout(10)->get("https://openexchangerates.org/api/latest.json?app_id={$appId}");
            } catch (\Exception $e) {
                $this->error('Network error during sync: '.$e->getMessage());
            }
        }

        $names = [];
        $rates = [];

        if ($namesResponse && $namesResponse->successful() && $ratesResponse && $ratesResponse->successful()) {
            $names = $namesResponse->json();
            $ratesData = $ratesResponse->json();
            $rates = $ratesData['rates'] ?? [];
            $this->info('Successfully fetched rates and names from openexchangerates.org.');
        } else {
            $this->warn('Could not fetch real-time rates. Falling back to default list...');
            // Fallback list of major currencies with approximate mock rates relative to USD (for testing/robustness)
            $names = [
                'USD' => 'United States Dollar',
                'EUR' => 'Euro',
                'GBP' => 'British Pound Sterling',
                'INR' => 'Indian Rupee',
                'AUD' => 'Australian Dollar',
                'CAD' => 'Canadian Dollar',
                'JPY' => 'Japanese Yen',
                'CNY' => 'Chinese Yuan',
                'CHF' => 'Swiss Franc',
                'NZD' => 'New Zealand Dollar',
                'ZAR' => 'South African Rand',
                'RUB' => 'Russian Ruble',
                'BRL' => 'Brazilian Real',
                'SGD' => 'Singapore Dollar',
                'HKD' => 'Hong Kong Dollar',
                'KRW' => 'South Korean Won',
                'MXN' => 'Mexican Peso',
                'TRY' => 'Turkish Lira',
                'SAR' => 'Saudi Riyal',
                'AED' => 'United Arab Emirates Dirham',
            ];
            $rates = [
                'USD' => 1.0,
                'EUR' => 0.92,
                'GBP' => 0.79,
                'INR' => 83.25,
                'AUD' => 1.51,
                'CAD' => 1.36,
                'JPY' => 156.40,
                'CNY' => 7.24,
                'CHF' => 0.90,
                'NZD' => 1.63,
                'ZAR' => 18.45,
                'RUB' => 90.15,
                'BRL' => 5.15,
                'SGD' => 1.35,
                'HKD' => 7.82,
                'KRW' => 1375.50,
                'MXN' => 16.70,
                'TRY' => 32.20,
                'SAR' => 3.75,
                'AED' => 3.67,
            ];
        }

        if (empty($names) || empty($rates)) {
            $this->error('Failed to retrieve rates and fallback is empty. Sync aborted.');

            return 1;
        }

        $this->info('Fetching flags from RestCountries API...');
        $countries = null;
        try {
            $countriesResponse = Http::timeout(10)->get('https://restcountries.com/v3.1/all?fields=name,flags,currencies,cca2,flag');
            if ($countriesResponse->successful()) {
                $countries = $countriesResponse->json();
                // Save to local fallback cache
                file_put_contents(storage_path('app/geo/countries_info.json'), json_encode($countries));
                $this->info('Successfully fetched flag metadata from RestCountries API.');
            }
        } catch (\Exception $e) {
            $this->warn('Error fetching flags from RestCountries API: '.$e->getMessage());
        }

        if (empty($countries)) {
            $this->info('Loading flag metadata from local fallback storage...');
            $fallbackPath = storage_path('app/geo/countries_info.json');
            if (file_exists($fallbackPath)) {
                $countries = json_decode(file_get_contents($fallbackPath), true);
            }
        }

        if (empty($countries)) {
            $this->error('Failed to retrieve flag metadata from both API and local fallback. Sync aborted.');

            return 1;
        }

        $currencyFlags = [
            'EUR' => [
                'flag' => '🇪🇺',
                'flag_png' => 'https://flagcdn.com/w320/eu.png',
                'flag_svg' => 'https://flagcdn.com/eu.svg',
                'symbol' => '€',
            ],
        ];
        $currencyToCountriesList = [];
        $countryToCurrencyCode = [];

        foreach ($countries as $country) {
            $cca2 = strtoupper($country['cca2'] ?? '');
            if (! $cca2 || empty($country['currencies'])) {
                continue;
            }

            $countryName = $country['name']['common'] ?? ($country['name']['official'] ?? '');
            if (! $countryName) {
                continue;
            }

            $currenciesList = array_keys($country['currencies']);
            $primaryCurrency = strtoupper($currenciesList[0]);
            $countryToCurrencyCode[$cca2] = $primaryCurrency;

            foreach ($country['currencies'] as $currencyCode => $currencyInfo) {
                $currencyCode = strtoupper($currencyCode);

                if (! isset($currencyToCountriesList[$currencyCode])) {
                    $currencyToCountriesList[$currencyCode] = [];
                }
                $currencyToCountriesList[$currencyCode][] = $countryName;

                $isPreferred = (substr($currencyCode, 0, 2) === $cca2);

                if ($currencyCode === 'EUR') {
                    $currencyFlags['EUR'] = [
                        'flag' => '🇪🇺',
                        'flag_png' => 'https://flagcdn.com/w320/eu.png',
                        'flag_svg' => 'https://flagcdn.com/eu.svg',
                        'symbol' => '€',
                    ];
                } else {
                    if (! isset($currencyFlags[$currencyCode]) || $isPreferred) {
                        $currencyFlags[$currencyCode] = [
                            'flag' => $country['flag'] ?? null,
                            'flag_png' => $country['flags']['png'] ?? null,
                            'flag_svg' => $country['flags']['svg'] ?? null,
                            'symbol' => $currencyInfo['symbol'] ?? null,
                        ];
                    }
                }
            }
        }

        $this->info('Saving currencies to the database...');

        foreach ($names as $code => $name) {
            // Only save if we have a rate for it
            if (isset($rates[$code])) {
                if ($code === 'EUR') {
                    $eurozoneCountries = isset($currencyToCountriesList['EUR']) ? array_unique($currencyToCountriesList['EUR']) : [];
                    if (empty($eurozoneCountries)) {
                        $country = 'Eurozone (Austria, Belgium, Cyprus, Estonia, Finland, France, Germany, Greece, Ireland, Italy, Latvia, Lithuania, Luxembourg, Malta, Netherlands, Portugal, Slovakia, Slovenia, Spain)';
                    } else {
                        sort($eurozoneCountries);
                        $country = 'Eurozone ('.implode(', ', $eurozoneCountries).')';
                    }
                } else {
                    $country = isset($currencyToCountriesList[$code]) ? implode(', ', array_unique($currencyToCountriesList[$code])) : null;
                }

                // If not found in dynamic list, guess country name based on currency description
                if (! $country) {
                    $parts = explode(' ', $name);
                    if (count($parts) > 1) {
                        array_pop($parts); // remove "Dollar", "Rupee", etc.
                        $country = implode(' ', $parts);
                    } else {
                        $country = $name;
                    }
                }

                $flagInfo = $currencyFlags[$code] ?? null;

                // Skip currencies that do not have flag emoji or flag images
                if (empty($flagInfo) || empty($flagInfo['flag']) || empty($flagInfo['flag_png']) || empty($flagInfo['flag_svg'])) {
                    continue;
                }

                Currency::updateOrCreate(
                    ['code' => $code],
                    [
                        'name' => $name,
                        'country' => $country,
                        'flag' => $flagInfo['flag'],
                        'flag_png' => $flagInfo['flag_png'],
                        'flag_svg' => $flagInfo['flag_svg'],
                        'symbol' => $flagInfo['symbol'] ?? null,
                        'rate' => $rates[$code],
                        'active' => true,
                    ]
                );
            }
        }

        // Cache in Redis for performance
        $this->info('Caching currencies and rates in Redis...');
        $activeCurrencies = Currency::where('active', true)->get();
        $ratesCache = $activeCurrencies->pluck('rate', 'code')->toArray();

        Cache::forget('currencies:all');
        Cache::forget('currencies:all_list');
        Cache::forget('currencies:rates');

        Cache::put('currencies:all', $activeCurrencies, now()->addDays(7));
        Cache::put('currencies:all_list', $activeCurrencies->keyBy('code')->toArray(), now()->addDays(7));
        Cache::put('currencies:rates', $ratesCache, now()->addDays(7));

        $this->info('Processing Geo data (Countries, States, Cities)...');
        try {
            $countriesPath = storage_path('app/geo/countries.json');
            $statesPath = storage_path('app/geo/states.json');
            $citiesPath = storage_path('app/geo/cities.json');

            if (file_exists($countriesPath) && file_exists($statesPath) && file_exists($citiesPath)) {
                $countriesData = json_decode(file_get_contents($countriesPath), true);
                $countriesList = $countriesData['countries'] ?? [];
                unset($countriesData);

                $statesData = json_decode(file_get_contents($statesPath), true);
                $statesList = $statesData['states'] ?? [];
                unset($statesData);

                $citiesData = json_decode(file_get_contents($citiesPath), true);
                $citiesList = $citiesData['cities'] ?? [];
                unset($citiesData);
                gc_collect_cycles();

                $this->info('Clearing and seeding Country, State, City database tables...');

                $connection = DB::connection()->getDriverName();
                if ($connection === 'sqlite') {
                    DB::statement('PRAGMA foreign_keys = OFF');
                    DB::table('cities')->delete();
                    DB::table('states')->delete();
                    DB::table('countries')->delete();
                    DB::statement('PRAGMA foreign_keys = ON');
                } else {
                    DB::statement('TRUNCATE TABLE cities CASCADE');
                    DB::statement('TRUNCATE TABLE states CASCADE');
                    DB::statement('TRUNCATE TABLE countries CASCADE');
                }

                $this->info('Seeding countries...');
                $countriesToInsert = [];
                foreach ($countriesList as $c) {
                    $cca2 = strtoupper($c['sortname'] ?? '');
                    $currencyCode = $countryToCurrencyCode[$cca2] ?? null;

                    $countriesToInsert[] = [
                        'id' => (int) $c['id'],
                        'name' => $c['name'],
                        'sortname' => $c['sortname'] ?? null,
                        'currency_code' => $currencyCode,
                        'phone_code' => isset($c['phoneCode']) ? (int) $c['phoneCode'] : null,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ];
                }
                foreach (array_chunk($countriesToInsert, 100) as $chunk) {
                    DB::table('countries')->insert($chunk);
                }

                $this->info('Seeding states...');
                $statesToInsert = [];
                foreach ($statesList as $s) {
                    $statesToInsert[] = [
                        'id' => (int) $s['id'],
                        'name' => $s['name'],
                        'country_id' => (int) $s['country_id'],
                        'created_at' => now(),
                        'updated_at' => now(),
                    ];
                }
                foreach (array_chunk($statesToInsert, 1000) as $chunk) {
                    DB::table('states')->insert($chunk);
                }

                $this->info('Seeding cities...');
                $validStateIds = DB::table('states')->pluck('id')->toArray();
                $validStateIdsMap = array_flip($validStateIds);

                $chunk = [];
                foreach ($citiesList as $c) {
                    $stateId = (int) $c['state_id'];
                    if (! isset($validStateIdsMap[$stateId])) {
                        continue;
                    }

                    $chunk[] = [
                        'id' => (int) $c['id'],
                        'name' => $c['name'],
                        'state_id' => $stateId,
                        'created_at' => now(),
                        'updated_at' => now(),
                    ];
                    if (count($chunk) >= 5000) {
                        DB::table('cities')->insert($chunk);
                        $chunk = [];
                    }
                }
                if (! empty($chunk)) {
                    DB::table('cities')->insert($chunk);
                }

                $this->info('Database seeding completed successfully. Retrieving from database to compile Geo Cache...');

                $dbCountries = DB::table('countries')->get(['id', 'name', 'sortname', 'currency_code'])->toArray();
                $dbStates = DB::table('states')->get(['id', 'name', 'country_id'])->toArray();
                $dbCities = DB::table('cities')->get(['id', 'name', 'state_id'])->toArray();

                // 1. Group states by country_id
                $countryStates = [];
                foreach ($dbStates as $s) {
                    $sArray = (array) $s;
                    $countryStates[$sArray['country_id']][] = [
                        'id' => $sArray['id'],
                        'name' => $sArray['name'],
                    ];
                }

                // 2. Group cities by state_id
                $stateCities = [];
                foreach ($dbCities as $c) {
                    $cArray = (array) $c;
                    $stateCities[$cArray['state_id']][] = $cArray['name'];
                }

                // 3. Build merged list
                $mergedGeo = [];
                $activeCurrenciesMapped = Currency::where('active', true)->get()->keyBy('code')->toArray();

                foreach ($dbCountries as $c) {
                    $cArray = (array) $c;
                    $currCode = $cArray['currency_code'] ?? null;

                    if ($currCode && isset($activeCurrenciesMapped[$currCode])) {
                        $curr = $activeCurrenciesMapped[$currCode];
                        $countryKey = strtolower($cArray['name']);

                        $states = [];
                        $sList = $countryStates[$cArray['id']] ?? [];
                        foreach ($sList as $stateInfo) {
                            $cities = $stateCities[$stateInfo['id']] ?? [];
                            $states[$stateInfo['name']] = $cities;
                        }

                        $mergedGeo[$countryKey] = [
                            'currency' => [
                                'name' => $curr['name'],
                                'code' => $curr['code'],
                                'symbol' => $curr['symbol'] ?? null,
                                'flag' => $curr['flag'],
                                'flag_png' => $curr['flag_png'],
                                'flag_svg' => $curr['flag_svg'],
                                'rate' => (float) $curr['rate'],
                                'country' => $curr['country'],
                                'active' => (bool) $curr['active'],
                            ],
                            'states' => $states,
                        ];
                    }
                }

                $countriesCache = array_map(function ($item) {
                    return (array) $item;
                }, $dbCountries);

                $statesCache = array_map(function ($item) {
                    return (array) $item;
                }, $dbStates);

                $citiesCache = array_map(function ($item) {
                    return (array) $item;
                }, $dbCities);

                // Cache individual and merged lists
                Cache::put('geo:countries', $countriesCache, now()->addDays(7));
                Cache::put('geo:states_list', $statesCache, now()->addDays(7));
                Cache::put('geo:cities_list', $citiesCache, now()->addDays(7));
                Cache::put('geo:all_merged', $mergedGeo, now()->addDays(7));

                $this->info('Successfully processed and cached Geo data.');
            } else {
                $this->warn('Geo JSON files not found in storage. Skipping geo compilation.');
            }
        } catch (\Exception $e) {
            $this->error('Error processing Geo data: '.$e->getMessage());
        }

        $this->info('Currency sync completed successfully!');

        return 0;
    }

    /**
     * Convert 2-letter country code to flag emoji.
     */
    public static function countryCodeToEmoji(string $countryCode): string
    {
        $countryCode = strtoupper($countryCode);
        if (strlen($countryCode) !== 2) {
            return '';
        }

        $codePoints = array_map(function ($char) {
            return 127397 + ord($char);
        }, str_split($countryCode));

        return mb_convert_encoding('&#'.implode(';&#', $codePoints).';', 'UTF-8', 'HTML-ENTITIES');
    }
}