<?php

// TODO: la unele clase autocomplete, sa existe in tabel in db o coloana ranking/hitCount care sa indice pentru un anumit string de cautare ce element am ales (poate fi util pentru orase cu aceleasi nume, orgdinea rezultatelor poate fi: name ASC, ranking DESC sau poate invers - de vazut dc are sens.
// TODO: de setat minLength (la searchstring) la o valoare 1/2/3 car, etc pt autocomplete-urile care isi preiau informatiile din tabele cu multe inreg.
// TODO: this should be replaced with $R['filters']; all the filters for a field should be collected inside $R['filters'] - this means that all the calls for the autocomplete fields must be fixed - this should be done after all the autocomplete fields are generated using the OOP \Engine\Form\Input\ElementAbstract style.

namespace Engine\Form\Input;

abstract class Autocomplete_Data_GeneralAbstract
{

	/**
	 * The number of results returned by the
	 * @var Int
	 */
	protected $resultsCountPage=10;

	/**
	 * The listing offset.
	 * @var Int
	 */
	protected $offset=0;

	/**
	 * The search string.
	 * @var String
	 */
	protected $searchString='';

	/**
	 * Indicates which is the minimum length for the search to be performed.
	 * @var Int
	 */
	protected $minLength=0;

	/**
	 * The total results found during SQL execution.
	 * @var Int
	 */
	protected $resultsCountTotal=0;

	/**
	 * Indicate if the sql failed during the search. It is set to true if the sql fails.
	 * @var Boolean
	 */
	protected $sqlFailed=false;

	/**
	 * The filters for this request.
	 * @var array
	 */
	protected $filters=[];

	/**
	 * Indicates the search type (startsWith/contains).
	 * @var String
	 */
	protected $searchType = 'startsWith';


	/**
	 * The class constructor.
	 * @param String $searchString - the search string
	 * @param Int $offset - the listing offset
	 */
	public function __construct($searchString, $offset=0)
	{
		$this->setSearchString($searchString);
		$this->setOffset($offset);
	}


	/**
	 * Sets the search string.
	 * @param String $searchString
	 */
	public function setSearchString($searchString)
	{
		$this->searchString=$searchString;
	}


	/**
	 * Sets the minlength for the search to be triggered.
	 * @param Int $length
	 * @throws Exception - if the length is not a valid value.
	 */
	public function setMinLength($length)
	{
		if (!is_numeric($length) or $length<0) {
			throw new \Exception('The min length must be a positive numeric value.');
		}
		$this->minLength=$length;
	}


	/**
	 * Sets the offset for the results list.
	 * @param Int $offset
	 * @throws Exception - if the offset is not a valid value.
	 */
	public function setOffset($offset)
	{
		if (!is_numeric($offset) or $offset<0) {
			throw new \Exception('The offset must be a positive numeric value.');
		}
		$this->offset=$offset;
	}

	/**
	 * Sets the results count per page.
	 * @param int $resultsCountPage
	 */
	public function setResultsCountPage($resultsCountPage)
	{
		$this->resultsCountPage = $resultsCountPage;
	}

	/**
	 * Sets some filters for this autocomplete request.
	 * @param Array $filters
	 */
	public function setFilters(Array $filters)
	{
		$this->filters=$filters;
	}


	/**
	 * Returns the array which will is then sent (json encoded) to be interpreted by the autocomplete lib in JS.
	 * @return Array
	 */
	public function getOutput()
	{

		$output= ['version'=>'3.0', 'totalResults'=>0, 'items'=>[]];

		// check if the search string is too small ------------------------------------------------
		if ($this->minLength and mb_strlen($this->searchString)<$this->minLength) {
			$output['error']='searchStringTooSmall';
		}

		// GET RESULTS ----------------------------------------------------------------------------
		$resultsBulk = [];
		if (!$output['error']) {
			$resultsBulk = $this->getPageResultsBulk();
			if ($this->sqlFailed) {
				$output['error']='sqlFailed';
			}
		}
		// AND FORMAT THEM ------------------------------------------------------------------------
		if (!$output['error']) {
			$resultsForm=$this->getPageResultsFormatted($resultsBulk);
			$output['totalResults']=$this->resultsCountTotal;
			$output['items']=$resultsForm;
		}

		if ($output['items'] && !$output['totalResults']) {
			throw new \Exception("You did not correctly fill in totalResults");
		}

		if ($output['totalResults'] && ($this->offset>$output['totalResults'])) {
			throw new \Exception("You requested results past the max limit");
		}

		if (!$output['items'] && $output['totalResults']) {
			throw new \Exception("You did not correctly fill in the items list. totalResults is {$output['totalResults']}");
		}

		return $output;
	}


