using Dapper;
using Microsoft.AspNetCore.Hosting;
using MisIngredientesVue.Core.Exceptions;
using MisIngredientesVue.Core.Extensions;
using MisIngredientesVue.Core.Models;
using MisIngredientesVue.Core.Services;
using Newtonsoft.Json;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using System.Xml.Serialization;

namespace MisIngredientesVue.Core.Services
{
    public interface IDeliveryRoutesService
    {
        Task<IEnumerable<GetRouteResponse>> GetRoute(string tenantId, int year, int month, int day, int offset);
        Task<EditDeliveryRoute[]> EditRoutesForDay(string tenantId, int createdBy, int year, int month, int day, EditDeliveryRoute[] routes);
        Task<IEnumerable<Driver>> GetDrivers(string tenantId);
        Task<IEnumerable<Vehicle>> GetCars(string tenantId);
        Task<IEnumerable<Warehouse>> GetWarehouses(string tenantId);
        Task<Driver> CreateDriver(string tenantId, int createdBy, Driver driver);
        Task<Warehouse> CreateWarehouse(string tenantId, int createdBy, Warehouse warehouse);
        Task<Vehicle> CreateVehicle(string tenantId, int createdBy, Vehicle vehicle);
        Task<MemoryStream> ExportToExcel(string tenantId, int year, int month, int day, int offset);
        Task<IEnumerable<ClientBranch>> GetBranchesForDay(string tenantId, int year, int month, int day, int offset);
        Task<DailyPlanning> GetDailyPlanning(string tenantId, int[] ids, int year, int month, int day, int offset);
        Task<MemoryStream> ExportDailyPlanningToExcel(string tenantId, int[] ids, int year, int month, int day, int offset);
        Task<MemoryStream> ExportVerticalPlanningToExcel(string tenantId, int[] ids, int year, int month, int day, int offset);
        Task UpdateVehicle(string tenantId, int modifiedBy, Vehicle vehicle);
        Task UpdateDriver(string tenantId, int modifiedBy, Driver driver);
        Task UpdateWarehouse(string tenantId, int modifiedBy, Warehouse warehouse);
        Task DeleteVehicles(string tenantId, int deletedById, int[] ids);
        Task DeleteDrivers(string tenantId, int deletedById, int[] ids);
        Task DeleteWarehouses(string tenantId, int deletedById, int[] ids);

    }

    public class EditDeliveryRoute
    {
        public int Vehicle { get; set; }
        public SortOrderAndId[] Drivers { get; set; }
        public SortOrderAndId[] Branches { get; set; }
        public SortOrderAndPlace[] Places { get; set; }
    }

    public class SortOrderAndId
    {
        public int SortOrder { get; set; }
        public int Id { get; set; }
    }

    public class SortedItem
    {
        public int SortOrder { get; set; }
    }

    public class SortOrderAndPlace : SortedItem
    {
        public Place Place { get; set; }
    }

    public class SortOrderAndDriver : SortedItem
    {
        public Driver Driver { get; set; }
    }

    public class SortOrderAndClientBranch : SortedItem
    {
        public ClientBranch Branch { get; set; }
    }

    public class GetRouteResponse
    {
        public Vehicle Vehicle { get; set; }
        public SortOrderAndClientBranch[] Branches { get; set; }
        public SortOrderAndDriver[] Drivers { get; set; }
        public SortOrderAndPlace[] Places { get; set; }
    }

    public class DeliveryRoutesService : IDeliveryRoutesService
    {
        private readonly IRepository _repository;
        private readonly ISettingsService _settingsService;

        public DeliveryRoutesService(IRepository repository, ISettingsService settingsService)
        {
            _settingsService = settingsService;
            _repository = repository;
        }

        public async Task<Driver> CreateDriver(string tenantId, int createdBy, Driver driver)
        {
            return await _repository.Insert<Driver, int>(tenantId, createdBy, driver);
        }

        public async Task<Vehicle> CreateVehicle(string tenantId, int createdBy, Vehicle vehicle)
        {
            return await _repository.Insert<Vehicle, int>(tenantId, createdBy, vehicle);
        }

        public async Task<Warehouse> CreateWarehouse(string tenantId, int createdBy, Warehouse warehouse)
        {
            return await _repository.Insert<Warehouse, int>(tenantId, createdBy, warehouse);
        }

        public async Task UpdateDriver(string tenantId, int modifiedBy, Driver driver)
        {
            await _repository.Update<Driver, int>(tenantId, modifiedBy, driver);
        }

        public async Task UpdateWarehouse(string tenantId, int modifiedBy, Warehouse warehouse)
        {
            await _repository.Update<Warehouse, int>(tenantId, modifiedBy, warehouse);
        }

        public async Task UpdateVehicle(string tenantId, int modifiedBy, Vehicle vehicle)
        {
            await _repository.Update<Vehicle, int>(tenantId, modifiedBy, vehicle);
        }

