using Google.Protobuf.WellKnownTypes;
using K4os.Compression.LZ4.Internal;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Diagnostics;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore.Storage;
using ODCWeb.DataAccess.Data;
using ODCWeb.DataAccess.Repository.IRepository;
using ODCWeb.Models;
using ODCWeb.Models.ViewModels;
using ODCWeb.Utility;
using Syncfusion.Drawing;
using Syncfusion.EJ2.Linq;
using Syncfusion.XlsIO;
using System.CodeDom.Compiler;
using System.Data;
using System.Dynamic;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace ODCWeb.Areas.Staff.Controllers
{
    [Area("Staff")]


    public class CalculationController : Controller
    {
        /*        private IHttpContextAccessor Accessor;*/
        private readonly ApplicationDbContext _db;
        private readonly IUnitOfWork _unitOfWork;
        /*        private readonly UserManager<ApplicationUser> userManager;*/

        public CalculationController(ApplicationDbContext applicationDbContext, IUnitOfWork unitOfWork/*, IHttpContextAccessor accessor*/)
        {
            _db = applicationDbContext;
            _unitOfWork = unitOfWork;
            /*   Accessor = accessor;*/
            /*            this.userManager = userManager;*/
        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpGet]
        public IActionResult NewProperty(int? id)      //Property
        {
            ViewBag.Id = id;
            Property property = new()
            {

            };
            List<DataProperty> properties = _unitOfWork.DataProperty.GetAll().ToList();
            List<string> property_names = new List<string>();
            foreach (var item in properties)
            {
                property_names.Add(item.Name);

            }
            ViewBag.PropertyNames = property_names;

            return View(property);


        }
        [Authorize(Roles = "Admin,Employee")]
        public IActionResult Checker()
        {
            List<Property> properties = new List<Property>();
            List<Material> materials = new List<Material>();
            List<Property> properties1 = _unitOfWork.Property.GetAll().ToList();
            List<Material> materials1 = _unitOfWork.Material.GetAll().ToList();
            int checker = 0;
            int checker1 = 0;
            foreach (var item in properties1)
            {
                if (checker == 0)
                {
                    properties.Add(item);
                    checker = checker + 1;
                }
                if (item.Name == null || item.Value == null || item.Visible == null || item.PropertyId == null || item.Description == null || item.TaskId == null)
                {
                    properties.Add(item);
                }
            }
            foreach (var item in materials1)
            {
                if (checker1 == 0)
                {
                    materials.Add(item);
                    checker1 = checker1 + 1;
                }
                if (item.ODCProjectId == null || item.ActionId == null || item.FloorId == null || item.MaterialId == null || item.Name == null || item.TaskId == null)
                {
                    materials.Add(item);
                }
            }
            dynamic mymodel = new ExpandoObject();
            mymodel.properties = properties;
            mymodel.materials = materials;
            /*if (mymodel.properties == null && mymodel.materials == null)
            {
                return View("Error");
            }
            else
            {
*/
            return View(mymodel);
            /*}*/
        }
        [Authorize(Roles = "Admin,Employee")]
        [HttpPost]
        public IActionResult NewProperty(Property obj)  //*Combining Post controller for create and edit
        {
            if (ModelState.IsValid)
            {

                obj.Value = 0;
                _unitOfWork.Property.Add(obj);
                _unitOfWork.save();
                TempData["success"] = "Property created successfully";
                return RedirectToAction("TaskDetails", "ODCProject", new { id = obj.TaskId });

            }
            else
            {
                TempData["error"] = "Something wrong";
                return View(obj);
            }
        }
        ///////
        ///
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public IActionResult EditProperty(int? id)      //Property
        {

            Property property = _unitOfWork.Property.Get(u => u.PropertyId == id);
            ViewBag.TaskId = property.TaskId;
            return View(property);


        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpPost]
        public IActionResult EditProperty(Property obj)  //*Combining Post controller for create and edit
        {
            if (ModelState.IsValid)
            {


                _unitOfWork.Property.Update(obj);
                _unitOfWork.save();
                TempData["success"] = "Property updated successfully";
                return RedirectToAction("TaskDetails", "ODCProject", new { id = obj.TaskId });

            }
            else
            {
                TempData["error"] = "Something wrong";
                return View(obj);
            }
        }
        [Authorize(Roles = "Admin,Employee")]
        public IActionResult DeleteProperty(int? id)
        {
            Property obj = _unitOfWork.Property.Get(u => u.PropertyId == id);
            _unitOfWork.Property.Remove(obj);
            _unitOfWork.save();
            TempData["success"] = "Property deleted successfully";
            return RedirectToAction("TaskDetails", "ODCProject", new { id = obj.TaskId });
        }
        [HttpGet]
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult EnterData(int? id)
        {

            IEnumerable<Property> properties_temp = _unitOfWork.Property.Where(u => u.TaskId == id);
            List<Property> properties = properties_temp.Where(u => u.Visible == true).ToList();
            ActionTask task = _unitOfWork.Task.Get(u => u.TaskId == id);
            FloorAction action = _unitOfWork.Action.Get(u => u.ActionId == task.ActionId);
            Floor floor = _unitOfWork.Floor.Get(u => u.FloorId == action.FloorId);
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == floor.ODCProjectId);
            /*dynamic mymodel = new ExpandoObject();
            mymodel.properties = properties;
            mymodel.task = task;
            mymodel.action = action;
            mymodel.floor = floor;
            mymodel.project = project;*/
            ViewBag.task = task;
            ViewBag.project = project;
            ViewBag.floor = floor;
            ViewBag.action = action;
            if (properties.Count > 0)
            {
                return View(properties);
            }
            else
            {
                TempData["error"] = "Nothing to enter";
                return View("Error");
            }

        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpPost]
        public IActionResult EnterData(List<Property> objs)
        {
            int idd = 0;
            int temp = 0;
            ActionTask task = new ActionTask();
            if (ModelState.IsValid)
            {
                foreach (var item in objs)
                {
                    idd = item.TaskId;
                    task = _unitOfWork.Task.Get(u => u.TaskId == idd);
                    _unitOfWork.Property.Update(item);
                    _unitOfWork.save();
                    TempData["success"] = "Properties updated successfully";
                }
                if (task != null)
                {
                    FloorAction action = _unitOfWork.Action.Get(u => u.ActionId == task.ActionId);
                    if (action != null)
                    {
                        Floor floor = _unitOfWork.Floor.Get(u => u.FloorId == action.FloorId);
                        if (floor != null)
                        {
                            temp = floor.ODCProjectId;
                        }
                    }

                }
                if (temp != null || temp != 0)
                {
                    return RedirectToAction("CalculateFormulaP", "Calculation", new { id = temp });
                }
                else
                {
                    TempData["error"] = "Values updated but Formula calculation pending";
                    return RedirectToAction("TaskDetails", "ODCProject", new { id = idd });
                }
            }
            else
            {
                TempData["error"] = "Something Wrong";
                return View(objs);
            }
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public IActionResult EnterCompleteData(int? id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            List<ActionTask> tasks = new List<ActionTask>();
            List<Property> properties = new List<Property>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();
            ViewBag.project = project;

            // Get all actions for each floor
            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }

            // Create a dictionary to group actions by their base name
            Dictionary<string, List<FloorAction>> actionGroups = new Dictionary<string, List<FloorAction>>();

            // Group actions that seem to be duplicates (based on name pattern)
            foreach (var action in actions)
            {
                // Extract base name (without the numbering)
                string baseName = GetBaseName(action.Name);

                if (!actionGroups.ContainsKey(baseName))
                {
                    actionGroups[baseName] = new List<FloorAction>();
                }

                actionGroups[baseName].Add(action);
            }

            // Clear and rebuild the actions list in the desired order
            actions.Clear();

            // For each floor, maintain the group order
            foreach (var floor in floors)
            {
                HashSet<int> processedActionIds = new HashSet<int>();
                List<FloorAction> floorActions = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();

                // First, add all base actions in their original order
                foreach (var action in floorActions)
                {
                    if (processedActionIds.Contains(action.ActionId))
                        continue;

                    string baseName = GetBaseName(action.Name);

                    // Get all actions in this group for this floor
                    var groupActions = actionGroups[baseName]
                        .Where(a => a.FloorId == floor.FloorId)
                        .OrderBy(a => a.Name)
                        .ToList();

                    // Add the entire group
                    actions.AddRange(groupActions);

                    // Mark these as processed
                    foreach (var groupAction in groupActions)
                    {
                        processedActionIds.Add(groupAction.ActionId);
                    }
                }
            }

            // Continue with the rest of your code...
            foreach (var action in actions)
            {
                List<ActionTask> tasks_part = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                tasks.AddRange(tasks_part);
            }

            foreach (var task in tasks)
            {
                List<Property> properties_part = _unitOfWork.Property.Where(u => u.TaskId == task.TaskId).ToList();
                properties.AddRange(properties_part.Where(u => u.Visible == true).ToList());
            }

            List<PropertyWithTask_Action_Floor> new_prop = new List<PropertyWithTask_Action_Floor>();
            foreach (var property in properties)
            {
                ActionTask t = _unitOfWork.Task.Get(u => u.TaskId == property.TaskId);
                FloorAction a = _unitOfWork.Action.Get(u => u.ActionId == t.ActionId);
                Floor f = _unitOfWork.Floor.Get(u => u.FloorId == a.FloorId);
                PropertyWithTask_Action_Floor prop = new()
                {
                    property = property,
                    TaskName = t.EffectiveDisplayName,
                    TaskId = property.TaskId,
                    Reference_Id = t.Reference_Id,
                    SDate = t.SDate,
                    Duration = t.Duration,
                    ActionName = a.EffectiveDisplayNameA,
                    ActionId = a.ActionId,
                    FloorName = f.Name,
                    FloorId = f.FloorId
                };
                new_prop.Add(prop);
            }
            ViewBag.Prop = new_prop;
            return View(properties);
        }

        // Helper method to extract base name from an action name
        private string GetBaseName(string name)
        {
            // Check if the name ends with a number preceded by space
            string pattern = @"^(.*?)(?:\s+\d+)?$";
            Match match = Regex.Match(name, pattern);

            if (match.Success && match.Groups.Count > 1)
            {
                return match.Groups[1].Value.Trim();
            }

            return name;
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpPost]
        public IActionResult EnterCompleteData(List<Property> objs)
        {
            int? temp = 10;
            if (ModelState.IsValid)
            {
                foreach (var item in objs)
                {
                    _unitOfWork.Property.Update(item);
                    _unitOfWork.save();
                }
                TempData["success"] = "PPPPProperties updated successfullyyyyyy";

                // Check if the request is AJAX (auto-save)
                if (Request.Headers["X-Requested-With"] == "XMLHttpRequest")
                {
                    return Json(new { success = true, message = "Auto-saved successfully" });
                }

                // Regular form submission logic continues below
                int temppp = objs.First().TaskId;
                int temp2 = _unitOfWork.Task.Get(u => u.TaskId == temppp).ActionId;
                int temp3 = _unitOfWork.Action.Get(u => u.ActionId == temp2).FloorId;
                int temp4 = _unitOfWork.Floor.Get(u => u.FloorId == temp3).ODCProjectId;
                DataTemp check = _unitOfWork.DataTemp.Get(u => u.DataTempId == 1);
                if (check != null)
                {
                    int check2 = check.Value;
                    if (check2 == 1)
                    {
                        check.Value = 0;
                        _unitOfWork.DataTemp.update(check);
                        _unitOfWork.save();
                        return RedirectToAction("CalculateFormulaP", "Calculation", new { id = temp4 });
                    }
                    else
                    {
                        return RedirectToAction("CalculateFormulaP", "Calculation", new { id = temp4 });
                    }
                }
                else
                {
                    return RedirectToAction("CalculateFormulaP", "Calculation", new { id = temp4 });
                }
            }
            else
            {
                // Handle validation errors
                TempData["error"] = "Something Wrong";

                // If AJAX request, return JSON with error details
                if (Request.Headers["X-Requested-With"] == "XMLHttpRequest")
                {
                    return Json(new { success = false, message = "Validation failed" });
                }

                return View(objs);
            }
        }
        ////////
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public IActionResult EnterCompleteMData(int? id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            List<ActionTask> tasks = new List<ActionTask>();
            List<Material> materials = new List<Material>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();
            ViewBag.project = project;

            // Get all actions for each floor
            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }

            // Create a dictionary to group actions by their base name
            Dictionary<string, List<FloorAction>> actionGroups = new Dictionary<string, List<FloorAction>>();

            // Group actions that seem to be duplicates (based on name pattern)
            foreach (var action in actions)
            {
                // Extract base name (without the numbering)
                string baseName = GetBaseName(action.Name);

                if (!actionGroups.ContainsKey(baseName))
                {
                    actionGroups[baseName] = new List<FloorAction>();
                }

                actionGroups[baseName].Add(action);
            }

            // Clear and rebuild the actions list in the desired order
            actions.Clear();

            // For each floor, maintain the group order
            foreach (var floor in floors)
            {
                HashSet<int> processedActionIds = new HashSet<int>();
                List<FloorAction> floorActions = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();

                // First, add all base actions in their original order
                foreach (var action in floorActions)
                {
                    if (processedActionIds.Contains(action.ActionId))
                        continue;

                    string baseName = GetBaseName(action.Name);

                    // Get all actions in this group for this floor
                    var groupActions = actionGroups[baseName]
                        .Where(a => a.FloorId == floor.FloorId)
                        .OrderBy(a => a.Name)
                        .ToList();

                    // Add the entire group
                    actions.AddRange(groupActions);

                    // Mark these as processed
                    foreach (var groupAction in groupActions)
                    {
                        processedActionIds.Add(groupAction.ActionId);
                    }
                }
            }

            // Continue with the rest of your code to process tasks and materials
            foreach (var action in actions)
            {
                List<ActionTask> tasks_part = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                tasks.AddRange(tasks_part);
            }

            foreach (var task in tasks)
            {
                List<Material> material_parts = _unitOfWork.Material.Where(u => u.TaskId == task.TaskId).ToList();
                materials.AddRange(material_parts);
            }

            List<MaterialWithTask_Action_Floor> new_mat = new List<MaterialWithTask_Action_Floor>();
            foreach (var material in materials)
            {
                ActionTask t = _unitOfWork.Task.Get(u => u.TaskId == material.TaskId);
                FloorAction a = _unitOfWork.Action.Get(u => u.ActionId == t.ActionId);
                Floor f = _unitOfWork.Floor.Get(u => u.FloorId == a.FloorId);
                MaterialWithTask_Action_Floor mat = new()
                {
                    material = material,
                    TaskName = t.EffectiveDisplayName,
                    TaskId = t.TaskId,
                    ActionName = a.EffectiveDisplayNameA,
                    ActionId = a.ActionId,
                    FloorName = f.Name,
                    FloorId = f.FloorId
                };
                new_mat.Add(mat);
            }
            ViewBag.Mat = new_mat;
            return View(materials);
        }

        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpPost]
        public IActionResult EnterCompleteMData(List<Material> objs)
        {
            int? temp = 0;
            if (ModelState.IsValid)
            {
                foreach (var item in objs)
                {
                    temp = item.ODCProjectId;
                    _unitOfWork.Material.update(item);
                    _unitOfWork.save();
                }

                TempData["success"] = "Materials updated successfully";

                // Only process cost updates and redirection for non-AJAX requests
                if (Request.Headers["X-Requested-With"] != "XMLHttpRequest")
                {
                    ///////////////////////updating costs
                    List<FloorAction> actions = new List<FloorAction>();
                    List<ActionTask> tasks = new List<ActionTask>();
                    IEnumerable<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == temp);
                    if (floors != null)
                    {
                        foreach (var itemf in floors)
                        {
                            IEnumerable<FloorAction> actionss = _unitOfWork.Action.Where(u => u.FloorId == itemf.FloorId);
                            actions.AddRange(actionss);
                        }
                        if (actions != null)
                        {
                            foreach (var itema in actions)
                            {
                                IEnumerable<ActionTask> taskss = _unitOfWork.Task.Where(u => u.ActionId == itema.ActionId);
                                tasks.AddRange(taskss);
                            }
                            if (tasks != null)
                            {
                                foreach (var itemt in tasks)
                                {
                                    double? mat_cost = 0;
                                    IEnumerable<Material> materials = _unitOfWork.Material.Where(u => u.TaskId == itemt.TaskId);
                                    if (materials != null)
                                    {
                                        foreach (var itemm in materials)
                                        {
                                            mat_cost = mat_cost + (itemm.Qty * itemm.Unit_Cost);
                                        }
                                        itemt.Material_Cost = mat_cost;
                                        itemt.Mat_Lab = (itemt.Measured_Work * itemt.Labor_Rate) + itemt.Material_Cost;
                                        _unitOfWork.Task.update(itemt);
                                        _unitOfWork.save();
                                    }
                                }
                            }
                        }
                    }
                    ///////////////////////
                    return RedirectToAction("EnterCompleteMData", "Calculation", new { id = temp });
                }
                else
                {
                    // For AJAX requests, return JSON response
                    return Json(new { success = true, message = "Materials auto-saved successfully" });
                }
            }
            else
            {
                TempData["error"] = "Something Wrong";

                // If AJAX request, return JSON with error details
                if (Request.Headers["X-Requested-With"] == "XMLHttpRequest")
                {
                    return Json(new { success = false, message = "Validation failed" });
                }

                return View(objs);
            }
        }
        [HttpGet]
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult EnterMData(int? id)
        {

            List<Material> materials_temp = _unitOfWork.Material.Where(u => u.TaskId == id).ToList();
            List<Material> materials = materials_temp;
            ActionTask task = _unitOfWork.Task.Get(u => u.TaskId == id);
            FloorAction action = _unitOfWork.Action.Get(u => u.ActionId == task.ActionId);
            Floor floor = _unitOfWork.Floor.Get(u => u.FloorId == action.FloorId);
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == floor.ODCProjectId);
            /*dynamic mymodel = new ExpandoObject();
            mymodel.properties = properties;
            mymodel.task = task;
            mymodel.action = action;
            mymodel.floor = floor;
            mymodel.project = project;*/
            ViewBag.task = task;
            ViewBag.project = project;
            ViewBag.floor = floor;
            ViewBag.action = action;
            if (materials.Count > 0)
            {
                return View(materials);
            }
            else
            {
                TempData["error"] = "Nothing to enter";
                return View("Error");
            }

        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpPost]
        public IActionResult EnterMData(List<Material> objs)
        {
            /*try
            {*/
            int? idd = 0;
            if (ModelState.IsValid)
            {
                foreach (var item in objs)
                {
                    idd = item.TaskId;
                    _unitOfWork.Material.update(item);
                    _unitOfWork.save();
                    TempData["success"] = "Materials updated successfully";
                }
                return RedirectToAction("TaskDetails", "ODCProject", new { id = idd });
            }
            else
            {
                TempData["error"] = "Something Wrong";
                return View(objs);
            }
            /*}
            catch (Exception e)
            {
                TempData["error"] = "Something Wrong";
                return View("Error");
            }*/

        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult CalculationArea(int? id)
        {
            Property property = _unitOfWork.Property.Get(u => u.PropertyId == id);
            List<Property> properties = _unitOfWork.Property.Where(u => u.TaskId == property.TaskId).ToList();
            double? length = 0;
            double? width = 0;
            foreach (var item in properties)
            {
                if (item.Name.ToUpper() == "LENGTH (FT)")
                {
                    length = item.Value;
                }
                if (item.Name.ToUpper() == "WIDTH (FT)")
                {
                    width = item.Value;
                }
            }
            property.Value = length * width;
            _unitOfWork.Property.Update(property);
            _unitOfWork.save();

            return RedirectToAction("TaskDetails", "ODCProject", new { id = property.TaskId });
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult CalculationVolume(int? id)
        {
            Property property = _unitOfWork.Property.Get(u => u.PropertyId == id);
            List<Property> properties = _unitOfWork.Property.Where(u => u.TaskId == property.TaskId).ToList();
            double? length = 0;
            double? width = 0;
            double? height = 0;
            foreach (var item in properties)
            {
                if (item.Name.ToUpper() == "LENGTH (FT)")
                {
                    length = item.Value;
                }
                if (item.Name.ToUpper() == "WIDTH (FT)")
                {
                    width = item.Value;
                }
                if (item.Name.ToUpper() == "HEIGHT (FT)")
                {
                    height = item.Value;
                }
                else if (item.Name.ToUpper() == "DEPTH (FT)")
                {
                    height = item.Value;
                }
            }
            property.Value = length * width * height;
            _unitOfWork.Property.Update(property);
            _unitOfWork.save();

            return RedirectToAction("TaskDetails", "ODCProject", new { id = property.TaskId });
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult CalculationVolumeYard(int? id)
        {
            Property property = _unitOfWork.Property.Get(u => u.PropertyId == id);
            List<Property> properties = _unitOfWork.Property.Where(u => u.TaskId == property.TaskId).ToList();
            double? length = 0;
            double? width = 0;
            double? height = 0;
            foreach (var item in properties)
            {
                if (item.Name.ToUpper() == "LENGTH (FT)")
                {
                    length = item.Value;
                }
                if (item.Name.ToUpper() == "WIDTH (FT)")
                {
                    width = item.Value;
                }
                if (item.Name.ToUpper() == "HEIGHT (FT)")
                {
                    height = item.Value;
                }
                else if (item.Name.ToUpper() == "DEPTH (FT)")
                {
                    height = item.Value;
                }
            }
            property.Value = (length * width * height) / 27;
            _unitOfWork.Property.Update(property);
            _unitOfWork.save();

            return RedirectToAction("TaskDetails", "ODCProject", new { id = property.TaskId });
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult CalculationAreaYard(int? id)
        {
            Property property = _unitOfWork.Property.Get(u => u.PropertyId == id);
            List<Property> properties = _unitOfWork.Property.Where(u => u.TaskId == property.TaskId).ToList();
            double? length = 0;
            double? width = 0;
            foreach (var item in properties)
            {
                if (item.Name.ToUpper() == "LENGTH (FT)")
                {
                    length = item.Value;
                }
                if (item.Name.ToUpper() == "WIDTH (FT)")
                {
                    width = item.Value;
                }
            }
            property.Value = (length * width) / 9;
            _unitOfWork.Property.Update(property);
            _unitOfWork.save();

            return RedirectToAction("TaskDetails", "ODCProject", new { id = property.TaskId });
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult CalculateFormula(int id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            List<ActionTask> tasks = new List<ActionTask>();
            List<Material> materials = new List<Material>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();
            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }
            foreach (var action in actions)
            {
                List<ActionTask> tasks_part = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                tasks.AddRange(tasks_part);
            }
            foreach (var task in tasks)
            {
                List<Material> material_parts = _unitOfWork.Material.Where(u => u.TaskId == task.TaskId).ToList();
                materials.AddRange(material_parts);
            }
            ///////////////////////////////////////////////////
            if (materials != null)
            {
                foreach (var material in materials)
                {
                    ActionTask task = _unitOfWork.Task.Get(u => u.TaskId == material.TaskId);
                    string last_c = task.Name[task.Name.Length - 1].ToString();
                    string taskName = "";
                    if (int.TryParse(last_c, out _) == true)
                    {
                        taskName = task.Name.Substring(0, task.Name.Length - 2);
                    }
                    else
                    {
                        taskName = task.Name;
                    }


                    DataTask dataTask = _unitOfWork.DataTask.Get(u => u.Name == taskName);
                    IEnumerable<TemplateMaterial> all_templateMaterials = _unitOfWork.TemplateMaterial.Where(u => u.DataTaskId == dataTask.DataTaskId);
                    TemplateMaterial templateMaterial = all_templateMaterials.FirstOrDefault(u => u.Name == material.Name);
                    if (templateMaterial != null)
                    {
                        TemplateFormula formuladb = _unitOfWork.TemplateFormula.Get(u => u.TemplateMId == templateMaterial.TemplateMId);
                        if (formuladb != null && formuladb.Formula != "" && formuladb.Formula != null)
                        {

                            string dbFormula = formuladb.Formula;
                            List<TemplateProperty> template_properties = _unitOfWork.TemplateProperty.Where(u => u.DataTaskId == dataTask.DataTaskId).ToList();
                            List<string> propertyIds = new List<string>();
                            foreach (var tproperty in template_properties)
                            {
                                propertyIds.Add(tproperty.TemplatePId.ToString());
                            }
                            string temp_formula = dbFormula;
                            int checker = 0;
                            if (temp_formula != null && temp_formula != "")
                            {

                                for (int b = 0; b < 1; b++)
                                {
                                    int difference = 0;
                                    foreach (var tid in propertyIds)
                                    {
                                        if (temp_formula.Contains(tid))
                                        {

                                            int splace = temp_formula.IndexOf(tid) - difference;
                                            int elength = dbFormula.Length - splace;
                                            if (splace > -1 && elength > 0)
                                            {
                                                string name = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId.ToString() == tid).Name;
                                                List<Property> properties = _unitOfWork.Property.Where(u => u.TaskId == material.TaskId).ToList();
                                                string value = "0";
                                                if (properties != null && name != null)
                                                {
                                                    Property tempi = properties.Find(u => u.Name == name);
                                                    if (tempi != null)
                                                    {
                                                        value = tempi.Value.ToString();
                                                    }
                                                    else
                                                    {
                                                        value = "0";
                                                    }
                                                }
                                                else
                                                {
                                                    value = "0";
                                                }
                                                if (value == "" || value == null)
                                                {
                                                    value = "0";
                                                }
                                                string to_be_replaced = dbFormula.Substring(splace, elength);
                                                dbFormula = dbFormula.Substring(0, splace) + to_be_replaced.Replace(tid, value);
                                                difference = difference + tid.Length - value.Length;
                                            }

                                        }
                                    }
                                }
                            }
                            string tempp = "(2-2)";
                            DataTable dt = new DataTable();
                            var v = dt.Compute(tempp, "");
                            try
                            {

                                v = dt.Compute(dbFormula, "");
                            }
                            catch (Exception e)
                            {

                            }



                            if (v != null && v.ToString() != "NaN")
                            {
                                double temp = Convert.ToDouble(v);
                                if (Double.IsInfinity(temp) == false)
                                {
                                    material.Qty = Math.Round(temp, 2);

                                    _unitOfWork.Material.update(material);
                                    _unitOfWork.save();
                                }
                            }


                        }
                    }
                    else
                    {
                        //do nothing
                    }

                }
            }

            /////////////update mat_lab costs
            foreach (var itm in tasks)
            {
                double? temp_mat_cost = 0;
                IEnumerable<Material> mats = _unitOfWork.Material.Where(u => u.TaskId == itm.TaskId);
                if (mats != null)
                {
                    foreach (var mat in mats)
                    {
                        temp_mat_cost = temp_mat_cost + (mat.Qty * mat.Unit_Cost);
                    }
                }
                itm.Material_Cost = temp_mat_cost;
                itm.Mat_Lab = itm.Labor_Cost + itm.Material_Cost;
                _unitOfWork.Task.update(itm);
                _unitOfWork.save();
            }
            ///

            return RedirectToAction("EnterCompleteData", "Calculation", new { id });
        }


        [Authorize(Roles = "Admin,Employee,Customer")]
        public IActionResult CalculateFormulaP(int id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            List<ActionTask> taskss = new List<ActionTask>();
            List<Property> properties = new List<Property>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();

            // Gather all floors, actions, tasks, and properties
            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }
            foreach (var action in actions)
            {
                List<ActionTask> tasks_part = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                taskss.AddRange(tasks_part);
            }
            foreach (var taskk in taskss)
            {
                List<Property> property_parts = _unitOfWork.Property.Where(u => u.TaskId == taskk.TaskId).ToList();
                properties.AddRange(property_parts);
            }

            // Process each property
            foreach (var property in properties)
            {
                ActionTask task = _unitOfWork.Task.Get(u => u.TaskId == property.TaskId);
                string last_c = task.Name[task.Name.Length - 1].ToString();
                string taskName = "";
                if (int.TryParse(last_c, out _) == true)
                {
                    taskName = task.Name.Substring(0, task.Name.Length - 2);
                }
                else
                {
                    taskName = task.Name;
                }

                DataTask dataTask = _unitOfWork.DataTask.Get(u => u.Name == taskName);
                if (dataTask == null) continue;

                IEnumerable<TemplateProperty> all_TemplatePropertys = _unitOfWork.TemplateProperty.Where(u => u.DataTaskId == dataTask.DataTaskId);
                TemplateProperty TemplateProperty = all_TemplatePropertys.FirstOrDefault(u => u.Name == property.Name);

                if (TemplateProperty != null)
                {
                    TemplateFormulaP formuladb = _unitOfWork.TemplateFormulaP.Get(u => u.TemplatePId == TemplateProperty.TemplatePId);
                    if (formuladb != null && !string.IsNullOrEmpty(formuladb.Formula))
                    {
                        string dbFormula = formuladb.Formula;
                        string parsedFormula = dbFormula;

                        // FIRST: Process cross-task references (I format)
                        if (dbFormula.Contains('I'))
                        {
                            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 property reference
                                string propRef = parsedFormula.Substring(firstI + 1, secondI - firstI - 1);
                                string replacementValue = "0"; // Default if not found

                                // Check if it's a cross-task reference
                                if (propRef.Contains(':'))
                                {
                                    string[] parts = propRef.Split(':');
                                    if (parts.Length == 2 &&
                                        int.TryParse(parts[0], out int propId) &&
                                        int.TryParse(parts[1], out int referencedDataTaskId))
                                    {
                                        // Get the current task to know which action we're in
                                        ActionTask currentTask = _unitOfWork.Task.Get(u => u.TaskId == property.TaskId);

                                        // Get the referenced template property
                                        TemplateProperty templateProperty = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId == propId);

                                        // Get the referenced DataTask (template)
                                        DataTask referencedDataTask = _unitOfWork.DataTask.Get(u => u.DataTaskId == referencedDataTaskId);

                                        if (templateProperty != null && referencedDataTask != null && currentTask != null)
                                        {
                                            // CRITICAL FIX: Find the task with matching DataTask name IN THE SAME ACTION
                                            // This ensures duplicated actions reference their own tasks, not the original action's tasks
                                            ActionTask referencedTask = _unitOfWork.Task
                                                .Get(u => u.Name == referencedDataTask.Name && u.ActionId == currentTask.ActionId);

                                            if (referencedTask != null)
                                            {
                                                // Get property from the referenced task
                                                Property refProperty = _unitOfWork.Property
                                                    .Get(u => u.TaskId == referencedTask.TaskId && u.Name == templateProperty.Name);

                                                if (refProperty != null)
                                                {
                                                    replacementValue = refProperty.Value.ToString();
                                                }
                                            }
                                        }
                                    }
                                }
                                else if (int.TryParse(propRef, out int propId))
                                {
                                    // Handle old style reference (current task's property)
                                    TemplateProperty templateProperty = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId == propId);
                                    if (templateProperty != null)
                                    {
                                        List<Property> propertiess = _unitOfWork.Property.Where(u => u.TaskId == property.TaskId).ToList();
                                        Property refProperty = propertiess.Find(u => u.Name == templateProperty.Name);

                                        if (refProperty != null)
                                        {
                                            replacementValue = refProperty.Value.ToString();
                                        }
                                    }
                                }

                                // Replace the reference with the value
                                parsedFormula = parsedFormula.Replace($"I{propRef}I", replacementValue);

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

                        // SECOND: Process template property IDs directly (legacy format)
                        // Get all template properties for this data task
                        List<TemplateProperty> template_properties = _unitOfWork.TemplateProperty.Where(u => u.DataTaskId == dataTask.DataTaskId).ToList();
                        List<string> propertyIds = new List<string>();
                        foreach (var tproperty in template_properties)
                        {
                            propertyIds.Add(tproperty.TemplatePId.ToString());
                        }

                        // Sort property IDs by length in descending order to replace longer IDs first
                        // This prevents partial matches (e.g., replacing "1" in "12")
                        propertyIds.Sort((a, b) => b.Length.CompareTo(a.Length));

                        // Process each template property ID
                        if (!string.IsNullOrEmpty(parsedFormula))
                        {
                            foreach (var tid in propertyIds)
                            {
                                if (parsedFormula.Contains(tid))
                                {
                                    // Find all occurrences of this ID in the formula
                                    int position = 0;
                                    while ((position = parsedFormula.IndexOf(tid, position)) != -1)
                                    {
                                        // Check that this is a standalone ID (not part of I...I format or larger number)
                                        bool isStandaloneID = true;

                                        // Check character before (if not at start)
                                        if (position > 0)
                                        {
                                            char before = parsedFormula[position - 1];
                                            if (before == 'I' || char.IsDigit(before))
                                                isStandaloneID = false;
                                        }

                                        // Check character after (if not at end)
                                        if (position + tid.Length < parsedFormula.Length)
                                        {
                                            char after = parsedFormula[position + tid.Length];
                                            if (after == 'I' || char.IsDigit(after))
                                                isStandaloneID = false;
                                        }

                                        if (isStandaloneID)
                                        {
                                            string name = _unitOfWork.TemplateProperty.Get(u => u.TemplatePId.ToString() == tid).Name;
                                            List<Property> propertiess = _unitOfWork.Property.Where(u => u.TaskId == property.TaskId).ToList();
                                            Property t = propertiess.Find(u => u.Name == name);
                                            string value = t != null ? t.Value.ToString() : "0";

                                            // Replace this occurrence
                                            parsedFormula = parsedFormula.Substring(0, position) + value + parsedFormula.Substring(position + tid.Length);

                                            // Adjust position for next search based on replacement length
                                            position += value.Length;
                                        }
                                        else
                                        {
                                            // Skip this occurrence and move to next position
                                            position += tid.Length;
                                        }
                                    }
                                }
                            }
                        }

                        // Evaluate the final parsed formula
                        try
                        {
                            DataTable dt = new DataTable();
                            var v = dt.Compute(parsedFormula, "");

                            if (v != null && v.ToString() != "NaN")
                            {
                                double temp = Convert.ToDouble(v);
                                if (!Double.IsInfinity(temp))
                                {
                                    property.Value = Math.Round(temp, 2);
                                    _unitOfWork.Property.Update(property);
                                    _unitOfWork.save();
                                }
                            }
                        }
                        catch (Exception e)
                        {
                            System.Diagnostics.Debug.WriteLine($"Error evaluating formula for property {property.Name} in task {task.Name}: {e.Message}");
                        }
                    }
                }
            }

            // Update Measured Works for all tasks
            foreach (var task in taskss)
            {
                string last_c_MW = task.Name[task.Name.Length - 1].ToString();
                string taskName_MW = "";
                if (!string.IsNullOrEmpty(last_c_MW))
                {
                    if (int.TryParse(last_c_MW, out _) == true)
                    {
                        taskName_MW = task.Name.Substring(0, task.Name.Length - 2);
                    }
                    else
                    {
                        taskName_MW = task.Name;
                    }

                    DataTask dataTask_MW = _unitOfWork.DataTask.Get(u => u.Name == taskName_MW);
                    if (dataTask_MW != null)
                    {
                        IEnumerable<TemplateProperty> all_TemplatePropertys_MW = _unitOfWork.TemplateProperty.Where(u => u.DataTaskId == dataTask_MW.DataTaskId);
                        List<string> propertyIds_MW = new List<string>();
                        string temp_formula_MW = dataTask_MW.MeasuredWorkPId;
                        if (all_TemplatePropertys_MW != null)
                        {
                            foreach (var tproperty in all_TemplatePropertys_MW)
                            {
                                propertyIds_MW.Add(tproperty.TemplatePId.ToString());
                            }
                            if (propertyIds_MW != null)
                            {
                                foreach (var itemp in propertyIds_MW)
                                {
                                    if (temp_formula_MW.Contains(itemp))
                                    {
                                        TemplateProperty t_property_MW = all_TemplatePropertys_MW.FirstOrDefault(u => u.TemplatePId.ToString() == itemp);
                                        if (t_property_MW != null)
                                        {
                                            IEnumerable<Property> properties_MW = _unitOfWork.Property.Where(u => u.TaskId == task.TaskId);
                                            if (properties_MW != null)
                                            {
                                                Property f_property_MW = properties_MW.FirstOrDefault(u => u.Name == t_property_MW.Name);
                                                if (f_property_MW != null)
                                                {
                                                    task.Measured_Work = f_property_MW.Value;
                                                    task.Labor_Cost = task.Measured_Work * task.Labor_Rate;
                                                    task.Mat_Lab = task.Labor_Cost + task.Material_Cost;
                                                    _unitOfWork.Task.update(task);
                                                    _unitOfWork.save();
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }

            return RedirectToAction("CalculateFormula", "Calculation", new { id });
        }
        [Authorize(Roles = "Admin")]
        [HttpGet]
        public IActionResult UpdateLaborRates(int? id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            List<ActionTask> tasks = new List<ActionTask>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();
            ViewBag.project = project;
            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }
            foreach (var action in actions)
            {
                List<ActionTask> tasks_part = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                tasks.AddRange(tasks_part);
            }

            List<TaskWith_Action_Floor> new_task = new List<TaskWith_Action_Floor>();
            foreach (var task in tasks)
            {
                FloorAction a = _unitOfWork.Action.Get(u => u.ActionId == task.ActionId);
                Floor f = _unitOfWork.Floor.Get(u => u.FloorId == a.FloorId);
                TaskWith_Action_Floor tas = new()
                {
                    task = task,
                    ActionName = a.Name,
                    ActionId = a.ActionId,
                    FloorName = f.Name,
                    FloorId = f.FloorId
                };
                new_task.Add(tas);
            }
            ViewBag.Prop = new_task;
            return View(tasks);
        }
        [Authorize(Roles = "Admin")]
        [HttpPost]
        public IActionResult UpdateLaborRates(List<ActionTask> objs)
        {
            if (ModelState.IsValid)
            {
                foreach (var item in objs)
                {
                    item.Labor_Cost = item.Labor_Rate * item.Measured_Work;
                    item.Mat_Lab = item.Labor_Cost + item.Material_Cost;
                    _unitOfWork.Task.update(item);
                    _unitOfWork.save();
                    TempData["success"] = "Labor Rates updated successfully";
                }/*
                int temp2 = _unitOfWork.Task.Get(u => u.TaskId == objs.First().TaskId).ActionId;
                int temp3 = _unitOfWork.Action.Get(u => u.ActionId == temp2).FloorId;
                int temp4 = _unitOfWork.Floor.Get(u => u.FloorId == temp3).ODCProjectId;*/
                return RedirectToAction("Index", "ODCProject");
                /*                    return RedirectToAction("EnterCompleteData", "Calculation", new { id = temp4 });*/




            }
            else
            {
                /*// Print ModelState errors to console
                foreach (var modelStateEntry in ModelState.Values)
                {
                    foreach (var error in modelStateEntry.Errors)
                    {
                        Console.WriteLine($"ModelState Error: {error.ErrorMessage}");
                    }
                }*/
                TempData["error"] = "Something Wrong";
                return View(objs);
            }
        }
        ////////
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public IActionResult UpdateCompletionPercentage(int? id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            List<ActionTask> tasks = new List<ActionTask>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();
            ViewBag.project = project;
            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action.Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }
            foreach (var action in actions)
            {
                List<ActionTask> tasks_part = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                tasks.AddRange(tasks_part);
            }

            List<TaskWith_Action_Floor> new_task = new List<TaskWith_Action_Floor>();
            foreach (var task in tasks)
            {
                FloorAction a = _unitOfWork.Action.Get(u => u.ActionId == task.ActionId);
                Floor f = _unitOfWork.Floor.Get(u => u.FloorId == a.FloorId);
                TaskWith_Action_Floor tas = new()
                {
                    task = task,
                    ActionName = a.Name,
                    ActionId = a.ActionId,
                    FloorName = f.Name,
                    FloorId = f.FloorId
                };
                new_task.Add(tas);
            }
            ViewBag.Prop = new_task;
            return View(tasks);
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpPost]
        public IActionResult UpdateCompletionPercentage(List<ActionTask> objs)
        {
            if (ModelState.IsValid)
            {
                foreach (var item in objs)
                {
                    /*                    item.Labor_Cost = item.Labor_Rate * item.Measured_Work;
                                        item.Mat_Lab = item.Labor_Cost + item.Material_Cost;*/
                    _unitOfWork.Task.update(item);
                    _unitOfWork.save();
                    TempData["success"] = "Completion % updated successfully";
                }/*
                int temp2 = _unitOfWork.Task.Get(u => u.TaskId == objs.First().TaskId).ActionId;
                int temp3 = _unitOfWork.Action.Get(u => u.ActionId == temp2).FloorId;
                int temp4 = _unitOfWork.Floor.Get(u => u.FloorId == temp3).ODCProjectId;*/
                return RedirectToAction("Index", "ODCProject");
                /*                    return RedirectToAction("EnterCompleteData", "Calculation", new { id = temp4 });*/




            }
            else
            {
                /*// Print ModelState errors to console
                foreach (var modelStateEntry in ModelState.Values)
                {
                    foreach (var error in modelStateEntry.Errors)
                    {
                        Console.WriteLine($"ModelState Error: {error.ErrorMessage}");
                    }
                }*/
                TempData["error"] = "Something Wrong";
                return View(objs);
            }
        }
        ////////
        // Controller method
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public IActionResult ProjectBudget(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var viewModel = GetProjectBudgetViewModel(id.Value);
            return View(viewModel);
        }
        [Authorize(Roles = "Admin,Employee,Customer")]
        [HttpGet]
        public IActionResult ExportToExcel(int id)
        {
            var viewModel = GetProjectBudgetViewModel(id);

            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                IApplication application = excelEngine.Excel;
                application.DefaultVersion = ExcelVersion.Excel2016;
                IWorkbook workbook = application.Workbooks.Create(1);
                IWorksheet worksheet = workbook.Worksheets[0];

                // Set title
                worksheet.Range["A1"].Text = $"Project Budget Overview - {viewModel.Project.Name}";
                worksheet.Range["A1:K1"].Merge();
                worksheet.Range["A1"].CellStyle.Font.Bold = true;
                worksheet.Range["A1"].CellStyle.Font.Size = 14;
                worksheet.Range["A1"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;

                // Project Summary section
                worksheet.Range["A3"].Text = "Project Summary";
                worksheet.Range["A3"].CellStyle.Font.Bold = true;

                // Headers for summary
                int currentRow = 4;
                if (User.IsInRole(SD.Role_Admin))
                {
                    worksheet.Range["A4:C4"].Text = "Calculated Costs";
                    worksheet.Range["D4:F4"].Text = "Projected Costs";
                    worksheet.Range["G4:J4"].Text = "Actual Budget Summary";

                    // Style the header sections using Syncfusion.Drawing.Color
                    worksheet.Range["A4:C4"].CellStyle.Color = Color.FromArgb(173, 216, 230); // LightBlue
                    worksheet.Range["D4:F4"].CellStyle.Color = Color.FromArgb(144, 238, 144); // LightGreen
                    worksheet.Range["G4:J4"].CellStyle.Color = Color.FromArgb(255, 255, 224); // LightYellow

                    string[] summaryHeaders = new string[] {
                        "Labor Cost", "Material Cost", "Total Budget",
                        "Projected Labor", "Projected Material", "Projected Total",
                        "Actual Labor", "Actual Material", "Budget Left", "Profit/Loss"
                    };
                    for (int i = 0; i < summaryHeaders.Length; i++)
                    {
                        worksheet.Range[currentRow + 1, i + 1].Text = summaryHeaders[i];
                        worksheet.Range[currentRow + 1, i + 1].CellStyle.Font.Bold = true;
                    }
                    currentRow++;

                    // Summary data rows remain the same...
                }

                currentRow += 3;

                // Tasks Breakdown section
                worksheet.Range[$"A{currentRow}"].Text = "Tasks Breakdown";
                worksheet.Range[$"A{currentRow}"].CellStyle.Font.Bold = true;
                currentRow++;

                // Task headers
                if (User.IsInRole(SD.Role_Admin))
                {
                    string[] taskHeaders = new string[] {
                        "Task Name", "Labor Cost", "Material Cost", "Calculated Budget",
                        "Projected Labor", "Projected Material", "Projected Budget",
                        "Actual Labor", "Actual Material", "Budget Left", "Profit/Loss"
                    };
                    for (int i = 0; i < taskHeaders.Length; i++)
                    {
                        worksheet.Range[currentRow, i + 1].Text = taskHeaders[i];
                        worksheet.Range[currentRow, i + 1].CellStyle.Font.Bold = true;
                        worksheet.Range[currentRow, i + 1].CellStyle.Color = Color.FromArgb(128, 128, 128); // Gray
                    }
                }
                currentRow++;

                // Write tasks data grouped by action
                foreach (var actionGroup in viewModel.ActionGroups)
                {
                    // Action header
                    worksheet.Range[$"A{currentRow}"].Text = actionGroup.Action.Name;
                    worksheet.Range[$"A{currentRow}"].CellStyle.Font.Bold = true;
                    var headerRange = worksheet.Range[$"A{currentRow}:K{currentRow}"];
                    headerRange.CellStyle.Color = Color.FromArgb(211, 211, 211); // LightGray
                    headerRange.Merge();
                    headerRange.CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
                    currentRow++;

                    foreach (var task in actionGroup.Tasks)
                    {
                        worksheet.Range[$"A{currentRow}"].Text = task.Name;
                        worksheet.Range[$"B{currentRow}"].Number = task.Labor_Cost ?? 0;
                        worksheet.Range[$"C{currentRow}"].Number = task.Material_Cost ?? 0;
                        worksheet.Range[$"D{currentRow}"].Number = task.Mat_Lab ?? 0;
                        worksheet.Range[$"E{currentRow}"].Number = task.M_Labor_Cost ?? 0;
                        worksheet.Range[$"F{currentRow}"].Number = task.M_Mat_Cost ?? 0;
                        worksheet.Range[$"G{currentRow}"].Number = task.M_Budget ?? 0;
                        worksheet.Range[$"H{currentRow}"].Number = task.A_Labor_Cost ?? 0;
                        worksheet.Range[$"I{currentRow}"].Number = task.A_Mat_Cost ?? 0;
                        worksheet.Range[$"J{currentRow}"].Number = task.A_Budget_Left ?? 0;
                        worksheet.Range[$"K{currentRow}"].Number = task.T_Profit_Loss ?? 0;

                        if (task.T_Profit_Loss < 0)
                        {
                            worksheet.Range[$"K{currentRow}"].CellStyle.Color = Color.FromArgb(255, 182, 193); // LightPink
                        }

                        currentRow++;
                    }
                }

                // Format and save remain the same...
                worksheet.UsedRange.AutofitColumns();

                using (var stream = new MemoryStream())
                {
                    workbook.SaveAs(stream);
                    stream.Position = 0;
                    string fileName = $"ProjectBudget_{viewModel.Project.Name}_{DateTime.Now:yyyyMMdd}.xlsx";
                    return File(
                        stream.ToArray(),
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                        fileName
                    );
                }
            }
        }

        private ProjectBudgetViewModel GetProjectBudgetViewModel(int id)
        {
            List<FloorAction> actions = new List<FloorAction>();
            ODCProject project = _unitOfWork.ODCProject.Get(u => u.Id == id);
            List<Floor> floors = _unitOfWork.Floor.Where(u => u.ODCProjectId == project.Id).ToList();

            foreach (var floor in floors)
            {
                List<FloorAction> actions_part = _unitOfWork.Action
                    .Where(u => u.FloorId == floor.FloorId).ToList();
                actions.AddRange(actions_part);
            }

            var actionGroups = new List<ActionTaskGroupViewModel>();
            foreach (var action in actions)
            {
                var tasks = _unitOfWork.Task.Where(u => u.ActionId == action.ActionId).ToList();
                actionGroups.Add(new ActionTaskGroupViewModel
                {
                    Action = action,
                    Tasks = tasks
                });
            }

            var allTasks = actionGroups.SelectMany(g => g.Tasks).ToList();
            var projectSummary = new ProjectSummaryViewModel
            {
                TotalLaborCost = allTasks.Sum(t => t.Labor_Cost ?? 0),
                TotalMaterialCost = allTasks.Sum(t => t.Material_Cost ?? 0),
                TotalCalculatedBudget = allTasks.Sum(t => t.Mat_Lab ?? 0),
                TotalProjectedLaborCost = allTasks.Sum(t => t.M_Labor_Cost ?? 0),
                TotalProjectedMaterialCost = allTasks.Sum(t => t.M_Mat_Cost ?? 0),
                TotalProjectedBudget = allTasks.Sum(t => t.M_Budget ?? 0),
                TotalActualLaborCost = allTasks.Sum(t => t.A_Labor_Cost ?? 0),
                TotalActualMaterialCost = allTasks.Sum(t => t.A_Mat_Cost ?? 0),
                TotalBudgetLeft = allTasks.Sum(t => t.A_Budget_Left ?? 0),
                TotalProfitLoss = allTasks.Sum(t => t.T_Profit_Loss ?? 0)
            };

            return new ProjectBudgetViewModel
            {
                Project = project,
                ProjectSummary = projectSummary,
                ActionGroups = actionGroups
            };
        }

    }
}