drop database proteomics;
create database proteomics;
connect proteomics;


CREATE TABLE Equipment
(
  Equipmentid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Name	VARCHAR(200),
  PRIMARY KEY(Equipmentid)
);

INSERT INTO Equipment VALUES(NULL,'Bruker Daltonics Ultraflex MALDI-TOF');
INSERT INTO Equipment VALUES(NULL,'FTMS');
INSERT INTO Equipment VALUES(NULL,'MALDI-TOF LC MS');
INSERT INTO Equipment VALUES(NULL,'LTQ Orbitrap LC MS');
INSERT INTO Equipment VALUES(NULL,'Bruker Ion Trap');


CREATE TABLE Person
(
  Personid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Name	VARCHAR(200),
  PRIMARY KEY(Personid)
);

INSERT INTO Person VALUES(NULL,'person 1');


CREATE TABLE Group_
(
  Group_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Group_code VARCHAR(100) UNIQUE,
  Name	   VARCHAR(200),
  PRIMARY KEY(Group_id)
);

INSERT INTO Group_ VALUES(NULL,'1', 'group 1');


CREATE TABLE Origin
(
  Originid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Name	VARCHAR(200),
  PRIMARY KEY(Originid)
);
INSERT INTO Origin VALUES(NULL,'organ 1');


CREATE TABLE Experiment
(
  Experimentid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Equipmentid  INT UNSIGNED,
  Name	       VARCHAR(200),
  Date	       DATE,
  Year	       VARCHAR(4),
  PRIMARY KEY(Experimentid),
  FOREIGN KEY(Equipmentid) REFERENCES Equipment(Equipmentid) ON DELETE SET NULL 
);


CREATE TABLE Material
(
  Materialid  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Name	  VARCHAR(200),
  PRIMARY KEY(Materialid)
);

INSERT INTO Material VALUES(NULL,'material 1');


CREATE TABLE Sample
(
  Sampleid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Materialid INT UNSIGNED, 
  Personid INT UNSIGNED,
  Originid INT UNSIGNED,
  Sample_code VARCHAR(100) UNIQUE,
  Name	VARCHAR(200),
  PRIMARY KEY(Sampleid),
  FOREIGN KEY(Materialid) REFERENCES Material(Materialid) ON DELETE SET NULL,
  FOREIGN KEY(Personid) REFERENCES Person(Personid) ON DELETE SET NULL,
  FOREIGN KEY(Originid) REFERENCES Origin(Originid) ON DELETE SET NULL
);

INSERT INTO Sample VALUES(NULL,1,1,1,'sample1','name 1');


CREATE TABLE Result
(
  Resultid      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Experimentid  INT UNSIGNED NOT NULL,
  Sampleid      INT UNSIGNED,
  Group_id      INT UNSIGNED,
  filenumber    INT UNSIGNED,
  lcfraction    INT UNSIGNED,
  Date	        DATE,
  Time	        TIME,
  Year	        VARCHAR(4),
  Size_KB       FLOAT(16,3),
  File	        VARCHAR(200) NOT NULL,
  Type          VARCHAR(16),
  Offset_LC_MS  FLOAT(16,3),
  retentiontime FLOAT(16,4),
  Quantilethreshold VARCHAR(8),	
  PRIMARY KEY(Resultid),
  FOREIGN KEY(Experimentid) REFERENCES Experiment(Experimentid) ON DELETE CASCADE,
  FOREIGN KEY(Sampleid) REFERENCES Sample(Sampleid) ON DELETE SET NULL,
  FOREIGN KEY(Group_id) REFERENCES Group_(Group_id) ON DELETE SET NULL 
);

CREATE TABLE Systemcode
(
  Systemcodeid     	INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Code			VARCHAR(16),
  Description      	VARCHAR(100),	
  PRIMARY KEY(Systemcodeid),
  Unique(Code)
);

INSERT INTO Systemcode VALUES(NULL,'CALMASSES','Calibration Masses');

CREATE TABLE SystemCodeItem
(
  SystemCodeItemid      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Systemcodeid       	INT UNSIGNED,
  ItemCode        	VARCHAR(16),
  Description	        VARCHAR(50),
  PRIMARY KEY(SystemCodeItemid),
  FOREIGN KEY(Systemcodeid) REFERENCES Systemcode(Systemcodeid) ON DELETE CASCADE,
  Unique(ItemCode)
);

INSERT INTO SystemCodeItem VALUES(NULL,1,'ALBMASSES','Albumine Masses');


CREATE TABLE Unit
(
  Unitid      	INT UNSIGNED NOT NULL AUTO_INCREMENT,
  Type		VARCHAR(16),
  UnitValue	VARCHAR(16),
  PRIMARY KEY(Unitid)
);
INSERT INTO Unit VALUES(NULL,'mass','m/z');


CREATE TABLE ItemValue
(
  ItemValueid      	INT UNSIGNED NOT NULL AUTO_INCREMENT,
  SystemCodeItemid      INT UNSIGNED,
  Unitid                INT UNSIGNED,
  Itemvalue        	VARCHAR(50),
  PRIMARY KEY(ItemValueid),
  FOREIGN KEY(SystemCodeItemid) REFERENCES SystemCodeItem(SystemCodeItemid) ON DELETE CASCADE,
  FOREIGN KEY(Unitid) REFERENCES Unit(Unitid) ON DELETE SET NULL
);

INSERT INTO ItemValue VALUES(NULL,1,1,'960.5631');
INSERT INTO ItemValue VALUES(NULL,1,1,'1000.6043');
INSERT INTO ItemValue VALUES(NULL,1,1,'1149.6156');
INSERT INTO ItemValue VALUES(NULL,1,1,'1511.8433');
INSERT INTO ItemValue VALUES(NULL,1,1,'2045.0959');


show tables;