# mis.py # MIS widgets for Olist chatbot: compact Customer 360 + Admin Seller Scorecards from __future__ import annotations import logging import sqlite3 from typing import Callable, Optional, Tuple, List import pandas as pd import streamlit as st import matplotlib.pyplot as plt # ------------------------------------------------------------------- # Logging # ------------------------------------------------------------------- logger = logging.getLogger("mis") if not logger.handlers: _h = logging.StreamHandler() _h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | mis | %(message)s")) logger.addHandler(_h) logger.setLevel(logging.INFO) # ------------------------------------------------------------------- # Small helpers # ------------------------------------------------------------------- def _run_df(conn: sqlite3.Connection, sql: str, params: Tuple = ()) -> pd.DataFrame: return pd.read_sql_query(sql, conn, params=params) def _list_tables(conn: sqlite3.Connection) -> List[str]: rows = conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall() return [r[0] for r in rows] def _table_exists(conn: sqlite3.Connection, name: str) -> bool: row = conn.execute( "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (name,) ).fetchone() return row is not None def _is_numeric(x) -> bool: try: float(x) return True except Exception: return False # ------------------------------------------------------------------- # Indexes useful for MIS (created only if table exists) # ------------------------------------------------------------------- def ensure_mis_indexes(get_conn: Callable[[], sqlite3.Connection]) -> None: """Create helpful indexes if they don't exist (idempotent + table-aware).""" try: with get_conn() as conn: tables = set(_list_tables(conn)) def idx(sql: str): try: conn.execute(sql) except Exception as e: logger.debug("ensure_mis_indexes: skipped %s", e) if "orders" in tables: idx("CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)") idx("CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(order_status)") idx("CREATE INDEX IF NOT EXISTS idx_orders_purchase_ts ON orders(order_purchase_timestamp)") idx("CREATE INDEX IF NOT EXISTS idx_orders_delivered_ts ON orders(order_delivered_customer_date)") idx("CREATE UNIQUE INDEX IF NOT EXISTS ux_feedback_chat_user ON feedback(chat_id, user_id)") if "order_items" in tables: idx("CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id)") idx("CREATE INDEX IF NOT EXISTS idx_order_items_seller ON order_items(seller_id)") idx("CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id)") if "order_reviews" in tables: idx("CREATE INDEX IF NOT EXISTS idx_reviews_order ON order_reviews(order_id)") if "order_payments" in tables: idx("CREATE INDEX IF NOT EXISTS idx_payments_order ON order_payments(order_id)") if "customers" in tables: idx("CREATE INDEX IF NOT EXISTS idx_customers_id ON customers(customer_id)") if "product_category_name_translation" in tables: idx("""CREATE INDEX IF NOT EXISTS idx_prod_cat_tx ON product_category_name_translation(product_category_name)""") conn.commit() except Exception as e: logger.warning("ensure_mis_indexes: %s", e) # ------------------------------------------------------------------- # Customer 360 (compact) # ------------------------------------------------------------------- @st.cache_data(show_spinner=False, ttl=300) def _load_customer_360_df(db_path: str, customer_id: str) -> pd.DataFrame: """One-row summary for a customer.""" conn = sqlite3.connect(db_path) try: sql = """ WITH cust_orders AS ( SELECT o.* FROM orders o WHERE o.customer_id = ? ), delivered AS ( SELECT * FROM cust_orders WHERE order_status = 'delivered' ), order_values AS ( SELECT oi.order_id, SUM(oi.price) AS item_value, SUM(oi.freight_value) AS freight_value FROM order_items oi JOIN cust_orders o ON o.order_id = oi.order_id GROUP BY oi.order_id ), delivered_values AS ( SELECT ov.* FROM order_values ov JOIN delivered d ON d.order_id = ov.order_id ) SELECT (SELECT customer_city FROM customers WHERE customer_id = ?) AS city, (SELECT customer_state FROM customers WHERE customer_id = ?) AS state, (SELECT MIN(order_purchase_timestamp) FROM cust_orders) AS first_order_ts, (SELECT COUNT(*) FROM cust_orders) AS total_orders, (SELECT COUNT(*) FROM delivered) AS delivered_orders, (SELECT IFNULL(SUM(item_value),0) FROM order_values) AS lifetime_gmv, (SELECT IFNULL(SUM(freight_value),0) FROM order_values) AS lifetime_freight, ( SELECT CASE WHEN COUNT(*)=0 THEN 0 ELSE ROUND(SUM(item_value)*1.0/COUNT(*),2) END FROM delivered_values ) AS aov_delivered, (SELECT ROUND(julianday('now') - julianday(MAX(order_delivered_customer_date))) FROM delivered) AS recency_days, ( SELECT ROUND( (COUNT(*)*1.0) / ( (julianday('now') - julianday(MIN(order_purchase_timestamp))) / 30.0 ), 2) FROM delivered ) AS frequency_per_month, (SELECT IFNULL(SUM(item_value),0) FROM delivered_values) AS monetary_delivered, ( SELECT ROUND(AVG(julianday(order_delivered_customer_date) - julianday(order_purchase_timestamp)),2) FROM delivered WHERE order_delivered_customer_date IS NOT NULL ) AS avg_delivery_days, ( SELECT ROUND(100.0 * AVG(CASE WHEN order_delivered_customer_date <= order_estimated_delivery_date THEN 1 ELSE 0 END),1) FROM delivered WHERE order_delivered_customer_date IS NOT NULL AND order_estimated_delivery_date IS NOT NULL ) AS pct_ontime, ( SELECT ROUND(AVG(r.review_score),2) FROM order_reviews r JOIN cust_orders o ON o.order_id = r.order_id ) AS avg_review_score, ( SELECT COUNT(*) FROM order_reviews r JOIN cust_orders o ON o.order_id = r.order_id ) AS review_count ; """ return pd.read_sql_query(sql, conn, params=(customer_id, customer_id, customer_id)) finally: conn.close() @st.cache_data(show_spinner=False, ttl=300) def _load_customer_360_slices(db_path: str, customer_id: str) -> Tuple[pd.DataFrame, pd.DataFrame]: """Returns (payment_mix_df, top_categories_df).""" conn = sqlite3.connect(db_path) try: sql_pay = """ SELECT op.payment_type, COUNT(*) AS payments, ROUND(SUM(op.payment_value),2) AS amount FROM order_payments op JOIN orders o ON o.order_id = op.order_id WHERE o.customer_id = ? GROUP BY op.payment_type ORDER BY amount DESC LIMIT 5; """ pay = _run_df(conn, sql_pay, (customer_id,)) # Category GMV (translate category name if translation table exists) tables = set(_list_tables(conn)) if "product_category_name_translation" in tables: sql_cat = """ SELECT COALESCE(t.product_category_name_english, p.product_category_name) AS category, ROUND(SUM(oi.price),2) AS gmv FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id LEFT JOIN product_category_name_translation t ON t.product_category_name = p.product_category_name WHERE o.customer_id = ? AND o.order_status = 'delivered' GROUP BY category ORDER BY gmv DESC LIMIT 5; """ else: sql_cat = """ SELECT p.product_category_name AS category, ROUND(SUM(oi.price),2) AS gmv FROM order_items oi JOIN orders o ON o.order_id = oi.order_id JOIN products p ON p.product_id = oi.product_id WHERE o.customer_id = ? AND o.order_status = 'delivered' GROUP BY category ORDER BY gmv DESC LIMIT 5; """ cat = _run_df(conn, sql_cat, (customer_id,)) return pay, cat finally: conn.close() def render_customer_360(customer_id: str, get_conn: Callable[[], sqlite3.Connection], db_path: Optional[str] = None) -> None: """ Compact Customer 360 (scoped). Two KPI rows + two small plots. """ if not customer_id: st.info("No customer selected for this session.") return # infer db_path for cache if db_path is None: try: with get_conn() as c: db_path = c.execute("PRAGMA database_list;").fetchone()[2] except Exception: db_path = ":memory:" st.subheader("Customer 360 (mini)") try: df = _load_customer_360_df(db_path, customer_id) except Exception as e: logger.exception("Customer360 main fetch failed") st.error("Could not load customer metrics.") st.exception(e) return if df.empty: st.info("No orders found for this customer.") return row = df.iloc[0].to_dict() with st.container(border=True): # Top row KPIs c1, c2, c3, c4 = st.columns(4) with c1: st.metric("Total orders", int(row.get("total_orders") or 0)) with c2: st.metric("Delivered", int(row.get("delivered_orders") or 0)) with c3: st.metric("Lifetime GMV", f"{(row.get('lifetime_gmv') or 0):.2f}") with c4: st.metric("AOV (deliv.)", f"{(row.get('aov_delivered') or 0):.2f}") # Second row compact KPIs d1, d2, d3, d4 = st.columns(4) with d1: st.metric("Avg review", row.get("avg_review_score") or 0) with d2: st.metric("On-time %", f"{row.get('pct_ontime') or 0}%") with d3: st.metric("Avg delivery (days)", row.get("avg_delivery_days") or 0) with d4: st.metric("Recency (days)", row.get("recency_days") or "-") # Footer chips (city/state + since) st.caption( f"**City/State:** {row.get('city') or '-'}, {row.get('state') or '-'}" f" • **Customer since:** {row.get('first_order_ts') or '-'}" ) # Small plots try: pay, cat = _load_customer_360_slices(db_path, customer_id) except Exception as e: logger.warning("Customer360 slices failed: %s", e) pay, cat = pd.DataFrame(), pd.DataFrame() g1, g2 = st.columns(2) with g1: st.caption("Top payment types") if not pay.empty: fig, ax = plt.subplots(figsize=(4, 2.4)) ax.barh(pay["payment_type"].astype(str), pay["amount"].astype(float)) ax.set_xlabel("Amount") ax.set_ylabel("Type") st.pyplot(fig, clear_figure=True, use_container_width=True) else: st.caption("—") with g2: st.caption("Top categories (GMV)") if not cat.empty: fig, ax = plt.subplots(figsize=(4, 2.4)) ax.barh(cat["category"].astype(str), cat["gmv"].astype(float)) ax.set_xlabel("GMV") ax.set_ylabel("Category") st.pyplot(fig, clear_figure=True, use_container_width=True) else: st.caption("—") # ------------------------------------------------------------------- # Seller Scorecards (Admin) - dynamic SQL if some tables are missing # ------------------------------------------------------------------- def _get_orders_span(conn: sqlite3.Connection) -> Tuple[Optional[str], Optional[str]]: row = conn.execute(""" SELECT MIN(order_purchase_timestamp), MAX(order_purchase_timestamp) FROM orders """).fetchone() return (row[0], row[1]) if row else (None, None) import pandas as pd def seller_scorecard_df( conn, seller_id: str | None = None, date_from: str | None = None, # 'YYYY-MM-DD' or full timestamp date_to: str | None = None, delivered_only: bool = True, limit: int = 50, ) -> pd.DataFrame: """ Build a seller scorecard from Olist tables. - date_from/date_to: filter by order_purchase_timestamp (inclusive) - delivered_only: if True, only include delivered orders - seller_id: None -> all sellers; otherwise restrict to that seller """ # Safe defaults if dates are not supplied date_from = date_from or "1900-01-01" date_to = date_to or "2100-01-01" # Inject the delivered-only filter INSIDE the CTE using the correct alias `o` status_filter = " AND o.order_status = 'delivered'" if delivered_only else "" sql = f""" WITH filtered_orders AS ( SELECT o.order_id, o.order_purchase_timestamp, o.order_delivered_customer_date, o.order_estimated_delivery_date, o.order_status FROM orders o WHERE o.order_purchase_timestamp BETWEEN ? AND ?{status_filter} ), items AS ( SELECT oi.order_id, oi.seller_id, oi.price, oi.freight_value FROM order_items oi JOIN filtered_orders fo ON fo.order_id = oi.order_id ), reviews AS ( SELECT order_id, review_score FROM order_reviews ) SELECT i.seller_id, COUNT(DISTINCT i.order_id) AS orders, COUNT(*) AS items, ROUND(SUM(i.price), 2) AS gmv, ROUND(SUM(i.freight_value), 2) AS freight, ROUND(AVG( CASE WHEN fo.order_delivered_customer_date IS NOT NULL THEN JULIANDAY(fo.order_delivered_customer_date) - JULIANDAY(fo.order_purchase_timestamp) END ), 2) AS avg_days_to_deliver, ROUND(AVG(r.review_score), 2) AS avg_review, CASE WHEN SUM(CASE WHEN fo.order_delivered_customer_date IS NOT NULL THEN 1 ELSE 0 END) = 0 THEN NULL ELSE ROUND( 100.0 * SUM( CASE WHEN fo.order_delivered_customer_date IS NOT NULL AND fo.order_delivered_customer_date <= fo.order_estimated_delivery_date THEN 1 ELSE 0 END ) / SUM(CASE WHEN fo.order_delivered_customer_date IS NOT NULL THEN 1 ELSE 0 END) , 1) END AS on_time_pct FROM items i JOIN filtered_orders fo ON fo.order_id = i.order_id LEFT JOIN reviews r ON r.order_id = i.order_id WHERE (? IS NULL OR i.seller_id = ?) GROUP BY i.seller_id ORDER BY gmv DESC LIMIT {int(limit)}; """ params = (date_from, date_to, seller_id, seller_id) return pd.read_sql_query(sql, conn, params=params) @st.cache_data(show_spinner=False, ttl=180) def _load_seller_scorecards(db_path: str, top_n: int = 50) -> pd.DataFrame: conn = sqlite3.connect(db_path) try: tables = set(_list_tables(conn)) if "orders" not in tables or "order_items" not in tables: # These two are mandatory return pd.DataFrame() has_reviews = "order_reviews" in tables # CTE: base seller-orders base_cte = """ WITH seller_orders AS ( SELECT oi.seller_id, o.order_id, o.customer_id, o.order_status, o.order_purchase_timestamp, o.order_delivered_customer_date, o.order_estimated_delivery_date FROM order_items oi JOIN orders o ON o.order_id = oi.order_id ), single_seller_orders AS ( SELECT oi.order_id FROM order_items oi GROUP BY oi.order_id HAVING COUNT(DISTINCT oi.seller_id) = 1 ), agg AS ( SELECT so.seller_id, COUNT(DISTINCT CASE WHEN so.order_status='delivered' THEN so.order_id END) AS delivered_orders, COUNT(*) AS items_sold, ROUND(SUM(CASE WHEN so.order_status='delivered' THEN oi.price ELSE 0 END),2) AS gmv, ROUND(SUM(CASE WHEN so.order_status='delivered' THEN oi.freight_value ELSE 0 END),2) AS freight, COUNT(DISTINCT so.customer_id) AS unique_customers FROM seller_orders so JOIN order_items oi ON oi.order_id=so.order_id AND oi.seller_id=so.seller_id GROUP BY so.seller_id ), aov AS ( SELECT seller_id, CASE WHEN delivered_orders=0 THEN 0 ELSE ROUND(gmv*1.0/delivered_orders,2) END AS aov_delivered FROM agg ), cxl AS ( SELECT seller_id, ROUND(100.0 * SUM(CASE WHEN order_status IN ('canceled','unavailable') THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_canceled FROM seller_orders GROUP BY seller_id ), sla AS ( SELECT so.seller_id, ROUND(AVG(julianday(so.order_delivered_customer_date) - julianday(so.order_purchase_timestamp)),2) AS avg_delivery_days, ROUND(100.0 * AVG( CASE WHEN so.order_delivered_customer_date <= so.order_estimated_delivery_date THEN 1 ELSE 0 END ),1) AS pct_ontime FROM seller_orders so JOIN single_seller_orders sso ON sso.order_id = so.order_id WHERE so.order_status='delivered' AND so.order_delivered_customer_date IS NOT NULL AND so.order_estimated_delivery_date IS NOT NULL GROUP BY so.seller_id ) """ reviews_cte = """ , reviews AS ( SELECT so.seller_id, ROUND(AVG(r.review_score),2) AS avg_review_score, COUNT(*) AS reviews_count FROM seller_orders so JOIN order_reviews r ON r.order_id = so.order_id GROUP BY so.seller_id ) """ # Final SELECT assembling pieces conditionally select_core = """ SELECT a.seller_id, a.delivered_orders, a.items_sold, a.gmv, a.freight, a.unique_customers, av.aov_delivered, {review_cols} c.pct_canceled, s.avg_delivery_days, s.pct_ontime FROM agg a LEFT JOIN aov av ON av.seller_id=a.seller_id {join_reviews} LEFT JOIN cxl c ON c.seller_id=a.seller_id LEFT JOIN sla s ON s.seller_id=a.seller_id ORDER BY a.gmv DESC LIMIT ?; """ if has_reviews: full_sql = ( base_cte + reviews_cte + select_core.format( review_cols="r.avg_review_score, r.reviews_count,", join_reviews="LEFT JOIN reviews r ON r.seller_id=a.seller_id", ) ) else: full_sql = ( base_cte + select_core.format( review_cols="NULL AS avg_review_score, NULL AS reviews_count,", join_reviews="", ) ) return _run_df(conn, full_sql, (top_n,)) finally: conn.close() def render_seller_scorecards(get_conn: Callable[[], sqlite3.Connection], role: str = "admin", db_path: Optional[str] = None, page_size: int = 12) -> None: """Admin-only seller scorecards with quick filters + pagination.""" if role != "admin": st.warning("Seller scorecards are available to admins only.") return # infer DB path for caching if db_path is None: try: with get_conn() as c: db_path = c.execute("PRAGMA database_list;").fetchone()[2] except Exception: db_path = ":memory:" st.subheader("Seller Scorecards") # Filters fc1, fc2, fc3 = st.columns([2, 1, 1]) with fc1: q = st.text_input("Filter seller_id (contains)", "") with fc2: min_deliv = st.number_input("Min delivered orders", min_value=0, value=0, step=10) with fc3: top_n = st.selectbox("Top by GMV", [50, 100, 200], index=0) try: df = _load_seller_scorecards(db_path, top_n=top_n) except Exception as e: logger.exception("Seller scorecards fetch failed") st.error("Could not load seller scorecards.") st.exception(e) return if df.empty: st.info("No sellers to show (check data load).") return if q: df = df[df["seller_id"].astype(str).str.contains(q, case=False, na=False)] if _is_numeric(min_deliv): df = df[df["delivered_orders"] >= int(min_deliv)] if df.empty: st.info("No sellers match the selected filters.") return # Pagination total = len(df) total_pages = max(1, (total + page_size - 1) // page_size) cp1, cp2, _ = st.columns([1, 1, 6]) with cp1: page = st.number_input("Page", min_value=1, max_value=total_pages, value=1) with cp2: st.caption(f"{total} sellers • {total_pages} page(s)") start = (int(page) - 1) * page_size end = start + page_size page_df = df.iloc[start:end].reset_index(drop=True) # Cards grid for _, row in page_df.iterrows(): with st.container(border=True): st.markdown(f"**Seller:** `{row['seller_id']}`") c1, c2, c3 = st.columns(3) with c1: st.metric("GMV", f"{(row['gmv'] or 0):.2f}") st.metric("Delivered orders", int(row.get("delivered_orders") or 0)) st.metric("AOV (deliv.)", f"{(row.get('aov_delivered') or 0):.2f}") with c2: st.metric("Items sold", int(row.get("items_sold") or 0)) st.metric("Unique customers", int(row.get("unique_customers") or 0)) st.metric("Canceled %", f"{row.get('pct_canceled') or 0}%") with c3: st.metric("Avg review", row.get("avg_review_score") or 0) st.metric("On-time %", f"{row.get('pct_ontime') or 0}%") st.metric("Reviews", int(row.get("reviews_count") or 0))