using Npgsql;
using System;
using System.Linq;
using Dapper;
using MongoDB.Driver;
using MongoDB.Bson;
using MisIngredientesVue.Core.Models;
using Microsoft.Extensions.Options;
using MisIngredientesVue.Core.Services;
using System.Reflection;
using System.ComponentModel.DataAnnotations;
using System.Collections.Generic;
using System.IO;
using System.Text;
using MisIngredientesVue.Core.Extensions;
using HashidsNet;
using System.Diagnostics;
using System.Threading.Tasks;

namespace MisIngredientes.Migrator
{
    class Program
    {
        private static string GetEnumDisplayName(Enum enumValue)
        {
            return enumValue.GetType()
                            .GetMember(enumValue.ToString())
                            .First()
                            .GetCustomAttribute<DisplayAttribute>()
                            .GetName();
        }

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

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

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

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

            return code;
        }

        private static void CompareDatabaseTables()
        {
            var cloudConnString = "Username=ikhzyydr;Host=isilo.db.elephantsql.com;Database=ikhzyydr;Password=QEIcvsSg5D9nwoUtAGP1bKM0KzYwCF3M";            
            var localConnString = "Host=localhost;Username=postgres;Database=misingredientes;Password=adminadmin";

            var cloudOptions = Options.Create(new DatabaseOptions
            {
                ConnectionString = cloudConnString
            });

            var localOptions = Options.Create(new DatabaseOptions
            {
                ConnectionString = localConnString
            });

            var cloudRepository = new BaseRepository(cloudOptions);
            var localRepository = new BaseRepository(localOptions);


            var task = localRepository.Query(async (db) =>
            {
                var strBuilder = new StringBuilder();
                var tables = await db.QueryAsync<string>("select table_name from information_schema.tables where table_schema = 'public'");
                var tables2 = await cloudRepository.Query(async (db2) =>
                {
                    var otherTables = await db2.QueryAsync<string>("select table_name from information_schema.tables where table_schema = 'public'");
                    return otherTables;
                });

                foreach (var table in tables)
                {
                    if (table == "deleteme" || table == "testentities") continue;

                    var columns = await db.QueryAsync<string>($"SELECT CONCAT(column_name, ';', data_type) as column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table}';");

                    var columns2 = await cloudRepository.Query(async (db2) =>
                    {
                        var otherColumns = await db2.QueryAsync<string>($"SELECT CONCAT(column_name, ';', data_type) as column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{table}';");
                        return otherColumns;
                    });

                    if(columns.Count() != columns2.Count())
                    {
                        var newColumns = columns.Except(columns2);
                        Console.WriteLine(table);
                        Console.WriteLine(string.Join(",", newColumns));

                        strBuilder.AppendLine(string.Join("", newColumns.Select(x => $"ALTER TABLE {table} ADD COLUMN {x.Split(";")[0]} {x.Split(";")[1].Replace("timestamp without time zone", "TIMESTAMP")}; ")));
                    }

                }

                var query = strBuilder.ToString();

                if (!string.IsNullOrEmpty(query))
                {
                    await cloudRepository.Query(async (db2) =>
                    {
                        await db2.QueryAsync<string>(query);
                        return "";
                    });
                }

               
                return "";
            });

            task.Wait();

        }

        public class Result
        {
            public decimal Abril { get; set; }
            public decimal Mayo { get; set; }
            public decimal Junio { get; set; }

        }

        private static string GetRandomString(int length)
        {
            var random = new Random();
            var str = new StringBuilder();
            for(var i=0; i < length; i++)
            {
                str.Append(random.Next(1, 10).ToString());
            }

            return str.ToString();
        }

