using AutoTurnOver.Models;
using AutoTurnOver.Models.Report;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AutoTurnOver.Utility;
using Dapper;

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 投资回报分析
    /// </summary>
    public class report_invest_return_dao : connectionHelper
    {
        public static List<dc_report_invest_return_analysis> AnaList(Condition_ConfigPromotion m, int offset, int limit, ref int total)
        {
            var list = new List<dc_report_invest_return_analysis>();

            try
            {
                var sql = @"select t1.* from dc_report_invest_return_analysis as t1
 where 1 = 1 ";

                

                total = _connection.QuerySingleOrDefault<int>("select count(0) from (" + sql + ") tb1");

                var obj = _connection.Query<dc_report_invest_return_analysis>(sql + " limit " + offset + "," + limit);

                return obj.AsList();

            }
            catch (Exception)
            {
                return list;
            }
        }

        /// <summary>
        /// 同步pps刊登过的sku
        /// </summary>
        public static void SynchPpsSku()
        {
            var skus = ApiUtility.GetPPSSkus();
            foreach (var item in skus)
            {
                var dbSku = new dc_report_invest_return_sku
                {
                    create_user = "pps",
                    gmt_create_time = DateTime.Now,
                    gmt_update_time = DateTime.Now,
                    project = "DE",
                    sku = item.skuCode,
                    sku_name = "",
                    id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_invest_return_sku where sku=@sku ", new { sku = item.skuCode }) ?? 0
                };
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                }
            }
        }
        public static void SynchBtmAdFees()
        {
            var fees = ApiUtility.GetBtmAdFees(DateTime.Now.AddDays(-60), DateTime.Now);
            foreach (var item in fees)
            {
                var meidongDate = DateTime.Parse(item.date_str);
                var dbSku = new t_base_shopify_adfee
                {
                    data_id = item.data_id,
                    account_id = item.account_id,
                    date_str = item.date_str,
                    fee = item.fee,
                    fee_cny = item.fee_cny,
                    bdate = meidongDate.GetMonthFirstDay().ToDayHome().AMESTimeToBeijinTime(),
                    edate = meidongDate.LastDayOfMonth().ToDayEnd().AMESTimeToBeijinTime(),
                    gmt_create_time = DateTime.Now,
                    share_time = null,
                    id = _connection.QueryFirstOrDefault<int?>(" select id from t_base_shopify_adfee where data_id=@data_id ", new { data_id = item.data_id }) ?? 0
                };
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                }
                else
                {
                    _connection.Update(dbSku);
                }
            }
        }
        public static void SynchBtmOrderRefund()
        {
            var fees = ApiUtility.GetBtmOrderRefunds(DateTime.Now.AddDays(-60), DateTime.Now);
            foreach (var item in fees)
            {
                var dbSku = new t_base_shopify_refund
                {
                    bailun_order_id = item.bailun_order_id,
                    warehouse_code = item.warehouse_code,
                    bailun_sku = item.bailun_sku,
                    order_pay_time = item.order_pay_time,
                    origin_order_id = item.origin_order_id,
                    refund_amount = item.refund_amount,
                    id = _connection.QueryFirstOrDefault<int?>(" select id from t_base_shopify_refund where bailun_order_id=@bailun_order_id and bailun_sku=@bailun_sku ", new { bailun_order_id = item.bailun_order_id, bailun_sku = item.bailun_sku }) ?? 0
                };
                if (dbSku.id <= 0)
                {
                    _connection.Insert(dbSku);
                }
                else
                {
                    _connection.Update(dbSku);
                }
            }
        }

        public static void CalculationStockScore(string sku=null)
        {
            var skus = _connection.Query<dc_report_invest_return_sku>(" select * from dc_report_invest_return_sku  ").ToList() ;
            if (!string.IsNullOrWhiteSpace(sku))
            {
                skus = skus.Where(s => s.sku == sku).ToList();
            }
            foreach (var skuItem in skus)
            {
                CalculationStockScoreSingle(skuItem);
            }
        }
        /// <summary>
        /// 计算仓库分数
        /// </summary>
        /// <param name="sku"></param>
        public static void CalculationStockScoreSingle(dc_report_invest_return_sku sku)
        {
            var day30Btime = DateTime.Now.AddDays(-31).ToDayHome();
            var day7Btime = DateTime.Now.AddDays(-31).ToDayHome();
            var dayEtime = DateTime.Now.AddDays(-1).ToDayEnd();
            // 查询这个sku涉及的所有仓库
            var stocks = _connection.Query<dc_base_stock>(" select * from dc_base_stock where bailun_sku=@sku ", new { sku = sku.sku });
            // 查询sku 基础表
            var baseSku = _connection.QueryFirstOrDefault<dc_base_sku>(" select * from dc_base_sku where     bailun_sku=@bailun_sku ", new
            {
                bailun_sku = sku.sku
            });
            foreach (var stockItem in stocks)
            {
                var warehouseData = _connection.QueryFirstOrDefault<dc_base_warehouse>(" select * from dc_base_warehouse where warehouse_code=@warehouse_code  ", new
                {
                    warehouse_code = stockItem.warehouse_code
                });
                // 查询在途数据
                var stock_transit = _connection.QueryFirstOrDefault<dc_mid_transit>(" select * from dc_mid_transit where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku ", new
                {
                    warehouse_code = stockItem.warehouse_code,
                    bailun_sku = sku.sku
                })??new dc_mid_transit { };
                // 查询历史订单
                var orders = _connection.Query<dc_base_oms_sku>(" select * from dc_base_oms_sku where warehouse_code=@warehouse_code and bailun_sku=@bailun_sku and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and platform_type != 'FBA' and paid_time<=@etime ", new
                {
                    warehouse_code = stockItem.warehouse_code,
                    bailun_sku = sku.sku,
                    etime = dayEtime
                }).ToList();


                dc_report_invest_return_analysis ana = new dc_report_invest_return_analysis
                {
                    sku = sku.sku,
                    stock = stockItem.usable_stock + stock_transit.quantity_purchase + stock_transit.quantity_transfer,
                    project = sku.project,
                    sku_name = baseSku.sku_title_cn,
                    quantity_transfer = stock_transit.quantity_transfer,
                    quantity_purchase = stock_transit.quantity_purchase,
                    create_user = "system",
                    first_order_date = null,
                    last_order_date = null,
                    gmt_create_time = DateTime.Now,
                    gmt_update_time = DateTime.Now,
                    order_days = orders.Where(s => s.paid_time != null).Select(s => s.paid_time.Value.ToString("yyyy-MM-dd")).Distinct().Count(),
                    library_stock = stockItem.usable_stock,
                    price = baseSku.unit_price,
                    quantity_purchase_amount = 0,
                    quantity_transfer_amount = 0,
                    sales_day_30 = orders.Where(s => s.paid_time >= day30Btime).Select(s => s.bailun_order_id).Distinct().Count(),
                    sales_day_7 = orders.Where(s => s.paid_time >= day7Btime).Select(s => s.bailun_order_id).Distinct().Count(),
                    consume_days_30 = orders.Where(s => s.paid_time != null).Select(s => s.paid_time.Value.ToString("yyyy-MM-dd")).Distinct().Count(),
                    stock_amount = 0,
                    warehouse_code = stockItem.warehouse_code,
                    library_stock_amount = 0,
                    warehouse_name = warehouseData.warehouse_name,
                    sales_day_average = 0,
                    profit_30_forecast = 0,
                    vendibility_days = 0,
                    seller_order_exchange_rate = null,
                };

                if (orders.Any(s => s.seller_order_exchange_rate > 0))
                {
                   // ana.seller_order_exchange_rate = orders.Where(s => s.paid_time >= day30Btime).Where(s => s.seller_order_exchange_rate > 0).Average(s => s.seller_order_exchange_rate);
                }
                ana.quantity_purchase_amount = ana.quantity_purchase * ana.price;
                ana.quantity_purchase_amount = ana.quantity_purchase * ana.price;
                ana.quantity_transfer_amount = ana.quantity_transfer * ana.price;
                ana.stock_amount = ana.stock * ana.price;
                ana.library_stock_amount = ana.library_stock * ana.price;
                ana.sales_day_average =Math.Round( (ana.sales_day_30 .Division( 30M) * 0.5M) + (ana.sales_day_7.Division( 7M) * 0.5M),0);
                ana.vendibility_days = Math.Round( ana.stock.Division( ana.sales_day_average),0);
                /// 30天利润
                ///预计30天内销量，可售天数大于30天的，则为加权日均销量*30，可售天数小于30天的，则为加权日均销量* 可售天数
                ///预计30天内销售额=预计30天内销量* 单位售价
                ///预计30天营业利润=预计30天内销量* 单位售价*(1-最近1个月的退款率-最近1个月的平台费率最近1个月的广告费率)-预计30天内销量* 单位采购成本
                ana.profit_30_forecast = 0;
                //预计30天内销量
                ana.sales_day_30_forecast = ana.sales_day_average * ana.vendibility_days;
                if (ana.vendibility_days > 30)
                {
                    //预计30天内销量
                    ana.sales_day_30_forecast = ana.sales_day_average * 30;
                }
                ana.gmv_day30_avg_unit_price_cny = 0;
                if (orders.Any(s => s.amount_sales > 0 && s.seller_order_exchange_rate > 0))
                {
                    //过去30天 单位售价
                    ana.gmv_day30_avg_unit_price_cny = 0;
                    var unit_price_orders = orders.Where(s => s.amount_sales > 0 && s.seller_order_exchange_rate > 0);
                    if (unit_price_orders.Count() >= 1)
                    {
                        ana.gmv_day30_avg_unit_price_cny = unit_price_orders.Average(s => s.amount_sales * s.seller_order_exchange_rate);
                    }
                   
                }
                //预计30天内销售额
                ana.gmv_day_30_forecast_cny = ana.sales_day_30_forecast * ana.gmv_day30_avg_unit_price_cny;
                // 计算最近1个月的广告费率
                var adFees = _connection.Query<t_base_shopify_adfee_order>(" select * from t_base_shopify_adfee_order where is_delete=0 and bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and order_pay_time>=@order_pay_btime and order_pay_time<=@order_pay_etime ", new
                {
                    bailun_sku = ana.sku,
                    warehouse_code = ana.warehouse_code,
                    order_pay_btime = day30Btime,
                    order_pay_etime = dayEtime,
                }).ToList();

                ana.adfee_day_30 = adFees.Sum(s => s.order_fee);
                ana.amount_total_day_30 = orders.Where(s => s.paid_time >= day30Btime).Sum(s => s.amount_total * s.order_to_usd_exchange_rate);
                ana.adfee_rate = Math.Round(ana.adfee_day_30 .Division( ana.amount_total_day_30), 4);

                // 计算最近1个月的平台费率
                ana.platform_fee_day_30 = orders.Where(s => s.paid_time >= day30Btime).Sum(s => s.cost_platform_fee * s.order_to_usd_exchange_rate);
                ana.platform_fee_rate = Math.Round(ana.platform_fee_day_30 .Division( ana.amount_total_day_30), 4);



                // 计算最近1个月的退款费率 
                // de的读btm的，其他的读oms的
                if ("de".Equals(ana.project, StringComparison.InvariantCultureIgnoreCase))
                {
                    // 计算最近1个月的广告费率
                    ana.refund_amount_day_30 = _connection.QueryFirstOrDefault<decimal?>(" select * from t_base_shopify_refund where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and order_pay_time>=@order_pay_btime and order_pay_time<=@order_pay_etime ", new
                    {
                        bailun_sku = ana.sku,
                        warehouse_code = ana.warehouse_code,
                        order_pay_btime = day30Btime,
                        order_pay_etime = dayEtime,
                    }) ?? 0M;

                }
                else
                {
                    ana.refund_amount_day_30 = 0;
                }
                ana.refund_rate = Math.Round(ana.refund_amount_day_30 .Division( ana.amount_total_day_30), 4); ;

                // 预计30天利润
                ana.profit_30_forecast = ana.sales_day_30 * (1 - ana.refund_rate - ana.adfee_rate - ana.platform_fee_rate);

                if (orders != null && orders.Count(s => s.paid_time != null) >= 1)
                {
                    ana.first_order_date = orders.Where(s => s.paid_time != null).Min(s => s.paid_time.Value);
                    ana.last_order_date = orders.Where(s => s.paid_time != null).Max(s => s.paid_time.Value);
                }

                //计算单位运费
                ana.freight_unit_price = CalculationFreight(ana.sku, ana.warehouse_code, day30Btime.AddDays(-30 *5),dayEtime);
                
                //预估运费，库存 * 单位运费
                ana.estimated_freight = ana.freight_unit_price * ana.stock;
                ana.stock_score = (ana.sales_day_average * 90).Division(ana.stock) * 100;
                ana.id = _connection.QueryFirstOrDefault<int?>(" select id from dc_report_invest_return_analysis where sku=@sku and warehouse_code=@warehouse_code ", new
                {
                    sku = ana.sku,
                    warehouse_code = ana.warehouse_code
                }) ?? 0;
                if (ana.id <= 0)
                {
                    _connection.Insert(ana);
                }
                else
                {
                    _connection.Update(ana);
                }
            }
        }

        /// <summary>
        /// 分摊广告费算法
        /// </summary>
        public static void ShareAdFee()
        {

            // 查询100条未分摊过的费用
            var fees = _connection.Query<t_base_shopify_adfee>(" select * from t_base_shopify_adfee where share_time is null limit 100 ").ToList();
            foreach (var feeItem in fees)
            {
                // 查询这个账号，该时间范围，所有有效订单
                var orders = _connection.Query<dc_base_oms_sku>(" select * from dc_base_oms_sku where bailun_account_id=@bailun_account_id  and   bailun_order_status not in ('Canceled')      and bailun_interception_status in ('None', 'Failed')  and platform_type != 'FBA' and paid_time>=@btime and paid_time<=@etime ", new
                {
                    bailun_account_id = feeItem.account_id,
                    btime = feeItem.bdate,
                    etime = feeItem.edate
                }).Where(s => s.order_to_usd_exchange_rate > 0 && s.amount_total > 0).ToList();
                if (orders != null && orders.Count > 0)
                {
                    List<t_base_shopify_adfee_order> adfee_orders = new List<t_base_shopify_adfee_order>();
                    // 计算这个范围内的总销售额
                    var amount_total_usd = orders.Sum(s => s.order_to_usd_exchange_rate * s.amount_total);
                    var useFeeCny = 0M;
                    var useFeeUsd = 0M;
                    foreach (var itemOrder in orders)
                    {
                        var order_amount_total = itemOrder.order_to_usd_exchange_rate * itemOrder.amount_total;
                        var ratio = order_amount_total / amount_total_usd;
                        t_base_shopify_adfee_order addfee_order = new t_base_shopify_adfee_order
                        {
                            amount_total_usd = amount_total_usd,
                            bailun_account_id = itemOrder.bailun_account_id,
                            bailun_sku = itemOrder.bailun_sku,
                            warehouse_code = itemOrder.warehouse_code,
                            calculation_btime = feeItem.bdate,
                            is_delete = 0,
                            order_pay_time = itemOrder.paid_time.Value,
                            calculation_etime = feeItem.edate,
                            date_str = feeItem.date_str,
                            fee_data_id = feeItem.data_id,
                            order_amount_total_usd = order_amount_total,
                            order_unique = $"{itemOrder.origin_order_id}-{itemOrder.bailun_account_id}-{itemOrder.bailun_sku}",
                            origin_order_id = itemOrder.origin_order_id,
                            origin_fee = feeItem.fee,
                            origin_fee_cny = feeItem.fee_cny,
                            order_fee = Math.Round(feeItem.fee * ratio, 2),
                            order_fee_cny = Math.Round(feeItem.fee_cny * ratio, 4),
                        };
                        addfee_order.unique_id = $"{addfee_order.fee_data_id}-{addfee_order.order_unique}";
                        useFeeCny += addfee_order.order_fee_cny;
                        useFeeUsd += addfee_order.order_fee;
                        adfee_orders.Add(addfee_order);
                    }

                    // 如果分摊之后，还有小数没有分摊均匀。就把多余的全部给最大的那笔
                    if (feeItem.fee_cny != useFeeCny || feeItem.fee != useFeeUsd)
                    {
                        var topOrder = adfee_orders.OrderByDescending(s => s.order_amount_total_usd).FirstOrDefault();
                        topOrder.order_fee += (feeItem.fee - useFeeUsd);
                        topOrder.order_fee_cny += (feeItem.fee_cny - useFeeCny);
                    }

                    foreach (var item in adfee_orders)
                    {
                        try
                        {
                            item.id = _connection.QueryFirstOrDefault<int?>(" select id from t_base_shopify_adfee_order where unique_id=@unique_id ", new { unique_id = item.unique_id }) ?? 0;
                            if (item.id <= 0)
                            {
                                _connection.Insert(item);
                            }
                            else
                            {
                                _connection.Update(item);
                            }
                        }
                        catch (Exception ex)
                        {

                            throw;
                        }

                    }

                    // 清理多余的数据
                    _connection.Execute(" update t_base_shopify_adfee_order set is_delete=1 where fee_data_id=@fee_data_id and unique_id not in @unique_ids ", new
                    {
                        fee_data_id = feeItem.data_id,
                        unique_ids = adfee_orders.Select(s => s.unique_id).ToList()
                    });

                }

                // 标记为已分摊
                _connection.Execute(" update t_base_shopify_adfee set share_time=now() where id=@id ", new { id = feeItem.id });
            }
        }

        /// <summary>
        /// 计算单位运费 
        /// </summary>
        public static decimal CalculationFreight(string bailun_sku, string warehouse_code, DateTime btime, DateTime etime)
        {
            // 清理运费计算日志
            _connection.Execute(" delete from dc_base_transfer_freight_new_order where main_bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
            {
                bailun_sku = bailun_sku,
                warehouse_code = warehouse_code
            });

            // 查询这个sku 时间范围内所有的调拨单明细
            var tempDatas = _connection.Query<CalculationFreightDto>($@"select t2.sku ,
t1.`code`,
t2.deliverycount,
t1.estimatedcost as 'order_estimatedcost',
t3.deliverycount as 'order_deliverycount',
t4.weight
from dc_base_transfer_info as t1
left join dc_base_transfer_info_skus as t2 on t1.dataid = t2.dataid
left join ( select dataid,sum(deliverycount) as 'deliverycount' from dc_base_transfer_info_skus GROUP BY dataid ) as t3 on  t1.dataid = t3.dataid
left join dc_base_sku as t4 on t2.sku = t4.bailun_sku
where t1.isdeleted=0 
and t1.isexception = 0
and t1.creationtime >=@btime and  t1.creationtime <=@etime
and t1.targetwareno=@warehouse_code
and t1.estimatedcost>0  
and t1.dataid in ( select dataid from dc_base_transfer_info_skus where  sku=@sku)", new
            {
                btime = btime,
                etime = etime,
                warehouse_code = warehouse_code,
                sku = bailun_sku
            }).ToList();

            if (tempDatas != null && tempDatas.Count >= 1)
            {
                List<dc_base_transfer_freight_new_order> orders = new List<dc_base_transfer_freight_new_order>();
                foreach (var itemGroup in tempDatas.GroupBy(s => s.code))
                {
                    //计算该订单总sku重量
                    var sumWeight = itemGroup.Sum(s => s.weight * s.deliverycount);
                    var sumSkuCount = itemGroup.Sum(s => s.deliverycount);
                    var sumCost = itemGroup.FirstOrDefault().order_estimatedcost;
                    List<dc_base_transfer_freight_new_order> groupOrders = new List<dc_base_transfer_freight_new_order>();
                    foreach (var item in itemGroup.AsQueryable())
                    {
                        dc_base_transfer_freight_new_order new_order = new dc_base_transfer_freight_new_order()
                        {
                            main_bailun_sku = bailun_sku,
                            bailun_sku = item.sku,
                            gmt_update_time = DateTime.Now,
                            sku_count = item.deliverycount,
                            transfer_order_id = itemGroup.Key,
                            sku_weight = item.weight * item.deliverycount,
                            warehouse_code = warehouse_code,
                            order_cost = item.order_estimatedcost,
                            order_sku_count = sumSkuCount,
                            order_weight = sumWeight,
                            sku_cost = Math.Round((item.weight * item.deliverycount).Division(sumWeight) * item.order_estimatedcost, 2)
                        };
                       
                        groupOrders.Add(new_order);
                    }
                    var cha = (sumCost - groupOrders.Sum(s => s.sku_cost));
                    if (cha>0)
                    {
                        var topSkuOrder = groupOrders.OrderByDescending(s => s.order_cost).FirstOrDefault();
                      
                        topSkuOrder.order_cost += cha;
                    }
                    orders.AddRange(groupOrders);
                    foreach (var itemOrder in orders)
                    {
                        _connection.Insert(itemOrder);
                    }

                   
                }

                var thisOrders = orders.Where(s => s.bailun_sku == bailun_sku).ToList();
                dc_base_transfer_freight_new newData = new dc_base_transfer_freight_new()
                {
                    bailun_sku = bailun_sku,
                    count = thisOrders.Sum(s => s.sku_count),
                    freight = thisOrders.Sum(s => s.sku_cost),
                    update_time = DateTime.Now,
                    warehouse_code = warehouse_code,
                    id = _connection.QueryFirstOrDefault<int?>(" select id from dc_base_transfer_freight_new where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = bailun_sku, warehouse_code = warehouse_code }) ?? 0
                };
                newData.freight_unit_price = newData.freight.Division(newData.count);
                if (newData.id <= 0)
                {
                    _connection.Insert(newData);
                }
                else
                {
                    _connection.Update(newData);
                }
                return newData.freight_unit_price;
            }
            else
            {
                // 清空运费
                _connection.Execute(" delete from dc_base_transfer_freight_new where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new
                {
                    bailun_sku = bailun_sku,
                    warehouse_code = warehouse_code,
                });
                return 0;
            }
        }

        public class CalculationFreightDto
        {
            public string sku { get; set; }
            public string code { get; set; }
            /// <summary>
            /// 发货数量
            /// </summary>
            public decimal deliverycount { get; set; }
            /// <summary>
            /// 调拨单总运费
            /// </summary>
            public decimal order_estimatedcost { get; set; }
            public string order_deliverycount { get; set; }

            /// <summary>
            /// sku 单位重量
            /// </summary>
            public decimal weight { get; set; }


        }
    }
}
