<?php

namespace App\Services\Dashboard;

use App\Enum\Dsar\DsarStatus;
use App\Models\Claim\Claim;
use App\Models\Claim\Status;
use App\Models\Claim\StatusHistory;
use App\Models\Client\Client;
use App\Models\Contact\Hub\ContactMessage;
use App\Models\DailyMetricSnapshot;
use App\Models\Dsar\Dsar;
use App\Models\History;
use App\Models\EsignDocument;
use App\Models\Institution\Institution;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

/**
 * Single source of truth for the KPI dashboard metrics (see the dashboard
 * spreadsheet). Every query filters solicitor_id EXPLICITLY rather than relying
 * on the BelongsToSolicitor global scope, so the exact same calculations run
 * correctly both in the web dashboard (App\Components\Dashboard\Stats) and in the
 * console snapshot command (App\Console\Commands\SnapshotDailyMetrics), where the
 * global scope is disabled.
 *
 * A null $solicitorId means "don't constrain by solicitor" (used only in the web
 * context for a user without a solicitor, mirroring the previous behaviour where
 * the no-op global scope returned all rows). The snapshot command always passes a
 * concrete solicitor id.
 */
class DashboardMetrics
{
    // --- Status display names (seeded in the DB; adjust here if renamed) --------
    public const STATUS_WELCOME_CALL   = 'Welcome Call';
    public const STATUS_SENT_TO_REDRESS = 'Part 1 & 3 Sent To Redress';
    public const STATUS_APP_NUMBER     = 'App Number Received';
    public const STATUS_MISSING_ID     = 'Identification & Proof of Address';
    public const STATUS_INVESTIGATION  = 'Investigation - DSAR Activity';
    public const STATUS_DSARS_SENT     = 'Investigation - DSARs Sent';
    public const STATUS_PART_DSAR      = 'Part Dsar Received';
    public const STATUS_CLOSED         = 'Closed';
    public const TAG_CLOSED_ANOTHER_FIRM = 'Already Represented';
    public const TAG_CLOSED_NO_CONTACT   = 'Unable to Contact';
    public const TAG_CLOSED_NO_CRITERIA  = 'Outside Redress Eligibility';
    public const TAG_CLOSED_DUPLICATE    = 'Duplicate Enquiry';

    /**
     * Stock/backlog metrics captured by the daily snapshot, keyed by the string
     * stored in daily_metric_snapshots.metric. Used by both the capture command
     * and the dashboard's delta display.
     */
    public const SNAPSHOT_METRICS = [
        'missing_id_poa',
        'investigation_dsar_activity',
        'waiting_on_app_number',
        'at_welcome_call',
        'actively_claiming',
        'institutions',
        'emails_outstanding',
    ];

    private ?Collection $statusCache = null;

    public function __construct(
        private readonly ?int $solicitorId = null,
        private readonly ?int $leadSourceId = null,
    ) {
    }

    // =========================================================================
    // Throughput metrics — "status changed to X" during a period (distinct
    // clients), or clients created during a period.
    // =========================================================================

