using Dapper;
using Microsoft.AspNetCore.Hosting;
using MisIngredientesVue.Core.Exceptions;
using MisIngredientesVue.Core.Models;
using MisIngredientesVue.Core.Services;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Serialization;

namespace MisIngredientesVue.Core.Services
{
    public interface IInvoicingService
    {
        Task<UsedInvoicingFolios> GetInvoicingFolios(string tenantId, string companyRfc, string companyName, string companyFiscalReg, string rfc, DateTime startDate, DateTime endDate);
        Task<Cfdi> GetGlobalPreviewInvoice(string tenantId, string companyRfc, string companyName, string companyFiscalReg, int[] orderIds, string rfc, string usoCfdi, string paymentForm, string paymentMethod, int timeOffset, bool breakDownOrders, OrdersFilterQuery filterQuery);
        Task<Cfdi> GenerateInvoice(string tenantId, string companyRfc, string companyName, string companyFiscalReg, int modifiedBy, int orderId, string rfc, string usoCfdi, string paymentForm, string paymentMethod, string subscriptionItemId, int timeOffset, int freeFolios, string subSubscriptionId);
        Task<Cfdi> GenerateGlobalInvoice(string tenantId, string companyRfc, string companyName, string companyFiscalReg, int modifiedBy, int[] orderIds, string rfc, string usoCfdi, string paymentForm, string paymentMethod, string subscriptionItemId, int timeOffset, bool breakDownOrders, OrdersFilterQuery filterQuery, int freeFolios, string subSubscriptionId);
        Task<Cfdi> GeneratePayment(string tenantId, int modifiedBy, int[] orderPaymentIds, string paymentMethod, string paymentDate, string paymentTime, string subscriptionItemId, int timeOffset, int freeFolios, string subSubscriptionId);
        Task<Cfdi> GetPreviewInvoice(string tenantId, string companyRfc, string companyName, string companyFiscalReg, int orderId, string rfc, string usoCfdi, string paymentForm, string paymentMethod, int timeOffset);
        Task<Cfdi> GetPreviewPayment(string tenantId, int[] orderPaymentIds, string paymentMethod, string paymentDate, string paymentTime, int timeOffset);
        Task<Cfdi> GetInvoice(string tenantId, int invoiceId, bool readXml = true);
        Task<string> CancelInvoice(string tenantId, int modifiedBy, int invoiceId, string subscriptionItemId, string motive);
        Task<Cfdi> GetPayment(string tenantId, int paymentId, bool readXml = true);
        Task<string> CancelPayment(string tenantId, int modifiedBy, int paymentId, string subscriptionItemId, string motive);
        Task SendEmail(string tenantId, Cfdi cfdi, byte[] pdf, string[] recipients, string emailBody, bool includeAds);
        Task SendCancelledEmail(string tenantId, Cfdi cfdi, string acuse, string[] recipients, string emailBody, bool includeAds);
        Task<InvoicingInfo> GetInvoicingInfo(string tenantId);
        Task<InvoicingInfo> SaveInvoicingInfo(string tenantId, InvoicingInfo info);
        Task<string[]> GetEmailsFromOrderId(string tenantId, int orderId);
        Task<string[]> GetEmailsFromOrderIds(string tenantId, int[] orderIds);
        Task ReportUsage(string tenantId, string subscriptionItemId, int userId, string folio, string uuid, CfdiRecordType type, CfdiRecordStatus status);
        Task<string> GetTenantIdFromInvoiceId(int invoiceId);
    }

    public class InvoicingService : IInvoicingService
    {
        //private readonly List<Cfdi> _cache;

        private readonly IFileStorageService _storageService;
        private readonly IOrdersService _orderService;
        private readonly IProductsService _productsService;
        private readonly IHostingEnvironment _env;
        private readonly IRepository _repository;
        private readonly ISettingsService _settingsService;
        private readonly IClientsService _clientsService;
        private readonly IEmailService _emailService;
        private readonly IBillingService _billingService;

        public InvoicingService(IOrdersService orderService,
            IFileStorageService storageService,
            IRepository repository,
            IBillingService billingService,
            IClientsService clientsService,
            IProductsService productsService,
            IEmailService emailService,
            ISettingsService settingsService,
            IHostingEnvironment env)
        {
            _orderService = orderService;
            _productsService = productsService;
            _storageService = storageService;
            _repository = repository;
            _clientsService = clientsService;
            _emailService = emailService;
            _settingsService = settingsService;
            _env = env;
            _billingService = billingService;
            //_cache = new List<Cfdi>();
        }

        public async Task SendEmail(string tenantId, Cfdi cfdi, byte[] pdf, string[] recipients, string emailBody, bool includeAds)
        {
            var companyName = await _settingsService.GetSettingValue<string>(tenantId, ClientSettingType.CompanyName);
            var filename = (cfdi.Xml.Receptor != null ? cfdi.Xml.Receptor.Rfc : string.Empty) + "_" + cfdi.Xml.Serie + cfdi.Xml.Folio;
            var subject = companyName + " - " + cfdi.Xml.Serie + "-" + cfdi.Xml.Folio;
            //var emailBody = (await _settingsService.GetEmailTemplateBody(tenantId, "invoicing.invoicefiles"))
            //    .Replace("[COMPANY_LEGAL_NAME]", companyName)
            //    .Replace("[SERIE]", cfdi.Xml.Serie)
            //    .Replace("[FOLIO]", cfdi.Xml.Folio)
            //    .Replace("[CLIENT_EMAIL]", string.Join(", ", recipients));

            emailBody = emailBody
                .Replace("[COMPANY_LEGAL_NAME]", companyName)
                .Replace("[SERIE]", cfdi.Xml.Serie)
                .Replace("[FOLIO]", cfdi.Xml.Folio)
                .Replace("[CLIENT_EMAIL]", string.Join(", ", recipients));

            var attachments = new MailAttachment[2] {

                new MailAttachment() { Name = filename + ".pdf", Data = pdf },
                new MailAttachment() { Name = filename + ".xml", Data = System.Text.Encoding.UTF8.GetBytes(cfdi.XmlAsString) }

            };

            _emailService.SendEmail(includeAds, "noreply@misingredientes.mx", recipients, subject, emailBody, true, attachments);

        }

        public async Task SendCancelledEmail(string tenantId, Cfdi cfdi, string acuse, string[] recipients, string emailBody, bool includeAds)
        {
            var filename = (cfdi.Xml.Receptor != null ? cfdi.Xml.Receptor.Rfc : string.Empty) + "_" + cfdi.Xml.Serie + cfdi.Xml.Folio;
            var companyName = await _settingsService.GetSettingValue<string>(tenantId, ClientSettingType.CompanyName);

            var subject = "Acuse de cancelación - " + companyName + " - " + cfdi.Uuid;

            if (string.IsNullOrEmpty(emailBody))
                emailBody = (await _settingsService.GetEmailTemplateBody(tenantId, EmailTemplateKey.InvoicingCancelled));

            //var emailBody = (await _settingsService.GetEmailTemplateBody(tenantId, EmailTemplateKey.InvoicingCancelled))
            //    .Replace("[COMPANY_LEGAL_NAME]", companyName)
            //    .Replace("[SERIE]", cfdi.Xml.Serie)
            //    .Replace("[FOLIO]", cfdi.Xml.Folio)
            //    .Replace("[UUID]", cfdi.Uuid)
            //    .Replace("[CLIENT_EMAIL]", string.Join(", ", recipients));

            emailBody = emailBody
               .Replace("[COMPANY_LEGAL_NAME]", companyName)
               .Replace("[SERIE]", cfdi.Xml.Serie)
               .Replace("[FOLIO]", cfdi.Xml.Folio)
               .Replace("[UUID]", cfdi.Uuid)
               .Replace("[CLIENT_EMAIL]", string.Join(", ", recipients));

            var attachments = new MailAttachment[1] {
                new MailAttachment() { Name = filename + ".xml", Data = System.Text.Encoding.UTF8.GetBytes(acuse) }
            };

            _emailService.SendEmail(includeAds, "noreply@misingredientes.mx", recipients, subject, emailBody, true, attachments);

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

            _emailService.SendEmail(includeAds, "noreply@misingredientes.mx", emailAddresses.Where(x => !string.IsNullOrEmpty(x)).ToArray(), 
                subject, emailBody, true, attachments);

        }

        private async Task<bool> HasAvailableFreeFolios(int freeFolios, string subSubscriptionId)
        {
            if (freeFolios == -1) return true;

            var usage = await _billingService.GetUsage(subSubscriptionId);

            if (usage.Qty < freeFolios)
                return true;

            return false;
        }

