using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Caching.Memory;
using Nuuru.Server.Auth;
using Nuuru.Server.Data;
using Nuuru.Server.Models.Booru;
using Nuuru.Server.Services.Search.Nodes;

namespace Nuuru.Server.Services.Search;

/// <summary>
/// Builds EF Core queries from search AST nodes.
/// </summary>
public class SearchQueryBuilder
{
    private static readonly TimeSpan TagIdCacheDuration = TimeSpan.FromMinutes(5);

    private readonly ApplicationDbContext _context;
    private readonly ICurrentUserContext _userContext;
    private readonly IMemoryCache? _cache;

    public SearchQueryBuilder(ApplicationDbContext context, ICurrentUserContext userContext, IMemoryCache? cache = null)
    {
        _context = context;
        _userContext = userContext;
        _cache = cache;
    }

    /// <summary>
    /// Builds an IQueryable from the parsed search result.
    /// </summary>
    /// <param name="parseResult">The parsed search result.</param>
    public IQueryable<Post> BuildQuery(SearchParseResult parseResult)
    {
        var query = _context.BooruPosts
            .Include(p => p.Uploader)
            .Include(p => p.PostTags)
                .ThenInclude(pt => pt.Tag)
                    .ThenInclude(t => t.Category)
            .AsQueryable();

        // Only include Votes when the controversial sort needs them — avoids loading
        // all vote records for every post on normal queries
        if (parseResult.OrderBy?.Field == "controversial")
        {
            query = query.Include(p => p.Votes);
        }

        return BuildQueryCore(query, parseResult);
    }

    /// <summary>
    /// Builds a lightweight query for counting (no includes).
    /// </summary>
    public IQueryable<Post> BuildCountQuery(SearchParseResult parseResult)
    {
        var query = _context.BooruPosts.AsQueryable();
        return BuildQueryCore(query, parseResult);
    }

    /// <summary>
    /// Applies only the filter nodes from a parse result to an existing query,
    /// without adding ordering, approval, or trash logic.
    /// </summary>
    public IQueryable<Post> ApplyFilterNodes(IQueryable<Post> query, SearchParseResult parseResult)
    {
        if (parseResult.RootNode != null)
            query = ApplyNode(query, parseResult.RootNode);
        return query;
    }

    /// <summary>
    /// Applies visibility filters (trash and approval) based on user context and search query.
    /// </summary>
    public IQueryable<Post> ApplyVisibilityFilters(IQueryable<Post> query, SearchParseResult? parseResult = null)
    {
        var canViewTrash = _userContext.HasPermission(Permissions.Admin.ViewTrash);
        var explicitlyRequestsTrashed = parseResult != null && ContainsPositiveTrashedStatusFilter(parseResult.RootNode);

        // Exclude trashed posts by default. Only include them when explicitly requested
        // and the current user has permission to view the trash.
        if (!(canViewTrash && explicitlyRequestsTrashed))
        {
            query = query.Where(p => !p.IsTrashed);
        }

        // Filter for approval based on current user context
        var canSeeUnapproved = _userContext.HasPermission(Permissions.Moderation.ApprovePost);
        if (!canSeeUnapproved)
        {
            var currentUserId = _userContext.UserId;
            if (currentUserId.HasValue)
            {
                // Use UploaderId FK directly — avoids INNER JOIN to AspNetUsers on count queries
                query = query.Where(p => p.IsApproved || p.UploaderId == currentUserId.Value);
            }
            else
            {
                query = query.Where(p => p.IsApproved);
            }
        }

        return query;
    }

