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

namespace MisIngredientesVue.Core.Services
{
    public interface IAnalyticsService
    {
        //Task<AnalyticsData> LoadData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId, int? productId);

        Task<AnalyticsOrdersPendingToPay> LoadOrdersPendingToPayData(string tenantId, int? clientId, int? brandId, int? branchId);
        Task<AnalyticsProductData> LoadProductData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId, int? productId);
        Task<AnalyticsMarginUtilityData> LoadMarginUtilityData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId);
        Task<AnalyticsSalesData> LoadSalesData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId);
        Task<BestSeller[]> LoadBestSellers(string tenantId, DateTime[] dateRange);
    }

    public class AnalyticsService : IAnalyticsService
    {
        private readonly IRepository _repository;

        public AnalyticsService(IRepository repository)
        {
            _repository = repository;
        }

        public async Task<AnalyticsOrdersPendingToPay> LoadOrdersPendingToPayData(string tenantId, int? clientId, int? brandId, int? branchId)
        {
            return await _repository.Query(async (db) =>
            {    
                var ordersPendingToPay = await GetOrdersPendingToPay(db, tenantId, clientId, brandId, branchId);

                return new AnalyticsOrdersPendingToPay()
                {
                    OrdersPendingToPay = ordersPendingToPay,
                    OrdersPendingToPayChartData = GetOrdersPendingToPayChartData(ordersPendingToPay),

                    OrdersPendingToPayTotal = ordersPendingToPay.Sum(x => x.Total)
                };
            });
        }

        public async Task<AnalyticsProductData> LoadProductData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId, int? productId)
        {
            var fromDate = dateRange != null && dateRange.Length > 0 ? (DateTime?)dateRange[0] : null;
            var toDate = dateRange != null && dateRange.Length > 1 ? (DateTime?)dateRange[1] : null;

            if (fromDate.HasValue && !toDate.HasValue)
                toDate = fromDate;

            var hasDateRange = fromDate.HasValue && toDate.HasValue;

            return await _repository.Query(async (db) =>
            {
 
                            ////////////////////VENTAS DE PRODUCTO

                            var productSales = productId != null ? (await db.QueryAsync<ProductSale>($@"
            SELECT orders.deliverydate as deliverydate, 
            clients.name as client, 
            clientbrands.name as brand, 
            clientbranches.name as branch,
            units.name as unit,
            SUM(orderproducts.qty * (CASE WHEN orderproducts.fixedprice IS NULL THEN orderproducts.saleprice ELSE orderproducts.fixedprice END)) as totalsales,
            SUM(orderproducts.qty * (CASE WHEN orderproducts.suppliercost IS NULL THEN products.suppliercost ELSE orderproducts.suppliercost END)) as totalcost,
            AVG((CASE WHEN orderproducts.suppliercost IS NULL THEN products.suppliercost ELSE orderproducts.suppliercost END)) as cost,
            SUM(orderproducts.qty) as count
            FROM orderproducts
            LEFT JOIN products ON products.id = orderproducts.productId
            LEFT JOIN units ON units.id = products.unitid
            LEFT JOIN orders ON orders.id = orderproducts.orderid
            LEFT JOIN clients ON clients.id = orders.clientid
            LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
            LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
            WHERE orders.tenantId = @tenantId 
            AND orders.canceledbyid IS NULL
            AND orderproducts.tenantId = @tenantId
            AND clients.tenantId = @tenantId 
            AND clientbrands.tenantId = @tenantId 
            AND clientbranches.tenantId = @tenantId
            AND orders.deliveryStatus = 2
            {(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
            {(clientId != null ? "AND clients.id = @clientId" : "")}
            {(brandId != null ? "AND clientbrands.id = @brandId" : "")}
            {(branchId != null ? "AND clientbranches.id = @branchId" : "")}
            {(productId != null ? "AND orderproducts.productId = @productId" : "")}
            GROUP BY orders.deliverydate, clients.name, clientbrands.name, clientbranches.name, units.name
            ORDER BY orders.deliverydate
            ", new { tenantId, clientId, brandId, branchId, productId })).ToArray() : new ProductSale[] { };

                            ////////////////////COSTO DEL PRODUCTO

                            var productCost = productId != null ? (await db.QueryAsync<ProductCost>($@"
            SELECT orders.deliverydate as deliverydate, 
            AVG(orderproducts.suppliercost) as cost
            FROM orderproducts
            LEFT JOIN orders ON orders.id = orderproducts.orderid
            WHERE orders.tenantId = @tenantId 
            AND orders.canceledbyid IS NULL
            AND orderproducts.tenantId = @tenantId
            AND orders.deliveryStatus = 2
            {(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
            {(productId != null ? "AND orderproducts.productId = @productId" : "")}
            GROUP BY orders.deliverydate
            ORDER BY orders.deliverydate
            ", new { tenantId, clientId, brandId, branchId, productId })).ToArray() : new ProductCost[] { };

                var dateRange = hasDateRange ? GetDateRanges(fromDate.Value, toDate.Value) : new DateRange[] { };

                return new AnalyticsProductData()
                {
                    ProductSales = productSales,
                    ProductSalesChartData = productId != null ? new ChartData()
                    {
                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
                        Items = new ChartSeries[] {
                                        GetProductSalesChartData_Count("Cantidad Vendidos", productSales, dateRange, "line"),
                                        GetProductSalesChartData_Total("Total Ventas ($)", productSales, dateRange),
                        }
                    } : null,
                    ProductCostChartData = productId != null ? new ChartData()
                    {
                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
                        Items = new ChartSeries[] {
                                        GetProductCostChartData_Average("Costo Unitario", productCost, dateRange, "line"),
                        }
                    } : null
                };
            });
        }

        public async Task<AnalyticsMarginUtilityData> LoadMarginUtilityData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId)
        {
            var fromDate = dateRange != null && dateRange.Length > 0 ? (DateTime?)dateRange[0] : null;
            var toDate = dateRange != null && dateRange.Length > 1 ? (DateTime?)dateRange[1] : null;

            if (fromDate.HasValue && !toDate.HasValue)
                toDate = fromDate;

            var hasDateRange = fromDate.HasValue && toDate.HasValue;

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

                            var marginUtilityData = hasDateRange ? (await db.QueryAsync<MarginUtility>($@"
            SELECT orders.id, orders.deliverydate as deliverydate, 
            clients.name as client, 
            clientbrands.name as brand, 
            clientbranches.name as branch,
            (SELECT SUM((orderproducts.saleprice 
            			+ (orderproducts.saleprice * ((CASE WHEN orderproducts.iva IS NULL THEN 0 ELSE orderproducts.iva END) / 100))
            			+ (orderproducts.saleprice * ((CASE WHEN orderproducts.ieps IS NULL THEN 0 ELSE orderproducts.ieps END) / 100))
            		   ) * orderproducts.qty) 
            		   FROM orderproducts where orderid = orders.id) as totalsales,
            (SELECT SUM(orderproducts.suppliercost * orderproducts.qty) FROM orderproducts where orderid = orders.id) as totalcost
            FROM orders
            LEFT JOIN clients ON clients.id = orders.clientid
            LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
            LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
            WHERE orders.deliveryStatus = 2
            AND orders.canceledbyid IS NULL
            AND orders.tenantId = @tenantId 
            AND clients.tenantId = @tenantId 
            AND clientbrands.tenantId = @tenantId 
            AND clientbranches.tenantId = @tenantId
            AND orders.deliveryStatus = 2
            {(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
            {(clientId != null ? "AND clients.id = @clientId" : "")}
            {(brandId != null ? "AND clientbrands.id = @brandId" : "")}
            {(branchId != null ? "AND clientbranches.id = @branchId" : "")}
            ORDER BY deliverydate
            ", new { tenantId, clientId, brandId, branchId })).ToArray() : new MarginUtility[] { };

                var dateRange = hasDateRange ? GetDateRanges(fromDate.Value, toDate.Value) : new DateRange[] { };

                return new AnalyticsMarginUtilityData()
                {
                    MarginUtilityData = marginUtilityData,
                    MarginUtilityChartData = marginUtilityData.Length > 0 ? new ChartData()
                    {
                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
                        Items = new ChartSeries[] {
                                        GetMarginUtilityChartData_TotalSales("Venta Total ($)", marginUtilityData, dateRange),
                                        GetMarginUtilityChartData_TotalCost("Costo Total ($)", marginUtilityData, dateRange),
                        }
                    } : null
                };
            });

        }

        public async Task<AnalyticsSalesData> LoadSalesData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId)
        {
            var fromDate = dateRange != null && dateRange.Length > 0 ? (DateTime?)dateRange[0] : null;
            var toDate = dateRange != null && dateRange.Length > 1 ? (DateTime?)dateRange[1] : null;

            if (fromDate.HasValue && !toDate.HasValue)
                toDate = fromDate;

            var hasDateRange = fromDate.HasValue && toDate.HasValue;

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

                var salesData = hasDateRange ? (await db.QueryAsync<Sales>($@"
            SELECT orders.deliverydate as deliverydate, 
            clients.name as client, 
            clientbrands.name as brand, 
            clientbranches.name as branch,
            sum(orders.total) as total,
            count(orders) as count
            FROM orders
            LEFT JOIN clients ON clients.id = orders.clientid
            LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
            LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
            WHERE orders.tenantId = @tenantId 
            AND clients.tenantId = @tenantId 
            AND clientbrands.tenantId = @tenantId 
            AND clientbranches.tenantId = @tenantId
            AND orders.deliveryStatus = 2
            AND orders.canceledbyid IS NULL
            {(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
            {(clientId != null ? "AND clients.id = @clientId" : "")}
            {(brandId != null ? "AND clientbrands.id = @brandId" : "")}
            {(branchId != null ? "AND clientbranches.id = @branchId" : "")}
            GROUP BY orders.deliverydate, clients.name, clientbrands.name, clientbranches.name
            ORDER BY orders.deliverydate
            ", new { tenantId, clientId, brandId, branchId })).ToArray() : new Sales[] { };

                            
                var dateRange = hasDateRange ? GetDateRanges(fromDate.Value, toDate.Value) : new DateRange[] { };

                return new AnalyticsSalesData()
                {
                    SalesData = salesData,

                    SalesChartData = salesData.Length > 0 ? new ChartData()
                    {
                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
                        Items = new ChartSeries[] {
                                        GetSalesChartData_Count("Número de pedidos", salesData, dateRange, "line"),
                                        GetSalesChartData_Total("Venta Total ($)", salesData, dateRange),

                        }
                    } : null
                };
            });

        }

        public async Task<BestSeller[]> LoadBestSellers(string tenantId, DateTime[] dateRange)
        {
            var fromDate = dateRange != null && dateRange.Length > 0 ? (DateTime?)dateRange[0] : null;
            var toDate = dateRange != null && dateRange.Length > 1 ? (DateTime?)dateRange[1] : null;

            if (fromDate.HasValue && !toDate.HasValue)
                toDate = fromDate;

            var hasDateRange = fromDate.HasValue && toDate.HasValue;

            return await _repository.Query(async (db) =>
            {
                var query = $@"SELECT productId,
            products.name as product,
            SUM(orderproducts.qty) as qty,
            units.name as unit,
            SUM(orderproducts.qty * (CASE WHEN orderproducts.fixedprice IS NULL THEN orderproducts.saleprice ELSE orderproducts.fixedprice END)) as total
            FROM orderproducts
            LEFT JOIN orders ON orders.id = orderproducts.orderid
            LEFT JOIN products ON products.id = productId
            LEFT JOIN units ON products.unitId = units.id
            WHERE orders.deliveryStatus = 2
            AND orders.canceledbyid IS NULL
            {(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
            AND products.tenantId = @tenantId 
            AND orderproducts.tenantId = @tenantId
            GROUP BY productid, products.name,units.name
            ORDER BY qty DESC";

                var bestSellers = (await db.QueryAsync<BestSeller>(query, new { tenantId })).ToArray();

                return bestSellers;
            });

        }

        private string[] GetChartLabels(DateRange[] dateRange)
        {
            return dateRange
                .Select(x => x.Name).ToArray();

            //return dateRange
            //    .Select(x => x.To.HasValue ? $"{x.From.ToString("dd/MM")}-{x.To.Value.ToString("dd/MM")}" 
            //        : $"{x.From.ToString("dd/MM")}")
            //    .ToArray();
        }

        private DateRange[] GetDateRanges(DateTime from, DateTime to)
        {
            var monthNames = new string[] { "", "ene", "feb", "mar", "abr", "may", "jun", "jul", "ago", "sep", "oct", "nov", "dic" };

            if (from > to) throw new ArgumentException();

            if (from == to) return new DateRange[] {
                new DateRange() { Name = from.ToString("dd/") + monthNames[from.Month], From = from, To = null }
            };

            var moreThanOneMonth = false;

            var currentDate = from;
            var lastDate = currentDate;

            if (to - from < TimeSpan.FromDays(31))
            {
                moreThanOneMonth = false;
            }
            else
            {
                while (currentDate <= to)
                {
                    if (currentDate.Month != lastDate.Month)
                    {
                        moreThanOneMonth = true;
                        break;
                    }

                    lastDate = currentDate;
                    currentDate = currentDate.AddDays(1);
                }
            }

            var list = new List<DateRange>();

            if (!moreThanOneMonth)
            {
                currentDate = from;
                while (currentDate <= to)
                {
                    list.Add(new DateRange() { Name = currentDate.ToString("dd/") + monthNames[currentDate.Month], From = currentDate, To = null });
                    currentDate = currentDate.AddDays(1);
                }
            }
            else
            {
                lastDate = from;
                currentDate = from;
                var currentMonth = from;

                while (currentDate <= to)
                {
                    if (currentDate.Month != lastDate.Month)
                    {
                        list.Add(new DateRange()
                        {
                            Name = currentMonth.ToString("yyyy/") + monthNames[currentMonth.Month],
                            From = currentMonth,
                            To = currentDate.AddDays(-1)
                        });

                        currentMonth = currentDate;
                    }

                    lastDate = currentDate;
                    currentDate = currentDate.AddDays(1);

                }

                list.Add(new DateRange()
                {
                    Name = currentMonth.ToString("yyyy/") + monthNames[currentMonth.Month],
                    From = currentMonth,
                    To = currentDate.AddDays(-1)
                });
            }

            return list.ToArray();
        }

        private ChartSeries GetSalesChartData_Total(string label, Sales[] data, DateRange[] dateRange, string type = "bar")
        {
            return new ChartSeries()
            {
                Label = label,
                Type = type,
                Data = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x => data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From))
                    .Sum(y => y.Total)).ToArray() : (new decimal[] { })
            };
        }

        private ChartSeries GetSalesChartData_Count(string label, Sales[] data, DateRange[] dateRange, string type = "bar")
        {
            return new ChartSeries()
            {
                Label = label,
                Type = type,
                Data = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x => data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From))
                    .Sum(y => y.Count)).ToArray() : (new int[] { })
            };
        }

        private ChartSeries GetProductSalesChartData_Count(string label, ProductSale[] data, DateRange[] dateRange, string type = "bar")
        {
            return new ChartSeries()
            {
                Type = type,
                Label = label,
                Data = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x => data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From))
                    .Sum(y => y.Count)).ToArray() : (new decimal[] { })
            };
        }

        private ChartSeries GetProductCostChartData_Average(string label, ProductCost[] data, DateRange[] dateRange, string type = "bar")
        {
            var dataItems = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x =>
                    {
                        var values = data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From));
                        if (values.Count() > 0) return values.Average(y => y.Cost);
                        return 0;
                    }).ToArray() : (new decimal[] { });

            var firstNonZeroValue = 0m;

            for(var i=0; i < dataItems.Length; i++)
            {
                if(dataItems[i] == 0)
                {
                    if (i > 0)
                    {
                        dataItems[i] = dataItems[i - 1];
                    }
                }
                else
                {
                    if (firstNonZeroValue == 0)
                        firstNonZeroValue = dataItems[i];
                }
            }

            if(dataItems.Length > 1 && dataItems[0] == 0)
            {
                for (var i = 0; i < dataItems.Length; i++)
                {
                    if (dataItems[i] == 0)
                    {
                        dataItems[i] = firstNonZeroValue;
                    }
                    else
                        break;
                }
            }

            return new ChartSeries()
            {
                Type = type,
                Label = label,
                Data = dataItems
            };

        }

        private ChartSeries GetProductSalesChartData_Total(string label, ProductSale[] data, DateRange[] dateRange, string type = "bar")
        {
            return new ChartSeries()
            {
                Type = type,
                Label = label,
                Data = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x => data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From))
                    .Sum(y => y.TotalSales)).ToArray() : (new decimal[] { })
            };
        }

        private ChartSeries GetMarginUtilityChartData_TotalCost(string label, MarginUtility[] data, DateRange[] dateRange, string type = "bar")
        {
            return new ChartSeries()
            {
                Type = type,
                Label = label,
                Data = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x => data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From))
                    .Sum(y => y.TotalCost)).ToArray() : (new decimal[] { })
            };
        }

        private ChartSeries GetMarginUtilityChartData_TotalSales(string label, MarginUtility[] data, DateRange[] dateRange, string type = "bar")
        {
            return new ChartSeries()
            {
                Type = type,
                Label = label,
                Data = data.Length > 0 && dateRange.Length > 0 ? dateRange
                    .Select(x => data.Where(y => x.To.HasValue ? (y.DeliveryDate >= x.From && y.DeliveryDate <= x.To) : (y.DeliveryDate == x.From))
                    .Sum(y => y.TotalSales)).ToArray() : (new decimal[] { })
            };
        }



