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

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

    <sql id="joinCols">
        f.forecast_id,
        f.forecast_month,
        f.target_org_id,
        (SELECT o.org_name FROM organization o WHERE o.org_id = f.target_org_id) AS target_org_name,
        f.metric_code,
        CASE f.metric_code
            WHEN 'MONTHLY_PREMIUM' THEN '월 수수료총액 (gross)'
            WHEN 'MONTHLY_PAYOUT'  THEN '월 순지급액 (net)'
            WHEN 'RETENTION_13M'   THEN '13개월 유지율'
            WHEN 'RETENTION_25M'   THEN '25개월 유지율'
            ELSE f.metric_code
        END AS metric_name,
        f.forecast_value,
        f.lower_bound,
        f.upper_bound,
        f.method,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'FORECAST_METHOD' AND cc.code = f.method) AS method_name,
        f.base_period,
        f.sample_count,
        f.generated_at
    </sql>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
          FROM forecast_kpi_monthly f
        <where>
            <if test="forecastMonth != null and forecastMonth != ''">AND f.forecast_month = #{forecastMonth}</if>
            <if test="targetOrgId   != null">AND f.target_org_id = #{targetOrgId}</if>
            <if test="metricCode    != null and metricCode    != ''">AND f.metric_code   = #{metricCode}</if>
        </where>
        ORDER BY f.forecast_month DESC, f.metric_code, f.target_org_id NULLS FIRST
    </select>

    <!--
      UPSERT (forecast_month, COALESCE(target_org_id,-1), metric_code) 키.
      target_org_id NULL은 부분 UNIQUE 인덱스(uq_fkm_all)로 처리.
      ON CONFLICT 절은 두 가지 케이스 (전사/조직별)를 모두 다뤄야 하지만
      MyBatis foreach에서는 키별로 INSERT 직후 갱신을 보장하기 어려우니
      pre-DELETE 후 INSERT 패턴으로 단순화.
    -->
    <delete id="deleteByKey" parameterType="map">
        DELETE FROM forecast_kpi_monthly
         WHERE forecast_month = #{forecastMonth}
           AND metric_code    = #{metricCode}
           AND ((#{targetOrgId,jdbcType=BIGINT} IS NULL AND target_org_id IS NULL)
                OR target_org_id = #{targetOrgId,jdbcType=BIGINT})
    </delete>

    <insert id="upsertBatch">
        INSERT INTO forecast_kpi_monthly
            (forecast_month, target_org_id, metric_code,
             forecast_value, lower_bound, upper_bound,
             method, base_period, sample_count)
        VALUES
        <foreach collection="list" item="f" separator=",">
            (#{f.forecastMonth}, #{f.targetOrgId}, #{f.metricCode},
             #{f.forecastValue}, #{f.lowerBound}, #{f.upperBound},
             COALESCE(#{f.method}, 'SMA3'), #{f.basePeriod}, #{f.sampleCount})
        </foreach>
        ON CONFLICT DO NOTHING
    </insert>

    <!--
        예측 산출.
        - sampleSize 개월 직전 평균(평균=forecast_value), 표준편차(±1σ=lower/upper)
        - method = SMA3/SMA6/NAIVE
        - metric:
            MONTHLY_PREMIUM = mv_monthly_summary.total_gross
            MONTHLY_PAYOUT  = mv_monthly_summary.total_net
            RETENTION_13M   = mv_retention.retention_rate_13m (carrier 평균)
            RETENTION_25M   = mv_retention.retention_rate_25m (carrier 평균)
        - target_org_id 는 모두 NULL (전사) — 조직별은 후속 보강
    -->
    <select id="computeForecast" resultMap="VOMap">
        WITH params AS (
            SELECT REPLACE(#{forecastMonth},'-','')                                          AS fmonth_yyyymm,
                   TO_CHAR((TO_DATE(#{forecastMonth}||'-01','YYYY-MM-DD') - INTERVAL '1 months'),  'YYYYMM') AS prev_yyyymm,
                   TO_CHAR((TO_DATE(#{forecastMonth}||'-01','YYYY-MM-DD') - (#{sampleSize}||' months')::INTERVAL), 'YYYYMM') AS from_yyyymm
        ),
        mp AS (
            SELECT 'MONTHLY_PREMIUM' AS metric_code,
                   AVG(total_gross)   AS fv,
                   AVG(total_gross) - COALESCE(STDDEV_POP(total_gross), 0) AS lb,
                   AVG(total_gross) + COALESCE(STDDEV_POP(total_gross), 0) AS ub,
                   COUNT(*)           AS sc
              FROM mv_monthly_summary, params
             WHERE yyyymm BETWEEN from_yyyymm AND prev_yyyymm
        ),
        mo AS (
            SELECT 'MONTHLY_PAYOUT' AS metric_code,
                   AVG(total_net)   AS fv,
                   AVG(total_net) - COALESCE(STDDEV_POP(total_net), 0) AS lb,
                   AVG(total_net) + COALESCE(STDDEV_POP(total_net), 0) AS ub,
                   COUNT(*)         AS sc
              FROM mv_monthly_summary, params
             WHERE yyyymm BETWEEN from_yyyymm AND prev_yyyymm
        ),
        r13 AS (
            SELECT 'RETENTION_13M'           AS metric_code,
                   AVG(retention_rate_13m)   AS fv,
                   AVG(retention_rate_13m) - COALESCE(STDDEV_POP(retention_rate_13m), 0) AS lb,
                   AVG(retention_rate_13m) + COALESCE(STDDEV_POP(retention_rate_13m), 0) AS ub,
                   COUNT(*)                  AS sc
              FROM mv_retention, params
             WHERE contract_month BETWEEN from_yyyymm AND prev_yyyymm
        ),
        r25 AS (
            SELECT 'RETENTION_25M'           AS metric_code,
                   AVG(retention_rate_25m)   AS fv,
                   AVG(retention_rate_25m) - COALESCE(STDDEV_POP(retention_rate_25m), 0) AS lb,
                   AVG(retention_rate_25m) + COALESCE(STDDEV_POP(retention_rate_25m), 0) AS ub,
                   COUNT(*)                  AS sc
              FROM mv_retention, params
             WHERE contract_month BETWEEN from_yyyymm AND prev_yyyymm
        ),
        merged AS (
            SELECT * FROM mp WHERE mp.fv IS NOT NULL
            UNION ALL SELECT * FROM mo WHERE mo.fv IS NOT NULL
            UNION ALL SELECT * FROM r13 WHERE r13.fv IS NOT NULL
            UNION ALL SELECT * FROM r25 WHERE r25.fv IS NOT NULL
        )
        SELECT
               #{forecastMonth}                                                  AS forecast_month,
               NULL::BIGINT                                                       AS target_org_id,
               m.metric_code                                                      AS metric_code,
               m.fv                                                               AS forecast_value,
               m.lb                                                               AS lower_bound,
               m.ub                                                               AS upper_bound,
               #{method}                                                          AS method,
               CONCAT((SELECT from_yyyymm FROM params), '~', (SELECT prev_yyyymm FROM params)) AS base_period,
               m.sc::INT                                                          AS sample_count
          FROM merged m
    </select>

</mapper>
