#!/bin/bash
# verify-db-consolidation.sh
# Quick check of consolidated database

DB="${1:-clavitor.ai/clavitor.db}"

echo "=== Clavitor DB Verification ==="
echo "Database: $DB"
echo ""

# Check tables exist
echo "Tables present:"
sqlite3 "$DB" "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;"

echo ""
echo "=== Core Data ==="

# POPs
echo ""
echo "POPs (source of truth):"
sqlite3 "$DB" "SELECT status, COUNT(*) FROM pops GROUP BY status;"

# Recent telemetry
echo ""
echo "Telemetry (last 24h):"
sqlite3 "$DB" "SELECT node_id, COUNT(*), MAX(received_at) 
FROM telemetry 
WHERE received_at > strftime('%s', 'now', '-1 day')
GROUP BY node_id;"

# Uptime summary
echo ""
echo "Uptime records (last 7 days):"
sqlite3 "$DB" "SELECT node_id, COUNT(*), MIN(date), MAX(date) 
FROM uptime 
WHERE date > date('now', '-7 days')
GROUP BY node_id
ORDER BY node_id;"

# Incidents
echo ""
echo "Incidents:"
sqlite3 "$DB" "SELECT status, COUNT(*) FROM incidents GROUP BY status;"

echo ""
echo "=== Size ==="
ls -lh "$DB"

echo ""
echo "=== OK ==="