<?php
/* $Id$ */
namespace Mammut\DB\Sql\Dialect;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\DB\DB;
use Mammut\DB\Sql\Select;
use Mammut\DB\Sql\Types;
use Mammut\Exception\IllegalStateException;
use Mammut\Exception\ImplementationException;
use Mammut\DB\Sql\Expression\iExprValue;
use Mammut\DB\Sql\Expression\SQLFunction;
use Mammut\DB\Sql\Expression\Literal;
use Mammut\DB\Sql\Expression\iExprBool;
use Mammut\DB\Sql\Expression\iExprCalc;
use Mammut\DB\Sql\Expression\iExpression;
use Mammut\DB\Sql\Query;
use Mammut\StrictObject;
use Mammut\DB\Sql\Update;
use Mammut\DB\Sql\Expression\Calc;
use Mammut\DB\Sql\Insert;
use Mammut\DB\Sql\Expression\Parameter;
use Mammut\DB\Sql\Expression\IsNull;

/**
 * SQL92 dialect implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Dialect
 */
class Sql92 extends StrictObject implements iDialect {
	protected $ddlInstance;

	public function getName() {
		return DB::DIALECT_ANSI;
	}

	public function isOffsetSupported() {
		return false;
	}
	
	public function isConstraintNameGlobal() {
		return false;
	}

	public function getDdl() {
		if(!is_null($this->ddlInstance))
			return $this->ddlInstance;
		$this->ddlInstance = new \Mammut\DB\Sql\Dialect\Ddl\Sql92Ddl();
		return $this->ddlInstance;
	}

	public function quoteIdent($identifier) {
		$identifier = str_replace('"', '\\"', $identifier);
		if(is_array($identifier)) {
			$identifier = implode('"."', $identifier);
			return '"' . $identifier . '"';
		}
		return '"' . $identifier . '"';
	}

	public function quoteIdentInFragment($identifier, array $safeWords = array()) {
		$parts = preg_split('#([\.\s\W])#', $identifier, -1, PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY);
		if($safeWords) {
			$safeWords = array_flip($safeWords);
			$safeWords = array_change_key_case($safeWords, CASE_LOWER);
		}
		foreach($parts as $i=>$part) {
			if($safeWords && isset($safeWords[strtolower($part)])) {
				continue;
			}
			switch($part) {
				case ' ':
				case '.':
				case '*':
				case 'AS':
				case 'As':
				case 'aS':
				case 'as':
				break;
				default:
					$parts[$i] = '"' . str_replace('"', '\\"', $part) . '"';
			}
		}
		return implode('', $parts);
	}

	public function quoteValue($value) {
		if (is_null($value))
			return 'NULL';
		return '\'' . addcslashes($value, "\x00\n\r\\'\"\x1a") . '\'';
	}

	public function escapeString($string) {
		return str_replace('\'', '\'\'', $string);
	}

	public function getSymbol($type) {
		switch($type) {
			case self::SYMB_QUOTE_VALUE:
				return '\'';
			case self::SYMB_QUOTE_IDENT:
				return '"';
			case self::SYMB_IDENT_SEP:
				return '.';
		}
		return false;
	}

	public function getDummyTableSQL() {
		return '';
	}

	public function getInsertSQL(array $param) {
		if (!isset($param[Insert::VALUES]))
			throw new \InvalidArgumentException('VALUES is undefined');
		
		$sql = 'INSERT INTO ';
		if(isset($param[Insert::TABLE]))
			$this->handleTable($sql, $param, false, '');

		if(isset($param[Insert::COLUMNS])) {
			$cols = array();
			foreach ($param[Insert::COLUMNS] as $col) {
				$cols[] = $this->quoteIdent($col);
			}
			$sql .= ' ('.implode(',', $cols).')';
		}
		$vals = array();
		foreach ($param[Insert::VALUES] as $val) {
			$vals[] = $this->getExpressionSQL($val);
		}
		$sql .= ' VALUES ('.implode(',', $vals).')';
		
		return $sql;
	}

