<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.web.modules.store.mapper.BizShopStatsMapper">

    <insert id="insertStatsForShopsWithReviews">
        insert into biz_shop_stats (shop_id, rating, fans_count, update_time)
        select r.shop_id,
               round(sum(r.score) / count(1), 2),
               0,
               now()
        from biz_goods_review r
        inner join biz_shop s on r.shop_id = s.shop_id and s.del_flag = '0'
        left join biz_shop_stats st on st.shop_id = r.shop_id
        where r.del_flag = '0'
          and r.score is not null
          and st.shop_id is null
        group by r.shop_id
    </insert>

    <update id="updateAllShopRatings">
        update biz_shop_stats st
        inner join biz_shop s on st.shop_id = s.shop_id and s.del_flag = '0'
        left join (
            select shop_id,
                   round(sum(score) / count(1), 2) as avg_rating
            from biz_goods_review
            where del_flag = '0'
              and score is not null
            group by shop_id
        ) r on st.shop_id = r.shop_id
        set st.rating = r.avg_rating,
            st.update_time = now()
    </update>

</mapper>
