<?php
/**
 * Database Table abstract
 * 
 * @todo pretty join support
 */
class LtDbTableDataGateway
{
	public $configHandle;
	public $dbh;
	/**
	 * The created field name
	 * 
	 * @var string 
	 */
	public $createdColumn;

	/**
	 * The modified field name
	 * 
	 * @var string 
	 */
	public $modifiedColumn;

	/**
	 * The table name
	 * 
	 * @var string 
	 */
	public $tableName;

	/**
	 * The fields array
	 * 
	 * @var array 
	 */
	protected $fields;

	/**
	 * The primary key
	 * 
	 * @var string 
	 */
	protected $primaryKey;
	protected $servers;

	/**
	 * Build table's field list
	 * 
	 * @return array 
	 */
	protected function buildFieldList()
	{
		if (!empty($this->fields))
		{
			return true;
		}
		$servers = $this->configHandle->get('db.servers');
		$group = $this->dbh->group;
		$node = $this->dbh->node;
		$role = $this->dbh->role;
		$table = $this->tableName;
		$host = key($servers[$group][$node][$role]);
		$key = md5($group . $node . $role . $table . $host . $table);
		if (!$value = $this->configHandle->get($key))
		{
			$sql = $this->dbh->sqlAdapter->showFields($this->tableName);
			$rs = $this->dbh->query($sql);
			$this->fields = $this->dbh->sqlAdapter->getFields($rs);
			foreach($this->fields as $field)
			{
				if ($field['primary'] == 1)
				{
					$this->primaryKey = $field['name'];
					break;
				}
			}

			$value['fields'] = $this->fields;
			$value['primaryKey'] = $this->primaryKey;
			$this->configHandle->addConfig(array($key => $value));
		}
		else
		{
			$this->fields = $value['fields'];
			$this->primaryKey = $value['primaryKey'];
		}
	}

	/**
	 * A shortcut to SELECT COUNT(*) FROM table WHERE condition
	 * 
	 * @param array $args 
	 * @return integer 
	 * @example count(array('expression' => 'id < :id', 'value' => array('id' => 10)));
	 */
	public function count($args = null)
	{
		$selectTemplate = 'SELECT COUNT(*) AS total FROM %s%s';
		$where = isset($args['where']['expression']) ? ' WHERE ' . $args['where']['expression'] : '';
		$bind = isset($args['where']['value']) ? $args['where']['value'] : array();
		$join = isset($args['join']) ? ' ' . $args['join'] : '';
		$sql = sprintf($selectTemplate, $this->tableName, $join . $where);
		$queryResult = $this->dbh->query($sql, $bind);
		return $queryResult[0]['total'];
	}

	/**
	 * Delete a row by primary key
	 * 
	 * @param string $primaryKeyId 
	 * @return string 
	 * @example delete(10);
	 */
	public function delete($primaryKeyId)
	{
		$this->buildFieldList();
		$where['expression'] = $this->primaryKey . '=:' . $this->primaryKey;
		$where['value'][$this->primaryKey] = $primaryKeyId;
		return $this->deleteRows($where);
	}

	/**
	 * Delete many rows from table
	 * Please use this method carefully!
	 * 
	 * @param array $args 
	 * @return integer 
	 * @example deleteRows(array('expression' => "id > :id", 'value' => array('id' => 2)));
	 */
	public function deleteRows($args = null)
	{
		$deleteTemplate = 'DELETE FROM %s%s';
		$where = isset($args['expression']) ? ' WHERE ' . $args['expression'] : '';
		$bind = isset($args['value']) ? $args['value'] : array();
		$sql = sprintf($deleteTemplate, $this->tableName, $where);
		return $this->dbh->query($sql, $bind);
	}

	/**
	 * Fetch one row from table by primary key
	 * 
	 * @param string $primaryKeyId 
	 * @param array $args 
	 * @param boolean $useSlave 
	 * @return array 
	 * @example fetch(10)
	 */
	public function fetch($primaryKeyId, $args = null, $useSlave = true)
	{
		$this->buildFieldList();
		$fetchRowsArgs['where']['expression'] = $this->tableName . '.' . $this->primaryKey . '=:' . $this->primaryKey;
		$fetchRowsArgs['where']['value'][$this->primaryKey] = $primaryKeyId;
		$fetchRowsArgs['fields'] = isset($args['fields']) ? $args['fields'] : null;
		$fetchRowsArgs['join'] = isset($args['join']) ? $args['join'] : null;
		$fetchResult = $this->fetchRows($fetchRowsArgs, $useSlave);
		return $fetchResult ? $fetchResult[0] : $fetchResult;
	}