        public async Task<EditDeliveryRoute[]> EditRoutesForDay(string tenantId, int createdBy, int year, int month, int day, EditDeliveryRoute[] routes)
        {
            return await _repository.Query(async (dbConnection) =>
            {
                var deleteParams = new DynamicParameters();
                var deleteQuery = _repository.GetDeleteQuery<DeliveryRoute, int>(tenantId, x => x.Day == day && x.Month == month && x.Year == year, ref deleteParams);

                await dbConnection.QueryAsync(deleteQuery, deleteParams);

                var insertParams = new DynamicParameters();

                var list = new List<string>();

                foreach (var route in routes)
                {
                    foreach (var driver in route.Drivers)
                    {
                        list.Add(_repository.GetInsertQuery<DeliveryRoute, int>
                            (tenantId, createdBy, new DeliveryRoute() { SortOrder = driver.SortOrder, VehicleId = route.Vehicle, DriverId = driver.Id, Day = day, Month = month, Year = year }, ref insertParams)); ;
                    }

                    foreach (var branch in route.Branches)
                    {
                        list.Add(_repository.GetInsertQuery<DeliveryRoute, int>
                            (tenantId, createdBy, new DeliveryRoute() { SortOrder = branch.SortOrder, VehicleId = route.Vehicle, ClientBranchId = branch.Id, Day = day, Month = month, Year = year }, ref insertParams));
                    }

                    foreach (var place in route.Places)
                    {
                        place.Place.TenantId = tenantId;

                        if (place.Place.Id < 0) //El lugar es nuevo
                        {
                            place.Place.Id = await dbConnection.ExecuteScalarAsync<int>("INSERT INTO places (tenantId, description, phone, receivedby, address, lat, lng) VALUES(@tenantId, @description, @phone, @receivedBy, @address, @lat, @lng) RETURNING Id;", new
                            {
                                tenantId,
                                description = place.Place.Description,
                                phone = place.Place.Phone,
                                receivedBy = place.Place.ReceivedBy,
                                address = place.Place.Address,
                                lat = place.Place.Lat,
                                lng = place.Place.Lng
                            });
                        }
                        else
                        {
                            await dbConnection.ExecuteAsync("UPDATE places SET description = @description, phone = @phone, receivedby = @receivedBy, address = @address, lat = @lat, lng = @lng WHERE id = @id", new
                            {
                                id = place.Place.Id,
                                description = place.Place.Description,
                                phone = place.Place.Phone,
                                receivedBy = place.Place.ReceivedBy,
                                address = place.Place.Address,
                                lat = place.Place.Lat,
                                lng = place.Place.Lng
                            });
                        }

                        list.Add(_repository.GetInsertQuery<DeliveryRoute, int>
                            (tenantId, createdBy, new DeliveryRoute() { SortOrder = place.SortOrder, VehicleId = route.Vehicle, PlaceId = place.Place.Id, Day = day, Month = month, Year = year }, ref insertParams));
                    }
                }

                await dbConnection.QueryAsync(string.Join("", list), insertParams);

                return routes;

            });
        }

