<?php

// TODO: [notImportant] la unele campuri sa pot sa injectez valori "fake" - de ex. la campuri de data, YM, Y; s-ar putea face dupa pasul populate_insertMainContent, si s-ar putea adauga in campurile din zona "data" ca valori NULL (doar ca sa existe inregistrari in pivot)
// TODO: [notImportant] tot aici sa pot sa pot sa "fortez copierea listei de valori" - de ex. dr grupez (pe sursa "facturi") dupa an si tip client (pe aceeasi zona, fie ambele pe rows fie pe cols) sa forteze adaugarea tuturor tipurilor de clienti pe toti anii (chiar daca in unii ani nu am facturat pe acel tip de client) - asta poate fi util mai departe in grafice?; ca solutie ar putea sa extraga lista completa de valori unice si sa o injecteze apoi pe fiecare camp unde lipseste
// TODO: [notImportant] display format based on type; ex.  for prices you can round the totals eg. 1245 => 1.2k; vezi mai jos checkDisplayFormat() pt validare dar si implementarea solutiei

// TODO: progress manager si pe generare continut?

namespace App\Reports\PivotTable;

class Manager
{

	/**
	 * Object managing the DB table that stores the pivot content.
	 * @var \App\Utils\Db\ManageTable
	 */
	private \App\Utils\Db\ManageTable $pivotTableManager;

	/**
	 * The full list of fields configs (similar to the ones from reports but can be manually set for a
	 * general purpose approach).
	 * @var array
	 */
	private array $fieldsCfg = [];

	/**
	 * The list of rows area fields and their attributes.
	 * @var array
	 */
	private array $rowsFields = [];

	/**
	 * The list of cols area fields and their attributes.
	 * @var array
	 */
	private array $colsFields = [];

	/**
	 * The list of data area fields and their attributes.
	 * @var array
	 */
	private array $dataFields = [];

	/**
	 * Filters applied when copying the info from the report table to the pivot table (when doiing the main GROUP BY)
	 * @var array
	 */
	private array $filters = [];

	/**
	 * Rows totals (tree array).
	 * @var array
	 */
	private array $rowsTotals = [];

	/**
	 * Cols totals (tree array).
	 * @var array
	 */
	private array $colsTotals = [];

	/**
	 * Grand totals (includes grand totals for all data fields).
	 * @var array
	 */
	private array $grandTotals = [];

	/**
	 * Rows totals (linearized array).
	 * @var array
	 */
	private array $rowsKeysLinear = [];

	/**
	 * Cols totals (linearized array).
	 * @var array
	 */
	private array $colsKeysLinear = [];

	/**
	 * All data values (linearized, 2 dim array).
	 * @var array
	 */
	private array $dataValues = [];


	/**
	 * Stores which totals are displayed and also stores a different name for them.
	 * @var array
	 */
	private array $displayTotals = [];

	/**
	 * Internal key that stores the "group other records".
	 * @var string
	 */
	private const GROUP_RECORDS_INTERNAL_KEY = '***LIMIT_OTHERS_KEY***';

	/**
	 * The max number of cells allowed for ROWS area.
	 * @var integer
	 */
	private const MAX_SIZE_ROWS = 10000;

	/**
	 * The max number of cells allowed for COLS area.
	 * @var integer
	 */
	private const MAX_SIZE_COLS = 1000;

	/**
	 * The max number of cells allowed for DATA area.
	 * @var integer
	 */
	private const MAX_SIZE_DATA = 100000;


	/**
	 * Includes the object for managing the DB table - the table must be already populated.
	 * @param \App\Utils\Db\ManageTable $pivotTableManager
	 */
	public function __construct(\App\Utils\Db\ManageTable $pivotTableManager)
	{
		$this->pivotTableManager = $pivotTableManager;
	}


	/**
	 * Imports the fields configs from columns manager object.
	 * @param \App\Reports\Sources\ColumnsManagerAbstract $columnsMng
	 */
	public function importFieldsCfgFromReportSource(\App\Reports\Sources\ColumnsManagerAbstract $columnsMng) :void
	{
		if (!$columnsMng->dependenciesAreIncluded()) {
			throw new Exception('The dependencis are not included - run dependenciesAreIncluded() on columns manager before importing the columns list.');
		}
		// add std columns and custom columns from the manager object
		foreach ($columnsMng->getStdColumns() as $field=>$details) {
			$this->addFieldCfg($field, $details['type'], $details['dbDef']);
		}
		foreach ($columnsMng->getCustomColumns() as $field=>$details) {
			$this->addFieldCfg($field, $details['type'], $details['dbDef']);
		}
	}



	/**
	 * Adds a new field config.
	 * @param string $field
	 * @param string $type
	 */
	private function addFieldCfg(string $field, string $type, string $dbDef) :void
	{

		// check field code
		if (!strlen($field)) {
			throw new Exception('Empty field cannot be added to pivot config fields.');
		}

		// check the types
		$allowedTypes = [
			'string', 'varchar10', 'varchar25', 'varchar50', 'varchar100', 'varchar150', 'varchar200',
			'varchar250', 'varchar300', 'varchar400', 'varchar500',
			'numeric', 'int', 'price', 'percent', 'yearMonth', 'year', 'date', 'datetime', 'boolean'
		];
		if (!in_array($type, $allowedTypes)) {
			throw new Exception('Invalid type for pivot fields - '.$type);
		}

		if (!strlen($dbDef)) {
			throw new Exception('Empty field db definition cannot be added to pivot config fields.');
		}

		// inject the new config column
		$this->fieldsCfg[$field] = ['type'=>$type, 'dbDef'=>$dbDef];
	}

