package com.smtaiserver.smtaiserver.core;

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

import org.locationtech.proj4j.CRSFactory;
import org.locationtech.proj4j.CoordinateReferenceSystem;
import org.locationtech.proj4j.CoordinateTransform;
import org.locationtech.proj4j.CoordinateTransformFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.cache.annotation.Cacheable;

import com.smtaiserver.smtaiserver.attach.SMTAIAttachMetricDef;
import com.smtaiserver.smtaiserver.attach.SMTAIAttachTableDef;
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.gismap.SMTGisMapLayerDef;
import com.smtaiserver.smtaiserver.gismap.SMTMapOtypeDef;
import com.smtaiserver.smtaiserver.gismap.tabledef.SMTMapTableDef;
import com.smtaiserver.smtaiserver.gismap.theme.SMTMapThemeDef;
import com.smtaiserver.smtaiserver.gismap.theme.SMTMapThemeTableDef;
import com.smtaiserver.smtaiserver.javaai.ast.ASTQuestionReplace;
import com.smtaiserver.smtaiserver.javaai.datasource.SMTDataSource;
import com.smtaiserver.smtaiserver.javaai.llm.core.SMTLLMFactory;
import com.smtaiserver.smtaiserver.javaai.metrics.base.SMTDimensionDef;
import com.smtaiserver.smtaiserver.javaai.metrics.base.SMTMetricsDef;
import com.smtaiserver.smtaiserver.javaai.querydetail.SMTAIQueryDetail;
import com.smtaiserver.smtaiserver.javaai.qwen.SMTQwenAgentManager;
import com.smtaiserver.smtaiserver.javaai.qwen.agent.SMTQwenAgent;
import com.smtaiserver.smtaiserver.javaai.qwen.agent.SMTQwenAgentKnowlgFile;
import com.smtservlet.util.Json;
import com.smtservlet.util.SMTStatic;

public class SMTAIServerEncache
{
	@Value("${hswater.tables.global_config}")
	protected String						_tableGlobalConfig;
	
    public static String makeQueryStringAllRegExp(String str)
    {
        return str.replace("\\", "\\\\").replace("*", "\\*")
                .replace("+", "\\+").replace("|", "\\|")
                .replace("{", "\\{").replace("}", "\\}")
                .replace("(", "\\(").replace(")", "\\)")
                .replace("^", "\\^").replace("$", "\\$")
                .replace("[", "\\[").replace("]", "\\]")
                .replace("?", "\\?").replace(",", "\\,")
                .replace(".", "\\.").replace("&", "\\&");
    }
    
    @Cacheable(value="GISTransform")
    public CoordinateTransform[] getGisTransform() throws Exception
    {
    	String gisProjParam = (String)SMTAIServerApp.getApp().getGlobalConfig("gis.proj.src", "+proj=longlat +datum=WGS84 +no_defs");
    	String mapProjParam = (String)SMTAIServerApp.getApp().getGlobalConfig("gis.proj.tag", "+proj=longlat +datum=WGS84 +no_defs");
    	
		CRSFactory crsFactory = new CRSFactory();
		CoordinateTransformFactory ctFactory = new CoordinateTransformFactory();
		CoordinateReferenceSystem gisCRS = createCoordinateReferenceSystem(crsFactory, gisProjParam);
		CoordinateReferenceSystem mapCRS = createCoordinateReferenceSystem(crsFactory, mapProjParam);
		CoordinateTransform transformGisToMap = ctFactory.createTransform(gisCRS, mapCRS);
		CoordinateTransform transformMapToGis = ctFactory.createTransform(mapCRS, gisCRS);

		return new CoordinateTransform[] {transformGisToMap, transformMapToGis};
    }
    
	private CoordinateReferenceSystem createCoordinateReferenceSystem(CRSFactory crsFactory, String sProjParam) throws Exception
	{
		CoordinateReferenceSystem targetCRS = crsFactory.createFromParameters("targetCRS", sProjParam);
		return targetCRS;
	}
	
