Set up development database environments with environment variables

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m'

# Default values
DB_PASSWORD=""
ROOT_PASSWORD=""
DB_USER="devuser"
DB_NAME="devdb"
INSTALL_POSTGRES=true
INSTALL_MYSQL=false

usage() {
    echo "Usage: $0 [OPTIONS]"
    echo "Options:"
    echo "  --db-password PASSWORD    Database user password (required)"
    echo "  --root-password PASSWORD  Root/admin password (required for MySQL)"
    echo "  --db-user USER           Database username (default: devuser)"
    echo "  --db-name NAME           Database name (default: devdb)"
    echo "  --postgres               Install PostgreSQL (default)"
    echo "  --mysql                  Install MySQL/MariaDB instead"
    echo "  --help                   Show this help"
    exit 1
}

log_info() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

log_warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

cleanup() {
    log_error "Script failed. Check logs above for details."
    exit 1
}

trap cleanup ERR

# Parse arguments
while [[ $# -gt 0 ]]; do
    case $1 in
        --db-password)
            DB_PASSWORD="$2"
            shift 2
            ;;
        --root-password)
            ROOT_PASSWORD="$2"
            shift 2
            ;;
        --db-user)
            DB_USER="$2"
            shift 2
            ;;
        --db-name)
            DB_NAME="$2"
            shift 2
            ;;
        --postgres)
            INSTALL_POSTGRES=true
            INSTALL_MYSQL=false
            shift
            ;;
        --mysql)
            INSTALL_POSTGRES=false
            INSTALL_MYSQL=true
            shift
            ;;
        --help)
            usage
            ;;
        *)
            echo "Unknown option: $1"
            usage
            ;;
    esac
done

# Validate required parameters
if [[ -z "$DB_PASSWORD" ]]; then
    log_error "Database password is required (--db-password)"
    usage
fi

if [[ "$INSTALL_MYSQL" == true && -z "$ROOT_PASSWORD" ]]; then
    log_error "Root password is required for MySQL (--root-password)"
    usage
fi

echo "[1/8] Checking prerequisites..."

# Check if running as root or with sudo
if [[ $EUID -eq 0 ]]; then
    SUDO=""
else
    if ! command -v sudo &> /dev/null; then
        log_error "This script requires sudo privileges"
        exit 1
    fi
    SUDO="sudo"
fi

# Detect distribution and package manager
echo "[2/8] Detecting distribution..."

if [ -f /etc/os-release ]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian)
            PKG_MGR="apt"
            PKG_INSTALL="apt install -y"
            PKG_UPDATE="apt update"
            PG_VERSION=$(apt-cache search postgresql | grep "^postgresql-[0-9]" | head -1 | cut -d' ' -f1 | cut -d'-' -f2)
            PG_CONFIG_DIR="/etc/postgresql/${PG_VERSION:-*/main"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            PG_CONFIG_DIR="/var/lib/pgsql/data"
            ;;
        fedora)
            PKG_MGR="dnf"
            PKG_INSTALL="dnf install -y"
            PKG_UPDATE="dnf update -y"
            PG_CONFIG_DIR="/var/lib/pgsql/data"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_INSTALL="yum install -y"
            PKG_UPDATE="yum update -y"
            PG_CONFIG_DIR="/var/lib/pgsql/data"
            ;;
        *)
            log_error "Unsupported distribution: $ID"
            exit 1
            ;;
    esac
    log_info "Detected $PRETTY_NAME"
else
    log_error "Cannot detect distribution"
    exit 1
fi

echo "[3/8] Creating environment configuration directory..."

# Create configuration directory
CONFIG_DIR="$HOME/dev-config/database-envs"
mkdir -p "$CONFIG_DIR"
cd "$CONFIG_DIR"

# Set up .gitignore
if [[ ! -f "$HOME/.gitignore" ]] || ! grep -q ".env" "$HOME/.gitignore" 2>/dev/null; then
    echo ".env*" >> "$HOME/.gitignore"
fi

echo "[4/8] Updating package manager..."
$SUDO $PKG_UPDATE

