/*
 *********************************************************************
 *
 * PostGIS - Spatial Types for PostgreSQL
 * http://postgis.refractions.net
 *
 * Copyright 2009-2011 George Rodrigues da Cunha Silva <georger.silva@gmail.com>
 *
 * This is free software; you can redistribute and/or modify it under
 * the terms of the GNU General Public Licence. See the COPYING file.
 *
 *********************************************************************
*/

-- PUBLIC FUNCTIONS --

/*
 * This function creates a table that will hold necessary values for managing history tables.
 * At this point in time, there is no dependency on this table, but later functions might require it.
 * Returns a description of action taken by the function.
*/
CREATE OR REPLACE FUNCTION postgis_install_history() RETURNS VARCHAR AS
$$
	DECLARE
		v_result text;
	BEGIN
	
		IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = 'historic_information') = TRUE THEN
			v_result := 'The historic_information table already exists.';
		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,'
			|| '     ENABLED BOOLEAN DEFAULT true,'
			|| '	 CONSTRAINT HISTORY_TABLES_PK PRIMARY KEY(TABLE_ID,TABLE_NAME));';
			v_result := 'The historic_information table was created sucessfully';
		END IF;
		
		RETURN v_result;
		
	END
$$
LANGUAGE 'plpgsql';

/*
 * This function SHOULD:
 * - Remove all information for PostGIS History Module;
 * - In it's present form, it only removes the historic_information table;
 * TODO: delete all postgis_history functions?
*/
CREATE OR REPLACE FUNCTION postgis_uinstall_history() RETURNS VARCHAR AS
$$
	DECLARE
		v_result text;
	BEGIN
	
		EXECUTE 'DROP TABLE IF EXISTS historic_information CASCADE';
		
		v_result := "THE HISTORIC INFORMATION TABLE WAS DROPPED. FUNCTIONS MARKED FOR DELETION.";
		
		return v_result;
	
	END
$$
LANGUAGE 'plpgsql';

/*
 * This function will create historic logging for any table in a determined schema.
 * This function:
 * - Creates a table that will contain all historic information;
 * - Inserts a new record on historic_information table;
 * - Inserts a new record on geometry_columns table;
 * - Creates indexes for the history table;
 * - Creates rules that will handle the inserts, updates and deletes on the main table;
*/
CREATE OR REPLACE FUNCTION postgis_create_history(p_schema text,p_table text,p_geometry_field text) returns VARCHAR as
$$
DECLARE

v_current_table text;
v_history_table text;

v_geometry_type text; --checks for the type of p_geometry_field
v_dimensions integer; --checks for the ndims in p_geometry_field
v_srid integer;       --checks for the srid in p_geometry_field
v_gid text;           --checks the name of the pk column in p_table

--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;

