<?php
/* $Id$ */
namespace Mammut\DB\MySQLi;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use \Mammut\DB\DB;
use \Mammut\DB\Charset;
use \Mammut\DB\Model\TableInfo;
use \Mammut\DB\Model\ColumnInfo;
use \Mammut\DB\Model\ViewInfo;
use \Mammut\Exception\ImplementationException;
use Mammut\DB\SQL\Types;

/**
 * MySQL utility library
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\MySQL
 */
class MySQLUtil extends \Mammut\StrictObject {
	const MYSQL_TYPE_BIT = 16;
	const MYSQL_TYPE_TINYINT = 1;
	const MYSQL_TYPE_BOOL = 1;
	const MYSQL_TYPE_SMALLINT = 2;
	const MYSQL_TYPE_MEDIUMINT = 9;
	const MYSQL_TYPE_INTEGER = 3;
	const MYSQL_TYPE_BIGINT = 8;
	const MYSQL_TYPE_SERIAL = 8;
	const MYSQL_TYPE_FLOAT = 4;
	const MYSQL_TYPE_DOUBLE = 5;
	const MYSQL_TYPE_DECIMAL = 246;
	const MYSQL_TYPE_NUMERIC = 246;
	const MYSQL_TYPE_FIXED = 246;
	const MYSQL_TYPE_DATE = 10;
	const MYSQL_TYPE_DATETIME = 12;
	const MYSQL_TYPE_TIMESTAMP = 7;
	const MYSQL_TYPE_TIME = 11;
	const MYSQL_TYPE_YEAR = 13;
	const MYSQL_TYPE_CHAR = 254;
	const MYSQL_TYPE_VARCHAR = 253;
	const MYSQL_TYPE_ENUM = 254;
	const MYSQL_TYPE_SET = 254;
	const MYSQL_TYPE_BINARY = 254;
	const MYSQL_TYPE_VARBINARY = 253;
	const MYSQL_TYPE_TINYBLOB = 252;
	const MYSQL_TYPE_BLOB = 252;
	const MYSQL_TYPE_MEDIUMBLOB = 252;
	const MYSQL_TYPE_TINYTEXT = 252;
	const MYSQL_TYPE_TEXT = 252;
	const MYSQL_TYPE_MEDIUMTEXT = 252;
	const MYSQL_TYPE_LONGTEXT = 252;
	const MYSQL_FLAG_NOT_NULL = 1;
	const MYSQL_FLAG_PRI_KEY = 2;
	const MYSQL_FLAG_UNIQUE_KEY = 4;
	const MYSQL_FLAG_BLOB = 16;
	const MYSQL_FLAG_UNSIGNED = 32;
	const MYSQL_FLAG_ZEROFILL = 64;
	const MYSQL_FLAG_BINARY = 128;
	const MYSQL_FLAG_ENUM = 256;
	const MYSQL_FLAG_AUTO_INCREMENT = 512;
	const MYSQL_FLAG_TIMESTAMP_FLAG = 1024;
	const MYSQL_FLAG_SET_FLAG = 2048;
	const MYSQL_FLAG_NUM_FLAG = 32768;
	const MYSQL_FLAG_PART_KEY_FLAG = 16384;
	const MYSQL_FLAG_GROUP_FLAG = 32768;
	const MYSQL_FLAG_UNIQUE_FLAG = 65536;
	const MYSQL_MARIA = 'mariadb';
	const MYSQL_CLUSTER = 'ndb';
	const MYSQL_GALERA = 'galera';