	public function getSelectSQL(array $param) {
		$sql = 'SELECT';
		$this->handleQuantifier($sql, $param);
		
		$this->handleColumns($sql, $param);
		if(isset($param[Select::TABLE]))
			$this->handleTable($sql, $param);
		$this->handleJoins($sql, $param);
		
		if(isset($param[Select::WHERE]))
			$sql .= ' WHERE ' . $this->getExpressionSQL($param[Select::WHERE]->getContent());
		
		$this->handleGroup($sql, $param);
		
		if(isset($param[Select::HAVING]))
			$sql .= ' HAVING ' . $this->getExpressionSQL($param[Select::HAVING]->getContent());
		
		$this->handleCombine($sql, $param);
		$this->handleOrder($sql, $param);
		
		if(isset($param[Select::LIMIT]))
			$sql .= ' LIMIT ' . $param[Select::LIMIT];
		
		return $sql;
	}

	public function getUpdateSQL(array $param) {
		$sql = 'UPDATE';
		$this->handleTable($sql, $param, false, '');
		
		if(isset($param[Update::VALUES_SET])) {
			$sets = array();
			foreach ($param[Update::VALUES_SET] as $ident => $value) {
				if ($value instanceof Select)
					$sets[] = $this->quoteIdent($ident).'=('.$value->getSql($this).')';
				else
					$sets[] = $this->quoteIdent($ident).'='.$this->getExpressionSQL($value);
			}
			$sql .= ' SET ' . implode(',',$sets);
		}
				
		if(isset($param[Query::WHERE]))
			$sql .= ' WHERE ' . $this->getExpressionSQL($param[Query::WHERE]->getContent());
		
		if(isset($param[Query::LIMIT]))
			$sql .= ' LIMIT ' . $param[Query::LIMIT];
		
		return $sql;
	}

	public function getDeleteSQL(array $param) {
		$sql = 'DELETE';
		$this->handleTable($sql, $param, false);
		
		if(isset($param[Query::WHERE]))
			$sql .= ' WHERE ' . $this->getExpressionSQL($param[Query::WHERE]->getContent());
		
		if(isset($param[Query::LIMIT]))
			$sql .= ' LIMIT ' . $param[Query::LIMIT];
		
		return $sql;
	}

	public function getTruncateSQL($table) {
		$table = $this->quoteIdent($table);
		return "DELETE FROM {$table}";
	}

