using Dapper;
using Microsoft.AspNetCore.JsonPatch;
using MisIngredientesVue.Core.Exceptions;
using MisIngredientesVue.Core.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Linq.Expressions;
using MisIngredientesVue.Core.Extensions;
using System.Data;
using Newtonsoft.Json;
using System.Threading;
using OfficeOpenXml;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using OfficeOpenXml.Style;

namespace MisIngredientesVue.Core.Services
{
    public class OrdersFilterQuery
    {
        public int? Id { get; set; }
        public string Client { get; set; }
        public string Brand { get; set; }
        public string Branch { get; set; }
        public decimal? Total { get; set; }
        public string DeliveryDate { get; set; }
        public string DeliveryStatus { get; set; }
        public string PaymentStatus { get; set; }
        public string PromisedPaymentDate { get; set; }
        public string ActualPaymentDate { get; set; }
        public int? IncludeCancelled { get; set; }
        public int? InvoiceStatus { get; set; }
    }

    public interface IOrdersService
    {
        Task<Order> GetOrderById(string tenantId, int userId, int id);
        Task<Order> GetClientOrderById(string tenantId, int userId, int id);
        Task<OrderPayment> GetOrderPaymentById(string tenantId, int id);
        Task DeleteOrderPayment(string tenantId, int deletedById, int orderId, int orderPaymentId);
        Task DeleteMultiOrderPayment(string tenantId, int deletedById, int orderPaymentId);
        Task<IEnumerable<Order>> GetOrdersByIds(string tenantId, int[] ids);
        Task UpdateOrder(string tenantId, int updatedBy, Order order, bool isClient = false);
        Task UpdateClientOrder(string tenantId, int updatedBy, Order order);
        Task<IEnumerable<DistinctResult>> GetClientFilters(string tenantId, string type, string q, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus, string name, string legalName);
        Task<IEnumerable<DistinctResult>> GetFilters(string tenantId, string type, string q, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus);
        Task<FindResults<TableOrder>> GetOrders(string tenantId, string q, int skip, int take, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus);
        Task<FindResults<TableClientOrder>> GetClientOrders(string tenantId, string q, int skip, int take, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus, string name, string legalName);
        Task<GetOrderPaymentsResponse> GetOrderPayments(string tenantId, int orderId);
        Task<GetOrderPaymentsResponse> GetOrdersTotal(string tenantId, int[] orderId, OrdersFilterQuery filterQuery);
        Task<Order> InsertOrder(string tenantId, int createdBy, Order order, bool isClient = false);
        Task<Order> CreateOrderFromTemplate(string tenantId, int createdBy, Order order);
        Task<IEnumerable<Order>> CreateClientOrdersFromTemplate(string tenantId, int createdBy, Order order);
        Task<Order> CreateOrderFromPublicTemplate(string templateId, Order order);
        Task<MemoryStream> GetExcel(string tenantId, int offset, string q, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus);
        Task<MemoryStream> GetClientExcel(string tenantId, int offset, string q, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus, string name, string legalName);
        Task CancelOrders(string tenantId, string logoUrl, int canceledById, int[] ids, OrdersFilterQuery filterQuery);
        Task CancelClientOrders(string tenantId, string logoUrl, int canceledById, int[] ids, OrdersFilterQuery filterQuery);
        Task<bool> SetDeliveryStatus(string tenantId, int modifiedBy, int[] ids, int status, OrdersFilterQuery filterQuery);
        //Task SetPaymentStatus(string tenantId, int modifiedBy, int[] ids, int status);
        Task SetPromisedPaymentDate(string tenantId, int modifiedBy, int[] ids, DateTime date, OrdersFilterQuery filterQuery);
        Task SetDeliveryDate(string tenantId, int modifiedBy, int[] ids, DateTime date, OrdersFilterQuery filterQuery);
        Task SetClientDeliveryDate(string tenantId, int modifiedBy, int[] ids, DateTime date, OrdersFilterQuery filterQuery);
        //Task SetActualPaymentDate(string tenantId, int modifiedBy, int[] ids, DateTime date);
        Task<Order> PatchOrder(string tenantId, int modifiedBy, int id, JsonPatchDocument<Order> changes);
        Task<UpdatedPaymentStatus> InsertPayment(string tenantId, int createdBy, int orderId, DateTime date, decimal amount);
        Task<UpdatedPaymentStatus> InsertMultiPayment(string tenantId, int createdBy, int[] orderIds, DateTime date, OrdersFilterQuery filterQuery);
        Task<IEnumerable<DistinctResult>> GetDistinctValues(string tenantId, string propName, string term);
        Task<Order> InsertOrderTest(int createdBy, Order order);
        Expression<Func<Order, bool>> GetFilterExpressionFromQuery(OrdersFilterQuery query);
        Task SetPaymentDate(string tenantId, int orderId, int paymentId, DateTime date);
        Task SendConfirmationEmail(Order order, string logoUrl, byte[] orderAsPdf);
        Task UpdateAccessTime(string tenantId, int userId, int[] orderIds);
        Task ClearAccessTime(string tenantId, int userId, int id);
        Task<OrderAllowedDates> GetAllowedDates(string tenantId, int id);
        Task<MemoryStream> GetExcelFromData(OrderProduct[] products, int offset);
        OrderCalculations CalculateTotal(Order order);
    }

    public class OrdersService : IOrdersService
    {
        private readonly IRepository _repository;
        private readonly IOrderTemplatesService _orderTemplatesService;
        private readonly IFileStorageService _storageService;
        private readonly IWebHookService _webHookService;
        private readonly IEmailService _emailService;
        private readonly ISettingsService _settingsService;

        public OrdersService(IRepository repository, IOrderTemplatesService orderTemplatesService,
            IFileStorageService storageService, IWebHookService webhookService, IEmailService emailService,
            ISettingsService settingsService)
        {
            _repository = repository;
            _orderTemplatesService = orderTemplatesService;
            _storageService = storageService;
            _webHookService = webhookService;
            _emailService = emailService;
            _settingsService = settingsService;
        }

        public async Task ClearAccessTime(string tenantId, int userId, int id)
        {
            await _repository.Query<string>(async (db) =>
            {
                var query = $@"UPDATE public.orders SET lastaccessed = NULL, accessedbyid = NULL
WHERE id = @id AND accessedbyid = @userId AND tenantId = @tenantId;";

                await db.QueryAsync(query, new { id, tenantId, userId });
                return "";
            });
        }

        public async Task UpdateAccessTime(string tenantId, int userId, int[] orderIds)
        {
            await _repository.Query<string>(async (db) =>
            {
                var query = $@"UPDATE public.orders SET lastaccessed = @lastaccessed, accessedbyid = @userId
    WHERE id IN ({string.Join(',', orderIds.Select(x => x.ToString()))}) AND accessedbyid = @userId AND tenantId = @tenantId";

                await db.QueryAsync(query, new { lastaccessed = DateTime.UtcNow, userId, tenantId });
                return "";
            });
        }

        //Este método es para que los clientes editen pedidos suyos o de proveedores
        public async Task UpdateClientOrder(string tenantId, int updatedBy, Order order)
        {
            //WARNING: Asegurarse que el cliente tiene permisos en este tenantId
            await UpdateOrder(order.TenantId, updatedBy, order, true);
        }

        public async Task UpdateOrder(string tenantId, int updatedBy, Order order, bool isClient = false)
        {
            var strBuilder = new StringBuilder();

            var orderId = order.Id;

            await _repository.Query(async (db) =>
            {
                var dynamicParameters = new DynamicParameters();

                var currentOrder = await db.QueryFirstOrDefaultAsync<Order>("SELECT accessedbyid, discountType FROM orders WHERE id = @orderId AND tenantId = @tenantId", new { tenantId, orderId });

                if(currentOrder == null)
                    throw new UserFriendlyException("El pedido no existe");

                if (currentOrder.AccessedById.HasValue && currentOrder.AccessedById.Value != updatedBy)
                    throw new UserFriendlyException("El pedido está siendo editado por otro usuario");

                if (currentOrder.DiscountType != null && order.DiscountType == null)
                    order.DiscountType = currentOrder.DiscountType;

                var existingOrderProducts =
                    (await db.QueryAsync<OrderProduct>(

                        $"SELECT * FROM {_repository.GetTableName<OrderProduct>()} WHERE OrderId = @orderId AND TenantId = @tenantId",
                        new { tenantId, orderId })).ToList();

                var existingOrderProductsArray = existingOrderProducts.ToArray();
                var addedProducts = new List<OrderProduct>();

                foreach (var product in order.Products)
                {
                    var existingOrderProduct = existingOrderProducts.FirstOrDefault(x => x.ProductId == product.ProductId);
                    product.OrderId = orderId;
                    product.TenantId = tenantId;

                    if (existingOrderProduct != null)
                    {
                        var existingOrderProductId = existingOrderProduct.Id;
                        existingOrderProducts.Remove(existingOrderProduct);
                        strBuilder.Append(_repository.GetUpdateQuery<OrderProduct, int>(tenantId, updatedBy, x => x.Id == existingOrderProductId, product, ref dynamicParameters));
                    }
                    else
                    {
                        addedProducts.Add(product);
                        strBuilder.Append(_repository.GetInsertQuery<OrderProduct, int>(tenantId, updatedBy, product, ref dynamicParameters));
                    }
                }

                foreach (var product in existingOrderProducts)
                {
                    strBuilder.Append(_repository.GetDeleteQuery<OrderProduct, int>(tenantId, product, ref dynamicParameters));
                }

                order.TenantId = tenantId;

                //Este es un caché para la columna del total
                order.Total = CalculateTotal(order).Total;

                strBuilder.Append(_repository.GetUpdateQuery<Order, int>(tenantId, updatedBy, x => x.Id == orderId, order, ref dynamicParameters));

                await db.QueryAsync(strBuilder.ToString(), dynamicParameters);

                await AdjustStockForExistingOrder(tenantId, db, orderId, order,
                    existingOrderProductsArray, //Productos existentes
                    existingOrderProducts.ToArray(), //Productos eliminados
                    addedProducts.ToArray()
                );


                if(isClient && order.TenantId == "test")
                    await _webHookService.TriggerWebHooks("test", "orders.updated", JsonConvert.SerializeObject(order));
                else 
                    await _webHookService.TriggerWebHooks(tenantId, "orders.updated", JsonConvert.SerializeObject(order));

                return "";
            });
        }

        public OrderCalculations CalculateTotal(Order order)
        {
            if(order == null)
            {
                return new OrderCalculations()
                {
                    Products = new ProductCalculations[] { },
                    Taxes = new TaxCalculations[] { },
                };
            }

            var hasAmountDiscount = false;
            var discountSum = 0M;
            var subtotal = 0M;
            var totalTaxes = 0M;
            var totalIva = 0M;
            var totalIeps = 0M;

            var products = new List<ProductCalculations>();

            var taxesDict = new Dictionary<string, decimal>();

            foreach (var product in order.Products.OrderBy(x => x.Product.Name))
            {
                var salePrice = (product.FixedPrice.HasValue && product.FixedPrice.Value > 0) ? product.FixedPrice.Value :
                    ((product.SalePrice.HasValue && product.SalePrice.Value > 0) ? product.SalePrice.Value : product.Product.SalePrice);

                var ivaPc = (product.Iva.HasValue && product.Iva.Value > 0) ? product.Iva.Value : (product.Product.Iva.HasValue ? product.Product.Iva.Value : 0);
                var iepsPc = (product.Ieps.HasValue && product.Ieps.Value > 0) ? product.Ieps.Value : (product.Product.Ieps.HasValue ? product.Product.Ieps.Value : 0);

                var productSubtotal = Math.Round(salePrice * product.Qty, 2, MidpointRounding.AwayFromZero);
                var productIva = Math.Round(productSubtotal * (ivaPc / 100M), 2, MidpointRounding.AwayFromZero);
                var productIeps = Math.Round(productSubtotal * (iepsPc / 100M), 2, MidpointRounding.AwayFromZero);

                if (ivaPc > 0)
                {
                    if (!taxesDict.ContainsKey("IVA_" + ivaPc))
                        taxesDict.Add("IVA_" + ivaPc, 0);

                    taxesDict["IVA_" + ivaPc] += productIva;
                }

                if (iepsPc > 0)
                {
                    if (!taxesDict.ContainsKey("IEPS_" + iepsPc))
                        taxesDict.Add("IEPS_" + iepsPc, 0);

                    taxesDict["IEPS_" + iepsPc] += productIeps;
                }

                totalTaxes += productIva + productIeps;
                totalIva += productIva;
                totalIeps += productIeps;

                subtotal += productSubtotal;

                decimal productDiscount = 0;

                if (order.Discount.HasValue)
                {
                    if (order.DiscountType == DiscountType.Percentage)
                    {
                        productDiscount = Math.Round(productSubtotal * (order.Discount.Value / 100M), 2, MidpointRounding.AwayFromZero);
                        discountSum += productDiscount;
                    }
                    else if(order.DiscountType == DiscountType.Amount)
                    {
                        productDiscount = -1;
                        hasAmountDiscount = true;
                    }
                }

                products.Add(new ProductCalculations()
                {
                    Product = product,
                    Price = salePrice,
                    Discount = productDiscount,
                    Subtotal = productSubtotal,
                    Iva = productIva,
                    IvaPc = ivaPc,
                    Ieps = productIeps,
                    IepsPc = iepsPc,
                    Taxes = productIva + productIeps
                });
            }


            if(hasAmountDiscount && order.Discount != null && order.Discount > 0)
            {
                foreach(var product in products)
                {
                    var pcPerProduct = product.Subtotal / subtotal;
                    var proportionalDiscount = Math.Round(pcPerProduct * order.Discount.Value, 2, MidpointRounding.AwayFromZero);
                    discountSum += proportionalDiscount;

                    product.Discount = proportionalDiscount;
                }
            }

            var discount =
                ((order.DiscountType == DiscountType.Amount)
                ? (order.Discount ?? 0M)
                : Math.Round((subtotal * ((order.Discount ?? 0M) / 100M)), 2, MidpointRounding.AwayFromZero));

            //Si la suma de los descuentos por producto es diferente al total real,
            //hay que emparejarlo quitando del último producto
            if (discountSum != discount)
            {
                var diff = discountSum - discount;

                //var spreadDiscount = Math.Round(diff / (decimal)products.Count, 2, MidpointRounding.AwayFromZero);
                //var spreadDiscountSum = 0M;

                //foreach(var product in products)
                //{
                //    spreadDiscountSum += spreadDiscount;
                //    product.Discount -= spreadDiscount;
                //}

                //products[0].Discount -= (diff - spreadDiscountSum);

                var productToAdd = products.OrderByDescending(x => x.Discount).FirstOrDefault();

                if (productToAdd != null) {
                    productToAdd.Discount -= diff;
                }
            }

            var total = subtotal - discount + totalTaxes;

            if (total < 0) total = 0;

            var taxes = taxesDict.Keys.Select(x => new TaxCalculations()
            {
                Type = x.Split("_")[0],
                Pc = Decimal.Parse(x.Split("_")[1]),
                Value = taxesDict[x]
            }).ToArray();

            //TODO
            //if (hasAmountDiscount)
            //{
            //    foreach (var product in products)
            //    {
            //        product.DescuentoSpecified = true;
            //        decimal pc = order.Discount.Value / comprobante.SubTotal;
            //        product.Descuento = TwoDecimals(product.Importe * pc);
            //        totalDiscounts += product.Descuento;
            //    }

            //    comprobante.Total = TwoDecimals(comprobante.SubTotal + taxTotal - totalDiscounts);
            //}

            return new OrderCalculations()
            {
                Total = total,
                TotalIeps = totalIeps,
                TotalIva = totalIva,
                Discount = discount,
                Subtotal = subtotal,
                Taxes = taxes,
                TotalTaxes = totalTaxes,
                Products = products.ToArray()
            };
        }

        public async Task<IEnumerable<Order>> GetOrdersByIds(int[] ids)
        {
            return await _repository.Query(async (db) =>
            {
                var query = $@"SELECT * FROM orders
LEFT JOIN clients ON clients.id = orders.clientid
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clienttaxpayers ON clienttaxpayers.id = clientbranches.taxpayerid
LEFT JOIN orderproducts ON orderproducts.orderId = orders.id
LEFT JOIN products ON products.id = orderproducts.productid
LEFT JOIN units ON units.id = products.unitId
WHERE orders.id IN ({string.Join(',', ids.Select(x => x.ToString()))}) AND orders.isactive = True";

                var orders = new List<Order>();

                await db.QueryAsync<Order, Client, ClientBranch, ClientTaxPayer, OrderProduct, Product, Unit, Order>(query,
                    (o, c, ob, otp, op, p, u) =>
                    {
                        if (!orders.Any(x => x.Id == o.Id))
                            orders.Add(o);

                        var order = orders.Single(x => x.Id == o.Id);

                        if (c != null) order.Client = c;

                        if (ob != null) order.Branch = ob;

                        order.Products = order.Products ?? new List<OrderProduct>();

                        if (p != null && op != null) op.Product = p;

                        if (op != null && op.Product != null && u != null)
                            op.Product.Unit = u;

                        if (order != null && order.Branch != null && otp != null)
                            order.Branch.TaxPayer = otp;

                        if (order != null && op != null)
                            order.Products.Add(op);

                        return order;

                    });

                var brandIds = orders.Select(x => x.Branch?.ClientBrandId).Where(x => x != null).ToArray().Distinct();

                var brandQueries = $"SELECT * FROM clientbrands WHERE id IN ({string.Join(',', brandIds.Select(x => x.ToString()))})";

                var brands = await db.QueryAsync<ClientBrand>(brandQueries);


                foreach(var order in orders)
                {
                    order.Branch.ClientBrand = brands.FirstOrDefault(x => x.Id == order.Branch.ClientBrandId);
                }

                return orders;

            });
        }

