OA2023/OASystem/OASystem.Api/OAMethodLib/GenericSearch/DynamicSearchService.cs

using SqlSugar;
using System.Diagnostics;
using System.DirectoryServices;
using System.Linq;
using System.Linq.Expressions;

namespace OASystem.API.OAMethodLib.GenericSearch
{
    /// <summary>
    /// 动态检索服务
    /// 支持动态字段权重配置、返回字段筛选、智能搜索等功能
    /// </summary>
    /// <typeparam name="T">实体类型</typeparam>
    public class DynamicSearchService<T> where T : class, new()
    {
        private readonly SqlSugarClient _db;
        private readonly ILogger<DynamicSearchService<T>> _logger;

        public DynamicSearchService(SqlSugarClient db, ILogger<DynamicSearchService<T>> logger)
        {
            _db = db;
            _logger = logger;
        }

        /// <summary>
        /// 执行动态搜索（应用层统计匹配度）
        /// </summary>
        /// <param name="request">搜索请求参数</param>
        /// <returns>包含搜索结果和匹配度信息的结果对象</returns>
        public async Task<SearchResult<T>> SearchAsync(DynamicSearchRequest request)
        {
            var resultView = new SearchResult<T>() { Success = false, Message = "异常错误" };

            var stopwatch = Stopwatch.StartNew();
            var searchId = Guid.NewGuid().ToString("N")[..8];

            try
            {
                List<T> data;
                int totalCount;

                // 使用原生SQL方式构建查询
                if (!string.IsNullOrWhiteSpace(request.Keyword))
                {
                    var result = await SearchWithNativeSqlAsync(request);
                    data = result.Data;
                    totalCount = result.TotalCount;
                }
                else
                {
                    // 无关键词时使用简单查询
                    var query = BuildBaseQuery(request);
                    totalCount = await query.CountAsync();
                    data = await query.ToPageListAsync(request.PageIndex, request.PageSize);
                }

                // 在应用层计算匹配度
                var scoredItems = CalculateMatchScore(data, request);

                stopwatch.Stop();

                return new SearchResult<T>
                {
                    Message = $"搜索成功！耗时：{stopwatch.ElapsedMilliseconds}ms",
                    Items = scoredItems,
                    TotalCount = totalCount,
                    Keyword = request.Keyword,
                    FieldWeights = request.FieldWeights,
                    ReturnFields = request.ReturnFields,
                    PageIndex = request.PageIndex,
                    PageSize = request.PageSize,
                    ResponseTime = stopwatch.ElapsedMilliseconds,
                    SearchId = searchId
                };
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                resultView.Message = string.Format("【{SearchId}】动态搜索失败: {ErrorMessage}", searchId, ex.Message);
                return resultView;
            }
        }

