math_cms/scripts/migrate_learning_analytics_data.php

<?php

/**
 * LearningAnalytics数据迁移脚本
 * 将PostgreSQL中的数据迁移到MySQL
 */

require __DIR__ . '/../vendor/autoload.php';

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

// 设置Laravel应用
$app = require_once __DIR__ . '/../bootstrap/app.php';
$kernel = $app->make(Illuminate\Contracts\Console\Kernel::class);
$kernel->bootstrap();

echo "=== LearningAnalytics数据迁移开始 ===\n\n";

try {
    // 1. 迁移知识点数据
    echo "1. 迁移知识点数据...\n";
    $knowledgePoints = [
        ['kp_code' => 'KP001', 'name' => '因式分解基础', 'parent_kp_code' => null, 'level' => 1],
        ['kp_code' => 'KP002', 'name' => '提公因式法', 'parent_kp_code' => 'KP001', 'level' => 2],
        ['kp_code' => 'KP003', 'name' => '分组分解法', 'parent_kp_code' => 'KP001', 'level' => 2],
        ['kp_code' => 'KP004', 'name' => '完全平方公式', 'parent_kp_code' => 'KP001', 'level' => 2],
        ['kp_code' => 'KP005', 'name' => '平方差公式', 'parent_kp_code' => 'KP001', 'level' => 2],
    ];

    foreach ($knowledgePoints as $kp) {
        DB::table('knowledge_points')->updateOrInsert(
            ['kp_code' => $kp['kp_code']],
            [
                'name' => $kp['name'],
                'parent_kp_code' => $kp['parent_kp_code'],
                'created_at' => now(),
                'updated_at' => now(),
            ]
        );
    }
    echo "✓ 知识点数据迁移完成\n\n";

    // 2. 从PostgreSQL导出student_attempts数据
    echo "2. 从PostgreSQL导出student_attempts数据...\n";
    echo "注意：需要手动执行以下命令导出数据：\n";
    echo "docker exec learning_analytics_postgres pg_dump -U rag_user -d learning_analytics -t student_attempts --data-only --no-owner --no-privileges > /tmp/student_attempts.sql\n\n";

    // 3. 转换PostgreSQL数据为MySQL格式
    echo "3. 数据转换说明：\n";
    echo " - PostgreSQL的timestamp with time zone -> MySQL的timestamp\n";
    echo " - PostgreSQL的boolean (t/f) -> MySQL的boolean (1/0)\n";
    echo " - PostgreSQL的numeric -> MySQL的decimal\n";
    echo " - PostgreSQL的jsonb -> MySQL的json\n";
    echo " - PostgreSQL的inet -> MySQL的varchar\n\n";

    // 4. 导入示例数据（如果PostgreSQL无法访问）
    echo "4. 导入示例数据到student_attempts表...\n";
    $sampleAttempts = [
        [
            'student_id' => 'stu_1762395159_4',
            'session_id' => 'session_stu_1762395159_4_1763471539',
            'attempt_number' => 1,
            'question_id' => 'Q_BATCH_stu_1762395159_4_1763471524_6680',
            'question_code' => 'Q_BATCH_stu_1762395159_4_1763471524_6680',
            'kp_code' => 'KP1001',
            'skill_tags' => null,
            'is_correct' => false,
            'student_answer' => '',
            'correct_answer' => '解题步骤：使用立方差公式a³ - b³ = (a - b)(a² + ab + b²)，其中a=x, b=2',
            'partial_score' => 0.0000,
            'attempt_time_seconds' => 88.00,
            'time_per_question' => 88.00,
            'started_at' => '2025-11-18 13:12:19',
            'completed_at' => '2025-11-18 13:12:19',
            'time_spent_seconds' => 88,
            'question_difficulty' => 0.08,
            'question_type' => '数学题',
            'max_score' => 100,
            'device_type' => null,
            'browser_info' => null,
            'ip_address' => null,
            'location_info' => null,
            'status' => 'completed',
            'reviewed' => false,
            'feedback_provided' => null,
            'created_at' => '2025-11-18 13:12:19',
            'updated_at' => '2025-11-18 13:12:19',
        ],
        [
            'student_id' => 'stu_1762395159_4',
            'session_id' => 'session_stu_1762395159_4_1763471539',
            'attempt_number' => 1,
            'question_id' => 'Q_BATCH_stu_1762395159_4_1763471524_7908',
            'question_code' => 'Q_BATCH_stu_1762395159_4_1763471524_7908',
            'kp_code' => 'KP1001',
            'skill_tags' => null,
            'is_correct' => true,
            'student_answer' => '',
            'correct_answer' => '应用解题过程：首先识别需要十字相乘的数学对象，然后应用相应的分解方法...',
            'partial_score' => 1.0000,
            'attempt_time_seconds' => 137.00,
            'time_per_question' => 137.00,
            'started_at' => '2025-11-18 13:12:19',
            'completed_at' => '2025-11-18 13:12:19',
            'time_spent_seconds' => 137,
            'question_difficulty' => 0.08,
            'question_type' => '数学题',
            'max_score' => 100,
            'device_type' => null,
            'browser_info' => null,
            'ip_address' => null,
            'location_info' => null,
            'status' => 'completed',
            'reviewed' => false,
            'feedback_provided' => null,
            'created_at' => '2025-11-18 13:12:19',
            'updated_at' => '2025-11-18 13:12:19',
        ],
    ];

    foreach ($sampleAttempts as $attempt) {
        DB::table('student_attempts')->insert($attempt);
    }
    echo "✓ 示例数据导入完成\n\n";

    // 5. 验证迁移结果
    echo "5. 验证迁移结果...\n";
    $kpCount = DB::table('knowledge_points')->count();
    $attemptCount = DB::table('student_attempts')->count();

    echo "知识点数量: {$kpCount}\n";
    echo "答题记录数量: {$attemptCount}\n\n";

    echo "=== LearningAnalytics数据迁移完成 ===\n";

} catch (Exception $e) {
    echo "❌ 迁移失败: " . $e->getMessage() . "\n";
    echo "堆栈跟踪: " . $e->getTraceAsString() . "\n";
    exit(1);
}