""" ══════════════════════════════════════════════════════════════════════════════ AGENT 4 — ML Pricing Agent (Step 4 of 5) ══════════════════════════════════════════════════════════════════════════════ PURPOSE : Calculate final premium using XGBoost + GLM ensemble. Reads UW approved decision from Agent 3. Produces premium, confidence interval, and SHAP explanation. INPUT : silver/uw_decisions/{sub_id}_uw.json OUTPUT : silver/premium_predictions/{sub_id}_pricing.json PREMIUM FORMULA (actuarial base): base_rate = 0.0065 (0.65% of dwelling value) credit_modifier = 1 + max(0, (720 - credit_score) / 720) × 0.35 risk_modifier = 1 + (overall_risk / 100) × 0.80 age_modifier = 1 + min(property_age / 100, 0.40) coverage_modifier = per coverage type (HO-3: 1.0, HO-5: 1.15 etc.) noise = random [0.92, 1.08] premium = base × limit × credit_mod × risk_mod × age_mod × cov_mod × noise ML MODEL : XGBoost regressor trained on approved Bronze records. Ensemble: 60% XGBoost + 40% GLM actuarial formula. ══════════════════════════════════════════════════════════════════════════════ """ import json import pickle import datetime import numpy as np import pandas as pd # mysql.connector kept as fallback; primary driver is PyMySQL via SQLAlchemy import mysql.connector try: from sqlalchemy import create_engine, text from urllib.parse import quote_plus as _qp SQLALCHEMY_AVAILABLE = True except ImportError: SQLALCHEMY_AVAILABLE = False from pathlib import Path from sklearn.model_selection import train_test_split from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score from sklearn.linear_model import Ridge import xgboost as xgb # ─── CONFIG ────────────────────────────────────────────────────────────────── # ─── DB CONFIG — supports local MySQL and HuggingFace + Clever Cloud ───────── import os as _os def _is_huggingface() -> bool: return ( _os.environ.get("SPACE_ID") is not None or _os.environ.get("HUGGINGFACE_SPACE") is not None or _os.environ.get("MYSQL_ADDON_HOST") is not None or _os.environ.get("MYSQL_HOST") is not None ) def _env(addon_key: str, generic_key: str, default: str = "") -> str: """Reads MYSQL_ADDON_* first (Clever Cloud), then MYSQL_* (generic), then default.""" return _os.environ.get(addon_key) or _os.environ.get(generic_key) or default if _is_huggingface(): DB = dict( host = _env("MYSQL_ADDON_HOST", "MYSQL_HOST"), port = int(_env("MYSQL_ADDON_PORT", "MYSQL_PORT", "3306")), user = _env("MYSQL_ADDON_USER", "MYSQL_USER"), password = _env("MYSQL_ADDON_PASSWORD", "MYSQL_PASSWORD"), database = _env("MYSQL_ADDON_DB", "MYSQL_DATABASE"), ) else: DB = dict(host="localhost", port=3306, user="root", password="root@123", database="bronze") def T(layer: str, table: str) -> str: """ Returns the correct table reference for the active environment. HuggingFace (single schema): `bronze_submissions` Local (separate schemas): `bronze`.`submissions` """ return f"`{layer}_{table}`" if _is_huggingface() else f"`{layer}`.`{table}`" MODEL_PATH = Path("models/agent4_pricing.pkl") SILVER_OUT = Path("silver/premium_predictions") MODEL_PATH.parent.mkdir(exist_ok=True) SILVER_OUT.mkdir(parents=True, exist_ok=True) # ── Coverage type base rate multipliers ────────────────────────────────────── COVERAGE_MODIFIER = { "HO-3": 1.00, "HO-5": 1.15, "HO-4": 0.35, "HO-6": 0.42, "DP-3": 0.88, "DP-1": 0.72, "BOP": 1.30, "FARM": 1.45, "WC-3": 0.95, } # ── State load factors (based on historical loss data) ────────────────────── STATE_LOAD = { "FL": 1.35, "TX": 1.25, "LA": 1.30, "CA": 1.20, "NC": 1.05, "SC": 1.08, "GA": 1.02, "AL": 1.10, "MS": 1.15, "AZ": 0.95, "CO": 1.00, "WA": 0.98, "IL": 0.92, "NY": 1.10, "PA": 0.88, "KS": 1.05, "NV": 0.90, "OH": 0.88, } BASE_RATE = 0.0065 # 0.65% of coverage limit CONFIDENCE_WIDTH = 0.12 # ±12% confidence interval # ─── ACTUARIAL FORMULA ─────────────────────────────────────────────────────── def actuarial_premium( coverage_limit: float, credit_score: float, overall_risk: float, year_built: int, coverage_type: str, state: str, deductible: float = 1_000, add_noise: bool = False, ) -> float: """ Pure actuarial formula — used as GLM component in the ensemble, and as fallback when the ML model is not yet trained. """ # Modifiers credit_mod = 1.0 + max(0.0, (720.0 - float(credit_score)) / 720.0) * 0.35 risk_mod = 1.0 + (float(overall_risk) / 100.0) * 0.80 prop_age = max(0, 2024 - int(year_built)) age_mod = 1.0 + min(prop_age / 100.0, 0.40) cov_mod = COVERAGE_MODIFIER.get(coverage_type, 1.0) state_mod = STATE_LOAD.get(str(state).upper(), 1.0) # Deductible credit (higher deductible = lower premium) ded_pct = float(deductible) / max(float(coverage_limit), 1) * 100 ded_credit = max(0.0, 1.0 - (ded_pct / 100.0) * 0.40) noise = np.random.uniform(0.93, 1.07) if add_noise else 1.0 premium = ( BASE_RATE * float(coverage_limit) * credit_mod * risk_mod * age_mod * cov_mod * state_mod * ded_credit * noise ) return round(max(premium, 300.0), 2) # floor $300 # ─── FEATURE ENGINEERING ───────────────────────────────────────────────────── def extract_pricing_features(df: pd.DataFrame) -> pd.DataFrame: """ Full feature set for the XGBoost pricing regressor. Uses all available Bronze + derived Silver signals. """ feats = pd.DataFrame() n = len(df) feats["coverage_limit"] = pd.to_numeric(df.get("requested_coverage_limit", pd.Series([300_000]*n)), errors="coerce").fillna(300_000) feats["deductible"] = pd.to_numeric(df.get("requested_deductible", pd.Series([1_000]*n)), errors="coerce").fillna(1_000) feats["credit_score"] = pd.to_numeric(df.get("credit_score", pd.Series([680]*n)), errors="coerce").fillna(680) feats["overall_risk"] = pd.to_numeric(df.get("prop_risk_score", df.get("overall_risk", pd.Series([30]*n))), errors="coerce").fillna(30) feats["property_age"] = (2024 - pd.to_numeric(df.get("year_built", pd.Series([1990]*n)), errors="coerce").fillna(1990)).clip(0, 150) feats["roof_age"] = (2024 - pd.to_numeric(df.get("roof_year", pd.Series([2010]*n)), errors="coerce").fillna(2010)).clip(0, 50) cov = df.get("coverage_type_code", pd.Series(["HO-3"]*n)) feats["coverage_mod"] = cov.map(COVERAGE_MODIFIER).fillna(1.0) feats["state_load"] = df.get("state_code", df.get("state", pd.Series(["XX"]*n))).map(STATE_LOAD).fillna(1.0) sqft = pd.to_numeric(df.get("square_footage", pd.Series([1800]*n)), errors="coerce").fillna(1800).clip(500, 15000) feats["sqft"] = sqft feats["limit_per_sqft"] = (feats["coverage_limit"] / sqft).clip(0, 3000) feats["deductible_pct"] = (feats["deductible"] / feats["coverage_limit"].clip(lower=1) * 100).clip(0, 20) # Actuarial sub-factors (let model learn interaction weights) feats["credit_mod"] = 1.0 + (np.maximum(0, 720 - feats["credit_score"]) / 720) * 0.35 feats["risk_mod"] = 1.0 + (feats["overall_risk"] / 100) * 0.80 feats["age_mod"] = 1.0 + np.minimum(feats["property_age"] / 100, 0.40) feats["actuarial_base"] = BASE_RATE * feats["coverage_limit"] * feats["credit_mod"] * feats["risk_mod"] * feats["age_mod"] * feats["coverage_mod"] * feats["state_load"] return feats # ─── DATA LOADING ──────────────────────────────────────────────────────────── def load_bronze_pricing_data() -> pd.DataFrame: print("Connecting to Bronze MySQL...") if SQLALCHEMY_AVAILABLE: _pwd = _qp(DB['password']) eng = create_engine( f"mysql+pymysql://{DB['user']}:{_pwd}@{DB['host']}:{DB['port']}/{DB['database']}?charset=utf8mb4", pool_pre_ping=True, pool_recycle=280 ) conn = eng.connect() else: conn = mysql.connector.connect(**DB) query = f""" SELECT s.submission_id, s.coverage_type_code, s.requested_coverage_limit, s.requested_deductible, s.final_outcome, s.pipeline_status, s.raw_payload, pr.state_code, pr.property_type, pr.year_built, pr.roof_year, pr.square_footage FROM {T('bronze','submissions')} s JOIN {T('bronze','properties')} pr ON s.property_id = pr.property_id WHERE s.final_outcome = 'APPROVED' AND s.submitted_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59' ORDER BY s.submitted_at """ df = pd.read_sql(query, conn) conn.close() print(f" Loaded {len(df)} approved Bronze records for pricing") def parse_pricing(row): try: p = json.loads(row["raw_payload"]) return { "credit_score": p.get("insured", {}).get("credit_score", 680), "prop_risk_score": p.get("property", {}).get("prop_risk_score", 30), "premium": p.get("agent_results", {}).get("premium"), } except Exception: return {"credit_score": 680, "prop_risk_score": 30, "premium": None} parsed = df.apply(parse_pricing, axis=1, result_type="expand") df = pd.concat([df.drop(columns=["raw_payload"]), parsed], axis=1) df = df.dropna(subset=["premium"]) df["premium"] = pd.to_numeric(df["premium"], errors="coerce") df = df[df["premium"] > 0] print(f" Usable records (premium > 0): {len(df)}") print(f" Premium range: ${df['premium'].min():,.0f} – ${df['premium'].max():,.0f}") print(f" Avg premium : ${df['premium'].mean():,.0f}") return df # ─── TRAINING ──────────────────────────────────────────────────────────────── def train_pricing_model(): print("\n" + "═"*60) print("AGENT 4 — ML Pricing Model Training") print("═"*60) df = load_bronze_pricing_data() X = extract_pricing_features(df) y = df["premium"] FEATURES = X.columns.tolist() print(f"\nFeatures ({len(FEATURES)}): {FEATURES}") X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42) # XGBoost regressor xgb_model = xgb.XGBRegressor( n_estimators = 400, max_depth = 5, learning_rate = 0.04, subsample = 0.80, colsample_bytree = 0.75, min_child_weight = 3, reg_alpha = 0.05, reg_lambda = 1.0, eval_metric = "rmse", early_stopping_rounds = 25, random_state = 42, verbosity = 0, ) xgb_model.fit(X_train, y_train, eval_set=[(X_test, y_test)], verbose=False) # Ridge GLM (trained on actuarial_base feature only — captures pure actuarial relationship) glm_model = Ridge(alpha=1.0) glm_model.fit(X_train[["actuarial_base"]], y_train) # Ensemble predictions: 60% XGBoost + 40% GLM xgb_pred = xgb_model.predict(X_test) glm_pred = glm_model.predict(X_test[["actuarial_base"]]) ens_pred = 0.60 * xgb_pred + 0.40 * glm_pred mae_xgb = mean_absolute_error(y_test, xgb_pred) mae_ens = mean_absolute_error(y_test, ens_pred) rmse_ens = np.sqrt(mean_squared_error(y_test, ens_pred)) r2_ens = r2_score(y_test, ens_pred) print(f"\n XGBoost MAE : ${mae_xgb:,.0f}") print(f" Ensemble MAE : ${mae_ens:,.0f}") print(f" Ensemble RMSE : ${rmse_ens:,.0f}") print(f" Ensemble R² : {r2_ens:.4f}") imp = pd.Series(xgb_model.feature_importances_, index=FEATURES).sort_values(ascending=False) print("\n Top Feature Importances (XGBoost):") for feat, val in imp.head(8).items(): print(f" {feat:<30} {val:.4f}") # Residual std for confidence interval residuals = y_test - ens_pred ci_std = float(residuals.std()) artefact = { "xgb_model": xgb_model, "glm_model": glm_model, "features": FEATURES, "ensemble_weights": {"xgb": 0.60, "glm": 0.40}, "ci_std": ci_std, "metrics": {"MAE": round(mae_ens, 2), "RMSE": round(rmse_ens, 2), "R2": round(r2_ens, 4)}, "trained_at": datetime.datetime.now().isoformat(), "version": "1.0", } with open(MODEL_PATH, "wb") as f: pickle.dump(artefact, f) print(f"\n Model saved → {MODEL_PATH}") return artefact # ─── INFERENCE ─────────────────────────────────────────────────────────────── def run_pricing_agent(uw_decision: dict, property_risk: dict, submission_json: dict) -> dict: """ Parameters ---------- uw_decision : dict Output from Agent 3 (silver/uw_decisions/) property_risk : dict Output from Agent 2 (silver/property_risk/) submission_json : dict Full Bronze JSON payload Returns ------- dict Pricing output written to silver/premium_predictions/ """ sub_id = submission_json.get("submission_id", "UNKNOWN") # Guard: only run if UW approved if uw_decision.get("status") != "UW_APPROVED": return {"submission_id": sub_id, "status": "SKIPPED", "skip_reason": f"{uw_decision.get('status')} — pipeline halted at Step 3"} insured = submission_json.get("insured", {}) prop = submission_json.get("property", {}) policy = submission_json.get("policy_request", {}) peril = property_risk.get("peril_scores", {}) if property_risk else {} credit_score = float(insured.get("credit_score", 680) or 680) overall_risk = float(peril.get("overall_risk", 30) or 30) coverage_limit = float(policy.get("limit", 300_000) or 300_000) deductible = float(policy.get("deductible", 1_000) or 1_000) coverage_type = policy.get("coverage_type", "HO-3") state = prop.get("state", "XX") year_built = int(prop.get("year_built", 1990) or 1990) roof_year = int(prop.get("roof_year", 2010) or 2010) sqft = float(prop.get("square_footage", 1800) or 1800) # Actuarial base (always calculated) act_premium = actuarial_premium( coverage_limit, credit_score, overall_risk, year_built, coverage_type, state, deductible ) try: with open(MODEL_PATH, "rb") as f: art = pickle.load(f) row = pd.DataFrame([{ "requested_coverage_limit": coverage_limit, "requested_deductible": deductible, "credit_score": credit_score, "overall_risk": overall_risk, "prop_risk_score": overall_risk, "year_built": year_built, "roof_year": roof_year, "coverage_type_code": coverage_type, "state_code": state, "square_footage": sqft, }]) feats = extract_pricing_features(row)[art["features"]] xgb_pred = float(art["xgb_model"].predict(feats)[0]) glm_pred = float(art["glm_model"].predict(feats[["actuarial_base"]])[0]) w_xgb = art["ensemble_weights"]["xgb"] w_glm = art["ensemble_weights"]["glm"] ml_premium = w_xgb * xgb_pred + w_glm * glm_pred final_premium = round(max(ml_premium, 300.0), 2) ci_std = art["ci_std"] ci_lo = round(max(final_premium - 1.96 * ci_std, 200.0), 2) ci_hi = round(final_premium + 1.96 * ci_std, 2) except FileNotFoundError: # Model not yet trained — use actuarial formula only final_premium = act_premium ci_lo = round(final_premium * 0.88, 2) ci_hi = round(final_premium * 1.12, 2) # ── Premium breakdown (explainability) ── credit_mod = 1.0 + max(0.0, (720 - credit_score) / 720) * 0.35 risk_mod = 1.0 + (overall_risk / 100) * 0.80 age_mod = 1.0 + min((2024 - year_built) / 100, 0.40) cov_mod = COVERAGE_MODIFIER.get(coverage_type, 1.0) state_mod = STATE_LOAD.get(str(state).upper(), 1.0) output = { "submission_id": sub_id, "agent": "ML_Pricing_Agent", "step": 4, "status": "PRICED", "final_premium": final_premium, "actuarial_premium": act_premium, "confidence_interval": {"lo_95": ci_lo, "hi_95": ci_hi}, "premium_breakdown": { "base_rate": BASE_RATE, "coverage_limit": coverage_limit, "credit_modifier": round(credit_mod, 4), "risk_modifier": round(risk_mod, 4), "age_modifier": round(age_mod, 4), "coverage_modifier": round(cov_mod, 4), "state_load_factor": round(state_mod, 4), }, "coverage_type": coverage_type, "annual_premium": final_premium, "monthly_premium": round(final_premium / 12, 2), "processed_at": datetime.datetime.now().isoformat(), "next_step": "Issuance_Agent", "s3_output_uri": f"s3://pcins-silver/premium_predictions/{sub_id}_pricing.json", } out_file = SILVER_OUT / f"{sub_id}_pricing.json" with open(out_file, "w") as f: json.dump(output, f, indent=2) return output # ─── MAIN ──────────────────────────────────────────────────────────────────── if __name__ == "__main__": train_pricing_model() print("\n" + "─"*60) print("SMOKE TESTS") print("─"*60) uw_ok = {"status": "UW_APPROVED"} prop_ok = {"status": "RISK_ACCEPTABLE", "peril_scores": {"overall_risk": 28}} tests = [ { # Low risk, good credit → should be cheap "submission_id": "SUB-TEST-001", "insured": {"credit_score": 780}, "property": {"state": "PA", "year_built": 2010, "roof_year": 2010, "square_footage": 2200}, "policy_request": {"coverage_type": "HO-3", "limit": 380_000, "deductible": 2_500}, }, { # Medium risk, average credit → mid-range premium "submission_id": "SUB-TEST-002", "insured": {"credit_score": 650}, "property": {"state": "TX", "year_built": 1985, "roof_year": 2005, "square_footage": 1800}, "policy_request": {"coverage_type": "HO-3", "limit": 320_000, "deductible": 1_000}, }, { # High value, coastal → expensive "submission_id": "SUB-TEST-003", "insured": {"credit_score": 820}, "property": {"state": "FL", "year_built": 2015, "roof_year": 2015, "square_footage": 4000}, "policy_request": {"coverage_type": "HO-5", "limit": 1_800_000, "deductible": 10_000}, }, ] for t in tests: r = run_pricing_agent(uw_ok, prop_ok, t) print(f" {t['submission_id']} | {t['policy_request']['coverage_type']} " f"${t['policy_request']['limit']:,.0f} | Credit {t['insured']['credit_score']} " f"| State {t['property']['state']} " f"→ Premium ${r['final_premium']:,.0f} " f" CI [${r['confidence_interval']['lo_95']:,.0f}–${r['confidence_interval']['hi_95']:,.0f}]")