1439 lines
39 KiB
TypeScript
Executable File
1439 lines
39 KiB
TypeScript
Executable File
/**
|
||
* 视频管理API路由
|
||
* Handle video upload, list, details, etc.
|
||
*/
|
||
import { Router, type Request, type Response } from 'express';
|
||
import multer from 'multer';
|
||
import path from 'path';
|
||
import fs from 'fs';
|
||
import { spawn } from 'child_process';
|
||
import { query, queryOne, execute } from '../config/database.js';
|
||
import { authenticateToken, optionalAuth } from '../middleware/auth.js';
|
||
|
||
const router = Router();
|
||
|
||
/**
|
||
* 从视频文件直接提取封面帧
|
||
* @param videoPath 视频文件路径
|
||
* @param timestamp 提取时间点（秒），默认为1秒
|
||
* @returns Promise<Buffer | null> 返回图片Buffer或null
|
||
*/
|
||
const extractVideoFrame = (videoPath: string, timestamp: number = 1): Promise<Buffer | null> => {
|
||
return new Promise((resolve) => {
|
||
const ffmpeg = spawn('ffmpeg', [
|
||
'-i', videoPath,
|
||
'-ss', timestamp.toString(),
|
||
'-vframes', '1',
|
||
'-f', 'image2pipe',
|
||
'-vcodec', 'mjpeg',
|
||
'-'
|
||
]);
|
||
|
||
const chunks: Buffer[] = [];
|
||
|
||
ffmpeg.stdout.on('data', (chunk) => {
|
||
chunks.push(chunk);
|
||
});
|
||
|
||
ffmpeg.on('close', (code) => {
|
||
if (code === 0 && chunks.length > 0) {
|
||
resolve(Buffer.concat(chunks));
|
||
} else {
|
||
resolve(null);
|
||
}
|
||
});
|
||
|
||
ffmpeg.on('error', () => {
|
||
resolve(null);
|
||
});
|
||
});
|
||
};
|
||
|
||
/**
|
||
* 批量更新视频分类
|
||
* PUT /api/videos/batch/category
|
||
*/
|
||
router.put('/batch/category', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
console.log('=== 批量修改分类接口开始 ===');
|
||
console.log('请求方法:', req.method);
|
||
console.log('请求路径:', req.path);
|
||
console.log('请求数据:', req.body);
|
||
console.log('用户信息:', req.user);
|
||
const { videoIds, category } = req.body;
|
||
|
||
if (!videoIds || !Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供要修改的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
if (category === undefined || category === null || category === '') {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供有效的分类'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 将分类ID转换为字符串（如果是数字）
|
||
const categoryValue = typeof category === 'number' ? category.toString() : category;
|
||
|
||
// 验证所有ID都是有效数字
|
||
const validIds = videoIds.filter(id => typeof id === 'number' && !isNaN(id));
|
||
if (validIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '无效的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查用户权限（只能修改自己上传的视频，或管理员可以修改所有视频）
|
||
const placeholders = validIds.map(() => '?').join(',');
|
||
console.log('批量修改分类 - 查询视频:', validIds);
|
||
console.log('批量修改分类 - 用户信息:', req.user);
|
||
|
||
const videos = await query(`
|
||
SELECT id, user_id FROM videos
|
||
WHERE id IN (${placeholders})
|
||
`, validIds);
|
||
|
||
console.log('批量修改分类 - 查询到的视频:', videos);
|
||
|
||
if (videos.length === 0) {
|
||
console.log('批量修改分类 - 未找到视频');
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '未找到要修改的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查权限 - 管理员可以修改所有视频
|
||
if (req.user!.role !== 'admin') {
|
||
const unauthorizedVideos = videos.filter(video =>
|
||
video.user_id !== req.user!.id
|
||
);
|
||
|
||
if (unauthorizedVideos.length > 0) {
|
||
console.log('批量修改分类 - 权限不足:', unauthorizedVideos);
|
||
res.status(403).json({
|
||
success: false,
|
||
message: '没有权限修改部分视频'
|
||
});
|
||
return;
|
||
}
|
||
}
|
||
|
||
console.log('批量修改分类 - 权限检查通过');
|
||
|
||
// 更新视频分类
|
||
const foundIds = videos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
await execute(`
|
||
UPDATE videos
|
||
SET category = ?, updated_at = datetime('now')
|
||
WHERE id IN (${foundPlaceholders})
|
||
`, [categoryValue, ...foundIds]);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功修改 ${foundIds.length} 个视频的分类`,
|
||
data: {
|
||
updatedCount: foundIds.length,
|
||
updatedIds: foundIds,
|
||
category: categoryValue
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 批量修改视频分类失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 批量删除视频 (POST方式)
|
||
* POST /api/videos/batch/delete
|
||
*/
|
||
router.post('/batch/delete', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const { videoIds } = req.body;
|
||
|
||
if (!videoIds || !Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供要删除的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 验证所有ID都是有效数字
|
||
const validIds = videoIds.filter(id => typeof id === 'number' && !isNaN(id));
|
||
if (validIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '无效的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查用户权限（只能删除自己上传的视频，或管理员可以删除所有视频）
|
||
const placeholders = validIds.map(() => '?').join(',');
|
||
const videos = await query(`
|
||
SELECT id, file_path, cover_image, user_id FROM videos
|
||
WHERE id IN (${placeholders})
|
||
`, validIds);
|
||
|
||
if (videos.length === 0) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '未找到要删除的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查权限
|
||
const unauthorizedVideos = videos.filter(video =>
|
||
video.user_id !== req.user!.id && req.user!.role !== 'admin'
|
||
);
|
||
|
||
if (unauthorizedVideos.length > 0) {
|
||
res.status(403).json({
|
||
success: false,
|
||
message: '没有权限删除部分视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 删除相关文件
|
||
for (const video of videos) {
|
||
try {
|
||
// 删除视频文件
|
||
if (video.file_path) {
|
||
const videoPath = path.join(process.cwd(), video.file_path);
|
||
if (fs.existsSync(videoPath)) {
|
||
fs.unlinkSync(videoPath);
|
||
}
|
||
}
|
||
|
||
// 删除封面文件
|
||
if (video.cover_image) {
|
||
const coverPath = path.join(process.cwd(), video.cover_image.replace(/^\//, ''));
|
||
if (fs.existsSync(coverPath)) {
|
||
fs.unlinkSync(coverPath);
|
||
}
|
||
}
|
||
} catch (fileError) {
|
||
// 删除文件失败
|
||
}
|
||
}
|
||
|
||
// 删除数据库记录
|
||
const foundIds = videos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
// 删除相关统计数据
|
||
await execute(`DELETE FROM video_stats WHERE video_id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
// 删除专题关联
|
||
await execute(`DELETE FROM video_topics WHERE video_id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
// 删除视频记录
|
||
await execute(`DELETE FROM videos WHERE id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功删除 ${foundIds.length} 个视频`,
|
||
data: {
|
||
deletedCount: foundIds.length,
|
||
deletedIds: foundIds
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 批量删除视频失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取视频时长
|
||
* @param videoPath 视频文件路径
|
||
* @returns Promise<number> 返回视频时长（秒），失败返回0
|
||
*/
|
||
const getVideoDuration = (videoPath: string): Promise<number> => {
|
||
return new Promise((resolve) => {
|
||
const ffprobe = spawn('ffprobe', [
|
||
'-v', 'quiet',
|
||
'-show_entries', 'format=duration',
|
||
'-of', 'csv=p=0',
|
||
videoPath
|
||
]);
|
||
|
||
let output = '';
|
||
|
||
ffprobe.stdout.on('data', (data) => {
|
||
output += data.toString();
|
||
});
|
||
|
||
ffprobe.on('close', (code) => {
|
||
if (code === 0) {
|
||
const videoDuration = parseFloat(output.trim());
|
||
if (!isNaN(videoDuration)) {
|
||
// 视频时长获取成功
|
||
resolve(Math.round(videoDuration));
|
||
} else {
|
||
// 视频时长解析失败
|
||
resolve(0);
|
||
}
|
||
} else {
|
||
// ffprobe 执行失败
|
||
resolve(0);
|
||
}
|
||
});
|
||
|
||
ffprobe.on('error', (error) => {
|
||
// ffprobe 执行错误
|
||
resolve(0);
|
||
});
|
||
});
|
||
};
|
||
|
||
/**
|
||
* 生成视频封面（保留原有功能用于兼容）
|
||
* @param videoPath 视频文件路径
|
||
* @param outputPath 输出封面路径
|
||
* @returns Promise<boolean> 是否生成成功
|
||
*/
|
||
const generateVideoCover = (videoPath: string, outputPath: string): Promise<boolean> => {
|
||
return new Promise((resolve) => {
|
||
// 确保输出目录存在
|
||
const outputDir = path.dirname(outputPath);
|
||
if (!fs.existsSync(outputDir)) {
|
||
fs.mkdirSync(outputDir, { recursive: true });
|
||
}
|
||
|
||
// 使用 ffmpeg 从视频第1秒提取一帧作为封面
|
||
const ffmpeg = spawn('ffmpeg', [
|
||
'-i', videoPath,
|
||
'-ss', '00:00:01',
|
||
'-vframes', '1',
|
||
'-y',
|
||
outputPath
|
||
]);
|
||
|
||
ffmpeg.on('close', (code) => {
|
||
if (code === 0 && fs.existsSync(outputPath)) {
|
||
// 视频封面生成成功
|
||
resolve(true);
|
||
} else {
|
||
// 视频封面生成失败
|
||
resolve(false);
|
||
}
|
||
});
|
||
|
||
ffmpeg.on('error', (error) => {
|
||
// ffmpeg 执行错误
|
||
resolve(false);
|
||
});
|
||
});
|
||
};
|
||
|
||
// 配置multer用于文件上传
|
||
const storage = multer.memoryStorage();
|
||
const upload = multer({
|
||
storage,
|
||
limits: {
|
||
fileSize: 500 * 1024 * 1024 // 500MB限制
|
||
},
|
||
fileFilter: (req, file, cb) => {
|
||
// 检查文件类型
|
||
// 文件过滤器检查
|
||
|
||
// 支持的MIME类型（更全面的列表）
|
||
const allowedTypes = [
|
||
// 标准视频MIME类型
|
||
'video/mp4', 'video/mpeg', 'video/quicktime', 'video/x-msvideo',
|
||
'video/webm', 'video/ogg', 'video/3gpp', 'video/3gpp2',
|
||
'video/x-ms-wmv', 'video/x-flv', 'video/x-matroska',
|
||
'video/mp2t', 'video/x-ms-asf', 'video/x-m4v',
|
||
// 应用程序类型（某些系统可能将视频文件识别为此类型）
|
||
'application/octet-stream', 'application/x-msvideo',
|
||
'application/vnd.rn-realmedia', 'application/x-shockwave-flash'
|
||
];
|
||
|
||
// 支持的文件扩展名（与前端一致）
|
||
const allowedExtensions = [
|
||
'.mp4', '.webm', '.ogg', '.avi', '.mov', '.3gp', '.3g2',
|
||
'.wmv', '.flv', '.mkv', '.m4v', '.ts', '.asf', '.mpg',
|
||
'.mpeg', '.m2v', '.rmvb', '.rm', '.divx', '.xvid',
|
||
'.f4v', '.m2ts', '.mts', '.vob', '.dat', '.amv'
|
||
];
|
||
|
||
const fileExtension = path.extname(file.originalname).toLowerCase();
|
||
const mimeType = file.mimetype.toLowerCase();
|
||
|
||
// 更宽松的验证逻辑：
|
||
// 1. 扩展名匹配（优先级最高，因为扩展名通常最可靠）
|
||
// 2. MIME类型完全匹配
|
||
// 3. MIME类型以video/开头且扩展名正确
|
||
// 4. MIME类型为空但扩展名正确（某些浏览器可能不提供MIME类型）
|
||
const isExtensionValid = allowedExtensions.includes(fileExtension);
|
||
const isMimeTypeValid = allowedTypes.includes(mimeType);
|
||
const isVideoMimeType = mimeType.startsWith('video/');
|
||
|
||
const isValid = isExtensionValid || isMimeTypeValid || (isVideoMimeType && isExtensionValid) || (!mimeType && isExtensionValid);
|
||
|
||
// 文件验证结果
|
||
|
||
if (isValid) {
|
||
// 文件类型和扩展名检查通过
|
||
cb(null, true);
|
||
} else {
|
||
const errorMsg = `不支持的视频格式。文件: ${file.originalname}, MIME类型: ${mimeType}, 扩展名: ${fileExtension}。请上传支持的视频格式：MP4、AVI、MOV、WMV、WebM、MKV、FLV 等`;
|
||
// 文件类型检查失败
|
||
cb(new Error(errorMsg));
|
||
}
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取视频列表（支持分页和搜索）
|
||
* GET /api/videos
|
||
*/
|
||
router.get('/', optionalAuth, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const page = parseInt(req.query.page as string) || 1;
|
||
const limit = parseInt(req.query.limit as string) || 12;
|
||
const search = req.query.search as string || '';
|
||
const topicId = req.query.topicId as string || '';
|
||
const category = req.query.category as string || '';
|
||
const offset = (page - 1) * limit;
|
||
|
||
let whereClause = '';
|
||
let params: any[] = [];
|
||
let joins = '';
|
||
|
||
const conditions: string[] = [];
|
||
|
||
if (search) {
|
||
conditions.push('(v.title LIKE ? OR v.description LIKE ?)');
|
||
params.push(`%${search}%`, `%${search}%`);
|
||
}
|
||
|
||
if (topicId) {
|
||
joins += ' INNER JOIN video_topics vt ON v.id = vt.video_id';
|
||
conditions.push('vt.topic_id = ?');
|
||
params.push(parseInt(topicId));
|
||
}
|
||
|
||
if (category) {
|
||
conditions.push('v.category = ?');
|
||
params.push(category);
|
||
}
|
||
|
||
// 添加状态过滤条件，只显示active状态的视频
|
||
conditions.push('v.status = ?');
|
||
params.push('active');
|
||
|
||
if (conditions.length > 0) {
|
||
whereClause = 'WHERE ' + conditions.join(' AND ');
|
||
}
|
||
|
||
// 查询参数
|
||
|
||
// 获取视频列表
|
||
const videos = await query(`
|
||
SELECT DISTINCT
|
||
v.id,
|
||
v.title,
|
||
v.description,
|
||
v.file_path,
|
||
v.video_url,
|
||
v.cover_image,
|
||
v.duration,
|
||
v.file_size,
|
||
v.category as category_id,
|
||
c.name as category,
|
||
v.created_at,
|
||
u.username as uploader,
|
||
COALESCE(vs.views, 0) as views,
|
||
COALESCE(vs.likes, 0) as likes
|
||
FROM videos v
|
||
LEFT JOIN users u ON v.user_id = u.id
|
||
LEFT JOIN video_stats vs ON v.id = vs.video_id
|
||
LEFT JOIN categories c ON v.category = c.id
|
||
${joins}
|
||
${whereClause}
|
||
ORDER BY v.created_at DESC
|
||
LIMIT ? OFFSET ?
|
||
`, [...params, limit, offset]);
|
||
|
||
// 获取总数
|
||
const totalResult = await queryOne(`
|
||
SELECT COUNT(DISTINCT v.id) as total
|
||
FROM videos v
|
||
${joins}
|
||
${whereClause}
|
||
`, params);
|
||
|
||
const total = totalResult.total;
|
||
const totalPages = Math.ceil(total / limit);
|
||
|
||
res.json({
|
||
code: 200,
|
||
message: '获取成功',
|
||
data: {
|
||
list: videos,
|
||
total,
|
||
page,
|
||
limit,
|
||
totalPages,
|
||
hasNext: page < totalPages,
|
||
hasPrev: page > 1
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 获取视频列表失败
|
||
res.status(500).json({
|
||
code: 500,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取视频详情
|
||
* GET /api/videos/:id
|
||
*/
|
||
router.get('/:id', optionalAuth, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const videoId = parseInt(req.params.id);
|
||
|
||
if (isNaN(videoId)) {
|
||
res.status(400).json({
|
||
code: 400,
|
||
message: '无效的视频ID'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 获取视频详情
|
||
const video = await queryOne(`
|
||
SELECT
|
||
v.id,
|
||
v.title,
|
||
v.description,
|
||
v.file_path,
|
||
v.video_url,
|
||
v.cover_image,
|
||
v.duration,
|
||
v.file_size,
|
||
v.category as category_id,
|
||
c.name as category,
|
||
v.created_at,
|
||
u.username as uploader,
|
||
COALESCE(vs.views, 0) as views,
|
||
COALESCE(vs.likes, 0) as likes
|
||
FROM videos v
|
||
LEFT JOIN users u ON v.user_id = u.id
|
||
LEFT JOIN video_stats vs ON v.id = vs.video_id
|
||
LEFT JOIN categories c ON v.category = c.id
|
||
WHERE v.id = ?
|
||
`, [videoId]);
|
||
|
||
if (!video) {
|
||
res.status(404).json({
|
||
code: 404,
|
||
message: '视频不存在'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 增加观看次数
|
||
await execute(`
|
||
INSERT INTO video_stats (video_id, views, likes, created_at)
|
||
VALUES (?, 1, 0, datetime('now'))
|
||
ON CONFLICT(video_id) DO UPDATE SET
|
||
views = views + 1
|
||
`, [videoId]);
|
||
|
||
res.json({
|
||
code: 200,
|
||
message: '获取成功',
|
||
data: video
|
||
});
|
||
} catch (error) {
|
||
// 获取视频详情失败
|
||
res.status(500).json({
|
||
code: 500,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* Multer错误处理中间件
|
||
*/
|
||
const handleMulterError = (error: any, req: Request, res: Response, next: any) => {
|
||
if (error instanceof multer.MulterError) {
|
||
// Multer错误
|
||
if (error.code === 'LIMIT_FILE_SIZE') {
|
||
return res.status(400).json({
|
||
code: 400,
|
||
message: '文件大小超过限制（最大500MB）'
|
||
});
|
||
}
|
||
return res.status(400).json({
|
||
code: 400,
|
||
message: '文件上传错误: ' + error.message
|
||
});
|
||
} else if (error) {
|
||
// 文件过滤器错误
|
||
return res.status(400).json({
|
||
code: 400,
|
||
message: error.message
|
||
});
|
||
}
|
||
next();
|
||
};
|
||
|
||
/**
|
||
* 上传视频
|
||
* POST /api/videos/upload
|
||
*/
|
||
router.post('/upload', authenticateToken, upload.single('video'), handleMulterError, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
// 视频上传请求开始
|
||
const {
|
||
title,
|
||
description,
|
||
category: rawCategory,
|
||
status = 'published',
|
||
tags = '[]',
|
||
topics = '[]',
|
||
allowComments = 'true',
|
||
allowDownload = 'false',
|
||
featured = 'false',
|
||
visibility = 'public',
|
||
thumbnailOption = 'auto',
|
||
duration: inputDuration = '0',
|
||
width = '0',
|
||
height = '0',
|
||
aspectRatio = '16:9'
|
||
} = req.body
|
||
|
||
// 确保category是数字类型
|
||
const category = parseInt(rawCategory, 10)
|
||
if (isNaN(category)) {
|
||
res.status(400).json({ error: '无效的分类ID' })
|
||
return
|
||
}
|
||
|
||
const file = req.file;
|
||
// 请求参数
|
||
|
||
// 解析专题ID列表
|
||
let topicIds: number[] = [];
|
||
if (topics) {
|
||
try {
|
||
topicIds = typeof topics === 'string' ? JSON.parse(topics) : topics;
|
||
if (!Array.isArray(topicIds)) {
|
||
topicIds = [];
|
||
}
|
||
// 确保所有ID都是数字
|
||
topicIds = topicIds.filter(id => typeof id === 'number' && !isNaN(id));
|
||
} catch (e) {
|
||
// 专题ID解析失败
|
||
topicIds = [];
|
||
}
|
||
}
|
||
|
||
if (!file) {
|
||
// 错误: 没有文件
|
||
res.status(400).json({
|
||
code: 400,
|
||
message: '请选择要上传的视频文件'
|
||
});
|
||
return;
|
||
}
|
||
|
||
if (!title) {
|
||
// 错误: 没有标题
|
||
res.status(400).json({
|
||
code: 400,
|
||
message: '视频标题不能为空'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 确保上传目录存在
|
||
const uploadDir = path.join(process.cwd(), 'uploads', 'videos');
|
||
// 上传目录
|
||
if (!fs.existsSync(uploadDir)) {
|
||
// 创建上传目录
|
||
fs.mkdirSync(uploadDir, { recursive: true });
|
||
}
|
||
|
||
// 生成唯一文件名
|
||
const fileExtension = path.extname(file.originalname);
|
||
const fileName = `${Date.now()}_${Math.random().toString(36).substring(2)}${fileExtension}`;
|
||
const filePath = path.join(uploadDir, fileName);
|
||
const relativePath = path.join('uploads', 'videos', fileName).replace(/\\/g, '/');
|
||
// 文件信息
|
||
|
||
// 保存文件
|
||
// 开始保存文件
|
||
fs.writeFileSync(filePath, file.buffer);
|
||
// 文件保存成功
|
||
|
||
// 生成视频URL（用于前端访问）
|
||
const videoUrl = `/${relativePath}`;
|
||
|
||
// 获取视频时长
|
||
// 开始获取视频时长
|
||
const duration = await getVideoDuration(filePath);
|
||
// 视频时长获取完成
|
||
|
||
// 生成视频封面 - 修复封面文件命名，确保与视频文件关联
|
||
let coverImage = '';
|
||
try {
|
||
// 使用视频文件的基础名称（不含扩展名）作为封面文件名的基础
|
||
const videoBaseName = path.basename(fileName, fileExtension);
|
||
const coverFilename = `${videoBaseName}_cover.jpg`;
|
||
const coverPath = path.join(process.cwd(), 'uploads', 'covers', coverFilename);
|
||
|
||
// 确保封面目录存在
|
||
const coverDir = path.dirname(coverPath);
|
||
if (!fs.existsSync(coverDir)) {
|
||
fs.mkdirSync(coverDir, { recursive: true });
|
||
}
|
||
|
||
// 开始生成视频封面
|
||
const coverGenerated = await generateVideoCover(filePath, coverPath);
|
||
|
||
if (coverGenerated) {
|
||
coverImage = `/uploads/covers/${coverFilename}`;
|
||
// 视频封面生成成功
|
||
} else {
|
||
// 视频封面生成失败，使用默认封面
|
||
}
|
||
} catch (error) {
|
||
// 生成视频封面时出错
|
||
}
|
||
|
||
// 插入视频记录
|
||
// 开始插入数据库记录
|
||
const result = await execute(`
|
||
INSERT INTO videos (
|
||
title, description, video_url, file_path, file_size, cover_image, duration, category, user_id, created_at
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
|
||
`, [title, description || '', videoUrl, relativePath, file.size, coverImage, duration, category || '', req.user!.id]);
|
||
// 数据库插入成功
|
||
|
||
const videoId = result.lastID;
|
||
|
||
// 关联专题
|
||
if (topicIds.length > 0) {
|
||
// 开始关联专题
|
||
for (const topicId of topicIds) {
|
||
try {
|
||
// 验证专题是否存在
|
||
const topicExists = await queryOne('SELECT id FROM topics WHERE id = ? AND status = "active"', [topicId]);
|
||
if (topicExists) {
|
||
await execute(`
|
||
INSERT INTO video_topics (video_id, topic_id, created_at)
|
||
VALUES (?, ?, datetime('now'))
|
||
`, [videoId, topicId]);
|
||
// 专题关联成功
|
||
} else {
|
||
// 专题不存在或已禁用
|
||
}
|
||
} catch (error) {
|
||
// 专题关联失败
|
||
}
|
||
}
|
||
}
|
||
|
||
res.status(201).json({
|
||
code: 201,
|
||
message: '视频上传成功',
|
||
data: {
|
||
id: videoId,
|
||
title,
|
||
description,
|
||
category,
|
||
file_path: relativePath,
|
||
file_size: file.size,
|
||
cover_image: coverImage,
|
||
topics: topicIds
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 视频上传失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误',
|
||
error: error instanceof Error ? error.message : 'Unknown error'
|
||
});
|
||
}
|
||
});
|
||
|
||
|
||
|
||
/**
|
||
* 批量删除视频 (POST方法)
|
||
* POST /api/videos/batch/delete
|
||
*/
|
||
router.post('/batch/delete', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const { videoIds } = req.body;
|
||
|
||
if (!videoIds || !Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供要删除的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 验证所有ID都是有效数字
|
||
const validIds = videoIds.filter(id => typeof id === 'number' && !isNaN(id));
|
||
if (validIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '无效的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查用户权限（只能删除自己上传的视频，或管理员可以删除所有视频）
|
||
const placeholders = validIds.map(() => '?').join(',');
|
||
const videos = await query(`
|
||
SELECT id, file_path, cover_image, user_id FROM videos
|
||
WHERE id IN (${placeholders})
|
||
`, validIds);
|
||
|
||
if (videos.length === 0) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '未找到要删除的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查权限
|
||
const unauthorizedVideos = videos.filter(video =>
|
||
video.user_id !== req.user!.id && req.user!.role !== 'admin'
|
||
);
|
||
|
||
if (unauthorizedVideos.length > 0) {
|
||
res.status(403).json({
|
||
success: false,
|
||
message: '没有权限删除部分视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 删除相关文件
|
||
for (const video of videos) {
|
||
try {
|
||
// 删除视频文件
|
||
if (video.file_path) {
|
||
const videoPath = path.join(process.cwd(), video.file_path);
|
||
if (fs.existsSync(videoPath)) {
|
||
fs.unlinkSync(videoPath);
|
||
}
|
||
}
|
||
|
||
// 删除封面文件
|
||
if (video.cover_image) {
|
||
const coverPath = path.join(process.cwd(), video.cover_image.replace(/^\//, ''));
|
||
if (fs.existsSync(coverPath)) {
|
||
fs.unlinkSync(coverPath);
|
||
}
|
||
}
|
||
} catch (fileError) {
|
||
// 删除文件失败
|
||
}
|
||
}
|
||
|
||
// 删除数据库记录
|
||
const foundIds = videos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
// 删除相关统计数据
|
||
await execute(`DELETE FROM video_stats WHERE video_id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
// 删除专题关联
|
||
await execute(`DELETE FROM video_topics WHERE video_id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
// 删除视频记录
|
||
await execute(`DELETE FROM videos WHERE id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功删除 ${foundIds.length} 个视频`,
|
||
data: {
|
||
deletedCount: foundIds.length,
|
||
deletedIds: foundIds
|
||
}
|
||
});
|
||
} catch (error) {
|
||
console.error('批量删除视频失败:', error);
|
||
console.error('错误堆栈:', error.stack);
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误',
|
||
error: process.env.NODE_ENV === 'development' ? error.message : undefined
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 批量更新视频状态
|
||
* PUT /api/videos/batch/status
|
||
*/
|
||
router.put('/batch/status', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const { videoIds, status } = req.body;
|
||
|
||
// 参数验证
|
||
if (!Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供有效的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
if (!status || !['published', 'draft', 'blocked'].includes(status)) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供有效的状态值（published/draft/blocked）'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 权限检查 - 只有管理员可以批量操作
|
||
const user = (req as any).user;
|
||
if (!user || user.role !== 'admin') {
|
||
res.status(403).json({
|
||
success: false,
|
||
message: '权限不足，只有管理员可以批量操作视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 验证所有视频ID都是有效数字
|
||
const validIds = videoIds.filter(id => !isNaN(parseInt(id))).map(id => parseInt(id));
|
||
if (validIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '没有有效的视频ID'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查视频是否存在
|
||
const placeholders = validIds.map(() => '?').join(',');
|
||
const existingVideos = await query(`SELECT id FROM videos WHERE id IN (${placeholders})`, validIds);
|
||
|
||
if (existingVideos.length === 0) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '没有找到指定的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 批量更新状态
|
||
const foundIds = existingVideos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
await execute(`
|
||
UPDATE videos
|
||
SET status = ?, updated_at = datetime('now')
|
||
WHERE id IN (${foundPlaceholders})
|
||
`, [status, ...foundIds]);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功更新 ${foundIds.length} 个视频的状态为 ${status}`,
|
||
data: {
|
||
updatedCount: foundIds.length,
|
||
updatedIds: foundIds,
|
||
status: status
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 批量更新视频状态失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 批量删除视频
|
||
* DELETE /api/videos/batch
|
||
*/
|
||
router.delete('/batch', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
console.log('批量删除视频开始，请求体:', req.body);
|
||
console.log('用户信息:', req.user);
|
||
try {
|
||
const { videoIds } = req.body;
|
||
|
||
// 参数验证
|
||
if (!Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供有效的视频ID列表'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 权限检查 - 只有管理员可以批量删除
|
||
const user = (req as any).user;
|
||
if (!user || user.role !== 'admin') {
|
||
res.status(403).json({
|
||
success: false,
|
||
message: '权限不足，只有管理员可以批量删除视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 验证所有视频ID都是有效数字
|
||
const validIds = videoIds.filter(id => !isNaN(parseInt(id))).map(id => parseInt(id));
|
||
if (validIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '没有有效的视频ID'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 获取要删除的视频信息
|
||
const placeholders = validIds.map(() => '?').join(',');
|
||
const videos = await query(`
|
||
SELECT id, file_path, cover_image
|
||
FROM videos
|
||
WHERE id IN (${placeholders})
|
||
`, validIds);
|
||
|
||
if (videos.length === 0) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '没有找到指定的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 删除物理文件
|
||
for (const video of videos) {
|
||
try {
|
||
// 删除视频文件
|
||
if (video.file_path && fs.existsSync(video.file_path)) {
|
||
fs.unlinkSync(video.file_path);
|
||
// 视频文件删除成功
|
||
}
|
||
|
||
// 删除缩略图文件
|
||
if (video.cover_image) {
|
||
const thumbnailPath = path.join(process.cwd(), 'public', video.cover_image);
|
||
if (fs.existsSync(thumbnailPath)) {
|
||
fs.unlinkSync(thumbnailPath);
|
||
// 缩略图删除成功
|
||
}
|
||
}
|
||
} catch (fileError) {
|
||
console.error(`删除文件失败 (video_id: ${video.id}):`, fileError);
|
||
}
|
||
}
|
||
|
||
// 删除数据库记录
|
||
const foundIds = videos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
// 删除相关统计数据
|
||
await execute(`DELETE FROM video_stats WHERE video_id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
// 删除专题关联
|
||
await execute(`DELETE FROM video_topics WHERE video_id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
// 删除视频记录
|
||
await execute(`DELETE FROM videos WHERE id IN (${foundPlaceholders})`, foundIds);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功删除 ${foundIds.length} 个视频`,
|
||
data: {
|
||
deletedCount: foundIds.length,
|
||
deletedIds: foundIds
|
||
}
|
||
});
|
||
} catch (error) {
|
||
console.error('批量删除视频失败:', error);
|
||
console.error('错误堆栈:', error.stack);
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误',
|
||
error: process.env.NODE_ENV === 'development' ? error.message : undefined
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 批量更新视频分类
|
||
* PUT /api/videos/batch/category
|
||
*/
|
||
router.put('/batch/category', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const { videoIds, category } = req.body;
|
||
|
||
// 参数验证
|
||
if (!Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请选择要修改的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
if (!category || typeof category !== 'string') {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供有效的分类名称'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 权限检查：确保用户有权限修改这些视频
|
||
const user = (req as any).user;
|
||
if (!user) {
|
||
res.status(401).json({
|
||
success: false,
|
||
message: '未授权访问'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查视频是否存在且用户有权限修改
|
||
const placeholders = videoIds.map(() => '?').join(',');
|
||
const videos = await query(`
|
||
SELECT id FROM videos
|
||
WHERE id IN (${placeholders}) AND user_id = ?
|
||
`, [...videoIds, user.id]);
|
||
|
||
if (videos.length === 0) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '未找到可修改的视频或无权限修改'
|
||
});
|
||
return;
|
||
}
|
||
|
||
const foundIds = videos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
// 批量更新分类
|
||
await execute(`
|
||
UPDATE videos
|
||
SET category = ?, updated_at = datetime('now')
|
||
WHERE id IN (${foundPlaceholders})
|
||
`, [category, ...foundIds]);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功修改 ${foundIds.length} 个视频的分类`,
|
||
data: {
|
||
updatedCount: foundIds.length,
|
||
updatedIds: foundIds,
|
||
category
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 批量修改分类失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 批量更新视频状态
|
||
* PUT /api/videos/batch/status
|
||
*/
|
||
router.put('/batch/status', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const { videoIds, status } = req.body;
|
||
|
||
// 参数验证
|
||
if (!Array.isArray(videoIds) || videoIds.length === 0) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请选择要修改的视频'
|
||
});
|
||
return;
|
||
}
|
||
|
||
if (!status || !['published', 'draft', 'blocked'].includes(status)) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '请提供有效的状态值（published/draft/blocked）'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 权限检查
|
||
const user = (req as any).user;
|
||
if (!user) {
|
||
res.status(401).json({
|
||
success: false,
|
||
message: '未授权访问'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查视频是否存在且用户有权限修改
|
||
const placeholders = videoIds.map(() => '?').join(',');
|
||
const videos = await query(`
|
||
SELECT id FROM videos
|
||
WHERE id IN (${placeholders}) AND user_id = ?
|
||
`, [...videoIds, user.id]);
|
||
|
||
if (videos.length === 0) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '未找到可修改的视频或无权限修改'
|
||
});
|
||
return;
|
||
}
|
||
|
||
const foundIds = videos.map(v => v.id);
|
||
const foundPlaceholders = foundIds.map(() => '?').join(',');
|
||
|
||
// 批量更新状态
|
||
await execute(`
|
||
UPDATE videos
|
||
SET status = ?, updated_at = datetime('now')
|
||
WHERE id IN (${foundPlaceholders})
|
||
`, [status, ...foundIds]);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: `成功修改 ${foundIds.length} 个视频的状态`,
|
||
data: {
|
||
updatedCount: foundIds.length,
|
||
updatedIds: foundIds,
|
||
status
|
||
}
|
||
});
|
||
} catch (error) {
|
||
// 批量修改状态失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 点赞视频
|
||
* POST /api/videos/:id/like
|
||
*/
|
||
router.post('/:id/like', authenticateToken, async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const videoId = parseInt(req.params.id);
|
||
|
||
if (isNaN(videoId)) {
|
||
res.status(400).json({
|
||
success: false,
|
||
message: '无效的视频ID'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查视频是否存在
|
||
const video = await queryOne('SELECT id FROM videos WHERE id = ?', [videoId]);
|
||
if (!video) {
|
||
res.status(404).json({
|
||
success: false,
|
||
message: '视频不存在'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 增加点赞数
|
||
await execute(`
|
||
INSERT INTO video_stats (video_id, views, likes, created_at)
|
||
VALUES (?, 0, 1, datetime('now'))
|
||
ON CONFLICT(video_id) DO UPDATE SET
|
||
likes = likes + 1
|
||
`, [videoId]);
|
||
|
||
res.json({
|
||
success: true,
|
||
message: '点赞成功'
|
||
});
|
||
} catch (error) {
|
||
// 点赞失败
|
||
res.status(500).json({
|
||
success: false,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取专题内其他视频
|
||
* GET /api/videos/:id/topic-videos
|
||
*/
|
||
router.get('/:id/topic-videos', async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const videoId = parseInt(req.params.id);
|
||
|
||
if (isNaN(videoId)) {
|
||
res.status(400).json({
|
||
code: 400,
|
||
message: '无效的视频ID'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 检查视频是否存在
|
||
const video = await queryOne('SELECT id FROM videos WHERE id = ?', [videoId]);
|
||
if (!video) {
|
||
res.status(404).json({
|
||
code: 404,
|
||
message: '视频不存在'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 获取该视频所属的专题
|
||
const videoTopics = await query(`
|
||
SELECT topic_id FROM video_topics WHERE video_id = ?
|
||
`, [videoId]);
|
||
|
||
if (videoTopics.length === 0) {
|
||
res.json({
|
||
code: 200,
|
||
message: '获取成功',
|
||
data: []
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 获取同专题的其他视频
|
||
const topicIds = videoTopics.map(vt => vt.topic_id);
|
||
const placeholders = topicIds.map(() => '?').join(',');
|
||
|
||
const topicVideos = await query(`
|
||
SELECT DISTINCT v.id, v.title, v.description, v.video_url, v.thumbnail,
|
||
v.duration, v.created_at,
|
||
COALESCE(vs.views, 0) as views,
|
||
COALESCE(vs.likes, 0) as likes
|
||
FROM videos v
|
||
LEFT JOIN video_stats vs ON v.id = vs.video_id
|
||
INNER JOIN video_topics vt ON v.id = vt.video_id
|
||
WHERE vt.topic_id IN (${placeholders})
|
||
AND v.id != ?
|
||
AND v.status = 'active'
|
||
ORDER BY v.created_at DESC
|
||
LIMIT 10
|
||
`, [...topicIds, videoId]);
|
||
|
||
res.json({
|
||
code: 200,
|
||
message: '获取成功',
|
||
data: topicVideos
|
||
});
|
||
} catch (error) {
|
||
// 获取专题视频失败
|
||
res.status(500).json({
|
||
code: 500,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
/**
|
||
* 获取视频封面帧
|
||
* GET /api/videos/:id/frame
|
||
*/
|
||
router.get('/:id/frame', async (req: Request, res: Response): Promise<void> => {
|
||
try {
|
||
const videoId = parseInt(req.params.id);
|
||
const timestamp = parseFloat(req.query.t as string) || 1; // 默认第1秒
|
||
|
||
if (isNaN(videoId)) {
|
||
res.status(400).json({
|
||
code: 400,
|
||
message: '无效的视频ID'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 获取视频信息
|
||
const video = await queryOne('SELECT file_path FROM videos WHERE id = ?', [videoId]);
|
||
if (!video) {
|
||
res.status(404).json({
|
||
code: 404,
|
||
message: '视频不存在'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 构建视频文件完整路径
|
||
const videoPath = path.join(process.cwd(), video.file_path);
|
||
|
||
// 检查视频文件是否存在
|
||
if (!fs.existsSync(videoPath)) {
|
||
res.status(404).json({
|
||
code: 404,
|
||
message: '视频文件不存在'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 提取视频帧
|
||
const frameBuffer = await extractVideoFrame(videoPath, timestamp);
|
||
|
||
if (!frameBuffer) {
|
||
res.status(500).json({
|
||
code: 500,
|
||
message: '视频帧提取失败'
|
||
});
|
||
return;
|
||
}
|
||
|
||
// 设置响应头并返回图片
|
||
res.set({
|
||
'Content-Type': 'image/jpeg',
|
||
'Content-Length': frameBuffer.length.toString(),
|
||
'Cache-Control': 'public, max-age=86400' // 缓存1天
|
||
});
|
||
|
||
res.send(frameBuffer);
|
||
} catch (error) {
|
||
// 获取视频封面帧失败
|
||
res.status(500).json({
|
||
code: 500,
|
||
message: '服务器内部错误'
|
||
});
|
||
}
|
||
});
|
||
|
||
export default router; |