<?php 
/**
 * Upgrades the database from version 1.0 to version 1.1.
 * 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;
    }
}

/**
 * Create new tables
 */
if(in_array('shipmentdestination', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "shipmentdestination table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating shipmentdestination table...");
    database::query(" CREATE TABLE shipmentdestination (
    	  id bigint(20) unsigned NOT NULL,
    	  name varchar(250) COLLATE utf8_bin NOT NULL,
    	  projectid bigint(20) unsigned NOT NULL,
          interfaceclass VARCHAR(100) NOT NULL COMMENT 'The name of the IceBear class that handles interactions with this destination' , 
          shipmentsubmiturl VARCHAR(250) NOT NULL COMMENT 'THe remote URL to which shipments should be posted' , 
          authenticateurl VARCHAR(250) NOT NULL COMMENT 'The remote URL to authenticate against',
    	  UNIQUE KEY name (name),
    	  UNIQUE KEY id (id),
    	  KEY projectid (projectid)
    	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Describes synchrotrons and other shipment destinations';
    ");
    database::query(" ALTER TABLE shipmentdestination
    	  ADD CONSTRAINT shipmentdestination_ibfk_2 FOREIGN KEY (projectid) REFERENCES project (id),
    	  ADD CONSTRAINT shipmentdestination_ibfk_1 FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE;
    ");
}

if(in_array('shipment', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "shipment table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating shipment table...");
    database::query(" CREATE TABLE shipment (
    	  id bigint(20) unsigned NOT NULL,
    	  name varchar(250) COLLATE utf8_bin NOT NULL,
    	  projectid bigint(20) unsigned NOT NULL,
    	  shipperid bigint(20) unsigned NOT NULL,
    	  shipmentdestinationid bigint(20) unsigned NOT NULL,
    	  dateshipped date DEFAULT NULL,
    	  datereturned date DEFAULT NULL,
          manifest TEXT NULL COMMENT 'Cache of containercontents::getcontainers() at ship time',
          requestbody TEXT NULL COMMENT 'The raw shipment data sent to the Xray source' , 
          responsebody TEXT NULL COMMENT 'The raw response received from the Xray source',
    	  UNIQUE KEY id (id),
    	  UNIQUE KEY name (name),
    	  KEY projectid (projectid),
    	  KEY shipperid (shipperid),
    	  KEY shipmentdestinationid (shipmentdestinationid),
    	  KEY dateshipped (dateshipped),
    	  KEY datereturned (datereturned)
    	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    ");
    database::query(" ALTER TABLE shipment
    	  ADD CONSTRAINT shipment_ibfk_4 FOREIGN KEY (shipmentdestinationid) REFERENCES shipmentdestination (id),
    	  ADD CONSTRAINT shipment_ibfk_1 FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
    	  ADD CONSTRAINT shipment_ibfk_2 FOREIGN KEY (projectid) REFERENCES project (id),
    	  ADD CONSTRAINT shipment_ibfk_3 FOREIGN KEY (shipperid) REFERENCES user (id);
    ");
}

if(in_array('shippingcomment', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "shippingcomment table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating shippingcomment table...");
    database::query(" CREATE TABLE shippingcomment (
    	  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,
    	  text varchar(250) COLLATE utf8_bin NOT NULL,
    	  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;
    ");
    database::query(" ALTER TABLE shippingcomment
    	  ADD CONSTRAINT shippingcomment_ibfk_4 FOREIGN KEY (shipmentid) REFERENCES shipment (id),
    	  ADD CONSTRAINT shippingcomment_ibfk_1 FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
    	  ADD CONSTRAINT shippingcomment_ibfk_2 FOREIGN KEY (projectid) REFERENCES project (id),
    	  ADD CONSTRAINT shippingcomment_ibfk_3 FOREIGN KEY (crystalid) REFERENCES crystal (id);
    ");
}

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 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 (userid,name),
        CONSTRAINT userconfig_ibfk_1 FOREIGN KEY (userid) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin");
}

if(in_array('containercategory', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "containercategory table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating containercategory table...");
    database::query(" CREATE TABLE  containercategory (
    		id bigint(20) unsigned NOT NULL,
    		name varchar(100) COLLATE utf8_bin NOT NULL,
    		projectid bigint(20) unsigned NOT NULL,
    		userscancreate BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Whether regular users can create containers in this category. If false, admin or technician must create.',
    		PRIMARY KEY (id),
    		UNIQUE KEY name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='High-level container description - pin, puck, dewar'; ");
    database::query(" ALTER TABLE containercategory ADD CONSTRAINT containercategory_ibfk_1 FOREIGN KEY (id) REFERENCES baseobject(id) ON DELETE CASCADE ON UPDATE CASCADE; ");
}

if(in_array('containertype', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "containertype table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating containertype table...");
    database::query(" CREATE TABLE containertype (
    		id bigint(20) unsigned NOT NULL,
    		name varchar(100) COLLATE utf8_bin NOT NULL,
    		projectid bigint(20) unsigned NOT NULL,
    		containercategoryid bigint(20) unsigned NOT NULL,
    		positions int(11) unsigned NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ");
    database::query(" ALTER TABLE containertype ADD UNIQUE (id); ");
    database::query(" ALTER TABLE containertype ADD UNIQUE (name); ");
    database::query(" ALTER TABLE containertype ADD INDEX (projectid); ");
    database::query(" ALTER TABLE containertype ADD INDEX (containercategoryid); ");
    database::query(" ALTER TABLE containertype ADD FOREIGN KEY (id) REFERENCES baseobject(id) ON DELETE CASCADE ON UPDATE CASCADE;  ");
    database::query(" ALTER TABLE containertype ADD FOREIGN KEY (projectid) REFERENCES project(id) ON DELETE RESTRICT ON UPDATE RESTRICT;  ");
    database::query(" ALTER TABLE containertype ADD FOREIGN KEY (containercategoryid) REFERENCES containercategory(id) ON DELETE RESTRICT ON UPDATE RESTRICT; ");
}

if(in_array('container', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "container table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating container table...");
    database::query(" CREATE TABLE IF NOT EXISTS container (
    		id bigint(20) unsigned NOT NULL,
    		name varchar(100) COLLATE utf8_bin NOT NULL,
    		projectid bigint(20) unsigned NOT NULL,
    		containertypeid bigint(20) unsigned NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ");
    database::query(" ALTER TABLE container ADD UNIQUE (id); ");
    database::query(" ALTER TABLE container ADD UNIQUE (name); ");
    database::query(" ALTER TABLE container ADD INDEX (projectid); ");
    database::query(" ALTER TABLE container ADD INDEX (containertypeid); ");
    database::query(" ALTER TABLE container ADD FOREIGN KEY (id) REFERENCES baseobject(id) ON DELETE CASCADE ON UPDATE CASCADE; ");
    database::query(" ALTER TABLE container ADD FOREIGN KEY (projectid) REFERENCES project(id) ON DELETE RESTRICT ON UPDATE RESTRICT; ");
    database::query(" ALTER TABLE container ADD FOREIGN KEY (containertypeid) REFERENCES containertype(id) ON DELETE RESTRICT ON UPDATE RESTRICT; ");
}
    
if(in_array('containercontent', $tables)){
    Log::write(Log::LOGLEVEL_INFO, "containercontent table already exists");
} else {
    Log::write(Log::LOGLEVEL_INFO, "Creating containercontent table...");
    database::query(" CREATE TABLE IF NOT EXISTS containercontent (
    	  id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'The unique id of this pairing',
    	  shipmentid bigint(20) unsigned DEFAULT NULL COMMENT 'An associated shipment, i.e., Pin 1 was in puck 3 during this shipment',
    	  parent bigint(20) unsigned NOT NULL COMMENT 'The containing item',
    	  child bigint(20) unsigned NOT NULL COMMENT 'The contained item',
    	  position int(10) unsigned NOT NULL COMMENT 'IF the parent has numbered positions, which contains/contained the child',
    	  iscurrent tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Whether the child is currently in the parent',
    	  UNIQUE KEY id (id),
    	  KEY shipmentid (shipmentid),
    	  KEY parent (parent),
    	  KEY position (position),
    	  KEY iscurrent (iscurrent),
    	  KEY child (child)
    	) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Record of an item having been in a container' AUTO_INCREMENT=1 ;
    ");
    database::query(" ALTER TABLE containercontent
      ADD CONSTRAINT containercontent_ibfk_2 FOREIGN KEY (parent) REFERENCES baseobject (id),
      ADD CONSTRAINT containercontent_ibfk_3 FOREIGN KEY (child) REFERENCES baseobject (id);
    ");
    database::query(" ALTER TABLE containercontent ADD FOREIGN KEY (id) REFERENCES baseobject(id) ON DELETE CASCADE ON UPDATE CASCADE ");
    database::query(" ALTER TABLE containercontent ADD FOREIGN KEY (shipmentid) REFERENCES shipment(id) ON DELETE RESTRICT ON UPDATE RESTRICT ");
    database::query(" ALTER TABLE containercontent ADD FOREIGN KEY (parent) REFERENCES baseobject(id) ON DELETE CASCADE ON UPDATE CASCADE ");
    database::query(" ALTER TABLE containercontent ADD FOREIGN KEY (child) REFERENCES baseobject(id) ON DELETE CASCADE ON UPDATE CASCADE ");
    
}

/**
 * Modifications to existing tables
 */
database::addOrAlterColumn('user','rfid',"VARCHAR(100) NULL AFTER password");
database::addOrAlterColumn('imager','manualimaging','BOOLEAN NOT NULL DEFAULT FALSE','Whether imager can be used for manual plate inspections');
database::addOrAlterColumn('plate','description','VARCHAR(250) NOT NULL DEFAULT "(no description)"');
database::addOrAlterColumn('crystal','isfished','BOOLEAN NOT NULL DEFAULT FALSE','Whether the crystal has been fished onto a pin for data collection');
database::addOrAlterColumn('crystal','isdummycoordinate','BOOLEAN NOT NULL DEFAULT FALSE');
database::addOrAlterColumn('crystal','spacegroup',"VARCHAR(20) NULL ");
database::addOrAlterColumn('crystal','unitcella',"FLOAT UNSIGNED NULL ");
database::addOrAlterColumn('crystal','unitcellb',"FLOAT UNSIGNED NULL ");
database::addOrAlterColumn('crystal','unitcellc',"FLOAT UNSIGNED NULL ");
database::addOrAlterColumn('crystal','unitcellalpha',"FLOAT UNSIGNED NULL ");
database::addOrAlterColumn('crystal','unitcellbeta',"FLOAT UNSIGNED NULL ");
database::addOrAlterColumn('crystal','unitcellgamma',"FLOAT UNSIGNED NULL ");
if(database::addOrAlterColumn('crystalscore','hotkey',"INT UNSIGNED NOT NULL","Pressing this key in the drop viewer applies this score. Should be 0-9.")){
    database::query(" UPDATE crystalscore SET hotkey=scoreindex; ");
}

/**
 * Create needed objects for new functionality
 */

Log::write(Log::LOGLEVEL_INFO, "Checking for and creating shippers usergroup");
$shippersGroup=false;
try {
    $shippersGroup=usergroup::getByName('Shippers');
    Log::write(Log::LOGLEVEL_INFO, "Shippers usergroup already exists");
} catch(NotFoundException $e){
    //expected if it doesn't exist
    Log::write(Log::LOGLEVEL_INFO, "Shippers usergroup does not exist");
}
if(empty($shippersGroup)){
    Log::write(Log::LOGLEVEL_INFO, "Creating Shippers usergroup...");
    database::query(" INSERT INTO usergroup
    		(id, name, groupvisibility, membershipvisibility, joining, issystem, cancreateprojects, cancreateusergroups, description)
    		VALUES (NULL, 'Shippers', 'visible', 'visible', 'closed', '1', '0', '0', 'Users in this group can manage containers and read all container contents.');
    ");
}

Log::write(Log::LOGLEVEL_INFO, "Creating default container categories and types...");
$dewarCategory=containercategory::getByName('Dewar');
if(!$dewarCategory){
    $dewarCategory=containercategory::create(array('name'=>'Dewar'));
    $dewarCategory=$dewarCategory['created'];
}
$puckCategory=containercategory::getByName('Puck');
if(!$puckCategory){
    $puckCategory=containercategory::create(array('name'=>'Puck'));
    $puckCategory=$puckCategory['created'];
}
$pinCategory=containercategory::getByName('Pin');
if(!$pinCategory){
    $pinCategory=containercategory::create(array('name'=>'Pin'));
    $pinCategory=$pinCategory['created'];
}
if(!containertype::getByName('Dewar')){
    containertype::create(array('name'=>'Dewar', 'positions'=>20, 'containercategoryid'=>$dewarCategory['id']));
}
if(!containertype::getByName('Unipuck')){
    containertype::create(array('name'=>'Unipuck', 'positions'=>16, 'containercategoryid'=>$puckCategory['id']));
}
if(!containertype::getByName('SPINE Puck')){
    containertype::create(array('name'=>'SPINE Puck', 'positions'=>10, 'containercategoryid'=>$puckCategory['id']));
}
if(!containertype::getByName('Pin')){
    containertype::create(array('name'=>'Pin', 'positions'=>1, 'containercategoryid'=>$pinCategory['id']));
}

Log::write(Log::LOGLEVEL_INFO, "Creating microscope imagers for manual imaging...");
$sharedProject=project::getByName(baseproject::SHARED);
$sharedProjectId=$sharedProject['id'];
$temperatures=array('4','20');
foreach($temperatures as $t){
    if(imager::getByName('+'.$t.' Microscope')){
        Log::write(Log::LOGLEVEL_INFO, "+$t Microscope already exists");
        continue;
    }
    Log::write(Log::LOGLEVEL_INFO, "Creating +$t Microscope");
    
    //Imager
    $imager=imager::create(array(
        'name'=>'+'.$t.' Microscope',
        'friendlyname'=>'+'.$t.' Microscope',
        'manufacturer'=>'(any)',
        'temperature'=>$t,
        'manualimaging'=>1,
        'platecapacity'=>1,
        'alertlevel'=>10000,
        'warninglevel'=>10000
    ));
    $imager=$imager['created'];
    
    //Imaging parameters
    $ip=imagingparameters::create(array(
        'name'=>'Microscope Visible',
        'projectid'=>$sharedProjectId,
        'manufacturerdatabaseid'=>0,
        'manufacturer'=>'(any)'
    ));
    $ip=$ip['created'];    

    //Imaging parameters version
    $ipv=imagingparametersversion::create(array(
        'name'=>$ip['name'].'_v'.$ip['id'],
        'imagingparametersid'=>$ip['id'],
        'manufacturerdatabaseid'=>0,
        'projectid'=>$sharedProjectId
    ));
    $ipv=$ipv['created'];
    
    //Set correct version ID on parameters
    imagingparameters::update($ip['id'], array(
        'currentversionid'=>$ipv['id']
    ));

     //Create light path setting
    imagingsetting::create(array(
        'settingname'=>'LightType',
        'settingvalue'=>'Visible',
        'imagerid'=>$imager['id'],
        'projectid'=>$sharedProjectId,
        'imagingparametersversionid'=>$ipv['id'],
        'name'=>'v'.$ipv['id'].'_robot'.$imager['id'].'_LightType'
    ));
}

/**
 * Bugfix - plate.hasconstructs was not set consistently when plate's construct was set 
 */
Log::write(Log::LOGLEVEL_INFO, "Applying plate.hasconstructs bugfix...");
database::query(" UPDATE plate, platewell, welldrop
SET hasconstructs=TRUE
WHERE welldrop.platewellid=platewell.id AND platewell.plateid=plate.id
AND welldrop.constructid IS NOT NULL
AND plate.hasconstructs=FALSE ");

//config
Log::write(Log::LOGLEVEL_INFO, "Tidying up imager config fields...");
if(null==config::get('rigaku_platepath')){
    database::query("INSERT INTO config (name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES
			('rigaku_hasimagers', 'Connect IceBear to Rigaku imagers', 'boolean', NULL, NULL, NULL, '0', '0'),
			('rigaku_platepath', 'Directory containing Rigaku plate XML files', 'text', NULL, NULL, NULL, '', ''),
			('rigaku_imagepath', 'Root directory of the Rigaku image store', 'text', NULL, NULL, NULL, '', ''),
			('rigaku_thumbpath', 'Root directory of the Rigaku image thumbnail store', 'text', NULL, NULL, NULL, '', '')"
	);
}
if(null==config::get('fx_imagestoreroot')){
    database::query('INSERT INTO config(name,description,type,defaultvalue,value)
			VALUES("fx_imagestoreroot","","text","/mnt/Formulatrix","/mnt/Formulatrix")'
	);
}

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

