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

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.tree.DefaultText;

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.smtservlet.util.Json;
import com.smtservlet.util.SMTJsonWriter;
import com.smtservlet.util.SMTStatic;

public class SMTQwenAgentSummarySQLXML extends SMTQwenAgent
{
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLExecArg
	{
		public Json						_toolArguments = null;
		public StringBuilder			_sbSQLText = null;
		public List<Object>				_sqlParams = new ArrayList<>();
		public Map<String, DBRecords>	_mapId2Records = new HashMap<>();
		public SMTJsonWriter 			_jsonWr;
		public SMTAIServerRequest 		_tranReq;
		public SMTQwenAgentSummarySQLXML _parent;
		
		public SQLXMLExecArg(Json toolArguments, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq, SMTQwenAgentSummarySQLXML parent)
		{
			_toolArguments = toolArguments;
			_jsonWr = jsonWr;
			_tranReq = tranReq;
			_parent = parent;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static abstract class SQLXMLNode
	{
		public abstract SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception;
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodeOUT_URL extends SQLXMLNode
	{
		private boolean 	_fullUrl;
		private String 		_url;
		
		public SQLXMLNodeOUT_URL(Element xmlRoot) throws Exception
		{
			_url = SMTStatic.getXmlAttr(xmlRoot, "url");
			_fullUrl = "true".equals(SMTStatic.getXmlAttr(xmlRoot, "full_url", "false"));
		}
		
		@Override
		public SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception 
		{
			SMTJsonWriter jsonWr = execArg._jsonWr;
			jsonWr.beginMap(null);
			{	
				jsonWr.addKeyValue("type", "url");
				jsonWr.addKeyValue("url", _url);
				jsonWr.addKeyValue("full_url", _fullUrl);
			}
			jsonWr.endMap();
			
			return null;
		}
		
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodeOUT_ALL_REC extends SQLXMLNode
	{
		private static class COLUMNDef
		{
			public String 	_title;
			public String	_type;
			public int		_fldPos;
			
			public COLUMNDef(Element xmlColumn) throws Exception
			{
				_title  = SMTStatic.getXmlAttr(xmlColumn, "title");
				_type   = SMTStatic.getXmlAttr(xmlColumn, "type");
				_fldPos = Integer.parseInt(SMTStatic.getXmlAttr(xmlColumn, "field"));
			}
		}
		
		private String 				_sqlId;
		private String				_title;
		private String				_chart;
		private List<COLUMNDef>		_listCOLUMN = new ArrayList<>();
		
		public SQLXMLNodeOUT_ALL_REC(Element xmlRoot) throws Exception
		{
			_sqlId = SMTStatic.getXmlAttr(xmlRoot, "sql_id");
			_title = SMTStatic.getXmlAttr(xmlRoot, "title");
			_chart = SMTStatic.getXmlAttr(xmlRoot, "chart");
			
			for(Node nodeColumn : xmlRoot.selectNodes("COL"))
			{
				_listCOLUMN.add(new COLUMNDef((Element)nodeColumn));
			}
		}

		@Override
		public SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception
		{
			DBRecords recs = execArg._mapId2Records.get(_sqlId);
			if(recs == null)
				return null;
			
			SMTJsonWriter jsonWr = execArg._jsonWr;
			jsonWr.beginMap(null);
			{
				jsonWr.addKeyValue("type", "recordset");
				jsonWr.addKeyValue("title", _title);
				jsonWr.addKeyValue("chart", _chart);
				List<Integer> listFldPos = new ArrayList<>();
				jsonWr.beginArray("cols");
				for(COLUMNDef col : _listCOLUMN)
				{
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("title", col._title);
						jsonWr.addKeyValue("type", col._type);
						listFldPos.add(col._fldPos);
					}
					jsonWr.endMap();
				}
				jsonWr.endArray();
				jsonWr.beginArray("values");
				for(DBRecord rec : recs.getRecords())
				{
					jsonWr.beginArray(null);
					for(Integer fldPos : listFldPos)
					{
						jsonWr.addKeyValue(null, rec.getValue(fldPos));
					}
					jsonWr.endArray();
				}
				jsonWr.endArray();
			}
			jsonWr.endMap();
			
			return null;
		}		
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodeOUT_ONE_REC extends SQLXMLNode
	{
		private static class COLUMNDef
		{
			public String 	_title;
			public int		_fldPos;
			
			public COLUMNDef(Element xmlColumn) throws Exception
			{
				_title = SMTStatic.getXmlAttr(xmlColumn, "title");
				_fldPos = Integer.parseInt(SMTStatic.getXmlAttr(xmlColumn, "field"));
			}
		}
		
		private String 				_sqlId;
		private String				_title;
		private List<COLUMNDef>		_listCOLUMN = new ArrayList<>();
		
		public SQLXMLNodeOUT_ONE_REC(Element xmlRoot) throws Exception
		{
			_sqlId = SMTStatic.getXmlAttr(xmlRoot, "sql_id");
			_title = SMTStatic.getXmlAttr(xmlRoot, "title");
			
			for(Node nodeColumn : xmlRoot.selectNodes("COL"))
			{
				_listCOLUMN.add(new COLUMNDef((Element)nodeColumn));
			}			
		}

		@Override
		public SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception
		{
			DBRecords recs = execArg._mapId2Records.get(_sqlId);
			if(recs == null || recs.getRowCount() == 0)
				return null;
			
			DBRecord rec = recs.getRecord(0);
			SMTJsonWriter jsonWr = execArg._jsonWr;
			jsonWr.beginMap(null);
			{
				jsonWr.addKeyValue("type", "summary");
				jsonWr.addKeyValue("title", _title);
				jsonWr.beginArray("values");
				for(COLUMNDef col : _listCOLUMN)
				{
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("title", col._title);
						jsonWr.addKeyValue("value", rec.getValue(col._fldPos));
					}
					jsonWr.endMap();
				}
				jsonWr.endArray();
			}
			jsonWr.endMap();
			
			return null;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodePARAM extends SQLXMLNode
	{
		private String	_key;
		
		public SQLXMLNodePARAM(Element xmlRoot) throws Exception
		{
			_key = SMTStatic.getXmlAttr(xmlRoot, "key");
		}

		@Override
		public SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception
		{
			Json jsonParam = execArg._toolArguments.safeGetJson(_key);
			if(jsonParam == null)
			{
				AgentArgument agentArgument = execArg._parent.getAgentArgument(_key);
				if(agentArgument != null)
					return new SMTJavaAIError("请输入参数：" + agentArgument._question);
				else
					return new SMTJavaAIError("请输入参数：" + _key);
			}
			
			execArg._sbSQLText.append("?");
			execArg._sqlParams.add(execArg._toolArguments.getJson(_key).asString());
			
			return null;
		}
	}

	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodeSQL extends SQLXMLNode
	{
		private String			_id;
		private String			_title;
		private List<Object>	_listChildren = new ArrayList<>();
		
		public SQLXMLNodeSQL(Element xmlRoot) throws Exception
		{
			_id = SMTStatic.getXmlAttr(xmlRoot, "id", null);
			_title = SMTStatic.getXmlAttr(xmlRoot, "title", "数据库");

			for (Iterator<Node> iterInner = xmlRoot.nodeIterator(); iterInner.hasNext();)
			{			
				Node nodeInner = iterInner.next();
				if(nodeInner.getNodeType() == Node.TEXT_NODE)
				{
					String text = ((DefaultText)nodeInner).getText();
					int lastPos = _listChildren.size() - 1;
					if(lastPos >= 0 && _listChildren.get(lastPos) instanceof String)
					{
						_listChildren.set(lastPos, (String)_listChildren.get(lastPos) + text);
					}
					else
					{
						_listChildren.add(text);
					}
				}	
				else
				{
					_listChildren.add(createSQLXMLNode((Element)nodeInner));
				}
			}
		}

		@Override
		public SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception
		{
			if(SMTStatic.isNullOrEmpty(_id))
				return null;
			
			execArg._tranReq.setAsynProcessText("正在查询" + _title);
			
			execArg._sbSQLText = new StringBuilder();
			execArg._sqlParams = new ArrayList<>();
			
			for(Object oxmlNode : _listChildren)
			{
				if(oxmlNode instanceof String)
				{
					execArg._sbSQLText.append(oxmlNode);
				}
				else if(oxmlNode instanceof SQLXMLNode)
				{
					SMTJavaAIError error = ((SQLXMLNode)oxmlNode).execute(execArg);
					if(error != null)
						return error;
				}
			}
			
			SMTDatabase db = SMTAIServerApp.getApp().getDataSource("DS_43").allocDatabase();
			try
			{
				DBRecords recs = db.querySQL(execArg._sbSQLText.toString(), execArg._sqlParams.toArray(new Object[execArg._sqlParams.size()]));
				execArg._mapId2Records.put(_id, recs);
			}
			finally
			{
				db.close();
			}
			
			return null;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodeOUT_MAP extends SQLXMLNode
	{
		public SQLXMLNodeOUT_MAP(Element xmlRoot) throws Exception
		{

		}

		@Override
		public SMTJavaAIError execute(SQLXMLExecArg execArg) throws Exception
		{
			SMTJsonWriter jsonWr = execArg._jsonWr;
			jsonWr.beginMap(null);
			{
				jsonWr.addKeyValue("type", "map");
				jsonWr.addKeyValue("title", "地图测试");
				jsonWr.addKeyValue("minx", 120.51);
				jsonWr.addKeyValue("maxx", 122.12);
				jsonWr.addKeyValue("miny", 30.40);
				jsonWr.addKeyValue("maxy", 31.53);
				jsonWr.beginArray("values");
				{
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("type", "point");
						jsonWr.addKeyValue("posx", 121);
						jsonWr.addKeyValue("posy", 31);
						jsonWr.addKeyValue("color", "FF0000");
						jsonWr.addKeyValue("title", "五一广场压力:200");
					}
					jsonWr.endMap();
				}
				jsonWr.endArray();
			}
			jsonWr.endMap();
			
			return null;
		}
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	private List<SQLXMLNode>		_listSQLXMLNode = new ArrayList<>();
	
	private static SQLXMLNode createSQLXMLNode(Element xmlRoot) throws Exception
	{
		String name = xmlRoot.getName().toUpperCase();
		if("SQL".equals(name))
			return new SQLXMLNodeSQL(xmlRoot);
		else if("PARAM".equals(name))
			return new SQLXMLNodePARAM(xmlRoot);
		else if("OUT_ALL_REC".equals(name))
			return new SQLXMLNodeOUT_ALL_REC(xmlRoot);
		else if("OUT_ONE_REC".equals(name))
			return new SQLXMLNodeOUT_ONE_REC(xmlRoot);
		else if("OUT_URL".equals(name))
			return new SQLXMLNodeOUT_URL(xmlRoot);
		else if("OUT_MAP".equals(name))
			return new SQLXMLNodeOUT_MAP(xmlRoot);
		else
			throw new Exception("unknow SQLXML : " + name);
		
	}
	
	@Override
	public void initInstance(DBRecord rec) throws Exception
	{
		super.initInstance(rec);
		Document doc = SMTStatic.convStrToXmlDoc("<ROOT>" + rec.getString("clz_arguments") + "</ROOT>");
		
		// 读取根节点
		Element rootElement=doc.getRootElement();

		// 读取所有子节点
		for (Iterator<Element> iterInner = rootElement.elementIterator(); iterInner.hasNext();)
		{
			Node nodeInner = iterInner.next();
			if(nodeInner.getNodeType() == Node.TEXT_NODE)
			{
			}
			else
			{
				_listSQLXMLNode.add(createSQLXMLNode((Element)nodeInner));
			}

		}
	}

	@Override
	public SMTJavaAIError callAgents(String jsonPath, Json jsonArgs, SMTLLMConnect llm, String question, SMTAIServerRequest tranReq) throws Exception 
	{
		SMTJsonWriter jsonWr = tranReq.getResultJsonWr();
		jsonWr.addKeyValue("answer_type", "summary");
		jsonWr.beginArray("summary");
		SMTJavaAIError error = null;
		SQLXMLExecArg execArg = new SQLXMLExecArg(jsonArgs, jsonWr, tranReq, this);
		for(SQLXMLNode xmlNode : _listSQLXMLNode)
		{
			if((error = xmlNode.execute(execArg)) != null)
				break;
		}
		jsonWr.endArray();
		return error;
	}


}