ggl/scripts/batch-upload-animal-videos.cjs

const fs = require('fs');
const path = require('path');
const sqlite3 = require('sqlite3').verbose();
const { exec } = require('child_process');
const util = require('util');
const execPromise = util.promisify(exec);

// 数据库连接
const db = new sqlite3.Database('goguryeo_video.db');

// 视频文件目录
const VIDEO_DIR = './video';
const UPLOAD_VIDEO_DIR = './uploads/videos';
const UPLOAD_COVER_DIR = './uploads/covers';

// 确保上传目录存在
if (!fs.existsSync(UPLOAD_VIDEO_DIR)) {
  fs.mkdirSync(UPLOAD_VIDEO_DIR, { recursive: true });
}
if (!fs.existsSync(UPLOAD_COVER_DIR)) {
  fs.mkdirSync(UPLOAD_COVER_DIR, { recursive: true });
}

// 动物分类映射
const animalCategoryMap = {
  '羊': { category: 5, tags: ['萌宠', '小羊', '可爱', '动物'] },
  '老虎': { category: 5, tags: ['萌宠', '老虎', '动物园', '可爱'] },
  '荷兰猪': { category: 5, tags: ['萌宠', '荷兰猪', '可爱'] },
  '兔子': { category: 5, tags: ['萌宠', '兔子', '可爱'] },
  '企鹅': { category: 5, tags: ['萌宠', '企鹅', '可爱'] },
  '牛': { category: 5, tags: ['动物', '小牛', '农村生活', '可爱'] },
  '猴子': { category: 5, tags: ['猴子', '可爱', '动物'] },
  '马': { category: 5, tags: ['萌宠', '矮马', '可爱'] },
  '水獭': { category: 5, tags: ['水獭', '萌宠', '治愈', '可爱'] },
  '鸟': { category: 5, tags: ['可爱', '治愈', '萌宠'] },
  '狗': { category: 5, tags: ['宠物', '狗子', '萌宠'] },
  '象': { category: 5, tags: ['小象', '萌宠', '可爱'] },
  '猪': { category: 5, tags: ['小香猪', '小猪', '可爱'] },
  '猫': { category: 5, tags: ['小猫咪', '萌宠', '可爱', '治愈'] }
};

// 根据文件名识别动物类型
function identifyAnimalType(filename) {
  for (const [animal, config] of Object.entries(animalCategoryMap)) {
    if (filename.includes(animal)) {
      return config;
    }
  }
  // 默认分类为民俗风情
  return { category: 5, tags: ['萌宠', '可爱', '动物'] };
}

// 生成唯一文件名
function generateUniqueFilename(originalName) {
  const timestamp = Date.now();
  const randomStr = Math.random().toString(36).substring(2, 15);
  const ext = path.extname(originalName);
  return `${timestamp}_${randomStr}${ext}`;
}

// 获取视频时长
async function getVideoDuration(videoPath) {
  try {
    const { stdout } = await execPromise(`ffprobe -v quiet -show_entries format=duration -of csv=p=0 "${videoPath}"`);
    return Math.round(parseFloat(stdout.trim()));
  } catch (error) {
    console.log(`无法获取视频时长: ${videoPath}`);
    return 0;
  }
}

// 生成视频封面
async function generateThumbnail(videoPath, outputPath) {
  try {
    await execPromise(`ffmpeg -i "${videoPath}" -ss 00:00:01 -vframes 1 -y "${outputPath}"`);
    return true;
  } catch (error) {
    console.log(`生成封面失败: ${videoPath}`);
    return false;
  }
}

