main.py

# ======================================================================================
# DART — COMPLETE SINGLE-FILE STREAMLIT APPLICATION
# Includes the dashboard, Email Agent UI, embedded example automation/template,
# condition evaluation, change detection, HTML/text email generation, and SMTP delivery.
# No separate Python helper modules, JSON templates, or HTML template files are required.
# Runtime automation settings and baseline snapshots are created automatically under
# EMAIL_AGENT_DIR so alerts can persist across application runs.
# ======================================================================================

import streamlit as st
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
import os
import json
import re
import ssl
import smtplib
import hashlib
import html
import uuid
from datetime import datetime, timezone
from email.message import EmailMessage
from pathlib import Path

# ======================================================================================
#  PAGE CONFIG
# ======================================================================================
st.set_page_config(
    page_title="DART — CMS Data Assurance Reconciliation Tracker",
    page_icon="🎯",
    layout="wide",
    initial_sidebar_state="expanded",
)

# ======================================================================================
#  SYSTEM LABELS  (the two systems being compared)
# ======================================================================================
SYS_A = "NCH/CVM"            # System A  (National Claims History / CWF Version)
SYS_B = "Shared Systems"     # System B  (SS – FISS / MCS / VMS)

# ======================================================================================
#  THEME / PALETTE
# ======================================================================================
PRIMARY = "#8B6A00"      # gold
ACCENT = "#12805C"       # green (good)
WARN = "#F59E0B"         # amber
DANGER = "#DC2626"       # red (bad)
PURPLE = "#7C3AED"
SLATE = "#64748B"
SYS_A_COLOR = "#8B6A00"   # gold for System A
SYS_B_COLOR = "#C9920A"   # amber-gold for System B

CLASS_COLORS = {
    "Match": "#12805C",
    "Different": "#F59E0B",
    "Missing": "#DC2626",
    "View is not in the MDCR VDM": "#7C3AED",
    "Table is empty": "#94A3B8",
    "Unspecified": "#CBD5E1",
}
DISP_COLORS = {
    "Good": "#12805C",
    "Action needed": "#DC2626",
    "Not analyzed": "#F59E0B",
    "Table is empty": "#94A3B8",
    "Other": "#64748B",
}
SEQ_BLUE = px.colors.sequential.Blues

# ======================================================================================
#  USE-CASE KNOWLEDGE BASE  (from the SSDS analysis presentation)
#  Maps each data sub-classification to the business meaning + recommended action.
# ======================================================================================
USE_CASES = {
    "Data matches / columns match": {
        "category": "Match",
        "severity": "good",
        "title": "Perfect Match — Data & Columns Align",
        "desc": (
            f"The {SYS_A} and {SYS_B} columns hold identical data. This is the ideal "
            "situation between the two claim streams."
        ),
        "action": "No action needed.",
    },
    "Different format-SS has more info": {
        "category": "Match",
        "severity": "good",
        "title": "Acceptable Match — SS Has More Information",
        "desc": (
            f"The {SYS_B} version contains more information than the {SYS_A} version. "
            "Counted as an acceptable match because it reflects richer data in SS "
            "(a bonus of using Shared Systems)."
        ),
        "action": "No action needed — SS provides bonus detail.",
    },
    "NCH is missing": {
        "category": "Match",
        "severity": "good",
        "title": "Acceptable Match — NCH Missing, SS Has More Data",
        "desc": (
            f"{SYS_A} is missing data that {SYS_B} contains for some streams. Still an "
            "acceptable match since more data is reflected in SS. Null CD columns should "
            "contain '~' per IDR standards."
        ),
        "action": "No action needed — SS is more complete.",
    },
    "Zeros, tilde, blanks, null": {
        "category": "Match",
        "severity": "warn",
        "title": "Match — But Values Are Zeros / Blanks / Null / Tildes",
        "desc": (
            "Columns match, but the data is always zeros, blanks, null, or tildes ('~'). "
            "Considered an acceptable match (all are valid null values), yet a populated "
            "'~' in '_CD' columns can indicate an ETL issue."
        ),
        "action": "Monitor — potential ETL indicator on _CD columns.",
    },
    "NCH and SS have different values": {
        "category": "Different",
        "severity": "danger",
        "title": "Different Values — Reconciliation Required",
        "desc": (
            f"The data in {SYS_A} and {SYS_B} differ and analysis determined that "
            "reconciliation between the two feeds is necessary (e.g. columns not "
            "populated for some streams, or differing date formats)."
        ),
        "action": "Reconcile the NCH ↔ SS feeds.",
    },
    "Do Nothing. SS date is more accurate.": {
        "category": "Different",
        "severity": "good",
        "title": "Different — SS Is More Accurate (No Action)",
        "desc": (
            f"Values differ, but {SYS_B} data is more accurate than the estimated "
            f"{SYS_A} data. NCH estimates the value while SS contains the actual value."
        ),
        "action": "No action needed — SS is authoritative.",
    },
    "Not expected to match": {
        "category": "Different",
        "severity": "good",
        "title": "Different — Not Expected to Match",
        "desc": (
            "Analysis determined the difference is acceptable because these IDR-derived "
            "columns are neither expected nor required to match."
        ),
        "action": "No action needed — mismatch is by design.",
    },
    "Data in different column": {
        "category": "Missing",
        "severity": "warn",
        "title": "Missing — Data Lives in a Different Column",
        "desc": (
            f"The {SYS_A} and {SYS_B} concepts match, but the data resides in a different "
            "column. The SS maintainer would need to harmonize so NCH and SS report the "
            "same way."
        ),
        "action": "SS to harmonize column placement.",
    },
    "Scheduled payment concept does not apply in SS": {
        "category": "Missing",
        "severity": "good",
        "title": "Missing — Concept Does Not Apply in SS",
        "desc": (
            "Acceptable: NCH estimates the scheduled payment date because CWF does not "
            "know the real payment date at IDR load time, whereas SS holds the actual "
            "payment date. The concept simply does not apply to SS."
        ),
        "action": "No action needed — concept not applicable.",
    },
    "SS is missing this data": {
        "category": "Missing",
        "severity": "danger",
        "title": "Missing — SS Is Not Sending This Data",
        "desc": (
            f"Analysis determined the {SYS_B} stream is not sending this information to "
            "the IDR."
        ),
        "action": "Have SS send the data, or confirm IDR users need it.",
    },
    "Data not available": {
        "category": "Missing",
        "severity": "warn",
        "title": "Missing — Data Not Available",
        "desc": "The data is not available in the source for comparison.",
        "action": "Investigate source availability.",
    },
    "Table is empty": {
        "category": "Table is empty",
        "severity": "warn",
        "title": "Table Is Empty",
        "desc": "The table has only default values for every target column.",
        "action": "Confirm whether the table should be populated.",
    },
    "Unspecified": {
        "category": "Unspecified",
        "severity": "warn",
        "title": "Unspecified Sub-Classification",
        "desc": "No sub-classification was recorded for these fields.",
        "action": "Review and classify.",
    },
}

# ======================================================================================
#  GLOBAL STYLING (CSS)
# ======================================================================================
st.markdown(
    """
    <style>
    /* ---- App background & fonts ---- */
    .stApp { background-color: #FDFCF8; }
    html, body, [class*="css"] { font-family: 'Segoe UI', 'Inter', sans-serif; }

    /* ---- Hero banner ---- */
    /* Text block inside the hero; container provides the gradient background */
    .hero {
        background: transparent;
        padding: 26px 34px 12px 34px; border-radius: 0; color: #ffffff;
        box-shadow: none; margin-bottom: 0;
    }
    .hero h1 { color:#ffffff; margin:0; font-size:2.0rem; font-weight:700; letter-spacing:-0.02em; }
    .hero p  { color:#FFF8DC; margin:6px 0 0 0; font-size:1.02rem; max-width:100%; overflow-wrap:anywhere; }
    .hero .tagpill {
        display:inline-block; background:rgba(255,255,255,0.18); color:#fff;
        padding:3px 12px; border-radius:20px; font-size:0.78rem; font-weight:600;
        margin-top:12px; margin-right:8px; letter-spacing:0.03em;
    }
    /* The Streamlit container block that wraps both text and nav dropdowns */
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) {
        background: linear-gradient(135deg, #6B4E00 0%, #C9920A 100%);
        border-radius: 16px;
        box-shadow: 0 6px 20px rgba(107,78,0,0.30);
        padding: 0 20px 24px 20px;
        margin-bottom: 8px;
    }
    /* Expanders inside the hero container */
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] {
        background: rgba(255,255,255,0.16) !important;
        border: 1px solid rgba(255,255,255,0.28) !important;
        border-radius: 14px !important;
        backdrop-filter: blur(8px);
        box-shadow: 0 6px 16px rgba(2,6,23,0.12) !important;
    }
    /* Closed expander header: white text and icon on the gold background */
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open]) > summary {
        background-color: transparent !important;
        color: #ffffff !important;
        border-radius: 14px !important;
    }

    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open]) > summary p,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open]) > summary span,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open]) > summary div,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open]) > summary svg,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open])
        [data-testid="stExpanderToggleIcon"] {
        color: #ffffff !important;
        fill: #ffffff !important;
        stroke: #ffffff !important;
        font-weight: 700 !important;
        opacity: 1 !important;
    }

    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details:not([open]) > summary:hover {
        background-color: rgba(255,255,255,0.12) !important;
    }

    /* Open expander header: dark text and icon on Streamlit's white header */
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary {
        background-color: rgba(255,255,255,0.97) !important;
        color: #0F172A !important;
        border-radius: 13px 13px 0 0 !important;
    }

    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary p,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary span,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary div,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary svg,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open]
        [data-testid="stExpanderToggleIcon"] {
        color: #0F172A !important;
        fill: #0F172A !important;
        stroke: #0F172A !important;
        font-weight: 700 !important;
        opacity: 1 !important;
    }

    /* Preserve readable colors while an open header is hovered or focused */
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary:hover,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary:focus,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary:focus-visible {
        background-color: #FFF8DC !important;
        color: #0F172A !important;
    }

    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary:hover *,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary:focus *,
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) [data-testid="stExpander"] details[open] > summary:focus-visible * {
        color: #0F172A !important;
        fill: #0F172A !important;
        stroke: #0F172A !important;
    }
    /* Nav buttons inside the hero */
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) .stButton > button {
        width:100%; justify-content:center; margin:4px 0; border-radius:10px;
        background: rgba(255,255,255,0.95); color:#0f172a;
        border: 1px solid rgba(255,255,255,0.8);
        font-weight:700; transition: all 0.18s ease;
    }
    [data-testid="stVerticalBlock"]:has(
        > .element-container > [data-testid="stMarkdown"] .hero-anchor
    ) .stButton > button[kind="primary"] {
        background: linear-gradient(135deg, #8B6A00 0%, #C9920A 100%) !important;
        color:#ffffff !important; border-color:#8B6A00 !important;
        box-shadow:0 0 0 2px rgba(255,255,255,0.25) inset;
    }

    /* ---- KPI cards ---- */
    .kpi-card {
        background:#ffffff; border-radius:14px; padding:16px 20px;
        box-shadow:0 2px 10px rgba(15,23,42,0.06); border:1px solid #eef1f5;
        margin-bottom:6px; height:100%; overflow-wrap:anywhere; word-break:break-word;
    }
    .kpi-label { font-size:0.74rem; color:#64748b; text-transform:uppercase; letter-spacing:0.06em; font-weight:700; }
    .kpi-value { font-size:1.85rem; font-weight:750; color:#0f172a; margin-top:4px; line-height:1.1; }
    .kpi-sub   { font-size:0.82rem; color:#64748b; margin-top:4px; }

    /* ---- Section headers ---- */
    .section-title { font-size:1.25rem; font-weight:700; color:#0f172a; margin:8px 0 2px 0; }
    .section-sub   { font-size:0.9rem;  color:#64748b; margin-bottom:12px; }

    /* ---- Insight callout ---- */
    .insight {
        background:#ffffff; border-left:5px solid #8B6A00; border-radius:8px;
        padding:12px 16px; margin:8px 0; box-shadow:0 1px 4px rgba(15,23,42,0.05);
        color:#0f172a; font-size:0.92rem;
    }
    .insight.good   { border-left-color:#12805C; }
    .insight.warn   { border-left-color:#F59E0B; }
    .insight.danger { border-left-color:#DC2626; }

    /* ---- AI agent bubble ---- */
    .ai-bubble {
        background:#ffffff; border:1px solid #e6ebf3; border-radius:14px;
        padding:16px 18px 16px 54px; margin:10px 0; position:relative;
        box-shadow:0 2px 10px rgba(15,23,42,0.06); color:#0f172a; font-size:0.95rem;
        line-height:1.5;
    }
    .ai-bubble:before {
        content:"🤖"; position:absolute; left:14px; top:14px; font-size:1.4rem;
        background:linear-gradient(135deg,#8B6A00,#C9920A); -webkit-background-clip:text;
    }
    .ai-bubble.warn   { border-left:5px solid #F59E0B; }
    .ai-bubble.danger { border-left:5px solid #DC2626; }
    .ai-bubble.good   { border-left:5px solid #12805C; }
    .ai-bubble b { color:#6B4E00; }

    /* ---- Use-case card ---- */
    .uc-card {
        background:#ffffff; border-radius:14px; padding:16px 18px; margin-bottom:12px;
        box-shadow:0 2px 10px rgba(15,23,42,0.06); border:1px solid #eef1f5;
    }
    .uc-badge {
        display:inline-block; padding:2px 10px; border-radius:20px; font-size:0.72rem;
        font-weight:700; color:#fff; letter-spacing:0.03em; margin-left:6px;
    }
    .uc-title { font-size:1.02rem; font-weight:700; color:#0f172a; }
    .uc-desc  { font-size:0.9rem; color:#334155; margin-top:6px; }
    .uc-action{ font-size:0.86rem; color:#8B6A00; font-weight:600; margin-top:8px; }

    /* ---- Methodology card ---- */
    .info-card {
        background:#ffffff; border:1px solid #eef1f5; border-radius:14px;
        padding:16px 18px; margin-bottom:12px; box-shadow:0 2px 10px rgba(15,23,42,0.06);
        overflow-wrap:anywhere; word-break:break-word;
    }
    .info-card ul { margin:6px 0 0 18px; padding:0; color:#334155; line-height:1.6; }
    .info-card li { margin-bottom:4px; }

    /* ---- Top navigation ---- */
    .top-nav {
        display:flex; flex-wrap:wrap; align-items:center; justify-content:space-between; gap:12px;
        padding:12px 16px; margin-bottom:12px; border-radius:18px;
        background: linear-gradient(135deg, #FFFEF0 0%, #FFF8DC 100%);
        border:1px solid #E8D8A0; box-shadow:0 6px 18px rgba(107,78,0,0.08);
    }
    .nav-brand {
        font-size:0.95rem; font-weight:800; letter-spacing:0.06em; text-transform:uppercase;
        color:#8B6A00;
    }
    .nav-actions { display:flex; flex-wrap:wrap; gap:8px; align-items:center; }
    .nav-pill {
        background:#ffffff; color:#475569; border:1px solid #e6ebf3; border-radius:999px;
        font-weight:700; padding:8px 14px; min-width:140px; transition: all 0.2s ease-in-out;
        box-shadow:0 1px 3px rgba(15,23,42,0.04);
    }
    .nav-pill:hover { border-color:#8B6A00; color:#8B6A00; }
    .nav-pill.active {
        background: linear-gradient(135deg, #8B6A00 0%, #C9920A 100%) !important;
        color:#ffffff !important; border-color:#8B6A00 !important; box-shadow:0 6px 16px rgba(139,106,0,0.25);
    }


    /* ---- Sidebar ---- */
    section[data-testid="stSidebar"] { background:#1C1200; }
    section[data-testid="stSidebar"] * { color:#e5edf7; }
    section[data-testid="stSidebar"] .stMultiSelect label,
    section[data-testid="stSidebar"] .stSlider label { color:#cbd8e8 !important; font-weight:600; }

    /* ---- Native metric tweak ---- */
    div[data-testid="stMetric"] {
        background:#ffffff; border:1px solid #eef1f5; border-radius:12px;
        padding:14px 16px; box-shadow:0 2px 8px rgba(15,23,42,0.05);
    }

    footer, #MainMenu { visibility: hidden; }
    </style>
    """,
    unsafe_allow_html=True,
)


# ======================================================================================
#  HELPERS
# ======================================================================================
def clean_pct(val):
    """Return a match rate as a fraction in [0, 1] or NaN."""
    if val is None:
        return np.nan
    if isinstance(val, str):
        s = val.strip().replace("%", "")
        if s == "" or s == "-":
            return np.nan
        try:
            num = float(s)
        except ValueError:
            return np.nan
    else:
        try:
            num = float(val)
        except (ValueError, TypeError):
            return np.nan
    if pd.isna(num):
        return np.nan
    if num > 1.5:            # stored as a percentage (e.g. 99.9) -> fraction
        num = num / 100.0
    return num


def normalize_disposition(val):
    s = str(val).strip().lower()
    if s in ("good",):
        return "Good"
    if s in ("action needed", "needs action", "action", "m"):
        return "Action needed"
    if s in ("not analyzed", "not analysed"):
        return "Not analyzed"
    if s in ("table is empty", "empty"):
        return "Table is empty"
    return "Other"


def normalize_subclass(val):
    s = str(val).strip()
    low = s.lower()
    if low in ("", "nan", "none"):
        return "Unspecified"
    if low.startswith("not expected"):
        return "Not expected to match"
    return s


def normalize_recommendation(val):
    s = str(val).strip()
    if s == "" or s.lower() in ("nan", "none"):
        return "(none)"
    # merge trivial case differences
    fixups = {
        "idr needs to derive": "IDR needs to derive",
        "idr to derive": "IDR needs to derive",
    }
    return fixups.get(s.lower(), s)


def is_blank(series):
    s = series.astype(str).str.strip().str.lower()
    return s.isin(["", "-", "nan", "none"])


def kpi_card(col, label, value, sub="", accent=PRIMARY):
    sub_html = f'<div class="kpi-sub">{sub}</div>' if sub else ""
    col.markdown(
        f"""
        <div class="kpi-card" style="border-top:4px solid {accent};">
            <div class="kpi-label">{label}</div>
            <div class="kpi-value">{value}</div>
            {sub_html}
        </div>
        """,
        unsafe_allow_html=True,
    )


def insight(text, kind=""):
    st.markdown(f'<div class="insight {kind}">{text}</div>', unsafe_allow_html=True)


def ai_say(text, kind=""):
    st.markdown(f'<div class="ai-bubble {kind}">{text}</div>', unsafe_allow_html=True)


def section(title, sub=""):
    sub_html = f'<div class="section-sub">{sub}</div>' if sub else ""
    st.markdown(f'<div class="section-title">{title}</div>{sub_html}', unsafe_allow_html=True)


def methodology_card(title, bullets, accent=PRIMARY):
    bullet_html = "".join(f"<li>{b}</li>" for b in bullets)
    st.markdown(
        f"""
        <div class="info-card" style="border-left:5px solid {accent};">
            <div class="section-title" style="margin:0;">{title}</div>
            <ul>{bullet_html}</ul>
        </div>
        """,
        unsafe_allow_html=True,
    )


