package com.smtaiserver.smtaiserver.javaai.metrics;

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

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

import com.smtaiserver.smtaiserver.core.SMTAIServerApp;
import com.smtaiserver.smtaiserver.core.SMTAIServerRequest;
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.ast.ASTCubeRecs;
import com.smtaiserver.smtaiserver.javaai.ast.ASTCubeRecs.ASTCubeRecsType;
import com.smtaiserver.smtaiserver.javaai.metrics.base.SMTMetricSqlXml;
import com.smtaiserver.smtaiserver.javaai.metrics.base.SMTMetricsDefXmlDim;
import com.smtaiserver.smtaiserver.javaai.metrics.base.SMTMetricSqlXml.SQLXMLQuery;
import com.smtaiserver.smtaiserver.javaai.ast.ASTCubeRecsValue;
import com.smtaiserver.smtaiserver.javaai.ast.ASTDBMap;
import com.smtaiserver.smtaiserver.javaai.ast.ASTResult;
import com.smtaiserver.smtaiserver.javaai.ast.ASTTimeRange;
import com.smtservlet.util.Json;
import com.smtservlet.util.SMTStatic;

public class SMTMetricsDefDevTimeDB extends SMTMetricsDefXmlDim 
{
	
	private static class TimeBucketTable
	{
		public String					_simSqlPerfix;
		public String					_chartType;
		public String					_titleField;
		public String[]					_onameFields;
		public String					_timeField;
		public String					_groupField;
		public char						_groupType;
		public String					_timeStepTitle;
		public int						_timeStepUnit;	
		public int						_timeStepValue;
		public SMTMetricSqlXml			_sqlxmlMeticName;
		public SMTMetricSqlXml			_sqlxmlMeticValue;
		
		public TimeBucketTable(Element xmlTimeBucket) throws Exception
		{
			_simSqlPerfix = "";
			String sTimeStep = SMTStatic.getXmlAttr(xmlTimeBucket, "time_step");
			_timeStepTitle = SMTStatic.getXmlAttr(xmlTimeBucket, "time_title");
			_chartType = SMTStatic.getXmlAttr(xmlTimeBucket, "chart_type", "");
			
			int[] aTimeStep = SMTAIServerApp.convStrToTimeStep(sTimeStep);
			if(aTimeStep == null)
				throw new Exception("can't parse time_step : " + sTimeStep);
			
			_timeStepUnit = aTimeStep[0];
			_timeStepValue = aTimeStep[1];
			
			Element xmlNameSQL = (Element)xmlTimeBucket.selectSingleNode("NAME_SQL");
			String sNameSQLRef = SMTStatic.getXmlAttr(xmlNameSQL, "ref", null);
			if(!SMTStatic.isNullOrEmpty(sNameSQLRef))
				xmlNameSQL = (Element) xmlNameSQL.getDocument().selectSingleNode("ROOT/" + sNameSQLRef);
			_onameFields = SMTStatic.getXmlAttr(xmlNameSQL, "oname_fields").toUpperCase().split(",");
			_titleField = SMTStatic.getXmlAttr(xmlNameSQL, "title_fields").toUpperCase();
			_sqlxmlMeticName = new SMTMetricSqlXml(xmlNameSQL);

			Element xmlValueSQL = (Element)xmlTimeBucket.selectSingleNode("VALUE_SQL");
			String sValueSQLRef = SMTStatic.getXmlAttr(xmlValueSQL, "ref", null);
			if(!SMTStatic.isNullOrEmpty(sValueSQLRef))
				xmlValueSQL = (Element) xmlNameSQL.getDocument().selectSingleNode("ROOT/" + sValueSQLRef);
			_timeField = SMTStatic.getXmlAttr(xmlValueSQL, "time_field").toUpperCase();
			_groupField = SMTStatic.getXmlAttr(xmlValueSQL, "group_field").toUpperCase();
			_groupType = SMTStatic.getXmlAttr(xmlValueSQL, "group_type").toUpperCase().charAt(0);
			_sqlxmlMeticValue = new SMTMetricSqlXml(xmlValueSQL);
		
		}
		
		public int compareTimeStep(int unit, int value)
		{
			int subUnit = _timeStepUnit - unit;
			if(subUnit != 0)
				return subUnit;
			
			int subValue = _timeStepValue - value;
			if(subValue == 0)
				return 0;
			if(subValue < 0)
				return -1;
			
			return 1;
		}
	}
	
	
	///////////////////////////////////////////////////////////////////////////////////////
	
