<?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.system.auth.mapper.AuthMapper">
    <!-- 역할에 할당된 메뉴 목록 조회 -->
    <select id="selectRoleMenuList" parameterType="String" resultType="RoleMenuVO">
        SELECT rm.ROLE_ID
             , rm.MENU_ID
             , rm.REG_DTTM
             , rm.RGTR
             , r.ROLE_NM
             , m.MENU_NM
             , m.MENU_DC
             , m.MENU_URL
             , m.MENU_ICON
             , m.MENU_LEVEL
             , m.SORT_ORDR
             , m.UPPER_MENU_ID
             , (SELECT MENU_NM FROM tb_menu WHERE MENU_ID = m.UPPER_MENU_ID) AS UPPER_MENU_NM
             , m.USE_YN
        FROM tb_role_menu rm
        JOIN tb_role r ON rm.ROLE_ID = r.ROLE_ID
        JOIN tb_menu m ON rm.MENU_ID = m.MENU_ID
        WHERE rm.ROLE_ID = #{roleId}
        ORDER BY m.MENU_LEVEL, m.SORT_ORDR
    </select>

    <!-- 역할에 할당되지 않은 메뉴 목록 조회 -->
    <select id="selectUnassignedMenuList" parameterType="String" resultType="MenuVO">
        SELECT m.MENU_ID
             , m.MENU_NM
             , m.MENU_DC
             , m.UPPER_MENU_ID
             , m.MENU_LEVEL
             , m.SORT_ORDR
             , m.MENU_URL
             , m.MENU_ICON
             , m.USE_YN
             , m.REG_DTTM
             , m.RGTR
             , m.MDFCN_DTTM
             , m.MDFR
             , (SELECT MENU_NM FROM tb_menu WHERE MENU_ID = m.UPPER_MENU_ID) AS UPPER_MENU_NM
        FROM tb_menu m
        WHERE m.USE_YN = 'Y'
          AND m.MENU_ID NOT IN (
              SELECT MENU_ID FROM tb_role_menu WHERE ROLE_ID = #{roleId}
          )
        ORDER BY m.MENU_LEVEL, m.SORT_ORDR
    </select>

    <!-- 역할에 메뉴 할당 -->
    <insert id="insertRoleMenu" parameterType="RoleMenuVO">
        INSERT INTO tb_role_menu (
            ROLE_ID,
            MENU_ID,
            REG_DTTM,
            RGTR
        ) VALUES (
            #{roleId},
            #{menuId},
            NOW(),
            #{rgtr}
        )
    </insert>

    <!-- 역할에서 메뉴 제거 -->
    <delete id="deleteRoleMenu">
        DELETE FROM tb_role_menu
        WHERE ROLE_ID = #{roleId}
          AND MENU_ID = #{menuId}
    </delete>

    <!-- 역할에서 모든 메뉴 제거 -->
    <delete id="deleteAllRoleMenu" parameterType="String">
        DELETE FROM tb_role_menu
        WHERE ROLE_ID = #{roleId}
    </delete>

    <!-- 그룹에 할당된 역할 목록 조회 -->
    <select id="selectGroupRoleList" parameterType="String" resultType="GroupRoleVO">
        SELECT gr.GROUP_ID
             , gr.ROLE_ID
             , gr.REG_DTTM
             , gr.RGTR
             , g.GROUP_NM
             , g.GROUP_DC
             , r.ROLE_NM
             , r.ROLE_DC
             , r.USE_YN
        FROM tb_group_role gr
        JOIN tb_group g ON gr.GROUP_ID = g.GROUP_ID
        JOIN tb_role r ON gr.ROLE_ID = r.ROLE_ID
        WHERE gr.GROUP_ID = #{groupId}
        ORDER BY g.SORT_ORDR
    </select>

    <!-- 그룹에 할당되지 않은 역할 목록 조회 -->
    <select id="selectUnassignedRoleList" parameterType="String" resultType="RoleVO">
        SELECT r.ROLE_ID
             , r.ROLE_NM
             , r.ROLE_DC
             , r.USE_YN
             , r.REG_DTTM
             , r.RGTR
             , r.MDFCN_DTTM
             , r.MDFR
        FROM tb_role r
        WHERE r.USE_YN = 'Y'
          AND r.ROLE_ID NOT IN (
              SELECT ROLE_ID FROM tb_group_role WHERE GROUP_ID = #{groupId}
          )
        ORDER BY r.SORT_ORDR
    </select>

    <!-- 그룹에 역할 할당 -->
    <insert id="insertGroupRole" parameterType="GroupRoleVO">
        INSERT INTO tb_group_role (
            GROUP_ID,
            ROLE_ID,
            REG_DTTM,
            RGTR
        ) VALUES (
            #{groupId},
            #{roleId},
            NOW(),
            #{rgtr}
        )
    </insert>

    <!-- 그룹에서 역할 제거 -->
    <delete id="deleteGroupRole">
        DELETE FROM tb_group_role
        WHERE GROUP_ID = #{groupId}
          AND ROLE_ID = #{roleId}
    </delete>

    <!-- 그룹에서 모든 역할 제거 -->
    <delete id="deleteAllGroupRole" parameterType="String">
        DELETE FROM tb_group_role
        WHERE GROUP_ID = #{groupId}
    </delete>

    <!-- 그룹 목록 조회 -->
    <select id="selectGroupList" parameterType="GroupSearchVO" resultType="GroupVO">
        select GROUP_ID
             , GROUP_NM
             , GROUP_DC
             , SORT_ORDR
             , USE_YN
             , REG_DTTM
             , RGTR
             , MDFCN_DTTM
             , MDFR
        from tb_group
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
            GROUP_ID like CONCAT('%', #{searchKeyword}, '%')
            or GROUP_NM like CONCAT('%', #{searchKeyword}, '%')
            or GROUP_DC like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
        order by SORT_ORDR asc
    </select>

    <!-- 역할 목록 조회 -->
    <select id="selectRoleList" parameterType="RoleSearchVO" resultType="RoleVO">
        select ROLE_ID
             , ROLE_NM
             , ROLE_DC
             , SORT_ORDR
             , USE_YN
             , REG_DTTM
             , RGTR
             , MDFCN_DTTM
             , MDFR
        from tb_role
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and USE_YN = #{searchUseYn}
        </if>
        <if test='searchKeyword != null and searchKeyword != ""'>
            and (
            ROLE_ID like CONCAT('%', #{searchKeyword}, '%')
            or ROLE_NM like CONCAT('%', #{searchKeyword}, '%')
            or ROLE_DC like CONCAT('%', #{searchKeyword}, '%')
            )
        </if>
        order by SORT_ORDR asc
    </select>

    <!-- 메뉴 목록 조회 -->
    <select id="selectMenuList" parameterType="MenuVO" resultType="MenuVO">
        select a.MENU_ID
             , a.MENU_NM
             , a.MENU_DC
             , a.UPPER_MENU_ID
             , a.MENU_LEVEL
             , a.SORT_ORDR
             , a.MENU_URL
             , a.URL_PATTERN
             , a.MENU_ICON
             , a.USE_YN
             , a.VIEW_YN
             , a.REG_DTTM
             , a.RGTR
             , a.MDFCN_DTTM
             , a.MDFR
             , b.MENU_NM as UPPER_MENU_NM
        from tb_menu a
        left join tb_menu b on a.UPPER_MENU_ID = b.MENU_ID
        where 1 = 1
        <if test='searchUseYn != null and searchUseYn != ""'>
            and a.USE_YN = #{searchUseYn}
        </if>
        order by a.MENU_LEVEL, a.SORT_ORDR
    </select>

</mapper>