	/**
	 * Adds a new field-aggFunction in the data area.
	 * @param string $field
	 * @param string $aggFunction
	 * @param string $displayFormat
	 */
	public function addDataField(string $field, string $aggFunction, ?string $displayFormat=null ) :void
	{

		// check the actual field
		if (!$field or !key_exists($field, $this->fieldsCfg)) {
			throw new Exception('Data field not set/valid.');
		}

		// check aggregate function
		$this->checkAggregateFunction($aggFunction, $this->fieldsCfg[$field]['type']);

		// check duplicate entry
		$storedKey = $field.'|'.$aggFunction;
		if (key_exists($storedKey, $this->dataFields)) {
			throw new Exception('This field with the same aggregate function is already added in data fields.');
		}

		if ($displayFormat!==null) {
			$this->checkDisplayFormat($this->fieldsCfg[$field]['type'], $displayFormat);
		}

		// store data field internally
		$this->dataFields[$storedKey] = [
			'field'			=> $field,
			'aggFunction'	=> $aggFunction,
			'displayFormat'	=> $displayFormat,
		];

	}

	/**
	 * Adds a new field in the rows area.
	 * @param string $field
	 * @param string $orderDataFieldName
	 * @param string $orderDataFieldAggFunction
	 * @param string $orderDir
	 * @param int $limitGroupSize
	 * @param string $displayFormat
	 */
	public function addRowsField(string $field, ?string $orderDataFieldName, ?string $orderDataFieldAggFunction, string $orderDir, ?int $limitGroupSize=null, ?bool $includeOthers=null, ?string $displayFormat=null ) :void
	{

		// check input
		$this->checkRowsAndColsField($field, $orderDataFieldName, $orderDataFieldAggFunction, $orderDir, $limitGroupSize, $includeOthers, $displayFormat);

		// adds the field
		$this->rowsFields[$field] = [
			'field'						=> $field,
			'orderDataFieldName'		=> $orderDataFieldName,
			'orderDataFieldAggFunction'	=> $orderDataFieldAggFunction,
			'orderDir'					=> $orderDir,
			'limitGroupSize'			=> $limitGroupSize,
			'includeOthers'				=> $includeOthers,
			'displayFormat'				=> $displayFormat,
		];
	}


	/**
	 *  Adds a new field in the cols area.
	 * @param string $field
	 * @param string $orderDataFieldName
	 * @param string $orderDataFieldAggFunction
	 * @param string $orderDir
	 * @param int $limitGroupSize
	 * @param string $displayFormat
	 */
	public function addColsField(string $field, ?string $orderDataFieldName, ?string $orderDataFieldAggFunction, string $orderDir, ?int $limitGroupSize=null, ?bool $includeOthers=null, ?string $displayFormat=null )
	{

		// check input
		$this->checkRowsAndColsField($field, $orderDataFieldName, $orderDataFieldAggFunction, $orderDir, $limitGroupSize, $includeOthers, $displayFormat);

		// adds the field
		$this->colsFields[$field] = [
			'field'						=> $field,
			'orderDataFieldName'		=> $orderDataFieldName,
			'orderDataFieldAggFunction'	=> $orderDataFieldAggFunction,
			'orderDir'					=> $orderDir,
			'limitGroupSize'			=> $limitGroupSize,
			'includeOthers'				=> $includeOthers,
			'displayFormat'				=> $displayFormat,
		];



	}

	/**
	 * Checks the adding of a new field for rows or cols area.
	 * @param string $field
	 * @param string $orderDataFieldName
	 * @param string $orderDataFieldAggFunction
	 * @param string $orderDir
	 * @param int $limitGroupSize
	 * @param string $displayFormat
	 */
	private function checkRowsAndColsField(string $field, ?string $orderDataFieldName, ?string $orderDataFieldAggFunction, string $orderDir, ?int $limitGroupSize, ?bool $includeOthers, ?string $displayFormat )
	{

		// check field - it is mandatory
		if (!$field or !key_exists($field, $this->fieldsCfg)) {
			throw new Exception('Rows/cols field not set or invalid: '.$field);
		}
		// cannot select TEXT fields
		if (strtoupper( substr($this->fieldsCfg[$field]['dbDef'], 0, 4) ) == 'TEXT') {
			throw new Exception(sprintf('Cannot choose the field `%s` for rows/cols because it is defined as TEXT in the db.: ', $field));
		}

		// check that the field is not already set as another rows/cols field
		if (key_exists($field, $this->rowsFields)) {
			throw new Exception(sprintf('The field `%s` is already defined in the rows area.', $field));
		}
		if (key_exists($field, $this->colsFields)) {
			throw new Exception(sprintf('The field `%s` is already defined in the cols area.', $field));
		}

		// field name must be set
		if ( ($orderDataFieldName and !$orderDataFieldAggFunction) or (!$orderDataFieldName and $orderDataFieldAggFunction) ) {
			throw new Exception('Set both data field name and data field aggregate function when ordering by data field.');
		}

		// order direction, is always set
		$this->checkOrderDirection($orderDir);

		if ($orderDataFieldName) {
			if (!key_exists($orderDataFieldName.'|'.$orderDataFieldAggFunction, $this->dataFields)) {
				throw new Exception('When setting the order by a data field and its aggregate function, make sure that field and its aggregate function are already set as data-field.');
			}
		}

		// check group limit
		if ($limitGroupSize!==null) {
			if ($limitGroupSize<=0 or $limitGroupSize>1000) { // this is a synthetic validation - its purpose is to avoid human error
				throw new Exception('When setting up the limiting of a rows/cols field while grouping you must set a strict positive value that is less or equal to 1000.');
			}
			if ($includeOthers===null) {
				throw new Exception('When setting up the limiting of a rows/cols field while grouping you must set the `include others` flag also.');
			}
		} else {
			if ($includeOthers!==null) {
				throw new Exception('When no limiting of a rows/cols field while grouping you must not set the `include others` flag.');
			}
		}

		// check display format
		if ($displayFormat!==null) {
			$this->checkDisplayFormat($this->fieldsCfg[$field], $displayFormat);
		}

	}


