Administrator / xlyAi

package com.xly.service;

import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONObject;
import com.xly.agent.ChatiAgent;
import com.xly.agent.DynamicTableNl2SqlAiAgent;
import com.xly.agent.ErpAiAgent;
import com.xly.agent.SceneSelectorAiAgent;
import com.xly.config.OperableChatMemoryProvider;
import com.xly.constant.CommonConstant;
import com.xly.constant.ReturnTypeCode;
import com.xly.entity.*;
import com.xly.exception.sqlexception.SqlGenerateException;
import com.xly.exception.sqlexception.SqlValidateException;
import com.xly.milvus.service.AiGlobalAgentQuestionSqlEmitterService;
import com.xly.milvus.service.MilvusService;
import com.xly.runner.AppStartupRunner;
import com.xly.thread.AiSqlErrorHistoryThread;
import com.xly.thread.AiUserAgentQuestionThread;
import com.xly.thread.MultiThreadPoolServer;
import com.xly.tool.DynamicToolProvider;
import com.xly.util.*;
import dev.langchain4j.agent.tool.ToolExecutionRequest;
import dev.langchain4j.data.message.AiMessage;
import dev.langchain4j.data.message.ChatMessage;
import dev.langchain4j.data.message.ChatMessageType;
import dev.langchain4j.model.chat.ChatLanguageModel;
import dev.langchain4j.model.ollama.OllamaChatModel;
import dev.langchain4j.service.AiServices;
import dev.langchain4j.service.MemoryId;
import dev.langchain4j.service.V;
import io.milvus.v2.common.DataType;
import io.milvus.v2.service.collection.request.CreateCollectionReq;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.IdGenerator;
import reactor.core.publisher.Flux;

import java.time.Duration;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Service
@RequiredArgsConstructor
@Slf4j
public class XlyErpService {
    //中文对话模型
    private final OllamaChatModel chatModel;
    private final ChatLanguageModel chatiModel;
    private final SceneSelectorAiAgent sceneSelectorAiAgent;
    private final UserSceneSessionService userSceneSessionService;
    private final DynamicToolProvider dynamicToolProvider;
    private final OperableChatMemoryProvider operableChatMemoryProvider;
    private final DynamicExeDbService dynamicExeDbService;
    private final RedisService redisService;
    private final AiGlobalAgentQuestionSqlEmitterService aiGlobalAgentQuestionSqlEmitterService;
    private final MilvusService milvusService;

    //执行动态语句 执行异常的情况下 最多执行次数
    private final Integer maxRetries = 5;
    //没有找到对应方法重走一次补偿次数
    public final static Integer maxTollRetries = 1;

    @Value("${langchain4j.ollama.base-url}")
    private String sqlModelUrl;

    @Value("${langchain4j.ollama.sql-model-name}")
    private String sqlModelName;


    /**
     * @Author 钱豹
     * @Date 19:18 2026/1/27
     * @Param [userInput, userId, sUserType]
     * @return reactor.core.publisher.Flux<AiResponseDTO>
     * @Description 问答（流式返回）
     **/
    public Flux<AiResponseDTO> erpUserInputStream(String userInput,
                                            String userId,
                                            String sUserName,
                                            String sBrandsId,
                                            String sSubsidiaryId,
                                            String sUserType,
                                            String authorization) {
        String sceneName = StrUtil.EMPTY;
        String methodName = StrUtil.EMPTY;
        UserSceneSession session=null;
        try {
            // 0. 预处理用户输入：去空格、转小写（方便匹配）
            String input= InputPreprocessor.preprocessWithCommons(userInput);
            // 1. 初始化用户场景会话（权限内场景）
            session = userSceneSessionService.getUserSceneSession(userId,sUserName,sBrandsId,sSubsidiaryId,sUserType,authorization);
            session.setAuthorization(authorization);
            session.setSFunPrompts(null);
            sceneName = ObjectUtil.isNotEmpty(session.getCurrentScene())?session.getCurrentScene().getSSceneName():StrUtil.EMPTY;
            methodName = ObjectUtil.isNotEmpty(session.getCurrentTool())?session.getCurrentTool().getSMethodName():StrUtil.EMPTY;
            // 2. 特殊指令：重置场景（无论是否已选，都可重置）
            if (input.contains("重置") || input.contains("重新选择")) {
                //清除记忆缓存
                reSet(userId ,sUserName, sBrandsId ,sSubsidiaryId,sUserType,authorization,session);
                return Flux.just(AiResponseDTO.builder()
                        .aiText(resetUserScene(session.getUserId(), session))
                        .sSceneName(sceneName)
                        .sMethodName(methodName)
                        .sReturnType(ReturnTypeCode.HTML.getCode())
                        .build());
            }
            //聊天只能体
            if (session.getCurrentScene() != null
                    && Objects.equals(session.getCurrentScene().getSSceneNo(), "ChatZone"))
            {
                return getChatiAgentStream(input, session);
            }
            // 3. 未选场景：先展示场景选择界面，处理用户序号选择
            if (!session.isSceneSelected() && ValiDataUtil.me().isPureNumber(input)){
                // 3.1 尝试处理场景选择（输入序号则匹配，否则展示选择提示）
                AiResponseDTO aiResponseDTO = handleSceneSelect(userId, input, session);
                return Flux.just(aiResponseDTO);
            }
            // 4. 构建Agent，执行业务交互,如果返回为null,说明大模型没有判段出场景，必判断出后才能继续
            ErpAiAgent aiAgent = createErpAiAgent(userId, input, session);
            // 没有选择到场景，进闲聊模式
            if (aiAgent == null){
                return getChatiAgentStream (input,session);
            }
            String sResponMessage = StrUtil.EMPTY;
            //用户输入添加方法(如果没有方法，动态SQL方法不需要)
            if(!(ObjectUtil.isNotEmpty(session.getCurrentTool())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSInputTabelName())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSStructureMemo()))
            ){
                sResponMessage = aiAgent.chat(userId, input);
            }

