<?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.dora.mapper.UserBalanceMapper">

    <resultMap id="UserBalanceResultMap" type="com.dora.entity.UserBalance">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="balance" property="balance"/>
        <result column="total_income" property="totalIncome"/>
        <result column="total_withdrawn" property="totalWithdrawn"/>
        <result column="frozen_amount" property="frozenAmount"/>
        <result column="last_updated_at" property="lastUpdatedAt"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <select id="selectByUserId" resultMap="UserBalanceResultMap">
        SELECT * FROM user_balance 
        WHERE user_id = #{userId} AND is_deleted = 0
        LIMIT 1
    </select>

    <insert id="insert" parameterType="com.dora.entity.UserBalance" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO user_balance (
            user_id, balance, total_income, total_withdrawn, frozen_amount, 
            last_updated_at, create_time, update_time, is_deleted
        ) VALUES (
            #{userId}, #{balance}, #{totalIncome}, #{totalWithdrawn}, #{frozenAmount}, 
            NOW(), NOW(), NOW(), 0
        )
    </insert>

    <update id="updateBalance">
        UPDATE user_balance 
        SET balance = #{balance},
            total_income = #{totalIncome},
            total_withdrawn = #{totalWithdrawn},
            frozen_amount = #{frozenAmount},
            last_updated_at = NOW(),
            update_time = NOW()
        WHERE user_id = #{userId} AND is_deleted = 0
    </update>

    <update id="increaseBalance">
        UPDATE user_balance 
        SET balance = balance + #{amount},
            total_income = total_income + #{totalIncome},
            last_updated_at = NOW(),
            update_time = NOW()
        WHERE user_id = #{userId} AND is_deleted = 0
    </update>

    <update id="decreaseBalance">
        UPDATE user_balance 
        SET balance = balance - #{amount},
            total_withdrawn = total_withdrawn + #{totalWithdrawn},
            last_updated_at = NOW(),
            update_time = NOW()
        WHERE user_id = #{userId} AND is_deleted = 0
    </update>

    <update id="freezeBalance">
        UPDATE user_balance 
        SET frozen_amount = frozen_amount + #{amount},
            last_updated_at = NOW(),
            update_time = NOW()
        WHERE user_id = #{userId} AND is_deleted = 0
    </update>

    <update id="unfreezeBalance">
        UPDATE user_balance 
        SET frozen_amount = frozen_amount - #{amount},
            last_updated_at = NOW(),
            update_time = NOW()
        WHERE user_id = #{userId} AND is_deleted = 0
    </update>

    <!-- ====== 仪表盘统计相关 ====== -->

    <!-- 统计总收入（基于已完成的订单） -->
    <select id="sumTotalIncome" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM `order` 
        WHERE status = 1 AND is_deleted = 0
    </select>

    <!-- 统计今日收入（基于今日已完成的订单） -->
    <select id="sumTodayIncome" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(amount), 0) FROM `order` 
        WHERE status = 1 
        AND DATE(paid_at) = DATE(#{today})
        AND is_deleted = 0
    </select>

    <!-- 统计用户余额总和 -->
    <select id="sumTotalBalance" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(balance), 0) FROM user_balance 
        WHERE is_deleted = 0
    </select>

</mapper> 