const express = require('express'); const mysql = require('mysql'); const bodyParser = require('body-parser'); const cors = require('cors');
const app = express();
app.use(cors());
app.use(bodyParser.json());

// MySQL数据库连接配置
const db = mysql.createConnection({
host: 'localhost',
user: 'root',
password: '123456', // 替换为你的MySQL密码
database: 'librarysystem'
});

db.connect(err => {
if (err) {
throw err;
}
console.log('MySQL connected...');
});

// 登录路由
app.post('/login', (req, res) => {
const { username, password } = req.body;
const query = 'SELECT * FROM users WHERE username = ? AND password = ?';
db.query(query, [username, password], (err, results) => {
if (err) {
res.status(500).json({ error: 'Server error' });
} else if (results.length > 0) {
console.log("Login successful, sending response:", results[0]);
res.json(results[0]);
} else {
res.status(400).json({ error: 'Invalid login' });
}
});
});

// 注册路由
app.post('/register', (req, res) => {
const { username, password } = req.body;
// 检查用户名是否已存在
const checkUserQuery = 'SELECT * FROM users WHERE username = ?';
db.query(checkUserQuery, [username], (err, results) => {
if (err) {
res.status(500).json({ error: 'Server error' });
} else if (results.length > 0) {
// 用户名已存在
res.status(400).json({ error: 'Username already exists' });
} else {
// 用户名不存在，可以注册
const insertUserQuery = 'INSERT INTO users (username, password, role) VALUES (?, ?, ?)';
db.query(insertUserQuery, [username, password, 'user'], (err, results) => {
if (err) {
res.status(500).json({ error: 'Failed to register user' });
} else {
res.status(201).send('User registered successfully');
}
});
}
});
});
// 管理员操作路由
// 添加图书
app.post('/admin/add-book', (req, res) => {
const { title, author, year_published } = req.body;
const query = 'INSERT INTO books (title, author, year_published) VALUES (?, ?, ?)';
db.query(query, [title, author, year_published], (err, results) => {
if (err) {
console.error('Error adding book:', err);
res.status(500).json({ error: 'Failed to add book' });
} else {
res.status(200).send('Book added successfully');
}
});
});
// 删除图书
// 删除图书
app.post('/admin/delete-book', (req, res) => {
const { title } = req.body;


// 确保标题是有效的字符串
if (!title || typeof title !== 'string') {
    res.status(400).json({ error: 'Invalid book title format' });
    return;
}

const query = 'DELETE FROM books WHERE title = ?';
db.query(query, [title], (err, results) => {
    if (err) {
        console.error('Error deleting book:', err);
        res.status(500).json({ error: 'Failed to delete book. Error details: ' + err.message });
    } else {
        // 检查是否有行被删除
        if (results.affectedRows > 0) {
            res.send('Book deleted');
        } else {
            res.status(404).send('No book found with this title');
        }
    }
});
});

// 用户操作路由
// 搜索书籍
app.post('/user/search-books', (req, res) => {
const { keyword } = req.body;
const query = SELECT * FROM books WHERE id = ? OR title LIKE ? OR author LIKE ? ;
db.query(query, [keyword, %${keyword}%, %${keyword}%], (err, results) => {
if (err) {
res.status(500).send('Server error');
} else {
res.json(results);
}
});
});
// 添加收藏
// 获取用户的收藏书籍
app.get('/user/favorites/:userId', (req, res) => {
const { userId } = req.params;
const query = SELECT b.id, b.title, b.author FROM favorites f JOIN books b ON f.book_id = b.id WHERE f.user_id = ? ;
db.query(query, [userId], (err, results) => {
if (err) {
res.status(500).send('Server error');
} else {
res.json(results);
}
});
});
// 编辑图书
app.post('/admin/edit-book', (req, res) => {
const { oldTitle, newTitle, newAuthor, newYearPublished } = req.body;


// 简单验证传入数据
if (!oldTitle || !newTitle || !newAuthor || !newYearPublished) {
    return res.status(400).json({ error: 'Invalid data provided' });
}

const query = 'UPDATE books SET title=?, author=?, year_published=? WHERE title=?';
db.query(query, [newTitle, newAuthor, newYearPublished, oldTitle], (err, results) => {
    if (err) {
        console.error('Error editing book:', err);
        return res.status(500).json({ error: 'Failed to edit book. Error details: ' + err.message });
    } 
    // 检查是否有行被更新
    if (results.affectedRows > 0) {
        res.status(200).send('Book edited successfully');
    } else {
        res.status(404).send('No book found with that title for editing');
    }
});
});

app.get('/admin/get-books', (req, res) => {
const query = 'SELECT * FROM books';
db.query(query, (err, results) => {
if (err) {
console.error('Error getting books:', err);
res.status(500).json({ error: 'Failed to get books' });
} else {
res.json(results);
}
});
});

