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

echo "🚧 Loon lõpliku teedevõrgu batch'ide kaupa skeemis ${destination_schema} (täpne veerustruktuur)"

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

-- 1. Loo isoleeritud lõikude tabel
DROP TABLE IF EXISTS ${destination_schema}.isolated_links;
CREATE UNLOGGED TABLE ${destination_schema}.isolated_links (id INTEGER);

-- 2. Tuvasta isoleeritud lõigud batchide kaupa
DO \$\$
DECLARE
  batch_size INTEGER := 10000;
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
BEGIN
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM ${destination_schema}.links;
  current_id := min_id;

  WHILE current_id <= max_id LOOP
    WITH vertex_usage AS (
      SELECT source AS id FROM ${destination_schema}.links
      WHERE id BETWEEN current_id AND current_id + batch_size - 1
      UNION ALL
      SELECT target FROM ${destination_schema}.links
      WHERE id BETWEEN current_id AND current_id + batch_size - 1
    ),
    degree AS (
      SELECT id, COUNT(*) AS deg FROM vertex_usage GROUP BY id
    )
    INSERT INTO ${destination_schema}.isolated_links
    SELECT l.id
    FROM ${destination_schema}.links l
    JOIN degree s ON l.source = s.id
    JOIN degree t ON l.target = t.id
    WHERE l.id BETWEEN current_id AND current_id + batch_size - 1
      AND s.deg = 1 AND t.deg = 1;

    RAISE NOTICE '🧹 Kontrollitud vahemik: % – %', current_id, current_id + batch_size - 1;
    current_id := current_id + batch_size;
  END LOOP;
END
\$\$;

-- 3. Loo lõplik graaf sama veerustruktuuriga
DROP TABLE IF EXISTS ${destination_schema}.links_final;
CREATE TABLE ${destination_schema}.links_final AS
SELECT * FROM ${destination_schema}.links
WHERE id NOT IN (SELECT id FROM ${destination_schema}.isolated_links)
UNION ALL
SELECT
  NULL::INTEGER AS eid,
  NULL::TEXT AS highway,
  NULL::TEXT AS surface,
  NULL::SMALLINT AS oneway,
  NULL::INTEGER AS maxspeed,
  NULL::TEXT AS name,
  geom,
  NULL::INTEGER AS id,
  source,
  target,
  cost,
  reverse_cost
FROM ${destination_schema}.links_graph;

-- 4. Loo ruumiline indeks
CREATE INDEX links_final_geom_idx ON ${destination_schema}.links_final USING GIST (geom);

-- 5. PGROUTING ettevalmistus
DROP TABLE IF EXISTS ${destination_schema}.links_final_vertices_pgr;

SELECT pgr_createVerticesTable('${destination_schema}.links_final', 'geom', 'source', 'target');
SELECT pgr_analyzeGraph('${destination_schema}.links_final', 0.001, the_geom := 'geom', id := 'id', source := 'source', target := 'target');

EOF

echo "✅ Lõplik batch'ide põhine graaf loodud: links_final ja tipud links_final_vertices_pgr"
