<?php

namespace TablePress\PhpOffice\PhpSpreadsheet\Calculation;

use TablePress\PhpOffice\PhpSpreadsheet\Cell\Cell;
use TablePress\PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use TablePress\PhpOffice\PhpSpreadsheet\Shared\Date;
use TablePress\PhpOffice\PhpSpreadsheet\Shared\StringHelper;

class Functions
{
	const PRECISION = 8.88E-016;

	/**
	 * 2 / PI.
	 */
	const M_2DIVPI = 0.63661977236758134307553505349006;

	const COMPATIBILITY_EXCEL = 'Excel';
	const COMPATIBILITY_GNUMERIC = 'Gnumeric';
	const COMPATIBILITY_OPENOFFICE = 'OpenOfficeCalc';

	/** Use of RETURNDATE_PHP_NUMERIC is discouraged - not 32-bit Y2038-safe, no timezone. */
	const RETURNDATE_PHP_NUMERIC = 'P';
	/** Use of RETURNDATE_UNIX_TIMESTAMP is discouraged - not 32-bit Y2038-safe, no timezone. */
	const RETURNDATE_UNIX_TIMESTAMP = 'P';
	const RETURNDATE_PHP_OBJECT = 'O';
	const RETURNDATE_PHP_DATETIME_OBJECT = 'O';
	const RETURNDATE_EXCEL = 'E';

	public const NOT_YET_IMPLEMENTED = '#Not Yet Implemented';

	/**
	 * Compatibility mode to use for error checking and responses.
	 */
	protected static string $compatibilityMode = self::COMPATIBILITY_EXCEL;

	/**
	 * Data Type to use when returning date values.
	 */
	protected static string $returnDateType = self::RETURNDATE_EXCEL;

	/**
	 * Set the Compatibility Mode.
	 *
	 * @param string $compatibilityMode Compatibility Mode
	 *                                  Permitted values are:
	 *                                      Functions::COMPATIBILITY_EXCEL        'Excel'
	 *                                      Functions::COMPATIBILITY_GNUMERIC     'Gnumeric'
	 *                                      Functions::COMPATIBILITY_OPENOFFICE   'OpenOfficeCalc'
	 *
	 * @return bool (Success or Failure)
	 */
	public static function setCompatibilityMode(string $compatibilityMode): bool
	{
		if (
			($compatibilityMode == self::COMPATIBILITY_EXCEL)
			|| ($compatibilityMode == self::COMPATIBILITY_GNUMERIC)
			|| ($compatibilityMode == self::COMPATIBILITY_OPENOFFICE)
		) {
			self::$compatibilityMode = $compatibilityMode;

			return true;
		}

		return false;
	}

	/**
	 * Return the current Compatibility Mode.
	 *
	 * @return string Compatibility Mode
	 *                Possible Return values are:
	 *                    Functions::COMPATIBILITY_EXCEL        'Excel'
	 *                    Functions::COMPATIBILITY_GNUMERIC     'Gnumeric'
	 *                    Functions::COMPATIBILITY_OPENOFFICE   'OpenOfficeCalc'
	 */
	public static function getCompatibilityMode(): string
	{
		return self::$compatibilityMode;
	}

	/**
	 * Set the Return Date Format used by functions that return a date/time (Excel, PHP Serialized Numeric or PHP DateTime Object).
	 *
	 * @param string $returnDateType Return Date Format
	 *                               Permitted values are:
	 *                                   Functions::RETURNDATE_UNIX_TIMESTAMP       'P'
	 *                                   Functions::RETURNDATE_PHP_DATETIME_OBJECT  'O'
	 *                                   Functions::RETURNDATE_EXCEL                'E'
	 *
	 * @return bool Success or failure
	 */
	public static function setReturnDateType(string $returnDateType): bool
	{
		if (
			($returnDateType == self::RETURNDATE_UNIX_TIMESTAMP)
			|| ($returnDateType == self::RETURNDATE_PHP_DATETIME_OBJECT)
			|| ($returnDateType == self::RETURNDATE_EXCEL)
		) {
			self::$returnDateType = $returnDateType;

			return true;
		}

		return false;
	}

