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

namespace Mall.Repository.Product
{
    /// <summary>
    /// 商品评论仓储层
    /// </summary>
    public class RB_Goods_CommentRepository : BaseRepository<RB_Goods_Comment>
    {

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetPageList(int pageIndex, int pageSize, out long count, RB_Goods_Comment_Extend dmodel)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.Id)}={dmodel.Id}";
            }
            if (dmodel.CommentGrade > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.CommentGrade)}={(int)dmodel.CommentGrade}";
            }
            if (dmodel.PlatformSource > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.PlatformSource)}={(int)dmodel.PlatformSource}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and c.{nameof(RB_Goods_Comment.UserName)} like '%{dmodel.UserName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.GoodsName))
            {
                where += $@" and g.{nameof(RB_Goods.Name)} like '%{dmodel.GoodsName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Content))
            {
                where += $@" and c.{nameof(RB_Goods_Comment.Content)} like '%{dmodel.Content}%'";
            }

            string sql = $@"SELECT c.*,g.Name as GoodsName,g.CarouselImage FROM RB_Goods_Comment c
left join rb_goods g on c.GoodsId = g.Id
where {where} order by c.Id desc";
            return GetPage<RB_Goods_Comment_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetAppletPageList(int pageIndex, int pageSize, out long count, RB_Goods_Comment_Extend dmodel)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0 ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.Id > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.Id)}={dmodel.Id}";
            }
            if (dmodel.CommentGrade > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.CommentGrade)}={(int)dmodel.CommentGrade}";
            }
            if (dmodel.GoodsId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.GoodsId)}={dmodel.GoodsId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.UserId)}={dmodel.UserId}";
            }
            if (dmodel.PlatformSource > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.PlatformSource)}={(int)dmodel.PlatformSource}";
            }
            if (dmodel.Is_Show > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.Is_Show)}={dmodel.Is_Show}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and c.{nameof(RB_Goods_Comment.UserName)} like '%{dmodel.UserName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Content))
            {
                where += $@" and c.{nameof(RB_Goods_Comment.Content)} like '%{dmodel.Content}%'";
            }

            string sql = $@"SELECT c.* FROM RB_Goods_Comment c where {where} order by c.Is_Top asc,c.Id desc";
            return GetPage<RB_Goods_Comment_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取评论数量统计
        /// </summary>
        /// <param name="goodsId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetAppletGoodsCommentStatistics(int goodsId, int tenantId, int mallBaseId)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0 and c.{nameof(RB_Goods_Comment.Is_Show)}=1";
            if (tenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={tenantId}";
            }
            if (mallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={mallBaseId}";
            }
            if (goodsId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.GoodsId)}={goodsId}";
            }

            string sql = $@"SELECT c.CommentGrade,COUNT(0) as CommentNum FROM RB_Goods_Comment c where {where} GROUP BY c.CommentGrade";
            return Get<RB_Goods_Comment_Extend>(sql).ToList();
        }


        /// <summary>
        /// 获取车辆和导游评分信息
        /// </summary>
        /// <param name="goodsId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetAppletGoodsCommentscore(int carId, int guideId, int tenantId, int mallBaseId)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0 ";
            if (tenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={tenantId}";
            }
            if (mallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={mallBaseId}";
            }
            if (carId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.CarId)}={carId}";
            }
            if (guideId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.GuideId)}={guideId}";
            }
            string sql = $@"SELECT COUNT(0) as CommentNum,SUM(c.CommentScore) as TotalScore FROM RB_Goods_Comment c  
where  {where}  ";
            return Get<RB_Goods_Comment_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取车辆评分信息
        /// </summary>
        /// <param name="goodsId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetCarCommentByOrderDetailId(string orderDetailIds, string goodsIds, int tenantId, int mallBaseId)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0  and c.{nameof(RB_Goods_Comment.Is_Show)}=1  and c.{nameof(RB_Goods_Comment.CarId)}>0 ";
            if (tenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={tenantId}";
            }
            if (mallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={mallBaseId}";
            }
            if (!string.IsNullOrWhiteSpace(goodsIds))
            {
                where += $@" and b.ID in({goodsIds}) ";
            }
            string sql = $@"SELECT c.*,a.`Name` as CarName,d.ColorName as CarColorName,cb.`Name` as CarBrandName FROM RB_Goods_Comment c
 LEFT JOIN rb_guidecar_car as a on c.CarId=a.ID