        public async Task<Cfdi> StampCfdi(string xml, string tenantId, int freeFolios, string subSubscriptionId)
        {
            try
            {
                if (tenantId != "test")
                {
                    if (!(await HasAvailableFreeFolios(freeFolios, subSubscriptionId)))
                    {
                        throw new EInvoicingException($"Se alcanzó el límite de {freeFolios} folios", true);
                    }
                }

                var cfdiTimbrado = new Cfdi();

                var random = new Random();
                var idComprobante = Guid.NewGuid().ToString("N").ToUpper();
                if (idComprobante.Length > 25)
                    idComprobante = idComprobante.Substring(0, 25);

                var resultados = FinkokService.sign_stamp(xml, tenantId == "demo" || tenantId == "edaaf4eab77b4987be4a86436b4bfe6e");

                if (resultados.sign_stampResult.Incidencias?.Length > 0)
                {
                    var incidencia = resultados.sign_stampResult.Incidencias[0];
                    throw new EInvoicingException(incidencia.CodigoError + " " + incidencia.MensajeIncidencia);

                }

                //Console.WriteLine(JsonConvert.SerializeObject(resultados));

                //string tipoExcepcion = "";
                //string numeroError = "";
                //string descripcion = "";

                //if (numeroError != "0")
                //{
                //    //MongoDBHelper.LogException(new Exception(xml), "", "", "XmlFactura", string.Format("[{0} {1}] {2}", numeroError, tipoExcepcion, descripcion + " " + resultados.stampResult.faultstring));
                //    throw new EInvoicingException(string.Format("[{0} {1}] {2}", numeroError, tipoExcepcion, resultados.sign_stampResult.faultstring));
                //}

                //Los siguientes datos deberán ir en la representación impresa ó PDF
                //Del XML ya con el timbre:
                //1.- Código bidimensional(resultados[4])
                //2.- Folio fiscal(//Comprobante/Complemento/TimbreFiscalDigital[UUID])
                //3.- No.Certificado del Emisor(//Comprobante/noCertificado)
                //4.- No.Certificado del SAT(//Comprobante/Complemento/TimbreFiscalDigital[noCertificadoSAT])
                //5.- Fecha y Hora de certificación(//Comprobante/Complemento/TimbreFiscalDigital[FechaTimbrado])
                //6.- Sello del CFDI(//Comprobante/Complemento/TimbreFiscalDigital[selloCFD])
                //7.- Sello del SAT(//Comprobante/Complemento/TimbreFiscalDigital[selloSAT])
                //8.- Cadena original del complemento de certificación(resultados[5])

                //Para que el código QR se vea congruente, cuando se timbra y cuando se obtiene de nuestra BD.
                //cfdiTimbrado.qr = (byte[])(resultados[4]);

                //cfdiTimbrado.xmlAsString = resultados[3].ToString();
                cfdiTimbrado.Xml = Utils.DeserializeObjectFromXmlString<Comprobante>(resultados.sign_stampResult.xml);
                cfdiTimbrado.XmlAsString = Utils.RemoveWhiteSpacesFromXmlString(resultados.sign_stampResult.xml);
                cfdiTimbrado.CadenaOriginalComplementoCertificacion = "-- NO DISPONIBLE --";

                //}

                return cfdiTimbrado;
            }
            catch (EInvoicingException iex)
            {
                throw iex;
            }
            catch (Exception ex)
            {
                throw new EInvoicingException("El servicio de facturación no se encuentra disponible en estos momentos.");
            }
        }

        public async Task<Cfdi> GetPreviewInvoice(string tenantId,
            string companyRfc, string companyName, string companyFiscalReg,
            int orderId, string rfc, string usoCfdi, string paymentForm, string paymentMethod, int timeOffset)
        {
            var order = await _orderService.GetOrderById(tenantId, -1, orderId);

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

            var comprobante = GetInvoiceXml(order, companyRfc, companyName, companyFiscalReg, order.Client, usoCfdi, paymentForm, paymentMethod, timeOffset);

            return new Cfdi()
            {
                Orders = new List<Order>() { order },
                Xml = comprobante,
                CadenaOriginalComplementoCertificacion = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                Serie = "XXX",
                Folio = "XXX"

            };

            //var conceptos = new List<ComprobanteConcepto>();

            //foreach (var item in order.Products)
            //{
            //    conceptos.Add(new ComprobanteConcepto() { Cantidad = item.Qty, Descripcion = item.Product.Name });
            //}

            //var company = await _currentCompanyAccessor.GetCompany();

            //return new Cfdi()
            //{
            //    Xml = new Comprobante() { 
            //        Emisor = new ComprobanteEmisor() { 
            //            Nombre = company.Name,
            //            RegimenFiscal = company.FiscalReg,
            //            Rfc = company.Rfc
            //        },
            //        Receptor = new ComprobanteReceptor() { 
            //            Nombre = order.Client?.Name,
            //            Rfc = order.Branch?.TaxPayer?.TaxPayerIdNo,
            //            UsoCFDI = usoCfdi,
            //            ResidenciaFiscalSpecified = true,
            //            ResidenciaFiscal = order.Branch?.ShippingAddress
            //        },
            //        MetodoPagoSpecified = true,
            //        MetodoPago = paymentMethod,
            //        FormaPago = paymentForm,
            //        Fecha = DateTime.UtcNow,


            //        Conceptos = conceptos.ToArray()
            //    }
            //};

        }

        public async Task<Cfdi> GetPreviewPayment(string tenantId, int[] orderPaymentIds, string paymentMethod, string paymentDate, string paymentTime, int timeOffset)
        {
            //if (orderPayment == null)
            //    throw new NotFoundException();

            var orderPayments = new List<OrderPayment>();

            foreach (var orderPaymentId in orderPaymentIds)
            {
                orderPayments.Add(await _orderService.GetOrderPaymentById(tenantId, orderPaymentId));
            }

            var comprobante = await GetPaymentXml(true, tenantId, orderPayments.ToArray(), paymentMethod, paymentDate, paymentTime, timeOffset);

            return new Cfdi()
            {
                Orders = new List<Order>() { },
                Xml = comprobante,
                CadenaOriginalComplementoCertificacion = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                Serie = "XXX",
                Folio = "XXX"

            };
        }

        public async Task<Cfdi> GeneratePayment(string tenantId, int modifiedBy, int[] orderPaymentIds, 
            string paymentMethod, string paymentDate, string paymentTime, string subscriptionItemId, int timeOffset, int freeFolios, string subSubscriptionId)
        {
            var orderPayments = new List<OrderPayment>();

            foreach (var orderPaymentId in orderPaymentIds)
            {
                orderPayments.Add(await _orderService.GetOrderPaymentById(tenantId, orderPaymentId));
            }

            var comprobante = await GetPaymentXml(false, tenantId, orderPayments.ToArray(), paymentMethod, paymentDate, paymentTime, timeOffset);

            var xml = Serializar(comprobante);

            //Console.WriteLine(xml);

            //return null;
            //xml = File.ReadAllText(@"C:\3DS\test.txt");
            var cfdi = await StampCfdi(xml, tenantId, freeFolios, subSubscriptionId);

            cfdi.Version = cfdi.Xml.Version;
            //cfdi.Rfc = cfdi.Xml.Receptor.Rfc;
            cfdi.FormaPago = cfdi.Xml.FormaPago;
            cfdi.MetodoPago = cfdi.Xml.MetodoPago;

            //var cfdi = await StampCfdi(File.ReadAllText(@"C:\Users\raulb\Desktop\fallo.xml"));

            var cfdiTimbrado = await _repository.Insert<Cfdi, int>(tenantId, 1, cfdi);

            await _storageService.SaveFile(cfdiTimbrado.Uuid + ".xml", "cfdixml", UTF8Encoding.UTF8.GetBytes(cfdiTimbrado.XmlAsString));
            //File.WriteAllText(@"C:\Facturas\" + cfdiTimbrado.Id + ".xml", cfdiTimbrado.XmlAsString);

            await MarkOrderPaymentAsInvoiced(tenantId, modifiedBy, cfdi, orderPaymentIds);

            await ReportUsage(tenantId, subscriptionItemId, modifiedBy, cfdi.Folio,
                cfdi.Uuid, CfdiRecordType.Payment, CfdiRecordStatus.Generated);

            //_cache.Add(cfdi);

            var fullCfdi = await GetInvoice(tenantId, cfdiTimbrado.Id, false);
            fullCfdi.XmlAsString = cfdiTimbrado.XmlAsString;

            return fullCfdi;
            //return null;
        }

        public async Task<Cfdi> GenerateInvoice(string tenantId,
            string companyRfc, string companyName, string companyFiscalReg,
            int modifiedBy, int orderId, string rfc, string usoCfdi, string paymentForm,
            string paymentMethod, string subscriptionItemId, int timeOffset, int freeFolios, string subSubscriptionId)
        {
            if (rfc == "XAXX010101000")
                throw new UserFriendlyException("RFC Inválido");

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

            var client = order.Client;
            var comprobante = GetInvoiceXml(order, companyRfc, companyName, companyFiscalReg, client, usoCfdi, paymentForm, paymentMethod, timeOffset);

            var xml = Serializar(comprobante);

            //Console.WriteLine(xml);

            //return null;
            //xml = File.ReadAllText(@"C:\3DS\test.txt");
            var cfdi = await StampCfdi(xml, tenantId, freeFolios, subSubscriptionId);

            cfdi.Version = cfdi.Xml.Version;
            cfdi.Rfc = cfdi.Xml.Receptor.Rfc;
            cfdi.FormaPago = cfdi.Xml.FormaPago;
            cfdi.MetodoPago = cfdi.Xml.MetodoPago;

            //var cfdi = await StampCfdi(File.ReadAllText(@"C:\Users\raulb\Desktop\fallo.xml"));

            var cfdiTimbrado = await _repository.Insert<Cfdi, int>(tenantId, 1, cfdi);

            await _storageService.SaveFile(cfdiTimbrado.Uuid + ".xml", "cfdixml", UTF8Encoding.UTF8.GetBytes(cfdiTimbrado.XmlAsString));
            //File.WriteAllText(@"C:\Facturas\" + cfdiTimbrado.Id + ".xml", cfdiTimbrado.XmlAsString);

            await MarkOrdersAsInvoiced(tenantId, modifiedBy, cfdi, orderId);

            await ReportUsage(tenantId, subscriptionItemId, modifiedBy, cfdi.Folio,
                cfdi.Uuid, CfdiRecordType.Invoice, CfdiRecordStatus.Generated);

            //_cache.Add(cfdi);

            var fullCfdi = await GetInvoice(tenantId, cfdiTimbrado.Id, false);
            fullCfdi.XmlAsString = cfdiTimbrado.XmlAsString;

            return fullCfdi;
            //return null;
        }

