const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const cors = require('cors');
const xlsx = require('xlsx');

const app = express();
const port = 3001;

// 使用 CORS
app.use(cors());

// 创建数据库连接池
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'mysql123',
  database: 'mysql'
});

// 解析 JSON 请求体
app.use(bodyParser.json());

// 获取一个随机学生的信息
app.get('/api/get-random-student', (req, res) => {
  const query = 'SELECT * FROM students ORDER BY RAND() LIMIT 1';
  pool.query(query, (error, results) => {
    if (error) {
      return res.status(500).json({ message: 'Error fetching random student' });
    }
    if (results.length === 0) {
      return res.status(404).json({ error: 'No students found' });
    }
    res.json(results[0]);
  });
});

// 为指定的学生添加出勤积分
app.post('/api/add-attendance-points', (req, res) => {
  const { student_id } = req.body;
  const query = 'UPDATE students SET points = points + 1 WHERE student_id = ?';
  pool.query(query, [student_id], (error, results) => {
    if (error) {
      return res.status(500).json({ message: 'Error adding attendance points' });
    }
    res.json({ message: 'Attendance points added', student_id });
  });
});

// 确认并更新学生的答题积分
app.post('/api/confirm-points', (req, res) => {
  const { student_id, points } = req.body;
  const query = 'UPDATE students SET points = points + ? WHERE student_id = ?';
  pool.query(query, [points, student_id], (error, results) => {
    if (error) {
      return res.status(500).json({ message: 'Error confirming points' });
    }
    res.json({ message: 'Points confirmed and updated', student_id, points });
  });
});

// 导出学生数据到Excel文件
app.get('/api/export-students', (req, res) => {
  const query = 'SELECT * FROM students';
  pool.query(query, (error, results) => {
    if (error) {
      return res.status(500).json({ message: 'Error fetching students' });
    }
    if (results.length === 0) {
      return res.status(404).json({ error: 'No students found' });
    }
    const worksheet = xlsx.utils.json_to_sheet(results);
    const workbook = xlsx.utils.book_new();
    xlsx.utils.book_append_sheet(workbook, worksheet, 'Students');
    res.setHeader('Content-Disposition', 'attachment; filename=students.xlsx');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.end(xlsx.write(workbook, { type: 'buffer' }));
  });
});

app.listen(port, () => {
  console.log(`Server is running on http://localhost:${port}`);
});