	/**
	 * Return the current Return Date Format for functions that return a date/time (Excel, PHP Serialized Numeric or PHP Object).
	 *
	 * @return string Return Date Format
	 *                Possible Return values are:
	 *                    Functions::RETURNDATE_UNIX_TIMESTAMP         'P'
	 *                    Functions::RETURNDATE_PHP_DATETIME_OBJECT    'O'
	 *                    Functions::RETURNDATE_EXCEL            '     'E'
	 */
	public static function getReturnDateType(): string
	{
		return self::$returnDateType;
	}

	/**
	 * DUMMY.
	 *
	 * @return string #Not Yet Implemented
	 */
	public static function DUMMY(): string
	{
		return self::NOT_YET_IMPLEMENTED;
	}

	/**
				 * @param mixed $idx
				 */
				public static function isMatrixValue($idx): bool
	{
		$idx = StringHelper::convertToString($idx);

		return (substr_count($idx, '.') <= 1) || (preg_match('/\.[A-Z]/', $idx) > 0);
	}

	/**
				 * @param mixed $idx
				 */
				public static function isValue($idx): bool
	{
		$idx = StringHelper::convertToString($idx);

		return substr_count($idx, '.') === 0;
	}

	/**
				 * @param mixed $idx
				 */
				public static function isCellValue($idx): bool
	{
		$idx = StringHelper::convertToString($idx);

		return substr_count($idx, '.') > 1;
	}

	/**
				 * @param mixed $condition
				 */
				public static function ifCondition($condition): string
	{
		$condition = self::flattenSingleValue($condition);

		if ($condition === '' || $condition === null) {
			return '=""';
		}
		if (!is_string($condition) || !in_array($condition[0], ['>', '<', '='], true)) {
			$condition = self::operandSpecialHandling($condition);
			if (is_bool($condition)) {
				return '=' . ($condition ? 'TRUE' : 'FALSE');
			}
			if (!is_numeric($condition)) {
				if ($condition !== '""') { // Not an empty string
					// Escape any quotes in the string value
					$condition = (string) preg_replace('/"/ui', '""', $condition);
				}
				$condition = Calculation::wrapResult(strtoupper($condition));
			}

			return str_replace('""""', '""', '=' . StringHelper::convertToString($condition));
		}
		$operator = $operand = '';
		if (1 === preg_match('/(=|<[>=]?|>=?)(.*)/', $condition, $matches)) {
			[, $operator, $operand] = $matches;
		}

		$operand = (string) self::operandSpecialHandling($operand);
		if (is_numeric(trim($operand, '"'))) {
			$operand = trim($operand, '"');
		} elseif (!is_numeric($operand) && $operand !== 'FALSE' && $operand !== 'TRUE') {
			$operand = str_replace('"', '""', $operand);
			$operand = Calculation::wrapResult(strtoupper($operand));
			$operand = StringHelper::convertToString($operand);
		}

		return str_replace('""""', '""', $operator . $operand);
	}

	/**
				 * @return bool|float|int|string
				 * @param mixed $operand
				 */
				private static function operandSpecialHandling($operand)
	{
		if (is_numeric($operand) || is_bool($operand)) {
			return $operand;
		}
		$operand = StringHelper::convertToString($operand);
		if (strtoupper($operand) === Calculation::getTRUE() || strtoupper($operand) === Calculation::getFALSE()) {
			return strtoupper($operand);
		}

		// Check for percentage
		if (preg_match('/^\-?\d*\.?\d*\s?\%$/', $operand)) {
			return ((float) rtrim($operand, '%')) / 100;
		}

		// Check for dates
		if (($dateValueOperand = Date::stringToExcel($operand)) !== false) {
			return $dateValueOperand;
		}

		return $operand;
	}

	/**
	 * Convert a multi-dimensional array to a simple 1-dimensional array.
	 *
	 * @param mixed $array Array to be flattened
	 *
	 * @return array<mixed> Flattened array
	 */
	public static function flattenArray($array): array
	{
		if (!is_array($array)) {
			return (array) $array;
		}

		$flattened = [];
		$stack = array_values($array);

		while (!empty($stack)) {
			$value = array_shift($stack);

			if (is_array($value)) {
				array_unshift($stack, ...array_values($value));
			} else {
				$flattened[] = $value;
			}
		}

		return $flattened;
	}