            if(ObjectUtil.isNotEmpty(session.getCurrentTool())
                    && !ObjectUtil.isNotEmpty(session.getCurrentTool().getSInputTabelName())
            ){
                input = session.getCurrentTool().getSMethodName()+","+input;
            }
            //动态方法或返回需要提示的信息
            if(ObjectUtil.isNotEmpty(session.getSFunPrompts())){
                return Flux.just(AiResponseDTO.builder()
                        .aiText(session.getSFunPrompts())
                        .sSceneName(sceneName)
                        .sMethodName(methodName)
                        .sReturnType(ReturnTypeCode.HTML.getCode())
                        .build());
            }
//          1.找到方法并且本方法带表结构描述时，需要调用 自然语言转SQL智能体
            if((ObjectUtil.isNotEmpty(session.getCurrentTool())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSInputTabelName())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSStructureMemo()))
            ){
                //查询是否走向量库 还是数据库查询
                Boolean isAggregation = aiAgent.routeQuery(session.getUserId(), input);
                if(!isAggregation){
                    //获取常量库内容
                    sResponMessage = getMilvus(session, input, aiAgent);
                }else {
                    sResponMessage = getDynamicTableSql(session, input, userId, userInput,0,StrUtil.EMPTY,StrUtil.EMPTY,"0",StrUtil.EMPTY, aiAgent);
                }
                return Flux.just(AiResponseDTO.builder()
                        .aiText(sResponMessage)
                        .sSceneName(sceneName)
                        .sMethodName(methodName)
                        .sReturnType(ReturnTypeCode.HTML.getCode())
                        .build());
            } else if (ObjectUtil.isNotEmpty(session.getCurrentTool())) {
                //2.处理工具参数采集结束后业务逻辑处理
                //调用方法,参数缺失部分提示，就直接使用方法返回的
                sResponMessage = dynamicToolProvider.doDynamicTool(session.getCurrentTool(),session);
                return Flux.just(AiResponseDTO.builder()
                        .aiText(sResponMessage)
                        .sSceneName(sceneName)
                        .sMethodName(methodName)
                        .sReturnType(ReturnTypeCode.HTML.getCode())
                        .build());
            }else if(session.getCurrentScene()== null ){
                return Flux.just(AiResponseDTO.builder()
                        .aiText("当前场景:没有选择 退回当前场景 请输入 "+ CommonConstant.RESET + sResponMessage)
                        .sSceneName(sceneName)
                        .sMethodName(methodName)
                        .sReturnType(ReturnTypeCode.HTML.getCode())
                        .build());
            }else{
                return getChatiAgentStream (input, session);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return Flux.just(AiResponseDTO.builder()
                    .aiText("系统异常：" + e.getMessage() + "，请稍后重试！")
                    .sSceneName(sceneName)
                    .sMethodName(methodName)
                    .sReturnType(ReturnTypeCode.HTML.getCode())
                    .build());
        }finally {
            //5.执行工具方法后，清除记忆
            if(session !=null && session.getBCleanMemory()){
                doCleanUserMemory(session,userId);
            }
        }
    }

    /***
     * @Author 钱豹
     * @Date 19:18 2026/1/27
     * @Param [userInput, userId, sUserType]
     * @return java.lang.String
     * @Description 问答
     **/
    public AiResponseDTO erpUserInput(String userInput,
                                      String  userId ,
                                      String  sUserName ,
                                      String  sBrandsId ,
                                      String  sSubsidiaryId,
                                      String sUserType,
                                      String authorization) {
        String sceneName = StrUtil.EMPTY;
        String methodName = StrUtil.EMPTY;
        UserSceneSession session=null;
        try {
            // 0. 预处理用户输入：去空格、转小写（方便匹配）
            String input= InputPreprocessor.preprocessWithCommons(userInput);
            // 1. 初始化用户场景会话（权限内场景）
            session = userSceneSessionService.getUserSceneSession(userId,sUserName,sBrandsId,sSubsidiaryId,sUserType,authorization);
            session.setAuthorization(authorization);
            session.setSFunPrompts(null);
            sceneName = ObjectUtil.isNotEmpty(session.getCurrentScene())?session.getCurrentScene().getSSceneName():StrUtil.EMPTY;
            methodName = ObjectUtil.isNotEmpty(session.getCurrentTool())?session.getCurrentTool().getSMethodName():StrUtil.EMPTY;
            // 2. 特殊指令：重置场景（无论是否已选，都可重置）
            if (input.contains("重置") || input.contains("重新选择")) {
                //清除记忆缓存
                reSet(userId ,sUserName, sBrandsId ,sSubsidiaryId,sUserType,authorization,session);
                return AiResponseDTO.builder().aiText(resetUserScene(session.getUserId(),session)).build();
            }
            //聊天只能体
            if (session.getCurrentScene() != null
                    && Objects.equals(session.getCurrentScene().getSSceneNo(), "ChatZone"))
            {
                return getChatiAgent(input, session);
            }
            // 3. 未选场景：先展示场景选择界面，处理用户序号选择
            if (!session.isSceneSelected() && ValiDataUtil.me().isPureNumber(input)){
                // 3.1 尝试处理场景选择（输入序号则匹配，否则展示选择提示）
                return handleSceneSelect(userId, input, session);
            }
            // 4. 构建Agent，执行业务交互,如果返回为null,说明大模型没有判段出场景，必判断出后才能继续
            ErpAiAgent aiAgent = createErpAiAgent(userId, input, session);
            // 没有选择到场景，进闲聊模式
            if (aiAgent == null){
                return getChatiAgent (input,session);
            }
            String sResponMessage = StrUtil.EMPTY;
            //用户输入添加方法(如果没有方法，动态SQL方法不需要)
            if(!(ObjectUtil.isNotEmpty(session.getCurrentTool())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSInputTabelName())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSStructureMemo()))
            ){
                sResponMessage = aiAgent.chat(userId, input);
            }

            if(ObjectUtil.isNotEmpty(session.getCurrentTool())
                    && !ObjectUtil.isNotEmpty(session.getCurrentTool().getSInputTabelName())
            ){
                input = session.getCurrentTool().getSMethodName()+","+input;
            }
            //动态方法或返回需要提示的信息
            if(ObjectUtil.isNotEmpty(session.getSFunPrompts())){
                return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText(session.getSFunPrompts()).sReturnType(ReturnTypeCode.HTML.getCode()).build();
            }