v_result 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';

	--sql to determine the values of geometry type, srid and ndims
	v_geometry_type:= (SELECT "type" FROM public.geometry_columns WHERE f_table_schema = p_schema AND f_table_name = p_table AND f_geometry_column = p_geometry_field);
	v_dimensions:= (SELECT coord_dimension FROM public.geometry_columns WHERE f_table_schema = p_schema AND f_table_name = p_table AND f_geometry_column = p_geometry_field);
	v_srid:= (SELECT srid FROM public.geometry_columns WHERE f_table_schema = p_schema AND f_table_name = p_table AND f_geometry_column = p_geometry_field);
	v_gid:= (SELECT column_name FROM information_schema.key_column_usage WHERE table_schema = p_schema AND table_name = p_table);
	--end sql
	
	--generate sql for creating the historic table
	v_table_sql:= 'CREATE TABLE ' || v_history_table || 
	'(' ||
	'hid serial not null,' ||
	'h_date_added timestamp not null default now(),' ||
	'h_date_deleted timestamp default null,' ||
	'h_last_operation varchar(30) not null,' ||
	'h_active_user varchar(90) not null default CURRENT_USER,' ||
	'h_current_version text not null,' ||
	'like ' || v_current_table || ',' ||
	'CONSTRAINT ' || p_table || '_history_pk primary key(hid));';
	--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) || ',' ||
	v_dimensions::text || ',' ||
	v_srid::text || ',' ||
	quote_literal(v_geometry_type) || ');';
	--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,enabled) VALUES (' ||
	'DEFAULT,' ||
	quote_literal(v_history_table) || ',' || 
	quote_literal(v_gid) || ',' ||
	quote_literal(p_geometry_field) || ',' ||
	'true);';
	--end update historic tables

	IF EXISTS(SELECT 1 FROM information_schema.tables WHERE table_name = v_history_table) = TRUE THEN
		-- historic table already EXISTS. just skip this
	ELSE
		execute v_table_sql;
		execute _postgis_create_history_indexes(p_schema,p_table,p_geometry_field);
	END IF;
	
	execute v_update_geometry_sql;
	execute v_update_history_sql;
	
	execute _postgis_add_insert_rule(p_schema,p_table,v_gid);
	execute _postgis_add_delete_rule(p_schema,p_table,v_gid);
	execute _postgis_add_update_rule(p_schema,p_table,v_gid);

	return 'SUCESSFULLY CREATED HISTORY TABLE, UPDATED GEOMETRY AND HISTORIC COLUMNS AND CREATED INDEXES';
	
END
$$
language 'plpgsql';

/*
* This function will drop all related information of a historic table.
* This function:
* - Drops the actual history table;
* - Deletes it's entry from historic_information
* - Deletes it's entry from geometry_columns
*/
CREATE OR REPLACE FUNCTION postgis_drop_history(p_schema text, p_table text) RETURNS VARCHAR AS
$$
DECLARE

v_delete_geometry_record text;
v_delete_history_record text;

v_result  text;

BEGIN

	v_delete_geometry_record := 'DELETE FROM geometry_columns WHERE f_table_schema = ' || quote_literal(p_schema) || ' AND f_table_name = '
	|| quote_literal(p_table || '_history') || ';';

	v_delete_history_record := 'DELETE FROM historic_information WHERE table_name = ' || quote_literal(p_schema || '.' || p_table || '_history') || ';';

	execute v_delete_geometry_record;
	execute v_delete_history_record;
	execute _postgis_drop_history_rules;
	
	v_result := 'DELETED GEOMETRY RECORD FOR ' || p_table || '_history, DELETED HISTORY RECORD FOR ' || p_table || '_history AND DROPPED INSERT, UPDATE AND DELETE RULES FOR ' || p_table || '.';
	
	return v_result;

END
$$
LANGUAGE 'plpgsql';

/* 
* This function will enable history information for a certain table.
* The historic table must already exists.
* All this function does is to recreate the rules for the selected table.
*/
CREATE OR REPLACE FUNCTION postgis_enable_history(p_schema text,p_table text) RETURNS VARCHAR AS
$$
DECLARE

v_table_name text;
v_gid text;
v_result text;

BEGIN

	v_table_name := p_schema || '.' || p_table || '_history';

	IF EXISTS(SELECT 1 FROM public.historic_information where table_name = v_table_name) = TRUE THEN
		
		v_gid := (SELECT primary_field FROM public.historic_information where table_name = v_table_name);
		
		execute _postgis_add_insert_rule(p_schema,p_table,v_gid);
		execute _postgis_add_delete_rule(p_schema,p_table,v_gid);
		execute _postgis_add_update_rule(p_schema,p_table,v_gid);
		UPDATE public.historic_information SET enabled = TRUE WHERE table_name = p_schema || '.' || p_table || '_history';
		v_result := 'ENABLED HISTORY ON ' || p_table;
	ELSE
		v_result := 'COULD NOT ENABLE HISTORY FOR ' || p_table || ' BECAUSE ITS NOT LISTED IN HISTORIC_INFORMATION TABLE';
	END IF;
	
	return v_result;
	
END
$$
LANGUAGE 'plpgsql';

/*
* this function disables the history logging. it will drop the historic rules
*/ 
CREATE OR REPLACE FUNCTION postgis_disable_history(p_schema text, p_table text) RETURNS VARCHAR AS
$$
DECLARE

v_table_name text;
v_delete_rules text;
v_result text;

BEGIN

	v_table_name := p_schema || '.' || p_table || '_history';

	IF EXISTS(SELECT 1 FROM public.historic_information WHERE table_name = v_table_name) = TRUE THEN
		execute _postgis_drop_history_rules(p_schema,p_table);
		UPDATE public.historic_information SET enabled = FALSE WHERE table_name = p_schema || '.' || p_table || '_history';
		v_result := 'DISABLED HISTORY ON ' || p_table;
	ELSE
		v_result := 'COULD NOT DISABLE HISTORY FOR ' || p_table || ' BECAUSE ITS NOT LISTED IN HISTORIC_INFORMATION TABLE';
	END IF;
	
	return v_result;

END
$$
LANGUAGE 'plpgsql';

	

-- PRIVATE FUNCTIONS --



/* this function will drop the rules for a history table */
CREATE OR REPLACE FUNCTION _postgis_drop_history_rules(p_schema text, p_table text) RETURNS void AS
$$
DECLARE

	v_delete_rules text;
	
BEGIN
	-- drop the rules
	v_delete_rules := 'DROP RULE ' || p_table || '_history_' || 'delete ON ' || p_schema || '.' || p_table || ';' ||
			  'DROP RULE ' || p_table || '_history_' || 'insert ON ' || p_schema || '.' || p_table || ';' ||
			  'DROP RULE ' || p_table || '_history_' || 'update ON ' || p_schema || '.' || p_table || ';';
			  
	execute v_delete_rules;
	
END
$$
LANGUAGE 'plpgsql';

/*
* Create all the pertinent indexes on our historic table
* The indexes created are:
* - GIST on GEOMETRY
* - DEFAULT on h_date_added,h_date_deleted
* - DEFAULT on h_active_user
* - DEFAULT on h_last_operation
*/
CREATE OR REPLACE FUNCTION _postgis_create_history_indexes(p_schema text, p_table text, p_geometry_field text) returns void as
$$
DECLARE

v_geomindex_sql text;
v_dateindex_sql text;
v_userindex_sql text;
v_operindex_sql text;

BEGIN
	v_geomindex_sql:= 'CREATE INDEX ' || 'idx_' || p_table || '_geometry_history' ||
	' ON ' || p_schema || '.' || p_table || '_history USING GIST(' || p_geometry_field || ');';
	
	v_dateindex_sql:= 'CREATE INDEX ' || 'idx_' || p_table || '_date_history' ||
	' ON ' || p_schema || '.' || p_table || '_history (h_date_added,h_date_deleted);';
	
	v_userindex_sql:= 'CREATE INDEX ' || 'idx_' || p_table || '_user_history' ||
	' ON ' || p_schema || '.' || p_table || '_history(h_active_user);';

	v_operindex_sql:= 'CREATE INDEX ' || 'idx_' || p_table || '_oper_history' ||
	' ON ' || p_schema || '.' || p_table || '_history (h_last_operation);';

	execute v_geomindex_sql;
	execute v_dateindex_sql;
	execute v_userindex_sql;
	execute v_operindex_sql;

END
$$
language 'plpgsql';


/*
* This function generates a new insert rule, given a schema, a table and primary key field
*/
CREATE OR REPLACE FUNCTION _postgis_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,' || --hid nextval()
	'DEFAULT,' || --h_date_added now()
	'NULL,' || --h_date_added 
	quote_literal('INSERT') || ',' || --operation
	'DEFAULT,' ||
	'NEW.' || p_gid_field || ',' ||
	'NEW.*));';

	execute v_sql;

	return;
END
$$
language 'plpgsql';	


/*
* This function generates a new update rule given a schema, table and primary key
*/
CREATE OR REPLACE FUNCTION _postgis_add_update_rule(p_schema text,p_table text,p_gid_field text) returns void as
$$
DECLARE

v_sql text;

BEGIN

	/*
		In here the h_current_version should
		actually be the hid for the record.
		I'm still thinking on how to achieve this.
		Basically:
		Insert new history
		Update old history set current_version = new_history.hid
	*/
	
	v_sql:= 'CREATE OR REPLACE RULE ' || p_table || '_history_update as ON UPDATE TO ' || p_schema || '.' || p_table ||
	' DO (' ||
	-- update old historic record
	'UPDATE ' || p_schema || '.' || p_table || '_history SET ' ||
	'h_date_deleted = now(),' ||
	'h_active_user = CURRENT_USER,' ||
	'h_current_version = ' || 'NEW.' || p_gid_field || ',' ||
	'h_last_operation = ' || quote_literal('UPDATED') ||
	'WHERE ' || p_gid_field || ' = OLD.' || p_gid_field || ';' ||
	-- insert new historic record
	'INSERT INTO ' || p_schema || '.' || p_table || '_history VALUES (' ||
	'DEFAULT,' || --hid nextval()
	'DEFAULT,' || --h_date_added now()
	'NULL,' || --h_date_added 
	quote_literal('UPDATED') || ',' || --operation
	'DEFAULT,' ||
	'NEW.' || p_gid_field || ',' ||
	'NEW.*););';

	execute v_sql;

	return;
END
$$
language 'plpgsql';


/*
* This function generates a new delete rule given a schema, table and primary key
*/
CREATE OR REPLACE FUNCTION _postgis_add_delete_rule(p_schema text,p_table text,p_gid_field text) returns void as
$$
DECLARE

v_sql text;
v_result 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 ' ||
	'h_date_deleted = now(),' ||
	'h_active_user = CURRENT_USER,' ||
	'h_current_version = ' || quote_literal('-9999') || ',' ||
	'h_last_operation = ' || quote_literal('DELETED') ||
	'WHERE ' || p_gid_field || ' = OLD.' || p_gid_field || ');';

	execute v_sql;

END
$$
language 'plpgsql';


/* 
* TEST METHODS 
* The purpose of these methods is to test if everything is working
* accordingly to the specs.
* As a general rule, if any of these methods return false or raise an
* exception, it means they failed.
* If it returns true, it means that everything worked as expected.
* Everything is one method, but will be broken in other methods.
*/
CREATE OR REPLACE FUNCTION _postgis_create_history_test() returns boolean AS
$$
DECLARE
	
	v_result bool;
	v_count_original integer;
	v_count_historic integer;
	v_history_status boolean;
	
BEGIN

	-- INSTALL
	execute 'SELECT * FROM postgis_install_history()';

	-- CREATE OUR TABLE
	execute 'CREATE TABLE testing (ID SERIAL NOT NULL PRIMARY KEY);';
	
	-- ADD A SPATIAL COLUMN (THE_GEOM, POINT, NO SRID, TWO DIMENSIONAL)
	EXECUTE 'SELECT ADDGEOMETRYCOLUMN(' ||
	QUOTE_LITERAL('public') ||   ',' ||
	QUOTE_LITERAL('testing') ||  ',' ||
	QUOTE_LITERAL('the_geom') || ',' || '-1,' ||
	QUOTE_LITERAL('POINT') || ',2);';
	
	-- INSTALL HISTORY
	EXECUTE 'SELECT POSTGIS_CREATE_HISTORY( ' ||
	QUOTE_LITERAL('public') || ',' ||
	QUOTE_LITERAL('testing') || ',' ||
	QUOTE_LITERAL('the_geom') || ');';
	
	-- TEST INITIAL CONDITIONS
	v_count_original := (SELECT COUNT(*) FROM testing);
	v_count_historic := (SELECT COUNT(*) FROM testing_history);
	
	IF (v_count_original != 0 OR v_count_historic != 0) THEN
		RAISE EXCEPTION 'COUNT FOR EMPTY TABLES DOES NOT MATCH. IT SHOULD BE ZERO.';
	END IF;
	
	-- EXECUTE INITIAL INSERT
	-- 100 VALUES
	EXECUTE 'INSERT INTO TESTING VALUES (generate_series(1,100),ST_POINT(RANDOM(),RANDOM()));';
	
	v_count_original := (SELECT COUNT(*) FROM testing);
	v_count_historic := (SELECT COUNT(*) FROM testing_history);
	
	IF (v_count_original <> 100 OR v_count_historic <> 100) THEN
		RAISE EXCEPTION 'COUNT FOR INSERTED TABLES DOES NOT MATCH. AFTER THE INSERTION OF 100 RECORDS, THE HISTORIC TABLE SHOULD CONTAIN 100 RECORDS.';
	END IF;
	
	-- DISABLE HISTORY
	EXECUTE 'SELECT * FROM POSTGIS_DISABLE_HISTORY(' ||
	QUOTE_LITERAL('public') || ',' ||
	QUOTE_LITERAL('testing') || ');';
	
	-- EXECUTE INSERT WITH DISABLED HISTORY
	EXECUTE 'INSERT INTO TESTING VALUES (generate_series(101,105),ST_POINT(RANDOM(),RANDOM()));';
	
	v_count_original := (SELECT COUNT(*) FROM testing);
	v_count_historic := (SELECT COUNT(*) FROM testing_history);
	
	IF (v_count_original <> 105 OR v_count_historic <> 100) THEN
		RAISE EXCEPTION 'COUNT FOR HISTORIC TABLE IS DIFFERENT THEN IT SHOULD BE (100).SOMETHING WENT WRONG DISABLING THE HISTORY TABLE';
	END IF;
	
	v_history_status := (SELECT ENABLED FROM historic_information where table_name = 'public.testing_history');
	
	IF (v_history_status = TRUE) THEN
		RAISE EXCEPTION 'ENABLED SHOULD BE FALSE FOR HISTORY TABLE';
	END IF;
	
	-- ENABLE HISTORY BACK
	EXECUTE 'SELECT * FROM POSTGIS_ENABLE_HISTORY(' ||
	QUOTE_LITERAL('public') || ',' ||
	QUOTE_LITERAL('testing') || ');';
	
	v_history_status := (SELECT ENABLED FROM historic_information where table_name = 'public.testing_history');
	
	IF (v_history_status = FALSE) THEN
		RAISE EXCEPTION 'ENABLED SHOULD BE TRUE FOR HISTORY TABLE';
	END IF;
	
	-- UPDATE RECORD #1
	EXECUTE 'UPDATE testing SET the_geom = ST_POINT(1,1) WHERE id = 1;';
	
	v_count_historic := (SELECT COUNT(*) FROM testing_history WHERE h_last_operation = 'UPDATED');
	
	IF (v_count_historic <> 2) THEN
		RAISE EXCEPTION 'WE SHOULD HAVE TWO UPDATED RECORDS.';
	END IF;	
	
	EXECUTE 'DELETE FROM testing WHERE id = 2';
	
	v_count_historic := (SELECT COUNT(*) FROM testing_history WHERE h_last_operation = 'DELETED');
	
	IF (v_count_historic <> 1) THEN
		RAISE EXCEPTION 'WE SHOULD ONE DELETED RECORD';
	END IF;
	
	RETURN TRUE;
	
END
$$
LANGUAGE 'plpgsql';