	/**
	 * Checks that the aggregate function is valid (by searching in a whitelist).
	 * @param string $function
	 */
	private function checkAggregateFunction(string $function, string $fieldType) :void
	{

		// check aggregate function
		if (!in_array($function, ['CNT', 'DST', 'SUM', 'AVG', 'MIN', 'MAX'])) {
			throw new Exception('Invalid aggregate function set - '.$function);
		}

		// check agg function depending on field type
		if ($function=='SUM' or $function=='AVG' or $function=='MIN' or $function=='MAX') { // allowed only for some types
			$allowedFieldTypes = [ 'numeric', 'int', 'price', 'percent' ];
			if (!in_array($fieldType, $allowedFieldTypes)) {
				throw new Exception(sprintf('You cannot use the function `%s` on field type `%s`.', $function, $fieldType));
			}
		}

	}


	/**
	 * Checks that the order direction is valid (ASC or DESC).
	 * @param string $function
	 */
	private function checkOrderDirection(string $orderDirection) :void
	{
		if (!in_array($orderDirection, ['ASC', 'DESC'])) {
			throw new Exception('Invalid order direction set: '.$orderDirection);
		}
	}

	/**
	 * Checks that a certain field type can support the associated displayFormat
	 * @param string $fieldType
	 * @param string $displayFormat
	 */
	private function checkDisplayFormat(string $fieldType, string $displayFormat) :void
	{
		throw new \Exception('Not yet implemented!');
	}


	/**
	 * Collects filters to be applied when popualting the table.
	 * @param string $column
	 * @param string $operation
	 * @param mixed $value
	 */
	public function addFilter(string $column, string $operation, mixed $value=null) :void
	{
		// CHECK INPUT ----------------------------------------------------------------------------
		// check the filtering column
		if (!$column or !key_exists($column, $this->fieldsCfg)) {
			throw new Exception('Invalid/unset column `%s` used for filtering a pivot table.');
		}
		// check operation / value
		switch ($operation) {
			case '=': case '!=': case 'LIKE': case 'NOT LIKE': case '>': case '>=': case '<': case '<=':
				if (!is_scalar($value)) {
					throw new Exception(sprintf('You must set a scalar value for pivot table filter on column `%s`, operation `%s`.', $column, $operation));
				}
			break;
			case 'IS NULL': case 'IS NOT NULL':
				if ($value!==null) {
					throw new Exception(sprintf('You must set the value to NULL on pivot table filter on column `%s`, operation `%s`.', $column, $operation));
				}
			break;
			case 'IN': case 'NOT IN':
				if (!is_array($value) or !count($value)) {
					throw new Exception(sprintf('You must set the value to array with at least one element for pivot table filter on column `%s`, operation `%s`.', $column, $operation));
				}
			break;
			case 'BETWEEN':
				if (!is_array($value) or count($value)!=2) {
					throw new Exception(sprintf('You must set the value to an array with 2 elements for pivot table filter on column `%s`, operation `%s`.', $column, $operation));
				}
			break;
			default:
				throw new Exception('Invalid/unset operation `%s` used for filtering a pivot table.');
		}

		// STORE THE FILTER INTERNALLY ------------------------------------------------------------
		$this->filters[] = [
			'column'	=> $column,
			'operation'	=> $operation,
			'value'		=> $value
		];
	}

	/**
	 * Sets a total that will be displayed in the final pivot.
	 * @param ?string $field - besides the rows/cols fields it can be set to 'ROWS_GRAND_TOTALS' or 'COLS_GRAND_TOTALS'
	 * @param string $dataField
	 * @param string $aggFunction
	 * @param ?string $totalName
	 */
	public function addDisplayedTotal(string $field, string $dataField, string $aggFunction, ?string $name=null) :void
	{

		// CHECK INPUT ----------------------------------------------------------------------------
		// field must exist in rows/cols or be set as some constant values
		if ( !in_array($field, ['ROWS_GRAND_TOTALS', 'COLS_GRAND_TOTALS']) ) {
			if (!key_exists($field, $this->rowsFields) and !key_exists($field, $this->colsFields)) {
				throw new Exception( sprintf('Invalid field `%s` added - it must be associated to either rows/cols area of the pivot.', $field) );
			}
		}
		// check field and its aggFunction
		$storedKey = $dataField.'|'.$aggFunction;
		if (!key_exists($storedKey, $this->dataFields)) {
			throw new Exception( sprintf('Invalid data field `%s` and/or aggregate function `%s` added for the pivot.', $dataField, $aggFunction) );
		}
		if (!in_array($aggFunction, ['CNT', 'DST', 'SUM'])) {
			throw new Exception( sprintf('The aggregate function `%s` cannot be selected for totals (used on field `%s`).', $aggFunction, $dataField) );
		}

		// STORE THE TOTAL DETAILS ----------------------------------------------------------------
		$this->displayTotals[] = [
			'field'			=> $field,
			'dataField'		=> $dataField,
			'aggFunction'	=> $aggFunction,
			'name'			=> $name,
		];
	}


