using MySqlConnector;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zxd.Crm.Domain.Dto.AssignRule;
using Zxd.Crm.Domain.Impl.AssignRule;
using Zxd.Entity.Dncms;

namespace Zxd.Crm.Domain
{
    /// <summary>
    /// 客服分配规则
    /// </summary>
    public class AssignRuleDomain : IAssignRuleDomain
    {
        private readonly IBaseRepository<DncmsbaseDbContext> _cmsRepository;
        private readonly IMapper _mapper;

        public AssignRuleDomain(IBaseRepository<DncmsbaseDbContext> cmsRepository,
            IMapper mapper)
        {
            _cmsRepository = cmsRepository;
            _mapper = mapper;
        }

        /// <summary>
        /// 列表
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<PageResult<AssignRuleModel>> GetList(AssignRuleQueryDto dto)
        {
            var filter = new List<string>();
            var ps = new List<MySqlParameter>();
            var tableFilter = "";
            if (!string.IsNullOrWhiteSpace(dto.allsgids))
            {
                filter.Add($" t1.salegroupid  in( {dto.allsgids} )");
            }
            if (dto.UserId.HasValue)
            {
                filter.Add($" t1.inneruserid =  @userid ");
                ps.Add(new MySqlParameter("userid", dto.UserId));
            }
            if (dto.sgid.HasValue)
            {
                filter.Add($" t1.salegroupid = @sgid");
                ps.Add(new MySqlParameter("sgid", dto.sgid));
            }
            if (!string.IsNullOrWhiteSpace(dto.name))
            {
                filter.Add($" (t2.employee_name like @cname or t1.eid like @cname)");
                ps.Add(new MySqlParameter("cname", $"%{dto.name}%"));
            }
            if (dto.departmentId.HasValue)
            {
                tableFilter = $"{Environment.NewLine}JOIN employee_department_detail t3 on t3.eid = t1.eid";
                filter.Add($"t3.department_id = @deptid");
                filter.Add($"t3.is_deleted = 0");
                ps.Add(new MySqlParameter("deptid", dto.departmentId));
            }
            if (dto.Level.HasValue)
            {
                filter.Add($" t1.levelno = @level");
                ps.Add(new MySqlParameter("level", dto.Level));
            }
            if (dto.isvalid.HasValue)
            {
                filter.Add($" t1.isvalid = @isvalid");
                ps.Add(new MySqlParameter("isvalid", dto.isvalid));
            }
            var weworkUserJoinSql = " left join";
            if (!string.IsNullOrWhiteSpace(dto.AppUserId))
            {
                weworkUserJoinSql = " join ";
                filter.Add($" b.userid = @AppUserId");
                ps.Add(new MySqlParameter("AppUserId", dto.AppUserId));
            }
            if (!string.IsNullOrWhiteSpace(dto.NickName))
            {
                weworkUserJoinSql = " join ";
                filter.Add($" b.name like @NickName");
                ps.Add(new MySqlParameter("NickName", $"%{dto.NickName}%"));
            }
            if (!string.IsNullOrWhiteSpace(dto.ResId))
            {
                weworkUserJoinSql = " join ";
                filter.Add($" b.ResId = @ResId");
                ps.Add(new MySqlParameter("ResId", dto.ResId));
            }
            var where = "";
            if (filter.Count > 0)
            {
                where = $"{Environment.NewLine}where {string.Join(" and ", filter)}";
            }
            var fields = " t1.*,we.name as AppName,b.name as nickName,b.resid as Phone ";
            var tables = $@"AssignRules  t1  {tableFilter}
                        join db_company_base_conf.employee t2 on t1.eid =t2.employee_id
                        left join Wework we on t1.appid = we.appid
                        {weworkUserJoinSql} WeworkUser as b on t1.appid=b.appid and t1.userid=b.userid ";
            var sql = $"select count(1) from {tables}{where}";
            var total = await _cmsRepository.ExecuteSqlToCountLongAsync(sql, ps.ToArray());

            var skip = (dto.PageIndex - 1) * dto.PageSize;
            var take = dto.PageSize;
            sql = $"select {fields} from {tables}{where} order by t1.id desc limit {skip},{take}";
            var res = await _cmsRepository.ExecuteSqlToListAsync<AssignRuleModel>(sql, ps.ToArray());
            foreach (var item in res)
            {
                item.nickName = System.Web.HttpUtility.UrlDecode(item.nickName, System.Text.Encoding.UTF8);
            }
            return new PageResult<AssignRuleModel>(dto.PageIndex, dto.PageSize, Convert.ToInt32(total), res);
        }

