strprfcrm/app/Services/Import/ImportCommonCatalogService.php

<?php

declare(strict_types=1);

namespace App\Services\Import;

use App\Models\CommonCatalogItem;
use App\Models\File;
use App\Models\Import;
use App\Models\User;
use App\Services\Access\AccessService;
use App\Services\Export\ExportCommonCatalogService;
use App\Services\FileService;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\BaseDrawing;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Worksheet\MemoryDrawing;
use RuntimeException;
use Throwable;

class ImportCommonCatalogService
{
    public function __construct(
        private readonly Import $import,
        private readonly int $userId,
    ) {}

    public function handle(): bool
    {
        try {
            $path = Storage::disk('upload')->path((string) $this->import->filename);
            $spreadsheet = (new Xlsx)->load($path);
            $sheet = $spreadsheet->getActiveSheet();
            $this->assertHeaders(
                $sheet->rangeToArray('A1:AD1', null, true, true, false)[0],
                $sheet->rangeToArray('A2:AD2', null, true, true, false)[0],
            );
            $drawings = $this->drawingsByRow($sheet->getDrawingCollection());
            $articleCounts = $this->articleCounts($sheet);
            $user = User::query()->findOrFail($this->userId);
            $access = app(AccessService::class);

            $created = 0;
            $updated = 0;
            $errors = 0;

            for ($row = 3; $row <= $sheet->getHighestDataRow(); $row++) {
                $article = $this->stringValue($sheet->getCell("A{$row}")->getFormattedValue());
                if ($article === null) {
                    continue;
                }

                $data = [
                    'article' => $article,
                    'calculator_name' => $this->stringValue($sheet->getCell("B{$row}")->getValue()),
                    'kind' => $this->stringValue($sheet->getCell("C{$row}")->getValue()),
                    'dimension_length' => $this->dimensionValue($sheet->getCell("D{$row}")),
                    'dimension_width' => $this->dimensionValue($sheet->getCell("E{$row}")),
                    'dimension_height' => $this->dimensionValue($sheet->getCell("F{$row}")),
                    'site_length' => $this->dimensionValue($sheet->getCell("G{$row}")),
                    'site_width' => $this->dimensionValue($sheet->getCell("H{$row}")),
                    'fall_height' => $this->numericValue($sheet->getCell("I{$row}")->getCalculatedValue()),
                    'additional_info' => $this->stringValue($sheet->getCell("J{$row}")->getValue()),
                    'dimension_unit' => $this->stringValue($sheet->getCell("K{$row}")->getValue()),
                    'weight' => $this->numericValue($sheet->getCell("L{$row}")->getCalculatedValue()),
                    'volume' => $this->numericValue($sheet->getCell("M{$row}")->getCalculatedValue()),
                    'places' => $this->integerValue($sheet->getCell("N{$row}")->getCalculatedValue()),
                    'composition' => $this->stringValue($sheet->getCell("O{$row}")->getValue()),
                    'age_group' => $this->stringValue($sheet->getCell("P{$row}")->getFormattedValue()),
                    'max_users' => $this->integerValue($sheet->getCell("Q{$row}")->getCalculatedValue()),
                    'unit' => $this->stringValue($sheet->getCell("R{$row}")->getValue()),
                    'series' => $this->stringValue($sheet->getCell("T{$row}")->getValue()),
                    'trademark' => $this->stringValue($sheet->getCell("U{$row}")->getValue()),
                    'calculator_enabled' => $this->booleanValue($sheet->getCell("V{$row}")->getValue()),
                    'builders_price' => $this->numericValue($sheet->getCell("W{$row}")->getCalculatedValue()),
                    'wholesale_price' => $this->numericValue($sheet->getCell("X{$row}")->getCalculatedValue()),
                    'recommended_price' => $this->numericValue($sheet->getCell("Y{$row}")->getCalculatedValue()),
                    'retail_price' => $this->numericValue($sheet->getCell("Z{$row}")->getCalculatedValue()),
                    'project_price' => $this->numericValue($sheet->getCell("AA{$row}")->getCalculatedValue()),
                    'project_with_installation_price' => $this->numericValue($sheet->getCell("AB{$row}")->getCalculatedValue()),
                    'pik_price' => $this->numericValue($sheet->getCell("AC{$row}")->getCalculatedValue()),
                    'recommended_plus_10_price' => $this->numericValue($sheet->getCell("AD{$row}")->getCalculatedValue()),
                ];
                $data = $access->filterWritableData($user, 'common-catalog', $data);

                $validator = Validator::make($data, $this->rules());
                if ($validator->fails()) {
                    $errors++;
                    $this->import->log(
                        "Строка {$row}: ".implode(' ', $validator->errors()->all()),
                        'WARNING',
                    );

                    continue;
                }

                $validated = $validator->validated();
                $item = $this->findExistingItem(
                    $article,
                    $validated['calculator_name'],
                    $articleCounts[$article] ?? 1,
                );
                if ($item) {
                    $item->update($validated);
                    $updated++;
                } else {
                    $item = CommonCatalogItem::query()->create($validated);
                    $created++;
                }

                if (isset($drawings[$row])) {
                    try {
                        $this->replaceImage($item, $drawings[$row]);
                    } catch (Throwable $exception) {
                        $errors++;
                        $this->import->log(
                            "Строка {$row}: изображение не импортировано ({$exception->getMessage()}).",
                            'WARNING',
                        );
                    }
                }
            }

            [$technicalDescriptionsUpdated, $technicalDescriptionsErrors] = $this->importTechnicalDescriptions(
                $spreadsheet,
                $user,
                $access,
            );
            $errors += $technicalDescriptionsErrors;

            $this->import->log(
                "Создано: {$created}; обновлено: {$updated}; техописаний обновлено: {$technicalDescriptionsUpdated}; ошибок: {$errors}.",
            );
            $this->import->status = 'DONE';
            $this->import->save();
            $spreadsheet->disconnectWorksheets();

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

            throw $exception;
        }
    }