    /// <summary>
    /// Walks the entire AST and pre-resolves all tag names to IDs in async batch queries,
    /// populating the IMemoryCache so that subsequent sync resolvers are instant cache hits.
    /// Call this once before BuildQuery/BuildCountQuery to avoid blocking DB I/O during query building.
    /// </summary>
    public async Task PreResolveTagIdsAsync(SearchParseResult parseResult)
    {
        if (_cache == null || parseResult.RootNode == null)
            return;

        var plainNames = new HashSet<string>();
        var categoryNames = new Dictionary<string, HashSet<string>>();
        var wildcardPrefixes = new HashSet<string>();
        var categoryWildcardKeys = new HashSet<string>();
        CollectTagNames(parseResult.RootNode, plainNames, categoryNames, wildcardPrefixes, categoryWildcardKeys);

        // Batch resolve plain tags — skip already-cached names
        var uncachedPlain = plainNames.Where(n => !_cache.TryGetValue($"tag_ids:{n}", out _)).ToList();
        if (uncachedPlain.Count > 0)
        {
            var results = await _context.BooruTags
                .Where(t => uncachedPlain.Contains(t.Name))
                .Select(t => new { t.Name, t.Id })
                .ToListAsync();

            var found = new HashSet<string>();
            foreach (var group in results.GroupBy(x => x.Name))
            {
                _cache.Set($"tag_ids:{group.Key}", group.Select(x => x.Id).ToList(), TagIdCacheDuration);
                found.Add(group.Key);
            }
            foreach (var name in uncachedPlain.Where(n => !found.Contains(n)))
                _cache.Set($"tag_ids:{name}", new List<Guid>(), TagIdCacheDuration);
        }

        // Batch resolve category tags per category — skip already-cached
        foreach (var (slug, names) in categoryNames)
        {
            var uncached = names.Where(n => !_cache.TryGetValue($"tag_ids:{slug}:{n}", out _)).ToList();
            if (uncached.Count == 0) continue;

            var results = await _context.BooruTags
                .Where(t => t.Category != null && t.Category.Slug == slug && uncached.Contains(t.Name))
                .Select(t => new { t.Name, t.Id })
                .ToListAsync();

            var found = new HashSet<string>();
            foreach (var group in results.GroupBy(x => x.Name))
            {
                _cache.Set($"tag_ids:{slug}:{group.Key}", group.Select(x => x.Id).ToList(), TagIdCacheDuration);
                found.Add(group.Key);
            }
            foreach (var name in uncached.Where(n => !found.Contains(n)))
                _cache.Set($"tag_ids:{slug}:{name}", new List<Guid>(), TagIdCacheDuration);
        }

        // Pre-resolve plain wildcard prefixes (e.g. "variant" from "variant:*" parsed as WildcardTagNode)
        foreach (var prefix in wildcardPrefixes)
        {
            var cacheKey = $"tag_ids_wildcard:{prefix}";
            if (_cache.TryGetValue(cacheKey, out _)) continue;

            var ids = await _context.BooruTags
                .Where(t => t.Name.StartsWith(prefix))
                .Select(t => t.Id)
                .ToListAsync();
            _cache.Set(cacheKey, ids, TagIdCacheDuration);
        }

        // Pre-resolve category wildcard keys (e.g. "nas:*", "variant:*" from CategoryTagNode)
        foreach (var key in categoryWildcardKeys)
        {
            var cacheKey = $"tag_ids_cat_wildcard:{key}";
            if (_cache.TryGetValue(cacheKey, out _)) continue;

            var parts = key.Split(':', 2);
            var catSlug = parts[0];
            var tagName = parts[1];

            IQueryable<Tag> tagQuery = _context.BooruTags.AsNoTracking();
            if (catSlug != "*")
            {
                if (catSlug.EndsWith('*'))
                {
                    var catPrefix = catSlug.Substring(0, catSlug.Length - 1);
                    tagQuery = tagQuery.Where(t => t.Category != null && t.Category.Slug.StartsWith(catPrefix));
                }
                else
                    tagQuery = tagQuery.Where(t => t.Category != null && t.Category.Slug == catSlug);
            }
            if (tagName != "*")
            {
                if (tagName.EndsWith('*'))
                {
                    var namePrefix = tagName.Substring(0, tagName.Length - 1);
                    tagQuery = tagQuery.Where(t => t.Name.StartsWith(namePrefix));
                }
                else
                    tagQuery = tagQuery.Where(t => t.Name == tagName);
            }

            var ids = await tagQuery.Select(t => t.Id).ToListAsync();
            _cache.Set(cacheKey, ids, TagIdCacheDuration);
        }
    }

    private static void CollectTagNames(SearchNode node, HashSet<string> plain,
        Dictionary<string, HashSet<string>> category,
        HashSet<string>? wildcardPrefixes = null,
        HashSet<string>? categoryWildcardKeys = null)
    {
        switch (node)
        {
            case TagNode tn:
                plain.Add(tn.Name);
                break;
            case WildcardTagNode wn:
                wildcardPrefixes?.Add(wn.Prefix);
                break;
            case CategoryTagNode cn:
                bool hasWildcard = cn.CategorySlug == "*" || cn.CategorySlug.EndsWith('*')
                                || cn.Name == "*" || cn.Name.EndsWith('*');
                if (hasWildcard)
                    categoryWildcardKeys?.Add($"{cn.CategorySlug}:{cn.Name}");
                else
                {
                    if (!category.TryGetValue(cn.CategorySlug, out var names))
                        category[cn.CategorySlug] = names = [];
                    names.Add(cn.Name);
                }
                break;
            case AndNode an:
                foreach (var child in an.Children) CollectTagNames(child, plain, category, wildcardPrefixes, categoryWildcardKeys);
                break;
            case OrNode on:
                foreach (var child in on.Children) CollectTagNames(child, plain, category, wildcardPrefixes, categoryWildcardKeys);
                break;
        }
    }

    /// <summary>
    /// Resolves tag names to IDs, using IMemoryCache when available.
    /// After PreResolveTagIdsAsync, all lookups are instant cache hits.
    /// </summary>
    private List<Guid> ResolveTagIdsByName(List<string> tagNames)
    {
        if (_cache == null)
        {
            return _context.BooruTags
                .Where(t => tagNames.Contains(t.Name))
                .Select(t => t.Id)
                .ToList();
        }

        var result = new List<Guid>();
        var uncachedNames = new List<string>();

        foreach (var name in tagNames)
        {
            if (_cache.TryGetValue($"tag_ids:{name}", out List<Guid>? cached))
                result.AddRange(cached!);
            else
                uncachedNames.Add(name);
        }

        if (uncachedNames.Count > 0)
        {
            var lookupResults = _context.BooruTags
                .Where(t => uncachedNames.Contains(t.Name))
                .Select(t => new { t.Name, t.Id })
                .ToList();

            var foundNames = new HashSet<string>();
            foreach (var group in lookupResults.GroupBy(x => x.Name))
            {
                var ids = group.Select(x => x.Id).ToList();
                _cache.Set($"tag_ids:{group.Key}", ids, TagIdCacheDuration);
                result.AddRange(ids);
                foundNames.Add(group.Key);
            }
            foreach (var name in uncachedNames.Where(n => !foundNames.Contains(n)))
                _cache.Set($"tag_ids:{name}", new List<Guid>(), TagIdCacheDuration);
        }

        return result;
    }