        /// <summary>
        /// 创建编辑
        /// </summary>
        /// <param name="rule"></param>
        /// <returns></returns>
        public async Task<bool> Create(List<AssignRuleCreateDto> ruleList)
        {
            //校验 todo
            using var transaction = await _cmsRepository.BeginTransactionAsync();
            var group = ruleList.GroupBy(n => new { n.salegroupid, n.eid })
                .Select(n => new { n.Key.salegroupid, n.Key.eid, count = n.Count() }).ToList();
            if (group.Exists(n => n.count > 1))
            {
                throw new Exception($"同个规则组不能存在多条相同工号的记录，重复工号为【{string.Join(",", group.Where(n => n.count > 1).Select(n => n.eid))}】");
            }
            var eids = ruleList.Select(n => n.eid).Distinct().ToList();
            var gid = ruleList.FirstOrDefault().salegroupid;
            var dbList = await _cmsRepository.GetRepository<AssignRules>().Query().Where(n => n.salegroupid == gid && eids.Contains(n.eid)).ToListAsync();

            foreach (var rule in ruleList)
            {
                if (dbList.Exists(n => n.eid == rule.eid && n.salegroupid == gid))
                {
                    throw new Exception($"同个规则组不能存在多条相同工号的记录，重复工号为【{rule.eid}】");
                }
                var dbassign = _cmsRepository.GetRepository<Assign>().Query().FirstOrDefault(n => n.eid == rule.eid && n.salegroupid == rule.salegroupid);
                if (dbassign == null)
                {
                    var info = new Assign()
                    {
                        eid = rule.eid,
                        salegroupid = rule.salegroupid.Value,
                        allocations = 0,
                        quantity = 0,
                        todayquantity = 0,
                        tgallocations = 0,
                        tgtodayallocations = 0,
                        tgquantity = 0
                    };

                    await _cmsRepository.GetRepository<Assign>().InsertAsync(info);
                }
                AssignRules newrule = new AssignRules
                {
                    eid = rule.eid,
                    salegroupid = rule.salegroupid,
                    appid = rule.appid,
                    userid = rule.userid,
                    uname = rule.uname,
                    rate = rule.rate,
                    num = rule.num,
                    islimit = rule.islimit,
                    isvalid = rule.isvalid,
                    levelno = rule.levelno,
                    crmdeptid = rule.crmdeptid,
                    deptrate = rule.deptrate,
                    inneruserid = rule.inneruserid,
                    ctime = DateTime.Now,
                    utime = DateTime.Now,
                };
                await _cmsRepository.GetRepository<AssignRules>().InsertAsync(newrule);
            }

            await transaction.CommitAsync();
            return true;
        }

        /// <summary>
        /// 上下线
        /// </summary>
        /// <param name="dto"></param>
        /// <returns></returns>
        public async Task<bool> UpOrDowmAssignRule(AssignRuleUpOrDowmDto dto)
        {
            var ruleList = await _cmsRepository.GetRepository<AssignRules>().Query().Where(n => dto.ids.Contains(n.id)).ToListAsync();
            if (dto.type == 1)
            {
                foreach (var rule in ruleList)
                {
                    rule.isvalid = dto.isvalid;
                    rule.startTime = dto.startTime;
                    rule.endTime = dto.endTime;
                    rule.utime = DateTime.Now;
                }
            }
            else
            {
                foreach (var rule in ruleList)
                {
                    if (dto.islimit.HasValue)
                    {
                        rule.islimit = dto.islimit;
                    }
                    if (dto.num.HasValue)
                    {
                        rule.num = dto.num;
                    }
                    if (dto.levelno.HasValue)
                    {
                        rule.levelno = dto.levelno;
                    }
                    rule.utime = DateTime.Now;
                }
            }
            await _cmsRepository.GetRepository<AssignRules>().BatchUpdateAsync(ruleList);
            return true;
        }

