pip install langchain==0.3.0 langchain-openai==0.2.0 langchain-community==0.3.0 \
    chromadb==0.5.0 python-dotenv==1.0.1 faker==30.0.0

# database.py
import sqlite3
import json
from datetime import datetime, timedelta
import random

def create_ecommerce_db(db_path: str = "/tmp/ecommerce.db"):
    """Create a sample e-commerce database with products and orders."""
    conn = sqlite3.connect(db_path)
    c = conn.cursor()

    c.execute("""CREATE TABLE IF NOT EXISTS products (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        category TEXT,
        price REAL,
        description TEXT,
        inventory_count INTEGER,
        rating REAL,
        tags TEXT
    )""")

    c.execute("""CREATE TABLE IF NOT EXISTS orders (
        order_id TEXT PRIMARY KEY,
        customer_email TEXT,
        customer_name TEXT,
        product_ids TEXT,
        total_amount REAL,
        status TEXT,
        created_at TEXT,
        tracking_number TEXT,
        estimated_delivery TEXT
    )""")

    # Sample products
    products = [
        ("P001", "Wireless Noise-Canceling Headphones", "Electronics", 249.99,
         "Premium over-ear headphones with 30h battery and ANC technology",
         45, 4.7, '["audio", "wireless", "premium"]'),
        ("P002", "Ergonomic Desk Chair", "Furniture", 399.99,
         "Lumbar support, adjustable armrests, breathable mesh back",
         12, 4.5, '["office", "ergonomic", "chair"]'),
        ("P003", "Running Shoes - Trail Edition", "Footwear", 139.99,
         "Lightweight trail running shoes with aggressive tread pattern",
         67, 4.8, '["running", "outdoor", "shoes"]'),
        ("P004", "Stainless Steel Water Bottle", "Kitchen", 34.99,
         "32oz insulated bottle, keeps cold 24h, hot 12h",
         200, 4.6, '["hydration", "eco-friendly", "kitchen"]'),
        ("P005", "Laptop Stand Adjustable", "Electronics", 79.99,
         "Aluminum laptop stand, fits 10-17in laptops, foldable",
         88, 4.4, '["laptop", "desk", "accessories"]'),
    ]
    c.executemany("INSERT OR REPLACE INTO products VALUES (?,?,?,?,?,?,?,?)", products)

    # Sample orders
    statuses = ["processing", "shipped", "delivered", "returned"]
    tracking_prefix = ["1Z999AA1", "9400111899", "7489271695"]
    for i in range(20):
        order_id = f"ORD-{10000 + i}"
        created = (datetime.now() - timedelta(days=random.randint(1, 30))).isoformat()
        status = random.choice(statuses)
        c.execute("INSERT OR REPLACE INTO orders VALUES (?,?,?,?,?,?,?,?,?)", (
            order_id,
            f"customer{i}@example.com",
            f"Customer {i}",
            json.dumps([random.choice(["P001", "P002", "P003", "P004", "P005"])]),
            round(random.uniform(30, 400), 2),
            status,
            created,
            f"{random.choice(tracking_prefix)}{random.randint(100000, 999999)}",
            (datetime.now() + timedelta(days=random.randint(1, 7))).strftime("%Y-%m-%d"),
        ))

    conn.commit()
    conn.close()
    return db_path

DB_PATH = create_ecommerce_db()

def query_db(sql: str, params: tuple = ()) -> list[dict]:
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    result = [dict(row) for row in conn.execute(sql, params).fetchall()]
    conn.close()
    return result

# product_search.py
from langchain_openai import OpenAIEmbeddings
from langchain_community.vectorstores import Chroma
from langchain_core.documents import Document
from database import query_db
import json

def build_product_vectorstore() -> Chroma:
    """Index product descriptions for semantic search."""
    products = query_db("SELECT * FROM products")
    docs = []
    for p in products:
        tags = json.loads(p["tags"])
        content = (
            f"{p['name']}. {p['description']}. "
            f"Category: {p['category']}. Tags: {', '.join(tags)}. "
            f"Price: ${p['price']}."
        )
        docs.append(Document(
            page_content=content,
            metadata={"id": p["id"], "name": p["name"], "price": p["price"],
                       "category": p["category"], "inventory": p["inventory_count"]},
        ))

    embeddings = OpenAIEmbeddings()
    return Chroma.from_documents(docs, embeddings, collection_name="products")

product_store = build_product_vectorstore()

# agent_tools.py
import json
from langchain.tools import tool
from product_search import product_store
from database import query_db

RETURN_POLICY = """
Our return policy: Items can be returned within 30 days of delivery.
Electronics must be unused and in original packaging.
Clothing can be returned if unworn with tags attached.
Return shipping is free for defective items. Customer pays for change-of-mind returns.
Refunds are processed within 5-7 business days.
"""

@tool
def search_products(query: str, max_results: int = 5) -> str:
    """Search the product catalog using a natural language query."""
    results = product_store.similarity_search(query, k=max_results)
    if not results:
        return "No products found matching your query."
    lines = ["Here are matching products:"]
    for doc in results:
        m = doc.metadata
        stock = "In stock" if m["inventory"] > 0 else "Out of stock"
        lines.append(
            f"- {m['name']} | ${m['price']} | {stock}\n"
            f"  {doc.page_content[:120]}..."
        )
    return "\n".join(lines)

