using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using MySqlConnector;
using ODCWeb.DataAccess.Data;
using ODCWeb.DataAccess.Repository;
using ODCWeb.DataAccess.Repository.IRepository;
using ODCWeb.Models;
using ODCWeb.Models.ViewModels;
using Syncfusion.Drawing;
using Syncfusion.XlsIO;
using System.Data;
using System.Security.Claims;

namespace ODCWeb.Areas.Staff.Controllers
{

    [Area("Staff")]

    public class BQController : Controller
    {
        private readonly ApplicationDbContext _db;
        private readonly IUnitOfWork _unitOfWork;
        private readonly UserManager<ApplicationUser> userManager;
        private readonly ITemplateService _templateService;
        public static List<ActionTask> _tasks = new List<ActionTask>();
        public static List<Property> _areas = new List<Property>();

        public static List<TaskWithArea> tasksareas = new List<TaskWithArea>();
        public BQController(ApplicationDbContext applicationDbContext, UserManager<ApplicationUser> userManager, IUnitOfWork unitOfWork, ITemplateService templateService)
        {
            _db = applicationDbContext;
            _unitOfWork = unitOfWork;
            this.userManager = userManager;
            _templateService = templateService;
        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpGet]
        public IActionResult Index()
        {
            _tasks.Clear();
            _areas.Clear();
            IEnumerable<SelectListItem> ProjectList = _unitOfWork.ODCProject.GetAll().Select(u => new SelectListItem
            {
                Text = u.Name,
                Value = u.Id.ToString()
            });
            ViewBag.ProjectList = ProjectList;
            return View();
        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpPost]
        public IActionResult Index(int list)
        {

            return RedirectToAction("BQListComplete", "BQ", new { id = list });
        }
        /// floor bq       
        [Authorize(Roles = "Admin,Employee")]
        [HttpGet]
        public IActionResult Index2()
        {
            _tasks.Clear();
            _areas.Clear();
            IEnumerable<SelectListItem> ProjectList = _unitOfWork.ODCProject.GetAll().Select(u => new SelectListItem
            {
                Text = u.Name,
                Value = u.Id.ToString()
            });
            ViewBag.ProjectList = ProjectList;
            return View();
        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpPost]
        public IActionResult Index2(int list)
        {

            return RedirectToAction("FloorBQ", "BQ", new { dat = list });
        }
        /// floor bq       
        [Authorize(Roles = "Admin")]
        [HttpGet]
        public IActionResult EditMarkup(int? id)
        {
            Constants markup = _unitOfWork.Constants.Get(u => u.ConstantsId == id);
            if (markup == null)
            {
                return View("Error");
            }
            else
            {

                return View(markup);
            }
        }
        [Authorize(Roles = "Admin")]
        [HttpPost]
        public IActionResult EditMarkup(Constants obj)
        {
            if (ModelState.IsValid)
            {
                _unitOfWork.Constants.update(obj);
                _unitOfWork.save();
                TempData["success"] = "Markup updated successfully";
                return RedirectToAction("Index", "BQ");
            }
            else
            {
                TempData["error"] = "Something Wrong";
                return View(obj);
            }    

        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpGet]
        public IActionResult FloorBQ(int dat)
        {
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == dat).ToList();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == dat);
            ViewBag.Project = project;

