<?php

require_once 'lib/IRMDB.php';

error_reporting(E_ALL);

// The index of the upgrades array shows the version of the database which you
// are upgrading *from*.

$UPGRADES = array();

$UPGRADES['1.3.0'] = array(
			"ALTER TABLE tracking
				MODIFY status ENUM 
				('new', 'old', 'wait', 'assigned', 'active',
					'complete')"
			);

$UPGRADES['1.3.1'] = array(
			"CREATE TABLE config (
				ID int(11) NOT NULL default '0',
				notifyassignedbyemail tinyint(4) NOT NULL default '1',
				notifynewtrackingbyemail tinyint(4) NOT NULL default '0',
				newtrackingemail char(200) NOT NULL default 'user@host.com',
				groups tinyint(4) NOT NULL default '1',
				usenamesearch tinyint(4) NOT NULL default '1',
				userupdates tinyint(4) NOT NULL default '1',
				sendexpire tinyint(4) NOT NULL default '0',
				showjobsonlogin tinyint(4) NOT NULL default '1',
				minloglevel tinyint(4) NOT NULL default '5',
				logo char(50) NOT NULL default 'irm-jr1.jpg',
				snmp tinyint(4) NOT NULL default '0',
				snmp_rcommunity char(50) NOT NULL default 'public',
				snmp_ping tinyint(4) NOT NULL default '0',
				version char(50) NOT NULL default '1.3.2',
				build char(50) NOT NULL default '2001041201',
				PRIMARY KEY (ID),
				UNIQUE KEY ID_2 (ID),
				KEY ID (ID))",
			"ALTER table inst_software
				ADD lID int default '0' not null",
			"ALTER table inst_software
				ADD index(lID)",
			"ALTER table inst_software
				ADD gID int(11)",
			"ALTER table software drop version",
			"ALTER table software drop serial",
			"ALTER table software drop otherserial",
			"ALTER table software drop location",
			"ALTER table software drop license",
			"ALTER table software 
				ADD class ENUM('Operating System',
					'Application',
					'CAL',
					'Application Bundle')
				     DEFAULT 'Application' AFTER platform",
			"CREATE TABLE software_bundles (
				bID int(11) unsigned DEFAULT '0' NOT NULL,
				sID int(11) unsigned DEFAULT '0' NOT NULL,
				KEY sID_ndx (sID),
				KEY bID_ndx (bID),
				PRIMARY KEY (sID,bID))",
			"CREATE TABLE software_licenses (
				sID int(11) NOT NULL,
				licensekey varchar(200),
				entitlement int(11) DEFAULT '0' NOT NULL,
				ID int(11) NOT NULL auto_increment,
				oem_sticker enum ('Yes', 'No') DEFAULT 'No' NOT NULL,
				PRIMARY KEY (ID),
				KEY sID_ndx (sID),
				KEY ID_ndx (ID))",
			"DROP TABLE IF EXISTS version",
			"INSERT INTO config
				(ID, notifyassignedbyemail,
				 notifynewtrackingbyemail, newtrackingemail,
				 groups, usenamesearch, userupdates,
				 sendexpire, showjobsonlogin, minloglevel,
				 logo, snmp, snmp_rcommunity, snmp_ping,
				 version, build)
			     VALUES
				('0', '1', '0', 'user@host.com', '1', '1',
				 '1', '0', '1', '5', 'irm-jr1.jpg', '0',
				 'public', '0', '1.3.2', '2001041201')"
			);

