Spaces:
Sleeping
Sleeping
Download src/admin_export.py from SuhasGholkar/chatbot: direct link, hf CLI and curl.
- Browser
- Download file 10.8 kB
-
https://huggingface.co/spaces/SuhasGholkar/chatbot/resolve/main/src/admin_export.py
- Command line
-
hf download hf://spaces/SuhasGholkar/chatbot/src/admin_export.py
-
curl -L -o admin_export.py https://huggingface.co/spaces/SuhasGholkar/chatbot/resolve/main/src/admin_export.py
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, | |
| ) | |