using AutoTurnOver.DB;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;
using AutoTurnOver.Models;
using Dapper;
using AutoTurnOver.Utility;

namespace AutoTurnOver.Services
{
    /// <summary>
    /// 采购平均指标计算
    /// </summary>
    public class PurchaseAverageTargetServices
    {
        /// <summary>
        /// 取数基数
        /// </summary>
        public static int _seed_count = 10;

        public static void Calculation(string bailun_sku = null, int days = 7)
        {
            MyMySqlConnection conn = connectionHelper._connection;

            var task_log = new dc_task_log
            {
                StartTime = DateTime.Now,
                Type = 1,
                Count = 0
            };

            var taskID = conn.Insert(task_log);
            task_log.ID = taskID.Value;

            try
            {

                // 获取最近2天发送变化的SKU 用于计算
                var chageSkuList = AveragePurchase.GetTransferChangeSku(days, bailun_sku);
                foreach (var skuItem in chageSkuList)
                {
                    // 清空记录，重新记录数据源
                    conn.Execute(" delete from dc_base_order_data_source where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and order_type=@order_type ", new
                    {
                        bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                        order_type = "采购单-签收天数计算"
                    });

                    // 判断是否jit
                    var has_jit = ((skuItem.buyer_name == "张莹霞" || skuItem.buyer_name == "张莹霞1") && skuItem.warehouse_code == "GZBLWH");

                    dc_average_purchase data = new dc_average_purchase()
                    {
                        bailun_sku = skuItem.bailun_sku,
                        supplier_id = skuItem.supplier_id ?? 0,
                        warehouse_code = skuItem.warehouse_code
                    };



                    // 查询相关采购单
                    var purchaseList = AveragePurchase.GetPurchaseList(skuItem, _seed_count).ToList();


                    //查出来的采购单数量
                    var purchase_order_count = purchaseList.Select(s => s.purchase_id).Distinct().Count();

                    //有相关采购单 (要至少有3个单，才算平均数)
                    if (purchaseList != null && purchase_order_count >= 3)
                    {
                        var inboundList = AveragePurchase.GetInboundList(purchaseList.Select(s => s.purchase_id), skuItem.bailun_sku).ToList();
                        data.supplier_name = purchaseList[0].supplier_name;

                        //计算平均价格
                        data.price = purchaseList.Sum(s => s.price ?? 0) / purchaseList.Count;

                        // 计算平均到货天数
                        // 计算总交期
                        int delivery_days = 0;


                        foreach (var itemArrivalList in purchaseList.GroupBy(s => s.purchase_id))
                        {
                            var tempPurchase = purchaseList.SingleOrDefault(s => s.purchase_id == itemArrivalList.Key);
                            var temp_s_delivery_days = 0;
                            if (tempPurchase.pay_type == 1)
                            {
                                temp_s_delivery_days = (int)Math.Ceiling((itemArrivalList.Min(s => s.last_signtime) - tempPurchase.pay_time).Value.TotalHours / 24);

                            }
                            else
                            {
                                temp_s_delivery_days = (int)Math.Ceiling((itemArrivalList.Min(s => s.last_signtime) - tempPurchase.confirm_time).Value.TotalHours / 24);
                            }
                            delivery_days += temp_s_delivery_days;

                            conn.Insert(new dc_base_order_data_source
                            {
                                bailun_sku = skuItem.bailun_sku,
                                warehouse_code = skuItem.warehouse_code,
                                logistics_code = "",
                                bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                                bale_days = 1,
                                logistics_name = "",
                                order_no = itemArrivalList.Key,
                                order_type = "采购单-签收天数计算",
                                put_days = 0,
                                sign_days = temp_s_delivery_days,
                                transport_type = "",
                                confirm_time = tempPurchase.confirm_time,
                                create_date = tempPurchase.create_time,
                                pay_time = tempPurchase.pay_time,
                                pay_type = (tempPurchase.pay_type == 1 ? "先款后货" : "先货后款"),
                                sign_time = itemArrivalList.Min(s => s.last_signtime),
                                put_time = (inboundList.OrderByDescending(s => s.update_time).FirstOrDefault(s => s.purchase_id == itemArrivalList.Key) ?? new Models.dc_base_purchase.dc_base_purchase_inbound_dto()).update_time
                            });
                        }


                        // 平均采购交期
                        var temp_delivery_days = delivery_days / purchaseList.Count;
                        data.delivery_days = temp_delivery_days;
                        data.virtual_delivery_days = temp_delivery_days;
                        data.payment_type = purchaseList.Any(s => s.pay_type == 1) ? 1 : 2;

                        if (has_jit)
                        {
                            data.delivery_days = 3;
                            data.payment_type = 2; // 先货后款
                        }

                        //写入数据库
                        AveragePurchase.Save(data, conn);

                        conn.Insert(new dc_base_queue
                        {
                            submit_date = DateTime.Now,
                            error_message = "",
                            consume_date = null,
                            error_stack_trace = "",
                            message = new { bailun_sku = data.bailun_sku, warehouse_code = data.warehouse_code }.ToJson(),
                            type = "刷新周转表"
                        });

                        task_log.Count++;
                    }
                    else
                    {
                        // 不够3个单，不该有平均数
                        // 不够5个单不算这个值
                        var sku_sign_days = WriteConfig(conn, skuItem, has_jit);
                        data.delivery_days = (int)sku_sign_days;
                        data.supplier_name = "";

                        if (has_jit)
                        {
                            data.payment_type = 2; // 先货后款
                        }
                        AveragePurchase.Save(data, conn);
                    }

                }

                task_log.CompleteTime = DateTime.Now;
            }
            catch (Exception ex)
            {
                task_log.ErrorMsg = ex.Message + "--------" + ex.StackTrace;
            }

            conn.Update(task_log);
        }

