-- Valga halduspiirkonna järgi
DROP TABLE IF EXISTS :schema.test_highways;
CREATE TABLE :schema.test_highways AS
SELECT   h.id eid,
  h.hwtype,
  h.surface,
  h.oneway,
  h.maxspeed,
  h.name,
  ST_Transform(h.geom, 3301) AS geom
FROM :schema.ardok_highways h
JOIN :schema.ardok_administrative p
  ON ST_Intersects(h.geom, p.geom)
WHERE p.name like 'Valga%' AND p.admin_level = '7';


-- 0. Eemalda olemasolev
DROP TABLE IF EXISTS :schema.links CASCADE;
DROP TABLE IF EXISTS :schema._noded_geom CASCADE;
DROP TABLE IF EXISTS :schema._raw_segments CASCADE;

-- 1. Lõika geomeetriad ristumiskohtades
CREATE UNLOGGED TABLE :schema._noded_geom AS
SELECT
  ST_Node(ST_Collect(geom)) AS geom  -- ühendame kõik teed ja lõikame ristumised
FROM :schema.test_highways;

-- 2. Võta igast lõigatud joonest üks segment
CREATE UNLOGGED TABLE :schema._raw_segments AS
SELECT 
  row_number() OVER () + 100000000 AS id, geom from ( SELECT
  (ST_Dump(geom)).geom AS geom
FROM :schema._noded_geom) rs;

-- 3. Lisa metaandmed lõigule, mille sees või mille osana link asub
CREATE UNLOGGED TABLE :schema.links AS
SELECT
  s.id,
  h.eid,
  h.hwtype AS highway,
  h.surface,
  h.oneway,
  h.maxspeed,
  COALESCE(NULLIF(h.name, ''), NULLIF(h.hwtype, ''), 'Unnamed road') AS name,
  s.geom
FROM :schema._raw_segments s
JOIN LATERAL (
  SELECT *
  FROM :schema.test_highways h
  WHERE ST_Intersects(h.geom, s.geom)  -- NB! sisuline kattuvus
  ORDER BY ST_Length(ST_Intersection(h.geom, s.geom)) DESC
  LIMIT 1
) h ON TRUE;


-- 4. Indeks ja PK
CREATE INDEX links_geom_idx ON :schema.links USING GIST (geom);
ALTER TABLE :schema.links ADD PRIMARY KEY (id);
