using Marco.Pms.DataAccess.Data;
using Marco.Pms.Model.Dtos.Attendance;
using Marco.Pms.Model.Employees;
using Marco.Pms.Model.Mail;
using Marco.Pms.Model.MongoDBModels.Project;
using Marco.Pms.Model.Utilities;
using Marco.Pms.Model.ViewModels.Report;
using MarcoBMS.Services.Service;
using Microsoft.EntityFrameworkCore;
using System.Globalization;

namespace Marco.Pms.Services.Helpers
{
    public class ReportHelper
    {
        private readonly ApplicationDbContext _context;
        private readonly IEmailSender _emailSender;
        private readonly ILoggingService _logger;
        private readonly CacheUpdateHelper _cache;
        public ReportHelper(ApplicationDbContext context, IEmailSender emailSender, ILoggingService logger, CacheUpdateHelper cache)
        {
            _context = context;
            _emailSender = emailSender;
            _logger = logger;
            _cache = cache;
        }

        public async Task<ProjectStatisticReport?> GetDailyProjectReportWithOutTenant(Guid projectId)
        {
            // await _cache.GetBuildingAndFloorByWorkAreaId();
            DateTime reportDate = DateTime.UtcNow.AddDays(-1).Date;
            var project = await _cache.GetProjectDetailsWithBuildings(projectId);
            if (project == null)
            {
                var projectSQL = await _context.Projects
               .AsNoTracking()
               .FirstOrDefaultAsync(p => p.Id == projectId);
                if (projectSQL != null)
                {
                    project = new ProjectMongoDB
                    {
                        Id = projectSQL.Id.ToString(),
                        Name = projectSQL.Name,
                        ShortName = projectSQL.ShortName,
                        ProjectAddress = projectSQL.ProjectAddress,
                        ContactPerson = projectSQL.ContactPerson
                    };
                    await _cache.AddProjectDetails(projectSQL);
                }
            }
            if (project != null)
            {

                var statisticReport = new ProjectStatisticReport
                {
                    Date = reportDate,
                    ProjectName = project.Name ?? "",
                    TimeStamp = DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss", CultureInfo.InvariantCulture)
                };

                // Preload relevant data
                var projectAllocations = await _context.ProjectAllocations
                    .Include(p => p.Employee)
                    .Where(p => p.ProjectId == projectId && p.IsActive)
                    .ToListAsync();

                var assignedEmployeeIds = projectAllocations.Select(p => p.EmployeeId).ToHashSet();

                var attendances = await _context.Attendes
                    .AsNoTracking()
                    .Where(a => a.ProjectID == projectId && a.InTime != null && a.InTime.Value.Date == reportDate)
                    .ToListAsync();

                var checkedInEmployeeIds = attendances.Select(a => a.EmployeeId).Distinct().ToHashSet();
                var checkoutPendingIds = attendances.Where(a => a.OutTime == null).Select(a => a.EmployeeId).Distinct().ToHashSet();
                var regularizationIds = attendances
                    .Where(a => a.Activity == ATTENDANCE_MARK_TYPE.REQUEST_REGULARIZE)
                    .Select(a => a.EmployeeId).Distinct().ToHashSet();

                // Preload buildings, floors, areas
                List<BuildingMongoDBVM>? buildings = null;
                List<FloorMongoDBVM>? floors = null;
                List<WorkAreaMongoDB>? areas = null;
                List<WorkItemMongoDB>? workItems = null;

                // Fetch Buildings
                buildings = project.Buildings
                .Select(b => new BuildingMongoDBVM
                {
                    Id = b.Id,
                    ProjectId = b.ProjectId,
                    BuildingName = b.BuildingName,
                    Description = b.Description
                }).ToList();
                if (!buildings.Any())
                {
                    buildings = await _context.Buildings
                    .Where(b => b.ProjectId == projectId)
                    .Select(b => new BuildingMongoDBVM
                    {
                        Id = b.Id.ToString(),
                        ProjectId = b.ProjectId.ToString(),
                        BuildingName = b.Name,
                        Description = b.Description
                    })
                    .ToListAsync();
                }

                // fetch Floors
                floors = project.Buildings
                .SelectMany(b => b.Floors.Select(f => new FloorMongoDBVM
                {
                    Id = f.Id.ToString(),
                    BuildingId = f.BuildingId,
                    FloorName = f.FloorName
                })).ToList();
                if (!floors.Any())
                {
                    var buildingIds = buildings.Select(b => Guid.Parse(b.Id)).ToList();
                    floors = await _context.Floor
                    .Where(f => buildingIds.Contains(f.BuildingId))
                    .Select(f => new FloorMongoDBVM
                    {
                        Id = f.Id.ToString(),
                        BuildingId = f.BuildingId.ToString(),
                        FloorName = f.FloorName
                    })
                    .ToListAsync();
                }

                // fetch Work Areas
                areas = project.Buildings
                .SelectMany(b => b.Floors)
                .SelectMany(f => f.WorkAreas).ToList();
                if (!areas.Any())
                {
                    var floorIds = floors.Select(f => Guid.Parse(f.Id)).ToList();
                    areas = await _context.WorkAreas
                    .Where(a => floorIds.Contains(a.FloorId))
                    .Select(wa => new WorkAreaMongoDB
                    {
                        Id = wa.Id.ToString(),
                        FloorId = wa.FloorId.ToString(),
                        AreaName = wa.AreaName,
                    })
                    .ToListAsync();
                }

                var areaIds = areas.Select(a => Guid.Parse(a.Id)).ToList();

                // fetch Work Items
                workItems = await _cache.GetWorkItemsByWorkAreaIds(areaIds, new List<Guid>());
                if (workItems == null || !workItems.Any())
                {
                    workItems = await _context.WorkItems
                    .Include(w => w.ActivityMaster)
                    .Where(w => areaIds.Contains(w.WorkAreaId))
                    .Select(wi => new WorkItemMongoDB
                    {
                        Id = wi.Id.ToString(),
                        WorkAreaId = wi.WorkAreaId.ToString(),
                        PlannedWork = wi.PlannedWork,
                        CompletedWork = wi.CompletedWork,
                        Description = wi.Description,
                        TaskDate = wi.TaskDate,
                        ActivityMaster = new ActivityMasterMongoDB
                        {
                            ActivityName = wi.ActivityMaster != null ? wi.ActivityMaster.ActivityName : null,
                            UnitOfMeasurement = wi.ActivityMaster != null ? wi.ActivityMaster.UnitOfMeasurement : null
                        }
                    })
                    .ToListAsync();
                }

                var itemIds = workItems.Select(i => Guid.Parse(i.Id)).ToList();

                var tasks = await _context.TaskAllocations
                    .Where(t => itemIds.Contains(t.WorkItemId))
                    .ToListAsync();

                var taskIds = tasks.Select(t => t.Id).ToList();

                var taskMembers = await _context.TaskMembers
                    .Include(m => m.Employee)
                    .Where(m => taskIds.Contains(m.TaskAllocationId))
                    .ToListAsync();

                // Aggregate data
                double totalPlannedWork = workItems.Sum(w => w.PlannedWork);
                double totalCompletedWork = workItems.Sum(w => w.CompletedWork);

                var todayAssignedTasks = tasks.Where(t => t.AssignmentDate.Date == reportDate).ToList();
                var todaysCompletedTasks = tasks.Where(t => t.AssignmentDate.Date == reportDate && t.ReportedById != null).ToList();
                var reportPending = tasks.Where(t => t.ReportedDate == null).ToList();

                double totalPlannedTask = todayAssignedTasks.Sum(t => t.PlannedTask);
                double totalCompletedTask = todayAssignedTasks.Sum(t => t.CompletedTask);
                var jobRoleIds = projectAllocations.Select(pa => pa.JobRoleId).ToList();
                var jobRoles = await _context.JobRoles
                    .Where(j => jobRoleIds.Contains(j.Id))
                    .ToListAsync();

                // Team on site
                var teamOnSite = jobRoles
                    .Select(role =>
                    {
                        var count = projectAllocations.Count(p => p.JobRoleId == role.Id && checkedInEmployeeIds.Contains(p.EmployeeId));
                        return new TeamOnSite { RoleName = role.Name, NumberofEmployees = count };
                    })
                    .OrderByDescending(t => t.NumberofEmployees)
                    .ToList();

                // Task details
                var performedTasks = todayAssignedTasks.Select(task =>
                {
                    var workItem = workItems.FirstOrDefault(w => w.Id == task.WorkItemId.ToString());
                    var area = areas.FirstOrDefault(a => a.Id == workItem?.WorkAreaId);
                    var floor = floors.FirstOrDefault(f => f.Id == area?.FloorId);
                    var building = buildings.FirstOrDefault(b => b.Id == floor?.BuildingId);

                    string activityName = workItem?.ActivityMaster?.ActivityName ?? "";
                    string location = $"{building?.BuildingName} > {floor?.FloorName}</span><br/><span style=\"color: gray; font-size: small; padding-left: 10px;\"> {floor?.FloorName}-{area?.AreaName}";
                    double pending = (workItem?.PlannedWork ?? 0) - (workItem?.CompletedWork ?? 0);

                    var taskTeam = taskMembers
                        .Where(m => m.TaskAllocationId == task.Id)
                        .Select(m =>
                        {
                            string name = $"{m.Employee?.FirstName ?? ""} {m.Employee?.LastName ?? ""}";
                            var role = jobRoles.FirstOrDefault(r => r.Id == m.Employee?.JobRoleId);
                            return new TaskTeam { Name = name, RoleName = role?.Name ?? "" };
                        })
                        .ToList();

                    return new PerformedTask
                    {
                        Activity = activityName,
                        Location = location,
                        AssignedToday = task.PlannedTask,
                        CompletedToday = task.CompletedTask,
                        Pending = pending,
                        Comment = task.Description,
                        Team = taskTeam
                    };
                }).ToList();

                // Attendance details
                var performedAttendance = attendances.Select(att =>
                {
                    var alloc = projectAllocations.FirstOrDefault(p => p.EmployeeId == att.EmployeeId);
                    var role = jobRoles.FirstOrDefault(r => r.Id == alloc?.JobRoleId);
                    string name = $"{alloc?.Employee?.FirstName ?? ""} {alloc?.Employee?.LastName ?? ""}";

                    return new PerformedAttendance
                    {
                        Name = name,
                        RoleName = role?.Name ?? "",
                        InTime = att.InTime ?? DateTime.UtcNow,
                        OutTime = att.OutTime,
                        Comment = att.Comment
                    };
                }).ToList();

                // Fill report
                statisticReport.TodaysAttendances = checkedInEmployeeIds.Count;
                statisticReport.TotalEmployees = assignedEmployeeIds.Count;
                statisticReport.AttendancePercentage = assignedEmployeeIds.Count > 0 ? (checkedInEmployeeIds.Count / assignedEmployeeIds.Count) * 100 : 0;
                statisticReport.RegularizationPending = regularizationIds.Count;
                statisticReport.CheckoutPending = checkoutPendingIds.Count;
                statisticReport.TotalPlannedWork = totalPlannedWork;
                statisticReport.TotalCompletedWork = totalCompletedWork;
                statisticReport.CompletionStatus = totalPlannedWork > 0 ? (totalCompletedWork / totalPlannedWork) * 100 : 0;
                statisticReport.TotalPlannedTask = totalPlannedTask;
                statisticReport.TotalCompletedTask = totalCompletedTask;
                statisticReport.AttendancePercentage = totalCompletedTask > 0 ? (totalCompletedTask / totalPlannedTask) * 100 : 0;
                statisticReport.TodaysAssignTasks = todayAssignedTasks.Count;
                statisticReport.TodaysCompletedTasks = todaysCompletedTasks.Count;
                statisticReport.ReportPending = reportPending.Count;
                statisticReport.TeamOnSite = teamOnSite;
                statisticReport.PerformedTasks = performedTasks;
                statisticReport.PerformedAttendance = performedAttendance;
                return statisticReport;
            }
            return null;
        }

