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

<?php

namespace App\Services\IpPool;

use Carbon\Carbon;
use Carbon\Exceptions\InvalidFormatException;
use DateTimeInterface;
use Illuminate\Http\UploadedFile;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date as ExcelDate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;

class IpPoolSheetReader
{
    public const SHEET_NAME = 'IP_POOL';

    private const READERS = ['xlsx' => 'Xlsx', 'xls' => 'Xls', 'csv' => 'Csv', 'txt' => 'Csv'];

    private const HEADERS = [
        'TEN_MANG'          => 'ip_block',
        'CIDR'              => 'cidr',
        'OWNER_TYPE'        => 'owner_type',
        'IP_ADDRESS'        => 'ip_address',
        'IP_ROLE'           => 'ip_role',
        'PARENT_IP'         => 'parent_ip',
        'SUBNET_MASK'       => 'netmask',
        'GATEWAY'           => 'gateway',
        'TEN_KHACH_HANG'    => 'customer_name',
        'TEN_DICH_VU'       => 'service_name',
        'NGAY_CAP_PHAT'     => 'allocated_at',
        'NGAY_THU_HOI'      => 'revoked_at',
        'TRANG_THAI'        => 'sheet_status',
        'CONTACT_EMAIL'     => 'contact_email',
        'CONTACT_CC'        => 'contact_cc',
        'CONTACT_PHONE'     => 'contact_phone',
        'MONTHLY_COST'      => 'monthly_cost',
        'SELLING_PRICE'     => 'selling_price',
        'THONG_TIN_BO_SUNG' => 'additional_info',
        'LAST_EMAIL_TYPE'   => 'last_email_type',
        'LAST_EMAIL_AT'     => 'last_email_at',
        'UPDATED_BY'        => 'updated_by',
        'GHI_CHU'           => 'note',
    ];

    private const UPPERCASE_FIELDS = ['owner_type', 'ip_role', 'sheet_status', 'last_email_type'];

    /**
     * @return array{rows: array<int, array<string, mixed>>, errors: array<int, array{row: int, message: string}>, skipped_blank: int}
     */
    public function read(UploadedFile $file): array
    {
        $type = self::READERS[strtolower($file->getClientOriginalExtension())] ?? 'Csv';
        $reader = IOFactory::createReader($type);
        $reader->setReadDataOnly(true);

        if ($type !== 'Csv') {
            $sheets = $reader->listWorksheetNames($file->getRealPath());
            $reader->setLoadSheetsOnly(in_array(self::SHEET_NAME, $sheets, true) ? self::SHEET_NAME : $sheets[0]);
        }

        return $this->parse($this->cachedValues($reader->load($file->getRealPath())->getActiveSheet()));
    }

    /**
     * @return array<int, array<int, mixed>>
     */
    private function cachedValues(Worksheet $sheet): array
    {
        $table = [];
        foreach ($sheet->getRowIterator() as $row) {
            $cells = [];
            foreach ($row->getCellIterator() as $cell) {
                $cells[] = $cell->isFormula() ? $cell->getOldCalculatedValue() : $cell->getValue();
            }
            $table[] = $cells;
        }

        return $table;
    }

    /**
     * @param  array<int, array<int, mixed>>  $table
     */
    public function parse(array $table): array
    {
        $columns = $this->mapHeader(array_shift($table) ?? []);
        $rows = [];
        $errors = [];
        $skippedBlank = 0;

        foreach ($table as $index => $cells) {
            $line = $index + 2;
            $raw = [];
            foreach ($columns as $position => $field) {
                $raw[$field] = $cells[$position] ?? null;
            }

            if ($this->clean($raw['ip_address'] ?? null) === null) {
                $skippedBlank++;
                continue;
            }

            $problems = [];
            $row = $this->normalize($raw, $problems);

            if ($problems) {
                $errors[] = ['row' => $line, 'message' => implode('; ', $problems)];
                continue;
            }

            $rows[$line] = $row;
        }

        return ['rows' => $rows, 'errors' => $errors, 'skipped_blank' => $skippedBlank];
    }

    /**
     * @return array<int, string>
     */
    private function mapHeader(array $header): array
    {
        $columns = [];
        foreach ($header as $position => $title) {
            $field = self::HEADERS[strtoupper((string) $this->clean($title))] ?? null;
            if ($field && !in_array($field, $columns, true)) {
                $columns[$position] = $field;
            }
        }

        return $columns;
    }

