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

echo "🏷️ Märgistan is_isolated ja is_intersection batch'ide kaupa tabelis ${destination_schema}.links"

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

DO \$\$
DECLARE
  batch_size INTEGER := 10000;
  min_id INTEGER;
  max_id INTEGER;
  current_id INTEGER;
BEGIN
  -- Leia ID vahemik
  SELECT MIN(id), MAX(id) INTO min_id, max_id FROM ${destination_schema}.links;
  current_id := min_id;

  -- Lisa veerud kui puudu
  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;

  WHILE current_id <= max_id LOOP
    -- Märgista is_isolated
    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
    )
    UPDATE ${destination_schema}.links l
    SET is_isolated = TRUE
    FROM degree s, degree t
    WHERE l.id BETWEEN current_id AND current_id + batch_size - 1
      AND l.source = s.id AND l.target = t.id
      AND s.deg = 1 AND t.deg = 1;

    -- Märgista is_intersection
    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
    ),
    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.id BETWEEN current_id AND current_id + batch_size - 1
      AND (l.source = h.id OR l.target = h.id);

    RAISE NOTICE '✅ Töödeldud id vahemik: % – %', current_id, current_id + batch_size - 1;

    current_id := current_id + batch_size;
  END LOOP;

  -- Lõpus 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;

END
\$\$;

EOF
