<?php

require_once APP_ROOT . '/models/Collection.php';
require_once APP_ROOT . '/models/Subcollection.php';
require_once APP_ROOT . '/models/Item.php';
require_once APP_ROOT . '/models/ItemVariant.php';
require_once APP_ROOT . '/models/Coin.php';
require_once APP_ROOT . '/models/CoinImage.php';

// Import PhpSpreadsheet classes
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Worksheet\Drawing;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;

class PdfController
{
    public function __construct()
    {
        if (!Session::isLoggedIn()) {
            header('Location: ' . APP_URL . '/login');
            exit;
        }
    }

    public function exportCollection(string $id): void
    {
        $collectionModel = new Collection();
        $collection = $collectionModel->findById((int)$id);

        if (!$collection) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        if ($collection['user_id'] !== Session::getUserId()) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        $subModel = new Subcollection();
        $subcollections = $subModel->getAllByCollection((int)$id);

        // Proveri tip kolekcije i učitaj odgovarajuće podatke
        if ($collection['type'] === 'coin') {
            $coinModel = new Coin();
            $coinImageModel = new CoinImage();
            foreach ($subcollections as &$sub) {
                $sub['items'] = $coinModel->getAllBySubcollection($sub['id']);
                foreach ($sub['items'] as &$itm) {
                    $itm['images'] = $coinImageModel->getAllByCoin($itm['id']);
                }
                unset($itm);
            }
            unset($sub);
        } else {
            $itemModel = new Item();
            $variantModel = new ItemVariant();
            foreach ($subcollections as &$sub) {
                $sub['items'] = $itemModel->getAllBySubcollection($sub['id']);
                foreach ($sub['items'] as &$itm) {
                    $itm['variants'] = $variantModel->getAllByItem($itm['id']);
                }
                unset($itm);
            }
            unset($sub);
        }

        $pdf = new TCPDF('L', 'mm', 'A4', true, 'UTF-8', false);
        $pdf->SetCreator(Lang::get('app_name'));
        $pdf->SetAuthor(Lang::get('app_name'));
        $pdf->SetTitle($collection['name']);
        $pdf->SetMargins(10, 15, 10);
        $pdf->SetAutoPageBreak(true, 15);
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);

        $pdf->AddPage();
        $pdf->SetFont('helvetica', 'B', 20);
        $pdf->Cell(0, 12, $collection['name'], 0, 1, 'C');

        if (!empty($collection['description'])) {
            $pdf->SetFont('helvetica', '', 10);
            $pdf->MultiCell(0, 6, $collection['description'], 0, 'C');
        }
        $pdf->Ln(5);

        foreach ($subcollections as $sub) {
            $this->renderSubcollectionPdf($pdf, $sub, true);
        }

