-- ----------------------------
-- Function structure for create_version_trigger
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."records_create_version_trigger"() CASCADE;
CREATE OR REPLACE FUNCTION "public"."records_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 POSITION('records' IN ddl_command.object_identity) != 0 THEN   
			RAISE LOG 'Create Version Trigger Set: %', ddl_command.object_identity;
			EXECUTE 'CREATE TRIGGER records_event_version_trigger
				 AFTER INSERT OR UPDATE OR DELETE
				 ON ' || ddl_command.object_identity || '
				 FOR EACH ROW
				 EXECUTE PROCEDURE records_version_trigger()';
        END IF;
    END LOOP;	 

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
-- ----------------------------
-- Function structure for version_trigger
-- ----------------------------
DROP FUNCTION IF EXISTS "public"."records_version_trigger"() CASCADE;
CREATE OR REPLACE FUNCTION "public"."records_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_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);	
				EXECUTE format('CREATE INDEX "records_history_key0_index" ON %s USING btree ("address" "pg_catalog"."bytea_ops" ASC NULLS LAST,"table_id" "pg_catalog"."bytea_ops" ASC NULLS LAST,"key0" "pg_catalog"."bytea_ops" ASC NULLS LAST)', targetTable);	
				EXECUTE format('CREATE INDEX "records_history_key1_index" ON %s USING btree ("address" "pg_catalog"."bytea_ops" ASC NULLS LAST,"table_id" "pg_catalog"."bytea_ops" ASC NULLS LAST,"key1" "pg_catalog"."bytea_ops" ASC NULLS LAST)', targetTable);	
				EXECUTE format('CREATE INDEX "records_history_block_number_index" ON %s (block_number)', targetTable);	
				EXECUTE format('CREATE INDEX "records_history__start_block_index" ON %s (_start_block)', targetTable);	
				EXECUTE format('CREATE INDEX "records_history__end_block_index" ON %s (_end_block)', targetTable);	

    END IF;

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

        RETURN NEW;
    END IF;
 
    IF TG_OP = 'DELETE' THEN
				EXECUTE format('SELECT MAX(_history_id) FROM %s WHERE address = $1 AND table_id = $2 AND key_bytes = $3', targetTable) USING OLD.address, OLD.table_id, OLD.key_bytes INTO maxHistoryId;
        EXECUTE format('UPDATE %s SET _end_block = $1 WHERE address = $2 AND table_id = $3 AND  key_bytes = $4 AND _history_id = $5', targetTable)
        USING OLD."block_number", OLD.address, OLD.table_id, 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 records_event_create_version_trigger
ON ddl_command_end
EXECUTE FUNCTION records_create_version_trigger();