// 导入学生名单（从Excel）
app.post('/import-students', upload.single('file'), async (req, res) => {
  try {
    const file = req.file;
    const workbook = xlsx.readFile(file.path);
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const students = xlsx.utils.sheet_to_json(sheet); // 解析Excel数据

    // 批量插入数据库
    for (const student of students) {
      await db.execute(
        'INSERT INTO students (student_id, name, major) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE name=?, major=?',
        [student.学号, student.姓名, student.专业, student.姓名, student.专业]
      );
    }
    res.json({ success: true, message: '导入成功' });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

// 随机点名（根据积分调整概率：积分越高，概率越低）
app.get('/random-call', async (req, res) => {
  try {
    // 查询所有学生的积分和学号
    const [students] = await db.execute('SELECT student_id, name, score FROM students');
    
    // 计算权重（积分倒数，避免0积分导致权重无穷大）
    const totalWeight = students.reduce((sum, s) => sum + (1 / (s.score + 1)), 0);
    let random = Math.random() * totalWeight;
    let selected;

    // 根据权重随机选择学生
    for (const s of students) {
      const weight = 1 / (s.score + 1);
      if (random <= weight) {
        selected = s;
        break;
      }
      random -= weight;
    }

    // 记录点名信息并更新随机点名次数
    await db.execute(
      'INSERT INTO attendance (student_id, mode) VALUES (?, ?)',
      [selected.student_id, 'random']
    );
    await db.execute(
      'UPDATE students SET random_count = random_count + 1 WHERE student_id = ?',
      [selected.student_id]
    );

    res.json({ success: true, student: selected });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

// 更新学生积分（例如：回答问题后）
app.post('/update-score', async (req, res) => {
  const { student_id, answer_score } = req.body;
  try {
    // 先获取当前积分
    const [rows] = await db.execute('SELECT score FROM students WHERE student_id = ?', [student_id]);
    const currentScore = rows[0].score;
    
    // 更新积分（出勤+1分已在点名时处理，此处仅处理回答问题得分）
    const newScore = currentScore + answer_score;
    await db.execute(
      'UPDATE students SET score = ? WHERE student_id = ?',
      [newScore, student_id]
    );
    // 同步更新点名记录的回答得分
    await db.execute(
      'UPDATE attendance SET answer_score = ? WHERE student_id = ? ORDER BY attendance_time DESC LIMIT 1',
      [answer_score, student_id]
    );
    res.json({ success: true, newScore });
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});

// 导出积分详单（生成Excel）
app.get('/export-scores', async (req, res) => {
  try {
    const [students] = await db.execute(
      'SELECT student_id, name, major, random_count, score FROM students'
    );
    const worksheet = xlsx.utils.json_to_sheet(students);
    const workbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(workbook, worksheet, '积分详单');
    const buffer = xlsx.write(workbook, { type: 'buffer', bookType: 'xlsx' });
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment; filename="scores.xlsx"');
    res.send(buffer);
  } catch (err) {
    res.status(500).json({ success: false, error: err.message });
  }
});