<?php

// TODO: la unele campuri, ar trebui sa se genereze intregul interval - ex. pentru lunile anului (sa apara fortat cele 12 luni, sa nu fie sarite daca nu am date atunci)

namespace App\QuickReports\PivotTables\Data;

abstract class GeneralAbstract
{

	protected $dbTable = null;

	/**
	 * A list of all the available columns for the current pivot source.
	 * @var array
	 */
	protected $allAvailableColumns = [];


	protected $filters = [];

	protected $groupingColumns = [];

	protected $orderingColumns = [];

	protected $dataColumns = [];

	protected $limit = 0;


	/**
	 * Stores and checks the db table.
	 * @throws \Exception
	 */
	public function __construct()
	{

		// store and check the db table name
		$this->dbTable = $this->getDbTable();
		if (!$this->dbTable) {
			throw new \Exception('No table name set for `'.__CLASS__.'`.');
		}

	}

	/**
	 * Returns the pivot source for the current pivot data.
	 * @return String
	 */
	public function getPivotSource()
	{
		return stripClassNamespace(get_called_class());
	}

	/**
	 * Indicates if a certain column is valid.
	 * @param String $column
	 */
	protected function columnIsValid($column)
	{
		return in_array($column, $this->allAvailableColumns);
	}


	/**
	 * Returns the db table name that manages the pivot source.
	 * @return String
	 */
	abstract protected function getDbTable();

	/**
	 * Adds a new filter for this pivot data.
	 * @param String $column
	 * @param String $operation
	 * @param Mixed $value
	 */
	public function addFilter($column, $operation, $value)
	{
		// check filter / operation
		if (!$this->columnIsValid($column)) {
			throw new Exception("Invalid filter column '{$column}' in '{$this->getPivotSource()}' pivot source.");
		}
		if (!in_array($operation, ['>', '>=', '<', '<=', '=', '!=', 'LIKE', 'NOT LIKE', 'BETWEEN', 'IN', 'NOT IN', 'IS NULL', 'IS NOT NULL'])) {
			throw new Exception("Invalid filter operation '{$operation}' in '{$this->getPivotSource()}' pivot source.");
		}

		// store filter
		$this->filters[] = [
			'column'	=> $column,
			'operation' => $operation,
			'value'		=> $value,
		];
	}

	/**
	 * Add multiple filters at once (array of array of keys 'column', 'operation', 'value' - the internal storage format.
	 * @param Array $filters
	 */
	public function addFilters(Array $filters)
	{
		foreach ($filters as $filter) {
			$this->addFilter($filter['column'], $filter['operation'], $filter['value']);
		}
	}

	/**
	 * Returns the internally stored filters.
	 */
	public function getFilters()
	{
		return $this->filters;
	}

	/**
	 * Resets the currently stored filters.
	 */
	public function resetFilters()
	{
		$this->filters = [];
	}

	/**
	 * Sets the grouping columns (resets previous
	 * @param array $groupingColumns
	 */
	public function addGroupingColumn($column)
	{
		if (!$this->columnIsValid($column)) {
			throw new Exception("Invalid grouping column '{$column}' in '{$this->getPivotSource()}' pivot source.");
		}
		$this->groupingColumns[$column] = $column;
	}

	/**
	 * Resets the grouping columns.
	 */
	public function resetGroupingColumns()
	{
		$this->groupingColumns = [];
	}

	/**
	 * Adds a new data column to the object.
	 * @param String $column
	 * @throws Exception
	 */
	public function addDataColumn($column, $aggregateFunction)
	{
		if (!$this->columnIsValid($column)) {
			throw new Exception("Invalid data column '{$column}' in '{$this->getPivotSource()}' pivot source.");
		}
		if (!in_array($aggregateFunction, ['SUM', 'COUNT', 'AVG'])) {
			throw new Exception("Invalid data column aggregate function '{$aggregateFunction}' in '{$this->getPivotSource()}' pivot source.");
		}
		$this->dataColumns[$column.'_'.strtolower($aggregateFunction)] = ['column'=>$column, 'aggregate'=>$aggregateFunction];
	}

	/**
	 * Resets the data columns.
	 */
	public function resetDataColumns()
	{
		$this->dataColumns = [];
	}

