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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 下首单
    /// </summary>
    public class dc_auto_first_order_sku_dao : connectionHelper
    {
        public static void Add(dc_auto_first_order_sku_input_dto input_data)
        {
            var now = DateTime.Now;
            if (input_data.detailed == null || input_data.detailed.Count <= 0)
            {
                throw new Exception("sku必填");
            }
            //if (input_data.sendtype <= 0)
            //{
            //    throw new Exception("运输方式必填");
            //}
            if (string.IsNullOrWhiteSpace(input_data.warehouse_code))
            {
                throw new Exception("仓库编码必填");
            }
            using (var conn = MyMySqlConnection._connection)
            {
                conn.Open();
                using (var t = conn.BeginTransaction())
                {
                    foreach (var item in input_data.detailed)
                    {
                        if (string.IsNullOrWhiteSpace(item.bailun_sku))
                        {
                            throw new Exception("sku必填");
                        }

                        var old_data = conn.QuerySingleOrDefault<dc_auto_first_order_sku>(" select * from dc_auto_first_order_sku where bailun_sku=@bailun_sku and warehouse_code=@warehouse_code ", new { bailun_sku = item.bailun_sku, warehouse_code = input_data.warehouse_code });
                        if (old_data != null) throw new Exception($" sku {item.bailun_sku} , 仓库编码 ：{ input_data.warehouse_code} 已存在 ");
                        conn.Insert(new dc_auto_first_order_sku
                        {
                            bailun_sku = item.bailun_sku,
                            push_date = now,
                            warehouse_code = input_data.warehouse_code,
                            sendtype = input_data.sendtype,
                            brand = item.brand,
                            product_inner_code = item.product_inner_code,
                            product_sales = item.product_sales,
                            source = item.source,
                            order_time = new DateTime(1991, 1, 1)
                        }, t);
                    }

                    t.Commit();
                }
            }

        }

        /// <summary>
        /// 生成采购建议
        /// </summary>
        public static void GeneratePurchaseAdvise()
        {

            using (var conn = MyMySqlConnection._connection)
            {
                conn.Open();
                using (var t = conn.BeginTransaction())
                {
                    // 查询当前待出建议的sku
                    var sku_list = conn.Query<dc_auto_first_order_sku>(" select * from dc_auto_first_order_sku where order_time < '2020-01-01' and is_error<=0  ").AsList();
                    //var sku_list = conn.Query<dc_auto_first_order_sku>(" select * from dc_auto_first_order_sku where order_time < '2020-01-01'   ").AsList();

                    var rule_list = conn.Query<dc_auto_first_order_rule>(" select * from dc_auto_first_order_rule ").AsList();

                    var warehouse_list = conn.Query<dc_base_warehouse>(" select * from dc_base_warehouse ").AsList();

                    var err_count = false;
                    foreach (var itemGroup in sku_list.GroupBy(s => new { s.product_inner_code, s.warehouse_code }))
                    {
                        int purchase_advise_id = conn.Insert<dc_auto_purchase_advise>(new dc_auto_purchase_advise { buy_sys_plan_no = "自动下首单", create_time = DateTime.Now, no = purchase_advise.MaxNo(), sendtype = itemGroup.Max(v => v.sendtype) }, t) ?? 0;
                        //记录推送过程
                        StringBuilder remarks = new StringBuilder();
                        try
                        {
                            var item_warehouse = warehouse_list.FirstOrDefault(s => itemGroup.Key.warehouse_code.Equals(s.warehouse_code, StringComparison.CurrentCultureIgnoreCase));
                            if (item_warehouse == null)
                            {
                                remarks.AppendLine($"仓库编码不存在");
                                throw new Exception("仓库编码不存在");
                            }
                            // 匹配规则
                            var item_rule = rule_list.FirstOrDefault(s => s.source == itemGroup.Max(v => v.source)
                            && s.warehouse_type.Split(',').Any(sp => sp.Equals(item_warehouse.hq_type, StringComparison.CurrentCultureIgnoreCase))
                            && (s.brand == "0" || s.brand.Split(',').Any(sp => sp.Equals(itemGroup.Max(v => v.brand), StringComparison.CurrentCultureIgnoreCase)))
                            && s.source == itemGroup.Max(v => v.source)
                            );
                            if (item_rule == null)
                            {
                                remarks.AppendLine($"未命中规则");
                                throw new Exception("未命中规则");
                            }

                            remarks.AppendLine($"命中规则 {item_rule.title}");

                            List<auto_first_order_detailed_dto> data_detailed_list = new List<auto_first_order_detailed_dto>();

                            //if (item_rule.lower_limit_sku_count > 0 && itemGroup.Count() > item_rule.lower_limit_sku_count)
                            //{
                            //    remarks.AppendLine($" sku数量不应该超过 {item_rule.lower_limit_sku_count} 个 ");
                            //    throw new Exception($" sku数量不应该超过 {item_rule.lower_limit_sku_count} 个 ");
                            //}

                            //if (item_rule.lower_limit_sales > 0 && itemGroup.Max(v => v.product_sales) < item_rule.lower_limit_sales)
                            //{
                            //    remarks.AppendLine($" 商品日均不应该低于 {item_rule.lower_limit_sales} 个 ");
                            //    throw new Exception($" 商品日均不应该低于 {item_rule.lower_limit_sales} 个 ");
                            //}

                            int? max_delivery = null;

                            // 固定数量
                            if (item_rule.order_type == 1)
                            {
                                remarks.AppendLine($"采用固定数量 {item_rule.quantity} 下单");

                                var single_sku_count = item_rule.quantity;

                                foreach (var item in itemGroup.AsEnumerable())
                                {
                                    data_detailed_list.Add(new auto_first_order_detailed_dto
                                    {
                                        bailun_sku = item.bailun_sku,
                                        quantity = single_sku_count,
                                        remarks = new StringBuilder()
                                    });
                                }

                                // 自动凑moq
                                if (item_rule.has_collect)
                                {
                                    remarks.AppendLine($"自动凑moq");

                                    //查询商品moq
                                    var moq = conn.QuerySingleOrDefault<int?>(" select max(moq) from dc_base_sku where product_inner_code=@product_inner_code ", new { product_inner_code = itemGroup.Key.product_inner_code });
                                    if (moq == null)
                                    {
                                        remarks.AppendLine($"未查询到商品moq");
                                        throw new Exception("未查询到商品moq");
                                    }


                                    // 计算差额,补齐 moq
                                    var count_difference = moq - data_detailed_list.Sum(s => s.quantity);
                                    remarks.AppendLine($"补齐moq 差额 {count_difference}");
                                    if (count_difference > 0)
                                    {
                                        var count_difference_index = 0;
                                        while (count_difference > 0)
                                        {
                                            data_detailed_list[count_difference_index].quantity++;
                                            if (count_difference_index >= (data_detailed_list.Count - 1))
                                            {
                                                count_difference_index = 0;
                                            }
                                            else
                                            {
                                                count_difference_index++;
                                            }
                                            count_difference--;
                                        }
                                    }
                                }

                                foreach (var item in data_detailed_list)
                                {
                                    item.remarks.Append(remarks);
                                }
                            }
                            else // 下一个供应链长度
                            {
                                remarks.AppendLine($"根据供应链长度下单模式");

                                int? moq = 0;
                                if (item_rule.has_collect)
                                {
                                    //查询商品moq
                                    moq = conn.QuerySingleOrDefault<int?>(" select max(moq) from dc_base_sku where product_inner_code=@product_inner_code ", new { product_inner_code = itemGroup.Key.product_inner_code });
                                    if (moq == null)
                                    {
                                        remarks.AppendLine($"未查询到商品moq");
                                        throw new Exception("未查询到商品moq");
                                    }
                                }

                                foreach (var item in itemGroup.AsEnumerable())
                                {
                                    var item_order_detailed = new auto_first_order_detailed_dto
                                    {
                                        bailun_sku = item.bailun_sku,
                                        quantity = 0,
                                        remarks = new StringBuilder()
                                    };
                                    item_order_detailed.remarks.Append(remarks);

                                    // 查询交期
                                    max_delivery = null;
                                    if ("国内仓".Equals(item_warehouse.hq_type))
                                    {
                                        max_delivery = conn.QuerySingleOrDefault<int?>(" select max(supplier_delivery) from dc_base_sku where bailun_sku=@bailun_sku ", new { bailun_sku = item.bailun_sku });
                                    }
                                    else
                                    {
                                        max_delivery = conn.QuerySingleOrDefault<int?>(" select max(supplier_delivery + `transfer_delivery`) from dc_base_sku where bailun_sku=@bailun_sku ", new { bailun_sku = item.bailun_sku });
                                    }
                                    if (max_delivery == null)
                                    {
                                        item_order_detailed.remarks.AppendLine($"未查询到交期");
                                        continue;
                                    }
                                    item_order_detailed.remarks.AppendLine($"供应链交期 {max_delivery} ");

                                    // 查询sku日均销量
                                    var skuSalesList = GetSkuSales(new List<string> { item.bailun_sku });

                                    // 计算预计下单数量
                                    var sum_quantity = max_delivery.Value * (skuSalesList[0].averageSales * item_rule.sales_ratio);
                                    item_order_detailed.remarks.AppendLine($"经过计算，预计下单数 {sum_quantity} =  （供应链交期 {max_delivery} ）* （日均销量 {skuSalesList[0].averageSales}）* （配置的日均销量比例 {item_rule.sales_ratio}）");
                                    item_order_detailed.quantity = sum_quantity;

                                    data_detailed_list.Add(item_order_detailed);



                                }

                                var moqSb = new StringBuilder();
                                if (item_rule.has_collect)
                                {
                                    moqSb.AppendLine("开始拼凑moq");

                                    moqSb.AppendLine($" 商品moq 个数 {moq} ");

                                    if (data_detailed_list.Sum(s => s.quantity ) < moq)
                                    {
                                        var demandQuantity = moq - data_detailed_list.Sum(s => s.quantity) ;
                                        moqSb.AppendLine($" 当前需求数量 个数 {data_detailed_list.Sum(s => s.quantity) } ");

                                        int tempIndex = 0;
                                        while (true)
                                        {
                                            if (demandQuantity <= 0)
                                            {
                                                break;
                                            }
                                            if (data_detailed_list.Count  <= tempIndex)
                                            {
                                                throw new Exception($" 操蛋 {data_detailed_list.Count} - {tempIndex} ");
                                            }
                                            data_detailed_list[tempIndex].quantity++;
                                            data_detailed_list[tempIndex].share_quantity++;
                                            demandQuantity--;
                                           
                                            if (data_detailed_list.Count-1 <= tempIndex)
                                            {
                                                tempIndex = 0;
                                            }
                                            else
                                            {
                                                tempIndex++;
                                            }
                                        }

                                        foreach (var item in data_detailed_list)
                                        {
                                            item.remarks.AppendLine($"累积为商品moq 分摊 {item.share_quantity} 个，当前预计下单 {item.quantity} ");
                                        }
                                    }
                                    else
                                    {
                                        moqSb.AppendLine("总需求已经大于moq ，跳过该步骤");
                                    }

                                }
                            }

                           

                            foreach (var item in data_detailed_list)
                            {
                                conn.Insert(new dc_auto_purchase_advise_detailed
                                {
                                    bailun_sku = item.bailun_sku,
                                    warehouse_code = itemGroup.Key.warehouse_code,
                                    quantity_init_advise = item.quantity,
                                    quantity_actual = item.quantity,
                                    main_id = purchase_advise_id,
                                    forecast_formula = "",
                                    fit_forecast_formula = "",
                                    ispush = 0,
                                    type = 4,
                                    product_inner_code = itemGroup.Key.product_inner_code,
                                    good_sku_codes = "",
                                    purchase_type_jit = 3,
                                    remarks = item.remarks.ToString(),
                                    turnover_days = max_delivery ?? 0,
                                }, t);
                            }

                            //所有需求标记为已下单
                            conn.Execute(" update dc_auto_first_order_sku set order_time=@order_time where id in @ids ", new { ids = itemGroup.Select(s => s.id), order_time = DateTime.Now }, t);

                        }
                        catch (Exception ex)
                        {
                            remarks.AppendLine(ex.Message);
                            var is_error = itemGroup.Max(s => s.is_error) + 1;
                            var goods_sku = $"【商品sku】:【{itemGroup.Key.product_inner_code}】【{itemGroup.Key.warehouse_code}】";
                            conn.Execute(" update dc_auto_first_order_sku set is_error=@is_error where id in @ids ", new { ids = itemGroup.Select(s => s.id), is_error = is_error }, t);
                            err_count = true;
                            conn.Insert(new dc_task_error_log
                            {
                                message = goods_sku + ex.Message,
                                stack_trace = ex.StackTrace,
                                task_name = "auto_first_order_GeneratePurchaseAdvise",
                                date = DateTime.Now
                            }, t);
                            QiYeJiQiRenHelper.QiYeJiQiRenMsPush(new QiYeJiQiRenHelper.QiYeJiQiRenMsDto { msgtype = "text", text = new QiYeJiQiRenHelper.QiYeJiQiRenMsDto.text_dto { content = " 查收异常消息： " + goods_sku + ex.Message } },
                                "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=0ff68206-f585-4d1e-824b-630c473978d8");
                            foreach (var item in itemGroup.ToList())
                            {
                                conn.Insert(new dc_auto_purchase_advise_detailed
                                {
                                    bailun_sku = item.bailun_sku,
                                    warehouse_code = itemGroup.Key.warehouse_code,
                                    quantity_init_advise = 0,
                                    quantity_actual = 0,
                                    main_id = purchase_advise_id,
                                    forecast_formula = "",
                                    fit_forecast_formula = "",
                                    ispush = 0,
                                    type = 4,
                                    product_inner_code = itemGroup.Key.product_inner_code,
                                    good_sku_codes = "",
                                    purchase_type_jit = 3,
                                    remarks = remarks.ToString()
                                }, t); ;
                            }
                        }

                    }




                    t.Commit();
                }
            }

        }

        /// <summary>
        /// 获取sku销量
        /// </summary>
        public static List<pds_sku_sales_dto> GetSkuSales(List<string> skus)
        {
            string url = ConfigHelper.GetValue("postaveragesales");
            string resultStr = HttpHelper.Request(url, RequestType.POST, skus.ToJson(), timeout: 1000 * 60 * 60 * 24, entype: "application/json");
            var result = resultStr.ToObj<List<pds_sku_sales_dto>>();
            if (result == null)
            {
                throw new Exception("pds系统异常： 未获取到sku销量数据");
            }
            return result;
        }
    }
}
