using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;
using Nuuru.Server.Constants;
using Nuuru.Server.Data;
using Nuuru.Server.DTOs;
using Nuuru.Server.DTOs.Members;
using Nuuru.Server.Models;

namespace Nuuru.Server.Services
{
    // Helper class for EF Core query translation
    internal class UserStat
    {
        public Guid UserId { get; set; }
        public int StatValue { get; set; }
    }

    public interface IMemberService
    {
        Task<PagedResult<MemberListItemDto>> GetMembersAsync(string? search, MemberSortType sort, SortDirection direction, TimePeriod period, int page, int pageSize);
    }

    public class MemberService : IMemberService
    {
        private readonly UserManager<ApplicationUser> _userManager;
        private readonly ApplicationDbContext _context;
        private readonly IUserBadgeService _userBadgeService;

        public MemberService(
            UserManager<ApplicationUser> userManager,
            ApplicationDbContext context,
            IUserBadgeService userBadgeService)
        {
            _userManager = userManager;
            _context = context;
            _userBadgeService = userBadgeService;
        }

        public async Task<PagedResult<MemberListItemDto>> GetMembersAsync(string? search, MemberSortType sort, SortDirection direction, TimePeriod period, int page, int pageSize)
        {
            page = Math.Max(1, page);
            pageSize = Math.Clamp(pageSize, 1, 100);

            // Calculate the cutoff date for time period filtering
            DateTime? cutoffDate = period switch
            {
                TimePeriod.Day => DateTime.UtcNow.AddDays(-1),
                TimePeriod.Week => DateTime.UtcNow.AddDays(-7),
                TimePeriod.Month => DateTime.UtcNow.AddMonths(-1),
                TimePeriod.Year => DateTime.UtcNow.AddYears(-1),
                _ => null // AllTime
            };

            // For leaderboards with time filtering, use an optimized approach:
            // aggregate from the relevant table first, then join to users
            if (cutoffDate != null && sort != MemberSortType.Newest)
            {
                return await GetMembersWithTimeFilterAsync(search, sort, direction, cutoffDate.Value, page, pageSize);
            }

            // For AllTime or Newest sort, use the standard approach
            return await GetMembersAllTimeAsync(search, sort, direction, page, pageSize);
        }

        private async Task<PagedResult<MemberListItemDto>> GetMembersAllTimeAsync(string? search, MemberSortType sort, SortDirection direction, int page, int pageSize)
        {
            var query = _userManager.Users.AsQueryable();

            query = query.Where(u => !u.IsSystemAccount);

            if (!string.IsNullOrWhiteSpace(search))
            {
                var searchLower = search.ToLower();
                query = query.Where(u => u.UserName != null && u.UserName.ToLower().Contains(searchLower));
            }

            var projectedQuery = query.Select(u => new
            {
                u.Id,
                u.UserName,
                u.AvatarStorageIdentifier,
                u.DateCreated,
                TotalPosts = _context.BooruPosts.Count(p => p.UploaderId == u.Id),
                TotalForumThreads = _context.ForumThreads.Count(t => t.AuthorId == u.Id),
                TotalForumPosts = _context.ForumPosts.Count(fp => fp.AuthorId == u.Id),
                TotalComments = _context.BooruComments.Count(c => c.UserId == u.Id),
                UpvotesReceived = _context.BooruPostVotes.Count(v => v.Post.UploaderId == u.Id && v.Value == 1),
                DownvotesReceived = _context.BooruPostVotes.Count(v => v.Post.UploaderId == u.Id && v.Value == -1),
                FavoritesReceived = _context.BooruPostFavorites.Count(f => f.Post.UploaderId == u.Id),
                u.ReactionScore
            });

            var orderedQuery = sort switch
            {
                MemberSortType.Posts => projectedQuery.OrderByDescending(m => m.TotalPosts).ThenByDescending(m => m.DateCreated),
                MemberSortType.ForumThreads => projectedQuery.OrderByDescending(m => m.TotalForumThreads).ThenByDescending(m => m.DateCreated),
                MemberSortType.ForumPosts => projectedQuery.OrderByDescending(m => m.TotalForumPosts).ThenByDescending(m => m.DateCreated),
                MemberSortType.Comments => projectedQuery.OrderByDescending(m => m.TotalComments).ThenByDescending(m => m.DateCreated),
                MemberSortType.Upvotes => projectedQuery.OrderByDescending(m => m.UpvotesReceived).ThenByDescending(m => m.DateCreated),
                MemberSortType.Downvotes => projectedQuery.OrderByDescending(m => m.DownvotesReceived).ThenByDescending(m => m.DateCreated),
                MemberSortType.Favorites => projectedQuery.OrderByDescending(m => m.FavoritesReceived).ThenByDescending(m => m.DateCreated),
                MemberSortType.ReactionScore when direction == SortDirection.Ascending =>
                    projectedQuery.OrderBy(m => m.ReactionScore).ThenByDescending(m => m.DateCreated),
                MemberSortType.ReactionScore =>
                    projectedQuery.OrderByDescending(m => m.ReactionScore).ThenByDescending(m => m.DateCreated),
                _ => projectedQuery.OrderByDescending(m => m.DateCreated)
            };

            var totalCount = await query.CountAsync();

            var members = await orderedQuery
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            var userIds = members.Select(m => m.Id).ToList();
            var displayInfoMap = await _userBadgeService.GetUsersDisplayInfoAsync(userIds);

            var items = members.Select(m =>
            {
                var displayInfo = displayInfoMap.TryGetValue(m.Id, out var info) ? info : null;
                return new MemberListItemDto
                {
                    Id = m.Id,
                    UserName = m.UserName ?? string.Empty,
                    AvatarUrl = m.AvatarStorageIdentifier != null
                        ? $"/api/user/{m.UserName}/avatar?v={m.AvatarStorageIdentifier[..8]}"
                        : null,
                    RoleColor = displayInfo?.RoleColor,
                    RoleName = displayInfo?.RoleName,
                    ActiveBanZones = displayInfo?.ActiveBanZones.Select(z => z.ToString()).ToList() ?? [],
                    ActiveBans = displayInfo?.ActiveBans ?? [],
                    ClanId = displayInfo?.ClanId,
                    ClanTag = displayInfo?.ClanTag,
                    ClanColor = displayInfo?.ClanColor,
                    ClanBadgeUrl = displayInfo?.ClanBadgeUrl,
                    DateCreated = m.DateCreated,
                    TotalPosts = m.TotalPosts,
                    TotalForumThreads = m.TotalForumThreads,
                    TotalForumPosts = m.TotalForumPosts,
                    TotalComments = m.TotalComments,
                    UpvotesReceived = m.UpvotesReceived,
                    DownvotesReceived = m.DownvotesReceived,
                    FavoritesReceived = m.FavoritesReceived,
                    ReactionScore = m.ReactionScore
                };
            }).ToList();

            return new PagedResult<MemberListItemDto>
            {
                Items = items,
                TotalCount = totalCount,
                Page = page,
                PageSize = pageSize
            };
        }