def style_fig(fig, height=380):
    fig.update_layout(
        height=height,
        margin=dict(l=10, r=10, t=40, b=10),
        paper_bgcolor="rgba(0,0,0,0)",
        plot_bgcolor="rgba(0,0,0,0)",
        font=dict(family="Segoe UI, sans-serif", color="#0f172a", size=12),
        legend=dict(bgcolor="rgba(0,0,0,0)"),
        title=dict(font=dict(size=14, color="#0f172a")),
    )
    fig.update_xaxes(showgrid=True, gridcolor="#e6ebf2", zeroline=False)
    fig.update_yaxes(showgrid=True, gridcolor="#e6ebf2", zeroline=False)
    return fig


def fmt_int(n):
    try:
        return f"{int(n):,}"
    except (ValueError, TypeError):
        return "—"


def claim_weighted_match_rate(df, matched_col="MatchedClaims", unmatched_col="NotMatchedClaims"):
    """Return the claim/event-volume-weighted match rate and its component counts.

    This remains available as a secondary diagnostic metric:
        total matched / (total matched + total not matched)

    It is intentionally not the dashboard's primary "Average Match Rate" because
    very high-volume fields can dominate the result.
    """
    if df is None or df.empty:
        return np.nan, 0, 0, 0

    matched = pd.to_numeric(df[matched_col], errors="coerce").fillna(0).sum()
    unmatched = pd.to_numeric(df[unmatched_col], errors="coerce").fillna(0).sum()
    total = matched + unmatched
    rate = (matched / total) if total > 0 else np.nan
    return rate, int(matched), int(unmatched), int(total)


def comparable_field_scope(df):
    """Return directly comparable rows with a usable field-level MatchRate.

    ClassificationClean = Missing is excluded from the average because those rows
    describe absent data, data living in another column, or a concept that does not
    apply in Shared Systems. They are important data-quality findings, but they are
    not direct value-to-value field comparisons.
    """
    if df is None:
        return pd.DataFrame()
    if df.empty:
        return df.copy()

    out = df.copy()

    if "ClassificationClean" in out.columns:
        out = out[out["ClassificationClean"].isin(["Match", "Different"])]
    elif "Classification" in out.columns:
        cls = out["Classification"].astype(str).str.strip()
        out = out[cls.isin(["Match", "Different"])]

    if "MatchRate" in out.columns:
        out = out[out["MatchRate"].notna()]

    return out


def average_comparable_match_rate(df):
    """Arithmetic mean of MatchRate across directly comparable field rows."""
    scope = comparable_field_scope(df)
    if scope.empty:
        return np.nan, 0
    rate = pd.to_numeric(scope["MatchRate"], errors="coerce").mean()
    return rate, len(scope)


# ======================================================================================
#  DATA LOADING & PREPARATION
# ======================================================================================
MAIN_PATH = "Data/All Streams June 2026.xlsx"
RULES_PATH = "Data/CMS Version NCH to NCH STTM 12_13_2024.xlsx"

MATCHED_COL = "1/1/24 - 12/31/24 Matched claims"
NOTMATCHED_COL = "1/1/24 - 12/31/24 Not Matched claims"


@st.cache_data(show_spinner="Loading and preparing data...", ttl=300)
def load_data():
    # ---------------- MAIN comparison dataset ----------------
    df = pd.read_excel(MAIN_PATH, sheet_name="Sheet1")

    df["MatchedClaims"] = pd.to_numeric(df[MATCHED_COL], errors="coerce")
    df["NotMatchedClaims"] = pd.to_numeric(df[NOTMATCHED_COL], errors="coerce")
    df["TotalClaims"] = df["MatchedClaims"].fillna(0) + df["NotMatchedClaims"].fillna(0)

    df["MatchRate"] = df["% matched"].apply(clean_pct)
    derived = df["MatchedClaims"] / df["TotalClaims"].replace(0, np.nan)
    df["MatchRate"] = df["MatchRate"].fillna(derived)
    # Claim-level mismatch rate (share of claims that did NOT match)
    df["MismatchRate"] = df["NotMatchedClaims"] / df["TotalClaims"].replace(0, np.nan)

    # Normalize categorical text
    required_cols = ["Stream", "Classification", "Disposition", "% matched", MATCHED_COL, NOTMATCHED_COL]
    missing_cols = [c for c in required_cols if c not in df.columns]
    if missing_cols:
        raise ValueError(f"The source workbook is missing required columns: {', '.join(missing_cols)}")

    df["Classification"] = df["Classification"].astype(str).str.strip()
    df["ClassificationClean"] = df["Classification"].replace({"": "Unspecified", "nan": "Unspecified"})
    df["Disposition"] = df["Disposition"].astype(str).str.strip()
    df["DispositionClean"] = df["Disposition"].apply(normalize_disposition)
    df["Stream"] = df["Stream"].astype(str).str.strip()
    if "Sub-Classification" in df.columns:
        df["SubClassClean"] = df["Sub-Classification"].apply(normalize_subclass)
    else:
        df["SubClassClean"] = "Unspecified"
    if "Recommendation" in df.columns:
        df["RecommendationClean"] = df["Recommendation"].apply(normalize_recommendation)
    else:
        df["RecommendationClean"] = "(none)"
    if "Lynette's recommendation" in df.columns:
        df["LynetteRec"] = df["Lynette's recommendation"].fillna("").astype(str).str.strip()
    else:
        df["LynetteRec"] = ""

    # A column "needs change" when it is flagged for action or not fully matching
    df["NeedsChange"] = df["DispositionClean"].eq("Action needed")

    # Stringify remaining object columns to avoid Arrow serialization issues
    for col in df.columns:
        if df[col].dtype == "object" and col not in (
            "MatchedClaims", "NotMatchedClaims", "TotalClaims", "MatchRate", "MismatchRate",
        ):
            df[col] = df[col].fillna("").astype(str)

    # ---------------- RULES dataset (business rules / STTM) ----------------
    df_rules = pd.read_excel(RULES_PATH, sheet_name="Lynnette's Version")
    if str(df_rules.columns[0]).startswith("Unnamed"):
        new_header = df_rules.iloc[0]
        df_rules = df_rules[2:]
        df_rules.columns = new_header
    df_rules = df_rules.reset_index(drop=True)
    for col in df_rules.columns:
        if df_rules[col].dtype == "object":
            df_rules[col] = df_rules[col].fillna("").astype(str).str.strip()

    extra = {}
    for sheet in ["NCH STTM to NCH Data & % Pop", "Missing STTM Cols with Data"]:
        try:
            ex = pd.read_excel(RULES_PATH, sheet_name=sheet)
            for col in ex.columns:
                if ex[col].dtype == "object":
                    ex[col] = ex[col].fillna("").astype(str)
            extra[sheet] = ex
        except Exception:
            pass

    # ---------------- CROSS-DATASET LINK ----------------
    rules_lk = df_rules.copy()
    if "NCH Target Column" in rules_lk.columns and "NCH Target Table" in rules_lk.columns:
        valid = ~is_blank(rules_lk["NCH Target Column"])
        rules_lk = rules_lk[valid]
        keep_cols = [c for c in ["NCH Target Table", "NCH Target Column", "Business Rule",
                                 "Data Match? Y/N", "MCS Table", "MCS Column",
                                 "NCH Cobol Copy book Field Name"] if c in rules_lk.columns]
        rules_lk = rules_lk[keep_cols].drop_duplicates(
            subset=["NCH Target Table", "NCH Target Column"], keep="first"
        )
        rules_lk = rules_lk.rename(columns={
            "Business Rule": "Rule_BusinessRule",
            "Data Match? Y/N": "Rule_DataMatch",
            "MCS Table": "Rule_MCSTable",
            "MCS Column": "Rule_MCSColumn",
            "NCH Cobol Copy book Field Name": "Rule_CobolField",
        })
        df = df.merge(rules_lk, on=["NCH Target Table", "NCH Target Column"], how="left")

    df["HasRule"] = df.get("Rule_BusinessRule", "").astype(str).str.strip().replace(
        {"-": "", "nan": ""}
    ).ne("")

    def rule_verdict(v):
        s = str(v).strip().upper()
        if s.startswith("Y"):
            return "Match"
        if s.startswith("N"):
            return "No Match"
        return ""

    df["RuleVerdict"] = df.get("Rule_DataMatch", "").apply(rule_verdict)
    df["ComparisonVerdict"] = np.where(df["ClassificationClean"].eq("Match"), "Match", "No Match")
    df["Agreement"] = np.select(
        [df["RuleVerdict"].eq(""), df["RuleVerdict"].eq(df["ComparisonVerdict"])],
        ["No rule verdict", "Agree"],
        default="Conflict",
    )

    return df, df_rules, extra


try:
    df_all, df_rules, extra_sheets = load_data()
except Exception as e:
    st.error(f"Error loading data: {e}")
    st.stop()



# ======================================================================================
#  REUSABLE ANALYSIS ENGINE  (used by the main data AND the custom-upload demo tab)
# ======================================================================================
def analyze_from_counts(df, label_col, matched_col, notmatched_col):
    """Given a pre-computed comparison table (one row per column/field with matched &
    not-matched counts), return a tidy per-column summary + headline metrics."""
    out = pd.DataFrame()
    out["Field"] = df[label_col].astype(str)
    out["Matched"] = pd.to_numeric(df[matched_col], errors="coerce").fillna(0)
    out["Mismatched"] = pd.to_numeric(df[notmatched_col], errors="coerce").fillna(0)
    out["Total"] = out["Matched"] + out["Mismatched"]
    out["Match Rate"] = out["Matched"] / out["Total"].replace(0, np.nan)
    out["Mismatch Rate"] = out["Mismatched"] / out["Total"].replace(0, np.nan)
    out["Status"] = np.where(out["Mismatched"] <= 0, "Full match", "Mismatch present")
    return out


def compare_two_systems(dfa, dfb, key_cols, compare_cols):
    """Compare two raw system extracts row-by-row on a shared key.
    Returns (per-column summary, merged frame)."""
    a = dfa.copy()
    b = dfb.copy()
    for k in key_cols:
        a[k] = a[k].astype(str).str.strip()
        b[k] = b[k].astype(str).str.strip()
    merged = a.merge(b, on=key_cols, how="outer", suffixes=("__A", "__B"), indicator=True)
    both = merged["_merge"] == "both"
    only_a = int((merged["_merge"] == "left_only").sum())
    only_b = int((merged["_merge"] == "right_only").sum())

    rows = []
    for c in compare_cols:
        ca, cb = f"{c}__A", f"{c}__B"
        if ca not in merged.columns or cb not in merged.columns:
            continue
        va = merged.loc[both, ca].astype(str).str.strip()
        vb = merged.loc[both, cb].astype(str).str.strip()
        matched = int((va == vb).sum())
        mismatched = int((va != vb).sum())
        total = matched + mismatched
        rows.append({
            "Field": c,
            "Matched": matched,
            "Mismatched": mismatched,
            "Total": total,
            "Match Rate": (matched / total) if total else np.nan,
            "Mismatch Rate": (mismatched / total) if total else np.nan,
            "Status": "Full match" if mismatched == 0 else "Mismatch present",
        })
    summary = pd.DataFrame(rows)
    meta = {"only_a": only_a, "only_b": only_b, "shared_keys": int(both.sum())}
    return summary, meta


def generic_narrative(summary, sys_a=SYS_A, sys_b=SYS_B):
    """Produce AI-agent style statements from a per-field summary frame."""
    lines = []
    if summary.empty:
        return [("No comparable columns were found.", "warn")]
    n_fields = len(summary)
    mismatch_fields = int((summary["Mismatched"] > 0).sum())
    pct_fields = mismatch_fields / n_fields if n_fields else 0
    total_matched = int(summary["Matched"].sum())
    total_mismatched = int(summary["Mismatched"].sum())
    grand = total_matched + total_mismatched
    overall_rate = (total_matched / grand) if grand else np.nan

    kind = "good" if pct_fields < 0.1 else ("warn" if pct_fields < 0.3 else "danger")
    lines.append((
        f"I compared <b>{n_fields}</b> columns between <b>{sys_a}</b> and <b>{sys_b}</b>. "
        f"<b>{mismatch_fields}</b> of them (<b>{pct_fields:.0%}</b>) show at least one "
        f"record-level mismatch.", kind,
    ))
    if grand:
        lines.append((
            f"Across all compared records, <b>{total_mismatched:,}</b> did not match "
            f"(<b>{(1-overall_rate):.2%}</b> of {grand:,}), while "
            f"<b>{total_matched:,}</b> matched cleanly.", "good" if overall_rate >= 0.99 else "warn",
        ))

    worst = summary[summary["Mismatched"] > 0].sort_values("Mismatch Rate", ascending=False).head(5)
    if not worst.empty:
        names = ", ".join(f"<b>{r['Field']}</b> ({r['Mismatch Rate']:.1%})" for _, r in worst.iterrows())
        lines.append((f"🔎 Top columns by mismatch percentage: {names}.", "danger"))

    vol = summary.sort_values("Mismatched", ascending=False).head(1)
    if not vol.empty and vol.iloc[0]["Mismatched"] > 0:
        r = vol.iloc[0]
        lines.append((
            f"📦 The largest mismatch by volume is <b>{r['Field']}</b> with "
            f"<b>{int(r['Mismatched']):,}</b> mismatched records.", "warn",
        ))
    lines.append((
        f"✅ Recommendation: <b>{mismatch_fields}</b> column(s) need attention / reconciliation; "
        f"the remaining <b>{n_fields - mismatch_fields}</b> are aligned.",
        "good" if mismatch_fields == 0 else "warn",
    ))
    return lines


def render_summary_block(summary, sys_a=SYS_A, sys_b=SYS_B, key_prefix="gen"):
    """Render KPIs + charts + drill-down for a per-field summary frame (shared UI)."""
    if summary.empty:
        st.info("No comparable columns to analyze.")
        return

    n_fields = len(summary)
    mismatch_fields = int((summary["Mismatched"] > 0).sum())
    total_matched = int(summary["Matched"].sum())
    total_mismatched = int(summary["Mismatched"].sum())
    grand = total_matched + total_mismatched
    overall_rate = (total_matched / grand) if grand else np.nan

    c1, c2, c3, c4, c5 = st.columns(5)
    kpi_card(c1, "Columns Compared", f"{n_fields:,}", "fields analyzed", PRIMARY)
    kpi_card(c2, "Columns w/ Mismatch", f"{mismatch_fields:,}",
             f"{mismatch_fields/n_fields:.0%} of columns", DANGER)
    kpi_card(c3, "Matched Records", f"{total_matched:,}", "row-level", ACCENT)
    kpi_card(c4, "Mismatched Records", f"{total_mismatched:,}", "row-level", WARN)
    kpi_card(c5, "Overall Match Rate",
             f"{overall_rate:.2%}" if pd.notna(overall_rate) else "—", "record-level", PRIMARY)

    st.markdown("<br>", unsafe_allow_html=True)
    for text, kind in generic_narrative(summary, sys_a, sys_b):
        ai_say(text, kind)

    st.markdown("<br>", unsafe_allow_html=True)
    lcol, rcol = st.columns([1.3, 1])
    with lcol:
        section("Matched vs. Mismatched by Column", "Top 20 columns by total records.")
        top = summary.sort_values("Total", ascending=False).head(20)
        melt = top.melt(id_vars="Field", value_vars=["Matched", "Mismatched"],
                        var_name="Result", value_name="Records")
        fig = px.bar(melt, x="Records", y="Field", color="Result", orientation="h",
                     barmode="stack",
                     color_discrete_map={"Matched": ACCENT, "Mismatched": DANGER})
        fig.update_layout(yaxis={"categoryorder": "total ascending"})
        st.plotly_chart(style_fig(fig, height=460), use_container_width=True,
                        key=f"{key_prefix}_bar")
    with rcol:
        section("Top 5 Columns by Mismatch %", "Where the two systems diverge most.")
        worst = summary[summary["Mismatched"] > 0].sort_values(
            "Mismatch Rate", ascending=False).head(5)
        if worst.empty:
            st.success("No columns with mismatches — the systems fully agree.")
        else:
            fig = px.bar(worst, x="Mismatch Rate", y="Field", orientation="h",
                         color="Mismatch Rate", color_continuous_scale="Reds",
                         text=worst["Mismatch Rate"].apply(lambda v: f"{v:.1%}"))
            fig.update_layout(coloraxis_showscale=False,
                              yaxis={"categoryorder": "total ascending"})
            fig.update_xaxes(tickformat=".0%")
            st.plotly_chart(style_fig(fig, height=460), use_container_width=True,
                            key=f"{key_prefix}_worst")

    section("Per-Column Comparison", "Full matched / mismatched breakdown with actions.")
    show = summary.copy()
    show["Action"] = np.where(show["Mismatched"] > 0, "Review / reconcile", "None")
    st.dataframe(
        show.sort_values("Mismatch Rate", ascending=False).style.format({
            "Matched": "{:,.0f}", "Mismatched": "{:,.0f}", "Total": "{:,.0f}",
            "Match Rate": "{:.2%}", "Mismatch Rate": "{:.2%}",
        }),
        use_container_width=True, hide_index=True,
    )
    csv = show.to_csv(index=False).encode("utf-8")
    st.download_button("⬇️ Download comparison summary (CSV)", data=csv,
                       file_name="two_system_comparison.csv", mime="text/csv",
                       key=f"{key_prefix}_dl")


def read_uploaded(file):
    """Read an uploaded CSV or Excel file into a DataFrame (all columns stringified)."""
    name = file.name.lower()
    if name.endswith(".csv"):
        df = pd.read_csv(file)
    else:
        df = pd.read_excel(file)
    for c in df.columns:
        if df[c].dtype == "object":
            df[c] = df[c].fillna("").astype(str)
    return df


@st.cache_data(show_spinner=False)
def make_sample_systems():
    """Generate two synthetic system extracts so the demo works out of the box."""
    rng = np.random.default_rng(42)
    n = 600
    claim_id = np.arange(100000, 100000 + n)
    status = rng.choice(["PAID", "DENIED", "PENDING"], size=n, p=[0.7, 0.2, 0.1])
    provider = rng.integers(1000, 1050, size=n)
    amount = np.round(rng.uniform(50, 5000, size=n), 2)
    diag = rng.choice(["A01", "B20", "C34", "D50", "E11"], size=n)
    svc_date = pd.to_datetime("2024-01-01") + pd.to_timedelta(rng.integers(0, 364, size=n), unit="D")

    sys_a = pd.DataFrame({
        "claim_id": claim_id, "status_code": status, "provider_id": provider,
        "payment_amount": amount, "diagnosis_code": diag,
        "service_date": svc_date.strftime("%Y-%m-%d"),
    })
    sys_b = sys_a.copy()
    # Introduce controlled, realistic differences
    m = rng.random(n) < 0.06            # 6% payment differences
    sys_b.loc[m, "payment_amount"] = np.round(sys_b.loc[m, "payment_amount"] * rng.uniform(0.9, 1.1, m.sum()), 2)
    m2 = rng.random(n) < 0.02           # 2% status differences
    sys_b.loc[m2, "status_code"] = rng.choice(["PAID", "DENIED", "PENDING"], size=m2.sum())
    m3 = rng.random(n) < 0.15           # 15% date-format differences (still same day)
    sys_b.loc[m3, "service_date"] = pd.to_datetime(sys_b.loc[m3, "service_date"]).dt.strftime("%m/%d/%Y")
    for c in sys_a.columns:
        sys_a[c] = sys_a[c].astype(str)
        sys_b[c] = sys_b[c].astype(str)
    return sys_a, sys_b