        public async Task<Cfdi> GenerateGlobalInvoice(string tenantId, 
            string companyRfc, string companyName, string companyFiscalReg,
            int modifiedBy, int[] orderIds, 
            string rfc, string usoCfdi, string paymentForm, string paymentMethod, 
            string subscriptionItemId, int timeOffset, bool breakDownOrders, 
            OrdersFilterQuery filterQuery, int freeFolios, string subSubscriptionId)
        {
            if (rfc == "XAXX010101000")
                throw new UserFriendlyException("RFC Inválido");

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

            var orders = await _orderService.GetOrdersByIds(tenantId, orderIds);

            if (orders == null || orders.Count() == 0)
                throw new UserFriendlyException("No se encontraron los pedidos especificados");

            //if(orders.Count() != orderIds.Length)
            //    throw new UserFriendlyException($"Algunos de los pedidos especificados no se encontraron");

            if (orders.Select(x => x.ClientId).Distinct().Count() != 1)
                throw new UserFriendlyException("No se permite contar con clientes distintos en una misma factura");

            var client = orders.ElementAt(0).Client;
            var comprobante = await GetMultiInvoiceXml(false, tenantId, orders.ToArray(), companyRfc, companyName, companyFiscalReg, client, usoCfdi, paymentForm, paymentMethod, timeOffset, breakDownOrders);

            var xml = Serializar(comprobante);

            //Console.WriteLine(xml);

            //return null;
            //xml = File.ReadAllText(@"C:\3DS\test.txt");
            var cfdi = await StampCfdi(xml, tenantId, freeFolios, subSubscriptionId);

            cfdi.Version = cfdi.Xml.Version;
            cfdi.Rfc = cfdi.Xml.Receptor.Rfc;
            cfdi.FormaPago = cfdi.Xml.FormaPago;
            cfdi.MetodoPago = cfdi.Xml.MetodoPago;

            //var cfdi = await StampCfdi(File.ReadAllText(@"C:\Users\raulb\Desktop\fallo.xml"));

            var cfdiTimbrado = await _repository.Insert<Cfdi, int>(tenantId, 1, cfdi);

            await _storageService.SaveFile(cfdiTimbrado.Uuid + ".xml", "cfdixml", UTF8Encoding.UTF8.GetBytes(cfdiTimbrado.XmlAsString));
            //File.WriteAllText(@"C:\Facturas\" + cfdiTimbrado.Id + ".xml", cfdiTimbrado.XmlAsString);

            await MarkOrdersAsInvoiced(tenantId, modifiedBy, cfdi, orderIds);

            await ReportUsage(tenantId, subscriptionItemId, modifiedBy, cfdi.Folio,
                cfdi.Uuid, CfdiRecordType.Invoice, CfdiRecordStatus.Generated);

            //_cache.Add(cfdi);

            var fullCfdi = await GetInvoice(tenantId, cfdiTimbrado.Id, false);
            fullCfdi.XmlAsString = cfdiTimbrado.XmlAsString;

            return fullCfdi;
            //return null;
        }

        private async Task MarkOrdersAsInvoiced(string tenantId, int modifiedBy, Cfdi cfdi, params int[] orderIds)
        {
            await _repository.Update<Order, int>(tenantId, modifiedBy, x => orderIds.Contains(x.Id), new UpdateInfo<Order>[] {
                new UpdateInfo<Order>() { Expression = x => x.InvoiceId, Value = cfdi.Id }
            });
        }

        private async Task MarkOrderPaymentAsInvoiced(string tenantId, int modifiedBy, Cfdi cfdi, params int[] orderPaymentIds)
        {
            await _repository.Update<OrderPayment, int>(tenantId, modifiedBy, x => orderPaymentIds.Contains(x.Id), new UpdateInfo<OrderPayment>[] {
                new UpdateInfo<OrderPayment>() { Expression = x => x.PaymentId, Value = cfdi.Id }
            });
        }

        private DateTime AddTimeToDate(DateTime dateTime, string time)
        {
            try
            {
                return new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, Int32.Parse(time.Split(':')[0]), Int32.Parse(time.Split(':')[1]), 0);
            }
            catch
            {
                return new DateTime(dateTime.Year, dateTime.Month, dateTime.Day, 12, 0, 0);
            }
        }