    public function leadsCreated(Carbon $from, Carbon $to): int
    {
        return Client::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('clients.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereBetween('clients.created_at', [$from, $to])
            ->count();
    }

    public function changedToStatus(string $statusName, Carbon $from, Carbon $to): int
    {
        $statusId = $this->statusId($statusName);
        if ($statusId === null) {
            return 0;
        }

        return $this->transitionQuery($statusId, $from, $to)
            ->distinct()
            ->count('claims.client_id');
    }

    public function closedWithTag(string $tagName, Carbon $from, Carbon $to): int
    {
        $closedId = $this->statusId(self::STATUS_CLOSED);
        if ($closedId === null) {
            return 0;
        }

        return $this->transitionQuery($closedId, $from, $to)
            ->join('taggables', function ($join) {
                $join->on('taggables.taggable_id', '=', 'claims.id')
                    ->where('taggables.taggable_type', '=', Claim::class);
            })
            ->join('tags', 'tags.id', '=', 'taggables.tag_id')
            ->where('tags.name', $tagName)
            ->distinct()
            ->count('claims.client_id');
    }

    public function contractsSent(Carbon $from, Carbon $to): int
    {
        return $this->esignQuery()
            ->whereNotNull('esign_documents.sent_at')
            ->whereBetween('esign_documents.sent_at', [$from, $to])
            ->distinct()
            ->count('contract_requests.client_id');
    }

    public function contractsSigned(Carbon $from, Carbon $to): int
    {
        // signed_at is the milestone; some documents jump straight to completed
        // without a distinct signed_at, so fall back to completed_at.
        return $this->esignQuery()
            ->whereRaw('COALESCE(esign_documents.signed_at, esign_documents.completed_at) BETWEEN ? AND ?', [$from, $to])
            ->distinct()
            ->count('contract_requests.client_id');
    }

    // =========================================================================
    // Stock metrics — clients CURRENTLY in a status (point-in-time).
    // =========================================================================

    public function currentlyInStatus(string $statusName): int
    {
        $statusId = $this->statusId($statusName);
        if ($statusId === null) {
            return 0;
        }

        return Claim::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->where('claims.status_id', $statusId)
            ->when($this->leadSourceId, fn ($q, $id) => $q->whereHas('client', fn ($c) => $c->where('lead_source_id', $id)))
            ->count();
    }

    /**
     * Actively Claiming = claims sitting between "Investigation - DSAR Activity"
     * (inclusive) and "Closed" (exclusive) by status sort order.
     */
    public function activelyClaiming(): int
    {
        $from = $this->statusSort(self::STATUS_INVESTIGATION);
        $to   = $this->statusSort(self::STATUS_CLOSED);
        if ($from === null || $to === null) {
            return 0;
        }

        return Claim::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->whereHas('status', fn ($q) => $q
                ->where('sort_order', '>=', $from)
                ->where('sort_order', '<', $to))
            ->when($this->leadSourceId, fn ($q, $id) => $q->whereHas('client', fn ($c) => $c->where('lead_source_id', $id)))
            ->count();
    }

    /**
     * Current number of individual care settings — i.e. institutions in the
     * system. Institutions are global (not tied to a solicitor) and not
     * claim-linked, so neither the solicitor nor the lead-source filter applies.
     */
    public function institutionsCount(): int
    {
        return Institution::query()->count();
    }

    /**
     * Inbound emails still needing action (open in triage). Not lead-source
     * segmentable, so the lead-source filter does not apply here.
     */
    public function emailsOutstanding(): int
    {
        return ContactMessage::query()
            ->inbound()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('solicitor_id', $id))
            ->whereIn('triage_status', ['pending', 'in_progress'])
            ->count();
    }

    // =========================================================================
    // Derived metrics.
    // =========================================================================

    /** Contact rate = clients contacted (→ Welcome Call) ÷ leads in, as a %. */
    public function contactRate(Carbon $from, Carbon $to): ?float
    {
        $leads = $this->leadsCreated($from, $to);
        if ($leads === 0) {
            return null;
        }

        return round($this->changedToStatus(self::STATUS_WELCOME_CALL, $from, $to) / $leads * 100, 1);
    }

    /** Sign-up rate = contracts signed ÷ contracts sent, as a %. */
    public function signUpRate(Carbon $from, Carbon $to): ?float
    {
        $sent = $this->contractsSent($from, $to);
        if ($sent === 0) {
            return null;
        }

        return round($this->contractsSigned($from, $to) / $sent * 100, 1);
    }

    /**
     * Average lead time (days) between "Sent To Redress" and "APP Number
     * Received", for claims that received their APP number in the period. Null
     * when there is no data. Bounded N+1 (one lookup per APP-received claim in
     * the window) kept for clarity.
     */
    public function avgAppLeadTime(Carbon $from, Carbon $to): ?float
    {
        $appId     = $this->statusId(self::STATUS_APP_NUMBER);
        $redressId = $this->statusId(self::STATUS_SENT_TO_REDRESS);
        if ($appId === null || $redressId === null) {
            return null;
        }

        $appRows = StatusHistory::query()
            ->where('status_histories.status_id', $appId)
            ->where('status_histories.subject_type', Claim::class)
            ->whereBetween('status_histories.started_at', [$from, $to])
            ->join('claims', 'claims.id', '=', 'status_histories.subject_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q
                ->join('clients', 'clients.id', '=', 'claims.client_id')
                ->where('clients.lead_source_id', $id))
            ->select('status_histories.subject_id as claim_id', 'status_histories.started_at as app_at')
            ->get();

        $diffs = [];
        foreach ($appRows as $row) {
            $redressAt = StatusHistory::query()
                ->where('subject_type', Claim::class)
                ->where('subject_id', $row->claim_id)
                ->where('status_id', $redressId)
                ->where('started_at', '<=', $row->app_at)
                ->max('started_at');

            if ($redressAt) {
                $diffs[] = Carbon::parse($redressAt)->diffInDays(Carbon::parse($row->app_at));
            }
        }

        if ($diffs === []) {
            return null;
        }

        return round(array_sum($diffs) / count($diffs), 1);
    }

