#!/bin/bash
# --------------------------------------------------------------------------
# check-node-connectivity.sh  (Phase B-2 live validation)
# فحص جاهزية اتصال التقاطعات بالتوجيه على قاعدة البيانات الحية
#
# GraphHopper only connects ways that SHARE an OSM node id. This script verifies
# that the osm2pgsql `--slim` middle tables on THIS database can be used to resolve
# the real node id nearest each approved-road endpoint (the mechanism the API uses
# in connectApprovedRoad + apply-delta.sh). Run it on the DB host after an import.
#
# Usage:  bash infrastructure/scripts/check-node-connectivity.sh
# Exit 0 = ready, Exit 1 = middle tables unusable (roads draw but won't route-connect)
# --------------------------------------------------------------------------
set -euo pipefail

q() { docker compose exec -T db psql -U mapuser -d mapdb -t -A -X -c "$1" 2>/dev/null | tr -d '\r'; }
trim() { echo "$1" | tr -d '[:space:]'; }

echo "════════════════════════════════════════════════════════════"
echo " Node-connectivity diagnostic — osm2pgsql middle tables"
echo "════════════════════════════════════════════════════════════"

FAIL=0

# ── 1. Required columns present? ──────────────────────────────────────────
echo ""
echo "[1] Schema check:"
for pair in "planet_osm_ways:nodes" "planet_osm_nodes:lat" "planet_osm_nodes:lon"; do
  tbl="${pair%%:*}"; col="${pair##*:}"
  n=$(trim "$(q "SELECT COUNT(*) FROM information_schema.columns WHERE table_name='${tbl}' AND column_name='${col}'")")
  if [ "$n" = "1" ]; then
    echo "    ✓ ${tbl}.${col}"
  else
    echo "    ✗ ${tbl}.${col}  MISSING"
    FAIL=1
  fi
done
if [ "$FAIL" = "1" ]; then
  echo ""
  echo "  ⚠️  Middle tables are not in the expected shape. Most common cause:"
  echo "      the import used --flat-nodes (node locations go to a file, not a table)."
  echo "      Endpoint auto-connection will be skipped (roads still draw on tiles)."
  echo "      To enable it, re-import with --slim and WITHOUT --flat-nodes."
  exit 1
fi

# ── 2. Node coordinate reconstruction (scaling sanity) ────────────────────
echo ""
echo "[2] Node coordinate reconstruction (expect a sane lon/lat in your region):"
q "SELECT '    node '||id||'  ->  lon='||round((lon/1e7)::numeric,6)||'  lat='||round((lat/1e7)::numeric,6)
   FROM planet_osm_nodes
   WHERE lon BETWEEN 240000000 AND 430000000 AND lat BETWEEN 210000000 AND 380000000
   LIMIT 3"

# ── 3. Nearest-highway-node lookup around Amman city centre ───────────────
echo ""
echo "[3] Nearest-highway-node lookup near (35.91, 31.95):"
RESULT=$(q "
  WITH p AS (SELECT ST_Transform(ST_SetSRID(ST_MakePoint(35.91, 31.95), 4326), 3857) AS pt)
  SELECT n.id||' | lon='||round((n.lon/1e7)::numeric,6)||' lat='||round((n.lat/1e7)::numeric,6)
  FROM p
  JOIN planet_osm_line l ON l.highway IS NOT NULL AND l.way && ST_Expand(p.pt, 300)
  JOIN planet_osm_ways w ON w.id = l.osm_id
  CROSS JOIN LATERAL unnest(w.nodes) AS wn(node_id)
  JOIN planet_osm_nodes n ON n.id = wn.node_id
  ORDER BY ST_SetSRID(ST_MakePoint(n.lon/1e7, n.lat/1e7), 4326) <-> ST_Transform(p.pt, 4326)
  LIMIT 1")
if [ -n "$(trim "$RESULT")" ]; then
  echo "    ✓ resolved node → ${RESULT}"
else
  echo "    ✗ no highway node found near Amman — is OSM data imported for this region?"
  FAIL=1
fi

# ── 4. Current approved_roads connection status ───────────────────────────
echo ""
echo "[4] approved_roads connection status:"
if [ "$(trim "$(q "SELECT COUNT(*) FROM information_schema.tables WHERE table_name='approved_roads'")")" = "1" ]; then
  q "SELECT '    total='||COUNT(*)||'  with start_node='||COUNT(start_node)||'  with end_node='||COUNT(end_node) FROM approved_roads"
else
  echo "    (approved_roads not created yet — approve a candidate first)"
fi

echo ""
echo "════════════════════════════════════════════════════════════"
if [ "$FAIL" = "0" ]; then
  echo " ✅ READY — endpoint auto-connection will work on this database."
  echo "    New approvals get start_node/end_node; apply-delta.sh shares them,"
  echo "    and GraphHopper routes THROUGH approved roads after the next rebuild."
  exit 0
else
  echo " ❌ NOT READY — see messages above."
  exit 1
fi