-- phpMyAdmin SQL Dump
-- version 4.1.14
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Jun 06, 2017 at 01:36 PM
-- Server version: 5.6.17
-- PHP Version: 5.5.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: 'icebear'
--

-- --------------------------------------------------------

--
-- Table structure for table 'baseobject'
--

CREATE TABLE IF NOT EXISTS baseobject (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `projectid` bigint(20) unsigned NOT NULL,
  `objecttype` char(50) COLLATE utf8_bin DEFAULT NULL,
  `creator` bigint(20) unsigned NOT NULL,
  `createtime` datetime NOT NULL,
  `lasteditor` bigint(20) unsigned DEFAULT NULL,
  `lastedittime` datetime DEFAULT NULL,
  `details` varchar(1000) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY `projectid` (`projectid`),
  KEY `objecttype` (`objecttype`),
  KEY `creator` (`creator`),
  KEY `lasteditor` (`lasteditor`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'config'
--

CREATE TABLE IF NOT EXISTS config (
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `description` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '(No description)',
  `type` enum('boolean','int','float','text','enum') COLLATE utf8_bin NOT NULL DEFAULT 'text',
  `enumvalues` varchar(200) COLLATE utf8_bin DEFAULT NULL,
  `minimum` decimal(10,0) DEFAULT NULL,
  `maximum` decimal(10,0) DEFAULT NULL,
  `defaultvalue` varchar(200) COLLATE utf8_bin NOT NULL,
  `value` varchar(200) COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'construct'
--

CREATE TABLE IF NOT EXISTS construct (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `proteinid` bigint(20) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `description` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '(No description)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`,`proteinid`),
  KEY `projectid` (`projectid`),
  KEY `proteinid` (`proteinid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'crystal'
--

CREATE TABLE IF NOT EXISTS crystal (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `prefix` varchar(45) COLLATE utf8_bin NOT NULL,
  `suffix` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `numberindrop` int(10) unsigned NOT NULL,
  `pixelx` int(10) unsigned NOT NULL,
  `pixely` int(10) unsigned NOT NULL,
  `welldropid` bigint(20) unsigned NOT NULL,
  `dropimageid` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `dropandnumberunique` (`welldropid`,`numberindrop`) COMMENT 'Combination of drop and crystal number must be unique',
  KEY `dropimageid` (`dropimageid`),
  KEY `projectid` (`projectid`),
  KEY `welldropid` (`welldropid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'crystalscore'
--

CREATE TABLE IF NOT EXISTS crystalscore (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL COMMENT 'NOT the score name, a unique identifier',
  `projectid` bigint(20) unsigned NOT NULL,
  `crystalscoringsystemid` bigint(20) unsigned NOT NULL,
  `label` varchar(30) COLLATE utf8_bin NOT NULL COMMENT 'The user-displayed name ("Plates","Crystals")',
  `color` varchar(6) COLLATE utf8_bin NOT NULL COMMENT 'HTML hex code',
  `scoreindex` int(10) unsigned NOT NULL COMMENT 'Order within the scoring system',
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `id` (`id`),
  KEY `projectid` (`projectid`,`crystalscoringsystemid`),
  KEY `crystalscoringsystemid` (`crystalscoringsystemid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'crystalscoringsystem'
--

CREATE TABLE IF NOT EXISTS crystalscoringsystem (
  `id` bigint(11) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `projectid` bigint(11) unsigned NOT NULL,
  `iscurrent` tinyint(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `id` (`id`),
  KEY `projectid` (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'dropimage'
--

CREATE TABLE IF NOT EXISTS dropimage (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `imagingsessionid` bigint(20) unsigned NOT NULL,
  `welldropid` bigint(20) unsigned NOT NULL,
  `pixelheight` int(11) NOT NULL,
  `pixelwidth` int(11) NOT NULL,
  `micronsperpixelx` float NOT NULL,
  `micronsperpixely` float NOT NULL,
  `latestcrystalscoreid` bigint(20) unsigned DEFAULT NULL,
  `imagestorepath` varchar(250) COLLATE utf8_bin NOT NULL,
  `imagepath` varchar(250) COLLATE utf8_bin NOT NULL,
  `thumbnailpath` varchar(250) COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `id` (`id`),
  KEY `imagestorepath` (`imagestorepath`),
  KEY `imagingsessionid` (`imagingsessionid`),
  KEY `projectid` (`projectid`),
  KEY `welldropid` (`welldropid`),
  KEY `latestscore` (`latestcrystalscoreid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'file'
--

CREATE TABLE IF NOT EXISTS file (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '',
  `parentid` bigint(20) unsigned NOT NULL,
  `filename` varchar(250) COLLATE utf8_bin NOT NULL,
  `mimetype` varchar(250) COLLATE utf8_bin NOT NULL,
  `bytes` bigint(20) unsigned NOT NULL,
  `description` varchar(250) COLLATE utf8_bin NOT NULL DEFAULT '(No description)',
  UNIQUE KEY `id` (`id`),
  KEY `projectid` (`projectid`),
  KEY `parentid` (`parentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'groupmembership'
--

CREATE TABLE IF NOT EXISTS groupmembership (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userid` bigint(20) unsigned NOT NULL,
  `usergroupid` bigint(20) unsigned NOT NULL,
  `isgroupadmin` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `membership` (`userid`,`usergroupid`),
  KEY `usergroupid` (`usergroupid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'homepagebrick'
--

CREATE TABLE IF NOT EXISTS homepagebrick (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `version` int(11) DEFAULT '1',
  `title` varchar(60) COLLATE utf8_bin NOT NULL,
  `description` varchar(250) COLLATE utf8_bin DEFAULT '(No description)',
  `height` int(11) NOT NULL DEFAULT '1',
  `width` int(11) NOT NULL DEFAULT '1',
  `apiurl` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `scriptblock` text COLLATE utf8_bin,
  `content` text COLLATE utf8_bin,
  `headertemplates` text COLLATE utf8_bin NOT NULL,
  `rowtemplates` text COLLATE utf8_bin NOT NULL,
  `adminonly` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY name (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'homepagedefaultbrick'
--

CREATE TABLE IF NOT EXISTS homepagedefaultbrick (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `homepagebrickid` bigint(20) unsigned NOT NULL,
  `row` int(11) NOT NULL,
  `col` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`homepagebrickid`,`row`,`col`),
  KEY `hpb_brick` (`homepagebrickid`),
  KEY `brick_id` (`homepagebrickid`),
  KEY `homepagebrickid` (`homepagebrickid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'homepageuserbrick'
--

CREATE TABLE IF NOT EXISTS homepageuserbrick (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userid` bigint(20) unsigned NOT NULL,
  `homepagebrickid` bigint(20) unsigned NOT NULL,
  `row` int(11) NOT NULL,
  `col` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`userid`,`homepagebrickid`,`row`,`col`),
  KEY `hpb_brick` (`homepagebrickid`),
  KEY `brick_id` (`homepagebrickid`),
  KEY `user_id_2` (`userid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'imager'
--

CREATE TABLE IF NOT EXISTS imager (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(10) unsigned NOT NULL COMMENT 'For an imager, this will almost certainly be the "Shared" project',
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `friendlyname` varchar(100) COLLATE utf8_bin NOT NULL,
  `temperature` int(11) NOT NULL,
  `manufacturer` varchar(50) COLLATE utf8_bin NOT NULL,
  `platecapacity` int(10) unsigned NOT NULL COMMENT 'How many plates the imager can hold',
  `platesactive` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'How many plates are in the imager with at least one inspection still to occur',
  `platesexpired` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'How many plates in the imager have no scheduled inspections left',
  `alertlevel` int(11) NOT NULL DEFAULT '10' COMMENT 'Plate load level where system should alert',
  `warninglevel` int(11) NOT NULL DEFAULT '15' COMMENT 'Plate load where system should warn',
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `friendlyname` (`friendlyname`),
  KEY `projectid` (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'imagerloadlog'
--

CREATE TABLE IF NOT EXISTS imagerloadlog (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `loadingdate` date NOT NULL,
  `imagerid` bigint(20) unsigned NOT NULL,
  `platesactive` int(10) unsigned NOT NULL,
  `platesexpired` int(10) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `imager_date_unique` (`loadingdate`,`imagerid`),
  KEY `projectid` (`projectid`),
  KEY `loadingdate` (`loadingdate`),
  KEY `imagerid` (`imagerid`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Historic log of imager loading';

-- --------------------------------------------------------

--
-- Table structure for table 'imagingparameters'
--

CREATE TABLE IF NOT EXISTS imagingparameters (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `manufacturer` varchar(50) COLLATE utf8_bin NOT NULL,
  `currentversionid` bigint(20) unsigned DEFAULT NULL,
  `manufacturerdatabaseid` bigint(20) unsigned NOT NULL,
  UNIQUE KEY id (`id`),
  KEY `projectid` (`projectid`,`name`,`manufacturer`,`currentversionid`),
  KEY `currentversionid` (`currentversionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'imagingparametersversion'
--

CREATE TABLE IF NOT EXISTS imagingparametersversion (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `imagingparametersid` bigint(20) unsigned NOT NULL,
  `manufacturerdatabaseid` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `projectid` (`projectid`,`name`,`imagingparametersid`),
  KEY `imagingparametersid` (`imagingparametersid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'imagingsession'
--

CREATE TABLE IF NOT EXISTS imagingsession (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `temperature` int(11) NOT NULL DEFAULT '20',
  `manufacturerdatabaseid` bigint(20) unsigned NOT NULL,
  `imagerid` bigint(20) unsigned NOT NULL,
  `plateid` bigint(20) unsigned NOT NULL,
  `imageddatetime` datetime NOT NULL COMMENT 'Assumed to be UTC.',
  `lighttype` varchar(50) COLLATE utf8_bin NOT NULL COMMENT 'e.g., UV or Visible',
  `imagingparametersversionid` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`),
  KEY `imagerid` (`imagerid`),
  KEY `plateid` (`plateid`),
  KEY `imageddatetime` (`imageddatetime`),
  KEY `lighttype` (`lighttype`),
  KEY `manufacturerdatabaseid` (`manufacturerdatabaseid`),
  KEY `imagingparametersversionid` (`imagingparametersversionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'imagingsetting'
--

CREATE TABLE IF NOT EXISTS imagingsetting (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `imagingparametersversionid` bigint(20) unsigned NOT NULL,
  `imagerid` bigint(20) unsigned NOT NULL,
  `settingname` varchar(100) COLLATE utf8_bin NOT NULL,
  `settingvalue` varchar(100) COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `projectid` (`projectid`,`name`,`imagingparametersversionid`,`imagerid`,`settingname`,`settingvalue`),
  KEY `imagerid` (`imagerid`),
  KEY `imagingparametersversionid` (`imagingparametersversionid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'note'
--

CREATE TABLE IF NOT EXISTS note (
  `id` bigint(20) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `parentid` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `userid` bigint(20) unsigned NOT NULL,
  `text` text COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `id` (`id`),
  KEY `parentid` (`parentid`,`projectid`,`userid`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'permission'
--

CREATE TABLE IF NOT EXISTS permission (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `usergroupid` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `type` enum('create','read','update','delete') COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `projectid` (`projectid`),
  KEY `usergroupid` (`usergroupid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'plate'
--

CREATE TABLE IF NOT EXISTS plate (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `platetypeid` bigint(20) unsigned NOT NULL,
  `ownerid` bigint(20) unsigned NOT NULL,
  `locationid` bigint(20) unsigned DEFAULT NULL COMMENT 'The ID of a baseobject, eg imager or room',
  `crystalscoringsystemid` bigint(20) unsigned NOT NULL,
  `bestcrystalscoreid` bigint(20) unsigned DEFAULT NULL,
  `dropsize` varchar(10) COLLATE utf8_bin NOT NULL DEFAULT '0nL',
  `hasconstructs` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether at least one well in the plate has had a protein set. Used for task list generation. Actual protein stored against wells.',
  `screenid` bigint(20) unsigned DEFAULT NULL,
  `offsetinscreenx` int(11) NOT NULL DEFAULT '0' COMMENT 'How many columns the plate is offset from column 1 in the screen (usually 0; left is positive)',
  `offsetinscreeny` int(11) NOT NULL DEFAULT '0' COMMENT 'How many rows the plate is offset from row A in the screen (usually 0; down is positive)',
  `nextinspectiontime` datetime DEFAULT NULL,
  `finalinspectiontime` datetime DEFAULT NULL,
  `inspectionsremaining` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`),
  KEY `ownerid` (`ownerid`),
  KEY `bestcrystalscoreid` (`bestcrystalscoreid`),
  KEY `crystalscoringsystemid` (`crystalscoringsystemid`),
  KEY `platetypeid` (`platetypeid`),
  KEY `screenid` (`screenid`),
  KEY `locationid` (`locationid`),
  KEY `nextinspectiontime` (`nextinspectiontime`,`finalinspectiontime`,`inspectionsremaining`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'platetype'
--

CREATE TABLE IF NOT EXISTS platetype (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `rows` int(10) unsigned NOT NULL,
  `cols` int(10) unsigned NOT NULL,
  `subs` int(10) unsigned NOT NULL,
  `dropmapping` varchar(60) COLLATE utf8_bin DEFAULT NULL,
  `defaultdropsize` varchar(10) COLLATE utf8_bin DEFAULT '0nL',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `project` (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'platewell'
--

CREATE TABLE IF NOT EXISTS platewell (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `plateid` bigint(20) unsigned NOT NULL,
  `row` int(11) NOT NULL,
  `col` int(11) NOT NULL,
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `id` (`id`),
  KEY `projectid` (`projectid`),
  KEY `plateid` (`plateid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'project'
--

CREATE TABLE IF NOT EXISTS project (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `owner` bigint(20) unsigned NOT NULL,
  `isarchived` tinyint(1) NOT NULL DEFAULT '0',
  `issystem` tinyint(1) NOT NULL DEFAULT '0',
  `description` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '(No description)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `owner` (`owner`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'protein'
--

CREATE TABLE IF NOT EXISTS protein (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `proteinacronym` varchar(50) COLLATE utf8_bin NOT NULL,
  `description` varchar(200) COLLATE utf8_bin NOT NULL DEFAULT '(No description)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `projectid_2` (`projectid`,`name`),
  KEY `projectid` (`projectid`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'scoreofdropimage'
--

CREATE TABLE IF NOT EXISTS scoreofdropimage (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `dropimageid` bigint(11) unsigned NOT NULL,
  `crystalscoreid` bigint(11) unsigned NOT NULL,
  `scoreddatetime` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`),
  KEY `dropimageid` (`dropimageid`),
  KEY `crystalscoreid` (`crystalscoreid`),
  KEY `scoreddatetime` (`scoreddatetime`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Holds user (or system) scores of images' AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'screen'
--

CREATE TABLE IF NOT EXISTS screen (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(250) COLLATE utf8_bin NOT NULL,
  `manufacturer` varchar(100) COLLATE utf8_bin NOT NULL DEFAULT "",
  `catalognumber` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT "",
  `isstandard` tinyint(1) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'screencondition'
--

CREATE TABLE IF NOT EXISTS screencondition (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(250) COLLATE utf8_bin NOT NULL,
  `screenid` bigint(20) unsigned NOT NULL,
  `wellnumber` int(11) NOT NULL,
  `row` int(11) NOT NULL,
  `col` int(11) NOT NULL,
  `description` text COLLATE utf8_bin NOT NULL,
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`),
  KEY `screenid` (`screenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'sequence'
--

CREATE TABLE IF NOT EXISTS sequence (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `constructid` bigint(20) unsigned NOT NULL,
  `name` varchar(60) COLLATE utf8_bin NOT NULL,
  `dnasequence` text COLLATE utf8_bin NOT NULL,
  `proteinsequence` text COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`),
  KEY `projectid` (`projectid`),
  KEY `constructid` (`constructid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table 'user'
--

CREATE TABLE IF NOT EXISTS user (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8_bin NOT NULL,
  `fullname` varchar(150) COLLATE utf8_bin NOT NULL,
  `password` varchar(100) COLLATE utf8_bin NOT NULL,
  `email` varchar(100) COLLATE utf8_bin NOT NULL,
  `isactive` tinyint(1) NOT NULL DEFAULT '1',
  `supervisorid` bigint(20) unsigned DEFAULT NULL COMMENT 'The userid of this user''s supervisor',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'usergroup'
--

CREATE TABLE IF NOT EXISTS usergroup (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `groupvisibility` enum('hidden','membersonly','visible') COLLATE utf8_bin NOT NULL DEFAULT 'hidden',
  `membershipvisibility` enum('hidden','membersonly','visible') COLLATE utf8_bin NOT NULL DEFAULT 'hidden',
  `joining` enum('closed','request','open','auto') COLLATE utf8_bin NOT NULL DEFAULT 'closed',
  `issystem` tinyint(1) NOT NULL DEFAULT '0',
  `cancreateprojects` tinyint(1) NOT NULL DEFAULT '0',
  `cancreateusergroups` tinyint(1) NOT NULL DEFAULT '0',
  `description` char(250) COLLATE utf8_bin NOT NULL DEFAULT '(No description)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table 'welldrop'
--

CREATE TABLE IF NOT EXISTS welldrop (
  `id` bigint(20) unsigned NOT NULL,
  `projectid` bigint(20) unsigned NOT NULL,
  `name` varchar(100) COLLATE utf8_bin NOT NULL,
  `platewellid` bigint(20) unsigned NOT NULL,
  `dropnumber` int(10) unsigned NOT NULL,
  `constructid` bigint(20) unsigned DEFAULT NULL COMMENT 'The protein construct that is in this drop',
  `proteinconcentrationamount` float DEFAULT NULL,
  `proteinconcentrationunit` char(10) COLLATE utf8_bin NOT NULL DEFAULT 'mg/mL',
  `proteinbuffer` char(250) COLLATE utf8_bin DEFAULT NULL,
  `wellsolutionamount` float DEFAULT NULL,
  `wellsolutionunit` char(10) COLLATE utf8_bin NOT NULL DEFAULT 'uL',
  `proteinsolutionamount` float DEFAULT NULL,
  `proteinsolutionunit` char(10) COLLATE utf8_bin NOT NULL DEFAULT 'nL',
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `projectid` (`projectid`),
  KEY `platewellid` (`platewellid`),
  KEY `constructid` (`constructid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

--
-- Constraints for dumped tables
--

--
-- Constraints for table baseobject
--
ALTER TABLE baseobject
  ADD CONSTRAINT baseobject_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT baseobject_creator_user_id FOREIGN KEY (creator) REFERENCES user (id),
  ADD CONSTRAINT baseobject_lasteditor_user_id FOREIGN KEY (lasteditor) REFERENCES user (id);

--
-- Constraints for table construct
--
ALTER TABLE construct
  ADD CONSTRAINT construct_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT construct_proteinid_protein_id FOREIGN KEY (proteinid) REFERENCES protein (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT construct_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id);

--
-- Constraints for table crystal
--
ALTER TABLE crystal
  ADD CONSTRAINT crystal_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT crystal_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT crystal_welldropid_welldrop_id FOREIGN KEY (welldropid) REFERENCES welldrop (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT crystal_dropimageid_dropimage_id FOREIGN KEY (dropimageid) REFERENCES dropimage (id);

--
-- Constraints for table crystalscore
--
ALTER TABLE crystalscore
  ADD CONSTRAINT crystalscore_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT crystalscore_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT crystalscore_crystalscoringsystemid_crystalscoringsystem_id FOREIGN KEY (crystalscoringsystemid) REFERENCES crystalscoringsystem (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table crystalscoringsystem
--
ALTER TABLE crystalscoringsystem
  ADD CONSTRAINT crystalscoringsystem_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT crystalscoringsystem_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id);

--
-- Constraints for table dropimage
--
ALTER TABLE dropimage
  ADD CONSTRAINT dropimage_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT dropimage_welldropid_welldrop_id FOREIGN KEY (welldropid) REFERENCES welldrop (id),
  ADD CONSTRAINT dropimage_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT dropimage_imagingsessionid_imagingsession_id FOREIGN KEY (imagingsessionid) REFERENCES imagingsession (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT dropimage_latestcrystalscoreid_crystalscore_id FOREIGN KEY (latestcrystalscoreid) REFERENCES crystalscore (id);

--
-- Constraints for table file
--
ALTER TABLE file
  ADD CONSTRAINT file_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT file_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT file_parentid_baseobject_id FOREIGN KEY (parentid) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table groupmembership
--
ALTER TABLE groupmembership
  ADD CONSTRAINT groupmembership_userid_user_id FOREIGN KEY (userid) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT groupmembership_usergroupid_usergroup_id FOREIGN KEY (usergroupid) REFERENCES usergroup (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table homepagedefaultbrick
--
ALTER TABLE homepagedefaultbrick
  ADD CONSTRAINT homepagedefaultbrick_homepagebrickid_homepagebrick_id FOREIGN KEY (homepagebrickid) REFERENCES homepagebrick (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table homepageuserbrick
--
ALTER TABLE homepageuserbrick
  ADD CONSTRAINT homepageuserbrick_homepagebrickid_homepagebrick_id FOREIGN KEY (homepagebrickid) REFERENCES homepagebrick (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT homepageuserbrick_userid_user_id FOREIGN KEY (userid) REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table imager
--
ALTER TABLE imager
  ADD CONSTRAINT imager_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imager_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id);

--
-- Constraints for table imagerloadlog
--
ALTER TABLE imagerloadlog
  ADD CONSTRAINT imagerloadlog_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id),
  ADD CONSTRAINT imagerloadlog_imagerid_imager_id FOREIGN KEY (imagerid) REFERENCES imager (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagerloadlog_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table imagingparameters
--
ALTER TABLE imagingparameters
  ADD CONSTRAINT imagingparameters_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagingparameters_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT imagingparameters_currentversionid_imagingparametersversion_id FOREIGN KEY (currentversionid) REFERENCES imagingparametersversion (id);

--
-- Constraints for table imagingparametersversion
--
ALTER TABLE imagingparametersversion
  ADD CONSTRAINT imagingparametersversion_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagingparametersversion_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT imagingparametersversion_ipid_imagingparamaters_id FOREIGN KEY (imagingparametersid) REFERENCES imagingparameters (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table imagingsession
--
ALTER TABLE imagingsession
  ADD CONSTRAINT imagingsession_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagingsession_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT imagingsession_imagerid_imager_id FOREIGN KEY (imagerid) REFERENCES imager (id),
  ADD CONSTRAINT imagingsession_plateid_plate_id FOREIGN KEY (plateid) REFERENCES plate (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagingsession_ipvid_imagingparametersversion_id FOREIGN KEY (imagingparametersversionid) REFERENCES imagingparametersversion (id);

--
-- Constraints for table imagingsetting
--
ALTER TABLE imagingsetting
  ADD CONSTRAINT imagingsetting_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagingsetting_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT imagingsetting_ipvid_imagingparametersversion_id FOREIGN KEY (imagingparametersversionid) REFERENCES imagingparametersversion (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT imagingsetting_imagerid_imager_id FOREIGN KEY (imagerid) REFERENCES imager (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table permission
--
ALTER TABLE permission
  ADD CONSTRAINT permission_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT permission_usergroupid_usergroup_id FOREIGN KEY (usergroupid) REFERENCES usergroup (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table plate
--
ALTER TABLE plate
  ADD CONSTRAINT plate_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT plate_platetypeid_platetype_id FOREIGN KEY (platetypeid) REFERENCES platetype (id),
  ADD CONSTRAINT plate_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT plate_ownerid_user_id FOREIGN KEY (ownerid) REFERENCES user (id),
  ADD CONSTRAINT plate_bestcrystalscoreid_crystalscore_id FOREIGN KEY (bestcrystalscoreid) REFERENCES crystalscore (id),
  ADD CONSTRAINT plate_crystalscoringsystemid_crystalscoringsystem_id FOREIGN KEY (crystalscoringsystemid) REFERENCES crystalscoringsystem (id),
  ADD CONSTRAINT plate_screenid_screen_id FOREIGN KEY (screenid) REFERENCES screen (id),
  ADD CONSTRAINT plate_locationid_baseobject_id FOREIGN KEY (locationid) REFERENCES baseobject (id) ON DELETE SET NULL ON UPDATE SET NULL;

--
-- Constraints for table platetype
--
ALTER TABLE platetype
  ADD CONSTRAINT platetype_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT platetype_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id) ON DELETE RESTRICT ON UPDATE RESTRICT;

--
-- Constraints for table platewell
--
ALTER TABLE platewell
  ADD CONSTRAINT platewell_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id) ON UPDATE CASCADE,
  ADD CONSTRAINT platewell_plateid_plate_id FOREIGN KEY (plateid) REFERENCES plate (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table project
--
ALTER TABLE project
  ADD CONSTRAINT project_ownerid_user_id FOREIGN KEY (owner) REFERENCES user (id) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Constraints for table protein
--
ALTER TABLE protein
  ADD CONSTRAINT protein_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT protein_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table scoreofdropimage
--
ALTER TABLE scoreofdropimage
  ADD CONSTRAINT scoreofdropimage_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT scoreofdropimage_dropimageid_dropimage_id FOREIGN KEY (dropimageid) REFERENCES dropimage (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT scoreofdropimage_crystalscoreid_crystalscore_id FOREIGN KEY (crystalscoreid) REFERENCES crystalscore (id);

--
-- Constraints for table screen
--
ALTER TABLE screen
  ADD CONSTRAINT screen_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT screen_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id);

--
-- Constraints for table screencondition
--
ALTER TABLE screencondition
  ADD CONSTRAINT screencondition_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT screencondition_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT screencondition_screenid_screen_id FOREIGN KEY (screenid) REFERENCES screen (id) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table sequence
--
ALTER TABLE sequence
  ADD CONSTRAINT sequence_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT sequence_constructid_construct_id FOREIGN KEY (constructid) REFERENCES construct (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT sequence_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id);

--
-- Constraints for table welldrop
--
ALTER TABLE welldrop
  ADD CONSTRAINT welldrop_id_baseobject_id FOREIGN KEY (id) REFERENCES baseobject (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT welldrop_projectid_project_id FOREIGN KEY (projectid) REFERENCES project (id),
  ADD CONSTRAINT welldrop_platewellid_platewell_id FOREIGN KEY (platewellid) REFERENCES platewell (id) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT welldrop_constructid_construct_id FOREIGN KEY (constructid) REFERENCES construct (id);

--
-- Config items
--

INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_icebearversion', 'IceBear version number', 'text', NULL, NULL, NULL, 'unknown', '1.0');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_cas_context', '', 'text', NULL, NULL, NULL, 'http://', 'http://');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_cas_host', '', 'text', NULL, NULL, NULL, 'http://', 'http://');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_cas_port', '', 'int', NULL, '0', '65535', '443', '443');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_db_allow_rfid_login', 'If DB auth, allow users to log in with RFID', 'boolean', NULL, NULL, NULL, '0', '0');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_ldap_allow_rfid_login', 'If LDAP auth, allow users to log in with RFID', 'boolean', NULL, NULL, NULL, '0', '0');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_method', 'How users authenticate', 'enum', '''Database'',''LDAP'',''CAS''', NULL, NULL, 'Database', 'Database');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('auth_timeout_mins', 'Inactivity timeout (minutes)', 'int', NULL, '0', NULL, '15', '15');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_databasebackup', 'Directory where database backups are stored', 'text', NULL, NULL, NULL, '/icebearbackups/database', '');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_databasebackupstokeep', 'How many database backups to keep', 'int', NULL, '0', NULL, '0', '5');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_filestore', 'Where uploaded files are stored', 'text', NULL, NULL, NULL, '/icebearstore/fileuploads', '/icebearstore/fileuploads');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_filestorebackup', 'Where uploaded files are backed up', 'text', NULL, NULL, NULL, '', '');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_imagestore', 'Path to the IceBear image store', 'text', NULL, NULL, NULL, '', '');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('core_imagestorebackup', 'Where IceBear''s image store is backed up', 'text', NULL, NULL, NULL, '', '');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('delete_allowed', 'If this is unset, non-administrators can never delete records, regardless of group permissions', 'boolean', NULL, NULL, NULL, '1', '1');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_dbhost', 'Database connection URI', 'text', NULL, NULL, NULL, 'FORMULATRIXBOX.myplace.edu', 'FORMULATRIXBOX.myplace.edu');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_dbport', 'Database port number', 'int', NULL, '0', '65535', '53364', '53664');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_dbuser', 'Database username', 'text', NULL, NULL, NULL, '', 'formulatrix');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_dbpass', 'Database password', 'text', NULL, NULL, NULL, '', 'fmlx_123');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_hasimagers', 'Connect IceBear to Formulatrix imagers', 'boolean', NULL, NULL, NULL, '0', '0');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_ridbname', 'RockImager database name', 'text', NULL, NULL, NULL, 'RockImager', 'RockImager');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_rmdbname', 'RockMaker database name', 'text', NULL, NULL, NULL, 'RockMaker', 'RockMaker');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('imaging_allowmanual', 'Whether users can create plate inspections manually', 'boolean', NULL, NULL, NULL, '0', '0');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_importerrunning', 'Unset this to pause the importer', 'boolean', NULL,NULL,NULL,'0','0');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('fx_importfromdate', 'The date from which to start importing, in YYYY-MM-DD format (normally empty)', 'text', NULL, NULL, NULL, '2001-01-01', '2001-01-01');
INSERT INTO config(name, description, type, enumvalues, minimum, maximum, defaultvalue, value) VALUES('update_baseuri', 'Base URI for updates, etc', 'text', NULL, NULL, NULL, 'https://www.icebear.fi/', 'https://www.icebear.fi/');

--
-- Homepage bricks
--
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(1, 'fi_oulu_Projects', 1, 'Projects', 'A list of projects', 1, 1, '/api/project', '', '', '[''Name'']', '[''<a href="/project/{{id}}">{{name}}</a>'']', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(2, 'fi_oulu_Groups', 2, 'Groups', 'A list of user groups', 1, 1, '/api/usergroup', '\r\n', '', '[''Name'']', '[''<a href="/usergroup/{{id}}">{{name}}</a>'']', 1);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(3, 'fi_oulu_FirstBrick', 1, 'Hello!', 'A simple brick, explaining that the homepage is configurable.', 1, 1, '', '', '<p>You can set up your homepage the way you like it. Just click the gear at the top right of the page to get started.</p>\r\n<p>Click and drag the title bar of a brick to move it, or click the red x to remove the brick from your homepage.</p>\r\n<p>Click on an empty space to find more bricks.</p>', '', '', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(4, 'fi_oulu_PlateTypes', 1, 'Plate types', 'A list of plate types', 1, 1, '/api/platetype', '', '', '[''Name'',''Dimensions'']', '[''<a href="/platetype/{{id}}">{{name}}</a>'',''{{rows}}x{{cols}}, {{subs}}-drop'']', 1);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(6, 'fi_oulu_PlateInspections', 1, 'Latest plate inspections', 'The most recent plate inspections', 1, 1, '/api/imagingsession?sortby=imageddatetime&sortdescending=1', '//', '', '', '[ [ImagingSession.getLink,''platename''],[ui.fieldToFriendlyDate,''imageddatetime''],''<a href="/imager/{{imagerid}}">{{imagerfriendlyname}}</a>'',''<a href="/project/{{projectid}}">{{projectname}}</a>'' ]', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(8, 'fi_oulu_Imagers', 1, 'Imager overview', 'An overview of imager load', 1, 1, '/api/imager', 'document.fi_oulu_Imagers_functions={\r\n	onSuccess:function(transport,box){\r\n		var statusColors={\r\n			activenormal: "#0c0",\r\n			activealert:  "#cc0",\r\n			activewarn:   "#c00",\r\n			expirednormal:"#070",\r\n			expiredalert: "#770",\r\n			expiredwarn:  "#700",\r\n		};\r\n		var imagers=transport.responseJSON.rows;\r\n		box.querySelector(".boxbody").innerHTML="";\r\n		imagers.forEach(function(im){\r\n			if(im.platecapacity*1<10 || "Unknown"==im.manufacturer){ return; } //from this iteration\r\n			var activePercent=(100*im.platesactive/im.platecapacity);\r\n			var expiredPercent=(100*im.platesexpired/im.platecapacity);\r\n			var alertState="normal";\r\n			if((1*im.platesactive)+(1*im.platesexpired)>=1*im.alertlevel){ alertState=''alert''; }\r\n			if((1*im.platesactive)+(1*im.platesexpired)>=1*im.warninglevel){ alertState=''warn''; }\r\n			var title=im.platesactive+'' active, ''+im.platesexpired+'' expired, ''+(1*im.platecapacity-((1*im.platesactive)+(1*im.platesexpired)))+'' free'';\r\n			var imagerDiv=document.createElement("div");\r\n			var link=document.createElement("a");\r\n			link.href="/imager/"+im.id;\r\n			link.innerHTML=im.friendlyname+" ("+im.name+")";\r\n			imagerDiv.title=title;\r\n			imagerDiv.appendChild(link);\r\n			var capacityBar=document.createElement("div");\r\n			imagerDiv.appendChild(capacityBar);\r\n			var activeBar=document.createElement("div");\r\n			capacityBar.appendChild(activeBar);\r\n			var expiredBar=document.createElement("div");\r\n			capacityBar.appendChild(expiredBar);\r\n			box.querySelector(".boxbody").appendChild(imagerDiv);\r\n			Object.assign(capacityBar.style,{\r\n				margin:"0.25em 0 0.5em 0",\r\n				minHeight:"1em",\r\n				backgroundColor:"#ccc",\r\n				borderRadius:"3px",\r\n				boxShadow:"2px 2px 2px #666 inset",\r\n				fontSize:"80%", \r\n				height:"1em", \r\n				overflow:"hidden",\r\n				position:"relative", \r\n				top:0\r\n			});\r\n			Object.assign(activeBar.style,{\r\n				position:"absolute", \r\n				top:0, \r\n				left:0, \r\n				bottom:0, \r\n				width:activePercent+"%", \r\n				backgroundColor:statusColors[''active''+alertState], \r\n				boxShadow:"1px 1px 1px #333 inset",\r\n			});\r\n			Object.assign(expiredBar.style,{\r\n				position:"absolute", \r\n				top:0, \r\n				left:activePercent+"%", \r\n				bottom:0, \r\n				width:expiredPercent+"%", \r\n				backgroundColor:statusColors[''expired''+alertState], \r\n				boxShadow:"1px 1px 1px #333 inset"\r\n			});\r\n			Object.assign(imagerDiv.style,{\r\n				padding:"0.25em 0", \r\n				borderBottom:"1px solid #999"\r\n			});\r\n		});\r\n	}\r\n}', '', '', '', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(10, 'fi_oulu_AutoProvisionedUsers', 1, 'Auto-provisioned users', 'A list of users created by an importer. Usernames begin with AUTO, accounts inactive.', 1, 1, '/api/user/search/AUTO?sortby=id&sortdescending=yes', '', '', '[''Username'',''Name'']', '[''{{name}}'',''<a href="/user/{{id}}">{{fullname}}</a>'']', 1);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(11, 'fi_oulu_MyRecentPlates', 1, 'My latest plates', 'Plates owned by the current user, listed newest first.', 1, 1, '/api/plate/ownerid/{{userid}}?sortby=name&sortdescending=1', '', '', '', '[''<a href="/plate/{{id}}">{{name}}</a>'' ]', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(12, 'fi_oulu_Plates', 1, 'Plates', 'Plates visible to the current user, listed newest first.', 1, 1, '/api/plate/?sortby=name&sortdescending=1', '', '', '[''Barcode'',''Project'']', '[''<a href="/plate/{{id}}">{{name}}</a>'',''<a href="/project/{{projectid}}">{{projectname}}</a>'' ]', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(14, 'fi_oulu_Tasks', 2, 'Tasks', 'A to-do list of items needing attention', 1, 1, '/api/task', '', '', '[''Item'',''Task'']', '[''<a href="/{{type}}/{{id}}">{{name}}</a>'', ''{{description}}'' ]', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(15, 'fi_oulu_DiskUsage', 1, 'Disk usage overview', 'An overview of disk usage on the server, including mounted network drives', 1, 1, '/api/diskusage', 'document.fi_oulu_DiskUsage_functions={\r\n	onSuccess:function(transport,box){\r\n		var alertThreshold=85;\r\n		var warnThreshold=95;\r\n		var statusColors={\r\n			normal: "#0c0",\r\n			alert:  "#f60",\r\n			warn:   "#c00",\r\n		};\r\n		var disks=transport.responseJSON.rows;\r\n		box.querySelector(".boxbody").innerHTML="";\r\n		disks.forEach(function(d){\r\n			var alertState="normal";\r\n			if((1*d.percent)>=alertThreshold){ alertState=''alert''; }\r\n			if((1*d.percent)>=warnThreshold){ alertState=''warn''; }\r\n			var diskDiv=document.createElement("div");\r\n			var capacityBar=document.createElement("div");\r\n			diskDiv.appendChild(capacityBar);\r\n			var fullBar=document.createElement("div");\r\n			capacityBar.appendChild(fullBar);\r\n			var percentFull=document.createElement("span");\r\n			percentFull.innerHTML=d.percent+"%";\r\n			diskDiv.appendChild(percentFull);\r\n			var label=document.createElement("span");\r\n			label.innerHTML=d.mountpoint;\r\n			diskDiv.title="Size "+d.size+", "+d.used+" used, "+d.free+" free ("+d.filesystem+")";\r\n			diskDiv.appendChild(label);\r\n			box.querySelector(".boxbody").appendChild(diskDiv);\r\n			Object.assign(diskDiv.style,{\r\n				padding:"0.25em 0", \r\n				lineHeight:"1.5em",\r\n				borderBottom:"1px solid #999",\r\n			});\r\n			Object.assign(capacityBar.style,{\r\n				width:"40%",\r\n				display:"inline-block",\r\n				margin:"0em 1em 0 0", \r\n				backgroundColor:"#ccc", \r\n				borderRadius:"3px", \r\n				boxShadow:"2px 2px 2px #666 inset", \r\n				fontSize:"80%", \r\n				height:"1em", \r\n				overflow:"hidden", \r\n				position:"relative", \r\n				top:0,\r\n			});\r\n			Object.assign(fullBar.style,{\r\n				position:"absolute", \r\n				top:0, \r\n				left:0, \r\n				bottom:0, \r\n				width:d.percent+"%", \r\n				backgroundColor:statusColors[alertState], \r\n				boxShadow:"1px 1px 1px #333 inset"\r\n			});\r\n			Object.assign(percentFull.style,{\r\n				display:"inline-block",\r\n				width:"4em",\r\n				fontWeight:"bold",\r\n				color:statusColors[alertState],\r\n			});\r\n		});\r\n	},\r\n	onFailure:function(transport,box){\r\n		if(transport.responseJSON && transport.responseJSON.error){\r\n			box.querySelector(".boxbody").innerHTML=transport.responseJSON.error;\r\n		} else {\r\n			box.querySelector(".boxbody").innerHTML="Could not get disk usage information.";\r\n		}\r\n	}\r\n}\r\n', '', '', '', 1);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(16, 'fi_oulu_MyPlatesByProject', 1, 'My latest plates, by project', 'A list of your most recent plates, by project', 2, 1, '/api/project/isarchived/0?sortby=name', 'document.fi_oulu_MyPlatesByProject_functions={\r\n	onSuccess:function(transport,box){\r\n		var projects=transport.responseJSON.rows;\r\n		var bb=box.querySelector(".boxbody");\r\n		bb.innerHTML="";\r\n		projects.forEach(function(p){\r\n			if(1*p.owner!=userId){ return; }\r\n			bb.treeItem({ \r\n				id:''fi_oulu_MyPlatesByProject_proj''+p.id,\r\n				header:''<a href="/project/''+p.id+''">''+p.name+''</a> (mine)'',\r\n				record:p,\r\n				updater:document.fi_oulu_MyPlatesByProject_functions.getPlatesForProject\r\n			});\r\n		});\r\n		projects.forEach(function(p){\r\n			if(1*p.owner==userId){ return; }\r\n			bb.treeItem({ \r\n				id:''fi_oulu_MyPlatesByProject_proj''+p.id,\r\n				header:''<a href="/project/''+p.id+''">''+p.name+''</a>'',\r\n				record:p,\r\n				updater:document.fi_oulu_MyPlatesByProject_functions.getPlatesForProject\r\n			});\r\n		});\r\n		\r\n	},\r\n\r\n	onFailure:function(transport,box){\r\n		if(transport.responseJSON && transport.responseJSON.error){\r\n			box.querySelector(".boxbody").innerHTML=transport.responseJSON.error;\r\n		} else {\r\n			box.querySelector(".boxbody").innerHTML="Could not get projects.";\r\n		}\r\n	},\r\n	\r\n	getPlatesForProject: function(clicked){\r\n		var treeItem=clicked.closest(".treeitem");\r\n		var projectid=treeItem.record.id;\r\n		new AjaxUtils.Request("/api/plate/projectid/"+projectid+"/ownerid/"+userId+"?pagesize=50&sortby=name&sortdescending=1",{\r\n			method:"get",\r\n			onSuccess:function(transport){ document.fi_oulu_MyPlatesByProject_functions.getPlatesForProject_onSuccess(transport,treeItem)},\r\n			onFailure:function(transport){ document.fi_oulu_MyPlatesByProject_functions.getPlatesForProject_onFailure(transport,treeItem)},\r\n		});\r\n	},\r\n	getPlatesForProject_onSuccess: function(transport,treeItem){\r\n		var t=ui.table({\r\n			cellTemplates:[''<a href="/plate/{{id}}">{{name}}</a>'']\r\n		}, transport.responseJSON, treeItem.querySelector(".treebody") );\r\n		t.querySelector("tr:last-child").remove();\r\n	},\r\n	getPlatesForProject_onFailure: function(transport,treeItem){\r\n		if(transport.responseJSON && transport.responseJSON.error){\r\n			treeItem.querySelector(".treebody").innerHTML=transport.responseJSON.error;\r\n		} else {\r\n			treeItem.querySelector(".treebody").innerHTML="Could not get plates.";\r\n		}\r\n	},\r\n\r\n}\r\n', '', '', '', 0);
INSERT INTO homepagebrick (id, name, version, title, description, height, width, apiurl, scriptblock, content, headertemplates, rowtemplates, adminonly) VALUES(17, 'fi_oulu_AllPlatesByProject', 1, 'Latest plates, by project', 'A list of the most recent plates (yours and others''), by project.', 2, 1, '/api/project/isarchived/0?sortby=name', 'document.fi_oulu_AllPlatesByProject_functions={\r\n	onSuccess:function(transport,box){\r\n		var projects=transport.responseJSON.rows;\r\n		var bb=box.querySelector(".boxbody");\r\n		bb.innerHTML="";\r\n		projects.forEach(function(p){\r\n			if(1*p.owner!=userId){ return; }\r\n			bb.treeItem({ \r\n				id:''fi_oulu_AllPlatesByProject_proj''+p.id,\r\n				header:''<a href="/project/''+p.id+''">''+p.name+''</a> (mine)'',\r\n				record:p,\r\n				updater:document.fi_oulu_AllPlatesByProject_functions.getPlatesForProject\r\n			});\r\n		});\r\n		projects.forEach(function(p){\r\n			if(1*p.owner==userId){ return; }\r\n			bb.treeItem({ \r\n				id:''fi_oulu_AllPlatesByProject_proj''+p.id,\r\n				header:''<a href="/project/''+p.id+''">''+p.name+''</a>'',\r\n				record:p,\r\n				updater:document.fi_oulu_AllPlatesByProject_functions.getPlatesForProject\r\n			});\r\n		});\r\n	},\r\n\r\n	onFailure:function(transport,box){\r\n		if(transport.responseJSON && transport.responseJSON.error){\r\n			box.querySelector(".boxbody").innerHTML=transport.responseJSON.error;\r\n		} else {\r\n			box.querySelector(".boxbody").innerHTML="Could not get projects.";\r\n		}\r\n	},\r\n	\r\n	getPlatesForProject: function(clicked){\r\n		var treeItem=clicked.closest(".treeitem");\r\n		var projectid=treeItem.record.id;\r\n		new AjaxUtils.Request("/api/plate/projectid/"+projectid+"?pagesize=25&pagenumber=1&sortby=name&sortdescending=1",{\r\n			method:"get",\r\n			onSuccess:function(transport){ document.fi_oulu_AllPlatesByProject_functions.getPlatesForProject_onSuccess(transport,treeItem)},\r\n			onFailure:function(transport){ document.fi_oulu_AllPlatesByProject_functions.getPlatesForProject_onFailure(transport,treeItem)},\r\n		});\r\n	},\r\n	getPlatesForProject_onSuccess: function(transport,treeItem){\r\n		var t=ui.table({\r\n			headers:[''Plate'',''Owner''],\r\n			cellTemplates:[''<a href="/plate/{{id}}">{{name}}</a>'',''<a href="/user/{{ownerid}}">{{ownername}}</a>'']\r\n		}, transport.responseJSON, treeItem.querySelector(".treebody") );\r\n		t.querySelector("tr:last-child").remove();\r\n	},\r\n	getPlatesForProject_onFailure: function(transport,treeItem){\r\n		if(transport.responseJSON && transport.responseJSON.error){\r\n			treeItem.querySelector(".treebody").innerHTML=transport.responseJSON.error;\r\n		} else {\r\n			treeItem.querySelector(".treebody").innerHTML="Could not get plates.";\r\n		}\r\n	},\r\n\r\n}\r\n', '', '', '', 0);