        private async Task<PagedResult<MemberListItemDto>> GetMembersWithTimeFilterAsync(string? search, MemberSortType sort, SortDirection direction, DateTime cutoffDate, int page, int pageSize)
        {
            // Build a query that aggregates from the relevant table first (more efficient for time-filtered queries)
            // Only compute the stat we're sorting by
            IQueryable<UserStat> statsQuery = sort switch
            {
                MemberSortType.Posts => _context.BooruPosts
                    .Where(p => p.UploadedAt >= cutoffDate)
                    .GroupBy(p => p.UploaderId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.ForumThreads => _context.ForumThreads
                    .Where(t => t.CreatedAt >= cutoffDate)
                    .GroupBy(t => t.AuthorId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.ForumPosts => _context.ForumPosts
                    .Where(fp => fp.CreatedAt >= cutoffDate)
                    .GroupBy(fp => fp.AuthorId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.Comments => _context.BooruComments
                    .Where(c => c.CreatedAt >= cutoffDate)
                    .GroupBy(c => c.UserId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.Upvotes => _context.BooruPostVotes
                    .Where(v => v.Value == 1 && v.CreatedAt >= cutoffDate)
                    .GroupBy(v => v.Post.UploaderId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.Downvotes => _context.BooruPostVotes
                    .Where(v => v.Value == -1 && v.CreatedAt >= cutoffDate)
                    .GroupBy(v => v.Post.UploaderId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.Favorites => _context.BooruPostFavorites
                    .Where(f => f.CreatedAt >= cutoffDate)
                    .GroupBy(f => f.Post.UploaderId)
                    .Select(g => new UserStat { UserId = g.Key, StatValue = g.Count() }),

                MemberSortType.ReactionScore => GetReactionScoreStatsQuery(cutoffDate),

                _ => throw new ArgumentException("Invalid sort type for time filter")
            };

            // Join with users and apply search filter
            var joined = from stat in statsQuery
                         join user in _userManager.Users on stat.UserId equals user.Id
                         where !user.IsSystemAccount && (string.IsNullOrWhiteSpace(search) || (user.UserName != null && user.UserName.ToLower().Contains(search.ToLower())))
                         select new
                         {
                             user.Id,
                             user.UserName,
                             user.AvatarStorageIdentifier,
                             user.DateCreated,
                             stat.StatValue
                         };

            var query = sort == MemberSortType.ReactionScore && direction == SortDirection.Ascending
                ? joined.OrderBy(m => m.StatValue).ThenByDescending(m => m.DateCreated)
                : joined.OrderByDescending(m => m.StatValue).ThenByDescending(m => m.DateCreated);

            var totalCount = await query.CountAsync();

            var members = await query
                .Skip((page - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            var userIds = members.Select(m => m.Id).ToList();
            var displayInfoMap = await _userBadgeService.GetUsersDisplayInfoAsync(userIds);

            var items = members.Select(m =>
            {
                var displayInfo = displayInfoMap.TryGetValue(m.Id, out var info) ? info : null;
                var dto = new MemberListItemDto
                {
                    Id = m.Id,
                    UserName = m.UserName ?? string.Empty,
                    AvatarUrl = m.AvatarStorageIdentifier != null
                        ? $"/api/user/{m.UserName}/avatar?v={m.AvatarStorageIdentifier[..8]}"
                        : null,
                    RoleColor = displayInfo?.RoleColor,
                    RoleName = displayInfo?.RoleName,
                    ActiveBanZones = displayInfo?.ActiveBanZones.Select(z => z.ToString()).ToList() ?? [],
                    ActiveBans = displayInfo?.ActiveBans ?? [],
                    ClanId = displayInfo?.ClanId,
                    ClanTag = displayInfo?.ClanTag,
                    ClanColor = displayInfo?.ClanColor,
                    ClanBadgeUrl = displayInfo?.ClanBadgeUrl,
                    DateCreated = m.DateCreated
                };

                // Set the appropriate stat field based on sort type
                switch (sort)
                {
                    case MemberSortType.Posts: dto.TotalPosts = m.StatValue; break;
                    case MemberSortType.ForumThreads: dto.TotalForumThreads = m.StatValue; break;
                    case MemberSortType.ForumPosts: dto.TotalForumPosts = m.StatValue; break;
                    case MemberSortType.Comments: dto.TotalComments = m.StatValue; break;
                    case MemberSortType.Upvotes: dto.UpvotesReceived = m.StatValue; break;
                    case MemberSortType.Downvotes: dto.DownvotesReceived = m.StatValue; break;
                    case MemberSortType.Favorites: dto.FavoritesReceived = m.StatValue; break;
                    case MemberSortType.ReactionScore: dto.ReactionScore = m.StatValue; break;
                }

                return dto;
            }).ToList();

            return new PagedResult<MemberListItemDto>
            {
                Items = items,
                TotalCount = totalCount,
                Page = page,
                PageSize = pageSize
            };
        }
        private IQueryable<UserStat> GetReactionScoreStatsQuery(DateTime cutoffDate)
        {
            var positiveEmotes = Emotes.GetAll().Where(e => Emotes.GetValue(e) > 0).ToList();
            var negativeEmotes = Emotes.GetAll().Where(e => Emotes.GetValue(e) < 0).ToList();

            // Resolve content owner for each reaction via entity joins, excluding self-reactions
            // Project each source into (OwnerId, Score) so they can be unioned
            var booruPostReactions = from r in _context.Reactions
                                     join p in _context.BooruPosts on r.EntityId equals p.Id
                                     where r.EntityType == ReactionEntityType.BooruPost
                                           && r.CreatedAt >= cutoffDate
                                           && r.UserId != p.UploaderId
                                     select new { OwnerId = p.UploaderId, Score = positiveEmotes.Contains(r.EmoteName) ? 1 : negativeEmotes.Contains(r.EmoteName) ? -1 : 0 };

            var booruCommentReactions = from r in _context.Reactions
                                        join c in _context.BooruComments on r.EntityId equals c.Id
                                        where r.EntityType == ReactionEntityType.BooruComment
                                              && r.CreatedAt >= cutoffDate
                                              && r.UserId != c.UserId
                                        select new { OwnerId = c.UserId, Score = positiveEmotes.Contains(r.EmoteName) ? 1 : negativeEmotes.Contains(r.EmoteName) ? -1 : 0 };

            var forumPostReactions = from r in _context.Reactions
                                     join fp in _context.ForumPosts on r.EntityId equals fp.Id
                                     where r.EntityType == ReactionEntityType.ForumPost
                                           && r.CreatedAt >= cutoffDate
                                           && r.UserId != fp.AuthorId
                                     select new { OwnerId = fp.AuthorId, Score = positiveEmotes.Contains(r.EmoteName) ? 1 : negativeEmotes.Contains(r.EmoteName) ? -1 : 0 };

            // Post votes (upvote +1, downvote -1), excluding self-votes
            var postVotes = from v in _context.BooruPostVotes
                            join p in _context.BooruPosts on v.PostId equals p.Id
                            where v.CreatedAt >= cutoffDate
                                  && v.UserId != p.UploaderId
                            select new { OwnerId = p.UploaderId, Score = v.Value };

            var allScores = booruPostReactions
                .Concat(booruCommentReactions)
                .Concat(forumPostReactions)
                .Concat(postVotes);

            return allScores
                .GroupBy(r => r.OwnerId)
                .Select(g => new UserStat
                {
                    UserId = g.Key,
                    StatValue = g.Sum(r => r.Score)
                });
        }
    }
}