server-mysql.js

// server-mysql.js - 使用MySQL数据库的校园圈后端服务器 (Hugging Face Spaces版本)
require('dotenv').config();
const express = require('express');
const cors = require('cors');
const helmet = require('helmet');
const multer = require('multer');
const path = require('path');
const fs = require('fs');
const bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const { body, validationResult } = require('express-validator');
const winston = require('winston');
const { pool, initDatabase } = require('./db-config');
const { r2Storage } = require('./r2-storage');

// JWT密钥
const JWT_SECRET = process.env.JWT_SECRET || 'campus-circle-secret-key-2024';

// 服务器端口 - Hugging Face Spaces 使用 7860
const PORT = process.env.PORT || 7860;

// 创建日志记录器
const logger = winston.createLogger({
  level: 'info',
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.json()
  ),
  transports: [
    new winston.transports.Console(),
    new winston.transports.File({ filename: 'server.log' })
  ]
});

const app = express();

// 确保uploads目录存在 - 使用Hugging Face Spaces持久化目录
const uploadsDir = process.env.HF_SPACE ? 
  path.join('/tmp', 'uploads') :  // Hugging Face Spaces临时目录
  path.join(__dirname, 'uploads'); // 本地开发目录

if (!fs.existsSync(uploadsDir)) {
  fs.mkdirSync(uploadsDir, { recursive: true });
  console.log('📁 创建uploads目录:', uploadsDir);
}

// 创建静态文件目录（用于持久化存储）
const staticDir = path.join(__dirname, 'static');
if (!fs.existsSync(staticDir)) {
  fs.mkdirSync(staticDir, { recursive: true });
  console.log('📁 创建static目录:', staticDir);
}

// 添加调试信息 - 列出现有文件
try {
  const files = fs.readdirSync(uploadsDir);
  console.log('📂 uploads目录现有文件:', files);
} catch (error) {
  console.log('📂 无法读取uploads目录:', error.message);
}

// 配置multer用于文件上传
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    // 使用/tmp目录，Hugging Face Spaces中有写权限
    const uploadPath = uploadsDir;
    
    // 确保目录存在
    if (!fs.existsSync(uploadPath)) {
      try {
        fs.mkdirSync(uploadPath, { recursive: true });
        console.log('📁 创建上传目录:', uploadPath);
      } catch (error) {
        console.error('❌ 创建上传目录失败:', error);
      }
    }
    
    cb(null, uploadPath);
  },
  filename: function (req, file, cb) {
    // 生成唯一文件名
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    const ext = path.extname(file.originalname);
    const filename = file.fieldname + '-' + uniqueSuffix + ext;
    console.log('📸 生成文件名:', filename);
    cb(null, filename);
  }
});

const upload = multer({ 
  storage: storage,
  limits: {
    fileSize: 2 * 1024 * 1024 // 2MB限制，减少base64大小
  },
  fileFilter: function (req, file, cb) {
    console.log('📸 文件过滤检查:', file.mimetype);
    // 只允许图片文件
    if (file.mimetype.startsWith('image/')) {
      cb(null, true);
    } else {
      cb(new Error('只允许上传图片文件'));
    }
  }
});

// 中间件
app.use(helmet());
app.use(cors());
app.use(express.json({ limit: '10mb' }));
app.use(express.urlencoded({ extended: true, limit: '10mb' }));

// 静态文件服务 - 配置CORS和缓存
app.use('/uploads', express.static(uploadsDir, {
  setHeaders: (res, path, stat) => {
    res.set('Access-Control-Allow-Origin', '*');
    res.set('Cross-Origin-Resource-Policy', 'cross-origin');
  }
}));

app.use('/static', express.static(staticDir, {
  setHeaders: (res, path, stat) => {
    res.set('Access-Control-Allow-Origin', '*');
    res.set('Cross-Origin-Resource-Policy', 'cross-origin');
  }
}));

app.use('/images', express.static('images', {
  setHeaders: (res, path, stat) => {
    res.set('Access-Control-Allow-Origin', '*');
    res.set('Cross-Origin-Resource-Policy', 'cross-origin');
  }
}));

// JWT验证中间件
const authenticateToken = (req, res, next) => {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];

  if (!token) {
    return res.status(401).json({ error: '访问令牌缺失' });
  }

  jwt.verify(token, JWT_SECRET, (err, user) => {
    if (err) {
      return res.status(403).json({ error: '令牌无效' });
    }
    req.user = user;
    next();
  });
};

// 图片上传接口 - 文件存储方案
app.post('/api/upload', authenticateToken, upload.single('file'), (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ 
        success: false, 
        error: '没有上传文件' 
      });
    }

    // 检查文件大小
    if (req.file.size > 5 * 1024 * 1024) { // 5MB限制
      // 删除上传的文件
      if (fs.existsSync(req.file.path)) {
        fs.unlinkSync(req.file.path);
      }
      return res.status(400).json({ 
        success: false, 
        error: '图片文件过大，请选择小于5MB的图片' 
      });
    }

    // 生成文件URL
    const fileUrl = `/static/${req.file.filename}`;
    
    logger.info(`图片上传成功: ${req.file.originalname}, 大小: ${req.file.size}字节, 路径: ${req.file.path}`);
    
    res.json({
      success: true,
      message: '文件上传成功',
      url: fileUrl,
      filename: req.file.filename,
      originalname: req.file.originalname,
      size: req.file.size
    });
  } catch (error) {
    logger.error('文件上传失败:', error);
    
    // 清理上传的文件
    if (req.file && req.file.path && fs.existsSync(req.file.path)) {
      try {
        fs.unlinkSync(req.file.path);
      } catch (cleanupError) {
        logger.error('清理上传文件失败:', cleanupError);
      }
    }
    
    res.status(500).json({ 
      success: false, 
      error: '文件上传失败: ' + error.message 
    });
  }
});

// 健康检查端点
app.get('/api/health', (req, res) => {
  res.status(200).json({ 
    status: 'ok', 
    timestamp: new Date().toISOString(),
    service: 'campusloop-backend',
    port: PORT,
    platform: 'Hugging Face Spaces'
  });
});

// 获取平台统计数据（注册用户数、动态数、活跃聊天数）
app.get('/api/stats', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      // 查询注册用户总数
      const [usersResult] = await connection.query(
        'SELECT COUNT(*) as count FROM users'
      );
      const totalUsers = usersResult[0].count || 0;
      
      // 查询动态总数
      const [postsResult] = await connection.query(
        'SELECT COUNT(*) as count FROM posts'
      );
      const totalPosts = postsResult[0].count || 0;
      
      // 查询活跃聊天数（最近7天有发送消息的用户数）
      const [chatsResult] = await connection.query(
        `SELECT COUNT(DISTINCT sender_id) as count FROM chat_messages 
         WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)`
      );
      const activeChats = chatsResult[0].count || 0;
      
      res.json({
        success: true,
        stats: {
          totalUsers,
          totalPosts,
          activeChats
        }
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('获取统计数据失败:', error);
    logger.error('获取统计数据失败:', error);
    // 返回默认值，避免前端显示错误
    res.json({
      success: true,
      stats: {
        totalUsers: 0,
        totalPosts: 0,
        activeChats: 0
      }
    });
  }
});

