<?php
/**
 * Upgrades the database from version 1.6.0 to version 1.7.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.
 */

//In importer, if config item does not exist, create it and set it TRUE.
//Warn in update that this has been done.

Log::write(Log::LOGLEVEL_INFO, "In updater, from 1.6.0 to 1.7.0");

Log::write(Log::LOGLEVEL_INFO, "Adding height and width columns to homepageuserbrick");
database::query("ALTER TABLE `homepageuserbrick` 
    ADD `height` INT UNSIGNED NOT NULL DEFAULT '1' AFTER `col`, 
    ADD `width` INT UNSIGNED NOT NULL DEFAULT '1' AFTER `height`;
");
Log::write(Log::LOGLEVEL_INFO, "...done");
Log::write(Log::LOGLEVEL_INFO, "Adding height and width columns to homepagedefaultbrick");
database::query("ALTER TABLE `homepagedefaultbrick` 
    ADD `height` INT UNSIGNED NOT NULL DEFAULT '1' AFTER `col`, 
    ADD `width` INT UNSIGNED NOT NULL DEFAULT '1' AFTER `height`;
");
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Setting user homepage brick height/widths to initial brick dimensions");
database::query("UPDATE homepageuserbrick,homepagebrick
SET homepageuserbrick.height=homepagebrick.height, homepageuserbrick.width=homepagebrick.width
WHERE homepagebrick.id=homepageuserbrick.homepagebrickid
");
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Setting default homepage brick height/widths to initial brick dimensions");
database::query("UPDATE homepagedefaultbrick,homepagebrick
SET homepagedefaultbrick.height=homepagebrick.height, homepagedefaultbrick.width=homepagebrick.width
WHERE homepagebrick.id=homepagedefaultbrick.homepagebrickid
");
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Creating table for autoprocessing results");
database::query("CREATE TABLE autoprocessingresult ( 
    `id` BIGINT UNSIGNED NOT NULL , 
    `name` VARCHAR(250) NOT NULL , 
    `projectid` BIGINT UNSIGNED NOT NULL , 
    `datasetid` BIGINT UNSIGNED NOT NULL , 
    `pipelinename` VARCHAR(100) NOT NULL , 
    `succeeded` BOOLEAN NOT NULL , 
    `scalingstatisticstype` VARCHAR(100) NOT NULL, 
    INDEX (`datasetid`), INDEX (`projectid`), UNIQUE (`name`)
    ) ENGINE = InnoDB;");
database::query("ALTER TABLE `autoprocessingresult` ADD `remoteobject` TEXT NULL AFTER `scalingstatisticstype`;");
database::query("ALTER TABLE `autoprocessingresult` ADD FOREIGN KEY (`id`) REFERENCES `baseobject`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `autoprocessingresult` ADD FOREIGN KEY (`projectid`) REFERENCES `project`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `autoprocessingresult` ADD FOREIGN KEY (`datasetid`) REFERENCES `dataset`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query('ALTER TABLE `autoprocessingresult` 
    ADD `spacegroup` VARCHAR(20) NULL AFTER `scalingstatisticstype`, 
    ADD `rpim` FLOAT NULL AFTER `spacegroup`, 
    ADD `rmerge` FLOAT NULL AFTER `rpim`, 
    ADD `ioversigma` FLOAT NULL AFTER `rmerge`, 
    ADD `cchalf` FLOAT NULL AFTER `ioversigma`;
');
database::query("ALTER TABLE `autoprocessingresult` 
    ADD `isanomalous` TINYINT NOT NULL DEFAULT 0 AFTER `succeeded`;
");
Log::write(Log::LOGLEVEL_INFO, "...done");


Log::write(Log::LOGLEVEL_INFO, "Creating table for autoprocessing result parameters");
database::query("CREATE TABLE autoprocessingresultparameter ( 
    `id` BIGINT UNSIGNED NOT NULL , 
    `name` VARCHAR(250) NOT NULL , 
    `projectid` BIGINT UNSIGNED NOT NULL , 
    `autoprocessingresultid` BIGINT UNSIGNED NOT NULL , 
    `parametername` VARCHAR(100) NOT NULL , 
    `parametervalue` VARCHAR(250) NOT NULL , 
    INDEX (`projectid`), INDEX (`autoprocessingresultid`), UNIQUE (`name`)
    ) ENGINE = InnoDB;");
database::query("ALTER TABLE `autoprocessingresultparameter` ADD FOREIGN KEY (`id`) REFERENCES `baseobject`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `autoprocessingresultparameter` ADD FOREIGN KEY (`projectid`) REFERENCES `project`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
database::query("ALTER TABLE `autoprocessingresultparameter` ADD FOREIGN KEY (`autoprocessingresultid`) REFERENCES `autoprocessingresult`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;");
Log::write(Log::LOGLEVEL_INFO, "...done");

Log::write(Log::LOGLEVEL_INFO, "Simplifying permissions");
//Before this version, we could set read/create/update/delete permissions independently.
//Now, the API will enforce that setting create, update or delete will set all four. Valid
//permissions are (none), (read), (read,create,update,delete). (none) and (read) are unchanged,
//but we need to force any project/usergroup combinations with create, read, or delete to have
//full (read,create,update,delete).
//Strategy:
//1. Find all project/usergroup combinations for each of create/update/delete (NOT read)
//2. For each project/usergroup combination, determine existing permissions
//3. Compare existing permissions against full permissions (read/create/update/delete), creating as required
//Update can only be run by administrators, so all projects/groups will be visible.
$permissionTypes=['create','update','delete'];
foreach ($permissionTypes as $permissionType){
    //First find all permissions of the relevant type
    $permissions=database::queryGetAll(
        'SELECT projectid, usergroupid FROM permission WHERE type=:type',
        array(':type'=>$permissionType)
    );
    if(!empty($permissions)){
        $permissions=$permissions['rows'];
        foreach($permissions as $p){
            // For every project/usergroup combination with this permission type, find all existing permissions for
            // that project/usergroup combination
            $existing=database::queryGetAll(
                'SELECT type FROM permission WHERE usergroupid=:usergroupid AND projectid=:projectid',
                array(':usergroupid'=>$p['usergroupid'], ':projectid'=>$p['projectid'])
            );
            $existing=$existing['rows'];
            $existing=array_column($existing,'type');
            $fullPermissions=['read','create','update','delete'];
            foreach($fullPermissions as $needed){
                //For each of the full permissions set, if it is not in the existing set, create it
                if(!in_array($needed, $existing)){
                    database::query(
                        'INSERT INTO permission(usergroupid, projectid, type) VALUES(:usergroupid, :projectid, :type)',
                        array(':usergroupid'=>$p['usergroupid'], ':projectid'=>$p['projectid'], ':type'=>$needed)
                    );
                }
            }
        }
    }
}

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

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

