#!/bin/bash
# CodiMD 資料庫查詢工具

REMOTE_HOST="ubuntu@192.168.42.120"
DB_POD_NAME="codimd-db-0"
DB_NAMESPACE="default"
DB_NAME="codimd"
DB_USER="codimd"

# 取得實際運行的 pod 名稱
get_db_pod() {
    ssh "$REMOTE_HOST" "sudo kubectl get pod -n $DB_NAMESPACE -l app=codimd-db -o jsonpath='{.items[0].metadata.name}'"
}

# 檢查是否已透過 SSH 建立 port-forward
check_ssh_pf() {
    pgrep -f "port-forward.*codimd-db" > /dev/null
}

SCRIPT_NAME=$(basename "$0")

# 如果沒有參數，顯示說明
if [ -z "$1" ]; then
    echo "=== CodiMD 資料庫查詢工具 ==="
    echo ""
    echo "用法: $SCRIPT_NAME <command> [args]"
    echo ""
    echo "指令:"
    echo "  list-users              列出所有使用者"
    echo "  user-notes <user-id>    查看特定使用者的筆記"
    echo "  note-count <user-id>    統計使用者筆記數量"
    echo "  db-query <sql>          執行自訂 SQL 查詢"
    echo ""
    echo "範例:"
    echo "  $SCRIPT_NAME list-users"
    echo "  $SCRIPT_NAME user-notes 33eabf61-c2ad-410e-99e0-19e11d86beec"
    echo "  $SCRIPT_NAME note-count 33eabf61-c2ad-410e-99e0-19e11d86beec"
    echo "  $SCRIPT_NAME db-query 'SELECT COUNT(*) FROM \"Notes\";'"
    exit 0
fi

DB_POD=$(get_db_pod)

case "$1" in
    list-users)
        if check_ssh_pf; then
            # 使用 port-forward 和 cli-anything-codimd
            if command -v cli-anything-codimd &> /dev/null; then
                cli-anything-codimd admin list-users \
                    --db-type postgresql \
                    --db-host localhost \
                    --db-name "$DB_NAME" \
                    --db-user "$DB_USER" \
                    --db-password 8xG94tL0uKlGiBEd
            else
                echo "⚠️  cli-anything-codimd 未安裝"
            fi
        else
            echo "⚠️  Port-forward 未運行，透過 SSH 直接查詢"
        fi
        # 總是顯示直接查詢結果
        ssh "$REMOTE_HOST" "sudo kubectl exec $DB_POD -- psql -U $DB_USER -d $DB_NAME -c 'SELECT id, email, profile, \"createdAt\" FROM \"Users\";'"
        ;;
    user-notes)
        if [ -z "$2" ]; then
            echo "用法: $0 user-notes <user-id>"
            exit 1
        fi
        echo "=== 使用者筆記列表 ==="
        ssh "$REMOTE_HOST" "sudo kubectl exec $DB_POD -- psql -U $DB_USER -d $DB_NAME -c \"SELECT id, alias, title, \\\"lastchangeAt\\\", permission FROM \\\"Notes\\\" WHERE \\\"ownerId\\\" = '$2' ORDER BY \\\"lastchangeAt\\\" DESC;\""
        ;;
    note-count)
        if [ -z "$2" ]; then
            echo "用法: $0 note-count <user-id>"
            exit 1
        fi
        ssh "$REMOTE_HOST" "sudo kubectl exec $DB_POD -- psql -U $DB_USER -d $DB_NAME -t -c \"SELECT COUNT(*) FROM \\\"Notes\\\" WHERE \\\"ownerId\\\" = '$2';\""
        ;;
    db-query)
        shift
        SQL="$*"
        if [ -z "$SQL" ]; then
            echo "用法: $0 db-query <sql>"
            exit 1
        fi
        ssh "$REMOTE_HOST" "sudo kubectl exec $DB_POD -- psql -U $DB_USER -d $DB_NAME -c '$SQL'"
        ;;
    *)
        echo "❌ 未知指令: $1"
        echo ""
        echo "可用指令: list-users, user-notes, note-count, db-query"
        exit 1
        ;;
esac