-----------------------------------
-- Version 004
-----------------------------------

-- Create domain types section -------------------------------------------------

CREATE DOMAIN TECH_ID AS BIGINT;

CREATE DOMAIN CODE AS VARCHAR(40);

CREATE DOMAIN FILE_NAME as VARCHAR(255);

CREATE DOMAIN FILE_PATH as VARCHAR(1000);

CREATE DOMAIN SHORT_LABEL as VARCHAR(20); 

CREATE DOMAIN LONG_LABEL as VARCHAR(100); 

CREATE DOMAIN CHAR as VARCHAR(1);

CREATE DOMAIN ms_quantification_software_kind AS character varying(40)
	CONSTRAINT ms_quantification_software_kind_check CHECK (((VALUE)::text = 
		ANY ((ARRAY['msSoft'::character varying, 'Xcalibur'::character varying, 
								'Analyst'::character varying, 'MassHunter'::character varying])::text[])));


-- Create tables section -------------------------------------------------

-- Table EXPERIMENTS

CREATE TABLE EXPERIMENTS (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,
  NAME LONG_LABEL NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE (PERM_ID)
);

-- Table SAMPLES

CREATE TABLE SAMPLES (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,
  NAME LONG_LABEL NOT NULL,
  EXPE_ID TECH_ID NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE (PERM_ID),
  CONSTRAINT FK_SAMPLE_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Table DATA_SETS

CREATE TABLE DATA_SETS (
  ID BIGSERIAL NOT NULL,
  PERM_ID CODE NOT NULL,  
  EXPE_ID TECH_ID NOT NULL,
  SAMP_ID TECH_ID,
  PRIMARY KEY (ID),
  UNIQUE (PERM_ID),
  CONSTRAINT FK_DATA_SET_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_DATA_SET_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Table EIC_MS_RUNS

CREATE TABLE EIC_MS_RUNS (
  ID BIGSERIAL NOT NULL,
  EXPE_ID TECH_ID NOT NULL,
  SAMP_ID TECH_ID,
  DS_ID TECH_ID NOT NULL,
  RAW_DATA_FILE_NAME FILE_NAME NOT NULL,
  RAW_DATA_FILE_PATH FILE_PATH DEFAULT NULL,
  ACQUISITION_DATE TIMESTAMP DEFAULT NULL,
  INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,
  INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,
  INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,
  METHOD_IONISATION SHORT_LABEL DEFAULT NULL,
  METHOD_SEPARATION LONG_LABEL DEFAULT NULL,
  OPERATOR SHORT_LABEL DEFAULT NULL,
  MS_RUN_ID BIGINT DEFAULT NULL,
  SET_ID TECH_ID DEFAULT NULL,
  START_TIME REAL NOT NULL,
  END_TIME REAL NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_EIC_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_EIC_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_EIC_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX EIC_MS_RUNS_FK_DS_ID_IDX ON EIC_MS_RUNS(DS_ID);
CREATE INDEX EIC_MS_RUNS_FK_SAMP_ID_IDX ON EIC_MS_RUNS(SAMP_ID);
CREATE INDEX EIC_MS_RUNS_FK_EXPE_ID_IDX ON EIC_MS_RUNS(EXPE_ID);

-- Table EIC_CHROMATOGRAMS

CREATE TABLE EIC_CHROMATOGRAMS (
  ID BIGSERIAL NOT NULL,
  EIC_MS_RUN_ID TECH_ID NOT NULL,
  Q1_MZ REAL NOT NULL,
  Q3_LOW_MZ REAL NOT NULL,
  Q3_HIGH_MZ REAL NOT NULL,
  LABEL LONG_LABEL DEFAULT NULL,
  POLARITY CHAR DEFAULT NULL,
  RUN_TIMES TEXT NOT NULL,
  INTENSITIES TEXT NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_EIC_CHROMATOGRAM_1 FOREIGN KEY (EIC_MS_RUN_ID) REFERENCES EIC_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX EIC_CHROMATOGRAM_FK_IDX ON EIC_CHROMATOGRAMS(EIC_MS_RUN_ID);

-- Table FIA_MS_RUNS

CREATE TABLE FIA_MS_RUNS (
  ID BIGSERIAL NOT NULL,
  EXPE_ID TECH_ID NOT NULL,
  SAMP_ID TECH_ID,
  DS_ID TECH_ID NOT NULL,
  RAW_DATA_FILE_NAME FILE_NAME NOT NULL,
  RAW_DATA_FILE_PATH FILE_PATH DEFAULT NULL,
  ACQUISITION_DATE TIMESTAMP DEFAULT NULL,
  INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,
  INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,
  INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,
  METHOD_IONISATION SHORT_LABEL DEFAULT NULL,
  METHOD_SEPARATION LONG_LABEL DEFAULT NULL,
  POLARITY VARCHAR(1) DEFAULT NULL,
  LOW_MZ REAL NOT NULL,
  HIGH_MZ REAL NOT NULL,
  INTERNAL_STANDARD REAL NOT NULL,
  -- Is this a good name?
  OD REAL NOT NULL,
  OPERATOR SHORT_LABEL DEFAULT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_FIA_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_EIA_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_EIA_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX FIA_MS_RUNS_FK_DS_ID_IDX ON FIA_MS_RUNS(DS_ID);
CREATE INDEX FIA_MS_RUNS_FK_SAMP_ID_IDX ON FIA_MS_RUNS(SAMP_ID);
CREATE INDEX FIA_MS_RUNS_FK_EXPE_ID_IDX ON FIA_MS_RUNS(EXPE_ID);

-- Table FIA_PROFILES

CREATE TABLE FIA_PROFILES (
  ID BIGSERIAL NOT NULL,
  FIA_MS_RUN_ID TECH_ID NOT NULL,
  LOW_MZ REAL NOT NULL,
  HIGH_MZ REAL NOT NULL,
  MZ TEXT NOT NULL,
  INTENSITIES TEXT NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_FIA_PROFILE_1 FOREIGN KEY (FIA_MS_RUN_ID) REFERENCES FIA_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX FIA_PROFILE_I_ID on FIA_PROFILES(FIA_MS_RUN_ID);
CREATE INDEX FIA_PROFILE_I_ID_MZ on FIA_PROFILES(FIA_MS_RUN_ID, LOW_MZ, HIGH_MZ);

-- Table FIA_CENTROIDS

CREATE TABLE FIA_CENTROIDS (
  ID BIGSERIAL NOT NULL,
  FIA_MS_RUN_ID TECH_ID NOT NULL,
  MZ REAL NOT NULL,
  INTENSITY REAL NOT NULL,
  CORRELATION REAL NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_FIA_CENTROID_1 FOREIGN KEY (FIA_MS_RUN_ID) REFERENCES FIA_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX FIA_CENTROID_I_ID ON FIA_CENTROIDS(FIA_MS_RUN_ID);
CREATE INDEX FIA_CENTROID_I_ID_MZ ON FIA_CENTROIDS(FIA_MS_RUN_ID, MZ);
 
-- Table MS_QUANTIFICATIONS

CREATE TABLE MS_QUANTIFICATIONS (
  ID BIGSERIAL NOT NULL,
  DS_ID TECH_ID NOT NULL,
  EXPE_ID TECH_ID NOT NULL,
  SOURCE MS_QUANTIFICATION_SOFTWARE_KIND NOT NULL,
  VALID BOOLEAN NOT NULL,
  COMMENT TEXT,
  REGISTRATOR SHORT_LABEL DEFAULT NULL,
  REGISTRATION_DATE TIMESTAMP DEFAULT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT MS_QUANTIFICATION_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT MS_QUANTIFICATION_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX MS_QUANTIFICATION_FK_DS_ID_IDX ON MS_QUANTIFICATIONS(DS_ID);
CREATE INDEX MS_QUANTIFICATION_FK_EXPE_ID_IDX ON MS_QUANTIFICATIONS(EXPE_ID);

-- Table MS_QUANT_CONCENTRATIONS

CREATE TABLE MS_QUANT_CONCENTRATIONS (
  ID BIGSERIAL NOT NULL,
  MS_QUANTIFICATION_ID TECH_ID NOT NULL,
  PARENT_DS_PERM_ID CODE NOT NULL,
  AMOUNT REAL NOT NULL,
  UNIT SHORT_LABEL NOT NULL,
  VALID BOOLEAN NOT NULL,
  COMMENT TEXT,
  RETENTION_TIME REAL NOT NULL,
  Q1 REAL NOT NULL,
  Q3 REAL NOT NULL,
  INTERNAL_STANDARD VARCHAR(30) NOT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK_QUANT_CONCENTRATIONS_1 FOREIGN KEY (MS_QUANTIFICATION_ID) REFERENCES MS_QUANTIFICATIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Table MS_QUANT_COMPOUND

CREATE TABLE MS_QUANT_COMPOUNDS (
	ID BIGSERIAL NOT NULL,
  MS_QUANT_CONCENTRATION_ID TECH_ID NOT NULL,
  COMPOUND_ID TECH_ID NOT NULL,
  CONSTRAINT FK_QUANT_COMPOUND_1 FOREIGN KEY (MS_QUANT_CONCENTRATION_ID) REFERENCES MS_QUANT_CONCENTRATIONS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

--------------- mzXML file content
CREATE TABLE MZ_MS_RUNS (
  ID BIGSERIAL NOT NULL,
  EXPE_ID TECH_ID NOT NULL,
  SAMP_ID TECH_ID,
  DS_ID TECH_ID NOT NULL,

  INSTRUMENT_TYPE SHORT_LABEL DEFAULT NULL,
  INSTRUMENT_MANUFACTURER LONG_LABEL DEFAULT NULL,
  INSTRUMENT_MODEL LONG_LABEL DEFAULT NULL,
  METHOD_IONISATION SHORT_LABEL DEFAULT NULL,

  PRIMARY KEY (ID), 
  CONSTRAINT FK_MZ_MS_RUN_1 FOREIGN KEY (EXPE_ID) REFERENCES EXPERIMENTS (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_MZ_MS_RUN_2 FOREIGN KEY (SAMP_ID) REFERENCES SAMPLES (ID) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT FK_MZ_MS_RUN_3 FOREIGN KEY (DS_ID) REFERENCES DATA_SETS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX MZ_MS_RUNS_FK_DS_ID_IDX ON MZ_MS_RUNS(DS_ID);
CREATE INDEX MZ_MS_RUNS_FK_SAMP_ID_IDX ON MZ_MS_RUNS(SAMP_ID);
CREATE INDEX MZ_MS_RUNS_FK_EXPE_ID_IDX ON MZ_MS_RUNS(EXPE_ID);


CREATE TABLE MZ_SCANS (
  ID BIGSERIAL NOT NULL,
	MZ_MS_RUN_ID TECH_ID NOT NULL,

	NUMBER INTEGER NOT NULL CHECK (NUMBER > 0),
	LEVEL INTEGER NOT NULL CHECK (LEVEL > 0),
	PEAKS_COUNT INTEGER NOT NULL CHECK (PEAKS_COUNT >= 0),
	POLARITY CHAR (1) CHECK (POLARITY = '+' OR POLARITY = '-'),
	SCAN_TYPE VARCHAR(10),
	COLLISION_ENERGY REAL, 
	LOW_MZ REAL,
	HIGH_MZ REAL,
	RETENTION_TIME REAL,

	PRECURSOR1_MZ REAL,
	PRECURSOR1_INTENSITY REAL,
	PRECURSOR1_CHARGE INTEGER CHECK (PRECURSOR1_CHARGE > 0),

	PRECURSOR2_MZ REAL,
	PRECURSOR2_INTENSITY REAL,
	PRECURSOR2_CHARGE INTEGER CHECK (PRECURSOR2_CHARGE > 0),
	
	CHECK (
			(PRECURSOR1_MZ IS NULL AND PRECURSOR1_INTENSITY IS NULL AND PRECURSOR1_CHARGE IS NULL) OR 
			(PRECURSOR1_MZ IS NOT NULL AND PRECURSOR1_INTENSITY IS NOT NULL) 
	),
	CHECK (
			(PRECURSOR2_MZ IS NULL AND PRECURSOR2_INTENSITY IS NULL AND PRECURSOR2_CHARGE IS NULL) OR 
			(PRECURSOR2_MZ IS NOT NULL AND PRECURSOR2_INTENSITY IS NOT NULL) 
	),
	PRIMARY KEY (ID),
	CONSTRAINT FK_MZ_MS_RUN FOREIGN KEY (MZ_MS_RUN_ID) REFERENCES MZ_MS_RUNS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE MZ_PEAKS (
 MZ_SCAN_ID TECH_ID NOT NULL,
 MZ REAL NOT NULL,
 INTENSITY REAL NOT NULL,
 CONSTRAINT FK_MZ_SCAN FOREIGN KEY (MZ_SCAN_ID) REFERENCES MZ_SCANS (ID) ON DELETE CASCADE ON UPDATE CASCADE
);

--------------- END mzXML

-- Table EVENTS

CREATE TABLE EVENTS (
  LAST_SEEN_DELETION_EVENT_ID TECH_ID NOT NULL
);

GRANT SELECT ON TABLE EXPERIMENTS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE SAMPLES TO GROUP metabol_readonly;
GRANT SELECT ON TABLE DATA_SETS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE EIC_MS_RUNS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE EIC_CHROMATOGRAMS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE FIA_MS_RUNS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE FIA_PROFILES TO GROUP metabol_readonly;
GRANT SELECT ON TABLE FIA_CENTROIDS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MS_QUANTIFICATIONS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MS_QUANT_CONCENTRATIONS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MS_QUANT_COMPOUNDS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE EVENTS TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE EXPERIMENTS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE SAMPLES_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE DATA_SETS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE EIC_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE EIC_CHROMATOGRAMS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE FIA_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE FIA_PROFILES_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE FIA_CENTROIDS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE MS_QUANTIFICATIONS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE MS_QUANT_CONCENTRATIONS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MZ_MS_RUNS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MZ_SCANS TO GROUP metabol_readonly;
GRANT SELECT ON TABLE MZ_PEAKS TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE MZ_MS_RUNS_ID_SEQ TO GROUP metabol_readonly;
GRANT SELECT ON SEQUENCE MZ_SCANS_ID_SEQ TO GROUP metabol_readonly;

GRANT ALL PRIVILEGES ON TABLE EXPERIMENTS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE SAMPLES TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE DATA_SETS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE EIC_MS_RUNS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE EIC_CHROMATOGRAMS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE FIA_MS_RUNS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE FIA_PROFILES TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE FIA_CENTROIDS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MS_QUANTIFICATIONS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MS_QUANT_CONCENTRATIONS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MS_QUANT_COMPOUNDS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE EVENTS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE EXPERIMENTS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE SAMPLES_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE DATA_SETS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE EIC_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE EIC_CHROMATOGRAMS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE FIA_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE FIA_PROFILES_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE FIA_CENTROIDS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE MS_QUANTIFICATIONS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE MS_QUANT_CONCENTRATIONS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MZ_MS_RUNS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MZ_SCANS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON TABLE MZ_PEAKS TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE MZ_MS_RUNS_ID_SEQ TO GROUP metabol_readwrite;
GRANT ALL PRIVILEGES ON SEQUENCE MZ_SCANS_ID_SEQ TO GROUP metabol_readwrite;
