<?php

namespace App\Console\Commands\Migrations;

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

class AssociationMigration extends BaseMigration
{
    /**
     * Execute the association migration
     */
    public function migrate(bool $dryRun, int $batchSize, int $startFrom): void
    {
        $this->info('👥 Starting associations migration...');

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

        // Get total count using the same pattern as FastAPI (User -> Association join)
        $totalCount = $oldDb->table('sek_users')
            ->leftJoin('sek_associations', 'sek_users.association_id', '=', 'sek_associations.id')
            ->whereNotNull('sek_users.association_id')
            ->count();
        $this->stats['total'] = $totalCount;

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

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

            return;
        }

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

        // Query pattern matching FastAPI: User -> Association join
        $oldDb->table('sek_users')
            ->leftJoin('sek_associations', 'sek_users.association_id', '=', 'sek_associations.id')
            ->leftJoin('sek_addresses', 'sek_users.id', '=', 'sek_addresses.user_id')
            ->leftJoin('sek_user_bank_information', 'sek_users.id', '=', 'sek_user_bank_information.user_id')
            ->leftJoin('sek_banks', 'sek_user_bank_information.bank_id', '=', 'sek_banks.id')
            ->whereNotNull('sek_users.association_id')
            ->select(
                'sek_associations.*',
                'sek_users.id as user_id',
                'sek_users.association_id as user_association_id',
                'sek_users.entered_association_name_ar as entered_association_name_ar',
                'sek_users.license_expiration_date as license_expiration_date',
                'sek_users.entered_association_owner_fullname as entered_association_owner_fullname',
                'sek_users.position_at_association as position_at_association',
                'sek_users.mobile_number as mobile_number',
                'sek_users.email as email',
                'sek_users.association_admin_verified_at as association_admin_verified_at',
                'sek_users.is_profile_edit_locked as is_profile_edit_locked',
                'sek_addresses.city_ar as city_ar',
                'sek_addresses.postcode as postcode',
                'sek_addresses.area_ar as area_ar',
                'sek_addresses.building_number as building_number',
                'sek_addresses.street_name_ar as street_name_ar',
                'sek_addresses.additional_number as additional_number',
                'sek_addresses.obj_lat_lng as obj_lat_lng',
                'sek_user_bank_information.iban as iban',
                'sek_user_bank_information.full_name as bank_account_holder',
                'sek_banks.name_ar as bank_name_ar'
            )
            ->orderBy('sek_associations.id')
            ->skip($startFrom)
            ->chunk($batchSize, function ($oldAssociations) use ($dryRun, &$processed, $bar) {
                foreach ($oldAssociations as $oldAssociation) {
                    // Skip if user_association_id is null
                    if (is_null($oldAssociation->user_association_id)) {
                        $this->warn("⚠️  Skipping record with null user_association_id for user ID {$oldAssociation->user_id}");
                        $this->stats['skipped']++;
                        $processed++;
                        $bar->advance();

                        continue;
                    }

                    $this->info("🔄 Migrating association ID {$oldAssociation->id}, named: {$oldAssociation->entered_association_name_ar}, user association_id: {$oldAssociation->user_association_id}");

                    try {
                        $mappedData = $this->mapData($oldAssociation);

                        if ($this->validateData($mappedData)) {
                            if (! $dryRun) {
                                // Check if association with this ID already exists
                                if ($this->recordExists($mappedData['id'])) {
                                    $this->warn("⚠️  Association with ID {$mappedData['id']} already exists, skipping");
                                    $this->stats['skipped']++;
                                } else {
                                    $this->createModel($mappedData);
                                    $this->stats['migrated']++;
                                }
                            } else {
                                $this->stats['migrated']++;
                            }
                        } else {
                            $this->stats['skipped']++;
                            $this->warn("⚠️  Skipped association ID {$oldAssociation->id}: validation failed");
                        }
                    } catch (\Exception $e) {
                        $this->stats['errors']++;
                        $this->error("❌ Error migrating association ID {$oldAssociation->id}: ".$e->getMessage());
                    }

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

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

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

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

    /**
     * Map old association data to new schema
     */
    protected function mapData(object $oldAssociation): array
    {
        // Parse coordinates if available
        $lat = null;
        $lng = null;
        if (! empty($oldAssociation->obj_lat_lng)) {
            $coordinates = $this->parseCoordinates($oldAssociation->obj_lat_lng);
            $lat = $coordinates['lat'];
            $lng = $coordinates['lng'];
        }

        // Prioritize English name, fallback to Arabic
        $name = $oldAssociation->entered_association_name_ar ?? $oldAssociation->name_ar ?? "Association {$oldAssociation->id}";

        $data = [
            'id' => $oldAssociation->id, // Preserve original ID
            'name' => $name,
            'license_number' => $oldAssociation->license_number ?? "LIC-{$oldAssociation->id}",
            'license_expiration_date' => $this->parseDate($oldAssociation->license_expiration_date) ?? now()->addYear(), // Default to 1 year from now if missing
            'representative_full_name' => $oldAssociation->entered_association_owner_fullname ?? 'Unknown Representative',
            'representative_position' => $oldAssociation->position_at_association ?? 'Unknown Position',
            'phone_number' => $oldAssociation->mobile_number ?? '000000000',
            'secondary_phone_number' => null, // Not available in old schema
            'email' => $oldAssociation->email ?? "association{$oldAssociation->id}@example.com",

            // Address information
            'map_location' => null, // Not available in old schema
            'city' => $oldAssociation->city_ar,
            'postal_code' => $oldAssociation->postcode,
            'neighborhood' => $oldAssociation->area_ar,
            'building_number' => $oldAssociation->building_number,
            'street' => $oldAssociation->street_name_ar,
            'branch_number' => $oldAssociation->additional_number,

            // Coordinates
            'lat' => $lat,
            'lng' => $lng,

            // Banking information
            'iban' => $oldAssociation->iban,
            'bank_name' => $oldAssociation->bank_name_ar,
            'account_holder_name' => $oldAssociation->bank_account_holder,

            // Status information
            'is_verified' => ! empty($oldAssociation->association_admin_verified_at),
            'is_locked' => (bool) ($oldAssociation->is_profile_edit_locked ?? false),

            // Timestamps
            'created_at' => $this->parseDateTime($oldAssociation->created_at),
            'updated_at' => $this->parseDateTime($oldAssociation->modified_at ?? $oldAssociation->created_at),
        ];

        // Log file migration needs
        if (! empty($oldAssociation->association_logo)) {
            $this->info("📝 Note: Association {$oldAssociation->id} has logo: {$oldAssociation->association_logo} - manual file migration needed");
        }

        // Log missing data that was filled with defaults
        $missingFields = [];
        if (empty($oldAssociation->license_expiration_date)) {
            $missingFields[] = 'license_expiration_date';
        }
        if (empty($oldAssociation->entered_association_owner_fullname)) {
            $missingFields[] = 'representative_full_name';
        }
        if (empty($oldAssociation->position_at_association)) {
            $missingFields[] = 'representative_position';
        }
        if (empty($oldAssociation->mobile_number)) {
            $missingFields[] = 'phone_number';
        }
        if (empty($oldAssociation->email)) {
            $missingFields[] = 'email';
        }

        if (! empty($missingFields)) {
            $this->info("📝 Association {$oldAssociation->id} missing fields filled with defaults: ".implode(', ', $missingFields));
        }

        return array_filter($data, function ($value) {
            return $value !== null;
        }); // Remove only null values, keep empty strings
    }

    /**
     * Validate association data
     */
    protected function validateData(array $data): bool
    {
        $validator = Validator::make($data, [
            'id' => 'required|integer|min:1',
            'name' => 'required|string|max:255',
            'license_number' => 'required|string|max:255',
            'license_expiration_date' => 'required|date',
            'representative_full_name' => 'required|string|max:255',
            'representative_position' => 'required|string|max:255',
            'phone_number' => 'required|string|max:20',
            'email' => 'required|email|max:255',
            'city' => 'nullable|string|max:255',
            'postal_code' => 'nullable|string|max:20',
            'neighborhood' => 'nullable|string|max:255',
            'building_number' => 'nullable|string|max:20',
            'street' => 'nullable|string|max:255',
            'branch_number' => 'nullable|string|max:20',
            'lat' => 'nullable|numeric|between:-90,90',
            'lng' => 'nullable|numeric|between:-180,180',
            'iban' => 'nullable|string|max:34',
            'bank_name' => 'nullable|string|max:255',
            'account_holder_name' => 'nullable|string|max:255',
            'is_verified' => 'boolean',
            'is_locked' => 'boolean',
        ]);

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

            return false;
        }

        return true;
    }
}
