strprfcrm/app/Services/Import/ImportProductionOrdersService.php

<?php

declare(strict_types=1);

namespace App\Services\Import;

use App\Enums\ProductionOrderExecutionType;
use App\Enums\ProductionOrderSource;
use App\Enums\ProductionOrderStatus;
use App\Models\CommonCatalogItem;
use App\Models\Import;
use App\Models\ProductionOrder;
use App\Models\ProductionOrderItem;
use App\Models\Role;
use App\Models\User;
use App\Services\Export\ExportProductionOrdersService;
use App\Services\ProductionOrderService;
use DateTimeImmutable;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Validation\Rule;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Shared\Date as SpreadsheetDate;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use RuntimeException;
use Throwable;

final class ImportProductionOrdersService
{
    public function __construct(
        private readonly Import $import,
        private readonly int $userId,
        private readonly ProductionOrderService $orderService,
    ) {}

    public function handle(): bool
    {
        $spreadsheet = null;

        try {
            $actor = User::query()->findOrFail($this->userId);
            $spreadsheet = (new Xlsx)->load(
                Storage::disk('upload')->path((string) $this->import->filename),
            );
            $ordersSheet = $spreadsheet->getSheetByName(ExportProductionOrdersService::ORDERS_SHEET);
            $itemsSheet = $spreadsheet->getSheetByName(ExportProductionOrdersService::ITEMS_SHEET);
            if ($ordersSheet === null || $itemsSheet === null) {
                throw new RuntimeException('В файле должны быть листы «График заказов» и «МАФ».');
            }

            $this->assertHeaders($ordersSheet, $itemsSheet);
            $orders = $this->readOrders($ordersSheet);
            $this->readItems($itemsSheet, $orders);

            $created = 0;
            $updated = 0;
            DB::transaction(function () use ($orders, $actor, &$created, &$updated): void {
                foreach ($orders as $orderData) {
                    $payload = $orderData['data'];
                    $payload['items'] = $orderData['items'];

                    if ($orderData['order'] instanceof ProductionOrder) {
                        $this->orderService->update(
                            $orderData['order'],
                            $payload,
                            $actor,
                            true,
                        );
                        $updated++;
                    } else {
                        $this->orderService->create(
                            $payload,
                            $actor,
                            ProductionOrderSource::Spreadsheet,
                        );
                        $created++;
                    }
                }
            });

            $this->import->log("Создано заказов: {$created}; обновлено заказов: {$updated}.");
            $this->import->update(['status' => 'DONE']);

            return true;
        } catch (Throwable $exception) {
            $this->import->log($exception->getMessage(), 'ERROR');
            $this->import->update(['status' => 'ERROR']);

            throw $exception;
        } finally {
            $spreadsheet?->disconnectWorksheets();
        }
    }

    private function assertHeaders(Worksheet $ordersSheet, Worksheet $itemsSheet): void
    {
        $orderHeaders = $ordersSheet->rangeToArray('A1:R1', null, true, true, false)[0];
        $itemHeaders = $itemsSheet->rangeToArray('A1:H1', null, true, true, false)[0];

        if ($this->normalizedHeaders($orderHeaders) !== $this->normalizedHeaders(ExportProductionOrdersService::HEADERS)) {
            throw new RuntimeException('Некорректные заголовки листа «График заказов». Используйте экспорт графика заказов.');
        }
        if ($this->normalizedHeaders($itemHeaders) !== $this->normalizedHeaders(ExportProductionOrdersService::ITEM_HEADERS)) {
            throw new RuntimeException('Некорректные заголовки листа «МАФ». Используйте экспорт графика заказов.');
        }
    }

