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

const crypto = require('crypto')
const db = require('../../plugin/DataBase/db')
const Logger = require('../Logger')

const ACTIVE_STATE = 1
const DELETED_STATE = 2
const ASSISTANT_UUID = 'e4fe0277-0b1a-41a1-b25f-8b6e4cec3281'
const DEFAULT_TITLE = '新对话'
const MAX_MESSAGE_IMAGES = 3
const logger = new Logger()

function toPositiveInt(value, fallback = null) {
    const n = Number(value)
    if (!Number.isInteger(n) || n <= 0) return fallback
    return n
}

function normalizePage(current = 1, pagesize = 20, maxPageSize = 50) {
    const c = toPositiveInt(current, 1)
    const p = Math.min(toPositiveInt(pagesize, 20), maxPageSize)
    return { current: c, pagesize: p, offset: (c - 1) * p }
}

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

function normalizeChannel(channel) {
    return channel === 'wechat' ? 'wechat' : 'web'
}

function normalizeImages(images) {
    if (!Array.isArray(images)) return []
    return images
        .map((item) => {
            if (typeof item === 'string') return item
            return item?.url || item?.response?.data?.picturePath || item?.response?.picturePath || ''
        })
        .map((item) => String(item || '').trim())
        .filter(Boolean)
        .slice(0, MAX_MESSAGE_IMAGES)
}

function buildPreview(content, images = []) {
    const text = normalizeText(content, 160).replace(/\s+/g, ' ')
    if (text) return text
    return images.length > 0 ? `[图片] x${images.length}` : ''
}

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

function normalizeMessage(row) {
    return {
        ...row,
        channel: normalizeChannel(row.channel),
        images: parseImages(row.images)
    }
}

function normalizeConversation(row) {
    if (!row) return row
    const channel = normalizeChannel(row.channel)
    return {
        ...row,
        channel,
        readonly: channel === 'wechat'
    }
}

async function generateConversationNo() {
    for (let i = 0; i < 8; i++) {
        const conversationNo = String(crypto.randomInt(100000000000, 999999999999))
        const rows = await db.query('SELECT id FROM ai_chat_conversation WHERE conversation_no = ? LIMIT 1', [conversationNo])
        if (!rows || rows.length === 0) return conversationNo
    }
    return `${String(Date.now()).slice(1)}${crypto.randomInt(100, 999)}`
}

async function assertUserConversation(conversationId, uuid) {
    const id = toPositiveInt(conversationId)
    if (!id) return null
    const rows = await db.query(
        'SELECT id, conversation_no, create_user, title, channel, state, last_message_preview, last_message_time, create_time, update_time FROM ai_chat_conversation WHERE id = ? AND create_user = ? AND state = ? LIMIT 1',
        [id, uuid, ACTIVE_STATE]
    )
    return rows && rows.length === 1 ? normalizeConversation(rows[0]) : null
}

class ConversationService {
    async listUserConversations({ uuid, current, pagesize }) {
        const page = normalizePage(current, pagesize)
        const rows = await db.query(
            `SELECT id, conversation_no, title, channel, state, last_message_preview, last_message_time, create_time, update_time
             FROM ai_chat_conversation
             WHERE create_user = ? AND state = ?
             ORDER BY update_time DESC
             LIMIT ? OFFSET ?`,
            [uuid, ACTIVE_STATE, String(page.pagesize), String(page.offset)]
        )
        const countRows = await db.query(
            'SELECT COUNT(*) AS total FROM ai_chat_conversation WHERE create_user = ? AND state = ?',
            [uuid, ACTIVE_STATE]
        )
        return {
            data: (rows || []).map(normalizeConversation),
            pagination: {
                current: page.current,
                pagesize: page.pagesize,
                total: Number(countRows?.[0]?.total || 0)
            }
        }
    }