	/**
	 * Populates all pivot table info (data cells, totals, etc)
	 * @param string $sourceTableName
	 */
	public function populate(string $sourceTableName)
	{

		// CHECK INPUTS ///////////////////////////////////////////////////////////////////////////
		if (!$this->dataFields) {
			throw new Exception('You cannot generate the pivot content as there are no data-fields added.');
		}

		if (!$this->colsFields and !$this->rowsFields) {
			throw new Exception('You cannot generate the pivot content as there must be at least one rows/cols field added.');
		}

		// CREATE THE DB TABLE ////////////////////////////////////////////////////////////////////
		$this->populate_createPivotDbTable();

		// CREATE MAIN TABLE CONTENT //////////////////////////////////////////////////////////////
		$this->populate_insertMainContent($sourceTableName);

		// COMPUTE ROWS/COLS TOTALS ///////////////////////////////////////////////////////////////
		$this->populate_determineOrderedFieldsAndTotals();

		// COMPUTE DATA VALUES ////////////////////////////////////////////////////////////////////
		$this->populate_determineDataValues();

	}

	/**
	 * Creates the pivot DB table.
	 */
	private function populate_createPivotDbTable() :void
	{

		foreach ($this->rowsFields as $fieldDet) {
			$this->pivotTableManager->addColumnByDef($fieldDet['field'], $this->fieldsCfg[$fieldDet['field']]['dbDef']);
		}
		foreach ($this->colsFields as $fieldDet) {
			$this->pivotTableManager->addColumnByDef($fieldDet['field'], $this->fieldsCfg[$fieldDet['field']]['dbDef']);
		}
		foreach ($this->dataFields as $fieldDet) {
			$this->pivotTableManager->addColumnByDef($fieldDet['field'].'_'.$fieldDet['aggFunction'], 'DECIMAL(16,3) NOT NULL DEFAULT 0');
		}

		$this->pivotTableManager->createTable();

	}


	/**
	 * Returns the aggregate function SQL part based on column and agg function.
	 * @param string $field
	 * @param string $aggFunction
	 * @return string
	 */
	private function getAggregateFunctionSQL(string $field, string $aggFunction) :string
	{
		switch ($aggFunction) {
			case 'CNT': return "COUNT(`{$field}`)"; break;
			case 'DST': return "COUNT(DISTINCT(`{$field}`))"; break;
			case 'SUM': return "SUM(`{$field}`)"; break;
			case 'AVG': return "AVG(`{$field}`)"; break;
			case 'MIN': return "MIN(`{$field}`)"; break;
			case 'MAX': return "MAX(`{$field}`)"; break;
			default:
				throw new \Exception("Invalid aggregate function on field `{$field}`: {$aggFunction}");
		}
	}

	/**
	 * Adds the main content in the pivot DB table.
	 * @param string $sourceTableName
	 */
	private function populate_insertMainContent(string $sourceTableName) :void
	{

		// PREPARE FIELDS TO BE USED IN THE SQLs --------------------------------------------------
		$dimFields = [];
		$indexRowsFields = $indexColsFields = [];
		foreach ($this->rowsFields as $fieldDet) {
			$dimFields[] = $fieldDet['field'];
			// collect indexes (sized)
			$indexSize = $this->populate_insertMainContent_getIndexSizePerColumn($fieldDet['field']);
			if ($indexSize) {
				$indexRowsFields[] = $fieldDet['field']."({$indexSize})";
			} else {
				$indexRowsFields[] = $fieldDet['field'];
			}
		}
		foreach ($this->colsFields as $fieldDet) {
			$dimFields[] = $fieldDet['field'];
			// collect indexes (sized)
			$indexSize = $this->populate_insertMainContent_getIndexSizePerColumn($fieldDet['field']);
			if ($indexSize) {
				$indexColsFields[] = $fieldDet['field']."({$indexSize})";
			} else {
				$indexColsFields[] = $fieldDet['field'];
			}
		}

		$dataFields = [];
		foreach ($this->dataFields as $fieldDet) {
			$fieldName = $fieldDet['field'] . '_' . $fieldDet['aggFunction'];
			$dataFields[$fieldName] = $this->getAggregateFunctionSQL($fieldDet['field'], $fieldDet['aggFunction']);
		}

		// DB inits
		$pivotTableName = $this->pivotTableManager->getTableName();
		$db = \DB::getInstance();

		// PREPARE FILTERS ------------------------------------------------------------------------
		$filters = $this->populate_insertMainContent_getFiltersSQL();
		$bindVars = $filters['vars'];
		$filtersSQL = $filters['sql'];

		// EXECUTE THE SQL ------------------------------------------------------------------------
		$insertGroupedRecordsSQL = "
			INSERT INTO `{$pivotTableName}` (" . implode(', ', array_merge($dimFields, array_keys($dataFields))) . ")
			SELECT " . implode(', ', array_merge($dimFields, array_values($dataFields))) . "
				FROM `$sourceTableName`
				WHERE {$filtersSQL}
				GROUP BY " . implode(', ', $dimFields);

		$db->Execute($insertGroupedRecordsSQL, $bindVars);
		if ($db->ErrorNo) {
			throw new \DBException('Invalid SQL for copying grouped records when generating pivot table.');
		}

		// ADD INDEX ON ROWS AND COLS FIELDS ------------------------------------------------------
		$addRowsIndexSQL = "ALTER TABLE `{$pivotTableName}` ADD INDEX `rowsIndex` (" . implode(', ', $indexRowsFields) . ")";
		$db->Execute($addRowsIndexSQL);
		if ($db->ErrorNo) {
			throw new \DBException('Invalid SQL for adding rows index on pivot table.');
		}

		$addColsIndexSQL = "ALTER TABLE `{$pivotTableName}` ADD INDEX `colsIndex` (" . implode(', ', $indexColsFields) . ")";
		$db->Execute($addColsIndexSQL);
		if ($db->ErrorNo) {
			throw new \DBException('Invalid SQL for adding cols index on pivot table.');
		}

	}


