<?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.settle.TerminationSettlementMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.settle.TerminationSettlementVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.settle.TerminationSettlementResp"/>

    <!-- 조회용 컬럼 목록 -->
    <sql id="cols">
        ts.term_settle_id,
        ts.agent_id,
        a.agent_name,
        o.org_name,
        ts.termination_date,
        ts.org_history_id,
        ts.calc_month,
        ts.unpaid_recruit_amount,
        ts.unpaid_installment_amount,
        ts.unpaid_incentive_amount,
        -- 미지급 합계 (계산값)
        ts.unpaid_recruit_amount + ts.unpaid_installment_amount + ts.unpaid_incentive_amount
                                                                          AS unpaid_total_amount,
        ts.chargeback_expected_amount,
        ts.chargeback_waived_amount,
        ts.deduction_amount,
        ts.final_pay_amount,
        ts.status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'TERM_SETTLE_STATUS' AND cc.code = ts.status) AS status_name,
        ts.note,
        ts.confirmed_by,
        u.user_name                                                        AS confirmed_by_name,
        ts.confirmed_at,
        ts.paid_at,
        ts.payment_id,
        ts.created_at,
        ts.created_by,
        ts.updated_at,
        ts.updated_by
    </sql>

    <!-- 조인 절 -->
    <sql id="joins">
        FROM termination_settlement ts
        LEFT JOIN agent       a ON a.agent_id = ts.agent_id
        LEFT JOIN organization o ON o.org_id  = a.org_id
        LEFT JOIN users       u ON u.user_id  = ts.confirmed_by
    </sql>

    <!-- 해촉 정산 목록 조회 (페이징) -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="cols"/>
        <include refid="joins"/>
        <where>
            <if test="agentId != null">AND ts.agent_id = #{agentId}</if>
            <if test="orgId != null">AND a.org_id = #{orgId}</if>
            <if test="calcMonth != null and calcMonth != ''">
                AND ts.calc_month = #{calcMonth}
            </if>
            <if test="status != null and status != ''">AND ts.status = #{status}</if>
        </where>
        ORDER BY ts.termination_date DESC, ts.term_settle_id DESC
    </select>

    <!-- 해촉 정산 단건 조회 (응답) -->
    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="cols"/>
        <include refid="joins"/>
        WHERE ts.term_settle_id = #{termSettleId}
    </select>

    <!-- 해촉 정산 단건 조회 (VO) -->
    <select id="selectVoById" resultMap="VOMap">
        SELECT term_settle_id, agent_id, termination_date, org_history_id, calc_month,
               unpaid_recruit_amount, unpaid_installment_amount, unpaid_incentive_amount,
               chargeback_expected_amount, chargeback_waived_amount,
               deduction_amount, final_pay_amount,
               status, note, confirmed_by, confirmed_at,
               paid_at, payment_id,
               created_at, created_by, updated_at, updated_by
        FROM termination_settlement WHERE term_settle_id = #{termSettleId}
    </select>

    <!-- 설계사별 해촉 정산 단건 조회 -->
    <select id="selectByAgent" resultMap="VOMap">
        SELECT term_settle_id, agent_id, termination_date, org_history_id, calc_month,
               unpaid_recruit_amount, unpaid_installment_amount, unpaid_incentive_amount,
               chargeback_expected_amount, chargeback_waived_amount,
               deduction_amount, final_pay_amount,
               status, note, confirmed_by, confirmed_at,
               paid_at, payment_id,
               created_at, created_by, updated_at, updated_by
        FROM termination_settlement WHERE agent_id = #{agentId}
        ORDER BY termination_date DESC LIMIT 1
    </select>

    <!-- 해촉 정산 등록 -->
    <insert id="insert" parameterType="com.ga.core.vo.settle.TerminationSettlementVO"
            useGeneratedKeys="true" keyProperty="termSettleId">
        INSERT INTO termination_settlement (
            agent_id, termination_date, org_history_id, calc_month,
            unpaid_recruit_amount, unpaid_installment_amount, unpaid_incentive_amount,
            chargeback_expected_amount, chargeback_waived_amount,
            deduction_amount, final_pay_amount,
            status, note, created_at, created_by
        ) VALUES (
            #{agentId}, #{terminationDate}, #{orgHistoryId}, #{calcMonth},
            COALESCE(#{unpaidRecruitAmount}, 0),
            COALESCE(#{unpaidInstallmentAmount}, 0),
            COALESCE(#{unpaidIncentiveAmount}, 0),
            COALESCE(#{chargebackExpectedAmount}, 0),
            COALESCE(#{chargebackWaivedAmount}, 0),
            COALESCE(#{deductionAmount}, 0),
            COALESCE(#{finalPayAmount}, 0),
            'CALCULATING', #{note}, NOW(), #{createdBy}
        )
    </insert>

    <!-- 해촉 정산 금액 업데이트 -->
    <update id="update" parameterType="com.ga.core.vo.settle.TerminationSettlementVO">
        UPDATE termination_settlement SET
            unpaid_recruit_amount      = #{unpaidRecruitAmount},
            unpaid_installment_amount  = #{unpaidInstallmentAmount},
            unpaid_incentive_amount    = #{unpaidIncentiveAmount},
            chargeback_expected_amount = #{chargebackExpectedAmount},
            chargeback_waived_amount   = #{chargebackWaivedAmount},
            deduction_amount           = #{deductionAmount},
            final_pay_amount           = #{finalPayAmount},
            note                       = #{note},
            updated_at                 = NOW(),
            updated_by                 = #{updatedBy}
        WHERE term_settle_id = #{termSettleId}
          AND status = 'CALCULATING'
    </update>

    <!-- 상태 변경 -->
    <update id="updateStatus">
        UPDATE termination_settlement SET
            status       = #{status},
            confirmed_by = CASE WHEN #{status} = 'CONFIRMED' THEN #{userId} ELSE confirmed_by END,
            confirmed_at = CASE WHEN #{status} = 'CONFIRMED' THEN NOW()     ELSE confirmed_at END,
            paid_at      = CASE WHEN #{status} = 'PAID'      THEN NOW()     ELSE paid_at      END,
            updated_at   = NOW()
        WHERE term_settle_id = #{termSettleId}
    </update>

    <!-- 지급 처리: payment_id 연결 -->
    <update id="linkPayment">
        UPDATE termination_settlement SET
            payment_id = #{paymentId},
            status     = 'PAID',
            paid_at    = NOW(),
            updated_at = NOW()
        WHERE term_settle_id = #{termSettleId}
          AND status = 'CONFIRMED'
    </update>

</mapper>