# ======================================================================================
#  EMAIL AGENT — AUTOMATION, CHANGE DETECTION, EMAIL BUILDING, AND SMTP DELIVERY
# ======================================================================================
# The agent stores automation templates and lightweight snapshots on disk. On Render or
# another hosted environment, point EMAIL_AGENT_DIR at a persistent disk so templates and
# baselines survive redeployments.
EMAIL_AGENT_DIR = Path(os.environ.get("EMAIL_AGENT_DIR", "Data/email_agent"))
EMAIL_TEMPLATE_PATH = EMAIL_AGENT_DIR / "automation_templates.json"
EMAIL_SNAPSHOT_DIR = EMAIL_AGENT_DIR / "snapshots"
EMAIL_AGENT_AUTO_RUN = os.environ.get("EMAIL_AGENT_AUTO_RUN", "false").strip().lower() in (
    "1", "true", "yes", "y", "on"
)
EMAIL_AGENT_MIN_CHECK_SECONDS = int(os.environ.get("EMAIL_AGENT_MIN_CHECK_SECONDS", "300"))
EMAIL_PREVIEW_ROW_LIMIT = 30
NULL_TOKEN = "<DART_NULL>"

EMAIL_OPERATORS = [
    "Is blank (empty or null)",
    "Is null / NaN",
    "Is empty string",
    "Equals",
    "Does not equal",
    "Contains",
    "Does not contain",
    "Starts with",
    "Greater than",
    "Greater than or equal to",
    "Less than",
    "Less than or equal to",
    "Before date",
    "After date",
]


def _ensure_email_agent_dirs():
    EMAIL_AGENT_DIR.mkdir(parents=True, exist_ok=True)
    EMAIL_SNAPSHOT_DIR.mkdir(parents=True, exist_ok=True)


def _get_secret_or_env(name, default=""):
    """Read a setting from environment variables first, then Streamlit secrets."""
    env_value = os.environ.get(name)
    if env_value not in (None, ""):
        return env_value
    try:
        if name in st.secrets:
            return st.secrets[name]
    except Exception:
        pass
    return default


def _as_bool(value, default=False):
    if value is None:
        return default
    return str(value).strip().lower() in ("1", "true", "yes", "y", "on")


def _utc_now_iso():
    return datetime.now(timezone.utc).replace(microsecond=0).isoformat()


def _safe_template_id(template_id):
    safe = re.sub(r"[^A-Za-z0-9_-]", "", str(template_id))
    return safe or uuid.uuid4().hex[:12]


def _snapshot_path(template_id):
    _ensure_email_agent_dirs()
    return EMAIL_SNAPSHOT_DIR / f"{_safe_template_id(template_id)}.csv"


def load_email_automations():
    _ensure_email_agent_dirs()
    if not EMAIL_TEMPLATE_PATH.exists():
        return []
    try:
        raw = json.loads(EMAIL_TEMPLATE_PATH.read_text(encoding="utf-8"))
        return raw if isinstance(raw, list) else []
    except Exception:
        return []


def save_email_automations(templates):
    _ensure_email_agent_dirs()
    temp_path = EMAIL_TEMPLATE_PATH.with_suffix(".tmp")
    temp_path.write_text(json.dumps(templates, indent=2, ensure_ascii=False), encoding="utf-8")
    temp_path.replace(EMAIL_TEMPLATE_PATH)


def upsert_email_automation(template):
    templates = load_email_automations()
    template = dict(template)
    template["id"] = _safe_template_id(template.get("id") or uuid.uuid4().hex[:12])
    template["updated_at"] = _utc_now_iso()
    if not template.get("created_at"):
        template["created_at"] = template["updated_at"]

    replaced = False
    for i, existing in enumerate(templates):
        if existing.get("id") == template["id"]:
            templates[i] = template
            replaced = True
            break
    if not replaced:
        templates.append(template)
    save_email_automations(templates)
    return template


def delete_email_automation(template_id):
    templates = [t for t in load_email_automations() if t.get("id") != template_id]
    save_email_automations(templates)
    path = _snapshot_path(template_id)
    if path.exists():
        path.unlink()


def _parse_recipients(value):
    recipients = [x.strip() for x in re.split(r"[,;]", str(value or "")) if x.strip()]
    return recipients


def validate_recipients(value):
    recipients = _parse_recipients(value)
    email_re = re.compile(r"^[^\s@]+@[^\s@]+\.[^\s@]+$")
    invalid = [x for x in recipients if not email_re.match(x)]
    return recipients, invalid


def smtp_settings():
    host = str(_get_secret_or_env("SMTP_HOST", "")).strip()
    username = str(_get_secret_or_env("SMTP_USERNAME", "")).strip()
    password = str(_get_secret_or_env("SMTP_PASSWORD", ""))
    from_email = str(_get_secret_or_env("SMTP_FROM", username)).strip()
    from_name = str(_get_secret_or_env("SMTP_FROM_NAME", "DART Email Agent")).strip()
    port_raw = _get_secret_or_env("SMTP_PORT", "587")
    try:
        port = int(port_raw)
    except (TypeError, ValueError):
        port = 587
    return {
        "host": host,
        "port": port,
        "username": username,
        "password": password,
        "from_email": from_email,
        "from_name": from_name,
        "use_tls": _as_bool(_get_secret_or_env("SMTP_USE_TLS", "true"), True),
        "use_ssl": _as_bool(_get_secret_or_env("SMTP_USE_SSL", "false"), False),
    }


def smtp_is_configured():
    cfg = smtp_settings()
    return bool(cfg["host"] and cfg["from_email"])


def _snapshot_value(value):
    if value is None or pd.isna(value):
        return NULL_TOKEN
    if isinstance(value, (pd.Timestamp, datetime)):
        return value.isoformat()
    if isinstance(value, float):
        if np.isnan(value):
            return NULL_TOKEN
        return format(value, ".15g")
    return str(value).strip()


def _valid_columns(columns, df):
    return [c for c in columns if c in df.columns]


def build_snapshot_frame(df, template):
    """Build a stable row-key/hash snapshot using the template's identity and monitor fields."""
    source = df.reset_index(drop=True).copy()
    identity_columns = _valid_columns(template.get("identity_columns", []), source)
    if not identity_columns:
        identity_columns = [source.columns[0]] if len(source.columns) else []

    requested_monitor = template.get("monitor_columns", [])
    condition_columns = [r.get("column") for r in template.get("conditions", [])]
    display_columns = template.get("display_columns", [])
    monitor_columns = _valid_columns(
        list(dict.fromkeys(identity_columns + requested_monitor + condition_columns + display_columns)),
        source,
    )
    if not monitor_columns:
        monitor_columns = list(source.columns)

    normalized = pd.DataFrame(index=source.index)
    for col in monitor_columns:
        normalized[col] = source[col].map(_snapshot_value)

    if identity_columns:
        base_key = normalized[identity_columns].agg(" | ".join, axis=1)
    else:
        base_key = pd.Series(["ROW"] * len(source), index=source.index)
    occurrence = base_key.groupby(base_key, sort=False).cumcount().astype(str)
    row_key = base_key + " | occurrence=" + occurrence

    row_payload = normalized[monitor_columns].agg("\x1f".join, axis=1)
    row_hash = row_payload.map(lambda s: hashlib.sha256(s.encode("utf-8")).hexdigest())

    snapshot = normalized.copy()
    snapshot.insert(0, "_row_hash", row_hash)
    snapshot.insert(0, "_row_key", row_key)
    snapshot.insert(0, "_source_position", np.arange(len(source)))
    return source, snapshot, identity_columns, monitor_columns


def write_email_snapshot(df, template):
    _, snapshot, _, _ = build_snapshot_frame(df, template)
    path = _snapshot_path(template["id"])
    snapshot.to_csv(path, index=False)
    return path


def read_email_snapshot(template_id):
    path = _snapshot_path(template_id)
    if not path.exists():
        return None
    try:
        return pd.read_csv(path, dtype=str, keep_default_na=False)
    except Exception:
        return None


def detect_email_agent_changes(df, template):
    """Return new/changed rows and metadata. The first run establishes a baseline only."""
    source, current, identity_columns, monitor_columns = build_snapshot_frame(df, template)
    previous = read_email_snapshot(template["id"])
    meta = {
        "baseline_missing": previous is None,
        "identity_columns": identity_columns,
        "monitor_columns": monitor_columns,
        "duplicate_identity_count": 0,
        "source_rows": len(source),
    }

    if identity_columns and not source.empty:
        normalized_keys = source[identity_columns].astype(str).agg(" | ".join, axis=1)
        meta["duplicate_identity_count"] = int(normalized_keys.duplicated(keep=False).sum())

    if previous is None:
        empty = source.iloc[0:0].copy()
        empty["_Change Type"] = pd.Series(dtype=str)
        empty["_Changed Fields"] = pd.Series(dtype=str)
        empty["_Previous Values"] = pd.Series(dtype=str)
        return empty, meta

    prev_by_key = previous.set_index("_row_key", drop=False)
    rows = []
    trigger_mode = template.get("trigger_mode", "New or changed rows")

    for _, snap_row in current.iterrows():
        key = snap_row["_row_key"]
        pos = int(snap_row["_source_position"])
        change_type = None
        changed_fields = []
        previous_values = []

        if key not in prev_by_key.index:
            change_type = "New"
            changed_fields = [c for c in monitor_columns if c not in identity_columns]
        else:
            prev_row = prev_by_key.loc[key]
            if isinstance(prev_row, pd.DataFrame):
                prev_row = prev_row.iloc[0]
            if str(prev_row.get("_row_hash", "")) != str(snap_row.get("_row_hash", "")):
                change_type = "Changed"
                for col in monitor_columns:
                    old = str(prev_row.get(col, NULL_TOKEN))
                    new = str(snap_row.get(col, NULL_TOKEN))
                    if old != new:
                        changed_fields.append(col)
                        old_show = "Null" if old == NULL_TOKEN else old
                        new_show = "Null" if new == NULL_TOKEN else new
                        previous_values.append(f"{col}: {old_show} → {new_show}")

        if change_type is None:
            continue
        if trigger_mode == "New rows only" and change_type != "New":
            continue

        record = source.iloc[pos].to_dict()
        record["_Change Type"] = change_type
        record["_Changed Fields"] = ", ".join(changed_fields) if changed_fields else "—"
        record["_Previous Values"] = "; ".join(previous_values[:8]) if previous_values else "—"
        rows.append(record)

    return pd.DataFrame(rows), meta


def _blank_mask(series):
    text = series.astype(str).str.strip().str.lower()
    return series.isna() | text.isin(["", "nan", "none", "null", "<na>"])


def condition_mask(series, operator, target=""):
    operator = str(operator)
    text = series.astype(str).str.strip()
    text_lower = text.str.lower()
    target_text = str(target or "").strip()
    target_lower = target_text.lower()

    if operator == "Is blank (empty or null)":
        return _blank_mask(series)
    if operator == "Is null / NaN":
        return series.isna()
    if operator == "Is empty string":
        return series.notna() & text.eq("")
    if operator == "Equals":
        if pd.api.types.is_numeric_dtype(series):
            try:
                return pd.to_numeric(series, errors="coerce").eq(float(target_text))
            except ValueError:
                pass
        return text_lower.eq(target_lower)
    if operator == "Does not equal":
        return ~condition_mask(series, "Equals", target_text)
    if operator == "Contains":
        return text_lower.str.contains(re.escape(target_lower), na=False)
    if operator == "Does not contain":
        return ~text_lower.str.contains(re.escape(target_lower), na=False)
    if operator == "Starts with":
        return text_lower.str.startswith(target_lower, na=False)

    if operator in (
        "Greater than", "Greater than or equal to", "Less than", "Less than or equal to"
    ):
        numeric = pd.to_numeric(series, errors="coerce")
        try:
            threshold = float(target_text)
        except ValueError:
            return pd.Series(False, index=series.index)
        if operator == "Greater than":
            return numeric > threshold
        if operator == "Greater than or equal to":
            return numeric >= threshold
        if operator == "Less than":
            return numeric < threshold
        return numeric <= threshold

    if operator in ("Before date", "After date"):
        dates = pd.to_datetime(series, errors="coerce")
        target_date = pd.to_datetime(target_text, errors="coerce")
        if pd.isna(target_date):
            return pd.Series(False, index=series.index)
        return dates < target_date if operator == "Before date" else dates > target_date

    return pd.Series(False, index=series.index)


def evaluate_email_conditions(df, template):
    if df.empty:
        return df.copy()
    conditions = [r for r in template.get("conditions", []) if r.get("column") in df.columns]
    if not conditions:
        return df.copy()

    masks = []
    for rule in conditions:
        masks.append(condition_mask(df[rule["column"]], rule.get("operator", "Equals"), rule.get("value", "")))

    logic = template.get("condition_logic", "ALL conditions")
    combined = masks[0].copy()
    for mask in masks[1:]:
        combined = (combined | mask) if logic == "ANY condition" else (combined & mask)
    return df[combined.fillna(False)].copy()


def current_matching_rows(df, template):
    rows = evaluate_email_conditions(df, template)
    rows = rows.copy()
    rows["_Change Type"] = "Current match"
    rows["_Changed Fields"] = "Matches configured conditions"
    rows["_Previous Values"] = "—"
    return rows


def _condition_sentence(rule):
    col = rule.get("column", "(field)")
    operator = rule.get("operator", "Equals")
    value = str(rule.get("value", "")).strip()
    no_value = operator in ("Is blank (empty or null)", "Is null / NaN", "Is empty string")
    return f"{col} {operator.lower()}" if no_value else f"{col} {operator.lower()} {value}"


def _email_display_value(value, column):
    if value is None or pd.isna(value):
        return "Null"
    if column in ("MatchRate", "MismatchRate"):
        try:
            return f"{float(value):.2%}"
        except (TypeError, ValueError):
            pass
    if column in ("MatchedClaims", "NotMatchedClaims", "TotalClaims"):
        try:
            return f"{float(value):,.0f}"
        except (TypeError, ValueError):
            pass
    text = str(value)
    return text if text.strip() else "(empty)"


def build_structured_email(template, matches, is_test=False):
    name = template.get("name", "DART data alert")
    subject_prefix = template.get("subject_prefix", "[DART Alert]")
    subject = f"{subject_prefix} {'TEST — ' if is_test else ''}{name}: {len(matches):,} matching change(s)"
    greeting = template.get("greeting", "Hello,")
    intro = template.get(
        "email_intro",
        "DART detected new or changed reconciliation data that matches your saved requirements.",
    )

    display_columns = [c for c in template.get("display_columns", []) if c in matches.columns]
    meta_columns = [c for c in ["_Change Type", "_Changed Fields", "_Previous Values"] if c in matches.columns]
    display_columns = list(dict.fromkeys(meta_columns + display_columns))
    if not display_columns:
        display_columns = list(matches.columns[:10])

    condition_lines = [_condition_sentence(r) for r in template.get("conditions", [])]
    logic_label = template.get("condition_logic", "ALL conditions")
    streams = []
    if "Stream" in matches.columns:
        streams = sorted(str(v) for v in matches["Stream"].dropna().unique() if str(v).strip())
    unmatched_total = None
    if "NotMatchedClaims" in matches.columns:
        unmatched_total = pd.to_numeric(matches["NotMatchedClaims"], errors="coerce").fillna(0).sum()
    action_count = None
    if "DispositionClean" in matches.columns:
        action_count = int(matches["DispositionClean"].astype(str).eq("Action needed").sum())

    rows_html = []
    for _, row in matches.head(EMAIL_PREVIEW_ROW_LIMIT).iterrows():
        cells = "".join(
            f"<td style='padding:8px;border:1px solid #e5e7eb;vertical-align:top;'>"
            f"{html.escape(_email_display_value(row.get(col), col))}</td>"
            for col in display_columns
        )
        rows_html.append(f"<tr>{cells}</tr>")
    header_html = "".join(
        f"<th style='padding:8px;border:1px solid #d1d5db;background:#f8fafc;text-align:left;'>"
        f"{html.escape(col)}</th>" for col in display_columns
    )

    summary_cards = [
        ("Matching changes", f"{len(matches):,}"),
        ("Streams affected", f"{len(streams):,}" if streams else "—"),
    ]
    if unmatched_total is not None:
        summary_cards.append(("Not-matched claims", f"{unmatched_total:,.0f}"))
    if action_count is not None:
        summary_cards.append(("Action-needed rows", f"{action_count:,}"))

    cards_html = "".join(
        f"<td style='padding:10px 16px;border:1px solid #eadfb8;background:#fffdf5;'>"
        f"<div style='font-size:11px;color:#64748b;text-transform:uppercase;font-weight:700;'>{html.escape(label)}</div>"
        f"<div style='font-size:22px;color:#0f172a;font-weight:750;'>{html.escape(value)}</div></td>"
        for label, value in summary_cards
    )
    rules_html = "".join(f"<li>{html.escape(line)}</li>" for line in condition_lines)
    truncated_note = ""
    if len(matches) > EMAIL_PREVIEW_ROW_LIMIT:
        truncated_note = (
            f"<p style='color:#64748b;font-size:12px;'>Showing the first {EMAIL_PREVIEW_ROW_LIMIT:,} "
            f"of {len(matches):,} matching changes. The attached CSV contains all matches.</p>"
        )

    body_html = f"""
    <div style="font-family:Segoe UI,Arial,sans-serif;color:#0f172a;max-width:1100px;margin:auto;">
      <div style="background:linear-gradient(135deg,#6B4E00,#C9920A);padding:22px 26px;border-radius:14px 14px 0 0;color:white;">
        <div style="font-size:12px;font-weight:700;letter-spacing:.08em;text-transform:uppercase;">DART Email Agent</div>
        <h2 style="margin:5px 0 0 0;color:white;">{html.escape(name)}</h2>
      </div>
      <div style="border:1px solid #eadfb8;border-top:0;padding:24px 26px;border-radius:0 0 14px 14px;background:white;">
        <p>{html.escape(greeting)}</p>
        <p>{html.escape(intro)}</p>
        <table style="border-collapse:collapse;margin:18px 0;"><tr>{cards_html}</tr></table>
        <h3 style="margin-bottom:6px;">Requirements that triggered this alert</h3>
        <p style="margin-top:0;color:#475569;">Rule logic: <b>{html.escape(logic_label)}</b></p>
        <ul>{rules_html}</ul>
        {f"<p><b>Streams:</b> {html.escape(', '.join(streams))}</p>" if streams else ""}
        <h3>Matching data changes</h3>
        <div style="overflow-x:auto;">
          <table style="border-collapse:collapse;width:100%;font-size:12px;">
            <thead><tr>{header_html}</tr></thead>
            <tbody>{''.join(rows_html)}</tbody>
          </table>
        </div>
        {truncated_note}
        <p style="margin-top:22px;"><b>Recommended next step:</b> Review the affected NCH target fields and use the RecommendationClean value to route reconciliation work.</p>
        <p style="color:#64748b;font-size:12px;margin-top:24px;">Generated automatically by DART on {html.escape(_utc_now_iso())}.</p>
      </div>
    </div>
    """

    text_lines = [
        greeting,
        "",
        intro,
        "",
        f"Automation: {name}",
        f"Matching changes: {len(matches):,}",
        f"Rule logic: {logic_label}",
        "Requirements:",
    ]
    text_lines.extend(f"- {line}" for line in condition_lines)
    if streams:
        text_lines.append(f"Streams: {', '.join(streams)}")
    if unmatched_total is not None:
        text_lines.append(f"Total not-matched claims: {unmatched_total:,.0f}")
    text_lines.append("")
    text_lines.append("Matching data changes:")
    text_lines.append(matches[display_columns].head(EMAIL_PREVIEW_ROW_LIMIT).to_string(index=False))
    text_lines.append("")
    text_lines.append("Review the affected NCH target fields and use RecommendationClean to route reconciliation work.")
    return subject, "\n".join(text_lines), body_html, display_columns