        $filename = preg_replace('/[^a-zA-Z0-9_-]/', '_', $collection['name']) . '.pdf';
        $pdf->Output($filename, 'D');
        exit;
    }

    public function exportSubcollection(string $id): void
    {
        $subModel = new Subcollection();
        $subcollection = $subModel->getWithCollection((int)$id);

        if (!$subcollection) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        $collectionModel = new Collection();
        $collection = $collectionModel->findById($subcollection['collection_id']);

        if (!$collection || $collection['user_id'] !== Session::getUserId()) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        // Proveri tip kolekcije i učitaj odgovarajuće podatke
        if ($collection['type'] === 'coin') {
            $coinModel = new Coin();
            $coinImageModel = new CoinImage();
            $subcollection['items'] = $coinModel->getAllBySubcollection((int)$id);
            foreach ($subcollection['items'] as &$itm) {
                $itm['images'] = $coinImageModel->getAllByCoin($itm['id']);
            }
            unset($itm);
        } else {
            $itemModel = new Item();
            $variantModel = new ItemVariant();
            $subcollection['items'] = $itemModel->getAllBySubcollection((int)$id);
            foreach ($subcollection['items'] as &$itm) {
                $itm['variants'] = $variantModel->getAllByItem($itm['id']);
            }
            unset($itm);
        }

        $pdf = new TCPDF('L', 'mm', 'A4', true, 'UTF-8', false);
        $pdf->SetCreator(Lang::get('app_name'));
        $pdf->SetAuthor(Lang::get('app_name'));
        $pdf->SetTitle($subcollection['name']);
        $pdf->SetMargins(10, 15, 10);
        $pdf->SetAutoPageBreak(true, 15);
        $pdf->setPrintHeader(false);
        $pdf->setPrintFooter(false);

        $pdf->AddPage();

        if ($collection) {
            $pdf->SetFont('helvetica', '', 10);
            $pdf->Cell(0, 6, $collection['name'], 0, 1, 'C');
        }

        $this->renderSubcollectionPdf($pdf, $subcollection, true);

        $filename = preg_replace('/[^a-zA-Z0-9_-]/', '_', $subcollection['name']) . '.pdf';
        $pdf->Output($filename, 'D');
        exit;
    }

    private function renderSubcollectionPdf(TCPDF $pdf, array $sub, bool $showOwned): void
    {
        $pdf->SetFont('helvetica', 'B', 14);
        $pdf->Cell(0, 10, $sub['name'], 0, 1, 'L');

        if (!empty($sub['description'])) {
            $pdf->SetFont('helvetica', '', 9);
            $pdf->MultiCell(0, 5, $sub['description'], 0, 'L');
        }
        $pdf->Ln(3);

        if (empty($sub['items'])) {
            $pdf->SetFont('helvetica', 'I', 9);
            $pdf->Cell(0, 6, Lang::get('no_items'), 0, 1);
            $pdf->Ln(5);
            return;
        }

        // Proveri tip kolekcije i postavi odgovarajuće header-e
        $isCoin = false;
        if (!empty($sub['items'])) {
            $firstItem = reset($sub['items']);
            $isCoin = isset($firstItem['year_minted']); // kovanice imaju year_minted
        }

        if ($isCoin) {
            $headers = [
                Lang::get('obverse'),
                Lang::get('reverse'),
                Lang::get('country'),
                Lang::get('nominal_value'),
                Lang::get('year_minted'),
                Lang::get('material'),
                Lang::get('weight'),
                Lang::get('diameter'),
                Lang::get('mintage'),
                Lang::get('catalog_number'),
                Lang::get('owned'),
            ];
            $colWidths = [30, 30, 20, 20, 15, 15, 12, 12, 18, 20, 12];
        } else {
            $headers = [
                Lang::get('obverse'),
                Lang::get('reverse'),
                Lang::get('country'),
                Lang::get('nominal_value'),
                Lang::get('dimensions'),
                Lang::get('color'),
                Lang::get('issue_date'),
                Lang::get('additional_info'),
                Lang::get('catalog_number'),
                Lang::get('variants'),
            ];
            $colWidths = [30, 30, 22, 24, 20, 16, 20, 26, 20, 46];
        }

        $imgW = 30;

        $totalW = array_sum($colWidths);
        $pageW = $pdf->getPageWidth() - 20;
        if ($totalW > $pageW) {
            $ratio = $pageW / $totalW;
            $colWidths = array_map(fn($w) => $w * $ratio, $colWidths);
            $imgW = $imgW * $ratio;
        }

        $pdf->SetFont('helvetica', 'B', 7);
        $pdf->SetFillColor(240, 240, 240);
        for ($i = 0; $i < count($headers); $i++) {
            $pdf->Cell($colWidths[$i], 7, $headers[$i], 1, 0, 'C', true);
        }
        $pdf->Ln();

        $pdf->SetFont('helvetica', '', 7);
        $rowH = max(22, $imgW * 0.7);

        foreach ($sub['items'] as $item) {
            if ($pdf->GetY() + $rowH > $pdf->getPageHeight() - 20) {
                $pdf->AddPage();
            }

            $startY = $pdf->GetY();
            $startX = $pdf->GetX();

            $obvImg = $item['image_obverse'] ? UPLOAD_ITEMS_PATH . '/' . $item['image_obverse'] : null;
            $revImg = $item['image_reverse'] ? UPLOAD_ITEMS_PATH . '/' . $item['image_reverse'] : null;

            $x = $startX;
            if ($obvImg && file_exists($obvImg)) {
                $pdf->Image($obvImg, $x + 2, $startY + 1, $imgW - 2, $rowH - 2, '', '', '', true, 150, '', false, false, 0);
            }
            $x += $colWidths[0];

            if ($revImg && file_exists($revImg)) {
                $pdf->Image($revImg, $x + 2, $startY + 1, $imgW - 2, $rowH - 2, '', '', '', true, 150, '', false, false, 0);
            }
            $x += $colWidths[1];

            // Priprema tekstualnih kolona zavisno od tipa
            if ($isCoin) {
                $textCols = [
                    $item['country'] ?? '',
                    $item['nominal_value'] ?? '',
                    $item['year_minted'] ?? '',
                    $item['material'] ? Lang::get('material_' . $item['material']) : '',
                    $item['weight'] ? $item['weight'] . 'g' : '',
                    $item['diameter'] ? $item['diameter'] . 'mm' : '',
                    $item['mintage'] ? number_format($item['mintage']) : '',
                    $item['catalog_number'] ?? '',
                    $item['owned'] ? Lang::get('owned_yes') : Lang::get('owned_no'),
                ];
            } else {
                $variantLines = [];
                if (!empty($item['variants'])) {
                    foreach ($item['variants'] as $v) {
                        $parts = [];
                        if ($v['condition_label']) $parts[] = $v['condition_label'];
                        if ($v['variant_type_label']) $parts[] = $v['variant_type_label'];
                        if ($v['note']) $parts[] = $v['note'];
                        $owned = $v['owned'] ? Lang::get('owned_yes') : Lang::get('owned_no');
                        $variantLines[] = implode(' / ', $parts) . ' [' . $owned . ']';
                    }
                }
                
                $textCols = [
                    $item['country'] ?? '',
                    $item['nominal_value'] ?? '',
                    $item['dimensions'] ?? '',
                    $item['color'] ?? '',
                    $item['issue_date'] ?? '',
                    $item['additional_info'] ?? '',
                    $item['catalog_number'] ?? '',
                    implode("\n", $variantLines),
                ];
            }

            $x = $startX + $colWidths[0] + $colWidths[1];
            for ($i = 0; $i < count($textCols); $i++) {
                $pdf->SetXY($x, $startY);
                $colIndex = $i + 2; // preskačemo prve 2 kolone (slike)
                if ($colIndex < count($colWidths)) {
                    $pdf->MultiCell($colWidths[$colIndex], $rowH, $textCols[$i], 1, 'C', false, 0);
                    $x += $colWidths[$colIndex];
                }
            }

            $pdf->Rect($startX, $startY, $colWidths[0], $rowH);
            $pdf->Rect($startX + $colWidths[0], $startY, $colWidths[1], $rowH);

            $pdf->SetXY($startX, $startY + $rowH);
        }

        $pdf->Ln(8);
    }

    public function exportCollectionXls(string $id): void
    {
        $collectionModel = new Collection();
        $collection = $collectionModel->findById((int)$id);

        if (!$collection) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        if ($collection['user_id'] !== Session::getUserId()) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        $subModel = new Subcollection();
        $subcollections = $subModel->getAllByCollection((int)$id);

        // Load data similar to PDF export
        if ($collection['type'] === 'coin') {
            $coinModel = new Coin();
            $coinImageModel = new CoinImage();
            foreach ($subcollections as &$sub) {
                $sub['items'] = $coinModel->getAllBySubcollection($sub['id']);
                foreach ($sub['items'] as &$itm) {
                    $itm['images'] = $coinImageModel->getAllByCoin($itm['id']);
                }
                unset($itm);
            }
            unset($sub);
        } else {
            $itemModel = new Item();
            $variantModel = new ItemVariant();
            foreach ($subcollections as &$sub) {
                $sub['items'] = $itemModel->getAllBySubcollection($sub['id']);
                foreach ($sub['items'] as &$itm) {
                    $itm['variants'] = $variantModel->getAllByItem($itm['id']);
                }
                unset($itm);
            }
            unset($sub);
        }

        $this->generateXlsFile($collection, $subcollections);
    }

    public function exportSubcollectionXls(string $id): void
    {
        $subModel = new Subcollection();
        $subcollection = $subModel->getWithCollection((int)$id);

        if (!$subcollection) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        if ($subcollection['user_id'] !== Session::getUserId()) {
            header('Location: ' . APP_URL . '/home');
            exit;
        }

        $collection = [
            'id' => $subcollection['collection_id'],
            'name' => $subcollection['collection_name'],
            'type' => $subcollection['type']
        ];

        // Load data
        if ($subcollection['type'] === 'coin') {
            $coinModel = new Coin();
            $coinImageModel = new CoinImage();
            $subcollection['items'] = $coinModel->getAllBySubcollection($subcollection['id']);
            foreach ($subcollection['items'] as &$itm) {
                $itm['images'] = $coinImageModel->getAllByCoin($itm['id']);
            }
            unset($itm);
        } else {
            $itemModel = new Item();
            $variantModel = new ItemVariant();
            $subcollection['items'] = $itemModel->getAllBySubcollection($subcollection['id']);
            foreach ($subcollection['items'] as &$itm) {
                $itm['variants'] = $variantModel->getAllByItem($itm['id']);
            }
            unset($itm);
        }

        $this->generateXlsFile($collection, [$subcollection]);
    }

    private function generateXlsFile(array $collection, array $subcollections): void
    {
        // Debug: Log collection name
        error_log('XLS Export - Collection: ' . $collection['name']);
        error_log('XLS Export - Subcollections count: ' . count($subcollections));
        
        // Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        
        // Set document properties
        $spreadsheet->getProperties()
            ->setTitle($collection['name'])
            ->setCreator('NotaphilyCatalogue')
            ->setLastModifiedBy('NotaphilyCatalogue')
            ->setDescription('Export of collection: ' . $collection['name']);
        
        // Set default styles
        $headerStyle = [
            'font' => [
                'bold' => true,
                'color' => ['rgb' => 'FFFFFF']
            ],
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => ['rgb' => '4472C4']
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        
        $dataStyle = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];
        
        $row = 1;
        
        // Collection title
        $sheet->setCellValue('A' . $row, $collection['name']);
        $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(16);
        $row++;
        $row++;
        
        // Track row heights dynamically
        $rowHeights = [];
        
        foreach ($subcollections as $sub) {
            // Subcollection title
            $sheet->setCellValue('A' . $row, $sub['name']);
            $sheet->getStyle('A' . $row)->getFont()->setBold(true)->setSize(14);
            $row++;
            $row++;
            
            if (empty($sub['items'])) {
                $sheet->setCellValue('A' . $row, Lang::get('no_items'));
                $row += 2;
                continue;
            }
            
            // Headers based on collection type
            if ($collection['type'] === 'coin') {
                $headers = [
                    Lang::get('image_obverse'),
                    Lang::get('image_reverse'),
                    Lang::get('country'),
                    Lang::get('nominal_value'),
                    Lang::get('year_minted'),
                    Lang::get('material'),
                    Lang::get('weight'),
                    Lang::get('diameter'),
                    Lang::get('mintage'),
                    Lang::get('catalog_number'),
                    Lang::get('owned')
                ];
                
                // Set headers
                $col = 'A';
                foreach ($headers as $header) {
                    $sheet->setCellValue($col . $row, $header);
                    $sheet->getStyle($col . $row)->applyFromArray($headerStyle);
                    $col++;
                }
                $row++;
                
                // Data rows
                foreach ($sub['items'] as $item) {
                    $col = 'A';
                    $maxImageHeight = 80; // Default height
                    
                    // Images
                    $obvImg = $item['image_obverse'] ? UPLOAD_ITEMS_PATH . '/' . $item['image_obverse'] : null;
                    $revImg = $item['image_reverse'] ? UPLOAD_ITEMS_PATH . '/' . $item['image_reverse'] : null;
                    
                    // Process obverse image
                    if ($obvImg && file_exists($obvImg)) {
                        // Resize image for export
                        $resizedImg = $this->resizeImageForExport($obvImg);
                        if ($resizedImg) {
                            $imageSize = getimagesize($resizedImg);
                            if ($imageSize) {
                                $maxImageHeight = max($maxImageHeight, $imageSize[1]);
                            }
                            
                            $drawing = new Drawing();
                            $drawing->setPath($resizedImg);
                            $drawing->setCoordinates($col . $row);
                            $drawing->setWidth(80);
                            $drawing->setHeight(80);
                            $drawing->setResizeProportional(false);
                            $drawing->setOffsetX(2);
                            $drawing->setOffsetY(2);
                            $drawing->setWorksheet($sheet);
                        }
                    }
                    $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
                    $col++;
                    
                    // Process reverse image
                    if ($revImg && file_exists($revImg)) {
                        // Resize image for export
                        $resizedImg = $this->resizeImageForExport($revImg);
                        if ($resizedImg) {
                            $imageSize = getimagesize($resizedImg);
                            if ($imageSize) {
                                $maxImageHeight = max($maxImageHeight, $imageSize[1]);
                            }
                            
                            $drawing = new Drawing();
                            $drawing->setPath($resizedImg);
                            $drawing->setCoordinates($col . $row);
                            $drawing->setWidth(80);
                            $drawing->setHeight(80);
                            $drawing->setResizeProportional(false);
                            $drawing->setOffsetX(2);
                            $drawing->setOffsetY(2);
                            $drawing->setWorksheet($sheet);
                        }
                    }
                    $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
                    $col++;
                    
                    // Store row height
                    $rowHeights[$row] = max($maxImageHeight + 20, 80); // Add padding, minimum 80
                    
                    // Data
                    $data = [
                        $item['country'] ?? '',
                        $item['nominal_value'] ?? '',
                        $item['year_minted'] ?? '',
                        $item['material'] ? Lang::get('material_' . $item['material']) : '',
                        $item['weight'] ?? '',
                        $item['diameter'] ?? '',
                        $item['mintage'] ? number_format($item['mintage']) : '',
                        $item['catalog_number'] ?? '',
                        $item['owned'] ? Lang::get('owned_yes') : Lang::get('owned_no')
                    ];
                    
                    foreach ($data as $value) {
                        $sheet->setCellValue($col . $row, $value);
                        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
                        $col++;
                    }
                    
                    $row++;
                }
            } else {
                // Banknotes/Items
                $headers = [
                    Lang::get('image_obverse'),
                    Lang::get('image_reverse'),
                    Lang::get('country'),
                    Lang::get('nominal_value'),
                    Lang::get('dimensions'),
                    Lang::get('color'),
                    Lang::get('issue_date'),
                    Lang::get('additional_info'),
                    Lang::get('catalog_number'),
                    Lang::get('variants')
                ];
                
                // Set headers
                $col = 'A';
                foreach ($headers as $header) {
                    $sheet->setCellValue($col . $row, $header);
                    $sheet->getStyle($col . $row)->applyFromArray($headerStyle);
                    $col++;
                }
                $row++;
                
                // Data rows
                foreach ($sub['items'] as $item) {
                    $col = 'A';
                    $maxImageHeight = 80; // Default height
                    
                    // Images
                    $obvImg = $item['image_obverse'] ? UPLOAD_ITEMS_PATH . '/' . $item['image_obverse'] : null;
                    $revImg = $item['image_reverse'] ? UPLOAD_ITEMS_PATH . '/' . $item['image_reverse'] : null;
                    
                    // Process obverse image
                    if ($obvImg && file_exists($obvImg)) {
                        // Resize image for export
                        $resizedImg = $this->resizeImageForExport($obvImg);
                        if ($resizedImg) {
                            $imageSize = getimagesize($resizedImg);
                            if ($imageSize) {
                                $maxImageHeight = max($maxImageHeight, $imageSize[1]);
                            }
                            
                            $drawing = new Drawing();
                            $drawing->setPath($resizedImg);
                            $drawing->setCoordinates($col . $row);
                            $drawing->setWidth(80);
                            $drawing->setHeight(80);
                            $drawing->setResizeProportional(false);
                            $drawing->setOffsetX(2);
                            $drawing->setOffsetY(2);
                            $drawing->setWorksheet($sheet);
                        }
                    }
                    $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
                    $col++;
                    
                    // Process reverse image
                    if ($revImg && file_exists($revImg)) {
                        // Resize image for export
                        $resizedImg = $this->resizeImageForExport($revImg);
                        if ($resizedImg) {
                            $imageSize = getimagesize($resizedImg);
                            if ($imageSize) {
                                $maxImageHeight = max($maxImageHeight, $imageSize[1]);
                            }
                            
                            $drawing = new Drawing();
                            $drawing->setPath($resizedImg);
                            $drawing->setCoordinates($col . $row);
                            $drawing->setWidth(80);
                            $drawing->setHeight(80);
                            $drawing->setResizeProportional(false);
                            $drawing->setOffsetX(2);
                            $drawing->setOffsetY(2);
                            $drawing->setWorksheet($sheet);
                        }
                    }
                    $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
                    $col++;
                    
                    // Store row height
                    $rowHeights[$row] = max($maxImageHeight + 20, 80); // Add padding, minimum 80
                    
                    // Data
                    $variantLines = [];
                    if (!empty($item['variants'])) {
                        foreach ($item['variants'] as $v) {
                            $parts = [];
                            if ($v['condition_label']) $parts[] = $v['condition_label'];
                            if ($v['variant_type_label']) $parts[] = $v['variant_type_label'];
                            if ($v['note']) $parts[] = $v['note'];
                            $owned = $v['owned'] ? Lang::get('owned_yes') : Lang::get('owned_no');
                            $variantLines[] = implode(' / ', $parts) . ' [' . $owned . ']';
                        }
                    }
                    
                    $data = [
                        $item['country'] ?? '',
                        $item['nominal_value'] ?? '',
                        $item['dimensions'] ?? '',
                        $item['color'] ?? '',
                        $item['issue_date'] ?? '',
                        $item['additional_info'] ?? '',
                        $item['catalog_number'] ?? '',
                        implode(' | ', $variantLines)
                    ];
                    
                    foreach ($data as $value) {
                        $sheet->setCellValue($col . $row, $value);
                        $sheet->getStyle($col . $row)->applyFromArray($dataStyle);
                        $col++;
                    }
                    
                    $row++;
                }
            }
            
            $row += 2;
        }
        
        // Apply dynamic row heights
        foreach ($rowHeights as $rowNum => $height) {
            $sheet->getRowDimension($rowNum)->setRowHeight($height);
        }
        
        // Auto-size columns for better readability (except first two columns)
        foreach (range('C', 'Z') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
        
        // Set fixed width for image columns
        $sheet->getColumnDimension('A')->setWidth(25); // Obverse image
        $sheet->getColumnDimension('B')->setWidth(25); // Reverse image
        
        // Set headers for download
        $filename = preg_replace('/[^a-zA-Z0-9_-]/', '_', $collection['name']);
        if (count($subcollections) === 1) {
            $filename .= '_' . preg_replace('/[^a-zA-Z0-9_-]/', '_', $subcollections[0]['name']);
        }
        $filename .= '.xlsx';
        
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');
        header('Expires: 0');
        header('Pragma: public');
        
        // Create writer and output
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }
    
    private function resizeImageForExport(string $imagePath, int $maxWidth = 160, int $maxHeight = 160): ?string
    {
        if (!file_exists($imagePath)) {
            return null;
        }
        
        // Create temporary file for resized image
        $tempFile = tempnam(sys_get_temp_dir(), 'export_img_');
        unlink($tempFile); // Remove temp file so we can create it with proper extension
        $tempFile .= '.jpg';
        
        // Get image info
        $imageInfo = getimagesize($imagePath);
        if (!$imageInfo) {
            return $imagePath; // Return original if can't get info
        }
        
        list($width, $height, $type) = $imageInfo;
        
        // Calculate new dimensions
        $ratio = min($maxWidth / $width, $maxHeight / $height, 1);
        $newWidth = (int)($width * $ratio);
        $newHeight = (int)($height * $ratio);
        
        // If image is already small enough, return original
        if ($ratio >= 1) {
            return $imagePath;
        }
        
        // Create image resource based on type
        switch ($type) {
            case IMAGETYPE_JPEG:
                $source = imagecreatefromjpeg($imagePath);
                break;
            case IMAGETYPE_PNG:
                $source = imagecreatefrompng($imagePath);
                break;
            case IMAGETYPE_GIF:
                $source = imagecreatefromgif($imagePath);
                break;
            default:
                return $imagePath;
        }
        
        if (!$source) {
            return $imagePath;
        }
        
        // Create new image
        $destination = imagecreatetruecolor($newWidth, $newHeight);
        
        // Handle transparency for PNG
        if ($type == IMAGETYPE_PNG) {
            imagealphablending($destination, false);
            imagesavealpha($destination, true);
            $transparent = imagecolorallocatealpha($destination, 255, 255, 255, 127);
            imagefilledrectangle($destination, 0, 0, $newWidth, $newHeight, $transparent);
        }
        
        // Resize image
        imagecopyresampled($destination, $source, 0, 0, 0, 0, $newWidth, $newHeight, $width, $height);
        
        // Save resized image
        switch ($type) {
            case IMAGETYPE_JPEG:
                imagejpeg($destination, $tempFile, 85); // 85% quality
                break;
            case IMAGETYPE_PNG:
                imagepng($destination, $tempFile, 6); // Compression level 6
                break;
            case IMAGETYPE_GIF:
                imagegif($destination, $tempFile);
                break;
            default:
                imagedestroy($source);
                imagedestroy($destination);
                return $imagePath;
        }
        
        // Clean up
        imagedestroy($source);
        imagedestroy($destination);
        
        return $tempFile;
    }
}