	private function __construct() {
	} // only static access

	
	/**
	 * creates a type attribute sql string which describes a charset/collate setting
	 *
	 * @param string $charset
	 *        	of the Charset::CS_* constants
	 * @param string $collate
	 *        	of the Charset::COL_* constants
	 * @return string resulting sql code
	 */
	protected static function createCollateSQL($charset, $collate) {
		$sql = '';
		
		switch($charset) {
			case Charset::CS_ASCII:
				$sql .= ' CHARACTER SET ascii';
				switch($collate) {
					case Charset::COL_BIN:
						$sql = ' COLLATE ascii_bin';
					break;
					case Charset::COL_GEN_CI:
						$sql = ' COLLATE ascii_general_ci';
					break;
					case Charset::COL_GEN_CS:
						throw new \InvalidArgumentException('unsupported collation');
					break;
					case Charset::COL_UNI:
						throw new \InvalidArgumentException('unsupported collation');
					break;
				}
			break;
			
			case Charset::CS_BINARY:
				$sql .= ' CHARACTER SET binary';
				if(!empty($collate))
					throw new \InvalidArgumentException('binary dosen\'t supported collation');
			break;
			
			case Charset::CS_LATIN1:
				$sql .= ' CHARACTER SET latin1';
				switch($collate) {
					case Charset::COL_BIN:
						$sql .= ' COLLATE latin1_bin';
					break;
					case Charset::COL_GEN_CI:
						$sql .= ' COLLATE latin1_general_ci';
					break;
					case Charset::COL_GEN_CS:
						$sql .= ' COLLATE latin1_general_cs';
					break;
					case Charset::COL_UNI:
						throw new \InvalidArgumentException('unsupported collation');
					break;
				}
			break;
			
			case Charset::CS_UTF8:
				$sql .= ' CHARACTER SET utf8';
				switch($collate) {
					case Charset::COL_BIN:
						$sql .= ' COLLATE utf8_bin';
					break;
					case Charset::COL_GEN_CI:
						$sql .= ' COLLATE utf8_general_ci';
					break;
					case Charset::COL_GEN_CS:
						throw new \InvalidArgumentException('unsupported collation');
					break;
					case Charset::COL_UNI:
						$sql .= ' COLLATE utf8_unicode_ci';
					break;
				}
			break;
			
			default:
			break;
		}
		return $sql;
	}

	public static function fieldDefSQL(DB $db, ColumnInfo $column, $primary = array()) {
		$sql = $db->escapeColumnName($column->getName());
		$dialect = $db->getDialect();
		
		$type = $column->getType();
		switch($type) {
			case Types::TYPE_BLOB:
			case Types::TYPE_DATE:
			case Types::TYPE_DATETIME:
			case Types::TYPE_BOOLEAN:
				$sql .= ' '.$dialect->getNativeType($type);
			break;
			
			case Types::TYPE_TINY:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 4)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' AUTO_INCREMENT';
				}
			break;
			
