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

    <resultMap id="CBMap" type="com.ga.core.vo.settle.ChargebackVO"/>

    <resultMap id="CBRespMap" type="com.ga.core.vo.settle.ChargebackResp"/>

    <!-- 조회 공통 컬럼: chargeback 전체 + 조인 필드 -->
    <sql id="cbRespCols">
        cb.cb_id,
        cb.contract_id,
        cb.agent_id,
        a.agent_name,
        cb.policy_no,
        ct.contractor_name,
        cb.lapse_month,
        cb.cb_rate,
        cb.cb_amount,
        cb.paid_amount,
        cb.remain_amount,
        cb.settle_month,
        cb.installment_no,
        cb.max_installments,
        cb.status,
        cb.created_at
    </sql>

    <!-- 동적 WHERE 조건 (selectList / countByCondition 공용) -->
    <sql id="cbWhere">
        <where>
            <if test="settleMonth != null and settleMonth != ''">
                AND cb.settle_month = #{settleMonth}
            </if>
            <if test="agentId != null">
                AND cb.agent_id = #{agentId}
            </if>
            <if test="status != null and status != ''">
                AND cb.status = #{status}
            </if>
            <if test="policyNo != null and policyNo != ''">
                AND cb.policy_no = #{policyNo}
            </if>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND (cb.policy_no LIKE CONCAT('%', #{searchKeyword}, '%')
                  OR a.agent_name LIKE CONCAT('%', #{searchKeyword}, '%'))
            </if>
            <if test="startDate != null and startDate != ''">
                AND cb.created_at &gt;= #{startDate}::date
            </if>
            <if test="endDate != null and endDate != ''">
                AND cb.created_at &lt;= (#{endDate}::date + INTERVAL '1 day')
            </if>
        </where>
    </sql>

    <insert id="insert" parameterType="com.ga.core.vo.settle.ChargebackVO" useGeneratedKeys="true" keyProperty="cbId">
        INSERT INTO chargeback (contract_id, agent_id, policy_no, lapse_month, cb_rate,
                                cb_amount, paid_amount, remain_amount, settle_month,
                                installment_no, max_installments, status, created_at)
        VALUES (#{contractId}, #{agentId}, #{policyNo}, #{lapseMonth}, #{cbRate},
                #{cbAmount}, COALESCE(#{paidAmount}, 0), #{remainAmount}, #{settleMonth},
                #{installmentNo}, #{maxInstallments}, COALESCE(#{status},'NEW'), NOW())
    </insert>

    <insert id="insertBatch">
        INSERT INTO chargeback (contract_id, agent_id, policy_no, lapse_month, cb_rate,
                                cb_amount, remain_amount, settle_month, status, created_at)
        VALUES
        <foreach collection="list" item="r" separator=",">
            (#{r.contractId}, #{r.agentId}, #{r.policyNo}, #{r.lapseMonth}, #{r.cbRate},
             #{r.cbAmount}, #{r.remainAmount}, #{r.settleMonth}, COALESCE(#{r.status},'NEW'), NOW())
        </foreach>
    </insert>

    <select id="selectByAgent" resultMap="CBMap">
        SELECT * FROM chargeback
        WHERE agent_id = #{agentId}
        <if test="settleMonth != null and settleMonth != ''">AND settle_month = #{settleMonth}</if>
        ORDER BY created_at DESC
    </select>

    <update id="updatePaid">
        UPDATE chargeback SET
            paid_amount = paid_amount + #{paidAmount},
            remain_amount = cb_amount - (paid_amount + #{paidAmount}),
            status = #{status}
        WHERE cb_id = #{cbId}
    </update>

    <select id="aggregateByAgent" resultType="map">
        SELECT agent_id AS "agentId", SUM(cb_amount) AS "total"
        FROM chargeback WHERE settle_month = #{settleMonth} GROUP BY agent_id
    </select>

    <select id="sumByMonth" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(cb_amount), 0) FROM chargeback WHERE settle_month = #{settleMonth}
    </select>

    <!-- ── P10 환수자료 업로드 / 조회 ────────────────────────────────────── -->

    <!--
        환수 목록 조회.
        PageHelper.startPage() 호출 후 이 쿼리 실행 시 자동 페이징.
        agent / contract 좌조인: contract 없는 행(보험사 직접 통보, contract_id NULL)도 포함.
    -->
    <select id="selectList" resultMap="CBRespMap">
        SELECT <include refid="cbRespCols"/>
        FROM chargeback cb
        JOIN agent a ON a.agent_id = cb.agent_id
        LEFT JOIN contract ct ON ct.contract_id = cb.contract_id
        <include refid="cbWhere"/>
        ORDER BY cb.created_at DESC, cb.cb_id DESC
    </select>

    <!--
        페이징 없이 총 건수만 반환.
        PageHelper 없이 count가 필요한 경우(별도 카운트 쿼리) 사용.
    -->
    <select id="countByCondition" resultType="int">
        SELECT COUNT(*)
        FROM chargeback cb
        JOIN agent a ON a.agent_id = cb.agent_id
        LEFT JOIN contract ct ON ct.contract_id = cb.contract_id
        <include refid="cbWhere"/>
    </select>

    <!--
        동일 contract_id + settle_month 환수 레코드 존재 여부.
        반환값 > 0 이면 중복 → 업로드 시 해당 행 스킵 + 에러행 리포트.
    -->
    <select id="existsByContractAndMonth" resultType="int">
        SELECT COUNT(*)
        FROM chargeback
        WHERE contract_id = #{contractId}
          AND settle_month = #{settleMonth}
    </select>

</mapper>
