strprfcrm/app/Services/Export/ExportYearDataService.php

<?php

namespace App\Services\Export;

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 Exception;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use RecursiveDirectoryIterator;
use RecursiveIteratorIterator;
use ZipArchive;

class ExportYearDataService
{
    private string $tempDir;
    private string $dataDir;
    private string $filesDir;
    private array $stats = [];
    private array $fileMapping = []; // file_id => archive_path

    public function __construct(
        private readonly int $year,
        private readonly int $userId,
    ) {}

    public function handle(): string
    {
        $this->prepareTempDirectories();

        try {
            // Экспорт данных в Excel
            $this->exportProducts();
            $this->exportMafOrders();
            $this->exportOrders();
            $this->exportProductsSku();
            $this->exportReclamations();
            $this->exportSchedules();
            $this->exportContracts();
            $this->exportTtn();

            // Копирование файлов и создание pivot таблицы
            $this->copyFilesAndExportPivots();

            // Создание манифеста
            $this->createManifest();

            // Создание ZIP архива
            $archivePath = $this->createArchive();

            return $archivePath;
        } finally {
            // Очистка временной директории
            $this->cleanupTempDirectory();
        }
    }

    private function prepareTempDirectories(): void
    {
        $this->tempDir = storage_path('app/temp/export_year_' . $this->year . '_' . Str::random(8));
        $this->dataDir = $this->tempDir . '/data';
        $this->filesDir = $this->tempDir . '/files';

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

    /**
     * Записывает строку данных в лист Excel
     */
    private function writeRow($sheet, int $row, array $data): void
    {
        $col = 1;
        foreach ($data as $value) {
            $cellAddress = Coordinate::stringFromColumnIndex($col) . $row;
            $sheet->setCellValue($cellAddress, $value);
            $col++;
        }
    }

    private function exportProducts(): void
    {
        $products = Product::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->with('certificate')
            ->cursor();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Products');

        // Заголовки
        $headers = [
            'id', 'article', 'name_tz', 'type_tz', 'nomenclature_number', 'sizes',
            'manufacturer', 'unit', 'type', 'product_price', 'installation_price',
            'total_price', 'manufacturer_name', 'note', 'passport_name', 'statement_name',
            'service_life', 'certificate_number', 'certificate_date', 'certificate_issuer',
            'certificate_type', 'weight', 'volume', 'places', 'certificate_file',
            'created_at', 'updated_at', 'deleted_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        $count = 0;
        foreach ($products as $product) {
            $certificatePath = '';
            if ($product->certificate_id && $product->certificate) {
                $certificatePath = $this->mapFileToArchive($product->certificate, 'products/certificates');
            }

            $this->writeRow($sheet, $row, [
                $product->id,
                $product->article,
                $product->name_tz,
                $product->type_tz,
                $product->nomenclature_number,
                $product->sizes,
                $product->manufacturer,
                $product->unit,
                $product->type,
                $product->getRawOriginal('product_price'),
                $product->getRawOriginal('installation_price'),
                $product->getRawOriginal('total_price'),
                $product->manufacturer_name,
                $product->note,
                $product->passport_name,
                $product->statement_name,
                $product->service_life,
                $product->certificate_number,
                $product->certificate_date,
                $product->certificate_issuer,
                $product->certificate_type,
                $product->weight,
                $product->volume,
                $product->places,
                $certificatePath,
                $product->created_at?->toIso8601String(),
                $product->updated_at?->toIso8601String(),
                $product->deleted_at?->toIso8601String(),
            ]);

            $row++;
            $count++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/products.xlsx');
        $this->stats['products'] = $count;
    }

    private function exportMafOrders(): void
    {
        $mafOrders = MafOrder::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->with(['user', 'product'])
            ->cursor();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('MafOrders');

        $headers = [
            'id', 'order_number', 'status', 'user_id', 'user_name', 'product_id',
            'product_nomenclature', 'quantity', 'in_stock', 'created_at', 'updated_at', 'deleted_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        $count = 0;
        foreach ($mafOrders as $mafOrder) {
            $this->writeRow($sheet, $row, [
                $mafOrder->id,
                $mafOrder->order_number,
                $mafOrder->status,
                $mafOrder->user_id,
                $mafOrder->user?->name,
                $mafOrder->product_id,
                $mafOrder->product?->nomenclature_number,
                $mafOrder->quantity,
                $mafOrder->in_stock,
                $mafOrder->created_at?->toIso8601String(),
                $mafOrder->updated_at?->toIso8601String(),
                $mafOrder->deleted_at?->toIso8601String(),
            ]);

            $row++;
            $count++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/maf_orders.xlsx');
        $this->stats['maf_orders'] = $count;
    }

    private function exportOrders(): void
    {
        $orders = Order::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->with(['user', 'district', 'area', 'objectType', 'brigadier', 'orderStatus'])
            ->cursor();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Orders');

        $headers = [
            'id', 'name', 'user_id', 'user_name', 'district_id', 'district_shortname',
            'area_id', 'area_name', 'object_address', 'object_type_id', 'object_type_name',
            'comment', 'installation_date', 'ready_date', 'brigadier_id', 'brigadier_name',
            'order_status_id', 'order_status_name', 'tg_group_name', 'tg_group_link',
            'ready_to_mount', 'install_days', 'created_at', 'updated_at', 'deleted_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        $count = 0;
        foreach ($orders as $order) {
            $this->writeRow($sheet, $row, [
                $order->id,
                $order->name,
                $order->user_id,
                $order->user?->name,
                $order->district_id,
                $order->district?->shortname,
                $order->area_id,
                $order->area?->name,
                $order->object_address,
                $order->object_type_id,
                $order->objectType?->name,
                $order->comment,
                $order->installation_date,
                $order->ready_date,
                $order->brigadier_id,
                $order->brigadier?->name,
                $order->order_status_id,
                $order->orderStatus?->name,
                $order->tg_group_name,
                $order->tg_group_link,
                $order->ready_to_mount,
                $order->install_days,
                $order->created_at?->toIso8601String(),
                $order->updated_at?->toIso8601String(),
                $order->deleted_at?->toIso8601String(),
            ]);

            $row++;
            $count++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/orders.xlsx');
        $this->stats['orders'] = $count;
    }

    private function exportProductsSku(): void
    {
        $skus = ProductSKU::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->with(['product', 'order', 'maf_order', 'passport'])
            ->cursor();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('ProductsSKU');

        $headers = [
            'id', 'product_id', 'product_nomenclature', 'order_id', 'order_address',
            'maf_order_id', 'maf_order_number', 'status', 'rfid', 'factory_number',
            'manufacture_date', 'statement_number', 'statement_date', 'upd_number',
            'comment', 'passport_file', 'created_at', 'updated_at', 'deleted_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        $count = 0;
        foreach ($skus as $sku) {
            $passportPath = '';
            if ($sku->passport_id && $sku->passport) {
                $passportPath = $this->mapFileToArchive($sku->passport, 'products_sku/passports');
            }

            $this->writeRow($sheet, $row, [
                $sku->id,
                $sku->product_id,
                $sku->product?->nomenclature_number,
                $sku->order_id,
                $sku->order?->object_address,
                $sku->maf_order_id,
                $sku->maf_order?->order_number,
                $sku->status,
                $sku->rfid,
                $sku->factory_number,
                $sku->manufacture_date,
                $sku->statement_number,
                $sku->statement_date,
                $sku->upd_number,
                $sku->comment,
                $passportPath,
                $sku->created_at?->toIso8601String(),
                $sku->updated_at?->toIso8601String(),
                $sku->deleted_at?->toIso8601String(),
            ]);

            $row++;
            $count++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/products_sku.xlsx');
        $this->stats['products_sku'] = $count;
    }

    private function exportReclamations(): void
    {
        $orderIds = Order::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->pluck('id');

        $reclamations = Reclamation::whereIn('order_id', $orderIds)
            ->with(['order', 'user', 'brigadier', 'status'])
            ->get();

        $spreadsheet = new Spreadsheet();

        // Лист 1: Reclamations
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Reclamations');

        $headers = [
            'id', 'order_id', 'order_address', 'user_id', 'user_name', 'status_id',
            'status_name', 'reason', 'guarantee', 'whats_done', 'create_date',
            'finish_date', 'start_work_date', 'work_days', 'brigadier_id',
            'brigadier_name', 'comment', 'created_at', 'updated_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        foreach ($reclamations as $reclamation) {
            $this->writeRow($sheet, $row, [
                $reclamation->id,
                $reclamation->order_id,
                $reclamation->order?->object_address,
                $reclamation->user_id,
                $reclamation->user?->name,
                $reclamation->status_id,
                $reclamation->status?->name,
                $reclamation->reason,
                $reclamation->guarantee,
                $reclamation->whats_done,
                $reclamation->create_date,
                $reclamation->finish_date,
                $reclamation->start_work_date,
                $reclamation->work_days,
                $reclamation->brigadier_id,
                $reclamation->brigadier?->name,
                $reclamation->comment,
                $reclamation->created_at?->toIso8601String(),
                $reclamation->updated_at?->toIso8601String(),
            ]);
            $row++;
        }

        // Лист 2: ReclamationDetails
        $sheet2 = $spreadsheet->createSheet();
        $sheet2->setTitle('ReclamationDetails');

        $headers2 = ['id', 'reclamation_id', 'name', 'quantity', 'created_at', 'updated_at'];
        $this->writeRow($sheet2, 1, $headers2);

        $reclamationIds = $reclamations->pluck('id');
        $details = ReclamationDetail::whereIn('reclamation_id', $reclamationIds)->get();

        $row = 2;
        $detailsCount = 0;
        foreach ($details as $detail) {
            $this->writeRow($sheet2, $row, [
                $detail->id,
                $detail->reclamation_id,
                $detail->name,
                $detail->quantity,
                $detail->created_at?->toIso8601String(),
                $detail->updated_at?->toIso8601String(),
            ]);
            $row++;
            $detailsCount++;
        }

        // Лист 3: ReclamationSKU (many-to-many)
        $sheet3 = $spreadsheet->createSheet();
        $sheet3->setTitle('ReclamationSKU');

        $headers3 = ['reclamation_id', 'product_sku_id'];
        $this->writeRow($sheet3, 1, $headers3);

        $skuRelations = DB::table('reclamation_product_sku')
            ->whereIn('reclamation_id', $reclamationIds)
            ->get();

        $row = 2;
        foreach ($skuRelations as $relation) {
            $this->writeRow($sheet3, $row, [
                $relation->reclamation_id,
                $relation->product_sku_id,
            ]);
            $row++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/reclamations.xlsx');
        $this->stats['reclamations'] = $reclamations->count();
        $this->stats['reclamation_details'] = $detailsCount;
    }

    private function exportSchedules(): void
    {
        $orderIds = Order::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->pluck('id');

        $schedules = Schedule::whereIn('order_id', $orderIds)
            ->with(['district', 'area', 'brigadier'])
            ->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Schedules');

        $headers = [
            'id', 'installation_date', 'address_code', 'manual', 'source', 'order_id',
            'district_id', 'district_shortname', 'area_id', 'area_name', 'object_address',
            'object_type', 'mafs', 'mafs_count', 'brigadier_id', 'brigadier_name',
            'comment', 'created_at', 'updated_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        foreach ($schedules as $schedule) {
            $this->writeRow($sheet, $row, [
                $schedule->id,
                $schedule->installation_date,
                $schedule->address_code,
                $schedule->manual,
                $schedule->source,
                $schedule->order_id,
                $schedule->district_id,
                $schedule->district?->shortname,
                $schedule->area_id,
                $schedule->area?->name,
                $schedule->object_address,
                $schedule->object_type,
                $schedule->mafs,
                $schedule->mafs_count,
                $schedule->brigadier_id,
                $schedule->brigadier?->name,
                $schedule->comment,
                $schedule->created_at?->toIso8601String(),
                $schedule->updated_at?->toIso8601String(),
            ]);
            $row++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/schedules.xlsx');
        $this->stats['schedules'] = $schedules->count();
    }

    private function exportContracts(): void
    {
        $contracts = Contract::where('year', $this->year)->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Contracts');

        $headers = ['id', 'contract_number', 'contract_date', 'created_at', 'updated_at'];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        foreach ($contracts as $contract) {
            $this->writeRow($sheet, $row, [
                $contract->id,
                $contract->contract_number,
                $contract->contract_date,
                $contract->created_at?->toIso8601String(),
                $contract->updated_at?->toIso8601String(),
            ]);
            $row++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/contracts.xlsx');
        $this->stats['contracts'] = $contracts->count();
    }

    private function exportTtn(): void
    {
        $ttns = Ttn::where('year', $this->year)->with('file')->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Ttn');

        $headers = [
            'id', 'ttn_number', 'ttn_number_suffix', 'order_number', 'order_date',
            'order_sum', 'skus', 'file_path', 'created_at', 'updated_at'
        ];

        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        foreach ($ttns as $ttn) {
            $filePath = '';
            if ($ttn->file_id && $ttn->file) {
                $filePath = $this->mapFileToArchive($ttn->file, 'ttn');
            }

            $this->writeRow($sheet, $row, [
                $ttn->id,
                $ttn->ttn_number,
                $ttn->ttn_number_suffix,
                $ttn->order_number,
                $ttn->order_date,
                $ttn->order_sum,
                $ttn->skus,
                $filePath,
                $ttn->created_at?->toIso8601String(),
                $ttn->updated_at?->toIso8601String(),
            ]);
            $row++;
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/ttn.xlsx');
        $this->stats['ttn'] = $ttns->count();
    }

    private function copyFilesAndExportPivots(): void
    {
        $orderIds = Order::withoutGlobalScopes()
            ->withTrashed()
            ->where('year', $this->year)
            ->pluck('id');

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

        $spreadsheet = new Spreadsheet();
        $hasSheets = false;

        // Order photos
        if ($this->exportPivotSheet($spreadsheet, 'order_photo', 'order_id', $orderIds, 'orders', 'photos', !$hasSheets)) {
            $hasSheets = true;
        }

        // Order documents
        if ($this->exportPivotSheet($spreadsheet, 'order_document', 'order_id', $orderIds, 'orders', 'documents', !$hasSheets)) {
            $hasSheets = true;
        }

        // Order statements
        if ($this->exportPivotSheet($spreadsheet, 'order_statement', 'order_id', $orderIds, 'orders', 'statements', !$hasSheets)) {
            $hasSheets = true;
        }

        // Reclamation photos before
        if ($this->exportPivotSheet($spreadsheet, 'reclamation_photo_before', 'reclamation_id', $reclamationIds, 'reclamations', 'photos_before', !$hasSheets)) {
            $hasSheets = true;
        }

        // Reclamation photos after
        if ($this->exportPivotSheet($spreadsheet, 'reclamation_photo_after', 'reclamation_id', $reclamationIds, 'reclamations', 'photos_after', !$hasSheets)) {
            $hasSheets = true;
        }

        // Reclamation documents
        if ($this->exportPivotSheet($spreadsheet, 'reclamation_document', 'reclamation_id', $reclamationIds, 'reclamations', 'documents', !$hasSheets)) {
            $hasSheets = true;
        }

        // Reclamation acts
        if ($this->exportPivotSheet($spreadsheet, 'reclamation_act', 'reclamation_id', $reclamationIds, 'reclamations', 'acts', !$hasSheets)) {
            $hasSheets = true;
        }

        // Если ничего не экспортировано, создаём пустой лист
        if (!$hasSheets) {
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('empty');
            $sheet->setCellValue('A1', 'No pivot data');
        }

        $writer = new Xlsx($spreadsheet);
        $writer->save($this->dataDir . '/pivot_tables.xlsx');

        // Копируем все файлы из fileMapping
        $this->copyMappedFiles();
    }

    private function exportPivotSheet(
        Spreadsheet $spreadsheet,
        string $tableName,
        string $foreignKey,
        Collection $ids,
        string $entityFolder,
        string $fileSubfolder,
        bool $isFirstSheet
    ): bool {
        $records = DB::table($tableName)
            ->whereIn($foreignKey, $ids)
            ->get();

        if ($records->isEmpty()) {
            return false;
        }

        if ($isFirstSheet) {
            $sheet = $spreadsheet->getActiveSheet();
        } else {
            $sheet = $spreadsheet->createSheet();
        }
        $sheet->setTitle($tableName);

        $headers = [$foreignKey, 'file_id', 'file_archive_path'];
        $this->writeRow($sheet, 1, $headers);

        $row = 2;
        foreach ($records as $record) {
            $file = File::find($record->file_id);
            $archivePath = '';

            if ($file) {
                $entityId = $record->$foreignKey;
                $archivePath = $this->mapFileToArchive(
                    $file,
                    "{$entityFolder}/{$entityId}/{$fileSubfolder}"
                );
            }

            $this->writeRow($sheet, $row, [
                $record->$foreignKey,
                $record->file_id,
                $archivePath,
            ]);
            $row++;
        }

        return true;
    }

    private function mapFileToArchive(File $file, string $subPath): string
    {
        if (isset($this->fileMapping[$file->id])) {
            return $this->fileMapping[$file->id]['archive_path'];
        }

        $extension = pathinfo($file->original_name ?? '', PATHINFO_EXTENSION);
        $safeName = Str::slug(pathinfo($file->original_name ?? 'file', PATHINFO_FILENAME));
        if (empty($safeName)) {
            $safeName = 'file';
        }
        $archivePath = "files/{$subPath}/{$file->id}_{$safeName}.{$extension}";

        $this->fileMapping[$file->id] = [
            'archive_path' => $archivePath,
            'storage_path' => $file->path,
            'original_name' => $file->original_name,
            'mime_type' => $file->mime_type,
        ];

        return $archivePath;
    }

    private function copyMappedFiles(): void
    {
        $filesCount = 0;

        foreach ($this->fileMapping as $fileId => $fileData) {
            $storagePath = $fileData['storage_path'];
            $archivePath = $fileData['archive_path'];

            if ($storagePath && Storage::disk('public')->exists($storagePath)) {
                $targetPath = $this->tempDir . '/' . $archivePath;
                $targetDir = dirname($targetPath);

                if (!is_dir($targetDir)) {
                    mkdir($targetDir, 0755, true);
                }

                $content = Storage::disk('public')->get($storagePath);
                file_put_contents($targetPath, $content);
                $filesCount++;
            }
        }

        $this->stats['files'] = $filesCount;
    }

    private function createManifest(): void
    {
        $manifest = [
            'version' => '1.0',
            'exported_at' => now()->toIso8601String(),
            'year' => $this->year,
            'exported_by_user_id' => $this->userId,
            'stats' => $this->stats,
        ];

        file_put_contents(
            $this->tempDir . '/manifest.json',
            json_encode($manifest, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE)
        );
    }

    private function createArchive(): string
    {
        $archiveName = "year_data_{$this->year}_" . date('Y-m-d_His') . '.zip';
        $archivePath = storage_path('app/public/export/' . $archiveName);

        // Создаем директорию если не существует
        $exportDir = storage_path('app/public/export');
        if (!is_dir($exportDir)) {
            mkdir($exportDir, 0755, true);
        }

        $zip = new ZipArchive();
        if ($zip->open($archivePath, ZipArchive::CREATE | ZipArchive::OVERWRITE) !== true) {
            throw new Exception("Не удалось создать ZIP архив");
        }

        $files = new RecursiveIteratorIterator(
            new RecursiveDirectoryIterator($this->tempDir),
            RecursiveIteratorIterator::LEAVES_ONLY
        );

        foreach ($files as $file) {
            if ($file->isDir()) {
                continue;
            }

            $filePath = $file->getRealPath();
            $relativePath = substr($filePath, strlen($this->tempDir) + 1);
            $zip->addFile($filePath, $relativePath);
        }

        $zip->close();

        // Создаем запись File в БД
        File::create([
            'user_id' => $this->userId,
            'original_name' => $archiveName,
            'mime_type' => 'application/zip',
            'path' => 'export/' . $archiveName,
            'link' => url('/storage/export/' . $archiveName),
        ]);

        // Возвращаем только имя файла (фронтенд добавляет /storage/export/ сам)
        return $archiveName;
    }

    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);
    }

    public function getStats(): array
    {
        return $this->stats;
    }
}