<?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 e.employee_id AS user_id,
           e.full_name AS username,
           e.employee_code AS emp_no,
           t.team_code AS dept_code,
           'N' AS can_read_contents,
           CASE WHEN e.employment_status = 'ACTIVE' THEN 'Y' ELSE 'N' END AS active_yn
    FROM hmm_hr_employees e
    JOIN hmm_org_teams t ON t.team_id = e.team_id
    ORDER BY username
  </select>

  <select id="findById" resultType="com.cloudhandson.vpdbackoffice.domain.user.AppUser">
    SELECT e.employee_id AS user_id,
           e.full_name AS username,
           e.employee_code AS emp_no,
           t.team_code AS dept_code,
           'N' AS can_read_contents,
           CASE WHEN e.employment_status = 'ACTIVE' THEN 'Y' ELSE 'N' END AS active_yn
    FROM hmm_hr_employees e
    JOIN hmm_org_teams t ON t.team_id = e.team_id
    WHERE e.employee_id = #{userId}
  </select>

  <select id="findUserRoles" resultType="com.cloudhandson.vpdbackoffice.domain.user.UserRoleView">
    SELECT u.employee_id AS user_id,
           u.full_name AS username,
           r.role_id,
           r.role_name
    FROM hmm_employee_access_roles ur
    JOIN hmm_hr_employees u ON u.employee_id = ur.employee_id
    JOIN hmm_access_roles r ON r.role_id = ur.role_id
    ORDER BY u.full_name, r.role_name
  </select>

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

  <insert id="insertUser">
    INSERT INTO hmm_hr_employees (
      employee_id, employee_code, full_name, email, job_title, team_id, hire_date, employment_status
    )
    SELECT #{userId},
           #{command.empNo},
           #{command.username},
           LOWER(#{command.empNo}) || '@example.test',
           'HMM Demo Employee',
           t.team_id,
           TRUNC(SYSDATE),
           'ACTIVE'
    FROM hmm_org_teams t
    WHERE t.team_code = #{command.deptCode}
  </insert>

  <update id="updateActive">
    UPDATE hmm_hr_employees
    SET employment_status = CASE WHEN #{activeYn} = 'Y' THEN 'ACTIVE' ELSE 'INACTIVE' END
    WHERE employee_id = #{userId}
  </update>

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

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