<?php
/* $Id$ */
namespace Mammut\DB\MSSQL;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Model\RefInfo;
use Mammut\DB\SQL\Types;


/**
 * MSSQL utility library
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\MSSQL
 */
class MSSQLUtil extends \Mammut\StrictObject {

	private function __construct() {
	} // only static access

	public static function fieldDefSQL(\Mammut\DB\DB $db, ColumnInfo $column, $primary = array()) {
		$sql = $db->escapeColumnName($column->getName());
		$dialect = $db->getDialect();
		
		switch($column->getType()) {
			
			case Types::TYPE_TINY:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type tiny");
				$size = (int) $size;
				if($size > 4)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' IDENTITY(1,1)';
				
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				}
				elseif(!is_null($column->getDefault())) {
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
				}
				else
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case Types::TYPE_SHORT:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type short");
				$size = (int) $size;
				if($size > 6)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' IDENTITY(1,1)';
				
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				}
				elseif(!is_null($column->getDefault())) {
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
				}
				else
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case Types::TYPE_INT:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type short");
				$size = (int) $size;
				if($size > 11)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' IDENTITY(1,1)';
				
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				}
				elseif(!is_null($column->getDefault())) {
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
				}
				else
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case Types::TYPE_LONG:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type short");
				$size = (int) $size;
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' IDENTITY(1,1)';
				
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				}
				elseif(!is_null($column->getDefault())) {
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
				}
				else
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case Types::TYPE_FLOAT:
			case Types::TYPE_DOUBLE:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("precicion has to be a integer for type ".$column->getType());
				
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				
				if(!is_null($column->getDefault()))
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			break;
			
			case Types::TYPE_DECIMAL:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_array($size))
					throw new \InvalidArgumentException("size has to be an array for type decimal");
				if(count($size) != 2)
					throw new \RangeException("size array has to containt 2 integers");
				reset($size);
				$m = (int) current($size);
				next($size);
				$d = (int) current($size);
				if($m < 0 || $d < 0)
					throw new \UnexpectedValueException("integers of the size array needs to be positive");
				
				$sql .= '(' . $m . ',' . $d . ')';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if(!is_null($column->getDefault()))
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			break;
			
			case Types::TYPE_CHAR:
			case Types::TYPE_VCHAR:
			case Types::TYPE_BINARY:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type ".$column->getType());
				
				$size = (int) $size;
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if(!is_null($column->getDefault()))
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			break;
			
			case Types::TYPE_TEXT:
			case Types::TYPE_BLOB:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				// TODO: fix for new sql server versions, NTEXT/IMAGE is deprecated
				
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if(!is_null($column->getDefault()))
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			break;
			
			case Types::TYPE_BOOLEAN:
			case Types::TYPE_DATE:
			case Types::TYPE_DATETIME:
			case Types::TYPE_UTS:
				$sql .= ' '.$dialect->getNativeType($column->getType());
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if(!is_null($column->getDefault()))
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			break;
			
			case Types::TYPE_ENUM:
				$sql .= ' VARCHAR(64)';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if(!is_null($column->getDefault()))
					$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			break;
			
			default:
				throw new \InvalidArgumentException('unknown datatype: ' . $column->getType());
		}
		return $sql;
	}

	public static function createTableSQL(\Mammut\DB\DB $db, TableInfo $info) {
		$sql = 'CREATE TABLE ' . $db->escapeTableName($info->getName()) . " (\n";
		$next = false;
		
		$primary = $info->getPrimary();
		$indexes = $info->getIndexes();
		$refs = $info->getRefs();
		
		for($i = 0; $i < $info->getColumnCount(); $i++) {
			if($next)
				$sql .= ",\n";
			
			$column = $info->getColumnInfo((int) $i);
			$sql .= self::fieldDefSQL($db, $column, $primary);
/*			if (isset($refs[$column->getName()])) {
				$targetObj = $db->escapeTableName($refs[$column->getName()]['object']);
				$targetCol = $db->escapeColumnName($refs[$column->getName()]['column']);
				$sql .= ' REFERENCES '.$targetObj.' ('.$targetCol.')';
				unset($refs[$column->getName()]);
			}*/
			$next = true;
		}
		
		if(count($primary) != 0) {
			$sql .= ', PRIMARY KEY(' . implode(',', array_map(array($db,'escapeColumnName'), $primary)) . ')';
		}
		if(count($refs) != 0) {
			/** @var $ref \Mammut\DB\Model\RefInfo */
			foreach($refs as $name=>$ref) {
				if(substr_count($name, ",") > 0) {
					$fields = explode(",", $name);
					foreach($fields as &$f)
						$f = $db->escapeColumnName($f);
					$field = implode(',', $fields);
				}
				else
					$field = $db->escapeColumnName($name);
				
				$targetObj = $db->escapeTableName($ref->getRefTable());
				if(substr_count($ref->getRefColumns(), ",") > 0) {
					$fields = explode(",", $ref->getRefColumns());
					foreach($fields as &$f)
						$f = $db->escapeColumnName($f);
					$targetCol = implode(',', $fields);
				}
				else
					$targetCol = $db->escapeColumnName($ref->getRefColumns());

				$del = "ON DELETE CASCADE ";
				if($ref->getDelete() == RefInfo::ACT_NULL)
					$del = "ON DELETE SET NULL ";
				elseif ($ref->getDelete() == RefInfo::ACT_PREVENT)
					$del = "ON DELETE NO ACTION ";
				
				$up = "ON UPDATE CASCADE ";
				if($ref->getUpdate() == RefInfo::ACT_NULL)
					$up = "ON UPDATE SET NULL ";
				elseif ($ref->getUpdate() == RefInfo::ACT_PREVENT)
					$up = "ON UPDATE NO ACTION ";
				
				$sql .= ",FOREIGN KEY ({$field}) REFERENCES {$targetObj} ({$targetCol})  {$del} {$up}";
			}
		}
		$sql .= "\n);\n";
		if(count($indexes) != 0) {
			foreach($indexes as $name=>$values) {
				foreach($values as $id=>$val) {
					if(preg_match('#([a-zA-Z0-9_ ]*)\\(([0-9]+)\\)#', $val, $match))
						$values[$id] = $db->escapeColumnName($match[1]);
					else
						$values[$id] = $db->escapeColumnName($val);
				}
				$sql .= 'CREATE INDEX ' . $db->escapeColumnName($name) . ' ON ' . $db->escapeTableName($info->getName()) . ' (' . implode(',', $values) . ");\n";
			}
		}
		// add column comments
		for($i = 0; $i < $info->getColumnCount(); $i++) {
			$column = $info->getColumnInfo($i);
			switch($column->getType()) {
				
				case ColumnInfo::TYPE_BOOLEAN:
				break;
				
				case ColumnInfo::TYPE_TINY:
				break;
				
				case ColumnInfo::TYPE_SHORT:
				break;
				
				case ColumnInfo::TYPE_INT:
				break;
				
				case ColumnInfo::TYPE_LONG:
				break;
				
				case ColumnInfo::TYPE_FLOAT:
				break;
				
				case ColumnInfo::TYPE_DOUBLE:
				break;
				
				case ColumnInfo::TYPE_DECIMAL:
				break;
				
				case ColumnInfo::TYPE_CHAR:
				break;
				
				case ColumnInfo::TYPE_VCHAR:
				break;
				
				case ColumnInfo::TYPE_TEXT:
				break;
				
				case ColumnInfo::TYPE_BINARY:
				break;
				
				case ColumnInfo::TYPE_BLOB:
				break;
				
				case ColumnInfo::TYPE_DATE:
					$version = $db->getServerVersion();
//					if(version_compare($version, '10', '<')) {
						$sql .= "EXEC sys.sp_addextendedproperty " . "@name=N'MFType', @value=N'date'," . "@level0type=N'SCHEMA',@level0name=N'dbo'," . "@level1type=N'TABLE',@level1name=N" . $db->escapeValue($info->getName()) . "," . "@level2type=N'COLUMN',@level2name=N" . $db->escapeValue($column->getName()) . ";\n";
//					}
				break;
				
				case ColumnInfo::TYPE_DATETIME:
					$sql .= "EXEC sys.sp_addextendedproperty " . "@name=N'MFType', @value=N'datetime'," . "@level0type=N'SCHEMA',@level0name=N'dbo'," . "@level1type=N'TABLE',@level1name=N" . $db->escapeValue($info->getName()) . "," . "@level2type=N'COLUMN',@level2name=N" . $db->escapeValue($column->getName()) . ";\n";
				break;
				
				case ColumnInfo::TYPE_UTS:
					$sql .= "EXEC sys.sp_addextendedproperty " . "@name=N'MFType', @value=N'uts'," . "@level0type=N'SCHEMA',@level0name=N'dbo'," . "@level1type=N'TABLE',@level1name=N" . $db->escapeValue($info->getName()) . "," . "@level2type=N'COLUMN',@level2name=N" . $db->escapeValue($column->getName()) . ";\n";
				break;
				
				case ColumnInfo::TYPE_ENUM:
					$sql .= "EXEC sys.sp_addextendedproperty " . "@name=N'MFType', @value=N'enum(" . implode('|', $column->getParam()) . " )'," . "@level0type=N'SCHEMA',@level0name=N'dbo'," . "@level1type=N'TABLE',@level1name=N" . $db->escapeValue($info->getName()) . "," . "@level2type=N'COLUMN',@level2name=N" . $db->escapeValue($column->getName()) . ";\n";
				break;
			}
		}
		
		return $sql;
	}
	
	public static function getInfoSQLBase($db) {
		$sql = 
		'SELECT
			sch.name as [schema],
			tab.name as [table],
			ind.name as [index],
			col.name as [column],
			ind.type as [itype],
			ind.is_primary_key as [pkey],
			ind.is_unique as [unique],
			ind.is_unique_constraint as [unique_const]
		FROM ['.$db.'].sys.indexes ind
		INNER JOIN ['.$db.'].sys.index_columns	ico	ON ind.object_id = ico.object_id AND ind.index_id = ico.index_id
		INNER JOIN ['.$db.'].sys.columns 		col	ON ico.object_id = col.object_id AND ico.column_id = col.column_id
		INNER JOIN ['.$db.'].sys.tables			tab	ON ind.object_id = tab.object_id
		INNER JOIN ['.$db.'].sys.schemas		sch	ON tab.schema_id = sch.schema_id
		WHERE tab.is_ms_shipped = 0';
		return $sql;
	}
	
	/**
	 * Convert a database UCS-2LE string to UTF-8 encoding
	 * @param string $value
	 * @return string
	 */
	public static function ucs2utf($value) {
		return mb_detect_encoding($value, mb_detect_order(), true) === 'UTF-8' ? $value : mb_convert_encoding($value, 'UTF-8');
	}
	
	/**
	 * Convert a UTF-8 string to database UCS-2LE encoding
	 * @param string $value
	 * @return string
	 */
	public static function utf2ucs($value) {
		return	mb_convert_encoding($value, 'UCS-2LE', mb_detect_encoding($value, mb_detect_order(), true));
	}
}