# =========================
# 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}")