<?php

namespace App\Console\Commands;

use App\Models\AdoptionApplication;
use App\Models\AdoptionApplicationNote;
use App\Models\Pet;
use App\Models\User;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;

class MigrateLegacyAdoptions extends Command
{
    protected $signature = 'migrate:legacy-adoptions
                            {--dry-run : Run without persisting data}
                            {--skip-users : Skip migrating users from users table}
                            {--limit= : Limit number of adoptions to migrate}';

    protected $description = 'Migrate legacy adoption applications and users from SQL dump';

    private int $usersCreated = 0;

    private int $usersUpdated = 0;

    private int $usersSkipped = 0;

    private int $adoptionsCreated = 0;

    private int $adoptionsSkipped = 0;

    private int $adoptionsFailed = 0;

    private int $petsNotFound = 0;

    /** @var array<string, User> */
    private array $userCache = [];

    public function handle(): int
    {
        $this->info('Legacy Adoption Migration');
        $this->info('========================');
        $this->newLine();

        if ($this->option('dry-run')) {
            $this->warn('DRY RUN MODE - No data will be persisted');
            $this->newLine();
        }

        try {
            $this->performPreflightChecks();

            if (! $this->option('skip-users')) {
                $this->migrateUsers();
            }

            $this->migrateAdoptions();
            $this->displaySummary();

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error("Migration failed: {$e->getMessage()}");
            $this->error("Stack trace: {$e->getTraceAsString()}");

            return Command::FAILURE;
        }
    }

    private function performPreflightChecks(): void
    {
        $this->info('Running preflight checks...');

        // Ensure temp tables exist
        try {
            $userCount = DB::table('temp_legacy_users')->count();
            $adoptionCount = DB::table('temp_legacy_adoptions')->count();
            $this->info("✓ Found {$userCount} users and {$adoptionCount} adoptions in temp tables");
        } catch (\Exception $e) {
            throw new \Exception('Temporary tables not found. Run "php artisan legacy:load-temp-tables load" first.', previous: $e);
        }

        $this->newLine();
    }

    private function migrateUsers(): void
    {
        $userCount = DB::table('temp_legacy_users')->count();

        if ($userCount === 0) {
            $this->warn('No users found in SQL dump');

            return;
        }

        $this->info("Migrating {$userCount} users from users table...");
        $this->newLine();

        $progressBar = $this->output->createProgressBar($userCount);
        $progressBar->start();

        $users = DB::table('temp_legacy_users')->get();

        foreach ($users as $oldUser) {
            $progressBar->advance();

            if (DB::table('legacy_user_migrations')->where('old_user_id', $oldUser->id)->exists()) {
                $this->usersSkipped++;

                continue;
            }

            try {
                $user = $this->createOrUpdateUser($oldUser);

                if (! $this->option('dry-run')) {
                    DB::table('legacy_user_migrations')->insert([
                        'old_user_id' => $oldUser->id,
                        'new_user_id' => $user->id,
                        'migrated_at' => now(),
                    ]);
                }
            } catch (\Exception $e) {
                $this->newLine();
                $this->warn("Failed to migrate user {$oldUser->email}: {$e->getMessage()}");
                $this->usersSkipped++;
            }
        }

        $progressBar->finish();
        $this->newLine(2);
    }

    private function createOrUpdateUser(object $oldUser): User
    {
        $email = $oldUser->email;

        if (! $email) {
            throw new \Exception('User has no email address');
        }

        $existingUser = User::where('email', $email)->first();

        $userData = [
            'name' => trim(($oldUser->first_name ?? '').' '.($oldUser->last_name ?? '')),
            'email' => $email,
            'password' => Hash::make('password'),
            'is_admin' => true,
            'email_verified_at' => now(),
        ];

        if ($existingUser) {
            if (! $this->option('dry-run')) {
                $existingUser->update($userData);
            }
            $this->usersUpdated++;

            return $existingUser;
        }

        if ($this->option('dry-run')) {
            $user = new User($userData);
            $user->id = 999999;

            return $user;
        }

        $user = new User($userData);
        $user->save();

        if ($oldUser->created) {
            $user->created_at = $oldUser->created;
        }
        if ($oldUser->modified) {
            $user->updated_at = $oldUser->modified;
        }
        $user->save();

        $this->usersCreated++;

        return $user;
    }

