<?php

namespace App\Services\Dashboard;

use App\Enum\Esign\EsignEventType;
use App\Enum\Esign\EsignStatus;
use App\Models\Claim\Status;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

/**
 * Metrics for the Productivity report — a period-scoped pipeline funnel that
 * replaces the old Throughput Trends / Pipeline KPIs views. Every "count" is
 * throughput (events that happened inside the [from, to] window); the "(status)"
 * figures are live point-in-time snapshots of where work sits right now.
 *
 * Sources are the ones agreed for this report and differ deliberately from
 * App\Services\Dashboard\DashboardMetrics:
 *   - contact:   clients.first_successful_contact_at (not the contact_made history)
 *   - contracts: contract_requests (sent) + esign_events (signed) — not esign_documents
 *   - redress:   claims.app_sent_date / app_receival_date columns — not status_histories
 *
 * All queries filter solicitor_id explicitly and honour an optional lead-source
 * segment, so the figures reconcile with the Lead Performance dashboard.
 */
class ProductivityMetrics
{
    // Status display names (seeded in the DB; adjust here if renamed).
    public const STATUS_NEW_CAMPAIGN_LEAD = 'New Campaign Lead';
    public const STATUS_WELCOME_CALL      = 'Welcome Call';
    public const STATUS_SENT_TO_REDRESS   = 'Part 1 & 3 Sent To Redress';

    private ?Collection $statusCache = null;

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

    /**
     * The full funnel for the window. `sections` are the throughput/derived rows
     * grouped as on the board; `snapshots` are the live "(status)" cards.
     */
    public function report(Carbon $from, Carbon $to): array
    {
        $leadsIn        = $this->leadsIn($from, $to);
        $contacted      = $this->contacted($from, $to);
        $contractSent   = $this->contractsSent($from, $to);
        $contractSigned = $this->contractsSigned($from, $to);
        $sentToRedress  = $this->sentToRedress($from, $to);
        $appReceived    = $this->appNumberReceived($from, $to);

        $pct = fn (int $num, int $den): ?float => $den === 0 ? null : round($num / $den * 100, 1);

        return [
            'sections' => [
                [
                    'title' => 'Acquisition & Contact',
                    'rows'  => [
                        $this->row('Leads In', 'int', $leadsIn, '(by source)'),
                        $this->row('Client Contacted', 'int', $contacted),
                        $this->row('Contact Rate', 'pct', $pct($contacted, $leadsIn)),
                        $this->row('Time Lead In → Contacted', 'duration', $this->avgTimeToContact($from, $to), 'average'),
                    ],
                ],
                [
                    'title' => 'Contracts',
                    'rows'  => [
                        $this->row('Contracts Sent', 'int', $contractSent),
                        $this->row('Contacted → Contracts Sent', 'pct', $pct($contractSent, $contacted)),
                        $this->row('Contracts Signed', 'int', $contractSigned),
                        $this->row('Sign-up Rate', 'pct', $pct($contractSigned, $contractSent)),
                    ],
                ],
                [
                    'title' => 'Redress & APP Number',
                    'rows'  => [
                        $this->row('Sent To Redress', 'int', $sentToRedress, '(Parts 1 & 3)'),
                        $this->row('APP Number Received', 'int', $appReceived),
                        $this->row('APP Number Turnaround', 'days', $this->avgAppTurnaround($from, $to), 'average'),
                    ],
                ],
            ],
            'snapshots' => [
                $this->snapshot('New Campaign Lead', $this->currentlyInStatus(self::STATUS_NEW_CAMPAIGN_LEAD), 'Claims sitting in New Campaign Lead now'),
                $this->snapshot('At Welcome Call', $this->currentlyInStatus(self::STATUS_WELCOME_CALL), 'Claims sitting in Welcome Call now'),
                $this->snapshot('Contracts Sent', $this->contractsAwaitingSignature(), 'Contract requests sent, not yet signed'),
                $this->snapshot('Sent To Redress', $this->currentlyInStatus(self::STATUS_SENT_TO_REDRESS), 'Claims in Part 1 & 3 Sent To Redress now'),
                $this->snapshot('Waiting On APP Number', $this->waitingOnAppNumber(), 'Redress sent, APP number not yet received'),
            ],
        ];
    }

    // =========================================================================
    // Throughput — events inside [$from, $to].
    // =========================================================================

    /** Clients created in the window (the intake for the period). */
    public function leadsIn(Carbon $from, Carbon $to): int
    {
        return $this->clientScope()
            ->whereBetween('clients.created_at', [$from, $to])
            ->count();
    }

    /** Clients whose first successful contact landed in the window. */
    public function contacted(Carbon $from, Carbon $to): int
    {
        return $this->clientScope()
            ->whereBetween('clients.first_successful_contact_at', [$from, $to])
            ->count();
    }

    /**
     * Average seconds between a client being created and their first successful
     * contact, over clients contacted in the window. Null when none.
     */
    public function avgTimeToContact(Carbon $from, Carbon $to): ?float
    {
        $avg = $this->clientScope()
            ->whereBetween('clients.first_successful_contact_at', [$from, $to])
            ->whereColumn('clients.first_successful_contact_at', '>=', 'clients.created_at')
            ->avg(DB::raw('TIMESTAMPDIFF(SECOND, clients.created_at, clients.first_successful_contact_at)'));

        return $avg === null ? null : (float) $avg;
    }

