<?php

namespace App\Services\Import;

use App\Models\Contract;
use App\Models\File;
use App\Models\MafOrder;
use App\Models\Order;
use App\Models\Product;
use App\Models\ProductSKU;
use App\Models\Reclamation;
use App\Models\ReclamationDetail;
use App\Models\Schedule;
use App\Models\Ttn;
use App\Models\User;
use Exception;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use ZipArchive;

class ImportYearDataService
{
    private string $tempDir;
    private array $manifest;
    private array $logs = [];

    // ID маппинги: old_id => new_id
    private array $productIdMapping = [];
    private array $mafOrderIdMapping = [];
    private array $orderIdMapping = [];
    private array $productSkuIdMapping = [];
    private array $reclamationIdMapping = [];
    private array $fileIdMapping = [];

    // Справочники для маппинга
    private array $districtMapping = [];
    private array $areaMapping = [];
    private array $userMapping = [];
    private array $objectTypeMapping = [];
    private array $orderStatusMapping = [];
    private array $reclamationStatusMapping = [];

    public function __construct(
        private readonly string $archivePath,
        private readonly int $year,
        private readonly int $userId,
        private readonly bool $clearExisting = false,
    ) {}

    public function handle(): bool
    {
        $this->prepareTempDirectory();

        try {
            $this->log("Начало импорта данных за {$this->year} год");

            // Распаковка архива
            $this->extractArchive();

            // Валидация манифеста
            $this->validateManifest();

            // Загрузка справочников для маппинга
            $this->loadDictionaries();

            // Очистка существующих данных (опционально)
            if ($this->clearExisting) {
                $this->log("Очистка существующих данных за {$this->year} год...");
                $this->clearExistingData();
            }

            DB::beginTransaction();

            try {
                // Импорт данных в правильном порядке
                $this->importProducts();
                $this->importMafOrders();
                $this->importOrders();
                $this->importProductsSku();
                $this->importReclamations();
                $this->importSchedules();
                $this->importContracts();
                $this->importTtn();

                // Импорт pivot таблиц (файлы заказов и рекламаций)
                $this->importPivotTables();

                DB::commit();
                $this->log("Импорт успешно завершён");

                return true;
            } catch (Exception $e) {
                DB::rollBack();
                throw $e;
            }
        } catch (Exception $e) {
            $this->log("Ошибка импорта: " . $e->getMessage(), 'ERROR');
            Log::error("ImportYearDataService error: " . $e->getMessage(), [
                'trace' => $e->getTraceAsString()
            ]);
            return false;
        } finally {
            $this->cleanupTempDirectory();
        }
    }

    private function prepareTempDirectory(): void
    {
        $this->tempDir = storage_path('app/temp/import_year_' . $this->year . '_' . Str::random(8));

        if (!is_dir($this->tempDir)) {
            mkdir($this->tempDir, 0755, true);
        }
    }

    private function extractArchive(): void
    {
        $this->log("Распаковка архива...");

        $zip = new ZipArchive();
        if ($zip->open($this->archivePath) !== true) {
            throw new Exception("Не удалось открыть архив");
        }

        $zip->extractTo($this->tempDir);
        $zip->close();

        $this->log("Архив распакован");
    }

    private function validateManifest(): void
    {
        $manifestPath = $this->tempDir . '/manifest.json';

        if (!file_exists($manifestPath)) {
            throw new Exception("Файл manifest.json не найден в архиве");
        }

        $this->manifest = json_decode(file_get_contents($manifestPath), true);

        if (!$this->manifest) {
            throw new Exception("Некорректный формат manifest.json");
        }

        if (!isset($this->manifest['version']) || !isset($this->manifest['year'])) {
            throw new Exception("Отсутствуют обязательные поля в manifest.json");
        }

        $this->log("Манифест валиден. Версия: {$this->manifest['version']}, Год экспорта: {$this->manifest['year']}");

        // Показываем статистику из манифеста
        if (isset($this->manifest['stats'])) {
            $this->log("Статистика из архива:");
            foreach ($this->manifest['stats'] as $entity => $count) {
                $this->log("  - {$entity}: {$count}");
            }
        }
    }

