<?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.ContentRevenueMapper">

    <resultMap id="ContentRevenueResultMap" type="com.dora.entity.ContentRevenue">
        <id column="id" property="id"/>
        <result column="user_id" property="userId"/>
        <result column="content_type" property="contentType"/>
        <result column="content_id" property="contentId"/>
        <result column="revenue_source" property="revenueSource"/>
        <result column="source_value" property="sourceValue"/>
        <result column="quantity" property="quantity"/>
        <result column="commission_rate" property="commissionRate"/>
        <result column="revenue_amount" property="revenueAmount"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="settled_at" property="settledAt"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <select id="selectByUserId" resultMap="ContentRevenueResultMap">
        SELECT * FROM content_revenue 
        WHERE user_id = #{userId} AND is_deleted = 0
        ORDER BY create_time DESC
    </select>

    <select id="sumSettledRevenueByUserId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(revenue_amount), 0) FROM content_revenue 
        WHERE user_id = #{userId} AND status = 1 AND is_deleted = 0
    </select>

    <select id="selectByUserIdAndType" resultMap="ContentRevenueResultMap">
        SELECT * FROM content_revenue 
        WHERE user_id = #{userId} AND content_type = #{contentType} AND is_deleted = 0
        ORDER BY create_time DESC
    </select>

    <select id="sumSettledRevenueByUserIdAndType" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(revenue_amount), 0) FROM content_revenue 
        WHERE user_id = #{userId} AND content_type = #{contentType} AND status = 1 AND is_deleted = 0
    </select>

</mapper> 