<?php
/**
 * Upgrades the database from version 1.8.1 to version 1.8.2
 * 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.8.1 to 1.8.2...");


Log::write(Log::LOGLEVEL_INFO, "Adding isinuse column to imager...");
database::addOrAlterColumn('imager','isinuse','BOOLEAN NOT NULL DEFAULT TRUE');
Log::write(Log::LOGLEVEL_INFO, "...done");


Log::write(Log::LOGLEVEL_INFO, "Renaming shipmentdestination.baseuri to apibaseuri...");
if(empty(database::queryGetAll('SHOW COLUMNS in shipmentdestination LIKE "apibaseuri"'))) {
    database::query('ALTER TABLE `shipmentdestination` CHANGE `baseuri` `apibaseuri` VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NULL');
}
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Adding shipmentdestination.clientbaseuri column...");
database::addOrAlterColumn('shipmentdestination','clientbaseuri','VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin NULL');
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Adding welldrop columns latestcrystalscoreid and latestcrystalprobabilitypercent...");
database::addOrAlterColumn('welldrop','latestcrystalscoreid','BIGINT UNSIGNED NULL');
database::addOrAlterColumn('welldrop','latestcrystalprobabilitypercent','FLOAT UNSIGNED NULL');
Log::write(Log::LOGLEVEL_INFO, "...done");


Log::write(Log::LOGLEVEL_INFO, "Datafix, force-score all images with marked crystals as Crystals...");
$imagesWithCrystals=database::queryGetAll('SELECT DISTINCT c.dropimageid, di.latestcrystalscoreid, di.welldropid, wd.platewellid, pw.plateid, p.crystalscoringsystemid
    FROM crystal as c, dropimage AS di, welldrop AS wd, platewell AS pw, plate AS p
    WHERE di.welldropid=wd.id AND c.dropimageid=di.id AND wd.platewellid=pw.id AND pw.plateid=p.id'
);
if(empty($imagesWithCrystals)){
    Log::write(Log::LOGLEVEL_INFO, "No images have marked crystals, nothing to fix");
} else {
    if(isset($imagesWithCrystals['rows'])){
        $imagesWithCrystals=$imagesWithCrystals['rows'];
    }
    Log::write(Log::LOGLEVEL_DEBUG, count($imagesWithCrystals)." images have marked crystals");
    $crystalScoreIds=[]; // The "crystals" score ID, indexed by "systemNNN" where NNN is the crystal scoring system ID
    $forceScoredCount=0;
    $alreadyCrystalsCount=0;
    foreach ($imagesWithCrystals as $crystalImage){
        if(!isset($crystalScoreIds['system'.$crystalImage['crystalscoringsystemid']])){
            $crystalScore=crystalscoringsystem::getScoreBestMatchingName($crystalImage['crystalscoringsystemid'],'crystals');
            if(!$crystalScore){
                //There is no "Crystals" score, so can't do anything with this image (or plate, or probably any plate)
                Log::warning('Could not find a suitable "Crystals" score for scoring system '.$crystalImage['crystalscoringsystemid']);
                continue;
            }
            Log::debug('"Crystals" score for scoring system ID '.$crystalImage['crystalscoringsystemid'].' is '.$crystalScore['id'].' ('.$crystalScore['label'].')');
            $crystalScoreIds['system'.$crystalImage['crystalscoringsystemid']]=$crystalScore['id'];
        }
        $crystalScoreId=$crystalScoreIds['system'.$crystalImage['crystalscoringsystemid']];
        if($crystalScoreId!==$crystalImage['latestcrystalscoreid']){
            dropimage::update($crystalImage['dropimageid'], array(
                'latestcrystalscoreid'=>$crystalScoreId,
                'scoringservicename'=>scoreofdropimage::SCORING_SERVICE_NAME_ICEBEAR,
                'scoringenginename'=>scoreofdropimage::SCORING_ENGINE_CRYSTAL_MARKED
            ));
            $forceScoredCount++;
        } else {
            $alreadyCrystalsCount++;
        }
    }
    Log::write(Log::LOGLEVEL_DEBUG, $alreadyCrystalsCount." images force-scored as Crystals");
    Log::write(Log::LOGLEVEL_DEBUG, $forceScoredCount." images already scored as Crystals");
}
Log::write(Log::LOGLEVEL_INFO, "...done, scored all images with marked crystals as Crystals");


Log::write(Log::LOGLEVEL_INFO, "Datafix, recalculating dropimage and welldrop latest scores...");
$offset=0;
$limit=2500;
Log::write(Log::LOGLEVEL_INFO, "Working in batches of $limit");
$sql='SELECT DISTINCT welldropid from dropimage WHERE latestcrystalscoreid IS NOT NULL';
$dropsWithScores=database::queryGetAll($sql.' LIMIT '.$offset.','.$limit);
$plateIds=[];
while (!empty($dropsWithScores)){
    if(isset($dropsWithScores['rows'])){ $dropsWithScores=$dropsWithScores['rows']; }
    Log::debug("Processing ".count($dropsWithScores)." welldrops");
    foreach ($dropsWithScores as $wellDrop){
        $wellDropId=$wellDrop['welldropid'];
        $latestScore=database::queryGetOne('SELECT scoreofdropimage.crystalscoreid AS crystalscoreid, dropimage.id AS dropimageid 
            FROM scoreofdropimage, dropimage, imagingsession 
            WHERE scoreofdropimage.dropimageid=dropimage.id 
                AND dropimage.imagingsessionid=imagingsession.id 
                AND scoreofdropimage.crystalscoreid IS NOT NULL 
                AND dropimage.welldropid=:welldropid 
            ORDER BY imagingsession.imageddatetime DESC, scoreofdropimage.scoreddatetime DESC',
            array(':welldropid'=>$wellDropId)
        );
        if(!$latestScore){
            continue;
        }
        database::query(
            'UPDATE dropimage set latestcrystalscoreid=:scoreid WHERE id=:dropimageid',
            array(':scoreid'=>$latestScore['crystalscoreid'], ':dropimageid'=>$latestScore['dropimageid'])
        );
        database::query(
            'UPDATE welldrop set latestcrystalscoreid=:scoreid WHERE id=:welldropid',
            array(':scoreid'=>$latestScore['crystalscoreid'], ':welldropid'=>$wellDropId)
        );
        $plate=database::queryGetOne('
            SELECT platewell.plateid AS plateid FROM welldrop,platewell
                WHERE welldrop.platewellid=platewell.id AND welldrop.id=:welldropid',
            array(':welldropid'=>$wellDropId)
        );
        $plateIds[]=$plate['plateid'];
    }
    $offset+=$limit;
    Log::info("Checking for more scored drops (offset $offset, limit $limit)");
    $dropsWithScores=database::queryGetAll($sql.' LIMIT '.$offset.','.$limit);
}
Log::write(Log::LOGLEVEL_INFO, "Finished recalculating dropimage and welldrop latest scores.");

if(!empty($plateIds)){
    $plateIds=array_unique($plateIds);
    Log::write(Log::LOGLEVEL_INFO, "Datafix, recalculating plate best scores on ".count($plateIds)." plates...");
    foreach ($plateIds as $plateId) {
        plate::calculateBestScores($plateId);
    }
    Log::write(Log::LOGLEVEL_INFO, "...done, plate best scores recalculated");
}

Log::write(Log::LOGLEVEL_INFO, "...Datafix complete");

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

