<?php

namespace App\Console\Commands\Migrations;

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

class AssociationLocationMigrationFinal 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 associations first
        $this->info('🔍 Finding associations with location data...');

        $associations = $oldDb->table('sek_association_locations as sal')
            ->leftJoin('sek_users as su', 'sal.user_id', '=', 'su.id')
            ->whereNotNull('su.association_id')
            ->where('su.association_id', '>', 0)
            ->distinct('su.association_id')
            ->pluck('su.association_id');

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

        $this->info("📊 Found {$totalCount} unique associations with location data");

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

            return;
        }

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

        foreach ($associations as $associationId) {
            try {
                // Get all unique location combinations for this association
                $locations = $oldDb->table('sek_association_locations as sal')
                    ->leftJoin('sek_users as su', 'sal.user_id', '=', 'su.id')
                    ->where('su.association_id', $associationId)
                    ->select(['sal.state_id', 'sal.city_id', 'sal.center_id'])
                    ->distinct()
                    ->get();

                if (! $dryRun) {
                    $this->migrateAssociationLocations($associationId, $locations);
                } else {
                    $this->info("🔍 [DRY RUN] Would migrate {$locations->count()} location combinations for association {$associationId}");
                }

                $this->stats['migrated']++;
            } catch (\Exception $e) {
                $this->stats['errors']++;
                $this->error("❌ Error migrating locations for association ID {$associationId}: ".$e->getMessage());
            }

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

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

    private function migrateAssociationLocations(int $associationId, $locations): void
    {
        $association = Association::find($associationId);
        if (! $association) {
            $this->warn("⚠️  Association {$associationId} not found, skipping");

            return;
        }

        $statesLinked = 0;
        $citiesLinked = 0;
        $centersLinked = 0;

        foreach ($locations as $location) {
            // Link to states
            if (! empty($location->state_id) && ! $association->states()->where('state_id', $location->state_id)->exists()) {
                $association->states()->attach($location->state_id);
                $statesLinked++;
            }

            // Link to cities
            if (! empty($location->city_id) && ! $association->cities()->where('city_id', $location->city_id)->exists()) {
                $association->cities()->attach($location->city_id);
                $citiesLinked++;
            }

            // Link to centers
            if (! empty($location->center_id) && ! $association->centers()->where('center_id', $location->center_id)->exists()) {
                $association->centers()->attach($location->center_id);
                $centersLinked++;
            }
        }

        if ($statesLinked > 0 || $citiesLinked > 0 || $centersLinked > 0) {
            $this->info("📝 Association {$associationId}: linked {$statesLinked} states, {$citiesLinked} cities, {$centersLinked} centers");
        }
    }

    protected function mapData(object $oldRecord): array
    {
        // Not used in this implementation
        return [];
    }

    protected function validateData(array $data): bool
    {
        // Not used in this implementation
        return true;
    }

    protected function createModel(array $data): void
    {
        // Not used in this implementation
    }
}
