package com.smtaiserver.smtaiserver.control;

import java.util.*;
import java.util.Map.Entry;

import cn.hutool.http.HttpUtil;
import com.smtaiserver.smtaiserver.weixinLogin.WeChatUtils;
import org.apache.commons.text.similarity.JaccardSimilarity;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.web.servlet.ModelAndView;

import com.smtaiserver.smtaiserver.core.SMTAIServerApp;
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.smtservlet.core.SMTRequest;
import com.smtservlet.util.Json;
import com.smtservlet.util.SMTHttpClient;
import com.smtservlet.util.SMTJsonWriter;
import com.smtservlet.util.SMTStatic;

public class SMTAIServerControl 
{
	private static Object					_lockSSO = new Object();
	private static Logger 					_logger = LogManager.getLogger(SMTAIServerControl.class);
	
    public ModelAndView swaggerConfigurationUI(SMTRequest tranReq) throws Exception {
//		SMTJsonWriter jsonWr = new SMTJsonWriter(false);
//		jsonWr.addKeyValue("docExpansion", "none");
//		jsonWr.addKeyValue("apisSorter", "alpha");
//		jsonWr.addKeyValue("defaultModelRendering", "schema");
//		jsonWr.beginArray("supportedSubmitMethods");
//		{
//			jsonWr.addKeyValue(null, "get");
//			jsonWr.addKeyValue(null, "post");
//		}
//		jsonWr.endArray();
//		jsonWr.addKeyValue("jsonEditor", false);
//		jsonWr.addKeyValue("showRequestHeaders", true); 
//		return tranReq.returnJson(jsonWr);

        String retText = "{\"deepLinking\":true,\"displayOperationId\":false,\"defaultModelsExpandDepth\":1,\"defaultModelExpandDepth\":1,\"defaultModelRendering\":\"example\",\"displayRequestDuration\":false,\"docExpansion\":\"none\",\"filter\":false,\"operationsSorter\":\"alpha\",\"showExtensions\":false,\"tagsSorter\":\"alpha\",\"validatorUrl\":\"\",\"apisSorter\":\"alpha\",\"jsonEditor\":false,\"showRequestHeaders\":false,\"supportedSubmitMethods\":[\"get\",\"put\",\"post\",\"delete\",\"options\",\"head\",\"patch\",\"trace\"]}";
        return tranReq.returnText(retText);
    }
    
    public ModelAndView swaggerConfigurationSecurity(SMTRequest tranReq) throws Exception 
    {
        return tranReq.returnJson(Json.object());
    }
    
    public ModelAndView swaggerResource(SMTRequest tranReq) throws Exception
    {
    	Json json = SMTAIServerApp.getApp().readSwaggerApiDocJson();
    	List<Json> listGroups = json.getJson("hwngroups").asJsonList();
    	
    	
        SMTJsonWriter jsonWr = new SMTJsonWriter(true);
        
        if(listGroups.size() == 0)
        {
	        jsonWr.beginMap(null);
	        {
	            jsonWr.addKeyValue("name", "default");
	            jsonWr.addKeyValue("url", "/v2/api-docs");
	            jsonWr.addKeyValue("location", "/v2/api-docs");
	            jsonWr.addKeyValue("swaggerVersion", "2.0");
	        }
	        jsonWr.endMap();
        }
        else
        {
	        jsonWr.beginMap(null);
	        {
	            jsonWr.addKeyValue("name", "default");
	            jsonWr.addKeyValue("url", "/v2/api-docs");
	            jsonWr.addKeyValue("location", "/v2/api-docs");
	            jsonWr.addKeyValue("swaggerVersion", "2.0");
	        }
	        jsonWr.endMap();
        	
        	for(Json jsonGroup : listGroups)
        	{
        		String groupName = jsonGroup.asString();
        		
    	        jsonWr.beginMap(null);
    	        {
    	            jsonWr.addKeyValue("name", groupName);
    	            jsonWr.addKeyValue("url", "/v2/api-docs?group=" + groupName);
    	            jsonWr.addKeyValue("location", "/v2/api-docs");
    	            jsonWr.addKeyValue("swaggerVersion", "2.0");
    	        }
    	        jsonWr.endMap();       		
        	}
        }
        return tranReq.returnJson(jsonWr);
    }

    public ModelAndView swaggerDocs(SMTRequest tranReq) throws Exception 
    {
    	String groupName = (String) tranReq.convParamToString("group", false);
        Json json = SMTAIServerApp.getApp().readSwaggerApiDocJson();
        
        if(groupName == null)
        	groupName = "";
        
        SMTJsonWriter jsonWr = new SMTJsonWriter(false);
        jsonWr.addKeyRaw("swagger", json.getJson("swagger"));
        jsonWr.addKeyRaw("info", json.getJson("info"));
        jsonWr.beginMap("paths");
        for(Entry<String, Json> jsonPort : json.getJson("paths").asJsonMap().entrySet())
        {
        	String curGroupName = jsonPort.getValue().safeGetStr("hwngroup", "");
        	if(!curGroupName.equals(groupName))
        		continue;
        	
        	jsonWr.addKeyRaw(jsonPort.getKey(), jsonPort.getValue());
        }
        jsonWr.endMap();
        
        //Json json = Json.read(new File("D:/TTT_DOC/backup/swagger/swagger-test.json"));
        return tranReq.returnJson(jsonWr);
    }
    
