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

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

    <!-- 조회용 컬럼 목록 -->
    <sql id="cols">
        cro.recon_out_id,
        cro.carrier_id,
        ic.company_name                                                   AS carrier_name,
        cro.settle_month,
        cro.file_path,
        cro.file_name,
        cro.file_size,
        cro.record_count,
        cro.status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'RECON_OUT_STATUS' AND cc.code = cro.status) AS status_name,
        cro.sent_at,
        cro.ack_at,
        cro.ack_message,
        cro.fail_reason,
        cro.retry_count,
        cro.last_retry_at,
        cro.created_at,
        cro.created_by,
        u.user_name                                                        AS created_by_name,
        cro.updated_at
    </sql>

    <!-- 조인 절 -->
    <sql id="joins">
        FROM carrier_reconciliation_out cro
        LEFT JOIN insurance_company ic ON ic.company_id = cro.carrier_id
        LEFT JOIN users             u  ON u.user_id     = cro.created_by
    </sql>

    <!-- 전송 이력 목록 조회 (페이징) -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="cols"/>
        <include refid="joins"/>
        <where>
            <if test="carrierId != null">AND cro.carrier_id = #{carrierId}</if>
            <if test="settleMonth != null and settleMonth != ''">
                AND cro.settle_month = #{settleMonth}
            </if>
            <if test="status != null and status != ''">AND cro.status = #{status}</if>
        </where>
        ORDER BY cro.settle_month DESC, cro.carrier_id
    </select>

    <!-- 전송 이력 단건 조회 (응답) -->
    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="cols"/>
        <include refid="joins"/>
        WHERE cro.recon_out_id = #{reconOutId}
    </select>

    <!-- 전송 이력 단건 조회 (VO) -->
    <select id="selectVoById" resultMap="VOMap">
        SELECT recon_out_id, carrier_id, settle_month,
               file_path, file_name, file_size, record_count,
               status, sent_at, ack_at, ack_message,
               fail_reason, retry_count, last_retry_at,
               created_at, created_by, updated_at
        FROM carrier_reconciliation_out WHERE recon_out_id = #{reconOutId}
    </select>

    <!-- 보험사 + 정산월로 조회 (UNIQUE 보장) -->
    <select id="selectByCarrierMonth" resultMap="VOMap">
        SELECT recon_out_id, carrier_id, settle_month,
               file_path, file_name, file_size, record_count,
               status, sent_at, ack_at, ack_message,
               fail_reason, retry_count, last_retry_at,
               created_at, created_by, updated_at
        FROM carrier_reconciliation_out
        WHERE carrier_id   = #{carrierId}
          AND settle_month = #{settleMonth}
    </select>

    <!-- 전송 이력 등록 -->
    <insert id="insert" parameterType="com.ga.core.vo.settle.CarrierReconciliationOutVO"
            useGeneratedKeys="true" keyProperty="reconOutId">
        INSERT INTO carrier_reconciliation_out (
            carrier_id, settle_month,
            file_path, file_name, file_size, record_count,
            status, retry_count, created_at, created_by
        ) VALUES (
            #{carrierId}, #{settleMonth},
            #{filePath}, #{fileName}, #{fileSize}, #{recordCount},
            'READY', 0, NOW(), #{createdBy}
        )
    </insert>

    <!-- 상태 변경 (SENT/ACK/FAIL) -->
    <update id="updateStatus">
        UPDATE carrier_reconciliation_out SET
            status     = #{status},
            updated_at = NOW()
        WHERE recon_out_id = #{reconOutId}
    </update>

    <!-- 전송 완료: SENT 상태 + sent_at 기록 -->
    <update id="markSent">
        UPDATE carrier_reconciliation_out SET
            status     = 'SENT',
            sent_at    = NOW(),
            updated_at = NOW()
        WHERE recon_out_id = #{reconOutId}
          AND status IN ('READY', 'FAIL')
    </update>

    <!-- 수신 확인: ACK 상태 + ack_at + ack_message 기록 -->
    <update id="markAck">
        UPDATE carrier_reconciliation_out SET
            status      = 'ACK',
            ack_at      = NOW(),
            ack_message = #{ackMessage},
            updated_at  = NOW()
        WHERE recon_out_id = #{reconOutId}
          AND status = 'SENT'
    </update>

    <!-- 전송 실패: FAIL 상태 + fail_reason + retry_count 증가 -->
    <update id="markFail">
        UPDATE carrier_reconciliation_out SET
            status        = 'FAIL',
            fail_reason   = #{failReason},
            retry_count   = retry_count + 1,
            last_retry_at = NOW(),
            updated_at    = NOW()
        WHERE recon_out_id = #{reconOutId}
    </update>

</mapper>
