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

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

    <!-- 목록/단건 조회 컬럼 — 설계사명·조직명·계약번호 조인 포함 -->
    <sql id="ledgerJoinCols">
        l.ledger_id,
        l.agent_id, a.agent_name, o.org_name,
        l.contract_id, ct.policy_no,
        l.settle_month,
        l.premium_amount, l.conversion_rate, l.converted_premium,
        l.recognition_rate, l.recognized_amount,
        l.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'INCOME_STATUS' AND cc.code = l.status) AS status_name
    </sql>

    <sql id="ledgerJoins">
        FROM recognized_performance_ledger l
        LEFT JOIN agent a        ON a.agent_id   = l.agent_id
        LEFT JOIN organization o ON o.org_id     = a.org_id
        LEFT JOIN contract ct    ON ct.contract_id = l.contract_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        WHERE l.ledger_id = #{ledgerId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        <where>
            <if test="agentId != null">AND l.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="contractId != null">AND l.contract_id = #{contractId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND l.settle_month = #{settleMonth}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.agent_id, l.ledger_id DESC
    </select>

    <!-- 설계사 + 정산월 인정실적 합계 — 시상 기준 실적 산출 용도, CANCELLED 제외 -->
    <select id="sumRecognizedByAgentAndMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(recognized_amount), 0)
          FROM recognized_performance_ledger
         WHERE agent_id    = #{agentId}
           AND settle_month = #{settleMonth}
           AND status      != 'CANCELLED'
    </select>

    <!-- converted_premium / recognized_amount 는 Service가 계산 후 VO에 set, status 기본 CALCULATED -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="ledgerId">
        INSERT INTO recognized_performance_ledger
            (agent_id, contract_id, settle_month, premium_amount, conversion_rate,
             converted_premium, recognition_rate, recognized_amount, status)
        VALUES
            (#{agentId}, #{contractId}, #{settleMonth}, #{premiumAmount}, #{conversionRate},
             #{convertedPremium}, #{recognitionRate}, #{recognizedAmount},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

    <update id="updateStatus">
        UPDATE recognized_performance_ledger
           SET status = #{status}, updated_at = NOW()
         WHERE ledger_id = #{ledgerId}
    </update>

    <delete id="delete">
        DELETE FROM recognized_performance_ledger WHERE ledger_id = #{ledgerId}
    </delete>

</mapper>