        public async Task<MemoryStream> ExportToExcel(string tenantId, int year, int month, int day, int offset)
        {
            var memoryStream = new MemoryStream();

            var data = await GetRoute(tenantId, year, month, day, offset);

            using (var excelPackage = new ExcelPackage())
            {
                var ws = excelPackage.Workbook.Worksheets.Add("Datos");

                var colIndex = 1;

                foreach (var item in data)
                {
                    var rowIndex = 1;
                    var total = 0M;

                    ws.Cells[1, colIndex].Style.Font.Bold = true;

                    ws.SetValue(rowIndex, colIndex, item.Vehicle.ToString());
                    rowIndex++;

                    var objects = new List<SortedItem>();

                    objects.AddRange(item.Drivers);
                    objects.AddRange(item.Branches);
                    objects.AddRange(item.Places);

                    foreach(var obj in objects.OrderBy(x => x.SortOrder))
                    {
                        if (obj is SortOrderAndDriver)
                        {
                            var driver = (obj as SortOrderAndDriver).Driver;
                            ws.SetValue(rowIndex, colIndex, $"{driver.FirstName} {driver.LastName}");
                        }
                        else if (obj is SortOrderAndPlace)
                        {
                            var place = (obj as SortOrderAndPlace).Place;
                            ws.SetValue(rowIndex, colIndex, $"{place.Description} ({place.Address})");
                        }
                        else if (obj is SortOrderAndClientBranch)
                        {
                            var branch = (obj as SortOrderAndClientBranch).Branch;
                            total += branch.Total;
                            ws.SetValue(rowIndex, colIndex, $"{branch.Client.Name} ({branch.Name})");
                        }
                            
                        rowIndex++;
                    }

                    ws.SetValue(rowIndex, colIndex, $"Total:");
                    rowIndex++;
                    ws.SetValue(rowIndex, colIndex, total);

                    rowIndex++;
                    colIndex++;
                }

                for (var i = 1; i <= colIndex; i++)
                {
                    ws.Column(i).AutoFit();
                }

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        public async Task<IEnumerable<ClientBranch>> GetBranchesForDay(string tenantId, int year, int month, int day, int offset)
        {
            return await _repository.Query(async (dbConnection) =>
            {
                var dayStart = new DateTime(year, month, day, 0, 0, 0);
                dayStart = dayStart.AddMinutes(offset);
                var dayEnd = dayStart.AddDays(1);
                var requestedDeliveryStatus = (int)DeliveryStatus.Requested;

                //                var query = $@"
                //SELECT DISTINCT public.clientbranches.*, public.clientbrands.*, public.clients.*
                //FROM public.orders
                //LEFT JOIN public.clientbranches ON public.clientbranches.id = public.orders.branchid
                //LEFT JOIN public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
                //LEFT JOIN public.clients ON public.clients.id = public.clientbranches.clientid
                //WHERE public.orders.deliverydate >= '{dayStart.ToString("yyyy-MM-ddTHH:mm:ss")}' 
                //AND public.orders.deliverystatus != @requestedDeliveryStatus
                //AND public.orders.deliverydate < '{dayEnd.ToString("yyyy-MM-ddTHH:mm:ss")}' 
                //AND public.orders.canceledbyid IS NULL
                //AND public.orders.tenantId = @tenantId";

                var query = $@"
SELECT public.clientbranches.*,
SUM(orders.total) as total
FROM public.orders
LEFT JOIN public.clientbranches ON public.clientbranches.id = public.orders.branchid
LEFT JOIN public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
LEFT JOIN public.clients ON public.clients.id = public.clientbranches.clientid
WHERE public.orders.deliverydate >= '{dayStart.ToString("yyyy-MM-ddTHH:mm:ss")}' 
AND public.orders.deliverystatus != @requestedDeliveryStatus
AND public.orders.deliverydate < '{dayEnd.ToString("yyyy-MM-ddTHH:mm:ss")}' 
AND public.orders.canceledbyid IS NULL
AND public.orders.tenantId = @tenantId
GROUP BY public.clientbranches.id
";

                //return await dbConnection.QueryAsync<ClientBranch, ClientBrand, Client, ClientBranch>(
                //query, (cbranch, cbrand, c) =>
                //{
                //    if (c != null)
                //        cbranch.Client = c;

                //    if (cbrand != null)
                //        cbranch.ClientBrand = cbrand;

                //    return cbranch;

                //}, new { tenantId, requestedDeliveryStatus });

                return await dbConnection.QueryAsync<ClientBranch>(query, new { tenantId, requestedDeliveryStatus });
            });

        }

        public async Task<IEnumerable<Vehicle>> GetCars(string tenantId)
        {
            return await _repository.FindAll<Vehicle, int>(tenantId);
        }

        public async Task<IEnumerable<Warehouse>> GetWarehouses(string tenantId)
        {
            return await _repository.FindAll<Warehouse, int>(tenantId);
        }

        public async Task<MemoryStream> ExportDailyPlanningToExcel(string tenantId, int[] ids, int year, int month, int day, int offset)
        {
            var dailyPlanning = await GetDailyPlanning(tenantId, ids, year, month, day, offset);

            var memoryStream = new MemoryStream();

            var colIndex = 1;

            using (var excelPackage = new ExcelPackage())
            {
                var ws = excelPackage.Workbook.Worksheets.Add($"Planeación_{year}_{month}_{day}");

                var totalsRowIndex = 1;
                var rowIndex = 3;
                var headerRowIndex = 2;

                ws.SetValue(headerRowIndex, 1, "Comprador");
                ws.SetValue(headerRowIndex, 2, "Producto");
                ws.SetValue(headerRowIndex, 3, "Zona de compra");
                ws.SetValue(headerRowIndex, 4, "Proveedor");
                ws.SetValue(headerRowIndex, 5, "Unidades");

                ws.Cells[headerRowIndex, 1].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 2].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 3].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 4].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 5].Style.Font.Bold = true;

                colIndex = 6;

                foreach (var client in dailyPlanning.Clients)
                {
                    foreach (var branch in client.Branches)
                    {
                        ws.Cells[headerRowIndex, colIndex].Style.Font.Bold = true;
                        ws.SetValue(headerRowIndex, colIndex, $"{branch.Name} ({client.Client})");
                        colIndex++;
                    }
                }

                ws.SetValue(headerRowIndex, colIndex, "Costo total");
                ws.Cells[headerRowIndex, colIndex].Style.Font.Bold = true;
                colIndex++;
                ws.SetValue(headerRowIndex, colIndex, "Cantidad total");
                ws.Cells[headerRowIndex, colIndex].Style.Font.Bold = true;
                colIndex++;
                ws.SetValue(headerRowIndex, colIndex, "Vigencia costo");
                ws.Cells[headerRowIndex, colIndex].Style.Font.Bold = true;

                var totalQtyColIndex = 0;
                var totalCostColIndex = 0;

