<?php
namespace SabaiApps\Framework\DB;

use SabaiApps\Framework\Exception;

/**
 * AbstractDB class for interacting with a database.
 * Provides methods for querying, executing SQL, and managing transactions.
 */
abstract class AbstractDB {

	/**
	 * @var AbstractConnection Database connection object.
	 */
	protected $_connection;

	/**
	 * @var string Prefix for database resources.
	 */
	protected $_resourcePrefix;

	/**
	 * Constructor.
	 *
	 * @param AbstractConnection $connection Database connection object.
	 * @param string             $resourcePrefix Prefix for database resources.
	 */
	public function __construct( AbstractConnection $connection, $resourcePrefix = '' ) {
		$this->_connection     = $connection->connect();
		$this->_resourcePrefix = $resourcePrefix;
	}

	/**
	 * Gets the database connection object.
	 *
	 * @return AbstractConnection Database connection object.
	 */
	public function getConnection() {
		return $this->_connection;
	}

	/**
	 * Gets the prefix used for database resources.
	 *
	 * @return string Resource prefix.
	 */
	public function getResourcePrefix() {
		return $this->_resourcePrefix;
	}

	/**
	 * Begins a transaction.
	 */
	public function begin() {
		$this->exec( 'BEGIN' );
	}

	/**
	 * Commits a transaction.
	 */
	public function commit() {
		$this->exec( 'COMMIT' );
	}

	/**
	 * Rolls back a transaction.
	 */
	public function rollback() {
		$this->exec( 'ROLLBACK' );
	}

	/**
	 * Queries the database and returns a rowset.
	 *
	 * @param string $sql SQL query string.
	 * @param int    $limit Maximum number of rows to return.
	 * @param int    $offset Number of rows to skip.
	 * @return AbstractRowset Rowset object.
	 * @throws Exception If the query fails.
	 */
	public function query( $sql, $limit = 0, $offset = 0 ) {
		$query = $this->getQuery( $sql, $limit, $offset );
		if ( ! $rs = $this->_doQuery( $query ) ) {
			throw new Exception( sprintf( '%s SQL: %s', $this->lastError(), $query ) );
		}
		return $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 AbstractRowset Rowset object.
	 */
	public function bigQuery( $sql, $limit = 0, $offset = 0, array $options = array() ) {
		$options += array(
			'sql_big_selects'      => true,
			'group_concat_max_len' => null,
		);
		return $this->_doBigQuery( $sql, $limit, $offset, $options );
	}

	/**
	 * 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 AbstractRowset Rowset object.
	 */
	protected function _doBigQuery( $sql, $limit = 0, $offset = 0, array $options = array() ) {
		return $this->query( $sql, $limit, $offset );
	}

	/**
	 * Executes an SQL statement.
	 *
	 * @param string $sql SQL statement.
	 * @return int Number of rows affected.
	 * @throws Exception If the execution fails.
	 */
	public function exec( $sql ) {
		if ( ! $this->_doExec( $sql ) ) {
			throw new Exception( sprintf( '%s SQL: %s', $this->lastError(), $sql ) );
		}
		return $this->affectedRows();
	}

	/**
	 * Seeds the random number generator for database operations.
	 *
	 * @param int $seed Seed value.
	 */
	public function seedRandom( $seed ) {}

	/**
	 * Encodes a value as a JSON string.
	 *
	 * @param mixed $value Value to encode.
	 * @return string JSON-encoded string.
	 */
	public function jsonEncode( $value ) {
		$value = is_array( $value ) ? $value : array();
		return json_encode( $value, JSON_UNESCAPED_UNICODE );
	}

	/**
	 * 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.
	 */
	abstract public function getQuery( $sql, $limit = 0, $offset = 0 );

	/**
	 * Executes a query and returns a rowset object.
	 *
	 * @param string $sql SQL query string.
	 * @return AbstractRowset|false Rowset object on success, false on failure.
	 */
	abstract protected function _doQuery( $sql );

	/**
	 * Executes an SQL statement.
	 *
	 * @param string $sql SQL statement.
	 * @return bool True on success, false on failure.
	 */
	abstract protected function _doExec( $sql );

	/**
	 * Gets the number of affected rows from the last operation.
	 *
	 * @return int Number of affected rows.
	 */
	abstract public function affectedRows();

	/**
	 * Gets the ID of the last inserted row.
	 *
	 * @param string|null $name Optional sequence name.
	 * @return mixed Last inserted ID.
	 */
	abstract public function lastInsertId( $name = null );

	/**
	 * Gets the last error message and code.
	 *
	 * @return string Error message and code.
	 */
	abstract public function lastError();

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

	/**
	 * Escapes a string value for safe use in SQL queries.
	 *
	 * @param string $value String value to escape.
	 * @return string Escaped string.
	 */
	abstract public function escapeString( $value );

	/**
	 * Escapes a datetime value for safe use in SQL queries.
	 *
	 * @param string|int $value Datetime value to escape.
	 * @return string Escaped datetime string.
	 */
	abstract public function escapeDatetime( $value );

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

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

	/**
	 * Gets the random number generator function for the database.
	 *
	 * @param int|null $seed Optional seed value.
	 * @return string Random function string.
	 */
	abstract public function getRandomFunc( $seed = null );

	/**
	 * Gets the 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.
	 */
	abstract public function getGroupConcatFunc( $column, $separator = ',', $distinct = false );
}
