namespace BZPT.Repositories
{
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Threading.Tasks;
    using DevExtreme.AspNet.Data.ResponseModel;
    using DevExtreme.AspNet.Data;
    using SqlSugar;
    using NPlatform.Result;

    public static class SqlSugarDataSourceLoader
    {
        // Main method to load data
        public static async Task<ListResult<T>> LoadAsync<T>(this ISugarQueryable<T> source, DataSourceLoadOptions options) where T : class, new()
        {
            // 1. Apply Filtering
            var filteredQuery = ApplyFilter(source, options.Filter as IList<object>);

            // 2. Calculate Total Summaries (before paging, after filtering)
            object[]? totalSummaries = null;
            if (options.TotalSummary != null && options.TotalSummary.Any())
            {
                totalSummaries = await CalculateTotalSummariesAsync(filteredQuery, options.TotalSummary);
            }

            // 3. Calculate Total Count (before paging, after filtering)
            long totalCount = -1; // Use long for potentially large counts
            if (options.RequireTotalCount)
            {
                // Use CountAsync for efficiency
                totalCount = await filteredQuery.CountAsync();
            }

            // 4. Apply Sorting
            var sortedQuery = ApplySorting(filteredQuery, options.Sort);

            // 5. Apply Paging
            var pagedQuery = ApplyPaging(sortedQuery, options.Skip, options.Take);

            // --- Grouping Check ---
            // Grouping is complex to implement correctly and efficiently with SqlSugar
            // matching DevExtreme's expected output structure. We explicitly don't support it here.
            if (options.Group != null && options.Group.Any())
            {
                // Option 1: Throw an exception
                throw new NotImplementedException("Grouping is not supported by this SqlSugarDataSourceLoader implementation due to its complexity. Implement custom grouping logic if required.");

                // Option 2: Return an empty grouped result (less disruptive but might hide issues)
                // return new LoadResult {
                //     groupCount = 0,
                //     totalCount = totalCount, // Might still be relevant
                //     summary = totalSummaries,
                //     data = new List<Group>() // Return empty group structure
                // };
            }

            // 6. Execute Query to Get Data
            List<T> data = await pagedQuery.ToListAsync();

            // 7. Assemble Result
            return new ListResult<T>(data, totalCount, totalSummaries, 200);
        }

        // --- Private Helper Methods ---

        #region Filtering

        private static ISugarQueryable<T> ApplyFilter<T>(ISugarQueryable<T> query, IList<object>? filter) where T : class, new()
        {
            if (filter == null || filter.Count == 0)
                return query;

            try
            {
                var parameter = Expression.Parameter(typeof(T), "x");
                var filterExpression = ParseFilter(filter, parameter);
                if (filterExpression != null)
                {
                    var lambda = Expression.Lambda<Func<T, bool>>(filterExpression, parameter);
                    return query.Where(lambda);
                }
            }
            catch (Exception ex)
            {
                // Log the error details
                Console.WriteLine($"Error parsing DevExtreme filter: {ex}");
                // Decide handling: throw, return original query, or return empty set
                // Throwing is often better to signal a malformed request
                throw new ArgumentException("Failed to parse the provided filter criteria.", nameof(filter), ex);
            }

            return query;
        }

        private static Expression? ParseFilter(IList<object> filter, ParameterExpression parameter)
        {
            // Format: [ "field", "operator", value ] or [ filter1, "and/or", filter2, ... ] or [ "!", filter ]

            if (filter.Count > 0 && filter[0] is IList<object> nestedFilter) // Nested filters or logical operator
            {
                var expressions = new List<Expression>();
                string? logicalOperator = null;

                foreach (var item in filter)
                {
                    if (item is IList<object> subFilter)
                    {
                        var parsed = ParseFilter(subFilter, parameter);
                        if (parsed != null)
                            expressions.Add(parsed);
                    }
                    else if (item is string op && (op.ToLowerInvariant() == "and" || op.ToLowerInvariant() == "or"))
                    {
                        if (logicalOperator != null && logicalOperator != op.ToLowerInvariant())
                            throw new ArgumentException("Mixing 'and' and 'or' at the same level is not supported without explicit nesting.");
                        logicalOperator = op.ToLowerInvariant();
                    }
                }

                if (!expressions.Any()) return null;

                logicalOperator ??= "and"; // Default to AND if not specified

                Expression? combined = expressions[0];
                for (int i = 1; i < expressions.Count; i++)
                {
                    combined = logicalOperator == "or"
                        ? Expression.OrElse(combined, expressions[i])
                        : Expression.AndAlso(combined, expressions[i]);
                }
                return combined;
            }
            else if (filter.Count == 2 && filter[0] is string unaryOp && unaryOp == "!") // Unary 'not' operator
            {
                if (filter[1] is IList<object> subFilter)
                {
                    var parsed = ParseFilter(subFilter, parameter);
                    return parsed != null ? Expression.Not(parsed) : null;
                }
                throw new ArgumentException("Invalid '!' operator usage in filter.");
            }
            else if (filter.Count == 3 && filter[0] is string fieldName && filter[1] is string operation) // Simple condition
            {
                object? value = filter[2];
                MemberExpression member = Expression.PropertyOrField(parameter, fieldName);
                Expression valueExpression = CreateConstantExpression(value, member.Type);

                return BuildComparisonExpression(member, operation, valueExpression, parameter);
            }
            else
            {
                throw new ArgumentException($"Invalid filter format: {string.Join(",", filter)}");
            }
        }


