/*************************************************/
/***                   ADMIN                   ***/
/*************************************************/

CREATE TABLE ADM_Lab
(
  ID        VARCHAR(50)   NOT NULL,
  name      VARCHAR(200)  NOT NULL,
  address   VARCHAR(200),
  phone     VARCHAR(50),
  e_mail    VARCHAR(50)   NOT NULL,
  person_ID VARCHAR(50),
  comment   TEXT,

  PRIMARY KEY(ID)
);


CREATE TABLE ADM_Person
(
  ID          VARCHAR(50)   NOT NULL,
  name        VARCHAR(50)   NOT NULL,
  affiliation VARCHAR(50)   NOT NULL,
  position    VARCHAR(50),
  address     VARCHAR(200),
  phone       VARCHAR(50),
  e_mail      VARCHAR(50)   NOT NULL,
  other       TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(affiliation) REFERENCES ADM_Lab(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_ExperimentProtocol
(
  ID              VARCHAR(50) NOT NULL,
  experiment_type VARCHAR(10) NOT NULL,
  method          VARCHAR(50) NOT NULL,
  parameters      TEXT,
  xml_schema      VARCHAR(50),
  comment         TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(experiment_type) REFERENCES VOC_experiment_type(entry)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(method) REFERENCES VOC_method(entry)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(xml_schema) REFERENCES XML_xmlSchema(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_ExperimentSet
(
  ID              VARCHAR(50)   NOT NULL,
  name            VARCHAR(50)   NOT NULL,
  experiment_type VARCHAR(10)   NOT NULL,
  description     TEXT,
  lab_ID          VARCHAR(50)   NOT NULL,
  protocol_ID     VARCHAR(50)   NOT NULL,
  conductor_ID    VARCHAR(50)   NOT NULL,
  comment         TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(lab_ID) REFERENCES ADM_Lab(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(protocol_ID) REFERENCES ADM_ExperimentProtocol(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(conductor_ID) REFERENCES ADM_Person(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_Experiment
(
  ID         VARCHAR(50) NOT NULL,
  name       VARCHAR(50),
  set_ID     VARCHAR(50) NOT NULL,
  start_date DATE        NOT NULL,
  start_time TIME,
  end_date   DATE        NOT NULL,
  end_time   TIME,
  comment    TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(set_ID) REFERENCES ADM_ExperimentSet(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_ExperimentStudy
(
  ID                 VARCHAR(50) NOT NULL,
  name               VARCHAR(50) NOT NULL,
  description        TEXT,
  comment            TEXT,

  PRIMARY KEY(ID)
);


CREATE TABLE ADM_ExperimentPipeline
(
  study_ID VARCHAR(50) NOT NULL,
  step     INTEGER     NOT NULL,
  set_ID   VARCHAR(50) NOT NULL,

  PRIMARY KEY(study_ID, set_ID),

  FOREIGN KEY(study_ID) REFERENCES ADM_ExperimentStudy(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(set_ID) REFERENCES ADM_ExperimentSet(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_Instrument
(
  ID                  VARCHAR(50) NOT NULL,
  name                VARCHAR(50) NOT NULL,
  manufacturer        VARCHAR(50),
  model               VARCHAR(50),
  serial_number       VARCHAR(50),
  maintenance_history TEXT,
  comment             TEXT,

  PRIMARY KEY(ID)
);


CREATE TABLE ADM_InstrumentUsed
(
  set_ID        VARCHAR(50) NOT NULL,
  instrument_ID VARCHAR(50) NOT NULL,

  PRIMARY KEY(set_ID, instrument_ID),

  FOREIGN KEY(set_ID) REFERENCES ADM_ExperimentSet(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(instrument_ID) REFERENCES ADM_Instrument(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_Program
(
  ID           VARCHAR(50) NOT NULL,
  name         VARCHAR(50) NOT NULL,
  manufacturer VARCHAR(50),
  version      VARCHAR(50),
  licence      VARCHAR(50),
  user_ID      VARCHAR(50),
  key_code     VARCHAR(50),
  expiry_date  DATE,
  methodology  VARCHAR(50) NOT NULL,
  platforms    VARCHAR(50),
  files_url    VARCHAR(50),
  comment      TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(user_ID) REFERENCES ADM_Person(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(methodology) REFERENCES VOC_Method(entry)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ADM_ProgramUsed
(
  experiment_ID VARCHAR(50) NOT NULL,
  program_ID    VARCHAR(50) NOT NULL,

  PRIMARY KEY(experiment_ID, program_ID),

  FOREIGN KEY(experiment_ID) REFERENCES ADM_Experiment(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(program_ID) REFERENCES ADM_Program(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);