<?php

/**
 * SQLite-backed Database implementation for tests.
 *
 * Provides a lightweight, in-memory RDBMS without requiring MySQL, similar in spirit to DbUnit.
 * It implements the subset of the legacy Database API used by our tests and by the classes we test.
 */
class Database
{
	private static ?PDO $pdo = null;
	private static int $queryCount = 0;
	private static float $queryTime = 0.0;
	private static bool $initialized = false;

	public static function reset(): void
	{
		self::$pdo = null; // drop the connection; will be recreated lazily
		self::$queryCount = 0;
		self::$queryTime = 0.0;
		self::$initialized = false;
	}

	private static function connect(): PDO
	{
		if (self::$pdo instanceof PDO)
			return self::$pdo;
		$pdo = new PDO('sqlite::memory:');
		$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
		$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
		// Enable foreign keys if needed
		$pdo->exec('PRAGMA foreign_keys = ON');
		// Register lightweight MySQL-compatibility functions used in queries
		if (method_exists($pdo, 'sqliteCreateFunction')) {
			// UNIX_TIMESTAMP() -> current epoch seconds
			$pdo->sqliteCreateFunction('UNIX_TIMESTAMP', function (): int { return time(); }, 0);
		}
		self::$pdo = $pdo;
		self::initializeSchemaAndData();
		return $pdo;
	}

	public static function resetSchema(): void
	{
		// Recreate connection and schema
		self::reset();
		self::connect();
	}

	public static function reseed(): void
	{
		if (!self::$pdo)
			self::connect();
		self::seedData(self::$pdo);
	}

	public static function truncateAll(): void
	{
		$pdo = self::connect();
		$tables = ['location',
			'subnet',
			'machine',
			'user',
			'role',
			'role_x_user',
			'role_x_location',
			'role_x_permission',
			'clientlog',
			'mail_queue',
			'mail_config',
			'audit'];
		foreach ($tables as $t) {
			$pdo->exec("DELETE FROM $t");
		}
	}

	private static function initializeSchemaAndData(): void
	{
		if (self::$initialized)
			return;
		$pdo = self::$pdo;
		// Create tables
		$pdo->exec('CREATE TABLE location (locationid INTEGER PRIMARY KEY, parentlocationid INTEGER NOT NULL, locationname TEXT NOT NULL, openingtime TEXT NULL)');
  $pdo->exec('CREATE TABLE subnet (startaddr INTEGER NOT NULL, endaddr INTEGER NOT NULL, locationid INTEGER NOT NULL)');
		$pdo->exec("CREATE TABLE machine (machineuuid TEXT PRIMARY KEY, subnetlocationid INTEGER, locationid INTEGER, fixedlocationid INTEGER, hostname TEXT, clientip TEXT, state TEXT DEFAULT 'OFFLINE')");
		$pdo->exec('CREATE TABLE user (userid INTEGER PRIMARY KEY, login TEXT, passwd TEXT, fullname TEXT, lasteventid INTEGER, permissions INTEGER)');
		$pdo->exec('CREATE TABLE role (roleid INTEGER PRIMARY KEY, rolename TEXT, builtin INTEGER DEFAULT 0, roledescription TEXT)');
		$pdo->exec('CREATE TABLE role_x_user (userid INTEGER, roleid INTEGER)');
		$pdo->exec('CREATE TABLE role_x_location (roleid INTEGER, locationid INTEGER)');
		$pdo->exec('CREATE TABLE role_x_permission (roleid INTEGER, permissionid TEXT)');
		$pdo->exec('CREATE TABLE clientlog (logid INTEGER PRIMARY KEY, dateline INTEGER, logtypeid TEXT, clientip TEXT, machineuuid TEXT, description TEXT, extra TEXT)');
		$pdo->exec('CREATE TABLE mail_queue (mailid INTEGER PRIMARY KEY, configid INTEGER, rcpt TEXT, subject TEXT, body TEXT, dateline INTEGER, nexttry INTEGER DEFAULT 0)');
		$pdo->exec('CREATE TABLE mail_config (configid INTEGER PRIMARY KEY, host TEXT, port INTEGER, ssl TEXT, senderaddress TEXT, replyto TEXT, username TEXT, password TEXT)');
  $pdo->exec('CREATE TABLE audit (id INTEGER PRIMARY KEY, dateline INTEGER, userid INTEGER, ipaddr TEXT, module TEXT, action TEXT, data TEXT, response INTEGER)');
		// Rebootcontrol scheduler table
		$pdo->exec('CREATE TABLE reboot_scheduler (locationid INTEGER PRIMARY KEY, action TEXT, nextexecution INTEGER, options TEXT)');
		// Exams module tables
		$pdo->exec('CREATE TABLE exams (examid INTEGER PRIMARY KEY, lectureid TEXT NULL, autologin TEXT, starttime INTEGER, endtime INTEGER, description TEXT)');
		$pdo->exec('CREATE TABLE exams_x_location (examid INTEGER, locationid INTEGER NULL)');
		// Simulated sat.* schema as sat_ prefixed tables for SQLite
		$pdo->exec('CREATE TABLE sat_user (userid INTEGER PRIMARY KEY, firstname TEXT, lastname TEXT, email TEXT)');
		$pdo->exec('CREATE TABLE sat_lecture (lectureid TEXT PRIMARY KEY, ownerid INTEGER, displayname TEXT, starttime INTEGER, endtime INTEGER, isexam INTEGER, isenabled INTEGER, islocationprivate INTEGER DEFAULT 0)');
		$pdo->exec('CREATE TABLE sat_lecture_x_location (lectureid TEXT, locationid INTEGER)');
		self::seedData($pdo);
		self::$initialized = true;
	}