    private function migrateAdoptions(): void
    {
        $query = DB::table('temp_legacy_adoptions');

        if ($limit = $this->option('limit')) {
            $query->limit((int) $limit);
        }

        $adoptions = $query->get();
        $adoptionCount = $adoptions->count();

        if ($adoptionCount === 0) {
            $this->warn('No adoptions found in SQL dump');

            return;
        }

        $this->info("Migrating {$adoptionCount} adoption applications...");
        $this->newLine();

        $progressBar = $this->output->createProgressBar($adoptionCount);
        $progressBar->start();

        foreach ($adoptions as $oldAdoption) {
            if (DB::table('legacy_adoption_migrations')->where('old_adoption_id', $oldAdoption->id)->exists()) {
                $this->adoptionsSkipped++;
                $progressBar->advance();

                continue;
            }

            try {
                $user = $this->createOrFindUserFromAdoption($oldAdoption);
                $pet = $this->findPetByName($oldAdoption->what_animal);

                if (! $pet) {
                    $this->petsNotFound++;
                }

                $progressBar->clear();
                $progressBar->display();

                $application = $this->createAdoptionApplication($oldAdoption, $user, $pet);

                if (! $this->option('dry-run')) {
                    $this->createApplicationAnswers($application, $oldAdoption);
                    $this->createAdminNote($application, $oldAdoption);

                    DB::table('legacy_adoption_migrations')->insert([
                        'old_adoption_id' => $oldAdoption->id,
                        'new_adoption_application_id' => $application->id,
                        'migrated_at' => now(),
                    ]);
                }

                $this->adoptionsCreated++;
            } catch (\Exception $e) {
                $progressBar->clear();
                $this->warn("Failed to migrate adoption {$oldAdoption->id}: {$e->getMessage()}");
                $progressBar->display();
                $this->adoptionsFailed++;
            }

            $progressBar->advance();
        }

        $progressBar->finish();
        $this->newLine(2);
    }

    private function createOrFindUserFromAdoption(object $oldAdoption): User
    {
        $email = $oldAdoption->email;

        if (! $email) {
            throw new \Exception('Adoption has no email address');
        }

        // Check cache first
        if (isset($this->userCache[$email])) {
            $existingUser = $this->userCache[$email];
        } else {
            $existingUser = User::where('email', $email)->first();
            if ($existingUser) {
                $this->userCache[$email] = $existingUser;
            }
        }

        $firstName = $oldAdoption->first_name ?? null;
        $lastName = $oldAdoption->last_name ?? null;
        $phone = $oldAdoption->phone_number ?? null;
        $address = trim(($oldAdoption->address ?? '').' '.($oldAdoption->city ?? '').' '.($oldAdoption->province ?? '').' '.($oldAdoption->postal_code ?? ''));

        if ($existingUser) {
            if (! $this->option('dry-run')) {
                $existingUser->update([
                    'phone' => $phone ?: $existingUser->phone,
                    'address' => $address ?: $existingUser->address,
                ]);
            }

            return $existingUser;
        }

        if ($this->option('dry-run')) {
            $user = new User([
                'first_name' => $firstName,
                'last_name' => $lastName,
                'email' => $email,
                'password' => Hash::make(Str::random(32)),
                'phone' => $phone,
                'address' => $address,
            ]);
            $user->id = 999999;

            return $user;
        }

        $user = new User([
            'first_name' => $firstName,
            'last_name' => $lastName,
            'email' => $email,
            'password' => Hash::make(Str::random(32)),
            'phone' => $phone,
            'address' => $address,
        ]);
        $user->save();

        if ($oldAdoption->created) {
            $user->created_at = $oldAdoption->created;
        }
        if ($oldAdoption->modified) {
            $user->updated_at = $oldAdoption->modified;
        }
        $user->save();

        // Cache the new user
        $this->userCache[$email] = $user;

        return $user;
    }

    private function findPetByName(?string $name): ?Pet
    {
        if (! $name) {
            return null;
        }

        // Try to extract Reference ID from patterns like "BUFFY Reference ID: 784"
        if (preg_match('/Reference ID:\s*(\d+)/i', $name, $matches)) {
            $legacyPetId = (int) $matches[1];

            // Look up the new pet ID from the legacy migration table
            $migration = DB::table('legacy_pet_migrations')
                ->where('old_pet_id', $legacyPetId)
                ->first();

            if ($migration) {
                return Pet::find($migration->new_pet_id);
            }

            // Fallback: try to find the pet directly by ID (for tests or if migration table is empty)
            $pet = Pet::find($legacyPetId);
            if ($pet) {
                return $pet;
            }
        }

        // Try to extract ID from patterns like "Oreo - 792"
        if (preg_match('/\s-\s(\d+)$/i', $name, $matches)) {
            $legacyPetId = (int) $matches[1];

            $migration = DB::table('legacy_pet_migrations')
                ->where('old_pet_id', $legacyPetId)
                ->first();

            if ($migration) {
                return Pet::find($migration->new_pet_id);
            }
        }

        // Try to match by exact pet name
        $cleanName = trim($name);
        $pet = Pet::where('name', $cleanName)->first();

        if ($pet) {
            return $pet;
        }

        return null;
    }

