<?php

/**
 * Vvveb
 *
 * Copyright (C) 2022  Ziadin Givan
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License as
 * published by the Free Software Foundation, either version 3 of the
 * License, or (at your option) any later version.
 *
 * This program 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <https://www.gnu.org/licenses/>.
 *
 */

namespace Vvveb\System\Db;

use Vvveb\System\Event;

/**
 * Polyfill for mysqli_result when native fetch_all is missing on mysqli_stmt.
 *
 * Wraps a prepared statement and provides fetch_all, fetch_assoc, and fetchRow
 * methods by binding result columns and iterating through the statement.
 */
class mysqli_result {
	/** @var \mysqli_stmt The underlying prepared statement. */
	private \mysqli_stmt $stmt;

	/** @var ?\mysqli_result Metadata for the statement result. */
	private ?\mysqli_result $meta = null;

	/** @var array row. */
	private $row = [];

	/**
	 * @param \mysqli_stmt $stmt The prepared statement to wrap.
	 */
	public function __construct(\mysqli_stmt $stmt) {
		$this->stmt = $stmt;

		$this->stmt->store_result();
		$meta = $this->stmt->result_metadata();
		if ($meta) {
			$this->row  = [];
			/*
			for ($i = 0;$i < $meta->field_count; $i++) {
				$field = $meta->fetch_field_direct($i);
				$name = $field->name;
				$this->row[$name] = [];
				$params[] = &$this->row[$name];
			}
			*/

			while ($field = $meta->fetch_field()) {
				$name = $result = preg_replace('/[^a-zA-Z0-9_-]+/', '', $field->name);
				$this->row[$name] = [];
				$params[] = &$this->row[$name];
			}

			call_user_func_array([$this->stmt, 'bind_result'], $params);
		}
	}

	/**
	 * Fetch all rows as an associative array (delegates to fetch_assoc).
	 *
	 * @return array<int, array<string, mixed>> All result rows.
	 */
	public function fetch_all(): array {
		return $this->fetch_assoc();
	}

	/**
	 * Fetch all rows as an associative array (delegates to fetch_assoc).
	 *
	 * @param int $resulttype Ignored — always returns associative.
	 *
	 * @return array<int, array<string, mixed>>|null All result rows.
	 */
	public function fetch_array(int $resulttype): ?array {
		if ($result = $this->stmt->fetch()) {
			//dereference
			foreach ($this->row as $key => $value) {
				if ($resulttype == MYSQLI_ASSOC) {
					$row[$key] = $value;
				} else {
					$row[] = $value;
				}
			}

			return $row;
		} else {
			//$this->stmt->close();
			return $result;
		}
	}

	/**
	 * Fetch all rows as an associative array by binding result columns.
	 *
	 * @return array<int, array<string, mixed>> All result rows.
	 */
	public function fetch_assoc(): array {
		$result = [];
		while ($return = $this->stmt->fetch()) {
			$c = [];

			$row = $this->row;
			foreach ($row as $key => $val) {
				$c[$key] = $val;
			}
			$result[] = $c;
		}

		return $result;
	}

	/**
	 * Stub for interface compatibility — does nothing.
	 *
	 * @return void
	 */
	public function fetch_field(): void {
	}

	/**
	 * Stub for interface compatibility — does nothing.
	 *
	 * @return void
	 */
	public function fetch_fields(): void {
	}

	/**
	 * Fetch all rows as an associative array (alias for fetch_assoc).
	 *
	 * @return array<int, array<string, mixed>> All result rows.
	 */
	public function fetchRow(): array {
		if ($result = $this->stmt->fetch()) {
			//dereference
			foreach ($this->row as $key => $value) {
				$row[$key] = $value;
			}
			return $row;
		} else {
			//$this->stmt->close();
			return $result;
		}
	}

	private function destroy() {
		$this->stmt->close();
	}
}

/**
 * MySQL database driver using the mysqli extension.
 *
 * Provides connection management, query execution with prepared statements,
 * result fetching, escaping, and schema introspection for MySQL/MariaDB.
 */
class Mysqli extends DBDriver {
	/** @var ?\MySQLi The shared database connection (singleton per process). */
	private static ?\MySQLi $link = null;

	/** @var ?\mysqli_stmt The current prepared statement, if any. */
	private ?\mysqli_stmt $stmt = null;

	/** @var int Number of rows affected by the last query. */
	public int $affected_rows = 0;

	/** @var ?int The auto-increment ID generated by the last INSERT. */
	public ?int $insert_id = null;

	/** @var string Identifier quote character for MySQL. */
	public string $quote  = '`';

	/** @var string Table name prefix. */
	public string $prefix = ''; //'vv_';

