
CREATE TABLE psu_bin (
  rack_id char(3),               -- rack where psu bin is located
  height smallint,               -- location of psu bin within rack
  type smallint default 0,       -- bin type to be matched with psu type (see possible values below)
  ip_addr inet NOT NULL,
  outlet_id char(7) NOT NULL,
  PRIMARY KEY (rack_id, height), -- Wiener bin identified by (rack,height) pair
  UNIQUE (rack_id, height, type) -- trick to enforce type match with psu.type
);
-- Comments:
-- the unique constraint seems dumb: (rack_id, height) is already unique! But it allows type matching (see below)
-- example of values inserted: ('I28',33,1,'10.160.134.16','3806-01');

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE psu (
  serial char(7) PRIMARY KEY, -- Wiener serial number
  rack_id char(3),            -- rack where psu bin is located
  height smallint,            -- location of psu bin within rack
  LHCserial char(8) UNIQUE,   -- LHC serial number
  type smallint default 0,    -- psu type to be matched with bin type (see possible values below)
  mac_addr macaddr NOT NULL,
  firmware inet,              -- the firmware has ip address format
  loc_id integer,             -- reference to physical locations table
  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 (rack_id,height) REFERENCES psu_bin(rack_id,height) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (rack_id,height,type) REFERENCES psu_bin(rack_id,height,type) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (loc_id) REFERENCES loc(loc_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT psu_single_pos CHECK (loc_id IS NULL OR rack_id IS NULL AND height IS NULL) 
);
-- Comments:
-- the type match between psu and psu_bin previously used a subquery in the check constraint:
-- CHECK (type=(SELECT type FROM psu_bin WHERE psu.rack_id=psu_bin.rack_id AND psu.height=psu_bin.height))
-- however, this seems to be illegal: "ERROR: cannot use subquery in check constraint".
-- the foreign key declarations of psu table with the unique constraints of psu_bin table have the desired effect.
-- psu type: 0=unknown[default], 1=2x200[Amps], 2=3x150+50[Amps], 3=3x100+4x50[Amps], 4=nx22[Amps]
-- example of values inserted: (4394056,'I28',33,'72720027',1,'00:50:C2:2D:C3:D9','2.1.0.8',NULL,'PC')

CREATE TRIGGER history_trigger BEFORE DELETE OR UPDATE ON psu FOR EACH ROW EXECUTE PROCEDURE history_trigger('psu_old');
CREATE TABLE psu_old (
  serial char(7) NOT NULL, rack_id char(3), height smallint, LHCserial char(8), type smallint, mac_addr macaddr NOT NULL,
  firmware inet, loc_id integer, person char(3) NOT NULL, begin_time timestamptz NOT NULL, end_time timestamptz NOT NULL
);

-- add table for comments on Wieners
CREATE TABLE psu_cmnt_link (
  cmnt_id integer PRIMARY KEY,  -- reference to comment table with the actual comments
  serial char(7) NOT NULL,      -- reference to Wiener serial number for which the comment is added
  FOREIGN KEY (serial) REFERENCES psu(serial) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (cmnt_id) REFERENCES comment(cmnt_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE psu_chan (
  rack_id char(3),                -- rack where psu bin is located
  height smallint,                -- location of psu bin within rack
  channel smallint NOT NULL,      -- psu channel number (0,4,8 for SM layers, etc.)
  goesto varchar(20) PRIMARY KEY, -- psu channel identified by the TRD component it is connected to
  FOREIGN KEY (rack_id,height) REFERENCES psu_bin(rack_id,height) ON DELETE RESTRICT ON UPDATE CASCADE
);
-- Ken will add the pre-trigger cables later; since this is a one time deal, an SQL script would suffice
-- examples of values inserted: ('C16',15,0,'GTU-00-09-33V'), ('I27',17,4,'SM01-D18V-L01')

-- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE pcu (
  pcu_id smallint PRIMARY KEY,
  loc_id integer,             -- reference to physical locations table
  FOREIGN KEY (loc_id) REFERENCES loc(loc_id) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT valid_pcu_id CHECK (pcu_id=0 OR pcu_id=1 OR pcu_id=2 OR pcu_id=3)
);

-- since pcu table is small, insert values here:
LOCK TABLE pcu;
INSERT INTO pcu VALUES (0,11),(1,11),(2,11),(3,11);
-- note that loc_id is 11 (CERN,Point2) without a check constraint...