LEFT JOIN rb_goods as b on b.CarId=a.ID
LEFT JOIN rb_guidecar_carcolor as d on d.ID=b.CarColorId
LEFT JOIN rb_guidecar_carbrand as cb on cb.ID=a.CarBrandId where  {where}  ";
            return Get<RB_Goods_Comment_Extend>(sql).ToList();
        }


        /// <summary>
        /// 获取导游评分信息
        /// </summary>
        /// <param name="goodsId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetGuideCommentByOrderDetailId(string orderDetailIds,  int tenantId, int mallBaseId)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0  and c.{nameof(RB_Goods_Comment.Is_Show)}=1  and c.{nameof(RB_Goods_Comment.GuideId)}>0 ";
            if (tenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={tenantId}";
            }
            if (mallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={mallBaseId}";
            }
            if (!string.IsNullOrWhiteSpace(orderDetailIds))
            {
                where += $@" and c.{nameof(RB_Goods_Comment.OrderDetailId)} in ({orderDetailIds}) ";
            }
           
            string sql = $@"SELECT c.*,a.`Name` as GuideName FROM RB_Goods_Comment c LEFT JOIN rb_guidecar_guide as a on c.GuideId=a.ID where  {where}  ";
            return Get<RB_Goods_Comment_Extend>(sql).ToList();
        }

        /// <summary>
        /// 小程序商品详情评论分页列表
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="dmodel"></param>
        /// <returns></returns>
        public List<GoodsComment_Extend> GetGoodsDetailPageList(int pageIndex, int pageSize, out long count, RB_Goods_Comment_Extend dmodel)
        {
            string where = $" 1=1 and {nameof(RB_Goods_Comment.Status)}=0 ";
            string whereGrade = " where 1=1  ";
            if (dmodel.TenantId > 0)
            {
                where += $@" and {nameof(RB_Goods_Comment.TenantId)}={dmodel.TenantId}";
            }
            if (dmodel.MallBaseId > 0)
            {
                where += $@" and {nameof(RB_Goods_Comment.MallBaseId)}={dmodel.MallBaseId}";
            }
            if (dmodel.GoodsId > 0)
            {
                where += $@" and {nameof(RB_Goods_Comment.GoodsId)}={dmodel.GoodsId}";
            }
            if (dmodel.UserId > 0)
            {
                where += $@" and {nameof(RB_Goods_Comment.UserId)}={dmodel.UserId}";
            }
            if (dmodel.PlatformSource > 0)
            {
                where += $@" and {nameof(RB_Goods_Comment.PlatformSource)}={(int)dmodel.PlatformSource}";
            }
            if (dmodel.Is_Show > 0)
            {
                where += $@" and {nameof(RB_Goods_Comment.Is_Show)}={dmodel.Is_Show}";
            }
            if (!string.IsNullOrEmpty(dmodel.UserName))
            {
                where += $@" and {nameof(RB_Goods_Comment.UserName)} like '%{dmodel.UserName}%'";
            }
            if (!string.IsNullOrEmpty(dmodel.Content))
            {
                where += $@" and {nameof(RB_Goods_Comment.Content)} like '%{dmodel.Content}%'";
            }

            if (dmodel.CommentGrade.HasValue)
            {
                if (dmodel.CommentGrade.Value == Common.Enum.Goods.GoodsCommentTypeEnum.Praise)
                {
                    whereGrade += $@" and   t.TotalScore>6";
                }
                else if (dmodel.CommentGrade.Value == Common.Enum.Goods.GoodsCommentTypeEnum.Medium)
                {
                    whereGrade += $@" and   t.TotalScore>3 and t.TotalScore<=6";
                }
                else if (dmodel.CommentGrade.Value == Common.Enum.Goods.GoodsCommentTypeEnum.Negative)
                {
                    whereGrade += $@" and   t.TotalScore>=1 and t.TotalScore<=3";
                }
            }
            string sql = $@"  SELECT * from(SELECT OrderDetailId,UserName,UserId,UserPhoto,Is_Anonymity,SUM(CommentScore) AS TotalScore,GoodsId,DATE_FORMAT(UpdateDate, '%y-%m-%d') AS UpdateDate ,       
    case    
    when   SUM(CommentScore)<=3 then 3       
    when SUM(CommentScore)>3 and SUM(CommentScore)<=6 then 2     
when SUM(CommentScore)>6 then 1 
ELSE 0     
    end as    CommentGrade
from rb_goods_comment where {where} GROUP BY OrderDetailId order by CreateDate desc ) as t  {whereGrade}";
            return GetPage<GoodsComment_Extend>(pageIndex, pageSize, out count, sql).ToList();
        }

        /// <summary>
        /// 获取评论数量统计
        /// </summary>
        /// <param name="goodsId"></param>
        /// <param name="tenantId"></param>
        /// <param name="mallBaseId"></param>
        /// <returns></returns>
        public List<RB_Goods_Comment_Extend> GetGuideCarCommentStatistics(int goodsId, int tenantId, int mallBaseId)
        {
            string where = $" 1=1 and c.{nameof(RB_Goods_Comment.Status)}=0 and c.{nameof(RB_Goods_Comment.Is_Show)}=1";
            if (tenantId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.TenantId)}={tenantId}";
            }
            if (mallBaseId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.MallBaseId)}={mallBaseId}";
            }
            if (goodsId > 0)
            {
                where += $@" and c.{nameof(RB_Goods_Comment.GoodsId)}={goodsId}";
            }

            string sql = $@"SELECT CommentGrade,COUNT(0) as CommentNum from (SELECT OrderDetailId,SUM(CommentScore) as TotalScore,GoodsId ,       
    case    
    when   SUM(CommentScore)<=3 then 3       
    when SUM(CommentScore)>3 and SUM(CommentScore)<=6 then 2     
when SUM(CommentScore)>6 then 1 
ELSE 0     
    end as    CommentGrade
from rb_goods_comment as c where {where} GROUP BY OrderDetailId) as t GROUP BY t.CommentGrade";
            return Get<RB_Goods_Comment_Extend>(sql).ToList();
        }

        /// <summary>
        /// 获取商品评分
        /// </summary>
        /// <param name="goodsId"></param>
        /// <returns></returns>
        public RB_Goods_Comment_Extend GetGoodsCommentScore(int goodsId) {
            string sql = $@"SELECT SUM(CommentScore) AS CommentScore, COUNT(0) AS CommentNum FROM rb_goods_comment WHERE Status =0 and GoodsId = {goodsId}";
            return Get<RB_Goods_Comment_Extend>(sql).FirstOrDefault();
        }


    }
}