// 插入视频到数据库
function insertVideo(videoData) {
  return new Promise((resolve, reject) => {
    const sql = `
      INSERT INTO videos (
        title, description, video_url, file_path, cover_url, cover_image,
        duration, file_size, category, tags, user_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;
    
    db.run(sql, [
      videoData.title,
      videoData.description,
      videoData.video_url,
      videoData.file_path,
      videoData.cover_url,
      videoData.cover_image,
      videoData.duration,
      videoData.file_size,
      videoData.category,
      videoData.tags,
      1 // admin user_id
    ], function(err) {
      if (err) {
        reject(err);
      } else {
        resolve(this.lastID);
      }
    });
  });
}

// 处理单个视频文件
async function processVideo(filename) {
  try {
    const originalPath = path.join(VIDEO_DIR, filename);
    const stats = fs.statSync(originalPath);
    
    // 生成新文件名
    const newVideoName = generateUniqueFilename(filename);
    const newCoverName = newVideoName.replace('.mp4', '_cover.jpg');
    
    const newVideoPath = path.join(UPLOAD_VIDEO_DIR, newVideoName);
    const newCoverPath = path.join(UPLOAD_COVER_DIR, newCoverName);
    
    // 复制视频文件
    fs.copyFileSync(originalPath, newVideoPath);
    console.log(`复制视频: ${filename} -> ${newVideoName}`);
    
    // 生成封面
    const thumbnailGenerated = await generateThumbnail(newVideoPath, newCoverPath);
    
    // 获取视频时长
    const duration = await getVideoDuration(newVideoPath);
    
    // 识别动物类型和分类
    const animalConfig = identifyAnimalType(filename);
    
    // 清理标题（移除文件扩展名和平台标识）
    const title = filename
      .replace('.mp4', '')
      .replace('-快手', '')
      .trim();
    
    // 准备视频数据
    const videoData = {
      title: title,
      description: `可爱的动物视频：${title}`,
      video_url: `/uploads/videos/${newVideoName}`,
      file_path: `/uploads/videos/${newVideoName}`,
      cover_url: thumbnailGenerated ? `/uploads/covers/${newCoverName}` : null,
      cover_image: thumbnailGenerated ? `/uploads/covers/${newCoverName}` : null,
      duration: duration,
      file_size: stats.size,
      category: animalConfig.category,
      tags: animalConfig.tags.join(','),
    };
    
    // 插入数据库
    const videoId = await insertVideo(videoData);
    console.log(`视频上传成功: ${title} (ID: ${videoId})`);
    
    return {
      success: true,
      videoId: videoId,
      title: title,
      category: animalConfig.category
    };
    
  } catch (error) {
    console.error(`处理视频失败 ${filename}:`, error.message);
    return {
      success: false,
      filename: filename,
      error: error.message
    };
  }
}

// 主函数
async function main() {
  try {
    console.log('开始批量上传动物视频...');
    
    // 读取视频目录
    const files = fs.readdirSync(VIDEO_DIR)
      .filter(file => file.endsWith('.mp4'));
    
    console.log(`找到 ${files.length} 个视频文件`);
    
    const results = [];
    const categoryStats = {};
    
    // 处理每个视频文件
    for (const file of files) {
      console.log(`\n处理: ${file}`);
      const result = await processVideo(file);
      results.push(result);
      
      if (result.success) {
        const category = result.category;
        categoryStats[category] = (categoryStats[category] || 0) + 1;
      }
      
      // 添加延迟避免系统过载
      await new Promise(resolve => setTimeout(resolve, 1000));
    }
    
    // 统计结果
    const successful = results.filter(r => r.success).length;
    const failed = results.filter(r => !r.success).length;
    
    console.log('\n=== 上传完成 ===');
    console.log(`成功: ${successful} 个`);
    console.log(`失败: ${failed} 个`);
    
    console.log('\n=== 分类统计 ===');
    for (const [categoryId, count] of Object.entries(categoryStats)) {
      console.log(`分类 ${categoryId}: ${count} 个视频`);
    }
    
    if (failed > 0) {
      console.log('\n=== 失败的文件 ===');
      results.filter(r => !r.success).forEach(r => {
        console.log(`${r.filename}: ${r.error}`);
      });
    }
    
  } catch (error) {
    console.error('批量上传失败:', error);
  } finally {
    db.close();
  }
}

// 运行脚本
if (require.main === module) {
  main();
}

module.exports = { main, processVideo };