<?php

namespace App\Console\Commands;

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

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Merge duplicate centers by updating foreign key references and deleting duplicates';

    protected int $mergedGroups = 0;

    protected int $centersMerged = 0;

    protected int $referencesUpdated = 0;

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $this->info('🔍 Searching for duplicate centers...');
        $this->newLine();

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

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

            return self::SUCCESS;
        }

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

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

        // Process each duplicate group
        foreach ($duplicates as $duplicate) {
            $this->processDuplicateGroup($duplicate);
        }

        $this->newLine();
        $this->info('📊 Summary:');
        $this->table(
            ['Metric', 'Count'],
            [
                ['Duplicate Groups Processed', $this->mergedGroups],
                ['Centers Merged (Deleted)', $this->centersMerged],
                ['Foreign Key References Updated', $this->referencesUpdated],
            ]
        );

        if ($this->option('dry-run')) {
            $this->newLine();
            $this->line('<fg=yellow>This was a dry run. Run without --dry-run to apply changes.</>');
        }

        return self::SUCCESS;
    }

    /**
     * Process a group of duplicate centers
     */
    protected function processDuplicateGroup(object $duplicate): void
    {
        $ids = array_map('intval', explode(',', trim($duplicate->ids, '{}')));
        $city = City::find($duplicate->city_id);
        $cityName = $city?->name ?? 'Unknown';
        $stateName = $city?->state?->name ?? 'Unknown';

        $this->line('─────────────────────────────────────────────────────────────');
        $this->line("<fg=cyan>Processing: \"{$duplicate->name}\"</>");
        $this->line("City: {$cityName} (State: {$stateName})");
        $this->line("Found {$duplicate->count} duplicates with IDs: ".implode(', ', $ids));
        $this->newLine();

        // Select primary center
        $primaryId = $this->selectPrimaryCenter($ids, $duplicate->name);

        if (! $primaryId) {
            $this->warn('⚠️  Skipped - No primary center selected');
            $this->newLine();

            return;
        }

        $duplicateIds = array_filter($ids, fn ($id) => $id !== $primaryId);

        $this->info("✓ Primary center: ID {$primaryId}");
        $this->line('  Merging duplicates: '.implode(', ', $duplicateIds));
        $this->newLine();

        // Get reference counts before merging
        $references = $this->getReferenceCounts($duplicateIds);
        $this->displayReferenceCounts($references);

        if (! $this->option('dry-run')) {
            DB::transaction(function () use ($primaryId, $duplicateIds, &$references) {
                $this->mergeCenters($primaryId, $duplicateIds, $references);
            });
        }

        $this->mergedGroups++;
        $this->centersMerged += count($duplicateIds);
        $this->newLine();
    }

    /**
     * Select which center to keep as primary
     */
    protected function selectPrimaryCenter(array $ids, string $name): ?int
    {
        if ($this->option('auto')) {
            // Automatically select the one with lowest ID
            return min($ids);
        }

        // Show details for each center
        $centers = Center::whereIn('id', $ids)
            ->with(['orders', 'associations'])
            ->get();

        $this->table(
            ['ID', 'Families Count', 'Orders', 'Associations', 'Created At'],
            $centers->map(fn ($c) => [
                $c->id,
                $c->families_count ?? 0,
                $c->orders->count(),
                $c->associations->count(),
                $c->created_at?->format('Y-m-d H:i:s') ?? 'N/A',
            ])
        );

        $choice = $this->ask('Which center ID should be kept as primary? (Enter to skip)', (string) min($ids));

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

    /**
     * Get counts of references from related tables
     */
    protected function getReferenceCounts(array $centerIds): array
    {
        return [
            'center_order' => DB::table('center_order')
                ->whereIn('center_id', $centerIds)
                ->count(),
            'association_center' => DB::table('association_center')
                ->whereIn('center_id', $centerIds)
                ->count(),
            'well_licenses' => DB::table('well_licenses')
                ->whereIn('center_id', $centerIds)
                ->count(),
            'station_licenses' => DB::table('station_licenses')
                ->whereIn('center_id', $centerIds)
                ->count(),
        ];
    }

    /**
     * Display reference counts
     */
    protected function displayReferenceCounts(array $references): void
    {
        $total = array_sum($references);

        if ($total === 0) {
            $this->line('  <fg=gray>No foreign key references found</>');

            return;
        }

        $this->line("  <fg=yellow>Foreign key references to update: {$total}</>");

        foreach ($references as $table => $count) {
            if ($count > 0) {
                $this->line("    - {$table}: {$count}");
            }
        }
    }

    /**
     * Merge centers by updating foreign keys and deleting duplicates
     */
    protected function mergeCenters(int $primaryId, array $duplicateIds, array $references): void
    {
        $updated = 0;

        // Merge center_order (many-to-many pivot)
        if ($references['center_order'] > 0) {
            // Get all unique order IDs that are related to any of these centers (including primary)
            $allCenterIds = array_merge([$primaryId], $duplicateIds);
            $orderIds = DB::table('center_order')
                ->whereIn('center_id', $allCenterIds)
                ->distinct()
                ->pluck('order_id')
                ->toArray();

            // Delete all relationships for these orders with any of the centers
            DB::table('center_order')
                ->whereIn('order_id', $orderIds)
                ->whereIn('center_id', $allCenterIds)
                ->delete();

            // Re-insert with primary center only
            $insertData = array_map(fn ($orderId) => [
                'order_id' => $orderId,
                'center_id' => $primaryId,
            ], $orderIds);

            if (! empty($insertData)) {
                DB::table('center_order')->insert($insertData);
                $updated += count($insertData);
            }
        }

        // Merge association_center (many-to-many pivot)
        if ($references['association_center'] > 0) {
            // Get all unique association IDs that are related to any of these centers (including primary)
            $allCenterIds = array_merge([$primaryId], $duplicateIds);
            $associationIds = DB::table('association_center')
                ->whereIn('center_id', $allCenterIds)
                ->distinct()
                ->pluck('association_id')
                ->toArray();

            // Delete all relationships for these associations with any of the centers
            DB::table('association_center')
                ->whereIn('association_id', $associationIds)
                ->whereIn('center_id', $allCenterIds)
                ->delete();

            // Re-insert with primary center only
            $insertData = array_map(fn ($associationId) => [
                'association_id' => $associationId,
                'center_id' => $primaryId,
            ], $associationIds);

            if (! empty($insertData)) {
                DB::table('association_center')->insert($insertData);
                $updated += count($insertData);
            }
        }

        // Update well_licenses
        if ($references['well_licenses'] > 0) {
            $updated += DB::table('well_licenses')
                ->whereIn('center_id', $duplicateIds)
                ->update(['center_id' => $primaryId]);
        }

        // Update station_licenses
        if ($references['station_licenses'] > 0) {
            $updated += DB::table('station_licenses')
                ->whereIn('center_id', $duplicateIds)
                ->update(['center_id' => $primaryId]);
        }

        // Delete duplicate centers
        Center::whereIn('id', $duplicateIds)->delete();

        $this->referencesUpdated += $updated;
        $this->info("  ✓ Updated {$updated} references and deleted ".count($duplicateIds).' duplicate center(s)');
    }
}