def send_structured_email(template, matches, is_test=False):
    recipients, invalid = validate_recipients(template.get("recipient_email", ""))
    if not recipients or invalid:
        raise ValueError(f"Invalid recipient email address(es): {', '.join(invalid) or 'none supplied'}")

    cfg = smtp_settings()
    if not cfg["host"] or not cfg["from_email"]:
        raise RuntimeError("SMTP is not configured. Set SMTP_HOST and SMTP_FROM (plus credentials if required).")

    subject, text_body, html_body, display_columns = build_structured_email(template, matches, is_test=is_test)
    message = EmailMessage()
    message["Subject"] = subject
    message["From"] = f"{cfg['from_name']} <{cfg['from_email']}>"
    message["To"] = ", ".join(recipients)
    message.set_content(text_body)
    message.add_alternative(html_body, subtype="html")

    if template.get("include_csv", True) and not matches.empty:
        attachment_cols = [c for c in display_columns if c in matches.columns]
        csv_bytes = matches[attachment_cols].to_csv(index=False).encode("utf-8")
        message.add_attachment(
            csv_bytes,
            maintype="text",
            subtype="csv",
            filename=f"dart_alert_{_safe_template_id(template.get('id', 'alert'))}.csv",
        )

    if cfg["use_ssl"]:
        context = ssl.create_default_context()
        with smtplib.SMTP_SSL(cfg["host"], cfg["port"], context=context, timeout=30) as server:
            if cfg["username"]:
                server.login(cfg["username"], cfg["password"])
            server.send_message(message)
    else:
        with smtplib.SMTP(cfg["host"], cfg["port"], timeout=30) as server:
            server.ehlo()
            if cfg["use_tls"]:
                server.starttls(context=ssl.create_default_context())
                server.ehlo()
            if cfg["username"]:
                server.login(cfg["username"], cfg["password"])
            server.send_message(message)
    return subject


def establish_email_baseline(df, template):
    path = write_email_snapshot(df, template)
    return {"status": "Baseline established", "snapshot": str(path), "rows": len(df)}


def run_email_automation(df, template, send_email=True):
    """Check one saved automation against new/changed rows and update its baseline safely."""
    result = {
        "id": template.get("id"),
        "name": template.get("name"),
        "status": "Not run",
        "matches": 0,
        "checked_at": _utc_now_iso(),
    }
    changes, meta = detect_email_agent_changes(df, template)
    result["change_rows"] = len(changes)
    result["meta"] = meta

    if meta["baseline_missing"]:
        write_email_snapshot(df, template)
        result["status"] = "Baseline established; no email sent"
        return result

    matches = evaluate_email_conditions(changes, template)
    result["matches"] = len(matches)

    if matches.empty:
        write_email_snapshot(df, template)
        result["status"] = "Checked; no matching changes"
        return result

    if not send_email:
        result["status"] = "Matching changes found; email not sent"
        result["matched_rows"] = matches
        return result

    try:
        result["subject"] = send_structured_email(template, matches, is_test=False)
        write_email_snapshot(df, template)
        result["status"] = "Email sent"
    except Exception as exc:
        result["status"] = f"Email failed: {exc}"
        result["error"] = str(exc)
    result["matched_rows"] = matches
    return result


def run_enabled_email_automations(df):
    results = []
    templates = load_email_automations()
    changed = False
    for template in templates:
        if not template.get("enabled", False):
            continue
        result = run_email_automation(df, template, send_email=True)
        template["last_run_at"] = result.get("checked_at")
        template["last_status"] = result.get("status")
        template["last_match_count"] = result.get("matches", 0)
        results.append(result)
        changed = True
    if changed:
        save_email_automations(templates)
    return results


def _render_email_html_preview(subject, body_html, height=650):
    st.markdown(f"**Subject:** `{subject}`")
    st.components.v1.html(body_html, height=height, scrolling=True)


def _default_email_agent_template(df):
    columns = list(df.columns)
    key_defaults = [c for c in ["Stream", "NCH Target Table", "NCH Target Column"] if c in columns]
    display_defaults = [c for c in [
        "Stream", "NCH Target Table", "NCH Target Column", "SS Table",
        "DispositionClean", "MatchRate", "MismatchRate", "NotMatchedClaims",
        "SubClassClean", "RecommendationClean",
    ] if c in columns]
    conditions = []
    if "DispositionClean" in columns:
        conditions.append({"column": "DispositionClean", "operator": "Equals", "value": "Action needed"})
    if "NotMatchedClaims" in columns:
        conditions.append({"column": "NotMatchedClaims", "operator": "Greater than", "value": "0"})
    return {
        "id": "example_action_needed",
        "name": "Action-needed reconciliation alert",
        "recipient_email": "data.owner@example.gov",
        "enabled": False,
        "trigger_mode": "New or changed rows",
        "identity_columns": key_defaults,
        "monitor_columns": display_defaults,
        "condition_logic": "ALL conditions",
        "conditions": conditions,
        "display_columns": display_defaults,
        "subject_prefix": "[DART Alert]",
        "greeting": "Hello Data Owner,",
        "email_intro": "DART detected new or changed reconciliation data requiring review.",
        "include_csv": True,
    }


# ======================================================================================
#  TOP NAVIGATION
# ======================================================================================
if "active_view" not in st.session_state:
    st.session_state.active_view = "overview"

# Groq key: set GROQ_API_KEY env var on Render; falls back to empty for local dev
if "groq_api_key" not in st.session_state:
    st.session_state.groq_api_key = os.environ.get("GROQ_API_KEY", "")

nav_items = [
    ("overview", "📈 Executive Overview"),
    ("ai", "🤖 Intelligence Tracker"),
    ("compare", "⚖️ System A vs System B"),
    ("integrity", "🎯 Data Integrity"),
    ("email_agent", "✉️ Email Agent"),
    ("usecases", "📖 Use Cases"),
    ("connections", "🔗 Connections"),
    ("correlations", "📊 Correlations"),
    ("explorer", "🔍 Explorer"),
    ("rules", "📋 Business Rules"),
    ("actions", "🚨 Action Items"),
    ("custom", "🧪 Custom Data (Demo)"),
]

st.markdown("<br>", unsafe_allow_html=True)

# ======================================================================================
#  HERO HEADER
# ======================================================================================
analysis_items = [
    ("ai", "🤖 Intelligence Tracker"),
    ("compare", "⚖️ System A vs System B"),
    ("integrity", "🎯 Data Integrity"),
    ("email_agent", "✉️ Email Agent"),
    ("domains", "🗂️ Domain Analysis"),
    ("usecases", "📖 Use Cases"),
    ("chatbot", "🎯 Dartboard"),
]
data_items = [
    ("connections", "🔗 Connections"),
    ("correlations", "📊 Correlations"),
    ("explorer", "🔍 Explorer"),
    ("rules", "📋 Business Rules"),
    ("actions", "🚨 Action Items"),
    ("custom", "🧪 Custom Data (Demo)"),
]

def set_active_view(view):
    st.session_state.active_view = view

with st.container():
    # hidden anchor lets CSS :has() target this entire stVerticalBlock
    st.markdown(
        f"""
        <span class="hero-anchor" style="display:none"></span>
        <div class="hero">
            <h1>🎯 DART — CMS Data Assurance Reconciliation Tracker</h1>
            <p>Shared Systems Data Analysis Study (SSDS) &nbsp;•&nbsp; comparing
            <b>System A: {SYS_A}</b> ↔ <b>System B: {SYS_B}</b> — column-level match analysis,
            business-rule linkage &amp; AI-assisted insights</p>
            <span class="tagpill">📅 Period: 1/1/24 – 12/31/24</span>
            <span class="tagpill">⚖️ Two-system comparison</span>
            <span class="tagpill">🤖 Intelligence tracker</span>
            <span class="tagpill">🧪 Bring-your-own-data demo</span>
            <span class="tagpill">✔️ Source data verified</span>
        </div>
        """,
        unsafe_allow_html=True,
    )

    hero_nav_col1, hero_nav_col2 = st.columns([1, 1])
    with hero_nav_col1:
        with st.expander("📊 Analysis", expanded=False):
            st.button(
                "📈 Overview",
                key="hero_overview",
                use_container_width=True,
                type="primary" if st.session_state.active_view == "overview" else "secondary",
                on_click=set_active_view,
                args=("overview",),
            )
            for key, label in analysis_items:
                st.button(
                    label,
                    key=f"hero_{key}",
                    use_container_width=True,
                    type="primary" if st.session_state.active_view == key else "secondary",
                    on_click=set_active_view,
                    args=(key,),
                )

    with hero_nav_col2:
        with st.expander("🧭 Data & Insights", expanded=False):
            for key, label in data_items:
                st.button(
                    label,
                    key=f"hero_data_{key}",
                    use_container_width=True,
                    type="primary" if st.session_state.active_view == key else "secondary",
                    on_click=set_active_view,
                    args=(key,),
                )


# ======================================================================================
#  SIDEBAR FILTERS
# ======================================================================================
st.sidebar.markdown("### ⚙️ Global Filters")
st.sidebar.caption(f"Comparing {SYS_A} vs {SYS_B}. Filters apply to analysis tabs; saved email automations evaluate the full source dataset.")

streams = st.sidebar.multiselect(
    "Stream", options=sorted(df_all["Stream"].unique()),
    default=sorted(df_all["Stream"].unique()),
)
classifications = st.sidebar.multiselect(
    "Classification", options=sorted(df_all["ClassificationClean"].unique()),
    default=sorted(df_all["ClassificationClean"].unique()),
)
dispositions = st.sidebar.multiselect(
    "Disposition", options=sorted(df_all["DispositionClean"].unique()),
    default=sorted(df_all["DispositionClean"].unique()),
)
min_rate = st.sidebar.slider(
    "Minimum match rate (%)", min_value=0, max_value=100, value=0, step=5,
    help="Show only fields whose match rate is at or above this value (records with no rate are kept).",
)
rule_link = st.sidebar.radio(
    "Business-rule linkage",
    options=["All records", "Only with a linked rule", "Only without a rule"],
    index=0,
)

mask = (
    df_all["Stream"].isin(streams)
    & df_all["ClassificationClean"].isin(classifications)
    & df_all["DispositionClean"].isin(dispositions)
)
rate_mask = df_all["MatchRate"].isna() | (df_all["MatchRate"] >= min_rate / 100.0)
mask &= rate_mask
if rule_link == "Only with a linked rule":
    mask &= df_all["HasRule"]
elif rule_link == "Only without a rule":
    mask &= ~df_all["HasRule"]

fdf = df_all[mask].copy()


# Optional automatic agent execution. It is disabled by default and only runs when
# EMAIL_AGENT_AUTO_RUN=true. Snapshots prevent duplicate emails for unchanged data.
email_agent_auto_results = []
if EMAIL_AGENT_AUTO_RUN:
    now_ts = datetime.now(timezone.utc).timestamp()
    last_check = st.session_state.get("email_agent_last_auto_check", 0.0)
    if now_ts - last_check >= EMAIL_AGENT_MIN_CHECK_SECONDS:
        email_agent_auto_results = run_enabled_email_automations(df_all)
        st.session_state.email_agent_last_auto_check = now_ts
        st.session_state.email_agent_last_auto_results = email_agent_auto_results

st.sidebar.markdown("---")
st.sidebar.metric("Records in view", f"{len(fdf):,}", f"of {len(df_all):,} total")
st.sidebar.metric("Source workbook rows", f"{len(df_all):,}", "loaded and validated")
st.sidebar.markdown("---")
st.sidebar.info(
    f"This dashboard reconciles **{SYS_A}** vs **{SYS_B}** field comparisons and links "
    "them to the **NCH→NCH STTM business rules** to surface data-integrity gaps."
)
methodology_card(
    "Verified data inputs",
    [
        f"Workbook loaded: {MAIN_PATH}",
        f"Rows available: {len(df_all):,}",
        "Core fields detected: Stream, Classification, Disposition, matched claims, not-matched claims, and % matched.",
        "The app uses those values to calculate match rate, mismatch rate, and action recommendations.",
    ],
    ACCENT,
)

if fdf.empty:
    st.warning("No records match the current filters. Adjust the filters in the sidebar.")
    st.stop()


# --------------------------------------------------------------------------------------
#  TAB 1 — EXECUTIVE OVERVIEW
# --------------------------------------------------------------------------------------
def render_overview_tab():
    section("Executive Summary", f"High-level health of the {SYS_A} ↔ {SYS_B} reconciliation.")

    total = len(fdf)
    matches = int((fdf["ClassificationClean"] == "Match").sum())
    diffs = total - matches
    action_ct = int((fdf["DispositionClean"] == "Action needed").sum())
    avg_match_rate, comparable_fields = average_comparable_match_rate(fdf)
    event_weighted_rate, matched_claims, unmatched_claims, total_claims = claim_weighted_match_rate(fdf)

    c1, c2, c3, c4, c5 = st.columns(5)
    kpi_card(c1, "Columns Compared", f"{total:,}", "all field rows in view", PRIMARY)
    kpi_card(c2, "Matching Columns", f"{matches:,}", f"{matches/total:.1%} of total", ACCENT)
    kpi_card(c3, "Columns Needing Change", f"{action_ct:,}", "flagged Action needed", DANGER)
    kpi_card(c4, "Average Match Rate",
             f"{avg_match_rate:.1%}" if pd.notna(avg_match_rate) else "—",
             f"across {comparable_fields:,} comparable fields", PRIMARY)
    kpi_card(c5, "Events Not Matched", f"{unmatched_claims:,}",
             f"of {total_claims:,} event comparisons", WARN)

    st.markdown("<br>", unsafe_allow_html=True)
    methodology_card(
        "How this analysis works",
        [
            "Each row in the dashboard represents a field or column comparison between NCH/CVM and Shared Systems.",
            "Matched events are records where both systems agree for the same key; not-matched events are records where they differ.",
            "For an individual field, match rate is matched events divided by total compared events, while mismatch rate is not-matched events divided by total compared events.",
            "The dashboard's Average Match Rate is the arithmetic mean of field-level MatchRate values for directly comparable rows classified as Match or Different.",
            "Rows classified as Missing are excluded from the Average Match Rate because there is no direct value-to-value comparison; those rows remain visible everywhere else as data-quality findings.",
            f"For context, the separate claim-volume-weighted event rate in the current view is {event_weighted_rate:.1%} ({matched_claims:,} matched of {total_claims:,} event comparisons).",
            "A field is surfaced as needing action when the mismatch is meaningful or when the disposition is flagged as Action needed.",
        ],
        PRIMARY,
    )

    col_a, col_b = st.columns([1.4, 1])
    with col_a:
        section("Classification by Stream")
        cross = fdf.groupby(["Stream", "ClassificationClean"]).size().reset_index(name="Count")
        fig = px.bar(cross, x="Stream", y="Count", color="ClassificationClean",
                     color_discrete_map=CLASS_COLORS, barmode="stack",
                     labels={"ClassificationClean": "Classification"})
        st.plotly_chart(style_fig(fig), use_container_width=True, key="ov_class")
    with col_b:
        section("Disposition Mix")
        disp = fdf["DispositionClean"].value_counts().reset_index()
        disp.columns = ["Disposition", "Count"]
        fig = px.pie(disp, names="Disposition", values="Count", hole=0.55,
                     color="Disposition", color_discrete_map=DISP_COLORS)
        fig.update_traces(textposition="inside", textinfo="percent+label")
        st.plotly_chart(style_fig(fig), use_container_width=True, key="ov_disp")

    col_c, col_d = st.columns(2)
    with col_c:
        section("Average Match Rate by Stream",
                "Mean field-level match rate for directly comparable Match/Different rows.")
        comparable = comparable_field_scope(fdf)
        by_stream = (
            comparable.groupby("Stream", as_index=False)
            .agg(MatchRate=("MatchRate", "mean"),
                 ComparableFields=("MatchRate", "count"))
            .sort_values("MatchRate")
        )
        fig = px.bar(by_stream, x="MatchRate", y="Stream", orientation="h",
                     color="MatchRate", color_continuous_scale="RdYlGn", range_color=[0, 1],
                     text=by_stream["MatchRate"].apply(lambda v: f"{v:.1%}" if pd.notna(v) else "—"))
        fig.update_layout(coloraxis_showscale=False)
        fig.update_xaxes(tickformat=".0%", range=[0, 1])
        st.plotly_chart(style_fig(fig), use_container_width=True, key="ov_bystream")
    with col_d:
        section("Highest-Impact Gaps", "Discrepant fields ranked by not-matched event volume.")
        gaps = fdf[fdf["ClassificationClean"] != "Match"].copy()
        gaps = gaps.sort_values("NotMatchedClaims", ascending=False).head(10)
        show = gaps[["Stream", "NCH Target Table", "NCH Target Column", "NotMatchedClaims"]].rename(
            columns={"NCH Target Table": "Table", "NCH Target Column": "Column",
                     "NotMatchedClaims": "Unmatched Claims"})
        st.dataframe(show, use_container_width=True, hide_index=True)

    worst = by_stream.iloc[0] if not by_stream.empty else None
    if worst is not None:
        insight(
            f"📉 <b>{worst['Stream']}</b> has the lowest average comparable-field match rate "
            f"(<b>{worst['MatchRate']:.1%}</b>). Overall, <b>{matches/total:.0%}</b> of compared "
            f"fields fully match, with <b>{action_ct:,}</b> flagged for action.",
            "warn" if worst["MatchRate"] < 0.95 else "good",
        )


