import mysql.

connector
from [Link] import Error

def get_db_connection():
"""
Establish a connection to the MySQL database using hardcoded credentials.

Returns:
connection ([Link].connection_cext.CMySQLConnection): The database connection object if successful, or None if an error occurs.
"""
host = 'localhost'
user = 'root'
password = 'yourpassword'
database = 'inventory_db'

try:
connection = [Link](
host=host,
user=user,
password=password,
database=database
)
return connection
except Error as e:
print(f"Error: {e}")
return None

def add_item():
name = input("Enter the item name: ")
quantity = int(input("Enter the quantity: "))
price = float(input("Enter the price: "))
category = input("Enter the category (optional): ") or None

connection = get_db_connection()
if connection:
cursor = [Link]()
[Link](
"INSERT INTO inventory_items (name, quantity, price, category) VALUES (%s, %s, %s, %s)",
(name, quantity, price, category)
)
[Link]()
[Link]()
[Link]()
print("Item added successfully.")

def update_item():
item_id = int(input("Enter the ID of the item to update: "))
name = input("Enter the new item name (leave blank to keep current): ")
quantity = input("Enter the new quantity (leave blank to keep current): ")
price = input("Enter the new price (leave blank to keep current): ")
category = input("Enter the new category (leave blank to keep current): ")

update_fields = []
update_values = []

if name:
update_fields.append("name = %s")
update_values.append(name)
if quantity:
update_fields.append("quantity = %s")
update_values.append(int(quantity))
if price:
update_fields.append("price = %s")
update_values.append(float(price))
if category:
update_fields.append("category = %s")
update_values.append(category)

if update_fields:
update_values.append(item_id)
update_query = f"UPDATE inventory_items SET {', '.join(update_fields)} WHERE id = %s"

connection = get_db_connection()
if connection:
cursor = [Link]()
[Link](update_query, tuple(update_values))
[Link]()
[Link]()
[Link]()
print("Item updated successfully.")

def show_items():
connection = get_db_connection()
if connection:
cursor = [Link](dictionary=True)
[Link]("SELECT * FROM inventory_items")
items = [Link]()
[Link]()
[Link]()

if items:
print("Inventory List:")
for item in items:
print(f"ID: {item['id']}, Name: {item['name']}, Quantity: {item['quantity']}, Price: {item['price']}, Category: {item['category']}")
else:
print("No items found.")

def filter_items():
filter_option = input("Filter by (category/price_range): ").strip().lower()
 connection = get_db_connection()
if connection:
cursor = [Link](dictionary=True)

if filter_option == 'category':
category = input("Enter the category to filter by: ")
[Link]("SELECT * FROM inventory_items WHERE category = %s", (category,))

elif filter_option == 'price_range':

min_price = float(input("Enter the minimum price: "))
max_price = float(input("Enter the maximum price: "))
[Link]("SELECT * FROM inventory_items WHERE price BETWEEN %s AND %s", (min_price, max_price))

else:
print("Invalid filter option.")
return

items = [Link]()
[Link]()
[Link]()

if items:
print(f"Filtered Inventory List ({filter_option.capitalize()}):")
for item in items:
print(f"ID: {item['id']}, Name: {item['name']}, Quantity: {item['quantity']}, Price: {item['price']}, Category: {item['category']}")
else:
print("No items found.")

def delete_item():
item_id = int(input("Enter the ID of the item to delete: "))

connection = get_db_connection()
if connection:
cursor = [Link]()
[Link]("DELETE FROM inventory_items WHERE id = %s", (item_id,))
[Link]()
[Link]()
[Link]()
print("Item deleted successfully.")

def search_items():
search_term = input("Enter search term: ")

connection = get_db_connection()
if connection:
cursor = [Link](dictionary=True)
[Link]("SELECT * FROM inventory_items WHERE name LIKE %s", ('%' + search_term + '%',))
items = [Link]()
[Link]()
[Link]()

if items:
print(f"Search Results for '{search_term}':")
for item in items:
print(f"ID: {item['id']}, Name: {item['name']}, Quantity: {item['quantity']}, Price: {item['price']}, Category: {item['category']}")
else:
print(f"No items found for '{search_term}'.")

def main():
while True:
print("\nInventory Management System")
print("1. Add Item")
print("2. Update Item")
print("3. Show Items")
print("4. Filter Items")
print("5. Delete Item")
print("6. Search Items")
print("7. Exit")

choice = input("Enter your choice: ")

if choice == '1':
add_item()
elif choice == '2':
update_item()
elif choice == '3':
show_items()
elif choice == '4':
filter_items()
elif choice == '5':
delete_item()
elif choice == '6':
search_items()
elif choice == '7':
print("Exiting...")
break
else:
print("Invalid choice. Please try again.")

if __name__ == '__main__':
main()

# -- Create the database if it does not exist

# ❤️​ CREATE DATABASE IF NOT EXISTS inventory_db;

# -- Select the database to use

# ❤️​ USE inventory_db;
# -- Create the table if it does not exist
# ❤️​ CREATE TABLE IF NOT EXISTS inventory_items (
# ❤️​ id INT AUTO_INCREMENT PRIMARY KEY, -- Unique identifier for each item
# ❤️​ name VARCHAR(255) NOT NULL, -- Name of the item
# ❤️​ quantity INT NOT NULL, -- Quantity of the item in stock
# ❤️​ price DECIMAL(10, 2) NOT NULL, -- Price of the item
# ❤️​ category VARCHAR(255) DEFAULT NULL -- Category of the item (optional)
# ❤️​ );