	/**
	 * Adds a new ordering column.
	 * @param String $column
	 * @param String $aggregateFunction
	 * @param String $direction
	 * @throws Exception
	 */
	public function addOrderingColumn($column, $aggregateFunction, $direction)
	{
		// check ordering cols
		if (!$this->columnIsValid($column)) {
			throw new Exception("Invalid ordering column '{$column}' in '{$this->getPivotSource()}' pivot source.");
		}
		if ($aggregateFunction and !in_array($aggregateFunction, ['SUM', 'COUNT', 'AVG'])) {
			throw new Exception("Invalid ordering column aggregate function '{$aggregateFunction}' in '{$this->getPivotSource()}' pivot source.");
		}
		if ($direction!='ASC' and $direction!='DESC') {
			throw new Exception("Invalid ordering direction '{$direction}' in '{$this->getPivotSource()}' pivot source.");
		}

		// store ordering columns
		$this->orderingColumns[] = [
			'column'	=> $column,
			'aggregate'	=> $aggregateFunction,
			'direction'	=> $direction
		];
	}

	/**
	 * Resets the ordering columns.
	 */
	public function resetOrderingColumns()
	{
		$this->orderingColumns = [];
	}

	/**
	 * Sets the limit of the SQL.
	 * @param Int $limit
	 */
	public function setLimit($limit)
	{
		if (!is_int($limit) or $limit<0) {
			throw new Exception("Invalid limit '{$limit}' in '{$this->getPivotSource()}' pivot source.");
		}
		$this->limit = $limit;
	}

	/**
	 * Returns the records available for this sql.
	 * @throws \DBException
	 * @return \ADORecordSet
	 */
	public function getRecords()
	{

		$sqlDet = $this->getSql();

		// check if the data was cached
		$cacheObj = new \Engine\Cache\DiskWithLRU();

		$CFG = \Engine\Loader_Cfg::getInstance()->getGlobal();
		$cacheObj->setPath($CFG['paths']['quickReportsCache']);
		$cacheKey = 'quickReports_sql_'.$sqlDet['sql'].'['.serialize($sqlDet['prepVars']).']';

		$rowsList = $cacheObj->get($cacheKey);

		// if no cache found - get it from db
		if ($rowsList===null) {

			$db = \DB::getInstance();

			$rowsList = $db->GetAll($sqlDet['sql'], $sqlDet['prepVars']);

			if (!is_array($rowsList)) {
				throw new \DBException("Could not run SQL for '{$this->getPivotSource()}' pivot source.");
			}

			// cache the current rows
			$cacheObj->set($cacheKey, $rowsList, 86400); // cache for 1 day

		}

		return $rowsList;

	}