        private static Expression CreateConstantExpression(object? value, Type targetType)
        {
            // Handle potential type mismatches, especially with nulls and enums
            if (value == null)
            {
                return Expression.Constant(null, targetType);
            }

            Type underlyingTargetType = Nullable.GetUnderlyingType(targetType) ?? targetType;
            object? convertedValue;

            try
            {
                if (underlyingTargetType.IsEnum)
                {
                    convertedValue = Enum.Parse(underlyingTargetType, value.ToString()!, true);
                }
                else if (value is IConvertible)
                {
                    // Handle DateTimeOffset specifically if necessary, depending on how DevExtreme sends it
                    if (underlyingTargetType == typeof(DateTimeOffset) && value is string s && DateTimeOffset.TryParse(s, out var dto))
                    {
                        convertedValue = dto;
                    }
                    else if (underlyingTargetType == typeof(DateTime) && value is string strDt && DateTime.TryParse(strDt, out var dt))
                    {
                        // Add specific DateTime parsing if needed (e.g., handle specific formats/kinds)
                        convertedValue = dt;
                    }
                    else if (underlyingTargetType == typeof(Guid) && value is string strGuid && Guid.TryParse(strGuid, out var guid))
                    {
                        convertedValue = guid;
                    }
                    else
                    {
                        convertedValue = Convert.ChangeType(value, underlyingTargetType, System.Globalization.CultureInfo.InvariantCulture);
                    }
                }
                else if (underlyingTargetType == value.GetType()) // 如果类型已完全匹配
                {
                    convertedValue = value;
                } 
                // 如果目标类型不是集合，但传入的值是集合 (可能用于 'anyof')
                  // 或者类型不匹配且值不是 IConvertible，我们直接使用原始值。
                  // BuildComparisonExpression 中的 'anyof' 分支会检查它是否真的是一个集合。
                  // 其他标量操作符如果收到集合，会在尝试比较时因类型不匹配而自然失败（或需要更复杂的处理）。
                  // 这种方式是妥协，避免 CreateConstantExpression 需要知道操作符。
                else if (value is IEnumerable && !(value is string)) // 如果是集合 (且不是字符串，因为字符串也是IEnumerable)
                {
                    // 对于 'anyof' 操作符，我们希望传递原始集合
                    // 对于其他操作符，如果到这里，说明类型不匹配且不是IConvertible，
                    // 传递原始值，后续比较可能会失败，这是预期的。
                    convertedValue = value; // 直接使用原始集合对象
                                            // 注意：此时的 targetType 可能与 value.GetType() 不同。
                                            // Expression.Constant(convertedValue, value.GetType()) 可能更安全，
                                            // 但为了与现有逻辑兼容（期望返回 Expression.Constant(..., targetType)），我们暂时这样。
                                            // 如果后续标量比较需要，可能需要调整。
                                            // 更稳妥的做法是，如果CreateConstantExpression感知到是为anyof准备的，则直接返回Expression.Constant(value, value.GetType())
                                            // 但这需要修改 ParseFilter 给 CreateConstantExpression 传递更多信息。
                                            // 目前，我们让它通过，并寄希望于 targetType 兼容或 BuildComparisonExpression 正确处理。
                    return Expression.Constant(convertedValue, value.GetType()); // ***** 使用值的实际类型创建常量 *****
                }
                else // 其他情况（非IConvertible，类型不匹配，也不是集合）
                {
                    throw new InvalidCastException($"值 '{value}' (类型: {value.GetType().Name}) 不是IConvertible且与目标类型 '{underlyingTargetType.Name}' (字段: 不匹配，也不是一个可直接用于集合操作的 IEnumerable。");
                }
            }
            catch (Exception ex)
            {
                throw new InvalidCastException($"Cannot convert filter value '{value}' (Type: {value.GetType().Name}) to target property type '{targetType.Name}'. Field: [Implicit FieldName]", ex);
            }

            return Expression.Constant(convertedValue, targetType); // Use the original targetType (which might be Nullable<T>)
        }

