aiapply/migrate-prompt-template-data.js

const mysql = require('mysql2/promise');

// 数据库连接配置
const dbConfig = {
  host: 'gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com',
  port: 24936,
  user: 'root',
  password: '!Rjb12191',
  database: 'pronode_db',
  charset: 'utf8mb4'
};

async function migratePromptTemplateData() {
  let connection;
  
  try {
    console.log('🔗 连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    
    // 1. 首先检查 prompt_template 表是否存在
    console.log('📋 检查 prompt_template 表...');
    const [tables] = await connection.execute(`
      SELECT TABLE_NAME 
      FROM information_schema.TABLES 
      WHERE TABLE_SCHEMA = 'pronode_db' 
      AND TABLE_NAME = 'prompt_template'
    `);
    
    if (tables.length === 0) {
      console.log('❌ prompt_template 表不存在');
      return;
    }
    
    // 2. 获取 prompt_template 表的结构
    console.log('🔍 获取 prompt_template 表结构...');
    const [columns] = await connection.execute(`
      DESCRIBE prompt_template
    `);
    console.log('prompt_template 表结构:', columns);
    
    // 3. 获取 prompt_template 表中的所有数据
    console.log('📊 获取 prompt_template 数据...');
    const [sourceData] = await connection.execute('SELECT * FROM prompt_template');
    console.log(`找到 ${sourceData.length} 条数据`);
    
    if (sourceData.length === 0) {
      console.log('❌ prompt_template 表中没有数据');
      return;
    }
    
    // 4. 显示前几条数据作为示例
    console.log('📝 数据示例:');
    sourceData.slice(0, 3).forEach((row, index) => {
      console.log(`记录 ${index + 1}:`, row);
    });
    
    // 5. 获取 promptforge_templates 表结构
    console.log('🔍 获取 promptforge_templates 表结构...');
    const [targetColumns] = await connection.execute(`
      DESCRIBE promptforge_templates
    `);
    console.log('promptforge_templates 表结构:', targetColumns);
    
    // 6. 开始数据迁移
    console.log('🚀 开始数据迁移...');
    let successCount = 0;
    let errorCount = 0;
    
    for (const sourceRow of sourceData) {
      try {
        // 构建目标数据，根据字段名进行映射
        const targetData = {
          id: sourceRow.id || `template_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`,
          title: sourceRow.title || sourceRow.name || '未命名模板',
          description: sourceRow.description || sourceRow.desc || '',
          category: sourceRow.category || 'programming',
          role: sourceRow.role || '',
          task: sourceRow.task || '',
          context: sourceRow.context || '',
          constraints: sourceRow.constraints ? JSON.stringify(sourceRow.constraints) : '[]',
          variables: sourceRow.variables ? JSON.stringify(sourceRow.variables) : '[]',
          outputFormat: sourceRow.outputFormat || sourceRow.output_format || '',
          authorId: sourceRow.authorId || sourceRow.user_id || sourceRow.author_id || 'system',
          isPublic: sourceRow.isPublic || sourceRow.is_public || true,
          usageCount: sourceRow.usageCount || sourceRow.usage_count || 0,
          rating: sourceRow.rating || 0,
          ratingCount: sourceRow.ratingCount || sourceRow.rating_count || 0,
          createdAt: sourceRow.createdAt || sourceRow.created_at || new Date().toISOString().slice(0, 19).replace('T', ' '),
          updatedAt: sourceRow.updatedAt || sourceRow.updated_at || new Date().toISOString().slice(0, 19).replace('T', ' ')
        };
        
        // 检查是否已存在相同ID的记录
        const [existing] = await connection.execute(
          'SELECT id FROM promptforge_templates WHERE id = ?',
          [targetData.id]
        );
        
        if (existing.length > 0) {
          console.log(`⚠️  跳过已存在的记录: ${targetData.id}`);
          continue;
        }
        
        // 插入数据
        await connection.execute(`
          INSERT INTO promptforge_templates (
            id, title, description, category, role, task, context, 
            constraints, variables, outputFormat, authorId, isPublic, 
            usageCount, rating, ratingCount, createdAt, updatedAt
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `, [
          targetData.id,
          targetData.title,
          targetData.description,
          targetData.category,
          targetData.role,
          targetData.task,
          targetData.context,
          targetData.constraints,
          targetData.variables,
          targetData.outputFormat,
          targetData.authorId,
          targetData.isPublic,
          targetData.usageCount,
          targetData.rating,
          targetData.ratingCount,
          targetData.createdAt,
          targetData.updatedAt
        ]);
        
        successCount++;
        console.log(`✅ 成功迁移: ${targetData.title}`);
        
      } catch (error) {
        errorCount++;
        console.error(`❌ 迁移失败 (${sourceRow.id || 'unknown'}):`, error.message);
      }
    }
    
    console.log('\n📊 迁移完成统计:');
    console.log(`✅ 成功: ${successCount} 条`);
    console.log(`❌ 失败: ${errorCount} 条`);
    console.log(`📝 总计: ${sourceData.length} 条`);
    
    // 7. 验证迁移结果
    console.log('\n🔍 验证迁移结果...');
    const [finalCount] = await connection.execute('SELECT COUNT(*) as count FROM promptforge_templates');
    console.log(`promptforge_templates 表现在有 ${finalCount[0].count} 条记录`);
    
  } catch (error) {
    console.error('❌ 迁移过程中发生错误:', error);
  } finally {
    if (connection) {
      await connection.end();
      console.log('🔌 数据库连接已关闭');
    }
  }
}

// 运行迁移
migratePromptTemplateData().catch(console.error);