# ========================= # Olist Chatbot — app.py # ========================= # ---- Streamlit must be first for page config ---- import streamlit as st # st.set_page_config(page_title="Olist Chatbot", layout="wide") st.set_page_config( page_title="Olist Chatbot 🚀", page_icon="🛍️", layout="wide" ) st.markdown( """
Olist Chatbot 🚀
Your AI-Powered Shopping Assistant
""", unsafe_allow_html=True ) # ---- Standard imports ---- import os import re import sqlite3 import logging import json import time from datetime import datetime import pathlib from pathlib import Path import shutil import dateparser import pandas as pd # OpenAI SDK v1 from openai import OpenAI # Matplotlib: set non-interactive backend BEFORE importing pyplot import matplotlib matplotlib.use("Agg") import matplotlib.pyplot as plt # ---- Video export deps (NO ImageMagick path) ---- import numpy as np from io import BytesIO import textwrap from PIL import Image, ImageDraw, ImageFont from moviepy.editor import ImageClip, ColorClip, CompositeVideoClip, concatenate_videoclips from moviepy.audio.io.AudioFileClip import AudioFileClip # ---- Optional TTS (gTTS) ---- from gtts import gTTS # ========================= # Writable dirs & env # ========================= APP_DIR = pathlib.Path(__file__).parent.resolve() DB_PATH = Path(os.environ.get("DB_PATH", APP_DIR / "olist.sqlite")) os.environ.setdefault("HOME", str(APP_DIR)) # avoid writing to '/' os.environ.setdefault("XDG_CACHE_HOME", str(APP_DIR / ".cache")) # general cache os.environ.setdefault("MPLCONFIGDIR", str(APP_DIR / ".mplconfig")) # matplotlib cache/config os.environ.setdefault("STREAMLIT_SERVER_HEADLESS", "true") # headless always # ensure dirs exist os.path.isdir(APP_DIR / ".streamlit") os.path.isdir(APP_DIR / ".cache") os.path.isdir(APP_DIR / ".mplconfig") # ========================= # DB ensure / download # ========================= def ensure_db(): """Make sure DB_PATH exists; copy from repo or fetch from HF Hub once.""" p = Path(DB_PATH) # Already present → OK if p.exists(): return # Fallback to repo copy repo_db = APP_DIR / "olist.sqlite" if repo_db.exists(): p.parent.mkdir(parents=True, exist_ok=True) if p.resolve() != repo_db.resolve(): shutil.copy2(repo_db, p) return # Fetch from Hub (optional: set envs HF_DB_REPO / HF_DB_FILE / HF_TOKEN) from huggingface_hub import hf_hub_download repo_id = os.getenv("HF_DB_REPO", "SuhasGholkar/olist-db") filename = os.getenv("HF_DB_FILE", "olist.sqlite") token = os.getenv("HF_TOKEN") # optional for private repos local = hf_hub_download(repo_id=repo_id, filename=filename, token=token) p.parent.mkdir(parents=True, exist_ok=True) shutil.move(local, p) ensure_db() # ========================= # Logging # ========================= logging.basicConfig( level=logging.DEBUG, format="%(asctime)s [%(levelname)s] %(message)s", handlers=[logging.StreamHandler()] ) logger = logging.getLogger(__name__) # ========================= # OpenAI client (v1) # ========================= API_KEY = os.getenv("OPENAI_API_KEY") if not API_KEY: raise ValueError("OPENAI_API_KEY environment variable not set.") client = OpenAI(api_key=API_KEY) # ========================= # SQLite helpers # ========================= def get_connection(): # Use SQLite URI; check_same_thread=False for Streamlit threads uri = f"file:{DB_PATH}?" return sqlite3.connect(uri, uri=True, check_same_thread=False) def init_chat_history_table(): with get_connection() as conn: conn.execute(""" CREATE TABLE IF NOT EXISTS chat_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, timestamp TEXT, user_message TEXT, intent TEXT, sentiment TEXT, resolution TEXT, bot_reply TEXT, ftr_ms REAL, sql_query TEXT, sql_params TEXT ) """) conn.commit() def save_chat_to_db(user_message, intent, sentiment, resolution, bot_reply, ftr_ms, sql_query, sql_params): with get_connection() as conn: conn.execute(""" INSERT INTO chat_history (timestamp, user_message, intent, sentiment, resolution, bot_reply, ftr_ms, sql_query, sql_params) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) """, ( datetime.now().strftime("%Y-%m-%d %H:%M:%S"), user_message, intent, sentiment, resolution, bot_reply, ftr_ms, sql_query, json.dumps(sql_params or {}) )) conn.commit() # ========================= # Intents (SQL templates) # ========================= INTENTS = { # --- Orders --- "order_status": { "required": ["order_id"], "optional": [], "sql": """ SELECT o.order_id, o.order_status, o.order_purchase_timestamp, o.order_delivered_customer_date, o.order_estimated_delivery_date FROM olist_orders o WHERE o.order_id = :order_id LIMIT 1; """ }, "orders_by_city": { "required": ["city"], "optional": ["start_date","end_date"], "sql": """ SELECT c.customer_city AS city, COUNT(*) AS orders_count FROM olist_orders o JOIN olist_customers c USING(customer_id) WHERE LOWER(c.customer_city) = LOWER(:city) AND (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY c.customer_city; """ }, "delivery_delay_metrics": { "required": [], "optional": ["start_date","end_date"], "sql": """ SELECT ROUND(AVG(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date)), 2) AS avg_delay_days, ROUND(SUM(CASE WHEN o.order_delivered_customer_date IS NOT NULL AND o.order_estimated_delivery_date IS NOT NULL AND JULIANDAY(o.order_delivered_customer_date) > JULIANDAY(o.order_estimated_delivery_date) THEN 1 ELSE 0 END)*1.0 / SUM(CASE WHEN o.order_delivered_customer_date IS NOT NULL AND o.order_estimated_delivery_date IS NOT NULL THEN 1 ELSE 0 END), 3) AS late_ratio FROM olist_orders o WHERE o.order_delivered_customer_date IS NOT NULL AND o.order_estimated_delivery_date IS NOT NULL AND (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date); """ }, "late_orders_list": { "required": [], "optional": ["start_date","end_date","limit"], "sql": """ SELECT o.order_id, o.order_purchase_timestamp, o.order_delivered_customer_date, o.order_estimated_delivery_date, ROUND(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date), 2) AS delay_days FROM olist_orders o WHERE o.order_delivered_customer_date IS NOT NULL AND o.order_estimated_delivery_date IS NOT NULL AND JULIANDAY(o.order_delivered_customer_date) > JULIANDAY(o.order_estimated_delivery_date) AND (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) ORDER BY delay_days DESC LIMIT :limit; """ }, # --- Products --- "top_products_revenue": { "required": [], "optional": ["start_date","end_date","k"], "sql": """ SELECT i.product_id, ROUND(SUM(i.price), 2) AS revenue, COUNT(*) AS quantity_sold FROM olist_order_items i JOIN olist_orders o USING(order_id) WHERE (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY i.product_id ORDER BY revenue DESC LIMIT :k; """ }, "top_products_quantity": { "required": [], "optional": ["start_date","end_date","k"], "sql": """ SELECT i.product_id, COUNT(*) AS quantity_sold, ROUND(SUM(i.price), 2) AS revenue FROM olist_order_items i JOIN olist_orders o USING(order_id) WHERE (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY i.product_id ORDER BY quantity_sold DESC LIMIT :k; """ }, "category_sales": { "required": [], "optional": ["category","start_date","end_date"], "sql": """ SELECT p.product_category_name, p.product_category_name_english, ROUND(SUM(i.price), 2) AS revenue, COUNT(*) AS lines FROM olist_order_items i JOIN olist_orders o USING(order_id) JOIN olist_products p USING(product_id) WHERE (:category IS NULL OR LOWER(p.product_category_name) = LOWER(:category) OR LOWER(p.product_category_name_english) = LOWER(:category)) AND (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY p.product_category_name, p.product_category_name_english ORDER BY revenue DESC; """ }, "seller_performance": { "required": ["seller_id"], "optional": ["start_date","end_date"], "sql": """ SELECT i.seller_id, ROUND(SUM(i.price),2) AS revenue, COUNT(*) AS items_sold, ROUND(AVG(i.freight_value),2) AS avg_freight FROM olist_order_items i JOIN olist_orders o USING(order_id) WHERE i.seller_id = :seller_id AND (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY i.seller_id; """ }, # --- Customers & Geo --- "orders_by_state": { "required": [], "optional": ["start_date","end_date","k"], "sql": """ SELECT c.customer_state, COUNT(*) AS orders_count FROM olist_orders o JOIN olist_customers c USING(customer_id) WHERE (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY c.customer_state ORDER BY orders_count DESC LIMIT :k; """ }, "repeat_rate": { "required": [], "optional": ["start_date","end_date"], "sql": """ WITH cust_orders AS ( SELECT c.customer_unique_id AS cuid, COUNT(*) AS n FROM olist_orders o JOIN olist_customers c USING(customer_id) WHERE (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY c.customer_unique_id ) SELECT ROUND(SUM(CASE WHEN n>=2 THEN 1 ELSE 0 END)*1.0 / COUNT(*), 3) AS repeat_customer_ratio FROM cust_orders; """ }, # --- Payments --- "payments_breakdown": { "required": [], "optional": ["start_date","end_date"], "sql": """ SELECT p.payment_type, COUNT(*) AS payments_count, ROUND(SUM(p.payment_value),2) AS total_value, ROUND(AVG(p.payment_installments),2) AS avg_installments FROM olist_order_payments p JOIN olist_orders o USING(order_id) WHERE (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY p.payment_type ORDER BY total_value DESC; """ }, # --- AOV Trend --- "aov_trend_monthly": { "required": [], "optional": ["start_date","end_date"], "sql": """ WITH order_value AS ( SELECT o.order_id, DATE(o.order_purchase_timestamp, 'start of month') AS month, SUM(i.price) AS order_value FROM olist_orders o JOIN olist_order_items i USING(order_id) WHERE (:start_date IS NULL OR o.order_purchase_timestamp >= :start_date) AND (:end_date IS NULL OR o.order_purchase_timestamp < :end_date) GROUP BY o.order_id, month ) SELECT month, ROUND(AVG(order_value), 2) AS avg_order_value FROM order_value GROUP BY month ORDER BY month; """ }, # --- Reviews --- "latest_reviews": { "required": [], "optional": ["product_id","category","limit"], "sql": """ SELECT r.order_id, r.review_score, r.review_creation_date, r.review_comment_message FROM olist_order_reviews r WHERE (:product_id IS NULL OR EXISTS ( SELECT 1 FROM olist_order_items i WHERE i.order_id = r.order_id AND i.product_id = :product_id)) AND (:category IS NULL OR EXISTS ( SELECT 1 FROM olist_order_items i JOIN olist_products p USING(product_id) WHERE i.order_id = r.order_id AND (LOWER(p.product_category_name) = LOWER(:category) OR LOWER(p.product_category_name_english) = LOWER(:category)) )) ORDER BY r.review_creation_date DESC LIMIT :limit; """ }, "rating_summary": { "required": [], "optional": ["product_id","category","start_date","end_date"], "sql": """ SELECT ROUND(AVG(r.review_score),2) AS avg_rating, COUNT(*) AS n_reviews FROM olist_order_reviews r JOIN olist_orders o ON o.order_id = r.order_id WHERE (:start_date IS NULL OR r.review_creation_date >= :start_date) AND (:end_date IS NULL OR r.review_creation_date < :end_date) AND (:product_id IS NULL OR EXISTS ( SELECT 1 FROM olist_order_items i WHERE i.order_id = r.order_id AND i.product_id = :product_id)) AND (:category IS NULL OR EXISTS ( SELECT 1 FROM olist_order_items i JOIN olist_products p USING(product_id) WHERE i.order_id = r.order_id AND (LOWER(p.product_category_name) = LOWER(:category) OR LOWER(p.product_category_name_english) = LOWER(:category)) )); """ }, } INTENT_KEYWORDS = { "order_status": ["order", "status", "track", "tracking", "delivered", "delivery", "estimate", "order id"], "orders_by_city": ["city", "town", "municipality"], "delivery_delay_metrics": ["delay", "late", "delivery time", "estimated", "sla"], "late_orders_list": ["late", "delayed", "overdue"], "top_products_revenue": ["top", "revenue", "sales", "gross", "gmv"], "top_products_quantity": ["top", "quantity", "units", "sold", "bestseller"], "category_sales": ["category", "department", "sales by category"], "seller_performance": ["seller", "merchant", "vendor", "performance"], "orders_by_state": ["state", "region", "province"], "repeat_rate": ["repeat", "retention", "returning"], "payments_breakdown": ["payment", "installments", "card", "boleto", "pix", "method"], "aov_trend_monthly": ["aov", "average order value", "trend", "monthly"], "latest_reviews": ["review", "comment", "feedback", "opinion", "rating"], "rating_summary": ["rating", "score", "stars", "nps", "csat"], } def _has_overlap(intent: str, text: str) -> bool: kws = INTENT_KEYWORDS.get(intent, []) if not kws: return True t = text.lower() return any(re.search(r"\b" + re.escape(kw.lower()) + r"\b", t) for kw in kws) # ========================= # GPT helpers (OpenAI v1) # ========================= def gpt_classify_intent(user_text: str) -> dict: options = list(INTENTS.keys()) system = ( "You are an intent classifier for an e-commerce analytics chatbot. " "You MUST return strict JSON. If none of the intents clearly applies, return UNMAPPED." ) user = ( "Available intents (keys): " + ", ".join(options) + "\n" "Task: Choose the single BEST matching intent key OR 'UNMAPPED' if none clearly fits.\n" "Output STRICT JSON with fields: intent (string), confidence (0.0-1.0), reason (short string).\n" "Do NOT guess. If the message is unrelated, return UNMAPPED.\n\n" f"Message: {user_text}" ) resp = client.chat.completions.create( model="gpt-3.5-turbo", messages=[{"role":"system","content": system}, {"role":"user","content": user}], temperature=0 ) txt = resp.choices[0].message.content.strip() try: data = json.loads(txt) raw_intent = str(data.get("intent","")).strip().lower() if raw_intent == "" or raw_intent in ("none","n/a","unknown"): data["intent"] = "UNMAPPED" else: if raw_intent not in [k.lower() for k in INTENTS.keys()] and raw_intent != "unmapped": data["intent"] = "UNMAPPED" else: for k in INTENTS.keys(): if k.lower() == raw_intent: data["intent"] = k break try: c = float(data.get("confidence", 0)) data["confidence"] = max(0.0, min(1.0, c)) except Exception: data["confidence"] = 0.0 data["reason"] = str(data.get("reason","")).strip() return data except Exception: return {"intent":"UNMAPPED","confidence":0.0,"reason":"parse_error"} def gpt_classify_sentiment(user_text: str) -> str: prompt = "Classify sentiment as Positive, Negative, or Neutral.\n\nMessage: " + user_text resp = client.chat.completions.create( model="gpt-3.5-turbo", messages=[ {"role":"system","content":"You are a sentiment classifier."}, {"role":"user","content": prompt} ], temperature=0 ) return resp.choices[0].message.content.strip() def gpt_extract_params(intent_key: str, user_text: str) -> dict: if intent_key not in INTENTS: return {} cfg = INTENTS[intent_key] param_list = cfg["required"] + cfg["optional"] prompt = ( "Extract the following parameters from the user's query and return STRICT JSON only.\n" f"Keys: {param_list}. If a parameter is missing set it to null. " "Dates may be natural language; return them as-is.\n\n" f"User query: {user_text}" ) resp = client.chat.completions.create( model="gpt-3.5-turbo", messages=[ {"role":"system","content":"You extract parameters and ONLY output valid JSON."}, {"role":"user","content": prompt} ], temperature=0 ) txt = resp.choices[0].message.content.strip() try: params = json.loads(txt) except Exception: params = {p: None for p in param_list} return params def gpt_fallback_response(user_text: str) -> str: options = list(INTENTS.keys()) system = ( "You are a helpful support agent for an e-commerce analytics chatbot. " "The app could not map the user's request to any known intent/SQL template. " "Be helpful: either answer conversationally, gather missing details with specific " "questions, or suggest the closest capability." ) user = ( "No matching intent was found for the following message.\n" f"Known intents are: {options}\n\n" f"User message: {user_text}\n\n" "Please provide a concise, helpful response. If appropriate, suggest the nearest " "matching capability from the list above or ask one or two targeted follow-up questions." ) resp = client.chat.completions.create( model="gpt-3.5-turbo", messages=[{"role":"system","content": system}, {"role":"user","content": user}], temperature=0.3 ) return resp.choices[0].message.content.strip() def gpt_label_unmapped(user_text: str) -> str: system = ( "You label short messages into one category: " "greeting | clarification | thanks | complaint | small_talk | farewell | other. " "Return ONLY the label, nothing else." ) user = f"Message: {user_text}" resp = client.chat.completions.create( model="gpt-3.5-turbo", messages=[{"role":"system","content": system}, {"role":"user","content": user}], temperature=0 ) label = resp.choices[0].message.content.strip().lower() allowed = {"greeting","clarification","thanks","complaint","small_talk","farewell","other"} return label if label in allowed else "other" # ========================= # Param processing # ========================= def process_params(intent_key: str, params: dict) -> dict: if params is None: params = {} cfg = INTENTS[intent_key] required = cfg.get("required", []) optional = cfg.get("optional", []) allowed_keys = set(required + optional) # Keep only allowed keys clean = {k: params.get(k) for k in allowed_keys} # Defaults for numeric limits if "k" in allowed_keys: v = clean.get("k") try: clean["k"] = int(str(v).strip()) if v not in (None, "", "null", "None") else 10 except Exception: clean["k"] = 10 if "limit" in allowed_keys: v = clean.get("limit") try: clean["limit"] = int(str(v).strip()) if v not in (None, "", "null", "None") else 50 except Exception: clean["limit"] = 50 # Common optional fields for k in ("start_date", "end_date", "category", "product_id", "city", "seller_id", "order_id"): if k in allowed_keys and k not in clean: clean[k] = None # Parse NL dates for k in ("start_date", "end_date"): if k in clean: v = clean.get(k) if v and str(v).strip().lower() not in ("none", "null", ""): parsed = dateparser.parse(str(v)) clean[k] = parsed.strftime("%Y-%m-%d") if parsed else None else: clean[k] = None return clean # ========================= # Run intent (SQLite) # ========================= def run_intent(intent_key: str, params: dict): cfg = INTENTS[intent_key] sql = cfg["sql"] required = cfg.get("required", []) optional = cfg.get("optional", []) allowed_keys = set(required + optional) bound = {k: params.get(k) for k in allowed_keys} if "k" in allowed_keys: try: bound["k"] = int(bound.get("k", 10)) except Exception: bound["k"] = 10 if "limit" in allowed_keys: try: bound["limit"] = int(bound.get("limit", 50)) except Exception: bound["limit"] = 50 for k in ("start_date","end_date"): if k in bound: v = bound[k] if v is not None and not isinstance(v, str): try: bound[k] = v.strftime("%Y-%m-%d") except Exception: bound[k] = None logging.info(f"SQL intent={intent_key} | params={ {k: (type(v).__name__, v) for k,v in bound.items()} }") with get_connection() as conn: cur = conn.cursor() cur.execute(sql, bound) rows = cur.fetchall() col_names = [d[0] for d in cur.description] return rows, col_names, sql # ========================= # Video export helpers (PIL-based; no ImageMagick) # ========================= def _get_font(size: int) -> ImageFont.FreeTypeFont: # DejaVu Sans is installed via fonts-dejavu-core in the Dockerfile for p in [ "/usr/share/fonts/truetype/dejavu/DejaVuSans.ttf", "/usr/share/fonts/truetype/dejavu/DejaVuSansCondensed.ttf", ]: if os.path.exists(p): return ImageFont.truetype(p, size=size) # Fallback to default PIL bitmap font (limited coverage) return ImageFont.load_default() def _draw_multiline(draw: ImageDraw.ImageDraw, text: str, xy, font, fill, max_width_px: int, leading: int = 6): """Word-wrap `text` so each line fits <= max_width_px, then draw it. Returns bottom y.""" text = (text or "").strip() if not text: text = "." words = text.split() lines, cur = [], [] for w in words: test = " ".join(cur + [w]).strip() bbox = draw.textbbox((0, 0), test, font=font) w_px = bbox[2] - bbox[0] if w_px <= max_width_px: cur.append(w) else: lines.append(" ".join(cur).strip()) cur = [w] if cur: lines.append(" ".join(cur).strip()) x, y = xy for line in lines: draw.text((x, y), line, font=font, fill=fill) bbox = draw.textbbox((x, y), line, font=font) h = bbox[3] - bbox[1] y += h + leading return y def build_slide( title: str, body: str, *, w: int = 1280, h: int = 720, bg_color=(245, 247, 250), title_color: str = "black", body_color: str = "black", seconds: float = 3.5, ): # Create background img = Image.new("RGB", (w, h), color=bg_color) draw = ImageDraw.Draw(img) # Fonts title_font = _get_font(48) body_font = _get_font(36) # Layout left = 60 right = w - 60 max_text_w = right - left title_y = 80 # Title title_y_bottom = _draw_multiline( draw, title, (left, title_y), font=title_font, fill=title_color, max_width_px=max_text_w, leading=8 ) # Body body_y = max(title_y + 80, title_y_bottom + 20) _draw_multiline( draw, body, (left, body_y), font=body_font, fill=body_color, max_width_px=max_text_w, leading=6 ) # Convert to clip frame = np.array(img) clip = ImageClip(frame).set_duration(seconds) return clip # ========================= # TTS helpers # ========================= def tts_synthesize(text: str, lang: str, out_mp3: str) -> str: """ Synthesize `text` to an MP3 file at `out_mp3` using gTTS. Returns the path to the written MP3. Raises on hard failure. """ safe_text = (text or "").strip() or "." tts = gTTS(text=safe_text, lang=lang, slow=False) tts.save(out_mp3) return out_mp3 def narration_text_for_slide(title: str, body: str, max_chars: int = 600) -> str: """Build a concise narration string for TTS.""" text = f"{title}. {body}".strip() if len(text) > max_chars: text = text[: max_chars - 1].rsplit(" ", 1)[0] + "…" return text # ========================= # Build video from conversation (PIL slides + optional TTS) # ========================= def make_video_from_conversation( history: list, *, seconds_per_slide: float = 3.5, width: int = 1280, height: int = 720, out_path: str = "chat_video.mp4", narrate: bool = False, tts_lang: str = "en", ) -> str: """Build an MP4 from the current session history. If narrate=True, add TTS per slide.""" slides = [] tmp_dir = APP_DIR / ".cache" / "tts_tmp" audio_clips = [] if narrate: os.makedirs(tmp_dir, exist_ok=True) def _make_slide(title, body, bg_color=None): duration = seconds_per_slide audio_clip = None if narrate: try: mp3_path = str(tmp_dir / f"tts_{len(audio_clips):04d}.mp3") tts_text = narration_text_for_slide(title, body) tts_synthesize(tts_text, tts_lang, mp3_path) audio_clip = AudioFileClip(mp3_path) duration = max(seconds_per_slide, float(audio_clip.duration) + 0.4) except Exception: audio_clip = None duration = seconds_per_slide clip = build_slide( title, body, seconds=duration, w=width, h=height, bg_color=(245, 247, 250) if bg_color is None else bg_color ) if narrate and audio_clip is not None: clip = clip.set_audio(audio_clip) audio_clips.append(audio_clip) return clip # Intro slides.append(_make_slide("Olist Chatbot — Conversation", "Auto-generated summary video")) # Each turn: customer then agent for turn in history: ts = turn.get("timestamp", "") user_text = turn.get("user", "") reply = turn.get("reply", "") intent = turn.get("intent", "") or "UNMAPPED" sentiment = turn.get("sentiment", "") or "-" slides.append(_make_slide(f"Customer • {ts}", user_text, bg_color=(255, 255, 255))) slides.append(_make_slide(f"Support Agent • intent: {intent} | sentiment: {sentiment}", reply, bg_color=(242, 255, 242))) # Outro slides.append(_make_slide("Thanks for watching", "Exported from the Olist Streamlit app")) video = concatenate_videoclips(slides, method="compose") video.write_videofile( out_path, fps=24, codec="libx264", audio=narrate, audio_codec="aac" if narrate else None, threads=2, preset="medium", ffmpeg_params=["-movflags", "+faststart"], ) # Best-effort cleanup try: if narrate and tmp_dir.exists(): for f in tmp_dir.glob("tts_*.mp3"): try: f.unlink() except Exception: pass except Exception: pass return out_path # ========================= # App UI # ========================= init_chat_history_table() # Tabs (4th is Video Export) tab1, tab2, tab3, tab4 = st.tabs(["💬 Chat", "📊 MIS", "📥 Export", "🎬 Video Export"]) # ---------- TAB 1: Chat ---------- with tab1: st.title("📦 Olist E-commerce Chatbot") # session-state setup if "history" not in st.session_state: st.session_state.history = [] if "clear_input" not in st.session_state: st.session_state.clear_input = False if "user_input" not in st.session_state: st.session_state.user_input = "" # auto-clear (must occur BEFORE widget instantiation) if st.session_state.clear_input: st.session_state.user_input = "" st.session_state.clear_input = False user_input = st.text_input("You:", key="user_input") show_hist = st.checkbox("Show conversation history (current session)") # --- Submit handler --- if st.button("Submit"): if st.session_state.user_input.strip(): start_time = time.time() current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S") user_text = st.session_state.user_input # 1) Intent (with UNMAPPED) & Sentiment cls = gpt_classify_intent(user_text) # dict intent = cls.get("intent", "UNMAPPED") confidence = cls.get("confidence", 0.0) reason = cls.get("reason", "") logger.info(f"Intent -> '{intent}', conf={confidence}, reason='{reason}' | text='{user_text}'") sentiment = gpt_classify_sentiment(user_text) # Keyword validation: if no overlap, force UNMAPPED if intent != "UNMAPPED" and not _has_overlap(intent, user_text): logger.info(f"Keyword validation failed for intent='{intent}' → forcing UNMAPPED") intent = "UNMAPPED" sql_query = "" table_cols, table_rows = None, None params = {} if intent == "UNMAPPED": meta = gpt_label_unmapped(user_text) intent = f"{meta}" # store meta label bot_reply = gpt_fallback_response(user_text) resolution = "Responded" else: # 2) Extract + process params params = gpt_extract_params(intent, user_text) params = process_params(intent, params) # 3) Required params check required = INTENTS[intent]["required"] missing = [p for p in required if not params.get(p)] if missing: bot_reply = f"Could you please provide the following information: {', '.join(missing)}" resolution = "Unresolved" else: rows, col_names, sql_query = run_intent(intent, params) if rows: table_cols, table_rows = col_names, rows bot_reply = "Please see the table above for results." resolution = "Resolved" else: bot_reply = "No data found." resolution = "Unresolved" ftr_ms = round((time.time() - start_time) * 1000, 2) # 4) Session history + DB persistence entry = { "timestamp": current_time, "user": user_text, "intent": intent, "sentiment": sentiment, "resolution": resolution, "reply": bot_reply, "ftr_ms": ftr_ms, "table_cols": table_cols, # session-only "table_rows": table_rows, # session-only } st.session_state.history.append(entry) if len(st.session_state.history) > 50: st.session_state.history.pop(0) try: save_chat_to_db( user_message=user_text, intent=intent, sentiment=sentiment, resolution=resolution, bot_reply=bot_reply, ftr_ms=ftr_ms, sql_query=sql_query, sql_params=params if intent != "UNMAPPED" else {} ) except Exception: logger.exception("Failed to save chat history to DB") # request input clear on next render and rerun st.session_state.clear_input = True try: st.rerun() except Exception: st.experimental_rerun() # renderer for one turn def render_turn(turn): ts = turn["timestamp"] user_text = turn["user"] intent_display = turn["intent"] if turn["intent"] else "UNMAPPED" sentiment = turn["sentiment"] resolution = turn["resolution"] bot_reply = turn["reply"] table_cols = turn.get("table_cols") table_rows = turn.get("table_rows") st.markdown( f"""
{ts}
Customer: {user_text}
""", unsafe_allow_html=True ) st.markdown( f"""
Platform Analysis:
Intent Analysis: {intent_display}
Sentiment Analysis: {sentiment}
Resolution Status: {resolution}
""", unsafe_allow_html=True ) if table_cols and table_rows: df_result = pd.DataFrame(table_rows, columns=table_cols) st.table(df_result) st.markdown( f"""
{ts}
Support Agent: {bot_reply}
""", unsafe_allow_html=True ) if st.session_state.history: st.subheader("Conversation") if show_hist: for turn in st.session_state.history[-50:]: render_turn(turn) else: render_turn(st.session_state.history[-1]) # ---------- TAB 2: MIS ---------- with tab2: st.title("📊 Advanced MIS Dashboard") with get_connection() as conn: df_all = pd.read_sql_query("SELECT * FROM chat_history", conn) if df_all.empty: st.info("No chats logged yet.") else: df_all["timestamp"] = pd.to_datetime(df_all["timestamp"], errors="coerce") df_all["date"] = df_all["timestamp"].dt.date min_d = df_all["date"].min() max_d = df_all["date"].max() c1, c2 = st.columns([2, 3]) with c1: date_range = st.date_input("Date range", value=[min_d, max_d], min_value=min_d, max_value=max_d) with c2: all_intents = sorted([x for x in df_all["intent"].dropna().unique().tolist()]) intent_filter = st.multiselect("Filter by intent(s)", options=all_intents, default=all_intents) if isinstance(date_range, (list, tuple)) and len(date_range) == 2: start_d, end_d = date_range else: start_d, end_d = min_d, max_d mask = (df_all["date"] >= start_d) & (df_all["date"] <= end_d) if intent_filter: mask &= df_all["intent"].isin(intent_filter) df = df_all.loc[mask].copy() if df.empty: st.warning("No data for the selected filters.") st.stop() total_chats = len(df) resolved_pct = (df["resolution"] == "Resolved").mean() if total_chats else 0.0 avg_ftr = df["ftr_ms"].mean() if total_chats else 0.0 p95_ftr = df["ftr_ms"].quantile(0.95) if total_chats else 0.0 sla_ms = int(os.getenv("SLA_MS", "1500")) sla_pct = (df["ftr_ms"] <= sla_ms).mean() if total_chats else 0.0 k1,k2,k3,k4,k5 = st.columns(5) k1.metric("Total chats", f"{total_chats}") k2.metric("Resolved %", f"{resolved_pct*100:.1f}%") k3.metric("Avg FTR (ms)", f"{avg_ftr:.0f}") k4.metric("P95 FTR (ms)", f"{p95_ftr:.0f}") k5.metric(f"SLA ≤ {sla_ms} ms", f"{sla_pct*100:.1f}%") st.markdown("---") perf = ( df.groupby("intent") .agg( total=("intent","size"), resolved_pct=("resolution", lambda s: (s == "Resolved").mean()), avg_ftr_ms=("ftr_ms","mean") ) .sort_values("total", ascending=False) ) perf["resolved_pct"] = (perf["resolved_pct"]*100).round(1) perf["avg_ftr_ms"] = perf["avg_ftr_ms"].round(0) st.subheader("Intent performance") st.dataframe(perf.reset_index()) st.markdown("---") st.subheader("Daily chat volume") daily = df.groupby("date").size().reset_index(name="count") fig_vol, ax_vol = plt.subplots() ax_vol.plot(daily["date"], daily["count"]) ax_vol.set_xlabel("Date") ax_vol.set_ylabel("Chats") ax_vol.set_title("Chats per day") st.pyplot(fig_vol) st.subheader("Sentiment trend (daily)") sent_daily = ( df.pivot_table(index="date", columns="sentiment", values="id", aggfunc="count") .fillna(0) .sort_index() ) fig_sent, ax_sent = plt.subplots() for col in sent_daily.columns: ax_sent.plot(sent_daily.index, sent_daily[col], label=str(col)) ax_sent.set_xlabel("Date") ax_sent.set_ylabel("Count") ax_sent.set_title("Daily sentiment counts") ax_sent.legend() st.pyplot(fig_sent) st.subheader("Top intents") top_intents = df["intent"].value_counts().head(10) fig_int, ax_int = plt.subplots() ax_int.bar(top_intents.index.astype(str), top_intents.values) ax_int.set_ylabel("Count") ax_int.set_title("Top 10 intents") plt.setp(ax_int.get_xticklabels(), rotation=30, ha="right") st.pyplot(fig_int) st.subheader("FTR distribution") fig_ftr, ax_ftr = plt.subplots() ax_ftr.hist(df["ftr_ms"].dropna(), bins=20) ax_ftr.set_xlabel("FTR (ms)") ax_ftr.set_ylabel("Frequency") ax_ftr.set_title("FTR histogram") st.pyplot(fig_ftr) st.markdown("---") st.subheader("Recent interactions") cols = ["timestamp","user_message","intent","sentiment","resolution","ftr_ms","sql_query","sql_params","bot_reply"] show_cols = [c for c in cols if c in df.columns] st.dataframe(df.sort_values("timestamp", ascending=False).head(50)[show_cols]) # ---------- TAB 3: Export ---------- with tab3: st.title("📥 Export Chat History") scope = st.radio("Select scope to export:", ["Session only", "Full history"]) if scope == "Session only": export_df = pd.DataFrame(st.session_state.get("history", [])) if not export_df.empty: export_df = export_df.rename(columns={"user":"user_message","reply":"bot_reply"}) for col in ["timestamp","user_message","intent","sentiment","resolution","bot_reply","ftr_ms","sql_query","sql_params"]: if col not in export_df.columns: export_df[col] = None export_df = export_df[["timestamp","user_message","intent","sentiment","resolution","bot_reply","ftr_ms","sql_query","sql_params"]] else: with get_connection() as conn: export_df = pd.read_sql_query("SELECT * FROM chat_history", conn) if export_df is not None and not export_df.empty: csv_bytes = export_df.to_csv(index=False).encode("utf-8") st.download_button( "Download as CSV", data=csv_bytes, file_name=f"chat_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv", mime="text/csv" ) txt_lines = [] for _, row in export_df.iterrows(): ts = row.get("timestamp", "") user_msg = row.get("user_message", "") intent = row.get("intent", "") sentiment = row.get("sentiment", "") res = row.get("resolution", "") bot = row.get("bot_reply", "") txt_lines.append(f"[{ts}] Customer: {user_msg}") txt_lines.append(f"Intent: {intent} | Sentiment: {sentiment} | Resolution: {res}") txt_lines.append(f"Support Agent: {bot}") txt_lines.append("---") txt_bytes = "\n".join(txt_lines).encode("utf-8") st.download_button( "Download as TXT", data=txt_bytes, file_name=f"chat_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.txt", mime="text/plain" ) else: st.info("No history to export.") # ---------- TAB 4: Video Export ---------- with tab4: st.title("🎬 Video Export") st.write("Create a short MP4 summary of the conversation in this session.") history = st.session_state.get("history", []) if not history: st.info("No conversation yet. Start chatting in the first tab.") else: c1, c2 = st.columns([1, 1]) with c1: seconds_per_slide = st.slider("Seconds per slide", 2.0, 8.0, 3.5, 0.5) narrate = st.checkbox("Add voiceover narration (gTTS)", value=True) with c2: limit_turns = st.number_input( "Max turns to include (0 = all)", min_value=0, max_value=200, value=0, step=1 ) tts_lang = st.selectbox("Narration language", ["en", "hi", "pt", "es", "fr"], index=0) history_to_render = history if limit_turns in (0, None) else history[-int(limit_turns):] if st.button("Make Video"): try: out_path = make_video_from_conversation( history_to_render, seconds_per_slide=seconds_per_slide, width=1280, height=720, out_path="chat_video.mp4", narrate=narrate, tts_lang=tts_lang, ) with open(out_path, "rb") as f: data = f.read() st.success("Video ready!") st.video(data) st.download_button( "Download MP4", data=data, file_name=f"chat_video_{datetime.now().strftime('%Y%m%d_%H%M%S')}.mp4", mime="video/mp4", ) except Exception as e: st.error(f"Video generation failed: {e}")