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

    <!-- ── DDL 실행 로그 삽입 ─────────────────────────────── -->
    <insert id="insertDdlLog" parameterType="map">
        INSERT INTO DDL_EXECUTION_LOG (
            USER_ID
            , COMPANY_CODE
            , DDL_TYPE
            , TABLE_NAME
            , DDL_QUERY
            , SUCCESS
            , ERROR_MESSAGE
            , EXECUTED_AT
        ) VALUES (
            #{user_id}
            , #{company_code}
            , #{ddl_type}
            , #{table_name}
            , #{ddl_query}
            , #{success}
            , #{error_message}
            , NOW()
        )
    </insert>

    <!-- ── DDL 실행 로그 목록 조회 ───────────────────────── -->
    <select id="selectDdlLogs" parameterType="map" resultType="map">
        SELECT
            ID
            , USER_ID
            , COMPANY_CODE
            , DDL_TYPE
            , TABLE_NAME
            , SUCCESS
            , ERROR_MESSAGE
            , EXECUTED_AT
            , CASE
                WHEN LENGTH(DDL_QUERY) > 100 THEN SUBSTRING(DDL_QUERY, 1, 100) || '...'
                ELSE DDL_QUERY
              END AS DDL_QUERY_PREVIEW
        FROM DDL_EXECUTION_LOG
        WHERE 1=1
        <if test="user_id != null and user_id != ''">
            AND USER_ID = #{user_id}
        </if>
        <if test="ddl_type != null and ddl_type != ''">
            AND DDL_TYPE = #{ddl_type}
        </if>
        ORDER BY EXECUTED_AT DESC
        LIMIT #{limit}
    </select>

    <!-- ── DDL 전체 통계 ──────────────────────────────────── -->
    <select id="selectDdlTotalStats" parameterType="map" resultType="map">
        SELECT
            COUNT(*) AS TOTAL_EXECUTIONS
            , SUM(CASE WHEN SUCCESS = 'true' THEN 1 ELSE 0 END) AS SUCCESSFUL_EXECUTIONS
            , SUM(CASE WHEN SUCCESS = 'false' THEN 1 ELSE 0 END) AS FAILED_EXECUTIONS
        FROM DDL_EXECUTION_LOG
        WHERE 1=1
        <if test="from_date != null and from_date != ''">
            AND EXECUTED_AT >= CAST(#{from_date} AS TIMESTAMP)
        </if>
        <if test="to_date != null and to_date != ''">
            AND EXECUTED_AT &lt;= CAST(#{to_date} AS TIMESTAMP)
        </if>
    </select>

    <!-- ── DDL 타입별 통계 ───────────────────────────────── -->
    <select id="selectDdlStatsByType" parameterType="map" resultType="map">
        SELECT
            DDL_TYPE
            , COUNT(*) AS COUNT
        FROM DDL_EXECUTION_LOG
        WHERE 1=1
        <if test="from_date != null and from_date != ''">
            AND EXECUTED_AT >= CAST(#{from_date} AS TIMESTAMP)
        </if>
        <if test="to_date != null and to_date != ''">
            AND EXECUTED_AT &lt;= CAST(#{to_date} AS TIMESTAMP)
        </if>
        GROUP BY DDL_TYPE
        ORDER BY COUNT DESC
    </select>

    <!-- ── 사용자별 통계 (상위 10명) ─────────────────────── -->
    <select id="selectDdlStatsByUser" parameterType="map" resultType="map">
        SELECT
            USER_ID
            , COUNT(*) AS COUNT
        FROM DDL_EXECUTION_LOG
        WHERE 1=1
        <if test="from_date != null and from_date != ''">
            AND EXECUTED_AT >= CAST(#{from_date} AS TIMESTAMP)
        </if>
        <if test="to_date != null and to_date != ''">
            AND EXECUTED_AT &lt;= CAST(#{to_date} AS TIMESTAMP)
        </if>
        GROUP BY USER_ID
        ORDER BY COUNT DESC
        LIMIT 10
    </select>

    <!-- ── 최근 실패 로그 (최대 10건) ────────────────────── -->
    <select id="selectRecentFailures" parameterType="map" resultType="map">
        SELECT
            USER_ID
            , DDL_TYPE
            , TABLE_NAME
            , ERROR_MESSAGE
            , EXECUTED_AT
        FROM DDL_EXECUTION_LOG
        WHERE SUCCESS = 'false'
        <if test="from_date != null and from_date != ''">
            AND EXECUTED_AT >= CAST(#{from_date} AS TIMESTAMP)
        </if>
        <if test="to_date != null and to_date != ''">
            AND EXECUTED_AT &lt;= CAST(#{to_date} AS TIMESTAMP)
        </if>
        ORDER BY EXECUTED_AT DESC
        LIMIT 10
    </select>

    <!-- ── 테이블 DDL 히스토리 (최대 20건) ──────────────── -->
    <select id="selectTableDdlHistory" parameterType="map" resultType="map">
        SELECT
            ID
            , USER_ID
            , DDL_TYPE
            , DDL_QUERY
            , SUCCESS
            , ERROR_MESSAGE
            , EXECUTED_AT
        FROM DDL_EXECUTION_LOG
        WHERE TABLE_NAME = #{table_name}
        ORDER BY EXECUTED_AT DESC
        LIMIT 20
    </select>

    <!-- ── 오래된 DDL 로그 삭제 ──────────────────────────── -->
    <delete id="deleteOldDdlLogs" parameterType="map">
        DELETE FROM DDL_EXECUTION_LOG
        WHERE EXECUTED_AT &lt; CAST(#{cutoff_date} AS TIMESTAMP)
    </delete>

    <!-- ── 테이블 메타데이터 조회 ────────────────────────── -->
    <select id="selectTableInfo" parameterType="map" resultType="map">
        SELECT
            TABLE_NAME
            , TABLE_LABEL
            , DESCRIPTION
            , CREATED_DATE
            , UPDATED_DATE
        FROM TABLE_LABELS
        WHERE TABLE_NAME = #{table_name}
    </select>

    <!-- ── 테이블 컬럼 메타데이터 조회 ──────────────────── -->
    <select id="selectTableColumns" parameterType="map" resultType="map">
        SELECT *
        FROM TABLE_TYPE_COLUMNS
        WHERE TABLE_NAME = #{table_name}
          AND COMPANY_CODE = '*'
        ORDER BY DISPLAY_ORDER ASC
    </select>

</mapper>