//        public async Task<AnalyticsData> LoadData(string tenantId, DateTime[] dateRange, int? clientId, int? brandId, int? branchId, int? productId)
//        {
//            var fromDate = dateRange != null && dateRange.Length > 0 ? (DateTime?)dateRange[0] : null;
//            var toDate = dateRange != null && dateRange.Length > 1 ? (DateTime?)dateRange[1] : null;

//            if (fromDate.HasValue && !toDate.HasValue)
//                toDate = fromDate;

//            var hasDateRange = fromDate.HasValue && toDate.HasValue;

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

//                var salesData = hasDateRange ? (await db.QueryAsync<Sales>($@"
//SELECT orders.deliverydate as deliverydate, 
//clients.name as client, 
//clientbrands.name as brand, 
//clientbranches.name as branch,
//sum(orders.total) as total,
//count(orders) as count
//FROM orders
//LEFT JOIN clients ON clients.id = orders.clientid
//LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
//LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
//WHERE orders.tenantId = @tenantId 
//AND clients.tenantId = @tenantId 
//AND clientbrands.tenantId = @tenantId 
//AND clientbranches.tenantId = @tenantId
//AND orders.deliveryStatus = 2
//AND orders.canceledbyid IS NULL
//{(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
//{(clientId != null ? "AND clients.id = @clientId" : "")}
//{(brandId != null ? "AND clientbrands.id = @brandId" : "")}
//{(branchId != null ? "AND clientbranches.id = @branchId" : "")}
//GROUP BY orders.deliverydate, clients.name, clientbrands.name, clientbranches.name
//ORDER BY orders.deliverydate
//", new { tenantId, clientId, brandId, branchId, productId })).ToArray() : new Sales[] { };

