<?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="com.erp.ai.mapper.AiAnalysisLogMapper">

  <resultMap id="AnalysisLogMap" type="com.erp.ai.model.AiAnalysisLog">
    <id property="id" column="id"/>
    <result property="group_id" column="group_id"/>
    <result property="agent_id" column="agent_id"/>
    <result property="schedule_id" column="schedule_id"/>
    <result property="execution_type" column="execution_type"/>
    <result property="input_message" column="input_message"/>
    <result property="analysis_result" column="analysis_result"/>
    <result property="prediction" column="prediction"/>
    <result property="actual_result" column="actual_result"/>
    <result property="accuracy_score" column="accuracy_score"/>
    <result property="tokens_used" column="tokens_used"/>
    <result property="duration_ms" column="duration_ms"/>
    <result property="metadata" column="metadata"/>
    <result property="company_code" column="company_code"/>
    <result property="created_at" column="created_at"/>
  </resultMap>

  <insert id="insert" parameterType="com.erp.ai.model.AiAnalysisLog">
    INSERT INTO ai_analysis_logs
      (group_id, agent_id, schedule_id, execution_type, input_message, analysis_result,
       prediction, actual_result, accuracy_score, tokens_used, duration_ms, metadata, company_code)
    VALUES (#{group_id}, #{agent_id}, #{schedule_id}, #{execution_type},
            #{input_message}, #{analysis_result},
            <choose>
              <when test="prediction != null">#{prediction}::jsonb</when>
              <otherwise>NULL</otherwise>
            </choose>,
            <choose>
              <when test="actual_result != null">#{actual_result}::jsonb</when>
              <otherwise>NULL</otherwise>
            </choose>,
            #{accuracy_score},
            COALESCE(#{tokens_used}, 0),
            #{duration_ms},
            COALESCE(#{metadata}::jsonb, '{}'::jsonb),
            #{company_code})
  </insert>

  <select id="getRecentLogs" resultMap="AnalysisLogMap">
    SELECT id, group_id, agent_id, schedule_id, execution_type,
           input_message, analysis_result,
           prediction::text AS prediction, actual_result::text AS actual_result,
           accuracy_score, tokens_used, duration_ms, metadata::text AS metadata,
           company_code, created_at
    FROM ai_analysis_logs
    WHERE group_id = #{groupId}
      AND created_at &gt;= NOW() - (#{days} || ' days')::interval
    ORDER BY created_at DESC
    LIMIT #{limit}
  </select>

  <select id="getAverageAccuracy" resultType="java.math.BigDecimal">
    SELECT COALESCE(AVG(accuracy_score), 0)
    FROM ai_analysis_logs
    WHERE group_id = #{groupId} AND accuracy_score IS NOT NULL
  </select>

</mapper>
