<?php

namespace App\Performance;

use App\Models\Comment;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Cache;
use Illuminate\Support\Facades\DB;

class N1QueryOptimizer 
{
    /**
     * Optimize community category loading to prevent N+1 queries
     * This replaces the PostPresenter->communityCategory() calls with batch loading
     */
    public static function optimizeCommunityCategories($posts, $communityId)
    {
        if (empty($posts) || !is_iterable($posts)) {
            return [];
        }
        
        // Extract unique category IDs from posts
        $categoryIds = collect($posts)
            ->pluck('type_id')
            ->filter()
            ->unique()
            ->values()
            ->toArray();
            
        if (empty($categoryIds)) {
            return [];
        }
        
        // Create cache key for this batch
        $cacheKey = "batch_post_categories_{$communityId}_" . md5(implode(',', $categoryIds));
        
        return Cache::remember($cacheKey, 300, function() use ($categoryIds, $communityId) { // 5 min cache
            return DB::table('id_community_categories')
                ->whereIn('id', $categoryIds)
                ->where('community_id', $communityId)
                ->get()
                ->keyBy('id')
                ->toArray();
        });
    }
    
    /**
     * Pre-load community categories for dashboard performance
     * Call this before rendering the dashboard view
     */
    public static function preloadDashboardCategories($communityId)
    {
        $cacheKey = "dashboard_categories_$communityId";
        
        return Cache::remember($cacheKey, 600, function() use ($communityId) { // 10 min cache
            return DB::table('id_community_categories')
                ->where('community_id', $communityId)
                ->where('status', 1)
                ->orderBy('title')
                ->get()
                ->keyBy('id')
                ->toArray();
        });
    }
    
    /**
     * Get category data for a post without database query
     * Uses pre-loaded categories from cache
     */
    public static function getCategoryForPost($typeId, $communityId, $preloadedCategories = null)
    {
        // If no pre-loaded data, fall back to individual cache lookup
        if ($preloadedCategories === null) {
            $cacheKey = "category_{$typeId}_comm_{$communityId}";
            return Cache::remember($cacheKey, 300, function() use ($typeId, $communityId) {
                return DB::table('id_community_categories')
                    ->where('id', $typeId)
                    ->where('community_id', $communityId)
                    ->first();
            });
        }
        
        // Use pre-loaded data
        return isset($preloadedCategories[$typeId]) ? 
            (object) $preloadedCategories[$typeId] : null;
    }
    
    /**
     * Optimize the posts query that's causing slowdown
     * This addresses the complex posts query taking 29ms
     */
    public static function optimizePostsQuery($communityId, $userId, $limit = 5)
    {
        $cacheKey = "dashboard_posts_{$communityId}_{$userId}";
        
        return Cache::remember($cacheKey, 180, function() use ($communityId, $userId, $limit) { // 3 min cache
            // Get blocked users once
            $blockedUsers = Cache::remember("blocked_users_$userId", 300, function() use ($userId) {
                return DB::table('id_user_blocks')
                    ->where('user_id', $userId)
                    ->pluck('blocked_user_id')
                    ->toArray();
            });
            
            // Get hidden posts once
            $hiddenPosts = Cache::remember("hidden_posts_$userId", 300, function() use ($userId) {
                return DB::table('id_post_hide')
                    ->where('user_id', $userId)
                    ->pluck('post_id')
                    ->toArray();
            });
            
            // Optimized posts query with proper indexing hints
            $query = DB::table('id_posts')
                ->select(['id', 'user_id', 'type_id', 'community_id', 'text', 'post_header_texts', 'created_at', 'file_path'])
                ->where('published_by_community_owner', 1)
                ->where('published_food', 1)
                ->where('status', 1)
                ->where('ignored', 0)
                ->where('is_announcement', 1)
                ->where('show_on_my_timeline', '!=', 0)
                ->where('file_path', '')
                ->where(function($query) use ($communityId) {
                    $query->where('posted_in_community', $communityId)
                          ->orWhere('community_id', $communityId);
                });
                
            // Apply blocks and hidden posts if any exist
            if (!empty($blockedUsers)) {
                $query->whereNotIn('user_id', $blockedUsers);
            }
            
            if (!empty($hiddenPosts)) {
                $query->whereNotIn('id', $hiddenPosts);
            }
            
            return $query->orderBy('created_at', 'desc')
                         ->limit($limit)
                         ->get()
                         ->toArray();
        });
    }

