<?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.ga.core.mapper.training.AgentTrainingMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.training.AgentTrainingVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.training.AgentTrainingResp"/>

    <sql id="joinCols">
        at2.training_id, at2.agent_id, a.agent_name, o.org_name,
        at2.course_name, at2.course_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'COURSE_TYPE' AND cc.code = at2.course_type) AS course_type_name,
        at2.training_date, at2.hours, at2.certificate_no, at2.instructor
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_training at2
        LEFT JOIN agent a        ON a.agent_id = at2.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE at2.training_id = #{trainingId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_training at2
        LEFT JOIN agent a        ON a.agent_id = at2.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        <where>
            <if test="agentId != null">AND at2.agent_id = #{agentId}</if>
            <if test="courseType != null and courseType != ''">AND at2.course_type = #{courseType}</if>
            <if test="year != null">AND EXTRACT(YEAR FROM at2.training_date) = #{year}</if>
        </where>
        ORDER BY at2.training_date DESC, at2.training_id DESC
    </select>

    <!-- 연간 이수 시간 합계 — 의무 12시간 충족 검사용 -->
    <select id="selectAnnualHoursByAgent" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(hours), 0)
          FROM agent_training
         WHERE agent_id = #{agentId}
           AND EXTRACT(YEAR FROM training_date) = #{year}
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="trainingId">
        INSERT INTO agent_training
            (agent_id, course_name, course_type, training_date, hours, certificate_no, instructor, created_by)
        VALUES
            (#{agentId}, #{courseName}, #{courseType}, #{trainingDate}, #{hours},
             #{certificateNo}, #{instructor}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE agent_training
           SET course_name    = #{courseName},
               course_type    = #{courseType},
               training_date  = #{trainingDate},
               hours          = #{hours},
               certificate_no = #{certificateNo},
               instructor     = #{instructor},
               updated_at     = NOW()
         WHERE training_id = #{trainingId}
    </update>

    <delete id="delete">
        DELETE FROM agent_training WHERE training_id = #{trainingId}
    </delete>

</mapper>
