<?php
/* $Id$ */
namespace Mammut\DB;

(defined('MAMMUT') && (basename(__FILE__) != basename($_SERVER['PHP_SELF']))) or die('ACCESS DENIED');

use Mammut\DB\Model\ColumnInfo;
use Mammut\DB\Sql\Expression\SQLFunction;
use Mammut\DB\Sql\Elements\Condition;

/**
 * Table abstraction base class
 * 
 * @author Stefan Daurer <s.daurer@q-spot.org>
 * @package Mammut\DB
 */
abstract class Table extends \Mammut\StrictObject implements iDatastore {
	/**
	 * The database object used
	 *
	 * @var DB
	 */
	protected $db;
	
	protected $dbname;
	protected $schema;
	
	/**
	 * The name of this table
	 *
	 * @var string
	 */
	protected $name;
	protected $prefix;
	
	/**
	 * Info cache
	 *
	 * @var array
	 */
	protected $info = NULL;

	public function __construct(DB &$db, $name) {
		$this->db = &$db;
		$this->name = $name;
	}

	/**
	 *
	 * @return string the name of the table
	 */
	public function getName() {
		return $this->name;
	}

	/**
	 * Inserts an object or an array into the table
	 *
	 * @param mixed $data
	 *        	values that should be added
	 * @param array $ignore
	 *        	of the array or object which should be ignored
	 */
	public function insert($data, array $ignore = array()) {
		if(is_array($data) || is_object($data)) {
			if(is_object($data))
				$data = get_object_vars($data);
			if(count($data) == 0)
				throw new \InvalidArgumentException('data is empty');
			
			foreach($ignore as $field) {
				if(array_key_exists($field, $data))
					unset($data[$field]);
			}
			
			$sql = $this->db->insert();
			$sql->into($this->name);
			$sql->columns(array_keys($data));
			$sql->values(array_values($data));
			
			$this->db->query($sql);
			
// 			$fields = array_map(array($this->db->getDialect(),'quoteIdent'), array_keys($data)); // escape all columns
// 			$fields = implode(',', $fields);
			
// 			$values = array_values(array_values($data));
// 			$param = array();
// 			foreach($values as $key=>$val) {
// 				if ($val instanceof SQLFunction)
// 					$values[$key] = $this->db->escapeValue($values[$key]);
// 				else {
// 					$param[$key] = $values[$key];
// 					$values[$key] = '?';
// 				}
// 			}
// 			$values = implode(',', $values);
			
// 			$sql ='INSERT INTO ' . $this->db->getDialect()->quoteIdent($this->name) . '(' . $fields . ') VALUES (' . $values . ')';
// 			$stmt = $this->db->prepareStatement($sql);
// 			$stmt->execute($param);
// 			$stmt->close();
		}
		else
			throw new \InvalidArgumentException('data has to be an array or object');
	}

	/**
	 * Inserts an object or an array into the table if a basic dataset is not found.
	 * If the dataset exists already, it will be updated instead.
	 *
	 * @param mixed $base
	 *        	value that will be searched in the table
	 * @param mixed $insert
	 *        	values which will be merged into base before inserting
	 * @param mixed $update
	 *        	which will be used to update existing rows
	 */
	public function insertOrUpdate($base, $insert, $update) {
		if(is_object($base))
			$base = get_object_vars($base);
		if(is_object($insert))
			$insert = get_object_vars($insert);
		if(is_object($update))
			$update = get_object_vars($update);
		if(!(is_array($base) && is_array($insert) && is_array($update)))
			throw new \InvalidArgumentException('base, insert and update must be arrays or objects');
		
		if($this->update($update, $base) == 0)
			$this->insert(array_merge($base, $insert));
	}

