<?php
/**
 * @package     Cherry Picker for Virtuemart
 * @subpackage  Sql Helper
 * @author		Maksym Stefanhuck
 * @copyright   Copyright (C) 2009 - 2014 Galt.md. All rights reserved.
 * @license     GNU General Public License version 2 or later; see COPYING.txt
 */

/*
	Description:

	The purpose of this Class is to provide routines to build SQL queries.
	It becomes invaluable when you have different subsystems providing columns,
	tables, joins, wheres, etc. It's more of a tool to build a query by
	declaring dependencies. Several subsystems may provide same table for a
	join (kind of declaring that this table is needed in a query),
	and when uniquify() is called it resolves the dependancy issue and removes
	duplicate tables.

	Simple examples:

	$table = Sql::table('products', 'p');
	* Note that uniquify process gives advantage to aliased tables, rather
	then non-aliased (which are the candidates for removal).

	$column = Sql::column('product_id', $table); // (or)
	$column = Sql::column('product_id', 'p');

	$category_table = Sql::table('category'); // no alias
	$category_column = Sql::column('product_id', $category_table);
	$join = Sql::join('left', $category_table, $category_column);


*/

namespace cherrypicker;
defined('_JEXEC') or die;

defined(__NAMESPACE__ ."\SQL_DIFFERENT") or define(__NAMESPACE__ ."\SQL_DIFFERENT", -1);
defined(__NAMESPACE__ ."\SQL_EQUAL") or define(__NAMESPACE__ ."\SQL_EQUAL", 0);
defined(__NAMESPACE__ ."\SQL_LEFT_REDUNDANT") or define(__NAMESPACE__ ."\SQL_LEFT_REDUNDANT", 1);
defined(__NAMESPACE__ ."\SQL_RIGHT_REDUNDANT") or define(__NAMESPACE__ ."\SQL_RIGHT_REDUNDANT", 2);


class Sql {

	public static function column($name, $table = '') {
		return new SqlColumn($name, $table);
	}

	public static function table($name, $alias = '') {
		return new SqlTable($name, $alias);
	}

	public static function join($type, SqlTable $table, SqlColumn $column1, SqlColumn $column2 = null) {
		return new SqlJoin($type, $table, $column1, $column2);
	}

	public static function where($string/*, [... columns] */) {
		//$args = func_get_args();
//var_dump( is_callable(array(__NAMESPACE__ .'\SqlWhere', 'dose')) );
		//$instance = new SqlWhere();
		//return call_user_func_array(array($instance, 'args'), func_get_args());


		$reflection = new \ReflectionClass(__NAMESPACE__ .'\SqlWhere');
		return $reflection->newInstanceArgs(func_get_args());

		//return call_user_func(array(__NAMESPACE__ .'\SqlWhere', '__construct'), func_get_args());
		//return new SqlWhere($args);
	}

	public static function query() {
		return new SqlQuery();
	}

}


class SqlColumn {
	private $name;
	private $table;

	function __construct($name, $table) {
		$this->name = $name;
		$this->table = $table;
	}

	function __toString() {
		$s = '';
		if ($this->table) {
			if (gettype($this->table) == "string") {
				$s = $this->table .'.';
			} else {
				$alias = $this->table->alias();
				$s = $alias ? $alias : "`". $this->table->name() ."`";
				$s .= '.';
			}
		}
		return $s ."`". $this->name ."`";
	}

	// todo: used for JOIN's ON part
	public function name() {
		return $this->name;
	}

	public function table($set = null) {
		if ($set === null) {
			return $this->table;
		} else {
			$this->table = $set;
		}
	}

}

/*
	Possible uses:

	from (`#__products` as p)
	where p.`product_id`
	join `#__products` as p on `#__images`.`id`=p.`id`

*/
class SqlTable {
	private $name;
	private $alias;

	function __construct($name, $alias) {
		$this->name = $name;
		$this->alias = $alias;
	}

	public function name() {
		return $this->name;
	}

	public function alias() {
		return $this->alias;
	}

	function __toString() {
		$s = "`". $this->name ."`";
		if ($this->alias)
			$s .= " as ". $this->alias;
		return $s;
	}

	public static function compare($table1, $table2) {
		$type1 = gettype($table1);
		$type2 = gettype($table2);
		if ($type1 == "string" && $type2 == "string")
			return ($table1 == $table2) ? SQL_EQUAL : SQL_DIFFERENT;
		if ($type1 == "string")
			return ($table1 == $table2->name() || $table1 == $table2->alias()) ?
				SQL_EQUAL : SQL_DIFFERENT;
		if ($type2 == "string")
			return ($table2 == $table1->name() || $table2 == $table1->alias()) ?
				SQL_EQUAL : SQL_DIFFERENT;
		if ($table1->name() == $table2->name()) {
			$alias1 = $table1->alias();
			$alias2 = $table2->alias();
			if ($alias1 == $alias2)
				return SQL_EQUAL;
			if ($alias1 && !$alias2)
				return SQL_RIGHT_REDUNDANT;
			if (!$alias1 && $alias2)
				return SQL_LEFT_REDUNDANT;
			return SQL_DIFFERENT;
		}
		return SQL_DIFFERENT;

		//if ($table1 == $table2)
		//	return SQL_EQUAL;
		//if ($table1->alias() && !$table2->alias())
		//	return SQL_RIGHT_REDUNDANT;
		//if (!$table1->alias() && $table2->alias())
		//	return SQL_LEFT_REDUNDANT;
		//return SQL_DIFFERENT;
	}


}


