"""
agent6_audit.py — AXIOM: The Audit Agent
=========================================
Agent 6 in the underwriting pipeline.
Reads all 5 agent outputs for a given submission and generates:
  - A human-readable plain-English audit summary (ACCEPT or DECLINE)
  - Key decision factors (what went right / what triggered decline)
  - A structured audit record stored in the Gold layer DB table

Runs AFTER NOVA (Agent 5) has issued or declined the policy.
Called by: app.py pipeline orchestrator (or independently via /audit/<submission_id>)

DB Table: gold_audit_log
"""

import os
import json
import logging
from datetime import datetime
from sqlalchemy import text
from db import get_engine   # reuse the singleton DB engine from your existing db.py

logger = logging.getLogger(__name__)

# ──────────────────────────────────────────────────────────────────────────────
# DB HELPERS
# ──────────────────────────────────────────────────────────────────────────────

def _fetch_submission(engine, submission_id: int) -> dict | None:
    """Pull the full submission row from bronze layer."""
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT * FROM bronze_submissions WHERE id = :sid"),
            {"sid": submission_id}
        ).mappings().fetchone()
    return dict(row) if row else None


def _fetch_kyc(engine, submission_id: int) -> dict | None:
    """AURA output — silver_kyc_results."""
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT * FROM silver_kyc_results WHERE submission_id = :sid ORDER BY id DESC LIMIT 1"),
            {"sid": submission_id}
        ).mappings().fetchone()
    return dict(row) if row else None


def _fetch_property_risk(engine, submission_id: int) -> dict | None:
    """TERRA output — silver_property_risk."""
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT * FROM silver_property_risk WHERE submission_id = :sid ORDER BY id DESC LIMIT 1"),
            {"sid": submission_id}
        ).mappings().fetchone()
    return dict(row) if row else None


def _fetch_underwriting(engine, submission_id: int) -> dict | None:
    """KARMA output — silver_underwriting_decisions."""
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT * FROM silver_underwriting_decisions WHERE submission_id = :sid ORDER BY id DESC LIMIT 1"),
            {"sid": submission_id}
        ).mappings().fetchone()
    return dict(row) if row else None


def _fetch_pricing(engine, submission_id: int) -> dict | None:
    """AURUM output — silver_pricing_results."""
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT * FROM silver_pricing_results WHERE submission_id = :sid ORDER BY id DESC LIMIT 1"),
            {"sid": submission_id}
        ).mappings().fetchone()
    return dict(row) if row else None


def _fetch_issuance(engine, submission_id: int) -> dict | None:
    """NOVA output — gold_policies."""
    with engine.connect() as conn:
        row = conn.execute(
            text("SELECT * FROM gold_policies WHERE submission_id = :sid ORDER BY id DESC LIMIT 1"),
            {"sid": submission_id}
        ).mappings().fetchone()
    return dict(row) if row else None


# ──────────────────────────────────────────────────────────────────────────────
# AUDIT SUMMARY BUILDER
# ──────────────────────────────────────────────────────────────────────────────

