<?php
/* $Id$ */
namespace Mammut\DB\MSSQL;

(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;
use Mammut\DB\DB;

/**
 * MSSQL table implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\MSSQL
 */
class Table extends \Mammut\DB\Table {

	public function __construct(DB &$db, $name) {
		parent::__construct($db, $name);
	}

	public function getInfo() {
		if(is_object($this->info))
			return $this->info;
		$table = $this->getName();
		$info = new TableInfo($table);
		
		$result = $this->db->query("SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{$table}'");
		while($row = $result->fetchArray()) {
			$name = $row['COLUMN_NAME'];
			$type = '';
			$size = -1;
			$param = array();
			$charset = NULL;
			$collate = NULL;
			$default = isset($row['COLUMN_DEFAULT']) ? $row['COLUMN_DEFAULT'] : false; 
			
			if(preg_match('#^([a-zA-Z]+)#', $row['DATA_TYPE'], $match)) {
				if($row['IS_NULLABLE'] == 'YES')
					$param[] = ColumnInfo::P_ALLOW_NULL;
				switch(strtolower(trim($match[1]))) {
					case 'tinyint':
						$type = ColumnInfo::TYPE_TINY;
					break;
					case 'smallint':
						$type = ColumnInfo::TYPE_SHORT;
					break;
					case 'int':
						$type = ColumnInfo::TYPE_INT;
					break;
					case 'bigint':
						$type = ColumnInfo::TYPE_LONG;
					break;
					case 'float':
						if ($row['NUMERIC_PRECISION']>50) {
							$type = ColumnInfo::TYPE_DOUBLE;
							break;
						}
					case 'real':
						$type = ColumnInfo::TYPE_FLOAT;
					break;
					case 'char':
					case 'nchar':
						$type = ColumnInfo::TYPE_CHAR;
						$size = (int) $row['CHARACTER_MAXIMUM_LENGTH'];
					break;
					case 'varchar':
					case 'nvarchar':
						$type = ColumnInfo::TYPE_VCHAR;
						$size = (int) $row['CHARACTER_MAXIMUM_LENGTH'];
					break;
					case 'date':
						$type = ColumnInfo::TYPE_DATE;
					break;
					case 'datetime':
						$type = ColumnInfo::TYPE_DATETIME;
					break;
					case 'bit':
						$type = ColumnInfo::TYPE_BOOLEAN;
					break;
					case 'text':
					case 'ntext':
						$type = ColumnInfo::TYPE_TEXT;
					break;
					case 'varbinary':
						$type = ColumnInfo::TYPE_BINARY;
					break;
					case 'image':
						$type = ColumnInfo::TYPE_BLOB;
					break;
				}
			}
			$addinfo = $this->db->getArrayList("SELECT objtype, objname, name, CAST(value AS nvarchar(255)) AS value FROM fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', '{$table}', 'column', '{$name}')");
			foreach($addinfo as $add) {
				if($add['name'] == 'MFType') {
					if(preg_match('!enum\((.*)\)!', $add['value'], $match)) {
						$type = ColumnInfo::TYPE_ENUM;
						$param[] = trim($match[1]);
						$size = -1;
					}
					elseif($add['value'] == 'date') {
						$type = ColumnInfo::TYPE_DATE;
					}
				}
			}
			
			$cinfo = new ColumnInfo($name, $type, $size, $param, $charset, $collate, $default);
			$info->addColumn($cinfo);
		}
		$this->info = $info;
		return $info;
	}

	public function setPKey(array $fields) {
		$sql = 'ALTER TABLE ['.$this->name.'] ADD PRIMARY KEY (['.implode('],[', $fields).'])';
		$this->db->query($sql);
	}

	public function getPKeyFields() {
		$sqlbase = MSSQLUtil::getInfoSQLBase($this->db->getDatabase());
		$sqlbase .= ' AND ind.is_primary_key=1';
		$sqlbase = 'SELECT DISTINCT a.[column] FROM ('.$sqlbase.') a WHERE [table]=\''.$this->name.'\'';
		$cols = $this->db->getObjectList($sqlbase);
		$r = array();
		foreach ($cols as $c)
			$r[]  = $c->column;
		return $r;
	}

	public function removePKey() {
		$sqlbase = MSSQLUtil::getInfoSQLBase($this->db->getDatabase());
		$sqlbase .= ' AND ind.is_primary_key=1';
		$sqlbase = 'SELECT DISTINCT a.[index] FROM ('.$sqlbase.') a WHERE [table]=\''.$this->name.'\'';
		$pkey = $this->db->getObjectList($sqlbase);
		if (count($pkey) > 1)
			throw new \InvalidArgumentException('More than one index returned - BUG?');
		if (count($pkey) == 1)
			$this->db->query('ALTER TABLE ['.$this->name.'] DROP CONSTRAINT ['.$pkey[0]->index.']');
	}
	
	public function getIndexList() {
		$sqlbase = MSSQLUtil::getInfoSQLBase($this->db->getDatabase());
		$sqlbase .= ' AND ind.is_primary_key=0 AND ind.is_unique=0 AND ind.is_unique_constraint=0';
		$sqlbase = 'SELECT DISTINCT a.[index] FROM ('.$sqlbase.') a WHERE [table]=\''.$this->name.'\'';
		$cols = $this->db->getObjectList($sqlbase);
		$r = array();
		foreach ($cols as $c)
			$r[]  = $c->index;
		return $r;
	}
	
	public function isIndexDefined($name) {
		$sqlbase = MSSQLUtil::getInfoSQLBase($this->db->getDatabase());
		$sqlbase .= ' AND ind.is_primary_key=0 AND ind.is_unique=0 AND ind.is_unique_constraint=0';
		$sqlbase = 'SELECT DISTINCT a.[index] FROM ('.$sqlbase.') a WHERE [table]=\''.$this->name.'\' AND a.[index]=\''.$name.'\'';
		$c = $this->db->getRowCount($sqlbase);
		if ($c > 1)
			throw new \InvalidArgumentException('More than one index returned - BUG?');
		return ($c == 1);
	}

	public function getIndexFields($name) {
		$sqlbase = MSSQLUtil::getInfoSQLBase($this->db->getDatabase());
		$sqlbase .= ' AND ind.is_primary_key=0 AND ind.is_unique=0 AND ind.is_unique_constraint=0';
		$sqlbase = 'SELECT DISTINCT a.[column] FROM ('.$sqlbase.') a WHERE [table]=\''.$this->name.'\' AND a.[index]=\''.$name.'\'';
		$cols = $this->db->getObjectList($sqlbase);
		$r = array();
		foreach ($cols as $c)
			$r[]  = $c->column;
		return $r;
	}

	public function addColumn(ColumnInfo $info, $after = NULL) {
		$colSQL = MSSQLUtil::fieldDefSQL($this->db, $info);
		$sql = "ALTER TABLE " . $this->db->escapeTableName($this->getName()) . " ADD " . $colSQL;
		if(is_int($after)) {
			if($after < 0)
				$sql .= ' FIRST';
			else {
				$tinfo = $this->getInfo();
				$colName = $tinfo->getColumn($after)->getName();
				$sql .= ' AFTER ' . $this->db->escapeColumnName($colName);
				unset($colName, $tinfo);
			}
		}
		$this->info = NULL;
		$this->db->query($sql);
	}

	public function renameColumn($column, $newName) {
		$table = $this->getName();
		$column = $column;
		$newName = $newName;
		// echo "EXEC sp_rename '{$table}.{$column}', '{$newName}', 'COLUMN'";
		// die();
		$this->db->query("EXEC sp_rename '{$table}.{$column}', '{$newName}', 'COLUMN'");
		sleep(1); // it seems to need a little moment to rename, a better solution would be nice to see if it is finished
	}

	public function alterColumn($column, ColumnInfo $newInfo) {
		$tname = $this->getName();
		
		if($column != $newInfo->getName())
			$this->renameColumn($column, $newInfo->getName());
		
		$sql = "ALTER TABLE [{$tname}] ALTER COLUMN ";
		$sql .= MSSQLUtil::fieldDefSQL($this->db, $newInfo, $this->getPKeyFields());
		$this->db->query($sql);
	}

	public function removeColumn($column) {
		$this->info = NULL;
		if($column instanceof ColumnInfo)
			$column = $column->getName();
		
		$cname = $column;
		$tname = $this->getName();
		
		// remove default value
		$constr = $this->db->getArrayList("SELECT o.name AS name FROM sysobjects o INNER JOIN syscolumns c ON o.id = c.cdefault INNER JOIN sysobjects t ON c.id = t.id WHERE o.xtype = 'd' AND c.name = '$cname' AND t.name = '{$tname}'");
		foreach($constr as $c)
			$this->db->query("ALTER TABLE [{$tname}] DROP CONSTRAINT [{$c['name']}]");
		
		$this->db->query("ALTER TABLE [{$tname}] DROP COLUMN [{$column}]");
	}
	
	public function getFKeyList() {
		$tname = $this->getName();
		$sql = 'SELECT tbl.name AS tbl, fk.name AS fkname FROM sys.foreign_keys fk ';
		$sql .= 'INNER JOIN sys.tables tbl ON fk.parent_object_id=tbl.object_id ';		
		$sql .= 'WHERE tbl.name=\''.$tname.'\''; 		
		$fkeys = $this->db->getObjectList($sql);
		$r = array();
		foreach ($fkeys as $fk)
			$r[]  = $fk->fkname;
		return $r;
	}
}