Py.Cafe

import streamlit as st
import pandas as pd
import io
import os
import json
from datetime import datetime
from inventory_processor import InventoryProcessor
from excel_handler import ExcelHandler

# Disable telemetry and usage statistics
os.environ['STREAMLIT_BROWSER_GATHER_USAGE_STATS'] = 'false'
os.environ['STREAMLIT_GATHER_USAGE_STATS'] = 'false'

# Settings file path
SETTINGS_FILE = 'app_settings.json'

def load_settings():
    """Load settings from file"""
    default_settings = {
        'current_month': datetime.now().month,
        'lead_time_months': 1.0,
        'safety_stock_months': 0.5,
        'column_mappings': {}
    }
    
    try:
        if os.path.exists(SETTINGS_FILE):
            with open(SETTINGS_FILE, 'r') as f:
                settings = json.load(f)
                # Merge with defaults to handle missing keys
                return {**default_settings, **settings}
    except:
        pass
    
    return default_settings

def save_settings(settings):
    """Save settings to file"""
    try:
        with open(SETTINGS_FILE, 'w') as f:
            json.dump(settings, f, indent=2)
    except:
        pass

def main():
    st.title("📦 Inventory Management Tool")
    st.markdown("Upload your inventory Excel file to calculate optimal reorder quantities")
    
    # Load saved settings
    saved_settings = load_settings()
    
    # Initialize session state
    if 'processed_data' not in st.session_state:
        st.session_state.processed_data = None
    if 'filtered_count' not in st.session_state:
        st.session_state.filtered_count = 0
    if 'total_count' not in st.session_state:
        st.session_state.total_count = 0
    
    # File upload section
    st.header("📁 Upload Inventory File")
    uploaded_file = st.file_uploader(
        "Choose an Excel file",
        type=['xlsx', 'xls'],
        help="Upload your inventory Excel file containing item data"
    )
    
    if uploaded_file is not None:
        try:
            # Load and display basic file info
            excel_handler = ExcelHandler()
            df = excel_handler.load_excel_file(uploaded_file)
            
            st.success(f"✅ File loaded successfully! Found {len(df)} items")
            
            # Show data preview
            with st.expander("📋 Data Preview", expanded=False):
                st.markdown("**First 5 rows of your data:**")
                st.dataframe(df.head(), use_container_width=True)
                st.markdown(f"**Available columns:** {', '.join(df.columns.tolist())}")
            
            # Settings info and reset option
            if os.path.exists(SETTINGS_FILE):
                col_info, col_reset = st.columns([3, 1])
                with col_info:
                    st.info("💾 Using your saved settings from last session. Adjust values above if needed.")
                with col_reset:
                    if st.button("🔄 Reset Settings", help="Clear saved settings and use defaults"):
                        try:
                            os.remove(SETTINGS_FILE)
                            st.success("Settings reset! Refresh the page to see default values.")
                            st.rerun()
                        except:
                            st.error("Could not reset settings file")
            
            # Configuration section
            st.header("⚙️ Configuration")
            st.info("💡 Monthly average will be calculated as: Year-to-Date Usage ÷ Current Month")
            
            col1, col2, col3 = st.columns(3)
            
            with col1:
                current_month = st.number_input(
                    "Current month of year",
                    min_value=1,
                    max_value=12,
                    value=saved_settings['current_month'],
                    help="Which month are we currently in? Used to calculate monthly average from year-to-date data."
                )
            
            with col2:
                lead_time_months = st.number_input(
                    "Lead time (months)",
                    min_value=0.1,
                    max_value=12.0,
                    value=saved_settings['lead_time_months'],
                    step=0.1,
                    help="How many months does it take to receive orders?"
                )
            
            with col3:
                safety_stock_months = st.number_input(
                    "Safety stock (months)",
                    min_value=0.0,
                    max_value=12.0,
                    value=saved_settings['safety_stock_months'],
                    step=0.1,
                    help="Additional stock buffer in months of usage"
                )
            
            # Column mapping section
            st.header("📊 Column Mapping")
            st.markdown("Map your Excel columns to the required fields:")
            st.info("💡 Columns are labeled as 'Column A', 'Column B', etc. to match Excel letter format")
            
            columns = df.columns.tolist()
            
            col1, col2, col3 = st.columns(3)
            
            # Get saved column mappings or use first column as default
            def get_saved_column(field_name, default_index=0):
                saved_col = saved_settings['column_mappings'].get(field_name)
                if saved_col and saved_col in columns:
                    return columns.index(saved_col)
                return min(default_index, len(columns) - 1) if columns else 0
            
            with col1:
                item_name_col = st.selectbox(
                    "Item Name/ID Column",
                    options=columns,
                    index=get_saved_column('item_name', 0),
                    help="Column containing item names or IDs"
                )
                
                on_hand_col = st.selectbox(
                    "Quantity on Hand",
                    options=columns,
                    index=get_saved_column('on_hand', 1),
                    help="Current inventory quantity"
                )
            
            with col2:
                pending_sales_col = st.selectbox(
                    "Pending Sales",
                    options=columns,
                    index=get_saved_column('pending_sales', 2),
                    help="Quantity committed to pending sales"
                )
                
                already_ordered_col = st.selectbox(
                    "Already Ordered",
                    options=columns,
                    index=get_saved_column('already_ordered', 3),
                    help="Quantity already ordered from supplier"
                )
            
            with col3:
                yearly_usage_col = st.selectbox(
                    "Year-to-Date Usage",
                    options=columns,
                    index=get_saved_column('yearly_usage', 4),
                    help="Total usage from January through current month (year-to-date sales)"
                )
                
                location_col = st.selectbox(
                    "Location/Warehouse",
                    options=columns,
                    index=get_saved_column('location', 5),
                    help="Storage location or warehouse for the item"
                )
            
            # Process button
            if st.button("🔄 Process Inventory", type="primary"):
                # Store parameters in session state for later use
                st.session_state.lead_time_months = lead_time_months
                st.session_state.safety_stock_months = safety_stock_months
                
                # Save settings for next time
                current_settings = {
                    'current_month': current_month,
                    'lead_time_months': lead_time_months,
                    'safety_stock_months': safety_stock_months,
                    'column_mappings': {
                        'item_name': item_name_col,
                        'on_hand': on_hand_col,
                        'pending_sales': pending_sales_col,
                        'already_ordered': already_ordered_col,
                        'yearly_usage': yearly_usage_col,
                        'location': location_col
                    }
                }
                save_settings(current_settings)
                
                process_inventory(
                    df, excel_handler, current_month, lead_time_months, 
                    safety_stock_months, item_name_col, on_hand_col, 
                    pending_sales_col, already_ordered_col, yearly_usage_col, location_col
                )
            
            # Display results if processed
            if st.session_state.processed_data is not None:
                display_results()
                
        except Exception as e:
            st.error(f"❌ Error processing file: {str(e)}")
            st.markdown("**Please ensure your Excel file contains the required columns with numeric data.**")

