agent14_ibnr.py

"""
ActuarialOS — Agent 14: Reserve Adequacy / IBNR
=================================================
Reads from silver_act_dev_triangles + silver_act_ibnr_reserves
Writes to gold_act_ibnr_estimates + gold_act_audit_log

Methods implemented:
  1. Chain Ladder (Volume-Weighted LDFs)
  2. Bornhuetter-Ferguson (ELR blend)
  3. Cape Cod (Experience-based ELR)
  4. Over-Dispersed Poisson (ODP Bootstrap)
  5. XGBoost ML (pattern recognition across triangle cells)
  6. Weighted ensemble with uncertainty quantification

ETL:  run_etl_triangles_to_silver()  — bronze → silver dev triangles
Train: train_agent14_model()
Run:   run_agent14(payload)           — single LOB/state/AY
Batch: run_agent14_batch(payload)     — full portfolio sweep
"""

import os, json, logging, uuid
from datetime import date, datetime

import numpy as np
import pandas as pd

log = logging.getLogger(__name__)

MODELS_DIR = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'models')
MODEL_PATH  = os.path.join(MODELS_DIR, 'agent14_ibnr.pkl')

# ── Volume-weighted LDF benchmarks by LOB (industry priors) ──
BENCHMARK_LDFS = {
    #        12→24  24→36  36→48  48→60  60→72  72→84  84→96  96→108 108→120
    'HO':   [1.450, 1.150, 1.060, 1.030, 1.015, 1.007, 1.003, 1.001, 1.000],
    'AUTO': [1.600, 1.200, 1.090, 1.040, 1.020, 1.009, 1.004, 1.001, 1.000],
    'CMP':  [1.700, 1.250, 1.120, 1.060, 1.030, 1.012, 1.005, 1.002, 1.001],
    'GL':   [2.100, 1.450, 1.200, 1.100, 1.050, 1.020, 1.008, 1.003, 1.001],
    'WC':   [1.900, 1.350, 1.160, 1.080, 1.040, 1.015, 1.006, 1.002, 1.001],
}

DEV_MONTHS = [12, 24, 36, 48, 60, 72, 84, 96, 108, 120]

# Target loss ratios (ELR priors for BF method)
ELR_PRIOR = {
    'HO': 0.62, 'AUTO': 0.68, 'CMP': 0.58, 'GL': 0.55, 'WC': 0.72,
}

FEATURE_COLS_14 = [
    'lob_enc', 'dev_month_enc', 'accident_year_norm',
    'log_cumulative', 'log_earned_premium',
    'pct_developed', 'ata_factor', 'atf_to_ult',
    'elr_prior', 'cc_elr_ratio',
    'tail_factor', 'ldf_12_24', 'ldf_24_36',
    'years_of_data', 'claim_count_log',
]

LOB_ENC_14 = {'HO': 0, 'AUTO': 1, 'CMP': 2, 'GL': 3, 'WC': 4}


# ══════════════════════════════════════════════════════════════
# DB HELPERS
# ══════════════════════════════════════════════════════════════

def _get_engine():
    from sqlalchemy import create_engine
    from sqlalchemy.pool import NullPool
    from urllib.parse import quote_plus as qp
    import os
    DB = dict(
        host     = os.environ.get('MYSQL_ADDON_HOST',     'btvbbpqhvnttzvptguj3-mysql.services.clever-cloud.com'),
        port     = int(os.environ.get('MYSQL_ADDON_PORT', '3306')),
        user     = os.environ.get('MYSQL_ADDON_USER',     'utenclk29u394u1j'),
        password = os.environ.get('MYSQL_ADDON_PASSWORD', 'QXFZTmUtPnXrKFqZKpLQ'),
        database = os.environ.get('MYSQL_ADDON_DB',       'btvbbpqhvnttzvptguj3'),
    )
    pwd = qp(DB['password'])
    return create_engine(
        f"mysql+pymysql://{DB['user']}:{pwd}@{DB['host']}:{DB['port']}/{DB['database']}?charset=utf8mb4",
        poolclass=NullPool, connect_args={"connect_timeout": 15}
    )


def _safe_json(obj):
    import math
    if isinstance(obj, dict):        return {k: _safe_json(v) for k, v in obj.items()}
    if isinstance(obj, list):        return [_safe_json(v) for v in obj]
    if isinstance(obj, float):       return None if (math.isnan(obj) or math.isinf(obj)) else round(obj, 6)
    if isinstance(obj, np.integer):  return int(obj)
    if isinstance(obj, np.floating):
        v = float(obj); return None if (math.isnan(v) or math.isinf(v)) else round(v, 6)
    if isinstance(obj, np.ndarray):  return obj.tolist()
    if isinstance(obj, (date, datetime)): return str(obj)
    return obj