	/**
	 * Composes the SQL for the current data, filters, grouping, etc.
	 */
	public function getSql()
	{

		// prepared statements var (for the whole SQL)
		$prepVars = [];
		$aggregateFunctionFound = false;

		// SOLVE "SELECT" CLAUSE ------------------------------------------------------------------
		$columnsList = [];
		$columnsList = $this->groupingColumns;
		// add data cols
		foreach($this->dataColumns as $dataColDet) {
			$aggregateFunctionFound = true;
			$columnsList[$dataColDet['column'].'_'.strtolower($dataColDet['aggregate'])] = "{$dataColDet['aggregate']}(`{$dataColDet['column']}`) AS `{$dataColDet['column']}_".strtolower($dataColDet['aggregate'])."`";
		}
		// add ordering cols
		foreach($this->orderingColumns as $orderColDet) {
			if ($orderColDet['aggregate']) {
				$aggregateFunctionFound = true;
				$columnsList[$orderColDet['column'].'_'.strtolower($orderColDet['aggregate'])] = "{$orderColDet['aggregate']}(`{$orderColDet['column']}`) AS `{$orderColDet['column']}_".strtolower($orderColDet['aggregate'])."`";
			}
		}
		$columnsSQL = implode(', ', $columnsList);

		// check that if no group by and no aggregate function - the output may be huge; do not continue
		if (!$this->groupingColumns and !$aggregateFunctionFound) {
			throw new Exception('You must set either the grouping column(s) or call an aggregate function; otherwise the records list may be huge.');
		}

		// SOLVE "WHERE" CLAUSE -------------------------------------------------------------------
		$filtersList = [];
		foreach($this->filters as $filter) {
			$filterOp = strtoupper($filter['operation']);
			$filterColumn = $filter['column'];
			$currentFilter = '';
			switch ($filterOp) {
				case '>': case '>=': case '<': case '<=': case '=': case '!=': case 'LIKE': case 'NOT LIKE': {
					$currentFilter = "`{$filterColumn}` {$filterOp} ?";
					$prepVars[] = $filter['value'];
					break;
				}
				case 'BETWEEN': {
					if (!is_array($filter['value']) or count($filter['value'])!=2) {
						throw new \Exception("Invalid values for `{$filterOp}`. Values should be as array of with two elements.");
					}
					$currentFilter = "`{$filterColumn}` BETWEEN ? AND ?";
					$prepVars[] = min($filter['value']);
					$prepVars[] = max($filter['value']);
					break;
				}
				case 'IN': case 'NOT IN': {
					if (!is_array($filter['value'])) {
						throw new \Exception("Invalid operation values for `{$filterOp}`. Values should be set as array.");
					}
					if ($filter['value']) {
						$currentFilter = "`{$filterColumn}` {$filterOp} (".implode(", ", array_fill(0, count($filter['value']), '?')).")";
						$prepVars = array_merge($prepVars, array_values($filter['value']));
					} else {
						$currentFilter = "0";
					}
					break;
				}
				case 'IS NULL': case 'IS NOT NULL': {
					$currentFilter = "`{$filterColumn}` $filterOp";
					break;
				}
				default: {
					throw new \Exception("Invalid operation `{$filterOp}` set in ".__METHOD__." for class ".get_called_class());
				}
			}

			$filtersList[] = '('.$currentFilter.')';

		}

		if ($filtersList) {
			$filtersSQL = implode(" AND ", $filtersList);
		} else {
			$filtersSQL = '1';
		}

		// SOLVE "GROUP BY" CLAUSE ----------------------------------------------------------------
		if ($this->groupingColumns) {
			$groupingSQL = "GROUP BY ".implode(", ", $this->groupingColumns);
		} else {
			$groupingSQL = '';
		}

		// SOLVE "ORDER BY" CLAUSE ----------------------------------------------------------------
		if ($this->orderingColumns) {
			$orderingSQL = "ORDER BY ";
			$orderCols = [];
			foreach($this->orderingColumns as $orderCol) {
				if ($orderCol['aggregate']) {
					$orderCols[] = "`{$orderCol['column']}_".strtolower($orderCol['aggregate'])."` {$orderCol['direction']}";
				} else {
					$orderCols[] = "`{$orderCol['column']}` {$orderCol['direction']}";
				}
			}
			$orderingSQL .= implode(", ", $orderCols);
		} else {
			$orderingSQL = '';
		}

		// SOLVE "LIMIT" CLAUSE -------------------------------------------------------------------
		$limitSQL = '';
		if ($this->limit) {
			$limitSQL = "LIMIT {$this->limit}";
		}

		// COMPOSE THE FINAL SQL ------------------------------------------------------------------
		$sql = trim("SELECT {$columnsSQL} FROM `{$this->dbTable}` WHERE {$filtersSQL} {$groupingSQL} {$orderingSQL} {$limitSQL}");

		return ['sql'=>$sql, 'prepVars'=>$prepVars];

	}

	/**
	 * Displays the name of that column based on type and value (for human readable format).
	 * @param String $column
	 * @param String $value
	 * @throws Exception
	 */
	public function getColumnDisplayValue($column, $value)
	{
		if (!$this->columnIsValid($column)) {
			throw new Exception('Column not set/valid to be translated in `'.$this->getPivotSource().'`.');
		}
		return $value;
	}

	public function getColumnName($column)
	{
		$loaderLang = \App\Loader_Lang::getInstance();
		$loaderLang->loadAppFile("_customLoad/pivotTablesSources/{$this->getPivotSource()}.lang.php");
		$_LANG = $loaderLang->getGlobal();
		$colName = $_LANG['Reports']['PivotTables']['fieldNames'][$this->getPivotSource()][$column];
		if (!$colName) {
			$colName = $column;
		}
		return $colName;
	}

}