$UPGRADES['1.3.2'] = array(
			"ALTER TABLE config
				ADD knowledgebase tinyint(4) AFTER build",
			"UPDATE config SET
				ID=0,
				version='1.3.3',
				build='20010516',
				knowledgebase=1",
			"CREATE TABLE kbcategories (
				ID int(11) NOT NULL auto_increment,
				parentID int(11) NOT NULL default '0',
				name text NOT NULL,
				PRIMARY KEY (ID),
				KEY ID (ID))",
			"CREATE TABLE kbarticles (
				ID int(11) NOT NULL auto_increment,
				categoryID int(11) NOT NULL default '0',
				question text NOT NULL,
				answer text NOT NULL,
				faq enum('yes','no') NOT NULL default 'no',
				PRIMARY KEY (ID), KEY ID (ID))",
			"INSERT INTO kbcategories VALUES (1, 0, 'IRM')",
			"INSERT INTO kbcategories VALUES (2, 1, 'Computers')",
			"INSERT INTO kbcategories VALUES (3, 1, 'Networking')",
			"INSERT INTO kbcategories VALUES (4, 1, 'Software')",
			"INSERT INTO kbcategories VALUES (5, 1, 'Tracking')",
			"INSERT INTO kbcategories VALUES (6, 1, 'Reports')",
			"INSERT INTO kbcategories VALUES (7, 1, 'Request Help')",
			"INSERT INTO kbcategories VALUES (8, 1, 'Setup')",
			"INSERT INTO kbcategories VALUES (9, 1, 'Preferences')",
			"INSERT INTO kbcategories VALUES (10, 1, 'Knowledge Base')",
			"INSERT INTO kbcategories VALUES (11, 1, 'FAQ')",
			"INSERT INTO kbcategories VALUES (12, 1, 'Logout')"
			);

$UPGRADES['1.3.3'] = array(
			"ALTER TABLE users RENAME usersbak",
			"CREATE TABLE users (
				name char(255) DEFAULT '' NOT NULL,
				password char(255),
				fullname char(200),
				email char(100),
				location char(200),
				phone char(100),
				type enum('post-only','normal','tech','admin')
					DEFAULT 'post-only' NOT NULL,
				comments text,
				PRIMARY KEY(name),
				KEY (type))"
			);

	// Need to convert all of the users to the new user table schema
	// Remember the code here gets run *before* the schema has been
	// changed, but the INSERT queries generated get run *after* the
	// schema change

	// $DB comes from the including function, and is an IRMDB handle
	// to the database we're currently operating on.
	$rows = $DB->getAll("SELECT * FROM users", NULL, array(), NULL,
				MDB_FETCHMODE_ASSOC);
	
	foreach ($rows as $row)
	{
		$username = $DB->getTextValue($row["name"]);
		$password = $DB->getTextValue($row["password"]);
		$email = $DB->getTextValue($row["email"]);
		$location = $DB->getTextValue($row["location"]);
		$phone = $DB->getTextValue($row["phone"]);
		$type = $DB->getTextValue($row["type"]);
		$comments = $DB->getTextValue($row["comments"]);
		$UPGRADES['1.3.3'][] = "INSERT INTO users
				(name, password, fullname, email, location,
				 phone, type, comments)
			    VALUES
				($username, $password, $username,
				 $email, $location, $phone, $type,
				 $comments)";
	}

$UPGRADES['1.3.3'][] = "ALTER TABLE config ADD fasttrack int default '1'";
$UPGRADES['1.3.3'][] = "CREATE TABLE fasttracktemplates (
				ID INT NOT NULL auto_increment,
				name char(100),
				priority int(11),
				request text,
				response text,
				PRIMARY KEY (ID))";
$UPGRADES['1.3.3'][] = "INSERT INTO fasttracktemplates
				(name, priority, request, response)
				VALUES
				('Default',3, '', '')";
$UPGRADES['1.3.3'][] = "INSERT INTO fasttracktemplates
				(name, priority, request, response)
				VALUES
				('Reset Password',3, 'User forgot password',
				 'Reset password on the system')";
$UPGRADES['1.3.3'][] = "INSERT INTO fasttracktemplates
				(name, priority, request, response)
				VALUES
				('Floppy Disk in Drive',3, 'Computer will not boot, it says something about NTLDR not found', 'There was a floppy disk in the drive, once user removed it and rebooted system it started up just fine.')";
$UPGRADES['1.3.3'][] = "UPDATE config SET
				ID=0,
				version='1.3.4', build='2001071101'";

