<?php
/* $Id$ */
namespace Mammut\DB\DB2;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use OverflowException;
use InvalidArgumentException;
use Mammut\Exception\ImplementationException;
use Mammut\DB\DB;
use Mammut\DB\Model\TableInfo;

/**
 * IBM DB2 utility library
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\DB2
 */
class DB2Util extends \Mammut\StrictObject {

	private function __construct() {
	} // only static access

	public static function fieldDefSQL(DB $db, ColumnInfo $column) {
		$preSQL = array();
		$postSQL = array();
		$sql = "\t" . $db->escapeColumnName($column->getName());
		switch($column->getType()) {
			
			case ColumnInfo::TYPE_BOOLEAN:
				$sql .= ' BOOLEAN';
				$size = $column->getSize();
				if(!is_int($size))
					throw new InvalidArgumentException("size has to be a integer for type boolean");
			break;
			
			case ColumnInfo::TYPE_TINY:
				$sql .= ' SMALLINT'; // pgsql has no tinyint type
				$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 . ')';
					
					// add info that this is a tinyint column
				$postSQL[] = 'COMMENT ON COLUMN "' . $info->getName() . '"."' . $column->getName() . '" IS \'tiny\'';
				
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
					$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . strtolower($seqName) . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '"';
				}
			break;
			
			case ColumnInfo::TYPE_SHORT:
				$sql .= ' SMALLINT';
				$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 . ')';
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				

				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
					$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '"';
				}
			break;
			