    private function loadDictionaries(): void
    {
        $this->log("Загрузка справочников...");

        // Округа
        $districts = DB::table('districts')->get();
        foreach ($districts as $d) {
            $this->districtMapping[$d->shortname] = $d->id;
        }

        // Районы
        $areas = DB::table('areas')->get();
        foreach ($areas as $a) {
            $this->areaMapping[$a->name] = $a->id;
        }

        // Пользователи
        $users = User::all();
        foreach ($users as $u) {
            $this->userMapping[$u->name] = $u->id;
        }

        // Типы объектов
        $objectTypes = DB::table('object_types')->get();
        foreach ($objectTypes as $ot) {
            $this->objectTypeMapping[$ot->name] = $ot->id;
        }

        // Статусы заказов
        $orderStatuses = DB::table('order_statuses')->get();
        foreach ($orderStatuses as $os) {
            $this->orderStatusMapping[$os->name] = $os->id;
        }

        // Статусы рекламаций
        $reclamationStatuses = DB::table('reclamation_statuses')->get();
        foreach ($reclamationStatuses as $rs) {
            $this->reclamationStatusMapping[$rs->name] = $rs->id;
        }

        $this->log("Справочники загружены");
    }

    private function clearExistingData(): void
    {
        // Используем логику из ClearYearDataJob
        $orderIds = Order::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->pluck('id');
        $productIds = Product::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->pluck('id');
        $productSkuIds = ProductSKU::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->pluck('id');
        $reclamationIds = Reclamation::whereIn('order_id', $orderIds)->pluck('id');

        // Собираем файлы до удаления связей
        $fileIds = $this->collectFileIds($orderIds, $productIds, $productSkuIds);

        // Рекламации
        DB::table('reclamation_details')->whereIn('reclamation_id', $reclamationIds)->delete();
        DB::table('reclamation_product_sku')->whereIn('reclamation_id', $reclamationIds)->delete();
        DB::table('reclamation_photo_before')->whereIn('reclamation_id', $reclamationIds)->delete();
        DB::table('reclamation_photo_after')->whereIn('reclamation_id', $reclamationIds)->delete();
        DB::table('reclamation_document')->whereIn('reclamation_id', $reclamationIds)->delete();
        DB::table('reclamation_act')->whereIn('reclamation_id', $reclamationIds)->delete();
        Reclamation::whereIn('id', $reclamationIds)->delete();

        // Связи заказов
        DB::table('order_photo')->whereIn('order_id', $orderIds)->delete();
        DB::table('order_document')->whereIn('order_id', $orderIds)->delete();
        DB::table('order_statement')->whereIn('order_id', $orderIds)->delete();

        // Расписания
        Schedule::whereIn('order_id', $orderIds)->delete();

        // SKU
        ProductSKU::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->forceDelete();

        // Заказы
        Order::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->forceDelete();

        // МАФ заказы
        MafOrder::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->forceDelete();

        // Продукты
        Product::withoutGlobalScopes()->withTrashed()
            ->whereIn('id', $productIds)
            ->update(['certificate_id' => null]);
        Product::withoutGlobalScopes()->withTrashed()->where('year', $this->year)->forceDelete();

        // ТТН
        Ttn::where('year', $this->year)->update(['file_id' => null]);
        Ttn::where('year', $this->year)->delete();

        // Контракты
        Contract::where('year', $this->year)->delete();

        // Файлы
        $this->deleteFiles($fileIds);

        $this->log("Существующие данные очищены");
    }

    private function collectFileIds($orderIds, $productIds, $productSkuIds): \Illuminate\Support\Collection
    {
        $fileIds = collect();

        $fileIds = $fileIds->merge(DB::table('order_photo')->whereIn('order_id', $orderIds)->pluck('file_id'));
        $fileIds = $fileIds->merge(DB::table('order_document')->whereIn('order_id', $orderIds)->pluck('file_id'));
        $fileIds = $fileIds->merge(DB::table('order_statement')->whereIn('order_id', $orderIds)->pluck('file_id'));

        $reclamationIds = Reclamation::whereIn('order_id', $orderIds)->pluck('id');

        $fileIds = $fileIds->merge(DB::table('reclamation_photo_before')->whereIn('reclamation_id', $reclamationIds)->pluck('file_id'));
        $fileIds = $fileIds->merge(DB::table('reclamation_photo_after')->whereIn('reclamation_id', $reclamationIds)->pluck('file_id'));
        $fileIds = $fileIds->merge(DB::table('reclamation_document')->whereIn('reclamation_id', $reclamationIds)->pluck('file_id'));
        $fileIds = $fileIds->merge(DB::table('reclamation_act')->whereIn('reclamation_id', $reclamationIds)->pluck('file_id'));

        $fileIds = $fileIds->merge(
            Product::withoutGlobalScopes()->withTrashed()
                ->whereIn('id', $productIds)
                ->whereNotNull('certificate_id')
                ->pluck('certificate_id')
        );

        $fileIds = $fileIds->merge(
            ProductSKU::withoutGlobalScopes()->withTrashed()
                ->whereIn('id', $productSkuIds)
                ->whereNotNull('passport_id')
                ->pluck('passport_id')
        );

        $fileIds = $fileIds->merge(
            Ttn::where('year', $this->year)->whereNotNull('file_id')->pluck('file_id')
        );

        return $fileIds->unique();
    }