@tool
def get_product_details(product_id: str) -> str:
    """Get full details for a specific product by its ID (e.g., P001)."""
    products = query_db("SELECT * FROM products WHERE id = ?", (product_id,))
    if not products:
        return f"Product {product_id} not found."
    p = products[0]
    tags = json.loads(p["tags"])
    return (
        f"Product: {p['name']}\n"
        f"ID: {p['id']}\n"
        f"Price: ${p['price']}\n"
        f"Category: {p['category']}\n"
        f"Description: {p['description']}\n"
        f"Rating: {p['rating']}/5\n"
        f"Stock: {p['inventory_count']} units available\n"
        f"Tags: {', '.join(tags)}"
    )

@tool
def lookup_order(order_id: str) -> str:
    """Look up the status and details of an order by order ID."""
    orders = query_db("SELECT * FROM orders WHERE order_id = ?", (order_id,))
    if not orders:
        return f"Order {order_id} not found. Please verify the order ID."
    o = orders[0]
    product_ids = json.loads(o["product_ids"])
    return (
        f"Order: {o['order_id']}\n"
        f"Status: {o['status'].title()}\n"
        f"Total: ${o['total_amount']}\n"
        f"Items: {', '.join(product_ids)}\n"
        f"Placed: {o['created_at'][:10]}\n"
        f"Tracking: {o['tracking_number']}\n"
        f"Estimated Delivery: {o['estimated_delivery']}"
    )

@tool
def get_policy_info(topic: str) -> str:
    """Get information about store policies: returns, shipping, warranty, privacy."""
    policies = {
        "return": RETURN_POLICY,
        "shipping": "Standard shipping: 3-5 business days ($4.99). Express: 1-2 days ($14.99). Free standard shipping on orders over $50.",
        "warranty": "All electronics have a 1-year manufacturer warranty. Extended warranties available at checkout.",
        "privacy": "We do not sell customer data. See our full privacy policy at /privacy.",
    }
    for key, value in policies.items():
        if key in topic.lower():
            return value
    return "Policy topic not recognized. Available topics: return, shipping, warranty, privacy."

@tool
def initiate_return(order_id: str, reason: str) -> str:
    """Initiate a return request for an order. Requires order_id and reason."""
    orders = query_db(
        "SELECT * FROM orders WHERE order_id = ? AND status = 'delivered'",
        (order_id,),
    )
    if not orders:
        return (
            f"Cannot initiate return for {order_id}. "
            "Order must be in 'delivered' status to start a return."
        )
    return_id = f"RET-{order_id}"
    return (
        f"Return initiated successfully!\n"
        f"Return ID: {return_id}\n"
        f"Reason recorded: {reason}\n"
        f"A prepaid return label will be emailed within 24 hours.\n"
        f"Refund will be processed within 5-7 business days of receipt."
    )

@tool
def escalate_to_human(customer_message: str, reason: str) -> str:
    """Escalate this conversation to a human support agent."""
    ticket_id = f"TKT-{hash(customer_message) % 100000:05d}"
    return (
        f"I've escalated your request to our support team.\n"
        f"Ticket ID: {ticket_id}\n"
        f"Reason: {reason}\n"
        f"Expected response time: 2-4 business hours.\n"
        f"You'll receive an email confirmation shortly."
    )

# agent.py
from langchain_openai import ChatOpenAI
from langchain.agents import create_openai_tools_agent, AgentExecutor
from langchain_core.prompts import ChatPromptTemplate, MessagesPlaceholder
from langchain_core.messages import HumanMessage, AIMessage
from agent_tools import (
    search_products, get_product_details, lookup_order,
    get_policy_info, initiate_return, escalate_to_human,
)

SYSTEM_PROMPT = """You are a helpful e-commerce customer service agent for TechShop.

Your capabilities:
- Search for products and provide recommendations
- Look up order status and tracking information
- Explain store policies (returns, shipping, warranty)
- Initiate return requests for eligible orders
- Escalate complex issues to human support

Guidelines:
- Always be polite and solution-oriented
- Look up real data before answering questions about orders or products
- If a customer is frustrated or has a complex issue, offer to escalate
- Never make up order numbers, product IDs, or policies
- Escalate when: customer explicitly asks for human, issue is complex/emotional, or you cannot resolve it"""

def build_ecommerce_agent() -> AgentExecutor:
    llm = ChatOpenAI(model="gpt-4o", temperature=0.1)
    tools = [
        search_products, get_product_details, lookup_order,
        get_policy_info, initiate_return, escalate_to_human,
    ]
    prompt = ChatPromptTemplate.from_messages([
        ("system", SYSTEM_PROMPT),
        MessagesPlaceholder(variable_name="chat_history"),
        ("human", "{input}"),
        MessagesPlaceholder(variable_name="agent_scratchpad"),
    ])
    agent = create_openai_tools_agent(llm, tools, prompt)
    return AgentExecutor(agent=agent, tools=tools, verbose=False, max_iterations=8)

def run_chat_session():
    executor = build_ecommerce_agent()
    history = []
    print("TechShop Customer Service Agent. Type 'quit' to exit.\n")

    while True:
        user_input = input("Customer: ").strip()
        if user_input.lower() in ("quit", "exit"):
            break
        result = executor.invoke({"input": user_input, "chat_history": history})
        response = result["output"]
        print(f"\nAgent: {response}\n")
        history.extend([HumanMessage(content=user_input), AIMessage(content=response)])

if __name__ == "__main__":
    run_chat_session()