<?php
	function db_get_new_handle($alt_db = NULL)
	{
		global $db_host;
		global $db_user;
		global $db_pass;
		global $db_name;

		$link = @mysqli_connect($db_host, $db_user, $db_pass);
		if (!$link)
			return mysqli_connect_error();

		if (!mysqli_select_db($link, $alt_db ?: $db_name))
			return mysqli_error($link);

		return $link;
	}

	/**
	 * Make sure that we have a connection to the database.  Call
	 * this before any _direct_ calls to mysqli_query.
	 *
	 * $check constants:
	 * NONE - no special handling.
	 * PING - use _ping and if that fails, reconnect.  Should
	 */
	define('DB_CHECK_NONE', 0);
	define('DB_CHECK_PING', 1);
	define('DB_CHECK_DISCONNECT', 2);
	define('DB_CHECK_RECONNECT', 3);

	function check_db($may_fail = false, $check = DB_CHECK_NONE)
	{
		static $db_initialised = false;

		global $db_noinit;
		global $db_link;
		global $db_transaction_level;

		if ($check != DB_CHECK_NONE && $db_transaction_level != 0)
			do_die("Can only use check_db with check != DB_CHECK_NONE when transaction levels are 0.");
		if (in_array($check, [DB_CHECK_DISCONNECT, DB_CHECK_RECONNECT])) {
			if ($db_initialised) {
				mysqli_close($db_link);
				$db_initialised = false;
				$db_link = NULL;
			}
			if ($check == DB_CHECK_DISCONNECT)
				return true;
		}

		if (!$db_initialised) {
			mysqli_report(MYSQLI_REPORT_OFF);
			if (isset($db_noinit)) {
				if ($may_fail)
					return false;
				do_die("db_noinit is set and may_fail is false.");
			}

			$db_link = db_get_new_handle();
			if (!is_object($db_link)) {
			 	if ($may_fail)
					return false;
				else
					do_die("Error establishing MySQL connection: ".$db_link, 1);
			} elseif ($check == DB_CHECK_PING && !mysqli_ping($db_link)) {
				mysqli_close($db_link);
				$db_initialised = false;
				$db_link = NULL;
				return check_db($may_fail);
			}

			$db_initialised = true;
		}

		return true;
	}

	/**
	 * When you want to start a transaction, call db_transaction_up().  This
	 * will create a new transaction if one is not already in progress.
	 *
	 * Then call either db_transaction_down() OR db_transaction_fail() - down
	 * for success and fail for failure.  If you issue an _up you _MUST_ issue
	 * one of _down or _fail.
	 *
	 * Please note that a call to db_transaction_fail will ONLY rollback the changes
	 * to the last call of db_transaction_up().  A call to db_transaction_down() will
	 * only commit once all savepoints have been downed.  Note that a sequence
	 * like this is perfectly legal:
	 *
	 * db_transaction_up();
	 *  (a)
	 * db_transaction_up();
	 *  (b)
	 * db_transaction_fail();
	 *  (c)
	 * db_transaction_down();
	 *
	 * Other SQL at (a) and (c) will be COMMITed, whilst statements at (b) will
	 * be discarded.
	 */
	$db_transaction_level = 0;

	function db_transaction_up()
	{
		global $db_transaction_level;

		if ($db_transaction_level) {
			query_execute("SAVEPOINT savepoint".$db_transaction_level);
		} else {
			query_execute("START TRANSACTION");
		}
		++$db_transaction_level;
	}

	function db_transaction_down()
	{
		global $db_transaction_level;

		if (!--$db_transaction_level) {
			query_execute("COMMIT");
		} else {
			query_execute("RELEASE SAVEPOINT savepoint".$db_transaction_level);
		}
	}

	function db_transaction_fail()
	{
		global $db_transaction_level;

		if (!--$db_transaction_level) {
			query_execute("ROLLBACK");
		} else {
			query_execute("ROLLBACK TO savepoint".$db_transaction_level);
		}
	}

	// For use by do_die to rollback the transaction all the way.
	// This function should not be USED ANYWHERE ELSE UNLESS YOU REALLY
	// REALLY know what you're doing.
	function db_transaction_release_all()
	{
		global $db_transaction_level;
		global $db_in_query;

		if ($db_in_query) {
			while (query_next_row($db_in_query));
			mysqli_free_result($db_in_query);
			$db_in_query = false;
		}

		while ($db_transaction_level > 0)
			db_transaction_fail();
	}

	function db_debug_on()
	{
		global $db_debug;
		$db_debug++;
	}

	function db_debug_off()
	{
		global $db_debug;
		if ($db_debug)
			$db_debug--;
	}

	/**
	 * The cursor functions.  These are basically just the DB wrappers.  Generally you
	 * shouldn't use these directly.  If you are passing _huge_ (>32MB) data sets back
	 * to the client then this is what you want to use, otherwise look at one of the other
	 * wrappers which is supposed to make dealing with data sets easier.
	 *
	 * NOTE:  ignore_errors parameter used to be $die_on_error, so we need to explicitly
	 * check for a non-array value that implies true and then remap that to an empty array.
	 */
	$db_in_query = false;

	function query_getcursor($query, $ignored_errors = array(), $depth = 1)
	{
		global $db_link;
		global $db_in_query;
		global $db_debug;

		if ($db_in_query)
			do_die("SQL status out of sync (currently in query, trying to execute another).");

		if (!check_db($ignored_errors === false))
			return false;
		if ($db_debug) {
			error_log("QUERY: $query");
			$timer = timer_start("QUERY");
		}
		$res = mysqli_query($db_link, $query, MYSQLI_USE_RESULT);
		if ($db_debug) {
			timer_tag($timer, "Execution Time");
		}
		if (!$res) {
			if (!is_array($ignored_errors)) {
				if ($ignored_errors /* die_on_error */)
					do_die("MySQL Error ".mysqli_errno($db_link).": ".mysqli_error($db_link), $depth);
			} else {
				if (in_array(mysqli_errno($db_link), $ignored_errors))
					return FALSE;
				do_die("MySQL Error ".mysqli_errno($db_link).": ".mysqli_error($db_link), $depth);
			}
		}

		if (!is_bool($res))
			$db_in_query = $res;
		return $res;
	}

	function query_next_check($r)
	{
		global $db_in_query;
		if (!$db_in_query)
			do_die("Fetching rows from MySQL whilst not processing a query.");
		if (!$r) {
			mysqli_free_result($db_in_query);
			$db_in_query = false;
		}
		return $r;
	}

	function query_next($r)
	{
		return query_next_check(mysqli_fetch_array($r));
	}

	function query_next_assoc($r)
	{
		return query_next_check(mysqli_fetch_assoc($r));
	}

	function query_next_row($r)
	{
		return query_next_check(mysqli_fetch_row($r));
	}

	function escape_sql($s, $die_on_error = true) { return sql_escape($s, $die_on_error); }
	function sql_escape($s, $die_on_error = true)
	{
		global $db_link;

		/* if die_on_error is false, and exactly false, then this may fail */
		if (!check_db($die_on_error === false))
			return null;

		return mysqli_real_escape_string($db_link, $s);

	}

	function sql_string($s)
	{
		return "'".sql_escape($s)."'";
	}

	function sql_string_null($s, $nullonlyifnotset = false)
	{
		if ($nullonlyifnotset ? isset($s) : !empty($s))
			return "'".sql_escape($s)."'";
		else
			return "NULL";
	}

	function sql_array_string($a)
	{
		if (is_array($a)) {
			return array_map("sql_string", $a);
		} else
			return NULL;
	}

	function sql_array_string_null($a, $nullonlyifnotset = false)
	{
		if (is_array($a)) {
			$escape = function($key) use($nullonlyifnotset) {
				return sql_string_null($key, $nullonlyifnotset);
			};
			return array_map($escape, $a);
		} else
			return NULL;
	}

	function sql_array_string_dropnull($a)
	{
		$a = array_filter($a);
		return sql_array_string($a);
	}

	/**
	 * Instead of calling mysqli_query() direct.  This will allow us to easily (well,
	 * easier at least) switch to an entirely different DB engine should the need arise,
	 * or potentially make the DB layer plugable.
	 *
	 * This really is just a call to query_getcursor(), with a double negation to turn the
	 * resource identifier into a boolean value.
	 */
	function query_execute($query, $die_on_error = true)
	{
		$r = query_getcursor($query, $die_on_error, 2);
		if (!is_bool($r))
			while(query_next_row($r));
		return !!$r;
	}

	function query_get_insert_id()
	{
		global $db_link;
		return mysqli_insert_id($db_link);
	}

	function query_get_error()
	{
		global $db_link;

		return mysqli_error($db_link);
	}

	function query_get_errno()
	{
		global $db_link;

		return mysqli_errno($db_link);
	}

	/**
	 * Check for the number of affected rows.
	 */
	function query_affected()
	{
		global $db_link;

		return mysqli_affected_rows($db_link);
	}

	/**
	 * The bread and butter of the database interface.  Unless you're expecting
	 * really huge data sets this is probably what you want.  If you have big data
	 * sets, use the query_getcursor() and query_next() functions directly
	 * (loses in-memory data structures but generally when we have such big data
	 * sets we are not doing any processing in-memory).
	 */
	function query_getset($query, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if (!$res)
			return FALSE;

		$result = array();
		while($row = query_next_assoc($res))
			$result[] = $row;

		return $result;
	}

	/**
	 * From here on really aught to get a bit of a re-working.
	 */
	function query_getmultilevelsplitset($query, $split_fields, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_assoc($res)) {
			$ap_array =& $result;
			foreach($split_fields as $l) {
				if (!isset($ap_array[$row[$l]]))
					$ap_array[$row[$l]] = array();
				$ap_array =& $ap_array[$row[$l]];
			}
			$ap_array[] = $row;
		}

		return $result;
	}

	function query_getsplitset($query, $split_field, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_assoc($res))
			$result[$row[$split_field]][] = $row;

		return $result;
	}

	function query_getkeyedset($query, $key_field, $die_on_error = true)
	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_assoc($res))
			$result[$row[$key_field]] = $row;

		return $result;
	}

	function query_getrow($query, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$row = query_next_assoc($res);
		if ($row)
			while (query_next_assoc($res)); /* Need to fetch all results */

		return $row;
	}

	function query_getarray($query, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_row($res))
			$result[] = $row[0];
		return $result;
	}

	function query_getkeyedarray($query, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_row($res))
			$result[$row[0]] = $row[1];
		return $result;
	}

	function query_getkeyedmultiarray($query, $die_on_error = true)
   	{
		$res = query_getcursor($query, $die_on_error, 2);
		if(!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_row($res))
			$result[$row[0]][] = $row[1];
		return $result;
	}

	function query_getlayeredarray($query, $die_on_error = true)
	{
		$res = query_getcursor($query, $die_on_error, 2);
		if (!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_row($res)) {
			$ap_array =& $result;
			$maxelem = count($row) - 1;
			for ($i = 0; $i < $maxelem - 1; ++$i) {
				if (!isset($ap_array[$row[$i]]))
					$ap_array[$row[$i]] = array();
				$ap_array =& $ap_array[$row[$i]];
			}
			$ap_array[$row[$maxelem - 1]] = $row[$maxelem];
		}

		return $result;
	}

	function query_getlayeredmultiarray($query, $die_on_error = true)
	{
		$res = query_getcursor($query, $die_on_error, 2);
		if (!$res)
			return FALSE;

		$result = array();
		while ($row = query_next_row($res)) {
			$ap_array =& $result;
			$maxelem = count($row) - 1;
			for ($i = 0; $i < $maxelem - 1; ++$i) {
				if (!isset($ap_array[$row[$i]]))
					$ap_array[$row[$i]] = array();
				$ap_array =& $ap_array[$row[$i]];
			}
			$ap_array[$row[$maxelem - 1]][] = $row[$maxelem];
		}

		return $result;
	}

	function query_gettree($query, $grouping, $next, $base, $label, $value = NULL, $withstop = "..here..", $die_on_error = true)
	{
		$dataset = query_getsplitset($query, $grouping, $die_on_error);
		return maketree($dataset, $grouping, $next, $base, $label, $value, $withstop);
	}

	function dataset_make_layered(&$dataset, $grouping, $next, $base, $children = "children")
	{
		if (!isset($dataset[$base]))
			return array();
		foreach ($dataset[$base] as $child) {
			$rt[$child[$next]] = $child;
			$rt[$child[$next]][$children] = dataset_make_layered($dataset, $grouping, $next, $child[$next], $children);
		}
		return $rt;
	}

	function query_getlayered($query, $grouping, $next, $base, $children = "children", $die_on_error = true)
	{
		$dset = query_getsplitset($query, $grouping, $die_on_error);
		return dataset_make_layered($dset, $grouping, $next, $base, $children);
	}

	function db_columns($table)
	{
		static $cache = [];
		if (!isset($cache[$table]))
			$cache[$table] = query_getkeyedset("EXPLAIN ".$table, "Field");
		return $cache[$table];
	}

	function db_set_values($table, $field)
	{
		$r = db_columns($table);
		if (!isset($r[$field]))
			return [];
		if (!preg_match("/^[Ss][Ee][Tt]\((.*)\)$/", $r[$field]['Type'], $p))
			return [];
		eval("\$opts = [".$p[1]."];");
		return $opts;
	}

	function db_enum_values($table, $field)
	{
		$r = db_columns($table);
		if (!isset($r[$field]))
			return [];
		if (!preg_match("/^[Ee][Nn][Uu][Mm]\((.*)\)$/", $r[$field]['Type'], $p))
			return [];
		eval("\$opts = [".$p[1]."];");
		return $opts;
	}

	function db_enum_is_at_least($table, $field, $minval, $value)
	{
		$opts = db_enum_values($table, $field);
		$r = false;
		foreach ($opts as $c) {
			if ($c == $minval)
				$r = true;
			if ($c == $value)
				return $r;
		}
		error_log("Invocation of ".__FUNCTION__."($table/$field, minval=$minval, $value=$value) resulting in value not matching any fields".($r ? "" : ", nor minval"));
		return false;
	}

	function db_enum_is_at_most(string $table, string $field, string $maxval, string $value) : bool
	{
		$opts = db_enum_values($table, $field);
		foreach ($opts as $c) {
			if ($c == $value)
				return true;
			if ($c == $maxval)
				return false;
		}
		error_log("Invocation of ".__FUNCTION__."($table/$field, maxval=$maxval, $value=$value) resulting in value not matching any fields".($r ? "" : ", nor maxval"));
		return false;
	}

	function db_column_type($table, $field)
	{
		$r = db_columns($table);
		if (!isset($r[$field]))
			return NULL;
		return $r[$field]['Type'];
	}

	function db_find_foreign_keys_to(string $table, string $column, ?string $schema = NULL) : array
	{
		if ($schema)
			$target = sql_string("$schema/$table");
		else
			$target = "CONCAT(DATABASE(), ".sql_string("/$table").")";

		$sources = query_getset("SELECT ID, FOR_NAME FROM information_schema.INNODB_SYS_FOREIGN NATURAL JOIN information_schema.INNODB_SYS_FOREIGN_COLS WHERE REF_NAME=".$target." AND REF_COL_NAME=".sql_string($column));

		if (count($sources) == 0)
			return [];

		$res = [];
		foreach ($sources as $s) {
			preg_match(":^([^/]+)/(.*)$:", $s['FOR_NAME'], $p) or do_die("Unable to split ".$s['FOR_NAME']." into schema/table.");
			$res[$s['ID']] = [
				"schema" => $p[1],
				"table" => $p[2],
				"sourcecols" => [],
				"destcols" => [],
			];
		}

		$refs = query_getcursor("SELECT ID, POS, FOR_COL_NAME, REF_COL_NAME FROM information_schema.INNODB_SYS_FOREIGN_COLS WHERE ID IN(".
			implode(", ", sql_array_string(array_map(function($e) { return $e['ID']; }, $sources))).")");

		while ($ref = query_next_assoc($refs)) {
			if (!isset($res[$ref['ID']])
				|| isset($res[$ref['ID']]['sourcecols'][$ref['POS']])
				|| isset($res[$ref['ID']]['destcols'][$ref['POS']]))
				do_die("BUG, logic error");
			$res[$ref['ID']]['sourcecols'][$ref['POS']] = $ref['FOR_COL_NAME'];
			$res[$ref['ID']]['destcols'][$ref['POS']] = $ref['REF_COL_NAME'];
		}

		return $res;
	}

	function db_check_replication_status($max_delay = 15 /* seconds */)
	{
		$slave = query_getset("show all slaves status", [1227 /* access denied */]);
		if (!is_array($slave)) {
			error_log(__FUNCTION__.": no access, this is assuming we're up to date now.");
			return true;
		}

		foreach ($slave as $s) {
			if ($s['Slave_SQL_Running'] != "Yes") {
				error_log(__FUNCTION__.": replication".($s['Connection_name'] ? " from ".$s['Connection_name'] : "")." not ok: SQL not running.");
				return false;
			}

			if ($s['Seconds_Behind_Master'] > $max_delay) {
				error_log(__FUNCTION__.": replication".($s['Connection_name'] ? " from ".$s['Connection_name'] : "")." not ok: SQL more than $max_delay seconds behind (".$s['Seconds_Behind_Master']." seconds total).");
				return false;
			}
		}

		return true;
	}

	function valid_datetime($s, $accuracy = 0)
	{
		$reg = '/^([1-9][0-9]{0,3}-(0?[1-9]|1[0-2])-([0-2]?[0-9]|3[01]) ([01]?[0-9]|2[0-3]):[0-5]?[0-9]:[0-5]?[0-9])';
		if ($accuracy > 0)
			$reg .= "([.]([0-9]{1,".(int)$accuracy."})[0-9]*)?";
		$reg .= '$/';

		if (!preg_match($reg, $s, $p))
			return false;

		if ($accuracy > 0) {
			$t = @$p[6] ?: "";
			while (strlen($t) < $accuracy)
				$t .= "0";
			$s = $p[1].".".$t;
		}

		return $s;
	}

	function db_get_lock($name, $timeout = 0)
	{
		list($res) = query_getrow("SELECT GET_LOCK(".sql_string($name).", ".(int)$timeout.") `0`");
		return $res != '0';
	}

	function db_release_lock($name)
	{
		query_execute("SELECT RELEASE_LOCK(".sql_string($name).")");
	}
?>