def _build_audit_summary(
    submission: dict,
    kyc: dict | None,
    risk: dict | None,
    uw: dict | None,
    pricing: dict | None,
    issuance: dict | None,
) -> dict:
    """
    Generates the plain-English audit summary and decision factors.
    Returns a dict with:
        - decision          : "APPROVED" | "DECLINED"
        - overall_summary   : 2-3 sentence plain-English explanation
        - decision_factors  : list of factor dicts {agent, factor, outcome, detail}
        - decline_reasons   : list of plain-English decline reasons (empty if approved)
        - risk_band         : LOW / MEDIUM / HIGH / DECLINED
        - final_premium     : numeric or None
        - policy_number     : string or None
        - audit_score       : 0-100 composite confidence score
    """
    factors = []
    decline_reasons = []

    # ── AURA: KYC & Compliance ────────────────────────────────────────────────
    if kyc:
        kyc_pass = kyc.get("kyc_passed", True)
        ofac_clear = kyc.get("ofac_clear", True)
        fraud_score = float(kyc.get("fraud_score", 0.0))
        credit_score = int(kyc.get("credit_score", 700))

        kyc_outcome = "PASS" if kyc_pass else "FAIL"
        factors.append({
            "agent": "Document Validation Agent",
            "factor": "Identity & Compliance Check",
            "outcome": kyc_outcome,
            "detail": (
                f"SSN verified. Credit score: {credit_score}. "
                f"OFAC: {'Clear' if ofac_clear else 'HIT — FLAGGED'}. "
                f"Fraud signal score: {fraud_score:.2f} "
                f"({'Low risk' if fraud_score < 0.3 else 'Elevated risk' if fraud_score < 0.6 else 'HIGH RISK'})."
            )
        })

        if not kyc_pass:
            decline_reasons.append(
                f"Document validation failed: applicant did not pass identity or compliance screening "
                f"(fraud signal: {fraud_score:.2f}, OFAC clear: {ofac_clear})."
            )
        if not ofac_clear:
            decline_reasons.append(
                "OFAC/sanctions screening returned a hit. Policy cannot be issued under US federal compliance rules."
            )

    # ── TERRA: Property Risk ──────────────────────────────────────────────────
    if risk:
        risk_band = risk.get("risk_band", "MEDIUM")
        wind_score = float(risk.get("wind_score", 0.5))
        flood_score = float(risk.get("flood_score", 0.5))
        fire_score = float(risk.get("fire_score", 0.5))
        overall_risk = float(risk.get("overall_risk_score", 0.5))

        risk_outcome = "PASS" if risk_band not in ("HIGH", "DECLINED") else "REFER" if risk_band == "HIGH" else "DECLINE"
        factors.append({
            "agent": "Property Risk Agent",
            "factor": "Peril Risk Assessment",
            "outcome": risk_outcome,
            "detail": (
                f"Overall risk band: {risk_band}. "
                f"Wind: {wind_score:.2f} | Flood: {flood_score:.2f} | Fire: {fire_score:.2f}. "
                f"Composite risk score: {overall_risk:.2f}/1.00."
            )
        })

        if risk_band == "DECLINED":
            decline_reasons.append(
                f"Property risk score ({overall_risk:.2f}) exceeds maximum threshold. "
                f"One or more perils (wind: {wind_score:.2f}, flood: {flood_score:.2f}, fire: {fire_score:.2f}) "
                f"are outside acceptable underwriting parameters."
            )

    # ── KARMA: Underwriting Decision ─────────────────────────────────────────
    if uw:
        uw_decision = uw.get("decision", "APPROVED")
        uw_confidence = float(uw.get("confidence", 0.8))
        uw_reason = uw.get("reason_code", "")
        shap_top = uw.get("shap_top_feature", "credit_score")

        uw_outcome = "APPROVED" if uw_decision == "APPROVED" else "DECLINED"
        factors.append({
            "agent": "Underwriting Agent",
            "factor": "AI Underwriting Decision",
            "outcome": uw_outcome,
            "detail": (
                f"Decision: {uw_decision} (confidence: {uw_confidence:.1%}). "
                f"Primary decision driver (SHAP): {shap_top}. "
                f"Reason code: {uw_reason if uw_reason else 'Standard approval criteria met'}."
            )
        })

        if uw_decision != "APPROVED":
            decline_reasons.append(
                f"Underwriting model returned a DECLINE decision with {uw_confidence:.1%} confidence. "
                f"Primary factor: {shap_top}. Reason: {uw_reason or 'risk profile outside binding authority guidelines'}."
            )

    # ── AURUM: Pricing ────────────────────────────────────────────────────────
    if pricing:
        base_premium = float(pricing.get("base_premium", 0))
        final_premium = float(pricing.get("final_premium", 0))
        credit_mod = float(pricing.get("credit_modifier", 1.0))
        risk_mod = float(pricing.get("risk_band_modifier", 1.0))
        confidence_low = float(pricing.get("confidence_low", final_premium * 0.9))
        confidence_high = float(pricing.get("confidence_high", final_premium * 1.1))

        factors.append({
            "agent": "Pricing Agent",
            "factor": "Actuarial Premium Calculation",
            "outcome": "CALCULATED",
            "detail": (
                f"Base premium: ${base_premium:,.2f}. "
                f"Credit modifier: {credit_mod:.2f}x | Risk band modifier: {risk_mod:.2f}x. "
                f"Final premium: ${final_premium:,.2f} "
                f"(95% CI: ${confidence_low:,.2f} – ${confidence_high:,.2f})."
            )
        })
    else:
        final_premium = None

    # ── NOVA: Issuance ────────────────────────────────────────────────────────
    policy_number = None
    if issuance:
        policy_number = issuance.get("policy_number")
        issued_at = issuance.get("issued_at", "")
        coverage_amount = issuance.get("coverage_amount", 0)

        factors.append({
            "agent": "Issuance Agent",
            "factor": "Policy Issuance",
            "outcome": "ISSUED" if policy_number else "NOT ISSUED",
            "detail": (
                f"Policy number: {policy_number or 'N/A'}. "
                f"Coverage: ${float(coverage_amount or 0):,.2f}. "
                f"Issued at: {issued_at}."
            )
        })

    # ── Final decision ────────────────────────────────────────────────────────
    decision = "APPROVED" if not decline_reasons else "DECLINED"
    if uw and uw.get("decision") != "APPROVED":
        decision = "DECLINED"

    # ── Audit confidence score (0-100) ────────────────────────────────────────
    score_components = []
    if kyc:
        score_components.append(100 if kyc.get("kyc_passed") else 0)
    if risk:
        band_scores = {"LOW": 100, "MEDIUM": 70, "HIGH": 30, "DECLINED": 0}
        score_components.append(band_scores.get(risk.get("risk_band", "MEDIUM"), 50))
    if uw:
        score_components.append(int(float(uw.get("confidence", 0.5)) * 100))
    audit_score = int(sum(score_components) / len(score_components)) if score_components else 50

    # ── Plain-English overall summary ─────────────────────────────────────────
    insured_name = submission.get("insured_name", "the applicant") if submission else "the applicant"
    property_addr = submission.get("property_address", "the insured property") if submission else "the insured property"
    risk_band_str = risk.get("risk_band", "MEDIUM") if risk else "UNKNOWN"

    if decision == "APPROVED":
        overall_summary = (
            f"The application from {insured_name} for {property_addr} has been approved. "
            f"The property was assessed as {risk_band_str} risk across all perils, "
            f"identity and compliance screening passed with no flags, "
            f"and the underwriting model approved the risk at {uw.get('confidence', 0.8):.1%} confidence. "
            f"A final premium of ${final_premium:,.2f} has been calculated and the policy has been issued "
            f"under policy number {policy_number}."
        ) if final_premium and policy_number else (
            f"The application from {insured_name} has been approved with a {risk_band_str} risk classification. "
            f"All compliance, risk and underwriting checks passed successfully."
        )
    else:
        reason_summary = " ".join(decline_reasons[:2])  # top 2 reasons
        overall_summary = (
            f"The application from {insured_name} for {property_addr} has been declined. "
            f"{reason_summary} "
            f"The pipeline processed all {len(factors)} agent checks before reaching this decision."
        )

    return {
        "decision": decision,
        "overall_summary": overall_summary,
        "decision_factors": factors,
        "decline_reasons": decline_reasons,
        "risk_band": risk_band_str,
        "final_premium": final_premium,
        "policy_number": policy_number,
        "audit_score": audit_score,
    }


