/*************************************************/
/***           ANALYTICAL EXPERIMENT           ***/
/*************************************************/

CREATE TABLE ANL_Analysis
(
  ID            VARCHAR(50)   NOT NULL,
  experiment_ID VARCHAR(50)   NOT NULL,
  sample_ID     VARCHAR(50)   NOT NULL,
  name          VARCHAR(50),
  software_name VARCHAR(50),
  software_ver  VARCHAR(50),
  file_url      VARCHAR(100),
  file_type     VARCHAR(50),
  comment       TEXT,

  PRIMARY KEY(ID),

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

  FOREIGN KEY(sample_ID) REFERENCES SMP_Sample(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_gcPeakList
(
  ID      VARCHAR(50) NOT NULL,
  comment TEXT,

  PRIMARY KEY(ID),

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


CREATE TABLE ANL_gcPeak
(
  peak_list_ID          VARCHAR(50) NOT NULL,
  ID                    INT         NOT NULL,
  absolute_retention    FLOAT,
  relative_retention    FLOAT,
  retention_index       FLOAT,
  retention_lower_bound FLOAT,
  retention_upper_bound FLOAT,
  absolute_area         FLOAT,
  relative_area         FLOAT,

  PRIMARY KEY(peak_list_ID, ID),

  FOREIGN KEY(peak_list_ID) REFERENCES ANL_gcPeakList(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_MassSpectrum
(
  ID           VARCHAR(50)  NOT NULL,
  analysis_ID  VARCHAR(50)  NOT NULL,
  gc_peak_ID INT,
  tree_number  VARCHAR(200) NOT NULL,
  mz_min       INT,
  mz_max       INT,
  tic          INT,
  avg_peak     FLOAT,
  max_peak     INT,
  total_peaks  INT,
  peaks        TEXT,
  comment      TEXT,

  PRIMARY KEY(ID),

  FOREIGN KEY(analysis_ID) REFERENCES ANL_Analysis(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

  FOREIGN KEY(analysis_ID, gc_peak_ID) REFERENCES ANL_gcPeak(peak_list_ID, ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_msPeak
(
  ms_ID                     VARCHAR(50) NOT NULL,
  mz_value                  INT         NOT NULL,
  absolute_intensity        INT         NOT NULL,

  PRIMARY KEY(ms_ID, mz_value),

  FOREIGN KEY(ms_ID) REFERENCES ANL_MassSpectrum(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_lecoPeak
(
  peak_list_ID            VARCHAR(50) NOT NULL,
  peak_ID                 INT         NOT NULL,
  name                    VARCHAR(100),
  sample_type             VARCHAR(100),
  ratio                   FLOAT,
  unique_mass             INT,
  concentration           FLOAT,
  match                   INT,
  quant_masses            VARCHAR(200),
  quant_signal2noise      FLOAT,
  baseline_mod            BOOLEAN,
  quantification          VARCHAR(100),
  non_saturated_apex      FLOAT,
  signal2noise            FLOAT,
  noise                   FLOAT,
  purity                  FLOAT,
  hit                     VARCHAR(200),
  synonyms                VARCHAR(200),
  similarity              INT,
  reverse                 INT,
  reverse_probability     INT,
  CAS                     VARCHAR(100),
  library                 VARCHAR(100),
  libraryID               INT,
  formula                 VARCHAR(100),
  weight                  INT,
  contributor             VARCHAR(50),
  hit_no                  INT,
  height                  FLOAT,
  response_factor         FLOAT,
  integration_begin_time  FLOAT,
  integration_begin_scan  INT,
  integration_end_time    FLOAT,
  integration_end_scan    INT,
  converted_concentration FLOAT,
  concerns                VARCHAR(50),
  group_info              VARCHAR(100),
  analyte_ID              VARCHAR(100),
  ion_ratio_masses        VARCHAR(200),
  calculated_ion_ratio    FLOAT,
  ion_ratio_result        VARCHAR(100),
  calibration             VARCHAR(100),
  half_height_width       FLOAT,
  expected_ion_ratio      VARCHAR(100),
  unknown                 FLOAT,
  analyte_type            VARCHAR(100),
  expected_analyte_RT     FLOAT,
  conc_units              VARCHAR(20),
  conc_conv_units         VARCHAR(20),
  conc_concern            VARCHAR(50),
  sample_concentration    FLOAT,

  PRIMARY KEY(peak_list_ID, peak_ID),

  FOREIGN KEY(peak_list_ID, peak_ID) REFERENCES ANL_gcPeak(peak_list_ID, ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_agilentPeak
(
  peak_list_ID           VARCHAR(50) NOT NULL,
  peak_ID                INT         NOT NULL,
  unique_mass            INT,
  integration_start_scan INT,
  apex_scan              INT,
  derivatives            VARCHAR(200),
  relative_intensity     FLOAT,

  PRIMARY KEY(peak_list_ID, peak_ID),

  FOREIGN KEY(peak_list_ID, peak_ID) REFERENCES ANL_gcPeak(peak_list_ID, ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_ftirSpectrum
(
  ID      VARCHAR(50) NOT NULL,
  comment TEXT,

  PRIMARY KEY(ID),

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


CREATE TABLE ANL_ftirPeak
(
  spectrum_ID VARCHAR(50) NOT NULL,
  wave_number FLOAT       NOT NULL, /* X point */
  absorbance  FLOAT       NOT NULL, /* Y point */

  PRIMARY KEY(spectrum_ID, wave_number),

  FOREIGN KEY(spectrum_ID) REFERENCES ANL_ftirSpectrum(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);


CREATE TABLE ANL_RamanSpectrum
(
  ID      VARCHAR(50) NOT NULL,
  comment TEXT,

  PRIMARY KEY(ID),

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


CREATE TABLE ANL_RamanPeak
(
  spectrum_ID VARCHAR(50) NOT NULL,
  raman_shift FLOAT       NOT NULL, /* X point */
  intensity   FLOAT       NOT NULL, /* Y point */

  PRIMARY KEY(spectrum_ID, raman_shift),

  FOREIGN KEY(spectrum_ID) REFERENCES ANL_ramanSpectrum(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE
);