Spaces:
Sleeping
Sleeping
| 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.") | |