<?php
namespace SabaiApps\Framework\DB;

/**
 * MySQL class for interacting with a MySQL database using the MySQLi extension.
 * Provides methods for querying, executing SQL, and escaping values.
 */
class MySQL extends AbstractDB {

	/**
	 * Builds a SQL query with optional LIMIT and OFFSET clauses.
	 *
	 * @param string $sql SQL query string.
	 * @param int    $limit Maximum number of rows to return.
	 * @param int    $offset Number of rows to skip.
	 * @return string Modified SQL query string.
	 */
	public function getQuery( $sql, $limit = 0, $offset = 0 ) {
		if ( intval( $limit ) > 0 ) {
			$sql .= sprintf( ' LIMIT %d, %d', $offset, $limit );
		}

		return $sql;
	}

	/**
	 * Executes a query and returns a rowset object.
	 *
	 * @param string $query SQL query string.
	 * @return MySQLRowset|false Rowset object on success, false on failure.
	 */
	protected function _doQuery( $query ) {
		if ( ! $rs = mysqli_query( $this->_connection->getLink(), $query ) ) {
			return false;
		}

		return new MySQLRowset( $rs );
	}

	/**
	 * Executes a big query with optional settings.
	 *
	 * @param string $sql SQL query string.
	 * @param int    $limit Maximum number of rows to return.
	 * @param int    $offset Number of rows to skip.
	 * @param array  $options Additional options for the query.
	 * @return MySQLRowset|false Rowset object on success, false on failure.
	 */
	protected function _doBigQuery( $sql, $limit = 0, $offset = 0, array $options = array() ) {
		$setCommands = $revertCommands = array();

		if ( $options['sql_big_selects'] ) {
			$setCommands[]    = 'SQL_BIG_SELECTS=1';
			$revertCommands[] = 'SQL_BIG_SELECTS=0';
		}

		if ( isset( $options['group_concat_max_len'] )
			&& ( $len = (int) $options['group_concat_max_len'] )
		) {
			$setCommands[]    = "group_concat_max_len={$len}";
			$revertCommands[] = 'group_concat_max_len=1024';
		}

		if ( ! empty( $setCommands ) ) {
			$this->query( 'SET SESSION ' . implode( ', ', $setCommands ) );
		}

		try {
			return $this->query( $sql, $limit, $offset );
		} finally {
			if ( ! empty( $revertCommands ) ) {
				$this->query( 'SET SESSION ' . implode( ', ', $revertCommands ) );
			}
		}
	}

	/**
	 * Executes an SQL statement.
	 *
	 * @param string $sql SQL statement.
	 * @return bool True on success, false on failure.
	 */
	protected function _doExec( $sql ) {
		return mysqli_query( $this->_connection->getLink(), $sql );
	}

	/**
	 * Gets the ID of the last inserted row.
	 *
	 * @param string|null $name Optional sequence name.
	 * @return int Last inserted ID.
	 */
	public function lastInsertId( $name = null ) {
		return mysqli_insert_id( $this->_connection->getLink() );
	}

	/**
	 * Gets the number of affected rows from the last operation.
	 *
	 * @return int Number of affected rows.
	 */
	public function affectedRows() {
		return mysqli_affected_rows( $this->_connection->getLink() );
	}

	/**
	 * Gets the last error message and code.
	 *
	 * @return string Error message and code.
	 */
	public function lastError() {
		return sprintf( '%s(%s)', mysqli_error( $this->_connection->getLink() ), mysqli_errno( $this->_connection->getLink() ) );
	}

	/**
	 * Escapes a boolean value for safe use in SQL queries.
	 *
	 * @param bool $value Boolean value to escape.
	 * @return int Escaped boolean value.
	 */
	public function escapeBool( $value ) {
		return intval( $value );
	}

	/**
	 * Escapes a string value for safe use in SQL queries.
	 *
	 * @param string $value String value to escape.
	 * @return string Escaped string.
	 */
	public function escapeString( $value ) {
		return "'" . mysqli_real_escape_string( $this->_connection->getLink(), $value ) . "'";
	}

	/**
	 * Escapes a datetime value for safe use in SQL queries.
	 *
	 * @param string|int $value Datetime value to escape.
	 * @return string Escaped datetime string.
	 */
	public function escapeDatetime( $value ) {
		if ( is_int( $value )
			&& ( false === $value = gmdate( 'Y-m-d H:i:s', $value ) )
		) {
			$value = '0000-00-00 00:00:00';
		}
		return $this->escapeString( $value );
	}

	/**
	 * Escapes a blob value for safe use in SQL queries.
	 *
	 * @param string $value Blob value to escape.
	 * @return string Escaped blob string.
	 */
	public function escapeBlob( $value ) {
		return $this->escapeString( $value );
	}

	/**
	 * Unescapes a blob value retrieved from the database.
	 *
	 * @param string $value Blob value to unescape.
	 * @return string Unescaped blob value.
	 */
	public function unescapeBlob( $value ) {
		return $value;
	}

	/**
	 * Gets the MySQL RAND() function with an optional seed.
	 *
	 * @param int|null $seed Optional seed value.
	 * @return string RAND() function string.
	 */
	public function getRandomFunc( $seed = null ) {
		return isset( $seed ) ? 'RAND(' . (int) $seed . ')' : 'RAND()';
	}

	/**
	 * Gets the MySQL GROUP_CONCAT() function for concatenating column values.
	 *
	 * @param string $column Column name.
	 * @param string $separator Separator string.
	 * @param bool   $distinct Whether to use DISTINCT.
	 * @return string GROUP_CONCAT() function string.
	 */
	public function getGroupConcatFunc( $column, $separator = ',', $distinct = false ) {
		if ( $distinct ) {
			$column = 'DISTINCT ' . $column;
		}
		return 'GROUP_CONCAT(' . $column . ' SEPARATOR \'' . $separator . '\')';
	}
}
