-- ----------------------------
-- Function structure for create_version_trigger
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."create_version_trigger"() CASCADE;
CREATE OR REPLACE FUNCTION "public"."create_version_trigger"()
  RETURNS "pg_catalog"."event_trigger" AS $BODY$
DECLARE
  ddl_command record;	
BEGIN

    FOR ddl_command IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP      
        IF ddl_command.object_type = 'table' AND ddl_command.command_tag = 'CREATE TABLE' AND ddl_command.schema_name != 'public' AND ddl_command.schema_name != '__mud_internal' AND ddl_command.schema_name != 'mud' AND POSITION('_mud' IN ddl_command.schema_name) = 0 AND POSITION('_yeomen_indexer' IN ddl_command.schema_name) = 0 AND POSITION('_yeomen_mapping' IN ddl_command.schema_name) = 0 AND POSITION('_history' IN ddl_command.object_identity) = 0 THEN   
			RAISE LOG 'Create Version Trigger Set: %', ddl_command.object_identity;
			EXECUTE 'CREATE TRIGGER event_version_trigger
				 AFTER INSERT OR UPDATE OR DELETE
				 ON ' || ddl_command.object_identity || '
				 FOR EACH ROW
				 EXECUTE PROCEDURE version_trigger()';
        END IF;
    END LOOP;	 

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- ----------------------------
-- Function structure for version_trigger
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."version_trigger"() CASCADE;
CREATE OR REPLACE FUNCTION "public"."version_trigger"()
  RETURNS "pg_catalog"."trigger" AS $BODY$
DECLARE		
		sourceTable TEXT;		
    targetTable TEXT;			
		maxHistoryId INTEGER;	  
BEGIN		
		sourceTable := format('%I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME);
		targetTable := format('%I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME || '_history'); -- Construct the history table name		
			
		
		-- Skip core tables from versioning, Extra layer check. Avoid event binding from table create trigger
		IF TG_TABLE_SCHEMA = '__mud_internal' OR TG_TABLE_SCHEMA = 'mud' OR TG_TABLE_SCHEMA ILIKE '%_mud' OR TG_TABLE_SCHEMA ILIKE '%_yeomen_indexer' OR TG_TABLE_SCHEMA ILIKE '%_yeomen_mapping' OR TG_TABLE_NAME ILIKE '%_history'  THEN
			RETURN NULL;
		END IF;
		
	RAISE LOG 'Version Trigger Set: %', sourceTable;	
		
		-- Check if the history table already exists
    IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME || '_history') THEN
        -- Create the history table using the template        
				EXECUTE format('CREATE TABLE %s (LIKE %s)', targetTable, sourceTable);
				EXECUTE format('ALTER TABLE %s ADD COLUMN _start_block INT4, ADD COLUMN _end_block INT4, ADD COLUMN _history_id SERIAL PRIMARY KEY', targetTable);				

    END IF;

    IF TG_OP = 'UPDATE' THEN
        IF NEW."__key_bytes" <> OLD."__key_bytes" THEN
            RAISE EXCEPTION 'the __key_bytes must not be changed';
        END IF;				
				
				EXECUTE format('SELECT MAX(_history_id) FROM %s WHERE __key_bytes = $1', targetTable) USING NEW.__key_bytes INTO maxHistoryId;	
        EXECUTE format('UPDATE %s SET _end_block = $1 WHERE __key_bytes = $2 AND _history_id = $3', targetTable)
        USING NEW."__last_updated_block_number", NEW."__key_bytes", maxHistoryId;				
        
    END IF;
 
    IF TG_OP IN ('INSERT', 'UPDATE')  THEN
				
				EXECUTE format('INSERT INTO %s (SELECT %s.*, $2, NULL FROM %s WHERE __key_bytes = $1)', targetTable, sourceTable, sourceTable) 
				USING NEW."__key_bytes", NEW."__last_updated_block_number";

        RETURN NEW;
    END IF;
 
    IF TG_OP = 'DELETE' THEN
				EXECUTE format('SELECT MAX(_history_id) FROM %s WHERE __key_bytes = $1', targetTable) USING OLD.__key_bytes INTO maxHistoryId;
        EXECUTE format('UPDATE %s SET _end_block = $1 WHERE __key_bytes = $2 AND _history_id = $3', targetTable)
        USING OLD."__last_updated_block_number", OLD."__key_bytes", maxHistoryId;

        IF FOUND THEN
            RETURN OLD;
        ELSE
            RETURN NULL;
        END IF;
    END IF;
		
		RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
  
  
 -- Attach the event trigger to the database
CREATE EVENT TRIGGER event_create_version_trigger
ON ddl_command_end
EXECUTE FUNCTION create_version_trigger();