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

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


        #region 基础信息
        /// <summary>
        /// 表名称
        /// </summary>
        public string discountCouponTableName { get { return nameof(RB_Education_MemberCoupon); } }

        /// <summary>
        /// 卡券信息列表
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <param name="rowCount">总条数</param>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Education_Coupon_Extend> GetDiscountCouponPageList(int pageIndex, int pageSize, out long rowCount, RB_Education_Coupon_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_Education_Coupon_Extend.Status)}=0");
            if (query != null)
            {
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Education_Coupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Education_Coupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND a.{nameof(RB_Education_Coupon_Extend.Name)} like '%{query.Name}%'");
                }
            }
            builder.Append($"  order by  a.{nameof(RB_Education_Coupon_Extend.ID)} desc");
            return GetPage<RB_Education_Coupon_Extend>(pageIndex, pageSize, out rowCount, builder.ToString()).ToList();
        }


        /// <summary>
        /// 卡券信息列表
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Education_Coupon_Extend> GetDiscountCouponList(RB_Education_Coupon_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_Education_Coupon_Extend.Status)}=0 ");
            }
            if (query != null)
            {
                if (query.ID > 0)
                {
                    builder.Append($" AND {nameof(RB_Education_Coupon_Extend.ID)}={query.ID}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND {nameof(RB_Education_Coupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND {nameof(RB_Education_Coupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (!string.IsNullOrWhiteSpace(query.Name))
                {
                    builder.Append($" AND {nameof(RB_Education_Coupon_Extend.Name)} like '%{query.Name}%'");
                }
                if (!string.IsNullOrEmpty(query.CouponIds))
                {
                    builder.Append($" AND {nameof(RB_Education_Coupon_Extend.ID)} in({query.CouponIds})");
                }
            }
            return Get<RB_Education_Coupon_Extend>(builder.ToString()).ToList();
        }




        /// <summary>
        /// 根据用户id和商品分类以及商品id获取未使用的优惠券信息
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Education_Coupon_Extend> GetNoUseCouponList(RB_Education_Coupon_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_Education_Coupon_Extend.TenantId)}={query.TenantId}";
                }
                if (query.MallBaseId > 0)
                {
                    where += $" AND b.{nameof(RB_Education_Coupon_Extend.MallBaseId)}={query.MallBaseId}";
                }
                if (query.UserId > 0)
                {
                    where += $" AND a.{nameof(RB_Education_Coupon_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_Education_MemberCoupon as a 
 LEFT JOIN  RB_Education_Coupon as b on a.CouponId=b.ID
LEFT JOIN RB_Education_CouponProduct as  c on c.CouponId=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_Education_MemberCoupon as a 
 LEFT JOIN  RB_Education_Coupon as b on a.CouponId=b.ID
LEFT JOIN RB_Education_CouponProduct as  c on c.CouponId=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_Education_MemberCoupon as a 
 LEFT JOIN  RB_Education_Coupon as b on a.CouponId=b.ID
 where   b.UseType=3   {where} {allWhere}";
            return Get<RB_Education_Coupon_Extend>(sql).ToList();
        }


        #endregion






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

        /// <summary>
        /// 领劵中心优惠券
        /// </summary>
        /// <param name="query">查询条件</param>
        /// <returns></returns>
        public List<RB_Education_Coupon_Extend> GetAllCouponList(int pageIndex, int pageSize, out long rowCount, RB_Education_Coupon_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_Education_Coupon_Extend.TenantId)}={query.TenantId}";

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

                }
                else
                {
                    where += $" AND a.{nameof(RB_Education_Coupon_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.CouponIco,a.IndateDay,a.IndateType from {TableName} 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.CouponIco,a.IndateDay,a.IndateType from {TableName} as a 
  {where}  and a.IndateType=2  and a.EndDate>NOW() )
as t   LEFT JOIN (SELECT CouponId,COUNT(*) as MemberNum from RB_Education_MemberCoupon where `Status`=0  {userWhere} GROUP BY CouponId) as c on t.ID=c.CouponId ";
            return GetPage<RB_Education_Coupon_Extend>(pageIndex, pageSize, out rowCount, sql).ToList();
        }



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

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

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

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

        #endregion


        #region 自动发放优惠券信息
        public List<RB_Education_Coupon_Extend> GetSelfmotionCouponList(RB_Education_Coupon_Extend query)
        {
            StringBuilder builder = new StringBuilder();
            string userWhere = "";
            if (query != null)
            {

                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Education_Coupon_Extend.TenantId)}={query.TenantId}");
                }
                if (query.TenantId > 0)
                {
                    builder.Append($" AND a.{nameof(RB_Education_Coupon_Extend.MallBaseId)}={query.MallBaseId}");
                }
                if (query.TriggerType > 0)
                {
                    builder.Append($" AND b.{nameof(RB_Education_Coupon_Extend.TriggerType)}={query.TriggerType}");
                }
                if (query.UserId > 0)
                {
                    userWhere += $"and c.UserId={query.UserId}";
                }

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

            string sql = @$"SELECT a.*,b.TriggerType,b.GrantNum from rb_education_couponselfmotion 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_education_couponselfmotion as b LEFT JOIN  {TableName} as a on a.ID=b.DiscountCouponId
LEFT JOIN rb_education_couponselfmotionmember 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_Education_Coupon_Extend>(sql).ToList();
        }

        #endregion
    }
}
