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

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

const ACTIVE_STATE = 1
const DELETED_STATE = 2
const ASSISTANT_UUID = 'e4fe0277-0b1a-41a1-b25f-8b6e4cec3281'

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

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,
        images: parseImages(row.images)
    }
}

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 rows = await db.query(
        'SELECT id, conversation_no, create_user, title, state FROM ai_chat_conversation WHERE id = ? AND create_user = ? AND state = ? LIMIT 1',
        [conversationId, uuid, ACTIVE_STATE]
    )
    return rows && rows.length === 1 ? 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, 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 || [],
            pagination: {
                current: page.current,
                pagesize: page.pagesize,
                total: Number(countRows?.[0]?.total || 0)
            }
        }
    }

    async createConversation({ uuid, title }) {
        const emptyRows = await db.query(
            `SELECT id, conversation_no, title, 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
             ORDER BY update_time DESC
             LIMIT 1`,
            [uuid, ACTIVE_STATE]
        )
        if (emptyRows && emptyRows.length === 1) {
            return { ...emptyRows[0], reused: true }
        }

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

    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, 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, 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 }) {
        const conv = await assertUserConversation(conversationId, uuid)
        if (!conv) return null

        const text = normalizeText(content, 2000)
        const imgs = normalizeImages(images)
        if (!text && imgs.length === 0) return { missingContent: true }

        const now = Date.now()
        const result = await db.query(
            `INSERT INTO ai_chat_message
             (conversation_id, create_user, role, content, images, status, error_msg, create_time, update_time)
             VALUES (?, ?, 'user', ?, ?, 'done', '', ?, ?)`,
            [conversationId, uuid, text, JSON.stringify(imgs), now, now]
        )
        const preview = buildPreview(text, imgs)
        const title = conv.title === '小妍助理对话' && 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, conversationId]
        )
        return {
            conversationId,
            conversationNo: conv.conversation_no,
            messageId: result?.insertId,
            content: text,
            images: imgs
        }
    }

    async addAssistantMessage({ conversationId, conversationNo, content, images = [], status = 'done', errorMsg = '' }) {
        const whereSql = conversationNo ? 'conversation_no = ?' : 'id = ?'
        const whereValue = conversationNo || conversationId
        const rows = await db.query(
            `SELECT id, create_user 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()
        await db.query(
            `INSERT INTO ai_chat_message
             (conversation_id, create_user, role, content, images, status, error_msg, create_time, update_time)
             VALUES (?, ?, 'assistant', ?, ?, ?, ?, ?, ?)`,
            [targetConversationId, uuid, 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]
        )
        return true
    }

    async addSystemMessage({ conversationId, content, status = 'done', errorMsg = '' }) {
        const rows = await db.query(
            'SELECT id, create_user 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, content, images, status, error_msg, create_time, update_time)
             VALUES (?, ?, 'system', ?, JSON_ARRAY(), ?, ?, ?, ?)`,
            [conversationId, rows[0].create_user, normalizeText(content, 1000), status, normalizeText(errorMsg, 200), now, now]
        )
        return true
    }

    async listAdminConversations({ id, conversation_no, create_user, username, state, 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 (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.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 || [],
            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.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, content, images, status, error_msg, create_time, update_time
             FROM ai_chat_message
             WHERE conversation_id = ?
             ORDER BY id ASC`,
            [conversationId]
        )
        return {
            ...convRows[0],
            messages: (msgRows || []).map(normalizeMessage),
            assistantUuid: ASSISTANT_UUID
        }
    }
}

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