//                ////////////////////MARGEN/UTILIDAD

//                var marginUtilityData = hasDateRange ? (await db.QueryAsync<MarginUtility>($@"
//SELECT orders.id, orders.deliverydate as deliverydate, 
//clients.name as client, 
//clientbrands.name as brand, 
//clientbranches.name as branch,
//(SELECT SUM((orderproducts.saleprice 
//			+ (orderproducts.saleprice * ((CASE WHEN orderproducts.iva IS NULL THEN 0 ELSE orderproducts.iva END) / 100))
//			+ (orderproducts.saleprice * ((CASE WHEN orderproducts.ieps IS NULL THEN 0 ELSE orderproducts.ieps END) / 100))
//		   ) * orderproducts.qty) 
//		   FROM orderproducts where orderid = orders.id) as totalsales,
//(SELECT SUM(orderproducts.suppliercost * orderproducts.qty) FROM orderproducts where orderid = orders.id) as totalcost
//FROM orders
//LEFT JOIN clients ON clients.id = orders.clientid
//LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
//LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
//WHERE orders.deliveryStatus = 2
//AND orders.canceledbyid IS NULL
//AND orders.tenantId = @tenantId 
//AND clients.tenantId = @tenantId 
//AND clientbrands.tenantId = @tenantId 
//AND clientbranches.tenantId = @tenantId
//AND orders.deliveryStatus = 2
//{(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
//{(clientId != null ? "AND clients.id = @clientId" : "")}
//{(brandId != null ? "AND clientbrands.id = @brandId" : "")}
//{(branchId != null ? "AND clientbranches.id = @branchId" : "")}
//ORDER BY deliverydate
//", new { tenantId, clientId, brandId, branchId, productId })).ToArray() : new MarginUtility[] { };