	/**
	 * Selects data from the table.
	 * Prefix an order field with ! to order descenting
	 *
	 * @param array $limiter        	
	 * @param array $order
	 *        	an field name list
	 * @param array $fields
	 *        	the fields that should be returned (and their order), false for all
	 * @return Result
	 */
	public function select($limiter = false, $order = false, $fields = false) {
		$name = $this->db->escapeTableName($this->name);
		
		// this is standard sql and should work on all servers
		if($fields == false)
			$sql = "SELECT * FROM {$name}";
		elseif(is_array($fields) && count($fields) != 0) {
			foreach($fields as &$field)
				$field = $this->db->escapeColumnName($field);
			$sql = "SELECT " . implode(',', $fields) . " FROM {$name}";
		}
		else
			throw new \InvalidArgumentException('$fields should be an array or \'false\'');
		
		$qParam = array();
		if(is_array($limiter)) {
			$con = array();
			foreach($limiter as $field=>$value) {
				if (is_array($value)) {
					$conStr = '';
					foreach ($value as $k => &$v) {
						if (is_null($v)) {
							$conStr .= $this->db->escapeColumnName($field) .' IS NULL OR ';
							unset($value[$k]);
						}
						else
							$v = $this->db->getDialect()->quoteValue($v);
					}
					$con[] = $conStr.$this->db->escapeColumnName($field) .' IN ('.implode(',', $value).')';
				}
				else {
					$con[] = $this->db->escapeColumnName($field) . (is_null($value) ? ' IS NULL' : '=?');
					if(!is_null($value)) {
						if($value instanceof \DateTime)
							$value = $this->db->escapeValue($value, false);
						$qParam[] = $value;
					}
				}
			}
			$sql .= ' WHERE (' . implode(') AND (', $con) . ')';
		}
		
		if(!empty($order)) {
			if(is_string($order)) {
				$colname = $this->db->escapeColumnName($order);
				$sql .= " ORDER BY {$colname}";
			}
			elseif(is_array($order)) {
				$sql .= " ORDER BY ";
				$next = false;
				foreach($order as $field) {
					$asc = true;
					if($field[0] == '!') {
						$field = substr($field, 1);
						$asc = false;
					}
					$sql .= ($next ? ',' : '') . $this->db->escapeColumnName($field);
					$sql .= ($asc ? ' ASC' : ' DESC');
					$next = true;
				}
			}
			else
				throw new \InvalidArgumentException('order has to be a string or array of field names');
		}
		$pstmt = $this->db->prepareStatement($sql);
		$pstmt->execute($qParam);
		return $pstmt;
	}

	/**
	 * Fechtes a single row from the table
	 *
	 * @param string $limiter        	
	 * @param string $order        	
	 * @return array
	 */
	public function getRow($limiter = false, $order = false) {
		$result = $this->select($limiter, $order);
		$data = $result->fetchRow();
		$result->close();
		return $data;
	}

	/**
	 * Fechtes a single row from the table
	 *
	 * @param string $limiter        	
	 * @param string $order        	
	 * @return array
	 */
	public function getArray($limiter = false, $order = false) {
		$result = $this->select($limiter, $order);
		$data = $result->fetchArray();
		$result->close();
		return $data;
	}

	/**
	 * Fetches a single row of this table as an object.
	 *
	 * This method returns the first row found in the database. No exception will be
	 * thrown if multiple rows exist.
	 *
	 * @param array $limiter
	 *        	limits the selection
	 * @param array $order
	 *        	order of the dataset
	 * @param string $classname
	 *        	class name of the object
	 * @param array $param
	 *        	parameters which are used in the constructor
	 * @return \stdClass the row object
	 */
	public function getObject($limiter = false, $order = false, $classname = false, array $param = array()) {
		$result = $this->select($limiter, $order);
		$data = ($classname === false) ? $result->fetchObject(false, $param) : $result->fetchObject($classname, $param);
		$result->close();
		return $data;
	}

	/**
	 * Fetches all rows of this table as arrays
	 *
	 * @param array $limiter
	 *        	limits the selection
	 * @param array $order
	 *        	order of the dataset
	 * @return array
	 */
	public function getRowList($limiter = false, $order = false) {
		$result = $this->select($limiter, $order);
		$data = $result->fetchRowList();
		$result->close();
		return $data;
	}

