<?php
// Copyright 2011 Toby Zerner, Simon Zerner
// This file is part of esoTalk. Please see the included license file for usage information.

if (!defined("IN_ESOTALK")) exit;

/**
 * The database structure class provides a wrapper for defining table structures so that they can be easily
 * manipluated and upgraded without having to run arbitrary queries in a linear upgrade script.
 *
 * Similar to the SQLQuery class, this implementation tries to be as SQL-neutral as possible, but is
 * ultimately written to work with MySQL. It can be extended to provide structure management for a different
 * database engine.
 *
 * @package esoTalk
 */
class ETDatabaseStructure {


/**
 * The name of the table currently being constructed.
 * @var string
 */
protected $tableName = "";


/**
 * The engine of the table currently being constructed.
 * @var string
 */
protected $engine = "";


/**
 * Whether or not the table currently exists.
 * @var bool
 */
protected $exists = null;


/**
 * An array of existing columns and their information.
 * @var array
 */
protected $existingColumns = null;


/**
 * An array of existing keys and their information.
 * @var array
 */
protected $existingKeys = null;


/**
 * An array of columns in the table currently being constructed.
 * @var array
 */
protected $columns = array();


/**
 * An array of keys in the table currently being constructed.
 * @var array
 */
protected $keys = array();


/**
 * Class constructor: reset all variables in the instance so we have a fresh slate.
 *
 * @return void
 */
public function __construct()
{
	$this->reset();
}


/**
 * Reset the instance so that all structure information is cleared.
 *
 * @return ETDatabaseStructure
 */
public function reset()
{
	$this->tableName = "";
	$this->engine = "InnoDB";
	$this->columns = array();
	$this->keys = array();
	$this->exists = null;
	$this->existingColumns = null;
	$this->existingKeys = null;

	return $this;
}


/**
 * Start the construction of a table.
 *
 * @param string $tableName The name of the table.
 * @param string $engine The table engine that the table should use.
 * @return ETDatabaseStructure
 */
public function table($tableName, $engine = "InnoDB")
{
	$this->reset();
	$this->tableName = $tableName;
	$this->engine = $engine;

	return $this;
}


/**
 * Add a column to the table.
 *
 * @param string $name The name of the column.
 * @param string $type The type signature of the column.
 * @param mixed $default The default value of the column. If this is false, the column will be NOT NULL.
 * @return ETDatabaseStructure
 */
public function column($name, $type, $default = null)
{
	$this->columns[$name] = array("type" => $type, "null" => $default !== false, "default" => $default);

	return $this;
}


/**
 * Add a key to the table.
 *
 * @param mixed $columns The name of the column, or an array of columns, to index.
 * @param string $type The type of key: primary, unique, fulltext, or empty for a normal key.
 * @return ETDatabaseStructure
 */
public function key($columns, $type = "")
{
	$columns = (array)$columns;

	// If this is a primary key, and there's only one column, and that column's type is an int, set auto
	// increment on it.
	if ($type == "primary" and count($columns) == 1 and substr($this->columns[$columns[0]]["type"], 0, 3) == "int")
		$this->columns[$columns[0]]["autoIncrement"] = true;

	// If it's not a primary key, we must work out a name for the key.
	if ($type != "primary") $name = $this->tableName."_".implode("_", $columns);
	else {
		$name = "PRIMARY";
		foreach ($columns as $column) $this->columns[$column]["null"] = false;
	}

	// Add the key to the keys array.
	$this->keys[$name] = array("type" => $type, "columns" => $columns);

	return $this;
}


/**
 * Execute the queries necessary to bring the database structure up-to-date with the one that has been defined
 * in the instance.
 *
 * @param bool $drop Whether or not an existing table should be dropped before creating one adhering to the
 * 		structure definition.
 * @return ETDatabaseStructure
 */
public function exec($drop = false)
{
	// Do we need to drop the table before we recreate it?
	if ($drop) {
		ET::SQL("DROP TABLE IF EXISTS `".ET::$database->tablePrefix.$this->tableName."`");
		$this->exists = false;
	}

	// Firstly, work out whether or not the table exists. If it doesn't, we'll have to create the table from
	// scratch.
	if (!$this->exists()) {
		$sql = "CREATE TABLE `".ET::$database->tablePrefix.$this->tableName."` (\n\t";
		$lines = array();

		// Add the columns.
		foreach ($this->columns as $name => $column) {
			$lines[] = "`$name` ".$this->columnDefinition($column);
		}

		// Add the keys.
		foreach ($this->keys as $name => $key) {
			$lines[] = $this->keyDefinition($name, $key);
		}

		// Put it all together.
		$sql .= implode(",\n\t", $lines)."\n)";
		if ($this->engine) $sql .= " ENGINE=$this->engine";
		$sql .= " DEFAULT CHARSET=".C("esoTalk.database.characterEncoding");

		ET::SQL($sql);
	}

	// Otherwise, based on what's already there, we need to modify the table to be up-to-date.
	else {
		$alterPrefix = "ALTER TABLE `".ET::$database->tablePrefix.$this->tableName."`";

		// Set the table's engine and character set if necessary.
		$engine = ET::SQL("SHOW TABLE STATUS LIKE '".ET::$database->tablePrefix.$this->tableName."'")->firstRow();
		$engine = $engine["Engine"];
		if ($engine != $this->engine)
			ET::SQL($alterPrefix." ENGINE=$this->engine");

		// Go through the columns and add/modify them as necessary.
		$existingColumns = $this->existingColumns();
		$previousColumn = false;
		foreach ($this->columns as $name => $column) {

			$definition = $this->columnDefinition($column);

			// If the column doesn't exist, we'll need to add it.
			if (!array_key_exists($name, $existingColumns)) {
				ET::SQL($alterPrefix." ADD `$name` ".$definition.($previousColumn !== false ? " AFTER `$previousColumn`" : ""));
			}

			// If it does exist, work out if we need to modify it.
			else {
				$existingDefinition = $this->columnDefinition($existingColumns[$name]);
				if ($definition != $existingDefinition) {
					ET::SQL($alterPrefix." MODIFY `$name` ".$definition);
				}
			}

			$previousColumn = $name;

		}

		// Go through the keys and add/modify them as necessary.
		$existingKeys = $this->existingKeys();
		foreach ($this->keys as $name => $key) {

			$definition = $this->keyDefinition($name, $key);

			// If this key already exists, and it's different to the one we want, drop it and re-add it.
			if (array_key_exists($name, $existingKeys)) {
				$existingDefinition = $this->keyDefinition($name, $existingKeys[$name]);
				if ($definition != $existingDefinition) {
					ET::SQL($alterPrefix." DROP ".($name == "PRIMARY" ? "PRIMARY KEY" : "KEY `$name`"));
					ET::SQL($alterPrefix." ADD ".$definition);
				}
			}

			// If the key doesn't exist, just add it.
			else {
				ET::SQL($alterPrefix." ADD ".$definition);
			}

		}
	}

	$this->reset();
}


/**
 * Get the SQL definition string for a column (eg. int(11) unsigned NOT NULL AUTO_INCREMENT).
 *
 * @param array $column The column details.
 * @return string
 */
protected function columnDefinition($column)
{
	$definition = $column["type"];
	if (!$column["null"]) $definition .= " NOT NULL";
	if ($column["default"] !== false) $definition .= " DEFAULT ".ET::$database->escapeValue(is_null($column["default"]) ? null : (string)$column["default"]);
	if (!empty($column["autoIncrement"])) $definition .= " AUTO_INCREMENT";
	return $definition;
}


/**
 * Get the SQL definition string for a key (eg. UNIQUE KEY `keyname` (`column1`,`column2`)).
 *
 * @param string $name The name of the the key.
 * @param array $key The key details.
 * @return string
 */
protected function keyDefinition($name, $key)
{
	foreach ($key["columns"] as &$column) $column = "`$column`";
	if ($name == "PRIMARY")
		return "PRIMARY KEY (".implode(",", $key["columns"]).")";
	else
		return ($key["type"] ? strtoupper($key["type"])." " : "")."KEY `$name` (".implode(",", $key["columns"]).")";
}


/**
 * Returns whether or not the current table exists in the database.
 *
 * @return bool
 */
public function exists()
{
	if ($this->exists === null) {
		$this->exists = (bool)ET::SQL("SHOW TABLES LIKE '".ET::$database->tablePrefix.$this->tableName."'")->numRows();
	}
	return $this->exists;
}


/**
 * Returns whether or not a column exists in the database.
 *
 * @param string $name The name of the column to check.
 * @return bool
 */
public function columnExists($name)
{
	return array_key_exists($name, $this->existingColumns());
}


/**
 * Returns whether or not an key exists in the database.
 *
 * @param string $name The name of the key, or "PRIMARY" for the primary key.
 * @return bool
 */
public function keyExists($name)
{
	return array_key_exists($name, $this->existingKeys());
}


/**
 * Returns a list of columns and their information in the current table.
 *
 * @return array
 */
public function existingColumns()
{
	if ($this->existingColumns === null) {
		$result = ET::SQL("SHOW COLUMNS FROM `".ET::$database->tablePrefix.$this->tableName."`")->allRows();
		$this->existingColumns = array();
		foreach ($result as $column) {
			$this->existingColumns[$column["Field"]] = array(
				"type" => $column["Type"],
				"null" => $column["Null"] == "YES",
				"default" => ($column["Default"] !== null or $column["Null"] == "YES") ? $column["Default"] : false,
				"autoIncrement" => $column["Extra"] == "auto_increment"
			);
		}
	}
	return $this->existingColumns;
}


/**
 * Returns a list of keys and their information in the current table.
 *
 * @return array
 */
public function existingKeys()
{
	if ($this->existingKeys === null) {
		$result = ET::SQL("SHOW INDEXES FROM `".ET::$database->tablePrefix.$this->tableName."`")->allRows();
		$this->existingKeys = array();
		foreach ($result as $key) {
			if (!isset($this->existingKeys[$key["Key_name"]])) {
				$this->existingKeys[$key["Key_name"]] = array(
					"type" => $key["Non_unique"] ? "" : "unique",
					"columns" => array()
				);
				if ($key["Index_type"] == "FULLTEXT")
					$this->existingKeys[$key["Key_name"]]["type"] = "fulltext";
			}
			$this->existingKeys[$key["Key_name"]]["columns"][] = $key["Column_name"];
		}
	}
	return $this->existingKeys;
}


/**
 * Drop the current table from the database.
 *
 * @return ETDatabaseStructure
 */
public function drop()
{
	ET::SQL("DROP TABLE IF EXISTS `".ET::$database->tablePrefix.$this->tableName."`");
	return $this;
}


/**
 * Drop the specified column from the current table.
 *
 * @param string $name The name of the column.
 * @return ETDatabaseStructure
 */
public function dropColumn($name)
{
	if ($this->columnExists($name))
		ET::SQL("ALTER TABLE `".ET::$database->tablePrefix.$this->tableName."` DROP COLUMN `$name`");
	return $this;
}


/**
 * Drop the specified key from the current table.
 *
 * @param string $name The name of the key, or "PRIMARY" to drop the primary key.
 * @return ETDatabaseStructure
 */
public function dropKey($name)
{
	if ($this->keyExists($name))
		ET::SQL("ALTER TABLE `".ET::$database->tablePrefix.$this->tableName."` DROP ".($name == "primary" ? "PRIMARY KEY" : "KEY `$name`"));
	return $this;
}


/**
 * Rename the current table.
 *
 * @param string $newName The new name of the table.
 * @return ETDatabaseStructure
 */
public function rename($newName)
{
	if ($this->exists())
		ET::SQL("ALTER TABLE `".ET::$database->tablePrefix.$this->tableName."` RENAME TO `$newName`");
	return $this;
}


/**
 * Rename the specified column on the current table.
 *
 * @param string $name The name of the column.
 * @param string $newName The new name of the column.
 * @return ETDatabaseStructure
 */
public function renameColumn($name, $newName)
{
	if ($this->columnExists($name)) {
		$existing = $this->existingColumns();
		$definition = $this->columnDefinition($existing[$name]);
		ET::SQL("ALTER TABLE `".ET::$database->tablePrefix.$this->tableName."` CHANGE COLUMN `$name` `$newName` $definition");
	}
	return $this;
}

}
