using System;
using System.Collections.Generic;
using System.Linq;
using pavesys_iRAP.DTO;
using ClosedXML.Excel;
using pavesys_iRAP.Helper;
using System.IO;
using System.Diagnostics;
using Serilog;
using System.Threading.Tasks;
using System.Text;
using System.Globalization;
using Mapsui.Projections;

namespace pavesys_iRAP.Business
{
    internal static class XlsxHandler
    {
        public static void InputXlsx(int projectId, string filename)
        {
            try
            {
                using (var workbook = new XLWorkbook(filename))
                {

                    var ws = workbook.Worksheets.First(); // assuming there will be only one worksheet.
                    var codings = CrudHelper.FindCodingsByProject(projectId, false);

                    var maxRow = Math.Min(ws.LastRowUsed().RowNumber(), codings.Count);

                    int rowNbr = 2; // the first row should be only the categories so we start by the secnd
                    foreach (var coding in codings)
                    {
                        if (rowNbr > maxRow)
                            break;

                        coding.CoderName = ws.Cell(rowNbr, 1).GetValue<string>();
                        coding.CodingDate = ws.Cell(rowNbr, 2).GetValue<DateTime>();
                        coding.RoadSurveyDate = ws.Cell(rowNbr, 3).GetValue<DateTime>();
                        coding.RoadName = ws.Cell(rowNbr, 5).GetValue<string>();
                        coding.Section = ws.Cell(rowNbr, 6).GetValue<string>();
                        coding.Distance = ws.Cell(rowNbr, 7).GetValue<Decimal>();
                        coding.Length = ws.Cell(rowNbr, 8).GetValue<Decimal>();

                        coding.Latitude = ws.Cell(rowNbr, 9).GetValue<Decimal>();
                        coding.Longitude = ws.Cell(rowNbr, 10).GetValue<Decimal>();

                        coding.Landmark = ws.Cell(rowNbr, 11).GetValue<string>();
                        coding.Comments = ws.Cell(rowNbr, 12).GetValue<string>();
                        coding.CarriageWay = ws.Cell(rowNbr, 13).GetValue<int>();
                        coding.UpgradeCost = ws.Cell(rowNbr, 14).GetValue<int>();
                        coding.MotorcycleObserveredFlow = ws.Cell(rowNbr, 15).GetValue<int>();
                        coding.BicycleObservedFlow = ws.Cell(rowNbr, 16).GetValue<int>();
                        coding.PedestrianObservedFlow = ws.Cell(rowNbr, 17).GetValue<int>();
                        coding.PedestrianObservedAlongRoadDriver = ws.Cell(rowNbr, 18).GetValue<int>();
                        coding.PedestrianObservedAlongRoadPassenger = ws.Cell(rowNbr, 19).GetValue<int>();
                        coding.LandUseDrivers = ws.Cell(rowNbr, 20).GetValue<int>();
                        coding.LandUsePassenger = ws.Cell(rowNbr, 21).GetValue<int>();
                        coding.AreaType = ws.Cell(rowNbr, 22).GetValue<int>();
                        coding.SpeedLimit = ws.Cell(rowNbr, 23).GetValue<int>();
                        coding.MotorcycleSpeedLimit = ws.Cell(rowNbr, 24).GetValue<int>();
                        coding.TruckSpeedLimit = ws.Cell(rowNbr, 25).GetValue<int>();
                        coding.DifferentialSpeeds = ws.Cell(rowNbr, 26).GetValue<int>();
                        coding.MedianType = ws.Cell(rowNbr, 27).GetValue<int>();
                        coding.CentrelineRumbleStrips = ws.Cell(rowNbr, 28).GetValue<int>();
                        coding.RoadsideSeverityDriversSideDistance = ws.Cell(rowNbr, 29).GetValue<int>();
                        coding.RoadsideSeverityDriversSideObject = ws.Cell(rowNbr, 30).GetValue<int>();
                        coding.RoadsideSeverityPassengerSideDistance = ws.Cell(rowNbr, 31).GetValue<int>();
                        coding.RoadsideSeverityPassengerSideObject = ws.Cell(rowNbr, 32).GetValue<int>();
                        coding.ShoulderRumbleStrips = ws.Cell(rowNbr, 33).GetValue<int>();
                        coding.PavedShoulderDriversSide = ws.Cell(rowNbr, 34).GetValue<int>();
                        coding.PavedShoulderPassengerSide = ws.Cell(rowNbr, 35).GetValue<int>();
                        coding.IntersectionType = ws.Cell(rowNbr, 36).GetValue<int>();
                        coding.IntersectionChannelisation = ws.Cell(rowNbr, 37).GetValue<int>();
                        coding.IntersectingRoadVolume = ws.Cell(rowNbr, 38).GetValue<int>();
                        coding.IntersectionQuality = ws.Cell(rowNbr, 39).GetValue<int>();
                        coding.PropertyAccessPoints = ws.Cell(rowNbr, 40).GetValue<int>();
                        coding.NumberLanes = ws.Cell(rowNbr, 41).GetValue<int>();
                        coding.LaneWidth = ws.Cell(rowNbr, 42).GetValue<int>();
                        coding.Curvature = ws.Cell(rowNbr, 43).GetValue<int>();
                        coding.QualityCurve = ws.Cell(rowNbr, 44).GetValue<int>();
                        coding.Grade = ws.Cell(rowNbr, 45).GetValue<int>();
                        coding.RoadCondition = ws.Cell(rowNbr, 46).GetValue<int>();
                        coding.SkidResistanceGrip = ws.Cell(rowNbr, 47).GetValue<int>();
                        coding.Delineation = ws.Cell(rowNbr, 48).GetValue<int>();
                        coding.StreetLighting = ws.Cell(rowNbr, 49).GetValue<int>();
                        coding.PedestrianCrossingInspectedRoad = ws.Cell(rowNbr, 50).GetValue<int>();
                        coding.PedestrianCrossingQuality = ws.Cell(rowNbr, 51).GetValue<int>();
                        coding.PedestrianCrossingFacilitiesSideRoad = ws.Cell(rowNbr, 52).GetValue<int>();
                        coding.PedestrianFencing = ws.Cell(rowNbr, 53).GetValue<int>();
                        coding.SpeedManagementTrafficCalming = ws.Cell(rowNbr, 54).GetValue<int>();
                        coding.VehicleParking = ws.Cell(rowNbr, 55).GetValue<int>();
                        coding.SidewalkDriversSide = ws.Cell(rowNbr, 56).GetValue<int>();
                        coding.SidewalkPassengerSide = ws.Cell(rowNbr, 57).GetValue<int>();
                        coding.ServiceRoad = ws.Cell(rowNbr, 58).GetValue<int>();
                        coding.FacilitiesMotorisedTwoWheelers = ws.Cell(rowNbr, 59).GetValue<int>();
                        coding.BicycleFacility = ws.Cell(rowNbr, 60).GetValue<int>();
                        coding.Roadworks = ws.Cell(rowNbr, 61).GetValue<int>();
                        coding.SightDistance = ws.Cell(rowNbr, 62).GetValue<int>();
                        if (!ws.Cell(rowNbr, 63).IsEmpty())
                            coding.VehicleFlow = ws.Cell(rowNbr, 63).GetValue<int>();
                        if (!ws.Cell(rowNbr, 64).IsEmpty())
                            coding.MotorcyclePercentual = ws.Cell(rowNbr, 64).GetValue<int>();
                        if (!ws.Cell(rowNbr, 65).IsEmpty())
                            coding.PedestrianPeakHourFlowAcrossRoad = ws.Cell(rowNbr, 65).GetValue<int>();
                        if (!ws.Cell(rowNbr, 66).IsEmpty())
                            coding.PedestrianPeakHourFlowAlongRoadDriver = ws.Cell(rowNbr, 66).GetValue<int>();
                        if (!ws.Cell(rowNbr, 67).IsEmpty())
                            coding.PedestrianPeakHourFlowAlongRoadPassenger = ws.Cell(rowNbr, 67).GetValue<int>();
                        if (!ws.Cell(rowNbr, 68).IsEmpty())
                            coding.BicyclePeakHourlyFlow = ws.Cell(rowNbr, 68).GetValue<int>();
                        if (!ws.Cell(rowNbr, 69).IsEmpty())
                            coding.OperatingSpeed85thPercentile = ws.Cell(rowNbr, 69).GetValue<int>();
                        if (!ws.Cell(rowNbr, 70).IsEmpty())
                            coding.OperatingSpeedMean = ws.Cell(rowNbr, 70).GetValue<int>();
                        if (!ws.Cell(rowNbr, 71).IsEmpty())
                            coding.RoadsCarsRead = ws.Cell(rowNbr, 71).GetValue<int>();
                        if (!ws.Cell(rowNbr, 72).IsEmpty())
                            coding.CarStarRatingPolicyTarget = ws.Cell(rowNbr, 72).GetValue<int>();
                        if (!ws.Cell(rowNbr, 73).IsEmpty())
                            coding.MotorcycleStarRatingPolicyTarget = ws.Cell(rowNbr, 73).GetValue<int>();
                        if (!ws.Cell(rowNbr, 74).IsEmpty())
                            coding.PedestrianStarRatingPolicyTarget = ws.Cell(rowNbr, 74).GetValue<int>();
                        if (!ws.Cell(rowNbr, 75).IsEmpty())
                            coding.BicycleStarRatingPolicyTarget = ws.Cell(rowNbr, 75).GetValue<int>();
                        coding.AnnualFatalityGrowthMultiplier = ws.Cell(rowNbr, 76).GetValue<int>();
                        coding.SchoolZoneWarning = ws.Cell(rowNbr, 77).GetValue<int>();
                        coding.SchoolZoneCrossingSupervisor = ws.Cell(rowNbr, 78).GetValue<int>();

                        rowNbr++;
                    }
                }
                CrudHelper.dbCtx.SaveChanges();
                Log.Debug("Successfully imported codings");
            }
            catch (Exception ex)
            {
                Log.Error(ex, "Failed to open xlsx file for input");
            }

        }
        public static void InputNewProjectCsv(string filepath) 
        {
            try
            {
                var lines = File.ReadAllLines(filepath);
                List<Coding> codings = new();

                for (int i = 1; i < lines.Count(); i++) // start at 1 to skip the header row
                {
                    Coding coding = new Coding();
                    var line = lines[i];
                    var fields = line.Split(';');


                    coding.CoderName = fields[0];
                    coding.CodingDate = DateTime.ParseExact(fields[1], "M/d/yyyy H:mm", CultureInfo.InvariantCulture);
                    coding.RoadSurveyDate = DateTime.ParseExact(fields[2], "M/d/yyyy H:mm", CultureInfo.InvariantCulture);
                    //Logica para as fotos aqui codingimage e tal
                    var imageNames = fields[3].Split(",");
                    foreach (var imageName in imageNames) 
                    {
                        
                    }

                    coding.RoadName = fields[4];
                    coding.Section = fields[5];
                    coding.Distance = Decimal.Parse(fields[6]);
                    coding.Length = Decimal.Parse(fields[7]);
                    coding.Latitude = Decimal.Parse(fields[8]);
                    coding.Longitude = Decimal.Parse(fields[9]);
                    coding.Landmark = fields[10];
                    coding.Comments = fields[11];
                    coding.CarriageWay = int.Parse(fields[12]);
                    coding.UpgradeCost = int.Parse(fields[13]);
                    coding.MotorcycleObserveredFlow = int.Parse(fields[14]);
                    coding.BicycleObservedFlow = int.Parse(fields[15]);
                    coding.PedestrianObservedFlow = int.Parse(fields[16]);
                    coding.PedestrianObservedAlongRoadDriver = int.Parse(fields[17]);
                    coding.PedestrianObservedAlongRoadPassenger = int.Parse(fields[18]);
                    coding.LandUseDrivers = int.Parse(fields[19]);
                    coding.LandUsePassenger = int.Parse(fields[20]);
                    coding.AreaType = int.Parse(fields[21]);
                    coding.SpeedLimit = int.Parse(fields[22]);
                    coding.MotorcycleSpeedLimit = int.Parse(fields[23]);
                    coding.TruckSpeedLimit = int.Parse(fields[24]);
                    coding.DifferentialSpeeds = int.Parse(fields[25]);
                    coding.MedianType = int.Parse(fields[26]);
                    coding.CentrelineRumbleStrips = int.Parse(fields[27]);
                    coding.RoadsideSeverityDriversSideDistance = int.Parse(fields[28]);
                    coding.RoadsideSeverityDriversSideObject = int.Parse(fields[29]);
                    coding.RoadsideSeverityPassengerSideDistance = int.Parse(fields[30]);
                    coding.RoadsideSeverityPassengerSideObject = int.Parse(fields[31]);
                    coding.ShoulderRumbleStrips = int.Parse(fields[32]);
                    coding.PavedShoulderDriversSide = int.Parse(fields[33]);
                    coding.PavedShoulderPassengerSide = int.Parse(fields[34]);
                    coding.IntersectionType = int.Parse(fields[35]);
                    coding.IntersectionChannelisation = int.Parse(fields[36]);
                    coding.IntersectingRoadVolume = int.Parse(fields[37]);
                    coding.IntersectionQuality = int.Parse(fields[38]);
                    coding.PropertyAccessPoints = int.Parse(fields[39]);
                    coding.NumberLanes = int.Parse(fields[40]);
                    coding.LaneWidth = int.Parse(fields[41]);
                    coding.Curvature = int.Parse(fields[42]);
                    coding.QualityCurve = int.Parse(fields[43]);
                    coding.Grade = int.Parse(fields[44]);
                    coding.RoadCondition = int.Parse(fields[45]);
                    coding.SkidResistanceGrip = int.Parse(fields[46]);
                    coding.Delineation = int.Parse(fields[47]);
                    coding.StreetLighting = int.Parse(fields[48]);
                    coding.PedestrianCrossingInspectedRoad = int.Parse(fields[49]);
                    coding.PedestrianCrossingQuality = int.Parse(fields[50]);
                    coding.PedestrianCrossingFacilitiesSideRoad = int.Parse(fields[51]);
                    coding.PedestrianFencing = int.Parse(fields[52]);
                    coding.SpeedManagementTrafficCalming = int.Parse(fields[53]);
                    coding.VehicleParking = int.Parse(fields[54]);
                    coding.SidewalkDriversSide = int.Parse(fields[55]);
                    coding.SidewalkPassengerSide = int.Parse(fields[56]);
                    coding.ServiceRoad = int.Parse(fields[57]);
                    coding.FacilitiesMotorisedTwoWheelers = int.Parse(fields[58]);
                    coding.BicycleFacility = int.Parse(fields[59]);
                    coding.Roadworks = int.Parse(fields[60]);
                    coding.SightDistance = int.Parse(fields[61]);
                    if (!string.IsNullOrWhiteSpace(fields[62]))
                        coding.VehicleFlow = int.Parse(fields[62]);
                    if (!string.IsNullOrWhiteSpace(fields[63]))
                        coding.MotorcyclePercentual = int.Parse(fields[63]);
                    if (!string.IsNullOrWhiteSpace(fields[64]))
                        coding.PedestrianPeakHourFlowAcrossRoad = int.Parse(fields[64]);
                    if (!string.IsNullOrWhiteSpace(fields[65]))
                        coding.PedestrianPeakHourFlowAlongRoadDriver = int.Parse(fields[65]);
                    if (!string.IsNullOrWhiteSpace(fields[66]))
                        coding.PedestrianPeakHourFlowAlongRoadPassenger = int.Parse(fields[66]);
                    if (!string.IsNullOrWhiteSpace(fields[67]))
                        coding.BicyclePeakHourlyFlow = int.Parse(fields[67]);
                    if (!string.IsNullOrWhiteSpace(fields[68]))
                        coding.OperatingSpeed85thPercentile = int.Parse(fields[68]);
                    if (!string.IsNullOrWhiteSpace(fields[69]))
                        coding.OperatingSpeedMean = int.Parse(fields[69]);
                    if (!string.IsNullOrWhiteSpace(fields[70]))
                        coding.RoadsCarsRead = int.Parse(fields[70]);
                    if (!string.IsNullOrWhiteSpace(fields[71]))
                        coding.CarStarRatingPolicyTarget = int.Parse(fields[71]);
                    if (!string.IsNullOrWhiteSpace(fields[72]))
                        coding.MotorcycleStarRatingPolicyTarget = int.Parse(fields[72]);
                    if (!string.IsNullOrWhiteSpace(fields[73]))
                        coding.PedestrianStarRatingPolicyTarget = int.Parse(fields[73]);
                    if (!string.IsNullOrWhiteSpace(fields[74]))
                        coding.BicycleStarRatingPolicyTarget = int.Parse(fields[74]);
                    coding.AnnualFatalityGrowthMultiplier = int.Parse(fields[75]);
                    coding.SchoolZoneWarning = int.Parse(fields[76]);
                    coding.SchoolZoneCrossingSupervisor = int.Parse(fields[77]);
                    codings.Add(coding);
                }
                CrudHelper.dbCtx.AddRange(codings);
                CrudHelper.dbCtx.SaveChanges();
                Log.Debug("Successfully imported codings");
            }
            catch (Exception ex)
            {
                Log.Error(ex, "Failed to open CSV file for input");
            }
        }