    /// <summary>
    /// Resolves category:name wildcard patterns to tag IDs, with caching.
    /// </summary>
    private List<Guid> ResolveCategoryWildcardTagIds(string categorySlug, string tagName)
    {
        var cacheKey = $"tag_ids_cat_wildcard:{categorySlug}:{tagName}";
        if (_cache != null && _cache.TryGetValue(cacheKey, out List<Guid>? cached))
            return cached!;

        IQueryable<Tag> tagQuery = _context.BooruTags.AsNoTracking();

        if (categorySlug != "*")
        {
            if (categorySlug.EndsWith('*'))
            {
                var catPrefix = categorySlug.Substring(0, categorySlug.Length - 1);
                tagQuery = tagQuery.Where(t => t.Category != null && t.Category.Slug.StartsWith(catPrefix));
            }
            else
                tagQuery = tagQuery.Where(t => t.Category != null && t.Category.Slug == categorySlug);
        }

        if (tagName != "*")
        {
            if (tagName.EndsWith('*'))
            {
                var namePrefix = tagName.Substring(0, tagName.Length - 1);
                tagQuery = tagQuery.Where(t => t.Name.StartsWith(namePrefix));
            }
            else
                tagQuery = tagQuery.Where(t => t.Name == tagName);
        }

        var ids = tagQuery.Select(t => t.Id).ToList();
        _cache?.Set(cacheKey, ids, TagIdCacheDuration);
        return ids;
    }

    /// <summary>
    /// Resolves wildcard prefix to matching tag IDs, with caching.
    /// </summary>
    private List<Guid> ResolveWildcardTagIds(string prefix)
    {
        var cacheKey = $"tag_ids_wildcard:{prefix}";
        if (_cache != null && _cache.TryGetValue(cacheKey, out List<Guid>? cached))
            return cached!;

        var ids = _context.BooruTags
            .Where(t => t.Name.StartsWith(prefix))
            .Select(t => t.Id)
            .ToList();

        _cache?.Set(cacheKey, ids, TagIdCacheDuration);
        return ids;
    }

    /// <summary>
    /// Resolves category:name tag pairs to IDs, using IMemoryCache when available.
    /// </summary>
    private List<Guid> ResolveCategoryTagIds(string categorySlug, List<string> tagNames)
    {
        if (_cache == null)
        {
            return _context.BooruTags
                .Where(t => t.Category != null && t.Category.Slug == categorySlug && tagNames.Contains(t.Name))
                .Select(t => t.Id)
                .ToList();
        }

        var result = new List<Guid>();
        var uncachedNames = new List<string>();

        foreach (var name in tagNames)
        {
            if (_cache.TryGetValue($"tag_ids:{categorySlug}:{name}", out List<Guid>? cached))
                result.AddRange(cached!);
            else
                uncachedNames.Add(name);
        }

        if (uncachedNames.Count > 0)
        {
            var lookupResults = _context.BooruTags
                .Where(t => t.Category != null && t.Category.Slug == categorySlug && uncachedNames.Contains(t.Name))
                .Select(t => new { t.Name, t.Id })
                .ToList();

            var foundNames = new HashSet<string>();
            foreach (var group in lookupResults.GroupBy(x => x.Name))
            {
                var ids = group.Select(x => x.Id).ToList();
                _cache.Set($"tag_ids:{categorySlug}:{group.Key}", ids, TagIdCacheDuration);
                result.AddRange(ids);
                foundNames.Add(group.Key);
            }
            foreach (var name in uncachedNames.Where(n => !foundNames.Contains(n)))
                _cache.Set($"tag_ids:{categorySlug}:{name}", new List<Guid>(), TagIdCacheDuration);
        }

        return result;
    }

    private IQueryable<Post> BuildQueryCore(IQueryable<Post> query, SearchParseResult parseResult)
    {
        query = ApplyVisibilityFilters(query, parseResult);

        // Apply filter nodes
        if (parseResult.RootNode != null)
        {
            query = ApplyNode(query, parseResult.RootNode);
        }

        // Apply ordering
        query = ApplyOrdering(query, parseResult.OrderBy);

        return query;
    }

    private static bool ContainsPositiveTrashedStatusFilter(SearchNode? node)
    {
        if (node == null)
            return false;

        return node switch
        {
            StatusFilterNode statusNode => !statusNode.Negated && statusNode.Status == "trashed",
            AndNode andNode => andNode.Children.Any(ContainsPositiveTrashedStatusFilter),
            OrNode orNode => orNode.Children.Any(ContainsPositiveTrashedStatusFilter),
            _ => false
        };
    }

    private IQueryable<Post> ApplyNode(IQueryable<Post> query, SearchNode node)
    {
        return node switch
        {
            TagNode tn => ApplyTagFilter(query, tn),
            CategoryTagNode cn => ApplyCategoryTagFilter(query, cn),
            WildcardTagNode wn => ApplyWildcardFilter(query, wn),
            AndNode an => ApplyAndFilter(query, an),
            OrNode on => ApplyOrFilter(query, on),
            RatingFilterNode rn => ApplyRatingFilter(query, rn),
            CategoryFilterNode cn => ApplyCategoryFilter(query, cn),
            UploaderFilterNode un => ApplyUploaderFilter(query, un),
            NumericRangeFilterNode nf => ApplyNumericFilter(query, nf),
            DateRangeFilterNode df => ApplyDateFilter(query, df),
            FileTypeFilterNode ff => ApplyFileTypeFilter(query, ff),
            StatusFilterNode sf => ApplyStatusFilter(query, sf),
            _ => query
        };
    }

