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

namespace AutoTurnOver.DB
{
    public class base_sku_dao : connectionHelper
    {
        public static Page<dc_base_sku> GetPage(dc_base_sku_search_dto search)
        {
            var sql = " select t1.* from dc_base_sku as t1  where 1=1 ";
            DynamicParameters parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(search.bailun_sku))
            {
                sql += " and t1.bailun_sku = @bailun_sku ";
                parameters.Add("bailun_sku", search.bailun_sku);
            }
            if (!string.IsNullOrWhiteSpace(search.buyer_name))
            {
                sql += " and t1.buyer_name = @buyer_name ";
                parameters.Add("buyer_name", search.buyer_name);
            }
            if (!string.IsNullOrWhiteSpace(search.product_inner_code))
            {
                sql += " and t1.product_inner_code = @product_inner_code ";
                parameters.Add("product_inner_code", search.product_inner_code);
            }
            if (!string.IsNullOrWhiteSpace(search.product_code))
            {
                sql += " and t1.product_code = @product_code ";
                parameters.Add("product_code", search.product_code);
            }

            return _connection.Page<dc_base_sku>(sql, search, parameters);
        }

        public static List<dc_base_sku_character> CharacterAll()
        {
            return _connection.Query<dc_base_sku_character>(" select * from dc_base_sku_character ").ToList();
        }

        /// <summary>
        /// 同步sku特性
        /// </summary>
        public static void SynchroSkuCharacter()
        {
            var task_name = $"SynchroSkuCharacter_v1";
            // 查询最后一次成功抓取的记录
            var last_task_synchro_log = _connection.QuerySingleOrDefault<task_synchro_log>(" select  * from task_synchro_log where task_name=@task_name and status=1 order by end_time desc limit 1 ", new { task_name = task_name });

            var new_task_synchro_log = new task_synchro_log
            {
                create_date = DateTime.Now,
                end_time = DateTime.Now.AddMinutes(-2),
                status = 0,
                task_name = task_name
            };

            if (last_task_synchro_log != null)
            {
                new_task_synchro_log.start_time = last_task_synchro_log.end_time.AddMinutes(-1);
            }
            else
            {
                new_task_synchro_log.start_time = new DateTime(2016,1,1);
            }

            new_task_synchro_log.id = _connection.Insert(new_task_synchro_log) ?? 0;
            int count = 0;
            int page = 1;
            int rows = 300;
            var character_list = CharacterAll();
            var now = DateTime.Now;
            while (true)
            {
                var datas = _connection.Query<string>($@" select bailun_sku from dc_base_sku as t1  
where t1.gmt_modified>=@btime and t1.gmt_modified <=@etime 
order by t1.gmt_modified asc 
 limit {(page - 1) * rows},{rows}  ", new { btime = new_task_synchro_log.start_time, etime = new_task_synchro_log.end_time }, commandTimeout: 0).ToList();
                if (datas.Count <= 0) { break; }
                page++;
                // 查询所有sku的特性
                var characters = ApiUtility.GetSkuCharacterBailun(datas);
                foreach (var item in datas)
                {
                    if (!string.IsNullOrWhiteSpace(item))
                    {
                        var itemCharacters = characters.FirstOrDefault(s => item.Equals(s.sku, StringComparison.InvariantCultureIgnoreCase));
                        var character_names = ((itemCharacters == null || string.IsNullOrWhiteSpace(itemCharacters.productCharacter)) ? "" : string.Join(",",
                            itemCharacters.productCharacter.Split(',').Select(s => (character_list.FirstOrDefault(c => c.key == s) ?? new dc_base_sku_character()).name)
                            ));
                        _connection.Execute(" update dc_base_sku set character_names=@character_names where bailun_sku=@bailun_sku ",new {
                            bailun_sku = item,
                            character_names  = character_names
                        });

                    }

                }

            }


            new_task_synchro_log.count = count;
            new_task_synchro_log.status = 1;
            _connection.Update(new_task_synchro_log);
        }

        /// <summary>
        /// 同步sku特性
        /// </summary>
        public static void SynchroSkuCharacterSupplement()
        {
            
            // 取100条
            var datas = _connection.Query<string>($@" select bailun_sku from dc_base_sku as t1  
where t1.character_names is null
 limit 100  ", commandTimeout: 0).ToList();
            if (datas.Count <= 0) { return; }
            var character_list = CharacterAll();
            // 查询所有sku的特性
            var characters = ApiUtility.GetSkuCharacterBailun(datas);
            foreach (var item in datas)
            {
                if (!string.IsNullOrWhiteSpace(item))
                {
                    var itemCharacters = characters.FirstOrDefault(s => item.Equals(s.sku, StringComparison.InvariantCultureIgnoreCase));
                    var character_names = ((itemCharacters == null || string.IsNullOrWhiteSpace(itemCharacters.productCharacter)) ? "" : string.Join(",",
                        itemCharacters.productCharacter.Split(',').Select(s => (character_list.FirstOrDefault(c => c.key == s) ?? new dc_base_sku_character()).name)
                        ));
                    _connection.Execute(" update dc_base_sku set character_names=@character_names where bailun_sku=@bailun_sku ", new
                    {
                        bailun_sku = item,
                        character_names = character_names
                    });

                }

            }
        }


        /// <summary>
        /// 抓取所有特性
        /// </summary>
        public static void SynchroCharacter()
        {
            var conn = _connection;
            var datas = ApiUtility.GetCharacterBailun();
            if (datas != null && datas.Count >= 1)
            {
                foreach (var item in datas)
                {
                    var db_data = new dc_base_sku_character()
                    {
                        key = item.value,
                        name = item.text,
                        status = item.status
                    };
                    db_data.id = conn.QueryFirstOrDefault<int?>(" select id from dc_base_sku_character where `key`=@key ", new { db_data.key }) ?? 0;
                    if (db_data.id > 0)
                    {
                        conn.Update(db_data);
                    }
                    else
                    {
                        conn.Insert(db_data);
                    }
                }
            }
        }
    }
}