        public async Task<ProjectStatisticReport?> GetDailyProjectReport(Guid projectId, Guid tenantId)
        {
            // await _cache.GetBuildingAndFloorByWorkAreaId();
            DateTime reportDate = DateTime.UtcNow.AddDays(-1).Date;
            var project = await _cache.GetProjectDetailsWithBuildings(projectId);
            if (project == null)
            {
                var projectSQL = await _context.Projects
               .AsNoTracking()
               .FirstOrDefaultAsync(p => p.Id == projectId && p.TenantId == tenantId);
                if (projectSQL != null)
                {
                    project = new ProjectMongoDB
                    {
                        Id = projectSQL.Id.ToString(),
                        Name = projectSQL.Name,
                        ShortName = projectSQL.ShortName,
                        ProjectAddress = projectSQL.ProjectAddress,
                        ContactPerson = projectSQL.ContactPerson
                    };
                    await _cache.AddProjectDetails(projectSQL);
                }
            }
            if (project != null)
            {

                var statisticReport = new ProjectStatisticReport
                {
                    Date = reportDate,
                    ProjectName = project.Name ?? "",
                    TimeStamp = DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss", CultureInfo.InvariantCulture)
                };

                // Preload relevant data
                var projectAllocations = await _context.ProjectAllocations
                    .Include(p => p.Employee)
                    .Where(p => p.ProjectId == projectId && p.IsActive)
                    .ToListAsync();

                var assignedEmployeeIds = projectAllocations.Select(p => p.EmployeeId).ToHashSet();

                var attendances = await _context.Attendes
                    .AsNoTracking()
                    .Where(a => a.ProjectID == projectId && a.InTime != null && a.InTime.Value.Date == reportDate)
                    .ToListAsync();

                var checkedInEmployeeIds = attendances.Select(a => a.EmployeeId).Distinct().ToHashSet();
                var checkoutPendingIds = attendances.Where(a => a.OutTime == null).Select(a => a.EmployeeId).Distinct().ToHashSet();
                var regularizationIds = attendances
                    .Where(a => a.Activity == ATTENDANCE_MARK_TYPE.REQUEST_REGULARIZE)
                    .Select(a => a.EmployeeId).Distinct().ToHashSet();

                // Preload buildings, floors, areas
                List<BuildingMongoDBVM>? buildings = null;
                List<FloorMongoDBVM>? floors = null;
                List<WorkAreaMongoDB>? areas = null;
                List<WorkItemMongoDB>? workItems = null;

                // Fetch Buildings
                buildings = project.Buildings
                .Select(b => new BuildingMongoDBVM
                {
                    Id = b.Id,
                    ProjectId = b.ProjectId,
                    BuildingName = b.BuildingName,
                    Description = b.Description
                }).ToList();
                if (!buildings.Any())
                {
                    buildings = await _context.Buildings
                    .Where(b => b.ProjectId == projectId)
                    .Select(b => new BuildingMongoDBVM
                    {
                        Id = b.Id.ToString(),
                        ProjectId = b.ProjectId.ToString(),
                        BuildingName = b.Name,
                        Description = b.Description
                    })
                    .ToListAsync();
                }

                // fetch Floors
                floors = project.Buildings
                .SelectMany(b => b.Floors.Select(f => new FloorMongoDBVM
                {
                    Id = f.Id.ToString(),
                    BuildingId = f.BuildingId,
                    FloorName = f.FloorName
                })).ToList();
                if (!floors.Any())
                {
                    var buildingIds = buildings.Select(b => Guid.Parse(b.Id)).ToList();
                    floors = await _context.Floor
                    .Where(f => buildingIds.Contains(f.BuildingId))
                    .Select(f => new FloorMongoDBVM
                    {
                        Id = f.Id.ToString(),
                        BuildingId = f.BuildingId.ToString(),
                        FloorName = f.FloorName
                    })
                    .ToListAsync();
                }

                // fetch Work Areas
                areas = project.Buildings
                .SelectMany(b => b.Floors)
                .SelectMany(f => f.WorkAreas).ToList();
                if (!areas.Any())
                {
                    var floorIds = floors.Select(f => Guid.Parse(f.Id)).ToList();
                    areas = await _context.WorkAreas
                    .Where(a => floorIds.Contains(a.FloorId))
                    .Select(wa => new WorkAreaMongoDB
                    {
                        Id = wa.Id.ToString(),
                        FloorId = wa.FloorId.ToString(),
                        AreaName = wa.AreaName,
                    })
                    .ToListAsync();
                }

                var areaIds = areas.Select(a => Guid.Parse(a.Id)).ToList();

                // fetch Work Items
                workItems = await _cache.GetWorkItemsByWorkAreaIds(areaIds, new List<Guid>());
                if (workItems == null || !workItems.Any())
                {
                    workItems = await _context.WorkItems
                    .Include(w => w.ActivityMaster)
                    .Where(w => areaIds.Contains(w.WorkAreaId))
                    .Select(wi => new WorkItemMongoDB
                    {
                        Id = wi.Id.ToString(),
                        WorkAreaId = wi.WorkAreaId.ToString(),
                        PlannedWork = wi.PlannedWork,
                        CompletedWork = wi.CompletedWork,
                        Description = wi.Description,
                        TaskDate = wi.TaskDate,
                        ActivityMaster = new ActivityMasterMongoDB
                        {
                            ActivityName = wi.ActivityMaster != null ? wi.ActivityMaster.ActivityName : null,
                            UnitOfMeasurement = wi.ActivityMaster != null ? wi.ActivityMaster.UnitOfMeasurement : null
                        }
                    })
                    .ToListAsync();
                }

                var itemIds = workItems.Select(i => Guid.Parse(i.Id)).ToList();

                var tasks = await _context.TaskAllocations
                    .Where(t => itemIds.Contains(t.WorkItemId))
                    .ToListAsync();

                var taskIds = tasks.Select(t => t.Id).ToList();

                var taskMembers = await _context.TaskMembers
                    .Include(m => m.Employee)
                    .Where(m => taskIds.Contains(m.TaskAllocationId))
                    .ToListAsync();

                // Aggregate data
                double totalPlannedWork = workItems.Sum(w => w.PlannedWork);
                double totalCompletedWork = workItems.Sum(w => w.CompletedWork);

                var todayAssignedTasks = tasks.Where(t => t.AssignmentDate.Date == reportDate).ToList();
                var reportPending = tasks.Where(t => t.ReportedDate == null).ToList();

                double totalPlannedTask = todayAssignedTasks.Sum(t => t.PlannedTask);
                double totalCompletedTask = todayAssignedTasks.Sum(t => t.CompletedTask);
                var jobRoleIds = projectAllocations.Select(pa => pa.JobRoleId).ToList();
                var jobRoles = await _context.JobRoles
                    .Where(j => j.TenantId == tenantId && jobRoleIds.Contains(j.Id))
                    .ToListAsync();

                // Team on site
                var teamOnSite = jobRoles
                    .Select(role =>
                    {
                        var count = projectAllocations.Count(p => p.JobRoleId == role.Id && checkedInEmployeeIds.Contains(p.EmployeeId));
                        return new TeamOnSite { RoleName = role.Name, NumberofEmployees = count };
                    })
                    .OrderByDescending(t => t.NumberofEmployees)
                    .ToList();

                // Task details
                var performedTasks = todayAssignedTasks.Select(task =>
                {
                    var workItem = workItems.FirstOrDefault(w => w.Id == task.WorkItemId.ToString());
                    var area = areas.FirstOrDefault(a => a.Id == workItem?.WorkAreaId);
                    var floor = floors.FirstOrDefault(f => f.Id == area?.FloorId);
                    var building = buildings.FirstOrDefault(b => b.Id == floor?.BuildingId);

                    string activityName = workItem?.ActivityMaster?.ActivityName ?? "";
                    string location = $"{building?.BuildingName} > {floor?.FloorName}</span><br/><span style=\"color: gray; font-size: small; padding-left: 10px;\"> {floor?.FloorName}-{area?.AreaName}";
                    double pending = (workItem?.PlannedWork ?? 0) - (workItem?.CompletedWork ?? 0);

                    var taskTeam = taskMembers
                        .Where(m => m.TaskAllocationId == task.Id)
                        .Select(m =>
                        {
                            string name = $"{m.Employee?.FirstName ?? ""} {m.Employee?.LastName ?? ""}";
                            var role = jobRoles.FirstOrDefault(r => r.Id == m.Employee?.JobRoleId);
                            return new TaskTeam { Name = name, RoleName = role?.Name ?? "" };
                        })
                        .ToList();

                    return new PerformedTask
                    {
                        Activity = activityName,
                        Location = location,
                        AssignedToday = task.PlannedTask,
                        CompletedToday = task.CompletedTask,
                        Pending = pending,
                        Comment = task.Description,
                        Team = taskTeam
                    };
                }).ToList();

                // Attendance details
                var performedAttendance = attendances.Select(att =>
                {
                    var alloc = projectAllocations.FirstOrDefault(p => p.EmployeeId == att.EmployeeId);
                    var role = jobRoles.FirstOrDefault(r => r.Id == alloc?.JobRoleId);
                    string name = $"{alloc?.Employee?.FirstName ?? ""} {alloc?.Employee?.LastName ?? ""}";

                    return new PerformedAttendance
                    {
                        Name = name,
                        RoleName = role?.Name ?? "",
                        InTime = att.InTime ?? DateTime.UtcNow,
                        OutTime = att.OutTime,
                        Comment = att.Comment
                    };
                }).ToList();

                // Fill report
                statisticReport.TodaysAttendances = checkedInEmployeeIds.Count;
                statisticReport.TotalEmployees = assignedEmployeeIds.Count;
                statisticReport.RegularizationPending = regularizationIds.Count;
                statisticReport.CheckoutPending = checkoutPendingIds.Count;
                statisticReport.TotalPlannedWork = totalPlannedWork;
                statisticReport.TotalCompletedWork = totalCompletedWork;
                statisticReport.TotalPlannedTask = totalPlannedTask;
                statisticReport.TotalCompletedTask = totalCompletedTask;
                statisticReport.CompletionStatus = totalPlannedWork > 0 ? totalCompletedWork / totalPlannedWork : 0;
                statisticReport.TodaysAssignTasks = todayAssignedTasks.Count;
                statisticReport.ReportPending = reportPending.Count;
                statisticReport.TeamOnSite = teamOnSite;
                statisticReport.PerformedTasks = performedTasks;
                statisticReport.PerformedAttendance = performedAttendance;
                return statisticReport;
            }
            return null;
        }
        /// <summary>
        /// Retrieves project statistics for a given project ID and sends an email report.
        /// </summary>
        /// <param name="projectId">The ID of the project.</param>
        /// <param name="recipientEmail">The email address of the recipient.</param>
        /// <returns>An ApiResponse indicating the success or failure of retrieving statistics and sending the email.</returns>
        public async Task<ApiResponse<object>> GetProjectStatistics(Guid projectId, List<string> recipientEmails, string body, string subject, Guid tenantId)
        {
            // --- Input Validation ---
            if (projectId == Guid.Empty)
            {
                _logger.LogWarning("Validation Error: Provided empty project ID while fetching project report.");
                return ApiResponse<object>.ErrorResponse("Provided empty Project ID.", "Provided empty Project ID.", 400);
            }

            if (recipientEmails == null || !recipientEmails.Any())
            {
                _logger.LogWarning("Validation Error: No recipient emails provided for project ID {ProjectId}.", projectId);
                return ApiResponse<object>.ErrorResponse("No recipient emails provided.", "No recipient emails provided.", 400);
            }

            // --- Fetch Project Statistics ---
            var statisticReport = await GetDailyProjectReport(projectId, tenantId);

            if (statisticReport == null)
            {
                _logger.LogWarning("Project Data Not Found: User attempted to fetch project progress for project ID {ProjectId} but it was not found.", projectId);
                return ApiResponse<object>.ErrorResponse("Project not found.", "Project not found.", 404);
            }

            // --- Send Email & Log ---
            string emailBody;
            try
            {
                emailBody = await _emailSender.SendProjectStatisticsEmail(recipientEmails, body, subject, statisticReport);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Email Sending Error: Failed to send project statistics email for project ID {ProjectId}.", projectId);
                return ApiResponse<object>.ErrorResponse("Failed to send email.", "An error occurred while sending the email.", 500);
            }

            // Find a relevant employee. Use AsNoTracking() for read-only query if the entity won't be modified.
            // Consider if you need *any* employee from the recipients or a specific one (e.g., the sender).
            var employee = await _context.Employees
                                         .AsNoTracking() // Optimize for read-only
                                         .FirstOrDefaultAsync(e => e.Email != null && recipientEmails.Contains(e.Email)) ?? new Employee();

            // Initialize Employee to a default or null, based on whether an employee is always expected.
            // If employee.Id is a non-nullable type, ensure proper handling if employee is null.
            Guid employeeId = employee.Id; // Default to Guid.Empty if no employee found

            var mailLogs = recipientEmails.Select(recipientEmail => new MailLog
            {
                ProjectId = projectId,
                EmailId = recipientEmail,
                Body = emailBody,
                EmployeeId = employeeId, // Use the determined employeeId
                TimeStamp = DateTime.UtcNow,
                TenantId = tenantId
            }).ToList();

            _context.MailLogs.AddRange(mailLogs);

            try
            {
                await _context.SaveChangesAsync();
                _logger.LogInfo("Successfully sent and logged project statistics email for Project ID {ProjectId} to {RecipientCount} recipients.", projectId, recipientEmails.Count);
                return ApiResponse<object>.SuccessResponse(statisticReport, "Email sent successfully", 200);
            }
            catch (DbUpdateException dbEx)
            {
                _logger.LogError(dbEx, "Database Error: Failed to save mail logs for project ID {ProjectId}.", projectId);
                // Depending on your requirements, you might still return success here as the email was sent.
                // Or return an error indicating the logging failed.
                return ApiResponse<object>.ErrorResponse("Email sent, but failed to log activity.", "Email sent, but an error occurred while logging.", 500);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Unexpected Error: An unhandled exception occurred while processing project statistics for project ID {ProjectId}.", projectId);
                return ApiResponse<object>.ErrorResponse("An unexpected error occurred.", "An unexpected error occurred.", 500);
            }
        }
    }
}
