using Dapper;
using Microsoft.AspNetCore.JsonPatch;
using MisIngredientesVue.Core.Exceptions;
using MisIngredientesVue.Core.Extensions;
using MisIngredientesVue.Core.Models;
using Npgsql;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MisIngredientesVue.Core.Services
{
    public interface IOrderTemplatesService
    {
        Task<IEnumerable<ClientBranch>> GetBranchesByTemplateId(string tenantId, int templateId);
        Task<IEnumerable<ClientBranch>> GetClientBranchesByTemplateId(string tenantId, int templateId);
        Task<IEnumerable<ClientBranch>> GetBranchesByPublicTemplateId(string templateId);
        Task<FindResults<OrderTemplate>> GetTemplates(string tenantId, int skip, int take, string sortBy, string sortOrder, string q);
        Task<FindResults<OrderTemplate>> GetClientTemplates(string tenantId, int skip, int take, string sortBy, string sortOrder, string q);
        Task<OrderTemplate> GetOrderTemplateById(string tenantId, int templateId);
        Task<OrderTemplate> GetClientOrderTemplateById(string tenantId, int templateId);
        Task<OrderTemplate> GetClientOrderTemplateBySupplierId(string tenantId, int supplierId);
        Task<OrderTemplate> GetOrderTemplateByPublicId(string publicId);
        Task<OrderTemplate> InsertOrderTemplate(string tenantId, int createdBy, OrderTemplate orderTemplate);
        Task<OrderTemplate> InsertClientOrderTemplate(string tenantId, int createdBy, OrderTemplate orderTemplate);
        Task UpdateOrderTemplate(string tenantId, int updatedBy, OrderTemplate orderTemplate);
        Task UpdateGeneralOrderTemplate(string tenantId, int updatedBy, OrderTemplate orderTemplate);
        Task UpdateOrderTemplateMetadata(string tenantId, int updatedBy, OrderTemplate orderTemplate);
        Task UpdateClientOrderTemplateMetadata(string tenantId, int updatedBy, OrderTemplate orderTemplate);
        Task DeleteOrderTemplates(string tenantId, int deletedById, int[] ids);
        Task PatchOrderTemplate(string tenantId, int modifiedBy, int id, JsonPatchDocument<OrderTemplate> changes);
        Task<MemoryStream> GetTemplateExcel(string tenantId, int id);
        Task<MemoryStream> GetPublicTemplateExcel(string templateId);
        Task<MemoryStream> GetClientTemplateExcel(string tenantId, int id);
        Task SendByEmail(string tenantId, int id, string[] recipients, string emailBody, bool includeAds);
    }

    public class OrderTemplatesService : IOrderTemplatesService
    {
        private readonly IRepository _repository;
        private readonly IEmailService _emailService;
        private readonly ISettingsService _settingsService;

        public OrderTemplatesService(IRepository repository, IEmailService emailService,
            ISettingsService settingsService)
        {
            _repository = repository;
            _emailService = emailService;
            _settingsService = settingsService;
        }

        public async Task DeleteOrderTemplates(string tenantId, int deletedById, int[] ids)
        {
            var queryTemplate = $@"

_count = (SELECT COUNT(*) FROM orders WHERE orders.templateid = [ID] AND tenantId = '{tenantId}');
  IF (_count = 0) THEN
  DELETE FROM ordertemplateproducts WHERE ordertemplateid = [ID] AND tenantId = '{tenantId}';
  DELETE FROM ordertemplatebrands WHERE ordertemplateid = [ID] AND tenantId = '{tenantId}';
  UPDATE ordertemplates SET isactive = false, modifiedbyid = {deletedById} WHERE id = [ID] AND tenantId = '{tenantId}';
  ELSE
  UPDATE ordertemplateproducts SET isactive = False WHERE ordertemplateid = [ID] AND tenantId = '{tenantId}';
  UPDATE ordertemplatebrands SET isactive = False WHERE ordertemplateid = [ID] AND tenantId = '{tenantId}';
  UPDATE ordertemplates SET isactive = false, modifiedbyid = {deletedById} WHERE id = [ID] AND tenantId = '{tenantId}';
  END IF;
";

            await _repository.Query<dynamic>(async (db) =>
            {
                var query = $@"
                DO $$
                DECLARE _count int;
                BEGIN
                {string.Join(" ", ids.Select(x => queryTemplate.Replace("[ID]", x.ToString())).ToArray())}
                END;
                $$;
                ";

                //Console.WriteLine(query);
                return await db.QueryAsync(query);
            });
        }

        //Un template puede tener asignadas varias marcas, y a su vez una marca tiene varias sucursales, este metodo es para obtener las sucursales en base al templateId
        public async Task<IEnumerable<ClientBranch>> GetBranchesByTemplateId(string tenantId, int templateId)
        {
            //WARNING: Sólo mostrar si el tenantId es amigo
            var query = $@"SELECT clientbranches.*, clientbrands.* FROM clientbranches 
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
WHERE clientbrandid IN (SELECT clientbrandid FROM ordertemplatebrands WHERE ordertemplateid = @templateId)";

            return await _repository.Query(async (db) =>
            {
                var branches = new List<ClientBranch>();

                //Cada sucursal tiene su marca (un marca tiene varias sucursales)
                await db.QueryAsync<ClientBranch, ClientBrand, ClientBranch>
                    (query, (bch, br) =>
                    {
                        bch.ClientBrand = br;

                        if (!branches.Any(x => x.Id == bch.Id))
                            branches.Add(bch);

                        return bch;
                    },
                    new { tenantId, templateId });

                return branches;
            });
        }

        public async Task<IEnumerable<ClientBranch>> GetClientBranchesByTemplateId(string tenantId, int templateId)
        {
            var query = $@"SELECT clientbranches.*, clientbrands.* FROM ordertemplatebranches
LEFT JOIN clientbranches ON ordertemplatebranches.clientbranchid = clientbranches.id
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
WHERE ordertemplatebranches.ordertemplateid = @templateId AND ordertemplatebranches.tenantId = @tenantId";

            return await _repository.Query(async (db) =>
            {
                var branches = new List<ClientBranch>();

                //Cada sucursal tiene su marca (un marca tiene varias sucursales)
                await db.QueryAsync<ClientBranch, ClientBrand, ClientBranch>
                    (query, (bch, br) =>
                    {
                        bch.ClientBrand = br;

                        if (!branches.Any(x => x.Id == bch.Id))
                            branches.Add(bch);

                        return bch;
                    },
                    new { tenantId, templateId });

                return branches;
            });
        }

        public async Task<IEnumerable<ClientBranch>> GetBranchesByPublicTemplateId(string templateId)
        {
            var query = $@"SELECT DISTINCT public.clientbranches.id, public.clientbranches.name, public.clientbrands.id, public.clientbrands.name
FROM public.clientbranches
LEFT JOIN public.clientbrands ON public.clientbranches.clientbrandid = public.clientbrands.id 
WHERE public.clientbranches.clientbrandid IN (SELECT clientbrandid FROM
												 public.ordertemplatebrands
												 WHERE ordertemplateid = (SELECT id FROM public.ordertemplates WHERE publicid = @templateId))";

            return await _repository.Query(async (db) =>
            {
                return await db.QueryAsync<ClientBranch, ClientBrand, ClientBranch>
                    (query, (bch, br) =>
                    {
                        bch.ClientBrand = br;
                        return bch;
                    },
                    new { templateId });
            });
        }

        public async Task<OrderTemplate> GetOrderTemplateByPublicId(string id)
        {
            OrderTemplate orderTemplate = null;

            await _repository.Query(async (db) =>
            {
                return (await db.QueryAsync<OrderTemplate, OrderTemplateBrand, Client, OrderTemplateProduct, Product, Unit, OrderTemplate>(

                @"SELECT ordertemplates.*, clientmintotals.mintotal, ordertemplatebrands.*, clients.*, ordertemplateproducts.*, products.*, units.* FROM ordertemplates
                LEFT JOIN clientmintotals ON clientmintotals.clientid = ordertemplates.clientid
                LEFT JOIN ordertemplatebrands ON ordertemplatebrands.ordertemplateid = ordertemplates.id
                LEFT JOIN clients ON ordertemplates.clientid = clients.id
                LEFT JOIN ordertemplateproducts ON ordertemplateproducts.ordertemplateid = ordertemplates.id
                LEFT JOIN products ON ordertemplateproducts.productid = products.id
                LEFT JOIN units ON products.unitId = units.id
                WHERE ordertemplates.isactive = true AND ordertemplates.publicid = @id", (ot, otb, c, otp, p, u) =>

                {

                    if (orderTemplate == null)
                    {
                        orderTemplate = ot;
                        orderTemplate.Brands = new List<ClientBrand>();
                        orderTemplate.Products = new List<OrderTemplateProduct>();
                    }

                    if (otb != null && !orderTemplate.Brands.Any(x => x.Id == otb.ClientBrandId))
                        orderTemplate.Brands.Add(new ClientBrand() { Id = otb.ClientBrandId });

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

                    if (otp != null && !orderTemplate.Products.Any(x => x.Id == otp.Id))
                    {
                        otp.Product = p;
                        otp.Product.Unit = u;
                        orderTemplate.Products.Add(otp);
                    }

                    return orderTemplate;

                }, new { id }))
                .Distinct()
                .ToList();

            });

            if (orderTemplate == null)
                throw new UserFriendlyException("Lo sentimos pero esta plantilla no existe o ha sido eliminada, por favor ponte en contacto con tu proveedor.");

            var now = DateTime.UtcNow;

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

            foreach (var uniqueTenantId in orderTemplate.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)
                });
            }

            foreach (var product in orderTemplate.Products)
            {
                product.Product.PriceExpirationStatus = "Vigente";

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

                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";
            }


            return orderTemplate;
        }

        public async Task<OrderTemplate> GetClientOrderTemplateBySupplierId(string tenantId, int supplierId)
        {
            var productsQuery = @$"SELECT ordertemplateproducts.*, (SELECT name FROM suppliers WHERE id = @supplierId) as SuppliersDisplay, products.*, units.* FROM ordertemplateproducts
LEFT JOIN ordertemplates ON ordertemplates.id = ordertemplateproducts.ordertemplateid
LEFT JOIN products ON ordertemplateproducts.productid = products.id
LEFT JOIN units ON products.unitId = units.id
WHERE ordertemplates.clientid = (SELECT id FROM clients WHERE tenantId = @tenantId) 
AND ordertemplateproducts.tenantid = (SELECT tenantId FROM suppliers WHERE id = @supplierId)";

            var maxMinOrderQuery = @$"SELECT mintotal FROM clientmintotals 
WHERE clientid = (SELECT id FROM clients WHERE tenantId = @tenantId) AND tenantid = (SELECT tenantId FROM suppliers WHERE id = @supplierId)";

            return await _repository.Query<OrderTemplate>(async (db) =>
            {
                decimal maxMinOrder = 0;

                //TODO: Quitar este catch vacío
                try
                {
                    maxMinOrder = await db.QueryFirstAsync<decimal>(maxMinOrderQuery, new { tenantId, supplierId });
                }
                catch
                {

                }

                var products = (await db.QueryAsync<OrderTemplateProduct, Product, Unit, OrderTemplateProduct>(productsQuery,
                    (otp, p, u) =>
                    {
                        otp.Product = p;
                        otp.Product.Unit = u;
                        return otp;
                    },
                    new { tenantId, supplierId })).DistinctBy(x => x.ProductId).ToList();

                var now = DateTime.UtcNow;

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

                foreach (var uniqueTenantId in 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 = 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 productId in distinctProductIds)
                    {
                        lastPrices.Add(multi.Read<OrderProduct>().FirstOrDefault());
                    }
                }

                foreach (var product in products)
                {
                    product.Product.PriceExpirationStatus = "Vigente";

                    var priceDiff = lastPrices.Any(x => x.ProductId == product.ProductId)
                   ? (product.FixedPrice ?? 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"));
                    }

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

                    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";
                }

                var allowedDates = await GetAllowedDatesBySupplierId(supplierId);

                return new OrderTemplate()
                {
                    //Se necesita el ID de la plantilla aquí (al menos el del primero), para que funcione
                    //la carga de sucursales
                    //Id = products.Count > 0 ? products[0].OrderTemplateId : 0,
                  
                    Products = products,
                    Name = products.Count > 0 ? products[0].SuppliersDisplay : "",
                    IsVerified = true,
                    AllowedDates = allowedDates,
                    MinTotal = maxMinOrder,
                };
            });

        }

        private async Task<OrderTemplate> GetGeneralOrderTemplate(string tenantId)
        {
            return await _repository.Query(async (db) =>
            {
                //Sólo productos de proveedores verificados
                var dbProducts = await db.QueryAsync<OrderTemplateProduct, Product, Unit, OrderTemplateProduct>(
@$"SELECT ordertemplateproducts.*, True as preventDelete, products.*, units.*, (CASE WHEN public.products.tenantid <> @tenantId
THEN(SELECT name from suppliers WHERE suppliers.tenantid = public.products.tenantid)
ELSE '' END) as externalsupplier FROM ordertemplateproducts
LEFT JOIN products ON ordertemplateproducts.productid = products.id
LEFT JOIN units ON products.unitId = units.id
LEFT JOIN ordertemplates ON ordertemplates.id = ordertemplateproducts.ordertemplateid
WHERE (clientid = (select id from clients where tenantId = @tenantId) AND ordertemplates.tenantId <> @tenantId) AND ordertemplates.IsActive = True

UNION

SELECT generalordertemplateproducts.id, -1 as ordertemplateid, generalordertemplateproducts.productId, 
generalordertemplateproducts.qty, generalordertemplateproducts.fixedprice, generalordertemplateproducts.specification,
generalordertemplateproducts.tenantId, generalordertemplateproducts.createdbyid, generalordertemplateproducts.created,
generalordertemplateproducts.modifiedbyid, generalordertemplateproducts.modified, generalordertemplateproducts.isactive, 
False as preventDelete, products.*, units.*, (CASE WHEN public.products.tenantid <> @tenantId
THEN(SELECT name from suppliers WHERE suppliers.tenantid = public.products.tenantid)
ELSE '' END) as externalsupplier FROM generalordertemplateproducts
LEFT JOIN products ON generalordertemplateproducts.productid = products.id
LEFT JOIN units ON products.unitId = units.id
WHERE generalordertemplateproducts.tenantId = @tenantId",               
                (otp, p, unit) =>
                {
                    otp.Product = p;
                    otp.Product.Unit = unit;

                    return otp;

                }, new { tenantId });

                var products = dbProducts.GroupBy(x => x.ProductId).Select(x => x.First()).ToList();

                var now = DateTime.UtcNow;

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

                foreach (var uniqueTenantId in 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 = 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 productId in distinctProductIds)
                    {
                        lastPrices.Add(multi.Read<OrderProduct>().FirstOrDefault());
                    }
                }

                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
                    if (!string.IsNullOrEmpty(x.Product.ClientSupplierTenantId))
                        return x.Product.ClientSupplierTenantId;

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

                //Para obtener la lista de proveedores externos
                var suppliersQuery = @$"SELECT id, tenantId, name FROM suppliers where tenantId IN ({string.Join(",", groupedProductsBySupplier.Select(x => x.Key).Distinct().Select(x => $"'{x}'"))})";
                var suppliers = await db.QueryAsync<Supplier>(suppliersQuery);

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

                foreach (var product in products)
                {
                    if(product.OrderTemplateId == -1) //Es un producto adicional a la plantilla general
                    {
                        product.Product.ClientSupplier = internalSuppliers.FirstOrDefault(x => x.Id == product.Product.ClientSupplierId);
                    }
                    else if (string.IsNullOrEmpty(product.Product.ExternalSupplier))
                    {
                        product.Product.ExternalSupplier = suppliers.FirstOrDefault(x => x.TenantId == product.TenantId)?.Name;
                    }

                    product.Product.PriceExpirationStatus = "Vigente";

                    var priceDiff = lastPrices.Any(x => x.ProductId == product.ProductId)
                    ? (product.FixedPrice ?? 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"));
                    }

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

                    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";
                }


                return new OrderTemplate()
                {
                    Name = "General",
                    Id = -1,
                    IsVerified = true,
                    Products = products.ToList()
                };
            });
        }


        public async Task<OrderTemplate> GetClientOrderTemplateById(string tenantId, int id)
        {
            if (id == 0) //plantilla general
                return await GetGeneralOrderTemplate(tenantId);

            OrderTemplate orderTemplate = null;

            await _repository.Query(async (db) =>
            {
                var orderTemplates = (await db.QueryAsync<OrderTemplate, OrderTemplateBrand, Client, OrderTemplateProduct, Product, Unit, Supplier, OrderTemplate>(

@"SELECT ordertemplates.*, clientmintotals.mintotal, ordertemplatebrands.*, clients.*, ordertemplateproducts.*,
products.*,
(CASE WHEN public.products.tenantid <> @tenantId
THEN(SELECT name from suppliers WHERE suppliers.tenantid = public.products.tenantid)
ELSE '' END) as externalsupplier,
units.*, suppliers.* FROM ordertemplates
LEFT JOIN clientmintotals ON clientmintotals.clientid = ordertemplates.clientid
LEFT JOIN ordertemplatebrands ON ordertemplatebrands.ordertemplateid = ordertemplates.id
LEFT JOIN clients ON ordertemplates.clientid = clients.id
LEFT JOIN ordertemplateproducts ON ordertemplateproducts.ordertemplateid = ordertemplates.id
LEFT JOIN products ON ordertemplateproducts.productid = products.id
LEFT JOIN units ON products.unitId = units.id
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
WHERE ordertemplates.id = @id AND (ordertemplates.tenantid = @tenantId OR ordertemplates.clientid = (select id from clients where tenantId = @tenantId))", (ot, otb, c, otp, p, u, s) =>

                {

                    if (orderTemplate == null)
                    {
                        orderTemplate = ot;
                        orderTemplate.Brands = new List<ClientBrand>();
                        orderTemplate.Products = new List<OrderTemplateProduct>();
                        orderTemplate.Branches = new List<ClientBranch>();
                        orderTemplate.Suppliers = new List<Supplier>();
                    }

                    if (otb != null && !orderTemplate.Brands.Any(x => x.Id == otb.ClientBrandId))
                        orderTemplate.Brands.Add(new ClientBrand() { Id = otb.ClientBrandId });

                    //if (otbr != null && !orderTemplate.Branches.Any(x => x.Id == otbr.ClientBranchId))
                    //    orderTemplate.Branches.Add(new ClientBranch() { Id = otbr.ClientBranchId });

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

                    if (otp != null && !orderTemplate.Products.Any(x => x.Id == otp.Id))
                    {
                        otp.Product = p;
                        otp.Product.Unit = u;
                        otp.Product.ClientSupplier = s;

                        if (!orderTemplate.Products.Any(x => x.ProductId == otp.ProductId))
                            orderTemplate.Products.Add(otp);
                    }

                    return orderTemplate;

                }, new { id, tenantId }))
                .Distinct()
                .ToList();

                if (orderTemplate != null)
                {
                    var distinctProductIds = orderTemplate.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;");
                    }

                    orderTemplate.Branches = (await db.QueryAsync<ClientBranch>(
                        $@"SELECT clientbranches.* FROM ordertemplatebranches
LEFT JOIN clientbranches ON ordertemplatebranches.clientbranchid = clientbranches.id
WHERE ordertemplateid = {orderTemplate.Id}")).ToList();

                    var lastPrices = new List<OrderProduct>();

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

                    foreach(var product in orderTemplate.Products)
                    {
                        var priceDiff = lastPrices.Any(x => x.ProductId == product.ProductId)
                           ? (product.FixedPrice ?? 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"));
                        }
                    }

                }

                return orderTemplates;

            });

            if (orderTemplate == null)
                throw new UserFriendlyException("No se encontró la plantilla especificada.");

            orderTemplate.IsVerified = orderTemplate.TenantId != tenantId;

            var now = DateTime.UtcNow;

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

            foreach (var uniqueTenantId in orderTemplate.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)
                });
            }

            foreach (var product in orderTemplate.Products)
            {
                product.PreventDelete = orderTemplate.IsVerified;

                product.Product.PriceExpirationStatus = "Vigente";

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

                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";
            }

            orderTemplate.AllowedDates = await GetAllowedDates(tenantId, orderTemplate.Id);
           
            return orderTemplate;
        }

        public async Task<OrderAllowedDates> GetAllowedDatesBySupplierId(int id)
        {
            var supplier = await _repository.Query<Supplier>(async (db) =>
            {
                var query = @$"SELECT tenantId FROM suppliers WHERE id = {id}";
                return await db.QueryFirstOrDefaultAsync<Supplier>(query);
            });


            var settings = await _settingsService.GetSettings(supplier.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,

            };
        }

        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 ordertemplates WHERE id = {id}";
                return await db.QueryFirstOrDefaultAsync<Order>(query);
            });

            //Esto significa que la plantilla 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,

            };
        }

        public async Task<OrderTemplate> GetOrderTemplateById(string tenantId, int id)
        {
            OrderTemplate orderTemplate = null;

            await _repository.Query(async (db) =>
            {
                var orderTemplates = (await db.QueryAsync<OrderTemplate, OrderTemplateBrand, Client, OrderTemplateProduct, Product, Unit, OrderTemplate>(

@"SELECT ordertemplates.*, clientmintotals.mintotal, ordertemplatebrands.*, clients.*, ordertemplateproducts.*, products.*, units.* FROM ordertemplates
LEFT JOIN clientmintotals ON clientmintotals.clientid = ordertemplates.clientid
LEFT JOIN ordertemplatebrands ON ordertemplatebrands.ordertemplateid = ordertemplates.id
LEFT JOIN clients ON ordertemplates.clientid = clients.id
LEFT JOIN ordertemplateproducts ON ordertemplateproducts.ordertemplateid = ordertemplates.id
LEFT JOIN products ON ordertemplateproducts.productid = products.id
LEFT JOIN units ON products.unitId = units.id
WHERE ordertemplates.id = @id AND ordertemplates.tenantid = @tenantId", (ot, otb, c, otp, p, u) =>

                {

                    if (orderTemplate == null)
                    {
                        orderTemplate = ot;
                        orderTemplate.Brands = new List<ClientBrand>();
                        orderTemplate.Products = new List<OrderTemplateProduct>();
                    }

                    if(otb != null && !orderTemplate.Brands.Any(x => x.Id == otb.ClientBrandId))
                        orderTemplate.Brands.Add(new ClientBrand() { Id = otb.ClientBrandId });

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

                    if (otp != null && !orderTemplate.Products.Any(x => x.Id == otp.Id))
                    {
                        otp.Product = p;
                        otp.Product.Unit = u;

                        if (!orderTemplate.Products.Any(x => x.ProductId == otp.ProductId))
                            orderTemplate.Products.Add(otp);
                    }

                    return orderTemplate;

                }, new { id, tenantId }))
                .Distinct()
                .ToList();


                var distinctProductIds = orderTemplate.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 productId in distinctProductIds)
                    {
                        lastPrices.Add(multi.Read<OrderProduct>().FirstOrDefault());
                    }
                }


                foreach (var product in orderTemplate.Products)
                {
                    var priceDiff = lastPrices.Any(x => x.ProductId == product.ProductId)
                       ? (product.FixedPrice ?? 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"));
                    }
                }


                return orderTemplates;

            });

            if (orderTemplate == null)
                throw new UserFriendlyException("No se encontró la plantilla especificada.");

            var now = DateTime.UtcNow;

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

            foreach (var uniqueTenantId in orderTemplate.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)
                });
            }


            foreach (var product in orderTemplate.Products)
            {
                product.Product.PriceExpirationStatus = "Vigente";

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

                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";
            }

            return orderTemplate;
        }

        public async Task<MemoryStream> GetPublicTemplateExcel(string templateId)
        {
            var orderTemplate = await GetOrderTemplateByPublicId(templateId);

            var memoryStream = new MemoryStream();

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

                var colIndex = 3;

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

                ws.Cells[1, 1].Value = "Artículo";
                ws.Cells[1, 2].Value = "Unidad";
                ws.Cells[1, 3].Value = "Precio";

                var rowIndex = 2;

                foreach (var product in orderTemplate.Products.OrderBy(x => x.Product.Name))
                {
                    ws.SetValue(rowIndex, 1, product.Product.Name);
                    ws.SetValue(rowIndex, 2, product.Product.Unit.Name);
                    ws.SetValue(rowIndex, 3, OrderTemplateProduct.GetActualSalePrice(product));
                    rowIndex++;
                }

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

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;


        }

        public async Task<MemoryStream> GetTemplateExcel(string tenantId, int id)
        {
            var orderTemplate = id == 0 ? await GetGeneralOrderTemplate(tenantId) : await GetOrderTemplateById(tenantId, id);

            var memoryStream = new MemoryStream();

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

                var colIndex = 5;

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

                ws.Cells[1, 1].Value = "Código";
                ws.Cells[1, 2].Value = "Artículo";
                ws.Cells[1, 3].Value = "Unidad";
                ws.Cells[1, 4].Value = "Costo";
                ws.Cells[1, 5].Value = "Precio catálogo";
                ws.Cells[1, 6].Value = "Precio fijo";

                var rowIndex = 2;

                foreach (var product in orderTemplate.Products.OrderBy(x => x.Product.Name))
                {
                    ws.SetValue(rowIndex, 1, product.Product.Code);
                    ws.SetValue(rowIndex, 2, product.Product.Name);
                    ws.SetValue(rowIndex, 3, product.Product.Unit.Name);
                    ws.SetValue(rowIndex, 4, product.Product.SupplierCost);
                    ws.SetValue(rowIndex, 5, product.Product.SalePrice);
                    ws.SetValue(rowIndex, 6, product.FixedPrice);
                    rowIndex++;

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

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

                    //foreach (var branch in item.Branches)
                    //{
                    //    ws.SetValue(rowIndex, colIndex, $"{branch.Client.Name} ({branch.Name})");
                    //    rowIndex++;
                    //}

                    //foreach (var driver in item.Drivers)
                    //{
                    //    ws.SetValue(rowIndex, colIndex, driver.ToString());
                    //    rowIndex++;
                    //}

                    //rowIndex++;
                    //colIndex++;
                }

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

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;


        }


        public async Task<MemoryStream> GetClientTemplateExcel(string tenantId, int id)
        {
            var orderTemplate = id == 0 ? await GetGeneralOrderTemplate(tenantId) : await GetOrderTemplateById(tenantId, id);

            var memoryStream = new MemoryStream();

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

                var colIndex = 5;

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

                ws.Cells[1, 1].Value = "Código";
                ws.Cells[1, 2].Value = "Artículo";
                ws.Cells[1, 3].Value = "Unidad";
                ws.Cells[1, 4].Value = "Precio";
                ws.Cells[1, 5].Value = "Proveedor";
                ws.Cells[1, 6].Value = "Vigencia de precio";

                var rowIndex = 2;

                foreach (var product in orderTemplate.Products.OrderBy(x => x.Product.Name))
                {
                    ws.SetValue(rowIndex, 1, product.Product.Code);
                    ws.SetValue(rowIndex, 2, product.Product.Name);
                    ws.SetValue(rowIndex, 3, product.Product.Unit.Name);
                    ws.SetValue(rowIndex, 4, OrderTemplateProduct.GetActualSalePrice(product));
                    ws.SetValue(rowIndex, 5, product.Product.ClientSupplier?.Name ?? product.Product.ExternalSupplier);
                    ws.SetValue(rowIndex, 6, product.Product.PriceExpirationStatus);
                    rowIndex++;
                }

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

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        public async Task<FindResults<OrderTemplate>> GetClientTemplates(string tenantId, int skip, int take, string sortBy, string sortOrder, string q)
        {
            return await _repository.Query(async (db) =>
            {
                if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
                    sortOrder = "DESC";

                if (string.IsNullOrEmpty(sortBy))
                    sortBy = "Name";

                sortOrder = sortOrder.ToUpper();

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

                if (!(new[] { "name" }.Contains(sortBy)))
                    sortBy = "ordertemplates.name";

                //                var query = $@"SELECT ordertemplates.*,  (CASE WHEN public.ordertemplates.tenantid <> @tenantId
                //THEN(SELECT name from suppliers WHERE suppliers.tenantid = public.ordertemplates.tenantid)
                //ELSE '' END) as suppliersdisplay
                //FROM ordertemplates
                //WHERE (clientid = (select id from clients where tenantId = @tenantId) OR ordertemplates.tenantId = @tenantId) AND ordertemplates.IsActive = True 
                //ORDER BY {sortBy} {sortOrder}
                //LIMIT {take}
                //OFFSET {skip}";

                var suppliersQuery = @"SELECT id, name FROM suppliers
LEFT JOIN client_suppliers ON suppliers.tenantId = client_suppliers.supplier_tenantId
WHERE client_tenantid = @tenantId";

                var suppliers = (await db.QueryAsync<Supplier>(suppliersQuery, new { tenantId })).ToList();

                var query = $@"SELECT ordertemplates.*, clientmintotals.mintotal
                FROM ordertemplates
                LEFT JOIN clientmintotals ON clientmintotals.clientid = ordertemplates.clientid
                WHERE ordertemplates.tenantId = @tenantId AND ordertemplates.IsActive = True 
                ORDER BY {sortBy} {sortOrder}
                LIMIT {take}
                OFFSET {skip}";

                var countQuery = $@"SELECT COUNT(*) FROM ordertemplates
WHERE ordertemplates.tenantId = @tenantId AND ordertemplates.IsActive = True";

                var templates = (await db.QueryAsync<OrderTemplate>(query, new { tenantId })).ToList();

                foreach(var supplier in suppliers)
                {
                    templates.Insert(0, new OrderTemplate()
                    {
                        Id = supplier.Id * -1, //Negativo para diferenciarlo de los IDs de las plantillas
                        IsActive = true,
                        IsVerified = true,
                        Name = supplier.Name,
                        SuppliersDisplay = $"{supplier.Name}"
                    });
                }


                foreach(var template in templates)
                {
                    template.IsVerified = (template.TenantId != tenantId);
                    template.SuppliersDisplay = template.IsVerified ? template.SuppliersDisplay : "Varios";
                    template.Name = template.IsVerified ? $"Plantilla global de {template.SuppliersDisplay}" : template.Name;
                    template.Suppliers = new List<Supplier>();
                }

                //await db.QueryAsync<OrderTemplate, Supplier, OrderTemplate>(query, (ot, s) =>
                //{

                //    var template = templates.FirstOrDefault(x => x.Id == ot.Id);

                //    if (template == null)
                //    {
                //        templates.Add(ot);
                //        template = ot;
                //        template.Suppliers = new List<Supplier>();
                //    }

                //    if (s != null)
                //        template.Suppliers.Add(s);

                //    return ot;

                //}, new { tenantId });

                var count = await db.QueryFirstAsync<int>(countQuery, new { tenantId });
                count += suppliers.Count;

                //for(var i=0; i < templates.Count; i++)
                //{

                //    //if (templates[i].Suppliers.Count > 0)
                //    //{
                //    //    templates[i].SuppliersDisplay = string.Join(", ", templates[i].Suppliers.Select(x => x.Name));
                //    //}
                //    if (templates[i].Suppliers.Count == 0)
                //    {
                //        templates[i].SuppliersDisplay = "Varios";

                //    }
                //    else
                //    {
                //        templates[i].Name = $"Plantilla global de {templates[i].SuppliersDisplay}";
                //        templates[i].IsVerified = true;
                //    }
                //}


                return new FindResults<OrderTemplate>()
                {
                    Results = templates.ToArray(),
                    Total = count
                };
            });
        }

        public async Task<FindResults<OrderTemplate>> GetTemplates(string tenantId, int skip, int take, string sortBy, string sortOrder, string q)
        {
            return await _repository.Query(async (db) =>
            {

                var dynamicParams = new DynamicParameters();
                dynamicParams.Add("@tenantId", tenantId);

                if (string.IsNullOrEmpty(sortBy) || sortBy == "name")
                    sortBy = "ordertemplates.name";

                if (!string.IsNullOrEmpty(sortBy) && sortBy != "name")
                    sortBy = "ordertemplates.name";

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

                sortOrder = sortOrder.ToUpper();

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

                var whereQuery = "";

                if (!string.IsNullOrEmpty(q))
                {
                    var term = q.PrepareQuery();
                    whereQuery = " ordertemplates.Name ~* @term AND ";
                    dynamicParams.Add("term", term);
                }

                var resultsQuery = $@"SELECT ordertemplates.*, clientmintotals.mintotal, clients.* FROM ordertemplates 
            LEFT JOIN clientmintotals ON clientmintotals.clientid = ordertemplates.clientid
            LEFT JOIN clients ON clients.Id = ordertemplates.ClientId
            WHERE {whereQuery} ordertemplates.TenantId = @tenantId
            AND ordertemplates.IsActive = True 
            AND clients.IsActive = true  
            ORDER BY {sortBy} {sortOrder}
            LIMIT {take} 
            OFFSET {skip}";

                var countQuery = $@"SELECT COUNT(*) FROM ordertemplates 
            LEFT JOIN clients ON clients.Id = ordertemplates.ClientId
            WHERE {whereQuery} ordertemplates.TenantId = @tenantId
            AND ordertemplates.IsActive = True 
            AND clients.IsActive = true";

                var orderTemplates = await db.QueryAsync<OrderTemplate, Client, OrderTemplate>(resultsQuery, (ot, cl) =>
                {
                    if (cl != null)
                        ot.Client = cl;

                    return ot;
                }, dynamicParams);


                foreach (var template in orderTemplates)
                {
                    template.IsVerified = template.Client.IsVerified;
                }

                return new FindResults<OrderTemplate>()
                {
                    Results = orderTemplates.ToArray(),
                    Total = await db.QueryFirstAsync<int>(countQuery, dynamicParams)
                };

            });

            //return await _repository.Find<OrderTemplate, int>(tenantId, x => x.Name.Contains(q), skip, take, sortBy, sortOrder,
            //    null,
            //    @"LEFT JOIN clients ON clients.Id = ordertemplates.ClientId",
            //    (async (db, query, dparams) =>
            //    {
            //        return await db.QueryAsync<OrderTemplate, Client, OrderTemplate>(query, (ot, cl) =>
            //        {
            //            if (cl != null)
            //                ot.Client = cl;

            //            return ot;

            //        }, dparams);
            //    })
            //    );
        }

        public async Task<OrderTemplate> InsertOrderTemplate(string tenantId, int createdBy, OrderTemplate orderTemplate)
        {
            orderTemplate.PublicId = Guid.NewGuid().ToString("N").ToLower();

            var strBuilder = new StringBuilder();

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

                var orderTemplateId = await db.QueryFirstAsync<int>(_repository.GetInsertQuery<OrderTemplate, int>(tenantId, createdBy, orderTemplate, ref insertTemplateParams, "id"), insertTemplateParams);

                var dynamicParameters = new DynamicParameters();

                foreach (var brand in orderTemplate.Brands)
                {
                    strBuilder.Append(_repository.GetInsertQuery<OrderTemplateBrand, int>
                        (tenantId, createdBy,
                        new OrderTemplateBrand() { TenantId = tenantId, ClientBrandId = brand.Id, OrderTemplateId = orderTemplateId }, 
                        ref dynamicParameters));
                }

                if (orderTemplate?.Products != null)
                {
                    foreach (var product in orderTemplate.Products)
                    {
                        product.TenantId = tenantId;
                        product.OrderTemplateId = orderTemplateId;

                        strBuilder.Append(_repository.GetInsertQuery<OrderTemplateProduct, int>
                            (tenantId, createdBy,
                            product,
                            ref dynamicParameters));
                    }
                }

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

                orderTemplate.Id = orderTemplateId;
                return orderTemplate;
            });
        }

        public async Task<OrderTemplate> InsertClientOrderTemplate(string tenantId, int createdBy, OrderTemplate orderTemplate)
        {

            orderTemplate.PublicId = Guid.NewGuid().ToString("N").ToLower();

            var strBuilder = new StringBuilder();

            return await _repository.Query(async (db) =>
            {

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

                var insertTemplateParams = new DynamicParameters();

                var orderTemplateId = await db.QueryFirstAsync<int>(_repository.GetInsertQuery<OrderTemplate, int>(tenantId, createdBy, orderTemplate, ref insertTemplateParams, "id"), insertTemplateParams);

                var dynamicParameters = new DynamicParameters();

                foreach (var brand in orderTemplate.Brands)
                {
                    strBuilder.Append(_repository.GetInsertQuery<OrderTemplateBrand, int>
                        (tenantId, createdBy,
                        new OrderTemplateBrand() { TenantId = tenantId, ClientBrandId = brand.Id, OrderTemplateId = orderTemplateId },
                        ref dynamicParameters));
                }

                foreach (var branch in orderTemplate.Branches)
                {
                    strBuilder.Append(_repository.GetInsertQuery<OrderTemplateBranch, int>
                        (tenantId, createdBy,
                        new OrderTemplateBranch() { TenantId = tenantId, ClientBranchId = branch.Id, OrderTemplateId = orderTemplateId },
                        ref dynamicParameters));
                }

                if (orderTemplate?.Products != null)
                {
                    foreach (var product in orderTemplate.Products)
                    {
                        product.TenantId = tenantId;
                        product.OrderTemplateId = orderTemplateId;

                        strBuilder.Append(_repository.GetInsertQuery<OrderTemplateProduct, int>
                            (tenantId, createdBy,
                            product,
                            ref dynamicParameters));
                    }
                }

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

                orderTemplate.Id = orderTemplateId;
                return orderTemplate;
            });
        }

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

        public async Task SendByEmail(string tenantId, int id, string[] recipients, string emailBody, bool includeAds)
        {
            var orderTemplate = await GetOrderTemplateById(tenantId, id);

            //var emailBody = await _settingsService.GetEmailTemplateBody(tenantId, EmailTemplateKey.OrderTemplatesPriceList);

            var dateTimeNow = DateTime.Now;

            var excel = await GetTemplateExcel(orderTemplate);

            var attachments = new MailAttachment[1] {
                new MailAttachment() { Name = $"PlantillaProductos_{orderTemplate.Name}{dateTimeNow.Year}-{dateTimeNow.Month}-{dateTimeNow.Day}.xlsx", Data = excel.ToArray() },
            };

            _emailService.SendEmail(includeAds, "noreply@misingredientes.mx", recipients, orderTemplate.Name + " - Lista de Precios", emailBody, true, attachments);
        }

        public async Task UpdateOrderTemplateMetadata(string tenantId, int updatedBy, OrderTemplate orderTemplate)
        {
            await _repository.Query(async (db) =>
            {
                var orderTemplateId = orderTemplate.Id;

                var existingOrderTemplate = await db.QueryFirstAsync<OrderTemplate>(
                    "SELECT * FROM ordertemplates WHERE id = @orderTemplateId AND tenantid = @tenantId",
                    new { tenantId, orderTemplateId });

                if (existingOrderTemplate == null)
                    throw new UserFriendlyException("No se encontró la plantilla seleccionada");

                var existingBrands = (await db.QueryAsync<OrderTemplateBrand>(
                    "SELECT * FROM ordertemplatebrands WHERE ordertemplateid = @orderTemplateId AND tenantid = @tenantId",
                    new { tenantId, orderTemplateId })).ToList();

                var strBuilder = new StringBuilder();
                var dynamicParameters = new DynamicParameters();

                dynamicParameters.Add("tenantId", tenantId);
                dynamicParameters.Add("orderTemplateId", orderTemplateId);
                dynamicParameters.Add("name", orderTemplate.Name);
                dynamicParameters.Add("clientId", orderTemplate.ClientId);

                foreach (var brand in orderTemplate.Brands)
                {
                    brand.TenantId = tenantId;

                    var existingBrand = existingBrands.FirstOrDefault(x => x.ClientBrandId == brand.Id);

                    if (existingBrand != null)
                    {
                        var existingBrandId = existingBrand.Id;
                        existingBrands.Remove(existingBrand);
                    }
                    else
                    {
                        strBuilder.Append(_repository.GetInsertQuery<OrderTemplateBrand, int>
                            (tenantId, updatedBy, new OrderTemplateBrand() { 
                                TenantId = tenantId,
                                OrderTemplateId = orderTemplateId,  
                                ClientBrandId = brand.Id
                            }, ref dynamicParameters));
                    }
                }

                foreach (var brand in existingBrands)
                {
                    strBuilder.Append(_repository.GetDeleteQuery<OrderTemplateBrand, int>(tenantId, brand, ref dynamicParameters));
                }

                await db.QueryAsync("UPDATE ordertemplates SET name = @name, clientid = @clientId WHERE id = @orderTemplateId AND tenantId = @tenantId; " +
                    strBuilder.ToString(), 
                    dynamicParameters);

                return "";

            });
        }

        public async Task UpdateClientOrderTemplateMetadata(string tenantId, int updatedBy, OrderTemplate orderTemplate)
        {
            await _repository.Query(async (db) =>
            {
                var orderTemplateId = orderTemplate.Id;

                var existingOrderTemplate = await db.QueryFirstAsync<OrderTemplate>(
                    "SELECT * FROM ordertemplates WHERE id = @orderTemplateId AND tenantid = @tenantId",
                    new { tenantId, orderTemplateId });

                if (existingOrderTemplate == null)
                    throw new UserFriendlyException("No se encontró la plantilla seleccionada");

                var existingBrands = (await db.QueryAsync<OrderTemplateBrand>(
                    "SELECT * FROM ordertemplatebrands WHERE ordertemplateid = @orderTemplateId AND tenantid = @tenantId",
                    new { tenantId, orderTemplateId })).ToList();

                var existingBranches = (await db.QueryAsync<OrderTemplateBranch>(
                    "SELECT * FROM ordertemplatebranches WHERE ordertemplateid = @orderTemplateId AND tenantid = @tenantId",
                    new { tenantId, orderTemplateId })).ToList();

                var strBuilder = new StringBuilder();
                var dynamicParameters = new DynamicParameters();

                dynamicParameters.Add("tenantId", tenantId);
                dynamicParameters.Add("orderTemplateId", orderTemplateId);
                dynamicParameters.Add("name", orderTemplate.Name);
                dynamicParameters.Add("clientId", orderTemplate.ClientId);

                foreach (var brand in orderTemplate.Brands)
                {
                    brand.TenantId = tenantId;

                    var existingBrand = existingBrands.FirstOrDefault(x => x.ClientBrandId == brand.Id);

                    if (existingBrand != null)
                    {
                        var existingBrandId = existingBrand.Id;
                        existingBrands.Remove(existingBrand);
                    }
                    else
                    {
                        strBuilder.Append(_repository.GetInsertQuery<OrderTemplateBrand, int>
                            (tenantId, updatedBy, new OrderTemplateBrand()
                            {
                                TenantId = tenantId,
                                OrderTemplateId = orderTemplateId,
                                ClientBrandId = brand.Id
                            }, ref dynamicParameters));
                    }
                }

                foreach (var branch in orderTemplate.Branches)
                {
                    branch.TenantId = tenantId;

                    var existingBranch = existingBranches.FirstOrDefault(x => x.ClientBranchId == branch.Id);

                    if (existingBranch != null)
                    {
                        var existingBranchId = existingBranch.Id;
                        existingBranches.Remove(existingBranch);
                    }
                    else
                    {
                        strBuilder.Append(_repository.GetInsertQuery<OrderTemplateBranch, int>
                            (tenantId, updatedBy, new OrderTemplateBranch()
                            {
                                TenantId = tenantId,
                                OrderTemplateId = orderTemplateId,
                                ClientBranchId = branch.Id
                            }, ref dynamicParameters));
                    }
                }

                foreach (var brand in existingBrands)
                {
                    strBuilder.Append(_repository.GetDeleteQuery<OrderTemplateBrand, int>(tenantId, brand, ref dynamicParameters));
                }

                foreach (var branch in existingBranches)
                {
                    strBuilder.Append(_repository.GetDeleteQuery<OrderTemplateBranch, int>(tenantId, branch, ref dynamicParameters));
                }

                await db.QueryAsync("UPDATE ordertemplates SET name = @name, clientid = @clientId WHERE id = @orderTemplateId AND tenantId = @tenantId; " +
                    strBuilder.ToString(),
                    dynamicParameters);

                return "";
            });
        }

        public GeneralOrderTemplateProduct MapProduct(OrderTemplateProduct product)
        {
            return new GeneralOrderTemplateProduct()
            {
                Created = product.Created,
                CreatedBy = product.CreatedBy,
                CreatedById = product.CreatedById,
                FixedPrice = product.FixedPrice,
                Id = product.Id,
                IsActive = product.IsActive,
                Qty = product.Qty,
                TenantId = product.TenantId,
                Modified = product.Modified,
                ModifiedBy = product.ModifiedBy,
                ModifiedById = product.ModifiedById,
                PreventDelete = product.PreventDelete,
                Product = product.Product,
                ProductId = product.ProductId,
                Specification = product.Specification,
                Subtotal = product.Subtotal,
                SuppliersDisplay = product.SuppliersDisplay
            };
        }

        public async Task UpdateGeneralOrderTemplate(string tenantId, int updatedBy, OrderTemplate orderTemplate)
        {
            var strBuilder = new StringBuilder();

            for (var i = 0; i < orderTemplate.Products.Count; i++)
            {
                orderTemplate.Products[i].Qty = 0;
                orderTemplate.Products[i].Specification = "";
            }

            var orderTemplateId = orderTemplate.Id;

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


                var existingOrderTemplateProducts =
                    (await db.QueryAsync<GeneralOrderTemplateProduct>(

                        "SELECT * FROM ordertemplateproducts WHERE OrderTemplateId = @orderTemplateId AND TenantId = @tenantId",
                        new { tenantId, orderTemplateId = orderTemplate.Id })).ToList();

                foreach (var product in orderTemplate.Products)
                {
                    var existingOrderTemplateProduct = existingOrderTemplateProducts.FirstOrDefault(x => x.ProductId == product.ProductId);
                    product.OrderTemplateId = orderTemplateId;
                    product.TenantId = tenantId;

                    if (existingOrderTemplateProduct != null)
                    {
                        var existingOrderTemplateProductId = existingOrderTemplateProduct.Id;
                        existingOrderTemplateProducts.Remove(existingOrderTemplateProduct);
                        strBuilder.Append(_repository.GetUpdateQuery<GeneralOrderTemplateProduct, int>(tenantId, updatedBy, x => x.Id == existingOrderTemplateProductId, MapProduct(product), ref dynamicParameters));
                    }
                    else
                    {
                        strBuilder.Append(_repository.GetInsertQuery<GeneralOrderTemplateProduct, int>(tenantId, updatedBy, MapProduct(product), ref dynamicParameters));
                    }
                }

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

                orderTemplate.TenantId = tenantId;
                strBuilder.Append(_repository.GetUpdateQuery<OrderTemplate, int>(tenantId, updatedBy, x => x.Id == orderTemplateId, orderTemplate, ref dynamicParameters));

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

        public async Task UpdateOrderTemplate(string tenantId, int updatedBy, OrderTemplate orderTemplate)
        {
            var strBuilder = new StringBuilder();

            for(var i=0; i < orderTemplate.Products.Count; i++)
            {
                orderTemplate.Products[i].Qty = 0;
                orderTemplate.Products[i].Specification = "";
            }
            
            var orderTemplateId = orderTemplate.Id;

            await _repository.Query(async (db) =>
            {
                var existingOrderTemplate = await db.QueryFirstAsync<OrderTemplate>(
                    "SELECT * FROM ordertemplates WHERE id = @orderTemplateId AND tenantid = @tenantId",
                    new { tenantId, orderTemplateId });

                if (existingOrderTemplate == null)
                    throw new UserFriendlyException("No se encontró la plantilla seleccionada");

                var dynamicParameters = new DynamicParameters();

                orderTemplate.Name = existingOrderTemplate.Name;

                var existingOrderTemplateProducts =
                    (await db.QueryAsync<OrderTemplateProduct>(

                        "SELECT * FROM ordertemplateproducts WHERE OrderTemplateId = @orderTemplateId AND TenantId = @tenantId",
                        new { tenantId, orderTemplateId = orderTemplate.Id })).ToList();

                foreach (var product in orderTemplate.Products)
                {
                    var existingOrderTemplateProduct = existingOrderTemplateProducts.FirstOrDefault(x => x.ProductId == product.ProductId);
                    product.OrderTemplateId = orderTemplateId;
                    product.TenantId = tenantId;

                    if (existingOrderTemplateProduct != null)
                    {
                        var existingOrderTemplateProductId = existingOrderTemplateProduct.Id;
                        existingOrderTemplateProducts.Remove(existingOrderTemplateProduct);
                        strBuilder.Append(_repository.GetUpdateQuery<OrderTemplateProduct, int>(tenantId, updatedBy, x => x.Id == existingOrderTemplateProductId, product, ref dynamicParameters));
                    }
                    else
                    {
                        strBuilder.Append(_repository.GetInsertQuery<OrderTemplateProduct, int>(tenantId, updatedBy, product, ref dynamicParameters));
                    }
                }
                
                foreach (var product in existingOrderTemplateProducts)
                {
                    strBuilder.Append(_repository.GetDeleteQuery<OrderTemplateProduct, int>(tenantId, product, ref dynamicParameters));
                }

                orderTemplate.TenantId = tenantId;
                strBuilder.Append(_repository.GetUpdateQuery<OrderTemplate, int>(tenantId, updatedBy, x => x.Id == orderTemplateId, orderTemplate, ref dynamicParameters));

                var d = new List<string>();
            

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

        private async Task<MemoryStream> GetTemplateExcel(OrderTemplate template)
        {
            var memoryStream = new MemoryStream();

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

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

                ws.Cells[1, 2].Value = "Artículo";
                ws.Cells[1, 3].Value = "Unidad";
                ws.Cells[1, 4].Value = "Precio con impuestos";

                var rowIndex = 2;

                var now = DateTime.UtcNow;

                foreach (var product in template.Products.OrderBy(x => x.Product?.Name))
                {
                    var catalogSalePrice = product.Product?.SalePrice + 
                        (product.Product?.SalePrice * ((product.Product?.Iva ?? 0) / 100)) + (product.Product?.SalePrice * ((product.Product?.Ieps ?? 0) / 100));

                    var fixedPrice = product.FixedPrice +
                        (product.FixedPrice * ((product.Product?.Iva ?? 0) / 100)) + (product.FixedPrice * ((product.Product?.Ieps ?? 0) / 100));

                    ws.SetValue(rowIndex, 1, product.Product?.Name);
                    ws.SetValue(rowIndex, 2, product.Product?.Unit?.Name);
                    ws.SetValue(rowIndex, 3, product.FixedPrice.HasValue ? fixedPrice : catalogSalePrice);
                    rowIndex++;
                }

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

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }
       
    }
}