        public static decimal WriteConfig(MyMySqlConnection conn, temp_sku_dto skuItem, bool has_jit)
        {
            var sku_sign_days = 0M;
            if (has_jit)
            {
                sku_sign_days = 3;
            }
            else
            {
                // 查sku表
                var skuData = conn.QuerySingleOrDefault<dc_base_sku>(" select * from dc_base_sku where bailun_sku=@bailun_sku ", new { bailun_sku = skuItem.bailun_sku });
                if (skuData != null)
                {
                    if (skuItem.hq_type == "国内仓")
                    {
                        sku_sign_days = skuData.supplier_delivery;
                    }
                    else
                    {
                        sku_sign_days = skuData.transfer_delivery;
                    }
                }
            }

            conn.Insert(new dc_base_order_data_source
            {
                bailun_sku = skuItem.bailun_sku,
                warehouse_code = skuItem.warehouse_code,
                logistics_code = "",
                bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                bale_days = 1,
                logistics_name = "",
                order_no = "无有效历史单，读sku的配置",
                order_type = "采购单-签收天数计算",
                put_days = 0,
                sign_days = sku_sign_days,
                transport_type = ""
            });

            return sku_sign_days;
        }

        public static void CalculationTransfer(string bailun_sku = "", int days = 30)
        {

            MyMySqlConnection conn = connectionHelper._connection;

            var task_log = new dc_task_log
            {
                StartTime = DateTime.Now,
                Type = 2,
                Count = 0
            };

            var taskID = conn.Insert(task_log);
            task_log.ID = taskID.Value;

            try
            {

                // 获取最近2天发送变化的SKU 用于计算
                var chageSkuList = AveragePurchase.GetTransferChangeSku(days, bailun_sku);
                foreach (var skuItem in chageSkuList)
                {
                    try
                    {
                        if (skuItem.warehouse_code == "MMDUSFBA")
                        {

                        }
                        var base_wa = conn.QueryFirstOrDefault<string>(" select t2.`code` from dc_base_warehouse as t1 left join dc_base_country as t2 on t1.area_name = t2.`name` where warehouse_code=@warehouse_code ", new { skuItem.warehouse_code });

                        var purchaseWList = AveragePurchase.GetPurchaseList(skuItem, _seed_count).ToList();

                        // 清空记录，重新记录数据源
                        conn.Execute(" delete from dc_base_order_data_source where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and order_type=@order_type ", new
                        {
                            bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                            order_type = "采购单-质检入库天数计算"
                        });
                        dc_average_warehouse dataWarehouse = new dc_average_warehouse()
                        {
                            bailun_sku = skuItem.bailun_sku,
                            warehouse_code = skuItem.warehouse_code
                        };
                        if (purchaseWList != null && purchaseWList.Count() >= 1)
                        {
                            // 计算平均入库天数
                            // 查询相关的入库记录
                            var inboundList = AveragePurchase.GetInboundList(purchaseWList.Select(s => s.purchase_id), skuItem.bailun_sku).ToList();


                            if (inboundList != null && inboundList.Count >= 1)
                            {
                                foreach (var itemLms in inboundList.GroupBy(s => s.purchase_id))
                                {
                                    var puData = purchaseWList.FirstOrDefault(s => s.purchase_id == itemLms.Key);
                                    conn.Insert(new dc_base_order_data_source
                                    {
                                        bailun_sku = skuItem.bailun_sku,
                                        warehouse_code = skuItem.warehouse_code,
                                        logistics_code = "",
                                        bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                                        bale_days = 0,
                                        logistics_name = "",
                                        order_no = itemLms.Key,
                                        order_type = "采购单-质检入库天数计算",
                                        put_days = (decimal)Math.Ceiling((itemLms.Max(s => s.update_time) - itemLms.Max(s => s.arrival_time)).TotalHours / 24),
                                        sign_days = 0,
                                        transport_type = "",
                                        sign_time = itemLms.Max(s => s.arrival_time),
                                        put_time = itemLms.Max(s => s.update_time),
                                        confirm_time = puData.confirm_time,
                                        create_date = puData.create_time,
                                        pay_time = puData.pay_time,
                                        pay_type = (puData.pay_type == 1 ? "先款后货" : "先货后款")
                                    });
                                }

                            }

                            if (inboundList != null && inboundList.Count() >= 1)
                            {
                                var inboundListGroup = inboundList.GroupBy(s => s.purchase_id).ToList();
                                dataWarehouse.inbound_days = (int)inboundListGroup.Sum(s => Math.Ceiling((s.Max(v => v.update_time) - s.Max(v => v.arrival_time)).TotalHours / 24)) / inboundListGroup.Count();
                            }
                            else
                            {
                                var inbound_days = WritePutConfig(conn, skuItem);
                                dataWarehouse.inbound_days = (int)inbound_days;
                            }
                        }
                        else
                        {
                            var inbound_days = WritePutConfig(conn, skuItem);
                            dataWarehouse.inbound_days = (int)inbound_days;
                        }

                        //头程天数
                        // 查询是否有默认渠道
                        var dc_base_head_transport_config_data = conn.QuerySingleOrDefault<dc_base_head_transport_config>(" select * from dc_base_head_transport_config  where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code and is_delete=0 ", new
                        {
                            bailun_sku = skuItem.bailun_sku,
                            warehouse_code = skuItem.warehouse_code
                        }) ?? new dc_base_head_transport_config { };
                        //var transport3List = AveragePurchase.GetTransportList(skuItem.bailun_sku, skuItem.warehouse_code, dc_base_head_transport_config_data.head_transport, dc_base_head_transport_config_data.logistics_code, _seed_count);
                        var transfer_type = 0;
                        if (dc_base_head_transport_config_data.head_transport == "陆运")
                        {
                            transfer_type = 1;
                        }
                        else if (dc_base_head_transport_config_data.head_transport == "海运")
                        {
                            transfer_type = 2;
                        }
                        else if (dc_base_head_transport_config_data.head_transport == "空运")
                        {
                            transfer_type = 3;
                        }

                        //如果没有配置渠道，则去lms查哪些渠道可发
                        if (string.IsNullOrWhiteSpace(dc_base_head_transport_config_data.logistics_code))
                        {
                            // 查询物流方案
                            var logisticsDatas = ApiUtility.RealTimeShipLogisticsList(new Models.ApiDto.LmsShipLogisticsRequstDto
                            {
                                endCountries = base_wa,
                                character_skus = skuItem.bailun_sku,
                                startPoint = "3"
                            });
                            if (logisticsDatas != null && logisticsDatas.Count >= 1)
                            {
                                dc_base_head_transport_config_data.logistics_code = string.Join(",", logisticsDatas.Select(s => s.Line_Code));
                            }
                            else
                            {
                                dc_base_head_transport_config_data.logistics_code = "无可发渠道";
                            }
                        }

                        var lmsAvg = ApiUtility.GetLmsAvgOrderTransferList(new Models.ApiDto.api_lms_order_transfer_sku_requst_dto
                        {
                            //bailun_sku = skuItem.bailun_sku,
                            transfer_type_val = transfer_type,
                            //warehouse_code = skuItem.warehouse_code,
                            logistics_code = dc_base_head_transport_config_data.logistics_code,
                            country = base_wa
                        }).GroupBy(s => new { s.order_no, s.logistics_code });

                        if (lmsAvg != null && lmsAvg.Count() >= 1)
                        {
                            dataWarehouse.transfer_bale_delivery = lmsAvg.Select(s => s.FirstOrDefault().calculation_bale_days).Average(); ;
                            dataWarehouse.transfer_delivery = lmsAvg.Select(s => s.FirstOrDefault().calculation_sign_days).Average();
                            dataWarehouse.abroad_inbound_delivery = lmsAvg.Select(s => s.FirstOrDefault().calculation_put_days).Average();
                        }

                        // 清空记录，重新记录数据源
                        conn.Execute(" delete from dc_base_order_data_source where bailun_sku_warehouse_code=@bailun_sku_warehouse_code and order_type=@order_type ", new
                        {
                            bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                            order_type = "调拨单-平均天数计算"
                        });
                        if (lmsAvg != null && lmsAvg.Count() >= 1)
                        {
                            foreach (var itemLms in lmsAvg)
                            {
                                conn.Insert(new dc_base_order_data_source
                                {
                                    bailun_sku = skuItem.bailun_sku,
                                    warehouse_code = skuItem.warehouse_code,
                                    logistics_code = itemLms.FirstOrDefault().logistics_code,
                                    bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                                    bale_days = itemLms.FirstOrDefault().calculation_bale_days,
                                    logistics_name = itemLms.FirstOrDefault().logistics_name,
                                    order_no = itemLms.Key.order_no,
                                    order_type = "调拨单-平均天数计算",
                                    put_days = itemLms.FirstOrDefault().put_days,
                                    sign_days = itemLms.FirstOrDefault().sign_days,
                                    transport_type = itemLms.FirstOrDefault().transport_type,
                                    put_time = itemLms.FirstOrDefault().put_time,
                                    sign_time = itemLms.FirstOrDefault().sign_time,
                                    shipping_time = itemLms.FirstOrDefault().shipping_time,
                                    create_date = itemLms.FirstOrDefault().create_date,
                                });
                            }
                        }
                        else
                        {
                            //写入调拨配置
                            WriteTransConfig(conn, skuItem, dataWarehouse);
                        }

                        var lmsTransportCountDatas = ApiUtility.GetLmsTransportCountList(new Models.ApiDto.api_transport_count_order_transfer_query_dto
                        {
                            bailun_sku = skuItem.bailun_sku,
                            country = base_wa
                        });

                        if (lmsTransportCountDatas != null && lmsTransportCountDatas.Count>=1)
                        {
                            var lmsTransportCountData = lmsTransportCountDatas.FirstOrDefault();
                            dataWarehouse.product_count = lmsTransportCountData.product_count;
                            dataWarehouse.product_count_2 = lmsTransportCountData.product_count_2;
                            dataWarehouse.product_count_3 = lmsTransportCountData.product_count_3;
                            dataWarehouse.order_count = lmsTransportCountData.order_count;
                            dataWarehouse.order_count_2 = lmsTransportCountData.order_count_2;
                            dataWarehouse.order_count_3 = lmsTransportCountData.order_count_3;
                        }

                        AveragePurchase.Save(dataWarehouse, conn);

                        conn.Insert(new dc_base_queue
                        {
                            submit_date = DateTime.Now,
                            error_message = "",
                            consume_date = null,
                            error_stack_trace = "",
                            message = new { bailun_sku = dataWarehouse.bailun_sku, warehouse_code = dataWarehouse.warehouse_code }.ToJson(),
                            type = "刷新周转表"
                        });

                    }
                    catch (Exception ex)
                    {
                        conn.Insert(new dc_task_error_log
                        {
                            date = DateTime.Now,
                            task_name = "CalculationTransfer",
                            message = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                            stack_trace = ex.Message + ": " + ex.StackTrace
                        });
                    }


                }

                task_log.CompleteTime = DateTime.Now;
            }
            catch (Exception ex)
            {
                task_log.ErrorMsg = ex.Message + "--------" + ex.StackTrace;
            }

            conn.Update(task_log);
        }