# ──────────────────────────────────────────────────────────────────────────────
# DB WRITE — gold_audit_log
# ──────────────────────────────────────────────────────────────────────────────

def _save_audit_record(engine, submission_id: int, audit: dict) -> int:
    """
    Insert audit record into gold_audit_log.
    Returns the new audit record ID.
    """
    with engine.begin() as conn:
        result = conn.execute(
            text("""
                INSERT INTO gold_audit_log (
                    submission_id,
                    decision,
                    overall_summary,
                    decision_factors_json,
                    decline_reasons_json,
                    risk_band,
                    final_premium,
                    policy_number,
                    audit_score,
                    audited_at
                ) VALUES (
                    :submission_id,
                    :decision,
                    :overall_summary,
                    :decision_factors_json,
                    :decline_reasons_json,
                    :risk_band,
                    :final_premium,
                    :policy_number,
                    :audit_score,
                    :audited_at
                )
            """),
            {
                "submission_id":        submission_id,
                "decision":             audit["decision"],
                "overall_summary":      audit["overall_summary"],
                "decision_factors_json": json.dumps(audit["decision_factors"]),
                "decline_reasons_json": json.dumps(audit["decline_reasons"]),
                "risk_band":            audit.get("risk_band"),
                "final_premium":        audit.get("final_premium"),
                "policy_number":        audit.get("policy_number"),
                "audit_score":          audit.get("audit_score", 0),
                "audited_at":           datetime.utcnow(),
            }
        )
        return result.lastrowid