	/**
	 * Fetches all rows of this table as arrays
	 *
	 * @param array $limiter
	 *        	limits the selection
	 * @param array $order
	 *        	order of the dataset
	 * @return array
	 */
	public function getArrayList($limiter = false, $order = false) {
		$result = $this->select($limiter, $order);
		$data = $result->fetchArrayList();
		$result->close();
		return $data;
	}

	/**
	 * Fetches all rows of this table as objects.
	 *
	 * This method returns the rows found in the database. No exception will be
	 * thrown if no rows exist, only an empty array is returned.
	 *
	 * @param array $limiter
	 *        	limits the selection
	 * @param array $order
	 *        	order of the dataset
	 * @param string $classname
	 *        	class name of the object
	 * @param array $param
	 *        	parameters which are used in the constructor
	 * @return array the row object array
	 */
	public function getObjectList($limiter = false, $order = false, $classname = false, array $param = array()) {
		$result = $this->select($limiter, $order);
		$data = $result->fetchObjectList($classname, $param);
		$result->close();
		return $data;
	}

	/**
	 * updates fields based on a array or object, and a key array
	 *
	 * @param $data mixed
	 *        	the data which should be set
	 * @param $key array
	 *        	the key which is used to get the dataset
	 * @param $exclude array
	 *        	a list of field which should be ignored in the $data param
	 * @return int the number of affected fields
	 */
	public function update($data, array $key, array $exclude = array()) {
		$name = $this->db->escapeTableName($this->name);
		
		// create array with the update commands based on data
		$update = array();
		foreach($data as $field=>$value)
			if(!in_array($field, $exclude))
				$update[] = $this->db->escapeColumnName($field) . (is_null($value) ? '=NULL' : '=' . $this->db->escapeValue($value));
			
			// create array with the conditions based on the key
		$con = array();
		foreach($key as $field=>$value)
			$con[] = $this->db->escapeColumnName($field) . (is_null($value) ? ' IS NULL' : '=' . $this->db->escapeValue($value));
		
		$sql = "UPDATE {$name} SET " . implode(',', $update) . ' WHERE (' . implode(') AND (', $con) . ')';
		$this->db->query($sql);
		return $this->db->getAffectedRowCount();
	}

	/**
	 * Deletes data from the table
	 *
	 * @param array $data        	
	 */
	public function delete(array $data) {
// 		if(count($data) == 0) {
// 			$this->eraseData();
// 			return true;
// 		}
		$sql = $this->db->delete();
		$sql->from($this->name);
		if (!empty($data))
			$sql->where($data);
		$this->db->query($sql);
		return $this->db->getAffectedRowCount();
	}

	/**
	 * Counts the numbers of rows of the table
	 *
	 * @return int the row count
	 */
	public function size(array $condition = array()) {
		$select = $this->db->select();
		$select->from($this->name)->columns(['c' => SQLFunction::count()]);
		if (count($condition) > 0)
			$select->where($condition);
		return $this->db->getObject($select)->c;
	}

	/**
	 * (non-PHPdoc)
	 *
	 * @see \Countable
	 */
	public function count(): int {
		return $this->size();
	}

	/**
	 * (non-PHPdoc)
	 *
	 * @see \Mammut\StdObject::__toString()
	 */
	public function __toString() {
		return $this->name;
	}

	/**
	 * Creates an information object which describes the table
	 * 
	 * @see \Mammut\DB\Model\TableInfo
	 * @return \Mammut\DB\Model\TableInfo
	 */
	abstract public function getInfo();

	abstract public function setPKey(array $fields);

	/**
	 *
	 * @return array the fields in the primary key
	 */
	abstract public function getPKeyFields();

	abstract public function removePKey();

	/**
	 * Adds a CHECK constraint to the table
	 * 
	 * @param string $name
	 *        	a constraint name
	 * @param Condition $checkCondition
	 *        	the condition that should be used
	 * @throws \BadMethodCallException
	 */
	public function addCheck($name, Condition $checkCondition) {
	    throw new \BadMethodCallException('not implemented');
	}

