<?php
/* $Id$ */
namespace Mammut\DB\Oracle;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Exception\ImplementationException;
use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Model\TableInfo;

/**
 * Oracle utility library
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\Oracle
 */
class OracleUtil extends \Mammut\StrictObject {

	private function __construct() {
	} // only static access

	public static function createTableSQL(Oracle $db, TableInfo $info) {
		$preSQL = array();
		$postSQL = array();
		$sql = 'CREATE TABLE ' . $db->escapeTableName($info->getName()) . " (\n";
		$next = false;
		
		$primary = $info->getPrimary();
		
		for($i = 0; $i < $info->getColumnCount(); $i++) {
			$column = $info->getColumnInfo($i);
			
			if($next)
				$sql .= ",\n";
			$sql .= "\t" . $db->escapeColumnName($column->getName());
			switch($column->getType()) {
				
				case ColumnInfo::TYPE_BOOLEAN:
					$sql .= ' NUMBER(1)';
					$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 .= ' NUMBER(3)'; // oracle 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 . ')';
						
						// $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");
						
						$seqName = $info->getName() . '_' . strtolower($column->getName()) . '_seq';
						$trigName = $info->getName() . '_' . strtolower($column->getName()) . '_instrig';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						// $sql .= ' DEFAULT (SELECT '.$seqName.'.NEXTVAL from dual)';
						$postSQL[] = 'CREATE OR REPLACE TRIGGER "' . $trigName . '"
 BEFORE INSERT ON ' . $db->escapeTableName($info->getName()) . ' FOR EACH ROW
 BEGIN
     IF :new."' . $column->getName() . '" IS NULL THEN
         SELECT "' . $seqName . '".nextval INTO :new."' . $column->getName() . '" FROM dual;
     END IF;
 END;\\';
					}
				break;
				
				case ColumnInfo::TYPE_SHORT:
					$sql .= ' NUMBER(5)';
					$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' : '';
					

					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';
						$trigName = $info->getName() . '_' . strtolower($column->getName()) . '_instrig';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						// $sql .= ' DEFAULT (SELECT '.$seqName.'.NEXTVAL from dual)';
						$postSQL[] = 'CREATE OR REPLACE TRIGGER "' . $trigName . '"
 BEFORE INSERT ON ' . $db->escapeTableName($info->getName()) . ' FOR EACH ROW
 BEGIN
     IF :new."' . $column->getName() . '" IS NULL THEN
         SELECT "' . $seqName . '".nextval INTO :new."' . $column->getName() . '" FROM dual;
     END IF;
 END;\\';
					}
				break;
				
				case ColumnInfo::TYPE_INT:
					$sql .= ' NUMBER(10)';
					$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' : '';
					

					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';
						$trigName = $info->getName() . '_' . strtolower($column->getName()) . '_instrig';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						// $sql .= ' DEFAULT (SELECT '.$seqName.'.NEXTVAL from dual)';
						$postSQL[] = 'CREATE OR REPLACE TRIGGER "' . $trigName . '" BEFORE INSERT ON ' . $db->escapeTableName($info->getName()) . ' FOR EACH ROW BEGIN IF :new."' . $column->getName() . '" IS NULL THEN SELECT "' . $seqName . '".nextval INTO :new."' . $column->getName() . '" FROM dual;  END IF; END;';
					}
				break;
				
				case ColumnInfo::TYPE_LONG:
					$sql .= ' NUMBER(19)';
					$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' : '';
					

					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';
						$trigName = $info->getName() . '_' . strtolower($column->getName()) . '_instrig';
						$preSQL[] = 'CREATE SEQUENCE "' . $seqName . '"';
						// $sql .= ' DEFAULT (SELECT '.$seqName.'.NEXTVAL from dual)';
						$postSQL[] = 'CREATE OR REPLACE TRIGGER "' . $trigName . '"
 BEFORE INSERT ON ' . $db->escapeTableName($info->getName()) . ' FOR EACH ROW
 BEGIN
     IF :new."' . $column->getName() . '" IS NULL THEN
         SELECT "' . $seqName . '".nextval INTO :new."' . $column->getName() . '" FROM dual;
     END IF;
 END;\\';
					}
				break;
				
				case ColumnInfo::TYPE_FLOAT:
					$sql .= ' BINARY_FLOAT';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("precicion has to be a integer for type real");
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				break;
				
				case ColumnInfo::TYPE_DOUBLE:
					$sql .= ' BINARY_DOUBLE';
					$size = $column->getSize();
					if(!is_int($size))
						throw new InvalidArgumentException("precicion has to be a integer for type double");
					
					// $sql .= $column->isUnsigned()? ' UNSIGNED' : '';
				break;
				
				case ColumnInfo::TYPE_DECIMAL:
					$sql .= ' NUMBER';
					$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' : '';
				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());
				break;
				
				case ColumnInfo::TYPE_VCHAR:
					$sql .= ' VARCHAR2';
					$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());
				break;
				
				case ColumnInfo::TYPE_TEXT:
					$sql .= ' NCLOB';
				
				//	$sql .= ' '.self::createCollateSQL($column->getCharset(), $column->getCollate());
				break;
				
				case ColumnInfo::TYPE_BINARY:
					$sql .= ' RAW(255)';
				// $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.')'; 
				break;
				
				case ColumnInfo::TYPE_BLOB:
					$sql .= ' BLOB';
				break;
				
				case ColumnInfo::TYPE_DATE:
					$sql .= ' DATE';
				break;
				
				case ColumnInfo::TYPE_DATETIME:
					$sql .= ' TIMESTAMP';
				break;
				
				case ColumnInfo::TYPE_UTS:
					$sql .= ' INT';
					$sql .= $column->isUnsigned() ? ' UNSIGNED' : '';
				break;
				
				case ColumnInfo::TYPE_ENUM:
					$sql .= ' VARCHAR2(64)';
				break;
			}
			
			if(!is_null($column->getDefault()))
				$sql .= ' DEFAULT ' . $db->escapeValue($column->getDefault());
			
			$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
			
			//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)";
		$result = array();
		foreach($preSQL as $q)
			$result[] = $q;
		$result[] = $sql;
		foreach($postSQL as $q)
			$result[] = $q;
		return $result;
	}
}