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

  <!-- ═══════════════════════════════════════════════════
       테이블 목록
  ═══════════════════════════════════════════════════ -->
  <select id="getTableList" resultType="map">
    SELECT
        T.TABLE_NAME
        , COALESCE(TL.TABLE_LABEL, T.TABLE_NAME) AS DISPLAY_NAME
        , COALESCE(TL.DESCRIPTION, '')            AS DESCRIPTION
        , (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
           WHERE TABLE_NAME = T.TABLE_NAME AND TABLE_SCHEMA = 'public') AS COLUMN_COUNT
    FROM INFORMATION_SCHEMA.TABLES T
    LEFT JOIN TABLE_LABELS TL
      ON T.TABLE_NAME = TL.TABLE_NAME
    WHERE T.TABLE_SCHEMA = 'public'
      AND T.TABLE_TYPE = 'BASE TABLE'
      AND T.TABLE_NAME NOT LIKE 'pg_%'
      AND T.TABLE_NAME NOT LIKE 'sql_%'
    ORDER BY T.TABLE_NAME
  </select>

  <!-- ═══════════════════════════════════════════════════
       컬럼 수
  ═══════════════════════════════════════════════════ -->
  <select id="getColumnListCnt" parameterType="map" resultType="int">
    SELECT
        COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS C
    WHERE C.TABLE_NAME = #{table_name}
  </select>

  <!-- ═══════════════════════════════════════════════════
       컬럼 목록 (company_code 없이 - 공통 설정만)
  ═══════════════════════════════════════════════════ -->
  <select id="getColumnList" parameterType="map" resultType="map">
    SELECT
        C.COLUMN_NAME
        , COALESCE(CL.COLUMN_LABEL, C.COLUMN_NAME) AS DISPLAY_NAME
        , C.DATA_TYPE
        , C.DATA_TYPE AS DB_TYPE
        , COALESCE(CL.INPUT_TYPE, 'text') AS WEB_TYPE
        , COALESCE(CL.INPUT_TYPE, 'direct') AS INPUT_TYPE
        , COALESCE(CL.DETAIL_SETTINGS::TEXT, '') AS DETAIL_SETTINGS
        , COALESCE(CL.DESCRIPTION, '') AS DESCRIPTION
        , CASE
            WHEN CL.IS_NULLABLE IS NOT NULL
            THEN CASE WHEN CL.IS_NULLABLE = 'N' THEN 'NO' ELSE 'YES' END
            ELSE C.IS_NULLABLE
          END AS IS_NULLABLE
        , CASE WHEN CL.IS_UNIQUE = 'Y' THEN 'YES' ELSE 'NO' END AS IS_UNIQUE
        , CASE WHEN PK.COLUMN_NAME IS NOT NULL THEN TRUE ELSE FALSE END AS IS_PRIMARY_KEY
        , C.COLUMN_DEFAULT AS DEFAULT_VALUE
        , C.CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH
        , C.NUMERIC_PRECISION
        , C.NUMERIC_SCALE
        , CL.CODE_INFO
        , CL.CODE_VALUE
        , CL.REFERENCE_TABLE
        , CL.REFERENCE_COLUMN
        , CL.DISPLAY_COLUMN
        , CL.DISPLAY_ORDER
        , CL.IS_VISIBLE
        , DCL.COLUMN_LABEL AS DISPLAY_COLUMN_LABEL
    FROM INFORMATION_SCHEMA.COLUMNS C
    LEFT JOIN TABLE_TYPE_COLUMNS CL
      ON C.TABLE_NAME = CL.TABLE_NAME AND C.COLUMN_NAME = CL.COLUMN_NAME AND CL.COMPANY_CODE = '*'
    LEFT JOIN TABLE_TYPE_COLUMNS DCL
      ON CL.REFERENCE_TABLE = DCL.TABLE_NAME AND CL.DISPLAY_COLUMN = DCL.COLUMN_NAME AND DCL.COMPANY_CODE = '*'
    LEFT JOIN (
      SELECT
          KCU.COLUMN_NAME
          , KCU.TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
      WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND TC.TABLE_NAME = #{table_name}
    ) PK
      ON C.COLUMN_NAME = PK.COLUMN_NAME AND C.TABLE_NAME = PK.TABLE_NAME
    WHERE C.TABLE_NAME = #{table_name}
    ORDER BY C.ORDINAL_POSITION
    LIMIT #{size} OFFSET #{offset}
  </select>

  <!-- ═══════════════════════════════════════════════════
       컬럼 목록 (company_code 포함 - 회사별 설정)
  ═══════════════════════════════════════════════════ -->
  <select id="getColumnListWithCompany" parameterType="map" resultType="map">
    SELECT
        C.COLUMN_NAME
        , COALESCE(NULLIF(TTC.COLUMN_LABEL, C.COLUMN_NAME), CL.COLUMN_LABEL, C.COLUMN_NAME) AS DISPLAY_NAME
        , C.DATA_TYPE
        , C.DATA_TYPE AS DB_TYPE
        , COALESCE(TTC.INPUT_TYPE, CL.INPUT_TYPE, 'text') AS WEB_TYPE
        , COALESCE(TTC.INPUT_TYPE, CL.INPUT_TYPE, 'direct') AS INPUT_TYPE
        , COALESCE(TTC.DETAIL_SETTINGS::TEXT, CL.DETAIL_SETTINGS::TEXT, '') AS DETAIL_SETTINGS
        , COALESCE(TTC.DESCRIPTION, CL.DESCRIPTION, '') AS DESCRIPTION
        , CASE
            WHEN COALESCE(TTC.IS_NULLABLE, CL.IS_NULLABLE) IS NOT NULL
            THEN CASE WHEN COALESCE(TTC.IS_NULLABLE, CL.IS_NULLABLE) = 'N' THEN 'NO' ELSE 'YES' END
            ELSE C.IS_NULLABLE
          END AS IS_NULLABLE
        , CASE WHEN COALESCE(TTC.IS_UNIQUE, CL.IS_UNIQUE) = 'Y' THEN 'YES' ELSE 'NO' END AS IS_UNIQUE
        , CASE WHEN PK.COLUMN_NAME IS NOT NULL THEN TRUE ELSE FALSE END AS IS_PRIMARY_KEY
        , C.COLUMN_DEFAULT AS DEFAULT_VALUE
        , C.CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH
        , C.NUMERIC_PRECISION
        , C.NUMERIC_SCALE
        , COALESCE(TTC.CODE_INFO, CL.CODE_INFO) AS CODE_INFO
        , COALESCE(TTC.CODE_VALUE, CL.CODE_VALUE) AS CODE_VALUE
        , COALESCE(TTC.REFERENCE_TABLE, CL.REFERENCE_TABLE) AS REFERENCE_TABLE
        , COALESCE(TTC.REFERENCE_COLUMN, CL.REFERENCE_COLUMN) AS REFERENCE_COLUMN
        , COALESCE(TTC.DISPLAY_COLUMN, CL.DISPLAY_COLUMN) AS DISPLAY_COLUMN
        , COALESCE(TTC.DISPLAY_ORDER, CL.DISPLAY_ORDER) AS DISPLAY_ORDER
        , COALESCE(TTC.IS_VISIBLE, CL.IS_VISIBLE) AS IS_VISIBLE
        , DCL.COLUMN_LABEL AS DISPLAY_COLUMN_LABEL
    FROM INFORMATION_SCHEMA.COLUMNS C
    LEFT JOIN TABLE_TYPE_COLUMNS CL
      ON C.TABLE_NAME = CL.TABLE_NAME AND C.COLUMN_NAME = CL.COLUMN_NAME AND CL.COMPANY_CODE = '*'
    LEFT JOIN TABLE_TYPE_COLUMNS TTC
      ON C.TABLE_NAME = TTC.TABLE_NAME AND C.COLUMN_NAME = TTC.COLUMN_NAME AND (TTC.COMPANY_CODE = #{company_code} OR TTC.COMPANY_CODE = '*')
    LEFT JOIN TABLE_TYPE_COLUMNS DCL
      ON COALESCE(TTC.REFERENCE_TABLE, CL.REFERENCE_TABLE) = DCL.TABLE_NAME
      AND COALESCE(TTC.DISPLAY_COLUMN, CL.DISPLAY_COLUMN) = DCL.COLUMN_NAME
      AND DCL.COMPANY_CODE = '*'
    LEFT JOIN (
      SELECT
          KCU.COLUMN_NAME
          , KCU.TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
      JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        ON TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
      WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND TC.TABLE_NAME = #{table_name}
    ) PK
      ON C.COLUMN_NAME = PK.COLUMN_NAME AND C.TABLE_NAME = PK.TABLE_NAME
    WHERE C.TABLE_NAME = #{table_name}
    ORDER BY C.ORDINAL_POSITION
    LIMIT #{size} OFFSET #{offset}
  </select>

  <!-- ═══════════════════════════════════════════════════
       테이블 스키마 (컬럼 타입 정보)
  ═══════════════════════════════════════════════════ -->
  <select id="getTableSchemaList" parameterType="map" resultType="map">
    SELECT
        C.COLUMN_NAME
        , C.COLUMN_NAME AS DISPLAY_NAME
        , C.DATA_TYPE
        , C.UDT_NAME AS DB_TYPE
        , C.IS_NULLABLE
        , C.COLUMN_DEFAULT AS DEFAULT_VALUE
        , C.CHARACTER_MAXIMUM_LENGTH AS MAX_LENGTH
        , C.NUMERIC_PRECISION
        , C.NUMERIC_SCALE
        , CASE
            WHEN C.COLUMN_NAME IN (
              SELECT KCU.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
              WHERE KCU.TABLE_NAME = #{table_name} AND KCU.CONSTRAINT_NAME LIKE '%_pkey'
            ) THEN TRUE ELSE FALSE
          END AS IS_PRIMARY_KEY
        , COL_DESCRIPTION(
            (SELECT OID FROM PG_CLASS
             WHERE RELNAME = #{table_name}
               AND RELNAMESPACE = (SELECT OID FROM PG_NAMESPACE WHERE NSPNAME = 'public')),
            C.ORDINAL_POSITION
          ) AS COLUMN_COMMENT
    FROM INFORMATION_SCHEMA.COLUMNS C
    WHERE C.TABLE_NAME = #{table_name}
      AND C.TABLE_SCHEMA = 'public'
    ORDER BY C.ORDINAL_POSITION
  </select>

  <!-- ═══════════════════════════════════════════════════
       테이블 존재 여부
  ═══════════════════════════════════════════════════ -->
  <select id="checkTableExists" parameterType="map" resultType="map">
    SELECT EXISTS (
      SELECT 1 FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = #{table_name}
        AND TABLE_SCHEMA = 'public'
        AND TABLE_TYPE = 'BASE TABLE'
    ) AS EXISTS
  </select>

  <!-- ═══════════════════════════════════════════════════
       테이블 라벨
  ═══════════════════════════════════════════════════ -->
  <select id="getTableLabelInfo" parameterType="map" resultType="map">
    SELECT
        TABLE_NAME
        , TABLE_LABEL
        , DESCRIPTION
        , CREATED_DATE
        , UPDATED_DATE
    FROM TABLE_LABELS
    WHERE TABLE_NAME = #{table_name}
  </select>

  <insert id="upsertTableLabel" parameterType="map">
    INSERT INTO TABLE_LABELS (
        TABLE_NAME
        , TABLE_LABEL
        , DESCRIPTION
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{display_name}
        , #{description}
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME)
    DO UPDATE SET
        TABLE_LABEL  = EXCLUDED.TABLE_LABEL
        , DESCRIPTION  = EXCLUDED.DESCRIPTION
        , UPDATED_DATE = NOW()
  </insert>

  <insert id="insertTableLabelIfNotExists" parameterType="map">
    INSERT INTO TABLE_LABELS (
        TABLE_NAME
        , TABLE_LABEL
        , DESCRIPTION
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{table_name}
        , ''
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME) DO NOTHING
  </insert>

  <!-- ═══════════════════════════════════════════════════
       컬럼 라벨 (단건)
  ═══════════════════════════════════════════════════ -->
  <select id="getColumnLabelInfo" parameterType="map" resultType="map">
    SELECT
        ID
        , TABLE_NAME
        , COLUMN_NAME
        , COLUMN_LABEL
        , INPUT_TYPE AS WEB_TYPE
        , DETAIL_SETTINGS::TEXT AS DETAIL_SETTINGS
        , DESCRIPTION
        , DISPLAY_ORDER
        , IS_VISIBLE
        , CODE_INFO
        , CODE_VALUE
        , REFERENCE_TABLE
        , REFERENCE_COLUMN
        , CREATED_DATE
        , UPDATED_DATE
    FROM TABLE_TYPE_COLUMNS
    WHERE TABLE_NAME = #{table_name}
      AND COLUMN_NAME = #{column_name}
      AND COMPANY_CODE = '*'
  </select>

  <!-- ═══════════════════════════════════════════════════
       컬럼 설정 UPSERT (전체 설정)
  ═══════════════════════════════════════════════════ -->
  <insert id="upsertColumnSettings" parameterType="map">
    INSERT INTO TABLE_TYPE_COLUMNS (
        TABLE_NAME
        , COLUMN_NAME
        , COLUMN_LABEL
        , INPUT_TYPE
        , DETAIL_SETTINGS
        , CODE_INFO
        , CODE_VALUE
        , REFERENCE_TABLE
        , REFERENCE_COLUMN
        , DISPLAY_COLUMN
        , DISPLAY_ORDER
        , IS_VISIBLE
        , IS_NULLABLE
        , COMPANY_CODE
        , CATEGORY_REF
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{column_name}
        , #{column_label}
        , #{input_type}
        , #{detail_settings}::JSONB
        , #{code_info}
        , #{code_value}
        , #{reference_table}
        , #{reference_column}
        , #{display_column}
        , #{display_order}
        , #{is_visible}
        , COALESCE(#{is_nullable}, 'Y')
        , #{company_code}
        , #{category_ref}
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME, COLUMN_NAME, COMPANY_CODE)
    DO UPDATE SET
        COLUMN_LABEL     = COALESCE(EXCLUDED.COLUMN_LABEL,     TABLE_TYPE_COLUMNS.COLUMN_LABEL)
        , INPUT_TYPE       = COALESCE(EXCLUDED.INPUT_TYPE,       TABLE_TYPE_COLUMNS.INPUT_TYPE)
        , DETAIL_SETTINGS  = COALESCE(EXCLUDED.DETAIL_SETTINGS,  TABLE_TYPE_COLUMNS.DETAIL_SETTINGS)
        , CODE_INFO    = EXCLUDED.CODE_INFO
        , CODE_VALUE       = EXCLUDED.CODE_VALUE
        , REFERENCE_TABLE  = EXCLUDED.REFERENCE_TABLE
        , REFERENCE_COLUMN = EXCLUDED.REFERENCE_COLUMN
        , DISPLAY_COLUMN   = EXCLUDED.DISPLAY_COLUMN
        , DISPLAY_ORDER    = COALESCE(EXCLUDED.DISPLAY_ORDER,    TABLE_TYPE_COLUMNS.DISPLAY_ORDER)
        , IS_VISIBLE       = COALESCE(EXCLUDED.IS_VISIBLE,       TABLE_TYPE_COLUMNS.IS_VISIBLE)
        , IS_NULLABLE      = COALESCE(EXCLUDED.IS_NULLABLE,      TABLE_TYPE_COLUMNS.IS_NULLABLE)
        , CATEGORY_REF     = EXCLUDED.CATEGORY_REF
        , UPDATED_DATE     = NOW()
  </insert>

  <!-- ═══════════════════════════════════════════════════
       컬럼 입력 타입 UPSERT (input_type 전용)
  ═══════════════════════════════════════════════════ -->
  <insert id="upsertColumnInputType" parameterType="map">
    INSERT INTO TABLE_TYPE_COLUMNS (
        TABLE_NAME
        , COLUMN_NAME
        , INPUT_TYPE
        , DETAIL_SETTINGS
        , IS_NULLABLE
        , DISPLAY_ORDER
        , COMPANY_CODE
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{column_name}
        , #{input_type}
        , #{detail_settings}::JSONB
        , 'Y'
        , 0
        , #{company_code}
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME, COLUMN_NAME, COMPANY_CODE)
    DO UPDATE SET
        INPUT_TYPE      = EXCLUDED.INPUT_TYPE
        , DETAIL_SETTINGS = EXCLUDED.DETAIL_SETTINGS
        , REFERENCE_TABLE = CASE WHEN #{clear_entity} = 'true' THEN NULL ELSE TABLE_TYPE_COLUMNS.REFERENCE_TABLE END
        , REFERENCE_COLUMN= CASE WHEN #{clear_entity} = 'true' THEN NULL ELSE TABLE_TYPE_COLUMNS.REFERENCE_COLUMN END
        , DISPLAY_COLUMN  = CASE WHEN #{clear_entity} = 'true' THEN NULL ELSE TABLE_TYPE_COLUMNS.DISPLAY_COLUMN END
        , CODE_INFO   = CASE WHEN #{clear_code} = 'true' THEN NULL ELSE TABLE_TYPE_COLUMNS.CODE_INFO END
        , CODE_VALUE      = CASE WHEN #{clear_code} = 'true' THEN NULL ELSE TABLE_TYPE_COLUMNS.CODE_VALUE END
        , CATEGORY_REF    = CASE WHEN #{clear_category} = 'true' THEN NULL ELSE TABLE_TYPE_COLUMNS.CATEGORY_REF END
        , UPDATED_DATE    = NOW()
  </insert>

  <!-- ═══════════════════════════════════════════════════
       컬럼 입력타입 목록 (company-specific + fallback *)
  ═══════════════════════════════════════════════════ -->
  <select id="getColumnInputTypeList" parameterType="map" resultType="map">
    SELECT DISTINCT ON (TTC.COLUMN_NAME)
        TTC.COLUMN_NAME
        , COALESCE(TTC.COLUMN_LABEL, TTC.COLUMN_NAME) AS DISPLAY_NAME
        , TTC.INPUT_TYPE
        , COALESCE(TTC.DETAIL_SETTINGS::TEXT, '{}') AS DETAIL_SETTINGS
        , TTC.IS_NULLABLE
        , IC.DATA_TYPE
        , TTC.COMPANY_CODE
        , TTC.CATEGORY_REF
    FROM TABLE_TYPE_COLUMNS TTC
    LEFT JOIN INFORMATION_SCHEMA.COLUMNS IC
      ON TTC.TABLE_NAME = IC.TABLE_NAME AND TTC.COLUMN_NAME = IC.COLUMN_NAME
    WHERE TTC.TABLE_NAME = #{table_name}
      AND TTC.COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY TTC.COLUMN_NAME,
      CASE WHEN TTC.COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END,
      TTC.DISPLAY_ORDER
  </select>

  <!-- ═══════════════════════════════════════════════════
       제약조건 - PK
  ═══════════════════════════════════════════════════ -->
  <select id="getTablePrimaryKeyList" parameterType="map" resultType="map">
    SELECT
        TC.CONNAME AS constraint_name
        , ARRAY_AGG(A.ATTNAME ORDER BY X.N)::text AS columns
    FROM PG_CONSTRAINT TC
    JOIN PG_CLASS C
      ON TC.CONRELID = C.OID
    JOIN PG_NAMESPACE NS
      ON C.RELNAMESPACE = NS.OID
    CROSS JOIN LATERAL UNNEST(TC.CONKEY) WITH ORDINALITY AS X(ATTNUM, N)
    JOIN PG_ATTRIBUTE A
      ON A.ATTRELID = TC.CONRELID AND A.ATTNUM = X.ATTNUM
    WHERE NS.NSPNAME = 'public'
      AND C.RELNAME = #{table_name}
      AND TC.CONTYPE = 'p'
    GROUP BY TC.CONNAME
  </select>

  <!-- ═══════════════════════════════════════════════════
       제약조건 - 인덱스 (PK 제외)
  ═══════════════════════════════════════════════════ -->
  <select id="getTableIndexList" parameterType="map" resultType="map">
    SELECT
        I.RELNAME AS index_name
        , IX.INDISUNIQUE AS is_unique
        , ARRAY_AGG(A.ATTNAME ORDER BY X.N)::text AS columns
    FROM PG_INDEX IX
    JOIN PG_CLASS T
      ON IX.INDRELID = T.OID
    JOIN PG_CLASS I
      ON IX.INDEXRELID = I.OID
    JOIN PG_NAMESPACE NS
      ON T.RELNAMESPACE = NS.OID
    CROSS JOIN LATERAL UNNEST(IX.INDKEY) WITH ORDINALITY AS X(ATTNUM, N)
    JOIN PG_ATTRIBUTE A
      ON A.ATTRELID = T.OID AND A.ATTNUM = X.ATTNUM
    WHERE NS.NSPNAME = 'public'
      AND T.RELNAME = #{table_name}
      AND IX.INDISPRIMARY = FALSE
    GROUP BY I.RELNAME, IX.INDISUNIQUE
    ORDER BY I.RELNAME
  </select>

  <!-- ═══════════════════════════════════════════════════
       NOT NULL / UNIQUE 소프트 제약조건
  ═══════════════════════════════════════════════════ -->
  <insert id="upsertNullable" parameterType="map">
    INSERT INTO TABLE_TYPE_COLUMNS (
        TABLE_NAME
        , COLUMN_NAME
        , IS_NULLABLE
        , COMPANY_CODE
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{column_name}
        , #{is_nullable}
        , #{company_code}
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME, COLUMN_NAME, COMPANY_CODE)
    DO UPDATE SET
        IS_NULLABLE = #{is_nullable}
        , UPDATED_DATE = NOW()
  </insert>

  <insert id="upsertUnique" parameterType="map">
    INSERT INTO TABLE_TYPE_COLUMNS (
        TABLE_NAME
        , COLUMN_NAME
        , IS_UNIQUE
        , COMPANY_CODE
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{column_name}
        , #{is_unique}
        , #{company_code}
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME, COLUMN_NAME, COMPANY_CODE)
    DO UPDATE SET
        IS_UNIQUE = #{is_unique}
        , UPDATED_DATE = NOW()
  </insert>

  <!-- ═══════════════════════════════════════════════════
       컬럼 존재 여부
  ═══════════════════════════════════════════════════ -->
  <select id="checkTableHasColumn" parameterType="map" resultType="int">
    SELECT
        COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME  = #{table_name}
      AND COLUMN_NAME = #{column_name}
      AND TABLE_SCHEMA = 'public'
  </select>

  <!-- ═══════════════════════════════════════════════════
       NOT NULL 제약 컬럼 목록
  ═══════════════════════════════════════════════════ -->
  <select id="getNotNullColumnList" parameterType="map" resultType="map">
    SELECT DISTINCT ON (COLUMN_NAME)
        COLUMN_NAME AS column_name
    FROM TABLE_TYPE_COLUMNS
    WHERE TABLE_NAME   = #{table_name}
      AND IS_NULLABLE  = 'N'
      AND COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY COLUMN_NAME,
      CASE WHEN COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END
  </select>

  <!-- ═══════════════════════════════════════════════════
       UNIQUE 제약 컬럼 목록
  ═══════════════════════════════════════════════════ -->
  <select id="getUniqueColumnList" parameterType="map" resultType="map">
    SELECT DISTINCT ON (COLUMN_NAME)
        COLUMN_NAME AS column_name
    FROM TABLE_TYPE_COLUMNS
    WHERE TABLE_NAME   = #{table_name}
      AND IS_UNIQUE    = 'Y'
      AND COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY COLUMN_NAME,
      CASE WHEN COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END
  </select>

  <!-- ═══════════════════════════════════════════════════
       카테고리 컬럼 (회사별)
  ═══════════════════════════════════════════════════ -->
  <select id="getCategoryColumnListByCompany" parameterType="map" resultType="map">
    SELECT DISTINCT ON (TTC.TABLE_NAME, TTC.COLUMN_NAME)
        TTC.TABLE_NAME
        , TTC.COLUMN_NAME
        , COALESCE(TTC.COLUMN_LABEL, TTC.COLUMN_NAME) AS COLUMN_LABEL
        , TTC.CATEGORY_REF
        , TTC.COMPANY_CODE
        , TL.TABLE_LABEL
    FROM TABLE_TYPE_COLUMNS TTC
    LEFT JOIN TABLE_LABELS TL
      ON TTC.TABLE_NAME = TL.TABLE_NAME
    WHERE TTC.INPUT_TYPE = 'category'
      AND TTC.COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY TTC.TABLE_NAME, TTC.COLUMN_NAME,
      CASE WHEN TTC.COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END
  </select>

  <!-- ═══════════════════════════════════════════════════
       채번 컬럼 (회사별)
  ═══════════════════════════════════════════════════ -->
  <select id="getNumberingColumnListByCompany" parameterType="map" resultType="map">
    SELECT DISTINCT ON (TTC.TABLE_NAME, TTC.COLUMN_NAME)
        TTC.TABLE_NAME
        , TTC.COLUMN_NAME
        , COALESCE(TTC.COLUMN_LABEL, TTC.COLUMN_NAME) AS COLUMN_LABEL
        , TTC.COMPANY_CODE
        , TL.TABLE_LABEL
    FROM TABLE_TYPE_COLUMNS TTC
    LEFT JOIN TABLE_LABELS TL
      ON TTC.TABLE_NAME = TL.TABLE_NAME
    WHERE TTC.INPUT_TYPE = 'numbering'
      AND TTC.COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY TTC.TABLE_NAME, TTC.COLUMN_NAME,
      CASE WHEN TTC.COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END
  </select>

  <!-- ═══════════════════════════════════════════════════
       카테고리 컬럼 (메뉴별)
  ═══════════════════════════════════════════════════ -->
  <select id="getCategoryColumnListByMenu" parameterType="map" resultType="map">
    SELECT DISTINCT ON (TTC.TABLE_NAME, TTC.COLUMN_NAME)
        TTC.TABLE_NAME
        , TTC.COLUMN_NAME
        , COALESCE(TTC.COLUMN_LABEL, TTC.COLUMN_NAME) AS COLUMN_LABEL
        , TTC.CATEGORY_REF
        , CCM.MENU_OBJID
    FROM TABLE_TYPE_COLUMNS TTC
    JOIN CATEGORY_COLUMN_MAPPING CCM
      ON TTC.TABLE_NAME = CCM.TABLE_NAME
      AND TTC.COLUMN_NAME = CCM.LOGICAL_COLUMN_NAME
      AND CCM.COMPANY_CODE IN (#{company_code}, '*')
    WHERE TTC.INPUT_TYPE = 'category'
      AND TTC.COMPANY_CODE IN (#{company_code}, '*')
      AND CCM.MENU_OBJID = #{menu_objid}
    ORDER BY TTC.TABLE_NAME, TTC.COLUMN_NAME,
      CASE WHEN TTC.COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END
  </select>

  <!-- ═══════════════════════════════════════════════════
       엔티티 관계 (두 테이블 간)
  ═══════════════════════════════════════════════════ -->
  <select id="getEntityRelationList" parameterType="map" resultType="map">
    SELECT
        LEFT_COL.COLUMN_NAME AS LEFT_COLUMN
        , LEFT_COL.REFERENCE_TABLE AS RIGHT_TABLE
        , LEFT_COL.REFERENCE_COLUMN AS RIGHT_COLUMN
        , LEFT_COL.DISPLAY_COLUMN
        , COALESCE(LEFT_COL.COLUMN_LABEL, LEFT_COL.COLUMN_NAME) AS COLUMN_LABEL
    FROM TABLE_TYPE_COLUMNS LEFT_COL
    WHERE LEFT_COL.TABLE_NAME = #{left_table}
      AND LEFT_COL.REFERENCE_TABLE = #{right_table}
      AND LEFT_COL.INPUT_TYPE = 'entity'
      AND LEFT_COL.COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY LEFT_COL.DISPLAY_ORDER
  </select>

  <!-- ═══════════════════════════════════════════════════
       현재 테이블을 참조하는 테이블 목록
  ═══════════════════════════════════════════════════ -->
  <select id="getReferencedByTableList" parameterType="map" resultType="map">
    SELECT DISTINCT
        TTC.TABLE_NAME
        , TTC.COLUMN_NAME
        , COALESCE(TTC.COLUMN_LABEL, TTC.COLUMN_NAME) AS COLUMN_LABEL
        , TTC.REFERENCE_COLUMN
        , TTC.DISPLAY_COLUMN
        , COALESCE(TL.TABLE_LABEL, TTC.TABLE_NAME) AS TABLE_LABEL
    FROM TABLE_TYPE_COLUMNS TTC
    LEFT JOIN TABLE_LABELS TL
      ON TTC.TABLE_NAME = TL.TABLE_NAME
    WHERE TTC.REFERENCE_TABLE = #{table_name}
      AND TTC.INPUT_TYPE = 'entity'
      AND TTC.COMPANY_CODE IN (#{company_code}, '*')
    ORDER BY TTC.TABLE_NAME, TTC.COLUMN_NAME
  </select>

  <!-- ═══════════════════════════════════════════════════
       로그 설정 조회
  ═══════════════════════════════════════════════════ -->
  <select id="getLogConfigInfo" parameterType="map" resultType="map">
    SELECT
        TABLE_NAME
        , IS_ACTIVE
        , LOG_COLUMNS
        , CREATED_DATE
        , UPDATED_DATE
    FROM LOG_TABLES
    WHERE TABLE_NAME = #{table_name}
  </select>

  <insert id="upsertLogConfig" parameterType="map">
    INSERT INTO LOG_TABLES (
        TABLE_NAME
        , IS_ACTIVE
        , LOG_COLUMNS
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{table_name}
        , #{is_active}
        , #{log_columns}
        , NOW()
        , NOW()
    )
    ON CONFLICT (TABLE_NAME)
    DO UPDATE SET
        IS_ACTIVE    = #{is_active}
        , LOG_COLUMNS  = #{log_columns}
        , UPDATED_DATE = NOW()
  </insert>

  <!-- ═══════════════════════════════════════════════════
       DB 연결 확인
  ═══════════════════════════════════════════════════ -->
  <select id="checkDatabaseConnection" resultType="map">
    SELECT
        1 AS RESULT
        , NOW() AS TIMESTAMP
  </select>

  <!-- ═══════════════════════════════════════════════════
       화면 레이아웃 동기화 (컬럼 입력 타입 변경 시)
  ═══════════════════════════════════════════════════ -->
  <update id="syncScreenLayoutsInputType" parameterType="map">
    UPDATE SCREEN_LAYOUTS SL
    SET
        PROPERTIES = JSONB_SET(
          JSONB_SET(
            SL.PROPERTIES::JSONB,
            '{widgetType}', TO_JSONB(#{component_id}::TEXT)
          ),
          '{componentType}', TO_JSONB(#{component_id}::TEXT)
        )::TEXT
    FROM SCREEN_DEFINITIONS SD
    WHERE SL.SCREEN_ID = SD.SCREEN_ID
      AND SL.PROPERTIES::JSONB->>'tableName' = #{table_name}
      AND SL.PROPERTIES::JSONB->>'columnName' = #{column_name}
      AND ((SD.COMPANY_CODE = #{company_code} OR SD.COMPANY_CODE = '*') OR #{company_code} = '*')
  </update>

</mapper>
