-- V37: KPI 대시보드 Materialized View 4종
--
-- REFRESH 전략:
--   CONCURRENTLY 옵션으로 락 없이 갱신 (UNIQUE INDEX 필수).
--   야간 배치(ga-batch)에서 매일 00:30 순차 REFRESH.
--   명령: REFRESH MATERIALIZED VIEW CONCURRENTLY <mv_name>;
--
-- 주의: CONCURRENTLY 는 트랜잭션 안에서 실행 불가.
--       배치에서 Connection.setAutoCommit(true) 상태로 호출할 것.

-- ============================================================
-- 1. mv_monthly_summary — 월별 정산 요약
--    단위: 정산월(yyyymm) 전체 합산
-- ============================================================

CREATE MATERIALIZED VIEW mv_monthly_summary AS
SELECT
    sm.settle_month                                         AS yyyymm,
    COUNT(sm.settle_id)                                     AS agent_count,
    COALESCE(SUM(sm.recruit_total),   0)                    AS total_recruit,
    COALESCE(SUM(sm.maintain_total),  0)                    AS total_maintain,
    COALESCE(SUM(sm.override_total),  0)                    AS total_override,
    COALESCE(SUM(sm.chargeback_total),0)                    AS total_chargeback,
    COALESCE(SUM(sm.gross_amount),    0)                    AS total_gross,
    COALESCE(SUM(sm.tax_amount),      0)                    AS total_tax,
    COALESCE(SUM(sm.net_amount),      0)                    AS total_net,
    -- 인센티브 합계 (해당 월 PAID 기준)
    COALESCE((
        SELECT SUM(ip.incentive_amount)
        FROM   incentive_payment ip
        WHERE  ip.settle_month = sm.settle_month
          AND  ip.status = 'PAID'
    ), 0)                                                   AS total_incentive,
    NOW()                                                   AS refreshed_at
FROM settle_master sm
WHERE sm.status IN ('CONFIRMED', 'PAID')
GROUP BY sm.settle_month
WITH DATA;

COMMENT ON MATERIALIZED VIEW mv_monthly_summary IS '월별 정산 요약 KPI — REFRESH CONCURRENTLY 야간 배치';
COMMENT ON COLUMN mv_monthly_summary.yyyymm         IS '정산년월 (YYYYMM)';
COMMENT ON COLUMN mv_monthly_summary.agent_count    IS '해당 월 확정/지급 완료 설계사 수';
COMMENT ON COLUMN mv_monthly_summary.total_gross    IS '총 수수료 (세전)';
COMMENT ON COLUMN mv_monthly_summary.total_tax      IS '총 원천세';
COMMENT ON COLUMN mv_monthly_summary.total_net      IS '총 순지급액 (세후)';
COMMENT ON COLUMN mv_monthly_summary.total_incentive IS '해당 월 지급 완료 시책 합계';
COMMENT ON COLUMN mv_monthly_summary.refreshed_at   IS 'MATERIALIZED VIEW 마지막 갱신 시각';

-- CONCURRENTLY REFRESH 를 위한 UNIQUE INDEX (필수)
CREATE UNIQUE INDEX idx_mv_monthly_summary_pk ON mv_monthly_summary(yyyymm);

-- ============================================================
-- 2. mv_org_summary — 조직별 월별 요약
--    단위: org_id + yyyymm
-- ============================================================

CREATE MATERIALIZED VIEW mv_org_summary AS
SELECT
    a.org_id,
    o.org_name,
    o.org_type,
    sm.settle_month                                         AS yyyymm,
    COUNT(DISTINCT sm.agent_id)                             AS agent_count,
    COALESCE(SUM(sm.recruit_total),   0)                    AS total_recruit,
    COALESCE(SUM(sm.maintain_total),  0)                    AS total_maintain,
    COALESCE(SUM(sm.override_total),  0)                    AS total_override,
    COALESCE(SUM(sm.chargeback_total),0)                    AS total_chargeback,
    COALESCE(SUM(sm.gross_amount),    0)                    AS total_gross,
    COALESCE(SUM(sm.net_amount),      0)                    AS total_net,
    NOW()                                                   AS refreshed_at
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.status IN ('CONFIRMED', 'PAID')
GROUP BY a.org_id, o.org_name, o.org_type, sm.settle_month
WITH DATA;

COMMENT ON MATERIALIZED VIEW mv_org_summary IS '조직별 월별 요약 KPI — REFRESH CONCURRENTLY 야간 배치';
COMMENT ON COLUMN mv_org_summary.org_id         IS '조직 ID';
COMMENT ON COLUMN mv_org_summary.yyyymm         IS '정산년월 (YYYYMM)';
COMMENT ON COLUMN mv_org_summary.agent_count    IS '해당 조직의 월 확정/지급 설계사 수';
COMMENT ON COLUMN mv_org_summary.total_gross    IS '조직 소속 설계사 총 수수료 합계 (세전)';
COMMENT ON COLUMN mv_org_summary.total_net      IS '조직 소속 설계사 총 순지급액 합계';

