-- 1. Set schema and search path
SET search_path TO :schema, public;

-- 2. Drop and recreate the intersections_unconnected table
DROP TABLE IF EXISTS :"schema".intersections_unconnected;
CREATE TABLE :"schema".intersections_unconnected (
  id1 INTEGER,
  id2 INTEGER,
  intersection_geom geometry(Point, 3857)
);

-- 3. Begin batch-based detection of unconnected intersections
DO $$
DECLARE
  batch_size INTEGER := 10000;
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
  schema_name TEXT := current_schema;  -- ⬅ PARANDUS
BEGIN
  EXECUTE format('SELECT MIN(id), MAX(id) FROM %I.links', schema_name)
  INTO min_id, max_id;

  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;

    EXECUTE format(
      'INSERT INTO %I.intersections_unconnected (id1, id2, intersection_geom)
       SELECT l1.id, l2.id, ST_Intersection(l1.geom, l2.geom)::geometry(Point, 3857)
       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''',
      schema_name, schema_name, schema_name, current_id, current_id + batch_size - 1
    );

    current_id := current_id + batch_size;
  END LOOP;

  RAISE NOTICE '✅ Ristumised salvestatud tabelisse %.intersections_unconnected.', schema_name;
END
$$;