    private function assertHeaders(array $headers, array $subheaders): void
    {
        $actual = array_map($this->normalizeHeader(...), $headers);
        $expected = array_map($this->normalizeHeader(...), ExportCommonCatalogService::HEADERS);
        $actualSubheaders = array_map($this->normalizeHeader(...), $subheaders);
        $expectedSubheaders = array_map($this->normalizeHeader(...), ExportCommonCatalogService::SUBHEADERS);

        if ($actual !== $expected || $actualSubheaders !== $expectedSubheaders) {
            throw new RuntimeException('Некорректные заголовки файла общего каталога. Используйте утверждённый формат.');
        }
    }

    private function importTechnicalDescriptions(
        \PhpOffice\PhpSpreadsheet\Spreadsheet $spreadsheet,
        User $user,
        AccessService $access,
    ): array {
        $sheet = $spreadsheet->getSheetByName(ExportCommonCatalogService::TECHNICAL_DESCRIPTIONS_SHEET);
        if ($sheet === null) {
            return [0, 0];
        }

        $headers = $sheet->rangeToArray('A1:G1', null, true, true, false)[0];
        $actualHeaders = array_map($this->normalizeHeader(...), $headers);
        $expectedHeaders = array_map(
            $this->normalizeHeader(...),
            ExportCommonCatalogService::TECHNICAL_DESCRIPTION_HEADERS,
        );
        if ($actualHeaders !== $expectedHeaders) {
            throw new RuntimeException('Некорректные заголовки листа «Техописания». Используйте экспорт общего каталога.');
        }

        $updated = 0;
        $errors = 0;
        for ($row = 2; $row <= $sheet->getHighestDataRow(); $row++) {
            $article = $this->stringValue($sheet->getCell("A{$row}")->getFormattedValue());
            if ($article === null) {
                continue;
            }

            $calculatorName = $this->stringValue($sheet->getCell("B{$row}")->getValue());
            $item = $this->findTechnicalDescriptionItem($article, $calculatorName);
            if ($item === null) {
                $errors++;
                $this->import->log(
                    "Лист «Техописания», строка {$row}: позиция {$article} не найдена или неоднозначна.",
                    'WARNING',
                );

                continue;
            }

            $data = $access->filterWritableData($user, 'common-catalog', [
                'print_name' => $this->stringValue($sheet->getCell("C{$row}")->getValue()),
                'product_group' => $this->stringValue($sheet->getCell("D{$row}")->getValue()),
                'characteristics' => $this->stringValue($sheet->getCell("E{$row}")->getValue()),
                'technical_description' => $this->stringValue($sheet->getCell("F{$row}")->getValue()),
                'technical_description_short' => $this->stringValue($sheet->getCell("G{$row}")->getValue()),
            ]);
            $validator = Validator::make($data, $this->technicalDescriptionRules());
            if ($validator->fails()) {
                $errors++;
                $this->import->log(
                    "Лист «Техописания», строка {$row}: ".implode(' ', $validator->errors()->all()),
                    'WARNING',
                );

                continue;
            }

            $item->update($validator->validated());
            $updated++;
        }

        return [$updated, $errors];
    }

