<?php

namespace App\Http\Controllers;

use App\Models\Bien;
use App\Models\DevolucionPrestamo;
use App\Models\DevolucionTransferencia;
use App\Models\Dotacion;
use App\Models\Operacion;
use App\Models\Prestamo;
use App\Models\Transferencia;
use App\Models\Usuario;
use App\Services\AsignacionOperacionUtilityService;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Route;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\BaseDrawing;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Symfony\Component\HttpFoundation\StreamedResponse;

class AsignacionOperacionController extends Controller
{
    private const BARCODE_START_COLUMN = 9;

    private const BARCODE_COLUMN_SPAN = 3;

    private const BARCODE_IMAGE_WIDTH = 150;

    private const BARCODE_IMAGE_HEIGHT = 100;

    private const BARCODE_OFFSET_X = 4;

    private const BARCODE_OFFSET_Y = 6;

    public function __construct(private readonly AsignacionOperacionUtilityService $utilityService)
    {
    }

    private const OP_TYPE_LABELS = [
        'alimentos' => 'Asignacion',
        'transferencia' => 'Transferencia',
        'dotacion' => 'Dotacion',
        'devolucion-prestamo' => 'Devolucion de prestamos',
        'devolucion-transferencia' => 'Devolucion de transferencias',
    ];

    private const BIEN_TYPE_LABELS = [
        1 => 'Equipo',
        2 => 'Herramienta',
        3 => 'Dotacion',
        4 => 'Consumible',
        5 => 'Repuesto',
        6 => 'Material oficina',
        7 => 'Material medico',
    ];

    public function show(string $tipo, int $numero): Response
    {
        $data = $this->buildOperationData($tipo, $numero);

        abort_unless($data !== null, 404);

        $barcodeValue = $this->utilityService->buildBarcodeValue($numero);

        return response()->view('asignacion.operacion', [
            'tipo' => $tipo,
            'numero' => $numero,
            'title' => $data['title'],
            'meta' => $data['meta'],
            'columns' => $data['columns'],
            'rows' => $data['rows'],
            'barcodeValue' => $barcodeValue,
            'barcodeSvg' => $this->utilityService->buildBarcodeSvg($barcodeValue),
            'exportUrl' => route('asignacion.operacion.export', ['tipo' => $tipo, 'numero' => $numero]),
            'backUrl' => route('administracion.crud.index', ['entity' => 'operaciones']),
        ]);
    }

    public function showByOperationId(int $id): Response
    {
        $operation = Operacion::query()->findOrFail($id);
        $tipo = $this->utilityService->resolveTipoRoute((int) ($operation->tipo ?? 0));

        abort_unless($tipo !== null, 404);

        $numero = (int) ($operation->numero ?? 0);
        $data = $this->buildOperationData($tipo, $numero);

        abort_unless($data !== null, 404);

        $barcodeValue = $this->utilityService->buildBarcodeValue($numero);

        return response()->view('asignacion.operacion', [
            'tipo' => $tipo,
            'numero' => $numero,
            'title' => $data['title'],
            'meta' => $data['meta'],
            'columns' => $data['columns'],
            'rows' => $data['rows'],
            'barcodeValue' => $barcodeValue,
            'barcodeSvg' => $this->utilityService->buildBarcodeSvg($barcodeValue),
            'exportUrl' => route('asignacion.operacion.export', ['tipo' => $tipo, 'numero' => $numero]),
            'backUrl' => route('administracion.crud.index', ['entity' => 'operaciones']),
        ]);
    }

    public function showByOperationNumber(string $numero): Response|RedirectResponse
    {
        $candidateNumbers = $this->scannedOperationCandidates($numero);

        if ($candidateNumbers === []) {
            return redirect()
                ->route('administracion.crud.index', ['entity' => 'operaciones', 'q' => $numero])
                ->with('warning', 'No se pudo interpretar el codigo escaneado.');
        }

        $operation = null;
        foreach ($candidateNumbers as $candidateNumber) {
            $operation = Operacion::query()
                ->where('numero', $candidateNumber)
                ->orderByDesc('id')
                ->first();

            if ($operation !== null) {
                break;
            }
        }

        if (! $operation) {
            return redirect()
                ->route('administracion.crud.index', ['entity' => 'operaciones', 'q' => $numero])
                ->with('warning', 'No se encontro una operacion para el codigo escaneado.');
        }

        $tipo = $this->utilityService->resolveTipoRoute((int) ($operation->tipo ?? 0));

        if ($tipo === null) {
            return redirect()
                ->route('administracion.crud.index', ['entity' => 'operaciones', 'q' => (int) ($operation->numero ?? 0)])
                ->with('warning', 'La operacion encontrada no tiene comprobante disponible.');
        }

        return $this->showByOperationId((int) $operation->id);
    }

    public function barcode(string $tipo, int $numero): Response
    {
        $data = $this->buildOperationData($tipo, $numero);

        abort_unless($data !== null, 404);

        $barcodeBinary = $this->utilityService->buildBarcodeBinary($this->utilityService->buildBarcodeValue($numero));

        abort_unless($barcodeBinary !== null, 404);

        return response($barcodeBinary, 200, [
            'Content-Type' => 'image/png',
            'Cache-Control' => 'no-store, max-age=0',
        ]);
    }

    public function barcodeByOperationId(int $id): Response
    {
        $operation = Operacion::query()->findOrFail($id);
        $tipo = $this->utilityService->resolveTipoRoute((int) ($operation->tipo ?? 0));

        abort_unless($tipo !== null, 404);

        return $this->barcode($tipo, (int) ($operation->numero ?? 0));
    }