//          1.找到方法并且本方法带表结构描述时，需要调用 自然语言转SQL智能体
            if((ObjectUtil.isNotEmpty(session.getCurrentTool())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSInputTabelName())
                    && ObjectUtil.isNotEmpty(session.getCurrentTool().getSStructureMemo()))
            ){
                //查询是否走向量库 还是数据库查询
                Boolean isAggregation = aiAgent.routeQuery(session.getUserId(), input);
                if(!isAggregation){
                    //获取常量库内容
                    sResponMessage = getMilvus(session, input, aiAgent);
                }else {
                    sResponMessage = getDynamicTableSql(session, input, userId, userInput,0,StrUtil.EMPTY,StrUtil.EMPTY,"0",StrUtil.EMPTY, aiAgent);
                }
                return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText(sResponMessage).sReturnType(ReturnTypeCode.HTML.getCode()).build();
            } else if (ObjectUtil.isNotEmpty(session.getCurrentTool())) {
                //2.处理工具参数采集结束后业务逻辑处理
                //调用方法,参数缺失部分提示，就直接使用方法返回的
                sResponMessage = dynamicToolProvider.doDynamicTool(session.getCurrentTool(),session);
                return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText(sResponMessage).sReturnType(ReturnTypeCode.HTML.getCode()).build();
            }else if(session.getCurrentScene()== null ){
                return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText("当前场景:没有选择 退回当前场景 请输入 "+ CommonConstant.RESET + sResponMessage).sReturnType(ReturnTypeCode.HTML.getCode()).build();
            }else{
                return getChatiAgent (input, session);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText("系统异常：" + e.getMessage() + "，请稍后重试！").sReturnType(ReturnTypeCode.HTML.getCode()).build();
        }finally {
            //5.执行工具方法后，清除记忆
            if(session !=null && session.getBCleanMemory()){
                doCleanUserMemory(session,userId);
            }
        }
    }
    /***
     * @Author 钱豹
     * @Date 22:47 2026/3/16
     * @Param [userId, sUserName, sBrandsId, sSubsidiaryId, sUserType, authorization]
     * @return void
     * @Description 回首页
     **/
    public void reSet( String  userId ,
                                      String  sUserName ,
                                      String  sBrandsId ,
                                      String  sSubsidiaryId,
                                      String sUserType,
                                      String authorization, UserSceneSession session) {
        userSceneSessionService.cleanUserSession(userId);
        session.setCurrentScene(null);
        session.setSceneSelected(false);
        session.setBCleanMemory(false);
        session.setCurrentRowData(null);
        UserSceneSessionService.USER_SCENE_SESSION_CACHE.put(userId, session);
        // 清空Agent缓存
        UserSceneSessionService.ERP_AGENT_CACHE.remove(userId);
        UserSceneSessionService.CHAT_AGENT_CACHE.remove(userId);
        cleanMemory(userId, sUserName, sBrandsId, sSubsidiaryId, sUserType, authorization);
    }

    public AiResponseDTO cleanMemory( String  userId ,
                                      String  sUserName ,
                                      String  sBrandsId ,
                                      String  sSubsidiaryId,
                                      String sUserType,
                                      String authorization) {

        UserSceneSession session = userSceneSessionService.getUserSceneSession(userId,sUserName,sBrandsId,sSubsidiaryId,sUserType,authorization);
        operableChatMemoryProvider.clearSpecifiedMemory(userId);
        session.setCurrentTool(null);
        session.setArgs(null);
        session.setSUserQuestionList(new ArrayList<>());
        UserSceneSessionService.ERP_AGENT_CACHE.remove(userId);
        UserSceneSessionService.CHAT_AGENT_CACHE.remove(userId);
        session.setBCleanMemory(false);
        String sceneName = ObjectUtil.isNotEmpty(session.getCurrentScene())?session.getCurrentScene().getSSceneName():StrUtil.EMPTY;
        return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(StrUtil.EMPTY).aiText(StrUtil.EMPTY).systemText("清除记忆成功！").sReturnType(ReturnTypeCode.HTML.getCode()).build();
    }

    /***
     * @Author 钱豹
     * @Date 10:16 2026/3/25
     * @Param [session, input, userId, userInput, attempt, errorSql, errorMessage, iErroCount, historySqlList, aiAgent]
     * @return java.lang.String
     * @Description 查询向量库
     **/
    private String getMilvus(UserSceneSession session,String userInput,ErpAiAgent aiAgent){
        String resultExplain = "信息模糊,请提供更具体的问题或指令";
        try{
            String sVectorfiled = session.getCurrentTool().getSVectorfiled();
            String sInputTabelName = session.getCurrentTool().getSInputTabelName();
            String sVectorfiledAll = session.getCurrentTool().getSVectorfiledAll();
            Map<String,Object> rMap = milvusService.getMilvusFiled(sVectorfiled,sVectorfiledAll);
            String sMilvusFiled =  rMap.get("sMilvusFiled").toString();
            String sMilvusFiledDescription = rMap.get("sMilvusFiledDescription").toString();
            String sMilvusFiledDescriptionAll = rMap.get("sMilvusFiledDescriptionAll").toString();
            List<String> fields = (List<String>) rMap.get("sFileds");
//            List<Map<String, String>> title  = (List<Map<String, String>>) rMap.get("title");
            String milvusFilter = aiAgent.getMilvusFilter(session.getUserId(),userInput, sMilvusFiled, sMilvusFiledDescription);
            List<Map<String,Object>> data = milvusService.getDataToCollection(sInputTabelName, milvusFilter,userInput,100,fields);
            //采用表格形式显示
            resultExplain = aiAgent.explainMilvusResult(session.getUserId(),userInput,sMilvusFiledDescriptionAll,JSONObject.toJSONString(data));
                    //buildMarkdownTableWithStream(data, title);
            return resultExplain;
        }catch (Exception e){
            e.printStackTrace();
        }
        return resultExplain;
    }
    /***
     * @Author 钱豹
     * @Date 13:19 2026/3/25
     * @Param [data, title]
     * @return java.lang.String
     * @Description 数据转成MarkdownTable
     **/
    public String buildMarkdownTableWithStream(List<Map<String, Object>> data, List<Map<String, String>> title) {
        if (data == null || data.isEmpty()) {
            return "暂无数据";
        }

        // 动态构建表头
        StringBuilder headerBuilder = new StringBuilder("|");
        StringBuilder separatorBuilder = new StringBuilder("|");

        for (Map<String, String> column : title) {
            String displayName = column.get("sTitle");  // 中文显示名称
            headerBuilder.append(" ").append(displayName).append(" |");
            separatorBuilder.append("------|");
        }
        String header = headerBuilder.toString() + "\n" + separatorBuilder.toString() + "\n";
        // 构建数据行
        String rows = IntStream.range(0, data.size())
                .mapToObj(i -> {
                    Map<String, Object> item = data.get(i);
                    StringBuilder rowBuilder = new StringBuilder("|");

                    for (Map<String, String> column : title) {
                        String fieldName = column.get("sName");
                        Object value = item.getOrDefault(fieldName, "");
                        rowBuilder.append(" ").append(value).append(" |");
                    }

                    return rowBuilder.toString();
                })
                .collect(Collectors.joining("\n"));

        return header + rows;
    }


    /***
     * @Author 钱豹
     * @Date 18:38 2026/2/5
     * @Param [session, input, userId, userInput]
     * @return java.lang.String
     * @Description 获取执行动态SQL
     **/
    private String getDynamicTableSql(UserSceneSession session,String input,String userId,String userInput,Integer attempt,String errorSql,String errorMessage,String iErroCount,String historySqlList,ErpAiAgent aiAgent){
        String resultExplain = "信息模糊,请提供更具体的问题或指令";
        try{
            while (attempt < maxRetries) {
                try{
                    attempt = attempt+1;
                    if(attempt==1){
                        List<String> userQuestionList = session.getSUserQuestionList();
                        if(ObjectUtil.isEmpty(userQuestionList)){
                            userQuestionList = new ArrayList<>();
                        }
                        String  sQuestion = StrUtil.replace(userInput," ",StrUtil.EMPTY);
                        sQuestion = StrUtil.replace(sQuestion,"\t",StrUtil.EMPTY);
                        sQuestion = StrUtil.replace(sQuestion,"\n",StrUtil.EMPTY);
                        sQuestion = sQuestion.toLowerCase();
                        userQuestionList.add(sQuestion);
                        session.setSUserQuestionList(userQuestionList);
                    }
                    return getDynamicTableSqlExec(session, input, userId, userInput,errorSql,errorMessage,iErroCount,historySqlList, aiAgent);
                }catch (SqlValidateException e){
                    return "本场景没有识别到您的意图<br/> 如果切换场景，点［回首页］，如果在本场景下，转换意图，点［清除记忆］";
                }catch (Exception e){
                    String erroMsg = e.getMessage();
                    String errorSqlOld = StrUtil.EMPTY;
                    if(erroMsg.contains(EnhancedErrorGuidance.splitString) && erroMsg.split(EnhancedErrorGuidance.splitString).length>1){
                        errorSqlOld = erroMsg.split(EnhancedErrorGuidance.splitString)[1];
                        errorSqlOld = StrUtil.replace(errorSqlOld,";","");
                        if(StrUtil.isNotEmpty(historySqlList)){
                            historySqlList = historySqlList+"/"+errorSqlOld;
                        }else{
                            historySqlList = errorSqlOld;
                        }
                    }
                    String errorMessageOld = erroMsg.split(EnhancedErrorGuidance.splitString)[0];
                    if (attempt == maxRetries) {
                        return resultExplain +"<br/>查询的SQL语句："+historySqlList;
                    } else {
                        return getDynamicTableSql( session, input, userId, userInput, attempt,errorSqlOld,errorMessageOld,attempt.toString(),historySqlList, aiAgent);
                    }
                }
            }
        }catch (Exception e){
        }
//        finally {
//           doCleanUserMemory(session,userId);
//        }
        return resultExplain;
    }

    /***
     * @Author 钱豹
     * @Date 19:59 2026/3/4
     * @Param [session, sUserId]
     * @return void
     * @Description 删除用户记忆 方法
     **/
    private void doCleanUserMemory(UserSceneSession session,String userId){
        operableChatMemoryProvider.clearSpecifiedMemory(userId);
        session.setCurrentTool(null);
        session.setSUserQuestionList(new ArrayList<>());
        session.setArgs(new HashMap<>());
//        session.setSceneSelected(false);
        UserSceneSessionService.ERP_AGENT_CACHE.remove(userId);
        UserSceneSessionService.CHAT_AGENT_CACHE.remove(userId);
        session.setBCleanMemory(false);
    }


    /***
     * @Author 钱豹
     * @Date 19:49 2026/3/4
     * @Param [session, input, userId, userInput]
     * @return java.lang.String
     * @Description 执行动态sSql
     **/
    private String getDynamicTableSqlExec(UserSceneSession session,String input,String userId,String userInput,String errorSql,String errorMessage,String iErroCount,String historySqlList,ErpAiAgent aiAgent){
        // 1. 构建自然语言转SQLAgent，
        List<Map<String, Object>> sqlResult;
        String cleanSql = StrUtil.EMPTY;
        String rawSql;
        String tableStruct = session.getCurrentTool().getSStructureMemo();
        String sError_mes;
        Boolean doAddSql = false;
        List<ChatMessage> chatMessage = new ArrayList<>();
        try{
            //获取缓存动态SQL
//            cleanSql = getDynamicTableNl2Sql(session,input);
            //如果之前已查询直接返回
            if(ObjectUtil.isEmpty(cleanSql)){
                DynamicTableNl2SqlAiAgent aiDynamicTableNl2SqlAiAgent = createDynamicTableNl2SqlAiAgent(userId, input, session);
                chatMessage = operableChatMemoryProvider.getCurrentChatMessages(session.getUserId());
                String tableNames = session.getCurrentTool().getSInputTabelName();
                // "订单表:viw_salsalesorder,客户信息表:elecustomer,结算方式表:sispayment,产品表(无单价,无金额,无数量):viw_product_sort,销售人员表:viw_sissalesman_depart";
                String sDataNow = DateUtil.now();
                        //DateFormatUtils.format(new Date(), "yyyy年MM月dd日HH时mm分ss秒");
//                String sDataNow = DateUtil.format(new Date(), DatePattern.CHINESE_DATE_TIME_FORMAT);

                if(ObjectUtil.isEmpty(errorSql) && ObjectUtil.isEmpty(errorMessage)){
                    rawSql = aiDynamicTableNl2SqlAiAgent.generateMysqlSql(userId,tableNames,tableStruct,sDataNow,userInput);
                }else{
                    rawSql = aiDynamicTableNl2SqlAiAgent.regenerateSqlWithError(userId, tableNames,tableStruct,sDataNow,userInput,errorSql,errorMessage,iErroCount,historySqlList);
                }
                log.info("rawSql："+rawSql);
                if (rawSql == null || rawSql.trim().isEmpty()) {
                    throw new SqlValidateException("SQL EMPTY");
                }
                // 2. 清理SQL多余符号 + 生产级强校验（核心安全保障，不可省略）
                cleanSql = SqlValidateUtil.cleanSqlSymbol(rawSql);
                SqlValidateUtil.validateMysqlSql(cleanSql);
                doAddSql = true;
            }
//            List<ChatMessage> chatMessage2 = operableChatMemoryProvider.getCurrentChatMessages(session.getUserId());
            try{
               sqlResult = dynamicExeDbService.findSql(new HashMap<>(),cleanSql);
            }catch (Exception e){
                throw new SqlGenerateException(e.getMessage()+" OLDSQL "+cleanSql);
            }
        }catch (SqlValidateException e){
              //删除记录
//            operableChatMemoryProvider.deleteUserLasterMessageBySize(userId,3);
            sError_mes = e.getMessage();
            doAiSqlErrorHistoryThread(session, StrUtil.EMPTY, cleanSql, sError_mes,input);
            throw e;
        }catch (SqlGenerateException e){
//            operableChatMemoryProvider.deleteUserLasterMessageBySize(userId,3);
            sError_mes = e.getMessage();
            doAiSqlErrorHistoryThread(session, StrUtil.EMPTY, cleanSql, sError_mes,input);
            throw e;
        }

        // 5. 调用AI服务生成自然语言解释（传入表结构，让解释更贴合业务）
        String resultJson = JSON.toJSONString(sqlResult);

        //执行正确去修改对应正确的SQl
        if(Integer.valueOf(iErroCount)>0){
            doAiSqlErrorHistoryThread(session, cleanSql, StrUtil.EMPTY, StrUtil.EMPTY,input);
        }
        //插入常用操作
        if(doAddSql){
            //执行操作记录表
            doAiUserAgentQuestion(session,input,cleanSql,chatMessage);
        }
        String sText = aiAgent.explainSqlResult(
                userId,
                userInput,
                cleanSql,
                tableStruct,
                resultJson
        );
        return sText;
    }

    /***
     * @Author 钱豹
     * @Date 17:04 2026/3/19
     * @Param [session]
     * @return java.lang.String
     * @Description 获取动态SQL（历史中查询）
     **/
    private String getDynamicTableNl2Sql(UserSceneSession session,String input){
//        String sReidKey = SqlValidateUtil.getsKey( session.getCurrentScene().getSId(), session.getCurrentTool().getSId(), input);
//        Object sSql = redisService.get(sReidKey);
//        if(ObjectUtil.isNotEmpty(sSql)){
//            return sSql.toString();
//        }
        String searchText = session.getCurrentScene().getSId()+"_"+session.getCurrentTool().getSId()+input;
                //SqlValidateUtil.getsKey( session.getCurrentScene().getSId(), session.getCurrentTool().getSId(), SqlValidateUtil.getsQuestion(session.getSUserQuestionList()));
        //根据问题查询向量库
        Map<String,Object> serMap = aiGlobalAgentQuestionSqlEmitterService.queryAiGlobalAgentQuestionSqlEmitter(searchText, "ai_global_agent_question_sql");
        if(ObjectUtil.isNotEmpty(serMap)){
            return serMap.get("sSqlContent").toString();
        }
        return null;
    }

    /***
     * @Author 钱豹
     * @Date 16:57 2026/3/14
     * @Param
     * @return
     * @Description 记录动态SQL日志(多线程)
     **/
    public void doAiSqlErrorHistoryThread(UserSceneSession session,
                                                String sSqlContent,
                                                String sError_sql,
                                                String sError_mes,
                                                String sQuestion

    ){
        MultiThreadPoolServer mts = MultiThreadPoolServer.getInstance();
        AiSqlErrorHistoryThread at = new AiSqlErrorHistoryThread(session,  sSqlContent,  sError_sql, sError_mes,sQuestion);
        mts.service(at);
    }

    /***
     * @Author 钱豹
     * @Date 16:57 2026/3/14
     * @Param
     * @return
     * @Description 记录动态SQL日志(多线程)
     **/
    public void doAiUserAgentQuestion(UserSceneSession session,
                                          String sQuestion,
                                          String sSqlContent,
                                          List<ChatMessage> chatMessage

    ){
        MultiThreadPoolServer mts = MultiThreadPoolServer.getInstance();
        AiUserAgentQuestionThread at = new AiUserAgentQuestionThread(session,sQuestion,sSqlContent,chatMessage);
        mts.service(at);
    }



    /***
     * @Author 钱豹
     * @Date 11:22 2026/1/31
     * @Param
     * @return
     * @Description 动态参数补齐处理
     **/
    private String dotoolExecutionRequests(AiMessage aiMessage){
        String textTs = aiMessage.text();
        if(aiMessage.hasToolExecutionRequests()){
            List<ToolExecutionRequest> toolExecutionRequests = aiMessage.toolExecutionRequests();
            toolExecutionRequests.forEach(toolRequests->{
                String arguments = toolRequests.arguments();
                log.info(arguments);
            });
        }
        return textTs;
    }

    /***
     * 存入全部场景
     * @Author 钱豹
     * @Date 19:06 2026/1/26
     * @Param [sUserId, sUserType]
     * @return java.lang.String
     * 页面刷新/首次进入时调用：初始化用户场景会话，直接返回场景选择引导词
     * 前端页面加载完成后，无需用户输入，直接调用该方法即可显示引导词
     * @param sUserId 用户ID（前端传入，如user-001） sUserType 角色状态
     * @return 场景选择引导词（即原buildSceneSelectHint生成的文案）
     */
    public AiResponseDTO initSceneGuide(String systemText,String sUserId,String sUserName,String sBrandsId,String sSubsidiaryId,String sUserType,String authorization) {
        try {
            UserSceneSession userSceneSession = userSceneSessionService.getUserSceneSession( sUserId,sUserName,sBrandsId,sSubsidiaryId,sUserType,authorization);
            systemText = userSceneSession.buildSceneSelectHint();
        } catch (Exception e) {
            systemText = "<p style='color:red;'>抱歉，你暂无任何业务场景的访问权限，请联系管理员开通！</p>";
        }
        return AiResponseDTO.builder().aiText(StrUtil.EMPTY).systemText(systemText) .build();
    }




    // ====================== 动态构建Agent（支持选定场景/未选场景） ======================
    private DynamicTableNl2SqlAiAgent createDynamicTableNl2SqlAiAgent(String userId, String userInput, UserSceneSession session) {
//      4. 获取/创建用DynamicTableNl2SqlAiAgent
        DynamicTableNl2SqlAiAgent aiAgent = UserSceneSessionService.ERP_DynamicTableNl2SqlAiAgent_CACHE.get(userId);
        if(ObjectUtil.isEmpty(aiAgent)){
            OllamaChatModel ol = OllamaChatModel.builder()
                    .baseUrl(sqlModelUrl)
                    .modelName(sqlModelName)    // 使用SQL模型名称
                    .temperature(0.0)
                    .topP(0.95)
                    .numPredict(4096)          // 代码生成需要更长
                    .timeout(Duration.ofSeconds(120))
                    .maxRetries(3)
//                .repeatPenalty(1.1)        // 减少重复
                    .build();
            aiAgent = AiServices.builder(DynamicTableNl2SqlAiAgent.class)
                    .chatLanguageModel(ol)
                    .chatMemoryProvider(operableChatMemoryProvider)
                    .toolProvider(dynamicToolProvider)
                    .build();
            UserSceneSessionService.ERP_DynamicTableNl2SqlAiAgent_CACHE.put(userId, aiAgent);
        }
        return aiAgent;
    }

    // ====================== 动态构建Agent（支持选定场景/未选场景） ======================
    private ErpAiAgent createErpAiAgent(String userId, String userInput, UserSceneSession session) {

        // 1. 已选场景：强制绑定该场景工具
        if (session.isSceneSelected() && session.getCurrentScene() != null) {
            dynamicToolProvider.sSceneIdMap.put(userId,session.getCurrentScene().getSId());
        } else {
            // 2. 未选场景：大模型根据输入返加相应的场景
            SceneDto sceneDto = parseSceneByLlm(userId, userInput, session);
            if (sceneDto != null) {
                session.setCurrentScene(sceneDto);
                session.setSceneSelected(true);
                UserSceneSessionService.USER_SCENE_SESSION_CACHE.put(userId, session);
                dynamicToolProvider.sSceneIdMap.put(userId,session.getCurrentScene().getSId());
            }else {return  null;}
        }
        //  4. 获取/创建用Agent
        ErpAiAgent aiAgent = UserSceneSessionService.ERP_AGENT_CACHE.get(userId);
        if(ObjectUtil.isEmpty(aiAgent)){
            aiAgent = AiServices.builder(ErpAiAgent.class)
                    .chatLanguageModel(chatModel)
                    .chatMemoryProvider(operableChatMemoryProvider)
                    .toolProvider(dynamicToolProvider)
//                    .toolChoice(ChatCompletionToolChoice.ofRequired()) // 👈 必须调用一个工具
                    .build();
            UserSceneSessionService.ERP_AGENT_CACHE.put(userId, aiAgent);
            // 初始化AiService 以防止热加载太慢 找不到相应的方法
            try{
                aiAgent.chat(userId, "initAiService");
            }catch (Exception e){
                e.printStackTrace();
            }
            log.info("用户{}Agent构建完成，已选场景：{},场景ID{}", userId, session.isSceneSelected() ? session.getCurrentScene().getSSceneName() : "未选（全场景匹配）", dynamicToolProvider.sSceneIdMap.get(userId));
        }
        return aiAgent;
    }


    /**
     * 大模型意图解析核心方法(获取场景)
     * @param userId 用户ID
     * @param userInput 用户输入
     * @param session 用户会话
     * @return 匹配的BusinessScene，null表示解析失败
     */
    private SceneDto parseSceneByLlm(String userId, String userInput, UserSceneSession session) {
        try {
            List<ToolMeta> metasAll = session.getAuthTool();
                    // toolMetaMapper.findAll();
            // 1. 构建大模型意图解析请求
            String authScenesDesc =session.buildAuthScenesForLlm(metasAll);
            // 2. 调用大模型解析意图，LangChain4j自动将大模型输出映射为SceneIntentParseResp
//            {{authScenesDesc}}
            SceneIntentParseResp parseResp = sceneSelectorAiAgent.parseSceneIntent(userInput,authScenesDesc);
//            authScenesDesc
            // 3. 解析结果处理
            if (parseResp == null || parseResp.getSceneCode() == null || "NO_MATCH".equals(parseResp.getSceneCode())) {
                log.warn("用户{}大模型未匹配到任何场景，输入：{}", userId, userInput);
                return null;
            }
            // 4. 将场景编码转换为BusinessScene枚举
            String sSceneNo = parseResp.getScene();
            return AppStartupRunner.getAiAgentByCode(sSceneNo);
        } catch (Exception e) {
            log.error("用户{}大模型意图解析失败，输入：{}", userId, userInput, e);
            return null;
        }
    }

    /***
     * @Author 钱豹
     * @Date 19:28 2026/1/26
     * @Param [userId, session]
     * @return java.lang.String
     * @Description 重置用户场景选择：恢复为未选状态，清空当前场景，重新展示选择界面
     **/
    private String resetUserScene(String userId, UserSceneSession session) {
        session.setSceneSelected(false);
        session.setBCleanMemory(false);
        session.setCurrentTool(null);
        session.setSUserQuestionList(new ArrayList<>());
        session.setCurrentScene(null);
        session.setCurrentRowData(null);
        UserSceneSessionService.USER_SCENE_SESSION_CACHE.put(userId, session);
        // 清空Agent缓存
        UserSceneSessionService.ERP_AGENT_CACHE.remove(userId);
        UserSceneSessionService.CHAT_AGENT_CACHE.remove(userId);
        return "场景选择已重置！请重新选择业务场景：\n" + session.buildSceneSelectHint();
    }

    /**
     * 处理用户场景选择：输入序号→匹配场景→更新会话状态
     */
    private AiResponseDTO handleSceneSelect(String userId, String userInput, UserSceneSession session) {
        // 1. 尝试根据序号匹配场景
        boolean selectSuccess = session.selectSceneByInput(userInput);
        String sceneName = StrUtil.EMPTY;
        String methodName = StrUtil.EMPTY;
        if (selectSuccess) {
            // 2. 选择成功：更新缓存，返回成功提示
            UserSceneSessionService.USER_SCENE_SESSION_CACHE.put(userId, session);
            // 清空该用户原有Agent缓存（重新构建绑定新场景的Agent）
            UserSceneSessionService.ERP_AGENT_CACHE.remove(userId);
            //清除记忆缓存
            operableChatMemoryProvider.clearSpecifiedMemory(userId);
            StringBuffer aiText = new StringBuffer().append(" <div style='line-height:1.8;width:100%;'>")
                    .append("<div style=\"color: #333;\">")
                    .append("智能体选择成功！ 现在可以问她相关问题(如" + String.join("、", session.getCurrentScene().getSSceneContext()) + "）")
                    .append("</div>");
            //插入用户常用问题
            aiText.append(getSelectAgent(session,1));
            sceneName = ObjectUtil.isNotEmpty(session.getCurrentScene())?session.getCurrentScene().getSSceneName():StrUtil.EMPTY;
            methodName = ObjectUtil.isNotEmpty(session.getCurrentTool())?session.getCurrentTool().getSControlName():StrUtil.EMPTY;
            return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText(aiText.toString()).sSceneName(session.getCurrentScene().getSSceneName()).build();
        } else {
            // 3. 选择失败：重新展示场景选择提示
            return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText(session.buildSceneSelectHint()).build();
        }
    }
    /***
     * @Author 钱豹
     * @Date 11:45 2026/3/13
     * @Param []
     * @return java.lang.String
     * @Description 选择智能体成功后获取高频问题列表
     **/
    private String getSelectAgent(UserSceneSession session,Integer page){
        List<ToolMeta> toolMetaAll = session.getAuthTool();
        String sSceneId = session.getCurrentScene().getSId();
        toolMetaAll = toolMetaAll.stream().filter(to-> to.getSSceneId().equals(session.getCurrentScene().getSId())).collect(Collectors.toUnmodifiableList());
        StringBuffer sb = new StringBuffer();
        List<List<ToolMeta>> firstFive = ListUtil.split(toolMetaAll,5);
        List<ToolMeta> showList;
        if(ObjectUtil.isNotEmpty(firstFive.get(page-1))){
            showList = firstFive.get(page-1);
            page = page + 1;
        }else{
            showList = firstFive.get(0);
            page = 1;
        }
        showList.forEach(one->{
            sb.append("<div style=\"color: #4096ff; margin-top: 5px;display:flex;align-items:center;font-size:12px;\" data-action=\"reset\" data-text=\"")
                    .append(one.getSMethodName()).append("\" onclick=\"reset(").append(one.getSMethodName()).append("\">")
                    .append("<span style=\"display:inline-block; width:5px; height:5px; background-color:#ccc; border-radius:50%; margin-right:5px;\"></span>")
                    .append(one.getSMethodName())
                    .append(" </div>");
        });
        sb.append("</div>");
        sb.append(" <div style=\"color: #4096ff;  margin-top: 5px;width:100%;text-align:right;margin-right:10px;font-size:12px;\" data-action=\"resetTag\" data-text=\"").append(sSceneId).append(",").append(page).append("\" onclick=\"reset(换一换)\">");
        sb.append(" 换一换").append("</div>");
        return sb.toString();
    }

    /**
     * @Author 钱豹
     * @Date 13:32 2026/2/6
     * @Param [input, session]
     * @return reactor.core.publisher.Flux<AiResponseDTO>
     * @Description 获取智普通智能体（流式返回）
     **/
    private Flux<AiResponseDTO> getChatiAgentStream(String input, UserSceneSession session) {
        String sceneName = ObjectUtil.isNotEmpty(session.getCurrentScene())
                ? session.getCurrentScene().getSSceneName() : StrUtil.EMPTY;
        String methodName = ObjectUtil.isNotEmpty(session.getCurrentTool())
                ? session.getCurrentTool().getSMethodName() : "随便聊聊";

        // 从缓存获取或创建ChatiAgent
        ChatiAgent chatiAgent = UserSceneSessionService.CHAT_AGENT_CACHE.get(session.getUserId());
        if (ObjectUtil.isEmpty(chatiAgent)) {
            chatiAgent = AiServices.builder(ChatiAgent.class)
                    .chatLanguageModel(chatiModel)
                    .chatMemoryProvider(operableChatMemoryProvider)
                    .build();
            UserSceneSessionService.CHAT_AGENT_CACHE.put(session.getUserId(), chatiAgent);
        }

        // 调用流式聊天方法
        return chatiAgent.chatStream(session.getUserId(), input)
                .map(chunk -> AiResponseDTO.builder()
                        .sSceneName(sceneName)
                        .sMethodName(methodName)
                        .aiText(chunk)
                        .systemText(StrUtil.EMPTY)
                        .sReturnType(ReturnTypeCode.STREAM.getCode())
                        .build());
    }

    /***
     * @Author 钱豹
     * @Date 13:32 2026/2/6
     * @Param [input, session]
     * @return java.lang.String
     * @Description 获取智普通智能体
     **/
    private AiResponseDTO getChatiAgent (String input,UserSceneSession session){
        String sceneName = ObjectUtil.isNotEmpty(session.getCurrentScene())?session.getCurrentScene().getSSceneName():StrUtil.EMPTY;
        String methodName = ObjectUtil.isNotEmpty(session.getCurrentTool())?session.getCurrentTool().getSMethodName():"随便聊聊";
        ChatiAgent chatiAgent = UserSceneSessionService.CHAT_AGENT_CACHE.get(session.getUserId());
        if(ObjectUtil.isEmpty(chatiAgent)){
            chatiAgent = AiServices.builder(ChatiAgent.class)
                    .chatLanguageModel(chatiModel)
                    .chatMemoryProvider(operableChatMemoryProvider)
                    .build();
            UserSceneSessionService.CHAT_AGENT_CACHE.put(session.getUserId(), chatiAgent);                }
        String sChatMessage = chatiAgent.chat(session.getUserId(), input);
        return AiResponseDTO.builder().sSceneName(sceneName).sMethodName(methodName).aiText(sChatMessage).systemText(StrUtil.EMPTY).sReturnType(ReturnTypeCode.HTML.getCode()).build();
    }
    /***
     * @Author 钱豹
     * @Date 12:14 2026/3/8
     * @Param [chatMessage, memoryId]
     * @return void
     * @Description 随便聊聊移除最后一个AI 返回信息 跟系统询问的随便聊聊
     **/
    private void removeMssageSbll(List<ChatMessage> chatMessage,String memoryId){
        if(chatMessage!=null){
//            operableChatMemoryProvider.deleteSingleMessage(memoryId,chatMessage.get(chatMessage.size()-1));
            for(int i=chatMessage.size()-1;i>0;i--){
                ChatMessage data = chatMessage.get(i);
                ChatMessageType sType  = data.type();
                if(ChatMessageType.SYSTEM.equals(sType)){
                    operableChatMemoryProvider.deleteSingleMessage(memoryId,data);
                    return;
                }
            }
        }
    }

}