    public ModelAndView getSwaggerJson(SMTRequest tranReq) throws Exception 
    {
    	SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    	jsonWr.beginMap("swagger");
    	SMTAIServerApp.getApp().readSwaggerJson(jsonWr);
    	jsonWr.endMap();
    	
    	return tranReq.returnJson(jsonWr);
    }
    
    public ModelAndView sendLoginSMS(SMTAIServerRequest tranReq) throws Exception 
    {
    	String phone = tranReq.convParamToString("phone", true);
    	
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT user_id FROM sys_user_info WHERE user_phone=?", new Object[] {
    			phone	
    		});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(true, null, null);
    		
    		Random  random  =  new  Random();
    		String smsCode = "";
    		for(int i = 0; i < 4; i ++)
    		{
    			smsCode += SMTStatic.toString(Math.abs(random.nextInt()) % 10);
    		}
    		
    		SMTHttpClient web = new SMTHttpClient();
    		
    		String ret = web.getHttpString("http://www.ztsms.cn:8800/sendXSms.do", new String[] {
    			"username", "eventech",
    			"password", "Abcd123456",
    			"mobile", phone,
    			"content", "验证码:" + smsCode + "【选型系统】",
    			"dstime","",
    			"productid","676767",
    			"xh",""
    		}, null);
    		
    		if(!ret.startsWith("1,"))
    			return tranReq.returnJsonState(false, "验证码发送失败", null);
    		
    		_logger.info("=========> sendSMSCode PHONE=" + phone + " sms=" + smsCode);
    		db.executeSQL("UPDATE sys_user_info SET sms_code=? WHERE user_phone=?", new Object[] {
    			smsCode + "\n" + SMTStatic.toString(new Date()),
    			phone
    		});
    		
      	   	return tranReq.returnJsonState(true, null, null);
       	}
    	finally
    	{
    		db.close();
    	}   	
    }
    
    private ModelAndView loginUser(SMTDatabase db, DBRecord recUser, boolean updateLoginFlag, SMTAIServerRequest tranReq) throws Exception
    {
    	String userId = recUser.getString("user_id");
    	
		tranReq.setLoginUserId(userId);
		
		if(updateLoginFlag)
			db.executeSQL("UPDATE sys_user_info SET web_login='Y' WHERE user_id=?", new Object[] {userId});
		
		
		// 获取用户所属的agent_group
		DBRecords recs = db.querySQL("SELECT DISTINCT group_id FROM sys_role_group R WHERE role_id in (SELECT role_id FROM sys_role_user WHERE user_id=?)", 
			new Object[] {userId});
		Set<String> setAgentGroup = new HashSet<>();
		for(DBRecord rec : recs.getRecords())
		{
			setAgentGroup.add(rec.getString("group_id"));
		}
		tranReq.setAgentGroupSet(setAgentGroup);
		
    	SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    	jsonWr.addKeyValue("hswatersession", tranReq.getSessionId());
    	jsonWr.addKeyValue("phone", recUser.getString("user_phone"));
    	jsonWr.addKeyValue("id", recUser.getString("user_id"));
    	jsonWr.addKeyValue("name", recUser.getString("user_name"));
    	jsonWr.addKeyValue("real_name", recUser.getString("real_name"));
    	jsonWr.addKeyValue("part", recUser.getString("user_part"));
    	jsonWr.addKeyValue("sex", recUser.getString("user_sex"));
    	jsonWr.addKeyValue("email", recUser.getString("user_email"));
    	jsonWr.addKeyValue("note", recUser.getString("note"));
		if (recUser.getString("weixin_nickname")!=null)
    	jsonWr.addKeyValue("weixin_nickname",recUser.getString("weixin_nickname") );
		boolean b1 = recUser.getString("weixin_openid") != null;
		jsonWr.addKeyValue("weixin_openid",b1);
    	jsonWr.addKeyValue("web_login", "Y".equals(recUser.getString("web_login")));
	   	return tranReq.returnJson(jsonWr);
    }
    
    public ModelAndView loginSMS(SMTAIServerRequest tranReq) throws Exception 
    {
    	String phone = tranReq.convParamToString("phone", true);
    	String code = tranReq.convParamToString("code", true);
    	String client = tranReq.convParamToString("client", false);
    	if(SMTStatic.isNullOrEmpty(client))
    		client = "手机";
    	
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT * FROM sys_user_info WHERE user_phone=? AND sms_code IS NOT NULL AND user_state_code='Y'", new Object[] {
    			phone	
    		});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "登录失败", null);
    		
    		DBRecord rec = recs.getRecord(0);
    		String[] sp = rec.getString("sms_code").split("\n");
    		if(sp.length == 2)
    		{
    			if(sp[0].equals(code))
    			{
	    			db.executeSQL("UPDATE sys_user_info SET sms_code=NULL WHERE user_phone=?", new Object[] {phone});
	    			
	    			if((new Date().getTime() - SMTStatic.toDate(sp[1]).getTime()) < 5 * 60 * 1000)
	    			{
	    	    		db.executeSQL("INSERT INTO sys_operate_log(op_id, op_type, op_time, op_user, op_note, client_ip,op_from)VALUES(?,?,?,?,?,?,?)", new Object[] {
	    	        			SMTStatic.newUUID(),
	    	        			"短信登录",
	    	        			new Date(),
	    	        			recs.getRecord(0).getString("user_id"),
	    	        			"用户通过" + client + "用手机号登录",
	    	        			tranReq.getClientAddr(),
	    	        			client
	    	        		});
	    				
	    				return loginUser(db, rec, false, tranReq);
	    			}
	    			else
	    			{
	    				return tranReq.returnJsonState(false, "验证码超时", null);
	    			}
    			}
    			
    		}
    		
        	return tranReq.returnJsonState(false, "登录失败", null);
    		
    	}
    	finally
    	{
    		db.close();
    	}

    }
    
    public ModelAndView loginSSO(SMTAIServerRequest tranReq) throws Exception 
    {
     	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String token = tranReq.convParamToString("user_token", true);

    		String ssoUrl = (String) SMTAIServerApp.getApp().getGlobalConfig("sso_url");
    		ssoUrl = ssoUrl.replace("{user_token}", java.net.URLEncoder.encode(token, "UTF-8"));
    		SMTHttpClient web = new SMTHttpClient();
    		Json jsonResp = Json.read(web.getHttpString(ssoUrl, null, null));
    		
    		if(!jsonResp.safeGetBoolean("json_ok", false))
    			throw new Exception("login error");
    		
    		//{"json_ok":true,"value":{"USER_ID":"b6178aa09e4946d6a2d6bbbd7d22f666","USER_NAME":"唐成","USER_LOGIN_NAME":"tc","USER_PART":"ba24b67a00574d8b9391434a207ea972","USER_SEX":null,"USER_PHONE":null,"USER_EMAIL":null}}
    		
    		Json jsonUserInfo = jsonResp.getJson("value");
    		String userId = jsonUserInfo.getJson("USER_ID").asString();
    		String userName = jsonUserInfo.getJson("USER_NAME").asString();
    		
    		// 查询用户是否存在, 不存在则创建用户
    		synchronized(_lockSSO)
    		{
    			DBRecords recs = db.querySQL("SELECT * FROM sys_user_info WHERE user_id=?", new Object[] {userId});
    			if(recs.getRowCount() == 0)
    			{
    				db.beginTran();
    				try
    				{
	    				db.executeSQL("INSERT INTO sys_user_info(user_id, user_name, user_state_code, create_time)VALUES(?, ?, ?, ?)", new Object[] {
	    					userId,
	    					userName,
	    					"Y",
	    					new Date()
	    				});
	    				
	    				db.executeSQL("INSERT INTO sys_role_user(user_id, role_id)VALUES(?, ?)", new Object[] {
	    					userId,
	    					(String) SMTAIServerApp.getApp().getGlobalConfig("sso_role_id")
	    				});
	    				db.commitTran();
    				}
    				catch(Exception ex)
    				{
    					db.rollbackDB(ex);
    				}
    				
    				recs = db.querySQL("SELECT * FROM sys_user_info WHERE user_id=?", new Object[] {userId});
    			}
    			
         		return loginUser(db, recs.getRecord(0), false, tranReq);

    		}
    		
    	}
    	finally
    	{
    		db.close();
    	}
    }
    
    public ModelAndView login(SMTAIServerRequest tranReq) throws Exception 
    {
    	String username = tranReq.convParamToString("user", false);
    	String password = tranReq.convParamToString("pass", false);
    	String client = tranReq.convParamToString("client", false);
    	String weixinCode = tranReq.convParamToString("weixin_code", false);

			if (weixinCode != null){
				username="";
				password="";
			}
    	if(SMTStatic.isNullOrEmpty(client))
    		client = "Web端";
    	
    	boolean updateLoginFlag = false;
    	if("Web端".equals(client))
    		updateLoginFlag = true;
    	
    	password = SMTStatic.convStrToMD5(password);
    	
     	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
			boolean is_weixin=false;
			// 微信扫码登录
			if (weixinCode != null) {
				Map<String, String> accessTokenMap = WeChatUtils.getAccessToken(weixinCode);
				String openid = accessTokenMap.get("openid");
				String accessToken = accessTokenMap.get("access_token");
				String nikename = getUserInfo(accessToken, openid);

				if (openid == null || openid.isEmpty()) {
					return tranReq.returnJsonState(false, "登录失败, 无效的微信二维码", null);
				}

				DBRecords userByOpenid = db.querySQL("SELECT * FROM sys_user_info WHERE weixin_openid = ?", new Object[]{openid});

				if (userByOpenid.getRowCount() == 0) {
					return tranReq.returnJsonState(false, "登录失败, 请绑定账号", null);
				}
				// 获取用户名和密码
				username = userByOpenid.getRecord(0).getString("user_name");
				password = userByOpenid.getRecord(0).getString("user_pass");
				db.executeSQL("UPDATE sys_user_info SET weixin_nickname=? WHERE user_name=?", new Object[] {
						nikename,
						username
				});
				is_weixin=true;
			}


			DBRecords recs = db.querySQL("SELECT * FROM sys_user_info WHERE user_name=? AND user_state_code='Y'", new Object[] {
    			username	
    		});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "登录失败", null);
    		
    		String userPass = recs.getRecord(0).getString("user_pass");
    		//if(!SMTStatic.isNullOrEmpty(userPass))
    		{
    			if(!password.equals(userPass))
    				return tranReq.returnJsonState(false, "登录失败", null);
    		}
    		
    		db.executeSQL("INSERT INTO sys_operate_log(op_id, op_type, op_time, op_user, op_note, client_ip, op_from)VALUES(?,?,?,?,?,?, ?)", new Object[] {
    			SMTStatic.newUUID(),
					is_weixin?"微信登录":"密码登录",
    			new Date(),
    			recs.getRecord(0).getString("user_id"),
    			"用户通过" + client + "用密码登录",
    			tranReq.getClientAddr(),
    			client
    		});
    		tranReq.setSessionAttribute("login_from", client);

    		return loginUser(db, recs.getRecord(0), updateLoginFlag, tranReq);
    	}
    	finally
    	{
    		db.close();
    	}
    	
    }

  public static String getUserInfo(String accessToken, String openId) {
    // 拼接请求地址
    String requestUrl =
        "https://api.weixin.qq.com/sns/userinfo?access_token="
            + accessToken
            + "&openid="
            + openId
            + "&lang=zh_CN";
    String result = HttpUtil.get(requestUrl);
    Json.Reader reader = new Json.Reader();
    Json read = (Json) reader.read(result, null);
    //    JSONObject jsonObject = new JSONObject(result);
    return read.safeGetStr("nickname", null);
  }

    public ModelAndView  getLLMList(SMTAIServerRequest tranReq) throws Exception
    {
    	SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    	jsonWr.beginArray("llm_list");
    	{
    		jsonWr.beginMap(null);
    		{
    			jsonWr.addKeyValue("id", "chat-mm");
    			jsonWr.addKeyValue("title", "chat-mm模型");
    		}
    		jsonWr.endMap();
    		
    		jsonWr.beginMap(null);
    		{
    			jsonWr.addKeyValue("id", "chat-3.5");
    			jsonWr.addKeyValue("title", "chat-3.5模型");
    		}
    		jsonWr.endMap();
    	}
    	jsonWr.endArray();
    	
     	return tranReq.returnJson(jsonWr);
    }
    
    
    public ModelAndView logout(SMTAIServerRequest tranReq) throws Exception 
    {
    	tranReq.clearSession();
    	return tranReq.returnJsonState(true, null, null);
    }
    
    public ModelAndView getSceneGroupTree(SMTAIServerRequest tranReq) throws Exception 
    {
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String userId = tranReq.getLoginUserId();
    		DBRecords recs;
    		
    		if(SMTStatic.isNullOrEmpty(userId))
    		{
        		recs = db.querySQL("SELECT group_id, group_name, p_group_id, group_title, group_type FROM ai_scene_group ORDER BY group_name", null);
    		}
    		else
    		{
    			String sql = 
					  "SELECT\r\n"
					  + "	group_id, group_name, p_group_id, group_title, group_type\r\n"
					  + "FROM\r\n"
					  + "	ai_scene_group S\r\n"
					  + "WHERE\r\n"
					  + "	S.group_id IN (\r\n"
					  + "		SELECT\r\n"
					  + "			G.group_id\r\n"
					  + "		FROM\r\n"
					  + "			sys_role_group G\r\n"
					  + "		INNER JOIN\r\n"
					  + "			sys_role_user R\r\n"
					  + "		ON\r\n"
					  + "				R.user_id=?\r\n"
					  + "			AND R.role_id=G.role_id\r\n"
					  + "	)\r\n"
					  + "ORDER BY \r\n"
					  + "	group_name"
					;
    			recs = db.querySQL(sql, new Object[] {userId});
    		}
    		

    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("groups");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("group_id", rec.getString("group_id"));
    				jsonWr.addKeyValue("group_name", rec.getString("group_name"));
    				jsonWr.addKeyValue("p_group_id", rec.getString("p_group_id"));
    				jsonWr.addKeyValue("group_title", rec.getString("group_title"));
    				jsonWr.addKeyValue("group_type", rec.getString("group_type"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}   	
    }
    
    public ModelAndView getSceneSample(SMTAIServerRequest tranReq) throws Exception 
    {
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String userId = tranReq.getLoginUserId();
    		DBRecords recs;
    		
    		if(SMTStatic.isNullOrEmpty(userId))
    		{
        		recs = db.querySQL("SELECT group_id, sample_id, sample_title, sample_question FROM ai_scene_sample ORDER BY sample_id", null);
    		}
    		else
    		{
    			String sql = 
    				"SELECT\r\n"
    				+ "	group_id, sample_id, sample_title, sample_question\r\n"
    				+ "FROM\r\n"
    				+ "	ai_scene_sample S\r\n"
    				+ "WHERE\r\n"
    				+ "		S.sample_enable='Y'\r\n"
    				+ "	AND S.group_id IN (\r\n"
    				+ "		SELECT\r\n"
    				+ "			G.group_id\r\n"
    				+ "		FROM\r\n"
    				+ "			sys_role_group G\r\n"
    				+ "		INNER JOIN\r\n"
    				+ "			sys_role_user R\r\n"
    				+ "		ON\r\n"
    				+ "				R.user_id=?\r\n"
    				+ "			AND R.role_id=G.role_id\r\n"
    				+ "	)\r\n"
    				+ "ORDER BY \r\n"
    				+ "	sample_id\r\n"
					;
    			recs = db.querySQL(sql, new Object[] {userId});
    		}
 
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("samples");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("group_id", rec.getString("group_id"));
    				jsonWr.addKeyValue("sample_id", rec.getString("sample_id"));
    				jsonWr.addKeyValue("sample_title", rec.getString("sample_title"));
    				jsonWr.addKeyValue("sample_question", rec.getString("sample_question"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}   	
    }
	public ModelAndView addSceneSample(SMTAIServerRequest tranReq) throws Exception {
		String groupId = tranReq.convParamToString("group_id", true);
		String sampleId = tranReq.convParamToString("sample_id", true);
		String sampleQuestion = tranReq.convParamToString("sample_question", true);
		String sampleTitle = tranReq.convParamToString("sample_title", true);
		String sampleEnable = tranReq.convParamToString("sample_enable", true);
		String autoTestSupervisor = tranReq.convParamToString("auto_test_supervisor", false);
		String autoTestJson = tranReq.convParamToString("auto_test_json", false);
		String autoTestMacro = tranReq.convParamToString("auto_test_macro", false);
		String autoTestNote = tranReq.convParamToString("auto_test_note", false);
		String autoTestEnable = tranReq.convParamToString("auto_test_enable", false);
		String sampleMatch = tranReq.convParamToString("sample_match", false);

		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();

		try {
			// 检查样本ID是否已存在
			String selectSql = "SELECT * FROM ai_doc.ai_scene_sample WHERE sample_id = ?";
			Object[] selectParams = {sampleId};
			if (db.querySQL(selectSql, selectParams).getRowCount() > 0) {
				return tranReq.returnJsonState(false, "样本ID已存在", null);
			}

			// 插入新样本
			String insertSql = "INSERT INTO ai_doc.ai_scene_sample (group_id, sample_id, sample_question, sample_title, sample_enable, auto_test_supervisor, auto_test_json, auto_test_macro, auto_test_note, auto_test_enable, sample_match) " +
					"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			Object[] insertParams = {groupId, sampleId, sampleQuestion, sampleTitle, sampleEnable, autoTestSupervisor, autoTestJson, autoTestMacro, autoTestNote, autoTestEnable, sampleMatch};

			db.executeSQL(insertSql, insertParams);

			// 返回 JSON 结果
			SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			return tranReq.returnJson(jsonWr);
		} finally {
			db.close();
		}
	}

	public ModelAndView updateSceneSample(SMTAIServerRequest tranReq) throws Exception {
		String sampleId = tranReq.convParamToString("sample_id", true);
		String sampleQuestion = tranReq.convParamToString("sample_question", false);
		String sampleTitle = tranReq.convParamToString("sample_title", false);
		String sampleEnable = tranReq.convParamToString("sample_enable", false);
		String autoTestSupervisor = tranReq.convParamToString("auto_test_supervisor", false);
		String autoTestJson = tranReq.convParamToString("auto_test_json", false);
		String autoTestMacro = tranReq.convParamToString("auto_test_macro", false);
		String autoTestNote = tranReq.convParamToString("auto_test_note", false);
		String autoTestEnable = tranReq.convParamToString("auto_test_enable", false);
		String sampleMatch = tranReq.convParamToString("sample_match", false);

		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();

		try {
			// 检查样本ID是否存在
			String selectSql = "SELECT * FROM ai_doc.ai_scene_sample WHERE sample_id = ?";
			Object[] selectParams = {sampleId};
			if (db.querySQL(selectSql, selectParams).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "样本ID不存在", null);
			}

			// 更新样本信息
			String updateSql = "UPDATE ai_doc.ai_scene_sample SET sample_question = ?, sample_title = ?, sample_enable = ?, auto_test_supervisor = ?, auto_test_json = ?, auto_test_macro = ?, auto_test_note = ?, auto_test_enable = ?, sample_match = ? WHERE sample_id = ?";
			Object[] updateParams = {sampleQuestion, sampleTitle, sampleEnable, autoTestSupervisor, autoTestJson, autoTestMacro, autoTestNote, autoTestEnable, sampleMatch, sampleId};

			db.executeSQL(updateSql, updateParams);

			// 返回 JSON 结果
			SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			return tranReq.returnJson(jsonWr);
		} finally {
			db.close();
		}
	}


	public ModelAndView createHistoryGroup(SMTAIServerRequest tranReq) throws Exception
    {
    	String groupTitle = tranReq.convParamToString("group_title", true);
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String groupId = SMTStatic.newUUID();
    		db.executeSQL("INSERT INTO chat_history_group(group_id, group_title, user_id, create_time)VALUES(?, ?, ?, ?)", new Object[] {
    			groupId,
    			groupTitle,
    			tranReq.getLoginUserId(),
    			new Date()
    		});
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.addKeyValue("history_group_id", groupId);
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
    }
    
    
    public ModelAndView queryHistoryDetail(SMTAIServerRequest tranReq) throws Exception 
    {
    	DBRecords recs;
    	String historyGroupId = tranReq.convParamToString("history_group_id", true);
    	Integer lastEnd = tranReq.convParamToInteger("last_end", false);
    	Integer lastCount = tranReq.convParamToInteger("last_count", false);
    	String[] idList = tranReq.convParamToStringArray("id_list", false);
    	
    	if(lastEnd != null && lastCount == null)
    		throw new Exception("last_end and last_count must be setted in same time");
    	
    	String idListSQL = "";
    	if(idList != null)
    	{
    		for(String id : idList)
    		{
    			if(idListSQL.length() > 0)
    				idListSQL += ",";
    			idListSQL += "'" + id.replace("'", "''") + "'";
    		}
    		idListSQL = " AND history_id in (" + idListSQL + ")";
    	}
    	
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		recs = db.querySQL("SELECT group_id FROM chat_history_group WHERE user_id=? AND group_id=? ORDER BY create_time", new Object[] {
    			tranReq.getLoginUserId(),
    			historyGroupId
    		});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "对话分组id不存在或不属于当前分组", null);
    		
    		recs = db.querySQL("SELECT history_id,question,create_time,sample_id,answer_state FROM chat_history_detail WHERE group_id=? " + idListSQL + " ORDER BY create_time", new Object[] {historyGroupId});
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		
    		int start = 0;
    		int end = recs.getRecords().size();
    		
    		if(lastEnd != null)
    		{
    			int total = recs.getRecords().size();
    			end = total - lastEnd;
    			if(end > total)
    				end = total;
     			start = end - lastCount;
    			if(start < 0)
    				start = 0;
    			
    		}
    		
    		jsonWr.beginArray("details");
    		for(; start < end; start ++)
    		{
    			DBRecord rec = recs.getRecord(start);
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("history_id", rec.getString("history_id"));
    				jsonWr.addKeyValue("sample_id", rec.getString("sample_id"));
    				jsonWr.addKeyValue("question", rec.getString("question"));
    				jsonWr.addKeyValue("create_time", rec.getString("create_time"));
    				jsonWr.addKeyValue("answer_state", rec.getString("answer_state"));
    			}
    			jsonWr.endMap();
    		}

    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
    }    
    
    public ModelAndView getHistoryGroups(SMTAIServerRequest tranReq) throws Exception 
    {
    	if(tranReq.getLoginUserId() == null)
    		return tranReq.returnJsonState(false, "user not login", null);
    	
    	DBRecords recs;
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
			String sql = 
		          "SELECT\r\n"
				+ "	A.*,\r\n"
				+ "	CASE\r\n"
				+ "		WHEN B.chat_count IS NULL THEN 0\r\n"
				+ "		ELSE B.chat_count\r\n"
				+ "	END AS chat_count,\r\n"
				+ "	case when chat_time is null then '1900-01-01'::timestamp else chat_time end as last_chat_time\r\n"
				+ "FROM\r\n"
				+ "	(\r\n"
				+ "		SELECT\r\n"
				+ "			group_id,\r\n"
				+ "			group_title,\r\n"
				+ "			create_time\r\n"
				+ "		FROM\r\n"
				+ "			chat_history_group\r\n"
				+ "		WHERE\r\n"
				+ "			user_id =?\r\n"
				+ "	) A\r\n"
				+ "LEFT JOIN (\r\n"
				+ "	SELECT\r\n"
				+ "		group_id,\r\n"
				+ "		count(*) AS chat_count,\r\n"
				+ "		max(create_time) as chat_time\r\n"
				+ "	FROM\r\n"
				+ "		chat_history_detail\r\n"
				+ "	GROUP BY\r\n"
				+ "		group_id\r\n"
				+ ") B\r\n"
				+ "ON\r\n"
				+ "	A.group_id = B.group_id\r\n"
				+ "ORDER BY\r\n"
				+ "	last_chat_time"
				;
    		
    		recs = db.querySQL(sql, new Object[] {
    					tranReq.getLoginUserId()	
    		});
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("groups");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("group_id", rec.getString("group_id"));
    				jsonWr.addKeyValue("group_title", rec.getString("group_title"));
    				jsonWr.addKeyValue("chat_count", rec.getString("chat_count"));
    				jsonWr.addKeyValue("create_time", rec.getString("create_time"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
    } 
    
    public ModelAndView getHistoryAnswer(SMTAIServerRequest tranReq) throws Exception 
    {
    	DBRecords recs;
    	String historyId = tranReq.convParamToString("history_id", true);
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		recs = db.querySQL("SELECT ask_json,answer_state FROM chat_history_detail WHERE history_id=?", new Object[] {
    				historyId	
    		});
    		
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未发现回答内容", null);
    		
    		String sjsonAnswer = recs.getRecord(0).getString(0);
    		
    		
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.addKeyValue("answer_state", recs.getRecord(0).getString(1));
    		
    		if(SMTStatic.isNullOrEmpty(sjsonAnswer))
    		{
    			jsonWr.addKeyValue("answer", null);
    		}
    		else
    		{
    			jsonWr.addKeyRaw("answer", sjsonAnswer);
    		}
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
    } 
    
    public ModelAndView setHistoryAnswerState(SMTAIServerRequest tranReq) throws Exception 
    {
    	String historyId = tranReq.convParamToString("history_id", true);
    	String answerState = tranReq.convParamToString("answer_state", false);
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		db.executeSQL("UPDATE chat_history_detail SET answer_state=? WHERE history_id=?", new Object[] {
    			answerState,
    			historyId
    		});
    		
    		return tranReq.returnJsonState(true, null, null);
    	}
    	finally
    	{
    		db.close();
    	}
    } 
    
    public ModelAndView reportHistoryProblem(SMTAIServerRequest tranReq) throws Exception 
    {
    	String historyId = tranReq.convParamToString("history_id", true);
    	String reportNotes = tranReq.convParamToString("report_note", false);
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String sql = 
    			  " INSERT INTO chat_history_report("
    			+ " history_id, question, sample_id, create_time, llm_id, ask_json, run_ms, answer_state, trace_llm, chat_state, report_note, report_user"
    			+ " )SELECT "
    			+ " history_id, question, sample_id, create_time, llm_id, ask_json, run_ms, answer_state, trace_llm, chat_state, ?, ?"
    			+ " FROM chat_history_detail WHERE history_id=?"
    			;
    		db.executeSQL(sql, new Object[] {
    			reportNotes,
    			tranReq.getLoginUserId(),
    			historyId
    		});
    		
    		return tranReq.returnJsonState(true, null, null);
    	}
    	finally
    	{
    		db.close();
    	}
    } 
    
    public ModelAndView deleteHistoryGroup(SMTAIServerRequest tranReq) throws Exception 
    {
    	DBRecords recs;
    	String historyGroupId = tranReq.convParamToString("history_group_id", true);
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		recs = db.querySQL("SELECT group_id FROM chat_history_group WHERE group_id=? and user_id=?", new Object[] {
    				historyGroupId,
    				tranReq.getLoginUserId()
    		});
    		
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未发现要删除的历史", null);
    		
    		db.beginTran();
    		try
    		{
    			db.executeSQL("DELETE  FROM chat_history_group WHERE group_id=?", new Object[] {
    				historyGroupId
    			});
    			// 不删除明细
//    			db.executeSQL("DELETE  FROM chat_history_detail WHERE group_id=?", new Object[] {
//        			historyGroupId
//        		});
    			db.commitTran();
    		}
    		catch(Exception ex)
    		{
    			db.rollbackTran();
    			throw ex;
    		}
    		return tranReq.returnJsonState(true, null, null);
    	}
    	finally
    	{
    		db.close();
    	}   	
    }
    
    public ModelAndView getSysNotifyList(SMTAIServerRequest tranReq) throws Exception 
    {
    	DBRecords recs;
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		recs = db.querySQL("SELECT notify_id,notify_message,notify_type,notify_time FROM sys_notify_info ORDER BY notify_time", null);
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("messages");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("notify_id", rec.getString("notify_id"));
    				jsonWr.addKeyValue("notify_message", rec.getString("notify_message"));
    				jsonWr.addKeyValue("notify_type", rec.getString("notify_type"));
    				jsonWr.addKeyValue("notify_time", rec.getString("notify_time"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
    }
    
    public ModelAndView getUserTemplateList(SMTAIServerRequest tranReq) throws Exception 
    {
    	DBRecords recs;
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		recs = db.querySQL("SELECT template_id, template_title, template_type, template_value, create_time, template_group FROM ai_user_template ORDER BY create_time", null);
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("templates");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("template_id", rec.getString("template_id"));
    				jsonWr.addKeyValue("template_title", rec.getString("template_title"));
    				jsonWr.addKeyValue("template_group", rec.getString("template_group"));
    				jsonWr.addKeyValue("template_type", rec.getString("template_type"));
    				jsonWr.addKeyValue("template_value", rec.getString("template_value"));
    				jsonWr.addKeyValue("create_time", rec.getString("create_time"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
    }
    
    public ModelAndView getSystemVersion(SMTAIServerRequest tranReq) throws Exception 
    {
    	String verison = SMTStatic.readTextStream(this.getClass().getResourceAsStream("/version.txt"));
    	SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    	jsonWr.addKeyValue("version", verison);
    	return tranReq.returnJson(jsonWr);
    }
  
    
    public ModelAndView setHistoryGroupTitle(SMTAIServerRequest tranReq) throws Exception 
    {
    	String historyGroupId = tranReq.convParamToString("history_group_id", true);
    	String title = tranReq.convParamToString("title", true);
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		db.executeSQL("UPDATE chat_history_group SET group_title=? WHERE group_id=?", new Object[] {
    			title,
    			historyGroupId
    		});
		}
		finally
		{
			db.close();
		}    	
    	
    	return tranReq.returnJsonState(true, null, null);
    }
    
    public ModelAndView querySimilarityHistory(SMTAIServerRequest tranReq) throws Exception 
    {
    	String question = tranReq.convParamToString("question", true);
    	String groupType = tranReq.convParamToString("group_type", false);
    	if(SMTStatic.isNullOrEmpty(groupType))
    		groupType = "业务场景";
    	
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String sql = 
    			  "SELECT DISTINCT question, ratio FROM ("
      			+ "SELECT question, ratio\n"
    			+ "FROM (SELECT question, group_id, ratio FROM\n"
      			+ "(SELECT question, group_id, similarity(?, question) as ratio FROM chat_history_detail WHERE chat_state='OK' AND group_type=?) T)A\n" //question, group_type
    			+ "INNER JOIN chat_history_group B\n"
    			+ "ON A.group_id=B.group_id AND B.user_id=?\n" // user
    			+ "UNION ALL\n"
    			+ "SELECT question, ratio FROM\n"
    			+ "(SELECT sample_question as question, similarity(?, sample_question) as ratio, group_id FROM ai_scene_sample WHERE sample_enable='Y')A\n" // question
    			+ "INNER JOIN ai_scene_group B\n"
    			+ "ON A.group_id=B.group_id AND B.group_type=?\n" // group_type
    			+ ") T\n"
    			+ "UNION ALL\n"
    			+ "SELECT question, ratio FROM\n"
    			+ "(SELECT sample_question as question, similarity(?, sample_question) as ratio FROM ai_user_sample WHERE sample_user=? AND group_type=?)A\n" //question, user, group_type
    			+ " WHERE ratio > 0.1\n"
    			+ "ORDER BY ratio DESC\n"
    			+ "LIMIT 5"
    			;
     		DBRecords recs = db.querySQL(sql, new Object[] {
				question,
				groupType,
				tranReq.getLoginUserId(),
				question,
				groupType,
				question,
				tranReq.getLoginUserId(),
				groupType
			});

    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("values");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("question", rec.getString("question"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
		}
		finally
		{
			db.close();
		}  

    }

    public ModelAndView querySimilarityScenePrompt(SMTAIServerRequest tranReq) throws Exception 
    {
    	String question = tranReq.convParamToString("question", true).toLowerCase();
    	String sceneId = tranReq.convParamToString("scene_id", true);
   	
    	JaccardSimilarity jaccardSimilarity = new JaccardSimilarity();
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT prompt_text FROM ai_scene_prompts WHERE scene_id=?", new Object[] {sceneId});
    		
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("values");
    		for(DBRecord rec : recs.getRecords())
    		{
    			String prompt = rec.getString("prompt_text").toLowerCase();
    			if(question.indexOf(prompt) >= 0 || prompt.indexOf(question) >= 0 || jaccardSimilarity.apply(prompt, question) > 0.3)
    			{
    				jsonWr.addKeyValue(null, prompt);
    			}
    		}
    		jsonWr.endArray();
    		
        	return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}

    }

	/**
	 * 绑定微信openid和nickname
	 * @param tranReq
	 * @return
	 * @throws Exception
	 */
  public ModelAndView userBindingWeChatAccount(SMTAIServerRequest tranReq) throws Exception {
    String weixinCode = tranReq.convParamToString("weixin_code", true);
    String userName;
	  String nikcname;
	  SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    try {
      String loginUserId = tranReq.getLoginUserId();
      DBRecords dbRecords =
          db.querySQL("select * from sys_user_info where user_id =?", new Object[] {loginUserId});
      if (dbRecords.getRowCount() > 0) {
        userName = (String) dbRecords.getRecords().get(0).getValue("user_name");
      } else {
        return tranReq.returnJsonState(false, "绑定失败, 用户信息异常", null);
      }
      Map<String, String> accessTokenMap = WeChatUtils.getAccessToken(weixinCode);
      String openid = accessTokenMap.get("openid");
      String accessToken = accessTokenMap.get("access_token");
       nikcname = getUserInfo(accessToken, openid);
      if (openid == null || openid.isEmpty()) {
        return tranReq.returnJsonState(false, "绑定失败, 无效的微信二维码", null);
      }
          db.executeSQL(
              "UPDATE sys_user_info set weixin_openid =?,weixin_nickname=?  where user_name=?",
              new Object[] {openid, nikcname, userName});
    }
	finally
	{
		db.close();
	}
    return tranReq.returnJsonState(true, "绑定成功", nikcname);
  }
}