""" Amazon RAG Chatbot — Streamlit UI Run with: streamlit run app.py """ import streamlit as st import pandas as pd from sqlalchemy import create_engine from rag_core import ( LocalRAG, build_clean_views, ensure_llm_up, DB_PATH, LLM_MODEL, HF_TOKEN, ) st.set_page_config( page_title="Amazon Analytics Chatbot", page_icon="📦", layout="wide", initial_sidebar_state="expanded", ) st.markdown(""" """, unsafe_allow_html=True) @st.cache_resource(show_spinner="Initializing RAG engine...") def init_bot(): engine = create_engine(f"sqlite:///{DB_PATH}", echo=False) build_clean_views(engine) bot = LocalRAG(engine) return bot, engine # ── Sidebar ── with st.sidebar: st.markdown("### System Status") llm_ok = ensure_llm_up() if llm_ok: st.markdown(f'

● LLM ({LLM_MODEL.split("/")[-1]}) Online

', unsafe_allow_html=True) else: st.markdown(f'

● LLM ({LLM_MODEL.split("/")[-1]}) Offline

', unsafe_allow_html=True) if not HF_TOKEN: st.warning("RAG mode unavailable.\n\n**HF_TOKEN** is not set. Add it in\nSpace Settings → Secrets.") else: st.warning("RAG mode unavailable.\nLLM API is unreachable. Check your HF_TOKEN or model quota.") st.markdown("
", unsafe_allow_html=True) st.markdown("### Data Coverage") st.markdown(""" - **Orders** — revenue, units, AOV, ASP, profit - **Business Reports** — sessions, page views, buy box, conversion - **Advertising** — spend, impressions, clicks, ACOS, ROAS - **Keepa** — ratings, reviews, sales rank - **Filters** — year, quarter, month, last N days/weeks/months - **Dimensions** — country, city, state, channel, ASIN, SKU """) st.markdown("---") st.markdown("### Example Questions") examples = [ "Total revenue in 2023 Q1", "Monthly sessions trend last 30 days", "Total ad spend in 2024", "2024 H1 B2B revenue by state", "Average buy box percentage", "Top search terms by spend", ] for eq in examples: if st.button(eq, key=f"ex_{eq}", use_container_width=True): st.session_state["pending_question"] = eq st.markdown("---") if st.button("Clear conversation", use_container_width=True, type="secondary"): st.session_state["messages"] = [] st.rerun() st.markdown('

Amazon RAG v3 · SQL + Semantic Search

', unsafe_allow_html=True) # ── Header ── st.markdown("""

📦

Amazon Analytics Chatbot

Ask questions about your data in plain English — powered by SQL & semantic search

""", unsafe_allow_html=True) bot, engine = init_bot() if "messages" not in st.session_state: st.session_state["messages"] = [] # ── Chat history ── if not st.session_state["messages"]: st.markdown("""

💬

Start a conversation

Type a question below or pick an example from the sidebar.
Try: "What was total revenue in 2023?"

""", unsafe_allow_html=True) else: st.markdown('

', unsafe_allow_html=True) for msg in st.session_state["messages"]: if msg["role"] == "user": st.markdown(f'

You

{msg["content"]}

', unsafe_allow_html=True) else: st.markdown(f'

Assistant

{msg["content"]}

', unsafe_allow_html=True) if msg.get("dataframe") is not None: st.dataframe(pd.DataFrame(msg["dataframe"]), use_container_width=True, hide_index=True) st.markdown('

', unsafe_allow_html=True) # ── Input ── pending = st.session_state.pop("pending_question", None) user_input = st.chat_input("Ask about your Amazon data...") question = pending or user_input if question: st.session_state["messages"].append({"role": "user", "content": question}) st.markdown(f'

You

{question}

', unsafe_allow_html=True) with st.spinner("Processing..."): result = bot.answer(question, k=6) mode = result.get("mode", "unknown") response_html = "" css_class = "" df_rows = None if mode == "template_sql": rows = result.get("result", []) response_html = ( f'SQL {result["answer"]}' f'

View SQL query

' f'

{result["sql"]}

' ) if len(rows) > 1: df_rows = rows elif mode == "template_sql_error": css_class = "err" response_html = ( f'SQL Error {result.get("error","Unknown error")}' ) if result.get("sql"): response_html += f'

View SQL

{result["sql"]}

' elif mode == "rag": response_html = f'Semantic {result["answer"]}' else: response_html = f"Unknown mode: {mode}" st.markdown(f'

Assistant

{response_html}

', unsafe_allow_html=True) if df_rows: st.dataframe(pd.DataFrame(df_rows), use_container_width=True, hide_index=True) st.session_state["messages"].append({ "role": "bot", "content": response_html, "css_class": css_class, "dataframe": df_rows, })