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

import java.util.ArrayList;
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.regex.Matcher;
import java.util.regex.Pattern;

import org.mozilla.javascript.Context;
import org.mozilla.javascript.ImporterTopLevel;
import org.mozilla.javascript.NativeArray;
import org.mozilla.javascript.NativeObject;
import org.mozilla.javascript.ScriptableObject;

import com.smtaiserver.smtaiserver.core.SMTAIServerApp;
import com.smtaiserver.smtaiserver.core.SMTAIServerRequest;
import com.smtaiserver.smtaiserver.javaai.ast.ASTDBMap;
import com.smtservlet.util.Json;
import com.smtservlet.util.SMTJsonWriter;
import com.smtservlet.util.SMTStatic;
import com.smtservlet.util.SMTStatic.SMTCalcTime;
import com.smtaiserver.smtaiserver.database.SMTDatabase;
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBQueryNotify;
import com.smtaiserver.smtaiserver.database.SMTDatabase.DBRecord;

public class SMTQwenAgentScriptScope extends ImporterTopLevel
{
	private Json 					_jsonArgs;
	private ASTDBMap 				_dbMap;
	@SuppressWarnings("unused")
	private SMTQwenAgentScriptJet 	_scriptJet;
	private SMTAIServerRequest 		_tranReq;
	
	private static Pattern			_patTimeStep = java.util.regex.Pattern.compile("(\\d+)\\s+(days|years|months)");

	
	public SMTQwenAgentScriptScope(Json jsonArgs, ASTDBMap dbMap, SMTQwenAgentScriptJet scriptJet, SMTAIServerRequest tranReq)
	{
		_dbMap = dbMap;
		_jsonArgs = jsonArgs;
		_scriptJet = scriptJet;
		_tranReq = tranReq;
	}
	
	public void __init__(Context cx) throws Exception
    {
   		// Define some global functions particular to the shell. Note
        // that these functions are not part of ECMA.
        initStandardObjects(cx, false);
        
		String[] funcListArr = new String[]{
			"getArgValue",
			"queryRecord",
			"outputTimeChart",
			"outputTable",
			"outputError",
			"joinRecordset"
        };
        defineRunFunctions(this.getClass(), funcListArr);
    }
	
    protected void defineRunFunctions(Class<?> clz, String[] names)
    {
    	defineFunctionProperties(names, clz, ScriptableObject.DONTENUM);
    }	
    