        /// <summary>
        /// 轻量级搜索 - 只返回指定字段，提升性能（应用层统计匹配度）
        /// </summary>
        /// <typeparam name="TResult">返回的结果类型</typeparam>
        /// <param name="request">搜索请求参数</param>
        /// <param name="selector">字段选择表达式</param>
        /// <returns>包含指定字段和匹配度信息的搜索结果</returns>
        public async Task<SearchResult<TResult>> LightweightSearchAsync<TResult>(
            DynamicSearchRequest request,
            Expression<Func<T, TResult>> selector) where TResult : class, new()
        {
            var stopwatch = Stopwatch.StartNew();
            var searchId = Guid.NewGuid().ToString("N")[..8];

            _logger.LogInformation("【{SearchId}】开始轻量级搜索: 实体{Entity}, 返回类型{ResultType}",
                searchId, typeof(T).Name, typeof(TResult).Name);

            try
            {
                // 构建基础查询
                var baseQuery = _db.Queryable<T>();

                // 应用过滤条件
                baseQuery = ApplyFilters(baseQuery, request.Filters);

                // 应用搜索条件
                if (!string.IsNullOrWhiteSpace(request.Keyword))
                {
                    var searchAnalysis = AnalyzeSearchPattern(request.Keyword);
                    if (searchAnalysis.HasSearchContent)
                    {
                        var searchFields = request.FieldWeights?.Keys.ToList() ?? GetDefaultSearchFields();
                        var searchConditions = BuildSearchConditions(searchAnalysis, searchFields);
                        if (searchConditions.Any())
                        {
                            baseQuery = baseQuery.Where(searchConditions);
                        }
                    }
                }

                // 应用字段选择 - 在数据库层面进行字段选择
                var finalQuery = baseQuery.Select(selector);

                // 应用排序
                finalQuery = ApplyOrderByForLightweight(finalQuery, request.OrderBy, request.IsDescending);

                // 执行查询获取轻量级数据
                var totalCount = await finalQuery.CountAsync();
                var lightweightData = await finalQuery.ToPageListAsync(request.PageIndex, request.PageSize);

                // 为了计算匹配度，需要查询完整的实体数据
                List<T> fullDataForScoring;
                if (!string.IsNullOrWhiteSpace(request.Keyword))
                {
                    var fullResult = await SearchWithNativeSqlAsync(request);
                    fullDataForScoring = fullResult.Data;
                }
                else
                {
                    var fullQuery = BuildBaseQuery(request);
                    fullDataForScoring = await fullQuery.ToPageListAsync(request.PageIndex, request.PageSize);
                }

                // 计算匹配度
                var scoredItems = CalculateMatchScore(fullDataForScoring, request);

                // 将匹配度信息与轻量级数据关联
                var lightweightItems = AssociateMatchScores(lightweightData, scoredItems, selector);

                stopwatch.Stop();

                _logger.LogInformation("【{SearchId}】轻量级搜索完成: 找到 {Count} 条记录, 耗时 {TotalTime}ms",
                    searchId, lightweightItems.Count, stopwatch.ElapsedMilliseconds);

                return new SearchResult<TResult>
                {
                    Items = lightweightItems,
                    TotalCount = totalCount,
                    Keyword = request.Keyword,
                    FieldWeights = request.FieldWeights,
                    PageIndex = request.PageIndex,
                    PageSize = request.PageSize,
                    ResponseTime = stopwatch.ElapsedMilliseconds,
                    SearchId = searchId
                };
            }
            catch (Exception ex)
            {
                stopwatch.Stop();
                _logger.LogError(ex, "【{SearchId}】轻量级搜索失败", searchId);
                throw;
            }
        }

        /// <summary>
        /// 将匹配度信息与轻量级数据关联
        /// </summary>
        private List<SearchResultItem<TResult>> AssociateMatchScores<TResult>(
            List<TResult> lightweightData,
            List<SearchResultItem<T>> scoredItems,
            Expression<Func<T, TResult>> selector) where TResult : class, new()
        {
            var result = new List<SearchResultItem<TResult>>();

            // 构建一个字典来快速查找匹配度信息
            var scoreDict = new Dictionary<int, SearchResultItem<T>>();

            foreach (var scoredItem in scoredItems)
            {
                var id = GetEntityId(scoredItem.Data);
                if (id > 0)
                {
                    scoreDict[id] = scoredItem;
                }
            }

            // 关联匹配度信息
            foreach (var lightItem in lightweightData)
            {
                var id = GetEntityId(lightItem);
                if (id > 0 && scoreDict.TryGetValue(id, out var scoredItem))
                {
                    result.Add(new SearchResultItem<TResult>
                    {
                        Data = lightItem,
                        MatchScore = scoredItem.MatchScore,
                        MatchFields = scoredItem.MatchFields
                    });
                }
                else
                {
                    result.Add(new SearchResultItem<TResult>
                    {
                        Data = lightItem,
                        MatchScore = 0,
                        MatchFields = new List<MatchFieldInfo>()
                    });
                }
            }

            return result.OrderByDescending(x => x.MatchScore).ToList();
        }

        /// <summary>
        /// 获取实体ID（通过反射）
        /// </summary>
        private int GetEntityId<TEntity>(TEntity entity)
        {
            if (entity == null) return 0;

            var idProperty = typeof(TEntity).GetProperty("Id");
            if (idProperty != null && idProperty.PropertyType == typeof(int))
            {
                return (int)(idProperty.GetValue(entity) ?? 0);
            }

            return 0;
        }

