/*************************************************/
/***              DATA PROCESSING              ***/
/*************************************************/

CREATE TABLE DPR_MetaboliteIdentification
(
  ID            VARCHAR(50),
  analysis_ID   VARCHAR(50),
  CAS           VARCHAR(100),
  confidence    FLOAT,
  concentration FLOAT,

  PRIMARY KEY(ID, analysis_ID, CAS),

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


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

  PRIMARY KEY(ID)
);


CREATE TABLE DPR_MetaboliteGeneralDesc
(
  ID               VARCHAR(50) NOT NULL,
  CAS              VARCHAR(100),
  KEGG_ID          CHAR(6),
  PubChem_SID      INT,
  ChEBI_ID         INT,
  ThreeDMET_ID     CHAR(6),

  name             VARCHAR(500),
  iupac_name       VARCHAR(500),
  openeye_name     VARCHAR(500),
  cas_name         VARCHAR(500),
  systematic_name  VARCHAR(500),
  traditional_name VARCHAR(500),
  synonyms         TEXT,

  mol_formula      VARCHAR(500),
  mol_weight       FLOAT,

  can_smiles       VARCHAR(500),
  iso_smiles       VARCHAR(500),
  nist_inchi       VARCHAR(500),

  comment          TEXT,

  PRIMARY KEY(ID)
);


CREATE TABLE DPR_MetaboliteLocalDesc
(
  ID            VARCHAR(50) NOT NULL,
  metabolite_ID VARCHAR(50) NOT NULL,
  library_ID    VARCHAR(50) NOT NULL,
  comment       TEXT,

  PRIMARY KEY(ID),

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

  FOREIGN KEY(metabolite_ID) REFERENCES DPR_MetaboliteGeneralDesc(ID)
  ON DELETE RESTRICT ON UPDATE CASCADE,

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