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

echo "🏷️ Parandatud: lisame 'is_isolated' ja 'is_intersection' veerud korrektselt tabelisse ${destination_schema}.links"

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

-- 1. Lisa veerud kui neid pole
ALTER TABLE ${destination_schema}.links ADD COLUMN IF NOT EXISTS is_isolated BOOLEAN;
ALTER TABLE ${destination_schema}.links ADD COLUMN IF NOT EXISTS is_intersection BOOLEAN;

-- 2. Korrektselt loenda tippe (source ja target) ja märgista isoleeritud lõigud
WITH vertex_usage AS (
  SELECT source AS id FROM ${destination_schema}.links
  UNION ALL
  SELECT target FROM ${destination_schema}.links
),
degree AS (
  SELECT id, COUNT(*) AS deg FROM vertex_usage GROUP BY id
)
UPDATE ${destination_schema}.links l
SET is_isolated = TRUE
FROM degree s, degree t
WHERE l.source = s.id AND l.target = t.id
  AND s.deg = 1 AND t.deg = 1;

WITH vertex_usage AS (
  SELECT source AS id FROM ${destination_schema}.links
  UNION ALL
  SELECT target FROM ${destination_schema}.links
),
degree AS (
  SELECT id, COUNT(*) AS deg FROM vertex_usage GROUP BY id
),
high_degree AS (
  SELECT id FROM degree WHERE deg >= 3
)
UPDATE ${destination_schema}.links l
SET is_intersection = TRUE
FROM high_degree h
WHERE l.source = h.id OR l.target = h.id;


-- 4. Täida NULL väärtused FALSE-ga
UPDATE ${destination_schema}.links SET is_isolated = FALSE WHERE is_isolated IS NULL;
UPDATE ${destination_schema}.links SET is_intersection = FALSE WHERE is_intersection IS NULL;

EOF
