Add tests using PHPUnit

Tests generated by Junie AI. Might not have the best possible quality
but at least we got something, and if it turns out to be complete
rubbish, we can just throw it out again without any issues, as this is
independent of the actual code base.

1 files changed, 413 insertions, 0 deletions

diff --git a/tests/Stubs/Database.php b/tests/Stubs/Database.php
new file mode 100644
index 00000000..b0f8f30c
--- /dev/null
+++ b/tests/Stubs/Database.php
@@ -0,0 +1,413 @@
+<?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;
+	}
+}