# ══════════════════════════════════════════════════════════════
# CORE ACTUARIAL METHODS
# ══════════════════════════════════════════════════════════════

class Triangle:
    """
    Represents a single LOB/state development triangle.
    Rows = accident years, columns = dev months.
    """

    def __init__(self, lob: str, state: str, triangle_type: str = 'PAID_LOSS'):
        self.lob   = lob
        self.state = state
        self.ttype = triangle_type
        self.data: dict = {}       # {(ay, dev_mo): cumulative_value}
        self.premiums: dict = {}   # {ay: earned_premium}
        self.claims: dict = {}     # {ay: claim_count}

    def add_cell(self, ay: int, dev_mo: int, value: float,
                 premium: float = None, claims: int = None):
        self.data[(ay, dev_mo)] = value
        if premium: self.premiums[ay] = premium
        if claims:  self.claims[ay]   = claims

    @property
    def accident_years(self):
        return sorted(set(ay for ay, _ in self.data))

    @property
    def dev_periods(self):
        return sorted(set(d for _, d in self.data))

    def get(self, ay, dev):
        return self.data.get((ay, dev))

    def volume_weighted_ldfs(self) -> list:
        """Compute volume-weighted age-to-age factors."""
        devs = self.dev_periods
        ldfs = []
        for i in range(len(devs) - 1):
            d1, d2 = devs[i], devs[i + 1]
            num, den = 0.0, 0.0
            for ay in self.accident_years:
                v1 = self.get(ay, d1)
                v2 = self.get(ay, d2)
                if v1 and v2 and v1 > 0:
                    num += v2
                    den += v1
            ldfs.append(round(num / den, 6) if den > 0 else
                        BENCHMARK_LDFS.get(self.lob, BENCHMARK_LDFS['HO'])[i])
        return ldfs

    def cdfs_to_ultimate(self, ldfs: list) -> dict:
        """CDF from each dev period to ultimate (tail = 1.000)."""
        devs  = self.dev_periods
        cdfs  = {}
        cdfs[devs[-1]] = 1.000
        for i in range(len(devs) - 2, -1, -1):
            cdfs[devs[i]] = round(cdfs[devs[i + 1]] * ldfs[i], 6)
        return cdfs


def chain_ladder(tri: Triangle) -> dict:
    """
    Classic Chain Ladder / Development Method.
    Ultimate = Latest Diagonal × CDF to Ultimate
    IBNR = Ultimate - (Paid + Case)
    """
    ldfs = tri.volume_weighted_ldfs()
    cdfs = tri.cdfs_to_ultimate(ldfs)
    devs = tri.dev_periods

    results = {}
    for ay in tri.accident_years:
        # Find latest diagonal
        latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
        latest_val = tri.get(ay, latest_dev)
        cdf        = cdfs.get(latest_dev, 1.0)
        ultimate   = round(latest_val * cdf, 2)
        ibnr       = round(max(0, ultimate - latest_val), 2)
        results[ay] = dict(
            method='CHAIN_LADDER', ultimate=ultimate, ibnr=ibnr,
            latest_dev=latest_dev, latest_value=latest_val,
            cdf_applied=cdf, pct_developed=round(1/cdf, 5)
        )
    return results


def bornhuetter_ferguson(tri: Triangle, elr: float = None) -> dict:
    """
    Bornhuetter-Ferguson Method.
    Ultimate = Actual Reported + Expected Unreported
    Expected Unreported = EP × ELR × (1 - 1/CDF)
    """
    ldfs = tri.volume_weighted_ldfs()
    cdfs = tri.cdfs_to_ultimate(ldfs)
    devs = tri.dev_periods
    elr  = elr or ELR_PRIOR.get(tri.lob, 0.65)

    results = {}
    for ay in tri.accident_years:
        latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
        latest_val = tri.get(ay, latest_dev)
        cdf        = cdfs.get(latest_dev, 1.0)
        ep         = tri.premiums.get(ay, latest_val / elr)
        pct_undev  = round(1 - (1 / cdf), 5)
        exp_unrep  = ep * elr * pct_undev
        ultimate   = round(latest_val + exp_unrep, 2)
        ibnr       = round(max(0, ultimate - latest_val), 2)
        results[ay] = dict(
            method='BORNHUETTER_FERGUSON', ultimate=ultimate, ibnr=ibnr,
            elr_used=elr, pct_undeveloped=pct_undev,
            expected_unreported=round(exp_unrep, 2),
            cdf_applied=cdf
        )
    return results