// 根路径
app.get('/', (req, res) => {
  res.json({
    message: '🎓 CampusLoop Backend API',
    version: '1.0.0',
    platform: 'Hugging Face Spaces',
    endpoints: {
      health: '/api/health',
      auth: {
        register: 'POST /api/auth/register',
        login: 'POST /api/auth/login'
      },
      posts: {
        list: 'GET /api/posts',
        create: 'POST /api/posts',
        comments: 'GET /api/posts/:id/comments'
      },
      forum: {
        posts: 'GET /api/forum/posts',
        create: 'POST /api/forum/posts'
      },
      activities: {
        list: 'GET /api/activities',
        create: 'POST /api/activities'
      }
    }
  });
});

// 用户注册接口
app.post('/api/auth/register', [
  body('username').isLength({ min: 3 }).withMessage('用户名至少3个字符'),
  body('email').isEmail().withMessage('请输入有效的邮箱地址'),
  body('password').isLength({ min: 6 }).withMessage('密码至少6个字符')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { username, email, password } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查用户名和邮箱是否已存在
      const [existingUsers] = await connection.query(
        'SELECT id FROM users WHERE username = ? OR email = ?',
        [username, email]
      );

      if (existingUsers.length > 0) {
        return res.status(400).json({ error: '用户名或邮箱已存在' });
      }

      // 加密密码
      const hashedPassword = await bcrypt.hash(password, 10);

      // 插入新用户（设置默认头像）
      const [result] = await connection.query(
        'INSERT INTO users (username, email, password, avatar) VALUES (?, ?, ?, ?)',
        [username, email, hashedPassword, '/images/default-avatar.svg']
      );

      const userId = result.insertId;
      const token = jwt.sign({ userId, username }, JWT_SECRET, { expiresIn: '24h' });

      res.status(201).json({
        message: '注册成功',
        token,
        user: { id: userId, username, email }
      });

    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('注册失败:', error);
    res.status(500).json({ error: '注册失败，请稍后重试' });
  }
});

// 用户登录接口
app.post('/api/auth/login', [
  body('username').notEmpty().withMessage('用户名不能为空'),
  body('password').notEmpty().withMessage('密码不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { username, password } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 尝试查询包含role字段，如果失败则查询不包含role字段
      let users;
      try {
        [users] = await connection.query(
          'SELECT id, username, email, password, role FROM users WHERE username = ? OR email = ?',
          [username, username]
        );
      } catch (error) {
        if (error.code === 'ER_BAD_FIELD_ERROR') {
          // role字段不存在，使用不带role的查询
          [users] = await connection.query(
            'SELECT id, username, email, password FROM users WHERE username = ? OR email = ?',
            [username, username]
          );
        } else {
          throw error;
        }
      }

      if (users.length === 0) {
        return res.status(401).json({ error: '用户名或密码错误' });
      }

      const user = users[0];
      const isValidPassword = await bcrypt.compare(password, user.password);

      if (!isValidPassword) {
        return res.status(401).json({ error: '用户名或密码错误' });
      }

      const token = jwt.sign(
        { userId: user.id, username: user.username },
        JWT_SECRET,
        { expiresIn: '24h' }
      );

      res.json({
        message: '登录成功',
        token,
        user: { 
          id: user.id, 
          username: user.username, 
          email: user.email,
          role: user.role || 'user'
        }
      });

    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('登录失败:', error);
    res.status(500).json({ error: '登录失败，请稍后重试' });
  }
});

// 获取个人资料接口
app.get('/api/profile', authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      // 查询用户信息，包含所有字段
      const [users] = await connection.query(
        'SELECT id, username, email, avatar, bio, role, phone, major, grade, gender, student_id, created_at FROM users WHERE id = ?',
        [req.user.userId]
      );

      if (users.length === 0) {
        return res.status(404).json({ error: '用户不存在' });
      }

      const user = users[0];
      // 不返回密码字段
      delete user.password;
      res.json({ success: true, user });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取用户信息失败:', error);
    res.status(500).json({ error: '获取用户信息失败' });
  }
});

// 获取主页成绩信息接口
app.get('/api/grades/home', authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      const [grades] = await connection.query(
        'SELECT * FROM grades WHERE user_id = ? ORDER BY created_at DESC LIMIT 5',
        [req.user.userId]
      );

      res.json({ grades });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取成绩失败:', error);
    res.status(500).json({ error: '获取成绩失败' });
  }
});

// 获取校园地图接口
app.get('/api/map', (req, res) => {
  // 返回校园地图数据
  const mapData = {
    id: 1,
    name: '校园地图',
    imageUrl: '/images/campus-map.jpg',
    locations: [
      { id: 1, name: '图书馆', x: 100, y: 150, type: 'library' },
      { id: 2, name: '教学楼A', x: 200, y: 100, type: 'building' },
      { id: 3, name: '食堂', x: 150, y: 200, type: 'restaurant' },
      { id: 4, name: '体育馆', x: 300, y: 180, type: 'gym' }
    ]
  };
  
  res.json({ map: mapData });
});

// 获取动态接口
app.get('/api/posts', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      const [posts] = await connection.query(`
        SELECT p.*, u.username, u.avatar 
        FROM posts p 
        JOIN users u ON p.user_id = u.id 
        ORDER BY p.created_at DESC 
        LIMIT 50
      `);

      res.json({ success: true, posts });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('获取动态失败:', error);
    logger.error('获取动态失败:', error);
    res.status(500).json({ success: false, error: '获取动态失败' });
  }
});