//                var index = 1; foreach (var item in marginUtilityData) { item.Utility = item.TotalSales - item.TotalCost ; index++; }

//                ////////////////////VENTAS DE PRODUCTO

//                var productSales = productId != null ? (await db.QueryAsync<ProductSale>($@"
//SELECT orders.deliverydate as deliverydate, 
//clients.name as client, 
//clientbrands.name as brand, 
//clientbranches.name as branch,
//units.name as unit,
//SUM(orderproducts.qty * (CASE WHEN orderproducts.fixedprice IS NULL THEN orderproducts.saleprice ELSE orderproducts.fixedprice END)) as totalsales,
//SUM(orderproducts.qty * (CASE WHEN orderproducts.suppliercost IS NULL THEN products.suppliercost ELSE orderproducts.suppliercost END)) as totalcost,
//AVG((CASE WHEN orderproducts.suppliercost IS NULL THEN products.suppliercost ELSE orderproducts.suppliercost END)) as cost,
//SUM(orderproducts.qty) as count
//FROM orderproducts
//LEFT JOIN products ON products.id = orderproducts.productId
//LEFT JOIN units ON units.id = products.unitid
//LEFT JOIN orders ON orders.id = orderproducts.orderid
//LEFT JOIN clients ON clients.id = orders.clientid
//LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
//LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
//WHERE orders.tenantId = @tenantId 
//AND orders.canceledbyid IS NULL
//AND orderproducts.tenantId = @tenantId
//AND clients.tenantId = @tenantId 
//AND clientbrands.tenantId = @tenantId 
//AND clientbranches.tenantId = @tenantId
//AND orders.deliveryStatus = 2
//{(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
//{(clientId != null ? "AND clients.id = @clientId" : "")}
//{(brandId != null ? "AND clientbrands.id = @brandId" : "")}
//{(branchId != null ? "AND clientbranches.id = @branchId" : "")}
//{(productId != null ? "AND orderproducts.productId = @productId" : "")}
//GROUP BY orders.deliverydate, clients.name, clientbrands.name, clientbranches.name, units.name
//ORDER BY orders.deliverydate
//", new { tenantId, clientId, brandId, branchId, productId })).ToArray() : new ProductSale[] { };