        /// <summary>
        /// 获取实体可搜索字段信息
        /// </summary>
        /// <returns>可搜索字段信息列表，按权重降序排列</returns>
        public List<FieldInfo> GetSearchableFields()
        {
            var entityType = typeof(T);
            var properties = entityType.GetProperties();
            var searchableFields = new List<FieldInfo>();

            foreach (var prop in properties)
            {
                var fieldInfo = new FieldInfo
                {
                    FieldName = prop.Name,
                    DisplayName = GetDisplayName(prop),
                    DataType = prop.PropertyType.Name,
                    IsSearchable = prop.PropertyType == typeof(string),
                    DefaultWeight = GetDefaultWeight(prop.Name),
                    Description = GetFieldDescription(prop),
                    CanFilter = true,
                    CanSort = true
                };

                searchableFields.Add(fieldInfo);
            }

            return searchableFields
                .OrderByDescending(f => f.DefaultWeight)
                .ThenBy(f => f.FieldName)
                .ToList();
        }

        /// <summary>
        /// 验证字段配置
        /// </summary>
        /// <param name="fieldWeights">字段权重配置</param>
        /// <param name="returnFields">返回字段列表</param>
        /// <returns>验证结果</returns>
        public (bool IsValid, string Message) ValidateFieldConfig(
            Dictionary<string, int> fieldWeights,
            List<string> returnFields)
        {
            var allFields = GetSearchableFields();
            var validFieldNames = allFields.Select(f => f.FieldName).ToList();

            // 验证搜索字段
            if (fieldWeights != null)
            {
                var invalidSearchFields = fieldWeights.Keys.Except(validFieldNames).ToList();
                if (invalidSearchFields.Any())
                {
                    return (false, $"无效的搜索字段: {string.Join(", ", invalidSearchFields)}");
                }
            }

            // 验证返回字段
            if (returnFields != null)
            {
                var invalidReturnFields = returnFields.Except(validFieldNames).ToList();
                if (invalidReturnFields.Any())
                {
                    return (false, $"无效的返回字段: {string.Join(", ", invalidReturnFields)}");
                }
            }

            return (true, "字段配置有效");
        }

        #region 私有方法 - 搜索逻辑

        /// <summary>
        /// 使用原生SQL进行搜索
        /// </summary>
        private async Task<(List<T> Data, int TotalCount)> SearchWithNativeSqlAsync(DynamicSearchRequest request)
        {
            var whereConditions = new List<string>();
            var parameters = new List<SugarParameter>();

            // 获取搜索字段
            var searchFields = request.FieldWeights?.Keys.ToList() ?? GetDefaultSearchFields();
            var validFields = ValidateSearchFields(searchFields);

            // 构建搜索条件
            if (!string.IsNullOrWhiteSpace(request.Keyword))
            {
                var searchAnalysis = AnalyzeSearchPattern(request.Keyword);
                var keywordConditions = new List<string>(); // 专门存放关键字相关条件

                // 符号分割的关键字条件
                foreach (var segment in searchAnalysis.SymbolSegments)
                {
                    var cleanSegment = Regex.Replace(segment, @"[^\u4e00-\u9fa5a-zA-Z0-9]", "");
                    if (!string.IsNullOrEmpty(cleanSegment))
                    {
                        var fieldConditions = validFields.Select(field =>
                        {
                            var paramName = $"@segment{parameters.Count}";
                            parameters.Add(new SugarParameter(paramName, $"%{cleanSegment}%"));
                            return $"{field} LIKE {paramName}";
                        });
                        // 每个片段内部使用 OR 连接不同字段
                        keywordConditions.Add($"({string.Join(" OR ", fieldConditions)})");
                    }
                }

                // 单字检索条件
                foreach (var singleChar in searchAnalysis.SingleChars)
                {
                    var charStr = singleChar.ToString();
                    var fieldConditions = validFields.Select(field =>
                    {
                        var paramName = $"@char{parameters.Count}";
                        parameters.Add(new SugarParameter(paramName, $"%{charStr}%"));
                        return $"{field} LIKE {paramName}";
                    });
                    // 每个单字内部使用 OR 连接不同字段
                    keywordConditions.Add($"({string.Join(" OR ", fieldConditions)})");
                }

                // 所有关键字条件使用 OR 连接
                if (keywordConditions.Any())
                {
                    whereConditions.Add($"({string.Join(" OR ", keywordConditions)})");
                }

            }

            // 构建过滤条件
            var filterConditions = BuildNativeFilterConditions(request.Filters, parameters);
            whereConditions.AddRange(filterConditions);

            // 构建完整SQL
            var whereClause = whereConditions.Any()
                ? "WHERE " + string.Join(" AND ", whereConditions)
                : "";

            var orderByClause = BuildNativeOrderByClause(request.OrderBy, request.IsDescending);

            var tableName = _db.EntityMaintenance.GetTableName(typeof(T));

            // 构建返回字段
            //var returnFields = BuildReturnFields(request.ReturnFields);

            // 先查询总数
            var countSql = $"SELECT COUNT(1) FROM {tableName} {whereClause}";
            var totalCount = await _db.Ado.GetIntAsync(countSql, parameters);

            // 再查询数据
            var offset = (request.PageIndex - 1) * request.PageSize;

            var dataSql = $@"
            SELECT * FROM (
                SELECT *, ROW_NUMBER() OVER ({orderByClause}) AS RowNumber 
                FROM {tableName} 
                {whereClause}
            ) AS Paginated
            WHERE Paginated.RowNumber > {offset} AND Paginated.RowNumber <= {offset + request.PageSize}
            {orderByClause}";

            var data = await _db.Ado.SqlQueryAsync<T>(dataSql, parameters);

            return (data, totalCount);
        }

