DROP TABLE IF EXISTS :schema.links CASCADE;

CREATE TABLE :schema.links AS
WITH
-- 1. Toome välja kõik jooned, säilitades ka hwtype
multilines AS (
  SELECT id, geom, hwtype FROM :schema.ardok_highways
),

-- 2. Eemaldame MultiLineStringid ja lahti dumpime kõik jooned
dumped_1 AS (
  SELECT id, hwtype, (ST_Dump(geom)).geom AS geom FROM multilines
),

-- 3. Liidame kokku ainult sama hwtype-ga geomeetriad
unified AS (
  SELECT hwtype, ST_Union(geom) AS geom
  FROM dumped_1
  GROUP BY hwtype
),

-- 4. Jagame lõikudeks
split_lines AS (
  SELECT hwtype, (ST_Dump(ST_LineMerge(geom))).geom AS geom
  FROM unified
),

-- 5. Lõplikud servad: unikaalsed lõigud per hwtype
numbered AS (
  SELECT ROW_NUMBER() OVER () AS id, hwtype, geom
  FROM split_lines
  WHERE GeometryType(geom) = 'LINESTRING'
),

-- 6. Leiame seose lähteandmetega (eid)
info AS (
  SELECT 
    l.id,
    h.id AS eid,
    l.hwtype,
    l.geom
  FROM numbered l
  JOIN :schema.ardok_highways h
    ON ST_Intersects(h.geom, ST_LineSubstring(l.geom, 0.01, 0.99))
    AND h.hwtype = l.hwtype
)
SELECT id, MIN(eid) AS eid, hwtype, geom
FROM info
GROUP BY id, hwtype, geom;
