# 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"""{text}""" # --------------------------- # 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( """