<?php

$res = array();

$res[] = tableCreate('setting_location', '
	`locationid` INT(11) NOT NULL,
	`setting` VARCHAR(28) NOT NULL,
	`value` TEXT NOT NULL,
	`displayvalue` TEXT NOT NULL,
	PRIMARY KEY (`locationid`,`setting`),
	KEY `setting` (`setting`)
');

$res[] = tableCreate('location', '
	`locationid` INT(11) NOT NULL AUTO_INCREMENT,
	`parentlocationid` INT(11) NOT NULL,
	`locationname` VARCHAR(100) NOT NULL,
	`openingtime` BLOB DEFAULT NULL,
	PRIMARY KEY (`locationid`),
	KEY `locationname` (`locationname`),
	KEY `parentlocationid` (`parentlocationid`)
');

$res[] = tableCreate('subnet', '
	`subnetid` INT(11) NOT NULL AUTO_INCREMENT,
	`startaddr` DECIMAL(39,0) UNSIGNED NOT NULL,
	`endaddr` DECIMAL(39,0) UNSIGNED NOT NULL,
	`locationid` INT(11) NOT NULL,
	PRIMARY KEY (`subnetid`),
	KEY `startaddr` (`startaddr`,`endaddr`),
	KEY `locationid` (`locationid`)
');

$res[] = tableAddConstraint('subnet', 'locationid', 'location', 'locationid',
	'ON UPDATE CASCADE ON DELETE CASCADE');
$res[] = tableAddConstraint('setting_location', 'locationid', 'location', 'locationid',
	'ON UPDATE CASCADE ON DELETE CASCADE');

// Update

// 2020-07-14 Add openingtime column to location table, then migrate data and delete the column from locationinfo
if (!tableHasColumn('location', 'openingtime')) {
	if (Database::exec("ALTER TABLE location ADD openingtime BLOB DEFAULT NULL") === false) {
		finalResponse(UPDATE_FAILED, 'Could not create openingtime column');
	}
	$res[] = UPDATE_DONE;
}
if (tableHasColumn('locationinfo_locationconfig', 'openingtime')) {
	if (Database::exec(
			"UPDATE location, locationinfo_locationconfig
			SET location.openingtime = locationinfo_locationconfig.openingtime
			WHERE location.locationid = locationinfo_locationconfig.locationid
			  AND (Length(location.openingtime) < 5 OR location.openingtime IS NULL)
			  AND Length(locationinfo_locationconfig.openingtime) > 5") === false) {
		finalResponse(UPDATE_FAILED, 'Could not migrate openingtime data from table to table');
	}
	if (Database::exec("ALTER TABLE locationinfo_locationconfig DROP COLUMN openingtime") === false) {
		finalResponse(UPDATE_FAILED, 'Could not delete old openingtime column');
	}
	$res[] = UPDATE_DONE;
}

// 2021-03-19: Fix this. No idea how this came to be, maybe during dev only? But better be safe...
Database::exec("UPDATE location SET openingtime = NULL WHERE openingtime = ''");

// Create response for browser
responseFromArray($res);