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

<?php

namespace App\Services\IpPool;

use App\Models\IpPool;
use App\Models\ModuleIpAddress;
use Illuminate\Http\UploadedFile;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class IpPoolSheetImporter
{
    private const ERROR_LIMIT = 100;

    private const CHUNK = 500;

    private const NETWORK_FIELDS = ['gateway', 'netmask'];

    private const COPIED_FIELDS = [
        'ip_block', 'gateway', 'netmask', 'owner_type', 'ip_role', 'parent_ip',
        'customer_name', 'service_name', 'allocated_at', 'revoked_at',
        'contact_email', 'contact_cc', 'contact_phone', 'monthly_cost', 'selling_price',
        'additional_info', 'last_email_type', 'last_email_at', 'updated_by', 'note',
    ];

    public function __construct(private readonly IpPoolSheetReader $reader) {}

    public function preview(UploadedFile $file): array
    {
        return $this->run($file, false);
    }

    public function import(UploadedFile $file): array
    {
        return $this->run($file, true);
    }

    public static function resolveStatus(?string $currentStatus, bool $systemOwned, ?string $sheetStatus): string
    {
        if ($currentStatus !== null && $systemOwned) {
            return $currentStatus;
        }

        return match ($sheetStatus) {
            'ALLOCATED' => 'assigned',
            'FREE', 'REVOKED' => 'free',
            default => $currentStatus ?? 'free',
        };
    }

    private function run(UploadedFile $file, bool $apply): array
    {
        $sheet = $this->reader->read($file);
        $rows = [];
        foreach ($sheet['rows'] as $row) {
            $rows[$row['ip_address']] = $row;
        }

        $summary = [
            'total_rows'     => count($sheet['rows']) + count($sheet['errors']) + $sheet['skipped_blank'],
            'valid'          => count($rows),
            'skipped_blank'  => $sheet['skipped_blank'],
            'duplicates'     => count($sheet['rows']) - count($rows),
            'error_count'    => count($sheet['errors']),
            'errors'         => array_slice($sheet['errors'], 0, self::ERROR_LIMIT),
            'create'         => 0,
            'update'         => 0,
            'restore'        => 0,
            'to_assigned'    => 0,
            'to_free'        => 0,
            'kept_by_system' => 0,
        ];

        [$inserts, $updates] = $this->plan($rows, $summary);

        if ($apply) {
            DB::transaction(function () use ($inserts, $updates) {
                foreach (array_chunk($inserts, self::CHUNK) as $chunk) {
                    IpPool::insert($chunk);
                }
                foreach (array_chunk($updates, self::CHUNK) as $chunk) {
                    IpPool::withTrashed()->upsert($chunk, ['id'], [...self::COPIED_FIELDS, 'cidr', 'status', 'deleted_at']);
                }
            });
            IpPool::resyncAllContacts();
        }

        return $summary;
    }

    /**
     * @param  array<string, array<string, mixed>>  $rows
     * @return array{0: array<int, array<string, mixed>>, 1: array<int, array<string, mixed>>}
     */
    private function plan(array $rows, array &$summary): array
    {
        $ips = array_keys($rows);
        $existing = $this->existingByIp($ips);
        $inUse = $this->inUseKeys($ips);
        $now = now()->toDateTimeString();

        $inserts = [];
        $updates = [];
        foreach ($rows as $ip => $row) {
            $pools = $existing->get($ip);

            if ($pools === null) {
                $status = self::resolveStatus(null, false, $row['sheet_status']);
                $summary['create']++;
                $summary['to_assigned'] += $status === 'assigned' ? 1 : 0;
                $inserts[] = $this->record($row, null, $status) + ['created_at' => $now, 'updated_at' => $now];
                continue;
            }

            foreach ($pools as $pool) {
                $systemOwned = $pool->status === 'reserved'
                    || $pool->service_id !== null
                    || isset($inUse[$pool->module_network_id . '|' . $ip]);
                $status = self::resolveStatus($pool->status, $systemOwned, $row['sheet_status']);

                $summary[$pool->deleted_at ? 'restore' : 'update']++;
                if ($systemOwned) {
                    $summary['kept_by_system']++;
                } elseif ($pool->status !== $status) {
                    $summary[$status === 'assigned' ? 'to_assigned' : 'to_free']++;
                }

                $updates[] = $this->record($row, $pool, $status);
            }
        }

        return [$inserts, $updates];
    }

    /**
     * @param  array<int, string>  $ips
     * @return Collection<string, Collection<int, IpPool>>
     */
    private function existingByIp(array $ips): Collection
    {
        return collect($ips)->chunk(1000)->flatMap(fn ($chunk) => IpPool::withTrashed()
            ->whereIn('ip_address', $chunk->all())
            ->get(['id', 'uuid', 'ip_address', 'module_network_id', 'status', 'service_id', 'cidr', 'gateway', 'netmask', 'deleted_at'])
        )->groupBy('ip_address');
    }

    /**
     * @param  array<int, string>  $ips
     * @return array<string, true>
     */
    private function inUseKeys(array $ips): array
    {
        $keys = [];
        foreach (array_chunk($ips, 1000) as $chunk) {
            foreach (ModuleIpAddress::where('status', 1)->whereIn('ip', $chunk)->get(['ip', 'module_network_id']) as $mirror) {
                $keys[$mirror->module_network_id . '|' . $mirror->ip] = true;
            }
        }

        return $keys;
    }

    private function record(array $row, ?IpPool $pool, string $status): array
    {
        $record = [
            'uuid'              => $pool?->uuid ?? (string) Str::uuid(),
            'ip_address'        => $row['ip_address'],
            'module_network_id' => $pool?->module_network_id,
            'cidr'              => $row['cidr'] ?? $pool?->cidr ?? 24,
            'status'            => $status,
            'deleted_at'        => null,
        ];

        foreach (self::COPIED_FIELDS as $field) {
            $record[$field] = $row[$field];
        }

        foreach (self::NETWORK_FIELDS as $field) {
            $record[$field] ??= $pool?->{$field};
        }

        return $pool ? ['id' => $pool->id] + $record : $record;
    }
}