-- 📍 Logi kõik geomeetrilised ristumised, kus lõigud ei ole ühendatud (ükskõik mis geomeetria tüüp)

SET myvars.schema TO :'schema';

DO $$
DECLARE
  batch_size INTEGER := 10000;
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
  schema_name TEXT := current_setting('myvars.schema');
BEGIN
  -- Loome logitabeli
  EXECUTE format('DROP TABLE IF EXISTS %I.intersections_all_geom', schema_name);
  EXECUTE format('
    CREATE TABLE %I.intersections_all_geom (
      geom geometry,
      link1_id INTEGER,
      link2_id INTEGER,
      geom_type TEXT
    )', schema_name);

  EXECUTE format('SELECT MIN(id), MAX(id) FROM %I.links', schema_name)
  INTO min_id, max_id;

  current_id := min_id;

  WHILE current_id <= max_id LOOP
    RAISE NOTICE '📦 Töötlen batch: % – %', current_id, current_id + batch_size - 1;

    EXECUTE format($f$
      INSERT INTO %I.intersections_all_geom (geom, link1_id, link2_id, geom_type)
      SELECT
        ST_Intersection(l1.geom, l2.geom),
        l1.id,
        l2.id,
        GeometryType(ST_Intersection(l1.geom, l2.geom))
      FROM %I.links l1
      JOIN %I.links l2
        ON ST_Intersects(l1.geom, l2.geom)
      WHERE l1.id BETWEEN %s AND %s
        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
        )
    $f$, schema_name, schema_name, schema_name, current_id, current_id + batch_size - 1);

    current_id := current_id + batch_size;
  END LOOP;

  EXECUTE format('CREATE INDEX ON %I.intersections_all_geom USING GIST (geom)', schema_name);

  RAISE NOTICE '✅ Kõik ruumilised ristumised logitud tabelisse %.intersections_all_geom', schema_name;
END
$$;