        private static Expression BuildComparisonExpression(MemberExpression member, string operation, Expression valueExpression, ParameterExpression parameter)
        {
            var memberType = member.Type;
            var valueType = valueExpression.Type;

            // Ensure types are compatible for comparison, potentially adjusting for nullables
            Expression left = member;
            Expression right = valueExpression;

            // If one side is nullable and the other isn't, we might need a conversion
            // Example: Comparing Nullable<int> property with int constant
            if (memberType != valueType)
            {
                if (Nullable.GetUnderlyingType(memberType) == valueType && valueType.IsValueType) // Property is Nullable<T>, Value is T
                {
                    // Promote value to Nullable<T>
                    right = Expression.Convert(valueExpression, memberType);
                }
                else if (memberType == Nullable.GetUnderlyingType(valueType) && memberType.IsValueType) // Property is T, Value is Nullable<T>
                {
                    // This scenario is less common with constants, but possible if the filter value was somehow nullable
                    // We might need to access the .Value property of the nullable constant, but need null check
                    right = Expression.Property(valueExpression, "Value"); // Potential NullReferenceException if value is null
                                                                           // Safer: Introduce null check? Or assume filter ensures non-null for comparison?
                                                                           // For simplicity here, assume non-null if comparing with non-nullable property.
                }
                // Add more sophisticated type matching/conversion if needed
            }


            switch (operation.ToLowerInvariant())
            {
                case "=":
                    return Expression.Equal(left, right);
                case "<>":
                    return Expression.NotEqual(left, right);
                case ">":
                    return Expression.GreaterThan(left, right);
                case ">=":
                    return Expression.GreaterThanOrEqual(left, right);
                case "<":
                    return Expression.LessThan(left, right);
                case "<=":
                    return Expression.LessThanOrEqual(left, right);
                case "contains": // String.Contains, or check if collection contains value
                    if (left.Type == typeof(string))
                    {
                        MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) })
                                                   ?? throw new InvalidOperationException("String.Contains method not found.");
                        // Ensure the value is a string constant for String.Contains
                        Expression stringValue = right.NodeType == ExpressionType.Constant && ((ConstantExpression)right).Value is string
                                                ? right
                                                : Expression.Call(right, typeof(object).GetMethod("ToString", Type.EmptyTypes)!); // Or handle error if not convertible
                        return Expression.Call(left, containsMethod, stringValue);
                    }
                    else if (typeof(IEnumerable).IsAssignableFrom(left.Type) && left.Type.IsGenericType) // e.g. List<int>.Contains(value)
                    {
                        // Assuming 'right' is the element type
                        var elementType = left.Type.GetGenericArguments()[0];
                        MethodInfo containsMethod = typeof(Enumerable).GetMethods()
                             .Single(m => m.Name == "Contains" && m.GetParameters().Length == 2)
                             .MakeGenericMethod(elementType);
                        return Expression.Call(containsMethod, left, right); // Might need type adjustment for 'right'
                    }
                    throw new NotSupportedException($"'contains' operator is not supported for type {left.Type}.");

