<?php
/* $Id$ */
namespace Mammut\DB\Sql\Dialect\Ddl;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\Exception\IllegalStateException;
use Mammut\Exception\ImplementationException;
use Mammut\DB\Sql\Types;
use Mammut\DB\Sql\Dialect\iDialect;
use Mammut\DB\Model\TableInfo;
use Mammut\DB\Model\ViewInfo;
use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Model\RefInfo;

/**
 * PostgreSQL DDL dialect addon implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Dialect
 */
class PostgresqlDdl extends \Mammut\DB\Sql\Dialect\Postgresql implements iDialect, iDdlDialect {
	use DefaultDropTable;
	use DefaultDropView;
	use DefaultRenameTable;
	use DefaultDropColumn;
	use DefaultCheck;
	
	/**
	 * @return $this
	 */
	public function getDdl() {
		return $this;
	}

	public function getCreateTableSQL($param) {
		if ($param instanceof TableInfo)
			return $this->getCreateTableSQLFromInfo($param);
		throw new ImplementationException('not supported');
	}
	
	protected function getCreateTableSQLFromInfo(TableInfo $info) {
		$preSQL = array();
		$postSQL = array();
		
		if ($this->con instanceof \PDO) {
			$result = $this->con->query("SELECT current_schema() AS s");
			$schema = $result->fetchObject()->s;
			$result->closeCursor();
		}
		else {
			$result = pg_query($this->con, "SELECT current_schema() AS s");
			$schema = pg_fetch_object($result)->s;
			pg_free_result($result);
		}
		
		$tblRef = empty($schema) ?
			$this->quoteIdent($info->getName()) :
			$this->quoteIdent($schema) . '.' . $this->quoteIdent($info->getName());
		
		$sql = "CREATE TABLE {$tblRef} (\n";
		$next = false;
		
		$primary = $info->getPrimary();
		$indexes = $info->getIndexes();
		$refs = $info->getRefs();
		$checks = $info->getChecks();
		
		for($i = 0; $i < $info->getColumnCount(); $i++) {
			$column = $info->getColumnInfo($i);
			
			if($next)
				$sql .= ",\n";
			$sql .= "\t" . $this->quoteIdent($column->getName());
			
			$type = $column->getType();
			switch($type) {
				
				case ColumnInfo::TYPE_BOOLEAN:
					$sql .= ' '.$this->getNativeType($type);
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					break;
				
				case ColumnInfo::TYPE_TINY:
				case ColumnInfo::TYPE_SHORT:
				case ColumnInfo::TYPE_INT:
				case ColumnInfo::TYPE_LONG:
					$sql .= ' '.$this->getNativeType($type); // 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;
					
					switch($type) {
						case Types::TYPE_TINY:
							// add info that this is a tinyint column
							$postSQL[] = 'COMMENT ON COLUMN "' . $info->getName() . '"."' . $column->getName() . '" IS \'tiny\'';
							if($size > 4)
								throw new \OverflowException("size bigger than maximum size");
							break;
						case Types::TYPE_SHORT:
							if($size > 6)
								throw new \OverflowException("size bigger than maximum size");
							break;
						case Types::TYPE_INT:
							if($size > 11)
								throw new \OverflowException("size bigger than maximum size");
							break;
					}
					if($size > 0)
						$sql .= '(' . $size . ')';
						
					$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");
						
						$result = self::genNumSequence($info->getName(), $column->getName());
						$preSQL[] = $result['pre'];
						$sql .= ' DEFAULT nextval(\'"' . $result['name'] . '"\')';
						$postSQL[] = $result['post'];
					}
					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:
				case ColumnInfo::TYPE_VCHAR:
				case ColumnInfo::TYPE_BINARY:
					$sql .= ' '.$this->getNativeType($type);
					$size = $column->getSize();
					if(!is_int($size))
						throw new \InvalidArgumentException("size has to be a integer for type {$type}");
					if ($size == 0)
						$size = 255;
					
					$size = (int) $size;
					if($size > 0)
						$sql .= '(' . $size . ')';
						
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
				break;
				
				
				case ColumnInfo::TYPE_TEXT:
				case ColumnInfo::TYPE_BLOB:
					$sql .= ' '.$this->getNativeType($type);
					$sql .= $column->isNullAllowed() ? ' NULL' : ' NOT NULL';
					break;
				
				case ColumnInfo::TYPE_DATE:
				case ColumnInfo::TYPE_DATETIME:
				case ColumnInfo::TYPE_UTS:
					$sql .= ' '.$this->getNativeType($type);
					$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 ' . $this->quoteValue($column->getDefault());

			$next = true;
		}
		$prefix = '';
		$postfix = '';
		if(count($primary) != 0)
			$sql .= ",\nPRIMARY KEY(" . implode(',', array_map(array($this,'quoteIdent'), $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 = $this->quoteIdent($f);
					$field = implode(',', $fields);
				}
				else
					$field = $this->quoteIdent($name);
		
				$targetObj = $this->quoteIdent($ref->getRefTable());
				if(substr_count($ref->getRefColumns(), ",") > 0) {
					$fields = explode(",", $ref->getRefColumns());
					foreach($fields as &$f)
						$f = $this->quoteIdent($f);
					$targetCol = implode(',', $fields);
				}
				else
					$targetCol = $this->quoteIdent($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 .= ",\nFOREIGN KEY ({$field}) REFERENCES {$targetObj} ({$targetCol})  {$del} {$up}";
			}
		}
		if(count($indexes) != 0) {
			foreach($indexes as $identifier=>$values) {
				foreach($values as $id=>$val) {
					if(preg_match('#([a-zA-Z0-9_ ]*)\\(([0-9]+)\\)#', $val, $match))
						$values[$id] = $this->quoteIdent($match[1]) . '(' . $match[2] . ')';
					else
						$values[$id] = $this->quoteIdent($val);
				}
				$postfix .= 'CREATE INDEX ' . $this->quoteIdent($info->getName().'_'.$identifier) . " ON {$tblRef} (" . implode(',', $values) . ");\n";
			}
		}
		if(count($checks) != 0)
			$sql .= $this->getChecksSQL($checks);
		$sql .= "\n)";
		foreach($preSQL as $q)
			$prefix .= $q . ";\n";
		foreach($postSQL as $q)
			$postfix .= $q . ";\n";
		$sql = $prefix . $sql . ";\n" . $postfix;
// 		echo $sql;
		return $sql;
	}

