Spaces:
Sleeping
Sleeping
File size: 16,255 Bytes
5f637af a277ed7 5f637af a277ed7 5f637af 172e479 6a99dd9 11e92ae 5f637af a277ed7 13c6682 a277ed7 13c6682 a277ed7 13c6682 ea1514b a277ed7 ea1514b a277ed7 13c6682 a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b 13c6682 a277ed7 ea1514b 13c6682 a277ed7 172e479 a8d3a7a 172e479 a8d3a7a 172e479 a277ed7 ea1514b a277ed7 d8d9973 ea1514b 13c6682 d8d9973 a277ed7 d8d9973 a277ed7 d8d9973 a277ed7 13c6682 ea1514b a277ed7 ea1514b 13c6682 ea1514b 13c6682 a277ed7 13c6682 172e479 13c6682 5f637af 13c6682 5f637af a277ed7 06362b8 ea1514b 06362b8 a277ed7 06362b8 d8d9973 a277ed7 d8d9973 a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 7d52c3a a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 ea1514b a277ed7 172e479 ea1514b a277ed7 ea1514b 0aa516c 6a99dd9 78addfa ae90a2d 78addfa c1881c9 5f637af e1b491c 172e479 e1b491c 5f637af e1b491c 5f637af 06362b8 e1b491c 06362b8 e1b491c 06362b8 e1b491c 06362b8 e1b491c 06362b8 e1b491c 78addfa 172e479 5f637af e1b491c 7d52c3a e1b491c 172e479 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 | # src/mis.py
from __future__ import annotations
import streamlit as st
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime, date, timedelta
import sqlite3
# -----------------------------
# Lightweight data helpers
# -----------------------------
def _load_distinct_intents(conn) -> list[str]:
"""Return distinct non-empty intents (sorted)."""
try:
rows = conn.execute(
"SELECT DISTINCT intent FROM chat_history WHERE intent IS NOT NULL"
).fetchall()
return sorted([r[0] for r in rows if r and r[0]])
except Exception:
return []
def _load_users_for_picker(conn) -> pd.DataFrame:
"""Return DataFrame with id + label (username/email) for user picker."""
try:
df = pd.read_sql_query(
"SELECT id, username, email FROM users ORDER BY username COLLATE NOCASE",
conn,
)
df["label"] = df["username"].fillna(df["email"])
return df[["id", "label"]]
except Exception:
return pd.DataFrame(columns=["id", "label"])
def _apply_filters_base_query(
start_str: str | None,
end_str: str | None,
chosen_intents: list[str],
user_id_or_none: int | None,
) -> tuple[str, list]:
"""
Build WHERE clause for chat_history + users join, using column aliases:
ch.<col> for chat_history, u.<col> for users.
"""
where = ["1=1"]
params: list = []
if start_str:
where.append("date(ch.timestamp) >= ?")
params.append(start_str)
if end_str:
where.append("date(ch.timestamp) <= ?")
params.append(end_str)
if chosen_intents:
q = ",".join(["?"] * len(chosen_intents))
where.append(f"ch.intent IN ({q})")
params.extend(chosen_intents)
if user_id_or_none:
where.append("ch.user_id = ?")
params.append(int(user_id_or_none))
return " WHERE " + " AND ".join(where) + " ", params
def _metric_card(label: str, value):
"""Render a small st.metric safely."""
if value is None:
value = "-"
st.metric(label, value)
# -----------------------------
# Caching layer for data query
# -----------------------------
@st.cache_data(show_spinner="Fetching data...")
def _load_mis_data(
_conn: sqlite3.Connection,
start_str: str | None,
end_str: str | None,
chosen_intents: list[str],
user_id_or_none: int | None,
) -> pd.DataFrame:
"""
Fetches filtered data from the database and caches it.
"""
where_sql, params = _apply_filters_base_query(
start_str, end_str, chosen_intents, user_id_or_none
)
base_sql = """
SELECT ch.id, ch.user_id, u.username,
ch.timestamp, ch.intent, ch.sentiment, ch.resolution, ch.ftr_ms,
substr(ch.user_message, 1, 200) AS user_message
FROM chat_history ch
JOIN users u ON u.id = ch.user_id
"""
return pd.read_sql_query(
base_sql + where_sql + " ORDER BY ch.timestamp DESC", _conn, params=params
)
# -----------------------------
# Compact Admin MIS
# -----------------------------
def render_mis_admin(conn):
"""
Compact Admin MIS:
- Preserves your filters and KPIs.
- Uses usernames (not IDs) in charts & tables.
- Four mini charts (~200px tall).
- Filtered table tucked in an expander.
"""
st.markdown("### 📊 MIS (Admin) — Compact")
# ---------- Filters ----------
intents = _load_distinct_intents(conn)
users_df = _load_users_for_picker(conn)
f1, f2, f3 = st.columns([1, 1, 1])
with f1:
start_date = st.date_input(
"From", value=(date.today() - timedelta(days=30)), key="mis_admin_from"
)
with f2:
end_date = st.date_input("To", value=date.today(), key="mis_admin_to")
with f3:
chosen_intents = st.multiselect(
"Intent(s)", intents, default=[], key="mis_admin_intents"
)
user_picker = None
if not users_df.empty:
label_to_id = {row.label: int(row.id) for _, row in users_df.iterrows()}
sel = st.selectbox(
"User", options=["(All)"] + users_df["label"].tolist(), index=0, key="mis_admin_user"
)
user_picker = label_to_id.get(sel, None)
else:
st.info("No users available to filter.")
start_str = start_date.strftime("%Y-%m-%d") if start_date else None
end_str = end_date.strftime("%Y-%m-%d") if end_date else None
# ---------- Load filtered dataset with USERNAME join (using cached function) ----------
df = _load_mis_data(conn, start_str, end_str, chosen_intents, user_picker)
if df.empty:
st.info("No chats found for the selected filters.")
return
# ---------- KPIs (preserved; rendered as compact cards) ----------
total = len(df)
resolved_pct = (df["resolution"] == "Resolved").mean() if "resolution" in df else None
avg_ftr = df["ftr_ms"].dropna().mean() if "ftr_ms" in df else None
p95_ftr = (
df["ftr_ms"].dropna().quantile(0.95)
if "ftr_ms" in df and not df["ftr_ms"].dropna().empty
else None
)
k1, k2, k3, k4 = st.columns(4)
with k1:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card("Total chats", f"{total}")
st.markdown("</div>", unsafe_allow_html=True)
with k2:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card(
"Resolved %", f"{resolved_pct*100:.1f}%" if resolved_pct is not None else None
)
st.markdown("</div>", unsafe_allow_html=True)
with k3:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card("Avg FTR (ms)", f"{avg_ftr:.0f}" if avg_ftr is not None else None)
st.markdown("</div>", unsafe_allow_html=True)
with k4:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card("P95 FTR (ms)", f"{p95_ftr:.0f}" if p95_ftr is not None else None)
st.markdown("</div>", unsafe_allow_html=True)
st.markdown("")
# ---------- Mini charts (4 panels, compact) ----------
cA, cB = st.columns(2, gap="small")
cC, cD = st.columns(2, gap="small")
# A) Volume by Day
with cA:
st.markdown(
'<div class="mini-chart"><b>Volume by day</b></div>', unsafe_allow_html=True
)
_plot_bar_from_series(_series_count_by_day(df), height_in=2.0, rotate=30)
# B) Intents (Top 10)
with cB:
st.markdown(
'<div class="mini-chart"><b>Intents (Top 10)</b></div>', unsafe_allow_html=True
)
top_intents = df["intent"].fillna("UNMAPPED").value_counts().head(10)
_plot_bar_from_series(top_intents, height_in=2.0, rotate=30)
# C) Sentiment distribution
with cC:
st.markdown(
'<div class="mini-chart"><b>Sentiment distribution</b></div>',
unsafe_allow_html=True,
)
sent = df["sentiment"].fillna("Unknown").value_counts()
_plot_bar_from_series(sent, height_in=2.0, rotate=0)
# D) Top users by chat volume (username, truncated labels to keep compact)
with cD:
st.markdown(
'<div class="mini-chart"><b>Top users by chat volume</b></div>',
unsafe_allow_html=True,
)
top_users = (
df["username"]
.fillna("(unknown)")
.apply(lambda s: s if len(s) <= 20 else s[:17] + "…")
.value_counts()
.head(10)
)
_plot_bar_from_series(top_users, height_in=2.0, rotate=0)
# ---------- Filtered table (use USERNAME, not user_id) ----------
# Manual state management without 'key'
if 'admin_expander_expanded' not in st.session_state:
st.session_state.admin_expander_expanded = False
with st.expander("Filtered chats (first 100)", expanded=st.session_state.admin_expander_expanded):
st.session_state.admin_expander_expanded = True
show_cols = [
"timestamp",
"username",
"intent",
"sentiment",
"resolution",
"user_message",
"ftr_ms",
]
show_cols = [c for c in show_cols if c in df.columns]
st.dataframe(df[show_cols].head(100), use_container_width=True, height=320)
# -----------------------------
# Tiny plotting helpers
# -----------------------------
def _series_count_by_day(df: pd.DataFrame) -> pd.Series:
"""Return a series: day(str) -> count."""
s = pd.to_datetime(df["timestamp"]).dt.date.value_counts().sort_index()
s.index = s.index.astype(str)
return s
def _plot_bar_from_series(s: pd.Series, height_in: float = 2.0, rotate: int = 0):
"""Render a compact bar chart from a series."""
if s is None or s.empty:
st.caption("No data for current filters.")
return
fig, ax = plt.subplots(figsize=(5.6, height_in))
ax.bar(s.index.astype(str), s.values)
ax.set_ylabel("Count")
ax.set_title("")
plt.setp(
ax.get_xticklabels(),
rotation=rotate,
ha="right" if rotate else "center",
fontsize=8,
)
st.pyplot(fig, use_container_width=True)
def _load_distinct_intents_for_user(conn, user_id: int) -> list[str]:
"""Return distinct intents used by this user."""
try:
rows = conn.execute(
"SELECT DISTINCT intent FROM chat_history WHERE user_id = ? AND intent IS NOT NULL",
(user_id,),
).fetchall()
return sorted([r[0] for r in rows if r and r[0]])
except Exception:
return []
def _apply_filters_base_query_user(
start_str: str | None,
end_str: str | None,
chosen_intents: list[str],
user_id: int,
) -> tuple[str, list]:
"""Construct WHERE clause and params for filtering user MIS queries."""
where = ["ch.user_id = ?"]
params = [int(user_id)]
if start_str:
where.append("date(ch.timestamp) >= ?"); params.append(start_str)
if end_str:
where.append("date(ch.timestamp) <= ?"); params.append(end_str)
if chosen_intents:
q = ",".join(["?"] * len(chosen_intents))
where.append(f"ch.intent IN ({q})"); params.extend(chosen_intents)
return " WHERE " + " AND ".join(where) + " ", params
def _one_val(sql: str, conn: sqlite3.Connection):
return pd.read_sql_query(sql, conn).iloc[0,0]
def kpi_avg_delivery_days(conn: sqlite3.Connection):
avg_days = _one_val("""
SELECT AVG(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp))
FROM olist_orders
WHERE order_delivered_customer_date IS NOT NULL
AND order_purchase_timestamp >= date('now','-30 day')
""", conn)
st.metric("Avg delivery time (30d)", f"{(avg_days or 0):.2f} days")
def _plot_resolution_rate_by_day(df, height_in=2.5):
"""
Plot daily resolution rate = (# resolved / total) per day.
"""
if df.empty or "timestamp" not in df or "resolution" not in df:
st.info("No resolution data to plot.")
return
# Ensure datetime
df["date_only"] = pd.to_datetime(df["timestamp"]).dt.date
grouped = df.groupby("date_only")["resolution"].apply(
lambda x: (x == "Resolved").mean()
)
fig, ax = plt.subplots(figsize=(5, height_in))
grouped.plot(kind="line", marker="o", ax=ax, color="#2563eb")
ax.set_title("Resolution Rate by Day")
ax.set_ylabel("Resolved %")
ax.set_xlabel("Date")
ax.set_ylim(0, 1)
ax.grid(True, alpha=0.3)
st.pyplot(fig)
def render_mis_user(conn, user_id: int):
"""
Compact MIS for a single end user.
- Filters: From / To, (optional) Intent(s)
- 4 small KPI cards
- 4 compact charts (~200px tall)
- Filtered table with username (not user_id)
"""
st.markdown("### 📊 My MIS — Compact")
# ---------- Filters ----------
intents = _load_distinct_intents_for_user(conn, user_id)
f1, f2, f3 = st.columns([1, 1, 1])
with f1:
start_date = st.date_input(
"From", value=(date.today() - timedelta(days=30)), key="mis_user_from"
)
with f2:
end_date = st.date_input("To", value=date.today(), key="mis_user_to")
with f3:
chosen_intents = st.multiselect(
"Intent(s)", intents, default=[], key="mis_user_intents"
)
start_str = start_date.strftime("%Y-%m-%d") if start_date else None
end_str = end_date.strftime("%Y-%m-%d") if end_date else None
# WHERE limited to this user
where_sql, params = _apply_filters_base_query_user(
start_str=start_str,
end_str=end_str,
chosen_intents=chosen_intents,
user_id=user_id,
)
# ---------- Load filtered dataset with USERNAME join (using cached function) ----------
df = _load_mis_data(conn, start_str, end_str, chosen_intents, user_id)
if df.empty:
st.info("No chats found for the selected filters.")
return
# ---------- KPIs (compact cards) ----------
total = len(df)
resolved_pct = (df["resolution"] == "Resolved").mean() if "resolution" in df else None
avg_ftr = df["ftr_ms"].dropna().mean() if "ftr_ms" in df else None
p95_ftr = (
df["ftr_ms"].dropna().quantile(0.95)
if "ftr_ms" in df and not df["ftr_ms"].dropna().empty
else None
)
k1, k2, k3, k4 = st.columns(4)
with k1:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card("My total chats", f"{total}")
st.markdown("</div>", unsafe_allow_html=True)
with k2:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card(
"Resolved %", f"{resolved_pct*100:.1f}%" if resolved_pct is not None else None
)
st.markdown("</div>", unsafe_allow_html=True)
with k3:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card("Avg FTR (ms)", f"{avg_ftr:.0f}" if avg_ftr is not None else None)
st.markdown("</div>", unsafe_allow_html=True)
with k4:
st.markdown('<div class="kpi-card">', unsafe_allow_html=True)
_metric_card("P95 FTR (ms)", f"{p95_ftr:.0f}" if p95_ftr is not None else None)
st.markdown("</div>", unsafe_allow_html=True)
st.markdown("")
# ---------- Mini charts (4 panels) ----------
cA, cB = st.columns(2, gap="small")
cC, cD = st.columns(2, gap="small")
# A) Volume by Day (my activity)
with cA:
st.markdown('<div class="mini-chart"><b>My volume by day</b></div>', unsafe_allow_html=True)
_plot_bar_from_series(_series_count_by_day(df), height_in=2.0, rotate=30)
# B) My Intents (Top 10)
with cB:
st.markdown('<div class="mini-chart"><b>My intents (Top 10)</b></div>', unsafe_allow_html=True)
top_intents = df["intent"].fillna("UNMAPPED").value_counts().head(10)
_plot_bar_from_series(top_intents, height_in=2.0, rotate=30)
# C) Sentiment distribution
with cC:
st.markdown('<div class="mini-chart"><b>My sentiment distribution</b></div>', unsafe_allow_html=True)
sent = df["sentiment"].fillna("Unknown").value_counts()
_plot_bar_from_series(sent, height_in=2.0, rotate=0)
# D) Resolution by day (industry-favorite view)
with cD:
st.markdown('<div class="mini-chart"><b>Average Delivery Time in Days (Last 30 days)</b></div>', unsafe_allow_html=True)
#_plot_resolution_rate_by_day(df, height_in=2.0)
kpi_avg_delivery_days(conn)
# ---------- Filtered table (username, not ID) ----------
# Manual state management without 'key'
if 'user_expander_expanded' not in st.session_state:
st.session_state.user_expander_expanded = False
with st.expander("My filtered chats (first 50)", expanded=st.session_state.user_expander_expanded):
st.session_state.user_expander_expanded = True
show_cols = [
"timestamp",
"username",
"intent",
"sentiment",
"resolution",
"user_message",
"ftr_ms",
]
show_cols = [c for c in show_cols if c in df.columns]
st.dataframe(df[show_cols].head(50), use_container_width=True, height=320) |