<?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.report.ReportMapper">

    <!--
        리포트 Mapper — 읽기전용 집계 (신규 테이블 없음).
        mapUnderscoreToCamelCase=true 전제: 집계 컬럼에 snake alias 부여.
    -->

    <!-- 1. 실적 리포트 — 정산월별 settle_master 집계 -->
    <sql id="performanceSql">
        SELECT settle_month,
               COUNT(DISTINCT agent_id)            AS agent_count,
               COALESCE(SUM(recruit_total), 0)     AS recruit_total,
               COALESCE(SUM(maintain_total), 0)    AS maintain_total,
               COALESCE(SUM(net_amount), 0)        AS net_amount
        FROM settle_master
        WHERE settle_month LIKE #{year} || '%'
        GROUP BY settle_month
        ORDER BY settle_month
    </sql>

    <select id="selectPerformance" resultType="com.ga.core.vo.report.PerfReportRow">
        <include refid="performanceSql"/>
    </select>
    <select id="selectPerformanceCursor" resultType="com.ga.core.vo.report.PerfReportRow" fetchSize="500">
        <include refid="performanceSql"/>
    </select>

    <!-- 2. 조직별 리포트 — 정산월 기준 조직 단위 집계 -->
    <sql id="orgSql">
        SELECT o.org_id,
               o.org_name,
               o.org_type,
               COUNT(DISTINCT sm.agent_id)          AS agent_count,
               COALESCE(SUM(sm.recruit_total), 0)   AS recruit_total,
               COALESCE(SUM(sm.maintain_total), 0)  AS maintain_total,
               COALESCE(SUM(sm.net_amount), 0)      AS net_amount
        FROM settle_master sm
        JOIN agent a        ON a.agent_id = sm.agent_id
        JOIN organization o ON o.org_id = a.org_id
        WHERE sm.settle_month = #{settleMonth}
        GROUP BY o.org_id, o.org_name, o.org_type
        ORDER BY net_amount DESC
    </sql>

    <select id="selectOrg" resultType="com.ga.core.vo.report.OrgReportRow">
        <include refid="orgSql"/>
    </select>
    <select id="selectOrgCursor" resultType="com.ga.core.vo.report.OrgReportRow" fetchSize="500">
        <include refid="orgSql"/>
    </select>

    <!-- 3. 환수위험 리포트 — 계약별 경과월 기준 환수율 적용 -->
    <sql id="chargebackRiskSql">
        WITH rl AS (
            SELECT contract_id,
                   agent_id,
                   MIN(policy_no)        AS policy_no,
                   MIN(company_code)     AS company_code,
                   MIN(settle_month)     AS recruit_month,
                   SUM(agent_amount)     AS recruit_amount
            FROM recruit_ledger
            GROUP BY contract_id, agent_id
        )
        SELECT rl.agent_id,
               a.agent_name,
               o.org_name,
               rl.policy_no,
               ic.company_name,
               rl.recruit_month,
               elapsed.months_elapsed,
               rl.recruit_amount,
               COALESCE(cg.chargeback_percent, 0)                                          AS chargeback_rate,
               ROUND(rl.recruit_amount * COALESCE(cg.chargeback_percent, 0) / 100.0)        AS estimated_chargeback,
               CASE WHEN COALESCE(cg.chargeback_percent, 0) >= 50 THEN 'HIGH'
                    WHEN COALESCE(cg.chargeback_percent, 0) >= 10 THEN 'MEDIUM'
                    ELSE 'LOW' END                                                          AS risk_level
        FROM rl
        JOIN agent a              ON a.agent_id = rl.agent_id
        LEFT JOIN organization o  ON o.org_id = a.org_id
        LEFT JOIN insurance_company ic ON ic.company_code = rl.company_code
        CROSS JOIN LATERAL (
            SELECT (EXTRACT(YEAR FROM CURRENT_DATE)::int * 12 + EXTRACT(MONTH FROM CURRENT_DATE)::int)
                 - (substr(rl.recruit_month, 1, 4)::int * 12 + substr(rl.recruit_month, 5, 2)::int) AS months_elapsed
        ) elapsed
        LEFT JOIN chargeback_grade cg
               ON cg.is_active = 'Y'
              AND elapsed.months_elapsed >= cg.months_from
              AND (cg.months_to IS NULL OR elapsed.months_elapsed &lt;= cg.months_to)
        ORDER BY estimated_chargeback DESC
    </sql>

    <select id="selectChargebackRisk" resultType="com.ga.core.vo.report.ChargebackRiskRow">
        <include refid="chargebackRiskSql"/>
    </select>
    <select id="selectChargebackRiskCursor" resultType="com.ga.core.vo.report.ChargebackRiskRow" fetchSize="500">
        <include refid="chargebackRiskSql"/>
    </select>

    <!-- 4. 사업소득 간이지급명세서(월별) — commission_statement(MONTHLY) 위 읽기전용 집계 -->
    <sql id="simplifiedPaymentSql">
        SELECT cs.agent_id,
               cs.settle_month,
               a.agent_name,
               a.resident_no,
               a.business_no,
               CASE a.tax_type
                    WHEN 'BUSINESS_INCOME' THEN '사업소득'
                    WHEN 'OTHER_INCOME'    THEN '기타소득'
                    ELSE COALESCE(a.tax_type, '사업소득') END  AS income_type_name,
               cs.gross_amount,
               cs.income_tax_amount,
               cs.local_tax_amount,
               cs.net_amount
        FROM commission_statement cs
        JOIN agent a ON a.agent_id = cs.agent_id
        WHERE cs.statement_type = 'MONTHLY'
          AND cs.settle_month   = #{settleMonth}
        ORDER BY a.agent_name, cs.agent_id
    </sql>

    <select id="selectSimplifiedPayment" resultType="com.ga.core.vo.report.SimplifiedPaymentRow">
        <include refid="simplifiedPaymentSql"/>
    </select>
    <select id="selectSimplifiedPaymentCursor" resultType="com.ga.core.vo.report.SimplifiedPaymentRow" fetchSize="500">
        <include refid="simplifiedPaymentSql"/>
    </select>

</mapper>
