#!/bin/bash
# ///
# table_stats.sh
# 描述：查看数据库表统计信息
# 作者：AI Generated
# 创建日期：2026-04-06
#
# 使用方式：
#   ./table_stats.sh
# ///

DB_HOST=${DB_HOST:-localhost}
DB_PORT=${DB_PORT:-5432}
DB_NAME=${DB_NAME:-wxauto}
DB_USER=${DB_USER:-wxauto}
DB_PASS=${DB_PASS:-wxauto_password}

export PGPASSWORD="$DB_PASS"

echo "=== 数据库表统计 ==="
echo ""

echo "--- nodes 表 ---"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT COUNT(*) as total_nodes FROM nodes;"

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT enabled, COUNT(*) FROM nodes GROUP BY enabled;"

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT status, COUNT(*) FROM nodes GROUP BY status;"

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT wechat_status, COUNT(*) FROM nodes GROUP BY wechat_status;"

echo ""
echo "--- webhook_urls 表 ---"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT COUNT(*) as total_webhooks FROM webhook_urls;"

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT enabled, COUNT(*) FROM webhook_urls GROUP BY enabled;"

echo ""
echo "--- logs 表 ---"
psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT COUNT(*) as total_logs FROM logs;"

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT level, COUNT(*) FROM logs GROUP BY level ORDER BY count DESC;"

psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c \
    "SELECT to_char(timestamp, 'YYYY-MM-DD') as date, COUNT(*) \
     FROM logs GROUP BY date ORDER BY date DESC LIMIT 7;"