    /**
     * @return array<int, array{
     *     order: ProductionOrder|null,
     *     data: array<string, mixed>,
     *     items: list<array<string, mixed>>
     * }>
     */
    private function readOrders(Worksheet $sheet): array
    {
        $orders = [];
        $seenIds = [];
        $seenNumbers = [];

        for ($row = 2; $row <= $sheet->getHighestDataRow(); $row++) {
            if (! $this->rowHasValues($sheet, $row, 'A', 'R')) {
                continue;
            }

            $id = $this->integerValue($sheet->getCell("A{$row}"), 'ID заказа', 1, true);
            if ($id !== null && isset($seenIds[$id])) {
                throw new RuntimeException("Лист «График заказов», строка {$row}: ID {$id} указан повторно.");
            }

            $order = $id === null ? null : ProductionOrder::query()->find($id);
            if ($id !== null && $order === null) {
                throw new RuntimeException("Лист «График заказов», строка {$row}: заказ с ID {$id} не найден.");
            }

            $orderNumber = $this->requiredString($sheet->getCell("B{$row}"), 'Номер заказа');
            $orderYear = $this->integerValue($sheet->getCell("C{$row}"), 'Год', 2000, false, 2100);
            $numberKey = $this->normalize($orderNumber).'|'.$orderYear;
            if (isset($seenNumbers[$numberKey])) {
                throw new RuntimeException(
                    "Лист «График заказов», строка {$row}: заказ {$orderNumber} за {$orderYear} год указан повторно.",
                );
            }

            $data = [
                'order_number' => $orderNumber,
                'order_year' => $orderYear,
                'customer_name' => $this->requiredString($sheet->getCell("D{$row}"), 'Заказчик'),
                'object_address' => $this->requiredString($sheet->getCell("E{$row}"), 'Адрес объекта'),
                'invoice_number' => $this->requiredString($sheet->getCell("F{$row}"), 'Номер счёта'),
                'invoice_date' => $this->dateValue($sheet->getCell("G{$row}"), 'Дата счёта', false),
                'contract_number' => $this->nullableString($sheet->getCell("H{$row}")),
                'contract_date' => $this->dateValue($sheet->getCell("I{$row}"), 'Дата договора', false),
                'payment_date' => $this->dateValue($sheet->getCell("J{$row}"), 'Дата оплаты', true),
                'supply_working_days' => $this->integerValue(
                    $sheet->getCell("K{$row}"),
                    'Срок поставки',
                    0,
                    false,
                    2000,
                ),
                'application_shipment_date' => $this->dateValue(
                    $sheet->getCell("M{$row}"),
                    'Дата отгрузки по заявке',
                    false,
                ),
                'status' => $this->statusValue($sheet->getCell("N{$row}")),
                'execution_type' => $this->executionTypeValue($sheet->getCell("O{$row}")),
                'manager_id' => $this->managerId($sheet->getCell("P{$row}")),
                'note' => $this->nullableString($sheet->getCell("R{$row}")),
            ];

            $validator = Validator::make($data, [
                'order_number' => [
                    'required',
                    'string',
                    'max:100',
                    Rule::unique('production_orders', 'order_number')
                        ->where('order_year', $orderYear)
                        ->ignore($order?->id),
                ],
                'order_year' => ['required', 'integer', 'min:2000', 'max:2100'],
                'customer_name' => ['required', 'string', 'max:500'],
                'object_address' => ['required', 'string', 'max:5000'],
                'invoice_number' => ['required', 'string', 'max:100'],
                'invoice_date' => ['nullable', 'date'],
                'contract_number' => ['nullable', 'string', 'max:100'],
                'contract_date' => ['nullable', 'date'],
                'payment_date' => ['required', 'date'],
                'supply_working_days' => ['required', 'integer', 'min:0', 'max:2000'],
                'application_shipment_date' => ['nullable', 'date'],
                'status' => ['required', Rule::enum(ProductionOrderStatus::class)],
                'execution_type' => ['required', Rule::enum(ProductionOrderExecutionType::class)],
                'manager_id' => [
                    'required',
                    'integer',
                    Rule::exists('users', 'id')->where('role', Role::MANAGER),
                ],
                'note' => ['nullable', 'string', 'max:10000'],
            ]);
            if ($validator->fails()) {
                throw new RuntimeException(
                    "Лист «График заказов», строка {$row}: ".implode(' ', $validator->errors()->all()),
                );
            }

            $orders[$row] = [
                'order' => $order,
                'data' => $validator->validated(),
                'items' => [],
            ];
            if ($id !== null) {
                $seenIds[$id] = true;
            }
            $seenNumbers[$numberKey] = true;
        }

        if ($orders === []) {
            throw new RuntimeException('Лист «График заказов» не содержит заказов для импорта.');
        }

        return $orders;
    }