    private IQueryable<Post> ApplyStatusFilter(IQueryable<Post> query, StatusFilterNode node)
    {
        var status = node.Status;
        return status switch
        {
            "approved" => node.Negated ? query.Where(p => !p.IsApproved) : query.Where(p => p.IsApproved),
            "pending" => node.Negated ? query.Where(p => p.IsApproved) : query.Where(p => !p.IsApproved),
            "featured" => node.Negated ? query.Where(p => !p.IsFeatured) : query.Where(p => p.IsFeatured),
            "locked" => node.Negated ? query.Where(p => !p.CommentsLocked) : query.Where(p => p.CommentsLocked),
            "trashed" => node.Negated ? query.Where(p => !p.IsTrashed) : query.Where(p => p.IsTrashed),
            _ => query
        };
    }

    #region Tag Filters

    private IQueryable<Post> ApplyTagFilter(IQueryable<Post> query, TagNode node)
    {
        var tagIds = ResolveTagIdsByName([node.Name]);
        return ApplyTagIdFilter(query, tagIds, node.Negated);
    }

    private IQueryable<Post> ApplyWildcardFilter(IQueryable<Post> query, WildcardTagNode node)
    {
        var prefix = node.Prefix;
        if (node.Negated)
            return query.Where(p => !p.PostTags.Any(pt => pt.Tag.Name.StartsWith(prefix)));
        else
            return query.Where(p => p.PostTags.Any(pt => pt.Tag.Name.StartsWith(prefix)));
    }

    private IQueryable<Post> ApplyCategoryTagFilter(IQueryable<Post> query, CategoryTagNode node)
    {
        var categorySlug = node.CategorySlug;
        var tagName = node.Name;

        // Fast path: no wildcards, use cached resolver
        bool hasWildcard = categorySlug == "*" || categorySlug.EndsWith('*')
                        || tagName == "*" || tagName.EndsWith('*');

        if (!hasWildcard)
        {
            var matchingTagIds = ResolveCategoryTagIds(categorySlug, [tagName]);
            return ApplyTagIdFilter(query, matchingTagIds, node.Negated);
        }

        // Wildcard path: resolve to tag IDs (cached) and use ANY() for direct index lookup
        var wildcardTagIds = ResolveCategoryWildcardTagIds(categorySlug, tagName);
        return ApplyTagIdFilter(query, wildcardTagIds, node.Negated);
    }

    private IQueryable<Post> ApplyTagIdFilter(IQueryable<Post> query, List<Guid> tagIds, bool negated)
    {
        if (tagIds.Count == 0)
            return negated ? query : query.Where(p => false);

        // Use NOT EXISTS / EXISTS instead of NOT IN / IN — PostgreSQL generates an anti-join
        // which can use the PostTag(PostId, TagId) index and short-circuit per row,
        // whereas NOT IN materializes the entire subquery result set first.
        return negated
            ? query.Where(p => !_context.Set<PostTag>().Any(pt => pt.PostId == p.Id && tagIds.Contains(pt.TagId)))
            : query.Where(p => _context.Set<PostTag>().Any(pt => pt.PostId == p.Id && tagIds.Contains(pt.TagId)));
    }

    #endregion

    #region Boolean Filters

    private IQueryable<Post> ApplyAndFilter(IQueryable<Post> query, AndNode node)
    {
        // Batch all simple negated tags into a single exclusion query
        // instead of N separate DB roundtrips + N NOT IN subqueries
        var negatedTagNames = new List<string>();
        var negatedCategoryTags = new List<CategoryTagNode>();
        // Conditional exclusions: {-excluded ~ whitelist} OR groups that can be merged
        // Key = whitelist tag IDs (sorted for grouping), Value = list of excluded tag IDs
        var conditionalExclusions = new List<(List<Guid> ExcludedIds, List<Guid> WhitelistIds)>();
        var remaining = new List<SearchNode>();

        foreach (var child in node.Children)
        {
            switch (child)
            {
                case TagNode { Negated: true } tn:
                    negatedTagNames.Add(tn.Name);
                    break;
                case CategoryTagNode { Negated: true } cn
                    when !cn.CategorySlug.EndsWith('*') && cn.CategorySlug != "*"
                      && !cn.Name.EndsWith('*') && cn.Name != "*":
                    negatedCategoryTags.Add(cn);
                    break;
                case OrNode on when TryExtractConditionalExclusion(on, out var excluded, out var whitelist):
                    conditionalExclusions.Add((excluded, whitelist));
                    break;
                default:
                    remaining.Add(child);
                    break;
            }
        }

        // Resolve all negated tag IDs (plain + category) and combine into a single NOT EXISTS subquery
        var allExcludedTagIds = new List<Guid>();

        if (negatedTagNames.Count > 0)
        {
            allExcludedTagIds.AddRange(ResolveTagIdsByName(negatedTagNames));
        }

        if (negatedCategoryTags.Count > 0)
        {
            foreach (var group in negatedCategoryTags.GroupBy(cn => cn.CategorySlug))
            {
                var slug = group.Key;
                var names = group.Select(cn => cn.Name).ToList();
                allExcludedTagIds.AddRange(ResolveCategoryTagIds(slug, names));
            }
        }

        if (allExcludedTagIds.Count > 0)
        {
            query = query.Where(p => !_context.Set<PostTag>()
                .Any(pt => pt.PostId == p.Id && allExcludedTagIds.Contains(pt.TagId)));
        }

        // Merge conditional exclusions that share the same whitelist tags into single subquery pairs.
        // {-A ~ W} AND {-B ~ W} AND {-C ~ W} becomes:
        //   NOT EXISTS(PostTag WHERE PostId AND TagId IN [A,B,C]) OR EXISTS(PostTag WHERE PostId AND TagId IN [W])
        // This reduces 2N correlated subqueries to 2 per unique whitelist set.
        if (conditionalExclusions.Count > 0)
        {
            var grouped = conditionalExclusions
                .GroupBy(ce => string.Join(",", ce.WhitelistIds.Order()))
                .ToList();

            foreach (var group in grouped)
            {
                var mergedExcluded = group.SelectMany(ce => ce.ExcludedIds).Distinct().ToList();
                var whitelistIds = group.First().WhitelistIds;

                if (mergedExcluded.Count == 0) continue;

                if (whitelistIds.Count == 0)
                {
                    // No whitelist — pure exclusion, fold into main NOT EXISTS
                    query = query.Where(p => !_context.Set<PostTag>()
                        .Any(pt => pt.PostId == p.Id && mergedExcluded.Contains(pt.TagId)));
                }
                else
                {
                    // Conditional: exclude unless whitelisted
                    // Use NOT(EXISTS AND NOT EXISTS) instead of NOT EXISTS OR EXISTS
                    // so PostgreSQL can short-circuit: posts without excluded tags skip the whitelist check entirely
                    query = query.Where(p =>
                        !(_context.Set<PostTag>().Any(pt => pt.PostId == p.Id && mergedExcluded.Contains(pt.TagId))
                        && !_context.Set<PostTag>().Any(pt => pt.PostId == p.Id && whitelistIds.Contains(pt.TagId))));
                }
            }
        }

        // Apply remaining filters normally
        foreach (var child in remaining)
        {
            query = ApplyNode(query, child);
        }

        return query;
    }

