-- create ptb (pre-trigger box) table necessary for dcs location:
CREATE TABLE ptb (
  ptb_id integer PRIMARY KEY,
  loc_id integer,
  FOREIGN KEY (loc_id) REFERENCES loc(loc_id) ON DELETE RESTRICT ON UPDATE CASCADE
);

-- CREATE TABLE dcs_old_backup AS SELECT * FROM dcs_old;
-- CREATE TABLE dcs_backup AS SELECT * FROM dcs;
-- remove pdb_id as valid location and add sm_id, ptb_id as valid locations:
ALTER TABLE dcs DROP CONSTRAINT dcs_pdbpos_range;
ALTER TABLE dcs DROP CONSTRAINT dcs_pos_notnull;
ALTER TABLE dcs DROP CONSTRAINT dcs_single_pos;
DROP VIEW IF EXISTS dcs_hist_inventory;
DROP VIEW IF EXISTS dcs_hist;
ALTER TABLE dcs RENAME COLUMN pdb_id TO sm_id;
ALTER TABLE dcs ALTER COLUMN sm_id SET DEFAULT NULL;
ALTER TABLE dcs ADD CONSTRAINT dcs_sm_id_key UNIQUE (sm_id);
ALTER TABLE dcs ADD FOREIGN KEY (sm_id) REFERENCES sm(sm_id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE dcs RENAME COLUMN pdb_pos TO ptb_id;
ALTER TABLE dcs ALTER COLUMN ptb_id SET DEFAULT NULL;
ALTER TABLE dcs ADD CONSTRAINT dcs_ptb_id_key UNIQUE (ptb_id);
ALTER TABLE dcs ADD FOREIGN KEY (ptb_id) REFERENCES ptb(ptb_id) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE dcs DROP CONSTRAINT dcs_pdb_id_key;
ALTER TABLE dcs DROP CONSTRAINT dcs_pdb_id_fkey;
DROP VIEW  IF EXISTS pdb_hist;
-- modify the dcs_single_pos constraint accordingly:
ALTER TABLE dcs ADD CONSTRAINT dcs_single_pos CHECK (
	rob_id IS NULL AND  sm_id IS NULL AND ptb_id IS NULL OR 
	rob_id IS NULL AND  sm_id IS NULL AND loc_id IS NULL OR 
	rob_id IS NULL AND ptb_id IS NULL AND loc_id IS NULL OR
	 sm_id IS NULL AND ptb_id IS NULL AND loc_id IS NULL
);

-- drop the dcs_positions_valid trigger:
DROP TRIGGER dcs_positions_valid ON dcs RESTRICT;
-- preserve the history trigger:
DROP TRIGGER history_trigger ON dcs RESTRICT; -- is this really necessary?
ALTER TABLE dcs_old RENAME COLUMN pdb_id TO sm_id;
ALTER TABLE dcs_old ALTER COLUMN sm_id SET DEFAULT NULL;
ALTER TABLE dcs_old RENAME COLUMN pdb_pos TO ptb_id;
ALTER TABLE dcs_old ALTER COLUMN ptb_id SET DEFAULT NULL;
CREATE TRIGGER history_trigger BEFORE DELETE OR UPDATE ON dcs FOR EACH ROW EXECUTE PROCEDURE history_trigger('dcs_old');
-- RESTRICT = Refuse to drop the trigger if any objects depend on it. This is the default. 
-- CASCADE= Automatically drop objects that depend on the trigger.

-- recreate views dcs_hist and dcs_hist_inventory:
CREATE VIEW dcs_hist AS
 SELECT dcs_old.dcs_id, dcs_old.status, dcs_old.version, dcs_old.mac, dcs_old.type, dcs_old.rob_id, dcs_old.sm_id, dcs_old.ptb_id, dcs_old.loc_id, dcs_old.person, dcs_old.begin_time, dcs_old.end_time
   FROM dcs_old
UNION ALL
 SELECT dcs.dcs_id, dcs.status, dcs.version, dcs.mac, dcs.type, dcs.rob_id, dcs.sm_id, dcs.ptb_id, dcs.loc_id, dcs.person, dcs.begin_time, NULL::unknown AS end_time
   FROM dcs;
CREATE VIEW dcs_hist_inventory AS
 SELECT 'DCS'::text || lpad(dcs_hist.dcs_id::text, 4, '0'::text) AS name, 'dcs'::text AS type, dcs_hist.type AS subtype, dcs_hist.dcs_id AS id, ( SELECT dcs_status.description
           FROM dcs_status
          WHERE dcs_status.status = dcs_hist.status) AS status, dcs_hist.person, COALESCE(( SELECT loc.name
           FROM loc
          WHERE dcs_hist.loc_id = loc.loc_id), (( SELECT rob_inventory.name
           FROM rob_inventory
          WHERE rob_inventory.id = dcs_hist.rob_id))::character varying) AS loc, date_trunc('second'::text, dcs_hist.begin_time) AS begin_time, date_trunc('second'::text, dcs_hist.end_time) AS end_time
   FROM dcs_hist;
