<?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.contract.AgentContractMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.contract.AgentContractVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.contract.AgentContractResp"/>

    <sql id="joinCols">
        ac.contract_id, ac.agent_id, a.agent_name,
        ac.carrier_id, ic.company_name AS carrier_name,
        ac.contract_no, ac.signed_date,
        ac.effective_from, ac.effective_to,
        ac.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'AGENT_CONTRACT_STATUS' AND cc.code = ac.status) AS status_name,
        ac.onboarding_bonus_amount,
        ac.terminated_at, ac.terminated_reason,
        ac.created_at
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_contract ac
        LEFT JOIN agent a              ON a.agent_id = ac.agent_id
        LEFT JOIN insurance_company ic ON ic.company_id = ac.carrier_id
        WHERE ac.contract_id = #{contractId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_contract ac
        LEFT JOIN agent a              ON a.agent_id = ac.agent_id
        LEFT JOIN insurance_company ic ON ic.company_id = ac.carrier_id
        <where>
            <if test="agentId != null">AND ac.agent_id = #{agentId}</if>
            <if test="carrierId != null">AND ac.carrier_id = #{carrierId}</if>
            <if test="status != null and status != ''">AND ac.status = #{status}</if>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND (a.agent_name LIKE CONCAT('%', #{searchKeyword}, '%')
                     OR ac.contract_no LIKE CONCAT('%', #{searchKeyword}, '%'))
            </if>
        </where>
        ORDER BY ac.effective_from DESC, ac.contract_id DESC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="contractId">
        INSERT INTO agent_contract
            (agent_id, carrier_id, contract_no, signed_date, effective_from, effective_to,
             status, onboarding_bonus_amount, created_by)
        VALUES
            (#{agentId}, #{carrierId}, #{contractNo}, #{signedDate}, #{effectiveFrom}, #{effectiveTo},
             COALESCE(#{status}, 'ACTIVE'), #{onboardingBonusAmount}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE agent_contract
           SET contract_no             = #{contractNo},
               signed_date             = #{signedDate},
               effective_from          = #{effectiveFrom},
               effective_to            = #{effectiveTo},
               onboarding_bonus_amount = #{onboardingBonusAmount},
               updated_at              = NOW(),
               updated_by              = #{updatedBy}
         WHERE contract_id = #{contractId}
    </update>

    <update id="updateStatus">
        UPDATE agent_contract
           SET status            = #{status},
               terminated_at     = CASE WHEN #{status} = 'TERMINATED' THEN NOW() ELSE terminated_at END,
               terminated_reason = #{terminatedReason},
               updated_at        = NOW()
         WHERE contract_id = #{contractId}
    </update>

    <delete id="delete">
        DELETE FROM agent_contract WHERE contract_id = #{contractId}
    </delete>

</mapper>