    /// <summary>
    /// Detects OR groups matching the pattern {-excluded1 -excluded2 ... ~ whitelist1 whitelist2 ...}
    /// where all children are simple (non-wildcard) tag or category:tag nodes.
    /// Returns pre-resolved tag IDs for both sides.
    /// </summary>
    private bool TryExtractConditionalExclusion(OrNode node, out List<Guid> excludedIds, out List<Guid> whitelistIds)
    {
        excludedIds = [];
        whitelistIds = [];

        if (node.Children.Count < 2)
            return false;

        var negatedNames = new List<string>();
        var negatedCategoryTags = new List<CategoryTagNode>();
        var positiveNames = new List<string>();
        var positiveCategoryTags = new List<CategoryTagNode>();

        foreach (var child in node.Children)
        {
            switch (child)
            {
                case TagNode tn:
                    (tn.Negated ? negatedNames : positiveNames).Add(tn.Name);
                    break;
                case CategoryTagNode cn:
                {
                    bool isWildcard = cn.CategorySlug == "*" || cn.CategorySlug.EndsWith('*')
                                  || cn.Name == "*" || cn.Name.EndsWith('*');
                    if (isWildcard)
                    {
                        var wcIds = ResolveCategoryWildcardTagIds(cn.CategorySlug, cn.Name);
                        (cn.Negated ? excludedIds : whitelistIds).AddRange(wcIds);
                    }
                    else
                    {
                        (cn.Negated ? negatedCategoryTags : positiveCategoryTags).Add(cn);
                    }
                    break;
                }
                case WildcardTagNode wn:
                {
                    var wcIds = ResolveWildcardTagIds(wn.Prefix);
                    (wn.Negated ? excludedIds : whitelistIds).AddRange(wcIds);
                    break;
                }
                default:
                    return false; // Complex nodes — bail out
            }
        }

        if (negatedNames.Count == 0 && negatedCategoryTags.Count == 0 && excludedIds.Count == 0)
            return false;

        // Resolve IDs
        if (negatedNames.Count > 0)
            excludedIds.AddRange(ResolveTagIdsByName(negatedNames));
        foreach (var group in negatedCategoryTags.GroupBy(cn => cn.CategorySlug))
            excludedIds.AddRange(ResolveCategoryTagIds(group.Key, group.Select(cn => cn.Name).ToList()));

        if (positiveNames.Count > 0)
            whitelistIds.AddRange(ResolveTagIdsByName(positiveNames));
        foreach (var group in positiveCategoryTags.GroupBy(cn => cn.CategorySlug))
            whitelistIds.AddRange(ResolveCategoryTagIds(group.Key, group.Select(cn => cn.Name).ToList()));

        return excludedIds.Count > 0;
    }

    private IQueryable<Post> ApplyOrFilter(IQueryable<Post> query, OrNode node)
    {
        // For simple OR of tags, use an optimized approach
        var simpleTags = node.Children
            .OfType<TagNode>()
            .Where(t => !t.Negated)
            .Select(t => t.Name)
            .ToList();

        if (simpleTags.Count == node.Children.Count && simpleTags.Count > 0)
        {
            var tagIds = ResolveTagIdsByName(simpleTags);

            if (tagIds.Count == 0)
                return query.Where(p => false);

            return query.Where(p => _context.Set<PostTag>()
                .Any(pt => pt.PostId == p.Id && tagIds.Contains(pt.TagId)));
        }

        // Complex OR - fall back to applying each filter separately and combining
        // This is less efficient but handles complex cases
        return BuildOrExpression(query, node);
    }