	/**
	 * Get the MySQL server version as an integer.
	 *
	 * @return int|null The server version (e.g. 80031) or null if not connected.
	 */
	public static function version(): ?int {
		if (self :: $link) {
			return mysqli_get_server_version(self :: $link);
		}

		return null;
	}

	/**
	 * Get the MySQL server version string.
	 *
	 * @return string|null The version string (e.g. '8.0.31') or null if not connected.
	 */
	public static function info(): ?string {
		if (self :: $link) {
			return mysqli_get_server_info(self :: $link);
		}

		return null;
	}

	/**
	 * Get the error message from the last MySQL operation.
	 *
	 * @return string|null The error message, or null if not connected.
	 */
	public function error(): ?string {
		if (self :: $link) {
			return self :: $link->error;
		}

		return null;
	}

	/**
	 * Get the error code from the last MySQL operation.
	 *
	 * @return int|null The error code, or null if not connected.
	 */
	public function errorCode(): ?int {
		if (self :: $link) {
			return self :: $link->errno;
		}

		return null;
	}

	/**
	 * Wrap a mysqli_stmt in a mysqli_result polyfill for fetch_all support.
	 *
	 * @param \mysqli_stmt $stmt The prepared statement.
	 *
	 * @return mysqli_result The polyfilled result object.
	 */
	public function get_result(\mysqli_stmt $stmt): mysqli_result {
		$result = new mysqli_result($stmt);

		return $result;
	}

	/**
	 * @param string   $host   Database host.
	 * @param string   $dbname Database name.
	 * @param string   $user   Database user.
	 * @param string   $pass   Database password.
	 * @param int|null $port   Database port.
	 * @param string   $prefix Table name prefix.
	 */
	public function __construct(string $host = DB_HOST, string $dbname = DB_NAME, string $user = DB_USER, string $pass = DB_PASS, ?int $port = DB_PORT,  string $prefix = DB_PREFIX) {
		//return $this->connect($host, $dbname, $user, $pass, $port,  $prefix);
	}

	/**
	 * Connect to the MySQL database.
	 *
	 * Uses a singleton pattern — returns the existing connection if already open.
	 * Throws an exception with a sanitised error message on failure.
	 *
	 * @param string   $host   Database host.
	 * @param string   $dbname Database name.
	 * @param string   $user   Database user.
	 * @param string   $pass   Database password.
	 * @param int|null $port   Database port.
	 * @param string   $prefix Table name prefix.
	 *
	 * @return ?\MySQLi The database connection, or null on failure.
	 *
	 * @throws \Exception On connection failure.
	 */
	public function connect(string $host = DB_HOST, string $dbname = DB_NAME, string $user = DB_USER, string $pass = DB_PASS, ?int $port = DB_PORT,  string $prefix = DB_PREFIX): ?\MySQLi {
		//mysqli_report(MYSQLI_REPORT_OFF);
		//connect to database
		if (self :: $link) {
			return self :: $link;
		}
		$this->prefix = $prefix;

		\mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

		try {
			self :: $link = new \Mysqli(/*'p:' . */$host, $user, $pass, $dbname, $port);
			//self :: $link = $this;
		} catch (\mysqli_sql_exception $e) {
			$errorMessage = str_replace($pass,'*****', $e->getMessage());

			throw new \Exception($errorMessage, $e->getCode());
		}

		// check if a connection established
		if (\mysqli_connect_errno()) {
			$error        = mysqli_connect_error();
			$errorMessage = str_replace($pass,'*****', $error);

			throw new \Exception($errorMessage, mysqli_connect_errno());
		}

		if ((! self :: $link instanceof \MySQLi)) {
			throw new \Exception('Mysqli not an object', mysqli_connect_errno());
		}

		//sync database time
		//$this->execute('SET `time_zone` = :zone', ['zone' => $this->escape(date('P'))]);

		return self :: $link;
	}

	/**
	 * Get all columns for a table used for sanitizing input.
	 *
	 * @param string $tableName The table name to introspect.
	 * @param bool   $comment   Whether to include the column comment field.
	 *
	 * @return array<string, array{name: string, d: mixed, n: string, t: string, e: string, l: int|null, c?: string}>|false
	 *                                                                                                                      Associative array keyed by column name, or false on failure.
	 */
	public function getColumnsMeta(string $tableName, bool $comment = false): array|false {
		$sql =
		'SELECT COLUMN_NAME as name, COLUMN_DEFAULT as d, IS_NULLABLE  as n, DATA_TYPE as t, EXTRA as e, CHARACTER_MAXIMUM_LENGTH as l'
		. ($comment ? ', COLUMN_COMMENT as c' : '') .
		' FROM `INFORMATION_SCHEMA`.`COLUMNS` 
		WHERE `TABLE_SCHEMA`= "' . DB_NAME . '" 
			AND `TABLE_NAME`="' . $tableName . '"';

		if ($result = $this->query($sql)) {
			//$columns = $result->fetch_all(MYSQLI_ASSOC);
			$columns = [];

			while ($row = $result->fetch_assoc()) {
				$columns[$row['name']] = $row;
			}

			/* free result set */
			$result->close();

			return $columns;
		} else {
		}

		return false;
	}