                foreach (var product in dailyPlanning.Products.OrderBy(x => x.Name))
                {
                    ws.SetValue(rowIndex, 1, product.Buyer);
                    ws.SetValue(rowIndex, 2, product.Name);
                    ws.SetValue(rowIndex, 3, product.PurchaseZone);
                    ws.SetValue(rowIndex, 4, product.Supplier);
                    ws.SetValue(rowIndex, 5, product.UnitName);

                    colIndex = 6;

                    var qtyIndex = 0;
                    foreach (var qty in product.Qties)
                    {
                        if (string.IsNullOrEmpty(product.Specifications[qtyIndex]))
                        {
                            if (!qty.HasValue || qty == 0)
                                ws.SetValue(rowIndex, colIndex, "");
                            else
                                ws.SetValue(rowIndex, colIndex, qty);
                        }
                        else
                            ws.SetValue(rowIndex, colIndex, $"{qty} \"{product.Specifications[qtyIndex]}\"");

                        colIndex++;
                        qtyIndex++;
                    }

                    totalCostColIndex = colIndex;
                    ws.SetValue(rowIndex, colIndex, product.TotalCost.ToString("c"));
                    colIndex++;

                    totalQtyColIndex = colIndex;
                    ws.SetValue(rowIndex, colIndex, product.TotalQty);
                    colIndex++;

                    ws.SetValue(rowIndex, colIndex, product.ExpirationStatus);

                    rowIndex++;
                }

                ws.SetValue(totalsRowIndex, totalCostColIndex, $"Total: {dailyPlanning.TotalCost}");
                ws.SetValue(totalsRowIndex, totalQtyColIndex, $"Total: {dailyPlanning.TotalQty}");

                for (var i = 1; i <= colIndex; i++)
                {
                    ws.Column(i).AutoFit();
                }

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;

        }


