<?php
/* $Id$ */
namespace Mammut\DB\MySQLi;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Model\TableInfo;

/**
 * MySQL table implementation
 *
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB\Adapter\MySQL
 */
class Table extends \Mammut\DB\Table {

	public function __construct(\Mammut\Db\DB &$db, $name) {
		parent::__construct($db, $name);
	}

	/**
	 *
	 * @param $db \Mammut\DB\DB
	 *        	instance
	 * @param $tableName string
	 *        	name
	 * @return TableInfo info object
	 */
	public function getInfo() {
		if(is_object($this->info))
			return $this->info;
		$name = $this->getName();
		$info = new TableInfo($name);
		$result = $this->db->query("SHOW FULL COLUMNS FROM `{$name}`");
		while($row = $result->fetchArray()) {
			$name = $row['Field'];
			$type = '';
			$size = -1;
			$param = array();
			$charset = NULL;
			$collate = NULL;
			if(preg_match('#^([a-zA-Z]+)(\(([0-9]+)\))?( [a-zA-Z_]+)*#', $row['Type'], $match)) {
				if($row['Null'] == 'YES')
					$param[] = 'null';
				switch(strtoupper(trim($match[1]))) {
					case 'BIGINT':
						$type = ColumnInfo::TYPE_LONG;
						if($row['Extra'] == 'auto_increment')
							$param[] = 'autonum';
						if(in_array(' unsigned', $match))
							$param[] = 'unsigned';
					break;
					case 'INT':
						$type = ColumnInfo::TYPE_INT;
						if($row['Extra'] == 'auto_increment')
							$param[] = 'autonum';
						if(in_array(' unsigned', $match))
							$param[] = 'unsigned';
						$size = (int) $match[3];
					break;
					case 'SMALLINT':
						$type = ColumnInfo::TYPE_SHORT;
						if($row['Extra'] == 'auto_increment')
							$param[] = 'autonum';
						if(in_array(' unsigned', $match))
							$param[] = 'unsigned';
						$size = (int) $match[3];
					break;
					case 'TINYINT':
						// try to detect pseudo boolean columns
						if($row['Comment'] == 'boolean') {
							$type = ColumnInfo::TYPE_BOOLEAN;
							$size = -1;
						}
						else {
							$type = ColumnInfo::TYPE_TINY;
							if($row['Extra'] == 'auto_increment')
								$param[] = 'autonum';
							if(in_array(' unsigned', $match))
								$param[] = 'unsigned';
							$size = (int) $match[3];
						}
					break;
					case 'CHAR':
						$type = ColumnInfo::TYPE_CHAR;
						$size = (int) $match[3];
					break;
					case 'VARCHAR':
						$type = ColumnInfo::TYPE_VCHAR;
						$size = (int) $match[3];
					break;
					case 'TEXT':
					case 'LONGTEXT':
						$type = ColumnInfo::TYPE_TEXT;
					break;
					case 'BLOB':
					case 'LONGBLOB':
						$type = ColumnInfo::TYPE_BLOB;
					break;
					case 'DATE':
						$type = ColumnInfo::TYPE_DATE;
					break;
					case 'DATETIME':
						$type = ColumnInfo::TYPE_DATETIME;
					break;
					case 'ENUM':
						$type = ColumnInfo::TYPE_ENUM;
						$values = array();
						if(preg_match('#^[a-zA-Z]+\(([^)]+)*\)#', $row['Type'], $match)) {
							$vs = $match[1];
							$vs = explode(',', $vs);
							foreach($vs as $v)
								$values[] = substr($v, 1, strlen($v) - 2);
						}
						$param[] = 'values(' . implode(',', $values) . ')';
					break;
				}
			}
			$cinfo = new ColumnInfo($name, $type, $size, $param, $charset, $collate);
			$info->addColumn($cinfo);
		}
		$this->info = $info;
		return $info;
	}

