<?php

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::create('lead_phones', function (Blueprint $table) {
            $table->id();
            $table->foreignId('lead_id')->constrained('leads')->cascadeOnDelete();
            $table->string('country_code')->nullable();
            $table->string('phone')->unique();
            $table->boolean('is_primary')->default(false);
            $table->timestamps();

            $table->index(['lead_id', 'is_primary']);
        });

        Schema::create('lead_emails', function (Blueprint $table) {
            $table->id();
            $table->foreignId('lead_id')->constrained('leads')->cascadeOnDelete();
            $table->string('email')->unique();
            $table->boolean('is_primary')->default(false);
            $table->timestamps();

            $table->index(['lead_id', 'is_primary']);
        });

        DB::table('leads')
            ->whereNotNull('phone')
            ->where('phone', '!=', '')
            ->orderBy('id')
            ->get(['id', 'country_code', 'phone', 'created_at', 'updated_at'])
            ->each(function ($lead) {
                DB::table('lead_phones')->insertOrIgnore([
                    'lead_id' => $lead->id,
                    'country_code' => $lead->country_code,
                    'phone' => $lead->phone,
                    'is_primary' => true,
                    'created_at' => $lead->created_at ?? now(),
                    'updated_at' => $lead->updated_at ?? now(),
                ]);
            });

        DB::table('leads')
            ->whereNotNull('email')
            ->where('email', '!=', '')
            ->orderBy('id')
            ->get(['id', 'email', 'created_at', 'updated_at'])
            ->each(function ($lead) {
                DB::table('lead_emails')->insertOrIgnore([
                    'lead_id' => $lead->id,
                    'email' => $lead->email,
                    'is_primary' => true,
                    'created_at' => $lead->created_at ?? now(),
                    'updated_at' => $lead->updated_at ?? now(),
                ]);
            });
    }

    public function down(): void
    {
        Schema::dropIfExists('lead_emails');
        Schema::dropIfExists('lead_phones');
    }
};
