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

namespace MisIngredientesVue.Core.Services
{
    public interface IQuotesService
    {
        Task UpdateQuote(string tenantId, int updatedBy, Quote quote);
        Task<Quote> GetQuoteById(string tenantId, int id);
        Task<FindResults<Quote>> GetQuotes(string tenantId, int skip, int take, string sortBy, string sortOrder);
        Task<Order> CreateOrderFromQuote(string tenantId, int createdBy, int quoteId, int clientId, int branchId, DateTime deliveryDate);
        Task<Quote> InsertQuote(string tenantId, int createdBy, Quote quote);
        Task PatchQuote(string tenantId, int modifiedBy, int id, JsonPatchDocument<Quote> changes);
        Task DeleteQuotes(string tenantId, int deletedBy, int[] items);
        Task<OrderTemplate> GetQuoteTemplate(string tenantId);
        Task<MemoryStream> GetPublicTemplateExcel(string tenantId, OrderTemplate orderTemplate);
    }

    public class QuotesService : IQuotesService
    {
        private readonly IRepository _repository;
        private readonly IOrdersService _ordersService;
        private readonly ISettingsService _settingsService;

        public QuotesService(IRepository repository, IOrdersService ordersService, ISettingsService settingsService)
        {
            _repository = repository;
            _ordersService = ordersService;
            _settingsService = settingsService;
        }