def cape_cod(tri: Triangle) -> dict:
    """
    Cape Cod Method.
    ELR derived empirically from experience (not a priori).
    ELR_CC = Sum(Reported) / Sum(Used-up Premium)
    Used-up premium = EP × (1 - 1/CDF)
    """
    ldfs = tri.volume_weighted_ldfs()
    cdfs = tri.cdfs_to_ultimate(ldfs)
    devs = tri.dev_periods

    total_reported = 0.0
    total_used_up  = 0.0
    for ay in tri.accident_years:
        latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
        latest_val = tri.get(ay, latest_dev)
        cdf        = cdfs.get(latest_dev, 1.0)
        ep         = tri.premiums.get(ay, latest_val / ELR_PRIOR.get(tri.lob, 0.65))
        total_reported += latest_val
        total_used_up  += ep * (1 - 1/cdf)

    cc_elr = round(total_reported / total_used_up, 5) if total_used_up > 0 else ELR_PRIOR.get(tri.lob, 0.65)

    results = {}
    for ay in tri.accident_years:
        latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
        latest_val = tri.get(ay, latest_dev)
        cdf        = cdfs.get(latest_dev, 1.0)
        ep         = tri.premiums.get(ay, latest_val / cc_elr)
        pct_undev  = round(1 - (1/cdf), 5)
        exp_unrep  = ep * cc_elr * pct_undev
        ultimate   = round(latest_val + exp_unrep, 2)
        ibnr       = round(max(0, ultimate - latest_val), 2)
        results[ay] = dict(
            method='CAPE_COD', ultimate=ultimate, ibnr=ibnr,
            cc_elr=cc_elr, pct_undeveloped=pct_undev,
            expected_unreported=round(exp_unrep, 2)
        )
    return results


def odp_bootstrap(tri: Triangle, n_sims: int = 1000, seed: int = 14) -> dict:
    """
    Over-Dispersed Poisson Bootstrap.
    Simulates the full predictive distribution of IBNR.
    Returns mean + percentiles (P50, P75, P90, P99).
    """
    rng  = np.random.default_rng(seed)
    ldfs = tri.volume_weighted_ldfs()
    cdfs = tri.cdfs_to_ultimate(ldfs)
    devs = tri.dev_periods

    # Fitted incremental values (using CL ratios)
    fitted = {}
    for ay in tri.accident_years:
        for i, d in enumerate(devs):
            if tri.get(ay, d) is not None:
                if i == 0:
                    fitted[(ay, d)] = tri.get(ay, d)
                else:
                    prev = tri.get(ay, devs[i-1])
                    if prev:
                        fitted[(ay, d)] = prev * (ldfs[i-1] - 1)

    # Pearson residuals
    residuals = []
    for (ay, d), fv in fitted.items():
        actual = tri.get(ay, d)
        if actual and fv and fv > 0:
            r = (actual - fv) / (fv ** 0.5)
            residuals.append(r)
    residuals = np.array(residuals) if residuals else np.array([0.0])

    # Bootstrap
    sim_totals = []
    for _ in range(n_sims):
        total_ibnr = 0.0
        for ay in tri.accident_years:
            latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
            latest_val = tri.get(ay, latest_dev)
            cdf        = cdfs.get(latest_dev, 1.0)
            # Simulate future development
            noise = rng.choice(residuals) * (latest_val * (cdf-1)) ** 0.5 if cdf > 1 else 0
            sim_ibnr = max(0, latest_val * (cdf - 1) + noise)
            total_ibnr += sim_ibnr
        sim_totals.append(total_ibnr)

    sim_totals = np.array(sim_totals)
    per_ay = {}
    for ay in tri.accident_years:
        latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
        latest_val = tri.get(ay, latest_dev)
        cdf        = cdfs.get(latest_dev, 1.0)
        mean_ibnr  = round(max(0, latest_val * (cdf - 1)), 2)
        per_ay[ay] = dict(
            method='ODP', ultimate=round(latest_val * cdf, 2),
            ibnr=mean_ibnr, cdf_applied=cdf
        )

    return dict(
        per_ay        = per_ay,
        total_ibnr_mean = round(float(np.mean(sim_totals)), 2),
        total_ibnr_p50  = round(float(np.percentile(sim_totals, 50)), 2),
        total_ibnr_p75  = round(float(np.percentile(sim_totals, 75)), 2),
        total_ibnr_p90  = round(float(np.percentile(sim_totals, 90)), 2),
        total_ibnr_p99  = round(float(np.percentile(sim_totals, 99)), 2),
        cv              = round(float(np.std(sim_totals) / np.mean(sim_totals)), 4) if np.mean(sim_totals) > 0 else 0,
        n_sims          = n_sims,
    )


