package com.smtaiserver.smtaiserver.attach;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.tree.DefaultText;

import com.smtaiserver.smtaiserver.attach.SMTAIAttachTableDef.SMTAIAttachTableColumn;
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.ast.ASTDBMap;
import com.smtservlet.util.Json;
import com.smtservlet.util.SMTJsonWriter;
import com.smtservlet.util.SMTStatic;



public class SMTAttachTableSqlXml 
{
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLExecArg
	{
		public StringBuilder		_sbSQLText = new StringBuilder();
		public List<Object>			_sqlParams = new ArrayList<>();
		
	}
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static abstract class SQLXMLNode
	{
		public abstract void execute(SQLXMLExecArg execArg) throws Exception;
	}
	
	
	///////////////////////////////////////////////////////////////////////////////////////
	private static class SQLXMLNodeSQL extends SQLXMLNode
	{
		private List<Object>	_listChildren = new ArrayList<>();
		
		public SQLXMLNodeSQL(Element xmlRoot) throws Exception
		{
			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 void execute(SQLXMLExecArg execArg) throws Exception
		{
			for(Object oxmlNode : _listChildren)
			{
				if(oxmlNode instanceof String)
				{
					execArg._sbSQLText.append(oxmlNode);
				}
				else if(oxmlNode instanceof SQLXMLNode)
				{
					((SQLXMLNode)oxmlNode).execute(execArg);
				}
			}
		}
	}


	///////////////////////////////////////////////////////////////////////////////////////
	private String					_dsId;
	private SQLXMLNode				_SQLXMLNode;
	
	private static SQLXMLNode createSQLXMLNode(Element xmlRoot) throws Exception
	{
		String name = xmlRoot.getName().toUpperCase();
		if("SQL".equals(name))
			return new SQLXMLNodeSQL(xmlRoot);
		else
			throw new Exception("unknow SQLXML : " + name);
	}

	public SMTAttachTableSqlXml(Element rootElement) throws Exception
	{
		_dsId = SMTStatic.getXmlAttr(rootElement, "ds_id", null);
		
		_SQLXMLNode = createSQLXMLNode(rootElement);
	}

	public void querySQLToJson(ASTDBMap dbMap, Json jsonFilters, Json jsonOrders, SMTAIAttachTableDef attachTableDef, SMTJsonWriter jsonWr) throws Exception 
	{
		// 生成原始SQL
		SQLXMLExecArg execArg = new SQLXMLExecArg();
		_SQLXMLNode.execute(execArg);
		
		// 生成条件查询
		StringBuilder sbWHERE = new StringBuilder();
		if(jsonFilters != null)
		{
			for(Json jsonFilter : jsonFilters.asJsonList())
			{
				if(sbWHERE.length() > 0)
					sbWHERE.append(" AND ");
				
				String column = jsonFilter.getJson("col").asString();
				String filter = jsonFilter.getJson("filter").asString();
				List<Json> values = jsonFilter.getJson("values").asJsonList();
			
				if("like".equals(filter))
				{
					sbWHERE.append(column + " LIKE '%" + values.get(0).asString().replace("'", "''") + "%'");
				}
				else if("timerange".equals(filter))
				{
					sbWHERE.append(
						  column + " BETWEEN '" + values.get(0).asString().replace("'", "''") + "'::timestamp AND '" 
						+ values.get(1).asString().replace("'", "''") + "'::timestamp");
				}
				else
					throw new Exception("can't find filter op : " + filter);
			}
		}
		if(sbWHERE.length() > 0)
			sbWHERE.insert(0, " WHERE ");
		
		// 生成排序条件
		StringBuilder sbORDER = new StringBuilder();
		if(jsonOrders != null)
		{
			for(Json jsonOrder : jsonOrders.asJsonList())
			{
				if(sbORDER.length() > 0)
					sbORDER.append(",");
				String colName = jsonOrder.getJson("col").asString();
				String order = jsonOrder.safeGetStr("order", "ASC");
				
				sbORDER.append(colName + " " + order);
			}
		}
		if(sbORDER.length() > 0)
			sbORDER.insert(0, " ORDER BY ");
		
		
		SMTDatabase db = dbMap.getDatabase(_dsId);
		DBRecords recs = db.querySQL(
			"SELECT * FROM (" + execArg._sbSQLText.toString() + ") T " + sbWHERE.toString() + " " + sbORDER.toString() + " LIMIT 100", 
			execArg._sqlParams.size() == 0 ? null : execArg._sqlParams.toArray(new Object[] {execArg._sqlParams.size()})
		);
		
		jsonWr.beginArray("columns");
		for(SMTAIAttachTableColumn column : attachTableDef.getColumnList())
		{
			jsonWr.addKeyValue(null, column.getTitle());
		}
		jsonWr.endArray();
		
		jsonWr.beginArray("values");
		for(DBRecord rec : recs.getRecords())
		{
			jsonWr.beginArray(null);
			for(SMTAIAttachTableColumn column : attachTableDef.getColumnList())
			{
				jsonWr.addKeyValue(null, rec.getString(column.getName()));
			}			
			jsonWr.endArray();
		}
		jsonWr.endArray();
	}
}