import gradio as gr
import pandas as pd
from pandasql import sqldf

# 模拟数据库表
products = pd.DataFrame({
    "product_id": [1, 2, 3, 4, 5],
    "name": ["iPhone 14", "Galaxy S22", "Sony WH-1000XM5", "MacBook Pro", "Echo Dot"],
    "category": ["Electronics", "Electronics", "Audio", "Computers", "Smart Home"],
    "price": [799.99, 699.99, 399.99, 1999.99, 49.99],
    "stock": [50, 40, 30, 20, 70]
})

orders = pd.DataFrame({
    "order_id": [1, 2, 3],
    "order_number": ["ORD001", "ORD002", "ORD003"],
    "customer_id": [1, 2, 3],
    "total_amount": [1599.98, 699.99, 399.99],
    "status": ["PAID", "PAID", "PENDING"]
})

customers = pd.DataFrame({
    "customer_id": [1, 2, 3],
    "name": ["Alice", "Bob", "Charlie"],
    "email": ["alice@example.com", "bob@example.com", "charlie@example.com"],
    "phone": ["1234567890", "2345678901", "3456789012"]
})

order_items = pd.DataFrame({
    "order_item_id": [1, 2, 3],
    "order_id": [1, 2, 3],
    "product_id": [1, 2, 3],
    "quantity": [2, 1, 1],
    "subtotal": [1599.98, 699.99, 399.99]
})

# 定义查询函数
def query_to_sql(sql_query):
    try:
        # 将 Pandas 数据帧作为 SQL 查询的上下文
        context = {
            "products": products,
            "orders": orders,
            "customers": customers,
            "order_items": order_items
        }
        result = sqldf(sql_query, context)
        return result.to_string(index=False)  # 返回查询结果
    except Exception as e:
        return f"Error: {str(e)}"

# 创建 Gradio 界面
interface = gr.Interface(
    fn=query_to_sql,
    inputs="text",
    outputs="text",
    title="SQL Query Simulator",
    description="输入 SQL 查询语句，模拟查询结果。"
)

# 启动应用
interface.launch()