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

# Võta kõik maakonnad admin_taseme 6 järgi
COUNTIES=$(psql  ${CONNECTION_NAME} -At -d "$destination_dbname" -c "SELECT name FROM ${destination_schema}.ardok_administrative WHERE admin_level = '6';")

for county in $COUNTIES; do
  safe_name=$(echo "$county" | tr ' ' '_' | tr -cd '[:alnum:]_')  # nt "Harju maakond" → "Harju_maakond"
  echo "📦 Töötlen $county → links_$safe_name"

  psql ${CONNECTION_NAME} -v schema=${destination_schema} -v county="'$county'" -v links_table="links_$safe_name" -d ${destination_dbname} <<'EOF'
\set ON_ERROR_STOP on

-- 0. Tee test_highways antud maakonna piires
DROP TABLE IF EXISTS test_highways;
CREATE TEMP TABLE test_highways AS
SELECT h.*
FROM :schema.ardok_highways h
JOIN :schema.ardok_administrative a ON ST_Intersects(h.geom, a.geom)
WHERE a.name = :county AND a.admin_level = '6';

-- 1. Ristumiste lõikamine
DROP TABLE IF EXISTS :schema._noded_geom;
CREATE UNLOGGED TABLE :schema._noded_geom AS
SELECT ST_Node(ST_Collect(geom)) AS geom FROM test_highways;

-- 2. Dump segmentideks
DROP TABLE IF EXISTS :schema._raw_segments;
CREATE UNLOGGED TABLE :schema._raw_segments AS
SELECT row_number() OVER () + 100000000 AS id,
       (ST_Dump(geom)).geom AS geom
FROM :schema._noded_geom;

-- 3. Lisa metaandmed
DROP TABLE IF EXISTS :schema.:links_table;
CREATE UNLOGGED TABLE :schema.:links_table AS
SELECT
  s.id,
  h.id AS 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 test_highways h
  WHERE h.geom && s.geom AND ST_Intersects(h.geom, s.geom)
  ORDER BY ST_Length(ST_Intersection(h.geom, s.geom)) DESC
  LIMIT 1
) h ON TRUE;

EOF

done

# NB! See osa võiks käivitada eraldi, kui paralleelselt jooksutad eelmist osa!
# Lõpuks: ühendame kõik links_* tabelid üheks
psql ${CONNECTION_NAME} -d ${destination_dbname} -v schema=${destination_schema} <<'EOF'
DROP TABLE IF EXISTS :schema.links;

CREATE TABLE :schema.links AS
SELECT * FROM (
  SELECT format('SELECT * FROM %I.%I', :'schema', tablename) AS sql
  FROM pg_tables
  WHERE schemaname = :'schema' AND tablename LIKE 'links_%'
) q,
LATERAL (
  EXECUTE q.sql
) AS link_row;

CREATE INDEX links_geom_idx ON :schema.links USING GIST (geom);
ALTER TABLE :schema.links ADD PRIMARY KEY (id);
EOF
