<?php

namespace Velis;

use InvalidArgumentException;
use PhpOffice\PhpSpreadsheet\Calculation\DateTime;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Csv as CsvReader;
use PhpOffice\PhpSpreadsheet\Reader\IReader;
use PhpOffice\PhpSpreadsheet\Reader\Xls as XlsReader;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Settings;
use PhpOffice\PhpSpreadsheet\Shared\Date as XlsDate;
use PhpOffice\PhpSpreadsheet\Shared\Drawing;
use PhpOffice\PhpSpreadsheet\Shared\Font;
use PhpOffice\PhpSpreadsheet\Style\Font as StyleFont;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\IWriter;
use Velis\Xls\ValueBinder;

/**
 * Basic XLS functions wrapper
 * @author Olek Procki <olo@velis.pl>
 */
class Xls
{
    const DEFAULT_COLUMN_WIDTH = 20;

    /* Data types */
    const TYPE_STRING2  = 'str';
    const TYPE_STRING   = 's';
    const TYPE_FORMULA  = 'f';
    const TYPE_NUMERIC  = 'n';
    const TYPE_BOOL     = 'b';
    const TYPE_NULL     = 'null';
    const TYPE_INLINE   = 'inlineStr';
    const TYPE_ERROR    = 'e';
    const TYPE_TEXT     = '@';
    const TYPE_TIMEINTERVAL = '[h]:mm';

    // Border style
    const BORDER_NONE = 'none';
    const BORDER_DASHDOT = 'dashDot';
    const BORDER_DASHDOTDOT = 'dashDotDot';
    const BORDER_DASHED = 'dashed';
    const BORDER_DOTTED = 'dotted';
    const BORDER_DOUBLE = 'double';
    const BORDER_HAIR = 'hair';
    const BORDER_MEDIUM = 'medium';
    const BORDER_MEDIUMDASHDOT = 'mediumDashDot';
    const BORDER_MEDIUMDASHDOTDOT = 'mediumDashDotDot';
    const BORDER_MEDIUMDASHED = 'mediumDashed';
    const BORDER_SLANTDASHDOT = 'slantDashDot';
    const BORDER_THICK = 'thick';
    const BORDER_THIN = 'thin';

    // Fill types
    const FILL_NONE = 'none';
    const FILL_SOLID = 'solid';
    const FILL_GRADIENT_LINEAR = 'linear';
    const FILL_GRADIENT_PATH = 'path';
    const FILL_PATTERN_DARKDOWN = 'darkDown';
    const FILL_PATTERN_DARKGRAY = 'darkGray';
    const FILL_PATTERN_DARKGRID = 'darkGrid';
    const FILL_PATTERN_DARKHORIZONTAL = 'darkHorizontal';
    const FILL_PATTERN_DARKTRELLIS = 'darkTrellis';
    const FILL_PATTERN_DARKUP = 'darkUp';
    const FILL_PATTERN_DARKVERTICAL = 'darkVertical';
    const FILL_PATTERN_GRAY0625 = 'gray0625';
    const FILL_PATTERN_GRAY125 = 'gray125';
    const FILL_PATTERN_LIGHTDOWN = 'lightDown';
    const FILL_PATTERN_LIGHTGRAY = 'lightGray';
    const FILL_PATTERN_LIGHTGRID = 'lightGrid';
    const FILL_PATTERN_LIGHTHORIZONTAL = 'lightHorizontal';
    const FILL_PATTERN_LIGHTTRELLIS = 'lightTrellis';
    const FILL_PATTERN_LIGHTUP = 'lightUp';
    const FILL_PATTERN_LIGHTVERTICAL = 'lightVertical';
    const FILL_PATTERN_MEDIUMGRAY = 'mediumGray';

    const FORMAT_XLS  = 'Xls';
    const FORMAT_XLSX = 'Xlsx';
    const FORMAT_CSV  = 'Csv';

    // Horizontal alignment styles
    const HORIZONTAL_GENERAL = 'general';
    const HORIZONTAL_LEFT = 'left';
    const HORIZONTAL_RIGHT = 'right';
    const HORIZONTAL_CENTER = 'center';
    const HORIZONTAL_CENTER_CONTINUOUS = 'centerContinuous';
    const HORIZONTAL_JUSTIFY = 'justify';
    const HORIZONTAL_FILL = 'fill';
    const HORIZONTAL_DISTRIBUTED = 'distributed'; // Excel2007 only

    // Vertical alignment styles
    const VERTICAL_BOTTOM = 'bottom';
    const VERTICAL_TOP = 'top';
    const VERTICAL_CENTER = 'center';
    const VERTICAL_JUSTIFY = 'justify';
    const VERTICAL_DISTRIBUTED = 'distributed'; // Excel2007 only

