#!/bin/bash
# Automated database setup - tries multiple methods

set -e

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
API_DIR="$(cd "$SCRIPT_DIR/.." && pwd)"

cd "$API_DIR"

echo "=========================================="
echo "Automated Database Setup"
echo "=========================================="
echo ""

# Ensure .env exists with correct password
if [ ! -f .env ]; then
  cat > .env << 'ENVEOF'
DB_HOST=localhost
DB_PORT=5432
DB_NAME=sankofa
DB_USER=postgres
DB_PASSWORD=dev_sankofa_2024_secure
NODE_ENV=development
PORT=4000
ENVEOF
  echo "✅ Created .env file"
else
  # Update password in .env
  if ! grep -q "^DB_PASSWORD=dev_sankofa_2024_secure" .env; then
    sed -i 's|^DB_PASSWORD=.*|DB_PASSWORD=dev_sankofa_2024_secure|' .env || echo "DB_PASSWORD=dev_sankofa_2024_secure" >> .env
    echo "✅ Updated .env with password"
  fi
  # Ensure NODE_ENV is development
  if ! grep -q "^NODE_ENV=development" .env; then
    sed -i 's|^NODE_ENV=.*|NODE_ENV=development|' .env || echo "NODE_ENV=development" >> .env
  fi
fi

echo ""
echo "Attempting to set up database..."
echo ""

# Method 1: Try with sudo (may require password)
echo "Method 1: Trying with sudo..."
if sudo -n true 2>/dev/null; then
  echo "Sudo access available (no password required)"
  sudo -u postgres psql -c "CREATE DATABASE sankofa;" 2>/dev/null && echo "✅ Database created" || echo "Database may already exist"
  sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'dev_sankofa_2024_secure';" 2>/dev/null && echo "✅ Password set" || echo "⚠ Could not set password (may already be set)"
else
  echo "⚠ Sudo requires password - will try other methods"
fi

# Method 2: Try direct connection
echo ""
echo "Method 2: Trying direct PostgreSQL connection..."
if psql -U postgres -d postgres -c "SELECT 1;" >/dev/null 2>&1; then
  echo "✅ Can connect to PostgreSQL"
  psql -U postgres -d postgres -c "CREATE DATABASE sankofa;" 2>/dev/null && echo "✅ Database created" || echo "Database may already exist"
  psql -U postgres -d postgres -c "ALTER USER postgres PASSWORD 'dev_sankofa_2024_secure';" 2>/dev/null && echo "✅ Password set" || echo "⚠ Could not set password"
else
  echo "⚠ Cannot connect directly"
fi

# Method 3: Try with createdb
echo ""
echo "Method 3: Trying createdb command..."
if command -v createdb >/dev/null 2>&1; then
  createdb -U postgres sankofa 2>/dev/null && echo "✅ Database created" || echo "Database may already exist"
else
  echo "⚠ createdb command not found"
fi

# Final test
echo ""
echo "Testing database connection..."
sleep 1

if PGPASSWORD="dev_sankofa_2024_secure" psql -h localhost -U postgres -d sankofa -c "SELECT 1;" >/dev/null 2>&1; then
  echo "✅ Database connection successful!"
  echo ""
  echo "Database is ready. You can now run:"
  echo "  ./RUN_ME.sh"
  echo ""
  exit 0
else
  echo "❌ Database connection failed"
  echo ""
  echo "Please run manually:"
  echo ""
  echo "  sudo -u postgres psql << EOSQL"
  echo "  CREATE DATABASE sankofa;"
  echo "  ALTER USER postgres PASSWORD 'dev_sankofa_2024_secure';"
  echo "  \\q"
  echo "  EOSQL"
  echo ""
  echo "Or see: setup-db-commands.txt"
  echo ""
  exit 1
fi