# --------------------------------------------------------------------------------------
#  TAB 2 — INTELLIGENCE TRACKER  (AI-assisted insights)
# --------------------------------------------------------------------------------------
def render_ai_tab():
    section("🤖 AI Intelligence Tracker",
            f"Automated read-out of where {SYS_A} and {SYS_B} disagree — and what to do about it.")

    total = len(fdf)
    mismatch_cols = int((fdf["ClassificationClean"] != "Match").sum())
    change_cols = int(fdf["NeedsChange"].sum())
    pct_mismatch = mismatch_cols / total if total else 0
    claims_at_risk = int(fdf.loc[fdf["NeedsChange"], "NotMatchedClaims"].fillna(0).sum())
    streams_affected = int(fdf.loc[fdf["NeedsChange"], "Stream"].nunique())

    c1, c2, c3, c4 = st.columns(4)
    kpi_card(c1, "% Columns Mismatch", f"{pct_mismatch:.1%}",
             f"{mismatch_cols:,} of {total:,} columns", DANGER)
    kpi_card(c2, "Columns to Change", f"{change_cols:,}", "flagged Action needed", WARN)
    kpi_card(c3, "Claims Tied to Actions", f"{claims_at_risk:,}", "unmatched claims", PRIMARY)
    kpi_card(c4, "Streams Affected", f"{streams_affected}", "need attention", ACCENT)

    st.markdown("<br>", unsafe_allow_html=True)
    methodology_card(
        "What the intelligence tracker is measuring",
        [
            "The tracker summarizes how many columns are not a full match, how many are flagged for change, and how many events are tied to those issues.",
            "Top 5 columns by mismatch percentage are ranked by the share of events that do not match; top 5 by volume are ranked by the absolute number of not-matched events.",
            "This gives both a rate-based view and a business-impact view so teams can prioritize the most urgent gaps.",
        ],
        DANGER,
    )

    # ---- AI narrative ----
    overall_kind = "good" if pct_mismatch < 0.15 else ("warn" if pct_mismatch < 0.35 else "danger")
    ai_say(
        f"I analyzed <b>{total:,}</b> column comparisons between <b>{SYS_A}</b> and "
        f"<b>{SYS_B}</b>. <b>{mismatch_cols:,}</b> columns (<b>{pct_mismatch:.1%}</b>) are "
        f"<b>not a full match</b>, and <b>{change_cols:,}</b> are explicitly flagged as "
        f"needing a change.", overall_kind,
    )

    # Top 5 columns by claim-level mismatch percentage (with real volume)
    cand = fdf[(fdf["TotalClaims"] > 0) & (fdf["NotMatchedClaims"].fillna(0) > 0)].copy()
    top_pct = cand.sort_values("MismatchRate", ascending=False).head(5)
    if not top_pct.empty:
        items = "<br>".join(
            f"&nbsp;&nbsp;• <b>{r['NCH Target Column']}</b> "
            f"({r['Stream']}) — <b>{r['MismatchRate']:.1%}</b> mismatch "
            f"({int(r['NotMatchedClaims']):,} claims)"
            for _, r in top_pct.iterrows()
        )
        ai_say(f"🔎 <b>Top 5 columns by mismatch percentage:</b><br>{items}", "danger")

    # Top 5 columns by unmatched claim VOLUME
    top_vol = cand.sort_values("NotMatchedClaims", ascending=False).head(5)
    if not top_vol.empty:
        items = "<br>".join(
            f"&nbsp;&nbsp;• <b>{r['NCH Target Column']}</b> "
            f"({r['Stream']}) — <b>{int(r['NotMatchedClaims']):,}</b> not-matched events "
            f"({r['MismatchRate']:.2%})"
            for _, r in top_vol.iterrows()
        )
        ai_say(f"📦 <b>Top 5 columns by not-matched event volume:</b><br>{items}", "warn")

    # Recommendation roll-up
    if change_cols > 0:
        rec_counts = (fdf.loc[fdf["NeedsChange"], "RecommendationClean"]
                      .value_counts().head(5))
        rec_items = "<br>".join(
            f"&nbsp;&nbsp;• <b>{k}</b> — {v} column(s)" for k, v in rec_counts.items()
        )
        ai_say(
            f"🛠️ <b>{change_cols:,} columns need to change.</b> Most common recommended "
            f"actions:<br>{rec_items}", "warn",
        )
    else:
        ai_say("✅ No columns are currently flagged for action in this view.", "good")

    st.markdown("<br>", unsafe_allow_html=True)
    gcol, hcol = st.columns([1, 1])
    with gcol:
        section("Top 5 Columns — Mismatch %", "Highest claim-level mismatch rate.")
        if not top_pct.empty:
            tp = top_pct.copy()
            tp["Label"] = tp["NCH Target Column"] + " (" + tp["Stream"] + ")"
            fig = px.bar(tp, x="MismatchRate", y="Label", orientation="h",
                         color="MismatchRate", color_continuous_scale="Reds",
                         text=tp["MismatchRate"].apply(lambda v: f"{v:.1%}"))
            fig.update_layout(coloraxis_showscale=False, yaxis={"categoryorder": "total ascending"})
            fig.update_xaxes(tickformat=".0%")
            st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="ai_toppct")
        else:
            st.success("No mismatches to rank in this view.")
    with hcol:
        section("Columns Needing Change by Stream")
        chg = fdf[fdf["NeedsChange"]]
        if not chg.empty:
            bys = chg["Stream"].value_counts().reset_index()
            bys.columns = ["Stream", "Columns"]
            fig = px.bar(bys, x="Columns", y="Stream", orientation="h",
                         color="Columns", color_continuous_scale="Reds")
            fig.update_layout(coloraxis_showscale=False, yaxis={"categoryorder": "total ascending"})
            st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="ai_chgstream")
        else:
            st.success("No columns flagged for change in this view.")

    section("Watch List — Columns to Change", "Ranked by not-matched event volume.")
    watch = fdf[fdf["NeedsChange"]].sort_values("NotMatchedClaims", ascending=False)
    wcols = [c for c in ["Stream", "NCH Target Table", "NCH Target Column", "SS Table",
                         "MatchRate", "MismatchRate", "NotMatchedClaims", "SubClassClean",
                         "RecommendationClean"] if c in watch.columns]
    st.dataframe(
        watch[wcols].rename(columns={
            "NCH Target Table": "Table", "NCH Target Column": "Column",
            "MatchRate": "Match Rate", "MismatchRate": "Mismatch Rate",
            "NotMatchedClaims": "Unmatched Claims", "SubClassClean": "Sub-Classification",
            "RecommendationClean": "Recommendation",
        }).style.format({"Match Rate": "{:.2%}", "Mismatch Rate": "{:.2%}",
                         "Unmatched Claims": "{:,.0f}"}),
        use_container_width=True, hide_index=True,
    )


# --------------------------------------------------------------------------------------
#  TAB 3 — SYSTEM A vs SYSTEM B
# --------------------------------------------------------------------------------------
def render_compare_tab():
    section(f"⚖️ {SYS_A} vs {SYS_B}",
            "For every column: total matched & not-matched events, match %, and the action to take.")

    matched_claims = int(fdf["MatchedClaims"].fillna(0).sum())
    unmatched_claims = int(fdf["NotMatchedClaims"].fillna(0).sum())
    grand = matched_claims + unmatched_claims
    event_weighted_rate = matched_claims / grand if grand else np.nan
    average_match_rate, comparable_fields = average_comparable_match_rate(fdf)
    missing_fields = int((fdf["ClassificationClean"] == "Missing").sum())
    full_match_cols = int((fdf["NotMatchedClaims"].fillna(0) == 0).sum())
    change_cols = int(fdf["NeedsChange"].sum())

    c1, c2, c3, c4, c5 = st.columns(5)
    kpi_card(c1, "Columns Compared", f"{len(fdf):,}", "field comparisons", PRIMARY)
    kpi_card(c2, "Events Matched", f"{matched_claims:,}", f"in {SYS_A} & {SYS_B}", ACCENT)
    kpi_card(c3, "Events Not Matched", f"{unmatched_claims:,}", "differ between systems", DANGER)
    kpi_card(c4, "Average Match Rate",
             f"{average_match_rate:.3%}" if pd.notna(average_match_rate) else "—",
             f"{comparable_fields:,} comparable fields", PRIMARY)
    kpi_card(c5, "Columns to Change", f"{change_cols:,}", "action needed", WARN)

    st.markdown("<br>", unsafe_allow_html=True)
    methodology_card(
        "What we matched on",
        [
            "For the built-in workbook, this view uses the provided matched and not-matched claim counts and the field-level % matched values.",
            "Average Match Rate is the arithmetic mean of MatchRate across directly comparable fields classified as Match or Different.",
            f"{missing_fields:,} rows classified as Missing are excluded from that average because they do not represent a direct value-to-value field comparison.",
            f"The claim-volume-weighted event rate is shown as context below: {event_weighted_rate:.3%}. It answers a different question and can be dominated by high-volume fields.",
            "In the custom-data demo, the app joins two raw extracts on a shared key and compares the selected columns row-by-row.",
        ],
        ACCENT,
    )

    insight(
        f"<b>Why the headline average is about {average_match_rate:.1%}:</b> "
        f"DART averages the field-level match percentages across {comparable_fields:,} "
        f"directly comparable Match/Different rows. The separate event-volume-weighted "
        f"rate is <b>{event_weighted_rate:.3%}</b>. Both are valid, but they answer "
        f"different questions.",
    )

    section("Matched vs. Not-Matched Events by Stream")
    grp = fdf.groupby("Stream").agg(
        Matched=("MatchedClaims", "sum"), Mismatched=("NotMatchedClaims", "sum")
    ).reset_index()
    melt = grp.melt(id_vars="Stream", value_vars=["Matched", "Mismatched"],
                    var_name="Result", value_name="Events")
    fig = px.bar(melt, x="Stream", y="Events", color="Result", barmode="group",
                 color_discrete_map={"Matched": ACCENT, "Mismatched": DANGER})
    fig.update_yaxes(title="Events (log scale)", type="log")
    st.plotly_chart(style_fig(fig, height=360), use_container_width=True, key="cmp_bystream")

    # ---- Summary -> drill down ----
    section("Summary → Drill Down", "Pick a stream and table to inspect column-level detail.")
    d1, d2 = st.columns(2)
    with d1:
        stream_pick = st.selectbox("Stream", options=["All streams"] + sorted(fdf["Stream"].unique()),
                                   key="cmp_stream")
    scope = fdf if stream_pick == "All streams" else fdf[fdf["Stream"] == stream_pick]
    with d2:
        tables = ["All tables"] + sorted(t for t in scope["NCH Target Table"].unique() if t)
        table_pick = st.selectbox("NCH Target Table", options=tables, key="cmp_table")
    if table_pick != "All tables":
        scope = scope[scope["NCH Target Table"] == table_pick]

    sm, sc, sr = st.columns(3)
    kpi_card(sm, "Columns in Scope", f"{len(scope):,}", stream_pick, PRIMARY)
    scope_rate, scope_comparable_fields = average_comparable_match_rate(scope)
    _, scope_matched, scope_unmatched, scope_total = claim_weighted_match_rate(scope)
    kpi_card(sc, "Not-Matched Events", f"{scope_unmatched:,}", "in scope", DANGER)
    kpi_card(sr, "Average Match Rate", f"{scope_rate:.2%}" if pd.notna(scope_rate) else "—",
             f"{scope_comparable_fields:,} comparable fields", ACCENT)

    detail_cols = [c for c in [
        "Stream", "NCH Target Table", "NCH Target Column", "SS Table", "SS Colmn if different",
        "MatchedClaims", "NotMatchedClaims", "MatchRate", "MismatchRate",
        "ClassificationClean", "SubClassClean", "DispositionClean", "RecommendationClean",
    ] if c in scope.columns]
    st.dataframe(
        scope[detail_cols].sort_values("NotMatchedClaims", ascending=False).rename(columns={
            "NCH Target Table": f"{SYS_A} Table", "NCH Target Column": f"{SYS_A} Column",
            "SS Table": f"{SYS_B} Table", "SS Colmn if different": f"{SYS_B} Column (if diff)",
            "MatchedClaims": "Matched", "NotMatchedClaims": "Mismatched",
            "MatchRate": "Match Rate", "MismatchRate": "Mismatch Rate",
            "ClassificationClean": "Classification", "SubClassClean": "Sub-Classification",
            "DispositionClean": "Disposition", "RecommendationClean": "Recommendation",
        }).style.format({"Matched": "{:,.0f}", "Mismatched": "{:,.0f}",
                         "Match Rate": "{:.2%}", "Mismatch Rate": "{:.2%}"}),
        use_container_width=True, hide_index=True,
    )
    csv = scope[detail_cols].to_csv(index=False).encode("utf-8")
    st.download_button("⬇️ Download this comparison (CSV)", data=csv,
                       file_name="system_comparison.csv", mime="text/csv", key="cmp_dl")


# --------------------------------------------------------------------------------------
#  TAB 4 — DATA INTEGRITY
# --------------------------------------------------------------------------------------
def render_integrity_tab():
    section("Data Integrity Health", "Where does the data diverge, and how badly?")

    rated = comparable_field_scope(fdf)
    overall_rate, comparable_fields = average_comparable_match_rate(fdf)
    event_weighted_rate, matched_events, unmatched_events, total_events = claim_weighted_match_rate(fdf)
    missing_fields = int((fdf["ClassificationClean"] == "Missing").sum())
    perfect = int((rated["MatchRate"] >= 0.9999).sum())
    below99 = int((rated["MatchRate"] < 0.99).sum())

    c1, c2, c3, c4 = st.columns(4)
    kpi_card(c1, "Comparable Fields", f"{comparable_fields:,}", "Match or Different", PRIMARY)
    kpi_card(c2, "Average Match Rate",
             f"{overall_rate:.1%}" if pd.notna(overall_rate) else "—",
             "equal weight per comparable field", ACCENT)
    kpi_card(c3, "Missing Fields Excluded", f"{missing_fields:,}", "tracked separately", DANGER)
    kpi_card(c4, "Fields Below 99%", f"{below99:,}", "among comparable fields", WARN)

    st.markdown("<br>", unsafe_allow_html=True)
    gcol, hcol = st.columns([1, 1.5])
    with gcol:
        section("Average Comparable-Field Match Rate",
                "Each directly comparable field contributes equally to this average.")
        fig = go.Figure(go.Indicator(
            mode="gauge+number",
            value=(overall_rate * 100) if pd.notna(overall_rate) else 0,
            number={"suffix": "%", "font": {"size": 40}},
            gauge={"axis": {"range": [0, 100]}, "bar": {"color": PRIMARY},
                   "steps": [{"range": [0, 90], "color": "#fde2e2"},
                             {"range": [90, 99], "color": "#fef3d6"},
                             {"range": [99, 100], "color": "#d7f0e4"}],
                   "threshold": {"line": {"color": DANGER, "width": 3}, "value": 99}},
        ))
        st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="int_gauge")
        insight(
            f"<b>How this score is calculated:</b> DART takes the arithmetic mean of "
            f"the field-level MatchRate values for <b>{comparable_fields:,} directly comparable "
            f"fields</b> classified as Match or Different. Rows classified as Missing are "
            f"excluded because they do not have a direct field-to-field comparison. "
            f"The current average is <b>{overall_rate:.2%}</b>. "
            f"For context, the separate claim-volume-weighted event rate is "
            f"<b>{event_weighted_rate:.3%}</b> ({matched_events:,} matched of "
            f"{total_events:,} event comparisons)."
            if pd.notna(overall_rate)
            else "No directly comparable fields with a match rate are available for the current selection.",
        )
    with hcol:
        section("Comparable Field Match-Rate Distribution",
                "Distribution of Match/Different field rates used in the average.")
        fig = px.histogram(rated, x="MatchRate", nbins=40, color_discrete_sequence=[PRIMARY])
        fig.update_xaxes(tickformat=".0%", title="Match rate")
        fig.update_yaxes(title="Number of fields")
        st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="int_hist")

    section("Average Match Rate Heatmap — Stream × Classification",
            "Arithmetic mean of field MatchRate within comparable Match/Different groups.")
    heat = comparable_field_scope(fdf)
    pivot = heat.pivot_table(
        index="Stream", columns="ClassificationClean", values="MatchRate", aggfunc="mean"
    )
    if not pivot.empty:
        fig = px.imshow(pivot, aspect="auto",
                        color_continuous_scale="RdYlGn", zmin=0, zmax=1)
        fig.update_traces(texttemplate="<b>%{z:.1%}</b>", textfont=dict(size=11))
        fig.update_layout(coloraxis_colorbar=dict(tickformat=".0%", title="Match"))
        st.plotly_chart(style_fig(fig, height=340), use_container_width=True, key="int_heat")

    section("Lowest Matching Fields", "The 15 fields most in need of reconciliation.")
    worst_fields = rated[
        rated["DispositionClean"] != "Good"
    ].sort_values("MatchRate").head(15)[
        ["Stream", "NCH Target Table", "NCH Target Column", "MatchRate",
         "MatchedClaims", "NotMatchedClaims", "DispositionClean"]
    ].rename(columns={"NCH Target Table": "Table", "NCH Target Column": "Column",
                      "MatchRate": "Match Rate", "MatchedClaims": "Matched",
                      "NotMatchedClaims": "Unmatched", "DispositionClean": "Disposition"})
    st.dataframe(
        worst_fields.style.format({"Match Rate": "{:.2%}", "Matched": "{:,.0f}",
                                   "Unmatched": "{:,.0f}"}),
        use_container_width=True, hide_index=True,
    )


# --------------------------------------------------------------------------------------
#  TAB 5 — USE CASES  (from the SSDS presentation)
# --------------------------------------------------------------------------------------
def render_usecases_tab():
    section("📖 Comparison Use Cases",
            "Every column falls into one of these scenarios from the SSDS analysis. "
            "Counts below reflect the current filters.")

    badge_color = {"Match": ACCENT, "Different": WARN, "Missing": DANGER,
                   "Table is empty": SLATE, "Unspecified": SLATE}

    # Sub-classification distribution
    section("Sub-Classification Distribution")
    sc = fdf["SubClassClean"].value_counts().reset_index()
    sc.columns = ["Sub-Classification", "Columns"]
    fig = px.bar(sc, x="Columns", y="Sub-Classification", orientation="h",
                 color="Columns", color_continuous_scale="Blues")
    fig.update_layout(coloraxis_showscale=False, yaxis={"categoryorder": "total ascending"})
    st.plotly_chart(style_fig(fig, height=420), use_container_width=True, key="uc_dist")

    section("Use-Case Guide", "What each scenario means and the recommended action.")
    counts = fdf["SubClassClean"].value_counts().to_dict()
    # order: those present in data first (by count), then any remaining defined use cases
    ordered = sorted(USE_CASES.keys(), key=lambda k: (-counts.get(k, 0), k))
    for key in ordered:
        uc = USE_CASES[key]
        cnt = counts.get(key, 0)
        rows = fdf[fdf["SubClassClean"] == key]
        badge = badge_color.get(uc["category"], SLATE)
        st.markdown(
            f"""
            <div class="uc-card" style="border-left:5px solid {badge};">
                <span class="uc-title">{uc['title']}</span>
                <span class="uc-badge" style="background:{badge};">{uc['category']}</span>
                <span class="uc-badge" style="background:#334155;">{cnt} columns</span>
                <div class="uc-desc">{uc['desc']}</div>
                <div class="uc-action">✅ Recommended action: {uc['action']}</div>
            </div>
            """,
            unsafe_allow_html=True,
        )
        if cnt > 0:
            with st.expander(f"See {min(cnt, 8)} example column(s) for “{key}”"):
                ex_cols = [c for c in ["Stream", "NCH Target Table", "NCH Target Column",
                                       "SS Table", "MatchedClaims", "NotMatchedClaims",
                                       "MatchRate", "Comments/Examples"] if c in rows.columns]
                ex = rows.sort_values("NotMatchedClaims", ascending=False).head(8)[ex_cols].rename(
                    columns={"NCH Target Table": "Table", "NCH Target Column": "Column",
                             "MatchedClaims": "Matched", "NotMatchedClaims": "Mismatched",
                             "MatchRate": "Match Rate"})
                st.dataframe(
                    ex.style.format({"Matched": "{:,.0f}", "Mismatched": "{:,.0f}",
                                     "Match Rate": "{:.2%}"}),
                    use_container_width=True, hide_index=True,
                )


