<?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="go.kr.project.login.mapper.LoginMapper">

    <select id="selectUser" parameterType="String" resultType="LoginUserVO">
        select USER_ID
             , USER_ACNT
             , USER_NM
             , PASSWD
             , EMP_NO
             , GENDER
             , ZIP
             , ADDR
             , DADDR
             , AREA_NO
             , EML_ADDR
             , ORG_CD
             , USER_GROUP_ID
             , NSTT_CD
             , POS_NM
             , CRTFC_DN
             , LOCK_YN
             , LOCK_CNT
             , LOCK_DTTM
             , REG_DTTM
             , RGTR
             , MDFCN_DTTM
             , MDFR
             , USER_STATUS_CD
             , FXNO
             , TELNO
             , MBL_TELNO
             , BRDT
             , DEPT_CD
             , USE_YN
             , RSDNT_NO
             , PASSWD_INIT_YN
        from tb_user
        where USER_ID = #{userId}
    </select>

    <select id="selectUserByAcnt" parameterType="String" resultType="LoginUserVO">
        select u.USER_ID
             , u.USER_ACNT
             , u.USER_NM
             , u.PASSWD
             , u.EMP_NO
             , u.GENDER
             , u.ZIP
             , u.ADDR
             , u.DADDR
             , u.AREA_NO
             , u.EML_ADDR
             , u.ORG_CD
             , g.GROUP_NM as USER_GROUP_ID_NM
             , u.USER_GROUP_ID
             , cd_nstt.CD_NM as STT_CD_NAME
             , u.NSTT_CD
             , u.POS_NM
             , u.CRTFC_DN
             , u.LOCK_YN
             , u.LOCK_CNT
             , u.LOCK_DTTM
             , u.REG_DTTM
             , u.RGTR
             , u.MDFCN_DTTM
             , u.MDFR
             , u.USER_STATUS_CD
             , cd_userStatus.CD_NM as USER_STATUS_NM
             , u.FXNO
             , u.TELNO
             , u.MBL_TELNO
             , u.BRDT
             , u.DEPT_CD
             , cd_dept.CD_NM as DEPT_CD_NM
             , u.USE_YN
             , u.RSDNT_NO
             , cd_org.CD_NM as ORG_CD_NM
             , u.PASSWD_INIT_YN
          from tb_user u
          left join tb_group g on u.USER_GROUP_ID = g.GROUP_ID
          left join tb_cd_detail cd_nstt on u.NSTT_CD = cd_nstt.CD_ID and cd_nstt.CD_GROUP_ID = 'NSTT_CD'
          left join tb_cd_detail cd_userStatus on u.USER_STATUS_CD = cd_userStatus.CD_ID and cd_userStatus.CD_GROUP_ID = 'USER_STATUS_CD'
          left join tb_cd_detail cd_dept on u.DEPT_CD = cd_dept.CD_ID and cd_dept.CD_GROUP_ID = 'DEPT_CD'
          left join tb_cd_detail cd_org on u.ORG_CD = cd_org.CD_ID and cd_org.CD_GROUP_ID = 'ORG_CD'
        where u.USER_ACNT = #{userAcnt}
          and u.USE_YN = 'Y'
          and u.USER_STATUS_CD != 'DELETED' <!-- 삭제는 제외 -->
    </select>

    <!-- 비밀번호 잠금 정보 업데이트 -->
    <update id="updatePasswordLock" parameterType="LoginUserVO">
        UPDATE tb_user
        SET LOCK_CNT = #{lockCnt}
          , LOCK_YN = #{lockYn}
          <if test="lockDttm != null">
          , LOCK_DTTM = #{lockDttm}
          </if>
          , MDFCN_DTTM = NOW()
        WHERE USER_ID = #{userId}
    </update>

    <!-- 비밀번호 잠금 해제 정보 업데이트 -->
    <update id="updatePasswordUnlock" parameterType="LoginUserVO">
        UPDATE tb_user
        SET LOCK_YN = 'N'
          , LOCK_CNT = 0
          , LOCK_DTTM = NULL
          , MDFCN_DTTM = NOW()
        WHERE USER_ID = #{userId}
    </update>

    <!-- 그룹 정보 조회 -->
    <select id="selectGroup" parameterType="String" resultType="GroupVO">
        select GROUP_ID
             , GROUP_NM
             , GROUP_DC
             , SORT_ORDR
             , USE_YN
             , REG_DTTM
             , RGTR
             , MDFCN_DTTM
             , MDFR
        from tb_group
        where GROUP_ID = #{userGroupId}
    </select>

    <!-- 그룹 ID로 역할 목록 조회 -->
    <select id="selectRolesByUserGroupId" parameterType="String" resultType="RoleVO">
        SELECT 
            r.ROLE_ID,
            r.ROLE_NM,
            r.ROLE_DC,
            r.SORT_ORDR,
            r.USE_YN,
            r.REG_DTTM,
            r.RGTR,
            r.MDFCN_DTTM,
            r.MDFR
        FROM 
            tb_role r
        JOIN 
            tb_group_role gr ON r.ROLE_ID = gr.ROLE_ID
        WHERE 
            gr.GROUP_ID = #{userGroupId}
            AND r.USE_YN = 'Y'
        ORDER BY 
            r.SORT_ORDR, r.ROLE_ID
    </select>

    <!-- 역할 ID 목록으로 메뉴 목록을 트리 구조로 조회 -->
    <select id="selectMenusByRoleIds" resultType="MenuVO">
        select
            distinct m.MENU_ID ,
                     m.MENU_NM ,
                     m.MENU_DC ,
                     m.UPPER_MENU_ID ,
                     m.MENU_LEVEL ,
                     m.SORT_ORDR ,
                     m.MENU_URL ,
                     m.URL_PATTERN ,
                     m.MENU_ICON ,
                     m.USE_YN ,
                     m.VIEW_YN ,
                     m.REG_DTTM ,
                     m.RGTR ,
                     m.MDFCN_DTTM ,
                     m.MDFR ,
                     cast(m.MENU_ID as CHAR(1000)) as MENU_PATH ,
                     cast(m.MENU_NM as CHAR(1000)) as MENU_PATH_NM ,
                     cast(CONCAT(LPAD(m.MENU_LEVEL, 2, '0'), ':', LPAD(m.SORT_ORDR, 3, '0'), ':', m.MENU_ID) as CHAR(1000)) as MENU_ALL_SORT
        from
            tb_menu m
                join tb_role_menu rm on
                m.MENU_ID = rm.MENU_ID
        WHERE rm.ROLE_ID IN
        <foreach collection="roleIds" item="roleId" open="(" separator="," close=")">
            #{roleId}
        </foreach>
          and m.USE_YN = 'Y'
          and m.UPPER_MENU_ID = #{upperMenuId}
        order by SORT_ORDR
    </select>

    <!-- 사용자 세션 정보 저장 -->
    <insert id="insertUserSession" parameterType="UserSessionVO">
        INSERT INTO tb_user_session (
            SESSION_ID,
            USER_ID,
            USER_ACNT,
            LOGIN_DTTM,
            LAST_ACCESS_DTTM,
            IP_ADDR,
            USER_AGENT
        ) VALUES (
            #{sessionId},
            #{userId},
            #{userAcnt},
            #{loginDttm},
            #{lastAccessDttm},
            #{ipAddr},
            #{userAgent}
        )
    </insert>

    <!-- 사용자 ID로 세션 정보 조회 -->
    <select id="selectUserSessionByUserId" parameterType="String" resultType="UserSessionVO">
        SELECT
            SESSION_ID as sessionId,
            USER_ID as userId,
            USER_ACNT as userAcnt,
            LOGIN_DTTM as loginDttm,
            LAST_ACCESS_DTTM as lastAccessDttm,
            IP_ADDR as ipAddr,
            USER_AGENT as userAgent
        FROM
            tb_user_session
        WHERE
            USER_ID = #{userId}
    </select>

    <!-- 세션 ID로 세션 정보 조회 -->
    <select id="selectUserSessionBySessionId" parameterType="String" resultType="UserSessionVO">
        SELECT
            SESSION_ID,
            USER_ID,
            USER_ACNT,
            LOGIN_DTTM,
            LAST_ACCESS_DTTM,
            IP_ADDR,
            USER_AGENT
        FROM
            tb_user_session
        WHERE
            SESSION_ID = #{sessionId}
    </select>

    <!-- 사용자 ID로 세션 정보 삭제 -->
    <delete id="deleteUserSessionByUserId" parameterType="String">
        DELETE FROM tb_user_session
        WHERE USER_ID = #{userId}
    </delete>

    <!-- 세션 ID로 세션 정보 삭제 -->
    <delete id="deleteUserSessionBySessionId" parameterType="String">
        DELETE FROM tb_user_session
        WHERE SESSION_ID = #{sessionId}
    </delete>

    <!-- 세션 정보 업데이트 -->
    <update id="updateUserSession" parameterType="UserSessionVO">
        UPDATE tb_user_session
        SET
            LAST_ACCESS_DTTM = #{lastAccessDttm}
        WHERE
            SESSION_ID = #{sessionId}
    </update>
</mapper>
