using Marco.Pms.DataAccess.Data;
using Marco.Pms.Model.Dtos.Attendance;
using Marco.Pms.Model.Entitlements;
using Marco.Pms.Model.Expenses;
using Marco.Pms.Model.Utilities;
using Marco.Pms.Model.ViewModels.DashBoard;
using Marco.Pms.Services.Service;
using Marco.Pms.Services.Service.ServiceInterfaces;
using MarcoBMS.Services.Helpers;
using MarcoBMS.Services.Service;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Globalization;

namespace Marco.Pms.Services.Controllers
{
    [Authorize]
    [Route("api/[controller]")]
    [ApiController]
    public class DashboardController : ControllerBase
    {
        private readonly ApplicationDbContext _context;
        private readonly UserHelper _userHelper;
        private readonly IProjectServices _projectServices;
        private readonly ILoggingService _logger;
        private readonly PermissionServices _permissionServices;
        private readonly IServiceScopeFactory _serviceScopeFactory;
        public static readonly Guid ActiveId = Guid.Parse("b74da4c2-d07e-46f2-9919-e75e49b12731");
        private static readonly Guid Draft = Guid.Parse("297e0d8f-f668-41b5-bfea-e03b354251c8");
        private static readonly Guid Review = Guid.Parse("6537018f-f4e9-4cb3-a210-6c3b2da999d7");
        private static readonly Guid Approve = Guid.Parse("4068007f-c92f-4f37-a907-bc15fe57d4d8");
        private static readonly Guid ProcessPending = Guid.Parse("f18c5cfd-7815-4341-8da2-2c2d65778e27");
        private static readonly Guid Processed = Guid.Parse("61578360-3a49-4c34-8604-7b35a3787b95");
        private static readonly Guid RejectedByReviewer = Guid.Parse("965eda62-7907-4963-b4a1-657fb0b2724b");
        private static readonly Guid RejectedByApprover = Guid.Parse("d1ee5eec-24b6-4364-8673-a8f859c60729");
        private readonly Guid tenantId;
        public DashboardController(ApplicationDbContext context,
            UserHelper userHelper,
            IProjectServices projectServices,
            IServiceScopeFactory serviceScopeFactory,
            ILoggingService logger,
            PermissionServices permissionServices)
        {
            _context = context;
            _userHelper = userHelper;
            _projectServices = projectServices;
            _logger = logger;
            _serviceScopeFactory = serviceScopeFactory;
            _permissionServices = permissionServices;
            tenantId = userHelper.GetTenantId();
        }
        /// <summary>
        /// Fetches project progression data (planned and completed tasks) in graph form for a tenant and specified (or all) projects over a date range.
        /// </summary>
        /// <param name="days">Number of days back to fetch data for</param>
        /// <param name="FromDate">Starting date for the graph, or today if null</param>
        /// <param name="projectId">Optionally, the project to filter on</param>
        [HttpGet("progression")]
        public async Task<IActionResult> GetGraphAsync([FromQuery] double days, [FromQuery] string FromDate, [FromQuery] Guid? projectId)
        {
            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("Invalid request: TenantId is empty on progression endpoint");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

            // Step 1: Parse and validate incoming date, fallback to today if null.
            if (!string.IsNullOrWhiteSpace(FromDate) && !DateTime.TryParse(FromDate, out DateTime fromDate))
            {
                _logger.LogWarning("Invalid starting date provided for progression graph by employee {EmployeeId}", loggedInEmployee.Id);
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid starting date.", "Invalid starting date.", 400));
            }
            DateTime fromDateValue = string.IsNullOrWhiteSpace(FromDate) ? DateTime.UtcNow.Date : DateTime.Parse(FromDate);

            // Step 2: Get earliest task date for tenant, fallback to today
            var firstTask = await _context.TaskAllocations
                .Where(t => t.TenantId == tenantId)
                .OrderBy(t => t.AssignmentDate)
                .Select(t => t.AssignmentDate)
                .FirstOrDefaultAsync();
            if (firstTask == default) firstTask = DateTime.UtcNow;

            // Step 3: Determine toDate (the oldest date in range)
            double negativeDays = -Math.Abs(days);
            DateTime toDate = fromDateValue.AddDays(negativeDays);
            if (firstTask.Date >= toDate.Date)
                toDate = firstTask;

            var projectProgressionVMs = new List<ProjectProgressionVM>();

            // Step 4: Query depends on whether filtering for specific project
            if (projectId == null)
            {
                // All projects for the tenant in range
                var tasks = await _context.TaskAllocations
                    .Where(t => t.TenantId == tenantId && t.AssignmentDate.Date <= fromDateValue.Date && t.AssignmentDate.Date >= toDate.Date)
                    .ToListAsync();

                for (double flagDays = 0; negativeDays < flagDays; flagDays -= 1)
                {
                    var date = fromDateValue.AddDays(flagDays);
                    if (date >= firstTask.Date)
                    {
                        var todayTasks = tasks.Where(t => t.AssignmentDate.Date == date.Date);
                        projectProgressionVMs.Add(new ProjectProgressionVM
                        {
                            ProjectId = Guid.Empty,
                            ProjectName = "",
                            Date = date,
                            PlannedTask = todayTasks.Sum(t => t.PlannedTask),
                            CompletedTask = todayTasks.Sum(t => t.CompletedTask),
                        });
                    }
                }
                _logger.LogInfo("Project progression report for all projects fetched successfully by employee {EmployeeId}", loggedInEmployee.Id);
            }
            else
            {
                // Specified project: Fetch hierarchical data efficiently
                var project = await _context.Projects.FirstOrDefaultAsync(p => p.Id == projectId && p.TenantId == tenantId);
                if (project == null)
                {
                    _logger.LogWarning("Project not found. ProjectId: {ProjectId}, TenantId: {TenantId}, Requested by: {EmployeeId}", projectId, tenantId, loggedInEmployee.Id);
                    return NotFound(ApiResponse<object>.ErrorResponse("Project not found", "Specified project not found", 404));
                }
                var tasks = await _context.TaskAllocations
                    .Include(t => t.WorkItem)
                        .ThenInclude(wi => wi!.WorkArea)
                            .ThenInclude(wa => wa!.Floor)
                                .ThenInclude(f => f!.Building)
                    .Where(t =>
                        t.WorkItem != null &&
                        t.WorkItem.WorkArea != null &&
                        t.WorkItem.WorkArea.Floor != null &&
                        t.WorkItem.WorkArea.Floor.Building != null &&
                        t.WorkItem.WorkArea.Floor.Building.ProjectId == projectId &&
                        t.TenantId == tenantId &&
                        t.AssignmentDate.Date <= fromDateValue.Date &&
                        t.AssignmentDate.Date >= toDate.Date)
                    .ToListAsync();

                for (double flagDays = 0; negativeDays < flagDays; flagDays -= 1)
                {
                    var date = fromDateValue.AddDays(flagDays);
                    if (date >= firstTask.Date)
                    {
                        var todayTasks = tasks.Where(t => t.AssignmentDate.Date == date.Date);
                        projectProgressionVMs.Add(new ProjectProgressionVM
                        {
                            ProjectId = projectId.Value,
                            ProjectName = project.Name,
                            Date = date,
                            PlannedTask = todayTasks.Sum(t => t.PlannedTask),
                            CompletedTask = todayTasks.Sum(t => t.CompletedTask),
                        });
                    }
                }
                _logger.LogInfo("Project progression report for project {ProjectId} fetched successfully by employee {EmployeeId}", projectId, loggedInEmployee.Id);
            }

            return Ok(ApiResponse<object>.SuccessResponse(projectProgressionVMs, "Project progression data fetched successfully.", 200));
        }

