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

<?php

namespace App\Services\IpPool;

use App\Models\IpPool;
use App\Models\ModuleIpAddress;
use App\Models\ModuleVmware;
use App\Models\Service;
use App\Models\VirtualMachines;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;

class IpPoolUsageResolver
{
    private const CHUNK = 1000;

    /**
     * @param  Collection<int, IpPool>  $pools
     * @return array<string, array<string, mixed>>
     */
    public function forPools(Collection $pools): array
    {
        $inUse = $this->chunked($pools->pluck('ip_address'), fn ($ips) => ModuleIpAddress::where('status', 1)
            ->whereIn('ip', $ips)
            ->get(['id', 'ip', 'vm_name']))
            ->keyBy('ip');

        if ($inUse->isEmpty()) {
            return [];
        }

        $ips = $inUse->keys();
        $vmwares = $this->vmwares($inUse);
        $services = $this->services($vmwares);
        $vcenterVms = $this->vcenterVms($ips, $inUse->pluck('vm_name')->filter());

        return $inUse->mapWithKeys(function (ModuleIpAddress $mirror, string $ip) use ($vmwares, $services, $vcenterVms) {
            $vmware = $vmwares->get($ip);
            $service = $vmware ? $services->get($vmware->id) : null;
            $vcenterVm = $vcenterVms->get($ip);

            return [$ip => [
                'assigned'       => true,
                'vm_name'        => $vmware?->name ?? $vcenterVm?->name ?? $mirror->vm_name,
                'vm_id'          => $vmware?->id,
                'vcenter_vm_id'  => $vcenterVm?->id,
                'service_id'     => $service?->id,
                'service_name'   => $service?->server_name,
                'service_status' => $service?->status,
                'customer_name'  => $service?->customer?->fullname,
                'customer_email' => $service?->customer?->email,
                'customer_uuid'  => $service?->customer?->uuid,
            ]];
        })->all();
    }

    /**
     * @param  Collection<string, ModuleIpAddress>  $inUse
     * @return Collection<string, ModuleVmware>
     */
    private function vmwares(Collection $inUse): Collection
    {
        $mirrorIp = $inUse->mapWithKeys(fn (ModuleIpAddress $mirror) => [$mirror->id => $mirror->ip]);

        return ModuleVmware::query()
            ->where(fn ($query) => $query->whereIn('module_ip_address', $mirrorIp->keys())->orWhereIn('ip', $inUse->keys()))
            ->get(['id', 'name', 'ip', 'module_ip_address', 'service_id', 'customer_id'])
            ->keyBy(fn (ModuleVmware $vm) => $mirrorIp[$vm->module_ip_address] ?? $vm->ip);
    }

    /**
     * @param  Collection<string, ModuleVmware>  $vmwares
     * @return Collection<int, Service>
     */
    private function services(Collection $vmwares): Collection
    {
        if ($vmwares->isEmpty()) {
            return collect();
        }

        $services = Service::with('customer:id,uuid,fullname,email')
            ->where(fn ($query) => $query->whereIn('id', $vmwares->pluck('service_id')->filter())->orWhereIn('vm_id', $vmwares->pluck('id')))
            ->get(['id', 'server_name', 'status', 'customer_id', 'vm_id']);

        return $vmwares->mapWithKeys(fn (ModuleVmware $vm) => [
            $vm->id => $services->firstWhere('id', $vm->service_id) ?? $services->firstWhere('vm_id', $vm->id),
        ])->filter();
    }

    /**
     * @param  Collection<int, string>  $ips
     * @param  Collection<int, string>  $vmNames
     * @return Collection<string, VirtualMachines>
     */
    private function vcenterVms(Collection $ips, Collection $vmNames): Collection
    {
        $prefix = DB::raw("SUBSTRING_INDEX(name, '_', 1)");

        return VirtualMachines::query()
            ->where(fn ($query) => $query->whereIn('ip', $ips)->orWhereIn('name', $vmNames)->orWhereIn($prefix, $ips))
            ->orderByDesc('id')
            ->get(['id', 'name', 'ip'])
            ->reduce(function (Collection $byIp, VirtualMachines $vm) use ($ips) {
                $ip = $vm->ip && $ips->contains($vm->ip) ? $vm->ip : strstr($vm->name, '_', true);
                if ($ip && $ips->contains($ip) && !$byIp->has($ip)) {
                    $byIp->put($ip, $vm);
                }

                return $byIp;
            }, collect());
    }

    private function chunked(Collection $values, callable $query): Collection
    {
        return $values->chunk(self::CHUNK)->flatMap(fn (Collection $chunk) => $query($chunk->values()->all()));
    }
}