using Dapper;
using HashidsNet;
using Microsoft.AspNetCore.JsonPatch;
using MisIngredientesVue.Core.Attributes;
using MisIngredientesVue.Core.Exceptions;
using MisIngredientesVue.Core.Extensions;
using MisIngredientesVue.Core.Models;
using MongoDB.Bson;
using Newtonsoft.Json;
using Npgsql;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;

namespace MisIngredientesVue.Core.Services
{
    public interface IProductsService
    {
        Task<Product> GetProductById(string tenantId, int id);
        Task<Product> GetClientProductById(string tenantId, int id, int[] branchIds);
        Task<FindResults<Product>> GetProducts(string tenantId, int skip, int take, string sortBy, string sortOrder, string q, int includeDeleted = 0, bool userHasFreeSubscription = false);
        Task<FindResults<Product>> GetClientProducts(string tenantId, int skip, int take, string sortBy, string sortOrder, string q, int[] branchIds, bool loadInventories = false);
        Task<Product> InsertProduct(string tenantId, int createdBy, Product product);
        Task UpdateProduct(string tenantId, int updatedBy, Product product);
        Task UpdateClientProduct(string tenantId, int updatedBy, Product product, int[] branchIds);
        Task DeleteProducts(string tenantId, int deletedById, int[] ids, bool silentMode);
        Task<Product> PatchProduct(string tenantId, int modifiedBy, int id, JsonPatchDocument<Product> changes);
        Task<MemoryStream> GetExcel(string tenantId, int offset, string sortBy, string sortOrder, string q);
        Task<MemoryStream> GetClientExcel(string tenantId, int offset, string sortBy, string sortOrder, string q);
        Task UpdateRate(string tenantId, int modifiedBy, int id, decimal supplierCost, decimal salePriceRate);
        Task<IEnumerable<DistinctResult>> GetPurchaseZones(string tenantId, string term);
        Task<IEnumerable<DistinctResult>> GetBuyers(string tenantId, string term);
        Task<IEnumerable<DistinctResult>> GetSuppliers(string tenantId, string term);
        Task<IEnumerable<DistinctResult>> GetCategories(string tenantId, string term);
        Task<IEnumerable<DistinctResult>> GetDistinct(string tenantId, string propName, string q);
        Task InsertBulk(string tenantId, int createdBy, BulkUploadProduct[] products, bool userHasFreeSubscription);
        Task<IEnumerable<Product>> SearchTemplateProducts(string tenantId, int templateId, string q);
        Task<IEnumerable<Product>> SearchClientTemplateProducts(string tenantId, int templateId, string q);
        Task<IEnumerable<Product>> SearchSupplierTemplateProducts(string tenantId, int supplierId, int? clientId, string q);
        Task<IEnumerable<Product>> SearchPublicTemplateProducts(string templateId, string q);
        Task<IEnumerable<Product>> SearchClientProducts(string tenantId, string q, bool getExpirationStatus = false);
        Task<IEnumerable<Product>> SearchProducts(string tenantId, string q, bool getExpirationStatus = false);
        Task<Product> UpsertProductByCode(string tenantId, int upsertedBy, Product product, bool silentMode);
        Task SendList(string tenantId, string[] recipients, string emailBody, bool includeAds);
        Task<int> RefreshProducts(string tenantId, int skip, int count);
        Task<IEnumerable<Product>> QuickSearchProducts(string tenantId, string q);
        Task UpdateProductBranchQty(string tenantId, int id, int branchId, decimal qty);
        Task UpdateProductBranchMinQty(string tenantId, int id, int branchId, decimal minQty);
        Task UpdateProductBranchMaxQty(string tenantId, int id, int branchId, decimal maxQty);
        Task UpdateProductBranchInventory(string tenantId, int id, int branchId, decimal qty, decimal minQty, decimal maxQty);
    }

    public class ProductsService : IProductsService
    {
        private readonly IRepository _repository;
        private readonly IWebHookService _webHookService;
        private readonly ISettingsService _settingsService;
        private readonly IEmailService _emailService;

        public ProductsService(IRepository repository, ISettingsService settingsService, 
            IWebHookService webHookService, IEmailService emailService
            )
        {
            _repository = repository;
            _settingsService = settingsService;
            _webHookService = webHookService;
            _emailService = emailService;
        }