	/**
	 * Get all table names in the database.
	 *
	 * @param string $db The database name.
	 *
	 * @return array<int, string>|false List of table names, or false on failure.
	 */
	public function getTableNames(string $db = DB_NAME): array|false {
		$sql ="SELECT table_name as name
			FROM information_schema.tables 
			WHERE table_schema = '$db' ORDER BY table_name";

		if ($result = $this->query($sql)) {
			//$names = $result->fetch_all(MYSQLI_ASSOC);
			$names = [];

			while ($row = $result->fetch_assoc()) {
				$names[] = $row['name'];
			}

			/* free result set */
			$result->close();

			return $names;
		} else {
		}

		return false;
	}

	/**
	 * Select a different database on the current connection.
	 *
	 * @param string $db The database name.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function select_db(string $db): bool {
		return self :: $link->select_db($db);
	}

	/**
	 * Execute a raw SQL query.
	 *
	 * Automatically connects if not connected. Updates affected_rows and insert_id.
	 *
	 * @param string $sql The SQL query to execute.
	 *
	 * @return bool|\mysqli_result Query result or true for success, false on failure.
	 *
	 * @throws \Exception On query failure.
	 */
	public function query(string $sql): bool|\mysqli_result {
		if (! self :: $link) {
			$this->connect();
		}

		if (LOG_SQL_QUERIES) {
			error_log($sql);
		}

		try {
			$result = self :: $link->query($sql);
		} catch (\mysqli_sql_exception $e) {
			$message = $e->getMessage() . "\n" . $sql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

			throw new \Exception($message, $e->getCode());
		}

		if ($result) {
			$this->affected_rows = self :: $link->affected_rows;
			$this->insert_id     = self :: $link->insert_id;
		}

		return $result;
	}

	/**
	 * Execute multiple SQL statements in a single call.
	 *
	 * @param string $sql The SQL statements to execute (semicolon-separated).
	 *
	 * @return bool True on success, false on failure.
	 */
	public function multi_query(string $sql): bool {
		if (! self :: $link) {
			$this->connect();
		}

		$result = self :: $link->multi_query($sql);

		if ($result) {
			$this->affected_rows = self :: $link->affected_rows;
			$this->insert_id     = self :: $link->insert_id;
		}

		return $result;
	}

	/**
	 * Escape a value for safe inclusion in a SQL query.
	 *
	 * Strings are escaped with real_escape_string. Null values become 'null'.
	 * All other types are returned as-is.
	 *
	 * @param mixed $string The value to escape.
	 *
	 * @return mixed The escaped value.
	 */
	public function escape(mixed $string): mixed {
		if (is_string($string)) {
			return self :: $link->real_escape_string($string);
		}

		if (is_null($string)) {
			return 'null';
		}

		return $string;
	}

	/**
	 * Escape a string value for use in a SQL literal.
	 *
	 * @param string $string The string to escape.
	 *
	 * @return string The escaped string.
	 */
	public function escapeLiteral(string $string): string {
		return $this->escape($string);
	}

	/**
	 * Generate a MySQL-compatible LIMIT clause.
	 *
	 * @param int|string $start Offset (0-based).
	 * @param int|string $limit Maximum number of rows.
	 *
	 * @return string The LIMIT clause (e.g. 'LIMIT 0, 10').
	 */
	public function sqlLimit(int|string $start, int|string $limit): string {
		return "LIMIT $start, $limit";
	}

	/**
	 * Fetch a single row from a statement or result set.
	 *
	 * @param \mysqli_stmt|\mysqli_result $stmt The statement or result to fetch from.
	 *
	 * @return array<string, mixed> The fetched row, or empty array if no rows.
	 */
	public function fetchArray(\mysqli_stmt|\mysqli_result $stmt): array {
		$data      = [];
		$statement = (get_class($stmt) === 'mysqli_stmt');

		if ($statement) {
			$this->store_result();
			$result = $stmt->get_result();
		} else {
			$result = $stmt;
		}

		if ($result) {
			$result = $result->fetch_array(MYSQLI_ASSOC);

			if ($data) {
				$data = $result;

				if ($statement && $stmt->more_results()) {
					$stmt->next_result();
				}
			}
		}

		return $data;
	}

