-- pk_ modeline (pozor na dvojtecku na konci)
-- vim: set filetype=plsql tabstop=2 shiftwidth=2:


CREATE OR REPLACE FUNCTION do_newlayer()
	RETURNS void AS
$BODY$
DECLARE
	recrow RECORD;
BEGIN
	DROP TABLE newlayer ;
	CREATE TABLE newlayer ( gid int PRIMARY KEY, stav int, split int, obce_id int, name_a text, name_i text, name_u text, the_geom geometry ) ;
	FOR recrow IN
		SELECT a.gid as agid, a.kod as akod, a.the_geom as athegeom
		FROM pracovni a
		ORDER BY a.gid
	LOOP
		EXECUTE 'INSERT INTO newlayer (gid, stav, the_geom) VALUES ('||recrow.agid||', -1,\''||recrow.athegeom||'\')';
	END LOOP;	
	RETURN;
END;
$BODY$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION clear_touches()
	RETURNS void AS
$BODY$
DECLARE
	aflag int;
	bflag int;
	xflag int;
	astav int;
	bstav int;
	recrow RECORD;
BEGIN
	FOR recrow IN
		SELECT a.gid as agid, b.gid as bgid, a.the_geom as athegeom, b.the_geom as bthegeom
--    FROM pracovni a CROSS JOIN pracovni b
		FROM newlayer a CROSS JOIN newlayer b
		WHERE a.the_geom && b.the_geom
--    AND a.the_geom && SetSRID('BOX3D(14.2519395480583260 49.9565913219706133, 14.7211093910930479 50.2204851203498777)'::box3d,4326)
--    AND a.gid = 537
--    AND a.gid IN (495, 496, 497)
		AND a.gid > b.gid
--    AND area(a.the_geom) > area(b.the_geom)
--    AND Equals(a.the_geom, b.the_geom)
		ORDER BY a.gid DESC -- desc je od nejvetsiho
	LOOP
-- stanovit flag a
			SELECT INTO xflag stav FROM newlayer WHERE gid = recrow.agid;
			IF    xflag = -1 THEN aflag := 1;
			ELSIF xflag = -2 THEN aflag := 2;
			ELSE aflag := 0;
			END IF;
-- stanovit flag b
			SELECT INTO xflag stav  FROM newlayer WHERE gid = recrow.bgid;
      IF    xflag = -1 THEN bflag := 1;
			ELSIF xflag = -2 THEN bflag := 2;
			ELSE bflag := 0;
			END IF;
