using Mall.Model.Entity.MarketingCenter;
using Mall.Model.Entity.User;
using Mall.Model.Extend.MarketingCenter;
using Mall.Model.Extend.User;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Mall.Repository.MarketingCenter
{
    public class RB_DiscountCouponRepository : BaseRepository<RB_DiscountCoupon>
    {
        /// <summary>
        /// 表名称
        /// </summary>
        public string TableName { get { return nameof(RB_DiscountCoupon); } }

        /// <summary>
        /// 表名称
        /// </summary>
        public string discountCouponTableName { get { return nameof(RB_Member_DiscountCoupon); } }

        /// <summary>
        /// 卡券信息列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_DiscountCoupon_Extend> GetDiscountCouponPageList(int pageIndex, int pageSize, out long rowCount, RB_DiscountCoupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append($" SELECT a.*,b.`Name` as  SmallShopName FROM {TableName} as A  LEFT JOIN rb_smallshops_info as b on a.SmallShopId=b.Id WHERE  a.{nameof(RB_DiscountCoupon_Extend.Status)}=0");
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_DiscountCoupon_Extend.Name)} like '%{query.Name}%'");
                }
            }
            builder.Append($"  order by  a.{nameof(RB_DiscountCoupon_Extend.ID)} desc");
            return GetPage<RB_DiscountCoupon_Extend>(pageIndex, pageSize, out rowCount, builder.ToString()).ToList();
        }


        /// <summary>
        /// 卡券信息列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_DiscountCoupon_Extend> GetDiscountCouponList(RB_DiscountCoupon_Extend query, bool IsSelectDel = false)
        {
            StringBuilder builder = new StringBuilder();
            if (IsSelectDel)
            {
                builder.Append($" SELECT * FROM {TableName} WHERE 1=1 ");
            }
            else
            {
                builder.Append($" SELECT * FROM {TableName} WHERE {nameof(RB_DiscountCoupon_Extend.Status)}=0 ");
            }
            if (query != null)
            {
                if (query.ID > 0)
                {
                    builder.Append($" AND {nameof(RB_DiscountCoupon_Extend.ID)}={query.ID}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND {nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND {nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND {nameof(RB_DiscountCoupon_Extend.Name)} like '%{query.Name}%'");
                }
                if (!string.IsNullOrEmpty(query.CouponIds))
                {
                    builder.Append($" AND {nameof(RB_DiscountCoupon_Extend.ID)} in({query.CouponIds})");
                }
            }
            return Get<RB_DiscountCoupon_Extend>(builder.ToString()).ToList();
        }




        /// <summary>
        /// 根据用户id和商品分类以及商品id获取未使用的优惠券信息
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_DiscountCoupon_Extend> GetNoUseCouponList(RB_DiscountCoupon_Extend query)
        {
            string where = "   and a.StartDate<NOW() and  a.EndDate>NOW()   and a.UseState=0  ";
            string goodsClassWhere = "";//分类
            string goodsWhere = "";//商品
            string allWhere = "";//通用
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    where += $" AND b.{nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}";
                }
                if (query.MallBaseId > 0)
                {
                    where += $" AND b.{nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.UserId > 0)
                {
                    where += $" AND a.{nameof(RB_DiscountCoupon_Extend.UserId)}={query.UserId}";
                }
                if (query.ProductList != null && query.ProductList.Any())
                {

                    List<int> allGoodsClass = new List<int>();

                    foreach (var item in query.ProductList)
                    {
                        if (!string.IsNullOrWhiteSpace(goodsWhere))
                        {
                            goodsWhere += $" or (c.ProductId = {item.ProductId} and a.MinConsumePrice<= {item.GoodsPrice})   ";
                        }
                        else
                        {
                            goodsWhere += $"  (c.ProductId = {item.ProductId} and a.MinConsumePrice<= {item.GoodsPrice})   ";
                        }
                        allGoodsClass.AddRange(item.GoodsClassId.ToList());
                    }
                    goodsWhere = " and (" + goodsWhere + ")";

                    //分类
                    var goodsClassList = allGoodsClass.GroupBy(x => x);
                    foreach (var item in goodsClassList)
                    {
                        decimal nowPrice = 0;

                        foreach (var classItem in query.ProductList)
                        {
                            if (classItem.GoodsClassId.Any(x => x == item.Key))
                            {
                                nowPrice += classItem.GoodsPrice;
                            }
                        }
                        if (!string.IsNullOrWhiteSpace(goodsClassWhere))
                        {
                            goodsClassWhere += $" or (c.ProductId = {item.Key} and b.MinConsumePrice<= {nowPrice})   ";
                        }
                        else
                        {
                            goodsClassWhere += $"  (c.ProductId = {item.Key} and b.MinConsumePrice<= {nowPrice})   ";
                        }
                    }
                    goodsClassWhere = " and (" + goodsClassWhere + ")";
                    decimal allPrice = query.ProductList.Sum(x => x.GoodsPrice);
                    allWhere = $" and  b.MinConsumePrice<= {allPrice} ";

                }
            }
            string sql = @$" SELECT a.ID as MemberCouponId,a.UserId,b.`Name`,b.CouponType,b.UseType,b.MinConsumePrice,b.DiscountsPrice,b.MaxDiscountsPrice,b.`Describe`,a.StartDate,a.EndDate,b.ID from rb_member_discountcoupon as a 
 LEFT JOIN  rb_discountcoupon as b on a.CouponId=b.ID