        public async Task<IEnumerable<Order>> GetOrdersByIds(string tenantId, int[] ids)
        {
            return await _repository.Query(async (db) =>
            {
                var query = $@"SELECT * FROM orders
LEFT JOIN clients ON clients.id = orders.clientid
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clienttaxpayers ON clienttaxpayers.id = clientbranches.taxpayerid
LEFT JOIN orderproducts ON orderproducts.orderId = orders.id
LEFT JOIN products ON products.id = orderproducts.productid
LEFT JOIN units ON units.id = products.unitId
WHERE orders.id IN ({string.Join(',', ids.Select(x => x.ToString()))}) AND orders.TenantId = @tenantId AND orders.isactive = True";

                var orders = new List<Order>();

                await db.QueryAsync<Order, Client, ClientBranch, ClientTaxPayer, OrderProduct, Product, Unit, Order>(query,
                    (o, c, ob, otp, op, p, u) =>
                    {
                        if (!orders.Any(x => x.Id == o.Id))
                            orders.Add(o);

                        var order = orders.Single(x => x.Id == o.Id);

                        if (c != null) order.Client = c;

                        if (ob != null) order.Branch = ob;

                        order.Products = order.Products ?? new List<OrderProduct>();

                        if (p != null && op != null) op.Product = p;

                        if (op != null && op.Product != null && u != null)
                            op.Product.Unit = u;

                        if (order != null && order.Branch != null && otp != null)
                            order.Branch.TaxPayer = otp;

                        if (order != null && op != null)
                            order.Products.Add(op);

                        return order;

                    }, new { tenantId });

                return orders;

            });
        }

        public async Task<OrderPayment> GetOrderPaymentById(string tenantId, int id)
        {
            //Pagos únicos
            return await _repository.Query(async (db) =>
            {
                var query = $@"SELECT orderpayments.*, orders.*, cfdis.* FROM orderpayments
LEFT JOIN orders ON orderpayments.orderid = orders.id
LEFT JOIN cfdis ON cfdis.id = orders.invoiceid
WHERE orderpayments.id = @id AND orderpayments.isactive = true AND orders.tenantid = @tenantId AND cfdis.tenantid = @tenantId AND orderpayments.tenantid = @tenantId";

                OrderPayment orderPayment = null;

                var orderPayments = await db.QueryAsync<OrderPayment, Order, Cfdi, OrderPayment>(query,
                    (op, o, c) =>
                    {
                        orderPayment = op;
                        orderPayment.Order = o;
                        orderPayment.Order.Invoice = c;

                        return orderPayment;

                    }, new { tenantId, id });

                if (orderPayment != null)
                    orderPayment.Order.Invoice.XmlAsString =
                           UTF8Encoding.UTF8.GetString(await _storageService.LoadFile(orderPayment.Order.Invoice.Uuid + ".xml", "cfdixml"));


                if (orderPayment == null)
                {
                    query = $@"select orderpayments.*, orderpaymentitems.*, orders.*, cfdis.* from orderpayments
LEFT JOIN orderpaymentitems ON orderpayments.id = orderpaymentitems.orderpaymentid
LEFT JOIN orders ON orders.id = orderpaymentitems.orderid
LEFT JOIN cfdis ON cfdis.id = orders.invoiceid
WHERE orderpayments.id = @id
AND orderpayments.isactive = true
AND orderpayments.tenantid = @tenantId
AND orderpaymentitems.tenantid = @tenantId 
AND orders.tenantid = @tenantId
AND cfdis.tenantid = @tenantId";

                    var orderPaymentItems = new List<OrderPaymentItem>();

                    orderPayments = await db.QueryAsync<OrderPayment, OrderPaymentItem, Order, Cfdi, OrderPayment>(query,
                    (op, opi, o, c) =>
                    {
                        if (orderPayment == null)
                        {
                            orderPayment = op;
                        }

                        opi.Order = o;

                        opi.Order.Invoice = c;
                        orderPaymentItems.Add(opi);
                        orderPayment.OrderPaymentItems = orderPaymentItems.ToArray();

                        return orderPayment;

                    }, new { tenantId, id });

                    foreach (var orderPaymentItem in orderPaymentItems)
                    {
                        orderPaymentItem.Order.Invoice.XmlAsString =
                                UTF8Encoding.UTF8.GetString(await _storageService.LoadFile(orderPaymentItem.Order.Invoice.Uuid + ".xml", "cfdixml"));
                    }

                }

                return orderPayment;

            });

        }

        public async Task<Order> GetOrderById(string tenantId, int userId, int id)
        {

            var lastAccessed = DateTime.UtcNow;
            var lastAccessedToString = lastAccessed.ToString("yyyy-MM-dd HH:mm:ss");

            return await _repository.Query(async (db) =>
            {
                var query = $@" 

SELECT * FROM orders
LEFT JOIN clients ON clients.id = orders.clientid
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clienttaxpayers ON clienttaxpayers.id = clientbranches.taxpayerid
LEFT JOIN orderproducts ON orderproducts.orderId = orders.id
LEFT JOIN products ON products.id = orderproducts.productid
LEFT JOIN units ON units.id = products.unitId
WHERE orders.id = @id AND orders.TenantId = @tenantId AND orders.isactive = True;
";

                if (userId > 0)
                {
                    //El acceso exclusivo expira en 5 minutos (con UpdateAccessTime se renueva el acceso exclusivo)
                    query = @$"UPDATE public.orders SET lastaccessed = '{lastAccessedToString}', accessedbyid = '{userId}'
WHERE id = @id AND tenantId = @tenantId AND (lastaccessed IS NULL OR 
(((DATE_PART('day', '{lastAccessedToString}'::timestamp - lastaccessed::timestamp) * 24 + 
DATE_PART('hour', '{lastAccessedToString}'::timestamp - lastaccessed::timestamp)) * 60 +
DATE_PART('minute', '{lastAccessedToString}'::timestamp - lastaccessed::timestamp)) > 5)); " + query;
                }

                Order order = null;

                var orders = await db.QueryAsync<Order, Client, ClientBranch, ClientTaxPayer, OrderProduct, Product, Unit, Order>(query,
                    (o, c, ob, otp, op, p, u) =>
                    {
                        if (order == null)
                            order = o;

                        if (c != null) order.Client = c;

                        if (ob != null) order.Branch = ob;

                        order.Products = order.Products ?? new List<OrderProduct>();

                        if (op != null)
                        {
                            if (p != null) op.Product = p;
                        }

                        if (op != null && op.Product != null && u != null)
                            op.Product.Unit = u;

                        if (order.Branch != null && otp != null)
                            order.Branch.TaxPayer = otp;

                        if (op != null)
                            order.Products.Add(op);

                        return order;
                    },

                    new { id, tenantId });

                if (order == null) return order;

                //order.Invoice.XmlAsString =
                //           UTF8Encoding.UTF8.GetString(await _storageService.LoadFile(order.Invoice.Uuid + ".xml", "cfdixml"));

                order.Branch.ClientBrand = await db.QueryFirstAsync<ClientBrand>($"SELECT * FROM clientbrands WHERE id = {order.Branch.ClientBrandId}");

                if (order.AccessedById != null)
                    order.AccessedBy =
                        (await db.QueryFirstOrDefaultAsync<string>($"SELECT CONCAT(firstname, ' ', lastname) FROM users WHERE id = {order.AccessedById}"))
                        ?? "Usuario no encontrado";

                return order;

            });

        }


        public async Task<Order> GetClientOrderById(string tenantId, int userId, int id)
        {

            var lastAccessed = DateTime.UtcNow;
            var lastAccessedToString = lastAccessed.ToString("yyyy-MM-dd HH:mm:ss");

            var order = await _repository.Query(async (db) =>
            {
                var query = $@" 

SELECT * FROM orders
LEFT JOIN clients ON clients.id = orders.clientid
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clienttaxpayers ON clienttaxpayers.id = clientbranches.taxpayerid
LEFT JOIN orderproducts ON orderproducts.orderId = orders.id
LEFT JOIN products ON products.id = orderproducts.productid
LEFT JOIN units ON units.id = products.unitId
WHERE orders.id = @id 
AND (orders.TenantId = @tenantId OR orders.clientid = (SELECT id from clients WHERE tenantId = @tenantId)) 
AND orders.isactive = True;
";

                if (userId > 0)
                {
                    //El acceso exclusivo expira en 5 minutos (con UpdateAccessTime se renueva el acceso exclusivo)
                    query = @$"UPDATE public.orders SET lastaccessed = '{lastAccessedToString}', accessedbyid = '{userId}'
WHERE id = @id AND tenantId = @tenantId AND (lastaccessed IS NULL OR 
(((DATE_PART('day', '{lastAccessedToString}'::timestamp - lastaccessed::timestamp) * 24 + 
DATE_PART('hour', '{lastAccessedToString}'::timestamp - lastaccessed::timestamp)) * 60 +
DATE_PART('minute', '{lastAccessedToString}'::timestamp - lastaccessed::timestamp)) > 5)); " + query;
                }

                Order order = null;

                var orders = await db.QueryAsync<Order, Client, ClientBranch, ClientTaxPayer, OrderProduct, Product, Unit, Order>(query,
                    (o, c, ob, otp, op, p, u) =>
                    {
                        if (order == null)
                            order = o;

                        if (c != null) order.Client = c;

                        if (ob != null) order.Branch = ob;

                        order.Products = order.Products ?? new List<OrderProduct>();

                        if (op != null)
                        {
                            if (p != null) op.Product = p;
                        }

                        if (op != null && op.Product != null && u != null)
                            op.Product.Unit = u;

                        if (order.Branch != null && otp != null)
                            order.Branch.TaxPayer = otp;

                        if (op != null)
                        {
                            if (!order.Products.Any(x => x.ProductId == op.ProductId))
                                order.Products.Add(op);
                        }

                        return order;
                    },

                    new { id, tenantId });

                if (order == null) return order;

                //order.Invoice.XmlAsString =
                //           UTF8Encoding.UTF8.GetString(await _storageService.LoadFile(order.Invoice.Uuid + ".xml", "cfdixml"));

                var now = DateTime.UtcNow;

                var settings = new Dictionary<string, dynamic>();

                foreach (var uniqueTenantId in order.Products.Select(x => x.Product.TenantId).Distinct())
                {
                    settings.Add(uniqueTenantId, new
                    {
                        calculateproductpriceexpirationcontinue = await _settingsService.GetSettingValue<bool>(uniqueTenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationContinue),
                        calculateproductpriceexpirationdeny = await _settingsService.GetSettingValue<bool>(uniqueTenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationDeny),
                        calculateproductpriceexpirationcontinue_days = await _settingsService.GetSettingValue<int>(uniqueTenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationContinueDays),
                        calculateproductpriceexpirationdeny_days = await _settingsService.GetSettingValue<int>(uniqueTenantId, ClientSettingType.CatalogsCalculateProductPriceExpirationDenyDays)
                    });
                }

                var distinctProductIds = order.Products.Select(x => x.ProductId).Distinct().ToList();

                var lastPricesQueries = new List<string>();

                foreach (var productId in distinctProductIds)
                {
                    lastPricesQueries.Add(@$"(SELECT productId, orderproducts.saleprice FROM orderproducts
INNER JOIN orders ON orderproducts.orderid = orders.id
LEFT JOIN products ON products.id = orderproducts.productId
WHERE productId = {productId} AND orders.deliverystatus = 2
ORDER BY orders.id DESC
LIMIT 1)
UNION
SELECT NULL as saleprice, NULL as productId;");
                }

                var lastPrices = new List<OrderProduct>();

                using (var multi = await db.QueryMultipleAsync(string.Join(" ", lastPricesQueries)))
                {
                    foreach (var product in order.Products)
                    {
                        lastPrices.Add(multi.Read<OrderProduct>().FirstOrDefault());
                    }
                }

                //                var lastPricesQuery = 
                //@$"SELECT productId, (CASE WHEN products.id = null THEN orderproducts.saleprice ELSE products.saleprice END) as saleprice FROM orderproducts
                //INNER JOIN orders ON orderproducts.orderid = orderproducts.orderid 
                //LEFT JOIN products ON products.id = orderproducts.productId
                //WHERE productId IN ({string.Join(",", distinctProductIds)}) AND orders.deliverystatus = 2
                //ORDER BY orderproducts.id DESC
                //LIMIT {distinctProductIds.Count}";

                //                var lastPrices = await db.QueryAsync<OrderProduct>(lastPricesQuery);

                foreach (var product in order.Products)
                {

                    product.Product.PriceExpirationStatus = "Vigente";

                    var productSettings = settings[product.Product.TenantId];


                    Console.WriteLine(JsonConvert.SerializeObject(product));

                    var priceDiff = lastPrices.Any(x => x.ProductId == product.ProductId)
                    ? (product.SalePrice ?? product.Product.SalePrice) - lastPrices.First(x => x.ProductId == product.ProductId).SalePrice as decimal? :
                    null;

                    if (priceDiff != null)
                    {
                        product.Product.PriceChange = (priceDiff > 0 ? "up" : (priceDiff < 0 ? "down" : "eq"));
                    }

                    if (product.Product.ContinueStockMode && productSettings.calculateproductpriceexpirationcontinue)
                        product.Product.PriceExpirationStatus = now - product.Product.CostModified > TimeSpan.FromDays(productSettings.calculateproductpriceexpirationcontinue_days) ? "No vigente" : "Vigente";

                    if (!product.Product.ContinueStockMode && productSettings.calculateproductpriceexpirationdeny)
                        product.Product.PriceExpirationStatus = now - product.Product.CostModified > TimeSpan.FromDays(productSettings.calculateproductpriceexpirationdeny_days) ? "No vigente" : "Vigente";
                }

                order.Branch.ClientBrand = await db.QueryFirstAsync<ClientBrand>($"SELECT * FROM clientbrands WHERE id = {order.Branch.ClientBrandId}");

                if (order.AccessedById != null)
                    order.AccessedBy =
                        (await db.QueryFirstOrDefaultAsync<string>($"SELECT CONCAT(firstname, ' ', lastname) FROM users WHERE id = {order.AccessedById}"))
                        ?? "Usuario no encontrado";

                return order;

            });

            order.AllowedDates = await GetAllowedDates(tenantId, order.Id);

            return order;
        }


        public async Task<IEnumerable<DistinctResult>> GetFilters(string tenantId, string type, string q, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus)
        {
            return await _repository.Query<IEnumerable<DistinctResult>>(async (db) =>
            {
                var filterExpression = GetFilterExpression(id, deliveryStatus, paymentStatus, total, client, branch, brand, deliveryDate, promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled, invoiceStatus);

                var where = "";
                var dynamicParams = _repository.GetWhereQueryFromExpression<Order, int>(tenantId, filterExpression, out where);

                if (where.Contains("orders.Branch.ClientBrandId.Value"))
                    where = where.Replace("orders.Branch.ClientBrandId.Value", "clientbrands.id");


                var fromQuery = @$"FROM public.orders
LEFT JOIN users AS cancellers ON cancellers.id = orders.canceledbyid
INNER JOIN users AS creators ON creators.id = orders.createdbyid
INNER JOIN users AS modifiers ON modifiers.id = orders.modifiedbyid
INNER JOIN clients ON clients.id = orders.clientid
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
LEFT JOIN clienttaxpayers ON clientbranches.taxpayerid = clienttaxpayers.id
LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
{where}";

                if (type == "branch")
                    return await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clientbranches.id AS value, public.clientbranches.name AS text {fromQuery}", dynamicParams);

                if (type == "client")
                    return await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clients.id AS value, public.clients.name AS text {fromQuery}", dynamicParams);

                if (type == "brand")
                    return await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clientbrands.id AS value, public.clientbrands.name AS text {fromQuery}", dynamicParams);

                throw new Exception("Invalid type");
            });
        }