    /** Distinct clients sent a contract in the window (request left draft). */
    public function contractsSent(Carbon $from, Carbon $to): int
    {
        return DB::table('contract_requests')
            ->join('clients', 'clients.id', '=', 'contract_requests.client_id')
            ->where('contract_requests.solicitor_id', $this->solicitorId)
            ->whereNull('clients.deleted_at')
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereNotIn('contract_requests.status', [EsignStatus::DRAFT->value])
            ->whereBetween('contract_requests.created_at', [$from, $to])
            ->distinct()
            ->count('contract_requests.client_id');
    }

    /**
     * Distinct clients whose contract reached signed/completed in the window,
     * dated by the esign event (the request-level audit trail). A request signs
     * once its documents do, so the earliest signed/completed event per client
     * inside the window counts them.
     */
    public function contractsSigned(Carbon $from, Carbon $to): int
    {
        return DB::table('esign_events')
            ->join('esign_documents', 'esign_documents.id', '=', 'esign_events.esign_document_id')
            ->join('contract_requests', 'contract_requests.id', '=', 'esign_documents.contract_request_id')
            ->join('clients', 'clients.id', '=', 'contract_requests.client_id')
            ->where('esign_events.solicitor_id', $this->solicitorId)
            ->whereNull('clients.deleted_at')
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereIn('esign_events.event_type', [EsignEventType::SIGNED->value, EsignEventType::COMPLETED->value])
            ->whereBetween('esign_events.occurred_at', [$from, $to])
            ->distinct()
            ->count('contract_requests.client_id');
    }

    /** Claims whose redress application was sent in the window. */
    public function sentToRedress(Carbon $from, Carbon $to): int
    {
        return $this->claimScope()
            ->whereBetween('claims.app_sent_date', [$from->toDateString(), $to->toDateString()])
            ->count();
    }

    /** Claims whose APP number was received in the window. */
    public function appNumberReceived(Carbon $from, Carbon $to): int
    {
        return $this->claimScope()
            ->whereBetween('claims.app_receival_date', [$from->toDateString(), $to->toDateString()])
            ->count();
    }

    /**
     * Average days between redress sent and APP number received, over claims that
     * received their APP number in the window. Null when none.
     */
    public function avgAppTurnaround(Carbon $from, Carbon $to): ?float
    {
        $avg = $this->claimScope()
            ->whereBetween('claims.app_receival_date', [$from->toDateString(), $to->toDateString()])
            ->whereNotNull('claims.app_sent_date')
            ->whereColumn('claims.app_receival_date', '>=', 'claims.app_sent_date')
            ->avg(DB::raw('DATEDIFF(claims.app_receival_date, claims.app_sent_date)'));

        return $avg === null ? null : (float) $avg;
    }

    // =========================================================================
    // Live snapshots — point-in-time, not window-bound.
    // =========================================================================

    /** Claims currently sitting in $statusName. */
    public function currentlyInStatus(string $statusName): int
    {
        $statusId = $this->statusId($statusName);
        if ($statusId === null) {
            return 0;
        }

        return $this->claimScope()
            ->where('claims.status_id', $statusId)
            ->count();
    }

    /** Contract requests sent and awaiting signature (sent/viewed, not yet signed). */
    public function contractsAwaitingSignature(): int
    {
        return DB::table('contract_requests')
            ->join('clients', 'clients.id', '=', 'contract_requests.client_id')
            ->where('contract_requests.solicitor_id', $this->solicitorId)
            ->whereNull('clients.deleted_at')
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id))
            ->whereIn('contract_requests.status', [EsignStatus::SENT->value, EsignStatus::VIEWED->value])
            ->count();
    }

    /** Claims with redress sent but the APP number not yet received. */
    public function waitingOnAppNumber(): int
    {
        return $this->claimScope()
            ->whereNotNull('claims.app_sent_date')
            ->whereNull('claims.app_receival_date')
            ->count();
    }

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

    /** Base client query: solicitor-scoped, not soft-deleted, lead-source segment. */
    private function clientScope()
    {
        return DB::table('clients')
            ->where('clients.solicitor_id', $this->solicitorId)
            ->whereNull('clients.deleted_at')
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id));
    }

    /** Base claim query joined to its client for solicitor/lead-source scoping. */
    private function claimScope()
    {
        return DB::table('claims')
            ->join('clients', 'clients.id', '=', 'claims.client_id')
            ->where('claims.solicitor_id', $this->solicitorId)
            ->whereNull('claims.deleted_at')
            ->whereNull('clients.deleted_at')
            ->when($this->leadSourceId, fn ($q, $id) => $q->where('clients.lead_source_id', $id));
    }

    private function statusId(string $name): ?int
    {
        $this->statusCache ??= Status::query()->pluck('id', 'display_name');

        $id = $this->statusCache->get($name);

        return $id === null ? null : (int) $id;
    }

    private function row(string $label, string $kind, int|float|null $value, string $sub = ''): array
    {
        return ['label' => $label, 'sub' => $sub, 'kind' => $kind, 'value' => $value];
    }

    private function snapshot(string $label, int $value, string $note): array
    {
        return ['label' => $label, 'value' => $value, 'note' => $note];
    }
}
