<?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.commission.AgentGradeEvaluationMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.commission.AgentGradeEvaluationVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.commission.AgentGradeEvaluationResp"/>

    <!--
        공통 SELECT 컬럼.
        prev_grade / evaluated_grade 두 등급명을 각각 LEFT JOIN.
        status 코드명은 common_code 서브쿼리.
    -->
    <sql id="evalCols">
        e.eval_id,
        e.agent_id,
        a.agent_name,
        o.org_name,
        e.eval_period,
        e.performance_score,
        e.prev_grade_id,
        pg.grade_name  AS prev_grade_name,
        e.evaluated_grade_id,
        eg.grade_name  AS evaluated_grade_name,
        e.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'GRADE_EVAL_STATUS' AND cc.code = e.status) AS status_name,
        e.created_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="evalCols"/>
        FROM agent_grade_evaluation e
        LEFT JOIN agent       a  ON a.agent_id  = e.agent_id
        LEFT JOIN organization o ON o.org_id    = a.org_id
        LEFT JOIN grade       pg ON pg.grade_id = e.prev_grade_id
        LEFT JOIN grade       eg ON eg.grade_id = e.evaluated_grade_id
        WHERE e.eval_id = #{evalId}
    </select>

    <!-- 목록: agentId, agentName(LIKE), evalPeriod, status 필터 -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="evalCols"/>
        FROM agent_grade_evaluation e
        LEFT JOIN agent       a  ON a.agent_id  = e.agent_id
        LEFT JOIN organization o ON o.org_id    = a.org_id
        LEFT JOIN grade       pg ON pg.grade_id = e.prev_grade_id
        LEFT JOIN grade       eg ON eg.grade_id = e.evaluated_grade_id
        <where>
            <if test="agentId != null">AND e.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="evalPeriod != null and evalPeriod != ''">AND e.eval_period = #{evalPeriod}</if>
            <if test="status != null and status != ''">AND e.status = #{status}</if>
        </where>
        ORDER BY e.eval_period DESC, e.agent_id
    </select>

    <!--
        INSERT: evaluated_grade_id 는 Service(등급산정) 후 채워 전달.
        status 기본값 'EVALUATED'.
    -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="evalId">
        INSERT INTO agent_grade_evaluation
            (agent_id, eval_period, performance_score, prev_grade_id, evaluated_grade_id, status)
        VALUES
            (#{agentId}, #{evalPeriod}, #{performanceScore},
             #{prevGradeId}, #{evaluatedGradeId},
             COALESCE(#{status}, 'EVALUATED'))
    </insert>

    <!-- 상태 변경: EVALUATED → APPLIED -->
    <update id="updateStatus">
        UPDATE agent_grade_evaluation
           SET status = #{status}
         WHERE eval_id = #{evalId}
    </update>

    <delete id="delete">
        DELETE FROM agent_grade_evaluation WHERE eval_id = #{evalId}
    </delete>

</mapper>
