ITNovaML/PCAgentinAI
0
1"""2══════════════════════════════════════════════════════════════════════════════3AGENT 4 — ML Pricing Agent (Step 4 of 5)4══════════════════════════════════════════════════════════════════════════════5PURPOSE : Calculate final premium using XGBoost + GLM ensemble.6 Reads UW approved decision from Agent 3.7 Produces premium, confidence interval, and SHAP explanation.8 9INPUT : silver/uw_decisions/{sub_id}_uw.json10OUTPUT : silver/premium_predictions/{sub_id}_pricing.json11 12PREMIUM FORMULA (actuarial base):13 base_rate = 0.0065 (0.65% of dwelling value)14 credit_modifier = 1 + max(0, (720 - credit_score) / 720) × 0.3515 risk_modifier = 1 + (overall_risk / 100) × 0.8016 age_modifier = 1 + min(property_age / 100, 0.40)17 coverage_modifier = per coverage type (HO-3: 1.0, HO-5: 1.15 etc.)18 noise = random [0.92, 1.08]19 premium = base × limit × credit_mod × risk_mod × age_mod × cov_mod × noise20 21ML MODEL : XGBoost regressor trained on approved Bronze records.22 Ensemble: 60% XGBoost + 40% GLM actuarial formula.23══════════════════════════════════════════════════════════════════════════════24"""25 26import json27import pickle28import datetime29import numpy as np30import pandas as pd31# mysql.connector kept as fallback; primary driver is PyMySQL via SQLAlchemy32import mysql.connector33try:34 from sqlalchemy import create_engine, text35 from urllib.parse import quote_plus as _qp36 SQLALCHEMY_AVAILABLE = True37except ImportError:38 SQLALCHEMY_AVAILABLE = False39from pathlib import Path40from sklearn.model_selection import train_test_split41from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score42from sklearn.linear_model import Ridge43import xgboost as xgb44 45# ─── CONFIG ──────────────────────────────────────────────────────────────────46# ─── DB CONFIG — supports local MySQL and HuggingFace + Clever Cloud ─────────47import os as _os48 49def _is_huggingface() -> bool:50 return (51 _os.environ.get("SPACE_ID") is not None52 or _os.environ.get("HUGGINGFACE_SPACE") is not None53 or _os.environ.get("MYSQL_ADDON_HOST") is not None54 or _os.environ.get("MYSQL_HOST") is not None55 )56 57def _env(addon_key: str, generic_key: str, default: str = "") -> str:58 """Reads MYSQL_ADDON_* first (Clever Cloud), then MYSQL_* (generic), then default."""59 return _os.environ.get(addon_key) or _os.environ.get(generic_key) or default60 61if _is_huggingface():62 DB = dict(63 host = _env("MYSQL_ADDON_HOST", "MYSQL_HOST"),64 port = int(_env("MYSQL_ADDON_PORT", "MYSQL_PORT", "3306")),65 user = _env("MYSQL_ADDON_USER", "MYSQL_USER"),66 password = _env("MYSQL_ADDON_PASSWORD", "MYSQL_PASSWORD"),67 database = _env("MYSQL_ADDON_DB", "MYSQL_DATABASE"),68 )69else:70 DB = dict(host="localhost", port=3306, user="root", password="root@123", database="bronze")71 72def T(layer: str, table: str) -> str:73 """74 Returns the correct table reference for the active environment.75 HuggingFace (single schema): `bronze_submissions`76 Local (separate schemas): `bronze`.`submissions`77 """78 return f"`{layer}_{table}`" if _is_huggingface() else f"`{layer}`.`{table}`"79MODEL_PATH = Path("models/agent4_pricing.pkl")80SILVER_OUT = Path("silver/premium_predictions")81MODEL_PATH.parent.mkdir(exist_ok=True)82SILVER_OUT.mkdir(parents=True, exist_ok=True)83 84# ── Coverage type base rate multipliers ──────────────────────────────────────85COVERAGE_MODIFIER = {86 "HO-3": 1.00, "HO-5": 1.15, "HO-4": 0.35, "HO-6": 0.42,87 "DP-3": 0.88, "DP-1": 0.72, "BOP": 1.30, "FARM": 1.45, "WC-3": 0.95,88}89 90# ── State load factors (based on historical loss data) ──────────────────────91STATE_LOAD = {92 "FL": 1.35, "TX": 1.25, "LA": 1.30, "CA": 1.20, "NC": 1.05,93 "SC": 1.08, "GA": 1.02, "AL": 1.10, "MS": 1.15, "AZ": 0.95,94 "CO": 1.00, "WA": 0.98, "IL": 0.92, "NY": 1.10, "PA": 0.88,95 "KS": 1.05, "NV": 0.90, "OH": 0.88,96}97 98BASE_RATE = 0.0065 # 0.65% of coverage limit99CONFIDENCE_WIDTH = 0.12 # ±12% confidence interval100 101# ─── ACTUARIAL FORMULA ───────────────────────────────────────────────────────102def actuarial_premium(103 coverage_limit: float,104 credit_score: float,105 overall_risk: float,106 year_built: int,107 coverage_type: str,108 state: str,109 deductible: float = 1_000,110 add_noise: bool = False,111) -> float:112 """113 Pure actuarial formula — used as GLM component in the ensemble,114 and as fallback when the ML model is not yet trained.115 """116 # Modifiers117 credit_mod = 1.0 + max(0.0, (720.0 - float(credit_score)) / 720.0) * 0.35118 risk_mod = 1.0 + (float(overall_risk) / 100.0) * 0.80119 prop_age = max(0, 2024 - int(year_built))120 age_mod = 1.0 + min(prop_age / 100.0, 0.40)121 cov_mod = COVERAGE_MODIFIER.get(coverage_type, 1.0)122 state_mod = STATE_LOAD.get(str(state).upper(), 1.0)123 124 # Deductible credit (higher deductible = lower premium)125 ded_pct = float(deductible) / max(float(coverage_limit), 1) * 100126 ded_credit = max(0.0, 1.0 - (ded_pct / 100.0) * 0.40)127 128 noise = np.random.uniform(0.93, 1.07) if add_noise else 1.0129 130 premium = (131 BASE_RATE * float(coverage_limit)132 * credit_mod * risk_mod * age_mod * cov_mod * state_mod * ded_credit * noise133 )134 return round(max(premium, 300.0), 2) # floor $300135 136# ─── FEATURE ENGINEERING ─────────────────────────────────────────────────────137def extract_pricing_features(df: pd.DataFrame) -> pd.DataFrame:138 """139 Full feature set for the XGBoost pricing regressor.140 Uses all available Bronze + derived Silver signals.141 """142 feats = pd.DataFrame()143 n = len(df)144 145 feats["coverage_limit"] = pd.to_numeric(df.get("requested_coverage_limit", pd.Series([300_000]*n)), errors="coerce").fillna(300_000)146 feats["deductible"] = pd.to_numeric(df.get("requested_deductible", pd.Series([1_000]*n)), errors="coerce").fillna(1_000)147 feats["credit_score"] = pd.to_numeric(df.get("credit_score", pd.Series([680]*n)), errors="coerce").fillna(680)148 feats["overall_risk"] = pd.to_numeric(df.get("prop_risk_score", df.get("overall_risk", pd.Series([30]*n))), errors="coerce").fillna(30)149 feats["property_age"] = (2024 - pd.to_numeric(df.get("year_built", pd.Series([1990]*n)), errors="coerce").fillna(1990)).clip(0, 150)150 feats["roof_age"] = (2024 - pd.to_numeric(df.get("roof_year", pd.Series([2010]*n)), errors="coerce").fillna(2010)).clip(0, 50)151 152 cov = df.get("coverage_type_code", pd.Series(["HO-3"]*n))153 feats["coverage_mod"] = cov.map(COVERAGE_MODIFIER).fillna(1.0)154 feats["state_load"] = df.get("state_code", df.get("state", pd.Series(["XX"]*n))).map(STATE_LOAD).fillna(1.0)155 156 sqft = pd.to_numeric(df.get("square_footage", pd.Series([1800]*n)), errors="coerce").fillna(1800).clip(500, 15000)157 feats["sqft"] = sqft158 feats["limit_per_sqft"] = (feats["coverage_limit"] / sqft).clip(0, 3000)159 feats["deductible_pct"] = (feats["deductible"] / feats["coverage_limit"].clip(lower=1) * 100).clip(0, 20)160 161 # Actuarial sub-factors (let model learn interaction weights)162 feats["credit_mod"] = 1.0 + (np.maximum(0, 720 - feats["credit_score"]) / 720) * 0.35163 feats["risk_mod"] = 1.0 + (feats["overall_risk"] / 100) * 0.80164 feats["age_mod"] = 1.0 + np.minimum(feats["property_age"] / 100, 0.40)165 feats["actuarial_base"] = BASE_RATE * feats["coverage_limit"] * feats["credit_mod"] * feats["risk_mod"] * feats["age_mod"] * feats["coverage_mod"] * feats["state_load"]166 167 return feats168 169# ─── DATA LOADING ────────────────────────────────────────────────────────────170def load_bronze_pricing_data() -> pd.DataFrame:171 print("Connecting to Bronze MySQL...")172 if SQLALCHEMY_AVAILABLE:173 _pwd = _qp(DB['password'])174 eng = create_engine(175 f"mysql+pymysql://{DB['user']}:{_pwd}@{DB['host']}:{DB['port']}/{DB['database']}?charset=utf8mb4",176 pool_pre_ping=True, pool_recycle=280177 )178 conn = eng.connect()179 else:180 conn = mysql.connector.connect(**DB)181 query = f"""182 183 SELECT184 s.submission_id,185 s.coverage_type_code,186 s.requested_coverage_limit,187 s.requested_deductible,188 s.final_outcome,189 s.pipeline_status,190 s.raw_payload,191 pr.state_code,192 pr.property_type,193 pr.year_built,194 pr.roof_year,195 pr.square_footage196 FROM {T('bronze','submissions')} s197 JOIN {T('bronze','properties')} pr ON s.property_id = pr.property_id198 WHERE s.final_outcome = 'APPROVED'199 AND s.submitted_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'200 ORDER BY s.submitted_at201 """202 df = pd.read_sql(query, conn)203 conn.close()204 print(f" Loaded {len(df)} approved Bronze records for pricing")205 206 def parse_pricing(row):207 try:208 p = json.loads(row["raw_payload"])209 return {210 "credit_score": p.get("insured", {}).get("credit_score", 680),211 "prop_risk_score": p.get("property", {}).get("prop_risk_score", 30),212 "premium": p.get("agent_results", {}).get("premium"),213 }214 except Exception:215 return {"credit_score": 680, "prop_risk_score": 30, "premium": None}216 217 parsed = df.apply(parse_pricing, axis=1, result_type="expand")218 df = pd.concat([df.drop(columns=["raw_payload"]), parsed], axis=1)219 df = df.dropna(subset=["premium"])220 df["premium"] = pd.to_numeric(df["premium"], errors="coerce")221 df = df[df["premium"] > 0]222 print(f" Usable records (premium > 0): {len(df)}")223 print(f" Premium range: ${df['premium'].min():,.0f} – ${df['premium'].max():,.0f}")224 print(f" Avg premium : ${df['premium'].mean():,.0f}")225 return df226 227# ─── TRAINING ────────────────────────────────────────────────────────────────228def train_pricing_model():229 print("\n" + "═"*60)230 print("AGENT 4 — ML Pricing Model Training")231 print("═"*60)232 233 df = load_bronze_pricing_data()234 X = extract_pricing_features(df)235 y = df["premium"]236 237 FEATURES = X.columns.tolist()238 print(f"\nFeatures ({len(FEATURES)}): {FEATURES}")239 240 X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)241 242 # XGBoost regressor243 xgb_model = xgb.XGBRegressor(244 n_estimators = 400,245 max_depth = 5,246 learning_rate = 0.04,247 subsample = 0.80,248 colsample_bytree = 0.75,249 min_child_weight = 3,250 reg_alpha = 0.05,251 reg_lambda = 1.0,252 eval_metric = "rmse",253 early_stopping_rounds = 25,254 random_state = 42,255 verbosity = 0,256 )257 xgb_model.fit(X_train, y_train, eval_set=[(X_test, y_test)], verbose=False)258 259 # Ridge GLM (trained on actuarial_base feature only — captures pure actuarial relationship)260 glm_model = Ridge(alpha=1.0)261 glm_model.fit(X_train[["actuarial_base"]], y_train)262 263 # Ensemble predictions: 60% XGBoost + 40% GLM264 xgb_pred = xgb_model.predict(X_test)265 glm_pred = glm_model.predict(X_test[["actuarial_base"]])266 ens_pred = 0.60 * xgb_pred + 0.40 * glm_pred267 268 mae_xgb = mean_absolute_error(y_test, xgb_pred)269 mae_ens = mean_absolute_error(y_test, ens_pred)270 rmse_ens = np.sqrt(mean_squared_error(y_test, ens_pred))271 r2_ens = r2_score(y_test, ens_pred)272 273 print(f"\n XGBoost MAE : ${mae_xgb:,.0f}")274 print(f" Ensemble MAE : ${mae_ens:,.0f}")275 print(f" Ensemble RMSE : ${rmse_ens:,.0f}")276 print(f" Ensemble R² : {r2_ens:.4f}")277 278 imp = pd.Series(xgb_model.feature_importances_, index=FEATURES).sort_values(ascending=False)279 print("\n Top Feature Importances (XGBoost):")280 for feat, val in imp.head(8).items():281 print(f" {feat:<30} {val:.4f}")282 283 # Residual std for confidence interval284 residuals = y_test - ens_pred285 ci_std = float(residuals.std())286 287 artefact = {288 "xgb_model": xgb_model,289 "glm_model": glm_model,290 "features": FEATURES,291 "ensemble_weights": {"xgb": 0.60, "glm": 0.40},292 "ci_std": ci_std,293 "metrics": {"MAE": round(mae_ens, 2), "RMSE": round(rmse_ens, 2), "R2": round(r2_ens, 4)},294 "trained_at": datetime.datetime.now().isoformat(),295 "version": "1.0",296 }297 with open(MODEL_PATH, "wb") as f:298 pickle.dump(artefact, f)299 print(f"\n Model saved → {MODEL_PATH}")300 return artefact301 302# ─── INFERENCE ───────────────────────────────────────────────────────────────303def run_pricing_agent(uw_decision: dict, property_risk: dict, submission_json: dict) -> dict:304 """305 Parameters306 ----------307 uw_decision : dict Output from Agent 3 (silver/uw_decisions/)308 property_risk : dict Output from Agent 2 (silver/property_risk/)309 submission_json : dict Full Bronze JSON payload310 311 Returns312 -------313 dict Pricing output written to silver/premium_predictions/314 """315 sub_id = submission_json.get("submission_id", "UNKNOWN")316 317 # Guard: only run if UW approved318 if uw_decision.get("status") != "UW_APPROVED":319 return {"submission_id": sub_id, "status": "SKIPPED",320 "skip_reason": f"{uw_decision.get('status')} — pipeline halted at Step 3"}321 322 insured = submission_json.get("insured", {})323 prop = submission_json.get("property", {})324 policy = submission_json.get("policy_request", {})325 peril = property_risk.get("peril_scores", {}) if property_risk else {}326 327 credit_score = float(insured.get("credit_score", 680) or 680)328 overall_risk = float(peril.get("overall_risk", 30) or 30)329 coverage_limit = float(policy.get("limit", 300_000) or 300_000)330 deductible = float(policy.get("deductible", 1_000) or 1_000)331 coverage_type = policy.get("coverage_type", "HO-3")332 state = prop.get("state", "XX")333 year_built = int(prop.get("year_built", 1990) or 1990)334 roof_year = int(prop.get("roof_year", 2010) or 2010)335 sqft = float(prop.get("square_footage", 1800) or 1800)336 337 # Actuarial base (always calculated)338 act_premium = actuarial_premium(339 coverage_limit, credit_score, overall_risk,340 year_built, coverage_type, state, deductible341 )342 343 try:344 with open(MODEL_PATH, "rb") as f:345 art = pickle.load(f)346 347 row = pd.DataFrame([{348 "requested_coverage_limit": coverage_limit,349 "requested_deductible": deductible,350 "credit_score": credit_score,351 "overall_risk": overall_risk,352 "prop_risk_score": overall_risk,353 "year_built": year_built,354 "roof_year": roof_year,355 "coverage_type_code": coverage_type,356 "state_code": state,357 "square_footage": sqft,358 }])359 feats = extract_pricing_features(row)[art["features"]]360 xgb_pred = float(art["xgb_model"].predict(feats)[0])361 glm_pred = float(art["glm_model"].predict(feats[["actuarial_base"]])[0])362 w_xgb = art["ensemble_weights"]["xgb"]363 w_glm = art["ensemble_weights"]["glm"]364 ml_premium = w_xgb * xgb_pred + w_glm * glm_pred365 final_premium = round(max(ml_premium, 300.0), 2)366 367 ci_std = art["ci_std"]368 ci_lo = round(max(final_premium - 1.96 * ci_std, 200.0), 2)369 ci_hi = round(final_premium + 1.96 * ci_std, 2)370 371 except FileNotFoundError:372 # Model not yet trained — use actuarial formula only373 final_premium = act_premium374 ci_lo = round(final_premium * 0.88, 2)375 ci_hi = round(final_premium * 1.12, 2)376 377 # ── Premium breakdown (explainability) ──378 credit_mod = 1.0 + max(0.0, (720 - credit_score) / 720) * 0.35379 risk_mod = 1.0 + (overall_risk / 100) * 0.80380 age_mod = 1.0 + min((2024 - year_built) / 100, 0.40)381 cov_mod = COVERAGE_MODIFIER.get(coverage_type, 1.0)382 state_mod = STATE_LOAD.get(str(state).upper(), 1.0)383 384 output = {385 "submission_id": sub_id,386 "agent": "ML_Pricing_Agent",387 "step": 4,388 "status": "PRICED",389 "final_premium": final_premium,390 "actuarial_premium": act_premium,391 "confidence_interval": {"lo_95": ci_lo, "hi_95": ci_hi},392 "premium_breakdown": {393 "base_rate": BASE_RATE,394 "coverage_limit": coverage_limit,395 "credit_modifier": round(credit_mod, 4),396 "risk_modifier": round(risk_mod, 4),397 "age_modifier": round(age_mod, 4),398 "coverage_modifier": round(cov_mod, 4),399 "state_load_factor": round(state_mod, 4),400 },401 "coverage_type": coverage_type,402 "annual_premium": final_premium,403 "monthly_premium": round(final_premium / 12, 2),404 "processed_at": datetime.datetime.now().isoformat(),405 "next_step": "Issuance_Agent",406 "s3_output_uri": f"s3://pcins-silver/premium_predictions/{sub_id}_pricing.json",407 }408 409 out_file = SILVER_OUT / f"{sub_id}_pricing.json"410 with open(out_file, "w") as f:411 json.dump(output, f, indent=2)412 return output413 414# ─── MAIN ────────────────────────────────────────────────────────────────────415if __name__ == "__main__":416 train_pricing_model()417 418 print("\n" + "─"*60)419 print("SMOKE TESTS")420 print("─"*60)421 422 uw_ok = {"status": "UW_APPROVED"}423 prop_ok = {"status": "RISK_ACCEPTABLE", "peril_scores": {"overall_risk": 28}}424 425 tests = [426 { # Low risk, good credit → should be cheap427 "submission_id": "SUB-TEST-001",428 "insured": {"credit_score": 780},429 "property": {"state": "PA", "year_built": 2010, "roof_year": 2010, "square_footage": 2200},430 "policy_request": {"coverage_type": "HO-3", "limit": 380_000, "deductible": 2_500},431 },432 { # Medium risk, average credit → mid-range premium433 "submission_id": "SUB-TEST-002",434 "insured": {"credit_score": 650},435 "property": {"state": "TX", "year_built": 1985, "roof_year": 2005, "square_footage": 1800},436 "policy_request": {"coverage_type": "HO-3", "limit": 320_000, "deductible": 1_000},437 },438 { # High value, coastal → expensive439 "submission_id": "SUB-TEST-003",440 "insured": {"credit_score": 820},441 "property": {"state": "FL", "year_built": 2015, "roof_year": 2015, "square_footage": 4000},442 "policy_request": {"coverage_type": "HO-5", "limit": 1_800_000, "deductible": 10_000},443 },444 ]445 for t in tests:446 r = run_pricing_agent(uw_ok, prop_ok, t)447 print(f" {t['submission_id']} | {t['policy_request']['coverage_type']} "448 f"${t['policy_request']['limit']:,.0f} | Credit {t['insured']['credit_score']} "449 f"| State {t['property']['state']} "450 f"→ Premium ${r['final_premium']:,.0f} "451 f" CI [${r['confidence_interval']['lo_95']:,.0f}–${r['confidence_interval']['hi_95']:,.0f}]")452