<?php
/**
 * Upgrades the database from version 1.7.2 to version 1.8.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::write(Log::LOGLEVEL_INFO, "In updater, from 1.7.2 to 1.8.0");


Log::write(Log::LOGLEVEL_INFO, "Adding foreign key to dataset.beamlineid...");
database::query("ALTER TABLE `dataset` ADD INDEX (`beamlineid`);");
database::query("ALTER TABLE `dataset` ADD FOREIGN KEY (`beamlineid`) REFERENCES `beamline`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;");
Log::write(Log::LOGLEVEL_INFO, "...done.");

Log::write(Log::LOGLEVEL_INFO, "Fixing some crystal remote URLs...");
// OLD: https://ispyb.maxiv.lu.se/ispyb/ispyb-ws/rest//samples/sid/45810
// NEW: https://exi.maxiv.lu.se/mx/index.html#/mx/datacollection/sample/45810/main
$sql='SELECT id, shipmentid, crystalidatremotefacility,crystalurlatremotefacility FROM diffractionrequest WHERE crystalurlatremotefacility LIKE "%/rest//%"';
$crystals=database::queryGetAll($sql);
while(!empty($crystals)){
    foreach ($crystals['rows'] as $crystal){
        $shipment=shipment::getById($crystal['shipmentid']);
        $shipmentDestination=shipmentdestination::getById($shipment['shipmentdestinationid']);
        $proposal=$shipment['proposalname'];
        $newRemoteUrl=rtrim($shipmentDestination['baseuri'], '/');
        $newRemoteUrl=str_replace('//ispyb.','//exi.', $newRemoteUrl);
        $newRemoteUrl=str_replace('/ispyb/ispyb-ws/rest','/mx/index.html#', $newRemoteUrl);
        $newRemoteUrl.='/mx/datacollection/sample/'.$crystal['crystalidatremotefacility'].'/main';
        $stmt='UPDATE diffractionrequest SET crystalurlatremotefacility=:url WHERE id=:id';
        database::query($stmt, array(
            ':url'=>$newRemoteUrl,
            ':id'=>$crystal['id']
        ));
    }
    $crystals=database::queryGetAll($sql);
}
Log::write(Log::LOGLEVEL_INFO, "...done.");


Log::write(Log::LOGLEVEL_INFO, "Checking for Lightbug config items...");
$existing=config::get('tracker_lightbug_username');
if(is_null($existing)){
    Log::write(Log::LOGLEVEL_INFO, "Lightbug config items do not exist. Creating...");
    database::query("INSERT INTO `config` 
        (`name`, `description`, `type`, `enumvalues`, `minimum`, `maximum`, `defaultvalue`, `value`) VALUES 
        ('tracker_lightbug_username', 'Lightbug account username', 'text', NULL, NULL, NULL, '', ''), 
        ('tracker_lightbug_password', 'Lightbug account password', 'text', NULL, NULL, NULL, '', '')
    ");
    Log::write(Log::LOGLEVEL_INFO, "...done.");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Lightbug config items already exist.");
}

Log::write(Log::LOGLEVEL_INFO, "Adding home and country code columns to shipmentdestination...");
database::addOrAlterColumn('shipmentdestination','ishome','BOOLEAN NOT NULL DEFAULT FALSE AFTER isavailable','If true, this is the home lab. Trackers entering/leaving this zone may trigger special actions.');
database::addOrAlterColumn('shipmentdestination','countrycode','VARCHAR(10) NULL AFTER projectid');

database::addOrAlterColumn('shipmentdestination','baseuri','VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NULL');


$home=shipmentdestination::getByName('Home');
if(!$home){
    $home=shipmentdestination::create(array(
        'name'=>'Home',
        'isavailable'=>false,
        'ishome'=>true,
        'shipmenthandler'=>'DefaultShipmentHandler',
        'baseuri'=>'https://icebear.fi'
    ))['created'];
}
shipmentdestination::update($home['id'], array('ishome'=>true));
Log::write(Log::LOGLEVEL_INFO, "...done.");

Log::write(Log::LOGLEVEL_INFO, "Altering scoreofdropimage to support automatic scoring, etc.");
database::addOrAlterColumn('scoreofdropimage','crystalscoreid','BIGINT(11) UNSIGNED NULL');
database::addOrAlterColumn('scoreofdropimage','crystalprobabilitypercent','FLOAT UNSIGNED NULL AFTER crystalscoreid');
database::addOrAlterColumn('scoreofdropimage','humanscorerid','BIGINT UNSIGNED NULL AFTER scoreddatetime');
database::addOrAlterColumn('scoreofdropimage','scoringservicename','VARCHAR(50) NULL AFTER humanscorerid');
database::addOrAlterColumn('scoreofdropimage','scoringenginename','VARCHAR(50) NULL AFTER scoringservicename');

database::query("ALTER TABLE `scoreofdropimage` ADD INDEX (`humanscorerid`);");
database::query("ALTER TABLE `scoreofdropimage` ADD FOREIGN KEY (`humanscorerid`) REFERENCES `user`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;");
Log::write(Log::LOGLEVEL_INFO, "...done.");

Log::write(Log::LOGLEVEL_INFO, "Altering dropimage to support automatic scoring, etc.");
database::addOrAlterColumn('dropimage','latestcrystalprobabilitypercent','FLOAT UNSIGNED NULL AFTER latestcrystalscoreid');
Log::write(Log::LOGLEVEL_INFO, "...done.");

Log::write(Log::LOGLEVEL_INFO, "Altering plate to add best probability column.");
database::addOrAlterColumn('plate','bestprobability','FLOAT NULL  AFTER bestcrystalscoreid','Best probability of crystal for all drops');
Log::write(Log::LOGLEVEL_INFO, "...done.");

Log::write(Log::LOGLEVEL_INFO, "Extending username column to 100 characters...");
database::addOrAlterColumn('user','name','VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL');
Log::write(Log::LOGLEVEL_INFO, "...done.");


Log::write(Log::LOGLEVEL_INFO, "Adding columns to autoprocessingresult...");
database::addOrAlterColumn('autoprocessingresult','completeness','FLOAT UNSIGNED NULL AFTER spacegroup');
database::addOrAlterColumn('autoprocessingresult','bestresolution','FLOAT UNSIGNED NULL AFTER completeness');
Log::write(Log::LOGLEVEL_INFO, "...done.");

Log::write(Log::LOGLEVEL_INFO, "Populating new columns in autoprocessingresult...");
database::query('UPDATE autoprocessingresultparameter,autoprocessingresult SET autoprocessingresult.completeness=autoprocessingresultparameter.parametervalue
WHERE autoprocessingresultparameter.autoprocessingresultid=autoprocessingresult.id AND autoprocessingresultparameter.parametername="completeness_overall";');
database::query('UPDATE autoprocessingresultparameter,autoprocessingresult SET autoprocessingresult.bestresolution=autoprocessingresultparameter.parametervalue
WHERE autoprocessingresultparameter.autoprocessingresultid=autoprocessingresult.id AND autoprocessingresultparameter.parametername="resolutionLimitHigh_overall";');
Log::write(Log::LOGLEVEL_INFO, "...done.");




Log::write(Log::LOGLEVEL_INFO, "...update done");

