--
-- this table contains the reference to the entry in the inspire database:
-- we use the inspire API to extract info like author, Journal etc from inspire
-- using the ReferenceUpload java application 
--
CREATE TABLE INSPIREREFERENCE(
   INSPIREID  INTEGER NOT NULL PRIMARY KEY 
);
ALTER TABLE INSPIREREFERENCE OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON  INSPIREREFERENCE TO G4VALWRITER;
GRANT SELECT ON INSPIREREFERENCE  TO G4VALREADER;
INSERT INTO PUBLIC.INSPIREREFERENCE (INSPIREID) VALUES (287791);
INSERT INTO PUBLIC.INSPIREREFERENCE (INSPIREID) VALUES (886780);
INSERT INTO PUBLIC.INSPIREREFERENCE (INSPIREID) VALUES (532743);
INSERT INTO PUBLIC.INSPIREREFERENCE (INSPIREID) VALUES (695147);
---
--- The following table for reference 
--- (e.g. data extracted from the inspire database is cashed here to guarantee fast access)
--
CREATE TABLE REFERENCE(
   REFID      INTEGER NOT NULL PRIMARY KEY,
   INSPIREID  INTEGER NOT NULL UNIQUE,
   AUTHORS    CHARACTER VARYING(100)[],  -- list of authors 
   TITLE      TEXT,                      -- title of the paper
   JOURNAL    CHARACTER VARYING(100),    -- name of the scientific journal
   ERN        CHARACTER VARYING(100),    -- electronic-resource-num
   PAGES      CHARACTER VARYING(50),     -- page numbers
   VOLUME     CHARACTER VARYING(50),     -- journal volume
   YEA        INTEGER,                   -- year of publication
   ABSTR      TEXT,                      -- abstract
   KEYWORDS   CHARACTER VARYING(50)[],   -- keywords 
   LINKURL    TEXT             -- link to the e.g. inspire url for this paper 
);
ALTER TABLE REFERENCE OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON  REFERENCE TO g4valwriter;
GRANT SELECT ON REFERENCE TO g4valreader;
---
--- stuff representing the data (e.g. 1D/2d Histograms, 1D/2D Graphs)
---
CREATE TABLE DATATYPES(
DTYPE INTEGER PRIMARY KEY,
DESCRIPTION CHARACTER VARYING(50) UNIQUE
);
ALTER TABLE DATATYPES OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON  DATATYPES TO G4VALWRITER;
GRANT SELECT ON  DATATYPES TO G4VALREADER;

---
--- Table representing the data (e.g. 1D/2d Histograms, 1D/2D Graphs)
---
CREATE TABLE DATATABLE(
       DTID SERIAL PRIMARY KEY,
       DTYPE       INTEGER  REFERENCES DATATYPES(DTYPE),
       title       CHARACTER VARYING(100),
       npoints     INTEGER,
       axis_title  CHARACTER VARYING(100)[],
       val REAL[],
       err_stat_plus REAL[],
       err_stat_minus REAL[],
       err_sys_plus REAL[],
       err_sys_minus REAL[],
       bin_min REAL[],
       bin_max REAL[]
);
ALTER TABLE DATATABLE OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON  DATATABLE TO G4VALWRITER;
GRANT SELECT ON  DATATABLE TO G4VALREADER;
--
-- Name: scores; Type: TABLE; Schema: public; Owner: g4valwriter; Tablespace: 
--
CREATE TABLE SCORES (
    SCOREID  SERIAL PRIMARY KEY,
    SCORE CHARACTER VARYING(10),
    STYPE CHARACTER VARYING(10), 
    UNIQUE (SCORE,STYPE)
);
ALTER TABLE SCORES OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON SCORES  TO G4VALWRITER;
GRANT SELECT ON  SCORES TO G4VALREADER;
INSERT INTO PUBLIC.SCORES ( SCORE,STYPE) VALUES ( 'passed','expert');
--
--  dictionary table defining the ACCESS of a test result
--
CREATE TABLE ACCESS (
    ACCESSID  SERIAL PRIMARY KEY,
    ACCESS CHARACTER VARYING(50) NOT NULL UNIQUE
);
ALTER TABLE ACCESS OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON ACCESS  TO G4VALWRITER;
GRANT SELECT ON  ACCESS TO G4VALREADER;
INSERT INTO PUBLIC.ACCESS (ACCESS) VALUES ('public');                  -- visible to everyone
INSERT INTO PUBLIC.ACCESS (ACCESS) VALUES ('internal');                -- visible only to geant 4 caloborators
INSERT INTO PUBLIC.ACCESS (ACCESS) VALUES ('temporary');               -- to be deleted