	public function getExpressionSQL($param, $hint = NULL) {
// 		echo 'getExp for ' . gettype($param) . "\n";
		if(is_string($param) && $hint == iExprValue::TYPE_IDENTIFIER)
			return $this->quoteIdent($param);
		if(is_scalar($param) || is_null($param))
			return $this->quoteValue($param);
		if($param instanceof Literal) {
			switch($param->getValueType()) {
				case iExprValue::TYPE_IDENTIFIER:
					return $this->quoteIdent($param->getValue());
				case iExprValue::TYPE_VALUE:
					return $this->getExpressionSQL($param->getValue());
				case iExprValue::TYPE_PARAMETER:
					return '?';
			}
		}
		if($param instanceof SQLFunction)
			return $this->getFunctionSQL($param);
		if($param instanceof Parameter) {
			$sql = '';
			$this->handleParameter($sql, []);
			return $sql;
		}
		if ($param instanceof \Mammut\Date\Date)
			return $this->getExpressionSQL($param->format('Y-m-d'));
		if ($param instanceof \DateTime)
			return $this->getExpressionSQL($param->format('Y-m-d H:i:s'));
		
		if(is_array($param)) {
			$sql = '';
			foreach($param as $p) {
				if(!empty($sql))
					$sql .= ' ' . $p->concat . ' ';
				if($p instanceof iExpression)
					$sql .= $this->getExpressionSQL($p->content);
				elseif(is_object($p))
					$sql .= $this->getExpressionSQL($p);
				elseif(is_array($p))
					$sql .= $this->quoteIdent($p);
			}
			if(count($param) > 1)
				$sql = '(' . $sql . ')';
			return $sql;
		}
		
		if($param instanceof \Mammut\DB\Sql\Expression\Compare) {
			$left = $this->getExpressionSQL($param->getLeft());
			$right = $this->getExpressionSQL($param->getRight());
			$op = $this->compareConst2Sql($param->getComparator());
			return $left . $op . $right;
		}
		
		if($param instanceof \Mammut\DB\Sql\Expression\In) {
			$not = $param instanceof \Mammut\DB\Sql\Expression\NotIn;
			$target = $this->getExpressionSQL($param->getTarget());
			$values = $param->getValueSet();
			
			$addAndNull = false;
			if (in_array(NULL, $values)) {
				$addAndNull = true;
				foreach(array_keys($values, NULL, true) as $k)
					unset($values[$k]);
			}
			
			foreach($values as &$v)
				$v = $this->quoteValue($v);
			$sql = $target .($not ? ' NOT IN (' : ' IN (') . implode(',', $values) . ')';
			if ($addAndNull) {
				$e = new IsNull($param->getTarget());
				$sql = '('. $this->getExpressionSQL($e). ' OR ' .$sql.')';
			}
			return  $sql;
		}
		
		if($param instanceof \Mammut\DB\Sql\Expression\IsNull) {
			$not = $param instanceof \Mammut\DB\Sql\Expression\IsNotNull;
			$target = $this->getExpressionSQL($param->getTarget());
			return $target . ($not ? ' IS NOT NULL' : ' IS NULL');
		}
		
		if($param instanceof \Mammut\DB\Sql\Expression\Between) {
			$not = $param instanceof \Mammut\DB\Sql\Expression\NotBetween;
			$target = $this->getExpressionSQL($param->getTarget());
			$min = $this->getExpressionSQL($param->getMinValue());
			$max = $this->getExpressionSQL($param->getMaxValue());
			return $target . ($not ? ' NOT BETWEEN ' : ' BETWEEN ') . $min . ' AND ' . $max;
		}

		if($param instanceof \Mammut\DB\Sql\Expression\Like) {
			$not = $param instanceof \Mammut\DB\Sql\Expression\NotLike;
			$target = $this->getExpressionSQL($param->getTarget());
			$pattern = $this->getExpressionSQL($param->getLike());
			return $target . ($not ? ' NOT LIKE ' : ' LIKE ') . $pattern;
		}
		
		if($param instanceof \Mammut\DB\Sql\Expression\Calc) {
			$sql = '(';
 			$elements = $param->getElements();
 			foreach ($elements as $e) {
				if (is_object($e)) {
 					if (isset($e->operator))
 						$sql .= ' '.$this->handleCalcOperator($e->operator).' ';
 					$sql .= $this->getExpressionSQL($e->value, $e->valuetype);
 				}
 			}
			return $sql.')';
		}
		
		if ($param instanceof \Mammut\DB\Sql\Expression\PredicateSet) {
			return $this->getExpressionSQL($param->getContent());
		}
		
		if(is_object($param) && isset($param->type)) {
			switch($param->type) {
				case 'fragment':
					return $this->getExpressionSQL($param->content);
				// 				case \Mammut\DB\Sql\Expression\Literal::PREDICATE_LITERAL:
				// 					return $this->getExpressionSQL($param->value, $param->valuetype);
				// 				case \Mammut\DB\Sql\Expression\Operator\IsNull::PREDICATE_ISNULL:
				// 					$ident = $this->getExpressionSQL($param->ident);
				// 					return $ident . ' IS NULL';
				// 				case \Mammut\DB\Sql\Expression\Operator\IsNotNull::PREDICATE_ISNOTNULL:
				// 					$ident = $this->getExpressionSQL($param->ident);
				// 					return $ident . ' IS NOT NULL';
				// 				case \Mammut\DB\Sql\Expression\Operator\Compare::PREDICATE_COMPARE:
				// 					$left = $this->getExpressionSQL($param->left[0], $param->left[1]);
				// 					$right = $this->getExpressionSQL($param->right[0], $param->right[1]);
				// 					$op = self::compareConst2Sql($param->comp);
				// 					return $left . $op . $right;
				// 				case \Mammut\DB\Sql\Expression\Operator\In::PREDICATE_IN:
				// 					$ident = $this->getExpressionSQL($param->ident, iExpression::TYPE_IDENTIFIER);
				// 					if($param->value instanceof Select) {
				// 						$subparam = $param->value->getContent();
				// 						$subselect = $this->getSelectSQL($subparam);
				// 						return $ident . ' IN (' . $subselect . ')';
				// 					}
				// 					elseif(is_array($param->value)) {
				// 						$v = $param->value;
				// 						$me = $this;
				// 						array_walk($v, function ($e) use($me) {
				// 							$e = $me->quoteValue($e);
				// 						});
	

// 						return $ident . ' IN (' . implode(',', $v) . ')';
				// 					}
				// 				break;
			}
		}
		echo "\nUnknown Param Type\n";
		var_dump($param);
		throw new \InvalidArgumentException(gettype($param));
	}