CREATE UNIQUE INDEX idx_mv_org_summary_pk ON mv_org_summary(org_id, yyyymm);
CREATE INDEX idx_mv_org_summary_month     ON mv_org_summary(yyyymm);

-- ============================================================
-- 3. mv_retention — 계약 유지율 (13M / 25M)
--    단위: contract_month (계약 기준월) + carrier_id
--    유지율 = 기준월 이후 N개월 시점에 유효한 계약 비율
-- ============================================================

CREATE MATERIALIZED VIEW mv_retention AS
WITH base AS (
    -- 계약 기준월별 원수 계약 집계
    SELECT
        TO_CHAR(c.contract_date, 'YYYYMM')         AS contract_month,
        p.company_id                                AS carrier_id,
        COUNT(c.contract_id)                        AS total_contracts
    FROM contract c
    JOIN product  p ON p.product_id = c.product_id
    WHERE c.status != 'TERMINATE'
    GROUP BY TO_CHAR(c.contract_date, 'YYYYMM'), p.company_id
),
m13 AS (
    -- 13개월 시점 유지 계약 (lapse_month > 12 또는 아직 실효 없음)
    SELECT
        TO_CHAR(c.contract_date, 'YYYYMM')         AS contract_month,
        p.company_id                                AS carrier_id,
        COUNT(c.contract_id)                        AS retained_13m
    FROM contract c
    JOIN product  p ON p.product_id = c.product_id
    WHERE c.status != 'TERMINATE'
      AND (
          -- 실효 데이터 없거나 실효 경과월 > 12
          NOT EXISTS (
              SELECT 1 FROM chargeback cb
              WHERE cb.contract_id = c.contract_id
                AND cb.lapse_month IS NOT NULL
                AND cb.lapse_month <= 12
          )
      )
    GROUP BY TO_CHAR(c.contract_date, 'YYYYMM'), p.company_id
),
m25 AS (
    -- 25개월 시점 유지 계약 (lapse_month > 24 또는 실효 없음)
    SELECT
        TO_CHAR(c.contract_date, 'YYYYMM')         AS contract_month,
        p.company_id                                AS carrier_id,
        COUNT(c.contract_id)                        AS retained_25m
    FROM contract c
    JOIN product  p ON p.product_id = c.product_id
    WHERE c.status != 'TERMINATE'
      AND (
          NOT EXISTS (
              SELECT 1 FROM chargeback cb
              WHERE cb.contract_id = c.contract_id
                AND cb.lapse_month IS NOT NULL
                AND cb.lapse_month <= 24
          )
      )
    GROUP BY TO_CHAR(c.contract_date, 'YYYYMM'), p.company_id
)
SELECT
    b.contract_month,
    b.carrier_id,
    ic.company_name                                                 AS carrier_name,
    b.total_contracts,
    COALESCE(m13.retained_13m, 0)                                   AS retained_13m,
    COALESCE(m25.retained_25m, 0)                                   AS retained_25m,
    -- 13개월 유지율 %
    CASE WHEN b.total_contracts > 0
         THEN ROUND(COALESCE(m13.retained_13m, 0) * 100.0 / b.total_contracts, 2)
         ELSE 0
    END                                                             AS retention_rate_13m,
    -- 25개월 유지율 %
    CASE WHEN b.total_contracts > 0
         THEN ROUND(COALESCE(m25.retained_25m, 0) * 100.0 / b.total_contracts, 2)
         ELSE 0
    END                                                             AS retention_rate_25m,
    NOW()                                                           AS refreshed_at
FROM base b
JOIN  insurance_company ic ON ic.company_id = b.carrier_id
LEFT JOIN m13 ON m13.contract_month = b.contract_month AND m13.carrier_id = b.carrier_id
LEFT JOIN m25 ON m25.contract_month = b.contract_month AND m25.carrier_id = b.carrier_id
WITH DATA;

COMMENT ON MATERIALIZED VIEW mv_retention IS '계약 유지율 KPI (13M/25M) — REFRESH CONCURRENTLY 야간 배치';
COMMENT ON COLUMN mv_retention.contract_month       IS '계약 기준월 (YYYYMM)';
COMMENT ON COLUMN mv_retention.carrier_id           IS '보험사 ID';
COMMENT ON COLUMN mv_retention.total_contracts      IS '기준월 전체 유효 계약 수';
COMMENT ON COLUMN mv_retention.retained_13m         IS '13개월 시점 유지 계약 수';
COMMENT ON COLUMN mv_retention.retained_25m         IS '25개월 시점 유지 계약 수';
COMMENT ON COLUMN mv_retention.retention_rate_13m   IS '13개월 유지율 (%)';
COMMENT ON COLUMN mv_retention.retention_rate_25m   IS '25개월 유지율 (%)';