    /**
     * @param array<int, array{
     *     order: ProductionOrder|null,
     *     data: array<string, mixed>,
     *     items: list<array<string, mixed>>
     * }> $orders
     */
    private function readItems(Worksheet $sheet, array &$orders): void
    {
        $seenIds = [];

        for ($row = 2; $row <= $sheet->getHighestDataRow(); $row++) {
            if (! $this->rowHasValues($sheet, $row, 'A', 'H')) {
                continue;
            }

            $orderRow = $this->integerValue($sheet->getCell("A{$row}"), 'Строка заказа', 2);
            if (! isset($orders[$orderRow])) {
                throw new RuntimeException(
                    "Лист «МАФ», строка {$row}: строка заказа {$orderRow} отсутствует на листе «График заказов».",
                );
            }

            $itemId = $this->integerValue($sheet->getCell("B{$row}"), 'ID МАФ', 1, true);
            $existingItem = null;
            if ($itemId !== null) {
                if (isset($seenIds[$itemId])) {
                    throw new RuntimeException("Лист «МАФ», строка {$row}: ID МАФ {$itemId} указан повторно.");
                }
                $existingItem = ProductionOrderItem::query()->find($itemId);
                if ($existingItem === null) {
                    throw new RuntimeException("Лист «МАФ», строка {$row}: МАФ с ID {$itemId} не найден.");
                }
                $targetOrder = $orders[$orderRow]['order'];
                if ($targetOrder === null || $existingItem->production_order_id !== $targetOrder->id) {
                    throw new RuntimeException(
                        "Лист «МАФ», строка {$row}: МАФ с ID {$itemId} не принадлежит указанному заказу.",
                    );
                }
                $seenIds[$itemId] = true;
            }

            $catalogItemId = $this->integerValue(
                $sheet->getCell("C{$row}"),
                'ID позиции каталога',
                1,
            );
            if (! CommonCatalogItem::query()->whereKey($catalogItemId)->exists()) {
                throw new RuntimeException(
                    "Лист «МАФ», строка {$row}: позиция общего каталога с ID {$catalogItemId} не найдена.",
                );
            }

            $itemData = [
                'id' => $existingItem?->id,
                'common_catalog_item_id' => $catalogItemId,
                'order_item_number' => $this->requiredString(
                    $sheet->getCell("F{$row}"),
                    'Номер заказа МАФ',
                ),
                'factory_number' => $this->nullableString($sheet->getCell("G{$row}")),
                'manufacture_date' => $this->dateValue(
                    $sheet->getCell("H{$row}"),
                    'Дата производства',
                    false,
                ),
            ];
            $validator = Validator::make($itemData, [
                'id' => ['nullable', 'integer'],
                'common_catalog_item_id' => ['required', 'integer'],
                'order_item_number' => ['required', 'string', 'max:100'],
                'factory_number' => ['nullable', 'string', 'max:100'],
                'manufacture_date' => ['nullable', 'date'],
            ]);
            if ($validator->fails()) {
                throw new RuntimeException(
                    "Лист «МАФ», строка {$row}: ".implode(' ', $validator->errors()->all()),
                );
            }

            $orders[$orderRow]['items'][] = array_filter(
                $validator->validated(),
                static fn (mixed $value, string $key): bool => $key !== 'id' || $value !== null,
                ARRAY_FILTER_USE_BOTH,
            );
        }

        foreach ($orders as $row => $order) {
            if ($order['items'] === []) {
                throw new RuntimeException(
                    "Лист «График заказов», строка {$row}: на листе «МАФ» не указано оборудование заказа.",
                );
            }
        }
    }

    /** @param list<mixed> $headers */
    private function normalizedHeaders(array $headers): array
    {
        return array_map($this->normalize(...), $headers);
    }