			case Types::TYPE_SHORT:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 6)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' AUTO_INCREMENT';
				}
			break;
			
			case Types::TYPE_INT:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 11)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' AUTO_INCREMENT';
				}
			break;
			
			case Types::TYPE_LONG:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 0)
					$sql .= '(' . $size . ')';
				
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
				
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new \InvalidArgumentException("serial fields needs to be in the primary key");
					$sql .= ' AUTO_INCREMENT';
				}
			break;
			
			case Types::TYPE_FLOAT:
			case Types::TYPE_DOUBLE:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
			break;
			
			case Types::TYPE_DECIMAL:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_array($size))
					throw new \InvalidArgumentException("size has to be an array for type {$type}");
				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->isUnsigned() ? ' UNSIGNED' : '';
			break;
			
			case Types::TYPE_CHAR:
			case Types::TYPE_VCHAR:
			case Types::TYPE_BINARY:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				
				$size = (int) $size;
				if($size > 0)
					$sql .= '(' . $size . ')';
				else
					$sql .= '(255)';
				
				if ($type != Types::TYPE_BINARY)
					$sql .= ' ' . self::createCollateSQL($column->getCharset(), $column->getCollate());
			break;
			
			case Types::TYPE_TEXT:
				$sql .= ' '.$dialect->getNativeType($type);
				$sql .= ' ' . self::createCollateSQL($column->getCharset(), $column->getCollate());
			break;
			
			case Types::TYPE_UTS:
				$sql .= ' '.$dialect->getNativeType($type);
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
			break;
			
			case Types::TYPE_ENUM:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $column->getSize();
				$values = array();
				foreach($column->getParam() as $e) {
					if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
						$values = explode(',', $match[1]);
						break;
					}
				}
				$sql .= '(\'' . implode('\',\'', $values) . '\')';
			break;
			
			default:
				throw new \InvalidArgumentException('unknown datatype: ' . $type);
		}
		$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
		
		if(!is_null($column->getDefault()))
			$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
		
		switch($type) {
			
			case Types::TYPE_BOOLEAN:
				$sql .= ' COMMENT \'boolean\'';
			break;
			
			case Types::TYPE_UTS:
				$sql .= ' COMMENT \'uts\'';
			break;
		}
		
		return $sql;
	}

	public static function createAlterColumnSQLs(DB $db, $table, $colName, ColumnInfo $colInfo) {
		$preSQL = array();
		$mainSQL = array();
		$postSQL = array();
		
		//echo "Old name: $colName\n";
		

		$tableInfo = $db->table($table)->getInfo();
		$oldInfo = $tableInfo->getColumnInfo($colName);
		$dialect = $db->getDialect();
		
		// echo "Old info:\n";
		// var_dump($oldInfo);
		// echo "New info:\n";
		// var_dump($colInfo);
		$oldColName = false;
		
		$sql = "ALTER TABLE " . $db->escapeTableName($table);
		$sql .= " CHANGE COLUMN " . $dialect->quoteIdent($colName) . ' ' . $dialect->quoteIdent($colInfo->getName());
		
		$type = $colInfo->getType();
		switch($type) {
			case Types::TYPE_BOOLEAN:
				$sql .= ' '.$dialect->getNativeType($type);
			break;
			
			case Types::TYPE_TINY:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 4)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
			break;
			
			case Types::TYPE_SHORT:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 6)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
			break;
			
			case Types::TYPE_INT:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 11)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
			break;
			
			case Types::TYPE_LONG:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 13)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
			break;
			
			case Types::TYPE_FLOAT:
			case Types::TYPE_DOUBLE:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("precicion has to be a integer for type {$type}");
			break;
			
			case Types::TYPE_DECIMAL:
				die('not implemented');
				$sql .= ' '.$dialect->getNativeType($type);
				$mainSQL[] = $sqlbase . ' DECIMAL';
				$size = $colInfo->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 . ')';
			break;
			
			case Types::TYPE_CHAR:
			case Types::TYPE_VCHAR:
			case Types::TYPE_BINARY:
				$sql .= ' '.$dialect->getNativeType($type);
				$size = $colInfo->getSize();
				if(!is_int($size))
					throw new \InvalidArgumentException("size has to be a integer for type {$type}");
				$size = (int) $size;
				if($size > 255)
					throw new \OverflowException("size bigger than maximum size");
				if($size > 0)
					$sql .= '(' . $size . ')';
			break;
			
			case Types::TYPE_TEXT:
			case Types::TYPE_BLOB:
			case Types::TYPE_DATE:
			case Types::TYPE_DATETIME:
			case Types::TYPE_UTS:
				$sql .= ' '.$dialect->getNativeType($type);
			break;
			
			case Types::TYPE_ENUM:
				throw new ImplementationException('not implemented');
				$values = array();
				foreach($colInfo->getParam() as $e) {
					if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
						$values = explode(',', $match[1]);
						break;
					}
				}
				$enuName = $info->getName() . '_' . strtolower($colInfo->getName()) . '_enum';
				
				if($oldColName) {
					$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				}
				
				$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
				
				$sql .= ' "' . $enuName . '"';
			break;
		}
		
		$sql .= ($colInfo->isUnsigned() ? ' UNSIGNED' : '');
		$sql .= ($colInfo->isNullAllowed() ? '' : ' NOT') . ' NULL';
		$sql .= ($colInfo->isAutoNum() ? ' AUTO_INCREMENT' : '');
		
		switch($type) {
			case Types::TYPE_BOOLEAN:
				$sql .= ' COMMENT \'boolean\'';
				break;
					
			case Types::TYPE_UTS:
				$sql .= ' COMMENT \'uts\'';
				break;
		}
		
		$result = array('pre' => $preSQL,'main' => array($sql),'post' => $postSQL);
		return $result;
	}