    async createConversation({ uuid, title, channel = 'web' }) {
        const safeChannel = normalizeChannel(channel)
        const emptyRows = await db.query(
            `SELECT id, conversation_no, title, channel, state, last_message_preview, last_message_time, create_time, update_time
             FROM ai_chat_conversation
             WHERE create_user = ? AND state = ? AND last_message_time = 0 AND channel = ?
             ORDER BY update_time DESC
             LIMIT 1`,
            [uuid, ACTIVE_STATE, safeChannel]
        )
        if (emptyRows && emptyRows.length === 1) {
            return normalizeConversation({ ...emptyRows[0], reused: true })
        }

        const now = Date.now()
        const safeTitle = normalizeText(title, 80) || DEFAULT_TITLE
        const conversationNo = await generateConversationNo()
        const result = await db.query(
            `INSERT INTO ai_chat_conversation
             (conversation_no, create_user, title, channel, state, last_message_preview, last_message_time, create_time, update_time)
             VALUES (?, ?, ?, ?, ?, '', 0, ?, ?)`,
            [conversationNo, uuid, safeTitle, safeChannel, ACTIVE_STATE, now, now]
        )
        return normalizeConversation({
            id: result?.insertId,
            conversation_no: conversationNo,
            title: safeTitle,
            channel: safeChannel,
            state: ACTIVE_STATE,
            last_message_preview: '',
            last_message_time: 0,
            create_time: now,
            update_time: now,
            reused: false
        })
    }

    async getOrCreateChannelConversation({ uuid, channel, title }) {
        const safeChannel = normalizeChannel(channel)
        const rows = await db.query(
            `SELECT id, conversation_no, title, channel, state, last_message_preview, last_message_time, create_time, update_time
             FROM ai_chat_conversation
             WHERE create_user = ? AND state = ? AND channel = ?
             ORDER BY create_time ASC
             LIMIT 1`,
            [uuid, ACTIVE_STATE, safeChannel]
        )
        if (rows && rows.length === 1) return normalizeConversation(rows[0])
        return this.createConversation({ uuid, title, channel: safeChannel })
    }

    async deleteConversation({ uuid, conversationId }) {
        const now = Date.now()
        const result = await db.query(
            'UPDATE ai_chat_conversation SET state = ?, update_time = ? WHERE id = ? AND create_user = ? AND state = ?',
            [DELETED_STATE, now, conversationId, uuid, ACTIVE_STATE]
        )
        return result?.affectedRows === 1
    }

    async listMessages({ uuid, conversationId, afterId = 0, current, pagesize }) {
        const conv = await assertUserConversation(conversationId, uuid)
        if (!conv) return null

        const after = Number(afterId || 0)
        if (Number.isFinite(after) && after > 0) {
            const rows = await db.query(
                `SELECT id, conversation_id, role, channel, content, images, status, error_msg, create_time, update_time
                 FROM ai_chat_message
                 WHERE conversation_id = ? AND create_user = ? AND id > ?
                 ORDER BY id ASC
                 LIMIT 100`,
                [conversationId, uuid, after]
            )
            return { data: (rows || []).map(normalizeMessage), pagination: null }
        }

        const page = normalizePage(current, pagesize, 100)
        const rows = await db.query(
            `SELECT id, conversation_id, role, channel, content, images, status, error_msg, create_time, update_time
             FROM ai_chat_message
             WHERE conversation_id = ? AND create_user = ?
             ORDER BY id DESC
             LIMIT ? OFFSET ?`,
            [conversationId, uuid, String(page.pagesize), String(page.offset)]
        )
        const countRows = await db.query(
            'SELECT COUNT(*) AS total FROM ai_chat_message WHERE conversation_id = ? AND create_user = ?',
            [conversationId, uuid]
        )
        return {
            data: (rows || []).map(normalizeMessage).reverse(),
            pagination: {
                current: page.current,
                pagesize: page.pagesize,
                total: Number(countRows?.[0]?.total || 0)
            }
        }
    }

