#!/bin/bash
# spend-monitor-collect.sh — Collect LLM spend data for daily monitor

echo "=== LLM Spend Report — $(date +'%b %d %Y %H:%M') ==="

# 1. LiteLLM key spend (cumulative by key)
echo ""
echo "--- LiteLLM Key Spend (cumulative) ---"
ssh -i /root/.ssh/itpp-infra -o ConnectTimeout=5 -o BatchMode=yes root@178.156.167.181 bash << 'SSHEOF'
  PGPASSWORD=litellm docker exec -e PGPASSWORD=litellm litellm_postgres psql -U litellm -d litellm_db -t -c \
    "SELECT key_name, ROUND(spend::numeric,2) as total FROM \"LiteLLM_VerificationToken\" WHERE spend > 0 ORDER BY spend DESC;"
SSHEOF

# 2. Last 24h total spend
echo ""
echo "--- Last 24h Total Spend ---"
ssh -i /root/.ssh/itpp-infra -o ConnectTimeout=5 -o BatchMode=yes root@178.156.167.181 bash << 'SSHEOF'
  PGPASSWORD=litellm docker exec -e PGPASSWORD=litellm litellm_postgres psql -U litellm -d litellm_db -t -c \
    "SELECT CAST(SUM(spend) AS numeric(10,2)) as last_24h FROM \"LiteLLM_SpendLogs\" WHERE \"startTime\" > NOW() - INTERVAL '24 hours';"
SSHEOF

# 3. DeepSeek models last 24h
echo ""
echo "--- DeepSeek Models Last 24h ---"
ssh -i /root/.ssh/itpp-infra -o ConnectTimeout=5 -o BatchMode=yes root@178.156.167.181 bash << 'SSHEOF'
  PGPASSWORD=litellm docker exec -e PGPASSWORD=litellm litellm_postgres psql -U litellm -d litellm_db -t -c \
    "SELECT model, ROUND(SUM(spend)::numeric,4) as cost FROM \"LiteLLM_SpendLogs\" WHERE \"startTime\" > NOW() - INTERVAL '24 hours' AND model LIKE '%deepseek%' GROUP BY model ORDER BY cost DESC;"
SSHEOF

# 4. Top models last 24h
echo ""
echo "--- All Models Last 24h (top 10) ---"
ssh -i /root/.ssh/itpp-infra -o ConnectTimeout=5 -o BatchMode=yes root@178.156.167.181 bash << 'SSHEOF'
  PGPASSWORD=litellm docker exec -e PGPASSWORD=litellm litellm_postgres psql -U litellm -d litellm_db -t -c \
    "SELECT model, ROUND(SUM(spend)::numeric,4) as cost FROM \"LiteLLM_SpendLogs\" WHERE \"startTime\" > NOW() - INTERVAL '24 hours' GROUP BY model ORDER BY cost DESC LIMIT 10;"
SSHEOF