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 { insertLedgerRecord } = require('../Lepao/CountLedger')

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 normalizeNumber(value) {
    if (!isProvided(value)) return null
    const numberValue = Number(value)
    return Number.isFinite(numberValue) ? numberValue : null
}

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)
    }
}

function mapSendCountRequest(row) {
    const { id, sender_uuid, receiver_user_id, ...item } = row
    return {
        request_id: id,
        ...item,
        created_at: formatBeijingTime(item.created_at),
        reviewed_at: formatBeijingTime(item.reviewed_at)
    }
}

function mapWorkOrder(row) {
    const { id, ...item } = row
    return {
        work_order_id: id,
        ...item,
        create_time: formatBeijingTime(item.create_time),
        update_time: formatBeijingTime(item.update_time)
    }
}

function mapWorkOrderMessage(msg) {
    return {
        ...msg,
        time: formatBeijingTime(msg.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 get_lepao_count_balance(args) {
        const { sender } = args
        if (required([sender])) return '缺少 sender'

        const rows = await db.query(
            'SELECT username, lepao_count FROM users WHERE uuid = ? LIMIT 1',
            [sender]
        )
        if (!rows || rows.length !== 1) return '获取用户乐跑次数余额失败'

        return asJson({
            username: rows[0].username,
            lepao_count: Number(rows[0].lepao_count || 0)
        })
    }

    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, total_num, 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 existing = existRows[0] || {}
        const targetCount = isProvided(args.target_count)
            ? normalizeNumber(args.target_count)
            : (existRows.length > 0 ? normalizeNumber(existing.target_count) : 30)
        if (targetCount === null || targetCount < 0 || targetCount > 99) return '乐跑目标次数不在合法范围内'
        args.target_count = targetCount
        const noticeType = isProvided(args.notice_type) ? args.notice_type : (existing.notice_type || 'email')
        const email = isProvided(args.email) ? String(args.email).trim() : (existing.email || '')
        if (noticeType === 'email') {
            if (!this.emailRegex.test(email)) return '邮箱格式不正确'
            const emailDomain = email.split('@')[1].toLowerCase()
            if (this.banEmailList.includes(emailDomain)) return `暂不支持使用 ${emailDomain} 域名的邮箱，请更换其他邮箱后重试`
        } else if (email && !this.emailRegex.test(email)) {
            return '邮箱格式不正确'
        }

        const autoRun = isProvided(args.auto_run) ? Number(args.auto_run) : (existRows.length > 0 ? Number(existing.auto_run) : 1)
        if (![0, 1].includes(autoRun)) return 'auto_run 只能为 0 或 1'
        args.auto_run = autoRun

        const autoDay = isProvided(args.auto_day)
            ? args.auto_day
            : (existRows.length > 0 ? parseJson(existing.auto_day, []) : [0, 1, 2, 3, 4, 5, 6])
        if (autoRun === 1 && (!Array.isArray(autoDay) || !autoDay.every(v => Number.isInteger(v) && v >= 0 && v <= 6))) return '自动乐跑星期不合法'
        if (isProvided(args.auto_day)) args.auto_day = autoDay

        const totalNum = normalizeNumber(existing.total_num) || 0
        if (autoRun === 1 && totalNum >= targetCount && targetCount !== 0) 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.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_day)) {

            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' })
                if (!apiRes || !apiRes.data || !apiRes.data[0]) return '获取电费信息失败，请稍后再试'
                balance = apiRes.data[0][1]
                koufeiDate = apiRes.data[0][2]
            } catch (_) {
                return '获取电费信息失败，请稍后再试'
            }
            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 send_lepao_count(args) {
        let { sender, username, count } = args
        username = typeof username === 'string' ? username.trim() : username
        count = Number(count)

        if (required([sender, username]) || Number.isNaN(count)) return '缺少 sender、username 或 count'
        if (!Number.isInteger(count) || count < 1 || count > 9999) return '超出赠送的次数范围，请重新选择赠送次数'

        const conn = await db.connect()
        try {
            await conn.beginTransaction()

            const [senderRows] = await conn.execute(
                'SELECT id, username, lepao_count, COALESCE(send_count_auto_approve, 0) AS send_count_auto_approve FROM users WHERE uuid = ?',
                [sender]
            )
            if (!senderRows || senderRows.length !== 1) {
                await conn.rollback()
                return '获取用户信息失败'
            }

            const [targetRows] = await conn.execute(
                'SELECT id, uuid FROM users WHERE username = ?',
                [username]
            )
            if (!targetRows || targetRows.length !== 1) {
                await conn.rollback()
                return '未找到接收用户，请检查用户名是否正确'
            }
            if (targetRows[0].uuid === sender) {
                await conn.rollback()
                return '不能给自己赠送次数'
            }

            const [decResult] = await conn.execute(
                'UPDATE users SET lepao_count = lepao_count - ? WHERE uuid = ? AND lepao_count >= ?',
                [count, sender, count]
            )
            if (!decResult || decResult.affectedRows !== 1) {
                await conn.rollback()
                return '剩余乐跑次数不足，请购买后再赠送'
            }

            const senderUsername = senderRows[0].username
            const senderLepaoBefore = Number(senderRows[0].lepao_count || 0)
            const autoApprove = Number(senderRows[0].send_count_auto_approve) === 1

            if (autoApprove) {
                const [recvRows] = await conn.execute(
                    'SELECT uuid, lepao_count FROM users WHERE id = ? FOR UPDATE',
                    [targetRows[0].id]
                )
                if (!recvRows || recvRows.length !== 1) {
                    await conn.rollback()
                    return '未找到接收用户，请检查用户名是否正确'
                }

                const receiverUuid = recvRows[0].uuid
                const beforeRecv = Number(recvRows[0].lepao_count || 0)
                const [incResult] = await conn.execute(
                    'UPDATE users SET lepao_count = lepao_count + ? WHERE id = ?',
                    [count, targetRows[0].id]
                )
                if (!incResult || incResult.affectedRows !== 1) {
                    await conn.rollback()
                    return '接收方入账失败，请稍后再试'
                }

                const [insertResult] = await conn.execute(
                    `INSERT INTO lepao_send_count_request
                    (sender_uuid, receiver_user_id, count, status, created_at, reviewed_at, reviewer_uuid)
                    VALUES (?, ?, ?, 'approved', NOW(), NOW(), NULL)`,
                    [sender, targetRows[0].id, count]
                )
                if (!insertResult || insertResult.affectedRows !== 1) {
                    await conn.rollback()
                    return '记录赠送失败，请稍后再试'
                }

                const requestId = insertResult.insertId
                await insertLedgerRecord({
                    executor: conn,
                    userUuid: sender,
                    delta: -count,
                    balanceBefore: senderLepaoBefore,
                    balanceAfter: senderLepaoBefore - count,
                    bizType: 'gift_send_lock',
                    bizId: `send_request:${requestId}`,
                    remark: `向${username}赠送${count}次`
                })
                await insertLedgerRecord({
                    executor: conn,
                    userUuid: receiverUuid,
                    delta: count,
                    balanceBefore: beforeRecv,
                    balanceAfter: beforeRecv + count,
                    bizType: 'gift_receive',
                    bizId: `send_request:${requestId}`,
                    operatorUuid: null,
                    remark: `${senderUsername}赠送${count}次`
                })

                await conn.commit()
                const reviewTime = Date.now()
                Promise.resolve().then(async () => {
                    try {
                        const infoRows = await db.query('SELECT email FROM users WHERE id = ?', [targetRows[0].id])
                        if (infoRows && infoRows.length === 1 && infoRows[0].email) {
                            await EmailTemplate.sendCountRequestApproved(infoRows[0].email, {
                                requestId,
                                senderUsername,
                                count,
                                reviewTime
                            })
                        }
                    } catch (mailErr) {
                        this.logger.error(`[AiAssistantMcp][SendCount][auto][${requestId}] notify failed: ${mailErr.message || mailErr}`)
                    }
                })

                return asJson({ success: true, status: 'approved', request_id: requestId, message: '赠送成功，对方已到账' })
            }

            const [insertResult] = await conn.execute(
                `INSERT INTO lepao_send_count_request
                (sender_uuid, receiver_user_id, count, status, created_at)
                VALUES (?, ?, ?, 'pending', NOW())`,
                [sender, targetRows[0].id, count]
            )
            if (!insertResult || insertResult.affectedRows !== 1) {
                await conn.rollback()
                return '提交赠送审核失败，请稍后再试'
            }

            const requestId = insertResult.insertId
            await insertLedgerRecord({
                executor: conn,
                userUuid: sender,
                delta: -count,
                balanceBefore: senderLepaoBefore,
                balanceAfter: senderLepaoBefore - count,
                bizType: 'gift_send_lock',
                bizId: `send_request:${requestId}`,
                remark: `向${username}赠送${count}次`
            })

            await conn.commit()
            const createTime = Date.now()
            Promise.resolve().then(async () => {
                try {
                    const adminRows = await db.query(`
                        SELECT email
                        FROM users
                        WHERE email IS NOT NULL
                          AND email <> ''
                          AND (JSON_CONTAINS(permission, '"admin"') OR JSON_CONTAINS(permission, '"service"'))
                    `)
                    const emails = [...new Set((adminRows || []).map(row => row.email).filter(Boolean))]
                    for (const email of emails) {
                        await EmailTemplate.sendCountRequestNotifyAdmins(email, {
                            requestId,
                            senderUsername,
                            receiverUsername: username,
                            count,
                            createTime
                        })
                    }
                } catch (mailErr) {
                    this.logger.error(`[AiAssistantMcp][SendCount][pending][${requestId}] notify failed: ${mailErr.message || mailErr}`)
                }
            })

            return asJson({ success: true, status: 'pending', request_id: requestId, message: '已提交审核，审核通过后接收方将到账' })
        } catch (err) {
            try { await conn.rollback() } catch (_) { }
            this.logger.error(`MCP赠送乐跑次数失败：${err.stack || err}`)
            return '赠送次数失败，请稍后再试'
        } finally {
            if (conn?.connection && typeof conn.connection.release === 'function' && typeof conn?.release === 'function') {
                conn.release()
            }
        }
    }

    async list_send_count_requests(args) {
        const { sender, direction, status, current, pagesize } = args
        if (required([sender])) return '缺少 sender'

        if (direction && !['sent', 'received'].includes(direction)) return 'direction 参数错误'
        if (status && !['pending', 'approved', 'rejected'].includes(status)) return 'status 参数错误'

        const page = pageArgs({ current, pagesize })
        const userRows = await db.query('SELECT id FROM users WHERE uuid = ?', [sender])
        if (!userRows || userRows.length !== 1) return '获取用户信息失败'

        const userId = userRows[0].id
        let sql = `
            SELECT
                r.id,
                r.sender_uuid,
                su.username AS sender_username,
                su.avatar AS sender_avatar,
                r.receiver_user_id,
                ru.username AS receiver_username,
                ru.avatar AS receiver_avatar,
                CASE WHEN r.sender_uuid = ? THEN 'sent' ELSE 'received' END AS direction,
                CASE WHEN r.sender_uuid = ? THEN ru.username ELSE su.username END AS counterparty_username,
                CASE WHEN r.sender_uuid = ? THEN ru.avatar ELSE su.avatar END AS counterparty_avatar,
                r.count,
                r.status,
                r.created_at,
                r.reviewed_at,
                r.reject_reason
            FROM lepao_send_count_request r
            LEFT JOIN users su ON su.uuid = r.sender_uuid
            LEFT JOIN users ru ON ru.id = r.receiver_user_id
            WHERE (r.sender_uuid = ? OR r.receiver_user_id = ?)
        `
        let countSql = `
            SELECT COUNT(*) AS total
            FROM lepao_send_count_request r
            WHERE (r.sender_uuid = ? OR r.receiver_user_id = ?)
        `
        const params = [sender, sender, sender, sender, userId]
        const countParams = [sender, userId]

        if (direction === 'sent') {
            sql += ' AND r.sender_uuid = ?'
            countSql += ' AND r.sender_uuid = ?'
            params.push(sender)
            countParams.push(sender)
        }
        if (direction === 'received') {
            sql += ' AND r.receiver_user_id = ?'
            countSql += ' AND r.receiver_user_id = ?'
            params.push(userId)
            countParams.push(userId)
        }
        if (status) {
            sql += ' AND r.status = ?'
            countSql += ' AND r.status = ?'
            params.push(status)
            countParams.push(status)
        }

        sql += ' ORDER BY r.id DESC LIMIT ? OFFSET ?'
        params.push(String(page.pagesize), String(page.offset))

        const rows = await db.query(sql, params)
        const totalRows = await db.query(countSql, countParams)
        if (!rows || !totalRows) return '获取赠送记录失败'

        return asJson({
            list: rows.map(mapSendCountRequest),
            pagination: pagination(page, totalRows)
        })
    }

    async list_work_orders(args) {
        const { sender, current, pagesize } = args
        if (required([sender])) return '缺少 sender'
        const page = pageArgs({ current, pagesize })

        const rows = await db.query(
            `SELECT id, title, state, email, create_time, update_time
             FROM work_order
             WHERE create_user = ?
             ORDER BY update_time DESC
             LIMIT ? OFFSET ?`,
            [sender, String(page.pagesize), String(page.offset)]
        )
        const totalRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM work_order
             WHERE create_user = ?`,
            [sender]
        )
        if (!rows || !totalRows) return '查询工单列表失败'

        return asJson({
            list: rows.map(mapWorkOrder),
            pagination: pagination(page, totalRows)
        })
    }

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

        const rows = await db.query(
            `SELECT id, title, msg, state, email, create_time, update_time
             FROM work_order
             WHERE create_user = ? AND id = ?`,
            [sender, work_order_id]
        )
        if (!rows || rows.length !== 1) return '工单异常，请提交新工单'

        const order = mapWorkOrder(rows[0])
        const msgList = Array.isArray(rows[0].msg) ? rows[0].msg : parseJson(rows[0].msg, [])
        const uuidList = [...new Set(msgList.map(msg => msg?.uuid).filter(Boolean))]
        let userInfo = {}
        if (uuidList.length > 0) {
            const placeholders = uuidList.map(() => '?').join(', ')
            const userRows = await db.query(`SELECT uuid, username, avatar FROM users WHERE uuid IN (${placeholders})`, uuidList)
            if (!userRows || userRows.length === 0) return '用户信息获取失败'
            userRows.forEach(user => {
                userInfo[user.uuid] = {
                    username: user.username,
                    avatar: user.avatar
                }
            })
        }

        return asJson({
            ...order,
            msg: msgList.map(mapWorkOrderMessage),
            userInfo
        })
    }

    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()