	/**
	 * Returns the current page of elements from the database.
	 * @return Array - the elements from the current page
	 */
	abstract protected function getPageResultsBulk() :array ;


	/**
	 * The method returns the current page of elements in the format accepted by the autocomplee lib.
	 * The parameter contains the current elements inthe format extracted by getPageResultsBulk() from the database.
	 * @param Array $pageResults - the format
	 * @return Array - the list of elements in the format accepted by the autocomplete lib.
	 */
	abstract protected function getPageResultsFormatted(Array $pageResults);


	/**
	 * This method is optionally used for running sqls composed by the concrete classes. It adds the
	 * escaped replacements and checks the sql after its execution. If the SQL is okay, the total results
	 * is also saved inside the class attribute $this->resultsCountTotal.
	 * @param String $sql - the sql to be executed
	 * @param Array $replacements - the replacements for the SQL.
	 */
	protected function executeSql($sql, Array $replacements) :array
	{

		// check sql
		$db=\DB::getInstance();
		$rowsEx=$db->Execute($sql, $replacements);
		if ($db->ErrorNo()) {
			\Log::getInstance()->dbError("Could not get the list of results for an autocomplete field which run on `".get_called_class()."` class.");
			$this->sqlFailed = true;
			return [];
		}

		return $rowsEx->GetRows();
	}

	protected function executeCountSql(string $sql, array $replacements)
	{
		$db = \DB::getInstance();
		$countResult = $db->GetOne($sql, $replacements);
		if ($db->ErrorNo()) {
			\Log::getInstance()->dbError("Could not get the total results for an autocomplete field which run on `".get_called_class()."` class.");
			$this->sqlFailed = true;
			return '';
		}

		$this->resultsCountTotal = $countResult;
	}

	protected function executeSqlEntityList(\App\EntityList\GeneralAbstract $entList) :array
	{
		$return = $entList->exportRecords();
		$this->resultsCountTotal = $entList->getRecordsCount();

		return $return;
	}

	/**
	 * The method is optionally used for re-formatting the array from db (sql) format to output (it
	 * is executed for only one element).
	 * @param Mixed $code (usually Int) - the id/code for the current class element.
	 * @param String $value - the value displayed as main info for the current element in the list.
	 * @param String $extra - the extra info which is displayed in the right part of each element of the list.
	 * @param String $info - the extra info which is displayed in the right/left part of each element of the list (outside the record).
	 * @param Array $otherFields - other fields (associative array) that are attached as is.
	 * @return Array - the list element as JS lib knows how to interpret.
	 */
	protected function getCleanItem($code, $value, $extra='', $info='', Array $otherFields=[])
	{

		// add array keys of a list element
		$return= ['code'=>htmlUnclean($code), 'value'=>htmlUnclean($value)];
		if ($extra) {
			$return['extra']=htmlUnclean($extra);
		}
		if ($info) {
			$return['info']=$info;
		}
		if ($otherFields) {
			foreach($otherFields as $key=>$val) {
				$return[$key]=htmlUnclean($val);
			}
		}

		return $return;

	}


	/**
	 * Indicates the search type.
	 * @param string $searchType
	 * @throws \Exception
	 */
	public function setSearchType($searchType)
	{
		if ($searchType!='startsWith' and $searchType!='contains') {
			throw new \Exception('Invalid search type.');
		}
		$this->searchType = $searchType;
	}


	/**
	 * Build the filtering algorithm
	 * This partially overlaps the setSearchType() function. It could use the serachType hint to do the filtering in different ways
	 * Uses REGEXP's
	 * @param array $searchWords
	 * @param array $sortedColumns
	 * @return string
	 */
	protected function buildFilterAlgorithm_regexp($searchWords, $filteredColumnsCount, $filteredColumns)
	{
		$filterAlgorithm = [];
		if ($searchWords) {
			foreach ($searchWords as $word) {
				// filter by name and code for bank/office
				$wordEscapedSqlRegexp = sprintf("[[:<:]]%s", $this->sqlEscapeForRegexpString($word));

				// add "filter"
				$filteredColumnsSqlArr = [];
				foreach ($filteredColumns as $column) {
					$filteredColumnsSqlArr[]= sprintf("{$column} REGEXP '%s'", $wordEscapedSqlRegexp);
				}
				$filterAlgorithm[]= sprintf(" AND (%s)", implode(" OR ", $filteredColumnsSqlArr));
			}
		}

		return implode(" ", $filterAlgorithm);
	}

