<?php

namespace App\Modules\Admin\IntegrationsHealth\Repositories;

use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\DB;

final class IntegrationObservabilityRepository
{
    public function deadLetterCount(Carbon $since): int
    {
        return (int) DB::table('integration_webhook_deliveries')
            ->where('status', 'dead_letter')
            ->where('dead_lettered_at', '>=', $since)
            ->count();
    }

    /**
     * @return list<array<string, mixed>>
     */
    public function recentDeadLetters(int $limit = 25): array
    {
        return DB::table('integration_webhook_deliveries as d')
            ->join('integration_webhooks as w', 'w.id', '=', 'd.webhook_id')
            ->where('d.status', 'dead_letter')
            ->orderByDesc('d.dead_lettered_at')
            ->limit($limit)
            ->get([
                'd.id',
                'd.tenant_id',
                'd.webhook_id',
                'd.event_id',
                'd.attempt_count',
                'd.dead_lettered_at',
                'w.name as webhook_name',
            ])
            ->map(fn ($r) => (array) $r)
            ->all();
    }

    public function retryStormCount(Carbon $since): int
    {
        return (int) DB::table('integration_webhook_deliveries')
            ->where('attempt_count', '>=', 4)
            ->where('last_attempted_at', '>=', $since)
            ->count();
    }

    /**
     * @return array{p95:?int,avg:?int,slow_count:int}
     */
    public function slowWebhookSignals(Carbon $since): array
    {
        $row = DB::table('integration_webhook_deliveries')
            ->where('status', 'delivered')
            ->whereNotNull('duration_ms')
            ->where('delivered_at', '>=', $since)
            ->selectRaw('AVG(duration_ms) as avg_ms, COUNT(*) as c')
            ->first();

        $slow = (int) DB::table('integration_webhook_deliveries')
            ->where('status', 'delivered')
            ->whereNotNull('duration_ms')
            ->where('delivered_at', '>=', $since)
            ->where('duration_ms', '>=', 5000)
            ->count();

        return [
            'avg' => $row && $row->avg_ms !== null ? (int) round((float) $row->avg_ms) : null,
            'p95' => null,
            'slow_count' => $slow,
        ];
    }

    public function failingWebhookEndpoints(Carbon $since): int
    {
        $v = DB::table('integration_webhook_deliveries')
            ->where('created_at', '>=', $since)
            ->whereIn('status', ['failed', 'dead_letter'])
            ->selectRaw('COUNT(DISTINCT webhook_id) as c')
            ->value('c');

        return (int) $v;
    }
}
