ITNovaML/PCAgentinAI
0
1"""2PolicyBridge — Flask API Server3================================4Run locally: python app.py (connects to localhost MySQL)5Run on HF: Set Secrets below, app auto-detects and connects to Clever Cloud6 7HuggingFace Secrets to add (Settings → Variables and Secrets):8 MYSQL_ADDON_HOST = btvbbpqhvnttzvptguj3-mysql.services.clever-cloud.com9 MYSQL_ADDON_PORT = 330610 MYSQL_ADDON_USER = utenclk29u394u1j11 MYSQL_ADDON_PASSWORD = QXFZTmUtPnXrKFqZKpLQ12 MYSQL_ADDON_DB = btvbbpqhvnttzvptguj313"""14 15import os, sys, json, logging16from datetime import datetime17 18from flask import Flask, jsonify, request, send_from_directory19from flask_cors import CORS20 21AGENTS_DIR = os.path.dirname(os.path.abspath(__file__))22sys.path.insert(0, AGENTS_DIR)23 24def _import_agent(module_name, func_name):25 try:26 mod = __import__(module_name)27 return getattr(mod, func_name)28 except Exception as e:29 logging.warning(f"Could not import {module_name}.{func_name}: {e}")30 return None31 32# ════════════════════════════════════════════════════════════════════33# DYNAMIC ENVIRONMENT — Local MySQL vs HuggingFace + Clever Cloud34# ════════════════════════════════════════════════════════════════════35 36def _is_huggingface() -> bool:37 return (38 os.environ.get("SPACE_ID") is not None39 or os.environ.get("HUGGINGFACE_SPACE") is not None40 or os.environ.get("MYSQL_ADDON_HOST") is not None41 or os.environ.get("MYSQL_HOST") is not None42 )43 44def _env(addon_key: str, generic_key: str, default: str = "") -> str:45 return os.environ.get(addon_key) or os.environ.get(generic_key) or default46 47DB = dict(48 host = _env("MYSQL_ADDON_HOST", "MYSQL_HOST", "localhost"),49 port = int(_env("MYSQL_ADDON_PORT", "MYSQL_PORT", "3306")),50 user = _env("MYSQL_ADDON_USER", "MYSQL_USER", "root"),51 password = _env("MYSQL_ADDON_PASSWORD", "MYSQL_PASSWORD", "root@123"),52 database = _env("MYSQL_ADDON_DB", "MYSQL_DATABASE", "bronze"),53)54 55def T(layer: str, table: str) -> str:56 return f"`{layer}_{table}`" if _is_huggingface() else f"`{layer}`.`{table}`"57 58app = Flask(__name__)59CORS(app, origins="*")60logging.basicConfig(level=logging.INFO, format='%(asctime)s [%(levelname)s] %(message)s')61log = logging.getLogger(__name__)62 63_engine = None64 65def get_engine():66 """67 NullPool engine — NO connection pooling.68 Each request opens one connection and closes it immediately when done.69 This is the only safe approach for Clever Cloud free tier (max 5 connections)70 because pooling keeps connections open between requests.71 With NullPool: active connections = number of requests being processed RIGHT NOW.72 On a single-worker server this is almost always 1.73 """74 global _engine75 if _engine is None:76 from sqlalchemy import create_engine77 from sqlalchemy.pool import NullPool78 from urllib.parse import quote_plus as qp79 pwd = qp(DB['password'])80 _engine = create_engine(81 f"mysql+pymysql://{DB['user']}:{pwd}@{DB['host']}:{DB['port']}/{DB['database']}?charset=utf8mb4",82 poolclass=NullPool,83 connect_args={"connect_timeout": 10}84 )85 log.info(f"[DB] NullPool engine created → {DB['host']}:{DB['port']}/{DB['database']}")86 return _engine87 88def get_conn():89 return get_engine().connect()90 91pipeline_ctx: dict = {}92 93# ════════════════════════════════════════════════════════════════════94# HELPERS95# ════════════════════════════════════════════════════════════════════96def safe_json(obj):97 import math, numpy as np98 if isinstance(obj, dict): return {k: safe_json(v) for k, v in obj.items()}99 if isinstance(obj, list): return [safe_json(v) for v in obj]100 if isinstance(obj, float): return None if (math.isnan(obj) or math.isinf(obj)) else obj101 if isinstance(obj, np.integer): return int(obj)102 if isinstance(obj, np.floating):103 v = float(obj); return None if (math.isnan(v) or math.isinf(v)) else v104 if isinstance(obj, np.bool_): return bool(obj)105 if isinstance(obj, np.ndarray): return obj.tolist()106 return obj107 108def df_to_records(df) -> list:109 return df.where(df.notna(), other=None).to_dict(orient="records")110 111def load_submission(sub_id: str) -> dict | None:112 import pandas as pd113 try:114 eng = get_engine()115 query = f"""116 SELECT s.submission_id, s.coverage_type_code, s.requested_coverage_limit,117 s.requested_deductible, s.pipeline_status, s.final_outcome,118 s.halt_reason, s.raw_payload,119 i.full_name, i.dob, i.email, i.phone,120 i.city AS insured_city, i.state_code AS insured_state,121 i.street AS insured_street, i.zip AS insured_zip,122 p.street AS prop_street, p.city AS prop_city, p.state_code,123 p.zip AS prop_zip, p.year_built, p.square_footage,124 p.construction_type, p.roof_type, p.roof_year,125 p.num_stories, p.property_type, p.occupancy,126 b.broker_code, b.broker_name127 FROM {T('bronze','submissions')} s128 LEFT JOIN {T('bronze','insureds')} i ON s.insured_id = i.insured_id129 LEFT JOIN {T('bronze','properties')} p ON s.property_id = p.property_id130 LEFT JOIN {T('bronze','brokers')} b ON s.broker_id = b.broker_id131 WHERE s.submission_id = %(sid)s LIMIT 1132 """133 with eng.connect() as conn:134 df = pd.read_sql(query, conn, params={"sid": sub_id})135 if df.empty: return None136 137 row = df_to_records(df)[0]138 payload = {}139 raw_str = row.get("raw_payload") or ""140 if raw_str:141 try:142 payload = json.loads(raw_str)143 except json.JSONDecodeError:144 log.warning(f"load_submission({sub_id}): JSON corrupt — rebuilding from DB")145 try:146 import re147 clean = re.sub(r',\s*"[^"]*$', '', raw_str).rstrip(',')148 clean += '}' * max(clean.count('{') - clean.count('}'), 0)149 payload = json.loads(clean)150 except Exception:151 payload = {}152 153 ins = payload.setdefault("insured", {})154 prop = payload.setdefault("property", {})155 payload.setdefault("policy_request", {})156 payload.setdefault("agent_results", {})157 158 ins.update({k: v for k, v in {159 "full_name": row.get("full_name"),160 "dob": str(row.get("dob","")) if row.get("dob") else None,161 "email": row.get("email"),162 "phone": row.get("phone"),163 }.items() if v is not None})164 165 prop.update({k: v for k, v in {166 "street": row.get("prop_street"), "city": row.get("prop_city"),167 "state": row.get("state_code"), "state_code": row.get("state_code"),168 "zip": row.get("prop_zip"), "year_built": row.get("year_built"),169 "square_footage": row.get("square_footage"),170 "construction_type": row.get("construction_type"),171 "roof_type": row.get("roof_type"), "roof_year": row.get("roof_year"),172 "num_stories": row.get("num_stories"),173 "property_type": row.get("property_type"), "occupancy": row.get("occupancy"),174 }.items() if v is not None})175 176 if not ins.get("credit_score"): ins["credit_score"] = 650177 if not ins.get("kyc_status"): ins["kyc_status"] = "PENDING"178 179 payload.update({180 "_submission_id": sub_id,181 "_coverage_type_code": row.get("coverage_type_code"),182 "_requested_coverage_limit": row.get("requested_coverage_limit"),183 "_requested_deductible": row.get("requested_deductible"),184 "_pipeline_status": row.get("pipeline_status"),185 "_final_outcome": row.get("final_outcome"),186 "_broker_code": row.get("broker_code"),187 "_broker_name": row.get("broker_name"),188 })189 log.info(f"load_submission({sub_id}): OK — {ins.get('full_name')} / {prop.get('city')}")190 return payload191 except Exception as e:192 log.error(f"load_submission({sub_id}): {e}", exc_info=True)193 return None194 195# ════════════════════════════════════════════════════════════════════196# ROUTES197# ════════════════════════════════════════════════════════════════════198@app.route("/status", methods=["GET"])199def status():200 try:201 import pandas as pd202 conn = get_conn()203 df = pd.read_sql(f"SELECT COUNT(*) AS c FROM {T('bronze','submissions')}", conn)204 tbls = pd.read_sql("SHOW TABLES", conn).iloc[:, 0].tolist()205 conn.close()206 audit_table_exists = any("audit_log" in t for t in tbls)207 return jsonify({208 "status": "ok",209 "environment": "huggingface" if _is_huggingface() else "local",210 "db_host": DB['host'], "db_name": DB['database'],211 "submissions_count": int(df_to_records(df)[0].get("c", 0)),212 "tables": tbls,213 "timestamp": datetime.now().isoformat(),214 "agents": {215 "agent1_kyc": os.path.exists(os.path.join(AGENTS_DIR,"models","agent1_kyc_classifier.pkl")),216 "agent2_property": os.path.exists(os.path.join(AGENTS_DIR,"models","agent2_property_risk.pkl")),217 "agent3_underwriting": os.path.exists(os.path.join(AGENTS_DIR,"models","agent3_underwriting.pkl")),218 "agent4_pricing": os.path.exists(os.path.join(AGENTS_DIR,"models","agent4_pricing.pkl")),219 "agent6_audit": True,220 },221 "audit_log_table": audit_table_exists,222 })223 except Exception as e:224 return jsonify({"status": "error", "error": str(e)}), 500225 226@app.route("/submissions/recent", methods=["GET"])227def recent_submissions():228 limit = request.args.get("limit", 10, type=int)229 try:230 import pandas as pd231 eng = get_engine()232 query = f"""233 SELECT s.submission_id, s.coverage_type_code, s.requested_coverage_limit,234 s.requested_deductible, s.final_outcome, s.pipeline_status,235 s.halt_reason, s.submitted_at, s.raw_payload,236 i.full_name, p.city AS prop_city, p.state_code AS prop_state,237 p.street AS prop_street, p.zip AS prop_zip, p.year_built,238 p.square_footage, p.construction_type, p.roof_type,239 p.roof_year, p.num_stories, p.property_type,240 b.broker_code, b.broker_name241 FROM {T('bronze','submissions')} s242 LEFT JOIN {T('bronze','insureds')} i ON s.insured_id = i.insured_id243 LEFT JOIN {T('bronze','properties')} p ON s.property_id = p.property_id244 LEFT JOIN {T('bronze','brokers')} b ON s.broker_id = b.broker_id245 ORDER BY s.submitted_at DESC LIMIT {limit}246 """247 with eng.connect() as conn:248 df = pd.read_sql(query, conn)249 subs = []250 for d in df_to_records(df):251 try: payload = json.loads(d["raw_payload"]) if d.get("raw_payload") else {}252 except: payload = {}253 ins = payload.setdefault("insured", {})254 prop = payload.setdefault("property", {})255 payload.setdefault("policy_request", {}); payload.setdefault("agent_results", {})256 if d.get("full_name"): ins["full_name"] = d["full_name"]257 if d.get("prop_city"): prop["city"] = d["prop_city"]258 if d.get("prop_state"): prop["state"] = d["prop_state"]; prop["state_code"] = d["prop_state"]259 if d.get("prop_street"): prop["street"] = d["prop_street"]260 if d.get("prop_zip"): prop["zip"] = d["prop_zip"]261 for fld in ["year_built","square_footage","construction_type","roof_type","roof_year","num_stories","property_type"]:262 if d.get(fld) is not None: prop[fld] = d[fld]263 subs.append(safe_json({264 "submission_id": d.get("submission_id"),265 "coverage_type_code": d.get("coverage_type_code"),266 "requested_coverage_limit": d.get("requested_coverage_limit"),267 "requested_deductible": d.get("requested_deductible"),268 "final_outcome": d.get("final_outcome"),269 "pipeline_status": d.get("pipeline_status"),270 "halt_reason": d.get("halt_reason"),271 "submitted_at": str(d.get("submitted_at","")),272 "broker_code": d.get("broker_code"),273 "broker_name": d.get("broker_name"),274 "raw_payload": payload,275 }))276 return jsonify({"submissions": subs, "count": len(subs)})277 except Exception as e:278 log.error(f"/submissions/recent: {e}", exc_info=True)279 return jsonify({"error": str(e), "submissions": [], "count": 0}), 500280 281@app.route("/debug/tables", methods=["GET"])282def debug_tables():283 try:284 import pandas as pd285 eng = get_engine()286 with eng.connect() as c:287 tbls = pd.read_sql("SHOW TABLES", c).iloc[:, 0].tolist()288 result = {}289 for tbl in tbls:290 try:291 with eng.connect() as c:292 cols = pd.read_sql(f"SHOW COLUMNS FROM `{tbl}`", c)293 sample = pd.read_sql(f"SELECT * FROM `{tbl}` LIMIT 1", c)294 result[tbl] = {"columns": cols["Field"].tolist(),295 "sample": safe_json(sample.iloc[0].to_dict()) if not sample.empty else {}}296 except Exception as te:297 result[tbl] = {"error": str(te)}298 return jsonify(result)299 except Exception as e:300 return jsonify({"error": str(e)}), 500301 302@app.route("/debug/submission", methods=["GET"])303def debug_submission():304 try:305 import pandas as pd306 conn = get_conn()307 df = pd.read_sql(f"SELECT * FROM {T('bronze','submissions')} ORDER BY submitted_at DESC LIMIT 1", conn)308 conn.close()309 if df.empty: return jsonify({"error": "No submissions found"})310 row = df.iloc[0].to_dict()311 try: row["raw_payload"] = json.loads(row["raw_payload"]) if row["raw_payload"] else {}312 except: pass313 for col in ["submitted_at","created_at","updated_at","received_at"]:314 row[col] = str(row.get(col,""))315 return jsonify(safe_json(row))316 except Exception as e:317 return jsonify({"error": str(e)}), 500318 319# ── AGENT 1: KYC ──────────────────────────────────────────────────320@app.route("/agent/kyc", methods=["POST"])321def agent_kyc():322 sub_id = request.json.get("submission_id")323 if not sub_id: return jsonify({"error": "submission_id required"}), 400324 sub = pipeline_ctx.get(sub_id, {}).get("submission") or load_submission(sub_id)325 if not sub: return jsonify({"error": f"Submission {sub_id} not found"}), 404326 log.info(f"[KYC] Running Agent 1 for {sub_id}")327 try:328 from agent1_ssn_kyc import run_kyc_agent329 result = run_kyc_agent(sub)330 except Exception as e:331 log.error(f"[KYC] Agent error: {e}")332 credit = (sub.get("insured") or {}).get("credit_score", 0)333 ofac = (sub.get("insured") or {}).get("ofac_result", "CLEAR")334 result = {"submission_id": sub_id,335 "status": "KYC_PASS" if credit >= 550 and ofac == "CLEAR" else "KYC_FAIL",336 "credit_score": credit, "ofac_result": ofac, "fraud_signals": 0,337 "ml_kyc_probability": 0.85 if credit >= 550 else 0.1,338 "decline_reason": None if credit >= 550 else f"Credit {credit} below 550",339 "_fallback": True}340 pipeline_ctx.setdefault(sub_id, {}).update({"kyc": result, "submission": sub})341 return jsonify(safe_json(result))342 343# ── AGENT 2: PROPERTY RISK ────────────────────────────────────────344@app.route("/agent/property", methods=["POST"])345def agent_property():346 sub_id = request.json.get("submission_id")347 if not sub_id: return jsonify({"error": "submission_id required"}), 400348 ctx = pipeline_ctx.get(sub_id, {})349 sub = ctx.get("submission") or load_submission(sub_id)350 kyc = ctx.get("kyc", {"status": "KYC_PASS"})351 log.info(f"[PROPERTY] Running Agent 2 for {sub_id}")352 try:353 from agent2_property_risk import run_property_risk_agent354 result = run_property_risk_agent(kyc, sub)355 except Exception as e:356 log.error(f"[PROPERTY] Agent error: {e}")357 prop = (sub or {}).get("property", {})358 score = prop.get("prop_risk_score", 40)359 band = prop.get("risk_band", "MEDIUM") or "MEDIUM"360 is_ok = score is not None and score < 75361 result = {"submission_id": sub_id,362 "status": "RISK_ACCEPTABLE" if is_ok else "RISK_DECLINED",363 "risk_band": band if is_ok else "DECLINED",364 "peril_scores": {"wind_score": round((score or 40)*0.65),365 "flood_score": round((score or 40)*0.55),366 "fire_score": round((score or 40)*0.60),367 "overall_risk": score or 40},368 "risk_acceptability_prob": 0.8 if is_ok else 0.1,369 "decline_reason": None if is_ok else f"Risk score {score} exceeds threshold",370 "_fallback": True}371 pipeline_ctx.setdefault(sub_id, {}).update({"property": result, "submission": sub})372 return jsonify(safe_json(result))373 374# ── AGENT 3: UNDERWRITING ─────────────────────────────────────────375@app.route("/agent/underwriting", methods=["POST"])376def agent_underwriting():377 sub_id = request.json.get("submission_id")378 if not sub_id: return jsonify({"error": "submission_id required"}), 400379 ctx = pipeline_ctx.get(sub_id, {})380 sub = ctx.get("submission") or load_submission(sub_id)381 prop = ctx.get("property", {"status": "RISK_ACCEPTABLE", "peril_scores": {}})382 log.info(f"[UW] Running Agent 3 for {sub_id}")383 try:384 from agent3_underwriting import run_underwriting_agent385 result = run_underwriting_agent(prop, sub)386 except Exception as e:387 log.error(f"[UW] Agent error: {e}")388 cov = (sub or {}).get("_coverage_type_code") or (sub or {}).get("policy_request",{}).get("coverage_type","HO-3")389 lim = (sub or {}).get("_requested_coverage_limit") or (sub or {}).get("policy_request",{}).get("limit",300000)390 result = {"submission_id": sub_id, "status": "UW_APPROVED", "coverage_type": cov,391 "uw_approval_probability": 0.82, "expected_loss_ratio": 0.58,392 "primary_decline_reason": None, "all_rule_violations": [],393 "reinsurance_required": lim > 2_000_000, "_fallback": True}394 pipeline_ctx.setdefault(sub_id, {}).update({"underwriting": result})395 return jsonify(safe_json(result))396 397# ── AGENT 4: PRICING ──────────────────────────────────────────────398@app.route("/agent/pricing", methods=["POST"])399def agent_pricing():400 sub_id = request.json.get("submission_id")401 if not sub_id: return jsonify({"error": "submission_id required"}), 400402 ctx = pipeline_ctx.get(sub_id, {})403 sub = ctx.get("submission") or load_submission(sub_id)404 uw = ctx.get("underwriting", {"status": "UW_APPROVED"})405 prop = ctx.get("property", {"peril_scores": {}})406 log.info(f"[PRICING] Running Agent 4 for {sub_id}")407 try:408 from agent4_pricing import run_pricing_agent409 result = run_pricing_agent(uw, prop, sub)410 except Exception as e:411 log.error(f"[PRICING] Agent error: {e}")412 lim = float((sub or {}).get("_requested_coverage_limit") or413 (sub or {}).get("policy_request",{}).get("limit", 300000))414 prem = max(300, round(lim * 0.0065))415 result = {"submission_id": sub_id, "final_premium": prem,416 "actuarial_premium": prem, "annual_premium": prem,417 "monthly_premium": round(prem/12),418 "confidence_interval": {"lo_95": round(prem*0.88), "hi_95": round(prem*1.12)},419 "premium_breakdown": {"credit_modifier":1.0,"risk_modifier":1.0,"age_modifier":1.0},420 "_fallback": True}421 pipeline_ctx.setdefault(sub_id, {}).update({"pricing": result})422 return jsonify(safe_json(result))423 424# ── AGENT 5: ISSUANCE ─────────────────────────────────────────────425@app.route("/agent/issuance", methods=["POST"])426def agent_issuance():427 sub_id = request.json.get("submission_id")428 if not sub_id: return jsonify({"error": "submission_id required"}), 400429 ctx = pipeline_ctx.get(sub_id, {})430 sub = ctx.get("submission") or load_submission(sub_id)431 pric = ctx.get("pricing", {"final_premium": 0})432 uw = ctx.get("underwriting", {})433 prop = ctx.get("property", {})434 log.info(f"[ISSUANCE] Running Agent 5 for {sub_id}")435 try:436 from agent5_issuance_orchestrator import run_issuance_agent437 result = run_issuance_agent(pric, uw, prop, sub)438 except Exception as e:439 log.error(f"[ISSUANCE] Agent error: {e}")440 yr = datetime.now().year441 prem = pric.get("final_premium", 0)442 result = {"submission_id": sub_id,443 "policy_number": f"PC-{yr}-{sub_id[-5:]}{(sub or {}).get('property',{}).get('state','XX')}",444 "policy_details": {445 "effective_date": datetime.now().strftime("%Y-%m-%d"),446 "expiration_date": f"{yr+1}-{datetime.now().strftime('%m-%d')}",447 "annual_premium": prem, "monthly_premium": round(prem/12),448 "coverage_type": uw.get("coverage_type","HO-3"),449 "risk_band": prop.get("risk_band","MEDIUM"),450 },451 "documents_generated": ["declarations_page.pdf","policy_contract.pdf"],452 "notifications_sent": {"email":True,"sns":True},453 "_fallback": True}454 # Store issuance in ctx BEFORE popping (audit needs it)455 pipeline_ctx.setdefault(sub_id, {}).update({"issuance": result})456 _update_bronze_status(sub_id, result)457 return jsonify(safe_json(result))458 459# ════════════════════════════════════════════════════════════════════460# AGENT 6: AUDIT — AXIOM461# Reads all prior agent outputs from pipeline_ctx (or DB fallback)462# Generates plain-English audit summary + saves to gold_audit_log463# ════════════════════════════════════════════════════════════════════464 465def _run_audit_from_ctx(sub_id: str, ctx: dict) -> dict:466 """Core audit logic — works from in-memory ctx or loaded submission."""467 sub = ctx.get("submission", {})468 kyc = ctx.get("kyc", {})469 prop = ctx.get("property", {})470 uw = ctx.get("underwriting", {})471 pric = ctx.get("pricing", {})472 iss = ctx.get("issuance", {})473 474 factors = []475 decline_reasons = []476 477 # ── Agent 1: KYC / Document Validation ────────────────────────478 kyc_pass = kyc.get("status") == "KYC_PASS"479 credit = kyc.get("credit_score", 0)480 ofac = kyc.get("ofac_result", "CLEAR")481 fraud_sig = kyc.get("fraud_signals", 0)482 kyc_prob = float(kyc.get("ml_kyc_probability") or 0.85)483 if kyc:484 factors.append({485 "agent": "Document Validation Agent",486 "factor": "Identity & Compliance Check",487 "outcome": "PASS" if kyc_pass else "FAIL",488 "detail": (489 f"Credit score: {credit}. "490 f"OFAC: {'Clear' if ofac == 'CLEAR' else 'HIT — FLAGGED'}. "491 f"Fraud signals: {fraud_sig}. "492 f"KYC model confidence: {kyc_prob:.1%}."493 )494 })495 if not kyc_pass:496 reason = kyc.get("decline_reason") or f"Credit {credit} below minimum or OFAC flag detected."497 decline_reasons.append(f"Document Validation failed: {reason}")498 499 # ── Agent 2: Property Risk ─────────────────────────────────────500 risk_band = prop.get("risk_band", "MEDIUM")501 peril = prop.get("peril_scores", {})502 wind = peril.get("wind_score", 0)503 flood = peril.get("flood_score", 0)504 fire = peril.get("fire_score", 0)505 overall_r = peril.get("overall_risk", 0)506 risk_ok = prop.get("status") != "RISK_DECLINED"507 if prop:508 factors.append({509 "agent": "Property Risk Agent",510 "factor": "Peril Risk Assessment",511 "outcome": "PASS" if risk_ok else "DECLINE",512 "detail": (513 f"Risk band: {risk_band}. "514 f"Wind: {wind} | Flood: {flood} | Fire: {fire}. "515 f"Overall risk score: {overall_r}."516 )517 })518 if not risk_ok:519 reason = prop.get("decline_reason") or f"Risk score {overall_r} exceeds threshold."520 decline_reasons.append(f"Property risk declined: {reason}")521 522 # ── Agent 3: Underwriting ──────────────────────────────────────523 uw_status = uw.get("status", "UW_APPROVED")524 uw_approved = uw_status == "UW_APPROVED"525 uw_prob = float(uw.get("uw_approval_probability") or 0.82)526 uw_elr = float(uw.get("expected_loss_ratio") or 0.58)527 uw_reason = uw.get("primary_decline_reason") or ""528 violations = uw.get("all_rule_violations", [])529 if uw:530 factors.append({531 "agent": "Underwriting Agent",532 "factor": "AI Underwriting Decision",533 "outcome": "APPROVED" if uw_approved else "DECLINED",534 "detail": (535 f"Decision: {uw_status}. "536 f"Approval probability: {uw_prob:.1%}. "537 f"Expected loss ratio: {uw_elr:.2f}. "538 f"Rule violations: {len(violations)}."539 + (f" Decline reason: {uw_reason}." if uw_reason else "")540 )541 })542 if not uw_approved:543 decline_reasons.append(544 f"Underwriting declined: {uw_reason or 'Risk profile outside binding authority guidelines'}. "545 f"Approval probability: {uw_prob:.1%}."546 )547 548 # ── Agent 4: Pricing ──────────────────────────────────────────549 final_prem = float(pric.get("final_premium") or pric.get("annual_premium") or 0)550 monthly_prem = float(pric.get("monthly_premium") or (final_prem / 12 if final_prem else 0))551 ci = pric.get("confidence_interval", {})552 breakdown = pric.get("premium_breakdown", {})553 credit_mod = float(breakdown.get("credit_modifier") or 1.0)554 risk_mod = float(breakdown.get("risk_modifier") or 1.0)555 if pric and final_prem > 0:556 factors.append({557 "agent": "Pricing Agent",558 "factor": "Actuarial Premium Calculation",559 "outcome": "CALCULATED",560 "detail": (561 f"Annual premium: ${final_prem:,.2f} (${monthly_prem:,.2f}/mo). "562 f"Credit modifier: {credit_mod:.2f}x | Risk modifier: {risk_mod:.2f}x. "563 f"95% CI: ${ci.get('lo_95', 0):,.0f} – ${ci.get('hi_95', 0):,.0f}."564 )565 })566 567 # ── Agent 5: Issuance ─────────────────────────────────────────568 policy_num = iss.get("policy_number")569 pol_details = iss.get("policy_details", {})570 eff_date = pol_details.get("effective_date", "")571 exp_date = pol_details.get("expiration_date", "")572 docs = iss.get("documents_generated", [])573 if iss:574 factors.append({575 "agent": "Issuance Agent",576 "factor": "Policy Issuance",577 "outcome": "ISSUED" if policy_num else "NOT ISSUED",578 "detail": (579 f"Policy number: {policy_num or 'N/A'}. "580 f"Effective: {eff_date} to {exp_date}. "581 f"Documents: {', '.join(docs) if docs else 'None generated'}."582 )583 })584 585 # ── Final decision ─────────────────────────────────────────────586 decision = "DECLINED" if decline_reasons else "APPROVED"587 588 ins_name = (sub.get("insured") or {}).get("full_name", "the applicant")589 prop_city = (sub.get("property") or {}).get("city", "")590 prop_state = (sub.get("property") or {}).get("state_code", "")591 location = f"{prop_city}, {prop_state}".strip(", ") or "the insured property"592 593 if decision == "APPROVED":594 overall_summary = (595 f"The application from {ins_name} for the property at {location} has been approved. "596 f"The property was assessed as {risk_band} risk across all perils, "597 f"identity and compliance screening passed with {fraud_sig} fraud signal(s) detected, "598 f"and the underwriting model approved the submission at {uw_prob:.1%} confidence. "599 f"A final annual premium of ${final_prem:,.2f} has been calculated and policy "600 f"{policy_num} has been issued."601 )602 else:603 reason_text = " ".join(decline_reasons[:2])604 overall_summary = (605 f"The application from {ins_name} for the property at {location} has been declined. "606 f"{reason_text} "607 f"All {len(factors)} agent checks were completed before reaching this decision."608 )609 610 # Composite audit confidence score (0–100)611 score_parts = []612 if kyc: score_parts.append(100 if kyc_pass else 0)613 if prop: score_parts.append({"LOW":100,"MEDIUM":70,"HIGH":30,"DECLINED":0}.get(risk_band, 50))614 if uw: score_parts.append(int(uw_prob * 100))615 audit_score = int(sum(score_parts) / len(score_parts)) if score_parts else 50616 617 audit_result = {618 "submission_id": sub_id,619 "decision": decision,620 "overall_summary": overall_summary,621 "decision_factors": factors,622 "decline_reasons": decline_reasons,623 "risk_band": risk_band,624 "final_premium": final_prem if final_prem > 0 else None,625 "policy_number": policy_num,626 "audit_score": audit_score,627 "audited_at": datetime.utcnow().isoformat(),628 }629 630 # ── Save to gold_audit_log ─────────────────────────────────────631 try:632 from sqlalchemy import text as sqlt633 eng = get_engine()634 with eng.begin() as conn:635 r = conn.execute(sqlt(f"""636 INSERT INTO {T('gold','audit_log')} (637 submission_id, decision, overall_summary,638 decision_factors_json, decline_reasons_json,639 risk_band, final_premium, policy_number,640 audit_score, audited_at641 ) VALUES (642 :submission_id, :decision, :overall_summary,643 :factors_json, :reasons_json,644 :risk_band, :final_premium, :policy_number,645 :audit_score, :audited_at646 )647 """), {648 "submission_id": sub_id,649 "decision": decision,650 "overall_summary": overall_summary,651 "factors_json": json.dumps(factors),652 "reasons_json": json.dumps(decline_reasons),653 "risk_band": risk_band,654 "final_premium": final_prem if final_prem > 0 else None,655 "policy_number": policy_num,656 "audit_score": audit_score,657 "audited_at": datetime.utcnow(),658 })659 audit_result["audit_log_id"] = r.lastrowid660 log.info(f"[AUDIT] Saved audit_log_id={audit_result['audit_log_id']} for {sub_id} — {decision}")661 except Exception as db_err:662 log.warning(f"[AUDIT] Could not save to gold_audit_log: {db_err}")663 audit_result["audit_log_id"] = None664 audit_result["_db_warning"] = str(db_err)665 666 return audit_result667 668 669@app.route("/agent/audit", methods=["POST"])670def agent_audit():671 """672 Agent 6 — AXIOM Audit Agent.673 POST /agent/audit Body: {"submission_id": "SUB-001"}674 Generates plain-English audit summary and saves to gold_audit_log.675 """676 sub_id = request.json.get("submission_id")677 if not sub_id:678 return jsonify({"error": "submission_id required"}), 400679 log.info(f"[AUDIT] Running Agent 6 (AXIOM) for {sub_id}")680 ctx = pipeline_ctx.get(sub_id, {})681 if not ctx.get("submission"):682 sub = load_submission(sub_id)683 if not sub:684 return jsonify({"error": f"Submission {sub_id} not found"}), 404685 ctx["submission"] = sub686 result = _run_audit_from_ctx(sub_id, ctx)687 return jsonify(safe_json(result))688 689 690@app.route("/audit/<sub_id>", methods=["GET"])691def get_audit(sub_id):692 """693 GET /audit/<submission_id>694 Returns the most recent audit record from gold_audit_log.695 Runs audit on demand if no record exists yet.696 """697 try:698 import pandas as pd699 eng = get_engine()700 with eng.connect() as conn:701 df = pd.read_sql(702 f"SELECT * FROM {T('gold','audit_log')} WHERE submission_id = %(sid)s ORDER BY audited_at DESC LIMIT 1",703 conn, params={"sid": sub_id}704 )705 if not df.empty:706 record = df_to_records(df)[0]707 for col in ["decision_factors_json", "decline_reasons_json"]:708 val = record.get(col)709 if val:710 try:711 record[col.replace("_json", "")] = json.loads(val)712 except Exception:713 record[col.replace("_json", "")] = []714 record["audited_at"] = str(record.get("audited_at", ""))715 return jsonify(safe_json(record))716 # Not in DB — run on demand717 log.info(f"[AUDIT] No record for {sub_id}, running on demand")718 ctx = pipeline_ctx.get(sub_id, {})719 if not ctx.get("submission"):720 sub = load_submission(sub_id)721 if not sub:722 return jsonify({"error": f"Submission {sub_id} not found"}), 404723 ctx["submission"] = sub724 return jsonify(safe_json(_run_audit_from_ctx(sub_id, ctx)))725 except Exception as e:726 log.error(f"[AUDIT] GET /audit/{sub_id}: {e}", exc_info=True)727 return jsonify({"error": str(e)}), 500728 729 730@app.route("/audit/run/<sub_id>", methods=["POST"])731def trigger_audit(sub_id):732 """733 POST /audit/run/<submission_id>734 Manually re-trigger audit for any existing submission (backfill).735 """736 log.info(f"[AUDIT] Manual trigger for {sub_id}")737 ctx = pipeline_ctx.get(sub_id, {})738 if not ctx.get("submission"):739 sub = load_submission(sub_id)740 if not sub:741 return jsonify({"error": f"Submission {sub_id} not found"}), 404742 ctx["submission"] = sub743 return jsonify(safe_json(_run_audit_from_ctx(sub_id, ctx)))744 745 746# ── FULL PIPELINE (Agents 1–6) ────────────────────────────────────747@app.route("/pipeline/run", methods=["POST"])748def pipeline_run():749 sub_id = request.json.get("submission_id")750 if not sub_id: return jsonify({"error": "submission_id required"}), 400751 sub = load_submission(sub_id)752 if not sub: return jsonify({"error": f"Submission {sub_id} not found"}), 404753 pipeline_ctx[sub_id] = {"submission": sub}754 steps = {}755 756 def call(ep):757 with app.test_client() as c:758 r = c.post(f"/agent/{ep}", json={"submission_id": sub_id}, content_type="application/json")759 return json.loads(r.data)760 761 # Agent 1: KYC762 steps["kyc"] = call("kyc")763 if steps["kyc"].get("status") != "KYC_PASS":764 steps["audit"] = call("audit")765 return jsonify({766 "final_outcome": "DECLINED",767 "halt_reason": "KYC_FAIL",768 "steps": steps,769 "audit_summary": steps["audit"].get("overall_summary"),770 "audit_score": steps["audit"].get("audit_score"),771 "audit_log_id": steps["audit"].get("audit_log_id"),772 })773 774 # Agent 2: Property Risk775 steps["property"] = call("property")776 if steps["property"].get("status") == "RISK_DECLINED":777 steps["audit"] = call("audit")778 return jsonify({779 "final_outcome": "DECLINED",780 "halt_reason": "PROP_DECLINED",781 "steps": steps,782 "audit_summary": steps["audit"].get("overall_summary"),783 "audit_score": steps["audit"].get("audit_score"),784 "audit_log_id": steps["audit"].get("audit_log_id"),785 })786 787 # Agent 3: Underwriting788 steps["underwriting"] = call("underwriting")789 if steps["underwriting"].get("status") == "UW_DECLINED":790 steps["audit"] = call("audit")791 return jsonify({792 "final_outcome": "DECLINED",793 "halt_reason": "UW_DECLINED",794 "steps": steps,795 "audit_summary": steps["audit"].get("overall_summary"),796 "audit_score": steps["audit"].get("audit_score"),797 "audit_log_id": steps["audit"].get("audit_log_id"),798 })799 800 # Agent 4: Pricing801 steps["pricing"] = call("pricing")802 803 # Agent 5: Issuance804 steps["issuance"] = call("issuance")805 806 # Agent 6: Audit — always runs at pipeline end807 steps["audit"] = call("audit")808 809 return jsonify({810 "final_outcome": "APPROVED",811 "policy_number": steps["issuance"].get("policy_number"),812 "final_premium": steps["pricing"].get("final_premium"),813 "audit_summary": steps["audit"].get("overall_summary"),814 "audit_score": steps["audit"].get("audit_score"),815 "audit_log_id": steps["audit"].get("audit_log_id"),816 "steps": steps,817 })818 819# ════════════════════════════════════════════════════════════════════820# INTERNAL821# ════════════════════════════════════════════════════════════════════822def _update_bronze_status(sub_id: str, issuance_result: dict):823 try:824 from sqlalchemy import text825 eng = get_engine()826 prem = (issuance_result.get("policy_details") or {}).get("annual_premium", 0)827 with eng.begin() as conn:828 conn.execute(text(f"""829 UPDATE {T('bronze','submissions')}830 SET pipeline_status = 'ISSUED', final_outcome = 'APPROVED', updated_at = NOW()831 WHERE submission_id = :sid832 """), {"sid": sub_id})833 log.info(f"[DB] Updated {sub_id} → ISSUED / ${prem}")834 except Exception as e:835 log.warning(f"[DB] Could not update {sub_id}: {e}")836 837# ════════════════════════════════════════════════════════════════════838# SUBMIT839# ════════════════════════════════════════════════════════════════════840@app.route("/submit", methods=["POST"])841def submit_new():842 try:843 from sqlalchemy import text844 data = request.get_json(force=True)845 if not data: return jsonify({"error": "No JSON payload received"}), 400846 j = data.get("submission", data)847 sub = j if "submission_id" in j else data.get("submission", {})848 if not sub: return jsonify({"error": "Missing 'submission' object"}), 400849 sid = sub.get("submission_id", "")850 broker = sub.get("broker", {})851 insured = sub.get("insured", {})852 prop = sub.get("property", {})853 pol_req = sub.get("policy_request", {})854 attachments = sub.get("attachments", [])855 if not sid: return jsonify({"error": "Missing submission_id"}), 400856 857 eng = get_engine()858 with eng.begin() as conn:859 # 1. BROKERS860 conn.execute(text(f"""861 INSERT INTO {T('bronze','brokers')} (broker_code, broker_name, contact_email, state_code)862 VALUES (:broker_code, :broker_name, :contact_email, :state_code)863 ON DUPLICATE KEY UPDATE broker_name=VALUES(broker_name),864 contact_email=VALUES(contact_email), state_code=VALUES(state_code)865 """), {"broker_code": broker.get("broker_code",""), "broker_name": broker.get("name",""),866 "contact_email": broker.get("contact_email",""), "state_code": broker.get("state_code","")})867 broker_row = conn.execute(text(868 f"SELECT broker_id FROM {T('bronze','brokers')} WHERE broker_code = :bc"869 ), {"bc": broker.get("broker_code","")}).fetchone()870 broker_id = broker_row[0] if broker_row else None871 872 # 2. INSUREDS873 import hashlib874 ssn_raw = insured.get("ssn","")875 ssn_hash = hashlib.sha256(ssn_raw.encode()).hexdigest() if ssn_raw else None876 ins_result = conn.execute(text(f"""877 INSERT INTO {T('bronze','insureds')}878 (full_name, dob, ssn_hash, email, phone, street, city, state_code, zip)879 VALUES (:full_name, :dob, :ssn_hash, :email, :phone, :street, :city, :state_code, :zip)880 """), {"full_name": insured.get("full_name",""), "dob": insured.get("dob",None),881 "ssn_hash": ssn_hash, "email": insured.get("email",""),882 "phone": insured.get("phone",""), "street": insured.get("street",""),883 "city": insured.get("city",""), "state_code": insured.get("state",""),884 "zip": insured.get("zip","")})885 insured_id = ins_result.lastrowid886 887 # 3. PROPERTIES888 prop_result = conn.execute(text(f"""889 INSERT INTO {T('bronze','properties')}890 (insured_id, street, city, state_code, zip, year_built, square_footage,891 construction_type, roof_type, roof_year, num_stories, property_type, occupancy)892 VALUES (:insured_id, :street, :city, :state_code, :zip, :year_built, :square_footage,893 :construction_type, :roof_type, :roof_year, :num_stories, :property_type, :occupancy)894 """), {"insured_id": insured_id, "street": prop.get("street",""),895 "city": prop.get("city",""), "state_code": prop.get("state_code", prop.get("state","")),896 "zip": prop.get("zip",""), "year_built": prop.get("year_built",None),897 "square_footage": prop.get("square_footage",None),898 "construction_type": prop.get("construction_type",""),899 "roof_type": prop.get("roof_type",""), "roof_year": prop.get("roof_year",None),900 "num_stories": prop.get("num_stories",None),901 "property_type": prop.get("property_type",""), "occupancy": prop.get("occupancy","")})902 property_id = prop_result.lastrowid903 904 # 4. SUBMISSIONS905 import json as _json906 conn.execute(text(f"""907 INSERT INTO {T('bronze','submissions')}908 (submission_id, broker_id, insured_id, property_id, coverage_type_code,909 requested_coverage_limit, requested_deductible, line_of_business, market_type,910 submitted_at, received_at, pipeline_status, final_outcome, halt_reason, raw_payload)911 VALUES (:submission_id, :broker_id, :insured_id, :property_id, :coverage_type_code,912 :requested_coverage_limit, :requested_deductible, :line_of_business, :market_type,913 :submitted_at, NOW(), 'RECEIVED', NULL, NULL, :raw_payload)914 ON DUPLICATE KEY UPDATE pipeline_status='RECEIVED', received_at=NOW()915 """), {"submission_id": sid, "broker_id": broker_id, "insured_id": insured_id,916 "property_id": property_id,917 "coverage_type_code": pol_req.get("coverage_type",""),918 "requested_coverage_limit": pol_req.get("requested_coverage_limit",None),919 "requested_deductible": pol_req.get("requested_deductible",None),920 "line_of_business": sub.get("line_of_business",""),921 "market_type": sub.get("market_type",""),922 "submitted_at": sub.get("submitted_at",None),923 "raw_payload": _json.dumps(data)})924 925 # 5. ATTACHMENTS926 for att in attachments:927 conn.execute(text(f"""928 INSERT IGNORE INTO {T('bronze','submission_attachments')}929 (submission_id, attachment_type, file_name, s3_uri, file_format, file_size_bytes, uploaded_at)930 VALUES (:submission_id, :attachment_type, :file_name, :s3_uri, :file_format, :file_size_bytes, NOW())931 """), {"submission_id": sid, "attachment_type": att.get("attachment_type","OTHER"),932 "file_name": att.get("file_name",""),933 "s3_uri": att.get("s3_uri", f"s3://pcins-bronze/attachments/{sid}/{att.get('file_name','')}"),934 "file_format": att.get("file_format",""), "file_size_bytes": att.get("file_size_bytes",0)})935 936 # 6. PIPELINE AGENT LOG — 6 agents including AXIOM937 for agent_code, seq in [938 ("SSN_Identity_Validation_Agent", 1),939 ("Property_Risk_Assessment_Agent", 2),940 ("Underwriting_Decision_Agent", 3),941 ("Premium_Pricing_Agent", 4),942 ("Issuance_Agent", 5),943 ("Audit_Agent", 6),944 ]:945 conn.execute(text(f"""946 INSERT IGNORE INTO {T('bronze','pipeline_agent_log')}947 (submission_id, agent_code, agent_sequence, status, halt_reason, created_at)948 VALUES (:sid, :agent_code, :seq, 'QUEUED', 'Awaiting prior agents', NOW())949 """), {"sid": sid, "agent_code": agent_code, "seq": seq})950 951 log.info(f"[SUBMIT] {sid} inserted — insured_id={insured_id}, property_id={property_id}")952 return jsonify({"success": True, "submission_id": sid, "insured_id": insured_id,953 "property_id": property_id, "broker_id": broker_id,954 "message": f"{sid} committed to bronze · 6 agents queued"})955 except Exception as e:956 log.error(f"[SUBMIT] Error: {e}", exc_info=True)957 return jsonify({"error": str(e)}), 500958 959# ════════════════════════════════════════════════════════════════════960# STATIC FILE SERVING — HTML front-end apps961# ════════════════════════════════════════════════════════════════════962 963@app.route("/ui")964@app.route("/ui/simulation")965def simulation():966 """Serve the Policy Simulation HTML — inline response, bypasses HF proxy."""967 try:968 html = open(os.path.join(AGENTS_DIR, "pc_insurance_multiagent_v4.html"), encoding="utf-8").read()969 return html, 200, {"Content-Type": "text/html; charset=utf-8"}970 except Exception as e:971 return jsonify({"error": str(e)}), 500972 973@app.route("/ui/policybridge")974def policybridge():975 """Serve the PolicyBridge HTML — inline response."""976 try:977 html = open(os.path.join(AGENTS_DIR, "policybridge_app.html"), encoding="utf-8").read()978 return html, 200, {"Content-Type": "text/html; charset=utf-8"}979 except Exception as e:980 return jsonify({"error": str(e)}), 500981 982 983 984# ════════════════════════════════════════════════════════════════════985# RISKRADAR — GEO RISK INTELLIGENCE (Agent 12)986# ════════════════════════════════════════════════════════════════════987 988@app.route("/riskradar/score", methods=["POST"])989def riskradar_score():990 """991 Score a property address for wind/flood/fire/quake risk.992 POST /riskradar/score993 Body: { address, state_code, zip, year_built, construction_type,994 roof_year, num_stories, [latitude], [longitude], [submission_id] }995 """996 try:997 from agent12_riskradar import run_riskradar_agent998 data = request.get_json(force=True) or {}999 if not data.get('state_code') or not data.get('zip'):1000 return jsonify({"error": "state_code and zip are required"}), 4001001 1002 result = run_riskradar_agent(data)1003 1004 # Persist to DB1005 try:1006 from sqlalchemy import text as sqlt1007 eng = get_engine()1008 with eng.begin() as conn:1009 conn.execute(sqlt("""1010 INSERT INTO silver_riskradar_lookups (1011 address, state_code, zip_code, latitude, longitude,1012 wind_score, flood_score, fire_score, quake_score,1013 overall_score, overall_band,1014 wind_band, flood_band, fire_band, quake_band,1015 narrative, raw_result_json, source, submission_id1016 ) VALUES (1017 :address, :state, :zip, :lat, :lng,1018 :wind, :flood, :fire, :quake,1019 :overall, :overall_band,1020 :wind_band, :flood_band, :fire_band, :quake_band,1021 :narrative, :raw, :source, :sub_id1022 )1023 """), {1024 "address": result.get("address"),1025 "state": result["state_code"],1026 "zip": result["zip_code"],1027 "lat": result["latitude"],1028 "lng": result["longitude"],1029 "wind": result["scores"]["wind"],1030 "flood": result["scores"]["flood"],1031 "fire": result["scores"]["fire"],1032 "quake": result["scores"]["quake"],1033 "overall": result["overall_score"],1034 "overall_band": result["overall_band"],1035 "wind_band": result["bands"]["wind"],1036 "flood_band": result["bands"]["flood"],1037 "fire_band": result["bands"]["fire"],1038 "quake_band": result["bands"]["quake"],1039 "narrative": result["narrative"],1040 "raw": json.dumps(safe_json(result)),1041 "source": data.get("source", "LOOKUP"),1042 "sub_id": data.get("submission_id"),1043 })1044 except Exception as db_err:1045 log.warning(f"[RISKRADAR] DB persist failed: {db_err}")1046 1047 return jsonify(safe_json(result))1048 except Exception as e:1049 log.error(f"[RISKRADAR] /riskradar/score: {e}", exc_info=True)1050 return jsonify({"error": str(e)}), 5001051 1052 1053@app.route("/riskradar/history", methods=["GET"])1054def riskradar_history():1055 """Recent risk lookups — last N records."""1056 limit = request.args.get("limit", 12, type=int)1057 try:1058 import pandas as pd1059 eng = get_engine()1060 with eng.connect() as conn:1061 df = pd.read_sql(1062 f"""SELECT lookup_id, address, state_code, zip_code,1063 wind_score, flood_score, fire_score, quake_score,1064 overall_score, overall_band, latitude, longitude,1065 looked_up_at1066 FROM silver_riskradar_lookups1067 ORDER BY looked_up_at DESC LIMIT {limit}""",1068 conn1069 )1070 records = df.where(df.notna(), other=None).to_dict(orient="records")1071 for r in records:1072 r["looked_up_at"] = str(r.get("looked_up_at",""))1073 return jsonify({"lookups": records, "count": len(records)})1074 except Exception as e:1075 return jsonify({"error": str(e), "lookups": [], "count": 0}), 5001076 1077 1078@app.route("/ui/riskradar")1079def riskradar_ui():1080 """Serve RiskRadar as standalone page."""1081 try:1082 html = open(os.path.join(AGENTS_DIR, "pc_insurance_multiagent_v4.html"),1083 encoding="utf-8").read()1084 return html, 200, {"Content-Type": "text/html; charset=utf-8"}1085 except Exception as e:1086 return jsonify({"error": str(e)}), 5001087 1088 1089 1090# ════════════════════════════════════════════════════════════════════1091# POLICYBRIDGE UI — SUBMISSION + MGA ONBOARDING ROUTES1092# ════════════════════════════════════════════════════════════════════1093 1094@app.route("/policybridge/submit", methods=["POST"])1095def policybridge_submit():1096 try:1097 from sqlalchemy import text as sqlt1098 import hashlib1099 data = request.get_json(force=True) or {}1100 sub_id = data.get("submission_id", "")1101 if not sub_id:1102 return jsonify({"error": "submission_id required"}), 4001103 1104 broker = data.get("broker", {})1105 insured = data.get("insured", {})1106 prop = data.get("property", {})1107 policy = data.get("policy_request", {})1108 mga = data.get("mga", {})1109 1110 eng = get_engine()1111 with eng.begin() as conn:1112 1113 # 1. Upsert broker1114 conn.execute(sqlt("""1115 INSERT INTO bronze_brokers (broker_code, broker_name, contact_email, state_code)1116 VALUES (:code, :name, :email, :state)1117 ON DUPLICATE KEY UPDATE1118 broker_name=VALUES(broker_name),1119 contact_email=VALUES(contact_email),1120 updated_at=NOW()1121 """), {1122 "code": broker.get("broker_code", ""),1123 "name": broker.get("broker_name", ""),1124 "email": broker.get("contact_email", ""),1125 "state": broker.get("state_code", ""),1126 })1127 broker_row = conn.execute(sqlt(1128 "SELECT broker_id FROM bronze_brokers WHERE broker_code=:bc LIMIT 1"1129 ), {"bc": broker.get("broker_code", "")}).fetchone()1130 broker_id = broker_row[0] if broker_row else None1131 1132 # 2. Insert insured1133 ssn_hash = hashlib.sha256(1134 (insured.get("ssn") or sub_id).encode()1135 ).hexdigest()1136 ins_r = conn.execute(sqlt("""1137 INSERT INTO bronze_insureds1138 (full_name, dob, ssn_hash, email, phone,1139 street, city, state_code, zip)1140 VALUES (:full_name, :dob, :ssn_hash, :email, :phone,1141 :street, :city, :state_code, :zip)1142 """), {1143 "full_name": insured.get("full_name", ""),1144 "dob": insured.get("dob") or None,1145 "ssn_hash": ssn_hash,1146 "email": insured.get("email", ""),1147 "phone": insured.get("phone", ""),1148 "street": insured.get("street", ""),1149 "city": insured.get("city", ""),1150 "state_code": insured.get("state", insured.get("state_code", "")),1151 "zip": insured.get("zip", ""),1152 })1153 insured_id = ins_r.lastrowid1154 1155 # 3. Insert property1156 prop_r = conn.execute(sqlt("""1157 INSERT INTO bronze_properties1158 (insured_id, street, city, state_code, zip,1159 property_type, year_built, square_footage,1160 construction_type, roof_type, roof_year,1161 num_stories, occupancy)1162 VALUES (:insured_id, :street, :city, :state_code, :zip,1163 :property_type, :year_built, :sqft,1164 :construction, :roof_type, :roof_year,1165 :stories, :occupancy)1166 """), {1167 "insured_id": insured_id,1168 "street": prop.get("street", ""),1169 "city": prop.get("city", ""),1170 "state_code": prop.get("state_code", prop.get("state", "")),1171 "zip": prop.get("zip", ""),1172 "property_type": prop.get("property_type", ""),1173 "year_built": prop.get("year_built") or None,1174 "sqft": prop.get("square_footage") or None,1175 "construction": prop.get("construction_type", ""),1176 "roof_type": prop.get("roof_type", ""),1177 "roof_year": prop.get("roof_year") or None,1178 "stories": prop.get("num_stories") or None,1179 "occupancy": prop.get("occupancy", ""),1180 })1181 property_id = prop_r.lastrowid1182 1183 # 4. Insert into policybridge_submissions1184 conn.execute(sqlt("""1185 INSERT INTO policybridge_submissions (1186 submission_id, broker_code, broker_name, broker_email, broker_state,1187 mga_code, insured_full_name, insured_dob, insured_email, insured_phone,1188 insured_credit_score, insured_street, insured_city, insured_state, insured_zip,1189 prop_street, prop_city, prop_state, prop_zip, prop_type,1190 year_built, square_footage, construction_type, roof_type, roof_year,1191 coverage_type_code, coverage_limit, deductible,1192 effective_date, notes, pipeline_status, raw_payload_json, submitted_at1193 ) VALUES (1194 :sub_id, :broker_code, :broker_name, :broker_email, :broker_state,1195 :mga_code, :ins_name, :ins_dob, :ins_email, :ins_phone,1196 :credit, :ins_street, :ins_city, :ins_state, :ins_zip,1197 :prop_street, :prop_city, :prop_state, :prop_zip, :prop_type,1198 :year_built, :sqft, :construction, :roof_type, :roof_year,1199 :cov_type, :cov_limit, :deductible,1200 :eff_date, :notes, 'RECEIVED', :raw, NOW()