	protected function buildFilterAlgorithm_like($searchWords, $filteredColumnsCount, $filteredColumns)
	{
		$filterAlgorithm = [];
		if ($searchWords) {
			foreach ($searchWords as $word) {
				// filter by name and code for bank/office
				$wordEscapedSqlLike =  $this->sqlEscapeForLikeString($word);

				// add "filter"
				$filteredColumnsSqlArr = [];
				foreach ($filteredColumns as $column) {
					$filteredColumnsSqlArr[]= sprintf("{$column} LIKE '%s%%'", $wordEscapedSqlLike);

					$delimiters = [' ', '-', '.', ];
					foreach ($delimiters as $d) {
						$filteredColumnsSqlArr[]= sprintf("{$column} LIKE '%%%s%s%%'", $this->sqlEscapeForLikeString($d), $wordEscapedSqlLike);
					}
				}
				$filterAlgorithm[]= sprintf(" AND (%s)", implode(" OR ", $filteredColumnsSqlArr));
			}
		}

		return implode(" ", $filterAlgorithm);
	}




	/**
	 * Build the ranking algorithm.
	 * It will generate a virtual column, containing various sql instructions to sort the results in a "user-friendly" way
	 * @param array $searchWords
	 * @param int $sortedColumnsCount - the maximum count of columns that will be used in this autocomplete. It might be different than count($sortedColumns)
	 * @param array $sortedColumns
	 * @return string
	 */
	protected function buildRankingAlgorithm($searchWords, $sortedColumnsCount, $sortedColumns)
	{
		// default ranking algorithm
		$rankingAlgorithm = sprintf("CONCAT(%s) AS rankingColumn", implode(", '-', ", $sortedColumns));

		if ($searchWords) {
			$sortColWords = count($searchWords);
			$sortColMaxValue = pow(10, $sortColWords + $sortedColumnsCount + 1);

			$sortCriterias = [];

			foreach ($searchWords as $word) {
				// filter by name and code for bank/office
				$wordEscaped = $this->sqlEscapeForString($word);

				$sortCriteriaSql = "";
				if (count($sortedColumns)>1) {
					$sortCriteriaSql = "LEAST(";
				}
				$sortCriteriaSqlArr = [];
				foreach ($sortedColumns as $colunmIdx=>$column) {
					$sortCriteriaSqlArr[]= sprintf("IF(LOCATE('%s', {$column})>0, LOCATE('%s', {$column}) + %d, {$sortColMaxValue})",
						$wordEscaped, $wordEscaped, pow(10, $sortColWords+$colunmIdx)
					);
				}

				$sortCriteriaSql.= implode(",", $sortCriteriaSqlArr);
				if (count($sortedColumns)>1) {
					$sortCriteriaSql.= ")";
				}

				$sortCriterias[]= $sortCriteriaSql;
			}

			$concatenatedColumns = [];
			$concatenatedColumns[]= sprintf("LPAD(%s, %d, '0')", implode(" + ", $sortCriterias), $sortColWords+$sortedColumnsCount+2);
			foreach ($sortedColumns as $column) {
				$concatenatedColumns[]= $column;
			}
			$rankingAlgorithm = sprintf("CONCAT(%s) AS rankingColumn", implode(", '-', ", $concatenatedColumns));
		}

		return $rankingAlgorithm;
	}


	/**
	 * Escape the sql statement, avoiding sql injections
	 * @param string $word
	 * @return string
	 * // TODO: these should be moved to the DB class
	 * // TODO: this seems to be re-defined multiple times. Unify the calls
	 */
	protected function sqlEscapeForRegexpString($word)
	{
		return addcslashes(addcslashes($word, '%_[]<>\\\(\)\.\+\-\*\?\^\$'), '\'"\\');
	}

	/**
	 * Escape the sql statement, avoiding sql injections
	 * @param string $word
	 * @return string
	 */
	protected function sqlEscapeForLikeString($word)
	{
		return addcslashes($word, '%_\'"');
	}

	/**
	 * Escape the sql statement, avoiding sql injections
	 * @param string $word
	 * @return string
	 */
	protected function sqlEscapeForString($word)
	{
		return addcslashes($word, '\'"\\');
	}
}