        public async Task DeleteProducts(string tenantId, int deletedById, int[] ids, bool silentMode)
        {
            await _repository.DeleteManyByIds<Product, int>(tenantId, deletedById, ids);

            await _repository.Query(async (dbConnection) =>
            {
                await dbConnection.QueryAsync($@"

DELETE FROM orderproducts 
WHERE id in (
    SELECT orderproducts.id FROM orderproducts
	LEFT JOIN orders ON orders.id = orderproducts.orderid
	WHERE orderproducts.productid IN ({string.Join(", ", ids)}) AND orders.deliveryStatus = 0
    AND orders.tenantId = @tenantId AND orderproducts.tenantId = @tenantId
);

DELETE FROM ordertemplateproducts
WHERE productid IN ({string.Join(", ", ids)}) AND tenantId = @tenantId;

", new { tenantId });

                return "";
            });

            if (!silentMode)
            {
                foreach (var id in ids)
                {
                    await _webHookService.TriggerWebHooks(tenantId, "products.deleted", JsonConvert.SerializeObject(new Product() { Id = id }));
                }
            }
        }

        public async Task UpdateRate(string tenantId, int modifiedBy, int id, decimal supplierCost, decimal salePriceRate)
        {
            //Precio de venta = Costo proveedor / Tasa %;
            await _repository.Update<Product, int>(tenantId, modifiedBy, x => x.Id == id,
                new UpdateInfo<Product>() { Expression = (x => x.SupplierCost), Value = supplierCost },
                new UpdateInfo<Product>() { Expression = (x => x.SalePriceRate), Value = salePriceRate },
                new UpdateInfo<Product>() { Expression = (x => x.SalePrice), Value = supplierCost / (salePriceRate / 100) },
                new UpdateInfo<Product>() { Expression = (x => x.CostModified), Value = DateTime.UtcNow },
                new UpdateInfo<Product>() { Expression = (x => x.CostModifiedById), Value = modifiedBy }
                );
        }

        public async Task<MemoryStream> GetExcel(string tenantId, int offset, string sortBy, string sortOrder, string q)
        {
            var filterExpression = GetFilterExpression(q);
            return await _repository.GetExcel<Product, int>(tenantId, offset, filterExpression, sortBy, sortOrder);
        }

        public async Task<MemoryStream> GetClientExcel(string tenantId, int offset, string sortBy, string sortOrder, string q)
        {
            var products = await GetClientProducts(tenantId, 0, 3000, sortBy, sortOrder, q, new int[] { });

            //TODO: Usar mapping
            var clientProducts = products.Results.Select(x => new ClientProduct()
            {
                Category = x.Category,
                ClaveUnidadSat = x.ClaveUnidadSat,
                ClaveProductoSat = x.ClaveProductoSat,
                Code = x.Code,
                Name = x.Name,
                Ieps = x.Ieps,
                Created = x.Created,
                CreatedBy = x.CreatedBy,
                CreatedById = x.CreatedById,
                Id = x.Id,
                IsActive = x.IsActive,
                Iva = x.Iva,
                MinOrder = x.MinOrder,
                Modified = x.Modified,
                ModifiedBy = x.ModifiedBy,
                ModifiedById = x.ModifiedById,
                SalePrice = x.SalePrice,
                Supplier = x.Supplier,
                TenantId = x.TenantId,
                Unit = x.Unit,
                UnitId = x.UnitId,
                Qty = x.Qty,
               
            }).ToArray();

            var results = new FindResults<ClientProduct>()
            {
                Results = clientProducts,
                CustomData = products.CustomData,
                Filters = products.Filters,
                Total = products.Total
            };

            return await _repository.GetExcel<ClientProduct, int>(results, offset, (prop, item, val) => {
                return null;
            });
        }

        public async Task<FindResults<Product>> GetProducts(string tenantId, int skip, int take, string sortBy, 
                string sortOrder, string q, int includeDeleted = 0, bool userHasFreeSubscription = false)
        {
            var filterExpression = GetFilterExpression(q);
            var products = await _repository.Find<Product, int>(tenantId, filterExpression, skip, take, sortBy, sortOrder, null, null, null, includeDeleted == 0);

            var now = DateTime.UtcNow;
            var productCount = products.Results.Count();

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

            if (userHasFreeSubscription)
            {
                calculateproductpriceexpirationcontinue = false;
                calculateproductpriceexpirationdeny = false;
                calculateproductpriceexpirationcontinue_days = 0;
                calculateproductpriceexpirationdeny_days = 0;

                foreach(var product in products.Results)
                {
                    product.ContinueStockMode = true;
                }
            }

            foreach (var product in products.Results)
            {
                if(product.ContinueStockMode && calculateproductpriceexpirationcontinue)
                    product.PriceExpirationStatus = now - product.CostModified > TimeSpan.FromDays(calculateproductpriceexpirationcontinue_days) ? "No vigente" : "Vigente";

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

            return products;
        }
        
        public async Task<FindResults<Product>> GetClientProducts(string tenantId, int skip, int take, string sortBy, string sortOrder, string q, int[] branchIds, bool loadInventories = false)
        {
            return await _repository.Query(async (db) =>
            {
                if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
                    sortOrder = "DESC";

                if (string.IsNullOrEmpty(sortBy))
                    sortBy = "products.name";

                sortOrder = sortOrder.ToUpper();

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

                if (!(new[] { "code", "name", "category", "supplierCost", "unit.name" }.Contains(sortBy)))
                    sortBy = "products.name";

                var whereQuery = "";

                var filterExpression = GetFilterExpression(q);

                //                var query = $@"SELECT products.*, suppliers.* FROM products
                //LEFT JOIN client_suppliers ON client_suppliers.supplier_tenantid = products.tenantid
                //LEFT JOIN suppliers ON suppliers.tenantid = products.tenantid
                //WHERE (products.tenantId = @tenantId OR client_tenantid = @tenantId) {whereQuery}
                //ORDER BY {sortBy} {sortOrder}
                //LIMIT {take}
                //OFFSET {skip}";

                var query = $@"SELECT DISTINCT products.id, products.code, products.name, products.category, products.suppliercost, products.salepricerate, 
products.buyer, products.supplier, products.purchasezone, products.unitid, products.saleprice, 
(SELECT SUM(qty) FROM branchinventories WHERE productId = products.id) as qty, 
products.minorder, products.claveproductosat, products.claveunidadsat, products.ieps, products.iva, products.tenantid, products.createdbyid, 
products.created, products.modifiedbyid, products.modified, products.isactive, products.costmodifiedbyid, products.costmodified, products.continuestockmode, 
products.clientsupplierid 
, (CASE WHEN public.products.tenantid <> @tenantId
THEN(SELECT name from suppliers WHERE suppliers.tenantid = public.products.tenantid)
ELSE '' END) as externalsupplier, units.*, suppliers.*, creators.*, modifiers.* FROM public.products
LEFT JOIN units ON products.unitId = units.id
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
LEFT JOIN ordertemplateproducts ON ordertemplateproducts.productid = public.products.id
LEFT JOIN ordertemplates ON ordertemplateproducts.ordertemplateid = ordertemplates.id
LEFT JOIN users as creators ON creators.id = products.createdbyid
LEFT JOIN users as modifiers ON modifiers.id = products.modifiedbyid
WHERE ((ordertemplates.tenantId <> @tenantId AND ordertemplates.clientid = (SELECT id from clients WHERE tenantid = @tenantId))
OR public.products.tenantId = @tenantId) AND products.isactive = true";

                var countQuery = $@"SELECT COUNT(DISTINCT public.products.id) FROM public.products
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
LEFT JOIN ordertemplateproducts ON ordertemplateproducts.productid = public.products.id
LEFT JOIN ordertemplates ON ordertemplateproducts.ordertemplateid = ordertemplates.id
WHERE ((ordertemplates.tenantId <> @tenantId AND ordertemplates.clientid = (SELECT id from clients WHERE tenantid = @tenantId))
OR public.products.tenantId = @tenantId) AND products.isactive = true";

                var products = (await db.QueryAsync<Product, Unit, Supplier, UserData, UserData, Product>(query,
                (product, unit, supplier, createdBy, modifiedBy) =>
                {
                    product.ClientSupplier = supplier;
                    product.Unit = unit;

                    if (!string.IsNullOrEmpty(product.ExternalSupplier))
                        product.ClientSupplier = new Supplier { Name = product.ExternalSupplier };

                    product.CreatedBy = createdBy;
                    product.ModifiedBy = modifiedBy;

                    return product;
                }, new { tenantId })).ToArray();

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

                var branchInventories = branchIds != null && branchIds.Length > 0 ? await db.QueryAsync<BranchInventory>(
                            $@"SELECT * FROM branchinventories WHERE 
tenantId = @tenantId AND branchid IN ({string.Join(',', branchIds.Select(x => x.ToString()))}) AND
productid IN ({string.Join(',', products.Select(x => x.Id.ToString()))})",
                            new { tenantId }) : new List<BranchInventory>();

                //Esto es para no desvelar información sobre nombres de usuarios
                foreach (var product in products)
                {
                    product.Qty = branchInventories.Where(x => x.ProductId == product.Id).Sum(x => x.Qty);

                    if (product.CreatedBy != null && product.CreatedBy.TenantId != tenantId)
                    {
                        
                        product.CreatedBy.FirstName = "Usuario";
                        product.CreatedBy.LastName = "Externo";
                        product.CreatedBy.TenantId = "";
                        product.CreatedBy.Email = "";
                    }

                    if (product.ModifiedBy != null && product.ModifiedBy.TenantId != tenantId)
                    {
                        product.ModifiedBy.FirstName = "Usuario";
                        product.ModifiedBy.LastName = "Externo";
                        product.ModifiedBy.TenantId = "";
                        product.ModifiedBy.Email = "";
                    }
                }

               
                return new FindResults<Product>()
                {
                    Results = products,
                    Total = count,
                    CustomData = loadInventories && branchIds.Length > 0
                        ? branchInventories
                        : null
                };
            });
        }


        public async Task<IEnumerable<Product>> QuickSearchProducts(string tenantId, string q)
        {
            q = q?.PrepareQuery();
            var resultsQuery = $"SELECT * FROM {_repository.GetTableName<Product>()} {(!string.IsNullOrEmpty(q) ? $"WHERE Name ~* @q AND TenantId = @tenantId" : "TenantId = @tenantId")} ORDER BY char_length(Name) LIMIT 20";

            return await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product>(resultsQuery, new { q, tenantId });
            });
        }

