<?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.menu.mapper.MenuMapper">

    <!-- 메뉴 목록을 트리 구조로 조회 -->
    <select id="selectMenuTreeList" parameterType="MenuSearchVO" resultType="MenuVO">
        WITH RECURSIVE menu_tree AS (
            SELECT 
                MENU_ID
                , MENU_NM
                , MENU_DC
                , UPPER_MENU_ID
                , MENU_LEVEL
                , SORT_ORDR
                , MENU_URL
                , URL_PATTERN
                , MENU_ICON
                , USE_YN
                , VIEW_YN
                , REG_DTTM
                , RGTR
                , MDFCN_DTTM
                , MDFR
                , CAST(MENU_ID AS CHAR(1000)) AS MENU_PATH
                , CAST(MENU_NM AS CHAR(1000)) AS MENU_PATH_NM
                , CAST(CONCAT(LPAD(MENU_LEVEL, 2, '0'), ':', LPAD(SORT_ORDR, 3, '0'), ':', MENU_ID) AS CHAR(1000)) AS MENU_ALL_SORT
            FROM tb_menu
            WHERE MENU_ID = 'ROOT'

            UNION ALL

            SELECT 
                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
                , CONCAT(mt.MENU_PATH, '>', m.MENU_ID) AS MENU_PATH
                , CONCAT(mt.MENU_PATH_NM, '>', m.MENU_NM) AS MENU_PATH_NM
                , CONCAT(mt.MENU_ALL_SORT, '>', LPAD(m.MENU_LEVEL, 2, '0'), ':', LPAD(m.SORT_ORDR, 3, '0'), ':', m.MENU_ID) AS MENU_ALL_SORT
        FROM tb_menu m
            INNER JOIN menu_tree mt ON m.UPPER_MENU_ID = mt.MENU_ID
            <if test='searchUseYn != null and searchUseYn != ""'>
                WHERE m.USE_YN = #{searchUseYn}
            </if>
            <if test='searchKeyword != null and searchKeyword != ""'>
                AND (
                m.MENU_ID LIKE CONCAT('%', #{searchKeyword}, '%')
                OR m.MENU_NM LIKE CONCAT('%', #{searchKeyword}, '%')
                OR m.MENU_DC LIKE CONCAT('%', #{searchKeyword}, '%')
                )
            </if>
        )
        SELECT * FROM menu_tree
        ORDER BY MENU_ALL_SORT
    </select>

    <!-- 하위 메뉴 존재 여부 확인 -->
    <select id="countChildMenus" parameterType="String" resultType="int">
        select COUNT(*)
        from tb_menu
        where UPPER_MENU_ID = #{menuId}
    </select>

    <!-- 메뉴 등록 -->
    <insert id="insertMenu" parameterType="MenuVO">
        INSERT INTO tb_menu (
            MENU_ID,
            MENU_NM,
            MENU_DC,
            UPPER_MENU_ID,
            MENU_LEVEL,
            SORT_ORDR,
            MENU_URL,
            URL_PATTERN,
            MENU_ICON,
            USE_YN,
            VIEW_YN,
            REG_DTTM,
            RGTR,
            MDFCN_DTTM,
            MDFR
        ) VALUES (
            #{menuId},
            #{menuNm},
            #{menuDc},
            #{upperMenuId},
            #{menuLevel},
            #{sortOrdr},
            #{menuUrl},
            #{urlPattern},
            #{menuIcon},
            #{useYn},
            #{viewYn},
            NOW(),
            #{rgtr},
            NOW(),
            #{rgtr}
        )
    </insert>

    <!-- 메뉴 수정 -->
    <update id="updateMenu" parameterType="MenuVO">
        UPDATE tb_menu
        SET
            MENU_NM = #{menuNm},
            <if test="menuDc != null">
            MENU_DC = #{menuDc},
            </if>
            <if test="upperMenuId != null">
            UPPER_MENU_ID = #{upperMenuId},
            </if>
            MENU_LEVEL = #{menuLevel},
            SORT_ORDR = #{sortOrdr},
            <if test="menuUrl != null">
            MENU_URL = #{menuUrl},
            </if>
            <if test="urlPattern != null">
                URL_PATTERN = #{urlPattern},
            </if>
            <if test="menuIcon != null">
            MENU_ICON = #{menuIcon},
            </if>
            USE_YN = #{useYn},
            VIEW_YN = #{viewYn},
            MDFCN_DTTM = NOW(),
            MDFR = #{mdfr}
        WHERE MENU_ID = #{menuId}
    </update>

    <!-- 메뉴 삭제 -->
    <delete id="deleteMenu" parameterType="String">
        DELETE FROM tb_menu
        WHERE MENU_ID = #{menuId}
    </delete>

    <!-- 메뉴가 사용 중인지 확인 -->
    <select id="checkMenuInUse" parameterType="String" resultType="boolean">
        SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
        FROM tb_role_menu
        WHERE MENU_ID = #{menuId}
        LIMIT 1
    </select>

    <!-- 메뉴를 사용 중인 역할, 그룹, 사용자 정보 조회 -->
    <select id="getMenuUsageInfo" parameterType="String" resultType="java.util.HashMap">
        SELECT 
            r.ROLE_ID, 
            r.ROLE_NM, 
            g.GROUP_ID,
            g.GROUP_NM, 
            u.USER_ID, 
            u.USER_NM
        FROM 
            tb_role_menu rm
        JOIN 
            tb_role r ON rm.ROLE_ID = r.ROLE_ID
        LEFT JOIN 
            tb_group_role gr ON r.ROLE_ID = gr.ROLE_ID
        LEFT JOIN 
            tb_group g ON gr.GROUP_ID = g.GROUP_ID
        LEFT JOIN 
            tb_user u ON g.GROUP_ID = u.USER_GROUP_ID
        WHERE 
            rm.MENU_ID = #{menuId}
    </select>

    <!-- 메뉴 ID 자동 생성 -->
    <select id="generateMenuIdByQuery" resultType="String">
        SELECT 
            CASE 
                WHEN #{menuLevel} = 0 AND (#{upperMenuId} IS NULL OR #{upperMenuId} = '') THEN 'ROOT'
                ELSE CONCAT('MENU', LPAD(NEXTVAL(seq_menu_id), 16, '0'))
            END
    </select>
</mapper>