//                ////////////////////COSTO DEL PRODUCTO
             
//                var productCost = productId != null ? (await db.QueryAsync<ProductCost>($@"
//SELECT orders.deliverydate as deliverydate, 
//AVG(orderproducts.suppliercost) as cost
//FROM orderproducts
//LEFT JOIN orders ON orders.id = orderproducts.orderid
//WHERE orders.tenantId = @tenantId 
//AND orders.canceledbyid IS NULL
//AND orderproducts.tenantId = @tenantId
//AND orders.deliveryStatus = 2
//{(hasDateRange ? $"AND orders.deliverydate >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orders.deliverydate <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
//{(productId != null ? "AND orderproducts.productId = @productId" : "")}
//GROUP BY orders.deliverydate
//ORDER BY orders.deliverydate
//", new { tenantId, clientId, brandId, branchId, productId })).ToArray() : new ProductCost[] { };

//                ////////////////////////////////////////////////////////////////////////////////

//                var bestSellers = (await db.QueryAsync<BestSeller>($@"SELECT productId,
//products.name as product,
//SUM(orderproducts.qty) as qty,
//units.name as unit,
//SUM(orderproducts.qty * (CASE WHEN orderproducts.fixedprice IS NULL THEN orderproducts.saleprice ELSE orderproducts.fixedprice END)) as total
//FROM orderproducts
//LEFT JOIN orders ON orders.id = orderproducts.orderid
//LEFT JOIN products ON products.id = productId
//LEFT JOIN units ON products.unitId = units.id
//WHERE orders.deliveryStatus = 2
//AND orders.canceledbyid IS NULL
//{(hasDateRange ? $"AND orderproducts.created >= '{fromDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}' AND orderproducts.created <= '{toDate.Value.ToString("yyyy-MM-ddTHH:mm:ss")}'" : "")} 
//AND products.tenantId = @tenantId 
//AND orderproducts.tenantId = @tenantId
//GROUP BY productid, products.name,units.name
//ORDER BY qty DESC", new { tenantId })).ToArray();