	public function getChecks() {
	    throw new \BadMethodCallException('not implemented');
	}

	public function removeCheck($name, Condition $checkCondition) {
	    throw new \BadMethodCallException('not implemented');
	}

	public function setIndex($name, array $fields, $unique = false) {
		$d = $this->db->getDialect();
		$sql = 'CREATE '.($unique ? 'UNIQUE ' : '').'INDEX '.$d->quoteIdent($name).' ';
		array_walk($fields, function(&$val, $key) {
			$val = $this->db->getDialect()->quoteIdent($val);
		});
		$sql .= 'ON '.$d->quoteIdent($this->getName()).' ('.implode(',', $fields).')';
		$this->db->query($sql);
	}

	abstract public function getIndexList();

	abstract function isIndexDefined($name);

	/**
	 *
	 * @param string $name
	 *        	name of the index
	 * @return array the fields in the index
	 */
	abstract public function getIndexFields($name);

	public function removeIndex($name) {
		$d = $this->db->getDialect();
		$sql = 'DROP INDEX '.$d->quoteIdent($name).' ON '.$d->quoteIdent($this->name);
		$this->db->query($sql);
	}

	abstract public function getFKeyList();

	/**
	 * Add a FOREIGN KEY constraint to the table
	 * 
	 * @param string $name
	 * @param array $myFields
	 * @param mixed $targetObj
	 * @param array $targetFields
	 * @param string $delAction
	 *        	one of PREVENT, CASCADE or NULL
	 * @param string $updateAction
	 *        	one of PREVENT, CASCADE or NULL
	 */
	public function addFKey($name, array $myFields, $targetObj, array $targetFields, $delAction = 'PREVENT', $updateAction = 'CASCADE') {
		$d = $this->db->getDialect();
		array_walk($myFields, function (&$val, $key) {
			$val = $this->db->getDialect()->quoteIdent($val);
		});
		array_walk($targetFields, function (&$val, $key) {
			$val = $this->db->getDialect()->quoteIdent($val);
		});
		$sql = 'ALTER TABLE '.$d->quoteIdent($this->name);
		$sql .= ' ADD CONSTRAINT '.$d->quoteIdent($name).' FOREIGN KEY ('.implode(',', $myFields).')';
		$sql .= ' REFERENCES '.$d->quoteIdent($targetObj).' ('.implode(',', $targetFields).')';
		
		$sql .= ' ON DELETE ';
		switch($delAction) {
			case 'PREVENT':
				$sql .= 'NO ACTION';
				break;
			case 'CASCADE':
				$sql .= 'CASCADE';
				break;
			case 'NULL':
				$sql .= 'SET NULL';
				break;
		}
		$sql .= ' ON UPDATE ';
		switch($updateAction) {
			case 'PREVENT':
				$sql .= 'NO ACTION';
				break;
			case 'CASCADE':
				$sql .= 'CASCADE';
				break;
			case 'NULL':
				$sql .= 'SET NULL';
				break;
		}
		$this->db->query($sql);
	}

	public function removeFKey($name) {
		$d = $this->db->getDialect();
		$sql = 'ALTER TABLE '.$d->quoteIdent($this->name).' DROP CONSTRAINT '.$d->quoteIdent($name);
		$this->db->query($sql);
	}

	abstract public function addColumn(ColumnInfo $info, $after = NULL);

	abstract public function renameColumn($column, $newName);

	abstract public function alterColumn($column, ColumnInfo $newInfo);

	public function removeColumn($column) {
		$this->info = NULL;
		if($column instanceof ColumnInfo)
			$column = $column->getName();
		$sql = $this->db->getDialect()->getDdl()->getDropColumnSQL($this->name, $column);
		$this->db->query($sql);
	}

	/**
	 * Removes all rows from the table.
	 * WARNING: on some databases, this is a non-transactionional event and cannot be
	 * rolled back!
	 */
	public function eraseData() {
		$sql = $this->db->getDialect()->getTruncateSQL($this->name);
		$this->db->query($sql);
	}
}