<?php

namespace App\Console\Commands\Migrations;

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

class AssociationLocationMigration extends BaseMigration
{
    /**
     * Get the model class
     */
    protected function getModelClass(): string
    {
        return Association::class;
    }

    /**
     * Migrate association location relationships from old database
     */
    public function migrate(bool $dryRun = false, int $batchSize = 100, int $startFrom = 0): void
    {
        $this->command->info('📍 Starting association locations migration...');

        $oldDb = DB::connection($this->connection);

        // Complex query to get unique association locations by joining with users table
        // Using DISTINCT to avoid duplicates
        $query = $oldDb->table('sek_association_locations as sal')
            ->leftJoin('sek_users as su', 'sal.user_id', '=', 'su.id')
            ->select([
                'sal.user_id',
                'su.association_id',
                'sal.state_id',
                'sal.city_id',
                'sal.center_id',
            ])
            ->whereNotNull('su.association_id')
            ->where('su.association_id', '>', 0)
            ->distinct();

        // Get total count
        $totalCount = $query->count();
        $this->stats['total'] = $totalCount;

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

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

            return;
        }

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

        $query->orderBy('sal.user_id')
            ->skip($startFrom)
            ->chunk($batchSize, function ($oldLocations) use ($dryRun, &$processed, $bar) {
                foreach ($oldLocations as $oldLocation) {
                    try {
                        $mappedData = $this->mapData($oldLocation);

                        if ($this->validateData($mappedData)) {
                            if (! $dryRun) {
                                $this->createRelationships($mappedData);
                                $this->stats['migrated']++;
                            } else {
                                $this->stats['migrated']++;
                            }
                        } else {
                            $this->stats['skipped']++;
                            $this->warn("⚠️  Skipped location for user ID {$oldLocation->user_id}: validation failed");
                        }
                    } catch (\Exception $e) {
                        $this->stats['errors']++;
                        $this->error("❌ Error migrating location for user ID {$oldLocation->user_id}: ".$e->getMessage());
                    }

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

        $bar->finish();
        $this->command->newLine();
        $this->info('✅ Association locations migration completed');
    }

    /**
     * Get the model name for display purposes
     */
    protected function getModelName(): string
    {
        return 'AssociationLocation';
    }

    /**
     * Map old database data to new model structure
     */
    protected function mapData(object $oldLocation): array
    {
        $data = [
            'association_id' => $oldLocation->association_id,
            'state_id' => $oldLocation->state_id,
            'city_id' => $oldLocation->city_id,
            'center_id' => $oldLocation->center_id,
            'user_id' => $oldLocation->user_id, // For reference in logs
        ];

        return $data;
    }

    /**
     * Validate the mapped data
     */
    protected function validateData(array $data): bool
    {
        $validator = Validator::make($data, [
            'association_id' => 'required|integer|exists:associations,id',
            'state_id' => 'nullable|integer|exists:states,id',
            'city_id' => 'nullable|integer|exists:cities,id',
            'center_id' => 'nullable|integer|exists:centers,id',
        ]);

        if ($validator->fails()) {
            $this->error('Validation errors: '.implode(', ', $validator->errors()->all()));

            return false;
        }

        return true;
    }

    /**
     * Create the relationship records in pivot tables
     */
    protected function createRelationships(array $data): void
    {
        $association = Association::find($data['association_id']);

        if (! $association) {
            throw new \Exception("Association {$data['association_id']} not found");
        }

        // 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);
    }
}