CREATE TABLE MCTOOL(
   MCID SERIAL PRIMARY KEY,	
   MCNAME VARCHAR(20) NOT NULL,
   UNIQUE (MCNAME)
);

ALTER TABLE  MCTOOL OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON MCTOOL  TO G4VALWRITER;
GRANT SELECT ON MCTOOL TO G4VALREADER;
insert into PUBLIC.MCTOOL(MCNAME) VALUES ('Experiment');
insert into PUBLIC.MCTOOL(MCNAME) VALUES ('Geant4');
insert into PUBLIC.MCTOOL(MCNAME) VALUES ('Genie');



CREATE TABLE VERSION(
   VERID SERIAL PRIMARY KEY,
   TAG  CHARACTER VARYING(50) NOT NULL UNIQUE
);
ALTER TABLE  VERSION OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON VERSION  TO g4valwriter;
GRANT SELECT ON VERSION TO g4valreader;

insert into PUBLIC.VERSION(TAG) VALUES ('geant4.10.1.p02');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.10.1.p01');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.10.0.p01');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.10.0.p02');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.10.0.p03');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.9.6.p01');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.9.6.p02');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.9.6.p03');
insert into PUBLIC.VERSION(TAG) VALUES ('geant4.9.6.p04');

CREATE TABLE MODEL(
   MODID SERIAL PRIMARY KEY,
   MODEL  CHARACTER VARYING(50) UNIQUE NOT NULL
);
ALTER TABLE  MODEL OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON  MODEL TO g4valwriter;
GRANT SELECT ON MODEL TO g4valreader;

insert into PUBLIC.MODEL(MODEL) VALUES ('INCLXX');
insert into PUBLIC.MODEL(MODEL) VALUES ('BIC');
insert into PUBLIC.MODEL(MODEL) VALUES ('BERTINI');
insert into PUBLIC.MODEL(MODEL) VALUES ('FTFP_BERT');
insert into PUBLIC.MODEL(MODEL) VALUES ('QGSP_BERT');
insert into PUBLIC.MODEL(MODEL) VALUES ('FTFP_BERT_HP');

CREATE TABLE PARTICLE(
   PID   SERIAL PRIMARY KEY,
   PDGID INTEGER UNIQUE NOT NULL,     -- PDG ID
   PNAME  CHARACTER VARYING(50) UNIQUE
);
ALTER TABLE  PARTICLE OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON PARTICLE  TO g4valwriter;
GRANT SELECT ON PARTICLE  TO g4valreader;

insert into PUBLIC.PARTICLE(PDGID,PNAME) VALUES (211,'pi+');
insert into PUBLIC.PARTICLE(PDGID,PNAME) VALUES (11,'e-');
insert into PUBLIC.PARTICLE(PDGID,PNAME) VALUES (13,'mu-');
insert into PUBLIC.PARTICLE(PDGID,PNAME) VALUES (321,'K+');
insert into PUBLIC.PARTICLE(PDGID,PNAME) VALUES (2212,'p');
insert into PUBLIC.PARTICLE(PDGID,PNAME) VALUES (2112,'n');

CREATE TABLE MATERIAL(
   MID SERIAL PRIMARY KEY,
   MNAME  CHARACTER VARYING(50) UNIQUE
);
ALTER TABLE  MATERIAL OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON VERSION  TO g4valwriter;
GRANT SELECT ON VERSION TO g4valreader;

