<?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.liquidnet.service.adam.mapper.AdamCaomeiPassportMapper">

    <select id="selectPassportAdminList" resultType="com.liquidnet.service.adam.dto.AdamCaomeiPassportListDto">
        SELECT
            p.mid AS mid,
            p.passport_no AS passportNo,
            IFNULL(ui.nickname, '') AS nickname,
            IFNULL(arn.name, '') AS realName,
            CASE
                WHEN u.mobile IS NULL OR u.mobile = '' THEN ''
                WHEN CHAR_LENGTH(u.mobile) &gt;= 11 THEN CONCAT(SUBSTRING(u.mobile, 1, 3), '****', SUBSTRING(u.mobile, 8, 4))
                ELSE u.mobile
            END AS mobile,
            CASE
                WHEN arn.id_card IS NULL OR arn.id_card = '' THEN ''
                WHEN CHAR_LENGTH(arn.id_card) &gt; 8 THEN CONCAT(SUBSTRING(arn.id_card, 1, 4), '****', SUBSTRING(arn.id_card, CHAR_LENGTH(arn.id_card) - 3, 4))
                ELSE '****'
            END AS idCard,
            p.status AS bindStatus,
            IFNULL(bc.cnt, 0) AS claimedBadgeCount
        FROM adam_caomei_passport p
        LEFT JOIN adam_user u ON p.user_id = u.uid AND p.user_id &lt;&gt; ''
        LEFT JOIN adam_user_info ui ON p.user_id = ui.uid
        LEFT JOIN (
            SELECT r.uid, r.name, r.id_card
            FROM adam_real_name r
            INNER JOIN (
                SELECT uid, MAX(mid) AS max_mid
                FROM adam_real_name
                WHERE state = 1
                GROUP BY uid
            ) rm ON r.uid = rm.uid AND r.mid = rm.max_mid AND r.state = 1
        ) arn ON p.user_id = arn.uid
        LEFT JOIN (
            SELECT user_id, COUNT(1) AS cnt
            FROM adam_caomei_user_badge
            GROUP BY user_id
        ) bc ON p.user_id = bc.user_id
        <where>
            <if test="passportNo != null and passportNo != ''">
                AND p.passport_no LIKE CONCAT('%', #{passportNo}, '%')
            </if>
            <if test="userName != null and userName != ''">
                AND (ui.nickname LIKE CONCAT('%', #{userName}, '%') OR arn.name LIKE CONCAT('%', #{userName}, '%'))
            </if>
            <if test="mobile != null and mobile != ''">
                AND u.mobile LIKE CONCAT('%', #{mobile}, '%')
            </if>
            <if test="idCard != null and idCard != ''">
                AND arn.id_card LIKE CONCAT('%', #{idCard}, '%')
            </if>
            <if test="bindStatus != null">
                AND p.status = #{bindStatus}
            </if>
        </where>
        <!-- 已绑定优先展示，避免仅按更新时间导致已绑定记录长期沉底 -->
        ORDER BY (p.status = 1) DESC, p.updated_at DESC, p.mid DESC
    </select>

    <select id="selectUserBadgesByUid" resultType="com.liquidnet.service.adam.dto.AdamCaomeiPassportUserBadgeDto">
        SELECT
            ub.badge_id AS badgeId,
            IFNULL(b.name, '') AS badgeName,
            IFNULL(b.sub_title, '') AS subTitle,
            IFNULL(b.icon, '') AS icon,
            IFNULL(b.share_text, '') AS shareText,
            IFNULL(b.type, 0) AS type,
            IFNULL(b.performance_id, '') AS performanceId,
            ub.created_at AS claimedAt,
            ub.source AS source
        FROM adam_caomei_user_badge ub
        LEFT JOIN adam_caomei_badge b ON ub.badge_id = b.badge_id
        WHERE ub.user_id = #{uid}
        ORDER BY ub.created_at DESC
    </select>
</mapper>