        /// <summary>
        /// 构建返回字段列表
        /// </summary>
        private string BuildReturnFields(List<string> returnFields)
        {
            if (returnFields == null || !returnFields.Any())
            {
                return "*";
            }

            // 验证字段是否存在
            var validFields = ValidateReturnFields(returnFields);

            if (!validFields.Any())
            {
                return "*";
            }

            // 构建字段列表，处理可能的SQL注入和字段名转义
            var escapedFields = validFields.Select(field =>
            {
                // 如果字段包含空格、特殊字符或关键字，用方括号括起来
                if (field.Contains(" ") || IsSqlKeyword(field) || field.Contains("."))
                {
                    return $"[{field}]";
                }
                return field;
            });

            return string.Join(", ", escapedFields);
        }


        /// <summary>
        /// 验证返回字段的有效性
        /// </summary>
        private List<string> ValidateReturnFields(List<string> returnFields)
        {
            if (returnFields == null) return new List<string>();

            var entityType = typeof(T);
            var entityProperties = entityType.GetProperties(BindingFlags.Public | BindingFlags.Instance)
                .Select(p => p.Name)
                .ToList();

            // 过滤出实体类中存在的字段
            var validFields = returnFields
                .Where(field => entityProperties.Contains(field, StringComparer.OrdinalIgnoreCase))
                .ToList();

            // 记录无效字段警告
            var invalidFields = returnFields.Except(validFields, StringComparer.OrdinalIgnoreCase).ToList();
            if (invalidFields.Any())
            {
                _logger.LogWarning("发现无效的返回字段: {InvalidFields}", string.Join(", ", invalidFields));
            }

            return validFields;
        }

        /// <summary>
        /// 检查是否为SQL关键字
        /// </summary>
        private bool IsSqlKeyword(string word)
        {
            var sqlKeywords = new HashSet<string>(StringComparer.OrdinalIgnoreCase)
            {
                "SELECT", "FROM", "WHERE", "INSERT", "UPDATE", "DELETE", "CREATE", "DROP",
                "ALTER", "TABLE", "VIEW", "INDEX", "PRIMARY", "KEY", "FOREIGN", "REFERENCES",
                "AND", "OR", "NOT", "LIKE", "IN", "BETWEEN", "IS", "NULL", "ORDER", "BY",
                "GROUP", "HAVING", "JOIN", "INNER", "LEFT", "RIGHT", "OUTER", "ON", "AS",
                "DISTINCT", "TOP", "COUNT", "SUM", "AVG", "MIN", "MAX", "UNION", "ALL"
            };

            return sqlKeywords.Contains(word);
        }

        /// <summary>
        /// 构建基础查询
        /// </summary>
        private ISugarQueryable<T> BuildBaseQuery(DynamicSearchRequest request)
        {
            var query = _db.Queryable<T>();

            // 应用过滤条件
            query = ApplyFilters(query, request.Filters);

            // 应用排序
            query = ApplyOrderBy(query, request.OrderBy, request.IsDescending);

            return query;
        }