    private IQueryable<Post> BuildOrExpression(IQueryable<Post> query, OrNode node)
    {
        if (node.Children.Count == 0)
            return query;

        var param = Expression.Parameter(typeof(Post), "p");
        Expression? combinedExpr = null;

        foreach (var child in node.Children)
        {
            var childExpr = BuildNodeExpression(param, child);
            if (childExpr != null)
            {
                combinedExpr = combinedExpr == null
                    ? childExpr
                    : Expression.OrElse(combinedExpr, childExpr);
            }
        }

        if (combinedExpr == null)
            return query;

        var lambda = Expression.Lambda<Func<Post, bool>>(combinedExpr, param);
        return query.Where(lambda);
    }

    private Expression? BuildNodeExpression(ParameterExpression param, SearchNode node)
    {
        return node switch
        {
            TagNode tn => BuildTagExpression(param, tn),
            CategoryTagNode cn => BuildCategoryTagExpression(param, cn),
            WildcardTagNode wn => BuildWildcardExpression(param, wn),
            AndNode an => BuildAndExpression(param, an),
            StatusFilterNode sn => BuildStatusExpression(param, sn),
            RatingFilterNode rn => BuildRatingExpression(param, rn),
            CategoryFilterNode cn => BuildCategoryExpression(param, cn),
            UploaderFilterNode un => BuildUploaderExpression(param, un),
            FileTypeFilterNode fn => BuildFileTypeExpression(param, fn),
            _ => null
        };
    }

    private Expression? BuildStatusExpression(ParameterExpression param, StatusFilterNode node)
    {
        Expression? statusExpression = node.Status switch
        {
            "approved" => Expression.Property(param, nameof(Post.IsApproved)),
            "pending" => Expression.Not(Expression.Property(param, nameof(Post.IsApproved))),
            "featured" => Expression.Property(param, nameof(Post.IsFeatured)),
            "locked" => Expression.Property(param, nameof(Post.CommentsLocked)),
            "trashed" => Expression.Property(param, nameof(Post.IsTrashed)),
            _ => null
        };

        if (statusExpression == null)
            return null;

        return node.Negated ? Expression.Not(statusExpression) : statusExpression;
    }

    private static Expression BuildRatingExpression(ParameterExpression param, RatingFilterNode node)
    {
        // p.Rating == rating  (or p.Rating != rating when negated)
        var ratingProp = Expression.Property(param, nameof(Post.Rating));
        var ratingValue = Expression.Constant(node.Rating);
        Expression expr = Expression.Equal(ratingProp, ratingValue);
        return node.Negated ? Expression.Not(expr) : expr;
    }

    private static Expression BuildCategoryExpression(ParameterExpression param, CategoryFilterNode node)
    {
        // p.Category == category (or p.Category != category when negated)
        var categoryProp = Expression.Property(param, nameof(Post.Category));
        var categoryValue = Expression.Constant(node.Category);
        Expression expr = Expression.Equal(categoryProp, categoryValue);
        return node.Negated ? Expression.Not(expr) : expr;
    }

    private static Expression BuildUploaderExpression(ParameterExpression param, UploaderFilterNode node)
    {
        // p.Uploader.UserName != null && p.Uploader.UserName.ToLower() == username
        var uploaderProp = Expression.Property(param, nameof(Post.Uploader));
        var userNameProp = Expression.Property(uploaderProp, "UserName");
        var notNull = Expression.NotEqual(userNameProp, Expression.Constant(null, typeof(string)));
        var toLowerMethod = typeof(string).GetMethod(nameof(string.ToLower), Type.EmptyTypes)!;
        var toLowerCall = Expression.Call(userNameProp, toLowerMethod);
        var usernameConst = Expression.Constant(node.Username);
        var equals = Expression.Equal(toLowerCall, usernameConst);
        Expression expr = Expression.AndAlso(notNull, equals);
        return node.Negated ? Expression.Not(expr) : expr;
    }

    private static Expression BuildFileTypeExpression(ParameterExpression param, FileTypeFilterNode node)
    {
        // p.MimeType == mimeType  (or p.MimeType != mimeType when negated)
        var mimeTypeProp = Expression.Property(param, nameof(Post.MimeType));
        var mimeTypeValue = Expression.Constant(node.MimeType);
        Expression expr = Expression.Equal(mimeTypeProp, mimeTypeValue);
        return node.Negated ? Expression.Not(expr) : expr;
    }

    /// <summary>
    /// Builds a tag filter expression using pre-resolved tag IDs instead of joining through
    /// BooruTags by name. This eliminates the INNER JOIN BooruTags in correlated subqueries,
    /// generating: p.PostTags.Any(pt => pt.TagId == id)
    /// instead of: p.PostTags.Any(pt => pt.Tag.Name == 'name') which requires a JOIN.
    /// </summary>
    private Expression BuildTagExpression(ParameterExpression param, TagNode node)
    {
        var tagIds = ResolveTagIdsByName([node.Name]);
        return BuildTagIdExistsExpression(param, tagIds, node.Negated);
    }

    private Expression BuildWildcardExpression(ParameterExpression param, WildcardTagNode node)
    {
        var matchingTagIds = ResolveWildcardTagIds(node.Prefix);
        return BuildTagIdExistsExpression(param, matchingTagIds, node.Negated);
    }

    private Expression BuildCategoryTagExpression(ParameterExpression param, CategoryTagNode node)
    {
        // For non-wildcard category tags, use the cached resolver
        bool hasWildcard = node.CategorySlug == "*" || node.CategorySlug.EndsWith('*')
                        || node.Name == "*" || node.Name.EndsWith('*');

        if (!hasWildcard)
        {
            var tagIds = ResolveCategoryTagIds(node.CategorySlug, [node.Name]);
            return BuildTagIdExistsExpression(param, tagIds, node.Negated);
        }

        // Wildcard path: resolve to cached tag IDs for direct index lookup (no joins)
        var wcIds = ResolveCategoryWildcardTagIds(node.CategorySlug, node.Name);
        return BuildTagIdExistsExpression(param, wcIds, node.Negated);
    }

