-----------------------------------
-- Migration 002-003
----------------

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[])));

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

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