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

echo "🔄 Parandatud: Tuvastan ja jagan ristuvad lõigud batch'ide kaupa skeemis ${destination_schema} (SRID 3857)"

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

-- 1. Tabel ristuvate lõikude jaoks (kasutame SRID 3857)
DROP TABLE IF EXISTS ${destination_schema}.intersecting_links;
CREATE UNLOGGED TABLE ${destination_schema}.intersecting_links (
  id1 INTEGER,
  id2 INTEGER,
  intersection_geom GEOMETRY(POINT, 3857)
);

-- 2. Tuvasta ristuvad lõigud batch'ide 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
    INSERT INTO ${destination_schema}.intersecting_links
    SELECT
      l1.id AS id1,
      l2.id AS id2,
      ST_Intersection(l1.geom, l2.geom) AS intersection_geom
    FROM ${destination_schema}.links l1
    JOIN ${destination_schema}.links l2
      ON ST_Intersects(l1.geom, l2.geom)
    WHERE l1.id BETWEEN current_id AND current_id + batch_size - 1
      AND l1.id < l2.id
      AND NOT (
        l1.source = l2.source OR l1.source = l2.target
        OR l1.target = l2.source OR l1.target = l2.target
      )
      AND GeometryType(ST_Intersection(l1.geom, l2.geom)) = 'POINT';
    COMMIT;

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

-- 3. Loo punktikiht
DROP TABLE IF EXISTS ${destination_schema}.intersection_points;
CREATE TABLE ${destination_schema}.intersection_points AS
SELECT DISTINCT intersection_geom AS geom
FROM ${destination_schema}.intersecting_links;

-- 4. Lõika lõigud tükkideks
DROP TABLE IF EXISTS ${destination_schema}.links_split;
CREATE TABLE ${destination_schema}.links_split AS
SELECT
  l.id,
  (ST_Dump(ST_Split(l.geom, p.geom))).geom AS geom
FROM ${destination_schema}.links l
LEFT JOIN ${destination_schema}.intersection_points p
  ON ST_DWithin(l.geom, p.geom, 0.001) AND ST_Intersects(l.geom, p.geom);

ALTER TABLE ${destination_schema}.links_split ADD COLUMN new_id SERIAL PRIMARY KEY;

EOF

echo "✅ Ristuvad lõigud tuvastatud ja jagatud osadeks (${destination_schema}.links_split)"