	/**
	 * Convert a multi-dimensional array to a simple 1-dimensional array.
	 * Same as above but argument is specified in ... format.
	 *
	 * @param mixed $array Array to be flattened
	 *
	 * @return array<mixed> Flattened array
	 */
	public static function flattenArray2(...$array): array
	{
		$flattened = [];
		$stack = array_values($array);

		while (!empty($stack)) {
			$value = array_shift($stack);

			if (is_array($value)) {
				array_unshift($stack, ...array_values($value));
			} else {
				$flattened[] = $value;
			}
		}

		return $flattened;
	}

	/**
				 * @param mixed $value
				 * @return mixed
				 */
				public static function scalar($value)
	{
		if (!is_array($value)) {
			return $value;
		}

		do {
			$value = array_pop($value);
		} while (is_array($value));

		return $value;
	}

	/**
	 * Convert a multi-dimensional array to a simple 1-dimensional array, but retain an element of indexing.
	 *
	 * @param array|mixed $array Array to be flattened
	 *
	 * @return array<mixed> Flattened array
	 */
	public static function flattenArrayIndexed($array): array
	{
		if (!is_array($array)) {
			return (array) $array;
		}

		$arrayValues = [];
		foreach ($array as $k1 => $value) {
			if (is_array($value)) {
				foreach ($value as $k2 => $val) {
					if (is_array($val)) {
						foreach ($val as $k3 => $v) {
							$arrayValues[$k1 . '.' . $k2 . '.' . $k3] = $v;
						}
					} else {
						$arrayValues[$k1 . '.' . $k2] = $val;
					}
				}
			} else {
				$arrayValues[$k1] = $value;
			}
		}

		return $arrayValues;
	}

	/**
				 * Convert an array to a single scalar value by extracting the first element.
				 *
				 * @param mixed $value Array or scalar value
				 * @return mixed
				 */
				public static function flattenSingleValue($value)
	{
		while (is_array($value)) {
			$value = array_shift($value);
		}

		return $value;
	}

	public static function expandDefinedName(string $coordinate, Cell $cell): string
	{
		$worksheet = $cell->getWorksheet();
		$spreadsheet = $worksheet->getParentOrThrow();
		// Uppercase coordinate
		$pCoordinatex = strtoupper($coordinate);
		// Eliminate leading equal sign
		$pCoordinatex = (string) preg_replace('/^=/', '', $pCoordinatex);
		$defined = $spreadsheet->getDefinedName($pCoordinatex, $worksheet);
		if ($defined !== null) {
			$worksheet2 = $defined->getWorkSheet();
			if (!$defined->isFormula() && $worksheet2 !== null) {
				$coordinate = "'" . $worksheet2->getTitle() . "'!"
					. (string) preg_replace('/^=/', '', str_replace('$', '', $defined->getValue()));
			}
		}

		return $coordinate;
	}

	public static function trimTrailingRange(string $coordinate): string
	{
		return (string) preg_replace('/:[\w\$]+$/', '', $coordinate);
	}

	public static function trimSheetFromCellReference(string $coordinate): string
	{
		if (str_contains($coordinate, '!')) {
			$coordinate = (string) substr($coordinate, strrpos($coordinate, '!') + 1);
		}

		return $coordinate;
	}

	/** @param mixed[] $array */
	public static function convertArrayToCellRange(array $array): string
	{
		$retVal = '';
		$lastRow = $lastColumn = $firstRow = $firstColumn = 0;
		foreach ($array as $rowkey => $row) {
			if (!is_array($row) || !is_int($rowkey) || $rowkey < 1) {
				$firstRow = 0;

				break;
			}
			if ($firstRow > $rowkey || $firstRow === 0) {
				$firstRow = $rowkey;
			}
			if ($lastRow < $rowkey) {
				$lastRow = $rowkey;
			}
			foreach ($row as $colkey => $cellValue) {
				if (!preg_match('/^[A-Z]{1,3}$/', $colkey)) {
					$firstRow = 0;

					break 2;
				}
				$column = Coordinate::columnIndexFromString($colkey);
				if ($firstColumn > $column || $firstColumn === 0) {
					$firstColumn = $column;
				}
				if ($lastColumn < $column) {
					$lastColumn = $column;
				}
			}
		}
		if ($firstRow > 0 && $firstColumn > 0 && ($firstRow !== $lastRow || $firstColumn !== $lastColumn)) {
			$retVal = Coordinate::stringFromColumnIndex($firstColumn)
				. $firstRow
				. ':'
				. Coordinate::stringFromColumnIndex($lastColumn)
				. $lastRow;
		}

		return $retVal;
	}
}
