agent4_pricing.py

"""
══════════════════════════════════════════════════════════════════════════════
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}]")