insert into PUBLIC.MATERIAL(MNAME) VALUES ('Cu');
insert into PUBLIC.MATERIAL(MNAME) VALUES ('Ta');
insert into PUBLIC.MATERIAL(MNAME) VALUES ('Fe');
insert into PUBLIC.MATERIAL(MNAME) VALUES ('Steel');
insert into PUBLIC.MATERIAL(MNAME) VALUES ('H2O');
insert into PUBLIC.MATERIAL(MNAME) VALUES ('H');

CREATE TABLE OBSERVABLE(
   OID SERIAL PRIMARY KEY,
   ONAME  CHARACTER VARYING(50) UNIQUE
);
ALTER TABLE  OBSERVABLE OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON VERSION  TO g4valwriter;
GRANT SELECT ON VERSION TO g4valreader;

insert into PUBLIC.OBSERVABLE(ONAME) VALUES ('Cross Section');
insert into PUBLIC.OBSERVABLE(ONAME) VALUES ('differential cross section');
insert into PUBLIC.OBSERVABLE(ONAME) VALUES ('momentum spectrum');

CREATE TABLE REACTION(
   RID SERIAL PRIMARY KEY,
   RNAME  CHARACTER VARYING(50) UNIQUE
);
ALTER TABLE  REACTION OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON VERSION  TO g4valwriter;
GRANT SELECT ON VERSION TO g4valreader;

insert into PUBLIC.REACTION(RNAME) VALUES ('scattering');
insert into PUBLIC.REACTION(RNAME) VALUES ('particle production');
insert into PUBLIC.REACTION(RNAME) VALUES ('capture');
---
--- The following table should evolve as we will deal with test beam setups etc. 
---
CREATE TABLE BEAM(
   BID SERIAL PRIMARY KEY,
   PARTICLE INTEGER REFERENCES PARTICLE(PID),     
   ENERGY REAL                              -- kinetic Energy in MeV
);
ALTER TABLE  BEAM OWNER TO g4valwriter;
GRANT SELECT, UPDATE, INSERT, DELETE ON VERSION  TO g4valwriter;
GRANT SELECT ON VERSION TO g4valreader;
---
-- table representing dictionary of working group
--
CREATE TABLE WGROUPS (
    WGID SERIAL  PRIMARY KEY,                    -- working group id
    WGNAME      CHARACTER VARYING(50) UNIQUE     -- name of the working group             
);
ALTER TABLE WGROUPS OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON WGROUPS  TO G4VALWRITER;
GRANT SELECT ON WGROUPS TO G4VALREADER;

