using AutoTurnOver.DB.Base;
using AutoTurnOver.Models;
using AutoTurnOver.Models.Base;
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AutoTurnOver.DB
{
    public class dc_auto_stock_monitor_dao : connectionHelper
    {
        public static Page<dc_auto_stock_monitor_dto> GetPage(dc_auto_stock_monitor_search_dto search)
        {
            string sql = @" select
t1.bailun_sku,
t1.product_code,
t1.sku_title_cn,
t1.develop_time,
t1.push_time,
t1.product_type_desc,
t1.source,
t1.unit_price,
t1.buyer_name,
t1.weight,
( case when t5.bailun_sku is not null then '监控中' else '停止监控' end ) as 'monitor_status',
t2.*,
t1.`status` 
from dc_view_stock_monitor_sku as tv
inner join dc_base_sku as t1 on t1.bailun_sku = tv.bailun_sku 
left join dc_auto_stock_monitor as t2 on t1.bailun_sku = t2.bailun_sku
left join dc_auto_monitor_sku as t5 on t1.bailun_sku = t5.bailun_sku 
where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if(search.start_date!=null && search.end_date != null)
                {
                    sql += "  and EXISTS (select * from dc_base_stock_record as t_re where tv.bailun_sku = t_re.bailun_sku and t_re.stock_time>=@start_date and t_re.stock_time<=@end_date and has_transfer=1 ) ";
                    parameters.Add("start_date", search.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("end_date", search.start_date.Value.ToString("yyyy-MM-dd 23:59:59"));
                }
                else if (search.start_date != null)
                {
                    sql += "  and EXISTS (select * from dc_base_stock_record as t_re where tv.bailun_sku = t_re.bailun_sku and t_re.stock_time>=@start_date and has_transfer=1 ) ";
                    parameters.Add("start_date", search.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                }else if (search.end_date != null)
                {
                    sql += "  and EXISTS (select * from dc_base_stock_record as t_re where tv.bailun_sku = t_re.bailun_sku  and t_re.stock_time<=@end_date and has_transfer=1 ) ";
                    parameters.Add("end_date", search.start_date.Value.ToString("yyyy-MM-dd 23:59:59"));
                }
                if (search.bailun_sku != null)
                {
                    sql += " and t1.bailun_sku=@bailun_sku ";
                    parameters.Add("bailun_sku", search.bailun_sku);
                }
                if (search.type==1)
                {
                    sql += " and t2.haiwai_us_usable_stock>0 ";
                }else if(search.type == 2)
                {
                    sql += " and t2.haiwai_uk_usable_stock>0 ";
                }else if(search.type == 3)
                {
                    sql += " and t2.guangzhou01_transfer_stock<=0 ";
                }else if(search.type == 4)
                {
                    sql += " and t2.haiwai_us_transfer<=0 ";
                }else if(search.type == 5)
                {
                    sql += " and t2.haiwai_uk_transfer<=0 ";
                }
            }
            return _connection.Page<dc_auto_stock_monitor_dto>(sql, search, parameters);
        }
        
        /// <summary>
        /// listing 状态监控
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        public static Page<listing_status_view_dto> GetListingStatusPage(listing_status_view_search_dto search)
        {
            string sql = @" select
tw.hq_type,
tw.area_name,
tw.warehouse_name,
tw.warehouse_code,
t1.bailun_sku as 'base_bailun_sku',
t1.sku_title_cn,
t1.product_code,
t1.product_inner_code,
t1.buyer_name,
t1.brand,
t1.weight,
t1.unit_price,
t1.height,
(case when t8.id is null then '未侵权' else  '侵权' end) as 'has_tort',
( case when t5.bailun_sku is not null then '监控中' else '停止监控' end ) as 'monitor_status',
t9.history_sevenday_sales,
tdb.usable_stock,
(tdb.usable_stock * t1.unit_price) as 'usable_stock_amount',
ifnull(t10.quantity_purchase,0) as 'quantity_purchase',
ifnull(t10.quantity_transfer,0) as 'quantity_transfer',
t1.source,
t11.*
from dc_base_stock as tdb
left join dc_base_warehouse as tw on tdb.warehouse_code = tw.warehouse_code
inner join dc_base_sku as t1 on tdb.bailun_sku = t1.bailun_sku 
left join dc_auto_monitor_sku as t5 on t1.bailun_sku = t5.bailun_sku 
left join dc_base_tort as t8  on t1.bailun_sku = t8.bailun_sku
left join dc_auto_turnover as t9 on tdb.bailun_sku = t9.bailun_sku and tdb.warehouse_code = t9.warehouse_code
left join dc_mid_transit as t10 on tdb.bailun_sku = t10.bailun_sku and tdb.warehouse_code = t10.warehouse_code
left join dc_base_listing_status_sum_view as t11 on tdb.bailun_sku = t11.bailun_sku
where tdb.usable_stock>0 ";
            DynamicParameters parameters = new DynamicParameters();
            if (search != null)
            {
                if(search.start_date!=null && search.end_date != null)
                {
                    sql += "  and EXISTS (select * from dc_base_stock_record as t_re where tdb.bailun_sku = t_re.bailun_sku and t_re.stock_time>=@start_date and t_re.stock_time<=@end_date and has_transfer=1 ) ";
                    parameters.Add("start_date", search.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                    parameters.Add("end_date", search.start_date.Value.ToString("yyyy-MM-dd 23:59:59"));
                }
                else if (search.start_date != null)
                {
                    sql += "  and EXISTS (select * from dc_base_stock_record as t_re where tdb.bailun_sku = t_re.bailun_sku and tdb.warehouse_code = t_re.warehouse_code and  t_re.stock_time>=@start_date ) ";
                    parameters.Add("start_date", search.start_date.Value.ToString("yyyy-MM-dd 00:00:00"));
                }else if (search.end_date != null)
                {
                    sql += "  and EXISTS (select * from dc_base_stock_record as t_re where tdb.bailun_sku = t_re.bailun_sku and tdb.warehouse_code = t_re.warehouse_code and t_re.stock_time<=@end_date ) ";
                    parameters.Add("end_date", search.start_date.Value.ToString("yyyy-MM-dd 23:59:59"));
                }
                if(!string.IsNullOrWhiteSpace(search.brand))
                {
                    sql += " and t1.brand=@brand ";
                    parameters.Add("brand",search.brand);
                }
                if (!string.IsNullOrEmpty(search.warehouse_code))
                {
                    sql += " and tdb.warehouse_code='" + search.warehouse_code + "'";
                }
                else
                {
                    if (!string.IsNullOrWhiteSpace(search.warehousetype))
                    {
                        sql += " and tw.hq_type=" + $"'{search.warehousetype}'";
                    }
                    if (search.warehousearea > 0)
                    {
                        sql += " and tw.area_id=" + search.warehousearea;
                    }
                }
                if (search.has_tort == 1)
                {
                    sql += " and t8.id is not null ";
                }
                else if(search.has_tort == 0)
                {
                    sql += " and t8.id is null ";
                }
                if (search.source>0)
                {
                    sql += " and t1.source =@source ";
                    parameters.Add("source", search.source);
                } 
                if (!string.IsNullOrWhiteSpace(search.product_code))
                {
                    sql += " and t1.product_inner_code =@product_inner_code ";
                    parameters.Add("product_inner_code", search.product_code);
                }
                if (search.listing_status == 1)
                {
                    // 可上架
                    var fields = GetShowField(search.warehouse_code);
                    sql += $"and tdb.usable_stock>0 and  ( {string.Join(" or ", fields.Select(v=>$" ifnull(t11.{v},0) <= 0 "))} )";
                }
                if (search.listing_status == 2)
                {
                    // 无在线
                    var fields = GetShowField(search.warehouse_code);
                    sql += $"and tdb.usable_stock>0 and  ( {string.Join(" and ", fields.Select(v => $" ifnull(t11.{v},0) <= 0 "))} )";
                }
            }
            return _connection.Page<listing_status_view_dto>(sql, search, parameters);
        }

        /// <summary>
        /// 查询要显示的字段
        /// </summary>
        /// <param name="warehouse_code"></param>
        /// <returns></returns>
        public static List<string> GetShowField(string warehouse_code)
        {
            var full = new List<string> { "ebay_uk_china" , "ebay_uk_abroad", "ebay_us_china", "ebay_us_abroad", "wish_china", "wish_abroad", "amazon_uk_china", "amazon_uk_abroad", "amazon_us_china", "amazon_us_abroad"
            ,"aliexpress_china","aliexpress_abroad","walmart_china","walmart_abroad","sears_china","sears_abroad","ebay_au_china","ebay_au_abroad","ebay_de_china","ebay_de_abroad","ebay_es_china","ebay_es_abroad",
            "ebay_fr_china","ebay_fr_abroad","ebay_it_china","ebay_it_abroad","amazon_au_china","amazon_au_abroad","amazon_de_china","amazon_de_abroad","amazon_es_china","amazon_es_abroad","amazon_fr_china",
            "amazon_fr_abroad","amazon_it_china","amazon_it_abroad","amazon_jp_china","amazon_jp_abroad","amazon_na_china","amazon_na_abroad","amazon_ca_china","amazon_ca_abroad"
            };
            if (string.IsNullOrWhiteSpace(warehouse_code))
            {
                return full;
            }
            var wData = _connection.QuerySingleOrDefault<dc_base_warehouse>(" select * from dc_base_warehouse where warehouse_code=@warehouse_code ",new { warehouse_code  = warehouse_code });
            if (wData.hq_type == "国内仓")
            {
                return new List<string>() { "ebay_uk_china", "ebay_us_china", "wish_uk_china", "amazon_uk_china", "amazon_us_china", "aliexpress_uk_china", "aliexpress_us_china", "walmart_us_china", "sears_us_china" };
            }
            else
            {
                if (wData.area_name == "英国")
                {
                    return new List<string> { "ebay_uk_abroad" , "amazon_uk_abroad"  ,"aliexpress_abroad" };
                }else if (wData.area_name == "美国")
                {
                    return new List<string> { "ebay_us_abroad", "amazon_us_abroad", "aliexpress_abroad" , "walmart_abroad", "sears_abroad" };
                }
                else
                {
                    return full;
                }
            }

        }
    }
}