    // Data validation error styles
    const VALIDATION_STYLE_STOP = 'stop';
    const VALIDATION_STYLE_WARNING = 'warning';
    const VALIDATION_STYLE_INFORMATION = 'information';

    // Data validation types
    const VALIDATION_TYPE_NONE = 'none';
    const VALIDATION_TYPE_CUSTOM = 'custom';
    const VALIDATION_TYPE_DATE = 'date';
    const VALIDATION_TYPE_DECIMAL = 'decimal';
    const VALIDATION_TYPE_LIST = 'list';
    const VALIDATION_TYPE_TEXTLENGTH = 'textLength';
    const VALIDATION_TYPE_TIME = 'time';
    const VALIDATION_TYPE_WHOLE = 'whole';

    /** Protection styles */
    const PROTECTION_INHERIT = 'inherit';
    const PROTECTION_PROTECTED = 'protected';
    const PROTECTION_UNPROTECTED = 'unprotected';


    const NUM_FORMAT_NUMBER = '0';
    const NUM_FORMAT_NUMBER_00 = '0.00';
    const NUM_FORMAT_NUMBER_COMMA_SEPARATED1 = '#,##0.00';
    const NUM_FORMAT_NUMBER_COMMA_SEPARATED2 = '#,##0.00_-';

    const NUM_FORMAT_PERCENTAGE = '0%';
    const NUM_FORMAT_PERCENTAGE_00 = '0.00%';


    const STYLE_HEAD_PANEL = [
        'alignment' => [
            'horizontal' => Xls::HORIZONTAL_CENTER,
        ],
        'font' => [
            'bold' => true,
            'size' => 11
        ]
    ];
    const STYLE_REQUIRED = [
        'alignment' => [
            'horizontal' => Xls::HORIZONTAL_CENTER,
        ],
        'font' => [
            'bold'  => true,
            'color' => ['rgb' => 'FF0000'],
            'size'  => 11
        ]
    ];

    const STYLE_BOLD = [
        'font' => [
            'bold' => true,
        ]
    ];

    const STYLE_YELLOW = [
        'fill' => [
            'fillType' => Xls::FILL_SOLID,
            'type'     => Xls::FILL_SOLID,
            'color'    => ['rgb' => 'FFFF00']
        ]
    ];

    const STYLES = [
        'head-panel' => self::STYLE_HEAD_PANEL,
        'required'   => self::STYLE_REQUIRED,
        'bold'       => self::STYLE_BOLD,
    ];

    const SHEET_NAME_INVALID_CHARACTERS = ['*', ':', '/', '\\', '?', '[', ']'];

    /**
     * Verifies if PhpSpreadsheet library is available
     *
     * @return bool
     *
     * @deprecated PhpSpreadsheet should always be available
     */
    public static function hasPhpSpreadsheet()
    {
        return true;
    }


    /**
     * Creates empty spreadsheet object
     *
     * @param bool $allowFormula
     * @return Spreadsheet
     */
    public static function createSpreadsheet($allowFormula = null)
    {
        $locale = strtolower(App::$user->getLocale());

        try {
            Settings::setLocale($locale);
        } catch (\Exception $ex) {
            // do nothing
        }

        self::setValueBinder($allowFormula);

        return new Spreadsheet();
    }

    public static function setValueBinder($allowFormula = null): void
    {
        if ($allowFormula === null) {
            $allowFormula = !App::$config->escapeXlsFormulas;
        }

        Cell::setValueBinder(new ValueBinder($allowFormula));
    }


    /**
     * Extracts timestamp from date cell
     *
     * @param mixed $dateCell
     * @param string $timezone
     * @return int
     * @throws \Exception
     */
    public static function extractTimestamp($dateCell, $timezone = null)
    {
        return XlsDate::excelToTimestamp($dateCell, $timezone);
    }


    /**
     * Converts given $date to Xls date object
     *
     * @param string $date
     * @return bool|float
     */
    public static function toXlsDate($date)
    {
        $date = date('Y-m-d H:i:s', strtotime($date));

        return XlsDate::PHPToExcel($date);
    }

    /**
     * Converts given $time to Xls time
     *
     * @param string $time
     * @return bool|float
     */
    public static function toXlsTime($time)
    {
        $timeValue = DateTime::TIMEVALUE($time);
        if ($timeValue === Functions::VALUE()) {
            return false;
        }

        return $timeValue;
    }

    /**
     * Returns array object to change cell color
     * @param string $color
     * @return array
     */
    public static function getCellColor($color)
    {
        return [
            'fillType' => Xls::FILL_SOLID,
            'color' => ['rgb' => $color],
        ];
    }


