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

    <resultMap id="ContentRevenueStageResultMap" type="com.dora.entity.ContentRevenueStage">
        <id column="id" property="id"/>
        <result column="content_type" property="contentType"/>
        <result column="content_id" property="contentId"/>
        <result column="user_id" property="userId"/>
        <result column="stage_level" property="stageLevel"/>
        <result column="stage_name" property="stageName"/>
        <result column="target_count" property="targetCount"/>
        <result column="reward_amount" property="rewardAmount"/>
        <result column="current_count" property="currentCount"/>
        <result column="is_achieved" property="isAchieved"/>
        <result column="achieved_at" property="achievedAt"/>
        <result column="is_withdrawn" property="isWithdrawn"/>
        <result column="withdrawn_at" property="withdrawnAt"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <select id="selectByUserIdAndType" resultMap="ContentRevenueStageResultMap">
        SELECT * FROM content_revenue_stage 
        WHERE user_id = #{userId} AND content_type = #{contentType} AND is_deleted = 0
        ORDER BY content_id ASC, stage_level ASC
    </select>

    <select id="selectByContentId" resultMap="ContentRevenueStageResultMap">
        SELECT * FROM content_revenue_stage 
        WHERE content_type = #{contentType} AND content_id = #{contentId} AND is_deleted = 0
        ORDER BY stage_level ASC
    </select>

    <update id="updateCurrentCount">
        UPDATE content_revenue_stage 
        SET current_count = #{currentCount}, update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <update id="markAsAchieved">
        UPDATE content_revenue_stage 
        SET is_achieved = 1, achieved_at = #{achievedAt}, update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <update id="markAsWithdrawn">
        UPDATE content_revenue_stage 
        SET is_withdrawn = 1, withdrawn_at = #{withdrawnAt}, update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <select id="selectWithdrawableStages" resultMap="ContentRevenueStageResultMap">
        SELECT * FROM content_revenue_stage 
        WHERE user_id = #{userId} AND is_achieved = 1 AND is_withdrawn = 0 AND is_deleted = 0
        ORDER BY content_type ASC, content_id ASC, stage_level ASC
    </select>

    <select id="sumWithdrawnAmount" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(reward_amount), 0) FROM content_revenue_stage 
        WHERE user_id = #{userId} AND is_withdrawn = 1 AND is_deleted = 0
    </select>

    <select id="sumUnwithdrawnAmount" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(reward_amount), 0) FROM content_revenue_stage 
        WHERE user_id = #{userId} AND is_achieved = 1 AND is_withdrawn = 0 AND is_deleted = 0
    </select>

    <select id="selectByUserIdAndTypeWithPagination" resultMap="ContentRevenueStageResultMap">
        SELECT * FROM content_revenue_stage 
        WHERE user_id = #{userId} AND content_type = #{contentType} AND is_deleted = 0
        ORDER BY content_id ASC, stage_level ASC
        LIMIT #{offset}, #{size}
    </select>

    <select id="countByUserIdAndType" resultType="java.lang.Long">
        SELECT COUNT(*) FROM content_revenue_stage 
        WHERE user_id = #{userId} AND content_type = #{contentType} AND is_deleted = 0
    </select>

    <select id="sumUnwithdrawnAmountByContentId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(reward_amount), 0) FROM content_revenue_stage 
        WHERE user_id = #{userId} AND content_type = #{contentType} AND content_id = #{contentId} 
        AND is_achieved = 1 AND is_withdrawn = 0 AND is_deleted = 0
    </select>

    <!-- 插入新的收益阶段记录 -->
    <insert id="insert" parameterType="com.dora.entity.ContentRevenueStage" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO content_revenue_stage (
            content_type, content_id, user_id, stage_level, stage_name, 
            target_count, reward_amount, current_count, is_achieved, 
            achieved_at, is_withdrawn, withdrawn_at, create_time, update_time, is_deleted
        ) VALUES (
            #{contentType}, #{contentId}, #{userId}, #{stageLevel}, #{stageName},
            #{targetCount}, #{rewardAmount}, #{currentCount}, #{isAchieved},
            #{achievedAt}, #{isWithdrawn}, #{withdrawnAt}, #{createTime}, #{updateTime}, #{isDeleted}
        )
    </insert>

    <!-- 根据内容ID统计已达到的收益总金额 -->
    <select id="sumRevenueByContentId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(reward_amount), 0) FROM content_revenue_stage 
        WHERE content_type = #{contentType} AND content_id = #{contentId} 
        AND is_achieved = 1 AND is_deleted = 0
    </select>

</mapper> 