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

  <!-- ══════════════════════════════════════════════════════════════
       Screen Groups
       ══════════════════════════════════════════════════════════════ -->

  <select id="countScreenGroups" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM SCREEN_GROUPS
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
    <if test='exclude_pop != null and exclude_pop == "true"'>
      AND (HIERARCHY_PATH IS NULL
        OR (HIERARCHY_PATH NOT LIKE 'POP/%' AND HIERARCHY_PATH != 'POP'))
    </if>
    <if test="search_term != null and search_term != ''">
      AND (GROUP_NAME ILIKE '%' || #{search_term} || '%'
        OR GROUP_CODE ILIKE '%' || #{search_term} || '%'
        OR DESCRIPTION ILIKE '%' || #{search_term} || '%')
    </if>
  </select>

  <select id="selectScreenGroups" parameterType="map" resultType="map">
    SELECT
      SG.*,
      (SELECT COUNT(*)
       FROM SCREEN_GROUP_SCREENS SGS
       LEFT JOIN SCREEN_DEFINITIONS SD ON SGS.SCREEN_ID = SD.SCREEN_ID
       WHERE SGS.GROUP_ID = SG.ID
         AND (SD.IS_ACTIVE IS NULL OR SD.IS_ACTIVE != 'D')
      ) AS SCREEN_COUNT
    FROM SCREEN_GROUPS SG
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (SG.COMPANY_CODE = #{company_code} OR SG.COMPANY_CODE = '*')
    </if>
    <if test='exclude_pop != null and exclude_pop == "true"'>
      AND (SG.HIERARCHY_PATH IS NULL
        OR (SG.HIERARCHY_PATH NOT LIKE 'POP/%' AND SG.HIERARCHY_PATH != 'POP'))
    </if>
    <if test="search_term != null and search_term != ''">
      AND (SG.GROUP_NAME ILIKE '%' || #{search_term} || '%'
        OR SG.GROUP_CODE ILIKE '%' || #{search_term} || '%'
        OR SG.DESCRIPTION ILIKE '%' || #{search_term} || '%')
    </if>
    ORDER BY SG.DISPLAY_ORDER ASC, SG.CREATED_DATE DESC
    <if test="limit != null">
      LIMIT #{limit} OFFSET #{offset}
    </if>
  </select>

  <!-- 그룹 스크린 목록 (복수 그룹 ID) — 서비스에서 조립 -->
  <select id="selectGroupScreensByGroupIds" parameterType="map" resultType="map">
    SELECT
      SGS.ID,
      SGS.GROUP_ID,
      SGS.SCREEN_ID,
      SD.SCREEN_NAME,
      SGS.SCREEN_ROLE,
      SGS.DISPLAY_ORDER,
      SGS.IS_DEFAULT,
      SD.TABLE_NAME
    FROM SCREEN_GROUP_SCREENS SGS
    LEFT JOIN SCREEN_DEFINITIONS SD ON SGS.SCREEN_ID = SD.SCREEN_ID
    WHERE SGS.GROUP_ID IN
    <foreach collection="groupIds" item="gid" open="(" separator="," close=")">
      #{gid}
    </foreach>
    AND (SD.IS_ACTIVE IS NULL OR SD.IS_ACTIVE != 'D')
    ORDER BY SGS.DISPLAY_ORDER ASC
  </select>

  <select id="selectScreenGroupById" parameterType="map" resultType="map">
    SELECT *
    FROM SCREEN_GROUPS
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </select>

  <select id="selectParentGroupById" parameterType="map" resultType="map">
    SELECT ID, GROUP_LEVEL, HIERARCHY_PATH
    FROM SCREEN_GROUPS
    WHERE ID = #{parent_group_id}
  </select>

  <insert id="insertScreenGroup" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_GROUPS (
      GROUP_NAME, GROUP_CODE, MAIN_TABLE_NAME, DESCRIPTION, ICON,
      DISPLAY_ORDER, IS_ACTIVE, COMPANY_CODE, WRITER, PARENT_GROUP_ID, GROUP_LEVEL
    ) VALUES (
      #{group_name}, #{group_code}, #{main_table_name}, #{description}, #{icon},
      #{display_order}, #{is_active}, #{company_code}, #{user_id}, #{parent_group_id}, #{group_level}
    )
  </insert>

  <update id="updateScreenGroupHierarchyPath" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET HIERARCHY_PATH = #{hierarchy_path}
    WHERE ID = #{id}
  </update>

  <!-- 회사 코드 변경 없이 수정 -->
  <update id="updateScreenGroup" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET GROUP_NAME      = #{group_name},
        GROUP_CODE      = #{group_code},
        MAIN_TABLE_NAME = #{main_table_name},
        DESCRIPTION     = #{description},
        ICON            = #{icon},
        DISPLAY_ORDER   = #{display_order},
        IS_ACTIVE       = #{is_active},
        UPDATED_DATE    = NOW(),
        PARENT_GROUP_ID = #{parent_group_id},
        GROUP_LEVEL     = #{group_level},
        HIERARCHY_PATH  = #{hierarchy_path}
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

  <!-- 회사 코드 포함 수정 (최고관리자) -->
  <update id="updateScreenGroupWithCompany" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET GROUP_NAME      = #{group_name},
        GROUP_CODE      = #{group_code},
        MAIN_TABLE_NAME = #{main_table_name},
        DESCRIPTION     = #{description},
        ICON            = #{icon},
        DISPLAY_ORDER   = #{display_order},
        IS_ACTIVE       = #{is_active},
        UPDATED_DATE    = NOW(),
        PARENT_GROUP_ID = #{parent_group_id},
        GROUP_LEVEL     = #{group_level},
        HIERARCHY_PATH  = #{hierarchy_path},
        (COMPANY_CODE    = #{target_company_code} OR COMPANY_CODE = '*')
    WHERE ID = #{id}
  </update>

  <!-- Delete support queries -->
  <select id="selectScreenGroupForDelete" parameterType="map" resultType="map">
    SELECT COMPANY_CODE
    FROM SCREEN_GROUPS
    WHERE ID = #{id}
  </select>

  <select id="selectAllChildGroupIds" parameterType="map" resultType="map">
    WITH RECURSIVE CHILD_GROUPS AS (
      SELECT ID, COMPANY_CODE
      FROM SCREEN_GROUPS
      WHERE ID = #{id}
        AND (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')

      UNION ALL

      SELECT SG.ID, SG.COMPANY_CODE
      FROM SCREEN_GROUPS SG
      JOIN CHILD_GROUPS CG ON SG.PARENT_GROUP_ID = CG.ID AND SG.COMPANY_CODE = CG.COMPANY_CODE
    )
    SELECT ID FROM CHILD_GROUPS
  </select>

  <select id="selectMenusByGroupIds" parameterType="map" resultType="map">
    SELECT OBJID
    FROM MENU_INFO
    WHERE SCREEN_GROUP_ID = ANY(ARRAY
    <foreach collection="groupIds" item="gid" open="[" separator="," close="]::INT[]">
      #{gid}
    </foreach>
    )
    AND (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')
  </select>

  <delete id="deleteScreenMenuAssignmentsByMenuObjids" parameterType="map">
    DELETE FROM SCREEN_MENU_ASSIGNMENTS
    WHERE MENU_OBJID = ANY(ARRAY
    <foreach collection="menuObjids" item="objid" open="[" separator="," close="]::BIGINT[]">
      #{objid}
    </foreach>
    )
    AND (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')
  </delete>

  <delete id="deleteMenusByGroupIds" parameterType="map">
    DELETE FROM MENU_INFO
    WHERE SCREEN_GROUP_ID = ANY(ARRAY
    <foreach collection="groupIds" item="gid" open="[" separator="," close="]::INT[]">
      #{gid}
    </foreach>
    )
    AND (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')
  </delete>

  <select id="isRootGroupById" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM SCREEN_GROUPS
    WHERE ID = #{id}
      AND PARENT_GROUP_ID IS NULL
  </select>

  <delete id="deleteNumberingRuleParts" parameterType="map">
    DELETE FROM NUMBERING_RULE_PARTS
    WHERE RULE_ID IN (
      SELECT RULE_ID
      FROM NUMBERING_RULES
      WHERE (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')
    )
  </delete>

  <delete id="deleteNumberingRules" parameterType="map">
    DELETE FROM NUMBERING_RULES
    WHERE (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')
  </delete>

  <delete id="deleteScreenGroupById" parameterType="map">
    DELETE FROM SCREEN_GROUPS
    WHERE ID = #{id}
      AND (COMPANY_CODE = #{target_company_code} OR COMPANY_CODE = '*')
  </delete>

  <!-- ══════════════════════════════════════════════════════════════
       Screen Group Screens
       ══════════════════════════════════════════════════════════════ -->

  <insert id="insertGroupScreen" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_GROUP_SCREENS (
      GROUP_ID, SCREEN_ID, SCREEN_ROLE, DISPLAY_ORDER, IS_DEFAULT, COMPANY_CODE, WRITER
    ) VALUES (
      #{group_id}, #{screen_id}, #{screen_role}, #{display_order}, #{is_default},
      #{company_code}, #{user_id}
    )
  </insert>

  <update id="updateGroupScreen" parameterType="map">
    UPDATE SCREEN_GROUP_SCREENS
    SET SCREEN_ROLE   = #{screen_role},
        DISPLAY_ORDER = #{display_order},
        IS_DEFAULT    = #{is_default},
        UPDATED_DATE  = NOW()
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

  <delete id="deleteGroupScreen" parameterType="map">
    DELETE FROM SCREEN_GROUP_SCREENS
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </delete>

  <!-- ══════════════════════════════════════════════════════════════
       Field Joins
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectFieldJoins" parameterType="map" resultType="map">
    SELECT SFJ.*,
      TL1.TABLE_LABEL AS SAVE_TABLE_LABEL,
      TL2.TABLE_LABEL AS JOIN_TABLE_LABEL
    FROM SCREEN_FIELD_JOINS SFJ
    LEFT JOIN TABLE_LABELS TL1 ON SFJ.SAVE_TABLE = TL1.TABLE_NAME
    LEFT JOIN TABLE_LABELS TL2 ON SFJ.JOIN_TABLE  = TL2.TABLE_NAME
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (SFJ.COMPANY_CODE = #{company_code} OR SFJ.COMPANY_CODE = '*')
    </if>
    <if test="screen_id != null">
      AND SFJ.SCREEN_ID = #{screen_id}
    </if>
    ORDER BY SFJ.ID ASC
  </select>

  <insert id="insertFieldJoin" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_FIELD_JOINS (
      SCREEN_ID, LAYOUT_ID, COMPONENT_ID, FIELD_NAME,
      SAVE_TABLE, SAVE_COLUMN, JOIN_TABLE, JOIN_COLUMN, DISPLAY_COLUMN,
      JOIN_TYPE, FILTER_CONDITION, SORT_COLUMN, SORT_DIRECTION,
      IS_ACTIVE, COMPANY_CODE, WRITER
    ) VALUES (
      #{screen_id}, #{layout_id}, #{component_id}, #{field_name},
      #{save_table}, #{save_column}, #{join_table}, #{join_column}, #{display_column},
      #{join_type}, #{filter_condition}, #{sort_column}, #{sort_direction},
      #{is_active}, #{company_code}, #{user_id}
    )
  </insert>

  <update id="updateFieldJoin" parameterType="map">
    UPDATE SCREEN_FIELD_JOINS
    SET LAYOUT_ID        = #{layout_id},
        COMPONENT_ID     = #{component_id},
        FIELD_NAME       = #{field_name},
        SAVE_TABLE       = #{save_table},
        SAVE_COLUMN      = #{save_column},
        JOIN_TABLE       = #{join_table},
        JOIN_COLUMN      = #{join_column},
        DISPLAY_COLUMN   = #{display_column},
        JOIN_TYPE        = #{join_type},
        FILTER_CONDITION = #{filter_condition},
        SORT_COLUMN      = #{sort_column},
        SORT_DIRECTION   = #{sort_direction},
        IS_ACTIVE        = #{is_active},
        UPDATED_DATE     = NOW()
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

  <delete id="deleteFieldJoin" parameterType="map">
    DELETE FROM SCREEN_FIELD_JOINS
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </delete>

  <!-- ══════════════════════════════════════════════════════════════
       Data Flows
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectDataFlows" parameterType="map" resultType="map">
    SELECT SDF.*,
      SD1.SCREEN_NAME AS SOURCE_SCREEN_NAME,
      SD2.SCREEN_NAME AS TARGET_SCREEN_NAME,
      SG.GROUP_NAME
    FROM SCREEN_DATA_FLOWS SDF
    LEFT JOIN SCREEN_DEFINITIONS SD1 ON SDF.SOURCE_SCREEN_ID = SD1.SCREEN_ID
    LEFT JOIN SCREEN_DEFINITIONS SD2 ON SDF.TARGET_SCREEN_ID = SD2.SCREEN_ID
    LEFT JOIN SCREEN_GROUPS      SG  ON SDF.GROUP_ID = SG.ID
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (SDF.COMPANY_CODE = #{company_code} OR SDF.COMPANY_CODE = '*')
    </if>
    <if test="group_id != null">
      AND SDF.GROUP_ID = #{group_id}
    </if>
    <if test="source_screen_id != null">
      AND SDF.SOURCE_SCREEN_ID = #{source_screen_id}
    </if>
    ORDER BY SDF.ID ASC
  </select>

  <insert id="insertDataFlow" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_DATA_FLOWS (
      GROUP_ID, SOURCE_SCREEN_ID, SOURCE_ACTION, TARGET_SCREEN_ID, TARGET_ACTION,
      DATA_MAPPING, FLOW_TYPE, FLOW_LABEL, CONDITION_EXPRESSION, IS_ACTIVE,
      COMPANY_CODE, WRITER
    ) VALUES (
      #{group_id}, #{source_screen_id}, #{source_action}, #{target_screen_id}, #{target_action},
      #{data_mapping}::JSONB, #{flow_type}, #{flow_label}, #{condition_expression}, #{is_active},
      #{company_code}, #{user_id}
    )
  </insert>

  <update id="updateDataFlow" parameterType="map">
    UPDATE SCREEN_DATA_FLOWS
    SET GROUP_ID             = #{group_id},
        SOURCE_SCREEN_ID     = #{source_screen_id},
        SOURCE_ACTION        = #{source_action},
        TARGET_SCREEN_ID     = #{target_screen_id},
        TARGET_ACTION        = #{target_action},
        DATA_MAPPING         = #{data_mapping}::JSONB,
        FLOW_TYPE            = #{flow_type},
        FLOW_LABEL           = #{flow_label},
        CONDITION_EXPRESSION = #{condition_expression},
        IS_ACTIVE            = #{is_active},
        UPDATED_DATE         = NOW()
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

  <delete id="deleteDataFlow" parameterType="map">
    DELETE FROM SCREEN_DATA_FLOWS
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </delete>

  <!-- ══════════════════════════════════════════════════════════════
       Table Relations
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectTableRelations" parameterType="map" resultType="map">
    SELECT STR.*,
      SD.SCREEN_NAME,
      SG.GROUP_NAME,
      TL.TABLE_LABEL
    FROM SCREEN_TABLE_RELATIONS STR
    LEFT JOIN SCREEN_DEFINITIONS SD ON STR.SCREEN_ID = SD.SCREEN_ID
    LEFT JOIN SCREEN_GROUPS      SG ON STR.GROUP_ID  = SG.ID
    LEFT JOIN TABLE_LABELS       TL ON STR.TABLE_NAME = TL.TABLE_NAME
    WHERE 1=1
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (STR.COMPANY_CODE = #{company_code} OR STR.COMPANY_CODE = '*')
    </if>
    <if test="screen_id != null">
      AND STR.SCREEN_ID = #{screen_id}
    </if>
    <if test="group_id != null">
      AND STR.GROUP_ID = #{group_id}
    </if>
    ORDER BY STR.ID ASC
  </select>

  <insert id="insertTableRelation" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_TABLE_RELATIONS (
      GROUP_ID, SCREEN_ID, TABLE_NAME, RELATION_TYPE, CRUD_OPERATIONS,
      DESCRIPTION, IS_ACTIVE, COMPANY_CODE, WRITER
    ) VALUES (
      #{group_id}, #{screen_id}, #{table_name}, #{relation_type}, #{crud_operations},
      #{description}, #{is_active}, #{company_code}, #{user_id}
    )
  </insert>

  <update id="updateTableRelation" parameterType="map">
    UPDATE SCREEN_TABLE_RELATIONS
    SET GROUP_ID        = #{group_id},
        TABLE_NAME      = #{table_name},
        RELATION_TYPE   = #{relation_type},
        CRUD_OPERATIONS = #{crud_operations},
        DESCRIPTION     = #{description},
        IS_ACTIVE       = #{is_active},
        UPDATED_DATE    = NOW()
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </update>

  <delete id="deleteTableRelation" parameterType="map">
    DELETE FROM SCREEN_TABLE_RELATIONS
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </delete>

  <!-- ══════════════════════════════════════════════════════════════
       Layout Summary
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectLayoutComponents" parameterType="map" resultType="map">
    SELECT
      PROPERTIES->>'widgetType'   AS WIDGET_TYPE,
      PROPERTIES->>'label'        AS LABEL,
      PROPERTIES->>'fieldName'    AS FIELD_NAME,
      PROPERTIES->>'tableName'    AS TABLE_NAME
    FROM SCREEN_LAYOUTS
    WHERE SCREEN_ID      = #{screen_id}
      AND COMPONENT_TYPE = 'component'
    ORDER BY DISPLAY_ORDER ASC
  </select>

  <select id="selectMultipleLayoutComponents" parameterType="map" resultType="map">
    SELECT
      SCREEN_ID,
      COMPONENT_TYPE,
      POSITION_X,
      POSITION_Y,
      WIDTH,
      HEIGHT,
      PROPERTIES->>'componentType'             AS COMPONENT_KIND,
      PROPERTIES->>'widgetType'                AS WIDGET_TYPE,
      PROPERTIES->>'label'                     AS LABEL,
      COALESCE(
        PROPERTIES->'componentConfig'->>'bindField',
        PROPERTIES->>'bindField',
        PROPERTIES->'componentConfig'->>'field',
        PROPERTIES->>'field',
        PROPERTIES->>'columnName'
      )                                        AS BIND_FIELD,
      PROPERTIES->'componentConfig'            AS COMPONENT_CONFIG
    FROM SCREEN_LAYOUTS
    WHERE SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND COMPONENT_TYPE = 'component'
    ORDER BY SCREEN_ID, DISPLAY_ORDER ASC
  </select>

  <!-- ══════════════════════════════════════════════════════════════
       Sub Tables — 11 queries assembled in service
       ══════════════════════════════════════════════════════════════ -->

  <!-- 1. 컴포넌트 config에서 서브 테이블 정보 -->
  <select id="selectSubTableComponentConfigs" parameterType="map" resultType="map">
    SELECT DISTINCT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                                  AS MAIN_TABLE,
      COALESCE(
        SL.PROPERTIES->'componentConfig'->>'tableName',
        SL.PROPERTIES->'componentConfig'->>'sourceTable'
      )                                                              AS SUB_TABLE,
      SL.PROPERTIES->>'componentType'                               AS COMPONENT_TYPE,
      SL.PROPERTIES->'componentConfig'->>'targetTable'              AS TARGET_TABLE,
      SL.PROPERTIES->'componentConfig'->'fieldMappings'             AS FIELD_MAPPINGS,
      SL.PROPERTIES->'componentConfig'->'columns'                   AS COLUMNS_CONFIG
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS SL ON SD.SCREEN_ID = SL.SCREEN_ID
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND (
      SL.PROPERTIES->'componentConfig'->>'tableName'    IS NOT NULL
      OR SL.PROPERTIES->'componentConfig'->>'sourceTable' IS NOT NULL
    )
    ORDER BY SD.SCREEN_ID
  </select>

  <!-- 2. (table, column) 쌍으로 column_label 조회 -->
  <select id="selectColumnLabelsByPairs" parameterType="map" resultType="map">
    SELECT TABLE_NAME, COLUMN_NAME, COLUMN_LABEL
    FROM TABLE_TYPE_COLUMNS
    WHERE COMPANY_CODE = '*'
    AND (
      <foreach collection="pairs" item="pair" separator=" OR ">
        (TABLE_NAME = #{pair.tableName} AND COLUMN_NAME = #{pair.columnName})
      </foreach>
    )
  </select>

  <!-- 3. reference_table 기반 참조 서브 테이블 -->
  <select id="selectReferenceColumns" parameterType="map" resultType="map">
    SELECT DISTINCT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                   AS MAIN_TABLE,
      TTC.COLUMN_NAME,
      TTC.REFERENCE_TABLE,
      TTC.REFERENCE_COLUMN,
      TTC.COLUMN_LABEL                                AS SOURCE_DISPLAY_NAME,
      REF_TTC.COLUMN_LABEL                            AS TARGET_DISPLAY_NAME
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS SL ON SD.SCREEN_ID = SL.SCREEN_ID
    JOIN TABLE_TYPE_COLUMNS TTC
      ON TTC.TABLE_NAME = SD.TABLE_NAME
      AND TTC.COMPANY_CODE = '*'
      AND TTC.REFERENCE_TABLE IS NOT NULL
    LEFT JOIN TABLE_TYPE_COLUMNS REF_TTC
      ON REF_TTC.TABLE_NAME  = TTC.REFERENCE_TABLE
      AND REF_TTC.COLUMN_NAME = TTC.REFERENCE_COLUMN
      AND REF_TTC.COMPANY_CODE = '*'
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    ORDER BY SD.SCREEN_ID
  </select>

  <!-- 4. parentDataMapping 파싱 -->
  <select id="selectParentDataMappingConfigs" parameterType="map" resultType="map">
    SELECT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                                    AS MAIN_TABLE,
      SL.PROPERTIES->>'componentType'                                 AS COMPONENT_TYPE,
      SL.PROPERTIES->'componentConfig'->'parentDataMapping'           AS PARENT_DATA_MAPPING
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS SL ON SD.SCREEN_ID = SL.SCREEN_ID
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND SL.PROPERTIES->'componentConfig'->'parentDataMapping' IS NOT NULL
  </select>

  <!-- 5. rightPanel.relation (v1 + v2 UNION) -->
  <select id="selectRightPanelRelations" parameterType="map" resultType="map">
    SELECT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                                          AS MAIN_TABLE,
      SL.PROPERTIES->>'componentType'                                       AS COMPONENT_TYPE,
      SL.PROPERTIES->'componentConfig'->'rightPanel'->'relation'            AS RIGHT_PANEL_RELATION,
      SL.PROPERTIES->'componentConfig'->'rightPanel'->>'tableName'          AS RIGHT_PANEL_TABLE,
      SL.PROPERTIES->'componentConfig'->'rightPanel'->'columns'             AS RIGHT_PANEL_COLUMNS
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS SL ON SD.SCREEN_ID = SL.SCREEN_ID
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND SL.PROPERTIES->'componentConfig'->'rightPanel'->'relation' IS NOT NULL

    UNION ALL

    SELECT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                             AS MAIN_TABLE,
      COMP->'overrides'->>'type'                               AS COMPONENT_TYPE,
      COMP->'overrides'->'rightPanel'->'relation'              AS RIGHT_PANEL_RELATION,
      COMP->'overrides'->'rightPanel'->>'tableName'            AS RIGHT_PANEL_TABLE,
      COMP->'overrides'->'rightPanel'->'columns'               AS RIGHT_PANEL_COLUMNS
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS_V2 SLV2 ON SD.SCREEN_ID = SLV2.SCREEN_ID,
    JSONB_ARRAY_ELEMENTS(SLV2.LAYOUT_DATA->'components') AS COMP
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND COMP->'overrides'->'rightPanel'->'relation' IS NOT NULL
  </select>

  <!-- 6. rightPanel joinedTables FK 컬럼 조회 -->
  <select id="selectFKColumnsForJoinedTables" parameterType="map" resultType="map">
    SELECT
      CL.TABLE_NAME,
      CL.COLUMN_NAME,
      CL.COLUMN_LABEL,
      CL.REFERENCE_TABLE,
      CL.REFERENCE_COLUMN,
      TL.TABLE_LABEL AS REFERENCE_TABLE_LABEL
    FROM TABLE_TYPE_COLUMNS CL
    LEFT JOIN TABLE_LABELS TL ON CL.REFERENCE_TABLE = TL.TABLE_NAME
    WHERE CL.TABLE_NAME IN
    <foreach collection="subTableNames" item="t" open="(" separator="," close=")">
      #{t}
    </foreach>
    AND CL.REFERENCE_TABLE IN
    <foreach collection="refTableNames" item="r" open="(" separator="," close=")">
      #{r}
    </foreach>
    AND CL.COMPANY_CODE = '*'
  </select>

  <!-- 7. v2-repeater 컴포넌트 -->
  <select id="selectV2Repeaters" parameterType="map" resultType="map">
    SELECT DISTINCT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                            AS MAIN_TABLE,
      COMP->'overrides'->>'type'              AS COMPONENT_TYPE,
      COMP->'overrides'->>'selectedTable'     AS SUB_TABLE,
      COMP->'overrides'->>'foreignKey'        AS FOREIGN_KEY,
      COMP->'overrides'->>'parentTable'       AS PARENT_TABLE
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS_V2 SLV2 ON SD.SCREEN_ID = SLV2.SCREEN_ID,
    JSONB_ARRAY_ELEMENTS(SLV2.LAYOUT_DATA->'components') AS COMP
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND COMP->'overrides'->>'type'            = 'v2-repeater'
    AND COMP->'overrides'->>'selectedTable'   IS NOT NULL
  </select>

  <!-- 8. v2-bom-tree detailTable -->
  <select id="selectV2DetailTables" parameterType="map" resultType="map">
    SELECT DISTINCT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                          AS MAIN_TABLE,
      INNER_COMP->>'type'                                    AS COMPONENT_TYPE,
      INNER_COMP->'componentConfig'->>'detailTable'          AS SUB_TABLE,
      INNER_COMP->'componentConfig'->>'foreignKey'           AS FOREIGN_KEY
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS_V2 SLV2 ON SD.SCREEN_ID = SLV2.SCREEN_ID,
    JSONB_ARRAY_ELEMENTS(SLV2.LAYOUT_DATA->'components') AS COMP,
    JSONB_ARRAY_ELEMENTS(
      COALESCE(
        COMP->'overrides'->'rightPanel'->'components',
        COMP->'overrides'->'leftPanel'->'components',
        '[]'::JSONB
      )
    ) AS INNER_COMP
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND INNER_COMP->'componentConfig'->>'detailTable' IS NOT NULL
  </select>

  <!-- 9. 저장 버튼 테이블 -->
  <select id="selectSaveTableActions" parameterType="map" resultType="map">
    SELECT DISTINCT
      SD.SCREEN_ID,
      SD.SCREEN_NAME,
      SD.TABLE_NAME                                                        AS MAIN_TABLE,
      SL.PROPERTIES->'componentConfig'->'action'->>'type'                AS ACTION_TYPE,
      SL.PROPERTIES->>'componentType'                                     AS COMPONENT_TYPE,
      SL.PROPERTIES->'componentConfig'->>'targetTable'                   AS TARGET_TABLE,
      SL.PROPERTIES->'componentConfig'->'action'->'dataTransfer'->>'targetTable' AS TRANSFER_TARGET_TABLE
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS SL ON SD.SCREEN_ID = SL.SCREEN_ID
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND SL.PROPERTIES->'componentConfig'->'action'->>'type' = 'save'
    AND SL.PROPERTIES->'componentConfig'->'action'->>'targetScreenId' IS NULL
    <!-- table-like 화면 (canonical 'table' / legacy 'table-list' / hidden 'v2-table-list')
         중 체크박스가 활성화된 것이 있으면 제외.
         체크박스 config 경로가 두 가지로 구분된다:
           - legacy table-list / v2-table-list : componentConfig.checkbox.enabled (boolean)
           - canonical table                   : componentConfig.showCheckbox (boolean) -->
    AND NOT EXISTS (
      SELECT 1 FROM SCREEN_LAYOUTS SL_LIST
      WHERE SL_LIST.SCREEN_ID = SD.SCREEN_ID
        AND SL_LIST.PROPERTIES->>'componentType' IN ('table', 'table-list', 'v2-table-list')
        AND (
          (SL_LIST.PROPERTIES->'componentConfig'->'checkbox'->>'enabled')::BOOLEAN = TRUE
          OR (SL_LIST.PROPERTIES->'componentConfig'->>'showCheckbox')::BOOLEAN = TRUE
        )
    )
    AND NOT EXISTS (
      SELECT 1 FROM SCREEN_LAYOUTS SL_MODAL
      WHERE SL_MODAL.SCREEN_ID = SD.SCREEN_ID
        AND SL_MODAL.PROPERTIES->'componentConfig'->'action'->>'type' = 'openModalWithData'
    )
    ORDER BY SD.SCREEN_ID
  </select>

  <!-- 10. 전역 메인 테이블 목록 -->
  <select id="selectGlobalMainTables" parameterType="map" resultType="map">
    SELECT DISTINCT TABLE_NAME AS MAIN_TABLE
    FROM SCREEN_DEFINITIONS
    WHERE SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND TABLE_NAME IS NOT NULL

    UNION

    SELECT DISTINCT COMP->'overrides'->'rightPanel'->>'tableName' AS MAIN_TABLE
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS_V2 SLV2 ON SD.SCREEN_ID = SLV2.SCREEN_ID,
    JSONB_ARRAY_ELEMENTS(SLV2.LAYOUT_DATA->'components') AS COMP
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND COMP->'overrides'->'rightPanel'->>'tableName' IS NOT NULL

    UNION

    SELECT DISTINCT SL.PROPERTIES->'componentConfig'->'rightPanel'->>'tableName' AS MAIN_TABLE
    FROM SCREEN_DEFINITIONS SD
    JOIN SCREEN_LAYOUTS SL ON SD.SCREEN_ID = SL.SCREEN_ID
    WHERE SD.SCREEN_ID IN
    <foreach collection="screenIds" item="sid" open="(" separator="," close=")">
      #{sid}
    </foreach>
    AND SL.PROPERTIES->'componentConfig'->'rightPanel'->>'tableName' IS NOT NULL
  </select>

  <!-- ══════════════════════════════════════════════════════════════
       POP Groups
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectPopScreenGroups" parameterType="map" resultType="map">
    SELECT SG.*,
      (SELECT COUNT(*)
       FROM SCREEN_GROUP_SCREENS SGS
       INNER JOIN SCREEN_LAYOUTS_POP SLP
         ON SGS.SCREEN_ID = SLP.SCREEN_ID AND SGS.COMPANY_CODE = SLP.COMPANY_CODE
       WHERE SGS.GROUP_ID = SG.ID AND SGS.COMPANY_CODE = SG.COMPANY_CODE
      ) AS SCREEN_COUNT
    FROM SCREEN_GROUPS SG
    WHERE (SG.HIERARCHY_PATH LIKE 'POP/%' OR SG.HIERARCHY_PATH = 'POP')
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (SG.COMPANY_CODE = #{company_code} OR SG.COMPANY_CODE = '*')
    </if>
    <if test="search_term != null and search_term != ''">
      AND (SG.GROUP_NAME ILIKE '%' || #{search_term} || '%'
        OR SG.GROUP_CODE ILIKE '%' || #{search_term} || '%'
        OR SG.DESCRIPTION ILIKE '%' || #{search_term} || '%')
    </if>
    ORDER BY SG.DISPLAY_ORDER ASC, SG.HIERARCHY_PATH ASC
  </select>

  <select id="selectPopGroupScreens" parameterType="map" resultType="map">
    SELECT
      SGS.ID,
      SGS.GROUP_ID,
      SGS.SCREEN_ID,
      SD.SCREEN_NAME,
      SGS.SCREEN_ROLE,
      SGS.DISPLAY_ORDER,
      SGS.IS_DEFAULT,
      SD.TABLE_NAME
    FROM SCREEN_GROUP_SCREENS SGS
    LEFT JOIN SCREEN_DEFINITIONS SD ON SGS.SCREEN_ID = SD.SCREEN_ID
    INNER JOIN SCREEN_LAYOUTS_POP SLP ON SGS.SCREEN_ID = SLP.SCREEN_ID AND SGS.COMPANY_CODE = SLP.COMPANY_CODE
    WHERE SGS.GROUP_ID IN
    <foreach collection="groupIds" item="gid" open="(" separator="," close=")">
      #{gid}
    </foreach>
    ORDER BY SGS.DISPLAY_ORDER ASC
  </select>

  <insert id="insertPopScreenGroup" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_GROUPS (
      GROUP_NAME, GROUP_CODE, DESCRIPTION, ICON, DISPLAY_ORDER,
      PARENT_GROUP_ID, HIERARCHY_PATH, COMPANY_CODE, WRITER, IS_ACTIVE
    ) VALUES (
      #{group_name}, #{group_code}, #{description}, #{icon}, #{display_order},
      #{parent_group_id}, #{hierarchy_path}, #{company_code}, #{user_id}, 'Y'
    )
  </insert>

  <select id="countGroupByCode" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM SCREEN_GROUPS
    WHERE GROUP_CODE = #{group_code}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <select id="selectScreenGroupForUpdate" parameterType="map" resultType="map">
    SELECT *
    FROM SCREEN_GROUPS
    WHERE ID = #{id}
    <if test="company_code != null and company_code != &quot;*&quot;">
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    </if>
  </select>

  <update id="updatePopScreenGroup" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET GROUP_NAME    = COALESCE(#{group_name},    GROUP_NAME),
        DESCRIPTION   = COALESCE(#{description},  DESCRIPTION),
        ICON          = COALESCE(#{icon},          ICON),
        DISPLAY_ORDER = COALESCE(#{display_order},  DISPLAY_ORDER),
        IS_ACTIVE     = COALESCE(#{is_active},      IS_ACTIVE),
        UPDATED_DATE  = NOW()
    WHERE ID = #{id}
  </update>

  <select id="selectAnyScreenGroupById" parameterType="map" resultType="map">
    SELECT COMPANY_CODE
    FROM SCREEN_GROUPS
    WHERE ID = #{id}
  </select>

  <select id="countChildGroupsByParentId" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM SCREEN_GROUPS
    WHERE PARENT_GROUP_ID = #{id}
  </select>

  <select id="countGroupScreensByGroupId" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM SCREEN_GROUP_SCREENS
    WHERE GROUP_ID = #{id}
  </select>

  <select id="selectPopRootGroup" parameterType="map" resultType="map">
    SELECT *
    FROM SCREEN_GROUPS
    WHERE HIERARCHY_PATH = 'POP'
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <insert id="insertPopRootGroup" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_GROUPS (
      GROUP_NAME, GROUP_CODE, HIERARCHY_PATH, COMPANY_CODE,
      DESCRIPTION, DISPLAY_ORDER, IS_ACTIVE, WRITER
    ) VALUES (
      'POP 화면', 'POP', 'POP', #{company_code},
      'POP 화면 관리 루트', 0, 'Y', #{user_id}
    )
  </insert>

  <!-- ══════════════════════════════════════════════════════════════
       Sync: Screen Groups → Menu
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectScreenGroupsForSync" parameterType="map" resultType="map">
    SELECT
      SG.ID,
      SG.GROUP_NAME,
      SG.GROUP_CODE,
      SG.PARENT_GROUP_ID,
      SG.GROUP_LEVEL,
      SG.DISPLAY_ORDER,
      SG.DESCRIPTION,
      SG.ICON,
      SG.MENU_OBJID,
      PARENT.MENU_OBJID AS PARENT_MENU_OBJID
    FROM SCREEN_GROUPS SG
    LEFT JOIN SCREEN_GROUPS PARENT ON SG.PARENT_GROUP_ID = PARENT.ID
    WHERE (SG.COMPANY_CODE = #{company_code} OR SG.COMPANY_CODE = '*')
    ORDER BY SG.GROUP_LEVEL ASC, SG.DISPLAY_ORDER ASC
  </select>

  <select id="selectExistingMenusForSync" parameterType="map" resultType="map">
    SELECT
      M.OBJID,
      M.MENU_NAME_KOR,
      M.PARENT_OBJ_ID,
      M.SCREEN_GROUP_ID,
      P.MENU_NAME_KOR AS PARENT_NAME
    FROM MENU_INFO M
    LEFT JOIN MENU_INFO P ON M.PARENT_OBJ_ID = P.OBJID
    WHERE (M.COMPANY_CODE = #{company_code} OR M.COMPANY_CODE = '*')
      AND M.MENU_TYPE = '1'
  </select>

  <select id="selectUserMenuRoot" parameterType="map" resultType="map">
    SELECT OBJID
    FROM MENU_INFO
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
      AND MENU_TYPE = '1'
      AND PARENT_OBJ_ID = '0'
    ORDER BY SEQ ASC
    LIMIT 1
  </select>

  <insert id="insertUserMenuRoot" parameterType="map">
    INSERT INTO MENU_INFO (
      OBJID, PARENT_OBJ_ID, MENU_NAME_KOR, MENU_NAME_ENG,
      SEQ, MENU_TYPE, COMPANY_CODE, WRITER, CREATED_DATE, STATUS
    ) VALUES (
      #{objid}, 0, '사용자', 'User', 1, 1,
      #{company_code}, #{user_id}, NOW(), 'active'
    )
  </insert>

  <select id="selectDefaultScreenForGroup" parameterType="map" resultType="map">
    SELECT SD.SCREEN_ID, SD.SCREEN_CODE, SD.SCREEN_NAME
    FROM SCREEN_GROUP_SCREENS SGS
    JOIN SCREEN_DEFINITIONS SD ON SGS.SCREEN_ID = SD.SCREEN_ID
    WHERE SGS.GROUP_ID = #{group_id}
      AND (SGS.COMPANY_CODE = #{company_code} OR SGS.COMPANY_CODE = '*')
    ORDER BY CASE WHEN SGS.IS_DEFAULT = 'Y' THEN 0 ELSE 1 END, SGS.DISPLAY_ORDER ASC
    LIMIT 1
  </select>

  <update id="updateMenuUrlAndScreenCode" parameterType="map">
    UPDATE MENU_INFO
    SET MENU_URL    = #{menu_url},
        SCREEN_CODE = #{screen_code}
    WHERE OBJID = #{objid}
  </update>

  <select id="getNextMenuSeqUnderParent" parameterType="map" resultType="int">
    SELECT COALESCE(MAX(SEQ), 0) + 1
    FROM MENU_INFO
    WHERE PARENT_OBJ_ID = #{parent_objid}
      AND (COMPANY_CODE  = #{company_code} OR COMPANY_CODE = '*')
      AND MENU_TYPE     = '1'
  </select>

  <insert id="insertMenuForGroup" parameterType="map">
    INSERT INTO MENU_INFO (
      OBJID, PARENT_OBJ_ID, MENU_NAME_KOR, MENU_NAME_ENG,
      SEQ, MENU_TYPE, COMPANY_CODE, WRITER, CREATED_DATE, STATUS,
      SCREEN_GROUP_ID, MENU_DESC, MENU_URL, SCREEN_CODE, MENU_ICON
    ) VALUES (
      #{objid}, #{parent_objid}, #{group_name}, #{group_code},
      #{seq}, 1, #{company_code}, #{user_id}, NOW(), 'active',
      #{group_id}, #{description}, #{menu_url}, #{screen_code}, #{icon}
    )
  </insert>

  <update id="updateScreenGroupMenuObjid" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET MENU_OBJID   = #{menu_objid},
        UPDATED_DATE = NOW()
    WHERE ID = #{id}
  </update>

  <update id="clearScreenGroupMenuObjid" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET MENU_OBJID = NULL, UPDATED_DATE = NOW()
    WHERE ID = #{id}
  </update>

  <update id="updateMenuScreenGroupId" parameterType="map">
    UPDATE MENU_INFO
    SET SCREEN_GROUP_ID = #{group_id}
    WHERE OBJID = #{objid}
  </update>

  <!-- ══════════════════════════════════════════════════════════════
       Sync: Menu → Screen Groups
       ══════════════════════════════════════════════════════════════ -->

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

  <select id="selectMenusForSync" parameterType="map" resultType="map">
    SELECT
      M.OBJID,
      M.MENU_NAME_KOR,
      M.MENU_NAME_ENG,
      M.PARENT_OBJ_ID,
      M.SEQ,
      M.MENU_URL,
      M.MENU_DESC,
      M.SCREEN_GROUP_ID,
      PARENT.SCREEN_GROUP_ID AS PARENT_SCREEN_GROUP_ID
    FROM MENU_INFO M
    LEFT JOIN MENU_INFO PARENT ON M.PARENT_OBJ_ID = PARENT.OBJID
    WHERE (M.COMPANY_CODE = #{company_code} OR M.COMPANY_CODE = '*')
      AND M.MENU_TYPE = '1'
    ORDER BY
      CASE WHEN M.PARENT_OBJ_ID = '0' THEN 0 ELSE 1 END,
      M.PARENT_OBJ_ID,
      M.SEQ
  </select>

  <select id="selectGroupsForSync" parameterType="map" resultType="map">
    SELECT
      G.ID,
      G.GROUP_NAME,
      G.MENU_OBJID,
      G.PARENT_GROUP_ID,
      P.GROUP_NAME AS PARENT_NAME
    FROM SCREEN_GROUPS G
    LEFT JOIN SCREEN_GROUPS P ON G.PARENT_GROUP_ID = P.ID
    WHERE (G.COMPANY_CODE = #{company_code} OR G.COMPANY_CODE = '*')
  </select>

  <select id="selectRootCompanyFolder" parameterType="map" resultType="map">
    SELECT ID
    FROM SCREEN_GROUPS
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
      AND PARENT_GROUP_ID IS NULL
      AND GROUP_LEVEL = '0'
    ORDER BY ID ASC
    LIMIT 1
  </select>

  <select id="getMaxRootDisplayOrder" resultType="int">
    SELECT COALESCE(MAX(DISPLAY_ORDER), 0) + 1
    FROM SCREEN_GROUPS
    WHERE PARENT_GROUP_ID IS NULL
  </select>

  <insert id="insertCompanyFolder" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_GROUPS (
      GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
      DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH
    ) VALUES (
      #{company_name}, #{group_code}, NULL, 0,
      #{display_order}, #{company_code}, #{user_id}, '/'
    )
  </insert>

  <update id="updateGroupHierarchyPathById" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET HIERARCHY_PATH = #{hierarchy_path}
    WHERE ID = #{id}
  </update>

  <insert id="insertScreenGroupForSync" parameterType="map" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    INSERT INTO SCREEN_GROUPS (
      GROUP_NAME, GROUP_CODE, PARENT_GROUP_ID, GROUP_LEVEL,
      DISPLAY_ORDER, COMPANY_CODE, WRITER, HIERARCHY_PATH,
      MENU_OBJID, DESCRIPTION, IS_ACTIVE
    ) VALUES (
      #{group_name}, #{group_code}, #{parent_group_id}, #{group_level},
      #{display_order}, #{company_code}, #{user_id}, #{hierarchy_path},
      #{menu_objid}, #{description}, 'Y'
    )
  </insert>

  <update id="updateScreenGroupForMenuSync" parameterType="map">
    UPDATE SCREEN_GROUPS
    SET MENU_OBJID   = #{menu_objid},
        UPDATED_DATE = NOW()
    WHERE ID = #{id}
  </update>

  <!-- ══════════════════════════════════════════════════════════════
       Sync: Status
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectSyncStatusGroups" parameterType="map" resultType="map">
    SELECT
      COUNT(*)                                           AS TOTAL_GROUPS,
      COUNT(CASE WHEN MENU_OBJID IS NOT NULL THEN 1 END) AS LINKED_GROUPS,
      COUNT(CASE WHEN MENU_OBJID IS NULL THEN 1 END)     AS UNLINKED_GROUPS
    FROM SCREEN_GROUPS
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <select id="selectSyncStatusMenus" parameterType="map" resultType="map">
    SELECT
      COUNT(*)                                                  AS TOTAL_MENUS,
      COUNT(CASE WHEN SCREEN_GROUP_ID IS NOT NULL THEN 1 END) AS LINKED_MENUS,
      COUNT(CASE WHEN SCREEN_GROUP_ID IS NULL THEN 1 END)     AS UNLINKED_MENUS
    FROM MENU_INFO
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
      AND MENU_TYPE = '1'
  </select>

  <!-- ══════════════════════════════════════════════════════════════
       Sync: All Companies
       ══════════════════════════════════════════════════════════════ -->

  <select id="selectAllCompanyCodes" resultType="map">
    SELECT DISTINCT COMPANY_CODE
    FROM SCREEN_GROUPS
    WHERE COMPANY_CODE != '*'
    ORDER BY COMPANY_CODE
  </select>

</mapper>
