DROP VIEW ga_snp;
DROP TABLE ga_study_snp CASCADE;
DROP TABLE ga_study_subpart CASCADE;
DROP TABLE ga_study CASCADE;


CREATE TABLE ga_study (
       ga_id       int NOT NULL PRIMARY KEY,
       short_name  TEXT NOT NULL,
       full_name   TEXT NOT NULL,
       description TEXT NOT NULL DEFAULT '',
       date 	   TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX ON ga_study(short_name);

CREATE TABLE ga_study_subpart (
       ga_subpart_id INT NOT NULL PRIMARY KEY,
       ga_id INT NOT NULL REFERENCES ga_study,
       short_name TEXT NOT NULL,
       full_name TEXT NOT NULL,
       description TEXT NOT NULL DEFAULT ''
);

CREATE UNIQUE INDEX ON ga_study_subpart(ga_id,short_name);
CREATE INDEX ON ga_study_subpart(ga_id);

CREATE TABLE ga_study_snp (
       snp_id int NOT NULL,
       ga_subpart_id INT NOT NULL REFERENCES ga_study_subpart,
       pvalue double precision,
       qvalue double precision,
       fdr double precision,
       significant BOOLEAN DEFAULT FALSE
);

CREATE UNIQUE INDEX ON ga_study_snp(snp_id,ga_subpart_id);
CREATE INDEX ON ga_study_snp(snp_id);
CREATE INDEX ON ga_study_snp(fdr);
CREATE INDEX ON ga_study_snp(qvalue);
CREATE INDEX ON ga_study_snp(pvalue);


CREATE OR REPLACE VIEW ga_snp AS
       SELECT gss.snp_id AS snp_id,
              gs.short_name AS study_name,
              gssp.short_name AS subpart_name,
              gss.pvalue AS pvalue,
              gss.qvalue AS qvalue,
              gss.fdr AS fdr,
              gss.significant AS significant
              FROM ga_study_snp gss
         JOIN ga_study_subpart gssp ON gss.ga_subpart_id = gssp.ga_subpart_id
         JOIN ga_study gs ON gssp.ga_id=gs.ga_id;