	private static function seedData(PDO $pdo): void
	{
		// Seed a small canonical dataset used by multiple tests
		$loc = $pdo->prepare('INSERT INTO location (locationid, parentlocationid, locationname) VALUES (?, ?, ?)');
		$rows = [
			[1, 0, 'Campus'],
			[2, 1, 'Building A'],
			[3, 1, 'Building B'],
			[4, 2, 'Room 101'],
			[5, 0, 'Offsite'],
		];
		foreach ($rows as $r) {
			$loc->execute($r);
		}
		// Seed opening times for locations: Campus (1) open all week 00:00-23:59
		$today = date('l');
		$now = time();
		$week = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'];
		$otAllDay = json_encode([
			['days' => $week, 'openingtime' => '00:00', 'closingtime' => '23:59']
		]);
		$pdo->prepare('UPDATE location SET openingtime = ? WHERE locationid = 1')->execute([$otAllDay]);
		// Force a predictable day/time check by crafting a narrow window around now
		// XXX: Will fail around midnight, since the shifted opening times will span
		// to the next/previous day, but be attached to the current weekday.
		// First, move opening time into future (location 5)
		$open = date('H:i', $now + 3600);    // opens in 1h
		$close = date('H:i', $now + 7200);    // closes in 2h
		$json = json_encode([[ 'days' => [$today], 'openingtime' => $open, 'closingtime' => $close ]]);
		Database::exec('UPDATE location SET openingtime = :ot WHERE locationid = :id', ['ot' => $json, 'id' => 5]);
		// Now, move opening time into past (location 4)
		$open = date('H:i', $now - 7200);    // opened 2h ago
		$close = date('H:i', $now - 3600);    // closed 1h ago
		$json = json_encode([[ 'days' => [$today], 'openingtime' => $open, 'closingtime' => $close ]]);
		Database::exec('UPDATE location SET openingtime = :ot WHERE locationid = :id', ['ot' => $json, 'id' => 4]);
		// Subnets
		$sub = $pdo->prepare('INSERT INTO subnet (startaddr, endaddr, locationid) VALUES (?, ?, ?)');
		$sub->execute([sprintf('%u', ip2long('10.0.0.0')), sprintf('%u', ip2long('10.0.0.255')), 1]);
		$sub->execute([sprintf('%u', ip2long('10.0.0.128')), sprintf('%u', ip2long('10.0.0.191')), 2]);
		$sub->execute([sprintf('%u', ip2long('192.168.1.0')), sprintf('%u', ip2long('192.168.1.255')), 5]);
		// Users and roles minimal
		$pdo->exec("INSERT INTO user (userid, login, passwd, fullname, permissions) VALUES (1,'alice','STORED','Alice Doe',0), (2,'bob','STORED','Bob',0)");
		$pdo->exec("INSERT INTO role (roleid, rolename, builtin, roledescription) VALUES (1,'Admin',1,''),(2,'Tech',0,'')");
		$pdo->exec("INSERT INTO role_x_user (userid, roleid) VALUES (1,1)");
		$pdo->exec('INSERT INTO role_x_user (userid, roleid) VALUES (1,2)');
		$pdo->exec("INSERT INTO role_x_permission (roleid, permissionid) VALUES (1,'permissionmanager.*'),(2,'permissionmanager.users.view')");
		// Map Admin role (1) globally (NULL -> treated as 0) and Tech role (2) to Building A (2) which has child Room 101 (4)
		$pdo->exec('INSERT INTO role_x_location (roleid, locationid) VALUES (1, NULL)');
		$pdo->exec('INSERT INTO role_x_location (roleid, locationid) VALUES (2, 2)');
		// Machine table minimal
		$pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid) VALUES ('uuid-1', NULL, NULL)");
		// Ensure some machines with fixedlocationid/subnetlocationid and details for mismatch checks
		$pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid, fixedlocationid, hostname, clientip)
			VALUES ('m1', 1, 1, 5, 'host-m1', '10.0.0.10')");
		$pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid, fixedlocationid, hostname, clientip)
			VALUES ('m2', 5, 5, 5, 'host-m2', '192.168.1.50')");
		// Seed some client log entries within last month for syslog tests (types and listing)
		$now = time();
		$ins = $pdo->prepare('INSERT INTO clientlog (dateline, logtypeid, clientip, machineuuid, description, extra) VALUES (?, ?, ?, ?, ?, ?)');
		$ins->execute([$now - 60, 'session-open', '1.2.3.4', 'm1', 'User login', '']);
		$ins->execute([$now - 120, 'partition-temp', '2.3.4.5', 'm2', 'Temp high', '']);
		$pdo->exec("INSERT INTO machine (machineuuid, subnetlocationid, locationid, fixedlocationid, hostname, clientip) VALUES ('wrong2', 0, NULL, 2, 'host-w2', '10.0.0.77')");
	}

	public static function pdo(): PDO
	{
		return self::connect();
	}

	private static function startTimer(): float { return microtime(true); }

	private static function stopTimer(float $t0): void
	{
		self::$queryTime += (microtime(true) - $t0);
		self::$queryCount++;
	}

	/**
	 * Expand array parameters and special bulk VALUES :arg syntax used in legacy code
	 */
	private static function expandParams(string $sql, array $params): array
	{
		$flat = [];
		// Handle bulk insert syntax: VALUES :arg where :arg is array of associative rows
		if (preg_match('/VALUES\s*:(\w+)/i', $sql, $m)) {
			$key = $m[1];
			$rows = $params[$key] ?? [];
			if (!is_array($rows) || empty($rows)) {
				// No rows -> create a no-op that inserts zero rows; easiest is to use SELECT with no rows
				$sql = preg_replace('/VALUES\s*:\w+/i', "SELECT 1 WHERE 0", $sql);
				return [$sql, []];
			}
			$cols = array_keys($rows[0]);
			$groups = [];
			$idx = 0;
			foreach ($rows as $r) {
				$phs = [];
				foreach ($cols as $c) {
					$name = ":{$key}_{$c}_{$idx}";
					$flat[$key . '_' . $c . '_' . $idx] = $r[$c];
					$phs[] = $name;
				}
				$groups[] = '(' . implode(', ', $phs) . ')';
				$idx++;
			}
			$sql = preg_replace('/VALUES\s*:\w+/i', 'VALUES ' . implode(', ', $groups), $sql);
			// Remove the original bulk key so it does not get processed again
			unset($params[$key]);
		}
		// Handle IN (:list) where param value is array
		foreach ($params as $name => $value) {
			if (is_array($value)) {
				$placeholders = [];
				foreach (array_values($value) as $i => $val) {
					$ph = ":{$name}_{$i}";
					$placeholders[] = $ph;
					$flat[$name . '_' . $i] = $val;
				}
				$sql = preg_replace('/:' . preg_quote($name, '/') . '\b/', implode(', ', $placeholders), $sql);
			} else {
				$flat[$name] = $value;
			}
		}
		return [$sql, $flat];
	}

	public static function exec(string $sql, array $params = [], ...$rest): int
	{
		$pdo = self::connect();
		[$sql2, $flat] = self::expandParams($sql, $params);
		$sql2 = self::fixupSyntax($sql2);
		$t0 = self::startTimer();
		try {
			$stmt = $pdo->prepare($sql2);
		} catch (PDOException $e) {
			error_log($sql2);
		}
		$stmt->execute($flat);
		self::stopTimer($t0);
		return $stmt->rowCount();
	}

	/**
	 * Translates a MySQL-query into an SQLite query.
	 * Very rudimentary, might need improvements as we go.
	 */
 private static function fixupSyntax(string $sql): string
	{
		// MySQL -> SQLite tweaks
		$sql = preg_replace('/^\s*INSERT\s+IGNORE/i', 'INSERT OR IGNORE', $sql);
		// sat.* schema mapping to sat_* tables for tests
		$sql = preg_replace('/\bsat\.lecture_x_location\b/i', 'sat_lecture_x_location', $sql);
		$sql = preg_replace('/\bsat\.lecture\b/i', 'sat_lecture', $sql);
		$sql = preg_replace('/\bsat\.user\b/i', 'sat_user', $sql);
		// GROUP_CONCAT with SEPARATOR -> group_concat(expr, sep)
		$sql = preg_replace("/GROUP_CONCAT\s*\(\s*([^\)]*?)\s+SEPARATOR\s+'([^']*)'\s*\)/i", "group_concat($1, '$2')", $sql);
		// Generic GROUP_CONCAT -> group_concat
		$sql = preg_replace('/GROUP_CONCAT\s*\(/i', 'group_concat(', $sql);
		// duplicate key handling
		$parts = preg_split('/\bON\s+DUPLICATE\s+KEY\s+UPDATE\b/i', $sql);
		if (count($parts) === 2) {
			$parts[1] = preg_replace('/\bVALUES\s*\(\s*(\w+)\s*\)/i', 'excluded.$1', $parts[1]);
			$sql = implode(' ON CONFLICT DO UPDATE SET ', $parts);
		}
		return $sql;
	}

	public static function lastInsertId(): int
	{
		return (int)self::connect()->lastInsertId();
	}

	public static function queryFirst(string $sql, array $params = [])
	{
		$pdo = self::connect();
		[$sql2, $flat] = self::expandParams($sql, $params);
		$sql2 = self::fixupSyntax($sql2);
		$t0 = self::startTimer();
		$stmt = $pdo->prepare($sql2);
		$stmt->execute($flat);
		self::stopTimer($t0);
		$row = $stmt->fetch(PDO::FETCH_ASSOC);
		return $row !== false ? $row : false;
	}

	public static function queryAll(string $sql, array $params = [])
	{
		$pdo = self::connect();
		[$sql2, $flat] = self::expandParams($sql, $params);
		$sql2 = self::fixupSyntax($sql2);
		$t0 = self::startTimer();
		$stmt = $pdo->prepare($sql2);
		$stmt->execute($flat);
		self::stopTimer($t0);
		return $stmt->fetchAll(PDO::FETCH_ASSOC);
	}

	public static function simpleQuery(string $sql, array $params = [])
	{
		$pdo = self::connect();
		[$sql2, $flat] = self::expandParams($sql, $params);
		$sql2 = self::fixupSyntax($sql2);
		$t0 = self::startTimer();
		$stmt = $pdo->prepare($sql2);
		$stmt->execute($flat);
		self::stopTimer($t0);
		return new SqliteDbResult($stmt);
	}

	public static function queryIndexedList(string $sql)
	{
		$pdo = self::connect();
		$t0 = self::startTimer();
		$sql = self::fixupSyntax($sql);
		$stmt = $pdo->query($sql);
		$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
		self::stopTimer($t0);
		$out = [];
		foreach ($rows as $row) {
			// Use the first column as key
			$key = array_key_first($row);
			$out[$row[$key]] = $row;
		}
		return $out;
	}

	public static function queryKeyValueList(string $sql, array $params = [])
	{
		$pdo = self::connect();
		[$sql2, $flat] = self::expandParams($sql, $params);
		$sql2 = self::fixupSyntax($sql2);
		$t0 = self::startTimer();
		$stmt = $pdo->prepare($sql2);
		$stmt->execute($flat);
		$rows = $stmt->fetchAll(PDO::FETCH_NUM);
		self::stopTimer($t0);
		$out = [];
		foreach ($rows as $row) {
			if (count($row) >= 2) {
				$out[$row[0]] = $row[1];
			}
		}
		return $out;
	}

	/**
	 * Return rows grouped by a key. If the selected result includes a column named
	 * 'keyval', it will be used as the grouping key; otherwise, the first column is used.
	 * Each group contains a single-level numeric array of rows.
	 */
	public static function queryGroupList(string $sql)
	{
		$pdo = self::connect();
		$t0 = self::startTimer();
		$sql = self::fixupSyntax($sql);
		$stmt = $pdo->query($sql);
		$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
		self::stopTimer($t0);
		$out = [];
		foreach ($rows as $row) {
			$key = array_key_exists('keyval', $row) ? $row['keyval'] : $row[array_key_first($row)];
			$out[$key][] = $row;
		}
		return array_values($out);
	}

	public static function getQueryCount(): int { return self::$queryCount; }

	public static function getQueryTime(): float { return self::$queryTime; }
}

class SqliteDbResult implements IteratorAggregate
{
	private PDOStatement $stmt;
	private ?array $cache = null;

	public function __construct(PDOStatement $stmt)
	{
		$this->stmt = $stmt;
	}

	private function ensureCache(): void
	{
		if ($this->cache === null) {
			$this->cache = $this->stmt->fetchAll(PDO::FETCH_ASSOC);
		}
	}

	public function rowCount(): int
	{
		$this->ensureCache();
		return count($this->cache);
	}

	public function getIterator(): Traversable
	{
		$this->ensureCache();
		return new ArrayIterator($this->cache);
	}

	public function fetchAll(): array
	{
		$this->ensureCache();
		return $this->cache;
	}
}