<?php

namespace App\Console\Commands;

use App\Models\Center;
use App\Models\City;
use App\Models\State;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\File;

class SeedCentersData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:seed-centers-data {csv_file : Path to the CSV file}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Import states, cities, and centers data from CSV file';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $csvFile = $this->argument('csv_file');

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

            return Command::FAILURE;
        }

        $this->info("Starting data import from: {$csvFile}");

        try {
            // Read and process CSV
            $data = $this->readCsvFile($csvFile);

            if (empty($data)) {
                $this->error('No data found in CSV file or invalid format');

                return Command::FAILURE;
            }

            $this->info('Found '.count($data).' records in CSV');

            // Process data in steps
            $this->processStates($data);
            $this->processCities($data);
            $this->processCenters($data);

            $this->info('Data import completed successfully!');

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error('Error processing CSV: '.$e->getMessage());

            return Command::FAILURE;
        }
    }

    /**
     * Read CSV file and return data array
     */
    private function readCsvFile($filePath)
    {
        $data = [];
        $file = fopen($filePath, 'r');

        if (! $file) {
            throw new \Exception('Cannot open CSV file');
        }

        // Read header line
        $header = fgetcsv($file, 0, ';');

        if (! $header || count($header) < 7) {
            fclose($file);
            throw new \Exception('Invalid CSV header format');
        }

        // Expected headers: المنطقة;المحافظة;المركز;المسمى السكاني;نوع المسمى السكاني;السكان;المساكن
        $expectedHeaders = ['المنطقة', 'المحافظة', 'المركز', 'المسمى السكاني', 'نوع المسمى السكاني', 'السكان', 'المساكن'];

        // Read data lines
        while (($row = fgetcsv($file, 0, ';')) !== false) {
            if (count($row) >= 7) {
                $data[] = [
                    'region' => trim($row[0]),
                    'governorate' => trim($row[1]),
                    'center' => trim($row[2]),
                    'residential_name' => trim($row[3]),
                    'residential_type' => trim($row[4]),
                    'population' => (int) $row[5],
                    'houses' => (int) $row[6],
                ];
            }
        }

        fclose($file);

        return $data;
    }

    /**
     * Process and create/update States
     */
    private function processStates($data)
    {
        $this->info('Processing States...');

        $uniqueStates = collect($data)
            ->pluck('region')
            ->filter()
            ->unique()
            ->values();

        $createdCount = 0;
        $existingCount = 0;

        foreach ($uniqueStates as $stateName) {
            // Try to find existing state by Arabic name (cross-database compatible)
            $state = State::where(function ($query) use ($stateName) {
                $this->addJsonWhereClause($query, 'name', $stateName);
            })->first();

            if (! $state) {
                $state = State::create([
                    'name' => [
                        'ar' => $stateName,
                        'en' => $stateName, // You can translate this later
                    ],
                ]);
                $createdCount++;
                $this->line("Created state: {$stateName}");
            } else {
                $existingCount++;
            }
        }

        $this->info("States processed - Created: {$createdCount}, Existing: {$existingCount}");
    }

    /**
     * Process and create/update Cities
     */
    private function processCities($data)
    {
        $this->info('Processing Cities...');

        $uniqueCities = collect($data)
            ->groupBy('region')
            ->flatMap(function ($regionData, $regionName) {
                return collect($regionData)
                    ->pluck('governorate')
                    ->filter()
                    ->unique()
                    ->map(function ($cityName) use ($regionName) {
                        return [
                            'city_name' => $cityName,
                            'state_name' => $regionName,
                        ];
                    });
            })
            ->unique(function ($item) {
                return $item['city_name'].'|'.$item['state_name'];
            });

        $createdCount = 0;
        $existingCount = 0;

        foreach ($uniqueCities as $cityData) {
            // Find state by Arabic name (cross-database compatible)
            $state = State::where(function ($query) use ($cityData) {
                $this->addJsonWhereClause($query, 'name', $cityData['state_name']);
            })->first();

            if (! $state) {
                $this->warn("State not found for city: {$cityData['city_name']} (State: {$cityData['state_name']})");

                continue;
            }

            // Try to find existing city (cross-database compatible)
            $city = City::where('state_id', $state->id)
                ->where(function ($query) use ($cityData) {
                    $this->addJsonWhereClause($query, 'name', $cityData['city_name']);
                })
                ->first();

            if (! $city) {
                $city = City::create([
                    'name' => [
                        'ar' => $cityData['city_name'],
                        'en' => $cityData['city_name'], // You can translate this later
                    ],
                    'state_id' => $state->id,
                ]);
                $createdCount++;
                $this->line("Created city: {$cityData['city_name']} in {$cityData['state_name']}");
            } else {
                $existingCount++;
            }
        }

        $this->info("Cities processed - Created: {$createdCount}, Existing: {$existingCount}");
    }

    /**
     * Process and create/update Centers
     */
    private function processCenters($data)
    {
        $this->info('Processing Centers...');

        $centerData = collect($data)
            ->groupBy(function ($item) {
                return $item['region'].'|'.$item['governorate'].'|'.$item['center'];
            })
            ->map(function ($centerRecords, $key) {
                $parts = explode('|', $key);
                $totalHouses = $centerRecords->sum('houses');

                return [
                    'state_name' => $parts[0],
                    'city_name' => $parts[1],
                    'center_name' => $parts[2],
                    'total_houses' => $totalHouses,
                ];
            })
            ->filter(function ($center) {
                return ! empty($center['center_name']);
            });

        $createdCount = 0;
        $updatedCount = 0;
        $existingCount = 0;

        foreach ($centerData as $centerInfo) {
            // Find the city by state and city name (cross-database compatible)
            $city = City::whereHas('state', function ($query) use ($centerInfo) {
                $this->addJsonWhereClause($query, 'name', $centerInfo['state_name']);
            })->where(function ($query) use ($centerInfo) {
                $this->addJsonWhereClause($query, 'name', $centerInfo['city_name']);
            })->first();

            if (! $city) {
                $this->warn("City not found for center: {$centerInfo['center_name']} (City: {$centerInfo['city_name']}, State: {$centerInfo['state_name']})");

                continue;
            }

            // Find or create center (cross-database compatible)
            $center = Center::where('city_id', $city->id)
                ->where(function ($query) use ($centerInfo) {
                    $this->addJsonWhereClause($query, 'name', $centerInfo['center_name']);
                })
                ->first();

            if ($center) {
                // Update existing center if families_count is different
                if ($center->families_count != $centerInfo['total_houses']) {
                    $center->update(['families_count' => $centerInfo['total_houses']]);
                    $updatedCount++;
                    $this->line("Updated center: {$centerInfo['center_name']} (families: {$centerInfo['total_houses']})");
                } else {
                    $existingCount++;
                }
            } else {
                // Create new center
                Center::create([
                    'name' => [
                        'ar' => $centerInfo['center_name'],
                        'en' => $centerInfo['center_name'], // You can translate this later
                    ],
                    'city_id' => $city->id,
                    'families_count' => $centerInfo['total_houses'],
                ]);
                $createdCount++;
                $this->line("Created center: {$centerInfo['center_name']} in {$centerInfo['city_name']} (families: {$centerInfo['total_houses']})");
            }
        }

        $this->info("Centers processed - Created: {$createdCount}, Updated: {$updatedCount}, Existing: {$existingCount}");
    }

    /**
     * Helper method to query JSON fields across different database drivers
     */
    private function addJsonWhereClause($query, $jsonField, $value, $key = 'ar')
    {
        $driver = config('database.default');

        if ($driver === 'pgsql') {
            // PostgreSQL syntax
            return $query->whereRaw("{$jsonField}->>'ar' = ?", [$value])
                ->orWhereRaw("{$jsonField}->>'en' = ?", [$value]);
        } else {
            // MySQL syntax
            return $query->whereRaw("JSON_UNQUOTE(JSON_EXTRACT({$jsonField}, '$.ar')) = ?", [$value])
                ->orWhereRaw("JSON_UNQUOTE(JSON_EXTRACT({$jsonField}, '$.en')) = ?", [$value]);
        }
    }
}
