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

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

    <!--
        공통 SELECT 컬럼.
        계약 policy_no 및 설계사/조직명 조인 포함.
        role 코드명: JOINT_ROLE 그룹.
    -->
    <sql id="jointCols">
        jc.joint_id,
        jc.contract_id,
        ct.policy_no,
        jc.agent_id,
        a.agent_name,
        o.org_name,
        jc.role,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'JOINT_ROLE' AND cc.code = jc.role) AS role_name,
        jc.share_rate,
        jc.created_at,
        jc.created_by
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="jointCols"/>
        FROM joint_contract jc
        LEFT JOIN contract     ct ON ct.contract_id = jc.contract_id
        LEFT JOIN agent        a  ON a.agent_id     = jc.agent_id
        LEFT JOIN organization o  ON o.org_id       = a.org_id
        WHERE jc.joint_id = #{jointId}
    </select>

    <!-- 목록: contractId, agentId, agentName, role 필터 -->
    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="jointCols"/>
        FROM joint_contract jc
        LEFT JOIN contract     ct ON ct.contract_id = jc.contract_id
        LEFT JOIN agent        a  ON a.agent_id     = jc.agent_id
        LEFT JOIN organization o  ON o.org_id       = a.org_id
        <where>
            <if test="contractId != null">AND jc.contract_id = #{contractId}</if>
            <if test="agentId != null">AND jc.agent_id = #{agentId}</if>
            <if test="agentName != null and agentName != ''">AND a.agent_name LIKE '%' || #{agentName} || '%'</if>
            <if test="role != null and role != ''">AND jc.role = #{role}</if>
        </where>
        ORDER BY jc.contract_id, jc.role, jc.joint_id
    </select>

    <!--
        계약별 공동설계사 목록 (지분율 확인 및 share_rate 합 검증에 사용).
    -->
    <select id="selectByContract" resultMap="RespMap">
        SELECT <include refid="jointCols"/>
        FROM joint_contract jc
        LEFT JOIN contract     ct ON ct.contract_id = jc.contract_id
        LEFT JOIN agent        a  ON a.agent_id     = jc.agent_id
        LEFT JOIN organization o  ON o.org_id       = a.org_id
        WHERE jc.contract_id = #{contractId}
        ORDER BY jc.role, jc.joint_id
    </select>

    <!--
        계약별 지분율 합산 — Service에서 합=1 검증 가드에 사용.
    -->
    <select id="sumShareByContract" resultType="java.math.BigDecimal">
        SELECT COALESCE(SUM(share_rate), 0)
          FROM joint_contract
         WHERE contract_id = #{contractId}
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="jointId">
        INSERT INTO joint_contract
            (contract_id, agent_id, role, share_rate, created_by)
        VALUES
            (#{contractId}, #{agentId}, #{role}, #{shareRate}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE joint_contract
           SET role       = #{role},
               share_rate = #{shareRate}
         WHERE joint_id = #{jointId}
    </update>

    <delete id="delete">
        DELETE FROM joint_contract WHERE joint_id = #{jointId}
    </delete>

</mapper>
