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

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

    <!--
        공통 SELECT 컬럼.
        carrier_id → insurance_company.company_name AS carrier_name.
        status 코드명: GUARANTEE_STATUS 그룹.
    -->
    <sql id="giCols">
        gi.guarantee_id,
        gi.agent_id,
        a.agent_name,
        o.org_name,
        gi.policy_no,
        gi.carrier_id,
        ic.company_name AS carrier_name,
        gi.guarantee_amount,
        gi.start_date,
        gi.end_date,
        gi.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'GUARANTEE_STATUS' AND cc.code = gi.status) AS status_name,
        gi.created_at,
        gi.updated_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="giCols"/>
        FROM agent_guarantee_insurance gi
        LEFT JOIN agent             a  ON a.agent_id   = gi.agent_id
        LEFT JOIN organization      o  ON o.org_id     = a.org_id
        LEFT JOIN insurance_company ic ON ic.company_id = gi.carrier_id
        WHERE gi.guarantee_id = #{guaranteeId}
    </select>

    <!-- 목록: agentId, agentName, carrierId, status 필터 -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="giCols"/>
        FROM agent_guarantee_insurance gi
        LEFT JOIN agent             a  ON a.agent_id    = gi.agent_id
        LEFT JOIN organization      o  ON o.org_id      = a.org_id
        LEFT JOIN insurance_company ic ON ic.company_id = gi.carrier_id
        <where>
            <if test="agentId != null">AND gi.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="carrierId != null">AND gi.carrier_id = #{carrierId}</if>
            <if test="status != null and status != ''">AND gi.status = #{status}</if>
        </where>
        ORDER BY gi.agent_id, gi.end_date DESC, gi.guarantee_id DESC
    </select>

    <!-- INSERT: status 기본값 'ACTIVE' -->
    <insert id="insert" useGeneratedKeys="true" keyProperty="guaranteeId">
        INSERT INTO agent_guarantee_insurance
            (agent_id, policy_no, carrier_id, guarantee_amount, start_date, end_date, status)
        VALUES
            (#{agentId}, #{policyNo}, #{carrierId}, #{guaranteeAmount},
             #{startDate}, #{endDate},
             COALESCE(#{status}, 'ACTIVE'))
    </insert>

    <update id="update">
        UPDATE agent_guarantee_insurance
           SET policy_no        = #{policyNo},
               carrier_id       = #{carrierId},
               guarantee_amount = #{guaranteeAmount},
               start_date       = #{startDate},
               end_date         = #{endDate},
               status           = #{status},
               updated_at       = NOW()
         WHERE guarantee_id = #{guaranteeId}
    </update>

    <delete id="delete">
        DELETE FROM agent_guarantee_insurance WHERE guarantee_id = #{guaranteeId}
    </delete>

</mapper>
