<?php

namespace AdminNeo;

class Admin extends Origin
{
	private $values = [];

	public function getOperators(): array
	{
		return ["<=", ">="];
	}

	public function getLikeOperator(): ?string
	{
		return null;
	}

	public function getRegexpOperator(): ?string
	{
		return null;
	}

	public function getServiceTitle(): string
	{
		return "<a href='" . h(HOME_URL) . "'><svg role='img' class='logo' width='130' height='28'><desc>EditorNeo</desc><use href='" . link_files("logo.svg", ["images/logo.svg"]) . "#logo'/></svg></a>";
	}

	public function getDatabase(): ?string
	{
		if (!Connection::get()) {
			return null;
		}

		// Returns the first available database.
		$databases = $this->admin->getDatabases(false);
		$systemDatabases = Driver::get()->getSystemDatabases();

		foreach ($databases as $database) {
			if (!in_array($database, $systemDatabases)) {
				return $database;
			}
		}

		// Return username if no database is available.
		return Connection::get()->getValue("SELECT SUBSTRING_INDEX(CURRENT_USER, '@', 1)");
	}

	public function getQueryTimeout(): int
	{
		return 5;
	}

	public function printToHead(): void
	{
		//
	}

	public function printLoginForm(): void
	{
		$driver = $this->config->getDefaultDriver(Drivers::getList());
		$server = $this->config->getDefaultServer();

		echo "<table class='box box-light'>\n";
		echo $this->admin->getLoginFormRow('driver', '', '<input type="hidden" name="auth[driver]" value="' . h($driver) . '">');
		echo $this->admin->getLoginFormRow('server', '', '<input type="hidden" name="auth[server]" value="' . h($server) . '">');
		echo $this->admin->getLoginFormRow('username', lang('Username'), '<input class="input" name="auth[username]" id="username" value="' . h($_GET["username"]) . '" autocomplete="username" autocapitalize="off">');
		echo $this->admin->getLoginFormRow('password', lang('Password'), '<input type="password" class="input" name="auth[password]" autocomplete="current-password">');
		echo "</table>\n";

		echo "<p>";
		echo "<input type='submit' class='button default' value='" . lang('Login') . "'>";
		echo checkbox("auth[permanent]", 1, $_COOKIE["neo_permanent"], lang('Permanent login'));
		echo "</p>\n";
	}

	public function getFieldName(array $field, int $order = 0): string
	{
		return h($field["field"]);
	}

	public function printTableMenu(array $tableStatus, ?string $set = ""): void
	{
		if ($set !== null) {
			$table = $tableStatus["Name"];
			echo '<p class="links top-links"><a href="', h(ME . 'edit=' . urlencode($table) . $set), '">', icon("item-add"), lang('New item'), "</a>\n";
		}
	}

