<?php

declare(strict_types=1);

namespace ImovelZapAi\Analytics\Application\Queries;

use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;
use ImovelZapAi\Analytics\Domain\Enums\AnalyticsEventKey;
use ImovelZapAi\Analytics\Infrastructure\Persistence\Eloquent\AnalyticsFactModel;

final class GetFunnelQuery
{
    /** @var list<array{key: string, label: string, event_key: string}> */
    private const STAGES = [
        ['key' => 'opportunities', 'label' => 'Oportunidades', 'event_key' => AnalyticsEventKey::DealCreated->value],
        ['key' => 'visits', 'label' => 'Visitas agendadas', 'event_key' => AnalyticsEventKey::VisitCreated->value],
        ['key' => 'visits_done', 'label' => 'Visitas concluídas', 'event_key' => AnalyticsEventKey::VisitCompleted->value],
        ['key' => 'signatures', 'label' => 'Assinaturas solicitadas', 'event_key' => AnalyticsEventKey::SignatureRequested->value],
        ['key' => 'signatures_done', 'label' => 'Assinaturas concluídas', 'event_key' => AnalyticsEventKey::SignatureCompleted->value],
        ['key' => 'won', 'label' => 'Oportunidades ganhas', 'event_key' => AnalyticsEventKey::DealWon->value],
    ];

    /**
     * @param  array{from?: string, to?: string, days?: int}  $filters
     * @return array<string, mixed>
     */
    public function execute(string $tenantId, array $filters = []): array
    {
        [$from, $to] = $this->period($filters);

        $counts = AnalyticsFactModel::query()
            ->select('event_key', DB::raw('COUNT(*) as total'))
            ->where('tenant_id', $tenantId)
            ->whereBetween('occurred_at', [$from, $to])
            ->whereIn('event_key', array_column(self::STAGES, 'event_key'))
            ->groupBy('event_key')
            ->pluck('total', 'event_key');

        $stages = [];
        $previousCount = null;

        foreach (self::STAGES as $stage) {
            $count = (int) ($counts[$stage['event_key']] ?? 0);
            $conversion = $previousCount !== null && $previousCount > 0
                ? round(($count / $previousCount) * 100, 1)
                : null;

            $stages[] = [
                'key' => $stage['key'],
                'label' => $stage['label'],
                'count' => $count,
                'conversion_from_previous' => $conversion,
            ];

            $previousCount = $count > 0 ? $count : $previousCount;
        }

        return [
            'period' => ['from' => $from->toDateString(), 'to' => $to->toDateString()],
            'stages' => $stages,
        ];
    }

    /** @param array{from?: string, to?: string, days?: int} $filters */
    /** @return array{0: Carbon, 1: Carbon} */
    private function period(array $filters): array
    {
        $to = isset($filters['to']) ? Carbon::parse($filters['to'])->endOfDay() : now();
        $from = isset($filters['from'])
            ? Carbon::parse($filters['from'])->startOfDay()
            : $to->copy()->subDays((int) ($filters['days'] ?? 30))->startOfDay();

        return [$from, $to];
    }
}
