test_tunnel.sh

#!/bin/bash
# Script test Cloudflare Tunnel URL và kiểm tra dữ liệu PostgreSQL
# Usage: ./test_tunnel.sh <host> <port> [user] [password] [database]

set -e

# Colors
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
RED='\033[0;31m'
BLUE='\033[0;34m'
NC='\033[0m'

log() {
    echo -e "${BLUE}[TEST]${NC} $1"
}

success() {
    echo -e "${GREEN}✅${NC} $1"
}

warn() {
    echo -e "${YELLOW}⚠️${NC} $1"
}

error() {
    echo -e "${RED}❌${NC} $1"
}

# Parse arguments
TUNNEL_HOST="${1:-}"
TUNNEL_PORT="${2:-5432}"
DB_USER="${3:-hue_remote}"
DB_PASSWORD="${4:-huepass123}"
DB_NAME="${5:-hue_portal}"

if [ -z "$TUNNEL_HOST" ]; then
    error "Thiếu tham số host!"
    echo ""
    echo "Usage: $0 <host> [port] [user] [password] [database]"
    echo ""
    echo "Ví dụ:"
    echo "  $0 nas.aliss.io.vn 5432"
    echo "  $0 xyz.trycloudflare.com 5432 hue_remote huepass123 hue_portal"
    exit 1
fi

echo -e "${BLUE}============================================================${NC}"
echo -e "${BLUE}Test Cloudflare Tunnel và kiểm tra dữ liệu PostgreSQL${NC}"
echo -e "${BLUE}============================================================${NC}"
echo ""
log "Thông tin kết nối:"
echo "  Host: $TUNNEL_HOST"
echo "  Port: $TUNNEL_PORT"
echo "  User: $DB_USER"
echo "  Database: $DB_NAME"
echo ""

# Test connection
log "Đang test kết nối..."
export PGPASSWORD="$DB_PASSWORD"

if psql -h "$TUNNEL_HOST" -p "$TUNNEL_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT 1;" &>/dev/null; then
    success "Kết nối thành công!"
    echo ""
    
    # Get PostgreSQL version
    VERSION=$(psql -h "$TUNNEL_HOST" -p "$TUNNEL_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT version();" 2>/dev/null | head -1 | xargs)
    log "PostgreSQL version: ${VERSION:0:50}..."
    
    # Count tables
    TABLE_COUNT=$(psql -h "$TUNNEL_HOST" -p "$TUNNEL_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" 2>/dev/null | xargs)
    success "Số lượng tables: $TABLE_COUNT"
    echo ""
    
    # Check data
    echo -e "${BLUE}📊 Kiểm tra dữ liệu:${NC}"
    echo "============================================================"
    
    TOTAL_DOCS=0
    
    for table in core_legaldocument core_fine core_procedure core_office core_advisory; do
        COUNT=$(psql -h "$TUNNEL_HOST" -p "$TUNNEL_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM $table;" 2>/dev/null | xargs || echo "0")
        if [ "$COUNT" != "0" ] && [ -n "$COUNT" ]; then
            TOTAL_DOCS=$((TOTAL_DOCS + COUNT))
            success "$table: $(printf "%'d" $COUNT) records"
        else
            warn "$table: 0 records (trống)"
        fi
    done
    
    echo "============================================================"
    success "Tổng số documents: $(printf "%'d" $TOTAL_DOCS)"
    echo ""
    
    # Show sample records
    if [ "$TOTAL_DOCS" -gt 0 ]; then
        echo -e "${BLUE}📄 Sample records (5 mới nhất):${NC}"
        psql -h "$TUNNEL_HOST" -p "$TUNNEL_PORT" -U "$DB_USER" -d "$DB_NAME" -c "
            SELECT id, LEFT(title, 50) as title, created_at 
            FROM core_legaldocument 
            ORDER BY created_at DESC 
            LIMIT 5;
        " 2>/dev/null || warn "Không thể lấy sample records"
        echo ""
    fi
    
    # Show DATABASE_URL
    echo -e "${BLUE}============================================================${NC}"
    success "KẾT NỐI THÀNH CÔNG!"
    if [ "$TOTAL_DOCS" -gt 0 ]; then
        success "Có $(printf "%'d" $TOTAL_DOCS) documents trong database"
    else
        warn "Database trống, chưa có dữ liệu"
    fi
    echo ""
    echo -e "${BLUE}📝 DATABASE_URL để dùng:${NC}"
    echo "postgres://$DB_USER:$DB_PASSWORD@$TUNNEL_HOST:$TUNNEL_PORT/$DB_NAME"
    echo -e "${BLUE}============================================================${NC}"
    
else
    error "KHÔNG KẾT NỐI ĐƯỢC!"
    echo ""
    echo -e "${BLUE}💡 Kiểm tra:${NC}"
    echo "  1. Cloudflare Tunnel URL có đúng không?"
    echo "  2. Tunnel có đang chạy trên server không?"
    echo "  3. PostgreSQL có đang chạy không?"
    echo "  4. Firewall có block port không?"
    echo ""
    echo "Thử test với:"
    echo "  psql -h $TUNNEL_HOST -p $TUNNEL_PORT -U $DB_USER -d $DB_NAME"
    exit 1
fi