# --------------------------------------------------------------------------------------
#  TAB 6 — CROSS-DATASET CONNECTIONS
# --------------------------------------------------------------------------------------
def render_connections_tab():
    section("Cross-Dataset Connections",
            "Linking the comparison results to the NCH→NCH STTM business rules on "
            "<code>NCH Target Table</code> + <code>NCH Target Column</code>.")

    linked = int(fdf["HasRule"].sum())
    unlinked = len(fdf) - linked
    conflicts = int((fdf["Agreement"] == "Conflict").sum())
    agree = int((fdf["Agreement"] == "Agree").sum())

    c1, c2, c3, c4 = st.columns(4)
    kpi_card(c1, "Linked to a Rule", f"{linked:,}", f"{linked/len(fdf):.0%} coverage", ACCENT)
    kpi_card(c2, "No Linked Rule", f"{unlinked:,}", "gaps in documentation", WARN)
    kpi_card(c3, "Verdicts Agree", f"{agree:,}", "rule ✓ = comparison ✓", ACCENT)
    kpi_card(c4, "Verdict Conflicts", f"{conflicts:,}", "rule vs. actual mismatch", DANGER)

    if conflicts > 0:
        insight(
            f"⚠️ <b>{conflicts}</b> field(s) where the business rule's documented "
            f"<i>Data Match? (Y/N)</i> disagrees with the actual comparison result. "
            "These are prime data-integrity investigation targets.", "danger",
        )
    else:
        insight("✅ No conflicts between documented rule verdicts and actual comparison results in this view.",
                "good")

    st.markdown("<br>", unsafe_allow_html=True)
    fcol, scol = st.columns([1, 1.4])
    with fcol:
        section("Rule Coverage")
        cov = pd.DataFrame({"Linkage": ["Linked to rule", "No linked rule"],
                            "Count": [linked, unlinked]})
        fig = px.pie(cov, names="Linkage", values="Count", hole=0.55, color="Linkage",
                     color_discrete_map={"Linked to rule": ACCENT, "No linked rule": WARN})
        fig.update_traces(textinfo="percent+value")
        st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="conn_cov")
    with scol:
        section("Rule Verdict vs. Comparison Result")
        agdf = fdf[fdf["RuleVerdict"].ne("")]
        if not agdf.empty:
            ct = agdf.groupby(["RuleVerdict", "ComparisonVerdict"]).size().reset_index(name="Count")
            fig = px.bar(ct, x="RuleVerdict", y="Count", color="ComparisonVerdict", barmode="group",
                         color_discrete_map={"Match": ACCENT, "No Match": DANGER},
                         labels={"RuleVerdict": "Documented rule verdict",
                                 "ComparisonVerdict": "Actual comparison"})
            st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="conn_verdict")
        else:
            st.info("No documented rule verdicts available for the current selection.")

    section("Data Flow — Stream → Classification → Disposition",
            "How records flow from each stream through their classification to a final disposition.")
    sdf = fdf.copy()
    flow1 = sdf.groupby(["Stream", "ClassificationClean"]).size().reset_index(name="v")
    flow2 = sdf.groupby(["ClassificationClean", "DispositionClean"]).size().reset_index(name="v")
    labels = (list(sdf["Stream"].unique()) + list(sdf["ClassificationClean"].unique())
              + list(sdf["DispositionClean"].unique()))
    idx = {name: i for i, name in enumerate(labels)}
    src, tgt, val, colors = [], [], [], []
    for _, r in flow1.iterrows():
        src.append(idx[r["Stream"]]); tgt.append(idx[r["ClassificationClean"]]); val.append(r["v"])
        colors.append("rgba(11,83,148,0.35)")
    for _, r in flow2.iterrows():
        src.append(idx[r["ClassificationClean"]]); tgt.append(idx[r["DispositionClean"]]); val.append(r["v"])
        colors.append("rgba(18,128,92,0.30)")
    node_colors = (["#0B5394"] * sdf["Stream"].nunique()
                   + [CLASS_COLORS.get(c, SLATE) for c in sdf["ClassificationClean"].unique()]
                   + [DISP_COLORS.get(d, SLATE) for d in sdf["DispositionClean"].unique()])
    fig = go.Figure(go.Sankey(
        node=dict(label=labels, pad=16, thickness=16, color=node_colors,
                  line=dict(color="#ffffff", width=0.5)),
        link=dict(source=src, target=tgt, value=val, color=colors),
    ))
    st.plotly_chart(style_fig(fig, height=460), use_container_width=True, key="conn_sankey")

    with st.expander("🔎 Inspect verdict conflicts (rule vs. comparison)"):
        conf = fdf[fdf["Agreement"] == "Conflict"]
        if conf.empty:
            st.success("No conflicts in the current view.")
        else:
            cols = [c for c in ["Stream", "NCH Target Table", "NCH Target Column",
                                "ClassificationClean", "RuleVerdict", "Rule_BusinessRule"]
                    if c in conf.columns]
            st.dataframe(
                conf[cols].rename(columns={
                    "NCH Target Table": "Table", "NCH Target Column": "Column",
                    "ClassificationClean": "Comparison", "RuleVerdict": "Rule Says",
                    "Rule_BusinessRule": "Business Rule"}),
                use_container_width=True, hide_index=True,
            )


# --------------------------------------------------------------------------------------
#  TAB 7 — CORRELATIONS
# --------------------------------------------------------------------------------------
def render_correlations_tab():
    section("Correlation & Relationship Analysis",
            "Do event volume, matched/not-matched counts and match rate move together?")

    num_df = fdf[["MatchedClaims", "NotMatchedClaims", "TotalClaims", "MatchRate"]].apply(
        pd.to_numeric, errors="coerce").rename(columns={
            "MatchedClaims": "Matched", "NotMatchedClaims": "Unmatched",
            "TotalClaims": "Total Volume", "MatchRate": "Match Rate"})
    num_df = num_df.dropna(how="all")

    ccol, tcol = st.columns([1, 1])
    with ccol:
        section("Correlation Matrix")
        corr = num_df.corr(numeric_only=True)
        if not corr.empty:
            fig = px.imshow(corr, text_auto=".2f", color_continuous_scale="RdBu",
                            zmin=-1, zmax=1, aspect="auto")
            st.plotly_chart(style_fig(fig, height=360), use_container_width=True, key="corr_matrix")
    with tcol:
        section("Volume vs. Match Rate")
        scat = fdf.dropna(subset=["MatchRate", "TotalClaims"])
        scat = scat[scat["TotalClaims"] > 0]
        if not scat.empty:
            fig = px.scatter(scat, x="TotalClaims", y="MatchRate", color="Stream",
                             hover_data=["NCH Target Table", "NCH Target Column"],
                             log_x=True, opacity=0.7)
            fig.update_yaxes(tickformat=".0%", title="Match rate", range=[0, 1.02])
            fig.update_xaxes(title="Total event volume (log)")
            st.plotly_chart(style_fig(fig, height=360), use_container_width=True, key="corr_scatter")

    if {"Total Volume", "Match Rate"}.issubset(num_df.columns):
        sub = num_df.dropna(subset=["Total Volume", "Match Rate"])
        if len(sub) > 3:
            r = sub["Total Volume"].corr(sub["Match Rate"])
            if pd.notna(r):
                strength = "weak" if abs(r) < 0.3 else ("moderate" if abs(r) < 0.6 else "strong")
                direction = "higher" if r > 0 else "lower"
                kind = "good" if r >= 0 else "warn"
                insight(
                    f"🧮 Correlation between <b>event volume</b> and <b>match rate</b> is "
                    f"<b>{r:+.2f}</b> ({strength}). Higher-volume fields tend to have "
                    f"<b>{direction}</b> match rates.", kind,
                )

    section("Match Rate by Classification", "Distribution of match rates within each classification.")
    box = fdf.dropna(subset=["MatchRate"])
    if not box.empty:
        fig = px.box(box, x="ClassificationClean", y="MatchRate", color="ClassificationClean",
                     color_discrete_map=CLASS_COLORS, points="outliers",
                     labels={"ClassificationClean": "Classification"})
        fig.update_yaxes(tickformat=".0%", title="Match rate")
        fig.update_layout(showlegend=False)
        st.plotly_chart(style_fig(fig, height=360), use_container_width=True, key="corr_box")


# --------------------------------------------------------------------------------------
#  TAB 8 — DETAILED EXPLORER
# --------------------------------------------------------------------------------------
def render_explorer_tab():
    section("Detailed Explorer", "Search, slice and export the full comparison dataset.")

    q = st.text_input("🔎 Search table / column / comments", "", key="explore_search")
    edf = fdf
    if q:
        cols_to_search = [c for c in ["NCH Target Table", "NCH Target Column", "SS Table",
                                      "Comments/Examples", "Recommendation"] if c in edf.columns]
        smask = np.zeros(len(edf), dtype=bool)
        for c in cols_to_search:
            smask |= edf[c].astype(str).str.contains(q, case=False, na=False).to_numpy()
        edf = edf[smask]

    default_cols = [c for c in [
        "Stream", "NCH Target Table", "NCH Target Column", "SS Table",
        "MatchedClaims", "NotMatchedClaims", "MatchRate",
        "ClassificationClean", "DispositionClean", "Recommendation", "HasRule",
    ] if c in edf.columns]
    chosen = st.multiselect("Columns to display", options=list(edf.columns), default=default_cols,
                            key="explore_cols")

    st.caption(f"Showing {len(edf):,} records")
    view = edf[chosen] if chosen else edf
    st.dataframe(view, use_container_width=True, hide_index=True)

    csv = view.to_csv(index=False).encode("utf-8")
    st.download_button("⬇️ Download current view (CSV)", data=csv,
                       file_name="cms_comparison_view.csv", mime="text/csv", key="explore_dl")


# --------------------------------------------------------------------------------------
#  TAB 9 — BUSINESS RULES
# --------------------------------------------------------------------------------------
def render_rules_tab():
    section("Business Rules & STTM Mapping",
            "The NCH Cobol copybook → NCH target → MCS source-to-target mapping.")

    rq = st.text_input("🔎 Search business rules (column, field name, rule text)", "", key="rule_search")
    rules_view = df_rules
    if rq:
        search_cols = [c for c in ["NCH Target Column", "NCH Cobol Copy book Field Name",
                                   "Business Rule", "NCH Target Table", "MCS Column"]
                       if c in df_rules.columns]
        rmask = np.zeros(len(df_rules), dtype=bool)
        for c in search_cols:
            rmask |= df_rules[c].astype(str).str.contains(rq, case=False, na=False).to_numpy()
        rules_view = df_rules[rmask]
        st.caption(f"Found {len(rules_view):,} matching rules")
    else:
        st.caption(f"Showing all {len(df_rules):,} rules")

    st.dataframe(rules_view, use_container_width=True, hide_index=True)

    if extra_sheets:
        section("Additional Reference Sheets")
        for name, edf2 in extra_sheets.items():
            with st.expander(f"📄 {name}  ({len(edf2):,} rows)"):
                st.dataframe(edf2, use_container_width=True, hide_index=True)


# --------------------------------------------------------------------------------------
#  TAB 10 — ACTION ITEMS
# --------------------------------------------------------------------------------------
def render_actions_tab():
    section("Action Items", "Records flagged **Action needed** that require reconciliation.")

    adf = fdf[fdf["DispositionClean"] == "Action needed"].copy()
    if adf.empty:
        st.success("🎉 No action items for the current filters — everything is reconciled or in good standing!")
    else:
        c1, c2, c3 = st.columns(3)
        kpi_card(c1, "Open Action Items", f"{len(adf):,}", "in current view", DANGER)
        kpi_card(c2, "Streams Affected", f"{adf['Stream'].nunique()}", "distinct streams", WARN)
        unmatched_vol = int(adf["NotMatchedClaims"].fillna(0).sum())
        kpi_card(c3, "Not-Matched Events at Stake", f"{unmatched_vol:,}", "across action items", PRIMARY)

        st.markdown("<br>", unsafe_allow_html=True)
        acol, bcol = st.columns([1, 1.4])
        with acol:
            section("Action Items by Stream")
            bys = adf["Stream"].value_counts().reset_index()
            bys.columns = ["Stream", "Count"]
            fig = px.bar(bys, x="Count", y="Stream", orientation="h",
                         color="Count", color_continuous_scale="Reds")
            fig.update_layout(coloraxis_showscale=False)
            st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="act_stream")
        with bcol:
            section("Action Items by Classification")
            byc = adf["ClassificationClean"].value_counts().reset_index()
            byc.columns = ["Classification", "Count"]
            fig = px.bar(byc, x="Count", y="Classification", orientation="h",
                         color="Classification", color_discrete_map=CLASS_COLORS)
            fig.update_layout(showlegend=False)
            st.plotly_chart(style_fig(fig, height=320), use_container_width=True, key="act_class")

        section("Action Item Details")
        stream_filter = st.selectbox("Filter by Stream", options=["All"] + sorted(adf["Stream"].unique()),
                                     key="act_streamfilter")
        if stream_filter != "All":
            adf = adf[adf["Stream"] == stream_filter]

        detail_cols = [c for c in [
            "Stream", "NCH Target Table", "NCH Target Column", "SS Table",
            "MatchRate", "NotMatchedClaims", "ClassificationClean",
            "Recommendation", "Lynette's recommendation", "HasRule",
        ] if c in adf.columns]
        st.dataframe(
            adf[detail_cols].rename(columns={
                "NCH Target Table": "Table", "NCH Target Column": "Column",
                "MatchRate": "Match Rate", "NotMatchedClaims": "Unmatched",
                "ClassificationClean": "Classification"}),
            use_container_width=True, hide_index=True,
        )

        csv = adf.to_csv(index=False).encode("utf-8")
        st.download_button("⬇️ Download Action Items (CSV)", data=csv,
                           file_name="cms_action_items.csv", mime="text/csv", key="act_dl")


# --------------------------------------------------------------------------------------
#  TAB 11 — CUSTOM DATA (DEMO)
# --------------------------------------------------------------------------------------
def render_custom_tab():
    section("🧪 Bring Your Own Data — Compare Any Two Systems",
            "A demo of how this dashboard tunes to ANY two systems. Upload two extracts (or a "
            "pre-computed comparison) and get the same matched/mismatched, top-mismatch and "
            "action analysis.")
    methodology_card(
        "How the custom-data mode works",
        [
            "Choose between comparing two raw extracts on a key, analyzing a pre-computed comparison file, or using the built-in sample.",
            "When comparing raw files, the system joins records on the selected key and evaluates the chosen columns field-by-field.",
            "The output is the same experience as the main dashboard: matched counts, mismatched counts, match rate, mismatch rate, and action guidance.",
        ],
        PRIMARY,
    )

    mode = st.radio(
        "How do you want to provide the data?",
        options=[
            "Compare two raw system extracts (join on a key)",
            "Analyze a pre-computed comparison file (matched / not-matched columns)",
            "Use a built-in sample (no upload needed)",
        ],
        key="custom_mode",
    )

    # ---------------- MODE 1: two raw extracts ----------------
    if mode == "Compare two raw system extracts (join on a key)":
        st.caption("Upload one file per system. Supported formats: CSV, XLSX.")
        u1, u2 = st.columns(2)
        with u1:
            st.markdown(f"**System A**")
            file_a = st.file_uploader("System A file", type=["csv", "xlsx", "xls"], key="up_a")
            name_a = st.text_input("System A label", value="System A", key="lbl_a")
        with u2:
            st.markdown(f"**System B**")
            file_b = st.file_uploader("System B file", type=["csv", "xlsx", "xls"], key="up_b")
            name_b = st.text_input("System B label", value="System B", key="lbl_b")

        if file_a is not None and file_b is not None:
            try:
                dfa = read_uploaded(file_a)
                dfb = read_uploaded(file_b)
            except Exception as e:
                st.error(f"Could not read the files: {e}")
                st.stop()

            common = [c for c in dfa.columns if c in dfb.columns]
            if not common:
                st.warning("The two files share no common column names, so they cannot be joined.")
            else:
                keys = st.multiselect("Key column(s) to join on (unique record id)",
                                      options=common, default=common[:1], key="cust_keys")
                remaining = [c for c in common if c not in keys]
                compare_cols = st.multiselect("Columns to compare", options=remaining,
                                              default=remaining, key="cust_cmp")
                if st.button("▶️ Run comparison", key="cust_run") and keys and compare_cols:
                    summary, meta = compare_two_systems(dfa, dfb, keys, compare_cols)
                    st.markdown("<br>", unsafe_allow_html=True)
                    m1, m2, m3 = st.columns(3)
                    kpi_card(m1, "Shared Records", f"{meta['shared_keys']:,}", "matched on key", PRIMARY)
                    kpi_card(m2, f"Only in {name_a}", f"{meta['only_a']:,}", "missing from B", WARN)
                    kpi_card(m3, f"Only in {name_b}", f"{meta['only_b']:,}", "missing from A", WARN)
                    st.markdown("<br>", unsafe_allow_html=True)
                    render_summary_block(summary, name_a, name_b, key_prefix="cust1")
                elif not (keys and compare_cols):
                    st.info("Select at least one key column and one column to compare.")
        else:
            st.info("Upload both files to begin, or switch to the built-in sample below.")

    # ---------------- MODE 2: pre-computed comparison ----------------
    elif mode == "Analyze a pre-computed comparison file (matched / not-matched columns)":
        st.caption("Upload one file where each row is a column/field with matched & not-matched counts.")
        file_c = st.file_uploader("Comparison file", type=["csv", "xlsx", "xls"], key="up_c")
        if file_c is not None:
            try:
                dfc = read_uploaded(file_c)
            except Exception as e:
                st.error(f"Could not read the file: {e}")
                st.stop()
            cols = list(dfc.columns)
            f1, f2, f3 = st.columns(3)
            with f1:
                label_col = st.selectbox("Column / field name", options=cols, key="pc_label")
            with f2:
                matched_col = st.selectbox("Matched count column", options=cols,
                                           index=min(1, len(cols) - 1), key="pc_matched")
            with f3:
                notmatched_col = st.selectbox("Not-matched count column", options=cols,
                                              index=min(2, len(cols) - 1), key="pc_notmatched")
            if st.button("▶️ Analyze", key="pc_run"):
                summary = analyze_from_counts(dfc, label_col, matched_col, notmatched_col)
                st.markdown("<br>", unsafe_allow_html=True)
                render_summary_block(summary, "System A", "System B", key_prefix="cust2")
        else:
            st.info("Upload a comparison file to begin.")

    # ---------------- MODE 3: built-in sample ----------------
    else:
        st.caption("Two synthetic claim extracts are generated with realistic, seeded differences.")
        sys_a, sys_b = make_sample_systems()
        p1, p2 = st.columns(2)
        with p1:
            st.markdown("**Sample System A (first 5 rows)**")
            st.dataframe(sys_a.head(), use_container_width=True, hide_index=True)
        with p2:
            st.markdown("**Sample System B (first 5 rows)**")
            st.dataframe(sys_b.head(), use_container_width=True, hide_index=True)

        keys = ["claim_id"]
        compare_cols = [c for c in sys_a.columns if c != "claim_id"]
        summary, meta = compare_two_systems(sys_a, sys_b, keys, compare_cols)
        st.markdown("<br>", unsafe_allow_html=True)
        m1, m2, m3 = st.columns(3)
        kpi_card(m1, "Shared Records", f"{meta['shared_keys']:,}", "matched on claim_id", PRIMARY)
        kpi_card(m2, "Only in A", f"{meta['only_a']:,}", "missing from B", WARN)
        kpi_card(m3, "Only in B", f"{meta['only_b']:,}", "missing from A", WARN)
        st.markdown("<br>", unsafe_allow_html=True)
        render_summary_block(summary, "System A", "System B", key_prefix="cust3")


