-----------------------------------
-- Migration 003-004
-- Add support for storing 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
);

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 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;