class SqlJoin {
	private $type;
	private $table;
	private $column1;
	private $column2;

	function __construct($type, SqlTable $table, SqlColumn $column1, SqlColumn $column2 = null) {
		$this->type = $type;
		$this->table = $table;
		$this->column1 = $column1;
		$this->column2 = $column2;
	}

	public function table() {
		return $this->table;
	}

	public static function compare($join1, $join2) {
		$compared = SqlTable::compare($join1->table(), $join2->table());
		if ($compared == SQL_EQUAL)
			return SQL_EQUAL;
		if ($compared == SQL_LEFT_REDUNDANT || $compared == SQL_RIGHT_REDUNDANT) {
			if ($compared == SQL_LEFT_REDUNDANT) {
				$table = $join1->table();
				$columns = $join2->columns();
			} else {
				$table = $join2->table();
				$columns = $join1->columns();
			}

			/* If some column relies on the table in question, then this table
			cannot be removed. For example, two joins:
			(1) left join `products` on m.`id`=`products`.`id`
			(2) left join `products` as child on `products`.`id`=child.`id`

			Here (2) join cannot be left alone as it has a column reference
			from (1) join.
			*/
			foreach ($columns as $column)
				if (SqlTable::compare($column->table(), $table) == SQL_EQUAL)
					return SQL_DIFFERENT;

			return $compared;
		}
		return SQL_DIFFERENT;
	}

	public function columns() {
		$columns = array();
		$columns[] = $this->column1;
		if ($this->column2) $columns[] = $this->column2;
		return $columns;
	}

	function __toString() {
		$s = "";
		if ($this->type) $s = strtoupper($this->type) ." ";
		$s .= "JOIN ". $this->table ." ";
		if ($this->column2)
			$s .= "ON ". $this->column1 .'='. $this->column2;
		else
			$s .= "USING(`". $this->column1->name() ."`)";
		return $s;
	}
}


class SqlWhere {
	private $args;

	function __construct($string/*, [... columns] */) {
		//if (func_num_args()) {
//$args = func_get_args();
//print_r($args);
//die;
		$this->args = func_get_args();
		//}
	}

//	public function args($string) {
//$args = func_get_args();
//print_r($args);
//die;
//		$this->args = func_get_args();
//	}

	public function columns() {
		return array_slice($this->args, 1, count($this->args) - 1);
	}

	function __toString() {
		return call_user_func_array('sprintf', $this->args);
	}
}


class SqlQuery {
	private $select = array();
	private $from = array();
	private $join = array();
	private $stringJoins = array();
	private $where = array();
	private $group = array();
	private $order = array();
	private $limit = array();
	private $distinct = false;

	public function select($what, $as = '') {
		//if (gettype($what) == string) {
		//} else {
		//}
		$this->select[] = array($what, $as);

		return $this;
	}

	public function from(SqlTable $table) {
		/* can do a check here whether $table is added first time */
		$this->from[] = $table;
		return $this;
	}

	public function distinct($set = null) {
		if ($set === null) {
			return $this->distinct;
		} else {
			$this->distinct = $set;
			return $this;
		}
	}

	/*
	* @param $mixed Could of type SqlJoin in which case all other parameters are
	* ignored. Otherwise first parameter is type of join 'left', 'right', 'inner',
	* 'outer'
	*/
	public function join($mixed, SqlTable $table = null, SqlColumn $column1 = null, SqlColumn $column2 = null) {
		$type = gettype($mixed);
		if ($type == "object" && get_class($mixed) == __NAMESPACE__ ."\SqlJoin") {
			$this->join[] = $mixed;
		} else if ($type == "string") {
			if ( !$table || !$column1 )
				throw new \Exception('Parameter 2 and 3 are missing');
			$this->join[] = new SqlJoin($mixed, $table, $column1, $column2);
		}
		return $this;
	}

	// todo: remove legacy
	public function stringJoin($string) {
		$this->stringJoins[] = $string;
	}

	/*
	* @param $mixed Could of type SqlWhere or a string.
	*/
	public function where($mixed) {
		$this->where[] = $mixed;
		return $this;
	}

	/*
	* @param $mixed SqlColumn or array of columns
	*/
	public function group($mixed) {
		$this->group = $mixed;
		return $this;
	}