        /// <summary>
        /// 读头程天数的配置
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="skuItem"></param>
        /// <param name="dataWarehouse"></param>
        public static void WriteTransConfig(MyMySqlConnection conn, temp_sku_dto skuItem, dc_average_warehouse dataWarehouse)
        {
            dataWarehouse.abroad_inbound_delivery = 0M; // 海外仓入库
            dataWarehouse.transfer_delivery = 0M; // 调拨
            dataWarehouse.transfer_bale_delivery = 0M; // 打包.
            if (skuItem.hq_type == "国内仓")
            {

                conn.Insert(new dc_base_order_data_source
                {
                    bailun_sku = skuItem.bailun_sku,
                    warehouse_code = skuItem.warehouse_code,
                    logistics_code = "",
                    bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                    bale_days = dataWarehouse.transfer_bale_delivery,
                    logistics_name = "",
                    order_no = "国内仓，该字段无意义",
                    order_type = "调拨单-平均天数计算",
                    put_days = dataWarehouse.abroad_inbound_delivery,
                    sign_days = dataWarehouse.transfer_delivery,
                    transport_type = ""
                });
            }
            else
            {

                // 查询aims的配置
                var dc_auto_config_delivery_data = conn.QuerySingleOrDefault<dc_auto_config_delivery>(" select * from dc_auto_config_delivery where `type`=3 and variable_code=@variable_code ", new { variable_code = skuItem.warehouse_code });
                if (dc_auto_config_delivery_data != null)
                {
                    dataWarehouse.abroad_inbound_delivery = dc_auto_config_delivery_data.abroad_inbound; // 海外仓入库
                    dataWarehouse.transfer_delivery = dc_auto_config_delivery_data.tranfer_head; // 调拨
                    dataWarehouse.transfer_bale_delivery = dc_auto_config_delivery_data.tranfer_bale; // 打包.
                }
                else
                {
                    var dc_auto_config_delivery_data1 = conn.QuerySingleOrDefault<dc_auto_config_delivery>(" select * from dc_auto_config_delivery where `type`=4 and variable_code=@variable_code ", new { variable_code = skuItem.hq_type });
                    if (dc_auto_config_delivery_data1 != null)
                    {
                        dataWarehouse.abroad_inbound_delivery = dc_auto_config_delivery_data1.abroad_inbound; // 海外仓入库
                        dataWarehouse.transfer_delivery = dc_auto_config_delivery_data1.tranfer_head; // 调拨
                        dataWarehouse.transfer_bale_delivery = dc_auto_config_delivery_data1.tranfer_bale; // 打包.
                    }
                }

                conn.Insert(new dc_base_order_data_source
                {
                    bailun_sku = skuItem.bailun_sku,
                    warehouse_code = skuItem.warehouse_code,
                    logistics_code = "",
                    bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                    bale_days = dataWarehouse.transfer_bale_delivery,
                    logistics_name = "",
                    order_no = "无有效历史单，读aims配置",
                    order_type = "调拨单-平均天数计算",
                    put_days = dataWarehouse.abroad_inbound_delivery,
                    sign_days = dataWarehouse.transfer_delivery,
                    transport_type = ""
                });

            }



        }