    private function createAdoptionApplication(object $oldAdoption, User $user, ?Pet $pet): AdoptionApplication
    {
        $status = $this->mapStatus($oldAdoption->status);

        if ($this->option('dry-run')) {
            $application = new AdoptionApplication([
                'user_id' => $user->id,
                'pet_id' => $pet?->id,
                'status' => $status,
            ]);
            $application->id = 999999;

            return $application;
        }

        $application = new AdoptionApplication([
            'user_id' => $user->id,
            'pet_id' => $pet?->id,
            'status' => $status,
        ]);
        $application->save();

        if ($oldAdoption->created) {
            $application->created_at = $oldAdoption->created;
        }
        if ($oldAdoption->modified) {
            $application->updated_at = $oldAdoption->modified;
        }
        $application->save();

        // Handle pet status update for approved applications
        if ($status === 'approved' && $pet) {
            $adoptionDate = $oldAdoption->modified ?? $oldAdoption->created ?? null;

            // Only set adoption_date if it's not in the future
            if ($adoptionDate && (int) strtotime($adoptionDate) <= time()) {
                $pet->update([
                    'status' => 'adopted',
                    'adoption_date' => $adoptionDate,
                ]);
            } else {
                $pet->update(['status' => 'adopted']);
            }
        }

        return $application;
    }

    private function mapStatus(int $oldStatus): string
    {
        return match ($oldStatus) {
            0 => 'submitted',
            1 => 'approved',
            2 => 'rejected',
            3 => 'archived',
            default => 'archived',
        };
    }

