ic-ctbu-backend/lib/AIChat/AiAssistantMcp.js

const db = require('../../plugin/DataBase/db')
const { axiosWithQgOutbound } = require('../Lepao/qgOutboundAxios')
const Logger = require('../Logger')
const path = require('path')
const QxsBookList = require('../../apis/QuXuanShu/GetBookList').GetBookList
const EmailTemplate = require('../../plugin/Email/emailTemplate')

const ASSISTANT_UUID = 'e4fe0277-0b1a-41a1-b25f-8b6e4cec3281'

function required(values) {
    return values.some(v => v === '' || v === null || v === undefined)
}

function isProvided(value) {
    return value !== '' && value !== null && value !== undefined
}

function pageArgs({ current = 1, pagesize = 10 } = {}, max = 20) {
    const c = Math.max(1, Number.parseInt(current, 10) || 1)
    const p = Math.min(Math.max(1, Number.parseInt(pagesize, 10) || 10), max)
    return { current: c, pagesize: p, offset: (c - 1) * p }
}

function pagination(page, totalRows) {
    const total = Number(totalRows?.[0]?.total || 0)
    return {
        current: page.current,
        pagesize: page.pagesize,
        total,
        total_pages: Math.ceil(total / page.pagesize)
    }
}

function asJson(data) {
    return JSON.stringify(data)
}

function pad2(value) {
    return String(value).padStart(2, '0')
}

function formatBeijingTime(value) {
    if (value === '' || value === null || value === undefined) return ''

    if (typeof value === 'string' && !/^\d+$/.test(value.trim())) {
        const match = value.trim().match(/^(\d{4})[-/](\d{1,2})[-/](\d{1,2})(?:[ T](\d{1,2}):(\d{1,2})(?::(\d{1,2}))?)?/)
        if (match && !/[zZ]|[+-]\d{2}:?\d{2}$/.test(value.trim())) {
            const [, y, m, d, h = '0', min = '0', s = '0'] = match
            return `${y}-${pad2(m)}-${pad2(d)} ${pad2(h)}:${pad2(min)}:${pad2(s)}`
        }
    }

    const numberValue = typeof value === 'number' || /^\d+$/.test(String(value).trim())
        ? Number(value)
        : null
    const millis = numberValue !== null
        ? (numberValue < 1000000000000 ? numberValue * 1000 : numberValue)
        : new Date(value).getTime()
    if (!Number.isFinite(millis)) return String(value)

    const date = new Date(millis + 8 * 60 * 60 * 1000)
    return `${date.getUTCFullYear()}-${pad2(date.getUTCMonth() + 1)}-${pad2(date.getUTCDate())} ${pad2(date.getUTCHours())}:${pad2(date.getUTCMinutes())}:${pad2(date.getUTCSeconds())}`
}

function parseJson(value, fallback = []) {
    if (!value) return fallback
    if (Array.isArray(value)) return value
    try {
        const parsed = JSON.parse(value)
        return parsed == null ? fallback : parsed
    } catch (_) {
        return fallback
    }
}

function stateText(value, map) {
    return map[value] || String(value)
}

function normalizeText(value, max = 2000) {
    return String(value || '').trim().slice(0, max)
}

function normalizeRunArea(area) {
    if (!isProvided(area)) return ''
    const value = String(area).trim()
    if (value === '随机分配') return ''
    const allowed = new Set([
        '兰花湖校区跑区',
        '主校区北跑区',
        '主校区南跑区',
        '重庆工商大学茶园校区',
        ''
    ])
    return allowed.has(value) ? value : null
}

function mapLepaoAccount(row) {
    return {
        student_num: row.student_num,
        name: row.name || '',
        state: stateText(row.state, { 0: '需登录', 1: '正常', 2: '异常' }),
        area: row.area || '随机分配',
        auto_run: row.auto_run === 1,
        auto_time: row.auto_time,
        today_auto_time: row.today_auto_time,
        target_count: row.target_count,
        total_num: row.total_num,
        term_num: row.term_num,
        auto_day: parseJson(row.auto_day, []),
        notice_type: row.notice_type,
        email: row.email || '',
        notes: row.notes || ''
    }
}

