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

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

    <!-- 목록/단건 조회 컬럼 — 보험사명 조인 포함 -->
    <sql id="criJoinCols">
        cr.recon_id,
        cr.company_id, ic.company_code, ic.company_name,
        cr.settle_month,
        cr.file_name,
        cr.record_count,
        cr.reported_total,
        cr.system_total,
        cr.variance_amount,
        cr.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'RECON_IN_STATUS' AND cc.code = cr.status) AS status_name,
        cr.created_at
    </sql>

    <sql id="criJoins">
        FROM carrier_reconciliation_in cr
        LEFT JOIN insurance_company ic ON ic.company_id = cr.company_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="criJoinCols"/>
        <include refid="criJoins"/>
        WHERE cr.recon_id = #{reconId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="criJoinCols"/>
        <include refid="criJoins"/>
        <where>
            <if test="companyId != null">AND cr.company_id = #{companyId}</if>
            <if test="settleMonth != null and settleMonth != ''">AND cr.settle_month = #{settleMonth}</if>
            <if test="status != null and status != ''">AND cr.status = #{status}</if>
        </where>
        ORDER BY cr.settle_month DESC, cr.recon_id DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="reconId">
        INSERT INTO carrier_reconciliation_in
            (company_id, settle_month, file_name, record_count,
             reported_total, system_total, variance_amount, status,
             created_by, created_at)
        VALUES
            (#{companyId}, #{settleMonth}, #{fileName}, COALESCE(#{recordCount}, 0),
             COALESCE(#{reportedTotal}, 0), COALESCE(#{systemTotal}, 0),
             COALESCE(#{varianceAmount}, 0),
             COALESCE(#{status}, 'RECEIVED'), #{createdBy}, NOW())
    </insert>

    <update id="update">
        UPDATE carrier_reconciliation_in
           SET file_name       = #{fileName},
               record_count    = #{recordCount},
               reported_total  = #{reportedTotal},
               system_total    = #{systemTotal},
               variance_amount = #{varianceAmount},
               status          = #{status},
               updated_at      = NOW()
         WHERE recon_id = #{reconId}
    </update>

    <!--
        보험사 + 정산월 기준 upsert.
        UNIQUE(company_id, settle_month) 충돌 시 갱신 — 멱등 재처리 지원.
    -->
    <insert id="upsert" useGeneratedKeys="true" keyProperty="reconId">
        INSERT INTO carrier_reconciliation_in
            (company_id, settle_month, file_name, record_count,
             reported_total, system_total, variance_amount, status,
             created_by, created_at)
        VALUES
            (#{companyId}, #{settleMonth}, #{fileName}, COALESCE(#{recordCount}, 0),
             COALESCE(#{reportedTotal}, 0), COALESCE(#{systemTotal}, 0),
             COALESCE(#{varianceAmount}, 0),
             COALESCE(#{status}, 'RECEIVED'), #{createdBy}, NOW())
        ON CONFLICT (company_id, settle_month) DO UPDATE
            SET file_name       = EXCLUDED.file_name,
                record_count    = EXCLUDED.record_count,
                reported_total  = EXCLUDED.reported_total,
                system_total    = EXCLUDED.system_total,
                variance_amount = EXCLUDED.variance_amount,
                status          = EXCLUDED.status,
                updated_at      = NOW()
    </insert>

</mapper>
