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

  <!-- 공통 SELECT 컬럼 -->
  <sql id="historyColumns">
    log_id,
    operation_type,
    original_id,
    changed_column,
    old_value,
    new_value,
    changed_by,
    changed_at,
    ip_address,
    user_agent,
    full_row_before,
    full_row_after
  </sql>

  <!-- 전체 이력 WHERE 조건 (original_id 제외) -->
  <sql id="allHistoryFilter">
    <if test="operation_type != null and operation_type != ''">
      AND operation_type = #{operation_type}
    </if>
    <if test="changed_by_like != null">
      AND changed_by ILIKE #{changed_by_like}
    </if>
    <if test="start_date != null and start_date != ''">
      AND changed_at &gt;= #{start_date}::timestamp
    </if>
    <if test="end_date != null and end_date != ''">
      AND changed_at &lt;= #{end_date}::timestamp
    </if>
  </sql>

  <!-- 특정 레코드 이력 WHERE 조건 -->
  <sql id="recordHistoryFilter">
    WHERE original_id = #{record_id}
    <include refid="allHistoryFilter"/>
  </sql>

  <!-- ─── 이력 테이블 존재 여부 확인 ─────────────────────────────── -->
  <!-- logTableName은 #{} 바인딩 사용 (SQL 인젝션 안전) -->
  <select id="checkHistoryTableExists" parameterType="map" resultType="map">
    SELECT EXISTS (
      SELECT FROM information_schema.tables
      WHERE table_schema = 'public'
        AND table_name = #{log_table_name}
    ) AS "exists"
  </select>

  <!-- ─── 테이블 전체 이력 요약 ─────────────────────────────────── -->
  <!-- logTableName은 서비스에서 sanitize 후 ${} 사용 -->
  <select id="selectTableHistorySummary" parameterType="map" resultType="map">
    SELECT
      operation_type,
      COUNT(*)                    AS count,
      COUNT(DISTINCT original_id) AS affected_records,
      COUNT(DISTINCT changed_by)  AS unique_users,
      MIN(changed_at)             AS first_change,
      MAX(changed_at)             AS last_change

    FROM ${logTableName}

    GROUP BY operation_type
  </select>

  <!-- ─── 전체 테이블 이력 조회 ─────────────────────────────────── -->
  <select id="selectAllTableHistory" parameterType="map" resultType="map">
    SELECT <include refid="historyColumns"/>

    FROM ${logTableName}
    <where>
      <include refid="allHistoryFilter"/>
    </where>

    ORDER BY log_id DESC
    LIMIT #{limit} OFFSET #{offset}
  </select>

  <!-- 전체 테이블 이력 카운트 -->
  <select id="countAllTableHistory" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM ${logTableName}
    <where>
      <include refid="allHistoryFilter"/>
    </where>
  </select>

  <!-- ─── 특정 레코드 이력 조회 ─────────────────────────────────── -->
  <select id="selectRecordHistory" parameterType="map" resultType="map">
    SELECT <include refid="historyColumns"/>

    FROM ${logTableName}
    <include refid="recordHistoryFilter"/>

    ORDER BY log_id DESC
    LIMIT #{limit} OFFSET #{offset}
  </select>

  <!-- 특정 레코드 이력 카운트 -->
  <select id="countRecordHistory" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM ${logTableName}
    <include refid="recordHistoryFilter"/>
  </select>

  <!-- ─── 특정 레코드 타임라인 (동일 시간대 변경 그룹화) ─────────── -->
  <select id="selectRecordTimeline" parameterType="map" resultType="map">
    WITH grouped_changes AS (
      SELECT
        changed_at,
        changed_by,
        operation_type,
        ip_address,
        json_agg(
          json_build_object(
            'column',   changed_column,
            'oldValue', old_value,
            'newValue', new_value
          ) ORDER BY changed_column
        ) AS changes,
        full_row_before,
        full_row_after

      FROM ${logTableName}

      WHERE original_id = #{record_id}

      GROUP BY changed_at, changed_by, operation_type, ip_address, full_row_before, full_row_after

      ORDER BY changed_at DESC
      LIMIT 100
    )
    SELECT * FROM grouped_changes
  </select>

</mapper>
