<?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 s.user_id AS stakeholder_user_id,
           s.user_nm AS username,
           s.role,
           s.channel,
           s.access_scope,
           u.user_id AS app_user_id
    FROM poc_2.kb_stakeholders s
    JOIN cb_app_user u ON u.stakeholder_user_id = s.user_id
    WHERE u.active = 'Y'
    ORDER BY CASE s.role WHEN '지점장' THEN 1 WHEN '설계사' THEN 2 ELSE 3 END,
             s.channel,
             s.user_nm,
             s.user_id
  </select>

  <select id="findTokenSubject" resultType="com.cloudhandson.vpdbackoffice.domain.stakeholder.StakeholderTokenSubject">
    SELECT s.user_id AS stakeholder_user_id,
           s.user_nm AS username,
           s.role,
           s.channel,
           s.access_scope,
           u.user_id AS app_user_id
    FROM poc_2.kb_stakeholders s
    JOIN cb_app_user u ON u.stakeholder_user_id = s.user_id
    WHERE s.user_id = #{stakeholderUserId,jdbcType=VARCHAR}
      AND u.active = 'Y'
  </select>
</mapper>