        public async Task<FindResults<TableOrder>> GetOrders(string tenantId, string q, int skip, int take, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus)
        {
            return await _repository.Query(async (db) =>
            {
                var filterExpression = GetFilterExpression(id, deliveryStatus, paymentStatus, total, client, branch, brand, deliveryDate, promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled, invoiceStatus);

                if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
                    sortOrder = "DESC";

                var emptySortOrder = false;
                if (string.IsNullOrEmpty(sortBy))
                {
                    emptySortOrder = true;
                    sortBy = "deliveryDate";
                    sortOrder = "DESC";
                }

                sortOrder = sortOrder.ToUpper();

                if (sortOrder != "ASC" && sortOrder != "DESC")
                    sortOrder = "DESC";

                if (sortBy == "clientId" || sortBy == "client" || sortBy == "clientid") sortBy = "clients.name";
                if (sortBy == "brandId" || sortBy == "brand" || sortBy == "brandid") sortBy = "clientbrands.name";
                if (sortBy == "branchId" || sortBy == "branch" || sortBy == "branchid") sortBy = "clientbranches.name";

                if (sortBy == "id") sortBy = "orders.id";
                if (sortBy == "modifiedById") sortBy = "orders.modifiedbyid";
                if (sortBy == "modified") sortBy = "orders.modified";
                if (sortBy == "createdById") sortBy = "orders.createdbyid";
                if (sortBy == "created") sortBy = "orders.created";

                if (!(new[] { "orders.id", "orders.modifiedbyid", "orders.modified", "orders.createdbyid", "orders.created", "clients.name", "clientbrands.name", "clientbranches.name",
                    "deliveryDate", "total", "deliveryStatus", "paymentStatus", "promisedPaymentDate", "actualPaymentDate"}
                    .Contains(sortBy)))
                    sortBy = "orders.id";

                var where = "";
                var dynamicParams = _repository.GetWhereQueryFromExpression<Order, int>(tenantId, filterExpression, out where);

                if (where.Contains("orders.Branch.ClientBrandId.Value"))
                    where = where.Replace("orders.Branch.ClientBrandId.Value", "clientbrands.id");


                if (!string.IsNullOrEmpty(q))
                {
                    //where += $@" AND products.name ~* @term";
                    where += $@" AND orders.id IN (SELECT public.orders.id FROM public.orders
INNER JOIN public.orderproducts ON public.orderproducts.orderid = public.orders.id
INNER JOIN public.products ON public.products.id = public.orderproducts.productid
WHERE public.products.name ~* @term)";
                    dynamicParams.Add("term", q.PrepareQuery());
                }

                var fromQuery = @$"FROM public.orders
LEFT JOIN users AS cancellers ON cancellers.id = orders.canceledbyid
INNER JOIN users AS creators ON creators.id = orders.createdbyid
INNER JOIN users AS modifiers ON modifiers.id = orders.modifiedbyid
INNER JOIN clients ON clients.id = orders.clientid
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
LEFT JOIN clienttaxpayers ON clientbranches.taxpayerid = clienttaxpayers.id
LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
{where}";

                var sorting = $"{sortBy} {sortOrder}";

                if (emptySortOrder)
                {
                    sorting = "orders.deliveryDate DESC, orders.numfolio DESC";
                }

                var query = $@"
SELECT
orders.id, orders.numfolio, clients.name as clientname, orders.deliverydate,
orders.deliverystatus, orders.invoiceid, orders.paymentstatus, orders.promisedpaymentdate,
orders.actualpaymentdate, orders.canceledById, orders.created, orders.modified,
orders.discount, orders.discounttype,
clients.name as clientname,
clients.isverified as isverified,
clientbranches.name as branchname, clientbrands.name as brandname,
clienttaxpayers.taxpayeridno,
clienttaxpayers.paymentmethod as taxPayerPaymentMethod,
clienttaxpayers.paymentform as taxPayerPaymentForm,
clienttaxpayers.usocfdi as taxPayerUsoCfdi,
orders.isactive,
CONCAT(creators.firstname, ' ', creators.lastname) as createdByName,
CONCAT(modifiers.firstname, ' ', modifiers.lastname) as modifiedByName,
CONCAT(cancellers.firstname, ' ', cancellers.lastname) as canceledByName,
orders.total,
COUNT(*) OVER() as totalrows
{fromQuery}
GROUP BY orders.id, orders.numfolio, clientname, isverified, deliverydate, total, deliverystatus, invoiceid,
paymentstatus, promisedpaymentdate, actualpaymentdate, orders.canceledById, orders.created, orders.modified,
clientname, branchname, brandname, taxpayeridno, taxPayerPaymentMethod, taxPayerPaymentForm, taxPayerUsoCfdi,
orders.isactive, createdByName, modifiedByName, canceledByName
ORDER BY {sorting}
LIMIT {take}
OFFSET {skip}
";

                var orders = await db.QueryAsync<TableOrder>(query, dynamicParams);

                ///////////////////
                //Esto es para refrescar la columna del total (caché)
                var ordersToUpdateIds = orders.Where(x => x.DeliveryStatus != DeliveryStatus.InRoute 
                    && x.DeliveryStatus != DeliveryStatus.Delivered).Select(x => x.Id).ToArray();

                var ordersToUpdate = new List<Order>();

                if (ordersToUpdateIds.Length > 0)
                {
                    await db.QueryAsync<Order, OrderProduct, Product, Order>($@"
SELECT orders.id, orders.discount, orders.discounttype, 
orderproducts.id, orderproducts.qty, orderproducts.fixedprice, orderproducts.suppliercost, 
orderproducts.saleprice, orderproducts.iva, orderproducts.ieps, 
products.id, products.saleprice, products.iva, products.ieps 
FROM orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
WHERE orders.id IN ({string.Join(", ", ordersToUpdateIds.Select(x => x.ToString()))})"
, (o, op, p) =>
{
    if (!ordersToUpdate.Any(x => x.Id == o.Id))
        ordersToUpdate.Add(o);

    var order = ordersToUpdate.Single(x => x.Id == o.Id);

    order.Products = order.Products ?? new List<OrderProduct>();

    op.Product = p;
    order.Products.Add(op);

    return order;

});

                    var updateQueryBuilder = new List<string>();
                    foreach(var orderToUpdate in ordersToUpdate) {
                        
                        var tableOrder = orders.FirstOrDefault(x => x.Id == orderToUpdate.Id);
                        if (tableOrder != null)
                        {
                            var total = CalculateTotal(orderToUpdate);

                            if (total.Total != tableOrder.Total)
                            {
                                tableOrder.Total = total.Total;
                                updateQueryBuilder.Add($"UPDATE orders SET total = '{total.Total}' WHERE id = {orderToUpdate.Id}; ");
                            }
                        }
                    }

                    if(updateQueryBuilder.Count > 0)
                        await db.QueryAsync<Order>(string.Join(" ", updateQueryBuilder));

                }
                ///////////////////

                var totalrows = orders.FirstOrDefault()?.TotalRows ?? 0;

                var folios = new List<OrderIdAndFolio>();

                if (orders.Count() > 0)
                {
                    query = $"SELECT orders.id as OrderId, cfdis.folio as Folio FROM cfdis, orders WHERE cfdis.id = orders.invoiceid AND orders.id IN ({string.Join(',', orders.Select(x => x.Id).Select(x => x.ToString()))}) AND orders.TenantId = @tenantId";
                    folios = (await db.QueryAsync<OrderIdAndFolio>(query, new { tenantId })).ToList();
                }

                foreach (var order in orders)
                {
                    order.CanceledByName = order.CanceledByName == " " ? null : order.CanceledByName;
                    order.InvoiceStatus = folios.FirstOrDefault(x => x.OrderId == order.Id)?.Folio
                        ?? (order.InvoiceId == null ? null : order.Folio);
                }

                //var totalSumQuery = @$"SELECT SUM(public.orders.total) {fromQuery}";
                var totalSumQuery = @$"
SELECT SUM(total) as sum FROM (SELECT
orders.id, 
orders.total
FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
{where}
GROUP BY orders.id) as results
";


                return new FindResults<TableOrder>()
                {
                    Results = orders.ToArray(),
                    CustomData = new
                    {
                        total = totalrows,
                        sum = totalrows > 0 ? (await db.QueryFirstOrDefaultAsync<decimal>(totalSumQuery, dynamicParams)) : 0M,
                        requestedOrders = await db.QueryFirstOrDefaultAsync<int>("SELECT COUNT(*) FROM public.orders WHERE deliveryStatus = 3 AND canceledbyid IS NULL AND tenantId = @tenantId", new { tenantId })
                    },
                    //Filters = new
                    //{
                    //    branch = totalrows > 0 ? await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clientbranches.id AS value, public.clientbranches.name AS text {fromQuery}", dynamicParams) : new List<DistinctResult>(),
                    //    client = totalrows > 0 ? await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clients.id AS value, public.clients.name AS text {fromQuery}", dynamicParams) : new List<DistinctResult>(),
                    //    brand = totalrows > 0 ? await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clientbrands.id AS value, public.clientbrands.name AS text {fromQuery}", dynamicParams) : new List<DistinctResult>()
                    //},
                    Total = totalrows
                };
            });
        }

        public async Task<IEnumerable<DistinctResult>> GetClientFilters(string tenantId, string type, string q, int? id, 
            string deliveryStatus, string paymentStatus, decimal? total, string client, 
            string branch, string brand, string deliveryDate, string promisedPaymentDate, 
            string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus, 
            string name, string legalName)
        {
            return await _repository.Query<IEnumerable<DistinctResult>>(async (db) =>
            {
                var whereQuery = GetFilterExpressionAsString(id, deliveryStatus, paymentStatus, total, client, branch, brand,
                    deliveryDate, promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled, invoiceStatus, name, legalName);

                var fromQuery = @$"FROM orders
LEFT JOIN users AS cancellers ON cancellers.id = orders.canceledbyid
INNER JOIN users AS creators ON creators.id = orders.createdbyid
INNER JOIN users AS modifiers ON modifiers.id = orders.modifiedbyid
LEFT JOIN client_suppliers ON orders.tenantId = client_suppliers.supplier_tenantId
LEFT JOIN suppliers ON suppliers.tenantId = client_suppliers.supplier_tenantId
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbranches.clientbrandid = clientbrands.id
LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
WHERE orders.clientid = (SELECT id from clients WHERE tenantid = @tenantId)
AND (client_tenantid = @tenantId OR orders.tenantid = @tenantId)
{(includeCancelled == 1 ? ("") : ("AND orders.canceledbyid IS NULL"))}
{(!string.IsNullOrEmpty(whereQuery) ? ("AND " + whereQuery) : "")}";

                if (type == "branch")
                    return await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clientbranches.id AS value, public.clientbranches.name AS text {fromQuery}", new { tenantId });

                if (type == "client")
                    return await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clients.id AS value, public.clients.name AS text {fromQuery}", new { tenantId });

                if (type == "brand")
                    return await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.clientbrands.id AS value, public.clientbrands.name AS text {fromQuery}", new { tenantId });

                if (type == "legalName")
                {
                    //Este query sólo trae los proveedores verificados, no los locales
                    var results = await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.suppliers.id AS value, public.suppliers.legalname AS text {fromQuery}", new { tenantId });
                    var localSupplierIds = (await db.QueryAsync<int?>($"SELECT DISTINCT public.orders.supplierid {fromQuery}", new { tenantId })).Where(x => x != null).ToArray();

                    var distinctLocalSupplierIds = new List<int>();
                    var currentIds = results.Where(x => x.Value != null).Select(x => (int)x.Value).ToArray();

                    foreach (var id in localSupplierIds)
                    {
                        if(id.HasValue && !currentIds.Contains(id.Value))
                            distinctLocalSupplierIds.Add(id.Value);
                    }

                    distinctLocalSupplierIds = distinctLocalSupplierIds.Distinct().ToList();

                    if (distinctLocalSupplierIds.Count > 0) {
                        var localSupplierResults = await db.QueryAsync<DistinctResult>($"SELECT id as value, legalName as text FROM suppliers WHERE id IN ({string.Join(',', distinctLocalSupplierIds.Select(x => x.ToString()))})");
                        return results.Where(x => x.Value != null).Concat(localSupplierResults);
                    }
                    else
                        return results.Where(x => x.Value != null);
                }

                if (type == "name")
                {
                    var results = await db.QueryAsync<DistinctResult>($"SELECT DISTINCT public.suppliers.id AS value, public.suppliers.name AS text {fromQuery}", new { tenantId });
                    var localSupplierIds = (await db.QueryAsync<int?>($"SELECT DISTINCT public.orders.supplierid {fromQuery}", new { tenantId })).Where(x => x != null).ToArray();

                    var distinctLocalSupplierIds = new List<int>();
                    var currentIds = results.Where(x => x.Value != null).Select(x => (int)x.Value).ToArray();

                    foreach (var id in localSupplierIds)
                    {
                        if (id.HasValue && !currentIds.Contains(id.Value))
                            distinctLocalSupplierIds.Add(id.Value);
                    }

                    distinctLocalSupplierIds = distinctLocalSupplierIds.Distinct().ToList();

                    if (distinctLocalSupplierIds.Count > 0)
                    {
                        var localSupplierResults = await db.QueryAsync<DistinctResult>($"SELECT id as value, name as text FROM suppliers WHERE id IN ({string.Join(',', distinctLocalSupplierIds.Select(x => x.ToString()))})");
                        return results.Where(x => x.Value != null).Concat(localSupplierResults);
                    }
                    else
                        return results.Where(x => x.Value != null);
                }

                throw new Exception("Invalid type");
            });
        }

        public async Task<FindResults<TableClientOrder>> GetClientOrders(string tenantId, string q, int skip, int take, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus, string name, string legalName)
        {
            return await _repository.Query(async (db) =>
            {
                if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
                    sortOrder = "DESC";

                var emptySortOrder = false;
                if (string.IsNullOrEmpty(sortBy))
                {
                    emptySortOrder = true;
                }

                sortOrder = sortOrder.ToUpper();

                if (sortOrder != "ASC" && sortOrder != "DESC")
                    sortOrder = "DESC";

                if (sortBy == "clientId" || sortBy == "client" || sortBy == "clientid") sortBy = "clients.name";
                if (sortBy == "brandId" || sortBy == "brand" || sortBy == "brandid") sortBy = "clientbrands.name";
                if (sortBy == "branchId" || sortBy == "branch" || sortBy == "branchid") sortBy = "clientbranches.name";

                if (sortBy == "id") sortBy = "orders.id";
                if (sortBy == "modifiedById") sortBy = "orders.modifiedbyid";
                if (sortBy == "modified") sortBy = "orders.modified";
                if (sortBy == "createdById") sortBy = "orders.createdbyid";
                if (sortBy == "created") sortBy = "orders.created";

                if (!(new[] { "orders.id", "orders.modifiedbyid", "orders.modified", "orders.createdbyid", "orders.created", "clients.name", "clientbrands.name", "clientbranches.name",
                    "deliveryDate", "total", "deliveryStatus", "paymentStatus", "promisedPaymentDate", "actualPaymentDate"}
                    .Contains(sortBy)))
                    sortBy = "orders.id";

                var whereQuery = GetFilterExpressionAsString(id, deliveryStatus, paymentStatus, total, client, branch, brand, 
                    deliveryDate, promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled, invoiceStatus, name, legalName);

                var sorting = $"{sortBy} {sortOrder}";

                if (emptySortOrder)
                {
                    sorting = "orders.deliveryDate DESC, orders.numfolio DESC";
                }

                //orders.suppliername, orders.supplierlegalname,

                var query = $@"SELECT orders.id, numfolio, deliverydate, total, clientbranches.name as branch, clientbrands.name as brand,
suppliers.name as name, suppliers.legalname as legalname,
deliveryStatus, paymentStatus, promisedpaymentdate, actualpaymentdate, orders.invoiceId, orders.created, orders.createdbyid, 
orders.modified, orders.modifiedbyid, orders.isactive, suppliers.isverified,
CONCAT(creators.firstname, ' ', creators.lastname) as createdByName,
CONCAT(modifiers.firstname, ' ', modifiers.lastname) as modifiedByName,
CONCAT(cancellers.firstname, ' ', cancellers.lastname) as canceledByName,
(SELECT name FROM suppliers WHERE id = supplierid) as suppliername,
(SELECT legalname FROM suppliers WHERE id = supplierid) as supplierlegalname,
creators.tenantId as createdByTenantId,
modifiers.tenantId as modifiedByTenantId,
cancellers.tenantId as canceledTenantId,
orders.tenantId,
orders.total,
COUNT(*) OVER() as totalrows
FROM orders
LEFT JOIN users AS cancellers ON cancellers.id = orders.canceledbyid
INNER JOIN users AS creators ON creators.id = orders.createdbyid
INNER JOIN users AS modifiers ON modifiers.id = orders.modifiedbyid
LEFT JOIN client_suppliers ON orders.tenantId = client_suppliers.supplier_tenantId
LEFT JOIN suppliers ON suppliers.tenantId = client_suppliers.supplier_tenantId
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbranches.clientbrandid = clientbrands.id
LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id

WHERE orders.clientid = (SELECT id from clients WHERE tenantid = @tenantId)
AND (client_tenantid = @tenantId OR orders.tenantid = @tenantId)
{(includeCancelled == 1 ? ("") : ("AND orders.canceledbyid IS NULL"))}
{(!string.IsNullOrEmpty(whereQuery) ? ("AND " + whereQuery) : "")}
GROUP BY orders.id, orders.numfolio, deliverydate, total, clientbranches.name, clientbrands.name, suppliers.name,
suppliers.legalname, deliveryStatus, paymentStatus, promisedpaymentdate, actualpaymentdate, orders.created,
orders.createdbyid, orders.modified, orders.modifiedbyid, orders.isactive, suppliers.isverified,
createdByName, modifiedByName, canceledByName, createdByTenantId, modifiedByTenantId, canceledTenantId,
orders.tenantId
ORDER BY {sorting}
LIMIT {take}
OFFSET {skip}";

//                var countQuery = $@"SELECT COUNT(*) FROM orders
//LEFT JOIN client_suppliers ON orders.tenantId = client_suppliers.supplier_tenantId
//LEFT JOIN suppliers ON suppliers.tenantId = client_suppliers.supplier_tenantId
//LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
//LEFT JOIN clientbrands ON clientbranches.clientbrandid = clientbrands.id
//LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
//WHERE orders.clientid = (SELECT id from clients WHERE tenantid = @tenantId)
//AND (client_tenantid = @tenantId OR orders.tenantid = @tenantId)
//{(includeCancelled == 1 ? ("") : ("AND orders.canceledbyid IS NULL"))}
//{(!string.IsNullOrEmpty(whereQuery) ? ("AND " + whereQuery) : "")}";

                //string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus
                var orders = (await db.QueryAsync<TableClientOrder>(query, new { tenantId, sortBy, paymentStatus, total })).ToList();
                //var count = await db.QueryFirstAsync<int>(countQuery, new { tenantId, sortBy, paymentStatus, total });

                ///////////////////
                //Esto es para refrescar la columna del total (caché)
                var ordersToUpdateIds = orders.Where(x => x.DeliveryStatus != DeliveryStatus.InRoute
                    && x.DeliveryStatus != DeliveryStatus.Delivered).Select(x => x.Id).ToArray();

                var ordersToUpdate = new List<Order>();

                if (ordersToUpdateIds.Length > 0)
                {
                    await db.QueryAsync<Order, OrderProduct, Product, Order>($@"
SELECT orders.id, orders.discount, orders.discounttype, 
orderproducts.id, orderproducts.qty, orderproducts.fixedprice, orderproducts.suppliercost, 
orderproducts.saleprice, orderproducts.iva, orderproducts.ieps, 
products.id, products.saleprice, products.iva, products.ieps 
FROM orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
WHERE orders.id IN ({string.Join(", ", ordersToUpdateIds.Select(x => x.ToString()))})"
, (o, op, p) =>
{
    if (!ordersToUpdate.Any(x => x.Id == o.Id))
        ordersToUpdate.Add(o);

    var order = ordersToUpdate.Single(x => x.Id == o.Id);

    order.Products = order.Products ?? new List<OrderProduct>();

    op.Product = p;
    order.Products.Add(op);

    return order;

});

                    var updateQueryBuilder = new List<string>();
                    foreach (var orderToUpdate in ordersToUpdate)
                    {

                        var tableOrder = orders.FirstOrDefault(x => x.Id == orderToUpdate.Id);
                        if (tableOrder != null)
                        {
                            var total = CalculateTotal(orderToUpdate);

                            if (total.Total != tableOrder.Total)
                            {
                                tableOrder.Total = total.Total;
                                updateQueryBuilder.Add($"UPDATE orders SET total = '{total.Total}' WHERE id = {orderToUpdate.Id}; ");
                            }
                        }
                    }

                    if (updateQueryBuilder.Count > 0)
                        await db.QueryAsync<Order>(string.Join(" ", updateQueryBuilder));

                }
                ///////////////////


                var totalrows = orders.FirstOrDefault()?.TotalRows ?? 0;

                foreach (var order in orders)
                {
                    order.LegalName = string.IsNullOrEmpty(order.LegalName) ? order.SupplierLegalName : order.LegalName;
                    order.Name = string.IsNullOrEmpty(order.Name) ? order.SupplierName : order.Name;
                    order.CanceledByName = order.CanceledByName == " " ? null : order.CanceledByName;

                    if (order.CreatedByTenantId != tenantId && !string.IsNullOrEmpty(order.CreatedByName.Trim()))
                    {
                        order.CreatedByName = "Usuario Externo";
                    }
                    if (order.ModifiedByTenantId != tenantId && !string.IsNullOrEmpty(order.ModifiedByName.Trim()))
                    {
                        order.ModifiedByName = "Usuario Externo";
                    }
                    if (order.CanceledTenantId != tenantId && order.CanceledById != null)
                    {
                        order.CanceledByName = "Usuario Externo";
                    }

                    order.ModifiedByTenantId = "";
                    order.CreatedByTenantId = "";
                    order.CanceledTenantId = "";
                }


                var folios = new List<OrderIdAndFolio>();

                try
                {
                    query = $"SELECT orders.id as OrderId, cfdis.folio as Folio FROM cfdis, orders WHERE cfdis.id = orders.invoiceid AND orders.id IN ({string.Join(',', orders.Select(x => x.Id).Select(x => x.ToString()))}) AND orders.TenantId = @tenantId";
                    folios = (await db.QueryAsync<OrderIdAndFolio>(query, new { tenantId })).ToList();
                }
                catch
                {

                }

                foreach (var order in orders)
                {
                    order.InvoiceStatus = folios.FirstOrDefault(x => x.OrderId == order.Id)?.Folio
                        ?? (order.InvoiceId == null ? null : order.Folio);
                }

                var totalSumQuery = @$"
SELECT SUM(total) as sum FROM (

SELECT orders.id, orders.total
FROM orders
LEFT JOIN users AS cancellers ON cancellers.id = orders.canceledbyid
INNER JOIN users AS creators ON creators.id = orders.createdbyid
INNER JOIN users AS modifiers ON modifiers.id = orders.modifiedbyid
LEFT JOIN client_suppliers ON orders.tenantId = client_suppliers.supplier_tenantId
LEFT JOIN suppliers ON suppliers.tenantId = client_suppliers.supplier_tenantId
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbranches.clientbrandid = clientbrands.id
LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
WHERE orders.clientid = (SELECT id from clients WHERE tenantid = @tenantId)
AND (client_tenantid = @tenantId OR orders.tenantid = @tenantId)
{(includeCancelled == 1 ? ("") : ("AND orders.canceledbyid IS NULL"))}
{(!string.IsNullOrEmpty(whereQuery) ? ("AND " + whereQuery) : "")}
GROUP BY orders.id

) as results
";

                return new FindResults<TableClientOrder>()
                {
                    Results = orders.ToArray(),
                    CustomData = new
                    {
                        total = totalrows,
                        sum = totalrows > 0 ? (await db.QueryFirstOrDefaultAsync<decimal>(totalSumQuery, new { tenantId, sortBy, paymentStatus, total })) : 0M,
                    },
                    Total = totalrows
                };
            });
        }


