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

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

//Plate table
Log::write(Log::LOGLEVEL_INFO, "Adding isdestroyed column to plate table...");
database::addOrAlterColumn('plate','isdestroyed','BOOLEAN NOT NULL DEFAULT FALSE');

//Shipment destination
Log::write(Log::LOGLEVEL_INFO, "Changing shipmentdestination columns...");
Log::write(Log::LOGLEVEL_INFO, "Dropping unused columns...");
database::dropColumnIfExists('shipmentdestination','interfaceclass');
database::dropColumnIfExists('shipmentdestination','shipmentsubmiturl');
database::dropColumnIfExists('shipmentdestination','authenticateurl');
Log::write(Log::LOGLEVEL_INFO, "Adding new columns...");
database::addOrAlterColumn('shipmentdestination','shipmenthandler','VARCHAR(100) NOT NULL','The Javascript handler object - see js/shipping/handlers');
database::addOrAlterColumn('shipmentdestination','baseuri','VARCHAR(100) NOT NULL','Base URI of the shipping API');

if(in_array('diffractionrequest', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "diffractionrequest table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating diffractionrequest table...");
    database::query("
        CREATE TABLE IF NOT EXISTS `diffractionrequest` (
        `id` bigint(20) unsigned NOT NULL,
        `name` varchar(250) COLLATE utf8_bin NOT NULL,
        `projectid` bigint(20) unsigned NOT NULL,
        `crystalid` bigint(20) unsigned NOT NULL,
        `shipmentid` bigint(20) unsigned DEFAULT NULL,
        `diffractiontype` varchar(10) COLLATE utf8_bin DEFAULT NULL COMMENT 'OSC/SAD/MAD',
        `observedresolution` float DEFAULT NULL COMMENT 'In Angstroms',
        `requiredresolution` float DEFAULT NULL COMMENT 'In Angstroms',
        `minimumresolution` float DEFAULT NULL COMMENT 'In Angstroms',
        `comment` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
        UNIQUE KEY `id` (`id`),
        UNIQUE KEY `name` (`name`),
        KEY `projectid` (`projectid`),
        KEY `crystalid` (`crystalid`),
        KEY `shipmentid` (`shipmentid`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Describes how a crystal should be shot';
    ");    
    database::query("ALTER TABLE `diffractionrequest`
        ADD CONSTRAINT `diffractionrequest_ibfk_1` FOREIGN KEY (`id`) REFERENCES `baseobject` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        ADD CONSTRAINT `diffractionrequest_ibfk_2` FOREIGN KEY (`projectid`) REFERENCES `project` (`id`),
        ADD CONSTRAINT `diffractionrequest_ibfk_3` FOREIGN KEY (`crystalid`) REFERENCES `crystal` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
        ADD CONSTRAINT `diffractionrequest_ibfk_4` FOREIGN KEY (`shipmentid`) REFERENCES `shipment` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
    ");
}

//User config table
if(in_array('userconfig', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "userconfig table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating userconfig table...");
    database::query("
        CREATE TABLE IF NOT EXISTS userconfig (
                userid bigint(20) unsigned NOT NULL,
                `name` varchar(100) COLLATE utf8_bin NOT NULL,
                `value` varchar(250) COLLATE utf8_bin NOT NULL,
                UNIQUE KEY userid_propname (userid,`name`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 
    ");
    database::query(" 
        ALTER TABLE userconfig
        ADD CONSTRAINT userconfig_ibfk_1 FOREIGN KEY (userid) REFERENCES `user` (id) ON DELETE CASCADE ON UPDATE CASCADE; 
    ");
}

/* Some bricks failed to render on new install due to database error. headertemplates and rowtemplates could nt be empty. */
database::addOrAlterColumn('homepagebrick', 'headertemplates', 'TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL');
database::addOrAlterColumn('homepagebrick', 'rowtemplates', 'TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL');

/* Some missing columns in containercontent */
database::addOrAlterColumn('containercontent', 'name', 'VARCHAR(100) NOT NULL DEFAULT ""');
database::addOrAlterColumn('containercontent', 'projectid', 'BIGINT(20) UNSIGNED NOT NULL','Always Shared project ID');

/* Add constraint if not exists - HEL update failed due to duplicate */
$result=database::queryGetAll('SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, 
        REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
        WHERE TABLE_NAME="containercontent" AND REFERENCED_TABLE_NAME="project" AND REFERENCED_COLUMN_NAME="id"');
if(!$result){
    database::query('
        ALTER TABLE containercontent ADD CONSTRAINT containercontent_projfk FOREIGN KEY(projectid) REFERENCES project(id)
    ');
}


//Updater - base URI
if(null===config::get('update_baseuri')){
    database::query('
    INSERT INTO config(name,description,defaultvalue,value) VALUES
    ("update_baseuri","Base URI for updates, etc.","https://www.icebear.fi/","https://www.icebear.fi/")
    ON DUPLICATE KEY UPDATE name=name;
');

}

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