        /// <summary>
        /// Gets the count of total and ongoing projects for the current tenant,
        /// using properly optimized queries and structured logging.
        /// </summary>
        [HttpGet("projects")]
        public async Task<IActionResult> GetProjectCountAsync()
        {
            // Step 1: Tenant validation (defensive coding for multi-tenancy)
            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("Project count request denied: Empty TenantId.");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            // Step 2: Get logged-in employee for logging/auditing purposes
            var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

            // Step 3: Fetch relevant status IDs only (query optimized for DB filtering)
            var projectStatusIds = await _context.StatusMasters
                .Where(s => s.Status == "Active" || s.Status == "In Progress")
                .Select(s => s.Id)
                .ToListAsync();

            // Step 4: Query total and ongoing project counts directly (avoid .ToList())
            var totalProjectsCount = await _context.Projects
                .CountAsync(p => p.TenantId == tenantId);

            var ongoingProjectsCount = await _context.Projects
                .CountAsync(p => p.TenantId == tenantId && projectStatusIds.Contains(p.ProjectStatusId));

            var dashboardVM = new ProjectDashboardVM
            {
                TotalProjects = totalProjectsCount,
                OngoingProjects = ongoingProjectsCount
            };

            _logger.LogInfo("Fetched project counts: {TotalProjects} total, {OngoingProjects} ongoing. Employee: {EmployeeId}, TenantId: {TenantId}",
                totalProjectsCount, ongoingProjectsCount, loggedInEmployee.Id, tenantId);

            return Ok(ApiResponse<object>.SuccessResponse(dashboardVM, "Project counts fetched successfully.", 200));
        }

        /// <summary>
        /// Retrieves a dashboard summary of total employees and today's attendance.
        /// If a projectId is provided, it returns totals for that project; otherwise, for all accessible active projects.
        /// </summary>
        /// <param name="projectId">Optional. The ID of a specific project to get totals for.</param>
        [HttpGet("teams")]
        public async Task<IActionResult> GetTotalEmployees([FromQuery] Guid? projectId)
        {
            try
            {
                if (tenantId == Guid.Empty)
                {
                    _logger.LogWarning("Invalid request: TenantId is empty on progression endpoint");
                    return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
                }

                var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

                _logger.LogInfo("GetTotalEmployees called by user {UserId} for ProjectId: {ProjectId}", loggedInEmployee.Id, projectId ?? Guid.Empty);

                // --- Step 1: Get the list of projects the user can access ---
                // This query is more efficient as it only selects the IDs needed.
                var projects = await _projectServices.GetMyProjectIdsAsync(tenantId, loggedInEmployee);

                var accessibleActiveProjectIds = await _context.Projects
                    .Where(p => p.ProjectStatusId == ActiveId && projects.Contains(p.Id))
                    .Select(p => p.Id)
                    .ToListAsync();

                if (!accessibleActiveProjectIds.Any())
                {
                    _logger.LogInfo("User {UserId} has no accessible active projects.", loggedInEmployee.Id);
                    return Ok(ApiResponse<TeamDashboardVM>.SuccessResponse(new TeamDashboardVM(), "No accessible active projects found.", 200));
                }

                // --- Step 2: Build the list of project IDs to query against ---
                List<Guid> finalProjectIds;

                if (projectId.HasValue)
                {
                    // Security Check: Ensure the requested project is in the user's accessible list.
                    var hasPermission = await _permissionServices.HasProjectPermission(loggedInEmployee, projectId.Value);
                    if (!hasPermission)
                    {
                        _logger.LogWarning("Access DENIED for user {UserId} on project {ProjectId} (not active or not accessible).", loggedInEmployee.Id, projectId.Value);
                        return StatusCode(403, ApiResponse<object>.ErrorResponse("Access Denied.", "You do not have permission to view this project, or it is not active.", 403));
                    }
                    finalProjectIds = new List<Guid> { projectId.Value };
                }
                else
                {
                    finalProjectIds = accessibleActiveProjectIds;
                }

                // --- Step 3: Run efficient aggregation queries SEQUENTIALLY ---
                // Since we only have one DbContext instance, we await each query one by one.

                // Query 1: Count total distinct employees allocated to the final project list
                int totalEmployees = await _context.ProjectAllocations
                    .Where(pa => pa.TenantId == tenantId &&
                                 finalProjectIds.Contains(pa.ProjectId) &&
                                 pa.IsActive)
                    .Select(pa => pa.EmployeeId)
                    .Distinct()
                    .CountAsync();

                // Query 2: Count total distinct employees who checked in today
                // Use an efficient date range check
                var today = DateTime.UtcNow.Date;
                var tomorrow = today.AddDays(1);

                int inTodays = await _context.Attendes
                    .Where(a => a.InTime >= today && a.InTime < tomorrow &&
                                 finalProjectIds.Contains(a.ProjectID))
                    .Select(a => a.EmployeeId)
                    .Distinct()
                    .CountAsync();

                // --- Step 4: Assemble the response ---
                var teamDashboardVM = new TeamDashboardVM
                {
                    TotalEmployees = totalEmployees,
                    InToday = inTodays
                };

                _logger.LogInfo("Successfully fetched team dashboard for user {UserId}. Total: {TotalEmployees}, InToday: {InToday}",
                    loggedInEmployee.Id, teamDashboardVM.TotalEmployees, teamDashboardVM.InToday);

                return Ok(ApiResponse<TeamDashboardVM>.SuccessResponse(teamDashboardVM, "Dashboard data retrieved successfully.", 200));
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "An unexpected error occurred in GetTotalEmployees for projectId {ProjectId}", projectId ?? Guid.Empty);
                return StatusCode(500, ApiResponse<object>.ErrorResponse("An internal server error occurred.", null, 500));
            }
        }

