src/streamlit_app.py

import streamlit as st
import os
import re, ast
import sqlite3
import streamlit as st


#from langchain.chat_models import ChatOpenAI
from langchain_openai import ChatOpenAI
from langchain_community.utilities import SQLDatabase
from langchain_core.messages import HumanMessage

# ---- Session State Initialization ----
if "conversation_stage" not in st.session_state:
    st.session_state.conversation_stage = "IDLE"

if "pending_order_id" not in st.session_state:
    st.session_state.pending_order_id = None

if "pending_action" not in st.session_state:
    st.session_state.pending_action = None



DB_PATH = "customer_orders.db"

def initialize_database():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()

    cursor.execute("""
    CREATE TABLE IF NOT EXISTS orders (
        order_id INTEGER PRIMARY KEY,
        cust_id TEXT,
        order_time TEXT,
        order_status TEXT,
        payment_status TEXT,
        item_in_order TEXT,
        preparing_eta TEXT,
        prepared_time TEXT,
        delivery_eta TEXT,
        delivery_time TEXT
    )
    """)

    cursor.execute("SELECT COUNT(*) FROM orders")
    count = cursor.fetchone()[0]

    # Populate only once
    if count == 0:
        cursor.executemany("""
        INSERT INTO orders VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """, [
            (12486, "C1011", "12:00", "preparing food", "COD", "Burger, Fries", "12:15", None, None, None),
            (12487, "C1012", "12:05", "cancelled", "cancelled", "Pizza", None, None, None, None),
            (12488, "C1013", "12:10", "delivered", "completed", "Sandwich, Soda", "12:25", "12:25", "12:55", "13:00"),
            (12489, "C1014", "12:15", "picked up", "COD", "Salad", "12:30", "12:30", "12:45", None),
            (12490, "C1015", "12:20", "delivered", "completed", "Pasta", "12:35", "12:35", "13:05", "13:10"),
			(12491,"C1016","12:25","preparing food","COD","Burger","12:40",None,None,None),
(12492,"C1017","12:30","delivered","completed","Sushi, Salad","12:45","12:45","13:15","13:15"),
(12493,"C1018","12:35","picked up","COD","Steak","12:50","12:50","01:10",None),
(12494,"C1019","12:40","canceled","canceled","Pizza, Garlic Bread",None,None,None,None),
(12495,"C1020","12:45","preparing food","COD","Wrap, Juice","13:00",None,None,None),
(12496,"C1021","12:50","preparing food","COD","Taco, Nachos","13:05",None,None,None),
(12497,"C1022","12:55","delivered","completed","Burrito","13:10","13:10","13:15","13:15"),
(12498,"C1023","12:56","picked up","COD","Pancakes, Coffee","13:11","13:11","13:40",None),
(12499,"C1024","12:57","canceled","canceled","Waffle",None,None,None,None),
(12500,"C1025","12:58","delivered","completed","Omelette, Toast","13:13","13:13","13:15","13:15"),
(12501,"C1026","12:59","preparing food","COD","Burger, Fries, Soda","13:14",None,None,None),
(12502,"C1027","12:00","picked up","COD","Salad, Soup","13:15","13:15","13:45",None),
(12503,"C1028","12:40","delivered","completed","Steak, Pizza","13:15","13:15","13:15","13:15"),
(12504,"C1029","12:35","canceled","canceled","Hotdog",None,None,None,None),
(12505,"C1030","12:50","delivered","completed","Pasta, Garlic Bread","13:10","13:10","13:15","13:15")
        ])
        conn.commit()

    conn.close()

initialize_database()


# ---------------- PAGE CONFIG ----------------
st.set_page_config(
    page_title="FoodHub AI Customer Support",
    page_icon="🍔",
    layout="centered"
)

# ---------------- API KEY ----------------
#os.environ["OPENAI_API_KEY"] = "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"
OPENAI_API_KEY = os.getenv("OPENAI_API_KEY")

# ---------------- LOAD DB & LLM ----------------
#db = SQLDatabase.from_uri("sqlite:///./customer_orders.db")

DB_PATH = "customer_orders.db"
db = SQLDatabase.from_uri(f"sqlite:///{DB_PATH}")



llm = ChatOpenAI(
    model="gpt-4o-mini",
    temperature=0.2
)

# ---------------- handel Cancel ----------------
def detect_intent(text: str):
    text = text.lower()
    if "cancel" in text:
        return "CANCEL"
    if "add" in text or "update" in text or "extra" in text:
        return "ADD_ITEM"
    return "STATUS"