$UPGRADES['1.3.4'] = array(
			"ALTER TABLE config
				ADD anonymous tinyint(4) AFTER fasttrack",
			"ALTER TABLE config
				ADD anon_faq tinyint(4) AFTER anonymous",
			"ALTER TABLE config
				ADD anon_tt tinyint(4) AFTER anon_faq"
			);

$UPGRADES['1.4.2'] = array(
			"ALTER TABLE templates
				ADD flags_surplus tinyint(4) AFTER iface",
			"UPDATE config SET
				version = '1.4.3',
				build = '20040108'"
			);

$UPGRADES['1.4.3'] = array(
			"ALTER TABLE config
				MODIFY knowledgebase tinyint NOT NULL default 1",
			"ALTER TABLE config
				MODIFY fasttrack tinyint(4) NOT NULL default 1",
			"ALTER TABLE config
				MODIFY anonymous tinyint NOT NULL default 0",
			"ALTER TABLE config
				MODIFY anon_faq tinyint NOT NULL default 0",
			"ALTER TABLE config
				MODIFY anon_tt tinyint(4) NOT NULL default 0",
			"ALTER TABLE inst_software
				ADD lCnt int(11) NOT NULL default 1",
			"ALTER TABLE computers
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE event_log
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE followups
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE groups
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE inst_software
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE networking
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE networking_ports
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE networking_wire
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE software
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE templ_inst_software
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE templates
				MODIFY ID int(11) NOT NULL auto_increment",
			"ALTER TABLE tracking
				MODIFY ID int(11) NOT NULL auto_increment",
			"DROP TABLE IF EXISTS usersbak",
			"UPDATE users SET
				password=md5(password)
				WHERE password NOT REGEXP '^[0-9a-f]{32}$'",
			"UPDATE config SET
				version = '1.5.0',
				build = '1.5.0'"
			);

$UPGRADES['1.5.0'] = array("ALTER TABLE config
				MODIFY version char(50) NOT NULL DEFAULT '0'",
			"ALTER TABLE config
				DROP build",
			"ALTER TABLE software
				ADD install_package varchar(255) AFTER platform",
			"ALTER TABLE followups
				ADD public TINYINT NOT NULL DEFAULT 1 AFTER contents",
			"ALTER TABLE tracking
				ADD other_emails TEXT AFTER emailupdates",
			"ALTER TABLE computers
				MODIFY ID BIGINT UNSIGNED NOT NULL",
			"CREATE TABLE computers__ID (
				sequence BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (sequence)
				)",
			"ALTER TABLE tracking
				MODIFY ID BIGINT UNSIGNED NOT NULL",
			"CREATE TABLE tracking__ID (
				sequence BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (sequence)
				)",
			"ALTER TABLE networking
				MODIFY ID BIGINT UNSIGNED NOT NULL",
			"CREATE TABLE networking__ID (
				sequence BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
				PRIMARY KEY (sequence)
				)"
			);

// Create "sequences" (MDB-style emulations thereof, anyway, since MySQL
// doesn't do real sequences) for relevant tables, and set the nextID value
// appropriately
$lastid = $DB->getOne("SELECT ID+1 FROM computers ORDER BY ID DESC LIMIT 1");
if ($lastid)
{
	$UPGRADES['1.5.0'][] = "INSERT INTO computers__ID (sequence) VALUES ($lastid)";
}
$lastid = $DB->getOne("SELECT ID+1 FROM tracking ORDER BY ID DESC LIMIT 1");
if ($lastid)
{
	$UPGRADES['1.5.0'][] = "INSERT INTO tracking__ID (sequence) VALUES ($lastid)";
}
$lastid = $DB->getOne("SELECT ID+1 FROM networking ORDER BY ID DESC LIMIT 1");
if ($lastid)
{
	$UPGRADES['1.5.0'][] = "INSERT INTO networking__ID (sequence) VALUES ($lastid)";
}