        public async Task<IEnumerable<DistinctResult>> GetPurchaseZones(string tenantId, string term)
        {
            return await _repository.GetDistinct<Product, int>(tenantId, x => x.PurchaseZone, x => x.PurchaseZone, term);
        }

        private Expression<Func<Product,bool>> GetFilterExpression(string q)
        {
            return x => x.Name.Contains(q) || x.Code.Contains(q) || x.Buyer.Contains(q) || x.Supplier.Contains(q) || x.PurchaseZone.Contains(q) || x.Category.Contains(q);
        }

        public async Task<Product> GetProductById(string tenantId, int id)
        {
            return await _repository.FindOneById<Product, int>(tenantId, id);
        }

        public async Task<Product> GetClientProductById(string tenantId, int id, int[] branchIds)
        {
            return await _repository.Query<Product>(async (db) =>
            {
                var query = $@"SELECT public.products.*, (CASE WHEN public.products.tenantid <> @tenantId
THEN(SELECT name from suppliers WHERE suppliers.tenantid = public.products.tenantid)
ELSE '' END) as externalsupplier, public.suppliers.* FROM public.products
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
LEFT JOIN ordertemplateproducts ON ordertemplateproducts.productid = public.products.id
LEFT JOIN ordertemplates ON ordertemplateproducts.ordertemplateid = ordertemplates.id
WHERE public.products.id = @id AND 
((ordertemplates.tenantId <> @tenantId AND ordertemplates.clientid = (SELECT id from clients WHERE tenantid = @tenantId))
OR public.products.tenantId = @tenantId)";

                Product product = null;

                await db.QueryAsync<Product, Supplier, Product>(query, (p, s) =>
                {
                    if (product == null)
                    {
                        product = p;
                        product.ClientSupplier = s;

                        if (!string.IsNullOrEmpty(product.ExternalSupplier))
                            product.ClientSupplier = new Supplier
                            {
                                Name = product.ExternalSupplier
                            };
                    }

                    return product;
                }, new { id, tenantId });

                var branchInventoryQuery = branchIds.Length == 1
                    ? @"SELECT * FROM branchinventories WHERE branchid = @branchId AND productid = @id AND tenantId = @tenantId"
                    : @"SELECT SUM(qty) as qty, SUM(minqty) as minqty, SUM(maxqty) as maxqty FROM branchinventories WHERE productid = @id AND tenantId = @tenantId";

                var inventory = (await db.QueryAsync<BranchInventory>(branchInventoryQuery, 
                    new { id, branchId = branchIds.Length == 1 ? branchIds[0] : 0, tenantId })).ToList();

                if (inventory.Count() > 0)
                {
                    product.MaxQty = inventory[0]?.MaxQty ?? 0;
                    product.MinQty = inventory[0]?.MinQty ?? 0;
                    product.Qty = inventory[0]?.Qty ?? 0;
                   
                }

                return product;

            });
        }