    private function findTechnicalDescriptionItem(string $article, ?string $calculatorName): ?CommonCatalogItem
    {
        $exact = CommonCatalogItem::query()
            ->where('article', $article)
            ->where('calculator_name', $calculatorName)
            ->first();
        if ($exact) {
            return $exact;
        }

        $items = CommonCatalogItem::query()
            ->where('article', $article)
            ->limit(2)
            ->get();

        return $items->count() === 1 ? $items->first() : null;
    }

    private function articleCounts(\PhpOffice\PhpSpreadsheet\Worksheet\Worksheet $sheet): array
    {
        $counts = [];
        for ($row = 3; $row <= $sheet->getHighestDataRow(); $row++) {
            $article = $this->stringValue($sheet->getCell("A{$row}")->getFormattedValue());
            if ($article !== null) {
                $counts[$article] = ($counts[$article] ?? 0) + 1;
            }
        }

        return $counts;
    }

    private function findExistingItem(
        string $article,
        ?string $name,
        int $sourceOccurrences,
    ): ?CommonCatalogItem {
        $exact = CommonCatalogItem::query()
            ->where('article', $article)
            ->where('calculator_name', $name)
            ->first();
        if ($exact) {
            return $exact;
        }

        if ($sourceOccurrences !== 1) {
            return null;
        }

        $items = CommonCatalogItem::query()
            ->where('article', $article)
            ->limit(2)
            ->get();

        return $items->count() === 1 ? $items->first() : null;
    }

    /** @param iterable<BaseDrawing> $drawings */
    private function drawingsByRow(iterable $drawings): array
    {
        $result = [];
        foreach ($drawings as $drawing) {
            if (preg_match('/^S(\d+)$/i', $drawing->getCoordinates(), $matches) === 1) {
                $result[(int) $matches[1]] = $drawing;
            }
        }

        return $result;
    }

    private function replaceImage(CommonCatalogItem $item, BaseDrawing $drawing): void
    {
        [$contents, $mimeType, $extension] = $this->drawingContents($drawing);
        $relativePath = "common_catalog/items/{$item->id}/image/".Str::uuid().".{$extension}";
        Storage::disk('public')->put($relativePath, $contents);

        $newImage = File::query()->create([
            'user_id' => $this->userId,
            'original_name' => "{$item->article}.{$extension}",
            'mime_type' => $mimeType,
            'path' => $relativePath,
            'link' => url('/storage/'.$relativePath),
        ]);
        app(FileService::class)->ensureThumbnail($newImage);

        $oldImage = $item->imageFile;
        $item->update(['image_file_id' => $newImage->id]);
        if ($oldImage) {
            app(FileService::class)->deleteFileWithThumbnail($oldImage);
            $oldImage->delete();
        }
    }

    private function drawingContents(BaseDrawing $drawing): array
    {
        if ($drawing instanceof MemoryDrawing) {
            ob_start();
            ($drawing->getRenderingFunction())($drawing->getImageResource());
            $contents = ob_get_clean();
            $mimeType = $drawing->getMimeType();
        } elseif ($drawing instanceof Drawing) {
            $contents = file_get_contents($drawing->getPath());
            $mimeType = $this->mimeType($contents ?: '');
        } else {
            throw new RuntimeException('Неподдерживаемый формат изображения.');
        }

        if (! is_string($contents) || $contents === '') {
            throw new RuntimeException('Пустое изображение.');
        }

        $extension = match ($mimeType) {
            'image/jpeg' => 'jpg',
            'image/png' => 'png',
            'image/webp' => 'webp',
            default => throw new RuntimeException("Неподдерживаемый MIME-тип {$mimeType}"),
        };

        return [$contents, $mimeType, $extension];
    }