	private List<TimeBucketTable>		_listTimeBucketTable = new ArrayList<>();

	public boolean  isChartValues()
	{
		return true;
	}
	
	@Override
	protected void initInstanceByDoc(DBRecord rec, Document doc) throws Exception
	{
		super.initInstanceByDoc(rec, doc);
		
		// 将时序表
		for(Node nodeTimeBucket : doc.selectNodes("ROOT/TIME_BUCKET"))
		{
			TimeBucketTable timeBucketTable = new TimeBucketTable((Element)nodeTimeBucket);
			for(int i = 0; i < _listTimeBucketTable.size(); i ++)
			{
				TimeBucketTable curTable = _listTimeBucketTable.get(i);
				
				int comp = curTable.compareTimeStep(timeBucketTable._timeStepUnit, timeBucketTable._timeStepValue);
				
				if(comp == 0)
				{
					throw new Exception("time bucket existed at : " + SMTStatic.toInt(i));
				}
				else if(comp < 0)
				{
					_listTimeBucketTable.add(i, timeBucketTable);
					timeBucketTable = null;
					break;
				}
			}
			if(timeBucketTable != null)
				_listTimeBucketTable.add(timeBucketTable);
		}
		
		if(_listTimeBucketTable.size() == 0)
			throw new Exception("time bucket is empty : " + this._id + " : " + this._title);
		
	}
	
	private SMTJavaAIError parseSQLValueFilter(Json jsonCondList, String fieldName, char valueType, StringBuilder r_SQL) throws Exception
	{
		SMTJavaAIError error = null;
		
		if(!jsonCondList.isArray())
			return null;
		
		List<Json> listCond = jsonCondList.asJsonList();
		if(listCond.size() == 0)
			return null;
		
		Json jsonCond = listCond.get(0);
		
		// 解析[第一个表达式] [逻辑符号] [第二个表达式]
		if(jsonCond.isArray())
		{
			r_SQL.append("(");
			if((error = parseSQLValueFilter(jsonCond, fieldName, valueType, r_SQL)) != null)
				return error;
			r_SQL.append(")");
			
			if(listCond.size() > 1)
			{
				r_SQL.append(listCond.get(1).asString());
				
				r_SQL.append("(");
				if((error = parseSQLValueFilter(jsonCond, fieldName, valueType, r_SQL)) != null)
					return error;
				r_SQL.append(")");	
			}
			
		}
		// 解析[操作符号] [值]
		else if(jsonCond.isString())
		{
			r_SQL.append(fieldName);
			r_SQL.append(" " + jsonCond.asString() + " ");
			
			String value = listCond.get(1).asString();
			if(valueType == 'S')
			{
				r_SQL.append("'" + value.replace("'", "''") + "'");
			}
			else if(valueType == 'T')
			{
				r_SQL.append("'" + SMTStatic.toString(SMTStatic.toDate(value)) + "'");
			}
			else
			{
				r_SQL.append(SMTStatic.toString(SMTStatic.toDouble(value)));
			}				
		}
		else
			throw new Exception("jsonCond is not array or value:" + jsonCond.toString());
			
		return null;
		
	}
	
	// 东南厂近五天出厂每两日出厂流量最大值
	// 五一广场按照生产厂家分组且查询XXX厂生产的的昨日压力范围在20-30的明细
	// 查询五一广场前三天内每天的压力平均值
	// 五一广场和光明路昨日压力每小时为单位的压力最大值
	// 按照设备类型分组获取出厂日期为2001-01-01的昨日压力明细
	// 统计压力指标关联设备个数
	
	@Override
	public SMTJavaAIError queryMetrics(String jsonPath, ASTDBMap dbMap, Json jsonAST, Map<String, String> extArg, SMTAIServerRequest tranReq, ASTResult r_result) throws Exception 
	{
		SMTJavaAIError error = this.executeQueryMetrics(jsonPath, dbMap, jsonAST, extArg, tranReq, r_result);
		if(error != null)
		{
			appendSampleQuestionToResponse(jsonAST, tranReq);
			return error;
		}
		
		return null;
	}
	
