<?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.complaint.ComplaintMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.complaint.ComplaintVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.complaint.ComplaintResp"/>

    <sql id="joinCols">
        c.complaint_id, c.contract_id, ct.policy_no,
        c.agent_id, a.agent_name, o.org_name,
        c.customer_name, c.complaint_date,
        c.channel,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='COMPLAINT_CHANNEL' AND cc.code=c.channel) AS channel_name,
        c.complaint_type,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='COMPLAINT_TYPE' AND cc.code=c.complaint_type) AS complaint_type_name,
        c.severity,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='COMPLAINT_SEVERITY' AND cc.code=c.severity) AS severity_name,
        c.status,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='COMPLAINT_STATUS' AND cc.code=c.status) AS status_name,
        c.resolution, c.chargeback_triggered, c.settled_at, c.created_at
    </sql>

    <sql id="joins">
        FROM complaint c
        LEFT JOIN contract ct    ON ct.contract_id = c.contract_id
        LEFT JOIN agent a        ON a.agent_id = c.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joins"/>
        WHERE c.complaint_id = #{complaintId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        <include refid="joins"/>
        <where>
            <if test="agentId != null">AND c.agent_id = #{agentId}</if>
            <if test="complaintType != null and complaintType != ''">AND c.complaint_type = #{complaintType}</if>
            <if test="severity != null and severity != ''">AND c.severity = #{severity}</if>
            <if test="chargebackTriggered != null">AND c.chargeback_triggered = #{chargebackTriggered}</if>
            <if test="status != null and status != ''">AND c.status = #{status}</if>
            <if test="startDate != null and startDate != ''">AND c.complaint_date >= #{startDate}::DATE</if>
            <if test="endDate != null and endDate != ''">AND c.complaint_date &lt;= #{endDate}::DATE</if>
        </where>
        ORDER BY c.complaint_date DESC, c.complaint_id DESC
    </select>

    <!-- 환수 트리거 미처리 건 (부분 인덱스 idx_comp_chargeback 활용) -->
    <select id="selectPendingChargeback" resultMap="VOMap">
        SELECT c.complaint_id, c.contract_id, c.agent_id, c.customer_name,
               c.complaint_date, c.channel, c.complaint_type, c.severity, c.status,
               c.resolution, c.chargeback_triggered, c.settled_at, c.created_at
        FROM complaint c
        WHERE c.chargeback_triggered = TRUE
          AND c.status NOT IN ('CLOSED', 'WITHDRAWN')
        ORDER BY c.complaint_date ASC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="complaintId">
        INSERT INTO complaint
            (contract_id, agent_id, customer_name, complaint_date, channel,
             complaint_type, severity, status, resolution, chargeback_triggered, created_by)
        VALUES
            (#{contractId}, #{agentId}, #{customerName}, #{complaintDate}, #{channel},
             #{complaintType}, COALESCE(#{severity}, 'MID'), COALESCE(#{status}, 'OPEN'),
             #{resolution}, #{chargebackTriggered}, #{createdBy})
    </insert>

    <update id="update">
        UPDATE complaint
           SET complaint_type       = #{complaintType},
               severity             = #{severity},
               resolution           = #{resolution},
               chargeback_triggered = #{chargebackTriggered},
               updated_at           = NOW(),
               updated_by           = #{updatedBy}
         WHERE complaint_id = #{complaintId}
    </update>

    <update id="updateStatus">
        UPDATE complaint
           SET status      = #{status},
               resolution  = #{resolution},
               settled_at  = CASE WHEN #{status} IN ('CLOSED', 'WITHDRAWN') THEN NOW() ELSE settled_at END,
               updated_at  = NOW()
         WHERE complaint_id = #{complaintId}
    </update>

    <delete id="delete">
        DELETE FROM complaint WHERE complaint_id = #{complaintId}
    </delete>

</mapper>