        /// <summary>
        /// 构建原生过滤条件
        /// </summary>
        private List<string> BuildNativeFilterConditions(List<SearchFilter> filters, List<SugarParameter> parameters)
        {
            var conditions = new List<string>();

            if (filters == null) return conditions;

            foreach (var filter in filters)
            {
                var condition = filter.Operator?.ToLower() switch
                {
                    "eq" => BuildNativeCondition(filter, "=", parameters),
                    "neq" => BuildNativeCondition(filter, "!=", parameters),
                    "contains" => BuildNativeLikeCondition(filter, "%", "%", parameters),
                    "startswith" => BuildNativeLikeCondition(filter, "", "%", parameters),
                    "endswith" => BuildNativeLikeCondition(filter, "%", "", parameters),
                    "gt" => BuildNativeCondition(filter, ">", parameters),
                    "gte" => BuildNativeCondition(filter, ">=", parameters),
                    "lt" => BuildNativeCondition(filter, "<", parameters),
                    "lte" => BuildNativeCondition(filter, "<=", parameters),
                    "in" => BuildNativeInCondition(filter, parameters),
                    _ => null
                };

                if (!string.IsNullOrEmpty(condition))
                {
                    conditions.Add(condition);
                }
            }

            return conditions;
        }

        private string BuildNativeCondition(SearchFilter filter, string op, List<SugarParameter> parameters)
        {
            var paramName = $"@filter{parameters.Count}";
            parameters.Add(new SugarParameter(paramName, filter.Value));
            return $"{filter.Field} {op} {paramName}";
        }

        private string BuildNativeLikeCondition(SearchFilter filter, string prefix, string suffix, List<SugarParameter> parameters)
        {
            var paramName = $"@filter{parameters.Count}";
            parameters.Add(new SugarParameter(paramName, $"{prefix}{filter.Value}{suffix}"));
            return $"{filter.Field} LIKE {paramName}";
        }

        private string BuildNativeInCondition(SearchFilter filter, List<SugarParameter> parameters)
        {
            if (filter.Values == null || !filter.Values.Any())
                return null;

            var paramNames = new List<string>();
            foreach (var value in filter.Values)
            {
                var paramName = $"@filter{parameters.Count}";
                parameters.Add(new SugarParameter(paramName, value));
                paramNames.Add(paramName);
            }

            return $"{filter.Field} IN ({string.Join(",", paramNames)})";
        }

        #endregion

        #region 私有方法 - 匹配度计算

        /// <summary>
        /// 在应用层计算匹配度
        /// </summary>
        private List<SearchResultItem<T>> CalculateMatchScore(List<T> data, DynamicSearchRequest request)
        {
            if (string.IsNullOrWhiteSpace(request.Keyword))
            {
                // 无关键词时，所有记录匹配度为0
                return data.Select(item => new SearchResultItem<T>
                {
                    Data = item,
                    MatchScore = 0
                }).ToList();
            }

            var searchAnalysis = AnalyzeSearchPattern(request.Keyword);
            var searchFields = request.FieldWeights?.Keys.ToList() ?? GetDefaultSearchFields();
            var fieldWeights = request.FieldWeights ?? GetDefaultFieldWeights(searchFields);

            var scoredItems = data.Select(item =>
            {
                var matchResult = CalculateItemMatchScore(item, searchAnalysis, searchFields, fieldWeights);

                return new SearchResultItem<T>
                {
                    Data = item,
                    MatchScore = matchResult.TotalScore,
                    MatchFields = matchResult.MatchFields
                };
            })
            .Where(item => item.MatchScore > 0) // 只保留有匹配的记录
            .OrderByDescending(item => item.MatchScore)
            .ThenByDescending(item => GetCreateTime(item.Data))
            .ToList();

            return scoredItems;
        }

