<?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.lesingle.modules.sys.mapper.SysUserMapper">

    <!-- 查询用户的权限码列表 -->
    <select id="selectPermissionsByUserId" resultType="java.lang.String">
        SELECT DISTINCT p.code
        FROM t_sys_permission p
        JOIN t_sys_role_permission rp ON rp.permission_id = p.id
        JOIN t_sys_user_role ur ON ur.role_id = rp.role_id
        WHERE ur.user_id = #{userId}
          AND p.valid_state = 1
    </select>

    <!-- 查询用户的角色码列表 -->
    <select id="selectRolesByUserId" resultType="java.lang.String">
        SELECT DISTINCT r.code
        FROM t_sys_role r
        JOIN t_sys_user_role ur ON ur.role_id = r.id
        WHERE ur.user_id = #{userId}
          AND r.valid_state = 1
    </select>

    <!-- 查询用户列表（含角色和租户信息） -->
    <select id="selectUserPage" resultType="java.util.Map">
        SELECT
            u.id, u.tenant_id AS tenantId, u.username, u.nickname, u.email, u.phone,
            u.wx_openid AS wxOpenid, u.user_source AS userSource, u.user_type AS userType,
            u.city, u.birthday, u.gender, u.avatar, u.organization, u.status,
            u.valid_state AS validState, u.create_time AS createTime, u.modify_time AS updateTime,
            t.name AS tenantName, t.code AS tenantCode, t.tenant_type AS tenantType, t.is_super AS tenantIsSuper
        FROM t_sys_user u
        LEFT JOIN t_sys_tenant t ON t.id = u.tenant_id
        <where>
            u.valid_state = 1 AND (1=1)
            <if test="params.tenantId != null">
                AND u.tenant_id = #{params.tenantId}
            </if>
            <if test="params.keyword != null and params.keyword != ''">
                AND (u.username LIKE CONCAT('%', #{params.keyword}, '%')
                  OR u.nickname LIKE CONCAT('%', #{params.keyword}, '%')
                  OR u.email LIKE CONCAT('%', #{params.keyword}, '%')
                  OR u.phone LIKE CONCAT('%', #{params.keyword}, '%'))
            </if>
            <if test="params.userSource != null and params.userSource != ''">
                AND u.user_source = #{params.userSource}
            </if>
            <if test="params.status != null and params.status != ''">
                AND u.status = #{params.status}
            </if>
            <if test="params.filterTenantId != null">
                AND u.tenant_id = #{params.filterTenantId}
            </if>
            <!-- 机构租户 ID 列表过滤（排除超管、公众、评委） -->
            <if test="params.orgTenantIdsFilter != null and params.orgTenantIdsFilter.size() > 0">
                AND u.tenant_id IN
                <foreach collection="params.orgTenantIdsFilter" item="tenantId" open="(" separator="," close=")">
                    #{tenantId}
                </foreach>
            </if>
            <!-- 按租户 code 过滤（用于 public、judge 等固定租户） -->
            <if test="params.tenantCodeFilter != null and params.tenantCodeFilter != ''">
                AND t.code = #{params.tenantCodeFilter}
            </if>
            <!-- 超管按 userType 过滤时，映射到租户类型 -->
            <if test="params.tenantTypeFilter != null and params.tenantTypeFilter != ''">
                AND t.tenant_type = #{params.tenantTypeFilter}
            </if>
            <if test="params.isSuperTenantFilter != null">
                AND t.is_super = #{params.isSuperTenantFilter}
            </if>
        </where>
        ORDER BY u.create_time DESC
    </select>

    <!-- 查询用户详情（含角色、租户信息） -->
    <select id="selectUserDetail" resultType="java.util.Map">
        SELECT
            u.id, u.tenant_id AS tenantId, u.username, u.nickname, u.email, u.phone,
            u.wx_openid AS wxOpenid, u.user_source AS userSource, u.user_type AS userType,
            u.city, u.birthday, u.gender, u.avatar, u.organization, u.status,
            u.valid_state AS validState, u.create_time AS createTime, u.modify_time AS updateTime,
            t.id AS tenantId, t.name AS tenantName, t.code AS tenantCode,
            t.tenant_type AS tenantType, t.is_super AS tenantIsSuper
        FROM t_sys_user u
        LEFT JOIN t_sys_tenant t ON t.id = u.tenant_id
        WHERE u.id = #{userId}
          AND u.valid_state = 1
          AND (1=1)
          <if test="tenantId != null and !isSuperTenant">
              AND u.tenant_id = #{tenantId}
          </if>
    </select>

    <!-- 根据用户名查询（含角色和权限，用于登录） -->
    <select id="selectByUsernameWithRoles" resultType="java.util.Map">
        SELECT
            u.id, u.tenant_id AS tenantId, u.username, u.password, u.nickname,
            u.email, u.avatar, u.status, u.valid_state AS validState
        FROM t_sys_user u
        WHERE u.username = #{username}
          AND u.valid_state = 1
          AND (1=1)
          <if test="tenantId != null">
              AND u.tenant_id = #{tenantId}
          </if>
        LIMIT 1
    </select>

</mapper>