			case ColumnInfo::TYPE_INT:
				$sql .= ' INTEGER';
				$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 . ')';
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				

				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
					$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
					$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '"';
				}
			break;
			
			case ColumnInfo::TYPE_LONG:
				$sql .= ' BIGINT';
				$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 . ')';
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				

				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				if($column->isAutoNum()) {
					if(!in_array($column->getName(), $primary))
						throw new InvalidArgumentException("serial fields needs to be in the primary key");
					
					$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
					$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '";';
					$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
					$postSQL[] = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '";';
				}
			break;
			
			case ColumnInfo::TYPE_FLOAT:
				$sql .= ' REAL';
				$size = $column->getSize();
				if(!is_int($size))
					throw new InvalidArgumentException("precicion has to be a integer for type real");
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_DOUBLE:
				$sql .= ' DOUBLE PRECISION';
				$size = $column->getSize();
				if(!is_int($size))
					throw new InvalidArgumentException("precicion has to be a integer for type double");
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_DECIMAL:
				$sql .= ' DECIMAL';
				$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->isUnsigned()? ' UNSIGNED' : '';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_CHAR:
				$sql .= ' CHAR';
				$size = $column->getSize();
				if(!is_int($size))
					throw new InvalidArgumentException("size has to be a integer for type char");
				
				$size = (int) $size;
				if($size > 0)
					$sql .= '(' . $size . ')';
					
					//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_VCHAR:
				$sql .= ' VARCHAR';
				$size = $column->getSize();
				if(!is_int($size))
					throw new InvalidArgumentException("size has to be a integer for type vchar");
				
				$size = (int) $size;
				if($size > 0)
					$sql .= '(' . $size . ')';
					
					//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_TEXT:
				$sql .= ' TEXT';
				
				//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_BINARY:
				$sql .= ' BYTEA';
				// $size = $column->getSize();
				//if (!is_int($size))
				//	throw new InvalidArgumentException("size has to be a integer for type binary");
				// $size = (int) $size;
				//if ($size > 0)
				//	$sql .= '('.$size.')'; 
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_BLOB:
				$sql .= ' BYTEA';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_DATE:
				$sql .= ' DATE';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_DATETIME:
				$sql .= ' TIMESTAMP';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_UTS:
				$sql .= ' INT';
				$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
			
			case ColumnInfo::TYPE_ENUM:
				$values = array();
				foreach($column->getParam() as $e) {
					if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
						$values = explode(',', $match[1]);
						break;
					}
				}
				$enuName = $info->getName() . '_' . strtolower($column->getName()) . '_enum';
				$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
				$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
				
				$sql .= ' "' . $enuName . '"';
				$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			break;
		}
		
		if(!is_null($column->getDefault()))
			$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			
			//if (in_array($column->getName(), $primary))
			//	$sql .= ' PRIMARY KEY';
		return $sql;
	}

	public static function createTableSQL(DB $db, TableInfo $info, $schema = null) {
		$preSQL = array();
		$postSQL = array();
		if(is_null($schema))
			$sql = 'CREATE TABLE ' . $db->escapeTableName($info->getName()) . " (\n";
		else
			$sql = 'CREATE TABLE ' . $db->escapeTableName($schema) . '.' . $db->escapeTableName($info->getName()) . " (\n";
		$next = false;
		
		$primary = $info->getPrimary();
		
		for($i = 0; $i < $info->getColumnCount(); $i++) {
			$column = $info->getColumn($i);
			
			if($next)
				$sql .= ",\n";
			$sql .= "\t" . $db->escapeColumnName($column->getName());
			switch($column->getType()) {
				
				case ColumnInfo::TYPE_BOOLEAN:
					$sql .= ' BOOLEAN';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("size has to be a integer for type boolean");
				break;
				
				case ColumnInfo::TYPE_TINY:
					$sql .= ' SMALLINT'; // pgsql has no tinyint type
					$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 . ')';
						
						// add info that this is a tinyint column
					$postSQL[] = 'COMMENT ON COLUMN "' . $info->getName() . '"."' . $column->getName() . '" IS \'tiny\'';
					
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					if($column->isAutoNum()) {
						if(!in_array($column->getName(), $primary))
							throw new InvalidArgumentException("serial fields needs to be in the primary key");
						
						$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
						$postSQL[] = 'ALTER SEQUENCE "' . strtolower($seqName) . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '"';
					}
				break;
				
				case ColumnInfo::TYPE_SHORT:
					$sql .= ' SMALLINT';
					$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 . ')';
						
						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					

					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					if($column->isAutoNum()) {
						if(!in_array($column->getName(), $primary))
							throw new InvalidArgumentException("serial fields needs to be in the primary key");
						
						$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
						$postSQL[] = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '"';
					}
				break;
				
				case ColumnInfo::TYPE_INT:
					$sql .= ' INTEGER';
					$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 . ')';
						
						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					

					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					if($column->isAutoNum()) {
						if(!in_array($column->getName(), $primary))
							throw new InvalidArgumentException("serial fields needs to be in the primary key");
						
						$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
						$postSQL[] = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '"';
					}
				break;
				
				case ColumnInfo::TYPE_LONG:
					$sql .= ' BIGINT';
					$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 . ')';
						
						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					

					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					if($column->isAutoNum()) {
						if(!in_array($column->getName(), $primary))
							throw new InvalidArgumentException("serial fields needs to be in the primary key");
						
						$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '";';
						$sql .= ' DEFAULT nextval(\'"' . $seqName . '"\')';
						$postSQL[] = 'ALTER SEQUENCE "' . $seqName . '" OWNED BY "' . $info->getName() . '"."' . $column->getName() . '";';
					}
				break;
				
				case ColumnInfo::TYPE_FLOAT:
					$sql .= ' REAL';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("precicion has to be a integer for type real");
						
						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_DOUBLE:
					$sql .= ' DOUBLE PRECISION';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("precicion has to be a integer for type double");
						
						// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_DECIMAL:
					$sql .= ' DECIMAL';
					$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->isUnsigned()? ' UNSIGNED' : '';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_CHAR:
					$sql .= ' CHAR';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("size has to be a integer for type char");
					
					$size = (int) $size;
					if($size > 0)
						$sql .= '(' . $size . ')';
						
						//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_VCHAR:
					$sql .= ' VARCHAR';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("size has to be a integer for type vchar");
					
					$size = (int) $size;
					if($size > 0)
						$sql .= '(' . $size . ')';
						
						//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_TEXT:
					$sql .= ' TEXT';
					
					//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_BINARY:
					$sql .= ' BYTEA';
					// $size = $column->getSize();
					//if (!is_int($size))
					//	throw new InvalidArgumentException("size has to be a integer for type binary");
					// $size = (int) $size;
					//if ($size > 0)
					//	$sql .= '('.$size.')'; 
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_BLOB:
					$sql .= ' BYTEA';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_DATE:
					$sql .= ' DATE';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_DATETIME:
					$sql .= ' TIMESTAMP';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_UTS:
					$sql .= ' INT';
					$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				case ColumnInfo::TYPE_ENUM:
					$values = array();
					foreach($column->getParam() as $e) {
						if(preg_match('#values\(([a-zA-Z0-9_,]*)\)#', $e, $match)) {
							$values = explode(',', $match[1]);
							break;
						}
					}
					$enuName = $info->getName() . '_' . strtolower($column->getName()) . '_enum';
					$preSQL[] = "DROP TYPE IF EXISTS \"{$enuName}\"";
					$preSQL[] = "CREATE TYPE \"{$enuName}\" AS ENUM ('" . implode("','", $values) . "')";
					
					$sql .= ' "' . $enuName . '"';
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
			}
			
			if(!is_null($column->getDefault()))
				$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
				
				//if (in_array($column->getName(), $primary))
				//	$sql .= ' PRIMARY KEY';
			

			$next = true;
		}
		$prefix = '';
		$postfix = '';
		if(count($primary) != 0) {
			$sql .= ', PRIMARY KEY(' . implode(',', array_map(array($db,'escapeColumnName'), $primary)) . ')';
		}
		$sql .= "\n)";
		foreach($preSQL as $q)
			$prefix .= $q . ";\n";
		foreach($postSQL as $q)
			$postfix .= $q . ";\n";
		$sql = $prefix . $sql . ";\n" . $postfix;
		return $sql;
	}
}