# import os
# import sys
# import json
# from typing import List, Dict, Any

# import pandas as pd
# import streamlit as st
# from langchain_core.messages import SystemMessage, HumanMessage, AIMessage
# from langchain_groq import ChatGroq

# # Allow imports from project root
# sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), "..")))

# from api_client import WarehouseAPI


# # -----------------------------
# # Data loading (Streamlit cache)
# # -----------------------------
# @st.cache_data(show_spinner=False)
# def load_warehouse_data():
#     """
#     Load all warehouse-related data via the WarehouseAPI.

#     Returns:
#         inventory_df, order_items_df, product_df,
#         spare_parts_df, warehouses_df, orders_df
#     """
#     api = WarehouseAPI()

#     try:
#         inventory_df = api.get_inventory()
#         order_items_df = api.get_order_items()
#         product_df = api.get_products()
#         spare_parts_df = api.get_spare_parts()
#         warehouses_df = api.get_warehouses()
#         orders_df = api.get_orders()
#     except Exception as e:
#         st.error(f"API load error: {e}")
#         empty = pd.DataFrame()
#         return empty, empty, empty, empty, empty, empty

#     # Ensure datetime fields are parsed
#     if not orders_df.empty:
#         if "order_date" in orders_df.columns:
#             orders_df["order_date"] = pd.to_datetime(
#                 orders_df["order_date"], errors="coerce"
#             )
#         if "estimated_delivery" in orders_df.columns:
#             orders_df["estimated_delivery"] = pd.to_datetime(
#                 orders_df["estimated_delivery"], errors="coerce"
#             )

#     # In case order_items also has order_date
#     if not order_items_df.empty and "order_date" in order_items_df.columns:
#         order_items_df["order_date"] = pd.to_datetime(
#             order_items_df["order_date"], errors="coerce"
#         )

#     return (
#         inventory_df,
#         order_items_df,
#         product_df,
#         spare_parts_df,
#         warehouses_df,
#         orders_df,
#     )


# class WarehouseChatbot:
#     """
#     AI-powered chatbot for answering warehouse inventory and order questions
#     using LangChain and Groq.
#     """

#     def __init__(self, api_key: str | None = None):
#         """
#         Initialize the chatbot with Groq via LangChain.

#         Args:
#             api_key: Groq API key (or set GROQ_API_KEY env var)
#         """
#         self.api_key = api_key or os.getenv("GROQ_API_KEY")
#         if not self.api_key:
#             raise ValueError(
#                 "GROQ_API_KEY is not set. "
#                 "Set it in environment or pass api_key to WarehouseChatbot."
#             )

#         self.llm = ChatGroq(
#             model="llama-3.3-70b-versatile",
#             groq_api_key=self.api_key,
#             temperature=0.3,
#             max_tokens=1024,
#         )

#         # Conversation history
#         self.chat_history: List[Dict[str, str]] = []

#         # Load data
#         (
#             self.inventory_df,
#             self.order_items_df,
#             self.product_df,
#             self.spare_parts_df,
#             self.warehouses_df,
#             self.orders_df,
#         ) = load_warehouse_data()

#         # Build merged orders + order_items table
#         self.order_data = self._build_order_data()

#     # -----------------------------
#     # Internal helpers
#     # -----------------------------
#     def _build_order_data(self) -> pd.DataFrame:
#         """
#         Merge orders (header) and order_items (line items) using order_id.

#         Ensures a 1-to-many relationship and removes duplicated columns
#         that were copied into order_items when it was created.

#         Returns:
#             DataFrame with combined order + line-item fields.
#         """
#         if (
#             self.orders_df is None
#             or self.orders_df.empty
#             or self.order_items_df is None
#             or self.order_items_df.empty
#         ):
#             return pd.DataFrame()

#         # Use unique orders by order_id
#         orders = self.orders_df.drop_duplicates(subset=["order_id"])

#         # Remove any header-level columns accidentally duplicated in order_items
#         duplicate_cols = [
#             c for c in self.order_items_df.columns if c in orders.columns and c != "order_id"
#         ]
#         clean_items = self.order_items_df.drop(columns=duplicate_cols, errors="ignore")