	/**
	 * Fetch many rows from table
	 * 
	 * @param array $args 
	 * @param boolean $useSlave 
	 * @return array 
	 * @example fetchRows(array('where' => array('expression' => "id > :id", 'value' => array('id' => 2))));
	 */
	public function fetchRows($args = null, $useSlave = true)
	{
		$this->buildFieldList();
		$selectTemplate = 'SELECT %s FROM %s%s';
		$fields = isset($args['fields']) ? $args['fields'] : '*';
		$where = isset($args['where']['expression']) ? ' WHERE ' . $args['where']['expression'] : '';
		$bind = isset($args['where']['value']) ? $args['where']['value'] : array();
		$join = isset($args['join']) ? ' ' . $args['join'] : '';
		$orderby = isset($args['orderby']) ? ' ORDER BY ' . $args['orderby'] : '';
		$groupby = isset($args['groupby']) ? ' GROUP BY ' . $args['groupby'] : '';
		$sql = sprintf($selectTemplate, $fields, $this->tableName, $join . $where . $groupby . $orderby);
		if (isset($args['limit']))
		{
			$offset = isset($args['offset']) ? $args['offset'] : 0;
			$sql = $sql . ' ' . $this->dbh->sqlAdapter->limit($args['limit'], $offset);
		}
		return $this->dbh->query($sql, $bind);
	}

	/**
	 * Insert one row into table, then return the inserted row's pk
	 * 
	 * @param array $args 
	 * @return string 
	 * @example insert(array('name' => 'lily', 'age' => '12'));
	 */
	public function insert($args = null)
	{
		$this->buildFieldList();
		$insertTemplate = 'INSERT INTO %s (%s) VALUES (%s)';
		$fields = array();
		$placeHolders = array();
		foreach($args as $field => $value)
		{
			if (isset($this->fields[$field]))
			{
				$fields[] = $field;
				$placeholders[] = ":$field";
				$values[$field] = $value;
			}
		}
		if (isset($this->fields[$this->createdColumn]) && !isset($args[$this->createdColumn]))
		{
			$fields[] = $this->createdColumn;
			$placeholders[] = ':' . $this->createdColumn;
			$values[$this->createdColumn] = time();
		}
		if (isset($this->fields[$this->modifiedColumn]) && !isset($args[$this->modifiedColumn]))
		{
			$fields[] = $this->modifiedColumn;
			$placeholders[] = ':' . $this->modifiedColumn;
			$values[$this->modifiedColumn] = time();
		}
		$sql = sprintf($insertTemplate, $this->tableName, implode(",", $fields), implode(",", $placeholders));
		$bind = $values;
		$queryResult = $this->dbh->query($sql, $bind);
		return isset($args[$this->primaryKey]) ? $args[$this->primaryKey] : $queryResult;
	}

	/**
	 * Update one row  by primary key
	 * 
	 * @param string $primaryKeyId 
	 * @param array $args 
	 * @return integer 
	 * @example update(1, array('name' => 'lily', 'age' => '18'));
	 */
	public function update($primaryKeyId, $args = null)
	{
		$this->buildFieldList();
		$where['expression'] = $this->primaryKey . '=:' . $this->primaryKey;
		$where['value'][$this->primaryKey] = $primaryKeyId;
		return $this->updateRows($where, $args);
	}

	/**
	 * Update manay rows
	 * Please use this method carefully!
	 * 
	 * @param array $where 
	 * @param array $args 
	 * @return integer 
	 * @example updateRows(array('expression' => "id > :id", 'value' => array('id' => 2)), array('name' => 'kiwi', 'age' => '1'));
	 */
	public function updateRows($where, $args = null)
	{
		$this->buildFieldList();
		$updateTemplate = 'UPDATE %s SET %s%s';
		$fields = array();
		$bindParameters = array();
		$placeholderStyle = isset($where['value']) && array_key_exists(0, $where['value']) ? 'questionMark' : 'named';
		foreach($args as $field => $value)
		{
			if (isset($this->fields[$field]))
			{
				if ($args[$field] instanceof DbExpression)
				{
					$fields[] = "$field=" . $args[$field]->__toString();
				}
				else
				{
					if ('named' == $placeholderStyle)
					{
						$fields[] = "$field=:$field";
						$bindParameters[$field] = $args[$field];
					}
					else
					{
						$fields[] = "$field=?";
						$bindParameters[] = $args[$field];
					}
				}
			}
		}
		if (isset($this->fields[$this->modifiedColumn]) && !isset($args[$this->modifiedColumn]))
		{
			if ('named' == $placeholderStyle)
			{
				$fields[] = $this->modifiedColumn . '=:' . $this->modifiedColumn;
				$bindParameters[$this->modifiedColumn] = time();
			}
			else
			{
				$fields[] = $this->modifiedColumn . '=?';
				$bindParameters[] = time();
			}
		}
		$whereCause = isset($where['expression']) ? ' WHERE ' . $where['expression'] : '';
		$bind = isset($where['value']) ? array_merge($bindParameters, $where['value']) : $bindParameters;
		$sql = sprintf($updateTemplate, $this->tableName, implode(",", $fields), $whereCause);
		return $this->dbh->query($sql, $bind);
	}
}