        public async Task<MemoryStream> ExportVerticalPlanningToExcel(string tenantId, int[] ids, int year, int month, int day, int offset)
        {
            var planning = await GetVerticalPlanning(tenantId, ids, year, month, day, offset);

            var memoryStream = new MemoryStream();

            var now = DateTime.UtcNow;

            var calculateproductpriceexpirationcontinue = await _settingsService.GetSettingValue<bool>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationContinue);
            var calculateproductpriceexpirationdeny = await _settingsService.GetSettingValue<bool>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationDeny);
            var calculateproductpriceexpirationcontinue_days = await _settingsService.GetSettingValue<int>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationContinueDays);
            var calculateproductpriceexpirationdeny_days = await _settingsService.GetSettingValue<int>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationDenyDays);

            using (var excelPackage = new ExcelPackage())
            {
                var ws = excelPackage.Workbook.Worksheets.Add($"PlaneaciónVertical_{year}_{month}_{day}");

                var rowIndex = 2;
                var headerRowIndex = 1;

                ws.SetValue(headerRowIndex, 1, "Folio");
                ws.SetValue(headerRowIndex, 2, "Comprador");
                ws.SetValue(headerRowIndex, 3, "Proveedor");
                ws.SetValue(headerRowIndex, 4, "Zona de Compra");
                ws.SetValue(headerRowIndex, 5, "Marca");
                ws.SetValue(headerRowIndex, 6, "Sucursal");
                ws.SetValue(headerRowIndex, 7, "Producto");
                ws.SetValue(headerRowIndex, 8, "Cantidad solicitada");
                ws.SetValue(headerRowIndex, 9, "Unidad");
                ws.SetValue(headerRowIndex, 10, "Observaciones");
                ws.SetValue(headerRowIndex, 11, "Costo unitario");
                ws.SetValue(headerRowIndex, 12, "Presupuesto");
                ws.SetValue(headerRowIndex, 13, "Fecha de Entrega");
                ws.SetValue(headerRowIndex, 14, "Continuar vendiendo");
                ws.SetValue(headerRowIndex, 15, "Vigencia de costo");

                ws.Cells[headerRowIndex, 1].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 2].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 3].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 4].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 5].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 6].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 7].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 8].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 9].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 10].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 11].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 12].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 13].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 14].Style.Font.Bold = true;
                ws.Cells[headerRowIndex, 15].Style.Font.Bold = true;

                foreach (var row in planning)
                {
                    var iva = row.Iva > 0 ? row.Iva : row.ProductIva;
                    var ieps = row.Ieps > 0 ? row.Ieps : row.ProductIeps;

                    var cost = row.Cost > 0 ? row.Cost : row.ProductCost;
                    cost = cost + (cost * (iva / 100M)) + (cost * (ieps / 100M));

                    ws.SetValue(rowIndex, 1, row.Folio);
                    ws.SetValue(rowIndex, 2, row.Buyer);
                    ws.SetValue(rowIndex, 3, row.Supplier);
                    ws.SetValue(rowIndex, 4, row.PurchaseZone);
                    ws.SetValue(rowIndex, 5, row.Brand);
                    ws.SetValue(rowIndex, 6, row.Branch);
                    ws.SetValue(rowIndex, 7, row.Product);
                    ws.SetValue(rowIndex, 8, row.Qty);
                    ws.SetValue(rowIndex, 9, row.Unit);
                    ws.SetValue(rowIndex, 10, row.Specification);
                    ws.SetValue(rowIndex, 11, cost.ToString("C"));
                    ws.SetValue(rowIndex, 12, (cost * row.Qty).ToString("C"));
                    ws.SetValue(rowIndex, 13, row.DeliveryDate.ToString("dd/MM/yyyy"));
                    ws.SetValue(rowIndex, 14, row.ContinueStockMode ? "Sí" : "No");

                    var expirationStatus = "";

                    if (row.ProductCostModified.HasValue)
                    {
                        if (row.ContinueStockMode && calculateproductpriceexpirationcontinue)
                            expirationStatus = now - row.ProductCostModified > TimeSpan.FromDays(calculateproductpriceexpirationcontinue_days) ? "No vigente" : "Vigente";

                        if (!row.ContinueStockMode && calculateproductpriceexpirationdeny)
                            expirationStatus = now - row.ProductCostModified > TimeSpan.FromDays(calculateproductpriceexpirationdeny_days) ? "No vigente" : "Vigente";
                    }

                    ws.SetValue(rowIndex, 15, expirationStatus);

                    rowIndex++;
                }

                for (var i = 1; i <= 14; i++)
                {
                    ws.Column(i).AutoFit();
                }

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;

        }

        private async Task<IEnumerable<VerticalPlanningRow>> GetVerticalPlanning(string tenantId, int[] ids, int year, int month, int day, int offset)
        {
            var dayStart = new DateTime(year, month, day, 0, 0, 0);
            dayStart = dayStart.AddMinutes(offset);
            var dayEnd = dayStart.AddDays(1);

            var requestedDeliveryStatus = (int)DeliveryStatus.Requested;

            return await _repository.Query(async (dbConnection) => {
                var query = $@"
SELECT orders.numfolio as folio, products.buyer, products.supplier, products.purchasezone, clientbranches.name as branch, clientbrands.name as brand,
products.name as product, orderproducts.qty as qty, units.name as unit, orderproducts.specification, orderproducts.suppliercost as cost, products.suppliercost as productcost, 
orders.deliverydate, products.continuestockmode, orderproducts.iva as iva, products.iva as productiva, orderproducts.ieps as ieps, products.ieps as productieps,
products.costmodified as productcostmodified
from orders
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
LEFT JOIN products ON orderproducts.productid = products.id
LEFT JOIN units ON products.unitid = units.id
WHERE {(ids.Length > 0 ? $"public.clientbranches.id IN ({string.Join(",", ids)}) AND " : "")} 
public.orders.deliverydate >= '{dayStart.ToString("yyyy-MM-ddTHH:mm:ss")}' 
AND public.orders.deliverydate < '{dayEnd.ToString("yyyy-MM-ddTHH:mm:ss")}'
AND orders.tenantId = @tenantId 
AND products.tenantId = @tenantId 
AND orders.deliverystatus != @requestedDeliveryStatus
AND orders.canceledbyid IS NULL
ORDER BY orders.id
";

                return await dbConnection.QueryAsync<VerticalPlanningRow>(query, new { tenantId, requestedDeliveryStatus });
            });
        }

        public async Task<DailyPlanning> GetDailyPlanning(string tenantId, int[] ids, int year, int month, int day, int offset)
        {
            var dayStart = new DateTime(year, month, day, 0, 0, 0);
            dayStart = dayStart.AddMinutes(offset);
            var dayEnd = dayStart.AddDays(1);

            var requestedDeliveryStatus = (int)DeliveryStatus.Requested;

            var products = await _repository.Query(async (dbConnection) => {
                var query = $@"
SELECT productid, public.products.buyer, public.products.name, public.products.purchasezone,
public.products.supplier, public.units.name as unitname, public.products.costmodified as ProductCostModified, public.products.continuestockmode,
--(public.products.suppliercost * SUM(public.orderproducts.qty)) as totalcost,
SUM(public.orderproducts.qty) as totalqty, 
STRING_AGG(public.clients.name, '$%$') as rawclients, 
STRING_AGG(public.clientbranches.name, '$%$') as rawbranches,
STRING_AGG(CAST (public.clientbranches.id AS VARCHAR(5000)), '$%$') as rawbranchids,
STRING_AGG(CAST (public.orderproducts.qty AS VARCHAR(5000)), '$%$') as rawqtys, 
STRING_AGG(CAST (public.orderproducts.specification AS VARCHAR(10000)), '$%$') as rawspecs,
STRING_AGG(COALESCE(CAST (public.orderproducts.suppliercost AS VARCHAR(10000)), (SELECT CAST(suppliercost AS VARCHAR(100)) from products WHERE id = public.orderproducts.productid LIMIT 1)), '$%$') as rawsuppliercosts,
STRING_AGG(COALESCE(CAST (public.orderproducts.iva AS VARCHAR(10000)), COALESCE((SELECT CAST(iva AS VARCHAR(100)) from products WHERE id = public.orderproducts.productid LIMIT 1)), ''), '$%$') as rawiva,
STRING_AGG(COALESCE(CAST (public.orderproducts.ieps AS VARCHAR(10000)), COALESCE((SELECT CAST(ieps AS VARCHAR(100)) from products WHERE id = public.orderproducts.productid LIMIT 1)), ''), '$%$') as rawieps
FROM public.orders
left join public.orderproducts on public.orders.id = public.orderproducts.orderid
left join public.products on public.orderproducts.productid = public.products.id
left join public.clientbranches on public.clientbranches.id = public.orders.branchid
left join public.clients on public.orders.clientid = public.clients.id
left join public.units on public.units.id = public.products.unitid
WHERE {(ids.Length > 0 ? $"public.clientbranches.id IN ({string.Join(",", ids)}) AND " : "")} public.orders.deliverydate >= '{dayStart.ToString("yyyy-MM-ddTHH:mm:ss")}' AND public.orders.deliverydate < '{dayEnd.ToString("yyyy-MM-ddTHH:mm:ss")}'
AND public.orders.tenantId = @tenantId 
AND public.products.tenantId = @tenantId
AND public.orders.canceledbyid IS NULL
AND public.orders.deliverystatus != @requestedDeliveryStatus
group by productid, public.products.buyer, public.products.name, public.products.purchasezone, public.products.supplier,
public.products.suppliercost, public.units.name, public.products.costmodified, public.products.continuestockmode
";
                //Console.WriteLine(query);

                return await dbConnection.QueryAsync<DailyPlanningProduct>(query, new { tenantId, requestedDeliveryStatus });
            });

            var rawClientsAndQties = new List<RawDailyPlanningClient>();

            var resultingProducts = products.Select(x =>
            {
                var branchIds = x.RawBranchIds?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var branches = x.RawBranches?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var clients = x.RawClients?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var qties = x.RawQtys?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var specs = x.RawSpecs?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var rawIva = x.RawIva?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var rawIeps = x.RawIeps?.Split(new string[] { "$%$" }, StringSplitOptions.None);
                var rawSupplierCosts = x.RawSupplierCosts?.Split(new string[] { "$%$" }, StringSplitOptions.None);

                rawClientsAndQties.AddRange(branches
                .Zip(clients, (branch, client) =>
                {
                    return new RawDailyPlanningClient() {
                        ProductId = x.ProductId,
                        Client = client,
                        Branch = branch,
                        Qty = 0
                    };

                }).Zip(qties, (client, qty) =>
                {
                    client.Qty = Convert.ToDecimal(string.IsNullOrEmpty(qty) ? "0" : qty, CultureInfo.InvariantCulture);
                    
                    return client;

                }).Zip(branchIds, (client, branchId) =>
                {
                    client.BranchId = branchId;
                    return client;

                }).Zip(specs, (client, spec) =>
                {
                    client.Spec = spec;
                    return client;

                }).ToArray());

                //x.Specifications = specs;
                x.Ieps = rawIeps.Select(y => Convert.ToDecimal(string.IsNullOrEmpty(y) ? "0" : y, CultureInfo.InvariantCulture)).ToArray();
                x.Iva = rawIva.Select(y => Convert.ToDecimal(string.IsNullOrEmpty(y) ? "0" : y, CultureInfo.InvariantCulture)).ToArray();
                x.SupplierCosts = rawSupplierCosts.Select(y => Convert.ToDecimal(string.IsNullOrEmpty(y) ? "0" : y, CultureInfo.InvariantCulture)).ToArray();

                return x;
            }).ToArray();

            //Obtén grupos de clientes
            var resultingClients = rawClientsAndQties
                .GroupBy(x => x.Client)
                .Select(x => new DailyPlanningClient()
                {
                    Client = x.Key,

                    Branches = x.DistinctBy(y => y.BranchId).Select(y => new DailyPlanningBranch() { Id = y.BranchId, Name = y.Branch }).ToArray()
                })
                .ToArray();


            var now = DateTime.UtcNow;

            var calculateproductpriceexpirationcontinue = await _settingsService.GetSettingValue<bool>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationContinue);
            var calculateproductpriceexpirationdeny = await _settingsService.GetSettingValue<bool>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationDeny);
            var calculateproductpriceexpirationcontinue_days = await _settingsService.GetSettingValue<int>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationContinueDays);
            var calculateproductpriceexpirationdeny_days = await _settingsService.GetSettingValue<int>(tenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationDenyDays);


            foreach (var resultingProduct in resultingProducts)
            {
                //Agrupa (suma) las cantidades totales por sucursal

                var qties = new List<decimal?>();
                var specs = new List<string>();

                foreach (var resultingClient in resultingClients)
                {
                    foreach (var resultingBranch in resultingClient.Branches)
                    {

                        var qtyInfo = rawClientsAndQties.Where(x =>
                            x.ProductId == resultingProduct.ProductId && x.BranchId == resultingBranch.Id);

                        qties.Add(qtyInfo == null || qtyInfo.Count() == 0 ? 0 : qtyInfo.Sum(x => x.Qty));
                        specs.Add(qtyInfo == null || qtyInfo.Count() == 0 ? "" : string.Join(" // ", qtyInfo.Select(x => x.Spec).Where(x => !string.IsNullOrWhiteSpace(x))));
                    }
                }

                //Calcula el costo total para cada producto (incluyendo impuestos)

                var rawQties = resultingProduct.RawQtys?.Split(new string[] { "$%$" }, StringSplitOptions.None)
                    .Select(y => Convert.ToDecimal(string.IsNullOrEmpty(y) ? "0" : y, CultureInfo.InvariantCulture)).ToArray();

                for (var i = 0; i < resultingProduct.SupplierCosts.Length; i++)
                {
                    resultingProduct.TotalCost += (resultingProduct.SupplierCosts[i]
                        + (resultingProduct.SupplierCosts[i] * (resultingProduct.Iva[i] / 100M))
                        + (resultingProduct.SupplierCosts[i] * (resultingProduct.Ieps[i] / 100M)))
                        * rawQties[i];
                }

                resultingProduct.Specifications = specs.ToArray();
                resultingProduct.Qties = qties.ToArray();


                //Obtén la vigencia 

                var expirationStatus = "";

                if (resultingProduct.ProductCostModified.HasValue)
                {
                    if (resultingProduct.ContinueStockMode && calculateproductpriceexpirationcontinue)
                        expirationStatus = now - resultingProduct.ProductCostModified > TimeSpan.FromDays(calculateproductpriceexpirationcontinue_days) ? "No vigente" : "Vigente";

                    if (!resultingProduct.ContinueStockMode && calculateproductpriceexpirationdeny)
                        expirationStatus = now - resultingProduct.ProductCostModified > TimeSpan.FromDays(calculateproductpriceexpirationdeny_days) ? "No vigente" : "Vigente";
                }

                resultingProduct.ExpirationStatus = expirationStatus;

            }

            return new DailyPlanning()
            {
                TotalCost = resultingProducts.Sum(x => x.TotalCost),
                TotalQty = resultingProducts.Sum(x => x.TotalQty),
                Products = resultingProducts.OrderBy(x => x.Name).ToArray(),
                Clients = resultingClients
            };
        }

        public async Task<IEnumerable<Driver>> GetDrivers(string tenantId)
        {
            return await _repository.FindAll<Driver, int>(tenantId);
        }

        private async Task<IEnumerable<ClientBranch>> GetBranchTotals(string tenantId, int[] branchIds, int year, int month, int day, int offset) {

            if (branchIds.Length == 0) return new ClientBranch[] { };

            var dayStart = new DateTime(year, month, day, 0, 0, 0);
            dayStart = dayStart.AddMinutes(offset);
            var dayEnd = dayStart.AddDays(1);

            var requestedDeliveryStatus = (int)DeliveryStatus.Requested;

            return await _repository.Query(async (dbConnection) =>
            {
                var query = $@"
SELECT public.clientbranches.id,
SUM(orders.total) as total
FROM public.orders
LEFT JOIN public.clientbranches ON public.clientbranches.id = public.orders.branchid
LEFT JOIN public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
LEFT JOIN public.clients ON public.clients.id = public.clientbranches.clientid
WHERE public.orders.deliverydate >= '{dayStart.ToString("yyyy-MM-ddTHH:mm:ss")}' 
AND public.orders.deliverydate< '{dayEnd.ToString("yyyy-MM-ddTHH:mm:ss")}'  
AND public.clientbranches.id IN ({(string.Join(", ", branchIds.Select(x => x.ToString())))})
AND public.orders.canceledbyid IS NULL
AND public.orders.deliverystatus != @requestedDeliveryStatus
AND public.orders.tenantId = @tenantId
GROUP BY public.clientbranches.id
";

                return await dbConnection.QueryAsync<ClientBranch>(query, new { tenantId, requestedDeliveryStatus });
            });
        }

        public async Task<IEnumerable<GetRouteResponse>> GetRoute(string tenantId, int year, int month, int day, int offset)
        {
            //var routes = await _repository.FindAll<DeliveryRoute, int>(tenantId, x => x.Month == month && x.Year == year && x.Day == day);
            var routes = await _repository.Query(async (dbConnection) =>
            {
                var query = @"
SELECT * FROM public.deliveryroutes
LEFT JOIN public.clientbranches ON public.clientbranches.id = public.deliveryroutes.clientbranchid
LEFT JOIN public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
LEFT JOIN public.clients ON public.clients.id = public.clientbranches.clientid
LEFT JOIN public.drivers ON public.drivers.id = public.deliveryroutes.driverid
LEFT JOIN public.vehicles ON public.vehicles.id = public.deliveryroutes.vehicleid
LEFT JOIN public.places ON public.places.id = public.deliveryroutes.placeid
WHERE public.deliveryroutes.year = @year 
AND public.deliveryroutes.month = @month 
AND public.deliveryroutes.day = @day 
AND public.deliveryroutes.tenantId = @tenantId
ORDER BY public.deliveryroutes.sortorder
";
                return await dbConnection.QueryAsync<DeliveryRoute, ClientBranch, ClientBrand, Client, Driver, Vehicle, Place, DeliveryRoute>(
                query,
                (dr, cbranch, cbrand, cl, d, v, pl) =>
                {
                    if (cbranch != null)
                        dr.ClientBranch = cbranch;

                    if (cbrand != null && dr.ClientBranch != null)
                        dr.ClientBranch.ClientBrand = cbrand;

                    if (cl != null && dr.ClientBranch != null)
                        dr.ClientBranch.Client = cl;

                    if (d != null)
                        dr.Driver = d;

                    if (v != null)
                        dr.Vehicle = v;

                    if (pl != null)
                        dr.Place = pl;

                    return dr;

                }, new { tenantId, year, month, day });
            });

            var response = routes.GroupBy(x => x.VehicleId).Select(x => new GetRouteResponse()
            {
                Vehicle = routes.FirstOrDefault(y => y.VehicleId == x.Key).Vehicle,
                Branches = x.Where(y => y.ClientBranch != null).Select(y => new SortOrderAndClientBranch() { Branch = y.ClientBranch, SortOrder = y.SortOrder }).ToArray(),
                Drivers = x.Where(y => y.Driver != null).Select(y => new SortOrderAndDriver { Driver = y.Driver, SortOrder = y.SortOrder }).ToArray(),
                Places = x.Where(x => x.Place != null).Select(y => new SortOrderAndPlace { Place = y.Place, SortOrder = y.SortOrder }).ToArray(),
            });

            var branchIds = new List<int>();
            foreach (var item in response)
            {
                foreach (var branch in item.Branches)
                {
                    branchIds.Add(branch.Branch.Id);
                }
            }

            if (branchIds.Count > 0)
            {
                var branchTotals = await GetBranchTotals(tenantId, branchIds.ToArray(), year, month, day, offset);
                foreach (var item in response)
                {
                    foreach (var branch in item.Branches)
                    {
                        branch.Branch.Total = branchTotals.FirstOrDefault(x => x.Id == branch.Branch.Id)?.Total ?? 0;
                    }
                }
            }
           
            return response;

        }

        public async Task DeleteVehicles(string tenantId, int deletedById, int[] ids)
        {
            await _repository.DeleteManyByIds<Vehicle, int>(tenantId, deletedById, ids);
        }

        public async Task DeleteDrivers(string tenantId, int deletedById, int[] ids)
        {
            await _repository.DeleteManyByIds<Driver, int>(tenantId, deletedById, ids);
        }

        public async Task DeleteWarehouses(string tenantId, int deletedById, int[] ids)
        {
            await _repository.DeleteManyByIds<Warehouse, int>(tenantId, deletedById, ids);
        }
    }

    public class DailyPlanning
    {
        public decimal TotalQty { get; set; }
        public decimal TotalCost { get; set; }
        public DailyPlanningProduct[] Products { get; set; }
        public DailyPlanningClient[] Clients { get; set; }
    }

    public class VerticalPlanningRow
    {
        public string Folio { get; set; } 
        public string Buyer { get; set; }
        public string Supplier { get; set; }
        public string PurchaseZone { get; set; }
        public string Brand { get; set; }
        public string Branch { get; set; }
        public string Product { get; set; }
        public decimal Qty { get; set; }
        public string Unit { get; set; }
        public string Specification { get; set; }
        public decimal Cost { get; set; }
        public decimal ProductCost { get; set; }
        public DateTime? ProductCostModified { get; set; }
        public decimal Iva { get; set; }
        public decimal ProductIva { get; set; }
        public decimal Ieps { get; set; }
        public decimal ProductIeps { get; set; }
        public decimal SalePrice { get; set; }
        public DateTime DeliveryDate { get; set; }
        public bool ContinueStockMode { get; set; }
    }

    public class DailyPlanningProduct
    {
        public int ProductId { get; set; }
        public string Buyer { get; set; }
        public string Name { get; set; }
        public string PurchaseZone { get; set; }
        public string Supplier { get; set; }
        public string UnitName { get; set; }
        public decimal TotalCost { get; set; }
        public decimal TotalQty { get; set; }
        public string ExpirationStatus { get; set; }

        public bool ContinueStockMode { get; set; }
        public DateTime? ProductCostModified { get; set; }

        [JsonIgnore]
        public string RawClients { get; set; }
        [JsonIgnore]
        public string RawBranchIds { get; set; }
        [JsonIgnore]
        public string RawBranches { get; set; }


        [JsonIgnore]
        public string RawQtys { get; set; }
        [JsonIgnore]
        public string RawSpecs { get; set; }
        [JsonIgnore]
        public string RawSupplierCosts { get; set; }
        [JsonIgnore]
        public string RawIva { get; set; }
        [JsonIgnore]
        public string RawIeps { get; set; }


        public decimal?[] Qties { get; set; }
        public string[] Specifications { get; set; }
        public decimal[] SupplierCosts { get; set; }
        public decimal[] Iva { get; set; }
        public decimal[] Ieps { get; set; }
    }

    public class DailyPlanningClient
    {
        public string Client { get; set; }
        public DailyPlanningBranch[] Branches { get; set; }
    }

    public class DailyPlanningBranch
    {
        public string Id { get; set; }
        public string Name { get; set; }
    }

    public class RawDailyPlanningClient
    {
        public int ProductId { get; set; }
        public string Spec { get; set; }
        public string Client { get; set; }
        public string Branch { get; set; }
        public decimal Qty { get; set; }
        public string BranchId { get; set; }
    }
}