<?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.ruoyi.web.modules.account.mapper.PlatformAdminMapper">

    <resultMap id="PlatformAdminRowResult" type="com.ruoyi.web.modules.account.domain.PlatformAdminRow">
        <id property="userId" column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="nickName" column="nick_name"/>
        <result property="phonenumber" column="phonenumber"/>
        <result property="email" column="email"/>
        <result property="avatar" column="avatar"/>
        <result property="createTime" column="create_time"/>
        <result property="roleNames" column="role_names"/>
    </resultMap>

    <sql id="platformAdminRoleExists">
        and exists (
            select 1
            from sys_user_role ur
            inner join sys_role r on ur.role_id = r.role_id and r.del_flag = '0' and r.status = '0'
            where ur.user_id = u.user_id
              and r.role_key in
              <foreach collection="listRoleKeys" item="roleKey" open="(" separator="," close=")">
                  #{roleKey}
              </foreach>
        )
    </sql>

    <select id="selectList" resultMap="PlatformAdminRowResult">
        select u.user_id,
               u.user_name,
               u.nick_name,
               u.phonenumber,
               u.email,
               u.avatar,
               u.create_time,
               (
                   select group_concat(distinct r2.role_name order by r2.role_sort separator '、')
                   from sys_user_role ur2
                   inner join sys_role r2 on ur2.role_id = r2.role_id and r2.del_flag = '0' and r2.status = '0'
                   where ur2.user_id = u.user_id
                     and r2.role_key in
                     <foreach collection="listRoleKeys" item="roleKey" open="(" separator="," close=")">
                         #{roleKey}
                     </foreach>
               ) as role_names
        from sys_user u
        where u.del_flag = '0'
        <include refid="platformAdminRoleExists"/>
        <if test="keyword != null and keyword != ''">
            and (
                u.nick_name like concat('%', #{keyword}, '%')
                or u.user_name like concat('%', #{keyword}, '%')
            )
        </if>
        order by u.create_time desc
    </select>

    <select id="selectByUserId" resultMap="PlatformAdminRowResult">
        select u.user_id,
               u.user_name,
               u.nick_name,
               u.phonenumber,
               u.email,
               u.avatar,
               u.create_time,
               (
                   select group_concat(distinct r2.role_name order by r2.role_sort separator '、')
                   from sys_user_role ur2
                   inner join sys_role r2 on ur2.role_id = r2.role_id and r2.del_flag = '0' and r2.status = '0'
                   where ur2.user_id = u.user_id
                     and r2.role_key in
                     <foreach collection="listRoleKeys" item="roleKey" open="(" separator="," close=")">
                         #{roleKey}
                     </foreach>
               ) as role_names
        from sys_user u
        where u.user_id = #{userId} and u.del_flag = '0'
        <include refid="platformAdminRoleExists"/>
    </select>

    <select id="existsInListScope" resultType="int">
        select count(1)
        from sys_user u
        where u.user_id = #{userId} and u.del_flag = '0'
        <include refid="platformAdminRoleExists"/>
    </select>

    <select id="selectRoleOptions" resultType="com.ruoyi.web.modules.account.vo.PlatformAdminRoleOptionVO">
        select role_id as roleId, role_name as roleName, role_key as roleKey
        from sys_role
        where del_flag = '0' and status = '0'
          and role_key in
          <foreach collection="assignableRoleKeys" item="roleKey" open="(" separator="," close=")">
              #{roleKey}
          </foreach>
        order by role_sort
    </select>

    <select id="selectPlatformRolesByUserId" resultType="com.ruoyi.web.modules.account.vo.PlatformAdminRoleOptionVO">
        select r.role_id as roleId, r.role_name as roleName, r.role_key as roleKey
        from sys_user_role ur
        inner join sys_role r on ur.role_id = r.role_id and r.del_flag = '0' and r.status = '0'
        where ur.user_id = #{userId}
          and r.role_key in
          <foreach collection="listRoleKeys" item="roleKey" open="(" separator="," close=")">
              #{roleKey}
          </foreach>
        order by r.role_sort
    </select>

    <select id="selectAllRolesByUserId" resultType="com.ruoyi.web.modules.account.vo.PlatformAdminRoleOptionVO">
        select r.role_id as roleId, r.role_name as roleName, r.role_key as roleKey
        from sys_user_role ur
        inner join sys_role r on ur.role_id = r.role_id and r.del_flag = '0' and r.status = '0'
        where ur.user_id = #{userId}
        order by r.role_sort
    </select>

    <select id="countUsersWithRoleKey" resultType="int">
        select count(distinct u.user_id)
        from sys_user u
        inner join sys_user_role ur on u.user_id = ur.user_id
        inner join sys_role r on ur.role_id = r.role_id and r.del_flag = '0' and r.status = '0'
        where u.del_flag = '0' and r.role_key = #{roleKey}
    </select>

    <select id="countUsersWithRoleKeyAmong" resultType="int">
        select count(distinct u.user_id)
        from sys_user u
        inner join sys_user_role ur on u.user_id = ur.user_id
        inner join sys_role r on ur.role_id = r.role_id and r.del_flag = '0' and r.status = '0'
        where u.del_flag = '0'
          and r.role_key = #{roleKey}
          and u.user_id in
          <foreach collection="userIds" item="userId" open="(" separator="," close=")">
              #{userId}
          </foreach>
    </select>

    <select id="userHasRoleKey" resultType="int">
        select count(1)
        from sys_user_role ur
        inner join sys_role r on ur.role_id = r.role_id and r.del_flag = '0' and r.status = '0'
        where ur.user_id = #{userId} and r.role_key = #{roleKey}
    </select>
</mapper>