        //Inputs csv após criado um projeto. Aba de Edição de projetos.
        public static void InputCsv(int projectId, string filename)
        {
            try
            {
                var lines = File.ReadAllLines(filename);
                var codings = CrudHelper.FindCodingsByProject(projectId, false);
                var maxRow = Math.Min(lines.Length - 1, codings.Count); // subtracting 1 for the header row

                for (int i = 1; i <= maxRow; i++) // start at 1 to skip the header row
                {
                    var line = lines[i];
                    var fields = line.Split(';');

                    var coding = codings[i - 1]; // i - 1 because codings list is 0-based




                    coding.CoderName = fields[0];
                    coding.CodingDate = DateTime.ParseExact(fields[1], "M/d/yyyy H:mm", CultureInfo.InvariantCulture);
                    coding.RoadSurveyDate = DateTime.ParseExact(fields[2], "M/d/yyyy H:mm", CultureInfo.InvariantCulture);
                    coding.RoadName = fields[4];
                    coding.Section = fields[5];
                    coding.Distance = Decimal.Parse(fields[6]);
                    coding.Length = Decimal.Parse(fields[7]);
                    coding.Latitude = Decimal.Parse(fields[8]);
                    coding.Longitude = Decimal.Parse(fields[9]);
                    coding.Landmark = fields[10];
                    coding.Comments = fields[11];
                    coding.CarriageWay = int.Parse(fields[12]);
                    coding.UpgradeCost = int.Parse(fields[13]);
                    coding.MotorcycleObserveredFlow = int.Parse(fields[14]);
                    coding.BicycleObservedFlow = int.Parse(fields[15]);
                    coding.PedestrianObservedFlow = int.Parse(fields[16]);
                    coding.PedestrianObservedAlongRoadDriver = int.Parse(fields[17]);
                    coding.PedestrianObservedAlongRoadPassenger = int.Parse(fields[18]);
                    coding.LandUseDrivers = int.Parse(fields[19]);
                    coding.LandUsePassenger = int.Parse(fields[20]);
                    coding.AreaType = int.Parse(fields[21]);
                    coding.SpeedLimit = int.Parse(fields[22]);
                    coding.MotorcycleSpeedLimit = int.Parse(fields[23]);
                    coding.TruckSpeedLimit = int.Parse(fields[24]);
                    coding.DifferentialSpeeds = int.Parse(fields[25]);
                    coding.MedianType = int.Parse(fields[26]);
                    coding.CentrelineRumbleStrips = int.Parse(fields[27]);
                    coding.RoadsideSeverityDriversSideDistance = int.Parse(fields[28]);
                    coding.RoadsideSeverityDriversSideObject = int.Parse(fields[29]);
                    coding.RoadsideSeverityPassengerSideDistance = int.Parse(fields[30]);
                    coding.RoadsideSeverityPassengerSideObject = int.Parse(fields[31]);
                    coding.ShoulderRumbleStrips = int.Parse(fields[32]);
                    coding.PavedShoulderDriversSide = int.Parse(fields[33]);
                    coding.PavedShoulderPassengerSide = int.Parse(fields[34]);
                    coding.IntersectionType = int.Parse(fields[35]);
                    coding.IntersectionChannelisation = int.Parse(fields[36]);
                    coding.IntersectingRoadVolume = int.Parse(fields[37]);
                    coding.IntersectionQuality = int.Parse(fields[38]);
                    coding.PropertyAccessPoints = int.Parse(fields[39]);
                    coding.NumberLanes = int.Parse(fields[40]);
                    coding.LaneWidth = int.Parse(fields[41]);
                    coding.Curvature = int.Parse(fields[42]);
                    coding.QualityCurve = int.Parse(fields[43]);
                    coding.Grade = int.Parse(fields[44]);
                    coding.RoadCondition = int.Parse(fields[45]);
                    coding.SkidResistanceGrip = int.Parse(fields[46]);
                    coding.Delineation = int.Parse(fields[47]);
                    coding.StreetLighting = int.Parse(fields[48]);
                    coding.PedestrianCrossingInspectedRoad = int.Parse(fields[49]);
                    coding.PedestrianCrossingQuality = int.Parse(fields[50]);
                    coding.PedestrianCrossingFacilitiesSideRoad = int.Parse(fields[51]);
                    coding.PedestrianFencing = int.Parse(fields[52]);
                    coding.SpeedManagementTrafficCalming = int.Parse(fields[53]);
                    coding.VehicleParking = int.Parse(fields[54]);
                    coding.SidewalkDriversSide = int.Parse(fields[55]);
                    coding.SidewalkPassengerSide = int.Parse(fields[56]);
                    coding.ServiceRoad = int.Parse(fields[57]);
                    coding.FacilitiesMotorisedTwoWheelers = int.Parse(fields[58]);
                    coding.BicycleFacility = int.Parse(fields[59]);
                    coding.Roadworks = int.Parse(fields[60]);
                    coding.SightDistance = int.Parse(fields[61]);
                    if (!string.IsNullOrWhiteSpace(fields[62]))
                        coding.VehicleFlow = int.Parse(fields[62]);
                    if (!string.IsNullOrWhiteSpace(fields[63]))
                        coding.MotorcyclePercentual = int.Parse(fields[63]);
                    if (!string.IsNullOrWhiteSpace(fields[64]))
                        coding.PedestrianPeakHourFlowAcrossRoad = int.Parse(fields[64]);
                    if (!string.IsNullOrWhiteSpace(fields[65]))
                        coding.PedestrianPeakHourFlowAlongRoadDriver = int.Parse(fields[65]);
                    if (!string.IsNullOrWhiteSpace(fields[66]))
                        coding.PedestrianPeakHourFlowAlongRoadPassenger = int.Parse(fields[66]);
                    if (!string.IsNullOrWhiteSpace(fields[67]))
                        coding.BicyclePeakHourlyFlow = int.Parse(fields[67]);
                    if (!string.IsNullOrWhiteSpace(fields[68]))
                        coding.OperatingSpeed85thPercentile = int.Parse(fields[68]);
                    if (!string.IsNullOrWhiteSpace(fields[69]))
                        coding.OperatingSpeedMean = int.Parse(fields[69]);
                    if (!string.IsNullOrWhiteSpace(fields[70]))
                        coding.RoadsCarsRead = int.Parse(fields[70]);
                    if (!string.IsNullOrWhiteSpace(fields[71]))
                        coding.CarStarRatingPolicyTarget = int.Parse(fields[71]);
                    if (!string.IsNullOrWhiteSpace(fields[72]))
                        coding.MotorcycleStarRatingPolicyTarget = int.Parse(fields[72]);
                    if (!string.IsNullOrWhiteSpace(fields[73]))
                        coding.PedestrianStarRatingPolicyTarget = int.Parse(fields[73]);
                    if (!string.IsNullOrWhiteSpace(fields[74]))
                        coding.BicycleStarRatingPolicyTarget = int.Parse(fields[74]);
                    coding.AnnualFatalityGrowthMultiplier = int.Parse(fields[75]);
                    coding.SchoolZoneWarning = int.Parse(fields[76]);
                    coding.SchoolZoneCrossingSupervisor = int.Parse(fields[77]);
                }
                CrudHelper.dbCtx.SaveChanges();
                Log.Debug("Successfully imported codings");
            }
            catch (Exception ex)
            {
                Log.Error(ex, "Failed to open CSV file for input");
            }
        }
        public static async Task<String> ExecuteOutput(int idProjeto, int exportType)
        {

            var proj = CrudHelper.GetProjectById(idProjeto);
            return await Task.Run(async () =>
            {
                var workbook = new XLWorkbook();
                IXLWorksheet ws = workbook.Worksheets.Add(proj.Road);

                ws.Cell(1, 1).Value = "Coder name";
                ws.Cell(1, 2).Value = "Coding date";
                ws.Cell(1, 3).Value = "Road survey date";
                ws.Cell(1, 4).Value = "Image reference";
                ws.Cell(1, 5).Value = "Road Name";
                ws.Cell(1, 6).Value = "Section";
                ws.Cell(1, 7).Value = "Distance";
                ws.Cell(1, 8).Value = "Length";

                ws.Cell(1, 9).Value = "Latitude";
                ws.Cell(1, 10).Value = "Longitude";

                ws.Cell(1, 11).Value = "Landmark";
                ws.Cell(1, 12).Value = "Comments";
                ws.Cell(1, 13).Value = "Carriageway";
                ws.Cell(1, 14).Value = "Upgrade cost";
                ws.Cell(1, 15).Value = "Motorcycle obsevered flow";
                ws.Cell(1, 16).Value = "Bicycle observed flow";
                ws.Cell(1, 17).Value = "Pedestrian obsevered flow across the road";
                ws.Cell(1, 18).Value = "Pedestrian obsevered flow along the road driver-side";
                ws.Cell(1, 19).Value = "Pedestrian obsevered flow along the road passenger side";
                ws.Cell(1, 20).Value = "Land use - drivers side";
                ws.Cell(1, 21).Value = "Land use - passenger side";
                ws.Cell(1, 22).Value = "Area type";
                ws.Cell(1, 23).Value = "Speed limit";
                ws.Cell(1, 24).Value = "Motorcycle speed limit";
                ws.Cell(1, 25).Value = "Truck speed limit";
                ws.Cell(1, 26).Value = "Differential speeds";
                ws.Cell(1, 27).Value = "Median type";
                ws.Cell(1, 28).Value = "Centreline rumble strips";
                ws.Cell(1, 29).Value = "Roadside severity - drivers side distance";
                ws.Cell(1, 30).Value = "Roadside severity - drivers side object";
                ws.Cell(1, 31).Value = "Roadside severity - passenger side distance";
                ws.Cell(1, 32).Value = "Roadside severity - passenger side object";
                ws.Cell(1, 33).Value = "Shoulder rumble strips";
                ws.Cell(1, 34).Value = "Paved shoulder - drivers side";
                ws.Cell(1, 35).Value = "Paved shoulder - passenger side";
                ws.Cell(1, 36).Value = "Intersection type";
                ws.Cell(1, 37).Value = "Intersection channelisation";
                ws.Cell(1, 38).Value = "Intersecting road volume";
                ws.Cell(1, 39).Value = "Intersection quality";
                ws.Cell(1, 40).Value = "Property access points";
                ws.Cell(1, 41).Value = "Number of lanes";
                ws.Cell(1, 42).Value = "Lane width";
                ws.Cell(1, 43).Value = "Curvature";
                ws.Cell(1, 44).Value = "Quality of curve";
                ws.Cell(1, 45).Value = "Grade";
                ws.Cell(1, 46).Value = "Road condition";
                ws.Cell(1, 47).Value = "Skid resistance / grip";
                ws.Cell(1, 48).Value = "Delineation";
                ws.Cell(1, 49).Value = "Street lighting";
                ws.Cell(1, 50).Value = "Pedestrian crossing - inspected road";
                ws.Cell(1, 51).Value = "Pedestrian crossing quality";
                ws.Cell(1, 52).Value = "Pedestrian crossing facilities - side road";
                ws.Cell(1, 53).Value = "Pedestrian fencing";
                ws.Cell(1, 54).Value = "Speed management / traffic calming";
                ws.Cell(1, 55).Value = "Vehicle parking";
                ws.Cell(1, 56).Value = "Sidewalk - drivers side";
                ws.Cell(1, 57).Value = "Sidewalk - passenger side";
                ws.Cell(1, 58).Value = "Service road";
                ws.Cell(1, 59).Value = "Facilities for motorised two wheelers";
                ws.Cell(1, 60).Value = "Bicycle facility";
                ws.Cell(1, 61).Value = "Roadworks";
                ws.Cell(1, 62).Value = "Sight distance";
                ws.Cell(1, 63).Value = "Vehicle flow (AADT)";
                ws.Cell(1, 64).Value = "Motorcycle %";
                ws.Cell(1, 65).Value = "Pedestrian peak hour flow across the road";
                ws.Cell(1, 66).Value = "Pedestrian peak hour flow along the road driver-side";
                ws.Cell(1, 67).Value = "Pedestrian peak hour flow along the road passenger-side";
                ws.Cell(1, 68).Value = "Bicycle peak hourly flow";
                ws.Cell(1, 69).Value = "Operating Speed (85th percentile)";
                ws.Cell(1, 70).Value = "Operating Speed (mean)";
                ws.Cell(1, 71).Value = "Roads that cars can read";
                ws.Cell(1, 72).Value = "Car Star Rating Policy Target";
                ws.Cell(1, 73).Value = "Motorcycle Star Rating Policy Target";
                ws.Cell(1, 74).Value = "Pedestrian Star Rating Policy Target";
                ws.Cell(1, 75).Value = "Bicycle Star Rating Policy Target";
                ws.Cell(1, 76).Value = "Annual Fatality Growth Multiplier";
                ws.Cell(1, 77).Value = "School zone warning";
                ws.Cell(1, 78).Value = "School zone crossing supervisor";

                ws.Cell(1, 79).Value = "Latitude - end";
                ws.Cell(1, 80).Value = "Longitude - end";

                List<Coding> codings = CrudHelper.FindCodingsByProject(idProjeto, true);
                List<CodingImage> codingImages = new(await CrudHelper.FindCodingImagesByProjectId(idProjeto));
                if (codings != null && codings.Count > 0)
                {

                    int rowNbr = 1;
                    String lastImg = null;
                    foreach (Coding rapLoop in codings)
                    {

                        rowNbr++;

                        ws.Cell(rowNbr, 1).Value = rapLoop.CoderName;
                        ws.Cell(rowNbr, 2).Value = rapLoop.CodingDate;
                        ws.Cell(rowNbr, 3).Value = rapLoop.RoadSurveyDate;
                        var ImageNames = codingImages.Where(codImage => codImage.CodingId == rapLoop.CodingId);
                        foreach (var imageName in ImageNames)
                        {
                            ws.Cell(rowNbr, 4).Value += $"{imageName.ImageName},";
                        }
                      

                        ws.Cell(rowNbr, 5).Value = rapLoop.RoadName;
                        ws.Cell(rowNbr, 6).Value = rapLoop.Section;
                        ws.Cell(rowNbr, 7).Value = rapLoop.InitialKm ;
                        ws.Cell(rowNbr, 8).Value = Math.Abs(rapLoop.InitialKm.Value - rapLoop.FinalKm.Value);

                        ws.Cell(rowNbr, 9).Value = rapLoop.Latitude;
                        ws.Cell(rowNbr, 9).Style.NumberFormat.Format = "0.00000000";
                        ws.Cell(rowNbr, 10).Value = rapLoop.Longitude;
                        ws.Cell(rowNbr, 10).Style.NumberFormat.Format = "0.00000000";

                        ws.Cell(rowNbr, 11).Value = rapLoop.Landmark;
                        ws.Cell(rowNbr, 12).Value = rapLoop.Comments;
                        ws.Cell(rowNbr, 13).Value = rapLoop.CarriageWay;
                        ws.Cell(rowNbr, 14).Value = rapLoop.UpgradeCost;
                        ws.Cell(rowNbr, 15).Value = rapLoop.MotorcycleObserveredFlow;
                        ws.Cell(rowNbr, 16).Value = rapLoop.BicycleObservedFlow;
                        ws.Cell(rowNbr, 17).Value = rapLoop.PedestrianObservedFlow;
                        ws.Cell(rowNbr, 18).Value = rapLoop.PedestrianObservedAlongRoadDriver;
                        ws.Cell(rowNbr, 19).Value = rapLoop.PedestrianObservedAlongRoadPassenger;
                        ws.Cell(rowNbr, 20).Value = rapLoop.LandUseDrivers;
                        ws.Cell(rowNbr, 21).Value = rapLoop.LandUsePassenger;
                        ws.Cell(rowNbr, 22).Value = rapLoop.AreaType;
                        ws.Cell(rowNbr, 23).Value = rapLoop.SpeedLimit;
                        ws.Cell(rowNbr, 24).Value = rapLoop.MotorcycleSpeedLimit;
                        ws.Cell(rowNbr, 25).Value = rapLoop.TruckSpeedLimit;
                        ws.Cell(rowNbr, 26).Value = rapLoop.DifferentialSpeeds;
                        ws.Cell(rowNbr, 27).Value = rapLoop.MedianType;
                        ws.Cell(rowNbr, 28).Value = rapLoop.CentrelineRumbleStrips;
                        ws.Cell(rowNbr, 29).Value = rapLoop.RoadsideSeverityDriversSideDistance;
                        ws.Cell(rowNbr, 30).Value = rapLoop.RoadsideSeverityDriversSideObject;
                        ws.Cell(rowNbr, 31).Value = rapLoop.RoadsideSeverityPassengerSideDistance;
                        ws.Cell(rowNbr, 32).Value = rapLoop.RoadsideSeverityPassengerSideObject;
                        ws.Cell(rowNbr, 33).Value = rapLoop.ShoulderRumbleStrips;
                        ws.Cell(rowNbr, 34).Value = rapLoop.PavedShoulderDriversSide;
                        ws.Cell(rowNbr, 35).Value = rapLoop.PavedShoulderPassengerSide;
                        ws.Cell(rowNbr, 36).Value = rapLoop.IntersectionType;
                        ws.Cell(rowNbr, 37).Value = rapLoop.IntersectionChannelisation;
                        ws.Cell(rowNbr, 38).Value = rapLoop.IntersectingRoadVolume;
                        ws.Cell(rowNbr, 39).Value = rapLoop.IntersectionQuality;
                        ws.Cell(rowNbr, 40).Value = rapLoop.PropertyAccessPoints;
                        ws.Cell(rowNbr, 41).Value = rapLoop.NumberLanes;
                        ws.Cell(rowNbr, 42).Value = rapLoop.LaneWidth;
                        ws.Cell(rowNbr, 43).Value = rapLoop.Curvature;
                        ws.Cell(rowNbr, 44).Value = rapLoop.QualityCurve;
                        ws.Cell(rowNbr, 45).Value = rapLoop.Grade;
                        ws.Cell(rowNbr, 46).Value = rapLoop.RoadCondition;
                        ws.Cell(rowNbr, 47).Value = rapLoop.SkidResistanceGrip;
                        ws.Cell(rowNbr, 48).Value = rapLoop.Delineation;
                        ws.Cell(rowNbr, 49).Value = rapLoop.StreetLighting;
                        ws.Cell(rowNbr, 50).Value = rapLoop.PedestrianCrossingInspectedRoad;
                        ws.Cell(rowNbr, 51).Value = rapLoop.PedestrianCrossingQuality;
                        ws.Cell(rowNbr, 52).Value = rapLoop.PedestrianCrossingFacilitiesSideRoad;
                        ws.Cell(rowNbr, 53).Value = rapLoop.PedestrianFencing;
                        ws.Cell(rowNbr, 54).Value = rapLoop.SpeedManagementTrafficCalming;
                        ws.Cell(rowNbr, 55).Value = rapLoop.VehicleParking;
                        ws.Cell(rowNbr, 56).Value = rapLoop.SidewalkDriversSide;
                        ws.Cell(rowNbr, 57).Value = rapLoop.SidewalkPassengerSide;
                        ws.Cell(rowNbr, 58).Value = rapLoop.ServiceRoad;
                        ws.Cell(rowNbr, 59).Value = rapLoop.FacilitiesMotorisedTwoWheelers;
                        ws.Cell(rowNbr, 60).Value = rapLoop.BicycleFacility;
                        ws.Cell(rowNbr, 61).Value = rapLoop.Roadworks;
                        ws.Cell(rowNbr, 62).Value = rapLoop.SightDistance;
                        ws.Cell(rowNbr, 63).Value = rapLoop.VehicleFlow;
                        ws.Cell(rowNbr, 64).Value = rapLoop.MotorcyclePercentual;
                        ws.Cell(rowNbr, 65).Value = rapLoop.PedestrianPeakHourFlowAcrossRoad;
                        ws.Cell(rowNbr, 66).Value = rapLoop.PedestrianPeakHourFlowAlongRoadDriver;
                        ws.Cell(rowNbr, 67).Value = rapLoop.PedestrianPeakHourFlowAlongRoadPassenger;
                        ws.Cell(rowNbr, 68).Value = rapLoop.BicyclePeakHourlyFlow;
                        ws.Cell(rowNbr, 69).Value = rapLoop.OperatingSpeed85thPercentile;
                        ws.Cell(rowNbr, 70).Value = rapLoop.OperatingSpeedMean;
                        ws.Cell(rowNbr, 71).Value = rapLoop.RoadsCarsRead;
                        ws.Cell(rowNbr, 72).Value = rapLoop.CarStarRatingPolicyTarget;
                        ws.Cell(rowNbr, 73).Value = rapLoop.MotorcycleStarRatingPolicyTarget;
                        ws.Cell(rowNbr, 74).Value = rapLoop.PedestrianStarRatingPolicyTarget;
                        ws.Cell(rowNbr, 75).Value = rapLoop.BicycleStarRatingPolicyTarget;
                        ws.Cell(rowNbr, 76).Value = rapLoop.AnnualFatalityGrowthMultiplier;
                        ws.Cell(rowNbr, 77).Value = rapLoop.SchoolZoneWarning;
                        ws.Cell(rowNbr, 78).Value = rapLoop.SchoolZoneCrossingSupervisor;

                        if (rowNbr-1 < codings.Count && rowNbr != 0)
                        {
                            ws.Cell(rowNbr, 79).Value = codings[rowNbr-1].Latitude;
                            ws.Cell(rowNbr, 80).Value = codings[rowNbr-1].Longitude;
                        }

                    }
                    //decimal min = -0.0008M;
                    //decimal max = 0.0008M;

                    //decimal resultLat = (decimal)new Random().NextDouble() * (max - min) + min;
                    //decimal resultLon = (decimal)new Random().NextDouble() * (max - min) + min;

                    //ws.Cell(rowNbr, 79).Value = codings[rowNbr - 1].Latitude +resultLat  ;
                    //ws.Cell(rowNbr, 80).Value = codings[rowNbr - 1].Longitude + resultLon;

                }


                // Atualiza em disco o arquivo
                string folderName = Path.Combine(ConfigManager.GetExportPath(), $"{SanitizePath(proj.Section)}-{proj.KmStart}-{proj.KmEnd}-{CrudHelper.FindCodingsByProject((int)proj.ProjectId, false).Select(coding => coding.RoadSurveyDate).First().Value.ToString("dd-MM-yyyy", CultureInfo.InvariantCulture)}");
                if (!Directory.Exists(folderName))
                {
                    Directory.CreateDirectory(folderName);
                }

                String fileName = Path.Combine(folderName, $"{proj.Section}-{proj.KmStart}-{proj.KmEnd}" + ".xlsx");
                if (File.Exists(fileName))
                    File.Delete(fileName);

                if (!File.Exists(fileName))
                {
                    workbook.SaveAs(fileName);
                    return fileName;
                }

                throw new Exception(String.Format("Arquivo '{0}' já existe e não pode ser sobrescrito.",
                        fileName));
            });
        }