# ──────────────────────────────────────────────────────────────────────────────
# MAIN ENTRY POINT
# ──────────────────────────────────────────────────────────────────────────────

def run_audit_agent(submission_id: int) -> dict:
    """
    Main function. Call this from app.py after NOVA completes.

    Usage in app.py pipeline:
        from agent6_audit import run_audit_agent
        audit_result = run_audit_agent(submission_id)

    Returns full audit dict including plain-English summary,
    decision factors, and the saved audit_log_id.
    """
    logger.info(f"[AXIOM] Starting audit for submission_id={submission_id}")

    engine = get_engine()

    # Fetch all agent outputs
    submission = _fetch_submission(engine, submission_id)
    kyc        = _fetch_kyc(engine, submission_id)
    risk       = _fetch_property_risk(engine, submission_id)
    uw         = _fetch_underwriting(engine, submission_id)
    pricing    = _fetch_pricing(engine, submission_id)
    issuance   = _fetch_issuance(engine, submission_id)

    if not submission:
        logger.warning(f"[AXIOM] Submission {submission_id} not found.")
        return {"error": f"Submission {submission_id} not found."}

    # Build the audit summary
    audit = _build_audit_summary(submission, kyc, risk, uw, pricing, issuance)

    # Save to gold_audit_log
    try:
        audit_log_id = _save_audit_record(engine, submission_id, audit)
        audit["audit_log_id"] = audit_log_id
        logger.info(f"[AXIOM] Audit saved — id={audit_log_id}, decision={audit['decision']}")
    except Exception as e:
        logger.error(f"[AXIOM] Failed to save audit record: {e}")
        audit["audit_log_id"] = None
        audit["save_error"] = str(e)

    return audit


# ──────────────────────────────────────────────────────────────────────────────
# FETCH EXISTING AUDIT (for UI display)
# ──────────────────────────────────────────────────────────────────────────────

def get_audit_for_submission(submission_id: int) -> dict | None:
    """
    Fetch the most recent audit record for a submission.
    Use this in the Flask route to display the audit panel in the UI.
    """
    engine = get_engine()
    with engine.connect() as conn:
        row = conn.execute(
            text("""
                SELECT * FROM gold_audit_log
                WHERE submission_id = :sid
                ORDER BY audited_at DESC
                LIMIT 1
            """),
            {"sid": submission_id}
        ).mappings().fetchone()

    if not row:
        return None

    record = dict(row)
    # Parse JSON columns back
    record["decision_factors"] = json.loads(record.get("decision_factors_json") or "[]")
    record["decline_reasons"]  = json.loads(record.get("decline_reasons_json") or "[]")
    return record