        public static decimal WritePutConfig(MyMySqlConnection conn, temp_sku_dto skuItem)
        {
            var sku_sign_days = 2M;


            conn.Insert(new dc_base_order_data_source
            {
                bailun_sku = skuItem.bailun_sku,
                warehouse_code = skuItem.warehouse_code,
                logistics_code = "",
                bailun_sku_warehouse_code = $"{skuItem.bailun_sku}{skuItem.warehouse_code}",
                bale_days = 1,
                logistics_name = "",
                order_no = "无有效历史单，当前写死2天",
                order_type = "采购单-质检入库天数计算",
                put_days = sku_sign_days,
                sign_days = 0,
                transport_type = ""
            });

            return sku_sign_days;
        }
        public static IEnumerable<dc_average_purchase> GetListByWCode(string sku, string warehouse_code)
        {
            return AveragePurchase.GetListByWCode(sku, warehouse_code);
        }

        public static IEnumerable<dc_average_purchase> GetList(string supplier_ids, string bailun_skus)
        {
            return AveragePurchase.GetList(supplier_ids, bailun_skus);
        }

        public static dc_average_warehouse_dto GetWarehouse(string sku, string warehouse_code)
        {
            return AveragePurchase.GetWarehouse(sku, warehouse_code);
        }



    }


}
