<?php
/**
 * Upgrades the database from version 1.3.0 to version 1.3.1.
 * DO NOT call this directly, it will fail.
 * To be included from classes/core/updater.class.php.
 * Assumes that logging has already been init()ed. Log class will throw ServerException if not.
 */

Log::write(Log::LOGLEVEL_INFO, "In updater, from 1.3.0 to 1.3.1");

Log::write(Log::LOGLEVEL_INFO, "Extending mimetype column in files table...");
database::query("ALTER TABLE `file` CHANGE `mimetype` `mimetype` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL;");
Log::write(Log::LOGLEVEL_INFO, "...done");


Log::write(Log::LOGLEVEL_INFO, "Adding config items for pre-login message...");
database::query("INSERT INTO `config` ".
    "(`name`, `description`, `type`, `enumvalues`, `minimum`, `maximum`, `defaultvalue`, `value`) VALUES ".
    "('core_prelogin_messageheader', 'Pre-login message title', 'text', NULL, NULL, NULL, '', ''), ".
    "('core_prelogin_message', 'Pre-login message body', 'text', NULL, NULL, NULL, '', ''); "
);
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Creating table pdbdeposition...");
database::query("CREATE TABLE IF NOT EXISTS `pdbdeposition` ( 
    `id` BIGINT UNSIGNED NOT NULL , 
    `name` VARCHAR(50) NOT NULL COMMENT 'The PDB code' , 
    `projectid` BIGINT UNSIGNED NOT NULL , 
    `rawdatadoi` VARCHAR(250) NULL,
    UNIQUE KEY `id` (`id`),
    UNIQUE KEY `name` (`name`),
    KEY `projectid` (`projectid`)
) ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT = 'PDB entries of deposited structures';");
Log::write(Log::LOGLEVEL_INFO, "...adding constraints...");
database::query("ALTER TABLE `pdbdeposition` ADD FOREIGN KEY (`id`) REFERENCES `baseobject`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `pdbdeposition` ADD FOREIGN KEY (`projectid`) REFERENCES `project`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Creating table pdbdepositioncrystal...");
database::query("CREATE TABLE IF NOT EXISTS `pdbdepositioncrystal` (
    `id` BIGINT UNSIGNED NOT NULL , 
    `name` VARCHAR(250) NOT NULL , 
    `projectid` BIGINT UNSIGNED NOT NULL ,
    `pdbdepositionid` BIGINT UNSIGNED NOT NULL ,
    `crystalid` BIGINT UNSIGNED NOT NULL ,
    INDEX (`projectid`), 
    INDEX (`pdbdepositionid`),
    INDEX (`crystalid`), 
    UNIQUE (`name`), 
    UNIQUE (`id`)
) ENGINE = InnoDB COMMENT = 'Many-to-many crystal to PDB deposition';");
Log::write(Log::LOGLEVEL_INFO, "...adding constraints...");
database::query("ALTER TABLE `pdbdepositioncrystal` ADD FOREIGN KEY (`id`) REFERENCES `baseobject`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `pdbdepositioncrystal` ADD FOREIGN KEY (`projectid`) REFERENCES `project`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `pdbdepositioncrystal` ADD FOREIGN KEY (`pdbdepositionid`) REFERENCES `pdbdeposition`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `pdbdepositioncrystal` ADD FOREIGN KEY (`crystalid`) REFERENCES `crystal`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `pdbdepositioncrystal` ADD UNIQUE (`pdbdepositionid`, `crystalid`); ");
Log::write(Log::LOGLEVEL_INFO, "...done");


Log::write(Log::LOGLEVEL_INFO, "Creating table audiorecording...");
database::query('CREATE TABLE IF NOT EXISTS `audiorecording` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(150) COLLATE utf8_bin NOT NULL,
  `projectid` bigint(20) UNSIGNED NOT NULL,
  `parentid` bigint(20) UNSIGNED NOT NULL,
  `userid` bigint(20) UNSIGNED NOT NULL,
  `datetime` datetime NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`),
  KEY `parentid` (`parentid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;');
Log::write(Log::LOGLEVEL_INFO, "...adding constraints...");
database::query('ALTER TABLE `audiorecording`
  ADD CONSTRAINT `audiorecording_ibfk_1` FOREIGN KEY (`id`) REFERENCES `baseobject` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `audiorecording_ibfk_2` FOREIGN KEY (`projectid`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `audiorecording_ibfk_3` FOREIGN KEY (`parentid`) REFERENCES `baseobject` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `audiorecording_ibfk_4` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
');
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Adding createtime column to plate table...");
database::query("ALTER TABLE `plate` 
    ADD `createtime` DATETIME NULL DEFAULT NULL COMMENT 'Populated from baseobject on create' AFTER `offsetinscreeny`, 
    ADD INDEX (`createtime`);
");
Log::write(Log::LOGLEVEL_INFO, "...done");
Log::write(Log::LOGLEVEL_INFO, "Populating createtime column in plate table from baseobject.createtime...");
database::query("
    UPDATE baseobject, plate set plate.createtime=baseobject.createtime WHERE baseobject.id=plate.id 
");
Log::write(Log::LOGLEVEL_INFO, "...done");


Log::write(Log::LOGLEVEL_INFO, "Removing unused files:");
$toDelete=array(
    'upgrade/upgradeDatabase.php' //No longer used.
);
foreach ($toDelete as $path){
    /** @noinspection PhpUndefinedVariableInspection */
    $path=$wwwRoot.ltrim($path,'/');
    Log::write(Log::LOGLEVEL_INFO, "Removing ".$path);
    if(file_exists($path) && !@unlink($path)){
        throw new ServerException('Could not remove '.$path);
    }
}
Log::write(Log::LOGLEVEL_INFO, "...done");

