package com.smtaiserver.smtaiserver.javaai.qwen.agent;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

import com.smtaiserver.smtaiserver.core.SMTAIServerApp;
import com.smtaiserver.smtaiserver.core.SMTAIServerRequest;
import com.smtaiserver.smtaiserver.database.SMTDatabase;
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecord;
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecords;
import com.smtaiserver.smtaiserver.javaai.SMTJavaAIError;
import com.smtaiserver.smtaiserver.javaai.llm.core.SMTLLMConnect;
import com.smtaiserver.smtaiserver.javaai.qwen.sqltree.SMTQwenASTSQLTreeParser;
import com.smtservlet.util.Json;
import com.smtservlet.util.SMTJsonWriter;
import com.smtservlet.util.SMTStatic;

public class SMTQwenAgentRecordsetVPROP extends SMTQwenAgent
{
	private static class ASTSQLTree
	{
		public boolean						_depandPrev = false;
		public String						_pname		= null;
		public String						_ptitle		= null;
		public String						_title		= null;
		public boolean						_isSummary  = false;
		public SMTJsonWriter				_jsonSQL	= new SMTJsonWriter(false);
		public Map<String, SMTJsonWriter>	_mapId2SQLParam = new LinkedHashMap<>();
		
		public ASTSQLTree()
		{
		}
		
		public String getSQLParamId(String key)
		{
			return _mapId2SQLParam.get(key).getRootJson().getJson("id").asString();
		}
	}
	
	private static class ASTResult
	{
		public List<ASTSQLTree>		_listSQLTree = new ArrayList<>();
	}
	
	private static class ASTReport
	{
		public String						_title      = "";
		public boolean						_isSummary  = false;
		public List<ASTSQLTree>				_listSQLTree	= new ArrayList<>();
		public Map<String, Json>			_mapId2SQLParam = new LinkedHashMap<>();
		
		public void mergeSQLParamMap(Map<String, SMTJsonWriter> map)
		{
			for(SMTJsonWriter jsonWr : map.values())
			{
				Json jsonSQLParam = jsonWr.getRootJson();
				String id = jsonSQLParam.getJson("id").asString();
				if(_mapId2SQLParam.containsKey(id))
					continue;
				_mapId2SQLParam.put(id, jsonSQLParam);
			}
		}
	}
	
	//////////////////////////////////////////////////////////////////
	private String			_promptVPROP;
	
	@Override
	public void initInstance(DBRecord rec) throws Exception
	{
		super.initInstance(rec);
		_promptVPROP = (String)SMTAIServerApp.getApp().getGlobalConfig("prompt.tool_recordset_vprop"); 
	}
	
	@Override
	public SMTJavaAIError callAgents(String jsonPath, Json jsonArgs, SMTLLMConnect llm, String question, SMTAIServerRequest tranReq) throws Exception
	{
		long tick = System.currentTimeMillis();
		String answer = llm.callWithMessage(new String[] {_promptVPROP}, question, tranReq).replace("\r", "");
		tranReq.traceLLMPrompt(_promptVPROP);
		Json jsonAST = SMTStatic.convLLMAnswerToJson(answer, true);
		
		return callJsonAST(jsonAST, tick, tranReq);
	}
	
	private SMTJavaAIError callJsonAST(Json jsonAST, long tick, SMTAIServerRequest tranReq) throws Exception
	{
		SMTJsonWriter jsonWrResult = tranReq.getResultJsonWr();
		
		tranReq.traceLLMDebug("callAgents：[" + ((double)(System.currentTimeMillis() - tick) / 1000) + "秒] [\n" + SMTStatic.formatJson(jsonAST) + "\n]");

		jsonWrResult.addKeyValue("answer_type", "summary");
		jsonWrResult.beginArray("summary");
		SMTJavaAIError aiError = queryRecordByAST(jsonAST.asJsonList(), tranReq);
		jsonWrResult.endArray();
		
		return aiError;
	}
	
	public SMTJavaAIError callExtJson(Json jsonAST, SMTLLMConnect llm, String question, SMTAIServerRequest tranReq) throws Exception
	{
		long tick = System.currentTimeMillis();
		
		return callJsonAST(jsonAST, tick, tranReq);
	}
	