    private function mimeType(string $contents): string
    {
        $info = new \finfo(FILEINFO_MIME_TYPE);

        return (string) $info->buffer($contents);
    }

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

    private function stringValue(mixed $value): ?string
    {
        $value = trim((string) $value);

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

    private function numericValue(mixed $value): int|float|string|null
    {
        $value = $this->stringValue($value);
        if ($value === null) {
            return null;
        }

        return str_replace([' ', ','], ['', '.'], $value);
    }

    private function dimensionValue(Cell $cell): ?string
    {
        $calculated = $cell->getCalculatedValue();
        if (is_int($calculated) || is_float($calculated)) {
            return rtrim(rtrim(number_format((float) $calculated, 10, '.', ''), '0'), '.');
        }

        return $this->stringValue($cell->getFormattedValue());
    }

    private function integerValue(mixed $value): int|string|null
    {
        $value = $this->numericValue($value);
        if ($value === null) {
            return null;
        }

        return is_numeric($value) ? (int) $value : (string) $value;
    }

    private function booleanValue(mixed $value): bool|string|null
    {
        $value = $this->stringValue($value);
        if ($value === null) {
            return null;
        }

        return match (mb_strtolower($value)) {
            'да', 'yes', '1' => true,
            'нет', 'no', '0' => false,
            default => $value,
        };
    }

    private function rules(): array
    {
        return [
            'article' => ['required', 'string', 'max:100'],
            'calculator_name' => ['nullable', 'string', 'max:255'],
            'kind' => ['nullable', 'string', 'max:255'],
            'dimension_length' => ['nullable', 'string', 'max:255'],
            'dimension_width' => ['nullable', 'string', 'max:255'],
            'dimension_height' => ['nullable', 'string', 'max:255'],
            'site_length' => ['nullable', 'string', 'max:255'],
            'site_width' => ['nullable', 'string', 'max:255'],
            'fall_height' => ['nullable', 'numeric', 'min:0'],
            'additional_info' => ['nullable', 'string'],
            'dimension_unit' => ['nullable', 'string', 'max:50'],
            'weight' => ['nullable', 'numeric', 'min:0'],
            'volume' => ['nullable', 'numeric', 'min:0'],
            'places' => ['nullable', 'integer', 'min:0'],
            'composition' => ['nullable', 'string'],
            'age_group' => ['nullable', 'string', 'max:100'],
            'max_users' => ['nullable', 'integer', 'min:0'],
            'unit' => ['nullable', 'string', 'max:50'],
            'series' => ['nullable', 'string', 'max:255'],
            'trademark' => ['nullable', 'string', 'max:255'],
            'calculator_enabled' => ['nullable', 'boolean'],
            'builders_price' => ['nullable', 'numeric', 'min:0'],
            'wholesale_price' => ['nullable', 'numeric', 'min:0'],
            'recommended_price' => ['nullable', 'numeric', 'min:0'],
            'retail_price' => ['nullable', 'numeric', 'min:0'],
            'project_price' => ['nullable', 'numeric', 'min:0'],
            'project_with_installation_price' => ['nullable', 'numeric', 'min:0'],
            'pik_price' => ['nullable', 'numeric', 'min:0'],
            'recommended_plus_10_price' => ['nullable', 'numeric', 'min:0'],
        ];
    }

    private function technicalDescriptionRules(): array
    {
        return [
            'print_name' => ['nullable', 'string', 'max:255'],
            'product_group' => ['nullable', 'string', 'max:255'],
            'characteristics' => ['nullable', 'string'],
            'technical_description' => ['nullable', 'string'],
            'technical_description_short' => ['nullable', 'string'],
        ];
    }
}