<?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.role.mapper.RoleMapper">
    <!-- 역할 정보 조회 -->
    <select id="selectRole" parameterType="String" resultType="RoleVO">
        select ROLE_ID
             , ROLE_NM
             , ROLE_DC
             , SORT_ORDR
             , USE_YN
             , REG_DTTM
             , RGTR
             , MDFCN_DTTM
             , MDFR
        from tb_role
        where ROLE_ID = #{selectRoleId}
    </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>
        <if test="sortAscending != null and sortColumn != null and sortColumn != ''">
            <choose>
                <when test="sortColumn == 'roleId'">order by ROLE_ID</when>
                <when test="sortColumn == 'roleNm'">order by ROLE_NM</when>
                <when test="sortColumn == 'sortOrdr'">order by SORT_ORDR</when>
                <otherwise>
                    order by SORT_ORDR, ROLE_ID
                </otherwise>
            </choose>
            <choose>
                <when test="sortAscending == true">ASC</when>
                <when test="sortAscending == false">DESC</when>
                <otherwise>
                    ASC
                </otherwise>
            </choose>
        </if>
        <if test='pagingYn != null and pagingYn == "Y"'>
            LIMIT #{startIndex}, #{perPage}
        </if>
    </select>

    <!-- 역할 목록 총 개수 조회 -->
    <select id="selectRoleListTotalCount" parameterType="RoleSearchVO" resultType="int">
        select COUNT(*)
        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>
    </select>

    <!-- 역할 등록 -->
    <insert id="insertRole" parameterType="RoleVO">
        INSERT INTO tb_role (
            ROLE_ID,
            ROLE_NM,
            ROLE_DC,
            SORT_ORDR,
            USE_YN,
            REG_DTTM,
            RGTR,
            MDFCN_DTTM,
            MDFR
        ) VALUES (
            #{roleId},
            #{roleNm},
            #{roleDc},
            #{sortOrdr},
            #{useYn},
            NOW(),
            #{rgtr},
            NOW(),
            #{rgtr}
        )
    </insert>

    <!-- 역할 수정 -->
    <update id="updateRole" parameterType="RoleVO">
        UPDATE tb_role
        SET
            ROLE_NM = #{roleNm},
            <if test="roleDc != null">
            ROLE_DC = #{roleDc},
            </if>
            SORT_ORDR = #{sortOrdr},
            USE_YN = #{useYn},
            MDFCN_DTTM = NOW(),
            MDFR = #{mdfr}
        WHERE ROLE_ID = #{roleId}
    </update>

    <!-- 역할 삭제 -->
    <delete id="deleteRole" parameterType="String">
        DELETE FROM tb_role
        WHERE ROLE_ID = #{roleId}
    </delete>

    <!-- 역할-메뉴 매핑 정보 삭제 -->
    <delete id="deleteRoleMenus" parameterType="String">
        DELETE FROM tb_role_menu
        WHERE ROLE_ID = #{roleId}
    </delete>

    <!-- 그룹-역할 매핑 정보 삭제 -->
    <delete id="deleteGroupRoles" parameterType="String">
        DELETE FROM tb_group_role
        WHERE ROLE_ID = #{roleId}
    </delete>

    <!-- 역할이 사용 중인지 확인 -->
    <select id="checkRoleInUse" parameterType="String" resultType="boolean">
        SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
        FROM (
            SELECT 1
            FROM tb_group_role
            WHERE ROLE_ID = #{roleId}
        ) AS role_usage
        LIMIT 1
    </select>

    <!-- 역할 ID 중복 확인 -->
    <select id="checkRoleIdDuplicate" parameterType="String" resultType="boolean">
        SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
        FROM tb_role
        WHERE ROLE_ID = #{roleId}
    </select>

    <!-- 역할이 사용 중인 그룹 정보와 사용자 정보 조회 -->
    <select id="getRoleUsageInfo" parameterType="String" resultType="java.util.HashMap">
        SELECT 
            g.GROUP_ID, 
            g.GROUP_NM, 
            u.USER_ID, 
            u.USER_NM
        FROM 
            tb_group_role gr
        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 
            gr.ROLE_ID = #{roleId}
    </select>

</mapper>