import sqlite3
import random
import datetime

# Connect to sqlite
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# Create table with transaction_date
table = """
CREATE TABLE item (
    item_name VARCHAR(255),
    item_price INT,
    item_quantity INT,
    item_purchase_price INT,
    transaction_date DATE
);
"""
cursor.execute(table)

# Sample item names
item_names = ['Rice', 'Wheat', 'Milk', 'Eggs', 'Butter', 'Salt', 'Sugar', 'Tea', 'Coffee', 'Bread',
              'Flour', 'Oil', 'Chicken', 'Beef', 'Apple', 'Banana', 'Onion', 'Tomato', 'Potato', 'Garlic']

# Generate and insert sample data
for _ in range(500):
    item_name = random.choice(item_names) + f"_{random.randint(1, 1000)}"
    item_price = random.randint(10, 500)
    item_quantity = random.randint(1, 100)
    item_purchase_price = item_price - random.randint(1, 5)

    # Random date in the last 60 days
    transaction_date = datetime.date.today() - datetime.timedelta(days=random.randint(0, 60))

    insert_query = '''
    INSERT INTO item (item_name, item_price, item_quantity, item_purchase_price, transaction_date)
    VALUES (?, ?, ?, ?, ?);
    '''
    cursor.execute(insert_query, (item_name, item_price, item_quantity, item_purchase_price, transaction_date))

# Display data inserted
print("Data Inserted in the table:")
data = cursor.execute('''SELECT * FROM item''')
for row in data:
    print(row)

# Commit and close
conn.commit()
conn.close()