<?php

namespace App\Console\Commands;

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

class UpdateCentersResidentialData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'centers:update-residential-data 
                            {--dry-run : Run without actually updating the database}
                            {--file= : Path to CSV file (defaults to database/seeders/files/centers_data.csv)}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Update existing centers with population, residential_name, and residential_type from CSV data';

    /**
     * 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');
        }

        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();

            // Group data by center
            $centerData = $this->groupDataByCenters($data);

            $this->info('Processing '.count($centerData).' unique centers...');
            $this->newLine();

            // Statistics
            $stats = [
                'updated' => 0,
                'skipped_multiple_matches' => 0,
                'skipped_not_found' => 0,
                'skipped_already_has_data' => 0,
                'errors' => 0,
            ];

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

            foreach ($centerData as $centerInfo) {
                $result = $this->updateCenter($centerInfo, $dryRun);
                $stats[$result]++;
                $progressBar->advance();
            }

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

            // Display statistics
            $this->displayStatistics($stats, $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
     */
    private 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) {
                $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;
    }

    /**
     * Group CSV data by centers and aggregate data
     */
    private function groupDataByCenters(array $data): array
    {
        return collect($data)
            ->groupBy(function ($item) {
                return $item['region'].'|'.$item['governorate'].'|'.$item['center'];
            })
            ->map(function ($centerRecords, $key) {
                $parts = explode('|', $key);

                // Aggregate data for this center
                $totalPopulation = $centerRecords->sum('population');
                $totalHouses = $centerRecords->sum('houses');

                // Get all residential names and types
                $residentialNames = $centerRecords->pluck('residential_name')->filter()->unique()->values()->toArray();
                $residentialTypes = $centerRecords->pluck('residential_type')->filter()->unique()->values()->toArray();

                return [
                    'state_name' => $parts[0],
                    'city_name' => $parts[1],
                    'center_name' => $parts[2],
                    'population' => $totalPopulation,
                    'total_houses' => $totalHouses,
                    'residential_names' => $residentialNames,
                    'residential_types' => $residentialTypes,
                    'record_count' => $centerRecords->count(),
                ];
            })
            ->filter(function ($center) {
                return ! empty($center['center_name']);
            })
            ->values()
            ->toArray();
    }

    /**
     * Update a single center with cautious logic
     */
    private function updateCenter(array $centerInfo, bool $dryRun): string
    {
        try {
            // Find matching centers
            $matchingCenters = $this->findMatchingCenters($centerInfo);

            // Case 1: No centers found
            if ($matchingCenters->count() === 0) {
                $this->warn("\nCenter not found: {$centerInfo['center_name']} in {$centerInfo['city_name']}, {$centerInfo['state_name']}");

                return 'skipped_not_found';
            }

            // Case 2: Multiple centers found - DO NOT UPDATE
            if ($matchingCenters->count() > 1) {
                $this->error("\nMultiple centers found ({$matchingCenters->count()}) for: {$centerInfo['center_name']} in {$centerInfo['city_name']}, {$centerInfo['state_name']}");
                $this->line('  Center IDs: '.$matchingCenters->pluck('id')->implode(', '));

                return 'skipped_multiple_matches';
            }

            // Case 3: Exactly one center found - Safe to update
            $center = $matchingCenters->first();

            // Check if center already has residential data
            if ($center->population || $center->residential_name || $center->residential_type) {
                $this->line("\n<comment>Center already has data:</comment> {$centerInfo['center_name']} (ID: {$center->id})");

                return 'skipped_already_has_data';
            }

            // Prepare update data
            $updateData = [
                'population' => $centerInfo['population'],
                'residential_name' => implode(', ', $centerInfo['residential_names']),
                'residential_type' => implode(', ', array_unique($centerInfo['residential_types'])),
            ];

            // Also update families_count if it differs
            if ($center->families_count != $centerInfo['total_houses']) {
                $updateData['families_count'] = $centerInfo['total_houses'];
            }

            // Perform update (if not dry run)
            if (! $dryRun) {
                $center->update($updateData);
            }

            $this->info("\n<info>✓ Updated:</info> {$centerInfo['center_name']} (ID: {$center->id}) - Population: {$centerInfo['population']}, Houses: {$centerInfo['total_houses']}");

            return 'updated';
        } catch (\Exception $e) {
            $this->error("\nError updating center {$centerInfo['center_name']}: ".$e->getMessage());

            return 'errors';
        }
    }

    /**
     * Find centers matching the search criteria
     */
    private function findMatchingCenters(array $centerInfo)
    {
        return Center::whereHas('city', function ($cityQuery) use ($centerInfo) {
            $cityQuery->whereHas('state', function ($stateQuery) use ($centerInfo) {
                $this->addJsonWhereClause($stateQuery, 'name', $centerInfo['state_name']);
            })->where(function ($query) use ($centerInfo) {
                $this->addJsonWhereClause($query, 'name', $centerInfo['city_name']);
            });
        })->where(function ($query) use ($centerInfo) {
            $this->addJsonWhereClause($query, 'name', $centerInfo['center_name']);
        })->get();
    }

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

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

    /**
     * Display statistics
     */
    private function displayStatistics(array $stats, bool $dryRun): void
    {
        $this->newLine();
        $this->info('==========================================');
        $this->info('          Update Summary');
        $this->info('==========================================');

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

        $this->line("✓ Successfully updated:           {$stats['updated']}");
        $this->line("⊗ Skipped (not found):            {$stats['skipped_not_found']}");
        $this->line("⚠ Skipped (multiple matches):     {$stats['skipped_multiple_matches']}");
        $this->line("↷ Skipped (already has data):     {$stats['skipped_already_has_data']}");
        $this->line("✗ Errors:                          {$stats['errors']}");

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

        if ($stats['skipped_multiple_matches'] > 0) {
            $this->newLine();
            $this->warn('⚠ Warning: Some centers had multiple matches and were not updated.');
            $this->warn('  Please review the duplicate centers and resolve them manually.');
        }
    }
}