        private string GetNewProductId(string productName)
        {
            var hashids = new Hashids(productName);
            var hash = hashids.EncodeLong(DateTime.UtcNow.ToFileTimeUtc());
            var code = hash.ToUpper();

            if (code.Length > 10)
                return code.Substring(0, 10);

            if (code.Length < 10)
                code += DateTime.UtcNow.ToFileTime();

            if (code.Length > 10)
                return code.Substring(0, 10);

            return code;
        }

        public async Task<Product> InsertProduct(string tenantId, int createdBy, Product product)
        {
            if (string.IsNullOrWhiteSpace(product.Code))
                product.Code = GetNewProductId(product.Name);

            if(product.SalePriceRate != null)
                product.SalePriceRate = RoundDecimal(product.SalePriceRate.Value);

            var code = product.Code;
            var existingProduct = await _repository.QueryFirstOrDefault<Product>(
                "SELECT * FROM products WHERE (products.Code = @code) AND products.TenantId = @tenantId", 
                new { code, tenantId });

            if (existingProduct != null)
            {
                await _repository.Reactivate<Product, int>(tenantId, createdBy, existingProduct.Id);

                existingProduct.IsActive = true;
                existingProduct.Name = product.Name;
                existingProduct.Qty = product.Qty;
                //TODO: Terminar de mapear las propiedades

                await _repository.Update<Product, int>(tenantId, createdBy, existingProduct);

                await _webHookService.TriggerWebHooks(tenantId, "products.added", JsonConvert.SerializeObject(existingProduct));

                return existingProduct;
            }

            var newProduct = await _repository.Insert<Product, int>(tenantId, createdBy, product);

            newProduct.Unit = await _repository.FindOneById<Unit, int>(tenantId, product.UnitId);

            await _webHookService.TriggerWebHooks(tenantId, "products.added", JsonConvert.SerializeObject(newProduct));

            return newProduct;
        }

        public async Task<Product> PatchProduct(string tenantId, int modifiedBy, int id, JsonPatchDocument<Product> changes)
        {
            changes.Operations.RemoveAll(x => x.path.ToLower().Contains("costmodifiedbyid"));
            changes.Operations.RemoveAll(x => x.path.ToLower().Contains("costmodified"));

            if (changes.Operations.Any(x => x.path.ToLower().Contains("suppliercost")))
            {
                changes.Operations.Add(new Microsoft.AspNetCore.JsonPatch.Operations.Operation<Product>()
                {
                    path = "/costmodifiedbyid",
                    op = "replace",
                    value = modifiedBy
                });

                changes.Operations.Add(new Microsoft.AspNetCore.JsonPatch.Operations.Operation<Product>()
                {
                    path = "/costmodified",
                    op = "replace",
                    value = DateTime.UtcNow
                });
            }

            var updatedProduct = await _repository.Patch(tenantId, modifiedBy, id, changes);

            await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(updatedProduct));