CREATE UNIQUE INDEX idx_mv_retention_pk   ON mv_retention(contract_month, carrier_id);
CREATE INDEX idx_mv_retention_month       ON mv_retention(contract_month);

-- ============================================================
-- 4. mv_cumulative — 설계사/조직별 누계
--    단위: agent_id 또는 org_id (두 레벨 모두 포함)
-- ============================================================

CREATE MATERIALIZED VIEW mv_cumulative AS
-- 설계사 레벨 누계
SELECT
    'AGENT'                                         AS level_type,
    a.agent_id                                      AS entity_id,
    a.agent_name                                    AS entity_name,
    a.org_id                                        AS org_id,
    o.org_name                                      AS org_name,
    MIN(sm.settle_month)                            AS first_settle_month,
    MAX(sm.settle_month)                            AS last_settle_month,
    COUNT(sm.settle_id)                             AS settle_month_count,
    COALESCE(SUM(sm.gross_amount),     0)           AS cum_gross,
    COALESCE(SUM(sm.net_amount),       0)           AS cum_net,
    COALESCE(SUM(sm.chargeback_total), 0)           AS cum_chargeback,
    COALESCE(SUM(sm.override_total),   0)           AS cum_override,
    -- 시책 누계
    COALESCE((
        SELECT SUM(ip.incentive_amount)
        FROM   incentive_payment ip
        WHERE  ip.agent_id = a.agent_id
          AND  ip.status = 'PAID'
    ), 0)                                           AS cum_incentive,
    NOW()                                           AS refreshed_at
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.status IN ('CONFIRMED', 'PAID')
GROUP BY a.agent_id, a.agent_name, a.org_id, o.org_name

UNION ALL

-- 조직 레벨 누계 (소속 설계사 합산)
SELECT
    'ORG'                                           AS level_type,
    o.org_id                                        AS entity_id,
    o.org_name                                      AS entity_name,
    o.org_id                                        AS org_id,
    o.org_name                                      AS org_name,
    MIN(sm.settle_month)                            AS first_settle_month,
    MAX(sm.settle_month)                            AS last_settle_month,
    COUNT(DISTINCT sm.settle_month)                 AS settle_month_count,
    COALESCE(SUM(sm.gross_amount),     0)           AS cum_gross,
    COALESCE(SUM(sm.net_amount),       0)           AS cum_net,
    COALESCE(SUM(sm.chargeback_total), 0)           AS cum_chargeback,
    COALESCE(SUM(sm.override_total),   0)           AS cum_override,
    COALESCE((
        SELECT SUM(ip.incentive_amount)
        FROM   incentive_payment ip
        JOIN   agent ia ON ia.agent_id = ip.agent_id
        WHERE  ia.org_id = o.org_id
          AND  ip.status = 'PAID'
    ), 0)                                           AS cum_incentive,
    NOW()                                           AS refreshed_at
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.status IN ('CONFIRMED', 'PAID')
GROUP BY o.org_id, o.org_name
WITH DATA;

COMMENT ON MATERIALIZED VIEW mv_cumulative IS '설계사/조직 누계 수수료·환수 KPI — REFRESH CONCURRENTLY 야간 배치';
COMMENT ON COLUMN mv_cumulative.level_type          IS 'AGENT=설계사 레벨 / ORG=조직 레벨';
COMMENT ON COLUMN mv_cumulative.entity_id           IS 'level_type=AGENT이면 agent_id, ORG이면 org_id';
COMMENT ON COLUMN mv_cumulative.cum_gross           IS '누적 총 수수료 (세전)';
COMMENT ON COLUMN mv_cumulative.cum_net             IS '누적 순지급액 (세후)';
COMMENT ON COLUMN mv_cumulative.cum_chargeback      IS '누적 환수 합계';
COMMENT ON COLUMN mv_cumulative.cum_incentive       IS '누적 시책 지급액';
COMMENT ON COLUMN mv_cumulative.refreshed_at        IS 'MATERIALIZED VIEW 마지막 갱신 시각';

-- CONCURRENTLY 를 위한 UNIQUE INDEX: level_type + entity_id 조합
CREATE UNIQUE INDEX idx_mv_cumulative_pk    ON mv_cumulative(level_type, entity_id);
CREATE INDEX idx_mv_cumulative_org          ON mv_cumulative(org_id, level_type);
