-- 📍 Batch-põhine versioon ristuvate, kuid ühendamata lõikude tuvastamiseks

SET search_path TO :'schema';

-- Loome tulemuse hoidmiseks tühja tabeli
DROP TABLE IF EXISTS :schema.intersections_unconnected;
CREATE TABLE :schema.intersections_unconnected (
  id1 INTEGER,
  id2 INTEGER,
  intersection_geom public.geometry(Point, 4326)
);

DO $$
DECLARE
  batch_size INTEGER := 10000;
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
  schema_name TEXT := current_setting('search_path');
BEGIN
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM schema_name.links;

  RAISE NOTICE '🔍 Kontrollin links.id vahemikus %–%', min_id, max_id;

  current_id := min_id;
  WHILE current_id <= max_id LOOP
    RAISE NOTICE '📦 Batch: % – %', current_id, current_id + batch_size - 1;

    INSERT INTO schema_name.intersections_unconnected (id1, id2, intersection_geom)
    SELECT 
      l1.id,
      l2.id,
      ST_Intersection(l1.geom, l2.geom)
    FROM schema_name.links l1
    JOIN schema_name.links l2
      ON ST_Intersects(l1.geom, l2.geom)
    WHERE l1.id BETWEEN current_id AND current_id + batch_size - 1
      AND l1.id < l2.id
      AND NOT (
        l1.source = l2.source OR l1.source = l2.target OR
        l1.target = l2.source OR l1.target = l2.target
      )
      AND GeometryType(ST_Intersection(l1.geom, l2.geom)) = 'POINT';

    current_id := current_id + batch_size;
  END LOOP;

  RAISE NOTICE '✅ Ristumised leitud ja salvestatud tabelisse intersections_unconnected.';
END
$$;
