<?php

use App\Models\LeadSource\LeadSource;
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration {
    public function up(): void {
        Schema::table('pension_cases', function (Blueprint $table) {
            $table->foreignId('lead_source_id')
                ->nullable()
                ->after('lead_source_cpf')
                ->constrained('lead_sources')
                ->nullOnDelete();
        });

        $legacyValues = DB::table('pension_cases')
            ->whereNotNull('lead_source_cpf')
            ->where('lead_source_cpf', '!=', '')
            ->whereNull('lead_source_id')
            ->pluck('lead_source_cpf')
            ->map(fn($v) => trim($v))
            ->filter()
            ->unique()
            ->values();

        if ($legacyValues->isEmpty()) {
            return;
        }

        $existing = LeadSource::withTrashed()
            ->get(['id', 'display_name'])
            ->keyBy(fn($row) => mb_strtolower(trim($row->display_name)));

        foreach ($legacyValues as $legacy) {
            $key = mb_strtolower($legacy);
            $leadSource = $existing->get($key);

            if (!$leadSource) {
                $leadSource = LeadSource::create([
                    'display_name' => $legacy,
                    'status'       => 'active',
                ]);
                $existing->put($key, $leadSource);
            }

            DB::table('pension_cases')
                ->where('lead_source_cpf', $legacy)
                ->whereNull('lead_source_id')
                ->update(['lead_source_id' => $leadSource->id]);
        }
    }

    public function down(): void {
        Schema::table('pension_cases', function (Blueprint $table) {
            $table->dropConstrainedForeignId('lead_source_id');
        });
    }
};
