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

    <!-- 모든 활성 카테고리 조회 -->
    <select id="selectAllCategories" resultType="map">
        SELECT *

        FROM db_type_categories

        WHERE is_active = TRUE

        ORDER BY sort_order ASC, display_name ASC
    </select>

    <!-- 특정 typeCode 카테고리 조회 -->
    <select id="selectCategoryByTypeCode" parameterType="map" resultType="map">
        SELECT *

        FROM db_type_categories

        WHERE type_code = #{type_code}
    </select>

    <!-- typeCode 존재 여부 확인 -->
    <select id="countByTypeCode" parameterType="map" resultType="int">
        SELECT COUNT(*)

        FROM db_type_categories

        WHERE type_code = #{type_code}
    </select>

    <!-- 카테고리 생성 -->
    <insert id="insertCategory" parameterType="map">
        INSERT INTO db_type_categories
            (type_code, display_name, icon, color, sort_order, is_active, CREATED_DATE, UPDATED_DATE)
        VALUES
            (#{type_code}, #{display_name}, #{icon}, #{color},
             #{sort_order, jdbcType=INTEGER}, TRUE, NOW(), NOW())
    </insert>

    <!-- 카테고리 수정 (동적 UPDATE) -->
    <update id="updateCategory" parameterType="map">
        UPDATE db_type_categories
        <set>
            <if test="display_name != null">display_name = #{display_name},</if>
            <if test="icon != null">icon = #{icon},</if>
            <if test="color != null">color = #{color},</if>
            <if test="sort_order != null">sort_order = #{sort_order},</if>
            <if test="is_active != null">is_active = #{is_active},</if>
            UPDATED_DATE = NOW()
        </set>
        WHERE type_code = #{type_code}
    </update>

    <!-- 카테고리 비활성화 (소프트 삭제) -->
    <update id="deactivateCategory" parameterType="map">
        UPDATE db_type_categories
        SET is_active = FALSE, UPDATED_DATE = NOW()
        WHERE type_code = #{type_code}
    </update>

    <!-- 해당 DB 타입 사용 중인 연결 수 조회 -->
    <select id="countConnectionsByType" parameterType="map" resultType="int">
        SELECT COUNT(*)

        FROM external_db_connections

        WHERE db_type = #{type_code}
          AND is_active = 'Y'
    </select>

    <!-- DB 타입별 연결 통계 조회 -->
    <select id="selectConnectionStatsByType" resultType="map">
        SELECT
            c.*,
            COUNT(e.id) AS connection_count

        FROM db_type_categories c

        LEFT JOIN external_db_connections e
            ON c.type_code = e.db_type AND e.is_active = 'Y'

        WHERE c.is_active = TRUE

        GROUP BY c.type_code, c.display_name, c.icon, c.color,
                 c.sort_order, c.is_active, c.CREATED_DATE, c.UPDATED_DATE

        ORDER BY c.sort_order ASC
    </select>

    <!-- 기본 카테고리 초기화 (ON CONFLICT DO NOTHING) -->
    <insert id="insertDefaultCategories">
        INSERT INTO db_type_categories
            (type_code, display_name, icon, color, sort_order, is_active, CREATED_DATE, UPDATED_DATE)
        VALUES
            ('postgresql', 'PostgreSQL', 'postgresql', '#336791', 1, TRUE, NOW(), NOW()),
            ('oracle',     'Oracle',     'oracle',     '#F80000', 2, TRUE, NOW(), NOW()),
            ('mysql',      'MySQL',      'mysql',      '#4479A1', 3, TRUE, NOW(), NOW()),
            ('mariadb',    'MariaDB',    'mariadb',    '#003545', 4, TRUE, NOW(), NOW()),
            ('mssql',      'SQL Server', 'mssql',      '#CC2927', 5, TRUE, NOW(), NOW())
        ON CONFLICT (type_code) DO NOTHING
    </insert>

</mapper>
