<?php

namespace App\Utils\Db\ManageRecords;

class Insert extends InsertUpdateAbstract
{

	/**
	 * The main table name (contains info that is copied to populating table.
	 * @var String
	 */
	private $mainTableName;

	/**
	 * The main table alias (contains info that is copied to populating table.
	 * @var String
	 */
	private $mainTableAlias;

	/**
	 * The group columns list
	 * @var array
	 */
	private $groupByColumns = [];

	/**
	 * The order by column
	 * @var string
	 */
	private $orderByColumn = '';

	/**
	 * The order by sorting direction
	 * @var string
	 */
	private $orderBySort;


	/**
	 * Sets the report table name.
	 * @param string $populatingTableName
	 */
	public function __construct($populatingTableName)
	{
		if (!$populatingTableName) {
			throw new \Exception('You must set the populating table name.');
		}
		$this->populatingTableName	= $populatingTableName;
	}


	/**
	 * Forces the method to be public.
	 * @param string $tableName
	 * @param string $tableAlias
	 */
	public function setMainTable($mainTableName, $mainTableAlias)
	{
		$this->mainTableName = $mainTableName;
		$this->mainTableAlias = $mainTableAlias;
	}


	/**
	 * Composes the INSERT SQL. Returns an array with two keys: 'sql' and 'bindVars'
	 * @return array -
	 */
	protected function getSQL() :array
	{

		$bindVars = [];

		// add INSERT clause
		$sql = "INSERT INTO `{$this->populatingTableName}`";
		$sql .= ' ( ' . implode(', ', array_keys($this->sqlData['columns'])) . ' )';

		// add SELECT clause
		$sql .= " SELECT";
		$sql .= ' ' . implode(', ', array_values($this->sqlData['columns']));

		// add FROM clause
		$sql .= " FROM ".$this->getSQL_from();

		// add WHERE
		$whereSQL = $this->getSQL_where();
		if ($whereSQL['sql']) {
			$sql .= " WHERE ".$whereSQL['sql'];
			$bindVars = $whereSQL['bindVars'];
		}

		$groupBySql = $this->getSQL_groupBy();
		if ($groupBySql){
			$sql .= $groupBySql;
		}

		$orderBySql = $this->getSQL_orderBy();
		if ($orderBySql){
			$sql .= $orderBySql;
		}

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

	}


	/**
	 * Composes the FROM part of the SQL - adds all tables and joins them.
	 * @return string
	 */
	private function getSQL_from()
	{

		$return = "`{$this->mainTableName}` AS {$this->mainTableAlias}";
		foreach($this->sqlData['tables'] as $tbl=>$nada) {
			if ($tbl!=$this->mainTableAlias) { // skip main table from the list
				// check that the all table aliases exist
				if ($tbl and !$this->joinTablesList[$tbl]) {
					throw new \Exception("Invalid table alias joined: `{$tbl}`");
				}
				// add this joined table
				$tblDet = $this->joinTablesList[$tbl];
				$return .= " {$tblDet['joinType']} JOIN `{$tblDet['table']}` AS {$tbl} ON ({$tblDet['foreignKeyRef']})";
			}
		}
		return $return;
	}

	/**
	 *
	 * @param array $groupByColumns
	 * @throws \Exception
	 */
	public function addGroupBy(Array $groupByColumns)
	{
		if (!$groupByColumns) {
			throw new \Exception("At least a column is needed for grouping.");
		}

		$this->groupByColumns = $groupByColumns;
	}

	/**
	 *
	 * @return NULL|string
	 */
	private function getSQL_groupBy()
	{
		if (!$this->groupByColumns) {
			return null;
		}

		return " GROUP BY ".implode(',', $this->groupByColumns);
	}

	/**
	 *
	 * @param string $orderByColumn
	 * @throws \Exception
	 */
	public function addOrderBy($orderByColumn, $sort)
	{
		if (!$orderByColumn) {
			throw new \Exception("At least a column is needed for ordering.");
		}

		if (!in_array($sort, ['ASC', 'DESC'])) {
			throw new \Exception("The sorting order must be either ASC or DESC.");
		}

		$this->orderByColumn = $orderByColumn;
		$this->orderBySort = $sort;

	}

	/**
	 *
	 * @return NULL|string
	 */
	private function getSQL_orderBy()
	{
		if (!$this->orderByColumn or !$this->orderBySort) {
			return null;
		}


		return " ORDER BY ".$this->orderByColumn." ".$this->orderBySort;
	}
}