    public String getArgValue(String key)
    {
    	Json jsonValue = _jsonArgs.getJson(key);
    	
    	return jsonValue.asString();
    }
    
//    joinRecordset(记录集1, 记录集2, 对比字段名, 相差的时间范围)
//    功能:
//         将两个记录集按照对比字段进行合并，合并后的结果是：[[记录集1中相同字段记录, 记录集2中相同字段记录], .....]
//    参数:
//         记录集1：由queryRecord函数返回的记录集1
// 	    记录集2：由queryRecord函数返回的记录集2
// 	    相差的时间范围 : 如果要join的两组记录并不是来自同一个时间范围的，则需要计算:记录集2的起始时间-记录集1的起始时间，用 1 months, 1 days等形式表示，如果相同则为null
//    返回：
//         合并后的结果集
// 	    格式:[[记录集1中相同字段记录, 记录集2中相同字段记录], .....]
    public NativeArray joinRecordset(NativeArray arrRecs1, NativeArray arrRecs2, String keyColumn, String diffTime) throws Exception
    {
    	Map<String, NativeObject[]> mapKey2Recs = new LinkedHashMap<>();
    	
    	int timeStep = 0;
    	SMTCalcTime timeUnit = null;
    	if(!SMTStatic.isNullOrEmpty(diffTime))
    	{
    		Matcher m = _patTimeStep.matcher(diffTime);
    		if(!m.find())
    			throw new Exception("time step error : " + diffTime);
    		
    		timeStep = SMTStatic.toInt(m.group(1));
    		String sUnit = m.group(2);
    		if("years".equals(sUnit))
    			timeUnit = SMTCalcTime.ADD_YEAR;
    		else if("days".equals(sUnit))
    			timeUnit = SMTCalcTime.ADD_DATE;   		
    		else if("months".equals(sUnit))
    			timeUnit = SMTCalcTime.ADD_MONTH; 
    		else
    			throw new Exception("time step unsupport : " + diffTime);
    	}
    	
    	// 加入第一个记录集
    	for(int i = 0; i < arrRecs1.size(); i ++)
    	{
    		NativeObject nvRec = (NativeObject) SMTAIServerApp.unwrapObject(arrRecs1.get(i));
    		String key = (String) SMTAIServerApp.getJSValue(nvRec, keyColumn);
    		mapKey2Recs.put(key, new NativeObject[] {nvRec, null});
    	}
    	
    	// 加入第二个记录集
    	for(int i = 0; i < arrRecs2.size(); i ++)
    	{
    		NativeObject nvRec = (NativeObject) SMTAIServerApp.unwrapObject(arrRecs2.get(i));
    		String key = (String) SMTAIServerApp.getJSValue(nvRec, keyColumn);
    		
    		if(timeStep != 0)
    		{
    			key = SMTStatic.toString(SMTStatic.calculateTime(SMTStatic.toDate(key), timeUnit, timeStep));
    		}
    		
    		NativeObject[] arrRecs = mapKey2Recs.get(key);
    		if(arrRecs == null)
    			continue;
    		arrRecs[1] = nvRec;
    	}
    	
    	// 生成最终结果
    	List<NativeArray> list = new ArrayList<>();
    	for(NativeObject[] recList : mapKey2Recs.values())
    	{
    		if(recList[1] == null)
    			continue;
    		list.add(new NativeArray(recList));
    	}
    	
    	
    	return new NativeArray(list.toArray(new NativeArray[list.size()]));
    }
    
    
//    queryRecord(表名, 字段列表, 时间分组, 排序字段列表, 限制记录信息, 查询过滤条件) 
//    功能:
//        查询带时间范围的SQL语句，返回查询结果集。
//    参数:
//	      表名: 要查询的表名
//        字段列表: 要查询的字段名列表: [字段1, 字段2, 字段3... ]，这里只能放字段名，不能放任何运算操作
//        时间分组: 用于聚合操作的时间分组。
//		    格式：{"col":时间字段, "agg":聚合方式(例如:AVG,SUM,MAX,MIN等), "time":分组时间段(例如:1 hours, 1 days，如果问题中未提到时间步长，则不设置。禁止捏造不存在的时间步长)}，如果不存在，则设置为null
//        排序字段列表: 用于排序的字段及排序信息。
//		    格式:[{"col":第一个排序字段名, "order":第一个排序字段顺序(ASC,DESC)], ....] ，如果不存在则设置为null
//        限制记录信息: 用于限制输出条目数的配置。		
//		    格式:{"start":限制记录的起始位置，"limit":限制记录的条目数}, 如果不存在则设置为null
//        查询过滤条件: 用于过滤查询SQL的条件信息 
//		    格式:[{"col":查询条件字段名1, "op":查询条件操作符1, "value":查询值1}, ....]，如果查询操作符是IN,BETWEEN之类有多个值的，那查询值就是["值1","值2"...]，否者查询值就是一个字符串。如果要查询时间，请直接指定查询时间的范围，不允许吧时间拆成年月日分别判断。
//        返回:
//		    将查询后的结果返回
//		    格式：[{字段1:值1, 字段2:值2},{字段1:值3, 字段2:值4}...]
    public NativeArray queryRecord(String tableName, NativeArray arrColumns, NativeObject nvTimeGroup, NativeArray arrOrderColumns, NativeObject nvLimit, NativeArray arrFilters) throws Exception
    {
    	String sqlColTime = null;
		if(nvTimeGroup != null)
		{
			sqlColTime = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "col");
		}
    	
		// 加入字段
		StringBuilder sbColumn = new StringBuilder();
		java.util.Set<String> setExistCols = new HashSet<>();
		for(int i = 0; i < arrColumns.size(); i ++)
		{
			String colName = SMTStatic.toString(SMTAIServerApp.unwrapObject(arrColumns.get(i)));
			setExistCols.add(colName.toUpperCase());
			if(i > 0)
				sbColumn.append(",");
			sbColumn.append(colName);
		}
		
		if(sqlColTime != null && !setExistCols.contains(sqlColTime.toUpperCase()))
		{
			sbColumn.append("," + sqlColTime);
		}
		
		// 加入表名
		StringBuilder sbTable = new StringBuilder();
		