$UPGRADES['1.5.0'][] = "UPDATE config SET
				version = '1.5.1'";

// Retrieve the current system config
$DB->setErrorHandling(PEAR_ERROR_RETURN);
$cfgvars = $DB->getRow("SELECT * FROM config");
$DB->setErrorHandling(PEAR_ERROR_CALLBACK, 'DBDie');
if (MDB::isError($cfgvars))
{
	$cfgvars = array();
}

$UPGRADES['1.5.1'] = array("DROP TABLE IF EXISTS config",
			"CREATE TABLE config (
				variable	VARCHAR(255) NOT NULL,
				value		TEXT NOT NULL,
				PRIMARY KEY (variable)
			)");

foreach ($cfgvars as $var => $value)
{
	if ($var == 'ID')
	{
		continue;
	}
	if ($var == 'version')
	{
		$var = 'dbver';
	}

	$qval = $DB->getTextValue($value);
	$qvar = $DB->getTextValue($var);
	$query = "INSERT INTO config (variable, value) VALUES ($qvar, $qval)";
	$UPGRADES['1.5.1'][] = $query;
}

// Repair any sequences that might have become cruftified since our inability
// to actually consider the consequences of our actions...
$lastid = $DB->getOne("SELECT ID+1 FROM computers ORDER BY ID DESC LIMIT 1");
if ($lastid)
{
	$UPGRADES['1.5.1'][] = "DELETE FROM computers__ID";
	$UPGRADES['1.5.1'][] = "INSERT INTO computers__ID (sequence) VALUES ($lastid)";
}
$lastid = $DB->getOne("SELECT ID+1 FROM tracking ORDER BY ID DESC LIMIT 1");
if ($lastid)
{
	$UPGRADES['1.5.1'][] = "DELETE FROM tracking__ID";
	$UPGRADES['1.5.1'][] = "INSERT INTO tracking__ID (sequence) VALUES ($lastid)";
}
$lastid = $DB->getOne("SELECT ID+1 FROM networking ORDER BY ID DESC LIMIT 1");
if ($lastid)
{
	$UPGRADES['1.5.1'][] = "DELETE FROM networking__ID";
	$UPGRADES['1.5.1'][] = "INSERT INTO networking__ID (sequence) VALUES ($lastid)";
}

$UPGRADES['1.5.1'][] = "DELETE FROM config WHERE variable='dbver'";
$UPGRADES['1.5.1'][] = "INSERT INTO config (value, variable) VALUES ('1.5.2', 'dbver')";

// Very major fuckup in the 1.5.2->1.5.3 upgrade code.  I forgot to update
// the dbver config variable, leading to nasty duplicate upgrade errors.
// Here's an attempt to get around that.

$DB->pushErrorHandling(PEAR_ERROR_RETURN);
if (MDB::isError($DB->getOne("SELECT status FROM tracking_status")))
{
	$UPGRADES['1.5.2'] = array(
		"ALTER TABLE tracking TYPE=InnoDB",
		"CREATE TABLE tracking_status (
			status		VARCHAR(255) NOT NULL,
			closed		TINYINT UNSIGNED NOT NULL DEFAULT 0,
			PRIMARY KEY (status)) TYPE=InnoDB",
		"INSERT INTO tracking_status (status, closed) VALUES
			('new', 0),
			('old', 1),
			('wait', 0),
			('assigned', 0),
			('active', 0),
			('complete', 1)",
		"ALTER TABLE tracking ADD INDEX (status)",
		"ALTER TABLE tracking MODIFY status VARCHAR(255) NOT NULL DEFAULT 'new'",
		"ALTER TABLE tracking ADD FOREIGN KEY (status) REFERENCES tracking_status (status)"
		);
}
$DB->popErrorHandling();

$UPGRADES['1.5.2'][] = "DELETE FROM config WHERE variable='dbver'";
$UPGRADES['1.5.2'][] = "INSERT INTO config (variable, value) VALUES ('dbver', '1.5.3')";