<?php

namespace App\Console\Commands;

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

class CheckLocationDuplicates extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'location:check-duplicates {--show-details : Show detailed list of duplicates}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Check for duplicate records in states, cities, and centers tables';

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $this->info('🔍 Checking for duplicate records in location tables...');
        $this->newLine();

        $hasIssues = false;

        // Check States
        $hasIssues = $this->checkStateDuplicates() || $hasIssues;
        $this->newLine();

        // Check Cities
        $hasIssues = $this->checkCityDuplicates() || $hasIssues;
        $this->newLine();

        // Check Centers
        $hasIssues = $this->checkCenterDuplicates() || $hasIssues;
        $this->newLine();

        if ($hasIssues) {
            $this->warn('⚠️  Duplicates found! Please resolve them before adding unique constraints.');

            return self::FAILURE;
        }

        $this->info('✅ No duplicates found! Safe to add unique constraints.');

        return self::SUCCESS;
    }

    /**
     * Check for duplicate states
     */
    protected function checkStateDuplicates(): bool
    {
        $this->line('📍 <fg=cyan>Checking States (unique on: name)</>');

        $duplicates = DB::table('states')
            ->select('name', DB::raw('COUNT(*) as count'), DB::raw('array_agg(id) as ids'))
            ->groupBy('name')
            ->having(DB::raw('COUNT(*)'), '>', 1)
            ->get();

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

            return false;
        }

        $this->error("   ✗ Found {$duplicates->count()} duplicate state name(s)");

        if ($this->option('show-details')) {
            $this->table(
                ['Name', 'Count', 'IDs'],
                $duplicates->map(fn ($item) => [
                    $item->name,
                    $item->count,
                    str_replace(['{', '}'], '', $item->ids),
                ])
            );
        } else {
            foreach ($duplicates as $duplicate) {
                $this->line("     - \"{$duplicate->name}\" appears {$duplicate->count} times (IDs: ".str_replace(['{', '}'], '', $duplicate->ids).')');
            }
        }

        return true;
    }

    /**
     * Check for duplicate cities
     */
    protected function checkCityDuplicates(): bool
    {
        $this->line('🏙️  <fg=cyan>Checking Cities (unique on: name + state_id)</>');

        $duplicates = DB::table('cities')
            ->select('name', 'state_id', DB::raw('COUNT(*) as count'), DB::raw('array_agg(id) as ids'))
            ->groupBy('name', 'state_id')
            ->having(DB::raw('COUNT(*)'), '>', 1)
            ->get();

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

            return false;
        }

        $this->error("   ✗ Found {$duplicates->count()} duplicate city combination(s)");

        if ($this->option('show-details')) {
            $this->table(
                ['Name', 'State ID', 'Count', 'IDs'],
                $duplicates->map(fn ($item) => [
                    $item->name,
                    $item->state_id,
                    $item->count,
                    str_replace(['{', '}'], '', $item->ids),
                ])
            );
        } else {
            foreach ($duplicates as $duplicate) {
                $stateName = State::find($duplicate->state_id)?->name ?? 'Unknown';
                $this->line("     - \"{$duplicate->name}\" in state \"{$stateName}\" (ID: {$duplicate->state_id}) appears {$duplicate->count} times (IDs: ".str_replace(['{', '}'], '', $duplicate->ids).')');
            }
        }

        return true;
    }

    /**
     * Check for duplicate centers
     */
    protected function checkCenterDuplicates(): bool
    {
        $this->line('🏢 <fg=cyan>Checking Centers (unique on: name + city_id)</>');

        $duplicates = DB::table('centers')
            ->select('name', 'city_id', DB::raw('COUNT(*) as count'), DB::raw('array_agg(id) as ids'))
            ->groupBy('name', 'city_id')
            ->having(DB::raw('COUNT(*)'), '>', 1)
            ->get();

        if ($duplicates->isEmpty()) {
            $this->info('   ✓ No duplicate centers found');

            return false;
        }

        $this->error("   ✗ Found {$duplicates->count()} duplicate center combination(s)");

        if ($this->option('show-details')) {
            $this->table(
                ['Name', 'City ID', 'Count', 'IDs'],
                $duplicates->map(fn ($item) => [
                    $item->name,
                    $item->city_id,
                    $item->count,
                    str_replace(['{', '}'], '', $item->ids),
                ])
            );
        } else {
            foreach ($duplicates as $duplicate) {
                $city = City::find($duplicate->city_id);
                $cityName = $city?->name ?? 'Unknown';
                $stateName = $city?->state?->name ?? 'Unknown';
                $this->line("     - \"{$duplicate->name}\" in city \"{$cityName}\" (state: {$stateName}, city ID: {$duplicate->city_id}) appears {$duplicate->count} times (IDs: ".str_replace(['{', '}'], '', $duplicate->ids).')');
            }
        }

        return true;
    }
}
