#!/bin/bash

echo "=== RedFlag Database Investigation ==="
echo

# Check if containers are running
echo "1. Checking container status..."
docker ps | grep -E "redflag|postgres"

echo
echo "2. Testing database connection with different credentials..."

# Try with postgres credentials
echo "Trying with postgres user:"
docker exec redflag-postgres psql -U postgres -c "SELECT current_database(), current_user;" 2>/dev/null

# Try with redflag credentials
echo "Trying with redflag user:"
docker exec redflag-postgres psql -U redflag -d redflag -c "SELECT current_database(), current_user;" 2>/dev/null

echo
echo "3. Listing databases:"
docker exec redflag-postgres psql -U postgres -c "\l" 2>/dev/null

echo
echo "4. Checking tables in redflag database:"
docker exec redflag-postgres psql -U postgres -d redflag -c "\dt" 2>/dev/null || echo "Failed to list tables"

echo
echo "5. Checking migration status:"
docker exec redflag-postgres psql -U postgres -d redflag -c "SELECT version, applied_at FROM schema_migrations ORDER BY version;" 2>/dev/null || echo "No schema_migrations table found"

echo
echo "6. Checking users table:"
docker exec redflag-postgres psql -U postgres -d redflag -c "SELECT id, username, email, created_at FROM users LIMIT 5;" 2>/dev/null || echo "Users table not found"

echo
echo "7. Checking for security_* tables:"
docker exec redflag-postgres psql -U postgres -d redflag -c "\dt security_*" 2>/dev/null || echo "No security_* tables found"

echo
echo "8. Checking agent_commands table for signature column:"
docker exec redflag-postgres psql -U postgres -d redflag -c "\d agent_commands" 2>/dev/null | grep signature || echo "Signature column not found"

echo
echo "9. Checking recent logs from server:"
docker logs redflag-server 2>&1 | tail -20

echo
echo "10. Password configuration check:"
echo "From docker-compose.yml POSTGRES_PASSWORD:"
grep "POSTGRES_PASSWORD:" docker-compose.yml
echo "From config/.env POSTGRES_PASSWORD:"
grep "POSTGRES_PASSWORD:" config/.env
echo "From config/.env REDFLAG_DB_PASSWORD:"
grep "REDFLAG_DB_PASSWORD:" config/.env