    /// <summary>
    /// Builds: p.PostTags.Any(pt => pt.TagId == id) for single ID, or
    ///         p.PostTags.Any(pt => tagIds.Contains(pt.TagId)) for multiple IDs.
    /// Generates SQL: EXISTS (SELECT 1 FROM PostTag WHERE PostId = p.Id AND TagId = @id)
    /// — no JOIN to BooruTags needed.
    /// </summary>
    private static Expression BuildTagIdExistsExpression(ParameterExpression param, List<Guid> tagIds, bool negated)
    {
        if (tagIds.Count == 0)
            return Expression.Constant(negated); // negated + no tags = true (no match to exclude), positive + no tags = false

        var postTags = Expression.Property(param, nameof(Post.PostTags));
        var ptParam = Expression.Parameter(typeof(PostTag), "pt");
        var tagIdProp = Expression.Property(ptParam, nameof(PostTag.TagId));

        Expression condition;
        if (tagIds.Count == 1)
        {
            // pt.TagId == guid — simplest possible expression
            condition = Expression.Equal(tagIdProp, Expression.Constant(tagIds[0]));
        }
        else
        {
            // tagIds.Contains(pt.TagId)
            var containsMethod = typeof(List<Guid>).GetMethod(nameof(List<Guid>.Contains), [typeof(Guid)])!;
            condition = Expression.Call(Expression.Constant(tagIds), containsMethod, tagIdProp);
        }

        var anyLambda = Expression.Lambda<Func<PostTag, bool>>(condition, ptParam);
        var anyMethod = typeof(Enumerable).GetMethods()
            .First(m => m.Name == "Any" && m.GetParameters().Length == 2)
            .MakeGenericMethod(typeof(PostTag));

        Expression anyCall = Expression.Call(anyMethod, postTags, anyLambda);

        if (negated)
            anyCall = Expression.Not(anyCall);

        return anyCall;
    }

    private Expression? BuildAndExpression(ParameterExpression param, AndNode node)
    {
        Expression? combined = null;
        foreach (var child in node.Children)
        {
            var childExpr = BuildNodeExpression(param, child);
            if (childExpr != null)
            {
                combined = combined == null ? childExpr : Expression.AndAlso(combined, childExpr);
            }
        }
        return combined;
    }

    #endregion

    #region Meta Filters

    private IQueryable<Post> ApplyRatingFilter(IQueryable<Post> query, RatingFilterNode node)
    {
        if (node.Negated)
            return query.Where(p => p.Rating != node.Rating);
        return query.Where(p => p.Rating == node.Rating);
    }

    private IQueryable<Post> ApplyCategoryFilter(IQueryable<Post> query, CategoryFilterNode node)
    {
        if (node.Negated)
            return query.Where(p => p.Category != node.Category);
        return query.Where(p => p.Category == node.Category);
    }

    private IQueryable<Post> ApplyUploaderFilter(IQueryable<Post> query, UploaderFilterNode node)
    {
        var username = node.Username;

        if (node.Negated)
            return query.Where(p => p.Uploader.UserName != null && p.Uploader.UserName.ToLower() != username);
        return query.Where(p => p.Uploader.UserName != null && p.Uploader.UserName.ToLower() == username);
    }

    private IQueryable<Post> ApplyNumericFilter(IQueryable<Post> query, NumericRangeFilterNode node)
    {
        return node.Field switch
        {
            "id" => ApplyIntRangeFilter(query, p => p.Id, node),
            "score" => ApplyIntRangeFilter(query, p => p.Score, node),
            "width" => ApplyNullableIntRangeFilter(query, p => p.Width, node),
            "height" => ApplyNullableIntRangeFilter(query, p => p.Height, node),
            "filesize" => ApplyLongRangeFilter(query, p => p.FileSize, node),
            "tagcount" => ApplyIntRangeFilter(query, p => p.TagCount, node),
            _ => query
        };
    }

    private static IQueryable<Post> ApplyIntRangeFilter(IQueryable<Post> query, Expression<Func<Post, int>> selector, NumericRangeFilterNode node)
    {
        var min = node.Min.HasValue ? (int)node.Min.Value : (int?)null;
        var max = node.Max.HasValue ? (int)node.Max.Value : (int?)null;
        
        return ApplyGenericRangeFilter(query, selector, min, max, node.Negated);
    }

    private static IQueryable<Post> ApplyNullableIntRangeFilter(IQueryable<Post> query, Expression<Func<Post, int?>> selector, NumericRangeFilterNode node)
    {
        // For nullable fields like width/height, we treat null as 0 for range comparisons
        var min = node.Min.HasValue ? (int)node.Min.Value : (int?)null;
        var max = node.Max.HasValue ? (int)node.Max.Value : (int?)null;

        var param = selector.Parameters[0];
        // Replace p.Width with (p.Width ?? 0)
        var coalesce = Expression.Coalesce(selector.Body, Expression.Constant(0, typeof(int?)));
        var converted = Expression.Convert(coalesce, typeof(int));
        var newSelector = Expression.Lambda<Func<Post, int>>(converted, param);

        return ApplyGenericRangeFilter(query, newSelector, min, max, node.Negated);
    }

