<?php

$res = array();

$res[] = tableCreate('callback', "
	`taskid` varchar(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
	`dateline` int(10) unsigned NOT NULL,
	`cbfunction` varchar(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
	`args` text NOT NULL,
	PRIMARY KEY (`taskid`,`cbfunction`),
	KEY `dateline` (`dateline`)
");

$res[] = tableCreate('permission', "
	`mask` int(10) unsigned NOT NULL,
	`identifier` varchar(32) NOT NULL,
	PRIMARY KEY (`mask`),
	UNIQUE KEY `identifier` (`identifier`)
");

$res[] = tableCreate('property', "
	`name` varchar(50) NOT NULL,
	`dateline` int(10) unsigned NOT NULL DEFAULT '0',
	`value` mediumblob NOT NULL,
	PRIMARY KEY (`name`),
	KEY `dateline` (`dateline`)
");

$res[] = tableCreate('property_list', "
	`name` varchar(50) NOT NULL,
	`subkey` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`dateline` int(10) unsigned NOT NULL DEFAULT '0',
	`value` mediumblob NOT NULL,
	KEY (`name`),
	KEY `dateline` (`dateline`),
	KEY (`subkey`),
	UNIQUE KEY `compound` (`name`, `subkey`)
");

$res[] = tableCreate('user', "
	`userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`login` varchar(100) NOT NULL,
	`passwd` varchar(150) NOT NULL,
	`fullname` varchar(100) DEFAULT NULL,
	`phone` varchar(100) DEFAULT NULL,
	`email` varchar(100) DEFAULT NULL,
	`permissions` int(10) unsigned NOT NULL,
	`lasteventid` int(10) unsigned NOT NULL DEFAULT '0',
	`serverid` int(10) unsigned NULL DEFAULT NULL,
	PRIMARY KEY (`userid`),
	UNIQUE KEY `login` (`login`)
");

$res[] = tableCreate('session', "
	`sid` char(50) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
	`userid` int(10) unsigned NOT NULL,
	`dateline` int(10) unsigned NOT NULL DEFAULT '0',
	`lastip` varchar(45) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
	`fixedip` tinyint(1) unsigned NOT NULL DEFAULT '0',
	`data` blob NOT NULL,
	PRIMARY KEY (`sid`),
	KEY `dateline` (`dateline`)
");

$res[] = tableCreate('mail_queue', "
	`mailid` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`rcpt` varchar(200) NOT NULL,
	`subject` varchar(500) NOT NULL,
	`body` blob NOT NULL,
	`dateline` int(10) unsigned NOT NULL,
	`configid` int(10) unsigned NOT NULL,
	`nexttry` int(10) unsigned NOT NULL DEFAULT '0',
	PRIMARY KEY (`mailid`),
	KEY (`configid`),
	KEY (`nexttry`)
");

$res[] = tableCreate('mail_config', "
	`configid` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`host` varchar(100) NOT NULL,
	`port` smallint(5) UNSIGNED NOT NULL,
	`ssl` ENUM('FORCE_NONE', 'NONE', 'IMPLICIT', 'EXPLICIT') NOT NULL,
	`senderaddress` varchar(100) NOT NULL,
	`replyto` varchar(100) NOT NULL,
	`username` varchar(100) NOT NULL,
	`password` varchar(100) NOT NULL,
	PRIMARY KEY (`configid`)
");

// Update path

// #######################
// ##### 2014-05-28
// Add dateline field to property table
if (!tableHasColumn('property', 'dateline')) {
	Database::exec("ALTER TABLE `property` ADD `dateline` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `name`,
		ADD INDEX ( `dateline` )");
}

// #######################
// ##### 2014-08-18
// Remove description column from permission table
tableDropColumn('permission', 'description');
// Add details column to eventlog table
if (!tableHasColumn('user', 'lasteventid')) {
	Database::exec("ALTER TABLE `user` ADD `lasteventid` INT(10) UNSIGNED NOT NULL DEFAULT '0'");
}

// #######################
// ##### 2015-01-16
// Extend config module db table, add argument feature to callbacks
if (!tableHasColumn('callback', 'args')) {
	Database::exec("ALTER TABLE `callback` ADD `args` TEXT NOT NULL DEFAULT ''");
}

// #######################
// ##### 2018-03-19
// In preparation for LDAP/AD auth: Column to remember origin server
if (!tableHasColumn('user', 'serverid')) {
	Database::exec("ALTER TABLE `user` ADD `serverid` int(10) unsigned NULL DEFAULT NULL");
}

// #######################
// ##### 2022-07-04
// Add subkey to property_list, make value mediumblob instead of text
if (!tableHasColumn('property_list', 'subkey')) {
	$ret = Database::exec("ALTER TABLE property_list
    	ADD COLUMN `subkey` int(10) unsigned NOT NULL AUTO_INCREMENT AFTER `name`,
    	ADD KEY (`subkey`),
    	ADD UNIQUE KEY `compound` (`name`, `subkey`)");
	if ($ret === false) {
		finalResponse(UPDATE_FAILED, 'Cannot add subkey to property_list: ' . Database::lastError());
	}
	$res[] = UPDATE_DONE;
}
foreach (['property', 'property_list'] as $table) {
	if (stripos(tableColumnType($table, 'value'), 'mediumblob') === false) {
		$ret = Database::exec("ALTER TABLE `$table` MODIFY `value` mediumblob NOT NULL");
		if ($ret === false) {
			finalResponse(UPDATE_FAILED, "Cannot change value column of $table to mediumblob: " . Database::lastError());
		}
		$res[] = UPDATE_DONE;
	}
}

// Make sure that if any users exist, one of the has UID=1, otherwise if the permission module is
// used we'd lock out everyone
$someUser = Database::queryFirst('SELECT userid FROM user ORDER BY userid ASC LIMIT 1');
if ($someUser !== false && (int)$someUser['userid'] !== 1) {
	Database::exec('UPDATE user SET userid = 1 WHERE userid = :oldid', ['oldid' => $someUser['userid']]);
}

$res[] = tableAddConstraint('mail_queue', 'configid', 'mail_config', 'configid',
	'ON UPDATE CASCADE ON DELETE CASCADE');

// Create response for browser

if (in_array(UPDATE_DONE, $res)) {
	finalResponse(UPDATE_DONE, 'Tables created successfully');
}

finalResponse(UPDATE_NOOP, 'Everything already up to date');