LEFT JOIN rb_discountcoupon_product as  c on c.DiscountCouponId=b.ID
where b.UseType=1 and c.`Status`=0 {where} {goodsClassWhere}
UNION ALL
SELECT a.ID as MemberCouponId,a.UserId,b.`Name`,b.CouponType,b.UseType,b.MinConsumePrice,b.DiscountsPrice,b.MaxDiscountsPrice,b.`Describe`,a.StartDate,a.EndDate,b.ID from rb_member_discountcoupon as a 
 LEFT JOIN  rb_discountcoupon as b on a.CouponId=b.ID
LEFT JOIN rb_discountcoupon_product as  c on c.DiscountCouponId=b.ID
where b.UseType=2 and c.`Status`=0  {where} {goodsWhere}
UNION all
SELECT a.ID as MemberCouponId,a.UserId,b.`Name`,b.CouponType,b.UseType,b.MinConsumePrice,b.DiscountsPrice,b.MaxDiscountsPrice,b.`Describe`,a.StartDate,a.EndDate,b.ID from rb_member_discountcoupon as a 
 LEFT JOIN  rb_discountcoupon as b on a.CouponId=b.ID
 where   b.UseType=3   {where} {allWhere}";
            return Get<RB_DiscountCoupon_Extend>(sql).ToList();
        }







        #region 小程序个人中心-领劵中心

        /// <summary>
        /// 领劵中心优惠券
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_DiscountCoupon_Extend> GetAllCouponList(int pageIndex, int pageSize, out long rowCount, RB_DiscountCoupon_Extend query)
        {
            string where = "   where a.`Status`=0   and a.PickupCenter=1";
            string userWhere = "";//分类
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    where += $" AND a.{nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}";

                    userWhere += $" AND {nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}";
                }
                if (query.MallBaseId > 0)
                {
                    where += $" AND a.{nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}";
                    userWhere += $" AND {nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.UserId > 0)
                {
                    userWhere += $" AND {nameof(RB_DiscountCoupon_Extend.UserId)}={query.UserId}";
                }
                if (query.SmallShopId > 0)
                {
                    where += $" AND ( a.{nameof(RB_DiscountCoupon_Extend.SmallShopId)}={query.SmallShopId} or ( a.{nameof(RB_DiscountCoupon_Extend.SmallShopId)}=0 and a.{nameof(RB_DiscountCoupon_Extend.IsZanYangUse)}=0))";

                }
                else
                {
                    where += $" AND a.{nameof(RB_DiscountCoupon_Extend.SmallShopId)}=0";
                }
            }
            //            string sql = @$" SELECT t.*,c.MemberNum from (SELECT a.ID,a.`Name`,a.`Describe`,a.CouponType,a.UseType,a.StartDate,a.EndDate,a.MinConsumePrice,a.MaxDiscountsPrice,a.DiscountsPrice,a.IndateDay,a.IndateType from rb_discountcoupon as a 
            //  {where}  and a.IndateType=1 
            //UNION ALL 
            //SELECT a.ID,a.`Name`,a.`Describe`,a.CouponType,a.UseType,a.StartDate,a.EndDate,a.MinConsumePrice,a.MaxDiscountsPrice,a.DiscountsPrice,a.IndateDay,a.IndateType from rb_discountcoupon as a 
            //  {where}  and a.IndateType=2 and a.StartDate>NOW() and a.EndDate<NOW() )
            //as t   LEFT JOIN (SELECT CouponId,COUNT(*) as MemberNum from rb_member_discountcoupon where `Status`=0 and UseState!=2 {userWhere} GROUP BY CouponId) as c on t.ID=c.CouponId ";

            string sql = @$" SELECT t.*,c.MemberNum from (SELECT a.ID,a.`Name`,a.`Describe`,a.CouponType,a.UseType,a.StartDate,a.EndDate,a.MinConsumePrice,a.MaxDiscountsPrice,a.DiscountsPrice,a.IndateDay,a.IndateType from rb_discountcoupon as a 
  {where}  and a.IndateType=1 
UNION ALL 
SELECT a.ID,a.`Name`,a.`Describe`,a.CouponType,a.UseType,a.StartDate,a.EndDate,a.MinConsumePrice,a.MaxDiscountsPrice,a.DiscountsPrice,a.IndateDay,a.IndateType from rb_discountcoupon as a 
  {where}  and a.IndateType=2  and a.EndDate>NOW() )