        //public async Task<FindResults<Order>> GetOrders(string tenantId, string q, int skip, int take, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled)
        //{
        //    var filterExpression = GetFilterExpression(id, deliveryStatus, paymentStatus, total, client, branch, brand, deliveryDate, promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled);

        //    if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
        //        sortOrder = "DESC";

        //    if (string.IsNullOrEmpty(sortBy))
        //    {
        //        sortBy = "deliveryDate";
        //        sortOrder = "DESC";
        //    }

        //    if (sortBy == "clientId" || sortBy == "client" || sortBy == "clientid") sortBy = "clients.name";
        //    if (sortBy == "brandId" || sortBy == "brand" || sortBy == "brandid") sortBy = "clientbrands.name";
        //    if (sortBy == "branchId" || sortBy == "branch" || sortBy == "branchid") sortBy = "clientbranches.name";

        //    var where = "";
        //    var dynamicParams = _repository.GetWhereQueryFromExpression<Order, int>(tenantId, filterExpression, out where);

        //    var joins = @" LEFT JOIN users AS u ON u.id = orders.createdbyid
        //    LEFT JOIN users AS u2 ON u2.id = orders.modifiedbyid
        //    LEFT JOIN clients ON clients.id = orders.clientid
        //    LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
        //    LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
        //    LEFT JOIN clienttaxpayers ON clientbranches.taxpayerid = clienttaxpayers.id";

        //    if (!string.IsNullOrEmpty(q))
        //    {
        //        joins += @"
        //    LEFT JOIN orderproducts ON orderproducts.orderid = orders.id
        //    LEFT JOIN products ON orderproducts.productid = products.id";
        //        where += " AND products.name ~* @term";
        //        dynamicParams.Add("term", q.PrepareQuery());
        //    }

        //    if (where.Contains("orders.Branch.ClientBrandId.Value"))
        //        where = where.Replace("orders.Branch.ClientBrandId.Value", "clientbrands.id");

        //    var selectQuery = @$"SELECT {(!string.IsNullOrEmpty(q) ? "DISTINCT" : "")} orders.*, u.*, u2.*, clients.*, clientbranches.*, clientbrands.*, clienttaxpayers.* FROM public.orders {joins} {where}";

        //    return await _repository.CustomFind<Order, int>(tenantId,
        //        selectQuery,
        //        $"SELECT {(!string.IsNullOrEmpty(q) ? "DISTINCT" : "")} COUNT(*) as total, SUM(total) as sum FROM public.orders {joins} {where}", dynamicParams,
        //    skip, take, sortBy, sortOrder,
        //    (async (db, query, dparams) =>
        //    {
        //        var orders = await db.QueryAsync<Order, User, User, Client, ClientBranch, ClientBrand, ClientTaxPayer, Order>
        //            (query, (o, u, u2, cl, br, brnd, ctp) =>
        //            {
        //                if (u != null)
        //                    o.CreatedBy = new UserData() { Email = u.Email, FirstName = u.FirstName, LastName = u.LastName };

        //                if (u2 != null)
        //                    o.ModifiedBy = new UserData() { Email = u2.Email, FirstName = u2.FirstName, LastName = u2.LastName };

        //                if (cl != null)
        //                    o.Client = cl;

        //                if (br != null)
        //                {

        //                    o.Branch = br;
        //                    o.Branch.TaxPayer = ctp;
        //                }

        //                if (brnd != null && o.Branch != null)
        //                    o.Branch.ClientBrand = brnd;


        //                return o;

        //            }, dparams);


        //        if (orders.Any(x => x.CanceledById.HasValue))
        //        {
        //            var userIds = orders.Where(x => x.CanceledById.HasValue).Select(x => x.CanceledById.Value).ToArray();
        //            var cancelUsers = await db.QueryAsync<User>("SELECT * FROM users WHERE id = ANY(@userIds) AND TenantId = @tenantId", new { tenantId, userIds });

        //            foreach (var order in orders)
        //            {
        //                if (order.CanceledById.HasValue)
        //                {
        //                    order.CanceledBy = cancelUsers.FirstOrDefault(x => x.Id == order.CanceledById);
        //                }
        //            }

        //        }

        //        var folios = new List<OrderIdAndFolio>();

        //        try
        //        {
        //            query = $"SELECT orders.id as OrderId, cfdis.folio as Folio FROM cfdis, orders WHERE cfdis.id = orders.invoiceid AND orders.id IN ({string.Join(',', orders.Select(x => x.Id).Select(x => x.ToString()))}) AND orders.TenantId = @tenantId";

        //            folios = (await db.QueryAsync<OrderIdAndFolio>(query, new { tenantId })).ToList();
        //        }
        //        catch
        //        {

        //        }

        //        foreach (var order in orders)
        //        {
        //            order.InvoiceStatus = folios.FirstOrDefault(x => x.OrderId == order.Id)?.Folio
        //                ?? (order.InvoiceId == null ? null : order.Folio);
        //        }

        //        return orders;
        //    }),
        //    (async (db, query, dparams) =>
        //    {
        //        return await db.QueryFirstAsync<dynamic>(query, dparams);
        //    }));

        //}

        public async Task<GetOrderPaymentsResponse> GetOrderPayments(string tenantId, int orderId)
        {
            var totals = await GetOrderAndOrderPaymentsTotal(tenantId, orderId);
            var paidInMultiPayment = false;
            var orderIds = new int[] { };

            int? paymentCfdiId = null;

            var payments = (totals.OrderPaymentsTotal ?? 0) == 0
                ? new List<OrderPayment>() : (await _repository.Query(async (db) =>
                {
                    var orderPayments = await db.QueryAsync<OrderPayment>(
                        "SELECT * FROM orderpayments WHERE IsActive = true AND OrderId = @orderId AND TenantId = @tenantId ORDER BY PaymentDate DESC", new { tenantId, orderId });

                    paymentCfdiId = (await db.QueryFirstOrDefaultAsync<int?>(
                        "SELECT paymentid FROM orderpayments WHERE IsActive = true AND orderid = @orderId AND tenantid = @tenantId;", new { tenantId, orderId }));

                    if (paymentCfdiId == null)
                    {
                        paymentCfdiId = (await db.QueryFirstOrDefaultAsync<int?>(
                            "SELECT paymentid FROM orderpayments where IsActive = true AND tenantid = @tenantid AND id = (SELECT orderpaymentid FROM orderpaymentitems WHERE tenantid = @tenantId AND orderid = @orderId AND isactive = true);"
                            , new { tenantId, orderId }));
                    }

                    if (orderPayments.Count() == 0)
                    {
                        paidInMultiPayment = true;

                        var multiQuery = await db.QueryMultipleAsync($@"SELECT orderpayments.* FROM orderpaymentitems 
LEFT JOIN orderpayments ON orderpayments.id = orderpaymentitems.orderpaymentid WHERE orderpaymentitems.orderid = @orderId 
AND orderpaymentitems.tenantid = @tenantId AND orderpaymentitems.isactive = true AND orderpayments.isactive = true AND orderpayments.tenantId = @tenantId;

SELECT orderid FROM orderpaymentitems WHERE isactive = true AND orderpaymentid = (SELECT orderpaymentid FROM orderpaymentitems WHERE orderid = @orderId AND isactive = true);
",

new { tenantId, orderId });

                        orderPayments = await multiQuery.ReadAsync<OrderPayment>();
                        orderIds = (await multiQuery.ReadAsync<int>()).ToArray();
                    }

                    return orderPayments;

                }));

            var orderTotal = totals.OrderTotal ?? 0;
            var orderPaymentsTotal = totals.OrderPaymentsTotal ?? 0;

            var cfdis = orderIds.Length > 1 ? await _repository.Query<IEnumerable<Cfdi>>(async (db) =>
            {
                var q = $"SELECT id, formapago, metodopago from cfdis WHERE tenantid = @tenantId AND id IN (select invoiceid FROM orders WHERE tenantid = @tenantId AND id IN ({(string.Join(",", orderIds.Select(x => x.ToString())))}))";
                return await db.QueryAsync<Cfdi>(q, new { orderIds, tenantId });
            }) : await _repository.Query<IEnumerable<Cfdi>>(async (db) =>
            {
                var q = $"SELECT id, formapago, metodopago from cfdis WHERE tenantid = @tenantId AND id IN (select invoiceid FROM orders WHERE tenantid = @tenantId AND id = {orderId})";
                return await db.QueryAsync<Cfdi>(q, new { orderId, tenantId });
            });

            return new GetOrderPaymentsResponse()
            {
                LeftToPay = orderTotal - orderPaymentsTotal,
                LastPaymentDate = payments.FirstOrDefault()?.PaymentDate,
                OrderTotal = orderTotal,
                OrderIds = orderIds,
                PaidInMultiPayment = paidInMultiPayment,
                OrderPaymentsTotal = orderPaymentsTotal,
                CanGenerateCfdi = cfdis.Count() > 0 && cfdis.All(x => x.CanAddPayment),
                PaymentCfdiId = paymentCfdiId,
                IsPaymentComplete = totals.OrderPaymentsTotal >= totals.OrderTotal && totals.OrderPaymentsTotal > 0,
                Payments = payments
            };
        }