	private SMTJavaAIError queryRecordByAST(List<Json> jsonASTList, SMTAIServerRequest tranReq) throws Exception
	{
		SMTJsonWriter jsonWr = tranReq.getResultJsonWr();
		
		SMTDatabase db = SMTAIServerApp.getApp().getDataSource("DS_43").allocDatabase();
		try
		{
			ASTResult astResult = new ASTResult();
			
			// 解析所有语法树
			for(Json jsonAST : jsonASTList)
			{
				if(!jsonAST.getJson("args").safeGetBoolean("is_output", true))
					continue;
				
				// 如果发现语法树报错，则直接返回错误
				{
					String error;
					error = jsonAST.safeGetStr("error", null);
					if(!SMTStatic.isNullOrEmpty(error))
						return new SMTJavaAIError(error);
				}
				
				// 解析语法树
				SMTJavaAIError error = queryRecordAST2SQLTree(db, jsonAST, tranReq, astResult);
				if(error != null)
					return error;
			}
			
			List<ASTReport>  listReport = mergeASTResultToReportList(astResult);
			
			// 根据解析的语法树生成返回结果和中间语法树
			for(ASTReport ASTReport : listReport)
			{
				if(ASTReport._isSummary)
				{
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("type", "summary");
						String[] title = new String[] {ASTReport._title};
						outputSummaryToJson(db, ASTReport, jsonWr, tranReq, title);
						jsonWr.addKeyValue("title", title[0]);
					}
					jsonWr.endMap();
				}
				else
				{
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("type", "recordset");
						jsonWr.addKeyValue("chart", "muli_line");
						
				
						// 生成字段
						jsonWr.beginArray("cols");
						{
							jsonWr.beginMap(null);
							{
								jsonWr.addKeyValue("title", "时间");
								jsonWr.addKeyValue("type", "time");
							}
							jsonWr.endMap();
							
							jsonWr.beginMap(null);
							{
								jsonWr.addKeyValue("title", "名称");
								jsonWr.addKeyValue("type", "name");
							}
							jsonWr.endMap();
							
							jsonWr.beginMap(null);
							{
								jsonWr.addKeyValue("title", "值");
								jsonWr.addKeyValue("type", "value");
							}
							jsonWr.endMap();
						}
						jsonWr.endArray();
						
						// 查询数据
						String[] title = new String[] {ASTReport._title};
						outputDetailToJson(db, ASTReport, jsonWr, tranReq, title);
						jsonWr.addKeyValue("title", title[0]);
					}
					jsonWr.endMap();
				}
			}
		}
		finally
		{
			db.close();
		}
		
		return null;
	}
	
	private List<ASTReport> mergeASTResultToReportList(ASTResult astResult)
	{
		Set<Integer> setChecked = new HashSet<>();
		List<ASTReport> listReport = new ArrayList<>();
		
		for(int i = 0; i < astResult._listSQLTree.size(); i ++)
		{
			if(setChecked.contains(i))
				continue;
			setChecked.add(i);
			
			ASTReport report = mergeASTResultToReport(astResult._listSQLTree.get(i), astResult, setChecked);
			listReport.add(report);
		}
		
		return listReport;
	}
	
	private ASTReport mergeASTResultToReport(ASTSQLTree SQLTree, ASTResult astResult, Set<Integer> setChecked)
	{
		ASTReport report = new ASTReport();
		boolean isSummary = SQLTree._isSummary;
		
		// 将第一个SQLTree作为基准
		report._isSummary = SQLTree._isSummary;
		report._listSQLTree.add(SQLTree);
		report._title = SQLTree._title;
		report.mergeSQLParamMap(SQLTree._mapId2SQLParam);

		
		// 检查是否有其他SQLTree可以合并到这个SQLKey中
		for(int i = 0; i < astResult._listSQLTree.size(); i ++)
		{
			ASTSQLTree curSQLTree = astResult._listSQLTree.get(i);
			if(setChecked.contains(i) || curSQLTree._isSummary != isSummary)
				continue;
			
			// 如果是summary对象，则对象名和时间范围相同可以合并
			if(isSummary)
			{
				mergeASTReportSummary(SQLTree, curSQLTree, report, i, setChecked);
			}
			// 如果是detail对象，则时间范围相同可以合并
			else
			{
				mergeASTReportDetail(SQLTree, curSQLTree, report, i, setChecked);
			}
		}
		return report;
	}
	
	private boolean mergeASTReportDetail(ASTSQLTree SQLTree, ASTSQLTree curSQLTree, ASTReport report, int index, Set<Integer> setChecked)
	{
		SMTJsonWriter jsonWrTimeS1 = SQLTree._mapId2SQLParam.get("start_time");
		SMTJsonWriter jsonWrTimeS2 = curSQLTree._mapId2SQLParam.get("start_time");
		SMTJsonWriter jsonWrTimeE1 = SQLTree._mapId2SQLParam.get("end_time");
		SMTJsonWriter jsonWrTimeE2 = curSQLTree._mapId2SQLParam.get("end_time");
		SMTJsonWriter jsonWrStep1 = SQLTree._mapId2SQLParam.get("step_time");
		SMTJsonWriter jsonWrStep2 = curSQLTree._mapId2SQLParam.get("step_time");
		
		if(jsonWrTimeS1 == null || jsonWrTimeS2 == null || jsonWrTimeE1 == null || jsonWrTimeE2 == null || jsonWrStep1 == null || jsonWrStep2 == null)
			return false;				
		
		String timeS1 = jsonWrTimeS1.getRootJson().getJson("value").asString();
		String timeS2 = jsonWrTimeS2.getRootJson().getJson("value").asString();
		String timeE1 = jsonWrTimeE1.getRootJson().getJson("value").asString();
		String timeE2 = jsonWrTimeE2.getRootJson().getJson("value").asString();
		String step1  = jsonWrStep1.getRootJson().getJson("value").asString();
		String step2  = jsonWrStep2.getRootJson().getJson("value").asString();
		if(!timeS1.equals(timeS2) || !timeE1.equals(timeE2)|| !step1.equals(step2))
			return false;
		
		jsonWrTimeS2.addKeyValue("ref_id", jsonWrTimeS1.getRootJson().getJson("id").asString());
		jsonWrTimeS2.addKeyValue("hide", true); 

		jsonWrTimeE2.addKeyValue("ref_id", jsonWrTimeE1.getRootJson().getJson("id").asString());
		jsonWrTimeE2.addKeyValue("hide", true); 
		
		jsonWrStep2.addKeyValue("ref_id", jsonWrStep1.getRootJson().getJson("id").asString());
		jsonWrStep2.addKeyValue("hide", true); 
		
		report._listSQLTree.add(curSQLTree);
		report.mergeSQLParamMap(curSQLTree._mapId2SQLParam);
		setChecked.add(index);
		
		return true;
	}
	
	private boolean mergeASTReportSummary(ASTSQLTree SQLTree, ASTSQLTree curSQLTree, ASTReport report, int index, Set<Integer> setChecked)
	{
		SMTJsonWriter jsonWrOTYPE1 = SQLTree._mapId2SQLParam.get("otype");
		SMTJsonWriter jsonWrOTYPE2 = curSQLTree._mapId2SQLParam.get("otype");
		
		if(jsonWrOTYPE1 == null || jsonWrOTYPE2 == null)
			return false;
		
		String OTYPE1 = jsonWrOTYPE1.getRootJson().getJson("value").asString();
		String OTYPE2 = jsonWrOTYPE2.getRootJson().getJson("value").asString();
		if(!OTYPE1.equals(OTYPE2))
			return false;
		
		SMTJsonWriter jsonWrTimeS1 = SQLTree._mapId2SQLParam.get("start_time");
		SMTJsonWriter jsonWrTimeS2 = curSQLTree._mapId2SQLParam.get("start_time");
		SMTJsonWriter jsonWrTimeE1 = SQLTree._mapId2SQLParam.get("end_time");
		SMTJsonWriter jsonWrTimeE2 = curSQLTree._mapId2SQLParam.get("end_time");
		
		if(jsonWrTimeS1 == null || jsonWrTimeS2 == null || jsonWrTimeE1 == null || jsonWrTimeE2 == null)
			return false;				
		
		String timeS1 = jsonWrTimeS1.getRootJson().getJson("value").asString();
		String timeS2 = jsonWrTimeS2.getRootJson().getJson("value").asString();
		String timeE1 = jsonWrTimeE1.getRootJson().getJson("value").asString();
		String timeE2 = jsonWrTimeE2.getRootJson().getJson("value").asString();
		if(!timeS1.equals(timeS2) || !timeE1.equals(timeE2))
			return false;
		
		jsonWrOTYPE2.addKeyValue("ref_id", jsonWrOTYPE1.getRootJson().getJson("id").asString());
		jsonWrOTYPE2.addKeyValue("hide", true); 
		
		jsonWrTimeS2.addKeyValue("ref_id", jsonWrTimeS1.getRootJson().getJson("id").asString());
		jsonWrTimeS2.addKeyValue("hide", true); 

		jsonWrTimeE2.addKeyValue("ref_id", jsonWrTimeE1.getRootJson().getJson("id").asString());
		jsonWrTimeE2.addKeyValue("hide", true); 
		
		report._listSQLTree.add(curSQLTree);
		report.mergeSQLParamMap(curSQLTree._mapId2SQLParam);
		setChecked.add(index);
		
		return true;

	}
	
	private SMTJavaAIError outputDetailToJson(SMTDatabase db, ASTReport ASTReport, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq, String[] r_title) throws Exception
	{
		// 将参数信息打包到param_json
		SMTJsonWriter jsonParamJson = tranReq.prepareParamJsonWriter();
		jsonParamJson.beginArray("param_json");
		{
			jsonParamJson.beginMap(null);
			{
				jsonParamJson.addKeyValue("type", "query_detail");
				jsonParamJson.addKeyValue("title", r_title[0]);
				
				jsonParamJson.beginMap("params");
				for(Json jsonParam : ASTReport._mapId2SQLParam.values())
				{
					String id = jsonParam.getJson("id").asString();
					jsonParamJson.addKeyRaw(id, jsonParam);
				}
				jsonParamJson.endMap();
				
				jsonParamJson.beginArray("sql_json");
				for(ASTSQLTree SQLTree : ASTReport._listSQLTree)
				{
					jsonParamJson.addKeyRaw(null, SQLTree._jsonSQL.getRootJson());
				}
				jsonParamJson.endArray();
				
			}
			jsonParamJson.endMap();
		}
		jsonParamJson.endArray();

		// 创建查询器
		SMTQwenASTSQLTreeParser  parser = new SMTQwenASTSQLTreeParser(ASTReport._mapId2SQLParam.values());
		r_title[0] = parser.convStringToTitle(r_title[0]);
		
		parser.outputSQLParamToJson(jsonWr);
		
		// 输出每个查询的结果
		for(ASTSQLTree SQLTree : ASTReport._listSQLTree)
		{
			parser.queryDetailValuesToJson(db, SQLTree._jsonSQL.getRootJson(), SQLTree._title, jsonWr, tranReq);
		}

		return null;
	}
	
	private SMTJavaAIError outputSummaryToJson(SMTDatabase db, ASTReport ASTReport, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq, String[] r_title) throws Exception
	{
		// 将参数信息打包到param_json
		SMTJsonWriter jsonParamJson = tranReq.prepareParamJsonWriter();
		jsonParamJson.beginArray("param_json");
		{
			jsonParamJson.beginMap(null);
			{
				jsonParamJson.addKeyValue("type", "query_summary");
				jsonParamJson.addKeyValue("title", r_title[0]);
				
				jsonParamJson.beginMap("params");
				for(Json jsonParam : ASTReport._mapId2SQLParam.values())
				{
					String id = jsonParam.getJson("id").asString();
					jsonParamJson.addKeyRaw(id, jsonParam);
				}
				jsonParamJson.endMap();
				
				jsonParamJson.beginArray("sql_json");
				for(ASTSQLTree SQLTree : ASTReport._listSQLTree)
				{
					jsonParamJson.addKeyRaw(null, SQLTree._jsonSQL.getRootJson());
				}
				jsonParamJson.endArray();
				
			}
			jsonParamJson.endMap();
		}
		jsonParamJson.endArray();

		// 创建查询器
		SMTQwenASTSQLTreeParser  parser = new SMTQwenASTSQLTreeParser(ASTReport._mapId2SQLParam.values());
		r_title[0] = parser.convStringToTitle(r_title[0]);
		
		parser.outputSQLParamToJson(jsonWr);
		
		// 输出每个查询的结果
		for(ASTSQLTree SQLTree : ASTReport._listSQLTree)
		{
			parser.querySummaryValuesToJson(db, SQLTree._jsonSQL.getRootJson(), jsonWr, tranReq);
		}
		

		return null;
	}

	private SMTJavaAIError queryRecordAST2SQLTree(SMTDatabase db, Json jsonAST, SMTAIServerRequest tranReq, ASTResult r_parentResult) throws Exception
	{
		SMTJavaAIError aiError;
		String call = jsonAST.getJson("call").asString();
		Json jsonArgs = jsonAST.getJson("args");

		
		if("query_detail".equals(call))
		{
			aiError = call_query_detail(db, jsonArgs, tranReq, r_parentResult);
		}
		else if("comp_values".equals(call))
		{
			aiError = call_comp_values(db, jsonArgs, tranReq, r_parentResult);
		}
		else if("more_values".equals(call))
		{
			aiError = call_more_values(db, jsonArgs, tranReq, r_parentResult);
		}
		else
		{
			return new SMTJavaAIError("未知调用：" + call);
		}
		
		return aiError;

	}
	
	private SMTJavaAIError call_more_values(SMTDatabase db, Json jsonArgs, SMTAIServerRequest tranReq, ASTResult r_parentResult) throws Exception
	{
		Json jsonRecordsets = jsonArgs.safeGetJson("recordsets");
		if(jsonRecordsets == null || !jsonRecordsets.isArray())
			return new SMTJavaAIError("记录集解析错误");
		
		for(Json jsonAST : jsonRecordsets.asJsonList())
		{
			SMTJavaAIError error = queryRecordAST2SQLTree(db, jsonAST, tranReq, r_parentResult);
			if(error != null)
				return error;
		}
		
		return null;
	}
	
	private SMTJavaAIError call_comp_values(SMTDatabase db, Json jsonArgs, SMTAIServerRequest tranReq, ASTResult r_parentResult) throws Exception
	{
		String compType = jsonArgs.safeGetStr("comp_type", "");
		Json jsonRecordset1 = jsonArgs.safeGetJson("recordset1");
		Json jsonRecordset2 = jsonArgs.safeGetJson("recordset2");
		String compName = jsonArgs.safeGetStr("comp_name", "");
		if(jsonRecordset1 == null || jsonRecordset2 == null || SMTStatic.isNullOrEmpty(compType))
			return new SMTJavaAIError("记录集解析错误");
		

		SMTJavaAIError aiError;
		
		// 获取第一个SQL
		ASTResult result1 = new ASTResult();
		if((aiError = queryRecordAST2SQLTree(db, jsonRecordset1, tranReq, result1)) != null)
			return aiError;

		
		for(ASTSQLTree SQLTree1 : result1._listSQLTree)
		{
			// 获取第二个SQL
			ASTResult result2 = new ASTResult();
			if((aiError = queryRecordAST2SQLTree(db, jsonRecordset2, tranReq, result2)) != null)
				return aiError;
			
			// 将两个操作合并
			for(ASTSQLTree SQLTree2 : result2._listSQLTree)
			{
				if((aiError = merge_comp_values(db, compType, compName, tranReq, SQLTree1, SQLTree2, r_parentResult)) != null)
					return aiError;
			}
		}

		
		return null;
	}
	
	private SMTJavaAIError merge_comp_values(SMTDatabase db, String compType, String compName, SMTAIServerRequest tranReq, ASTSQLTree SQLTree1, ASTSQLTree SQLTree2, ASTResult r_parentResult) throws Exception
	{
		ASTSQLTree SQLTreeComp = new ASTSQLTree();
		SQLTreeComp._pname = SQLTree1._pname;
		SQLTreeComp._ptitle = SQLTree1._ptitle;
		
		// STEP_TIME引用第一个
		{
			SMTJsonWriter jsonWrSTEP1 = SQLTree1._mapId2SQLParam.get("step_time");
			SMTJsonWriter jsonWrSTEP2 = SQLTree2._mapId2SQLParam.get("step_time");
			if(jsonWrSTEP1 != null && jsonWrSTEP2 != null)
			{
				jsonWrSTEP2.addKeyValue("ref_id", jsonWrSTEP1.getRootJson().getJson("id").asString());
				jsonWrSTEP2.addKeyValue("hide", true);
			}
		}
		
		// 如果OTYPE相同，则第二个查询的OTYPE引用到第一个，如果不同则title添加设备名
		{
			SMTJsonWriter jsonWrOTYPE1 = SQLTree1._mapId2SQLParam.get("otype");
			SMTJsonWriter jsonWrOTYPE2 = SQLTree2._mapId2SQLParam.get("otype");
			if(jsonWrOTYPE1 != null && jsonWrOTYPE2 != null)
			{
				String value1 = jsonWrOTYPE1.getRootJson().getJson("value").asString();
				String value2 = jsonWrOTYPE2.getRootJson().getJson("value").asString();
				if(value1.equals(value2))
				{
					jsonWrOTYPE2.addKeyValue("ref_id", jsonWrOTYPE1.getRootJson().getJson("id").asString());
					jsonWrOTYPE2.addKeyValue("hide", true);
				}
				else
				{
					jsonWrOTYPE1.addKeyValue("title", SQLTree1._ptitle + "指标");
					jsonWrOTYPE2.addKeyValue("title", SQLTree2._ptitle + "指标");
				}
			}
		}
		
		// 如果时间范围相同，则将第二个时间范围引用到第一个，如果不同则title添加设备名，并且将终止时间设置为"offset_timestamp"
		{
			SMTJsonWriter jsonWrTIMES1 = SQLTree1._mapId2SQLParam.get("start_time");
			SMTJsonWriter jsonWrTIMES2 = SQLTree2._mapId2SQLParam.get("start_time");
			SMTJsonWriter jsonWrTIMEE1 = SQLTree1._mapId2SQLParam.get("end_time");
			SMTJsonWriter jsonWrTIMEE2 = SQLTree2._mapId2SQLParam.get("end_time");	
			if(jsonWrTIMES1 != null && jsonWrTIMES2 != null && jsonWrTIMEE1 != null && jsonWrTIMEE2 != null)
			{
				String timeS1 = jsonWrTIMES1.getRootJson().getJson("value").asString();
				String timeS2 = jsonWrTIMES2.getRootJson().getJson("value").asString();	
				String timeE1 = jsonWrTIMEE1.getRootJson().getJson("value").asString();
				String timeE2 = jsonWrTIMEE2.getRootJson().getJson("value").asString();
				
				if(timeS1.equals(timeS2) && timeE1.equals(timeE2))
				{
					jsonWrTIMES2.addKeyValue("ref_id", jsonWrTIMES1.getRootJson().getJson("id").asString());
					jsonWrTIMES2.addKeyValue("hide", true);
					jsonWrTIMEE2.addKeyValue("ref_id", jsonWrTIMEE1.getRootJson().getJson("id").asString());
					jsonWrTIMEE2.addKeyValue("hide", true);					
				}
				else
				{
					jsonWrTIMES1.addKeyValue("title", SQLTree1._ptitle + "起始时间");
					jsonWrTIMES2.addKeyValue("title", SQLTree2._ptitle + "起始时间");
					jsonWrTIMEE1.addKeyValue("title", SQLTree1._ptitle + "结束时间");
					jsonWrTIMEE2.addKeyValue("title", SQLTree2._ptitle + "结束时间");
					jsonWrTIMEE2.addKeyValue("hide", true);
					
					SQLTree2._jsonSQL.beginMap("offset_timestamp");
					{
						SQLTree2._jsonSQL.addKeyValue("type", "offset_timestamp");
						SQLTree2._jsonSQL.beginMap("cur_start_time");
						{
							SQLTree2._jsonSQL.addKeyValue("type", "start_time");
							SQLTree2._jsonSQL.addKeyValue("id", jsonWrTIMES2.getRootJson().getJson("id").asString());
						}
						SQLTree2._jsonSQL.endMap();
						
						SQLTree2._jsonSQL.beginMap("prev_start_time");
						{
							SQLTree2._jsonSQL.addKeyValue("type", "start_time");
							SQLTree2._jsonSQL.addKeyValue("id", jsonWrTIMES1.getRootJson().getJson("id").asString());
						}
						SQLTree2._jsonSQL.endMap();
						
						SQLTree2._jsonSQL.beginMap("prev_end_time");
						{
							SQLTree2._jsonSQL.addKeyValue("type", "end_time");
							SQLTree2._jsonSQL.addKeyValue("id", jsonWrTIMEE1.getRootJson().getJson("id").asString());
						}
						SQLTree2._jsonSQL.endMap();
		
					}	
					SQLTree2._jsonSQL.endMap();
				}
			}
		}
		
		// 将双方的参数合并
		for(Entry<String, SMTJsonWriter> entry : SQLTree1._mapId2SQLParam.entrySet())
		{
			SQLTreeComp._mapId2SQLParam.put(entry.getKey(), entry.getValue());
		}
		for(Entry<String, SMTJsonWriter> entry : SQLTree2._mapId2SQLParam.entrySet())
		{
			SQLTreeComp._mapId2SQLParam.put(entry.getValue().getRootJson().getJson("id").asString(), entry.getValue());
		}
		
		SQLTreeComp._depandPrev = SQLTree1._depandPrev;
		SQLTreeComp._jsonSQL.addKeyValue("call", "comp_values");
		SQLTreeComp._jsonSQL.addKeyRaw("recordset1", SQLTree1._jsonSQL.getRootJson());
		SQLTreeComp._jsonSQL.addKeyRaw("recordset2", SQLTree2._jsonSQL.getRootJson());
		SQLTreeComp._jsonSQL.addKeyValue("operate", compType);
		
	
		if(SQLTree1._title.equals(SQLTree2._title))
		{
			SQLTreeComp._title = SQLTree1._title + compName;
		}
		else
		{
			SQLTreeComp._title = SQLTree1._title + "和" + SQLTree2._title + "的" + compName;
		}
		SQLTreeComp._jsonSQL.addKeyValue("title", SQLTreeComp._title);
		r_parentResult._listSQLTree.add(SQLTreeComp);
		
		return null;
	}
	
	private static class QueryDetailRec
	{
		public String		_PTYPE;
		public String		_PNAME;
		public String		_OTYPE;
		public String		_ONAME;
		public String		_START_TIME;
		public String		_END_TIME;
		public String		_M_TABLE;
		public String		_F_OTIME;
		public String		_F_VALUE;
		public String 		_P_TITLE;
		public String[]		_GROUP_OP_LIST;
		public String		_STEP_TIME;
		public String 		_REC_GROUP;
		public String		_OTYPE_TITLE;
		public String		_PTYPE_TITLE;
		
	}
	
	private SMTJavaAIError call_query_detail(SMTDatabase db, Json jsonArgs, SMTAIServerRequest tranReq, ASTResult r_parentResult) throws Exception
	{
		SMTJavaAIError error;
		SMTDatabase dbSys = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			// 查询基本信息
			QueryDetailRec recDetail = new QueryDetailRec();
			if((error = queryDetailBaseInfo(dbSys, jsonArgs, tranReq, recDetail)) != null)
				return error;
			
			// 当前对象加入扩展
			{
				SMTJsonWriter jsonWrExtCall = tranReq.prepareExtCallJsonWriter();
				jsonWrExtCall.beginMap(null);
				{
					jsonWrExtCall.addKeyValue("ext_call_key", "yesterday_YOY@" + recDetail._PTYPE+ "@" + recDetail._PNAME + "@" + recDetail._OTYPE + "@同比");
					jsonWrExtCall.addKeyValue("question", "查询" + recDetail._P_TITLE + recDetail._OTYPE_TITLE + "的昨日和去年同期的同比");
					jsonWrExtCall.addKeyValue("call_ext_id", "yesterday_YOY");
					jsonWrExtCall.beginMap("agrs");
					{
						jsonWrExtCall.addKeyValue("PNAME", recDetail._P_TITLE);
						jsonWrExtCall.addKeyValue("PTYPE", recDetail._PTYPE_TITLE);
						jsonWrExtCall.addKeyValue("VPROP", recDetail._OTYPE_TITLE);
					}
					jsonWrExtCall.endMap();
				}
				jsonWrExtCall.endMap();
			}
			
			// 按照分组进行循环
			for(String groupName : recDetail._GROUP_OP_LIST)
			{
				ASTSQLTree SQLTree = createQuerySQLTree(recDetail);
				
				// 进入summary模式
				if(SMTStatic.isNullOrEmpty(recDetail._STEP_TIME) && !SMTStatic.isNullOrEmpty(groupName))
				{
					SQLTree._isSummary = true;
					createSQLParamOTYPE(dbSys, recDetail, SQLTree);
					createSQLParamStartTime(recDetail, SQLTree);
					createSQLParamEndTime(recDetail, SQLTree);
					
					setGroupAndTitleToJson(groupName, recDetail, SQLTree);
					
					r_parentResult._listSQLTree.add(SQLTree);
				}
				else
				{
					SQLTree._isSummary = false;
					
					createSQLParamOTYPE(dbSys, recDetail, SQLTree);
					createSQLParamStartTime(recDetail, SQLTree);
					createSQLParamEndTime(recDetail, SQLTree);
					
					setGroupAndTitleToJson(groupName, recDetail, SQLTree);
					
					r_parentResult._listSQLTree.add(SQLTree);
				}
			}

		}
		finally
		{
			dbSys.close();
		}			
		return null;
	}
	
	private void setGroupAndTitleToJson(String group, QueryDetailRec recDetail, ASTSQLTree r_SQLTree)
	{
		SMTJsonWriter jsonWr = r_SQLTree._jsonSQL;
		
		String stepTime = recDetail._STEP_TIME;
		boolean isRealValue = false;
		if(SMTStatic.isNullOrEmpty(group) && SMTStatic.isNullOrEmpty(stepTime))
		{
			group = "avg";
			stepTime = "5 minutes";
			isRealValue = true;
		}
		// 如果分组未指定，则avg运行
		else if(SMTStatic.isNullOrEmpty(group))
		{
			group = "avg";
			isRealValue = true;
		}
		
		String groupName;
		String groupSQL;
		String groupItem = group.toLowerCase();
		if("max".equals(groupItem) || "最大".equals(groupItem))
		{
			groupSQL = "max";
			groupName = "最大值";
		}
		else if("min".equals(groupItem) || "最小".equals(groupItem))
		{
			groupSQL = "min";
			groupName = "最小值";
		}
		else
		{
			groupSQL = "avg";
			groupName = "平均值";
		}
		if(isRealValue)
			groupName = "";
		
		jsonWr.beginMap("group");
		{
			if(r_SQLTree._isSummary)
			{
				jsonWr.addKeyValue("type", "summary");
				jsonWr.addKeyValue("operate", groupSQL);
				jsonWr.addKeyValue("title", groupName);
			}
			else
			{
				String idParamStepTime = SMTStatic.newUUID();
				SMTJsonWriter sqlParamWr = new SMTJsonWriter(false);
				r_SQLTree._mapId2SQLParam.put("step_time", sqlParamWr);
				sqlParamWr.addKeyValue("id", idParamStepTime);
				sqlParamWr.addKeyValue("title", "时长");
				sqlParamWr.addKeyValue("type", "list");
				sqlParamWr.addKeyValue("value", stepTime);
				sqlParamWr.beginArray("list");
				{
					sqlParamWr.beginMap(null);
					{
						sqlParamWr.addKeyValue("title", "5分钟");
						sqlParamWr.addKeyValue("value", "5 minutes");
					}
					sqlParamWr.endMap();
					
					sqlParamWr.beginMap(null);
					{
						sqlParamWr.addKeyValue("title", "10分钟");
						sqlParamWr.addKeyValue("value", "10 minutes");
					}
					sqlParamWr.endMap();
					
					sqlParamWr.beginMap(null);
					{
						sqlParamWr.addKeyValue("title", "半小时");
						sqlParamWr.addKeyValue("value", "30 minutes");
					}
					sqlParamWr.endMap();
					
					sqlParamWr.beginMap(null);
					{
						sqlParamWr.addKeyValue("title", "1小时");
						sqlParamWr.addKeyValue("value", "1 hours");
					}
					sqlParamWr.endMap();
					
					sqlParamWr.beginMap(null);
					{
						sqlParamWr.addKeyValue("title", "1天");
						sqlParamWr.addKeyValue("value", "1 days");
					}
					sqlParamWr.endMap();
					
					if(!"5 minutes".equals(stepTime) &&
					   !"10 minutes".equals(stepTime) &&
					   !"30 minutes".equals(stepTime) &&
					   !"1 hours".equals(stepTime) &&
					   !"1 days".endsWith(stepTime))
					{
						sqlParamWr.beginMap(null);
						{
							sqlParamWr.addKeyValue("title", stepTime.replace(" days", "天").replace(" minutes", "分钟").replace(" hours", "小时"));
							sqlParamWr.addKeyValue("value", stepTime);
						}
						sqlParamWr.endMap();						
					}
				}
				sqlParamWr.endArray();				
				
				jsonWr.addKeyValue("type", "time_bucket");
				jsonWr.addKeyValue("operate", groupSQL);
				jsonWr.addKeyValue("title", groupName);
				jsonWr.beginMap("step_time");
				{
					jsonWr.addKeyValue("type", "step_time");
					jsonWr.addKeyValue("id", idParamStepTime);
				}
				jsonWr.endMap();
			}
		}
		jsonWr.endMap();
		
		r_SQLTree._title = r_SQLTree.getSQLParamId("otype") + groupName;
		r_SQLTree._jsonSQL.addKeyValue("title", r_SQLTree._title);
	}
	
	private ASTSQLTree createQuerySQLTree(QueryDetailRec recDetail)
	{
		ASTSQLTree SQLTreeBase = new ASTSQLTree();
		
		// 保存快捷信息
		SQLTreeBase._pname = recDetail._PNAME;
		SQLTreeBase._ptitle = recDetail._P_TITLE;
		
		// 保存基本信息
		SQLTreeBase._jsonSQL.addKeyValue("call", "query_detail");
		SQLTreeBase._jsonSQL.addKeyValue("ptype", recDetail._PTYPE);
		SQLTreeBase._jsonSQL.addKeyValue("pname", recDetail._PNAME);
		SQLTreeBase._jsonSQL.addKeyValue("ptitle", recDetail._P_TITLE);
		SQLTreeBase._jsonSQL.addKeyValue("table_name", recDetail._M_TABLE);
		SQLTreeBase._jsonSQL.addKeyValue("column_otime", recDetail._F_OTIME);
		SQLTreeBase._jsonSQL.addKeyValue("column_value", recDetail._F_VALUE);
		
		return SQLTreeBase;
	}
	
	private String createSQLParamStartTime(QueryDetailRec recDetail, ASTSQLTree SQLTreeBase) throws Exception
	{
		String idParamId = SMTStatic.newUUID();
		SMTJsonWriter sqlParamWr = new SMTJsonWriter(false);
		
		SQLTreeBase._jsonSQL.beginMap("start_time");
		{
			SQLTreeBase._jsonSQL.addKeyValue("type", "start_time");
			SQLTreeBase._jsonSQL.addKeyValue("id", idParamId);
		}
		SQLTreeBase._jsonSQL.endMap();
		
		SQLTreeBase._mapId2SQLParam.put("start_time", sqlParamWr);
		sqlParamWr.addKeyValue("id", idParamId);
		sqlParamWr.addKeyValue("title", "起始时间");
		sqlParamWr.addKeyValue("type", "start_time");
		sqlParamWr.addKeyValue("group", recDetail._REC_GROUP);
		sqlParamWr.addKeyValue("value", recDetail._START_TIME);
		
		return idParamId;
	}
	
	private String createSQLParamEndTime(QueryDetailRec recDetail, ASTSQLTree SQLTreeBase) throws Exception
	{
		String idParamId = SMTStatic.newUUID();
		SMTJsonWriter sqlParamWr = new SMTJsonWriter(false);
		
		SQLTreeBase._jsonSQL.beginMap("end_time");
		{
			SQLTreeBase._jsonSQL.addKeyValue("type", "end_time");
			SQLTreeBase._jsonSQL.addKeyValue("id", idParamId);
		}
		SQLTreeBase._jsonSQL.endMap();
		
		SQLTreeBase._mapId2SQLParam.put("end_time", sqlParamWr);
		sqlParamWr.addKeyValue("id", idParamId);
		sqlParamWr.addKeyValue("title", "终止时间");
		sqlParamWr.addKeyValue("type", "end_time");
		sqlParamWr.addKeyValue("group", recDetail._REC_GROUP);
		sqlParamWr.addKeyValue("value", recDetail._END_TIME);
		
		return idParamId;
	}
	
	private String createSQLParamOTYPE(SMTDatabase dbSys, QueryDetailRec recDetail, ASTSQLTree SQLTreeBase) throws Exception
	{
		// 查询当前对象所包含的所有指标
		DBRecords recsRefOTYPE = dbSys.querySQL(
			  " SELECT T2.result_title, T2.result_otype, T1.result_oname FROM "
			+ " (SELECT result_otype, result_oname FROM dict_table WHERE result_ptype=? AND result_pname=? AND result_group='SDVAL') T1"
			+ " INNER JOIN"
			+ " (SELECT result_otype, result_title FROM dict_table WHERE result_group='OTYPE') T2"
			+ " ON T1.result_otype = T2.result_otype"
			,new Object[] {
			recDetail._PTYPE,
			recDetail._PNAME,
		});
		
		// 生成指标key
		String idParamOTYPE = SMTStatic.newUUID();
		SMTJsonWriter sqlParamWr = new SMTJsonWriter(false);
		SQLTreeBase._mapId2SQLParam.put("otype", sqlParamWr);
		
		SQLTreeBase._jsonSQL.beginMap("otype");
		{
			SQLTreeBase._jsonSQL.addKeyValue("type", "split_otype");
			SQLTreeBase._jsonSQL.addKeyValue("id", idParamOTYPE);
		}
		SQLTreeBase._jsonSQL.endMap();	
		
		SQLTreeBase._jsonSQL.beginMap("oname");
		{
			SQLTreeBase._jsonSQL.addKeyValue("type", "split_oname");
			SQLTreeBase._jsonSQL.addKeyValue("id", idParamOTYPE);
		}
		SQLTreeBase._jsonSQL.endMap();		
		
		sqlParamWr.addKeyValue("id", idParamOTYPE);
		sqlParamWr.addKeyValue("title", "指标");
		sqlParamWr.addKeyValue("type", "list");
		sqlParamWr.addKeyValue("value", recDetail._OTYPE + "," + recDetail._ONAME);
		sqlParamWr.beginArray("list");
		for(DBRecord recRefOTYPE : recsRefOTYPE.getRecords())
		{
			sqlParamWr.beginMap(null);
			{
				sqlParamWr.addKeyValue("title", recDetail._P_TITLE + recRefOTYPE.getString("result_title"));
				sqlParamWr.addKeyValue("value", recRefOTYPE.getString("result_otype") + "," + recRefOTYPE.getString("result_oname"));
			}
			sqlParamWr.endMap();
		}
		sqlParamWr.endArray();
		
		return idParamOTYPE;
	}

	
	private SMTJavaAIError queryDetailBaseInfo(SMTDatabase dbSys, Json jsonArgs, SMTAIServerRequest tranReq, QueryDetailRec r_recDetail) throws Exception
	{
		String askPNAME = jsonArgs.safeGetStr("pname", null);
		if(SMTStatic.isNullOrEmpty(askPNAME))
			return new SMTJavaAIError("未指定设备名称");
		
		String askPTYPE = jsonArgs.safeGetStr("ptype", null);
		String askVPROP = jsonArgs.safeGetStr("vprop", null);
		if(SMTStatic.isNullOrEmpty(askPTYPE) && SMTStatic.isNullOrEmpty(askVPROP))
			return new SMTJavaAIError("未指定设备类型或指标名");
		
		String startTime = jsonArgs.safeGetStr("start", null);
		if(SMTStatic.isNullOrEmpty(startTime))
			return new SMTJavaAIError("无法解析起始时间");
		
		String endTime = jsonArgs.safeGetStr("end", null);
		if(SMTStatic.isNullOrEmpty(endTime))
			return new SMTJavaAIError("无法解析结束时间");
		
		String stepTime = jsonArgs.safeGetStr("step_time", null);
		String group = null;
		Json jsonGroups = jsonArgs.safeGetJson("group_type");
		if(jsonGroups == null)
		{
			group = "";
		}
		else if(jsonGroups.isArray())
		{
			for(Json jsonGroup : jsonGroups.asJsonList())
			{
				if(group == null)
					group = jsonGroup.asString();
				else
					group += "," + jsonGroup.asString();
			}
		}
		else if(jsonGroups.isPrimitive())
		{
			group = jsonGroups.asString();
		}
		
		// 如果指定设备类型，则通过设备类型查找PTYPE
		String PTYPE = null;
		DBRecords recsPTYPE = null;
		if(!SMTStatic.isNullOrEmpty(askPTYPE))
		{
			recsPTYPE = dbSys.querySQL(
				  " SELECT result_ptype, match_value, result_otype"
				+ " FROM ("
				+ "		SELECT result_ptype, result_otype, tc_max_similarity(?, key_array) AS match_value, result_order"
				+ " 	FROM dict_table"
				+ " 	WHERE result_group='PTYPE'"
				+ " )T"
				+ " WHERE match_value > 0.1"
				+ " ORDER BY match_value DESC, result_order"
				+ " DESC LIMIT 1", 
				new Object[] {
					askPTYPE	
				});
			if(recsPTYPE.getRowCount() == 0)
			{
				tranReq.traceLLMDebug("未发现设备类型:" + askPTYPE + "，请输入正确的设备类型，例如：流量计，压力计等");
				return new SMTJavaAIError("未发现设备类型:" + askVPROP + "，请输入正确的设备类型，例如：流量计，压力计等");				
			}
			
			PTYPE = recsPTYPE.getRecord(0).getString("result_ptype");
			tranReq.traceLLMDebug("from ptype=[" + askPTYPE +  "] to PTYPE=" + PTYPE + "");
		}
		
		
		String OTYPE;
		String OTYPE_TITLE;
		// 如果指定了指标，则查询指标
		if(!SMTStatic.isNullOrEmpty(askVPROP))
		{
				DBRecords recsOTYPE = dbSys.querySQL(
				  " SELECT result_otype, match_value, result_title"
				+ " FROM ("
				+ "		SELECT result_otype, tc_max_similarity(?, key_array) AS match_value, result_order, result_title"
				+ " 	FROM dict_table"
				+ " 	WHERE result_group='OTYPE'"
				+ " )T"
				+ " WHERE match_value > 0.1"
				+ " ORDER BY match_value DESC, result_order"
				+ " DESC LIMIT 1", 
				new Object[] {
					askVPROP	
				});
			if(recsOTYPE.getRowCount() == 0)
			{
				tranReq.traceLLMDebug("未发现指标:" + askVPROP + "，请输入正确的指标，例如：流量，压力等");
				return new SMTJavaAIError("未发现指标:" + askVPROP + "，请输入正确的指标，例如：流量，压力等");
			}
			
			OTYPE = recsOTYPE.getRecord(0).getString("result_otype");
			OTYPE_TITLE = recsOTYPE.getRecord(0).getString("result_title");
			tranReq.traceLLMDebug("from vprop=[" + askVPROP +  "] to OTYPE=" + OTYPE + "");
		}
		// 如果未指定指标，则用PTYPE带的缺省指标
		else
		{
			OTYPE = recsPTYPE.getRecord(0).getString("result_otype");
			
			DBRecords recsOTYPETitle = dbSys.querySQL(
				"SELECT result_title FROM dict_table WHERE result_group='OTYPE' AND result_otype=?"
				, new Object[] {OTYPE});
			OTYPE_TITLE = recsOTYPETitle.getRecord(0).getString("result_title");
			tranReq.traceLLMDebug("use default OTYPE=" + OTYPE);
		}

		
		String sql = 
         	  " SELECT result_otype, result_oname, result_table, result_otime, result_column, match_value, result_title, result_p_title, result_ptype, result_pname"
			+ " FROM ("
			+ "		SELECT result_otype, result_oname, result_table, result_otime, result_column, tc_max_similarity(?, key_array) AS match_value, result_title, result_order, result_p_title, result_ptype, result_pname"
			+ " 	FROM dict_table"
			+ " 	WHERE result_group='SDVAL' AND result_otype=?"
			+ (PTYPE == null ? "" : " AND result_ptype='" + PTYPE + "'")
			+ " ) T"
			+ " ORDER BY match_value DESC, result_order"
			+ " LIMIT 5"
			;
		DBRecords recsONAME = dbSys.querySQL(sql, new Object[] {
				askPNAME,
				OTYPE
			});
		
		// 预先设置好错误内容
		String error;
		DBRecord recONAME = null;
		if(!SMTStatic.isNullOrEmpty(askPTYPE))
		{
			error = "未能找到名称为：" + askPNAME + "的" + askPTYPE + "，请核实输入名称是否正确!";
		}
		else
		{
			error = "未能找到名称为：" + askPNAME + "的" + askVPROP + "设备，请核实输入名称是否正确!";
		}
		
		// 如果没有相似对象，则直接返回错误内容
		if(recsONAME.getRowCount() == 0)
		{
			tranReq.traceLLMDebug(error);
			return new SMTJavaAIError(error);
		}
		// 如果相似对象，则直接返回错误内容+相似对象
		else
		{
			SMTJavaAIError aiError = new SMTJavaAIError(error);	
			SMTJsonWriter jsonWr = aiError.getJsonWriter();
			
			recONAME = recsONAME.getRecord(0);
			if(recONAME.getDouble("match_value") < 0.1)
			{
				tranReq.traceLLMDebug(error);

				jsonWr.beginMap("fix_question");
				{
					jsonWr.addKeyValue("title", "猜想是否查找");
					jsonWr.beginArray("values");
					for(DBRecord rec : recsONAME.getRecords())
					{

						jsonWr.beginMap(null);
						{
							String PTITLE = rec.getString("result_p_title");
							jsonWr.addKeyValue("title", PTITLE);
							jsonWr.addKeyValue("question", askPNAME + "修改为：" + PTITLE);
						}
						jsonWr.endMap();
					}
					jsonWr.endArray();
				}
				jsonWr.endMap();
				
				
				return aiError;
			}
		}
		String PTYPE_TITLE;
		{
			DBRecords recsPTYPETitle = dbSys.querySQL(
				"SELECT result_title FROM dict_table WHERE result_group='PTYPE' AND result_ptype=?"
				, new Object[] {recONAME.getString("RESULT_PTYPE")});
			PTYPE_TITLE = recsPTYPETitle.getRecord(0).getString(0);
		}
		
		tranReq.traceLLMDebug("from NAME=[" + askPNAME + "] to ONAME=" + recONAME.getString("result_oname"));
		
		// RESULT_OTYPE=[SDVAL_PRESS_W] 
		// RESULT_ONAME=[D_GW_04] 
		// RESULT_TABLE=[sd_values.sd_raws] 
		// RESULT_OTIME=[OTIME] 
		// RESULT_COLUMN=[SDVAL] 
		// MATCH_VALUE=[0.3636363744735718] 
		// RESULT_TITLE=[五一广场DN200瞬时压力] 
		// RESULT_P_TITLE=[五一广场DN200] 
		// RESULT_PTYPE=[DEV_PRESSURE_W] 
		// RESULT_PNAME=[STA91] 
		
		r_recDetail._PTYPE = recONAME.getString("RESULT_PTYPE");
		r_recDetail._PNAME = recONAME.getString("RESULT_PNAME");
		r_recDetail._OTYPE = recONAME.getString("RESULT_OTYPE");
		r_recDetail._ONAME = recONAME.getString("RESULT_ONAME");
		r_recDetail._M_TABLE = recONAME.getString("RESULT_TABLE");
		r_recDetail._F_OTIME = recONAME.getString("RESULT_OTIME");
		r_recDetail._F_VALUE = recONAME.getString("RESULT_COLUMN");
		r_recDetail._P_TITLE = recONAME.getString("RESULT_P_TITLE");
		r_recDetail._GROUP_OP_LIST = group.split("\\,");
		r_recDetail._STEP_TIME = stepTime;
		r_recDetail._START_TIME = startTime;
		r_recDetail._END_TIME = endTime;
		r_recDetail._REC_GROUP = SMTStatic.newUUID();
		r_recDetail._OTYPE_TITLE = OTYPE_TITLE;
		r_recDetail._PTYPE_TITLE = PTYPE_TITLE;
		return null;
	}
	
}