-- 🔧 Ühendab lähestikku asuvad tipud uue lõiguga, kui nad pole juba ühendatud

SET myvars.schema TO :'schema';

DO $$
DECLARE
  min_dist DOUBLE PRECISION := 0.5;  -- piir: 0.5 kraadi (~50 m)
  schema_name TEXT := current_setting('myvars.schema');
BEGIN
  RAISE NOTICE '🔗 Loome ühendavaid lõike lähedastele tippudele skeemis %', schema_name;

  -- Loome uue tabeli parandatud ühenduste jaoks
  EXECUTE format('
    DROP TABLE IF EXISTS %I.links_gap_fixes;
    CREATE TABLE %I.links_gap_fixes AS
    SELECT
      nextval(pg_get_serial_sequence(%L, %L)) AS id,
      v1.id AS source,
      v2.id AS target,
      ST_MakeLine(v1.the_geom, v2.the_geom) AS geom,
      NULL::INTEGER AS eid,
      NULL::TEXT AS highway,
      NULL::TEXT AS surface,
      0::SMALLINT AS oneway,
      NULL::INTEGER AS maxspeed,
      ''gap_fix''::TEXT AS name,
      NULL::FLOAT8 AS cost,
      NULL::FLOAT8 AS reverse_cost
    FROM %I.links_vertices_pgr v1
    JOIN %I.links_vertices_pgr v2
      ON v1.id < v2.id
    WHERE ST_DWithin(v1.the_geom, v2.the_geom, %s)
      AND NOT EXISTS (
        SELECT 1 FROM %I.links l
        WHERE (l.source = v1.id AND l.target = v2.id)
           OR (l.source = v2.id AND l.target = v1.id)
      );
  ', schema_name, schema_name, schema_name || '.links', 'id',
     schema_name, schema_name, min_dist, schema_name);

  -- Lisame parandatud lõigud olemasolevasse links-tabelisse
  EXECUTE format('
    INSERT INTO %I.links (
      id, source, target, geom, eid, highway, surface, oneway, maxspeed, name, cost, reverse_cost
    )
    SELECT
      id, source, target, geom, eid, highway, surface, oneway, maxspeed, name, cost, reverse_cost
    FROM %I.links_gap_fixes;
  ', schema_name, schema_name);

  RAISE NOTICE '✅ Paranduslõigud lisatud tabelisse %I.links', schema_name;
END
$$;