    private function deleteFiles($fileIds): void
    {
        $files = File::whereIn('id', $fileIds)->get();

        foreach ($files as $file) {
            if ($file->path && Storage::disk('public')->exists($file->path)) {
                Storage::disk('public')->delete($file->path);
            }
        }

        File::whereIn('id', $fileIds)->delete();
    }

    /**
     * Безопасно получает значение из строки Excel
     * Возвращает null для пустых строк, или значение по умолчанию
     */
    private function getValue(array $row, array $headerMap, string $key, mixed $default = null): mixed
    {
        if (!isset($headerMap[$key])) {
            return $default;
        }
        $value = $row[$headerMap[$key]] ?? null;
        if ($value === null || $value === '') {
            return $default;
        }
        return $value;
    }

    /**
     * Получает строковое значение (пустая строка вместо null)
     */
    private function getStringValue(array $row, array $headerMap, string $key, string $default = ''): string
    {
        $value = $this->getValue($row, $headerMap, $key);
        return $value !== null ? (string) $value : $default;
    }

    /**
     * Получает числовое значение
     */
    private function getNumericValue(array $row, array $headerMap, string $key, int|float $default = 0): int|float
    {
        $value = $this->getValue($row, $headerMap, $key);
        if ($value === null) {
            return $default;
        }
        return is_numeric($value) ? $value : $default;
    }

    private function importProducts(): void
    {
        $this->log("Импорт продуктов...");

        $filePath = $this->tempDir . '/data/products.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл products.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldId = $this->getValue($row, $headerMap, 'id');
            $nomenclatureNumber = $this->getStringValue($row, $headerMap, 'nomenclature_number', '');

            // Проверяем существует ли продукт
            $existing = Product::withoutGlobalScopes()
                ->where('year', $this->year)
                ->where('nomenclature_number', $nomenclatureNumber)
                ->first();

            // Импорт сертификата если есть
            $certificateId = null;
            $certificatePath = $this->getValue($row, $headerMap, 'certificate_file');
            if ($certificatePath) {
                $certificateId = $this->importFile($certificatePath);
            }

            $productData = [
                'year' => $this->year,
                'article' => $this->getStringValue($row, $headerMap, 'article'),
                'name_tz' => $this->getValue($row, $headerMap, 'name_tz'),
                'type_tz' => $this->getValue($row, $headerMap, 'type_tz'),
                'nomenclature_number' => $nomenclatureNumber,
                'sizes' => $this->getValue($row, $headerMap, 'sizes'),
                'manufacturer' => $this->getValue($row, $headerMap, 'manufacturer'),
                'unit' => $this->getStringValue($row, $headerMap, 'unit', 'шт.'),
                'type' => $this->getStringValue($row, $headerMap, 'type', 'Товар'),
                'product_price' => $this->getNumericValue($row, $headerMap, 'product_price', 0),
                'installation_price' => $this->getNumericValue($row, $headerMap, 'installation_price', 0),
                'total_price' => $this->getNumericValue($row, $headerMap, 'total_price', 0),
                'manufacturer_name' => $this->getValue($row, $headerMap, 'manufacturer_name'),
                'note' => $this->getValue($row, $headerMap, 'note'),
                'passport_name' => $this->getValue($row, $headerMap, 'passport_name'),
                'statement_name' => $this->getValue($row, $headerMap, 'statement_name'),
                'service_life' => $this->getValue($row, $headerMap, 'service_life'),
                'certificate_number' => $this->getValue($row, $headerMap, 'certificate_number'),
                'certificate_date' => $this->getValue($row, $headerMap, 'certificate_date'),
                'certificate_issuer' => $this->getValue($row, $headerMap, 'certificate_issuer'),
                'certificate_type' => $this->getValue($row, $headerMap, 'certificate_type'),
                'weight' => $this->getNumericValue($row, $headerMap, 'weight', 0),
                'volume' => $this->getNumericValue($row, $headerMap, 'volume', 0),
                'places' => $this->getNumericValue($row, $headerMap, 'places', 0),
                'certificate_id' => $certificateId,
            ];

            if ($existing) {
                $existing->update($productData);
                $this->productIdMapping[$oldId] = $existing->id;
            } else {
                $product = Product::withoutGlobalScopes()->create($productData);
                $this->productIdMapping[$oldId] = $product->id;
            }

            $count++;
        }

