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

echo "🏝️ Kontrollin isoleeritud lõike ja kas need asuvad saartel"

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

-- 1. Tee ajutine tabel isoleeritud lõikudest
DROP TABLE IF EXISTS ${destination_schema}.isolated_links;
CREATE UNLOGGED TABLE ${destination_schema}.isolated_links AS
SELECT id, geom
FROM ${destination_schema}.links
WHERE source = target
   OR id IN (
     SELECT l.id
     FROM ${destination_schema}.links l
     LEFT JOIN ${destination_schema}.links_vertices_pgr v
       ON l.source = v.id OR l.target = v.id
     GROUP BY l.id
     HAVING COUNT(DISTINCT v.id) = 1
   );

-- 2. Lisa veerg 'is_on_island' → TRUE kui asub saarel
ALTER TABLE ${destination_schema}.isolated_links ADD COLUMN is_on_island boolean;

-- 3. Märgista need, mis asuvad haldusüksuses, mille nimi sisaldab 'saar' või place='island'
UPDATE ${destination_schema}.isolated_links i
SET is_on_island = TRUE
FROM ${destination_schema}.ardok_administrative a
WHERE ST_Intersects(i.geom, a.geom)
  AND (a.name ILIKE '%saar%' OR a.place = 'island');

-- 4. Vaikimisi märgi ülejäänud FALSE
UPDATE ${destination_schema}.isolated_links
SET is_on_island = FALSE
WHERE is_on_island IS NULL;

-- 5. Kokkuvõte: mitu lõiku on saartel / maismaal
SELECT is_on_island, COUNT(*) AS count
FROM ${destination_schema}.isolated_links
GROUP BY is_on_island;

EOF
