-- 📍 Batch-põhine skript: ekspordi veel ühendamata geomeetrilised ristumispunktid

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 tulemuse hoidmiseks tühja tabeli
  EXECUTE format('DROP TABLE IF EXISTS %I.intersection_points_remaining', schema_name);
  EXECUTE format('
    CREATE TABLE %I.intersection_points_remaining (
      geom geometry(Point, 3857),
      link1_id INTEGER,
      link2_id INTEGER
    )', schema_name);
  COMMIT;

  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.intersection_points_remaining (geom, link1_id, link2_id)
      SELECT DISTINCT
        ST_Intersection(l1.geom, l2.geom),
        l1.id,
        l2.id
      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
        )
        AND GeometryType(ST_Intersection(l1.geom, l2.geom)) = 'POINT'
    $f$, schema_name, schema_name, schema_name, current_id, current_id + batch_size - 1);
    COMMIT;

    current_id := current_id + batch_size;
  END LOOP;

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

  RAISE NOTICE '✅ Valmis: ühendamata ristumispunktid salvestatud tabelisse %.intersection_points_remaining', schema_name;
END
$$;
