<?php

namespace App\Console\Commands\Migrations;

use App\Models\Association;
use Illuminate\Support\Facades\DB;

class AssociationLocationMigrationOptimized extends BaseMigration
{
    protected string $oldTableName = 'sek_association_locations';

    protected string $modelName = 'Association Location Relationships';

    public function migrate(bool $dryRun = false, int $batchSize = 1000, int $startFrom = 0): void
    {
        $oldDb = DB::connection($this->connection);
        $this->processData($oldDb, $dryRun, $startFrom, $batchSize);
    }

    protected function getModelName(): string
    {
        return 'Association Location Relationships';
    }

    protected function getModelClass(): string
    {
        return 'App\\Models\\Association';
    }

    protected function processData($oldDb, bool $dryRun = false, int $startFrom = 0, int $batchSize = 1000): void
    {
        // Get unique association-location combinations using a more efficient approach
        $this->info('🔍 Finding unique association-location combinations...');

        $uniqueLocations = $oldDb->table('sek_association_locations as sal')
            ->leftJoin('sek_users as su', 'sal.user_id', '=', 'su.id')
            ->select([
                'su.association_id',
                'sal.state_id',
                'sal.city_id',
                'sal.center_id',
            ])
            ->whereNotNull('su.association_id')
            ->where('su.association_id', '>', 0)
            ->groupBy(['su.association_id', 'sal.state_id', 'sal.city_id', 'sal.center_id'])
            ->get();

        $totalCount = $uniqueLocations->count();
        $this->stats['total'] = $totalCount;

        $this->info("📊 Found {$totalCount} unique association location combinations to migrate");

        if ($totalCount === 0) {
            $this->warn('⚠️  No association locations found to migrate');

            return;
        }

        // Process unique combinations
        $processed = 0;
        $bar = $this->command->getOutput()->createProgressBar($totalCount);

        foreach ($uniqueLocations as $locationData) {
            try {
                $mappedData = $this->mapData($locationData);

                if ($this->validateData($mappedData)) {
                    if (! $dryRun) {
                        $this->createRelationships($mappedData);
                        $this->stats['migrated']++;
                    } else {
                        $this->info("🔍 [DRY RUN] Would migrate association {$mappedData['association_id']} locations");
                    }
                } else {
                    $this->stats['skipped']++;
                }
            } catch (\Exception $e) {
                $this->stats['errors']++;
                $this->error("❌ Error migrating location for association ID {$locationData->association_id}: ".$e->getMessage());
            }

            $processed++;
            $bar->advance();
        }

        $bar->finish();
        $this->command->info('');
    }

    protected function mapData(object $oldRecord): array
    {
        return [
            'association_id' => (int) $oldRecord->association_id,
            'state_id' => ! empty($oldRecord->state_id) ? (int) $oldRecord->state_id : null,
            'city_id' => ! empty($oldRecord->city_id) ? (int) $oldRecord->city_id : null,
            'center_id' => ! empty($oldRecord->center_id) ? (int) $oldRecord->center_id : null,
        ];
    }

    protected function validateData(array $data): bool
    {
        // Must have association_id
        if (empty($data['association_id'])) {
            $this->warn('⚠️  Skipping record: missing association_id');

            return false;
        }

        // Must have at least one location (state, city, or center)
        if (empty($data['state_id']) && empty($data['city_id']) && empty($data['center_id'])) {
            $this->warn("⚠️  Skipping association {$data['association_id']}: no location data");

            return false;
        }

        // Validate association exists
        $association = Association::find($data['association_id']);
        if (! $association) {
            $this->warn("⚠️  Skipping: Association {$data['association_id']} not found");

            return false;
        }

        return true;
    }

    /**
     * Create the pivot table relationships for association locations
     */
    private function createRelationships(array $data): void
    {
        $association = Association::find($data['association_id']);

        // Create state relationship if state_id exists
        if (! empty($data['state_id'])) {
            if (! $association->states()->where('state_id', $data['state_id'])->exists()) {
                $association->states()->attach($data['state_id']);
                $this->info("📝 Linked association {$data['association_id']} to state {$data['state_id']}");
            } else {
                $this->info("⚠️  Association {$data['association_id']} already linked to state {$data['state_id']}");
            }
        }

        // Create city relationship if city_id exists
        if (! empty($data['city_id'])) {
            if (! $association->cities()->where('city_id', $data['city_id'])->exists()) {
                $association->cities()->attach($data['city_id']);
                $this->info("📝 Linked association {$data['association_id']} to city {$data['city_id']}");
            } else {
                $this->info("⚠️  Association {$data['association_id']} already linked to city {$data['city_id']}");
            }
        }

        // Create center relationship if center_id exists
        if (! empty($data['center_id'])) {
            if (! $association->centers()->where('center_id', $data['center_id'])->exists()) {
                $association->centers()->attach($data['center_id']);
                $this->info("📝 Linked association {$data['association_id']} to center {$data['center_id']}");
            } else {
                $this->info("⚠️  Association {$data['association_id']} already linked to center {$data['center_id']}");
            }
        }
    }

    /**
     * Override createModel since we're not creating a single model
     */
    protected function createModel(array $data): void
    {
        $this->createRelationships($data);
    }
}
