dbis_core/scripts/check-database-status.sh

#!/bin/bash
# Check Database Status for AS4 Settlement
# Verifies database connectivity and readiness

set -e

echo "========================================="
echo "AS4 Settlement Database Status Check"
echo "========================================="
echo ""

cd "$(dirname "$0")/.."

# Load environment variables
if [ -f .env ]; then
    export $(grep -v '^#' .env | xargs)
else
    echo "⚠ Warning: .env file not found"
    echo ""
fi

# Check PostgreSQL client
echo "1. Checking PostgreSQL client..."
if command -v psql &> /dev/null; then
    PSQL_VERSION=$(psql --version | head -1)
    echo "   ✓ PostgreSQL client installed: $PSQL_VERSION"
else
    echo "   ✗ PostgreSQL client not found"
    exit 1
fi
echo ""

# Check DATABASE_URL
echo "2. Checking DATABASE_URL..."
if [ -z "$DATABASE_URL" ]; then
    echo "   ✗ DATABASE_URL not set"
    echo ""
    echo "   Please set DATABASE_URL in .env file"
    echo "   Example: DATABASE_URL=postgresql://user:password@host:port/database"
    exit 1
else
    # Mask password in output
    MASKED_URL=$(echo "$DATABASE_URL" | sed 's/:\/\/[^:]*:[^@]*@/:\/\/***:***@/')
    echo "   ✓ DATABASE_URL is set: $MASKED_URL"
fi
echo ""

# Test connection
echo "3. Testing database connection..."
if timeout 5 psql "$DATABASE_URL" -c "SELECT version();" &> /dev/null; then
    PG_VERSION=$(timeout 5 psql "$DATABASE_URL" -c "SELECT version();" -t -A 2>/dev/null | head -1)
    echo "   ✓ Database connection successful"
    echo "   PostgreSQL version: $PG_VERSION"
else
    echo "   ✗ Database connection failed"
    echo ""
    echo "   Possible issues:"
    echo "   - Database server not running"
    echo "   - Network connectivity issues"
    echo "   - Incorrect credentials"
    echo "   - Database does not exist"
    exit 1
fi
echo ""

# Check database exists
echo "4. Checking database schema..."
if timeout 5 psql "$DATABASE_URL" -c "SELECT schema_name FROM information_schema.schemata WHERE schema_name = 'public';" &> /dev/null; then
    echo "   ✓ Public schema exists"
else
    echo "   ✗ Public schema not found"
    exit 1
fi
echo ""

# Check Prisma migrations table
echo "5. Checking Prisma migrations..."
if timeout 5 psql "$DATABASE_URL" -c "SELECT COUNT(*) FROM _prisma_migrations;" &> /dev/null 2>&1; then
    MIGRATION_COUNT=$(timeout 5 psql "$DATABASE_URL" -c "SELECT COUNT(*) FROM _prisma_migrations;" -t -A 2>/dev/null | tr -d ' ')
    echo "   ✓ Prisma migrations table exists"
    echo "   Migration count: $MIGRATION_COUNT"
    
    # Show last 5 migrations
    echo ""
    echo "   Recent migrations:"
    timeout 5 psql "$DATABASE_URL" -c "SELECT migration_name, finished_at FROM _prisma_migrations ORDER BY finished_at DESC LIMIT 5;" -t -A 2>/dev/null | while read line; do
        if [ -n "$line" ]; then
            echo "     - $line"
        fi
    done
else
    echo "   ⚠ Prisma migrations table not found (database may be new)"
fi
echo ""

# Check for AS4 tables
echo "6. Checking AS4 tables..."
AS4_TABLES=$(timeout 5 psql "$DATABASE_URL" -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'as4_%' ORDER BY table_name;" -t -A 2>/dev/null | grep -v '^$' | wc -l | tr -d ' ')
if [ "$AS4_TABLES" -gt 0 ]; then
    echo "   ✓ Found $AS4_TABLES AS4 table(s)"
    echo ""
    echo "   AS4 tables:"
    timeout 5 psql "$DATABASE_URL" -c "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'as4_%' ORDER BY table_name;" -t -A 2>/dev/null | while read line; do
        if [ -n "$line" ]; then
            echo "     - $line"
        fi
    done
else
    echo "   ⚠ No AS4 tables found (migration not yet applied)"
fi
echo ""

# Check Prisma client
echo "7. Checking Prisma client..."
if [ -f "node_modules/.prisma/client/index.js" ]; then
    echo "   ✓ Prisma client generated"
else
    echo "   ⚠ Prisma client not generated - run: npx prisma generate"
fi
echo ""

# Summary
echo "========================================="
echo "Database Status Summary"
echo "========================================="
echo "✓ Database connection: OK"
echo "✓ PostgreSQL version: $PG_VERSION"

if [ "$AS4_TABLES" -gt 0 ]; then
    echo "✓ AS4 tables: Found ($AS4_TABLES tables)"
    echo ""
    echo "Status: ✅ Database is ready and AS4 tables exist"
else
    echo "⚠ AS4 tables: Not found"
    echo ""
    echo "Status: ✅ Database is ready (migration needed)"
    echo ""
    echo "Next step: Run migration"
    echo "  npx prisma migrate deploy"
fi
echo ""