# ---------------- GUARDRAILS ----------------
def is_malicious_or_invalid(query: str) -> bool:
    blocked_keywords = [
        "hacker", "hack", "all orders",
        "database", "dump", "steal"
    ]
    return any(word in query.lower() for word in blocked_keywords)


import re

def extract_order_id(text: str):
    """
    Extract order ID from flexible user phrases.
    Supports:
    - order_id 1023
    - order id 1023
    - order 1023
    - my order is 1023
    """
    text = text.lower()

    patterns = [
        r"order[_\s]?id\s*(\d+)",
        r"order\s*(\d+)",
        r"order\s*is\s*(\d+)"
    ]

    for pattern in patterns:
        match = re.search(pattern, text)
        if match:
            return match.group(1)

    return None



# ---------------- TOOLS ----------------
def order_query_tool(order_id: str):
    query = f"SELECT * FROM orders WHERE order_id = {order_id}"
    result = db.run(query)

    if not result:
        return None

    if isinstance(result, str):
        result = ast.literal_eval(result)

    return result[0]


def answer_tool(order_data):
    prompt = f"""
    You are a FoodHub customer support assistant.
    Generate a polite and professional response.
    Order Data: {order_data}
    """
    return llm.invoke(prompt).content


# ---------------- CHAT AGENT ----------------
def chatagent(user_query: str):

    # 🚫 Guardrail
    if is_malicious_or_invalid(user_query):
        return "🚫 Unauthorized request. I can only help with your own order."

    # ---------------------------------
    # Handle follow-up confirmation
    # ---------------------------------
    if st.session_state.conversation_stage == "WAITING_FOR_CONFIRMATION":
        order_id = st.session_state.pending_order_id
        action = st.session_state.pending_action

        st.session_state.conversation_stage = "IDLE"
        st.session_state.pending_order_id = None
        st.session_state.pending_action = None

        return (
            f"✅ Thank you for confirming.\n\n"
            f"Your request regarding **Order #{order_id}** has been "
            f"forwarded to our **human support team**.\n\n"
            f"📞 A support executive will contact you shortly to assist "
            f"with item updates and payment."
        )

    # ---------------------------------
    # Normal flow
    # ---------------------------------
    order_id = extract_order_id(user_query)
    if not order_id:
        return "ℹ️ Please provide your order ID to proceed."

    intent = detect_intent(user_query)

    order_data = order_query_tool(order_id)
    if not order_data:
        return f"❌ No order found with ID {order_id}."

    order_status = order_data[3].lower()  # order_status column

    # ---------------------------------
    # ADD ITEM FLOW
    # ---------------------------------
    if intent == "ADD_ITEM":
        if "preparing" in order_status:
            st.session_state.conversation_stage = "WAITING_FOR_CONFIRMATION"
            st.session_state.pending_order_id = order_id
            st.session_state.pending_action = "ADD_ITEM"

            return (
                f"📝 Your order **#{order_id}** is currently being prepared.\n\n"
                f"➕ Adding items at this stage may require **additional payment**.\n\n"
                f"❓ Please confirm if you want to proceed. "
                f"Reply with **Yes** to connect with a human support agent."
            )
        else:
            return (
                f"⚠️ Item modification is not possible because "
                f"your order **#{order_id}** is already **{order_status}**."
            )

    # ---------------------------------
    # CANCEL FLOW (already implemented)
    # ---------------------------------
    if intent == "CANCEL":
        st.session_state.conversation_stage = "WAITING_FOR_REASON"
        st.session_state.pending_order_id = order_id

        return (
            f"⚠️ You have requested to cancel **Order #{order_id}**.\n\n"
            f"❓ Please tell us the reason for cancellation."
        )

    # ---------------------------------
    # STATUS FLOW
    # ---------------------------------
    return answer_tool(order_data)



# ---------------- UI ----------------
st.title("🍔 FoodHub AI Customer Support Chatbot")

st.markdown(
    """
Ask questions about your **order status, delivery ETA, or cancellation**.
**Example queries:**
- Where is my order with order_id 1023?
- I want to cancel my order with order_id 1023
"""
)

user_query = st.text_input("💬 Enter your question here")

if st.button("Ask"):
    if user_query.strip():
        with st.spinner("Fetching your order details..."):
            response = chatagent(user_query)
        st.success(response)
    else:
        st.warning("Please enter a question.")