using Microsoft.EntityFrameworkCore;
using vendorApproverBackend.Data;
using vendorApproverBackend.Entities;
using vendorApproverBackend.Enums;
using vendorApproverBackend.Responses;
using vendorApproverBackend.Requests;
using vendorApproverBackend.DTOs;
using System.Text.Json;
using System.Security.Claims;


using System.Text;
using System.Text.RegularExpressions;

namespace vendorApproverBackend.Services;

public class UserService
{
    private readonly DataContext _context;

    public UserService(DataContext context)
    {
        _context = context;
    }


    public async Task<User?> FindByUsernameWithVendorAsync(string username)
{
    return await _context.Users
        .Include(u => u.Vendor) // This loads the Vendor relationship
        .FirstOrDefaultAsync(u => u.Username == username);
}


    public async Task<User> SaveAdminAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }

        user.Role = RoleEnum.ROLE_ADMIN;
        user.Active = true;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }


     public async Task<User> SaveManagerAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }

        user.Role = RoleEnum.ROLE_MANAGER;
        user.Active = true;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }

     public async Task<User> SavePerformanceManagerAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }

        user.Role = RoleEnum.ROLE_PERFERMANCE_MANAGER;
        user.Active = true;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }

    public async Task<User> SaveAzureUserAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }

        user.Role = RoleEnum.ROLE_RENWE_APPROVER;
        user.Active = true;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }



    public async Task<PagedResult<User>> GetVendorsAsync(int page, int size, string? search, bool? active, long? id){
            // 1. Start with base query: Vendors that aren't deleted
            var query = _context.Users
                .Include(u => u.Vendor) // Ensure Vendor object is returned
                .Where(u => u.Role == RoleEnum.ROLE_VENDOR && !u.Deleted)
                .AsQueryable();

            // 2. Apply Filters
            if (id.HasValue)
            {
                query = query.Where(u => u.Id == id.Value);
            }

            if (active.HasValue)
            {
                query = query.Where(u => u.Active == active.Value);
            }

            if (!string.IsNullOrEmpty(search))
            {
                string lowerSearch = search.ToLower();
                query = query.Where(u => u.Email.ToLower().Contains(lowerSearch) || 
                                         u.Name.ToLower().Contains(lowerSearch));
            }

            // 3. Order and Count
            var totalCount = await query.CountAsync();
            
            var items = await query
                .OrderByDescending(u => u.Timestamp)
                .Skip(page * size)
                .Take(size)
                .ToListAsync();

            return new PagedResult<User>(items, totalCount, page, size);
        }

        public async Task<bool> DeleteUserAsync(long userId)
        {
            var user = await _context.Users.FindAsync(userId);
            if (user == null) return false;

            string suffix = "_" + DateTime.UtcNow.ToString("yyyyMMddHHmmss") + "DELETED";
            
            user.Active = false;
            user.Deleted = true;
            user.Email += suffix;
            user.Username += suffix;

            await _context.SaveChangesAsync();
            return true;
        }

        public async Task<User?> ToggleUserActiveAsync(long userId)
        {
            var user = await _context.Users.FindAsync(userId);
            if (user == null) return null;

            user.Active = !user.Active;
            await _context.SaveChangesAsync();
            
            return user;
        }
    


    public async Task<User?> FindByLoginIdentifierAsync(string loginIdentifier)
    {
        var identifierLower = loginIdentifier.ToLower();
        return await _context.Users
            .Include(u => u.Device)
            .FirstOrDefaultAsync(u => u.Username.ToLower() == identifierLower || u.Email.ToLower() == identifierLower);
    }


    public async Task<List<long>> GetAdminUserIdsAsync()
    {
        return await _context.Users
            .Where(u => u.Role == RoleEnum.ROLE_ADMIN && u.Active && !u.Deleted)
            .Select(u => u.Id)
            .ToListAsync();
    }

    public async Task<List<string>> GetAdminEmailsAsync()
{
    return await _context.Users
        .Where(u => u.Role == RoleEnum.ROLE_ADMIN && u.Active && !u.Deleted)
        .Select(u => u.Email)
        .ToListAsync();
}


     public async Task<List<long>> GetUserIdsBasedonRoleAsync(RoleEnum role)
    {
        return await _context.Users
            .Where(u => u.Role == role && u.Active && !u.Deleted)
            .Select(u => u.Id)
            .ToListAsync();
    }

     public async Task<bool> ExistByIdAsync(long id)
    {
        return await _context.Users
            .AnyAsync(u => u.Id == id);
    }

    public async Task<User?> FindByUsernameAsync(string username)
    {
        return await _context.Users
            .Include(u => u.Device)
            .Include(u => u.Vendor)
            .FirstOrDefaultAsync(u => u.Username.ToLower() == username.ToLower());
    }


    public async Task<User> SaveUserAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }
        user.Role = RoleEnum.ROLE_EMPLOYEE;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }



    public async Task<User> UpdateUserPassAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }

        await _context.SaveChangesAsync();
        return user;
    }


    public async Task<User> SaveFinancialAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }
        user.Role = RoleEnum.ROLE_FINANCIAL;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }


    public async Task<User> UpdateUserRoleAsync(User user, RoleEnum role)
    {

        user.Role = role;

        _context.Users.Update(user);
        await _context.SaveChangesAsync();
        return user;
    }


    public async Task<User> UpdateUserAsync(User user)
    {
        _context.Users.Update(user);
        await _context.SaveChangesAsync();
        return user;
    }

    public async Task<bool> DoesUsernameExistAsync(string username)
    {
        return await _context.Users.AnyAsync(u => u.Username.ToLower() == username.ToLower());
    }

    public async Task<User?> FindByIdAsync(long userId)
    {
        return await _context.Users.FindAsync(userId);
    }

    public async Task UpdatePasswordAsync(User user, string newPassword)
    {
        user.Password = BCrypt.Net.BCrypt.HashPassword(newPassword);
        _context.Users.Update(user);
        await _context.SaveChangesAsync();
    }


    public async Task<bool> ExistByEmailAsync(string email)
    {
        return await _context.Users.AnyAsync(u => u.Email.ToLower() == email.ToLower());
    }


    public string GenerateNewPassword()
    {
        const string prefix = "oetc";
        const int totalLength = 8;
        const string chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789";

        int randomLength = totalLength - prefix.Length;

        var random = new Random();
        var randomPart = new string(Enumerable.Repeat(chars, randomLength)
            .Select(s => s[random.Next(s.Length)]).ToArray());

        return prefix + randomPart;
    }

    public async Task<User?> FindByEmailAsync(string email)
    {
        if (string.IsNullOrWhiteSpace(email)) return null;
        var e = email.ToLower();
        return await _context.Users
            .Include(u => u.Device)
            .FirstOrDefaultAsync(u => u.Email.ToLower() == e);
    }

    public async Task<bool> ExistByPhoneNumberAsync(string? phoneNumber)
    {
        // 1. Handle null or empty phone number
        if (string.IsNullOrWhiteSpace(phoneNumber))
        {
            return false;
        }

        // 2. Check if any user exists with the provided phone number
        return await _context.Users
            .AnyAsync(u => u.PhoneNumber == phoneNumber);
    }

    public async Task<long> GetUserCountAsync()
    {
        return await _context.Users.CountAsync();
    }

    public async Task<User> SaveVendorAsync(User user)
    {
        if (!string.IsNullOrEmpty(user.Password))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(user.Password);
        }

        user.Role = RoleEnum.ROLE_VENDOR;
        user.Active = true;
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }

    public string GenerateUsername(string name, long userCount)
    {
        const string chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
        var random = new Random();
        var randomPart = new StringBuilder(4);
        for (int i = 0; i < 4; i++)
        {
            randomPart.Append(chars[random.Next(chars.Length)]);
        }

        string baseName = "USER";
        if (!string.IsNullOrWhiteSpace(name))
        {
            baseName = Regex.Replace(name, @"\s+", "").ToUpper();
        }

        if (string.IsNullOrEmpty(baseName))
        {
            baseName = "USER";
        }

        string username = $"{baseName}{userCount}{randomPart}";
        username = Regex.Replace(username, "[^A-Za-z0-9]", "");

        return username;
    }



    public async Task<PagedResult<User>> FindUsersByCriteriaAsync(
      int page,
      int size,
      string? search = null,
      RoleEnum? role = null,
      EmployeeTypeEnum? type = null,
      string? categoryName = null,
      bool? active = null,
      bool? status = null,
      bool? deleted = null,
      bool? freeEvaluator = null,
      bool? freeInspector = null)
    {

        var query = _context.Users.AsQueryable();
        if (!string.IsNullOrWhiteSpace(search))
        {
            var searchLower = search.ToLower();
            query = query.Where(u =>
                u.Name.ToLower().Contains(searchLower) ||
                u.Email.ToLower().Contains(searchLower));
        }

        if (role.HasValue)
        {
            query = query.Where(u => u.Role == role.Value);
        }

        if (type.HasValue)
        {
            query = query.Where(u => u.Type == type.Value);
        }

        if (!string.IsNullOrWhiteSpace(categoryName))
        {
            var categoryEntity = await _context.ApplicationCategories
                .FirstOrDefaultAsync(c =>
                    c.Name.ToLower() == categoryName.ToLower() ||
                    c.NameAr.ToLower() == categoryName.ToLower());

            if (categoryEntity != null)
            {
                query = query.Where(u => u.Categories.Any(c => c.Id == categoryEntity.Id));
            }
            else
            {
                // No category found → no results
                query = query.Where(u => false);
            }
        }

        if (active.HasValue)
        {
            query = query.Where(u => u.Active == active.Value);
        }

        if (status.HasValue)
        {
            query = query.Where(u => u.Status == status.Value);
        }

        if (deleted.HasValue)
        {
            query = query.Where(u => u.Deleted == deleted.Value);
        }

        /* if (freeEvaluator.HasValue)
        {
            // true: fetch all that have: EvaluationPqId not null.
            // false: fetch all that have EvaluationPqId is null.
            if (freeEvaluator.Value)
            {
                query = query.Where(u => u.EvaluationPqId != null);
            }
            else
            {
                query = query.Where(u => u.EvaluationPqId == null);
            }
        } */


        if (freeEvaluator.HasValue)
        {
            if (freeEvaluator.Value)
            {
                // Evaluator is FREE: Has 0, 1, or 2 IDs (Count < 3)
                query = query.Where(u => u.RequestsEvaluationIds.Count < 3);
            }
            else
            {
                // Evaluator is BUSY: Has 3 or more IDs (Count >= 3)
                query = query.Where(u => u.RequestsEvaluationIds.Count >= 3);
            }
        }

        if (freeInspector.HasValue)
        {
            // true: fetch all that have: InspectionPqId not null.
            // false: fetch all that have InspectionPqId is null.
            if (freeInspector.Value)
            {
                query = query.Where(u => u.InspectionPqId != null);
            }
            else
            {
                query = query.Where(u => u.InspectionPqId == null);
            }
        }

        query = query.Include(u => u.Categories);

        var totalCount = await query.CountAsync();

        query = query.OrderByDescending(u => u.Timestamp);

        var items = await query
            .Skip(page * size)
            .Take(size)
            .ToListAsync();

        return new PagedResult<User>(items, totalCount, page, size);
    }


    public async Task<List<User>> FindUserByCriteriaList(
    string? search = null,
    RoleEnum? role = null,
    EmployeeTypeEnum? type = null,
    string? categoryName = null,
    bool? active = null,
    bool? status = null,
    bool? deleted = null,
      bool? freeEvaluator = null,
      bool? freeInspector = null)
    {
        var query = _context.Users.AsQueryable();


        if (!string.IsNullOrWhiteSpace(search))
        {
            var searchLower = search.ToLower();
            query = query.Where(u =>
                u.Name.ToLower().Contains(searchLower) ||
                u.Email.ToLower().Contains(searchLower));
        }


        if (role.HasValue)
        {
            query = query.Where(u => u.Role == role.Value);
        }


        if (type.HasValue)
        {
            query = query.Where(u => u.Type == type.Value);
        }


        if (!string.IsNullOrWhiteSpace(categoryName))
        {
            var categoryEntity = await _context.ApplicationCategories
                .FirstOrDefaultAsync(c =>
                    c.Name.ToLower() == categoryName.ToLower() ||
                    c.NameAr.ToLower() == categoryName.ToLower());

            if (categoryEntity != null)
            {
                query = query.Where(u => u.Categories.Any(c => c.Id == categoryEntity.Id));
            }
            else
            {
                query = query.Where(u => false);
            }
        }


        if (active.HasValue)
        {
            query = query.Where(u => u.Active == active.Value);
        }


        if (status.HasValue)
        {
            query = query.Where(u => u.Status == status.Value);
        }


        if (deleted.HasValue)
        {
            query = query.Where(u => u.Deleted == deleted.Value);
        }

        if (freeEvaluator.HasValue)
{
    if (freeEvaluator.Value)
    {
        // Evaluator is FREE: Has 0, 1, or 2 IDs (Count < 3)
        query = query.Where(u => u.RequestsEvaluationIds.Count < 3);
    }
    else
    {
        // Evaluator is BUSY: Has 3 or more IDs (Count >= 3)
        query = query.Where(u => u.RequestsEvaluationIds.Count >= 3);
    }
}

        if (freeInspector.HasValue)
        {
            if (freeInspector.Value)
            {
                query = query.Where(u => u.InspectionPqId != null);
            }
            else
            {
                query = query.Where(u => u.InspectionPqId == null);
            }
        }

        query = query.Include(u => u.Categories);
        query = query.OrderByDescending(u => u.Timestamp);

        var items = await query.ToListAsync();

        return items;
    }


    private async Task LoadAndAssignCategoriesAsync(User user, List<long>? appCategoriesIds)
    {
        if (appCategoriesIds == null || !appCategoriesIds.Any())
        {
            user.Categories.Clear();
            return;
        }

        var categories = await _context.ApplicationCategories
            .Where(ac => appCategoriesIds.Contains(ac.Id))
            .ToListAsync();

        if (categories.Count != appCategoriesIds.Count)
        {
            throw new KeyNotFoundException("One or more ApplicationCategory IDs provided were not found.");
        }

        user.Categories.Clear();

        foreach (var category in categories)
        {
            user.Categories.Add(category);
        }
    }

    private void UpdateUserCategories(User user, List<long>? newCategoriesIds)
    {
        if (newCategoriesIds != null)
        {
            user.Categories.Clear();

            if (newCategoriesIds.Any())
            {

                var newCategories = _context.ApplicationCategories
                    .Where(ac => newCategoriesIds.Contains(ac.Id))
                    .ToList();

                if (newCategories.Count != newCategoriesIds.Count)
                {
                    throw new KeyNotFoundException("One or more ApplicationCategory IDs provided were not found.");
                }

                foreach (var category in newCategories)
                {
                    user.Categories.Add(category);
                }
            }
        }

    }


    public async Task<User> SaveUserAsync(User user, List<long>? appCategoriesIds)
    {
        await LoadAndAssignCategoriesAsync(user, appCategoriesIds);

        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }

    public async Task<User> AddUserAsync(User user)
    {
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }

    public async Task<User> UpdateUserAsync(User user, List<long>? appCategoriesIds)
    {
        var userToUpdate = await _context.Users
            .Include(u => u.Categories)
            .FirstOrDefaultAsync(u => u.Id == user.Id);

        if (userToUpdate == null) return null;

        UpdateUserCategories(userToUpdate, appCategoriesIds);
        _context.Users.Update(userToUpdate);
        await _context.SaveChangesAsync();
        return userToUpdate;
    }


    // 🎯 NEW CORE METHOD FOR EMPLOYEE PROFILE UPDATE
    public async Task<User?> UpdateUserProfileAsync(long userId, UpdateProfileRequest request)
    {
        var user = await _context.Users
            .Include(u => u.Categories)
            .Include(u => u.Degrees)
            .Include(u => u.WorkExperiences)
            .Include(u => u.Certifications)
            .FirstOrDefaultAsync(u => u.Id == userId);

        if (user == null) return null;

        if (request.Name != null) user.Name = request.Name;
        if (request.Gendre != null) user.Gendre = request.Gendre;
        if (request.Email != null) user.Email = request.Email;
        if (request.PhoneNumber != null) user.PhoneNumber = request.PhoneNumber;
        if (request.City != null) user.City = request.City;
        if (request.Country != null) user.Country = request.Country;
        if (request.Code != null) user.Code = request.Code;
        if (request.Departement != null) user.Departement = request.Departement;
        if (request.Position != null) user.Position = request.Position;
        if (request.Active.HasValue) user.Active = request.Active.Value;
        if (request.Status.HasValue) user.Status = request.Status.Value;
        if (request.Deleted.HasValue) user.Deleted = request.Deleted.Value;
        if (request.StartDate.HasValue) user.StartDate = request.StartDate.Value.ToUniversalTime();
        if (request.Type.HasValue) user.Type = request.Type.Value;
        if (request.AppCategoriesIds != null)
        {
            user.Categories.Clear();
            if (request.AppCategoriesIds.Any())
            {
                var categories = await _context.ApplicationCategories
                    .Where(ac => request.AppCategoriesIds.Contains(ac.Id))
                    .ToListAsync();


                foreach (var cat in categories) user.Categories.Add(cat);
            }
        }


        if (request.Degrees != null)
        {
            _context.Degrees.RemoveRange(user.Degrees);
            user.Degrees.Clear();

            if (request.Degrees.Any())
            {
                foreach (var degree in request.Degrees) user.Degrees.Add(degree);
            }
        }

        if (request.WorkExperiences != null)
        {
            _context.WorkExperiences.RemoveRange(user.WorkExperiences);
            user.WorkExperiences.Clear();
            if (request.WorkExperiences.Any())
            {
                foreach (var we in request.WorkExperiences) user.WorkExperiences.Add(we);
            }
        }

        if (request.Certifications != null)
        {
            _context.EmployeeCertifications.RemoveRange(user.Certifications);
            user.Certifications.Clear();
            if (request.Certifications.Any())
            {
                foreach (var cert in request.Certifications) user.Certifications.Add(cert);
            }
        }
        await _context.SaveChangesAsync();

        return user;
    }

    public async Task<User?> UpdateVendorProfileAsync(long userId, UpdateVendorProfile request)
    {
        var user = await _context.Users
            .Include(u => u.Vendor)
            .FirstOrDefaultAsync(u => u.Id == userId);

        if (user == null)
        {
            return null;
        }

        if (user.Vendor == null)
        {
            return user;
        }


        if (request.Name != null) user.Name = request.Name;
        if (request.Gendre.HasValue) user.Gendre = request.Gendre.Value;
        if (request.Country != null) user.Country = request.Country;
        if (request.City != null) user.City = request.City;
        if (request.Code != null) user.Code = request.Code;
        if (request.PhoneNumber != null) user.PhoneNumber = request.PhoneNumber;

        var vendor = user.Vendor;

        if (request.ManifactoryName != null) vendor.ManifactoryName = request.ManifactoryName;
        if (request.LocalAgentName != null) vendor.LocalAgentName = request.LocalAgentName;
        if (request.LocalAgentPhone != null) vendor.LocalAgentPhone = request.LocalAgentPhone;
        if (request.CommercialRegistrationNo != null) vendor.CommercialRegistrationNo = request.CommercialRegistrationNo;
        if (request.RepresentativeName != null) vendor.RepresentativeName = request.RepresentativeName;
        if (request.RepresentativeEmail != null) vendor.RepresentativeEmail = request.RepresentativeEmail;
        if (request.Position != null) vendor.Position = request.Position;
        if (request.PartnerCompany != null) vendor.PartnerCompany = request.PartnerCompany;
        await _context.SaveChangesAsync();

        return user;
    }

    public async Task<User?> UpdateUserPasswordAsync(long userId, UpdatePasswordRequest request)
    {
        var user = await _context.Users.FirstOrDefaultAsync(u => u.Id == userId);

        if (user == null)
        {
            return null;
        }


        if (string.IsNullOrEmpty(user.Password) || !BCrypt.Net.BCrypt.Verify(request.OldPassword, user.Password))
        {

            throw new InvalidOperationException("Old password is not correct.");
        }

        if (!string.IsNullOrEmpty(request.NewPassword))
        {
            user.Password = BCrypt.Net.BCrypt.HashPassword(request.NewPassword);
        }

        await _context.SaveChangesAsync();

        return user;
    }

    /* private async Task<List<EnrichedUser>> EnrichUsersAsync(List<User> users)
    {
        var enrichedUsers = new List<EnrichedUser>();

        // 1. Collect all unique Pq IDs that need to be fetched
        var evaluationPqIds = users
            .Where(u => u.EvaluationPqId.HasValue)
            .Select(u => u.EvaluationPqId!.Value)
            .Distinct();

        var inspectionPqIds = users
            .Where(u => u.InspectionPqId.HasValue)
            .Select(u => u.InspectionPqId!.Value)
            .Distinct();

        var allPqIds = evaluationPqIds.Union(inspectionPqIds).ToList();

        // 2. Fetch all necessary PrequalificationRequests in one go for efficiency
        var prequalificationRequests = await _context.PrequalificationRequests
            .Where(pq => allPqIds.Contains(pq.Id))
            // Include related entities for PrequalificationRequest here if necessary
            // e.g., .Include(pq => pq.Vendor)
            .ToDictionaryAsync(pq => pq.Id, pq => pq);

        // 3. Loop through users, convert to DTO, and assign the related PQ objects
        foreach (var user in users)
        {
            // Use serialization to copy all properties from User to EnrichedUser
            var userJson = JsonSerializer.Serialize(user);
            var enrichedUser = JsonSerializer.Deserialize<EnrichedUser>(userJson)!;

            // Enrich the DTO with PrequalificationRequest objects
            if (enrichedUser.EvaluationPqId.HasValue &&
                prequalificationRequests.TryGetValue(enrichedUser.EvaluationPqId.Value, out var evalPq))
            {
                enrichedUser.EvaluationPq = evalPq;
            }

            if (enrichedUser.InspectionPqId.HasValue &&
                prequalificationRequests.TryGetValue(enrichedUser.InspectionPqId.Value, out var inspPq))
            {
                enrichedUser.InspectionPq = inspPq;
            }

            enrichedUsers.Add(enrichedUser);
        }

        return enrichedUsers;
    }
     */

     private async Task<List<EnrichedUser>> EnrichUsersAsync(List<User> users)
{
    var enrichedUsers = new List<EnrichedUser>();

    // 1. Collect all unique IDs from the LISTS of all users
    // SelectMany flattens all IDs from all users into one flat collection
    var evaluationPqIds = users
        .SelectMany(u => u.RequestsEvaluationIds ?? new List<long>())
        .Distinct();

    var inspectionPqIds = users
        .Where(u => u.InspectionPqId.HasValue)
        .Select(u => u.InspectionPqId!.Value)
        .Distinct();

    var allPqIds = evaluationPqIds.Union(inspectionPqIds).ToList();

    // 2. Fetch all necessary PrequalificationRequests in one DB call
    var prequalificationRequests = await _context.PrequalificationRequests
        .Where(pq => allPqIds.Contains(pq.Id))
        .ToDictionaryAsync(pq => pq.Id, pq => pq);

    // 3. Loop through users, convert to DTO, and assign the related PQ objects
    foreach (var user in users)
    {
        // Use serialization to copy all properties (including the new List of IDs)
        var userJson = JsonSerializer.Serialize(user);
        var enrichedUser = JsonSerializer.Deserialize<EnrichedUser>(userJson)!;

        // Initialize the list in the DTO
        enrichedUser.EvaluationsPq = new List<PrequalificationRequest>();

        // ENRICH EVALUATIONS: Loop through the IDs in the user's list
        if (user.RequestsEvaluationIds != null)
        {
            foreach (var id in user.RequestsEvaluationIds)
            {
                if (prequalificationRequests.TryGetValue(id, out var evalPq))
                {
                    enrichedUser.EvaluationsPq.Add(evalPq);
                }
            }
        }

        // ENRICH INSPECTION: Remains single ID logic
        if (user.InspectionPqId.HasValue &&
            prequalificationRequests.TryGetValue(user.InspectionPqId.Value, out var inspPq))
        {
            enrichedUser.InspectionPq = inspPq;
        }

        enrichedUsers.Add(enrichedUser);
    }

    return enrichedUsers;
}


    // =======================================================================
    // UPDATED FindUsersByCriteriaAsync (Returns PagedResult<EnrichedUser>)
    // =======================================================================

    public async Task<PagedResult<EnrichedUser>> FindEnrichedUserByCriteriaAsync( // Changed return type
      int page,
      int size,
      string? search = null,
      RoleEnum? role = null,
      EmployeeTypeEnum? type = null,
      string? categoryName = null,
      bool? active = null,
      bool? status = null,
      bool? deleted = null,
      bool? freeEvaluator = null,
      bool? freeInspector = null)
    {

        var query = _context.Users.AsQueryable();
        if (!string.IsNullOrWhiteSpace(search))
        {
            var searchLower = search.ToLower();
            query = query.Where(u =>
                u.Name.ToLower().Contains(searchLower) ||
                u.Email.ToLower().Contains(searchLower));
        }

        if (role.HasValue)
        {
            query = query.Where(u => u.Role == role.Value);
        }

        if (type.HasValue)
        {
            query = query.Where(u => u.Type == type.Value);
        }

        if (!string.IsNullOrWhiteSpace(categoryName))
        {
            var categoryEntity = await _context.ApplicationCategories
                .FirstOrDefaultAsync(c =>
                    c.Name.ToLower() == categoryName.ToLower() ||
                    c.NameAr.ToLower() == categoryName.ToLower());

            if (categoryEntity != null)
            {
                query = query.Where(u => u.Categories.Any(c => c.Id == categoryEntity.Id));
            }
            else
            {
                query = query.Where(u => false);
            }
        }

        if (active.HasValue)
        {
            query = query.Where(u => u.Active == active.Value);
        }

        if (status.HasValue)
        {
            query = query.Where(u => u.Status == status.Value);
        }

        if (deleted.HasValue)
        {
            query = query.Where(u => u.Deleted == deleted.Value);
        }

        if (freeEvaluator.HasValue)
{
    if (freeEvaluator.Value)
    {
        // Evaluator is FREE: Has 0, 1, or 2 IDs (Count < 3)
        query = query.Where(u => u.RequestsEvaluationIds.Count < 3);
    }
    else
    {
        // Evaluator is BUSY: Has 3 or more IDs (Count >= 3)
        query = query.Where(u => u.RequestsEvaluationIds.Count >= 3);
    }
}

        if (freeInspector.HasValue)
        {
            if (freeInspector.Value)
            {
                query = query.Where(u => u.InspectionPqId != null);
            }
            else
            {
                query = query.Where(u => u.InspectionPqId == null);
            }
        }

        query = query.Include(u => u.Categories);

        var totalCount = await query.CountAsync();

        query = query.OrderByDescending(u => u.Timestamp);

        var items = await query
            .Skip(page * size)
            .Take(size)
            .ToListAsync();

        // Final step: Enrich and convert to DTO
        var enrichedItems = await EnrichUsersAsync(items);

        return new PagedResult<EnrichedUser>(enrichedItems, totalCount, page, size); // Return DTO
    }



    public async Task<List<EnrichedUser>> FindeEnrichedUserByCriteriaList( // Changed return type
    string? search = null,
    RoleEnum? role = null,
    EmployeeTypeEnum? type = null,
    string? categoryName = null,
    bool? active = null,
    bool? status = null,
    bool? deleted = null,
      bool? freeEvaluator = null,
      bool? freeInspector = null)
    {
        var query = _context.Users.AsQueryable();

        if (!string.IsNullOrWhiteSpace(search))
        {
            var searchLower = search.ToLower();
            query = query.Where(u =>
                u.Name.ToLower().Contains(searchLower) ||
                u.Email.ToLower().Contains(searchLower));
        }


        if (role.HasValue)
        {
            query = query.Where(u => u.Role == role.Value);
        }


        if (type.HasValue)
        {
            query = query.Where(u => u.Type == type.Value);
        }


        if (!string.IsNullOrWhiteSpace(categoryName))
        {
            var categoryEntity = await _context.ApplicationCategories
                .FirstOrDefaultAsync(c =>
                    c.Name.ToLower() == categoryName.ToLower() ||
                    c.NameAr.ToLower() == categoryName.ToLower());

            if (categoryEntity != null)
            {
                query = query.Where(u => u.Categories.Any(c => c.Id == categoryEntity.Id));
            }
            else
            {
                query = query.Where(u => false);
            }
        }


        if (active.HasValue)
        {
            query = query.Where(u => u.Active == active.Value);
        }


        if (status.HasValue)
        {
            query = query.Where(u => u.Status == status.Value);
        }


        if (deleted.HasValue)
        {
            query = query.Where(u => u.Deleted == deleted.Value);
        }

        if (freeEvaluator.HasValue)
{
    if (freeEvaluator.Value)
    {
        // Evaluator is FREE: Has 0, 1, or 2 IDs (Count < 3)
        query = query.Where(u => u.RequestsEvaluationIds.Count < 3);
    }
    else
    {
        // Evaluator is BUSY: Has 3 or more IDs (Count >= 3)
        query = query.Where(u => u.RequestsEvaluationIds.Count >= 3);
    }
}

        if (freeInspector.HasValue)
        {
            if (freeInspector.Value)
            {
                query = query.Where(u => u.InspectionPqId != null);
            }
            else
            {
                query = query.Where(u => u.InspectionPqId == null);
            }
        }

        query = query.Include(u => u.Categories);
        query = query.OrderByDescending(u => u.Timestamp);

        var items = await query.ToListAsync();

        // Final step: Enrich and convert to DTO
        return await EnrichUsersAsync(items);
    }

    public async Task<List<User>> GetPerformanceEvaluatorsAsync()
{
    return await _context.Users
        .Where(u => u.Type == EmployeeTypeEnum.PERFORMANCE_EVALUATOR && u.Active && !u.Deleted)
        .OrderByDescending(u => u.Timestamp)
        .ToListAsync();
}

}