Code Editor for app.py

import streamlit as st
import pandas as pd
from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime
from sqlalchemy.orm import DeclarativeBase, Session
from datetime import datetime
from st_aggrid import AgGrid, GridOptionsBuilder, GridUpdateMode, DataReturnMode

# Database setup
class Base(DeclarativeBase):
    pass

class InventoryItem(Base):
    __tablename__ = 'inventory'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False)
    category = Column(String(50), nullable=False)
    quantity = Column(Integer, nullable=False, default=0)
    unit_price = Column(Float, nullable=False)
    supplier = Column(String(100))
    last_updated = Column(DateTime, default=datetime.now)

# Create database engine and tables
engine = create_engine("sqlite:///inventory.sqlite")
Base.metadata.create_all(bind=engine)

# Helper functions
def add_item(name, category, quantity, unit_price, supplier):
    with Session(engine) as session:
        item = InventoryItem(
            name=name,
            category=category,
            quantity=quantity,
            unit_price=unit_price,
            supplier=supplier,
            last_updated=datetime.now()
        )
        session.add(item)
        session.commit()

def get_all_items():
    with Session(engine) as session:
        items = session.query(InventoryItem).all()
        return [{
            'ID': item.id,
            'Name': item.name,
            'Category': item.category,
            'Quantity': item.quantity,
            'Unit Price': item.unit_price,
            'Total Value': item.quantity * item.unit_price,
            'Supplier': item.supplier,
            'Last Updated': item.last_updated.strftime('%Y-%m-%d %H:%M')
        } for item in items]

def update_item_quantity(item_id, new_quantity):
    with Session(engine) as session:
        item = session.query(InventoryItem).filter(InventoryItem.id == item_id).first()
        if item:
            item.quantity = new_quantity
            item.last_updated = datetime.now()
            session.commit()
            return True
    return False

def delete_item(item_id):
    with Session(engine) as session:
        item = session.query(InventoryItem).filter(InventoryItem.id == item_id).first()
        if item:
            session.delete(item)
            session.commit()
            return True
    return False

# Streamlit app
st.set_page_config(page_title="Inventory Management", layout="wide")

st.title("📦 Inventory Management System")

# Sidebar for adding new items
with st.sidebar:
    st.header("Add New Item")
    
    with st.form("add_item_form"):
        name = st.text_input("Item Name", placeholder="Enter item name")
        category = st.selectbox("Category", ["Electronics", "Furniture", "Clothing", "Books", "Food", "Other"])
        quantity = st.number_input("Quantity", min_value=0, value=0)
        unit_price = st.number_input("Unit Price ($)", min_value=0.0, value=0.0, format="%.2f")
        supplier = st.text_input("Supplier", placeholder="Enter supplier name")
        
        submitted = st.form_submit_button("Add Item")
        
        if submitted:
            if name and category and unit_price >= 0:
                add_item(name, category, quantity, unit_price, supplier)
                st.success("Item added successfully!")
                st.rerun()
            else:
                st.error("Please fill in all required fields")

# Main content area
col1, col2 = st.columns([3, 1])

with col2:
    st.subheader("Quick Stats")
    items_data = get_all_items()
    if items_data:
        df = pd.DataFrame(items_data)
        total_items = len(df)
        total_value = df['Total Value'].sum()
        low_stock_items = len(df[df['Quantity'] < 10])
        
        st.metric("Total Items", total_items)
        st.metric("Total Inventory Value", f"${total_value:,.2f}")
        st.metric("Low Stock Items", low_stock_items)
        
        # Category breakdown
        st.subheader("Items by Category")
        category_counts = df['Category'].value_counts()
        st.bar_chart(category_counts)

with col1:
    st.subheader("Inventory Items")
    
    # Get inventory data
    items_data = get_all_items()
    
    if items_data:
        df = pd.DataFrame(items_data)
        
        # Configure grid options
        gb = GridOptionsBuilder.from_dataframe(df)
        gb.configure_pagination(paginationAutoPageSize=True)
        gb.configure_side_bar()
        gb.configure_selection('multiple', use_checkbox=True)
        gb.configure_default_column(editable=False, groupable=True)
        
        # Make quantity column editable
        gb.configure_column("Quantity", editable=True, type=["numericColumn"])
        
        # Format currency columns
        gb.configure_column("Unit Price", type=["numericColumn"], valueFormatter="'$' + x.toLocaleString()")
        gb.configure_column("Total Value", type=["numericColumn"], valueFormatter="'$' + x.toLocaleString()")
        
        # Configure low stock highlighting
        gb.configure_column("Quantity", cellStyle={'backgroundColor': 'lightyellow'}, 
                          cellClassRules={'low-stock': 'x < 10'})
        
        gridOptions = gb.build()
        
        # Display grid
        grid_response = AgGrid(
            df,
            gridOptions=gridOptions,
            data_return_mode=DataReturnMode.FILTERED_AND_SORTED,
            update_mode=GridUpdateMode.MODEL_CHANGED,
            fit_columns_on_grid_load=True,
            height=400,
            allow_unsafe_jscode=True
        )
        
        # Handle quantity updates
        if grid_response['data_changed'] and not grid_response['data'].empty:
            updated_df = grid_response['data']
            original_df = pd.DataFrame(items_data)
            
            # Find rows with quantity changes
            for idx, row in updated_df.iterrows():
                original_qty = original_df.loc[idx, 'Quantity']
                new_qty = row['Quantity']
                
                if original_qty != new_qty:
                    item_id = row['ID']
                    if update_item_quantity(item_id, new_qty):
                        st.success(f"Updated quantity for {row['Name']} to {new_qty}")
                        st.rerun()
        
        # Action buttons
        st.subheader("Actions")
        col_a, col_b = st.columns(2)
        
        with col_a:
            if st.button("🔄 Refresh Data"):
                st.rerun()
        
        with col_b:
            selected_rows = grid_response['selected_rows']
            if selected_rows and st.button("🗑️ Delete Selected"):
                for row in selected_rows:
                    delete_item(row['ID'])
                st.success(f"Deleted {len(selected_rows)} item(s)")
                st.rerun()
        
        # Low stock alert
        if items_data:
            low_stock_df = df[df['Quantity'] < 10]
            if not low_stock_df.empty:
                st.warning("⚠️ Low Stock Alert!")
                st.dataframe(low_stock_df[['Name', 'Quantity', 'Category']], use_container_width=True)
    
    else:
        st.info("No items in inventory. Add some items using the sidebar!")
        
        # Sample data button
        if st.button("Add Sample Data"):
            sample_items = [
                ("Laptop", "Electronics", 5, 999.99, "TechCorp"),
                ("Office Chair", "Furniture", 12, 199.99, "FurnitureInc"),
                ("T-Shirt", "Clothing", 25, 19.99, "ClothingCo"),
                ("Python Book", "Books", 8, 49.99, "BookStore"),
                ("Coffee Beans", "Food", 3, 12.99, "CoffeeSupply")
            ]
            
            for item in sample_items:
                add_item(*item)
            
            st.success("Sample data added!")
            st.rerun()