def ensemble_ibnr(cl: dict, bf: dict, cc: dict, odp_per_ay: dict,
                  ml_per_ay: dict = None, lob: str = 'HO') -> dict:
    """
    Weighted ensemble across methods.
    Weights reflect reliability by development age:
      - Early AYs (little data):  BF/CC weighted higher
      - Mature AYs (lots of data): CL weighted higher
    """
    results = {}
    for ay in cl:
        if ay not in bf or ay not in cc:
            continue
        cl_ult  = cl[ay]['ultimate']
        bf_ult  = bf[ay]['ultimate']
        cc_ult  = cc[ay]['ultimate']
        odp_ult = odp_per_ay.get(ay, {}).get('ultimate', cl_ult)
        ml_ult  = (ml_per_ay or {}).get(ay, {}).get('ultimate', cl_ult)

        pct_dev = cl[ay].get('pct_developed', 0.5)

        # Credibility weights — more developed = trust CL more
        if pct_dev >= 0.85:
            w_cl, w_bf, w_cc, w_odp, w_ml = 0.45, 0.20, 0.15, 0.10, 0.10
        elif pct_dev >= 0.60:
            w_cl, w_bf, w_cc, w_odp, w_ml = 0.30, 0.28, 0.20, 0.12, 0.10
        else:
            w_cl, w_bf, w_cc, w_odp, w_ml = 0.20, 0.32, 0.25, 0.13, 0.10

        if ml_per_ay is None:
            # Redistribute ML weight
            tot = w_cl + w_bf + w_cc + w_odp
            w_cl /= tot; w_bf /= tot; w_cc /= tot; w_odp /= tot; w_ml = 0

        ens_ult = (w_cl * cl_ult + w_bf * bf_ult + w_cc * cc_ult +
                   w_odp * odp_ult + w_ml * ml_ult)
        latest_val = cl[ay]['latest_value']
        ens_ibnr   = max(0, ens_ult - latest_val)

        results[ay] = dict(
            cl_ultimate   = round(cl_ult, 2),
            bf_ultimate   = round(bf_ult, 2),
            cc_ultimate   = round(cc_ult, 2),
            odp_ultimate  = round(odp_ult, 2),
            ml_ultimate   = round(ml_ult, 2) if ml_per_ay else None,
            ensemble_ultimate = round(ens_ult, 2),
            ibnr          = round(ens_ibnr, 2),
            weights       = dict(cl=w_cl, bf=w_bf, cc=w_cc, odp=w_odp, ml=w_ml),
            pct_developed = pct_dev,
            latest_value  = latest_val,
        )
    return results


# ══════════════════════════════════════════════════════════════
# ETL: BRONZE → SILVER TRIANGLES
# ══════════════════════════════════════════════════════════════

def run_etl_triangles_to_silver():
    """Aggregate bronze_act_triangles → silver_act_dev_triangles with LDFs."""
    from sqlalchemy import text
    eng = _get_engine()
    log.info("[AGENT14-ETL] bronze → silver triangles")

    with eng.connect() as conn:
        df = pd.read_sql("""
            SELECT lob, state_code, triangle_type, accident_year,
                   dev_month, AVG(cumulative_value) AS cumulative_value,
                   AVG(earned_premium) AS earned_premium,
                   AVG(claim_count) AS claim_count
            FROM bronze_act_triangles
            GROUP BY lob, state_code, triangle_type, accident_year, dev_month
            ORDER BY lob, state_code, triangle_type, accident_year, dev_month
        """, conn)

    if df.empty:
        log.warning("[AGENT14-ETL] No triangle data in bronze")
        return

    rows = []
    groups = df.groupby(['lob', 'state_code', 'triangle_type'])

    for (lob, state, ttype), grp in groups:
        # Build Triangle object
        tri = Triangle(lob, state, ttype)
        for _, r in grp.iterrows():
            tri.add_cell(
                int(r['accident_year']), int(r['dev_month']),
                float(r['cumulative_value']),
                premium=float(r['earned_premium']) if pd.notna(r['earned_premium']) else None,
                claims=int(r['claim_count']) if pd.notna(r['claim_count']) else None,
            )

        ldfs = tri.volume_weighted_ldfs()
        cdfs = tri.cdfs_to_ultimate(ldfs)
        devs = tri.dev_periods

        for (ay, dev_mo), cum_val in tri.data.items():
            idx     = devs.index(dev_mo) if dev_mo in devs else -1
            atf     = cdfs.get(dev_mo, 1.0)
            ata     = ldfs[idx] if 0 <= idx < len(ldfs) else 1.0
            prev_dev = devs[idx-1] if idx > 0 else None
            prev_val = tri.get(ay, prev_dev) if prev_dev else None
            incremental = round(cum_val - prev_val, 2) if prev_val else None

            # BF ultimate for this cell
            ep    = tri.premiums.get(ay)
            elr   = ELR_PRIOR.get(lob, 0.65)
            bf_ult= round(cum_val + (ep * elr * (1 - 1/atf)), 2) if ep and atf > 1 else round(cum_val * atf, 2)

            rows.append(dict(
                lob=lob, state_code=state, triangle_type=ttype,
                accident_year=ay, dev_month=dev_mo,
                cumulative_value=round(cum_val, 2),
                incremental_value=incremental,
                ata_factor=round(ata, 6),
                atf_factor=round(atf, 6),
                bornhuetter_ult=bf_ult,
                cl_ult=round(cum_val * atf, 2),
                pct_developed=round(1/atf, 5),
            ))

    silver_df = pd.DataFrame(rows).where(pd.notna(pd.DataFrame(rows)), other=None)
    with eng.begin() as conn:
        conn.execute(text("DELETE FROM silver_act_dev_triangles"))
        silver_df.to_sql('silver_act_dev_triangles', conn,
                         if_exists='append', index=False, method='multi', chunksize=500)

    log.info(f"[AGENT14-ETL] silver_act_dev_triangles: {len(silver_df)} rows")
    return {'triangle_rows': len(silver_df)}