        public async Task<GetOrderPaymentsResponse> GetOrdersTotal(string tenantId, int[] orderIds, OrdersFilterQuery filterQuery)
        {
            if (orderIds.Length == 0)
            {
                var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                orderIds = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            var cfdis = await _repository.Query<IEnumerable<Cfdi>>(async (db) =>
            {
                var q = $"SELECT id, formapago, metodopago from cfdis WHERE tenantid = @tenantId AND id IN (select invoiceid FROM orders WHERE paymentstatus = 0 AND tenantid = @tenantId AND id IN ({(string.Join(",", orderIds.Select(x => x.ToString())))}))";
                return await db.QueryAsync<Cfdi>(q, new { orderIds, tenantId });
            });

            //if (cfdis.Count() == 0)
            //{
            //    throw new UserFriendlyException("Uno o más de los pedidos ya se encuentran pagados o pagados parcialmente.");
            //}

            return new GetOrderPaymentsResponse()
            {
                IsPaymentComplete = true,
                OrderIds = orderIds,
                CanGenerateCfdi = cfdis.Count() > 0 && cfdis.All(x => x.CanAddPayment),
                OrderTotal = await CalculateGetOrdersTotal(tenantId, orderIds)
            };
        }

        public async Task<MemoryStream> GetExcelFromData(OrderProduct[] products, int offset)
        {
            var flattenedProducts = products.Select(orderProduct =>
            {
                var iva = orderProduct.Iva ?? orderProduct.Product.Iva ?? 0;
                var ieps = orderProduct.Ieps ?? orderProduct.Product.Ieps ?? 0;
                var salePrice = orderProduct.FixedPrice ?? orderProduct.SalePrice ?? orderProduct.Product.SalePrice;

                var salePriceWithTaxes = RoundDecimal(salePrice + (salePrice * (iva / 100M)) + (salePrice * (ieps / 100M)));

                return new UserOrderProduct()
                {
                    Name = orderProduct.Product.Name,
                    Qty = orderProduct.Qty,
                    //precio con impuestos
                    FixedPrice = salePriceWithTaxes,
                    SalePrice =salePrice,
                    Unit = orderProduct.Product.Unit,
                    UnitId = orderProduct.Product.UnitId,
                    Specification = orderProduct.Specification,
                    SupplierCost = orderProduct.SupplierCost ?? orderProduct.Product.SupplierCost,
                    Subtotal = orderProduct.Qty * salePriceWithTaxes,
                    Iva = orderProduct.Iva ?? orderProduct.Product.Iva,
                    Ieps = orderProduct.Ieps ?? orderProduct.Product.Ieps,
                };

            });
            return await _repository.GetExcelFromData<UserOrderProduct, int>(flattenedProducts, offset);
        }

        private decimal RoundDecimal(decimal value, int precision = 2, bool roundUp = true)
        {
            if ((decimal)value == 0.0m)
                return 0.0M;

            decimal corrector = 1.0m / (decimal)Math.Pow(10, precision + 2);

            if ((decimal)value < 0.0m)
            {
                if (roundUp)
                    return Math.Round(value, precision, MidpointRounding.ToEven);
                else
                    return Math.Round(value - corrector, precision, MidpointRounding.AwayFromZero);
            }
            else
            {
                if (roundUp)
                    return Math.Round(value + corrector, precision, MidpointRounding.AwayFromZero);
                else
                    return Math.Round(value, precision, MidpointRounding.ToEven);
            }
        }

        public async Task<MemoryStream> GetExcel(string tenantId, int offset, string q, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus)
        {
            var results = await GetOrders(tenantId, q, 0, 3000, sortBy, sortOrder, id, deliveryStatus, paymentStatus, total, client, branch, brand, deliveryDate,
                promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled, invoiceStatus);

            return await _repository.GetExcel<TableOrder, int>(results, offset, (prop, item, val) => {

                if (prop.PropertyType.Name == nameof(TableOrder.PaymentStatus)
                || prop.PropertyType.Name == nameof(TableOrder.DeliveryStatus))
                {
                    if (item.CanceledById != null)
                        return "Cancelado";
                }

                return null;
            });
        }

        public async Task<MemoryStream> GetClientExcel(string tenantId, int offset, string q, string sortBy, string sortOrder, int? id, string deliveryStatus, string paymentStatus, decimal? total, string client, string branch, string brand, string deliveryDate, string promisedPaymentDate, string actualPaymentDate, int? invoiceId, int includeCancelled, int? invoiceStatus, string name, string legalName)
        {
            var results = await GetClientOrders(tenantId, q, 0, 3000, sortBy, sortOrder, id, deliveryStatus, paymentStatus, total, client, branch, brand, deliveryDate,
                promisedPaymentDate, actualPaymentDate, invoiceId, includeCancelled, invoiceStatus, name, legalName);

            return await _repository.GetExcel<TableClientOrder, int>(results, offset, (prop, item, val) => {

                if (prop.PropertyType.Name == nameof(TableClientOrder.PaymentStatus)
                || prop.PropertyType.Name == nameof(TableClientOrder.DeliveryStatus))
                {
                    if (item.CanceledById != null)
                        return "Cancelado";
                }

                return null;
            });
        }

        public async Task CancelOrders(string tenantId, string logoUrl, int canceledById, int[] ids, OrdersFilterQuery filterQuery)
        {
            if (ids.Length == 0)
            {
                var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                ids = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            if (ids.Length > 1)
            {
                throw new UserFriendlyException("Por favor especifique un sólo elemento");
            }

            var orderId = ids[0];

            Order order = null;

            await _repository.Query(async (db) =>
            {
                order = await db.QueryFirstAsync<Order>("SELECT deliverystatus, deliverydate, numfolio, email FROM orders WHERE id = @orderId AND tenantId = @tenantId", new { orderId, tenantId });

                var deliveryStatus = (int)DeliveryStatus.Rejected;

                await db.QueryAsync(@"
UPDATE orders SET canceledbyid = @canceledById, modifiedbyid = @canceledById, deliverystatus = @deliveryStatus WHERE id = @orderId AND tenantId = @tenantId AND canceledbyid IS NULL AND deliverystatus != 2",
new { canceledById, deliveryStatus, orderId, tenantId });
                
                //Este era el código anterior
                //await _repository.Update<Order, int>(tenantId, canceledById,
                //    x => ids.Contains(x.Id) && x.DeliveryStatus != DeliveryStatus.Delivered && x.CanceledById == null,
                //            new UpdateInfo<Order>()
                //            {
                //                Expression = x => x.CanceledById,
                //                Value = canceledById
                //            },
                //            new UpdateInfo<Order>()
                //            {
                //                Expression = x => x.DeliveryStatus,
                //                Value = (int)DeliveryStatus.Rejected
                //            });

                await AdjustStockForCancelledOrders(tenantId, db, ids);
                return "";
            });

            var sendEmail = (order.DeliveryStatus == DeliveryStatus.Requested);

            if (sendEmail)
            {
                var companyName = await _settingsService.GetSettingValue<string>(tenantId, ClientSettingType.CompanyName);

                var currentCulture = Thread.CurrentThread.CurrentCulture;
                Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-MX");

                var externalUserCancellationEmailBody = (await _settingsService.GetEmailTemplateBody(tenantId, EmailTemplateKey.OrdersExternalUserCancellation))
                    .Replace("[COMPANY_LEGAL_NAME]", companyName)
                    .Replace("[COMPANY_LOGO_URL]", logoUrl)
                    .Replace("[FOLIO]", order.Folio)
                    .Replace("[FECHA_ENTREGA]", order.DeliveryDate.ToLongDateString());

                Thread.CurrentThread.CurrentCulture = currentCulture;

                _emailService.SendEmail(false, "noreply@misingredientes.mx", order.Email?.Split(","), "Cancelación de pedido", externalUserCancellationEmailBody, true);
            }

            var urls = await _webHookService.GetWebhookUrls(tenantId, "orders.cancelled");

            if (urls.Count() > 0)
            {
                await _webHookService.TriggerWebHooks(tenantId, "orders.cancelled", JsonConvert.SerializeObject(new CancelOrderWebhookPayload()
                {
                    Folios = await GetFoliosFromIds(ids)
                }));
            }
        }

        public async Task CancelClientOrders(string tenantId, string logoUrl, int canceledById, int[] ids, OrdersFilterQuery filterQuery)
        {
            if (ids.Length == 0)
            {
                var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                ids = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            if (ids.Length > 1)
            {
                throw new UserFriendlyException("Por favor especifique un sólo elemento");
            }

            var orderId = ids[0];

            Order order = null;

            await _repository.Query(async (db) =>
            {
                //WARNING: Corregir esto (lo del tenantId)
                order = await db.QueryFirstAsync<Order>("SELECT tenantId, deliverystatus, deliverydate, numfolio, email FROM orders WHERE id = @orderId", new { orderId });

                var deliveryStatus = (int)DeliveryStatus.Rejected;
                //WARNING: Corregir esto (lo del tenantId)
                await db.QueryAsync(@"
UPDATE orders SET canceledbyid = @canceledById, modifiedbyid = @canceledById, deliverystatus = @deliveryStatus WHERE id = @orderId AND canceledbyid IS NULL AND deliverystatus != 2",
new { canceledById, deliveryStatus, orderId });

                await AdjustStockForCancelledOrders(tenantId, db, ids);
                return "";
            });

            var sendEmail = (order.DeliveryStatus == DeliveryStatus.Requested);

            if (sendEmail)
            {
                var companyName = await _settingsService.GetSettingValue<string>(tenantId, ClientSettingType.CompanyName);

                var currentCulture = Thread.CurrentThread.CurrentCulture;
                Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-MX");

                var externalUserCancellationEmailBody = (await _settingsService.GetEmailTemplateBody(tenantId, EmailTemplateKey.OrdersExternalUserCancellation))
                    .Replace("[COMPANY_LEGAL_NAME]", companyName)
                    .Replace("[COMPANY_LOGO_URL]", logoUrl)
                    .Replace("[FOLIO]", order.Folio)
                    .Replace("[FECHA_ENTREGA]", order.DeliveryDate.ToLongDateString());

                Thread.CurrentThread.CurrentCulture = currentCulture;

                _emailService.SendEmail(false, "noreply@misingredientes.mx", order.Email?.Split(","), "Cancelación de pedido", externalUserCancellationEmailBody, true);
            }

            if (order.TenantId == "test")
            {
                var urls = await _webHookService.GetWebhookUrls("test", "orders.cancelled");

                if (urls.Count() > 0)
                {
                    await _webHookService.TriggerWebHooks("test", "orders.cancelled", JsonConvert.SerializeObject(new CancelOrderWebhookPayload()
                    {
                        Folios = await GetFoliosFromIds(ids)
                    }));
                }
            }
        }

        public async Task<Order> CreateOrderFromPublicTemplate(string templateId, Order order)
        {
            order.DeliveryStatus = DeliveryStatus.Requested;

            if (order.Products == null || order.Products.Count == 0)
                throw new UserFriendlyException("Seleccione al menos un producto para el pedido");

            if (order.HasFixedDiscount)
            {
                if (order.Discount == null)
                    order.HasFixedDiscount = false;
            }

            order.TemplateId = order.Id;
            var orderTemplate = MapOrderToOrderTemplate(order);
            TenantIdAndCreatedBy tenantIdAndCreatedBy = null;

            await _repository.Query<string>(async (db) =>
            {
                //TODO: Verificar que la plantilla esté activa

                tenantIdAndCreatedBy = await db.QuerySingleOrDefaultAsync<TenantIdAndCreatedBy>(@"SELECT (SELECT id FROM users WHERE email = 'externaluser') AS externaluserid, 
(SELECT tenantId FROM ordertemplates WHERE publicid = @templateId) AS tenantId", new { templateId });
                return null;

            });

            return await InsertOrder(tenantIdAndCreatedBy.TenantId, tenantIdAndCreatedBy.ExternalUserId, order);

        }

        public async Task SendConfirmationEmail(Order order, string logoUrl, byte[] orderAsPdf)
        {
            var companyName = await _settingsService.GetSettingValue<string>(order.TenantId, ClientSettingType.CompanyName);

            //Correo para el cliente
            var externalUserConfirmationEmailBody = (await _settingsService.GetEmailTemplateBody(order.TenantId, EmailTemplateKey.OrdersExternalUserConfirmation))
                .Replace("[COMPANY_LEGAL_NAME]", companyName)
                .Replace("[COMPANY_LOGO_URL]", logoUrl);

            if (!string.IsNullOrEmpty(order.Email))
                _emailService.SendEmail(false, "noreply@misingredientes.mx", order.Email.Split(","), "Confirmación de pedido", externalUserConfirmationEmailBody, true, new MailAttachment[] {
                    new MailAttachment() { Name = "pedido.pdf", Data = orderAsPdf }
                });

            //Correo para el proveedor
            var currentCulture = Thread.CurrentThread.CurrentCulture;
            Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("es-MX");

            var requestEmailBody = (await _settingsService.GetEmailTemplateBody(order.TenantId, EmailTemplateKey.OrdersRequest))
                .Replace("[MARCA]", order?.Branch?.ClientBrand?.Name)
                .Replace("[SUCURSAL]", order?.Branch?.Name)
                .Replace("[FECHA_ENTREGA]", order?.DeliveryDate.ToLongDateString());

            Thread.CurrentThread.CurrentCulture = currentCulture;

            var emailAddresses = await _repository.Query(async (db) =>
            {
                return await db.QueryAsync<string>("SELECT email FROM users WHERE sendexternalorderemail = true AND tenantid = @tenantId", new { tenantId = order.TenantId });
            });

            _emailService.SendEmail(false, "noreply@misingredientes.mx", emailAddresses.Where(x => !string.IsNullOrEmpty(x)).ToArray(), "Nuevo pedido en Mis Ingredientes",
                requestEmailBody, true, new MailAttachment[] {
                    new MailAttachment() { Name = "pedido.pdf", Data = orderAsPdf }
                });
        }

        public async Task<Order> CreateOrderFromTemplate(string tenantId, int createdBy, Order order) //Order en realidad es una plantilla
        {
            order.Email = "";
            if (order.Products == null || order.Products.Count == 0)
                throw new UserFriendlyException("Seleccione al menos un producto para el pedido");

            if (order.HasFixedDiscount)
            {
                if (order.Discount == null)
                    order.HasFixedDiscount = false;
            }

            order.TemplateId = order.Id == -1 ? null : (int?)order.Id;
            var orderTemplate = MapOrderToOrderTemplate(order);

            order.Total = CalculateTotal(order).Total;

            return await InsertOrder(tenantId, createdBy, order);
        }

        //Order en realidad es una plantilla
        public async Task<IEnumerable<Order>> CreateClientOrdersFromTemplate(string tenantId, int createdBy, Order order)
        {      
            if (order.Products == null || order.Products.Count == 0)
                throw new UserFriendlyException("Seleccione al menos un producto para el pedido");

            if (order.HasFixedDiscount)
            {
                if (order.Discount == null)
                    order.HasFixedDiscount = false;
            }

            order.TemplateId = order.Id <= 0 ? null : (int?)order.Id;
            var orderTemplate = MapOrderToOrderTemplate(order);

            //Este es un caché
            order.Total = CalculateTotal(order).Total;

            var orders = new List<Order>();

            //Se crea un pedido por cada proveedor
            await _repository.Query(async (db) =>
            {
                var branchId = order.BranchId;
                //Este es el correo a donde se enviará el pedido (del lado del restaurante)
                order.Email = await db.QueryFirstAsync<string>("SELECT manageremail FROM clientbranches WHERE tenantId = @tenantId AND id = @branchId", new { tenantId, branchId });

                order.ClientId = await db.QueryFirstAsync<int>("SELECT id from clients WHERE tenantId = @tenantId", new { tenantId });

                var products = await db.QueryAsync<Product>(
@$"SELECT products.*, suppliers.tenantid as ClientSupplierTenantId FROM products 
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
WHERE products.id in ({string.Join(",", order.Products.Select(x => x.ProductId))})");

                var groupedProductsBySupplier = products.GroupBy(x =>
                {
                    //Si el producto es externo usa el tenantId del producto
                    if (x.TenantId != tenantId)
                        return x.TenantId;

                    //Si el producto fue creado por el cliente de un proveedor verificado entonces usa el id del proveedor
                    //Se concatena con el id del proveedor para que se genere un pedido por proveedor
                    if (!string.IsNullOrEmpty(x.ClientSupplierTenantId))
                        return x.ClientSupplierId + "_" + x.ClientSupplierTenantId;

                    //Si el producto fue creado por el cliente y no tiene un proveedor verificado usa el tenantId del restaurante
                    return tenantId;
                });

                var supplierIds = groupedProductsBySupplier.Select(x => x.Key).Distinct().Select(x => $"'{x}'").ToArray();
                var suppliersQuery = supplierIds.Length == 0 ? "" : @$"SELECT id, tenantId, name, legalname FROM suppliers where tenantId IN ({string.Join(",", supplierIds)})";
                var suppliers = !string.IsNullOrEmpty(suppliersQuery)
                    ? await db.QueryAsync<Supplier>(suppliersQuery)
                    : new Supplier[0] { };

                //Para obtener la lista de proveedores para los productos adicionales
                var internalSupplierIds = products.Select(x => x.ClientSupplierId).Where(x => x != null).Distinct().ToArray();
                var internalSuppliersQuery = internalSupplierIds.Length == 0 ? "" : @$"SELECT id, tenantId, legalname, taxpayeridno, name FROM suppliers where id IN ({string.Join(",", internalSupplierIds.Select(x => $"'{x}'"))})";
                var internalSuppliers = !string.IsNullOrEmpty(internalSuppliersQuery)
                    ? await db.QueryAsync<Supplier>(internalSuppliersQuery)
                    : new Supplier[0] { };

                foreach (var group in groupedProductsBySupplier)
                {

                    var supplierTenantId = group.Key;
                    var clientSupplierId = -1;

                    if (supplierTenantId.Contains("_"))
                    {
                        clientSupplierId = Int32.Parse(supplierTenantId.Split("_")[0]);
                        supplierTenantId = supplierTenantId.Split("_")[1];
                    }

                    var supplierName = clientSupplierId == -1
                        ? suppliers.FirstOrDefault(x => x.TenantId == supplierTenantId)?.Name
                        : internalSuppliers.FirstOrDefault(x => x.Id == clientSupplierId)?.Name;

                    var supplierLegalName = clientSupplierId == -1
                        ? suppliers.FirstOrDefault(x => x.TenantId == supplierTenantId)?.LegalName
                        : internalSuppliers.FirstOrDefault(x => x.Id == clientSupplierId)?.Name;

                    orders.Add(new Order()
                    {
                        IsActive = true,
                        Comments = order.Comments,
                        BranchId = order.BranchId,
                        ActualPaymentDate = null,
                        //Subtotal = order.Subtotal,
                        //Discount = order.Discount,
                        //DiscountType = order.DiscountType,
                        //IepsTotal = order.IepsTotal,
                        //IvaTotal = order.IvaTotal,
                        //HasFixedDiscount = order.HasFixedDiscount,
                        //TODO: Hay que cambiarlo para que desde el cliente ya se envíe descompuesto
                        Total = groupedProductsBySupplier.Count() == 1 ? order.Total : 0,
                        CanceledById = null,
                        DeliveryStatus = tenantId == supplierTenantId ? DeliveryStatus.Pending : DeliveryStatus.Requested,
                        InvoiceId = null,
                        PaymentStatus = PaymentStatus.Pending,
                        AccessedById = null,
                        TemplateId = order.TemplateId,
                        ClientId = order.ClientId,
                        DeliveryDate = order.DeliveryDate,
                        TemplateSupplierId = order.TemplateSupplierId,
                        TenantId = supplierTenantId,
                        //SupplierName = supplierName,
                        //SupplierLegalName = supplierLegalName,
                        SupplierId = clientSupplierId == -1 ? null : (int?)clientSupplierId,
                        Products = group.Select(x => {

                            var orderProduct = order.Products.FirstOrDefault(y => y.ProductId == x.Id);

                            if (orderProduct == null)
                                return orderProduct;

                            orderProduct.ProductId = x.Id;
                            orderProduct.TenantId = supplierTenantId;

                            return orderProduct;

                        }).Where(x => x != null).ToList()
                    });
                }

                return "";
            });

            var insertedOrderIds = new List<int>();

            foreach(var o in orders)
            {
                if (o.Products.Any(x => x.Qty > 0))
                {
                    var newOrder = await InsertOrder(o.TenantId, createdBy, o, true);
                    insertedOrderIds.Add(newOrder.Id);
                }
            }

            return await GetOrdersByIds(insertedOrderIds.ToArray());
        }

        public async Task<Order> InsertOrder(string tenantId, int createdBy, Order order, bool isClient = false)
        {
            return await _repository.Query(async (db) =>
            {
                var productsWithNoStock = await VerifyStock(tenantId, db, order);

                if (productsWithNoStock.Length > 0)
                    throw new UserFriendlyException("Los siguientes productos no se pueden vender ya que no se cuenta con el inventario indicado: "
                        + string.Join(", ", productsWithNoStock.Select(x => x.Name).ToArray()));

                var dynamicParameters = new DynamicParameters();

                //Este es un caché
                order.Total = CalculateTotal(order).Total;

                var now = DateTime.UtcNow;

                dynamicParameters.Add("templateId", order.TemplateId);
                dynamicParameters.Add("clientId", order.ClientId);
                dynamicParameters.Add("branchId", order.BranchId);
                dynamicParameters.Add("email", order.Email);
                dynamicParameters.Add("deliveryDate", order.DeliveryDate);
                dynamicParameters.Add("total", order.Total);
                dynamicParameters.Add("deliveryStatus", order.DeliveryStatus);
                dynamicParameters.Add("invoiceId", order.InvoiceId);
                dynamicParameters.Add("paymentStatus", order.PaymentStatus);
                dynamicParameters.Add("promisedPaymentDate", order.PromisedPaymentDate);
                dynamicParameters.Add("actualPaymentDate", order.ActualPaymentDate);
                dynamicParameters.Add("discount", order.Discount);
                dynamicParameters.Add("discountType", order.DiscountType);
                dynamicParameters.Add("subtotal", order.Subtotal);
                dynamicParameters.Add("ivatotal", order.IvaTotal);
                dynamicParameters.Add("iepstotal", order.IepsTotal);
                dynamicParameters.Add("hasFixedDiscount", order.HasFixedDiscount);
                dynamicParameters.Add("canceledById", order.CanceledById);
                dynamicParameters.Add("tenantId", tenantId);
                dynamicParameters.Add("createdById", createdBy);
                dynamicParameters.Add("created", now);
                dynamicParameters.Add("modifiedById", createdBy);
                dynamicParameters.Add("modified", now);
                dynamicParameters.Add("isActive", true); 
                dynamicParameters.Add("comments", order.Comments);
                dynamicParameters.Add("supplierId", order.SupplierId);
                dynamicParameters.Add("templatesupplierid", order.TemplateSupplierId);
              

                //dynamicParameters.Add("supplierName", order.SupplierName ?? "");
                //dynamicParameters.Add("supplierLegalName", order.SupplierLegalName ?? "");

                var tempTableName = "T" + Guid.NewGuid().ToString("N");

                //@supplierName, @supplierLegalName
                //SupplierName, SupplierLegalName,

                var query = $@"

                BEGIN;

                CREATE TEMPORARY TABLE {tempTableName}(id INT);

                with newOrderId as (
                    INSERT INTO orders 
(NumFolio,Email,TemplateId,ClientId,BranchId,DeliveryDate,Total,DeliveryStatus,InvoiceId,PaymentStatus,PromisedPaymentDate,
ActualPaymentDate,Discount,DiscountType,HasFixedDiscount,Subtotal,IvaTotal,IepsTotal,CanceledById,TenantId,CreatedById,Created,ModifiedById,
Modified,IsActive, Comments, SupplierId, TemplateSupplierId) 
VALUES((SELECT (CASE WHEN (SELECT COUNT(*) FROM orders WHERE tenantId = @tenantId) = 0 THEN 1 ELSE (SELECT numfolio+1 as id FROM orders WHERE tenantId = @tenantId ORDER BY numfolio DESC LIMIT 1) END)),@email,@templateId,@clientId,@branchId,@deliveryDate,
@total,@deliveryStatus,@invoiceId, @paymentStatus,@promisedPaymentDate,@actualPaymentDate,@discount,@discountType,
@hasFixedDiscount,@subtotal,@ivatotal,@iepstotal,@canceledById, @tenantId,@createdById,@created,@modifiedById,@modified,@isActive,
@comments, @supplierId, @templateSupplierId) RETURNING id
                ) INSERT INTO {tempTableName}(id) VALUES((SELECT id FROM newOrderId));

                INSERT INTO public.orderproducts(orderid, productid, qty, fixedprice, subtotal, specification, suppliercost, saleprice, iva, ieps, tenantid, createdbyid, created, modifiedbyid, modified, isactive) 
                VALUES {string.Join(",", order.Products.Where(x => x.Qty > 0).Select(product =>
                {
                    var prefix = product.ProductId + "_";
                    dynamicParameters.Add($"{prefix}productid", product.ProductId);
                    dynamicParameters.Add($"{prefix}qty", product.Qty);
                    dynamicParameters.Add($"{prefix}fixedprice", product.FixedPrice);
                    dynamicParameters.Add($"{prefix}subtotal", product.Subtotal);
                    dynamicParameters.Add($"{prefix}specification", product.Specification);
                    dynamicParameters.Add($"{prefix}suppliercost", product.SupplierCost);
                    dynamicParameters.Add($"{prefix}saleprice", product.SalePrice);
                    dynamicParameters.Add($"{prefix}iva", product.Iva);
                    dynamicParameters.Add($"{prefix}ieps", product.Ieps);

                    return $@" ((SELECT * FROM {tempTableName}), @{prefix}productid, @{prefix}qty, @{prefix}fixedprice, @{prefix}subtotal, @{prefix}specification, @{prefix}suppliercost, 
    @{prefix}saleprice, @{prefix}iva, @{prefix}ieps, @tenantId, @createdById, @created, @modifiedById, @modified, true)";

                }).ToArray())};

                SELECT * FROM {tempTableName};

                END;

                ";

                var orderId = db.QueryFirst<int>(query, dynamicParameters);

                //var insertOrderQuery = _repository.GetInsertQuery<Order, int>(tenantId, createdBy, order, ref dynamicParameters, "Id");


                //var orderId = db.QueryFirst<int>(insertOrderQuery, dynamicParameters);

                //var strBuilder = new StringBuilder();

                //foreach (var product in order.Products.Where(x => x.Qty > 0))
                //{
                //    product.OrderId = orderId;
                //    product.TenantId = tenantId;
                //    strBuilder.Append(_repository.GetInsertQuery<OrderProduct, int>(tenantId, createdBy, product, ref dynamicParameters, "id"));
                //}

                //Console.WriteLine(strBuilder);

                //using (var multi = await db.QueryMultipleAsync(strBuilder.ToString(), dynamicParameters))
                //{
                //    foreach (var product in order.Products.Where(x => x.Qty > 0))
                //    {
                //        product.Id = multi.Read<int>().First();
                //    }
                //}

                //order.Id = orderId;

                order = await GetOrderById(tenantId, -1, orderId);

                await AdjustStockForNewOrders(tenantId, db, order, new int[] { orderId });

                if (isClient && order.TenantId == "test")
                {
                    await _webHookService.TriggerWebHooks("test", "orders.added", JsonConvert.SerializeObject(order));
                }
                else
                    await _webHookService.TriggerWebHooks(tenantId, "orders.added", JsonConvert.SerializeObject(order));

                return order;
            });
        }

        private OrderTemplate MapOrderToOrderTemplate(Order order)
        {
            return new OrderTemplate()
            {
                Id = order.Id,
                //BrandId = order.BrandId,
                ClientId = order.ClientId,
                Discount = order.Discount,
                DiscountType = order.DiscountType,
                HasFixedDiscount = order.HasFixedDiscount,
                Products = order.Products.Select(x => new OrderTemplateProduct()
                {
                    Id = x.Id,
                    OrderTemplateId = order.Id, //order.Id es en realidad el ordertemplate.id
                    ProductId = x.ProductId,
                    Qty = x.Qty,
                    Specification = x.Specification
                }).ToList()
            };
        }

        //        public async Task<bool> VerifyStock(string tenantId, IDbConnection db, int[] orderIds)
        //        {
        //            var stockQuery = $@"SELECT products.id, products.name, products.qty as stock, orderproducts.qty 
        //FROM products, orderproducts WHERE products.continuestockmode = FALSE 
        //AND orderproducts.productid = products.id
        //AND products.qty < orderproducts.qty
        //AND orderproducts.orderid IN ({string.Join(',', orderIds.Select(x => x.ToString()))})
        //AND products.tenantid = @tenantid
        //AND orderproducts.tenantid = @tenantid";

        //            var productsWithNoStock = await db.QueryAsync(stockQuery, new { tenantId });

        //            if (productsWithNoStock.Count() > 0)
        //            {
        //                return false;
        //            }

        //            return true;
        //        }

        public async Task<Product[]> VerifyStock(string tenantId, IDbConnection db, Order order)
        {
            var products = order.Products.Where(x => x.Qty > 0);

            if(products.Count() == 0)
                return new Product[] { };

            var query = $"SELECT * FROM products WHERE tenantId = @tenantId AND ({string.Join(" OR ", products.Select(x => $"(id = {x.ProductId} AND qty < {x.Qty} AND continuestockmode = false)"))})";

            var productsWithNoStock = await db.QueryAsync<Product>(query, new { tenantId });

            if (productsWithNoStock.Count() > 0)
            {
                foreach (var product in productsWithNoStock)
                {
                    Console.WriteLine("No hay inventario: " + product.Name);
                }

                return productsWithNoStock.ToArray();
            }

            return new Product[] { };
        }

        public async Task AdjustStockForExistingOrder(string tenantId, IDbConnection db, int orderId, Order order,
            OrderProduct[] existingOrderProducts, OrderProduct[] deletedProducts, OrderProduct[] addedProducts)
        {

            var query = string.Join("; ", existingOrderProducts.Select(x => @$"UPDATE products
SET qty = (products.qty + ({x.Qty} - orderproducts.qty))
FROM orderproducts
WHERE 
orderproducts.orderid = {orderId} AND
orderproducts.productid = products.id AND
products.id = {x.ProductId} AND products.tenantId = @tenantId AND orderproducts.tenantId = @tenantId"));


            var deletedQuery = string.Empty;
            var addedQuery = string.Empty;

            if (deletedProducts != null && deletedProducts.Length > 0)
            {
                deletedQuery = string.Join("; ", deletedProducts.Select(x => @$"UPDATE products
SET qty = (products.qty + {x.Qty})
WHERE 
id = {x.ProductId} AND
tenantId = @tenantId"));
            }

            if (!string.IsNullOrEmpty(query))
                query = query + "; " + deletedQuery;
            else query = deletedQuery;

            if (addedProducts.Length > 0)
            {
                addedQuery = string.Join("; ", addedProducts.Select(x => @$"UPDATE products
SET qty = (products.qty - {x.Qty})
WHERE 
id = {x.ProductId} AND
tenantId = @tenantId"));
            }

            if (!string.IsNullOrEmpty(query))
                query = query + "; " + addedQuery;
            else query = addedQuery;

            if (!string.IsNullOrEmpty(query))
            {
                await db.QueryAsync(query, new { tenantId });

                try
                {
                    var products = await db.QueryAsync<Product>($@"SELECT DISTINCT products.* FROM products,orderproducts WHERE 
orderproducts.productid = products.id
AND orderproducts.saleprice is null
AND orderproducts.orderid = {orderId}
AND products.tenantId = @tenantId
AND orderproducts.tenantId = @tenantId;", new { tenantId });

                    foreach (var product in products)
                    {
                        product.OrderInfo = order;
                        await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(product));
                    }

                    if (deletedProducts != null && deletedProducts.Length > 0)
                    {

                        query = @$"SELECT * FROM products WHERE products.id IN ({string.Join(',', deletedProducts.Select(x => x.ProductId))}) AND products.tenantid = @tenantId";
                        products = await db.QueryAsync<Product>(query, new { tenantId });

                        //Console.WriteLine(query);

                        foreach (var product in products)
                        {
                            product.OrderInfo = order;
                            await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(product));
                        }
                    }

                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error al notificar: " + ex.Message);
                }
            }
        }

        public async Task AdjustStockForCancelledOrders(string tenantId, IDbConnection db, int[] orderIds)
        {
            var query = $@"UPDATE products
SET qty = products.qty + orderproducts.qty
FROM orderproducts
WHERE 
orderproducts.productid = products.id
AND orderproducts.saleprice is null
AND orderproducts.orderid IN ({string.Join(',', orderIds.Select(x => x.ToString()))})
AND products.tenantId = @tenantId
AND orderproducts.tenantId = @tenantId;";

            await db.QueryAsync(query, new { tenantId });

            try
            {

                var products = await db.QueryAsync<Product>($@"SELECT DISTINCT products.* FROM products,orderproducts WHERE 
orderproducts.productid = products.id
AND orderproducts.saleprice is null
AND orderproducts.orderid IN({ string.Join(',', orderIds.Select(x => x.ToString()))})
AND products.tenantId = @tenantId
AND orderproducts.tenantId = @tenantId;", new { tenantId });

                foreach (var product in products)
                {
                    await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(product));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error al notificar: " + ex.Message);
            }

        }

        public async Task AdjustStockForNewOrders(string tenantId, IDbConnection db, Order order, int[] orderIds)
        {
            var query = $@"UPDATE products
SET qty = products.qty - orderproducts.qty
FROM orderproducts
WHERE 
orderproducts.productid = products.id
AND orderproducts.saleprice is null
AND orderproducts.orderid IN ({string.Join(',', orderIds.Select(x => x.ToString()))})
AND products.tenantId = @tenantId
AND orderproducts.tenantId = @tenantId;";

            await db.QueryAsync(query, new { tenantId });

            try
            {

                var products = await db.QueryAsync<Product>($@"SELECT DISTINCT products.* FROM products,orderproducts WHERE 
orderproducts.productid = products.id
AND orderproducts.saleprice is null
AND orderproducts.orderid IN({ string.Join(',', orderIds.Select(x => x.ToString()))})
AND products.tenantId = @tenantId
AND orderproducts.tenantId = @tenantId;", new { tenantId });

                foreach (var product in products)
                {
                    product.OrderInfo = order;
                    await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(product));
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error al notificar: " + ex.Message);
            }

        }

        public async Task<bool> SetDeliveryStatus(string tenantId, int modifiedBy, int[] ids, int status, OrdersFilterQuery filterQuery)
        {
            var deliveryStatus = (DeliveryStatus)status;

            if (deliveryStatus == DeliveryStatus.Requested) return false; //El estatus solicitado sólo se puede asignar cuando el pedido es nuevo (creado por un usuario externo)

            //if (deliveryStatus == DeliveryStatus.Pending) return false; //Ya no es posible regresar a Pendiente a menos de que esté solicitado

            if (deliveryStatus == DeliveryStatus.Pending)
            {
                if (ids.Length != 1)
                    throw new UserFriendlyException("Especifique un pedido únicamente");

                return await _repository.Query<bool>(async (db) =>
                {
                    var orderData = await db.QueryFirstOrDefaultAsync<Order>("SELECT * FROM orders WHERE tenantId = @tenantId AND id = @id",
                        new { tenantId, id = ids[0] });

                    if (orderData.DeliveryStatus != DeliveryStatus.Requested) throw new UserFriendlyException("Ya no es posible pasar al estado Pendiente");

                    var dynamicParameters = new DynamicParameters();

                    var query = _repository.GetUpdateQuery<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id), ref dynamicParameters,
                        new UpdateInfo<Order>()
                        {
                            Expression = x => x.DeliveryStatus,
                            Value = status
                        });

                    await db.QueryAsync(query, dynamicParameters);

                    var urls = await _webHookService.GetWebhookUrls(tenantId, "orders.deliverystatus");

                    if (urls.Count() > 0)
                    {
                        await _webHookService.TriggerWebHooks(tenantId, "orders.deliverystatus",
                            JsonConvert.SerializeObject(new DeliveryStatusWebhookPayload()
                            {
                                DeliveryStatus = deliveryStatus,
                                Folios = await GetFoliosFromIds(ids)
                            }));
                    }

                    if (!string.IsNullOrEmpty(orderData.Email))
                        _emailService.SendEmail(true, "noreply@misingredientes.mx", new string[] { orderData.Email }, "Su pedido se ha aceptado", "¡Felicidades, su pedido se ha aceptado");

                    return true;

                });
            }

            if (deliveryStatus == DeliveryStatus.InRoute || deliveryStatus == DeliveryStatus.Delivered)
            {
                //Si el estatus se cambia a entregado o en ruta se congelan los precios y aunque se cambien en el
                //catálogo ya no se cambiarán en el pedido

                return await _repository.Query<bool>(async (db) =>
                {
                    var dynamicParameters = new DynamicParameters();

                    if (ids.Length == 0)
                    {
                        var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                        ids = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
                    }

                    var query = _repository.GetUpdateQuery<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id), ref dynamicParameters,
                        new UpdateInfo<Order>()
                        {
                            Expression = x => x.DeliveryStatus,
                            Value = status
                        });

                    //Congela los precios y costos de los productos

                    query += $@"

UPDATE orderproducts 
SET saleprice = products.saleprice,
suppliercost = products.suppliercost,
iva = (CASE WHEN products.iva IS NULL THEN 0 ELSE products.iva END),
ieps = (CASE WHEN products.ieps IS NULL THEN 0 ELSE products.ieps END)
FROM products, orders
WHERE 
products.id = orderproducts.productid
AND orderproducts.saleprice is null
AND orders.id = orderproducts.orderid
AND orders.id IN ({string.Join(',', ids.Select(x => x.ToString()))})
AND orderproducts.tenantId = @tenantId
AND products.tenantId = @tenantId
AND orders.tenantId = @tenantId;

";

                    await db.QueryAsync(query, dynamicParameters);


                    var urls = await _webHookService.GetWebhookUrls(tenantId, "orders.deliverystatus");

                    if (urls.Count() > 0)
                    {
                        await _webHookService.TriggerWebHooks(tenantId, "orders.deliverystatus",
                            JsonConvert.SerializeObject(new DeliveryStatusWebhookPayload()
                            {
                                DeliveryStatus = deliveryStatus,
                                Folios = await GetFoliosFromIds(ids)
                            }));
                    }

                    return true;

                });

                //await _repository.Update<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id),
                //    new UpdateInfo<Order>()
                //    {
                //        Expression = x => x.DeliveryStatus,
                //        Value = status
                //    });
            }

            return false;

        }

        //public async Task SetPaymentStatus(string tenantId, int modifiedBy, int[] ids, int status)
        //{
        //    await _repository.Update<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id),
        //        new UpdateInfo<Order>()
        //        {
        //            Expression = x => x.PaymentStatus,
        //            Value = status
        //        });
        //}


        public async Task SetPromisedPaymentDate(string tenantId, int modifiedBy, int[] ids, DateTime date, OrdersFilterQuery filterQuery)
        {
            if (ids.Length == 0)
            {
                var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                ids = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            await _repository.Update<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id),
                new UpdateInfo<Order>()
                {
                    Expression = x => x.PromisedPaymentDate,
                    Value = date
                });

            var urls = await _webHookService.GetWebhookUrls(tenantId, "orders.promisedpaymentdate");

            if (urls.Count() > 0)
            {
                await _webHookService.TriggerWebHooks(tenantId, "orders.promisedpaymentdate",
                JsonConvert.SerializeObject(new PromisedPaymentDateWebhookPayload()
                {
                    PromisedPaymentDate = date,
                    Folios = await GetFoliosFromIds(ids)
                }));
            }
        }

        public async Task SetDeliveryDate(string tenantId, int modifiedBy, int[] ids, DateTime date, OrdersFilterQuery filterQuery)
        {
            if (ids.Length == 0)
            {
                var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                ids = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            await _repository.Update<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id),
                new UpdateInfo<Order>()
                {
                    Expression = x => x.DeliveryDate,
                    Value = date
                });

            var urls = await _webHookService.GetWebhookUrls(tenantId, "orders.deliverydate");

            if (urls.Count() > 0)
            {
                await _webHookService.TriggerWebHooks(tenantId, "orders.deliverydate",
                    JsonConvert.SerializeObject(new DeliveryDateWebhookPayload()
                    {
                        DeliveryDate = date,
                        Folios = await GetFoliosFromIds(ids)
                    }));
            }

        }

        public async Task SetClientDeliveryDate(string tenantId, int modifiedBy, int[] ids, DateTime date, OrdersFilterQuery filterQuery)
        {
            //WARNING: Poner aquí bien el query
            if (ids.Length == 0)
            {
                var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                ids = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            var now = DateTime.UtcNow;
            var query = $"UPDATE orders SET DeliveryDate = @date, ModifiedById = @modifiedBy, Modified = @now WHERE (orders.Id = ANY(ARRAY[{(string.Join(",", ids))}])) AND orders.IsActive = True;";

            await _repository.Query(async (db) =>
            {
                return await db.QueryAsync(query, new { date, modifiedBy, now });
            });

        }

        //public async Task SetActualPaymentDate(string tenantId, int modifiedBy, int[] ids, DateTime date)
        //{
        //    await _repository.Update<Order, int>(tenantId, modifiedBy, x => ids.Contains(x.Id),
        //        new UpdateInfo<Order>()
        //        {
        //            Expression = x => x.ActualPaymentDate,
        //            Value = date
        //        });


        //}

        public async Task<string[]> GetFoliosFromIds(int[] ids)
        {
            //return ids.Select(x => x.ToString().PadLeft(4, '0')).ToArray();
            var res = await _repository.Query(async (db) =>
            {
                var query = $"SELECT numfolio FROM public.orders WHERE id IN ({string.Join(',', ids.Select(x => $"{x}"))})";
                return await db.QueryAsync<int>(query);
            });

            return res.Select(x => x.ToString().PadLeft(4, '0')).ToArray();
        }

        public async Task<Order> PatchOrder(string tenantId, int modifiedBy, int id, JsonPatchDocument<Order> changes)
        {
            await _repository.Patch<Order, int>(tenantId, modifiedBy, id, changes, false);


            if(changes.Operations.Any(x => x.path == "/discountType")){
                throw new Exception("Invalid path");
            }

            var order = await GetOrderById(tenantId, -1, id);
            await _webHookService.TriggerWebHooks(tenantId, "orders.updated", JsonConvert.SerializeObject(order));
            return order;
        }

        public async Task<IEnumerable<DistinctResult>> GetDistinctValues(string tenantId, string propName, string term)
        {
            if (propName == "brand")
            {
                return await _repository.Query<IEnumerable<DistinctResult>>(async (db) =>
                {
                    term = term.PrepareQuery();

                    var query = @"
SELECT DISTINCT public.clientbrands.id AS value, public.clientbrands.name AS text
        FROM public.orders
		JOIN public.clientbranches ON public.clientbranches.clientid = public.orders.clientid
		JOIN public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
        WHERE public.clientbrands.name ~* @term AND orders.TenantId = @tenantId
        LIMIT 20
";

                    return await db.QueryAsync<DistinctResult>(query, new { term, tenantId });

                });
            }

            return await _repository.GetDistinct<Order, int>(tenantId, propName, term);
        }

        public Expression<Func<Order, bool>> GetFilterExpressionFromQuery(OrdersFilterQuery query)
        {
            var filterExpression = GetFilterExpression(query.Id, query.DeliveryStatus, query.PaymentStatus, query.Total, query.Client, query.Branch,
                   query.Brand, query.DeliveryDate, query.PromisedPaymentDate, query.ActualPaymentDate,
                   null, query.IncludeCancelled ?? 0, query.InvoiceStatus);

            return filterExpression;
        }

        private string GetFilterExpressionAsString(int? id, string deliveryStatus,
            string paymentStatus, decimal? total, string client, string branch, string brand,
            string deliveryDate, string promisedPaymentDate, string actualPaymentDate,
            int? invoiceId, int includeCanceled, int? invoiceStatus, string name, string legalName)
        {
            var builder = new List<string>();

            var deliveryStatusArray = deliveryStatus?.Split(',').Select(x => (DeliveryStatus)Convert.ToInt32(x)).ToArray() ?? new DeliveryStatus[] { };
            var paymentStatusArray = paymentStatus?.Split(',').Select(x => (PaymentStatus)Convert.ToInt32(x)).ToArray() ?? new PaymentStatus[] { };
            var clientArray = client?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };
            var branchesArray = branch?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };
            var brandsArray = brand?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };
            var supplierNamesArray = name?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };
            var supplierLegalNamesArray = legalName?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };

            var deliveryDateFrom = deliveryDate != null ? (deliveryDate.Contains(',')
                ? (DateTime?)DateTime.Parse(deliveryDate.Split(',')[0])
                : (DateTime?)DateTime.Parse(deliveryDate))
                : null;

            var deliveryDateTo = deliveryDate != null ? (deliveryDate.Contains(',')
                ? (DateTime?)DateTime.Parse(deliveryDate.Split(',')[1])
                : (DateTime?)DateTime.Parse(deliveryDate))
                : null;

            var promisedPaymentDateFrom = promisedPaymentDate != null ? (promisedPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(promisedPaymentDate.Split(',')[0])
                : (DateTime?)DateTime.Parse(promisedPaymentDate))
                : null;

            var promisedPaymentDateTo = promisedPaymentDate != null ? (promisedPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(promisedPaymentDate.Split(',')[1])
                : (DateTime?)DateTime.Parse(promisedPaymentDate))
                : null;

            var actualPaymentDateFrom = actualPaymentDate != null ? (actualPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(actualPaymentDate.Split(',')[0])
                : (DateTime?)DateTime.Parse(actualPaymentDate))
                : null;

            var actualPaymentDateTo = actualPaymentDate != null ? (actualPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(actualPaymentDate.Split(',')[1])
                : (DateTime?)DateTime.Parse(actualPaymentDate))
                : null;

            if (id != null) builder.Add($"numfolio = {id}");
            if (deliveryStatusArray?.Length > 0) builder.Add($"deliveryStatus IN ({string.Join(",", deliveryStatusArray.Select(ds => (int)ds))})");
            if (paymentStatusArray?.Length > 0) builder.Add($"paymentStatus IN ({string.Join(",", paymentStatusArray.Select(ds => (int)ds))})");
            if (deliveryDateFrom != null && deliveryDateTo != null)
                builder.Add($"(deliverydate >= '{deliveryDateFrom.Value.ToString("yyyy-MM-dd")}' AND deliverydate <= '{deliveryDateTo.Value.ToString("yyyy-MM-dd")}')");
            if (promisedPaymentDateFrom != null && promisedPaymentDateTo != null)
                builder.Add($"(promisedpaymentdate >= '{promisedPaymentDateFrom.Value.ToString("yyyy-MM-dd")}' AND promisedpaymentdate <= '{promisedPaymentDateTo.Value.ToString("yyyy-MM-dd")}')");
            if (actualPaymentDateFrom != null && actualPaymentDateTo != null)
                builder.Add($"(actualpayment >= '{actualPaymentDateFrom.Value.ToString("yyyy-MM-dd")}' AND actualpayment <= '{actualPaymentDateTo.Value.ToString("yyyy-MM-dd")}')");
            if (invoiceStatus == 0)
                builder.Add($"InvoiceId IS NULL");
            else if (invoiceStatus == 1)
                builder.Add($"InvoiceId IS NOT NULL");
            if (branchesArray?.Length > 0)
                builder.Add($"clientbranches.id IN ({string.Join(", ", branchesArray.Select(ds => (int)ds))})");
            if (brandsArray?.Length > 0)
                builder.Add($"clientbrands.id IN ({string.Join(", ", brandsArray.Select(ds => (int)ds))})");
            if (supplierNamesArray?.Length > 0)
                builder.Add($"(orders.supplierid IN ({string.Join(", ", supplierNamesArray.Select(ds => (int)ds))}) OR suppliers.id IN ({string.Join(", ", supplierNamesArray.Select(ds => (int)ds))}))");
            if (supplierLegalNamesArray?.Length > 0)
                builder.Add($"(orders.supplierid IN ({string.Join(", ", supplierLegalNamesArray.Select(ds => (int)ds))}) OR suppliers.id IN ({string.Join(", ", supplierLegalNamesArray.Select(ds => (int)ds))}))");

            return string.Join(" AND ", builder);
        }

        private Expression<Func<Order, bool>> GetFilterExpression(int? id, string deliveryStatus,
            string paymentStatus, decimal? total, string client, string branch, string brand,
            string deliveryDate, string promisedPaymentDate, string actualPaymentDate,
            int? invoiceId, int includeCanceled, int? invoiceStatus)
        {
            var deliveryStatusArray = deliveryStatus?.Split(',').Select(x => (DeliveryStatus)Convert.ToInt32(x)).ToArray() ?? new DeliveryStatus[] { };
            var paymentStatusArray = paymentStatus?.Split(',').Select(x => (PaymentStatus)Convert.ToInt32(x)).ToArray() ?? new PaymentStatus[] { };
            var clientArray = client?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };
            var branchesArray = branch?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };
            var brandsArray = brand?.Split(',').Select(x => Convert.ToInt32(x)).ToArray() ?? new int[] { };