                case "notcontains":
                    if (left.Type == typeof(string))
                    {
                        MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) })!;
                        Expression stringValue = right.NodeType == ExpressionType.Constant && ((ConstantExpression)right).Value is string
                                               ? right
                                               : Expression.Call(right, typeof(object).GetMethod("ToString", Type.EmptyTypes)!);
                        return Expression.Not(Expression.Call(left, containsMethod, stringValue));
                    }
                    // Add Enumerable logic similar to 'contains' if needed
                    throw new NotSupportedException($"'notcontains' operator is not supported for type {left.Type}.");

                case "startswith":
                    if (left.Type == typeof(string))
                    {
                        MethodInfo startsWithMethod = typeof(string).GetMethod("StartsWith", new[] { typeof(string) })!;
                        Expression stringValue = right.NodeType == ExpressionType.Constant && ((ConstantExpression)right).Value is string
                                                 ? right
                                                 : Expression.Call(right, typeof(object).GetMethod("ToString", Type.EmptyTypes)!);
                        return Expression.Call(left, startsWithMethod, stringValue);
                    }
                    throw new NotSupportedException($"'startswith' operator is not supported for type {left.Type}.");

                case "endswith":
                    if (left.Type == typeof(string))
                    {
                        MethodInfo endsWithMethod = typeof(string).GetMethod("EndsWith", new[] { typeof(string) })!;
                        Expression stringValue = right.NodeType == ExpressionType.Constant && ((ConstantExpression)right).Value is string
                                                ? right
                                                : Expression.Call(right, typeof(object).GetMethod("ToString", Type.EmptyTypes)!);
                        return Expression.Call(left, endsWithMethod, stringValue);
                    }
                    throw new NotSupportedException($"'endswith' operator is not supported for type {left.Type}.");

                case "anyof":
                case "in":
                    // 此时，valueExpression 应该是一个 Expression.Constant，其 .Value 是一个 IEnumerable 集合。
                    // 这是因为 CreateConstantExpression 在遇到 IEnumerable 时，返回了 Expression.Constant(value, value.GetType())。
                    if (!(valueExpression is ConstantExpression constantCollectionExpr))
                    {
                        throw new ArgumentException(
                            $"对于 '{operation}' 操作符，其值表达式必须是一个包含集合的 ConstantExpression。" +
                            $"实际接收到的表达式类型: {valueExpression.GetType().FullName}。这通常是 CreateConstantExpression 未能正确处理集合作为常量。");
                    }

                    object? collectionObject = constantCollectionExpr.Value;

                    if (collectionObject == null)
                        throw new ArgumentException($"字段 '{member.Member.Name}' 的 '{operation}' 操作符所对应的集合值不能为空。");

                    if (!(collectionObject is IEnumerable valueCollection))
                    {
                        throw new ArgumentException(
                            $"字段 '{member.Member.Name}' 的 '{operation}' 操作符所对应的值必须是一个 IEnumerable 集合。" +
                            $"常量表达式中实际值的类型: {collectionObject.GetType().FullName}");
                    }

                    List<object> itemsList;
                    try
                    {
                        itemsList = valueCollection.Cast<object>().ToList();
                    }
                    catch (Exception ex)
                    {
                        throw new InvalidOperationException($"为字段 '{member.Member.Name}' 的 '{operation}' 操作符转换集合项到 List<object> 失败。", ex);
                    }

                    if (!itemsList.Any())
                    {
                        return Expression.Constant(false); // 字段 IN (空列表) 结果为 false
                    }

                    // 使用 Enumerable.Contains<object>(IEnumerable<object> source, object value)
                    MethodInfo containsMethodin = typeof(Enumerable).GetMethods(BindingFlags.Static | BindingFlags.Public)
                        .Single(m => m.Name == "Contains" && m.GetParameters().Length == 2)
                        .MakeGenericMethod(typeof(object));

                    Expression memberAsObject = Expression.Convert(member, typeof(object)); // 将属性成员转为 object

                    // constantCollectionExpr (即 Expression.Constant(集合)) 作为源
                    // memberAsObject 作为要查找的值
                    return Expression.Call(null, containsMethodin, constantCollectionExpr, memberAsObject);


                default:
                    throw new NotSupportedException($"Filter operator '{operation}' is not supported.");
            }
        }


        #endregion

        #region Sorting

        private static ISugarQueryable<T> ApplySorting<T>(ISugarQueryable<T> query, SortingInfo[]? sortOptions) where T : class, new()
        {
            if (sortOptions == null || sortOptions.Length == 0)
                return query;

            var orderByList = new List<OrderByModel>();
            foreach (var sortInfo in sortOptions)
            {
                if (string.IsNullOrWhiteSpace(sortInfo.Selector)) continue;

                orderByList.Add(new OrderByModel
                {
                    FieldName = sortInfo.Selector,
                    OrderByType = sortInfo.Desc ? OrderByType.Desc : OrderByType.Asc
                });
            }

            if (orderByList.Any())
            {
                // SqlSugar's OrderBy accepts a list for multi-column sorting
                return query.OrderBy(orderByList);
            }

            return query;
        }

        #endregion

        #region Paging

        private static ISugarQueryable<T> ApplyPaging<T>(ISugarQueryable<T> query, int skip, int take) where T : class, new()
        {
            var result = query; // Start with the input query

            if (skip > 0)
            {
                result = result.Skip(skip);
            }
            if (take > 0)
            {
                // Important: SqlSugar's Skip/Take translate to LIMIT/OFFSET or ROWNUMBER based on DB.
                // Apply Take *after* Skip.
                result = result.Take(take);
            }
            return result;
        }


        #endregion

        #region Summaries

        private static async Task<object?[]?> CalculateTotalSummariesAsync<T>(ISugarQueryable<T> query, SummaryInfo[] summaries) where T : class, new()
        {
            if (summaries == null || summaries.Length == 0)
                return null;

            var results = new object?[summaries.Length];
            var parameter = Expression.Parameter(typeof(T), "s");

            for (int i = 0; i < summaries.Length; i++)
            {
                var summaryInfo = summaries[i];
                if (string.IsNullOrEmpty(summaryInfo.Selector))
                {
                    results[i] = null; // Cannot calculate summary without a selector
                    continue;
                }

                try
                {
                    MemberExpression member;
                    try
                    {
                        member = Expression.PropertyOrField(parameter, summaryInfo.Selector);
                    }
                    catch (ArgumentException ex) // Handle invalid field name early
                    {
                        Console.WriteLine($"Error creating summary expression for selector '{summaryInfo.Selector}': {ex.Message}");
                        results[i] = null; // Or some error indicator
                        continue;
                    }


                    // SqlSugar needs the Expression for aggregates
                    var selectorLambda = Expression.Lambda(member, parameter); // e.g., s => s.Price

                    // SqlSugar aggregate functions often need Expression<Func<T, TFieldType>>
                    // We need to dynamically create the correct generic type for the lambda

                    switch (summaryInfo.SummaryType.ToLowerInvariant())
                    {
                        case "sum":
                            // Need Expression<Func<T, TResult>> where TResult is the member type or decimal/double
                            var sumLambda = Expression.Lambda<Func<T, object>>(Expression.Convert(member, typeof(object)), parameter); // Convert to object for generic SumAsync
                            results[i] = await query.SumAsync(sumLambda); // SqlSugar might handle numeric conversion
                                                                                  // Or try to be more specific if SumAsync overload exists:
                                                                                  // if (member.Type == typeof(decimal)) results[i] = await query.SumAsync((Expression<Func<T, decimal>>)selectorLambda);
                                                                                  // else if (...) // other numeric types
                            break;
                        case "avg":
                            var avgLambda = Expression.Lambda<Func<T, object>>(Expression.Convert(member, typeof(object)), parameter);
                            results[i] = await query.AvgAsync(avgLambda);
                            break;
                        case "min":
                            var minLambda = Expression.Lambda<Func<T, object>>(Expression.Convert(member, typeof(object)), parameter);
                            results[i] = await query.MinAsync(minLambda);
                            break;
                        case "max":
                            var maxLambda = Expression.Lambda<Func<T, object>>(Expression.Convert(member, typeof(object)), parameter);
                            results[i] = await query.MaxAsync(maxLambda);
                            break;
                        case "count":
                            // Count doesn't usually need a selector in SQL (COUNT(*)),
                            // but if DevExtreme asks for count based on a field, it implies counting non-null values of that field.
                            // SqlSugar's CountAsync() counts rows. Use Where().CountAsync() for conditional count.
                            // For simplicity, we'll just return the total count (already calculated if RequireTotalCount was true).
                            // Or perform a separate count:
                            results[i] = await query.CountAsync(); // This counts rows matching the filter
                            break;
                        default:
                            Console.WriteLine($"Unsupported summary type: {summaryInfo.SummaryType}");
                            results[i] = null; // Unsupported summary type
                            break;
                    }
                }
                catch (Exception ex)
                {
                    // Log error specific to this summary item
                    Console.WriteLine($"Error calculating summary '{summaryInfo.SummaryType}' for selector '{summaryInfo.Selector}': {ex}");
                    results[i] = null; // Indicate error for this summary
                }
            }

            return results;
        }

        #endregion
    }
}