<?php

use App\Models\Claim\Claim;
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
{
    private const REDRESS_TEMPLATE_NAME = 'email to scheme - intake - enc application';

    public function up(): void
    {
        Schema::table('claims', function (Blueprint $table) {
            $table->date('app_sent_date')->nullable()->after('case_number');
            $table->date('app_est_receival_date')->nullable()->after('app_sent_date');
            $table->date('app_receival_date')->nullable()->after('app_est_receival_date');
        });

        $this->backfillSentDates();
        $this->backfillReceivalDates();
        $this->backfillEstimatedDates();
    }

    public function down(): void
    {
        Schema::table('claims', function (Blueprint $table) {
            $table->dropColumn([
                'app_sent_date',
                'app_est_receival_date',
                'app_receival_date',
            ]);
        });
    }

    private function backfillSentDates(): void
    {
        $clientClaims = DB::table('claims')
            ->whereNull('deleted_at')
            ->pluck('id', 'client_id');
        $claimIds = DB::table('claims')->whereNull('deleted_at')->pluck('id')->flip();

        DB::table('contact_messages')
            ->leftJoin('contact_templates', 'contact_templates.id', '=', 'contact_messages.template_id')
            ->whereNull('contact_messages.deleted_at')
            ->whereNotNull('contact_messages.sent_at')
            ->where('contact_messages.channel', 'email')
            ->where('contact_messages.direction', 'outbound')
            ->where(function ($query) {
                $query->whereRaw('LOWER(contact_templates.name) = ?', [self::REDRESS_TEMPLATE_NAME])
                    ->orWhereIn('contact_templates.key', [
                        'email-to-scheme-intake-enc-application',
                        'scheme-intake-enc-application',
                    ])
                    ->orWhereRaw('LOWER(contact_messages.subject) = ?', [self::REDRESS_TEMPLATE_NAME]);
            })
            ->select([
                'contact_messages.id as message_id',
                'contact_messages.messageable_type',
                'contact_messages.messageable_id',
                'contact_messages.related_type',
                'contact_messages.related_id',
                'contact_messages.sent_at',
            ])
            ->chunkById(500, function ($messages) use ($clientClaims, $claimIds) {
                foreach ($messages as $message) {
                    $claimId = $this->claimIdForMessage($message, $clientClaims, $claimIds);
                    if (!$claimId) {
                        continue;
                    }

                    $sentDate = Carbon::parse($message->sent_at)->toDateString();
                    DB::table('claims')
                        ->where('id', $claimId)
                        ->where(function ($query) use ($sentDate) {
                            $query->whereNull('app_sent_date')
                                ->orWhere('app_sent_date', '>', $sentDate);
                        })
                        ->update(['app_sent_date' => $sentDate]);
                }
            }, 'contact_messages.id', 'message_id');
    }

    private function backfillReceivalDates(): void
    {
        DB::table('claims')
            ->whereNull('deleted_at')
            ->whereNotNull('case_number')
            ->select(['id', 'client_id'])
            ->chunkById(250, function ($claims) {
                $clientIds = $claims->pluck('client_id');
                $claimIds = $claims->pluck('id');

                $history = DB::table('history')
                    ->where('owner_type', Client::class)
                    ->whereIn('owner_id', $clientIds)
                    ->orderBy('created_at')
                    ->get(['owner_id', 'context', 'created_at'])
                    ->groupBy('owner_id');

                $statusDates = DB::table('status_histories')
                    ->join('statuses', 'statuses.id', '=', 'status_histories.status_id')
                    ->where('status_histories.subject_type', Claim::class)
                    ->whereIn('status_histories.subject_id', $claimIds)
                    ->whereRaw('LOWER(statuses.display_name) = ?', ['app number received'])
                    ->groupBy('status_histories.subject_id')
                    ->selectRaw('status_histories.subject_id, MIN(status_histories.started_at) as received_at')
                    ->pluck('received_at', 'subject_id');

                foreach ($claims as $claim) {
                    $historyDate = $history->get($claim->client_id, collect())
                        ->first(function ($entry) {
                            $context = is_string($entry->context)
                                ? json_decode($entry->context, true)
                                : (array) $entry->context;

                            return trim((string) ($context['case_number'] ?? '')) !== '';
                        })
                        ?->created_at;

                    $receivedAt = $historyDate ?? $statusDates->get($claim->id);
                    if ($receivedAt) {
                        DB::table('claims')->where('id', $claim->id)->update([
                            'app_receival_date' => Carbon::parse($receivedAt)->toDateString(),
                        ]);
                    }
                }
            });
    }

    private function backfillEstimatedDates(): void
    {
        $solicitorIds = DB::table('claims')
            ->whereNull('deleted_at')
            ->whereNotNull('solicitor_id')
            ->distinct()
            ->pluck('solicitor_id');

        foreach ($solicitorIds as $solicitorId) {
            $durations = DB::table('claims')
                ->where('solicitor_id', $solicitorId)
                ->whereNull('deleted_at')
                ->whereNotNull('app_sent_date')
                ->whereNotNull('app_receival_date')
                ->get(['app_sent_date', 'app_receival_date'])
                ->map(fn ($claim) => Carbon::parse($claim->app_sent_date)
                    ->diffInDays(Carbon::parse($claim->app_receival_date), false))
                ->filter(fn ($days) => $days >= 0);

            $averageDays = $durations->isEmpty() ? 14 : max(14, (int) round($durations->average()));

            DB::table('claims')
                ->where('solicitor_id', $solicitorId)
                ->whereNull('deleted_at')
                ->whereNotNull('app_sent_date')
                ->orderBy('id')
                ->chunkById(500, function ($claims) use ($averageDays) {
                    foreach ($claims as $claim) {
                        DB::table('claims')->where('id', $claim->id)->update([
                            'app_est_receival_date' => Carbon::parse($claim->app_sent_date)
                                ->addDays($averageDays)
                                ->toDateString(),
                        ]);
                    }
                });
        }
    }

    private function claimIdForMessage($message, $clientClaims, $claimIds): ?int
    {
        foreach (['messageable', 'related'] as $prefix) {
            $type = $message->{"{$prefix}_type"};
            $id = $message->{"{$prefix}_id"};

            if ($type === Claim::class && $claimIds->has($id)) {
                return (int) $id;
            }

            if ($type === Client::class && $clientClaims->has($id)) {
                return (int) $clientClaims->get($id);
            }
        }

        return null;
    }
};
