ljwx-children/scripts/setup-postgres.sh

#!/bin/bash

# =============================================================================
# PostgreSQL 快速设置脚本
# =============================================================================
# 用于留守儿童心理风险检测平台的本地开发环境

set -e

echo "🐘 PostgreSQL 快速设置脚本"
echo "================================"
echo ""

# 检测操作系统
OS="$(uname -s)"
case "${OS}" in
    Linux*)     MACHINE=Linux;;
    Darwin*)    MACHINE=Mac;;
    *)          MACHINE="UNKNOWN:${OS}"
esac

echo "📌 检测到操作系统: ${MACHINE}"
echo ""

# 选择安装方式
echo "选择 PostgreSQL 安装方式:"
echo "  1) Docker (推荐) - 快速、隔离、易于清理"
echo "  2) 本地安装 (Homebrew/apt) - 持久化、系统级"
echo "  3) 使用现有 PostgreSQL"
echo ""
read -p "请选择 (1/2/3): " choice

case $choice in
    1)
        echo ""
        echo "🐳 使用 Docker 安装 PostgreSQL..."
        echo ""

        # 检查 Docker 是否安装
        if ! command -v docker &> /dev/null; then
            echo "❌ Docker 未安装，请先安装 Docker:"
            echo "   macOS: https://www.docker.com/products/docker-desktop"
            echo "   Linux: sudo apt-get install docker.io"
            exit 1
        fi

        # 停止并删除旧容器（如果存在）
        echo "🔍 检查是否存在旧容器..."
        docker stop ljwx-postgres 2>/dev/null || true
        docker rm ljwx-postgres 2>/dev/null || true

        # 启动 PostgreSQL 容器
        echo "🚀 启动 PostgreSQL 15 容器..."
        docker run -d \
            --name ljwx-postgres \
            -p 5432:5432 \
            -e POSTGRES_PASSWORD=postgres \
            -e POSTGRES_DB=ljwx_children \
            -v ljwx-postgres-data:/var/lib/postgresql/data \
            postgres:15

        echo "⏳ 等待 PostgreSQL 启动..."
        sleep 5

        # 创建测试数据库
        echo "📊 创建测试数据库..."
        docker exec ljwx-postgres psql -U postgres -c "CREATE DATABASE ljwx_children_test;" 2>/dev/null || echo "   测试数据库已存在"

        echo ""
        echo "✅ PostgreSQL 安装成功！"
        echo ""
        echo "📋 连接信息:"
        echo "   主机: localhost"
        echo "   端口: 5432"
        echo "   用户: postgres"
        echo "   密码: postgres"
        echo "   开发数据库: ljwx_children"
        echo "   测试数据库: ljwx_children_test"
        echo ""
        echo "🔗 连接字符串:"
        echo "   开发: postgresql+asyncpg://postgres:postgres@localhost:5432/ljwx_children"
        echo "   测试: postgresql://postgres:postgres@localhost:5432/ljwx_children_test"
        echo ""
        echo "🛠️  管理命令:"
        echo "   启动: docker start ljwx-postgres"
        echo "   停止: docker stop ljwx-postgres"
        echo "   日志: docker logs -f ljwx-postgres"
        echo "   进入: docker exec -it ljwx-postgres psql -U postgres -d ljwx_children"
        echo "   删除: docker stop ljwx-postgres && docker rm ljwx-postgres"
        ;;

    2)
        echo ""
        echo "💻 本地安装 PostgreSQL..."
        echo ""

        if [ "$MACHINE" = "Mac" ]; then
            # macOS 使用 Homebrew
            if ! command -v brew &> /dev/null; then
                echo "❌ Homebrew 未安装，请先安装: /bin/bash -c \"\$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)\""
                exit 1
            fi

            echo "📦 使用 Homebrew 安装 PostgreSQL 15..."
            brew install postgresql@15

            echo "🚀 启动 PostgreSQL 服务..."
            brew services start postgresql@15

            echo "⏳ 等待服务启动..."
            sleep 3

            # 创建数据库
            echo "📊 创建数据库..."
            createdb ljwx_children 2>/dev/null || echo "   开发数据库已存在"
            createdb ljwx_children_test 2>/dev/null || echo "   测试数据库已存在"

        elif [ "$MACHINE" = "Linux" ]; then
            # Linux 使用 apt
            echo "📦 使用 apt 安装 PostgreSQL..."
            sudo apt-get update
            sudo apt-get install -y postgresql postgresql-contrib

            echo "🚀 启动 PostgreSQL 服务..."
            sudo systemctl start postgresql
            sudo systemctl enable postgresql

            echo "📊 创建用户和数据库..."
            sudo -u postgres psql -c "CREATE USER postgres WITH PASSWORD 'postgres';" 2>/dev/null || echo "   用户已存在"
            sudo -u postgres psql -c "ALTER USER postgres WITH SUPERUSER;" 2>/dev/null || true
            sudo -u postgres createdb ljwx_children 2>/dev/null || echo "   开发数据库已存在"
            sudo -u postgres createdb ljwx_children_test 2>/dev/null || echo "   测试数据库已存在"
            sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE ljwx_children TO postgres;" 2>/dev/null || true
            sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE ljwx_children_test TO postgres;" 2>/dev/null || true
        fi

        echo ""
        echo "✅ PostgreSQL 安装成功！"
        echo ""
        echo "📋 连接信息:"
        echo "   主机: localhost"
        echo "   端口: 5432"
        echo "   用户: postgres"
        echo "   密码: postgres"
        echo "   开发数据库: ljwx_children"
        echo "   测试数据库: ljwx_children_test"
        ;;

    3)
        echo ""
        echo "🔌 使用现有 PostgreSQL..."
        echo ""
        read -p "请输入主机 (默认 localhost): " PG_HOST
        PG_HOST=${PG_HOST:-localhost}

        read -p "请输入端口 (默认 5432): " PG_PORT
        PG_PORT=${PG_PORT:-5432}

        read -p "请输入用户名 (默认 postgres): " PG_USER
        PG_USER=${PG_USER:-postgres}

        read -sp "请输入密码: " PG_PASS
        echo ""

        # 测试连接
        echo "🔍 测试数据库连接..."
        if PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -c "SELECT version();" &> /dev/null; then
            echo "✅ 连接成功！"

            # 创建数据库
            echo "📊 创建数据库..."
            PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -c "CREATE DATABASE ljwx_children;" 2>/dev/null || echo "   开发数据库已存在"
            PGPASSWORD=$PG_PASS psql -h $PG_HOST -p $PG_PORT -U $PG_USER -c "CREATE DATABASE ljwx_children_test;" 2>/dev/null || echo "   测试数据库已存在"

            echo ""
            echo "🔗 连接字符串:"
            echo "   开发: postgresql+asyncpg://$PG_USER:$PG_PASS@$PG_HOST:$PG_PORT/ljwx_children"
            echo "   测试: postgresql://$PG_USER:$PG_PASS@$PG_HOST:$PG_PORT/ljwx_children_test"
        else
            echo "❌ 连接失败，请检查配置"
            exit 1
        fi
        ;;

    *)
        echo "❌ 无效选择"
        exit 1
        ;;
esac

echo ""
echo "🎉 PostgreSQL 设置完成！"
echo ""
echo "📝 下一步:"
echo "   1. 复制环境变量配置: cp .env.example .env"
echo "   2. 编辑 .env 文件，确认 DATABASE_URL 正确"
echo "   3. 运行数据库迁移: alembic upgrade head"
echo "   4. 启动应用: python -m uvicorn backend.main:app --reload"
echo ""
echo "🧪 运行测试:"
echo "   source venv/bin/activate"
echo "   python -m pytest tests/unit/test_api_risk_assessments.py -v"
echo ""