create or replace function build_history_table() returns void as
$$
--this function creates a table that will hold some interesting values for managing history tables
--later functions will be added
BEGIN

	IF exists(select 1 from information_schema.tables where table_name = 'historic_information') = true THEN
		raise notice 'The table historic_information already exists. Could not create it.';
	ELSE
	execute 'create table historic_information(table_id serial not null,table_name varchar(100) not null,primary_field varchar(100) not null, geometry_field varchar(100) not null, constraint history_tables_pk primary key(table_id,table_name));';
	END IF;

END
$$
language 'plpgsql';

--end build_history_table


--im open to suggestions for the names of the functions.
--just realized that one is build_history_table and the other create_...
CREATE OR REPLACE FUNCTION create_history_table(p_schema text,p_table text,p_gid text,p_geometry_field text,p_geomtype text,p_dimensions integer,p_srid integer) returns boolean as
$$
DECLARE

v_current_table text;
v_history_table text;

--SQL statement that will create the historic table
v_table_sql text;

--SQL statement that will perform an update on geometry_columns
v_update_geometry_sql text; 

--SQL statement that will perform an update on historic_tables
v_update_history_sql text;

BEGIN

	--determines the name of current table
	v_current_table:= p_schema || '.' || p_table;
	--determines the name of historic table
	v_history_table:= p_schema || '.' || p_table || '_history';

	--generate sql for creating the historic table
	v_table_sql:= 'CREATE TABLE ' || v_history_table || 
	'(' ||
	'history_id serial not null,' ||
	'date_added timestamp not null default now(),' ||
	'date_deleted timestamp default null,' ||
	'operation varchar(30) not null,' ||
	'active_user varchar(90) not null default CURRENT_USER,' ||
	'current_version text not null,' ||
	'like ' || v_current_table || ',' ||
	'CONSTRAINT ' || p_table || '_history_pk primary key(history_id),' ||
	'CONSTRAINT enforce_dims_' || p_geometry_field || ' CHECK(ndims(' || p_geometry_field || ') = ' || p_dimensions::text || '),' ||
	'CONSTRAINT enforce_geotype_' || p_geometry_field || ' CHECK (geometrytype(' || p_geometry_field || ') = ' || quote_literal(p_geomtype) || '::text OR ' ||
	p_geometry_field || ' IS NULL),' ||
	'CONSTRAINT enforce_srid_' || p_geometry_field || ' CHECK (srid(' || p_geometry_field || ') = ' || p_srid::text || '));';
	--end sql

	--update geometry columns
	v_update_geometry_sql:='INSERT INTO public.geometry_columns(f_table_catalog,f_table_schema,f_table_name,f_geometry_column,coord_dimension,srid,type) values (' ||
	quote_literal('') || ',' ||
	quote_literal(p_schema) || ',' ||
	quote_literal(p_table || '_history') || ',' ||
	quote_literal(p_geometry_field) || ',' ||
	p_dimensions::text || ',' ||
	p_srid::text || ',' ||
	quote_literal(p_geomtype) || ');';
	--end update geometry_columns

	--insert into historic_tables
	v_update_history_sql:='INSERT INTO public.historic_information(table_id,table_name,primary_field,geometry_field) VALUES (' ||
	'DEFAULT,' ||
	quote_literal(v_history_table) || ',' || 
	quote_literal(p_gid) || ',' ||
	quote_literal(p_geometry_field) || ');';
	--end update historic tables

	execute v_table_sql;
	execute v_update_geometry_sql;
	execute v_update_history_sql;

	perform teste.add_insert_rule(p_schema,p_table,p_gid);
	perform teste.add_delete_rule(p_schema,p_table,p_gid);
	perform teste.add_update_rule(p_schema,p_table,p_gid);

	return true;
	
END
$$
language 'plpgsql';

--end create_history_table

--add_insert_rule
CREATE OR REPLACE FUNCTION add_insert_rule(p_schema text,p_table text,p_gid_field text) returns void as
$$
DECLARE

v_sql text;

BEGIN

	v_sql:= 'CREATE OR REPLACE RULE ' || p_table || '_history_insert as ON INSERT TO ' || p_schema || '.' || p_table ||
	' DO (' ||
	'INSERT INTO ' || p_schema || '.' || p_table || '_history VALUES(' ||
	'DEFAULT,' || --history_id nextval()
	'DEFAULT,' || --date_added now()
	'NULL,' || --date_deleted 
	quote_literal('INSERT') || ',' || --operation
	'DEFAULT,' ||
	'NEW.' || p_gid_field || ',' ||
	'NEW.*));';

	execute v_sql;

END
$$
language 'plpgsql';	
--end add_insert_rule

--add_update_rule
CREATE OR REPLACE FUNCTION add_update_rule(p_schema text,p_table text,p_gid_field text) returns void as
$$
DECLARE

v_sql text;

BEGIN

	v_sql:= 'CREATE OR REPLACE RULE ' || p_table || '_history_update as ON UPDATE TO ' || p_schema || '.' || p_table ||
	' DO (' ||
	'UPDATE ' || p_schema || '.' || p_table || '_history SET ' ||
	'date_deleted = now(),' ||
	'active_user = CURRENT_USER,' ||
	'current_version = ' || 'NEW.' || p_gid_field || ',' ||
	'operation = ' || quote_literal('UPDATE') ||
	'WHERE ' || p_gid_field || ' = OLD.' || p_gid_field || ';' || -- end of the update statement
	'INSERT INTO ' || p_schema || '.' || p_table || '_history VALUES (' ||
	'DEFAULT,' || --history_id nextval()
	'DEFAULT,' || --date_added now()
	'NULL,' || --date_deleted 
	quote_literal('INSERT') || ',' || --operation
	'DEFAULT,' ||
	'NEW.' || p_gid_field || ',' ||
	'NEW.*););';

	execute v_sql;

END
$$
language 'plpgsql';
--end add_update_rule

--add_delete_rule
CREATE OR REPLACE FUNCTION add_delete_rule(p_schema text,p_table text,p_gid_field text) returns void as
$$
DECLARE

v_sql text;

BEGIN

	v_sql:= 'CREATE OR REPLACE RULE ' || p_table || '_history_delete as ON DELETE TO ' || p_schema || '.' || p_table ||
	' DO (' ||
	'UPDATE ' || p_schema || '.' || p_table || '_history SET ' ||
	'date_deleted = now(),' ||
	'active_user = CURRENT_USER,' ||
	'current_version = ' || quote_literal('-9999') || ',' ||
	'operation = ' || quote_literal('DELETED') ||
	'WHERE ' || p_gid_field || ' = OLD.' || p_gid_field || ');';

	execute v_sql;

END
$$
language 'plpgsql'
--end ad__delete_rule