-- Your entity/diagram shapes - set is_calculated to true for the top level entity and all others to false
create table entity (
	entity_id serial not null primary key, 
	the_geom geometry not null, 
	is_calculated bit not null default 0::bit,
	parent_entity_id int null references entity(entity_id) on delete set null, 
	ratio_x float4 not null default 0, 
	ratio_y float4 not null default 0, 
	ratio_z float4 not null default 0
);

/*
	PURPOSE: placement of child entities in relation to the parent entity, 
		Watch for Recursion in your data!!  1-->2-->3-->1 
		Step 1: Center child on parent
		Step 2: Offset by er.offset_N of parent extents
		Step 3: Mark child as updated to prevent recursion
*/
create or replace rule entity_update AS ON update TO entity 
WHERE NOT (st_AsText(OLD.the_geom) ~= st_AsText(NEW.the_geom)) -- Only if geometry was modifed
DO 
	UPDATE entity 
	SET the_geom = st_translate(
		-- Center the child on parent geometry
		st_translate( 
			the_geom, 
			st_x(st_centroid(NEW.the_geom)) - st_x(st_centroid(the_geom)),  
			st_y(st_centroid(NEW.the_geom)) - st_y(st_centroid(the_geom)),
			st_z(st_centroid(NEW.the_geom)) - st_z(st_centroid(the_geom))		
		),
		--Translate to the left, right, top, bottom, front, back of the parent geometry by the ratio (x,y,x)  stored in entity_relation of the parent entity width/height 
		( st_xmax(NEW.the_geom) - st_xmin(NEW.the_geom) ) * ratio_x,
		( st_ymax(NEW.the_geom) - st_ymin(NEW.the_geom) ) * ratio_y,
		( st_zmax(NEW.the_geom) - st_zmin(NEW.the_geom) ) * ratio_z
	)
	WHERE parent_entity_id = NEW.entity_id	--Update all children of the modified parent record

	

	
/* 
	SOME 2D TEST ENTITIES 
	TOP LEVEL parent entity
	NO NEED TO SET is_calculated = 1 because it has no parent entity and the trigger should not touch it EVER
*/
insert into entity (the_geom, parent_entity_id, ratio_x, ratio_y)
SELECT 'POLYGON((0 0, 50 0, 50 50, 0 50, 0 0))'::geometry, NULL, 0, 0;	

insert into entity (the_geom, parent_entity_id, ratio_x, ratio_y)
SELECT 'POLYGON((0 0, 50 0, 50 50, 0 50, 0 0))'::geometry, (SELECT MIN(entity_id) FROM entity), 1.5, 0		-- TO RIGHT of parent
UNION SELECT 'POLYGON((0 0, 50 0, 50 50, 0 50, 0 0))'::geometry, (SELECT MIN(entity_id) FROM entity), -1.5, 0 	-- LEFT
UNION SELECT 'POLYGON((0 0, 50 0, 50 50, 0 50, 0 0))'::geometry, (SELECT MIN(entity_id) FROM entity), 0, 1.5  	-- TOP
UNION SELECT 'POLYGON((0 0, 50 0, 50 50, 0 50, 0 0))'::geometry, (SELECT MIN(entity_id) FROM entity), 0, -1.5 	-- BOTTOM
;

-- NOW LETS MOVE THE PARENT
update entity SET the_geom = st_translate(the_geom, 0, 0) where entity_id = (SELECT MIN(entity_id) FROM entity)
update entity SET ratio_z = 0 where entity_id = (SELECT MIN(entity_id) FROM entity)

