portal-api/app/Services/IpPool/IpPoolDashboard.php

<?php

namespace App\Services\IpPool;

use App\Models\Customer;
use App\Models\IpPool;
use Illuminate\Support\Facades\DB;

class IpPoolDashboard
{
    public const TOP_CUSTOMERS = 15;

    private const ADDON_ROLE = 'ADDON';

    public function build(): array
    {
        $blocks = IpPool::query()
            ->selectRaw("COALESCE(NULLIF(ip_block, ''), '') as ip_block")
            ->selectRaw('COUNT(*) as total')
            ->selectRaw('SUM(ip_role = ?) as addon', [self::ADDON_ROLE])
            ->selectRaw("SUM(status = 'assigned') as assigned")
            ->selectRaw("SUM(status = 'free') as free")
            ->selectRaw("SUM(status = 'reserved') as reserved")
            ->selectRaw('COALESCE(SUM(monthly_cost), 0) as cost')
            ->selectRaw("COALESCE(SUM(CASE WHEN status = 'assigned' THEN selling_price END), 0) as revenue")
            ->groupBy(DB::raw("COALESCE(NULLIF(ip_block, ''), '')"))
            ->orderBy('ip_block')
            ->get()
            ->map(fn ($row) => $this->withRates([
                'ip_block' => $row->ip_block ?: null,
                'total'    => (int) $row->total,
                'addon'    => (int) $row->addon,
                'assigned' => (int) $row->assigned,
                'free'     => (int) $row->free,
                'reserved' => (int) $row->reserved,
                'cost'     => (float) $row->cost,
                'revenue'  => (float) $row->revenue,
            ]));

        $summary = collect(['total', 'addon', 'assigned', 'free', 'reserved', 'cost', 'revenue'])
            ->mapWithKeys(fn (string $key) => [$key => $blocks->sum($key)])
            ->all();
        $summary['primary'] = $summary['total'] - $summary['addon'];

        return [
            'summary'       => $this->withRates($summary),
            'blocks'        => $blocks->values(),
            'top_customers' => $this->topCustomers(),
        ];
    }

    private function withRates(array $stats): array
    {
        $profit = $stats['revenue'] - $stats['cost'];

        return [
            ...$stats,
            'usage_rate' => $stats['total'] ? $stats['assigned'] / $stats['total'] : 0,
            'profit'     => $profit,
            'margin'     => $stats['revenue'] ? $profit / $stats['revenue'] : null,
        ];
    }

    private function topCustomers(): array
    {
        $rows = IpPool::query()
            ->where('status', 'assigned')
            ->where(fn ($q) => $q->whereNotNull('customer_id')->orWhere('customer_name', '!=', ''))
            ->selectRaw('customer_id, MAX(customer_name) as customer_name')
            ->selectRaw('COUNT(*) as ips')
            ->selectRaw('COALESCE(SUM(selling_price), 0) as revenue')
            ->groupBy('customer_id', DB::raw('CASE WHEN customer_id IS NULL THEN customer_name END'))
            ->orderByDesc('ips')
            ->orderBy('customer_name')
            ->limit(self::TOP_CUSTOMERS)
            ->get();

        $customers = Customer::whereIn('id', $rows->pluck('customer_id')->filter())->get(['id', 'uuid', 'fullname'])->keyBy('id');

        return $rows->map(fn ($row) => [
            'customer_uuid' => $customers[$row->customer_id]->uuid ?? null,
            'customer_name' => $customers[$row->customer_id]->fullname ?? $row->customer_name,
            'ips'           => (int) $row->ips,
            'revenue'       => (float) $row->revenue,
        ])->all();
    }
}