    public function export(string $tipo, int $numero): StreamedResponse
    {
        $data = $this->buildOperationData($tipo, $numero);

        abort_unless($data !== null, 404);

        $filename = sprintf('Comprobante_%s_%d.xlsx', ucfirst($tipo), $numero);
        $barcodeValue = $this->utilityService->buildBarcodeValue($numero);

        return response()->streamDownload(function () use ($barcodeValue, $data): void {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $temporaryFiles = [];
            $sheet->setTitle('Comprobante');
            $sheet->getPageSetup()
                ->setPaperSize(PageSetup::PAPERSIZE_LETTER)
                ->setOrientation(PageSetup::ORIENTATION_PORTRAIT)
                ->setFitToWidth(1)
                ->setFitToHeight(0);
            $sheet->getPageMargins()->setTop(0.35)->setBottom(0.35)->setLeft(0.3)->setRight(0.3);

            $barcodeStartColumn = self::BARCODE_START_COLUMN;
            $barcodeStartLetter = Coordinate::stringFromColumnIndex($barcodeStartColumn);
            $barcodeEndLetter = Coordinate::stringFromColumnIndex($barcodeStartColumn + self::BARCODE_COLUMN_SPAN);
            $titleEndColumn = max(5, $barcodeStartColumn - 2);
            $titleEndLetter = Coordinate::stringFromColumnIndex($titleEndColumn);

            $logoDrawing = $this->utilityService->buildLogoDrawing();
            if ($logoDrawing !== null) {
                $logoDrawing->setName('Logo Avante');
                $logoDrawing->setDescription('Logo corporativo Avante');
                $logoDrawing->setCoordinates('A3');
                $logoDrawing->setResizeProportional(true);
                $logoDrawing->setHeight(68);
                $logoDrawing->setOffsetX(6);
                $logoDrawing->setOffsetY(6);
                $logoDrawing->setWorksheet($sheet);
            }

            $sheet->setCellValue('B1', $data['title']);
            $sheet->mergeCells('B1:' . $titleEndLetter . '2');
            $sheet->setCellValue($barcodeStartLetter . '1', 'Codigo de barras');
            $this->writeSpreadsheetValue($sheet, $barcodeStartLetter . '2', $barcodeValue, 'codigo de barras');

            $sheet->getStyle('B1:' . $titleEndLetter . '2')->getFont()->setBold(true)->setSize(14);
            $sheet->getStyle('B1:' . $titleEndLetter . '2')->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_LEFT)
                ->setVertical(Alignment::VERTICAL_CENTER)
                ->setWrapText(true);
            $sheet->getStyle($barcodeStartLetter . '1:' . $barcodeStartLetter . '2')->getFont()->setBold(true);
            $sheet->getStyle($barcodeStartLetter . '1:' . $barcodeEndLetter . '2')->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                ->setVertical(Alignment::VERTICAL_CENTER);

            $sheet->getStyle('A1:' . $barcodeEndLetter . '2')->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB('F4F8FC');

            $this->attachBarcodeDrawing(
                $sheet,
                $barcodeValue,
                'Codigo 128 de la operacion',
                $barcodeStartLetter . '3',
                $temporaryFiles
            );

            foreach (range(3, 7) as $barcodeRow) {
                $sheet->getRowDimension($barcodeRow)->setRowHeight(18);
            }

            $rowIndex = 6;
            $metaEntries = array_values($data['meta']);
            $metaLabels = array_keys($data['meta']);
            $metaCount = count($metaLabels);

            for ($metaIndex = 0; $metaIndex < $metaCount; $metaIndex += 2) {
                $sheet->setCellValue('A' . $rowIndex, (string) $metaLabels[$metaIndex]);
                $this->writeSpreadsheetValue($sheet, 'B' . $rowIndex, $metaEntries[$metaIndex], (string) $metaLabels[$metaIndex]);
                $sheet->mergeCells('B' . $rowIndex . ':C' . $rowIndex);

                if (array_key_exists($metaIndex + 1, $metaLabels)) {
                    $sheet->setCellValue('D' . $rowIndex, (string) $metaLabels[$metaIndex + 1]);
                    $this->writeSpreadsheetValue($sheet, 'E' . $rowIndex, $metaEntries[$metaIndex + 1], (string) $metaLabels[$metaIndex + 1]);
                    $sheet->mergeCells('E' . $rowIndex . ':F' . $rowIndex);
                }

                $rowIndex++;
            }

            $sheet->getStyle('A6:A' . ($rowIndex - 1))->getFont()->setBold(true);
            $sheet->getStyle('D6:D' . ($rowIndex - 1))->getFont()->setBold(true);
            $sheet->getStyle('B6:C' . ($rowIndex - 1))->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_LEFT)
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);
            $sheet->getStyle('E6:F' . ($rowIndex - 1))->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_LEFT)
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);

            $rowIndex++;

            if ($rowIndex < 12) {
                $rowIndex = 12;
            }

            $columnKeys = array_keys($data['columns']);
            $columnLabels = array_values($data['columns']);

            $columnNumber = 1;
            foreach ($columnLabels as $label) {
                $sheet->setCellValue(Coordinate::stringFromColumnIndex($columnNumber) . $rowIndex, $label);
                $columnNumber++;
            }

            $highestColumn = Coordinate::stringFromColumnIndex(count($columnLabels));

            $sheet->getStyle('A' . $rowIndex . ':' . $highestColumn . $rowIndex)
                ->getFont()
                ->setBold(true);
            $sheet->getStyle('A' . $rowIndex . ':' . $highestColumn . $rowIndex)->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB('EAF2FA');

            $rowIndex++;
            $firstBodyRow = $rowIndex;

            foreach ($data['rows'] as $row) {
                $columnNumber = 1;

                foreach ($columnKeys as $key) {
                    $this->writeSpreadsheetValue(
                        $sheet,
                        Coordinate::stringFromColumnIndex($columnNumber) . $rowIndex,
                        $row[$key] ?? '',
                        $key
                    );
                    $columnNumber++;
                }

                $rowIndex++;
            }

            $lastBodyRow = $rowIndex - 1;

            if ($lastBodyRow >= $firstBodyRow) {
                $sheet->getStyle('A' . ($firstBodyRow - 1) . ':' . $highestColumn . $lastBodyRow)
                    ->getBorders()
                    ->getAllBorders()
                    ->setBorderStyle(Border::BORDER_THIN)
                    ->getColor()
                    ->setRGB('D5E0EA');
            }

            for ($columnNumber = 1; $columnNumber <= count($columnLabels); $columnNumber++) {
                $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($columnNumber))->setAutoSize(true);
            }

            $sheet->getColumnDimension('A')->setWidth(16);
            $sheet->getColumnDimension('B')->setWidth(8);
            $sheet->getColumnDimension('C')->setWidth(10);
            $sheet->getColumnDimension('D')->setWidth(12);
            $sheet->getColumnDimension('E')->setWidth(10);
            $sheet->getColumnDimension('F')->setWidth(10);
            $sheet->getColumnDimension($barcodeStartLetter)->setWidth(22);
            $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($barcodeStartColumn + 1))->setWidth(18);

            foreach ($columnKeys as $index => $key) {
                $columnLetter = Coordinate::stringFromColumnIndex($index + 1);
                $normalizedKey = strtolower($key);

                if ($normalizedKey === 'n' || $normalizedKey === '#') {
                    $sheet->getColumnDimension($columnLetter)->setWidth(5);
                } elseif (str_contains($normalizedKey, 'sap')) {
                    $sheet->getColumnDimension($columnLetter)->setWidth(8);
                } elseif (str_contains($normalizedKey, 'descripcion')) {
                    $sheet->getColumnDimension($columnLetter)->setWidth(34);
                } elseif (str_contains($normalizedKey, 'observacion')) {
                    $sheet->getColumnDimension($columnLetter)->setWidth(26);
                } elseif (str_contains($normalizedKey, 'codigo')) {
                    $sheet->getColumnDimension($columnLetter)->setWidth(10);
                } elseif (str_contains($normalizedKey, 'cantidad') || str_contains($normalizedKey, 'cant')) {
                    $sheet->getColumnDimension($columnLetter)->setWidth(10);
                }
            }

            $sheet->getStyle('A' . ($firstBodyRow - 1) . ':' . $highestColumn . $lastBodyRow)
                ->getAlignment()
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);

            $sheet->getPageSetup()->setPrintArea('A1:' . $highestColumn . $lastBodyRow);
            $sheet->freezePane('A' . $firstBodyRow);

            try {
                $writer = new Xlsx($spreadsheet);
                $writer->save('php://output');
            } finally {
                $spreadsheet->disconnectWorksheets();

                foreach ($temporaryFiles as $temporaryFile) {
                    if (is_string($temporaryFile) && $temporaryFile !== '' && file_exists($temporaryFile)) {
                        @unlink($temporaryFile);
                    }
                }
            }
        }, $filename, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    public function showDevolucion(): Response
    {
        $data = $this->buildDevolucionResultData();

        abort_unless($data !== null, 404);

        $barcodeValue = $this->utilityService->buildBarcodeValue((int) $data['numeroComprobante']);

        return response()->view('asignacion.devolucion-operacion', [
            'title' => $data['title'],
            'meta' => $data['meta'],
            'sections' => $data['sections'],
            'barcodeValue' => $barcodeValue,
            'barcodeSvg' => $this->utilityService->buildBarcodeSvg($barcodeValue),
            'exportUrl' => route('asignacion.devolucion.comprobante.export'),
            'backUrl' => route('administracion.crud.index', ['entity' => 'operaciones']),
        ]);
    }

    public function exportDevolucion(): StreamedResponse
    {
        $data = $this->buildDevolucionResultData();

        abort_unless($data !== null, 404);

        $barcodeValue = $this->utilityService->buildBarcodeValue((int) $data['numeroComprobante']);

        return response()->streamDownload(function () use ($barcodeValue, $data): void {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $temporaryFiles = [];
            $sheet->setTitle('Comprobante');
            $sheet->getPageSetup()
                ->setPaperSize(PageSetup::PAPERSIZE_LETTER)
                ->setOrientation(PageSetup::ORIENTATION_PORTRAIT)
                ->setFitToWidth(1)
                ->setFitToHeight(0);
            $sheet->getPageMargins()->setTop(0.35)->setBottom(0.35)->setLeft(0.3)->setRight(0.3);

            $barcodeStartColumn = self::BARCODE_START_COLUMN;
            $barcodeStartLetter = Coordinate::stringFromColumnIndex($barcodeStartColumn);
            $barcodeEndLetter = Coordinate::stringFromColumnIndex($barcodeStartColumn + self::BARCODE_COLUMN_SPAN);
            $titleEndColumn = max(5, $barcodeStartColumn - 2);
            $titleEndLetter = Coordinate::stringFromColumnIndex($titleEndColumn);

            $logoDrawing = $this->utilityService->buildLogoDrawing();
            if ($logoDrawing !== null) {
                $logoDrawing->setName('Logo Avante');
                $logoDrawing->setDescription('Logo corporativo Avante');
                $logoDrawing->setCoordinates('A1');
                $logoDrawing->setResizeProportional(true);
                $logoDrawing->setHeight(68);
                $logoDrawing->setOffsetX(6);
                $logoDrawing->setOffsetY(6);
                $logoDrawing->setWorksheet($sheet);
            }

            $sheet->setCellValue('B1', $data['title']);
            $sheet->mergeCells('B1:' . $titleEndLetter . '2');
            $sheet->setCellValue($barcodeStartLetter . '1', 'Codigo de barras');
            $this->writeSpreadsheetValue($sheet, $barcodeStartLetter . '2', $barcodeValue, 'codigo de barras');

            $sheet->getStyle('B1:' . $titleEndLetter . '2')->getFont()->setBold(true)->setSize(14);
            $sheet->getStyle('B1:' . $titleEndLetter . '2')->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_LEFT)
                ->setVertical(Alignment::VERTICAL_CENTER)
                ->setWrapText(true);
            $sheet->getStyle($barcodeStartLetter . '1:' . $barcodeStartLetter . '2')->getFont()->setBold(true);
            $sheet->getStyle($barcodeStartLetter . '1:' . $barcodeEndLetter . '2')->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER)
                ->setVertical(Alignment::VERTICAL_CENTER);

            $sheet->getStyle('A1:' . $barcodeEndLetter . '2')->getFill()
                ->setFillType(Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB('F4F8FC');

            $rowIndex = 6;

            $this->attachBarcodeDrawing(
                $sheet,
                $barcodeValue,
                'Codigo 128 del comprobante de devolucion',
                $barcodeStartLetter . '3',
                $temporaryFiles
            );

            foreach (range(3, 7) as $barcodeRow) {
                $sheet->getRowDimension($barcodeRow)->setRowHeight(18);
            }

            $metaEntries = array_values($data['meta']);
            $metaLabels = array_keys($data['meta']);
            $metaCount = count($metaLabels);

            for ($metaIndex = 0; $metaIndex < $metaCount; $metaIndex += 2) {
                $sheet->setCellValue('A' . $rowIndex, (string) $metaLabels[$metaIndex]);
                $this->writeSpreadsheetValue($sheet, 'B' . $rowIndex, $metaEntries[$metaIndex], (string) $metaLabels[$metaIndex]);
                $sheet->mergeCells('B' . $rowIndex . ':C' . $rowIndex);

                if (array_key_exists($metaIndex + 1, $metaLabels)) {
                    $sheet->setCellValue('D' . $rowIndex, (string) $metaLabels[$metaIndex + 1]);
                    $this->writeSpreadsheetValue($sheet, 'E' . $rowIndex, $metaEntries[$metaIndex + 1], (string) $metaLabels[$metaIndex + 1]);
                    $sheet->mergeCells('E' . $rowIndex . ':F' . $rowIndex);
                }

                $rowIndex++;
            }

            $sheet->getStyle('A6:A' . ($rowIndex - 1))->getFont()->setBold(true);
            $sheet->getStyle('D6:D' . ($rowIndex - 1))->getFont()->setBold(true);
            $sheet->getStyle('B6:C' . ($rowIndex - 1))->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_LEFT)
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);
            $sheet->getStyle('E6:F' . ($rowIndex - 1))->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_LEFT)
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);

            $rowIndex++;

            foreach ($data['sections'] as $section) {
                $sheet->setCellValue('A' . $rowIndex, $section['title']);
                $sheet->getStyle('A' . $rowIndex)->getFont()->setBold(true);
                $rowIndex++;

                $columnKeys = array_keys($section['columns']);
                $columnLabels = array_values($section['columns']);
                $columnNumber = 1;

                foreach ($columnLabels as $label) {
                    $sheet->setCellValue(Coordinate::stringFromColumnIndex($columnNumber) . $rowIndex, $label);
                    $columnNumber++;
                }

                $highestColumn = Coordinate::stringFromColumnIndex(count($columnLabels));
                $sheet->getStyle('A' . $rowIndex . ':' . $highestColumn . $rowIndex)->getFont()->setBold(true);
                $sheet->getStyle('A' . $rowIndex . ':' . $highestColumn . $rowIndex)->getFill()
                    ->setFillType(Fill::FILL_SOLID)
                    ->getStartColor()
                    ->setRGB('EAF2FA');

                foreach ($columnKeys as $index => $key) {
                    $columnLetter = Coordinate::stringFromColumnIndex($index + 1);
                    $normalizedKey = strtolower($key);

                    if ($normalizedKey === 'n' || $normalizedKey === '#') {
                        $sheet->getColumnDimension($columnLetter)->setWidth(5);
                    } elseif (str_contains($normalizedKey, 'sap')) {
                        $sheet->getColumnDimension($columnLetter)->setWidth(8);
                    } elseif (str_contains($normalizedKey, 'descripcion')) {
                        $sheet->getColumnDimension($columnLetter)->setWidth(34);
                    } elseif (str_contains($normalizedKey, 'observacion')) {
                        $sheet->getColumnDimension($columnLetter)->setWidth(26);
                    } elseif (str_contains($normalizedKey, 'codigo')) {
                        $sheet->getColumnDimension($columnLetter)->setWidth(10);
                    } elseif (str_contains($normalizedKey, 'cantidad') || str_contains($normalizedKey, 'cant')) {
                        $sheet->getColumnDimension($columnLetter)->setWidth(10);
                    }
                }

                $rowIndex++;
                $firstBodyRow = $rowIndex;

                foreach ($section['rows'] as $row) {
                    $columnNumber = 1;

                    foreach ($columnKeys as $key) {
                        $this->writeSpreadsheetValue(
                            $sheet,
                            Coordinate::stringFromColumnIndex($columnNumber) . $rowIndex,
                            $row[$key] ?? '',
                            $key
                        );
                        $columnNumber++;
                    }

                    $rowIndex++;
                }

                $lastBodyRow = $rowIndex - 1;

                if ($lastBodyRow >= $firstBodyRow) {
                    $sheet->getStyle('A' . ($firstBodyRow - 1) . ':' . $highestColumn . $lastBodyRow)
                        ->getBorders()
                        ->getAllBorders()
                        ->setBorderStyle(Border::BORDER_THIN)
                        ->getColor()
                        ->setRGB('D5E0EA');
                }

                $rowIndex++;
            }

            for ($columnNumber = 1; $columnNumber <= 9; $columnNumber++) {
                $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($columnNumber))->setAutoSize(true);
            }

            $sheet->getColumnDimension('A')->setWidth(16);
            $sheet->getColumnDimension('B')->setWidth(8);
            $sheet->getColumnDimension('C')->setWidth(10);
            $sheet->getColumnDimension('D')->setWidth(12);
            $sheet->getColumnDimension('E')->setWidth(10);
            $sheet->getColumnDimension('F')->setWidth(10);
            $sheet->getColumnDimension($barcodeStartLetter)->setWidth(22);
            $sheet->getColumnDimension(Coordinate::stringFromColumnIndex($barcodeStartColumn + 1))->setWidth(18);

            $lastUsedColumn = $sheet->getHighestColumn();
            $lastUsedRow = $sheet->getHighestRow();
            $sheet->getStyle('A12:' . $lastUsedColumn . $lastUsedRow)
                ->getAlignment()
                ->setVertical(Alignment::VERTICAL_TOP)
                ->setWrapText(true);
            $sheet->getPageSetup()->setPrintArea('A1:' . $lastUsedColumn . $lastUsedRow);

            try {
                $writer = new Xlsx($spreadsheet);
                $writer->save('php://output');
            } finally {
                $spreadsheet->disconnectWorksheets();

                foreach ($temporaryFiles as $temporaryFile) {
                    if (is_string($temporaryFile) && $temporaryFile !== '' && file_exists($temporaryFile)) {
                        @unlink($temporaryFile);
                    }
                }
            }
        }, 'Comprobante_Devolucion.xlsx', [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        ]);
    }

    private function buildOperationData(string $tipo, int $numero): ?array
    {
        if (! array_key_exists($tipo, self::OP_TYPE_LABELS)) {
            return null;
        }

        if ($tipo === 'alimentos') {
            return $this->buildAlimentosData($numero);
        }

        if ($tipo === 'transferencia') {
            return $this->buildTransferenciaData($numero);
        }

        if ($tipo === 'dotacion') {
            return $this->buildDotacionData($numero);
        }

        if ($tipo === 'devolucion-prestamo') {
            return $this->buildDevolucionPrestamoData($numero);
        }

        if ($tipo === 'devolucion-transferencia') {
            return $this->buildDevolucionTransferenciaData($numero);
        }

        return null;
    }

    /**
     * @param array<int, string> $temporaryFiles
     */
    private function attachBarcodeDrawing(
        Worksheet $sheet,
        string $barcodeValue,
        string $description,
        string $coordinates,
        array &$temporaryFiles,
    ): void {
        $barcodeDrawing = $this->utilityService->buildBarcodeDrawing($barcodeValue, $temporaryFiles);

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

        $barcodeDrawing->setName('Codigo de barras');
        $barcodeDrawing->setDescription($description);
        $barcodeDrawing->setCoordinates($coordinates);
        $barcodeDrawing->setResizeProportional(true);
        $barcodeDrawing->setWidthAndHeight(self::BARCODE_IMAGE_WIDTH, self::BARCODE_IMAGE_HEIGHT);
        $barcodeDrawing->setOffsetX(self::BARCODE_OFFSET_X);
        $barcodeDrawing->setOffsetY(self::BARCODE_OFFSET_Y);
        $barcodeDrawing->setWorksheet($sheet);
    }

    private function writeSpreadsheetValue(Worksheet $sheet, string $cell, mixed $value, ?string $fieldKey = null): void
    {
        if ($this->shouldWriteAsText($fieldKey)) {
            $sheet->setCellValueExplicit($cell, (string) $value, DataType::TYPE_STRING);
            $sheet->getStyle($cell)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_TEXT);

            return;
        }

        $sheet->setCellValue($cell, (string) $value);
    }

    private function shouldWriteAsText(?string $fieldKey): bool
    {
        $normalized = strtolower(trim((string) $fieldKey));

        foreach (['codigo', 'sap', 'cedula', 'comprobante', 'barra'] as $keyword) {
            if ($normalized !== '' && str_contains($normalized, $keyword)) {
                return true;
            }
        }

        return false;
    }

    /**
     * @return array<int, int>
     */
    private function scannedOperationCandidates(string $rawValue): array
    {
        $digits = preg_replace('/\D+/', '', $rawValue);
        if (! is_string($digits) || $digits === '') {
            return [];
        }

        $parsed = (int) $digits;
        if ($parsed <= 0) {
            return [];
        }

        $candidates = [$parsed];
        if ($parsed >= 2000000) {
            $decoded = $parsed - 2000000;
            if ($decoded > 0) {
                $candidates[] = $decoded;
            }
        }

        return array_values(array_unique($candidates));
    }

    private function buildDevolucionResultData(): ?array
    {
        $resultado = session('devolucion_resultado', session('asignacion.devolucion_resultado'));

        if (! is_array($resultado) || $resultado === []) {
            return null;
        }

        $sections = [];

        if (! empty($resultado['prestamos']) && is_array($resultado['prestamos'])) {
            $sections[] = [
                'title' => 'Prestamos devueltos',
                'columns' => [
                    'codigo' => 'Codigo',
                    'descripcion' => 'Descripcion',
                    'cantidad' => 'Cant.',
                    'tipo' => 'Tipo',
                    'ubicacion' => 'Ubicacion',
                    'stock_antes' => 'Stock antes',
                    'stock_despues' => 'Stock despues',
                ],
                'rows' => $resultado['prestamos'],
            ];
        }

        if (! empty($resultado['transferencias']) && is_array($resultado['transferencias'])) {
            $sections[] = [
                'title' => 'Transferencias devueltas',
                'columns' => [
                    'codigo' => 'Codigo',
                    'descripcion' => 'Descripcion',
                    'cantidad' => 'Cant.',
                    'tipo' => 'Tipo',
                    'emisor' => 'Emisor',
                    'receptor' => 'Receptor',
                    'ubicacion' => 'Ubicacion',
                    'stock_antes' => 'Stock antes',
                    'stock_despues' => 'Stock despues',
                ],
                'rows' => $resultado['transferencias'],
            ];
        }

        if (! empty($resultado['utilizados_items']) && is_array($resultado['utilizados_items'])) {
            $sections[] = [
                'title' => 'Transferencias catalogadas como utilizadas',
                'columns' => [
                    'codigo' => 'Codigo',
                    'descripcion' => 'Descripcion',
                    'cantidad' => 'Cant.',
                    'tipo' => 'Tipo',
                    'ubicacion' => 'Ubicacion',
                ],
                'rows' => $resultado['utilizados_items'],
            ];
        }

        $numeroComprobante = $this->resolveDevolucionComprobanteNumero($resultado);

        return [
            'title' => 'Comprobante de Devolucion',
            'numeroComprobante' => $numeroComprobante,
            'meta' => [
                'Comprobante Nro' => $numeroComprobante,
                'Solicitante' => (string) (session('prestamista') ?? ''),
                'Devueltos' => (int) ($resultado['devueltos'] ?? 0),
                'Utilizados' => (int) ($resultado['utilizados'] ?? 0),
                'Omitidos' => (int) ($resultado['omitidos'] ?? 0),
            ],
            'sections' => $sections,
        ];
    }

    private function resolveDevolucionComprobanteNumero(array $resultado): int
    {
        $numero = (int) ($resultado['numero_comprobante'] ?? 0);

        if ($numero > 0) {
            return $numero;
        }

        $hashSource = json_encode([
            'prestamos' => $resultado['prestamos'] ?? [],
            'transferencias' => $resultado['transferencias'] ?? [],
            'utilizados_items' => $resultado['utilizados_items'] ?? [],
        ]);

        return (int) (abs(crc32((string) $hashSource)) % 900000) + 100000;
    }

    private function resolveBackUrl(string $tipo): string
    {
        return match ($tipo) {
            'alimentos' => route('asignacion.alimentos'),
            'transferencia' => route('asignacion.transferencia'),
            'dotacion' => route('asignacion.dotacion'),
            'devolucion-prestamo' => route('asignacion.devolucion.prestamos'),
            'devolucion-transferencia' => route('asignacion.devolucion.transferencias'),
            default => Route::has('asignacion.iniciar') ? route('asignacion.iniciar') : route('dashboard'),
        };
    }

    private function buildDevolucionPrestamoData(int $numero): ?array
    {
        $records = DevolucionPrestamo::query()->where('numOperacion', $numero)->orderBy('id')->get();
        if ($records->isEmpty()) {
            return null;
        }

        $rows = [];
        foreach ($records as $index => $record) {
            $rows[] = [
                'n' => $index + 1,
                'codigo' => (string) ($record->codigo ?? ''),
                'descripcion' => (string) ($record->descripcion ?? ''),
                'cantidad' => (int) ($record->cantidad ?? 0),
                'tipo' => (string) ($record->tipo ?? ''),
                'ubicacion' => (string) ($record->ubicacion ?? ''),
                'fecha_devolucion' => (string) ($record->fechaDevolucion ?? ''),
                'observacion' => (string) ($record->observacion ?? ''),
            ];
        }

        return [
            'title' => 'Comprobante de Devolucion de Prestamos',
            'meta' => [
                'Operacion Nro' => $numero,
                'Solicitante' => (string) (session('prestamista') ?? ''),
                'Total items' => $records->count(),
            ],
            'columns' => [
                'n' => '#',
                'codigo' => 'Codigo',
                'descripcion' => 'Descripcion',
                'cantidad' => 'Cant.',
                'tipo' => 'Tipo',
                'ubicacion' => 'Ubicacion',
                'fecha_devolucion' => 'Fecha Devolucion',
                'observacion' => 'Observacion',
            ],
            'rows' => $rows,
        ];
    }

    private function buildDevolucionTransferenciaData(int $numero): ?array
    {
        $records = DevolucionTransferencia::query()->where('numOperacion', $numero)->orderBy('id')->get();
        if ($records->isEmpty()) {
            return null;
        }

        $rows = [];
        foreach ($records as $index => $record) {
            $observacion = (string) ($record->observacion ?? '');
            $resultado = 'Devuelto';

            if (str_starts_with($observacion, '[UTILIZADO]')) {
                $resultado = 'Utilizado';
                $observacion = trim((string) preg_replace('/^\[UTILIZADO\]\s*/', '', $observacion));
            } elseif (str_starts_with($observacion, '[DEVUELTO]')) {
                $observacion = trim((string) preg_replace('/^\[DEVUELTO\]\s*/', '', $observacion));
            }

            $rows[] = [
                'n' => $index + 1,
                'codigo' => (string) ($record->codigo ?? ''),
                'descripcion' => (string) ($record->descripcion ?? ''),
                'cantidad' => (int) ($record->cantidad ?? 0),
                'tipo' => (string) ($record->tipo ?? ''),
                'resultado' => $resultado,
                'emisor' => (string) ($record->emisor ?? ''),
                'receptor' => (string) ($record->receptor ?? ''),
                'ubicacion' => (string) ($record->ubicacion ?? ''),
                'fecha_devolucion' => (string) ($record->fechaDevolucion ?? ''),
                'observacion' => $observacion,
            ];
        }

        return [
            'title' => 'Comprobante de Devolucion de Transferencias',
            'meta' => [
                'Operacion Nro' => $numero,
                'Solicitante' => (string) (session('prestamista') ?? ''),
                'Total items' => $records->count(),
            ],
            'columns' => [
                'n' => '#',
                'codigo' => 'Codigo',
                'descripcion' => 'Descripcion',
                'cantidad' => 'Cant.',
                'tipo' => 'Tipo',
                'resultado' => 'Resultado',
                'emisor' => 'Emisor',
                'receptor' => 'Receptor',
                'ubicacion' => 'Ubicacion',
                'fecha_devolucion' => 'Fecha Devolucion',
                'observacion' => 'Observacion',
            ],
            'rows' => $rows,
        ];
    }

    private function buildAlimentosData(int $numero): ?array
    {
        $records = Prestamo::query()->where('numOperacion', $numero)->orderBy('id')->get();
        if ($records->isEmpty()) {
            return null;
        }

        $first = $records->first();
        if (! $first) {
            return null;
        }

        $bienes = Bien::query()
            ->whereIn('codigo', $records->pluck('codigo')->filter()->values())
            ->get(['codigo', 'sap', 'descripcion', 'tipo'])
            ->keyBy('codigo');

        $personas = $this->loadPersonNames([
            (string) ($first->cedulaSolicita ?? ''),
            (string) ($first->cedulaAutoriza ?? ''),
            (string) ($first->cedulaDespacha ?? ''),
        ]);

        $rows = [];
        foreach ($records as $index => $record) {
            $codigo = (string) ($record->codigo ?? '');
            $bien = $bienes->get($codigo);

            $rows[] = [
                'n' => $index + 1,
                'sap' => (string) ($bien?->sap ?? ''),
                'codigo' => $codigo,
                'descripcion' => (string) ($bien?->descripcion ?? ''),
                'tipo' => $this->formatBienTipo($bien?->tipo),
                'cantidad' => (int) ($record->cantidad ?? 0),
                'status' => $this->formatStatus((int) ($record->status ?? 0)),
                'recibido' => '',
                'observacion' => (string) ($record->observacion ?? ''),
            ];
        }

        return [
            'title' => 'Comprobante de Asignacion',
            'meta' => [
                'Operacion Nro' => $numero,
                'Solicita' => $this->formatPersona((string) ($first->cedulaSolicita ?? ''), $personas),
                'Autoriza' => $this->formatPersona((string) ($first->cedulaAutoriza ?? ''), $personas),
                'Despacha' => $this->formatPersona((string) ($first->cedulaDespacha ?? ''), $personas),
                'Fecha Entrega' => $this->formatFecha($first->fechaEntrega),
                'Ubicacion' => (string) ($first->ubicacion ?? ''),
            ],
            'columns' => [
                'n' => '#',
                'sap' => 'SAP',
                'codigo' => 'Codigo',
                'descripcion' => 'Descripcion',
                'tipo' => 'Tipo',
                'cantidad' => 'Cant',
                'status' => 'Status',
                'recibido' => 'Recibido',
                'observacion' => 'Observacion',
            ],
            'rows' => $rows,
        ];
    }

    private function buildTransferenciaData(int $numero): ?array
    {
        $records = Transferencia::query()->where('numOperacion', $numero)->orderBy('id')->get();
        if ($records->isEmpty()) {
            return null;
        }

        $first = $records->first();
        if (! $first) {
            return null;
        }

        $bienes = Bien::query()
            ->whereIn('codigo', $records->pluck('codigo')->filter()->values())
            ->get(['codigo', 'sap', 'descripcion', 'tipo', 'tiempoRetorno'])
            ->keyBy('codigo');

        $personas = $this->loadPersonNames([
            (string) ($first->cedulaSolicita ?? ''),
            (string) ($first->cedulaAutoriza ?? ''),
            (string) ($first->cedulaDespacha ?? ''),
            (string) ($first->cedulaTransportista ?? ''),
            ...$records->pluck('cedulaRecibeDev')->map(fn ($value) => (string) $value)->all(),
        ]);

        $rows = [];
        foreach ($records as $index => $record) {
            $codigo = (string) ($record->codigo ?? '');
            $bien = $bienes->get($codigo);
            $maxRetorno = '';

            if ((int) ($bien?->tipo ?? 0) === 1 && filled($record->fechaEntrega) && (int) ($bien?->tiempoRetorno ?? 0) > 0) {
                try {
                    $maxRetorno = date('d/m/Y', strtotime((string) $record->fechaEntrega . ' +' . (int) $bien->tiempoRetorno . ' days'));
                } catch (\Throwable) {
                    $maxRetorno = '';
                }
            }

            $rows[] = [
                'n' => $index + 1,
                'sap' => (string) ($bien?->sap ?? ''),
                'codigo' => $codigo,
                'descripcion' => (string) ($bien?->descripcion ?? ''),
                'tipo' => $this->formatBienTipo($bien?->tipo),
                'cantidad' => (int) ($record->cantidad ?? 0),
                'status' => $this->formatStatus((int) ($record->status ?? 0)),
                'max_retorno' => $maxRetorno,
                'fecha_devolucion' => $this->formatFecha($record->fechaDevolucion),
                'recibido_por' => $this->formatPersona((string) ($record->cedulaRecibeDev ?? ''), $personas),
                'observacion' => (string) ($record->observacion ?? ''),
            ];
        }

        return [
            'title' => 'Comprobante de Transferencia',
            'meta' => [
                'Operacion Nro' => $numero,
                'Solicita' => $this->formatPersona((string) ($first->cedulaSolicita ?? ''), $personas),
                'Autoriza' => $this->formatPersona((string) ($first->cedulaAutoriza ?? ''), $personas),
                'Despacha' => $this->formatPersona((string) ($first->cedulaDespacha ?? ''), $personas),
                'Transporta' => $this->formatPersona((string) ($first->cedulaTransportista ?? ''), $personas),
                'Fecha Entrega' => $this->formatFecha($first->fechaEntrega),
                'Emisor' => (string) ($first->emisor ?? ''),
                'Receptor' => (string) ($first->receptor ?? ''),
                'Ubicacion' => (string) ($first->ubicacion ?? ''),
            ],
            'columns' => [
                'n' => '#',
                'sap' => 'SAP',
                'codigo' => 'Codigo',
                'descripcion' => 'Descripcion',
                'tipo' => 'Tipo',
                'cantidad' => 'Cant',
                'status' => 'Status',
                'max_retorno' => 'Max. Retorno',
                'fecha_devolucion' => 'Fecha Devolucion',
                'recibido_por' => 'Recibido Por',
                'observacion' => 'Observacion',
            ],
            'rows' => $rows,
        ];
    }

    private function buildDotacionData(int $numero): ?array
    {
        $records = Dotacion::query()->where('numOperacion', $numero)->orderBy('id')->get();
        if ($records->isEmpty()) {
            return null;
        }

        $first = $records->first();
        if (! $first) {
            return null;
        }

        $bienes = Bien::query()
            ->whereIn('codigo', $records->pluck('codigo')->filter()->values())
            ->get(['codigo', 'sap', 'descripcion', 'mesesEntrega'])
            ->keyBy('codigo');

        $personas = $this->loadPersonNames([
            (string) ($first->cedulaSolicita ?? ''),
            (string) ($first->cedulaDespacha ?? ''),
        ]);

        $rows = [];
        foreach ($records as $index => $record) {
            $codigo = (string) ($record->codigo ?? '');
            $bien = $bienes->get($codigo);

            $ultima = Dotacion::query()
                ->where('codigo', $codigo)
                ->where('cedulaSolicita', $first->cedulaSolicita)
                ->where('id', '<', (int) $record->id)
                ->orderByDesc('id')
                ->first();

            $rows[] = [
                'n' => $index + 1,
                'sap' => (string) ($bien?->sap ?? ''),
                'codigo' => $codigo,
                'descripcion' => (string) ($bien?->descripcion ?? ''),
                'cantidad' => (int) ($record->cantidad ?? 0),
                'status' => $this->formatStatus((int) ($record->status ?? 0)),
                'entrega_meses' => (string) ($bien?->mesesEntrega ?? ''),
                'razon' => $this->formatDotacionRazon((int) ($record->razon ?? 0)),
                'ultima_entrega' => $this->formatFecha($ultima?->fechaEntrega),
                'razon_ultima' => $ultima ? $this->formatDotacionRazon((int) ($ultima->razon ?? 0)) : '',
            ];
        }

        return [
            'title' => 'Comprobante de Dotacion',
            'meta' => [
                'Operacion Nro' => $numero,
                'Solicita' => $this->formatPersona((string) ($first->cedulaSolicita ?? ''), $personas),
                'Despacha' => $this->formatPersona((string) ($first->cedulaDespacha ?? ''), $personas),
                'Fecha Entrega' => $this->formatFecha($first->fechaEntrega),
            ],
            'columns' => [
                'n' => '#',
                'sap' => 'SAP',
                'codigo' => 'Codigo',
                'descripcion' => 'Descripcion',
                'cantidad' => 'Cant',
                'status' => 'Status',
                'entrega_meses' => 'Entrega (meses)',
                'razon' => 'Razon',
                'ultima_entrega' => 'Ultima Entrega',
                'razon_ultima' => 'Razon Ult. Entrega',
            ],
            'rows' => $rows,
        ];
    }

    private function loadPersonNames(array $cedulas): array
    {
        $filtered = collect($cedulas)
            ->map(fn ($value) => trim((string) $value))
            ->filter()
            ->unique()
            ->values();

        if ($filtered->isEmpty()) {
            return [];
        }

        return Usuario::query()
            ->whereIn('cedula', $filtered)
            ->pluck('nombre', 'cedula')
            ->map(fn ($value) => (string) $value)
            ->toArray();
    }

    private function formatPersona(string $cedula, array $personas): string
    {
        $cedula = trim($cedula);
        if ($cedula === '') {
            return '';
        }

        $nombre = trim((string) ($personas[$cedula] ?? ''));

        return $nombre === '' ? $cedula : $cedula . ' - ' . $nombre;
    }

    private function formatBienTipo(mixed $tipo): string
    {
        return self::BIEN_TYPE_LABELS[(int) ($tipo ?? 0)] ?? 'Sin tipo';
    }

    private function formatStatus(int $status): string
    {
        return match ($status) {
            1 => 'Entregado',
            2 => 'Devuelto',
            3 => 'Utilizado',
            default => (string) $status,
        };
    }

    private function formatDotacionRazon(int $razon): string
    {
        return match ($razon) {
            1 => 'Vencida',
            2 => 'Deterioro',
            3 => 'Adelanto',
            default => '',
        };
    }

    private function formatFecha(mixed $fecha): string
    {
        if (! filled($fecha)) {
            return '';
        }

        try {
            return date('d/m/Y', strtotime((string) $fecha));
        } catch (\Throwable) {
            return '';
        }
    }
}
