Spaces:
Runtime error
Runtime error
Download app.py from SuhasGholkar/caps: direct link, hf CLI and curl.
- Browser
- Download file 39.5 kB
-
https://huggingface.co/spaces/SuhasGholkar/caps/resolve/main/app.py
- Command line
-
hf download hf://spaces/SuhasGholkar/caps/app.py
-
curl -L -o app.py https://huggingface.co/spaces/SuhasGholkar/caps/resolve/main/app.py
39.5 kB
| import os | |
| # ================= LOGGING ================= | |
| # Make sure the logs dir exists if you want a file too | |
| os.makedirs("/tmp", exist_ok=True) | |
| import logging | |
| import sys | |
| # Configure logging outputs | |
| logging.basicConfig( | |
| level=logging.DEBUG, # Or logging.INFO | |
| format="%(asctime)s - %(levelname)s - %(message)s", | |
| handlers=[ | |
| logging.StreamHandler(stream=sys.stdout), # HF Runtime Logs | |
| logging.FileHandler("/tmp/app.log", mode="a", encoding="utf-8"), # In-app debug tab | |
| ], | |
| force=True | |
| ) | |
| # Force root and all handlers to DEBUG | |
| root = logging.getLogger() | |
| root.setLevel(logging.DEBUG) | |
| for h in root.handlers: | |
| h.setLevel(logging.DEBUG) | |
| logger = logging.getLogger(__name__) | |
| logger.debug("Root handler levels forced to DEBUG") | |
| # Smoke tests | |
| logger.info("π Application starting up") | |
| print("STDOUT smoke test: hello from app.py") | |
| logger.debug("Logger smoke test: hello from app.py") | |
| import streamlit as st | |
| import matplotlib.pyplot as plt | |
| import pathlib | |
| from pathlib import Path | |
| import re | |
| import json | |
| import time | |
| from datetime import datetime | |
| import dateparser | |
| import pandas as pd | |
| from openai import OpenAI | |
| # --- Make config/cache dirs writable on Hugging Face Spaces --- | |
| APP_DIR = pathlib.Path(__file__).parent.resolve() | |
| 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 | |
| # pick a non-interactive backend for matplotlib | |
| import matplotlib | |
| matplotlib.use("Agg") | |
| # ================= CONFIG ================= | |
| DB_PATH = "/tmp/olist.sqlite" | |
| if not os.path.exists(DB_PATH): | |
| st.error(f"Error: Database file not found at {DB_PATH}") | |
| st.stop() | |
| MAX_HISTORY = int(os.getenv("MAX_HISTORY","10")) | |
| # ================= OPENAI CLIENT (v1) ================= | |
| logger.info("===== API_KEY CONFIG =====") | |
| API_KEY = os.getenv("OPENAI_API_KEY") | |
| client = OpenAI(api_key=API_KEY) if API_KEY else None | |
| logger.info("===== POST CLIENT CONFIG =====") | |
| # ================= SQLITE HELPERS ================= | |
| logger.info("===== BEFORE get connection =====") | |
| import streamlit as st | |
| import sqlite3 | |
| def get_connection(): | |
| try: | |
| conn = sqlite3.connect(f"file:{DB_PATH}", uri=True) | |
| return conn | |
| except sqlite3.OperationalError as e: | |
| st.error(f"Error connecting to database: {e}") | |
| st.stop() | |
| logger.info("===== BEFORE init_chat_history_table =====") | |
| def init_chat_history_table(): | |
| try: | |
| 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() | |
| except sqlite3.OperationalError as e: | |
| st.error(f"Error creating chat history table: {e}") | |
| st.stop() | |
| 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) | |
| )) | |
| conn.commit() | |
| # ================= FULL INTENTS (Olist tables) ================= | |
| 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 to validate mapping (helps reject garbage) | |
| 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 (v1 client) ================= | |
| def gpt_classify_intent(user_text: str) -> dict: | |
| """ | |
| Returns: {"intent": <key|UNMAPPED>, "confidence": float, "reason": str} | |
| """ | |
| 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}" | |
| ) | |
| logger.info("Sending request to OpenAI API - gpt_classify_intent") | |
| 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 | |
| logger.info("Sending request to OpenAI API - gpt_classify_sentiment") | |
| 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}" | |
| ) | |
| logger.info("Sending request to OpenAI API - gpt_extract_params") | |
| 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: | |
| """ | |
| Generate a helpful reply when no intent matches. | |
| """ | |
| 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." | |
| ) | |
| logger.info("Sending request to OpenAI API - gpt_fallback_response") | |
| 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: | |
| """ | |
| Classify unmapped messages into a small taxonomy. | |
| Return one of: greeting, clarification, thanks, complaint, small_talk, farewell, other | |
| """ | |
| 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}" | |
| logger.info("Sending request to OpenAI API - gpt_label_unmapped") | |
| 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 (strict & safe) ================= | |
| 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} | |
| # Ensure LIMIT ints | |
| 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 | |
| # Dates: ensure None or ISO string | |
| 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 | |
| # ================= STREAMLIT APP ================= | |
| st.set_page_config(page_title="Olist Chatbot", layout="wide") | |
| logger.info("going to init_chat_history_table") | |
| init_chat_history_table() | |
| tab1, tab2, tab3 = st.tabs(["π¬ Chat", "π MIS", "π₯ 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 --- | |
| logger.info("Inside Submit handler") | |
| if st.button("Submit"): | |
| if client is None: | |
| logger.error("OPENAI_API_KEY is not set. Aborting Submit handler.") | |
| st.error("OPENAI_API_KEY is not set (Spaces β Settings β Repository secrets).") | |
| st.stop() | |
| 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 | |
| if intent == "UNMAPPED": | |
| # Meta label for better UX | |
| meta = gpt_label_unmapped(user_text) | |
| intent = f"UNMAPPED ({meta})" # <-- store combined string, no DB migration needed | |
| params = {} | |
| 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: | |
| try: | |
| rows, col_names, sql_query = run_intent(intent, params) | |
| except Exception as e: | |
| logger.exception(f"Error running intent {intent}") | |
| st.error("Something went wrong. Please check the logs.") | |
| 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) > MAX_HISTORY: | |
| st.session_state.history.pop(0) | |
| # Persist (store params only when intent mapped) | |
| try: | |
| save_chat_to_db(user_text, intent, sentiment, resolution, bot_reply, ftr_ms, sql_query, | |
| params if intent in INTENTS 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") | |
| # Customer bubble | |
| st.markdown( | |
| f""" | |
| <div style='background-color:#e6f2ff;padding:10px;border-radius:10px;max-width:80%;margin-bottom:5px;'> | |
| <div style='font-size:0.8em;color:gray;'>{ts}</div> | |
| <b>Customer:</b> {user_text} | |
| </div> | |
| """, | |
| unsafe_allow_html=True | |
| ) | |
| # Platform Analysis | |
| st.markdown( | |
| f""" | |
| <div style='background-color:#ffffff;padding:10px;border-radius:10px;max-width:80%;margin-bottom:5px;'> | |
| <b>Platform Analysis:</b><br> | |
| <b>Intent Analysis:</b> {intent_display}<br> | |
| <b>Sentiment Analysis:</b> {sentiment}<br> | |
| <b>Resolution Status:</b> {resolution} | |
| </div> | |
| """, | |
| unsafe_allow_html=True | |
| ) | |
| # Table (if any) | |
| if table_cols and table_rows: | |
| df_result = pd.DataFrame(table_rows, columns=table_cols) | |
| st.table(df_result) | |
| # Agent bubble | |
| st.markdown( | |
| f""" | |
| <div style='background-color:#e6ffe6;padding:10px;border-radius:10px;max-width:80%;margin-bottom:20px;margin-left:auto;'> | |
| <div style='font-size:0.8em;color:gray;'>{ts}</div> | |
| <b>Support Agent:</b> {bot_reply} | |
| </div> | |
| """, | |
| unsafe_allow_html=True | |
| ) | |
| # Debug logs section (optional) | |
| with st.expander("π§ Debug log (last 200 lines)", expanded=False): | |
| try: | |
| with open("/tmp/app.log", "r", encoding="utf-8") as f: | |
| lines = f.readlines()[-200:] | |
| st.code("".join(lines), language="text") | |
| except Exception as e: | |
| st.write("No log file yet or error reading log:", e) | |
| # --- Always show the latest turn; optionally show full session history --- | |
| if st.session_state.history: | |
| st.subheader("Conversation") | |
| if show_hist: | |
| for turn in st.session_state.history[-MAX_HISTORY:]: | |
| 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: | |
| # Prep | |
| df_all["timestamp"] = pd.to_datetime(df_all["timestamp"], errors="coerce") | |
| df_all["date"] = df_all["timestamp"].dt.date | |
| # Filters | |
| 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) | |
| # Apply filters | |
| 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() | |
| # KPIs | |
| 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("---") | |
| # Intent performance table | |
| 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("---") | |
| # Volume over time | |
| 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) | |
| # Sentiment trend (daily) | |
| 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) | |
| # Top intents (bar) | |
| 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) | |
| # FTR distribution (hist) | |
| 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("---") | |
| # Recent interactions (last 50) | |
| 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.history) | |
| if not export_df.empty: | |
| export_df = export_df.rename(columns={"user":"user_message","reply":"bot_reply"}) | |
| # normalize columns to match DB export | |
| 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 | |
| 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 | |
| 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.") | |