Code Coverage
 
Lines
Functions and Methods
Classes and Traits
Total
98.57%
69 / 70
0.00%
0 / 1
 CRAP
0.00%
0 / 1
GetDealDashboardQuery
98.57%
69 / 70
0.00%
0 / 1
 4
0.00%
0 / 1
 execute
98.57%
69 / 70
0.00%
0 / 1
 4
1<?php
2
3declare(strict_types=1);
4
5namespace ImovelZapAi\Sales\Application\Queries;
6
7use ImovelZapAi\Sales\Infrastructure\Persistence\Eloquent\DealModel;
8use ImovelZapAi\Sales\Infrastructure\Persistence\Eloquent\DealTaskModel;
9use ImovelZapAi\Sales\Infrastructure\Persistence\Eloquent\SalesDealStageModel;
10
11final class GetDealDashboardQuery
12{
13    /**
14     * @return array<string, mixed>
15     */
16    public function execute(string $tenantId): array
17    {
18        $open = DealModel::query()
19            ->where('tenant_id', $tenantId)
20            ->whereNull('closed_at');
21
22        $openCount = (clone $open)->count();
23        $pipelineValue = (float) (clone $open)->sum('amount');
24
25        $won = DealModel::query()->where('tenant_id', $tenantId)->whereNotNull('won_at')->count();
26        $lost = DealModel::query()->where('tenant_id', $tenantId)->whereNotNull('lost_at')->count();
27        $closed = $won + $lost;
28        $conversionRate = $closed > 0 ? round(($won / $closed) * 100, 1) : null;
29
30        $avgTicket = $won > 0
31            ? round((float) DealModel::query()->where('tenant_id', $tenantId)->whereNotNull('won_at')->avg('amount'), 2)
32            : null;
33
34        $stagnationDays = (int) config('sales.stagnation_days', 7);
35        $inactive = DealModel::query()
36            ->where('tenant_id', $tenantId)
37            ->whereNull('closed_at')
38            ->where(function ($q) use ($stagnationDays): void {
39                $cutoff = now()->subDays($stagnationDays);
40                $q->where('last_activity_at', '<=', $cutoff)
41                    ->orWhere(function ($q2) use ($cutoff): void {
42                        $q2->whereNull('last_activity_at')->where('created_at', '<=', $cutoff);
43                    });
44            })
45            ->count();
46
47        $avgHoursByStage = SalesDealStageModel::query()
48            ->where('tenant_id', $tenantId)
49            ->orderBy('position')
50            ->get()
51            ->map(function (SalesDealStageModel $stage) use ($tenantId) {
52                $avg = DealModel::query()
53                    ->where('tenant_id', $tenantId)
54                    ->where('stage_id', $stage->id)
55                    ->whereNotNull('stage_entered_at')
56                    ->get()
57                    ->avg(fn (DealModel $d) => $d->stage_entered_at?->diffInHours(now()));
58
59                return [
60                    'stage_key' => $stage->key,
61                    'stage_name' => $stage->name,
62                    'avg_hours' => $avg !== null ? round((float) $avg, 1) : null,
63                ];
64            })
65            ->all();
66
67        $lostReasons = DealModel::query()
68            ->where('tenant_id', $tenantId)
69            ->whereNotNull('lost_reason')
70            ->selectRaw('lost_reason, count(*) as total')
71            ->groupBy('lost_reason')
72            ->orderByDesc('total')
73            ->get()
74            ->map(fn ($row) => [
75                'reason' => $row->lost_reason,
76                'total' => (int) $row->total,
77            ])
78            ->all();
79
80        $openTasks = DealTaskModel::query()
81            ->where('tenant_id', $tenantId)
82            ->where('status', 'open')
83            ->count();
84
85        return [
86            'open_deals' => $openCount,
87            'pipeline_value' => $pipelineValue,
88            'conversion_rate' => $conversionRate,
89            'avg_ticket' => $avgTicket,
90            'won' => $won,
91            'lost' => $lost,
92            'inactive_deals' => $inactive,
93            'open_tasks' => $openTasks,
94            'avg_hours_by_stage' => $avgHoursByStage,
95            'lost_reasons' => $lostReasons,
96        ];
97    }
98}