82 lines
3 KiB
Bash
Executable file
82 lines
3 KiB
Bash
Executable file
#!/usr/bin/env bash
|
|
# =============================================================================
|
|
# Show Sync Stats - Display current data counts and sample records
|
|
# =============================================================================
|
|
|
|
set -euo pipefail
|
|
|
|
# Load environment variables
|
|
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
|
|
PROJECT_ROOT="$(dirname "$SCRIPT_DIR")"
|
|
|
|
if [[ -f "$PROJECT_ROOT/.env" ]]; then
|
|
source "$PROJECT_ROOT/.env"
|
|
fi
|
|
|
|
# Database connection from environment
|
|
DB_HOST="${DB_HOST:-localhost}"
|
|
DB_PORT="${DB_PORT:-25433}"
|
|
DB_USER="${DB_USER:-postgres}"
|
|
DB_PASSWORD="${DB_PASSWORD:-devpassword}"
|
|
DB_NAME="${DB_NAME:-conversation_assistant}"
|
|
|
|
echo "╔════════════════════════════════════════════════════════════════╗"
|
|
echo "║ SYNC STATS - conversation-assistant ║"
|
|
echo "╚════════════════════════════════════════════════════════════════╝"
|
|
echo ""
|
|
|
|
SQL="
|
|
-- Counts
|
|
SELECT 'Contacts' AS table_name, COUNT(*) AS count FROM contacts
|
|
UNION ALL SELECT 'Conversations', COUNT(*) FROM conversations
|
|
UNION ALL SELECT 'Messages', COUNT(*) FROM messages
|
|
UNION ALL SELECT 'Classification History', COUNT(*) FROM classification_history;
|
|
"
|
|
|
|
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL"
|
|
|
|
echo ""
|
|
echo "── Recent Conversations ──────────────────────────────────────────"
|
|
SQL_CONVOS="
|
|
SELECT
|
|
LEFT(id::text, 8) as id,
|
|
LEFT(display_name, 30) as display_name,
|
|
is_group,
|
|
message_count,
|
|
jsonb_array_length(participant_ids) as participants,
|
|
last_message_at::date as last_msg
|
|
FROM conversations
|
|
ORDER BY last_message_at DESC NULLS LAST
|
|
LIMIT 5;
|
|
"
|
|
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL_CONVOS"
|
|
|
|
echo ""
|
|
echo "── Contacts by Classification ────────────────────────────────────"
|
|
SQL_CLASS="
|
|
SELECT
|
|
classification,
|
|
COUNT(*) as count
|
|
FROM contacts
|
|
GROUP BY classification
|
|
ORDER BY count DESC;
|
|
"
|
|
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL_CLASS"
|
|
|
|
echo ""
|
|
echo "── Sample Participant IDs (checking UUID vs raw) ─────────────────"
|
|
SQL_PARTS="
|
|
SELECT
|
|
LEFT(id::text, 8) as id,
|
|
LEFT(display_name, 25) as display_name,
|
|
LEFT(participant_ids->>0, 36) as first_participant,
|
|
CASE
|
|
WHEN participant_ids->>0 ~ '^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$'
|
|
THEN 'UUID ✓'
|
|
ELSE 'RAW ✗'
|
|
END as id_type
|
|
FROM conversations
|
|
WHERE jsonb_array_length(participant_ids) > 0
|
|
LIMIT 5;
|
|
"
|
|
PGPASSWORD="$DB_PASSWORD" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "$SQL_PARTS"
|