CREATE DATABASE SalesOrderSchema;
USE SalesOrderSchema;
CREATE TABLE Customer (  CustomerID INT AUTO_INCREMENT PRIMARY KEY,  FirstName VARCHAR(100),  LastName VARCHAR(100),  Email VARCHAR(255),  Phone VARCHAR(20),  BillingAddress TEXT,  ShippingAddress TEXT,  CustomerSince DATE,  IsActive BOOLEAN);
CREATE TABLE SalesOrder (  SalesOrderID INT AUTO_INCREMENT PRIMARY KEY,  CustomerID INT,  OrderDate DATE,  RequiredDate DATE,  ShippedDate DATE,  Status VARCHAR(50),  Comments TEXT,  PaymentMethod VARCHAR(50),  IsPaid BOOLEAN,  FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID));
CREATE TABLE Product (  ProductID INT AUTO_INCREMENT PRIMARY KEY,  ProductName VARCHAR(255),  Description TEXT,  UnitPrice DECIMAL(10, 2),  StockQuantity INT,  ReorderLevel INT,  Discontinued BOOLEAN);
CREATE TABLE LineItem (  LineItemID INT AUTO_INCREMENT PRIMARY KEY,  SalesOrderID INT,  ProductID INT,  Quantity INT,  UnitPrice DECIMAL(10, 2),  Discount DECIMAL(10, 2),  TotalPrice DECIMAL(10, 2),  FOREIGN KEY (SalesOrderID) REFERENCES SalesOrder(SalesOrderID),  FOREIGN KEY (ProductID) REFERENCES Product(ProductID));
CREATE TABLE Employee (  EmployeeID INT AUTO_INCREMENT PRIMARY KEY,  FirstName VARCHAR(100),  LastName VARCHAR(100),  Email VARCHAR(255),  Phone VARCHAR(20),  HireDate DATE,  Position VARCHAR(100),  Salary DECIMAL(10, 2));
CREATE TABLE Supplier (  SupplierID INT AUTO_INCREMENT PRIMARY KEY,  CompanyName VARCHAR(255),  ContactName VARCHAR(100),  ContactTitle VARCHAR(50),  Address TEXT,  Phone VARCHAR(20),  Email VARCHAR(255));
CREATE TABLE InventoryLog (  LogID INT AUTO_INCREMENT PRIMARY KEY,  ProductID INT,  ChangeDate DATE,  QuantityChange INT,  Notes TEXT,  FOREIGN KEY (ProductID) REFERENCES Product(ProductID));

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'SalesOrderSchema' ORDER BY TABLE_NAME, ORDINAL_POSITION;

#The code for loading data into the customer table#Customer Tableimport mysql.connectorfrom faker import Faker
# Initialize Fakerfake = Faker()
# Connect to MySQLconn = mysql.connector.connect(  host="localhost",  user="root",  password="Your MySQL Password",  database="SalesOrderSchema")cursor = conn.cursor()
# Generate and insert datafor _ in range(100):  # Let's say we want to generate 100 records    first_name = fake.first_name()    last_name = fake.last_name()    email = fake.email()    phone = fake.phone_number()    if len(phone) > 20:  # Assuming the 'Phone' column is VARCHAR(20)        phone = phone[:20]  # Truncate phone number to fit into the column    address = fake.address()    customer_since = fake.date_between(start_date='-5y', end_date='today')    is_active = fake.boolean()        # Insert customer data    cursor.execute("""        INSERT INTO Customer (FirstName, LastName, Email, Phone, BillingAddress, ShippingAddress, CustomerSince, IsActive)        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)    """, (first_name, last_name, email, phone, address, address, customer_since, is_active))
# Commit the transactionconn.commit()
# Close the cursor and connectioncursor.close()conn.close()

#Employee Tableimport mysql.connectorfrom faker import Faker
# Initialize Fakerfake = Faker()
# Connect to MySQLconn = mysql.connector.connect(  host="localhost",  user="root",  password="Your MySQL Password",  database="SalesOrderSchema")cursor = conn.cursor()
# Generate and insert 1000 employee recordsfor _ in range(1000):    first_name = fake.first_name()    last_name = fake.last_name()    email = fake.email()    phone = fake.phone_number()    if len(phone) > 20:  # Truncate phone number if necessary        phone = phone[:20]    hire_date = fake.date_between(start_date='-5y', end_date='today')    position = fake.job()    salary = round(fake.random_number(digits=5), 2)  # Generate a 5 digit salary        # Insert employee data    cursor.execute("""        INSERT INTO Employee (FirstName, LastName, Email, Phone, HireDate, Position, Salary)        VALUES (%s, %s, %s, %s, %s, %s, %s)    """, (first_name, last_name, email, phone, hire_date, position, salary))
# Commit the transactionconn.commit()
# Close the cursor and connectioncursor.close()conn.close()
print("1000 employee records inserted successfully.")

import osimport streamlit as stfrom langchain_openai import ChatOpenAIfrom langchain_community.utilities import SQLDatabasefrom langchain_community.agent_toolkits import create_sql_agent
# Set your OpenAI API key hereos.environ["OPENAI_API_KEY"] = "Your OpenAI API Key"
# Directly using database connection detailshost = "localhost"user = "root"password = "Your MySQL Password"database = "SalesOrderSchema"
# Setup database connectiondb_uri = f"mysql+mysqlconnector://{user}:{password}@{host}/{database}"db = SQLDatabase.from_uri(db_uri)llm = ChatOpenAI(model="gpt-4", temperature=0)agent_executor = create_sql_agent(llm, db=db, agent_type="openai-tools", verbose=True)
# Streamlit app layoutst.title('SQL Chatbot')
# User inputuser_query = st.text_area("Enter your SQL-related query:", "List Top 10 Employees by Salary?")
if st.button('Submit'):    #try:        # Processing user input        #response = agent_executor.invoke(user_query)        #response = agent_executor.invoke({"query": user_query})        #if st.button('Submit'):    try:        # Processing user input        response = agent_executor.invoke({            "agent_scratchpad": "",  # Assuming this needs to be an empty string if not used            "input": user_query  # Changed from "query" to "input"        })        st.write("Response:")        st.json(response)  # Use st.json to pretty print the response if it's a JSON    except Exception as e:        st.error(f"An error occurred: {e}")