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

    <!-- VO: INSERT/UPDATE/배치 용도 -->
    <resultMap id="VOMap" type="com.ga.core.vo.commission.ClawbackReceivableVO"/>

    <!-- Resp: 목록 조회 용도 (조인 포함, recoveries 없음) -->
    <resultMap id="RespMap" type="com.ga.core.vo.commission.ClawbackReceivableResp"/>

    <!-- 상세 Resp: recoveries collection 포함 -->
    <resultMap id="RespDetailMap" type="com.ga.core.vo.commission.ClawbackReceivableResp">
        <id     property="receivableId"      column="receivable_id"/>
        <result property="agentId"           column="agent_id"/>
        <result property="agentName"         column="agent_name"/>
        <result property="originSettleMonth" column="origin_settle_month"/>
        <result property="originalAmount"    column="original_amount"/>
        <result property="recoveredAmount"   column="recovered_amount"/>
        <result property="balance"           column="balance"/>
        <result property="accruedInterest"   column="accrued_interest"/>
        <result property="status"            column="status"/>
        <result property="statusName"        column="status_name"/>
        <result property="lastRecoveryMonth" column="last_recovery_month"/>
        <result property="writeoffReason"    column="writeoff_reason"/>
        <result property="writtenOffAt"      column="written_off_at"/>
        <result property="writtenOffBy"      column="written_off_by"/>
        <result property="createdAt"         column="created_at"/>
        <result property="updatedAt"         column="updated_at"/>
        <collection property="recoveries"
                    ofType="com.ga.core.vo.commission.ClawbackRecoveryResp"
                    select="com.ga.core.mapper.commission.ClawbackRecoveryMapper.selectByReceivableId"
                    column="receivable_id"/>
    </resultMap>

    <!-- 목록/단건 조회 컬럼 — 설계사명 조인 + 공통코드명 포함 -->
    <sql id="rcvJoinCols">
        r.receivable_id, r.agent_id, a.agent_name,
        r.origin_settle_month,
        r.original_amount, r.recovered_amount, r.balance, r.accrued_interest,
        r.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'CLAWBACK_RCV_STATUS' AND cc.code = r.status) AS status_name,
        r.last_recovery_month,
        r.writeoff_reason, r.written_off_at, r.written_off_by,
        r.created_at, r.updated_at
    </sql>

    <sql id="rcvJoins">
        FROM clawback_receivable r
        LEFT JOIN agent a ON a.agent_id = r.agent_id
    </sql>

    <!-- 단건 조회 — 회수이력은 RespDetailMap collection 으로 자동 populate -->
    <select id="selectById" resultMap="RespDetailMap">
        SELECT <include refid="rcvJoinCols"/>
        <include refid="rcvJoins"/>
        WHERE r.receivable_id = #{receivableId}
    </select>

    <!-- 목록 조회 — PageHelper startPage() 호환 -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="rcvJoinCols"/>
        <include refid="rcvJoins"/>
        <where>
            <if test="agentId != null">AND r.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="status != null and status != ''">AND r.status = #{status}</if>
            <if test="originSettleMonth != null and originSettleMonth != ''">AND r.origin_settle_month = #{originSettleMonth}</if>
        </where>
        ORDER BY r.origin_settle_month DESC, r.agent_id, r.receivable_id DESC
    </select>

    <!-- 설계사 + 발생월 단건 (배치 확인·reverse 용도) -->
    <select id="selectByAgentMonth" resultMap="VOMap">
        SELECT receivable_id, agent_id, origin_settle_month,
               original_amount, recovered_amount, balance,
               status, last_recovery_month,
               writeoff_reason, written_off_at, written_off_by,
               created_at, created_by, updated_at
          FROM clawback_receivable
         WHERE agent_id           = #{agentId}
           AND origin_settle_month = #{originSettleMonth}
    </select>

    <!--
        배치 priorBalance: 당월(settleMonth) 이전에 발생한 미결 잔액 합계.
        origin_settle_month < settleMonth 로 당월 발생분 자기상계 방지.
        COALESCE 로 null → 0 변환.
    -->
    <select id="outstandingBalance" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(balance), 0)
          FROM clawback_receivable
         WHERE agent_id            = #{agentId}
           AND status             IN ('OUTSTANDING', 'RECOVERING')
           AND origin_settle_month  &lt; #{settleMonth}
    </select>

    <!--
        FIFO 회수 대상 목록: 오래된 발생월 우선(ASC), 동월 내 receivable_id ASC.
        배치 applyCarryoverAndRecovery 단계 3에서 순회하며 상계.
    -->
    <select id="selectOutstandingByAgentFifo" resultMap="VOMap">
        SELECT receivable_id, agent_id, origin_settle_month,
               original_amount, recovered_amount, balance,
               status, last_recovery_month,
               writeoff_reason, written_off_at, written_off_by,
               created_at, created_by, updated_at
          FROM clawback_receivable
         WHERE agent_id            = #{agentId}
           AND status             IN ('OUTSTANDING', 'RECOVERING')
           AND origin_settle_month  &lt; #{settleMonth}
         ORDER BY origin_settle_month ASC, receivable_id ASC
    </select>

    <!--
        배치 멱등 upsert.
        UNIQUE(agent_id, origin_settle_month) 충돌 시 발생액·잔액을 덮어쓰고
        회수액=0, 상태=OUTSTANDING 으로 초기화.
        재실행 시 동일 채권이 중복 생성되지 않음.
    -->
    <insert id="upsertCarryover">
        INSERT INTO clawback_receivable
            (agent_id, origin_settle_month, original_amount, recovered_amount, balance,
             status, created_by, created_at)
        VALUES
            (#{agentId}, #{originSettleMonth}, #{originalAmount}, 0, #{balance},
             'OUTSTANDING', #{createdBy}, NOW())
        ON CONFLICT (agent_id, origin_settle_month) DO UPDATE
            SET original_amount  = EXCLUDED.original_amount,
                balance          = EXCLUDED.balance,
                recovered_amount = 0,
                status           = 'OUTSTANDING',
                updated_at       = NOW()
    </insert>

    <!--
        배치 회수 반영: recovered_amount, balance, status, last_recovery_month 갱신.
        status 는 배치가 계산 후 전달 (RECOVERING 또는 CLEARED).
    -->
    <update id="updateRecovery">
        UPDATE clawback_receivable
           SET recovered_amount    = #{recoveredAmount},
               balance             = #{balance},
               status              = #{status},
               last_recovery_month = #{lastRecoveryMonth},
               updated_at          = NOW()
         WHERE receivable_id = #{receivableId}
    </update>

    <!--
        대손/직접상환 write-off: balance→0, 상태 전이, 사유·일시·처리자 기록.
        OUTSTANDING/RECOVERING 인 경우만 Service 레이어에서 검증 후 호출.
    -->
    <update id="writeOff">
        UPDATE clawback_receivable
           SET balance         = 0,
               status          = #{status},
               writeoff_reason = #{reason},
               written_off_at  = NOW(),
               written_off_by  = #{userId},
               updated_at      = NOW()
         WHERE receivable_id = #{id}
    </update>

    <!-- 설계사별 미결 잔액 집계 (summary API) — 미결 상태만 -->
    <select id="summaryByAgent" resultType="map">
        SELECT r.agent_id         AS "agentId",
               a.agent_name       AS "agentName",
               COUNT(*)           AS "count",
               SUM(r.original_amount)  AS "originalTotal",
               SUM(r.recovered_amount) AS "recoveredTotal",
               SUM(r.balance)          AS "balanceTotal"
          FROM clawback_receivable r
          LEFT JOIN agent a ON a.agent_id = r.agent_id
         WHERE r.status IN ('OUTSTANDING', 'RECOVERING')
        <if test="agentId != null">AND r.agent_id = #{agentId}</if>
        <if test="originSettleMonth != null and originSettleMonth != ''">
            AND r.origin_settle_month = #{originSettleMonth}
        </if>
         GROUP BY r.agent_id, a.agent_name
         ORDER BY SUM(r.balance) DESC
    </select>

    <!-- 페이징 count 쿼리 -->
    <select id="countByCondition" resultType="int">
        SELECT COUNT(*)
          FROM clawback_receivable r
          LEFT JOIN agent a ON a.agent_id = r.agent_id
        <where>
            <if test="agentId != null">AND r.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="status != null and status != ''">AND r.status = #{status}</if>
            <if test="originSettleMonth != null and originSettleMonth != ''">AND r.origin_settle_month = #{originSettleMonth}</if>
        </where>
    </select>


    <!-- [MOD-6] 시효 만료 대상 채권 조회 — beforeMonth = baseMonth - expiration_months -->
    <select id="selectExpired" resultMap="VOMap">
        SELECT receivable_id, agent_id, origin_settle_month,
               original_amount, recovered_amount, balance,
               status, last_recovery_month,
               writeoff_reason, written_off_at, written_off_by,
               created_at, created_by, updated_at
          FROM clawback_receivable
         WHERE origin_settle_month &lt;= #{beforeMonth}
           AND status IN ('OUTSTANDING', 'RECOVERING')
         ORDER BY origin_settle_month ASC, receivable_id ASC
    </select>

    <!-- [V125] 연체이자 적립 대상 채권 — beforeMonth = settleMonth - (grace+1) -->
    <select id="selectAccruable" resultMap="VOMap">
        SELECT receivable_id, agent_id, origin_settle_month,
               original_amount, recovered_amount, balance, accrued_interest,
               status, last_recovery_month,
               writeoff_reason, written_off_at, written_off_by,
               created_at, created_by, updated_at
          FROM clawback_receivable
         WHERE status IN ('OUTSTANDING', 'RECOVERING')
           AND balance &gt; 0
           AND origin_settle_month &lt;= #{beforeMonth}
         ORDER BY origin_settle_month ASC, receivable_id ASC
    </select>

    <!--
        [V125] 연체이자 가감 — balance, accrued_interest 동시 갱신.
        GREATEST(...,0) 로 0 하한 클램프: 타월 대손(write-off로 balance=0 강제) 후 직전월 적립이
        역산되는 예외 재실행에서도 CHECK(balance>=0) 위반을 방지. 정상 적립/역산은 클램프 무영향.
    -->
    <update id="applyInterest">
        UPDATE clawback_receivable
           SET balance          = GREATEST(balance + #{delta}, 0),
               accrued_interest = GREATEST(accrued_interest + #{delta}, 0),
               updated_at       = NOW()
         WHERE receivable_id = #{receivableId}
    </update>

    <!-- [MOD-6] 만료 대상 채권 일괄 대손 처리 — WRITTEN_OFF 상태 전이 -->
    <update id="writeOffExpired">
        UPDATE clawback_receivable
           SET balance         = 0,
               status          = 'WRITTEN_OFF',
               writeoff_reason = #{reason},
               written_off_at  = NOW(),
               updated_at      = NOW()
         WHERE origin_settle_month &lt;= #{beforeMonth}
           AND status IN ('OUTSTANDING', 'RECOVERING')
    </update>

</mapper>
