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

  <!-- ═══════════════════════════════════════════════════════════════════
       table_relationships
       ═══════════════════════════════════════════════════════════════════ -->

  <!-- 목록 조회 -->
  <select id="getTableRelationshipList" parameterType="map" resultType="map">
    SELECT *
    FROM TABLE_RELATIONSHIPS
    WHERE IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
    ORDER BY CREATED_DATE DESC
  </select>

  <!-- 단건 조회 -->
  <select id="getTableRelationshipInfo" parameterType="map" resultType="map">
    SELECT *
    FROM TABLE_RELATIONSHIPS
    WHERE RELATIONSHIP_ID = #{relationship_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
    LIMIT 1
  </select>

  <!-- 중복 확인 -->
  <select id="getExistingRelationshipInfo" parameterType="map" resultType="map">
    SELECT *
    FROM TABLE_RELATIONSHIPS
    WHERE DIAGRAM_ID = #{diagram_id}
      AND FROM_TABLE_NAME = #{from_table_name}
      AND FROM_COLUMN_NAME = #{from_column_name}
      AND TO_TABLE_NAME = #{to_table_name}
      AND TO_COLUMN_NAME = #{to_column_name}
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
      AND IS_ACTIVE = 'Y'
    LIMIT 1
  </select>

  <!-- max diagram_id 조회 -->
  <select id="getMaxDiagramId" parameterType="map" resultType="map">
    SELECT
        COALESCE(MAX(DIAGRAM_ID), 0) AS MAX_DIAGRAM_ID
    FROM TABLE_RELATIONSHIPS
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
  </select>

  <!-- 생성 -->
  <insert id="insertTableRelationship" parameterType="map"
          useGeneratedKeys="true" keyProperty="relationshipId" keyColumn="relationship_id">
    INSERT INTO TABLE_RELATIONSHIPS (
        DIAGRAM_ID
        , RELATIONSHIP_NAME
        , FROM_TABLE_NAME
        , FROM_COLUMN_NAME
        , TO_TABLE_NAME
        , TO_COLUMN_NAME
        , RELATIONSHIP_TYPE
        , CONNECTION_TYPE
        , COMPANY_CODE
        , SETTINGS
        , IS_ACTIVE
        , CREATED_BY
        , UPDATED_BY
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{diagram_id}
        , #{relationship_name}
        , #{from_table_name}
        , #{from_column_name}
        , #{to_table_name}
        , #{to_column_name}
        , #{relationship_type}
        , #{connection_type}
        , #{company_code}
        , <choose>
            <when test="settings != null">#{settings}::JSONB</when>
            <otherwise>'{}'::JSONB</otherwise>
          </choose>
        , 'Y'
        , #{created_by}
        , #{created_by}
        , NOW()
        , NOW()
    )
  </insert>

  <!-- 수정 (동적 SET) -->
  <update id="updateTableRelationship" parameterType="map">
    UPDATE TABLE_RELATIONSHIPS
    <set>
      UPDATED_BY = #{updated_by},
      UPDATED_DATE = NOW(),
      <if test="relationship_name  != null">RELATIONSHIP_NAME = #{relationship_name},</if>
      <if test="from_table_name     != null">FROM_TABLE_NAME = #{from_table_name},</if>
      <if test="from_column_name    != null">FROM_COLUMN_NAME = #{from_column_name},</if>
      <if test="to_table_name       != null">TO_TABLE_NAME = #{to_table_name},</if>
      <if test="to_column_name      != null">TO_COLUMN_NAME = #{to_column_name},</if>
      <if test="relationship_type  != null">RELATIONSHIP_TYPE = #{relationship_type},</if>
      <if test="connection_type    != null">CONNECTION_TYPE = #{connection_type},</if>
      <if test="settings          != null">SETTINGS = #{settings}::JSONB,</if>
    </set>
    WHERE RELATIONSHIP_ID = #{relationship_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
  </update>

  <!-- 소프트 삭제 -->
  <update id="softDeleteTableRelationship" parameterType="map">
    UPDATE TABLE_RELATIONSHIPS
    SET
        IS_ACTIVE = 'N'
        , UPDATED_DATE = NOW()
    WHERE RELATIONSHIP_ID = #{relationship_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
  </update>

  <!-- ═══════════════════════════════════════════════════════════════════
       diagram operations
       ═══════════════════════════════════════════════════════════════════ -->

  <!-- 관계도 목록용: 검색 + 정렬 (그룹화는 서비스에서) -->
  <select id="getRelationshipForDiagramList" parameterType="map" resultType="map">
    SELECT
        RELATIONSHIP_ID
        , DIAGRAM_ID
        , RELATIONSHIP_NAME
        , FROM_TABLE_NAME
        , TO_TABLE_NAME
        , CONNECTION_TYPE
        , RELATIONSHIP_TYPE
        , CREATED_DATE
        , CREATED_BY
        , UPDATED_DATE
        , UPDATED_BY
    FROM TABLE_RELATIONSHIPS
    WHERE IS_ACTIVE = 'Y'
      AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    <if test="search_term != null and search_term != ''">
      AND (RELATIONSHIP_NAME ILIKE '%' || #{search_term} || '%'
        OR FROM_TABLE_NAME ILIKE '%' || #{search_term} || '%'
        OR TO_TABLE_NAME ILIKE '%' || #{search_term} || '%')
    </if>
    ORDER BY DIAGRAM_ID ASC, CREATED_DATE DESC
  </select>

  <!-- diagram_id로 조회 -->
  <select id="getDiagramRelationshipListByDiagramId" parameterType="map" resultType="map">
    SELECT *
    FROM TABLE_RELATIONSHIPS
    WHERE DIAGRAM_ID = #{diagram_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
    ORDER BY CREATED_DATE ASC
  </select>

  <!-- 관계도 이름(첫 relationship_name)으로 diagram_id 찾아 전체 조회 -->
  <select id="getDiagramRelationshipListByName" parameterType="map" resultType="map">
    SELECT *
    FROM TABLE_RELATIONSHIPS
    WHERE DIAGRAM_ID IN (
      SELECT DISTINCT DIAGRAM_ID
      FROM TABLE_RELATIONSHIPS
      WHERE RELATIONSHIP_NAME = #{diagram_name}
        AND (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
        AND IS_ACTIVE = 'Y'
    )
    AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
    ORDER BY CREATED_DATE ASC
  </select>

  <!-- relationship_id로 속한 diagram의 모든 관계 조회 -->
  <select id="getDiagramRelationshipListByRelationshipId" parameterType="map" resultType="map">
    SELECT *
    FROM TABLE_RELATIONSHIPS
    WHERE DIAGRAM_ID = (
      SELECT DIAGRAM_ID
      FROM TABLE_RELATIONSHIPS
      WHERE RELATIONSHIP_ID = #{relationship_id}
        AND IS_ACTIVE = 'Y'
      LIMIT 1
    )
    AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
    ORDER BY CREATED_DATE ASC
  </select>

  <!-- 다이어그램 이름 목록 조회 (복제 시 이름 중복 방지용) -->
  <select id="getDiagramNameExists" parameterType="map" resultType="map">
    SELECT DISTINCT
        RELATIONSHIP_NAME AS diagram_name
    FROM TABLE_RELATIONSHIPS
    WHERE RELATIONSHIP_NAME LIKE #{pattern}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
  </select>

  <!-- diagram_id 기준 전체 소프트 삭제 -->
  <update id="softDeleteDiagramByDiagramId" parameterType="map">
    UPDATE TABLE_RELATIONSHIPS
    SET
        IS_ACTIVE = 'N'
        , UPDATED_DATE = NOW()
    WHERE DIAGRAM_ID = #{diagram_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
  </update>

  <!-- ═══════════════════════════════════════════════════════════════════
       data_relationship_bridge
       ═══════════════════════════════════════════════════════════════════ -->

  <!-- 데이터 연결 생성 -->
  <insert id="insertDataLink" parameterType="map"
          useGeneratedKeys="true" keyProperty="bridgeId" keyColumn="bridge_id">
    INSERT INTO DATA_RELATIONSHIP_BRIDGE (
        RELATIONSHIP_ID
        , FROM_TABLE_NAME
        , FROM_COLUMN_NAME
        , TO_TABLE_NAME
        , TO_COLUMN_NAME
        , CONNECTION_TYPE
        , COMPANY_CODE
        , BRIDGE_DATA
        , IS_ACTIVE
        , CREATED_BY
        , CREATED_DATE
    ) VALUES (
        #{relationship_id}
        , #{from_table_name}
        , #{from_column_name}
        , #{to_table_name}
        , #{to_column_name}
        , #{connection_type}
        , #{company_code}
        , <choose>
            <when test="bridge_data != null">#{bridge_data}::JSONB</when>
            <otherwise>'{}'::JSONB</otherwise>
          </choose>
        , 'Y'
        , #{created_by}
        , NOW()
    )
  </insert>

  <!-- 관계별 연결 데이터 조회 -->
  <select id="getDataLinkList" parameterType="map" resultType="map">
    SELECT *
    FROM DATA_RELATIONSHIP_BRIDGE
    WHERE RELATIONSHIP_ID = #{relationship_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
    ORDER BY CREATED_DATE DESC
  </select>

  <!-- 연결 소프트 삭제 -->
  <update id="softDeleteDataLink" parameterType="map">
    UPDATE DATA_RELATIONSHIP_BRIDGE
    SET
        IS_ACTIVE = 'N'
        , UPDATED_DATE = NOW()
    WHERE BRIDGE_ID = #{bridge_id}
      AND IS_ACTIVE = 'Y'
    <include refid="common.companyCodeFilter"/>
  </update>

  <!-- ═══════════════════════════════════════════════════════════════════
       dynamic table data
       ⚠ ${tableName} / ${searchColumn} 은 서비스에서 정규식 검증 후 사용
       ═══════════════════════════════════════════════════════════════════ -->

  <!-- 테이블 존재 확인 -->
  <select id="checkTableExists" parameterType="map" resultType="map">
    SELECT
        TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = #{table_name}
      AND TABLE_SCHEMA = 'public'
    LIMIT 1
  </select>

  <!-- 행 수 카운트 (동적 테이블) -->
  <select id="countTableData" parameterType="map" resultType="int">
    SELECT
        COUNT(*)
    FROM "${tableName}"
    <if test="search != null and search != '' and search_column != null and search_column != ''">
      WHERE "${searchColumn}" ILIKE '%' || #{search} || '%'
    </if>
  </select>

  <!-- 테이블 데이터 조회 (동적 테이블, 페이지네이션) -->
  <select id="getTableDataList" parameterType="map" resultType="map">
    SELECT *
    FROM "${tableName}"
    <if test="search != null and search != '' and search_column != null and search_column != ''">
      WHERE "${searchColumn}" ILIKE '%' || #{search} || '%'
    </if>
    ORDER BY 1
    LIMIT #{limit} OFFSET #{offset}
  </select>

</mapper>