#         try:
#             merged = clean_items.merge(
#                 orders,
#                 how="left",
#                 on="order_id",
#                 validate="many_to-one",  # enforce 1 order -> many items
#             )
#             return merged
#         except Exception as e:
#             # Fallback to line items only if merge fails
#             print(f"[WarehouseChatbot] Error merging orders and order_items: {e}")
#             return clean_items

#     # -----------------------------
#     # Context builders
#     # -----------------------------
#     def get_data_context(self) -> str:
#         """
#         Generate a high-level summary of available data
#         to give the model structure and grounding.
#         """
#         context_parts: list[str] = []

#         # Orders header summary
#         if self.orders_df is not None and not self.orders_df.empty:
#             total_orders = len(self.orders_df)
#             date_min = (
#                 self.orders_df["order_date"].min().date()
#                 if "order_date" in self.orders_df.columns
#                 and pd.api.types.is_datetime64_any_dtype(self.orders_df["order_date"])
#                 else "unknown"
#             )
#             date_max = (
#                 self.orders_df["order_date"].max().date()
#                 if "order_date" in self.orders_df.columns
#                 and pd.api.types.is_datetime64_any_dtype(self.orders_df["order_date"])
#                 else "unknown"
#             )
#             total_revenue = (
#                 float(self.orders_df["order_total"].sum())
#                 if "order_total" in self.orders_df.columns
#                 else 0.0
#             )
#             unique_customers = (
#                 self.orders_df["customer_id"].nunique()
#                 if "customer_id" in self.orders_df.columns
#                 else 0
#             )
#             context_parts.append(
#                 f"Orders: {total_orders} total orders "
#                 f"(from {date_min} to {date_max}), "
#                 f"total revenue ≈ ${total_revenue:,.2f}, "
#                 f"{unique_customers} unique customers."
#             )

#             if "assigned_warehouse_id" in self.orders_df.columns:
#                 warehouses_count = self.orders_df["assigned_warehouse_id"].nunique()
#                 context_parts.append(
#                     f"Orders shipped from {warehouses_count} warehouses "
#                     f"(field: assigned_warehouse_id)."
#                 )

#         # Order items summary
#         if self.order_items_df is not None and not self.order_items_df.empty:
#             total_lines = len(self.order_items_df)
#             unique_skus = (
#                 self.order_items_df["sku"].nunique()
#                 if "sku" in self.order_items_df.columns
#                 else 0
#             )
#             context_parts.append(
#                 f"Order Items: {total_lines} line items across all orders, "
#                 f"{unique_skus} unique SKUs."
#             )

#             if "sku" in self.order_items_df.columns and "qty" in self.order_items_df.columns:
#                 top_skus = (
#                     self.order_items_df.groupby("sku")["qty"]
#                     .sum()
#                     .nlargest(5)
#                 )
#                 top_sku_str = ", ".join(
#                     f"{sku} ({qty} units)"
#                     for sku, qty in top_skus.to_dict().items()
#                 )
#                 context_parts.append(
#                     f"Top 5 SKUs by ordered quantity: {top_sku_str}."
#                 )

#         # Inventory summary (if present)
#         if self.inventory_df is not None and not self.inventory_df.empty:
#             context_parts.append(
#                 f"Inventory: {len(self.inventory_df)} inventory records "
#                 f"across {self.inventory_df['warehouse_id'].nunique() if 'warehouse_id' in self.inventory_df.columns else '?'} warehouses."
#             )

#         return "\n".join(context_parts)

#     # -----------------------------
#     # Query analysis
#     # -----------------------------
#     def analyze_query(self, query: str) -> Dict[str, Any]:
#         """
#         Analyze the query to extract relevant data slices and simple stats.

#         Returns:
#             {
#               'query_type': str | None,
#               'data': Any (often a list/dict for the model),
#               'insights': List[str]
#             }
#         """
#         analysis: Dict[str, Any] = {
#             "query_type": None,
#             "data": None,
#             "insights": [],
#         }

#         q = query.lower()

#         # Helper flags
#         mentions_orders = any(w in q for w in ["order", "orders", "sales"])
#         mentions_inventory = any(w in q for w in ["inventory", "stock", "quantity", "on hand"])
#         mentions_sku = "sku" in q or "product" in q
#         mentions_customer = "customer" in q
#         mentions_warehouse = "warehouse" in q
#         mentions_revenue = any(w in q for w in ["revenue", "order value", "aov", "sales $"])
#         mentions_status = any(w in q for w in ["status", "shipped", "pending", "open", "late"])

