<?php

$dbret = [];

$dbret[] = tableCreate('remoteaccess_group', "
	`groupid` int(11) NOT NULL AUTO_INCREMENT,
	`groupname` varchar(100) NOT NULL,
	`wolcount` smallint(11) NOT NULL,
	`passwd` varchar(100) NOT NULL,
	`active` tinyint(1) UNSIGNED NOT NULL DEFAULT '1',
	PRIMARY KEY (`groupid`)
");

$dbret[] = tableCreate('remoteaccess_x_location', "
	`groupid` int(11) NOT NULL,
	`locationid` int(11) NOT NULL,
	PRIMARY KEY (`groupid`, `locationid`)
");

$dbret[] = tableCreate('remoteaccess_machine', "
	`machineuuid` char(36) CHARACTER SET ascii NOT NULL,
	`password` char(8) CHARACTER SET ascii NULL DEFAULT NULL,
	`woltime` int(10) UNSIGNED NOT NULL DEFAULT '0',
	`vncport` smallint(5) UNSIGNED NOT NULL DEFAULT '5900',
	PRIMARY KEY (`machineuuid`)
");

$dbret[] = tableAddConstraint('remoteaccess_x_location', 'locationid', 'location', 'locationid',
	'ON UPDATE CASCADE ON DELETE CASCADE');

$dbret[] = tableAddConstraint('remoteaccess_x_location', 'groupid', 'remoteaccess_group', 'groupid',
	'ON UPDATE CASCADE ON DELETE CASCADE');

$dbret[] = tableAddConstraint('remoteaccess_machine', 'machineuuid', 'machine', 'machineuuid',
	'ON UPDATE CASCADE ON DELETE CASCADE');

if (tableExists('remoteaccess_location')
		&& tableExists('remoteaccess_x_location')
		&& tableExists('remoteaccess_group')) {
	// Migrate old version
	$wantedIdleCount = (int)Property::get('remoteaccess.wantedclients', 0);
	$ret = Database::exec("INSERT IGNORE INTO remoteaccess_group (groupid, groupname, wolcount, passwd)
			SELECT l.locationid, l.locationname, $wantedIdleCount AS blu, '' AS bla FROM location l
			    INNER JOIN remoteaccess_location rl USING (locationid)");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, Database::lastError());
	}
	Property::set('remoteaccess.wantedclients', 0, 1);
	$max = Database::queryFirst("SELECT groupid FROM remoteaccess_group ORDER BY groupid DESC LIMIT 1");
	if ($max !== false) {
		Database::exec("ALTER TABLE remoteaccess_group AUTO_INCREMENT = :next", ['next' => $max['groupid'] + 1]);
	}
	$ret = Database::exec("INSERT IGNORE INTO remoteaccess_x_location (groupid, locationid)
			SELECT locationid, locationid FROM remoteaccess_location");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, Database::lastError());
	}
	Database::exec("DROP TABLE remoteaccess_location");
}

// 2021-03-05: Add vncport column to machine table
if (!tableHasColumn('remoteaccess_machine', 'vncport')) {
	$ret = Database::exec("ALTER TABLE remoteaccess_machine ADD COLUMN `vncport` smallint(5) UNSIGNED NOT NULL DEFAULT '5900'");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, Database::lastError());
	}
	$dbret[] = UPDATE_DONE;
}

responseFromArray($dbret);