	/**
	 * Analyzes internal filters and generates the SQL part to be used in the main SQL for importing data.
	 * @return array
	 */
	private function populate_insertMainContent_getFiltersSQL() :array
	{
		$filtersSqlList = [];
		$bindVars = [];
		foreach ($this->filters as $filter)
		{
			$filterSQL = '';
			switch ($filter['operation']) {
				case '=': case '!=': case 'LIKE': case 'NOT LIKE': case '>': case '>=': case '<': case '<=':
					$filterSQL = "`{$filter['column']}` {$filter['operation']} ?";
					$bindVars[] = $filter['value'];
				break;
				case 'IS NULL': case 'IS NOT NULL':
					$filterSQL = "`{$filter['column']}` {$filter['operation']}";
				break;
				case 'IN': case 'NOT IN':
					$filterSQL = "`{$filter['column']}` {$filter['operation']} (" . implode(', ', array_fill(0, count($filter['value']), '?')) . ")";
					$bindVars = array_merge($bindVars, $filter['value']);
				break;
				case 'BETWEEN':
					$filterSQL = "`{$filter['column']}` BETWEEN ? AND ?";
					$bindVars[] = min($filter['value']);
					$bindVars[] = max($filter['value']);
				break;
			}

			$filtersSqlList[] = '(' . $filterSQL . ')';

		}
		return [
			'sql'	=> $filtersSqlList ? implode(' AND ', $filtersSqlList) : '1',
			'vars'	=> $bindVars
		];

	}


	/**
	 * Determines the correct size of the index (per column) to be used when indexing the main table.
	 * @param string $columnCode
	 * @return int|NULL
	 */
	private function populate_insertMainContent_getIndexSizePerColumn(string $columnCode) :?int
	{
		$columnType = $this->fieldsCfg[$columnCode]['type'];
		$columnDbDef = $this->fieldsCfg[$columnCode]['dbDef'];

		// if type is one of the many varchar(s) - limit size to 10
		if (substr($columnType, 0, 7)=='varchar') {
			return 10; // the lowest is varcahr(10) and we cannot index more than that
		}

		// for strings - check each type of column
		if ($columnType=='string') {
			if (strtoupper(substr($columnDbDef, 0, 4))=='TEXT') {
				return 10;
			}
			if (strtoupper(substr($columnDbDef, 0, 4))=='ENUM') {
				return null;
			}
			// for varchar/char get the size and return it (but limited)
			if (strtoupper(substr($columnDbDef, 0, 7))=='VARCHAR' or strtoupper(substr($columnDbDef, 0, 4))=='CHAR') {
				// get column (var)char size form its definition
				$matches = [];
				preg_match("/^(CHAR|VARCHAR)\(([0-9]+)\).*$/", strtoupper($columnDbDef), $matches);
				if (!$matches or !$matches[2]) {
					return null;
				}
				// limit the size of the index to max 10 chars
				if ($matches[2]>10) {
					return 10;
				} else {
					return null;
				}
			}
			return null;
		}

		// for no stirng/text columns we simply don't limit the size of the column to be indexed
		return null;

	}



	private function populate_determineOrderedFieldsAndTotals() :void
	{

		$dataFieldsWithTotal = [];
		foreach($this->dataFields as $fieldDet) {
			if (in_array($fieldDet['aggFunction'], ['CNT', 'DST', 'SUM'])) {
				$dataFieldsWithTotal[] = $fieldDet['field'] . '_' . $fieldDet['aggFunction'];
			}
		}

		// DETERMINE THE ROWS/COLS/GRAND TOTALS ---------------------------------------------------
		$this->rowsTotals = $this->getAreaOrderedTotalsTree($this->rowsFields, $dataFieldsWithTotal);

		$this->colsTotals = $this->getAreaOrderedTotalsTree($this->colsFields, $dataFieldsWithTotal);

		$this->grandTotals = $this->getOrderedTotals([], $dataFieldsWithTotal)[0];


		// DETERMINE ROWS/COLS LINEAR LIST (and check their size) ---------------------------------
		$currKeys = $rowsKeysLinear = $colsKeysLinear = [];

		if (key_exists('list', $this->rowsTotals)) {
			$this->linearizeAreaKeys($this->rowsTotals['list'], $currKeys, $rowsKeysLinear);
		}
		$this->rowsKeysLinear = $rowsKeysLinear;

		// check the max number of rows
		$rowsKeysLinearCount = count($rowsKeysLinear);
		if ($rowsKeysLinearCount>self::MAX_SIZE_ROWS) {
			throw new Exception( sprintf('Exceeded the max number of allowed rows (%s). Currently the pivot has %s rows.', self::MAX_SIZE_ROWS, $rowsKeysLinearCount) );
		}

		if (key_exists('list', $this->colsTotals)) {
			$this->linearizeAreaKeys($this->colsTotals['list'], $currKeys, $colsKeysLinear);
		}
		$this->colsKeysLinear = $colsKeysLinear;

		// check the max number of cols
		$colsKeysLinearCount = count($colsKeysLinear);
		if ($colsKeysLinearCount>self::MAX_SIZE_COLS) {
			throw new Exception( sprintf('Exceeded the max number of allowed cols (%s). Currently the pivot has %s cols.', self::MAX_SIZE_COLS, $colsKeysLinearCount) );
		}

		// check max number of data cells
		if ($rowsKeysLinearCount*$colsKeysLinearCount>self::MAX_SIZE_DATA) {
			throw new Exception( sprintf('Exceeded the max number of allowed data cells (%s). Currently the pivot has %s cells.', self::MAX_SIZE_DATA, $rowsKeysLinearCount*$colsKeysLinearCount) );
		}

	}