    async addUserMessage({ uuid, conversationId, content, images, channel = 'web' }) {
        const text = normalizeText(content, 2000)
        const imgs = normalizeImages(images)
        if (!text && imgs.length === 0) return { missingContent: true }

        const preview = buildPreview(text, imgs)
        const safeChannel = normalizeChannel(channel)
        const conv = conversationId
            ? await assertUserConversation(conversationId, uuid)
            : await this.createConversation({ uuid, title: preview ? preview.slice(0, 40) : DEFAULT_TITLE, channel: safeChannel })
        if (!conv) return null
        if (conv.channel === 'wechat' && safeChannel !== 'wechat') return { readonly: true }

        const now = Date.now()
        const result = await db.query(
            `INSERT INTO ai_chat_message
             (conversation_id, create_user, role, channel, content, images, status, error_msg, create_time, update_time)
             VALUES (?, ?, 'user', ?, ?, ?, 'done', '', ?, ?)`,
            [conv.id, uuid, safeChannel, text, JSON.stringify(imgs), now, now]
        )
        const title = (!conv.title || conv.title === DEFAULT_TITLE || Number(conv.last_message_time || 0) === 0) && preview
            ? preview.slice(0, 40)
            : conv.title
        await db.query(
            'UPDATE ai_chat_conversation SET title = ?, last_message_preview = ?, last_message_time = ?, update_time = ? WHERE id = ?',
            [title, preview, now, now, conv.id]
        )
        return {
            conversationId: conv.id,
            conversationNo: conv.conversation_no,
            messageId: result?.insertId,
            content: text,
            images: imgs,
            channel: safeChannel,
            conversation: normalizeConversation({
                id: conv.id,
                conversation_no: conv.conversation_no,
                title,
                channel: conv.channel,
                state: ACTIVE_STATE,
                last_message_preview: preview,
                last_message_time: now,
                create_time: conv.create_time || now,
                update_time: now
            }),
            message: {
                id: result?.insertId,
                conversation_id: conv.id,
                role: 'user',
                channel: safeChannel,
                content: text,
                images: imgs,
                status: 'done',
                error_msg: '',
                create_time: now,
                update_time: now
            }
        }
    }

    async addAssistantMessage({ conversationId, conversationNo, content, images = [], status = 'done', errorMsg = '', channel = null }) {
        const whereSql = conversationNo ? 'conversation_no = ?' : 'id = ?'
        const whereValue = conversationNo || conversationId
        const rows = await db.query(
            `SELECT id, create_user, channel FROM ai_chat_conversation WHERE ${whereSql} AND state = ? LIMIT 1`,
            [whereValue, ACTIVE_STATE]
        )
        if (!rows || rows.length !== 1) return false

        const targetConversationId = rows[0].id
        const uuid = rows[0].create_user
        const text = normalizeText(content, 4000)
        const imgs = normalizeImages(images)
        if (!text && imgs.length === 0 && status !== 'error') return false

        const now = Date.now()
        const safeChannel = normalizeChannel(channel || rows[0].channel)
        await db.query(
            `INSERT INTO ai_chat_message
             (conversation_id, create_user, role, channel, content, images, status, error_msg, create_time, update_time)
             VALUES (?, ?, 'assistant', ?, ?, ?, ?, ?, ?, ?)`,
            [targetConversationId, uuid, safeChannel, text, JSON.stringify(imgs), status, normalizeText(errorMsg, 200), now, now]
        )
        await db.query(
            'UPDATE ai_chat_conversation SET last_message_preview = ?, last_message_time = ?, update_time = ? WHERE id = ?',
            [buildPreview(text, imgs), now, now, targetConversationId]
        )
        if (safeChannel === 'wechat' && text && status === 'done') {
            try {
                const { sendTextToUser } = require('./WeixinBindingService')
                await sendTextToUser(uuid, text)
            } catch (err) {
                logger.error(`WeChat AIChat reply send failed user=${uuid} conversation=${targetConversationId}: ${err.stack || err.message || err}`)
            }
        }
        return true
    }