        $this->log("Импортировано продуктов: {$count}");
    }

    private function importMafOrders(): void
    {
        $this->log("Импорт заказов МАФ...");

        $filePath = $this->tempDir . '/data/maf_orders.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл maf_orders.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldId = $this->getValue($row, $headerMap, 'id');
            $oldProductId = $this->getValue($row, $headerMap, 'product_id');
            $orderNumber = $this->getStringValue($row, $headerMap, 'order_number', '');

            // Получаем новый product_id
            $newProductId = $this->productIdMapping[$oldProductId] ?? null;
            if (!$newProductId) {
                // Пробуем найти по номенклатуре
                $nomenclature = $this->getValue($row, $headerMap, 'product_nomenclature');
                if ($nomenclature) {
                    $product = Product::withoutGlobalScopes()
                        ->where('year', $this->year)
                        ->where('nomenclature_number', $nomenclature)
                        ->first();
                    $newProductId = $product?->id;
                }
            }

            if (!$newProductId) {
                $this->log("Пропуск MafOrder {$oldId}: продукт не найден", 'WARNING');
                continue;
            }

            // Проверяем существует ли
            $existing = MafOrder::withoutGlobalScopes()
                ->where('year', $this->year)
                ->where('product_id', $newProductId)
                ->where('order_number', $orderNumber)
                ->first();

            // Получаем user_id
            $userName = $this->getValue($row, $headerMap, 'user_name');
            $userId = $this->userMapping[$userName] ?? $this->userId;

            $mafOrderData = [
                'year' => $this->year,
                'order_number' => $orderNumber,
                'status' => $this->getValue($row, $headerMap, 'status'),
                'user_id' => $userId,
                'product_id' => $newProductId,
                'quantity' => $this->getNumericValue($row, $headerMap, 'quantity', 0),
                'in_stock' => $this->getNumericValue($row, $headerMap, 'in_stock', 0),
            ];

            if ($existing) {
                $existing->update($mafOrderData);
                $this->mafOrderIdMapping[$oldId] = $existing->id;
            } else {
                $mafOrder = MafOrder::withoutGlobalScopes()->create($mafOrderData);
                $this->mafOrderIdMapping[$oldId] = $mafOrder->id;
            }

            $count++;
        }

        $this->log("Импортировано заказов МАФ: {$count}");
    }

    private function importOrders(): void
    {
        $this->log("Импорт заказов (площадок)...");

        $filePath = $this->tempDir . '/data/orders.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл orders.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldId = $this->getValue($row, $headerMap, 'id');
            $objectAddress = $this->getStringValue($row, $headerMap, 'object_address', '');

            // Проверяем существует ли
            $existing = Order::withoutGlobalScopes()
                ->where('year', $this->year)
                ->where('object_address', $objectAddress)
                ->first();

            // Маппинг справочников
            $districtShortname = $this->getValue($row, $headerMap, 'district_shortname');
            $districtId = $this->districtMapping[$districtShortname] ?? null;

            $areaName = $this->getValue($row, $headerMap, 'area_name');
            $areaId = $this->areaMapping[$areaName] ?? null;

            $userName = $this->getValue($row, $headerMap, 'user_name');
            $userId = $this->userMapping[$userName] ?? $this->userId;

            $objectTypeName = $this->getValue($row, $headerMap, 'object_type_name');
            $objectTypeId = $this->objectTypeMapping[$objectTypeName] ?? null;

            $brigadierName = $this->getValue($row, $headerMap, 'brigadier_name');
            $brigadierId = $this->userMapping[$brigadierName] ?? null;

            $orderStatusName = $this->getValue($row, $headerMap, 'order_status_name');
            $orderStatusId = $this->orderStatusMapping[$orderStatusName] ?? Order::STATUS_NEW;

            $orderData = [
                'year' => $this->year,
                'name' => $this->getValue($row, $headerMap, 'name'),
                'user_id' => $userId,
                'district_id' => $districtId,
                'area_id' => $areaId,
                'object_address' => $objectAddress,
                'object_type_id' => $objectTypeId,
                'comment' => $this->getValue($row, $headerMap, 'comment'),
                'installation_date' => $this->getValue($row, $headerMap, 'installation_date'),
                'ready_date' => $this->getValue($row, $headerMap, 'ready_date'),
                'brigadier_id' => $brigadierId,
                'order_status_id' => $orderStatusId,
                'tg_group_name' => $this->getValue($row, $headerMap, 'tg_group_name'),
                'tg_group_link' => $this->getValue($row, $headerMap, 'tg_group_link'),
                'ready_to_mount' => $this->getValue($row, $headerMap, 'ready_to_mount'),
                'install_days' => $this->getValue($row, $headerMap, 'install_days'),
            ];

            if ($existing) {
                $existing->update($orderData);
                $this->orderIdMapping[$oldId] = $existing->id;
            } else {
                $order = Order::withoutGlobalScopes()->create($orderData);
                $this->orderIdMapping[$oldId] = $order->id;
            }

            $count++;
        }

        $this->log("Импортировано заказов: {$count}");
    }

    private function importProductsSku(): void
    {
        $this->log("Импорт SKU продуктов...");

        $filePath = $this->tempDir . '/data/products_sku.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл products_sku.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldId = $this->getValue($row, $headerMap, 'id');

            // Маппинг product_id
            $oldProductId = $this->getValue($row, $headerMap, 'product_id');
            $newProductId = $this->productIdMapping[$oldProductId] ?? null;

            if (!$newProductId) {
                // Пробуем найти по номенклатуре
                $nomenclature = $this->getValue($row, $headerMap, 'product_nomenclature');
                if ($nomenclature) {
                    $product = Product::withoutGlobalScopes()
                        ->where('year', $this->year)
                        ->where('nomenclature_number', $nomenclature)
                        ->first();
                    $newProductId = $product?->id;
                }
            }

            if (!$newProductId) {
                $this->log("Пропуск SKU {$oldId}: продукт не найден", 'WARNING');
                continue;
            }

            // Маппинг order_id
            $oldOrderId = $this->getValue($row, $headerMap, 'order_id');
            $newOrderId = $this->orderIdMapping[$oldOrderId] ?? null;

            if (!$newOrderId) {
                // Пробуем найти по адресу
                $orderAddress = $this->getValue($row, $headerMap, 'order_address');
                if ($orderAddress) {
                    $order = Order::withoutGlobalScopes()
                        ->where('year', $this->year)
                        ->where('object_address', $orderAddress)
                        ->first();
                    $newOrderId = $order?->id;
                }
            }

            if (!$newOrderId) {
                $this->log("Пропуск SKU {$oldId}: заказ не найден", 'WARNING');
                continue;
            }

            // Маппинг maf_order_id
            $oldMafOrderId = $this->getValue($row, $headerMap, 'maf_order_id');
            $newMafOrderId = null;
            if ($oldMafOrderId) {
                $newMafOrderId = $this->mafOrderIdMapping[$oldMafOrderId] ?? null;

                if (!$newMafOrderId) {
                    // Пробуем найти по номеру заказа
                    $mafOrderNumber = $this->getValue($row, $headerMap, 'maf_order_number');
                    if ($mafOrderNumber) {
                        $mafOrder = MafOrder::withoutGlobalScopes()
                            ->where('year', $this->year)
                            ->where('product_id', $newProductId)
                            ->where('order_number', $mafOrderNumber)
                            ->first();
                        $newMafOrderId = $mafOrder?->id;
                    }
                }
            }

            // Импорт паспорта если есть
            $passportId = null;
            $passportPath = $this->getValue($row, $headerMap, 'passport_file');
            if ($passportPath) {
                $passportId = $this->importFile($passportPath);
            }

            $skuData = [
                'year' => $this->year,
                'product_id' => $newProductId,
                'order_id' => $newOrderId,
                'maf_order_id' => $newMafOrderId,
                'status' => $this->getValue($row, $headerMap, 'status'),
                'rfid' => $this->getValue($row, $headerMap, 'rfid'),
                'factory_number' => $this->getValue($row, $headerMap, 'factory_number'),
                'manufacture_date' => $this->getValue($row, $headerMap, 'manufacture_date'),
                'statement_number' => $this->getValue($row, $headerMap, 'statement_number'),
                'statement_date' => $this->getValue($row, $headerMap, 'statement_date'),
                'upd_number' => $this->getValue($row, $headerMap, 'upd_number'),
                'comment' => $this->getValue($row, $headerMap, 'comment'),
                'passport_id' => $passportId,
            ];

            $sku = ProductSKU::withoutGlobalScopes()->create($skuData);
            $this->productSkuIdMapping[$oldId] = $sku->id;

            $count++;
        }

        $this->log("Импортировано SKU: {$count}");
    }

    private function importReclamations(): void
    {
        $this->log("Импорт рекламаций...");

        $filePath = $this->tempDir . '/data/reclamations.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл reclamations.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);

        // Лист 1: Reclamations
        $sheet = $spreadsheet->getSheetByName('Reclamations');
        if (!$sheet) {
            $this->log("Лист Reclamations не найден", 'WARNING');
            return;
        }

        $rows = $sheet->toArray();
        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldId = $this->getValue($row, $headerMap, 'id');

            // Маппинг order_id
            $oldOrderId = $this->getValue($row, $headerMap, 'order_id');
            $newOrderId = $this->orderIdMapping[$oldOrderId] ?? null;

            if (!$newOrderId) {
                // Пробуем найти по адресу
                $orderAddress = $this->getValue($row, $headerMap, 'order_address');
                if ($orderAddress) {
                    $order = Order::withoutGlobalScopes()
                        ->where('year', $this->year)
                        ->where('object_address', $orderAddress)
                        ->first();
                    $newOrderId = $order?->id;
                }
            }

            if (!$newOrderId) {
                $this->log("Пропуск рекламации {$oldId}: заказ не найден", 'WARNING');
                continue;
            }

            $userName = $this->getValue($row, $headerMap, 'user_name');
            $userId = $this->userMapping[$userName] ?? $this->userId;

            $brigadierName = $this->getValue($row, $headerMap, 'brigadier_name');
            $brigadierId = $this->userMapping[$brigadierName] ?? null;

            $statusName = $this->getValue($row, $headerMap, 'status_name');
            $statusId = $this->reclamationStatusMapping[$statusName] ?? Reclamation::STATUS_NEW;

            $reclamationData = [
                'order_id' => $newOrderId,
                'user_id' => $userId,
                'status_id' => $statusId,
                'reason' => $this->getValue($row, $headerMap, 'reason'),
                'guarantee' => $this->getValue($row, $headerMap, 'guarantee'),
                'whats_done' => $this->getValue($row, $headerMap, 'whats_done'),
                'create_date' => $this->getValue($row, $headerMap, 'create_date'),
                'finish_date' => $this->getValue($row, $headerMap, 'finish_date'),
                'start_work_date' => $this->getValue($row, $headerMap, 'start_work_date'),
                'work_days' => $this->getValue($row, $headerMap, 'work_days'),
                'brigadier_id' => $brigadierId,
                'comment' => $this->getValue($row, $headerMap, 'comment'),
            ];

            $reclamation = Reclamation::create($reclamationData);
            $this->reclamationIdMapping[$oldId] = $reclamation->id;

            $count++;
        }

        $this->log("Импортировано рекламаций: {$count}");

        // Лист 2: ReclamationDetails
        $this->importReclamationDetails($spreadsheet);

        // Лист 3: ReclamationSKU
        $this->importReclamationSkuRelations($spreadsheet);
    }

    private function importReclamationDetails(Spreadsheet $spreadsheet): void
    {
        $sheet = $spreadsheet->getSheetByName('ReclamationDetails');
        if (!$sheet) {
            return;
        }

        $rows = $sheet->toArray();
        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldReclamationId = $this->getValue($row, $headerMap, 'reclamation_id');
            $newReclamationId = $this->reclamationIdMapping[$oldReclamationId] ?? null;

            if (!$newReclamationId) {
                continue;
            }

            ReclamationDetail::create([
                'reclamation_id' => $newReclamationId,
                'name' => $this->getStringValue($row, $headerMap, 'name', ''),
                'quantity' => $this->getNumericValue($row, $headerMap, 'quantity', 0),
            ]);

            $count++;
        }

        $this->log("Импортировано деталей рекламаций: {$count}");
    }

    private function importReclamationSkuRelations(Spreadsheet $spreadsheet): void
    {
        $sheet = $spreadsheet->getSheetByName('ReclamationSKU');
        if (!$sheet) {
            return;
        }

        $rows = $sheet->toArray();
        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            $oldReclamationId = $this->getValue($row, $headerMap, 'reclamation_id');
            $oldProductSkuId = $this->getValue($row, $headerMap, 'product_sku_id');

            if (!$oldReclamationId || !$oldProductSkuId) {
                continue;
            }

            $newReclamationId = $this->reclamationIdMapping[$oldReclamationId] ?? null;
            $newProductSkuId = $this->productSkuIdMapping[$oldProductSkuId] ?? null;

            if (!$newReclamationId || !$newProductSkuId) {
                continue;
            }

            DB::table('reclamation_product_sku')->insert([
                'reclamation_id' => $newReclamationId,
                'product_sku_id' => $newProductSkuId,
            ]);

            $count++;
        }

        $this->log("Импортировано связей рекламация-SKU: {$count}");
    }

    private function importSchedules(): void
    {
        $this->log("Импорт расписаний...");

        $filePath = $this->tempDir . '/data/schedules.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл schedules.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            $oldOrderId = $this->getValue($row, $headerMap, 'order_id');
            $newOrderId = $this->orderIdMapping[$oldOrderId] ?? null;

            if (!$newOrderId) {
                continue;
            }

            $districtShortname = $this->getValue($row, $headerMap, 'district_shortname');
            $districtId = $this->districtMapping[$districtShortname] ?? null;

            $areaName = $this->getValue($row, $headerMap, 'area_name');
            $areaId = $this->areaMapping[$areaName] ?? null;

            $brigadierName = $this->getValue($row, $headerMap, 'brigadier_name');
            $brigadierId = $this->userMapping[$brigadierName] ?? null;

            Schedule::create([
                'installation_date' => $this->getValue($row, $headerMap, 'installation_date'),
                'address_code' => $this->getValue($row, $headerMap, 'address_code'),
                'manual' => $this->getValue($row, $headerMap, 'manual'),
                'source' => $this->getValue($row, $headerMap, 'source'),
                'order_id' => $newOrderId,
                'district_id' => $districtId,
                'area_id' => $areaId,
                'object_address' => $this->getValue($row, $headerMap, 'object_address'),
                'object_type' => $this->getValue($row, $headerMap, 'object_type'),
                'mafs' => $this->getValue($row, $headerMap, 'mafs'),
                'mafs_count' => $this->getNumericValue($row, $headerMap, 'mafs_count', 0),
                'brigadier_id' => $brigadierId,
                'comment' => $this->getValue($row, $headerMap, 'comment'),
            ]);

            $count++;
        }

        $this->log("Импортировано расписаний: {$count}");
    }

    private function importContracts(): void
    {
        $this->log("Импорт контрактов...");

        $filePath = $this->tempDir . '/data/contracts.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл contracts.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            Contract::create([
                'year' => $this->year,
                'contract_number' => $this->getStringValue($row, $headerMap, 'contract_number', ''),
                'contract_date' => $this->getValue($row, $headerMap, 'contract_date'),
            ]);

            $count++;
        }

        $this->log("Импортировано контрактов: {$count}");
    }

    private function importTtn(): void
    {
        $this->log("Импорт ТТН...");

        $filePath = $this->tempDir . '/data/ttn.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл ttn.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = $sheet->toArray();

        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            if (empty($row[$headerMap['id']])) {
                continue;
            }

            // Импорт файла ТТН если есть
            $fileId = null;
            $ttnFilePath = $this->getValue($row, $headerMap, 'file_path');
            if ($ttnFilePath) {
                $fileId = $this->importFile($ttnFilePath);
            }

            Ttn::create([
                'year' => $this->year,
                'ttn_number' => $this->getStringValue($row, $headerMap, 'ttn_number', ''),
                'ttn_number_suffix' => $this->getStringValue($row, $headerMap, 'ttn_number_suffix', ''),
                'order_number' => $this->getStringValue($row, $headerMap, 'order_number', ''),
                'order_date' => $this->getValue($row, $headerMap, 'order_date'),
                'order_sum' => $this->getNumericValue($row, $headerMap, 'order_sum', 0),
                'skus' => $this->getValue($row, $headerMap, 'skus'),
                'file_id' => $fileId,
            ]);

            $count++;
        }

        $this->log("Импортировано ТТН: {$count}");
    }

    private function importPivotTables(): void
    {
        $this->log("Импорт pivot таблиц (связей файлов)...");

        $filePath = $this->tempDir . '/data/pivot_tables.xlsx';
        if (!file_exists($filePath)) {
            $this->log("Файл pivot_tables.xlsx не найден, пропуск", 'WARNING');
            return;
        }

        $spreadsheet = IOFactory::load($filePath);

        // Order photos
        $this->importPivotSheet($spreadsheet, 'order_photo', 'order_id', $this->orderIdMapping);

        // Order documents
        $this->importPivotSheet($spreadsheet, 'order_document', 'order_id', $this->orderIdMapping);

        // Order statements
        $this->importPivotSheet($spreadsheet, 'order_statement', 'order_id', $this->orderIdMapping);

        // Reclamation photos before
        $this->importPivotSheet($spreadsheet, 'reclamation_photo_before', 'reclamation_id', $this->reclamationIdMapping);

        // Reclamation photos after
        $this->importPivotSheet($spreadsheet, 'reclamation_photo_after', 'reclamation_id', $this->reclamationIdMapping);

        // Reclamation documents
        $this->importPivotSheet($spreadsheet, 'reclamation_document', 'reclamation_id', $this->reclamationIdMapping);

        // Reclamation acts
        $this->importPivotSheet($spreadsheet, 'reclamation_act', 'reclamation_id', $this->reclamationIdMapping);
    }

    private function importPivotSheet(
        Spreadsheet $spreadsheet,
        string $sheetName,
        string $foreignKey,
        array $idMapping
    ): void {
        $sheet = $spreadsheet->getSheetByName($sheetName);
        if (!$sheet) {
            return;
        }

        $rows = $sheet->toArray();
        $headers = array_shift($rows);
        $headerMap = array_flip($headers);

        $count = 0;
        foreach ($rows as $row) {
            $oldEntityId = $row[$headerMap[$foreignKey]] ?? null;
            $archivePath = $row[$headerMap['file_archive_path']] ?? null;

            if (!$oldEntityId || !$archivePath) {
                continue;
            }

            $newEntityId = $idMapping[$oldEntityId] ?? null;
            if (!$newEntityId) {
                continue;
            }

            // Импортируем файл
            $fileId = $this->importFile($archivePath);
            if (!$fileId) {
                continue;
            }

            // Вставляем связь
            DB::table($sheetName)->insert([
                $foreignKey => $newEntityId,
                'file_id' => $fileId,
            ]);

            $count++;
        }

        if ($count > 0) {
            $this->log("Импортировано связей {$sheetName}: {$count}");
        }
    }

    private function importFile(string $archivePath): ?int
    {
        // Проверяем кэш
        if (isset($this->fileIdMapping[$archivePath])) {
            return $this->fileIdMapping[$archivePath];
        }

        $sourcePath = $this->tempDir . '/' . $archivePath;

        if (!file_exists($sourcePath)) {
            $this->log("Файл не найден: {$archivePath}", 'WARNING');
            return null;
        }

        // Определяем путь для сохранения
        $pathParts = explode('/', $archivePath);
        array_shift($pathParts); // Убираем 'files'

        $relativePath = implode('/', $pathParts);
        $fileName = basename($archivePath);
        $targetPath = dirname($relativePath) . '/' . $fileName;

        // Сохраняем файл
        $content = file_get_contents($sourcePath);
        Storage::disk('public')->put($targetPath, $content);

        // Создаем запись в БД
        $originalName = preg_replace('/^\d+_/', '', $fileName); // Убираем ID из имени
        $mimeType = mime_content_type($sourcePath) ?: 'application/octet-stream';

        $file = File::create([
            'user_id' => $this->userId,
            'original_name' => $originalName,
            'mime_type' => $mimeType,
            'path' => $targetPath,
            'link' => url('/storage/' . $targetPath),
        ]);

        $this->fileIdMapping[$archivePath] = $file->id;

        return $file->id;
    }

    private function cleanupTempDirectory(): void
    {
        if (is_dir($this->tempDir)) {
            $this->deleteDirectory($this->tempDir);
        }
    }

    private function deleteDirectory(string $dir): void
    {
        if (!is_dir($dir)) {
            return;
        }

        $files = array_diff(scandir($dir), ['.', '..']);
        foreach ($files as $file) {
            $path = $dir . '/' . $file;
            is_dir($path) ? $this->deleteDirectory($path) : unlink($path);
        }
        rmdir($dir);
    }

    private function log(string $message, string $level = 'INFO'): void
    {
        $this->logs[] = [
            'level' => $level,
            'message' => $message,
            'timestamp' => now()->toIso8601String(),
        ];

        if ($level === 'ERROR') {
            Log::error("ImportYearDataService: {$message}");
        } else {
            Log::info("ImportYearDataService: {$message}");
        }
    }

    public function getLogs(): array
    {
        return $this->logs;
    }
}