// 发布动态接口（支持图片上传）
// 优先使用 Cloudflare R2 存储，如未配置则使用 Base64 存数据库
app.post('/api/posts', authenticateToken, upload.single('image'), async (req, res) => {
  const { content } = req.body;
  
  // 验证：至少要有内容或图片
  if (!content && !req.file) {
    return res.status(400).json({ error: '请输入内容或上传图片' });
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      let imageUrl = null;
      
      // 如果有上传图片
      if (req.file) {
        const fileBuffer = fs.readFileSync(req.file.path);
        
        // 优先使用 R2 存储
        if (r2Storage.isConfigured()) {
          try {
            logger.info('使用 Cloudflare R2 存储图片...');
            const uploadResult = await r2Storage.uploadFile(
              fileBuffer,
              req.file.originalname,
              req.file.mimetype
            );
            imageUrl = uploadResult.url;
            logger.info(`R2 上传成功: ${imageUrl}`);
          } catch (r2Error) {
            logger.error('R2 上传失败，回退到 Base64:', r2Error.message);
            // R2 失败时回退到 Base64
            imageUrl = `data:${req.file.mimetype};base64,${fileBuffer.toString('base64')}`;
          }
        } else {
          // 未配置 R2，使用 Base64 存储
          logger.info('R2 未配置，使用 Base64 存储图片');
          imageUrl = `data:${req.file.mimetype};base64,${fileBuffer.toString('base64')}`;
        }
        
        // 删除临时文件
        try {
          fs.unlinkSync(req.file.path);
        } catch (unlinkError) {
          logger.warn('删除临时文件失败:', unlinkError.message);
        }
      }
      
      const [result] = await connection.query(
        'INSERT INTO posts (user_id, content, image) VALUES (?, ?, ?)',
        [req.user.userId, content || '', imageUrl]
      );

      res.status(201).json({
        success: true,
        message: '动态发布成功',
        postId: result.insertId,
        imageStorage: r2Storage.isConfigured() ? 'r2' : 'base64'
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('发布动态失败:', error);
    logger.error('发布动态失败:', error);
    res.status(500).json({ error: '发布动态失败' });
  }
});

// 编辑动态接口
app.put('/api/posts/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { content } = req.body;

  if (!content || !content.trim()) {
    return res.status(400).json({ error: '动态内容不能为空' });
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查动态是否存在以及当前用户是否有权限编辑
      const [posts] = await connection.execute(
        'SELECT * FROM posts WHERE id = ? AND user_id = ?',
        [id, req.user.userId]
      );

      if (posts.length === 0) {
        return res.status(404).json({ error: '动态不存在或无权限编辑' });
      }

      // 执行更新操作
      await connection.execute(
        'UPDATE posts SET content = ? WHERE id = ?',
        [content.trim(), id]
      );

      res.json({ success: true, message: '动态编辑成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('编辑动态失败:', error);
    res.status(500).json({ error: '编辑动态失败' });
  }
});

// 删除动态接口（支持管理员删除任何动态）
app.delete('/api/posts/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 先获取当前用户的角色
      const [users] = await connection.execute(
        'SELECT role FROM users WHERE id = ?',
        [req.user.userId]
      );
      
      const isAdmin = users.length > 0 && users[0].role === 'admin';
      
      // 检查动态是否存在
      const [posts] = await connection.execute(
        'SELECT * FROM posts WHERE id = ?',
        [id]
      );

      if (posts.length === 0) {
        return res.status(404).json({ error: '动态不存在' });
      }

      // 检查权限：只有动态作者或管理员可以删除
      if (posts[0].user_id !== req.user.userId && !isAdmin) {
        return res.status(403).json({ error: '无权限删除此动态' });
      }

      // 删除动态（会级联删除相关的评论和点赞）
      await connection.execute('DELETE FROM posts WHERE id = ?', [id]);

      logger.info(`动态删除成功: postId=${id}, 操作者=${req.user.username}, 是否管理员=${isAdmin}`);
      res.json({ success: true, message: '动态删除成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('删除动态失败:', error);
    res.status(500).json({ error: '删除动态失败' });
  }
});

// 点赞动态接口
app.post('/api/posts/:id/like', authenticateToken, async (req, res) => {
  const { id } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查是否已经点赞
      const [existingLikes] = await connection.execute(
        'SELECT * FROM likes WHERE post_id = ? AND user_id = ?',
        [id, req.user.userId]
      );

      if (existingLikes.length > 0) {
        // 取消点赞
        await connection.execute(
          'DELETE FROM likes WHERE post_id = ? AND user_id = ?',
          [id, req.user.userId]
        );

        res.json({ success: true, message: '取消点赞成功', liked: false });
      } else {
        // 添加点赞
        await connection.execute(
          'INSERT INTO likes (post_id, user_id) VALUES (?, ?)',
          [id, req.user.userId]
        );

        res.json({ success: true, message: '点赞成功', liked: true });
      }
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('点赞失败:', error);
    res.status(500).json({ error: '点赞失败' });
  }
});

// 获取动态评论接口
app.get('/api/posts/:id/comments', async (req, res) => {
  const { id } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      const [comments] = await connection.execute(`
        SELECT c.*, u.username, u.avatar 
        FROM comments c 
        JOIN users u ON c.user_id = u.id 
        WHERE c.post_id = ? 
        ORDER BY c.created_at ASC
      `, [id]);

      res.json({ success: true, comments });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取评论失败:', error);
    res.status(500).json({ error: '获取评论失败' });
  }
});

// 发表评论接口
app.post('/api/posts/:id/comments', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { content } = req.body;

  if (!content || !content.trim()) {
    return res.status(400).json({ error: '评论内容不能为空' });
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      const [result] = await connection.execute(
        'INSERT INTO comments (post_id, user_id, content) VALUES (?, ?, ?)',
        [id, req.user.userId, content.trim()]
      );

      res.json({
        success: true,
        comment: {
          id: result.insertId,
          post_id: id,
          user_id: req.user.userId,
          username: req.user.username,
          content: content.trim(),
          created_at: new Date().toISOString()
        }
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('发表评论失败:', error);
    res.status(500).json({ error: '发表评论失败' });
  }
});

// 删除评论接口
app.delete('/api/posts/:postId/comments/:commentId', authenticateToken, async (req, res) => {
  const { postId, commentId } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查评论是否存在以及是否属于当前用户
      const [comments] = await connection.execute(
        'SELECT * FROM comments WHERE id = ? AND post_id = ? AND user_id = ?',
        [commentId, postId, req.user.userId]
      );

      if (comments.length === 0) {
        return res.status(404).json({ error: '评论不存在或无权限删除' });
      }

      // 删除评论
      await connection.execute('DELETE FROM comments WHERE id = ?', [commentId]);

      res.json({ success: true, message: '评论删除成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('删除评论失败:', error);
    res.status(500).json({ error: '删除评论失败' });
  }
});

// 获取论坛帖子接口
app.get('/api/forum/posts', async (req, res) => {
  const { topic } = req.query;
  
  try {
    const connection = await pool.getConnection();
    
    try {
      let query = `
        SELECT fp.*, u.username, u.avatar 
        FROM forum_posts fp 
        JOIN users u ON fp.user_id = u.id
      `;
      let params = [];

      if (topic) {
        query += ' WHERE fp.topic = ?';
        params.push(topic);
      }

      query += ' ORDER BY fp.created_at DESC LIMIT 50';

      const [posts] = await connection.query(query, params);
      res.json({ posts });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取论坛帖子失败:', error);
    res.status(500).json({ error: '获取论坛帖子失败' });
  }
});

// 获取课程表接口
app.get('/api/schedule', authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      const [schedules] = await connection.query(
        'SELECT * FROM schedules WHERE user_id = ? ORDER BY weekday, start_time',
        [req.user.userId]
      );

      res.json({ success: true, schedules });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取课程表失败:', error);
    res.status(500).json({ success: false, error: '获取课程表失败' });
  }
});

// 添加课程接口
app.post('/api/schedule', authenticateToken, [
  body('course_name').notEmpty().withMessage('课程名称不能为空'),
  body('teacher').notEmpty().withMessage('教师姓名不能为空'),
  body('weekday').isInt({ min: 1, max: 7 }).withMessage('星期必须是1-7之间的数字')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { course_name, teacher, weekday, start_time, end_time, classroom } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      const [result] = await connection.query(
        'INSERT INTO schedules (user_id, course_name, teacher, weekday, start_time, end_time, classroom) VALUES (?, ?, ?, ?, ?, ?, ?)',
        [req.user.userId, course_name, teacher, weekday, start_time, end_time, classroom || '']
      );

      res.status(201).json({
        message: '课程添加成功',
        scheduleId: result.insertId
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('添加课程失败:', error);
    res.status(500).json({ error: '添加课程失败' });
  }
});

// 初始化测试课程数据接口
app.post('/api/schedule/init-test-data', authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查是否已有课程
      const [existing] = await connection.query(
        'SELECT COUNT(*) as count FROM schedules WHERE user_id = ?',
        [req.user.userId]
      );
      
      if (existing[0].count > 0) {
        return res.json({ success: true, message: '已有课程数据，无需初始化', count: existing[0].count });
      }
      
      // 测试课程数据
      const courses = [
        { course_name: '高等数学', teacher: '张教授', weekday: 1, start_time: '08:00', end_time: '09:45', classroom: '教学楼A201', week_start: 1, week_end: 16 },
        { course_name: '大学物理', teacher: '刘教授', weekday: 1, start_time: '10:00', end_time: '11:45', classroom: '理学楼B102', week_start: 1, week_end: 16 },
        { course_name: '思想政治', teacher: '陈老师', weekday: 1, start_time: '14:00', end_time: '15:45', classroom: '综合楼C301', week_start: 1, week_end: 18 },
        { course_name: '大学英语', teacher: '李老师', weekday: 2, start_time: '08:00', end_time: '09:45', classroom: '外语楼A305', week_start: 1, week_end: 16 },
        { course_name: '线性代数', teacher: '王教授', weekday: 2, start_time: '10:00', end_time: '11:45', classroom: '教学楼A203', week_start: 1, week_end: 14 },
        { course_name: '体育', teacher: '赵老师', weekday: 2, start_time: '14:00', end_time: '15:45', classroom: '体育馆', week_start: 1, week_end: 16 },
        { course_name: '程序设计', teacher: '孙老师', weekday: 3, start_time: '08:00', end_time: '09:45', classroom: '实验楼C102', week_start: 1, week_end: 16 },
        { course_name: '高等数学', teacher: '张教授', weekday: 3, start_time: '10:00', end_time: '11:45', classroom: '教学楼A201', week_start: 1, week_end: 16 },
        { course_name: '数据结构', teacher: '周教授', weekday: 3, start_time: '14:00', end_time: '15:45', classroom: '实验楼C201', week_start: 3, week_end: 18 },
        { course_name: '大学英语', teacher: '李老师', weekday: 4, start_time: '08:00', end_time: '09:45', classroom: '外语楼A305', week_start: 1, week_end: 16 },
        { course_name: '物理实验', teacher: '刘教授', weekday: 4, start_time: '14:00', end_time: '15:45', classroom: '物理实验楼', week_start: 2, week_end: 16 },
        { course_name: '计算机网络', teacher: '吴老师', weekday: 4, start_time: '16:00', end_time: '17:45', classroom: '实验楼C301', week_start: 5, week_end: 18 },
        { course_name: '程序设计', teacher: '孙老师', weekday: 5, start_time: '08:00', end_time: '09:45', classroom: '实验楼C102', week_start: 1, week_end: 16 },
        { course_name: '概率统计', teacher: '钱教授', weekday: 5, start_time: '10:00', end_time: '11:45', classroom: '教学楼A302', week_start: 1, week_end: 14 },
        { course_name: '创新创业', teacher: '郑老师', weekday: 5, start_time: '14:00', end_time: '15:45', classroom: '创业楼D101', week_start: 1, week_end: 10 }
      ];
      
      // 批量插入课程
      for (const course of courses) {
        await connection.query(
          'INSERT INTO schedules (user_id, course_name, teacher, weekday, start_time, end_time, classroom, week_start, week_end, week_type) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
          [req.user.userId, course.course_name, course.teacher, course.weekday, course.start_time, course.end_time, course.classroom, course.week_start, course.week_end, 'all']
        );
      }
      
      res.json({ success: true, message: '测试课程数据初始化成功', count: courses.length });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('初始化测试课程失败:', error);
    res.status(500).json({ error: '初始化测试课程失败' });
  }
});

// 获取失物招领接口
app.get('/api/lostfound', async (req, res) => {
  const { category, status } = req.query;
  
  try {
    const connection = await pool.getConnection();
    
    try {
      let query = `
        SELECT lf.*, u.username 
        FROM lost_found lf 
        JOIN users u ON lf.user_id = u.id
      `;
      let conditions = [];
      let params = [];

      if (category) {
        conditions.push('lf.category = ?');
        params.push(category);
      }

      if (status) {
        conditions.push('lf.status = ?');
        params.push(status);
      }

      if (conditions.length > 0) {
        query += ' WHERE ' + conditions.join(' AND ');
      }

      query += ' ORDER BY lf.created_at DESC LIMIT 50';

      const [items] = await connection.query(query, params);
      res.json({ items });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取失物招领失败:', error);
    res.status(500).json({ error: '获取失物招领失败' });
  }
});

// 发布失物招领接口
app.post('/api/lostfound', authenticateToken, [
  body('title').notEmpty().withMessage('标题不能为空'),
  body('description').notEmpty().withMessage('描述不能为空'),
  body('category').notEmpty().withMessage('分类不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { title, description, category, contact, images } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      const imagesJson = images && images.length > 0 ? JSON.stringify(images) : null;
      
      // 先尝试包含images字段的插入
      let result;
      try {
        [result] = await connection.query(
          'INSERT INTO lost_found (user_id, title, description, category, contact, images) VALUES (?, ?, ?, ?, ?, ?)',
          [req.user.userId, title, description, category, contact || '', imagesJson]
        );
      } catch (insertError) {
        if (insertError.code === 'ER_BAD_FIELD_ERROR') {
          // images字段不存在，使用不包含images的插入
          console.log('images字段不存在，使用兼容模式插入');
          [result] = await connection.query(
            'INSERT INTO lost_found (user_id, title, description, category, contact) VALUES (?, ?, ?, ?, ?)',
            [req.user.userId, title, description, category, contact || '']
          );
        } else {
          throw insertError;
        }
      }

      res.status(201).json({
        message: '失物招领发布成功',
        itemId: result.insertId
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('发布失物招领失败:', error);
    res.status(500).json({ error: '发布失物招领失败' });
  }
});

// 获取成绩接口
app.get('/api/grades', authenticateToken, async (req, res) => {
  const { semester } = req.query;
  
  try {
    const connection = await pool.getConnection();
    
    try {
      let query = 'SELECT * FROM grades WHERE user_id = ?';
      let params = [req.user.userId];

      if (semester) {
        query += ' AND semester = ?';
        params.push(semester);
      }

      query += ' ORDER BY created_at DESC';

      const [grades] = await connection.query(query, params);
      res.json({ grades });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取成绩失败:', error);
    res.status(500).json({ error: '获取成绩失败' });
  }
});

// 添加成绩接口
app.post('/api/grades', authenticateToken, [
  body('course_name').notEmpty().withMessage('课程名称不能为空'),
  body('score').isFloat({ min: 0, max: 100 }).withMessage('成绩必须是0-100之间的数字'),
  body('semester').notEmpty().withMessage('学期不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { course_name, score, semester, credit } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      const [result] = await connection.query(
        'INSERT INTO grades (user_id, course_name, score, semester, credit) VALUES (?, ?, ?, ?, ?)',
        [req.user.userId, course_name, score, semester, credit || 3]
      );

      res.status(201).json({
        message: '成绩添加成功',
        gradeId: result.insertId
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('添加成绩失败:', error);
    res.status(500).json({ error: '添加成绩失败' });
  }
});

// 获取通知接口
app.get('/api/notifications', authenticateToken, async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      const [notifications] = await connection.query(
        'SELECT * FROM notifications WHERE user_id = ? ORDER BY created_at DESC LIMIT 50',
        [req.user.userId]
      );

      res.json({ notifications });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取通知失败:', error);
    res.status(500).json({ error: '获取通知失败' });
  }
});

// 标记通知为已读接口
app.put('/api/notifications/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      await connection.query(
        'UPDATE notifications SET read_status = 1 WHERE id = ? AND user_id = ?',
        [id, req.user.userId]
      );

      res.json({ success: true, message: '通知标记为已读' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('标记通知为已读失败:', error);
    res.status(500).json({ error: '标记通知为已读失败' });
  }
});

// 获取活动接口
app.get('/api/activities', async (req, res) => {
  // 从请求头获取用户信息（可选）
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];
  let userId = null;

  if (token) {
    try {
      const decoded = jwt.verify(token, JWT_SECRET);
      userId = decoded.userId;
    } catch (error) {
      // 忽略token错误，继续执行
    }
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      const [activities] = await connection.query(`
        SELECT a.*, u.username as organizer_name,
               CASE WHEN ap.user_id IS NOT NULL THEN 1 ELSE 0 END as is_registered
        FROM activities a 
        JOIN users u ON a.organizer_id = u.id 
        LEFT JOIN activity_participants ap ON a.id = ap.activity_id AND ap.user_id = ?
        WHERE a.status = 'active'
        ORDER BY a.start_time ASC 
        LIMIT 50
      `, [userId]);

      res.json({ success: true, activities });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取活动失败:', error);
    res.status(500).json({ error: '获取活动失败' });
  }
});

// 报名活动接口
app.post('/api/activities/:id/register', authenticateToken, async (req, res) => {
  const activityId = req.params.id;
  const userId = req.user.userId;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查是否已经报名
      const [existing] = await connection.query(
        'SELECT * FROM activity_participants WHERE activity_id = ? AND user_id = ?',
        [activityId, userId]
      );

      if (existing.length > 0) {
        return res.status(400).json({ error: '您已经报名了这个活动' });
      }

      // 添加报名记录
      await connection.query(
        'INSERT INTO activity_participants (activity_id, user_id) VALUES (?, ?)',
        [activityId, userId]
      );

      // 更新活动参与人数
      await connection.query(
        'UPDATE activities SET participants = participants + 1 WHERE id = ?',
        [activityId]
      );

      res.json({ success: true, message: '报名成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('报名活动失败:', error);
    res.status(500).json({ error: '报名失败' });
  }
});

// 取消报名活动接口
app.post('/api/activities/:id/cancel', authenticateToken, async (req, res) => {
  const activityId = req.params.id;
  const userId = req.user.userId;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查是否已经报名
      const [existing] = await connection.query(
        'SELECT * FROM activity_participants WHERE activity_id = ? AND user_id = ?',
        [activityId, userId]
      );

      if (existing.length === 0) {
        return res.status(400).json({ error: '您尚未报名此活动' });
      }

      // 删除报名记录
      await connection.query(
        'DELETE FROM activity_participants WHERE activity_id = ? AND user_id = ?',
        [activityId, userId]
      );

      // 更新活动参与人数
      await connection.query(
        'UPDATE activities SET participants = GREATEST(0, participants - 1) WHERE id = ?',
        [activityId]
      );

      res.json({ success: true, message: '取消报名成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('取消报名失败:', error);
    res.status(500).json({ error: '取消报名失败' });
  }
});

// 获取单个活动详情接口
app.get('/api/activities/:id', async (req, res) => {
  const activityId = req.params.id;

  try {
    const connection = await pool.getConnection();
    
    try {
      const [activities] = await connection.query(`
        SELECT a.*, u.username as organizer_name
        FROM activities a 
        JOIN users u ON a.organizer_id = u.id 
        WHERE a.id = ?
      `, [activityId]);

      if (activities.length === 0) {
        return res.status(404).json({ error: '活动不存在' });
      }

      res.json({ success: true, activity: activities[0] });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取活动详情失败:', error);
    res.status(500).json({ error: '获取活动详情失败' });
  }
});

// 更新活动接口
app.put('/api/activities/:id', authenticateToken, [
  body('title').notEmpty().withMessage('活动标题不能为空'),
  body('description').notEmpty().withMessage('活动描述不能为空'),
  body('location').notEmpty().withMessage('活动地点不能为空'),
  body('start_time').notEmpty().withMessage('开始时间不能为空'),
  body('end_time').notEmpty().withMessage('结束时间不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const activityId = req.params.id;
  const userId = req.user.userId;
  const { title, description, location, start_time, end_time } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      console.log('更新活动请求:', { activityId, userId, title, description, location, start_time, end_time });
      
      // 检查用户是否为管理员或活动创建者
      const [activity] = await connection.query(
        'SELECT organizer_id FROM activities WHERE id = ?',
        [activityId]
      );

      if (activity.length === 0) {
        console.log('活动不存在:', activityId);
        return res.status(404).json({ error: '活动不存在' });
      }

      // 检查用户权限
      const [user] = await connection.query(
        'SELECT role FROM users WHERE id = ?',
        [userId]
      );

      const isAdmin = user.length > 0 && user[0].role === 'admin';
      const isOrganizer = activity[0].organizer_id === userId;

      console.log('权限检查:', { isAdmin, isOrganizer, userId, organizerId: activity[0].organizer_id });

      if (!isAdmin && !isOrganizer) {
        return res.status(403).json({ error: '您没有权限编辑此活动' });
      }

      // 更新活动
      const [result] = await connection.query(
        'UPDATE activities SET title = ?, description = ?, location = ?, start_time = ?, end_time = ? WHERE id = ?',
        [title, description, location, start_time, end_time, activityId]
      );

      console.log('更新结果:', result);

      res.json({ success: true, message: '活动更新成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('更新活动失败详细错误:', error);
    logger.error('更新活动失败:', error);
    res.status(500).json({ error: '更新活动失败', details: error.message });
  }
});

// 删除活动接口
app.delete('/api/activities/:id', authenticateToken, async (req, res) => {
  const activityId = req.params.id;
  const userId = req.user.userId;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查用户是否为管理员或活动创建者
      const [activity] = await connection.query(
        'SELECT organizer_id FROM activities WHERE id = ?',
        [activityId]
      );

      if (activity.length === 0) {
        return res.status(404).json({ error: '活动不存在' });
      }

      // 检查用户权限
      const [user] = await connection.query(
        'SELECT role FROM users WHERE id = ?',
        [userId]
      );

      const isAdmin = user.length > 0 && user[0].role === 'admin';
      const isOrganizer = activity[0].organizer_id === userId;

      if (!isAdmin && !isOrganizer) {
        return res.status(403).json({ error: '您没有权限删除此活动' });
      }

      // 删除活动（会自动删除相关的参与记录，因为有外键约束）
      await connection.query('DELETE FROM activities WHERE id = ?', [activityId]);

      res.json({ success: true, message: '活动删除成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('删除活动失败:', error);
    res.status(500).json({ error: '删除活动失败' });
  }
});

// 创建活动接口
app.post('/api/activities', authenticateToken, [
  body('title').notEmpty().withMessage('活动标题不能为空'),
  body('description').notEmpty().withMessage('活动描述不能为空'),
  body('location').notEmpty().withMessage('活动地点不能为空'),
  body('start_time').notEmpty().withMessage('开始时间不能为空'),
  body('end_time').notEmpty().withMessage('结束时间不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { title, description, location, start_time, end_time } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      const [result] = await connection.query(
        'INSERT INTO activities (title, description, location, start_time, end_time, organizer_id) VALUES (?, ?, ?, ?, ?, ?)',
        [title, description, location, start_time, end_time, req.user.userId]
      );

      res.status(201).json({
        success: true,
        message: '活动创建成功',
        activityId: result.insertId
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('创建活动失败:', error);
    res.status(500).json({ error: '创建活动失败' });
  }
});

// ============ 好友相关接口 ============

// 获取好友列表
app.get('/api/friends', authenticateToken, async (req, res) => {
  const userId = req.user.userId;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 查询好友列表（双向好友关系）
      const [friends] = await connection.query(`
        SELECT DISTINCT u.id, u.username, u.avatar, u.student_id
        FROM users u
        INNER JOIN (
          SELECT friend_id as user_id FROM friendships WHERE user_id = ? AND status = 'accepted'
          UNION
          SELECT user_id FROM friendships WHERE friend_id = ? AND status = 'accepted'
        ) f ON u.id = f.user_id
        ORDER BY u.username
      `, [userId, userId]);

      res.json({ 
        success: true, 
        friends: friends || [] 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('获取好友列表失败:', error);
    logger.error('获取好友列表失败:', error);
    res.status(500).json({ error: '获取好友列表失败' });
  }
});

// 搜索用户
app.get('/api/users/search', authenticateToken, async (req, res) => {
  const { keyword } = req.query;
  const currentUserId = req.user.userId;

  if (!keyword || keyword.trim() === '') {
    return res.json({ success: true, users: [] });
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      // 搜索用户（排除自己）
      const [users] = await connection.query(`
        SELECT id, username, avatar, student_id
        FROM users
        WHERE (username LIKE ? OR student_id LIKE ?) AND id != ?
        LIMIT 20
      `, [`%${keyword}%`, `%${keyword}%`, currentUserId]);

      // 检查每个用户是否已是好友
      const usersWithFriendStatus = await Promise.all(users.map(async (user) => {
        const [friendship] = await connection.query(`
          SELECT status FROM friendships 
          WHERE (user_id = ? AND friend_id = ?) OR (user_id = ? AND friend_id = ?)
        `, [currentUserId, user.id, user.id, currentUserId]);

        return {
          ...user,
          isFriend: friendship.length > 0 && friendship[0].status === 'accepted',
          friendshipStatus: friendship.length > 0 ? friendship[0].status : null
        };
      }));

      res.json({ 
        success: true, 
        users: usersWithFriendStatus 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('搜索用户失败:', error);
    logger.error('搜索用户失败:', error);
    res.status(500).json({ error: '搜索用户失败' });
  }
});

// ============ 用户资料相关接口 ============

// 上传头像
// 优先使用 Cloudflare R2 存储，如未配置则使用 Base64 存数据库
app.post('/api/profile/avatar', authenticateToken, upload.single('avatar'), async (req, res) => {
  try {
    console.log('📸 收到头像上传请求');
    console.log('📸 用户ID:', req.user.userId);
    console.log('📸 文件信息:', req.file);
    
    if (!req.file) {
      console.error('❌ 没有收到文件');
      return res.status(400).json({ error: '请选择头像文件' });
    }

    const userId = req.user.userId;
    
    console.log('📸 文件路径:', req.file.path);
    console.log('📸 文件大小:', req.file.size);
    
    // 检查文件是否存在
    if (!fs.existsSync(req.file.path)) {
      console.error('❌ 文件不存在:', req.file.path);
      return res.status(500).json({ error: '文件上传失败，请重试' });
    }
    
    // 读取文件
    const fileBuffer = fs.readFileSync(req.file.path);
    let avatarUrl = null;
    let storageType = 'base64';
    
    // 优先使用 R2 存储
    if (r2Storage.isConfigured()) {
      try {
        console.log('📸 使用 Cloudflare R2 存储头像...');
        
        // 获取文件扩展名
        const ext = req.file.originalname.split('.').pop() || 'jpg';
        
        // 使用固定路径：avatars/用户ID/avatar.扩展名
        // 每个用户有独立文件夹，每次上传覆盖同一文件
        const customKey = `avatars/user-${userId}/avatar.${ext}`;
        
        const uploadResult = await r2Storage.uploadFile(
          fileBuffer,
          req.file.originalname,
          req.file.mimetype,
          { customKey: customKey }
        );
        
        // 添加时间戳参数避免浏览器缓存
        avatarUrl = `${uploadResult.url}?t=${Date.now()}`;
        storageType = 'r2';
        console.log('📸 R2 上传成功:', avatarUrl);
        console.log('📸 存储路径:', customKey);
      } catch (r2Error) {
        console.error('📸 R2 上传失败，回退到 Base64:', r2Error.message);
        // R2 失败时回退到 Base64
        avatarUrl = `data:${req.file.mimetype};base64,${fileBuffer.toString('base64')}`;
      }
    } else {
      // 未配置 R2，使用 Base64 存储
      console.log('📸 R2 未配置，使用 Base64 存储头像');
      avatarUrl = `data:${req.file.mimetype};base64,${fileBuffer.toString('base64')}`;
    }
    
    console.log('📸 头像URL长度:', avatarUrl.length);
    console.log('📸 存储类型:', storageType);
    
    // 删除临时文件
    try {
      fs.unlinkSync(req.file.path);
      console.log('📸 临时文件已删除');
    } catch (unlinkError) {
      console.error('⚠️  删除临时文件失败:', unlinkError.message);
    }

    const connection = await pool.getConnection();
    
    try {
      // 更新用户头像
      await connection.query(
        'UPDATE users SET avatar = ? WHERE id = ?',
        [avatarUrl, userId]
      );

      console.log('✅ 头像上传成功');
      
      res.json({ 
        success: true, 
        avatarUrl: avatarUrl,
        storageType: storageType,
        message: '头像上传成功' 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('❌ 上传头像失败:', error);
    console.error('❌ 错误堆栈:', error.stack);
    logger.error('上传头像失败:', error);
    res.status(500).json({ 
      error: '上传头像失败',
      message: error.message 
    });
  }
});

// 更新用户资料
app.put('/api/profile', authenticateToken, async (req, res) => {
  const userId = req.user.userId;
  const { username, email, bio, phone, major, grade, gender, avatar } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 构建更新字段
      const updates = [];
      const values = [];

      if (username !== undefined) {
        updates.push('username = ?');
        values.push(username);
      }
      if (email !== undefined) {
        updates.push('email = ?');
        values.push(email);
      }
      if (bio !== undefined) {
        updates.push('bio = ?');
        values.push(bio);
      }
      if (phone !== undefined) {
        updates.push('phone = ?');
        values.push(phone);
      }
      if (major !== undefined) {
        updates.push('major = ?');
        values.push(major);
      }
      if (grade !== undefined) {
        updates.push('grade = ?');
        values.push(grade);
      }
      if (gender !== undefined) {
        updates.push('gender = ?');
        values.push(gender);
      }
      if (avatar !== undefined) {
        updates.push('avatar = ?');
        values.push(avatar);
      }

      if (updates.length === 0) {
        return res.status(400).json({ error: '没有要更新的字段' });
      }

      values.push(userId);

      await connection.query(
        `UPDATE users SET ${updates.join(', ')} WHERE id = ?`,
        values
      );

      res.json({ 
        success: true, 
        message: '资料更新成功' 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('更新资料失败:', error);
    logger.error('更新资料失败:', error);
    res.status(500).json({ error: '更新资料失败' });
  }
});

// 添加好友
app.post('/api/friends', authenticateToken, async (req, res) => {
  const userId = req.user.userId;
  const { friendId } = req.body;

  if (!friendId) {
    return res.status(400).json({ error: '好友ID不能为空' });
  }

  if (userId === friendId) {
    return res.status(400).json({ error: '不能添加自己为好友' });
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查是否已经是好友
      const [existing] = await connection.query(`
        SELECT * FROM friendships 
        WHERE (user_id = ? AND friend_id = ?) OR (user_id = ? AND friend_id = ?)
      `, [userId, friendId, friendId, userId]);

      if (existing.length > 0) {
        return res.status(400).json({ error: '已经是好友或已发送好友请求' });
      }

      // 添加好友关系（直接设为accepted，简化流程）
      await connection.query(
        'INSERT INTO friendships (user_id, friend_id, status) VALUES (?, ?, ?)',
        [userId, friendId, 'accepted']
      );

      res.json({ 
        success: true, 
        message: '添加好友成功' 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('添加好友失败:', error);
    logger.error('添加好友失败:', error);
    res.status(500).json({ error: '添加好友失败' });
  }
});

// ============ 聊天相关接口 ============

// 获取聊天消息
app.get('/api/chat/:friendId', authenticateToken, async (req, res) => {
  const userId = req.user.userId;
  const friendId = req.params.friendId;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 获取聊天消息
      const [messages] = await connection.query(`
        SELECT m.*, u.username, u.avatar
        FROM chat_messages m
        JOIN users u ON m.sender_id = u.id
        WHERE (m.sender_id = ? AND m.receiver_id = ?) 
           OR (m.sender_id = ? AND m.receiver_id = ?)
        ORDER BY m.created_at ASC
        LIMIT 100
      `, [userId, friendId, friendId, userId]);

      res.json({ 
        success: true, 
        messages: messages || [] 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('获取聊天消息失败:', error);
    logger.error('获取聊天消息失败:', error);
    res.status(500).json({ error: '获取聊天消息失败' });
  }
});

// 发送聊天消息
app.post('/api/chat', authenticateToken, async (req, res) => {
  const senderId = req.user.userId;
  const { receiverId, content, type = 'text' } = req.body;

  if (!receiverId || !content) {
    return res.status(400).json({ error: '接收者ID和消息内容不能为空' });
  }

  try {
    const connection = await pool.getConnection();
    
    try {
      // 插入消息
      const [result] = await connection.query(
        'INSERT INTO chat_messages (sender_id, receiver_id, content, type) VALUES (?, ?, ?, ?)',
        [senderId, receiverId, content, type]
      );

      // 获取刚插入的消息
      const [messages] = await connection.query(`
        SELECT m.*, u.username, u.avatar
        FROM chat_messages m
        JOIN users u ON m.sender_id = u.id
        WHERE m.id = ?
      `, [result.insertId]);

      res.json({ 
        success: true, 
        message: messages[0] 
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('发送消息失败:', error);
    logger.error('发送消息失败:', error);
    res.status(500).json({ error: '发送消息失败' });
  }
});

// ============ 兼容性路由 ============
// 失物招领兼容性路由 (前端调用的是 /api/lost-found)
app.get('/api/lost-found', async (req, res) => {
  const { category, status } = req.query;
  
  try {
    const connection = await pool.getConnection();
    
    try {
      let query = `
        SELECT lf.*, u.username 
        FROM lost_found lf 
        JOIN users u ON lf.user_id = u.id
      `;
      let conditions = [];
      let params = [];

      if (category) {
        conditions.push('lf.category = ?');
        params.push(category);
      }

      if (status) {
        conditions.push('lf.status = ?');
        params.push(status);
      }

      if (conditions.length > 0) {
        query += ' WHERE ' + conditions.join(' AND ');
      }

      query += ' ORDER BY lf.created_at DESC LIMIT 50';

      const [items] = await connection.query(query, params);
      res.json({ items });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取失物招领失败:', error);
    res.status(500).json({ error: '获取失物招领失败' });
  }
});

app.post('/api/lost-found', authenticateToken, [
  body('title').notEmpty().withMessage('标题不能为空'),
  body('description').notEmpty().withMessage('描述不能为空'),
  body('category').notEmpty().withMessage('分类不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { title, description, category, contact, images } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      const imagesJson = images && images.length > 0 ? JSON.stringify(images) : null;
      
      // 先尝试包含images字段的插入
      let result;
      try {
        [result] = await connection.query(
          'INSERT INTO lost_found (user_id, title, description, category, contact, images) VALUES (?, ?, ?, ?, ?, ?)',
          [req.user.userId, title, description, category, contact || '', imagesJson]
        );
      } catch (insertError) {
        if (insertError.code === 'ER_BAD_FIELD_ERROR') {
          // images字段不存在，使用不包含images的插入
          console.log('images字段不存在，使用兼容模式插入');
          [result] = await connection.query(
            'INSERT INTO lost_found (user_id, title, description, category, contact) VALUES (?, ?, ?, ?, ?)',
            [req.user.userId, title, description, category, contact || '']
          );
        } else {
          throw insertError;
        }
      }

      res.status(201).json({
        message: '失物招领发布成功',
        itemId: result.insertId
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('发布失物招领失败:', error);
    res.status(500).json({ error: '发布失物招领失败' });
  }
});

// 获取失物招领详情接口
app.get('/api/lost-found/:id', async (req, res) => {
  const { id } = req.params;
  
  try {
    const connection = await pool.getConnection();
    
    try {
      const [items] = await connection.query(`
        SELECT lf.*, u.username 
        FROM lost_found lf 
        JOIN users u ON lf.user_id = u.id 
        WHERE lf.id = ?
      `, [id]);

      if (items.length === 0) {
        return res.status(404).json({ error: '失物招领信息不存在' });
      }

      res.json({ item: items[0] });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('获取失物招领详情失败:', error);
    res.status(500).json({ error: '获取失物招领详情失败' });
  }
});

// 更新失物招领状态接口
app.put('/api/lost-found/:id/status', authenticateToken, async (req, res) => {
  const { id } = req.params;
  const { status } = req.body;
  
  try {
    const connection = await pool.getConnection();
    
    try {
      const [result] = await connection.query(
        'UPDATE lost_found SET status = ? WHERE id = ?',
        [status, id]
      );

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: '失物招领信息不存在' });
      }

      res.json({ success: true, message: '状态更新成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('更新失物招领状态失败:', error);
    res.status(500).json({ error: '更新失物招领状态失败' });
  }
});

// 编辑失物招领接口
app.put('/api/lost-found/:id', authenticateToken, [
  body('title').notEmpty().withMessage('标题不能为空'),
  body('description').notEmpty().withMessage('描述不能为空'),
  body('category').notEmpty().withMessage('分类不能为空')
], async (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { id } = req.params;
  const { title, description, category, contact, images } = req.body;

  try {
    const connection = await pool.getConnection();
    
    try {
      const imagesJson = images && images.length > 0 ? JSON.stringify(images) : null;
      
      // 先尝试包含images字段的更新
      let result;
      try {
        [result] = await connection.query(
          'UPDATE lost_found SET title = ?, description = ?, category = ?, contact = ?, images = ? WHERE id = ? AND user_id = ?',
          [title, description, category, contact || '', imagesJson, id, req.user.userId]
        );
      } catch (updateError) {
        if (updateError.code === 'ER_BAD_FIELD_ERROR') {
          // images字段不存在，使用不包含images的更新
          console.log('images字段不存在，使用兼容模式更新');
          [result] = await connection.query(
            'UPDATE lost_found SET title = ?, description = ?, category = ?, contact = ? WHERE id = ? AND user_id = ?',
            [title, description, category, contact || '', id, req.user.userId]
          );
        } else {
          throw updateError;
        }
      }

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: '失物招领信息不存在或无权限编辑' });
      }

      res.json({ success: true, message: '编辑成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('编辑失物招领失败:', error);
    res.status(500).json({ error: '编辑失物招领失败' });
  }
});

// 删除失物招领接口
app.delete('/api/lost-found/:id', authenticateToken, async (req, res) => {
  const { id } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      const [result] = await connection.query(
        'DELETE FROM lost_found WHERE id = ? AND user_id = ?',
        [id, req.user.userId]
      );

      if (result.affectedRows === 0) {
        return res.status(404).json({ error: '失物招领信息不存在或无权限删除' });
      }

      res.json({ success: true, message: '删除成功' });
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('删除失物招领失败:', error);
    res.status(500).json({ error: '删除失物招领失败' });
  }
});

// 失物招领点赞接口
app.post('/api/lost-found/:id/like', authenticateToken, async (req, res) => {
  const { id } = req.params;

  try {
    const connection = await pool.getConnection();
    
    try {
      // 检查是否已经点赞
      const [existingLikes] = await connection.query(
        'SELECT id FROM lost_found_likes WHERE item_id = ? AND user_id = ?',
        [id, req.user.userId]
      );

      if (existingLikes.length > 0) {
        // 已经点赞，取消点赞
        await connection.query(
          'DELETE FROM lost_found_likes WHERE item_id = ? AND user_id = ?',
          [id, req.user.userId]
        );
        
        await connection.query(
          'UPDATE lost_found SET likes = likes - 1 WHERE id = ?',
          [id]
        );
        
        res.json({ success: true, message: '取消点赞成功', liked: false });
      } else {
        // 未点赞，添加点赞
        await connection.query(
          'INSERT INTO lost_found_likes (item_id, user_id) VALUES (?, ?)',
          [id, req.user.userId]
        );
        
        await connection.query(
          'UPDATE lost_found SET likes = likes + 1 WHERE id = ?',
          [id]
        );
        
        res.json({ success: true, message: '点赞成功', liked: true });
      }
    } finally {
      connection.release();
    }
  } catch (error) {
    logger.error('失物招领点赞失败:', error);
    res.status(500).json({ error: '操作失败' });
  }
});

// 调试接口 - 检查文件是否存在
app.get('/api/debug/files', (req, res) => {
  try {
    // 检查uploads目录
    const uploadsFiles = fs.existsSync(uploadsDir) ? fs.readdirSync(uploadsDir) : [];
    const uploadsDetails = uploadsFiles.map(filename => {
      const filePath = path.join(uploadsDir, filename);
      const stats = fs.statSync(filePath);
      return {
        filename,
        size: stats.size,
        created: stats.birthtime,
        modified: stats.mtime,
        path: filePath
      };
    });

    // 检查static目录
    const staticFiles = fs.existsSync(staticDir) ? fs.readdirSync(staticDir) : [];
    const staticDetails = staticFiles.map(filename => {
      const filePath = path.join(staticDir, filename);
      const stats = fs.statSync(filePath);
      return {
        filename,
        size: stats.size,
        created: stats.birthtime,
        modified: stats.mtime,
        path: filePath
      };
    });
    
    res.json({
      uploadsDir,
      staticDir,
      uploads: {
        totalFiles: uploadsFiles.length,
        files: uploadsDetails
      },
      static: {
        totalFiles: staticFiles.length,
        files: staticDetails
      }
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// 调试接口 - 查看用户数据
app.get('/api/debug/users', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      const [users] = await connection.query('SELECT id, username, email, role, created_at FROM users');
      res.json({ 
        users: users.map(user => ({
          id: user.id,
          username: user.username,
          email: user.email,
          role: user.role,
          created_at: user.created_at
        })),
        count: users.length,
        message: '调试信息 - 用户列表'
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    res.status(500).json({ error: error.message, message: '查询用户数据失败' });
  }
});

// 调试接口 - 测试管理员登录
app.post('/api/debug/test-admin', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    
    try {
      const [users] = await connection.query(
        'SELECT id, username, email, password, role FROM users WHERE username = ? OR email = ?',
        ['admin', 'admin']
      );

      if (users.length === 0) {
        return res.json({ 
          success: false, 
          message: '管理员用户不存在',
          query: 'SELECT * FROM users WHERE username = admin OR email = admin'
        });
      }

      const user = users[0];
      const bcrypt = require('bcrypt');
      const isValidPassword = await bcrypt.compare('admin', user.password);

      res.json({
        success: isValidPassword,
        message: isValidPassword ? '密码验证成功' : '密码验证失败',
        user: {
          id: user.id,
          username: user.username,
          email: user.email,
          role: user.role,
          passwordHash: user.password.substring(0, 20) + '...'
        }
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    res.status(500).json({ error: error.message, message: '测试管理员登录失败' });
  }
});

// Multer错误处理中间件
app.use((error, req, res, next) => {
  if (error instanceof multer.MulterError) {
    console.error('❌ Multer错误:', error);
    if (error.code === 'LIMIT_FILE_SIZE') {
      return res.status(400).json({ 
        error: '文件太大',
        message: '文件大小不能超过2MB' 
      });
    }
    return res.status(400).json({ 
      error: '文件上传错误',
      message: error.message 
    });
  }
  
  if (error) {
    console.error('❌ 服务器错误:', error);
    return res.status(500).json({ 
      error: '服务器错误',
      message: error.message 
    });
  }
  
  next();
});

// 404处理
app.use('*', (req, res) => {
  res.status(404).json({ 
    error: 'API endpoint not found',
    message: 'Please check the API documentation at /'
  });
});

// 启动服务器
async function startServer() {
  try {
    console.log('🔄 正在连接MySQL数据库...');
    console.log(`📍 数据库地址: ${process.env.MYSQL_HOST || 'gz-cdb-1xrcr3dt.sql.tencentcdb.com'}:${process.env.MYSQL_PORT || 23767}`);
    
    // 测试数据库连接
    const connection = await pool.getConnection();
    console.log('✅ MySQL数据库连接成功！');
    connection.release();
    
    // 初始化数据库表
    await initDatabase();
    
    // 启动服务器
    app.listen(PORT, '0.0.0.0', () => {
      logger.info(`校园圈服务器运行在端口 ${PORT} - Hugging Face Spaces版本`);
      console.log(`\n🚀 CampusLoop Backend 启动成功！`);
      console.log(`🌐 服务器地址: http://0.0.0.0:${PORT}`);
      console.log(`🏥 健康检查: http://0.0.0.0:${PORT}/api/health`);
      console.log(`📅 当前时间: ${new Date().toLocaleString()}`);
      console.log(`🎯 平台: Hugging Face Spaces`);
      console.log(`\n📝 主要接口:`);
      console.log(`   GET   /                     - API文档`);
      console.log(`   GET   /api/health           - 健康检查`);
      console.log(`   POST  /api/auth/register    - 用户注册`);
      console.log(`   POST  /api/auth/login       - 用户登录`);
      console.log(`   GET   /api/posts            - 获取动态`);
      console.log(`   POST  /api/posts            - 发布动态`);
      console.log(`   GET   /api/forum/posts      - 获取论坛帖子`);
      console.log(`   GET   /api/activities       - 获取活动`);
    });
  } catch (error) {
    logger.error('服务器启动失败:', error);
    console.error('❌ 服务器启动失败:', error.message);
    
    if (error.code === 'ETIMEDOUT') {
      console.error('\n⚠️  数据库连接超时！可能的原因：');
      console.error('   1. 数据库服务器防火墙未开放外部访问');
      console.error('   2. Hugging Face Spaces IP未加入数据库白名单');
      console.error('   3. 数据库地址或端口配置错误');
      console.error('\n💡 解决方案：');
      console.error('   1. 在腾讯云MySQL控制台添加 0.0.0.0/0 到白名单（测试用）');
      console.error('   2. 检查Hugging Face Spaces环境变量配置');
      console.error('   3. 确认数据库服务正常运行');
    }
    
    process.exit(1);
  }
}

startServer();