        public static async Task<string> ExecuteOutputCSV(int idProjeto, int exportType)
        {
            var proj = CrudHelper.GetProjectById(idProjeto);
            return await Task.Run(async () =>
            {
                var sb = new StringBuilder();

                // Add CSV headers
                sb.AppendLine("Coder name;Coding date;Road survey date;Image reference;Road Name;Section;Distance;Length;Latitude;Longitude;Landmark;Comments;Carriageway;Upgrade cost;Motorcycle obsevered flow;Bicycle observed flow;Pedestrian obsevered flow across the road;Pedestrian obsevered flow along the road driver-side;Pedestrian obsevered flow along the road passenger side;Land use - drivers side;Land use - passenger side;Area type;Speed limit;Motorcycle speed limit;Truck speed limit;Differential speeds;Median type;Centreline rumble strips;Roadside severity - drivers side distance;Roadside severity - drivers side object;Roadside severity - passenger side distance;Roadside severity - passenger side object;Shoulder rumble strips;Paved shoulder - drivers side;Paved shoulder - passenger side;Intersection type;Intersection channelisation;Intersecting road volume;Intersection quality;Property access points;Number of lanes;Lane width;Curvature;Quality of curve;Grade;Road condition;Skid resistance / grip;Delineation;Street lighting;Pedestrian crossing - inspected road;Pedestrian crossing quality;Pedestrian crossing facilities - side road;Pedestrian fencing;Speed management / traffic calming;Vehicle parking;Sidewalk - drivers side;Sidewalk - passenger side;Service road;Facilities for motorised two wheelers;Bicycle facility;Roadworks;Sight distance;Vehicle flow (AADT);Motorcycle %;Pedestrian peak hour flow across the road;Pedestrian peak hour flow along the road driver-side;Pedestrian peak hour flow along the road passenger-side;Bicycle peak hourly flow;Operating Speed (85th percentile);Operating Speed (mean);Roads that cars can read;Car Star Rating Policy Target;Motorcycle Star Rating Policy Target;Pedestrian Star Rating Policy Target;Bicycle Star Rating Policy Target;Annual Fatality Growth Multiplier;School zone warning;School zone crossing supervisor;Latitude - end;Longitude - end");

                List<Coding> codings = CrudHelper.FindCodingsByProject(idProjeto, true);
                List<CodingImage> codingImages = new(await CrudHelper.FindCodingImagesByProjectId(idProjeto));
                if (codings != null && codings.Count > 0)
                {
                    int rowNbr = 0;
                    foreach (Coding rapLoop in codings)
                    {
                        var images = codingImages
                            .Where(codImage => codImage.CodingId == rapLoop.CodingId && codImage.Type == 1)
                            .Select(img => img.ImageName)
                            .ToList();

                        sb.Append(string.Join(";",
                            rapLoop.CoderName,
                            rapLoop.CodingDate,
                            rapLoop.RoadSurveyDate,
                            string.Join(",", images),
                            rapLoop.RoadName,
                            rapLoop.Section,
                            rapLoop.InitialKm ,
                            Math.Abs(rapLoop.InitialKm.Value - rapLoop.FinalKm.Value),
                            rapLoop.Latitude,
                            rapLoop.Longitude,
                            rapLoop.Landmark,
                            rapLoop.Comments,
                            rapLoop.CarriageWay,
                            rapLoop.UpgradeCost,
                            rapLoop.MotorcycleObserveredFlow,
                            rapLoop.BicycleObservedFlow,
                            rapLoop.PedestrianObservedFlow,
                            rapLoop.PedestrianObservedAlongRoadDriver,
                            rapLoop.PedestrianObservedAlongRoadPassenger,
                            rapLoop.LandUseDrivers,
                            rapLoop.LandUsePassenger,
                            rapLoop.AreaType,
                            rapLoop.SpeedLimit,
                            rapLoop.MotorcycleSpeedLimit,
                            rapLoop.TruckSpeedLimit,
                            rapLoop.DifferentialSpeeds,
                            rapLoop.MedianType,
                            rapLoop.CentrelineRumbleStrips,
                            rapLoop.RoadsideSeverityDriversSideDistance,
                            rapLoop.RoadsideSeverityDriversSideObject,
                            rapLoop.RoadsideSeverityPassengerSideDistance,
                            rapLoop.RoadsideSeverityPassengerSideObject,
                            rapLoop.ShoulderRumbleStrips,
                            rapLoop.PavedShoulderDriversSide,
                            rapLoop.PavedShoulderPassengerSide,
                            rapLoop.IntersectionType,
                            rapLoop.IntersectionChannelisation,
                            rapLoop.IntersectingRoadVolume,
                            rapLoop.IntersectionQuality,
                            rapLoop.PropertyAccessPoints,
                            rapLoop.NumberLanes,
                            rapLoop.LaneWidth,
                            rapLoop.Curvature,
                            rapLoop.QualityCurve,
                            rapLoop.Grade,
                            rapLoop.RoadCondition,
                            rapLoop.SkidResistanceGrip,
                            rapLoop.Delineation,
                            rapLoop.StreetLighting,
                            rapLoop.PedestrianCrossingInspectedRoad,
                            rapLoop.PedestrianCrossingQuality,
                            rapLoop.PedestrianCrossingFacilitiesSideRoad,
                            rapLoop.PedestrianFencing,
                            rapLoop.SpeedManagementTrafficCalming,
                            rapLoop.VehicleParking,
                            rapLoop.SidewalkDriversSide,
                            rapLoop.SidewalkPassengerSide,
                            rapLoop.ServiceRoad,
                            rapLoop.FacilitiesMotorisedTwoWheelers,
                            rapLoop.BicycleFacility,
                            rapLoop.Roadworks,
                            rapLoop.SightDistance,
                            rapLoop.VehicleFlow,
                            rapLoop.MotorcyclePercentual,
                            rapLoop.PedestrianPeakHourFlowAcrossRoad,
                            rapLoop.PedestrianPeakHourFlowAlongRoadDriver,
                            rapLoop.PedestrianPeakHourFlowAlongRoadPassenger,
                            rapLoop.BicyclePeakHourlyFlow,
                            rapLoop.OperatingSpeed85thPercentile,
                            rapLoop.OperatingSpeedMean,
                            rapLoop.RoadsCarsRead,
                            rapLoop.CarStarRatingPolicyTarget,
                            rapLoop.MotorcycleStarRatingPolicyTarget,
                            rapLoop.PedestrianStarRatingPolicyTarget,
                            rapLoop.BicycleStarRatingPolicyTarget,
                            rapLoop.AnnualFatalityGrowthMultiplier,
                            rapLoop.SchoolZoneWarning,
                            rapLoop.SchoolZoneCrossingSupervisor
                        ));
                        if (rowNbr < codings.Count - 1 )
                        {
                            sb.Append($";{codings[rowNbr + 1].Latitude};");
                            sb.AppendLine($"{codings[rowNbr + 1].Longitude}");
                        }
                        else if (rowNbr == codings.Count - 1)
                        {
                            //Random rand= new Random();
                            
                            //sb.Append($";{codings[rowNbr].Latitude + (0.00001M + (decimal)(rand.NextDouble() * (0.0009 - 0.00001)))};");
                            //sb.AppendLine($"{codings[rowNbr].Longitude + (0.00001M + (decimal)(rand.NextDouble() * (0.0009 - 0.00001)))}");
                        }

                        rowNbr++;
                    }
                }

                // Save CSV file
                string folderName = Path.Combine(ConfigManager.GetExportPath(), $"{SanitizePath(proj.Section)}-{proj.KmStart}-{proj.KmEnd}-{CrudHelper.FindCodingsByProject((int)proj.ProjectId, false).Select(coding => coding.RoadSurveyDate).First().Value.ToString("dd-MM-yyyy", CultureInfo.InvariantCulture)}");
                if (!Directory.Exists(folderName))
                {
                    Directory.CreateDirectory(folderName);
                }

                String fileName = Path.Combine(folderName, $"{proj.Section}-{proj.KmStart}-{proj.KmEnd}.csv");
                if (File.Exists(fileName))
                    File.Delete(fileName);

                if (!File.Exists(fileName))
                {
                    await File.WriteAllTextAsync(fileName, sb.ToString());
                    return fileName;
                }

                throw new Exception($"File '{fileName}' already exists and cannot be overwritten.");
            });
        }

        private static string SanitizePath(string path)
        {
            return path.Replace(".", ",").Replace("\\", "");
        }

    }
}