    /**
     * @param  array<int, string>  $problems
     */
    private function normalize(array $raw, array &$problems): array
    {
        $row = [];
        foreach (array_keys(self::HEADERS) as $header) {
            $field = self::HEADERS[$header];
            $row[$field] = $this->clean($raw[$field] ?? null);
        }

        foreach (self::UPPERCASE_FIELDS as $field) {
            $row[$field] = $row[$field] === null ? null : mb_strtoupper($row[$field]);
        }

        $row['ip_block'] = $row['ip_block'] === null ? null : preg_replace('/\s+/', ' ', $row['ip_block']);

        if (!filter_var($row['ip_address'], FILTER_VALIDATE_IP)) {
            $problems[] = "IP không hợp lệ: {$row['ip_address']}";
        }

        $row['cidr'] = preg_match('/(\d{1,2})$/', (string) $row['cidr'], $m) && (int) $m[1] <= 32 ? (int) $m[1] : null;
        $row['contact_email'] = $this->emails($row['contact_email'], 'CONTACT_EMAIL', $problems);
        $row['contact_cc'] = $this->emails($row['contact_cc'], 'CONTACT_CC', $problems);
        $row['contact_phone'] = $this->phone($row['contact_phone']);
        $row['monthly_cost'] = $this->money($raw['monthly_cost'] ?? null, 'MONTHLY_COST', $problems);
        $row['selling_price'] = $this->money($raw['selling_price'] ?? null, 'SELLING_PRICE', $problems);
        $row['allocated_at'] = $this->date($raw['allocated_at'] ?? null, false);
        $row['revoked_at'] = $this->date($raw['revoked_at'] ?? null, false);
        $row['last_email_at'] = $this->date($raw['last_email_at'] ?? null, true);

        return $row;
    }

    private function clean(mixed $value): ?string
    {
        if ($value === null || $value instanceof DateTimeInterface) {
            return $value?->format('Y-m-d H:i:s');
        }

        $value = trim(str_replace("\u{00A0}", ' ', (string) $value), " \t\n\r\0\x0B");

        return $value === '' ? null : $value;
    }

    private function emails(?string $value, string $label, array &$problems): ?string
    {
        if ($value === null) {
            return null;
        }

        $emails = array_values(array_filter(array_map('trim', preg_split('/[,;\s]+/', $value))));
        foreach ($emails as $email) {
            if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
                $problems[] = "{$label} không hợp lệ: {$email}";
            }
        }

        return $emails ? implode(', ', $emails) : null;
    }

    private function phone(?string $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $digits = preg_replace('/[\s.\-]/', '', $value);

        return preg_match('/^[1-9]\d{8}$/', $digits) ? '0' . $digits : $digits;
    }

    private function money(mixed $value, string $label, array &$problems): ?float
    {
        $text = $this->clean($value);
        if ($text === null) {
            return null;
        }

        if (is_numeric($text)) {
            return round((float) $text, 2);
        }

        if (preg_match('/^\d{1,3}([.,]\d{3})+$/', $text)) {
            return (float) preg_replace('/[.,]/', '', $text);
        }

        $problems[] = "{$label} không phải số: {$text}";

        return null;
    }

    private function date(mixed $value, bool $withTime): ?string
    {
        if ($value instanceof DateTimeInterface) {
            $date = Carbon::instance($value);
        } elseif (is_numeric($value)) {
            $date = Carbon::instance(ExcelDate::excelToDateTimeObject((float) $value));
        } else {
            $date = $this->parseDateText($this->clean($value));
        }

        if ($date === null) {
            return null;
        }

        return $withTime ? $date->toDateTimeString() : $date->toDateString();
    }

    private function parseDateText(?string $text): ?Carbon
    {
        if ($text === null) {
            return null;
        }

        foreach (['j/n/Y H:i:s', 'j/n/Y H:i', 'j/n/Y', 'Y-m-d H:i:s', 'Y-m-d'] as $format) {
            try {
                return Carbon::createFromFormat('!' . $format, $text);
            } catch (InvalidFormatException) {
                continue;
            }
        }

        return null;
    }
}