chat-demo-server/routes/message.js

const express = require("express");
const router = express.Router();
const db = require("../db/mysql");
const authMiddleware = require("../middleware/authMiddleware");

// 获取最近联系人的最后一条聊天消息
router.get(
    "/recentChats",
    authMiddleware,
    (req, res) => {    
        const currentUserName = req.user.username;     
        const sql = `
            SELECT
                contact_name,
                content,
                created_at
            FROM (
                SELECT
                    CASE
                        WHEN sender_name = ? THEN receiver_name
                        ELSE sender_name
                    END AS contact_name,
                    content,
                    created_at,
                    ROW_NUMBER() OVER (
                        PARTITION BY
                            CASE
                                WHEN sender_name = ? THEN receiver_name
                                ELSE sender_name
                            END
                        ORDER BY created_at DESC
                    ) AS rn
                FROM chat_mysql.messages
                WHERE sender_name = ?
                OR receiver_name = ?
            ) t
            WHERE rn = 1
            ORDER BY created_at DESC;
        `;
        db.query(sql, [currentUserName, currentUserName, currentUserName, currentUserName], (error, results) => {
            if (error) {
                console.error("数据库查询失败：", error);

                return res.status(500).json({
                    code: 500,
                    message: "查询失败",
                    data: null
                });
            }
            if (results.length > 0) {
                res.json({
                    code: 200,
                    message: "查询成功",
                    data: {
                        recentChats: results.map(message => ({
                            contactName: message.contact_name,
                            content: message.content,
                            createdAt: message.created_at
                        }))
                    },
                });
            } else {
                res.json({
                    code: 401,
                    message: "未找到联系人消息",
                    data: {
                        recentChats: []
                    }
                });
            }
        });

    }
);

// 获取某个联系人的最近n条聊天消息，分页获取
router.post(
    "/contactMessagesInfo",
    authMiddleware,
    (req, res) => {    
        const currentUserName = req.user.username;  
        const { contactName, limit, offset } = req.body;  
        const sql = `
            SELECT
                id,
                sender_name,
                receiver_name,
                content,
                created_at
            FROM chat_mysql.messages
            WHERE (sender_name = ? OR receiver_name = ?) AND (sender_name = ? OR receiver_name = ?)
            ORDER BY created_at DESC LIMIT ? OFFSET ?;
        `;
        db.query(sql, [currentUserName, currentUserName, contactName, contactName, limit, offset], (error, results) => {
            if (error) {
                console.error("数据库查询失败：", error);

                return res.status(500).json({
                    code: 500,
                    message: "查询失败",
                    data: null
                });
            }
            if (results.length > 0) {
                res.json({
                    code: 200,
                    message: "查询成功",
                    data: {
                        messages: results.map(message => ({
                            messageId: message.id,
                            senderName: message.sender_name,
                            receiverName: message.receiver_name,
                            content: message.content,
                            createdAt: message.created_at
                        }))
                    },
                });
            } else {
                res.json({
                    code: 401,
                    message: "未找到联系人消息",
                    data: {
                        messages: []
                    }
                });
            }
        });

    }
);

// 写入聊天消息
router.post(
    "/sendMessage",
    authMiddleware,
    (req, res) => {
    const message = req.body;
    const sql = `
        INSERT INTO messages
        (sender_name, receiver_name, content)
        VALUES (?, ?, ?)
    `;

    db.query(sql, [
        message.senderName,
        message.receiverName,
        message.content
    ], (error, result) => {
        if (error) {
            console.error(error);

            return res.status(500).json({
                code: 500,
                message: "发送消息失败",
                data: null
            });
        }

        res.json({
            code: 200,
            message: "发送消息成功",
            data: null
        });
    });
});

module.exports = router;