# --------------------------------------------------------------------------------------
#  EMAIL AGENT — RULE-BASED NEW/CHANGED DATA ALERTS
# --------------------------------------------------------------------------------------
def render_email_agent_tab():
    section(
        "✉️ Email Agent",
        "Create reusable alert templates that inspect new or changed rows and email a structured reconciliation summary.",
    )

    c1, c2, c3, c4 = st.columns(4)
    templates = load_email_automations()
    enabled_count = sum(bool(t.get("enabled")) for t in templates)
    baseline_count = sum(_snapshot_path(t.get("id", "")).exists() for t in templates)
    kpi_card(c1, "Saved Automations", f"{len(templates):,}", "JSON-backed templates", PRIMARY)
    kpi_card(c2, "Enabled", f"{enabled_count:,}", "checked when the agent runs", ACCENT)
    kpi_card(c3, "Baselines Ready", f"{baseline_count:,}", "used for change detection", WARN)
    kpi_card(c4, "SMTP", "Ready" if smtp_is_configured() else "Not configured",
             "delivery connection", ACCENT if smtp_is_configured() else DANGER)

    methodology_card(
        "How the Email Agent works",
        [
            "A template stores the recipient, row identity, monitored columns, rule logic, and fields to include in the email.",
            "The baseline snapshot uses real DART identity fields such as Stream, NCH Target Table, and NCH Target Column to recognize new or changed rows.",
            "Conditions can test blank/null values, exact values, text contents, numeric thresholds, and dates.",
            "An email is only sent when a row is new or changed and the row also satisfies the saved conditions.",
            "SMTP credentials are read from environment variables or Streamlit secrets; passwords are never stored in the automation template.",
        ],
        PRIMARY,
    )

    create_tab, saved_tab, operations_tab = st.tabs([
        "➕ Create automation", "🗂️ Saved automations", "⚙️ Delivery & scheduling"
    ])

    available_columns = list(df_all.columns)
    defaults = _default_email_agent_template(df_all)

    with create_tab:
        left, right = st.columns([1.05, 1])
        with left:
            section("1. Alert destination")
            draft_name = st.text_input(
                "Automation name",
                value="Action-needed reconciliation alert",
                key="email_draft_name",
            )
            draft_email = st.text_input(
                "Recipient email",
                placeholder="data.owner@example.gov",
                help="Separate multiple recipients with commas or semicolons.",
                key="email_draft_recipient",
            )
            subject_prefix = st.text_input(
                "Subject prefix", value="[DART Alert]", key="email_draft_subject_prefix"
            )
            trigger_mode = st.selectbox(
                "What should count as a change?",
                ["New or changed rows", "New rows only"],
                key="email_draft_trigger_mode",
            )
            enabled = st.checkbox(
                "Enable after saving",
                value=False,
                help="Enable only after SMTP has been configured and a test email succeeds.",
                key="email_draft_enabled",
            )

            section("2. Identify and monitor rows")
            identity_defaults = defaults["identity_columns"]
            identity_columns = st.multiselect(
                "Row identity columns",
                options=available_columns,
                default=identity_defaults,
                help="These fields identify the same row across data refreshes. The recommended DART key is Stream + NCH Target Table + NCH Target Column.",
                key="email_draft_identity",
            )
            display_defaults = defaults["display_columns"]
            monitor_columns = st.multiselect(
                "Columns to monitor for changes",
                options=available_columns,
                default=display_defaults,
                help="Only changes in these columns will classify an existing row as Changed.",
                key="email_draft_monitor",
            )

            if identity_columns:
                duplicated = int(df_all[identity_columns].astype(str).duplicated(keep=False).sum())
                if duplicated:
                    st.warning(
                        f"The selected identity fields are duplicated across {duplicated:,} rows. "
                        "Add another identity field if possible so changed-row tracking is stable."
                    )

        with right:
            section("3. Define the trigger requirements")
            condition_logic = st.radio(
                "Rule logic",
                ["ALL conditions", "ANY condition"],
                horizontal=True,
                key="email_draft_logic",
            )
            rule_count = int(st.number_input(
                "Number of conditions", min_value=1, max_value=6, value=2, step=1,
                key="email_draft_rule_count",
            ))
            conditions = []
            default_fields = ["DispositionClean", "NotMatchedClaims"]
            default_ops = ["Equals", "Greater than"]
            default_vals = ["Action needed", "0"]

            for i in range(rule_count):
                st.markdown(f"**Condition {i + 1}**")
                rc1, rc2 = st.columns([1.05, 1])
                default_field = default_fields[i] if i < len(default_fields) and default_fields[i] in available_columns else available_columns[0]
                with rc1:
                    field = st.selectbox(
                        "Field",
                        available_columns,
                        index=available_columns.index(default_field),
                        key=f"email_rule_field_{i}",
                    )
                with rc2:
                    default_op = default_ops[i] if i < len(default_ops) else "Equals"
                    operator = st.selectbox(
                        "Operator",
                        EMAIL_OPERATORS,
                        index=EMAIL_OPERATORS.index(default_op),
                        key=f"email_rule_operator_{i}",
                    )
                no_value = operator in (
                    "Is blank (empty or null)", "Is null / NaN", "Is empty string"
                )
                default_value = default_vals[i] if i < len(default_vals) else ""
                value = st.text_input(
                    "Comparison value",
                    value=default_value if not no_value else "",
                    disabled=no_value,
                    key=f"email_rule_value_{i}",
                    help="Numeric operators automatically convert the selected field and this value to numbers.",
                )
                conditions.append({"column": field, "operator": operator, "value": value})

            section("4. Structure the email")
            display_columns = st.multiselect(
                "Columns included in the email and CSV",
                options=available_columns,
                default=display_defaults,
                key="email_draft_display_columns",
            )
            greeting = st.text_input(
                "Greeting", value="Hello Data Owner,", key="email_draft_greeting"
            )
            email_intro = st.text_area(
                "Introductory message",
                value="DART detected new or changed reconciliation data requiring review.",
                height=90,
                key="email_draft_intro",
            )
            include_csv = st.checkbox(
                "Attach all matching rows as CSV", value=True, key="email_draft_csv"
            )
            establish_on_save = st.checkbox(
                "Use the current data as the baseline when saving",
                value=True,
                help="Recommended. Existing rows will not trigger an email; only later additions or changes can trigger one.",
                key="email_draft_baseline",
            )

        if "email_agent_draft_id" not in st.session_state:
            st.session_state.email_agent_draft_id = uuid.uuid4().hex[:12]

        draft_template = {
            "id": st.session_state.email_agent_draft_id,
            "name": draft_name.strip(),
            "recipient_email": draft_email.strip(),
            "enabled": enabled,
            "trigger_mode": trigger_mode,
            "identity_columns": identity_columns,
            "monitor_columns": monitor_columns,
            "condition_logic": condition_logic,
            "conditions": conditions,
            "display_columns": display_columns,
            "subject_prefix": subject_prefix.strip() or "[DART Alert]",
            "greeting": greeting.strip() or "Hello,",
            "email_intro": email_intro.strip(),
            "include_csv": include_csv,
        }

        st.markdown("---")
        b1, b2, b3 = st.columns([1, 1, 1.2])
        with b1:
            preview_clicked = st.button(
                "👁️ Preview matching rows & email", use_container_width=True, key="email_preview_draft"
            )
        with b2:
            save_clicked = st.button(
                "💾 Save automation", type="primary", use_container_width=True, key="email_save_draft"
            )
        with b3:
            st.download_button(
                "⬇️ Download automation JSON",
                data=json.dumps(draft_template, indent=2),
                file_name=f"dart_email_automation_{_safe_template_id(draft_template['id'])}.json",
                mime="application/json",
                use_container_width=True,
                key="email_download_draft",
            )

        if save_clicked:
            recipients, invalid = validate_recipients(draft_email)
            errors = []
            if not draft_name.strip():
                errors.append("Enter an automation name.")
            if not recipients or invalid:
                errors.append("Enter a valid recipient email address.")
            if not identity_columns:
                errors.append("Select at least one row identity column.")
            if not monitor_columns:
                errors.append("Select at least one column to monitor.")
            if not display_columns:
                errors.append("Select at least one email display column.")
            if any(
                r["operator"] not in ("Is blank (empty or null)", "Is null / NaN", "Is empty string")
                and not str(r.get("value", "")).strip()
                for r in conditions
            ):
                errors.append("Every value-based condition needs a comparison value.")

            if errors:
                for error in errors:
                    st.error(error)
            else:
                saved = upsert_email_automation(draft_template)
                if establish_on_save:
                    establish_email_baseline(df_all, saved)
                st.success(
                    "Automation saved" + (" and the current source data is now its baseline." if establish_on_save else ".")
                )
                if enabled and not smtp_is_configured():
                    st.warning("The automation is enabled, but SMTP is not configured yet. Delivery will fail until SMTP settings are added.")
                st.session_state.email_agent_draft_id = uuid.uuid4().hex[:12]

        if preview_clicked:
            preview_matches = current_matching_rows(df_all, draft_template)
            st.markdown(f"### Preview results: {len(preview_matches):,} current row(s) match")
            if preview_matches.empty:
                st.info("No current rows satisfy the selected conditions. The saved automation can still trigger when qualifying data arrives later.")
            else:
                preview_cols = [c for c in display_columns if c in preview_matches.columns]
                st.dataframe(preview_matches[preview_cols].head(100), use_container_width=True, hide_index=True)
                subject, _, body_html, _ = build_structured_email(
                    draft_template, preview_matches.head(100), is_test=True
                )
                _render_email_html_preview(subject, body_html)

        st.markdown("---")
        section("Example template and email", "Uses actual fields already created by this DART app.")
        example_template = _default_email_agent_template(df_all)
        example_matches = current_matching_rows(df_all, example_template)
        if example_matches.empty and "NotMatchedClaims" in df_all.columns:
            example_matches = df_all.sort_values("NotMatchedClaims", ascending=False).head(3).copy()
            example_matches["_Change Type"] = "New"
            example_matches["_Changed Fields"] = "DispositionClean, NotMatchedClaims"
            example_matches["_Previous Values"] = "—"
        else:
            example_matches = example_matches.sort_values(
                "NotMatchedClaims" if "NotMatchedClaims" in example_matches.columns else example_matches.columns[0],
                ascending=False,
            ).head(3)
        if not example_matches.empty:
            ex_subject, _, ex_html, _ = build_structured_email(example_template, example_matches, is_test=False)
            with st.expander("Open example structured email", expanded=True):
                st.markdown(
                    "**Example requirements:** `DispositionClean = Action needed` **AND** "
                    "`NotMatchedClaims > 0`. Row identity uses `Stream`, `NCH Target Table`, and `NCH Target Column`."
                )
                _render_email_html_preview(ex_subject, ex_html, height=610)

    with saved_tab:
        templates = load_email_automations()
        if not templates:
            st.info("No saved automations yet. Create one in the first tab.")
        else:
            labels = {
                t["id"]: f"{t.get('name', 'Unnamed')} — {t.get('recipient_email', 'No recipient')}"
                for t in templates
            }
            selected_id = st.selectbox(
                "Select an automation",
                options=[t["id"] for t in templates],
                format_func=lambda value: labels.get(value, value),
                key="email_saved_selection",
            )
            template = next(t for t in templates if t["id"] == selected_id)

            m1, m2, m3, m4 = st.columns(4)
            kpi_card(m1, "Status", "Enabled" if template.get("enabled") else "Paused", "delivery state", ACCENT if template.get("enabled") else SLATE)
            kpi_card(m2, "Baseline", "Ready" if _snapshot_path(selected_id).exists() else "Missing", "change comparison", ACCENT if _snapshot_path(selected_id).exists() else WARN)
            kpi_card(m3, "Last Matches", f"{int(template.get('last_match_count', 0)):,}", "most recent check", PRIMARY)
            kpi_card(m4, "Last Run", str(template.get("last_run_at", "Never"))[:19], str(template.get("last_status", "Not run")), PRIMARY)

            saved_enabled = st.checkbox(
                "Enabled", value=bool(template.get("enabled", False)), key=f"email_saved_enabled_{selected_id}"
            )
            if st.button("Save enabled/paused status", key=f"email_saved_status_btn_{selected_id}"):
                template["enabled"] = saved_enabled
                upsert_email_automation(template)
                st.success("Automation status updated.")

            with st.expander("View template configuration", expanded=False):
                st.json(template)

            x1, x2, x3, x4 = st.columns(4)
            preview_saved = x1.button("👁️ Preview", use_container_width=True, key=f"email_saved_preview_{selected_id}")
            baseline_saved = x2.button("📌 Refresh baseline", use_container_width=True, key=f"email_saved_baseline_{selected_id}")
            run_saved = x3.button("▶️ Run now", type="primary", use_container_width=True, key=f"email_saved_run_{selected_id}")
            test_saved = x4.button("✉️ Send test", use_container_width=True, key=f"email_saved_test_{selected_id}")

            if preview_saved:
                matches = current_matching_rows(df_all, template)
                st.info(f"{len(matches):,} current rows satisfy the conditions. Preview does not alter the baseline.")
                if not matches.empty:
                    subject, _, body_html, _ = build_structured_email(template, matches.head(100), is_test=True)
                    _render_email_html_preview(subject, body_html)

            if baseline_saved:
                result = establish_email_baseline(df_all, template)
                st.success(f"Baseline refreshed with {result['rows']:,} current rows.")

            if run_saved:
                result = run_email_automation(df_all, template, send_email=True)
                template["last_run_at"] = result.get("checked_at")
                template["last_status"] = result.get("status")
                template["last_match_count"] = result.get("matches", 0)
                upsert_email_automation(template)
                if result["status"] == "Email sent":
                    st.success(f"Email sent for {result['matches']:,} matching change(s).")
                elif result["status"].startswith("Email failed"):
                    st.error(result["status"])
                else:
                    st.info(result["status"])
                if isinstance(result.get("matched_rows"), pd.DataFrame) and not result["matched_rows"].empty:
                    st.dataframe(result["matched_rows"], use_container_width=True, hide_index=True)

            if test_saved:
                matches = current_matching_rows(df_all, template)
                if matches.empty:
                    st.warning("No current rows match this automation, so there is nothing to include in a test email.")
                else:
                    try:
                        subject = send_structured_email(template, matches.head(100), is_test=True)
                        st.success(f"Test email sent. Subject: {subject}")
                    except Exception as exc:
                        st.error(f"Test email failed: {exc}")

            st.markdown("---")
            confirm_delete = st.checkbox(
                "I understand this will delete the template and its baseline snapshot.",
                key=f"email_delete_confirm_{selected_id}",
            )
            if st.button(
                "🗑️ Delete automation",
                disabled=not confirm_delete,
                key=f"email_delete_{selected_id}",
            ):
                delete_email_automation(selected_id)
                st.success("Automation deleted.")
                st.rerun()

    with operations_tab:
        section("SMTP delivery settings")
        st.markdown(
            "The app reads these settings from environment variables or `.streamlit/secrets.toml`. "
            "No credentials are written to the saved JSON template."
        )
        st.code(
            """SMTP_HOST=smtp.office365.com
SMTP_PORT=587
SMTP_USERNAME=service-account@example.gov
SMTP_PASSWORD=your-secret-password
SMTP_FROM=service-account@example.gov
SMTP_FROM_NAME=DART Email Agent
SMTP_USE_TLS=true
SMTP_USE_SSL=false

# Optional: automatically check enabled templates when the Streamlit app executes
EMAIL_AGENT_AUTO_RUN=true
EMAIL_AGENT_MIN_CHECK_SECONDS=300

# Recommended on hosted deployments: a persistent folder/disk
EMAIL_AGENT_DIR=/var/data/dart_email_agent""",
            language="bash",
        )
        if smtp_is_configured():
            cfg = smtp_settings()
            st.success(f"SMTP is configured for {cfg['host']}:{cfg['port']} using sender {cfg['from_email']}.")
        else:
            st.warning("SMTP is not configured. You can create and preview templates now, but delivery and test sends will not work yet.")

        section("Automation execution")
        methodology_card(
            "Recommended production setup",
            [
                "Save the template and establish the current workbook as its baseline.",
                "Configure SMTP in the hosting environment and send a test email from the Saved automations tab.",
                "Set EMAIL_AGENT_AUTO_RUN=true so enabled templates are checked when the app executes.",
                "Use a scheduler, uptime check, or platform cron to invoke/reload the app at the desired cadence; Streamlit itself is not a background scheduler.",
                "Keep EMAIL_AGENT_DIR on persistent storage. The baseline is updated only after a successful email or after a check with no qualifying matches, preventing duplicate alerts.",
            ],
            ACCENT,
        )

        st.code(
            """# The function used by the app's guarded automatic runner:
results = run_enabled_email_automations(df_all)

# Each enabled template:
# 1. Compares the latest df_all rows with its prior snapshot.
# 2. Keeps only New / Changed rows.
# 3. Applies the saved conditions.
# 4. Sends one structured email and optional CSV attachment.
# 5. Updates the baseline after successful processing.""",
            language="python",
        )

        if st.session_state.get("email_agent_last_auto_results"):
            section("Most recent automatic check")
            result_rows = []
            for result in st.session_state.email_agent_last_auto_results:
                result_rows.append({
                    "Automation": result.get("name"),
                    "Status": result.get("status"),
                    "Detected changes": result.get("change_rows", 0),
                    "Matching changes": result.get("matches", 0),
                    "Checked at": result.get("checked_at"),
                })
            st.dataframe(pd.DataFrame(result_rows), use_container_width=True, hide_index=True)


