<?php
namespace App\ExportData\Output;

class DbHandler
{
	/**
	 * Stores current object
	 * @var \App\ExportData\Output\DbHandler
	 */
	static private $instance = NULL;

	/**
	 * Stores resources of database connection
	 */
	private $dbConn = NULL;

	/**
	 * Flag is set true when a transaction has
	 * @var Boolean
	 */
	private $transaction = NULL;

	/**
	 * Stores database host
	 * @var String
	 */
	private $host = 	NULL;

	/**
	 * Stores database username
	 * @var String
	 */
	private $username = NULL;

	/**
	 * Stores database password
	 * @var String
	 */
	private $password = NULL;

	/**
	 * Stores database name
	 * @var String
	 */
	private $database = NULL;

	/**
	 * Returns the singleton instantiation of database handler
	 * @return \App\ExportData\Output\DbHandler
	 */
	static public function getInstance()
	{
		if (self::$instance == NULL) {
			self::$instance = new self();
		}

		return self::$instance;
	}

	/**
	 * Set database credentials used for connecting
	 * @param String $host
	 * @param String $username
	 * @param String $password
	 * @param String $database
	 */
	public function setDatabaseCredentials($host, $username, $password, $database)
	{
		$this->host = 		$host;
		$this->username = 	$username;
		$this->password = 	$password;
		$this->database = 	$database;
	}

	/**
	 * Connect to database
	 * @throws \App\MessagesException
	 */
	private function connect()
	{
		$this->dbConn = ADONewConnection('mysqli');
		$this->dbConn->NConnect($this->host, $this->username, $this->password, $this->database);

		// if connection failed throw exeception
		if ($this->dbConn->ErrorNo()) {
			$msgs = new \App\MessagesCollector();
			throw new \App\MessagesException($msgs->addError(sprintf("Cannot connect to database. Error message: %s", $this->dbConn->ErrorMsg())));
		}

		$this->dbConn->setFetchMode(ADODB_FETCH_ASSOC);
	}

	/**
	 * Execute query
	 * @param String $sql
	 * @param array $values
	 */
	private function execute($sql, Array $values=[])
	{
		// connect to database only if it's necessary
		if (!$this->dbConn) {
			$this->connect();
		}

		return $this->dbConn->Execute($sql, $values);
	}

	/**
	 * Execute insert operation
	 * @param String $dbTable
	 * @param array $saveData
	 */
	public function insert($dbTable, Array $saveData)
	{
		$insertSQL = "INSERT INTO `{$dbTable}` SET ";
		$index = 0;
		$values = [];
		foreach ($saveData as $field => $value) {
			// add comma
			$index++;
			if ($index != 1) {
				$insertSQL .= ", ";
			}

			$insertSQL .= "`{$field}`=?";
			$values[] = $value;
		}

		return $this->execute($insertSQL, $values);
	}

	/**
	 * Execute update operation
	 * @param String $dbTable
	 * @param String $primaryKeyName
	 * @param String $primaryKeyValue
	 * @param array $saveData
	 */
	public function update($dbTable, $primaryKeyName, $primaryKeyValue, Array $saveData)
	{
		// compose SQL
		$updateSQL = "UPDATE `{$dbTable}` SET ";
		$index = 0;
		$values = [];

		foreach($saveData as $field => $value) {
			// add a comma
			$index++;
			if ($index!=1) {
				$updateSQL .= ", ";
			}

			$updateSQL .= "`{$field}`=?";
			$values[] = $value;
		}

		// finish the sql
		$updateSQL .= " WHERE `{$primaryKeyName}`=?";
		$values[] = $primaryKeyValue;

		return $this->execute($updateSQL, $values);

	}

	/**
	 * Execute delete operation
	 * @param String $dbTable
	 * @param String $primaryKeyName
	 * @param String $primaryKeyValue
	 */
	public function delete($dbTable, $primaryKeyName, $primaryKeyValue)
	{
		$values = [];
		$deleteSQL = "DELETE FROM `{$dbTable}` WHERE `{$primaryKeyName}`=?";
		$values[] = $primaryKeyValue;

		return $this->execute($deleteSQL, $values);
	}

	/**
	 * Execute command to start transaction
	 * @throws \Exception
	 * @throws \DBException
	 */
	public function startTransaction()
	{
		if ($this->transaction) {
			throw new \Exception("Starting transaction failed because another has already been started");
		}

		if (!$this->execute("START TRANSACTION")) {
			throw new \DBException('`START TRANSACTION` failed.');
		}
		$this->transaction = true;
	}

	/**
	 * Execute command to commit changes
	 * @throws \DBException
	 */
	public function commitTransaction()
	{
		if (!$this->transaction) {
			throw new \DBException('You cannot COMMIT because no transaction has been started.');
		}
		if (!$this->execute("COMMIT")) {
			throw new \DBException('`COMMIT` failed.');
		}

		$this->transaction = false;
	}

	/**
	 * Execute command to rollback changes
	 * @throws \DBException
	 */
	public function rollbackTransaction()
	{
		if (!$this->transaction) {
			throw new \DBException('You cannot ROLLBACK because no transaction has been started.');
		}
		if (!$this->execute("ROLLBACK")) {
			throw new \DBException('`ROLLBACK` failed.');
		}
		$this->transaction = false;
	}

	/**
	 * Close database connection
	 */
	public function close()
	{
		if ($this->dbConn) {
			$this->dbConn->Close();
			unset($this->dbConn);
		}
	}

	/**
	 * Select row from database
	 * @param String $tableName
	 * @param String $primaryKeyName
	 * @param String $primaryKeyValue
	 * @throws \DBException
	 */
	public function getRow($tableName, $primaryKeyName, $primaryKeyValue)
	{
		// connect to database if not already connected
		if (!$this->dbConn) {
			$this->connect();
		}

		// compose sql
		$sql = "SELECT * from `{$tableName}` WHERE `{$primaryKeyName}`=?";

		$result = $this->dbConn->GetRow($sql, (Array)$primaryKeyValue);

		if ($this->dbConn->ErrorNo()) {
			throw new \DBException(sprintf('Cannot select information from table `%s` with primary key name `%s` and primary key value `%s`. Error message: %s', $tableName, $primaryKeyName, $primaryKeyValue, $this->dbConn->ErrorMsg()));
		}

		return $result;
	}

	public function getErrorMessage()
	{
		return $this->dbConn->ErrorMsg();
	}
}