        private async Task<Comprobante> GetPaymentXml(bool isPreview, string tenantId, OrderPayment[] orderPayments, string paymentMethod, string paymentDate, string paymentTime, int timeOffset)
        {
            //TODO: Hacer algo con paymentDate o borrarlo de plano

            /*
             * Ejemplo de Complemento de pago para una factura de 666 pesos
             * Un pago puede tener varios doctos relacionados
             <?xml version="1.0" encoding="ISO-8859-1"?>
                <cfdi:Comprobante LugarExpedicion="20131" TipoDeComprobante="P" Total="0" Moneda="XXX" SubTotal="0" Certificado="" Fecha="2018-09-18T00:06:39" Folio="1" Serie="H" Version="3.3" xsi:schemaLocation="http://www.sat.gob.mx/sitio_internet/cfd/3/cfdv33.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tfd="http://www.sat.gob.mx/TimbreFiscalDigital" xmlns:cfdi="http://www.sat.gob.mx/cfd/3">
                    <cfdi:Emisor RegimenFiscal="605" Nombre="Una razón rh de cv" Rfc="LEGH870601DM7"/>
                    <cfdi:Receptor Nombre="Pepe SA DE CV" Rfc="BIO091204LB1" UsoCFDI="P01"/>
                    <cfdi:Conceptos>
                        <cfdi:Concepto Importe="0" ValorUnitario="0" Descripcion="Pago" ClaveUnidad="ACT" Cantidad="1" ClaveProdServ="84111506"/>
                    </cfdi:Conceptos>
                    <cfdi:Complemento>
                        <pago10:Pagos Version="1.0" xmlns:pago10="http://www.sat.gob.mx/Pagos">
                            <pago10:Pago Monto="666" MonedaP="MXN" FormaDePagoP="01" FechaPago="2018-09-18T00:06:39">
                                <pago10:DoctoRelacionado ImpPagado="600" MetodoDePagoDR="PUE" MonedaDR="MXN" IdDocumento="BEDC8964-7E57-4604-9968-7E01378E8706"/>
                                <pago10:DoctoRelacionado ImpPagado="66" MetodoDePagoDR="PUE" MonedaDR="MXN" IdDocumento="BEDC8964-7E57-4604-9968-7E01378E8706"/>
                            </pago10:Pago>
                        </pago10:Pagos>
                        <tfd:TimbreFiscalDigital Version="1.1" xsi:schemaLocation="http://www.sat.gob.mx/TimbreFiscalDigital http://www.sat.gob.mx/sitio_internet/cfd/TimbreFiscalDigital/TimbreFiscalDigitalv11.xsd" RfcProvCertif="EME000602QR9" SelloSAT="GICyRB9uhyZ1u6zuj8ZpxWM92gU2kDfmodwTvXoLy3ZMxrXWsqrF16tYfwBxK8eZDmXoxnKWTfoubcyt/DtgxDHDEyqRLf+oYashKByApRLRtIYTvivrm1XZCL6RuYlKyNwjrsk2vx4kJfk+/vCcy9pSYfG8sXHPPNg8yNsvpAdGpN2JF8b11QpbYukgaspkCh5hEcOuuu7/kM2p0sRC1bZMzMxAbD9QCgSoRkpMxnHbif5oY60pK68b3yp417ZtaKCLk2y/oetZGkpgNnImC6KDbqo4fuXDnfqxSrjEJW9UIMiyOW92sP8LljFXicGBjLTQMq1kGLyM7SeSAG7c/Q==" NoCertificadoSAT="20001000000300022323" SelloCFD="Vj+oPDouoVyXz0Gwh2wDsrukh2XwrwK9cZXOoh+RqFd6Otwdduz/RyFyvfR9Kio13IbCeLeLEVWZneik1CU/gwVAJ6+v/CBkd2AjL32JQsaWNitAqQ+kdgmtbSO0ZAoQazTcSyzYyk7n/14EQGlIwa66OjSS77WPkiZIhz8TFF5wCKir4i3tEMGfpuok1ZVR4EIpzVc9vu+CEnTPKhxd2wsPp6aUyaYTt3wvIrle+qmBlF9mkWMYai4obBq+BCp5HhmxUBFizafE+MAdj/M8y2WSobyg6PSD75pgfPas9l9mqX4919It5AqF3n7Iadvzo1hDK2vZAbwXAYShJ3gb3g==" FechaTimbrado="2018-09-18T00:06:44" UUID="FE5B2DD2-7E57-44E0-88C9-FB1AF0D17142"/>
                    </cfdi:Complemento>
                </cfdi:Comprobante>
            */


            if (orderPayments.Length > 1 && orderPayments[0].OrderPaymentItems?.Length > 0)
                throw new BadRequestException();

            if(orderPayments.Length > 2 && orderPayments.Select(x => x.OrderId).Distinct().Count() > 1)
                throw new BadRequestException();

            var isMultiOrderPayment = orderPayments.Length == 1 && orderPayments[0].OrderPaymentItems?.Length > 0;

            var date = DateTime.UtcNow.AddMinutes(-timeOffset);
            var pagos = new Pagos();

            var payments = orderPayments.OrderBy(x => x.Number).ToArray();
            var pagosPagos = new List<PagosPago>();

            if (!isMultiOrderPayment)
            {
                for (var i = 0; i < payments.Length; i++)
                {
                    var payment = payments[i];
                    var amount = payment.Total;

                    var doctosRelacionados = new List<PagosPagoDoctoRelacionado>();

                    var doctoRelacionado = new PagosPagoDoctoRelacionado()
                    {
                        IdDocumento = payment.Order.Invoice.Uuid,
                        MonedaDR = payment.Order.Invoice.Xml?.Moneda,
                        ImpPagado = amount,
                        ImpPagadoSpecified = true,
                        MetodoDePagoDR = payment.Order.Invoice.MetodoPago,
                        NumParcialidad = payment.Number.ToString(),
                        ImpSaldoAntSpecified = true,
                        ImpSaldoAnt = payment.LeftToPay,
                        ImpSaldoInsolutoSpecified = true,
                        ImpSaldoInsoluto = payment.LeftToPay - amount

                    };

                    doctosRelacionados.Add(
                        doctoRelacionado
                    );

                    var newPagosPago = new PagosPago()
                    {
                        FechaPago = AddTimeToDate(payments[i].PaymentDate, paymentTime),
                        FormaDePagoP = paymentMethod,
                        MonedaP = "MXN",
                        Monto = amount,  //Esta es la suma de los ImpPagado de los Docto Relacionados
                        DoctoRelacionado = doctosRelacionados.ToArray()
                    };

                    pagosPagos.Add(newPagosPago);

                    //if (payment.Order?.Invoice.MetodoPago == "PPD" && num == "1")
                    //{
                    //    doctoRelacionado.ImpSaldoAntSpecified = true;
                    //    doctoRelacionado.ImpSaldoAnt = payment.Order.Total;
                    //    doctoRelacionado.ImpSaldoInsolutoSpecified = true;
                    //    doctoRelacionado.ImpSaldoInsoluto = doctoRelacionado.ImpSaldoAnt - amount;
                    //}
                }

            }
            else //Multi order
            {
                var payment = payments[0];
                payments[0].Order = payment.OrderPaymentItems[0].Order;

                var doctosRelacionados = new List<PagosPagoDoctoRelacionado>();
                decimal totalAmount = 0;


                foreach (var paymentItem in payment.OrderPaymentItems)
                {
                    totalAmount += paymentItem.Total;

                    var doctoRelacionado = new PagosPagoDoctoRelacionado()
                    {
                        IdDocumento = paymentItem.Order.Invoice.Uuid,
                        MonedaDR = paymentItem.Order.Invoice.Xml?.Moneda,
                        ImpPagado = paymentItem.Total,
                        ImpPagadoSpecified = true,
                        MetodoDePagoDR = paymentItem.Order.Invoice.MetodoPago,
                        NumParcialidad = "1",
                        ImpSaldoAntSpecified = true,
                        ImpSaldoAnt = paymentItem.Total,
                        ImpSaldoInsolutoSpecified = true,
                        ImpSaldoInsoluto = 0

                    };

                    doctosRelacionados.Add(
                        doctoRelacionado
                    );
                }
                
               
                var newPagosPago = new PagosPago()
                {
                    FechaPago = AddTimeToDate(payment.PaymentDate, paymentTime),
                    FormaDePagoP = paymentMethod,
                    MonedaP = "MXN",
                    Monto = totalAmount,  //Esta es la suma de los ImpPagado de los Docto Relacionados
                    DoctoRelacionado = doctosRelacionados.ToArray()
                };

                pagosPagos.Add(newPagosPago);
            }

            pagos.Pago = pagosPagos.ToArray();
    
            var comprobante = new Comprobante();
            comprobante.Fecha = date;
            comprobante.XmlDateTime = date.ToString("yyyy-MM-ddTHH:mm:ss");
            comprobante.Total = 0;
            comprobante.Version = "3.3";
            comprobante.SubTotal = 0;
            comprobante.TipoDeComprobante = "P";
            comprobante.Serie = "P";
            comprobante.Folio = !isPreview
                ? (await _repository.GetAutoIncrementNumber(tenantId, "paymentfolios")).ToString().PadLeft(4, '0')
                : "XXXX";
            comprobante.LugarExpedicion = payments[0].Order.Invoice.Xml?.LugarExpedicion;
            comprobante.Moneda = "XXX";
            comprobante.Receptor = payments[0].Order.Invoice.Xml?.Receptor ?? new ComprobanteReceptor();
            comprobante.Receptor.UsoCFDI = "P01";
            comprobante.Emisor = payments[0].Order.Invoice.Xml?.Emisor ?? new ComprobanteEmisor();
            

            comprobante.Conceptos = new ComprobanteConcepto[1]
            {
                new ComprobanteConcepto()
                {
                    ClaveProdServ = "84111506",
                    Cantidad = 1,
                    Descripcion = "Pago",
                    ClaveUnidad = "ACT",
                    ValorUnitario = 0,
                    Importe = 0

                }
            };

            var doc = new XmlDocument();

            using (XmlWriter writer = doc.CreateNavigator().AppendChild())
            {
                new XmlSerializer(pagos.GetType()).Serialize(writer, pagos);
            }

            comprobante.Complemento = new ComprobanteComplemento()
            {
                Any = new System.Xml.XmlElement[1] { doc.DocumentElement }
            };

            return comprobante;
        }

