using System.Collections.Concurrent;
using System.Data;
using System.Text.RegularExpressions;
using Infrastructure;
using LitJson;
using Model;
using Model.Custom;
using Model.Source;
using Newtonsoft.Json;

namespace Util.Logic
{
    public class LogicDatabaseNodeHelper
    {
        /// <summary>
        /// 数据库查询节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="tables">数据库表列表</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbQuery(NodeList node, List<DatabaseInfo> databaseInfos, List<DatabaseTable> tables, List<ParamList> paramList, List<LogItem> logDic, string env = "development")
        { 
            Dictionary<string, object> condi = new Dictionary<string, object>(); // 条件参数字典
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData queryField = nodeData["data"]["formData"]["output"]["value"]; // 查询字段
            JsonData conditions = nodeData["data"]["formData"]["ifelse"]["value"][0]["conditions"]; // 条件
            JsonData sortConditions = nodeData["data"]["formData"]["sort"]["value"]; // 条件
            string queryLimit = nodeData["data"]["formData"]["queryLimit"]["value"].ToString(); // 查询上限
            string logicTag = nodeData["data"]["formData"]["ifelse"]["value"][0]["logic"].ToString(); // 逻辑
            string paramAttribute = "";
            if (nodeData.ToJson().Contains("\"parentNode\""))
            {
                paramAttribute = "iterationInputField";
            }
            else
            {
                paramAttribute = "";
            }
            string queryFieldString = "";
            foreach (JsonData field in queryField)
            {
                string fieldName = field["label"].ToString(); // 字段名
                JsonData fieldType = field["type"]; // 字段类型
                if(fieldType.Count == 2)
                {
                    if(fieldType[0].ToString() == "array" && fieldType[1].ToString() == "object")
                    {
                        JsonData children = field["children"];
                        foreach (JsonData child in children)
                        {
                            string subFieldName = child["label"].ToString(); // 字段名
                            queryFieldString += subFieldName + ",";
                        }
                    }
                }
                else
                {
                    queryFieldString += fieldName + ",";
                }
            }
            if(string.IsNullOrEmpty(queryFieldString)) queryFieldString = "*";
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            int dbTableId = int.Parse(dbInfo["dbTableId"].ToString()); // 数据库表ID
            string sql = "";
            var queryTable = tables.FirstOrDefault(x => x.id == dbTableId);
            if (queryTable != null)
            {
                string tableName = queryTable.tableName ?? string.Empty; // 数据库表名
                sql = "select " + queryFieldString.TrimEnd(',') + " from " + tableName;
                if (conditions.Count > 0)
                {
                    sql += " where ";
                }
                foreach (JsonData condition in conditions)
                {
                    string oper = condition["operator"].ToString(); // 运算符
                    string value = condition["value"].ToString(); // 值
                    if (value.Contains("."))
                    {
                        value = value.Substring(value.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == value && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == value) ?? new ParamList();
                        value = param.paramValueTransfer;
                    }
                    string variable = condition["variable"].ToString(); // 变量
                    if (variable.Contains("."))
                    {
                        variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == variable && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == variable) ?? new ParamList();
                        // string paramValue = param.paramValueTransfer;
                        sql += operatorTransferForSql(param, oper, value, variable);
                        sql += " " + logicTag + " ";
                    }
                }
                if (sql.EndsWith(" " + logicTag + " "))
                {
                    sql = sql.Substring(0, sql.Length - logicTag.Length - 2);
                }
                if (sortConditions.Count > 0)
                {
                    sql += " order by ";
                    foreach (JsonData sortCondition in sortConditions)
                    {
                        string variable = sortCondition["variable"].ToString(); // 变量
                        if (variable.Contains("."))
                        {
                            variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        }
                        string sort = sortCondition["sort"].ToString(); // 排序
                        sql += variable + " " + sort + ",";
                    }
                    sql = sql.TrimEnd(',');
                }
                if (!string.IsNullOrEmpty(queryLimit))
                {
                    sql += " limit " + queryLimit;
                }
                SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo(), env);
                Utils.WriteLog("执行sql:" + sql + ";");
                var items = db.Ado.GetDataTable(sql);
                if (items.Rows.Count > 0)
                { 
                    if (items.Rows.Count > 1)
                    {
                        foreach (JsonData field in queryField)
                        {
                            string fieldName = field["label"].ToString(); // 字段名
                            JsonData fieldType = field["type"]; // 字段类型
                            List<Dictionary<string, object>> values = new List<Dictionary<string, object>>();
                            if(fieldType.Count == 2)
                            {
                                if(fieldType[0].ToString() == "array" && fieldType[1].ToString() == "object")
                                {
                                    foreach (DataRow item in items.Rows)
                                    {
                                        Dictionary<string, object> fieldData = new Dictionary<string, object>();
                                        JsonData children = field["children"];
                                        foreach (JsonData child in children)
                                        {
                                            string subFieldName = child["label"].ToString(); // 字段名
                                            string subValue = item[subFieldName]?.ToString() ?? string.Empty; // 字段值
                                            fieldData.Add(subFieldName, subValue);
                                        }
                                        values.Add(fieldData);
                                    }
                                    paramList.Add(new ParamList()
                                    {
                                        nodeId = node.nodeId,
                                        paramAttribute = paramAttribute,
                                        paramName = fieldName,
                                        paramValue = Newtonsoft.Json.JsonConvert.SerializeObject(values),
                                        paramType = field["type"][0].ToString(),
                                    });
                                }
                            }
                        }
                    }
                    else
                    {
                        foreach (JsonData field in queryField)
                        {
                            string fieldName = field["label"].ToString(); // 字段名
                            string fieldType = field["type"][0].ToString();
                            string fieldValue = items.Rows.Count > 0 ? items.Rows[0][fieldName]?.ToString() : fieldType == "number" ? "0" : string.Empty; // 字段值
                            
                            if (nodeData.ToJson().Contains("\"parentNode\""))
                            { 
                                var parentParam = paramList.FirstOrDefault(x => x.paramName == fieldName && x.paramAttribute == "iterationInputField");
                                if(parentParam != null)
                                {
                                    parentParam.paramValue = fieldValue;
                                }
                                else
                                {
                                    paramList.Add(new ParamList()
                                    {
                                        nodeId = node.nodeId,
                                        paramAttribute = "iterationInputField",
                                        paramName = fieldName,
                                        paramValue = fieldValue,
                                        paramType = fieldType,
                                    });
                                }
                            }
                            else
                            {
                                paramList.Add(new ParamList()
                                {
                                    nodeId = node.nodeId,
                                    paramName = fieldName,
                                    paramValue = fieldValue,
                                    paramType = fieldType,
                                });
                            }
                            condi.Add(fieldName, fieldValue);
                        }
                    }
                }
                else
                {
                    foreach (JsonData field in queryField)
                    {
                        string fieldName = field["label"].ToString(); // 字段名
                        string fieldType = field["type"][0].ToString();
                        string fieldValue = fieldType == "number" ? "0" : string.Empty; // 字段值
                        var parentParam = paramList.FirstOrDefault(x => x.paramName == fieldName && x.paramAttribute == paramAttribute);
                        if(parentParam != null)
                        {
                            parentParam.paramValue = fieldValue;
                        }
                        else
                        {
                            paramList.Add(new ParamList()
                            {
                                nodeId = node.nodeId,
                                paramAttribute = paramAttribute,
                                paramName = fieldName,
                                paramValue = fieldValue,
                                paramType = fieldType,
                            });
                        }
                        condi.Add(fieldName, fieldValue);
                    }
                }
            }
            logDic.Add(new LogItem()
            {
                title = node.nodeData["data"]["nodeMeta"]["label"].ToString(),
                param = condi,
                detail = sql
            });
        }

        /// <summary>
        /// 数据库SQL查询节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbSqlQuery(NodeList node, List<DatabaseInfo> databaseInfos, List<ParamList> paramList, List<LogItem> logDic, string env = "development")
        { 
            Dictionary<string, object> condi = new Dictionary<string, object>(); // 条件参数字典
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData queryField = nodeData["data"]["formData"]["output"]["value"]; // 查询字段
            string sql = nodeData["data"]["formData"]["sql"]["value"]["code"]["content"].ToString(); // SQL查询语句
            string paramAttribute = "";
            if (nodeData.ToJson().Contains("\"parentNode\""))
            {
                paramAttribute = "iterationInputField";
            }
            else
            {
                paramAttribute = "";
            }
            MatchCollection matches = Regex.Matches(sql, "\\$\\{.*?\\}");
            foreach (Match match in matches)
            {
                string paramName = match.Value.Replace("${", "").Replace("}", "");
                string paramValue = "";
                var param = paramList.FirstOrDefault(x => x.paramName == paramName && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == paramName) ?? new ParamList();
                paramValue = param.paramValueTransfer;
                sql = sql.Replace(match.Value, paramValue);
            }
            sql = LogicHelper.MatchExpressionVal(sql);
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo(), env);
            Utils.WriteLog("执行sql:" + sql + ";");
            var items = db.Ado.GetDataTable(sql);
            if(items.Rows.Count == 1)
            {
                foreach (JsonData field in queryField)
                {
                    string fieldName = field["label"].ToString(); // 字段名
                    string fieldType = field["type"][0].ToString();
                    string fieldValue = items.Rows.Count > 0 ? items.Rows[0][fieldName]?.ToString() : fieldType == "number" ? "0" : string.Empty; // 字段值
                    var parentParam = paramList.FirstOrDefault(x => x.paramName == fieldName && x.paramAttribute == paramAttribute);
                    if(parentParam != null)
                    {
                        parentParam.paramValue = fieldValue;
                    }
                    else
                    {
                        paramList.Add(new ParamList()
                        {
                            nodeId = node.nodeId,
                            paramName = fieldName,
                            paramAttribute = paramAttribute,
                            paramValue = fieldValue,
                            paramType = fieldType,
                        });
                    }
                    condi.Add(fieldName, fieldValue);
                }
            }
            else if(items.Rows.Count > 1)
            {
                JsonData field = queryField[0];
                string fieldName = field["label"].ToString(); // 字段名
                string fieldType = field["type"][0].ToString();
                string fieldValue = JsonConvert.SerializeObject(items, Formatting.Indented);
                var parentParam = paramList.FirstOrDefault(x => x.paramName == fieldName && x.paramAttribute == paramAttribute);
                if(parentParam != null)
                {
                    parentParam.paramValue = fieldValue;
                }
                else
                {
                    paramList.Add(new ParamList()
                    {
                        nodeId = node.nodeId,
                        paramName = fieldName,
                        paramAttribute = paramAttribute,
                        paramValue = fieldValue,
                        paramType = fieldType,
                    });
                }
                condi.Add(fieldName, fieldValue);
            }
            logDic.Add(new LogItem()
            {
                title = node.nodeData["data"]["nodeMeta"]["label"].ToString(),
                param = condi,
                detail = sql
            });
        }

        /// <summary>
        /// 数据库插入节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="tables">数据库表列表</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbInsert(NodeList node, List<DatabaseInfo> databaseInfos, List<DatabaseTable> tables, List<ParamList> paramList, List<LogItem> logDic, string env = "development")
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData insertField = nodeData["data"]["formData"]["setField"]["value"]; // 插入字段
            string insertFieldString = "";
            string insertFieldValueString = "";
            string paramAttribute = "";
            if (nodeData.ToJson().Contains("\"parentNode\""))
            {
                paramAttribute = "iterationInputField";
            }
            else
            {
                paramAttribute = "";
            }
            foreach (JsonData field in insertField)
            {
                string fieldName = field["label"].ToString(); // 字段名
                string fieldType = field["type"][0].ToString(); // 字段类型
                string fieldValue = field["value"].ToString(); // 字段值
                if (fieldValue.Contains('.'))
                {
                    fieldValue = fieldValue.Substring(fieldValue.LastIndexOf(".") + 1);
                    var param = paramList.FirstOrDefault(x => x.paramName == fieldValue && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == fieldValue) ?? new ParamList();
                    fieldValue = param.paramValueTransfer;
                }
                insertFieldString += fieldName + ",";
                if(fieldType == "string" || fieldType == "time")
                {
                    insertFieldValueString += "'" + fieldValue + "',";
                }
                else
                {
                    insertFieldValueString += fieldValue + ",";
                }
            }
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            int dbTableId = int.Parse(dbInfo["dbTableId"].ToString()); // 数据库表ID
            var queryTable = tables.FirstOrDefault(x => x.id == dbTableId);
            string sql = "";
            if (queryTable != null)
            {
                string tableName = queryTable.tableName ?? string.Empty; // 数据库表名
                sql = "insert into " + tableName + " (" + insertFieldString.TrimEnd(',') + ") values (" + insertFieldValueString.TrimEnd(',') + ")";
                SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo(), env);
                sql = LogicHelper.MatchExpressionVal(sql);
                Utils.WriteLog("执行sql:" + sql + ";");
                var items = db.Ado.ExecuteCommand(sql);                
            }
            logDic.Add(new LogItem()
            {
                title = node.nodeData["data"]["nodeMeta"]["label"].ToString(),
                detail = sql
            });
        }

        /// <summary>
        /// 数据库更新节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="tables">数据库表列表</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbUpdate(NodeList node, List<DatabaseInfo> databaseInfos, List<DatabaseTable> tables, List<ParamList> paramList, List<LogItem> logDic, string env = "development")
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData conditions = nodeData["data"]["formData"]["ifelse"]["value"][0]["conditions"]; // 条件
            string logicTag = nodeData["data"]["formData"]["ifelse"]["value"][0]["logic"].ToString(); // 逻辑
            JsonData updateField = nodeData["data"]["formData"]["setField"]["value"]; // 修改字段
            string updateFieldString = "";
            string paramAttribute = "";
            if (nodeData.ToJson().Contains("\"parentNode\""))
            {
                paramAttribute = "iterationInputField";
            }
            else
            {
                paramAttribute = "";
            }

            foreach (JsonData field in updateField)
            {
                string fieldName = field["label"].ToString(); // 字段名
                string fieldType = field["type"][0].ToString(); // 字段类型
                string fieldValue = field["value"].ToString(); // 字段值
                if (fieldValue.Contains('.'))
                {
                    fieldValue = fieldValue.Substring(fieldValue.LastIndexOf(".") + 1);
                    var param = paramList.FirstOrDefault(x => x.paramName == fieldValue && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == fieldValue) ?? new ParamList();
                    fieldValue = param.paramValueTransfer;
                }
                if(fieldType == "string" || fieldType == "time")
                {
                    updateFieldString += fieldName + "='" + fieldValue + "',";
                }
                else
                {
                    updateFieldString += fieldName + "=" + fieldValue + ",";
                }
            }
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            int dbTableId = int.Parse(dbInfo["dbTableId"].ToString()); // 数据库表ID
            var queryTable = tables.FirstOrDefault(x => x.id == dbTableId);
            string sql = "";
            if (queryTable != null)
            {
                string tableName = queryTable.tableName ?? string.Empty; // 数据库表名
                sql = "update " + tableName + " set " + updateFieldString.TrimEnd(',');
                if (conditions.Count > 0)
                {
                    sql += " where ";
                }
                foreach (JsonData condition in conditions)
                {
                    string oper = condition["operator"].ToString(); // 运算符
                    string value = condition["value"].ToString(); // 值
                    if (value.Contains("."))
                    {
                        value = value.Substring(value.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == value && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == value) ?? new ParamList();
                        value = param.paramValueTransfer;
                    }
                    string variable = condition["variable"].ToString(); // 变量
                    if (variable.Contains("."))
                    {
                        variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == variable && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == variable) ?? new ParamList();
                        // string paramValue = param.paramValueTransfer;
                        sql += operatorTransferForSql(param, oper, value, variable);
                        sql += " " + logicTag + " ";
                    }
                }
                if (sql.EndsWith(" " + logicTag + " "))
                {
                    sql = sql.Substring(0, sql.Length - logicTag.Length - 2);
                }
                sql = LogicHelper.MatchExpressionVal(sql);
                SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo(), env);
                Utils.WriteLog("执行sql:" + sql + ";");
                var items = db.Ado.ExecuteCommand(sql);
            }
            logDic.Add(new LogItem()
            {
                title = node.nodeData["data"]["nodeMeta"]["label"].ToString(),
                detail = sql
            });
        }

        /// <summary>
        /// 数据库删除节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="tables">数据库表列表</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbDelete(NodeList node, List<DatabaseInfo> databaseInfos, List<DatabaseTable> tables, List<ParamList> paramList, List<LogItem> logDic, string env = "development")
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData conditions = nodeData["data"]["formData"]["ifelse"]["value"][0]["conditions"]; // 条件
            string logicTag = nodeData["data"]["formData"]["ifelse"]["value"][0]["logic"].ToString(); // 逻辑
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            int dbTableId = int.Parse(dbInfo["dbTableId"].ToString()); // 数据库表ID
            var queryTable = tables.FirstOrDefault(x => x.id == dbTableId);
            string paramAttribute = "";
            if (nodeData.ToJson().Contains("\"parentNode\""))
            {
                paramAttribute = "iterationInputField";
            }
            else
            {
                paramAttribute = "";
            }
            string sql = "";
            if (queryTable != null)
            {
                string tableName = queryTable.tableName ?? string.Empty; // 数据库表名
                sql = "delete from " + tableName;
                if (conditions.Count > 0)
                {
                    sql += " where ";
                }
                foreach (JsonData condition in conditions)
                {
                    string oper = condition["operator"].ToString(); // 运算符
                    string value = condition["value"].ToString(); // 值
                    if (value.Contains("."))
                    {
                        value = value.Substring(value.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == value && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == value) ?? paramList.FirstOrDefault(x => x.paramValue == value) ?? new ParamList();
                        value = param.paramValueTransfer;
                    }
                    string variable = condition["variable"].ToString(); // 变量
                    if (variable.Contains("."))
                    {
                        variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == variable && x.paramAttribute == paramAttribute) ?? paramList.FirstOrDefault(x => x.paramName == variable) ?? paramList.FirstOrDefault(x => x.paramValue == variable) ?? new ParamList();
                        // string paramValue = param.paramValueTransfer;
                        sql += operatorTransferForSql(param, oper, value, variable);
                        sql += " " + logicTag + " ";
                    }
                }
                if (sql.EndsWith(" " + logicTag + " "))
                {
                    sql = sql.Substring(0, sql.Length - logicTag.Length - 2);
                }
                sql = LogicHelper.MatchExpressionVal(sql);
                SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo(), env);
                var items = db.Ado.ExecuteCommand(sql);
            }
            logDic.Add(new LogItem()
            {
                title = node.nodeData["data"]["nodeMeta"]["label"].ToString(),
                detail = sql
            });
        }





        // 改用线程安全字典 ConcurrentDictionary，替代普通 Dictionary
        private static readonly ConcurrentDictionary<string, SqlSugarClient> _dbCache = new();

        /// <summary>
        /// 初始化/获取 SqlSugar 实例（多库+多环境 全局缓存）
        /// </summary>
        /// <param name="dbInfo">数据库配置</param>
        /// <param name="env">环境标识 development/production</param>
        /// <returns></returns>
        public static SqlSugarClient initDb(DatabaseInfo dbInfo, string env)
        {
            if (dbInfo == null)
                throw new ArgumentNullException(nameof(dbInfo));

            // 组合唯一 Key
            // string cacheKey = $"{dbInfo.id}_{env}";

            // 已存在直接返回
            // if (_dbCache.TryGetValue(cacheKey, out var existClient))
            // {
            //     return existClient;
            // }

            // 统一根据环境取值，减少重复判断
            string? host = env.Equals("development", StringComparison.OrdinalIgnoreCase) 
                ? dbInfo.devHostAddress 
                : dbInfo.hostAddress;

            int port = env.Equals("development", StringComparison.OrdinalIgnoreCase) 
                ? dbInfo.devPort 
                : dbInfo.port;

            string? userName = env.Equals("development", StringComparison.OrdinalIgnoreCase) 
                ? dbInfo.devUsername 
                : dbInfo.username;

            string? pwd = env.Equals("development", StringComparison.OrdinalIgnoreCase) 
                ? dbInfo.devPwd 
                : dbInfo.pwd;

            string dbName = dbInfo.dbName ?? string.Empty;
            if (string.IsNullOrEmpty(host) || string.IsNullOrEmpty(userName) || string.IsNullOrEmpty(dbName))
            {
                throw new Exception("数据库连接信息不完整：地址/账号/库名不能为空");
            }

            SqlSugar.DbType dbType;
            string connStr;

            // 区分 PG / MySQL，增加连接池配置
            if (dbInfo.dbType.Equals("pgsql", StringComparison.OrdinalIgnoreCase))
            {
                dbType = SqlSugar.DbType.PostgreSQL;
                connStr = $"Host={host};Port={port};Database={dbName};Username={userName};Password={pwd};Timeout=30;Pooling=true;Max Pool Size=100;Min Pool Size=5;";
            }
            else
            {
                dbType = SqlSugar.DbType.MySql;
                connStr = $"server={host};port={port};user={userName};password={pwd};database={dbName};charset=utf8;Max Pool Size=100;Min Pool Size=5;";
            }

            Utils.WriteLog($"数据库连接字符串：{connStr}");

            // 创建实例
            var config = new ConnectionConfig
            {
                ConnectionString = connStr,
                DbType = dbType,
                IsAutoCloseConnection = true, // 执行完毕自动归还物理连接到连接池
                InitKeyType = InitKeyType.Attribute
            };
            var newClient = new SqlSugarClient(config);

            // 原子写入缓存
            // _dbCache.TryAdd(cacheKey, newClient);
            return newClient;
        }

        /// <summary>
        /// 运算符转换（SQL）
        /// </summary>
        /// <param name="oper">运算符</param>
        /// <returns>转换后的运算符</returns>
        public static string operatorTransferForSql(ParamList param, string oper, string paramValue, string fieldName)
        {
            if (oper == "equal") //等于
            { 
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return fieldName + "='" + paramValue + "'";
                }
                return fieldName + "=" + paramValue;
            }
            else if (oper == "not_equal") //不等于
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return fieldName + "!='" + paramValue + "'";
                }
                return fieldName + "!=" + paramValue;
            }
            else if (oper == "greater") //大于
            {
                return fieldName + ">" + paramValue;
            }
            else if (oper == "greater_equal") //大于等于
            {
                return fieldName + " >= " + paramValue;
            }
            else if (oper == "less") //小于
            {
                return fieldName + " < " + paramValue;
            }
            else if (oper == "less_equal") //小于等于
            {
                return fieldName + " <= " + paramValue;
            }
            else if (oper == "between") //在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return fieldName + " >= " + betweenValues[0] + " and " + fieldName + " <= " + betweenValues[1];
            }
            else if (oper == "not_between") //不在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return "(" + fieldName + " < " + betweenValues[0] + " or " + fieldName + " > " + betweenValues[1] + ")";
            }
            else if (oper == "contains") //包含
            {
                return fieldName + " like '%" + paramValue + "%'";
            }
            else if (oper == "not_contains") //不包含
            {
                return fieldName + " not like '%" + paramValue + "%'";
            }
            else if (oper == "starts_with") //以...开头
            {
                return fieldName + " like '" + paramValue + "%'";
            }
            else if (oper == "ends_with") //以...结尾
            {
                return fieldName + " like '%" + paramValue + "'";
            }
            else if (oper == "is_empty")
            {
                return fieldName + " is null";
            }
            else if (oper == "is_not_empty") //不为空
            {
                return fieldName + " is not null";
            }
            else if (oper == "is_null") //为空
            {
                return fieldName + " is null";
            }
            else if (oper == "is_not_null") //不为空
            {
                return fieldName + " is not null";
            }
            else if (oper == "in") //在...中
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " in (" + paramValue + ")";
            }
            else if (oper == "not_in") //不在...中
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " not in (" + paramValue + ")";
            }
            else if (oper == "array_contains") //数组包含
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " in (" + paramValue + ")";
            }
            else if (oper == "array_not_contains") //数组不包含 
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " not in ('" + paramValue + "')";
                }
                return fieldName + " not in (" + paramValue + ")";
            }
            else if (oper == "array_length_equal") //数组长度等于
            {
                return "Length(" + fieldName + ")=" + paramValue;
            }
            else if (oper == "array_length_greater")
            {
                return "Length(" + fieldName + ") > " + paramValue;
            }
            else if (oper == "array_length_less") //数组长度小于
            {
                return "Length(" + fieldName + ") < " + paramValue;
            }
            else if (oper == "is_true") //为真
            {
                return fieldName + "=true";
            }
            else if (oper == "is_false") //为假
            {
                return fieldName + "=false";
            }
            else if (oper == "date_equal") //日期等于
            {
                return fieldName + "='" + paramValue + "'";
            }
            else if (oper == "date_before") //日期之前
            {
                return fieldName + " < '" + paramValue + "'";
            }
            else if (oper == "date_after") //日期之后
            {
                return fieldName + " > '" + paramValue + "'";
            }
            else if (oper == "date_between") //日期之间
            {
                string[] betweenValues = paramValue.Split(" - ", StringSplitOptions.None);
                return fieldName + " >= '" + betweenValues[0] + "' and " + fieldName + " <= '" + betweenValues[1] + "'";
            }
            return "";
        }
    
    }
}