-- vyresit hlavni strom
			IF Relate(recrow.athegeom, recrow.bthegeom, 'FF2F11212') THEN
				IF aflag = 1 AND bflag = 1 THEN
					RAISE NOTICE '%:% - %:%', aflag, bflag, recrow.agid, recrow.bgid;
					EXECUTE 'UPDATE newlayer SET stav=-2, the_geom=GeomUnion((SELECT the_geom FROM newlayer WHERE gid = '||recrow.agid||'), (SELECT the_geom FROM newlayer WHERE gid = '||recrow.bgid||')) WHERE gid ='||recrow.agid ;
					EXECUTE 'UPDATE newlayer SET stav='||recrow.agid||', the_geom=NULL WHERE gid ='||recrow.bgid ;
				ELSIF aflag = 0 AND bflag = 1 THEN
          SELECT INTO astav stav FROM newlayer WHERE gid = recrow.agid;
          IF astav <> recrow.bgid THEN
						RAISE NOTICE '%:% - %:% %:', aflag, bflag, recrow.agid, recrow.bgid, astav;
						EXECUTE 'UPDATE newlayer SET the_geom=GeomUnion((SELECT the_geom FROM newlayer WHERE gid = '||astav||'), (SELECT the_geom FROM newlayer WHERE gid = '||recrow.bgid||')) WHERE gid ='||astav;
						EXECUTE 'UPDATE newlayer SET stav='||astav||', the_geom=NULL WHERE gid ='||recrow.bgid ;
					END IF;					
				ELSIF aflag = 1 AND bflag = 0 THEN
          SELECT INTO bstav stav FROM newlayer WHERE gid = recrow.bgid;
          IF recrow.agid <> bstav THEN
						RAISE NOTICE '%:% - %:%    :%', aflag, bflag, recrow.agid, recrow.bgid, bstav;
	  				EXECUTE 'UPDATE newlayer SET the_geom=GeomUnion((SELECT the_geom FROM newlayer WHERE gid = '||recrow.agid||'), (SELECT the_geom FROM newlayer WHERE gid = '||bstav||')) WHERE gid ='||bstav;
						EXECUTE 'UPDATE newlayer SET stav='||bstav||', the_geom=NULL WHERE gid ='||recrow.agid ;
					END IF;					
				ELSIF aflag = 0 AND bflag = 0 THEN
          SELECT INTO astav stav FROM newlayer WHERE gid = recrow.agid;
          SELECT INTO bstav stav FROM newlayer WHERE gid = recrow.bgid;
          IF astav <> bstav THEN
						RAISE NOTICE '%:% - %:% %:%', aflag, bflag, recrow.agid, recrow.bgid, astav, bstav;
						EXECUTE 'UPDATE newlayer SET the_geom=GeomUnion((SELECT the_geom FROM newlayer WHERE gid = '||astav||'), (SELECT the_geom FROM newlayer WHERE gid = '||bstav||')) WHERE gid = '||astav;
						EXECUTE 'UPDATE newlayer SET stav='||astav||', the_geom=NULL WHERE gid = '||bstav;
						EXECUTE 'UPDATE newlayer SET stav='||astav||' WHERE stav = '||bstav;
					END IF;					
				END IF;
			END IF;
	END LOOP;	
	RETURN;
END;
$BODY$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION get_names()
	RETURNS void AS
$BODY$
DECLARE
	recrow RECORD;
	rr RECORD;
	nmz int;
	obv numeric;
	obvx numeric;
BEGIN
	FOR recrow IN
		SELECT gid, the_geom as tg FROM newlayer
	LOOP
		SELECT INTO nmz count(*) FROM obce WHERE the_geom && recrow.tg AND within(the_geom, recrow.tg);
		IF nmz = 1 THEN
			SELECT INTO rr cat, nazob FROM obce WHERE the_geom && recrow.tg AND within( the_geom, recrow.tg ) ;
			RAISE NOTICE '%: % -> %', nmz, recrow.gid, rr.nazob;
      EXECUTE 'UPDATE newlayer SET obce_id=\''||rr.cat||'\', name_a=(SELECT to_ascii(\''||rr.nazob||'\',\'ISO-8859-2\')), name_i=\''||rr.nazob||'\', name_u=(SELECT convert(\''||rr.nazob||'\' using iso_8859_2_to_utf_8)) WHERE gid='||recrow.gid ;
		ELSIF nmz > 1 THEN
			obv = 0;
			FOR rr IN
      SELECT cat, nazob, the_geom as tg FROM obce WHERE the_geom && recrow.tg AND within( the_geom, recrow.tg )
			LOOP
				SELECT INTO obvx obakt FROM obce WHERE cat=rr.cat ;
					IF obv < obvx THEN
      			EXECUTE 'UPDATE newlayer SET split='||nmz||', obce_id=\''||rr.cat||'\', name_a=(SELECT to_ascii(\''||rr.nazob||'\',\'ISO-8859-2\')), name_i=\''||rr.nazob||'\', name_u=(SELECT convert(\''||rr.nazob||'\' using iso_8859_2_to_utf_8)) WHERE gid='||recrow.gid ;
						obv = obvx;
					END IF ;
			RAISE NOTICE '%: % --> %  : %', nmz, recrow.gid, rr.nazob, obv;
			END LOOP;
		END IF ;
	END LOOP;	
	RETURN;
END;
$BODY$ LANGUAGE plpgsql;




SELECT do_newlayer() ;
SELECT clear_touches() ;
SELECT get_names() ;
ALTER TABLE newlayer DROP COLUMN stav;
DELETE FROM newlayer WHERE the_geom IS NULL ;