INSERT INTO PUBLIC.WGROUPS (WGNAME) VALUES ('Geant4 electromagnetic working group');
INSERT INTO PUBLIC.WGROUPS (WGNAME) VALUES ('Geant4 hadronic working group');
INSERT INTO PUBLIC.WGROUPS (WGNAME) VALUES ('Geant4 medical working group');
--
-- table representing a TEST
--
CREATE TABLE TEST (
    TESTID      INTEGER NOT NULL PRIMARY KEY,            -- number matches geant 4 test number if applicable
    TESTNAME    CHARACTER VARYING(50) UNIQUE,            -- name of the test
    MCID        INTEGER REFERENCES MCTOOL(MCID),         -- what montecarlo (MCTOOL)  (e.g. Genie or Geant4)
    DESCRIPTION TEXT,                                    -- text describing the purpose of the test
    RESPONSIBLE CHARACTER VARYING(100)[],                -- names of persons responsible for the test
    WG          INTEGER REFERENCES WGROUPS(WGID),        -- working group that the test is associated with
    KEYWORDS    CHARACTER VARYING(50)[],                 -- keywords associated with the test
    REFERENCEID INTEGER[]                                -- array elements reference RID in the  REFERENCE table
);
ALTER TABLE TEST OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON TEST  TO G4VALWRITER;
GRANT SELECT ON TEST TO G4VALREADER;
INSERT INTO PUBLIC.TEST ( TESTID, TESTNAME,DESCRIPTION,RESPONSIBLE,WG,KEYWORDS,REFERENCEID) VALUES 
(19,'test19',
'High energy Experiment, provides comparison with NA61 (31 GeV/c proton beam) and NA49 (158 GeV/c proton beam) data sets.', 
'{"Julia Yarba (Fermilab)"}',
2,
'{"Inclusive production","high energy","thick Target"}',
'{886780,694016}'
);
INSERT INTO PUBLIC.TEST ( TESTID, TESTNAME,DESCRIPTION,RESPONSIBLE,WG,KEYWORDS,REFERENCEID) VALUES 
(10000,'Franz',
'Neutron-induced production of protons, deuterons and tritons by neutrons between 300-580 MeV',
'{"Hans Wenzel (Fermilab)"}',
2,
'{"particle production","differential cross section","Thin Target"}',
'{287791}'
);
--
-- table representing TESTRESULT
-- it defines the meta data and links to a datatable
--
CREATE TABLE RESULTMETADATA (
   TRID         SERIAL  PRIMARY KEY,
   TID          INTEGER REFERENCES TEST(TESTID),                         -- REFERENCES THE TEST THAT TEST RESULT IS ASSOCIATED WITH
   VERSION      INTEGER REFERENCES VERSION(VERID),                       -- REFERENCES TAG OF GEANT 4 VERSION USED
   MODEL        INTEGER REFERENCES MODEL(MODID),                         -- REFERENCES TABLE WITH THE MODEL OR PHYSICSLIST USED
   BEAM         INTEGER REFERENCES BEAM(BID),                            -- REFERENCES PARTICLE TABLE (PDGID,NAME, ...)
   TARGET       INTEGER REFERENCES MATERIAL(MID),                        -- REFERENCES TABLE defining materials
   OBSERVABLE   INTEGER REFERENCES OBSERVABLE(OID),                      -- REFERENCES TABLE defininG OBSERVABLES (CROSS SECTION, MOMENTUM OF OUTGOING PARTICLES..)
   SECONDARY    INTEGER REFERENCES PARTICLE(PDGID),                      -- REFERENCES PARTICLE TABLE (PDGID,NAME, ...)
   REACTION     INTEGER REFERENCES REACTION(RID),                        -- REFERENCES REACTION TABLE (SCATTERING, PARTICLE PRODUCTION, CAPTURE, DECAY....)
   DATATABLEID  INTEGER REFERENCES DATATABLE(DTID),                      -- REFERENCES DATATABLE
   SCORE        INTEGER REFERENCES SCORES(SCOREID),                      -- TEST SCORE AND TYPE
   ACCESS       INTEGER REFERENCES ACCESS(ACCESSID),                     -- controls who has access to the test result
   KEYWORDS     CHARACTER VARYING(50)[],                                 -- keywords associated with the testresult e.g. angle of outgoing particle
   MODTIME      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP       -- make the default value of modtime be the time at which the row is inserted
);
ALTER TABLE RESULTMETADATA OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON RESULTMETADATA TO G4VALWRITER;
GRANT SELECT ON RESULTMETADATA TO G4VALREADER;
--
-- the following table provides a links to the TestRESULT that should be displayed as  default
--
CREATE TABLE TESTDEFAULTS (
    TDEFID INTEGER REFERENCES TEST(TESTID) UNIQUE PRIMARY KEY,   -- LINK TO ASSOCIATED TEST
    LINKS INTEGER[]                                              -- LINKS THE EXPERIMENTRESULT(TRID) LISTING THE EXPERIMENTAL RESULTS THAT SHOULD BE PLOTTED BY DEFAULT
);
ALTER TABLE TESTDEFAULTS OWNER TO G4VALWRITER;
GRANT SELECT, UPDATE, INSERT, DELETE ON TESTDEFAULTS  TO G4VALWRITER;
GRANT SELECT ON TESTDEFAULTS TO G4VALREADER;