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

  <!-- 배치 대시보드 통계: 전체/활성 배치 수, 오늘·어제 실행/실패 수 -->
  <select id="getBatchManagementStats" parameterType="map" resultType="map">
    WITH batch_stats AS (
      SELECT COUNT(*) AS total_count,
             SUM(CASE WHEN is_active = 'Y' THEN 1 ELSE 0 END) AS active_count
      FROM batch_configs
      WHERE 1=1
      <include refid="common.companyCodeFilter"/>
    ),
    execution_today AS (
      SELECT COUNT(*) AS today_count,
             SUM(CASE WHEN execution_status = 'FAILED' THEN 1 ELSE 0 END) AS today_failed
      FROM batch_execution_logs
      WHERE DATE(start_time) = CURRENT_DATE
      <include refid="common.companyCodeFilter"/>
    ),
    execution_yesterday AS (
      SELECT COUNT(*) AS yesterday_count,
             SUM(CASE WHEN execution_status = 'FAILED' THEN 1 ELSE 0 END) AS yesterday_failed
      FROM batch_execution_logs
      WHERE DATE(start_time) = CURRENT_DATE - INTERVAL '1 day'
      <include refid="common.companyCodeFilter"/>
    )
    SELECT b.total_count,
           b.active_count,
           COALESCE(t.today_count,  0) AS today_count,
           COALESCE(t.today_failed, 0) AS today_failed_count,
           COALESCE(y.yesterday_count,  0) AS yesterday_count,
           COALESCE(y.yesterday_failed, 0) AS yesterday_failed_count

    FROM batch_stats b, execution_today t, execution_yesterday y
  </select>

  <!-- 노드 플로우 목록 (배치 설정에서 선택용) -->
  <select id="getBatchManagementNodeFlowList" parameterType="map" resultType="map">
    SELECT flow_id,
           flow_name,
           flow_description AS description,
           company_code,
           COALESCE(
             jsonb_array_length(
               CASE WHEN flow_data IS NOT NULL AND flow_data::text != ''
                    THEN (flow_data::jsonb -> 'nodes')
                    ELSE '[]'::jsonb END
             ), 0
           ) AS node_count

    FROM node_flows

    WHERE 1=1
    <include refid="common.companyCodeFilter"/>

    ORDER BY flow_name
  </select>

  <!-- 인증 토큰 서비스명 목록 -->
  <select id="getBatchManagementAuthServiceList" parameterType="map" resultType="string">
    SELECT DISTINCT service_name

    FROM auth_tokens

    WHERE service_name IS NOT NULL
    <include refid="common.companyCodeFilter"/>

    ORDER BY service_name
  </select>

  <!-- 스파크라인: 최근 24시간 1시간 단위 실행 집계 -->
  <select id="getBatchManagementSparklineData" parameterType="map" resultType="map">
    SELECT DATE_TRUNC('hour', start_time) AS hour_slot,
           COUNT(*) AS total_count,
           SUM(CASE WHEN execution_status = 'SUCCESS' THEN 1 ELSE 0 END) AS success_count,
           SUM(CASE WHEN execution_status = 'FAILED'  THEN 1 ELSE 0 END) AS failed_count

    FROM batch_execution_logs

    WHERE batch_config_id = #{batch_config_id}::varchar
      AND start_time >= NOW() - INTERVAL '24 hours'

    GROUP BY DATE_TRUNC('hour', start_time)

    ORDER BY hour_slot
  </select>

  <!-- 글로벌 스파크라인: 회사 전체 배치의 최근 24시간 1시간 단위 실행 집계 (빈 슬롯 포함 24개 고정) -->
  <select id="getBatchManagementGlobalSparklineData" parameterType="map" resultType="map">
    WITH hours AS (
      SELECT generate_series(
        DATE_TRUNC('hour', NOW() - INTERVAL '23 hours'),
        DATE_TRUNC('hour', NOW()),
        INTERVAL '1 hour'
      ) AS hour_slot
    ),
    filtered_logs AS (
      SELECT DATE_TRUNC('hour', start_time) AS hour_slot,
             execution_status
      FROM batch_execution_logs
      WHERE start_time >= NOW() - INTERVAL '24 hours'
      <include refid="common.companyCodeFilter"/>
    )
    SELECT h.hour_slot,
           COUNT(l.execution_status) AS total_count,
           COALESCE(SUM(CASE WHEN l.execution_status = 'SUCCESS' THEN 1 ELSE 0 END), 0) AS success_count,
           COALESCE(SUM(CASE WHEN l.execution_status = 'FAILED'  THEN 1 ELSE 0 END), 0) AS failed_count
    FROM hours h
    LEFT JOIN filtered_logs l ON l.hour_slot = h.hour_slot
    GROUP BY h.hour_slot
    ORDER BY h.hour_slot
  </select>

  <!-- 최근 실행 로그 목록 (최대 20건) -->
  <select id="getBatchManagementRecentLogList" parameterType="map" resultType="map">
    SELECT id,
           batch_config_id,
           execution_status,
           start_time,
           end_time,
           duration_ms,
           total_records,
           success_records,
           failed_records,
           error_message

    FROM batch_execution_logs

    WHERE batch_config_id = #{batch_config_id}::varchar

    ORDER BY start_time DESC
    LIMIT 20
  </select>

</mapper>