        /// <summary>
        /// 计算单个项的匹配度详情
        /// </summary>
        private (int TotalScore, List<MatchFieldInfo> MatchFields) CalculateItemMatchScore(
            T item,
            SearchAnalysis analysis,
            List<string> searchFields,
            Dictionary<string, int> fieldWeights)
        {
            int totalScore = 0;
            var matchFields = new List<MatchFieldInfo>();

            foreach (var field in searchFields)
            {
                var fieldValue = GetFieldValue(item, field);
                if (string.IsNullOrEmpty(fieldValue))
                    continue;

                var weight = fieldWeights.ContainsKey(field) ? fieldWeights[field] : GetDefaultWeight(field);
                int fieldScore = 0;
                var fieldMatchReasons = new List<string>();

                // 符号分割关键字匹配
                foreach (var segment in analysis.SymbolSegments)
                {
                    var cleanSegment = Regex.Replace(segment, @"[^\u4e00-\u9fa5a-zA-Z0-9]", "");
                    if (!string.IsNullOrEmpty(cleanSegment) && fieldValue.Contains(cleanSegment))
                    {
                        int segmentScore = weight;

                        if (fieldValue.Equals(cleanSegment))
                        {
                            segmentScore += 15;
                            fieldMatchReasons.Add($"完全匹配 '{cleanSegment}'");
                        }
                        else if (fieldValue.StartsWith(cleanSegment))
                        {
                            segmentScore += 10;
                            fieldMatchReasons.Add($"开头匹配 '{cleanSegment}'");
                        }
                        else if (fieldValue.EndsWith(cleanSegment))
                        {
                            segmentScore += 5;
                            fieldMatchReasons.Add($"结尾匹配 '{cleanSegment}'");
                        }
                        else
                        {
                            fieldMatchReasons.Add($"包含 '{cleanSegment}'");
                        }

                        fieldScore += segmentScore;
                    }
                }

                // 单字匹配
                foreach (var singleChar in analysis.SingleChars)
                {
                    int count = fieldValue.Count(c => c == singleChar);
                    if (count > 0)
                    {
                        int charScore = count * (int)(weight * 0.3);

                        if (fieldValue.StartsWith(singleChar.ToString()))
                        {
                            charScore += weight;
                            fieldMatchReasons.Add($"开头单字 '{singleChar}'");
                        }
                        else
                        {
                            fieldMatchReasons.Add($"包含单字 '{singleChar}'({count}次)");
                        }

                        fieldScore += charScore;
                    }
                }

                if (fieldScore > 0)
                {
                    totalScore += fieldScore;
                    matchFields.Add(new MatchFieldInfo
                    {
                        FieldName = field,
                        FieldValue = GetDisplayFieldValue(fieldValue),
                        Score = fieldScore,
                        MatchReason = string.Join("; ", fieldMatchReasons)
                    });
                }
            }

            // 按分数排序匹配字段
            matchFields = matchFields.OrderByDescending(m => m.Score).ToList();

            return (totalScore, matchFields);
        }

        /// <summary>
        /// 获取显示用的字段值（截断过长的内容）
        /// </summary>
        private string GetDisplayFieldValue(string fieldValue)
        {
            if (string.IsNullOrEmpty(fieldValue))
                return fieldValue;

            // 如果字段值过长，截断显示
            return fieldValue.Length > 50 ? fieldValue.Substring(0, 50) + "..." : fieldValue;
        }

        #endregion

        #region 私有方法 - 辅助功能

        /// <summary>
        /// 应用过滤条件
        /// </summary>
        private ISugarQueryable<T> ApplyFilters(ISugarQueryable<T> query, List<SearchFilter> filters)
        {
            if (filters == null || !filters.Any())
                return query;

            foreach (var filter in filters)
            {
                query = filter.Operator?.ToLower() switch
                {
                    "eq" => query.Where($"{filter.Field} = @Value", new { filter.Value }),
                    "neq" => query.Where($"{filter.Field} != @Value", new { filter.Value }),
                    "contains" => query.Where($"{filter.Field} LIKE '%' + @Value + '%'", new { filter.Value }),
                    "startswith" => query.Where($"{filter.Field} LIKE @Value + '%'", new { filter.Value }),
                    "endswith" => query.Where($"{filter.Field} LIKE '%' + @Value", new { filter.Value }),
                    "gt" => query.Where($"{filter.Field} > @Value", new { filter.Value }),
                    "gte" => query.Where($"{filter.Field} >= @Value", new { filter.Value }),
                    "lt" => query.Where($"{filter.Field} < @Value", new { filter.Value }),
                    "lte" => query.Where($"{filter.Field} <= @Value", new { filter.Value }),
                    "in" => ApplyInFilter(query, filter),
                    _ => query
                };
            }

            return query;
        }