	private function getAreaOrderedTotalsTree(array $areaFields, array $dataFieldsWithTotal) :array
	{

		$areaTotals = [];

		$treeDepthFields = [];

		$areaFieldsNumIdx = array_values($areaFields);

		foreach ($areaFields as $areaField) {

			$treeDepthFields[] = $areaField['field'];
			if ($areaField['orderDataFieldName'] and $areaField['orderDataFieldAggFunction']) {
				$orderField = $areaField['orderDataFieldName'].'_'.$areaField['orderDataFieldAggFunction'];
			} else {
				$orderField = $areaField['field'];
			}

			// gets current depth fields totals (linearized)
			$currentTotals = $this->getOrderedTotals($treeDepthFields, $dataFieldsWithTotal, $orderField, $areaField['orderDir']);

			// associate the linearized data as a tree to correctly keep the ordering
			foreach ($currentTotals as $totalsRecord) {

				// go in depth to associate $totalsRecord by accessing the correct location by reference
				$theseTotals = &$areaTotals;
				$skipRecord = false;
				for($i=0;$i<count($treeDepthFields)-1;$i++) {

					if ($theseTotals['list'][$totalsRecord[$treeDepthFields[$i]]]) {
						$theseTotals = &$theseTotals['list'][$totalsRecord[$treeDepthFields[$i]]];
					} else {
						// if we have a case of limit group size and we are not interested in storing "other" records
						if ($areaFieldsNumIdx[$i]['limitGroupSize'] and !$areaFieldsNumIdx[$i]['includeOthers']) {
							$skipRecord = true; // skips the foreach loop as well to go to the next record
						} else {
							$theseTotals = &$theseTotals['list'][self::GROUP_RECORDS_INTERNAL_KEY]; // go in depth into the "others" key
						}
					}

				}

				// skip the record - we reached the limit group size and we do not store the "others" records
				if ($skipRecord) {
					continue;
				}

				// should limit from this point on
				if ($areaField['limitGroupSize'] and $theseTotals['list'] and count($theseTotals['list'])>=$areaField['limitGroupSize']) {

					// only if we are interested in storing the
					if ($areaField['includeOthers']) {

						// init the new "others" key
						if (!key_exists(self::GROUP_RECORDS_INTERNAL_KEY, $theseTotals['list'])) {
							$theseTotals['list'][self::GROUP_RECORDS_INTERNAL_KEY] = [ 'ttl' => [], 'size'=>0 ];
						}
						foreach ($dataFieldsWithTotal as $dataField) {
							$theseTotals['list'][self::GROUP_RECORDS_INTERNAL_KEY]['ttl'][$dataField] += (float)$totalsRecord[$dataField]; // adds up all "others" keys
						}
						$theseTotals['list'][self::GROUP_RECORDS_INTERNAL_KEY]['size']++; // count the size of this group

					} // else - it does not do anything, we simply want to skip to the next record

				}
				// no limit on this field or not yet reached the limit
				else {
					foreach ($dataFieldsWithTotal as $dataField) {
						$theseTotals['list'][$totalsRecord[$areaField['field']]]['ttl'][$dataField] = (float)$totalsRecord[$dataField]; // just copies the normal totals
					}
				}

			}

		}

		return $areaTotals;

	}


	private function getOrderedTotals(array $fields, array $sumFields, ?string $orderFieldName=null, ?string $orderDir=null) :array
	{

		$selectFields = $fields;
		if ($sumFields) {
			foreach($sumFields as $fld) {
				$selectFields[] = "SUM(`{$fld}`) AS {$fld}";
			}
		}
		$selectFieldsSQL = implode(', ', $selectFields);


		$sql = "SELECT {$selectFieldsSQL} FROM `{$this->pivotTableManager->getTableName()}`";

		// grouping columns (for rows/cols totals); not set when calculating grand totals
		if ($fields) {
			$sql .= "GROUP BY ".implode(', ', $fields);
		}
		// ordering is usually set, unless the call is for grand totals
		if ($orderFieldName and $orderDir) {
			$sql .= " ORDER BY `{$orderFieldName}` {$orderDir}";
		}

		$db = \DB::getInstance();

		$totals = $db->GetAll($sql);

		if ($db->ErrorNo()) {
			throw new \DBException('Invalid SQL for calculating the pivot totals.');
		}

		return $totals;

	}

	/**
	 * Recursively scan rows or cols totals and create a linear list of rows and cols fields.
	 * @param array $listElements
	 * @param array $currKeys
	 * @param array $linearList
	 */
	private function linearizeAreaKeys(array $totalsListElements, array &$currKeys, array &$linearList) :void
	{
		foreach ($totalsListElements as $key=>$chdTotals) {
			$currKeys[] = $key;
			if ($chdTotals['list']) {
				$this->linearizeAreaKeys($chdTotals['list'], $currKeys, $linearList);
			} else {
				$linearList[implode('|', $currKeys)] = $currKeys;
				array_pop($currKeys);
			}
		}
		array_pop($currKeys);
	}



