# import streamlit as st
# import pandas as pd
# import sys, os
# from pathlib import Path
# sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
# from api_client import WarehouseAPI
# import altair as alt

# # Add parent directory to path for AI modules
# project_root = Path(__file__).parent.parent
# sys.path.insert(0, str(project_root))

# # Import AI modules
# try:
#     from forecasting.smart_suggestions import get_smart_suggestions
#     from utils.suggestions_panel import display_smart_suggestions
#     imports_successful = True
# except ImportError as e:
#     imports_successful = False
#     import_error = str(e)

# st.set_page_config(page_title="AI Warehouse Management", page_icon="🏭", layout="wide")


# @st.cache_data
# def load_data():
#     api = WarehouseAPI()
#     try:
#         inventory = api.get_inventory()
#         orders = api.get_orders()
#         order_items = api.get_order_items()  # Added for AI suggestions
#         product = api.get_products()
#         spare_parts = api.get_spare_parts()
#         warehouses = api.get_warehouses()
#         return inventory, orders, order_items, product, spare_parts, warehouses
#     except Exception as e:
#         st.error(f"API load error: {e}")
#         return [pd.DataFrame()] * 6

# # Load data once
# inventory, orders, order_items, product, spare_parts, warehouses = load_data()

# # Store in session_state so subpages can access
# st.session_state["inventory"] = inventory
# st.session_state["orders"] = orders
# st.session_state["order_items"] = order_items
# st.session_state["product"] = product
# st.session_state["spare_parts"] = spare_parts
# st.session_state["warehouses"] = warehouses

# st.title("AI-Driven Warehouse Stock Management")
# st.caption("Executive overview with KPIs, category analytics, and operational performance.")

# today = pd.Timestamp.today()


# def merge_inventory_product(inventory, product):
#     if "product_id" in inventory.columns and "product_id" in product.columns:
#         return inventory.merge(product, on="product_id", how="left")
#     return inventory


# # -----------------------------
# # KPI CARDS
# # -----------------------------
# # Fix: Use correct column names from database
# total_inventory = inventory["q_on_hand"].sum() if "q_on_hand" in inventory.columns and not inventory.empty else 0
# processing_orders = len(orders[orders["status"].astype(str).str.lower() == "processing"]) if not orders.empty and "status" in orders.columns else 0
# total_products = product["product_id"].nunique() if not product.empty and "product_id" in product.columns else 0
# total_warehouses = inventory["warehouse_id"].nunique() if not inventory.empty and "warehouse_id" in inventory.columns else 0

# col1, col2, col3, col4 = st.columns(4)
# col1.metric("Total Inventory", f"{total_inventory:,}")
# col2.metric("Processing Orders", processing_orders)
# col3.metric("Total Products", total_products)
# col4.metric("Total Warehouses", total_warehouses)

# # -----------------------------
# # AI - Smart Suggestions Panel
# # -----------------------------
# st.divider()
# # st.subheader("🤖 AI Smart Suggestions")

# if not imports_successful:
#     st.error(f"❌ Import Error: {import_error}")
#     st.info("💡 Make sure forecasting/smart_suggestions.py and utils/suggestions_panel.py exist")
# elif not order_items.empty and not inventory.empty:
#     try:
#         with st.spinner("🤖 Generating AI insights..."):
#             # Generate AI recommendations
#             recommendations = get_smart_suggestions(order_items, inventory, top_n=3)
            
#             # Display recommendations
#             if recommendations:
#                 display_smart_suggestions(recommendations)
#             else:
#                 st.info("No urgent recommendations at this time.")
#     except Exception as e:
#         st.error(f"Error generating suggestions: {str(e)}")
#         st.info("💡 Ensure you have sufficient historical order data.")
# else:
#     st.info("📊 Insufficient data for AI recommendations. Need both order history and inventory data.")

# st.divider()

# # -----------------------------
# # 1. LOW STOCK ALERTS - FIXED
# # -----------------------------
# st.subheader("⚠️ Low Stock Alerts")
# if not inventory.empty and "q_on_hand" in inventory.columns and "safety_stock" in inventory.columns:
#     # Items below safety stock OR below critical threshold (20 units)
#     low_stock = inventory[
#         (inventory["q_on_hand"] <= inventory["safety_stock"]) | 
#         (inventory["q_on_hand"] < 20)
#     ].copy()
    
#     if not low_stock.empty:
#         # Calculate criticality score
#         low_stock["criticality"] = (
#             (low_stock["safety_stock"] - low_stock["q_on_hand"]) / 
#             low_stock["safety_stock"].replace(0, 1)
#         )
#         low_stock = low_stock.sort_values("criticality", ascending=False)
        
#         # Select display columns
#         display_cols = ["sku", "product_id", "warehouse_id", "q_on_hand", "safety_stock", "reorder_point"]
#         available_cols = [col for col in display_cols if col in low_stock.columns]
        
#         st.dataframe(
#             low_stock[available_cols].head(10),
#             width="stretch",
#             hide_index=True
#         )
#         st.caption(f"⚠️ **{len(low_stock)} items** are below safety stock levels")
#     else:
#         st.success("✅ All inventory items are adequately stocked!")
# else:
#     st.warning("No inventory data available")

# # -----------------------------
# # 2. ORDER STATUS GAUGES
# # -----------------------------
# st.divider()
# st.subheader("Order Status KPI")

# if not orders.empty and {"status", "order_date", "estimated_delivery"}.issubset(orders.columns):
#     orders_copy = orders.copy()
#     orders_copy["order_date"] = pd.to_datetime(orders_copy["order_date"], errors='coerce')
#     orders_copy["estimated_delivery"] = pd.to_datetime(orders_copy["estimated_delivery"], errors='coerce')
    
