<?php

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

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('clients', function (Blueprint $table) {
            $table->timestamp('first_successful_contact_at')
                ->nullable()
                ->after('contact_made')
                ->index();
        });

        // Preserve historic reporting by deriving the earliest approved contact:
        // an answered client call or an inbound email matched to the client.
        DB::table('clients')
            ->select(['id'])
            ->orderBy('id')
            ->chunkById(250, function ($clients) {
                foreach ($clients as $client) {
                    $voiceAt = DB::table('communications')
                        ->join('calls', 'calls.communication_id', '=', 'communications.id')
                        ->where('communications.communicable_type', Client::class)
                        ->where('communications.communicable_id', $client->id)
                        ->where('communications.channel', 'voice')
                        ->whereIn('calls.call_type', ['client', 'general_intake', 'manual'])
                        ->whereNotNull('communications.answered_at')
                        ->min('communications.answered_at');

                    $emailAt = DB::table('contact_messages')
                        ->where('messageable_type', Client::class)
                        ->where('messageable_id', $client->id)
                        ->where('channel', 'email')
                        ->where('direction', 'inbound')
                        ->min('created_at');

                    $timestamps = array_filter([$voiceAt, $emailAt]);
                    if ($timestamps === []) {
                        continue;
                    }

                    $firstAt = collect($timestamps)
                        ->map(fn ($value) => Carbon::parse($value))
                        ->sort()
                        ->first();

                    DB::table('clients')->where('id', $client->id)->update([
                        'contact_made' => true,
                        'first_successful_contact_at' => $firstAt,
                    ]);
                }
            });
    }

    public function down(): void
    {
        Schema::table('clients', function (Blueprint $table) {
            $table->dropIndex(['first_successful_contact_at']);
            $table->dropColumn('first_successful_contact_at');
        });
    }
};