	/**
	 * Hook to replace compare strings with dialect specific ones
	 *
	 * @param string $const        	
	 * @return string
	 */
	protected function compareConst2Sql($const) {
		switch($const) {
			case iExprBool::OP_NE:
				return '<>';
		}
		return $const;
	}

	public function getNativeType($type) {
		switch($type) {
			case Types::TYPE_VCHAR:
				return 'VARCHAR';
		}
		return $type;
	}

	public function getFunctionSQL(SQLFunction $function) {
		switch($function->getFunction()) {
			case SQLFunction::F_MIN:
				if(is_null($function->getParameters()))
					throw new \InvalidArgumentException('Noting to min');
				$fragment = $this->getExpressionSQL($function->getParameters());
				return 'MIN(' . $fragment . ')';
			
			case SQLFunction::F_MAX:
				if(is_null($function->getParameters()))
					throw new \InvalidArgumentException('Noting to max');
				$fragment = $this->getExpressionSQL($function->getParameters());
				return 'MAX(' . $fragment . ')';
			
			case SQLFunction::F_COUNT:
				if(is_null($function->getParameters()))
					return 'COUNT(*)';
				$fragment = $this->getExpressionSQL($function->getParameters());
				return 'COUNT(' . $fragment . ')';
			
			case SQLFunction::F_SUM:
				if(is_null($function->getParameters()))
					throw new \InvalidArgumentException('Noting to sum');
				$fragment = $this->getExpressionSQL($function->getParameters());
				return 'SUM(' . $fragment . ')';
			
			case SQLFunction::F_AVG:
				if(is_null($function->getParameters()))
					throw new \InvalidArgumentException('Noting to avg');
				$fragment = $this->getExpressionSQL($function->getParameters());
				return 'AVG(' . $fragment . ')';
			
			case SQLFunction::F_RANDOM:
				if(!is_null($function->getParameters()))
					throw new \InvalidArgumentException('No parameter allowed for RANDOM');
				return 'RANDOM()';
			
			case SQLFunction::F_NOW:
				if(!is_null($function->getParameters()))
					throw new \InvalidArgumentException('No parameter allowed for NOW');
				return 'NOW()';

			case SQLFunction::F_YEAR:
				if(is_null($function->getParameters())) {
					return 'YEAR(' . $this->getExpressionSQL(SQLFunction::now()) . ')';
				}
				else {
					$fragment = $this->getExpressionSQL($function->getParameters());
					return 'YEAR(' . $fragment . ')';
				}
				
			case SQLFunction::F_DEFAULT:
				if(!is_null($function->getParameters()))
					throw new \InvalidArgumentException('No parameter allowed for DEFAULT');
				return 'DEFAULT';
		}
		throw new \InvalidArgumentException('Unknown function ' . gettype($function->getFunction()));
	}