        public async Task<bool> Delete(List<int> ids)
        {
            var ruleList = await _cmsRepository.GetRepository<AssignRules>().Query().Where(n => ids.Contains(n.id)).ToListAsync();
            await _cmsRepository.GetRepository<AssignRules>().BatchDeleteAsync(ruleList);
            return true;
        }

        public async Task<List<NotSetAssignRuleDto>> GetNotSetAssignList(AssignRuleNotSetQueryDto dto)
        {
            if (!dto.sgid.HasValue)
            {
                return new List<NotSetAssignRuleDto>();
            }
            var query = await _cmsRepository.GetRepository<AssignRules>().Query().Where(n => n.salegroupid == dto.sgid).Select(n => n.eid).ToListAsync();
            var wheresql = $" where  a.eid >0 and r.eid is null and a.assignstatus=1	and te.is_deleted =0";
            var sql = @$" select a.id,a.appid,a.userid,a.eid,b.avatar,te.employee_name as Uname,b.name as NickName,we.name as AppName from  WeworkUser2Eid as a

                         left join Wework we on a.appid = we.appid
                         left join WeworkUser as b on a.appid=b.appid and a.userid=b.userid
                         JOIN db_company_base_conf.employee AS te ON a.eid=te.employee_id
				         LEFT join AssignRules r on a.eid = r.eid and salegroupid = @salegroupid ";
            var ps = new List<MySqlParameter>
            {
                new MySqlParameter("salegroupid",dto.sgid)
            };
            if (!string.IsNullOrWhiteSpace(dto.uname))
            {
                wheresql += $" and (te.employee_name like @cname or te.employee_id like @cname)";
                ps.Add(new MySqlParameter("cname", $"%{dto.uname}%"));
            }
            if (dto.departmentId.HasValue)
            {
                sql += $" join employee_department_detail em on a.eid = em.eid and em.department_id = @departmentid ";
                ps.Add(new MySqlParameter("departmentid", dto.departmentId));
            }
            if (!string.IsNullOrWhiteSpace(dto.deptIds))
            {
                wheresql += $" and  a.deptid in ({dto.deptIds})";
            }
            sql += wheresql;
            sql += " order by a.eid desc";
            var res = await _cmsRepository.ExecuteSqlToListAsync<NotSetAssignRuleDto>(sql, ps.ToArray());
            return res;
        }

        /// <summary>
        /// 自动上下线
        /// </summary>
        /// <returns></returns>
        public async Task AutoUpAssignRule()
        {
            var nowTime = DateTime.Now;
            var upList = await _cmsRepository.GetRepository<AssignRules>().Query().Where(n => n.isvalid == 0 && n.startTime <= nowTime && (!n.endTime.HasValue || (n.endTime.HasValue && n.endTime >= nowTime))).ToListAsync();
            foreach (var up in upList)
            {
                up.isvalid = 1;
            }
            await _cmsRepository.GetRepository<AssignRules>().BatchUpdateAsync(upList, x => new { x.isvalid });
            var downList = await _cmsRepository.GetRepository<AssignRules>().Query().Where(n => n.isvalid == 1 && n.endTime.HasValue && n.endTime <= nowTime).ToListAsync();
            foreach (var up in downList)
            {
                up.isvalid = 0;
            }
            await _cmsRepository.GetRepository<AssignRules>().BatchUpdateAsync(downList, x => new { x.isvalid });
        }
    }
}