    // =========================================================================
    // Daily snapshot support.
    // =========================================================================

    /** Current value of every snapshot metric, keyed by metric string. */
    public function snapshotValues(): array
    {
        return [
            'missing_id_poa'              => $this->currentlyInStatus(self::STATUS_MISSING_ID),
            'investigation_dsar_activity' => $this->currentlyInStatus(self::STATUS_INVESTIGATION),
            'waiting_on_app_number'       => $this->currentlyInStatus(self::STATUS_SENT_TO_REDRESS),
            'at_welcome_call'             => $this->currentlyInStatus(self::STATUS_WELCOME_CALL),
            'actively_claiming'           => $this->activelyClaiming(),
            'institutions'                => $this->institutionsCount(),
            'emails_outstanding'          => $this->emailsOutstanding(),
        ];
    }

    /**
     * Change in a snapshot metric versus the most recent snapshot on/before
     * yesterday, ~a week ago and ~a month ago: current − past. Null for a period
     * when no prior snapshot exists yet (e.g. before the capture command has run
     * for long enough). Only meaningful in the all-lead-sources view, since
     * snapshots are stored per solicitor across all lead sources.
     */
    public function snapshotDeltas(string $metric, int $current): array
    {
        $targets = [
            'day'   => Carbon::today()->subDay(),
            'week'  => Carbon::today()->subWeek(),
            'month' => Carbon::today()->subMonthNoOverflow(),
        ];

        $deltas = [];
        foreach ($targets as $key => $date) {
            $past = DailyMetricSnapshot::query()
                ->when($this->solicitorId, fn ($q, $id) => $q->where('solicitor_id', $id))
                ->where('metric', $metric)
                ->whereDate('captured_on', '<=', $date)
                ->orderByDesc('captured_on')
                ->value('value');

            $deltas[$key] = $past === null ? null : $current - (int) $past;
        }

        return $deltas;
    }

    /**
     * Daily value series for a snapshot metric over the last $days days (oldest
     * → newest), for drawing a sparkline. Missing days carry forward the last
     * known snapshot; the final point uses the live $current value so the line
     * ends on the number shown on the card. Null-safe: returns a flat line at
     * $current when there is no snapshot history yet.
     */
    public function snapshotSeries(string $metric, int $current, int $days = 14): array
    {
        $from = Carbon::today()->subDays($days - 1);

        $byDate = [];
        DailyMetricSnapshot::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('solicitor_id', $id))
            ->where('metric', $metric)
            ->whereDate('captured_on', '>=', $from)
            ->orderBy('captured_on')
            ->get(['captured_on', 'value'])
            ->each(function ($row) use (&$byDate) {
                $byDate[Carbon::parse($row->captured_on)->toDateString()] = (int) $row->value;
            });

