<?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.endorse.ContractEndorsementMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.endorse.ContractEndorsementVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.endorse.ContractEndorsementResp"/>

    <sql id="joinCols">
        ce.endorsement_id, ce.contract_id, ct.policy_no,
        ce.agent_id, a.agent_name,
        ce.endorsement_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'ENDORSEMENT_TYPE' AND cc.code = ce.endorsement_type) AS endorsement_type_name,
        ce.changed_at, ce.old_value, ce.new_value, ce.reason,
        ce.requires_commission_recalc, ce.processed, ce.processed_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM contract_endorsement ce
        LEFT JOIN contract ct ON ct.contract_id = ce.contract_id
        LEFT JOIN agent a     ON a.agent_id = ce.agent_id
        WHERE ce.endorsement_id = #{endorsementId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM contract_endorsement ce
        LEFT JOIN contract ct ON ct.contract_id = ce.contract_id
        LEFT JOIN agent a     ON a.agent_id = ce.agent_id
        <where>
            <if test="contractId != null">AND ce.contract_id = #{contractId}</if>
            <if test="agentId != null">AND ce.agent_id = #{agentId}</if>
            <if test="endorsementType != null and endorsementType != ''">AND ce.endorsement_type = #{endorsementType}</if>
            <if test="requiresCommissionRecalc != null">AND ce.requires_commission_recalc = #{requiresCommissionRecalc}</if>
            <if test="processed != null">AND ce.processed = #{processed}</if>
        </where>
        ORDER BY ce.changed_at DESC, ce.endorsement_id DESC
    </select>

    <!-- 재계산 미처리 건 (부분 인덱스 idx_ce_recalc 활용) -->
    <select id="selectPendingRecalc" resultMap="VOMap">
        SELECT ce.endorsement_id, ce.contract_id, ce.agent_id, ce.endorsement_type,
               ce.changed_at, ce.old_value, ce.new_value, ce.reason,
               ce.requires_commission_recalc, ce.processed, ce.processed_at, ce.created_at
        FROM contract_endorsement ce
        WHERE ce.requires_commission_recalc = TRUE
          AND ce.processed = FALSE
        ORDER BY ce.changed_at ASC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="endorsementId">
        INSERT INTO contract_endorsement
            (contract_id, agent_id, endorsement_type, changed_at, old_value, new_value,
             reason, requires_commission_recalc, processed, created_by)
        VALUES
            (#{contractId}, #{agentId}, #{endorsementType},
             COALESCE(#{changedAt}, NOW()), #{oldValue}, #{newValue},
             #{reason}, #{requiresCommissionRecalc}, FALSE, #{createdBy})
    </insert>

    <update id="markProcessed">
        UPDATE contract_endorsement
           SET processed    = TRUE,
               processed_at = NOW(),
               updated_at   = NOW()
         WHERE endorsement_id = #{endorsementId}
    </update>

    <delete id="delete">
        DELETE FROM contract_endorsement WHERE endorsement_id = #{endorsementId}
    </delete>

</mapper>
