-- History Tracking Trigger-Functions
--

CREATE TABLE history (
	hid   SERIAL UNIQUE NOT NULL,
	cid   int4 NOT NULL CONSTRAINT valid_client REFERENCES client ON DELETE CASCADE,
	ts    timestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
	uid   int4 NOT NULL CONSTRAINT valid_uid REFERENCES user_info DEFAULT app_session_int_vol('UID'),
	tbl   varchar(32) NOT NULL,
	act   char(1) NOT NULL CONSTRAINT valid_act CHECK (act IN ('U','I','D')),
	PRIMARY KEY (hid)
);

CREATE TABLE history_detail (
	hid  integer NOT NULL CONSTRAINT valid_hid REFERENCES client_history,
	col  varchar(32) NOT NULL,
	was  text,
	PRIMARY KEY (hid,col)
);

-- tcl_track_history(TABLE-NAME)
--	Set TABLE-NAME when creating the trigger. Will automatically record change 
--	details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
	switch $TG_op {
		DELETE {
			if { [llength [array names OLD cid]] > 0 } {
				set clival $OLD(cid)
			} else {
				set clival "NULL"
			}
			spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
		}
		INSERT {
			if { [llength [array names NEW cid]] > 0 } {
				set clival $NEW(cid)
			} else {
				set clival "NULL"
			}
			spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
		}
		UPDATE {
			if { [llength [array names OLD cid]] > 0 } {
				set clival $OLD(cid)
			} else {
				set clival "NULL"
			}
			set inserted_main_history_row false
			foreach {col} $TG_relatts {
				# First result seems to be an empty string when stepping through columns
				if { $col > "" } {
					# Check if OLD/NEW contain a value
					if { [llength [array names OLD $col]] > 0 } {
						set oldval $OLD($col)
					} else {
						set oldval "NULL"
					}
					if { [llength [array names NEW $col]] > 0 } {
						set newval $NEW($col)
					} else {
						set newval "NULL"
					}
					if { $oldval != $newval } {
						if { !$inserted_main_history_row } {
							spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
							set inserted_main_history_row true
						}
						spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')"
					}
				}
			}
		}
	}
	return OK
' LANGUAGE pltcl;

CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client');