        // Seed carry-forward with the most recent snapshot before the window.
        $last = DailyMetricSnapshot::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('solicitor_id', $id))
            ->where('metric', $metric)
            ->whereDate('captured_on', '<', $from)
            ->orderByDesc('captured_on')
            ->value('value');
        $last = $last === null ? null : (int) $last;

        $series = [];
        for ($i = 0; $i < $days; $i++) {
            $date = $from->copy()->addDays($i)->toDateString();
            if (array_key_exists($date, $byDate)) {
                $last = $byDate[$date];
            }
            // Today's point is the live figure; earlier gaps carry forward, and
            // fall back to $current only if nothing is known yet.
            $series[] = $i === $days - 1 ? $current : ($last ?? $current);
        }

        return $series;
    }

    // =========================================================================
    // Bucketed time series for the historic report. Each returns a
    // [periodKey => value] map, where periodKey is DATE_FORMAT($sqlFmt) — e.g.
    // '%Y-%m-%d' (day), '%x-%v' (ISO year-week), '%Y-%m' (month). $sqlFmt is a
    // fixed internal token, never user input.
    // =========================================================================

    public function createdByPeriod(string $sqlFmt, Carbon $from, Carbon $to): array
    {
        return Client::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('clients.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereBetween('clients.created_at', [$from, $to])
            ->select(
                DB::raw("DATE_FORMAT(clients.created_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(*) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    /**
     * Clients marked "Contact Made" per period, from the contact_made toggle on
     * the client. The boolean itself carries no timestamp, so we bucket by the
     * history entry written when it was switched on (context.field = contact_made,
     * new_value = true). Distinct clients per period, solicitor- and
     * lead-source-filtered.
     */
    public function contactMadeByPeriod(string $sqlFmt, Carbon $from, Carbon $to): array
    {
        return History::query()
            ->where('history.owner_type', Client::class)
            ->where('history.context->field', 'contact_made')
            ->where('history.context->new_value', true)
            ->whereBetween('history.created_at', [$from, $to])
            ->join('clients', 'clients.id', '=', 'history.owner_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('clients.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->select(
                DB::raw("DATE_FORMAT(history.created_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(DISTINCT history.owner_id) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    public function changedToStatusByPeriod(string $statusName, string $sqlFmt, Carbon $from, Carbon $to): array
    {
        $statusId = $this->statusId($statusName);
        if ($statusId === null) {
            return [];
        }

        return $this->transitionQuery($statusId, $from, $to)
            ->select(
                DB::raw("DATE_FORMAT(status_histories.started_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(DISTINCT claims.client_id) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    /**
     * Individual DSARs sent per period — one row per DSAR that has left the
     * pre-send stage (i.e. actually gone out), bucketed by created_at. Mirrors
     * how DsarReport treats "DSARs sent" so the figures reconcile. Solicitor- and
     * lead-source-filtered (lead source via the DSAR's claim → client).
     */
    public function dsarsSentByPeriod(string $sqlFmt, Carbon $from, Carbon $to): array
    {
        return $this->dsarsSentQuery($from, $to)
            ->select(
                DB::raw("DATE_FORMAT(dsars.created_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(*) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    /** Distinct clients with at least one DSAR sent per period (see dsarsSentByPeriod). */
    public function clientsWithDsarSentByPeriod(string $sqlFmt, Carbon $from, Carbon $to): array
    {
        return $this->dsarsSentQuery($from, $to)
            ->join('claims as dsar_claims', 'dsar_claims.id', '=', 'dsars.claim_id')
            ->select(
                DB::raw("DATE_FORMAT(dsars.created_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(DISTINCT dsar_claims.client_id) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    public function closedWithTagByPeriod(string $tagName, string $sqlFmt, Carbon $from, Carbon $to): array
    {
        $closedId = $this->statusId(self::STATUS_CLOSED);
        if ($closedId === null) {
            return [];
        }

        return $this->transitionQuery($closedId, $from, $to)
            ->join('taggables', function ($join) {
                $join->on('taggables.taggable_id', '=', 'claims.id')
                    ->where('taggables.taggable_type', '=', Claim::class);
            })
            ->join('tags', 'tags.id', '=', 'taggables.tag_id')
            ->where('tags.name', $tagName)
            ->select(
                DB::raw("DATE_FORMAT(status_histories.started_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(DISTINCT claims.client_id) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    public function contractsSentByPeriod(string $sqlFmt, Carbon $from, Carbon $to): array
    {
        return $this->esignQuery()
            ->whereNotNull('esign_documents.sent_at')
            ->whereBetween('esign_documents.sent_at', [$from, $to])
            ->select(
                DB::raw("DATE_FORMAT(esign_documents.sent_at, '{$sqlFmt}') as period"),
                DB::raw('COUNT(DISTINCT contract_requests.client_id) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    public function contractsSignedByPeriod(string $sqlFmt, Carbon $from, Carbon $to): array
    {
        return $this->esignQuery()
            ->whereRaw('COALESCE(esign_documents.signed_at, esign_documents.completed_at) BETWEEN ? AND ?', [$from, $to])
            ->select(
                DB::raw("DATE_FORMAT(COALESCE(esign_documents.signed_at, esign_documents.completed_at), '{$sqlFmt}') as period"),
                DB::raw('COUNT(DISTINCT contract_requests.client_id) as cnt'),
            )
            ->groupBy('period')
            ->pluck('cnt', 'period')
            ->all();
    }

    /**
     * Stock metric value per period from the daily snapshots. $phpFmt is the
     * Carbon format matching the SQL period key ('Y-m-d', 'o-W', 'Y-m'). The last
     * snapshot within a period wins (i.e. the end-of-period reading). Periods with
     * no snapshot are simply absent from the map (rendered as "—").
     */
    public function snapshotByPeriod(string $metricKey, string $phpFmt, Carbon $from, Carbon $to): array
    {
        $out = [];
        DailyMetricSnapshot::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('solicitor_id', $id))
            ->where('metric', $metricKey)
            ->whereDate('captured_on', '>=', $from->toDateString())
            ->whereDate('captured_on', '<=', $to->toDateString())
            ->orderBy('captured_on')
            ->get(['captured_on', 'value'])
            ->each(function ($row) use (&$out, $phpFmt) {
                $out[Carbon::parse($row->captured_on)->format($phpFmt)] = (int) $row->value;
            });

        return $out;
    }

    // =========================================================================
    // Cohort metrics — everything below is scoped to the set of clients CREATED
    // within [$from, $to] (the "cohort"), and then measures how far that same set
    // has progressed to date. This is the opposite framing to the throughput
    // methods above, which bucket by WHEN an event happened. Milestone counts are
    // cumulative (ever reached); "currently in status" counts are live (now).
    // =========================================================================

    /**
     * Cohort members who have EVER been marked "Contact Made" (distinct clients),
     * detected via the history entry that switched the toggle on. Mirrors the
     * KPI report's "Client Contacted" definition, but bounded by the client's
     * creation date instead of the toggle date.
     */
    public function cohortContacted(Carbon $from, Carbon $to): int
    {
        return History::query()
            ->where('history.owner_type', Client::class)
            ->where('history.context->field', 'contact_made')
            ->where('history.context->new_value', true)
            ->join('clients', 'clients.id', '=', 'history.owner_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('clients.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereBetween('clients.created_at', [$from, $to])
            ->distinct()
            ->count('history.owner_id');
    }

    /** Cohort claims that have EVER transitioned into $statusName (distinct clients). */
    public function cohortReachedStatus(string $statusName, Carbon $from, Carbon $to): int
    {
        $statusId = $this->statusId($statusName);
        if ($statusId === null) {
            return 0;
        }

        return $this->cohortTransitionQuery($statusId, $from, $to)
            ->distinct()
            ->count('claims.client_id');
    }

    /** Cohort claims closed carrying $tagName (distinct clients). */
    public function cohortClosedWithTag(string $tagName, Carbon $from, Carbon $to): int
    {
        $closedId = $this->statusId(self::STATUS_CLOSED);
        if ($closedId === null) {
            return 0;
        }

        return $this->cohortTransitionQuery($closedId, $from, $to)
            ->join('taggables', function ($join) {
                $join->on('taggables.taggable_id', '=', 'claims.id')
                    ->where('taggables.taggable_type', '=', Claim::class);
            })
            ->join('tags', 'tags.id', '=', 'taggables.tag_id')
            ->where('tags.name', $tagName)
            ->distinct()
            ->count('claims.client_id');
    }

    /** Cohort clients who have ever had a contract sent to them (distinct clients). */
    public function cohortContractsSent(Carbon $from, Carbon $to): int
    {
        return $this->cohortEsignQuery($from, $to)
            ->whereNotNull('esign_documents.sent_at')
            ->distinct()
            ->count('contract_requests.client_id');
    }

    /** Cohort clients who have ever signed (or completed) a contract (distinct clients). */
    public function cohortContractsSigned(Carbon $from, Carbon $to): int
    {
        return $this->cohortEsignQuery($from, $to)
            ->whereRaw('COALESCE(esign_documents.signed_at, esign_documents.completed_at) IS NOT NULL')
            ->distinct()
            ->count('contract_requests.client_id');
    }

    /** Total DSARs sent for the cohort (one row per sent DSAR). */
    public function cohortDsarsSent(Carbon $from, Carbon $to): int
    {
        return $this->cohortDsarsSentQuery($from, $to)->count('dsars.id');
    }

    /** Distinct cohort clients with at least one DSAR sent. */
    public function cohortClientsWithDsarSent(Carbon $from, Carbon $to): int
    {
        return $this->cohortDsarsSentQuery($from, $to)
            ->distinct()
            ->count('dsar_claims.client_id');
    }

    /**
     * Average lead time (days) between "Sent To Redress" and "APP Number Received"
     * for the cohort's claims that have reached APP Number. Null when none have.
     * Each claim contributes once (its latest APP transition).
     */
    public function cohortAvgAppLeadTime(Carbon $from, Carbon $to): ?float
    {
        $appId     = $this->statusId(self::STATUS_APP_NUMBER);
        $redressId = $this->statusId(self::STATUS_SENT_TO_REDRESS);
        if ($appId === null || $redressId === null) {
            return null;
        }

        $appRows = StatusHistory::query()
            ->where('status_histories.status_id', $appId)
            ->where('status_histories.subject_type', Claim::class)
            ->join('claims', 'claims.id', '=', 'status_histories.subject_id')
            ->join('clients', 'clients.id', '=', 'claims.client_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereBetween('clients.created_at', [$from, $to])
            ->select('status_histories.subject_id as claim_id', 'status_histories.started_at as app_at')
            ->get();

        // One entry per claim (latest APP transition), so a claim that re-entered
        // the status is not double-counted in the average.
        $appAtByClaim = [];
        foreach ($appRows as $row) {
            $existing = $appAtByClaim[$row->claim_id] ?? null;
            if ($existing === null || $row->app_at > $existing) {
                $appAtByClaim[$row->claim_id] = $row->app_at;
            }
        }

        $diffs = [];
        foreach ($appAtByClaim as $claimId => $appAt) {
            $redressAt = StatusHistory::query()
                ->where('subject_type', Claim::class)
                ->where('subject_id', $claimId)
                ->where('status_id', $redressId)
                ->where('started_at', '<=', $appAt)
                ->max('started_at');

            if ($redressAt) {
                $diffs[] = Carbon::parse($redressAt)->diffInDays(Carbon::parse($appAt));
            }
        }

        if ($diffs === []) {
            return null;
        }

        return round(array_sum($diffs) / count($diffs), 1);
    }

    /** Cohort claims sitting in $statusName right now (live, not a snapshot). */
    public function cohortCurrentlyInStatus(string $statusName, Carbon $from, Carbon $to): int
    {
        $statusId = $this->statusId($statusName);
        if ($statusId === null) {
            return 0;
        }

        return Claim::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->where('claims.status_id', $statusId)
            ->whereHas('client', function ($c) use ($from, $to) {
                $c->whereBetween('created_at', [$from, $to]);
                if ($this->leadSourceId) {
                    $c->where('lead_source_id', $this->leadSourceId);
                }
            })
            ->count();
    }

    /** Cohort claims currently "Actively Claiming" (Investigation ≤ status < Closed). */
    public function cohortActivelyClaiming(Carbon $from, Carbon $to): int
    {
        $fromSort = $this->statusSort(self::STATUS_INVESTIGATION);
        $toSort   = $this->statusSort(self::STATUS_CLOSED);
        if ($fromSort === null || $toSort === null) {
            return 0;
        }

        return Claim::query()
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->whereHas('status', fn ($q) => $q
                ->where('sort_order', '>=', $fromSort)
                ->where('sort_order', '<', $toSort))
            ->whereHas('client', function ($c) use ($from, $to) {
                $c->whereBetween('created_at', [$from, $to]);
                if ($this->leadSourceId) {
                    $c->where('lead_source_id', $this->leadSourceId);
                }
            })
            ->count();
    }

    // =========================================================================
    // Internals.
    // =========================================================================

    /**
     * Base query over claim status transitions into $statusId within a period,
     * solicitor- and lead-source-filtered. Returns distinct client counting to
     * the caller (which applies ->distinct()->count('claims.client_id')).
     */
    private function transitionQuery(int $statusId, Carbon $from, Carbon $to)
    {
        return StatusHistory::query()
            ->where('status_histories.status_id', $statusId)
            ->where('status_histories.subject_type', Claim::class)
            ->whereBetween('status_histories.started_at', [$from, $to])
            ->join('claims', 'claims.id', '=', 'status_histories.subject_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q
                ->join('clients', 'clients.id', '=', 'claims.client_id')
                ->where('clients.lead_source_id', $id));
    }

    /**
     * Base query over DSARs that have been sent (status past the pre-send stage)
     * with created_at in the window, solicitor- and lead-source-filtered. The
     * lead source lives on the DSAR's claim → client, joined under distinct
     * aliases so callers may add their own claims join.
     */
    private function dsarsSentQuery(Carbon $from, Carbon $to)
    {
        return Dsar::query()
            ->whereNotIn('dsars.status', DsarStatus::preSend())
            ->whereBetween('dsars.created_at', [$from, $to])
            ->when($this->solicitorId, fn ($q, $id) => $q->where('dsars.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q
                ->join('claims as ls_claims', 'ls_claims.id', '=', 'dsars.claim_id')
                ->join('clients as ls_clients', 'ls_clients.id', '=', 'ls_claims.client_id')
                ->where('ls_clients.lead_source_id', $id));
    }

    /**
     * Base query over claim status transitions into $statusId whose claim belongs
     * to a client CREATED within the window. Unlike transitionQuery() this always
     * joins clients (the cohort is defined by created_at) and puts no bound on the
     * transition's own date — a milestone reached at any time counts.
     */
    private function cohortTransitionQuery(int $statusId, Carbon $from, Carbon $to)
    {
        return StatusHistory::query()
            ->where('status_histories.status_id', $statusId)
            ->where('status_histories.subject_type', Claim::class)
            ->join('claims', 'claims.id', '=', 'status_histories.subject_id')
            ->join('clients', 'clients.id', '=', 'claims.client_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('claims.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereBetween('clients.created_at', [$from, $to]);
    }

    /**
     * Base query over esign documents whose contract request's client was created
     * within the window. Always joins clients (cohort definition); no bound on the
     * document's own send/sign date.
     */
    private function cohortEsignQuery(Carbon $from, Carbon $to)
    {
        return EsignDocument::query()
            ->join('contract_requests', 'contract_requests.id', '=', 'esign_documents.contract_request_id')
            ->join('clients', 'clients.id', '=', 'contract_requests.client_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('contract_requests.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereBetween('clients.created_at', [$from, $to]);
    }

    /**
     * Base query over sent DSARs whose claim's client was created within the
     * window. Always joins claim → client under distinct aliases; no bound on the
     * DSAR's own created_at.
     */
    private function cohortDsarsSentQuery(Carbon $from, Carbon $to)
    {
        return Dsar::query()
            ->whereNotIn('dsars.status', DsarStatus::preSend())
            ->join('claims as dsar_claims', 'dsar_claims.id', '=', 'dsars.claim_id')
            ->join('clients as dsar_clients', 'dsar_clients.id', '=', 'dsar_claims.client_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('dsars.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('dsar_clients.lead_source_id', $id))
            ->whereBetween('dsar_clients.created_at', [$from, $to]);
    }

    /** Base query over esign documents joined to their contract request. */
    private function esignQuery()
    {
        return EsignDocument::query()
            ->join('contract_requests', 'contract_requests.id', '=', 'esign_documents.contract_request_id')
            ->when($this->solicitorId, fn ($q, $id) => $q->where('contract_requests.solicitor_id', $id))
            ->when($this->leadSourceId, fn ($q, $id) => $q
                ->join('clients', 'clients.id', '=', 'contract_requests.client_id')
                ->where('clients.lead_source_id', $id));
    }

    private function statuses(): Collection
    {
        return $this->statusCache ??= Status::query()
            ->get(['id', 'display_name', 'sort_order'])
            ->keyBy('display_name');
    }

    private function statusId(string $name): ?int
    {
        return $this->statuses()->get($name)?->id;
    }

    private function statusSort(string $name): ?int
    {
        return $this->statuses()->get($name)?->sort_order;
    }
}
