<?php

namespace App\Console\Commands;

use App\Models\AssistanceApplication;
use App\Models\AssistanceApplicationNote;
use App\Models\User;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Str;

class MigrateLegacyAssistance extends Command
{
    protected $signature = 'migrate:legacy-assistance
                            {--dry-run : Run without persisting data}
                            {--limit= : Limit number of assistance applications to migrate}';

    protected $description = 'Migrate legacy assistance applications from SQL dump';

    private int $assistancesCreated = 0;

    private int $assistancesSkipped = 0;

    private int $assistancesFailed = 0;

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

    public function handle(): int
    {
        $this->info('Legacy Assistance 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();
            $this->migrateAssistances();
            $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 {
            $assistanceCount = DB::table('temp_legacy_assistance')->count();
            $this->info("✓ Found {$assistanceCount} assistance applications 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 migrateAssistances(): void
    {
        $query = DB::table('temp_legacy_assistance');

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

        $assistances = $query->get();
        $assistanceCount = $assistances->count();

        if ($assistanceCount === 0) {
            $this->warn('No assistance applications found in temp tables');

            return;
        }

        $this->info("Migrating {$assistanceCount} assistance applications...");
        $this->newLine();

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

        foreach ($assistances as $oldAssistance) {
            if (DB::table('legacy_assistance_migrations')->where('old_assistance_id', $oldAssistance->id)->exists()) {
                $this->assistancesSkipped++;
                $progressBar->advance();

                continue;
            }

            try {
                $user = $this->createOrFindUserFromAssistance($oldAssistance);
                $application = $this->createAssistanceApplication($oldAssistance, $user);

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

                    DB::table('legacy_assistance_migrations')->insert([
                        'old_assistance_id' => $oldAssistance->id,
                        'new_assistance_application_id' => $application->id,
                        'migrated_at' => now(),
                    ]);
                }

                $this->assistancesCreated++;
            } catch (\Exception $e) {
                $progressBar->clear();
                $this->warn("Failed to migrate assistance {$oldAssistance->id}: {$e->getMessage()}");
                $progressBar->display();
                $this->assistancesFailed++;
            }

            $progressBar->advance();
        }

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

    private function createOrFindUserFromAssistance(object $oldAssistance): User
    {
        $email = $oldAssistance->email;

        if (! $email) {
            throw new \Exception('Assistance application 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 = $oldAssistance->first_name ?? null;
        $lastName = $oldAssistance->last_name ?? null;
        $phone = $oldAssistance->phone_number ?? null;
        $address = trim(($oldAssistance->address ?? '').' '.($oldAssistance->city ?? '').' '.($oldAssistance->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 ($oldAssistance->created) {
            $user->created_at = $oldAssistance->created;
        }
        if ($oldAssistance->modified) {
            $user->updated_at = $oldAssistance->modified;
        }
        $user->save();

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

        return $user;
    }

    private function createAssistanceApplication(object $oldAssistance, User $user): AssistanceApplication
    {
        $status = $this->mapStatus($oldAssistance->status);

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

            return $application;
        }

        $application = new AssistanceApplication([
            'user_id' => $user->id,
            'status' => $status,
        ]);
        $application->save();

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

        return $application;
    }

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

    private function createApplicationAnswers(AssistanceApplication $application, object $oldAssistance): void
    {
        $questions = [
            [
                'field' => 'number_of_cats',
                'label' => 'Number of cats',
                'type' => 'text',
                'sort_order' => 1,
            ],
            [
                'field' => 'cats_friendly',
                'label' => 'Are the cats friendly?',
                'type' => 'select',
                'sort_order' => 2,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'cats_sick',
                'label' => 'Are any cats sick or injured?',
                'type' => 'select',
                'sort_order' => 3,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'cats_pregnant',
                'label' => 'Are any cats pregnant?',
                'type' => 'select',
                'sort_order' => 4,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'cats_nursing',
                'label' => 'Are any cats nursing?',
                'type' => 'select',
                'sort_order' => 5,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'are_there_kittens',
                'label' => 'Are there kittens?',
                'type' => 'select',
                'sort_order' => 6,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'age_of_kittens',
                'label' => 'Age of kittens',
                'type' => 'text',
                'sort_order' => 7,
            ],
            [
                'field' => 'feral_comment',
                'label' => 'Are they feral? Please explain',
                'type' => 'text',
                'sort_order' => 8,
            ],
            [
                'field' => 'own_property',
                'label' => 'Do you own the property?',
                'type' => 'select',
                'sort_order' => 9,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'owner_permission',
                'label' => 'Do you have owner permission?',
                'type' => 'select',
                'sort_order' => 10,
                'options' => ['Yes' => 1, 'No' => 0, 'N/A' => 3],
            ],
            [
                'field' => 'contribute_financially',
                'label' => 'Can you contribute financially?',
                'type' => 'select',
                'sort_order' => 11,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'donate_food',
                'label' => 'Can you donate food?',
                'type' => 'select',
                'sort_order' => 12,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'help_trapping',
                'label' => 'Can you help with trapping?',
                'type' => 'select',
                'sort_order' => 13,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'help_transport',
                'label' => 'Can you help with transport?',
                'type' => 'select',
                'sort_order' => 14,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'foster_days',
                'label' => 'Can you foster for a few days?',
                'type' => 'select',
                'sort_order' => 15,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'foster_adoptable',
                'label' => 'Can you foster until adoptable?',
                'type' => 'select',
                'sort_order' => 16,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'maintain_colony',
                'label' => 'Can you maintain a colony?',
                'type' => 'select',
                'sort_order' => 17,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'cant_help',
                'label' => 'Cannot help but want to donate',
                'type' => 'select',
                'sort_order' => 18,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'received_help',
                'label' => 'Have you received help before?',
                'type' => 'select',
                'sort_order' => 19,
                'options' => ['Yes' => 1, 'No' => 0],
            ],
            [
                'field' => 'additional_info',
                'label' => 'Additional information',
                'type' => 'text',
                'sort_order' => 20,
            ],
        ];

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

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

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

            if ($question['type'] === 'select') {
                $numericValue = (int) $fieldValue;
                $answer = array_search($numericValue, $question['options'], true);
                if ($answer === false) {
                    $answer = (string) $numericValue;
                }
            } else {
                $answer = (string) $fieldValue;
            }

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

            if (isset($question['options'])) {
                $questionSnapshot['options'] = array_keys($question['options']);
            }

            $answersToInsert[] = [
                'answerable_type' => AssistanceApplication::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(AssistanceApplication $application, object $oldAssistance): void
    {
        $adminNotes = $oldAssistance->admin_notes ?? null;

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

        $note = new AssistanceApplicationNote([
            'assistance_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('Assistance Applications:');
        $this->info("  Created: {$this->assistancesCreated}");
        $this->info("  Skipped: {$this->assistancesSkipped}");
        $this->info("  Failed: {$this->assistancesFailed}");
        $this->newLine();

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