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

    <!-- 结果映射 -->
    <resultMap id="UserResultMap" type="com.dora.entity.User">
        <id column="id" property="id"/>
        <result column="phone" property="phone"/>
        <result column="wechat_openid" property="wechatOpenid"/>
        <result column="username" property="username"/>
        <result column="real_username" property="realUsername"/>
        <result column="id_number" property="idNumber"/>
        <result column="password_jwt" property="passwordJwt"/>
        <result column="role" property="role"/>
        <result column="membership_expires_at" property="membershipExpiresAt"/>
        <result column="avatar_url" property="avatarUrl"/>
        <result column="is_verified" property="isVerified"/>
        <result column="invite_code" property="inviteCode"/>
        <result column="inviter_id" property="inviterId"/>
        <result column="promotion_level" property="promotionLevel"/>
        <result column="points" property="points"/>
        <result column="points_expires_at" property="pointsExpiresAt"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="is_deleted" property="isDeleted"/>
    </resultMap>

    <!-- 根据ID获取用户信息 -->
    <select id="selectById" resultMap="UserResultMap">
        SELECT 
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        FROM user 
        WHERE id = #{id} AND is_deleted = 0
    </select>

    <!-- 根据ID列表批量获取用户信息 -->
    <select id="selectByIds" resultMap="UserResultMap">
        SELECT 
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        FROM user 
        WHERE id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        AND is_deleted = 0
    </select>

    <!-- 根据手机号获取用户信息 -->
    <select id="selectByPhone" resultMap="UserResultMap">
        SELECT 
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        FROM user 
        WHERE phone = #{phone} AND is_deleted = 0
        LIMIT 1
    </select>

    <!-- 根据用户名获取用户信息 -->
    <select id="selectByUsername" resultMap="UserResultMap">
        SELECT 
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        FROM user 
        WHERE username = #{username} AND is_deleted = 0
        LIMIT 1
    </select>

    <!-- 根据推广码获取用户信息 -->
    <select id="selectByInviteCode" resultMap="UserResultMap">
        SELECT 
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        FROM user 
        WHERE invite_code = #{inviteCode} AND is_deleted = 0
        LIMIT 1
    </select>
    
    <!-- 根据微信openid查询用户 -->
    <select id="selectByWechatOpenid" parameterType="string" resultMap="UserResultMap">
        SELECT 
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        FROM user 
        WHERE wechat_openid = #{wechatOpenid} AND is_deleted = 0
        LIMIT 1
    </select>

    <!-- 插入新用户 -->
    <insert id="insert" parameterType="com.dora.entity.User" useGeneratedKeys="false" keyProperty="id">
        INSERT INTO user (
            id, phone, wechat_openid, username, real_username, id_number, password_jwt, role, membership_expires_at,
            avatar_url, is_verified, invite_code, inviter_id, promotion_level,
            points, points_expires_at, create_time, update_time, is_deleted
        ) VALUES (
            #{id}, #{phone}, #{wechatOpenid}, #{username}, #{realUsername}, #{idNumber}, #{passwordJwt}, #{role}, #{membershipExpiresAt},
            #{avatarUrl}, #{isVerified}, #{inviteCode}, #{inviterId}, #{promotionLevel},
            #{points}, #{pointsExpiresAt}, #{createTime}, #{updateTime}, #{isDeleted}
        )
    </insert>

    <!-- 更新用户信息 -->
    <update id="updateById" parameterType="com.dora.entity.User">
        UPDATE user
        <set>
            <if test="phone != null">phone = #{phone},</if>
            <if test="wechatOpenid != null">wechat_openid = #{wechatOpenid},</if>
            <if test="username != null">username = #{username},</if>
            <if test="realUsername != null">real_username = #{realUsername},</if>
            <if test="idNumber != null">id_number = #{idNumber},</if>
            <if test="passwordJwt != null">password_jwt = #{passwordJwt},</if>
            <if test="role != null">role = #{role},</if>
            <if test="membershipExpiresAt != null">membership_expires_at = #{membershipExpiresAt},</if>
            <if test="avatarUrl != null">avatar_url = #{avatarUrl},</if>
            <if test="isVerified != null">is_verified = #{isVerified},</if>
            <if test="inviteCode != null">invite_code = #{inviteCode},</if>
            <if test="inviterId != null">inviter_id = #{inviterId},</if>
            <if test="promotionLevel != null">promotion_level = #{promotionLevel},</if>
            <if test="points != null">points = #{points},</if>
            <if test="pointsExpiresAt != null">points_expires_at = #{pointsExpiresAt},</if>
            <if test="isDeleted != null">is_deleted = #{isDeleted},</if>
            update_time = NOW()
        </set>
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 更新用户角色和会员到期时间 -->
    <update id="updateRoleAndMembership">
        UPDATE user 
        SET 
            role = #{role},
            membership_expires_at = #{membershipExpiresAt},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 更新用户实名认证状态 -->
    <update id="updateVerificationStatus">
        UPDATE user 
        SET 
            is_verified = #{isVerified},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 更新用户积分 -->
    <update id="updateBalance">
        UPDATE user 
        SET 
            points = #{balance},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 更新推广等级 -->
    <update id="updatePromotionLevel">
        UPDATE user 
        SET 
            promotion_level = #{promotionLevel},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 根据用户ID获取推广等级 -->
    <select id="selectPromotionLevelById" resultType="java.lang.Integer">
        SELECT promotion_level 
        FROM user 
        WHERE id = #{id} AND is_deleted = 0
    </select>

    <!-- 更新用户推广人ID -->
    <update id="updateInviterId">
        UPDATE user 
        SET 
            inviter_id = #{inviterId},
            update_time = NOW()
        WHERE id = #{id} AND is_deleted = 0
    </update>

    <!-- 批量更新过期会员状态 -->
    <update id="updateExpiredMembership">
        UPDATE user 
        SET 
            role = 1,
            update_time = NOW()
        WHERE role &gt; 1 
          AND membership_expires_at IS NOT NULL 
          AND membership_expires_at &lt; #{currentTime}
          AND is_deleted = 0
    </update>

    <!-- 统计用户的粉丝数量 -->
    <select id="countFansByInviterId" resultType="int">
        SELECT COUNT(*)
        FROM user 
        WHERE inviter_id = #{inviterId} AND is_deleted = 0
    </select>

    <!-- 统计用户的付费粉丝数量（只统计真正付费的，不包括赠送和兑换的） -->
    <select id="countPaidFansByInviterId" resultType="int">
        SELECT COUNT(*)
        FROM user u
        LEFT JOIN `order` o ON u.id = o.user_id AND o.status = 1 AND o.is_deleted = 0
        WHERE u.inviter_id = #{inviterId} 
          AND u.is_deleted = 0 
          AND u.role &gt; 1
          AND o.id IS NOT NULL
    </select>

    <!-- 获取用户的总提成金额 -->
    <select id="getTotalCommissionByUserId" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(commission_amount), 0)
        FROM fan_promotion_commission 
        WHERE promoter_id = #{userId} 
          AND is_deleted = 0
    </select>

    <!-- 分页获取用户的粉丝列表（包含会员状态） -->
    <select id="selectFansByInviterId" resultType="com.dora.dto.FanDto">
        SELECT 
            u.id,
            u.username,
            u.phone,
            u.avatar_url as avatarUrl,
            u.role,
            CASE WHEN u.role &gt; 1 THEN 1 ELSE 0 END as isPaidFan,
            CASE 
                WHEN u.role &lt;= 1 THEN 'none'
                WHEN u.role &gt; 1 AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW()) THEN 'expired'
                WHEN EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                ) AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN 'exchange'
                WHEN u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY) 
                     AND u.role &gt; 1 
                     AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
                     AND NOT EXISTS (
                         SELECT 1 FROM `order` o 
                         WHERE o.user_id = u.id 
                         AND o.status = 1 
                         AND o.is_deleted = 0
                     ) THEN 'gift'
                WHEN u.role &gt; 1 AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN 'paid'
                ELSE 'none'
            END as membershipStatus,
            CASE 
                WHEN u.role &lt;= 1 THEN '非VIP'
                WHEN u.role &gt; 1 AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW()) THEN '过期会员'
                WHEN EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                ) AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN '兑换会员'
                WHEN u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY) 
                     AND u.role &gt; 1 
                     AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
                     AND NOT EXISTS (
                         SELECT 1 FROM `order` o 
                         WHERE o.user_id = u.id 
                         AND o.status = 1 
                         AND o.is_deleted = 0
                     ) THEN '赠送会员'
                WHEN u.role &gt; 1 AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN '付费会员'
                ELSE '非VIP'
            END as membershipStatusText,
            u.create_time as createTime,
            CASE WHEN u.role &gt; 1 THEN u.create_time ELSE NULL END as paidTime,
            u.membership_expires_at as membershipExpiresAt
        FROM user u
        WHERE u.inviter_id = #{inviterId} 
          AND u.is_deleted = 0
        ORDER BY u.create_time DESC
        LIMIT #{offset}, #{size}
    </select>

    <!-- 分页获取用户的付费粉丝列表（只统计真正付费的） -->
    <select id="selectPaidFansByInviterId" resultType="com.dora.dto.FanDto">
        SELECT 
            u.id,
            u.username,
            u.phone,
            u.avatar_url as avatarUrl,
            u.role,
            1 as isPaidFan,
            'paid' as membershipStatus,
            '付费会员' as membershipStatusText,
            u.create_time as createTime,
            u.create_time as paidTime,
            u.membership_expires_at as membershipExpiresAt
        FROM user u
        INNER JOIN `order` o ON u.id = o.user_id AND o.status = 1 AND o.is_deleted = 0
        WHERE u.inviter_id = #{inviterId} 
          AND u.is_deleted = 0
          AND u.role &gt; 1
        ORDER BY u.create_time DESC
        LIMIT #{offset}, #{size}
    </select>

    <!-- 根据状态筛选粉丝列表 -->
    <select id="selectFansByInviterIdAndStatus" resultType="com.dora.dto.FanDto">
        SELECT 
            u.id,
            u.username,
            u.phone,
            u.avatar_url as avatarUrl,
            u.role,
            CASE WHEN u.role &gt; 1 THEN 1 ELSE 0 END as isPaidFan,
            CASE 
                WHEN u.role &lt;= 1 THEN 'none'
                WHEN u.role &gt; 1 AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW()) THEN 'expired'
                WHEN EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                ) AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN 'exchange'
                WHEN u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY) 
                     AND u.role &gt; 1 
                     AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
                     AND NOT EXISTS (
                         SELECT 1 FROM `order` o 
                         WHERE o.user_id = u.id 
                         AND o.status = 1 
                         AND o.is_deleted = 0
                     ) THEN 'gift'
                WHEN u.role &gt; 1 AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN 'paid'
                ELSE 'none'
            END as membershipStatus,
            CASE 
                WHEN u.role &lt;= 1 THEN '非VIP'
                WHEN u.role &gt; 1 AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW()) THEN '过期会员'
                WHEN EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                ) AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN '兑换会员'
                WHEN u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY) 
                     AND u.role &gt; 1 
                     AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
                     AND NOT EXISTS (
                         SELECT 1 FROM `order` o 
                         WHERE o.user_id = u.id 
                         AND o.status = 1 
                         AND o.is_deleted = 0
                     ) THEN '赠送会员'
                WHEN u.role &gt; 1 AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW() THEN '付费会员'
                ELSE '非VIP'
            END as membershipStatusText,
            u.create_time as createTime,
            CASE WHEN u.role &gt; 1 THEN u.create_time ELSE NULL END as paidTime,
            u.membership_expires_at as membershipExpiresAt
        FROM user u
        WHERE u.inviter_id = #{inviterId} 
          AND u.is_deleted = 0
          <choose>
            <when test="status == 'gift'">
              AND u.role &gt; 1
              AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
              AND u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY)
              AND NOT EXISTS (
                  SELECT 1 FROM `order` o 
                  WHERE o.user_id = u.id 
                  AND o.status = 1 
                  AND o.is_deleted = 0
              )
              AND NOT EXISTS (
                  SELECT 1 FROM gift_code_usage gcu 
                  WHERE gcu.user_id = u.id 
                  AND gcu.type = 2 
                  AND gcu.status = 1 
                  AND gcu.is_deleted = 0
              )
            </when>
            <when test="status == 'exchange'">
              AND u.role &gt; 1
              AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
              AND EXISTS (
                  SELECT 1 FROM gift_code_usage gcu 
                  WHERE gcu.user_id = u.id 
                  AND gcu.type = 2 
                  AND gcu.status = 1 
                  AND gcu.is_deleted = 0
              )
            </when>
            <when test="status == 'paid'">
              AND u.role &gt; 1
              AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
              AND EXISTS (
                  SELECT 1 FROM `order` o 
                  WHERE o.user_id = u.id 
                  AND o.status = 1 
                  AND o.is_deleted = 0
              )
            </when>
            <when test="status == 'expired'">
              AND u.role &gt; 1
              AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
            </when>
            <when test="status == 'none'">
              AND u.role &lt;= 1
            </when>
          </choose>
        ORDER BY u.create_time DESC
        LIMIT #{offset}, #{size}
    </select>

    <!-- 统计指定状态的粉丝数量 -->
    <select id="countFansByInviterIdAndStatus" resultType="int">
        SELECT COUNT(*)
        FROM user u
        WHERE u.inviter_id = #{inviterId} 
          AND u.is_deleted = 0
          <choose>
            <when test="status == 'gift'">
              AND u.role &gt; 1
              AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
              AND u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY)
              AND NOT EXISTS (
                  SELECT 1 FROM `order` o 
                  WHERE o.user_id = u.id 
                  AND o.status = 1 
                  AND o.is_deleted = 0
              )
              AND NOT EXISTS (
                  SELECT 1 FROM gift_code_usage gcu 
                  WHERE gcu.user_id = u.id 
                  AND gcu.type = 2 
                  AND gcu.status = 1 
                  AND gcu.is_deleted = 0
              )
            </when>
            <when test="status == 'exchange'">
              AND u.role &gt; 1
              AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
              AND EXISTS (
                  SELECT 1 FROM gift_code_usage gcu 
                  WHERE gcu.user_id = u.id 
                  AND gcu.type = 2 
                  AND gcu.status = 1 
                  AND gcu.is_deleted = 0
              )
            </when>
            <when test="status == 'paid'">
              AND u.role &gt; 1
              AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
              AND EXISTS (
                  SELECT 1 FROM `order` o 
                  WHERE o.user_id = u.id 
                  AND o.status = 1 
                  AND o.is_deleted = 0
              )
            </when>
            <when test="status == 'expired'">
              AND u.role &gt; 1
              AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
            </when>
            <when test="status == 'none'">
              AND u.role &lt;= 1
            </when>
          </choose>
    </select>

    <!-- ==================== 后台管理相关SQL ==================== -->

    <!-- 分页查询用户列表（后台管理） -->
    <select id="selectUserListForAdmin" resultType="com.dora.dto.AdminUserDto$UserListItem">
        SELECT 
            u.id,
            u.phone,
            u.username,
            u.real_username as realUsername,
            u.id_number as idNumber,
            u.role,
            CASE 
                WHEN u.role = 0 THEN '游客'
                WHEN u.role = 1 THEN '普通用户'
                WHEN u.role = 2 THEN 'VIP'
                WHEN u.role = 3 THEN 'SVIP'
                ELSE '未知'
            END as roleName,
            u.membership_expires_at as membershipExpiresAt,
            u.avatar_url as avatarUrl,
            u.is_verified as isVerified,
            u.invite_code as inviteCode,
            u.inviter_id as inviterId,
            inviter.username as inviterUsername,
            u.promotion_level as promotionLevel,
            u.points,
            u.points_expires_at as pointsExpiresAt,
            u.create_time as createTime,
            u.update_time as updateTime,
            (SELECT COUNT(*) FROM user fan WHERE fan.inviter_id = u.id AND fan.is_deleted = 0) as fanCount,
            (SELECT COUNT(*) FROM user fan 
             LEFT JOIN `order` o ON fan.id = o.user_id AND o.status = 1 AND o.is_deleted = 0
             WHERE fan.inviter_id = u.id AND fan.is_deleted = 0 AND fan.role &gt; 1 AND o.id IS NOT NULL) as paidFanCount,
            COALESCE((
                SELECT SUM(ubl.change_amount) 
                FROM user_balance_log ubl 
                WHERE ubl.user_id = u.id 
                  AND ubl.change_type = 'income' 
                  AND ubl.source_type = 'promotion' 
                  AND ubl.is_deleted = 0
            ), 0) as totalCommission
        FROM user u
        LEFT JOIN user inviter ON u.inviter_id = inviter.id AND inviter.is_deleted = 0
        WHERE u.is_deleted = 0
        <if test="keyword != null and keyword != ''">
            AND (u.username LIKE CONCAT('%', #{keyword}, '%') 
                 OR u.phone LIKE CONCAT('%', #{keyword}, '%')
                 OR u.real_username LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        <if test="role != null">
            AND u.role = #{role}
        </if>
        <if test="isVerified != null">
            AND u.is_verified = #{isVerified}
        </if>
        <if test="promotionLevel != null">
            AND u.promotion_level = #{promotionLevel}
        </if>
        <if test="createTimeStart != null">
            AND u.create_time &gt;= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND u.create_time &lt;= #{createTimeEnd}
        </if>
        <!-- 会员类型筛选 -->
        <choose>
            <when test="membershipType == 'paid'">
                <!-- 当前付费会员：VIP用户且有成功的订单记录且会员未过期 -->
                AND u.role &gt; 1
                AND u.membership_expires_at IS NOT NULL 
                AND u.membership_expires_at &gt; NOW()
                AND EXISTS (
                    SELECT 1 FROM `order` o 
                    WHERE o.user_id = u.id 
                    AND o.status = 1 
                    AND o.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'exchange'">
                <!-- 当前兑换会员：有兑换会员卡记录且会员未过期 -->
                AND u.role &gt; 1
                AND u.membership_expires_at IS NOT NULL 
                AND u.membership_expires_at &gt; NOW()
                AND EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'gift'">
                <!-- 赠送会员：注册2天内的VIP用户，且没有付费记录和兑换记录，且会员未过期 -->
                AND u.role &gt; 1
                AND u.membership_expires_at IS NOT NULL 
                AND u.membership_expires_at &gt; NOW()
                AND u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY)
                AND NOT EXISTS (
                    SELECT 1 FROM `order` o 
                    WHERE o.user_id = u.id 
                    AND o.status = 1 
                    AND o.is_deleted = 0
                )
                AND NOT EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'expired'">
                <!-- 过期会员：VIP用户但会员已过期 -->
                AND u.role &gt; 1
                AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
            </when>
            <when test="membershipType == 'paidExpired'">
                <!-- 付费过期会员：有付费记录但会员已过期 -->
                AND u.role &gt; 1
                AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
                AND EXISTS (
                    SELECT 1 FROM `order` o 
                    WHERE o.user_id = u.id 
                    AND o.status = 1 
                    AND o.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'exchangeExpired'">
                <!-- 兑换过期会员：有兑换记录但会员已过期 -->
                AND u.role &gt; 1
                AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
                AND EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                )
            </when>
            <!-- membershipType为null、空字符串或'all'时不添加额外筛选条件 -->
        </choose>
        <choose>
            <when test="sortField == 'createTime'">
                ORDER BY u.create_time
            </when>
            <when test="sortField == 'updateTime'">
                ORDER BY u.update_time
            </when>
            <when test="sortField == 'role'">
                ORDER BY u.role
            </when>
            <when test="sortField == 'points'">
                ORDER BY u.points
            </when>
            <when test="sortField == 'promotionLevel'">
                ORDER BY u.promotion_level
            </when>
            <otherwise>
                ORDER BY u.create_time
            </otherwise>
        </choose>
        <if test="sortOrder == 'desc'">
            DESC
        </if>
        <if test="sortOrder == 'asc'">
            ASC
        </if>
        LIMIT #{offset}, #{size}
    </select>

    <!-- 统计用户列表总数（后台管理） -->
    <select id="countUserListForAdmin" resultType="int">
        SELECT COUNT(*)
        FROM user u
        WHERE u.is_deleted = 0
        <if test="keyword != null and keyword != ''">
            AND (u.username LIKE CONCAT('%', #{keyword}, '%') 
                 OR u.phone LIKE CONCAT('%', #{keyword}, '%')
                 OR u.real_username LIKE CONCAT('%', #{keyword}, '%'))
        </if>
        <if test="role != null">
            AND u.role = #{role}
        </if>
        <if test="isVerified != null">
            AND u.is_verified = #{isVerified}
        </if>
        <if test="promotionLevel != null">
            AND u.promotion_level = #{promotionLevel}
        </if>
        <if test="createTimeStart != null">
            AND u.create_time &gt;= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND u.create_time &lt;= #{createTimeEnd}
        </if>
        <!-- 会员类型筛选 -->
        <choose>
            <when test="membershipType == 'paid'">
                <!-- 当前付费会员：VIP用户且有成功的订单记录且会员未过期 -->
                AND u.role &gt; 1
                AND u.membership_expires_at IS NOT NULL 
                AND u.membership_expires_at &gt; NOW()
                AND EXISTS (
                    SELECT 1 FROM `order` o 
                    WHERE o.user_id = u.id 
                    AND o.status = 1 
                    AND o.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'exchange'">
                <!-- 当前兑换会员：有兑换会员卡记录且会员未过期 -->
                AND u.role &gt; 1
                AND u.membership_expires_at IS NOT NULL 
                AND u.membership_expires_at &gt; NOW()
                AND EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'gift'">
                <!-- 赠送会员：注册2天内的VIP用户，且没有付费记录和兑换记录，且会员未过期 -->
                AND u.role &gt; 1
                AND u.membership_expires_at IS NOT NULL 
                AND u.membership_expires_at &gt; NOW()
                AND u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY)
                AND NOT EXISTS (
                    SELECT 1 FROM `order` o 
                    WHERE o.user_id = u.id 
                    AND o.status = 1 
                    AND o.is_deleted = 0
                )
                AND NOT EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'expired'">
                <!-- 过期会员：VIP用户但会员已过期 -->
                AND u.role &gt; 1
                AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
            </when>
            <when test="membershipType == 'paidExpired'">
                <!-- 付费过期会员：有付费记录但会员已过期 -->
                AND u.role &gt; 1
                AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
                AND EXISTS (
                    SELECT 1 FROM `order` o 
                    WHERE o.user_id = u.id 
                    AND o.status = 1 
                    AND o.is_deleted = 0
                )
            </when>
            <when test="membershipType == 'exchangeExpired'">
                <!-- 兑换过期会员：有兑换记录但会员已过期 -->
                AND u.role &gt; 1
                AND (u.membership_expires_at IS NULL OR u.membership_expires_at &lt;= NOW())
                AND EXISTS (
                    SELECT 1 FROM gift_code_usage gcu 
                    WHERE gcu.user_id = u.id 
                    AND gcu.type = 2 
                    AND gcu.status = 1 
                    AND gcu.is_deleted = 0
                )
            </when>
            <!-- membershipType为null、空字符串或'all'时不添加额外筛选条件 -->
        </choose>
    </select>

    <!-- 获取用户详情（后台管理） -->
    <select id="selectUserDetailForAdmin" resultType="com.dora.dto.AdminUserDto$UserDetailResponse">
        SELECT 
            u.id,
            u.phone,
            u.username,
            u.real_username as realUsername,
            u.id_number as idNumber,
            u.role,
            CASE 
                WHEN u.role = 0 THEN '游客'
                WHEN u.role = 1 THEN '普通用户'
                WHEN u.role = 2 THEN 'VIP'
                WHEN u.role = 3 THEN 'SVIP'
                ELSE '未知'
            END as roleName,
            u.membership_expires_at as membershipExpiresAt,
            u.avatar_url as avatarUrl,
            u.is_verified as isVerified,
            u.invite_code as inviteCode,
            u.inviter_id as inviterId,
            inviter.username as inviterUsername,
            u.promotion_level as promotionLevel,
            u.points,
            u.points_expires_at as pointsExpiresAt,
            u.create_time as createTime,
            u.update_time as updateTime,
            (SELECT COUNT(*) FROM user fan WHERE fan.inviter_id = u.id AND fan.is_deleted = 0) as fanCount,
             (SELECT COUNT(*) FROM user fan 
             LEFT JOIN `order` o ON fan.id = o.user_id AND o.status = 1 AND o.is_deleted = 0
             WHERE fan.inviter_id = u.id AND fan.is_deleted = 0 AND fan.role &gt; 1 AND o.id IS NOT NULL) as paidFanCount,
            COALESCE((
                SELECT SUM(ubl.change_amount) 
                FROM user_balance_log ubl 
                WHERE ubl.user_id = u.id 
                  AND ubl.change_type = 'income' 
                  AND ubl.source_type = 'promotion' 
                  AND ubl.is_deleted = 0
            ), 0) as totalCommission
        FROM user u
        LEFT JOIN user inviter ON u.inviter_id = inviter.id AND inviter.is_deleted = 0
        WHERE u.id = #{userId} AND u.is_deleted = 0
    </select>

    <!-- 更新用户状态（启用/禁用） -->
    <update id="updateUserStatus">
        UPDATE user 
        SET 
            is_deleted = #{isDeleted},
            update_time = NOW()
        WHERE id = #{userId}
    </update>

    <!-- 更新用户积分 -->
    <update id="updateUserPoints">
        UPDATE user
        SET points = points + #{pointsChange}
        WHERE id = #{userId} AND points + #{pointsChange} >= 0
    </update>

    <select id="findById" resultType="com.dora.entity.User">
        SELECT * FROM user WHERE id = #{id} AND is_deleted = 0
    </select>

    <!-- 获取用户统计信息 -->
    <select id="selectUserStatistics" resultType="com.dora.dto.AdminUserDto$UserStatistics">
        SELECT 
            -- 基础用户统计
            (SELECT COUNT(*) FROM user WHERE is_deleted = 0) as totalUsers,
            (SELECT COUNT(*) FROM user WHERE DATE(create_time) = CURDATE() AND is_deleted = 0) as todayNewUsers,
            (SELECT COUNT(*) FROM user WHERE YEARWEEK(create_time) = YEARWEEK(NOW()) AND is_deleted = 0) as weekNewUsers,
            (SELECT COUNT(*) FROM user WHERE YEAR(create_time) = YEAR(NOW()) AND MONTH(create_time) = MONTH(NOW()) AND is_deleted = 0) as monthNewUsers,
            (SELECT COUNT(*) FROM user WHERE role = 1 AND is_deleted = 0) as normalUsers,
            
            -- VIP用户详细统计
            (SELECT COUNT(*) FROM user WHERE role = 2 AND is_deleted = 0) as vipUsers,
            (SELECT COUNT(*) FROM user u WHERE u.role = 2 AND u.is_deleted = 0 
             AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
             AND EXISTS (
                 SELECT 1 FROM `order` o 
                 WHERE o.user_id = u.id AND o.status = 1 AND o.is_deleted = 0
             )) as paidVipUsers,
            (SELECT COUNT(*) FROM user u WHERE u.role = 2 AND u.is_deleted = 0 
             AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
             AND EXISTS (
                 SELECT 1 FROM gift_code_usage gcu 
                 WHERE gcu.user_id = u.id AND gcu.type = 2 AND gcu.status = 1 AND gcu.is_deleted = 0
             )) as exchangeVipUsers,
            (SELECT COUNT(*) FROM user WHERE role = 2 AND is_deleted = 0 
             AND (membership_expires_at IS NULL OR membership_expires_at &lt;= NOW())) as expiredVipUsers,
            
            -- SVIP用户详细统计
            (SELECT COUNT(*) FROM user WHERE role = 3 AND is_deleted = 0) as svipUsers,
            (SELECT COUNT(*) FROM user u WHERE u.role = 3 AND u.is_deleted = 0 
             AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
             AND EXISTS (
                 SELECT 1 FROM `order` o 
                 WHERE o.user_id = u.id AND o.status = 1 AND o.is_deleted = 0
             )) as paidSvipUsers,
            (SELECT COUNT(*) FROM user u WHERE u.role = 3 AND u.is_deleted = 0 
             AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
             AND EXISTS (
                 SELECT 1 FROM gift_code_usage gcu 
                 WHERE gcu.user_id = u.id AND gcu.type = 2 AND gcu.status = 1 AND gcu.is_deleted = 0
             )) as exchangeSvipUsers,
            (SELECT COUNT(*) FROM user WHERE role = 3 AND is_deleted = 0 
             AND (membership_expires_at IS NULL OR membership_expires_at &lt;= NOW())) as expiredSvipUsers,
            
            -- 特殊会员类型统计
            (SELECT COUNT(*) FROM user u WHERE u.role &gt; 1 AND u.is_deleted = 0
             AND u.membership_expires_at IS NOT NULL AND u.membership_expires_at &gt; NOW()
             AND u.create_time &gt;= DATE_SUB(NOW(), INTERVAL 2 DAY)
             AND NOT EXISTS (
                 SELECT 1 FROM `order` o 
                 WHERE o.user_id = u.id AND o.status = 1 AND o.is_deleted = 0
             )
             AND NOT EXISTS (
                 SELECT 1 FROM gift_code_usage gcu 
                 WHERE gcu.user_id = u.id AND gcu.type = 2 AND gcu.status = 1 AND gcu.is_deleted = 0
             )) as giftMembers,
            (SELECT COUNT(*) FROM user u WHERE u.role &gt; 1 AND u.is_deleted = 0
             AND EXISTS (
                 SELECT 1 FROM gift_code_usage gcu 
                 WHERE gcu.user_id = u.id AND gcu.type = 2 AND gcu.status = 1 AND gcu.is_deleted = 0
             )
             AND NOT EXISTS (
                 SELECT 1 FROM `order` o 
                 WHERE o.user_id = u.id AND o.status = 1 AND o.is_deleted = 0
             )) as pureExchangeMembers,
            (SELECT COUNT(*) FROM user u WHERE u.role &gt; 1 AND u.is_deleted = 0
             AND EXISTS (
                 SELECT 1 FROM gift_code_usage gcu 
                 WHERE gcu.user_id = u.id AND gcu.type = 2 AND gcu.status = 1 AND gcu.is_deleted = 0
             )
             AND EXISTS (
                 SELECT 1 FROM `order` o 
                 WHERE o.user_id = u.id AND o.status = 1 AND o.is_deleted = 0
             )) as exchangeThenPaidMembers,
            
            -- 认证和推广统计
            (SELECT COUNT(*) FROM user WHERE is_verified = 1 AND is_deleted = 0) as verifiedUsers,
            (SELECT COUNT(*) FROM user WHERE is_verified = 0 AND is_deleted = 0) as unverifiedUsers,
            (SELECT COUNT(*) FROM user WHERE promotion_level &gt; 0 AND is_deleted = 0) as promotionUsers,
            
            -- 会员有效性统计
            (SELECT COUNT(*) FROM user WHERE role &gt; 1 AND is_deleted = 0 
             AND membership_expires_at IS NOT NULL AND membership_expires_at &gt; NOW()) as activeMembersTotal,
            (SELECT COUNT(*) FROM user WHERE role &gt; 1 AND is_deleted = 0 
             AND (membership_expires_at IS NULL OR membership_expires_at &lt;= NOW())) as expiredMembersTotal
    </select>

    <!-- 获取今日新增用户数 -->
    <select id="countTodayNewUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE DATE(create_time) = CURDATE() AND is_deleted = 0
    </select>

    <!-- 获取本周新增用户数 -->
    <select id="countWeekNewUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE YEARWEEK(create_time) = YEARWEEK(NOW()) AND is_deleted = 0
    </select>

    <!-- 获取本月新增用户数 -->
    <select id="countMonthNewUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE YEAR(create_time) = YEAR(NOW()) AND MONTH(create_time) = MONTH(NOW()) AND is_deleted = 0
    </select>

    <!-- 获取VIP用户数 -->
    <select id="countVipUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE role = 2 AND is_deleted = 0
    </select>

    <!-- 获取SVIP用户数 -->
    <select id="countSvipUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE role = 3 AND is_deleted = 0
    </select>

    <!-- 获取已实名认证用户数 -->
    <select id="countVerifiedUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE is_verified = 1 AND is_deleted = 0
    </select>

    <!-- 获取未实名认证用户数 -->
    <select id="countUnverifiedUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE is_verified = 0 AND is_deleted = 0
    </select>

    <!-- 获取有推广等级用户数 -->
    <select id="countPromotionUsers" resultType="int">
        SELECT COUNT(*) FROM user WHERE promotion_level &gt; 0 AND is_deleted = 0
    </select>

</mapper> 