<?php
/* $Id$ */
namespace Mammut\DB\Sql\Dialect;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Exception\IllegalStateException;
use Mammut\Exception\ImplementationException;
use Mammut\DB\DB;
use Mammut\DB\SQLException;
use Mammut\DB\Sql\Expression\Parameter;
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\Query;
use Mammut\DB\Sql\Select;
use Mammut\DB\Sql\Update;
use Mammut\StrictObject;
use Mammut\DB\SQL\Types;
use Zend\Stdlib\DateTime;

/**
 * Microsoft SQLServer dialect implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Dialect
 */
class SqlServer extends Sql92 implements iDialect {
	
	/**
	 *
	 * @var resource \PDO
	 */
	protected $con = null;
	protected $version = '2000';

	public function __construct($connection = null) {
		$this->con = $connection;
	}

	public function getName() {
		return DB::DIALECT_MSSQL;
	}

	public function isOffsetSupported() {
		return (intval($this->version) >= 2012);
	}

	public function getDdl() {
		if(!is_null($this->ddlInstance))
			return $this->ddlInstance;
		$this->ddlInstance = new \Mammut\DB\Sql\Dialect\Ddl\SqlServerDdl($this->con);
		return $this->ddlInstance;
	}

	public function quoteIdent($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] = '[' . $part . ']';
			}
		}
		return implode('', $parts);
	}

	public function quoteValue($value) {
		if(is_int($value))
			return $value;
		if(is_null($value))
			return 'NULL';
		if ($value instanceof \DateTime)
			return '\''.$value->format('Y-m-d\\TH:i:s').'\'';
		
		if($this->con instanceof \PDO)
			return $this->con->quote($value);
		return '\'' . str_replace('\'', '\'\'', $value) . '\'';
	}

	public function escapeString($string) {
		if(is_int($string))
			return $string;
		if(is_null($string))
			return 'NULL';
		if ($string instanceof \DateTime)
			return '\''.$value->format('Y-m-d\\TH:i:s').'\'';
		
		if($this->con instanceof \PDO) {
			$st = $this->con->quote($string);
			return substr($st, 1, -1);
		}
		return str_replace('\'', '\'\'', $string);
	}

	public function getSymbol($type) {
		switch($type) {
			case self::SYMB_QUOTE_VALUE:
				return '\'';
			case self::SYMB_QUOTE_IDENT:
				return array('[',']');
			case self::SYMB_IDENT_SEP:
				return '.';
		}
		return false;
	}

	public function getSelectSQL(array $param) {
		$sql = 'SELECT';
		$this->handleQuantifier($sql, $param);
		
		if(isset($param[Select::LIMIT]))
			$sql .= ' TOP (' . $param[Select::LIMIT] . ')';
		
		$this->handleColumns($sql, $param);
		if(isset($param[Query::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);
		
		return $sql;
	}

	public function getUpdateSQL(array $param) {
		$sql = 'UPDATE';
		
		if(isset($param[Query::LIMIT]))
			$sql .= ' TOP ' . $param[Query::LIMIT];
		
		$this->handleTable($sql, $param, false, '');
		
		if(isset($param[Update::VALUES_SET])) {
			$sets = array();
			foreach ($param[Update::VALUES_SET] as $ident => $value) {
				$sets[] = $this->quoteIdent($ident).'='.$this->getExpressionSQL($value);
			}
			$sql .= ' SET ' . implode(',',$sets);
		}
				
		if(isset($param[Query::WHERE]))
			$sql .= ' WHERE ' . $this->getExpressionSQL($param[Query::WHERE]->getContent());
		
		return $sql;
	}

	public function getDeleteSQL(array $param) {
		$sql = 'DELETE';
		
		if(isset($param[Select::LIMIT]))
			$sql .= ' TOP (' . $param[Select::LIMIT] . ')';
		
		$this->handleTable($sql, $param, false);
		
		if(isset($param[Query::WHERE]))
			$sql .= ' WHERE ' . $this->getExpressionSQL($param[Query::WHERE]->getContent());
		
		return $sql;
	}

	public function getTruncateSQL($table) {
		$table = $this->quoteIdent($table);
		return "TRUNCATE TABLE {$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))
			return $this->quoteValue($param);
		if (is_bool($param))
			return $param ? 1 : 0;
		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\\TH:i:s'));
		
		if($param instanceof Literal) {
			switch($param->getValueType()) {
				case iExprValue::TYPE_IDENTIFIER:
					return $this->quoteIdent($param->getValue());
				case iExprValue::TYPE_VALUE:
					return $this->quoteValue($param->getValue());
				case iExprValue::TYPE_PARAMETER:
					return '?';
			}
		}
		
		if($param instanceof \Mammut\IO\File) {
			if(!$param->isDir()) {
				$data = file_get_contents($param->getPath());
				$data = '0x' . bin2hex($data);
				return $data;
			}
			else
				throw new \InvalidArgumentException('could not add a directory to a table', 503);
		}
		
		if($param instanceof SQLFunction)
			return $this->getFunctionSQL($param);
		if($param instanceof Parameter) {
			$sql = '';
			$this->handleParameter($sql, []);
			return $sql;
		}
		
		if(is_array($param))
			return parent::getExpressionSQL($param, $hint);
		
		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();
			foreach($values as &$v)
				$v = $this->quoteValue($v);
			return $target . ($not ? ' NOT IN (' : ' IN (') . implode(',', $values) . ')';
		}
		
		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\Predicate\Operator\IsNull::PREDICATE_ISNULL:
	// 					$ident = self::getExpressionSQL($param->ident);
	// 					return $ident . ' IS NULL';
	// 				case \Mammut\DB\Sql\Predicate\Operator\IsNotNull::PREDICATE_ISNOTNULL:
	// 					$ident = self::getExpressionSQL($param->ident);
	// 					return $ident . ' IS NOT NULL';
	// 				case \Mammut\DB\Sql\Predicate\Operator\Compare::PREDICATE_COMPARE:
	// 					$left = self::getExpressionSQL($param->left[0], $param->left[1]);
	// 					$right = self::getExpressionSQL($param->right[0], $param->right[1]);
	// 					$op = self::compareConst2Sql($param->comp);
	// 					return $left . $op . $right;
	// 				case \Mammut\DB\Sql\Predicate\Operator\In::PREDICATE_IN:
	// 					$ident = self::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) . ')';
	// 					}
			}
		}
		return parent::getExpressionSQL($param, $hint);
	}

	/**
	 * Hook to replace compare strings with dialect specific ones
	 *
	 * @param string $const        	
	 * @return string
	 */
	protected function compareConst2Sql($const) {
		return $const;
	}

	public function getFunctionSQL(SQLFunction $function) {
		switch($function->getFunction()) {
			case SQLFunction::F_RANDOM:
				if(!empty($function->param))
					throw new \InvalidArgumentException('No parameter allowed for RANDOM');
				return 'RAND()';
				
			case SQLFunction::F_NOW:
				if(!is_null($function->getParameters()))
					throw new \InvalidArgumentException('No parameter allowed for NOW');
				return 'CURRENT_TIMESTAMP';		
		}
		return parent::getFunctionSQL($function);
	}
	
	public function getNativeType($type) {
		switch($type) {
			case Types::TYPE_BOOLEAN:
				return 'BIT';
			
			case Types::TYPE_TINY:
				return 'TINYINT';
			case Types::TYPE_SHORT:
				return 'SMALLINT';
			case Types::TYPE_INT:
				return 'INTEGER';
			case Types::TYPE_LONG:
				return 'BIGINT';
			
			case Types::TYPE_FLOAT:
				return 'FLOAT(24)';
			case Types::TYPE_DOUBLE:
				return 'FLOAT(53)';
				
			case Types::TYPE_DECIMAL:
				return 'DECIMAL';
				
			case Types::TYPE_CHAR:
				return 'NCHAR';
			case Types::TYPE_VCHAR:
				return 'NVARCHAR';
			case Types::TYPE_TEXT:
				return 'NTEXT';
				
			case Types::TYPE_BINARY:
				return 'VARBINARY';
			case Types::TYPE_BLOB:
				return 'IMAGE';

			case Types::TYPE_DATE:
				$version = $this->getServerVersion($this->con);
				if(version_compare($version, '10', '>='))
					return 'DATE';
				else
					return 'DATETIME';
			case Types::TYPE_DATETIME:
				return 'DATETIME';
			case Types::TYPE_UTS:
				return 'INT';
			
			case Types::TYPE_ENUM:
				return 'VARCHAR(64)';
		}
		return parent::getNativeType($type);
	}
	
	/**
	 * Fetches the server version string
	 * 
	 * @param mixed $con
	 *        	a database connection
	 * @throws SQLException on errors
	 * @return string the version string
	 */
	protected function getServerVersion() {
		if (function_exists('sqlsrv_server_info')) {
			$info = sqlsrv_server_info($this->con);
			return $info['SQLServerVersion'];
		}
		else {
			$result = mssql_query('SELECT CAST(SERVERPROPERTY(\'productversion\') AS VARCHAR(255)) AS version', $this->con);
			if(!$result)
				throw new SQLException(mssql_get_last_message(), $query, 4711);
			$v =  mssql_fetch_object($result)->version;
			mssql_free_result($result);
			return $v;
		}
	}
	
}