            var deliveryDateFrom = deliveryDate != null ? (deliveryDate.Contains(',')
                ? (DateTime?)DateTime.Parse(deliveryDate.Split(',')[0])
                : (DateTime?)DateTime.Parse(deliveryDate))
                : null;

            var deliveryDateTo = deliveryDate != null ? (deliveryDate.Contains(',')
                ? (DateTime?)DateTime.Parse(deliveryDate.Split(',')[1])
                : (DateTime?)DateTime.Parse(deliveryDate))
                : null;

            var promisedPaymentDateFrom = promisedPaymentDate != null ? (promisedPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(promisedPaymentDate.Split(',')[0])
                : (DateTime?)DateTime.Parse(promisedPaymentDate))
                : null;

            var promisedPaymentDateTo = promisedPaymentDate != null ? (promisedPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(promisedPaymentDate.Split(',')[1])
                : (DateTime?)DateTime.Parse(promisedPaymentDate))
                : null;

            var actualPaymentDateFrom = actualPaymentDate != null ? (actualPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(actualPaymentDate.Split(',')[0])
                : (DateTime?)DateTime.Parse(actualPaymentDate))
                : null;

            var actualPaymentDateTo = actualPaymentDate != null ? (actualPaymentDate.Contains(',')
                ? (DateTime?)DateTime.Parse(actualPaymentDate.Split(',')[1])
                : (DateTime?)DateTime.Parse(actualPaymentDate))
                : null;

