FoodHub-Chatbot / src /streamlit_app.py
Suunil-Dabral's picture
Update src/streamlit_app.py
0fc599d verified
import os
import sqlite3
import pandas as pd
import json
import uuid
from datetime import datetime
import re
import streamlit as st
from groq import Groq
# ================== PAGE CONFIG (set before any UI) ==================
st.set_page_config(
page_title="FoodHub Support",
page_icon="πŸ•",
layout="centered",
)
# Narrow / mobile-style layout
st.markdown(
"""
<style>
.main {
max-width: 480px;
margin: 0 auto;
}
.foodhub-card {
background: linear-gradient(135deg, #fff3e0, #ffe0cc);
padding: 16px 20px;
border-radius: 18px;
border: 1px solid #f5c28c;
margin-bottom: 16px;
}
.foodhub-header-title {
margin-bottom: 4px;
color: #e65c2b;
font-size: 26px;
font-weight: 800;
}
.foodhub-header-subtitle {
margin: 0;
color: #444;
font-size: 14px;
}
.foodhub-assistant-pill {
display: inline-flex;
align-items: center;
gap: 6px;
background: #ffffff;
border-radius: 999px;
padding: 6px 10px;
font-size: 11px;
color: #555;
border: 1px solid #ffd3a3;
margin-top: 8px;
}
.status-dot {
width: 8px;
height: 8px;
border-radius: 50%;
background: #2ecc71;
}
.quick-actions-title {
font-size: 13px;
font-weight: 600;
color: #555;
margin-top: 4px;
margin-bottom: 4px;
}
.bot-bubble {
background: #eef7f2;
border-radius: 12px;
padding: 10px 12px;
font-size: 14px;
margin-top: 12px;
}
.user-label {
font-size: 12px;
color: #777;
margin-top: 8px;
margin-bottom: 2px;
}
.bot-label {
font-size: 12px;
color: #777;
margin-top: 12px;
margin-bottom: 2px;
}
</style>
""",
unsafe_allow_html=True,
)
# ================== DB CONFIG ==================
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
CLEAN_DB_PATH = os.path.join(BASE_DIR, "orders_clean.db") # cleaned DB
# Optional startup sanity check – helps catch wrong DB uploads early
try:
with sqlite3.connect(CLEAN_DB_PATH) as _conn:
_cur = _conn.cursor()
_cur.execute("SELECT name FROM sqlite_master WHERE type='table';")
_tables = [r[0] for r in _cur.fetchall()]
if "orders_clean" not in _tables:
st.error(
f"Connected to {CLEAN_DB_PATH!r}, but table 'orders_clean' was not found.\n\n"
"Please ensure df_clean was saved to this DB with table name 'orders_clean' "
"and that the correct orders_clean.db file is present next to app.py."
)
st.stop()
except Exception as e:
st.error(f"Could not open database at {CLEAN_DB_PATH!r}: {e}")
st.stop()
# ================== AUTH ==================
groq_api_key = os.environ.get("GROQ_API_KEY")
if not groq_api_key:
st.error("Missing GROQ_API_KEY β€” Add it inside Hugging Face Space β†’ Settings β†’ Secrets")
st.stop()
client = Groq(api_key=groq_api_key)
# ================== SCHEMA (for LLM prompt only) ==================
ORDERS_SCHEMA = """
Table: orders_clean
Columns:
-------------------------------------------
- order_id (TEXT) : unique order identifier, e.g. 'O12488'
- cust_id (TEXT) : customer identifier, e.g. 'C1013'
- order_time (TEXT) : time when order was placed
- order_status (TEXT) : e.g. 'preparing food', 'picked up', 'delivered', 'canceled', 'unknown'
- payment_status (TEXT) : e.g. 'cod', 'completed', 'canceled'
- item_in_order (TEXT) : comma-separated items, e.g. 'Burger, Fries'
- preparing_eta (TEXT) : estimated time when food will be ready
- prepared_time (TEXT) : actual time when food was prepared
- delivery_eta (TEXT) : estimated delivery time
- delivery_time (TEXT) : actual delivery time
"""
# =====================================================================
# SQL SAFETY FIREWALL
# =====================================================================
def is_safe_sql(sql: str) -> bool:
"""
Simple SQL firewall:
- only SELECT allowed
- no stacked queries
- block destructive / DDL keywords
- block SQL comment injections
"""
if not isinstance(sql, str):
return False
sql_lower = sql.lower().strip()
# Must start with SELECT (read-only)
if not sql_lower.startswith("select"):
return False
# Block stacked queries like "SELECT ...; DROP TABLE ..."
if re.search(r";\s*\S", sql_lower):
return False
forbidden_keywords = [
"drop", "delete", "update", "insert",
"alter", "truncate", "create",
]
if any(kw in sql_lower for kw in forbidden_keywords):
return False
forbidden_comment_tokens = ["--", "/*", "*/"]
if any(tok in sql_lower for tok in forbidden_comment_tokens):
return False
return True
# =====================================================================
# run_sql_query()
# =====================================================================
def run_sql_query(sql: str) -> pd.DataFrame:
"""
Safely execute SQL queries on the cleaned database
and automatically convert timestamp fields into proper datetime types
for accurate chatbot calculations (ETA, SLA, delays, durations, etc.).
"""
# Step 1: Basic normalization
if not isinstance(sql, str):
return pd.DataFrame([{"message": "🚫 Invalid SQL type (expected string)."}])
sql = sql.strip()
# Step 2: Validate SQL using custom firewall logic
if not is_safe_sql(sql):
return pd.DataFrame([{"message": "🚫 Blocked unsafe or unsupported SQL."}])
try:
# Step 3: Execute SQL using a fresh DB connection
with sqlite3.connect(CLEAN_DB_PATH) as connection:
df = pd.read_sql_query(sql, connection)
# Step 4: Convert all string timestamp columns back to datetime
time_cols = ["order_time", "preparing_eta", "prepared_time", "delivery_eta", "delivery_time"]
for col in time_cols:
if col in df.columns:
df[col] = pd.to_datetime(df[col], errors="coerce")
return df
except Exception as e:
# Step 5: Fail gracefully with diagnostic message
return pd.DataFrame([{"message": f"⚠️ SQL execution error: {str(e)}"}])
# =====================================================================
# llm_to_sql()
# =====================================================================
def llm_to_sql(user_message: str) -> str:
"""
Convert a natural language user query into a safe, valid SQLite SELECT query
targeting the orders_clean table inside orders_clean.db.
"""
# Normalize user text for pattern matching
text = (user_message or "").lower().strip()
# FAST-PATH: If an order_id like O12488 exists β†’ skip LLM
match = re.search(r"\b(o\d+)\b", text)
if match:
order_id = match.group(1) # already lowercase
sql = (
"SELECT * FROM orders_clean "
f"WHERE LOWER(order_id) = LOWER('{order_id}')"
)
return sql if is_safe_sql(sql) else "SELECT 'Unable to answer safely.' AS message;"
# LLM PATH β€” General business questions
system_prompt = f"""
You are an expert SQLite assistant for a food delivery company.
You ONLY generate valid SQLite SELECT queries using this schema:
{ORDERS_SCHEMA}
RULES:
- Output ONLY the SQL query (no comments, no markdown, no explanation).
- Allowed table name: orders_clean
- Allowed operation: SELECT only
- If filtering by order_id, ALWAYS use:
LOWER(order_id) = LOWER('<value>')
- If unsure how to answer from available data:
SELECT 'Unable to answer with available data.' AS message;
"""
response = client.chat.completions.create(
model="llama-3.3-70b-versatile",
temperature=0.1,
messages=[
{"role": "system", "content": system_prompt},
{"role": "user", "content": user_message},
],
)
sql = (response.choices[0].message.content or "").strip()
# Clean fences if LLM returned ```sql ...```
if sql.startswith("```"):
sql = re.sub(r"^```sql", "", sql, flags=re.IGNORECASE).strip()
sql = re.sub(r"^```", "", sql).strip()
sql = sql.replace("```", "").strip()
# Force table name β†’ orders_clean
sql = re.sub(
r"\bfrom\s+\w+\b",
"FROM orders_clean",
sql,
flags=re.IGNORECASE,
)
# Normalize order_id equality to LOWER(...) form
sql = re.sub(
r"where\s+order_id\s*=\s*'([^']+)'",
r"WHERE LOWER(order_id) = LOWER('\1')",
sql,
flags=re.IGNORECASE,
)
# Final safety gate
if not is_safe_sql(sql):
return "SELECT 'Unable to answer safely.' AS message;"
return sql
# =====================================================================
# analyze_sentiment_and_escalation()
# =====================================================================
def analyze_sentiment_and_escalation(user_message: str) -> dict:
# Basic normalization
if user_message is None:
user_message = ""
user_message = str(user_message).strip()
system_prompt = """
You are a classifier for a food delivery chatbot.
Given a single customer message, you must return a JSON object ONLY, with no extra text:
{
"sentiment": "calm" | "neutral" | "frustrated" | "angry",
"escalate": true or false
}
Rules:
- Use "calm" when the user is relaxed or appreciative.
- Use "neutral" when they are just asking for information.
- Use "frustrated" when they express dissatisfaction, confusion, or mild complaints.
- Use "angry" when they use strong negative language, threats, or clear anger.
Escalate = true when:
- the user sounds upset, frustrated, or angry,
- they mention repeated attempts with no resolution,
- they demand urgent or immediate help.
If you are uncertain, default to:
{
"sentiment": "neutral",
"escalate": false
}
"""
response = client.chat.completions.create(
model="llama-3.3-70b-versatile",
messages=[
{"role": "system", "content": system_prompt},
{"role": "user", "content": user_message},
],
temperature=0.0,
)
raw = (response.choices[0].message.content or "").strip()
try:
info = json.loads(raw)
if not isinstance(info, dict):
raise ValueError("Not a JSON object")
if "sentiment" not in info or "escalate" not in info:
raise ValueError("Missing required keys")
except (json.JSONDecodeError, ValueError):
info = {"sentiment": "neutral", "escalate": False}
return info
# =====================================================================
# is_misuse_or_hacker_attempt()
# =====================================================================
def is_misuse_or_hacker_attempt(user_message: str) -> bool:
"""
Detect attempts to:
- Hack or bypass security
- Retrieve unauthorized bulk data (ALL orders / ALL customers)
- Perform SQL injection or prompt injection
"""
text = (user_message or "").lower().strip()
# Direct harmful / hacking intent
hacker_keywords = [
"hack",
"hacker",
"hacking",
"hacked",
"i am the hacker",
"i am hacker",
"breach",
"exploit",
"bypass security",
"disable firewall",
"root access",
"admin access",
"reverse engineer",
]
# Attempts to extract private or BULK data
bulk_keywords = [
"all orders",
"every order",
"all my orders",
"all previous orders",
"all past orders",
"order history",
"full database",
"entire database",
"all customers",
"customer list",
"download database",
"dump data",
"export all",
"export everything",
"show everything",
]
# Jailbreak / prompt injection indicators
jailbreak_keywords = [
"ignore previous instructions",
"override rules",
"forget rules",
"developer mode",
"you are no longer restricted",
"pretend you are",
"act like",
]
# SQL injection indicators
sql_injection_keywords = [
"1=1",
"union select",
"drop table",
"delete from",
"insert into",
"alter table",
"truncate",
]
# Only flag semicolons if other SQL keywords appear (stacked SQL)
if ";" in text and any(sig in text for sig in sql_injection_keywords):
return True
all_signals = (
hacker_keywords
+ bulk_keywords
+ jailbreak_keywords
+ sql_injection_keywords
)
return any(sig in text for sig in all_signals)
# =====================================================================
# create_ticket()
# =====================================================================
def create_ticket(
user_message: str,
sentiment: str,
ticket_type: str = "general",
order_id: str = None
) -> str:
# Extract order_id if not given (pattern: O12345)
if order_id is None:
match = re.search(r"\b(o\d+)\b", (user_message or "").lower())
order_id = match.group(1).upper() if match else None
ticket_id = "TKT-" + uuid.uuid4().hex[:8].upper()
created_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
with sqlite3.connect(CLEAN_DB_PATH) as conn:
cursor = conn.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS tickets (
ticket_id TEXT PRIMARY KEY,
ticket_type TEXT,
sentiment TEXT,
message TEXT,
order_id TEXT,
status TEXT DEFAULT 'Open',
created_at TEXT
);
""")
cursor.execute("""
INSERT INTO tickets (
ticket_id, ticket_type, sentiment, message,
order_id, status, created_at
)
VALUES (?, ?, ?, ?, ?, 'Open', ?);
""", (ticket_id, ticket_type, sentiment, user_message, order_id, created_at))
conn.commit()
return ticket_id
# =====================================================================
# sql_result_to_response()
# =====================================================================
def sql_result_to_response(
user_message: str,
df: pd.DataFrame,
tone: str = "polite-professional",
include_emojis: bool = True,
language: str = "auto",
max_rows_to_list: int = 5
) -> str:
# 0. Handle error DataFrames (from run_sql_query) directly
if (
isinstance(df, pd.DataFrame)
and "message" in df.columns
and len(df) == 1
):
return str(df.iloc[0]["message"])
# 1. Handle NO MATCHING DATA explicitly
if df.empty:
return (
"I couldn’t find any orders matching the details you provided. "
"Please double-check your order ID or the information you entered, "
"and try again."
)
# 2. Prepare data (truncate)
df_for_llm = df.head(max_rows_to_list).copy()
result_data = df_for_llm.to_dict(orient="records")
# Emojis
if include_emojis:
intro_line = (
"Welcome to FoodHub Support! πŸ‘‹ I'm your virtual assistant β€” here to help you.\n"
)
emoji_instruction = (
"Include a few relevant, tasteful emojis (like βœ…, 🚚, ⏱️, πŸ•, ℹ️, ⚠️, πŸ’›) "
"to make it friendly, but do not overuse them.\n"
)
else:
intro_line = (
"Welcome to FoodHub Support! I’m your virtual assistant β€” here to help you.\n"
)
emoji_instruction = "Do NOT use any emojis in your response.\n"
# Tone
if tone == "friendly-casual":
tone_instruction = "Use a friendly, conversational tone while staying respectful.\n"
elif tone == "formal":
tone_instruction = "Use a formal, professional tone.\n"
else:
tone_instruction = "Use a polite, professional tone, warm but not overly casual.\n"
# Language
if language == "auto":
language_instruction = (
"Detect the user's language and respond in the SAME language. "
"If unsure, default to English.\n"
)
else:
language_instruction = f"Respond in this language: {language}.\n"
system_prompt = f"""
You are a polite and professional customer support chatbot for FoodHub.
Always start with a short greeting like:
{intro_line}
VERY IMPORTANT POLICY:
- If ANY matching record exists in the data I give you β†’ The order is CONFIRMED.
- NEVER tell the user to re-check their order ID if matching rows exist.
- NEVER say "order not found", "unable to locate order", or similar messages
when there is at least one matching record.
- Instead, confidently acknowledge the order and use available data.
When answering:
- Explain the latest known order status clearly.
- If 'delivery_time' is missing but 'delivery_eta' exists:
- Say the order is still in progress, and share ETA.
- If both ETA and delivery_time are missing:
- Apologize for limited tracking information.
- If delivered and 'delivery_time' exists:
- Confirm delivery and mention the exact time (in friendly wording).
- If SLA columns exist:
- If 'on_time_delivery' == 1 β†’ reassure user their order was/has been on time.
- If delayed β†’ apologize sincerely and reassure support.
- For multiple rows β†’ summarize count + list top {max_rows_to_list} orders.
Tone & style rules:
{tone_instruction}
{emoji_instruction}
{language_instruction}
- Keep responses concise and human-friendly.
- Use natural phrasing like "around 1:00 PM" instead of "13:00:00".
- End warmly, e.g.: "If you need anything else, I’m here to help! πŸ’›"
"""
content = (
f"User question: {user_message}\n\n"
f"Matching order data (up to {max_rows_to_list} rows):\n{result_data}"
)
response = client.chat.completions.create(
model="llama-3.3-70b-versatile",
temperature=0.4,
messages=[
{"role": "system", "content": system_prompt},
{"role": "user", "content": content},
],
)
return response.choices[0].message.content.strip()
# =====================================================================
# chatbot_response() – main orchestration
# =====================================================================
def chatbot_response(user_message: str) -> str:
# Safely normalize user input
text = (user_message or "").lower().strip()
# Pre-compute order ID presence once
order_id_match = re.search(r"\b[oO]\d{3,}\b", text)
has_order_id = order_id_match is not None
# 0) Small-talk / greetings handling (A+YES)
greetings = ["hi", "hello", "hey", "good morning", "good evening", "good afternoon"]
thanks_words = ["thank you", "thanks", "thx", "thankyou"]
if any(text == g or text.startswith(g + " ") for g in greetings):
return (
"πŸ‘‹ Hi there! Welcome to FoodHub Support.\n"
"You can ask me about your order status, delivery, cancellation, or payment/refund issues. πŸ’›"
)
if any(w in text for w in thanks_words):
return (
"You’re most welcome! πŸ’›\n"
"If you need any help with your order, just share your question or Order ID (like `O12488`)."
)
# 1) Block hacking / misuse attempts
if is_misuse_or_hacker_attempt(user_message):
return (
"πŸ˜” Sorry, I can’t assist with that.\n"
"For everyone’s safety, I can only help with your own order using a valid order ID.\n"
"Try asking something like: *Where is my order O12488?* 😊"
)
# 2) Escalation detection for repeated unresolved concerns
escalation_keywords = [
"raised the query multiple times",
"asked multiple times",
"no resolution",
"still not resolved",
"immediate response",
]
if any(k in text for k in escalation_keywords):
senti_info = analyze_sentiment_and_escalation(user_message)
sentiment = senti_info.get("sentiment", "angry")
ticket_id = create_ticket(
user_message,
sentiment,
ticket_type="escalation",
)
# UPDATED, MORE EMPATHETIC ESCALATION RESPONSE
return (
"πŸ’› I’m really sorry that you’ve had to follow up multiple times and still don’t have a clear update β€” "
"I completely understand how frustrating that feels.\n\n"
"I’ve now **escalated your case to a senior support agent** so it gets immediate attention.\n\n"
f"πŸ“Œ Your escalation ticket ID is **{ticket_id}**.\n\n"
"They’ll review your case and the order history on priority and get back to you as soon as possible.\n"
"If you haven’t already, please share your **Order ID** (for example: `O12488`) and any key details so we can resolve this even faster."
)
# 3) Order cancellation intent detection
if (
"cancel my order" in text
or "cancel order" in text
or "i want to cancel my order" in text
or ("cancel" in text and "order" in text)
):
match = order_id_match
if match:
order_id = match.group(0).upper()
senti_info = analyze_sentiment_and_escalation(user_message)
sentiment = senti_info.get("sentiment", "neutral")
ticket_id = create_ticket(
f"Cancellation request for {order_id}. Original message: {user_message}",
sentiment,
ticket_type="cancellation",
)
return (
f"πŸ™ I understand you would like to cancel **Order {order_id}**, and I'm really sorry "
f"if there was any inconvenience that led to this decision.\n\n"
f"πŸ“ Your cancellation request has been successfully recorded.\n"
f"πŸ”Ž A customer support specialist will now verify and process it as soon as possible.\n\n"
f"πŸ“Œ **Ticket Reference ID:** `{ticket_id}`\n\n"
f"Please let me know if you'd like to modify the order instead β€” I'm here to help 😊"
)
# No order ID found in a cancel intent β†’ empathetic ask
return (
"πŸ’› I’m sorry to hear you’d like to cancel your order β€” I completely understand and I’m here to help.\n"
"Could you please share your **Order ID** (for example: `O12488`) so I can check the details and help "
"process the cancellation for you?"
)
# 4) Payment / refund intent detection (needs order ID)
if ("payment" in text or "refund" in text) and not has_order_id:
return (
"πŸ’› I’m sorry you’re facing an issue with the payment or refund.\n"
"I’ll help you with this right away.\n\n"
"Could you please share your **Order ID** (for example: `O12488`) "
"so I can check the payment status and update you?"
)
# 5) Status / details intents that require Order ID
order_status_intents = [
"where is my order",
"track my order",
"track order",
"order status",
"status of my order",
"delivery status",
"when will my order arrive",
"when will it arrive",
"order not delivered",
"order is late",
"order delayed",
"delay in delivery",
"order details",
"details for my order",
"details of my order",
"show me the details for my order",
]
has_status_intent = any(phrase in text for phrase in order_status_intents)
if has_status_intent and not has_order_id:
return (
"I’d be happy to check that for you πŸ’›\n"
"Please share your **Order ID** (for example: `O12488`) so I can look up the exact status."
)
# 6) Normal path β†’ LLM to SQL β†’ DB β†’ empathetic answer
try:
sql = llm_to_sql(user_message)
df = run_sql_query(sql)
if df.empty:
return "I could not find any matching order. Please confirm your order ID and try again 😊"
answer = sql_result_to_response(user_message, df)
return answer
except Exception as e:
print("[chatbot_response] Error while processing query:", e)
return (
"Sorry, I ran into a small issue while processing this request. πŸ™\n"
"Please make sure you mention a valid order ID like `O12488`."
)
# Simple wrapper so the UI still calls `chatbot(...)`
def chatbot(msg: str) -> str:
return chatbot_response(msg)
# ================== SESSION STATE FOR WELCOME ==================
if "welcome_shown" not in st.session_state:
st.session_state["welcome_shown"] = False
# ================== UI (unchanged) ==================
# Header card (replaces logo / banner)
st.markdown(
"""
<div class="foodhub-card">
<div style="display:flex; align-items:flex-start; gap:12px;">
<div style="font-size:32px;">πŸ•</div>
<div style="flex:1%;">
<div class="foodhub-header-title">
FoodHub AI Support
</div>
<p class="foodhub-header-subtitle">
Ask about your order status, delivery updates, cancellations, or payment issues.
</p>
<div class="foodhub-assistant-pill">
<span class="status-dot"></span>
<span>FoodHub virtual assistant Β· Online</span>
</div>
</div>
</div>
</div>
""",
unsafe_allow_html=True,
)
# One-time welcome (not repeated per message)
if not st.session_state["welcome_shown"]:
st.success("Welcome to FoodHub Support! I’m your virtual assistant for order, delivery, and payment queries.")
st.caption("Because great food deserves great service πŸ’›")
st.session_state["welcome_shown"] = True
# Quick action suggestions (mobile friendly)
st.markdown('<div class="quick-actions-title">Quick help options</div>', unsafe_allow_html=True)
col1, col2 = st.columns(2)
if col1.button("πŸ“ Track my order", use_container_width=True):
st.session_state["chat_input"] = "Where is my order? I want to track it."
if col2.button("πŸ’³ Payment issue", use_container_width=True):
st.session_state["chat_input"] = "I have an issue with my payment / refund."
col3, col4 = st.columns(2)
if col3.button("❌ Cancel order", use_container_width=True):
st.session_state["chat_input"] = "I want to cancel my order."
if col4.button("🧾 Order details", use_container_width=True):
st.session_state["chat_input"] = "Show me the details for my order."
# Main chat input
query = st.text_input(
"πŸ’¬ Type your question here:",
key="chat_input",
placeholder="e.g. Where is my order O12488?",
)
send_clicked = st.button("Send", use_container_width=True)
if send_clicked:
if query.strip():
reply = chatbot(query.strip())
# Simple conversational layout (last turn)
st.markdown('<div class="user-label">You</div>', unsafe_allow_html=True)
st.markdown(f"<div class='bot-bubble' style='background:#fff;'>πŸ’¬ {query}</div>", unsafe_allow_html=True)
st.markdown('<div class="bot-label">FoodHub Assistant</div>', unsafe_allow_html=True)
st.markdown(
f"<div class='bot-bubble'>πŸ€– {reply}</div>",
unsafe_allow_html=True,
)
else:
st.warning("Please enter a message to continue.")