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 recordsfirst_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 columnaddress = fake.address()customer_since = fake.date_between(start_date='-5y', end_date='today')is_active = fake.boolean()# Insert customer datacursor.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 necessaryphone = 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 datacursor.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 inputresponse = 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 JSONexcept Exception as e:st.error(f"An error occurred: {e}")