package com.smtaiserver.smtaiserver.control;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

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.SMTApp;
import com.smtservlet.util.SMTJsonWriter;
import com.smtservlet.util.SMTStatic;

public class SMTSystemManagerControl
{
	public ModelAndView getOperateLog(SMTAIServerRequest tranReq) throws Exception
	{
		Date startTime = tranReq.convParamToDate("start_time", false);
		Date endTime = tranReq.convParamToDate("end_time", false);
		String opType = tranReq.convParamToString("type", false);
		String opUser = tranReq.convParamToString("user", false);
		String opFrom = tranReq.convParamToString("from", false);
		
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		
    		String sql = "SELECT A.*, B.user_name FROM sys_operate_log A LEFT JOIN sys_user_info B ON A.op_user=B.user_id WHERE 1=1 ";
    		List<Object> sqlParams = new ArrayList<>();
    		if(startTime != null)
    		{
    			sql += " AND op_time >=?";
    			sqlParams.add(startTime);
    		}

    		if(endTime != null)
    		{
    			sql += " AND op_time <=?";
    			sqlParams.add(endTime);
    		}
    		
    		if(opType != null)
    		{
    			sql += " AND op_type in (SELECT regexp_split_to_table(?, ','))";
    			sqlParams.add(opType);
    		}
    		
    		if(opUser != null)
    		{
    			sql += " AND B.user_name LIKE ?";
    			sqlParams.add("%" + opUser + "%");
    		}
    		
    		if(opFrom != null)
    		{
    			sql += " AND op_from in (SELECT regexp_split_to_table(?, ','))";
    			sqlParams.add(opType);
    		}
    		
    		sql += " ORDER BY op_time DESC LIMIT 1000";
    		
    		DBRecords recs = db.querySQL(sql, sqlParams.toArray(new Object[sqlParams.size()]));
    		
    		jsonWr.beginArray("values");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("id", rec.getString("op_id"));
    				jsonWr.addKeyValue("type", rec.getString("op_type"));
    				jsonWr.addKeyValue("time", rec.getString("op_time"));
    				jsonWr.addKeyValue("user", rec.getString("user_name"));
    				jsonWr.addKeyValue("note", rec.getString("op_note"));
    				jsonWr.addKeyValue("from", rec.getString("op_from"));
    				jsonWr.addKeyValue("client_ip", rec.getString("client_ip"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}	
	}
	
	public ModelAndView updateUserRoles(SMTAIServerRequest tranReq) throws Exception
	{
		String userId = tranReq.convParamToString("user_id", true);
		String[] userRoles = tranReq.convParamToStringArray("user_roles", false);
		
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT user_name FROM sys_user_info WHERE user_id=?", new Object[] {userId});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未发现用户", null);
    		String userName = recs.getRecord(0).getString(0);
    		
    		db.executeSQL("BEGIN", null);
    		try
    		{
    			db.executeSQL("DELETE FROM sys_role_user WHERE user_id=?", new Object[] {userId});
    			if(userRoles != null)
    			{
    				for(String userRole : userRoles)
    				{
    					db.executeSQL("INSERT INTO sys_role_user(user_id, role_id)VALUES(?,?)", new Object[] {userId, userRole});
    				}
    			}
    			
    			tranReq.appendOperateLog(db, "更新角色", "更新用户[" + userName + "]的角色");
    			
    			db.executeSQL("COMMIT", null);
    			return tranReq.returnJsonState(true, null, null);

    		}
    		catch(Exception ex)
    		{
    			db.rollbackDB(ex);
    			return null;
    		}
				
			
    	}
    	finally
    	{
    		db.close();
    	}
	}
	
	public ModelAndView getRoleInfoList(SMTAIServerRequest tranReq) throws Exception
	{
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			DBRecords recsRoles = db.querySQL("SELECT * FROM sys_role_info", null);
			
			jsonWr.beginArray("values");
			for(DBRecord recRole : recsRoles.getRecords())
			{
				jsonWr.beginMap(null);
				{
			    	jsonWr.addKeyValue("id", recRole.getString("role_id"));
			    	jsonWr.addKeyValue("title", recRole.getString("role_title"));
				}
				jsonWr.endMap();
			}
			jsonWr.endArray();
				
			
			return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
	}

	public ModelAndView addRoleInfo(SMTAIServerRequest tranReq) throws Exception {
		String roleId = tranReq.convParamToString("role_id", true);
		String roleTitle = tranReq.convParamToString("role_title", true);

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

		try {
			// 检查角色ID是否已存在
			String selectSql = "SELECT * FROM ai_sys.sys_role_info WHERE role_id = ?";
			Object[] selectParams = {roleId};
			if (db.querySQL(selectSql, selectParams).getRowCount() > 0) {
				return tranReq.returnJsonState(false, "角色ID已存在", null);
			}

			// 插入新角色
			String insertSql = "INSERT INTO ai_sys.sys_role_info (role_id, role_title) VALUES (?, ?)";
			Object[] insertParams = {roleId, roleTitle};

			db.executeSQL(insertSql, insertParams);

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

	public ModelAndView updateRoleInfo(SMTAIServerRequest tranReq) throws Exception {
		String roleId = tranReq.convParamToString("role_id", true);
		String roleTitle = tranReq.convParamToString("role_title", false);

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

		try {
			// 检查角色ID是否存在
			String selectSql = "SELECT * FROM ai_sys.sys_role_info WHERE role_id = ?";
			Object[] selectParams = {roleId};
			if (db.querySQL(selectSql, selectParams).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "角色ID不存在", null);
			}

			// 更新角色信息
			String updateSql = "UPDATE ai_sys.sys_role_info SET role_title = ? WHERE role_id = ?";
			Object[] updateParams = {roleTitle, roleId};

			db.executeSQL(updateSql, updateParams);

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


	public ModelAndView addUserInfo(SMTAIServerRequest tranReq) throws Exception
	{
		String userName = tranReq.convParamToString("user_name", true);
		String password = SMTStatic.convStrToMD5(tranReq.convParamToString("password", true));
		String userPhone = tranReq.convParamToString("phone", false);
		String realName = tranReq.convParamToString("real_name", false);
		String userPart = tranReq.convParamToString("part", false);
		String userSex = tranReq.convParamToString("sex", false);
		String userEmail = tranReq.convParamToString("email", false);
		String userNote = tranReq.convParamToString("note", false);
		String[] userRoles = tranReq.convParamToStringArray("user_roles", false);
		
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String userId = SMTStatic.newUUID();
    		
    		DBRecords recs = db.querySQL("SELECT 1 FROM sys_user_info WHERE user_name=? AND user_state_code='Y'", new Object[] {userName});
    		if(recs.getRowCount() != 0)
    			return tranReq.returnJsonState(false, "用户已经存在", null);
    		
    		db.executeSQL("BEGIN", null);
    		try
    		{
	    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
				db.executeSQL("INSERT INTO sys_user_info(user_id, user_name, user_phone, real_name, user_part, user_sex, user_email, note,user_pass,user_state_code,create_time)VALUES(?,?,?,?,?,?,?,?,?,?,?)", new Object[] {
						userId,
						userName,
						userPhone, 
						realName,
						userPart,
						userSex,
						userEmail,
						userNote,
						password,
						"Y",
						new Date()
						});
				
    			if(userRoles != null)
    			{
    				for(String userRole : userRoles)
    				{
    					db.executeSQL("INSERT INTO sys_role_user(user_id, role_id)VALUES(?,?)", new Object[] {userId, userRole});
    				}
    			}
    			
    			tranReq.appendOperateLog(db, "创建用户", "创建用户:" + userName);
    			
				db.executeSQL("COMMIT", null);
				
				jsonWr.addKeyValue("user_id", userId);
				
				return tranReq.returnJson(jsonWr);
			}
			catch(Exception ex)
			{
				db.executeSQL("ROLLBACK", null);
				throw ex;
			}   		
    	}
    	finally
    	{
    		db.close();
    	}
	}
	
	public ModelAndView deleteUserInfo(SMTAIServerRequest tranReq) throws Exception
	{
		String userId = tranReq.convParamToString("user_id", true);
		
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		
    		DBRecords recs = db.querySQL("SELECT user_name FROM sys_user_info WHERE user_id=?", new Object[] {userId});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未发现用户", null);
    		String userName = recs.getRecord(0).getString(0);
    		
    		db.executeSQL("BEGIN", null);
    		try
    		{
				db.executeSQL("UPDATE sys_user_info SET user_state_code='N' WHERE user_id=?", new Object[] {userId});
				db.executeSQL("DELETE FROM sys_role_user WHERE user_id=?", new Object[] {userId});
				
				tranReq.appendOperateLog(db, "删除用户", "删除用户:" + userName);
				
				db.executeSQL("COMMIT", null);
    		}
    		catch(Exception ex)
    		{
    			db.rollbackDB(ex);
    			return null;
    		}
    		
			return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
	}	
	public ModelAndView updateUserInfo(SMTAIServerRequest tranReq) throws Exception
	{
		String userId = tranReq.convParamToString("user_id", true);
		String userPhone = tranReq.convParamToString("phone", false);
		String realName = tranReq.convParamToString("real_name", false);
		String userPart = tranReq.convParamToString("part", false);
		String userSex = tranReq.convParamToString("sex", false);
		String userEmail = tranReq.convParamToString("email", false);
		String userNote = tranReq.convParamToString("note", false);
		
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT user_name FROM sys_user_info WHERE user_id=?", new Object[] {userId});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未发现用户", null);
    		String userName = recs.getRecord(0).getString(0);
    		
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			db.executeSQL("UPDATE sys_user_info SET user_phone=?,real_name=?,user_part=?,user_sex=?,user_email=?,note=? WHERE user_id=?", new Object[] {
					userPhone, 
					realName,
					userPart,
					userSex,
					userEmail,
					userNote,
					userId
					});
			
			tranReq.appendOperateLog(db, "更新用户", "更新用户[" + userName + "]的信息");
			
			return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
	}
	
	public ModelAndView changeUserPassword(SMTAIServerRequest tranReq) throws Exception
	{
		String userId = tranReq.convParamToString("user_id", true);
		String password = SMTStatic.convStrToMD5(tranReq.convParamToString("password", true));
		
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT user_name FROM sys_user_info WHERE user_id=?", new Object[] {userId});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未发现用户", null);
    		String userName = recs.getRecord(0).getString(0);
    		
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			db.executeSQL("UPDATE sys_user_info SET user_pass=? WHERE user_id=?", new Object[] {password, userId});
			tranReq.appendOperateLog(db, "修改密码", "修改用户[" + userName + "]的密码");
			
			return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
	}
	
	public ModelAndView getUserInfoList(SMTAIServerRequest tranReq) throws Exception
	{
    	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			DBRecords recsUser = db.querySQL("SELECT U.*, P.department_name FROM sys_user_info U LEFT JOIN sys_department_info P ON U.user_part=P.department_id  WHERE user_state_code='Y'", null);
			DBRecords recsRoles = db.querySQL("SELECT * FROM sys_role_user", null);
			
			Map<String, Set<String>> mapUserId2RoleSet = new HashMap<>();
			for(DBRecord recRole : recsRoles.getRecords())
			{
				String roleId = recRole.getString("role_id");
				String userId = recRole.getString("user_id");
				Set<String> setRole = mapUserId2RoleSet.get(userId);
				if(setRole == null)
				{
					setRole = new HashSet<>();
					mapUserId2RoleSet.put(userId, setRole);
				}
				setRole.add(roleId);
			}
			
			jsonWr.beginArray("values");
			for(DBRecord recUser : recsUser.getRecords())
			{
				jsonWr.beginMap(null);
				{
					String userId = recUser.getString("user_id");
			    	jsonWr.addKeyValue("id", userId);
			    	jsonWr.addKeyValue("phone", recUser.getString("user_phone"));
			    	jsonWr.addKeyValue("name", recUser.getString("user_name"));
			    	jsonWr.addKeyValue("real_name", recUser.getString("real_name"));
			    	jsonWr.addKeyValue("part_id", recUser.getString("user_part"));
			    	jsonWr.addKeyValue("part_name", recUser.getString("department_name"));
			    	jsonWr.addKeyValue("sex", recUser.getString("user_sex"));
			    	jsonWr.addKeyValue("email", recUser.getString("user_email"));
			    	jsonWr.addKeyValue("note", recUser.getString("note"));	
			    	jsonWr.addKeyValue("create_time", recUser.getString("create_time"));
			    	
			    	Set<String> setRole = mapUserId2RoleSet.get(userId);
			    	if(setRole != null)
			    	{
			    		jsonWr.beginArray("roles");
			    		for(String role : setRole)
			    		{
			    			jsonWr.addKeyValue(null, role);
			    		}
			    		jsonWr.endArray();
			    	}
				}
				jsonWr.endMap();
			}
			jsonWr.endArray();
				
			
			return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
	}
	
	public ModelAndView getChatHistoryAnswer(SMTAIServerRequest tranReq) throws Exception
	{
		String historyId = tranReq.convParamToString("id", true);
		
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);	
    		
    		DBRecords recs = db.querySQL("SELECT ask_json, trace_llm FROM chat_history_detail WHERE history_id=?", new Object[] {historyId});
    		if(recs.getRowCount() == 0)
    			return tranReq.returnJsonState(false, "未找到对话id", null);
    		
    		DBRecord rec = recs.getRecord(0);
    		String sAskJson = rec.getString("ask_json");
    		if(!SMTStatic.isNullOrEmpty(sAskJson))
    			jsonWr.addKeyRaw("ask_json", sAskJson);
    		
    		jsonWr.addKeyValue("ask_log", rec.getString("trace_llm"));
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}
	}
	
	public ModelAndView getChatHistoryList(SMTAIServerRequest tranReq) throws Exception
	{
		Date startTime = tranReq.convParamToDate("start_time", false);
		Date endTime = tranReq.convParamToDate("end_time", false);
		String groupType = tranReq.convParamToString("group_type", false);
		String opUser = tranReq.convParamToString("user", false);
		String question = tranReq.convParamToString("question", false);
		String state = tranReq.convParamToString("state", false);
		
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		
    		String sql = 
    			" SELECT A.history_id, A.group_id, A.question, A.create_time, A.run_ms, A.chat_state, A.group_type, B.user_name"
    		  +	" FROM chat_history_detail A"
    		  + " INNER JOIN chat_history_group C ON A.group_id=C.group_id"
    		  + " LEFT JOIN sys_user_info B ON C.user_id=B.user_id"
    		  + " WHERE 1=1 "
    		  ;
    		List<Object> sqlParams = new ArrayList<>();
    		if(startTime != null)
    		{
    			sql += " AND A.create_time >=?";
    			sqlParams.add(startTime);
    		}

    		if(endTime != null)
    		{
    			sql += " AND A.create_time <=?";
    			sqlParams.add(endTime);
    		}
    		
    		if(groupType != null)
    		{
    			sql += " AND A.group_type in (SELECT regexp_split_to_table(?, ','))";
    			sqlParams.add(groupType);
    		}
    		
    		if(opUser != null)
    		{
    			sql += " AND B.user_name LIKE ?";
    			sqlParams.add("%" + opUser + "%");
    		}
    		
    		if(question != null)
    		{
    			sql += " AND A.question LIKE ?";
    			sqlParams.add("%" + question + "%");
    		}
    		
    		if(state != null)
    		{
    			sql += " AND A.chat_state = ?";
    			sqlParams.add(state);
    		}
    		
    		
    		sql += " ORDER BY create_time DESC LIMIT 1000";
    		
    		DBRecords recs = db.querySQL(sql, sqlParams.toArray(new Object[sqlParams.size()]));
    		
    		jsonWr.beginArray("values");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("id", rec.getString("history_id"));
    				jsonWr.addKeyValue("group_id", rec.getString("group_id"));
    				jsonWr.addKeyValue("time", rec.getString("create_time"));
    				jsonWr.addKeyValue("user", rec.getString("user_name"));
    				jsonWr.addKeyValue("question", rec.getString("question"));
    				jsonWr.addKeyValue("run_ms", rec.getString("run_ms"));
    				jsonWr.addKeyValue("state", rec.getString("chat_state"));
    				jsonWr.addKeyValue("group_type", rec.getString("group_type"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}	
		
	}
	
	public ModelAndView getDepartmentList(SMTAIServerRequest tranReq) throws Exception
	{
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		
    		DBRecords recs = db.querySQL("SELECT D.*, U.user_name FROM sys_department_info D LEFT JOIN sys_user_info U ON U.user_id=D.create_user", null);
    		
    		jsonWr.beginArray("values");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("id", rec.getString("department_id"));
    				jsonWr.addKeyValue("parent_id", rec.getString("department_p_id"));
    				jsonWr.addKeyValue("name", rec.getString("department_name"));
    				jsonWr.addKeyValue("create_user", rec.getString("user_name"));
    				jsonWr.addKeyValue("create_time", rec.getString("create_time"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}

	}
	
	public ModelAndView addDepartmentInfo(SMTAIServerRequest tranReq) throws Exception
	{
		String partName = tranReq.convParamToString("name", false);
		String parentId = tranReq.convParamToString("parent_id", false);
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		String partId = SMTStatic.newUUID();
    		
    		db.executeSQL(
    			"INSERT INTO sys_department_info(department_id, department_p_id, department_name, create_user, create_time)VALUES(?,?,?,?,?)", 
    			new Object[] {
    				partId,
    				parentId,
    				partName,
    				tranReq.getLoginUserId(),
    				new Date()
    			});
    		
    		jsonWr.addKeyValue("id", partId);
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}    	
	}
	
	public ModelAndView deleteDepartment(SMTAIServerRequest tranReq) throws Exception
	{
		String partId = tranReq.convParamToString("id", true);
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		DBRecords recs = db.querySQL("SELECT COUNT(*) FROM sys_department_info WHERE department_p_id=?", new Object[] {partId});
    		if(recs.getRecord(0).getInteger(0) > 0)
    			return tranReq.returnJsonState(false, "当前部门有下属子部门", null);
    		
    		recs = db.querySQL("SELECT COUNT(*) FROM sys_user_info WHERE user_part=?", new Object[] {partId});
    		if(recs.getRecord(0).getInteger(0) > 0)
    			return tranReq.returnJsonState(false, "当前部门有所属用户", null);   	
    		
    		db.executeSQL("DELETE FROM sys_department_info WHERE department_id=?", new Object[] {partId});
    		
    		return tranReq.returnJsonState(true, null, null);
    	}
    	finally
    	{
    		db.close();
    	}  
	}
	
	public ModelAndView updateDepartmentInfo(SMTAIServerRequest tranReq) throws Exception
	{
		String partId = tranReq.convParamToString("id", true);
		String partName = tranReq.convParamToString("name", true);
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		db.executeSQL("UPDATE sys_department_info SET department_name=? WHERE department_id=?", new Object[] {partName, partId});
    		
    		return tranReq.returnJsonState(true, null, null);
    	}
    	finally
    	{
    		db.close();
    	}  
	}

	public ModelAndView getUserSampleList(SMTAIServerRequest tranReq) throws Exception
	{
		String userId = tranReq.convParamToString("user_id", true);
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		String sql = 
				  "SELECT\r\n"
				+ "	group_id, sample_id, sample_question, sample_title\r\n"
				+ "FROM\r\n"
				+ "	ai_scene_sample\r\n"
				+ "WHERE\r\n"
				+ "   sample_enable='Y' AND \n" 
				+ "	  group_id IN (\r\n"
				+ "		SELECT\r\n"
				+ "			group_id\r\n"
				+ "		FROM\r\n"
				+ "			sys_role_group\r\n"
				+ "		WHERE\r\n"
				+ "			role_id IN (\r\n"
				+ "				SELECT\r\n"
				+ "					role_id\r\n"
				+ "				FROM\r\n"
				+ "					sys_role_user\r\n"
				+ "				WHERE\r\n"
				+ "					user_id=?\r\n"
				+ "			)\r\n"
				+ "	\r\n"
				+ "	)";
    		
    		DBRecords recs = db.querySQL(sql, new Object[] {userId});
    		
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		jsonWr.beginArray("values");
    		for(DBRecord rec : recs.getRecords())
    		{
    			jsonWr.beginMap(null);
    			{
    				jsonWr.addKeyValue("sample_id", rec.getString("sample_id"));
    				jsonWr.addKeyValue("group_id", rec.getString("group_id"));
    				jsonWr.addKeyValue("question", rec.getString("sample_question"));
    				jsonWr.addKeyValue("title", rec.getString("sample_title"));
    			}
    			jsonWr.endMap();
    		}
    		jsonWr.endArray();
    		
    		return tranReq.returnJson(jsonWr);
    	}
    	finally
    	{
    		db.close();
    	}  
	}
	
	public ModelAndView getLLMInfoList(SMTAIServerRequest tranReq) throws Exception
	{
	   	SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
    	try
    	{
    		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
    		
    		DBRecords recsFactory = db.querySQL("SELECT * FROM ai_llm_factory", null);
    		DBRecords recsConnect = db.querySQL("SELECT * FROM ai_llm_connect", null);
    		
    		jsonWr.beginMap("values");
    		for(DBRecord rec : recsFactory.getRecords())
    		{
    			jsonWr.beginMap(rec.getString("factory_id"));
    			{
    				jsonWr.addKeyValue("title", rec.getString("factory_title"));
    				
    				String sJsonConfig = rec.getString("factory_args");
    				if(!SMTStatic.isNullOrEmpty(sJsonConfig))
    					jsonWr.addKeyRaw("config", sJsonConfig);
    			}
    			jsonWr.endMap();
    		}
    		
    		for(DBRecord rec : recsConnect.getRecords())
    		{
    			jsonWr.beginMap(rec.getString("factory_id"));
    			{
    				jsonWr.beginMap("connects");
    				{
    					jsonWr.beginMap(rec.getString("connect_id"));
    					{
    						jsonWr.addKeyValue("title", rec.getString("connect_title"));
    						jsonWr.addKeyValue("class", rec.getString("class_name"));
    						String sJsonConfig = rec.getString("connect_args");
    	    				if(!SMTStatic.isNullOrEmpty(sJsonConfig))
    	    					jsonWr.addKeyRaw("config", sJsonConfig);
    					}
    					jsonWr.endMap();
    				}
    				jsonWr.endMap();
    			}
    			jsonWr.endMap();
    		}
    		
    		jsonWr.endMap();
    		
    		return tranReq.returnJson(jsonWr);
		}
		finally
		{
			db.close();
		}
	}

	public ModelAndView addLLMFactory(SMTAIServerRequest tranReq) throws Exception {
		// 解析请求参数
		String factoryId = tranReq.convParamToString("factory_id", true);
		String factoryTitle = tranReq.convParamToString("factory_title",true);
		String factoryArgs = tranReq.convParamToString("factory_args",true);
		SMTDatabase db = SMTAIServerApp.getApp().allocDatabase();
		try {

			String selectSql = "SELECT * FROM ai_llm_factory WHERE factory_id = ?";
			Object[] params1 = {factoryId};
			if (db.querySQL(selectSql, params1).getRowCount()>0) {
				return tranReq.returnJsonState(false, "模型id已存在", null);
			}
			// 构建 SQL 语句
			String sql = "INSERT INTO ai_llm_factory (factory_id, factory_title, factory_args) VALUES (?, ?, ?)";
			Object[] params2 = {factoryId, factoryTitle, factoryArgs};
			// 执行插入操作
			db.executeSQL(sql, params2);
			// 返回 JSON 结果
			SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			return tranReq.returnJson(jsonWr);
		} finally {
			db.close();
		}
	}
	public ModelAndView addLLMConnect(SMTAIServerRequest tranReq) throws Exception {
		// 解析请求参数
		String connectId = tranReq.convParamToString("connect_id", true);
		String factoryId = tranReq.convParamToString("factory_id", true);
		String connectTitle = tranReq.convParamToString("connect_title", true);
		String className = tranReq.convParamToString("class_name", true);
		String connectArgs = tranReq.convParamToString("connect_args", true);

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

		try {
			// 首先检查模型id是否存在
			String selectFactorySql = "SELECT * FROM ai_llm_factory WHERE factory_id = ?";
			Object[] factoryParams = {factoryId};
			if (db.querySQL(selectFactorySql, factoryParams).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "模型id不存在", null);
			}

			// 检查连接id是否已存在
			String selectConnectSql = "SELECT * FROM ai_llm_connect WHERE connect_id = ?";
			Object[] connectParams = {connectId};
			if (db.querySQL(selectConnectSql, connectParams).getRowCount() > 0) {
				return tranReq.returnJsonState(false, "连接id已存在", null);
			}

			// 构建 SQL 语句
			String insertSql = "INSERT INTO ai_llm_connect (connect_id, factory_id, connect_title, class_name, connect_args) VALUES (?, ?, ?, ?, ?)";
			Object[] insertParams = {connectId, factoryId, connectTitle, className, connectArgs};

			// 执行插入操作
			db.executeSQL(insertSql, insertParams);

			// 返回 JSON 结果
			SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			return tranReq.returnJson(jsonWr);
		} finally {
			db.close();
		}
	}
	public ModelAndView updateLLMFactory(SMTAIServerRequest tranReq) throws Exception {
		// 解析请求参数
		String factoryId = tranReq.convParamToString("factory_id", true);
		String factoryTitle = tranReq.convParamToString("factory_title", true);
		String factoryArgs = tranReq.convParamToString("factory_args", true);

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

		try {
			// 检查工厂 ID 是否存在
			String selectSql = "SELECT * FROM ai_llm_factory WHERE factory_id = ?";
			Object[] params = {factoryId};
			if (db.querySQL(selectSql, params).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "模型id不存在", null);
			}

			// 构建 SQL 语句
			String updateSql = "UPDATE ai_llm_factory SET factory_title = ?, factory_args = ? WHERE factory_id = ?";
			Object[] updateParams = {factoryTitle, factoryArgs, factoryId};

			// 执行更新操作
			db.executeSQL(updateSql, updateParams);

			// 返回 JSON 结果
			SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			return tranReq.returnJson(jsonWr);
		} finally {
			db.close();
		}
	}
	public ModelAndView updateLLMConnect(SMTAIServerRequest tranReq) throws Exception {
		// 解析请求参数
		String connectId = tranReq.convParamToString("connect_id", true);
		String factoryId = tranReq.convParamToString("factory_id", true);
		String connectTitle = tranReq.convParamToString("connect_title", true);
		String className = tranReq.convParamToString("class_name", true);
		String connectArgs = tranReq.convParamToString("connect_args", true);

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

		try {
			// 检查连接 ID 是否存在
			String selectConnectSql = "SELECT * FROM ai_llm_connect WHERE connect_id = ?";
			Object[] connectParams = {connectId};
			if (db.querySQL(selectConnectSql, connectParams).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "连接id不存在", null);
			}

			// 检查模型 ID 是否存在
			String selectFactorySql = "SELECT * FROM ai_llm_factory WHERE factory_id = ?";
			Object[] factoryParams = {factoryId};
			if (db.querySQL(selectFactorySql, factoryParams).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "模型id不存在", null);
			}

			// 构建 SQL 语句
			String updateSql = "UPDATE ai_llm_connect SET factory_id = ?, connect_title = ?, class_name = ?, connect_args = ? WHERE connect_id = ?";
			Object[] updateParams = {factoryId, connectTitle, className, connectArgs, connectId};

			// 执行更新操作
			db.executeSQL(updateSql, updateParams);

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


	public ModelAndView clearSystemCache(SMTAIServerRequest tranReq) throws Exception
	{
		SMTApp.getThis().clearEhCacheManager();
		return tranReq.returnJsonState(true, null, null);
	}
	
	
	public ModelAndView getQuestionReplaceList(SMTAIServerRequest tranReq) throws Exception
	{
		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
		try(SMTDatabase db = SMTAIServerApp.getApp().allocDatabase())
		{
			DBRecords recs = db.querySQL("SELECT * FROM ai_question_replace", null);
			
			jsonWr.beginArray("values");
			for(DBRecord rec : recs.getRecords())
			{
				jsonWr.beginMap(null);
				{
					jsonWr.addKeyValue("id", rec.getString("replace_id"));
					jsonWr.addKeyValue("question", rec.getString("question_text"));
					jsonWr.addKeyValue("replace", rec.getString("replace_text"));
					jsonWr.addKeyValue("order", rec.getString("replace_order"));
					jsonWr.addKeyValue("state", rec.getString("replace_state"));
					jsonWr.addKeyValue("group", rec.getString("group_type"));
					jsonWr.addKeyValue("create_time", rec.getString("create_time"));
				}
				jsonWr.endMap();
			}
			jsonWr.endArray();
		}
		return tranReq.returnJson(jsonWr);
	}
	public ModelAndView addQuestionReplace(SMTAIServerRequest tranReq) throws Exception {
		String loginUserId = tranReq.getLoginUserId();
		// 解析请求参数
		String replaceId = tranReq.convParamToString("replace_id", true);
		String questionText = tranReq.convParamToString("question_text", true);
		String replaceText = tranReq.convParamToString("replace_text", true);
		int replaceOrder = tranReq.convParamToInteger("replace_order", true);
		String replaceState = tranReq.convParamToString("replace_state", true);
		String groupType = tranReq.convParamToString("group_type", true);

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

		try {
			// 检查替换ID是否已存在
			String selectSql = "SELECT * FROM ai_question_replace WHERE replace_id = ?";
			Object[] selectParams = {replaceId};
			if (db.querySQL(selectSql, selectParams).getRowCount() > 0) {
				return tranReq.returnJsonState(false, "替换ID已存在", null);
			}

			// 构建 SQL 语句
			String insertSql = "INSERT INTO ai_question_replace (replace_id, question_text, replace_text, replace_order, replace_state, create_user, create_time, group_type) " +
					"VALUES (?, ?, ?, ?, ?, ?, NOW(), ?)";
      Object[] insertParams = {
        replaceId, questionText, replaceText, replaceOrder, replaceState, loginUserId, groupType
      };

			// 执行插入操作
			db.executeSQL(insertSql, insertParams);

			// 返回 JSON 结果
			SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
			return tranReq.returnJson(jsonWr);
		} finally {
			db.close();
		}
	}
	public ModelAndView updateQuestionReplace(SMTAIServerRequest tranReq) throws Exception {
		// 解析请求参数
		String replaceId = tranReq.convParamToString("replace_id", true);
		String questionText = tranReq.convParamToString("question_text", false);
		String replaceText = tranReq.convParamToString("replace_text", false);
		int replaceOrder = tranReq.convParamToInteger("replace_order", false);
		String replaceState = tranReq.convParamToString("replace_state", false);
		String groupType = tranReq.convParamToString("group_type", false);

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

		try {
			// 检查替换ID是否存在
			String selectSql = "SELECT * FROM ai_question_replace WHERE replace_id = ?";
			Object[] selectParams = {replaceId};
			if (db.querySQL(selectSql, selectParams).getRowCount() == 0) {
				return tranReq.returnJsonState(false, "替换ID不存在", null);
			}

			// 构建 SQL 语句
			String updateSql = "UPDATE ai_question_replace SET question_text = ?, replace_text = ?, replace_order = ?, replace_state = ?, group_type = ? " +
					"WHERE replace_id = ?";
			Object[] updateParams = {questionText, replaceText, replaceOrder, replaceState, groupType, replaceId};

			// 执行更新操作
			db.executeSQL(updateSql, updateParams);

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

	public ModelAndView getResourceUsage(SMTAIServerRequest tranReq) throws Exception 
	{
		SMTJsonWriter jsonWr = tranReq.newReturnJsonWriter(true, null, null);
		
		SMTAIServerApp.getApp().getDatasourceUsgaeToJson(jsonWr);
		
		return tranReq.returnJson(jsonWr);
	}

}