DROP TRIGGER IF EXISTS history_trigger ON psuhv_old CASCADE;

CREATE TABLE psuhv (
  psuhv_id varchar(6) PRIMARY KEY,  -- serial number
  sm_id integer,                 -- primary key of the SM that gets HV from this module
  type char(5),                  -- "anode"/"drift"/"crate"
  loc_id integer,                -- reference to physical locations table
  numchann integer,              -- number of output channels
  firmware varchar,
  status varchar,                -- "good"/"broken"/"maintainance"/"recalibration"
  person char(3) NOT NULL,       -- initials of person that made the change
  begin_time timestamptz NOT NULL default now(), -- used by the history trigger
  FOREIGN KEY (sm_id) REFERENCES sm(sm_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT psuhv_single_pos CHECK (loc_id IS NULL OR sm_id IS NULL)
);

CREATE TRIGGER history_trigger BEFORE DELETE OR UPDATE ON psuhv FOR EACH ROW EXECUTE PROCEDURE history_trigger('psuhv_old');
CREATE TABLE psuhv_old (
  psuhv_id varchar(6) NOT NULL, sm_id integer, type char(5), loc_id integer, numchann integer, firmware varchar, status varchar, person char(3) NOT NULL, begin_time timestamptz NOT NULL, end_time timestamptz NOT NULL
);
CREATE TABLE psuhv_cmnt_link (
  cmnt_id integer PRIMARY KEY,   -- reference to comment table with the actual comments
  psuhv_id varchar(6) NOT NULL,     -- reference to psu serial number for which the comment is added
  FOREIGN KEY (psuhv_id) REFERENCES psuhv(psuhv_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (cmnt_id) REFERENCES comment(cmnt_id) ON DELETE RESTRICT ON UPDATE CASCADE
);
