<?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.testor.module.iam.dao.SysUserDao">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.testor.biz.sys.user.model.domain.SysUser">
        <id column="user_id" property="userId"/>
        <result column="org_id" property="orgId"/>
        <result column="login_name" property="loginName"/>
        <result column="login_pwd" property="loginPwd"/>
        <result column="user_name" property="userName"/>
        <result column="user_type" property="userType"/>
        <result column="photo_url" property="photoUrl"/>
        <result column="login_ip" property="loginIp"/>
        <result column="login_date" property="loginDate"/>
        <result column="pwd_security_level" property="pwdSecurityLevel"/>
        <result column="sex" property="sex"/>
        <result column="age" property="age"/>
        <result column="address" property="address"/>
        <result column="zip_code" property="zipCode"/>
        <result column="phone_no" property="phoneNo"/>
        <result column="fax" property="fax"/>
        <result column="email" property="email"/>
        <result column="user_key_pid" property="userKeyPid"/>
        <result column="dept_id" property="deptId"/>
        <result column="post_id" property="postId"/>
        <result column="deputy_post_id" property="deputyPostId"/>
        <result column="org_name" property="orgName"/>
        <result column="post_name" property="postName"/>

        <!-- 继承父类的字段 -->
        <result column="create_by" property="createBy"/>
        <result column="create_date" property="createDate"/>
        <result column="update_by" property="updateBy"/>
        <result column="update_date" property="updateDate"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        user_id, org_id, login_name, login_pwd, user_name, user_type, photo_url,
        login_ip, login_date, pwd_security_level, sex, age, address, zip_code,
        phone_no, fax, email, user_key_pid, dept_id, post_id, deputy_post_id,
        org_name, post_name, create_by, create_date, update_by, update_date,
        del_flag, remark
    </sql>

    <!-- 查询有权限审批当前组织数据的用户 -->
    <select id="selectApprovalUsersByOrgId" resultMap="BaseResultMap">
        SELECT DISTINCT u.*
        FROM t_sys_user u
                 JOIN t_sys_user_role ur ON u.user_id = ur.user_id
                 JOIN t_sys_role r ON ur.role_id = r.role_id
                 JOIN t_sys_org o ON u.org_id = o.org_id
        WHERE r.role_name IN ('安全管理部门', '作业所在部门', '用户管理员', '系统管理员')
          AND o.status != '1'
          AND o.is_dept = '0'
          AND EXISTS (
            SELECT 1
            FROM t_sys_org o2
            WHERE o2.org_id = #{orgId}
          AND (o2.parent_ids LIKE CONCAT('%', o.org_id, '%') OR o2.org_id = o.org_id)
            )
    </select>

    <!-- 查询有权限审批特定承包商记录的用户 -->
    <select id="selectApprovalUsersForContractor" resultMap="BaseResultMap">
        WITH contractor_orgs AS (
            SELECT unnest(string_to_array(tcl.org_id, ',')) AS org_id
            FROM t_contractor_allow tca
                     JOIN t_contractor_ledger tcl ON tcl.id = tca.contractor_id
            WHERE tca.id = #{contractorId}
        )
        SELECT DISTINCT u.*
        FROM t_sys_user u
                 JOIN t_sys_user_role ur ON u.user_id = ur.user_id
                 JOIN t_sys_role r ON ur.role_id = r.role_id
                 JOIN t_sys_org o ON u.org_id = o.org_id
                 JOIN contractor_orgs co ON o.org_id = co.org_id
        WHERE r.role_name IN ('安全管理部门', '作业所在部门', '用户管理员', '系统管理员')
          AND o.status != '1'
          AND o.is_dept = '0'
          AND EXISTS (
            SELECT 1
            FROM t_sys_org o2
            WHERE o2.org_id = #{orgId}
          AND (o2.parent_ids LIKE CONCAT('%', o.org_id, '%') OR o2.org_id = o.org_id)
            )
    </select>

    <!-- 检查用户是否有审批角色 -->
    <select id="checkUserHasApprovalRole" resultType="boolean">
        SELECT COUNT(*) > 0
        FROM t_sys_user u
                 JOIN t_sys_user_role ur ON u.user_id = ur.user_id
                 JOIN t_sys_role r ON ur.role_id = r.role_id
        WHERE u.user_id = #{userId}
          AND r.role_name IN ('安全管理部门', '作业所在部门', '用户管理员', '系统管理员')
    </select>

    <!-- 检查组织访问权限 -->
    <select id="checkOrgAccess" resultType="boolean">
        SELECT EXISTS (
            SELECT 1
            FROM t_sys_org o1
                     JOIN t_sys_org o2 ON o1.org_id = #{sourceOrgId}
            WHERE (o2.parent_ids LIKE CONCAT('%', o1.org_id, '%') OR o2.org_id = o1.org_id)
              AND o1.org_id = #{targetOrgId}
        )
    </select>

</mapper>
