<?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;

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

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

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

	/** @var int|bool Number of rows returned by the last query, or 0 if not applicable. */
	public int|bool $num_rows = 0;

	/** @var ?int The row ID of the last inserted row, or null if none. */
	public ?int $insert_id = null;

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

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

	/**
	 * Get the SQLite library version as an integer.
	 *
	 * Note: This currently calls mysqli_get_server_version (likely a bug — should use SQLite3::version()).
	 *
	 * @return int|false The version integer, or false if not connected.
	 */
	public static function version(): int|false {
		if (self :: $link) {
			return self::$link->version();
		}

		return false;
	}

	/**
	 * Get the SQLite library version string.
	 *
	 * @return string|false The version string, or false if not connected.
	 */
	public static function info(): string|false {
		if (self :: $link) {
			return self :: $link->version();
		}

		return false;
	}

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

		return '';
	}

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

		return 0;
	}

	/**
	 * Pass through the statement result unchanged.
	 *
	 * @param mixed $stmt The statement result.
	 *
	 * @return mixed The same statement result.
	 */
	public function get_result(mixed $stmt): mixed {
		return $stmt;
	}

	/**
	 * @param string  $filename The SQLite database file path.
	 * @param string  $dbname   Database name (unused for SQLite).
	 * @param string  $user     Database user (unused for SQLite).
	 * @param string  $pass     Database password (unused for SQLite).
	 * @param ?string $port     Port (unused for SQLite).
	 * @param string  $prefix   Table name prefix.
	 */
	public function __construct(string $filename = DB_HOST, string $dbname = DB_NAME, string $user = DB_USER, string $pass = DB_PASS, ?string $port = null, string $prefix = DB_PREFIX) {
		//return $this->connect($host, $dbname, $user, $pass, $port,  $prefix);
	}

	/**
	 * Connect to the SQLite database.
	 *
	 * Uses a singleton pattern — returns the existing connection if already open.
	 * Enables exceptions for error handling.
	 *
	 * @param string  $filename The SQLite database file path.
	 * @param string  $dbname   Database name (unused for SQLite).
	 * @param string  $user     Database user (unused for SQLite).
	 * @param string  $pass     Database password (unused for SQLite).
	 * @param ?string $port     Port (unused for SQLite).
	 * @param string  $prefix   Table name prefix.
	 *
	 * @return \SQLite3 The database connection.
	 *
	 * @throws \Exception On connection failure.
	 */
	public function connect(string $filename = DB_HOST, string $dbname = DB_NAME, string $user = DB_USER, string $pass = DB_PASS, ?string $port = null, string $prefix = DB_PREFIX): \SQLite3 {
		if (self :: $link) {
			return self :: $link;
		}

		try {
			self :: $link = new \SQLite3($filename); //, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
		} catch (\Exception  $e) {
			$errorMessage = str_replace($pass,'*****', $e->getMessage());

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

		self :: $link->enableExceptions(true);

		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 column comments (unused for SQLite).
	 *
	 * @return array<string, array{name: string, d: mixed, n: string, t: string}>|false
	 *                                                                                  Associative array keyed by column name, or false on failure.
	 */
	public function getColumnsMeta(string $tableName, bool $comment = false): array|false {
		$sql =
		"SELECT type as t, name, dflt_value as d, `notnull` as n FROM pragma_table_info('$tableName');";

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

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

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

			return $columns;
		} else {
		}

		return false;
	}

	/**
	 * Get all table names in the database.
	 *
	 * @param string $db The database name (unused for SQLite — reads sqlite_master).
	 *
	 * @return array<int, string>|false List of table names, or false on failure.
	 */
	public function getTableNames(string $db = DB_NAME): array|false {
		$sql = "SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%'  AND name NOT LIKE '%_search%' ORDER BY name";

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

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

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

			return $names;
		} else {
		}

		return false;
	}

	/**
	 * Escape a value for safe inclusion in a SQL query.
	 *
	 * Strings are escaped with SQLite3::escapeString. 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->escapeString($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);
	}

	/**
	 * Fetch a single scalar value from a result.
	 *
	 * @param \SQLite3Result $result The query result.
	 *
	 * @return string|null|false The first column value, null if row exists but value is null, or false if no results.
	 */
	public function fetchOne(\SQLite3Result $result): string|null|false {
		$return = false;

		if ($result) {
			$return = $result->fetchArray(SQLITE3_NUM)[0] ?? null;
		}

		return $return;
	}

	/**
	 * Fetch a single row from a result.
	 *
	 * @param \SQLite3Result $result The query result.
	 *
	 * @return array<string, mixed>|false The row as an associative array, or false if no results.
	 */
	public function fetchArray(\SQLite3Result $result): array|false {
		$return = false;

		if ($result) {
			$return = $result->fetchArray(SQLITE3_ASSOC);
		}

		return $return;
	}

	/**
	 * Fetch all rows from a result.
	 *
	 * @param \SQLite3Result $result The query result.
	 *
	 * @return array<int, array<string, mixed>> All result rows.
	 */
	public function fetchAll(\SQLite3Result $result): array {
		$return = [];

		if ($result) {
			while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
				$return[] = $row;
			}
		}

		return $return;
	}

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

		$result = false;

		try {
			$result = self :: $link->query($sql);

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

			if ($result) {
				$this->affected_rows = self :: $link->changes();
				$this->insert_id     = self :: $link->lastInsertRowID();
				$this->num_rows      = $result->numColumns() && $result->columnType(0) != SQLITE3_NULL;
				//$result->finalize();
			} else {
				$message = $this->error() . "\n" . $sql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

				throw new \Exception($message, $this->errorCode());
			}
		} catch (\Exception $e) {
			$message = $e->getMessage() . "\n$sql\n" . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

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

		return $result;
	}

	/**
	 * Execute multiple SQL statements by splitting on semicolons.
	 *
	 * @param string $sql The SQL statements to execute (semicolon-separated).
	 *
	 * @return array<int, \SQLite3Result> Array of results from each statement.
	 *
	 * @throws \Exception On any statement failure.
	 */
	public function multi_query(string $sql): array {
		$results = [];

		$queries = preg_split('/;\n/', $sql);

		foreach ($queries as $query) {
			if (empty(trim($query))) {
				continue;
			}

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

			try {
				$result = self :: $link->query("$query;");

				if ($result) {
					$this->affected_rows = self :: $link->changes();
					$this->insert_id     = self :: $link->lastInsertRowID();
					$this->num_rows      = $result->numColumns() && $result->columnType(0) != SQLITE3_NULL;
					//$result->finalize();
				} else {
					$message = $this->error() . "\n" . $sql . "\n - " . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

					throw new \Exception($message, $this->errorCode());
				}

				$results[] = $result;
			} catch (\Exception $e) {
				$message = $e->getMessage() . "\n$sql\n" . print_r(debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 1), true);

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

		return $results;
	}

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

		return null;
	}

	/**
	 * Prepare and execute a parameterised query.
	 *
	 * Fires an Event::trigger hook before execution. Converts named parameters
	 * to positional ? placeholders, binds values with appropriate SQLite3 type
	 * constants, and executes the statement. Updates affected_rows, insert_id,
	 * and num_rows 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 \SQLite3Result|false|null The query result, false, or null on failure.
	 *
	 * @throws \Exception On prepare or execute failure.
	 */
	public function execute(string $sql, array $params = [], array $paramTypes = []): \SQLite3Result|false|null {
		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 (\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($paramTypes)*/) {
			foreach ($parameters as $key => $value) {
				$type  = $types[$key] ?? 's';
				$type  = ($type == 'i') ? SQLITE3_INTEGER : SQLITE3_TEXT;
				$index = (int)$key + 1;
				$stmt->bindValue($index, $value, $type);
			}
		} else {
			if (DEBUG) {
				error_log((self :: $link->lastErrorMsg ?? '') . ' ' . $this->debugSql($origSql, $params, $paramTypes));
			}
		}

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

		if ($stmt) {
			$result = null;

			try {
				if ($result = $stmt->execute()) {
					$this->affected_rows = self :: $link->changes();
					$this->insert_id     = self :: $link->lastInsertRowID();
					$this->num_rows      = $result->numColumns() && $result->columnType(0) != SQLITE3_NULL;

					return $result;
				} else {
					error_log(print_r($result, 1));
					error_log($this->debugSql($sql, $params, $paramTypes));
				}
			} catch (\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 SQLite3 type constant, or null for auto-detection.
	 *
	 * @return void
	 */
	public function bind(string|int $param, mixed $value, ?int $type = null): void {
		$this->stmt->bindValue($param, $value, $type);
	}
}