        private static string CreateClient(string tenantId, string userName, string userLastName, string email, string clientName, string phone)
        {
            string password = $"misingredientes_{GetRandomString(5)}";
            string hashedPassword = BCrypt.Net.BCrypt.HashPassword(password, UsersService.HASH);
            var connString = "Host=165.227.62.7;Username=miadmin;Database=misingredientes;Password=D$f8dJKcMs%dLDc1kc08D79dfd";

            var options = Options.Create(new DatabaseOptions
            {
                ConnectionString = connString
            });

            var repository = new BaseRepository(options);

            var task = repository.Query<string>(async (db) =>
            {
                string insertIntoUsersSql = @$"INSERT INTO public.users(

    firstname, lastname, email, role, password, isclient, tenantid, created, isactive, resetpassword, sendexternalorderemail, isconfirmed)
	VALUES('{userName}', '{userLastName}', '{email}', 0, '{hashedPassword}', true, '{tenantId}', '2021-11-29', true, false, false, true) RETURNING id";

                var newId = await db.ExecuteScalarAsync<int>(insertIntoUsersSql);

                string insertIntoClientsSql = @$"INSERT INTO public.clients(
	name, contact, phone, email, tenantid, createdbyid, created, modifiedbyid, modified, isactive, website, isverified, tags)
	VALUES ('{clientName}', '{userName} {userLastName}', '{phone}', '{email}', '{tenantId}', {newId}, '2021-11-29', {newId}, '2021-11-29',
			true, '', true, '');";

                await db.ExecuteScalarAsync(insertIntoClientsSql);

                string insertIntoClientSuppliersSql = $"INSERT INTO client_suppliers (client_tenantid, supplier_tenantid) VALUES ('{tenantId}', 'test')";

                await db.ExecuteScalarAsync(insertIntoClientSuppliersSql);

                return "";


            });
            task.Wait();

            return password;
        }

        public static OrderCalculations CalculateTotal(Order order)
        {
            var subtotal = 0M;
            var totalTaxes = 0M;

            var products = new List<ProductCalculations>();

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

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

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

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

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

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

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

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

                totalTaxes += productIva + productIeps;

                subtotal += productSubtotal;

                decimal productDiscount = 0;

                if (order.Discount.HasValue)
                {
                    if (order.DiscountType == DiscountType.Percentage)
                    {
                        productDiscount = Math.Round(subtotal * (order.Discount.Value / 100M), 2, MidpointRounding.AwayFromZero);
                    }
                }

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

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

            var total = subtotal - discount + totalTaxes;

            if (total < 0) total = 0;

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

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

        private static void InitDatabase()
        {

            var pass1 = CreateClient("ruculaavandaro", "Juan", "Pérez", "rucula.avandaro@gmail.com", "Rúcula Avándaro", "555555555");

            Console.WriteLine(pass1);

            //            var connString2 = "Host=165.227.62.7;Username=miadmin;Database=misingredientes;Password=D$f8dJKcMs%dLDc1kc08D79dfd";

            //            var options2 = Options.Create(new DatabaseOptions
            //            {
            //                ConnectionString = connString2
            //            });

            //            var repository2 = new BaseRepository(options2);

            //            for (var id = 1; id < 999999; id++)
            //            {

            //                var task = repository2.Query<string>(async (db) =>
            //                {

            //                    var ordersToUpdate = new List<Order>();

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

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

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

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

            //        return order;

            //    });


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

            //                            var total = CalculateTotal(orderToUpdate);
            //                            updateQueryBuilder.Add($"UPDATE orders SET total = '{total.Total}' WHERE id = {orderToUpdate.Id}; ");
            //                    }

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

            //                    return "";

            //                });
            //                task.Wait();

            //            }




            //            //var pass1 = CreateClient("incarnate", "Carolina", "Medina", "sandra.medina@ciw.edu.mx", "Centro Universitario Incarnate Word", "5549243853");
            //            //var pass2 = CreateClient("verdanna", "Anabella", "Fretes", "verdannamx@gmail.com", "Verdanna", "123456789");


            //            //var pass1 = CreateClient("decima", "Francisco", "Janeiro", "ladecimacantina@gmail.com", "La décima cantina", "5591967836");
            //            //var pass2 = CreateClient("yaxcoffee", "Yanin", "Torres Tanus", "yanintt@gmail.com", "YAX COFFEE", "5512283076");
            //            //var pass3 = CreateClient("galea", "Rafael", "Zaga Credi", "rafazaga1@gmail.com", "Galea", "5579462760");

            //            //var pass1 = CreateClient("legrand", "Fernando", "Pérez", "restaurante@seniorliving.com", "Le Grand Senior Living", "5530298182");

            //            //var pass1 = CreateClient("padovano", "David", "Ercolin", "padovanomx@gmail.com", "PADOVANO", "552296464");


            //            return;

            //var algo = BCrypt.Net.BCrypt.HashPassword("misingredientes_92302", UsersService.HASH);
            //var algo2 = BCrypt.Net.BCrypt.HashPassword("moraduraznos02", UsersService.HASH);

            //return;
            //var cfdisss = new Cfdi();

            //cfdisss.XmlAsString = File.ReadAllText(@"C:\Users\raulb\Desktop\TXT\factura.xml");

            //var test = BCrypt.Net.BCrypt.HashPassword("Azul1995.", UsersService.HASH);
            //return;
            //return;
            var connString = "Host=localhost;Username=postgres;Database=misingredientes;Password=adminadmin";
            //var connString = "Username=ikhzyydr;Host=isilo.db.elephantsql.com;Database=ikhzyydr;Password=QEIcvsSg5D9nwoUtAGP1bKM0KzYwCF3M";
            //var connString = "Host=165.227.62.7;Username=miadmin;Database=misingredientes;Password=D$f8dJKcMs%dLDc1kc08D79dfd";

            var options = Options.Create(new DatabaseOptions
            {
                ConnectionString = connString
            });

            var repository = new BaseRepository(options);
            var strBuilder = new List<string>();

            var jlop679 = repository.CreateTable<ClientMinTotal>();
            jlop679.Wait();
            jlop679 = repository.CreateTable<BranchInventory>();
            jlop679.Wait();
            jlop679 = repository.CreateTable<Supplier>();
            jlop679.Wait();
            jlop679 = repository.CreateTable<SupplierAccount>();
            jlop679.Wait();
            jlop679 = repository.CreateTable<SupplierContact>();
            jlop679.Wait();
            jlop679 = repository.CreateTable<Client_Supplier>();
            jlop679.Wait();
            //jlop679 = repository.CreateTable<SupplierOrderTemplate>();
            //jlop679.Wait();

            return;

            var ddd = repository.Query<string>(async (db) =>
            {

                var query = @$"

SELECT ROUND(SUM(total), 2) as sum FROM (SELECT
orders.id, 
(SUM(((CASE WHEN (orderproducts.fixedprice IS NULL OR orderproducts.fixedprice = 0) THEN (CASE WHEN (orderproducts.saleprice IS NULL OR orderproducts.saleprice = 0) then products.saleprice ELSE orderproducts.saleprice END) ELSE orderproducts.fixedprice END)
 * orderproducts.qty) +
        (((CASE WHEN (orderproducts.fixedprice IS NULL OR orderproducts.fixedprice = 0) THEN (CASE WHEN (orderproducts.saleprice IS NULL OR orderproducts.saleprice = 0) then products.saleprice ELSE orderproducts.saleprice END) ELSE orderproducts.fixedprice END)
 * orderproducts.qty) * (COALESCE(CASE WHEN (orderproducts.iva IS NULL OR orderproducts.iva = 0) THEN products.iva ELSE orderproducts.iva END, 0) / 100.0)) +
         (((CASE WHEN (orderproducts.fixedprice IS NULL OR orderproducts.fixedprice = 0) THEN (CASE WHEN (orderproducts.saleprice IS NULL OR orderproducts.saleprice = 0) then products.saleprice ELSE orderproducts.saleprice END) ELSE orderproducts.fixedprice END)
 * orderproducts.qty) * (COALESCE(CASE WHEN (orderproducts.ieps IS NULL OR orderproducts.ieps = 0) THEN products.ieps ELSE orderproducts.ieps END, 0) / 100.0))
) - 0) 
- (CASE WHEN orders.discount > 0 THEN (CASE WHEN orders.discounttype = 1 THEN orders.discount ELSE ((
	SUM(((CASE WHEN (orderproducts.fixedprice IS NULL OR orderproducts.fixedprice = 0) THEN (CASE WHEN (orderproducts.saleprice IS NULL OR orderproducts.saleprice = 0) then products.saleprice ELSE orderproducts.saleprice END) ELSE orderproducts.fixedprice END)
 * orderproducts.qty))
) * (orders.discount / 100.0)) END) ELSE 0 END) as total

FROM public.orders
INNER JOIN orderproducts ON orderproducts.orderid = orders.id
INNER JOIN products ON orderproducts.productid = products.id
 WHERE orders.TenantId = 'test' AND orders.IsActive = True 
GROUP BY orders.id) as results


";

                var list = await db.QueryFirstOrDefaultAsync<decimal>(query);
                //var query = "SELECT * from users";

                

                //foreach(var item in list)
                //{
                //    strBuilder.Add($"INSERT INTO emailnotifications (tenantid, userid, sendexternalorderemail) VALUES ('{item.TenantId}', '{item.Id}', true);");
                //}

                //var d = string.Join("\r\n", strBuilder.ToArray());
                return "";

            });
            ddd.Wait();

            return;


            //            var query = @"
            //BEGIN;

            //CREATE TEMPORARY TABLE asdfasdf(id INT);

            //WITH X AS (
            //	INSERT INTO deleteme2(id, anotherid) VALUES (50,70) RETURNING id
            //) INSERT INTO asdfasdf(id) VALUES((SELECT id FROM X));

            //SELECT * FROM asdfasdf;

            //END;
            //";


            var query = @"
                INSERT INTO 
ON CONFLICT (tenantId,{GetMemberName(conflictProperty)}) DO
{GetUpdateQuery<T, TId>(tenantId, createdOrModifiedBy, null, item, ref dynamicParams)}
            ";
            var taskita = repository.QueryFirstOrDefault<int>(query, new { });

            taskita.Wait();


            //var analyticsService = new AnalyticsService(repository);

            //var d = analyticsService.LoadData("test", new DateTime[] { new DateTime(2020, 12, 01), new DateTime(2020, 12, 02) }, null, null, null, 973);
            //d.Wait();

            //var ddd = repository.CreateTable<ActivityLog>();
            //ddd.Wait();

            //var settingsService = new SettingsService(repository);
            //var algo = settingsService.GetEmailTemplateBody("test", "invoicing.cancelled");
            //algo.Wait();

            return;

            //var productsService = new ProductsService(repository, null, null);

            //var task = productsService.InsertProduct("test", 1, new Product() { });
            //task.Wait();

            //return;

            var dddddfdf = repository.Query<string>(async (db) =>
            {
                try
                {
                    await db.QueryAsync("DELETE FROM public.products WHERE ID = 1");
                }
                catch(PostgresException ex)
                {
                    if (ex.SqlState == "23503") //foreign key violation
                    {
                        await db.QueryAsync("UPDATE public.products SET isactive = false WHERE ID = 1");
                    }
                    else
                        throw ex;
                }

                return null;
            });


            dddddfdf.Wait();

                return;

            var jlop67 = repository.CreateTable<WebHook>();
            jlop67.Wait();

            var jlop2 = repository.CreateTable<Setting>();
            jlop2.Wait();

            Console.ReadLine();

            return;

            IEnumerable<Cfdi> cfdis = null;

            var t2 = repository.Query<string>((db) =>
            {
                cfdis = db.Query<Cfdi>($"SELECT * FROM cfdis");
                return Task.FromResult("");
            });

            t2.Wait();

            var _storageService = new FileStorageService();

            foreach (var cfdi in cfdis)
            {
                var fd = _storageService.LoadFile(cfdi.Uuid + ".xml", "cfdixml");
                fd.Wait();
                cfdi.XmlAsString = UTF8Encoding.UTF8.GetString(fd.Result);

                if (string.IsNullOrEmpty(cfdi.FormaPago))
                {

                    var t = repository.Query<string>((db) =>
                    {
                        db.Query($"UPDATE cfdis SET MetodoPago = '{cfdi.Xml.MetodoPago}', FormaPago = '{cfdi.Xml.FormaPago}' WHERE id = '{cfdi.Id}'");
                        return Task.FromResult("");

                    });

                    t.Wait();
                }

            }


            //var jlop = repository.CreateTable<OrderPayment>();
            //jlop.Wait();
            //jlop = repository.CreateTable<OrderPaymentItem>();
            //jlop.Wait();

            return;

            //var repository = new BaseRepository(options);
            //repository.Find


            //var repository = new BaseRepository(options);
            //repository.QuerySingle<OrderAndOrderPaymentsTotal>()

            //var jlop = repository.CreateTable<OrderPayment>();
            //jlop.Wait();
            //jlop = repository.CreateTable<OrderPayment>();
            //jlop.Wait();


            //var test = BCrypt.Net.BCrypt.HashPassword("misingredientes_4919", UsersService.HASH);


            return;

            //var res = FinkokService.sign_stamp(File.ReadAllText(@"C:\Users\raulb\Desktop\VAL.xml"));

            //var ConnectionString = "Host=raja.db.elephantsql.com;Username=szwfhujz;Database=szwfhujz;Password=sjFxJmpqlNL-qNrzSCZv1PwOGUSCBA4J"
            //var connString = "Host=165.227.62.7;Username=miadmin;Database=misingredientes;Password=D$f8dJKcMs%dLDc1kc08D79dfd";
            //var connString = "Username=ikhzyydr;Host=isilo.db.elephantsql.com;Database=ikhzyydr;Password=QEIcvsSg5D9nwoUtAGP1bKM0KzYwCF3M";            
            //var connString = "Host=localhost;Username=postgres;Database=misingredientes;Password=adminadmin";

            

            //FinkokService.sign_stamp(File.ReadAllText(@"C:\3DS\test.xml"));

         
            return;

            var dddde = repository.Query<string>(async (db) =>
            {

                var products = new string[] { "camote amarillo", 
                    "champiñón blanco mediano",
                    "champiñón cremini",
                    "chile de árbol",
                    "chile serrano",
                    "cilantro manojo",
                    "coliflor kilo",
                    "Egapack Wezer 30X600",
                    "fresa a granel kilo",
                    "frijol negro",
                    "germen de lenteja",
                    "granola",
                    "jengibre",
                    "jitomate guaje",
                    "lenteja ch",
                    "limón eureka gr",
                    "pepino",
                    "pera mantequilla",
                    "plátano tabasco",
                    "setas" };

                foreach(var productName in products)
                {
                    var query = $@"
    SELECT(SELECT SUM(qty) FROM orderproducts
     LEFT JOIN orders ON orders.id = orderproducts.orderid
	WHERE orders.clientid = 1 AND orders.deliverydate >= '2020-04-01' AND orders.deliverydate < '2020-05-01'
	AND orderproducts.productid IN (SELECT id from products WHERE name ILIKE '{productName}%')) AS ABRIL,


	(SELECT SUM(qty) FROM orderproducts
	LEFT JOIN orders ON orders.id = orderproducts.orderid
	WHERE orders.clientid = 1 AND orders.deliverydate >= '2020-05-01' AND orders.deliverydate < '2020-06-01'
	AND orderproducts.productid IN (SELECT id from products WHERE name ILIKE '{productName}%')) as MAYO,
	

	(SELECT SUM(qty) FROM orderproducts
	LEFT JOIN orders ON orders.id = orderproducts.orderid
	WHERE orders.clientid = 1 AND orders.deliverydate >= '2020-06-01' AND orders.deliverydate < '2020-07-01'
	AND orderproducts.productid IN (SELECT id from products WHERE name ILIKE '{productName}%')) as JUNIO;

";

                    var result = db.Query<Result>(query).ToArray();
                    Debug.WriteLine($"{productName}\t{result[0].Abril}\t{result[0].Mayo}\t{result[0].Junio}");


                }


                return null;

            });

            //var options = Options.Create(new DatabaseOptions
            //{
            //    //ConnectionString = "Host=raja.db.elephantsql.com;Username=szwfhujz;Database=szwfhujz;Password=sjFxJmpqlNL-qNrzSCZv1PwOGUSCBA4J"
            //    ConnectionString = connString
            //});

            //var repository = new BaseRepository(options);
            //var ordersService2 = new OrdersService(repository, null);

            //var ordersTask = ordersService2.GetOrders("test", 0, 25, "", "", null, null, null, null, null, null, null, null, null, null, null, 1);

            //ordersTask.Wait();

            //return;


            //foreach (var order in ordersTask.Result.Results)
            //{
            //    if(order.Id == 79)
            //    {
            //        var dddde = repository.Query<string>(async (db) =>
            //        {

            //            var orderData = await ordersService2.GetOrderById("test", order.Id);
            //            var qes = $"UPDATE orders SET total = '{ordersService2.CalculateTotal(orderData)}' WHERE id = '{orderData.Id}'";
            //            await db.QueryAsync(qes);
            //            return "";
            //        });

            //        dddde.Wait();
            //    }


            //}

            //var jlop = repository.CreateTable<Quote>();
            //jlop.Wait();
            //jlop = repository.CreateTable<QuoteProduct>();
            //jlop.Wait();

            //Console.ReadLine();

            //return;

            //var dfs = repository.FindAll<Product, int>("test");

            //dfs.Wait();

            //var products = dfs.Result;

            //foreach(var product in products)
            //{
            //    var code = GetNewProductId(product.Name);

            //    var dddd = repository.Query<string>(async (db) =>
            //    {
            //        await db.QueryAsync($"UPDATE products SET code = '{code}' WHERE id = '{product.Id}'");
            //        return "";
            //    });

            //    dddd.Wait();
            //}


            //return;

            //            var query = @"


            //CREATE UNIQUE INDEX products_code_unique 
            //ON products (tenantId,code);

            //";

            //            var tttt = repository.Query<string>(async (db) =>
            //            {
            //                await db.QueryAsync(query);
            //                return "";
            //            });

            //            tttt.Wait();

            //            return;



            //var ttt = repository.Find<Order, int>("test", _ => true, 0, 10);
            //ttt.Wait();

            //return;

            //var task3 = repository.Insert<User, int>("test2", null,
            //    new User()
            //    {
            //        TenantId = "test2",
            //        IsActive = true,
            //        Created = DateTime.UtcNow,
            //        FirstName = "Josué",
            //        LastName = "Tlaxcoapan",
            //        Email = "josue@pruebas.com",
            //        Password = BCrypt.Net.BCrypt.HashPassword("prueba", UsersService.HASH)

            //    }, false);

            //task3.Wait();

            //var tasksdfsdf = repository.FindOne<Test, int>("test", _ => true);
            //tasksdfsdf.Wait();
            //return;

            //            var clientsService = new ClientsService(repository);
            //            var ordersService = new OrdersService(repository, null);

            //            var deleteTask2 = repository.Query(async (db) =>
            //            {

            //                for (var i = 0; i < 6; i++)
            //                {
            //                    try { await db.QueryAsync("DROP TABLE Vehicles"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE Drivers"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE DeliveryRoutes"); } catch(Exception ex) 
            //                    {

            //                    }
            //                    try { await db.QueryAsync("DROP TABLE OrderTemplateProducts"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE OrderTemplateBrands"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE Orders"); }
            //                    catch (Exception ex)
            //                    {

            //                    }
            //                    try { await db.QueryAsync("DROP TABLE OrderTemplates"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE ClientBranches"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE ClientTaxPayers"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE ClientBrands"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE ProductTaxes"); }
            //                    catch (Exception ex)
            //                    {

            //                    }
            //                    try { await db.QueryAsync("DROP TABLE Products"); }
            //                    catch (Exception ex)
            //                    {

            //                    }
            //                    try { await db.QueryAsync("DROP TABLE OrderProducts"); }
            //                    catch (Exception ex)
            //                    {

            //                    }
            //                    try { await db.QueryAsync("DROP TABLE Clients"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE Cfdis"); }
            //                    catch (Exception ex)
            //                    {

            //                    }
            //                    try { await db.QueryAsync("DROP TABLE Users"); } catch { }
            //                    try { await db.QueryAsync("DROP TABLE Units"); } catch { }
            //                }

            //                return "";
            //            });
            //            deleteTask2.Wait();

            //            var task2 = repository.CreateTable<User>(); task2.Wait();
            //            task2 = repository.CreateTable<Unit>(); task2.Wait();
            //            task2 = repository.CreateTable<Cfdi>(); task2.Wait();
            //            task2 = repository.CreateTable<Client>(); task2.Wait();
            //            task2 = repository.CreateTable<ClientBrand>(); task2.Wait();
            //            task2 = repository.CreateTable<ClientTaxPayer>(); task2.Wait();
            //            task2 = repository.CreateTable<ClientBranch>(); task2.Wait();
            //            task2 = repository.CreateTable<OrderTemplate>(); task2.Wait();
            //            task2 = repository.CreateTable<Order>(); task2.Wait();
            //            task2 = repository.CreateTable<Product>(); task2.Wait();
            //            task2 = repository.CreateTable<OrderProduct>(); task2.Wait();
            //            task2 = repository.CreateTable<Quote>(); task2.Wait();
            //            task2 = repository.CreateTable<QuoteProduct>(); task2.Wait();
            //            task2 = repository.CreateTable<OrderTemplateProduct>(); task2.Wait();
            //            task2 = repository.CreateTable<OrderTemplateBrand>(); task2.Wait();
            //            task2 = repository.CreateTable<Vehicle>(); task2.Wait();
            //            task2 = repository.CreateTable<Driver>(); task2.Wait();
            //            task2 = repository.CreateTable<DeliveryRoute>(); task2.Wait();

            //            var otherTask = repository.Query(async (db) =>
            //            {

            //                await db.QueryAsync("INSERT INTO Units (Name, Key, UsesDecimalPlaces) VALUES ('Piezas', 'U', false)");
            //                await db.QueryAsync("INSERT INTO Units (Name, Key, UsesDecimalPlaces) VALUES ('KG', 'KG', True);");
            //                await db.QueryAsync("INSERT INTO Units (Name, Key, UsesDecimalPlaces) VALUES ('Litros', 'L', True);");

            //                return "";
            //            });
            //            otherTask.Wait();

            //            task2 = repository.Insert<User, int>("test", null,
            //                new User()
            //                {
            //                    TenantId = "test",
            //                    IsActive = true,
            //                    Created = DateTime.UtcNow,
            //                    FirstName = "Javier",
            //                    LastName = "Luna",
            //                    Email = "javier@mi.com",
            //                    Password = BCrypt.Net.BCrypt.HashPassword("misingredientes_310", UsersService.HASH)

            //                }, false);

            //            task2.Wait();

            //            task2 = repository.Insert<User, int>("test", null,
            //                new User()
            //                {
            //                    TenantId = "test",
            //                    IsActive = true,
            //                    Created = DateTime.UtcNow,
            //                    FirstName = "Martín",
            //                    LastName = "Lazo",
            //                    Email = "martin@mi.com",
            //                    Password = BCrypt.Net.BCrypt.HashPassword("misingredientes_102", UsersService.HASH)

            //                }, false);

            //            task2.Wait();

            //            task2 = repository.Insert<User, int>("test", null,
            //                new User()
            //                {
            //                    TenantId = "test",
            //                    IsActive = true,
            //                    Created = DateTime.UtcNow,
            //                    FirstName = "Yesenia",
            //                    LastName = "Ramón",
            //                    Email = "yesenia@mi.com",
            //                    Password = BCrypt.Net.BCrypt.HashPassword("misingredientes_972", UsersService.HASH)

            //                }, false);

            //            task2.Wait();

            //            task2 = repository.Insert<User, int>("test", null,
            //                new User()
            //                {
            //                    TenantId = "test",
            //                    IsActive = true,
            //                    Created = DateTime.UtcNow,
            //                    FirstName = "Raúl",
            //                    LastName = "Bojalil",
            //                    Email = "raul@mi.com",
            //                    Password = BCrypt.Net.BCrypt.HashPassword("prueba", UsersService.HASH)

            //                }, false);

            //            task2.Wait();

            //            task2 = repository.Insert<User, int>("test2", null,
            //                new User()
            //                {
            //                    TenantId = "test2",
            //                    IsActive = true,
            //                    Created = DateTime.UtcNow,
            //                    FirstName = "Josué",
            //                    LastName = "Tlaxcoapan",
            //                    Email = "josue@pruebas.com",
            //                    Password = BCrypt.Net.BCrypt.HashPassword("prueba", UsersService.HASH)

            //                }, false);

            //            task2.Wait();

            //            //Productos

            //            var productsDict = new Dictionary<int, Product>();

            //            var source = new MongoClient("mongodb://admin:vxZCJXyNEdnIbTuQ@printerparts-shard-00-00-onhce.mongodb.net:27017,printerparts-shard-00-01-onhce.mongodb.net:27017,printerparts-shard-00-02-onhce.mongodb.net:27017/test?ssl=true&replicaSet=printerparts-shard-0&authSource=admin")
            //                .GetDatabase("main").GetCollection<BsonDocument>("products");

            //            using (var target = new NpgsqlConnection(connString))
            //            {

            //                foreach (var item in source.Find(x => x["_tid"] == new ObjectId("5b58dd0e0ec7430ab6073bd3")).Limit(200).ToList())
            //                {
            //                    var code = item["codes"].AsBsonArray[0].AsString;
            //                    var name = GetValue(item["description"]);
            //                    var category = GetValue(item["category"]);
            //                    var salepricerate = ConvertToDecimal(item["salepricerate"]?.ToString());
            //                    var wholesaleprice = ConvertToDecimal(item["wholesaleprice"]?.ToString());
            //                    var wholesaleqty = ConvertToDecimal(item["wholesaleqty"]?.ToString());
            //                    var unitid = ConvertToUnit(item["unit"]?.ToString());
            //                    var suppliercost = ConvertToDecimal(item["supplierprice"]?.ToString());
            //                    var saleprice = ConvertToDecimal(item["saleprice"]?.ToString());
            //                    var buyer = GetValue(item["buyer"]);
            //                    var supplier = GetValue(item["supplier"]);
            //                    var purchasezone = GetValue(item["zone"]);
            //                    var minqty = ConvertToQty(item["minstock"]?.ToString());
            //                    var qty = ConvertToQty(item["stock"]?.ToString());
            //                    var claveproductosat = GetValue(item["sat_claveproducto"]);
            //                    var claveunidadsat = GetValue(item["sat_claveunidad"]);
            //                    var taxes = item["taxes"].IsBsonNull ? null : item["taxes"].AsBsonArray;

            //                    var iva = "NULL";
            //                    var ieps = "NULL";


            //                    if (taxes != null)
            //                    {
            //                        var eiva = taxes.FirstOrDefault(x => x["name"].AsString.ToLower().Trim() == "iva");
            //                        if (eiva != null)
            //                            iva = eiva["pc"].AsString;

            //                        var eieps = taxes.FirstOrDefault(x => x["name"].AsString.ToLower().Trim() == "ieps");
            //                        if (eieps != null)
            //                            ieps = eieps["pc"].AsString;

            //                    }

            //                    var insertScript = $@"
            //INSERT INTO public.products(
            //	created, modified, createdbyid, modifiedbyid, code, name, category, salepricerate, unitid, suppliercost, saleprice, buyer, supplier, purchasezone, minqty, qty, claveproductosat, claveunidadsat, iva, ieps, tenantid)
            //	VALUES ('2020-01-01', '2020-01-01', 1, 1, '{code}', '{name}', '{category}', {salepricerate}, {unitid}, {suppliercost}, {saleprice}, '{buyer}', '{supplier}', '{purchasezone}', {minqty}, {qty}, '{claveproductosat}', '{claveunidadsat}', {iva}, {ieps}, 'test') RETURNING id;

            //";

            //                    var newId = target.Query<int>(insertScript);


            //                    productsDict.Add(newId.First(), new Product() { SalePrice = Convert.ToDecimal(saleprice) });

            //                    //                    if (taxes != null)
            //                    //                    {
            //                    //                        foreach(var tax in taxes)
            //                    //                        {
            //                    ////                            var insertScriptTax = $@"

            //                    ////INSERT INTO public.producttaxes(
            //                    ////	name, pc, productid)
            //                    ////	VALUES ('{tax["name"].AsString.ToUpper()}', '{tax["pc"].AsString}', '{newId.First()}');

            //                    ////";

            //                    ////                            target.Query<int>(insertScriptTax);

            //                    //                        }



            //                    //                    }


            //                }



            //            }

            //            return;

            //            //Clients

            //            var branchNames = new string[] { "Centro", "Insurgentes Norte", "Polanco", "Del Valle", "Iztapalapa", "Roma", "Aeropuerto", "Condesa",
            //            "Insurgentes Sur", "Tlalpan", "Ecatepec", "Nezahualcóyotl", "Bosques de las Lomas", "Interlomas", "Pedregal", "Oriente", "Poniente", "Norte", "Sur"
            //            };

            //            var clients = new List<Client>();

            //            for (var i = 0; i < 20; i++)
            //            {
            //                var brands = new List<ClientBrand>();
            //                var taxPayers = new List<ClientTaxPayer>();
            //                var branches = new List<ClientBranch>();

            //                for (var j = 0; j < 10; j++)
            //                {
            //                    brands.Add(new ClientBrand() { Id = j, Name = "Marca de prueba " + i, ContactEmail = "prueba@prueba.com", ContactPhone = "12341234" });
            //                }

            //                for (var j = 0; j < 10; j++)
            //                {
            //                    taxPayers.Add(new ClientTaxPayer() { Id = j, TaxPayerIdNo = "XXXXXXXXXXX" });
            //                }

            //                for (var j = 0; j < 10; j++)
            //                {
            //                    branches.Add(new ClientBranch()
            //                    {
            //                        Id = j,
            //                        Name = branchNames[new Random().Next(0, branchNames.Length)],
            //                        ClientBrandId = new Random().Next(0, brands.Count),
            //                        TaxPayerId = new Random().Next(0, taxPayers.Count)
            //                    });
            //                }

            //                var client = new Client()
            //                {
            //                    Branches = branches,
            //                    Brands = brands,
            //                    TaxPayers = taxPayers,
            //                    Contact = "Contacto de prueba",
            //                    Email = "prueba@prueba.com",
            //                    Phone = "12341234",
            //                    Name = "Cliente de prueba " + i
            //                };

            //                var insertTask = clientsService.InsertClient("test", 1, client);
            //                insertTask.Wait();

            //                clients.Add(insertTask.Result);
            //            }

            //            //Plantillas

            //            for (var i = 0; i < 10; i++)
            //            {
            //                task2 = repository.Insert<OrderTemplate, int>("test", 1, new OrderTemplate()
            //                {
            //                    Name = "Plantilla " + i,
            //                    ClientId = 1,


            //                });
            //                task2.Wait();
            //            }

            //            //Pedidos


            //            for (var i = 0; i < 50; i++)
            //            {
            //                var orderProducts = new List<OrderProduct>();

            //                var maxProducts = new Random().Next(3, 7);
            //                for (var j = 0; j < maxProducts; j++)
            //                {
            //                    var productId = new Random().Next(1, 50);
            //                    orderProducts.Add(new OrderProduct()
            //                    {

            //                        Product = productsDict[productId],
            //                        ProductId = productId,
            //                        Qty = new Random().Next(1, 10),
            //                        Specification = "Prueba",

            //                    });
            //                }

            //                var clientId = new Random().Next(0, clients.Count);
            //                var client = clients[clientId];

            //                task2 = ordersService.CreateOrderFromTemplate("test", 1, new Order()
            //                {
            //                    Id = new Random().Next(1, 8), //TEMPLATEID
            //                    BranchId = client.Branches[new Random().Next(0, client.Branches.Count)].Id,
            //                    ClientId = clientId,
            //                    DeliveryStatus = DeliveryStatus.Pending,
            //                    PaymentStatus = PaymentStatus.Pending,
            //                    TemplateId = new Random().Next(1, 8),
            //                    Products = orderProducts,
            //                    Total = orderProducts.Sum(x => x.Qty * x.Product.SalePrice)

            //                });
            //                task2.Wait();
            //            }

        }

        static void Main(string[] args)
        {

            InitDatabase();

            return;
            //var d = CompressionExtensions.Zip(File.ReadAllText(@"C:\Users\raulb\Desktop\revisame.xml") + File.ReadAllText(@"C:\Users\raulb\Desktop\revisame.xml"));
            //d.Wait();
            //var e = CompressionExtensions.Unzip(d.Result);
            //e.Wait();

            //var f = d.Result.Length;

           
            //CompareDatabaseTables();

            return;
            //var invService = new InvoicingService(null, null, null, null, null, null);
            //var t = invService.StampCfdi(File.ReadAllText(@"C:\Users\raulb\Desktop\fallo.xml"));
            //t.Wait();

            //return;
            //InitDatabase();

            //var cfdiSource = new MongoClient("mongodb://admin:3iw8Zbf86wNRMxZE@prodifrut-shard-00-00-pjkqz.mongodb.net:27017,prodifrut-shard-00-01-pjkqz.mongodb.net:27017,prodifrut-shard-00-02-pjkqz.mongodb.net:27017/test?ssl=true&replicaSet=Prodifrut-shard-0&authSource=admin&retryWrites=true&w=majority")
            //    .GetDatabase("59a9582851273f441937a8e3").GetCollection<BsonDocument>("cfdis");



            //var cfdis = cfdiSource.Find(

            //     MongoDB.Driver.Builders<BsonDocument>.Filter.Or(
            //    Enumerable.Range(90417, (90440 - 90417) + 1).Select(y => MongoDB.Driver.Builders<BsonDocument>.Filter.Eq(x => x["folio"], y.ToString())).ToArray()
            //)

            //x =>

            // x["folio"] == "90417"
            //|| x["folio"] == "90417"
            //|| x["folio"] == "90248"
            //|| x["folio"] == "90269"
            //|| x["folio"] == "90271"
            //|| x["folio"] == "90448"
            //|| x["folio"] == "90461"
            //|| x["folio"] == "90463"
            //|| x["folio"] == "90465"
            //|| x["folio"] == "90466"
            //|| x["folio"] == "90468"
            //|| x["folio"] == "90246"
            //|| x["folio"] == "90490" 
            //|| x["folio"] == "90491"
            //|| x["folio"] == "90492"
            //|| x["folio"] == "90493"
            //|| x["folio"] == "90494"
            //|| x["folio"] == "90495"
            //|| x["folio"] == "90496"
            //|| x["folio"] == "90497"
            //|| x["folio"] == "90498"
            //|| x["folio"] == "90499"
            //|| x["folio"] == "90501"
            //|| x["folio"] == "90502"
            //|| x["folio"] == "90503"
            //|| x["folio"] == "90500"
            //|| x["folio"] == "90501"
            //|| x["folio"] == "90502"
            //|| x["folio"] == "90503"
            //|| x["folio"] == "90504"
            //|| x["folio"] == "90505"
            //|| x["folio"] == "90506"
            //|| x["folio"] == "90507"
            //|| x["folio"] == "90508"
            //|| x["folio"] == "90509"
            //|| x["folio"] == "90510"
            //|| x["folio"] == "90511"
            //).ToList();

            //foreach (var cfdi in cfdis)
            //{
            //    File.WriteAllText(@"C:\Facturas\Factura_" + cfdi["folio"] + ".xml", cfdi["_xml"].ToString());

            //}


            //return;


            var source2 = new MongoClient("mongodb://admin:vxZCJXyNEdnIbTuQ@printerparts-shard-00-00-onhce.mongodb.net:27017,printerparts-shard-00-01-onhce.mongodb.net:27017,printerparts-shard-00-02-onhce.mongodb.net:27017/test?ssl=true&replicaSet=printerparts-shard-0&authSource=admin")
                .GetDatabase("5b58dd320ec7430ab6073bd4").GetCollection<BsonDocument>("receipts");
            var source3 = new MongoClient("mongodb://admin:vxZCJXyNEdnIbTuQ@printerparts-shard-00-00-onhce.mongodb.net:27017,printerparts-shard-00-01-onhce.mongodb.net:27017,printerparts-shard-00-02-onhce.mongodb.net:27017/test?ssl=true&replicaSet=printerparts-shard-0&authSource=admin")
                .GetDatabase("main").GetCollection<BsonDocument>("products");

            var folio = "5478";

            var ticket = source2.Find(x => x["cofolio"] == folio).First();


            File.WriteAllText(folio + "_" + DateTime.UtcNow.ToFileTime() + ".txt", ticket.ToJson());

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

            var currentTaxTotals = ticket["taxtotals"] as BsonArray;

            var totalWithoutTaxes = 0M; //Total calculado sin impuestos
            var totalAbs = 0M; //Total calculado absoluto
            long totalAbs2 = 0;
            var totalTaxes = 0M; //Total calculado de impuestos

            foreach (var item in ticket["lines"].AsBsonArray)
            {
                var currentTaxTotal = Convert.ToDecimal(item["taxtotal"] is BsonNull ? "0" : item["taxtotal"].ToString()) / 100;

                var productId = item["product"].AsObjectId;
                var product = source3.Find(x => x["_id"] == productId).First();

                var salePrice = Convert.ToDecimal(item["price"].ToString()) / 100M;
                var qty = Convert.ToDecimal(item["qty"].ToString()) / 1000M;

                var total = salePrice * qty;

                totalAbs += total;
                totalAbs2 += (Convert.ToInt64(item["price"].ToString())) * Convert.ToInt64(item["qty"].ToString());

                totalWithoutTaxes += total;

                var taxes = product["taxes"].IsBsonNull ? new BsonArray() : product["taxes"].AsBsonArray;

                var actualTaxtotal = 0M;
                long actualTaxtotal2 = 0;

                foreach (var tax in taxes)
                {
                    var pc = Convert.ToDecimal(tax["pc"].ToString());

                    var taxName = tax["name"].ToString().ToUpper();

                    var pcDisplay = pc.ToString();

                    if (pcDisplay.EndsWith(".00"))
                    {
                        pcDisplay = pcDisplay.Substring(0, pcDisplay.Length - 3);
                    }

                    if (!dictionary.ContainsKey(taxName + "-" + pcDisplay))
                        dictionary.Add(taxName + "-" + pcDisplay, 0);

                    //dictionary[taxName + "-" + pcDisplay] += (total * (pc / 100));
                    dictionary[taxName + "-" + pcDisplay] += (total * pc);

                    //if (taxName == "IEPS")
                    //    iepsTotal += (total * (pc / 100));
                    //if (taxName == "IVA")
                    //    ivaTotal += (total * (pc / 100));

                    actualTaxtotal += (total * (pc / 100));
                    actualTaxtotal2 += Convert.ToInt64(total * pc);
                }

                totalAbs += actualTaxtotal;

                if (currentTaxTotal != actualTaxtotal)
                {
                    item["taxtotal"] = Convert.ToInt64(actualTaxtotal * 100);
                }

                totalTaxes += actualTaxtotal;

            }


            var bsonArray = new BsonArray();
            long taxTotal = 0;

            foreach(var tx in dictionary.Keys)
            {
                var obj = new BsonDocument();
                obj["name"] = tx.Split("-")[0].ToUpper();
                obj["pc"] = tx.Split("-")[1];
                obj["total"] = Convert.ToInt64(dictionary[tx]);
                bsonArray.Add(obj);

                taxTotal += Convert.ToInt64(dictionary[tx]);
            }

         
            source2.UpdateOne(x => x["no"] == folio, Builders<BsonDocument>.Update.Set(x => x["_total"], Convert.ToInt64(totalAbs * 100M)));
            source2.UpdateOne(x => x["no"] == folio, Builders<BsonDocument>.Update.Set(x => x["taxtotals"], bsonArray));



            return;




            //var d = GetEnumDisplayName((Enum)Enum.Parse((new Order()).GetType().GetProperty("DeliveryStatus").PropertyType, "2"));

            //var d = (Enum)Convert.ChangeType(Enum.Parse(Enum.GetUnderlyingType(prop.PropertyType), x.Value.ToString()), Enum.GetUnderlyingType(prop.PropertyType))

            //Order order;
            //var totalProp = typeof(Order).GetProperties().FirstOrDefault(x => x.Name.ToLower() == "total");


            //return;


            //using (var target = new NpgsqlConnection(connString))
            //{
            //    target.Query("INSERT INTO public.clients(name, contact, phone, email, tenantid) VALUES ('Cliente de prueba', 'Prueba', '123456', 'prueba@prueba.com', 'test');");

            //    target.Query("INSERT INTO public.clienttaxpayers(clientid, taxpayeridno, legalname, street, intnumber, extnumber, neighborhood, municipality, state, zipcode, email, tenantid) VALUES(1, 'RFC01', 'Prueba', 'Prueba', 'Prueba', 'Prueba', 'Prueba', 'Prueba', 'Prueba', '12345', 'prueba@prueba.com', 'test');");
            //    target.Query("INSERT INTO public.clienttaxpayers(clientid, taxpayeridno, legalname, street, intnumber, extnumber, neighborhood, municipality, state, zipcode, email, tenantid) VALUES(1, 'RFC02', 'Prueba 2', 'Prueba 2', 'Prueba', 'Prueba 2', 'Prueba 2', 'Prueba 2', 'Prueba 2', '12345', 'prueba2@prueba.com', 'test');");

            //    target.Query("INSERT INTO public.clientbrands(clientid, name, contact, contactemail, contactphone, sendinvoice, tenantid) VALUES ('1', 'Marca01', 'Prueba', 'prueba@prueba.com', '12341234', true, 'test');");
            //    target.Query("INSERT INTO public.clientbrands(clientid, name, contact, contactemail, contactphone, sendinvoice, tenantid) VALUES ('1', 'Marca02', 'Prueba', 'prueba@prueba.com', '12341234', true, 'test');");
            //    target.Query("INSERT INTO public.clientbrands(clientid, name, contact, contactemail, contactphone, sendinvoice, tenantid) VALUES ('1', 'Marca03', 'Prueba', 'prueba@prueba.com', '12341234', true, 'test');");
            //    target.Query("INSERT INTO public.clientbrands(clientid, name, contact, contactemail, contactphone, sendinvoice, tenantid) VALUES ('1', 'Marca04', 'Prueba', 'prueba@prueba.com', '12341234', true, 'test');");
            //    target.Query("INSERT INTO public.clientbrands(clientid, name, contact, contactemail, contactphone, sendinvoice, tenantid) VALUES ('1', 'Marca05', 'Prueba', 'prueba@prueba.com', '12341234', true, 'test');");
            //    target.Query("INSERT INTO public.clientbrands(clientid, name, contact, contactemail, contactphone, sendinvoice, tenantid) VALUES ('1', 'Marca06', 'Prueba', 'prueba@prueba.com', '12341234', true, 'test');");

            //    target.Query("INSERT INTO public.clientbranches(clientid, name, manager, manageremail, managerphone, shippingaddress, sendinvoice, taxpayerid, clientbrandid, tenantid) VALUES ('1', 'Suc01', 'Prueba', 'prueba@prueba.com', '12341234', 'Prueba', true, 1, 1, 'test');");
            //    target.Query("INSERT INTO public.clientbranches(clientid, name, manager, manageremail, managerphone, shippingaddress, sendinvoice, taxpayerid, clientbrandid, tenantid) VALUES ('1', 'Suc02', 'Prueba', 'prueba@prueba.com', '12341234', 'Prueba', true, 1, 2, 'test');");
            //    target.Query("INSERT INTO public.clientbranches(clientid, name, manager, manageremail, managerphone, shippingaddress, sendinvoice, taxpayerid, clientbrandid, tenantid) VALUES ('1', 'Suc03', 'Prueba', 'prueba@prueba.com', '12341234', 'Prueba', true, 1, 3, 'test');");
            //    target.Query("INSERT INTO public.clientbranches(clientid, name, manager, manageremail, managerphone, shippingaddress, sendinvoice, taxpayerid, clientbrandid, tenantid) VALUES ('1', 'Suc04', 'Prueba', 'prueba@prueba.com', '12341234', 'Prueba', true, 2, 1, 'test');");
            //    target.Query("INSERT INTO public.clientbranches(clientid, name, manager, manageremail, managerphone, shippingaddress, sendinvoice, taxpayerid, clientbrandid, tenantid) VALUES ('1', 'Suc05', 'Prueba', 'prueba@prueba.com', '12341234', 'Prueba', true, 2, 2, 'test');");
            //    target.Query("INSERT INTO public.clientbranches(clientid, name, manager, manageremail, managerphone, shippingaddress, sendinvoice, taxpayerid, clientbrandid, tenantid) VALUES ('1', 'Suc06', 'Prueba', 'prueba@prueba.com', '12341234', 'Prueba', true, 2, 3, 'test');");
            //}


            //var dynamicParameters = new DynamicParameters();
            //var existingOrderTemplateProduct = new OrderTemplateProduct() { Id = 12 };

            //var algo = existingOrderTemplateProduct.Id;
            //var query = repository.GetUpdateQuery<OrderTemplateProduct, int>("test", x => x.Id == algo, ref dynamicParameters,
            //                new UpdateInfo<OrderTemplateProduct>()
            //                {
            //                    Expression = (x => x.FixedPrice),
            //                    Value = 1234
            //                });

            //query = repository.GetUpdateQuery<OrderTemplate, int>("test", x => x.Id == algo, ref dynamicParameters,
            //        new UpdateInfo<OrderTemplate>()
            //        {
            //            Expression = (x => x.Discount), Value = 1234
            //        });


            //var t = repository.Update<OrderTemplate, int>("test", x => x.Id == 9, new UpdateInfo<OrderTemplate>() { 

            //Expression = (x => x.Name),
            //Value = "Cambiado"


            //}); 

            //var tempTask = repository.Insert<OrderTemplate, int>("test", new OrderTemplate()
            //{
            //    Name = "Plantilla de prueba 2",
            //    Products = new System.Collections.Generic.List<OrderTemplateProduct>()
            //    {
            //        new OrderTemplateProduct() { Qty = 3, ProductId = 1, Specification = "Prueba" },
            //        new OrderTemplateProduct() { Qty = 10, ProductId = 2, Specification = "Prueba 2" }
            //    }
            //});

            //tempTask.Wait();

            return;

            //var otherTask = repository.Query(async (db) => {

            //    await db.QueryAsync("INSERT INTO Units (Name, Key, UsesDecimalPlaces) VALUES ('Piezas', 'U', false)");
            //    await db.QueryAsync("INSERT INTO Units (Name, Key, UsesDecimalPlaces) VALUES ('KG', 'KG', True);");
            //    await db.QueryAsync("INSERT INTO Units (Name, Key, UsesDecimalPlaces) VALUES ('Litros', 'L', True);");

            //    return "";
            //});
            //otherTask.Wait();


        }

        private static string GetValue(BsonValue bsonValue)
        {
            if (bsonValue == null || bsonValue.BsonType == BsonType.Null)
                return "";

            return bsonValue?.AsString.Replace("'", "''");
        }

        private static string ConvertToQty(string val)
        {
            if (val == null || val == "BsonNull" || val == "") return "NULL";

            var whatever = String.Format("{0:N}", (decimal)(Convert.ToDecimal(val)) / 1000);

            return whatever.Replace(",", "");

        }

        private static int ConvertToUnit(string asString)
        {
            if (asString == "0") return 1;
            if (asString == "1") return 2;
            if (asString == "2") return 3;

            return 1;
        }

        private static string ConvertToDecimal(string val)
        {
            if (val == null || val == "BsonNull" || val == "") return "NULL";

            var whatever = String.Format("{0:N}", (decimal)(Convert.ToDecimal(val)) / 100);

            return whatever.Replace(",", "");


        }
    }
}