	public function getGenericType($type) {
		switch(strtolower($type)) {
			case 'varchar':
			case 'nvarchar':
				return Types::TYPE_VCHAR;
		}
		return $type;
	}

	public function __toString() {
		return $this->getName();
	}

	protected function handleQuantifier(&$sql, array $param) {
		if(!empty($param[Select::QUANTIFIER]) && $param[Select::QUANTIFIER] == Select::QUANTIFIER_DISTINCT)
			$sql .= ' DISTINCT';
	}

	protected function handleCalcOperator($op) {
		return $op;
	}

	protected function handleColumns(&$sql, array $param) {
		if(empty($param[Select::COLUMNS]))
			$sql .= ' *';
		else {
			$columns = $param[Select::COLUMNS];
			if(is_array($columns)) {
				$me = $this;
				foreach($columns as $key=>&$value) {
					$value = $this->getExpressionSQL($value);
					if(is_string($key))
						$value .= ' AS ' . $this->quoteIdent($key);
				}
				$columns = implode(',', $columns);
			}
			else
				$columns = $this->quoteIdent($columns);
			$sql .= ' ' . $columns;
		}
	}

	/**
	 * Create the target part of a SQL query.
	 *
	 * @param string $sql
	 *        	the SQL string which should be worked on
	 * @param array $param        	
	 * @param boolean $allowSelect
	 *        	true to allow selects as target (UPDATE, SELECT), false otherwise
	 * @param string $prefix
	 *        	prefix of target part, 'FROM' for SELECT and DELETE
	 * @throws IllegalStateException if no source is defined in $param
	 * @throws \InvalidArgumentException if a SELECT should be used in an invalid context
	 */
	protected function handleTable(&$sql, array $param, $allowSelect = true, $prefix = 'FROM') {
		if(!isset($param[Query::TABLE]))
			throw new IllegalStateException('Missing source of select');
		if($param[Query::TABLE] instanceof Select)
			throw new IllegalStateException('SELECT needs to be defined as [aliasName->Select] array');
		
		if (is_string($param[Query::TABLE]))
 			$sql .= ' ' . $prefix . ' ' . $this->quoteIdent($param[Query::TABLE]);
 		elseif ($param[Query::TABLE] instanceof \Mammut\DB\Sql\Elements\Table) {
 			$sql .= ' ' . $prefix . ' ' . $param[Query::TABLE]->getSql($this);
		}
		elseif (is_array($param[Query::TABLE])) { // aliased data
			if($param[Query::TABLE] instanceof Select && !$allowSelect)
				throw new \InvalidArgumentException('SELECT not suiteable as target');
			$dataSrc = $param[Query::TABLE];
			if (count($dataSrc) != 1)
				throw new IllegalStateException('subselect array has to be of size 1');
			
			$alias = key($dataSrc);
			$select = current($dataSrc);
			if(is_string($select))
				$sql .= ' ' . $prefix . ' ' . $this->quoteIdent($select) . ' AS '.$this->quoteIdent($alias);
			elseif($select instanceof \Mammut\DB\Sql\Elements\Table)
				$sql .= ' ' . $prefix . ' ' . $select->getSql($this) . ' AS '.$this->quoteIdent($alias);
			elseif($select instanceof \Mammut\DB\Sql\Select)
				$sql .= ' ' . $prefix . ' (' . $select->getSql($this) . ') AS '.$this->quoteIdent($alias);
			else
				throw new IllegalStateException('target type is not suiteable: '.is_object($select) ? get_class($select) : gettype($select));
		}
		else // other variants
			$sql .= ' ' . $prefix . ' ' . $param[Query::TABLE]->getSql($this);
	}
	
