<?php

namespace App\Console\Commands;

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

class MergeDuplicateLocations extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'location:merge-all-duplicates 
                            {--dry-run : Run without making changes}
                            {--auto : Automatically select primary (lowest ID)}
                            {--states : Only merge duplicate states}
                            {--cities : Only merge duplicate cities}
                            {--centers : Only merge duplicate centers}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Merge duplicate locations (states, cities, centers) by updating foreign key references';

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $this->info('🔍 Merging Duplicate Locations');
        $this->newLine();

        if ($this->option('dry-run')) {
            $this->line('<fg=yellow>🔸 DRY RUN MODE - No changes will be made</>');
            $this->newLine();
        }

        $options = collect(['states', 'cities', 'centers'])
            ->filter(fn ($opt) => $this->option($opt))
            ->toArray();

        // If no specific option, run all
        if (empty($options)) {
            $options = ['states', 'cities', 'centers'];
        }

        $success = true;

        // Process in order: states -> cities -> centers (due to foreign key dependencies)
        if (in_array('states', $options)) {
            $this->info('═══════════════════════════════════════════════════════');
            $this->info('🗺️  MERGING DUPLICATE STATES');
            $this->info('═══════════════════════════════════════════════════════');
            $this->newLine();
            $success = $this->mergeStates() && $success;
        }

        if (in_array('cities', $options)) {
            $this->info('═══════════════════════════════════════════════════════');
            $this->info('🏙️  MERGING DUPLICATE CITIES');
            $this->info('═══════════════════════════════════════════════════════');
            $this->newLine();
            $success = $this->mergeCities() && $success;
        }

        if (in_array('centers', $options)) {
            $this->info('═══════════════════════════════════════════════════════');
            $this->info('🏢 MERGING DUPLICATE CENTERS');
            $this->info('═══════════════════════════════════════════════════════');
            $this->newLine();
            $success = $this->mergeCenters() && $success;
        }

        return $success ? self::SUCCESS : self::FAILURE;
    }

    /**
     * Merge duplicate states
     */
    protected function mergeStates(): bool
    {
        $duplicates = DB::table('states')
            ->select('name', DB::raw('COUNT(*) as count'), DB::raw('array_agg(id ORDER BY id) as ids'))
            ->groupBy('name')
            ->having(DB::raw('COUNT(*)'), '>', 1)
            ->get();

        if ($duplicates->isEmpty()) {
            $this->info('✅ No duplicate states found');
            $this->newLine();

            return true;
        }

        $this->warn("Found {$duplicates->count()} groups of duplicate states");
        $this->newLine();

        foreach ($duplicates as $duplicate) {
            $ids = array_map('intval', explode(',', trim($duplicate->ids, '{}')));
            $this->processDuplicateStates($duplicate->name, $ids);
        }

        return true;
    }

    /**
     * Process duplicate states
     */
    protected function processDuplicateStates(string $name, array $ids): void
    {
        $this->line("Processing state: \"{$name}\" (IDs: ".implode(', ', $ids).')');

        $primaryId = $this->selectPrimary($ids, 'state');
        if (! $primaryId) {
            $this->warn('  ⚠️  Skipped');
            $this->newLine();

            return;
        }

        $duplicateIds = array_filter($ids, fn ($id) => $id !== $primaryId);
        $this->info("  Primary: ID {$primaryId}, Merging: ".implode(', ', $duplicateIds));

        if (! $this->option('dry-run')) {
            DB::transaction(function () use ($primaryId, $duplicateIds) {
                // Update cities
                $updated = DB::table('cities')->whereIn('state_id', $duplicateIds)->update(['state_id' => $primaryId]);

                // Update orders
                $updated += DB::table('orders')->whereIn('state_id', $duplicateIds)->update(['state_id' => $primaryId]);

                // Merge association_state pivot
                // Get all unique association IDs related to any of these states (including primary)
                $allStateIds = array_merge([$primaryId], $duplicateIds);
                $associationIds = DB::table('association_state')
                    ->whereIn('state_id', $allStateIds)
                    ->distinct()
                    ->pluck('association_id')
                    ->toArray();

                // Delete all relationships for these associations
                DB::table('association_state')
                    ->whereIn('association_id', $associationIds)
                    ->whereIn('state_id', $allStateIds)
                    ->delete();

                // Re-insert with primary state only
                if (! empty($associationIds)) {
                    $insertData = array_map(fn ($assocId) => [
                        'association_id' => $assocId,
                        'state_id' => $primaryId,
                    ], $associationIds);
                    DB::table('association_state')->insert($insertData);
                    $updated += count($insertData);
                }

                // Update well_licenses and station_licenses
                DB::table('well_licenses')->whereIn('state_id', $duplicateIds)->update(['state_id' => $primaryId]);
                DB::table('station_licenses')->whereIn('state_id', $duplicateIds)->update(['state_id' => $primaryId]);

                // Delete duplicates
                State::whereIn('id', $duplicateIds)->delete();

                $this->info("  ✓ Updated {$updated} references, deleted ".count($duplicateIds).' state(s)');
            });
        } else {
            $this->line('  [DRY RUN] Would merge these states');
        }

        $this->newLine();
    }

    /**
     * Merge duplicate cities
     */
    protected function mergeCities(): bool
    {
        $duplicates = DB::table('cities')
            ->select('name', 'state_id', DB::raw('COUNT(*) as count'), DB::raw('array_agg(id ORDER BY id) as ids'))
            ->groupBy('name', 'state_id')
            ->having(DB::raw('COUNT(*)'), '>', 1)
            ->get();

        if ($duplicates->isEmpty()) {
            $this->info('✅ No duplicate cities found');
            $this->newLine();

            return true;
        }

        $this->warn("Found {$duplicates->count()} groups of duplicate cities");
        $this->newLine();

        foreach ($duplicates as $duplicate) {
            $ids = array_map('intval', explode(',', trim($duplicate->ids, '{}')));
            $this->processDuplicateCities($duplicate->name, $duplicate->state_id, $ids);
        }

        return true;
    }

    /**
     * Process duplicate cities
     */
    protected function processDuplicateCities(string $name, int $stateId, array $ids): void
    {
        $stateName = State::find($stateId)?->name ?? 'Unknown';
        $this->line("Processing city: \"{$name}\" in state \"{$stateName}\" (IDs: ".implode(', ', $ids).')');

        $primaryId = $this->selectPrimary($ids, 'city');
        if (! $primaryId) {
            $this->warn('  ⚠️  Skipped');
            $this->newLine();

            return;
        }

        $duplicateIds = array_filter($ids, fn ($id) => $id !== $primaryId);
        $this->info("  Primary: ID {$primaryId}, Merging: ".implode(', ', $duplicateIds));

        if (! $this->option('dry-run')) {
            DB::transaction(function () use ($primaryId, $duplicateIds) {
                // Update centers
                $updated = DB::table('centers')->whereIn('city_id', $duplicateIds)->update(['city_id' => $primaryId]);

                // Update orders
                $updated += DB::table('orders')->whereIn('city_id', $duplicateIds)->update(['city_id' => $primaryId]);

                // Merge association_city pivot
                // Get all unique association IDs related to any of these cities (including primary)
                $allCityIds = array_merge([$primaryId], $duplicateIds);
                $associationIds = DB::table('association_city')
                    ->whereIn('city_id', $allCityIds)
                    ->distinct()
                    ->pluck('association_id')
                    ->toArray();

                // Delete all relationships for these associations
                DB::table('association_city')
                    ->whereIn('association_id', $associationIds)
                    ->whereIn('city_id', $allCityIds)
                    ->delete();

                // Re-insert with primary city only
                if (! empty($associationIds)) {
                    $insertData = array_map(fn ($assocId) => [
                        'association_id' => $assocId,
                        'city_id' => $primaryId,
                    ], $associationIds);
                    DB::table('association_city')->insert($insertData);
                    $updated += count($insertData);
                }

                // Update well_licenses and station_licenses
                DB::table('well_licenses')->whereIn('city_id', $duplicateIds)->update(['city_id' => $primaryId]);
                DB::table('station_licenses')->whereIn('city_id', $duplicateIds)->update(['city_id' => $primaryId]);

                // Delete duplicates
                City::whereIn('id', $duplicateIds)->delete();

                $this->info("  ✓ Updated {$updated} references, deleted ".count($duplicateIds).' city/cities');
            });
        } else {
            $this->line('  [DRY RUN] Would merge these cities');
        }

        $this->newLine();
    }

    /**
     * Merge duplicate centers
     */
    protected function mergeCenters(): bool
    {
        // Call the dedicated command
        $exitCode = $this->call('location:merge-duplicate-centers', [
            '--dry-run' => $this->option('dry-run'),
            '--auto' => $this->option('auto'),
        ]);

        return $exitCode === self::SUCCESS;
    }

    /**
     * Select primary ID
     */
    protected function selectPrimary(array $ids, string $type): ?int
    {
        if ($this->option('auto')) {
            return min($ids);
        }

        $choice = $this->ask("Which {$type} ID should be kept as primary?", (string) min($ids));

        return $choice ? (int) $choice : null;
    }
}