#         # 1) Order-level questions
#         if mentions_orders and not mentions_inventory and self.orders_df is not None:
#             analysis["query_type"] = "orders"

#             # Recent 20 orders for context
#             orders_sorted = self.orders_df.copy()
#             if "order_date" in orders_sorted.columns:
#                 orders_sorted = orders_sorted.sort_values("order_date")

#             analysis["data"] = orders_sorted.tail(20).to_dict("records")
#             analysis["insights"].append(f"Total orders: {len(self.orders_df)}")

#             if "order_total" in self.orders_df.columns:
#                 total_rev = float(self.orders_df["order_total"].sum())
#                 avg_order = float(self.orders_df["order_total"].mean())
#                 analysis["insights"].append(
#                     f"Total revenue ≈ ${total_rev:,.2f}, "
#                     f"average order value ≈ ${avg_order:,.2f}."
#                 )

#         # 2) Inventory / stock questions
#         elif mentions_inventory and self.inventory_df is not None:
#             analysis["query_type"] = "inventory"
#             # Only pass a small sample to the model
#             analysis["data"] = self.inventory_df.head(50).to_dict("records")
#             analysis["insights"].append(
#                 f"Tracking {len(self.inventory_df)} inventory records."
#             )

#         # 3) SKU-specific questions
#         elif mentions_sku and self.order_data is not None and not self.order_data.empty:
#             analysis["query_type"] = "sku"

#             # Try to extract an explicit SKU token like 'SKU-1234'
#             sku_candidate = None
#             for word in query.replace(",", " ").split():
#                 if "sku" in word.lower():
#                     # Allow things like SKU-123, sku_123, etc.
#                     sku_candidate = word.strip(" ,.;:()[]{}").upper()
#                     break

#             if sku_candidate and "sku" in self.order_data.columns:
#                 sku_orders = self.order_data[self.order_data["sku"].str.upper() == sku_candidate]
#                 if not sku_orders.empty:
#                     total_qty = int(sku_orders["qty"].sum()) if "qty" in sku_orders.columns else None
#                     order_count = sku_orders["order_id"].nunique()
#                     recent_rows = (
#                         sku_orders.sort_values("order_date").tail(10).to_dict("records")
#                         if "order_date" in sku_orders.columns
#                         else sku_orders.tail(10).to_dict("records")
#                     )

#                     analysis["data"] = {
#                         "sku": sku_candidate,
#                         "total_quantity": total_qty,
#                         "order_count": order_count,
#                         "recent_orders": recent_rows,
#                     }
#                     if total_qty is not None:
#                         analysis["insights"].append(
#                             f"SKU {sku_candidate}: {total_qty} units ordered across {order_count} orders."
#                         )

#         # 4) Customer-focused questions
#         elif mentions_customer and self.orders_df is not None and not self.orders_df.empty:
#             analysis["query_type"] = "customer"

#             if "customer_id" in self.orders_df.columns:
#                 top_customers = (
#                     self.orders_df.groupby("customer_id")["order_total"]
#                     .sum()
#                     .nlargest(5)
#                     .to_dict()
#                 )
#                 analysis["data"] = {"top_customers": top_customers}
#                 analysis["insights"].append(
#                     f"Top customers by revenue (customer_id): "
#                     + ", ".join(f"{cid} (${val:,.2f})" for cid, val in top_customers.items())
#                 )

#         # 5) Warehouse-focused questions
#         elif mentions_warehouse and self.orders_df is not None and not self.orders_df.empty:
#             analysis["query_type"] = "warehouse"

#             if "assigned_warehouse_id" in self.orders_df.columns:
#                 by_wh = (
#                     self.orders_df.groupby("assigned_warehouse_id")["order_total"]
#                     .sum()
#                     .sort_values(ascending=False)
#                     .to_dict()
#                 )
#                 analysis["data"] = {"warehouse_revenue": by_wh}
#                 analysis["insights"].append(
#                     "Orders grouped by assigned_warehouse_id with total revenue."
#                 )

#         # 6) Revenue / value questions
#         elif mentions_revenue and self.orders_df is not None and not self.orders_df.empty:
#             analysis["query_type"] = "revenue"

