#!/bin/bash
# Manual database setup script - run this with appropriate permissions

echo "=========================================="
echo "Manual Database Setup for Sovereign Stack"
echo "=========================================="
echo ""
echo "This script will help you set up the database."
echo "You may need to run some commands with sudo."
echo ""

# Check if database exists
echo "Step 1: Checking if database 'sankofa' exists..."
if psql -U postgres -lqt 2>/dev/null | cut -d \| -f 1 | grep -qw sankofa; then
  echo "✅ Database 'sankofa' already exists"
else
  echo "Database 'sankofa' not found."
  echo ""
  echo "Please run ONE of the following commands:"
  echo ""
  echo "Option A (if you have sudo access):"
  echo "  sudo -u postgres createdb sankofa"
  echo ""
  echo "Option B (if you have postgres user access):"
  echo "  createdb -U postgres sankofa"
  echo ""
  echo "Option C (if you have a different PostgreSQL user):"
  echo "  createdb -U your_user sankofa"
  echo ""
  read -p "Press Enter after you've created the database..."
fi

# Check current password
echo ""
echo "Step 2: Testing database connection..."
echo "Current .env password: $(grep '^DB_PASSWORD=' .env 2>/dev/null | cut -d'=' -f2- | tr -d '"' | tr -d "'" || echo 'not set')"
echo ""

# Try to connect
DB_PASS=$(grep "^DB_PASSWORD=" .env 2>/dev/null | cut -d'=' -f2- | tr -d '"' | tr -d "'" | xargs)

if [ -n "$DB_PASS" ] && [ "$DB_PASS" != "your_secure_password_here" ]; then
  if PGPASSWORD="$DB_PASS" psql -h localhost -U postgres -d sankofa -c "SELECT 1;" >/dev/null 2>&1; then
    echo "✅ Database connection successful with current password"
    CONNECTION_OK=true
  else
    echo "❌ Database connection failed with current password"
    CONNECTION_OK=false
  fi
else
  echo "⚠ No valid password set in .env"
  CONNECTION_OK=false
fi

if [ "$CONNECTION_OK" != "true" ]; then
  echo ""
  echo "You need to set the correct PostgreSQL password."
  echo ""
  echo "Option 1: Set password for postgres user"
  echo "  Run: sudo -u postgres psql"
  echo "  Then: ALTER USER postgres PASSWORD 'your_password';"
  echo "  Then: \\q"
  echo ""
  echo "Option 2: Update .env with existing password"
  echo "  Edit .env and set DB_PASSWORD to your actual PostgreSQL password"
  echo ""
  read -p "Press Enter when password is configured..."
  
  # Update .env if user wants
  echo ""
  read -p "Would you like to update .env with a new password now? (y/N) " -n 1 -r
  echo
  if [[ $REPLY =~ ^[Yy]$ ]]; then
    read -sp "Enter PostgreSQL password: " NEW_PASS
    echo ""
    if [ -n "$NEW_PASS" ]; then
      sed -i "s|^DB_PASSWORD=.*|DB_PASSWORD=$NEW_PASS|" .env
      echo "✅ .env updated"
    fi
  fi
fi

# Final connection test
echo ""
echo "Step 3: Final connection test..."
DB_PASS=$(grep "^DB_PASSWORD=" .env | cut -d'=' -f2- | tr -d '"' | tr -d "'" | xargs)

if PGPASSWORD="$DB_PASS" psql -h localhost -U postgres -d sankofa -c "SELECT 1;" >/dev/null 2>&1; then
  echo "✅ Database connection successful!"
  echo ""
  echo "You can now run:"
  echo "  ./scripts/setup-sovereign-stack.sh"
  echo ""
  echo "Or continue with migrations:"
  echo "  pnpm db:migrate:up"
  echo "  pnpm db:seed:sovereign-stack"
  echo "  pnpm verify:sovereign-stack"
else
  echo "❌ Database connection still failing"
  echo ""
  echo "Please:"
  echo "  1. Verify PostgreSQL is running: sudo systemctl status postgresql"
  echo "  2. Verify database exists: psql -U postgres -l | grep sankofa"
  echo "  3. Verify password is correct in .env"
  echo "  4. Try connecting manually: psql -U postgres -d sankofa"
fi