BEGIN;

CREATE SCHEMA jizera;

SET SEARCH_PATH TO jizera, dibavod, public;

CREATE TABLE povodi_jizery (
    gid SERIAL PRIMARY KEY,
    tok_id numeric UNIQUE,
    max_utokjn numeric,
    tokrec_id numeric,
    idvt numeric(10,0),
    naz_tok character varying(30),
    shape_len numeric,
    geom public.geometry(MultiLineString,5514),
    rad int
);


CREATE INDEX ON povodi_jizery USING gist (geom);

INSERT INTO povodi_jizery 
(tok_id, max_utokjn, tokrec_id, idvt, naz_tok, shape_len, geom, rad)
WITH RECURSIVE povodi AS (
   SELECT tok_id
   , max_utokjn
   , tokrec_id
   , idvt
   , naz_tok
   , shape_len
   , geom
   , 1::int rad
   FROM vodni_toky
   WHERE tok_id = 110740000100 
   UNION ALL
   SELECT v.tok_id
   , v.max_utokjn
   , v.tokrec_id
   , v.idvt
   , v.naz_tok
   , v.shape_len
   , v.geom
   , rad + 1
   FROM vodni_toky v
   JOIN povodi
   ON povodi.tok_id = v.tokrec_id
   WHERE rad < 100000
)

SELECT * FROM povodi;





COMMIT;



SET SEARCH_PATH TO jizera, dibavod, public;

CREATE TABLE jizera.jize (ogc_fid serial primary key, geom geometry(LINESTRING, 5514));

INSERT INTO jizera.jize (geom)
SELECT (ST_Dump(geom)).geom FROM
(
   SELECT ST_Union(geom) geom FROM jizera.povodi_jizery
) uni
;
BEGIN;

CREATE INDEX ON jize USING gist(geom);

ALTER TABLE jize ADD rad smallint, ADD parent int;

UPDATE jize
SET parent = 0, rad = 1 WHERE ogc_fid = 1733;


DO $$
   DECLARE i int;
   BEGIN
      WHILE (SELECT count(*) FROM jize WHERE rad IS NULL) > 0
	 LOOP
	    UPDATE jize j
	    SET rad = r.rad+1, parent = r.ogc_fid
	    FROM jize r
	    WHERE r.rad IS NOT NULL 
	    AND j.rad IS NULL
	    AND ST_Touches(j.geom, r.geom)
	    ;

	    RAISE NOTICE '%', count(*) FROM jize WHERE rad IS NULL;
	    


	 END LOOP;
      /*
      FOR i IN 1..25
	 LOOP
	    UPDATE jize j
	    SET rad = r.rad+1, parent = r.ogc_fid
	    FROM jize r
	    WHERE r.rad IS NOT NULL 
	    AND j.rad IS NULL
	    AND ST_Touches(j.geom, r.geom)
	    ;

	    RAISE NOTICE '%', count(*) FROM jize WHERE rad IS NULL;
	    


	 END LOOP;
      */

   END
   $$;
   
--SELECT ogc_fid, parent, rad from jize where rad is not null;



COMMIT;


BEGIN;

ALTER TABLE jize ADD sum_d float;

CREATE INDEX ON jize (parent);

UPDATE jize
SET sum_d = ST_Length(geom)
WHERE ogc_fid NOT IN
(
   SELECT DISTINCT parent
   FROM jize
);

DO
   $$
   BEGIN
      WHILE (SELECT count(*) FROM jize WHERE sum_d IS NULL) > 0
	 LOOP
	    UPDATE jize j
	    SET sum_d = update_sum_d + ST_Length(geom)
	    FROM
	    (
	       SELECT parent, sum(sum_d) update_sum_d
	       FROM jize
	       GROUP BY parent
	       --HAVING sum(sum_d) IS NOT NULL 
	       --sum bere null jako NULU
	       HAVING count(*) = count(sum_d)
	       --pocet odpovida notnull
	    ) upd
	    WHERE j.sum_d IS NULL
	    AND upd.parent = j.ogc_fid;

	    RAISE NOTICE '%', count(*) FROM jize WHERE sum_d IS NULL;
	    


	 END LOOP;
      END

   $$;

COMMIT;

--CREATE TABLE t AS

/*
WITH RECURSIVE od_pramene AS (
   SELECT *
   , 1 tloust
   FROM jize 
   WHERE ogc_fid NOT IN
   (
      SELECT DISTINCT parent
      FROM jize
   )
   UNION ALL
   SELECT
   j.*
   FROM jize j
   JOIN LATERAL (
      SELECT 
      sum(tloust) tloust
      FROM od_pramene o
      WHERE j.ogc_fid = o.parent 
   ) tl
   ON tloust IS NOT NULL --musi bejt vsechny vetve spoctene

)

SELECT * FROM od_pramene;
*/

--v recur nejde agg


BEGIN;

ALTER TABLE jize ADD tloust INT;

CREATE INDEX ON jize (parent);

UPDATE jize
SET tloust = 1
WHERE ogc_fid NOT IN
(
   SELECT DISTINCT parent
   FROM jize
);

DO
   $$
   BEGIN
      WHILE (SELECT count(*) FROM jize WHERE tloust IS NULL) > 0
	 LOOP
	    UPDATE jize j
	    SET tloust = update_tloust
	    FROM
	    (
	       SELECT parent, sum(tloust) update_tloust
	       FROM jize
	       GROUP BY parent
	       --HAVING sum(tloust) IS NOT NULL 
	       --sum bere null jako NULU
	       HAVING count(*) = count(tloust)
	       --pocet odpovida notnull
	    ) upd
	    WHERE j.tloust IS NULL
	    AND upd.parent = j.ogc_fid;

	    RAISE NOTICE '%', count(*) FROM jize WHERE tloust IS NULL;
	    


	 END LOOP;
      END

   $$;

COMMIT;

