#!/bin/bash

# SQLite 到 D1 数据库迁移脚本
# 使用方法: ./migrate-to-d1.sh [sqlite_db_path]

set -e

# 检查参数
SQLITE_DB_PATH=${1:-"./ainovel.db"}

if [ ! -f "$SQLITE_DB_PATH" ]; then
    echo "❌ SQLite 数据库文件不存在: $SQLITE_DB_PATH"
    echo "请确保 SQLite 数据库文件存在，或者提供正确的路径"
    exit 1
fi

echo "🔄 开始从 SQLite 迁移到 Cloudflare D1..."
echo "📍 SQLite 数据库: $SQLITE_DB_PATH"

# 检查 wrangler 是否可用
if ! command -v wrangler &> /dev/null; then
    echo "❌ wrangler 命令不可用，请安装 Wrangler"
    echo "npm install -g wrangler"
    exit 1
fi

# 检查是否登录 Cloudflare
echo "📋 检查 Cloudflare 登录状态..."
if ! wrangler auth whoami &> /dev/null; then
    echo "❌ 未登录 Cloudflare，请先运行: wrangler login"
    exit 1
fi

# 创建临时导出目录
TEMP_DIR="./migration_temp"
mkdir -p "$TEMP_DIR"

echo "📊 导出现有数据..."

# 导出灵感数据
echo "📝 导出灵感数据..."
sqlite3 "$SQLITE_DB_PATH" "SELECT * FROM inspirations;" > "$TEMP_DIR/inspirations.csv"

# 导出大纲数据
echo "📝 导出大纲数据..."
sqlite3 "$SQLITE_DB_PATH" "SELECT * FROM outlines;" > "$TEMP_DIR/outlines.csv"

# 导出章节数据
echo "📝 导出章节数据..."
sqlite3 "$SQLITE_DB_PATH" "SELECT * FROM chapters;" > "$TEMP_DIR/chapters.csv"

# 导出全局线条数据
echo "📝 导出全局线条数据..."
sqlite3 "$SQLITE_DB_PATH" "SELECT * FROM global_lines;" > "$TEMP_DIR/global_lines.csv"

echo "✅ 数据导出完成"

# 检查 D1 数据库是否存在
echo "🗄️ 检查 D1 数据库..."
if ! wrangler d1 info ainovel-db &> /dev/null; then
    echo "🆕 创建 D1 数据库..."
    wrangler d1 create ainovel-db
    
    echo "⚠️  请将上面的 database_id 复制到 wrangler.toml 文件中"
    echo "然后重新运行此脚本"
    exit 1
fi

# 运行数据库初始化迁移
echo "🏗️ 初始化 D1 数据库结构..."
wrangler d1 execute ainovel-db --file=./migrations/001_initial_tables.sql

echo "🔄 开始导入数据到 D1..."

# 导入灵感数据（如果有）
if [ -s "$TEMP_DIR/inspirations.csv" ]; then
    echo "💾 导入灵感数据..."
    # 跳过标题行，逐行插入
    tail -n +2 "$TEMP_DIR/inspirations.csv" | while IFS='|' read -r id content category created_at updated_at; do
        if [ -n "$content" ]; then
            wrangler d1 execute ainovel-db --command="INSERT INTO inspirations (content, category, created_at, updated_at) VALUES ('$content', '$category', '$created_at', '$updated_at');"
        fi
    done
fi

# 导入大纲数据（如果有）
if [ -s "$TEMP_DIR/outlines.csv" ]; then
    echo "💾 导入大纲数据..."
    tail -n +2 "$TEMP_DIR/outlines.csv" | while IFS='|' read -r id title description structure created_at updated_at; do
        if [ -n "$title" ]; then
            wrangler d1 execute ainovel-db --command="INSERT INTO outlines (title, description, structure, created_at, updated_at) VALUES ('$title', '$description', '$structure', '$created_at', '$updated_at');"
        fi
    done
fi

# 导入章节数据（如果有）
if [ -s "$TEMP_DIR/chapters.csv" ]; then
    echo "💾 导入章节数据..."
    tail -n +2 "$TEMP_DIR/chapters.csv" | while IFS='|' read -r id outline_id title content chapter_number status created_at updated_at; do
        if [ -n "$title" ]; then
            wrangler d1 execute ainovel-db --command="INSERT INTO chapters (outline_id, title, content, chapter_number, status, created_at, updated_at) VALUES ($outline_id, '$title', '$content', $chapter_number, '$status', '$created_at', '$updated_at');"
        fi
    done
fi

# 导入全局线条数据（如果有）
if [ -s "$TEMP_DIR/global_lines.csv" ]; then
    echo "💾 导入全局线条数据..."
    tail -n +2 "$TEMP_DIR/global_lines.csv" | while IFS='|' read -r id content category importance created_at updated_at; do
        if [ -n "$content" ]; then
            wrangler d1 execute ainovel-db --command="INSERT INTO global_lines (content, category, importance, created_at, updated_at) VALUES ('$content', '$category', $importance, '$created_at', '$updated_at');"
        fi
    done
fi

# 清理临时文件
echo "🧹 清理临时文件..."
rm -rf "$TEMP_DIR"

# 验证数据
echo "✅ 验证迁移结果..."
echo ""
echo "📊 数据统计："
wrangler d1 execute ainovel-db --command="SELECT 'inspirations' as table_name, COUNT(*) as count FROM inspirations UNION SELECT 'outlines', COUNT(*) FROM outlines UNION SELECT 'chapters', COUNT(*) FROM chapters UNION SELECT 'global_lines', COUNT(*) FROM global_lines;"

echo ""
echo "🎉 数据库迁移完成!"
echo ""
echo "📖 下一步："
echo "1. 运行后端部署: ./deploy-backend.sh"
echo "2. 设置环境变量: wrangler secret put GEMINI_API_KEY"
echo "3. 部署前端: ../frontend/deploy-cloudflare.sh"
echo ""