    private function createApplicationAnswers(AdoptionApplication $application, object $oldAdoption): void
    {
        $questions = [
            [
                'field' => 'adoption_reason',
                'label' => 'Reason for adoption',
                'type' => 'text',
                'sort_order' => 1,
                'mapper' => 'mapAdoptionReason',
            ],
            [
                'field' => 'adopting_for',
                'label' => 'Who are you adopting for?',
                'type' => 'text',
                'sort_order' => 2,
                'mapper' => 'mapAdoptingFor',
            ],
            [
                'field' => 'birth_range',
                'label' => 'Age range',
                'type' => 'text',
                'sort_order' => 3,
                'mapper' => 'mapBirthRange',
            ],
            [
                'field' => 'housing_type',
                'label' => 'Housing type',
                'type' => 'text',
                'sort_order' => 4,
                'mapper' => 'mapHousingType',
            ],
            [
                'field' => 'own_rent',
                'label' => 'Do you own or rent?',
                'type' => 'text',
                'sort_order' => 5,
                'mapper' => 'mapOwnRent',
            ],
            [
                'field' => 'landlord_consent',
                'label' => 'Do you have landlord consent?',
                'type' => 'text',
                'sort_order' => 6,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'household_adults',
                'label' => 'Number of adults in household',
                'type' => 'number',
                'sort_order' => 7,
            ],
            [
                'field' => 'household_children',
                'label' => 'Number of children in household',
                'type' => 'number',
                'sort_order' => 8,
            ],
            [
                'field' => 'children_ages',
                'label' => 'Ages of children',
                'type' => 'text',
                'sort_order' => 9,
            ],
            [
                'field' => 'child_experiences',
                'label' => 'Children\'s experience with pets',
                'type' => 'text',
                'sort_order' => 10,
            ],
            [
                'field' => 'student_ind',
                'label' => 'Are you a student?',
                'type' => 'text',
                'sort_order' => 11,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'school_name',
                'label' => 'School name',
                'type' => 'text',
                'sort_order' => 12,
            ],
            [
                'field' => 'year_level',
                'label' => 'Year level',
                'type' => 'text',
                'sort_order' => 13,
            ],
            [
                'field' => 'outside_pet',
                'label' => 'Will this be an outside pet?',
                'type' => 'text',
                'sort_order' => 14,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'outside_harness',
                'label' => 'Outside harness details',
                'type' => 'text',
                'sort_order' => 15,
            ],
            [
                'field' => 'current_pets',
                'label' => 'Do you have current pets?',
                'type' => 'text',
                'sort_order' => 16,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'current_pet_details',
                'label' => 'Current pet details',
                'type' => 'text',
                'sort_order' => 17,
            ],
            [
                'field' => 'spayed_neutered',
                'label' => 'Are your pets spayed/neutered?',
                'type' => 'text',
                'sort_order' => 18,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'reason_why_not_spayed',
                'label' => 'Reason why not spayed/neutered',
                'type' => 'text',
                'sort_order' => 19,
            ],
            [
                'field' => 'vaccinations_current',
                'label' => 'Are vaccinations current?',
                'type' => 'text',
                'sort_order' => 20,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'reason_why_not',
                'label' => 'Reason why vaccinations not current',
                'type' => 'text',
                'sort_order' => 21,
            ],
            [
                'field' => 'previous_pets',
                'label' => 'Previous pets',
                'type' => 'text',
                'sort_order' => 22,
            ],
            [
                'field' => 'previous_surrender',
                'label' => 'Have you surrendered a pet before?',
                'type' => 'text',
                'sort_order' => 23,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'declaw_plans',
                'label' => 'Do you plan to declaw?',
                'type' => 'text',
                'sort_order' => 24,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'why_give_up',
                'label' => 'Why did you give up previous pet?',
                'type' => 'text',
                'sort_order' => 25,
            ],
            [
                'field' => 'permit_home_visit',
                'label' => 'Do you permit a home visit?',
                'type' => 'text',
                'sort_order' => 26,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'vet_visits',
                'label' => 'Frequency of vet visits',
                'type' => 'text',
                'sort_order' => 27,
            ],
            [
                'field' => 'monthly_food_litter',
                'label' => 'Monthly budget for food/litter',
                'type' => 'text',
                'sort_order' => 28,
                'mapper' => 'mapMonthlyBudget',
            ],
            [
                'field' => 'yearly_vet_bills',
                'label' => 'Yearly budget for vet bills',
                'type' => 'text',
                'sort_order' => 29,
                'mapper' => 'mapYearlyBudget',
            ],
            [
                'field' => 'employment_status',
                'label' => 'Employment status',
                'type' => 'text',
                'sort_order' => 30,
            ],
            [
                'field' => 'hours_alone',
                'label' => 'Hours pet will be alone',
                'type' => 'number',
                'sort_order' => 31,
            ],
            [
                'field' => 'lifestyle',
                'label' => 'Lifestyle description',
                'type' => 'text',
                'sort_order' => 32,
            ],
            [
                'field' => 'vet_clinic',
                'label' => 'Vet clinic name',
                'type' => 'text',
                'sort_order' => 33,
            ],
            [
                'field' => 'vet_city',
                'label' => 'Vet clinic city',
                'type' => 'text',
                'sort_order' => 34,
            ],
            [
                'field' => 'vet_phone',
                'label' => 'Vet clinic phone',
                'type' => 'text',
                'sort_order' => 35,
            ],
            [
                'field' => 'vet_records',
                'label' => 'Vet records',
                'type' => 'text',
                'sort_order' => 36,
                'mapper' => 'mapYesNo',
            ],
            [
                'field' => 'years_with_vet',
                'label' => 'Years with current vet',
                'type' => 'text',
                'sort_order' => 37,
            ],
            [
                'field' => 'other_vets',
                'label' => 'Other vets',
                'type' => 'text',
                'sort_order' => 38,
            ],
            [
                'field' => 'ref_name',
                'label' => 'Reference name',
                'type' => 'text',
                'sort_order' => 39,
            ],
            [
                'field' => 'ref_relationship',
                'label' => 'Reference relationship',
                'type' => 'text',
                'sort_order' => 40,
            ],
            [
                'field' => 'ref_city',
                'label' => 'Reference city',
                'type' => 'text',
                'sort_order' => 41,
            ],
            [
                'field' => 'ref_phone',
                'label' => 'Reference phone',
                'type' => 'text',
                'sort_order' => 42,
            ],
            [
                'field' => 'emp_name',
                'label' => 'Employer name',
                'type' => 'text',
                'sort_order' => 43,
            ],
            [
                'field' => 'emp_city',
                'label' => 'Employer city',
                'type' => 'text',
                'sort_order' => 44,
            ],
            [
                'field' => 'emp_phone',
                'label' => 'Employer phone',
                'type' => 'text',
                'sort_order' => 45,
            ],
        ];

        $answersToInsert = [];
        $now = now();

        foreach ($questions as $question) {
            $fieldValue = $oldAdoption->{$question['field']} ?? null;

            if ($fieldValue === null || $fieldValue === '') {
                continue;
            }

            // Apply mapper if specified
            if (isset($question['mapper']) && method_exists($this, $question['mapper'])) {
                $answer = $this->{$question['mapper']}($fieldValue);
            } else {
                $answer = (string) $fieldValue;
            }

            // Skip if mapper returned null or empty string
            if ($answer === null || $answer === '') {
                continue;
            }

            $questionSnapshot = [
                'label' => $question['label'],
                'type' => $question['type'],
            ];

            $answersToInsert[] = [
                'answerable_type' => AdoptionApplication::class,
                'answerable_id' => $application->id,
                'question_snapshot' => json_encode($questionSnapshot),
                'answer' => $answer,
                'sort_order' => $question['sort_order'],
                'created_at' => $application->created_at ?? $now,
                'updated_at' => $application->updated_at ?? $now,
            ];
        }

        // Batch insert all answers at once
        if (! empty($answersToInsert)) {
            DB::table('application_answers')->insert($answersToInsert);
        }
    }

