<?php

declare(strict_types=1);

namespace ImovelZapAi\Sales\Application\Queries;

use ImovelZapAi\Sales\Infrastructure\Persistence\Eloquent\DealModel;
use ImovelZapAi\Sales\Infrastructure\Persistence\Eloquent\DealTaskModel;
use ImovelZapAi\Sales\Infrastructure\Persistence\Eloquent\SalesDealStageModel;

final class GetDealDashboardQuery
{
    /**
     * @return array<string, mixed>
     */
    public function execute(string $tenantId): array
    {
        $open = DealModel::query()
            ->where('tenant_id', $tenantId)
            ->whereNull('closed_at');

        $openCount = (clone $open)->count();
        $pipelineValue = (float) (clone $open)->sum('amount');

        $won = DealModel::query()->where('tenant_id', $tenantId)->whereNotNull('won_at')->count();
        $lost = DealModel::query()->where('tenant_id', $tenantId)->whereNotNull('lost_at')->count();
        $closed = $won + $lost;
        $conversionRate = $closed > 0 ? round(($won / $closed) * 100, 1) : null;

        $avgTicket = $won > 0
            ? round((float) DealModel::query()->where('tenant_id', $tenantId)->whereNotNull('won_at')->avg('amount'), 2)
            : null;

        $stagnationDays = (int) config('sales.stagnation_days', 7);
        $inactive = DealModel::query()
            ->where('tenant_id', $tenantId)
            ->whereNull('closed_at')
            ->where(function ($q) use ($stagnationDays): void {
                $cutoff = now()->subDays($stagnationDays);
                $q->where('last_activity_at', '<=', $cutoff)
                    ->orWhere(function ($q2) use ($cutoff): void {
                        $q2->whereNull('last_activity_at')->where('created_at', '<=', $cutoff);
                    });
            })
            ->count();

        $avgHoursByStage = SalesDealStageModel::query()
            ->where('tenant_id', $tenantId)
            ->orderBy('position')
            ->get()
            ->map(function (SalesDealStageModel $stage) use ($tenantId) {
                $avg = DealModel::query()
                    ->where('tenant_id', $tenantId)
                    ->where('stage_id', $stage->id)
                    ->whereNotNull('stage_entered_at')
                    ->get()
                    ->avg(fn (DealModel $d) => $d->stage_entered_at?->diffInHours(now()));

                return [
                    'stage_key' => $stage->key,
                    'stage_name' => $stage->name,
                    'avg_hours' => $avg !== null ? round((float) $avg, 1) : null,
                ];
            })
            ->all();

        $lostReasons = DealModel::query()
            ->where('tenant_id', $tenantId)
            ->whereNotNull('lost_reason')
            ->selectRaw('lost_reason, count(*) as total')
            ->groupBy('lost_reason')
            ->orderByDesc('total')
            ->get()
            ->map(fn ($row) => [
                'reason' => $row->lost_reason,
                'total' => (int) $row->total,
            ])
            ->all();

        $openTasks = DealTaskModel::query()
            ->where('tenant_id', $tenantId)
            ->where('status', 'open')
            ->count();

        return [
            'open_deals' => $openCount,
            'pipeline_value' => $pipelineValue,
            'conversion_rate' => $conversionRate,
            'avg_ticket' => $avgTicket,
            'won' => $won,
            'lost' => $lost,
            'inactive_deals' => $inactive,
            'open_tasks' => $openTasks,
            'avg_hours_by_stage' => $avgHoursByStage,
            'lost_reasons' => $lostReasons,
        ];
    }
}
