<?php

namespace Velis\QuickReport;

use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Exception as WriterException;
use Velis\App;
use Velis\Lang;
use Velis\Model\BaseModel;
use Velis\Output;
use Velis\Xls;

/**
 * Quick report xls writer
 */
class XlsWriter extends BaseModel
{
    /**
     * @var string column with sheet titles
     */
    private static $_sheetColumn;

    /**
     * @var Spreadsheet
     */
    private $_xls;

    /**
     * Data to export
     * @var array
     */
    private $_data;

    /**
     * Sheet headers
     * @var array
     */
    private $_headers;

    /**
     * Added sheets
     * @var array
     */
    private $_sheets = [];

    /**
     * Current rows in sheets
     * @var array
     */
    private $_sheetsRows = [];

    /**
     * Header titles
     * @var array
     */
    private $_cols = [];

    /**
     * @var bool
     */
    private $_hasSheetColumn;

    /**
     * Current active sheet
     * @var string
     */
    private $_activeSheet;



    /**
     * Constructor
     *
     * @param array $data
     * @param array $headers
     */
    public function __construct($data, $headers)
    {
        $colName = 'A';

        $this->_xls = Xls::createSpreadsheet();
        self::$_sheetColumn = Lang::get('GENERAL_SHEET') != 'GENERAL_SHEET' ? Lang::get('GENERAL_SHEET') : 'Arkusz';

        foreach ($headers as $header) {
            if ($header == self::$_sheetColumn && $colName == 'A') {
                $this->_hasSheetColumn = true;
                continue;
            }
            $this->_cols[$colName++] = $header;
        }

        $this->_data = $data;
        $this->_headers = $headers;
    }


    /**
     * Adds new sheet
     *
     * @param string $name
     * @throws Exception
     */
    private function _addSheet($name)
    {
        if (count($this->_sheets)) {
            $this->_xls->createSheet();
        }
        $this->_xls->setActiveSheetIndex(count($this->_sheets));

        foreach ($this->_cols as $colIndex => $colLabel) {
             $this->_xls->getActiveSheet()->SetCellValue($colIndex . '1', $colLabel);
             $this->_xls->getActiveSheet()->getColumnDimension($colIndex)->setAutoSize(true);
             $this->_xls->getActiveSheet()->getStyle($colIndex . '1')->applyFromArray(Output::getXlsHeadStyle());
        }

        // Rename sheet
        array_push($this->_sheets, $name);
        $this->_xls->getActiveSheet()->setTitle($this->_getSheetName($name));
        $this->_sheetsRows[$name] = 2;
        $this->_activeSheet = $name;
    }


    /**
     * Sets active sheet by cell value
     *
     * @param string $name
     * @throws Exception
     */
    private function _setActiveSheet($name)
    {
        if (!in_array($name, $this->_sheets)) {
            $this->_addSheet($name);
        }
        $this->_activeSheet = $name;
        $this->_xls->setActiveSheetIndexByName($this->_getSheetName($name));
    }


    /**
     * Return type of column
     *
     * @param string $columnName
     * @return string
     */
    public static function columnType($columnName)
    {
        return self::$_db->metadata[$columnName]['native_type'];
    }


    /**
     * Return trimmed sheet title
     * @param string $name
     * @return string
     */
    private function _getSheetName($name)
    {
        $filteredName = trim(
            filter_var(
                str_replace(Xls::SHEET_NAME_INVALID_CHARACTERS, '-', Output::stripPolishChars(substr($name, 0, 30))),
                FILTER_SANITIZE_STRING,
                FILTER_FLAG_STRIP_HIGH
            )
        );

        if (!$filteredName) {
            return self::$_sheetColumn . " " . (array_search($name, $this->_sheets) + 1);
        }

        return $filteredName;
    }

    /**
     * Export to xls
     *
     * @param bool $return
     *
     * @throws Exception
     * @throws WriterException
     *
     * @return string|false|void
     */
    public function export($return = true)
    {
        $this->_xls->getProperties()->setTitle(Lang::get('CUSTOMER_RAPORT'));
        if (!$this->_hasSheetColumn) {
            $this->_addSheet(Lang::get('CUSTOMER_RAPORT'));
        }

        foreach ($this->_data as $item) {
            $colName = 'A';
            foreach ($this->_headers as $header) {
                if ($this->_hasSheetColumn && $header == self::$_sheetColumn) {
                    self::_setActiveSheet($item[$header]);
                    continue;
                }
                $sheet = $this->_xls->getActiveSheet();
                $row = $this->_sheetsRows[$this->_activeSheet];

                $columnType = self::columnType($header);

                if (preg_match('/interval/i', $columnType)) {
                    $cellValue = (string) $item[$header];
                    if ($cellValue !== "") {
                        $sheet->setCellValueExplicit($colName . $row, $cellValue, Xls::TYPE_STRING);
                    }
                } elseif (preg_match('/int/i', $columnType)) {
                    $cellValue = (string) $item[$header];
                    if ($cellValue !== "") {
                        $sheet->setCellValueExplicit($colName . $row, $cellValue, Xls::TYPE_NUMERIC);
                    }
                } elseif (preg_match('/bpchar/i', $columnType)) {
                    if (is_numeric(trim($item[$header]))) {
                        $sheet->setCellValueExplicit($colName . $row, (string) trim($item[$header]), Xls::TYPE_NUMERIC);
                    } else {
                        $sheet->setCellValue($colName . $row, (string) trim($item[$header]));
                    }
                } elseif ('numeric' == $columnType) {
                    $scale = self::$_db->metadata[$header]['scale'];

                    if ($item[$header]) {
                        $sheet->setCellValueExplicit($colName . $row, (string) trim($item[$header]), Xls::TYPE_NUMERIC);
                    }

                    if (2 == $scale) {
                        $sheet
                            ->getStyle($colName . $row)
                            ->getNumberFormat()
                            ->setFormatCode('0.00')
                        ;
                    }
                } elseif (in_array($columnType, ['timestamptz', 'timestamp', 'date'])) {
                    if (!empty($item[$header])) {
                        $sheet->setCellValue($colName . $row, Xls::toXlsDate($item[$header]) ?: $item[$header]);
                        $sheet->getStyle($colName . $row)
                            ->getNumberFormat()
                            ->setFormatCode(Xls::getDateFormat($columnType));
                    }
                } else {
                    // link was added to quick report's SQL expression
                    if (stripos($item[$header], '<a href="' . App::getBaseUrl()) === 0) {
                        $sheet->setCellValue($colName . $row, strip_tags($item[$header]));
                        preg_match_all('/href="([^"]+)"/i', $item[$header], $matches);
                        $link = reset($matches[1]);
                        if ($link) {
                            $sheet
                                ->getCell($colName . $row)
                                ->getHyperlink()
                                ->setUrl($link)
                                ->setTooltip(strip_tags($item[$header]))
                            ;
                        }
                    } else {
                        $sheet->setCellValueExplicit($colName . $row, (string) $item[$header], Xls::TYPE_STRING);
                    }
                }

                $colName++;
            }
            $this->_sheetsRows[$this->_activeSheet]++;
        }
        $this->_xls->setActiveSheetIndex(0);
        $this->_xls->getActiveSheet()->freezePane('A2');

        if ($return) {
            ob_start();
        }

        // Save Excel file
        $writer = Xls::createWriter($this->_xls, Xls::FORMAT_XLSX);
        $writer->save('php://output');

        if ($return) {
            $buffer = ob_get_contents();
            ob_end_clean();

            return $buffer;
        }
    }
}