	public function setPKey(array $fields) {
		$d = $this->db->getDialect();
		$sql = 'ALTER TABLE '.$d->quoteIdent($this->name).' ADD PRIMARY KEY';
		array_walk($fields, function(&$val, $key) {
			$val = $this->db->getDialect()->quoteIdent($val);
		});
		$sql .= ' ('.implode(',', $fields).')';
		$this->db->query($sql);
	}

	public function getPKeyFields() {
		$d = $this->db->getDialect();
		$sql = 'SHOW INDEX FROM '.$d->quoteIdent($this->name).' WHERE Key_name=\'PRIMARY\'';
		$idx = $this->db->getObjectList($sql);
		if (count($idx) == 0)
			return false;
		
		$r = array();
		foreach($idx as $i)
			$r[] = $i->Column_name;
		$r = array_unique($r);
		return $r;
	}

	public function removePKey() {
		$d = $this->db->getDialect();
		$sql = 'ALTER TABLE '.$d->quoteIdent($this->name).' DROP PRIMARY KEY';
		$this->db->query($sql);
	}
	
	public function getIndexList() {
		$d = $this->db->getDialect();
		$sql = 'SHOW INDEX FROM '.$d->quoteIdent($this->name).' WHERE Key_name<>\'PRIMARY\'';
		$idx = $this->db->getObjectList($sql);
		$r = array();
		foreach($idx as $i)
			$r[] = $i->Key_name;
		$r = array_unique($r);
		return $r;
	}

	public function isIndexDefined($name) {
		$idx = $this->getIndexList();
		return array_search($name, $idx) !== false;
	}
		
	public function getIndexFields($name) {
		$d = $this->db->getDialect();
		$sql = 'SHOW INDEX FROM '.$d->quoteIdent($this->name).' WHERE Key_name='.$d->quoteValue($name).'';
		$idx = $this->db->getObjectList($sql);
		$r = array();
		foreach($idx as $i)
			$r[] = $i->Column_name;
		return $r;
	}

	public function getFKeyList() {
		$d = $this->db->getDialect();
		
		$sql = 'SELECT CONSTRAINT_NAME AS fkname FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE ';
		$sql .= 'WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND CONSTRAINT_NAME<>\'PRIMARY\' AND REFERENCED_TABLE_NAME IS NOT NULL';
				
		$stmt = $this->db->prepareStatement($sql);
		$stmt->execute([$this->db->getDatabase(), $this->getName()]);
		$idx = $stmt->fetchObjectList();
		$stmt->close();
		
		$r = array();
		foreach($idx as $i)
			$r[] = $i->fkname;
		$r = array_unique($r);
		return $r;
	}
	
	public function removeFKey($name) {
		$d = $this->db->getDialect();
		$sql =  'ALTER TABLE '.$d->quoteIdent($this->name).' DROP FOREIGN KEY '.$d->quoteIdent($name);
		$this->db->query($sql);
	}
	
	public function addColumn(ColumnInfo $info, $after = NULL) {
		$colSQL = MySQLUtil::fieldDefSQL($this->db, $info);
		$sql = "ALTER TABLE " . $this->db->escapeTableName($this->getName()) . " ADD COLUMN " . $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) {
		$colInfo = $this->getInfo()->getColumnInfo($column);
		if (empty($colInfo))
			throw new \InvalidArgumentException('Column not found: '.$column);
		$colInfo->setName($newName);
		$this->alterColumn($column, $colInfo);
	}

	public function alterColumn($column, ColumnInfo $info) {
		$sqls = MySQLUtil::createAlterColumnSQLs($this->db, $this->getName(), $column, $info);
		
		// var_dump($sqls);
		// die();	
		
		foreach($sqls['pre'] as $sql) {
			$this->db->query($sql);
		}
		foreach($sqls['main'] as $sql) {
			$this->db->query($sql);
		}
		foreach($sqls['post'] as $sql) {
			$this->db->query($sql);
		}
		$this->info = NULL;
	}
}