<?php /** @noinspection PhpUnhandledExceptionInspection */
/**
 * Upgrades the database from version 1.11.0 to version 1.12.0
 * 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::info("In updater, from 1.11.to 1.12.0...");

Log::info("Adding datasetlocation table...");
database::query('CREATE TABLE IF NOT EXISTS `datasetlocation` (`id` BIGINT UNSIGNED NOT NULL , `name` VARCHAR(200) NOT NULL,  `projectid` BIGINT UNSIGNED NOT NULL, `datasetid` BIGINT UNSIGNED NOT NULL , `type` VARCHAR(20) NOT NULL , `datalocation` VARCHAR(2000) NOT NULL , INDEX (`id`), UNIQUE(`name`), INDEX(`projectid`), INDEX (`type`), INDEX (`datasetid`)) ENGINE = InnoDB;');
Log::info("...adding foreign keys...");
database::query('ALTER TABLE `datasetlocation` ADD FOREIGN KEY (`datasetid`) REFERENCES `dataset`(`id`) ON DELETE CASCADE ON UPDATE CASCADE; ');
database::query('ALTER TABLE `datasetlocation` ADD FOREIGN KEY (`id`) REFERENCES `baseobject`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;');
database::query('ALTER TABLE `datasetlocation` ADD FOREIGN KEY (`projectid`) REFERENCES `project`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;');
Log::info("...done");

Log::info('Migrating dataset.datalocation column to new datasetlocation table...');
$rows=database::queryGetAll('SELECT id,projectid,datalocation FROM `dataset` WHERE datalocation IS NOT NULL AND `datalocation`<>""');
if($rows['rows']){ $rows=$rows['rows']; }
if(!empty($rows)){
	Log::debug(' Found '.count($rows).' datasets with non-empty location');
	foreach($rows as $row){
		Log::debug(' - creating datasetlocation record for dataset '.$row['id']);
		datasetlocation::create([
			'type'=>'Original',
			'datasetid'=>$row['id'],
			'datalocation'=>$row['datalocation']
		]);
		Log::debug('   ...done. Location '.$row['datalocation']);
	}
}
Log::info('...done');
Log::info('dataset.datalocation column and contents have been left in place. They will be removed in a future update.');

Log::info("Adding collecteddatetime column to dataset table...");
database::query('ALTER TABLE `dataset` ADD `collecteddatetime` DATETIME NULL DEFAULT NULL AFTER `pdbdepositionid`, ADD INDEX (`collecteddatetime`);');
Log::info('...done');

Log::info("Populating dataset.collecteddatetime from cached synchrotron data objects...");
$like='"%,\"STA\":%"';
$rows=database::queryGetAll('SELECT id,remotedatasetobject FROM dataset WHERE remotedatasetobject LIKE '.$like);
if($rows['rows']){ $rows=$rows['rows']; }
if(!empty($rows)) {
	Log::debug('Found '.count($rows).' datasets with a STA datetime field (probably a SynchWeb synchrotron)');
	foreach ($rows as $row) {
		$obj=json_decode($row['remotedatasetobject'], true);
		if(is_array($obj) && isset($obj['STA']) && preg_match('/^\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d$/', $obj['STA'])){
			database::query(
				'UPDATE dataset SET collecteddatetime=:dt WHERE id=:id',
				[':dt'=>$obj['STA'], ':id'=>$row['id']]
			);
		}
	}
}
$like='"%,\"DataCollection_startTime\":%"';
$rows=database::queryGetAll('SELECT id,remotedatasetobject FROM dataset WHERE remotedatasetobject LIKE '.$like);
if($rows['rows']){ $rows=$rows['rows']; }
if(!empty($rows)){
	Log::debug('Found '.count($rows).' datasets with a DataCollection_startTime datetime field (probably an EXI synchrotron)');
	foreach($rows as $row){
		$obj=json_decode($row['remotedatasetobject'], true);
		if(is_array($obj) && isset($obj['DataCollection_startTime'])){
			$timestamp=strtotime($obj['DataCollection_startTime']);
			$mysqlDate = date("Y-m-d H:i:s",$timestamp);
			database::query(
				'UPDATE dataset SET collecteddatetime=:dt WHERE id=:id',
				[':dt'=>$mysqlDate, ':id'=>$row['id']]
			);
		}
	}
}
Log::info('...done');

Log::info('Datafix: Some EXI synchrotrons returned no beamline manufacturer/model, causing them to appear as undefined.');
foreach (['detectormanufacturer','detectormodel','detectormode','detectortype'] as $column){
	Log::debug('Clearing undefined entries for dataset.'.$column);
	database::query('UPDATE dataset SET '.$column.'="" WHERE '.$column.'="undefined"');
}
Log::info('...done');


Log::info("Adding backgroundimage table...");
database::query('CREATE TABLE `backgroundimage` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT , 
    `userid` BIGINT UNSIGNED NULL , 
    `mimetype` CHAR(30) NOT NULL , 
    PRIMARY KEY (`id`), 
    INDEX (`userid`))
    ENGINE = InnoDB');
Log::info("...adding foreign key...");
database::query('ALTER TABLE `backgroundimage` ADD FOREIGN KEY (`userid`) REFERENCES `user`(`id`) ON DELETE CASCADE ON UPDATE CASCADE');
Log::info("...done");

Log::info("Adding config item core_backgroundimage_default...");
$item=config::get('core_backgroundimage_default');
if(is_null($item)){
	database::query("INSERT INTO `config` (`name`, `description`, `type`, `defaultvalue`, `value`) VALUES ('core_backgroundimage_default', 'Default background image ID', 'int', '0', '0');");
	Log::info("...done");
} else {
	Log::info("...already exists");
}

Log::info("Adding config item core_backgroundimage_allow...");
$item=config::get('core_backgroundimage_allow');
if(is_null($item)){
	database::query("INSERT INTO `config` (`name`, `description`, `type`, `enumvalues`, `minimum`, `maximum`, `defaultvalue`, `value`) VALUES ('core_backgroundimage_allow', 'Allow users to set a background image', 'enum', \"'fixed','select','upload'\", NULL, NULL, 'upload', 'upload');");
	Log::info("...done");
} else {
	Log::info("...already exists");
}

Log::info("...update done");
