-----------------------------------
-- Version 001
-----------------------------------

-- 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 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 NOT NULL,
  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 NOT NULL,
  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
);

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

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