	private function populate_determineDataValues() :void
	{

		// does not store data values if no rows/cols fields present
		if (!$this->colsFields or !$this->rowsFields) {
			return ;
		}

		// get all records and match all that do not use "other fields"
		$allRecordsSQL = "SELECT * FROM `{$this->pivotTableManager->getTableName()}`";
		$allRecordsList = \DB::getInstance()->GetAll($allRecordsSQL);

		// get final list of data fields - to be used below for each cell
		$dataAreaFieldsList = array_map( fn($v) => $v['field'].'_'.$v['aggFunction'], $this->dataFields);


		// LOOP THROUH RECORDS AND FILL IN CELLS WITHOUT "other fields" ///////////////////////////
		foreach ($allRecordsList as $record) {

			// GET ROWS FIELD COMPOSED ------------------------------------------------------------

			$rowsFieldsVals = [];
			foreach($this->rowsFields as $rowFieldDet) {
				$rowsFieldsVals[$rowFieldDet['field']] = $record[$rowFieldDet['field']];
			}
			$rowsFieldsFullStr = implode('|', $rowsFieldsVals);

			// skip if the current row is not present in the linear list (it means it is grouped under "other values")
			if (!key_exists($rowsFieldsFullStr, $this->rowsKeysLinear)) {
				continue;
			}

			// GET COLS FIELD COMPOSED ------------------------------------------------------------

			$colsFieldsVals = [];
			foreach($this->colsFields as $colFieldDet) {
				$colsFieldsVals[$colFieldDet['field']] = $record[$colFieldDet['field']];
			}
			$colsFieldsFullStr = implode('|', $colsFieldsVals);

			// skip if the current row is not present in the linear list (it means it is grouped under "other values")
			if (!key_exists($colsFieldsFullStr, $this->colsKeysLinear)) {
				continue;
			}

			// if this point reached - the key is normally displayed in the final dataValues (no "other values" included for both rows/cols)

			// ADD THE DATA VALUES FOR THIS CELL (this is a non "other values" cell) --------------

			// initialize the main rows field key if not present
			if (!key_exists($rowsFieldsFullStr, $this->dataValues)) {
				$this->dataValues[$rowsFieldsFullStr] = [ $colsFieldsFullStr => [] ];
			}

			// associate the data values
			foreach ($dataAreaFieldsList as $dataFld) {
				$this->dataValues[$rowsFieldsFullStr][$colsFieldsFullStr][$dataFld] = $record[$dataFld];
			}

		}

		// CHECK ALL CELLS THAT HAVE "GROUP OTHERS" FIELDS  ///////////////////////////////////////

		// DETERMINE IF AT LEAST ONE ROWS/COLS FIELDS HAS limitGroupSize WITH includeOthers -------
		$hasLimitOthers = false;
		foreach($this->rowsFields as $fldDet) {
			if ($fldDet['limitGroupSize'] and $fldDet['includeOthers']) {
				$hasLimitOthers = true;
				break;
			}
		}
		if (!$hasLimitOthers) {
			foreach($this->colsKeysLinear as $fldDet) {
				if ($fldDet['limitGroupSize'] and $fldDet['includeOthers']) {
					$hasLimitOthers = true;
					break;
				}
			}
		}

		// no field found on rows/cols that should display
		if (!$hasLimitOthers) {
			return ;
		}


		// AT THIS POINT WE MIGHT HAVE GROUP RECORDS ----------------------------------------------

		// determine the data fields and their agg functions to be used on all group records cell data
		// skips DST and AVG as the values are not correctly determined
		$dataFieldsOnGroupRecords = [];
		foreach ($this->dataFields as $fieldDet) {
			$fieldName = $fieldDet['field'].'_'.$fieldDet['aggFunction'];
			if ($fieldDet['aggFunction']=='CNT' or $fieldDet['aggFunction']=='SUM') {
				$dataFieldsOnGroupRecords[$fieldName] = "SUM(`{$fieldName}`)";
			} elseif ($fieldDet['aggFunction']=='MIN' or $fieldDet['aggFunction']=='MAX') {
				$dataFieldsOnGroupRecords[$fieldName] = "{$fieldDet['aggFunction']}(`{$fieldName}`)";
			}
			// this will skip CNT and AVG as they cannot be combined
		}
		// stop if no data fields found that can be aggregated as groupOthers
		if (!$dataFieldsOnGroupRecords) {
			return ;
		}

		// cache for the rows values combinations
		$rowsSearchConditionsCache = $colsSearchConditionsCache = [];
		foreach ($this->rowsKeysLinear as $rowKey=>$rowComponents) {
			foreach ($this->colsKeysLinear as $colKey=>$colComponents) {

				// find combinations of rows/cols that contain groupRecords - in this case we need to calculate the data values
				if ( strpos($rowKey, self::GROUP_RECORDS_INTERNAL_KEY)!==false or strpos($colKey, self::GROUP_RECORDS_INTERNAL_KEY)!==false ) {

					// DETERMINE THE SEARCH CONDITIONS FOR ROWS FIELDS ............................
					if (key_exists($rowKey, $rowsSearchConditionsCache)) { // also uses a cache for current combination of rows fields
						$rowsSearchConditions = $rowsSearchConditionsCache[$rowKey];
					} else { // no cache - determine the search conditions now
						$rowsSearchConditions = [];
						$idx = 0;
						$rowsTotals = $this->rowsTotals;
						foreach($this->rowsFields as $fieldDet) {
							// the current field has a normal value
							if ($rowComponents[$idx]!=self::GROUP_RECORDS_INTERNAL_KEY) {
								$rowsSearchConditions[$fieldDet['field']] = [ 'op'=>'=', 'v'=>$rowComponents[$idx] ];
							}
							// the current field value is the group records
							else {
								$rowsSearchConditions[$fieldDet['field']] = [ 'op'=>'NOT IN', 'v' => array_diff( array_keys($rowsTotals['list']), [self::GROUP_RECORDS_INTERNAL_KEY]) ];
							}
							// go in depth in row totals tree
							$rowsTotals = $rowsTotals['list'][$rowComponents[$idx]];
							// go to the next field in "rows area"
							$idx++;
						}

						// cache the result, it may be used later
						$rowsSearchConditionsCache[$rowKey] = $rowsSearchConditions;
					}

					// DETERMINE THE SEARCH CONDITIONS FOR COLS FIELDS ............................
					if (key_exists($colKey, $colsSearchConditionsCache)) { // also uses a cache for current combination of cols fields
						$colsSearchConditions = $colsSearchConditionsCache[$colKey];
					} else {
						$colsSearchConditions = [];
						$idx = 0;
						$colsTotals = $this->colsTotals;
						foreach($this->colsFields as $fieldDet) {
							// the current field has a normal value
							if ($colComponents[$idx]!=self::GROUP_RECORDS_INTERNAL_KEY) {
								$colsSearchConditions[$fieldDet['field']] = [ 'op'=>'=', 'v'=>$colComponents[$idx] ];
							}
							// the current field value is the group records
							else {
								$colsSearchConditions[$fieldDet['field']] = [ 'op'=>'NOT IN', 'v' => array_diff( array_keys($colsTotals['list']), [self::GROUP_RECORDS_INTERNAL_KEY]) ];
							}
							// go in depth in col totals tree
							$colsTotals = $colsTotals['list'][$colComponents[$idx]];
							// go to the next field in "cols area"
							$idx++;
						}
						// cache the result, it may be used later
						$colsSearchConditionsCache[$colKey] = $colsSearchConditions;
					}

					// merge conditions from rows/cols and
					$searchConditions = array_merge($rowsSearchConditions, $colsSearchConditions);


					$this->dataValues[$rowKey][$colKey] = $this->getGroupRecordsCellDataValues($searchConditions, $dataFieldsOnGroupRecords);

				}

			}
		}

		// clear the cache
		unset($rowsSearchConditionsCache, $colsSearchConditionsCache);


	}


