<?php

declare(strict_types=1);

namespace ImovelZapAi\Owners\Application\Queries;

use ImovelZapAi\Owners\Domain\Enums\ListingAgreementStatus;
use ImovelZapAi\Owners\Domain\Enums\ListingAgreementType;
use ImovelZapAi\Owners\Domain\Enums\ListingStageKey;
use ImovelZapAi\Owners\Infrastructure\Persistence\Eloquent\ListingAgreementModel;
use ImovelZapAi\Owners\Infrastructure\Persistence\Eloquent\ListingOpportunityModel;

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

        $expiringDays = (int) config('owners.agreement_expiry_alert_days', 30);
        $expiringAgreements = ListingAgreementModel::query()
            ->where('tenant_id', $tenantId)
            ->where('status', ListingAgreementStatus::Active)
            ->whereDate('ends_on', '<=', now()->addDays($expiringDays)->toDateString())
            ->whereDate('ends_on', '>=', now()->toDateString())
            ->count();

        $exclusives = ListingAgreementModel::query()
            ->where('tenant_id', $tenantId)
            ->where('status', ListingAgreementStatus::Active)
            ->where('type', ListingAgreementType::Exclusive)
            ->count();

        $awaitingPublish = ListingOpportunityModel::query()
            ->where('tenant_id', $tenantId)
            ->whereNull('closed_at')
            ->whereHas('stage', fn ($q) => $q->where('key', ListingStageKey::Captured->value))
            ->count();

        $published = ListingOpportunityModel::query()
            ->where('tenant_id', $tenantId)
            ->whereHas('stage', fn ($q) => $q->where('key', ListingStageKey::Published->value))
            ->count();

        $lost = ListingOpportunityModel::query()
            ->where('tenant_id', $tenantId)
            ->whereNotNull('lost_at')
            ->count();

        $closed = $published + $lost;
        $conversion = $closed > 0 ? round(($published / $closed) * 100, 1) : null;

        $byBroker = ListingOpportunityModel::query()
            ->where('tenant_id', $tenantId)
            ->whereNotNull('responsible_user_id')
            ->selectRaw('responsible_user_id, count(*) as total')
            ->groupBy('responsible_user_id')
            ->orderByDesc('total')
            ->limit(10)
            ->get()
            ->map(fn ($row) => [
                'responsible_user_id' => $row->responsible_user_id,
                'total' => (int) $row->total,
            ])
            ->all();

        return [
            'open_captures' => $openCapture,
            'expiring_agreements' => $expiringAgreements,
            'exclusive_agreements' => $exclusives,
            'awaiting_publish' => $awaitingPublish,
            'published' => $published,
            'lost' => $lost,
            'conversion_rate' => $conversion,
            'by_broker' => $byBroker,
        ];
    }
}