# ══════════════════════════════════════════════════════════════
# ML FEATURE BUILDER + TRAINING
# ══════════════════════════════════════════════════════════════

def _build_features_14(lob: str, dev_month, ay: int,
                        cumulative: float, ep: float,
                        ldf_12_24: float, ldf_24_36: float,
                        atf: float, claim_count: int = 50) -> dict:
    dev_month = int(dev_month)
    latest_ay = 2024
    return {
        'lob_enc':          LOB_ENC_14.get(lob, 0),
        'dev_month_enc':    DEV_MONTHS.index(dev_month) if dev_month in DEV_MONTHS else 0,
        'accident_year_norm': round((ay - 2015) / 9, 4),
        'log_cumulative':   float(np.log(max(cumulative, 1))),
        'log_earned_premium': float(np.log(max(ep, 1))),
        'pct_developed':    round(1/atf if atf > 0 else 1.0, 5),
        'ata_factor':       ldf_12_24 if dev_month <= 24 else ldf_24_36,
        'atf_to_ult':       round(atf, 5),
        'elr_prior':        ELR_PRIOR.get(lob, 0.65),
        'cc_elr_ratio':     round(cumulative / max(ep * ELR_PRIOR.get(lob, 0.65), 1), 5),
        'tail_factor':      1.005 if lob in ('GL','WC') else 1.001,
        'ldf_12_24':        ldf_12_24,
        'ldf_24_36':        ldf_24_36,
        'years_of_data':    latest_ay - ay,
        'claim_count_log':  float(np.log(max(claim_count, 1))),
    }


def _generate_training_data_14(n=6000, seed=14):
    rng  = np.random.default_rng(seed)
    lobs = list(LOB_ENC_14.keys())
    rows = []

    for _ in range(n):
        lob   = rng.choice(lobs)
        ay    = int(rng.integers(2015, 2025))
        dev   = int(rng.choice(DEV_MONTHS))
        bench = BENCHMARK_LDFS.get(lob, BENCHMARK_LDFS['HO'])
        elr   = ELR_PRIOR[lob]
        ep    = float(rng.uniform(300000, 8000000))

        # True ultimate
        true_ult = ep * elr * float(rng.lognormal(0, 0.12))
        # CDF for this dev period
        idx = DEV_MONTHS.index(dev)
        cdf = 1.0
        for f in bench[idx:]: cdf *= f

        cum  = true_ult / cdf * float(rng.uniform(0.92, 1.08))
        cum  = max(cum, 1)
        ldf1 = bench[0] * float(rng.uniform(0.92, 1.08))
        ldf2 = bench[1] * float(rng.uniform(0.92, 1.08))
        cc   = max(1, int(rng.poisson(ep / 15000)))

        feats = _build_features_14(lob, dev, ay, cum, ep, ldf1, ldf2, cdf, cc)
        feats['target_ultimate'] = true_ult
        rows.append(feats)

    return pd.DataFrame(rows)


def train_agent14_model():
    """Train XGBoost to predict ultimate loss from triangle cell features."""
    import joblib
    from xgboost import XGBRegressor
    from sklearn.model_selection import train_test_split
    from sklearn.metrics import mean_absolute_error

    os.makedirs(MODELS_DIR, exist_ok=True)
    log.info("[AGENT14] Generating training data...")
    df = _generate_training_data_14(n=8000)
    X  = df[FEATURE_COLS_14]
    y  = df['target_ultimate']

    X_tr, X_te, y_tr, y_te = train_test_split(X, y, test_size=0.2, random_state=14)

    model = XGBRegressor(
        n_estimators=350, max_depth=6, learning_rate=0.04,
        subsample=0.80, colsample_bytree=0.80,
        min_child_weight=8, reg_alpha=0.2, reg_lambda=1.5,
        random_state=14, verbosity=0
    )
    model.fit(X_tr, y_tr, eval_set=[(X_te, y_te)], verbose=False)
    mae = mean_absolute_error(y_te, model.predict(X_te))
    mae_pct = mae / y_te.mean()
    log.info(f"[AGENT14] XGBoost MAE: {mae:,.0f} ({mae_pct:.2%} of mean)")

    bundle = dict(
        model=model, feature_cols=FEATURE_COLS_14,
        mae=mae, mae_pct=mae_pct, trained_at=str(date.today())
    )
    joblib.dump(bundle, MODEL_PATH)
    log.info(f"[AGENT14] Model saved → {MODEL_PATH}")
    return bundle


