#!/bin/bash
# LearningAnalytics 数据库修复脚本
# 修复 student_attempts 表的字段类型错误
# 1. ip_address: INET → VARCHAR(64)
# 2. partial_score: NUMERIC(5,4) → NUMERIC(6,2)
# 用法: bash fix_learning_analytics_db.sh

set -e  # 遇到错误立即退出

echo "=========================================="
echo "LearningAnalytics 数据库修复脚本"
echo "=========================================="
echo ""

# 数据库配置
DB_HOST="localhost"
DB_PORT="5433"
DB_NAME="learning_analytics"
DB_USER="rag_user"
DB_PASSWORD="your_password"

# 检查数据库容器是否存在
if docker ps -a | grep -q "learning_analytics_postgres"; then
    echo "✓ 检测到数据库容器: learning_analytics_postgres"
    DB_HOST="learning_analytics_postgres"
    DB_PORT="5432"
else
    echo "⚠ 未检测到专用数据库容器，将使用本地 PostgreSQL"
    echo "  请确保本地 PostgreSQL 服务正在运行"
fi

echo ""
echo "数据库配置:"
echo "  主机: $DB_HOST"
echo "  端口: $DB_PORT"
echo "  数据库: $DB_NAME"
echo "  用户: $DB_USER"
echo ""

# 执行 SQL 修复脚本
echo "正在执行数据库修复..."
echo "  修复字段："
echo "    1. ip_address: INET → VARCHAR(64)"
echo "    2. partial_score: NUMERIC(5,4) → NUMERIC(6,2)"
echo ""

# 方法1: 使用 docker exec (推荐)
if docker ps | grep -q "learning_analytics_postgres"; then
    docker exec learning_analytics_postgres psql -U rag_user -d learning_analytics <<'EOF'
        -- 1. 修改 ip_address 字段类型
        ALTER TABLE student_attempts
        ALTER COLUMN ip_address TYPE VARCHAR(64);

        -- 2. 修改 partial_score 字段类型
        ALTER TABLE student_attempts
        ALTER COLUMN partial_score TYPE NUMERIC(6,2) USING partial_score::NUMERIC(6,2);

        -- 3. 添加注释
        COMMENT ON COLUMN student_attempts.ip_address IS '客户端IP地址，字符串格式';
        COMMENT ON COLUMN student_attempts.partial_score IS '题目得分，保留2位小数，支持0-9999.99分';

        -- 4. 输出成功消息
        \echo ''
        \echo '✓ 修复完成!'
        \echo '  - ip_address: INET → VARCHAR(64)'
        \echo '  - partial_score: NUMERIC(5,4) → NUMERIC(6,2)'
        \echo ''
EOF

    echo "✓ 数据库修复完成！"

else
    echo "⚠ 未找到 learning_analytics_postgres 容器"
    echo "  请手动运行以下 SQL 命令："
    echo ""
    echo "  psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME -c \""
    echo "    ALTER TABLE student_attempts"
    echo "    ALTER COLUMN ip_address TYPE VARCHAR(64);"
    echo "    ALTER TABLE student_attempts"
    echo "    ALTER COLUMN partial_score TYPE NUMERIC(6,2);"
    echo "  \""
    echo ""
fi

echo ""
echo "验证修复结果..."
echo ""

# 验证字段类型
if docker ps | grep -q "learning_analytics_postgres"; then
    echo "检查 ip_address 字段:"
    docker exec learning_analytics_postgres psql -U rag_user -d learning_analytics -c "\d student_attempts" | grep "ip_address"

    echo ""
    echo "检查 partial_score 字段:"
    docker exec learning_analytics_postgres psql -U rag_user -d learning_analytics -c "\d student_attempts" | grep "partial_score"
fi

echo ""
echo "=========================================="
echo "修复完成！现在可以正常提交评分。"
echo "=========================================="