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

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

    <!-- 목록/단건 조회 컬럼 — 설계사명·조직명·공통코드명 조인 포함 -->
    <sql id="advJoinCols">
        ap.advance_id,
        ap.agent_id, a.agent_name, o.org_name,
        ap.advance_month,
        ap.principal_amount,
        ap.recovered_amount,
        ap.balance,
        ap.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'ADVANCE_STATUS' AND cc.code = ap.status) AS status_name,
        ap.memo,
        ap.created_at
    </sql>

    <sql id="advJoins">
        FROM advance_payment ap
        LEFT JOIN agent a        ON a.agent_id = ap.agent_id
        LEFT JOIN organization o ON o.org_id   = a.org_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="advJoinCols"/>
        <include refid="advJoins"/>
        WHERE ap.advance_id = #{advanceId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="advJoinCols"/>
        <include refid="advJoins"/>
        <where>
            <if test="agentId != null">AND ap.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="advanceMonth != null and advanceMonth != ''">AND ap.advance_month = #{advanceMonth}</if>
            <if test="status != null and status != ''">AND ap.status = #{status}</if>
        </where>
        ORDER BY ap.advance_month DESC, ap.advance_id DESC
    </select>

    <!-- balance = principal_amount (초기 등록 시 잔액=원금) -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="advanceId">
        INSERT INTO advance_payment
            (agent_id, advance_month, principal_amount, recovered_amount, balance,
             status, memo, created_by, created_at)
        VALUES
            (#{agentId}, #{advanceMonth}, #{principalAmount}, 0, #{principalAmount},
             'OUTSTANDING', #{memo}, #{createdBy}, NOW())
    </insert>

    <!-- 회수 후 갱신 — recovered_amount, balance, status, updated_at -->
    <update id="update">
        UPDATE advance_payment
           SET recovered_amount = #{recoveredAmount},
               balance          = #{balance},
               status           = #{status},
               memo             = #{memo},
               updated_at       = NOW()
         WHERE advance_id = #{advanceId}
    </update>

    <delete id="delete">
        DELETE FROM advance_payment WHERE advance_id = #{advanceId}
    </delete>

    <!-- 설계사의 미상환 잔액 합계 — OUTSTANDING/PARTIAL 대상 -->
    <select id="outstandingByAgent" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(balance), 0)
          FROM advance_payment
         WHERE agent_id = #{agentId}
           AND status IN ('OUTSTANDING', 'PARTIAL')
    </select>

    <!-- FIFO 상계 대상 목록 — 오래된 발생월 우선 -->
    <select id="selectOutstandingFifo" resultMap="VOMap">
        SELECT advance_id, agent_id, advance_month,
               principal_amount, recovered_amount, balance,
               status, memo, created_at, created_by, updated_at
          FROM advance_payment
         WHERE agent_id = #{agentId}
           AND status IN ('OUTSTANDING', 'PARTIAL')
         ORDER BY advance_month ASC, advance_id ASC
    </select>

</mapper>