# ══════════════════════════════════════════════════════════════
# MAIN AGENT ENTRY POINTS
# ══════════════════════════════════════════════════════════════

def _build_triangle_from_db(eng, lob: str, state: str,
                             ttype: str = 'PAID_LOSS') -> Triangle:
    """Load a Triangle object from silver_act_dev_triangles."""
    with eng.connect() as conn:
        df = pd.read_sql("""
            SELECT accident_year, dev_month, cumulative_value,
                   pct_developed, atf_factor
            FROM silver_act_dev_triangles
            WHERE lob=%s AND state_code=%s AND triangle_type=%s
            ORDER BY accident_year, dev_month
        """, conn, params=(lob, state, ttype))

        premiums = pd.read_sql("""
            SELECT accident_year, SUM(earned_premium) AS ep
            FROM silver_act_loss_ratios
            WHERE lob=%s AND state_code=%s
            GROUP BY accident_year
        """, conn, params=(lob, state))

    tri = Triangle(lob, state, ttype)
    prem_map = dict(zip(premiums['accident_year'], premiums['ep'])) if not premiums.empty else {}

    for _, row in df.iterrows():
        tri.add_cell(
            int(row['accident_year']), int(row['dev_month']),
            float(row['cumulative_value']),
            premium=prem_map.get(int(row['accident_year']))
        )
    return tri