            var location = new Uri($"{Request.Scheme}://{Request.Host}");
            var url = location.AbsoluteUri + "Staff/BQ/Index2";
            ViewBag.ReturnUrl = url;
            ViewBag.Data = "Floors";
            return View(floors);
        }
        
        [Authorize(Roles = "Admin,Employee")]
        public IActionResult BQList(int id)
        {
            tasksareas.Clear();
            _tasks.Clear();
            _areas.Clear();
            Floor floor = _unitOfWork.Floor.Get(u => u.FloorId == id);
            ViewBag.P_Id = floor.ODCProjectId; 
            List<FloorAction> final_actions = new List<FloorAction>();
            final_actions.AddRange(_unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList());
            List<ActionTask> final_tasks = new List<ActionTask>();
            foreach (var itemm in final_actions)
            {
                final_tasks.AddRange(_unitOfWork.Task.Where(u => u.ActionId == itemm.ActionId).ToList());
            }
            _tasks.AddRange(final_tasks);

            Property prop = new Property();
            prop.Name = "area_added";
            prop.Value = 0;
            prop.PropertyId = 0;
            List<Property> areas = new List<Property>();
            
            foreach(var unit in final_tasks)
            {
                bool check = false;
                List<Property> temp = _unitOfWork.Property.Where(u=>u.TaskId == unit.TaskId ).ToList();
                foreach (var unit2 in temp)
                {
                    if (unit2.Name == "Area (sq ft)")
                    {
                        areas.Add(unit2);
                        check = true;
                        break;
                    }
                    
                }
                if (check == false)
                {
                    areas.Add(prop);
                }
                temp.Clear();
            }
            _areas.AddRange(areas);

            
            int v = 0;
            foreach (var item in _tasks)
            {
   
                double? markup = _unitOfWork.Constants.Get(u => u.ConstantsId == 1).MarkUp;
                if (markup == null)
                {
                    markup = 0; 
                }
                int temp = _unitOfWork.Action.Get(y => y.ActionId == item.ActionId).FloorId;
                TaskWithArea taskarea = new()
                {
                    ActionId = item.ActionId,
                    TaskId = item.TaskId,
                    TaskName = item.Name,
                    PropertyId = _areas[v].PropertyId,
                    PropertyName = _areas[v].Name,
                    PropertyValue = _areas[v].Value,
                    ActionName = _unitOfWork.Action.Get(u=>u.ActionId == item.ActionId).Name,
                    FloorId = _unitOfWork.Action.Get(y=>y.ActionId == item.ActionId).FloorId,
                    FloorName = _unitOfWork.Floor.Get(z=>z.FloorId == temp).Name,
                    Rate = (item.Material_Cost + item.Labor_Cost) + ((markup / 100) * (item.Material_Cost + item.Labor_Cost)),
                    Completion = (double)item.Completion,
                    Balance = (item.Material_Cost + item.Labor_Cost) + ((markup / 100) * (item.Material_Cost + item.Labor_Cost)) - ((item.Completion/100)*( (item.Material_Cost + item.Labor_Cost) + ((markup / 100) * (item.Material_Cost + item.Labor_Cost))))
                };
                tasksareas.Add(taskarea);
                v = v + 1;

            }
            

            return View(final_tasks);
        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpGet]
        public IActionResult BQListApi()
        {
            return Json(new { data = tasksareas });
        }
        ///////////////////////////////////////////////////
        [HttpGet]
        public IActionResult ExcelTemplateUpload()
        {
            return View();
        }

        [HttpPost]
        public async Task<IActionResult> ExcelTemplateUpload(IFormFile file)
        {
            try
            {
                var (isValid, message) = await _templateService.ValidateTemplate(file);
                if (!isValid)   
                    return BadRequest(message);

                var userId = User.FindFirst(ClaimTypes.NameIdentifier)?.Value;
                await _templateService.SaveTemplate(file, userId);

                return Ok(new { message = "Template uploaded successfully" });
            }
            catch (Exception ex)
            {
                return StatusCode(500, $"Internal server error: {ex.Message}");
            }
        }

        public IActionResult BQListCompleteOld(int id)
        {
            var project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            if (project == null)
            {
                return NotFound();
            }
            ViewBag.P_Id = id;
            ViewBag.ProjectName = project.Name;
            ViewBag.ProjectLocation = project.Location;

            var floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == id).ToList();
            var final_actions = floors.SelectMany(floor =>
                _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId)).ToList();
            var final_tasks = final_actions.SelectMany(action =>
                _unitOfWork.Task.Where(u => u.ActionId == action.ActionId)
                .OrderBy(u => u.TaskId)).ToList();

            var markup = _unitOfWork.Constants.Get(u => u.ConstantsId == 1).MarkUp ?? 0;
            var tasksWithAreas = new List<TaskWithArea>();
            char taskLetter = 'A';

            foreach (var task in final_tasks)
            {
                DataTask temp_dTask = _unitOfWork.DataTask.Get(u => u.Name == task.Name);
                if (temp_dTask != null)
                {
                    if (temp_dTask.Visible != null)
                    {
                        if (temp_dTask.Visible == true)
                        {
                            var action = _unitOfWork.Action.Get(y => y.ActionId == task.ActionId);
                            var floor = _unitOfWork.Floor.Get(z => z.FloorId == action.FloorId);
                            var property = _unitOfWork.Property
                                .Where(u => u.TaskId == task.TaskId && u.Name == "Area (sq ft)")
                                .FirstOrDefault() ?? new Property { Value = 0 };

                            // Get variants for this task
                            var variants = _unitOfWork.BQTaskVariant
                                .Where(v => v.DataTaskId == temp_dTask.DataTaskId)
                                .ToList();
                            double temp = 2;
                            // If no variants exist, process using the default task properties
                            if (temp == 2)
                            {
                                // Use existing code for default task
                                double? rate = 0;
                                double? balance = 0;
                                string? tempN = "";
                                double? tempV = 0;
                                string? newName = "Temp";

                                DataTask dtask = _unitOfWork.DataTask.Get(u => u.Name == task.Name);
                                rate = dtask.BQRate;
                                if (dtask.BQTaskName != null)
                                {
                                    newName = dtask.BQTaskName;
                                }

                                if (dtask != null && dtask.BQPropertyId != "")
                                {
                                    string formula = dtask.BQPropertyId;

                                    if (formula != null && formula != "")
                                    {
                                        // Check if formula contains at least one property reference (enclosed in 'I')
                                        if (formula.Contains('I'))
                                        {
                                            // Parse the formula by finding property IDs enclosed in 'I' characters
                                            string parsedFormula = formula;
                                            int startIndex = 0;

                                            while (true)
                                            {
                                                int firstI = parsedFormula.IndexOf('I', startIndex);
                                                if (firstI == -1) break;

                                                int secondI = parsedFormula.IndexOf('I', firstI + 1);
                                                if (secondI == -1) break;

                                                // Extract the property ID
                                                string propIdStr = parsedFormula.Substring(firstI + 1, secondI - firstI - 1);
                                                if (int.TryParse(propIdStr, out int propId))
                                                {
                                                    // Find the property with this ID
                                                    TemplateProperty templateProperty = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId == propId);
                                                    if (templateProperty != null)
                                                    {
                                                        // Find the actual property value from the task
                                                        Property taskProperty = _unitOfWork.Property.Get(u => u.TaskId == task.TaskId && u.Name == templateProperty.Name);
                                                        if (taskProperty != null)
                                                        {
                                                            // Replace the property reference with its actual value
                                                            parsedFormula = parsedFormula.Replace($"I{propIdStr}I", taskProperty.Value.ToString());
                                                            tempN = taskProperty.Name; // Use the last property name found
                                                        }
                                                        else
                                                        {
                                                            // If property not found, replace with 0
                                                            parsedFormula = parsedFormula.Replace($"I{propIdStr}I", "0");
                                                        }
                                                    }
                                                    else
                                                    {
                                                        // If template property not found, replace with 0
                                                        parsedFormula = parsedFormula.Replace($"I{propIdStr}I", "0");
                                                    }
                                                }

                                                // Move start index past the current replacement
                                                startIndex = firstI + 1;
                                            }

                                            // Evaluate the formula using DataTable
                                            try
                                            {
                                                DataTable dt = new DataTable();
                                                var result = dt.Compute(parsedFormula, "");
                                                tempV = Convert.ToDouble(result);

                                            }
                                            catch (Exception)
                                            {
                                                // If formula evaluation fails, set tempV to 0
                                                tempV = 0;
                                            }
                                        }
                                        else
                                        {
                                            // If no property references, try to evaluate as a direct formula
                                            try
                                            {
                                                DataTable dt = new DataTable();
                                                var result = dt.Compute(formula, "");
                                                tempV = Convert.ToDouble(result);
                                            }
                                            catch (Exception)
                                            {
                                                // If direct formula fails, set tempV to 0
                                                tempV = 0;
                                            }
                                        }
                                    }
                                }

                                // Fallback if no value is found
                                if (tempN == "" && tempV == 0)
                                {
                                    tempN = property.Name;
                                    tempV = property.Value;
                                }

                                balance = rate * tempV;
                                tasksWithAreas.Add(new TaskWithArea
                                {
                                    ActionId = task.ActionId,
                                    TaskId = task.TaskId,
                                    TaskName = newName,
                                    TaskLetter = taskLetter.ToString(),
                                    PropertyId = property.PropertyId,
                                    PropertyName = tempN,
                                    PropertyValue = tempV,
                                    ActionName = action.Name,
                                    FloorId = action.FloorId,
                                    FloorName = floor.Name,
                                    Rate = balance,
                                    Completion = (double)task.Completion,
                                    Balance = rate,
                                    Progress = (double)task.Completion / 100,
                                    ProjectName = project.Name,
                                    ProjectLocation = project.Location
                                });
                                taskLetter = taskLetter > 'Z' ? 'A' : (char)(taskLetter + 1);
                            }
                            if (variants != null && variants.Any())
                            {
                                // Process each variant as a separate BQ item
                                foreach (var variant in variants)
                                {
                                    double? rate = variant.BQRate ?? temp_dTask.BQRate ?? 0;
                                    string? newName = variant.BQTaskName ?? temp_dTask.BQTaskName ?? "Temp";
                                    string? tempN = "";
                                    double? tempV = 0;

                                    // Use the variant's formula if available, otherwise use the default
                                    string formula = !string.IsNullOrEmpty(variant.BQPropertyId)
                                        ? variant.BQPropertyId
                                        : (temp_dTask.BQPropertyId ?? "");

                                    if (!string.IsNullOrEmpty(formula))
                                    {
                                        // Check if formula contains at least one property reference (enclosed in 'I')
                                        if (formula.Contains('I'))
                                        {
                                            // Parse the formula by finding property IDs enclosed in 'I' characters
                                            string parsedFormula = formula;
                                            int startIndex = 0;

                                            while (true)
                                            {
                                                int firstI = parsedFormula.IndexOf('I', startIndex);
                                                if (firstI == -1) break;

                                                int secondI = parsedFormula.IndexOf('I', firstI + 1);
                                                if (secondI == -1) break;

                                                // Extract the property ID
                                                string propIdStr = parsedFormula.Substring(firstI + 1, secondI - firstI - 1);
                                                if (int.TryParse(propIdStr, out int propId))
                                                {
                                                    // Find the property with this ID
                                                    TemplateProperty templateProperty = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId == propId);
                                                    if (templateProperty != null)
                                                    {
                                                        // Find the actual property value from the task
                                                        Property taskProperty = _unitOfWork.Property.Get(u => u.TaskId == task.TaskId && u.Name == templateProperty.Name);
                                                        if (taskProperty != null)
                                                        {
                                                            // Replace the property reference with its actual value
                                                            parsedFormula = parsedFormula.Replace($"I{propIdStr}I", taskProperty.Value.ToString());
                                                            tempN = taskProperty.Name; // Use the last property name found
                                                        }
                                                        else
                                                        {
                                                            // If property not found, replace with 0
                                                            parsedFormula = parsedFormula.Replace($"I{propIdStr}I", "0");
                                                        }
                                                    }
                                                    else
                                                    {
                                                        // If template property not found, replace with 0
                                                        parsedFormula = parsedFormula.Replace($"I{propIdStr}I", "0");
                                                    }
                                                }

                                                // Move start index past the current replacement
                                                startIndex = firstI + 1;
                                            }

                                            // Evaluate the formula using DataTable
                                            try
                                            {
                                                DataTable dt = new DataTable();
                                                var result = dt.Compute(parsedFormula, "");
                                                tempV = Convert.ToDouble(result);

                                            }
                                            catch (Exception)
                                            {
                                                // If formula evaluation fails, set tempV to 0
                                                tempV = 0;
                                            }
                                        }
                                        else
                                        {
                                            // If no property references, try to evaluate as a direct formula
                                            try
                                            {
                                                DataTable dt = new DataTable();
                                                var result = dt.Compute(formula, "");
                                                tempV = Convert.ToDouble(result);
                                            }
                                            catch (Exception)
                                            {
                                                // If direct formula fails, set tempV to 0
                                                tempV = 0;
                                            }
                                        }
                                    }

                                    // Fallback if no value is found
                                    if (tempN == "" && tempV == 0)
                                    {
                                        tempN = property.Name;
                                        tempV = property.Value;
                                    }

                                    double? balance = rate * tempV;
                                    tasksWithAreas.Add(new TaskWithArea
                                    {
                                        ActionId = task.ActionId,
                                        TaskId = task.TaskId,
                                        TaskName = newName,
                                        TaskLetter = taskLetter.ToString(),
                                        PropertyId = property.PropertyId,
                                        PropertyName = tempN,
                                        PropertyValue = tempV,
                                        ActionName = action.Name,
                                        FloorId = action.FloorId,
                                        FloorName = floor.Name,
                                        Rate = balance,
                                        Completion = (double)task.Completion,
                                        Balance = rate,
                                        Progress = (double)task.Completion / 100,
                                        ProjectName = project.Name,
                                        ProjectLocation = project.Location
                                    });
                                    taskLetter = taskLetter > 'Z' ? 'A' : (char)(taskLetter + 1);
                                }
                            }
                        }
                    }
                }
            }

            return View(tasksWithAreas);
        }
        public IActionResult BQListComplete(int id)
        {
            var project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            if (project == null)
            {
                return NotFound();
            }
            ViewBag.P_Id = id;
            ViewBag.ProjectName = project.Name;
            ViewBag.ProjectLocation = project.Location;

            var floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == id).ToList();
            var final_actions = floors.SelectMany(floor =>
                _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId)).ToList();
            var final_tasks = final_actions.SelectMany(action =>
                _unitOfWork.Task.Where(u => u.ActionId == action.ActionId)
                .OrderBy(u => u.TaskId)).ToList();

            // Create task dictionary for quick lookups
            var taskDictionary = final_tasks.ToDictionary(t => t.TaskId, t => t);

            var markup = _unitOfWork.Constants.Get(u => u.ConstantsId == 1).MarkUp ?? 0;
            var tasksWithAreas = new List<TaskWithArea>();
            char taskLetter = 'A';

            foreach (var task in final_tasks)
            {
                DataTask temp_dTask = _unitOfWork.DataTask.Get(u => u.Name == task.Name);
                if (temp_dTask != null)
                {
                    if (temp_dTask.Visible != null)
                    {
                        if (temp_dTask.Visible == true)
                        {
                            var action = _unitOfWork.Action.Get(y => y.ActionId == task.ActionId);
                            var floor = _unitOfWork.Floor.Get(z => z.FloorId == action.FloorId);
                            var property = _unitOfWork.Property
                                .Where(u => u.TaskId == task.TaskId && u.Name == "Area (sq ft)")
                                .FirstOrDefault() ?? new Property { Value = 0 };

                            // Get variants for this task
                            var variants = _unitOfWork.BQTaskVariant
                                .Where(v => v.DataTaskId == temp_dTask.DataTaskId)
                                .ToList();

                            // Always process the default task
                            ProcessMainTask(task, temp_dTask, action, floor, property, ref taskLetter,
                                project, tasksWithAreas, taskDictionary);

                            // Process each variant as a separate BQ item
                            if (variants != null && variants.Any())
                            {
                                foreach (var variant in variants)
                                {
                                    ProcessVariant(variant, task, temp_dTask, action, floor, property,
                                        ref taskLetter, project, tasksWithAreas, taskDictionary);
                                }
                            }
                        }
                    }
                }
            }

            return View(tasksWithAreas);
        }

        private void ProcessMainTask(
            ActionTask task,
            DataTask dtask,
            Models.FloorAction action,
            Floor floor,
            Property property,
            ref char taskLetter,
            ODCProject project,
            List<TaskWithArea> tasksWithAreas,
            Dictionary<int, ActionTask> taskDictionary)
        {
            double? rate = 0;
            double? balance = 0;
            string? tempN = "";
            double? tempV = 0;
            string? newName = "Temp";

            rate = dtask.BQRate;
            if (dtask.BQTaskName != null)
            {
                newName = dtask.BQTaskName;
            }

            if (dtask != null && dtask.BQPropertyId != "")
            {
                string formula = dtask.BQPropertyId;
                (tempN, tempV) = EvaluateFormula(formula, task, taskDictionary);
            }

            // Fallback if no value is found
            if (tempN == "" && tempV == 0)
            {
                tempN = property.Name;
                tempV = property.Value;
            }

            balance = rate * tempV;
            tasksWithAreas.Add(new TaskWithArea
            {
                ActionId = task.ActionId,
                TaskId = task.TaskId,
                TaskName = newName,
                TaskLetter = taskLetter.ToString(),
                PropertyId = property.PropertyId,
                PropertyName = tempN,
                PropertyValue = tempV,
                ActionName = action.EffectiveDisplayNameA,
                FloorId = action.FloorId,
                FloorName = floor.Name,
                Rate = balance,
                Completion = (double)task.Completion,
                Balance = rate,
                Progress = (double)task.Completion / 100,
                ProjectName = project.Name,
                ProjectLocation = project.Location
            });
            taskLetter = taskLetter > 'Z' ? 'A' : (char)(taskLetter + 1);
        }

        private void ProcessVariant(
            BQTaskVariant variant,
            ActionTask task,
            DataTask dtask,
            Models.FloorAction action,
            Floor floor,
            Property property,
            ref char taskLetter,
            ODCProject project,
            List<TaskWithArea> tasksWithAreas,
            Dictionary<int, ActionTask> taskDictionary)
        {
            double? rate = variant.BQRate ?? dtask.BQRate ?? 0;
            string? newName = variant.BQTaskName ?? dtask.BQTaskName ?? "Temp";
            string? tempN = "";
            double? tempV = 0;

            // Use the variant's formula if available, otherwise use the default
            string formula = !string.IsNullOrEmpty(variant.BQPropertyId)
                ? variant.BQPropertyId
                : (dtask.BQPropertyId ?? "");

            (tempN, tempV) = EvaluateFormula(formula, task, taskDictionary);

            // Fallback if no value is found
            if (tempN == "" && tempV == 0)
            {
                tempN = property.Name;
                tempV = property.Value;
            }

            double? balance = rate * tempV;
            tasksWithAreas.Add(new TaskWithArea
            {
                ActionId = task.ActionId,
                TaskId = task.TaskId,
                TaskName = newName,
                TaskLetter = taskLetter.ToString(),
                PropertyId = property.PropertyId,
                PropertyName = tempN,
                PropertyValue = tempV,
                ActionName = action.EffectiveDisplayNameA,
                FloorId = action.FloorId,
                FloorName = floor.Name,
                Rate = balance,
                Completion = (double)task.Completion,
                Balance = rate,
                Progress = (double)task.Completion / 100,
                ProjectName = project.Name,
                ProjectLocation = project.Location
            });
            taskLetter = taskLetter > 'Z' ? 'A' : (char)(taskLetter + 1);
        }

        private (string propertyName, double? propertyValue) EvaluateFormula(
            string formula,
            ActionTask currentTask,
            Dictionary<int, ActionTask> taskDictionary)
        {
            string propertyName = "";
            double? propertyValue = 0;
            int actionId = currentTask.ActionId;
            if (string.IsNullOrEmpty(formula))
            {
                return (propertyName, propertyValue);
            }

            // Check if formula contains at least one property reference (enclosed in 'I')
            if (formula.Contains('I'))
            {
                // Parse the formula by finding property references enclosed in 'I' characters
                string parsedFormula = formula;
                int startIndex = 0;
                List<string> usedProperties = new List<string>();

                while (true)
                {
                    int firstI = parsedFormula.IndexOf('I', startIndex);
                    if (firstI == -1) break;

                    int secondI = parsedFormula.IndexOf('I', firstI + 1);
                    if (secondI == -1) break;

                    // Extract the property reference
                    string propRef = parsedFormula.Substring(firstI + 1, secondI - firstI - 1);

                    // Check if it's a cross-task reference (PropID:TaskID)
                    if (propRef.Contains(':'))
                    {
                        string[] parts = propRef.Split(':');
                        if (parts.Length == 2 &&
                            int.TryParse(parts[0], out int propId) &&
                            int.TryParse(parts[1], out int taskId))
                        {
                            // First find the template property
                            TemplateProperty templateProperty = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId == propId);
                            DataTask ttask = _unitOfWork.DataTask.Get(u => u.DataTaskId == taskId);
                            if (templateProperty != null && ttask != null)
                            {
                                // Then find the property in the specified task
                                /*if (taskDictionary.ContainsKey(taskId))*/
                                ActionTask tttask = _unitOfWork.Task.Get(u => u.Name == ttask.Name && u.ActionId == actionId);
                                if (tttask != null)
                                {
                                    Property taskProperty = _unitOfWork.Property
                                        .Get(u => u.TaskId == tttask.TaskId && u.Name == templateProperty.Name );

                                    if (taskProperty != null)
                                    {
                                        parsedFormula = parsedFormula.Replace($"I{propRef}I", taskProperty.Value.ToString());
                                        string temp = parsedFormula;
/*                                        // Get task name for display
                                        ActionTask refTask = taskDictionary[taskId];
                                        string propDescription = $"{refTask.Name}.{templateProperty.Name}";
                                        usedProperties.Add(propDescription);
                                        propertyName = propDescription; // Use last property name*/
                                    }
                                    else
                                    {
                                        parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                                    }
                                }
                                else
                                {
                                    // Task not found in dictionary
                                    parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                                }
                            }
                            else
                            {
                                // Template property not found
                                parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                            }
                        }
                        else
                        {
                            // Invalid format
                            parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                        }
                    }
                    else if (int.TryParse(propRef, out int propId))
                    {
                        // Regular property reference from current task
                        TemplateProperty templateProperty = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId == propId);
                        if (templateProperty != null)
                        {
                            Property taskProperty = _unitOfWork.Property
                                .Get(u => u.TaskId == currentTask.TaskId && u.Name == templateProperty.Name);

                            if (taskProperty != null)
                            {
                                parsedFormula = parsedFormula.Replace($"I{propRef}I", taskProperty.Value.ToString());
                                propertyName = templateProperty.Name; // Use last property name
                                usedProperties.Add(templateProperty.Name);
                            }
                            else
                            {
                                parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                            }
                        }
                        else
                        {
                            parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                        }
                    }
                    else
                    {
                        // Not a recognized format
                        parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                    }

                    // Move start index past the current replacement
                    startIndex = firstI + 1;
                }

                // If multiple properties were used, create a combined name
                if (usedProperties.Count > 1)
                {
                    propertyName = $"Combined ({string.Join(", ", usedProperties)})";
                }

                // Evaluate the formula using DataTable
                try
                {
                    DataTable dt = new DataTable();
                    var result = dt.Compute(parsedFormula, "");
                    propertyValue = Convert.ToDouble(result);
                }
                catch (Exception)
                {
                    // If formula evaluation fails, set propertyValue to 0
                    propertyValue = 0;
                }
            }
            else
            {
                // If no property references, try to evaluate as a direct formula
                try
                {
                    DataTable dt = new DataTable();
                    var result = dt.Compute(formula, "");
                    propertyValue = Convert.ToDouble(result);
                    propertyName = "Formula Value";
                }
                catch (Exception)
                {
                    // If direct formula fails, set propertyValue to 0
                    propertyValue = 0;
                }
            }

            return (propertyName, propertyValue);
        }

        private (string propertyName, double? propertyValue) EvaluateFormula(
            string formula,
            int currentTaskId,
            Dictionary<int, ActionTask> taskDictionary,
            Dictionary<int, Dictionary<string, Property>> taskPropertiesDict,
            Dictionary<int, TemplateProperty> templatePropertyDict,
            Property defaultProperty)
        {
            string propertyName = "";
            double? propertyValue = 0;

            if (string.IsNullOrEmpty(formula))
            {
                // If no formula, return default property
                return (defaultProperty.Name, defaultProperty.Value);
            }

            // Check if formula contains at least one property reference (enclosed in 'I')
            if (formula.Contains('I'))
            {
                // Parse the formula by finding property IDs enclosed in 'I' characters
                string parsedFormula = formula;
                int startIndex = 0;
                List<string> usedPropertyDetails = new List<string>();

                while (true)
                {
                    int firstI = parsedFormula.IndexOf('I', startIndex);
                    if (firstI == -1) break;

                    int secondI = parsedFormula.IndexOf('I', firstI + 1);
                    if (secondI == -1) break;

                    // Extract the property reference
                    string propRef = parsedFormula.Substring(firstI + 1, secondI - firstI - 1);

                    // Check if it's in the format PropertyId:TaskId to reference a property from another task
                    if (propRef.Contains(':'))
                    {
                        string[] parts = propRef.Split(':');
                        if (parts.Length == 2 &&
                            int.TryParse(parts[0], out int propId) &&
                            int.TryParse(parts[1], out int taskId))
                        {
                            // It's a reference to a property in another task
                            if (templatePropertyDict.ContainsKey(propId) &&
                                taskPropertiesDict.ContainsKey(taskId) &&
                                taskPropertiesDict[taskId].ContainsKey(templatePropertyDict[propId].Name))
                            {
                                var property = taskPropertiesDict[taskId][templatePropertyDict[propId].Name];
                                parsedFormula = parsedFormula.Replace($"I{propRef}I", property.Value?.ToString() ?? "0");

                                // Get the task name for display
                                string taskName = taskDictionary.ContainsKey(taskId)
                                    ? taskDictionary[taskId].Name
                                    : $"Task {taskId}";

                                usedPropertyDetails.Add($"{taskName}.{templatePropertyDict[propId].Name}");
                            }
                            else
                            {
                                parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                            }
                        }
                        else
                        {
                            parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                        }
                    }
                    else if (int.TryParse(propRef, out int propId))
                    {
                        // It's a reference to a property in the current task
                        if (templatePropertyDict.ContainsKey(propId) &&
                            taskPropertiesDict.ContainsKey(currentTaskId) &&
                            taskPropertiesDict[currentTaskId].ContainsKey(templatePropertyDict[propId].Name))
                        {
                            var property = taskPropertiesDict[currentTaskId][templatePropertyDict[propId].Name];
                            parsedFormula = parsedFormula.Replace($"I{propRef}I", property.Value?.ToString() ?? "0");

                            usedPropertyDetails.Add(templatePropertyDict[propId].Name);
                        }
                        else
                        {
                            parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                        }
                    }
                    else
                    {
                        parsedFormula = parsedFormula.Replace($"I{propRef}I", "0");
                    }

                    // Move start index past the current replacement
                    startIndex = firstI + 1;
                }

                // Set the property name based on used properties
                if (usedPropertyDetails.Count == 1)
                {
                    propertyName = usedPropertyDetails[0];
                }
                else if (usedPropertyDetails.Count > 1)
                {
                    propertyName = $"Combined ({string.Join(", ", usedPropertyDetails)})";
                }

                // Evaluate the formula using DataTable
                try
                {
                    DataTable dt = new DataTable();
                    var result = dt.Compute(parsedFormula, "");
                    propertyValue = Convert.ToDouble(result);
                }
                catch (Exception)
                {
                    // If formula evaluation fails, set propertyValue to 0
                    propertyValue = 0;
                }
            }
            else
            {
                // If no property references, try to evaluate as a direct formula
                try
                {
                    DataTable dt = new DataTable();
                    var result = dt.Compute(formula, "");
                    propertyValue = Convert.ToDouble(result);
                    propertyName = "Formula Value";
                }
                catch (Exception)
                {
                    // If direct formula fails, set propertyValue to 0
                    propertyValue = 0;
                    propertyName = defaultProperty.Name;
                }
            }

            // If we still don't have a property name, use the default property name
            if (string.IsNullOrEmpty(propertyName))
            {
                propertyName = defaultProperty.Name;
            }

            return (propertyName, propertyValue);
        }

        [HttpPost]
        private string GetUnit(string taskName)
        {
            // Convert task name to lowercase for case-insensitive comparison
            string task = taskName.ToLower();

            // Area measurements (m²)
            string[] m2Terms = new[] {
        "slab", "floor", "formwork", "shed", "setting out", "excavate", "wall",
        "walling", "rendering", "surface", "tile", "tiling", "screed", "cement and sand",
        "suspended", "soffit", "side", "column", "beam", "footing",
          "mesh", "waterproofing", "window", "painting", "paint", "render"
    };

            // Weight measurements (kg)
            string[] kgTerms = new[] {
        "rod", "rods", "bars", "steel", "reinforcement", "mm rod", "brc"
    };

            // Linear measurements (m)
            string[] mTerms = new[] {
        "length", "trench", "perimeter", "linear", "arris", "jamb", "frame",
        "edge", "deep", "stirrup", "height", "labor arris", "door jambs"
    };

            // Volume measurements (m³)
            string[] m3Terms = new[] {
        "concrete", "volume", "fill", "filling", "marl", "excavated", "consolidate", "stiffener", "strip Foundation"
    };

            // Item/unit count (nr, item, sum)
            string[] itemTerms = new[] {
        "number", "piece", "unit", "nos", "pump", "bail", "kitchen", "cupboard",
        "sink", "fitting", "fixture", "provisional sum", "ps", "allow for", "plumbing"
    };

            // Check for matches in order of specificity
            if (task.Contains("water") && (task.Contains("pump") || task.Contains("bail")))
            {
                return "item";
            }

            if (task.Contains("provisional sum") || task.Contains("ps of"))
            {
                return "sum";
            }

            // Check for reinforcement rods which should be kg
            if (kgTerms.Any(term => task.Contains(term.ToLower())))
            {
                return "kg.";
            }

            // Check for linear measurements
            if (mTerms.Any(term => task.Contains(term.ToLower())))
            {
                return "m";
            }

            // Check for volume measurements
            if (m3Terms.Any(term => task.Contains(term.ToLower())))
            {
                return "m³";
            }

            // Check for area measurements
            if (m2Terms.Any(term => task.Contains(term.ToLower())))
            {
                return "m²";
            }

            // Check for item counts
            if (itemTerms.Any(term => task.Contains(term.ToLower())))
            {
                return "nr";
            }

            // Special cases
            if (task.Contains("plumbing") || task.Contains("drainage") || task.Contains("installation"))
            {
                if (task.Contains("ps of") || task.Contains("provisional sum"))
                    return "sum";
                return "item";
            }

            return ""; // Default empty unit if no match found
        }
            
        

        private string GetExcelColumnName(int columnNumber)
        {
            string columnName = "";
            while (columnNumber > 0)
            {
                int remainder = (columnNumber - 1) % 26;
                columnName = Convert.ToChar(65 + remainder) + columnName;
                columnNumber = (columnNumber - 1) / 26;
            }
            return columnName;
        } 
        
        public IActionResult GenerateTaskPropertiesExcel()
        {
            try
            {
                using (ExcelEngine excelEngine = new ExcelEngine())
                {
                    IApplication application = excelEngine.Excel;
                    application.DefaultVersion = ExcelVersion.Excel2016;
                    IWorkbook workbook = application.Workbooks.Create(1);
                    IWorksheet worksheet = workbook.Worksheets[0];

                    int currentRow = 1;
                    IEnumerable<ActionTask> tasks = _unitOfWork.Task.GetAll();
                    foreach (var task in tasks)
                    {
                        var properties = _unitOfWork.Property
                            .Where(p => p.TaskId == task.TaskId)
                            .OrderBy(p => p.Name)
                            .ToList();

                        if (properties.Any())
                        {
                            worksheet.Range[currentRow, 1].Text = task.Name;
                            worksheet.Range[currentRow, 1].CellStyle.Font.Bold = true;

                            int propertyColumn = 3;
                            foreach (var property in properties)
                            {
                                worksheet.Range[currentRow, propertyColumn].Text = property.Name;
                                propertyColumn++;
                            }
                            currentRow++;
                        }
                    }

                    worksheet.UsedRange.AutofitColumns();
                    worksheet.UsedRange.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
                    worksheet.SetColumnWidth(1, 30);
                    worksheet.SetColumnWidth(2, 5);

                    MemoryStream stream = new MemoryStream();
                    workbook.SaveAs(stream);
                    stream.Position = 0;

                    return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "TaskProperties.xlsx");
                }
            }
            catch (Exception ex)
            {
                return BadRequest("Failed to generate Excel file: " + ex.Message);
            }
        }

        [HttpPost]
        [HttpPost]
        public IActionResult ExportToExcel([FromBody] List<TaskWithArea> data)
        {
            try
            {
                using (ExcelEngine excelEngine = new ExcelEngine())
                {
                    IApplication application = excelEngine.Excel;
                    application.DefaultVersion = ExcelVersion.Excel2016;
                    IWorkbook workbook;
                    IWorksheet worksheet;

                    var template = _unitOfWork.ExcelTemplate.Get(u => true);
                    if (template != null)
                    {
                        using (MemoryStream ms = new MemoryStream(template.FileContent))
                        {
                            ms.Position = 0;
                            workbook = application.Workbooks.Open(ms);
                            worksheet = workbook.Worksheets[0];

                            // Process page breaks and other formatting as in your original code
                            for (int i = 1; i <= worksheet.UsedRange.LastRow; i++)
                            {
                                var cellText = worksheet.Range[i, 1].Text ?? "";
                                var mergeArea = worksheet.Range[i, 1].MergeArea;
                                var mergeAreaText = mergeArea?.Text ?? "";

                                if (cellText.Contains("Project Preliminaries", StringComparison.OrdinalIgnoreCase) ||
                                    mergeAreaText.Contains("Project Preliminaries", StringComparison.OrdinalIgnoreCase))
                                {
                                    worksheet.HPageBreaks.Add(worksheet.Range[i - 1, 1]);
                                }

                                var cell2Text = worksheet.Range[i, 2].Text ?? "";
                                var mergeArea2 = worksheet.Range[i, 2].MergeArea;
                                var mergeArea2Text = mergeArea2?.Text ?? "";

                                if (cell2Text.Equals("GENERAL SUMMARY", StringComparison.Ordinal) ||
                                    mergeArea2Text.Equals("GENERAL SUMMARY", StringComparison.Ordinal))
                                {
                                    worksheet.HPageBreaks.Add(worksheet.Range[i - 1, 1]);
                                }
                            }
                        }
                    }
                    else
                    {
                        workbook = application.Workbooks.Create(1);
                        worksheet = workbook.Worksheets[0];
                    }

                    int startRow = worksheet.UsedRange.End.Row + 1;
                    worksheet.HPageBreaks.Add(worksheet.Range[startRow, 1]);

                    string[] headers = new[] { "#", "Task", "Quant", "Unit", "Rate", "$ Budget", "%", "$ Completed", "$ Balance" };

                    var firstTask = data.FirstOrDefault();
                    var projectHeaderRange = worksheet.Range[startRow, 1, startRow + 1, headers.Length];
                    projectHeaderRange.Merge();
                    projectHeaderRange.Text = $"Project: {firstTask?.ProjectName ?? "N/A"}\nLocation: {firstTask?.ProjectLocation ?? "N/A"}";
                    projectHeaderRange.CellStyle.Color = Color.FromArgb(219, 229, 241);
                    projectHeaderRange.CellStyle.Font.Bold = true;
                    projectHeaderRange.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
                    projectHeaderRange.CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
                    projectHeaderRange.CellStyle.WrapText = true;

                    int headerRow = startRow + 3;
                    var headerRange = worksheet.Range[headerRow, 1, headerRow, headers.Length];
                    headerRange.CellStyle.Color = Color.FromArgb(219, 229, 241);
                    headerRange.CellStyle.Font.Bold = true;
                    headerRange.CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
                    headerRange.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
                    headerRange.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
                    headerRange.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;

                    for (int i = 0; i < headers.Length; i++)
                    {
                        worksheet.Range[headerRow, i + 1].Text = headers[i];
                    }

                    int rowIndex = headerRow + 1;
                    string currentFloor = "";
                    string currentAction = "";
                    int itemsPerPage = 30;
                    int currentPageItems = 0;
                    char taskLetter = 'A';

                    foreach (var item in data)
                    {
                        if (currentPageItems >= itemsPerPage || (rowIndex - headerRow) % itemsPerPage == 0)
                        {
                            worksheet.HPageBreaks.Add(worksheet.Range[rowIndex, 1]);
                            currentPageItems = 0;
                            taskLetter = 'A';
                        }

                        if (currentFloor != item.FloorName)
                        {
                            var floorRange = worksheet.Range[rowIndex, 1, rowIndex, headers.Length];
                            floorRange.Merge();
                            floorRange.Text = item.FloorName.ToUpper();
                            floorRange.CellStyle.Font.Bold = true;
                            floorRange.CellStyle.Color = Color.White;
                            currentPageItems++;
                            rowIndex++;
                            currentFloor = item.FloorName;
                            currentAction = "";
                        }

                        if (currentAction != item.ActionName)
                        {
                            var actionRange = worksheet.Range[rowIndex, 2];
                            actionRange.Text = item.ActionName;
                            actionRange.CellStyle.Font.Bold = true;
                            actionRange.CellStyle.Color = Color.White;
                            currentPageItems++;
                            rowIndex++;
                            currentAction = item.ActionName;
                        }

                        var rowRange = worksheet.Range[rowIndex, 1, rowIndex, headers.Length];

                        for (int col = 1; col <= headers.Length; col++)
                        {
                            var cell = worksheet.Range[rowIndex, col];
                            cell.CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
                            cell.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
                            cell.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
                            cell.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
                            cell.CellStyle.Color = Color.FromArgb(242, 242, 242);
                        }

                        worksheet.Range[rowIndex, 1].Text = taskLetter.ToString();
                        worksheet.Range[rowIndex, 2].Text = item.TaskName;
                        worksheet.Range[rowIndex, 3].Number = item.PropertyValue ?? 0;
                        worksheet.Range[rowIndex, 4].Text = GetUnit(item.TaskName);
                        worksheet.Range[rowIndex, 5].Number = (item.Rate / item.PropertyValue) ?? 0;
                        worksheet.Range[rowIndex, 6].Formula = $"=IF(C{rowIndex}=0,0,E{rowIndex}*C{rowIndex})";
                        worksheet.Range[rowIndex, 7].Number = item.Completion / 100;
                        worksheet.Range[rowIndex, 8].Formula = $"=F{rowIndex}*G{rowIndex}";
                        worksheet.Range[rowIndex, 9].Formula = $"=F{rowIndex}-H{rowIndex}";

                        worksheet.Range[rowIndex, 3].NumberFormat = "#,##0.00";
                        worksheet.Range[rowIndex, 5].NumberFormat = "#,##0.00";
                        worksheet.Range[rowIndex, 6].NumberFormat = "#,##0.00";
                        worksheet.Range[rowIndex, 7].NumberFormat = "0%";
                        worksheet.Range[rowIndex, 8].NumberFormat = "#,##0.00";
                        worksheet.Range[rowIndex, 9].NumberFormat = "#,##0.00";

                        taskLetter = taskLetter > 'Z' ? 'A' : (char)(taskLetter + 1);
                        currentPageItems++;
                        rowIndex++;
                    }

                    var totalRange = worksheet.Range[rowIndex, 1, rowIndex, headers.Length];
                    totalRange.CellStyle.Color = Color.FromArgb(219, 229, 241);
                    totalRange.CellStyle.Font.Bold = true;

                    for (int col = 1; col <= headers.Length; col++)
                    {
                        var cell = worksheet.Range[rowIndex, col];
                        cell.CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
                        cell.CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
                        cell.CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
                        cell.CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
                    }

                    worksheet.Range[rowIndex, 1, rowIndex, 5].Merge();
                    worksheet.Range[rowIndex, 1].Text = "TOTAL";
                    worksheet.Range[rowIndex, 1].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;

                    worksheet.Range[rowIndex, 6].Formula = $"=SUM(F{headerRow + 1}:F{rowIndex - 1})";
                    worksheet.Range[rowIndex, 8].Formula = $"=SUM(H{headerRow + 1}:H{rowIndex - 1})";
                    worksheet.Range[rowIndex, 9].Formula = $"=SUM(I{headerRow + 1}:I{rowIndex - 1})";

                    worksheet.Range[rowIndex, 6].NumberFormat = "#,##0.00";
                    worksheet.Range[rowIndex, 8].NumberFormat = "#,##0.00";
                    worksheet.Range[rowIndex, 9].NumberFormat = "#,##0.00";

                    worksheet.PageSetup.CenterFooter = "&P of &N";
                    worksheet.PageSetup.PaperSize = ExcelPaperSize.PaperA4;
                    worksheet.PageSetup.Orientation = ExcelPageOrientation.Portrait;
                    worksheet.PageSetup.FitToPagesTall = 0;
                    worksheet.PageSetup.FitToPagesWide = 1;
                    worksheet.PageSetup.LeftMargin = 0.25;
                    worksheet.PageSetup.RightMargin = 0.25;
                    worksheet.PageSetup.TopMargin = 0.25;
                    worksheet.PageSetup.BottomMargin = 0.25;
                    worksheet.PageSetup.HeaderMargin = 0;
                    worksheet.PageSetup.FooterMargin = 0.3;
                    worksheet.PageSetup.IsFitToPage = true;

                    worksheet.SetColumnWidth(1, 17);  // Column A
                    worksheet.SetColumnWidth(2, 30);  // Column B
                    worksheet.SetColumnWidth(4, 8);   // Column D (Units)
                    worksheet.SetColumnWidth(5, 9);   // Column E (Rate)
                    worksheet.SetColumnWidth(8, 15);  // Column H

                    worksheet.Range[$"A1:A{rowIndex}"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
                    worksheet.Range[$"C1:I{rowIndex}"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
                    worksheet.Range[$"B1:B{rowIndex}"].CellStyle.WrapText = true;

                    for (int i = 1; i <= headers.Length; i++)
                    {
                        if (i != 1 && i != 2 && i != 4 && i != 5 && i != 8)
                        {
                            worksheet.AutofitColumn(i);
                        }
                    }

                    worksheet.Range[$"A1:{GetExcelColumnName(headers.Length)}{rowIndex}"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
                    worksheet.PageSetup.PrintArea = $"A1:{GetExcelColumnName(headers.Length)}{rowIndex}";

                    // Populate the bill sections with floor budget totals
                    PopulateFloorBudgetTotals(worksheet, data);

                    MemoryStream stream = new MemoryStream();
                    workbook.SaveAs(stream);
                    stream.Position = 0;

                    return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "BQ_List.xlsx");
                }
            }
            catch (Exception ex)
            {
                return BadRequest("Failed to generate Excel file: " + ex.Message);
            }
        }

        private void PopulateFloorBudgetTotals(IWorksheet worksheet, List<TaskWithArea> data)
        {
            try
            {
                // Calculate budget totals by floor
                var groundFloorTasks = data.Where(t =>
                    t.FloorName != null &&
                    (t.FloorName.ToUpper().Contains("GROUND") || t.FloorName.ToUpper().Contains("G FLOOR"))
                ).ToList();

                var firstFloorTasks = data.Where(t =>
                    t.FloorName != null &&
                    (t.FloorName.ToUpper().Contains("FIRST") || t.FloorName.ToUpper().Contains("1ST FLOOR"))
                ).ToList();

                var secondFloorTasks = data.Where(t =>
                    t.FloorName != null &&
                    (t.FloorName.ToUpper().Contains("SECOND") || t.FloorName.ToUpper().Contains("2ND FLOOR"))
                ).ToList();

                // Calculate totals
                double groundFloorTotal = groundFloorTasks.Sum(t => t.Rate ?? 0);
                double firstFloorTotal = firstFloorTasks.Sum(t => t.Rate ?? 0);
                double secondFloorTotal = secondFloorTasks.Sum(t => t.Rate ?? 0);

                // Calculate completion percentages (weighted by budget)
                double groundFloorCompletionPercentage = CalculateWeightedCompletionPercentage(groundFloorTasks);
                double firstFloorCompletionPercentage = CalculateWeightedCompletionPercentage(firstFloorTasks);
                double secondFloorCompletionPercentage = CalculateWeightedCompletionPercentage(secondFloorTasks);

                // Search for and populate bill rows
                for (int row = 1; row <= worksheet.UsedRange.LastRow; row++)
                {
                    string cellText = worksheet.Range[row, 2].Text ?? "";

                    // BILL No.1 - normally would be for preliminaries, but you asked to populate with first BQ item
                    if (cellText.Contains("BILL No.1"))
                    {
                        //double firstBQItemBudget = data.Count > 0 ? data[0].Rate ?? 0 : 0;
                        //double completionPercentage = data.Count > 0 ? data[0].Completion / 100.0 : 0;

                        //PopulateBillRow(worksheet, row, firstBQItemBudget, completionPercentage);

                        //System.Diagnostics.Debug.WriteLine($"Populated BILL No.1 with {firstBQItemBudget}");
                    }
                    // BILL No.2 - Ground Floor
                    else if (cellText.Contains("BILL No.2"))
                    {
                        PopulateBillRow(worksheet, row, groundFloorTotal, groundFloorCompletionPercentage);

                        System.Diagnostics.Debug.WriteLine($"Populated BILL No.2 with Ground Floor total: {groundFloorTotal}");
                    }
                    // BILL No.3 - First Floor (if available)
                    else if (cellText.Contains("BILL No.3") && firstFloorTasks.Any())
                    {
                        PopulateBillRow(worksheet, row, firstFloorTotal, firstFloorCompletionPercentage);

                        System.Diagnostics.Debug.WriteLine($"Populated BILL No.3 with First Floor total: {firstFloorTotal}");
                    }
                    // BILL No.4 - Second Floor (if available)
                    else if (cellText.Contains("BILL No.4") && secondFloorTasks.Any())
                    {
                        PopulateBillRow(worksheet, row, secondFloorTotal, secondFloorCompletionPercentage);

                        System.Diagnostics.Debug.WriteLine($"Populated BILL No.4 with Second Floor total: {secondFloorTotal}");
                    }
                }
            }
            catch (Exception ex)
            {
                // Log error for debugging
                System.Diagnostics.Debug.WriteLine($"Error populating floor budget totals: {ex.Message}");
            }
        }

        private double CalculateWeightedCompletionPercentage(List<TaskWithArea> tasks)
        {
            if (tasks == null || !tasks.Any())
                return 0;

            double totalBudget = tasks.Sum(t => t.Rate ?? 0);
            if (totalBudget == 0)
                return 0;

            double completedAmount = tasks.Sum(t => (t.Rate ?? 0) * (t.Completion / 100.0));
            return completedAmount / totalBudget;
        }

        private void PopulateBillRow(IWorksheet worksheet, int row, double budget, double completionPercentage)
        {
            // Budget amount in column F
            worksheet.Range[row, 6].Number = budget;
            worksheet.Range[row, 6].NumberFormat = "#,##0.00";

            // Completion percentage in column G
            worksheet.Range[row, 7].Number = completionPercentage;
            worksheet.Range[row, 7].NumberFormat = "0%";

            // Completed amount in column H
            double completedAmount = budget * completionPercentage;
            worksheet.Range[row, 8].Number = completedAmount;
            worksheet.Range[row, 8].NumberFormat = "#,##0.00";

            // Balance amount in column I
            double balanceAmount = budget - completedAmount;
            worksheet.Range[row, 9].Number = balanceAmount;
            worksheet.Range[row, 9].NumberFormat = "#,##0.00";
        }
    }
}