		boolean isDevice = false;
		if("dev_names".equalsIgnoreCase(tableName))
		{
			tableName = 
					  "	(SELECT\r\n"
					+ "	    '压力计' AS DEVICE_TYPE,\r\n"
					+ "	    'XXX' AS FACTORY,\r\n"
					+ "	    N.title AS DEVICE_NAME,\r\n"
					+ "	    Q.otype AS QUOTA_TYPE,\r\n"
					+ "	    Q.oname AS QUOTA_NAME\r\n"
					+ "	FROM\r\n"
					+ "		(SELECT * FROM sd_names.sd_dev_pressure WHERE ver_timee = '5000-01-01'::timestamp) N\r\n"
					+ "	INNER JOIN\r\n"
					+ "		(SELECT * FROM sd_names.sd_quota_names WHERE ver_timee = '5000-01-01'::timestamp) Q\r\n"
					+ "	ON\r\n"
					+ "		N.otype = Q.ptype AND N.oname = Q.pname"
					+ ") T";
			isDevice = true;
		}
		else if("quota_value".equalsIgnoreCase(tableName))
		{
			tableName = "(SELECT otime, sdval, otype as QUOTA_TYPE, oname as QUOTA_NAME FROM sd_values.sd_raws_match) T";
		}
		
		sbTable.append(tableName);
    	
		// 加入过滤条件
		StringBuilder sbWhere = new StringBuilder();
		if(arrFilters != null && arrFilters.size() > 0)
		{
			for(int i = 0; i < arrFilters.size(); i ++)
			{
				if(sbWhere.length() > 0)
					sbWhere.append(" AND ");
				
				NativeObject nvFilter = (NativeObject) SMTAIServerApp.unwrapObject(arrFilters.get(i));
				String colName = (String)SMTAIServerApp.getJSValue(nvFilter, "col");
				String filterOP = (String)SMTAIServerApp.getJSValue(nvFilter, "op");
				if("BETWEEN".equalsIgnoreCase(filterOP))
				{
					sbWhere.append(colName + " BETWEEN ");
					NativeArray arrValues = (NativeArray)SMTAIServerApp.getJSValue(nvFilter, "value");
					sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(arrValues.get(0))).replace("'", "''") + "'");
					sbWhere.append(" AND ");
					sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(arrValues.get(1))).replace("'", "''") + "'");
				}
				else
				{
					if(isDevice)
					{
						if("DEVICE_NAME".equalsIgnoreCase(colName))
						{
							if("IN".equalsIgnoreCase(filterOP))
							{
								NativeArray arrValues = (NativeArray)SMTAIServerApp.getJSValue(nvFilter, "value");
								
								sbWhere.append("(");
								for(int ii = 0; ii < arrValues.size(); ii ++)
								{
									if(ii > 0)
										sbWhere.append(" OR ");
									
									sbWhere.append("DEVICE_NAME LIKE '%");
									sbWhere.append(SMTStatic.toString(SMTAIServerApp.unwrapObject(arrValues.get(ii))).replace("'", "''"));
									sbWhere.append("%'");									
								}
								sbWhere.append(")");
							}
							else
							{
								sbWhere.append("DEVICE_NAME LIKE '%");
								sbWhere.append(SMTStatic.toString(SMTAIServerApp.unwrapObject(SMTAIServerApp.getJSValue(nvFilter, "value"))).replace("'", "''"));
								sbWhere.append("%'");
							}
						}
						else if("QUOTA_TYPE".equalsIgnoreCase(colName))
						{
							sbWhere.append("QUOTA_TYPE = 'SDVAL_PRESS_W'");
						}
						else
						{
							sbWhere.append(colName + " " + filterOP + " ");
							sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(SMTAIServerApp.getJSValue(nvFilter, "value"))).replace("'", "''") + "'");
						}
					}
					else
					{
						sbWhere.append(colName + " " + filterOP + " ");
						sbWhere.append("'" + SMTStatic.toString(SMTAIServerApp.unwrapObject(SMTAIServerApp.getJSValue(nvFilter, "value"))).replace("'", "''") + "'");
					}
				}
				sbWhere.append("\n");
			}
		}
    	
		// 加入排序信息
		StringBuilder sbOrder = new StringBuilder();
		if(arrOrderColumns != null)
		{
			for(int i = 0; i < arrOrderColumns.size(); i ++)
			{
				if(sbOrder.length() > 0)
					sbOrder.append(",");
				
				NativeObject nvOrderColumn = (NativeObject) SMTAIServerApp.unwrapObject(arrOrderColumns.get(i));
				String colName = (String) SMTAIServerApp.getJSValue(nvOrderColumn, "col");
				String orderDir= (String) SMTAIServerApp.getJSValue(nvOrderColumn, "order");
				
				sbOrder.append(colName + " " + orderDir);
			}
		}
		
		// 加入limit
		StringBuilder sbLimit = new StringBuilder();
		if(nvLimit != null)
		{
			SMTAIServerApp.getApp();
			Object oLimit = SMTAIServerApp.getJSValue(nvLimit, "limit", null);
			if(oLimit != null)
			{
				int limit = SMTStatic.toInt(oLimit);
				sbLimit.append("LIMIT " + limit);
				
				Object oStart = SMTAIServerApp.getJSValue(nvLimit, "start", null);
				if(oStart != null)
					sbLimit.append(" OFFSET " + SMTStatic.toInt(oStart));
			}
		}
		
		// 整合SQL
		StringBuilder sbSQL = new StringBuilder();
		sbSQL.append("SELECT \n");
		sbSQL.append(sbColumn.toString() + "\n");
		sbSQL.append("FROM " + sbTable.toString() + "\n");
		if(sbWhere.length() > 0)
			sbSQL.append("WHERE " + sbWhere.toString() + "\n");

		
		String sql = sbSQL.toString();
		
		// {"col":时间字段, "agg":聚合方式(例如:AVG,SUM,MAX,MIN等), "time":分组时间段(例如:1 hours, 1 days，如果问题中未提到时间步长，则不设置。禁止捏造不存在的时间步长)}，如果不存在，则设置为null
		if(nvTimeGroup != null)
		{
			String colTime = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "col");
			String funcAgg = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "agg_func");
			String colAgg = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "agg_col");
			String aliasName = (String) SMTAIServerApp.getJSValue(nvTimeGroup, "alias_col");
			String timeStep =  (String) SMTAIServerApp.getJSValue(nvTimeGroup, "time", null);
			
			if(SMTStatic.isNullOrEmpty(timeStep))
			{
				sql = "SELECT " + funcAgg + "(" + colAgg + ") AS " + aliasName + " FROM (" + sql + ") T";
			}
			else
			{
				sql = 
					  " SELECT time_bucket('" + timeStep + "', " + colTime  + ") AS " + colTime + "," + funcAgg + "(" + colAgg + ") AS " + aliasName 
					+ " FROM (" + sql + ") T"
					+ " GROUP BY time_bucket('" + timeStep + "', " + colTime  + ")"
					;
			}
			sql = "SELECT * FROM (\n" + sql + "\n) T\n";
		}
		
		
		if(sbOrder.length() > 0)
			sql += "ORDER BY " + sbOrder.toString() + "\n";
		
		if(sbLimit.length() > 0)
			sql += " " + sbLimit.toString();

		
		SMTDatabase db = _dbMap.getDatabase("DS_43");
		List<NativeObject> recList = new ArrayList<>();
		System.out.println("=================>\n" + sql + "\n<====================\n");
		db.querySQLNotify(sql, null, new DBQueryNotify() {
			@Override
			public boolean onNextRecord(DBRecord rec) throws Exception
			{
				NativeObject nvRec = new NativeObject();
				recList.add(nvRec);
				for(Entry<String, Integer> entry : rec.getFieldMap().entrySet())
				{
					SMTAIServerApp.putJSNotNullValue(nvRec, 
						entry.getKey(), rec.getString(entry.getValue()));
				}
				return true;
			}
			
		});
		
		return new NativeArray(recList.toArray(new NativeObject[recList.size()]));
    }
    

