<?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.succession.ContractSuccessionMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.succession.ContractSuccessionVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.succession.ContractSuccessionResp"/>

    <sql id="joinCols">
        cs.succession_id, cs.contract_id, ct.policy_no,
        cs.from_agent_id, fa.agent_name AS from_agent_name,
        cs.to_agent_id,   ta.agent_name AS to_agent_name,
        cs.succession_date,
        cs.reason,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'SUCCESSION_REASON' AND cc.code = cs.reason) AS reason_name,
        cs.memo,
        cs.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'SUCCESSION_STATUS' AND cc.code = cs.status) AS status_name,
        cs.created_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM contract_succession cs
        LEFT JOIN contract ct ON ct.contract_id = cs.contract_id
        LEFT JOIN agent fa    ON fa.agent_id = cs.from_agent_id
        LEFT JOIN agent ta    ON ta.agent_id = cs.to_agent_id
        WHERE cs.succession_id = #{successionId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM contract_succession cs
        LEFT JOIN contract ct ON ct.contract_id = cs.contract_id
        LEFT JOIN agent fa    ON fa.agent_id = cs.from_agent_id
        LEFT JOIN agent ta    ON ta.agent_id = cs.to_agent_id
        <where>
            <if test="contractId != null">AND cs.contract_id = #{contractId}</if>
            <if test="fromAgentId != null">AND cs.from_agent_id = #{fromAgentId}</if>
            <if test="toAgentId != null">AND cs.to_agent_id = #{toAgentId}</if>
            <if test="reason != null and reason != ''">AND cs.reason = #{reason}</if>
            <if test="status != null and status != ''">AND cs.status = #{status}</if>
        </where>
        ORDER BY cs.succession_date DESC, cs.succession_id DESC
    </select>

    <!-- 정산월 기준 계약별 유효 승계 (가장 최근 ACTIVE 1건) -->
    <select id="selectEffectiveSuccessions" resultMap="VOMap">
        SELECT DISTINCT ON (cs.contract_id)
               cs.contract_id, cs.to_agent_id, cs.from_agent_id, cs.succession_date, cs.status
        FROM contract_succession cs
        WHERE cs.status = 'ACTIVE'
          AND TO_CHAR(cs.succession_date, 'YYYYMM') &lt;= #{settleMonth}
        ORDER BY cs.contract_id, cs.succession_date DESC, cs.succession_id DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="successionId">
        INSERT INTO contract_succession
            (contract_id, from_agent_id, to_agent_id, succession_date, reason, memo, status, created_by)
        VALUES
            (#{contractId}, #{fromAgentId}, #{toAgentId}, #{successionDate},
             COALESCE(#{reason}, 'TERMINATION'), #{memo}, 'ACTIVE', #{createdBy})
    </insert>

    <update id="cancel">
        UPDATE contract_succession
           SET status     = 'CANCELED',
               updated_at = NOW(),
               updated_by = #{updatedBy}
         WHERE succession_id = #{successionId}
    </update>

    <delete id="delete">
        DELETE FROM contract_succession WHERE succession_id = #{successionId}
    </delete>

</mapper>
