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

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

    <!-- 목록/단건 조회 컬럼 — 보험사명·계약번호 조인 포함 -->
    <sql id="ledgerJoinCols">
        l.income_id, l.contract_id, ct.policy_no,
        l.carrier_id, ic.company_name AS carrier_name,
        l.settle_month,
        l.premium_amount, l.company_rate, l.income_amount,
        l.commission_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'COMMISSION_TYPE' AND cc.code = l.commission_type) AS commission_type_name,
        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 income_commission_ledger l
        LEFT JOIN insurance_company ic ON ic.company_id = l.carrier_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.income_id = #{incomeId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="ledgerJoinCols"/>
        <include refid="ledgerJoins"/>
        <where>
            <if test="carrierId != null">AND l.carrier_id = #{carrierId}</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="commissionType != null and commissionType != ''">AND l.commission_type = #{commissionType}</if>
            <if test="status != null and status != ''">AND l.status = #{status}</if>
        </where>
        ORDER BY l.settle_month DESC, l.carrier_id, l.income_id DESC
    </select>

    <!-- 보험사 + 정산월 수입수수료 합계 — CANCELLED 제외 -->
    <select id="sumByCarrierAndMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(income_amount), 0)
          FROM income_commission_ledger
         WHERE carrier_id   = #{carrierId}
           AND settle_month = #{settleMonth}
           AND status != 'CANCELLED'
    </select>

    <!-- income_amount 는 Service가 계산 후 VO에 set, status 기본 CALCULATED -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="incomeId">
        INSERT INTO income_commission_ledger
            (contract_id, carrier_id, settle_month, premium_amount, company_rate,
             income_amount, commission_type, status)
        VALUES
            (#{contractId}, #{carrierId}, #{settleMonth}, #{premiumAmount}, #{companyRate},
             #{incomeAmount}, #{commissionType},
             COALESCE(#{status}, 'CALCULATED'))
    </insert>

    <update id="updateStatus">
        UPDATE income_commission_ledger
           SET status = #{status}, updated_at = NOW()
         WHERE income_id = #{incomeId}
    </update>

    <delete id="delete">
        DELETE FROM income_commission_ledger WHERE income_id = #{incomeId}
    </delete>

</mapper>