function mapLepaoRecord(row) {
    return {
        ...row,
        time: formatBeijingTime(row.time)
    }
}

function mapCountLedger(row) {
    return {
        ...row,
        created_at: formatBeijingTime(row.created_at)
    }
}

function mapOrder(row) {
    const orderStateMap = {
        0: '待支付',
        1: '已支付',
        2: '已取消',
        3: '异常',
        4: '已退款'
    }
    return {
        order_no: row.orderId,
        state: stateText(row.state, orderStateMap),
        create_time: formatBeijingTime(row.create_time),
        price: row.price,
        pay_type: row.pay_type,
        goods_name: row.goods_name
    }
}

function mapPowerTask(row) {
    const { id, ...task } = row
    return {
        ...task,
        koufei_date: formatBeijingTime(task.koufei_date),
        update_time: formatBeijingTime(task.update_time)
    }
}

function mapPowerRecord(row) {
    return {
        ...row,
        time: formatBeijingTime(row.time),
        change_time: formatBeijingTime(row.change_time)
    }
}

class AiAssistantMcp {
    constructor() {
        this.logger = new Logger(path.join(__dirname, '../../logs/AiAssistantMcp.log'), 'INFO')
        this.emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$/
        this.banEmailList = ['icloud.com']
    }

    async list_lepao_accounts(args) {
        const { sender, student_num, name, area, state, auto_run, current, pagesize } = args
        if (required([sender])) return '缺少 sender'
        const page = pageArgs({ current, pagesize })
        const where = ['create_user = ?']
        const params = [sender]
        if (student_num) {
            where.push('student_num LIKE ?')
            params.push(`%${student_num}%`)
        }
        if (name) {
            where.push('name LIKE ?')
            params.push(`%${name}%`)
        }
        if (area !== undefined && area !== null && area !== '') {
            const normalizedArea = normalizeRunArea(area)
            if (normalizedArea === null) return '跑区不合法'
            where.push('area = ?')
            params.push(normalizedArea)
        }
        if (state !== undefined && state !== null && state !== '' && Number(state) !== -1) {
            where.push('state = ?')
            params.push(state)
        }
        if (auto_run !== undefined && auto_run !== null && auto_run !== '') {
            where.push('auto_run = ?')
            params.push(Number(auto_run))
        }
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM lepao_account
             WHERE ${where.join(' AND ')}`,
            params
        )
        const rows = await db.query(
            `SELECT student_num, name, state, area, auto_run, auto_time, today_auto_time,
                    target_count, total_num, term_num, auto_day, notice_type, email, notes
             FROM lepao_account
             WHERE ${where.join(' AND ')}
             ORDER BY update_time DESC
             LIMIT ? OFFSET ?`,
            [...params, String(page.pagesize), String(page.offset)]
        )
        if (!rows || !totalRows) return '查询失败'
        return asJson({ list: rows.map(mapLepaoAccount), pagination: pagination(page, totalRows) })
    }

    async list_lepao_records(args) {
        const { sender, lepao_account, name, start_time, end_time, result, current, pagesize } = args
        if (required([sender])) return '缺少 sender'
        const page = pageArgs({ current, pagesize })
        const where = ['(r.uuid = ? OR a.create_user = ?)']
        const params = [sender, sender]
        if (lepao_account) {
            where.push('r.lepao_account LIKE ?')
            params.push(`%${lepao_account}%`)
        }
        if (name) {
            where.push('a.name LIKE ?')
            params.push(`%${name}%`)
        }
        if (result) {
            where.push('r.result LIKE ?')
            params.push(`%${result}%`)
        }
        if (start_time) {
            where.push('r.time >= ?')
            params.push(start_time)
        }
        if (end_time) {
            where.push('r.time < ?')
            params.push(end_time)
        }
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM lepao_record r
             LEFT JOIN lepao_account a ON r.lepao_account = a.student_num
             WHERE ${where.join(' AND ')}`,
            params
        )
        const rows = await db.query(
            `SELECT r.public_id, r.time, r.result, r.run_mode, r.lepao_account, a.name
             FROM lepao_record r
             LEFT JOIN lepao_account a ON r.lepao_account = a.student_num
             WHERE ${where.join(' AND ')}
             ORDER BY r.id DESC
             LIMIT ? OFFSET ?`,
            [...params, String(page.pagesize), String(page.offset)]
        )
        if (!rows || !totalRows) return '查询失败'
        return asJson({ list: rows.map(mapLepaoRecord), pagination: pagination(page, totalRows) })
    }

    async list_lepao_count_ledger(args) {
        const { sender, biz_type, remark, start_time, end_time, current, pagesize } = args
        if (required([sender])) return '缺少 sender'
        const page = pageArgs({ current, pagesize })
        const where = ['user_uuid = ?']
        const params = [sender]
        if (biz_type) {
            where.push('biz_type = ?')
            params.push(biz_type)
        }
        if (remark) {
            where.push('remark LIKE ?')
            params.push(`%${remark}%`)
        }
        if (start_time) {
            where.push('created_at >= FROM_UNIXTIME(? / 1000)')
            params.push(start_time)
        }
        if (end_time) {
            where.push('created_at <= FROM_UNIXTIME(? / 1000)')
            params.push(end_time)
        }
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM lepao_count_ledger
             WHERE ${where.join(' AND ')}`,
            params
        )
        const rows = await db.query(
            `SELECT delta, balance_before, balance_after, biz_type, remark,
                    UNIX_TIMESTAMP(created_at) * 1000 AS created_at
             FROM lepao_count_ledger
             WHERE ${where.join(' AND ')}
             ORDER BY id DESC
             LIMIT ? OFFSET ?`,
            [...params, String(page.pagesize), String(page.offset)]
        )
        if (!rows || !totalRows) return '查询失败'
        return asJson({ list: rows.map(mapCountLedger), pagination: pagination(page, totalRows) })
    }

    async list_goods(args) {
        const { keyword, category, current, pagesize } = args
        const page = pageArgs({ current, pagesize })
        const where = ['state = 1']
        const params = []
        if (keyword) {
            where.push('name LIKE ?')
            params.push(`%${keyword}%`)
        }
        if (category) {
            where.push('category = ?')
            params.push(category)
        }
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM goods
             WHERE ${where.join(' AND ')}`,
            params
        )
        const rows = await db.query(
            `SELECT name, price, num, \`limit\`, description, category, features
             FROM goods
             WHERE ${where.join(' AND ')}
             ORDER BY id DESC
             LIMIT ? OFFSET ?`,
            [...params, String(page.pagesize), String(page.offset)]
        )
        if (!rows || !totalRows) return '查询失败'
        return asJson({
            list: rows.map(r => ({ ...r, features: parseJson(r.features, []) })),
            pagination: pagination(page, totalRows)
        })
    }

    async list_my_orders(args) {
        const { sender, state, order_id, current, pagesize } = args
        if (required([sender])) return '缺少 sender'
        const page = pageArgs({ current, pagesize })
        const where = ['o.create_user = ?']
        const params = [sender]
        if (state !== undefined && state !== null && state !== '' && Number(state) !== -1) {
            where.push('o.state = ?')
            params.push(state)
        }
        if (order_id) {
            where.push('o.orderId = ?')
            params.push(order_id)
        }
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM orders o
             JOIN goods g ON o.goods_id = g.id
             WHERE ${where.join(' AND ')}`,
            params
        )
        const rows = await db.query(
            `SELECT o.orderId, o.state, o.create_time, o.price, o.pay_type, g.name AS goods_name
             FROM orders o
             JOIN goods g ON o.goods_id = g.id
             WHERE ${where.join(' AND ')}
             ORDER BY o.id DESC
             LIMIT ? OFFSET ?`,
            [...params, String(page.pagesize), String(page.offset)]
        )
        if (!rows || !totalRows) return '查询失败'
        return asJson({
            list: rows.map(mapOrder),
            pagination: pagination(page, totalRows)
        })
    }

    async save_lepao_account(args) {
        const { sender, student_num } = args
        if (required([sender, student_num])) return '缺少 sender 或 student_num'

        const existRows = await db.query(
            `SELECT id, create_user, student_num, email, area, auto_time, auto_run, target_count, auto_day, notice_type, notes
             FROM lepao_account
             WHERE student_num = ?
             LIMIT 1`,
            [student_num]
        )
        if (!existRows) return '查询账号失败'
        if (existRows.length > 0 && existRows[0].create_user && existRows[0].create_user !== sender) {
            return '该乐跑账号已被其他用户绑定'
        }

        const fields = []
        const params = []
        const mutable = [
            ['email', 'email'],
            ['auto_time', 'auto_time'],
            ['auto_run', 'auto_run'],
            ['target_count', 'target_count'],
            ['notice_type', 'notice_type'],
            ['notes', 'notes']
        ]

        if (isProvided(args.area)) {
            const area = normalizeRunArea(args.area)
            if (area === null) return '跑区不合法'
            fields.push('area = ?')
            params.push(area)
        }

        if (isProvided(args.email) && !this.emailRegex.test(args.email)) return '邮箱格式不正确'
        if (isProvided(args.notice_type) && !['email', 'none', 'bot'].includes(args.notice_type)) return '通知方式不合法'
        if (isProvided(args.auto_time)) {
            const autoTime = Number(args.auto_time)
            if (!Number.isInteger(autoTime) || autoTime < -1 || autoTime > 23) return '自动乐跑时间不合法'
            args.auto_time = autoTime
        }
        if (isProvided(args.auto_run) && ![0, 1].includes(Number(args.auto_run))) return 'auto_run 只能为 0 或 1'
        if (isProvided(args.auto_day)) {
            if (!Array.isArray(args.auto_day) || !args.auto_day.every(v => Number.isInteger(v) && v >= 0 && v <= 6)) return '自动乐跑星期不合法'
            fields.push('auto_day = ?')
            params.push(JSON.stringify(args.auto_day))
        }
        for (const [argName, column] of mutable) {
            if (isProvided(args[argName])) {
                fields.push(`${column} = ?`)
                params.push(args[argName])
            }
        }

        const now = Date.now()
        let result
        if (existRows.length > 0) {
            if (fields.length === 0) return '没有需要修改的字段'
            fields.push('update_time = ?', 'create_user = ?')
            params.push(now, sender)
            result = await db.query(
                `UPDATE lepao_account SET ${fields.join(', ')} WHERE student_num = ? AND (create_user = ? OR create_user IS NULL)`,
                [...params, student_num, sender]
            )
        } else {
            const area = isProvided(args.area) ? normalizeRunArea(args.area) : ''
            if (area === null) return '跑区不合法'
            const autoDay = isProvided(args.auto_day) ? args.auto_day : [0, 1, 2, 3, 4, 5, 6]
            if (!Array.isArray(autoDay) || !autoDay.every(v => Number.isInteger(v) && v >= 0 && v <= 6)) return '自动乐跑星期不合法'
            const email = args.email || ''
            if (email && !this.emailRegex.test(email)) return '邮箱格式不正确'
            result = await db.query(
                `INSERT INTO lepao_account
                 (student_num, email, area, auto_time, auto_run, target_count, create_user, create_time, update_time, notes, auto_day, notice_type)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
                [
                    student_num,
                    email,
                    area,
                    isProvided(args.auto_time) ? Number(args.auto_time) : -1,
                    isProvided(args.auto_run) ? Number(args.auto_run) : 1,
                    isProvided(args.target_count) ? args.target_count : 30,
                    sender,
                    now,
                    now,
                    args.notes || '',
                    JSON.stringify(autoDay),
                    args.notice_type || 'email'
                ]
            )
            const bindCode = Math.random().toString(36).slice(2, 8).toUpperCase()
            await db.query('INSERT IGNORE INTO lepao_extra (student_num, bind_code) VALUES (?, ?)', [student_num, bindCode])
        }
        if (!result || result.affectedRows < 1) return '保存失败或无权修改'
        return asJson({ success: true, student_num })
    }

    async save_power_task(args) {
        const { sender, task_id, area, building, room } = args
        if (required([sender])) return '缺少 sender'

        const now = Date.now()
        let existing = []
        if (task_id) {
            existing = await db.query('SELECT id, area, building, room FROM power_task WHERE id = ? AND create_user = ? LIMIT 1', [task_id, sender])
        } else if ([area, building, room].every(isProvided)) {
            existing = await db.query(
                'SELECT id, area, building, room FROM power_task WHERE create_user = ? AND area = ? AND building = ? AND room = ? LIMIT 1',
                [sender, area, building, room]
            )
        }
        if (!existing) return '查询电费任务失败'

        const fields = []
        const params = []
        for (const key of ['area', 'building', 'room', 'email', 'lowest', 'notes']) {
            if (isProvided(args[key])) {
                fields.push(`${key} = ?`)
                params.push(args[key])
            }
        }

        let result
        if (existing.length > 0) {
            if (fields.length === 0) return '没有需要修改的字段'
            fields.push('is_notice = 0', 'update_time = ?')
            params.push(now)
            result = await db.query(
                `UPDATE power_task SET ${fields.join(', ')} WHERE id = ? AND create_user = ?`,
                [...params, existing[0].id, sender]
            )
        } else {
            if (required([area, building, room, args.lowest])) return '新增电费任务需提供 area、building、room、lowest'
            let balance = ''
            let koufeiDate = ''
            try {
                const endpoint = `https://hqpay.ctbu.edu.cn/weixin/ashx/frmuser.ashx?test=lastlist&pid=${room}&dyid=${building}`
                const apiRes = await axiosWithQgOutbound({ method: 'get', url: endpoint, timeout: 15000, logger: this.logger, scene: 'AiAssistantPowerAccount' })
                balance = apiRes?.data?.[0]?.[1] ?? ''
                koufeiDate = apiRes?.data?.[0]?.[2] ?? ''
            } catch (_) { }
            result = await db.query(
                'INSERT INTO power_task (create_user, create_time, update_time, area, building, room, email, lowest, notes, balance, koufei_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
                [sender, now, now, area, building, room, args.email || '', args.lowest, args.notes || '', balance, koufeiDate]
            )
        }
        if (!result || result.affectedRows < 1) return '保存失败或无权修改'
        return asJson({ success: true, area, building, room })
    }

    async list_power_bills(args) {
        const { sender, area, building, room, current, pagesize } = args
        if (required([sender])) return 'missing sender'
        const page = pageArgs({ current, pagesize })
        const taskWhere = ['create_user = ?']
        const taskParams = [sender]
        if (area) {
            taskWhere.push('area = ?')
            taskParams.push(area)
        }
        if (building) {
            taskWhere.push('building = ?')
            taskParams.push(building)
        }
        if (room) {
            taskWhere.push('room = ?')
            taskParams.push(room)
        }
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM power_task
             WHERE ${taskWhere.join(' AND ')}`,
            taskParams
        )
        const tasks = await db.query(
            `SELECT id, area, building, room, balance, lowest, koufei_date, update_time, notes
             FROM power_task
             WHERE ${taskWhere.join(' AND ')}
             ORDER BY id DESC
             LIMIT ? OFFSET ?`,
            [...taskParams, String(page.pagesize), String(page.offset)]
        )
        if (!tasks || !totalRows) return 'query failed'
        return asJson({
            tasks: tasks.map(mapPowerTask),
            pagination: pagination(page, totalRows)
        })
    }

    async list_power_bill_records(args) {
        const { sender, area, building, room, current, pagesize } = args
        if (required([sender, area, building, room])) return 'missing sender, area, building or room'
        const page = pageArgs({ current, pagesize })

        const tasks = await db.query(
            `SELECT id, area, building, room, balance, lowest, koufei_date, update_time, notes
             FROM power_task
             WHERE create_user = ? AND area = ? AND building = ? AND room = ?
             ORDER BY id DESC
             LIMIT 1`,
            [sender, area, building, room]
        )
        if (!tasks) return 'query failed'
        if (tasks.length === 0) return asJson({ task: null, records: [], pagination: pagination(page, [{ total: 0 }]) })

        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM power_change_record
             WHERE task_id = ?`,
            [tasks[0].id]
        )
        const records = await db.query(
            `SELECT time, balance, old_balance, change_time
             FROM power_change_record
             WHERE task_id = ?
             ORDER BY id DESC
             LIMIT ? OFFSET ?`,
            [tasks[0].id, String(page.pagesize), String(page.offset)]
        )
        if (!records || !totalRows) return 'query failed'
        return asJson({
            task: mapPowerTask(tasks[0]),
            records: records.map(mapPowerRecord),
            pagination: pagination(page, totalRows)
        })
    }

    async query_qxs_book_list(args) {
        const { sender, username, password, current, pagesize } = args
        if (required([sender, username, password])) return '缺少 sender、username 或 password'
        try {
            const page = pageArgs({ current, pagesize })
            const qxs = new QxsBookList()
            const { accessToken } = await qxs.qxsLogin(username, password)
            const { userInfo } = await qxs.qsxUserInfo(accessToken)
            const { bookList } = await qxs.qsxGetList(accessToken, userInfo.termCode)
            const list = bookList || []
            return asJson({
                user: {
                    realname: userInfo.userName,
                    termName: userInfo.termName
                },
                list: list.slice(page.offset, page.offset + page.pagesize).map(book => ({
                    title: book.title || book.bookName || '',
                    isbn: book.isbn || '',
                    author: book.author || '',
                    publisher: book.publisher || '',
                    className: book.className || ''
                })),
                pagination: pagination(page, [{ total: list.length }])
            })
        } catch (err) {
            return err.message || '查询趣选书书单失败'
        }
    }

    async submit_work_order(args) {
        const { sender, title, content, email = '', files = [] } = args
        if (required([sender, title, content])) return '缺少 sender、title 或 content'
        if (email && !this.emailRegex.test(email)) return '邮箱格式不正确'
        if (email && this.banEmailList.includes(email.split('@')[1].toLowerCase())) return '暂不支持该邮箱域名'

        const safeTitle = normalizeText(title, 80)
        const safeContent = normalizeText(content, 4000)
        const safeFiles = Array.isArray(files) ? files.map(item => String(item || '').trim()).filter(Boolean).slice(0, 6) : []
        if (!safeTitle || !safeContent) return '标题或内容不能为空'

        const time = Date.now()
        const msg = [
            {
                time,
                content: safeContent,
                files: safeFiles,
                uuid: sender,
                type: 'user'
            },
            {
                time,
                content: '您的问题已收到，我们将尽快处理，请耐心等待。',
                uuid: ASSISTANT_UUID,
                type: 'system'
            }
        ]
        const result = await db.query(
            'INSERT INTO work_order (title, email, msg, create_user, create_time, update_time) VALUES (?, ?, ?, ?, ?, ?)',
            [safeTitle, email, msg, sender, time, time]
        )
        if (!result || result.affectedRows !== 1) return '提交工单失败'

        const kefuRows = await db.query(`
            SELECT email FROM users
            WHERE JSON_CONTAINS(permission, '"admin"') OR JSON_CONTAINS(permission, '"service"')
        `)
        const emails = [...new Set((kefuRows || []).map(row => row.email).filter(Boolean))]
        for (const targetEmail of emails) {
            await EmailTemplate.orderNewReply(targetEmail, { id: result.insertId, content: safeContent, files: safeFiles })
        }
        return asJson({ success: true, message: '工单提交成功，可在工单列表查看处理进度' })
    }
}

module.exports.AI_ASSISTANT_MCP = new AiAssistantMcp()