import streamlit as st
import sqlite3
import re
import os

DB_PATH = os.path.join(os.path.dirname(__file__), "customer_orders.db")


# -------------------------------
# Database Utility
# -------------------------------
# Ensures correct file location regardless of where the app runs

def execute_sql(query):
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    cursor.execute(query)
    rows = cursor.fetchall()
    columns = [desc[0] for desc in cursor.description]
    conn.close()
    return [dict(zip(columns, row)) for row in rows]


# -------------------------------
# SQL Agent (Simplified Wrapper)
# -------------------------------
def ask_sql_agent(question):
    match = re.search(r"O\d+", question)
    if not match:
        return []

    order_id = match.group()
    query = f"SELECT * FROM orders WHERE order_id='{order_id}'"
    return execute_sql(query)


# -------------------------------
# Chat Agent
# -------------------------------
def chat_agent(user_input, ask_sql_agent):
    text = user_input.lower()

    # Guardrails
    if any(word in text for word in ["hack", "all orders", "every order", "dump"]):
        return (
            "I'm sorry, but I can't assist with unauthorized access to customer data."
        )

    # Escalation handling
    if any(word in text for word in ["multiple times", "no response", "immediate"]):
        return (
            "I understand your concern. I'm escalating this to our support team "
            "for immediate assistance."
        )

    # Intent detection
    if "cancel" in text:
        intent = "cancel"
    elif any(word in text for word in ["where", "status", "track"]):
        intent = "status"
    else:
        intent = "unknown"

    # Order ID extraction
    match = re.search(r"O\d+", user_input)
    if not match:
        return "Please provide your order ID (for example: O12501)."

    order_id = match.group()
    result = ask_sql_agent(f"Get order details for {order_id}")

    if not result:
        return "Sorry, I couldn't find your order. Please check the order ID."

    order = result[0]
    status = order.get("order_status", "processing")

    # Business logic responses
    if intent == "cancel":
        if status.lower() in ["preparing food", "placed"]:
            return (
                "Your order is currently being prepared and is eligible for cancellation. "
                "Please proceed through the app or let me know if you need help."
            )
        else:
            return (
                "Your order has already been delivered and cannot be cancelled. "
                "Please contact customer support for further assistance."
            )

    if intent == "status":
        return f"Thank you for your patience. Your order is currently **{status}**."

    return (
        "I can help you with order status, delivery updates, payment details, "
        "or order cancellation."
    )


# -------------------------------
# Streamlit UI
# -------------------------------
st.set_page_config(page_title="Food Delivery Chatbot Assistant", layout="centered")

st.title("Food Delivery Chatbot Assistant")
st.write("Ask about your order status, delivery, payment, or cancellation.")

if "chat_history" not in st.session_state:
    st.session_state.chat_history = []

user_input = st.text_input("You:")

if user_input:
    response = chat_agent(user_input, ask_sql_agent)
    st.session_state.chat_history.append(("You", user_input))
    st.session_state.chat_history.append(("Food Delivery Chatbot Assistant", response))

for role, message in st.session_state.chat_history:
    if role == "You":
        st.markdown(f"**You:** {message}")
    else:
        st.markdown(f"**{role}:** {message}")