-- to run: "psql -d gateDB -U alice -f create_tables.sql -o create_tables.out 2>create_tables.err"
-- LEAK RATE TESTS:
DROP TABLE IF EXISTS roc_test_head_leak;
CREATE TABLE roc_test_head_leak(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  operator varchar(30),
  institute varchar(30),
  start_time timestamp,
  end_time timestamp,
  start_temp real,
  end_temp real,
  start_pres real,
  end_pres real,
  start_oxy real,
  end_oxy real,
  gas_mixture varchar(20),
  flow_rate real,
  low_gas real,
  high_gas real,
  low_leak real,
  high_leak real,
  comment varchar(99),
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (roc_type,roc_serial) -- to avoid multiple insertions of same roc
);
DROP TABLE IF EXISTS roc_test_data_leak;
CREATE TABLE roc_test_data_leak(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  relative_time real,
  oxygen_conc real,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_leak_search;
CREATE INDEX roc_test_data_leak_search ON roc_test_data_leak (roc_type,roc_serial);

-- CONDITIONING TESTS:
DROP TABLE IF EXISTS roc_test_head_cond;
CREATE TABLE roc_test_head_cond(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  operator varchar(30),
  institute varchar(30),
  start_time timestamp,
  end_time timestamp,
  start_temp real,
  end_temp real,
  start_pres real,
  end_pres real,
  start_oxy real,
  end_oxy real,
  gas_mixture varchar(20),
  flow_rate real,
  drift_volt real,
  low_curr real,
  high_curr real,
  trip_curr real,
  trip_num real,
  comment varchar(99),
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (roc_type,roc_serial) -- to avoid multiple insertions of same roc
);
DROP TABLE IF EXISTS roc_test_data_cond;
CREATE TABLE roc_test_data_cond(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  relative_time real,
  anode_voltage real,
  anode_dark_current real,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_cond_search;
CREATE INDEX roc_test_data_cond_search ON roc_test_data_cond (roc_type,roc_serial);

-- GAIN UNIFORMITY TESTS:
DROP TABLE IF EXISTS roc_test_head_guni;
CREATE TABLE roc_test_head_guni(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  operator varchar(30),
  institute varchar(30),
  start_time timestamp,
  end_time timestamp,
  start_temp real,
  end_temp real,
  start_pres real,
  end_pres real,
  start_oxy real,
  end_oxy real,
  gas_mixture varchar(20),
  flow_rate real,
  anode_volt real,
  drift_volt real,
  start_curr real,
  end_curr real,
  source char(5),
  phi_offset real,
  phi_num_pts real,
  z_num_pts real,
  mean_curr real,
  sigma_curr real,
  max_dev_curr real,
  comment varchar(99),
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (roc_type,roc_serial) -- to avoid multiple insertions of same roc
);
DROP TABLE IF EXISTS roc_test_data_guni_1d;
CREATE TABLE roc_test_data_guni_1d(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  z_pos real,
  anode_current real,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_guni_1d_search;
CREATE INDEX roc_test_data_guni_1d_search ON roc_test_data_guni_1d (roc_type,roc_serial);
DROP TABLE IF EXISTS roc_test_data_guni_2d;
CREATE TABLE roc_test_data_guni_2d(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  z_pos real,
  phi_pos real,
  anode_current real,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_guni_2d_search;
CREATE INDEX roc_test_data_guni_2d_search ON roc_test_data_guni_2d (roc_type,roc_serial);

-- ABSOLUTE GAIN TESTS:
DROP TABLE IF EXISTS roc_test_head_gabs;
CREATE TABLE roc_test_head_gabs(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  operator varchar(30),
  institute varchar(30),
  start_time timestamp,
  end_time timestamp,
  start_temp real,
  end_temp real,
  start_pres real,
  end_pres real,
  start_oxy real,
  end_oxy real,
  gas_mixture varchar(20),
  flow_rate real,
  drift_volt real,
  source char(5),
  count_rate real,
  comment varchar(99),
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (roc_type,roc_serial) -- to avoid multiple insertions of same roc
);
DROP TABLE IF EXISTS roc_test_data_gabs;
CREATE TABLE roc_test_data_gabs(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  anode_voltage real,
  anode_current real,
  gas_gain real,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_gabs_search;
CREATE INDEX roc_test_data_gabs_search ON roc_test_data_gabs (roc_type,roc_serial);

-- SPECTRAL TESTS:
DROP TABLE IF EXISTS roc_test_head_spec;
CREATE TABLE roc_test_head_spec(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  operator varchar(30),
  institute varchar(30),
  start_time timestamp,
  end_time timestamp,
  start_temp real,
  end_temp real,
  start_pres real,
  end_pres real,
  start_oxy real,
  end_oxy real,
  gas_mixture varchar(20),
  flow_rate real,
  anode_volt real,
  drift_volt real,
  peak_pos real,
  sigma real,
  comment varchar(99),
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (roc_type,roc_serial) -- to avoid multiple insertions of same roc
);
DROP TABLE IF EXISTS roc_test_data_spec;
CREATE TABLE roc_test_data_spec(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  channel integer,
  entries integer,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_spec_search;
CREATE INDEX roc_test_data_spec_search ON roc_test_data_spec (roc_type,roc_serial);

-- STABILITY TESTS:
DROP TABLE IF EXISTS roc_test_head_stab;
CREATE TABLE roc_test_head_stab(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  operator varchar(30),
  institute varchar(30),
  start_time timestamp,
  end_time timestamp,
  start_temp real,
  end_temp real,
  start_pres real,
  end_pres real,
  start_oxy real,
  end_oxy real,
  gas_mixture varchar(20),
  flow_rate real,
  anode_volt real,
  drift_volt real,
  comment varchar(99),
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  UNIQUE (roc_type,roc_serial) -- to avoid multiple insertions of same roc
);
DROP TABLE IF EXISTS roc_test_data_stab;
CREATE TABLE roc_test_data_stab(
  roc_type char(4) NOT NULL, 
  roc_serial integer NOT NULL,
  relative_time real,
  peak_pos real,
  sigma real,
  chi2 real,
  anode_current real,
  oxygen_conc real,
  pressure real,
  temperature real,
  FOREIGN KEY (roc_type,roc_serial) REFERENCES roc(roc_type,roc_serial) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- index roc definition for fast searching
DROP INDEX IF EXISTS roc_test_data_stab_search;
CREATE INDEX roc_test_data_stab_search ON roc_test_data_stab (roc_type,roc_serial);