	private function getGroupRecordsCellDataValues(array $searchConditions, array $dataFields) :array
	{

		if (!$searchConditions) {
			throw new \Exception('You cannot call '.__METHOD__.' without search conditions.');
		}
		if (!$dataFields) {
			return [];
		}

		// prepare select clause - fields and their aggregate functions
		$selectFields = [];
		foreach ($dataFields as $fld=>$aggFld) {
			$selectFields[] = "$aggFld AS {$fld}";
		}

		// prepare conditions and bind vars
		$conditionsSqls = $bindVars = [];
		foreach ($searchConditions as $field=>$condition) {
			switch ($condition['op']) {
				case '=':
					$conditionsSqls[] = "`$field` = ?";
					$bindVars[] = $condition['v'];
					break;
				case 'NOT IN':
					$conditionsSqls[] = "`$field` NOT IN (" . implode(', ', array_fill(0, count($condition['v']), '?') ) . ")";
					$bindVars = array_merge($bindVars, $condition['v']);
					break;
			}
		}

		$sql = "
			SELECT ".implode(', ', $selectFields)."
			FROM `{$this->pivotTableManager->getTableName()}`
			WHERE " . implode(' AND ', $conditionsSqls);
		$db = \DB::getInstance();

		$output = $db->GetRow($sql, $bindVars);

		return $output;

	}

	public function debugPivot()
	{

//		debug($this->rowsKeysLinear, $this->colsKeysLinear);

		// TODO: is this needed?
		$rowsKeysDisplay = $this->rowsKeysLinear ? : [''=>''];
		$colsKeysDisplay = $this->colsKeysLinear ? : [''=>''];

//		debug($rowsKeysDisplay, $colsKeysDisplay);

		debug($this->dataValues);

		debug($this->rowsTotals, $this->colsTotals, $this->grandTotals);


		echo "<table border='1'>";

		echo "<tr>";
		echo "<td style='background-color:#EDE6A3;color:#666666;'>XXX</td>";


		foreach($colsKeysDisplay as $colKey=>$colFields) {
			echo "<td style='background-color:#E2CDFF'>".($colKey?:'-')."</td>";
		}

		echo "</tr>";

		foreach($rowsKeysDisplay as $rowKey=>$rowFields) {
			echo "<tr>";
			echo "<td style='background-color:#F0D1BE'>".($rowKey?:'***')."</td>";
			foreach($colsKeysDisplay as $colKey=>$colFields) {
				if ($this->dataValues[$rowKey][$colKey]) {
					$cellValue = implode(' / ', $this->dataValues[$rowKey][$colKey]);
				} else {
					$cellValue = ' - ';
				}
				echo "<td style='background-color:#A3E8ED'>".($cellValue?:'&nbsp;')."</td>"; // TODO: data pt grups
			}
			echo "</tr>";
		}

		/*
		 foreach ($this->dataValues as $rowsKey=>$rowsList) {
		 echo "<tr>";
		 echo "<td style='background-color:#F0D1BE'>{$rowsKey}</td>";
		 foreach ($rowsList as $colsKey=>$cellValues) {
		 $cellValue = implode(" / ", array_map(fn($x)=>$x['res'], $cellValues));
		 echo "<td style='background-color:#A3E8ED'>".($cellValue?:'&nbsp;')."</td>";
		 }
		 echo "</tr>";
		 }
		 */
		echo "</table>";
	}

}

