-- Sales database schema
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    city TEXT,
    signup_date DATE
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT,
    category TEXT,
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    order_date DATE,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    hire_date DATE,
    salary DECIMAL(10,2)
);

-- Insert sample data
INSERT INTO customers VALUES
(1, 'Alice Johnson', 'alice@email.com', 'Mumbai', '2023-01-15'),
(2, 'Bob Smith', 'bob@email.com', 'Delhi', '2023-02-20'),
(3, 'Carol White', 'carol@email.com', 'Bangalore', '2023-03-10');

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 80000),
(2, 'Phone', 'Electronics', 50000),
(3, 'Book', 'Books', 500);

INSERT INTO orders VALUES
(1, 1, 1, 1, '2023-04-01', 80000),
(2, 2, 2, 2, '2023-04-05', 100000),
(3, 3, 3, 5, '2023-04-10', 2500),
(4, 1, 2, 1, '2023-04-15', 50000);

INSERT INTO employees VALUES
(1, 'John Doe', 'Sales', '2022-01-01', 60000),
(2, 'Jane Smith', 'Marketing', '2022-06-01', 55000),
(3, 'Mike Johnson', 'Engineering', '2022-09-01', 80000);

import openai

def generate_sql(question, schema):
    prompt = f"""You are a SQL expert. Convert the following natural language question into a SQL query.

    Database Schema:
    {schema}

    Question: {question}

    SQL Query:
    """

    response = openai.ChatCompletion.create(
        model="gpt-4",
        messages=[{"role": "user", "content": prompt}]
    )

    sql = response.choices[0].message.content.strip()

    # Clean up SQL (remove markdown formatting)
    if sql.startswith('```sql'):
        sql = sql.split('```sql')[1].split('```')[0].strip()
    elif sql.startswith('```'):
        sql = sql.split('```')[1].split('```')[0].strip()

    return sql

# Example
schema = """
customers: customer_id, name, email, city, signup_date
products: product_id, name, category, price
orders: order_id, customer_id, product_id, quantity, order_date, total_amount
employees: employee_id, name, department, hire_date, salary
"""

question = "What are the total sales by product category?"
sql = generate_sql(question, schema)
print(sql)

# Enhanced with schema description
def get_schema_description():
    return """
    customers table: customer_id (integer, primary key), name (text), email (text), city (text), signup_date (date)
    products table: product_id (integer, primary key), name (text), category (text), price (decimal)
    orders table: order_id (integer, primary key), customer_id (integer, foreign key to customers), product_id (integer, foreign key to products), quantity (integer), order_date (date), total_amount (decimal)
    employees table: employee_id (integer, primary key), name (text), department (text), hire_date (date), salary (decimal)

    Relationships:
    - orders.customer_id references customers.customer_id
    - orders.product_id references products.product_id
    """

def generate_sql_with_context(question):
    schema = get_schema_description()
    return generate_sql(question, schema)

import sqlite3
import pandas as pd

def execute_query(sql):
    conn = sqlite3.connect('sales.db')
    try:
        df = pd.read_sql_query(sql, conn)
        return df
    except Exception as e:
        return f"Error: {str(e)}"
    finally:
        conn.close()

def get_data(question):
    schema = get_schema_description()
    sql = generate_sql(question, schema)
    results = execute_query(sql)

    # Return both SQL and results
    return {
        'sql': sql,
        'results': results
    }

# Format results for display
def format_results(results):
    if isinstance(results, str):
        return {"error": results}

    if results.empty:
        return {"message": "No results found", "data": []}

    return {
        "columns": results.columns.tolist(),
        "data": results.values.tolist(),
        "row_count": len(results)
    }

# Example usage
question = "Show me total sales by product category"
result = get_data(question)
formatted = format_results(result['results'])
print(f"SQL: {result['sql']}")
print(f"Results: {formatted}")

import streamlit as st

st.title("Natural Language to SQL Analytics Assistant")

st.markdown("""
Ask questions about your data in plain English.
The assistant will convert your question to SQL and show results.
""")

# Example questions
examples = [
    "What are the total sales by product category?",
    "Show me customers from Mumbai",
    "What is the average order value?",
    "List employees with salary above 60000"
]

st.subheader("Try these examples:")
for ex in examples:
    if st.button(ex):
        st.session_state.question = ex

# User input
question = st.text_input("Your question:", value=st.session_state.get('question', ''))

if question and st.button("Get Data"):
    with st.spinner("Generating SQL query..."):
        result = get_data(question)

    # Show SQL query
    with st.expander("Generated SQL Query"):
        st.code(result['sql'], language='sql')

    # Show results
    if isinstance(result['results'], str):
        st.error(result['results'])
    elif result['results'].empty:
        st.info("No results found for your query.")
    else:
        st.subheader("Query Results")
        st.dataframe(result['results'])
        st.caption(f"Rows: {len(result['results'])}")

# Deploy on Streamlit Cloud
# Requirements:
# streamlit, openai, pandas, sqlite3

# Environment variables:
# OPENAI_API_KEY

# Run:
# streamlit run app.py