// outputTimeChart(记录集, 名称字段名, 时间字段名, 值字段名,标题名)
//    功能:
//        以曲线结果记录形式将多组同类型的记录集输出到客户
//	参数:
//     记录集: 要输出的由queryRecord函数返回的相同类型的记录集
//	   名称字段名 : 曲线结果中代表指标名称的字段名
//	   时间字段名 : 用于记录时间的字段名
//     值字段名: 用于记录值的字段名
//     标题名 : 当前输出曲线的标题名
//    返回:
//	    无
    public void outputTimeChart(NativeArray arrRecs, String nameColumn, String timeColumn, String valueColumn, String chartTitle) throws Exception
    {
		String title = (String) chartTitle;

		
		SMTJsonWriter jsonWrResult = _tranReq.getResultJsonWr();
		jsonWrResult.addKeyValue("answer_type", "summary");
		jsonWrResult.beginArray("summary");
		{
			jsonWrResult.beginMap(null);
			{
				// 加入基本信息
				jsonWrResult.addKeyValue("type", "recordset");
				jsonWrResult.addKeyValue("agent_key", "#0/" + SMTStatic.newUUID());
				jsonWrResult.addKeyValue("chart", "muli_line");
				jsonWrResult.addKeyValue("title", title);
				
				// 加入字段名
				jsonWrResult.beginArray("cols");
				{
					jsonWrResult.beginMap(null);
					{
						jsonWrResult.addKeyValue("title", "时间");
						jsonWrResult.addKeyValue("type", "time");
					}
					jsonWrResult.endMap();
					
					jsonWrResult.beginMap(null);
					{
						jsonWrResult.addKeyValue("title", "名称");
						jsonWrResult.addKeyValue("type", "name");
					}
					jsonWrResult.endMap();
					
					jsonWrResult.beginMap(null);
					{
						jsonWrResult.addKeyValue("title", "值");
						jsonWrResult.addKeyValue("type", "value");
					}
					jsonWrResult.endMap();
				}
				jsonWrResult.endArray();
				
				// 加入值
				jsonWrResult.beginArray("values");
				for(int i = 0; i < arrRecs.size(); i ++)
				{
					NativeObject nvRec = (NativeObject)SMTAIServerApp.unwrapObject(arrRecs.get(i));
					jsonWrResult.beginArray(null);
					{
						jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, timeColumn, null));
						jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, nameColumn, null));
						jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, valueColumn, null));
					}
					jsonWrResult.endArray();
				}
				jsonWrResult.endArray();
			}
			jsonWrResult.endMap();
		}
		jsonWrResult.endArray();
		
		_tranReq.sendChunkedResultBlock();
	}

    public void outputError(String message) throws Exception
    {
    	_tranReq.sendChunkedBlock("begin", message);

    }
    