    /**
     * Prepares header of worksheet
     *
     * @param Worksheet $sheet
     * @param array $labels
     * @param bool $fixed
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function prepareHeader($sheet, $labels, $fixed = false)
    {
        if ($fixed) {
            $sheet->freezePane('A2');
        }

        $cols = [];

        if (Filter::validateInt(key($labels))) {
            $index = 'A';
            foreach ($labels as $label) {
                $cols[$index++] = $label;
            }
        } else {
            $cols = $labels;
        }

        foreach ($cols as $colIndex => $colLabel) {
            $sheet->setCellValue($colIndex . '1', $colLabel);
            $sheet->getColumnDimension($colIndex)->setAutoSize(true);
            $sheet->getStyle($colIndex . '1')->applyFromArray(Output::getXlsHeadStyle());
        }
    }


    /**
     * Sets XLS protection
     *
     * @param object $sheet
     * @param string $range
     * @param string|null $password
     */
    public static function setProtection($sheet, $range = null, $password = null)
    {
        $sheet->getProtection()->setSheet(true);
        $sheet->getProtection()->setFormatColumns(false);

        if (App::$config->settings->passwordXSLX) {
            $sheet->getProtection()->setPassword(App::$config->settings->passwordXSLX);
        } else {
            $sheet->getProtection()->setPassword($password ?: 'Velis2016');
        }

        if ($range) {
            $sheet->getStyle($range)->getProtection()->setLocked(Xls::PROTECTION_UNPROTECTED);
        } else {
            $sheet->getProtection()->setSort(true);
            $sheet->getProtection()->setInsertRows(true);
            $sheet->getProtection()->setFormatCells(true);
        }
    }


    /**
     * Column index from string.
     *
     * @param string $string eg 'A'
     * @return int Column index (A = 1)
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     */
    public static function columnIndexFromString($string)
    {
        return Coordinate::columnIndexFromString($string);
    }


    /**
     * Creates XLS writer instance for specified $format
     *
     * @param Spreadsheet $xls
     * @param string $format
     * @return IWriter
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function createWriter($xls, $format = self::FORMAT_XLS)
    {
        return IOFactory::createWriter($xls, $format);
    }


    /**
     * Generates XLS fule contents
     *
     * @param object $source
     * @param string $format
     * @return string
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function generate($source, $format = null)
    {
        if ($source instanceof Spreadsheet) {
            if (!$format) {
                throw new InvalidArgumentException('XLS writer $format not specified');
            }
            $writer = self::createWriter($source, $format);
        } else {
            $writer = $source;
        }

        ob_start();
        $writer->save('php://output');
        $buffer = ob_get_contents();
        ob_end_clean();

        return $buffer;
    }


    /**
     * Generate date and time format based on XLS column type and user locale
     *
     * @param string $type
     * @return string
     */
    public static function getDateFormat($type)
    {
        $locale = App::$user->getLocale();
        if ($type == 'date') {
            if ($locale == 'en_US') {
                $format = 'mm/dd/yy';
            } else {
                $format = 'dd-mm-yy';
            }
        } else {
            if ($locale == 'en_US') {
                $format = 'mm/dd/yy h:mm:ss AM/PM';
            } elseif ($locale == 'en_GB') {
                $format = 'dd-mm-yy h:mm:ss AM/PM';
            } else {
                $format = 'dd-mm-yy h:mm:ss';
            }
        }

        return $format;
    }


    /**
     * Creates XLS writer instance for specified $format
     * @param string $format
     * @return IReader|void
     */
    public static function createReader($format = self::FORMAT_XLSX)
    {
        switch ($format) {
            case self::FORMAT_XLS:
                return new XlsReader();
            case self::FORMAT_XLSX:
                return new XlsxReader();
            case self::FORMAT_CSV:
                return new CsvReader();
        }
    }


    /**
     * Return cell value (calculated or text)
     *
     * @param Cell $cell
     * @return string
     */
    public static function getCellValue($cell = null)
    {
        if ($cell instanceof Cell) {
            if ($cell->getDataType() == DataType::TYPE_FORMULA) {
                return $cell->getOldCalculatedValue();
            } else {
                return $cell->getValue();
            }
        }

        return '';
    }


    /**
     * Creates empty worksheet object
     *
     * @return Worksheet
     */
    public static function createWorksheet()
    {
        return new Worksheet();
    }

    public static function getTextWidth(string $columnName): int
    {
        $font = new StyleFont();
        $font->applyFromArray([
            'font' => 'Arial',
            'bold' => true,
        ]);

        $colWidth = Drawing::pixelsToCellDimension(
            Font::getTextWidthPixelsApprox($columnName, $font),
            $font
        );

        if ($colWidth < self::DEFAULT_COLUMN_WIDTH) {
            $colWidth = self::DEFAULT_COLUMN_WIDTH;
        }

        return $colWidth;
    }

    public static function isFormulaChar(string $string): string
    {
        $char = mb_strlen($string) > 1 ? mb_substr($string, 0, 1) : $string;

        return in_array($char, ['+', '-', '*', '/', '%', '^', '=', '>', '<', '&']);
    }
}