	/**
	 * Fetch all rows from a statement or result set.
	 *
	 * @param \mysqli_stmt|\mysqli_result $stmt The statement or result to fetch from.
	 *
	 * @return array<int, array<string, mixed>> All result rows.
	 */
	public function fetchAll(\mysqli_stmt|\mysqli_result $stmt): array {
		$data      = [];
		$statement = (get_class($stmt) === 'mysqli_stmt');

		if ($statement) {
			$this->store_result();
			$result = $stmt->get_result();
		} else {
			$result = $stmt;
		}

		if ($result) {
			$data = $result->fetch_all(MYSQLI_ASSOC);
		}

		return $data;
	}

	/**
	 * Store the result of the last query in the client-side buffer.
	 *
	 * @return ?\mysqli_result The stored result, or null on failure.
	 */
	public function store_result(): \mysqli_result|false {
		return self :: $link->store_result();
	}

	/**
	 * Check if the current statement has more result sets.
	 *
	 * @return bool True if more results are available.
	 */
	public function more_results(): bool {
		return self :: $link->more_results();
	}

	/**
	 * Move to the next result set in a multi-query.
	 *
	 * @return bool True on success, false on failure.
	 */
	public function next_result(): bool {
		return self :: $link->next_result();
	}

	/**
	 * Close the database connection.
	 *
	 * @return ?bool True on success, null if not connected.
	 */
	public function close(): ?bool {
		if ((self :: $link instanceof \MySQLi)/* && self :: $link->ping()*/) {
			return self :: $link->close();
		}

		return null;
	}

	/**
	 * Prepare and execute a parameterized query.
	 *
	 * Fires an Event::trigger hook before execution. Converts named parameters
	 * to positional placeholders, binds parameters, and executes the statement.
	 * Updates affected_rows and insert_id on success.
	 *
	 * @param string                $sql        The SQL query with :named parameters.
	 * @param array<string, mixed>  $params     Named parameter values.
	 * @param array<string, string> $paramTypes Optional explicit type overrides.
	 *
	 * @return ?\mysqli_stmt The executed statement, or null on failure.
	 *
	 * @throws \Exception On prepare or execute failure.
	 */
	public function execute(string $sql, array $params = [], array $paramTypes = []): ?\mysqli_stmt {
		list($sql, $params) = Event::trigger(__CLASS__,__FUNCTION__, $sql, $params);
		//save orig sql for debugging info
		$origSql = $sql;

		if (! self :: $link) {
			$this->connect();
		}

		list($parameters, $types) = $this->paramsToQmark($sql, $params, $paramTypes);

		try {
			$stmt = self::$link->prepare($sql);
		} catch (\mysqli_sql_exception $e) {
			$message = $e->getMessage() . "\n" . $this->debugSql($origSql, $params, $paramTypes) . "\n - " . $origSql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

			throw new \Exception($message, $e->getCode());
		}

		if ($stmt && ! empty($types)) {
			array_unshift($parameters, $types);

			//hack for php 7.x bind_param "expected to be a reference, value given" stupid warning
			$referenceArray = [];

			foreach ($parameters as $key => $value) {
				$referenceArray[$key] = &$parameters[$key];
			}

			@call_user_func_array([$stmt, 'bind_param'], $referenceArray);
		}

		if (LOG_SQL_QUERIES) {
			error_log($this->debugSql($origSql, $params, $paramTypes));
		}

		if ($stmt) {
			try {
				if ($stmt->execute()) {
					$this->affected_rows = self :: $link->affected_rows;
					$this->insert_id     = self :: $link->insert_id;

					return $stmt;
				} else {
					error_log(print_r($stmt, 1));
					error_log($this->debugSql($sql, $params, $paramTypes));
				}
			} catch (\mysqli_sql_exception $e) {
				$message = $e->getMessage() . "\n" . $origSql . "\n" . $this->debugSql($origSql, $params, $paramTypes) . "\n" . print_r($parameters, 1) . $types . "\n" . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

				throw new \Exception($message, $e->getCode());
			}
		} else {
			error_log(print_r($stmt, 1));
			error_log($this->debugSql($origSql, $params, $paramTypes));
		}

		return $stmt;
	}

	/**
	 * Bind a parameter value to the current prepared statement.
	 *
	 * @param string|int $param The parameter index or name.
	 * @param mixed      $value The value to bind.
	 * @param int|null   $type  The PDO type constant, or null for auto-detection.
	 *
	 * @return void
	 */
	public function bind(string|int $param, mixed $value, ?int $type = null): void {
		// TODO: bindValue() is PDO-only; store bindings for later use if needed
	}
}