	/**
	 * Convert a JOIN parameter to SQL
	 * @param string $sql
	 * 			the SQL string which should be extended
	 * @param array $param
	 * @throws \InvalidArgumentException
	 */
	protected function handleJoins(&$sql, array $param) {
		if(isset($param[Select::JOINS])) {
			foreach($param[Select::JOINS] as $join) {
				switch($join['type']) {
					case Select::JOIN_INNER:
						$this->handleTable($sql, [Query::TABLE => $join['name']], false, 'INNER JOIN');
					break;
					case Select::JOIN_LEFT:
						$this->handleTable($sql, [Query::TABLE => $join['name']], false, 'LEFT JOIN');
					break;
					case Select::JOIN_RIGHT:
						$this->handleTable($sql, [Query::TABLE => $join['name']], false, 'RIGHT JOIN');
					break;
					case Select::JOIN_OUTER:
						$this->handleTable($sql, [Query::TABLE => $join['name']], false, 'OUTER JOIN');
					break;
					default:
						throw new \InvalidArgumentException('Unknown join type: '.$join['type']);
				}
				if(!empty($join['alias']) && !is_numeric($join['alias']))
					$sql .= ' AS ' . $this->quoteIdent($join['alias']);
				// Not supported ATM
				if(isset($join['using'])) {
					if(is_array($join['using']))
						$sql .= ' USING (' . implode(',', $this->quoteIdent($join['using'])) . ')';
					else
						$sql .= ' USING (' . $this->quoteIdent($join['using']) . ')';
				}
				elseif(isset($join['on'])) {
					$sql .= ' ON ' . $this->getExpressionSQL($join['on']);
				}
			}
		}
	}

	protected function handleGroup(&$sql, array $param) {
		if(isset($param[Select::GROUP])) {
			if(is_array($param[Select::GROUP])) {
				$fields = $param[Select::GROUP];
				$me = $this;
				array_walk($fields, function (&$value, &$key) use($me) {
					$value = ($value instanceof SQLFunction) ? $me->getFunctionSQL($value) : $me->quoteIdent($value);
				});
				$sql .= ' GROUP BY ' . implode(',', $fields);
			}
			else
				$sql .= ' GROUP BY ' . $this->quoteIdent($join['using']);
		}
	}

	protected function handleCombine(&$sql, array $param) {
		if (isset($param[Select::COMBINE])) {
			foreach ($param[Select::COMBINE] as $combine) {
				list($subselect, $type) = $combine;
				switch ($type) {
					case Select::COMBINE_UNION:
						$sql .= "\n UNION \n";
					break;
					case Select::COMBINE_UNIONALL:
						$sql .= "\n UNION ALL \n";
					break;
					case Select::COMBINE_EXCEPT:
						$sql .= "\n EXCEPT \n";
					break;
					case Select::COMBINE_INTERSECT:
						$sql .= "\n INTERSECT \n";
					break;
				}
			}
			$sql .= $subselect->getSql($this);
		}
	}

	protected function handleOrder(&$sql, array $param) {
		if(isset($param[Select::ORDER])) {
			$e = array();
			foreach($param[Select::ORDER] as $o) {
				// TODO: find a way "DESC" on functions
				if (is_string($o)) {
					if ($o[0] == '!') {
						$direction = 'DESC';
						$o = substr($o,1);
					}
					else
						$direction = 'ASC';
					$field = $this->quoteIdent($o);
				}
				else
					throw new \Exception('Unknown sort object type');
				$e[] = ' ' . $field . ' ' . $direction;
			}
			$sql .= ' ORDER BY'.implode(',', $e);
		}
	}

	protected function handleParameter(&$sql, array $param) {
		$sql .= '?';
	}
}