        private Comprobante GetInvoiceXml(Order order, 
            string companyRfc, string companyName, string companyFiscalReg, 
            Client client, string usoCfdi, string paymentForm, string paymentMethod, int timeOffset)
        {
            var comprobante = new Comprobante();

            var date = DateTime.UtcNow.AddMinutes(-timeOffset);

            //Datos generales

            comprobante.Version = "3.3";
            comprobante.Serie = "MI";
            comprobante.Folio = order.Folio;
            comprobante.Fecha = date;
            comprobante.XmlDateTime = date.ToString("yyyy-MM-ddTHH:mm:ss");
            comprobante.LugarExpedicion = "02980"; //TODO
            //comprobante.Moneda = order.CurrencyId;
            comprobante.Moneda = "MXN";

            comprobante.MetodoPagoSpecified = true;
            comprobante.MetodoPago = paymentMethod;
            comprobante.FormaPagoSpecified = true;
            comprobante.FormaPago = paymentForm;

            //var NumCtaPago = (additionalInfo != null && additionalInfo["lastBankNumbers"] != null) ? additionalInfo["lastBankNumbers"].ToString() : null;
            //comprobante.NumCtaPago = !string.IsNullOrEmpty(NumCtaPago) ? NumCtaPago : null;
            //comprobante.SubTotal = order.Total;
            //comprobante.Total = order.Total;

            //comprobante.sello = EInvoicing.ObtenSello();
            //comprobante.certificado = EInvoicing.ObtenCertificado(company != null ? company.rfc : string.Empty);
            //comprobante.noCertificado = EInvoicing.ObtenNoCertificado(company != null ? company.rfc : string.Empty);

            //Tipo de comprobante
            //Ingreso: Factura 1, Rec honorarios 4, rec de arrendamiento 5, Rec donativos 7, Nota de cargo 3
            //Egreso: Nota de credito 2
            //Traslado: Carta porte  6
            comprobante.TipoDeComprobante = "I"; //I = Ingreso, E = Egreso, T = Traslado, N = Nómina, P = Pago

            //Emisor
            comprobante.Emisor = new ComprobanteEmisor();
            comprobante.Emisor.Rfc = companyRfc;
            comprobante.Emisor.Nombre = companyName;
            comprobante.Emisor.RegimenFiscal = companyFiscalReg;

            //Expedido En

            //Receptor

            comprobante.Receptor = new ComprobanteReceptor();

            if (order.Branch?.TaxPayer != null)
            {
                comprobante.Receptor.Rfc = order.Branch.TaxPayer.TaxPayerIdNo;
                //comprobante.Receptor.Rfc = "XAXX010101000";
                //comprobante.Receptor.Nombre = "Prueba";
                comprobante.Receptor.Nombre = order.Branch.TaxPayer.LegalName;
            }

            comprobante.Receptor.ResidenciaFiscal = order.Branch?.ShippingAddress;
            comprobante.Receptor.ResidenciaFiscalSpecified = false;
            comprobante.Receptor.UsoCFDI = usoCfdi;

            //Conceptos
           
            var conceptos = new List<ComprobanteConcepto>();

            //var taxTotal = 0M;
            //var hasTaxes = false;
            //bool hasAmountDiscount = false;
            //decimal totalDiscounts = 0;
            //var dictionary = new Dictionary<string, ComprobanteImpuestosTraslado>();

            var totals = _orderService.CalculateTotal(order);

            var impuestos = new List<ComprobanteImpuestosTraslado>();

            foreach(var tax in totals.Taxes)
            {
                impuestos.Add(new ComprobanteImpuestosTraslado()
                {
                    TasaOCuota = (tax.Pc / 100).ToString().PadRight(8, '0'),
                    TipoFactor = "Tasa",
                    Impuesto = tax.Type == "IVA" ? "002" : "003",
                    Importe = tax.Value
                });
            }

            foreach (var product in totals.Products)
            {
                var productTaxes = new List<ComprobanteConceptoImpuestosTraslado>();

                if(product.IvaPc > 0)
                {
                    productTaxes.Add(new ComprobanteConceptoImpuestosTraslado()
                    {
                        Base = product.Subtotal,
                        Importe = product.Iva,
                        ImporteSpecified = true,
                        Impuesto = "002",
                        TasaOCuota = (product.IvaPc / 100).ToString().PadRight(8, '0'),
                        TasaOCuotaSpecified = true,
                        TipoFactor = "Tasa"
                    });
                }

                if (product.IepsPc > 0)
                {
                    productTaxes.Add(new ComprobanteConceptoImpuestosTraslado()
                    {
                        Base = product.Subtotal,
                        Importe = product.Ieps,
                        ImporteSpecified = true,
                        Impuesto = "003",
                        TasaOCuota = (product.IepsPc / 100).ToString().PadRight(8, '0'),
                        TasaOCuotaSpecified = true,
                        TipoFactor = "Tasa"
                    });
                }

                conceptos.Add(new ComprobanteConcepto()
                {
                    Descuento = product.Discount,
                    DescuentoSpecified = product.Discount > 0,
                    ClaveProdServ = product.Product.Product.ClaveProductoSat?.Trim(),
                    Cantidad = product.Product.Qty,
                    Unidad = ObtenerNombreClaveUnidad(product.Product.Product.ClaveUnidadSat?.Trim()),
                    ClaveUnidad = product.Product.Product.ClaveUnidadSat?.Trim(),
                    NoIdentificacion = product.Product.Product.Code,
                    Descripcion = product.Product.Product.Name,
                    ValorUnitario = product.Price,
                    Importe = product.Subtotal,
                    Impuestos = productTaxes.Count > 0 
                    ? new ComprobanteConceptoImpuestos()
                    {
                        Traslados = productTaxes.ToArray()
                    } : null

                });

                //var productSalePrice = (product.FixedPrice.HasValue && product.FixedPrice.Value > 0) 
                //    ? product.FixedPrice.Value 
                //    : ((product.SalePrice.HasValue && product.SalePrice.Value > 0) 
                //        ? product.SalePrice.Value 
                //        : product.Product.SalePrice);

                //if (productSalePrice > 0)
                //{
                //    var impuestos = new List<ComprobanteConceptoImpuestosTraslado>();
                //    var subtotal = Math.Round(productSalePrice * product.Qty, 2);

                //    if (product.Product.Iva != null)
                //    {
                //        var iva = product.Iva.HasValue ? product.Iva.Value : product.Product.Iva;

                //        if (iva.HasValue && iva.Value > 0)
                //        {
                //            hasTaxes = true;
                //            var key = "IVA" + iva;
                //            var productTaxTotal = Math.Round(subtotal * (iva.Value / 100M), 2);

                //            if (!dictionary.ContainsKey(key))
                //                dictionary.Add(key, new ComprobanteImpuestosTraslado()
                //                {
                //                    TasaOCuota = (iva / 100).ToString().PadRight(8, '0'),
                //                    TipoFactor = "Tasa",
                //                    Impuesto = "002",
                //                    Importe = 0M
                //                });

                //            dictionary[key].Importe += productTaxTotal;

                //            impuestos.Add(new ComprobanteConceptoImpuestosTraslado()
                //            {
                //                Base = subtotal,
                //                Importe = productTaxTotal,
                //                ImporteSpecified = true,
                //                Impuesto = "002", //IVA
                //                TasaOCuota = (iva / 100).ToString().PadRight(8, '0'),
                //                TasaOCuotaSpecified = true,
                //                TipoFactor = "Tasa"
                //            });

                //            taxTotal += productTaxTotal;
                //        }
                //    }

                //    if (product.Product.Ieps != null)
                //    {
                //        var ieps = product.Ieps.HasValue ? product.Ieps.Value : product.Product.Ieps;

                //        if (ieps.HasValue && ieps.Value > 0)
                //        {
                //            hasTaxes = true;
                //            var key = "IEPS" + ieps;
                //            var productTaxTotal = Math.Round(subtotal * (ieps.Value / 100M), 2);

                //            if (!dictionary.ContainsKey(key))
                //                dictionary.Add(key, new ComprobanteImpuestosTraslado()
                //                {
                //                    TasaOCuota = (ieps / 100).ToString().PadRight(8, '0'),
                //                    TipoFactor = "Tasa",
                //                    Impuesto = "003",
                //                    Importe = 0M
                //                });

                //            dictionary[key].Importe += productTaxTotal;

                //            impuestos.Add(new ComprobanteConceptoImpuestosTraslado()
                //            {
                //                Base = subtotal,
                //                Importe = productTaxTotal,
                //                ImporteSpecified = true,
                //                Impuesto = "003", //IEPS
                //                TasaOCuota = (ieps / 100).ToString().PadRight(8, '0'),
                //                TasaOCuotaSpecified = true,
                //                TipoFactor = "Tasa"
                //            });

                //            taxTotal += productTaxTotal;
                //        }
                //    }

                //    decimal discountValue = 0;
                //    decimal importe = Math.Round(productSalePrice * product.Qty, 2);

                //    if (order.Discount.HasValue)
                //    {
                //        if (order.DiscountType == DiscountType.Amount)
                //        {
                //            hasAmountDiscount = true;
                //        }
                //        else if (order.DiscountType == DiscountType.Percentage)
                //        {
                //            discountValue = importe * (order.Discount.Value / 100M);
                //        }
                //        discountValue = TwoDecimals(discountValue);

                //        totalDiscounts += discountValue;
                //    }


                //    conceptos.Add(new ComprobanteConcepto()
                //    {
                //        Descuento = discountValue,
                //        DescuentoSpecified = discountValue > 0,
                //        ClaveProdServ = product.Product.ClaveProductoSat?.Trim(),
                //        Cantidad = product.Qty,
                //        Unidad = ObtenerNombreClaveUnidad(product.Product.ClaveUnidadSat?.Trim()),
                //        ClaveUnidad = product.Product.ClaveUnidadSat?.Trim(),
                //        NoIdentificacion = product.Product.Code,
                //        Descripcion = product.Product.Name,
                //        ValorUnitario = productSalePrice,
                //        Importe = importe,
                //        Impuestos = impuestos.Count > 0
                //        ? new ComprobanteConceptoImpuestos()
                //        {
                //            Traslados = impuestos.ToArray()
                //        } : null

                //    });
                //}

            }

            comprobante.Conceptos = conceptos.ToArray();

            comprobante.SubTotal = totals.Subtotal;
            comprobante.Total = totals.Total;

            //Impuestos

            if (impuestos.Count > 0)
            {
                comprobante.Impuestos = new ComprobanteImpuestos()
                {
                    TotalImpuestosTrasladadosSpecified = true,
                    TotalImpuestosTrasladados = totals.TotalTaxes,
                    Traslados = impuestos.ToArray()
                };
            }

            //Descuentos
            comprobante.DescuentoSpecified = false;
            if (totals.Discount > 0)
            {
                comprobante.DescuentoSpecified = true;
                comprobante.Descuento = totals.Discount;
            }

            return comprobante;
        }