	public SMTJavaAIError executeQueryMetrics(String jsonPath, ASTDBMap dbMap, Json jsonAST, Map<String, String> extArg, SMTAIServerRequest tranReq, ASTResult r_result) throws Exception 
	{
		tranReq.sendChunkedBlock("begin", "分析要查询指标\"" + this._title + "\"的查询条件...");
		
		ASTCubeRecs astRS = new ASTCubeRecs();
		r_result._listRecordset.add(astRS);
		
		SMTJavaAIError error = null;
		TimeBucketTable timeBucket = null;
		
		// 获取步长时间
		String stepTime = jsonAST.safeGetStr("step_time", null);
		
		// 如果步长时间不为空,则采用最接近步长时间间隔的值
		int[] timeStep = null;
		if(!SMTStatic.isNullOrEmpty(stepTime))
		{
			timeStep = SMTAIServerApp.convStrToTimeStep(stepTime);
			
			if(timeStep != null)
			{
				for(TimeBucketTable curTimeBucket : this._listTimeBucketTable)
				{
					int comp = curTimeBucket.compareTimeStep(timeStep[0], timeStep[1]);
					if(comp <= 0)
					{
						if(comp == 0)
							stepTime = null;
						timeBucket = curTimeBucket;
						break;
					}
				}
			}
			
		}
		
		// 如果步长时间为空,则采用最小时间间隔的值
		if(timeBucket == null)
		{
			timeBucket = this._listTimeBucketTable.get(this._listTimeBucketTable.size() - 1);
		}
		
		String chartType = timeBucket._chartType;
		if(timeStep != null && SMTStatic.isNullOrEmpty(chartType))
		{
			if(timeStep[0] == 1 || timeStep[1] >= 24 * 60)
				chartType = "bar";
		}
		
		String startTime = jsonAST.safeGetStr("start_time", null);
		String endTime = jsonAST.safeGetStr("end_time", null);
		boolean hasTimeValue = !SMTStatic.isNullOrEmpty(startTime) && !SMTStatic.isNullOrEmpty(endTime);
		if(!hasTimeValue)
			return new SMTJavaAIError("未指定时间范围");
		
		// 如果存在查询名称的sqlxml，则首先查询名称
		Map<String, Object>	mapId2SqlArg = new HashMap<>();

		// 解析要分组的维度列表
		Map<String, SMTSQLXMLDimDef> mapIdDimDef = new LinkedHashMap<>();
		if((error = parseDimListFromJson(jsonAST, tranReq, mapIdDimDef)) != null)
			return error;

		StringBuilder sbDIM_NAME_GROUP = new StringBuilder();
		for(String name : mapIdDimDef.keySet())
		{
			sbDIM_NAME_GROUP.append(",");
			sbDIM_NAME_GROUP.append(name);
		}
		
		// 解析过滤条件
		StringBuilder sbDIM_NAME_FILTERS = new StringBuilder();
		if((error = parseSQLFilterFromJson(dbMap, jsonAST, timeBucket._simSqlPerfix, tranReq, sbDIM_NAME_FILTERS, null)) != null)
			return error;
		
		// 设置sql参数
		mapId2SqlArg.put("__METRICS_ID__", this.getId());
		mapId2SqlArg.put("__DIM_NAME_FILTERS__", sbDIM_NAME_FILTERS.toString());
		mapId2SqlArg.put("__DIM_NAME_GROUP__", sbDIM_NAME_GROUP.toString());

		// 生成SQL
		SQLXMLQuery queryONAME = new SQLXMLQuery();
		if((error = timeBucket._sqlxmlMeticName.parseSQL(dbMap, jsonAST, mapId2SqlArg, tranReq, queryONAME)) != null)
			return error;
		
		// 查询结果集
		tranReq.traceLLMDebug(queryONAME.getSqlLog());
		
		// 输出查询条件
		StringBuilder sbQueryProcess = new StringBuilder();
		sbQueryProcess.append("查询从" + startTime + "到" + endTime);
		if(!SMTStatic.isNullOrEmpty(stepTime))
			sbQueryProcess.append("的按照" + stepTime + " 为步长");
		if(astRS._dimNames.size() > 0)
		{
			sbQueryProcess.append("以:");
			for(String dimName : astRS._dimNames)
			{
				sbQueryProcess.append(SMTAIServerApp.getApp().getDimensionDef(dimName).getName() + " ");
			}
			sbQueryProcess.append("为分组");
		}
		sbQueryProcess.append("的数据...");
		tranReq.sendChunkedBlock("begin", sbQueryProcess.toString());
		
		// 查询记录
		DBRecords recsONAME = queryONAME._db.querySQL(queryONAME._sbSQLText.toString(), queryONAME._sqlParams.toArray(new Object[queryONAME._sqlParams.size()]));
		if(recsONAME.getRowCount() == 0)
			return new SMTJavaAIError("当前设备未匹配指标，请检查设备名是否拼写错误");
		
		recsONAME.limitRecord(10);
		
		// 创建cube记录集
		for(String dimName : mapIdDimDef.keySet())
		{
			astRS._dimNames.add(dimName);
		}
		

		// 扫描cube记录集
		StringBuilder sbDimKey = new StringBuilder();
		Map<String, Integer> mapDimKey2RsIdx = new HashMap<>();
		String title;
		
		if(astRS._dimNames.size() == 0)
		{
			title = this._title;
		}
		else
		{
			StringBuilder sbTitle = new StringBuilder();
			for(String dimName : astRS._dimNames)
			{
				if(sbTitle.length() > 0)
					sbTitle.append(",");
				sbTitle.append(this._mapId2SqlDimDef.get(dimName)._dimDef.getName());
			}
			sbTitle.insert(0, "按照");
			sbTitle.append("分类的" + this._title);
			
			title = sbTitle.toString();
		}
		title = timeBucket._timeStepTitle + title;

		// TITLE去重
		String devTITLE = null;
		for(DBRecord recONAME : recsONAME.getRecords())
		{
			if(devTITLE == null)
			{
				devTITLE = recONAME.getString(timeBucket._titleField);
			}
			else if(!devTITLE.equals(recONAME.getString(timeBucket._titleField)))
			{
				devTITLE = "多个设备";
				break;
			}
		}
		
		for(DBRecord recONAME : recsONAME.getRecords())
		{
			
			Integer rsIdx = null;
			
			// 将当前的dim值列表加入
			if(astRS._dimNames.size() == 0)
			{
				astRS._recsType = ASTCubeRecsType.VALUE;
				rsIdx = -1;
				if(!astRS._mapDimId2Recs.containsKey(rsIdx))
				{
					ASTCubeRecsValue recsCube = new ASTCubeRecsValue();
					recsCube._title = devTITLE;
					
					if(hasTimeValue)
					{
						recsCube._timeRange = new ASTTimeRange();
						recsCube._timeRange._title = recsCube._title;
						recsCube._timeRange._startTime = SMTStatic.toDate(startTime);
						recsCube._timeRange._pathStartTime = jsonPath + "start_time";
						recsCube._timeRange._endTime = SMTStatic.toDate(endTime);
						recsCube._timeRange._pathEndTime = jsonPath + "end_time";
						recsCube._timeRange._timeField = timeBucket._timeField;
					}
					
					recsCube._chartType = hasTimeValue ? chartType : null;
					recsCube._groupField = timeBucket._groupField;
					recsCube._groupType = timeBucket._groupType;
					astRS._mapDimId2Recs.put(rsIdx, recsCube);
				}
			}
			else
			{
				astRS._recsType = ASTCubeRecsType.VALUE;
				StringBuilder sbDimNames = new StringBuilder();
				Object[] dimValue = new Object[astRS._dimNames.size()];
				sbDimKey.setLength(0);
				for(int i = 0; i < astRS._dimNames.size(); i ++)
				{
					String dimName = astRS._dimNames.get(i);
					sbDimKey.append(recONAME.getString(dimName) + "\b");
					dimValue[i] = recONAME.getValue(dimName);
					sbDimNames.append(recONAME.getString(dimName) + "-");
				}
				sbDimNames.deleteCharAt(sbDimNames.length() - 1);
				rsIdx = mapDimKey2RsIdx.get(sbDimKey.toString());
				if(rsIdx == null)
				{
					rsIdx = astRS._dimValues.size();
					astRS._dimValues.add(dimValue);
					ASTCubeRecsValue recsCube = new ASTCubeRecsValue();
					if(recsONAME.getRowCount() == 1)
						recsCube._title = devTITLE;
					else
						recsCube._title = sbDimNames.toString();
					
					recsCube._timeRange = new ASTTimeRange();
					recsCube._timeRange._title = recsCube._title;
					recsCube._timeRange._startTime = SMTStatic.toDate(startTime);
					recsCube._timeRange._pathStartTime = jsonPath + "start_time";
					recsCube._timeRange._endTime = SMTStatic.toDate(endTime);
					recsCube._timeRange._pathEndTime = jsonPath + "end_time";
					recsCube._timeRange._timeField = timeBucket._timeField;
					recsCube._chartType = hasTimeValue ? chartType : null;
					recsCube._groupField = timeBucket._groupField;
					recsCube._groupType = timeBucket._groupType;
					astRS._mapDimId2Recs.put(rsIdx, recsCube);
					mapDimKey2RsIdx.put(sbDimKey.toString(), rsIdx);
				}
			}
			
			// 将基本名称加入
			ASTCubeRecsValue recsCube = astRS._mapDimId2Recs.get(rsIdx);
			
			// 如果存在延时数据，则将延时数据结果作为cube记录结果
			if(hasTimeValue)
			{
				// 将查询延时数据的值放入参数映射表
				for(String onameField : timeBucket._onameFields)
				{
					mapId2SqlArg.put(onameField, recONAME.getString(onameField));
				}
				
				// 获取value的过滤条件
				StringBuilder sbSQLFilterValue = new StringBuilder();
				Json jsonFilterValue = jsonAST.safeGetJson("value");
				if(jsonFilterValue != null)
				{
					if((error = parseSQLValueFilter(jsonFilterValue, timeBucket._groupField, timeBucket._groupType, sbSQLFilterValue)) != null)
						return error;
					
					if(sbSQLFilterValue.length() > 0)
						mapId2SqlArg.put("__VALUE_FILTER__", sbSQLFilterValue.toString());
				}
				
				// 生成SQL
				SQLXMLQuery queryValue = new SQLXMLQuery();
				if((error = timeBucket._sqlxmlMeticValue.parseSQL(dbMap, jsonAST, mapId2SqlArg, tranReq, queryValue)) != null)
					return error;
				
				// 查询结果集
				tranReq.traceLLMDebug(queryValue.getSqlLog());
				DBRecords recs = queryValue._db.querySQL(queryValue._sbSQLText.toString(), queryValue._sqlParams.toArray(new Object[] {queryValue._sqlParams.size()}));
				if(recsCube._recsValue == null)
				{
					recsCube._recsValue = recs;
				}
				else
				{
					for(DBRecord rec : recs.getRecords())
					{
						recsCube._recsValue.addRecord(rec.getValues());
					}
				}
				
				// 如果包含步长，则对步长做操作
				if(!SMTStatic.isNullOrEmpty(stepTime))
				{
					String stepOper = jsonAST.safeGetStr("step_op_key", "AVG");
					String stepTitle = jsonAST.safeGetStr("step_op_title", "平均");
					String stepTimePath = jsonAST.safeGetStr("step_time_path", null);
					
					ASTCubeRecsType[] cubeRecsType = new ASTCubeRecsType[1];
					cubeRecsType[0] = astRS._recsType;
					if((error = recsCube.groupValueRecords(stepOper, stepTime, cubeRecsType, null)) != null)
						return error;
					
					recsCube._timeRange._timeStep =   SMTAIServerApp.convTimeStepToUnitStr(timeStep[0], timeStep[1]);
					recsCube._timeRange._pathTimeSteps = SMTStatic.isNullOrEmpty(stepTimePath) ? 
							new String[] { jsonPath + "step_time"} : new String[] { jsonPath + "step_time", stepTimePath};
					
					title += "每" + SMTAIServerApp.convTimeStepToStr(timeStep[0], timeStep[1]) + "的" + stepTitle;
				}
				
					
			}
			// 如果不存在延时数据，则将名称作为记录集
			else
			{
				if(recsCube._recsValue == null)
				{
					recsCube._groupType = 'S';
					recsCube._groupField = "TITLE";
					
					recsCube._recsValue = new DBRecords();
					recsCube._recsValue.initColumn(new String[] {"TITLE"});
				}
				recsCube._recsValue.addRecord(new Object[] {recONAME.getString(timeBucket._titleField)});
			}

		}
		
		astRS._title = title;
		
		for(ASTCubeRecsValue cubeRecs  : astRS._mapDimId2Recs.values())
		{
			cubeRecs.distinctRecTime();
		}
		
		return error;
	}

}