<?php
/**
 * Excel/CSV Export & Import
 * Supports CSV and XLSX formats via PhpSpreadsheet
 */

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
use PhpOffice\PhpSpreadsheet\Reader\Csv as CsvReader;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;

class ExcelExport
{
    private array $headers = [];
    private array $rows = [];
    private string $title = '';
    private string $filename = 'export';
    private ?string $logoPath = null;

    public function setTitle(string $title): void
    {
        $this->title = $title;
    }

    public function setFilename(string $filename): void
    {
        $this->filename = $filename;
    }

    public function setLogo(?string $logoPath): void
    {
        $this->logoPath = $logoPath;
    }

    public function setHeaders(array $headers): void
    {
        $this->headers = $headers;
    }

    public function addRow(array $row): void
    {
        $this->rows[] = $row;
    }

    public function addRows(array $rows): void
    {
        foreach ($rows as $row) {
            $this->rows[] = $row;
        }
    }

    // --- CSV Methods ---

    public function generateCSV(): string
    {
        $output = fopen('php://temp', 'r+');

        if ($this->title) {
            fputcsv($output, [$this->title]);
            fputcsv($output, ['Generated: ' . date('Y-m-d H:i:s')]);
            fputcsv($output, []);
        }

        if (!empty($this->headers)) {
            fputcsv($output, $this->headers);
        }

        foreach ($this->rows as $row) {
            fputcsv($output, $row);
        }

        rewind($output);
        $csv = stream_get_contents($output);
        fclose($output);

        return $csv;
    }

    public function downloadCSV(): void
    {
        $csv = $this->generateCSV();
        $filename = $this->filename . '_' . date('Ymd_His') . '.csv';

        if (ob_get_level()) ob_clean();
        header('Content-Type: text/csv; charset=utf-8');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache');
        header('Expires: 0');
        header('Cache-Control: private, no-cache, no-store, must-revalidate');
        header('X-Content-Type-Options: nosniff');

        echo $csv;
        exit;
    }

    // --- XLSX Export ---

    public function downloadXLSX(): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $rowNum = 1;

        if ($this->logoPath && file_exists(APP_ROOT . '/' . $this->logoPath)) {
            $drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
            $drawing->setName('Logo');
            $drawing->setPath(APP_ROOT . '/' . $this->logoPath);
            $drawing->setHeight(60);
            $drawing->setCoordinates('A1');
            $drawing->setWorksheet($sheet);
            $rowNum = 4;
        }

        if ($this->title) {
            $sheet->setCellValue('A' . $rowNum, $this->title);
            $sheet->getStyle('A' . $rowNum)->getFont()->setBold(true)->setSize(14);
            $rowNum++;

            $sheet->setCellValue('A' . $rowNum, 'Generated: ' . date('Y-m-d H:i:s'));
            $rowNum++;
            $rowNum++;
        }

        if (!empty($this->headers)) {
            $col = 'A';
            foreach ($this->headers as $header) {
                $sheet->setCellValue($col . $rowNum, $header);
                $sheet->getStyle($col . $rowNum)->getFont()->setBold(true);
                $col++;
            }
            $sheet->getStyle('A' . $rowNum . ':' . chr(ord('A') + count($this->headers) - 1) . $rowNum)
                ->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                ->getStartColor()->setRGB('D9E1F2');
            $rowNum++;
        }

        foreach ($this->rows as $row) {
            $col = 'A';
            foreach ($row as $cell) {
                $sheet->setCellValue($col . $rowNum, $cell);
                $col++;
            }
            $rowNum++;
        }

        // Auto-size columns
        foreach (range('A', chr(ord('A') + count($this->headers) - 1)) as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $filename = $this->filename . '_' . date('Ymd_His') . '.xlsx';

        if (ob_get_level()) ob_clean();
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Pragma: no-cache');
        header('Expires: 0');
        header('Cache-Control: private, no-cache, no-store, must-revalidate');
        header('X-Content-Type-Options: nosniff');

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        exit;
    }

    // --- XLSX Import ---

    /**
     * Read rows from an XLSX file.
     * Returns an array of arrays (rows), each containing cell values.
     * Skips the first row if $skipHeader is true.
     */
    public static function readXLSX(string $filePath, bool $skipHeader = true): array
    {
        $reader = new XlsxReader();
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = [];
        $firstRow = true;
        foreach ($sheet->getRowIterator() as $row) {
            if ($firstRow && $skipHeader) {
                $firstRow = false;
                continue;
            }
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            $rowData = [];
            foreach ($cellIterator as $cell) {
                $rowData[] = $cell->getValue();
            }
            $rows[] = $rowData;
        }
        return $rows;
    }

    /**
     * Read rows from a CSV file.
     * Returns an array of arrays (rows).
     * Skips the first row if $skipHeader is true.
     */
    public static function readCSV(string $filePath, bool $skipHeader = true): array
    {
        $reader = new CsvReader();
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $rows = [];
        $firstRow = true;
        foreach ($sheet->getRowIterator() as $row) {
            if ($firstRow && $skipHeader) {
                $firstRow = false;
                continue;
            }
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            $rowData = [];
            foreach ($cellIterator as $cell) {
                $rowData[] = $cell->getValue();
            }
            $rows[] = $rowData;
        }
        return $rows;
    }

    // --- HTML Export ---

    public function generateHTML(): string
    {
        $html = '<table border="1" cellpadding="5" cellspacing="0">';
        if ($this->title) {
            $colspan = max(1, count($this->headers));
            $html .= '<tr><th colspan="' . $colspan . '">' . htmlspecialchars($this->title) . '</th></tr>';
        }
        if (!empty($this->headers)) {
            $html .= '<tr>';
            foreach ($this->headers as $header) {
                $html .= '<th>' . htmlspecialchars($header) . '</th>';
            }
            $html .= '</tr>';
        }
        foreach ($this->rows as $row) {
            $html .= '<tr>';
            foreach ($row as $cell) {
                $html .= '<td>' . htmlspecialchars($cell) . '</td>';
            }
            $html .= '</tr>';
        }
        $html .= '</table>';
        return $html;
    }
}