def run_agent14(payload: dict) -> dict:
    """
    Estimate IBNR for a single LOB/state combination.

    payload keys:
        lob, state_code, [eval_date], [run_id],
        [triangle_data: list of {ay, dev_month, value, premium}]

    Returns ensemble IBNR with all method outputs + uncertainty.
    """
    from sqlalchemy import text

    lob     = str(payload.get('lob', 'HO'))
    state   = str(payload.get('state_code', 'TX'))
    run_id  = payload.get('run_id') or f"AG14-{uuid.uuid4().hex[:12].upper()}"
    eval_dt = payload.get('eval_date') or str(date.today())

    log.info(f"[AGENT14] IBNR estimate {lob}/{state} run={run_id}")

    eng = _get_engine()

    # Build triangle — from payload override or DB
    if payload.get('triangle_data'):
        tri = Triangle(lob, state, 'PAID_LOSS')
        for cell in payload['triangle_data']:
            tri.add_cell(
                int(cell['ay']), int(cell['dev_month']),
                float(cell['value']),
                premium=cell.get('premium'),
            )
    else:
        try:
            tri = _build_triangle_from_db(eng, lob, state, 'PAID_LOSS')
        except Exception as e:
            log.warning(f"[AGENT14] DB triangle load failed: {e} — using synthetic fallback")
            tri = _synthetic_triangle(lob, state)

    if not tri.accident_years:
        return {'error': f'No triangle data for {lob}/{state}', 'run_id': run_id}

    # ── Run all actuarial methods ──────────────────────────────
    cl_res  = chain_ladder(tri)
    bf_res  = bornhuetter_ferguson(tri)
    cc_res  = cape_cod(tri)
    odp_res = odp_bootstrap(tri, n_sims=2000)

    # ── ML prediction per AY ──────────────────────────────────
    ml_per_ay = None
    try:
        import joblib, shap as shap_lib
        bundle  = joblib.load(MODEL_PATH)
        model   = bundle['model']
        ldfs    = tri.volume_weighted_ldfs()
        cdfs    = tri.cdfs_to_ultimate(ldfs)
        devs    = tri.dev_periods
        bench   = BENCHMARK_LDFS.get(lob, BENCHMARK_LDFS['HO'])
        ml_rows = []
        ml_ays  = []

        for ay in tri.accident_years:
            latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
            cum = tri.get(ay, latest_dev)
            ep  = tri.premiums.get(ay, cum / ELR_PRIOR.get(lob, 0.65))
            atf = cdfs.get(latest_dev, 1.0)
            feats = _build_features_14(
                lob, latest_dev, ay, cum, ep,
                bench[0], bench[1], atf
            )
            ml_rows.append(feats)
            ml_ays.append(ay)

        X_ml  = pd.DataFrame(ml_rows)[bundle['feature_cols']]
        preds = bundle['model'].predict(X_ml)

        ml_per_ay = {}
        for ay, pred_ult in zip(ml_ays, preds):
            latest_dev = max(d for d in devs if tri.get(ay, d) is not None)
            latest_val = tri.get(ay, latest_dev)
            ml_per_ay[ay] = dict(
                ultimate=round(float(pred_ult), 2),
                ibnr=round(max(0, float(pred_ult) - latest_val), 2)
            )

        # SHAP on first AY for explainability
        explainer  = shap_lib.TreeExplainer(model)
        shap_vals  = explainer.shap_values(X_ml)
        shap_mean  = {col: round(float(np.mean(np.abs(shap_vals[:, i]))), 6)
                      for i, col in enumerate(bundle['feature_cols'])}
        top_shap   = sorted(shap_mean.items(), key=lambda x: x[1], reverse=True)[:5]

    except Exception as e:
        log.warning(f"[AGENT14] ML unavailable: {e}")
        shap_mean, top_shap = {}, []

    # ── Ensemble ──────────────────────────────────────────────
    ens = ensemble_ibnr(cl_res, bf_res, cc_res, odp_res['per_ay'], ml_per_ay, lob)

    # ── Aggregate totals ──────────────────────────────────────
    total_ibnr_ens   = sum(v['ibnr'] for v in ens.values())
    total_ibnr_cl    = sum(v['ibnr'] for v in cl_res.values())
    total_ibnr_bf    = sum(v['ibnr'] for v in bf_res.values())
    total_paid       = sum(tri.get(ay, max(d for d in tri.dev_periods if tri.get(ay,d) is not None))
                           for ay in tri.accident_years)
    total_ep         = sum(tri.premiums.values()) if tri.premiums else 0

    # Uncertainty from ODP bootstrap
    cv       = odp_res['cv']
    p75_tot  = round(total_ibnr_ens * (1 + cv * 0.674), 2)
    p90_tot  = round(total_ibnr_ens * (1 + cv * 1.282), 2)
    p99_tot  = round(total_ibnr_ens * (1 + cv * 2.326), 2)

    result = dict(
        run_id           = run_id,
        lob              = lob,
        state_code       = state,
        eval_date        = eval_dt,
        total_ibnr       = round(total_ibnr_ens, 2),
        total_ibnr_cl    = round(total_ibnr_cl, 2),
        total_ibnr_bf    = round(total_ibnr_bf, 2),
        total_paid       = round(total_paid, 2),
        total_ep         = round(total_ep, 2),
        ibnr_cv          = cv,
        ibnr_p75         = p75_tot,
        ibnr_p90         = p90_tot,
        ibnr_p99         = p99_tot,
        odp_p75          = odp_res['total_ibnr_p75'],
        odp_p90          = odp_res['total_ibnr_p90'],
        odp_p99          = odp_res['total_ibnr_p99'],
        by_accident_year = ens,
        shap_summary     = shap_mean,
        top_drivers      = [{'feature': k, 'importance': v} for k, v in top_shap],
        accident_years   = sorted(tri.accident_years),
    )

    # ── Persist to gold ───────────────────────────────────────
    try:
        _persist_gold_14(result, eng)
    except Exception as e:
        log.warning(f"[AGENT14] Gold persist failed: {e}")

    return _safe_json(result)


def _synthetic_triangle(lob: str, state: str) -> Triangle:
    """Fallback: generate a synthetic triangle when DB has no data."""
    rng   = np.random.default_rng(int.from_bytes(lob.encode(), 'big') % 9999)
    bench = BENCHMARK_LDFS.get(lob, BENCHMARK_LDFS['HO'])
    elr   = ELR_PRIOR.get(lob, 0.65)
    tri   = Triangle(lob, state, 'PAID_LOSS')

    for ay in range(2015, 2025):
        ep     = float(rng.uniform(500000, 3000000))
        ult    = ep * elr * float(rng.lognormal(0, 0.10))
        max_dev = min((2024 - ay + 1) * 12, 120)
        cdf    = 1.0
        for i, dev in enumerate(DEV_MONTHS):
            if dev > max_dev: break
            for f in bench[i:]: cdf *= f
            cum = ult / cdf * float(rng.uniform(0.95, 1.05))
            tri.add_cell(ay, dev, max(1, cum), premium=ep)
            cdf = 1.0

    return tri


