<?php
namespace boru\boruai\OCR\Methods;

class Excell {
    /**
     * Convert a spreadsheet file (xls/xlsx) into a CSV string.
     * All sheets are concatenated with a "### Sheet: {name}" header.
     *
     * @param string $filePath
     * @return string
     * @throws Exception
     */
    public function convertSpreadsheetToCsvString($filePath)
    {
        // Adjust this include path to match your vtiger/PHPExcel setup
        if (!class_exists('\\PHPExcel_IOFactory')) {
            // In vtiger this is typically libraries/PHPExcel/PHPExcel.php
            require_once 'libraries/PHPExcel/PHPExcel.php';
            if(!class_exists('\\PHPExcel_IOFactory')) {
                throw new \Exception("PHPExcel library not found. Please ensure it is installed and the include path is correct.");
            }
        }

        // Identify the file type and create reader
        $inputFileType = \PHPExcel_IOFactory::identify($filePath);
        $reader = \PHPExcel_IOFactory::createReader($inputFileType);
        //$reader->setReadDataOnly(true);

        /** @var \PHPExcel $excel */
        $excel = $reader->load($filePath);

        $csvChunks = array();

        foreach ($excel->getWorksheetIterator() as $sheet) {
            /** @var \PHPExcel_Worksheet $sheet */
            $sheetTitle = $sheet->getTitle();
            $csvChunks[] = '### Sheet: ' . $sheetTitle;

            $highestRow = $sheet->getHighestRow();
            $highestColumn = $sheet->getHighestColumn();
            $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);

            for ($row = 1; $row <= $highestRow; $row++) {
                $rowData = array();

                for ($col = 0; $col < $highestColumnIndex; $col++) {
                    $cell = $sheet->getCellByColumnAndRow($col, $row);
                    $value = $cell->getFormattedValue(); // respects number/date formats
                    $rowData[] = $this->escapeCsvValue($value);
                }

                // Skip trailing empty rows if you want; for now include them
                $csvChunks[] = implode(',', $rowData);
            }

            // Blank line between sheets
            $csvChunks[] = '';
        }

        return implode("\n", $csvChunks);
    }

    /**
     * Very simple CSV value escaping for comma-separated output.
     *
     * @param mixed $value
     * @return string
     */
    protected function escapeCsvValue($value)
    {
        if ($value === null) {
            $value = '';
        }

        $value = (string)$value;

        // Escape inner quotes
        $value = str_replace('"', '""', $value);

        // If contains comma, quote, or newline, wrap in quotes
        $mustQuote = (strpos($value, ',') !== false)
            || (strpos($value, '"') !== false)
            || (strpos($value, "\n") !== false)
            || (strpos($value, "\r") !== false);

        if ($mustQuote) {
            $value = '"' . $value . '"';
        }

        return $value;
    }
}