<?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.finance.mapper.BizShopWalletMapper">

    <select id="sumAvailableBalance" resultType="java.math.BigDecimal">
        select ifnull(sum(available_balance), 0) from biz_shop_wallet
    </select>

    <select id="sumFrozenAmount" resultType="java.math.BigDecimal">
        select ifnull(sum(frozen_amount), 0) from biz_shop_wallet
    </select>

    <select id="selectPlatformShopRankList" resultType="com.ruoyi.web.modules.finance.vo.PlatformShopFundRankRowVO">
        select s.shop_id as shopId,
               s.shop_name as shopName,
               s.shop_avatar as shopAvatar,
               s.shop_status as shopStatus,
               s.del_flag as delFlag,
               s.create_time as openTime,
               ifnull(w.available_balance, 0) as availableBalance,
               ifnull(w.frozen_amount, 0) as frozenAmount,
               ifnull(ps.pending_settle, 0) as pendingSettleAmount
        from biz_shop s
        left join biz_shop_wallet w on w.shop_id = s.shop_id
        left join (
            select shop_id, ifnull(sum(pay_amount), 0) as pending_settle
            from biz_order
            where pay_status = '1'
              and order_status in ('1', '2')
              and del_flag = '0'
            group by shop_id
        ) ps on ps.shop_id = s.shop_id
        where (
            s.del_flag = '0'
            or w.wallet_id is not null
            or exists (select 1 from biz_shop_fund_log l where l.shop_id = s.shop_id limit 1)
        )
        order by
        <choose>
            <when test="query.orderByColumn == 'pendingSettleAmount' or query.orderByColumn == 'pending_settle_amount'">
                ifnull(ps.pending_settle, 0)
            </when>
            <when test="query.orderByColumn == 'frozenAmount' or query.orderByColumn == 'frozen_amount'">
                ifnull(w.frozen_amount, 0)
            </when>
            <otherwise>
                ifnull(w.available_balance, 0)
            </otherwise>
        </choose>
        <choose>
            <when test="query.isAsc == 'asc'">
                asc
            </when>
            <otherwise>
                desc
            </otherwise>
        </choose>
        , s.shop_id desc
    </select>

    <select id="selectByShopId" resultType="com.ruoyi.web.modules.finance.domain.BizShopWallet">
        select wallet_id as walletId, shop_id as shopId,
               available_balance as availableBalance, frozen_amount as frozenAmount, update_time as updateTime
        from biz_shop_wallet
        where shop_id = #{shopId}
    </select>

    <select id="selectByShopIdForUpdate" resultType="com.ruoyi.web.modules.finance.domain.BizShopWallet">
        select wallet_id as walletId, shop_id as shopId,
               available_balance as availableBalance, frozen_amount as frozenAmount, update_time as updateTime
        from biz_shop_wallet
        where shop_id = #{shopId}
        for update
    </select>

    <insert id="insert" parameterType="com.ruoyi.web.modules.finance.domain.BizShopWallet" useGeneratedKeys="true" keyProperty="walletId">
        insert into biz_shop_wallet (shop_id, available_balance, frozen_amount, update_time)
        values (#{shopId}, #{availableBalance}, #{frozenAmount}, #{updateTime})
    </insert>

    <update id="updateBalance">
        update biz_shop_wallet
        set available_balance = #{availableBalance},
            frozen_amount = #{frozenAmount},
            update_time = #{updateTime}
        where shop_id = #{shopId}
    </update>

</mapper>
