-- V15: 통계/리포트용 뷰

-- 설계사별 월 실적 요약
CREATE OR REPLACE VIEW v_agent_monthly_summary AS
SELECT
    sm.agent_id,
    a.agent_name,
    o.org_id,
    o.org_name,
    g.grade_name,
    sm.settle_month,
    sm.recruit_total,
    sm.maintain_total,
    sm.exception_plus,
    sm.exception_minus,
    sm.override_total,
    sm.chargeback_total,
    sm.gross_amount,
    sm.tax_amount,
    sm.net_amount,
    sm.status
FROM settle_master sm
JOIN agent a            ON a.agent_id = sm.agent_id
LEFT JOIN organization o ON o.org_id = a.org_id
LEFT JOIN grade g        ON g.grade_id = a.grade_id;

-- 조직별 월 정산 합계
CREATE OR REPLACE VIEW v_org_settle_summary AS
SELECT
    a.org_id,
    o.org_name,
    o.org_type,
    sm.settle_month,
    COUNT(sm.settle_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.gross_amount),   0) AS gross_amount,
    COALESCE(SUM(sm.tax_amount),     0) AS tax_amount,
    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
GROUP BY a.org_id, o.org_name, o.org_type, sm.settle_month;

-- 환수 위험 계약 (실효 + 환수 발생)
CREATE OR REPLACE VIEW v_chargeback_risk AS
SELECT
    cb.cb_id,
    cb.contract_id,
    cb.agent_id,
    a.agent_name,
    o.org_name,
    cb.policy_no,
    cb.lapse_month,
    cb.cb_rate,
    cb.cb_amount,
    cb.paid_amount,
    cb.remain_amount,
    cb.settle_month,
    cb.status
FROM chargeback cb
JOIN agent a            ON a.agent_id = cb.agent_id
LEFT JOIN organization o ON o.org_id = a.org_id;