        /// <summary>
        /// 使用SqlSugar条件构建器构建搜索条件
        /// </summary>
        private List<IConditionalModel> BuildSearchConditions(SearchAnalysis analysis, List<string> searchFields)
        {
            var conditionalModels = new List<IConditionalModel>();

            // 获取有效的搜索字段
            var validFields = ValidateSearchFields(searchFields);

            if (!validFields.Any())
                return conditionalModels;

            // 1. 符号分割的关键字条件
            foreach (var segment in analysis.SymbolSegments)
            {
                var cleanSegment = Regex.Replace(segment, @"[^\u4e00-\u9fa5a-zA-Z0-9]", "");
                if (!string.IsNullOrEmpty(cleanSegment))
                {
                    var segmentGroup = new List<IConditionalModel>();

                    foreach (var field in validFields)
                    {
                        segmentGroup.Add(new ConditionalModel
                        {
                            FieldName = field,
                            ConditionalType = ConditionalType.Like,
                            FieldValue = $"%{cleanSegment}%"
                        });
                    }

                    if (segmentGroup.Count > 1)
                    {
                        conditionalModels.Add(new ConditionalCollections
                        {
                            ConditionalList = new List<KeyValuePair<WhereType, ConditionalModel>>(
                                segmentGroup.Select((model, index) =>
                                    new KeyValuePair<WhereType, ConditionalModel>(
                                        index == 0 ? WhereType.And : WhereType.Or,
                                        (ConditionalModel)model))
                            )
                        });
                    }
                    else if (segmentGroup.Count == 1)
                    {
                        conditionalModels.Add(segmentGroup[0]);
                    }
                }
            }

            return conditionalModels;
        }

        /// <summary>
        /// 应用IN过滤条件
        /// </summary>
        private ISugarQueryable<T> ApplyInFilter(ISugarQueryable<T> query, SearchFilter filter)
        {
            if (filter.Values == null || !filter.Values.Any())
                return query;

            var valueList = string.Join(",", filter.Values.Select(v => $"'{v}'"));
            return query.Where($"{filter.Field} IN ({valueList})");
        }

        /// <summary>
        /// 应用排序
        /// </summary>
        private ISugarQueryable<T> ApplyOrderBy(ISugarQueryable<T> query, string orderBy, bool isDescending)
        {
            if (string.IsNullOrWhiteSpace(orderBy))
            {
                // 默认按主键或创建时间排序
                var entityType = typeof(T);
                var idProperty = entityType.GetProperty("Id") ?? entityType.GetProperty("CreateTime");
                if (idProperty != null)
                {
                    orderBy = idProperty.Name;
                }
            }

            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                return isDescending
                    ? query.OrderBy($"{orderBy} DESC")
                    : query.OrderBy($"{orderBy} ASC");
            }

            return query;
        }

        /// <summary>
        /// 为轻量级搜索应用排序
        /// </summary>
        private ISugarQueryable<TResult> ApplyOrderByForLightweight<TResult>(
            ISugarQueryable<TResult> query,
            string orderBy,
            bool isDescending) where TResult : class, new()
        {
            if (string.IsNullOrWhiteSpace(orderBy))
            {
                // 检查结果类型是否有Id或CreateTime字段
                var resultType = typeof(TResult);
                var idProperty = resultType.GetProperty("Id") ?? resultType.GetProperty("CreateTime");
                if (idProperty != null)
                {
                    orderBy = idProperty.Name;
                }
                else
                {
                    // 如果没有默认排序字段，返回原查询
                    return query;
                }
            }

            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                // 验证排序字段是否存在于结果类型中
                var resultType = typeof(TResult);
                var orderByProperty = resultType.GetProperty(orderBy);
                if (orderByProperty != null)
                {
                    return isDescending
                        ? query.OrderBy($"{orderBy} DESC")
                        : query.OrderBy($"{orderBy} ASC");
                }
                else
                {
                    _logger.LogWarning("排序字段 {OrderBy} 在返回类型 {ResultType} 中不存在", orderBy, resultType.Name);
                }
            }