#             if "order_date" in self.orders_df.columns and "order_total" in self.orders_df.columns:
#                 by_day = (
#                     self.orders_df
#                     .set_index("order_date")["order_total"]
#                     .resample("D").sum().tail(30)
#                 )
#                 analysis["data"] = by_day.reset_index().to_dict("records")
#                 analysis["insights"].append(
#                     "Last 30 days revenue by day based on order_total."
#                 )

#         # 7) Status / fulfillment questions
#         elif mentions_status and self.orders_df is not None and not self.orders_df.empty:
#             analysis["query_type"] = "status"

#             if "status" in self.orders_df.columns:
#                 status_counts = self.orders_df["status"].value_counts().to_dict()
#                 analysis["data"] = {"status_counts": status_counts}
#                 analysis["insights"].append(
#                     "Order status distribution: "
#                     + ", ".join(f"{s}: {c}" for s, c in status_counts.items())
#                 )

#         # Fallback: provide a small sample of merged order_data if available
#         elif self.order_data is not None and not self.order_data.empty:
#             analysis["query_type"] = "mixed"
#             analysis["data"] = self.order_data.head(30).to_dict("records")
#             analysis["insights"].append(
#                 "Provided a mixed sample of orders + order_items for flexible reasoning."
#             )

#         return analysis

#     # -----------------------------
#     # Chat interface
#     # -----------------------------
#     def chat(self, user_message: str) -> str:
#         """
#         Process a user message and return an AI response.

#         Args:
#             user_message: The user's question.

#         Returns:
#             AI-generated response string.
#         """
#         # Analyze the query for structure and data slices
#         analysis = self.analyze_query(user_message)

#         # Build system prompt with warehouse context
#         system_prompt = f"""You are an intelligent warehouse management assistant.
# You help warehouse managers understand their inventory, orders, customers,
# and warehouse operations.

# You have access to:
# - Order header data (orders table) including order_id, order_date, customer info,
#   order_total, items (list of SKUs), assigned_warehouse_id, status, payment_method,
#   shipping_address, estimated_delivery.
# - Order line-item data (order_items table) with one row per SKU per order.
# - A merged view `order_data` that combines order headers and line-items via order_id.
# - Inventory records by SKU and warehouse.

# Warehouse Data Summary:
# {self.get_data_context()}

# Query Analysis:
# Type: {analysis['query_type']}
# Insights: {', '.join(analysis['insights']) if analysis['insights'] else 'None'}

# Guidelines:
# - Be concise but informative.
# - Use specific numbers and time ranges when they are provided in the context.
# - Clearly distinguish between number of orders and number of line items.
# - If the question is about revenue or values, use order_total when available.
# - If exact data is not in the provided snippet, say so and reason approximately.
# - Prefer bullet points for lists or recommendations.
# - You can suggest follow-up analyses or metrics the user might care about.

# Current Query Data (sampled rows or aggregates):
# {str(analysis['data'])[:1200] if analysis['data'] is not None else 'No specific data extracted; answer conceptually based on your warehouse knowledge.'}
# """

#         # Build message chain
#         messages: List[Any] = [SystemMessage(content=system_prompt)]

#         # Add recent chat history for context (last 5 turns)
#         for msg in self.chat_history[-10:]:
#             if msg["role"] == "user":
#                 messages.append(HumanMessage(content=msg["content"]))
#             else:
#                 messages.append(AIMessage(content=msg["content"]))

#         # Add current user message
#         messages.append(HumanMessage(content=user_message))

#         # Get response from Groq LLM
#         response = self.llm.invoke(messages)

#         # Update chat history
#         self.chat_history.append({"role": "user", "content": user_message})
#         self.chat_history.append({"role": "assistant", "content": response.content})

#         return response.content

#     def clear_history(self):
#         """Clear conversation history."""
#         self.chat_history = []


# # Example usage for local testing (non-Streamlit)
# if __name__ == "__main__":
#     chatbot = WarehouseChatbot()  # Requires GROQ_API_KEY env var

#     example_queries = [
#         "How many total orders do we have and what's the total revenue?",
#         "What are our top selling SKUs?",
#         "Which warehouses are handling the most order value?",
#         "Tell me about customer trends.",
#     ]

#     for q in example_queries:
#         print(f"\n🧑 User: {q}")
#         ans = chatbot.chat(q)
#         print(f"🤖 Assistant: {ans}\n")