	@Cacheable(value="AIQuestionReplace")
	public ASTQuestionReplace getQueryAIQuestionReplace() throws Exception
	{
		
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			Map<String, Map<String, String>> mapGroupType2Question2Replace = new HashMap<>();
			StringBuilder sbPatQuestion = new StringBuilder();
			StringBuilder sbPatReplace = new StringBuilder();
			DBRecords recs = db.querySQL("SELECT question_text, replace_text, group_type FROM ai_question_replace WHERE replace_state='Y' ORDER BY replace_order, question_text desc", null);
			for(DBRecord rec : recs.getRecords())
			{
				String question = rec.getString("question_text");
				String replace = rec.getString("replace_text");
				String groupType = rec.getString("group_type");
				
				if(sbPatReplace.length() > 0)
					sbPatReplace.append("|");
				sbPatReplace.append(makeQueryStringAllRegExp(replace));
				
				if(sbPatQuestion.length() > 0)
					sbPatQuestion.append("|");
				sbPatQuestion.append(makeQueryStringAllRegExp(question));
				
				
				Map<String, String> mapQuestion2Replace =  mapGroupType2Question2Replace.get(groupType);
				if(mapQuestion2Replace == null)
				{
					mapQuestion2Replace = new HashMap<>();
					mapGroupType2Question2Replace.put(groupType, mapQuestion2Replace);
				}
				
				mapQuestion2Replace.put(question, replace);
			}
			
			return new ASTQuestionReplace(sbPatQuestion.toString(), sbPatReplace.toString(), mapGroupType2Question2Replace);

		}
		finally
		{
			db.close();
		}

	}
	
	@Cacheable(value="GlobalConfig")
	public Map<String, Object> queryGlobalConfigMap() throws Exception
	{
		Map<String, Object> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT config_key, config_value, config_type FROM " + _tableGlobalConfig + " T WHERE config_enable='1'", null);
			for(DBRecord rec : recs.getRecords())
			{
				String cfgKey = (String)rec.getString("CONFIG_KEY");
				String cfgValue = (String)rec.getString("CONFIG_VALUE");
				String cfgType = (String)rec.getString("CONFIG_TYPE");

				
				if("STRING".equalsIgnoreCase(cfgType))
				{
					mapResult.put(cfgKey, cfgValue);
				}
				else if("INTEGER".equalsIgnoreCase(cfgType))
				{
					mapResult.put(cfgKey, Integer.parseInt(cfgValue));
				}
				else if("DOUBLE".equalsIgnoreCase(cfgType))
				{
					mapResult.put(cfgKey, Double.parseDouble(cfgValue));
				}
				else if("JSON".equalsIgnoreCase(cfgType))
				{
					if(!SMTStatic.isNullOrEmpty(cfgValue))
						mapResult.put(cfgKey, Json.read(cfgValue));
				}
				else if("BOOLEAN".equalsIgnoreCase(cfgType))
				{
					if(!SMTStatic.isNullOrEmpty(cfgValue))
						mapResult.put(cfgKey, cfgValue.equalsIgnoreCase("true"));
				}
				else
				{
					throw new Exception("unknow config type : " + cfgType);
				}
			}
		}
		finally
		{
			db.close();
		}
		
		return mapResult;
	}
	
	@Cacheable(value="MetricsDefMap")
	public Map<String, Map<String, SMTMetricsDef>> queryMetricsMapGroup() throws Exception
	{
		Map<String, Map<String, SMTMetricsDef>> mapMapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			{
				DBRecords recs = db.querySQL(
					  " SELECT a.metrics_id, a.metrics_title, a.metrics_class, a.metrics_config, a.metrics_match, a.metrics_group, b.agent_id, A.metrics_macro, A.metrics_unit"
					+ " FROM ai_metrics_def a INNER JOIN ai_ref_agent_metrics b"
					+ " ON a.metrics_id=b.metrics_id AND a.is_publish='Y'"
					, null);
				for(DBRecord rec : recs.getRecords())
				{
					String agentId = rec.getString("agent_id");
					
					Map<String, SMTMetricsDef> mapResult = mapMapResult.get(agentId);
					if(mapResult == null)
					{
						mapResult = new HashMap<>();
						mapMapResult.put(agentId, mapResult);
					}
					
					SMTMetricsDef metricsDef = (SMTMetricsDef)Class.forName(rec.getString("metrics_class")).newInstance();
					metricsDef.initInstance(rec);
					mapResult.put(metricsDef.getId(), metricsDef);
				}
			}
			
			{
				DBRecords recs = db.querySQL(
						  " SELECT a.metrics_id, a.metrics_title, a.metrics_class, a.metrics_config, a.metrics_match, a.metrics_group, b.agent_id, A.metrics_macro, null as metrics_unit"
						+ " FROM ai_muli_metrics_def a INNER JOIN ai_ref_agent_metrics b"
						+ " ON a.metrics_id=b.metrics_id AND a.is_publish='Y'"
						, null);
				for(DBRecord rec : recs.getRecords())
				{
					String agentId = rec.getString("agent_id");
					
					Map<String, SMTMetricsDef> mapResult = mapMapResult.get(agentId);
					if(mapResult == null)
					{
						mapResult = new HashMap<>();
						mapMapResult.put(agentId, mapResult);
					}
					
					SMTMetricsDef metricsDef = (SMTMetricsDef)Class.forName(rec.getString("metrics_class")).newInstance();
					metricsDef.initInstance(rec);
					mapResult.put(metricsDef.getId(), metricsDef);
				}
			}
			
			for(Map<String, SMTMetricsDef> mapResult : mapMapResult.values())
			{
				for(SMTMetricsDef metricsDef : mapResult.values())
				{
					metricsDef.afterLoading(mapResult);
				}
			}
			
			return mapMapResult;
		}
		finally
		{
			db.close();
		}

	}
	
	@Cacheable(value="DataSourceMap")
	public Map<String, SMTDataSource> queryDataSourceMap() throws Exception
	{
		Map<String, SMTDataSource> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT ds_id, ds_class, ds_config FROM " + SMTAIServerApp.getApp().getTableDataSource(), null);
			for(DBRecord rec : recs.getRecords())
			{
				SMTDataSource dataSource = (SMTDataSource)Class.forName(rec.getString("ds_class")).newInstance();
				dataSource.initInstance(rec);
				mapResult.put(dataSource.getId(), dataSource);
			}
			
			return mapResult;
		}
		finally
		{
			db.close();
		}
	}
	
	@Cacheable(value="DimensionMap")
	public Map<String, SMTDimensionDef>	queryDimensionMap() throws Exception
	{
		Map<String, SMTDimensionDef> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT dim_id, dim_name, dim_title, dim_type, dim_alias, dim_unit, dim_value_list FROM ai_dimension", null);
			for(DBRecord rec : recs.getRecords())
			{
				SMTDimensionDef dimDef = new SMTDimensionDef(rec);
				mapResult.put(dimDef.getId(), dimDef);
			}
			
			return mapResult;
		}
		finally
		{
			db.close();
		}		
	}
	
	@Cacheable(value="QwenAgentManager")
	public SMTQwenAgentManager getQwenAgentManager() throws Exception
	{
		SMTQwenAgentManager manager = new SMTQwenAgentManager();
		boolean debugMode = SMTAIServerApp.getApp().isAppDebugMode();
		
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		
		try
		{
			String sql = 
				  " SELECT agent_id,agent_type,agent_xml,clz_name,clz_arguments, group_type, agent_title, agent_group, agent_order,inner_call"
				+ " FROM ai_agent_amis A"
			    + " LEFT JOIN ai_scene_group G ON A.agent_group = G.group_id"
				+ " WHERE " + (debugMode ? " agent_order > 0 OR is_debug='Y'" : "agent_order > 0 AND is_debug IS NULL")
				+ " ORDER BY abs(agent_order)"
				;

			// 读取所有amis的agent
			db.querySQLNotify(sql, null, new DBQueryNotify() {
					@Override
					public boolean onNextRecord(DBRecord rec) throws Exception 
					{
						SMTQwenAgent agent = (SMTQwenAgent)Class.forName(rec.getString("clz_name")).newInstance();
						agent.initInstance(rec);
						manager.addAgent(agent);
						
						return true;
					}
					
				}
			);
			
			// 读取所有指标的agent
			db.querySQLNotify(
				  " SELECT agent_id,agent_type,agent_xml,clz_name,clz_arguments, group_type, agent_title, agent_group, agent_order,inner_call"
				+ " FROM ai_agent_metrics A"
				+ " LEFT JOIN ai_scene_group G ON A.agent_group = G.group_id"
				+ " WHERE " + (debugMode ? " agent_order > 0 OR is_debug='Y'" : "agent_order > 0 AND is_debug IS NULL")
				+ " ORDER BY abs(agent_order)"
				, null,
				new DBQueryNotify() {
					@Override
					public boolean onNextRecord(DBRecord rec) throws Exception 
					{
						SMTQwenAgent agent = (SMTQwenAgent)Class.forName(rec.getString("clz_name")).newInstance();
						agent.initInstance(rec);
						manager.addAgent(agent);
						
						return true;
					}
					
				}
			);
			
			// 读取所有工作流的agent
			db.querySQLNotify(
				  " SELECT agent_id,agent_type,agent_xml,clz_name,clz_arguments, group_type, agent_title, agent_group, agent_order,inner_call"
				+ " FROM ai_agent_workflow A"
				+ " LEFT JOIN ai_scene_group G ON A.agent_group = G.group_id"
				+ " WHERE " + (debugMode ? " agent_order > 0 OR is_debug='Y'" : "agent_order > 0 AND is_debug IS NULL")
				+ " ORDER BY abs(agent_order)"
				, null,
				new DBQueryNotify() {
					@Override
					public boolean onNextRecord(DBRecord rec) throws Exception 
					{
						SMTQwenAgent agent = (SMTQwenAgent)Class.forName(rec.getString("clz_name")).newInstance();
						agent.initInstance(rec);
						manager.addAgent(agent);
						
						return true;
					}
					
				}
			);
			
			// 读取所有知识库的agent
			db.querySQLNotify(
				  " SELECT A.*, G.group_type  FROM ai_agent_knowlg a" 
				+ " LEFT JOIN ai_scene_group G ON A.agent_group = G.group_id"
				+ " WHERE is_publish='Y'"
				, null, new DBQueryNotify() {
				@Override
				public boolean onNextRecord(DBRecord rec) throws Exception 
				{
					SMTQwenAgentKnowlgFile agent = new SMTQwenAgentKnowlgFile();
					agent.initInstance(rec);
					manager.addAgent(agent);
					
					return true;
				}
			});
		}
		finally
		{
			db.close();
		}
		return manager;
	}
	
	@Cacheable(value="queryDetailMap")
	public Map<String, SMTAIQueryDetail> getQueryDetailMap() throws Exception
	{
		Map<String, SMTAIQueryDetail> mapResult = new HashMap<>();
		
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		
		try
		{
			DBRecords recs = db.querySQL("SELECT * FROM ai_detail_sql_xml", null);
			for(DBRecord rec : recs.getRecords())
			{
				SMTAIQueryDetail queryDetail = (SMTAIQueryDetail) Class.forName(rec.getString("sqlxml_class")).newInstance();
				queryDetail.initInstance(rec);
				mapResult.put(queryDetail.getId(), queryDetail);
			}
		}
		finally
		{
			db.close();
		}
		
		return mapResult;
	}
	
	@Cacheable(value="getLLMFactoryMap")
	public Map<String, SMTLLMFactory> getLLMFactoryMap() throws Exception
	{
		Map<String, SMTLLMFactory> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT F.factory_args, C.connect_id, C.class_name, C.connect_args FROM ai_llm_factory F INNER JOIN ai_llm_connect C ON F.factory_id=C.factory_id", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				SMTLLMFactory llmFactory = (SMTLLMFactory)Class.forName(rec.getString("class_name")).newInstance();
				llmFactory.initInstance(rec);
				mapResult.put(llmFactory.getId(), llmFactory);
				
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;
	}
	
	@Cacheable(value="getGroupTypeMap")
	public Map<String, String> getGroupTypeMap() throws Exception
	{
		Map<String, String> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT group_id, group_type FROM ai_scene_group", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				mapResult.put(rec.getString("group_id"), rec.getString("group_type"));
				
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;
		
	}
	
	@Cacheable(value="getMapLayerDef")
	public Map<String, SMTGisMapLayerDef> getMapLayerDef() throws Exception
	{
		Map<String, SMTGisMapLayerDef> mapResult = new LinkedHashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT * FROM ai_map.map_layer_def WHERE layer_enable='Y' ORDER BY layer_order", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				SMTGisMapLayerDef mapLayerDef = new SMTGisMapLayerDef(db, rec);
				mapResult.put(mapLayerDef.getLayerId(), mapLayerDef);
				
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;
	}
	
	@Cacheable(value="getMapTableDefMap")
	public Map<String, SMTMapTableDef>	getMapTableDefMap() throws Exception
	{
		Map<String, SMTMapTableDef> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT * FROM ai_map.map_table_def", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				SMTMapTableDef tableDef = (SMTMapTableDef)Class.forName(rec.getString("table_class")).newInstance();
				tableDef.initInstance(rec);
				mapResult.put(tableDef.getId(), tableDef);
				
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;		
	}
	
	@Cacheable(value="getMapThemeTableDefMap")
	public Map<String, SMTMapThemeTableDef>	getMapThemeTableDefMap() throws Exception
	{
		Map<String, SMTMapThemeTableDef> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT * FROM ai_map.map_theme_table", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				SMTMapThemeTableDef tableDef = (SMTMapThemeTableDef)Class.forName(rec.getString("theme_table_class")).newInstance();
				tableDef.initInstance(rec);
				mapResult.put(tableDef.getId(), tableDef);
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;		
	}
	
	@Cacheable(value="getMapThemeDefMap")
	public Map<String, SMTMapThemeDef>	getMapThemeDefMap() throws Exception
	{
		Map<String, SMTMapThemeDef> mapResult = new LinkedHashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs = db.querySQL("SELECT * FROM ai_map.map_theme_def ORDER BY theme_order", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				SMTMapThemeDef tableDef = (SMTMapThemeDef)Class.forName(rec.getString("theme_class")).newInstance();
				tableDef.initInstance(rec);
				mapResult.put(tableDef.getId(), tableDef);
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;		
	}
	
	@Cacheable(value="getMapVPropDefMap")
	public Map<String, SMTMapOtypeDef>	getMapVPropDefMap() throws Exception
	{
		Map<String, SMTMapOtypeDef> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs;
			
			recs = db.querySQL("SELECT * FROM ai_map.map_otype_def", null);
			for(DBRecord rec : recs.getRecords())
			{
				SMTMapOtypeDef otypeDef = new SMTMapOtypeDef(rec);
				mapResult.put(otypeDef.getOTYPE(), otypeDef);
			}
			
			
			recs = db.querySQL("SELECT * FROM ai_map.map_vprop_def", null);
			
			for(DBRecord rec : recs.getRecords())
			{
				String OTYPE = rec.getString("otype");
				SMTMapOtypeDef otypeDef = mapResult.get(OTYPE);
				if(otypeDef == null)
					throw new Exception("can't find otype : " + OTYPE);
				otypeDef.addVPropDef(rec);
			}
		}
		finally
		{
			db.close();
		}
		return mapResult;			
	}
	
	@Cacheable(value="getAttachTableDefMap")
	public Map<String, SMTAIAttachTableDef> getAttachTableDefMap() throws Exception
	{
		Map<String, SMTAIAttachTableDef> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs;
			
			recs = db.querySQL("SELECT * FROM ai_attach_table", null);
			for(DBRecord rec : recs.getRecords())
			{
				SMTAIAttachTableDef attachTableDef = new SMTAIAttachTableDef(rec);
				mapResult.put(attachTableDef.getId(), attachTableDef);
			}

		}
		finally
		{
			db.close();
		}
		return mapResult;			
	}
	
	@Cacheable(value="getAttachMetricDefMap")
	public Map<String, SMTAIAttachMetricDef> getAttachMetricDefMap() throws Exception
	{
		Map<String, SMTAIAttachMetricDef> mapResult = new HashMap<>();
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try
		{
			DBRecords recs;
			
			recs = db.querySQL("SELECT * FROM ai_attach_metric", null);
			for(DBRecord rec : recs.getRecords())
			{
				SMTAIAttachMetricDef attachMetricDef = new SMTAIAttachMetricDef(rec);
				mapResult.put(attachMetricDef.getId(), attachMetricDef);
			}

		}
		finally
		{
			db.close();
		}
		return mapResult;			
	}

}