            return updatedProduct;
        }

        public async Task<Product> UpsertProductByCode(string tenantId, int upsertedBy, Product product, bool silentMode)
        {
            var dynamicParams = new DynamicParameters();
            var query = GetUpsertQuery(tenantId, upsertedBy, product, x => x.Code, ref dynamicParams);

            //Para asegurar su reactivación
            query = query.Replace("UPDATE SET ", "UPDATE SET isactive = true, ");

            var productId = 0;

            Console.WriteLine(_repository.PrintQuery(query, dynamicParams));

            Product updatedProduct = null;

            await _repository.Query<string>(async (db) =>
            {
                productId = await db.QueryFirstAsync<int>(query, dynamicParams);

                if (productId == 0) throw new Exception("El id del producto inserceditado es 0 (??)");

                updatedProduct = await db.QueryFirstOrDefaultAsync<Product>($"SELECT * FROM products WHERE id = {productId}");
                return "";
            });

            if (updatedProduct == null)
            {
                throw new Exception("No se encontró el producto recientemente inserceditado (?)");
            }

            if(!silentMode)
                await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(updatedProduct));

            return updatedProduct;
        }

        public async Task UpdateProduct(string tenantId, int updatedBy, Product product)
        {
            var existingProduct = await _repository.FindOneById<Product, int>(tenantId, product.Id);
            product.CostModified = existingProduct.CostModified;
            product.CostModifiedById = existingProduct.CostModifiedById;

            if (product.SalePriceRate != null)
                product.SalePriceRate = RoundDecimal(product.SalePriceRate.Value);

            if (product.SupplierCost != existingProduct.SupplierCost) {
                product.CostModified = DateTime.UtcNow;
                product.CostModifiedById = updatedBy;
            }

            await _repository.Update<Product, int>(tenantId, updatedBy, product);

            var updatedProduct = await _repository.FindOneById<Product, int>(tenantId, product.Id);

            await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(updatedProduct));
        }
        
        public async Task UpdateClientProduct(string tenantId, int updatedBy, Product product, int[] branchIds)
        {
            var existingProduct = await _repository.QueryFirstOrDefault<Product>(
                "SELECT * FROM products WHERE id = @productId AND tenantId = @tenantId", new { productId = product.Id, tenantId });

            //Si es un producto verificado sólo se pueden editar el inventario sucursal y el inventario mínimo
            //dependiendo de la sucursal actual
            if (!existingProduct.IsVerified)
                await _repository.Update<Product, int>(tenantId, updatedBy, product);
            

            if (branchIds.Length == 1)
            {
                var query = @"INSERT INTO branchinventories(qty, minQty, maxQty, productId, branchId, tenantId) VALUES (@qty, @minQty, @maxQty, @id, @branchId, @tenantId)
ON CONFLICT (tenantId, branchid, productid) DO
UPDATE SET qty = @qty, minQty = @minQty, maxQty = @maxQty;";

                await _repository.Query<string>(async (dbConnection) =>
                {
                    await dbConnection.QueryAsync(query, new { tenantId, id = product.Id, branchId = branchIds[0], qty = product.Qty, minQty = product.MinQty, maxQty = product.MaxQty });
                    return "";
                });
            }
        }

        public async Task UpdateProductBranchQty(string tenantId, int id, int branchId, decimal qty)
        {
            var query = @"INSERT INTO branchinventories(qty, minQty, maxQty, productId, branchId, tenantId) VALUES (@qty, @minQty, @maxQty, @id, @branchId, @tenantId)
ON CONFLICT (tenantId, branchid, productid) DO
UPDATE SET qty = @qty;";

            await _repository.Query<string>(async (dbConnection) =>
            {
                await dbConnection.QueryAsync(query, new { id, tenantId, branchId, qty, maxQty = 0, minQty = 0 });
                return "";
            });
        }

        public async Task UpdateProductBranchMinQty(string tenantId, int id, int branchId, decimal minQty)
        {
            var query = @"INSERT INTO branchinventories(qty, minQty, maxQty, productId, branchId, tenantId) VALUES (@qty, @minQty, @maxQty, @id, @branchId, @tenantId)
ON CONFLICT (tenantId, branchid, productid) DO
UPDATE SET minqty = @minQty;";

            await _repository.Query<string>(async (dbConnection) =>
            {
                await dbConnection.QueryAsync(query, new { id, tenantId, branchId, qty = 0, minQty, maxQty = 0 });
                return "";
            });
        }

        public async Task UpdateProductBranchMaxQty(string tenantId, int id, int branchId, decimal maxQty)
        {
            var query = @"INSERT INTO branchinventories(qty, minQty, maxQty, productId, branchId, tenantId) VALUES (@qty, @minQty, @maxQty, @id, @branchId, @tenantId)
ON CONFLICT (tenantId, branchid, productid) DO
UPDATE SET maxqty = @maxQty;";

            await _repository.Query<string>(async (dbConnection) =>
            {
                await dbConnection.QueryAsync(query, new { id, tenantId, branchId, qty = 0, minQty = 0, maxQty });
                return "";
            });
        }

        public async Task UpdateProductBranchInventory(string tenantId, int id, int branchId, decimal qty, decimal minQty, decimal maxQty)
        {
            var query = @"INSERT INTO branchinventories(qty, minQty, maxQty, productId, branchId, tenantId) VALUES (@qty, @minQty, @maxQty, @id, @branchId, @tenantId)
ON CONFLICT (tenantId, branchid, productid) DO
UPDATE SET maxqty = @maxQty, minqty = @minQty, qty = @qty;";

            await _repository.Query<string>(async (dbConnection) =>
            {
                await dbConnection.QueryAsync(query, new { id, tenantId, branchId, qty, minQty, maxQty });
                return "";
            });
        }

        public async Task<IEnumerable<DistinctResult>> GetBuyers(string tenantId, string term)
        {
            return await _repository.GetDistinct<Product, int>(tenantId, x => x.Buyer, x => x.Buyer, term);
        }

        public async Task<IEnumerable<DistinctResult>> GetSuppliers(string tenantId, string term)
        {
            return await _repository.GetDistinct<Product, int>(tenantId, x => x.Supplier, x => x.Supplier, term);
        }

        public async Task<IEnumerable<DistinctResult>> GetCategories(string tenantId, string term)
        {
            return await _repository.GetDistinct<Product, int>(tenantId, x => x.Category, x => x.Category, term);
        }

        public async Task<IEnumerable<DistinctResult>> GetDistinct(string tenantId, string propName, string q)
        {
            return await _repository.GetDistinct<Product, int>(tenantId, propName, q);
        }

        private decimal GetSalePrice(decimal supplierCost, decimal? salePriceRate, 
            bool useMarginInsteadOfProfit, bool roundProductCosts)
        {
            if (!salePriceRate.HasValue) return 0;

            decimal salePriceDec = 0;

            if (useMarginInsteadOfProfit && salePriceRate.Value == 100)
                salePriceDec = 0;
            else
                salePriceDec = RoundDecimal(useMarginInsteadOfProfit
                        ? supplierCost / (1 - (salePriceRate.Value / 100m))
                        : supplierCost * (1 + (salePriceRate.Value / 100m)));

            if (roundProductCosts)
            {
                var salePrice = salePriceDec.ToString();

                if (salePrice.Contains("."))
                {
                    var g = salePrice.Split('.')[1];

                    if (!ContainsAllZeros(g))
                    {
                        var m = "0." + g;
                        var toAdd = 0.50M;

                        if (Convert.ToDecimal(m, CultureInfo.InvariantCulture) > toAdd)
                            toAdd = 1;

                        return RoundDecimal(Convert.ToDecimal(salePrice.Split('.')[0], CultureInfo.InvariantCulture) + toAdd);
                    }
                }
            }

            return salePriceDec;
        }

        public async Task InsertBulk(string tenantId, int createdBy, BulkUploadProduct[] products, bool userHasFreeSubscription)
        {
            
            var roundProductCosts = await _settingsService.GetSettingValue<bool>(tenantId,
                ClientSettingType.CatalogsRoundProductCosts);
            var useMarginInsteadOfProfit = await _settingsService.GetSettingValue<bool>(tenantId, 
                ClientSettingType.CatalogsUseMarginInsteadOfProfit);

            var productsToInsertOrUpdate = new List<Product>();
            foreach (var product in products)
            {
                if (string.IsNullOrWhiteSpace(product.Code))
                    product.Code = GetNewProductId(product.Name);

                if (!userHasFreeSubscription)
                {
                    //Se especificó el margen, por tanto hay que calcular el precio de venta
                    if (product.SalePriceRate.HasValue && product.SalePriceRate.Value > 0)
                    {
                        //FORMULAS
                        //Cuando es margen (margin) la fórmula es Costo / (1 - (margen/100)) = precio
                        //         ej. 100 / (1 - (30/100)) = 100 / 0.7 = 142.85 
                        //Cuando es utilidad (profit) la fórmula es Costo * (1 + (utilidad/100)) = precio
                        //         ej. 100 * (1 + (30/100)) = 100 * 1.3 = 130

                        var salePrice = GetSalePrice(product.SupplierCost, product.SalePriceRate,
                            useMarginInsteadOfProfit, roundProductCosts);

                        if (salePrice != -1)
                            product.SalePrice = salePrice;

                    }
                    //Se especificó el precio, por tanto hay que calcular el margen
                    else
                    {
                        //FORMULAS
                        //Cuando es margen (margin) la fórmula es (1 - (Costo / Precio)) * 100 = margen
                        //Cuando es utilidad (profit) la fórmula es ((Precio / Costo) - 1) * 100 = utilidad

                        if (product.SalePrice > 0)
                        {
                            product.SalePriceRate = RoundDecimal(useMarginInsteadOfProfit
                            ? ((1 - (product.SupplierCost / product.SalePrice)) * 100m)
                            : (((product.SalePrice / product.SupplierCost) - 1) * 100m));
                        }
                    }
                }

                productsToInsertOrUpdate.Add(new Product()
                {
                    Buyer = product.Buyer,
                    Category = product.Category,
                    ClaveProductoSat = product.ClaveProductoSat?.Trim(),
                    ClaveUnidadSat = product.ClaveUnidadSat?.Trim(),
                    Code = product.Code,
                    Ieps = product.Ieps,
                    Iva = product.Iva,
                    MinOrder = product.MinOrder,
                    MinQty = product.MinQty,
                    UnitId = product.UnitId,
                    Supplier = product.Supplier,
                    Name = product.Name,
                    PurchaseZone = product.PurchaseZone,
                    Qty = product.Qty,
                    SalePrice = product.SalePrice,
                    SalePriceRate = product.SalePriceRate,
                    SupplierCost = product.SupplierCost,
                    ContinueStockMode = product.ContinueStockMode
                });
            }

            await BulkUpload(tenantId, createdBy, x => x.Code, productsToInsertOrUpdate);
        }

        public 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);
            }
        }

        private bool ContainsAllZeros(string str)
        {
            try
            {
                var zero = Convert.ToInt32(str);

                return zero == 0;
            }
            catch
            {

            }

            if (string.IsNullOrEmpty(str)) return true;

            foreach (var d in str)
            {
                if (d != '0')
                {
                    return false;
                }
            }

            return true;
        }

        private async Task BulkUpload(string tenantId, int createdOrModifiedBy, Expression<Func<Product, object>> conflictProperty, IEnumerable<Product> items) 
        {

            if (string.IsNullOrEmpty(tenantId))
                throw new Exception("Invalid tenantId");

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

            await _repository.Query(async (dbConnection) =>
            {
                foreach (var item in items)
                {
                    strBuilder.AppendLine(GetUpsertQuery(tenantId, createdOrModifiedBy, item, conflictProperty, ref dynamicParams));
                }

                //Console.WriteLine(strBuilder.ToString());

                var ids = new List<int>();

                using (var multi = await dbConnection.QueryMultipleAsync(strBuilder.ToString(), dynamicParams))
                {
                    foreach (var item in items)
                    {
                        ids.Add(multi.Read<int>().First());
                    }
                }

                //Console.WriteLine($"Ids de los productos que cambiaron: {string.Join(", ", ids.Select(x => x.ToString()))}");

                var zippedProducts = items.Zip(ids, (upsertedProduct, intId) =>
                {
                    upsertedProduct.Id = intId;
                    return upsertedProduct;
                });

                foreach(var zippedProduct in zippedProducts)
                {
                    await _webHookService.TriggerWebHooks(tenantId, "products.updated", JsonConvert.SerializeObject(zippedProduct));
                }

                return "";
            });
        }

        private string GetUpsertQuery(string tenantId, int createdOrModifiedBy, Product item, Expression<Func<Product, object>> conflictProperty, ref DynamicParameters dynamicParams)
        {
            var insertQuery = _repository.GetInsertQuery<Product, int>(tenantId, createdOrModifiedBy, item, ref dynamicParams);

            if (insertQuery.EndsWith(";"))
                insertQuery = insertQuery.Substring(0, insertQuery.Length - 1);

            return $@"
{insertQuery}
ON CONFLICT (tenantId,{GetMemberName(conflictProperty)}) DO
{GetUpdateQuery(tenantId, createdOrModifiedBy, null, item, ref dynamicParams)} RETURNING id;
";
        }

        private string GetUpdateQuery(string tenantId, int modifiedBy, Expression<Func<Product, bool>> findExpression, Product item, ref DynamicParameters dynamicParams)
        {
            var whereQuery = "";

            if (findExpression != null)
            {
                var whereDynamicParams = _repository.GetWhereQueryFromExpression<Product, int>(tenantId, findExpression, out whereQuery);
                foreach (var p in whereDynamicParams.ParameterNames)
                    dynamicParams.Add(p, whereDynamicParams.Get<dynamic>(p));
            }

            var updatesList = new List<string>();

            var props = GetUpdateableProps(typeof(Product));
            foreach (var prop in props)
            {
                if (prop.Name == "CostModified" || prop.Name == "CostModifiedById") continue;

                var varName = $"@v" + Guid.NewGuid().ToString("N");

                updatesList.Add($"{prop.Name} = {varName}");

                dynamicParams.Add(varName, prop.GetValue(item));
            }

            updatesList.Add($"{nameof(Product.ModifiedById)} = @modifiedBy");
            updatesList.Add($"{nameof(Product.Modified)} = @modified");

            //Para que se modifique el CostModified y CostModifiedBy sólo cuando sea diferente del actual
            //updatesList.Add($"{nameof(Product.CostModifiedById)} = (CASE WHEN {_repository.GetTableName<Product>()}.{nameof(Product.SupplierCost)} = {item.SupplierCost} THEN {_repository.GetTableName<Product>()}.{nameof(Product.CostModifiedById)} ELSE @modifiedBy END)");
            //updatesList.Add($"{nameof(Product.CostModified)} = (CASE WHEN {_repository.GetTableName<Product>()}.{nameof(Product.SupplierCost)} = {item.SupplierCost} THEN {_repository.GetTableName<Product>()}.{nameof(Product.CostModified)} ELSE @modified END)");

            updatesList.Add($"{nameof(Product.CostModifiedById)} = @modifiedBy");
            updatesList.Add($"{nameof(Product.CostModified)} = @modified");

            dynamicParams.Add("@modifiedBy", modifiedBy);
            dynamicParams.Add("@modified", DateTime.UtcNow);
            
            if (string.IsNullOrEmpty(whereQuery))
                return $"UPDATE SET {string.Join(", ", updatesList.ToArray())} ";

            //Console.WriteLine($"UPDATE {_repository.GetTableName<Product>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery} ");

            return $"UPDATE {_repository.GetTableName<Product>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery} ";
        }

        private PropertyInfo[] GetUpdateableProps(Type type)
        {
            var props = type.GetProperties();

            return type.GetProperties()
                    .Where(x => x.Name != "Id"
                    && x.GetSetMethod() != null
                    && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                    && !Attribute.GetCustomAttributes(x).Any(y => y is DoNotUpdateAttribute)
                    && (!x.PropertyType.IsClass
                    || x.PropertyType == typeof(string)
                    || x.PropertyType == typeof(DateTime)
                    || x.PropertyType == typeof(DateTime?))
                    ).ToArray();
        }

        private string GetMemberName<T>(Expression<T> expression)
        {
            switch (expression.Body)
            {
                case MemberExpression m:
                    return m.Member.Name;
                case UnaryExpression u when u.Operand is MemberExpression m:
                    return m.Member.Name;
                default:
                    throw new NotImplementedException(expression.GetType().ToString());
            }
        }

        public async Task<IEnumerable<Product>> SearchPublicTemplateProducts(string templateId, string q)
        {
            q = q?.PrepareQuery();

            var resultsQuery = $@"
SELECT * from products
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid = (SELECT id FROM ordertemplates WHERE publicid = @templateId) AND
name ~* @q AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20";

            return await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product, Unit, Product>(
                resultsQuery,
                (product, unit) =>
                {
                    product.Unit = unit;
                    return product;
                }, new { templateId, q });

            });
        }

        public async Task<IEnumerable<Product>> SearchSupplierTemplateProducts(string tenantId, int supplierId, int? clientId, string q)
        {
            q = q?.PrepareQuery();

            if (supplierId > 0)
            {

                var resultsQuery = clientId == null ? $@"
SELECT * from products
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid IN (
SELECT id FROM ordertemplates WHERE clientid = (SELECT id from clients WHERE tenantid = @tenantId) AND tenantId = (SELECT tenantId FROM suppliers WHERE id = @supplierId)
) AND
name ~* @q AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20" 
: $@"
SELECT * from products
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid IN (
SELECT id FROM ordertemplates WHERE clientid = @clientId AND tenantId = @tenantId
) AND
name ~* @q AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20";

                return await _repository.Query(async (dbConnection) =>
                {
                    return await dbConnection.QueryAsync<Product, Unit, Product>(
                    resultsQuery,
                    (product, unit) =>
                    {
                        product.Unit = unit;
                        return product;
                    }, new { supplierId, q, tenantId, clientId });

                });
            }

            var resultsQuery2 = clientId == null ? $@"
SELECT * from products
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid IN (
SELECT id FROM ordertemplates WHERE clientid = (SELECT id from clients WHERE tenantid = @tenantId)
) AND
name ~* @q AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20" :
$@"
SELECT * from products
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid IN (
SELECT id FROM ordertemplates WHERE clientid = {clientId}
) AND
name ~* @q AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20"
;

            return await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product, Unit, Product>(
                resultsQuery2,
                (product, unit) =>
                {
                    product.Unit = unit;
                    return product;
                }, new { q, tenantId });

            });

        }

        public async Task<IEnumerable<Product>> SearchTemplateProducts(string tenantId, int templateId, string q)
        {
            q = q?.PrepareQuery();

            var resultsQuery = $@"
SELECT * from products
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid = @templateId AND
name ~* @q AND products.tenantId = @tenantId AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20";

            return await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product, Unit, Product>(
                resultsQuery,
                (product, unit) =>
                {
                    product.Unit = unit;
                    return product;
                }, new { templateId, q, tenantId = tenantId });

            });
        }

        public async Task<IEnumerable<Product>> SearchClientTemplateProducts(string tenantId, int templateId, string q)
        {
            //TODO: Buscar los productos de todos los proveedores de esta plantilla

            q = q?.PrepareQuery();

            var resultsQuery = $@"
SELECT 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 products
LEFT JOIN units ON products.unitId = units.id
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
join ordertemplateproducts ON products.id = ordertemplateproducts.productid
where ordertemplateproducts.ordertemplateid = @templateId AND
products.name ~* @q AND products.tenantId = @tenantId AND products.isactive = True
ORDER BY products.name ASC
LIMIT 20";

            return await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product, Unit, Supplier, Product>(
                resultsQuery,
                (product, unit, supplier) =>
                {
                    product.Unit = unit;

                    product.ClientSupplier = supplier;

                    if (product.ClientSupplier != null)
                        product.Name += $" ({product.ClientSupplier.Name})";
                    else
                        product.Name += $" ({product.ExternalSupplier})";

                    return product;
                }, new { templateId, q, tenantId = tenantId });

            });
        }

        public async Task<IEnumerable<Product>> SearchClientProducts(string tenantId, string q, bool getExpirationStatus = false)
        {
            //Este también busca en los productos de las plantillas de proveedor
            q = q?.PrepareQuery();

            var resultsQuery = $@"SELECT 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 products 
JOIN units on products.UnitId = units.id 
LEFT JOIN suppliers ON suppliers.id = products.clientsupplierid
LEFT JOIN ordertemplateproducts ON ordertemplateproducts.productid = public.products.id
LEFT JOIN ordertemplates ON ordertemplateproducts.ordertemplateid = ordertemplates.id
WHERE products.name ~* @q AND (products.tenantId = @tenantId OR (ordertemplates.tenantId <> @tenantId AND ordertemplates.clientid = (SELECT id from clients WHERE tenantid = @tenantId))) 
AND products.isactive = True ORDER BY char_length(products.name) ASC LIMIT 20";

            var products = (await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product, Unit, Supplier, Product>(
                resultsQuery,
                (product, unit, supplier) =>
                {
                    product.Unit = unit;
                    product.ClientSupplier = supplier;

                    if (product.ClientSupplier != null)
                        product.Name += $" ({product.ClientSupplier.Name})";
                    else
                        product.Name += $" ({product.ExternalSupplier})";

                    return product;
                }, new { q, tenantId });

            })).ToList();

            var productCount = products.Count;

            if (productCount > 0 && getExpirationStatus)
            {
                var now = DateTime.UtcNow;

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

                foreach (var uniqueTenantId in products.Select(x => x.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 products)
                {
                    var productSettings = settings[product.TenantId];

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

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


            return products;
        }

        public async Task<IEnumerable<Product>> SearchProducts(string tenantId, string q, bool getExpirationStatus = false)
        {
            q = q?.PrepareQuery();

            var resultsQuery = $@"SELECT * FROM products JOIN units on products.UnitId = units.id 
WHERE products.name ~* @q AND products.tenantId = @tenantId AND products.isactive = True ORDER BY char_length(products.name) ASC LIMIT 20";

            var products = (await _repository.Query(async (dbConnection) =>
            {
                return await dbConnection.QueryAsync<Product, Unit, Product>(
                resultsQuery,
                (product, unit) =>
                {
                    product.Unit = unit;
                    return product;
                }, new { q, tenantId });

            })).ToList();

            var productCount = products.Count;

            if (productCount > 0 && getExpirationStatus)
            {
                var now = DateTime.UtcNow;

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

                foreach (var product in products)
                {
                    if (product.ContinueStockMode && calculateproductpriceexpirationcontinue)
                        product.PriceExpirationStatus = now - product.CostModified > TimeSpan.FromDays(calculateproductpriceexpirationcontinue_days) ? "No vigente" : "Vigente";

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


            return products;
        }

        public async Task<MemoryStream> GetPriceListExcel(string tenantId)
        {
            var memoryStream = new MemoryStream();

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

                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 = "Descripción";
                ws.Cells[1, 3].Value = "Precio con Impuestos";
                ws.Cells[1, 4].Value = "Unidades";
                ws.Cells[1, 5].Value = "Categoría";
                ws.Cells[1, 6].Value = "Vigencia de Precios";

                var rowIndex = 2;

                var products = await _repository.FindAll<Product, int>(tenantId);

                var now = DateTime.UtcNow;
                var productCount = products.Count();

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

                foreach (var product in products)
                {
                    if (product.ContinueStockMode && calculateproductpriceexpirationcontinue)
                        product.PriceExpirationStatus = now - product.CostModified > TimeSpan.FromDays(calculateproductpriceexpirationcontinue_days) ? "No vigente" : "Vigente";

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

                foreach (var product in products)
                {
                    ws.SetValue(rowIndex, 1, product.Code);
                    ws.SetValue(rowIndex, 2, product.Name);
                    ws.SetValue(rowIndex, 3, product.SalePriceWithTaxes.ToString("c"));
                    ws.SetValue(rowIndex, 4, GetUnitsDisplayName(product.UnitId));
                    ws.SetValue(rowIndex, 5, product.Category);
                    ws.SetValue(rowIndex, 6, product.PriceExpirationStatus);
                    rowIndex++;
                }

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

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        private string GetUnitsDisplayName(int unitId)
        {
            if (unitId == 1) return "Piezas";
            if (unitId == 2) return "Kilogramos";
            if (unitId == 3) return "Litros";

            return "";
        }

        public async Task<int> RefreshProducts(string tenantId, int skip, int count)
        {
            var roundProductCosts = await _settingsService.GetSettingValue<bool>(tenantId, ClientSettingType.CatalogsRoundProductCosts);
            var useMarginInsteadOfProfit = await _settingsService.GetSettingValue<bool>(tenantId, ClientSettingType.CatalogsUseMarginInsteadOfProfit);

            return await _repository.Query<int>(async (dbConnection) =>
            {
                //var selectQuery = $"SELECT id, suppliercost, salepricerate FROM products WHERE tenantId = @tenantId LIMIT {count} OFFSET {skip};";
                var selectQuery = $"SELECT id, suppliercost, salepricerate FROM products WHERE tenantId = @tenantId;";

                var products = await dbConnection.QueryAsync<Product>(selectQuery, new { tenantId });

                var update = new StringBuilder();
                var counter = 0;

                foreach(var product in products)
                {
                    counter++;

                    var salePrice = GetSalePrice(product.SupplierCost, product.SalePriceRate,
                        useMarginInsteadOfProfit, roundProductCosts);

                    if (salePrice != -1)
                    {
                        product.SalePrice = salePrice;
                        update.Append($"UPDATE products SET saleprice = '{salePrice}' WHERE id = '{product.Id}'; ");
                    }
                }

                await dbConnection.QueryAsync(update.ToString(), new { tenantId });

                return 0;
            });
        }

        public async Task SendList(string tenantId, string[] recipients, string emailBody, bool includeAds)
        {
            //var emailBody = await _settingsService.GetEmailTemplateBody(tenantId, EmailTemplateKey.ProductsPriceList);

            var dateTimeNow = DateTime.Now;

            var excel = await GetPriceListExcel(tenantId);

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

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

    }
}