as t   LEFT JOIN (SELECT CouponId,COUNT(*) as MemberNum from rb_member_discountcoupon where `Status`=0 and UseState!=2 {userWhere} GROUP BY CouponId) as c on t.ID=c.CouponId ";
            return GetPage<RB_DiscountCoupon_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }



        /// <summary>
        /// 领劵中心优惠券
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_DiscountCoupon_Extend> GetMemberDisCouponList(int pageIndex, int pageSize, out long rowCount, RB_DiscountCoupon_Extend query)
        {
            string where = "   where a.`Status`=0 and b.Status=0  ";
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon.TenantId)}={query.TenantId}";

                    where += $" AND b.{nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}";
                }
                if (query.MallBaseId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon.MallBaseId)}={query.MallBaseId}";
                    where += $" AND b.{nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.UserId > 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon.UserId)}={query.UserId}";
                }

                if (query.StartDate.HasValue)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon.CreateDate)}>='{query.StartDate.Value:yyyy-MM-dd HH:mm:ss}'";
                }
                if (query.EndDate.HasValue)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon.CreateDate)}<='{query.EndDate.Value:yyyy-MM-dd HH:mm:ss}'";
                }
                if (query.UseState == 0)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState} AND a.{nameof(RB_Member_DiscountCoupon_Extend.EndDate)}> NOW()";
                }
                else if (query.UseState == 1)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}={query.UseState}";
                }
                else if (query.UseState == 2)
                {
                    where += $" AND a.{nameof(RB_Member_DiscountCoupon_Extend.EndDate)}<NOW()   AND a.{nameof(RB_Member_DiscountCoupon_Extend.UseState)}=0";
                }
            }
            string sql = @$"  SELECT a.ID as MemberCouponId,a.UseState,a.StartDate,a.EndDate,b.ID,b.`Name`,b.`Describe`,b.CouponType,b.UseType,a.GetType,a.CreateDate,
b.MinConsumePrice,b.MaxDiscountsPrice,b.DiscountsPrice,b.IndateDay,b.IndateType from rb_member_discountcoupon as a LEFT JOIN rb_discountcoupon as b 
on a.CouponId=b.ID {where} ";
            return GetPage<RB_DiscountCoupon_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }

        #endregion


        #region 自动发放优惠券信息

        /// <summary>
        /// 获取自动发放优惠券信息
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_DiscountCoupon_Extend> GetSelfmotionCouponList(RB_DiscountCoupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            string userWhere = "";
            // builder.Append(@$" SELECT a.*,b.TriggerType,b.GrantNum from rb_coupon_selfmotion as b LEFT JOIN {TableName} as a on a.ID=b.DiscountCouponId  WHERE a.{nameof(RB_DiscountCoupon_Extend.Status)}=0 and b.`Status`=0  and (a.IndateType=1 or (a.IndateType=2  and a.StartDate<NOW() and a.EndDate>NOW()))");
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_DiscountCoupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_DiscountCoupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.TriggerType > 0)
                {
                    builder.Append($" AND b.{nameof(RB_DiscountCoupon_Extend.TriggerType)}={query.TriggerType}");
                    if (query.TriggerType == 4 && query.RegisterDate.HasValue)
                    {
                        builder.Append($" AND DATE_FORMAT(b.{nameof(RB_DiscountCoupon_Extend.RegisterDate)},'%Y-%m-%d' )<= DATE_FORMAT('{query.RegisterDate}','%Y-%m-%d' )");
                    }
                }
                if (query.UserId > 0)
                {
                    userWhere += $"and c.UserId={query.UserId}";
                }

                if (query.SmallShopId > 0)
                {
                    builder.Append($" AND ( a.{nameof(RB_DiscountCoupon_Extend.SmallShopId)}={query.SmallShopId} or  (a.{nameof(RB_DiscountCoupon_Extend.SmallShopId)}=0 and a.{nameof(RB_DiscountCoupon_Extend.IsZanYangUse)}=0))");
                }
                else
                {
                    builder.Append($" AND a.{nameof(RB_DiscountCoupon_Extend.SmallShopId)}=0");
                }
            }

            string sql = @$"SELECT a.*,b.TriggerType,b.GrantNum from rb_coupon_selfmotion as b LEFT JOIN {TableName} as a on a.ID=b.DiscountCouponId
where (a.IndateType=1 or (a.IndateType=2   and a.EndDate>NOW()))
 and a.`Status`=0 and b.`Status`=0 and IsAll=0 {builder.ToString()}
UNION ALL 
SELECT a.*,b.TriggerType,b.GrantNum from rb_coupon_selfmotion as b LEFT JOIN rb_discountcoupon as a on a.ID=b.DiscountCouponId
LEFT JOIN rb_coupon_selfmotionmember as c on c.SelfMotionId=b.ID
where (a.IndateType=1 or (a.IndateType=2   and a.EndDate>NOW())) {builder.ToString()}
 and a.`Status`=0 and b.`Status`=0 and IsAll=1 {userWhere} ";
            return Get<RB_DiscountCoupon_Extend>(sql).ToList();
        }
        #endregion

    }
}
