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

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
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.DBQueryNotify;
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 SMTQwenAgentLastRawValue extends SMTQwenAgent
{
	private static class SdvalInfo
	{
		public String					_OTYPE;
		public Date						_OTIME;
		public double					_SDVAL;
	}
	
	private static class DeviceInfo
	{
		public String					_PTYPE;
		public String					_PNAME;
		public String					_PTITLE;
		public Map<String, SdvalInfo>	_mapOTYPE2SDVAL = new HashMap<>();
	}
	
	private static class SampleInfo
	{
		public Set<String>				_accessOTYPE = new HashSet<>();
		public Set<String>				_existOTYPE = new HashSet<>();
		public Map<String, String>		_mapONAME2PNAME = new HashMap<>();
		public Map<String, DeviceInfo>	_PNAME2Device = new LinkedHashMap<>();
		
		public void outputToJson(Map<String,String> mapPTYPE2TITLE, Map<String,String> mapOTYPE2TITLE, SMTJsonWriter jsonWr)
		{
			jsonWr.beginMap(null);
			{
				jsonWr.addKeyValue("type", "device_last_value");
				
				// 生成标题
				StringBuilder sbPTYPETitle = new StringBuilder();
				for(String title : mapPTYPE2TITLE.values())
				{
					if(sbPTYPETitle.length() > 0)
						sbPTYPETitle.append(",");
					sbPTYPETitle.append(title);
				}
				jsonWr.addKeyValue("title", sbPTYPETitle.toString() + "监测设备的最新数据");
				
				// 加入指标名
				jsonWr.beginArray("rows");
				for(Entry<String, String> entry : mapOTYPE2TITLE.entrySet())
				{
					if(!_accessOTYPE.contains(entry.getKey()))
						continue;
					
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("id", entry.getKey());
						jsonWr.addKeyValue("title", entry.getValue());
					}
					jsonWr.endMap();
				}
				jsonWr.endArray();
				
				// 加入数据
				jsonWr.beginArray("values");
				for(DeviceInfo deviceInfo : _PNAME2Device.values())
				{
					jsonWr.beginMap(null);
					{
						jsonWr.addKeyValue("OTYPE", deviceInfo._PTYPE);
						jsonWr.addKeyValue("ONAME", deviceInfo._PNAME);
						jsonWr.addKeyValue("OTITLE", deviceInfo._PTITLE);
						for(SdvalInfo sdvalInfo : deviceInfo._mapOTYPE2SDVAL.values())
						{
							jsonWr.addKeyValue("OTIME", sdvalInfo._OTIME);
							jsonWr.addKeyValue(sdvalInfo._OTYPE, sdvalInfo._SDVAL);
						}
					}
					jsonWr.endMap();
				}
				jsonWr.endArray();
			}
			jsonWr.endMap();
			System.out.println(jsonWr.getFullJson());

		}
		
		public void querySampleValues(SMTDatabase dbBusiness) throws Exception
		{
			StringBuilder sbOTYPEList = new StringBuilder();
			for(String OTYPE : _existOTYPE)
			{
				if(sbOTYPEList.length() > 0)
					sbOTYPEList.append(",");
				sbOTYPEList.append(OTYPE);
			}
			
			String sql =
				  "SELECT otype, oname, last(otime, otime) AS OTIME, last(sdval, otime) AS SDVAL FROM sd_values.sd_raws\n"
				+ "WHERE otype in (SELECT regexp_split_to_table(?, ',')) AND otime >= ?::timestamp + '-12 hours'\n"
				+ "GROUP BY otype, oname";
			
			dbBusiness.querySQLNotify(sql, new Object[] {sbOTYPEList.toString(), new Date()}, new DBQueryNotify() {
				@Override
				public boolean onNextRecord(DBRecord rec) throws Exception
				{
					String OTYPE = rec.getString("otype");
					String ONAME = rec.getString("oname");
					Date   OTIME = rec.getDate("otime");
					Double VALUE = rec.getDouble("SDVAL");
					String ONAMEKey = OTYPE + "\n" + ONAME;
					
					String PNAMEKey = _mapONAME2PNAME.get(ONAMEKey);
					if(PNAMEKey == null)
						return true;
					DeviceInfo deviceInfo = _PNAME2Device.get(PNAMEKey);
					
					_accessOTYPE.add(OTYPE);
					
					SdvalInfo sdvalInfo = new SdvalInfo();
					sdvalInfo._OTYPE = OTYPE;
					sdvalInfo._OTIME = OTIME;
					sdvalInfo._SDVAL = VALUE;
					deviceInfo._mapOTYPE2SDVAL.put(OTYPE, sdvalInfo);
					
					return true;
				}
				
			});
		}
	}
	
	///////////////////////////////////////////////////////////////////////////
	
	@Override
	public SMTJavaAIError callAgents(String jsonPath, Json jsonArgs, SMTLLMConnect llm, String question, SMTAIServerRequest tranReq) throws Exception
	{
		String[] askPTYPEList = jsonArgs.safeGetStr("ptype", "").split(",");
		String[] askVPROPList = jsonArgs.safeGetStr("vprop", "").split(",");
	
		Map<String,String> mapPTYPE2TITLE = new LinkedHashMap<>();
		Map<String,String> mapOTYPE2TITLE = new LinkedHashMap<>();

		SMTDatabase dbSys = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			SMTDatabase dbBusiness = SMTAIServerApp.getApp().getDataSource("DS_43").allocDatabase();
			try
			{
				// 如果指定设备类型，则通过设备类型查找PTYPE
				for(String askPTYPE : askPTYPEList)
				{
					String PTYPE = null;
					if(!SMTStatic.isNullOrEmpty(askPTYPE))
					{
						String sql = 							  
								" SELECT result_ptype, match_value, result_title"
								+ " FROM ("
								+ "		SELECT result_ptype, result_otype, tc_max_similarity(?, key_array) AS match_value, result_order, result_title"
								+ " 	FROM dict_table"
								+ " 	WHERE result_group='PTYPE'"
								+ " )T"
								+ " WHERE match_value > 0.1"
								+ " ORDER BY match_value DESC, result_order"
								+ " DESC LIMIT 1";
						DBRecords recsPTYPE = dbSys.querySQL(sql, new Object[] {askPTYPE});
						if(recsPTYPE.getRowCount() == 0)
						{
							tranReq.traceLLMDebug("未发现设备类型:" + askPTYPE + "，请输入正确的设备类型，例如：流量计，压力计等");
							return new SMTJavaAIError("未发现设备类型:" + askPTYPE + "，请输入正确的设备类型，例如：流量计，压力计等");				
						}
						
						PTYPE = recsPTYPE.getRecord(0).getString("result_ptype");
						tranReq.traceLLMDebug("from ptype=[" + askPTYPE +  "] to PTYPE=" + PTYPE + "");
						mapPTYPE2TITLE.put(PTYPE, recsPTYPE.getRecord(0).getString("result_title"));
					}
				}
				
				// 如果指定了指标，则查询指标
				for(String askVPROP : askVPROPList)
				{
					String OTYPE;
					if(!SMTStatic.isNullOrEmpty(askVPROP))
					{
						// 搜索大类，如果存在，则将大类中包含的小类都加入
						DBRecords recsGTYPE = dbSys.querySQL(
							  " SELECT result_otype, match_value, result_note"
							+ " FROM ("
							+ "		SELECT result_otype, public.tc_max_similarity(?, key_array) AS match_value, result_order, result_note"
							+ " 	FROM dict_table"
							+ " 	WHERE result_group='GTYPE'"
							+ " )T"
							+ " WHERE match_value > 0.8"
							+ " ORDER BY match_value DESC, result_order"
							+ " DESC LIMIT 1", 
							new Object[] {
								askVPROP	
							});
						if(recsGTYPE.getRowCount() > 0)
						{
							DBRecords recsOTYPEList = dbSys.querySQL(
								"SELECT result_otype, result_title FROM dict_table WHERE result_group='OTYPE' AND result_otype in (SELECT regexp_split_to_table(?, ','))"
								, new Object[] {recsGTYPE.getRecord(0).getString("result_note")});
							for(DBRecord recOTYPE : recsOTYPEList.getRecords())
							{
								String gpOTYPE = recOTYPE.getString("result_otype");
								String gpTITLE = recOTYPE.getString("result_title");
								if(mapOTYPE2TITLE.containsKey(gpOTYPE))
									continue;
								mapOTYPE2TITLE.put(gpOTYPE, gpTITLE);
							}
							continue;
						}
						
						// 搜索小类
						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.8"
							+ " ORDER BY match_value DESC, result_order"
							+ " DESC LIMIT 5", 
							new Object[] {
								askVPROP	
							});
						if(recsOTYPE.getRowCount() == 0)
						{
							tranReq.traceLLMDebug("未发现指标:" + askVPROP + "，请输入正确的指标，例如：流量，压力等");
							return new SMTJavaAIError("未发现指标:" + askVPROP + "，请输入正确的指标，例如：流量，压力等");
						}
						
						for(DBRecord recOTYPE : recsOTYPE.getRecords())
						{
							OTYPE = recOTYPE.getString("result_otype");
							tranReq.traceLLMDebug("from vprop=[" + askVPROP +  "] to OTYPE=" + OTYPE + "");
							mapOTYPE2TITLE.put(OTYPE, recOTYPE.getString("result_title"));
						}
						
					}
					// 如果未指定，则包含所有设备
					else
					{
						DBRecords recsOTYPE = dbSys.querySQL("SELECT result_otype, result_title FROM dict_table WHERE result_group='OTYPE'", null);
						for(DBRecord recOTYPE : recsOTYPE.getRecords())
						{
							OTYPE = recOTYPE.getString("result_otype");
							mapOTYPE2TITLE.put(OTYPE, recOTYPE.getString("result_title"));
						}						
					}
				}
				
				if(mapOTYPE2TITLE.size() == 0)
					return new SMTJavaAIError("未发现指定类型的设备");
				
				// 查询所有设备
				SampleInfo sampleInfo =queryDeviceNameList(dbSys, mapPTYPE2TITLE, mapOTYPE2TITLE);
				sampleInfo.querySampleValues(dbBusiness);
				
				SMTJsonWriter jsonWrResult = tranReq.getResultJsonWr();
				jsonWrResult.addKeyValue("answer_type", "summary");
				jsonWrResult.beginArray("summary");
				
				sampleInfo.outputToJson(mapPTYPE2TITLE, mapOTYPE2TITLE, jsonWrResult);
				
				jsonWrResult.endArray();

			}
			finally
			{
				dbBusiness.close();
			}
		}
		finally
		{
			dbSys.close();
		}
		return null;
	}

	private SampleInfo queryDeviceNameList(SMTDatabase dbSys, Map<String,String> mapPTYPE2TITLE, Map<String,String> mapOTYPE2TITLE) throws Exception
	{
		List<Object> listSqlParam = new ArrayList<>();
		StringBuilder sbWHERE = new StringBuilder();
		sbWHERE.append(" WHERE result_group='SDVAL'");
		
		if(mapPTYPE2TITLE.size() > 0)
		{
			StringBuilder sbPTYPESQL = new StringBuilder();
			for(String PTYPE : mapPTYPE2TITLE.keySet())
			{
				if(sbPTYPESQL.length() > 0)
					sbPTYPESQL.append(",");
				sbPTYPESQL.append(PTYPE);
			}
			sbWHERE.append(" AND result_ptype in (SELECT regexp_split_to_table(?, ','))");
			listSqlParam.add(sbPTYPESQL.toString());
		}
		
		if(mapOTYPE2TITLE.size() > 0)
		{
			StringBuilder sbOTYPESQL = new StringBuilder();
			for(String OTYPE : mapOTYPE2TITLE.keySet())
			{
				if(sbOTYPESQL.length() > 0)
					sbOTYPESQL.append(",");
				sbOTYPESQL.append(OTYPE);
			}
			sbWHERE.append(" AND result_otype in (SELECT regexp_split_to_table(?, ','))");
			listSqlParam.add(sbOTYPESQL.toString());			
		}
		
		DBRecords recsONAME = dbSys.querySQL(
			"SELECT result_ptype, result_pname, result_otype, result_oname, result_p_title FROM dict_table " + sbWHERE.toString()
			, listSqlParam.toArray(new Object[listSqlParam.size()]));
		
		SampleInfo sampleInfo = new SampleInfo();
		for(DBRecord recONAME : recsONAME.getRecords())
		{
			String PTYPE = recONAME.getString("result_ptype");
			String PNAME = recONAME.getString("result_pname");
			String OTYPE = recONAME.getString("result_otype");
			String ONAME = recONAME.getString("result_oname");
			String PNAMEKey = PTYPE + "\n" + PNAME;
			String ONAMEKey = OTYPE + "\n" + ONAME;
			
			if(!sampleInfo._PNAME2Device.containsKey(PNAMEKey))
			{
				DeviceInfo deviceInfo = new DeviceInfo();
				deviceInfo._PTYPE = PTYPE;
				deviceInfo._PNAME = PNAME;
				deviceInfo._PTITLE = recONAME.getString("result_p_title");
				sampleInfo._PNAME2Device.put(PNAMEKey, deviceInfo);
			}
			
			sampleInfo._existOTYPE.add(OTYPE);
			sampleInfo._mapONAME2PNAME.put(ONAMEKey, PNAMEKey);
			
		}
		
		return sampleInfo;
	}
}