        public async Task DeleteQuotes(string tenantId, int deletedById, int[] quotes)
        {
            var queryTemplate = $@"
  DELETE FROM quoteproducts WHERE quoteid = [ID] AND tenantId = '{tenantId}';
  UPDATE quotes SET isactive = false, ModifiedById = {deletedById} WHERE id = [ID] AND tenantId = '{tenantId}';
";

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

                return await db.QueryAsync(query);
            });

        }

        public async Task UpdateQuote(string tenantId, int updatedBy, Quote quote)
        {
            var strBuilder = new StringBuilder();

            var quoteId = quote.Id;

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

                var existingQuoteProducts =
                    (await db.QueryAsync<QuoteProduct>(

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


                foreach (var product in quote.Products)
                {
                    var existingQuoteProduct = existingQuoteProducts.FirstOrDefault(x => x.ProductId == product.ProductId);
                    product.QuoteId = quoteId;
                    product.TenantId = tenantId;

                    if (existingQuoteProduct != null)
                    {
                        var existingQuoteProductId = existingQuoteProduct.Id;
                        existingQuoteProducts.Remove(existingQuoteProduct);
                        strBuilder.Append(_repository.GetUpdateQuery<QuoteProduct, int>(tenantId, updatedBy, x => x.Id == existingQuoteProductId, product, ref dynamicParameters));
                    }
                    else
                    {
                        strBuilder.Append(_repository.GetInsertQuery<QuoteProduct, int>(tenantId, updatedBy, product, ref dynamicParameters));
                    }
                }


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

                quote.TenantId = tenantId;
                quote.Total = CalculateTotal(quote);

                strBuilder.Append(_repository.GetUpdateQuery<Quote, int>(tenantId, updatedBy, x => x.Id == quoteId, quote, ref dynamicParameters));

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

        public decimal CalculateTotal(Quote quote)
        {
            var subtotal = 0M;
            var taxes = 0M;

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

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

                var productSubtotal = salePrice * product.Qty;

                taxes += (productSubtotal * (iva / 100M));
                taxes += (productSubtotal * (ieps / 100M));

                subtotal += productSubtotal;
            }

            var discount =
                ((quote.DiscountType == DiscountType.Amount)
                ? (quote.Discount ?? 0M)
                : (subtotal * ((quote.Discount ?? 0M) / 100M)));

            var total = subtotal - discount + taxes;

            if (total < 0)
                total = 0;

            return total;
        }

        public async Task<Quote> GetQuoteById(string tenantId, int id)
        {
            return await _repository.Query(async (db) =>
            {
                var query = $@"SELECT * FROM quotes
LEFT JOIN quoteproducts ON quoteproducts.quoteId = quotes.id
LEFT JOIN products ON products.id = quoteproducts.productid
LEFT JOIN units ON units.id = products.unitId
WHERE quotes.id = @id AND quotes.TenantId = @tenantId AND quotes.isactive = True";

                Quote quote = null;

                var quotes = await db.QueryAsync<Quote, QuoteProduct, Product, Unit, Quote>(query,
                    (q, qp, p, u) =>
                    {
                        if (quote == null)
                            quote = q;

                        quote.Products = quote.Products ?? new List<QuoteProduct>();

                        if (p != null)
                        {
                            p.Unit = u;

                            if(qp != null)
                                qp.Product = p;
                        }

                        if(qp != null)
                            quote.Products.Add(qp);

                        return quote;
                    },

                    new { id, tenantId });

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

                var now = DateTime.UtcNow;

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

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

                return quote;

            });
        }

        public async Task<FindResults<Quote>> GetQuotes(string tenantId, int skip, int take, string sortBy, string sortOrder)
        {
            if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(sortOrder))
                sortOrder = "DESC";

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

            return await _repository.Find<Quote, int>(tenantId, _ => true,
                skip, take, sortBy, sortOrder);
        }

        public async Task<Quote> InsertQuote(string tenantId, int createdBy, Quote quote)
        {
            return await _repository.Query(async (db) =>
            {
                var dynamicParameters = new DynamicParameters();

                var insertQuoteQuery = _repository.GetInsertQuery<Quote, int>(tenantId, createdBy, quote, ref dynamicParameters, "Id");

                var quoteId = db.QueryFirst<int>(insertQuoteQuery, dynamicParameters);

                var strBuilder = new StringBuilder();

                if (quote.Products != null)
                {
                    foreach (var product in quote.Products.Where(x => x.Qty > 0))
                    {
                        product.QuoteId = quoteId;
                        product.TenantId = tenantId;
                        
                        strBuilder.Append(_repository.GetInsertQuery<QuoteProduct, int>(tenantId, createdBy, product, ref dynamicParameters, "id"));
                    }

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

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

                quote.Id = quoteId;
                return quote;
            });
        }

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

        public async Task<Order> CreateOrderFromQuote(string tenantId, int createdBy, int quoteId, int clientId, int branchId, DateTime deliveryDate)
        {
            var quote = await GetQuoteById(tenantId, quoteId);

            if (quote == null)
                throw new UserFriendlyException("No se encontró la cotización especificada");

            var order = new Order()
            {
                TemplateId = null,
                DeliveryStatus = DeliveryStatus.Pending,
                HasFixedDiscount = quote.HasFixedDiscount,
                PaymentStatus = PaymentStatus.Pending,
                DeliveryDate = deliveryDate,
                ClientId = clientId,
                BranchId = branchId,
                Discount = quote.Discount,
                DiscountType = quote.DiscountType,
                Total = CalculateTotal(quote),
                Products = quote.Products.Select(x => new OrderProduct()
                {
                    Id = x.Id,
                    Iva = x.Iva,
                    Ieps = x.Ieps,
                    FixedPrice = x.FixedPrice,
                    SalePrice = x.SalePrice,
                    SupplierCost = x.SupplierCost,
                    Product = x.Product,
                    ProductId = x.ProductId,
                    Qty = x.Qty,
                    Specification = x.Specification
                }).ToList()
            };
            return await _ordersService.InsertOrder(tenantId, createdBy, order);
        }

        public async Task<MemoryStream> GetPublicTemplateExcel(string tenantId, OrderTemplate orderTemplate)
        {
            var memoryStream = new MemoryStream();

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

                var colIndex = 7;

                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, 7].Style.Font.Bold = true;

                ws.Cells[1, 1].Value = "Artículo";
                ws.Cells[1, 2].Value = "Cantidad";
                ws.Cells[1, 3].Value = "Unidad";
                ws.Cells[1, 4].Value = "Precio";
                ws.Cells[1, 5].Value = "Precio c/impuesto";
                ws.Cells[1, 6].Value = "Vigencia de precio";
                ws.Cells[1, 7].Value = "Subtotal";

                var rowIndex = 2;

                var products = orderTemplate.Products.OrderBy(x => x.Product.Name);

                var productCount = products.Count();

                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)
                {
                    var status = "";

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

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

                    var salePrice = OrderTemplateProduct.GetActualSalePrice(product);
                    var salePriceWTaxes = salePrice
                        + (salePrice * ((product.Product.Iva ?? 0) / 100))
                        + (salePrice * ((product.Product.Ieps ?? 0) / 100));

                    ws.SetValue(rowIndex, 1, product.Product.Name);
                    ws.SetValue(rowIndex, 2, product.Qty);
                    ws.SetValue(rowIndex, 3, product.Product.Unit.Name);
                    ws.SetValue(rowIndex, 4, salePrice);
                    ws.SetValue(rowIndex, 5, salePriceWTaxes);
                    ws.SetValue(rowIndex, 6, status);
                    ws.SetValue(rowIndex, 7, product.Qty * salePriceWTaxes);
                    //ws.SetValue(rowIndex, 7, product.Subtotal);
                    rowIndex++;
                }

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

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        public async Task<OrderTemplate> GetQuoteTemplate(string tenantId)
        {
            var orderTemplate = new OrderTemplate();

            await _repository.Query<string>(async (db) =>
            {
                var products = await db.QueryAsync<Product, Unit, Product>(
                    @"SELECT * FROM products INNER JOIN units ON units.id = products.unitId WHERE tenantId = @tenantId", (p, u) =>
                    {
                        if (p != null)
                            p.Unit = u;
                        return p;

                    }, new { tenantId });
                orderTemplate.Products = products.Select(product => new OrderTemplateProduct() { 
                    Product = product,
                    ProductId = product.Id,
                    TenantId = product.TenantId
                }).ToList();

                return "";
            });

            return orderTemplate;
        }

    }
}