<?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="multilang">

    <!-- ═══════════════════════════════════════════════════════════════════════
         Language
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangLanguageList" parameterType="map" resultType="map">
        SELECT
            LANG_CODE
            , LANG_NAME
            , LANG_NATIVE
            , IS_ACTIVE
            , SORT_ORDER
            , CREATED_DATE
            , CREATED_BY
            , UPDATED_DATE
            , UPDATED_BY
        FROM LANGUAGE_MASTER
        ORDER BY SORT_ORDER ASC, LANG_CODE ASC
    </select>

    <select id="getMultilangLanguageInfo" parameterType="map" resultType="map">
        SELECT
            LANG_CODE
            , LANG_NAME
            , LANG_NATIVE
            , IS_ACTIVE
            , SORT_ORDER
            , CREATED_DATE
            , CREATED_BY
            , UPDATED_DATE
            , UPDATED_BY
        FROM LANGUAGE_MASTER
        WHERE LANG_CODE = #{lang_code}
    </select>

    <insert id="insertMultilangLanguage" parameterType="map" useGeneratedKeys="false">
        INSERT INTO LANGUAGE_MASTER (
            LANG_CODE
            , LANG_NAME
            , LANG_NATIVE
            , IS_ACTIVE
            , SORT_ORDER
            , CREATED_BY
            , UPDATED_BY
        ) VALUES (
            #{lang_code}
            , #{lang_name}
            , #{lang_native}
            , #{is_active}
            , #{sort_order}
            , #{created_by}
            , #{updated_by}
        )
    </insert>

    <update id="updateMultilangLanguage" parameterType="map">
        UPDATE LANGUAGE_MASTER
        <set>
            <if test="lang_name != null">LANG_NAME = #{lang_name},</if>
            <if test="lang_native != null">LANG_NATIVE = #{lang_native},</if>
            <if test="is_active != null">IS_ACTIVE = #{is_active},</if>
            <if test="sort_order != null">SORT_ORDER = #{sort_order},</if>
            UPDATED_BY = #{updated_by},
            UPDATED_DATE = NOW()
        </set>
        WHERE LANG_CODE = #{lang_code}
    </update>

    <update id="updateMultilangLanguageStatus" parameterType="map">
        UPDATE LANGUAGE_MASTER
        SET
            IS_ACTIVE = #{new_status}
            , UPDATED_BY = #{updated_by}
            , UPDATED_DATE = NOW()
        WHERE LANG_CODE = #{lang_code}
    </update>

    <delete id="deleteMultilangTextByLangCode" parameterType="map">
        DELETE FROM MULTI_LANG_TEXT
        WHERE LANG_CODE = #{lang_code}
    </delete>

    <delete id="deleteMultilangLanguage" parameterType="map">
        DELETE FROM LANGUAGE_MASTER
        WHERE LANG_CODE = #{lang_code}
    </delete>

    <!-- ═══════════════════════════════════════════════════════════════════════
         LangKey
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangKeyList" parameterType="map" resultType="map">
        SELECT
            KEY_ID
            , COMPANY_CODE
            , USAGE_NOTE
            , LANG_KEY
            , DESCRIPTION
            , IS_ACTIVE
            , CATEGORY_ID
            , CREATED_DATE
            , CREATED_BY
            , UPDATED_DATE
            , UPDATED_BY
        FROM MULTI_LANG_KEY_MASTER
        WHERE 1=1
        <if test="filter_company_code != null and filter_company_code != ''">
            AND (COMPANY_CODE = #{filter_company_code} OR COMPANY_CODE = '*')
        </if>
        <if test="menu_code != null and menu_code != ''">
            AND USAGE_NOTE = #{menu_code}
        </if>
        <if test="category_id != null">
            AND CATEGORY_ID IN (
                WITH RECURSIVE CATEGORY_TREE AS (
                    SELECT CATEGORY_ID
                    FROM MULTI_LANG_CATEGORY
                    WHERE CATEGORY_ID = #{category_id}
                    UNION ALL
                    SELECT C.CATEGORY_ID
                    FROM MULTI_LANG_CATEGORY C
                    INNER JOIN CATEGORY_TREE CT ON C.PARENT_ID = CT.CATEGORY_ID
                )
                SELECT CATEGORY_ID FROM CATEGORY_TREE
            )
        </if>
        <if test="search_text != null and search_text != ''">
            AND (LANG_KEY ILIKE '%' || #{search_text} || '%'
              OR DESCRIPTION ILIKE '%' || #{search_text} || '%'
              OR USAGE_NOTE ILIKE '%' || #{search_text} || '%')
        </if>
        ORDER BY COMPANY_CODE ASC, USAGE_NOTE ASC, LANG_KEY ASC
    </select>

    <select id="getMultilangKeyInfo" parameterType="map" resultType="map">
        SELECT
            KEY_ID
            , COMPANY_CODE
            , USAGE_NOTE
            , LANG_KEY
            , DESCRIPTION
            , IS_ACTIVE
            , CATEGORY_ID
            , KEY_MEANING
            , BASE_KEY_ID
            , CREATED_DATE
            , CREATED_BY
            , UPDATED_DATE
            , UPDATED_BY
        FROM MULTI_LANG_KEY_MASTER
        WHERE KEY_ID = #{key_id}
    </select>

    <select id="getMultilangKeyByCompanyAndKey" parameterType="map" resultType="map">
        SELECT KEY_ID
        FROM MULTI_LANG_KEY_MASTER
        WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
          AND LANG_KEY = #{lang_key}
    </select>

    <select id="getMultilangKeyByCompanyAndKeyExclude" parameterType="map" resultType="map">
        SELECT KEY_ID
        FROM MULTI_LANG_KEY_MASTER
        WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
          AND LANG_KEY = #{lang_key}
          AND KEY_ID != #{key_id}
    </select>

    <insert id="insertMultilangKey" parameterType="map" useGeneratedKeys="true" keyProperty="key_id" keyColumn="key_id">
        INSERT INTO MULTI_LANG_KEY_MASTER (
            COMPANY_CODE
            , USAGE_NOTE
            , LANG_KEY
            , DESCRIPTION
            , IS_ACTIVE
            , CREATED_BY
            , UPDATED_BY
        ) VALUES (
            #{company_code}
            , #{menu_name}
            , #{lang_key}
            , #{description}
            , #{is_active}
            , #{created_by}
            , #{updated_by}
        )
    </insert>

    <insert id="insertMultilangKeyWithCategory" parameterType="map" useGeneratedKeys="true" keyProperty="key_id" keyColumn="key_id">
        INSERT INTO MULTI_LANG_KEY_MASTER (
            COMPANY_CODE
            , LANG_KEY
            , CATEGORY_ID
            , KEY_MEANING
            , USAGE_NOTE
            , DESCRIPTION
            , IS_ACTIVE
            , CREATED_BY
            , UPDATED_BY
        ) VALUES (
            #{company_code}
            , #{lang_key}
            , #{category_id}
            , #{key_meaning}
            , #{usage_note}
            , #{description}
            , 'Y'
            , #{created_by}
            , #{created_by}
        )
    </insert>

    <update id="updateMultilangKey" parameterType="map">
        UPDATE MULTI_LANG_KEY_MASTER
        <set>
            <if test="company_code != null">COMPANY_CODE = #{company_code},</if>
            <if test="menu_name != null">USAGE_NOTE = #{menu_name},</if>
            <if test="lang_key != null">LANG_KEY = #{lang_key},</if>
            <if test="description != null">DESCRIPTION = #{description},</if>
            UPDATED_BY = #{updated_by}
        </set>
        WHERE KEY_ID = #{key_id}
    </update>

    <update id="updateMultilangKeyStatus" parameterType="map">
        UPDATE MULTI_LANG_KEY_MASTER
        SET
            IS_ACTIVE = #{new_status}
            , UPDATED_BY = #{updated_by}
        WHERE KEY_ID = #{key_id}
    </update>

    <delete id="deleteMultilangTextByKeyId" parameterType="map">
        DELETE FROM MULTI_LANG_TEXT
        WHERE KEY_ID = #{key_id}
    </delete>

    <delete id="deleteMultilangKey" parameterType="map">
        DELETE FROM MULTI_LANG_KEY_MASTER
        WHERE KEY_ID = #{key_id}
    </delete>

    <!-- ═══════════════════════════════════════════════════════════════════════
         LangText
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangTextList" parameterType="map" resultType="map">
        SELECT
            TEXT_ID
            , KEY_ID
            , LANG_CODE
            , LANG_TEXT
            , IS_ACTIVE
            , CREATED_DATE
            , CREATED_BY
            , UPDATED_DATE
            , UPDATED_BY
        FROM MULTI_LANG_TEXT
        WHERE KEY_ID = #{key_id}
          AND IS_ACTIVE = 'Y'
        ORDER BY LANG_CODE ASC
    </select>

    <insert id="insertMultilangText" parameterType="map">
        INSERT INTO MULTI_LANG_TEXT (
            KEY_ID
            , LANG_CODE
            , LANG_TEXT
            , IS_ACTIVE
            , CREATED_BY
            , UPDATED_BY
        ) VALUES (
            #{key_id}
            , #{lang_code}
            , #{lang_text}
            , #{is_active}
            , #{created_by}
            , #{updated_by}
        )
    </insert>

    <insert id="upsertMultilangText" parameterType="map">
        INSERT INTO MULTI_LANG_TEXT (
            KEY_ID
            , LANG_CODE
            , LANG_TEXT
            , IS_ACTIVE
            , CREATED_DATE
            , CREATED_BY
        ) VALUES (
            #{key_id}
            , #{lang_code}
            , #{lang_text}
            , 'Y'
            , NOW()
            , 'system'
        )
        ON CONFLICT (KEY_ID, LANG_CODE)
        DO UPDATE SET LANG_TEXT = #{lang_text}, UPDATED_DATE = NOW()
    </insert>

    <!-- ═══════════════════════════════════════════════════════════════════════
         Text lookup
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangUserText" parameterType="map" resultType="map">
        SELECT MLT.LANG_TEXT
        FROM MULTI_LANG_TEXT MLT
        INNER JOIN MULTI_LANG_KEY_MASTER MLKM ON MLT.KEY_ID = MLKM.KEY_ID
        WHERE MLT.LANG_CODE = #{user_lang}
          AND MLT.IS_ACTIVE = 'Y'
          AND (MLKM.COMPANY_CODE = #{company_code} OR MLKM.COMPANY_CODE = '*')
          AND MLKM.USAGE_NOTE = #{menu_code}
          AND MLKM.LANG_KEY = #{lang_key}
          AND MLKM.IS_ACTIVE = 'Y'
    </select>

    <select id="getMultilangSingleText" parameterType="map" resultType="map">
        SELECT MLT.LANG_TEXT
        FROM MULTI_LANG_TEXT MLT
        INNER JOIN MULTI_LANG_KEY_MASTER MLKM ON MLT.KEY_ID = MLKM.KEY_ID
        WHERE MLT.LANG_CODE = #{lang_code}
          AND MLT.IS_ACTIVE = 'Y'
          AND (MLKM.COMPANY_CODE = #{company_code} OR MLKM.COMPANY_CODE = '*')
          AND MLKM.LANG_KEY = #{lang_key}
          AND MLKM.IS_ACTIVE = 'Y'
    </select>

    <select id="getMultilangBatchTranslationList" parameterType="map" resultType="map">
        SELECT
            MLT.LANG_TEXT
            , MLKM.LANG_KEY
            , MLKM.COMPANY_CODE
            , CASE WHEN MLKM.COMPANY_CODE = #{company_code} THEN 1 ELSE 2 END AS PRIORITY
        FROM MULTI_LANG_TEXT MLT
        INNER JOIN MULTI_LANG_KEY_MASTER MLKM ON MLT.KEY_ID = MLKM.KEY_ID
        WHERE MLT.LANG_CODE = #{user_lang}
          AND MLT.IS_ACTIVE = 'Y'
          AND MLKM.LANG_KEY IN
              <foreach collection="langKeys" item="key" open="(" separator="," close=")">
                  #{key}
              </foreach>
          AND MLKM.COMPANY_CODE IN (#{company_code}, '*')
          AND MLKM.IS_ACTIVE = 'Y'
        ORDER BY MLKM.LANG_KEY ASC, PRIORITY ASC
    </select>

    <select id="getMultilangFallbackTranslationList" parameterType="map" resultType="map">
        SELECT
            MLT.LANG_TEXT
            , MLKM.LANG_KEY
            , MLKM.COMPANY_CODE
            , CASE WHEN MLKM.COMPANY_CODE = #{company_code} THEN 1 ELSE 2 END AS PRIORITY
        FROM MULTI_LANG_TEXT MLT
        INNER JOIN MULTI_LANG_KEY_MASTER MLKM ON MLT.KEY_ID = MLKM.KEY_ID
        WHERE MLT.LANG_CODE = 'KR'
          AND MLT.IS_ACTIVE = 'Y'
          AND MLKM.LANG_KEY IN
              <foreach collection="missingKeys" item="key" open="(" separator="," close=")">
                  #{key}
              </foreach>
          AND MLKM.COMPANY_CODE IN (#{company_code}, '*')
          AND MLKM.IS_ACTIVE = 'Y'
        ORDER BY MLKM.LANG_KEY ASC, PRIORITY ASC
    </select>

    <!-- ═══════════════════════════════════════════════════════════════════════
         Category
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangCategoryList" parameterType="map" resultType="map">
        SELECT
            CATEGORY_ID
            , CATEGORY_CODE
            , CATEGORY_NAME
            , PARENT_ID
            , LEVEL
            , KEY_PREFIX
            , DESCRIPTION
            , SORT_ORDER
            , IS_ACTIVE
        FROM MULTI_LANG_CATEGORY
        WHERE IS_ACTIVE = 'Y'
        ORDER BY LEVEL ASC, SORT_ORDER ASC, CATEGORY_NAME ASC
    </select>

    <select id="getMultilangCategoryInfo" parameterType="map" resultType="map">
        SELECT
            CATEGORY_ID
            , CATEGORY_CODE
            , CATEGORY_NAME
            , PARENT_ID
            , LEVEL
            , KEY_PREFIX
            , DESCRIPTION
            , SORT_ORDER
            , IS_ACTIVE
        FROM MULTI_LANG_CATEGORY
        WHERE CATEGORY_ID = #{category_id}
    </select>

    <select id="getMultilangCategoryPath" parameterType="map" resultType="map">
        WITH RECURSIVE CATEGORY_PATH AS (
            SELECT
                CATEGORY_ID
                , CATEGORY_CODE
                , CATEGORY_NAME
                , PARENT_ID
                , LEVEL
                , KEY_PREFIX
                , DESCRIPTION
                , SORT_ORDER
                , IS_ACTIVE
            FROM MULTI_LANG_CATEGORY
            WHERE CATEGORY_ID = #{category_id}
            UNION ALL
            SELECT
                C.CATEGORY_ID
                , C.CATEGORY_CODE
                , C.CATEGORY_NAME
                , C.PARENT_ID
                , C.LEVEL
                , C.KEY_PREFIX
                , C.DESCRIPTION
                , C.SORT_ORDER
                , C.IS_ACTIVE
            FROM MULTI_LANG_CATEGORY C
            INNER JOIN CATEGORY_PATH CP ON C.CATEGORY_ID = CP.PARENT_ID
        )
        SELECT *
        FROM CATEGORY_PATH
        ORDER BY LEVEL ASC
    </select>

    <select id="getMultilangCategoryByCodeAndParent" parameterType="map" resultType="map">
        SELECT CATEGORY_ID
        FROM MULTI_LANG_CATEGORY
        WHERE CATEGORY_CODE = #{category_code}
          AND PARENT_ID = #{parent_id}
    </select>

    <select id="getMultilangCategoryByNameAndParent" parameterType="map" resultType="map">
        SELECT CATEGORY_ID
        FROM MULTI_LANG_CATEGORY
        WHERE CATEGORY_NAME = #{category_name}
          AND PARENT_ID = #{parent_id}
    </select>

    <insert id="insertMultilangCategory" parameterType="map" useGeneratedKeys="true" keyProperty="categoryId" keyColumn="category_id">
        INSERT INTO MULTI_LANG_CATEGORY (
            CATEGORY_CODE
            , CATEGORY_NAME
            , PARENT_ID
            , LEVEL
            , KEY_PREFIX
            , DESCRIPTION
            , SORT_ORDER
            , IS_ACTIVE
            , CREATED_DATE
        ) VALUES (
            #{category_code}
            , #{category_name}
            , #{parent_id, jdbcType=INTEGER}
            , #{level}
            , #{key_prefix}
            , #{description}
            , #{sort_order}
            , 'Y'
            , NOW()
        )
    </insert>

    <!-- ═══════════════════════════════════════════════════════════════════════
         Override keys
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangOverrideKeyList" parameterType="map" resultType="map">
        SELECT
            KEY_ID
            , COMPANY_CODE
            , LANG_KEY
            , CATEGORY_ID
            , KEY_MEANING
            , USAGE_NOTE
            , BASE_KEY_ID
            , IS_ACTIVE
            , CREATED_DATE
        FROM MULTI_LANG_KEY_MASTER
        WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
          AND BASE_KEY_ID IS NOT NULL
        ORDER BY LANG_KEY ASC
    </select>

    <select id="getMultilangBaseKeyInfo" parameterType="map" resultType="map">
        SELECT
            KEY_ID
            , COMPANY_CODE
            , LANG_KEY
            , CATEGORY_ID
            , KEY_MEANING
        FROM MULTI_LANG_KEY_MASTER
        WHERE KEY_ID = #{key_id}
    </select>

    <insert id="insertMultilangOverrideKey" parameterType="map" useGeneratedKeys="true" keyProperty="keyId" keyColumn="key_id">
        INSERT INTO MULTI_LANG_KEY_MASTER (
            COMPANY_CODE
            , LANG_KEY
            , CATEGORY_ID
            , KEY_MEANING
            , BASE_KEY_ID
            , IS_ACTIVE
            , CREATED_BY
            , UPDATED_BY
        ) VALUES (
            #{company_code}
            , #{lang_key}
            , #{category_id}
            , #{key_meaning}
            , #{base_key_id}
            , 'Y'
            , #{created_by}
            , #{created_by}
        )
    </insert>

    <!-- ═══════════════════════════════════════════════════════════════════════
         Screen labels
    ═══════════════════════════════════════════════════════════════════════ -->

    <select id="getMultilangRootCategoryByCode" parameterType="map" resultType="map">
        SELECT CATEGORY_ID
        FROM MULTI_LANG_CATEGORY
        WHERE CATEGORY_CODE = #{category_code}
          AND PARENT_ID IS NULL
    </select>

    <select id="getMultilangScreenGroupId" parameterType="map" resultType="map">
        SELECT GROUP_ID
        FROM SCREEN_GROUP_SCREENS
        WHERE SCREEN_ID = #{screen_id}
        LIMIT 1
    </select>

    <select id="getMultilangScreenGroupPath" parameterType="map" resultType="map">
        WITH RECURSIVE GROUP_PATH AS (
            SELECT
                ID
                , PARENT_GROUP_ID
                , GROUP_NAME
                , GROUP_LEVEL
                , 1 AS DEPTH
            FROM SCREEN_GROUPS
            WHERE ID = #{group_id}
            UNION ALL
            SELECT
                G.ID
                , G.PARENT_GROUP_ID
                , G.GROUP_NAME
                , G.GROUP_LEVEL
                , GP.DEPTH + 1
            FROM SCREEN_GROUPS G
            INNER JOIN GROUP_PATH GP ON G.ID = GP.PARENT_GROUP_ID
            WHERE G.PARENT_GROUP_ID IS NOT NULL
        )
        SELECT
            GROUP_NAME
            , GROUP_LEVEL
        FROM GROUP_PATH
        ORDER BY DEPTH DESC
    </select>

    <select id="getMultilangScreenCompanyCode" parameterType="map" resultType="map">
        SELECT COMPANY_CODE
        FROM SCREEN_DEFINITIONS
        WHERE SCREEN_ID = #{screen_id}
    </select>

    <select id="getMultilangCompanyName" parameterType="map" resultType="map">
        SELECT COMPANY_NAME
        FROM COMPANY_MNG
        WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </select>

</mapper>
