<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cloudhandson.vpdbackoffice.mapper.StakeholderMapper">
  <select id="findTokenSubjects" resultType="com.cloudhandson.vpdbackoffice.domain.stakeholder.StakeholderTokenSubject">
    SELECT TO_CHAR(u.user_id) AS stakeholder_user_id,
           u.user_name AS username,
           COALESCE((
             SELECT MIN(r.role_name)
             FROM sg_user_role user_role
             JOIN sg_app_role r ON r.role_id = user_role.role_id
             WHERE user_role.user_id = u.user_id
           ), 'DATA_AI_OPERATOR') AS role,
           'BACKOFFICE' AS channel,
           u.dept_code AS access_scope,
           u.user_id AS app_user_id
    FROM sg_app_user u
    WHERE u.active = 'Y'
    ORDER BY role, u.dept_code, u.user_name, u.user_id
  </select>

  <select id="findTokenSubject" resultType="com.cloudhandson.vpdbackoffice.domain.stakeholder.StakeholderTokenSubject">
    SELECT TO_CHAR(u.user_id) AS stakeholder_user_id,
           u.user_name AS username,
           COALESCE((
             SELECT MIN(r.role_name)
             FROM sg_user_role user_role
             JOIN sg_app_role r ON r.role_id = user_role.role_id
             WHERE user_role.user_id = u.user_id
           ), 'DATA_AI_OPERATOR') AS role,
           'BACKOFFICE' AS channel,
           u.dept_code AS access_scope,
           u.user_id AS app_user_id
    FROM sg_app_user u
    WHERE TO_CHAR(u.user_id) = #{stakeholderUserId,jdbcType=VARCHAR}
      AND u.active = 'Y'
  </select>
</mapper>
