<?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="com.ga.common.code.CommonCodeMapper">

    <resultMap id="GroupMap" type="com.ga.common.code.CommonCodeGroupVO"/>
    <resultMap id="CodeMap"  type="com.ga.common.code.CommonCodeVO"/>

    <sql id="GroupColumns">
        group_code, group_name, description, is_system, is_active, sort_order,
        created_at, created_by, updated_at, updated_by
    </sql>

    <sql id="CodeColumns">
        code_id, group_code, code, code_name, code_name_en, code_desc,
        attr1, attr2, attr3, ref_code, sort_order, is_active,
        created_at, created_by, updated_at, updated_by
    </sql>

    <!-- ========== 그룹 ========== -->
    <select id="selectGroupList" resultMap="GroupMap">
        SELECT <include refid="GroupColumns"/>
        FROM common_code_group
        <where>
            <if test="keyword != null and keyword != ''">
                AND (group_code LIKE CONCAT('%', #{keyword}, '%')
                  OR group_name LIKE CONCAT('%', #{keyword}, '%'))
            </if>
        </where>
        ORDER BY sort_order, group_code
    </select>

    <select id="selectGroup" resultMap="GroupMap">
        SELECT <include refid="GroupColumns"/>
        FROM common_code_group WHERE group_code = #{groupCode}
    </select>

    <insert id="insertGroup" parameterType="com.ga.common.code.CommonCodeGroupVO">
        INSERT INTO common_code_group (
            group_code, group_name, description, is_system, is_active, sort_order,
            created_at, created_by
        ) VALUES (
            #{groupCode}, #{groupName}, #{description},
            COALESCE(#{isSystem},'N'), COALESCE(#{isActive},'Y'),
            COALESCE(#{sortOrder},0), #{createdAt}, #{createdBy}
        )
    </insert>

    <update id="updateGroup" parameterType="com.ga.common.code.CommonCodeGroupVO">
        UPDATE common_code_group SET
            group_name  = #{groupName},
            description = #{description},
            is_active   = #{isActive},
            sort_order  = #{sortOrder},
            updated_at  = #{updatedAt},
            updated_by  = #{updatedBy}
        WHERE group_code = #{groupCode}
    </update>

    <delete id="deleteGroup">
        DELETE FROM common_code_group WHERE group_code = #{groupCode} AND is_system = 'N'
    </delete>

    <!-- ========== 상세 코드 ========== -->
    <select id="selectCodes" resultMap="CodeMap">
        SELECT <include refid="CodeColumns"/>
        FROM common_code
        WHERE group_code = #{groupCode}
        ORDER BY sort_order, code
    </select>

    <select id="selectActiveCodes" resultMap="CodeMap">
        SELECT <include refid="CodeColumns"/>
        FROM common_code
        WHERE group_code = #{groupCode} AND is_active = 'Y'
        ORDER BY sort_order, code
    </select>

    <select id="selectCode" resultMap="CodeMap">
        SELECT <include refid="CodeColumns"/>
        FROM common_code WHERE code_id = #{codeId}
    </select>

    <select id="selectByGroupAndCode" resultMap="CodeMap">
        SELECT <include refid="CodeColumns"/>
        FROM common_code WHERE group_code = #{groupCode} AND code = #{code}
    </select>

    <insert id="insertCode" parameterType="com.ga.common.code.CommonCodeVO" useGeneratedKeys="true" keyProperty="codeId">
        INSERT INTO common_code (
            group_code, code, code_name, code_name_en, code_desc,
            attr1, attr2, attr3, ref_code, sort_order, is_active,
            created_at, created_by
        ) VALUES (
            #{groupCode}, #{code}, #{codeName}, #{codeNameEn}, #{codeDesc},
            #{attr1}, #{attr2}, #{attr3}, #{refCode},
            COALESCE(#{sortOrder},0), COALESCE(#{isActive},'Y'),
            #{createdAt}, #{createdBy}
        )
    </insert>

    <update id="updateCode" parameterType="com.ga.common.code.CommonCodeVO">
        UPDATE common_code SET
            code_name    = #{codeName},
            code_name_en = #{codeNameEn},
            code_desc    = #{codeDesc},
            attr1 = #{attr1}, attr2 = #{attr2}, attr3 = #{attr3},
            ref_code     = #{refCode},
            sort_order   = #{sortOrder},
            is_active    = #{isActive},
            updated_at   = #{updatedAt},
            updated_by   = #{updatedBy}
        WHERE code_id = #{codeId}
    </update>

    <delete id="deleteCode">
        DELETE FROM common_code WHERE code_id = #{codeId}
    </delete>

    <select id="existsByGroupAndCode" resultType="int">
        SELECT COUNT(*) FROM common_code WHERE group_code = #{groupCode} AND code = #{code}
    </select>

</mapper>
