<?php

namespace App\Console\Commands;

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

class ImportResidentialGatherings extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'residential:import 
                            {--dry-run : Run without actually saving to database}
                            {--file= : Path to CSV file (defaults to database/seeders/files/centers_data.csv)}
                            {--truncate : Truncate the table before importing}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Import residential gatherings from CSV file';

    protected array $stats = [
        'created' => 0,
        'skipped_duplicate' => 0,
        'skipped_no_center' => 0,
        'skipped_multiple_centers' => 0,
        'centers_created' => 0,
        'errors' => 0,
    ];

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $dryRun = $this->option('dry-run');
        $csvFile = $this->option('file') ?? database_path('seeders/files/centers_data.csv');

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

            return Command::FAILURE;
        }

        $this->info('📂 Reading data from: '.$csvFile);
        if ($dryRun) {
            $this->warn('🔸 DRY RUN MODE - No changes will be made to the database');
        }
        $this->newLine();

        // Truncate if requested
        if ($this->option('truncate') && ! $dryRun) {
            if ($this->confirm('Are you sure you want to truncate the residential_gatherings table?', false)) {
                ResidentialGathering::truncate();
                $this->info('✓ Table truncated');
                $this->newLine();
            }
        }

        try {
            // Read CSV data
            $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');
            $this->newLine();

            // Process each row
            $progressBar = $this->output->createProgressBar(count($data));
            $progressBar->start();

            foreach ($data as $row) {
                $this->processRow($row, $dryRun);
                $progressBar->advance();
            }

            $progressBar->finish();
            $this->newLine(2);

            // Display statistics
            $this->displayStatistics($dryRun);

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

            return Command::FAILURE;
        }
    }

    /**
     * Read CSV file and return data array
     */
    protected function readCsvFile(string $filePath): array
    {
        $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');
        }

        // Read data lines
        while (($row = fgetcsv($file, 0, ';')) !== false) {
            if (count($row) >= 7) {
                $residentialName = trim($row[3]);

                // Skip if residential name is empty or just "-"
                if (empty($residentialName) || $residentialName === '-') {
                    continue;
                }

                $data[] = [
                    'state_name' => trim($row[0]),
                    'city_name' => trim($row[1]),
                    'center_name' => trim($row[2]),
                    'residential_name' => $residentialName,
                    'residential_type' => trim($row[4]),
                    'population' => (int) $row[5],
                    'families_count' => (int) $row[6],
                ];
            }
        }

        fclose($file);

        return $data;
    }

    /**
     * Process a single CSV row
     */
    protected function processRow(array $row, bool $dryRun): void
    {
        try {
            // Find the center
            $center = $this->findCenter($row['state_name'], $row['city_name'], $row['center_name'], $dryRun);

            if (! $center) {
                $this->stats['skipped_no_center']++;

                return;
            }

            if ($center === 'multiple') {
                $this->stats['skipped_multiple_centers']++;

                return;
            }

            // Check if this residential gathering already exists
            if (! $dryRun) {
                $exists = ResidentialGathering::where('center_id', $center->id)
                    ->where('name', $row['residential_name'])
                    ->exists();

                if ($exists) {
                    $this->stats['skipped_duplicate']++;

                    return;
                }
            }

            // Create the residential gathering
            if (! $dryRun) {
                ResidentialGathering::create([
                    'name' => $row['residential_name'],
                    'center_id' => $center->id,
                    'type' => $row['residential_type'],
                    'families_count' => $row['families_count'],
                    'population' => $row['population'],
                ]);
            }

            $this->stats['created']++;
        } catch (\Exception $e) {
            $this->stats['errors']++;
        }
    }

    /**
     * Find center by state, city, and center name
     */
    protected function findCenter(string $stateName, string $cityName, string $centerName, bool $dryRun)
    {
        // Normalize center name (replace '-' with 'غير ذلك')
        $normalizedCenterName = $centerName === '-' ? 'غير ذلك' : $centerName;

        // First try: Find by normalized center name
        $centers = Center::whereHas('city', function ($cityQuery) use ($stateName, $cityName) {
            $cityQuery->where('name', $cityName)
                ->whereHas('state', function ($stateQuery) use ($stateName) {
                    $stateQuery->where('name', $stateName);
                });
        })
            ->where('name', $normalizedCenterName)
            ->get();

        if ($centers->count() > 1) {
            return 'multiple';
        }

        if ($centers->count() === 1) {
            return $centers->first();
        }

        // Second try: Find center with name = city name
        $centers = Center::whereHas('city', function ($cityQuery) use ($stateName, $cityName) {
            $cityQuery->where('name', $cityName)
                ->whereHas('state', function ($stateQuery) use ($stateName) {
                    $stateQuery->where('name', $stateName);
                });
        })
            ->where('name', $cityName)
            ->get();

        if ($centers->count() > 1) {
            return 'multiple';
        }

        if ($centers->count() === 1) {
            return $centers->first();
        }

        // Not found, create a new center
        $city = City::where('name', $cityName)
            ->whereHas('state', function ($stateQuery) use ($stateName) {
                $stateQuery->where('name', $stateName);
            })
            ->first();

        if (! $city) {
            return null; // Cannot create center without a valid city
        }

        // Create the center with the normalized name
        if ($dryRun) {
            // In dry-run mode, create a mock center object for validation
            $center = new Center([
                'name' => $normalizedCenterName,
                'city_id' => $city->id,
            ]);
            $center->id = 0; // Mock ID
        } else {
            $center = Center::create([
                'name' => $normalizedCenterName,
                'city_id' => $city->id,
            ]);
            $this->stats['centers_created']++;
        }

        return $center;
    }

    /**
     * Display statistics
     */
    protected function displayStatistics(bool $dryRun): void
    {
        $this->info('═══════════════════════════════════════════════════════');
        $this->info('          Import Summary');
        $this->info('═══════════════════════════════════════════════════════');

        if ($dryRun) {
            $this->warn('DRY RUN - No actual changes were made');
        }

        $this->line("✓ Created:                        {$this->stats['created']}");
        $this->line("⊕ Centers Created:                {$this->stats['centers_created']}");
        $this->line("⊗ Skipped (duplicate):            {$this->stats['skipped_duplicate']}");
        $this->line("⚠ Skipped (center not found):     {$this->stats['skipped_no_center']}");
        $this->line("⚠ Skipped (multiple centers):     {$this->stats['skipped_multiple_centers']}");
        $this->line("✗ Errors:                          {$this->stats['errors']}");

        $total = array_sum($this->stats);
        $this->newLine();
        $this->info("Total processed: {$total}");
        $this->info('═══════════════════════════════════════════════════════');

        if ($this->stats['centers_created'] > 0) {
            $this->newLine();
            $this->info("✓ {$this->stats['centers_created']} new centers were created automatically.");
        }

        if ($this->stats['skipped_multiple_centers'] > 0) {
            $this->newLine();
            $this->warn('⚠ Some centers had multiple matches. Run location:merge-duplicate-centers first.');
        }

        if ($this->stats['skipped_no_center'] > 0) {
            $this->newLine();
            $this->warn("⚠ {$this->stats['skipped_no_center']} residential gatherings could not be matched (city not found).");
        }
    }
}