        /// <summary>
        /// Retrieves a dashboard summary of total planned and completed tasks.
        /// If a projectId is provided, it returns totals for that project; otherwise, for all accessible projects.
        /// </summary>
        /// <param name="projectId">Optional. The ID of a specific project to get totals for.</param>
        /// <returns>An ApiResponse containing the task dashboard summary.</returns>
        [HttpGet("tasks")] // Example route
        public async Task<IActionResult> GetTotalTasks([FromQuery] Guid? projectId) // Changed to FromQuery as it's optional
        {
            try
            {
                if (tenantId == Guid.Empty)
                {
                    _logger.LogWarning("Invalid request: TenantId is empty on progression endpoint");
                    return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
                }

                var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

                _logger.LogInfo("GetTotalTasks called by user {UserId} for ProjectId: {ProjectId}", loggedInEmployee.Id, projectId ?? Guid.Empty);

                // --- Step 1: Build the base IQueryable for WorkItems ---
                // This query is NOT executed yet. We will add more filters to it.
                var baseWorkItemQuery = _context.WorkItems.Where(t => t.TenantId == tenantId);

                // --- Step 2: Apply Filters based on the request (Project or All Accessible) ---
                if (projectId.HasValue)
                {
                    // --- Logic for a SINGLE Project ---

                    // 2a. Security Check: Verify permission for the specific project.
                    var hasPermission = await _permissionServices.HasProjectPermission(loggedInEmployee, projectId.Value);
                    if (!hasPermission)
                    {
                        _logger.LogWarning("Access DENIED for user {UserId} on project {ProjectId}.", loggedInEmployee.Id, projectId.Value);
                        return StatusCode(403, ApiResponse<object>.ErrorResponse("Access Denied.", "You do not have permission to view this project.", 403));
                    }

                    // 2b. Add project-specific filter to the base query.
                    // This is more efficient than fetching workAreaIds separately.
                    baseWorkItemQuery = baseWorkItemQuery
                        .Where(wi => wi.WorkArea != null &&
                            wi.WorkArea.Floor != null &&
                            wi.WorkArea.Floor.Building != null &&
                            wi.WorkArea.Floor.Building.ProjectId == projectId.Value);
                }
                else
                {
                    // --- Logic for ALL Accessible Projects ---

                    // 2c. Get a list of all projects the user is allowed to see.
                    var accessibleProjectIds = await _projectServices.GetMyProjectIdsAsync(tenantId, loggedInEmployee);

                    if (!accessibleProjectIds.Any())
                    {
                        _logger.LogInfo("User {UserId} has no accessible projects.", loggedInEmployee.Id);
                        // Return a zeroed-out dashboard if the user has no projects.
                        return Ok(ApiResponse<TasksDashboardVM>.SuccessResponse(new TasksDashboardVM(), "No accessible projects found.", 200));
                    }

                    // 2d. Add a filter to include all work items from all accessible projects.
                    baseWorkItemQuery = baseWorkItemQuery
                        .Where(wi => wi.WorkArea != null &&
                            wi.WorkArea.Floor != null &&
                            wi.WorkArea.Floor.Building != null &&
                            accessibleProjectIds.Contains(wi.WorkArea.Floor.Building.ProjectId));
                }

                // --- Step 3: Execute the Aggregation Query ON THE DATABASE SERVER ---
                // This is the most powerful optimization. The database does all the summing.
                // EF Core translates this into a single, efficient SQL query like:
                // SELECT SUM(PlannedWork), SUM(CompletedWork) FROM WorkItems WHERE ...
                var tasksDashboardVM = await baseWorkItemQuery
                    .GroupBy(x => 1) // Group by a constant to aggregate all rows into one result.
                    .Select(g => new TasksDashboardVM
                    {
                        TotalTasks = g.Sum(wi => wi.PlannedWork),
                        CompletedTasks = g.Sum(wi => wi.CompletedWork)
                    })
                    .FirstOrDefaultAsync(); // Use FirstOrDefaultAsync as GroupBy might return no rows.

                // If the query returned no work items, the result will be null. Default to a zeroed object.
                tasksDashboardVM ??= new TasksDashboardVM();

                _logger.LogInfo("Successfully fetched task dashboard for user {UserId}. Total: {TotalTasks}, Completed: {CompletedTasks}",
                    loggedInEmployee.Id, tasksDashboardVM.TotalTasks, tasksDashboardVM.CompletedTasks);

                return Ok(ApiResponse<TasksDashboardVM>.SuccessResponse(tasksDashboardVM, "Dashboard data retrieved successfully.", 200));
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "An unexpected error occurred in GetTotalTasks for projectId {ProjectId}", projectId ?? Guid.Empty);
                return StatusCode(500, ApiResponse<object>.ErrorResponse("An internal server error occurred.", null, 500));
            }
        }

        /// <summary>
        /// Retrieves counts of pending attendance regularizations and check-outs for the logged-in employee.
        /// </summary>
        [HttpGet("pending-attendance")]
        public async Task<IActionResult> GetPendingAttendanceAsync()
        {
            // Step 1: Validate tenant context to prevent processing invalid requests
            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("Invalid request: TenantId is empty when fetching pending attendance");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            // Step 2: Get currently logged-in employee for scoped attendance query
            var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

            // Step 3: Query attendance entries for employee scoped to tenant
            var attendanceEntries = await _context.Attendes
                .Where(a => a.EmployeeId == loggedInEmployee.Id && a.TenantId == tenantId)
                .ToListAsync();

            if (!attendanceEntries.Any())
            {
                // Step 4: No attendance entries found for this employee, log and return 404
                _logger.LogWarning("No attendance entries found for employee {EmployeeId}", loggedInEmployee.Id);
                return NotFound(ApiResponse<object>.ErrorResponse("No attendance entry found.", "No attendance entry was found for this employee.", 404));
            }
            // Step 5: Calculate counts for pending regularizations and check-outs efficiently
            int pendingRegularizationCount = attendanceEntries.Count(a => a.Activity == ATTENDANCE_MARK_TYPE.REQUEST_REGULARIZE);
            int pendingCheckOutCount = attendanceEntries.Count(a => a.OutTime == null);

            var response = new
            {
                PendingRegularization = pendingRegularizationCount,
                PendingCheckOut = pendingCheckOutCount
            };

            _logger.LogInfo("Pending regularization: {PendingRegularization}, Pending check-out: {PendingCheckOut} for employee {EmployeeId}",
                pendingRegularizationCount, pendingCheckOutCount, loggedInEmployee.Id);

            return Ok(ApiResponse<object>.SuccessResponse(response, "Pending regularization and pending check-out fetched successfully.", 200));
        }

        /// <summary>
        /// Retrieves attendance records for a specific project on a given date.
        /// </summary>
        /// <param name="projectId">The project identifier</param>
        /// <param name="date">Optional date filter (defaults to current UTC date if null or invalid)</param>
        [HttpGet("project-attendance/{projectId}")]
        public async Task<IActionResult> GetProjectAttendance(Guid projectId, [FromQuery] string? date)
        {
            // Step 1: Validate tenant context
            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("GetProjectAttendance failed: TenantId is empty.");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            // Step 2: Get logged-in employee for audit logging
            var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

            // Step 3: Parse and validate date parameter, default to UTC today if invalid or null
            DateTime currentDate = DateTime.UtcNow.Date;
            if (!string.IsNullOrWhiteSpace(date) && !DateTime.TryParse(date, out currentDate))
            {
                _logger.LogWarning("Invalid date parameter '{Date}' sent by employee {EmployeeId}", date, loggedInEmployee.Id);
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid date.", "Invalid date format.", 400));
            }

            // Step 4: Verify project existence within tenant context
            var project = await _context.Projects
                .FirstOrDefaultAsync(p => p.Id == projectId && p.TenantId == tenantId);

            if (project == null)
            {
                _logger.LogWarning("Project {ProjectId} not found for employee {EmployeeId} on date {Date}", projectId, loggedInEmployee.Id, currentDate);
                return NotFound(ApiResponse<object>.ErrorResponse("Project not found", "Specified project does not exist.", 404));
            }

            // Step 5: Fetch active employee assignments for the project and tenant
            var employeeIds = await _context.ProjectAllocations
                .Where(pa => pa.ProjectId == projectId && pa.IsActive && pa.TenantId == tenantId)
                .Select(pa => pa.EmployeeId)
                .Distinct()
                .ToListAsync();

            if (!employeeIds.Any())
            {
                _logger.LogInfo("No active employee assignments found for project {ProjectId} on date {Date}", projectId, currentDate);
                return Ok(ApiResponse<object>.SuccessResponse(
                    new ProjectAttendanceVM
                    {
                        AttendanceTable = new List<EmployeeAttendanceVM>(),
                        CheckedInEmployee = 0,
                        AssignedEmployee = 0
                    },
                    $"No active employee assignments found for project {project.Name} on {currentDate:d}.", 200));
            }

            // Step 6: Bulk-fetch employees and their attendance on the specified date
            var employees = await _context.Employees
                .Where(e => employeeIds.Contains(e.Id))
                .ToListAsync();

            var attendances = await _context.Attendes
                .Where(a => employeeIds.Contains(a.EmployeeId)
                            && a.ProjectID == projectId
                            && a.InTime.HasValue
                            && a.InTime.Value.Date == currentDate)
                .ToListAsync();

            // Step 7: Map attendance data to VM, joining attendance with employee info efficiently
            var employeeAttendanceVMs = attendances
                .Join(employees,
                      attendance => attendance.EmployeeId,
                      employee => employee.Id,
                      (attendance, employee) => new EmployeeAttendanceVM
                      {
                          FirstName = employee.FirstName,
                          LastName = employee.LastName,
                          MiddleName = employee.MiddleName,
                          Comment = attendance.Comment,
                          InTime = attendance.InTime,
                          OutTime = attendance.OutTime
                      })
                .ToList();

            // Step 8: Prepare response VM with attendance counts
            var projectAttendanceVM = new ProjectAttendanceVM
            {
                AttendanceTable = employeeAttendanceVMs,
                CheckedInEmployee = employeeAttendanceVMs.Count,
                AssignedEmployee = employeeIds.Count
            };

            _logger.LogInfo("Attendance record retrieved for project {ProjectId} on date {Date} by employee {EmployeeId}", projectId, currentDate, loggedInEmployee.Id);

            return Ok(ApiResponse<object>.SuccessResponse(projectAttendanceVM, $"Attendance record for project {project.Name} on {currentDate:d} fetched successfully.", 200));
        }

        /// <summary>
        /// Retrieves detailed performed activity records for a specific project on a given date.
        /// </summary>
        /// <param name="projectId">The project identifier</param>
        /// <param name="date">Optional date filter (defaults to current UTC date if null or invalid)</param>
        [HttpGet("activities/{projectId}")]
        public async Task<IActionResult> GetActivities(Guid projectId, [FromQuery] string? date)
        {
            // Step 1: Validate tenant context for security and data isolation
            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("GetActivities request failed with empty TenantId.");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            // Step 2: Fetch logged-in employee for audit
            var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();

            // Step 3: Parse and validate date parameter, defaulting to UTC today if invalid or not provided
            DateTime currentDate = DateTime.UtcNow.Date;
            if (!string.IsNullOrWhiteSpace(date) && !DateTime.TryParse(date, out currentDate))
            {
                _logger.LogWarning("Invalid date parameter '{Date}' supplied by employee {EmployeeId}", date, loggedInEmployee.Id);
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid date.", "Invalid date format.", 400));
            }

            // Step 4: Verify project existence and tenant scope
            var project = await _context.Projects
                .FirstOrDefaultAsync(p => p.Id == projectId && p.TenantId == tenantId);

            if (project == null)
            {
                _logger.LogWarning("ProjectId {ProjectId} not found for employee {EmployeeId} during activity retrieval on {Date}", projectId, loggedInEmployee.Id, currentDate);
                return NotFound(ApiResponse<object>.ErrorResponse("Project not found", "Specified project does not exist.", 404));
            }

            // Step 5: Fetch tasks for the filtered work items on the specified date
            var tasks = await _context.TaskAllocations
                .Include(t => t.WorkItem)
                        .ThenInclude(wi => wi!.WorkArea)
                            .ThenInclude(wa => wa!.Floor)
                                .ThenInclude(f => f!.Building)
                .Include(t => t.WorkItem)
                    .ThenInclude(wi => wi!.ActivityMaster)
                .Where(t => t.WorkItem != null &&
                        t.WorkItem.WorkArea != null &&
                        t.WorkItem.WorkArea.Floor != null &&
                        t.WorkItem.WorkArea.Floor.Building != null &&
                        t.WorkItem.WorkArea.Floor.Building.ProjectId == projectId &&
                        t.WorkItem.ActivityMaster != null &&
                        t.AssignmentDate.Date == currentDate.Date && t.TenantId == tenantId)
                .ToListAsync();

            // Step 6: Aggregate totals and prepare performed activities list
            double totalPlannedTask = 0;
            double totalCompletedTask = 0;
            var performedActivities = new List<PerformedActivites>();

            foreach (var task in tasks)
            {
                totalPlannedTask += task.PlannedTask;
                totalCompletedTask += task.CompletedTask;

                performedActivities.Add(new PerformedActivites
                {
                    ActivityName = task.WorkItem?.ActivityMaster?.ActivityName,
                    BuldingName = task.WorkItem?.WorkArea?.Floor?.Building?.Name,
                    FloorName = task.WorkItem?.WorkArea?.Floor?.FloorName,
                    WorkAreaName = task.WorkItem?.WorkArea?.AreaName,
                    AssignedToday = task.PlannedTask,
                    CompletedToday = task.CompletedTask,
                });
            }

            // Step 7: Count tasks with no reported date
            int pendingReportCount = tasks.Count(t => t.ReportedDate == null);

            // Step 8: Assemble final activity report
            var activityReport = new ActivityReport
            {
                PerformedActivites = performedActivities,
                TotalCompletedWork = totalCompletedTask,
                TotalPlannedWork = totalPlannedTask,
                ReportPending = pendingReportCount,
                TodaysAssigned = tasks.Count
            };

            // Step 9: Log success and respond
            _logger.LogInfo("Activities retrieved for project {ProjectId} on {Date} by employee {EmployeeId}", projectId, currentDate, loggedInEmployee.Id);

            return Ok(ApiResponse<object>.SuccessResponse(activityReport, $"Performed activities for project {project.Name} on {currentDate:d} retrieved successfully.", 200));
        }

        /// <summary>
        /// Retrieves attendance overview grouped by job roles for a project over a specified number of days.
        /// </summary>
        /// <param name="projectId">The project identifier</param>
        /// <param name="days">Number of past days to include (string input, parsed as int)</param>
        [HttpGet("attendance-overview/{projectId}")]
        public async Task<IActionResult> GetAttendanceOverView(Guid projectId, [FromQuery] string days)
        {
            // Step 1: Validate tenant context for multi-tenant data isolation
            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("Empty TenantId in AttendanceOverview request.");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            _logger.LogInfo("GetAttendanceOverView called for ProjectId: {ProjectId}, Days: {Days}", projectId, days);

            // Step 2: Validate project existence under tenant scope
            var project = await _context.Projects
                .AsNoTracking()
                .FirstOrDefaultAsync(p => p.Id == projectId && p.TenantId == tenantId);
            if (project == null)
            {
                _logger.LogWarning("Project not found: {ProjectId}", projectId);
                return BadRequest(ApiResponse<object>.ErrorResponse("Project not found", "Project not found", 400));
            }

            // Step 3: Check if logged-in employee has permission for this project
            var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync();
            bool hasPermission = await _permissionServices.HasProjectPermission(loggedInEmployee!, projectId);
            if (!hasPermission)
            {
                _logger.LogWarning("Unauthorized access by EmployeeId: {EmployeeId} to ProjectId: {ProjectId}", loggedInEmployee.Id, projectId);
                return StatusCode(403, ApiResponse<object>.ErrorResponse(
                    "Permission Denied", "You don't have permission to access this feature", 403));
            }

            // Step 4: Validate and parse 'days' query parameter
            if (!int.TryParse(days, out int dayCount) || dayCount <= 0)
            {
                _logger.LogWarning("Invalid 'days' parameter: {Days}", days);
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid number of days", "Days must be a positive integer", 400));
            }

            // Step 5: Define date range for attendance fetching
            DateTime today = DateTime.UtcNow.Date;
            DateTime startDate = today.AddDays(-dayCount);

            // Step 6: Retrieve allocations and job roles for project and tenant
            var allocations = await _context.ProjectAllocations
                .Where(pa => pa.ProjectId == projectId && pa.TenantId == tenantId)
                .ToListAsync();

            if (!allocations.Any())
            {
                _logger.LogInfo("No allocations found for ProjectId: {ProjectId}", projectId);
                return Ok(ApiResponse<object>.SuccessResponse(new List<AttendanceOverviewVM>(), "No allocations found", 200));
            }

            var jobRoleIds = allocations.Select(pa => pa.JobRoleId).Distinct().ToList();
            var jobRoles = await _context.JobRoles
                .Where(jr => jobRoleIds.Contains(jr.Id) && jr.TenantId == tenantId)
                .ToListAsync();

            // Step 7: Fetch attendance records within date range filtered by project and tenant
            var attendances = await _context.Attendes
                .Where(a => a.ProjectID == projectId &&
                            a.InTime.HasValue &&
                            a.InTime.Value.Date >= startDate &&
                            a.InTime.Value.Date <= today &&
                            a.TenantId == tenantId)
                .ToListAsync();

            // Step 8: Group attendance by date and job role
            var overviewList = new List<AttendanceOverviewVM>();
            for (DateTime date = today; date > startDate; date = date.AddDays(-1))
            {
                foreach (var jobRole in jobRoles)
                {
                    var employeeIdsByRole = allocations
                        .Where(pa => pa.JobRoleId == jobRole.Id)
                        .Select(pa => pa.EmployeeId)
                        .ToList();

                    int presentCount = attendances
                        .Count(a => employeeIdsByRole.Contains(a.EmployeeId) && a.InTime!.Value.Date == date);

                    overviewList.Add(new AttendanceOverviewVM
                    {
                        Role = jobRole.Name,
                        Date = date.ToString("yyyy-MM-dd"),
                        Present = presentCount
                    });
                }
            }

            // Step 9: Sort results by date desc and present count desc
            var sortedResult = overviewList
                .OrderByDescending(r => r.Date)
                .ThenByDescending(r => r.Present)
                .ToList();

            _logger.LogInfo("Attendance overview fetched: ProjectId: {ProjectId}, Records: {Count}", projectId, sortedResult.Count);

            return Ok(ApiResponse<object>.SuccessResponse(sortedResult, $"{sortedResult.Count} records fetched for attendance overview", 200));
        }


        [HttpGet("expense/monthly")]
        public async Task<IActionResult> GetExpenseReportByProjectsAsync([FromQuery] Guid? projectId, [FromQuery] Guid? categoryId, [FromQuery] int months)
        {
            try
            {
                if (tenantId == Guid.Empty)
                {
                    _logger.LogWarning("Invalid request: TenantId is empty on progression endpoint");
                    return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
                }

                // Read-only base filter with tenant scope and non-draft
                var baseQuery = _context.Expenses
                    .AsNoTracking()
                    .Where(e =>
                        e.TenantId == tenantId
                        && e.IsActive
                        && e.StatusId != Draft); // [Server Filters]

                if (months != 0)
                {
                    months = 0 - months;
                    var end = DateTime.UtcNow.Date;
                    var start = end.AddMonths(months); // inclusive EOD
                    baseQuery = baseQuery.Where(e => e.TransactionDate >= start
                        && e.TransactionDate <= end);
                }

                if (projectId.HasValue)
                    baseQuery = baseQuery.Where(e => e.ProjectId == projectId);

                if (categoryId.HasValue)
                    baseQuery = baseQuery.Where(e => e.ExpensesTypeId == categoryId);

                // Single server-side group/aggregate by project
                var report = await baseQuery
                     .AsNoTracking()
                     .GroupBy(e => new { e.TransactionDate.Year, e.TransactionDate.Month })
                     .Select(g => new
                     {
                         Year = g.Key.Year,
                         Month = g.Key.Month,
                         Total = g.Sum(x => x.Amount),
                         Count = g.Count()
                     })
                     .OrderBy(x => x.Year).ThenBy(x => x.Month)
                     .ToListAsync();

                var culture = CultureInfo.GetCultureInfo("en-IN"); // pick desired locale

                var response = report
                    .Select(x => new
                    {
                        MonthName = culture.DateTimeFormat.GetMonthName(x.Month), // e.g., "January"
                        Year = x.Year,
                        Total = x.Total,
                        Count = x.Count
                    }).ToList();

                _logger.LogInfo(
                    "GetExpenseReportByProjects completed. TenantId={TenantId}, Rows={Rows}",
                    tenantId, report.Count); // [Completion Log]

                return Ok(ApiResponse<object>.SuccessResponse(response, "Expense report by project fetched successfully", 200)); // [Success Response]
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("GetExpenseReportByProjects canceled by client. TenantId={TenantId}", tenantId); // [Cancel Log]
                return StatusCode(499, ApiResponse<object>.ErrorResponse("Client has canceled the opration", "Client has canceled the opration", 499)); // [Cancel Response]
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "GetExpenseReportByProjects failed. TenantId={TenantId}",
                    tenantId); // [Error Log]
                return StatusCode(500,
                    ApiResponse<object>.ErrorResponse("An error occurred while fetching the expense report.", 500)); // [Error Response]
            }
        }

        [HttpGet("expense/type")]
        public async Task<IActionResult> GetExpenseReportByExpenseTypeAsync([FromQuery] Guid? projectId, [FromQuery] DateTime startDate, [FromQuery] DateTime endDate)
        {
            // Structured log: entering action with filters
            _logger.LogDebug(
                "GetExpenseReportByExpenseType started. TenantId={TenantId}, ProjectId={ProjectId}, StartDate={StartDate}, EndDate={EndDate}",
                tenantId, projectId ?? Guid.Empty, startDate, endDate); // [Start Log]

            if (tenantId == Guid.Empty)
            {
                _logger.LogWarning("Invalid request: TenantId is empty on progression endpoint");
                return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
            }

            try
            {
                // Compose base query: push filters to DB, avoid client evaluation
                IQueryable<Expenses> baseQuery = _context.Expenses
                    .AsNoTracking() // Reduce tracking overhead for read-only endpoint
                    .Where(e => e.TenantId == tenantId
                                && e.IsActive
                                && e.StatusId != Draft
                                && e.TransactionDate >= startDate
                                && e.TransactionDate <= endDate.AddDays(1).AddTicks(-1));

                if (projectId.HasValue)
                    baseQuery = baseQuery.Where(e => e.ProjectId == projectId.Value); // [Filter]

                // Project to a minimal shape before grouping to avoid loading navigation graphs
                // Group by expense type name; adjust to the correct key if ExpensesCategory is an enum or navigation
                var query = baseQuery
                    .Where(e => e.ExpensesType != null)
                    .Select(e => new
                    {
                        ExpenseTypeName = e.ExpensesType!.Name, // If enum, use e.ExpensesCategory.ToString()
                        Amount = e.Amount,
                        StatusId = e.StatusId
                    })
                    .GroupBy(x => x.ExpenseTypeName)
                    .Select(g => new
                    {
                        ProjectName = g.Key, // Original code used g.Key!.Name; here the grouping key is already a string
                        TotalApprovedAmount = g.Where(x => x.StatusId == Processed
                                                         || x.StatusId == ProcessPending).Sum(x => x.Amount),
                        TotalPendingAmount = g.Where(x => x.StatusId != Processed
                                                       && x.StatusId != RejectedByReviewer
                                                       && x.StatusId != RejectedByApprover)
                                              .Sum(x => x.Amount),
                        TotalRejectedAmount = g.Where(x => x.StatusId == RejectedByReviewer
                                                        || x.StatusId == RejectedByApprover)
                                               .Sum(x => x.Amount),
                        TotalProcessedAmount = g.Where(x => x.StatusId == Processed)
                                                .Sum(x => x.Amount)
                    })
                    .OrderBy(r => r.ProjectName); // Server-side order

                var report = await query.ToListAsync(); // Single round-trip

                var response = new
                {
                    Report = report,
                    TotalAmount = report.Sum(r => r.TotalApprovedAmount)
                };

                _logger.LogInfo(
                    "GetExpenseReportByExpenseType completed. TenantId={TenantId}, Filters: ProjectId={ProjectId}, StartDate={StartDate}, EndDate={EndDate}, Rows={RowCount}, TotalAmount={TotalAmount}",
                    tenantId, projectId ?? Guid.Empty, startDate, endDate, report.Count, response.TotalAmount); // [Completion Log]

                return Ok(ApiResponse<object>.SuccessResponse(response, "Expense report by expense type fetched successfully", 200)); // [Success Response]
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("GetExpenseReportByExpenseType canceled by client. TenantId={TenantId}", tenantId); // [Cancel Log] [memory:4]
                return StatusCode(499, ApiResponse<object>.ErrorResponse("Client has canceled the opration", "Client has canceled the opration", 499)); // [Cancel Response]
            }
            catch (Exception ex)
            {
                _logger.LogError(ex,
                    "GetExpenseReportByExpenseType failed. TenantId={TenantId}, ProjectId={ProjectId}, StartDate={StartDate}, EndDate={EndDate}",
                    tenantId, projectId ?? Guid.Empty, startDate, endDate); // [Error Log]
                return StatusCode(StatusCodes.Status500InternalServerError,
                    ApiResponse<object>.ErrorResponse("An error occurred while fetching the expense report.", 500)); // [Error Response]
            }
        }

        [HttpGet("expense/pendings")]
        public async Task<IActionResult> GetPendingExpenseListAsync([FromQuery] Guid? projectId)
        {
            // Start log with correlation fields
            _logger.LogDebug(
                "GetPendingExpenseListAsync started. Project={ProjectId} TenantId={TenantId}", projectId ?? Guid.Empty, tenantId); // [Start Log]

            try
            {
                if (tenantId == Guid.Empty)
                {
                    _logger.LogWarning("Invalid request: TenantId is empty on progression endpoint");
                    return BadRequest(ApiResponse<object>.ErrorResponse("Invalid TenantId", "Provided Invalid TenantId", 400));
                }

                // Resolve current employee once; avoid using scoped services inside Task.Run
                var loggedInEmployee = await _userHelper.GetCurrentEmployeeAsync(); // [User Context]

                // Resolve permission service from current scope once
                using var scope = _serviceScopeFactory.CreateScope();

                // Fire permission checks concurrently without Task.Run; these are async I/O methods

                var hasReviewPermissionTask = Task.Run(async () =>
                {
                    var _permission = scope.ServiceProvider.GetRequiredService<PermissionServices>();
                    return await _permission.HasPermission(PermissionsMaster.ExpenseReview, loggedInEmployee.Id);
                });

                var hasApprovePermissionTask = Task.Run(async () =>
                {
                    var _permission = scope.ServiceProvider.GetRequiredService<PermissionServices>();
                    return await _permission.HasPermission(PermissionsMaster.ExpenseApprove, loggedInEmployee.Id);
                });

                var hasProcessPermissionTask = Task.Run(async () =>
                {
                    var _permission = scope.ServiceProvider.GetRequiredService<PermissionServices>();
                    return await _permission.HasPermission(PermissionsMaster.ExpenseProcess, loggedInEmployee.Id);
                });

                var hasManagePermissionTask = Task.Run(async () =>
                {
                    var _permission = scope.ServiceProvider.GetRequiredService<PermissionServices>();
                    return await _permission.HasPermission(PermissionsMaster.ExpenseManage, loggedInEmployee.Id);
                });

                await Task.WhenAll(hasReviewPermissionTask, hasApprovePermissionTask, hasProcessPermissionTask, hasManagePermissionTask); // [Parallel Await]

                var hasReviewPermission = hasReviewPermissionTask.Result;
                var hasApprovePermission = hasApprovePermissionTask.Result;
                var hasProcessPermission = hasProcessPermissionTask.Result;
                var hasManagePermission = hasManagePermissionTask.Result;

                _logger.LogInfo(
                    "Permissions resolved: Review={Review}, Approve={Approve}, Process={Process}",
                    hasReviewPermission, hasApprovePermission, hasProcessPermission); // [Permissions Log]

                // Build base query: read-only, tenant-scoped
                var baseQuery = _context.Expenses
                    .Include(e => e.Status)
                    .AsNoTracking() // Reduce tracking overhead for read-only list
                    .Where(e => e.IsActive && e.TenantId == tenantId && e.StatusId != Processed && e.Status != null); // [Base Filter]

                // Project to DTO in SQL to avoid heavy Include graph.
                if (projectId.HasValue)
                    baseQuery = baseQuery.Where(e => e.ProjectId == projectId);

                // Prefer ProjectTo when profiles exist; otherwise project minimal fields
                var expenses = await baseQuery
                    .ToListAsync(); // Single round-trip; no Include needed for this shape

                var draftExpenses = expenses.Where(e => e.StatusId == Draft && e.CreatedById == loggedInEmployee.Id).ToList();
                var reviewExpenses = expenses.Where(e => (hasReviewPermission || e.CreatedById == loggedInEmployee.Id) && e.StatusId == Review).ToList();
                var approveExpenses = expenses.Where(e => (hasApprovePermission || e.CreatedById == loggedInEmployee.Id) && e.StatusId == Approve).ToList();
                var processPendingExpenses = expenses.Where(e => (hasProcessPermission || e.CreatedById == loggedInEmployee.Id) && e.StatusId == ProcessPending).ToList();
                var submitedExpenses = expenses.Where(e => e.StatusId != Draft && e.CreatedById == loggedInEmployee.Id).ToList();
                var totalAmount = expenses.Where(e => e.StatusId != Draft).Sum(e => e.Amount);

                if (hasManagePermission)
                {
                    var response = new
                    {
                        Draft = new
                        {
                            Count = draftExpenses.Count,
                            TotalAmount = draftExpenses.Sum(e => e.Amount)
                        },
                        ReviewPending = new
                        {
                            Count = reviewExpenses.Count,
                            TotalAmount = reviewExpenses.Sum(e => e.Amount)
                        },
                        ApprovePending = new
                        {
                            Count = approveExpenses.Count,
                            TotalAmount = approveExpenses.Sum(e => e.Amount)
                        },
                        ProcessPending = new
                        {
                            Count = processPendingExpenses.Count,
                            TotalAmount = processPendingExpenses.Sum(e => e.Amount)
                        },
                        Submited = new
                        {
                            Count = submitedExpenses.Count,
                            TotalAmount = submitedExpenses.Sum(e => e.Amount)
                        },
                        TotalAmount = totalAmount
                    };
                    _logger.LogInfo(
                        "GetPendingExpenseListAsync completed. TenantId={TenantId}",
                        tenantId); // [Completion Log]

                    return Ok(ApiResponse<object>.SuccessResponse(response, "Pending Expenses fetched successfully", 200)); // [Success Response]
                }
                else
                {
                    var response = new
                    {
                        Draft = new
                        {
                            Count = draftExpenses.Count
                        },
                        ReviewPending = new
                        {
                            Count = reviewExpenses.Count
                        },
                        ApprovePending = new
                        {
                            Count = approveExpenses.Count
                        },
                        ProcessPending = new
                        {
                            Count = processPendingExpenses.Count
                        },
                        Submited = new
                        {
                            Count = submitedExpenses.Count
                        },
                        TotalAmount = totalAmount
                    };
                    _logger.LogInfo(
                        "GetPendingExpenseListAsync completed. TenantId={TenantId}",
                        tenantId); // [Completion Log]

                    return Ok(ApiResponse<object>.SuccessResponse(response, "Pending Expenses fetched successfully", 200)); // [Success Response]
                }
            }
            catch (OperationCanceledException)
            {
                _logger.LogWarning("GetPendingExpenseListAsync canceled by client. TenantId={TenantId}", tenantId); // [Cancel Log]
                return StatusCode(499, ApiResponse<object>.ErrorResponse("Client has canceled the opration", "Client has canceled the opration", 499)); // [Cancel Response]
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "GetPendingExpenseListAsync failed. TenantId={TenantId}", tenantId); // [Error Log]
                return StatusCode(500,
                    ApiResponse<object>.ErrorResponse("An error occurred while fetching pending expenses.", "An error occurred while fetching pending expenses.", 500)); // [Error Response]
            }
        }
    }
}