            var isCanceled = includeCanceled;

            if(invoiceStatus == null)
            {
                return x =>
                x.NumFolio == id
                && (x.CanceledById == null || isCanceled == 1)
                && x.InvoiceId == invoiceId
                && x.DeliveryDate >= deliveryDateFrom && x.DeliveryDate <= deliveryDateTo
                && x.PromisedPaymentDate >= promisedPaymentDateFrom && x.PromisedPaymentDate <= promisedPaymentDateTo
                && x.ActualPaymentDate >= actualPaymentDateFrom && x.ActualPaymentDate <= actualPaymentDateTo
                && deliveryStatusArray.Contains(x.DeliveryStatus)
                && paymentStatusArray.Contains(x.PaymentStatus)
                && clientArray.Contains(x.ClientId)
                && branchesArray.Contains(x.BranchId)
                && brandsArray.Contains(x.Branch.ClientBrandId.Value);
            }
            else if(invoiceStatus == 0)
            {
                return x =>
                x.NumFolio == id
                && (x.CanceledById == null || isCanceled == 1)
                && x.InvoiceId == null
                && x.InvoiceId == invoiceId
                && x.DeliveryDate >= deliveryDateFrom && x.DeliveryDate <= deliveryDateTo
                && x.PromisedPaymentDate >= promisedPaymentDateFrom && x.PromisedPaymentDate <= promisedPaymentDateTo
                && x.ActualPaymentDate >= actualPaymentDateFrom && x.ActualPaymentDate <= actualPaymentDateTo
                && deliveryStatusArray.Contains(x.DeliveryStatus)
                && paymentStatusArray.Contains(x.PaymentStatus)
                && clientArray.Contains(x.ClientId)
                && branchesArray.Contains(x.BranchId)
                && brandsArray.Contains(x.Branch.ClientBrandId.Value)
                && x.Total == total;
            }
            else
            {
                return x =>
                x.NumFolio == id
                && (x.CanceledById == null || isCanceled == 1)
                && x.InvoiceId != null
                && x.InvoiceId == invoiceId
                && x.DeliveryDate >= deliveryDateFrom && x.DeliveryDate <= deliveryDateTo
                && x.PromisedPaymentDate >= promisedPaymentDateFrom && x.PromisedPaymentDate <= promisedPaymentDateTo
                && x.ActualPaymentDate >= actualPaymentDateFrom && x.ActualPaymentDate <= actualPaymentDateTo
                && deliveryStatusArray.Contains(x.DeliveryStatus)
                && paymentStatusArray.Contains(x.PaymentStatus)
                && clientArray.Contains(x.ClientId)
                && branchesArray.Contains(x.BranchId)
                && brandsArray.Contains(x.Branch.ClientBrandId.Value)
                && x.Total == total;
            }


        }

        public async Task<UpdatedPaymentStatus> InsertMultiPayment(string tenantId, int createdBy, int[] orderIds, DateTime date, OrdersFilterQuery filterQuery)
        {
            return await _repository.Query<UpdatedPaymentStatus>(async (db) =>
            {

                if (orderIds.Length == 0)
                {
                    var filterExpression = GetFilterExpressionFromQuery(filterQuery);
                    orderIds = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
                }

                //var totalQuery = @$"SELECT orders.id, orders.total from orders WHERE id = ANY(@orderIds) AND tenantid = @tenantId";
                var totalQuery = @$"
SELECT SUM(total) as total FROM (SELECT
orders.id, 
orders.total
FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid

WHERE public.orders.id = ANY(@orderIds) AND public.orders.tenantid = @tenantId
							 
GROUP BY orders.id) as results";

                var totalsQuery = @$"
(SELECT
orders.id, 
orders.total
FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
		
WHERE public.orders.id = ANY(@orderIds) AND public.orders.tenantid = @tenantId

GROUP BY orders.id)";

                var total = await db.QueryFirstAsync<decimal>(totalQuery, new { orderIds, tenantId });
                var totals = await db.QueryAsync<OrderTotal>(totalsQuery, new { orderIds, tenantId });

                var parentOrderPayment = new OrderPayment()
                {
                    PaymentDate = date,
                    Total = total,
                };

                var dynamicParameters = new DynamicParameters();
                var insertOrderQuery = _repository.GetInsertQuery<OrderPayment, int>(tenantId, createdBy, parentOrderPayment, ref dynamicParameters, "Id");

                var orderPaymentId = db.QueryFirst<int>(insertOrderQuery, dynamicParameters);

                var strBuilder = new StringBuilder();

                dynamicParameters = new DynamicParameters();

                foreach (var orderId in orderIds)
                {
                    var orderPaymentItem = new OrderPaymentItem()
                    {
                        OrderPaymentId = orderPaymentId,
                        OrderId = orderId,
                        Total = totals.FirstOrDefault(x => x.Id == orderId)?.Total ?? 0
                    };

                    //    tenantId, createdBy, x => x.Id == orderId,
                    //new UpdateInfo<Order>()
                    //{
                    //    Expression = x => x.PaymentStatus,
                    //    Value = paidStatus
                    //},
                    //new UpdateInfo<Order>()
                    //{
                    //    Expression = x => x.ActualPaymentDate,
                    //    Value = actualPaymentDate
                    //}


                    strBuilder.AppendLine(
                        _repository.GetUpdateQuery<Order, int>(tenantId,
                            createdBy,
                            x => x.Id == orderId,
                            ref dynamicParameters, new UpdateInfo<Order>()
                            {
                                Expression = x => x.PaymentStatus,
                                Value = (int)PaymentStatus.Paid
                            },
                            new UpdateInfo<Order>()
                            {
                                Expression = x => x.ActualPaymentDate,
                                Value = date
                            })
                        );

                    strBuilder.AppendLine(
                        _repository.GetInsertQuery<OrderPaymentItem, int>(tenantId,
                            createdBy,
                            orderPaymentItem,
                            ref dynamicParameters));
                }

                await db.QueryMultipleAsync(strBuilder.ToString(), dynamicParameters);

                return new UpdatedPaymentStatus()
                {
                    OrderPaymentId = orderPaymentId,
                    ActualPaymentDate = date,
                    PaymentStatus = (int)PaymentStatus.Paid,
                    OrderIds = orderIds
                };

            });
        }

        public async Task SetPaymentDate(string tenantId, int orderId, int paymentId, DateTime date)
        {
            await _repository.Query<string>(async (db) =>
            {
                var query = $@"
UPDATE orderpayments 
SET paymentdate = @date
WHERE 
orderpayments.tenantId = @tenantId AND orderpayments.id = @paymentId;

UPDATE orders
SET actualpaymentdate = @date
WHERE id = @orderId AND tenantId = @tenantId;

UPDATE orders
SET actualpaymentdate = @date
WHERE id IN (SELECT orderid FROM public.orderpaymentitems WHERE orderpaymentid = @paymentId) AND tenantId = @tenantId;";


            //UPDATE orders
            //        SET actualpaymentdate = (SELECT MAX(paymentdate)
            //            FROM orderpayments WHERE isactive = true AND orderid = @orderId AND tenantId = @tenantId)
            //        WHERE id = @orderId AND tenantId = @tenantId;

            //Console.WriteLine(tenantId + "" + orderId + "" + paymentId);

            await db.QueryAsync(query, new { paymentId, tenantId, orderId, date });

                return "";
            });
        }

        public async Task<Order> InsertOrderTest(int createdBy, Order order)
        {
            var tenantId = "pelican";
            order.Created = DateTime.UtcNow;
            //var order = new Order()
            //{
            //    TemplateId = null,
            //    DeliveryStatus = DeliveryStatus.Pending,
            //    HasFixedDiscount = quote.HasFixedDiscount,
            //    PaymentStatus = PaymentStatus.Pending,
            //    DeliveryDate = deliveryDate,
            //    ClientId = clientId,
            //    BranchId = branchId,
            //    Discount = quote.Discount,
            //    DiscountType = quote.DiscountType,
            //    Total = CalculateTotal(quote),
            //    Products = quote.Products.Select(x => new OrderProduct()
            //    {
            //        Id = x.Id,
            //        Iva = x.Iva,
            //        Ieps = x.Ieps,
            //        FixedPrice = x.FixedPrice,
            //        SalePrice = x.SalePrice,
            //        SupplierCost = x.SupplierCost,
            //        Product = x.Product,
            //        ProductId = x.ProductId,
            //        Qty = x.Qty,
            //        Specification = x.Specification
            //    }).ToList()
            //};
            return await InsertOrder(tenantId, 17, order);
        }

        public async Task DeleteMultiOrderPayment(string tenantId, int deletedById, int orderPaymentId)
        {
            
            await _repository.Query<string>(async (db) =>
            {
                var orderPayment =
                    (await db.QueryFirstOrDefaultAsync<OrderPayment>(
                        "SELECT * FROM orderpayments WHERE id = @orderPaymentId AND isactive = true AND tenantId = @tenantId ORDER BY number ASC; ",
                        new { tenantId, orderPaymentId }));

                if (orderPayment == null)
                    throw new UserFriendlyException("No se encontró el pago especificado");

                //Si este pago tiene un pedido asociado lanza excepción
                if (orderPayment.OrderId != null)
                    throw new UserFriendlyException("Este pago no es múltiple");

                if (orderPayment.PaymentId.HasValue)
                    throw new UserFriendlyException("No se puede eliminar este pago ya que se encuentra asociado a un complemento de pago CFDI. Cancele primero el CFDI e inténtelo de nuevo.");

                var orderIds = (await db.QueryAsync<int>("SELECT orderid FROM orderpaymentitems WHERE orderpaymentid = @orderPaymentId", new { orderPaymentId }));
                var paymentStatus = PaymentStatus.Pending;

                var query = $@"
UPDATE orderpayments SET isactive = false, modifiedbyid = @deletedById WHERE id = @orderPaymentId AND tenantId = @tenantId;
UPDATE orderpaymentitems SET isactive = false, modifiedbyid = @deletedById WHERE orderpaymentid = @orderPaymentId AND tenantId = @tenantId;
UPDATE orders SET paymentstatus = @paymentStatus, actualpaymentdate = NULL WHERE id IN ({string.Join(", ", orderIds.Select(x => x.ToString()))}) AND tenantId = @tenantId;
";
                await db.QueryAsync(query, new { tenantId, orderPaymentId, paymentStatus, deletedById });

                return "";
            });
        }

        public async Task DeleteOrderPayment(string tenantId, int deletedById, int orderId, int orderPaymentId)
        {
            await _repository.Query<string>(async (db) =>
            {
                var orderPayments =
                    (await db.QueryAsync<OrderPayment>(
                        "SELECT * FROM orderpayments WHERE orderid = @orderId AND isactive = true AND tenantId = @tenantId ORDER BY number ASC; ",
                        new { tenantId, orderId })).ToList();

                var orderPayment = orderPayments.FirstOrDefault(x => x.Id == orderPaymentId);

                if (orderPayment == null)
                    throw new UserFriendlyException("No se encontró el pago especificado");

                if (orderPayment.PaymentId.HasValue)
                    throw new UserFriendlyException("No se puede eliminar este pago ya que se encuentra asociado a un complemento de pago CFDI. Cancele primero el CFDI e inténtelo de nuevo.");

                var leftToPay = orderPayments[0].LeftToPay;

                var paymentStatus = orderPayments.Count >= 2
                    ? (int)PaymentStatus.PartiallyPaid
                    : (int)PaymentStatus.Pending;

                var actualPaymentDateQuery = paymentStatus == (int)PaymentStatus.Pending
                    ? ", actualpaymentdate = NULL "
                    : "";

                orderPayments.Remove(orderPayment);

                var index = 1;
                foreach (var op in orderPayments)
                {
                    op.LeftToPay = leftToPay;
                    op.Number = index;
                    index++;
                    leftToPay -= op.Total;
                }

                var query = $@"
UPDATE orderpayments SET isactive = false, modifiedbyid = @deletedById WHERE id = @orderPaymentId AND tenantId = @tenantId;
UPDATE orders SET paymentstatus = @paymentStatus{actualPaymentDateQuery} WHERE id = @orderId AND tenantId = @tenantId;
{string.Join("\n", orderPayments.Select(x => $"UPDATE orderpayments SET number = {x.Number}, leftToPay = {x.LeftToPay} WHERE id = {x.Id}; "))}
";
                await db.QueryAsync(query, new { tenantId, orderId, orderPaymentId, paymentStatus, deletedById });

                return "";
            });
        }

        public async Task<UpdatedPaymentStatus> InsertPayment(string tenantId, int createdBy, int orderId, DateTime date, decimal amount)
        {

            var paymentTotal = await GetOrderAndOrderPaymentsTotal(tenantId, orderId);

            if (paymentTotal.OrderPaymentsTotal >= paymentTotal.OrderTotal)
                throw new UserFriendlyException("El pago ya se encuentra completado");

            if (paymentTotal.OrderPaymentsTotal + amount > paymentTotal.OrderTotal || amount > paymentTotal.OrderTotal)
                throw new UserFriendlyException("El pago supera el total");

            var newPayment = new OrderPayment()
            {
                Number = paymentTotal.PaymentsCount + 1,
                LeftToPay = (paymentTotal.OrderTotal ?? 0) - (paymentTotal.OrderPaymentsTotal ?? 0),
                Total = amount,
                PaymentDate = date,
                OrderId = orderId
            };

            DateTime? actualPaymentDate = date;

            var paidStatus = (paymentTotal.OrderPaymentsTotal + amount >= paymentTotal.OrderTotal
                || amount >= paymentTotal.OrderTotal)
                ? (int)PaymentStatus.Paid
                : (int)PaymentStatus.PartiallyPaid;

            if (paidStatus == (int)PaymentStatus.PartiallyPaid)
                actualPaymentDate = null;

            var newOrderPayment = await _repository.Insert<OrderPayment, int>(tenantId, createdBy, newPayment);
            await _repository.Update<Order, int>(tenantId, createdBy, x => x.Id == orderId,
                new UpdateInfo<Order>()
                {
                    Expression = x => x.PaymentStatus,
                    Value = paidStatus
                },
                new UpdateInfo<Order>()
                {
                    Expression = x => x.ActualPaymentDate,
                    Value = actualPaymentDate
                });

            return new UpdatedPaymentStatus()
            {
                OrderPaymentId = newOrderPayment.Id,
                OrderIds = new int[] { },
                ActualPaymentDate = actualPaymentDate,
                PaymentStatus = paidStatus
            };
        }

        public async Task<OrderAllowedDates> GetAllowedDates(string tenantId, int id)
        {
            //WARNING: Verificar que el tenantId del order id es amigo del actual

            var order = await _repository.Query<Order>(async (db) =>
            {
                var query = @$"SELECT tenantId FROM orders WHERE id = {id}";
                return await db.QueryFirstOrDefaultAsync<Order>(query);
            });

            //Esto significa que el pedido es de un proveedor local (no verificado)
            if(order.TenantId == tenantId)
                return new OrderAllowedDates();

            var settings = await _settingsService.GetSettings(order.TenantId, new string[] { "orders" });

            return new OrderAllowedDates
            {
                TimeZone = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersTimezone)?.Value,
                RawHolidays = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersHolidays)?.Value,
                ImmediateDelivery = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersDeliveryType)?.Value == "1",
                DeliveryTypeDays = Convert.ToInt32(settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersDeliveryTypeDays)?.Value ?? "0"),
                AvailabilityForDeliverySun = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnSunday)?.Value?.ToLower() == "true",
                AvailabilityForDeliveryMon = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnMonday)?.Value?.ToLower() == "true",
                AvailabilityForDeliveryTue = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnTuesday)?.Value?.ToLower() == "true",
                AvailabilityForDeliveryWed = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnWednesday)?.Value?.ToLower() == "true",
                AvailabilityForDeliveryThu = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnThursday)?.Value?.ToLower() == "true",
                AvailabilityForDeliveryFri = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnFriday)?.Value?.ToLower() == "true",
                AvailabilityForDeliverySat = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryOnSaturday)?.Value?.ToLower() == "true",
                AvailabilityForDeliveryTimeSun = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnSunday)?.Value,
                AvailabilityForDeliveryTimeMon = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnMonday)?.Value,
                AvailabilityForDeliveryTimeTue = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnTuesday)?.Value,
                AvailabilityForDeliveryTimeWed = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnWednesday)?.Value,
                AvailabilityForDeliveryTimeThu = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnThursday)?.Value,
                AvailabilityForDeliveryTimeFri = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnFriday)?.Value,
                AvailabilityForDeliveryTimeSat = settings.FirstOrDefault(x => x.Name == ClientSettingType.OrdersAvailabilityForDeliveryTimeOnSaturday)?.Value,

            };
        }

        private async Task<OrderAndOrderPaymentsTotal> GetOrderAndOrderPaymentsTotal(string tenantId, int orderId)
        {
            return await _repository.Query<OrderAndOrderPaymentsTotal>(async (db) =>
            {
                var query = @$"SELECT

(SELECT SUM(total) as sum FROM (SELECT orders.id,
orders.total
FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
WHERE public.orders.id = @orderId AND public.orders.tenantid = @tenantId
GROUP BY orders.id) as results) as OrderTotal,

(SELECT SUM(orderpayments.total) from orderpayments WHERE orderid = @orderId AND isactive = true AND tenantid = @tenantId) as OrderPaymentsTotal,

(SELECT COUNT(*) from orderpayments WHERE orderid = @orderId AND isactive = true AND tenantid = @tenantId) as PaymentsCount";

                var totals = await db.QuerySingleAsync<OrderAndOrderPaymentsTotal>(query,
                    new { orderId, tenantId });

                totals.OrderPaymentsTotal = totals.OrderPaymentsTotal ?? 0;
                totals.OrderTotal = totals.OrderTotal ?? 0;

                //Si el pedido se pagó como parte de un grupo
                if (totals.OrderPaymentsTotal == 0)
                {
                    query = @$"SELECT 

(SELECT SUM(total) as sum FROM (SELECT orders.id,
orders.total
FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
WHERE public.orders.id = @orderId AND public.orders.tenantid = @tenantId
GROUP BY orders.id) as results) as OrderTotal,
(
    SELECT SUM(orderpayments.total) FROM orderpaymentitems 
    LEFT JOIN orderpayments ON orderpayments.id = orderpaymentitems.orderpaymentid 
    WHERE orderpaymentitems.orderid = @orderId 
    AND orderpaymentitems.isactive = true AND orderpaymentitems.tenantid = @tenantId 
    AND orderpayments.tenantid = @tenantId AND orderpayments.isactive = true
) AS OrderPaymentsTotal";

                    totals = await _repository.QuerySingle<OrderAndOrderPaymentsTotal>(query,
                        new { orderId, tenantId });

                    totals.PaymentsCount = 0;
                }

                return totals;

            });


        }

        private async Task<decimal> CalculateGetOrdersTotal(string tenantId, int[] orderIds)
        {
            //var query = @$"SELECT SUM(orders.total) from orders WHERE id = ANY(@orderIds) AND tenantid = @tenantId";

            var query = @$"SELECT 

(SELECT SUM(total) as sum FROM (SELECT orders.id,
orders.total
FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
INNER JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
WHERE public.orders.id = ANY(@orderIds) AND public.orders.tenantid = @tenantId
GROUP BY orders.id) as results) as OrderTotal
";

            return await _repository.QuerySingle<decimal>
                (query,
                new { orderIds, tenantId });
        }
    }

    public class OrderAllowedDates
    {
        public bool AvailabilityForDeliverySun { get; set; }
        public bool AvailabilityForDeliveryMon { get; set; }
        public bool AvailabilityForDeliveryTue { get; set; }
        public bool AvailabilityForDeliveryWed { get; set; }
        public bool AvailabilityForDeliveryThu { get; set; }
        public bool AvailabilityForDeliveryFri { get; set; }
        public bool AvailabilityForDeliverySat { get; set; }
        public string AvailabilityForDeliveryTimeSun { get; set; }
        public string AvailabilityForDeliveryTimeMon { get; set; }
        public string AvailabilityForDeliveryTimeTue { get; set; }
        public string AvailabilityForDeliveryTimeWed { get; set; }
        public string AvailabilityForDeliveryTimeThu { get; set; }
        public string AvailabilityForDeliveryTimeFri { get; set; }
        public string AvailabilityForDeliveryTimeSat { get; set; }
        public bool ImmediateDelivery { get; set; }
        public string TimeZone { get; set; }
        public string RawHolidays { get; set; }
        public int DeliveryTypeDays { get; set; }
    }

    public class TenantIdAndCreatedBy
    {
        public string TenantId { get; set; }
        public int ExternalUserId { get; set; }
    }

    public class OrderTotal
    {
        public int Id { get; set; }
        public decimal Total { get; set; }
    }

    public class UpdatedPaymentStatus
    {
        public int OrderPaymentId { get; set; }
        public int[] OrderIds { get; set; }
        public int PaymentStatus { get; set; }
        public DateTime? ActualPaymentDate { get; set; }
    }

    public class GetOrderPaymentsResponse
    {
        public int? PaymentCfdiId { get; set; }
        public decimal LeftToPay { get; set; }
        public decimal OrderTotal { get; set; }
        public DateTime? LastPaymentDate { get; set; }
        public decimal OrderPaymentsTotal { get; set; }
        public bool IsPaymentComplete { get; set; }
        public bool CanGenerateCfdi { get; set; }
        public int[] OrderIds { get; set; }
        public bool PaidInMultiPayment { get; set; }
        public IEnumerable<OrderPayment> Payments { get; set; }
    }

    public class OrderAndOrderPaymentsTotal
    {
        public int PaymentsCount { get; set; }
        public decimal? OrderPaymentsTotal { get; set; }
        public decimal? OrderTotal { get; set; }
    }

    public class OrderIdAndFolio
    {
        public int OrderId { get; set; }
        public string Folio { get; set; }
    }

    public class CancelOrderWebhookPayload
    {
        public string[] Folios { get; set; }
    }

    public class DeliveryStatusWebhookPayload
    {
        public DeliveryStatus DeliveryStatus { get; set; }
        public string[] Folios { get; set; }
    }

    public class DeliveryDateWebhookPayload
    {
        public DateTime DeliveryDate { get; set; }
        public string[] Folios { get; set; }
    }

    public class PromisedPaymentDateWebhookPayload
    {
        public DateTime PromisedPaymentDate { get; set; }
        public string[] Folios { get; set; }
    }

    public class SumAndTotal
    {
        public decimal Sum { get; set; }
        public int Total { get; set; }
    }
}