<?php
/**
 * Copyright 2017 Wim van Ravesteijn
*
* This file is part of AEGEE-Europe Statutory Event Applications.
*
* AEGEE-Europe Statutory Event Applications is free software: you can
* redistribute it and/or modify it under the terms of the GNU General
* Public License as published by the Free Software Foundation, either
* version 3 of the License, or (at your option) any later version.
*
* AEGEE-Europe Statutory Event Applications is distributed in the hope
* that it will be useful, but WITHOUT ANY WARRANTY; without even the
* implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
* PURPOSE.  See the GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with AEGEE-Europe Statutory Event Applications.  If not, see
* <http://www.gnu.org/licenses/>.
*/

require_once dirname(__FILE__) . "/IDB.php";
require_once dirname(__FILE__) . "/MySQLResult.php";
if( !interface_exists("IDebug") ) {
    require_once dirname(__FILE__) . "/../IDebug.php";
}

class MySQL implements IDB {
	private $host;
	
	private $user;
	
	private $password;
	
	private $database;
	
	private $debug;
	
	private $dbConnection = NULL;
	
	public function __construct($host, $user, $password, $database, IDebug $debug) {
		$this->host = $host;
		$this->user = $user;
		$this->password = $password;
		$this->database = $database;
		$this->debug = $debug;
	}
	
	public function __destruct() {
		if( $this->dbConnection != NULL ) {
			mysqli_close($this->dbConnection);
		}
	}
		
	private function connect() {
		if( $this->dbConnection == NULL ) {
			$this->dbConnection = new mysqli($this->host, $this->user, $this->password, $this->database);
		}
	}
	
	/**
	 * Fetch a row from a table
	 * 
	 * @param string $table
	 * @param int $id
	 * @return array associative array 
	 */
	public function fetchRow($table, $id, $idField = "id") {
		$this->connect();
		$query = "SELECT * FROM " . $this->escapeField($table) . " WHERE " . $this->escapeField($idField) . " = " . $this->escapeValue($id);
		$result = $this->dbConnection->query($query);
		if( $result === false ) {
			return null;
		}else {
			return $result->fetch_assoc();
		}
	}
	
	/**
	 * Select data
	 *
	 * @param string $query
	 * @return IResult
	 */
	public function select($query) {
		$this->connect();
		$result = $this->dbConnection->query($query);
		if( $result === false ) {
			$this->debug->debug($this->dbConnection->error);
			$this->debug->debug($query);
			return null;
		}else {
			return new MySQLResult($result);
		}
	}

	/**
	 * Insert data into a table
	 *
	 * @param string $table
	 * @param array $data
	 * @return int insertId, false in case of error
	 */
	public function insert($table, $data) {
		$this->connect();
		$query = $this->createInsertQuery($table, $data);
		if( $this->dbConnection->query($query) ) {
			return $this->dbConnection->insert_id;
		}else {
			$this->debug->debug($this->dbConnection->error);
			return false;
		}
	}

	private function createInsertQuery($table, $data) {
		$fields = array();
		$values = array();
		foreach( $data as $field => $value ) {
			$fields[] = $this->escapeField($field);
			$values[] = ($value == NULL ? "NULL" : $this->escapeValue($value));
		}
		return "INSERT INTO " . $this->escapeField($table) . " (" . implode(", ", $fields) . ") VALUES (" . implode(", ", $values) . ")";
	}

	/**
	 * Update a record
	 *
	 * @param string $table
	 * @param int $id
	 * @param array $data
	 * @param string $idField
	 * @return boolean true on success, false otherwise
	 */
	public function update($table, $id, $data, $idField = "id") {
		$this->connect();
		$query = $this->createUpdateQuery($table, $id, $data, $idField);
		if( $this->dbConnection->query($query) ) {
			return true;
		}else {
			$this->debug->debug($this->dbConnection->error);
			$this->debug->debug("Query: " . $query);
			return false;
		}
	}
	
	private function createUpdateQuery($table, $id, $data, $idField = "id") {
		$query = "UPDATE " . $this->escapeField($table) . " SET ";
		$sets = array();
		foreach( $data as $key => $value) {
			$sets[] = $this->escapeField($key) . " = " . ($value == NULL ? "NULL" : $this->escapeValue($value));
		}
		$query .= implode(", ", $sets);
		$query .= " WHERE " . $this->escapeField($idField) . " = " . $this->escapeValue($id);
		return $query;
	}

	/**
	 * Delete data
	 * @param string $table
	 * @param int or array of int $ids
	 */
	public function delete($table, $ids = NULL, $idField = "id") {
		$this->connect();
		$query = $this->createDeleteQuery($table, $ids, $idField);
		return $this->dbConnection->query($query);
	}
	
	private function createDeleteQuery($table, $ids = NULL, $idField = "id") {
		$query = "DELETE FROM " . $this->escapeField($table);
		if( $ids != NULL ) {
			$query .= " WHERE " . $this->escapeField($idField);
			if( is_array($ids) ) {
				$escapedIds = array();
				foreach( $ids as $id ) {
					$escapedIds[] = $this->escapeValue($id);
				}
				$query .= " IN (" . implode(", ", $escapedIds) . ")";
			}else {
				$query .= " = " . $this->escapeValue($ids);
			}
		}
		return $query;
	}
	
	public function flushPrivileges() {
		$this->connect();
		return $this->dbConnection->query("FLUSH PRIVILEGES");
	}
	
	/**
	 * Escape field for usage in select query. In case field contains a dot (.), it will be split and both sides will be escaped
	 *
	 * @param string $field
	 * @return string escaped field
	 */
	public function escapeField($field) {
		if( $field instanceof DBMethod ) {
			return $field->toQuery($this);
		}elseif( is_array($field) ) {
			$results = array();
			foreach( $field as $singleField ) {
				$results[] = $this->escapeField($singleField);
			}
			return implode(", ", $results);
		}else {
			return "`" . implode("`.`", preg_split("/\./", $field)) . "`";
		}
	}
	
	/**
	 * Escape value for usage in select query, including quoting non-numeric values
	 *
	 * @param string $value
	 * @return string escaped value
	 */
	public function escapeValue($value) {
		$this->connect();
		if( $value instanceof DBMethod ) {
			return $value->toQuery($this);
		}else {
			$escaped = $this->dbConnection->real_escape_string($value);
			if( is_numeric($escaped) ) {
				return $escaped;
			}else {
				return "'" . $escaped . "'";
			}
		}
	}
	
	public function getLastError() {
		return $this->dbConnection->error;
	}

	/**
	 * Return the version number of the server
	 *
	 * @return string server version
	 */
	public function getServerVersion() {
		$this->connect();
		return $this->dbConnection->server_info;
	}
	
	public function __toString() {
		return "MySQL[host=" . $this->host . "; user=" . $this->user . "; database=" . $this->database . "]";
	}
}
?>