//    outputTable(记录集, 表格标题名, 字段名列表, 字段标题列表, 分组字段名列表)
//    功能:
//       以表格形式输出记录集到客户
//	参数:
//       记录集: 要输出的由queryRecord函数返回的记录集
//       表格标题名 : 表格输出时候定义的名字 
//       字段名列表 : 要输出的字段名列表，[字段1,字段2,...]
//       字段标题列表: 字段名对应的标题列表, 如果标题不存在，则用字段名填充 [标题1, 标题2...]
//       分组字段名列表: 如果记录集中包含分组字段，则输出分组字段列表，否则设置为null
//	返回:
//	   无
    public void outputTable(NativeArray arrRS, String tableTitle, NativeArray arrColNames, NativeArray arrColTitles, NativeArray arrGroupName) throws Exception
    {
		String title = tableTitle;
		NativeArray arrRecs = arrRS;
		
		SMTJsonWriter jsonWrResult = _tranReq.getResultJsonWr();
		jsonWrResult.addKeyValue("answer_type", "summary");
		jsonWrResult.beginArray("summary");
		{
			jsonWrResult.beginMap(null);
			{
				// 加入基本信息
				jsonWrResult.addKeyValue("type", "recordset");
				jsonWrResult.addKeyValue("agent_key", "#0/" + SMTStatic.newUUID());
				jsonWrResult.addKeyValue("chart", "table");
				jsonWrResult.addKeyValue("title", title);
				
				// 加入字段列
				List<String> listColName = new ArrayList<>();
				jsonWrResult.beginArray("cols");
				for(int i = 0; i < arrColNames.size(); i ++)
				{
					String colName  = (String)SMTAIServerApp.unwrapObject(arrColNames.get(i));
					String colTitle = arrColTitles == null ? colName : (String)SMTAIServerApp.unwrapObject(arrColTitles.get(i));
					
					jsonWrResult.beginMap(null);
					{
						jsonWrResult.addKeyValue("title", colTitle);
						jsonWrResult.addKeyValue("name", colName);
						jsonWrResult.addKeyValue("type", "text");
						jsonWrResult.addKeyValue("group", false);
						listColName.add(colName);
					}
					jsonWrResult.endMap();
				}
				jsonWrResult.endArray();
				
				// 加入值列
				jsonWrResult.beginArray("values");
				for(int i = 0; i < arrRecs.size(); i ++)
				{
					NativeObject nvRec = (NativeObject)SMTAIServerApp.unwrapObject(arrRecs.get(i));
					jsonWrResult.beginArray(null);
					{
						for(int j = 0; j < listColName.size(); j ++)
						{
							String colName = listColName.get(j);
							jsonWrResult.addKeyValue(null, SMTAIServerApp.getJSValue(nvRec, colName, null));
						}
					}
					jsonWrResult.endArray();
				}
				jsonWrResult.endArray();
			}
			jsonWrResult.endMap();
		}
		jsonWrResult.endArray();
		
		_tranReq.sendChunkedResultBlock();			
    }
}