// 获取所有用户
app.get('/admin/get-users', (req, res) => {
const query = 'SELECT * FROM users';
db.query(query, (err, results) => {
if (err) {
console.error('Error getting users:', err);
res.status(500).json({ error: 'Failed to get users' });
} else {
res.json(results);
}
});
});
// 借阅书籍
app.post('/user/borrow-book', (req, res) => {
const { userId, bookId } = req.body;
const query = 'INSERT INTO borrow_records (user_id, book_id, borrow_date) VALUES (?, ?, NOW())';
db.query(query, [userId, bookId], (err, results) => {
if (err) {
console.error('Error borrowing book:', err);
res.status(500).json({ error: 'Failed to borrow book' });
} else {
res.status(200).send('Book borrowed successfully');
}
});
});

// 获取借阅历史
app.get('/user/borrow-history/:userId', (req, res) => {
const { userId } = req.params;
const query = SELECT b.title, b.author, br.borrow_date FROM borrow_records br JOIN books b ON br.book_id = b.id WHERE br.user_id = ? ORDER BY br.borrow_date DESC ;
db.query(query, [userId], (err, results) => {
if (err) {
res.status(500).send('Server error');
} else {
res.json(results);
}
});
});

// 获取所有借阅记录（管理员用）
app.get('/admin/borrow-records', (req, res) => {
const query = SELECT u.username, b.title, b.author, br.borrow_date FROM borrow_records br JOIN users u ON br.user_id = u.id JOIN books b ON br.book_id = b.id ORDER BY br.borrow_date DESC ;
db.query(query, (err, results) => {
if (err) {
res.status(500).send('Server error');
} else {
res.json(results);
}
});
});

// 编辑用户
app.post('/admin/edit-user', (req, res) => {
const { id, username, password, role } = req.body;
const query = 'UPDATE users SET username =?, password =?, role =? WHERE id =?';
db.query(query, [username, password, role, id], (err, results) => {
if (err) {
console.error('编辑用户信息出错，编辑的用户ID：', id);
console.error('错误信息：', err.message);
console.error('错误堆栈：', err.stack);
res.status(500).json({ error: 'Failed to edit user' });
} else {
console.log('用户信息编辑成功，编辑的用户ID：', id);
res.status(200).send('User edited successfully');
}
});
});

// 删除用户
app.post('/admin/delete-user', (req, res) => {
const { id } = req.body;
db.beginTransaction((err => {
if (err) {
console.error('Error starting transaction:', err);
res.status(500).send('Server error starting transaction');
return;
}
// 先删除 borrow_records 表中与该用户相关的记录
const deleteBorrowRecordsQuery = 'DELETE FROM borrow_records WHERE user_id =?';
db.query(deleteBorrowRecordsQuery, [id], (err => {
if (err) {
db.rollback(() => {
console.error('Error deleting related borrow records, rolling back transaction:', err);
res.status(500).send('Server error when deleting related borrow records, transaction rolled back');
});
return;
}
// 再删除 favorites 表中与该用户相关的记录
const deleteFavoritesQuery = 'DELETE FROM favorites WHERE user_id =?';
db.query(deleteFavoritesQuery, [id], (err => {
if (err) {
db.rollback(() => {
console.error('Error deleting related favorites, rolling back transaction:', err);
res.status(500).send('Server error when deleting related favorites, transaction rolled back');
});
return;
}
// 最后执行删除用户操作
const query = 'DELETE FROM users WHERE id =?';
db.query(query, [id], (err, results) => {
if (err) {
db.rollback(() => {
console.error('删除用户出错，要删除的用户ID：', id);
console.error('错误信息：', err.message);
console.error('错误堆栈：', err.stack);
res.status(500).send('Server error, transaction rolled back');
});
} else {
db.commit((err => {
if (err) {
console.error('Error committing transaction:', err);
res.status(500).send('Server error committing transaction');
return;
}
console.log('用户删除成功，删除的用户ID：', id);
res.send('User deleted');
}));
}
});
}));
}));
}));
});
//获取单个用户
app.get('/admin/get-user/:id', (req, res) => {
const { id } = req.params;
const query = 'SELECT * FROM users WHERE id = ?';
db.query(query, [id], (err, results) => {
if (err) {
console.error('Error getting user:', err);
res.status(500).json({ error: 'Failed to get user' });
} else if (results.length === 0) {
res.status(404).json({ error: 'User not found' });
} else {
res.json(results[0]);
}
});
});
// 编辑借阅记录（注意：通常借阅记录不需要编辑，但这里为了完整性提供示例）
app.post('/admin/edit-borrow-record', (req, res) => {
const { id, user_id, book_id, borrow_date } = req.body;
const query = 'UPDATE borrow_records SET user_id = ?, book_id = ?, borrow_date = ? WHERE id = ?';
db.query(query, [user_id, book_id, borrow_date, id], (err, results) => {
if (err) {
console.error('Error editing borrow record:', err);
res.status(500).json({ error: 'Failed to edit borrow record' });
} else {
res.status(200).send('Borrow record edited successfully');
}
});
});

