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

namespace AutoTurnOver.DB
{
    /// <summary>
    /// 现金流预测
    /// </summary>
    public class report_cash_flow_forecast_dao : connectionHelper
    {
        /// <summary>
        /// 计算现金流
        /// </summary>
        public static void Calculation(MySqlConnection conn, dc_report_cash_flow_forecast_task task_data)
        {
            try
            {
                var now = DateTime.Now;
                conn.Execute(" delete from dc_report_cash_flow_forecast_task_log where task_id=@task_id  ", new { task_id = task_data.id });
                var logisticsList = ApiUtility.RealTimeShipLogisticsList();
                //先清理数据
                List<dc_report_cash_flow_forecast_task_log> logs = new List<dc_report_cash_flow_forecast_task_log>();
                List<dc_report_cash_flow_forecast_task_sale> sale_list = conn.Query<dc_report_cash_flow_forecast_task_sale>(" select * from dc_report_cash_flow_forecast_task_sale where task_id=@task_id ", new { task_id = task_data.id }).AsList();

                if (sale_list == null || sale_list.Count <= 0)
                {
                    throw new Exception(" 未配置销量 ");
                }
                List<dc_report_cash_flow_config> configs = conn.Query<dc_report_cash_flow_config>(" select * from dc_report_cash_flow_config ").AsList();
                List<dc_report_logistics_company_config_dto> logistics_company_list = conn.Query<dc_report_logistics_company_config_dto>(" select * from dc_report_logistics_company_config ").AsList();
                List<dc_auto_config_stock_up_days> up_days = conn.Query<dc_auto_config_stock_up_days>(" select * from dc_auto_config_stock_up_days ").ToList();
                List<dc_auto_config_safe_inventory> safe_inventorys = conn.Query<dc_auto_config_safe_inventory>(" select * from dc_auto_config_safe_inventory ").ToList();

                // 模拟跑周转模型
                List<invented_turnover_model> turnover_list = GetTurnoverModels(task_data, sale_list, MatchingUpDays(up_days, task_data.warehouse_code), MatchingSafeInventorys(safe_inventorys, task_data.warehouse_code));


                // 模拟销量
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = (int)dc_report_cash_flow_log_data_type_enum.销售金额,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val * task_data.sale_unit_price_cny,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = report_cash_flow_dao.CalculationPayTime(configs, s.date, (int)dc_report_cash_flow_log_data_type_enum.销售金额, task_data.platform_type, task_data.web_site)
                }));

