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

    <!--
        KPI 대시보드 Mapper — V37 Materialized View 4종 단일 XML.
        모든 MV는 읽기 전용.
        REFRESH는 ga-batch 야간 배치에서 REFRESH MATERIALIZED VIEW CONCURRENTLY 호출.
        주의: 컬럼명은 MV 정의와 정확히 일치해야 함.
    -->

    <resultMap id="MonthlyMap"   type="com.ga.core.vo.kpi.KpiMonthlySummaryResp"/>
    <resultMap id="OrgMap"       type="com.ga.core.vo.kpi.KpiOrgSummaryResp"/>
    <resultMap id="RetentionMap" type="com.ga.core.vo.kpi.KpiRetentionResp"/>
    <resultMap id="CumulativeMap" type="com.ga.core.vo.kpi.KpiCumulativeResp"/>

    <!-- ============================================================
         1. mv_monthly_summary — 월별 정산 요약
         단위: 정산월 전체 합산.
         yyyymmFrom / yyyymmTo 범위 필터 가능.
    ============================================================ -->
    <select id="selectMonthlySummary" resultMap="MonthlyMap">
        SELECT
            yyyymm,
            agent_count,
            total_recruit,
            total_maintain,
            total_override,
            total_chargeback,
            total_gross,
            total_tax,
            total_net,
            total_incentive,
            refreshed_at::timestamp AS refreshed_at
        FROM mv_monthly_summary
        <where>
            <if test="yyyymmFrom != null and yyyymmFrom != ''">
                AND yyyymm &gt;= #{yyyymmFrom}
            </if>
            <if test="yyyymmTo != null and yyyymmTo != ''">
                AND yyyymm &lt;= #{yyyymmTo}
            </if>
        </where>
        ORDER BY yyyymm DESC
    </select>

    <!-- ============================================================
         2. mv_org_summary — 조직별 월별 요약
         orgId 필터 + 정산년월 범위 필터 가능.
    ============================================================ -->
    <select id="selectOrgSummary" resultMap="OrgMap">
        SELECT
            org_id,
            org_name,
            org_type,
            yyyymm,
            agent_count,
            total_recruit,
            total_maintain,
            total_override,
            total_chargeback,
            total_gross,
            total_net,
            refreshed_at::timestamp AS refreshed_at
        FROM mv_org_summary
        <where>
            <if test="orgId != null">AND org_id = #{orgId}</if>
            <if test="yyyymmFrom != null and yyyymmFrom != ''">
                AND yyyymm &gt;= #{yyyymmFrom}
            </if>
            <if test="yyyymmTo != null and yyyymmTo != ''">
                AND yyyymm &lt;= #{yyyymmTo}
            </if>
        </where>
        ORDER BY yyyymm DESC, org_id
    </select>

    <!-- ============================================================
         3. mv_retention — 계약 유지율 (13M/25M)
         carrierId 필터 + contract_month 범위 필터 가능.
         yyyymmFrom/yyyymmTo는 contract_month 기준으로 적용.
    ============================================================ -->
    <select id="selectRetention" resultMap="RetentionMap">
        SELECT
            contract_month,
            carrier_id,
            carrier_name,
            total_contracts,
            retained_13m,
            retained_25m,
            retention_rate_13m,
            retention_rate_25m,
            refreshed_at::timestamp AS refreshed_at
        FROM mv_retention
        <where>
            <if test="carrierId != null">AND carrier_id = #{carrierId}</if>
            <if test="yyyymmFrom != null and yyyymmFrom != ''">
                AND contract_month &gt;= #{yyyymmFrom}
            </if>
            <if test="yyyymmTo != null and yyyymmTo != ''">
                AND contract_month &lt;= #{yyyymmTo}
            </if>
        </where>
        ORDER BY contract_month DESC, carrier_id
    </select>

    <!-- ============================================================
         4. mv_cumulative — 설계사/조직별 누계
         levelType(AGENT/ORG), orgId, agentId 필터 가능.
         mv_cumulative는 AGENT + ORG 레벨 혼재 (UNION ALL).
    ============================================================ -->
    <select id="selectCumulative" resultMap="CumulativeMap">
        SELECT
            level_type,
            entity_id,
            entity_name,
            org_id,
            org_name,
            first_settle_month,
            last_settle_month,
            settle_month_count,
            cum_gross,
            cum_net,
            cum_chargeback,
            cum_override,
            cum_incentive,
            refreshed_at::timestamp AS refreshed_at
        FROM mv_cumulative
        <where>
            <!-- levelType으로 AGENT/ORG 레벨 선택 -->
            <if test="levelType != null and levelType != ''">
                AND level_type = #{levelType}
            </if>
            <!-- AGENT 레벨에서 agentId 필터 -->
            <if test="agentId != null">
                AND level_type = 'AGENT'
                AND entity_id  = #{agentId}
            </if>
            <!-- 조직 소속 필터 (AGENT 레벨에서 org_id 기준) -->
            <if test="orgId != null and agentId == null">
                AND org_id = #{orgId}
            </if>
        </where>
        ORDER BY level_type, org_id, entity_id
    </select>

</mapper>
