package com.smtaiserver.smtaiserver.javaai.qwen.sqltree;

import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

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

public class SMTQwenASTSQLTreeParser
{
	private Map<String, Json> 			_mapId2SQLParam = new LinkedHashMap<>();
	private Map<String, String>			_mapId2UserParam = new HashMap<>();
	
	public SMTQwenASTSQLTreeParser(Collection<Json> sqlParams)
	{
		for(Json jsonSQLParam : sqlParams)
		{
			_mapId2SQLParam.put(jsonSQLParam.getJson("id").asString(), jsonSQLParam);
		}
	}
	
	public void setUserParamMap(Json jsonUserParamMap)
	{
		for(Entry<String, Json> entry : jsonUserParamMap.asJsonMap().entrySet())
		{
			_mapId2UserParam.put(entry.getKey(), entry.getValue().asString());
		}
	}
	
	public String convStringToTitle(String str) throws Exception
	{
		for(Json jsonSQLParam : _mapId2SQLParam.values())
		{
			String curId = jsonSQLParam.getJson("id").asString();
			String curValue = getSQLParamValueByJson(curId);
			String type = jsonSQLParam.getJson("type").asString();
			if("list".equals(type))
			{
				for(Json jsonItem : jsonSQLParam.getJson("list").asJsonList())
				{
					String value = jsonItem.getJson("value").asString();
					String title = jsonItem.getJson("title").asString();
					if(curValue.equals(value))
					{
						curValue = title;
						break;
					}
				}
			}
			str = str.replace(curId, curValue);
		}
		
		return str;
	}
	
	public SMTJavaAIError  querySummaryValuesToJson(SMTDatabase db, Json jsonSQLTree, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq) throws Exception
	{
		StringBuilder sbSQL = new StringBuilder();
		SMTJavaAIError error = parseSQLTreeToSQL(jsonSQLTree, tranReq, sbSQL);
		if(error != null)
			return error;
		
		DBRecords recs = db.querySQL(sbSQL.toString(), null);
		if(recs.getRowCount() == 0)
			return null;
		
		DBRecord rec = recs.getRecord(0);
		jsonWr.beginArray("values");
		{
			jsonWr.beginMap(null);
			{
				jsonWr.addKeyValue("title", rec.getString("title"));
				jsonWr.addKeyValue("value", rec.getString("value"));
			}
			jsonWr.endMap();
		}
		jsonWr.endArray();
		
		return null;
	}
	
	public void outputSQLParamToJson(SMTJsonWriter jsonWr)
	{
		jsonWr.beginArray("params");
		{
			for(Json jsonParam : _mapId2SQLParam.values())
			{
				jsonWr.addKeyRaw(null, jsonParam);
			}
		}
		jsonWr.endArray();
	}
	
	public SMTJavaAIError  queryDetailValuesToJson(SMTDatabase db, Json jsonSQLTree, String title, SMTJsonWriter jsonWr, SMTAIServerRequest tranReq) throws Exception
	{
		StringBuilder sbSQL = new StringBuilder();
		SMTJavaAIError error = parseSQLTreeToSQL(jsonSQLTree, tranReq, sbSQL);
		if(error != null)
			return error;
		
		String curTitle = convStringToTitle(title);
		
		jsonWr.beginArray("values");
		db.querySQLNotify(sbSQL.toString(), null, new SMTDatabase.DBQueryNotify() {

			@Override
			public boolean onNextRecord(DBRecord rec) throws Exception
			{
				jsonWr.beginArray(null);
				{
					jsonWr.addKeyValue(null, rec.getString("otime"));
					jsonWr.addKeyValue(null, curTitle);
					jsonWr.addKeyValue(null, rec.getString("value"));
				}
				jsonWr.endArray();
				return true;
			}
			
		});
		jsonWr.endArray();
		
		return null;
	}

	
	private SMTJavaAIError parseSQLTreeToSQL(Json jsonSQLTree, SMTAIServerRequest tranReq, StringBuilder r_sbSQL) throws Exception
	{
		// 生成SQL
		tranReq.traceLLMDebug("SQL语法树：\n" + SMTStatic.formatJson(jsonSQLTree));
		SMTJavaAIError error = callParseSQLTreeToSQL(jsonSQLTree, r_sbSQL);
		if(error != null)
			return error;
		tranReq.traceLLMDebug("SQL语句：\n" + r_sbSQL.toString());

		return null;
	}
	
	private SMTJavaAIError callParseSQLTreeToSQL(Json jsonSQLTree, StringBuilder r_sbSQL) throws Exception
	{
		String call = jsonSQLTree.getJson("call").asString();
		if("query_detail".equals(call))
		{
			return call_query_detail(jsonSQLTree, r_sbSQL);
		}
		else if("comp_values".equals(call))
		{
			return call_comp_values(jsonSQLTree, r_sbSQL);
		}
		
	
		throw new Exception("unknow parse sql : " + call);
	}
	
	private String getSQLParamValueByJson(String id) throws Exception
	{
		return getSQLParamValueByJson(id, null);
	}
	
	private String getSQLParamValueByJson(String id, Set<String> setChecked) throws Exception
	{
		Json jsonSQLParam = _mapId2SQLParam.get(id);
		if(jsonSQLParam == null)
			throw new Exception("can't find sql param id : " + id);
		
		// 如果是引用id,则递归引用
		String refId = jsonSQLParam.safeGetStr("ref_id", null);
		if(!SMTStatic.isNullOrEmpty(refId))
		{
			if(setChecked == null)
				setChecked = new HashSet<>();
			else if(setChecked.contains(refId))
				throw new Exception("ref id for sql param is existed");
			
			setChecked.add(refId);
			
			return getSQLParamValueByJson(refId, setChecked);
		}

		String value = this._mapId2UserParam.get(id);
		if(value != null)
			return value;
		
		return jsonSQLParam.safeGetStr("value", "");
	}
	
