#!/bin/bash
# Test Database Connection and Queries

echo -e "\n========================================"
echo -e "   DATABASE CONNECTION TEST"
echo -e "========================================\n"

# Check if .env exists
if [ ! -f "orchestrator/.env" ]; then
    echo -e "\033[0;31m❌ orchestrator/.env not found\033[0m"
    exit 1
fi

# Get DATABASE_URL
DATABASE_URL=$(grep "^DATABASE_URL=" orchestrator/.env | cut -d '=' -f2- | tr -d '"' | tr -d "'")

if [ -z "$DATABASE_URL" ]; then
    echo -e "\033[0;31m❌ DATABASE_URL not set\033[0m"
    exit 1
fi

# Extract connection details
if [[ $DATABASE_URL =~ postgresql://([^:]+):([^@]+)@([^:]+):([^/]+)/(.+) ]]; then
    DB_USER="${BASH_REMATCH[1]}"
    DB_PASS="${BASH_REMATCH[2]}"
    DB_HOST="${BASH_REMATCH[3]}"
    DB_PORT="${BASH_REMATCH[4]}"
    DB_NAME="${BASH_REMATCH[5]}"
else
    echo -e "\033[0;31m❌ Could not parse DATABASE_URL\033[0m"
    exit 1
fi

# Check if psql is available
if ! command -v psql &> /dev/null; then
    echo -e "\033[0;33m⚠️  psql not found. Install PostgreSQL client to run tests.\033[0m"
    echo -e "   Ubuntu: sudo apt install postgresql-client"
    exit 1
fi

echo -e "Testing connection to: $DB_HOST:$DB_PORT/$DB_NAME\n"

# Test 1: Basic connection
echo -e "1. Testing basic connection..."
if PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT version();" > /dev/null 2>&1; then
    echo -e "   \033[0;32m✅ Connection successful\033[0m"
else
    echo -e "   \033[0;31m❌ Connection failed\033[0m"
    exit 1
fi

# Test 2: Check tables exist
echo -e "\n2. Checking database tables..."
TABLES=$(PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" 2>/dev/null | tr -d ' ')

if [ -n "$TABLES" ] && [ "$TABLES" -gt 0 ]; then
    echo -e "   \033[0;32m✅ Found $TABLES table(s)\033[0m"
    
    # List tables
    echo -e "\n   Tables:"
    PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;" 2>/dev/null | grep -v "^$" | grep -v "table_name" | grep -v "---" | sed 's/^/     - /'
else
    echo -e "   \033[0;33m⚠️  No tables found. Run migrations first.\033[0m"
fi

# Test 3: Test queries on each table
if [ -n "$TABLES" ] && [ "$TABLES" -gt 0 ]; then
    echo -e "\n3. Testing queries on tables..."
    
    TABLES_LIST=$(PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';" 2>/dev/null | tr -d ' ')
    
    for table in $TABLES_LIST; do
        if [ -n "$table" ]; then
            COUNT=$(PGPASSWORD="$DB_PASS" psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -t -c "SELECT COUNT(*) FROM $table;" 2>/dev/null | tr -d ' ')
            if [ $? -eq 0 ]; then
                echo -e "   \033[0;32m✅ $table: $COUNT row(s)\033[0m"
            else
                echo -e "   \033[0;33m⚠️  $table: Query failed\033[0m"
            fi
        fi
    done
fi

# Test 4: Test health endpoint (if orchestrator is running)
echo -e "\n4. Testing orchestrator health endpoint..."
if curl -s http://localhost:8080/health > /dev/null 2>&1; then
    HEALTH=$(curl -s http://localhost:8080/health)
    if echo "$HEALTH" | grep -q "healthy\|status"; then
        echo -e "   \033[0;32m✅ Health endpoint responding\033[0m"
        if command -v jq &> /dev/null; then
            DB_STATUS=$(echo "$HEALTH" | jq -r '.checks.database // "unknown"' 2>/dev/null)
            echo -e "   Database status: $DB_STATUS"
        fi
    else
        echo -e "   \033[0;33m⚠️  Health endpoint returned unexpected response\033[0m"
    fi
else
    echo -e "   \033[0;33m⚠️  Orchestrator not running or not accessible\033[0m"
fi

echo -e "\n\033[0;32m✅ Database tests completed\033[0m"
echo ""