    async addSystemMessage({ conversationId, content, status = 'done', errorMsg = '', channel = null }) {
        const rows = await db.query(
            'SELECT id, create_user, channel FROM ai_chat_conversation WHERE id = ? AND state = ? LIMIT 1',
            [conversationId, ACTIVE_STATE]
        )
        if (!rows || rows.length !== 1) return false
        const now = Date.now()
        await db.query(
            `INSERT INTO ai_chat_message
             (conversation_id, create_user, role, channel, content, images, status, error_msg, create_time, update_time)
             VALUES (?, ?, 'system', ?, ?, JSON_ARRAY(), ?, ?, ?, ?)`,
            [conversationId, rows[0].create_user, normalizeChannel(channel || rows[0].channel), normalizeText(content, 1000), status, normalizeText(errorMsg, 200), now, now]
        )
        return true
    }

    async listAdminConversations({ id, conversation_no, create_user, username, state, channel, queryTime, current, pagesize }) {
        const page = normalizePage(current, pagesize)
        const where = ['1 = 1']
        const params = []
        const countParams = []

        if (id) {
            where.push('c.id = ?')
            params.push(id)
            countParams.push(id)
        }
        if (conversation_no) {
            where.push('c.conversation_no = ?')
            params.push(conversation_no)
            countParams.push(conversation_no)
        }
        if (create_user) {
            where.push('c.create_user LIKE ?')
            params.push(`%${create_user}%`)
            countParams.push(`%${create_user}%`)
        }
        if (username) {
            where.push('u.username LIKE ?')
            params.push(`%${username}%`)
            countParams.push(`%${username}%`)
        }
        if (state !== undefined && state !== null && state !== '' && Number(state) !== -1) {
            where.push('c.state = ?')
            params.push(state)
            countParams.push(state)
        }
        if (channel && ['web', 'wechat'].includes(channel)) {
            where.push('c.channel = ?')
            params.push(channel)
            countParams.push(channel)
        }
        if (Array.isArray(queryTime) && queryTime.length === 2) {
            where.push('c.update_time >= ? AND c.update_time < ?')
            params.push(queryTime[0], queryTime[1])
            countParams.push(queryTime[0], queryTime[1])
        }

        const whereSql = where.join(' AND ')
        const rows = await db.query(
            `SELECT c.id, c.conversation_no, c.create_user, c.title, c.channel, c.state, c.last_message_preview, c.last_message_time,
                    c.create_time, c.update_time, u.username
             FROM ai_chat_conversation c
             LEFT JOIN users u ON u.uuid = c.create_user
             WHERE ${whereSql}
             ORDER BY c.update_time DESC
             LIMIT ? OFFSET ?`,
            [...params, String(page.pagesize), String(page.offset)]
        )
        const countRows = await db.query(
            `SELECT COUNT(*) AS total
             FROM ai_chat_conversation c
             LEFT JOIN users u ON u.uuid = c.create_user
             WHERE ${whereSql}`,
            countParams
        )
        return {
            data: (rows || []).map(normalizeConversation),
            pagination: {
                current: page.current,
                pagesize: page.pagesize,
                total: Number(countRows?.[0]?.total || 0)
            }
        }
    }

    async getAdminConversationDetail({ conversationId }) {
        const convRows = await db.query(
            `SELECT c.id, c.conversation_no, c.create_user, c.title, c.channel, c.state, c.last_message_preview, c.last_message_time,
                    c.create_time, c.update_time, u.username
             FROM ai_chat_conversation c
             LEFT JOIN users u ON u.uuid = c.create_user
             WHERE c.id = ?
             LIMIT 1`,
            [conversationId]
        )
        if (!convRows || convRows.length !== 1) return null
        const msgRows = await db.query(
            `SELECT id, conversation_id, role, channel, content, images, status, error_msg, create_time, update_time
             FROM ai_chat_message
             WHERE conversation_id = ?
             ORDER BY id ASC`,
            [conversationId]
        )
        return {
            ...normalizeConversation(convRows[0]),
            messages: (msgRows || []).map(normalizeMessage),
            assistantUuid: ASSISTANT_UUID
        }
    }
}

module.exports = {
    ConversationService: new ConversationService(),
    normalizeImages,
    normalizeText,
    normalizeChannel,
    ASSISTANT_UUID
}