#!/bin/bash
set -e
source env.sh

echo "🔁 Konverteerin links_split → links valmis graafiks skeemis ${destination_schema}"

psql ${CONNECTION_NAME} -d ${destination_dbname} <<EOF

-- 1. Loo uus tabel, millel on graafi struktuur
DROP TABLE IF EXISTS ${destination_schema}.links_graph;
CREATE TABLE ${destination_schema}.links_graph (
  id SERIAL PRIMARY KEY,
  geom geometry(LineString, 3857),
  source INTEGER,
  target INTEGER,
  cost DOUBLE PRECISION,
  reverse_cost DOUBLE PRECISION
);

-- 2. Loo ajutine tiputabel tippude määramiseks
DROP TABLE IF EXISTS ${destination_schema}.tmp_vertices;
CREATE UNLOGGED TABLE ${destination_schema}.tmp_vertices AS
SELECT DISTINCT
  ST_StartPoint(geom) AS pt
FROM ${destination_schema}.links_split
UNION
SELECT DISTINCT
  ST_EndPoint(geom) AS pt
FROM ${destination_schema}.links_split;

-- 3. Lisa unikaalsed ID-d tippudele
ALTER TABLE ${destination_schema}.tmp_vertices ADD COLUMN id SERIAL PRIMARY KEY;
CREATE INDEX tmp_vertices_geom_idx ON ${destination_schema}.tmp_vertices USING GIST (pt);

-- 4. Loo links_graph, määrates source/target vastavalt tippudele
INSERT INTO ${destination_schema}.links_graph (geom, source, target, cost, reverse_cost)
SELECT
  l.geom,
  sv.id AS source,
  tv.id AS target,
  ST_Length(l.geom) AS cost,
  ST_Length(l.geom) AS reverse_cost
FROM ${destination_schema}.links_split l
JOIN ${destination_schema}.tmp_vertices sv ON ST_DWithin(ST_StartPoint(l.geom), sv.pt, 0.001)
JOIN ${destination_schema}.tmp_vertices tv ON ST_DWithin(ST_EndPoint(l.geom), tv.pt, 0.001);

-- 5. Lõpuks tee indeks
CREATE INDEX links_graph_geom_idx ON ${destination_schema}.links_graph USING GIST (geom);

EOF

echo "✅ Uus graaf links_graph on loodud tabelist links_split (koos source/target/cost)"