    private function createAdminNote(AdoptionApplication $application, object $oldAdoption): void
    {
        $adminNotes = $oldAdoption->admin_notes ?? null;

        if (! $adminNotes || trim($adminNotes) === '') {
            return;
        }

        $note = new AdoptionApplicationNote([
            'adoption_application_id' => $application->id,
            'note' => $adminNotes,
            'created_by' => null,
        ]);
        $note->save();

        if ($application->created_at) {
            $note->created_at = $application->created_at;
            $note->updated_at = $application->updated_at;
            $note->save();
        }
    }

    private function displaySummary(): void
    {
        $this->info('Migration Summary');
        $this->info('=================');
        $this->newLine();

        $this->info('Users:');
        $this->info("  Created: {$this->usersCreated}");
        $this->info("  Updated: {$this->usersUpdated}");
        $this->info("  Skipped: {$this->usersSkipped}");
        $this->newLine();

        $this->info('Adoption Applications:');
        $this->info("  Created: {$this->adoptionsCreated}");
        $this->info("  Skipped: {$this->adoptionsSkipped}");
        $this->info("  Failed: {$this->adoptionsFailed}");
        $this->info("  Pets Not Found: {$this->petsNotFound}");
        $this->newLine();

        if ($this->option('dry-run')) {
            $this->warn('DRY RUN COMPLETE - No data was persisted');
        } else {
            $this->info('Migration complete!');
        }
    }

    /**
     * @param  mixed  $value
     */
    private function mapYesNo($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => 'No',
            1 => 'Yes',
            default => null,
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapAdoptionReason($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => 'Companion for myself',
            1 => 'Companion for another pet',
            2 => 'Gift for someone else',
            3 => 'Therapy/emotional support animal',
            4 => 'Other',
            default => 'Not specified',
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapAdoptingFor($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => 'Myself',
            1 => 'My family',
            2 => 'A child',
            3 => 'Someone else',
            4 => 'Other',
            default => 'Not specified',
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapBirthRange($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => '18-25',
            1 => '26-35',
            2 => '36-45',
            3 => '46-55',
            4 => '56-65',
            5 => '66+',
            default => 'Not specified',
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapHousingType($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => 'House',
            1 => 'Apartment',
            2 => 'Condo',
            3 => 'Townhouse',
            4 => 'Mobile home',
            5 => 'Farm',
            6 => 'Other',
            default => 'Not specified',
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapOwnRent($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => 'Own',
            1 => 'Rent',
            2 => 'Living with family/friends',
            3 => 'Other',
            default => 'Not specified',
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapMonthlyBudget($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => '$0-25',
            1 => '$25-50',
            2 => '$50-75',
            3 => '$75-100',
            4 => '$100-150',
            5 => '$150+',
            default => 'Not specified',
        };
    }

    /**
     * @param  mixed  $value
     */
    private function mapYearlyBudget($value): ?string
    {
        if ($value === null || $value === '') {
            return null;
        }

        $intValue = (int) $value;

        return match ($intValue) {
            0 => '$0-100',
            1 => '$100-250',
            2 => '$250-500',
            3 => '$500-1000',
            4 => '$1000-2000',
            5 => '$2000+',
            default => 'Not specified',
        };
    }
}
