<?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.LapsedReinstatementRecoveryMapper">

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

    <!-- 목록/단건 조회 컬럼 — 설계사명·증권번호 조인 포함 -->
    <sql id="lrrJoinCols">
        lr.recovery_id,
        lr.contract_id, ct.policy_no,
        lr.agent_id, a.agent_name, o.org_name,
        lr.lapsed_month,
        lr.reinstated_month,
        lr.withheld_amount,
        lr.repaid_amount,
        lr.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'LAPSE_RECOVERY_STATUS' AND cc.code = lr.status) AS status_name
    </sql>

    <sql id="lrrJoins">
        FROM lapsed_reinstatement_recovery lr
        LEFT JOIN agent a        ON a.agent_id       = lr.agent_id
        LEFT JOIN organization o ON o.org_id         = a.org_id
        LEFT JOIN contract ct    ON ct.contract_id   = lr.contract_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="lrrJoinCols"/>
        <include refid="lrrJoins"/>
        WHERE lr.recovery_id = #{recoveryId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="lrrJoinCols"/>
        <include refid="lrrJoins"/>
        <where>
            <if test="agentId != null">AND lr.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="reinstatedMonth != null and reinstatedMonth != ''">AND lr.reinstated_month = #{reinstatedMonth}</if>
            <if test="lapsedMonth != null and lapsedMonth != ''">AND lr.lapsed_month = #{lapsedMonth}</if>
            <if test="status != null and status != ''">AND lr.status = #{status}</if>
        </where>
        ORDER BY lr.reinstated_month DESC, lr.recovery_id DESC
    </select>

    <!-- repaid_amount 기본 0, status 기본 PENDING -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="recoveryId">
        INSERT INTO lapsed_reinstatement_recovery
            (contract_id, agent_id, lapsed_month, reinstated_month,
             withheld_amount, repaid_amount, status, created_by, created_at)
        VALUES
            (#{contractId}, #{agentId}, #{lapsedMonth}, #{reinstatedMonth},
             #{withheldAmount}, 0, 'PENDING', #{createdBy}, NOW())
    </insert>

    <!-- 상태 + 재지급액 갱신 — REPAID 시 repaid_amount=withheld_amount(Service에서 전달) -->
    <update id="updateStatus">
        UPDATE lapsed_reinstatement_recovery
           SET status        = #{status},
               repaid_amount = #{repaidAmount},
               updated_at    = NOW()
         WHERE recovery_id = #{recoveryId}
    </update>

    <!-- 정산월 기준 설계사별 재지급 합계 — status='REPAID' AND reinstated_month=settleMonth -->
    <select id="aggregateByAgent" resultType="map">
        SELECT agent_id            AS "agentId",
               SUM(repaid_amount)  AS "total"
          FROM lapsed_reinstatement_recovery
         WHERE reinstated_month = #{settleMonth}
           AND status           = 'REPAID'
         GROUP BY agent_id
    </select>

</mapper>