# ======================================================================================
#  DOMAIN ANALYSIS TAB
# ======================================================================================
def render_domains_tab():
    section("🗂️ Domain Analysis",
            "Summary and detailed analysis for each NCH/CVM claim domain — "
            "row count comparisons and top issues per stream")

    # Stream → (full name, SS system, SS abbreviation)
    DOMAIN_META = {
        "PROF":    ("Professional",               "Multi-Carrier System (MCS)",          "MCS"),
        "INP/SNF": ("Inpatient / SNF",            "FISS Institutional",                  "FISS"),
        "OUT":     ("Outpatient",                 "FISS Institutional",                  "FISS"),
        "HH":      ("Home Health",                "FISS Institutional",                  "FISS"),
        "HSPC":    ("Hospice",                    "FISS Institutional",                  "FISS"),
        "DME":     ("Durable Medical Equipment",  "ViPs Medicare System (VMS)",          "VMS"),
    }

    present_streams = [s for s in ["PROF", "INP/SNF", "OUT", "HH", "HSPC", "DME"]
                       if s in fdf["Stream"].unique()]

    # ── Cross-domain row count comparison table ────────────────────────────────────
    section("Row Count Comparison Across All Domains",
            "Matched vs. not-matched claim counts for each stream — "
            "the core NCH/CVM ↔ Shared Systems query comparison")

    grp = (
        fdf.groupby("Stream")
        .agg(
            Fields=("MatchRate", "count"),
            Matched=("MatchedClaims", "sum"),
            NotMatched=("NotMatchedClaims", "sum"),
            ActionNeeded=("NeedsChange", "sum"),
        )
        .reset_index()
    )
    grp["Total"] = grp["Matched"] + grp["NotMatched"]

    comparable_stream = (
        comparable_field_scope(fdf)
        .groupby("Stream", as_index=False)
        .agg(MatchPct=("MatchRate", "mean"),
             ComparableFields=("MatchRate", "count"))
    )
    grp = grp.merge(comparable_stream, on="Stream", how="left")
    grp["Domain"] = grp["Stream"].map(lambda s: DOMAIN_META.get(s, (s,))[0])
    grp["SS System"] = grp["Stream"].map(lambda s: DOMAIN_META.get(s, ("", s))[1])

    display_grp = grp[["Domain", "Stream", "SS System", "Fields",
                        "Matched", "NotMatched", "Total", "MatchPct", "ActionNeeded"]].rename(
        columns={"NotMatched": "Not Matched", "MatchPct": "Match Rate",
                 "ActionNeeded": "Fields w/ Action"}
    ).sort_values("Match Rate")

    st.dataframe(
        display_grp.style.format({
            "Matched": "{:,.0f}", "Not Matched": "{:,.0f}", "Total": "{:,.0f}",
            "Match Rate": "{:.2%}",
        }).background_gradient(subset=["Match Rate"], cmap="RdYlGn"),
        use_container_width=True, hide_index=True,
    )

    # bar chart: matched vs not-matched by domain
    melt = grp.melt(id_vars=["Domain", "Stream"], value_vars=["Matched", "NotMatched"],
                    var_name="Result", value_name="Claims")
    melt["Result"] = melt["Result"].replace({"NotMatched": "Not Matched"})
    fig = px.bar(
        melt, x="Domain", y="Claims", color="Result", barmode="group",
        color_discrete_map={"Matched": ACCENT, "Not Matched": DANGER},
        labels={"Domain": "Claim Domain"},
        title=f"Matched vs. Not-Matched Events — {SYS_A} ↔ {SYS_B} by Domain",
    )
    st.plotly_chart(style_fig(fig, height=380), use_container_width=True, key="dom_bar")

    st.markdown("<br>", unsafe_allow_html=True)

    # ── Per-domain detail panels ────────────────────────────────────────────────────
    section("Per-Domain Detailed Analysis",
            "Expand each domain to see its full field-level breakdown and top issues")

    for stream in present_streams:
        name, ss_sys, ss_abbr = DOMAIN_META.get(stream, (stream, "Shared Systems", "SS"))
        ddf = fdf[fdf["Stream"] == stream].copy()
        if ddf.empty:
            continue

        matched_c = int(ddf["MatchedClaims"].fillna(0).sum())
        not_matched_c = int(ddf["NotMatchedClaims"].fillna(0).sum())
        total_c = matched_c + not_matched_c
        match_rate, comparable_ct = average_comparable_match_rate(ddf)
        action_ct = int(ddf["NeedsChange"].sum())
        fields_ct = len(ddf)

        label_color = "🟢" if (not pd.isna(match_rate) and match_rate >= 0.95) else (
                       "🟡" if (not pd.isna(match_rate) and match_rate >= 0.80) else "🔴")

        with st.expander(
            f"{label_color}  **{name}** ({stream})  ·  {ss_sys}  ·  "
            f"Match rate: {match_rate:.1%}  ·  {action_ct} fields need action",
            expanded=False,
        ):
            c1, c2, c3, c4, c5 = st.columns(5)
            kpi_card(c1, "Fields Compared", f"{fields_ct:,}", stream, PRIMARY)
            kpi_card(c2, f"{SYS_A} Matched Events", f"{matched_c:,}", "identical data", ACCENT)
            kpi_card(c3, "Not-Matched Events", f"{not_matched_c:,}", "differ between systems", DANGER)
            kpi_card(c4, "Average Match Rate", f"{match_rate:.2%}" if pd.notna(match_rate) else "—",
                     f"{comparable_ct:,} comparable fields", PRIMARY)
            kpi_card(c5, "Fields Needing Action", f"{action_ct:,}", "Action needed", WARN)

            st.markdown("<br>", unsafe_allow_html=True)

            col_l, col_r = st.columns([1, 1])
            with col_l:
                section("Classification Breakdown")
                cls_data = ddf["ClassificationClean"].value_counts().reset_index()
                cls_data.columns = ["Classification", "Count"]
                fig = px.pie(cls_data, names="Classification", values="Count", hole=0.5,
                             color="Classification", color_discrete_map=CLASS_COLORS)
                fig.update_traces(textposition="inside", textinfo="percent+label")
                st.plotly_chart(style_fig(fig, height=260),
                                use_container_width=True, key=f"dom_cls_{stream}")

            with col_r:
                section("Sub-Classification Breakdown")
                sub_data = ddf["SubClassClean"].value_counts().head(8).reset_index()
                sub_data.columns = ["Sub-Classification", "Count"]
                fig = px.bar(sub_data, x="Count", y="Sub-Classification", orientation="h",
                             color_discrete_sequence=[PRIMARY])
                fig.update_layout(yaxis={"categoryorder": "total ascending"})
                st.plotly_chart(style_fig(fig, height=260),
                                use_container_width=True, key=f"dom_sub_{stream}")

            # Top mismatched fields (query-level examples)
            section("Top Fields by Not-Matched Event Volume",
                    "Detailed query comparison — fields with the most event-level discrepancies")
            detail_cols = [c for c in [
                "NCH Target Table", "NCH Target Column", "SS Table", "SS Colmn if different",
                "MatchedClaims", "NotMatchedClaims", "MatchRate",
                "ClassificationClean", "SubClassClean", "DispositionClean",
                "RecommendationClean", "LynetteRec", "Comments/Examples",
            ] if c in ddf.columns]
            top_fields = (
                ddf[detail_cols]
                .sort_values("NotMatchedClaims", ascending=False)
                .head(20)
                .rename(columns={
                    "NCH Target Table": "NCH Table", "NCH Target Column": "NCH Column",
                    "SS Table": "SS Table", "SS Colmn if different": "SS Column (if diff)",
                    "MatchedClaims": "Matched", "NotMatchedClaims": "Not Matched",
                    "MatchRate": "Match Rate", "ClassificationClean": "Classification",
                    "SubClassClean": "Sub-Classification", "DispositionClean": "Disposition",
                    "RecommendationClean": "Recommendation", "LynetteRec": "Lynette's Rec",
                    "Comments/Examples": "Examples",
                })
            )
            st.dataframe(
                top_fields.style.format({"Matched": "{:,.0f}", "Not Matched": "{:,.0f}",
                                         "Match Rate": "{:.2%}"}),
                use_container_width=True, hide_index=True,
            )

            csv = ddf[detail_cols].to_csv(index=False).encode("utf-8")
            st.download_button(
                f"⬇️ Download {name} detail (CSV)", data=csv,
                file_name=f"domain_{stream.replace('/', '_')}_detail.csv",
                mime="text/csv", key=f"dom_dl_{stream}",
            )


# ======================================================================================
#  AI CHATBOT TAB
# ======================================================================================
def render_chatbot_tab():
    from openai import OpenAI

    section("🎯 Dartboard — AI Data Quality Assistant",
            "Ask questions about both data sources — powered by Groq")

    GROQ_MODELS = [
        "llama-3.1-8b-instant",        # 500K tokens/day free — recommended default
        "llama-3.3-70b-versatile",     # most capable but 100K tokens/day limit
        "mixtral-8x7b-32768",
        "gemma2-9b-it",
    ]

    # model picker always visible; key is pre-loaded
    model_choice = st.sidebar.selectbox(
        "AI Model", GROQ_MODELS, key="groq_model_select",
        help="llama-3.3-70b is most capable; 8b-instant is fastest."
    )

    # Data context — query-aware smart filtering to stay under token limits
    def build_data_context(query=""):
        lines = []
        lines.append(f"Systems compared: {SYS_A} (System A) vs {SYS_B} (System B).")
        lines.append("Project: Shared Systems Data Analysis Study (SSDS).")
        lines.append(f"Total fields in current filtered view: {len(fdf):,}")

        avg_rate, comparable_count = average_comparable_match_rate(fdf)
        weighted_rate, total_matched, total_not, total_compared = claim_weighted_match_rate(fdf)
        if not pd.isna(avg_rate):
            lines.append(
                f"Primary dashboard Average Match Rate: {avg_rate:.1%} across "
                f"{comparable_count:,} directly comparable Match/Different field rows. "
                f"Rows classified as Missing are excluded from this average."
            )
        if not pd.isna(weighted_rate):
            lines.append(
                f"Secondary claim-volume-weighted event rate: {weighted_rate:.1%} "
                f"({total_matched:,} matched / {total_compared:,} total event comparisons)."
            )

        lines.append(f"Total matched claims (sum across fields): {total_matched:,.0f}")
        lines.append(f"Total not-matched claims (sum across fields): {total_not:,.0f}")

        # --- 100% match count ---
        if "MatchRate" in fdf.columns:
            perfect = int((fdf["MatchRate"] >= 0.9999).sum())
            lines.append(f"Fields with 100% match rate: {perfect}")

        cls_counts = fdf["ClassificationClean"].value_counts()
        lines.append("\nClassification breakdown:")
        for cls, cnt in cls_counts.items():
            lines.append(f"  {cls}: {cnt} fields")

        disp_counts = fdf["DispositionClean"].value_counts()
        lines.append("\nDisposition breakdown:")
        for disp, cnt in disp_counts.items():
            lines.append(f"  {disp}: {cnt} fields")

        stream_stats = fdf.groupby("Stream").agg(
            total=("MatchRate", "count"),
            action_needed=("NeedsChange", "sum"),
            matched_claims=("MatchedClaims", "sum"),
            not_matched_claims=("NotMatchedClaims", "sum"),
        )
        comp_stream = (
            comparable_field_scope(fdf)
            .groupby("Stream")
            .agg(average_match=("MatchRate", "mean"),
                 comparable_fields=("MatchRate", "count"))
        )
        stream_stats = stream_stats.join(comp_stream, how="left")
        stream_stats = stream_stats.sort_values("average_match")
        lines.append("\nPer-stream summary (sorted lowest average comparable-field match first):")
        for stream, row in stream_stats.iterrows():
            lines.append(
                f"  {stream}: average match {row['average_match']:.1%} across "
                f"{int(row['comparable_fields']) if pd.notna(row['comparable_fields']) else 0} comparable fields, "
                f"{int(row['action_needed'])} fields need action, {int(row['total'])} total scored fields, "
                f"matched event comparisons {int(row['matched_claims']):,}, "
                f"not-matched {int(row['not_matched_claims']):,}"
            )

        if "RecommendationClean" in fdf.columns:
            rec_counts = fdf["RecommendationClean"].value_counts().head(8)
            lines.append("\nTop recommendations in the dataset:")
            for rec, cnt in rec_counts.items():
                if rec != "(none)":
                    lines.append(f"  '{rec}': {cnt} fields")

        # --- Smart context: send only fields relevant to the user's query ---
        ncol  = next((c for c in fdf.columns if "nch target column" in c.lower()), None)
        ntbl  = next((c for c in fdf.columns if "nch target table" in c.lower()), None)
        sstbl = next((c for c in fdf.columns if c.lower() in ("ss table", "ss_table")), None)
        sscol = next((c for c in fdf.columns if "ss col" in c.lower() and "diff" in c.lower()), None)
        rec   = next((c for c in fdf.columns if c.lower().startswith("recommendation") and "clean" not in c.lower()), None)
        lyn   = next((c for c in fdf.columns if "lynette" in c.lower()), None)
        cmts  = next((c for c in fdf.columns if "comment" in c.lower() or "example" in c.lower()), None)

        q = query.lower()

        # 1. Column-name lookup: if the query contains an actual column name, fetch that row
        col_match = pd.DataFrame()
        if ncol and len(q) > 3:
            mask = fdf[ncol].astype(str).str.lower().str.contains(q[:40], na=False)
            col_match = fdf[mask]

        # 2. Stream filter
        stream_hit = next((s for s in fdf["Stream"].unique() if s.lower() in q), None)

        # 3. Intent flags
        wants_action  = any(w in q for w in ("action", "fix", "change", "issue", "problem", "wrong"))
        wants_perfect = any(w in q for w in ("100%", "100 %", "perfect", "all match", "fully match"))
        wants_lowest  = any(w in q for w in ("low", "worst", "bad", "least", "bottom", "poor"))

        def _row(r, full=False):
            mr = f"{r['MatchRate']:.3f}" if pd.notna(r.get("MatchRate")) else "?"
            base = [
                str(r.get("Stream", "")),
                str(r.get(ntbl, "")) if ntbl else "",
                str(r.get(ncol, "")) if ncol else "",
                mr,
                str(r.get("DispositionClean", "")),
                str(r.get("ClassificationClean", "")),
                str(r.get("SubClassClean", "")),
            ]
            if full:
                mc = f"{int(r['MatchedClaims'])}" if pd.notna(r.get("MatchedClaims")) else ""
                nm = f"{int(r['NotMatchedClaims'])}" if pd.notna(r.get("NotMatchedClaims")) else ""
                base += [
                    str(r.get(sstbl, "")) if sstbl else "",
                    str(r.get(sscol, "")) if sscol else "",
                    mc, nm,
                    str(r.get(rec, "")) if rec else "",
                    str(r.get(lyn, "")) if lyn else "",
                    str(r.get(cmts, ""))[:80] if cmts else "",
                ]
            return "|".join(base)

        if not col_match.empty:
            # Exact column lookup — always full detail
            lines.append("\n=== MATCHED FIELDS (query lookup) ===")
            lines.append("stream|nch_table|nch_col|match_rate|disposition|class|subclass|ss_table|ss_col|matched|not_matched|rec|lynette|comments")
            for _, r in col_match.head(20).iterrows():
                lines.append(_row(r, full=True))

        elif wants_perfect:
            # "100% match" queries — aggregate answer is in summary stats above; add count
            perfect_ct = int((fdf["MatchRate"] >= 0.9999).sum())
            lines.append(f"\nFields with 100% match rate (MatchRate >= 99.99%): {perfect_ct}")
            lines.append("Top 10 sample 100%-match fields:")
            for _, r in fdf[fdf["MatchRate"] >= 0.9999].head(10).iterrows():
                lines.append(_row(r))

        else:
            # General or action/stream/lowest queries — send targeted subset (max 80 rows)
            pool = fdf.copy()
            if stream_hit:
                pool = pool[pool["Stream"] == stream_hit]
            if wants_action:
                pool = pool[pool["NeedsChange"]]
            elif wants_lowest:
                pool = pool.nsmallest(80, "MatchRate")

            action_pool = pool[pool["NeedsChange"]].head(60)
            other_pool  = pool[~pool["NeedsChange"]].nsmallest(20, "MatchRate")
            target = pd.concat([action_pool, other_pool]).drop_duplicates().head(80)

            if not target.empty:
                lines.append("\n=== RELEVANT FIELDS (stream|nch_table|nch_col|match_rate|disposition|class|subclass|ss_table|ss_col|matched|not_matched|rec|lynette|comments) ===")
                for _, r in target.iterrows():
                    lines.append(_row(r, full=True))

        lines.append("\n--- Business Rules File (CMS NCH to NCH STTM) ---")
        lines.append(f"Total rows in STTM rules mapping: {len(df_rules):,}")

        if "HasRule" in fdf.columns:
            with_rule = int(fdf["HasRule"].sum())
            lines.append(f"Fields WITH a linked business rule: {with_rule:,}")
            lines.append(f"Fields WITHOUT a linked business rule: {len(fdf) - with_rule:,}")

        return "\n".join(lines)

    def _make_system_prompt(query=""):
        data_context = build_data_context(query)
        return f"""You are a senior data quality analyst for a CMS (Centers for Medicare & Medicaid Services) data integrity project called DART (Data Assurance Reconciliation Tracker).

PROJECT: Shared Systems Data Analysis Study (SSDS) — Claims Data Feasibility Analysis.

GOALS:
- Determine differences and commonalities between Shared Systems Phase 3 data and NCH/CVM output files.
- Determine the extent to which SS Phase 3 data can serve as adjudicated Part A & B claims for IDR users.
- Reduce inconsistencies between the two claim streams and eliminate duplicate processing/storage.
- Ensure data transparency to IDR users.
- Provide recommendations to CMS on data quality remediation.

SYSTEMS:
- {SYS_A} (System A): National Claims History / CWF Version.
- {SYS_B} (System B): Shared Systems — MCS (PROF), FISS (INP/SNF, HH, HSPC, OUT), VMS (DME).
  This analysis compares SS Phase 3 data against NCH/CVM for period 1/1/24–12/31/24.

DATA COLUMNS: Stream, NCH Target Table, NCH Target Column, SS Table, SS Column,
Matched event count, Not-Matched event count, Match Rate,
Classification (Match/Different/Missing/Table is empty),
Sub-Classification, Disposition (Good/Action needed), Recommendation, Lynette's recommendation.

Current dataset summary (sidebar filters applied):
{data_context}

Be concise and data-driven. Use markdown. Do not fabricate statistics not present above."""

    # Chat history
    if "chatbot_messages" not in st.session_state:
        st.session_state.chatbot_messages = [
            {"role": "assistant", "content":
             f"🎯 Hi! I'm **Dartboard**, your AI data quality assistant for the **{SYS_A} ↔ {SYS_B}** analysis.\n\n"
             f"I have access to **{len(fdf):,} fields** and **{len(df_rules):,} business rules** from both source files. "
             "Ask me anything about data integrity, mismatches, rule coverage, or action priorities."}
        ]

    for msg in st.session_state.chatbot_messages:
        with st.chat_message(msg["role"], avatar="\U0001f916" if msg["role"] == "assistant" else "\U0001f464"):
            st.markdown(msg["content"])

    if prompt := st.chat_input("Ask about data quality, mismatches, business rules, streams…"):
        st.session_state.chatbot_messages.append({"role": "user", "content": prompt})
        with st.chat_message("user", avatar="\U0001f464"):
            st.markdown(prompt)

        with st.chat_message("assistant", avatar="\U0001f916"):
            try:
                import httpx
                client = OpenAI(
                    api_key=st.session_state.groq_api_key,
                    base_url="https://api.groq.com/openai/v1",
                    http_client=httpx.Client(verify=False),  # bypass corporate SSL proxy
                )
                history = st.session_state.chatbot_messages[-10:]
                # Build query-aware system prompt fresh for each message
                system_prompt = _make_system_prompt(prompt)
                messages = [{"role": "system", "content": system_prompt}] + [
                    {"role": m["role"], "content": m["content"]} for m in history
                ]
                with st.spinner(f"Thinking with {model_choice}…"):
                    resp = client.chat.completions.create(
                        model=model_choice,
                        messages=messages,
                        max_tokens=900,
                        temperature=0.25,
                    )
                reply = resp.choices[0].message.content
            except Exception as exc:
                reply = (f"\u26a0\ufe0f **Groq error:** {exc}\n\n"
                         "Check your API key in the sidebar. Get a free key at "
                         "[console.groq.com](https://console.groq.com).")
            st.markdown(reply)

        st.session_state.chatbot_messages.append({"role": "assistant", "content": reply})

    st.markdown("<br>", unsafe_allow_html=True)
    if st.button("\U0001f5d1\ufe0f Clear conversation", key="clear_chat_btn"):
        del st.session_state.chatbot_messages
        st.rerun()


view_map = {
    "overview": render_overview_tab,
    "ai": render_ai_tab,
    "compare": render_compare_tab,
    "integrity": render_integrity_tab,
    "email_agent": render_email_agent_tab,
    "domains": render_domains_tab,
    "usecases": render_usecases_tab,
    "connections": render_connections_tab,
    "correlations": render_correlations_tab,
    "explorer": render_explorer_tab,
    "rules": render_rules_tab,
    "actions": render_actions_tab,
    "custom": render_custom_tab,
    "chatbot": render_chatbot_tab,
}

view_map[st.session_state.active_view]()