-- V76: 13M/25M 유지율 이상치 탐지 + 차월 KPI 예측
-- mv_retention_rate (V37) 값을 input 으로 직전 N개월 평균 대비 편차를 계산한다.

-- ============================================================
-- 1. retention_anomaly_alert: 유지율 이상 알림
-- ============================================================
CREATE TABLE retention_anomaly_alert (
    alert_id        BIGSERIAL     PRIMARY KEY,
    target_type     VARCHAR(10)   NOT NULL,                  -- AGENT / ORG / ALL
    target_id       BIGINT,                                  -- target_type=ALL 이면 NULL
    alert_month     VARCHAR(7)    NOT NULL,                  -- YYYY-MM
    retention_band  VARCHAR(5)    NOT NULL,                  -- 13M / 25M
    expected_rate   NUMERIC(7,4),                            -- 직전 3개월 평균
    actual_rate     NUMERIC(7,4),                            -- 현재 월
    deviation       NUMERIC(7,4),                            -- actual - expected
    severity        VARCHAR(5)    NOT NULL,                  -- LOW / MED / HIGH
    status          VARCHAR(10)   NOT NULL DEFAULT 'NEW',    -- NEW / REVIEWED / RESOLVED
    note            VARCHAR(300),
    detected_at     TIMESTAMP     NOT NULL DEFAULT now(),
    reviewed_at     TIMESTAMP,
    reviewed_by     BIGINT,
    CONSTRAINT chk_raa_type     CHECK (target_type    IN ('AGENT','ORG','ALL')),
    CONSTRAINT chk_raa_band     CHECK (retention_band IN ('13M','25M')),
    CONSTRAINT chk_raa_severity CHECK (severity       IN ('LOW','MED','HIGH')),
    CONSTRAINT chk_raa_status   CHECK (status         IN ('NEW','REVIEWED','RESOLVED'))
);
COMMENT ON TABLE  retention_anomaly_alert IS '13M/25M 유지율 이상치 알림';

-- target_id NULL 허용을 위한 부분 UNIQUE (ALL 스코프) / 일반 UNIQUE
CREATE UNIQUE INDEX uq_raa_target
    ON retention_anomaly_alert(target_type, target_id, alert_month, retention_band)
    WHERE target_id IS NOT NULL;
CREATE UNIQUE INDEX uq_raa_all
    ON retention_anomaly_alert(target_type, alert_month, retention_band)
    WHERE target_id IS NULL;

CREATE INDEX idx_raa_month    ON retention_anomaly_alert(alert_month);
CREATE INDEX idx_raa_severity ON retention_anomaly_alert(severity, status);

-- ============================================================
-- 2. forecast_kpi_monthly: 차월 KPI 예측 (단순 이동평균)
-- ============================================================
CREATE TABLE forecast_kpi_monthly (
    forecast_id     BIGSERIAL     PRIMARY KEY,
    forecast_month  VARCHAR(7)    NOT NULL,                   -- 예측 대상 월 YYYY-MM
    target_org_id   BIGINT,                                   -- NULL = 전사
    metric_code     VARCHAR(30)   NOT NULL,                   -- MONTHLY_PREMIUM / MONTHLY_PAYOUT / RETENTION_13M / RETENTION_25M
    forecast_value  NUMERIC(18,4),
    lower_bound     NUMERIC(18,4),                            -- 평균 - 1σ
    upper_bound     NUMERIC(18,4),                            -- 평균 + 1σ
    method          VARCHAR(20)   NOT NULL DEFAULT 'SMA3',    -- SMA3 / SMA6 / NAIVE
    base_period     VARCHAR(30),                              -- 산출 기준 (예: 2025-01~2025-03)
    sample_count    INTEGER,                                  -- 산출에 사용된 표본수
    generated_at    TIMESTAMP     NOT NULL DEFAULT now()
);
COMMENT ON TABLE  forecast_kpi_monthly IS '차월 KPI 예측 (이동평균 기반)';
COMMENT ON COLUMN forecast_kpi_monthly.metric_code IS 'MONTHLY_PREMIUM/MONTHLY_PAYOUT/RETENTION_13M/RETENTION_25M';

CREATE INDEX idx_fkm_month  ON forecast_kpi_monthly(forecast_month);
CREATE INDEX idx_fkm_metric ON forecast_kpi_monthly(metric_code);

-- 부분 UNIQUE: 전사(NULL) / 조직별
CREATE UNIQUE INDEX uq_fkm_org
    ON forecast_kpi_monthly(forecast_month, target_org_id, metric_code)
    WHERE target_org_id IS NOT NULL;
CREATE UNIQUE INDEX uq_fkm_all
    ON forecast_kpi_monthly(forecast_month, metric_code)
    WHERE target_org_id IS NULL;