def process_inventory(df, excel_handler, current_month, lead_time_months, 
                     safety_stock_months, item_name_col, on_hand_col, 
                     pending_sales_col, already_ordered_col, yearly_usage_col, location_col):
    """Process the inventory data and calculate reorder quantities"""
    
    with st.spinner("Processing inventory data..."):
        try:
            # Initialize processor
            processor = InventoryProcessor(
                current_month=current_month,
                lead_time_months=lead_time_months,
                safety_stock_months=safety_stock_months
            )
            
            # Column mapping
            column_mapping = {
                'item_name': item_name_col,
                'on_hand': on_hand_col,
                'pending_sales': pending_sales_col,
                'already_ordered': already_ordered_col,
                'yearly_usage': yearly_usage_col,
                'location': location_col
            }
            
            # Process the data
            processed_df = processor.process_inventory(df, column_mapping)
            st.session_state.total_count = len(processed_df)
            
            # Filter items that need ordering
            filtered_df = processor.filter_items_needing_orders(processed_df)
            st.session_state.filtered_count = len(filtered_df)
            
            # Store processed data
            st.session_state.processed_data = filtered_df
            
            st.success(f"✅ Processing complete! {st.session_state.filtered_count} out of {st.session_state.total_count} items need reordering.")
            
        except Exception as e:
            st.error(f"❌ Error during processing: {str(e)}")

def display_results():
    """Display the processed results"""
    
    st.header("📈 Results")
    
    # Summary metrics
    col1, col2, col3 = st.columns(3)
    
    with col1:
        st.metric(
            "Items Needing Orders",
            st.session_state.filtered_count,
            help="Items with positive reorder quantities"
        )
    
    with col2:
        st.metric(
            "Total Items Processed",
            st.session_state.total_count,
            help="Total items in the uploaded file"
        )
    
    with col3:
        filtered_percentage = (st.session_state.filtered_count / st.session_state.total_count) * 100 if st.session_state.total_count > 0 else 0
        st.metric(
            "Reorder Percentage",
            f"{filtered_percentage:.1f}%",
            help="Percentage of items requiring reorder"
        )
    
    # Display filtered data
    if len(st.session_state.processed_data) > 0:
        st.subheader("Items Requiring Reorder")
        
        # Format the display dataframe
        display_df = st.session_state.processed_data.copy()
        
        # Round numeric columns for better display
        numeric_columns = ['Monthly Average', 'Reorder Quantity', 'On Hand', 'Pending Sales', 'Already Ordered']
        for col in numeric_columns:
            if col in display_df.columns:
                display_df[col] = display_df[col].round(2)
        
        st.dataframe(
            display_df,
            use_container_width=True,
            hide_index=True
        )
        
        # Download section
        st.subheader("📥 Download Results")
        
        excel_handler = ExcelHandler()
        excel_buffer = excel_handler.create_excel_output(
            st.session_state.processed_data,
            lead_time_months=st.session_state.get('lead_time_months', 1.0),
            safety_stock_months=st.session_state.get('safety_stock_months', 0.5)
        )
        
        st.download_button(
            label="📊 Download Excel File",
            data=excel_buffer.getvalue(),
            file_name=f"reorder_quantities_{datetime.now().strftime('%Y%m%d_%H%M')}.xlsx",
            mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            help="Download Excel file with formulas showing reorder calculations"
        )
        
    else:
        st.info("🎉 Great news! No items currently need reordering based on your criteria.")

if __name__ == "__main__":
    main()