	public function getAlterTableSQL($param) {
		throw new ImplementationException('not supported');
	}

	public function getCreateViewSQL($param) {
		if ($param instanceof ViewInfo)
			return $this->getCreateViewSQLFromInfo($param);
		throw new ImplementationException('not supported');
	}
	
	protected function getCreateViewSQLFromInfo(ViewInfo $info) {
		$sql = 'CREATE OR REPLACE VIEW ' . $this->quoteIdent($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 = $this->quoteIdent($column['base']);
			$col = $this->quoteIdent($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 .= $this->quoteIdent($t);
			$next = true;
		}
		
		$joins = $info->getJoinObjects();
		foreach($joins as $j) {
			$jInfo = $info->getJoinInfo($j);
			switch($jInfo['type']) {
				case 'inner':
					$obj = $this->quoteIdent($j);
					$sql .= " JOIN {$obj}\n";
					$cons = array();
					foreach($jInfo['cond'] as $con) {
						$leftObjs = explode('.', $con['left']);
						if(count($leftObjs) == 2) {
							$left = $this->quoteIdent($leftObjs[0]) . '.' . $this->quoteIdent($leftObjs[1]);
						}
						$rightObjs = explode('.', $con['right']);
						if(count($rightObjs) == 2) {
							$right = $this->quoteIdent($rightObjs[0]) . '.' . $this->quoteIdent($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;
		
	}
}