chatbot / src /admin_export.py
SuhasGholkar's picture
Update src/admin_export.py
d19d204 verified
Raw History Blame Contribute Delete
10.8 kB
# src/admin_export.py
from __future__ import annotations
import io
from datetime import datetime, timedelta
from typing import List, Dict, Any, Tuple
import streamlit as st
from src.utils import get_connection
from src.video_export import export_records_to_video
# ---- constants
TXT_CSV_LIMIT = 100
VIDEO_LIMIT = 20
PAGE_SIZE = 10
# ---- session keys (namespaced)
SS_SEL = "admin_export_selected_ids"
SS_PAGE = "admin_export_page"
SS_SELALL = "admin_export_select_all"
SS_USER_MATCHES = "admin_export_user_matches"
SS_PICKED_USER = "admin_export_picked_user"
# ---------------------------
# helpers: db queries
# ---------------------------
def _query_users_like(prefix: str) -> List[Dict[str, Any]]:
"""Return users whose username or email starts-with prefix (case-insensitive)."""
if not prefix:
return []
q = """
SELECT id, username, email
FROM users
WHERE (LOWER(username) LIKE ? OR LOWER(email) LIKE ?)
ORDER BY username
LIMIT 30
"""
like = f"{prefix.lower()}%"
with get_connection() as conn:
rows = conn.execute(q, (like, like)).fetchall()
return [dict(r) for r in rows]
def _query_chats(conn, user_id: int, start_iso: str, end_iso: str) -> List[Dict[str, Any]]:
q = """
SELECT id, timestamp, user_message, bot_reply, intent, sentiment, resolution, ftr_ms, user_id
FROM chat_history
WHERE user_id = ?
AND timestamp >= ?
AND timestamp < ?
ORDER BY timestamp ASC
"""
rows = conn.execute(q, (user_id, start_iso, end_iso)).fetchall()
return [dict(r) for r in rows]
# ---------------------------
# ui helpers
# ---------------------------
def _ensure_state():
if SS_SEL not in st.session_state:
st.session_state[SS_SEL] = set() # store selected chat ids
if SS_PAGE not in st.session_state:
st.session_state[SS_PAGE] = 1
if SS_SELALL not in st.session_state:
st.session_state[SS_SELALL] = False
if SS_USER_MATCHES not in st.session_state:
st.session_state[SS_USER_MATCHES] = []
if SS_PICKED_USER not in st.session_state:
st.session_state[SS_PICKED_USER] = None
def _dt_to_iso(d: datetime) -> str:
return d.isoformat(timespec="seconds")
def _page_slice(rows: List[Dict[str, Any]], page: int, page_size: int) -> Tuple[int, List[Dict[str, Any]]]:
total_pages = max(1, (len(rows) + page_size - 1) // page_size)
page = min(max(1, page), total_pages)
i0 = (page - 1) * page_size
i1 = min(len(rows), i0 + page_size)
return total_pages, rows[i0:i1]
def _tag(text: str, color: str) -> str:
return f"""<span style="
background:{color};color:#0f172a;border-radius:999px;
padding:2px 8px;font-size:12px;border:1px solid rgba(15,23,42,.08)
">{text}</span>"""
# ---------------------------
# main render
# ---------------------------
def render_admin_export_tab(conn):
"""
Admin export tab with:
- date range + typeahead user picker
- paginated grid
- per-row checkboxes + Select-all-on-page
- export TXT / CSV / Video with limits
"""
_ensure_state()
st.subheader("Admin: Export Chat History")
# --- filters row
c1, c2 = st.columns([1, 1])
with c1:
start = st.date_input("From date", value=(datetime.utcnow() - timedelta(days=7)).date())
with c2:
end = st.date_input("To date", value=datetime.utcnow().date())
# time clamp
start_dt = datetime.combine(start, datetime.min.time())
end_dt = datetime.combine(end, datetime.max.time())
start_iso, end_iso = _dt_to_iso(start_dt), _dt_to_iso(end_dt)
st.markdown("##### Pick User")
cc1, cc2 = st.columns([1, 1])
with cc1:
prefix = st.text_input("Type at least 3 letters (username/email)", placeholder="e.g. use… or adm…")
if len(prefix.strip()) >= 3:
st.session_state[SS_USER_MATCHES] = _query_users_like(prefix.strip())
else:
st.session_state[SS_USER_MATCHES] = []
with cc2:
matches = st.session_state[SS_USER_MATCHES]
if matches:
options = [f"{m['username']} <{m['email']}>" for m in matches]
picked_label = st.selectbox("Matches", options, index=0 if options else None)
picked = matches[options.index(picked_label)] if options else None
else:
picked_label, picked = st.selectbox("Matches", ["(no matches)"]), None
# store picked user id
st.session_state[SS_PICKED_USER] = picked["id"] if picked else None
if not picked:
st.info("Search a user (≥3 chars), pick one, then data will appear here.")
# show zeroed table structure for consistency
_draw_empty_grid_hint()
return
# --- fetch
rows = _query_chats(conn, picked["id"], start_iso, end_iso)
total_pages, page_rows = _page_slice(rows, st.session_state[SS_PAGE], PAGE_SIZE)
# --- page select
left, right = st.columns([1, 5])
with left:
page = st.number_input("Page", min_value=1, max_value=total_pages, value=st.session_state[SS_PAGE], step=1)
if page != st.session_state[SS_PAGE]:
st.session_state[SS_PAGE] = page
# reset select-all for new page
st.session_state[SS_SELALL] = False
st.rerun()
# --- grid header
_draw_grid_header()
# --- select-all on page
st.checkbox(
"Select all rows on this page",
key=SS_SELALL,
value=st.session_state[SS_SELALL],
on_change=_toggle_select_all_for_page,
args=(page_rows,),
)
# --- rows with checkboxes
sel_ids = st.session_state[SS_SEL]
for r in page_rows:
rid = r["id"]
checked = rid in sel_ids
cols = st.columns([0.6, 1, 1, 2, 1, 1, 1, 0.8]) # [chk,id,timestamp,user_message,bot_reply,intent,sentiment,resolution,ftr]
with cols[0]:
# unique key per row checkbox
cur = st.checkbox("", value=checked, key=f"sel_{rid}")
if cur and not checked:
sel_ids.add(rid)
elif (not cur) and checked:
sel_ids.discard(rid)
cols[1].markdown(f"`{r['id']}`")
cols[2].markdown(f"{r['timestamp']}")
cols[3].markdown(f"{(r['user_message'] or '')[:30]}")
cols[4].markdown(f"{(r['bot_reply'] or '')[:40]}")
cols[5].markdown(_tag(r.get("intent") or "-", "#E0F2FE"), unsafe_allow_html=True)
cols[6].markdown(_tag(r.get("sentiment") or "-", "#FDE68A"), unsafe_allow_html=True)
cols[7].markdown(_tag(r.get("resolution") or "-", "#DCFCE7"), unsafe_allow_html=True)
st.caption(f"Selected {len(sel_ids)} records (Page {st.session_state[SS_PAGE]}/{total_pages})")
# --- single-user enforcement: naturally satisfied (grid filters by chosen user)
# but we also cleanup any selected IDs that are not in 'rows' for this user/time window:
valid_ids = {r["id"] for r in rows}
stale = set(sel_ids) - valid_ids
if stale:
for x in stale:
sel_ids.discard(x)
# --- export controls
st.subheader("Export Options")
c_txt, c_csv, c_vid = st.columns(3)
with c_txt:
if st.button("⬇️ Export TXT", use_container_width=True, disabled=len(sel_ids) == 0):
ids = list(sel_ids)[:TXT_CSV_LIMIT]
buf = io.StringIO()
for r in rows:
if r["id"] in ids:
buf.write(f"[{r['timestamp']}] USER: {r['user_message']}\n")
buf.write(f"[{r['timestamp']}] BOT: {r['bot_reply']}\n")
buf.write("-" * 60 + "\n")
st.download_button("Download TXT", buf.getvalue(), file_name="chat_export.txt", mime="text/plain", use_container_width=True)
with c_csv:
if st.button("⬇️ Export CSV", use_container_width=True, disabled=len(sel_ids) == 0):
import csv
ids = list(sel_ids)[:TXT_CSV_LIMIT]
out = io.StringIO()
w = csv.writer(out)
w.writerow(["id", "timestamp", "user_message", "bot_reply", "intent", "sentiment", "resolution", "ftr_ms"])
for r in rows:
if r["id"] in ids:
w.writerow([r["id"], r["timestamp"], r["user_message"], r["bot_reply"], r["intent"], r["sentiment"], r["resolution"], r["ftr_ms"]])
st.download_button("Download CSV", out.getvalue(), file_name="chat_export.csv", mime="text/csv", use_container_width=True)
with c_vid:
disable_video = len(sel_ids) == 0 or len(sel_ids) > VIDEO_LIMIT
note = "" if len(sel_ids) <= VIDEO_LIMIT else f"(limit {VIDEO_LIMIT})"
if st.button(f"🎬 Export Video {note}", use_container_width=True, disabled=disable_video):
ids = sorted(list(sel_ids))[:VIDEO_LIMIT]
chosen = [r for r in rows if r["id"] in ids]
try:
video_path = export_records_to_video(chosen, tts=True, min_secs_per_slide=4.0)
st.video(video_path)
st.success("Video ready.")
st.download_button("Download MP4", open(video_path, "rb"), file_name="chat_export.mp4", mime="video/mp4", use_container_width=True)
except Exception as e:
st.error(f"Video export failed: {e}")
st.divider()
st.caption("Tip: switch pages; selections persist. Use the ‘Select all rows on this page’ checkbox to bulk select.")
def _toggle_select_all_for_page(page_rows: List[Dict[str, Any]]):
sel = st.session_state[SS_SEL]
if st.session_state[SS_SELALL]:
for r in page_rows:
sel.add(r["id"])
else:
for r in page_rows:
sel.discard(r["id"])
def _draw_grid_header():
st.markdown(
"""
<div style="display:grid;grid-template-columns:40px 80px 200px 1fr 1.2fr 120px 120px 120px;gap:8px;
padding:8px 12px;border:1px solid #e5e7eb;border-radius:8px;background:#F8FAFC;">
<div></div>
<div style="font-weight:600">id</div>
<div style="font-weight:600">timestamp</div>
<div style="font-weight:600">user_message</div>
<div style="font-weight:600">bot_reply</div>
<div style="font-weight:600">intent</div>
<div style="font-weight:600">sentiment</div>
<div style="font-weight:600">resolution</div>
</div>
""",
unsafe_allow_html=True,
)
def _draw_empty_grid_hint():
st.markdown(
"""
<div style="margin-top:8px;border:1px dashed #e5e7eb;border-radius:8px;padding:16px;background:#F8FAFC;">
<em>No data yet.</em> Pick a user to view chats. You can then select rows with checkboxes and export.
</div>
""",
unsafe_allow_html=True,
)