#     # Remove rows with invalid dates
#     orders_copy = orders_copy.dropna(subset=["order_date", "estimated_delivery"])
    
#     if not orders_copy.empty:
#         # Categories: delivered, delayed, in-progress
#         delivered_on_time = orders_copy[
#             (orders_copy["status"].str.lower() == "delivered") & 
#             (orders_copy["estimated_delivery"] <= today)
#         ]

#         delayed = orders_copy[
#             (orders_copy["status"].str.lower() != "delivered") & 
#             (orders_copy["estimated_delivery"] < today)
#         ]

#         in_progress = orders_copy[
#             (orders_copy["status"].str.lower() != "delivered") & 
#             (orders_copy["estimated_delivery"] >= today)
#         ]

#         total_orders = len(orders_copy)
#         delivered_pct = (len(delivered_on_time) / total_orders) * 100 if total_orders > 0 else 0
#         delayed_pct = (len(delayed) / total_orders) * 100 if total_orders > 0 else 0
#         in_progress_pct = (len(in_progress) / total_orders) * 100 if total_orders > 0 else 0

#         # Display gauges
#         col1, col2, col3 = st.columns(3)

#         def make_full_gauge(value, label, color):
#             base = pd.DataFrame({'value': [value, 100 - value], 'category': [label, 'Remaining']})
#             chart = alt.Chart(base).mark_arc(innerRadius=60).encode(
#                 theta=alt.Theta('value:Q'),
#                 color=alt.Color('category:N',
#                                 scale=alt.Scale(domain=[label, 'Remaining'], range=[color, '#E0E0E0']),
#                                 legend=None)
#             ).properties(width=200, height=200)

#             text = alt.Chart(pd.DataFrame({'text': [f"{value:.1f}%"]})).mark_text(
#                 fontSize=22, fontWeight='bold', dy=0, color=color
#             ).encode(text='text:N')
#             label_text = alt.Chart(pd.DataFrame({'text': [label]})).mark_text(
#                 fontSize=14, dy=40, color='white'
#             ).encode(text='text:N')

#             return chart + text + label_text

#         with col1:
#             st.altair_chart(make_full_gauge(delivered_pct, "Delivered", "#2ecc71"), width='stretch')
#             st.caption("Orders delivered on time")
#         with col2:           
#             st.altair_chart(make_full_gauge(delayed_pct, "Delayed", "#f39c12"), width='stretch')
#             st.caption("Orders past estimated delivery date")
#         with col3:
#             st.altair_chart(make_full_gauge(in_progress_pct, "In Progress", "#3498db"), width='stretch')
#             st.caption("Orders not due yet")

#         # -----------------------------
#         # 3. Average Lead Time Over Past 4 Months
#         # -----------------------------
#         st.divider()
#         st.subheader("Average Lead Time Over Past 4 Months")
        
#         past_4_months = today - pd.DateOffset(months=4)
#         lead_time_data = orders_copy[
#             (orders_copy["status"].str.lower() == "delivered") &
#             (orders_copy["order_date"] >= past_4_months)
#         ].copy()

#         if not lead_time_data.empty:
#             lead_time_data["lead_time_days"] = (lead_time_data["estimated_delivery"] - lead_time_data["order_date"]).dt.days
#             lead_time_data["order_week"] = lead_time_data["order_date"].dt.to_period("W").dt.start_time

#             weekly_lead_time = lead_time_data.groupby("order_week")["lead_time_days"].mean().reset_index()

#             line_chart = alt.Chart(weekly_lead_time).mark_line(point=True).encode(
#                 x=alt.X('order_week:T', title='Week'),
#                 y=alt.Y('lead_time_days:Q', title='Average Lead Time (days)'),
#                 tooltip=[alt.Tooltip('order_week:T', title='Week'),
#                         alt.Tooltip('lead_time_days:Q', title='Avg Lead Time')]
#             ).properties(width=700, height=300, title="Average Lead Time Over Past 4 Months")

#             st.altair_chart(line_chart, width='stretch')
#         else:
#             st.info("No delivered orders in the past 4 months to calculate average lead time.")
#     else:
#         st.warning("No valid order data after date parsing")
# else:
#     st.warning("Missing required columns for order status gauges or no orders data available.")


# # -----------------------------
# # 4. PRODUCTS BY CATEGORY
# # -----------------------------
# st.divider()
# st.subheader("Products by Category")

# if not inventory.empty and not product.empty:
#     products_in_wh = merge_inventory_product(inventory, product)
#     # Fix: Use q_on_hand instead of qty
#     if "category" in products_in_wh.columns and "q_on_hand" in products_in_wh.columns:
#         categories = products_in_wh["category"].dropna().unique()
#         if len(categories) > 0:
#             selected_category = st.selectbox("Select Product Category", sorted(categories))
#             cat_products = products_in_wh[products_in_wh["category"] == selected_category]
            
#             if "product_name" in cat_products.columns:
#                 product_sums = cat_products.groupby("product_name")["q_on_hand"].sum().reset_index()

#                 bar_products = alt.Chart(product_sums).mark_bar().encode(
#                     x=alt.X("product_name:N", sort="-y", title="Product"),
#                     y=alt.Y("q_on_hand:Q", title="Quantity On Hand"),
#                     color="product_name:N",
#                     tooltip=["product_name", "q_on_hand"]
#                 ).properties(title=f"Inventory by Product ({selected_category})")
#                 st.altair_chart(bar_products, width='stretch')
#             else:
#                 st.warning("Missing 'product_name' in products dataset.")
#         else:
#             st.info("No product categories found.")
#     else:
#         st.warning("Missing 'category' or 'q_on_hand' columns in dataset.")
# else:
#     st.warning("No inventory or product data available.")