	private String convSQLParam2SQLText(Json jsonSQLNode) throws Exception
	{
		String id = jsonSQLNode.getJson("id").asString();
		String type = jsonSQLNode.getJson("type").asString();
		String value = getSQLParamValueByJson(id);
		
		if("start_time".equals(type))
		{
			return "'" + value.replace("'", "''") + "'::timestamp";
		}
		else if("end_time".equals(type))
		{
			return "'" + value.replace("'", "''") + "'::timestamp";
		}
		else if("timestamp".equals(type))
		{
			return "'" + value.replace("'", "''") + "'::timestamp";
		}		else if("split_otype".equals(type))
		{
			String[] sp = value.split("\\,", 2);
			return "'" + sp[0].replace("'", "''") + "'";
		}
		else if("split_oname".equals(type))
		{
			String[] sp = value.split("\\,");
			return "'" + sp[1].replace("'", "''") + "'";
		}
		else if("step_time".equals(type))
		{
			return "'" + value.replace("'", "''") + "'";
		}
		else
			 throw new Exception("unkonw sql param type : " + type);
	}

	private SMTJavaAIError call_query_detail(Json jsonSQLTree, StringBuilder r_sbSQL) throws Exception
	{
		Json jsonOffsetTime = jsonSQLTree.safeGetJson("offset_timestamp");
		
		String OTIME_SQL;
		String END_TIME_SQL;
		if(jsonOffsetTime == null)
		{
			OTIME_SQL = jsonSQLTree.getJson("column_otime").asString();
			END_TIME_SQL = convSQLParam2SQLText(jsonSQLTree.getJson("end_time"));
		}
		else
		{
			OTIME_SQL = 
				  jsonSQLTree.getJson("column_otime").asString() + "::timetsamp"
				+ " + (" 
			    +        convSQLParam2SQLText(jsonOffsetTime.getJson("prev_start_time"))
			    +        "-"
			    +        convSQLParam2SQLText(jsonOffsetTime.getJson("cur_start_time"))
				+    ")"
				;
			END_TIME_SQL = 
					convSQLParam2SQLText(jsonOffsetTime.getJson("cur_start_time"))
				+ " + ("
				+        convSQLParam2SQLText(jsonOffsetTime.getJson("prev_end_time"))
				+        "-"
				+        convSQLParam2SQLText(jsonOffsetTime.getJson("prev_start_time"))
				+    ")"
				;

		}
		
		
		// 生成基本明细SQL
		String sqlDetail = 
			  "SELECT\n" 
			+ "    " + OTIME_SQL + " as OTIME,\n" 
		    + "    " + jsonSQLTree.getJson("column_value").asString() + " as VALUE\n"
		    + "FROM \n"
		    + "    " + jsonSQLTree.getJson("table_name").asString() + "\n"
		    + "WHERE\n"
		    + "        " + jsonSQLTree.getJson("column_otime").asString() 
		    		+ " BETWEEN " + convSQLParam2SQLText(jsonSQLTree.getJson("start_time"))
		    		+ " AND " + END_TIME_SQL + "\n"
		    + "    AND OTYPE=" + convSQLParam2SQLText(jsonSQLTree.getJson("otype")) + "\n"
		    + "    AND ONAME=" + convSQLParam2SQLText(jsonSQLTree.getJson("oname")) + "\n"
		    ;
		
		// 获取分组信息
		Json jsonGroup = jsonSQLTree.safeGetJson("group");
		if(jsonGroup == null)
		{
			r_sbSQL.append(sqlDetail);
			return null;
		}
		
		String groupType = jsonGroup.getJson("type").asString();
		if("summary".equals(groupType))
		{
			String title = jsonGroup.getJson("title").asString();
			String operate = jsonGroup.getJson("operate").asString();
			String sqlGroup = "SELECT " + operate + "(value) as value, '" + title.replace("'", "''") + "' as title FROM (\n" + sqlDetail + "\n) T";
			r_sbSQL.append(sqlGroup);
			return null;
		}
		else if("time_bucket".equals(groupType))
		{
			String operate = jsonGroup.getJson("operate").asString();
			String sqlGroup = 
				  "SELECT otime, " + operate + "(value) as value\n"
				+ "FROM (SELECT time_bucket(" + convSQLParam2SQLText(jsonGroup.getJson("step_time")) + ", otime) as otime, value FROM (\n" + sqlDetail + "\n) T)T\n"
				+ "GROUP BY OTIME\n"
				+ "ORDER BY OTIME\n"
				;
			r_sbSQL.append(sqlGroup);
			return null;			
		}
		else
		{
			throw new Exception("unknow group type : " + groupType);
		}

	}
	
	private SMTJavaAIError call_comp_values(Json jsonSQLTree, StringBuilder r_sbSQL) throws Exception
	{
		StringBuilder sbRS1 = new StringBuilder();
		callParseSQLTreeToSQL(jsonSQLTree.getJson("recordset1"), sbRS1);
		StringBuilder sbRS2 = new StringBuilder();
		callParseSQLTreeToSQL(jsonSQLTree.getJson("recordset2"), sbRS2);
		
		String operate = jsonSQLTree.getJson("operate").asString();
		r_sbSQL.append(
			   "SELECT A.OTIME AS OTIME, " + operate + "\n"
			 + "FROM (SELECT OTIME, VALUE AS VALUE1 FROM (" + sbRS1.toString() + "\n) T)A\n"
			 + "INNER JOIN (SELECT OTIME, VALUE AS VALUE2 FROM (" + sbRS1.toString() + "\n) T)B\n"
			 + "ON A.OTIME=B.OTIME\n"
		);
		
		return null;
	}
}