	public function getBackwardKeys(string $table, string $tableName): array
	{
		$keys = [];

		foreach (get_rows("SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = " . q($this->admin->getDatabase()) . "
AND REFERENCED_TABLE_SCHEMA = " . q($this->admin->getDatabase()) . "
AND REFERENCED_TABLE_NAME = " . q($table) . "
ORDER BY ORDINAL_POSITION", null, "") as $row) { //! requires MySQL 5
			$keys[$row["TABLE_NAME"]]["keys"][$row["CONSTRAINT_NAME"]][$row["COLUMN_NAME"]] = $row["REFERENCED_COLUMN_NAME"];
		}

		foreach ($keys as $key => $val) {
			$name = $this->admin->getTableName(table_status($key, true));
			if ($name != "") {
				$search = preg_quote($tableName);
				$separator = "(:|\\s*-)?\\s+";
				$keys[$key]["name"] = (preg_match("(^$search$separator(.+)|^(.+?)$separator$search\$)iu", $name, $match) ? $match[2] . $match[3] : $name);
			} else {
				unset($keys[$key]);
			}
		}

		return $keys;
	}

	public function printBackwardKeys(array $backwardKeys, array $row): void
	{
		foreach ($backwardKeys as $table => $backwardKey) {
			foreach ($backwardKey["keys"] as $cols) {
				$link = ME . 'select=' . urlencode($table);
				$i = 0;
				foreach ($cols as $column => $val) {
					$link .= where_link($i++, $column, $row[$val]);
				}
				echo "<a href='" . h($link) . "'>" . h($backwardKey["name"]) . "</a>";
				$link = ME . 'edit=' . urlencode($table);
				foreach ($cols as $column => $val) {
					$link .= "&set" . urlencode("[" . bracket_escape($column) . "]") . "=" . urlencode($row[$val]);
				}
				echo "<a href='" . h($link) . "' title='" . lang('New item') . "'>", icon_solo("add"), "</a> ";
			}
		}
	}

	public function formatSelectQuery(string $query, float $start, bool $failed = false): string
	{
		return "<!--\n" . str_replace("--", "--><!-- ", $query) . "\n(" . format_time($start) . ")\n-->\n";
	}

	public function formatMessageQuery(string $query, string $time, bool $failed = false): string
	{
		return " <span class='time'>" . @date("H:i:s") . "</span><!--\n" . str_replace("--", "--><!-- ", $query) . "\n" . ($time ? "($time)\n" : "") . "-->";
	}

	public function formatSqlCommandQuery(string $query): string
	{
		return "";
	}

	public function getTableDescriptionFieldName(string $table): string
	{
		// first varchar column
		foreach (fields($table) as $field) {
			if (preg_match("~varchar|character varying~", $field["type"])) {
				return idf_escape($field["field"]);
			}
		}

		return "";
	}

	public function fillForeignDescriptions(array $rows, array $foreignKeys): array
	{
		$return = $rows;
		foreach ($rows[0] as $key => $val) {
			if (list($table, $id, $name) = $this->admin->getForeignColumnInfo($foreignKeys, $key)) {
				// find all used ids
				$ids = [];
				foreach ($rows as $row) {
					$ids[$row[$key]] = q($row[$key]);
				}
				// uses constant number of queries to get the descriptions, join would be complex, multiple queries would be slow
				$descriptions = $this->values[$table];
				if (!$descriptions) {
					$descriptions = get_key_vals("SELECT $id, $name FROM " . table($table) . " WHERE $id IN (" . implode(", ", $ids) . ")");
				}
				// use the descriptions
				foreach ($rows as $n => $row) {
					if (isset($row[$key])) {
						$return[$n][$key] = (string)$descriptions[$row[$key]];
					}
				}
			}
		}

		return $return;
	}

	public function formatSelectionValue(?string $val, ?string $link, ?array $field, ?string $original): string
	{
		$link = h($link);

		if ($val === null) {
			$text = "";
		} elseif (!$field) {
			$text = $val;
		} elseif (preg_match('~blob|bytea~', $field["type"]) && !is_utf8($val)) {
			$text = lang('%d byte(s)', strlen($original));
			if (preg_match("~^(GIF|\xFF\xD8\xFF|\x89PNG\x0D\x0A\x1A\x0A)~", $original)) { // GIF|JPG|PNG, getimagetype() works with filename
				$text = "<img src='$link' alt='$text'>";
			}
		} elseif ($this->looksLikeBool($field)) { // bool
			$text = (preg_match('~^(1|t|true|y|yes|on)$~i', $val) ? lang('yes') : lang('no'));
		} else {
			$text = $val;
		}

		if ($link) {
			$text = "<a href='$link'" . (is_web_url($link) ? target_blank() : "") . ">$text</a>";
		}

		// Firefox doesn't support <colgroup>
		if (preg_match('~date~', $field["type"])) {
			$text = "<div class='datetime'>$text</div>";
		}

		return $text;
	}

	public function formatFieldValue($value, array $field): ?string
	{
		if (preg_match('~date|timestamp~', $field["type"]) && $value !== null) {
			return preg_replace('~^(\d{2}(\d+))-(0?(\d+))-(0?(\d+))~', lang('$1-$3-$5'), $value);
		}

		return $value;
	}

	public function printTableStructure(array $fields): void
	{
		//
	}

	public function printTablePartitions(array $partitionInfo): void
	{
		//
	}

	public function printTableIndexes(array $indexes): void
	{
		//
	}

	public function printSelectionColumns(array $select, array $columns): void
	{
		//
	}

	public function printSelectionSearch(array $where, array $columns, array $indexes): void
	{
		$where = (array)$_GET["where"];
		echo '<fieldset id="fieldset-search"><legend>' . lang('Search') . "</legend><div class='fieldset-content'>\n";
		$keys = [];
		foreach ($where as $key => $val) {
			$keys[$val["col"]] = $key;
		}
		$i = 0;
		$fields = fields($_GET["select"]);
		foreach ($columns as $name => $desc) {
			$field = $fields[$name];
			if (preg_match("~enum~", $field["type"]) || $this->looksLikeBool($field)) { //! set - uses 1 << $i and FIND_IN_SET()
				$key = $keys[$name];
				$i--;
				echo "<div>" . h($desc) . "<input type='hidden' name='where[$i][col]' value='" . h($name) . "'>:";

				if ($this->looksLikeBool($field)) {
					echo " <select name='where[$i][val]'>" . optionlist(["" => "", lang('no'), lang('yes')], $where[$key]["val"] ?? null, true) . "</select>";
				} else {
					echo " ", enum_input("name='where[$i][val][]'", $field, (array)($where[$key]["val"] ?? []), ($field["null"] ? 0 : null), true);
				}

				echo "</div>\n";
				unset($columns[$name]);
			} elseif (is_array($options = $this->foreignKeyOptions($_GET["select"], $name))) {
				if ($fields[$name]["null"]) {
					$options[0] = '(' . lang('empty') . ')';
				}
				$key = $keys[$name];
				$i--;
				echo "<div>" . h($desc) . "<input type='hidden' name='where[$i][col]' value='" . h($name) . "'><input type='hidden' name='where[$i][op]' value='='>: <select name='where[$i][val]'>" . optionlist($options, $where[$key]["val"] ?? null, true) . "</select></div>\n";
				unset($columns[$name]);
			}
		}
		$i = 0;
		foreach ($where as $val) {
			if (($val["col"] == "" || $columns[$val["col"]]) && "$val[col]$val[val]" != "") {
				echo "<div><select name='where[$i][col]'><option value=''>(" . lang('anywhere') . ")" . optionlist($columns, $val["col"], true) . "</select>";
				echo html_select("where[$i][op]", [-1 => ""] + $this->admin->getOperators(), $val["op"]);
				echo "<input type='search' class='input' name='where[$i][val]' value='" . h($val["val"]) . "'>" . script("mixin(qsl('input'), {onkeydown: selectSearchKeydown, onsearch: selectSearchSearch});", "");
				echo " <button class='button light remove jsonly' title='" . h(lang('Remove')) . "'>", icon_solo("remove"), "</button>";
				echo script('qsl("#fieldset-search .remove").onclick = selectRemoveRow;', "");
				echo "</div>\n";
				$i++;
			}
		}
		echo "<div><select name='where[$i][col]'><option value=''>(" . lang('anywhere') . ")" . optionlist($columns, null, true) . "</select>";
		echo script("qsl('select').onchange = selectAddRow;", "");
		echo html_select("where[$i][op]", [-1 => ""] + $this->admin->getOperators());
		echo "<input type='search' class='input' name='where[$i][val]'>";
		echo script("mixin(qsl('input'), {onchange: function () { this.parentNode.firstChild.onchange(); }, onsearch: selectSearchSearch});");
		echo " <button class='button light remove jsonly' title='" . h(lang('Remove')) . "'>", icon_solo("remove"), "</button>";
		echo script('qsl("#fieldset-search .remove").onclick = selectRemoveRow;', "");
		echo "</div>";
		echo "</div></fieldset>\n";
	}

	public function printSelectionOrder(array $order, array $columns, array $indexes): void
	{
		//! desc
		$orders = [];
		foreach ($indexes as $key => $index) {
			$order = [];
			foreach ($index["columns"] as $val) {
				$order[] = $columns[$val];
			}
			if (count(array_filter($order, 'strlen')) > 1 && $key != "PRIMARY") {
				$orders[$key] = implode(", ", $order);
			}
		}
		if ($orders) {
			echo '<fieldset><legend>' . lang('Sort') . "</legend><div class='fieldset-content'>";
			echo "<select name='index_order'>" . optionlist(["" => ""] + $orders, (($_GET["order"][0] ?? null) != "" ? "" : $_GET["index_order"]), true) . "</select>";
			echo "</div></fieldset>\n";
		}
		if ($_GET["order"]) {
			echo "<div style='display: none;'>" . hidden_fields([
				"order" => [1 => reset($_GET["order"])],
				"desc" => ($_GET["desc"] ? [1 => 1] : []),
			]) . "</div>\n";
		}
	}

	public function printSelectionLimit(?int $limit): void
	{
		echo "<fieldset><legend>" . lang('Limit') . "</legend><div class='fieldset-content'>";
		echo html_select("limit", ["", "20", "30", "50", "70", "100"], (string)$limit);
		echo "</div></fieldset>\n";
	}

	public function printSelectionLength(?string $textLength): void
	{
		//
	}

	public function printSelectionAction(array $indexes): void
	{
		echo "<fieldset><legend>" . lang('Action') . "</legend><div class='fieldset-content'>";
		echo "<input type='submit' class='button' value='" . lang('Select') . "'>";
		echo "</div></fieldset>\n";
	}

	public function processSelectionColumns(array $columns, array $indexes): array
	{
		return [[], []];
	}

	public function processSelectionSearch(array $fields, array $indexes): array
	{
		$return = [];

		foreach ((array)$_GET["where"] as $key => $where) {
			$col = $where["col"];
			$op = $where["op"];
			$val = $where["val"];

			if (($key >= 0 && $col != "") || $val != "") {
				$conds = [];

				foreach (($col != "" ? [$col => $fields[$col]] : $fields) as $name => $field) {
					if ($col != "" || is_numeric($val) || !preg_match(number_type(), $field["type"])) {
						$name = idf_escape($name);

						if ($col != "" && $field["type"] == "enum") {
							$conds[] = (in_array(0, $val) ? "$name IS NULL OR " : "") . "$name IN (" . implode(", ", array_map('AdminNeo\q', $val)) . ")";
						} else {
							$text_type = preg_match('~char|text|enum|set~', $field["type"]);
							$value = $this->admin->processFieldInput($field, (!$op && $text_type && preg_match('~^[^%]+$~', $val) ? "%$val%" : $val));

							$conds[] = Driver::get()->convertSearch($name, $where, $field) . ($value == "NULL" ? " IS" . ($op == ">=" ? " NOT" : "") . " $value"
								: (in_array($op, $this->admin->getOperators()) || $op == "=" ? " $op $value"
								: ($text_type ? " LIKE $value"
								: " IN (" . str_replace(",", "', '", $value) . ")"
							)));

							if ($key < 0 && $val == "0") {
								$conds[] = "$name IS NULL";
							}
						}
					}
				}

				$return[] = ($conds ? "(" . implode(" OR ", $conds) . ")" : "1 = 0");
			}
		}

		return $return;
	}

	public function processSelectionOrder(array $fields, array $indexes): array
	{
		$index_order = $_GET["index_order"];
		if ($index_order != "") {
			unset($_GET["order"][1]);
		}

		if ($_GET["order"]) {
			return [idf_escape(reset($_GET["order"])) . ($_GET["desc"] ? " DESC" : "")];
		}

		foreach (($index_order != "" ? [$indexes[$index_order]] : $indexes) as $index) {
			if ($index_order != "" || $index["type"] == "INDEX") {
				$has_desc = array_filter($index["descs"]);
				$desc = false;
				foreach ($index["columns"] as $val) {
					if (preg_match('~date|timestamp~', $fields[$val]["type"])) {
						$desc = true;
						break;
					}
				}
				$return = [];
				foreach ($index["columns"] as $key => $val) {
					$return[] = idf_escape($val) . (($has_desc ? $index["descs"][$key] : $desc) ? " DESC" : "");
				}

				return $return;
			}
		}

		return [];
	}

	public function processSelectionLength(): string
	{
		return "100";
	}

	public function getFieldFunctions(array $field): array
	{
		$return = [];
		if ($field["null"] && preg_match('~blob~', $field["type"])) {
			$return["NULL"] = lang('empty');
		}

		$return[""] = ($field["null"] || $field["auto_increment"] || $this->looksLikeBool($field) ? "" : "*");

		//! respect driver
		if (preg_match('~date|time~', $field["type"])) {
			$return["now"] = lang('now');
		}
		if (preg_match('~_(md5|sha1)$~i', $field["field"], $match)) {
			$return[] = strtolower($match[1]);
		}

		return $return;
	}

	public function getFieldInput(?string $table, array $field, string $attrs, $value, ?string $function): string
	{
		if ($field["type"] == "enum") {
			if (!$value && !isset($_GET["select"])) {
				$value = "";
			}

			return enum_input($attrs, $field, $value, $field["null"] ? "" : null);
		}

		$options = $this->foreignKeyOptions($table, $field["field"], $value);
		if ($options !== null) {
			if (is_array($options)) {
				$result = "<select $attrs>" . optionlist($options, $value, true) . "</select>";
			} else {
				$result = "<input $attrs value='" . h($value) . "' class='input hidden'>"
					. "<input value='" . h($options) . "' class='input jsonly'>"
					. "<div></div>"
					. script("
						qsl('input').oninput = partial(whisper, '" . ME . "script=complete&source=" . urlencode($table) . "&field=" . urlencode($field["field"]) . "&value=');
						qsl('div').onclick = whisperClick;
					", "");
			}

			return $result;
		}

		if ($this->looksLikeBool($field)) {
			$checked = preg_match('~^(1|t|true|y|yes|on)$~i', $value) ? "checked" : "";

			return "<input type='checkbox' $attrs value='1' $checked>";
		}

		return "";
	}

	public function getFieldInputHint(?string $table, array $field, ?string $value): string
	{
		$hint = parent::getFieldInputHint($table, $field, $value);

		$format = "";
		if (preg_match('~time~', $field["type"])) {
			$format = lang('HH:MM:SS');
		}
		if (preg_match('~date|timestamp~', $field["type"])) {
			$format = lang('YYYY-MM-DD') . ($format ? " [$format]" : "");
		}

		if ($format) {
			$hint .= ($hint != "" ? "<br>" : "") . $this->admin->formatComment($format);
		}

		return $hint;
	}

	public function processFieldInput(?array $field, string $value, string $function = ""): string
	{
		if (!$field) {
			return q($value);
		}

		if ($function == "now") {
			return "$function()";
		}

		$return = $value;
		if (preg_match('~date|timestamp~', $field["type"]) && preg_match('(^' . str_replace('\$1', '(?P<p1>\d*)', preg_replace('~(\\\\\\$([2-6]))~', '(?P<p\2>\d{1,2})', preg_quote(lang('$1-$3-$5')))) . '(.*))', $value, $match)) {
			$return = ($match["p1"] != "" ? $match["p1"] : ($match["p2"] != "" ? ($match["p2"] < 70 ? 20 : 19) . $match["p2"] : gmdate("Y"))) . "-$match[p3]$match[p4]-$match[p5]$match[p6]" . end($match);
		}

		$return = ($field["type"] == "bit" && preg_match('~^[0-9]+$~', $value) ? $return : q($return));

		if ($value == "" && $this->looksLikeBool($field)) {
			$return = "'0'";
		} elseif ($value == "" && ($field["null"] || !preg_match('~char|text~', $field["type"]))) {
			$return = "NULL";
		} elseif (preg_match('~^(md5|sha1)$~', $function)) {
			$return = "$function($return)";
		}

		return unconvert_field($field, $return);
	}

	public function getDumpOutputs(): array
	{
		return [];
	}

	public function getDumpFormats(): array
	{
		return ['csv' => 'CSV,', 'csv;' => 'CSV;', 'tsv' => 'TSV'];
	}

	public function sendDumpHeaders(string $identifier, bool $multiTable = false): string
	{
		header("Content-Type: text/csv; charset=utf-8");

		return "csv";
	}

	public function dumpTable(string $table, string $style, int $viewType = 0): void
	{
		echo "\xef\xbb\xbf"; // UTF-8 byte order mark
	}

	public function dumpData(string $table, string $style, string $query): void
	{
		$result = Connection::get()->query($query, 1); // 1 - MYSQLI_USE_RESULT
		if (!$result) {
			return;
		}

		while ($row = $result->fetchAssoc()) {
			if ($style == "table") {
				dump_csv(array_keys($row));
				$style = "INSERT";
			}

			dump_csv($row);
		}
	}

	public function getImportFilePath(): string
	{
		return "";
	}

	public function printDatabaseMenu(): void
	{
		//
	}

	public function printNavigation(?string $missing): void
	{
		parent::printNavigation($missing);

		if ($missing == "auth") {
			$first = true;
			foreach ((array)$_SESSION["pwds"] as $vendor => $servers) {
				foreach ($servers[""] as $username => $password) {
					if ($password !== null) {
						if ($first) {
							echo "<nav id='logins'><menu>";
							$first = false;
						}
						echo "<li><a href='" . h(auth_url($vendor, "", $username)) . "' class='primary'>" . ($username != "" ? h($username) : "<i>" . lang('empty') . "</i>") . "</a></li>\n";
					}
				}
			}

			if (!$first) {
				echo "</menu></nav>\n";
			}
		} else {
			$this->admin->printDatabaseSwitcher($missing);

			if ($missing != "db" && $missing != "ns") {
				$status = table_status('', true);

				if (!$status) {
					echo "<p class='message'>" . lang('No tables.') . "</p>\n";
				} else {
					$this->admin->printTablesFilter();
					$this->admin->printTableList($status);
				}
			}
		}
	}

	public function printDatabaseSwitcher(?string $missing): void
	{
		//
	}

	public function printTableList(array $tables): void
	{
		echo "<nav id='tables'><menu>";

		foreach ($tables as $status) {
			// Skip views and tables without a name.
			$name = $this->admin->getTableName($status);
			if ($name == "") {
				continue;
			}

			$active = $_GET["select"] == $status["Name"] || $_GET["edit"] == $status["Name"];
			$class = "primary" . (is_view($status) ? " view" : "");
			$selectUrl = h(ME) . 'select=' . urlencode($status["Name"]);

			echo "<li>";
			echo "<a href='$selectUrl'", bold($active, $class), " data-primary='true' title='$name'>$name</a>";
			echo "</li>\n";
		}

		echo "</menu></nav>\n";
	}

	public function getForeignColumnInfo(array $foreignKeys, string $column): ?array
	{
		foreach ((array)$foreignKeys[$column] as $foreignKey) {
			if (count($foreignKey["source"]) == 1) {
				$name = $this->admin->getTableDescriptionFieldName($foreignKey["table"]);
				if ($name != "") {
					$id = idf_escape($foreignKey["target"][0]);

					return [$foreignKey["table"], $id, $name];
				}
			}
		}

		return null;
	}

	private function foreignKeyOptions($table, $column, $value = null)
	{
		if (list($target, $id, $name) = $this->admin->getForeignColumnInfo(column_foreign_keys($table), $column)) {
			$return = &$this->values[$target];
			if ($return === null) {
				$table_status = table_status($target);
				$return = ($table_status["Rows"] > 1000 ? "" : ["" => ""] + get_key_vals("SELECT $id, $name FROM " . table($target) . " ORDER BY 2"));
			}
			if (!$return && $value !== null) {
				return Connection::get()->getValue("SELECT $name FROM " . table($target) . " WHERE $id = " . q($value));
			}

			return $return;
		}

		return null;
	}

	/**
	 * Checks whether the column looks like boolean.
	 *
	 * @param array $field Single field returned from fields().
	 */
	protected function looksLikeBool(array $field): bool
	{
		return preg_match("~bool|(tinyint|bit)\\(1\\)~", $field["full_type"]);
	}
}