    /**
     * Prime request-scoped caches for rendering many posts via the shared post.media Blade.
     *
     * - Primes post like/comment counts (repository runtime caches)
     * - Preloads the 3 most recent comments per post and primes comment-like counts
     * - Optionally sets the request attribute map used by PostPresenter::hasNotSeen()
     */
    public static function primePostMediaForFeed($posts, array $options = []): void
    {
        if (empty($posts) || ! is_iterable($posts)) {
            return;
        }

        // Avoid per-post relationship queries during rendering (e.g., postShowTime() and
        // scheduled/category blocks that access $post->community / $post->postedIncommunity).
        // Best-effort only: this is safe to skip if $posts isn't an Eloquent collection.
        try {
            if (is_object($posts) && method_exists($posts, 'loadMissing')) {
                $posts->loadMissing([
                    'community',
                    'postedIncommunity',
                    'communityCategory',
                ]);
            }
        } catch (\Throwable $e) {
            // ignore
        }

        $commentsPerPost = (int) ($options['commentsPerPost'] ?? 3);
        if ($commentsPerPost <= 0) {
            $commentsPerPost = 3;
        }

        $communityId = (int) ($options['communityId'] ?? 0);
        $categoryId = (int) ($options['categoryId'] ?? 0);

        $userId = (int) ($options['userId'] ?? (Auth::check() ? Auth::id() : 0));

        $postIds = [];
        $inferredCommunityIds = [];
        $inferredCategoryIds = [];
        $pageIds = [];
        foreach ($posts as $post) {
            if (is_object($post) && isset($post->id)) {
                $postIds[] = (int) $post->id;
                if (isset($post->community_id) && is_numeric($post->community_id)) {
                    $inferredCommunityIds[] = (int) $post->community_id;
                }
                if (isset($post->type_id) && is_numeric($post->type_id)) {
                    $inferredCategoryIds[] = (int) $post->type_id;
                }
                if (isset($post->page_id) && is_numeric($post->page_id) && (int) $post->page_id > 0) {
                    $pageIds[] = (int) $post->page_id;
                }
            } elseif (is_array($post) && isset($post['id'])) {
                $postIds[] = (int) $post['id'];
                if (isset($post['community_id']) && is_numeric($post['community_id'])) {
                    $inferredCommunityIds[] = (int) $post['community_id'];
                }
                if (isset($post['type_id']) && is_numeric($post['type_id'])) {
                    $inferredCategoryIds[] = (int) $post['type_id'];
                }
                if (isset($post['page_id']) && is_numeric($post['page_id']) && (int) $post['page_id'] > 0) {
                    $pageIds[] = (int) $post['page_id'];
                }
            }
        }

        $postIds = array_values(array_unique(array_filter($postIds, function ($id) {
            return is_numeric($id) && (int) $id > 0;
        })));

        if (! $postIds) {
            return;
        }

        $pageIds = array_values(array_unique(array_filter($pageIds, function ($id) {
            return is_numeric($id) && (int) $id > 0;
        })));

        // If the caller didn't provide numeric IDs (common on routes using category slugs/hashes),
        // infer them from the posts so our request-level maps match PostPresenter::hasNotSeen().
        if ($communityId <= 0 && $inferredCommunityIds) {
            $inferredCommunityIds = array_values(array_unique(array_filter($inferredCommunityIds, function ($id) {
                return is_numeric($id) && (int) $id > 0;
            })));
            if (count($inferredCommunityIds) === 1) {
                $communityId = (int) $inferredCommunityIds[0];
            }
        }
        if ($categoryId <= 0 && $inferredCategoryIds) {
            $inferredCategoryIds = array_values(array_unique(array_filter($inferredCategoryIds, function ($id) {
                return is_numeric($id) && (int) $id > 0;
            })));
            if (count($inferredCategoryIds) === 1) {
                $categoryId = (int) $inferredCategoryIds[0];
            }
        }

        $request = request();
        $primedKey = 'feed_post_media_primed:' . md5(implode(',', $postIds) . '|' . $userId . '|' . $communityId . '|' . $categoryId . '|' . $commentsPerPost);
        if ($request->attributes->get($primedKey)) {
            return;
        }
        $request->attributes->set($primedKey, true);

        // Prime counts used by Post::countLikes() / Post::countComments() during render.
        $likeRepo = app('App\\Repositories\\LikeRepository');
        $commentRepo = app('App\\Repositories\\CommentRepository');

        // Be defensive: in some environments (e.g., OPcache not revalidating), an older LikeRepository
        // may be loaded that doesn't yet include the newer priming helpers.
        if (is_object($likeRepo) && method_exists($likeRepo, 'primeCounts')) {
            $likeRepo->primeCounts('post', $postIds);
        } elseif (is_object($likeRepo) && method_exists($likeRepo, 'count')) {
            foreach ($postIds as $postId) {
                $likeRepo->count('post', $postId);
            }
        }

        if (is_object($commentRepo) && method_exists($commentRepo, 'primeCounts')) {
            $commentRepo->primeCounts('post', $postIds);
        }

        if (is_object($likeRepo) && method_exists($likeRepo, 'primeCommentsLikeByPostIds')) {
            $likeRepo->primeCommentsLikeByPostIds($postIds);
        }

        if (is_object($likeRepo) && method_exists($likeRepo, 'primeLatestLikes')) {
            $likeRepo->primeLatestLikes('post', $postIds, 1);
        }
        if ($userId > 0) {
            if (is_object($likeRepo) && method_exists($likeRepo, 'primeHasLiked')) {
                $likeRepo->primeHasLiked('post', $postIds, $userId);
            }

            // Also expose a request attribute map for fast view lookups.
            // This avoids per-post exists() queries in views that still call hasLiked().
            try {
                $hasLikedKey = 'feed_post_has_liked_map:u' . $userId;
                $existing = $request->attributes->get($hasLikedKey);
                if (! is_array($existing)) {
                    $existing = [];
                }

                $missingIds = [];
                foreach ($postIds as $postId) {
                    $postId = (int) $postId;
                    if ($postId > 0 && ! array_key_exists($postId, $existing)) {
                        $missingIds[] = $postId;
                    }
                }

                if ($missingIds) {
                    $likedIds = DB::table('id_likes')
                        ->where('type', '=', 'post')
                        ->where('user_id', '=', $userId)
                        ->whereIn('type_id', $missingIds)
                        ->pluck('type_id');

                    $likedIds = is_object($likedIds) && method_exists($likedIds, 'toArray') ? $likedIds->toArray() : (array) $likedIds;
                    $likedSet = [];
                    foreach ($likedIds as $likedId) {
                        $likedSet[(int) $likedId] = true;
                    }

                    foreach ($missingIds as $postId) {
                        $existing[$postId] = isset($likedSet[$postId]);
                    }

                    $request->attributes->set($hasLikedKey, $existing);
                }
            } catch (\Throwable $e) {
                // Best-effort only
            }
        }

        // Prime hasLiked() for pages referenced by these posts (type='page').
        if ($userId > 0 && $pageIds) {
            if (is_object($likeRepo) && method_exists($likeRepo, 'primeHasLiked')) {
                $likeRepo->primeHasLiked('page', $pageIds, $userId);
            }

            try {
                $hasLikedKey = 'feed_page_has_liked_map:u' . $userId;
                $existing = $request->attributes->get($hasLikedKey);
                if (! is_array($existing)) {
                    $existing = [];
                }

                $missingIds = [];
                foreach ($pageIds as $pageId) {
                    $pageId = (int) $pageId;
                    if ($pageId > 0 && ! array_key_exists($pageId, $existing)) {
                        $missingIds[] = $pageId;
                    }
                }

                if ($missingIds) {
                    $likedIds = DB::table('id_likes')
                        ->where('type', '=', 'page')
                        ->where('user_id', '=', $userId)
                        ->whereIn('type_id', $missingIds)
                        ->pluck('type_id');

                    $likedIds = is_object($likedIds) && method_exists($likedIds, 'toArray') ? $likedIds->toArray() : (array) $likedIds;
                    $likedSet = [];
                    foreach ($likedIds as $likedId) {
                        $likedSet[(int) $likedId] = true;
                    }

                    foreach ($missingIds as $pageId) {
                        $existing[$pageId] = isset($likedSet[$pageId]);
                    }

                    $request->attributes->set($hasLikedKey, $existing);
                }
            } catch (\Throwable $e) {
                // Best-effort only
            }
        }

        // Build request attribute maps for fast Blade lookups.
        // This lets post.media avoid calling Post::countLikes()/countComments() per post.
        try {
            $likesCountByPostId = [];
            $commentLikesCountByPostId = [];
            $totalLikesCountByPostId = [];
            $commentsCountByPostId = [];

            foreach ($postIds as $postId) {
                $postId = (int) $postId;
                if ($postId <= 0) {
                    continue;
                }

                $postLikes = 0;
                if (is_object($likeRepo) && method_exists($likeRepo, 'count')) {
                    $postLikes = (int) $likeRepo->count('post', $postId);
                }

                $commentLikes = 0;
                if (is_object($likeRepo) && method_exists($likeRepo, 'getCommentsLikeByPost')) {
                    $commentLikes = (int) $likeRepo->getCommentsLikeByPost($postId);
                }

                $likesCountByPostId[$postId] = $postLikes;
                $commentLikesCountByPostId[$postId] = $commentLikes;
                $totalLikesCountByPostId[$postId] = $postLikes + $commentLikes;

                if (is_object($commentRepo) && method_exists($commentRepo, 'count')) {
                    $commentsCountByPostId[$postId] = (int) $commentRepo->count('post', $postId);
                }
            }

            $mergeIntoAttr = function (string $key, array $map) use ($request): void {
                if (! $map) {
                    return;
                }
                $existing = $request->attributes->get($key);
                if (! is_array($existing)) {
                    $existing = [];
                }
                // Do not overwrite existing keys set earlier in the request.
                foreach ($map as $id => $value) {
                    if (! array_key_exists($id, $existing)) {
                        $existing[$id] = $value;
                    }
                }
                $request->attributes->set($key, $existing);
            };

            $mergeIntoAttr('feed_post_likes_count_map', $likesCountByPostId);
            $mergeIntoAttr('feed_post_comment_likes_count_map', $commentLikesCountByPostId);
            $mergeIntoAttr('feed_post_total_likes_count_map', $totalLikesCountByPostId);
            $mergeIntoAttr('feed_post_comments_count_map', $commentsCountByPostId);
        } catch (\Throwable $e) {
            // Best-effort only
        }

        // Prime per-request event lookups for this feed to avoid per-post queries.
        try {
            $eventsRepo = app('App\\Repositories\\PostEventsRepository');
            if ($eventsRepo && method_exists($eventsRepo, 'preloadByPostIds')) {
                $eventsRepo->preloadByPostIds($postIds);
            }
        } catch (\Throwable $e) {
            // Best-effort only
        }

        // Prime "has not seen" maps (eliminates per-post exists() queries).
        // Do this per (community_id, category_id/type_id) pair found in the posts,
        // because some feeds may include mixed posts.
        if ($userId > 0) {
            $postSeenRepo = app('App\\Repositories\\PostSeenRepository');
            $canBatchRepo = is_object($postSeenRepo) && method_exists($postSeenRepo, 'mapNotSeenPosts');

            if ($canBatchRepo || class_exists('Illuminate\\Support\\Facades\\DB')) {
                $postIdsByPair = [];
                foreach ($posts as $post) {
                    $pid = 0;
                    $cid = 0;
                    $cat = 0;

                    if (is_object($post)) {
                        $pid = (int) ($post->id ?? 0);
                        $cid = (int) ($post->community_id ?? 0);
                        $cat = (int) ($post->type_id ?? 0);
                    } elseif (is_array($post)) {
                        $pid = (int) ($post['id'] ?? 0);
                        $cid = (int) ($post['community_id'] ?? 0);
                        $cat = (int) ($post['type_id'] ?? 0);
                    }

                    if ($pid <= 0 || $cid <= 0 || $cat <= 0) {
                        continue;
                    }

                    $pairKey = $cid.'|'.$cat;
                    if (! isset($postIdsByPair[$pairKey])) {
                        $postIdsByPair[$pairKey] = [];
                    }
                    $postIdsByPair[$pairKey][] = $pid;
                }

                foreach ($postIdsByPair as $pairKey => $pairPostIds) {
                    [$cid, $cat] = array_map('intval', explode('|', $pairKey, 2));
                    if ($cid <= 0 || $cat <= 0) {
                        continue;
                    }

                    // Store the post IDs for this pair so PostPresenter can lazily batch-fill
                    // the not-seen map if it gets called in a different render path.
                    $pairIdsKey = "feed_post_ids_by_pair:c{$cid}:cat{$cat}";
                    if (! $request->attributes->has($pairIdsKey)) {
                        $request->attributes->set($pairIdsKey, $pairPostIds);
                    }

                    $notSeenKey = "feed_not_seen_map:u{$userId}:c{$cid}:cat{$cat}";
                    if ($request->attributes->has($notSeenKey)) {
                        continue;
                    }

                    $pairPostIds = array_values(array_unique(array_filter(array_map('intval', $pairPostIds), function ($id) {
                        return $id > 0;
                    })));
                    if (! $pairPostIds) {
                        continue;
                    }

                    if ($canBatchRepo) {
                        $notSeen = $postSeenRepo->mapNotSeenPosts($pairPostIds, $cid, $cat, $userId);
                        $request->attributes->set($notSeenKey, $notSeen);
                        continue;
                    }

                    // Fallback: batch query directly (covers stale OPcache where mapNotSeenPosts() is missing).
                    try {
                        $ids = DB::table('id_posts_seen')
                            ->where('user_id', '=', $userId)
                            ->where('type', '=', 'post')
                            ->where('community_id', '=', $cid)
                            ->where('category_id', '=', $cat)
                            ->whereIn('type_id', $pairPostIds)
                            ->where('seen', '=', 0)
                            ->pluck('type_id');

                        $ids = is_object($ids) && method_exists($ids, 'toArray') ? $ids->toArray() : (array) $ids;
                        $map = [];
                        foreach ($ids as $id) {
                            $map[(int) $id] = true;
                        }
                        $request->attributes->set($notSeenKey, $map);
                    } catch (\Throwable $e) {
                        // best-effort
                    }
                }
            }
        }

        // Preload the 3 most recent comments per post and prime comment-like counts.
        $recentCommentsByPostId = [];
        $recentCommentIds = [];

        try {
            $ranked = DB::table('comments')
                ->select(['id', 'type_id'])
                ->selectRaw('ROW_NUMBER() OVER (PARTITION BY type_id ORDER BY id DESC) as rn')
                ->where('type', '=', 'post')
                ->whereIn('type_id', $postIds);

            $top = DB::query()
                ->fromSub($ranked, 'c')
                ->where('rn', '<=', $commentsPerPost)
                ->orderBy('id', 'desc')
                ->get(['id', 'type_id']);

            $commentIds = [];
            $postIdByCommentId = [];
            foreach ($top as $row) {
                $cid = (int) ($row->id ?? 0);
                $pid = (int) ($row->type_id ?? 0);
                if ($cid > 0) {
                    $commentIds[] = $cid;
                    $postIdByCommentId[$cid] = $pid;
                }
            }

            if ($commentIds) {
                $comments = Comment::query()
                    ->with('user')
                    ->whereIn('id', $commentIds)
                    ->orderBy('id', 'desc')
                    ->get();

                foreach ($comments as $comment) {
                    $pid = (int) ($comment->type_id ?? ($postIdByCommentId[(int) $comment->id] ?? 0));
                    if ($pid <= 0) {
                        continue;
                    }

                    if (! isset($recentCommentsByPostId[$pid])) {
                        $recentCommentsByPostId[$pid] = [];
                    }

                    if (count($recentCommentsByPostId[$pid]) < $commentsPerPost) {
                        $recentCommentsByPostId[$pid][] = $comment;
                        $recentCommentIds[] = (int) $comment->id;
                    }
                }
            }
        } catch (\Throwable $e) {
            // Fallback for older MySQL (no window functions). Limits total rows to keep it safe.
            $limit = min(500, $commentsPerPost * max(1, count($postIds)));
            $comments = Comment::query()
                ->with('user')
                ->where('type', '=', 'post')
                ->whereIn('type_id', $postIds)
                ->orderBy('id', 'desc')
                ->limit($limit)
                ->get();

            foreach ($comments as $comment) {
                $pid = (int) $comment->type_id;
                if (! isset($recentCommentsByPostId[$pid])) {
                    $recentCommentsByPostId[$pid] = [];
                }

                if (count($recentCommentsByPostId[$pid]) < $commentsPerPost) {
                    $recentCommentsByPostId[$pid][] = $comment;
                    $recentCommentIds[] = (int) $comment->id;
                }
            }
        }

        if ($recentCommentIds) {
            // Comment displays render "last activity" which calls LikeRepository::getLatestLikes('comment', ...).
            // Prime these to avoid per-comment latest-like queries.
            if (is_object($likeRepo) && method_exists($likeRepo, 'primeLatestLikes')) {
                $likeRepo->primeLatestLikes('comment', $recentCommentIds, 1);
            }

            if (is_object($likeRepo) && method_exists($likeRepo, 'primeCounts')) {
                $likeRepo->primeCounts('comment', $recentCommentIds);
            } elseif (is_object($likeRepo) && method_exists($likeRepo, 'count')) {
                foreach ($recentCommentIds as $commentId) {
                    $likeRepo->count('comment', $commentId);
                }
            }

            if ($userId > 0 && is_object($likeRepo) && method_exists($likeRepo, 'primeHasLiked')) {
                $likeRepo->primeHasLiked('comment', $recentCommentIds, $userId);
            }

            // Prime counts of replies (comments on comments) for the displayed comments.
            // This avoids N+1 queries in comment.display where it renders "(replies count)".
            try {
                if (is_object($commentRepo) && method_exists($commentRepo, 'primeCounts')) {
                    $commentRepo->primeCounts('comment', $recentCommentIds);
                }

                $replyCountMap = [];
                if (is_object($commentRepo) && method_exists($commentRepo, 'count')) {
                    foreach ($recentCommentIds as $commentId) {
                        $commentId = (int) $commentId;
                        if ($commentId <= 0) {
                            continue;
                        }
                        $replyCountMap[$commentId] = (int) $commentRepo->count('comment', $commentId);
                    }
                }

                if ($replyCountMap) {
                    $attrKey = 'feed_comment_replies_count_map';
                    $existing = $request->attributes->get($attrKey);
                    if (! is_array($existing)) {
                        $existing = [];
                    }
                    foreach ($replyCountMap as $commentId => $cnt) {
                        if (! array_key_exists($commentId, $existing)) {
                            $existing[$commentId] = (int) $cnt;
                        }
                    }
                    $request->attributes->set($attrKey, $existing);
                }
            } catch (\Throwable $e) {
                // best-effort
            }

            // Prime the 3 most recent replies for each displayed comment (comments on comments).
            // This eliminates N+1 queries from `$comment->comments->take(3)` in comment.display.
            try {
                $repliesPerComment = 3;
                $recentRepliesByCommentId = [];
                $recentReplyIds = [];

                $rankedReplies = DB::table('comments')
                    ->select(['id', 'type_id'])
                    ->selectRaw('ROW_NUMBER() OVER (PARTITION BY type_id ORDER BY id DESC) as rn')
                    ->where('type', '=', 'comment')
                    ->whereIn('type_id', $recentCommentIds);

                $topReplies = DB::query()
                    ->fromSub($rankedReplies, 'r')
                    ->where('rn', '<=', $repliesPerComment)
                    ->orderBy('id', 'desc')
                    ->get(['id', 'type_id']);

                $replyIds = [];
                $parentByReplyId = [];
                foreach ($topReplies as $row) {
                    $rid = (int) ($row->id ?? 0);
                    $pid = (int) ($row->type_id ?? 0);
                    if ($rid > 0 && $pid > 0) {
                        $replyIds[] = $rid;
                        $parentByReplyId[$rid] = $pid;
                    }
                }

                if ($replyIds) {
                    $replyModels = Comment::query()
                        ->with('user')
                        ->whereIn('id', $replyIds)
                        ->orderBy('id', 'desc')
                        ->get();

                    foreach ($replyModels as $reply) {
                        $rid = (int) ($reply->id ?? 0);
                        $pid = (int) ($reply->type_id ?? ($parentByReplyId[$rid] ?? 0));
                        if ($rid <= 0 || $pid <= 0) {
                            continue;
                        }
                        if (! isset($recentRepliesByCommentId[$pid])) {
                            $recentRepliesByCommentId[$pid] = [];
                        }
                        if (count($recentRepliesByCommentId[$pid]) < $repliesPerComment) {
                            $recentRepliesByCommentId[$pid][] = $reply;
                            $recentReplyIds[] = $rid;
                        }
                    }
                }

                // Fallback: ensure every displayed comment has a key (even empty)
                // so Blade never falls back to `$comment->comments` which queries.
                foreach ($recentCommentIds as $cid) {
                    $cid = (int) $cid;
                    if ($cid > 0 && ! array_key_exists($cid, $recentRepliesByCommentId)) {
                        $recentRepliesByCommentId[$cid] = [];
                    }
                }

                if ($recentReplyIds) {
                    if (is_object($likeRepo) && method_exists($likeRepo, 'primeLatestLikes')) {
                        $likeRepo->primeLatestLikes('comment', $recentReplyIds, 1);
                    }
                    if (is_object($likeRepo) && method_exists($likeRepo, 'primeCounts')) {
                        $likeRepo->primeCounts('comment', $recentReplyIds);
                    }
                    if ($userId > 0 && is_object($likeRepo) && method_exists($likeRepo, 'primeHasLiked')) {
                        $likeRepo->primeHasLiked('comment', $recentReplyIds, $userId);
                    }
                }

                $attrKey = 'feed_comment_recent_replies_map';
                $existing = $request->attributes->get($attrKey);
                if (! is_array($existing)) {
                    $existing = [];
                }
                foreach ($recentRepliesByCommentId as $cid => $list) {
                    if (! array_key_exists((int) $cid, $existing)) {
                        $existing[(int) $cid] = $list;
                    }
                }
                $request->attributes->set($attrKey, $existing);
            } catch (\Throwable $e) {
                // Fallback for older MySQL (no window functions).
                try {
                    $repliesPerComment = 3;
                    $recentRepliesByCommentId = [];
                    $limit = min(500, $repliesPerComment * max(1, count($recentCommentIds)));
                    $replyModels = Comment::query()
                        ->with('user')
                        ->where('type', '=', 'comment')
                        ->whereIn('type_id', $recentCommentIds)
                        ->orderBy('id', 'desc')
                        ->limit($limit)
                        ->get();

                    foreach ($replyModels as $reply) {
                        $pid = (int) ($reply->type_id ?? 0);
                        if ($pid <= 0) {
                            continue;
                        }
                        if (! isset($recentRepliesByCommentId[$pid])) {
                            $recentRepliesByCommentId[$pid] = [];
                        }
                        if (count($recentRepliesByCommentId[$pid]) < $repliesPerComment) {
                            $recentRepliesByCommentId[$pid][] = $reply;
                        }
                    }

                    foreach ($recentCommentIds as $cid) {
                        $cid = (int) $cid;
                        if ($cid > 0 && ! array_key_exists($cid, $recentRepliesByCommentId)) {
                            $recentRepliesByCommentId[$cid] = [];
                        }
                    }

                    $attrKey = 'feed_comment_recent_replies_map';
                    $existing = $request->attributes->get($attrKey);
                    if (! is_array($existing)) {
                        $existing = [];
                    }
                    foreach ($recentRepliesByCommentId as $cid => $list) {
                        if (! array_key_exists((int) $cid, $existing)) {
                            $existing[(int) $cid] = $list;
                        }
                    }
                    $request->attributes->set($attrKey, $existing);
                } catch (\Throwable $e2) {
                    // best-effort only
                }
            }

            // Expose a request attribute map for fast view lookups (avoids per-comment exists() queries).
            if ($userId > 0) {
                try {
                    $hasLikedKey = 'feed_comment_has_liked_map:u' . $userId;
                    $existing = $request->attributes->get($hasLikedKey);
                    if (! is_array($existing)) {
                        $existing = [];
                    }

                    $missingIds = [];
                    foreach ($recentCommentIds as $commentId) {
                        $commentId = (int) $commentId;
                        if ($commentId > 0 && ! array_key_exists($commentId, $existing)) {
                            $missingIds[] = $commentId;
                        }
                    }

                    if ($missingIds) {
                        $likedIds = DB::table('id_likes')
                            ->where('type', '=', 'comment')
                            ->where('user_id', '=', $userId)
                            ->whereIn('type_id', $missingIds)
                            ->pluck('type_id');

                        $likedIds = is_object($likedIds) && method_exists($likedIds, 'toArray') ? $likedIds->toArray() : (array) $likedIds;
                        $likedSet = [];
                        foreach ($likedIds as $likedId) {
                            $likedSet[(int) $likedId] = true;
                        }

                        foreach ($missingIds as $commentId) {
                            $existing[$commentId] = isset($likedSet[$commentId]);
                        }

                        $request->attributes->set($hasLikedKey, $existing);
                    }
                } catch (\Throwable $e) {
                    // Best-effort only
                }
            }
        }

        // Always expose a per-post recent-comments map (even if empty) so Blade can avoid
        // falling back to `$post->comments()->take(3)->get()` which triggers N+1 queries.
        $attrKey = 'feed_recent_comments_map';
        $existing = $request->attributes->get($attrKey);
        if (! is_array($existing)) {
            $existing = [];
        }

        foreach ($postIds as $pid) {
            $pid = (int) $pid;
            if ($pid <= 0) {
                continue;
            }
            if (! array_key_exists($pid, $existing)) {
                $existing[$pid] = $recentCommentsByPostId[$pid] ?? [];
            }
        }

        $request->attributes->set($attrKey, $existing);
    }
    
    /**
     * Clear all optimization caches for a community
     * Call this when community data is updated
     */
    public static function clearCommunityCache($communityId)
    {
        $patterns = [
            "dashboard_categories_$communityId",
            "dashboard_posts_{$communityId}_*",
            "batch_post_categories_{$communityId}_*",
            "category_*_comm_$communityId"
        ];
        
        // Clear specific known keys
        Cache::forget("dashboard_categories_$communityId");
        
        // In production, you might need Redis SCAN or similar for pattern matching
        // For now, we'll clear when we know the keys
    }
}