//                var ordersPendingToPay = await GetOrdersPendingToPay(db, tenantId, clientId, brandId, branchId);

//                var dateRange = hasDateRange ? GetDateRanges(fromDate.Value, toDate.Value) : new DateRange[] { };

//                return new AnalyticsData()
//                {
//                    SalesData = salesData,
                    
//                    SalesChartData = salesData.Length > 0 ? new ChartData() { 
//                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { }, 
//                        Items = new ChartSeries[] {
//                            GetSalesChartData_Count("Número de pedidos", salesData, dateRange, "line"),
//                            GetSalesChartData_Total("Venta Total ($)", salesData, dateRange),
                            
//                        }
//                    } : null,
//                    MarginUtilityData = marginUtilityData,
//                    MarginUtilityChartData = marginUtilityData.Length > 0 ? new ChartData()
//                    {
//                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
//                        Items = new ChartSeries[] {
//                            GetMarginUtilityChartData_TotalSales("Venta Total ($)", marginUtilityData, dateRange),
//                            GetMarginUtilityChartData_TotalCost("Costo Total ($)", marginUtilityData, dateRange),
//                        }
//                    } : null,
//                    ProductSales = productSales,
//                    ProductSalesChartData = productId != null ? new ChartData()
//                    {
//                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
//                        Items = new ChartSeries[] {
//                            GetProductSalesChartData_Count("Cantidad Vendidos", productSales, dateRange, "line"),
//                            GetProductSalesChartData_Total("Total Ventas ($)", productSales, dateRange),
//                        }
//                    } : null,
//                    ProductCostChartData = productId != null ? new ChartData()
//                    {
//                        Labels = dateRange.Length > 0 ? GetChartLabels(dateRange) : new string[] { },
//                        Items = new ChartSeries[] {
//                            GetProductCostChartData_Average("Costo Unitario", productCost, dateRange, "line"),
//                        }
//                    } : null,

