\set full_table :schema'.test_links_snapped'


-- Valga halduspiirkonna järgi
DROP TABLE IF EXISTS :schema.test_highways;
CREATE TABLE :schema.test_highways AS
SELECT ST_Transform(h.geom, 3301) AS geom, h.id, h.hwtype
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';

-- 2. Lõikude genereerimine: dumpimine, snap, LineMerge
DROP TABLE IF EXISTS :schema.test_links;
CREATE TABLE :schema.test_links AS
WITH
  dumped AS (
    SELECT id, hwtype, (ST_Dump(geom)).geom AS geom FROM :schema.test_highways
  ),
  snapped AS (
    SELECT id AS eid, hwtype, ST_SnapToGrid(geom, 0.01) AS geom FROM dumped
  ),
  segmented AS (
    SELECT eid, hwtype, (ST_Dump(ST_LineMerge(geom))).geom AS geom FROM snapped
  )
SELECT ROW_NUMBER() OVER () AS id, eid, hwtype, geom
FROM segmented
WHERE GeometryType(geom) = 'LINESTRING';

-- 3. Tippude klasterdamine ST_ClusterDBSCAN abil
DROP TABLE IF EXISTS :schema.clustered_nodes;
CREATE TABLE :schema.clustered_nodes AS
SELECT
  ROW_NUMBER() OVER () AS id,
  ST_Centroid(ST_Collect(geom)) AS geom
FROM (
  SELECT ST_ClusterDBSCAN(geom, eps := 0.5, minpoints := 1) OVER () AS cid, geom
  FROM (
    SELECT ST_StartPoint(geom) AS geom FROM :schema.test_links
    UNION ALL
    SELECT ST_EndPoint(geom) AS geom FROM :schema.test_links
  ) AS pts
) AS clusters
GROUP BY cid;
CREATE INDEX ON :schema.clustered_nodes USING GIST (geom);

-- 4. Uute snapped linkide loomine klastri tippude vahele
DROP TABLE IF EXISTS :schema.test_links_snapped;
CREATE TABLE :schema.test_links_snapped AS
SELECT
  l.id,
  l.eid,
  l.hwtype,
  ST_MakeLine(n1.geom, n2.geom) AS geom
FROM :schema.test_links l
JOIN :schema.clustered_nodes n1 ON ST_DWithin(ST_StartPoint(l.geom), n1.geom, 0.5)
JOIN :schema.clustered_nodes n2 ON ST_DWithin(ST_EndPoint(l.geom), n2.geom, 0.5);

-- 5. Lisa source, target, cost veerud
ALTER TABLE :schema.test_links_snapped ADD COLUMN source bigint;
ALTER TABLE :schema.test_links_snapped ADD COLUMN target bigint;
ALTER TABLE :schema.test_links_snapped ADD COLUMN cost double precision;

WITH tmp AS (
  SELECT
    l.id,
    n1.id AS source,
    n2.id AS target,
    ST_Length(l.geom) AS cost
  FROM :schema.test_links_snapped l
  JOIN :schema.clustered_nodes n1 ON ST_Equals(ST_StartPoint(l.geom), n1.geom)
  JOIN :schema.clustered_nodes n2 ON ST_Equals(ST_EndPoint(l.geom), n2.geom)
)
UPDATE :schema.test_links_snapped l
SET source = tmp.source,
    target = tmp.target,
    cost = ROUND(tmp.cost::numeric, 1)
FROM tmp
WHERE l.id = tmp.id;

-- 6. PGROUTING graafi ettevalmistus ja analüüs
-- Loo tipud servade põhjal
SELECT pgr_createVerticesTable(:'full_table', 'geom', 'source', 'target');

-- Analüüsi graafi struktuuri
SELECT * FROM pgr_analyzeGraph(:'full_table', 0.001, 'geom', 'id', 'source', 'target');


-- Lisa component veerg, kui puudub
ALTER TABLE :schema.test_links_snapped ADD COLUMN component integer;

-- Komponentide arvutamine
DROP TABLE IF EXISTS :schema.test_components;
CREATE TABLE :schema.test_components AS
SELECT * FROM pgr_connectedComponents(
  'SELECT id, source, target, cost FROM ' || :'full_table'
);

-- Lisa component ID linkidele
UPDATE :schema.test_links_snapped l
SET component = c.component
FROM :schema.test_components c
WHERE l.id = c.component;