File size: 10,827 Bytes
2781e17
b9ad84d
636bc6f
 
b9ad84d
636bc6f
2781e17
b9ad84d
 
5ac3894
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
636bc6f
 
b9ad84d
 
 
 
 
 
 
636bc6f
 
b9ad84d
 
 
 
636bc6f
 
b9ad84d
 
 
 
 
636bc6f
 
 
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
 
 
636bc6f
2781e17
b9ad84d
 
636bc6f
 
b9ad84d
 
 
 
 
 
2781e17
 
b9ad84d
 
 
 
 
636bc6f
 
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
5ac3894
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
636bc6f
b9ad84d
 
636bc6f
b9ad84d
 
 
 
636bc6f
 
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
ec8b966
d19d204
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
636bc6f
 
b9ad84d
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
636bc6f
 
b9ad84d
 
 
 
 
 
 
 
 
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
# 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,
    )