//                    BestSellers = bestSellers,
//                    OrdersPendingToPay = ordersPendingToPay,
//                    OrdersPendingToPayChartData = GetOrdersPendingToPayChartData(ordersPendingToPay),

//                    OrdersPendingToPayTotal = ordersPendingToPay.Sum(x => x.Total)
//                };
//            });

//        }

        private ChartData GetOrdersPendingToPayChartData(OrderPendingToPay[] ordersPendingToPay)
        {
            if (ordersPendingToPay.Length <= 10)
            {
                return new ChartData()
                {
                    Labels = ordersPendingToPay.Select(x => x.Name).ToArray(),
                    Items = new ChartSeries[]
                    {
                        new ChartSeries() {
                            Type = "pie",
                            Label = "Cuentas por cobrar",
                            Data = ordersPendingToPay.Select(x => x.Total).ToArray()
                        }
                    }
                };
            }
            else
            {
                return new ChartData() 
                {
                    Labels = ordersPendingToPay.Take(5).Select(x => x.Name)
                        .ToArray().Append("Otros").ToArray(),
                    Items = new ChartSeries[]
                    {
                        new ChartSeries() {
                            Type = "pie",
                            Label = "Cuentas por cobrar",
                            Data = ordersPendingToPay.Take(5).Select(x => x.Total)
                                .ToArray().Append(ordersPendingToPay.Skip(5).ToArray().Sum(x => x.Total)).ToArray()
                        }
                    }
                };
            }
        }

        private async Task<OrderPendingToPay[]> GetOrdersPendingToPay(IDbConnection db, string tenantId, int? clientId, int? brandId, int? branchId)
        {
            if (!clientId.HasValue && !brandId.HasValue && !branchId.HasValue) //Totales agrupados por cliente
            {
                var query = @"SELECT clients.name as name, 
SUM(
	COALESCE(
		(SELECT (orderpayments.lefttopay - orderpayments.total) as total FROM orderpayments WHERE orderid = orders.id AND isactive = true ORDER BY id DESC LIMIT 1)
	, orders.total)   
) as total 
FROM orders 
LEFT JOIN clients ON clients.id = orders.clientId
WHERE orders.paymentstatus != 1 AND orders.deliverystatus = 2
AND orders.canceledbyid IS NULL
AND orders.tenantId = @tenantId 
GROUP BY clients.name
ORDER BY total DESC";

                return (await db.QueryAsync<OrderPendingToPay>(query, new { tenantId })).ToArray();
            }

            if (clientId.HasValue && !brandId.HasValue && !branchId.HasValue) //Totales agrupados por marca
            {
                var query = @"SELECT clientbrands.name, 
COALESCE((SELECT SUM (orderpayments.lefttopay - orderpayments.total) as total FROM orderpayments
		 INNER JOIN orders ON orderpayments.orderid = orders.id
		 INNER JOIN clientbranches ON clientbranches.id = orders.branchid
		 WHERE clientbranches.clientbrandid = clientbrands.id AND orders.tenantId = @tenantId AND orders.canceledbyid IS NULL 
		  AND orders.deliverystatus = 2 AND orders.paymentstatus != 1 AND orderpayments.isactive = true), 0.00) as total
FROM clientbrands
WHERE clientid = @clientId";

                return (await db.QueryAsync<OrderPendingToPay>(query, new { tenantId, clientId })).ToArray();
            }

            if (clientId.HasValue && brandId.HasValue && !branchId.HasValue) //Totales agrupados por sucursal
            {
                var query = @"SELECT clientbranches.name as name, 
SUM(
	COALESCE(
		(SELECT (orderpayments.lefttopay - orderpayments.total) as total FROM orderpayments WHERE orderid = orders.id AND isactive = true ORDER BY id DESC LIMIT 1)
	, orders.total)   
) as total FROM orders
LEFT JOIN clientbranches ON clientbranches.id = orders.branchid
LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid
WHERE orders.paymentstatus != 1 AND orders.deliverystatus = 2
AND orders.canceledbyid IS NULL
AND orders.clientid = @clientId 
AND clientbrands.id = @brandId
AND orders.tenantId = @tenantId 
GROUP BY clientbranches.name
ORDER BY total DESC";

                return (await db.QueryAsync<OrderPendingToPay>(query, new { tenantId, clientId, brandId })).ToArray();
            }

            if (branchId.HasValue) //Pedidos específicos de la sucursal solicitada
            {
                var query = @"SELECT orders.id as name, 
	COALESCE(
		(SELECT (orderpayments.lefttopay - orderpayments.total) as total FROM orderpayments WHERE orderid = orders.id AND isactive = true ORDER BY id DESC LIMIT 1)
	, orders.total)   
as total FROM orders
WHERE orders.paymentstatus != 1 AND orders.deliverystatus = 2
AND orders.canceledbyid IS NULL
AND orders.branchid = @branchId
AND orders.tenantId = @tenantId 
ORDER BY total DESC";

                return (await db.QueryAsync<OrderPendingToPay>(query, new { tenantId, branchId })).ToArray();

            }

            return new OrderPendingToPay[] { };
        }

    }

    public class AnalyticsSalesData
    {
        public Sales[] SalesData { get; set; }
        public ChartData SalesChartData { get; set; }
    }

    public class AnalyticsMarginUtilityData
    {
        public MarginUtility[] MarginUtilityData { get; set; }
        public ChartData MarginUtilityChartData { get; set; }
    }

    public class AnalyticsProductData
    {
        public ProductSale[] ProductSales { get; set; }
        public ChartData ProductSalesChartData { get; set; }
        public ChartData ProductCostChartData { get; set; }
    }

    public class AnalyticsOrdersPendingToPay
    {
        public OrderPendingToPay[] OrdersPendingToPay { get; set; }
        public ChartData OrdersPendingToPayChartData { get; set; }
        public decimal OrdersPendingToPayTotal { get; set; }
    }

    //public class AnalyticsData
    //{
    //    public Sales[] SalesData { get; set; }
    //    public ChartData SalesChartData { get; set; }
    //    public MarginUtility[] MarginUtilityData { get; set; }
    //    public ChartData MarginUtilityChartData { get; set; }
    //    public ProductSale[] ProductSales { get; set; }
    //    public ChartData ProductSalesChartData { get; set; }
    //    public ChartData ProductCostChartData { get; set; }
    //    public OrderPendingToPay[] OrdersPendingToPay { get; set; }
    //    public ChartData OrdersPendingToPayChartData { get; set; }
    //    public decimal OrdersPendingToPayTotal { get; set; }
    //    public BestSeller[] BestSellers { get; set; }
    //}

    public class DateRange
    {
        public string Name { get; set; }
        public DateTime From { get; set; }
        public DateTime? To { get; set; }
    }

    public class ChartData
    {
        public ChartData()
        {
            Labels = new string[] { };
            Items = new ChartSeries[] { };
        }

        public string[] Labels { get; set; }
        public ChartSeries[] Items { get; set; }
    }

    public class Sales
    {
        public DateTime DeliveryDate { get; set; }
        public string Client { get; set; }
        public string Brand { get; set; }
        public string Branch { get; set; }
        public decimal Total { get; set; }
        public int Count { get; set; }
    }

    public class MarginUtility
    {
        public DateTime DeliveryDate { get; set; }
        public string Client { get; set; }
        public string Brand { get; set; }
        public string Branch { get; set; }
        public decimal TotalSales { get; set; }
        public decimal TotalCost { get; set; }
        public decimal Utility { get; set; }

    }

    public class ProductCost
    {
        public DateTime DeliveryDate { get; set; }
        public decimal Cost { get; set; }
    }

    public class ProductSale
    {
        public DateTime DeliveryDate { get; set; }
        public string Client { get; set; }
        public string Brand { get; set; }
        public string Branch { get; set; }
        public string Unit { get; set; }
        public decimal TotalSales { get; set; }
        public decimal TotalCost { get; set; }
        public decimal Cost { get; set; }
        public decimal Count { get; set; }
    }

    public class ChartSeries
    {
        public string Type { get; set; } //bar, line, pie
        public string Label { get; set; }
        public object Data { get; set; }
    }

    public class OrderPendingToPay
    {
        public string Name { get; set; }
        public decimal Total { get; set; }
    }

    public class BestSeller
    {
        public int ProductId { get; set; }
        public string Product { get; set; }
        public string Unit { get; set; }
        public decimal Total { get; set; }
        public decimal Qty { get; set; }

    }
}