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

$tables=array();
$result=database::queryGetAll('SHOW TABLES');
foreach($result['rows'] as $row){
    foreach($row as $k=>$v){
        $tables[]=$v;
    }
}

/*
if(in_array('testtable', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "Test table testtable already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating test table testtable...");
    database::query("
        CREATE TABLE IF NOT EXISTS testtable (
        id bigint(20) unsigned NOT NULL,
        name varchar(250) COLLATE utf8_bin NOT NULL,
        UNIQUE KEY id (id),
        UNIQUE KEY name (name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Test table';
    ");    
    Log::write(Log::LOGLEVEL_INFO, "Created test table.");
}
*/

database::addOrAlterColumn('shipment', 'idatremotefacility', 'VARCHAR(100) NULL');
database::addOrAlterColumn('shipment','urlatremotefacility','VARCHAR(250) NULL'); 

database::addOrAlterColumn('diffractionrequest', 'crystalidatremotefacility', 'VARCHAR(100) NULL');
database::addOrAlterColumn('diffractionrequest','crystalurlatremotefacility','VARCHAR(250) NULL');

Log::write(Log::LOGLEVEL_INFO, "Plate: Changing isdestroyed to datedestroyed");
Log::write(Log::LOGLEVEL_DEBUG, "- Checking table structure");
$cols=database::queryGetAll('SHOW COLUMNS IN plate');
$cols=array_column($cols['rows'], 'Field');
if(in_array('datedestroyed', $cols)){
    Log::write(Log::LOGLEVEL_WARN, "Column datedestroyed already exists in plate table");
} else if(!in_array('isdestroyed',$cols)){
    Log::write(Log::LOGLEVEL_WARN, "Column isdestroyed does not exist in plate table.");
    Log::write(Log::LOGLEVEL_WARN, "Column isdestroyed has not been updated to datedestroyed either.");
    database::query('ALTER TABLE plate ADD datedestroyed DATE NULL DEFAULT NULL');
} else {
    Log::write(Log::LOGLEVEL_INFO, "- retrieving list of destroyed plates");
    $destroyedPlates=database::queryGetAll('SELECT id FROM plate WHERE isdestroyed=1');
    Log::write(Log::LOGLEVEL_INFO, "- altering table");
    database::query('ALTER TABLE plate CHANGE isdestroyed datedestroyed DATE NULL DEFAULT NULL');
    if(empty($destroyedPlates)){
        Log::write(Log::LOGLEVEL_INFO, "- No destroyed plates, nothing more to do");
    } else {
        Log::write(Log::LOGLEVEL_INFO, "- Found destroyed plates, setting their destroyed date to today");
        $plateIds=array_column($destroyedPlates['rows'], 'id');
        $today=date('Y-m-d');
        database::query(
            'UPDATE plate SET datedestroyed=:date WHERE ID IN('.implode(',',$plateIds).')', 
            array(':date'=>$today)
        );
    }
    Log::write(Log::LOGLEVEL_INFO, "- finished");
}

/*
 * Make the "Plate destroyers" usergroup, if it doesn't exist.
 */
$groupName=usergroup::PLATEDESTROYERS;
Log::write(Log::LOGLEVEL_INFO, "Creating system usergroup ".$groupName."...");
try {
    $existing=usergroup::getByName($groupName);
    Log::write(Log::LOGLEVEL_INFO, "...already exists.");
} catch(NotFoundException $e){
    usergroup::create(array(
        'name'=>$groupName,
        'description'=>'Members can mark plates as destroyed, even if they cannot see them',
        'issystem'=>1
    ));    
    Log::write(Log::LOGLEVEL_INFO, "...done.");
}

/*
 * Plate re-import functionality
 */
if(in_array('platereimportqueue', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "Table platereimportqueue already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating table platereimportqueue...");
    database::query(
        "CREATE TABLE IF NOT EXISTS platereimportqueue (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        plateid bigint(20) unsigned NOT NULL,
        queuedby bigint(20) unsigned NOT NULL,
        queuedtime datetime NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY plateid (plateid),
        KEY queuedby (queuedby),
        KEY queuedtime (queuedtime)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Queue of plates to re-import' AUTO_INCREMENT=1"
	);
    database::query(
        'ALTER TABLE platereimportqueue ADD FOREIGN KEY (plateid) REFERENCES plate(id) ON DELETE CASCADE ON UPDATE CASCADE;'
	);
    database::query(
        'ALTER TABLE platereimportqueue ADD FOREIGN KEY (queuedby) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE;'
	);
    Log::write(Log::LOGLEVEL_INFO, "...done.");
    Log::write(Log::LOGLEVEL_INFO, "Creating config items for plate reimport queue...");
    database::query(
        "INSERT INTO config (name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES
             ('reimport_enabled', 'Enable plate re-import (importer must support this)', 'boolean', NULL, NULL, NULL, '0', '0'),
             ('reimport_ownerscanqueue', 'Whether plate owners can queue their plates for re-import', 'boolean', NULL, NULL, NULL, '0', '0'),
             ('reimport_quota', 'The maximum number of plates a user can queue for import', 'int', NULL, '0', NULL, '10', '10'); "
	);
    Log::write(Log::LOGLEVEL_INFO, "...done.");
}

Log::write(Log::LOGLEVEL_INFO, "Update script completed.");
