<?php

$res = array();

// The main statistic table used for log entries

$res[] = tableCreate('statistic', "
  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `dateline` int(10) unsigned NOT NULL,
  `typeid` varchar(30) NOT NULL,
  `clientip` varchar(40) NOT NULL,
  `machineuuid` char(36) CHARACTER SET ascii DEFAULT NULL,
  `username` varchar(30) NOT NULL,
  `data` BLOB NOT NULL,
  PRIMARY KEY (`logid`),
  KEY `dateline` (`dateline`),
  KEY `logtypeid` (`typeid`,`dateline`),
  KEY `clientip` (`clientip`,`dateline`),
  KEY `machineuuid` (`machineuuid`,`dateline`)
");

// Main table containing all known clients

$res[] = tableCreate('machine', "
  `machineuuid` char(36) CHARACTER SET ascii NOT NULL,
  `fixedlocationid` int(11) DEFAULT NULL           COMMENT 'Manually set location (e.g. roomplanner)',
  `subnetlocationid` int(11) DEFAULT NULL          COMMENT 'Automatically determined location (e.g. from subnet match)',
  `locationid` int(11) DEFAULT NULL                COMMENT 'Will be automatically set to fixedlocationid if not null, subnetlocationid otherwise',
  `macaddr` char(17) CHARACTER SET ascii NOT NULL,
  `clientip` varchar(45) CHARACTER SET ascii NOT NULL,
  `firstseen` int(10) unsigned NOT NULL,
  `lastseen` int(10) unsigned NOT NULL,
  `logintime` int(10) unsigned NOT NULL,
  `position` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `lastboot` int(10) unsigned NOT NULL,
  `state` enum('OFFLINE', 'IDLE', 'OCCUPIED', 'STANDBY', 'IGNORED') NOT NULL DEFAULT 'OFFLINE',
  `realcores` smallint(5) unsigned NOT NULL,
  `mbram` int(10) unsigned NOT NULL,
  `kvmstate` enum('UNKNOWN','UNSUPPORTED','DISABLED','ENABLED') NOT NULL,
  `cpumodel` varchar(120) NOT NULL,
  `systemmodel` varchar(120) NOT NULL DEFAULT '',
  `id44mb` int(10) unsigned NOT NULL,
  `id45mb` int(10) unsigned NOT NULL DEFAULT 0,
  `badsectors` int(10) unsigned NOT NULL,
  `data` mediumblob NOT NULL,
  `dataparsetime` int(10) unsigned NOT NULL DEFAULT 0,
  `hostname` varchar(200) NOT NULL DEFAULT '',
  `currentsession` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `currentuser` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`machineuuid`),
  KEY `macaddr` (`macaddr`),
  KEY `clientip` (`clientip`),
  KEY `state` (`state`),
  KEY `realcores` (`realcores`),
  KEY `mbram` (`mbram`),
  KEY `kvmstate` (`kvmstate`),
  KEY `id44mb` (`id44mb`),
  KEY `locationid` (`locationid`),
  KEY `lastseen` (`lastseen`),
  KEY `cpumodel` (`cpumodel`),
  KEY `systemmodel` (`systemmodel`)
");

$res[] = $machineHwCreate = tableCreate('machine_x_hw', "
  `machinehwid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hwid` int(10) unsigned NOT NULL,
  `machineuuid` char(36) CHARACTER SET ascii NOT NULL,
  `devpath` char(32) CHARACTER SET ascii NOT NULL,
  `disconnecttime` int(10) unsigned NOT NULL COMMENT 'time the device was not connected to the pc anymore for the first time, 0 if it is connected',
  PRIMARY KEY (`machinehwid`),
  UNIQUE KEY `hwid` (`hwid`,`machineuuid`,`devpath`),
  KEY `machineuuid` (`machineuuid`,`hwid`),
  KEY `disconnecttime` (`disconnecttime`)
 ");

$res[] = tableCreate('machine_x_hw_prop', "
  `machinehwid` int(10) unsigned NOT NULL,
  `prop` varchar(64) CHARACTER SET ascii NOT NULL,
  `value` varchar(500) NOT NULL,
  `numeric` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`machinehwid`,`prop`)
");

$res[] = tableCreate('statistic_hw', "
  `hwid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hwtype` char(16) CHARACTER SET ascii NOT NULL,
  `hwname` char(32) CHARACTER SET ascii NOT NULL,
  PRIMARY KEY (`hwid`),
  UNIQUE KEY `hwtype` (`hwtype`,`hwname`)
");

$res[] = tableCreate('statistic_hw_prop', "
  `hwid` int(10) unsigned NOT NULL,
  `prop` varchar(64) CHARACTER SET ascii NOT NULL,
  `value` varchar(500) NOT NULL,
  `numeric` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`hwid`,`prop`)
");

// PCI-ID cache

$res[] = tableCreate('pciid', "
	`category` enum('CLASS','VENDOR','DEVICE') NOT NULL,
	`id` varchar(10) CHARACTER SET ascii NOT NULL,
	`value` varchar(200) NOT NULL,
	`dateline` int(10) unsigned NOT NULL,
	PRIMARY KEY (`category`,`id`)
");

// baseconfig override per machine
$res[] = tableCreate('setting_machine', '
	`machineuuid` char(36) CHARACTER SET ascii NOT NULL,
	`setting` VARCHAR(28) NOT NULL,
	`value` TEXT NOT NULL,
	`displayvalue` TEXT NOT NULL,
	PRIMARY KEY (`machineuuid`,`setting`),
	KEY `setting` (`setting`)
');

//
// This was added/changed later -- keep update path
//

// 2015-12-21: Add machine uuid column to statistics table
if (!tableHasColumn('statistic', 'machineuuid')) {
	$ret = Database::exec('ALTER TABLE statistic'
		. ' ADD COLUMN `machineuuid` varchar(36) CHARACTER SET ascii DEFAULT NULL AFTER clientip,'
		. ' ADD INDEX `machineuuid` (`machineuuid`,`dateline`)');
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding machineuuid to statistic failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// Rename roomid to locationid
if (tableHasColumn('machine', 'roomid')) {
	$ret = Database::exec("ALTER TABLE `machine` CHANGE `roomid` `locationid` INT(11) DEFAULT NULL") !== false;
	$ret = Database::exec("ALTER TABLE `machine` DROP `roomid`") !== false || $ret;
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Renaming roomid to locationid in statistic failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2016-08-31: Add lectureid and user name
if (!tableHasColumn('machine', 'currentsession')) {
	$ret = Database::exec("ALTER TABLE `machine` ADD COLUMN `currentsession` varchar(120) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL AFTER hostname") !== false;
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding currentsession to machine failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
if (!tableHasColumn('machine', 'currentuser')) {
	$ret = Database::exec("ALTER TABLE `machine` ADD COLUMN `currentuser` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL AFTER currentsession") !== false;
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding currentuser to machine failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
// 2016-09-01: Fix position column size
$ret = Database::exec("ALTER TABLE `machine` CHANGE `position` `position` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL");
if ($ret === false) {
	finalResponse(UPDATE_FAILED, 'Expanding position column failed: ' . Database::lastError());
}

// 2016-12-06:
// Add subnetlocationid - contains automatically determined location (by subnet)
if (!tableHasColumn('machine', 'subnetlocationid')) {
	$ret = Database::exec('ALTER TABLE machine'
		. ' ADD COLUMN `subnetlocationid` int(11) DEFAULT NULL AFTER `machineuuid`');
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding subnetlocationid to machine failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
// And fixedlocationid - manually set location, currently used by roomplanner
if (!tableHasColumn('machine', 'fixedlocationid')) {
	$ret = Database::exec('ALTER TABLE machine'
		. ' ADD COLUMN `fixedlocationid` int(11) DEFAULT NULL AFTER `machineuuid`');
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding fixedlocationid to machine failed: ' . Database::lastError());
	}
	// Now copy over the values from locationid, since this was used before
	Database::exec("UPDATE machine SET fixedlocationid = locationid");
	$res[] = UPDATE_DONE;
}

$checkTrigger = Database::queryFirst("show triggers where `Trigger` = 'set_automatic_locationid'");
if ($checkTrigger === false) {
	$ret = Database::exec(" 
	CREATE TRIGGER set_automatic_locationid
		BEFORE UPDATE ON machine FOR EACH ROW
	BEGIN
		SET NEW.locationid = If(NEW.fixedlocationid IS NULL, NEW.subnetlocationid, NEW.fixedlocationid);
	END");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding locationid trigger to machine failed: ' . Database::lastError());
	}
	// This might be an update - calculate all subnetlocationid values (if location module is installed yet)
	if (Module::isAvailable('locations')) {
		if (tableExists('subnet')) {
			AutoLocation::rebuildAll();
		} else {
			$res[] = UPDATE_RETRY;
		}
	}
	$res[] = UPDATE_DONE;
}

$res[] = tableAddConstraint('machine_x_hw', 'hwid', 'statistic_hw', 'hwid', 'ON DELETE CASCADE');
$res[] = tableAddConstraint('machine_x_hw', 'machineuuid', 'machine', 'machineuuid', 'ON DELETE CASCADE ON UPDATE CASCADE');
$res[] = tableAddConstraint('machine_x_hw_prop', 'machinehwid', 'machine_x_hw', 'machinehwid', 'ON DELETE CASCADE');
$res[] = tableAddConstraint('statistic_hw_prop', 'hwid', 'statistic_hw', 'hwid', 'ON DELETE CASCADE');
if (Module::isAvailable('locations')) {
	if (tableExists('location')) {
		$res[] = tableAddConstraint('machine', 'fixedlocationid', 'location', 'locationid',
			'ON UPDATE CASCADE ON DELETE SET NULL');
		// No constraint for subnetlocationid -- needs recalc anyways (AutoLocation::rebuildAll())
	} else {
		$res[] = UPDATE_RETRY;
	}
}

// 2017-11-27: Add state column
if (!tableHasColumn('machine', 'state')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `state` enum('OFFLINE', 'IDLE', 'OCCUPIED', 'STANDBY', 'IGNORED') NOT NULL DEFAULT 'OFFLINE' AFTER `lastboot`,
		ADD INDEX `state` (`state`)");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding state column to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2019-01-25: Add memory/temp stats column
if (!tableHasColumn('machine', 'live_tmpsize')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `live_tmpsize` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `id44mb`,
		ADD COLUMN `live_tmpfree` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_tmpsize`,
		ADD COLUMN `live_swapsize` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_tmpfree`,
		ADD COLUMN `live_swapfree` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_swapsize`,
		ADD COLUMN `live_memsize` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_swapfree`,
		ADD COLUMN `live_memfree` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_memsize`,
		ADD INDEX `live_tmpfree` (`live_tmpfree`),
		ADD INDEX `live_memfree` (`live_memfree`)");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding mem-stat columns to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2019-02-20: Convert bogus UUIDs
$res2 = Database::simpleQuery("SELECT machineuuid, macaddr FROM machine WHERE machineuuid LIKE '00000000000000_-%'");
foreach ($res2 as $row) {
	$new = strtoupper('baad1d00-9491-4716-b98b-' . preg_replace('/[^0-9a-f]/i', '', $row['macaddr']));
	error_log('Replacing ' . $row['machineuuid'] . ' with ' . $new);
	if (strlen($new) === 36) {
		if (Database::exec("UPDATE machine SET machineuuid = :new WHERE machineuuid = :old",
					['old' => $row['machineuuid'], 'new' => $new]) === false) {
			error_log('Result: ' . Database::lastError());
			Database::exec("DELETE FROM machine WHERE machineuuid = :old", ['old' => $row['machineuuid']]);
		}
	}
}

// 2019-10-31: New table for per-machine config override
$res[] = tableAddConstraint('setting_machine', 'machineuuid', 'machine', 'machineuuid',
	'ON UPDATE CASCADE ON DELETE CASCADE');

// 2020-04-25: Track enter/exit standby count, live CPU load
if (!tableHasColumn('machine', 'live_cpuload')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `live_cpuload` tinyint(3) UNSIGNED NOT NULL DEFAULT '255' AFTER `live_memfree`,
		ADD COLUMN `live_cputemp` tinyint(3) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_cpuload`,
		ADD COLUMN `standbysem` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
		ADD INDEX `live_cpuload` (`live_cpuload`),
		ADD INDEX `live_cputemp` (`live_cputemp`)");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding live_cpuload column to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2020-06-29: Track current runmode (as reported by client)
if (!tableHasColumn('machine', 'currentrunmode')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `currentrunmode` varchar(30) NOT NULL DEFAULT '' AFTER `hostname`");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding live_cpuload column to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2019-01-25: Add memory/temp stats column
if (!tableHasColumn('machine', 'live_id45size')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `live_id45size` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_tmpfree`,
		ADD COLUMN `live_id45free` int(10) UNSIGNED NOT NULL DEFAULT '0' AFTER `live_id45size`");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding mem-stat columns to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2021-08-19 Enhanced machine property indexing
if (stripos(tableColumnType('statistic_hw_prop', 'prop'), 'varchar(64)') === false) {
	Database::exec("DELETE FROM statistic_hw_prop WHERE prop NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
	$ret = Database::exec("ALTER TABLE statistic_hw_prop
    	MODIFY `prop` varchar(64) CHARACTER SET ascii NOT NULL,
    	ADD `numeric` bigint(20) DEFAULT NULL");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Changing prop of statistic_hw_prop failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
if (stripos(tableColumnType('machine_x_hw_prop', 'prop'), 'varchar(64)') === false) {
	Database::exec("DELETE FROM machine_x_hw_prop WHERE prop NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
	$ret = Database::exec("ALTER TABLE machine_x_hw_prop
    	MODIFY `prop` varchar(64) CHARACTER SET ascii NOT NULL,
    	ADD `numeric` bigint(20) DEFAULT NULL");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Changing prop of machine_x_hw_prop failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
if (stripos(tableColumnType('statistic_hw', 'hwname'), 'char(32)') === false) {
	Database::exec("DELETE FROM statistic_hw WHERE hwname NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
	$ret = Database::exec("ALTER TABLE statistic_hw MODIFY `hwname` char(32) CHARACTER SET ascii NOT NULL,
		MODIFY `hwtype` char(16) CHARACTER SET ascii NOT NULL");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Changing hwname/hwtype of statistic_hw failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
if (stripos(tableColumnType('machine_x_hw', 'devpath'), 'char(32)') === false) {
	Database::exec("DELETE FROM machine_x_hw WHERE devpath NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
	$ret = Database::exec("ALTER TABLE machine_x_hw MODIFY `devpath` char(32) CHARACTER SET ascii NOT NULL");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Changing devpath of machine_x_hw failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
if (!tableHasColumn('machine', 'dataparsetime')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `dataparsetime` int(10) unsigned NOT NULL DEFAULT '0' AFTER `data`");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding dateparsetime column to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
if (!tableHasColumn('machine', 'id45mb')) {
	$ret = Database::exec("ALTER TABLE `machine`
		ADD COLUMN `id45mb` int(10) unsigned NOT NULL DEFAULT 0 AFTER `id44mb`");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Adding id45mb column to machine table failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// 2022-11-22 Change data column of statistic table from varchar(255) to blob
if (stripos(tableColumnType('statistic', 'data'), 'blob') === false) {
	$ret = Database::exec("ALTER TABLE `statistic` MODIFY COLUMN `data` BLOB NOT NULL");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Changing statistic.data to blob failed: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}

// Create response
responseFromArray($res);