<?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="org.xyzh.system.mapper.user.TbSysUserInfoMapper">

    <!-- 结果映射 -->
    <resultMap id="BaseResultMap" type="org.xyzh.common.dto.sys.TbSysUserInfoDTO">
        <!-- 用户信息字段 -->
        <id column="user_id" property="userId" jdbcType="VARCHAR"/>
        <result column="avatar" property="avatar" jdbcType="VARCHAR"/>
        <result column="gender" property="gender" jdbcType="INTEGER"/>
        <result column="username" property="username" jdbcType="VARCHAR"/>
        <result column="level" property="level" jdbcType="INTEGER"/>
        <result column="id_card" property="idCard" jdbcType="VARCHAR"/>
        <result column="address" property="address" jdbcType="VARCHAR"/>
        <!-- 基础字段 -->
        <result column="optsn" property="optsn" jdbcType="VARCHAR"/>
        <result column="creator" property="creator" jdbcType="VARCHAR"/>
        <result column="remark" property="remark" jdbcType="VARCHAR"/>
        <result column="create_time" property="createTime" jdbcType="DATE"/>
        <result column="update_time" property="updateTime" jdbcType="DATE"/>
        <result column="delete_time" property="deleteTime" jdbcType="DATE"/>
        <result column="deleted" property="deleted" jdbcType="BOOLEAN"/>
    </resultMap>

    <!-- 基础列 -->
    <sql id="Base_Column_List">
        user_id, avatar, gender, username, level, id_card, address,
        optsn, creator, remark, create_time, update_time, delete_time, deleted
    </sql>

    <!-- 插入系统用户信息（按表字段 + 默认值动态列，仅修改 insert） -->
    <insert id="insertUserInfo" parameterType="org.xyzh.common.dto.sys.TbSysUserInfoDTO">
        INSERT INTO sys.tb_sys_user_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <!-- 必填字段（NOT NULL 且无默认值）：optsn, user_id -->
            optsn,
            user_id,
            <!-- 可空/有默认值字段：按是否有入参动态拼接 -->
            <if test="avatar != null and avatar != ''">avatar,</if>
            <if test="gender != null">gender,</if>
            <if test="username != null and username != ''">username,</if>
            <if test="level != null">level,</if>
            <if test="idCard != null and idCard != ''">id_card,</if>
            <if test="address != null and address != ''">address,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="deleteTime != null">delete_time,</if>
            <if test="deleted != null">deleted,</if>
        </trim>
        VALUES
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <!-- 必填字段对应的值 -->
            #{optsn},
            #{userId},
            <!-- 可空/有默认值字段对应的值 -->
            <if test="avatar != null and avatar != ''">#{avatar},</if>
            <if test="gender != null">#{gender},</if>
            <if test="username != null and username != ''">#{username},</if>
            <if test="level != null">#{level},</if>
            <if test="idCard != null and idCard != ''">#{idCard},</if>
            <if test="address != null and address != ''">#{address},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="deleteTime != null">#{deleteTime},</if>
            <if test="deleted != null">#{deleted},</if>
        </trim>
    </insert>

    <!-- 更新系统用户信息 -->
    <update id="updateUserInfo" parameterType="org.xyzh.common.dto.sys.TbSysUserInfoDTO">
        UPDATE sys.tb_sys_user_info
        <set>
            <if test="avatar != null">
                avatar = #{avatar},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="level != null">
                level = #{level},
            </if>
            <if test="idCard != null">
                id_card = #{idCard},
            </if>
            <if test="address != null">
                address = #{address},
            </if>
            <if test="remark != null">
                remark = #{remark},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime},
            </if>
        </set>
        WHERE user_id = #{userId}
        <if test="deleted != null">
            AND deleted = #{deleted}
        </if>
    </update>

    <!-- 删除系统用户信息 -->
    <update id="deleteUserInfo" parameterType="org.xyzh.common.dto.sys.TbSysUserInfoDTO">
        UPDATE sys.tb_sys_user_info
        SET deleted = true,
            delete_time = NOW()
        WHERE user_id = #{userId}
    </update>

    <!-- 根据用户ID查询系统用户信息 -->
    <select id="getUserInfoById" resultMap="BaseResultMap" parameterType="java.lang.String">
        SELECT
        <include refid="Base_Column_List"/>
        FROM sys.tb_sys_user_info
        WHERE user_id = #{userId}
        AND (deleted IS NULL OR deleted = false)
    </select>

    <!-- 根据条件查询系统用户信息列表 -->
    <select id="getUserInfoByFilter" resultMap="BaseResultMap" parameterType="org.xyzh.common.dto.sys.TbSysUserInfoDTO">
        SELECT
        <include refid="Base_Column_List"/>
        FROM sys.tb_sys_user_info
        <where>
            <if test="filter.userId != null and filter.userId != ''">
                AND user_id = #{filter.userId}
            </if>
            <if test="filter.username != null and filter.username != ''">
                AND username LIKE CONCAT('%', #{filter.username}, '%')
            </if>
            <if test="filter.idCard != null and filter.idCard != ''">
                AND id_card = #{filter.idCard}
            </if>
            <if test="filter.gender != null">
                AND gender = #{filter.gender}
            </if>
            AND (deleted IS NULL OR deleted = false)
        </where>
        ORDER BY create_time DESC
    </select>

    <!-- 根据条件查询系统用户信息分页列表 -->
    <select id="getUserInfoPageByFilter" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List"/>
        FROM sys.tb_sys_user_info
        <where>
            <if test="filter.userId != null and filter.userId != ''">
                AND user_id = #{filter.userId}
            </if>
            <if test="filter.username != null and filter.username != ''">
                AND username LIKE CONCAT('%', #{filter.username}, '%')
            </if>
            <if test="filter.idCard != null and filter.idCard != ''">
                AND id_card = #{filter.idCard}
            </if>
            <if test="filter.gender != null">
                AND gender = #{filter.gender}
            </if>
            AND (deleted IS NULL OR deleted = false)
        </where>
        ORDER BY create_time DESC
        LIMIT #{pageParam.pageSize} OFFSET #{pageParam.offset}
    </select>

    <!-- 根据条件查询系统用户信息数量 -->
    <select id="getUserInfoCount" resultType="java.lang.Integer" parameterType="org.xyzh.common.dto.sys.TbSysUserInfoDTO">
        SELECT COUNT(1)
        FROM sys.tb_sys_user_info
        <where>
            <if test="filter.userId != null and filter.userId != ''">
                AND user_id = #{filter.userId}
            </if>
            <if test="filter.username != null and filter.username != ''">
                AND username LIKE CONCAT('%', #{filter.username}, '%')
            </if>
            <if test="filter.idCard != null and filter.idCard != ''">
                AND id_card = #{filter.idCard}
            </if>
            <if test="filter.gender != null">
                AND gender = #{filter.gender}
            </if>
            AND (deleted IS NULL OR deleted = false)
        </where>
    </select>

</mapper>