        private void GetConceptos(Order order, ref List<ComprobanteConcepto> conceptos, 
            ref Dictionary<string, ComprobanteImpuestosTraslado> taxesDict,
            ref decimal taxTotal,
            ref bool hasTaxes,
            ref bool hasDiscount,
            ref decimal totalDiscounts)
        {

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

                if (productSalePrice > 0)
                {
                    var impuestos = new List<ComprobanteConceptoImpuestosTraslado>();
                    var subtotal = Math.Round(productSalePrice * product.Qty, 2, MidpointRounding.AwayFromZero);

                    if (product.Product.Iva != null)
                    {
                        var iva = product.Iva.HasValue ? product.Iva.Value : product.Product.Iva;

                        if (iva.HasValue && iva.Value > 0)
                        {
                            hasTaxes = true;
                            var key = "IVA" + iva;
                            var productTaxTotal = Math.Round(subtotal * (iva.Value / 100M), 2, MidpointRounding.AwayFromZero);

                            if (!taxesDict.ContainsKey(key))
                                taxesDict.Add(key, new ComprobanteImpuestosTraslado()
                                {
                                    TasaOCuota = (iva / 100).ToString().PadRight(8, '0'),
                                    TipoFactor = "Tasa",
                                    Impuesto = "002",
                                    Importe = 0M
                                });

                            taxesDict[key].Importe += productTaxTotal;

                            impuestos.Add(new ComprobanteConceptoImpuestosTraslado()
                            {
                                Base = subtotal,
                                Importe = productTaxTotal,
                                ImporteSpecified = true,
                                Impuesto = "002", //IVA
                                TasaOCuota = (iva / 100).ToString().PadRight(8, '0'),
                                TasaOCuotaSpecified = true,
                                TipoFactor = "Tasa"
                            });

                            taxTotal += productTaxTotal;
                        }
                    }

                    if (product.Product.Ieps != null)
                    {
                        var ieps = product.Ieps.HasValue ? product.Ieps.Value : product.Product.Ieps;

                        if (ieps.HasValue && ieps.Value > 0)
                        {
                            hasTaxes = true;
                            var key = "IEPS" + ieps;
                            var productTaxTotal = Math.Round(subtotal * (ieps.Value / 100M), 2, MidpointRounding.AwayFromZero);

                            if (!taxesDict.ContainsKey(key))
                                taxesDict.Add(key, new ComprobanteImpuestosTraslado()
                                {
                                    TasaOCuota = (ieps / 100).ToString().PadRight(8, '0'),
                                    TipoFactor = "Tasa",
                                    Impuesto = "003",
                                    Importe = 0M
                                });

                            taxesDict[key].Importe += productTaxTotal;

                            impuestos.Add(new ComprobanteConceptoImpuestosTraslado()
                            {
                                Base = subtotal,
                                Importe = productTaxTotal,
                                ImporteSpecified = true,
                                Impuesto = "003", //IEPS
                                TasaOCuota = (ieps / 100).ToString().PadRight(8, '0'),
                                TasaOCuotaSpecified = true,
                                TipoFactor = "Tasa"
                            });

                            taxTotal += productTaxTotal;
                        }
                    }

                    decimal discountValue = 0;
                    decimal importe = Math.Round(productSalePrice * product.Qty, 2, MidpointRounding.AwayFromZero);

                    if (order.Discount.HasValue)
                    {
                        if (order.DiscountType == DiscountType.Amount)
                        {
                            hasDiscount = true;
                        }
                        else if (order.DiscountType == DiscountType.Percentage)
                        {
                            discountValue = importe * (order.Discount.Value / 100M);
                        }
                        discountValue = TwoDecimals(discountValue);

                        totalDiscounts += discountValue;
                    }


                    conceptos.Add(new ComprobanteConcepto()
                    {
                        Order = order,
                        Descuento = discountValue,
                        DescuentoSpecified = discountValue > 0,
                        ClaveProdServ = product.Product.ClaveProductoSat?.Trim(),
                        Cantidad = product.Qty,
                        Unidad = ObtenerNombreClaveUnidad(product.Product.ClaveUnidadSat?.Trim()),
                        ClaveUnidad = product.Product.ClaveUnidadSat?.Trim(),
                        NoIdentificacion = product.Product.Code,
                        Descripcion = $"{product.Product.Name} (Pedido {order.Folio})",
                        ValorUnitario = productSalePrice,
                        Importe = importe,
                        Impuestos = impuestos.Count > 0
                            ? new ComprobanteConceptoImpuestos()
                            {
                                Traslados = impuestos.ToArray()
                            } 
                            : null

                    });
                }

            }
        }

        private static decimal TwoDecimals(decimal d)
        {
            var asString = d.ToString();
            if (asString.Contains("."))
            {
                return Convert.ToDecimal(asString.Split('.')[0] + "." + asString.Split('.')[1].Substring(0, asString.Split('.')[1].Length == 1 ? 1 : 2), System.Globalization.CultureInfo.InvariantCulture);
            }
            return d;
        }

        public async Task<InvoicingInfo> GetInvoicingInfo(string tenantId)
        {
            return await _repository.Query<InvoicingInfo>(async (db) =>
            {
                var info = await db.QueryFirstOrDefaultAsync<InvoicingInfo>("SELECT * FROM invoicinginfos WHERE tenantId = @tenantId", new { tenantId });
                return info ?? new InvoicingInfo()
                {
                    Cer = null,
                    Col = "",
                    Email = "",
                    Key = null,
                    KeyPass = "",
                    Name = "",
                    NumExt = "",
                    NumSerie = "",
                    Phone = "",
                    NumInt = "",
                    RegFiscal = "",
                    Mun = "",
                    State = "",
                    Rfc = "",
                    Street = "",
                    Zip = "",
                    TenantId = "",
                    Id = 0
                };
            });
        }

        public async Task<InvoicingInfo> SaveInvoicingInfo(string tenantId, InvoicingInfo info)
        {
            var cer = string.Empty;
            var key = string.Empty;

            try
            {
                using (var stream = info.Cer.OpenReadStream())
                {
                    using (var streamReader = new StreamReader(stream))
                    {
                        cer = await streamReader.ReadToEndAsync();
                        cer = Convert.ToBase64String(Encoding.UTF8.GetBytes(cer));
                    }
                }
            }
            catch
            {
                
            }

            try
            {
                using (var stream = info.Key.OpenReadStream())
                {
                    using (var streamReader = new StreamReader(stream))
                    {
                        key = await streamReader.ReadToEndAsync();
                        key = Convert.ToBase64String(Encoding.UTF8.GetBytes(key));
                    }
                }
            }
            catch
            {

            }

            if (!string.IsNullOrEmpty(cer) && !string.IsNullOrEmpty(key))
            {
                try
                {
                    FinkokService.registration_edit(info.Rfc, cer, key, info.KeyPass, tenantId == "demo" || tenantId == "edaaf4eab77b4987be4a86436b4bfe6e");
                    //
                }
                catch (EInvoicingException ex2)
                {
                    if(ex2.Message.ToLower().Contains("el rfc no se encuentra registrado"))
                    {
                        try
                        {
                            FinkokService.registration_add(info.Rfc, cer, key, info.KeyPass, "O", tenantId == "demo" || tenantId == "edaaf4eab77b4987be4a86436b4bfe6e");
                        }
                        catch (EInvoicingException ex)
                        {
                            throw new UserFriendlyException(ex.Message);
                        }
                    }
                    else
                        throw new UserFriendlyException(ex2.Message);
                    
                }
            }

            await _repository.Query<InvoicingInfo>(async (db) =>
            {
                var query = @$"INSERT INTO public.invoicinginfos (tenantid, name, rfc, email, street, numint, numext, col, mun, zip, state, regfiscal, phone, numserie) 
VALUES (@tenantId, @name, @rfc, @email, @street, @numInt, @numExt, @col, @mun, @zip, @state, @regFiscal, @phone, @numSerie)
ON CONFLICT(tenantid) DO
UPDATE SET name = @name, rfc = @rfc, email = @email, street = @street, numint = @numInt, numext = @numExt, col = @col, 
mun = @mun, zip = @zip, state = @state, regfiscal = @regFiscal, phone = @phone, numserie = @numSerie;";

                await db.QueryFirstOrDefaultAsync<InvoicingInfo>(query, new
                {
                    tenantId,
                    name = info.Name,
                    rfc = info.Rfc,
                    email = info.Email,
                    street = info.Street,
                    numInt = info.NumInt,
                    numExt = info.NumExt,
                    col = info.Col,
                    mun = info.Mun,
                    zip = info.Zip,
                    state = info.State,
                    regFiscal = info.RegFiscal,
                    phone = info.Phone,
                    numSerie = info.NumSerie
                });

                return null;
            });

            return await GetInvoicingInfo(tenantId);
        }

        public async Task<Cfdi> GetGlobalPreviewInvoice(string tenantId, 
            string companyRfc, string companyName, string companyFiscalReg,
            int[] orderIds, string rfc, string usoCfdi, string paymentForm, string paymentMethod, int timeOffset, bool breakDownOrders, OrdersFilterQuery filterQuery)
        {
            if (orderIds.Length == 0)
            {
                var filterExpression = _orderService.GetFilterExpressionFromQuery(filterQuery);
                orderIds = (await _repository.FindIds<Order, int>(tenantId, filterExpression)).ToArray();
            }

            var orders = (await _orderService.GetOrdersByIds(tenantId, orderIds)).ToArray();

            if (orders == null || orders.Count() == 0)
                throw new UserFriendlyException("No se encontraron los pedidos especificados.");

            if (orders.Select(x => x.ClientId).Distinct().Count() != 1)
                throw new UserFriendlyException("No se pueden generar facturas con clientes distintos.");

            var comprobante = await GetMultiInvoiceXml(true, tenantId, orders, companyRfc, companyName, companyFiscalReg,
                orders.First().Client, usoCfdi, paymentForm, paymentMethod, timeOffset, breakDownOrders);

            return new Cfdi()
            {
                Orders = orders.ToList(),
                Xml = comprobante,
                CadenaOriginalComplementoCertificacion = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
                Serie = "XXX",
                Folio = "XXX"

            };
        }


        public async Task<string[]> GetEmailsFromOrderId(string tenantId, int orderId)
        {
            return await _repository.Query(async (db) =>
            {
                //                var query = @"
                //SELECT 
                //(CASE WHEN public.clientbranches.sendinvoice THEN public.clientbranches.manageremail ELSE NULL END) as manageremail, 
                //(CASE WHEN public.clienttaxpayers.sendinvoice THEN public.clienttaxpayers.email ELSE NULL END) as email,
                //(CASE WHEN public.clientbrands.sendinvoice THEN public.clientbrands.contactemail ELSE NULL END) as contactemail
                //FROM orders
                //left join public.clients ON public.clients.id = public.orders.clientid
                //left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
                //left join public.clienttaxpayers ON public.clienttaxpayers.id = public.clientbranches.taxpayerid
                //left join public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
                //WHERE orders.id = @orderId
                //AND orders.tenantId = @tenantId 
                //AND clients.tenantId = @tenantId  
                //AND clientbranches.tenantId = @tenantId 
                //AND clienttaxpayers.tenantId = @tenantId 
                //AND clientbrands.tenantId = @tenantId
                //";

                var query = @"
SELECT 
(CASE WHEN public.clienttaxpayers.sendinvoice THEN public.clienttaxpayers.email ELSE NULL END) as email
FROM orders
left join public.clients ON public.clients.id = public.orders.clientid
left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
left join public.clienttaxpayers ON public.clienttaxpayers.id = public.clientbranches.taxpayerid
WHERE orders.id = @orderId
AND orders.tenantId = @tenantId 
AND clients.tenantId = @tenantId  
AND clientbranches.tenantId = @tenantId
AND clienttaxpayers.tenantId = @tenantId

UNION

SELECT 
(CASE WHEN public.clientbranches.sendinvoice THEN public.clientbranches.manageremail ELSE NULL END) as email
FROM orders
left join public.clients ON public.clients.id = public.orders.clientid
left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
WHERE orders.id = @orderId
AND orders.tenantId = @tenantId 
AND clients.tenantId = @tenantId  
AND clientbranches.tenantId = @tenantId

UNION

SELECT 
(CASE WHEN public.clientbrands.sendinvoice THEN public.clientbrands.contactemail ELSE NULL END) as email
FROM orders
left join public.clients ON public.clients.id = public.orders.clientid
left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
left join public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid 
WHERE orders.id = @orderId
AND orders.tenantId = @tenantId 
AND clients.tenantId = @tenantId  
AND clientbranches.tenantId = @tenantId
AND clientbrands.tenantId = @tenantId
";

                var emails = await db.QueryAsync<string>(query, new { tenantId, orderId });
                return emails.Distinct().Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();

            });


        }

        public async Task<string[]> GetEmailsFromOrderIds(string tenantId, int[] orderIds)
        {
            return await _repository.Query(async (db) =>
            {
                var query = $@"
SELECT 
(CASE WHEN public.clienttaxpayers.sendinvoice THEN public.clienttaxpayers.email ELSE NULL END) as email
FROM orders
left join public.clients ON public.clients.id = public.orders.clientid
left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
left join public.clienttaxpayers ON public.clienttaxpayers.id = public.clientbranches.taxpayerid
WHERE orders.id IN ({string.Join(',', orderIds.Select(x => x.ToString()))})
AND orders.tenantId = @tenantId 
AND clients.tenantId = @tenantId  
AND clientbranches.tenantId = @tenantId
AND clienttaxpayers.tenantId = @tenantId

UNION

SELECT 
(CASE WHEN public.clientbranches.sendinvoice THEN public.clientbranches.manageremail ELSE NULL END) as email
FROM orders
left join public.clients ON public.clients.id = public.orders.clientid
left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
WHERE orders.id IN ({string.Join(',', orderIds.Select(x => x.ToString()))})
AND orders.tenantId = @tenantId 
AND clients.tenantId = @tenantId  
AND clientbranches.tenantId = @tenantId

UNION

SELECT 
(CASE WHEN public.clientbrands.sendinvoice THEN public.clientbrands.contactemail ELSE NULL END) as email
FROM orders
left join public.clients ON public.clients.id = public.orders.clientid
left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
left join public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid 
WHERE orders.id IN ({string.Join(',', orderIds.Select(x => x.ToString()))})
AND orders.tenantId = @tenantId 
AND clients.tenantId = @tenantId  
AND clientbranches.tenantId = @tenantId
AND clientbrands.tenantId = @tenantId
";

                var emails = await db.QueryAsync<string>(query, new { tenantId });
                return emails.Distinct().Where(x => !string.IsNullOrWhiteSpace(x)).ToArray();

            });


        }

        private async Task<Comprobante> GetMultiInvoiceXml(bool isPreview, string tenantId, Order[] orders, 
            string companyRfc, string companyName, string companyFiscalReg, Client client, 
            string usoCfdi, string paymentForm, string paymentMethod, int timeOffset, bool breakDownOrders = false)
        {
            var comprobante = new Comprobante();
            var date = DateTime.UtcNow.AddMinutes(-timeOffset);

            //Datos generales
            comprobante.Version = "3.3";
            comprobante.Serie = "MI";

            comprobante.Folio = !isPreview
                ? "Mult" + (await _repository.GetAutoIncrementNumber(tenantId, "multcfdis")).ToString().PadLeft(4, '0')
                : "MultXXXX";

            comprobante.Fecha = date;
            comprobante.XmlDateTime = date.ToString("yyyy-MM-ddTHH:mm:ss");
            comprobante.LugarExpedicion = "02980"; //TODO
            //comprobante.Moneda = orders.FirstOrDefault()?.CurrencyId ?? "MXN"; //TODO
            comprobante.Moneda = "MXN";
            comprobante.MetodoPagoSpecified = true;
            comprobante.MetodoPago = paymentMethod;
            comprobante.FormaPagoSpecified = true;
            comprobante.FormaPago = paymentForm;

            //var NumCtaPago = (additionalInfo != null && additionalInfo["lastBankNumbers"] != null) ? additionalInfo["lastBankNumbers"].ToString() : null;
            //comprobante.NumCtaPago = !string.IsNullOrEmpty(NumCtaPago) ? NumCtaPago : null;


            //comprobante.sello = EInvoicing.ObtenSello();
            //comprobante.certificado = EInvoicing.ObtenCertificado(company != null ? company.rfc : string.Empty);
            //comprobante.noCertificado = EInvoicing.ObtenNoCertificado(company != null ? company.rfc : string.Empty);

            //Tipo de comprobante
            //Ingreso: Factura 1, Rec honorarios 4, rec de arrendamiento 5, Rec donativos 7, Nota de cargo 3
            //Egreso: Nota de credito 2
            //Traslado: Carta porte  6
            comprobante.TipoDeComprobante = "I"; //I = Ingreso, E = Egreso, T = Traslado, N = Nómina, P = Pago

            //Emisor
            comprobante.Emisor = new ComprobanteEmisor();
            comprobante.Emisor.Rfc = companyRfc;
            comprobante.Emisor.Nombre = companyName;
            comprobante.Emisor.RegimenFiscal = companyFiscalReg;

            //Receptor

            comprobante.Receptor = new ComprobanteReceptor();

            if (orders[0].Branch?.TaxPayer != null)
            {
                comprobante.Receptor.Rfc = orders[0].Branch.TaxPayer.TaxPayerIdNo;
                comprobante.Receptor.Nombre = orders[0].Branch.TaxPayer.LegalName;
            }

            comprobante.Receptor.Nombre = client.Name;
            comprobante.Receptor.UsoCFDI = usoCfdi;
            comprobante.Receptor.ResidenciaFiscal = orders[0].Branch?.ShippingAddress;
            comprobante.Receptor.ResidenciaFiscalSpecified = false;

            //Conceptos

            decimal taxTotal = 0;
            var hasTaxes = false;
            var hasDiscount = false;
            var conceptos = new List<ComprobanteConcepto>();
            var taxesDict = new Dictionary<string, ComprobanteImpuestosTraslado>();
            decimal totalDiscounts = 0;

            foreach (var order in orders)
            {
                if(breakDownOrders)
                {
                    GetConceptos(order, ref conceptos, ref taxesDict, ref taxTotal, ref hasTaxes, ref hasDiscount, ref totalDiscounts);
                }
                else
                {
                    var orderSubtotal = order.Products.Sum(x =>
                    {
                        var productSalePrice = (x.FixedPrice.HasValue && x.FixedPrice.Value > 0)
                            ? x.FixedPrice.Value
                            : ((x.SalePrice.HasValue && x.SalePrice.Value > 0) 
                                ? x.SalePrice.Value 
                                : x.Product.SalePrice);

                        return Math.Round(productSalePrice * x.Qty, 2, MidpointRounding.AwayFromZero);

                    });

                    if (order.Discount.HasValue)
                    {
                        var discountValue = 0M;
                        if (order.DiscountType == DiscountType.Amount)
                        {
                            hasDiscount = true;
                        }
                        else if (order.DiscountType == DiscountType.Percentage)
                        {
                            discountValue = orderSubtotal * (order.Discount.Value / 100M);
                        }
                        discountValue = TwoDecimals(discountValue);

                        totalDiscounts += discountValue;
                    }

                    conceptos.Add(new ComprobanteConcepto()
                    {
                        Order = order,
                        DescuentoSpecified = false,
                        ClaveProdServ = "01010101",
                        Cantidad = 1,
                        Unidad = "PIEZA",
                        ClaveUnidad = "H87",
                        NoIdentificacion = order.Id.ToString(),
                        Descripcion = "Pedido " + order.Folio,
                        ValorUnitario = orderSubtotal,
                        Importe = orderSubtotal
                    });

                }

            }

            comprobante.Conceptos = conceptos.ToArray();

            //Descuentos
            if (hasDiscount)
            {
                foreach (var product in comprobante.Conceptos)
                {
                    product.DescuentoSpecified = true;
                    decimal pc = product.Order.Discount.Value / product.Order.Total;
                    product.Descuento = TwoDecimals(product.Importe * pc);

                    if(product.Descuento == 0)
                    {
                        product.DescuentoSpecified = false;
                    }
                    totalDiscounts += product.Descuento;
                }
            }

            comprobante.SubTotal = conceptos.Sum(x => x.Importe);
            comprobante.Total = TwoDecimals(comprobante.SubTotal + taxTotal - totalDiscounts);

            //Impuestos
            if (hasTaxes)
            {
                comprobante.Impuestos = new ComprobanteImpuestos()
                {
                    TotalImpuestosTrasladadosSpecified = true,
                    TotalImpuestosTrasladados = Math.Round(taxTotal, 2, MidpointRounding.AwayFromZero),
                    Traslados = taxesDict.Keys.Select(x => taxesDict[x]).ToArray()
                };
            }

            comprobante.DescuentoSpecified = false;
            if (totalDiscounts > 0)
            {
                comprobante.DescuentoSpecified = true;
                comprobante.Descuento = TwoDecimals(totalDiscounts);
            }

            return comprobante;
        }

        private string Serializar(Comprobante cfdi)
        {
            var xmlSerializerNamespaces = new XmlSerializerNamespaces();
            xmlSerializerNamespaces.Add("xsi", "http://www.w3.org/2001/XMLSchema-instance");
            xmlSerializerNamespaces.Add("cfdi", "http://www.sat.gob.mx/cfd/3");
            return Utils.SerializeObjectToXmlString<Comprobante>(cfdi, xmlSerializerNamespaces);
        }

        private string ObtenerNombreClaveUnidad(string claveUnidad)
        {
            switch (claveUnidad)
            {
                case "H87": return "PIEZA";
                case "X3H": return "BIDON";
                case "KGM": return "KILOGRAMO";
                case "XBH": return "MANOJO";
                case "X4H": return "DOMO (CAJA PLASTICO)";
                case "XCX": return "LATA CILINDRICA ";
                case "E54": return "VIAJE";
                case "DPC": return "DOCENA";
                case "LTR": return "LITRO";
                case "E48": return "UNIDAD DE SERVICIO";
                case "XDP": return "GARRAFA";
            };
            return claveUnidad;
        }

        public async Task<string> CancelInvoice(string tenantId, int modifiedBy, int invoiceId, string subscriptionItemId, string motive)
        {
            var cfdi = await GetInvoice(tenantId, invoiceId);
            var orderIds = await _repository.Query(async (db) =>
            {
                return await db.QueryAsync<int>
                    ($"SELECT id FROM public.orders WHERE invoiceid = {invoiceId} AND tenantid = @tenantId", new { tenantId });
            });

            var orderIdsArray = orderIds.ToArray();

            var acuse = FinkokService.sign_cancel(cfdi.Uuid, cfdi.Xml.Emisor.Rfc, cfdi.Xml.NoCertificado, motive, tenantId == "demo" || tenantId == "edaaf4eab77b4987be4a86436b4bfe6e");

            await _repository.Update<Order, int>(tenantId, modifiedBy, x => orderIdsArray.Contains(x.Id), new UpdateInfo<Order>[] {
                new UpdateInfo<Order>() { Expression = x => x.InvoiceId, Value = null }
            });

            await SetCancelled(tenantId, cfdi.Uuid);
            //await ReportUsage(tenantId, subscriptionItemId, modifiedBy, cfdi.Folio,
            //    cfdi.Uuid, CfdiRecordType.Invoice, CfdiRecordStatus.Cancelled);

            return acuse;
        }


        public async Task<Cfdi> GetInvoice(string tenantId, int invoiceId, bool readXml = true)
        {
            var cfdi = await _repository.Query(async (db) =>
            {
                var query = $@"SELECT *
	FROM public.cfdis
	left join public.orders ON public.orders.invoiceId = public.cfdis.id
    left join public.clients ON public.clients.id = public.orders.clientid
	left join public.clientbranches ON public.clientbranches.id = public.orders.branchid
    left join public.clienttaxpayers ON public.clienttaxpayers.id = public.clientbranches.taxpayerid
    left join public.clientbrands ON public.clientbrands.id = public.clientbranches.clientbrandid
	WHERE public.cfdis.id = @invoiceId AND public.cfdis.tenantId = @tenantId";

                Cfdi cfdiData = null;

                await db.QueryAsync<Cfdi, Order, Client, ClientBranch, ClientTaxPayer, ClientBrand, Cfdi>(query,
                    (c, o, cl, cbranch, ctaxpayer, cbrand) =>
                    {
                        if (cfdiData == null)
                        { 
                            cfdiData = c;
                            cfdiData.Orders = new List<Order>();
                        }

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

                            if(cbranch != null)
                                o.Branch = cbranch;

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

                            if (ctaxpayer != null && o.Branch != null)
                                o.Branch.TaxPayer = ctaxpayer;

                            cfdiData.Orders.Add(o);
                        }

                        return cfdiData;
                    },

                    new { invoiceId, tenantId });

                return cfdiData;

            });

            if(readXml)
            {
                cfdi.XmlAsString = UTF8Encoding.UTF8.GetString(await _storageService.LoadFile(cfdi.Uuid + ".xml", "cfdixml"));
                //cfdi.XmlAsString = File.ReadAllText(@"C:\Facturas\" + cfdi.Id + ".xml"); 
            }

            return cfdi;
        }

        public async Task<Cfdi> GetPayment(string tenantId, int paymentId, bool readXml = true)
        {
            var cfdi = await _repository.Query(async (db) =>
            {
                var query = $@"SELECT DISTINCT public.cfdis.*
	FROM public.cfdis
	left join public.orderpayments ON public.orderpayments.paymentid = public.cfdis.id
	WHERE public.cfdis.id = @paymentId AND public.cfdis.tenantId = @tenantId";

                Cfdi cfdiData = await _repository.QuerySingle<Cfdi>(query, new { paymentId, tenantId });
                return cfdiData;

            });

            if (readXml)
            {
                cfdi.XmlAsString = UTF8Encoding.UTF8.GetString(await _storageService.LoadFile(cfdi.Uuid + ".xml", "cfdixml"));
                //cfdi.XmlAsString = File.ReadAllText(@"C:\Facturas\" + cfdi.Id + ".xml"); 
            }

            return cfdi;
        }

        public async Task<string> CancelPayment(string tenantId, int modifiedBy, int paymentId, string subscriptionItemId, string motive)
        {
            var cfdi = await GetPayment(tenantId, paymentId);

            var paymentIds = await _repository.Query(async (db) =>
            {
                return await db.QueryAsync<int>($"SELECT id FROM public.orderpayments WHERE paymentid = {paymentId}");
            });

            var idsArray = paymentIds.ToArray();

            var acuse = FinkokService.sign_cancel(cfdi.Uuid, cfdi.Xml.Emisor.Rfc, cfdi.Xml.NoCertificado, motive, tenantId == "demo" || tenantId == "edaaf4eab77b4987be4a86436b4bfe6e");

            await _repository.Update<OrderPayment, int>(tenantId, modifiedBy, x => idsArray.Contains(x.Id), new UpdateInfo<OrderPayment>[] {
                new UpdateInfo<OrderPayment>() { Expression = x => x.PaymentId, Value = null }
            });

            await SetCancelled(tenantId, cfdi.Uuid);
            //await ReportUsage(tenantId, subscriptionItemId, modifiedBy, cfdi.Folio,
            //    cfdi.Uuid, CfdiRecordType.Payment, CfdiRecordStatus.Cancelled);

            return acuse;
        }

        public async Task<UsedInvoicingFolios> GetInvoicingFolios(string tenantId, 
            string companyRfc, string companyName, string companyFiscalReg, 
            string rfc, DateTime startDate, DateTime endDate)
        {
            var usedFolios = FinkokService.report_total(rfc, startDate, endDate, tenantId == "demo" || tenantId == "edaaf4eab77b4987be4a86436b4bfe6e");

            return new UsedInvoicingFolios()
            {
                Free = usedFolios,
                Surplus = 0, //TODO: Obtener los folios excedentes
                Total = usedFolios
            }; 
        }

        private async Task SetCancelled(string tenantId, string uuid)
        {
            await _repository.Query<string>(async (db) =>
            {
                await db.QueryAsync(@$"
UPDATE cfdiusagerecords SET status = {(int)CfdiRecordStatus.Cancelled} WHERE uuid = @uuid AND tenantId = @tenantId", new
                {
                    tenantId,
                    uuid
                });
                return null;
            });
        }

        //subscriptionItemId = ID DE ELEMENTO DE LA SUSCRIPCIÓN, por ejemplo si_IeV2vPnOQfyNDZ (Ver en Subscripción -> Tarifas)
        public async Task ReportUsage(string tenantId, string subscriptionItemId, int userId, string folio, string uuid, CfdiRecordType type, CfdiRecordStatus status)
        {
            if (tenantId == "test" || tenantId == "demo")
            {
                return;
            }

            Stripe.StripeConfiguration.ApiKey = BillingService.API_KEY;

            if (string.IsNullOrEmpty(subscriptionItemId)) return;

            var timestamp = DateTime.UtcNow;
            var service = new Stripe.UsageRecordService();

            var response = await service.CreateAsync(subscriptionItemId, new Stripe.UsageRecordCreateOptions()
            {
                Timestamp = timestamp,
                Quantity = 1
            });

            var record = new CfdiUsageRecord()
            {
                Created = timestamp,
                CreatedById = userId,
                Status = status,
                Type = type,
                Uuid = uuid,
                Folio = folio,
                SubscriptionItemId = subscriptionItemId,
                UsageRecordId = response.Id,
                TenantId = tenantId
            };

            await _repository.Query<string>(async (db) =>
            {
                await db.QueryAsync(@$"
INSERT INTO cfdiusagerecords (createdbyid, created, status, type, uuid, folio, subscriptionitemid, usagerecordid, tenantid) 
VALUES(@createdbyid, @created, @status, @type, @uuid, @folio, @subscriptionitemid, @usagerecordid, @tenantid)", new
                {
                    created = record.Created,
                    createdbyid = record.CreatedById,
                    status = record.Status,
                    type = record.Type,
                    uuid = record.Uuid,
                    folio = record.Folio,
                    subscriptionitemid = record.SubscriptionItemId,
                    usagerecordid = record.UsageRecordId,
                    tenantid = record.TenantId
                });
                return null;
            });
        }

        public async Task<string> GetTenantIdFromInvoiceId(int invoiceId)
        {
            return await _repository.Query<string>(async (db) =>
            {
                return await db.QueryFirstAsync<string>(@$"
SELECT tenantId FROM cfdis WHERE id = {invoiceId}");
            });
        }
    }

    public class UsedInvoicingFolios
    {
        public int Total { get; set; }
        public int Free { get; set; }
        public int Surplus { get; set; }
    }

    public class PaymentEmails
    {
        public string ManagerEmail { get; set; }
        public string Email { get; set; }
        public string ContactEmail { get; set; }
    }
}