                // 模拟销量
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = (int)dc_report_cash_flow_log_data_type_enum.销售数量,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟销售单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = report_cash_flow_dao.CalculationPayTime(configs, s.date, (int)dc_report_cash_flow_log_data_type_enum.销售数量, task_data.platform_type, task_data.web_site)
                }));

                if (!"FBA仓".Equals(task_data.warehouse_type, StringComparison.OrdinalIgnoreCase))
                {
                    var index = 0;
                    // 模拟尾程费
                    foreach (var s in turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.销量))
                    {
                        var temp_item = new dc_report_cash_flow_forecast_task_log()
                        {
                            warehouse_code = task_data.warehouse_code,
                            bailun_sku = task_data.bailun_sku,
                            data_type = (int)dc_report_cash_flow_log_data_type_enum.尾程费用,
                            is_delete = 0,
                            item = "",
                            item_no = $"虚拟尾程费用 - {task_data.id}-{s.date_str}-{index}",
                            no = $"虚拟尾程费用 - {task_data.id}-{s.date_str}-{index}",
                            occur_time = s.date,
                            pay_type = (int)dc_report_cash_flow_log_pay_type_enum.实时,
                            remarks = "模拟跑单",
                            task_id = task_data.id,
                            platform_type = task_data.platform_type,
                            val = s.val * ApiUtility.GetFilterLogisticsAssignLine(new Models.ApiDto.api_logistic_query_input_dto
                            {
                                endCountries = task_data.web_site,
                                high = task_data.high,
                                LineCodes = task_data.tail_logistics_code,
                                Long = task_data.@long,
                                platform = task_data.platform_type,
                                Site = task_data.web_site,
                                startPoint = "国内仓".Equals(task_data.warehouse_type, StringComparison.OrdinalIgnoreCase) ? "1" : "2",
                                warehouseNo = task_data.warehouse_code,
                                weightKg = task_data.weight_kg,
                                width = task_data.width
                            }).FootFee,
                            update_time = now,
                            web_site = task_data.web_site,
                            pay_time_year_month_no  ="",
                            occur_time_year_month_no = ""
                        };
                        string remarks = null;
                        temp_item.pay_time = report_cash_flow_dao.CalculationLogisticsPayTime(logisticsList, logistics_company_list, s.date, task_data.tail_logistics_code, out remarks);
                        temp_item.remarks = remarks;
                        index++;
                        logs.Add(temp_item);
                    }
              
                }

                // 模拟下采购单
                logs.AddRange(turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.最终采购数量).Select((s, index) => new dc_report_cash_flow_forecast_task_log()
                {
                    warehouse_code = task_data.warehouse_code,
                    bailun_sku = task_data.bailun_sku,
                    data_type = index == 0 ? (int)dc_report_cash_flow_log_data_type_enum.新增采购费用_首单 : (int)dc_report_cash_flow_log_data_type_enum.新增采购费用,
                    is_delete = 0,
                    item = "",
                    item_no = $"虚拟采购单 - {task_data.id}-{s.date_str}-{index}",
                    no = $"虚拟采购单 - {task_data.id}-{s.date_str}-{index}",
                    occur_time = s.date,
                    pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                    remarks = "模拟跑单",
                    task_id = task_data.id,
                    platform_type = task_data.platform_type,
                    val = s.val * task_data.buy_unit_price_cny,
                    update_time = now,
                    web_site = task_data.web_site,
                    pay_time_year_month_no = "",
                    occur_time_year_month_no = "",
                    pay_time = (task_data.buy_pay_type == 2 ? s.date.AddDays(task_data.buy_pay_days) : s.date)
                }));

                if (!"国内仓".Equals(task_data.warehouse_type, StringComparison.OrdinalIgnoreCase))
                {
                    var index = 0;
                    // 模拟尾程费
                    foreach (var s in turnover_list.Where(s => s.type == (int)invented_turnover_model.type_enum.调拨下单))
                    {
                        var temp_item = new dc_report_cash_flow_forecast_task_log()
                        {
                            warehouse_code = task_data.warehouse_code,
                            bailun_sku = task_data.bailun_sku,
                            data_type = (index==0? (int)dc_report_cash_flow_log_data_type_enum.新增头程费用_首单: (int)dc_report_cash_flow_log_data_type_enum.新增头程费用),
                            is_delete = 0,
                            item = "",
                            item_no = $"虚拟头程费用 - {task_data.id}-{s.date_str}-{index}",
                            no = $"虚拟头程费用 - {task_data.id}-{s.date_str}-{index}",
                            occur_time = s.date,
                            pay_type = (int)dc_report_cash_flow_log_pay_type_enum.后付,
                            remarks = "模拟跑单",
                            task_id = task_data.id,
                            platform_type = task_data.platform_type,
                            val = s.val * ApiUtility.GetFilterLogisticsAssignLine(new Models.ApiDto.api_logistic_query_input_dto
                            {
                                endCountries = task_data.web_site,
                                high = task_data.high,
                                LineCodes = task_data.tail_logistics_code,
                                Long = task_data.@long,
                                platform = task_data.platform_type,
                                Site = task_data.web_site,
                                startPoint = "3",
                                warehouseNo = task_data.warehouse_code,
                                weightKg = task_data.weight_kg,
                                width = task_data.width
                            }).FootFee,
                            update_time = now,
                            web_site = task_data.web_site,
                            pay_time_year_month_no = "",
                            occur_time_year_month_no = ""
                        };
                        string remarks = null;
                        temp_item.pay_time = report_cash_flow_dao.CalculationLogisticsPayTime(logisticsList, logistics_company_list, s.date, task_data.transfer_logistics_code, out remarks);
                        temp_item.remarks = remarks;
                        index++;
                        logs.Add(temp_item);
                    }
                }

                if(logs!=null && logs.Count >= 1)
                {
                    foreach (var item in logs)
                    {
                        conn.Insert(item);
                    }
                }
            }
            catch (Exception ex)
            {
                task_data.status = 3;
                task_data.error_message = ex.Message;
                task_data.error_stack_trace = ex.StackTrace;
                conn.Update(task_data);
            }
        }

        /// <summary>
        /// 预测周转模型
        /// </summary>
        public static List<invented_turnover_model> GetTurnoverModels(dc_report_cash_flow_forecast_task task_data, List<dc_report_cash_flow_forecast_task_sale> sale_list, dc_auto_config_stock_up_days stock_up_days, dc_auto_config_safe_inventory inventory_data)
        {
            // 最大预测日期，预测销量 - 供应商交期
            var delivery = task_data.delivery;
            if (task_data.warehouse_type != "国内仓")
            {
                delivery += task_data.transfer_days;
            }

            sale_list = sale_list.OrderBy(s => s.date).ToList();
            // 预测周转数据
            List<invented_turnover_model> turnover_list = new List<invented_turnover_model>();
            turnover_list.AddRange(sale_list.Select(s => new invented_turnover_model { date = s.date, val = s.val, type = (int)invented_turnover_model.type_enum.销量 }));
            // 下首单 (采购单)
            // 取最早的预测销量的一天
            var min_date_sale = sale_list.FirstOrDefault();
            var init_purchase_quantity = min_date_sale.val;
            // 考虑备货天数
            var stock_up_day_date = min_date_sale.date.AddDays(stock_up_days.stock_up_days - 1);
            if (stock_up_day_date.ToDayHome() > min_date_sale.date.ToDayHome())
            {
                // 加备货天数
                init_purchase_quantity += sale_list.Where(s => s.date > min_date_sale.date && s.date <= stock_up_day_date.ToDayEnd()).Sum(s => s.val);
            }
            // 安全库存
            var inventory_stock = (inventory_data.param * min_date_sale.val);
            // 考虑安全库存
            init_purchase_quantity += inventory_stock;
            // 考虑moq
            var final_purchase_quantity = Math.Max(init_purchase_quantity, task_data.moq);
            if (task_data.warehouse_type != "国内仓")
            {
                turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.调拨下单, date = min_date_sale.date.AddDays(task_data.delivery) });
            }
            turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.最终采购数量, date = min_date_sale.date.AddDays(0 - (delivery + 1)) });
            // 会在开卖的前一天到货
            turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.入库数量, date = min_date_sale.date.AddDays(-1) });
            turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.可用库存, date = min_date_sale.date.AddDays(-1) });


            var max_forecast_date = sale_list.Max(s => s.date).AddDays(delivery);

            var this_time = min_date_sale.date;
            while (this_time.ToDayHome() <= max_forecast_date.ToDayHome())
            {
                // 先查询【昨天的可用库存 + 今日入库】
                var yesterday_data_str = this_time.AddDays(-1).ToString("yyyy-MM-dd");
                var today_data_str = this_time.ToString("yyyy-MM-dd");
                decimal yesterday_stock = (turnover_list.Where(s => s.date_str == yesterday_data_str && s.type == (int)invented_turnover_model.type_enum.可用库存).FirstOrDefault() ?? new invented_turnover_model()).val;
                yesterday_stock += (turnover_list.Where(s => s.date_str == today_data_str && s.type == (int)invented_turnover_model.type_enum.入库数量).FirstOrDefault() ?? new invented_turnover_model()).val;

                // 减去销量等于【今日的可用库存】
                var today_sale = (turnover_list.Where(s => s.date_str == today_data_str && s.type == (int)invented_turnover_model.type_enum.销量).FirstOrDefault() ?? new invented_turnover_model()).val;
                var today_stock = yesterday_stock - today_sale;
                turnover_list.Add(new invented_turnover_model { val = today_stock, type = (int)invented_turnover_model.type_enum.可用库存, date = this_time });
                // 计算跟安全库存的差值
                if (inventory_stock - today_stock > 0)
                {
                    //准备采购
                    // 1. 补齐安全库存
                    var init_p = inventory_stock - today_stock;
                    // 2. 考虑备货天数
                    var this_stock_up_day_date = this_time.AddDays(stock_up_days.stock_up_days - 1);
                    if (this_stock_up_day_date.ToDayHome() > this_time.ToDayHome())
                    {
                        // 加备货天数
                        init_p += sale_list.Where(s => s.date > this_time && s.date <= this_stock_up_day_date.ToDayEnd()).Sum(s => s.val);
                    }
                    // 3. 考虑moq
                    // 考虑moq
                    var final_p = Math.Max(init_p, task_data.moq);
                    turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.最终采购数量, date = this_time });
                    // 加一个交期 等于 到货天数
                    turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.入库数量, date = this_time.AddDays(delivery) });
                    if (task_data.warehouse_type != "国内仓")
                    {
                        turnover_list.Add(new invented_turnover_model { val = final_purchase_quantity, type = (int)invented_turnover_model.type_enum.调拨下单, date = this_time.AddDays(task_data.delivery) });
                    }
                }

                this_time = this_time.AddDays(1);
            }

            return turnover_list;
        }

        /// <summary>
        /// 匹配备货天数模型
        /// </summary>
        /// <param name="up_days"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static dc_auto_config_stock_up_days MatchingUpDays(List<dc_auto_config_stock_up_days> up_days, string warehouse_code)
        {
            return (up_days.FirstOrDefault(s => s.warehouse_code == warehouse_code) ?? new dc_auto_config_stock_up_days()
            {
                warehouse_code = warehouse_code,
                stock_up_days = 1
            });
        }

        /// <summary>
        /// 匹配安全库存模型
        /// </summary>
        /// <param name="up_days"></param>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static dc_auto_config_safe_inventory MatchingSafeInventorys(List<dc_auto_config_safe_inventory> datas, string warehouse_code)
        {
            return (datas.FirstOrDefault(s => s.warehouse_code == warehouse_code) ?? new dc_auto_config_safe_inventory()
            {
                warehouse_code = warehouse_code,
                param = 1
            });
        }
    }
}