	/*
	* @param $mixed SqlColumn or array of columns
	*/
	public function order($mixed, $order = '') {
		if (gettype($mixed) == "array")
			$what = $mixed;
		else
			$what = array($mixed);
		$this->order = array($what, $order);
		return $this;
		//$type = gettype($mixed);
		//if ($type == "object" && get_class($mixed) == "SqlColumn") {
		//	$this->order[] = $column;
		//} else if ($type == "array") {
		//}
	}

	public function limit($from = 0, $to) {
		$this->limit = array($from, $to);
		return $this;
	}

	public function uniquify() {
		$joins = $this->join;
		$unique = array();
		$replacements = array(/* table, withTable */);
		foreach ($joins as $join) {
			$join_table = $join->table();
			$keep = true;
			foreach ($this->from as $table) {
				$compared = SqlTable::compare($join_table, $table);
				if ($compared == SQL_LEFT_REDUNDANT) {
					$replacements[] = array($join_table, $table);
					$keep = false;
				} else if ($compared == SQL_EQUAL) {
					$keep = false;
				}
			}
			if ($keep) $unique[] = $join;
		}

		$joins = $unique;
		$unique = array();
		foreach ($joins as $join) {
			$add = true;
			//$join_table = $join->table();
			foreach ($unique as &$unique_join) {
				//$unique_join_table = $unique_join->table();
				//$compared = SqlTable::compare($unique_join_table, $join_table);
				$compared = SqlJoin::compare($unique_join, $join);
	//if ($compared > 0) {
	//	echo '<br/>';
	//	echo $compared;
	//	echo '<br/>';
	//	echo $unique_join;
	//	echo '<br/>';
	//	echo '<br/>';
	//	echo $join;
	//}
				if ($compared == SQL_EQUAL) {
					$add = false;
				} else if ($compared == SQL_RIGHT_REDUNDANT) {
					$replacements[] = array($join->table(), $unique_join->table());
					$add = false;
				} else if ($compared == SQL_LEFT_REDUNDANT) {
					$replacements[] = array($unique_join->table(), $join->table());
					$unique_join = $join;
					$add = false;
				}
			}
			if ($add) $unique[] = $join;
		}

		if ($replacements) {
			foreach ($replacements as $replacement) {
				foreach ($unique as $join)
					foreach ($join->columns() as $column)
						if (SqlTable::compare($column->table(), $replacement[0]) == SQL_EQUAL)
							$column->table($replacement[1]);

				foreach ($this->where as $where) {
					$type = gettype($where);
					if ($type == "object" && get_class($where) == __NAMESPACE__ ."\SqlWhere") {
						foreach ($where->columns() as $column)
							if (SqlTable::compare($column->table(), $replacement[0]) == SQL_EQUAL)
								$column->table($replacement[1]);
					}
				}
			}
		}


		/*
			Scan through WHERE statements and check if any tables need to be
			re-associated with tables from FROM clause.
		*/
		foreach ($this->from as $table) {
			/* check if current table is within replacements list. In other
				words if it has already been used for replacements */
			foreach ($replacements as $replacement)
				if (SqlTable::compare($table, $replacement[1]) == SQL_EQUAL)
					continue 2; // go to next FROM table

			foreach ($this->where as $where) {
				$type = gettype($where);
				if ($type == "object" && get_class($where) == __NAMESPACE__ ."\SqlWhere") {
					foreach ($where->columns() as $column)
						if (SqlTable::compare($column->table(), $table) == SQL_LEFT_REDUNDANT)
							$column->table($table);
				}
			}
		}

		$this->join = $unique;

		//echo '<pre>';
		//print_r($replacements);
		//var_dump( count( $unique ) );
		//print_r($unique);
		//echo '</pre>';
	}

	public function joins() {
		return $this->join;
	}

	public function wheres() {
		return $this->where;
	}


	/* this can be expanded further to support UPDATE if needed. In our case
		updates are not in foreseeable future, so it is left out for
		simplicity */
	public function build() {
		$s = "SELECT ";
		if ($this->distinct) $s .= "DISTINCT ";
		$selects = array();
		foreach ($this->select as $column) {
			$selects[] = $column[1] ? $column[0] ." as ". $column[1] : $column[0];
		}
		$s .= $selects ? join(", ", $selects) : "*";

		$s .= "\nFROM (". join(", ", $this->from) .")";

		if ($this->join)
			$s .= "\n". join("\n", $this->join);

		if ($this->stringJoins)
			$s .= "\n". join("\n", $this->stringJoins);

		if ($this->where)
			$s .= "\nWHERE ". join("\nAND ", $this->where);

		if ($this->group)
			$s .= "\nGROUP BY ". $this->group;

		if ($this->order) {
			$s .= "\nORDER BY ". join(", ", $this->order[0]);
			if ($this->order[1]) $s .= " ". $this->order[1];
		}

		if ($this->limit)
			$s .= "\nLIMIT ". $this->limit[0] .", ". $this->limit[1];

		return $s;
	}

	function __toString() {
		return $this->build();
	}

}