// 	/**
// 	 * creates a mysql ddl create table statement
// 	 *
// 	 * @param $db DB
// 	 *        	connection
// 	 * @param $info ViewInfo
// 	 *        	info
// 	 * @return string the ddl statement
// 	 */
// 	public static function createViewSQL(DB $db, ViewInfo $info, $cluster = false) {
// 		$sql = 'CREATE OR REPLACE VIEW ' . $db->escapeTableName($info->getName()) . " AS \n";
// 		$next = false;
		
// 		/*			$primary = $info->getPrimary();
// 			$indexes = $info->getIndexes();
// 		$refs = $info->getRefs();*/
// 		$sql .= "SELECT  \n";
		
// 		$tables = array($info->getBase());
// 		for($i = 0; $i < $info->getColumnCount(); $i++) {
// 			if($next)
// 				$sql .= ",\n";
			
// 			$column = $info->getColumnInfo($i);
// 			$base = $db->escapeTableName($column['base']);
// 			$col = $db->escapeColumnName($column['basecolumn']);
// //			$tables[] = $column['base'];
			

// 			$sql .= $base . "." . $col;
			
// 			$next = true;
// 		}
// 		$tables = array_unique($tables);
		
// 		$sql .= " FROM \n";
		
// 		$next = false;
// 		foreach($tables as $t) {
// 			if($next)
// 				$sql .= ",\n";
// 			$sql .= $db->escapeTableName($t);
// 			$next = true;
// 		}
		
// 		$joins = $info->getJoinObjects();
// 		foreach($joins as $j) {
// 			$jInfo = $info->getJoinInfo($j);
// 			switch($jInfo['type']) {
// 				case 'inner':
// 					$obj = $db->escapeTableName($j);
// 					$sql .= " JOIN {$obj}\n";
// 					$cons = array();
// 					foreach($jInfo['cond'] as $con) {
// 						$leftObjs = explode('.', $con['left']);
// 						if(count($leftObjs) == 2) {
// 							$left = $db->escapeTableName($leftObjs[0]) . '.' . $db->escapeColumnName($leftObjs[1]);
// 						}
// 						$rightObjs = explode('.', $con['right']);
// 						if(count($rightObjs) == 2) {
// 							$right = $db->escapeTableName($rightObjs[0]) . '.' . $db->escapeColumnName($rightObjs[1]);
// 						}
// 						switch($con['operator']) {
// 							case '=':
// 								$operator = '=';
// 							break;
// 							default:
// 								throw new \Mammut\DB\DBException("unsupported operator: {$con['operator']}");
// 						}
// 						$cons[] = $left . $operator . $right;
// 						unset($left, $operator, $right, $leftObj, $rightObjs);
// 					}
// 					if(count($cons) > 0) {
// 						$sql .= 'ON ' . implode(' AND ', $cons);
// 					}
// 				break;
// 				default:
// 					throw new \Mammut\DB\DBException("unsupported join type: {$jInfo['type']}");
// 			}
// 			// var_dump($jInfo);
			

// 			//$sql .= 'AND '.$basea.'.'.$cola.'='.$baseb.'.'.$colb;
// 		}
		
// 		$sql .= ";\n";
// 		//echo $sql;
// 		return $sql;
// 	}
}