<?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.kpi.RetentionAnomalyAlertMapper">

    <resultMap id="RespMap" type="com.ga.core.vo.kpi.RetentionAnomalyAlertResp"/>
    <resultMap id="VOMap"   type="com.ga.core.vo.kpi.RetentionAnomalyAlertVO"/>

    <sql id="joinCols">
        ra.alert_id,
        ra.target_type,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'ANOMALY_TARGET_TYPE' AND cc.code = ra.target_type) AS target_type_name,
        ra.target_id,
        CASE ra.target_type
            WHEN 'AGENT' THEN (SELECT a.agent_name FROM agent a WHERE a.agent_id = ra.target_id)
            WHEN 'ORG'   THEN (SELECT o.org_name   FROM organization o WHERE o.org_id = ra.target_id)
            ELSE '전사'
        END AS target_name,
        ra.alert_month,
        ra.retention_band,
        ra.expected_rate,
        ra.actual_rate,
        ra.deviation,
        ra.severity,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'ANOMALY_SEVERITY' AND cc.code = ra.severity) AS severity_name,
        ra.status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'ANOMALY_STATUS' AND cc.code = ra.status) AS status_name,
        ra.note,
        ra.detected_at,
        ra.reviewed_at,
        ra.reviewed_by,
        (SELECT u.user_name FROM users u WHERE u.user_id = ra.reviewed_by) AS reviewed_by_name
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
          FROM retention_anomaly_alert ra
        <where>
            <if test="alertMonth    != null and alertMonth    != ''">AND ra.alert_month    = #{alertMonth}</if>
            <if test="targetType    != null and targetType    != ''">AND ra.target_type    = #{targetType}</if>
            <if test="retentionBand != null and retentionBand != ''">AND ra.retention_band = #{retentionBand}</if>
            <if test="severity      != null and severity      != ''">AND ra.severity       = #{severity}</if>
            <if test="status        != null and status        != ''">AND ra.status         = #{status}</if>
        </where>
        ORDER BY ra.alert_month DESC, ra.severity DESC, ra.detected_at DESC
    </select>

    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
          FROM retention_anomaly_alert ra
         WHERE ra.alert_id = #{alertId}
    </select>

    <insert id="insertBatch">
        INSERT INTO retention_anomaly_alert
            (target_type, target_id, alert_month, retention_band,
             expected_rate, actual_rate, deviation, severity, status, note)
        VALUES
        <foreach collection="list" item="a" separator=",">
            (#{a.targetType}, #{a.targetId}, #{a.alertMonth}, #{a.retentionBand},
             #{a.expectedRate}, #{a.actualRate}, #{a.deviation},
             #{a.severity}, COALESCE(#{a.status}, 'NEW'), #{a.note})
        </foreach>
        ON CONFLICT DO NOTHING
    </insert>

    <update id="updateStatus">
        UPDATE retention_anomaly_alert
           SET status      = #{status},
               note        = COALESCE(#{note}, note),
               reviewed_at = now(),
               reviewed_by = #{reviewedBy}
         WHERE alert_id IN
        <foreach collection="ids" item="id" open="(" separator="," close=")">#{id}</foreach>
    </update>

    <!--
        탐지 SQL.
        입력 alertMonth='YYYY-MM' → mv_retention.contract_month='YYYYMM'
        직전 3개월 평균 rate 대비 deviation >= 1.5%P → 알림.
        target_type='ALL' (carrier 통합) + carrier별 ('ORG'-like)는 carrier_id 를 target_id로.
        retention_band 두 종류(13M, 25M) UNION ALL.

        severity 룰:
          |dev| < 3   → LOW
          |dev| < 6   → MED
          else        → HIGH
    -->
    <select id="detectAnomalies" resultMap="VOMap">
        WITH curr AS (
            SELECT carrier_id, retention_rate_13m AS rate
              FROM mv_retention
             WHERE contract_month = REPLACE(#{alertMonth}, '-', '')
        ),
        prev AS (
            SELECT carrier_id, AVG(retention_rate_13m) AS exp_rate
              FROM mv_retention
             WHERE contract_month &lt; REPLACE(#{alertMonth}, '-', '')
               AND contract_month >=
                   TO_CHAR((TO_DATE(#{alertMonth}||'-01','YYYY-MM-DD') - INTERVAL '3 months'), 'YYYYMM')
             GROUP BY carrier_id
        ),
        anom13 AS (
            SELECT 'ALL'::VARCHAR(10)  AS target_type,
                   c.carrier_id        AS target_id,
                   #{alertMonth}::VARCHAR(7)  AS alert_month,
                   '13M'::VARCHAR(5)   AS retention_band,
                   p.exp_rate          AS expected_rate,
                   c.rate              AS actual_rate,
                   (c.rate - p.exp_rate) AS deviation,
                   CASE
                       WHEN ABS(c.rate - p.exp_rate) >= 6 THEN 'HIGH'
                       WHEN ABS(c.rate - p.exp_rate) >= 3 THEN 'MED'
                       ELSE 'LOW'
                   END AS severity,
                   'NEW'::VARCHAR(10)  AS status,
                   NULL::VARCHAR(300)  AS note
              FROM curr c JOIN prev p ON p.carrier_id = c.carrier_id
             WHERE ABS(c.rate - p.exp_rate) >= 1.5
        ),
        curr25 AS (
            SELECT carrier_id, retention_rate_25m AS rate
              FROM mv_retention
             WHERE contract_month = REPLACE(#{alertMonth}, '-', '')
        ),
        prev25 AS (
            SELECT carrier_id, AVG(retention_rate_25m) AS exp_rate
              FROM mv_retention
             WHERE contract_month &lt; REPLACE(#{alertMonth}, '-', '')
               AND contract_month >=
                   TO_CHAR((TO_DATE(#{alertMonth}||'-01','YYYY-MM-DD') - INTERVAL '3 months'), 'YYYYMM')
             GROUP BY carrier_id
        ),
        anom25 AS (
            SELECT 'ALL'::VARCHAR(10), c.carrier_id, #{alertMonth}::VARCHAR(7), '25M'::VARCHAR(5),
                   p.exp_rate, c.rate, (c.rate - p.exp_rate),
                   CASE
                       WHEN ABS(c.rate - p.exp_rate) >= 6 THEN 'HIGH'
                       WHEN ABS(c.rate - p.exp_rate) >= 3 THEN 'MED'
                       ELSE 'LOW'
                   END,
                   'NEW'::VARCHAR(10),
                   NULL::VARCHAR(300)
              FROM curr25 c JOIN prev25 p ON p.carrier_id = c.carrier_id
             WHERE ABS(c.rate - p.exp_rate) >= 1.5
        )
        SELECT * FROM anom13
        UNION ALL
        SELECT * FROM anom25
    </select>

</mapper>