// 删除借阅记录
app.post('/admin/delete-borrow-record', (req, res) => {
const { id } = req.body;
const query = 'DELETE FROM borrow_records WHERE id = ?';
db.query(query, [id], (err, results) => {
if (err) {
res.status(500).send('Server error');
} else {
res.send('Borrow record deleted');
}
});
});
// 获取单个借阅记录详情的路由
app.get('/admin/get-borrow-record/:id', (req, res) => {
const { id } = req.params;
// 验证id是否为有效的数字且大于0
if (!Number.isInteger(Number(id)) || Number(id) <= 0) {
res.status(400).json({ error: 'Invalid borrow record ID format or value' });
return;
}
const query = SELECT * FROM borrow_records WHERE id =? ;
db.query(query, [id], (err, results) => {
if (err) {
console.error('Error getting borrow record:', err);
res.status(500).json({ error: 'Failed to get borrow record. Error details: ' + err.message });
} else if (results.length === 0) {
res.status(404).json({ error: 'Borrow record not found' });
} else {
res.json(results[0]);
}
});
});
// 添加收藏路由
app.post('/user/add-favorite', (req, res) => {
const { userId, bookId } = req.body;
// 先检查该用户是否已经收藏了这本书
const checkFavoriteQuery = 'SELECT * FROM favorites WHERE user_id =? AND book_id =?';
db.query(checkFavoriteQuery, [userId, bookId], (err, results) => {
if (err) {
res.status(500).json({ error: 'Server error' });
} else if (results.length > 0) {
// 已收藏，提示用户
res.status(400).json({ error: 'This book has already been added to favorites' });
} else {
// 可以添加收藏，插入记录到 favorites 表
const insertFavoriteQuery = 'INSERT INTO favorites (user_id, book_id) VALUES (?,?)';
db.query(insertFavoriteQuery, [userId, bookId], (err, results) => {
if (err) {
res.status(500).json({ error: 'Failed to add to favorites' });
} else {
// 返回成功的 JSON 响应
res.status(200).json({ message: 'Book added to favorites successfully' });
}
});
}
});
});

// 删除收藏书籍路由
app.post('/user/delete-favorite', (req, res) => {
const { userId, bookId } = req.body;
const deleteQuery = 'DELETE FROM favorites WHERE user_id =? AND book_id =?';
db.query(deleteQuery, [userId, bookId], (err, results) => {
if (err) {
res.status(500).json({ error: 'Server error when deleting favorite' });
} else if (results.affectedRows === 0) {
// 如果受影响的行数为0，表示没有找到对应的收藏记录，即可能已经不存在该收藏了
res.status(400).json({ error: 'This book is not in your favorites' });
} else {
res.status(200).json({ message: 'Book removed from favorites successfully' });
}
});
});

// 从收藏中借阅书籍
app.post('/user/borrow-from-favorite', (req, res) => {
const { userId, bookId } = req.body;
// 先从收藏中删除这本书
const deleteFavoriteQuery = 'DELETE FROM favorites WHERE user_id = ? AND book_id = ?';
db.query(deleteFavoriteQuery, [userId, bookId], (err, deleteResults) => {
if (err) {
console.error('Error deleting favorite before borrowing:', err);
res.status(500).json({ error: 'Failed to borrow book from favorites' });
return;
}
if (deleteResults.affectedRows === 0) {
res.status(400).json({ error: 'This book is not in your favorites' });
return;
}


    // 然后借阅这本书
    const borrowQuery = 'INSERT INTO borrow_records (user_id, book_id, borrow_date) VALUES (?, ?, NOW())';
    db.query(borrowQuery, [userId, bookId], (err, borrowResults) => {
        if (err) {
            console.error('Error borrowing book:', err);
            res.status(500).json({ error: 'Failed to borrow book' });
        } else {
            // 返回成功的 JSON 响应
            res.status(200).json({ message: 'Book borrowed from favorites successfully' });
        }
    });
});
});

// 监听端口
const PORT = 3000;
app.listen(PORT, () => {
console.log(Server running on port ${PORT});
});在管理员页面的图书管理的中，对保存的图书进行编辑报错Uncaught TypeError: Cannot read properties of null (reading 'value')
at editBook (admin.html:550:57)
at HTMLButtonElement.onclick (admin.html:1:1)，修改代码，让他能够运行