<?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.UserMapper">
  <select id="findAll" resultType="com.cloudhandson.vpdbackoffice.domain.user.AppUser">
    SELECT user_id, user_name AS username, employee_no AS emp_no, dept_code,
           can_read_contents, active AS active_yn
    FROM sg_app_user
    ORDER BY username
  </select>

  <select id="findById" resultType="com.cloudhandson.vpdbackoffice.domain.user.AppUser">
    SELECT user_id, user_name AS username, employee_no AS emp_no, dept_code,
           can_read_contents, active AS active_yn
    FROM sg_app_user WHERE user_id = #{userId}
  </select>

  <select id="findUserRoles" resultType="com.cloudhandson.vpdbackoffice.domain.user.UserRoleView">
    SELECT u.user_id,
           u.user_name AS username,
           r.role_id,
           r.role_name
    FROM sg_user_role ur
    JOIN sg_app_user u ON u.user_id = ur.user_id
    JOIN sg_app_role r ON r.role_id = ur.role_id
    ORDER BY u.user_name, r.role_name
  </select>

  <select id="nextUserId" resultType="long">
    SELECT NVL(MAX(user_id), 0) + 1 FROM sg_app_user
  </select>

  <insert id="insertUser">
    INSERT INTO sg_app_user (user_id, login_id, user_name, employee_no, dept_code, can_read_contents, active)
    VALUES (#{userId}, LOWER(#{command.empNo}), #{command.username}, #{command.empNo},
            #{command.deptCode}, 'N', 'Y')
  </insert>

  <update id="updateActive">
    UPDATE sg_app_user SET active = #{activeYn} WHERE user_id = #{userId}
  </update>

  <insert id="insertUserRole">
    INSERT INTO sg_user_role (user_id, role_id) VALUES (#{userId}, #{roleId})
  </insert>

  <delete id="deleteUserRole">
    DELETE FROM sg_user_role WHERE user_id = #{userId}
      AND role_id = #{roleId}
  </delete>
</mapper>