    private static IQueryable<Post> ApplyLongRangeFilter(IQueryable<Post> query, Expression<Func<Post, long>> selector, NumericRangeFilterNode node)
    {
        return ApplyGenericRangeFilter(query, selector, node.Min, node.Max, node.Negated);
    }

    private static IQueryable<Post> ApplyGenericRangeFilter<TValue>(
        IQueryable<Post> query, 
        Expression<Func<Post, TValue>> selector, 
        TValue? min, 
        TValue? max, 
        bool negated) where TValue : struct, IComparable
    {
        if (negated)
        {
            if (min.HasValue && max.HasValue)
            {
                return query.Where(CombineWithOr(
                    CombineWithComparison(selector, Expression.LessThan, min.Value),
                    CombineWithComparison(selector, Expression.GreaterThan, max.Value)
                ));
            }
            if (min.HasValue)
            {
                return query.Where(CombineWithComparison(selector, Expression.LessThan, min.Value));
            }
            if (max.HasValue)
            {
                return query.Where(CombineWithComparison(selector, Expression.GreaterThan, max.Value));
            }
        }
        else
        {
            if (min.HasValue)
            {
                query = query.Where(CombineWithComparison(selector, Expression.GreaterThanOrEqual, min.Value));
            }
            if (max.HasValue)
            {
                query = query.Where(CombineWithComparison(selector, Expression.LessThanOrEqual, max.Value));
            }
        }

        return query;
    }

    private static Expression<Func<Post, bool>> CombineWithComparison<TValue>(
        Expression<Func<Post, TValue>> selector,
        Func<Expression, Expression, BinaryExpression> comparison,
        TValue value)
    {
        var constant = Expression.Constant(value, typeof(TValue));
        var body = comparison(selector.Body, constant);
        return Expression.Lambda<Func<Post, bool>>(body, selector.Parameters);
    }

    private static Expression<Func<Post, bool>> CombineWithOr(
        Expression<Func<Post, bool>> expr1,
        Expression<Func<Post, bool>> expr2)
    {
        var parameter = expr1.Parameters[0];
        var visitor = new ParameterReplacer(expr2.Parameters[0], parameter);
        var body2 = visitor.Visit(expr2.Body);
        var combined = Expression.OrElse(expr1.Body, body2);
        return Expression.Lambda<Func<Post, bool>>(combined, parameter);
    }

    private class ParameterReplacer(ParameterExpression oldParam, ParameterExpression newParam) : ExpressionVisitor
    {
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == oldParam ? newParam : base.VisitParameter(node);
        }
    }

    private IQueryable<Post> ApplyDateFilter(IQueryable<Post> query, DateRangeFilterNode node)
    {
        if (node.Negated)
        {
            if (node.Min.HasValue && node.Max.HasValue)
            {
                var min = node.Min.Value;
                var max = node.Max.Value;
                return query.Where(p => p.UploadedAt < min || p.UploadedAt > max);
            }
            if (node.Min.HasValue)
            {
                var min = node.Min.Value;
                return query.Where(p => p.UploadedAt < min);
            }
            if (node.Max.HasValue)
            {
                var max = node.Max.Value;
                return query.Where(p => p.UploadedAt > max);
            }
        }
        else
        {
            if (node.Min.HasValue)
            {
                var min = node.Min.Value;
                query = query.Where(p => p.UploadedAt >= min);
            }
            if (node.Max.HasValue)
            {
                var max = node.Max.Value;
                query = query.Where(p => p.UploadedAt <= max);
            }
        }
        return query;
    }

    private IQueryable<Post> ApplyFileTypeFilter(IQueryable<Post> query, FileTypeFilterNode node)
    {
        var mimeType = node.MimeType;
        if (node.Negated)
            return query.Where(p => p.MimeType != mimeType);
        return query.Where(p => p.MimeType == mimeType);
    }

    #endregion

    #region Ordering

    private static IQueryable<Post> ApplyOrdering(IQueryable<Post> query, OrderByNode? orderBy)
    {
        if (orderBy == null)
            return query.OrderByDescending(p => p.UploadedAt).ThenByDescending(p => p.Id);

        return orderBy.Field switch
        {
            "date" => orderBy.Descending
                ? query.OrderByDescending(p => p.UploadedAt).ThenByDescending(p => p.Id)
                : query.OrderBy(p => p.UploadedAt).ThenBy(p => p.Id),
            "id" => orderBy.Descending
                ? query.OrderByDescending(p => p.Id)
                : query.OrderBy(p => p.Id),
            "score" => orderBy.Descending
                ? query.OrderByDescending(p => p.Score).ThenByDescending(p => p.Id)
                : query.OrderBy(p => p.Score).ThenBy(p => p.Id),
            "filesize" => orderBy.Descending
                ? query.OrderByDescending(p => p.FileSize).ThenByDescending(p => p.Id)
                : query.OrderBy(p => p.FileSize).ThenBy(p => p.Id),
            "tagcount" => orderBy.Descending
                ? query.OrderByDescending(p => p.TagCount).ThenByDescending(p => p.Id)
                : query.OrderBy(p => p.TagCount).ThenBy(p => p.Id),
            "random" => query.OrderBy(_ => EF.Functions.Random()),
            "controversial" => query
                .OrderByDescending(p => Math.Log(1 + p.Votes.Count) * (1.0 - (double)Math.Abs(p.Score) / (p.Votes.Count + 2)))
                .ThenByDescending(p => p.Votes.Count),
            _ => query.OrderByDescending(p => p.UploadedAt).ThenByDescending(p => p.Id)
        };
    }

    #endregion
}