def _persist_gold_14(result: dict, eng):
    """Write IBNR estimates to gold tables."""
    from sqlalchemy import text

    run_id  = result['run_id']
    lob     = result['lob']
    state   = result['state_code']
    eval_dt = result['eval_date']

    with eng.begin() as conn:
        for ay, ens in result['by_accident_year'].items():
            conn.execute(text("""
                INSERT INTO gold_act_ibnr_estimates (
                    run_id, lob, state_code, accident_year, eval_date,
                    cl_ultimate, bf_ultimate, odp_ultimate, ensemble_ultimate,
                    ibnr_estimate, ibnr_cv, ibnr_p75, ibnr_p90, ibnr_p99,
                    paid_to_date, ensemble_weights_json, model_version
                ) VALUES (
                    :run_id, :lob, :state, :ay, :eval_dt,
                    :cl_ult, :bf_ult, :odp_ult, :ens_ult,
                    :ibnr, :cv, :p75, :p90, :p99,
                    :paid, :weights, '1.0'
                )
            """), dict(
                run_id=run_id, lob=lob, state=state, ay=ay, eval_dt=eval_dt,
                cl_ult=ens.get('cl_ultimate'),
                bf_ult=ens.get('bf_ultimate'),
                odp_ult=ens.get('odp_ultimate'),
                ens_ult=ens.get('ensemble_ultimate'),
                ibnr=ens.get('ibnr'),
                cv=result['ibnr_cv'],
                p75=result['ibnr_p75'] / max(len(result['by_accident_year']), 1),
                p90=result['ibnr_p90'] / max(len(result['by_accident_year']), 1),
                p99=result['ibnr_p99'] / max(len(result['by_accident_year']), 1),
                paid=ens.get('latest_value'),
                weights=json.dumps(_safe_json(ens.get('weights', {}))),
            ))

        # Audit log
        conn.execute(text("""
            INSERT INTO gold_act_audit_log (
                run_id, agent_name, agent_version, lob, state_code,
                analysis_date, function_performed,
                input_summary_json, output_summary_json,
                decision_factors_json, assumptions_json, confidence_score
            ) VALUES (
                :run_id, 'agent14_ibnr', '1.0', :lob, :state,
                :adate, 'IBNR_RESERVE_ESTIMATE',
                :inp, :out, :factors, :assump, :conf
            )
        """), dict(
            run_id=run_id, lob=lob, state=state,
            adate=date.today(),
            inp=json.dumps(_safe_json({
                'accident_years': result['accident_years'],
                'total_paid':     result['total_paid'],
                'total_ep':       result['total_ep'],
            })),
            out=json.dumps(_safe_json({
                'total_ibnr':  result['total_ibnr'],
                'ibnr_p90':    result['ibnr_p90'],
                'ibnr_cv':     result['ibnr_cv'],
            })),
            factors=json.dumps(_safe_json(result.get('top_drivers', []))),
            assump=json.dumps({
                'methods': ['CHAIN_LADDER','BF','CAPE_COD','ODP','XGBOOST'],
                'odp_sims': 2000,
                'tail_factor': 'LOB-specific',
                'elr_priors': ELR_PRIOR,
            }),
            conf=round(max(0.5, 1 - result['ibnr_cv']), 4),
        ))

    log.info(f"[AGENT14] Gold persisted — run_id={run_id}")


def run_agent14_batch(payload: dict = None) -> dict:
    """
    Run Agent 14 across all LOB/state combinations in silver triangles.
    """
    payload = payload or {}
    run_id  = payload.get('run_id') or f"AG14-BATCH-{uuid.uuid4().hex[:8].upper()}"
    log.info(f"[AGENT14-BATCH] Starting run={run_id}")

    try:
        eng = _get_engine()
        with eng.connect() as conn:
            combos = pd.read_sql("""
                SELECT DISTINCT lob, state_code
                FROM silver_act_dev_triangles
                ORDER BY lob, state_code
            """, conn)
    except Exception as e:
        return {'error': str(e), 'run_id': run_id}

    if combos.empty:
        return {'error': 'No silver triangle data. Run ETL first.', 'run_id': run_id}

    results, errors = [], []
    for _, row in combos.iterrows():
        if payload.get('lob_filter') and row['lob'] != payload['lob_filter']:
            continue
        try:
            r = run_agent14({'lob': row['lob'], 'state_code': row['state_code'], 'run_id': run_id})
            results.append(r)
        except Exception as e:
            errors.append({'lob': row['lob'], 'state': row['state_code'], 'error': str(e)})

    total_ibnr = sum(r.get('total_ibnr', 0) for r in results)
    total_p90  = sum(r.get('ibnr_p90', 0) for r in results)
    avg_cv     = np.mean([r.get('ibnr_cv', 0) for r in results]) if results else 0

    summary = dict(
        run_id=run_id, total_combos=len(results), errors=len(errors),
        total_ibnr=round(total_ibnr, 2),
        total_ibnr_p90=round(total_p90, 2),
        avg_cv=round(float(avg_cv), 4),
    )
    log.info(f"[AGENT14-BATCH] Complete: {summary}")

    return {'run_id': run_id, 'summary': summary,
            'results': _safe_json(results), 'errors': errors}