if [[ "$INSTALL_POSTGRES" == true ]]; then
    echo "[5/8] Installing PostgreSQL..."
    
    case "$ID" in
        ubuntu|debian)
            $SUDO $PKG_INSTALL postgresql postgresql-contrib postgresql-client
            ;;
        *)
            $SUDO $PKG_INSTALL postgresql postgresql-server postgresql-contrib
            # Initialize database for RHEL-based systems
            if [[ ! -f "$PG_CONFIG_DIR/postgresql.conf" ]]; then
                $SUDO postgresql-setup --initdb
            fi
            ;;
    esac

    echo "[6/8] Starting and enabling PostgreSQL..."
    $SUDO systemctl enable postgresql
    $SUDO systemctl start postgresql

    echo "[7/8] Creating PostgreSQL database and user..."
    
    # Create user and database
    $SUDO -u postgres psql -c "CREATE USER ${DB_USER} WITH PASSWORD '${DB_PASSWORD}';" 2>/dev/null || true
    $SUDO -u postgres createdb -O "$DB_USER" "$DB_NAME" 2>/dev/null || true

    # Configure authentication
    if [[ "$ID" == "ubuntu" || "$ID" == "debian" ]]; then
        PG_VERSION=$(ls /etc/postgresql/ | head -1)
        HBA_CONF="/etc/postgresql/${PG_VERSION}/main/pg_hba.conf"
    else
        HBA_CONF="$PG_CONFIG_DIR/pg_hba.conf"
    fi

    $SUDO cp "$HBA_CONF" "${HBA_CONF}.backup"
    
    # Add authentication rules if not already present
    if ! $SUDO grep -q "^local.*${DB_NAME}.*${DB_USER}" "$HBA_CONF"; then
        $SUDO tee -a "$HBA_CONF" > /dev/null << EOF
local   ${DB_NAME}      ${DB_USER}                              md5
host    ${DB_NAME}      ${DB_USER}      127.0.0.1/32            md5
host    ${DB_NAME}      ${DB_USER}      ::1/128                 md5
EOF
    fi

    $SUDO systemctl reload postgresql

    # Create environment file
    cat > .env.postgres << EOF
POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DB=${DB_NAME}
POSTGRES_USER=${DB_USER}
POSTGRES_PASSWORD=${DB_PASSWORD}
DATABASE_URL=postgresql://${DB_USER}:${DB_PASSWORD}@localhost:5432/${DB_NAME}
ENVIRONMENT=development
DEBUG=true
LOG_LEVEL=debug
EOF
    chmod 600 .env.postgres

elif [[ "$INSTALL_MYSQL" == true ]]; then
    echo "[5/8] Installing MariaDB..."
    $SUDO $PKG_INSTALL mariadb-server mariadb

    echo "[6/8] Starting and enabling MariaDB..."
    $SUDO systemctl enable mariadb
    $SUDO systemctl start mariadb

    echo "[7/8] Securing MariaDB and creating database..."
    
    # Set root password and create database/user
    $SUDO mysql -e "ALTER USER root@localhost IDENTIFIED BY '${ROOT_PASSWORD}';" 2>/dev/null || \
    $SUDO mysql -e "SET PASSWORD FOR root@localhost = PASSWORD('${ROOT_PASSWORD}');" 2>/dev/null || true
    
    mysql -u root -p"${ROOT_PASSWORD}" << EOF
CREATE DATABASE IF NOT EXISTS ${DB_NAME};
CREATE USER IF NOT EXISTS '${DB_USER}'@'localhost' IDENTIFIED BY '${DB_PASSWORD}';
GRANT ALL PRIVILEGES ON ${DB_NAME}.* TO '${DB_USER}'@'localhost';
FLUSH PRIVILEGES;
EOF

    # Create environment file
    cat > .env.mysql << EOF
MYSQL_HOST=localhost
MYSQL_PORT=3306
MYSQL_DATABASE=${DB_NAME}
MYSQL_USER=${DB_USER}
MYSQL_PASSWORD=${DB_PASSWORD}
MYSQL_ROOT_PASSWORD=${ROOT_PASSWORD}
DATABASE_URL=mysql://${DB_USER}:${DB_PASSWORD}@localhost:3306/${DB_NAME}
ENVIRONMENT=development
DEBUG=true
LOG_LEVEL=debug
EOF
    chmod 600 .env.mysql
fi

echo "[8/8] Running verification tests..."

if [[ "$INSTALL_POSTGRES" == true ]]; then
    # Test PostgreSQL connection
    set -a; source .env.postgres; set +a
    if psql "$DATABASE_URL" -c "SELECT version();" &>/dev/null; then
        log_info "PostgreSQL connection test: PASSED"
    else
        log_error "PostgreSQL connection test: FAILED"
        exit 1
    fi
    
    log_info "PostgreSQL environment file created: $CONFIG_DIR/.env.postgres"
    log_info "Load with: set -a; source $CONFIG_DIR/.env.postgres; set +a"

elif [[ "$INSTALL_MYSQL" == true ]]; then
    # Test MySQL connection
    if mysql -u"$DB_USER" -p"$DB_PASSWORD" -e "SELECT VERSION();" "$DB_NAME" &>/dev/null; then
        log_info "MariaDB connection test: PASSED"
    else
        log_error "MariaDB connection test: FAILED"
        exit 1
    fi
    
    log_info "MariaDB environment file created: $CONFIG_DIR/.env.mysql"
    log_info "Load with: set -a; source $CONFIG_DIR/.env.mysql; set +a"
fi

log_info "Database development environment setup completed successfully!"
log_info "Environment files are located in: $CONFIG_DIR"
log_warn "Remember: Never commit .env files to version control!"