            return query;
        }

        /// <summary>
        /// 构建原生排序子句
        /// </summary>
        private string BuildNativeOrderByClause(string orderBy, bool isDescending)
        {
            if (string.IsNullOrWhiteSpace(orderBy))
                //return "ORDER BY Id DESC";
                return "";

            return $"ORDER BY {orderBy} {(isDescending ? "DESC" : "ASC")}";
        }

        /// <summary>
        /// 分析搜索模式
        /// </summary>
        private SearchAnalysis AnalyzeSearchPattern(string keyword)
        {
            var analysis = new SearchAnalysis { OriginalKeyword = keyword };

            // 检查是否包含特殊符号
            var specialSymbols = new[] { ' ', ',', '，', ';', '；', '、', '/', '\\', '|', '-', '_' };
            if (keyword.Any(c => specialSymbols.Contains(c)))
            {
                analysis.HasSpecialSymbols = true;
                analysis.SymbolSegments = keyword.Split(specialSymbols, StringSplitOptions.RemoveEmptyEntries)
                                               .Select(s => s.Trim())
                                               .Where(s => !string.IsNullOrEmpty(s))
                                               .ToList();
            }

            // 清理关键词并提取单字
            var cleanKeyword = Regex.Replace(keyword, @"[^\u4e00-\u9fa5a-zA-Z0-9]", "");

            if (!string.IsNullOrEmpty(cleanKeyword))
            {
                foreach (char c in cleanKeyword)
                {
                    analysis.SingleChars.Add(c);
                }

                analysis.IsSingleChar = true;

                // 如果没有特殊符号但有关键词，也作为符号分割段
                if (cleanKeyword.Length > 1 && !analysis.HasSpecialSymbols)
                {
                    analysis.SymbolSegments.Add(cleanKeyword);
                }
            }

            // 去重
            analysis.SingleChars = analysis.SingleChars.Distinct().ToList();
            analysis.SymbolSegments = analysis.SymbolSegments.Distinct().ToList();

            return analysis;
        }

        /// <summary>
        /// 获取创建时间（用于排序）
        /// </summary>
        private DateTime GetCreateTime(T item)
        {
            var createTimeProperty = typeof(T).GetProperty("CreateTime");
            if (createTimeProperty != null)
            {
                return (DateTime)(createTimeProperty.GetValue(item) ?? DateTime.MinValue);
            }
            return DateTime.MinValue;
        }

        /// <summary>
        /// 获取字段值
        /// </summary>
        private string GetFieldValue(T item, string fieldName)
        {
            var property = typeof(T).GetProperty(fieldName);
            return property?.GetValue(item) as string;
        }

        /// <summary>
        /// 获取默认搜索字段
        /// </summary>
        private List<string> GetDefaultSearchFields()
        {
            return typeof(T).GetProperties()
                .Where(p => p.PropertyType == typeof(string))
                .Select(p => p.Name)
                .Take(5)
                .ToList();
        }

        /// <summary>
        /// 获取默认字段权重
        /// </summary>
        private Dictionary<string, int> GetDefaultFieldWeights(List<string> fields)
        {
            var weights = new Dictionary<string, int>();
            foreach (var field in fields)
            {
                weights[field] = GetDefaultWeight(field);
            }
            return weights;
        }

        /// <summary>
        /// 验证字段有效性
        /// </summary>
        private List<string> ValidateFields(List<string> fields)
        {
            var validFields = typeof(T).GetProperties()
                .Select(p => p.Name)
                .ToList();

            return fields.Intersect(validFields).ToList();
        }

        /// <summary>
        /// 验证搜索字段
        /// </summary>
        private List<string> ValidateSearchFields(List<string> searchFields)
        {
            var validFields = typeof(T).GetProperties()
                .Where(p => p.PropertyType == typeof(string))
                .Select(p => p.Name)
                .ToList();

            return searchFields.Intersect(validFields).ToList();
        }

        /// <summary>
        /// 获取默认权重
        /// </summary>
        private int GetDefaultWeight(string fieldName)
        {
            return fieldName.ToLower() switch
            {
                var name when name.Contains("name") => 10,
                var name when name.Contains("title") => 10,
                var name when name.Contains("code") => 8,
                var name when name.Contains("no") => 8,
                var name when name.Contains("desc") => 6,
                var name when name.Contains("content") => 6,
                var name when name.Contains("remark") => 5,
                var name when name.Contains("phone") => 4,
                var name when name.Contains("tel") => 4,
                var name when name.Contains("address") => 3,
                var name when name.Contains("email") => 3,
                _ => 5
            };
        }

        /// <summary>
        /// 获取显示名称
        /// </summary>
        private string GetDisplayName(System.Reflection.PropertyInfo property)
        {
            var displayAttr = property.GetCustomAttribute<System.ComponentModel.DataAnnotations.DisplayAttribute>();
            return displayAttr?.Name ?? property.Name;
        }

        /// <summary>
        /// 获取字段描述
        /// </summary>
        private string GetFieldDescription(System.Reflection.PropertyInfo property)
        {
            var descriptionAttr = property.GetCustomAttribute<System.ComponentModel.DescriptionAttribute>();
            return descriptionAttr?.Description ?? string.Empty;
        }

        #endregion
    }
}