    private function normalize(mixed $value): string
    {
        return mb_strtolower(trim((string) preg_replace('/\s+/u', ' ', (string) $value)));
    }

    private function rowHasValues(Worksheet $sheet, int $row, string $from, string $to): bool
    {
        $values = $sheet->rangeToArray("{$from}{$row}:{$to}{$row}", null, true, true, false)[0];

        return collect($values)->contains(static fn (mixed $value): bool => trim((string) $value) !== '');
    }

    private function requiredString(Cell $cell, string $label): string
    {
        $value = $this->nullableString($cell);
        if ($value === null) {
            throw new RuntimeException("Не заполнено поле «{$label}».");
        }

        return $value;
    }

    private function nullableString(Cell $cell): ?string
    {
        $value = trim((string) $cell->getFormattedValue());

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

    private function integerValue(
        Cell $cell,
        string $label,
        int $minimum,
        bool $nullable = false,
        ?int $maximum = null,
    ): ?int {
        $raw = $cell->getCalculatedValue();
        if ($raw === null || trim((string) $raw) === '') {
            if ($nullable) {
                return null;
            }

            throw new RuntimeException("Не заполнено поле «{$label}».");
        }

        $value = filter_var($raw, FILTER_VALIDATE_INT);
        if ($value === false || $value < $minimum || ($maximum !== null && $value > $maximum)) {
            throw new RuntimeException("Поле «{$label}» содержит недопустимое целое число.");
        }

        return $value;
    }

    private function dateValue(Cell $cell, string $label, bool $required): ?string
    {
        $raw = $cell->getCalculatedValue();
        if ($raw === null || trim((string) $raw) === '') {
            if ($required) {
                throw new RuntimeException("Не заполнено поле «{$label}».");
            }

            return null;
        }

        if (is_numeric($raw)) {
            try {
                return SpreadsheetDate::excelToDateTimeObject((float) $raw)->format('Y-m-d');
            } catch (Throwable) {
                throw new RuntimeException("Поле «{$label}» содержит некорректную дату.");
            }
        }

        $value = trim((string) $raw);
        foreach (['d.m.Y', 'Y-m-d', 'd/m/Y'] as $format) {
            $date = DateTimeImmutable::createFromFormat('!'.$format, $value);
            $errors = DateTimeImmutable::getLastErrors();
            if ($date !== false && ($errors === false || ($errors['warning_count'] === 0 && $errors['error_count'] === 0))) {
                return $date->format('Y-m-d');
            }
        }

        throw new RuntimeException("Поле «{$label}» содержит некорректную дату.");
    }

    private function managerId(Cell $cell): int
    {
        $name = $this->requiredString($cell, 'Менеджер');
        $managers = User::query()
            ->where('role', Role::MANAGER)
            ->where('name', $name)
            ->limit(2)
            ->get(['id']);
        if ($managers->isEmpty()) {
            throw new RuntimeException("Менеджер «{$name}» не найден.");
        }
        if ($managers->count() > 1) {
            throw new RuntimeException("Имя менеджера «{$name}» неоднозначно.");
        }

        return (int) $managers->first()->id;
    }

    private function statusValue(Cell $cell): string
    {
        $value = $this->normalize($this->requiredString($cell, 'Статус'));
        foreach (ProductionOrderStatus::cases() as $status) {
            if (in_array($value, [$this->normalize($status->value), $this->normalize($status->label())], true)) {
                return $status->value;
            }
        }

        throw new RuntimeException("Неизвестный статус «{$cell->getFormattedValue()}».");
    }

    private function executionTypeValue(Cell $cell): string
    {
        $value = $this->normalize($this->requiredString($cell, 'Тип исполнения'));
        foreach (ProductionOrderExecutionType::cases() as $type) {
            if (in_array($value, [$this->normalize($type->value), $this->normalize($type->label())], true)) {
                return $type->value;
            }
        }

        throw new RuntimeException("Неизвестный тип исполнения «{$cell->getFormattedValue()}».");
    }
}