<?php

namespace Velis\QuickReport;

use DateTime;
use Generator;
use Velis\App;
use Velis\Lang;
use Velis\Model\BaseModel;
use Velis\Output;
use Velis\Xls;
use Velis\Xls\XlsxWriterLite;

/**
 * Quick report xls writer
 *
 * @author Łukasz Gąsiorek <lukasz.gasiorek@velistech.com>
 */
class XlsWriterLite extends BaseModel
{
    /**
     * @var XLSXWriter
     */
    private $xls;

    /**
     * Data to export
     * @var array|Generator
     */
    private $data = [];

    /**
     * Sheet headers
     * @var array
     */
    private $headers = [];

    /**
     * @var bool
     */
    private $hasSheetColumn = false;

    /**
     * Column with sheet names
     */
    private $sheetColumnName = 'Arkusz';

    /**
     * Constructor
     * @param $data
     * @param array $headers
     */
    public function __construct($data, array $headers = [])
    {
        $this->xls = new XlsxWriterLite();
        $this->sheetColumnName = Lang::get('GENERAL_SHEET') != 'GENERAL_SHEET' ? Lang::get('GENERAL_SHEET') : 'Arkusz';


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

    /**
     * Export to xls
     * Returns null if data is empty
     *
     * @throws Exception
     * @return ?string
     */
    public function export()
    {
        $headerStyle = [
            'font-style' => 'bold',
            'fill' => '#DDDDDD',
            'halign' => 'left',
            'border' => 'left,right,bottom',
            'freeze_rows' => 1,
            'widths' => [],
        ];

        $headerRow = [];
        $sheetsRows = [];

        foreach ($this->data as $item) {
            if (empty($sheetsRows)) {
                $headerRow = $this->prepareHeaders($item);
            }
            $rowData = [];
            $sheetName = Lang::get('CUSTOMER_RAPORT');
            foreach ($this->headers as $header) {
                if (empty($sheetsRows)) {
                    $headerStyle['widths'][] = Xls::getTextWidth($header);
                }
                $cellValue = (string) $item[$header];

                if ($this->hasSheetColumn && $header === $this->sheetColumnName) {
                    $sheetName = self::filterSheetName($cellValue);
                    continue;
                }

                if (in_array(self::columnType($header), ['timestamptz', 'timestamp'])) {
                    if ($cellValue) {
                        $dateTime = new DateTime();
                        $dateTime->setTimestamp(strtotime($cellValue));
                        $cellValue = $dateTime->format('Y-m-d H:i:s');
                    }
                }

                if (self::columnType($header) === 'interval') {
                    $cellValue = $cellValue . ' h';
                }

                // Formulas are not allowed
                if (in_array(mb_substr(trim($cellValue), 0, 1), ['=', '+', '*', '/'])) {
                    $cellValue = "'{$cellValue}";
                }

                if (stripos($cellValue, '<a href="' . App::getBaseUrl()) === 0) {
                    $matches = [];
                    preg_match_all('/href="([^"]+)"/i', $cellValue, $matches);
                    $link = reset($matches[1]);

                    if ($link) {
                        $cellValue = sprintf('=HYPERLINK("%s","%s")', $link, strip_tags($cellValue));
                    }
                }

                $rowData[] = $cellValue;
            }

            if (!isset($sheetsRows[$sheetName])) {
                $sheetsRows[$sheetName] = 0;
                $this->xls->writeSheetHeader($sheetName, $headerRow, $headerStyle);
            }
            $this->xls->writeSheetRow($sheetName, $rowData);
            $sheetsRows[$sheetName]++;
        }

        if (empty($headerRow)) {
            return null;
        }

        $fileName = md5(time() . 'files' . session_id()) . '.xlsx';
        $this->xls->writeToFile(DATA_PATH . 'temp/' . $fileName);
        return $fileName;
    }

    private function prepareHeaders($item): array
    {
        if (empty($this->headers)) {
            $this->headers = array_keys($item);
        }

        if (in_array($this->sheetColumnName, $this->headers)) {
            $this->hasSheetColumn = true;
        }
        $headerRow = [];

        foreach ($this->headers as $header) {
            if ($this->hasSheetColumn && $header === $this->sheetColumnName) {
                continue;
            }

            $columnType = self::columnType($header);
            $xlsType = 'string';

            if (preg_match('/interval/i', $columnType)) {
                $xlsType = 'string';
            } elseif (preg_match('/int/i', $columnType)) {
                $xlsType = 'integer';
            } elseif (preg_match('/bpchar/i', $columnType)) {
                $xlsType = 'string';
            } elseif ('numeric' == $columnType) {
                $xlsType = '0.00';
            } elseif ($columnType === 'date') {
                $xlsType = 'date';
            } elseif (in_array($columnType, ['timestamptz', 'timestamp'])) {
                $xlsType = 'datetime';
            }
            $headerRow[$header] = $xlsType;
        }

        return $headerRow;
    }

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

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