from __future__ import annotations try: import pandas as pd # noqa: F401 except Exception: pd = None try: import streamlit as st # noqa: F401 except Exception: pass # --- FIX v2: logger dedupe + run-once guards (26-Sep-2025) --- import os, sys, logging import os as _olist_os import pathlib as _olist_pathlib import logging as _olist_logging from typing import Optional import re # === Auto-Chart Helpers (Admin-only) === def _infer_columns_for_chart(df: pd.DataFrame): """ Return a dict describing the best chart config, or None. Heuristics: - scatter: 2 numeric columns - bar/pie: 1 categorical + 1 numeric (≤ 8 categories -> pie, else bar) - stacked_bar: 2 categoricals + 1 numeric """ try: if df is None or df.empty: return None df2 = df.copy() num_cols = [c for c in df2.columns if pd.api.types.is_numeric_dtype(df2[c])] cat_cols = [c for c in df2.columns if (not pd.api.types.is_numeric_dtype(df2[c])) and df2[c].nunique() > 0] preferred_cats = [c for c in df2.columns if c.lower() in ( "category","categories","product_category","seller_id","product_id","state","city", "payment_type","status","month","date","order_status","review_score" )] preferred_nums = [c for c in df2.columns if c.lower() in ( "count","cnt","total","sum","amount","revenue","value","score","quantity","qty","gmv" )] if len(num_cols) >= 2: return {"kind":"scatter", "x": num_cols[0], "y": num_cols[1]} if cat_cols and num_cols: cat = preferred_cats[0] if preferred_cats and preferred_cats[0] in cat_cols else cat_cols[0] num = preferred_nums[0] if preferred_nums and preferred_nums[0] in num_cols else num_cols[0] top = (df2[[cat, num]] .groupby(cat, dropna=False, as_index=False)[num].sum() .sort_values(num, ascending=False)) ncat = top[cat].nunique() if ncat <= 8 and (top[num] >= 0).all(): return {"kind":"pie", "cat": cat, "val": num, "data": top.head(8)} else: return {"kind":"bar", "x": cat, "y": num, "data": top.head(25)} if len(cat_cols) >= 2 and num_cols: cat1, cat2 = cat_cols[:2] num = num_cols[0] data = (df2[[cat1, cat2, num]] .groupby([cat1, cat2], dropna=False, as_index=False)[num].sum()) return {"kind":"stacked_bar", "x": cat1, "hue": cat2, "y": num, "data": data} return None except Exception: return None def _render_chart_matplotlib(cfg: dict, df: pd.DataFrame): import matplotlib.pyplot as plt try: kind = cfg.get("kind") if kind == "scatter": x, y = cfg["x"], cfg["y"] fig, ax = plt.subplots() ax.scatter(df[x], df[y]) ax.set_xlabel(x); ax.set_ylabel(y); ax.set_title(f"{y} vs {x}") st.pyplot(fig, use_container_width=True) elif kind == "bar": data = cfg.get("data", df) x, y = cfg["x"], cfg["y"] fig, ax = plt.subplots() ax.bar(data[x].astype(str), data[y]) ax.set_xlabel(x); ax.set_ylabel(y); ax.set_title(f"{y} by {x}") ax.tick_params(axis='x', rotation=45) st.pyplot(fig, use_container_width=True) elif kind == "pie": data = cfg.get("data", df) cat, val = cfg["cat"], cfg["val"] fig, ax = plt.subplots() vals = data[val].clip(lower=0) ax.pie(vals, labels=data[cat].astype(str), autopct=lambda p: f"{p:.0f}%") ax.set_title(f"{val} share by {cat}") st.pyplot(fig, use_container_width=True) elif kind == "stacked_bar": data = cfg.get("data", df) x, hue, y = cfg["x"], cfg["hue"], cfg["y"] pivot = data.pivot_table(index=x, columns=hue, values=y, aggfunc="sum", fill_value=0) fig, ax = plt.subplots() bottom = None for col in pivot.columns: vals = pivot[col].values if bottom is None: ax.bar(pivot.index.astype(str), vals, label=str(col)) bottom = vals else: ax.bar(pivot.index.astype(str), vals, bottom=bottom, label=str(col)) bottom = bottom + vals ax.set_xlabel(x); ax.set_ylabel(y); ax.set_title(f"{y} by {x} and {hue}") ax.legend(loc="best", fontsize="small") ax.tick_params(axis='x', rotation=45) st.pyplot(fig, use_container_width=True) else: st.info("No suitable chart found.") except Exception as e: st.warning(f"Could not render chart: {e}") def _call_render_df(df, key_prefix: str, page_size: int = 10): """ Indirectly call the global render_df_with_pagination to avoid any local binding issues. Falls back to st.dataframe if the function isn't available yet in this scope. """ import sys fn = getattr(sys.modules[__name__], "render_df_with_pagination", None) try: if callable(fn): return fn(df, key_prefix, page_size) except Exception: pass try: st.dataframe(df) except Exception: pass # Make matplotlib happy in containers (already helped in your logs) os.environ.setdefault("MPLCONFIGDIR", "/tmp/mplconfig") try: os.makedirs(os.environ["MPLCONFIGDIR"], exist_ok=True) except Exception: pass # Simple per-process once registry __ONCE_FLAGS = set() def run_once(key: str) -> bool: """Return True only the first time a given key is seen in this process.""" if key in __ONCE_FLAGS: return False __ONCE_FLAGS.add(key) return True # Idempotent, non-propagating logger setup with a marker on our handler _FMT = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s") def _ensure_logger(name: Optional[str], level: int = logging.INFO) -> logging.Logger: lg = logging.getLogger(name) if name else logging.getLogger() # If we already have our own handler, don't add another have_marker = any(getattr(h, "_olist_marker", False) for h in lg.handlers) if not have_marker: # Remove any default handlers installed by framework reloaders for h in list(lg.handlers): # Keep other handlers if you really want them; usually best to clear lg.removeHandler(h) h = logging.StreamHandler(sys.stdout) h.setFormatter(_FMT) h._olist_marker = True # marker so we don't re-add lg.addHandler(h) lg.setLevel(level) lg.propagate = False # don't bubble to root and duplicate return lg # Prepare our named loggers exactly once per process if run_once("loggers_configured"): _ensure_logger(None, logging.INFO) # root olist_logger = _ensure_logger("olist", logging.DEBUG) feedback_logger = _ensure_logger("feedback", logging.DEBUG) # Only announce readiness once per process; guards prevent dupes on re-import if run_once("olist_ready"): olist_logger.info("olist logger ready (level=DEBUG)") if run_once("feedback_ready"): feedback_logger.info("feedback logger ready (level=DEBUG)") # --- END FIX v2 --- # Quiet down very chatty third‑party libs unless explicitly opted in if _olist_os.environ.get("OLIST_VERBOSE_LIBS") not in ("1", "true", "True"): for _name in ( "watchdog.observers.inotify_buffer", "PIL.PngImagePlugin", "matplotlib.font_manager", "matplotlib.pyplot", "httpcore.connection", "httpcore.http11", "openai._base_client", ): _olist_logging.getLogger(_name).setLevel(_olist_logging.WARNING) # --- END FIX --- # Heuristic: 'my' + (order|orders|payment|payment type) should go to SQL, not small-talk _MY_ACCOUNT_PAT = re.compile(r"\bmy\b.*\b(order|orders|payment|payment\s*type)\b|\b(order|orders|payment)\b.*\bmy\b", re.IGNORECASE) def _is_my_account_query(text: str) -> bool: try: return bool(_MY_ACCOUNT_PAT.search(text or '')) except Exception as e: return False def _clear_chat_input(_key: str = "user_query"): try: import streamlit as st if _key in st.session_state: st.session_state[_key] = "" except Exception: pass # --- begin: cache hardening helper (safe to add) --- def _ensure_writable_model_caches(): """Force all typical model caches to /data/.cache and patch Chroma's ONNX path.""" import os # put all caches under /data/.cache (writable in your container) os.environ.setdefault("HOME", "/data") os.environ.setdefault("XDG_CACHE_HOME", "/data/.cache") os.environ.setdefault("HF_HOME", "/data/.cache") os.environ.setdefault("HUGGINGFACE_HUB_CACHE", "/data/.cache") os.environ.setdefault("TRANSFORMERS_CACHE", "/data/.cache") os.environ.setdefault("SENTENCE_TRANSFORMERS_HOME", "/data/.cache") try: os.makedirs(os.environ["XDG_CACHE_HOME"], exist_ok=True) except Exception: pass # patch Chroma default ONNX embedder to download into our cache dir try: from chromadb.utils.embedding_functions.onnx_mini_lm_l6_v2 import ONNXMiniLM_L6_V2 onnx_cache = os.path.join(os.environ.get("XDG_CACHE_HOME", "/data/.cache"), "chromadb", "onnx_models") try: os.makedirs(onnx_cache, exist_ok=True) except Exception: pass try: ONNXMiniLM_L6_V2.DOWNLOAD_PATH = onnx_cache except Exception: pass except Exception: # it's fine if this import fails here; OpenAI EF path won't use it pass # --- end: cache hardening helper --- # app.py — Olist Analytics Assistant (LLM→SQL) with Indic (hi/mr) translation # --------------------------------------------------------------------- import os import re import time import hashlib import json # ==== RAG imports (1212) ==== try: import chromadb from chromadb.utils import embedding_functions as cef except Exception: chromadb = None cef = None try: import sqlglot import sqlglot.expressions as sxe except Exception: sqlglot = None import math import bcrypt import sqlite3 import pandas as pd import yaml import streamlit as st import eda from streamlit.runtime.scriptrunner import StopException # ---- Ensure page config is set before any other Streamlit calls ---- try: _pcfg = st.session_state.get("_page_config_set", False) except Exception: _pcfg = False if not _pcfg: try: st.set_page_config(page_title="SmartAssist BOT", layout="wide", initial_sidebar_state="collapsed") except Exception: pass # ignore if already set try: st.session_state["_page_config_set"] = True except Exception: pass # ------------------------------------------------------------------- # --- UI sanitizer for React-safe tables --- def _ui_safe_cell(x): # Convert dict/list/other objects to JSON or string so Streamlit/React won’t choke import json try: if isinstance(x, (dict, list)): return json.dumps(x, ensure_ascii=False) return str(x) except Exception: return str(x) # --- Safe display helper (prevents Streamlit 'function is not iterable' when a DB cursor sneaks in) --- def _as_dataframe_safely(obj): """Return a pandas DataFrame from many possible SQL result shapes. Handles: pandas.DataFrame, sqlite3.Cursor (attr or callable description), list[tuple]. Never raises; falls back to empty DataFrame. """ try: import pandas as pd # Already a DataFrame if isinstance(obj, pd.DataFrame): return obj # sqlite3.Cursor (or lookalike): has .fetchall and .description if hasattr(obj, "fetchall") and hasattr(obj, "description"): try: rows = obj.fetchall() except Exception: rows = [] desc = getattr(obj, "description", None) try: cols = [d[0] for d in (desc() if callable(desc) else (desc or []))] except Exception: cols = None try: return pd.DataFrame(rows, columns=cols) except Exception: return pd.DataFrame(rows) # Plain Python sequence of rows if isinstance(obj, (list, tuple)): try: import pandas as pd return pd.DataFrame(list(obj)) except Exception: pass except Exception: pass # ultimate fallback try: import pandas as pd return pd.DataFrame() except Exception: return obj def _assistant_text_for_transcript(out: dict, default: str = "") -> str: try: if out.get("text"): return out["text"] plan = out.get("plan") or {} if plan.get("explanation_display"): return str(plan.get("explanation_display")) if plan.get("explanation"): return str(plan.get("explanation")) qs = out.get("queries") or [] for q in qs: if q.get("kind") == "non_sql": df = q.get("df") if df is not None: try: import pandas as pd if isinstance(df, pd.DataFrame) and not df.empty and "message" in df.columns: return str(df.iloc[0]["message"]) except Exception: pass return default except Exception: return default def _begin_turn(user_text: str = ""): try: st.session_state['_transcript_done'] = False st.session_state['last_query_text'] = user_text or st.session_state.get('last_query_text') or '' except Exception: pass def _append_transcript_once(user_text: str, out: dict | None = None, explicit_text: str | None = None): try: if st.session_state.get('_transcript_done'): return asst = explicit_text if explicit_text is not None else _assistant_text_for_transcript(out or {}, default="") st.session_state.setdefault('transcript', []) st.session_state['transcript'].append({'user': user_text, 'assistant': asst, 'ts': time.time()}) st.session_state['_transcript_done'] = True except Exception: pass import logging, sys from datetime import datetime, timedelta from contextlib import contextmanager from collections import deque from typing import Optional, Tuple, List, Dict, Any import matplotlib.pyplot as plt from mis import seller_scorecard_df, render_customer_360, render_seller_scorecards, ensure_mis_indexes # ----------------------------- # Global Settings / Flags # ----------------------------- DATA_DIR = "data" SCHEMA_FILE = "olist_schema_registry.yaml" DB_DIR = "/data" # persistent volume DB_PATH = os.path.join(DB_DIR, "olist.sqlite") # --- RAG config --- RAG_BACKEND = os.getenv("RAG_BACKEND", "CHROMA").upper() # CHROMA | TFIDF RAG_DIR = os.getenv("RAG_DIR", "/data/rag_store") RAG_COLLECTION = os.getenv("RAG_COLLECTION", "olist_sql_exemplars") OPENAI_EMBED_MODEL = os.getenv("OPENAI_EMBED_MODEL", "text-embedding-3-small") EMBED_DIM = 1536 def _schema_fingerprint(registry: dict) -> str: try: parts = [] for t in sorted(registry.keys()): cols = ",".join(sorted(registry[t]["columns"].keys())) parts.append(f"{t}({cols})") return ";".join(parts) except Exception: return "unknown" class RAGStore: def __init__(self): self.backend = RAG_BACKEND self.chroma = None self.col = None # Lazy init def _ensure(self): if self.backend == "CHROMA": if chromadb is None: raise RuntimeError("chromadb not installed") os.makedirs(RAG_DIR, exist_ok=True) # Ensure model caches are writable to avoid '/.cache' permission errors os.environ.setdefault('HOME', '/data') os.environ.setdefault('XDG_CACHE_HOME', '/data/.cache') os.environ.setdefault('HF_HOME', '/data/.cache') os.environ.setdefault('HUGGINGFACE_HUB_CACHE', '/data/.cache') os.environ.setdefault('TRANSFORMERS_CACHE', '/data/.cache') os.environ.setdefault('SENTENCE_TRANSFORMERS_HOME', '/data/.cache') try: os.makedirs(os.environ['XDG_CACHE_HOME'], exist_ok=True) except Exception: pass client = chromadb.PersistentClient(path=RAG_DIR) # Use OpenAI embeddings if available, else default (may be slow) ef = None try: if cef is not None and os.getenv("OPENAI_API_KEY"): ef = cef.OpenAIEmbeddingFunction(api_key=os.getenv("OPENAI_API_KEY"), model_name=OPENAI_EMBED_MODEL) except Exception: ef = None self.col = client.get_or_create_collection(RAG_COLLECTION, embedding_function=ef) self.chroma = client else: # Simple in-memory TFIDF fallback from collections import defaultdict self.docs = {} # id -> text self.meta = {} # id -> dict self._tf = defaultdict(dict) def upsert_exemplar(self, ex_id: str, text: str, metadata: dict): self._ensure() if self.backend == "CHROMA": self.col.upsert(ids=[ex_id], documents=[text], metadatas=[metadata]) else: # naive store; search is linear cosine over TfidfVectorizer on demand self.docs[ex_id] = text self.meta[ex_id] = metadata def query(self, text: str, k: int = 3, where: dict | None = None): self._ensure() if self.backend == "CHROMA": where = where or {} res = self.col.query(query_texts=[text], n_results=k, where=where) out = [] for i in range(len(res.get("ids",[[]])[0])): out.append({ "id": res["ids"][0][i], "text": res["documents"][0][i], "metadata": res["metadatas"][0][i] }) return out else: # simple cosine via scikit-learn if available; else return best substring matches try: from sklearn.feature_extraction.text import TfidfVectorizer from sklearn.metrics.pairwise import cosine_similarity ids = list(self.docs.keys()) docs = [self.docs[i] for i in ids] vec = TfidfVectorizer().fit_transform([text] + docs) sims = cosine_similarity(vec[0:1], vec[1:]).ravel() order = sims.argsort()[::-1][:k] return [{"id": ids[i], "text": docs[i], "metadata": self.meta[ids[i]]} for i in order] except Exception: # fallback: naive substring rank items = [(i, self.docs[i]) for i in self.docs] items.sort(key=lambda p: p[1].count(text[:10]), reverse=True) return [{"id": i, "text": t, "metadata": self.meta[i]} for i,t in items[:k]] RAG = RAGStore() def canonicalize_sql(sql: str) -> str: try: if sqlglot is None: return sql.strip() return sqlglot.transpile(sql, read="sqlite", write="sqlite")[0] except Exception: return sql.strip() def exemplar_text(intent: str, question: str, schema_fp: str, plan: str, sql: str) -> str: plan_short = (plan or "").strip() if len(plan_short) > 400: plan_short = plan_short[:400] + "…" sql_canon = canonicalize_sql(sql or "") return f"[INTENT]={intent}\nQUESTION={question}\nSCHEMA={schema_fp}\nPLAN={plan_short}\nSQL={sql_canon}" LLM_CHOICE = os.getenv("LLM_CHOICE", "OPENAI") REVENUE_USER_MODE = os.getenv("REVENUE_USER_MODE", "WARN") SELLER_REVENUE_USER_MODE = os.getenv("SELLER_REVENUE_USER_MODE", "BLOCK") PRODUCT_REVENUE_USER_MODE = os.getenv("PRODUCT_REVENUE_USER_MODE", "BLOCK") PAYMENTS_USER_MODE = os.getenv("PAYMENTS_USER_MODE", "SCOPE").upper() # Feedback enforcement mode: OPTIONAL | SOFT_REQUIRED | HARD_REQUIRED FEEDBACK_MODE = os.getenv("FEEDBACK_MODE", "SOFT_REQUIRED").upper() DB_TIMEOUT = int(os.getenv("DB_TIMEOUT", "30")) BUSY_TIMEOUT_MS = int(os.getenv("BUSY_TIMEOUT_MS", "5000")) SQL_TIMEOUT = float(os.getenv("SQL_TIMEOUT", "30")) # default 30s NON_SQL_ROUTER = os.getenv("NON_SQL_ROUTER", "ON").upper() == "ON" # --- Tracing & timeouts --- TRACE_LOGGING = os.getenv("TRACE_LOGGING", "1") == "1" SQL_HARD_TIMEOUT_SEC = float(os.getenv("SQL_HARD_TIMEOUT_SEC", "15")) SQL_VM_BUDGET = int(os.getenv("SQL_VM_BUDGET", "4000000")) SQL_PROGRESS_NSTEPS = int(os.getenv("SQL_PROGRESS_NSTEPS", "1000")) # --- Rate limiting (role-aware) --- RATE_LIMIT_WINDOW_SEC = int(os.getenv("RATE_LIMIT_WINDOW_SEC", "60")) RATE_LIMIT_MAX_REQUESTS_USER = int(os.getenv("RATE_LIMIT_MAX_REQUESTS_USER", "8")) RATE_LIMIT_MAX_REQUESTS_ADMIN = int(os.getenv("RATE_LIMIT_MAX_REQUESTS_ADMIN", "30")) # --- Visibility toggle for detailed sections --- DISPLAY_DETAIL = os.getenv("DISPLAY_DETAIL", "YES").strip().upper() SHOW_DETAILS = (DISPLAY_DETAIL != "NO") # True unless explicitly set to NO # UI brand flag USE_BRANDING = True DEBUG_UI = os.getenv("DEBUG_UI", "0") == "1" LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO").upper() def setup_logging(): # Root logger -> stdout once root = logging.getLogger() if not root.handlers: h = logging.StreamHandler(sys.stdout) h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")) root.addHandler(h) root.setLevel(getattr(logging, LOG_LEVEL, logging.INFO)) # Quiet very chatty libs logging.getLogger("httpx").setLevel(logging.WARNING) logging.getLogger("urllib3").setLevel(logging.WARNING) return root root_logger = setup_logging() def _ensure_logger(name: str, level=logging.INFO): lg = logging.getLogger(name) lg.setLevel(level) # Attach its own handler to guarantee visibility (and avoid relying on propagation) if not lg.handlers: h = logging.StreamHandler(sys.stdout) h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")) lg.addHandler(h) # When a named logger has its own handler, set propagate False to avoid duplicates lg.propagate = False return lg logger = _ensure_logger("olist", getattr(logging, LOG_LEVEL, logging.INFO)) fb_logger = _ensure_logger("feedback", getattr(logging, LOG_LEVEL, logging.INFO)) logger.info("olist logger ready (level=%s)", LOG_LEVEL) fb_logger.info("feedback logger ready (level=%s)", LOG_LEVEL) def dbg(msg: str): if DEBUG_UI: st.sidebar.write(f"🧪 {msg}") # ---- Hugging Face cache fix ---- HF_CACHE_BASE = os.getenv("HF_CACHE_BASE", "/data/hf-cache") def _init_hf_cache(): import pathlib, time, os base = pathlib.Path(HF_CACHE_BASE) hub = base / "hub" trsf = base / "transformers" for d in (base, hub, trsf): d.mkdir(parents=True, exist_ok=True) # ensure transformers uses /data (not /.cache) os.environ.setdefault("HF_HOME", str(base)) os.environ.setdefault("HF_HUB_CACHE", str(hub)) os.environ.setdefault("TRANSFORMERS_CACHE", str(trsf)) os.environ.setdefault("HF_HUB_DISABLE_SYMLINKS_WARNING", "1") # Optional: clear stale lock files (>20 min old) now = time.time() for root, _, files in os.walk(str(base)): for f in files: if f.endswith(".lock"): p = os.path.join(root, f) try: if now - os.path.getmtime(p) > 1200: # 20 min os.remove(p) except Exception: pass _init_hf_cache() # ----------------------------- # Scoped UI helpers (safe & minimal) # ----------------------------- def _inject_brand_css(): """Loads assets/brand.css if present; otherwise falls back to embedded styles.""" import pathlib css_path = pathlib.Path("assets/brand.css") if css_path.exists(): st.markdown(f"", unsafe_allow_html=True) else: st.markdown(""" """, unsafe_allow_html=True) def ui_header(auth=None): user_chip = "" if auth: who = auth.get("email","") role = (auth.get("role") or "").title() user_chip = f'{who} • {role}' st.markdown(f"""
SmartAssist BOT
AI powered Bot
Enterprise Secure {user_chip}
""", unsafe_allow_html=True) def card_open(title=None, right=None): st.markdown('
', unsafe_allow_html=True) if title or right: st.markdown('
', unsafe_allow_html=True) if title: st.markdown(f'
{title}
', unsafe_allow_html=True) if right: st.markdown(f'
{right}
', unsafe_allow_html=True) st.markdown('
', unsafe_allow_html=True) def card_close(): st.markdown('
', unsafe_allow_html=True) if USE_BRANDING: _inject_brand_css() # ----------------------------- # Timing helper # ----------------------------- @contextmanager def timed_step(label: str, trace_id: str = ""): t0 = time.perf_counter() if TRACE_LOGGING: logger.info("[trace=%s] ▶ %s: start", trace_id, label) try: yield finally: dt = time.perf_counter() - t0 if TRACE_LOGGING: logger.info("[trace=%s] ◀ %s: %.3fs", trace_id, label, dt) # ----------------------------- # ----------------------------- # Weekly Popular Queries (Top-K) # ----------------------------- import hashlib import datetime as dt import re from datetime import timezone def _normalize_query_for_bucket(text: str) -> str: if not text: return "" t = text.lower().strip() t = re.sub(r"\d+", "", t) t = re.sub(r"[^a-z0-9 ]+", " ", t) t = re.sub(r"\s+", " ", t).strip() return t def ensure_weekly_popular_schema(conn: sqlite3.Connection): conn.execute(""" CREATE TABLE IF NOT EXISTS weekly_popular_queries ( week_start_utc TEXT NOT NULL, bucket_key TEXT NOT NULL, display_text TEXT NOT NULL, intent TEXT, cnt INTEGER NOT NULL, plan_blob TEXT, PRIMARY KEY (week_start_utc, bucket_key) ); """) conn.execute("CREATE INDEX IF NOT EXISTS idx_wpq_week_cnt ON weekly_popular_queries(week_start_utc, cnt DESC);") try: conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_created_at ON chat_history(created_at);") except Exception: pass try: conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_role ON chat_history(role);") except Exception: pass try: conn.execute("CREATE INDEX IF NOT EXISTS idx_chat_history_intent ON chat_history(intent);") except Exception: pass conn.commit() def _ist_week_start_anchor_utc(now: dt.datetime | None = None) -> str: if now is None: now = dt.datetime.now() ist = dt.timezone(dt.timedelta(hours=5, minutes=30)) n_ist = now.astimezone(ist) days_back = n_ist.weekday() start_ist = (n_ist - dt.timedelta(days=days_back)).replace(hour=0, minute=0, second=0, microsecond=0) start_utc = start_ist.astimezone(dt.timezone.utc) return start_utc.replace(tzinfo=dt.timezone.utc).isoformat() def _fetch_current_week_topk_live(conn: sqlite3.Connection, k: int = 5) -> list[dict]: week_anchor = _ist_week_start_anchor_utc() rows = conn.execute(""" SELECT user_input, intent, created_at FROM chat_history WHERE role='user' AND created_at >= ? """, (week_anchor,)).fetchall() buckets = {} for user_input, intent, _ts in rows: if not user_input: continue if intent and intent.lower() in {"greeting","thanks","help","smalltalk","complaint"}: continue norm = _normalize_query_for_bucket(user_input) key = f"{(intent or 'other').strip()} \u241f {norm}" d = buckets.get(key) if d is None: buckets[key] = {"bucket_key": key, "intent": intent or "other", "norm_q": norm, "display_text": user_input, "cnt": 1} else: d["cnt"] += 1 top = sorted(buckets.values(), key=lambda x: (-x["cnt"], x["intent"], x["norm_q"]))[:k] for t in top: t["week_start_utc"] = week_anchor return top def _materialize_weekly_topk(conn: sqlite3.Connection, k: int = 5) -> list[dict]: ensure_weekly_popular_schema(conn) top = _fetch_current_week_topk_live(conn, k=k) if not top: return [] week_anchor = top[0]["week_start_utc"] for row in top: conn.execute(""" INSERT INTO weekly_popular_queries (week_start_utc, bucket_key, display_text, intent, cnt, plan_blob) VALUES (?, ?, ?, ?, ?, COALESCE(?, plan_blob)) ON CONFLICT(week_start_utc, bucket_key) DO UPDATE SET display_text=excluded.display_text, intent=excluded.intent, cnt=excluded.cnt """, (week_anchor, row["bucket_key"], row["display_text"], row["intent"], int(row["cnt"]), None)) conn.commit() return top def _load_weekly_topk(conn: sqlite3.Connection, k: int = 5) -> list[dict]: ensure_weekly_popular_schema(conn) week_anchor = _ist_week_start_anchor_utc() cur = conn.execute(""" SELECT display_text, intent, cnt FROM weekly_popular_queries WHERE week_start_utc = ? ORDER BY cnt DESC LIMIT ? """, (week_anchor, int(k))) rows = [{"display_text": r[0], "intent": r[1], "cnt": int(r[2])} for r in cur.fetchall()] return rows def show_weekly_topk_chips(conn: sqlite3.Connection, k: int = 5, key_prefix: str = "wkpopular"): try: lst = _load_weekly_topk(conn, k=k) if not lst: lst = _fetch_current_week_topk_live(conn, k=k) lst = [{"display_text": r["display_text"], "intent": r["intent"], "cnt": r["cnt"]} for r in lst] except Exception as e: lst = [] if isinstance(e, StopException): raise # don't swallow st.stop() logger.exception("Unhandled exception in : %s", e) if not lst: return with st.container(): st.markdown( """ """, unsafe_allow_html=True ) st.markdown('
Top Enquiries this week
', unsafe_allow_html=True) num = len(lst) cols = st.columns(num, gap="small") for i, it in enumerate(lst): label = f"{it['display_text']} ({it['cnt']})" if cols[i].button(label, key=f"{key_prefix}_{i}", use_container_width=True): st.session_state["q_enabled"] = it["display_text"] # DB helpers / initialization # ----------------------------- def get_conn(): conn = sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) conn.execute("PRAGMA journal_mode=WAL;") conn.execute("PRAGMA synchronous=NORMAL;") conn.execute(f"PRAGMA busy_timeout={BUSY_TIMEOUT_MS};") return conn REQUIRED_TABLES = { "orders", "order_items", "order_payments", "order_reviews", "customers", "products", "sellers", "geolocation", "product_category_name_translation", } def _apply_busy_timeout(conn): try: conn.execute(f"PRAGMA busy_timeout={int(SQL_TIMEOUT * 1000)}") except Exception: pass return conn def _list_tables(conn) -> set: rows = conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall() return {r[0] for r in rows} def ensure_chat_history_schema(conn: sqlite3.Connection): conn.execute(""" CREATE TABLE IF NOT EXISTS chat_history ( chat_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, role TEXT NOT NULL, customer_id TEXT, user_input TEXT NOT NULL, llm_plan JSON, secured_sql JSON, sentiment TEXT, intent TEXT, llm_response_time REAL, feedback INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); """) conn.commit() def ensure_kv_store_schema(conn: sqlite3.Connection): conn.execute(""" CREATE TABLE IF NOT EXISTS kv_store ( k TEXT PRIMARY KEY, v TEXT ); """) conn.commit() def ensure_login_audit_schema(conn: sqlite3.Connection): conn.execute(""" CREATE TABLE IF NOT EXISTS login_audit ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, email TEXT, success INTEGER NOT NULL, reason TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); """) conn.execute("CREATE INDEX IF NOT EXISTS idx_login_audit_created_at ON login_audit(created_at);") conn.execute("CREATE INDEX IF NOT EXISTS idx_login_audit_user ON login_audit(user_id);") conn.commit() def ensure_performance_indexes(conn: sqlite3.Connection) -> list[str]: stmts = [ "CREATE INDEX IF NOT EXISTS idx_orders_order_id ON orders(order_id);", "CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);", "CREATE INDEX IF NOT EXISTS idx_orders_customer_order ON orders(customer_id, order_id);", "CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);", "CREATE INDEX IF NOT EXISTS idx_order_payments_order ON order_payments(order_id);", "CREATE INDEX IF NOT EXISTS idx_order_reviews_order ON order_reviews(order_id);", "CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id);", "CREATE INDEX IF NOT EXISTS idx_products_product ON products(product_id);", "CREATE INDEX IF NOT EXISTS idx_sellers_seller ON sellers(seller_id);", "CREATE INDEX IF NOT EXISTS idx_login_audit_created ON login_audit(created_at);" ] executed = [] for s in stmts: conn.execute(s); executed.append(s) conn.execute("ANALYZE;") conn.commit() return executed def _col_exists(conn, table, col) -> bool: cur = conn.execute(f"PRAGMA table_info({table})") return any(r[1] == col for r in cur.fetchall()) def _chat_history_pk_name(conn) -> str | None: """Return the primary key column name for chat_history ('id' or 'chat_id').""" cols = [r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()] if "id" in cols: return "id" if "chat_id" in cols: return "chat_id" return None def ensure_feedback_cols(conn): # make sure chat_history exists conn.execute(""" CREATE TABLE IF NOT EXISTS chat_history ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT, role TEXT, conversation_id TEXT, input_text TEXT, response_text TEXT, sentiment TEXT, intent TEXT, secured_sql TEXT, llm_plan TEXT, llm_time REAL, sql_time REAL, created_at TEXT )""") def has(col: str) -> bool: return any(r[1] == col for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()) changed = False if not has("feedback_value"): conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_value INTEGER"); changed = True if not has("feedback_text"): conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_text TEXT"); changed = True if not has("feedback_at"): conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_at TEXT"); changed = True if changed: conn.commit() def has_feedback_for_conversation(get_conn, *, user_id: int, conversation_id: str) -> bool: with get_conn() as conn: cur = conn.execute( """ SELECT 1 FROM chat_history WHERE user_id = ? AND conversation_id = ? AND feedback_value IS NOT NULL LIMIT 1 """, (user_id, conversation_id), ) row = cur.fetchone() fb_logger.debug("has_feedback_for_conversation user=%s cid=%s -> %s", user_id, conversation_id, bool(row)) return bool(row) # at top SCHEMA_VERSION = 1 # bump when you add migrations def _set_pragmas(conn: sqlite3.Connection) -> None: # All PRAGMAs must be literals (no ? bindings) conn.execute("PRAGMA journal_mode=WAL;") conn.execute("PRAGMA synchronous=NORMAL;") conn.execute(f"PRAGMA busy_timeout={int(SQL_TIMEOUT*1000)};") conn.execute("PRAGMA temp_store=MEMORY;") def initialize_db(quiet: bool = True) -> dict: os.makedirs(DB_DIR, exist_ok=True) created = not os.path.exists(DB_PATH) actions = [] with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: _set_pragmas(conn) row = conn.execute("PRAGMA user_version;").fetchone() prior_version = int(row[0]) if row and row[0] is not None else 0 ensure_chat_history_schema(conn) ensure_login_audit_schema(conn) ensure_performance_indexes(conn) ensure_feedback_cols(conn) if prior_version != SCHEMA_VERSION: conn.execute(f"PRAGMA user_version={int(SCHEMA_VERSION)};") actions.append(f"set_user_version:{SCHEMA_VERSION}") conn.commit() status = { "created": created, "prior_version": prior_version, "new_version": SCHEMA_VERSION, "actions": actions, "db_path": DB_PATH, } if not quiet: st.success(f"DB initialized (user_version {prior_version} → {SCHEMA_VERSION})") return status @st.cache_resource(show_spinner = False) def bootstrap_db_once() -> dict: return initialize_db(quiet = True) # Call it after functions are defined: _init_status = bootstrap_db_once() # Log only once per browser session (not on every rerun) if "init_logged" not in st.session_state: logger.info("[init] bootstrap_db_once => %s", _init_status) st.session_state.init_logged = True def initialize_db_old (quiet: bool = True) -> bool: """ Create/upgrade DB if needed. Returns True if rebuild/migration happened. Never writes to Streamlit UI; use logging instead. """ os.makedirs(DB_DIR, exist_ok=True) rebuilt = False # Fast existence & schema check need_create = not os.path.exists(DB_PATH) try: with sqlite3.connect(DB_PATH) as c: c.execute("PRAGMA journal_mode=WAL;") c.execute("PRAGMA busy_timeout = 5000;") # Store schema version in user_version cur = c.execute("PRAGMA user_version;").fetchone() current_ver = int(cur[0]) if cur and cur[0] is not None else 0 except Exception as e: need_create = True current_ver = 0 if need_create: # your current CSV → tables loader goes here... # ensure_chat_history_schema(c), ensure_login_audit_schema(c), etc. with sqlite3.connect(DB_PATH) as c: c.execute("PRAGMA user_version = ?", (SCHEMA_VERSION,)) rebuilt = True logger.info("[init] fresh database created") else: # migrations if schema version is older if current_ver < SCHEMA_VERSION: with sqlite3.connect(DB_PATH) as c: # run your ALTERs / ensures here ensure_chat_history_schema(c) ensure_login_audit_schema(c) ensure_performance_indexes(c) ensure_feedback_cols(c) c.execute("PRAGMA user_version = ?", (SCHEMA_VERSION,)) c.commit() rebuilt = True logger.info("[init] migrated database from v%s to v%s", current_ver, SCHEMA_VERSION) else: # still ensure additive bits without noise with sqlite3.connect(DB_PATH) as c: ensure_chat_history_schema(c) ensure_login_audit_schema(c) ensure_performance_indexes(c) ensure_feedback_cols(c) c.commit() return rebuilt # --- Admin data reload helpers --------------------------------------------- LOAD_ORDER = [ # parents → children (recommended load order) "geolocation", "customers", "sellers", "products", "product_category_name_translation", "orders", "order_items", "order_payments", "order_reviews", ] def get_table_csv_map() -> dict: return { "orders": f"{DATA_DIR}/olist_orders_dataset.csv", "order_items": f"{DATA_DIR}/olist_order_items_dataset.csv", "order_payments": f"{DATA_DIR}/olist_order_payments_dataset.csv", "order_reviews": f"{DATA_DIR}/olist_order_reviews_dataset.csv", "customers": f"{DATA_DIR}/olist_customers_dataset.csv", "products": f"{DATA_DIR}/olist_products_dataset.csv", "sellers": f"{DATA_DIR}/olist_sellers_dataset.csv", "geolocation": f"{DATA_DIR}/olist_geolocation_dataset.csv", "product_category_name_translation": f"{DATA_DIR}/product_category_name_translation.csv", } def _sorted_by_load_order(tables: list[str]) -> list[str]: order_index = {t: i for i, t in enumerate(LOAD_ORDER)} return sorted(tables, key=lambda t: order_index.get(t, 999)) def get_row_counts(conn: sqlite3.Connection, tables: list[str]) -> dict[str, int]: out = {} for t in tables: try: out[t] = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] except Exception as e: out[t] = None if isinstance(e, StopException): raise # don't swallow st.stop() logger.exception("Unhandled exception in : %s", e) return out def repopulate_tables_from_csv(tables: list[str], logger=None) -> tuple[bool, str, dict]: """ DELETE FROM selected tables, then reload from CSVs. Returns (ok, message, post_counts). """ if not tables: return False, "No tables selected.", {} csv_map = get_table_csv_map() missing = [t for t in tables if t not in csv_map] if missing: return False, f"No CSV mapping for: {', '.join(missing)}", {} tables = _sorted_by_load_order(tables) # Use a generous timeout in case of big loads timeout = 60.0 if 'SQL_TIMEOUT' not in globals() else SQL_TIMEOUT try: with sqlite3.connect(DB_PATH, timeout=timeout) as conn: conn.execute("PRAGMA journal_mode=WAL;") conn.execute("PRAGMA synchronous=NORMAL;") conn.execute("PRAGMA foreign_keys=OFF;") try: for t in tables: path = csv_map[t] if not os.path.exists(path): raise FileNotFoundError(f"CSV not found: {path}") if logger: logger.info("[reload] %s: deleting old rows", t) conn.execute(f"DELETE FROM {t};") conn.commit() if logger: logger.info("[reload] %s: loading from %s", t, path) # Chunked load to keep memory reasonable for chunk in pd.read_csv(path, chunksize=50000): chunk.to_sql(t, conn, if_exists="append", index=False) conn.commit() finally: conn.execute("PRAGMA foreign_keys=ON;") post = get_row_counts(conn, tables) return True, "Repopulation completed.", post except Exception as e: if logger: logger.exception("[reload] failed") return False, f"Repopulation failed: {e}", {} #--- Admin data reload helpers --------------------------------------------- def load_schema_registry(): if not os.path.exists(SCHEMA_FILE): raise FileNotFoundError(f"Missing schema file: {SCHEMA_FILE}") with open(SCHEMA_FILE, "r") as f: return yaml.safe_load(f) # --- Feedback confirmation persistence helper --- def _feedback_confirmed(chat_id: int) -> bool: try: import streamlit as st return bool(st.session_state.get(f"FB_CONFIRMED_{chat_id}")) except Exception: return False def _set_feedback_confirmed(chat_id: int, val: bool = True) -> None: try: import streamlit as st st.session_state[f"FB_CONFIRMED_{chat_id}"] = val except Exception: pass def render_feedback_controls(chat_id: int, key_ns: str | None = None, src_lang: str = "en"): import streamlit as st if not chat_id: return # --- unique namespace for widget keys (prevents DuplicateWidgetID) --- try: if key_ns is None: st.session_state.setdefault("__fb_ns_counter__", 0) st.session_state["__fb_ns_counter__"] += 1 key_ns = f"scope{st.session_state['__fb_ns_counter__']}" except Exception: if key_ns is None: key_ns = "scope0" # --- fetch any prior feedback (1 = up, 0 = down) --- try: existing = get_chat_feedback(chat_id) except Exception: existing = None # fail open: still allow user to click; we'll log fallback # session flag to persist “submitted” state across reruns submitted_flag = f"fb_submitted_{chat_id}" already_submitted = bool(st.session_state.get(submitted_flag)) or (existing is not None) st.caption("Was this response helpful?") # show persistent confirmation (if we had previous feedback or this session submitted) if existing is not None: st.info("Feedback recorded: " + ("👍 Helpful" if existing == 1 else "👎 Not helpful")) elif already_submitted: st.success("Thanks for the feedback! Recorded.") # If we already have feedback, don’t draw interactive controls again if already_submitted: return # --- use a form so the click & side effects occur together cleanly --- form_key = f"fb_form_{key_ns}_{chat_id}" with st.form(form_key, clear_on_submit=True): c1, c2 = st.columns(2) with c1: up_clicked = st.form_submit_button("👍 Yes", use_container_width=False) with c2: dn_clicked = st.form_submit_button("👎 No", use_container_width=False) if up_clicked or dn_clicked: val = 1 if up_clicked else 0 try: # call whichever saver exists in your codebase if "insert_chat_feedback" in globals(): insert_chat_feedback(chat_id, val) elif "save_chat_feedback" in globals(): save_chat_feedback(chat_id, val) elif "upsert_chat_feedback" in globals(): upsert_chat_feedback(chat_id, val) else: # fallback to logging so we don’t crash in dev if "fb_logger" in globals(): fb_logger.info(f"[ui] feedback chat_id={chat_id} value={val}") st.session_state[submitted_flag] = True st.success("Thanks for the feedback! Recorded as " + ("👍 Helpful." if val == 1 else "👎 Not helpful.")) # Let Streamlit re-run naturally; no st.rerun() needed here. except Exception as e: st.error("Could not save feedback.") try: st.exception(e) except Exception: pass def insert_chat_history(user_id, role, customer_id, user_input, llm_plan, secured_sql, sentiment, intent, llm_time) -> int: with sqlite3.connect(DB_PATH) as conn: cur = conn.execute(""" INSERT INTO chat_history (user_id, role, customer_id, user_input, llm_plan, secured_sql, sentiment, intent, llm_response_time, feedback) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NULL) """, ( user_id, role, customer_id, user_input, json.dumps(llm_plan), json.dumps(secured_sql), sentiment, intent, llm_time )) chat_id = cur.lastrowid conn.commit() return int(chat_id) @st.cache_data(ttl=30, show_spinner=False) def get_recent_chats_for_user(user_id: str, limit: int = 100) -> pd.DataFrame: with get_conn() as conn: cols = {r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()} # Detect PK and key columns pk = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None) created = "created_at" if "created_at" in cols else ( "ts" if "ts" in cols else None ) inp_col = "user_input" if "user_input" in cols else ( "input_text" if "input_text" in cols else None ) secured = "secured_sql" if "secured_sql" in cols else None llm_plan = "llm_plan" if "llm_plan" in cols else None feedback = "feedback_value" if "feedback_value" in cols else ( "feedback" if "feedback" in cols else None ) if not pk: raise RuntimeError("chat_history has no id/chat_id column") # Build SELECT list, substituting NULL for missing columns select_parts = [f"{pk} AS chat_id"] select_parts += [f"{created} AS created_at"] if created else ["NULL AS created_at"] select_parts += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"] select_parts += [f"{secured} AS secured_sql"] if secured else ["NULL AS secured_sql"] select_parts += [f"{llm_plan} AS llm_plan"] if llm_plan else ["NULL AS llm_plan"] select_parts += [f"{feedback} AS feedback"] if feedback else ["NULL AS feedback"] order_clause = f" ORDER BY datetime({created}) DESC" if created else "" sql = f""" SELECT {", ".join(select_parts)} FROM chat_history WHERE user_id = ? {order_clause} LIMIT ? """ return pd.read_sql_query(sql, conn, params=(user_id, limit)) @st.cache_data(ttl=30, show_spinner=False) def get_unrated_chats_for_user(user_id: str, limit: int = 100) -> pd.DataFrame: with get_conn() as conn: cols = {r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()} pk = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None) created = "created_at" if "created_at" in cols else None inp_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else None) secured = "secured_sql" if "secured_sql" in cols else None llm_plan = "llm_plan" if "llm_plan" in cols else None fb_col = "feedback_value" if "feedback_value" in cols else ("feedback" if "feedback" in cols else None) if not pk: raise RuntimeError("chat_history has no id/chat_id column") select_parts = [f"{pk} AS chat_id"] select_parts += [f"{created} AS created_at"] if created else ["NULL AS created_at"] select_parts += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"] select_parts += [f"{secured} AS secured_sql"] if secured else ["NULL AS secured_sql"] select_parts += [f"{llm_plan} AS llm_plan"] if llm_plan else ["NULL AS llm_plan"] select_parts += [f"{fb_col} AS feedback"] if fb_col else ["NULL AS feedback"] where_fb = f" AND {fb_col} IS NULL" if fb_col else "" order_clause = f" ORDER BY datetime({created}) DESC" if created else "" sql = f""" SELECT {", ".join(select_parts)} FROM chat_history WHERE user_id = ? {where_fb} {order_clause} LIMIT ? """ return pd.read_sql_query(sql, conn, params=(user_id, limit)) @st.cache_data(ttl=30) def get_recent_chats_admin(limit: int = 200, days: int | None = None, user_id: str | None = None) -> pd.DataFrame: """ Admin view: optionally filter by user_id; otherwise show all. """ sql = """ SELECT chat_id, user_id, role, customer_id, created_at, intent, sentiment, feedback, user_input, llm_response_time FROM chat_history WHERE 1=1 """ params = [] if user_id: sql += " AND user_id = ?" params.append(user_id) if days is not None: since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S") sql += " AND created_at >= ?" params.append(since) sql += " ORDER BY datetime(created_at) DESC LIMIT ?" params.append(int(limit)) with get_conn() as conn: df = pd.read_sql_query(sql, conn, params=params) return df import re def detect_non_sql_intent(q: str): """ Heuristic non‑SQL intent detector that is bilingual: - Checks patterns against the original text. - If the language is not English, also translates to English via _tx_to_en and checks again. Returns one of: "greeting", "thanks", "smalltalk", "complaint", or None. """ try: src = _detect_lang(q) except Exception: src = "en" import streamlit as st st.session_state["src_lang"] = src_lang # current turn st.session_state["last_detected_lang"] = src_lang # optional alias text_variants = [] q0 = (q or "").strip() qlc = q0.lower() text_variants.append(qlc) # Translate non‑English to English for pattern matching if src != "en": try: q_en = _tx_to_en(q0, src) if q_en and isinstance(q_en, str): text_variants.append(q_en.strip().lower()) except Exception as e: logger.debug("detect_non_sql_intent: _tx_to_en failed: %s", e) # Patterns pat_greet = re.compile(r"\b(hi|hello|hey|greetings|namaste|namaskar|नमस्ते|नमस्कार|सलाम|\u0939\u093e\u0932\s*चाल|हालचाल|हाल चाल|आप कैसे हैं|क्या हाल है)\b", re.I) pat_thanks = re.compile(r"\b(thanks|thank you|shukriya|धन्यवाद|शुक्रिया)\b", re.I) pat_smalltalk = re.compile(r"\b(how\s+are\s+you|what\'?s\s+up|how\s+is\s+it\s+going|कैसे\s*हैं|क्या\s*हाल\s*है|हालचाल|हाल\s*चाल)\b", re.I) pat_complaint = re.compile(r"\b(complain|not working|issue|problem|error|फिर\s*से\s*नहीं|काम\s*नहीं|समस्या|त्रुटि)\b", re.I) for tv in text_variants: if not tv: continue if pat_greet.search(tv): logger.info("Matching label is : greeting") return "greeting" if pat_thanks.search(tv): logger.info("Matching label is : thanks") return "thanks" if pat_smalltalk.search(tv): logger.info("Matching label is : smalltalk") return "smalltalk" if pat_complaint.search(tv): logger.info("Matching label is : complaint") return "complaint" return None def classify_destructive_intent_with_llm(llm, query: str) -> bool: # Normalize if the planner context was prefixed (case-insensitive) q = (query or "") m = re.search(r'question:\s*(.*)', q, flags=re.I | re.S) # grab everything after QUESTION: if m: q = m.group(1).strip() sys_msg = ( "You are a strict binary classifier.\n" "Return exactly one token: Destructive or Non-Destructive." ) user_msg = ( "Classify the intent of this user query as either Destructive or Non-Destructive.\n" f'Query: "{q}"\n' "Answer with only the single word.\n" "Examples of Destructive intent are:\n" "- delete data\n" "- delete my data\n" "- erase data\n" "- erase my data\n" "- erase data from table\n" "- drop data\n" "- drop table\n" "- modify data\n" "- update data\n" "- update table\n" "- cancel my order\n" "- void the order" ) try: out = llm.chat([ {"role": "system", "content": sys_msg}, {"role": "user", "content": user_msg}, ]) # Be robust to different client return types if isinstance(out, dict): out = out.get("content") or out.get("text") or "" label = (out or "").strip().splitlines()[-1].strip().lower().strip(" .:>") # Tolerant parsing if "non-destructive" in label or "non destructive" in label: return False if "destructive" in label and "non" not in label: return True except Exception as e: if isinstance(e, StopException): raise # don't swallow st.stop() logger.exception("Unhandled exception in : %s", e) pass # Fallback if the LLM path fails or is ambiguous return _intent_destructive(query) def _intent_destructive(text: str) -> bool: logger.info("inside _intent_destructive -> Text received is: %s", text) t = text.lower() # If the string contains our planner context, keep only the user question if 'question:' in t: t = t.split('question:', 1)[1] logger.info("inside _intent_destructive -> value of t is : %s", t) t = re.sub(r'\s+', ' ', t).strip() logger.info("inside _intent_destructive -> Final value of t is : %s", t) logger.info("inside _intent_destructive -> returning value : %s", bool(_DESTRUCTIVE_PAT.search(t))) return bool(_DESTRUCTIVE_PAT.search(t)) def respond_non_sql(label: str, src_lang: str | None = "en") -> str: msgs = { "greeting": ("Hi! 👋 I can answer questions about your Olist data. " "Try: “list my last 10 orders” or “top categories by GMV”."), "thanks": "You’re welcome! If you need examples, ask “help”.", "smalltalk":"I’m here for analytics on the Olist dataset. " "Ask me to list, aggregate, compare, or trend your data.", "complaint":"Sorry to hear that. I’m an analytics bot and don’t have access to customer support systems. " "I can help you check your order history, delivery dates, or reviews. For service issues, " "please contact support.", "help": ("I can generate SQL and answer questions like:\n" "• last 10 orders\n• payments for my orders in July\n• sellers with highest GMV\n" "• delivery time trend by month\n• top product categories for me\n"), } en = msgs.get(label, msgs["help"]) # If you have translation helpers in your code: try: if src_lang and src_lang != "en": return _tx_from_en(en, src_lang) # your existing translator except Exception: pass return en def classify_sentiment_and_intent(llm, text: str): CANONICAL_INTENTS = [ "list_sellers","list_products","my_orders","my_payments", "seller_revenue","product_revenue","shipment_status", "reviews_summary","top_categories","help_greeting","other" ] examples = [ {"input":"pls list all sellers available on the platform","sentiment":"Neutral","intent":"list_sellers"}, {"input":"Need the list of sellers located in state SP","sentiment":"Neutral","intent":"list_sellers"}, {"input":"show all my payments in 2017","sentiment":"Neutral","intent":"my_payments"}, {"input":"track my last order delivery status","sentiment":"Neutral","intent":"shipment_status"}, {"input":"hello, what can you do?","sentiment":"Positive","intent":"help_greeting"}, ] sys = f"""You are a strict classifier. Choose intent from this enum only: {CANONICAL_INTENTS} If a request lists sellers (even filtered by state/city), use "list_sellers". Return ONLY JSON.""" out = llm.chat([ {"role":"system","content":sys}, {"role":"user","content":json.dumps({"task":"classify","text":text,"examples":examples})} ]) try: parsed = json.loads(out) except Exception: return "Neutral","other" sentiment = parsed.get("sentiment","Neutral") intent = parsed.get("intent","other") if sentiment not in ("Positive","Negative","Neutral"): sentiment = "Neutral" if intent not in CANONICAL_INTENTS: intent = "other" return sentiment, intent # ----------------------------- # Auth helpers / Simple login # ----------------------------- def hash_password(plain: str) -> str: return bcrypt.hashpw(plain.encode("utf-8"), bcrypt.gensalt()).decode("utf-8") def verify_password(plain: str, hashed: str) -> bool: try: return bcrypt.checkpw(plain.encode("utf-8"), hashed.encode("utf-8")) except Exception: return False def get_user_by_email(email: str): q = "SELECT user_id, email, password_hash, customer_id, role FROM users WHERE email = ?" with sqlite3.connect(DB_PATH) as conn: row = conn.execute(q, (email,)).fetchone() if not row: return None keys = ["user_id", "email", "password_hash", "customer_id", "role"] return dict(zip(keys, row)) def create_user(email: str, plain_pw: str, customer_id: str, role: str = "user"): hashed = hash_password(plain_pw) user_id = f"U_{customer_id[:8]}" if customer_id else f"A_{int(time.time())}" with sqlite3.connect(DB_PATH) as conn: conn.execute(""" INSERT OR REPLACE INTO users (user_id, email, password_hash, customer_id, role) VALUES (?, ?, ?, ?, ?) """, (user_id, email, hashed, customer_id, role)) conn.commit() def log_login_attempt(email: str | None, user_id: str | None, success: bool, reason: str | None): try: with sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) as conn: conn.execute( "INSERT INTO login_audit (user_id, email, success, reason) VALUES (?, ?, ?, ?)", (user_id, (email or "").strip() or None, 1 if success else 0, reason or None) ) conn.commit() except Exception as e: logger.warning("login audit write failed: %s", e) if isinstance(e, StopException): raise # don't swallow st.stop() logger.exception("Unhandled exception in : %s", e) def simple_login_screen(): """Simple login with inline CAPTCHA; styled by branding banner.""" import random if "captcha_simple" not in st.session_state: st.session_state.captcha_simple = (random.randint(10, 99), random.randint(1, 9)) a, b = st.session_state.captcha_simple st.subheader("Sign in") with st.form("login_simple", clear_on_submit=False): email = st.text_input("Email", placeholder="you@company.com") pwd = st.text_input("Password", type="password", placeholder="••••••••") cap = st.text_input(f"Human check: {a} + {b} = ?") submit = st.form_submit_button("Sign in", use_container_width=True) if submit: try: if int((cap or "").strip()) != (a + b): log_login_attempt(email, None, False, "captcha_failed") st.error("CAPTCHA failed. Please try again.") st.session_state.captcha_simple = None return None except Exception: log_login_attempt(email, None, False, "captcha_invalid") st.error("Please answer the CAPTCHA.") return None user = get_user_by_email((email or "").strip()) if not user: log_login_attempt(email, None, False, "no_such_user") st.error("No such user.") return None if not verify_password(pwd or "", user["password_hash"]): log_login_attempt(email, user["user_id"], False, "wrong_password") st.error("Incorrect password.") return None log_login_attempt(email, user["user_id"], True, "success") st.success("Signed in successfully.") return { "user_id": user["user_id"], "email": user["email"], "role": user["role"], "customer_id": user.get("customer_id"), "remember": False, "ts": time.time() } st.info("Enter your email/password above to sign in.") return None # ----------------------------- # LLM plan normalization / SQL extraction (provider-aware) # ----------------------------- import json as _jsonmod, re as _re_mod _SQL_FENCE_RE = _re_mod.compile(r"```(?:sql)?\s*(.*?)```", _re_mod.I | _re_mod.S) _FIRST_SQL_RE = _re_mod.compile(r"(?is)\b(SELECT|WITH)\b.+?;") def _normalize_sql_statement(stmt: str) -> str: if not stmt: return "" s = stmt.strip() if not s.endswith(";"): s += ";" return s def _extract_sql_from_text(raw: str) -> str | None: """Extract first plausible SQL statement from raw text (fenced or free).""" if not raw: return None s = raw.strip() # fenced blocks = _SQL_FENCE_RE.findall(s) if blocks: for b in blocks: bb = (b or "").strip() if _re_mod.match(r"(?is)^\s*(select|with)\b", bb): return _normalize_sql_statement(bb) # free-text first SQL-ish m = _FIRST_SQL_RE.search(s) if m: semi = s.find(";", m.start()) stmt = s[m.start(): semi+1] if semi != -1 else s[m.start():].strip() + ";" return _normalize_sql_statement(stmt) return None def _unwrap_sql_from_json(raw: str) -> str | None: """If raw is a JSON object with 'queries', return the first query string.""" try: obj = _jsonmod.loads(raw) except Exception: return None q = obj.get("queries") if isinstance(q, list) and q and isinstance(q[0], str): return _normalize_sql_statement(q[0]) if isinstance(q, str) and q.strip(): return _normalize_sql_statement(q) return None def _maybe_extract_sql(sql: str) -> str: """Accepts raw provider output or plain SQL and returns a clean SQL string. Handles Anthropic-style JSON/text, fenced code, or plain SELECT/WITH. """ if not isinstance(sql, str): return sql s = sql.strip() # JSON case if s.startswith("{"): unwrapped = _unwrap_sql_from_json(s) if unwrapped: return unwrapped # Fenced / free text if not _re_mod.match(r"(?is)^\s*(select|with|pragma|explain)\b", s): extracted = _extract_sql_from_text(s) if extracted: return extracted # As-is (ensure semicolon for selects) return _normalize_sql_statement(s) if _re_mod.match(r"(?is)^\s*(select|with)\b", s) else s # Provider-specific plan parsers (adapter pattern) — optional use where you build 'plan' class Plan(dict): pass def parse_llm_openai(raw: str) -> Plan: p = Plan(queries=[], explanation="", explanation_display="", raw=(raw or "")) s = (raw or "").strip() if s.startswith("{"): try: obj = _jsonmod.loads(s) q = obj.get("queries") or [] p["queries"] = q if isinstance(q, list) else [q] p["explanation"] = obj.get("explanation","") p["explanation_display"] = obj.get("explanation_display", p["explanation"]) return p except Exception as e: if isinstance(e, StopException): raise # don't swallow st.stop() logger.exception("Unhandled exception in : %s", e) pass # fallback via extractor maybe = _maybe_extract_sql(s) if maybe and _re_mod.match(r"(?is)^\s*(select|with)\b", maybe): p["queries"] = [maybe] p["explanation"] = s p["explanation_display"] = p["explanation"] return p def parse_llm_anthropic(raw: str) -> Plan: p = Plan(queries=[], explanation="", explanation_display="", raw=(raw or "")) s = (raw or "").strip() if s.startswith("{"): u = _unwrap_sql_from_json(s) if u: p["queries"] = [u] try: obj = _jsonmod.loads(s) p["explanation"] = obj.get("explanation","") p["explanation_display"] = obj.get("explanation_display", p["explanation"]) except Exception: p["explanation"] = s p["explanation_display"] = s return p # fences / free text u = _extract_sql_from_text(s) if u: p["queries"] = [u] p["explanation"] = _SQL_FENCE_RE.sub("", s).strip() if _SQL_FENCE_RE.search(s) else s p["explanation_display"] = p["explanation"] return p def parse_llm_plan(raw: str, provider: str | None = None) -> Plan: prov = (provider or "").lower() if "anthropic" in prov: return parse_llm_anthropic(raw) return parse_llm_openai(raw) # ----------------------------- # SQL execution (timeouts) # ----------------------------- def _looks_like_select(sql: str) -> bool: s = "\n".join([ln for ln in sql.strip().splitlines() if not ln.strip().startswith("--")]).lstrip() head = s[:7].upper() return head.startswith("SELECT") or head.startswith("EXPLAIN") or head.startswith("PRAGMA") class SqlTimeoutError(Exception): pass def run_sql_generic(sql_or_tuple): # Accept "sql" or ("sql", params) if isinstance(sql_or_tuple, (tuple, list)): sql = sql_or_tuple[0] params = sql_or_tuple[1] if len(sql_or_tuple) > 1 else None else: sql = sql_or_tuple params = None # --- Robust SQL extraction from provider output (JSON/fenced) --- if isinstance(sql, str): sql = _maybe_extract_sql(sql) start = time.perf_counter() is_select = _looks_like_select(sql) conn = sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) try: conn.execute("PRAGMA journal_mode=WAL;") conn.execute("PRAGMA synchronous=NORMAL;") conn.execute(f"PRAGMA busy_timeout={BUSY_TIMEOUT_MS};") vm_steps = 0 def _progress(): nonlocal vm_steps vm_steps += SQL_PROGRESS_NSTEPS if (time.perf_counter() - start) > SQL_HARD_TIMEOUT_SEC: return 1 if vm_steps >= SQL_VM_BUDGET: return 1 return 0 conn.set_progress_handler(_progress, SQL_PROGRESS_NSTEPS) if is_select: if TRACE_LOGGING and logger.isEnabledFor(logging.DEBUG): logger.debug("[SQL] %s", sql) df = pd.read_sql_query(sql, conn, params=params) if TRACE_LOGGING: logger.info("[SQL] select rows=%s (elapsed=%.3fs)", len(df), time.perf_counter() - start) return df, None, "select" else: if TRACE_LOGGING and logger.isEnabledFor(logging.DEBUG): logger.debug("[SQL] %s", sql) if params: cur = conn.execute(sql, params) else: cur = conn.execute(sql) conn.commit() rows = cur.rowcount if cur.rowcount != -1 else None if TRACE_LOGGING: logger.info("[SQL] exec rows=%s (elapsed=%.3fs)", rows, time.perf_counter() - start) return None, rows, "exec" except sqlite3.OperationalError as e: if "interrupted" in str(e).lower(): raise SqlTimeoutError( f"SQL timed out after ~{SQL_HARD_TIMEOUT_SEC}s or exceeded VM budget {SQL_VM_BUDGET}." ) from e raise finally: try: conn.set_progress_handler(None, 0) except Exception: pass conn.close() def run_sql_generic_old (sql_or_tuple): """ Execute SQL safely with timeouts & progress budget. Accepts either: - sql: str - (sql, params): tuple[str, Sequence[Any]] Returns: (df, rows, kind) where kind is "select" or "exec". """ # Unpack (sql, params) or just sql if isinstance(sql_or_tuple, (tuple, list)): sql = sql_or_tuple[0] if len(sql_or_tuple) > 0 else "" params = sql_or_tuple[1] if len(sql_or_tuple) > 1 else None else: sql = sql_or_tuple params = None if not isinstance(sql, str): raise TypeError("SQL must be a string or (sql, params) tuple.") start = time.perf_counter() is_select = _looks_like_select(sql) conn = sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) try: # Pragmas tuned for WAL mode + decent performance conn.execute("PRAGMA journal_mode=WAL;") conn.execute("PRAGMA synchronous=NORMAL;") conn.execute(f"PRAGMA busy_timeout={BUSY_TIMEOUT_MS};") # Progress/timeout guard vm_steps = 0 def _progress(): nonlocal vm_steps vm_steps += SQL_PROGRESS_NSTEPS # Hard time limit if (time.perf_counter() - start) > SQL_HARD_TIMEOUT_SEC: return 1 # VM steps budget if vm_steps >= SQL_VM_BUDGET: return 1 return 0 conn.set_progress_handler(_progress, SQL_PROGRESS_NSTEPS) # Logging (trace) if TRACE_LOGGING and logger.isEnabledFor(logging.DEBUG): if params is not None: logger.debug("[SQL] %s | params=%s", sql, params) else: logger.debug("[SQL] %s", sql) if is_select: # pandas will bind params if provided if params is not None: df = pd.read_sql_query(sql, conn, params=params) else: df = pd.read_sql_query(sql, conn) if TRACE_LOGGING: logger.info("[SQL] select rows=%s (elapsed=%.3fs)", len(df), time.perf_counter() - start) return df, None, "select" else: # Exec with optional params if params is not None: cur = conn.execute(sql, params) else: cur = conn.execute(sql) conn.commit() rows = cur.rowcount if cur.rowcount != -1 else None if TRACE_LOGGING: logger.info("[SQL] exec rows=%s (elapsed=%.3fs)", rows, time.perf_counter() - start) return None, rows, "exec" except sqlite3.OperationalError as e: # Convert SQLite interruption into our timeout error if "interrupted" in str(e).lower(): raise SqlTimeoutError( f"SQL timed out after ~{SQL_HARD_TIMEOUT_SEC}s or exceeded VM budget {SQL_VM_BUDGET}." ) from e raise except sqlite3.ProgrammingError as e: # Helpful hint when bindings don't match if "Incorrect number of bindings" in str(e): if TRACE_LOGGING: logger.error("[SQL] binding mismatch for: %s | params=%s", sql, params) raise finally: try: conn.set_progress_handler(None, 0) except Exception: pass conn.close() # ==== EDA helpers ================================================ from datetime import datetime import math import numpy as np # Known keys for Olist tables (used for duplicate detection) OLIST_KEYS = { "orders": ["order_id"], "order_items": ["order_id", "order_item_id"], "order_payments": ["order_id", "payment_sequential"], "order_reviews": ["review_id"], "customers": ["customer_id"], "products": ["product_id"], "sellers": ["seller_id"], "product_category_name_translation": ["product_category_name"], # geolocation has no natural key; duplicates are expected } # Big-table guard rails EDA_ROW_CAP_FOR_FULLROW_DUP = 200_000 # skip full-row dup on very large tables EDA_SAMPLE_LIMIT_NUMERIC_HIST = 100_000 # cap numeric sample for histogram EDA_TOPK_CATEGORICAL = 10 # default for top-K categories def _list_tables(conn) -> list: cur = conn.execute(""" SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY 1; """) return [r[0] for r in cur.fetchall()] def _get_cols(conn, table: str) -> list: cur = conn.execute(f"PRAGMA table_info({table})") return [r[1] for r in cur.fetchall()] def _get_declared_types(conn, table: str) -> dict: cur = conn.execute(f"PRAGMA table_info({table})") # returns: cid, name, type, notnull, dflt_value, pk return {r[1]: (r[2] or "").upper() for r in cur.fetchall()} def _affinity(declared: str, colname: str) -> str: """Rough SQLite affinity detection with a pragmatic nudge by column name.""" t = (declared or "").upper() cn = (colname or "").lower() # Numeric-ish if any(k in t for k in ["INT", "REAL", "FLOA", "DOUB", "NUMERIC", "DEC"]): return "num" # Date-ish by declared type if any(k in t for k in ["DATE", "TIME"]): return "date" # Heuristic by name if any(k in cn for k in ["date", "timestamp", "time_"]): return "date" # Default text return "text" def _count_rows(conn, table: str) -> int: return conn.execute(f"SELECT COUNT(*) FROM {table}").fetchone()[0] def _count_nulls_per_col(conn, table: str, cols: list) -> dict: expr = ", ".join([f"SUM(CASE WHEN {c} IS NULL THEN 1 ELSE 0 END) AS {c}" for c in cols]) row = conn.execute(f"SELECT {expr} FROM {table}").fetchone() return {cols[i]: (row[i] or 0) for i in range(len(cols))} def _count_empty_strings_text(conn, table: str, cols: list, affinities: dict) -> dict: out = {} for c in cols: if affinities.get(c) == "text": out[c] = conn.execute(f"SELECT COUNT(*) FROM {table} WHERE TRIM({c}) = ''").fetchone()[0] else: out[c] = 0 return out def _count_distinct_per_col(conn, table: str, cols: list) -> dict: out = {} for c in cols: try: out[c] = conn.execute(f"SELECT COUNT(DISTINCT {c}) FROM {table}").fetchone()[0] except Exception: out[c] = None return out def _min_max_numeric(conn, table: str, col: str): try: mn, mx = conn.execute( f"SELECT MIN(CAST({col} AS REAL)), MAX(CAST({col} AS REAL)) FROM {table} WHERE {col} IS NOT NULL" ).fetchone() return mn, mx except Exception: return None, None def _min_max_datetime(conn, table: str, col: str): """ Try SQLite datetime() conversion; if NULLs, fall back to MIN/MAX text. Works for ISO-8601-ish timestamps in Olist. """ try: mn, mx = conn.execute( f"SELECT MIN(datetime({col})), MAX(datetime({col})) FROM {table} WHERE {col} IS NOT NULL" ).fetchone() if mn is None and mx is None: mn, mx = conn.execute( f"SELECT MIN({col}), MAX({col}) FROM {table} WHERE {col} IS NOT NULL" ).fetchone() return mn, mx except Exception: return None, None def _count_duplicates_by_keys(conn, table: str, key_cols: list) -> int: keys = ", ".join(key_cols) sql = f""" SELECT COALESCE(SUM(cnt - 1), 0) AS dup_count FROM ( SELECT COUNT(*) AS cnt FROM {table} GROUP BY {keys} HAVING COUNT(*) > 1 ) t; """ return conn.execute(sql).fetchone()[0] or 0 def _count_fullrow_duplicates(conn, table: str, cols: list, total_rows: int) -> int | None: if total_rows > EDA_ROW_CAP_FOR_FULLROW_DUP: return None parts = [f"IFNULL(CAST({c} AS TEXT),'∅')" for c in cols] concat = "|| '¦' ||".join(parts) sql = f""" SELECT (COUNT(*) - COUNT(DISTINCT {concat})) AS dup_all FROM {table}; """ return conn.execute(sql).fetchone()[0] or 0 # ---------- Distribution helpers (computed on-demand in UI) ------------------ def _hist_numeric_sample(conn, table: str, col: str, sample_limit: int = EDA_SAMPLE_LIMIT_NUMERIC_HIST): sql = f""" SELECT CAST({col} AS REAL) AS v FROM {table} WHERE {col} IS NOT NULL LIMIT ? """ vals = [r[0] for r in conn.execute(sql, (int(sample_limit),)).fetchall() if r[0] is not None] return vals def _topk_categorical(conn, table: str, col: str, k: int = EDA_TOPK_CATEGORICAL): try: rows = conn.execute( f"SELECT {col} AS v, COUNT(*) AS cnt FROM {table} GROUP BY {col} ORDER BY cnt DESC LIMIT ?", (int(k),) ).fetchall() return rows except Exception: return [] def _timeseries_monthly(conn, table: str, col: str): sql = f""" SELECT strftime('%Y-%m', datetime({col})) AS ym, COUNT(*) AS cnt FROM {table} WHERE {col} IS NOT NULL GROUP BY ym ORDER BY ym """ rows = conn.execute(sql).fetchall() return rows CODEY_HINTS = ("id", "code", "zip", "prefix", "number", "cpf", "cnpj") def _is_integer_series(s): s = s.dropna() if s.empty: return False # true integer or float with all whole numbers return (np.issubdtype(s.dtype, np.integer) or (np.issubdtype(s.dtype, np.floating) and np.all(np.isfinite(s)) and np.all(np.modf(s)[0] == 0.0))) def is_code_like(series: pd.Series) -> bool: """Heuristic: numeric but looks like an ID/code.""" name = (series.name or "").lower() s = series.dropna() if s.empty: return False # integer-like and column name hints OR very high cardinality with wide numeric range name_flag = any(h in name for h in CODEY_HINTS) int_like = _is_integer_series(s) # many unique values but mostly discrete high_card = s.nunique() > min(200, int(len(s) * 0.2)) wide_range = (s.max() - s.min()) > 1_000 # tweak as needed return int_like and (name_flag or (high_card and wide_range)) def pretty_bin_label(interval) -> str: lo, hi = interval.left, interval.right def fmt(x): if abs(x) >= 1_000_000: return f"{x/1_000_000:.1f}M" if abs(x) >= 1_000: return f"{x/1_000:.1f}k" return f"{x:.0f}" return f"{fmt(lo)}–{fmt(hi)}" def plot_distribution(ax, series: pd.Series, max_bins: int = 10): s = series.dropna() ax.set_title(series.name, fontsize=11) # 1) treat code-like numeric as categories if np.issubdtype(s.dtype, np.number) and is_code_like(series): counts = s.value_counts().head(20) counts.sort_values(ascending=False).plot(kind="bar", ax=ax) ax.set_ylabel("count") ax.set_xlabel("top 20 values") ax.tick_params(axis="x", rotation=45, labelsize=8) return # 2) real numeric: quantile bins if np.issubdtype(s.dtype, np.number): # drop duplicates='drop' to handle repeated quantiles try: cats = pd.qcut(s, q=min(max_bins, max(2, s.nunique())), duplicates="drop") counts = cats.value_counts().sort_index() # re-label bins nicely labels = [pretty_bin_label(iv) for iv in counts.index] ax.bar(range(len(counts)), counts.values) ax.set_xticks(range(len(counts))) ax.set_xticklabels(labels, rotation=60, ha="right", fontsize=8) ax.set_ylabel("count") except Exception: # fallback simple hist if qcut fails ax.hist(s, bins=min(max_bins, 20)) ax.tick_params(axis="x", rotation=45, labelsize=8) ax.set_ylabel("count") return # 3) non-numeric: top categories counts = s.astype(str).value_counts().head(20) counts.sort_values(ascending=False).plot(kind="bar", ax=ax) ax.set_ylabel("count") ax.set_xlabel("top 20 categories") ax.tick_params(axis="x", rotation=45, labelsize=8) @st.cache_data(show_spinner=False) def run_eda_snapshot(db_path: str, deep_freq: bool = False, topk: int = EDA_TOPK_CATEGORICAL) -> dict: """ Snapshot summary (lightweight): total rows, duplicate counts, per-column nulls, empties (TEXT), distincts, and min/max for numeric/date columns. Heavy plots are computed on-demand in the UI. """ _raw = os.getenv("EDA_EXCLUDE", "chat_history") EDA_EXCLUDE_TABLES = {x.strip() for x in _raw.split(",") if x.strip()} mtime = os.path.getmtime(db_path) out = {"_meta": {"db_mtime": mtime, "generated_at": datetime.utcnow().isoformat()}} with sqlite3.connect(db_path, timeout=SQL_TIMEOUT) as conn: _apply_busy_timeout(conn) all_tables = _list_tables(conn) tables = [t for t in all_tables if t not in EDA_EXCLUDE_TABLES ] for t in tables: cols = _get_cols(conn, t) if not cols: continue declared = _get_declared_types(conn, t) affinities = {c: _affinity(declared.get(c, ""), c) for c in cols} total = _count_rows(conn, t) nulls = _count_nulls_per_col(conn, t, cols) empties_text = _count_empty_strings_text(conn, t, cols, affinities) # Distinct counts try: distincts = _count_distinct_per_col(conn, t, cols) except Exception: distincts = {c: None for c in cols} # Duplicates if t in OLIST_KEYS: try: dup_by_keys = _count_duplicates_by_keys(conn, t, OLIST_KEYS[t]) except Exception: dup_by_keys = None dup_full = None else: dup_by_keys = None try: dup_full = _count_fullrow_duplicates(conn, t, cols, total) except Exception: dup_full = None # Min/Max (num & date) col_rows = [] for c in cols: item = { "name": c, "affinity": affinities.get(c), "nulls": nulls.get(c, 0), "empty_strings": empties_text.get(c, 0), "distinct": distincts.get(c), "min": None, "max": None, } if affinities.get(c) == "num": mn, mx = _min_max_numeric(conn, t, c) item["min"], item["max"] = mn, mx elif affinities.get(c) == "date": mn, mx = _min_max_datetime(conn, t, c) item["min"], item["max"] = mn, mx col_rows.append(item) out[t] = { "total_rows": total, "dup_by_keys": dup_by_keys, "dup_fullrow": dup_full, "columns": col_rows, } return out def _eda_to_df(eda: dict, table: str) -> pd.DataFrame: """Format a single table's column stats to a neat DataFrame.""" cols = eda.get(table, {}).get("columns", []) df = pd.DataFrame(cols) if df.empty: return df tr = max(eda.get(table, {}).get("total_rows", 0), 1) if "nulls" in df.columns: df["null_pct"] = (df["nulls"] * 100.0 / tr).round(2) if "empty_strings" in df.columns: df["empty_pct"] = (df["empty_strings"] * 100.0 / tr).round(2) # order columns nicely want = ["name", "affinity", "nulls", "null_pct", "empty_strings", "empty_pct", "distinct", "min", "max"] keep = [c for c in want if c in df.columns] others = [c for c in df.columns if c not in keep] return df[keep + others] #----------------EDA Helper End ----------------- # ----------------------------- # LLM wrapper # ----------------------------- def get_llm_client(choice: str): choice = (choice or "OPENAI").upper() if choice == "OPENAI": from openai import OpenAI client = OpenAI(api_key=os.getenv("OPENAI_API_KEY")) class Wrapper: def chat(self, messages): resp = client.chat.completions.create( model="gpt-4.1-mini", messages=messages, max_tokens=1000, temperature=0.1, ) return resp.choices[0].message.content.strip() return Wrapper() elif choice == "ANTHROPIC": import anthropic client = anthropic.Anthropic(api_key=os.getenv("ANTHROPIC_API_KEY")) class Wrapper: def chat(self, messages): sys = "\n".join([m["content"] for m in messages if m["role"]=="system"]) usr = "\n".join([m["content"] for m in messages if m["role"]=="user"]) content = (sys + "\n\n" + usr).strip() resp = client.messages.create( model="claude-3-haiku-20240307", max_tokens=1000, temperature=0.1, messages=[{"role":"user","content":content}], ) return "".join([getattr(b, "text", "") for b in resp.content]) return Wrapper() else: raise ValueError(f"Unsupported LLM_CHOICE: {choice}") def plan_sql(llm, user_text: str, schema_registry: dict): schema_json = json.dumps({t:list(schema_registry[t]["columns"].keys()) for t in schema_registry}) # === RAG retrieval (1212) === try: # simple heuristic to guess intent quickly using existing classifier if present intent_guess = None try: intent_guess = detect_non_sql_intent(user_text) or None except Exception: intent_guess = None schema_fp = _schema_fingerprint(schema_registry) ex_query = "\n".join([ "[INTENT]={}".format(intent_guess or ""), "QUESTION={}".format(user_text), "SCHEMA={}".format(schema_fp), ]) exemplars = RAG.query(ex_query, k=3, where={"schema_fp": schema_fp} if RAG_BACKEND=="CHROMA" else None) ex_blocks = [] for ex in exemplars: ex_blocks.append(ex.get("text","")) sep = "\n\n--- EXEMPLAR ---\n" # safe, single-line ex_text = sep.join(ex_blocks) if ex_blocks else "" except Exception: ex_text = "" sys_prompt = """You are a SQL planner for SQLite Olist dataset. Guidelines: - Use only valid tables/columns from SCHEMA. - Prefer a safe LIMIT 50-100 unless user explicitly asks for all rows. - Align date ranges with the user's wording. - Outputs must be pure JSON with this shape: {"queries": ["SQL1","SQL2?"], "explanation":"..."} If EXEMPLARS are provided, learn patterns but generate fresh SQL that matches the new QUESTION. """ user_block = f"""USER QUESTION: {user_text} SCHEMA: {schema_json}""" if ex_text: user_block += f""" EXEMPLARS: {ex_text}""" out = llm.chat([ {"role":"system","content":sys_prompt}, {"role":"user","content":user_block} ]) try: return json.loads(out) except Exception: return {"queries":[out.strip()],"explanation":"fallback"} # ----------------------------- # Validator / Policy helpers # ----------------------------- SQL_KEYWORDS = { "WHERE","ON","USING","GROUP","ORDER","LIMIT","JOIN","LEFT","RIGHT","FULL","INNER","OUTER", "CROSS","NATURAL","UNION","EXCEPT","INTERSECT","HAVING","OFFSET","FETCH","BY" } SENSITIVE_TABLES = {"orders", "order_items", "order_payments", "order_reviews"} def _strip_trailing_semicolon(s: str) -> str: return re.sub(r";\s*$", "", s.strip()) CLAUSE_BOUNDARY = re.compile( r"\b(GROUP\s+BY|HAVING|WINDOW|ORDER\s+BY|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT)\b", re.IGNORECASE, ) def _inject_where(sql: str, condition: str) -> str: s = _strip_trailing_semicolon(sql) m_where = re.search(r"\bWHERE\b", s, flags=re.IGNORECASE) if m_where: after_where = s[m_where.end():] m_bound = CLAUSE_BOUNDARY.search(after_where) segment = after_where[: m_bound.start()] if m_bound else after_where if segment.strip() == "": return s[: m_where.end()] + f" {condition} " + after_where else: return s[: m_where.end()] + f" {condition} AND" + after_where m_bound = CLAUSE_BOUNDARY.search(s) if m_bound: i = m_bound.start() return s[:i] + f" WHERE {condition} " + s[i:] else: return s + f" WHERE {condition}" def _top_level_alias(sql: str, table: str): pat = re.compile( rf"(FROM|JOIN)\s+{table}\b(?:\s+(?:AS\s+)?(?P[A-Za-z_][A-Za-z0-9_]*))?", re.IGNORECASE ) depth = 0 in_quote = None i = 0 pos_set = set() while i < len(sql): ch = sql[i] if in_quote: if ch == in_quote: in_quote = None else: if ch in ("'", '"'): in_quote = ch elif ch == "(": depth += 1 elif ch == ")" and depth > 0: depth -= 1 if depth == 0: pos_set.add(i) i += 1 for m in pat.finditer(sql): if m.start() in pos_set: alias = m.group("alias") if alias and alias.upper() not in SQL_KEYWORDS: return alias, True return table, True return None, False def _cleanup_where_and(sql: str) -> str: s = re.sub(r"\bWHERE\s+AND\b", "WHERE", sql, flags=re.IGNORECASE) s = re.sub(r"\bAND\s+AND\b", "AND", s, flags=re.IGNORECASE) s = re.sub(r"\s{2,}", " ", s) return s.strip() def _remove_unscoped_notin_products(sql: str, product_ref: str) -> str: pat = re.compile( rf"(?:\b{re.escape(product_ref)}\.)?product_id\s+NOT\s+IN\s*\(\s*(?P.*?FROM\s+order_items\b.*?)\)", re.IGNORECASE | re.DOTALL, ) def repl(m): sub = m.group("sub") if re.search(r"\b(orders|customer_id)\b", sub, flags=re.IGNORECASE): return m.group(0) return "" s = pat.sub(repl, sql) return _cleanup_where_and(s) def _find_table_alias(sql: str, table: str): m = re.search( rf"(?:FROM|JOIN)\s+{table}\b(?:\s+(?:AS\s+)?(?P[A-Za-z_][A-Za-z0-9_]*))?", sql, flags=re.IGNORECASE ) if not m: return (None, False) alias = m.group("alias") if alias and alias.upper() not in SQL_KEYWORDS: return (alias, True) return (table, True) def _mentions_seller(sql: str) -> bool: return bool( re.search(r"\bseller_id\b", sql, re.IGNORECASE) or re.search(r"(?:FROM|JOIN)\s+sellers\b", sql, re.IGNORECASE) or re.search(r"\bGROUP\s+BY\s+[^;]*seller_id\b", sql, re.IGNORECASE) or re.search(r"\bORDER\s+BY\s+[^;]*seller_id\b", sql, re.IGNORECASE) ) def _mentions_product(sql: str) -> bool: return bool( re.search(r"\bproduct_id\b", sql, re.IGNORECASE) or re.search(r"(?:FROM|JOIN)\s+products\b", sql, re.IGNORECASE) ) def _is_revenue_sql(sql: str) -> bool: return bool( re.search(r"\bSUM\s*\(\s*(oi\.)?(price|freight_value)\s*\)", sql, re.IGNORECASE) or re.search(r"\bSUM\s*\(\s*(op\.)?payment_value\s*\)", sql, re.IGNORECASE) or re.search(r"\b(revenue|gmv|sales|turnover)\b", sql, re.IGNORECASE) ) def _is_seller_revenue_sql(sql: str) -> bool: return _is_revenue_sql(sql) and _mentions_seller(sql) def _is_product_revenue_sql(sql: str) -> bool: return _is_revenue_sql(sql) and _mentions_product(sql) # payments helpers def _is_payments_sql(sql: str) -> bool: return re.search(r"(?:FROM|JOIN)\s+order_payments\b", sql, re.IGNORECASE) is not None def _has_customer_scope(sql: str) -> bool: if re.search(r"\bcustomer_id\s*=\s*['\"][^'\"()]+['\"]", sql, re.IGNORECASE): return True if re.search(r"EXISTS\s*\(\s*SELECT\s+1\s+FROM\s+orders\s+\w+\s+WHERE\s+[^)]*\bcustomer_id\b", sql, re.IGNORECASE): return True return False def _intent_platform_wide_payments(user_text: str) -> bool: t = f" { (user_text or '').lower() } " if "payment" not in t and "payments" not in t: return False user_markers = [" my ", " me ", " mine ", " by me ", " for me ", " my payments"] if any(m in t for m in user_markers): return False platform_markers = [ "on the platform", "platform payments", "overall payments", "entire platform", "whole platform", "across the platform", "site-wide", "system-wide" ] if any(m in t for m in platform_markers): return True if re.search(r"\ball\b.*\bpayments?\b", t): return True if re.search(r"\bevery\b.*\bpayments?\b", t): return True if re.search(r"\btotal\b.*\bpayments?\b", t): return True return False # write & introspection guards def _is_mutating_sql(sql: str) -> bool: return re.search( r"^\s*(UPDATE|DELETE|INSERT|REPLACE|MERGE|CREATE|ALTER|DROP|TRUNCATE|VACUUM)\b", sql, re.IGNORECASE ) is not None def _is_schema_introspection_sql(sql: str) -> bool: if re.search(r"^\s*PRAGMA\b", sql, re.IGNORECASE): return True if re.search(r"\bsqlite_master\b|\bsqlite_schema\b", sql, re.IGNORECASE): return True if re.search(r"SELECT\s+name\s+FROM\s+sqlite_master", sql, re.IGNORECASE): return True if re.search(r"SELECT\s+\*\s+FROM\s+sqlite_master", sql, re.IGNORECASE): return True return False def _intent_schema_introspection(user_text: str) -> bool: t = (user_text or "").lower() # Robust patterns (covers: "list of all tables", "share the list of tables", "show tables", etc.) patterns = [ r"\blist(?:\s+of)?\s+(?:all\s+)?tables\b", r"\bshare\s+(?:the\s+)?list\s+of\s+(?:all\s+)?tables\b", r"\bgive\s+me\s+(?:the\s+)?list\s+of\s+(?:all\s+)?tables\b", r"\bshow(?:\s+me)?\s+(?:the\s+)?(?:list\s+of\s+)?(?:all\s+)?tables\b", r"\bwhat\s+(?:are\s+)?(?:the\s+)?tables\b", r"\bdescribe\s+tables?\b", r"\b(list|show)\s+(?:all\s+)?indexes\b", r"\b(show|list)\s+schema\b", r"\b(db|database)\s+schema\b", r"\bschema\b", ] for p in patterns: if re.search(p, t): return True # Back-compat substring keys (kept so behavior elsewhere remains unchanged) keys = [ "list all tables", "list tables", "show tables", "what tables", "schema", "describe tables", "list indexes", "show indexes", "list all indexes", "show schema", "db schema", "database schema" ] return any(k in t for k in keys) def _intent_dump_all_data(user_text: str) -> bool: t = f" {(user_text or '').lower()} " # Direct phrases patterns = [ "list all data", "dump all data", "export all data", "everything in database", "all database data", "full data dump", "list data from all tables", "list data from each table", "show data from all tables", "show all table data", "dump data from all tables", "export data from all tables", "all tables data", "data from all tables", ] if any(p in t for p in patterns): return True # Heuristic: mentions "all/each/every table" + data-ish verbs/nouns table_scope = (" all tables " in t) or (" each table " in t) or (" every table " in t) data_words = any(w in t for w in [" data ", " rows ", " records ", " entries ", " select *", " list ", " show "]) return bool(table_scope and data_words) def _plan_looks_like_dump_all_tables(plan: dict) -> bool: try: qs = plan.get("queries") or [] except Exception: return False uniq_tables = set() count_selects = 0 for q in qs: if not isinstance(q, str): continue if re.search(r"^\s*SELECT\b", q, re.IGNORECASE): count_selects += 1 for t in _extract_tables_from_sql(q): uniq_tables.add(t) # Heuristic: selecting from several distinct base tables looks like a dump return (count_selects >= 3 and len(uniq_tables) >= 3) # pagination/limit helpers def _has_limit_clause(sql: str) -> bool: try: return re.search(r"\bLIMIT\b\s+\d+", sql, flags=re.IGNORECASE) is not None except Exception: return False def _ensure_limit(sql: str, n: int = 50) -> str: s = _strip_trailing_semicolon(sql) if not _looks_like_select(s): return s if _has_limit_clause(s): return s return f"{s} LIMIT {n}" def _force_limit(sql: str, n: int = 10) -> str: s = _strip_trailing_semicolon(sql) if not _looks_like_select(s): return s if _has_limit_clause(s): s = re.sub(r"(?i)LIMIT\s+\d+", f"LIMIT {n}", s); return s return f"{s} LIMIT {n}" def _cap_limit(sql: str, cap: int = 50) -> str: """Ensure LIMIT exists and is <= cap; if existing LIMIT > cap, reduce to cap; if no LIMIT, add cap.""" s = _strip_trailing_semicolon(sql) if not _looks_like_select(s): return s try: m = re.search(r"(?i)\bLIMIT\s+(\d+)", s) if m: val = int(m.group(1)) if val <= cap: return s # keep user's smaller limit return re.sub(r"(?i)\bLIMIT\s+\d+", f"LIMIT {cap}", s) else: return f"{s} LIMIT {cap}" except Exception: return _ensure_limit(s, cap) def _wants_all(user_text: str) -> bool: return bool(re.search(r"\ball\b", (user_text or ""), flags=re.IGNORECASE)) # rate limit helpers def _utc_now(): return datetime.utcnow() def _utc_threshold_str(seconds: int) -> str: return (_utc_now() - timedelta(seconds=seconds)).strftime("%Y-%m-%d %H:%M:%S") def check_rate_limit(user_id: str, role: str) -> tuple[bool, str]: max_req = RATE_LIMIT_MAX_REQUESTS_ADMIN if (role == "admin") else RATE_LIMIT_MAX_REQUESTS_USER if max_req <= 0: return (False, "") threshold = _utc_threshold_str(RATE_LIMIT_WINDOW_SEC) try: with get_conn() as conn: row = conn.execute( "SELECT COUNT(*) FROM chat_history WHERE user_id = ? AND created_at >= ?", (user_id, threshold) ).fetchone() count = int(row[0]) if row and row[0] is not None else 0 if count >= max_req: msg = f"⏳ Rate limit hit: max {max_req} requests per {RATE_LIMIT_WINDOW_SEC}s. Please try again shortly." return (True, msg) return (False, f"{count}/{max_req} used in the last {RATE_LIMIT_WINDOW_SEC}s.") except Exception as e: logger.warning("Rate-limit check failed: %s", e) return (False, "Rate-limit check unavailable.") # block: rowcounts across all tables / each table def _intent_rowcounts_all_tables(user_text: str) -> bool: t = f" {(user_text or '').lower()} " keywords = [ "count of records of all tables","count from each table","counts from each table", "row count of each table","row counts of all tables","table wise record count", "table-wise record count","table wise counts","table-wise counts", "how many rows in each table","list all tables with row counts","show tables with row counts", "rowcount of all tables","counts per table", ] if any(k in t for k in keywords): return True if (" count " in t or " counts " in t) and (" each table " in t or " all tables " in t): return True return False _rowcount_single_table_re = re.compile( r"^\s*SELECT\s+COUNT\s*\(\s*\*\s*\)\s+FROM\s+([A-Za-z_][A-Za-z0-9_]*)\b", re.IGNORECASE | re.DOTALL ) def _is_single_table_rowcount_sql(sql: str) -> str | None: s = _strip_trailing_semicolon(sql or "") m = _rowcount_single_table_re.match(s) if not m: return None if re.search(r"\bGROUP\s+BY\b", s, flags=re.IGNORECASE): return None return m.group(1) def _plan_looks_like_multitable_rowcounts(plan: dict) -> bool: try: qs = plan.get("queries") or [] except Exception: return False tables = set() for q in qs: t = _is_single_table_rowcount_sql(q) if t: tables.add(t.lower()) return len(tables) >= 2 def _is_user_count_query(sql: str) -> bool: s = _strip_trailing_semicolon(sql or "") return re.search(r"^\s*SELECT\s+COUNT\s*\(", s, re.IGNORECASE) is not None # ----------------------------- # (Multilingual) Translation helpers — IndicTrans2 (lazy cached) # ----------------------------- TRANSLATION_ENABLED = os.getenv("TRANSLATION_ENABLED", "1") == "1" LANG_CODE_MAP = {"en": "eng_Latn", "hi": "hin_Deva", "mr": "mar_Deva"} _LANG_TAGS = {"eng_Latn","hin_Deva","mar_Deva"} def _norm_guard_text(t: str) -> str: """Lowercase, strip punctuation to make intent regex robust.""" if not t: return "" t = t.lower() t = re.sub(r"[^\w\s]", " ", t) # remove punctuation like leading dots t = re.sub(r"\s+", " ", t).strip() return t # English patterns for “dump/list across all tables” _ALL_TABLES_EN_PATTERNS = [ r"(?:list|show|dump|export|fetch|display|print|get|provide)\s+(?:all\s+)?(?:data|records|rows|everything)\s+(?:from|in|across)\s+(?:all|every)\s+tables?", r"(?:list|show|dump|export|fetch|display|print|get|provide)\s+(?:data|records|rows|everything)\s+(?:from|in|across)\s+(?:all|every)\s+tables?", r"(?:all|every)\s+tables?\s+(?:data|records|rows|entries)", r"(?:across|from|in)\s+(?:all|every)\s+tables?", r"(?:for|from)\s+each\s+table(?:\s+counts?| data| records)?", # “count from each table”, etc. r"list\s+(?:all\s+)?tables?\s+data", # “list all tables data” r"dump\s+(?:the\s+)?database", # generic dump ] def is_forbidden_all_tables_post_tx(en_text: str) -> bool: """Run on the translated-to-English query BEFORE planning.""" t = _norm_guard_text(en_text) return any(re.search(p, t) for p in _ALL_TABLES_EN_PATTERNS) def _sample(s: str, n: int = 120) -> str: if s is None: return "" s = str(s) return s if len(s) <= n else (s[:n] + "…") def _strip_existing_tag(text: str) -> str: m = re.match(r"^\s*>>([A-Za-z_]+)<<\s*", text or "") if m and m.group(1) in _LANG_TAGS: return (text or "")[m.end():] return text or "" def _ensure_tag_prefixed(text: str, tag: str) -> str: core = _strip_existing_tag(text or "") return f">>{tag}<< " + core.lstrip() # Safe EN→target translation wrapper for UI strings (errors, notices, etc.) def _tx_safe(text: str, tgt_lang: str | None) -> str: """ Best-effort translate an English message to `tgt_lang`. Falls back to original English if translation is disabled or fails. """ if not text: return "" tgt = (tgt_lang or "").strip().lower() # Pass-through for English/auto/blank if tgt in ("", "en", "auto"): return text try: # Respect feature flag if present (defaults to enabled) if not globals().get("TRANSLATION_ENABLED", True): return text # Use your existing translator (should accept short codes like 'hi','mr') out = _tx_from_en(text, tgt) return text if out is None else str(out) except Exception as e: try: logger.warning("[tx-safe] translate failed tgt=%s: %s", tgt, e) except Exception: pass return text def _localize_msg(msg_en: str, user_lang: str) -> str: """Translate an English UI message to the user's language via _tx_safe.""" try: if not user_lang or user_lang.startswith("en"): return msg_en # _tx_safe internally handles fast-fail/cache; we call it explicitly here return _tx_safe(msg_en, src="en", tgt=user_lang) except Exception: # If translation fails for any reason, fall back to English return msg_en def _get_lang_token_id(tok, tag: str) -> int | None: """ Resolve token id for a language tag; prefer >>tag<< then fall back to plain. Return None if unresolved — we'll skip decoder forcing in that case. """ unk = getattr(tok, "unk_token_id", None) for cand in (f">>{tag}<<", tag, f"<<{tag}>>"): try: tid = tok.convert_tokens_to_ids(cand) if isinstance(tid, int) and tid != -1 and (unk is None or tid != unk): logger.debug("[tx] lang token '%s' → id=%s", cand, tid) return tid except Exception: pass logger.warning("[tx] cannot resolve token id for '%s'; will skip decoder forcing", tag) return None # Ensure cache envs are set earlier as we discussed @st.cache_resource(show_spinner=False) def _load_tx_models(): if not TRANSLATION_ENABLED: return {"ready": False, "error": "Translation disabled"} try: import torch from transformers import AutoModelForSeq2SeqLM, AutoTokenizer cache_dir = os.getenv("TRANSFORMERS_CACHE", "/data/hf-cache/transformers") device = "cuda" if torch.cuda.is_available() else "cpu" logger.debug("[tx] cache_dir=%s device=%s", cache_dir, device) # Indic → English indic_en_tok = AutoTokenizer.from_pretrained( "ai4bharat/indictrans2-indic-en-dist-200M", use_fast=False, trust_remote_code=True, cache_dir=cache_dir ) indic_en_mod = AutoModelForSeq2SeqLM.from_pretrained( "ai4bharat/indictrans2-indic-en-dist-200M", trust_remote_code=True, cache_dir=cache_dir ).to(device) # English → Indic en_indic_tok = AutoTokenizer.from_pretrained( "ai4bharat/indictrans2-en-indic-dist-200M", use_fast=False, trust_remote_code=True, cache_dir=cache_dir ) en_indic_mod = AutoModelForSeq2SeqLM.from_pretrained( "ai4bharat/indictrans2-en-indic-dist-200M", trust_remote_code=True, cache_dir=cache_dir ).to(device) logger.info("[tx] IndicTrans2 models loaded (trust_remote_code=True)") return { "ready": True, "device": device, "indic_en_tok": indic_en_tok, "indic_en_mod": indic_en_mod, "en_indic_tok": en_indic_tok, "en_indic_mod": en_indic_mod, } except Exception as e: logger.error("[tx] load failed: %s", e, exc_info=True) return {"ready": False, "error": str(e)} # --- Language detection helpers (backend) --- # Optional: langid try: import langid _HAS_LANGID = True try: langid.set_languages(['en','hi','mr']) except Exception: pass except Exception: _HAS_LANGID = False _DEVANAGARI_RANGE = ('\u0900', '\u097F') _MAR_GLYPHS = set("ळऴऱय़ॲॅॉॐॠॡ") _MAR_WORDS = {"आणि","माझ्या","यादी","कुठे","कधी","विक्री","ऑर्डर","दाखवा","एकूण"} _HIN_WORDS = {"है","हूँ","सूची","कहाँ","कब","विक्रय","आदेश","दिखाइए","कुल"} def _has_devanagari(text: str) -> bool: return any(_DEVANAGARI_RANGE[0] <= ch <= _DEVANAGARI_RANGE[1] for ch in (text or "")) def _score_words(text: str, lex: set[str]) -> int: t = (text or "").lower() return sum(1 for w in lex if w in t) def _heuristic_hi_mr(text: str) -> str: if not _has_devanagari(text): return "en" mar_score = sum(text.count(ch) for ch in _MAR_GLYPHS) + 2*_score_words(text,_MAR_WORDS) hin_score = 2*_score_words(text,_HIN_WORDS) if mar_score > hin_score: return "mr" if hin_score > mar_score: return "hi" return "hi" def _detect_lang(text: str) -> str: # 1) UI override override = st.session_state.get("ui_lang_override") if override in ("en","hi","mr"): logger.debug("[lang] override=%s text=%s", override, _sample(text)) return override # 2) langid if _HAS_LANGID and (text or "").strip(): try: lang, _ = langid.classify(text) logger.debug("[lang] langid=%s text=%s", lang, _sample(text)) if lang in ("hi","mr"): return lang if lang == "en" and not _has_devanagari(text): return "en" except Exception as e: logger.debug("[lang] langid error: %s", e) # 3) heuristic res = _heuristic_hi_mr(text) logger.debug("[lang] heuristic=%s text=%s", res, _sample(text)) return res def _tx_to_en_old(text: str, src_short: str) -> str: """Indic → English (prefix source tag + forced_bos to eng_Latn), with debug logs.""" if not text or not TRANSLATION_ENABLED or src_short == "en": logger.debug("[tx→en] bypass (src=%s) text=%s", src_short, _sample(text)) return text tx = _load_tx_models() if not tx.get("ready"): logger.warning("[tx→en] backend not ready: %s", tx.get("error")) return text import torch tok = tx["indic_en_tok"]; mod = tx["indic_en_mod"]; device = tx["device"] src_tag = LANG_CODE_MAP.get(src_short, "hin_Deva") inp = _ensure_tag_prefixed(text, src_tag) logger.debug("[tx→en] src_tag=%s device=%s inp=%s", src_tag, device, _sample(inp, 160)) try: enc = tok([inp], return_tensors="pt", padding=True, truncation=True) if device == "cuda": enc = {k: v.to(device) for k, v in enc.items()} mod = mod.to(device) forced_bos = tok.get_lang_id("eng_Latn") with torch.no_grad(): out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256) detok = tok.batch_decode(out, skip_special_tokens=True) result = detok[0] if detok else text logger.debug("[tx→en] ok translated=%s", _sample(result, 200)) return result except AssertionError as e: logger.error("[tx→en] tokenizer assertion: %s | inp=%s", e, _sample(inp, 200), exc_info=True) # hard fallback: try again with raw text (in case tag parsing misfired) try: raw = f">>{src_tag}<< " + (text or "").lstrip() enc = tok([raw], return_tensors="pt", padding=True, truncation=True) if device == "cuda": enc = {k: v.to(device) for k, v in enc.items()} mod = mod.to(device) forced_bos = tok.get_lang_id("eng_Latn") with torch.no_grad(): out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256) detok = tok.batch_decode(out, skip_special_tokens=True) result = detok[0] if detok else text logger.debug("[tx→en] retry translated=%s", _sample(result, 200)) return result except Exception as e2: logger.error("[tx→en] retry failed: %s", e2, exc_info=True) return text except Exception as e: logger.error("[tx→en] error: %s | inp=%s", e, _sample(inp, 200), exc_info=True) return text def _tx_from_en_old(text: str, tgt_short: str) -> str: """English → Indic (prefix eng_Latn + forced_bos to target), with debug logs.""" if not text or not TRANSLATION_ENABLED or tgt_short == "en": logger.debug("[tx en→] bypass (tgt=%s) text=%s", tgt_short, _sample(text)) return text tx = _load_tx_models() if not tx.get("ready"): logger.warning("[tx en→] backend not ready: %s", tx.get("error")) return text import torch tok = tx["en_indic_tok"]; mod = tx["en_indic_mod"]; device = tx["device"] tgt_tag = LANG_CODE_MAP.get(tgt_short, "hin_Deva") inp = _ensure_tag_prefixed(text, "eng_Latn") logger.debug("[tx en→] tgt_tag=%s device=%s inp=%s", tgt_tag, device, _sample(inp, 160)) try: enc = tok([inp], return_tensors="pt", padding=True, truncation=True) if device == "cuda": enc = {k: v.to(device) for k, v in enc.items()} mod = mod.to(device) forced_bos = tok.get_lang_id(tgt_tag) with torch.no_grad(): out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256) detok = tok.batch_decode(out, skip_special_tokens=True) result = detok[0] if detok else text logger.debug("[tx en→] ok translated=%s", _sample(result, 200)) return result except Exception as e: logger.error("[tx en→] error: %s | inp=%s", e, _sample(inp, 200), exc_info=True) return text def _tx_to_en(text: str, src_short: str) -> str: """ Indic → English using TWO-PLAIN-TAGS ' ' + text, explicit decoder start, greedy decode, and use_cache=False. """ if not text or not TRANSLATION_ENABLED or src_short == "en": logger.debug("[tx→en] bypass (src=%s) text=%s", src_short, _sample(text)) return text tx = _load_tx_models() if not tx.get("ready"): logger.warning("[tx→en] backend not ready: %s", tx.get("error")) return text import torch tok = tx["indic_en_tok"]; mod = tx["indic_en_mod"]; device = tx["device"] mod.eval() # ensure eval mode src_tag = LANG_CODE_MAP.get(src_short, "hin_Deva") tgt_tag = "eng_Latn" inp = f"{src_tag} {tgt_tag} " + (text or "").lstrip() logger.debug("[tx→en] inp(two-tags)=%s", _sample(inp, 160)) try: enc = tok([inp], return_tensors="pt", padding=True, truncation=True) if device == "cuda": enc = {k: v.to(device) for k, v in enc.items()} mod.to(device) tgt_id = _get_lang_token_id(tok, tgt_tag) logger.debug("[tx→en] decoder_start_id=%s", tgt_id) gen_kwargs = dict( # no beams, no sampling; avoid PKV path do_sample=False, num_beams=1, max_new_tokens=192, use_cache=False, ) if tgt_id is not None: decoder_input_ids = torch.tensor( [[tgt_id]], device=enc["input_ids"].device, dtype=enc["input_ids"].dtype ) gen_kwargs["decoder_input_ids"] = decoder_input_ids with torch.no_grad(): out = mod.generate( input_ids=enc["input_ids"], attention_mask=enc.get("attention_mask"), **gen_kwargs ) detok = tok.batch_decode(out, skip_special_tokens=True) result = detok[0] if detok else text logger.debug("[tx→en] OK → %s", _sample(result, 200)) return result except Exception as e: logger.error("[tx→en] failed: %s", e, exc_info=True) return text def _tx_from_en(text: str, tgt_short: str) -> str: """ English → Indic using TWO-PLAIN-TAGS 'eng_Latn ' + text, explicit decoder start, greedy decode, and use_cache=False. """ if not text or not TRANSLATION_ENABLED or tgt_short == "en": logger.debug("[tx en→] bypass (tgt=%s) text=%s", tgt_short, _sample(text)) return text tx = _load_tx_models() if not tx.get("ready"): logger.warning("[tx en→] backend not ready: %s", tx.get("error")) return text import torch tok = tx["en_indic_tok"]; mod = tx["en_indic_mod"]; device = tx["device"] mod.eval() src_tag = "eng_Latn" tgt_tag = LANG_CODE_MAP.get(tgt_short, "hin_Deva") inp = f"{src_tag} {tgt_tag} " + (text or "").lstrip() logger.debug("[tx en→] inp(two-tags)=%s", _sample(inp, 160)) try: enc = tok([inp], return_tensors="pt", padding=True, truncation=True) if device == "cuda": enc = {k: v.to(device) for k, v in enc.items()} mod.to(device) tgt_id = _get_lang_token_id(tok, tgt_tag) logger.debug("[tx en→] decoder_start_id=%s", tgt_id) gen_kwargs = dict( do_sample=False, num_beams=1, max_new_tokens=192, use_cache=False, ) if tgt_id is not None: decoder_input_ids = torch.tensor( [[tgt_id]], device=enc["input_ids"].device, dtype=enc["input_ids"].dtype ) gen_kwargs["decoder_input_ids"] = decoder_input_ids with torch.no_grad(): out = mod.generate( input_ids=enc["input_ids"], attention_mask=enc.get("attention_mask"), **gen_kwargs ) detok = tok.batch_decode(out, skip_special_tokens=True) result = detok[0] if detok else text logger.debug("[tx en→] OK → %s", _sample(result, 200)) return result except Exception as e: logger.error("[tx en→] failed: %s", e, exc_info=True) return text # ----------------------------- # Core validation / scoping (continued) # ----------------------------- def _extract_tables_from_sql(sql: str) -> list[str]: if not isinstance(sql, str): return [] tables = [] for kw in ("FROM", "JOIN"): for m in re.finditer(rf"\b{kw}\s+([A-Za-z_][A-Za-z0-9_+]*)", sql, flags=re.IGNORECASE): tables.append(m.group(1).lower()) return tables def _scope_sql_to_customer(sql: str, customer_id: str) -> str: """ Applies customer scope to orders/order_items/order_payments/order_reviews if referenced. """ s = _strip_trailing_semicolon(sql) has_orders = re.search(r"(?:FROM|JOIN)\s+orders\b", s, re.IGNORECASE) is not None has_oi = re.search(r"(?:FROM|JOIN)\s+order_items\b", s, re.IGNORECASE) is not None has_op = re.search(r"(?:FROM|JOIN)\s+order_payments\b", s, re.IGNORECASE) is not None has_or = re.search(r"(?:FROM|JOIN)\s+order_reviews\b", s, re.IGNORECASE) is not None if not (has_orders or has_oi or has_op or has_or): return s # prefer alias if present def ref_for(tbl): alias, ok = _find_table_alias(s, tbl) return alias if ok else tbl o_ref, has_o_top = _top_level_alias(s, "orders") oi_ref, has_oi_top = _top_level_alias(s, "order_items") op_ref, has_op_top = _top_level_alias(s, "order_payments") or_ref, has_or_top = _top_level_alias(s, "order_reviews") # if orders table is present, scope directly if has_o_top and o_ref: cond = f"{o_ref}.customer_id = '{customer_id}'" return _cleanup_where_and(_inject_where(s, cond)) # otherwise scope through EXISTS join back to orders child_ref = None if has_oi_top: child_ref = oi_ref elif has_op_top: child_ref = op_ref elif has_or_top: child_ref = or_ref if child_ref: if re.search(r"EXISTS\s*\(\s*SELECT\s+1\s+FROM\s+orders\s+\w+\s+WHERE", s, flags=re.IGNORECASE): return s cond = f"EXISTS (SELECT 1 FROM orders o WHERE o.order_id = {child_ref}.order_id AND o.customer_id = '{customer_id}')" return _inject_where(s, cond) return s def _apply_policy(sql: str, role: str, user_text: str, customer_id: str | None) -> tuple[str | None, dict]: """ Validates, scopes, and enforces policy. Returns (secured_sql, meta_flags) If blocked, secured_sql is None and meta_flags contain 'blocked_reason'/'policy_note'. """ s = (sql or "").strip() flags = {} # Hard blocks: write/DDL if _is_mutating_sql(s): flags["blocked_reason"] = "write_block" return None, flags # Hard block: schema introspection for normal users if role == "user" and (_is_schema_introspection_sql(s) or _intent_schema_introspection(user_text)): flags["blocked_reason"] = "introspection_user_block" return None, flags # Hard block: "dump all data" intents for normal users if role == "user" and _intent_dump_all_data(user_text): flags["blocked_reason"] = "dump_all_block" return None, flags # Hard block: row counts of ALL tables / EACH table if _intent_rowcounts_all_tables(user_text): flags["blocked_reason"] = "rowcounts_all_tables_block" return None, flags # Hard block: normal user cannot access 'customers' table directly if role == "user": tables = set(_extract_tables_from_sql(s)) if "customers" in tables: flags["blocked_reason"] = "customers_user_block" return None, flags # Seller/Product revenue restrictions for normal user if role == "user": if SELLER_REVENUE_USER_MODE.upper() == "BLOCK" and _is_seller_revenue_sql(s): flags["blocked_reason"] = "seller_revenue_user_block"; return None, flags if PRODUCT_REVENUE_USER_MODE.upper() == "BLOCK" and _is_product_revenue_sql(s): flags["blocked_reason"] = "product_revenue_user_block"; return None, flags # Platform-wide payments: block or scope if _is_payments_sql(s): if role == "user" and PAYMENTS_USER_MODE == "BLOCK": flags["blocked_reason"] = "platform_payments_user_block"; return None, flags if role == "user" and PAYMENTS_USER_MODE == "SCOPE": # scope to customer's payments even if query was platform-wide s = _scope_sql_to_customer(s, customer_id) # Row-count on any single table? For normal user, block. if role == "user" and _is_user_count_query(s): flags["blocked_reason"] = "count_user_block"; return None, flags # Scope to current customer where applicable (orders/oi/op/or) if customer_id and role == "user": s = _scope_sql_to_customer(s, customer_id) # Products/Sellers queries do not need customer scope (as per requirement) s = _cleanup_where_and(s) return s, flags # Put this once near the top if not already present _SCOPE_EXISTS_RE = re.compile( r"""\b(?:o\.)?customer_id\s*=\s*(?:\?|['"][^'"]+['"])""", re.IGNORECASE, ) # --- add once near your other helpers (top-level is fine) --- def _extract_queries_from_plan(plan_obj) -> list[str]: """ Normalize the plan["queries"] into a flat list of SQL strings. Supports both list[str] and list[dict] with common keys. """ out: list[str] = [] if not isinstance(plan_obj, dict): return out raw = plan_obj.get("queries", []) if not isinstance(raw, list): return out for item in raw: if isinstance(item, str): s = item.strip() if s: out.append(s) elif isinstance(item, dict): cand = ( item.get("secured") or item.get("sql") or item.get("original") or item.get("text") ) if isinstance(cand, str) and cand.strip(): out.append(cand.strip()) return out def validate_sql( sql: str, role: str, customer_id: Optional[str], registry: dict, user_text: str, **_unused ) -> tuple[Optional[str], dict]: # Work on a single string variable; don't overwrite later. text = (sql or "").strip() flags: dict = {} params: list = [] # Scope normal users to their own orders if the query touches orders and isn't already scoped touches_orders = bool(re.search(r'\b(from|join)\s+orders\b', text, re.I)) if role == "user" and touches_orders and not _SCOPE_EXISTS_RE.search(text): # Choose alias safely alias = "o" if re.search(r'\borders\s+o\b', text, re.I) else "orders" if re.search(r'\bwhere\b', text, re.I): text += f" AND {alias}.customer_id = ?" else: text += f" WHERE {alias}.customer_id = ?" if customer_id: params.append(customer_id) flags["scoped_orders"] = True # Pagination guardrails ut = (user_text or "") wants_all = _wants_all(ut) if _looks_like_select(text): if role != "admin": # enforce preview limit unless user explicitly asked for "all" if not wants_all: text = _cap_limit(text, 50) else: if not wants_all: text = _cap_limit(text, 50) # Optional product NOT IN clean-up (uses your existing helpers) if _mentions_product(text): alias_found, ok = _find_table_alias(text, "products") if ok: text = _remove_unscoped_notin_products(text, alias_found) # Apply your higher-level policy last secured, policy_flags = _apply_policy(text, role, ut, customer_id) flags.update(policy_flags or {}) if secured is None: return None, {"blocked": True, **policy_flags} # Note if LIMIT present if _has_limit_clause(secured): policy_flags["limit_enforced"] = True # Attach bound params so executor can use them without changing your return shape if params: flags["params"] = params return secured, flags # ----------------------------- # Planner + Runner # ----------------------------- def handle_user_prompt(user_text: str, role: str, customer_id: str, registry: dict, source_lang: str = "en"): """ user_text is already English (translated if needed). source_lang is the user's original language ('en'|'hi'|'mr') """ trace_id = f"{int(time.time()*1000)}" if TRACE_LOGGING: logger.info("[trace=%s] user=%s role=%s text=%r lang=%s", trace_id, customer_id, role, user_text, source_lang) # Early block: platform-wide payments if role == "user" and PAYMENTS_USER_MODE == "BLOCK" and _intent_platform_wide_payments(user_text): if TRACE_LOGGING: logger.info("[trace=%s] Early BLOCK: platform-wide payments intent detected", trace_id) plan = {"queries": [], "explanation": "Platform-wide payments are restricted to admins."} # Translate explanation back if needed plan["explanation_display"] = _tx_from_en(plan["explanation"], source_lang) return { "plan": plan, "queries": [{"kind": "non_sql", "original": user_text, "secured": None, "blocked_reason": "platform_payments_user_block", "df": pd.DataFrame({"message": [ _tx_from_en("Platform-wide payments are restricted to admins. Try: 'Show my payments'.", source_lang) ]}), "error": None, "sql_time": None }], "llm_time": 0.0 } # Early block: schema introspection ("list tables", "show schema", etc.) is admin-only if role == "user" and _intent_schema_introspection(user_text): if TRACE_LOGGING: logger.info("[trace=%s] Early BLOCK: schema-introspection intent detected", trace_id) plan = {"queries": [], "explanation": "Listing tables/indexes/schema is admin-only."} plan["explanation_display"] = _tx_from_en(plan["explanation"], source_lang) return { "plan": plan, "queries": [{ "original": user_text, "secured": None, "blocked_reason": "introspection_user_block", "df": pd.DataFrame({"message": [_tx_from_en(plan["explanation"], source_lang)]}), "error": None, "sql_time": None }], "llm_time": 0.0 } # Early block: row counts across all tables / each table if _intent_rowcounts_all_tables(user_text): msg = "Requests for row counts across all tables / each table are restricted and not supported." plan = {"queries": [], "explanation": msg, "explanation_display": _tx_from_en(msg, source_lang)} return { "plan": plan, "queries": [{ "original": user_text, "secured": None, "blocked_reason": "rowcounts_all_tables_block", "df": pd.DataFrame({"message": [_tx_from_en(msg, source_lang)]}), "error": None, "sql_time": None }], "llm_time": 0.0 } # Early block: dump-all-data for normal users if role == "user" and _intent_dump_all_data(user_text): msg = "Requests to list/dump data across all tables are restricted." plan = {"queries": [], "explanation": msg, "explanation_display": _tx_from_en(msg, source_lang)} return { "plan": plan, "queries": [{ "original": user_text, "secured": None, "blocked_reason": "dump_all_block", "df": pd.DataFrame({"message": [_tx_from_en(msg, source_lang)]}), "error": None, "sql_time": None }], "llm_time": 0.0 } llm = get_llm_client(LLM_CHOICE) # Early block: destructive intents (delete/update/drop...) are not allowed if classify_destructive_intent_with_llm(llm, user_text): _msg = "I can’t delete or modify data. I’m a read‑only analytics assistant. Try: 'show my latest order'." plan = {"queries": [], "explanation": _msg, "explanation_display": _tx_from_en(_msg, source_lang)} return { "plan": plan, "queries": [{ "original": user_text, "secured": None, "blocked_reason": "destructive_request_block", "df": pd.DataFrame({"message": [_tx_from_en(_msg, source_lang)]}), "error": None, "sql_time": None }], "llm_time": 0.0 } wants_all = _wants_all(user_text) with timed_step("LLM plan", trace_id): t0 = time.perf_counter() plan = plan_sql(llm, user_text, registry) # NEW: extract and cap queries for execution queries = _extract_queries_from_plan(plan) llm_elapsed = time.perf_counter() - t0 # Prepare output structure out_queries = [] for q in queries[:2]: entry = {"original": q, "secured": None, "error": None, "df": None, "sql_time": None} try: secured, flags = validate_sql(q, role, customer_id, registry, user_text) params = flags.get("params") # If there’s a “?” in the SQL but no params surfaced (edge case), use customer_id defensively: if params is None and "?" in (secured or "") and customer_id: params = [customer_id] # --- policy blocks / early exit --- if secured is None: entry.update(flags) reason = flags.get("blocked_reason") or "blocked" msg_map = { "seller_revenue_user_block": "Seller revenue queries are restricted to admins.", "product_revenue_user_block": "Product revenue queries are restricted to admins.", "introspection_user_block": "Listing tables/indexes/schema is admin-only.", "dump_all_block": "Requests to list/dump all database data are restricted.", "rowcounts_all_tables_block": "Requests for row counts across all tables / each table are restricted.", "count_user_block": "Row-count (COUNT) queries are restricted for normal users.", "customers_user_block": "Access to the customers table is restricted for normal users.", "platform_payments_user_block": "Platform-wide payments are restricted to admins.", "blocked": "This request is restricted by policy.", } entry["df"] = pd.DataFrame({"message": [_tx_safe(msg_map.get(reason, msg_map["blocked"]), source_lang)]}) out_queries.append(entry) continue # --- allowed path --- entry.update(flags) entry["secured"] = secured sql_for_exec = (secured, params) if params else secured t1 = time.perf_counter() df, rows, kind = run_sql_generic(sql_for_exec) entry["sql_time"] = time.perf_counter() - t1 entry["df"] = df if kind == "select" else pd.DataFrame({"rows_affected": [rows]}) # optional warn note revenue_mode = (os.getenv("REVENUE_USER_MODE") or os.getenv("SELLER_REVENUE_USER_MODE", "BLOCK")).upper() if role == "user" and _is_seller_revenue_sql(secured) and revenue_mode == "WARN": entry["policy_note"] = _tx_safe( "Note: revenue-style queries for sellers/products are limited for normal users.", source_lang ) except SqlTimeoutError as e: entry["error"] = _tx_safe(str(e), source_lang) except Exception as e: entry["error"] = _tx_safe(str(e), source_lang) out_queries.append(entry) # plan explanation (safe) expl = plan.get("explanation") plan["explanation_display"] = _tx_safe(expl, source_lang) if isinstance(expl, str) and expl.strip() else None return {"plan": plan, "queries": out_queries, "llm_time": llm_elapsed} # ----------------------------- # Classification, history, feedback, MIS (unchanged logic) # ----------------------------- @st.cache_data(ttl=30) def load_chat_history(days: int | None = 30, only_user_id: str | None = None) -> pd.DataFrame: q = "SELECT * FROM chat_history" params, conds = [], [] if days is not None: since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S") conds.append("created_at >= ?"); params.append(since) if only_user_id: conds.append("user_id = ?"); params.append(only_user_id) if conds: q += " WHERE " + " AND ".join(conds) q += " ORDER BY created_at DESC" with get_conn() as conn: df = pd.read_sql_query(q, conn, params=params) return df @st.cache_data(ttl=30, show_spinner=False) def _feedback_stats_for_user(user_id: str | None, days: int | None): """ Returns a dict with helpful_count (feedback=1), not_helpful_count (feedback=0), total_feedback, helpful_pct — filtered by user and timeframe. """ q = """ SELECT COUNT(*) AS total_feedback, SUM(CASE WHEN feedback = 1 THEN 1 ELSE 0 END) AS helpful_count, SUM(CASE WHEN feedback = 0 THEN 1 ELSE 0 END) AS not_helpful_count, ROUND(100.0 * SUM(CASE WHEN feedback = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS helpful_pct FROM chat_history WHERE (role = 'user' OR role IS NULL) """ params = [] if user_id: q += " AND user_id = ?" params.append(user_id) if days is not None: # inclusive lower bound by timestamp since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S") q += " AND created_at >= ?" params.append(since) with get_conn() as conn: row = pd.read_sql_query(q, conn, params=params).iloc[0].to_dict() # normalize Nones for k in ("total_feedback", "helpful_count", "not_helpful_count"): row[k] = int(row.get(k) or 0) row["helpful_pct"] = float(row.get("helpful_pct") or 0.0) return row @st.cache_data(show_spinner=False, ttl=30) def get_unrated_conversations_for_user(user_id: int, limit: int = 100): sql = """ WITH user_convos AS ( SELECT conversation_id, MAX(created_at) AS last_time FROM chat_history WHERE user_id = ? AND conversation_id IS NOT NULL GROUP BY conversation_id ), rated AS ( SELECT DISTINCT conversation_id FROM chat_history WHERE user_id = ? AND conversation_id IS NOT NULL AND feedback_value IS NOT NULL ) SELECT uc.conversation_id, uc.last_time FROM user_convos uc WHERE uc.conversation_id NOT IN (SELECT conversation_id FROM rated) ORDER BY uc.last_time DESC LIMIT ? """ with get_conn() as conn: df = pd.read_sql_query(sql, conn, params=[user_id, user_id, limit]) return df def record_conversation_feedback(get_conn, *, user_id: int, conversation_id: str, value: int, text: str | None = None) -> int: """ Insert a single feedback row. Returns inserted row id. value: +1 for thumbs up, -1 for thumbs down """ now = datetime.utcnow().isoformat(timespec="seconds") fb_logger.info("record_conversation_feedback called user=%s cid=%s value=%s text=%s", user_id, conversation_id, value, (text or "")) with get_conn() as conn: # Safety: if already rated, log & short-circuit cur_check = conn.execute( """ SELECT id, feedback_value, feedback_at FROM chat_history WHERE user_id = ? AND conversation_id = ? AND feedback_value IS NOT NULL ORDER BY feedback_at DESC LIMIT 1 """, (user_id, conversation_id), ) existing = cur_check.fetchone() if existing: fb_logger.warning("Feedback already exists for user=%s cid=%s -> row=%s", user_id, conversation_id, existing) return existing[0] sql = """ INSERT INTO chat_history ( user_id, role, conversation_id, input_text, response_text, sentiment, intent, feedback_value, feedback_text, feedback_at, created_at ) VALUES (?,?,?,?,?,?,?,?,?,?,?) """ params = [ user_id, "user", conversation_id, "[feedback]", None, "neutral", "feedback", value, text, now, now, ] fb_logger.debug("About to INSERT feedback: %s", params) cur = conn.execute(sql, params) conn.commit() rowid = cur.lastrowid fb_logger.info("Feedback INSERT committed rowid=%s", rowid) # Verify readback cur2 = conn.execute("SELECT conversation_id, feedback_value, feedback_at FROM chat_history WHERE id=?", (rowid,)) row2 = cur2.fetchone() fb_logger.debug("Readback row: %s", row2) return rowid import sqlite3 from datetime import datetime def _ensure_sql_string(x): """Coerce validator output into a SQL string.""" if x is None: return "" if isinstance(x, str): return x # common shapes: (sql, reason) or [sql, reason] if isinstance(x, (tuple, list)) and x: return _ensure_sql_string(x[0]) # dict shapes from some planners/validators if isinstance(x, dict): for k in ("secured", "sql", "text", "query", "original"): v = x.get(k) if isinstance(v, str): return v # last resort: stringify return str(x) def update_chat_feedback(chat_id: int, feedback_value: int) -> Tuple[bool, str]: """ Update feedback for the most recent chat (current session) regardless of owner. Returns (ok, msg). """ fb_logger.info("update_chat_feedback -- start chat_id=%s value=%s", chat_id, feedback_value) try: with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: cur = conn.execute( """ UPDATE chat_history SET feedback_value = ?, feedback_at = CURRENT_TIMESTAMP WHERE chat_id = ? """, (feedback_value, chat_id), ) conn.commit() if cur.rowcount == 0: fb_logger.warning("update_chat_feedback -- no row updated chat_id=%s", chat_id) return False, "Could not record feedback (chat not found)." fb_logger.info("update_chat_feedback -- success chat_id=%s value=%s", chat_id, feedback_value) return True, "Thanks for the feedback!" except sqlite3.OperationalError as e: fb_logger.error("sqlite operational error on feedback update chat_id=%s err=%s", chat_id, e) return False, "Database busy—please try again." except Exception as e: fb_logger.exception("unexpected error on feedback update chat_id=%s", chat_id) return False, "Failed to record feedback." def get_chat_feedback(chat_id: int) -> Optional[int]: """Return 1/0 if feedback exists, else None.""" try: with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: cur = conn.execute( "SELECT feedback_value FROM chat_history WHERE chat_id = ?", (chat_id,), ) row = cur.fetchone() if not row: return None return row[0] if row[0] is not None else None except Exception as e: fb_logger.error("get_chat_feedback failed chat_id=%s err=%s", chat_id, e) return None def update_chat_feedback_if_owner(chat_id: int, user_id: str, feedback_value: int) -> Tuple[bool, str]: """ Update feedback only if the given user owns the chat row. Used in 'Rate Earlier Conversations'. """ fb_logger.info("update_chat_feedback_if_owner called chat_id=%s user_id=%s value=%s", chat_id, user_id, feedback_value) try: with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: cur = conn.execute( """ UPDATE chat_history SET feedback_value = ?, feedback_at = CURRENT_TIMESTAMP WHERE chat_id = ? AND user_id = ? """, (feedback_value, chat_id, user_id), ) conn.commit() if cur.rowcount == 0: fb_logger.warning("update_chat_feedback_if_owner -- no row updated chat_id=%s user_id=%s", chat_id, user_id) return False, "You can only rate your own conversations." fb_logger.info("update_chat_feedback_if_owner -- success chat_id=%s value=%s", chat_id, feedback_value) return True, "Thanks for the feedback!" except sqlite3.OperationalError as e: fb_logger.error("sqlite operational error on feedback update chat_id=%s err=%s", chat_id, e) return False, "Database busy—please try again." except Exception as e: fb_logger.exception("unexpected error on owner feedback update chat_id=%s user_id=%s", chat_id, user_id) return False, "Failed to record feedback." def render_rate_earlier_conversation_tab(auth): st.subheader("Rate Earlier Conversations") fb_logger.info("Rate tab opened for user_id=%s", auth["user_id"]) unrated = get_unrated_conversations_for_user(auth["user_id"], limit=200) fb_logger.info("Unrated conversations fetched: %d", len(unrated)) if unrated.empty: st.success("No pending conversations to rate. 🙌") return # Use the actual conversation_id as the select value; label just for display. conv_ids = unrated["conversation_id"].tolist() def _conv_label(cid: str) -> str: with get_conn() as conn: row = conn.execute( """ SELECT created_at, input_text FROM chat_history WHERE conversation_id = ? AND user_id = ? ORDER BY created_at DESC LIMIT 1 """, (cid, auth["user_id"]), ).fetchone() ts = row[0] if row else "" prev = (row[1][:60] + "…") if row and row[1] else "" return f"#{cid} • {ts} • {prev}" selected_conversation_id = st.selectbox( "Pick a conversation to preview & rate:", options=conv_ids, format_func=_conv_label, index=0, key="select_conv_to_rate", ) fb_logger.debug("Selected conversation_id=%s", selected_conversation_id) if st.button("Preview Result", key="preview_conv"): fb_logger.info("Preview clicked for cid=%s", selected_conversation_id) with get_conn() as conn: sel = pd.read_sql_query( """ SELECT * FROM chat_history WHERE conversation_id = ? AND user_id = ? ORDER BY created_at DESC LIMIT 10 """, conn, params=[selected_conversation_id, auth["user_id"]] ) st.markdown("**Recent Messages**") if sel.empty: st.info("No rows found to preview for this conversation.") else: for _, r in sel.iterrows(): who = r.get("role", "user") txt = r.get("response_text") if who != "user" else r.get("input_text") st.markdown(f"**{who.capitalize()}** — {r.get('created_at','')}") st.code(str(txt or "").strip()[:2000], language="markdown") # (Optional) add your SQL preview here as before cols = st.columns([1,1,3]) with cols[0]: up = st.button("👍 Thumbs Up", use_container_width=True, key="fb_up") with cols[1]: down = st.button("👎 Thumbs Down", use_container_width=True, key="fb_down") with cols[2]: fb_txt = st.text_input("Optional note", key="fb_note") if up or down: value = 1 if up else -1 fb_logger.info("Feedback button pressed: value=%s cid=%s note=%s", value, selected_conversation_id, fb_txt) try: rowid = record_conversation_feedback( get_conn, user_id=auth["user_id"], conversation_id=selected_conversation_id, value=value, text=(fb_txt or None), ) st.success(_localize_msg("Thanks! Your feedback has been recorded.", src_lang)) fb_logger.info("Feedback saved rowid=%s; clearing caches and rerunning", rowid) # Clear caches so the just-rated convo disappears immediately try: get_unrated_conversations_for_user.clear() fb_logger.debug("Cleared cache for get_unrated_conversations_for_user") except Exception as e: st.cache_data.clear() fb_logger.warning("Fallback cache clear used: %s", e) st.rerun() except Exception as e: fb_logger.exception("Feedback insert failed for cid=%s", selected_conversation_id) st.error("Failed to save feedback. Please try again.") def _kpi_number(label, value, help_text=None): c1 = st.container(); c1.metric(label, value) if help_text: c1.caption(help_text) def _safe_pct(n, d): return (100.0 * n / d) if d else 0.0 def _json_obj(x): """Return Python object if x is JSON (or already a py obj); else None.""" if x is None: return None if isinstance(x, (dict, list)): return x if isinstance(x, str): s = x.strip() if not s: return None try: return json.loads(s) except Exception: return None return None def _json_list(x): """ Normalize x into a list. - If x is JSON list -> return it - If x is JSON dict -> try common keys - If x is plain string -> split on ; / newlines - Else -> [] """ obj = _json_obj(x) if obj is None: if isinstance(x, str): parts = [p.strip() for p in re.split(r"[;\n]+", x) if p.strip()] return parts return [] if isinstance(obj, list): return obj if isinstance(obj, dict): for k in ("secured_sql", "sql", "queries", "items"): v = obj.get(k) if isinstance(v, list): return v return [] def _json_plan_queries(x): try: obj = json.loads(x) if isinstance(x, str) else (x or {}) qs = obj.get("queries", []) return qs if isinstance(qs, list) else [] except Exception: return [] def _norm_sql(s: str) -> str: if not isinstance(s, str): return "" return re.sub(r"\s+", " ", s).strip().upper() def _auto_repaired(row) -> bool: plan_qs = _json_plan_queries(row.get("llm_plan")) sec_qs = _json_list(row.get("secured_sql")) if len(plan_qs) != len(sec_qs): return True for a, b in zip(plan_qs, sec_qs): if _norm_sql(a) != _norm_sql(b): return True return False def _pill(text: str, tone: str = "neutral"): """Render a small rounded pill; tone in {'positive','neutral','negative'}.""" palette = { "positive": ("#065f46", "#d1fae5"), # dark text, light bg "neutral": ("#374151", "#e5e7eb"), "negative": ("#7f1d1d", "#fee2e2"), } fg, bg = palette.get(tone, palette["neutral"]) return f""" {text} """ def render_chat_output(out): import streamlit as st import pandas as pd # Normalize objects out = out or {} plan = out.get("plan") or {} queries = out.get("queries") or [] if out.get("src_lang"): src_lang = out.get("src_lang") else: src_lang = "en" # Prefer a simple chat bubble if 'text' is present if out.get("text"): st.chat_message("assistant").write(out["text"]) return # <-- IMPORTANT: stop here, do not render tables/cards # Detect policy/guard conditions blocked = bool(plan.get("blocked") or out.get("blocked") or any((q or {}).get("blocked_reason") for q in queries)) # Prefer explicit display message if present display_msg = plan.get("explanation_display") or out.get("msg") or plan.get("explanation") # If this is a hard block, show the guardrail and EXIT early if blocked: st.warning(display_msg or "This action is restricted.") # Also render any specific per-card warnings, if present for q in queries: br = (q or {}).get("blocked_reason") if br == "seller_revenue_user_block": st.warning("🔒 Seller revenue queries are not available to normal users. Please contact an admin.") if br == "product_revenue_user_block": st.warning("🔒 Product revenue queries are not available to normal users. Please contact an admin.") if br == "platform_payments_user_block": st.warning("🔒 Platform-wide payments are restricted to admins.") if br == "rowcounts_all_tables_block": st.warning("🔒 Requests for row counts across all tables / each table are restricted.") if br == "write_block": st.warning("🔒 Write/DDL operations are disabled.") if br == "introspection_user_block": st.warning("🔒 Listing tables/indexes/schema is admin-only.") if br == "dump_all_block": st.warning("🔒 Requests to list/dump all database data are restricted.") if br == "count_user_block": st.warning("🔒 Row-count (COUNT) queries are restricted for normal users.") if br == "customers_user_block": st.warning("🔒 Access to the **customers** table is restricted for normal users.") if br == "destructive_request_block": st.warning("🔒 Destructive actions (delete/update/drop) are not allowed.") if (q or {}).get("policy_note"): st.info(q["policy_note"]) return # Not blocked: optionally show any informative message, but DO NOT exit early if display_msg: # Demote to info so results still render below st.info(display_msg) # --------------------------------------------------------------------- # ✅ Reconciled UI block (as requested): renders tables/queries/results # --------------------------------------------------------------------- _assistant_msg = "Pls find below the data for the input you provided" with st.chat_message("assistant"): st.write(_localize_msg(_assistant_msg, src_lang)) cols = st.columns(max(1, len(queries))) for i, card in enumerate(queries): card = card or {} with cols[i]: pill = "Limited to 50" if card.get("limit_enforced") else None if SHOW_DETAILS: card_open(f"Query {i+1}", pill) sql_text = (card.get("secured") or card.get("original") or "").strip() st.markdown('
', unsafe_allow_html=True) st.code(sql_text if sql_text else "-- no SQL generated --", language="sql") st.markdown('
', unsafe_allow_html=True) br = card.get("blocked_reason") if br == "seller_revenue_user_block": st.warning("🔒 Seller revenue queries are not available to normal users. Please contact an admin.") if br == "product_revenue_user_block": st.warning("🔒 Product revenue queries are not available to normal users. Please contact an admin.") if br == "platform_payments_user_block": st.warning("🔒 Platform-wide payments are restricted to admins.") if br == "rowcounts_all_tables_block": st.warning("🔒 Requests for row counts across all tables / each table are restricted.") if br == "write_block": st.warning("🔒 Write/DDL operations are disabled.") if br == "introspection_user_block": st.warning("🔒 Listing tables/indexes/schema is admin-only.") if br == "dump_all_block": st.warning("🔒 Requests to list/dump all database data are restricted.") if br == "count_user_block": st.warning("🔒 Row-count (COUNT) queries are restricted for normal users.") if br == "customers_user_block": st.warning("🔒 Access to the **customers** table is restricted for normal users.") if br == "destructive_request_block": st.warning("🔒 Destructive actions (delete/update/drop) are not allowed.") if card.get("policy_note"): st.info(card["policy_note"]) if card.get("error"): st.error(card["error"]) else: df_to_show = card.get("df", None) if isinstance(df_to_show, pd.DataFrame): if card.get("limit_enforced"): st.markdown( '
For performance, results are limited to 50 rows. ' 'Include the word “All” in your question to bypass this cap (may be slower).
', unsafe_allow_html=True ) if df_to_show.empty: st.info("No rows returned.") else: _role = (st.session_state.get("auth") or {}).get("role", "").lower() if _role == "admin": cfg = _infer_columns_for_chart(df_to_show) if cfg: c_tbl, c_plt = st.columns(2, gap="large") with c_tbl: _call_render_df(df_to_show, key_prefix=f"q{i}", page_size=10) with c_plt: _render_chart_matplotlib(cfg, df_to_show) else: _call_render_df(df_to_show, key_prefix=f"q{i}", page_size=10) else: _call_render_df(df_to_show, key_prefix=f"q{i}", page_size=10) else: st.info("No tabular result to display.") def render_df_with_pagination(df: pd.DataFrame, key_prefix: str, page_size: int = 10): total = len(df) if total <= page_size: st.dataframe(df, use_container_width=True) st.caption(f"Showing 1–{total} of {total} rows.") return total_pages = math.ceil(total / page_size) page = st.number_input( "Page", min_value=1, max_value=total_pages, value=1, step=1, key=f"{key_prefix}_page" ) start = (page - 1) * page_size end = min(start + page_size, total) st.dataframe(df.iloc[start:end], use_container_width=True) st.caption(f"Showing rows {start+1}–{end} of {total} (page {page}/{total_pages}).") def _apply_preview_limit(sql: str, limit: int = 10) -> str: """ Ensure the SQL has LIMIT <= limit for SELECT previews. If non-SELECT, return as-is. """ s = (sql or "").strip().rstrip(";") if not re.match(r"^\s*select\b", s, flags=re.I): return s m = re.search(r"\blimit\s+(\d+)\b", s, flags=re.I) if m: try: current = int(m.group(1)) if current > limit: s = re.sub(r"\blimit\s+\d+\b", f"LIMIT {limit}", s, flags=re.I) except Exception: s = f"{s} LIMIT {limit}" else: s = f"{s} LIMIT {limit}" return s # --- IST date helpers & audit DAO ------------------------------------------- from datetime import datetime, date try: from zoneinfo import ZoneInfo except ImportError: from backports.zoneinfo import ZoneInfo # only if needed IST = ZoneInfo("Asia/Kolkata") def ist_today() -> date: return datetime.now(IST).date() def _date_yyyymmdd(d: date) -> str: return d.strftime("%Y-%m-%d") def count_login_audit_range(start_d: date, end_d: date) -> int: with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: _apply_busy_timeout(conn) cur = conn.execute( """ SELECT COUNT(*) FROM login_audit WHERE date(created_at) BETWEEN ? AND ? """, (_date_yyyymmdd(start_d), _date_yyyymmdd(end_d)), ) row = cur.fetchone() return int(row[0] if row else 0) def get_login_audit_range(start_d: date, end_d: date, offset: int = 0, limit: int = 10) -> pd.DataFrame: with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: _apply_busy_timeout(conn) sql = """ SELECT id AS audit_id, created_at AS ts, email, user_id, success, reason FROM login_audit WHERE date(created_at) BETWEEN ? AND ? ORDER BY datetime(created_at) DESC LIMIT ? OFFSET ?; """ return pd.read_sql_query( sql, conn, params=(_date_yyyymmdd(start_d), _date_yyyymmdd(end_d), int(limit), int(offset)) ) # --- You already have: classify_sentiment_and_intent, insert_chat_history, # render_feedback_controls, admin utils, etc. Keeping that logic intact. # (Your existing functions are below, unchanged.) # ----------------------------- # Startup # ----------------------------- try: dbg("Starting bootstrap_db_once()") _init_status = bootstrap_db_once() # cached, runs once per process dbg(f"DB init bootstrap status: {_init_status}") schema_registry = load_schema_registry() dbg("Schema loaded") except Exception as e: st.error("Startup failed.") st.exception(e) st.stop() # ----------------------------- # Auth Gate # ----------------------------- if "auth" not in st.session_state: st.session_state["auth"] = None if st.session_state.get("auth") is None: if USE_BRANDING: ui_header(None) st.info("Login required") auth_obj = simple_login_screen() if auth_obj: st.session_state["auth"] = auth_obj st.rerun() st.stop() auth = st.session_state["auth"] if USE_BRANDING: ui_header(auth) with st.sidebar: st.success(f"Signed in as {auth['email']} ({auth['role']})") if auth["role"] == "user": st.caption(f"customer_id: {auth['customer_id']}") if st.button("Sign out"): st.session_state["auth"] = None st.rerun() def ensure_rag_published_schema(conn: sqlite3.Connection): # Minimal ledger to prevent re-publishing the same chat row to RAG. # Stores an arbitrary primary key string for the source row. conn.execute(""" CREATE TABLE IF NOT EXISTS rag_published ( pub_key TEXT PRIMARY KEY, created_at TEXT DEFAULT CURRENT_TIMESTAMP ); """) try: conn.execute("CREATE INDEX IF NOT EXISTS idx_rag_pub_created ON rag_published(created_at);") except Exception: pass conn.commit() def mark_published(pub_key: str): if not pub_key: return try: with sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) as _conn: _conn.execute("INSERT OR IGNORE INTO rag_published (pub_key) VALUES (?)", (str(pub_key),)) _conn.commit() except Exception as e: logger.warning("mark_published failed for key=%s: %s", pub_key, e) def publish_exemplar_from_row(row: dict, registry: dict, policy_version: str = "v1"): try: intent = row.get("intent") or "unknown" question = row.get("user_input") or "" plan_after = row.get("plan_after") or row.get("llm_plan") or "" sql_after = row.get("sql_after") or row.get("secured_sql") or "" sql_after = _ensure_sql_string(sql_after) schema_fp = _schema_fingerprint(registry) # make a simple, deterministic tables_used string sql_lc = (sql_after or "").lower() used = sorted([t for t in registry.keys() if t.lower() in sql_lc]) tables_used_str = ",".join(used) # <-- string, not list ex_text = exemplar_text( intent, question, schema_fp, plan_after if isinstance(plan_after, str) else json.dumps(plan_after, ensure_ascii=False), sql_after ) ex_id = hashlib.sha256( (intent + "|" + canonicalize_sql(sql_after) + "|" + schema_fp).encode("utf-8") ).hexdigest()[:24] metadata = { "intent": intent, # str "schema_fp": schema_fp, # str "tables_used": tables_used_str, # <-- now str "policy_version": policy_version, # str "created_at": datetime.utcnow().isoformat(), # str } RAG.upsert_exemplar(ex_id, ex_text, metadata) return ex_id except Exception as e: logger.exception("Failed to publish exemplar: %s", e) return None def admin_rag_publisher(registry: dict): #st.header("RAG Publisher (Admin)") st.caption("Publish vetted exemplars into the retrieval store to improve future SQL generation.") with st.expander("Build from recent positive chats", expanded=True): try: STOP_INTENTS = {"help_greeting", "thanks", "farewell", "smalltalk", "joke", "chitchat"} # ---------- 1) Discover chat_history schema ---------- with get_conn() as conn: cols_df = pd.read_sql_query("PRAGMA table_info(chat_history);", conn) # Ensure rag_published exists ensure_rag_published_schema(conn) cols = set(c.lower() for c in cols_df["name"].astype(str)) # Core columns (fallbacks) if "id" in cols: id_col_expr = "id" elif "chat_id" in cols: id_col_expr = "chat_id" else: id_col_expr = "rowid" id_select = f"CAST({id_col_expr} AS TEXT) AS pub_key" ts_candidates = ["created_at", "createdAt", "timestamp", "ts", "time"] ts_col = next((c for c in ts_candidates if c.lower() in cols), None) fb_col = "feedback_value" if "feedback_value" in cols else ("feedback" if "feedback" in cols else None) if fb_col is None: st.warning("No feedback column found in chat_history; cannot filter positives.") st.stop() # Only positives fb_pred = f"{fb_col} = 1" if fb_col == "feedback_value" else f"LOWER({fb_col}) IN ('up','thumbs_up','positive','1','true','yes')" # SQL column sql_col = "secured_sql" if "secured_sql" in cols else ("sql" if "sql" in cols else None) if sql_col is None: st.warning("No SQL column (secured_sql/sql) found in chat_history.") st.stop() # Optional columns user_id_col = "user_id" if "user_id" in cols else None intent_col = "intent" if "intent" in cols else None question_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else ("question" if "question" in cols else None)) # ---------- 2) Filters ---------- colA, colB, colC = st.columns([1,1,1]) with colA: end_date = st.date_input("End date", datetime.utcnow().date()) with colB: start_date = st.date_input("Start date", end_date - timedelta(days=30)) with colC: max_rows = st.number_input("Max rows", min_value=10, max_value=1000, value=200, step=10) intent_like = st.text_input("Filter by intent (optional, substring match)", "") # ---------- 3) Build SELECT + WHERE (exclude empty SQL & chit-chat intents & already-published) ---------- select_cols = [id_select] if ts_col: select_cols.append(ts_col) if user_id_col: select_cols.append(user_id_col) if intent_col: select_cols.append(intent_col) if question_col: select_cols.append(question_col) select_cols.append(sql_col) select_list = ", ".join(select_cols) where = [fb_pred] # Exclude empty/[]/null SQL where.append(f"TRIM(COALESCE({sql_col}, '')) <> ''") where.append(f"LOWER(TRIM(COALESCE({sql_col}, ''))) NOT IN ('[]','null','none')") params = [] if ts_col: where.append(f"{ts_col} BETWEEN ? AND ?") params.extend([f"{start_date} 00:00:00", f"{end_date} 23:59:59"]) if intent_col: placeholders = ",".join(["?"] * len(STOP_INTENTS)) where.append(f"LOWER({intent_col}) NOT IN ({placeholders})") params.extend([s.lower() for s in STOP_INTENTS]) if intent_like.strip() and intent_col: where.append(f"{intent_col} LIKE ?") params.append(f"%{intent_like.strip()}%") where_sql = " AND ".join(where) order_sql = f"ORDER BY {ts_col} DESC" if ts_col else "ORDER BY rowid DESC" base_sql = f""" SELECT {select_list} FROM chat_history ch WHERE {where_sql} AND NOT EXISTS ( SELECT 1 FROM rag_published rp WHERE rp.pub_key = CAST({id_col_expr} AS TEXT) ) {order_sql} LIMIT ? """ params.append(int(max_rows)) with get_conn() as conn: df = pd.read_sql_query(base_sql, conn, params=params) # ---------- 4) Extra Python-side safety ---------- if not df.empty: def _has_sql(x: str) -> bool: s = str(x or "").strip().lower() return len(s) > 0 and s not in {"[]", "null", "none"} df = df[df[sql_col].map(_has_sql)] if intent_col and intent_col in df.columns: df = df[~df[intent_col].astype(str).str.lower().isin(STOP_INTENTS)] if df.empty: st.success("No eligible positive chats to publish (after filters and dedup).") st.stop() # ---------- 5) Selectable grid (max 50) ---------- preview_cols = [] for c in ["pub_key", ts_col, user_id_col, intent_col, question_col, sql_col]: if c and c in df.columns: preview_cols.append(c) view = df[preview_cols].copy() def _ui_safe_cell_local(x): import json try: if isinstance(x, (dict, list)): return json.dumps(x, ensure_ascii=False) return str(x) except Exception: return str(x) view = view.map(_ui_safe_cell_local) view.insert(0, "Select", False) st.caption("Tick up to 50 rows to publish.") edited = st.data_editor( view.head(200), use_container_width=True, hide_index=True, key="rag_pos_editor", column_config={"Select": st.column_config.CheckboxColumn(required=False)}, ) selected_rows = edited[edited["Select"]] sel_count = int(len(selected_rows)) st.caption(f"Selected: {sel_count} (max 50)") disabled = sel_count == 0 or sel_count > 50 if st.button("Publish selected → RAG", key="btn_pub_selected", type="primary", disabled=disabled, use_container_width=True): ok = 0 skipped = 0 for _, row in selected_rows.iterrows(): payload = { "intent": row.get(intent_col, "") if intent_col else "", "user_input": row.get(question_col, "") if question_col else "", "llm_plan": row.get("llm_plan", ""), "plan_after": row.get("llm_plan", ""), "secured_sql": row.get(sql_col, ""), "sql_after": row.get(sql_col, ""), } # Ensure plain string SQL sql_after = payload.get("sql_after") or "" if not isinstance(sql_after, str): try: import json as _json sql_after = _json.dumps(sql_after, ensure_ascii=False) except Exception: sql_after = str(sql_after) payload["sql_after"] = sql_after try: ex_id = publish_exemplar_from_row(payload, registry) if ex_id: ok += 1 pk = str(row.get("pub_key") or "") mark_published(pk) else: skipped += 1 except Exception as e: skipped += 1 logger.exception("publish error: %s", e) st.success(f"Published {ok} exemplars to RAG. Skipped {skipped}.") st.rerun() except Exception as e: st.warning(f"Render error in 'positive chats' panel: {e}") with st.expander("Manual publish from a specific chat row", expanded=False): try: # Discover columns with get_conn() as conn: cols_df = pd.read_sql_query("PRAGMA table_info(chat_history);", conn) cols = set(str(n).lower() for n in cols_df["name"].astype(str)) # Candidate lookup keys, in priority order candidates = ["conversation_id", "conv_id", "session_id", "thread_id", "message_id", "turn_id", "id"] avail_keys = [c for c in candidates if c in cols] # rowid fallback if none of the above exist use_rowid = False if not avail_keys: avail_keys = ["rowid"] use_rowid = True # Pick a key and input value key_col = st.selectbox("Lookup key column", options=avail_keys, index=0) key_val = st.text_input("Key value (exact match)") # Timestamp / ordering column (optional) ts_candidates = ["created_at", "createdAt", "timestamp", "ts", "time"] ts_col = next((c for c in ts_candidates if c.lower() in cols), None) order_sql = f"ORDER BY {ts_col} DESC" if ts_col else "ORDER BY rowid DESC" if st.button("Fetch & Publish exemplar", key="btn_pub_one_row", disabled=not key_val): # Build query safely where_col = "rowid" if (use_rowid and key_col == "rowid") else key_col select_list = "*" base_sql = f"SELECT {select_list} FROM chat_history WHERE {where_col} = ? {order_sql} LIMIT 1" with get_conn() as conn: df_one = pd.read_sql_query(base_sql, conn, params=[key_val]) if df_one.empty: st.warning("No row found for that key/value.") else: row = df_one.iloc[0].to_dict() # Prepare payload for publisher (flexible keys) payload = { "intent": row.get("intent", ""), "user_input": row.get("user_input", row.get("question", "")), "llm_plan": row.get("llm_plan", ""), "plan_after": row.get("llm_plan", ""), "secured_sql": row.get("secured_sql", row.get("sql", "")), "sql_after": row.get("secured_sql", row.get("sql", "")), } ex_id = publish_exemplar_from_row(payload, registry) if ex_id: st.success(f"Published exemplar {ex_id}") else: st.error("Failed to publish exemplar.") except Exception as e: st.warning(f"Manual publish error: {e}") def render_rag_publisher(): """ Admin tool to curate positively-rated chats and push to a RAG collection. Adds debug counters + toggles so empty grids can be diagnosed quickly. """ import streamlit as st, sqlite3, os, datetime, json # Role gate auth = st.session_state.get("auth") or {} if (auth.get("role") or "").lower() != "admin": st.warning("RAG Publisher is available to admins only.") return # DB db_path = os.environ.get("DB_PATH", "/data/olist.sqlite") if not os.path.exists(db_path): st.error(f"Database not found at {db_path}") return conn = sqlite3.connect(db_path) conn.row_factory = sqlite3.Row # Track already-published ids try: conn.execute("""CREATE TABLE IF NOT EXISTS rag_published ( chat_id INTEGER PRIMARY KEY, pushed_at TEXT, collection TEXT )""") conn.commit() except Exception: pass # Discover schema try: cols = [r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()] except Exception as e: st.error("Table `chat_history` not found or unreadable.") try: st.exception(e) except Exception: pass conn.close(); return pk = "chat_id" if "chat_id" in cols else ("id" if "id" in cols else None) created = next((c for c in ["created_at","timestamp","ts","time","dt"] if c in cols), None) fb = "feedback" if "feedback" in cols else ("feedback_value" if "feedback_value" in cols else None) role = next((c for c in ["user_role","role"] if c in cols), None) # Intent column; we’ll match case-insensitively and tolerate NULLs intent_col = next((c for c in ["intent", "intent_label", "non_sql_label", "label"] if c in cols), None) default_excluded = {"greeting", "thanks", "smalltalk", "complaint", "others"} inp_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else None) plan_col = "llm_plan" if "llm_plan" in cols else None sql_col = "secured_sql" if "secured_sql" in cols else None # metadata only if not pk or not fb: st.error("`chat_history` must have a primary key and a feedback column (feedback / feedback_value).") conn.close(); return # Controls today = datetime.date.today() default_start = today - datetime.timedelta(days=7) c1, c2, c3 = st.columns([1,1,1]) with c1: start_date = st.date_input("From date", value=st.session_state.get("rag_from") or default_start, key="rag_from") with c2: end_date = st.date_input("To date", value=st.session_state.get("rag_to") or today, key="rag_to") with c3: max_fetch = st.number_input("Max rows", min_value=10, max_value=1000, value=200, step=10) if start_date > end_date: st.error("From date cannot be after To date.") conn.close(); return # Build WHERE where = [] params = [] # Positive feedback where.append(f"{fb} = 1") # End-user role (case-insensitive) if role: where.append(f"({role} IS NULL OR LOWER({role})='user')") # Date range if created: where.append(f"{created} BETWEEN ? AND ?") params += [f"{start_date} 00:00:00", f"{end_date} 23:59:59"] # Exclude already published unless debug override if not include_published: where.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)") # Exclude “chit-chat” intents, if the column is present and not disabled intent_params = [] if intent_col and not disable_intent_exclusion: ex = sorted(default_excluded) placeholders = ",".join(["?"] * len(ex)) # NULL intent rows should be kept; use COALESCE to empty string so they don’t match where.append(f"COALESCE(LOWER({intent_col}), '') NOT IN ({placeholders})") intent_params = [s.lower() for s in ex] # SELECT list select_cols = [f"{pk} AS chat_id"] select_cols += [f"{created} AS created_at"] if created else ["NULL AS created_at"] select_cols += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"] select_cols += [f"{plan_col} AS llm_plan"] if plan_col else ["NULL AS llm_plan"] select_cols += [f"{sql_col} AS secured_sql"] if sql_col else ["NULL AS secured_sql"] order_by = f"ORDER BY {created} DESC" if created else f"ORDER BY {pk} DESC" # ---- Debug counters to see where things drop ---- def _count(sql_where: str, args: list): try: return conn.execute(f"SELECT COUNT(1) FROM chat_history WHERE {sql_where}", args).fetchone()[0] except Exception: return None # stage 1: positive only where1 = [w for w in where if True] # remove date / intent / published / role progressively for introspection base_pos = _count(f"{fb} = 1", []) base_role = _count(" AND ".join([f"{fb}=1"] + ([f"({role} IS NULL OR LOWER({role})='user')"] if role else [])), []) base_date = None if created: base_date = _count(" AND ".join([f"{fb}=1"] + ([f"({role} IS NULL OR LOWER({role})='user')"] if role else []) + [f"{created} BETWEEN ? AND ?"]), [f"{start_date} 00:00:00", f"{end_date} 23:59:59"]) base_published = None if not include_published: _w = [f"{fb}=1"] if role: _w.append(f"({role} IS NULL OR LOWER({role})='user')") if created: _w.append(f"{created} BETWEEN ? AND ?") _w.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)") _p = ([f"{start_date} 00:00:00", f"{end_date} 23:59:59"] if created else []) base_published = _count(" AND ".join(_w), _p) base_intent = None if intent_col and not disable_intent_exclusion: _w = [f"{fb}=1"] if role: _w.append(f"({role} IS NULL OR LOWER({role})='user')") if created: _w.append(f"{created} BETWEEN ? AND ?") if not include_published: _w.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)") _w.append(f"COALESCE(LOWER({intent_col}), '') NOT IN ({','.join(['?']*len(default_excluded))})") _p = [] if created: _p += [f"{start_date} 00:00:00", f"{end_date} 23:59:59"] _p += [s.lower() for s in sorted(default_excluded)] base_intent = _count(" AND ".join(_w), _p) # Final query sql = f""" SELECT {", ".join(select_cols)} FROM chat_history WHERE {" AND ".join(where)} {order_by} LIMIT ? """ params += intent_params + [int(max_fetch)] rows = [] try: rows = conn.execute(sql, params).fetchall() except Exception as e: st.error(f"Failed to query chat_history: {e}") try: st.exception(e) except Exception: pass conn.close(); return # Show debug summary so you can see which filter bites with st.expander("Debug: filter counts", expanded=False): st.write({ "positive_only": base_pos, "positive_plus_role": base_role, "after_date": base_date, "after_excluding_published": base_published, "after_intent_exclusion": base_intent, "final_rows": len(rows), "excluded_intents": sorted(default_excluded) if (intent_col and not disable_intent_exclusion) else "N/A", "intent_column_used": intent_col or "N/A", }) # LLM plan preview helper def _plan_preview(raw) -> str: try: if not raw: return "" if isinstance(raw, (bytes, bytearray)): raw = raw.decode("utf-8", "ignore") obj = json.loads(raw) if isinstance(raw, str) else raw for k in ("explanation_display","explanation","message","text"): v = obj.get(k) if v: s = str(v).strip() return s if len(s) <= 300 else s[:300] + "…" return json.dumps(obj, ensure_ascii=False)[:300] + "…" except Exception: s = str(raw) return s if len(s) <= 300 else s[:300] + "…" import pandas as pd data = [] for r in rows: data.append({ "select": False, "chat_id": int(r["chat_id"]), "created_at": r["created_at"], "user_input": (r["user_input"] or "").strip() if "user_input" in r.keys() else "", "llm_plan": _plan_preview(r["llm_plan"]) if "llm_plan" in r.keys() else "", }) if not data: st.info("No eligible positively-rated chats for the chosen filters. Use the debug box above to identify the blocking filter.") conn.close(); return st.caption("Select up to 50 records to publish to RAG") df = pd.DataFrame(data) edited = st.data_editor( df, num_rows="fixed", column_config={ "select": st.column_config.CheckboxColumn("Select", help="Pick rows to publish", default=False), "chat_id": st.column_config.NumberColumn("chat_id", disabled=True), "created_at": st.column_config.TextColumn("created_at", disabled=True), "user_input": st.column_config.TextColumn("User input", width="large", disabled=True), "llm_plan": st.column_config.TextColumn("LLM plan (preview)", width="large", disabled=True), }, hide_index=True, key="rag_grid_ui", use_container_width=True, ) selected_ids = [int(row.chat_id) for _, row in edited.iterrows() if bool(row.select)] st.write(f"Selected: **{len(selected_ids)}**") if len(selected_ids) > 50: st.error("You can select at most 50 records at a time.") conn.close(); return st.markdown("---") coll = st.text_input("Collection name", value=os.environ.get("RAG_COLLECTION","rag_feedback")) push = st.button("Publish selected to RAG") if push: if not selected_ids: st.warning("Please select at least one row.") conn.close(); return qmarks = ",".join(["?"] * len(selected_ids)) sql2 = f""" SELECT {pk} AS chat_id, {inp_col if inp_col else "NULL"} AS user_input, {plan_col if plan_col else "NULL"} AS llm_plan, {sql_col if sql_col else "NULL"} AS secured_sql FROM chat_history WHERE {pk} IN ({qmarks}) {order_by} """ try: chosen = conn.execute(sql2, selected_ids).fetchall() except Exception as e: st.error(f"Failed to fetch selected rows: {e}") try: st.exception(e) except Exception: pass conn.close(); return def _plan_text(raw) -> str: try: if not raw: return "" if isinstance(raw, (bytes, bytearray)): raw = raw.decode("utf-8", "ignore") obj = json.loads(raw) if isinstance(raw, str) else raw for k in ("explanation_display","explanation","message","text"): v = obj.get(k); if v: return str(v).strip() return json.dumps(obj, ensure_ascii=False) except Exception: return str(raw) docs, ids, metas = [], [], [] for r in chosen[:50]: cid = int(r["chat_id"]) uq = (r["user_input"] or "").strip() plan_txt = _plan_text(r["llm_plan"]) if "llm_plan" in r.keys() else "" doc = f"Q: {uq}\nPlan: {plan_txt}" docs.append(doc) ids.append(f"chat_{cid}") metas.append({ "chat_id": cid, "source": "feedback_curated", "secured_sql": (r["secured_sql"] or None) }) # RAG push try: import chromadb from chromadb.utils import embedding_functions _ensure_writable_model_caches() openai_key = os.environ.get("OPENAI_API_KEY") or os.environ.get("OPENAI_APIKEY") ef = None if openai_key: embed_model = os.environ.get("EMBED_MODEL", "text-embedding-3-small") ef = embedding_functions.OpenAIEmbeddingFunction(api_key=openai_key, model_name=embed_model) client = chromadb.PersistentClient(path=os.environ.get("CHROMA_PATH","/data/chroma")) try: rag = client.get_collection(coll) except Exception: rag = client.create_collection(coll, embedding_function=ef) if ef else client.create_collection(coll) if docs: rag.upsert(documents=docs, ids=ids, metadatas=metas) st.success(f"Pushed {len(docs)} item(s) to collection '{coll}'.") else: st.warning("Nothing to publish (no text available).") except Exception as e: st.error(f"RAG push failed: {e}") try: st.exception(e) except Exception: pass # Mark published (so they won’t show again) try: conn.executemany( "INSERT OR IGNORE INTO rag_published (chat_id, pushed_at, collection) VALUES (?, datetime('now'), ?)", [(cid, coll) for cid in selected_ids] ) conn.commit() except Exception: pass # Optional aggregate audit try: conn.execute("""CREATE TABLE IF NOT EXISTS rag_push_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, pushed_at TEXT, count INTEGER, collection TEXT )""") conn.execute("INSERT INTO rag_push_log(pushed_at, count, collection) VALUES (datetime('now'), ?, ?)", (len(docs), coll)) conn.commit() except Exception: pass conn.close() # ----------------------------- # Tabs (role-aware) # ----------------------------- # if auth["role"] == "admin": # tab_chat, tab_mis, tab_admin, tab_rag = st.tabs(["💬 Chat", "📊 MIS", "🛠️ Admin Utils","RAG Publisher"]) # else: # tab_chat, tab_mis, tab_rate, tab_rag = st.tabs(["💬 Chat", "📊 MIS", "⭐ Rate Earlier Conversations","RAG Publisher"]) # ## ---- Tabs (Customer 360 / Chat / MIS / Rate/Admin) ---- if auth.get('role') == 'admin': try: tab_chat, tab_mis, tab_admin, tab_rag = st.tabs(["💬 Chat", "📊 MIS", "🛠️ Admin Utils","RAG Publisher"]) except Exception: tab_chat, tab_mis, tab_admin, tab_rag = st.tabs(["Chat", "MIS", "Admin Utils","RAG Publisher"]) else: try: tab_c360, tab_chat, tab_mis, tab_rate = st.tabs(["👤 Customer 360", "💬 Chat", "📊 MIS", "⭐ Rate Earlier Conversations"]) except Exception: tab_c360, tab_chat, tab_mis, tab_rate = st.tabs(["Customer 360", "Chat", "MIS", "Rate Earlier Conversations"]) # ---- Customer 360 ---- if auth and auth.get("role") == "user": with tab_c360: st.header("Customer 360") st.info("Please use the Chat Tab for discussion with our Agent.") # Compact height for charts if present st.markdown("""""", unsafe_allow_html=True) try: if auth and auth.get("role") == "user": try: render_customer_360(auth.get("customer_id"), get_conn, db_path=None) except Exception: st.caption("Customer 360 is temporarily unavailable.") except Exception: st.caption("Customer 360 is temporarily unavailable.") # ---- Chat ---- with tab_chat: st.header("Chat with SmartAssist BOT") st.session_state.setdefault('transcript', []) # Clear Ask input on rerun if flagged if st.session_state.pop('__clear_input__', False): st.session_state['q_enabled'] = '' st.session_state['q_disabled'] = '' # (removed) Customer 360 preview inside Chat — shown only in Customer 360 tab now. if "last_chat_out" not in st.session_state: st.session_state.last_chat_out = None st.session_state.last_sentiment = None st.session_state.last_intent = None st.session_state.last_query_text = None st.session_state.last_chat_id = None unrated = ( st.session_state.last_chat_id is not None and get_chat_feedback(st.session_state.last_chat_id) is None ) ask_disabled = False blocked = False bypass_main_logic = False if FEEDBACK_MODE == "HARD_REQUIRED" and unrated: st.warning("Please rate the previous answer (👍/👎) to continue.") ask_disabled = True if auth and auth.get("role") == "user": try: show_weekly_topk_chips(get_conn(), k=5) except Exception: pass q = st.text_area("Ask a question", disabled=True, key="q_disabled") else: if FEEDBACK_MODE == "SOFT_REQUIRED" and unrated: st.info("Quick rating helps improve results. Please rate the previous answer above. 👍/👎") if auth and auth.get("role") == "user": try: show_weekly_topk_chips(get_conn(), k=5) except Exception: pass q = st.text_area("Ask a question", key="q_enabled") ask_clicked = st.button("Ask", disabled=ask_disabled) # History toggle below input st.session_state.setdefault('show_history', False) show_hist = st.checkbox('Display Conversation History (This Session)', key='show_history') # On non-click rerun, show latest conversation under input if not ask_clicked and st.session_state.get('last_chat_out') is not None: _last_user = st.session_state.get('last_query_text') or '' if _last_user: with st.chat_message('user'): st.write(_last_user) render_chat_output(st.session_state.last_chat_out) render_feedback_controls(st.session_state.get('last_chat_id'), key_ns="clicked") if ask_clicked: st.session_state["__clear_input__"] = True limited, rl_msg = check_rate_limit(auth["user_id"], auth["role"]) if limited: st.warning(rl_msg) else: st.caption(f"Rate limit status: {rl_msg}") # Detect & translate input to English (if Indic) src_lang = _detect_lang(q) # 'en'|'hi'|'mr' logger.info("[chat] src_lang=%s user_text=%s", src_lang, _sample(q, 200)) q_for_llm = _tx_to_en(q, src_lang) st.session_state['last_chat_out'] = None llm = get_llm_client(LLM_CHOICE) # >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> # NEW: Hard stop destructive requests BEFORE any other routing. # This prevents the “please provide order/customer id” branch # from catching delete/erase/cancel requests. if classify_destructive_intent_with_llm(llm, q_for_llm): logger.info("[chat] inside _intent_destructive condition ") final_msg = ( "I can’t delete or modify your data. " "I’m read-only and can only help you view or summarize your data. " "If you’d like to examine your latest order or payments, I can show those." ) logger.info("[chat] inside _intent_destructive - saving the data to chat history ") # persist the bot message exactly as you already do try: insert_chat_history( user_id=auth["user_id"], role="assistant", message=final_msg, plan_json=None, queries_json=None, ) except Exception: pass out = { "queries": [{ "kind": "non_sql", "df": pd.DataFrame({"message": [final_msg]}), "blocked_reason": "destructive_request_block", }], "plan": { "explanation": final_msg, "explanation_display": final_msg, }, "llm_time": 0.0, "sentiment": "Neutral", "intent": "destructive_block", "src_lang": st.session_state.get("src_lang", "en"), } bypass_main_logic = True st.session_state['last_chat_out'] = out st.session_state['last_query_text'] = q st.session_state.last_sentiment = "Neutral" st.session_state.last_intent = "Other" st.session_state.last_chat_id = None # Show it in the current run render_chat_output(out) render_feedback_controls(st.session_state.get("last_chat_id"), key_ns="curr_run") # Keep transcript consistent with the SQL path try: st.session_state.setdefault("transcript", []) st.session_state["transcript"].append({"user": q, "assistant": final_msg, "ts": time.time()}) except Exception: pass # Clear the input and trigger a rerun so the text area empties st.session_state["__clear_input__"] = True st.rerun() # <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< # Provide logged-in customer context so LLM can resolve 'my' correctly if auth.get('role') == 'user' and auth.get('customer_id'): _cid = auth.get('customer_id') context_hint = ( 'CONTEXT:\n' f'- Logged-in customer_id: {_cid}\n' "- Treat 'my'/'me' as this customer_id.\n" "- 'latest order' means the order with the maximum orders.order_purchase_timestamp for this customer.\n" "- Prefer writing a single SQL SELECT that answers the question directly.\n" "- For 'payment type for my latest order', join orders -> order_payments and select payment_type.\n" ) q_for_llm = context_hint + '\nQUESTION:\n' + q_for_llm logger.info("[chat] src_lang=%s user_text=%s", src_lang, _sample(q, 200)) logger.info("[chat] Value of NON_SQL_ROUTER=%s ", NON_SQL_ROUTER) logger.info("[chat] Value of bypass_main_logic=%s", bypass_main_logic) logger.info("[chat] Value of q is =%s", q) logger.info("[chat] Value of _is_my_account_query(q)=%s", _is_my_account_query(q)) if NON_SQL_ROUTER and not _is_my_account_query(q) and not bypass_main_logic: non_sql_label = detect_non_sql_intent(q) logger.info("[chat] non_sql_label=%s", non_sql_label) if non_sql_label: # 1) sentiment/intent (kept for MIS) try: sentiment, intent = classify_sentiment_and_intent(get_llm_client(LLM_CHOICE), q) except Exception: sentiment, intent = ("neutral", non_sql_label) # 2) Build an English base message, then translate safely if needed base_msg_en = respond_non_sql(non_sql_label, src_lang="en") final_msg = _tx_safe(base_msg_en, src_lang) # translate only once, safely logger.info("[chat] non_sql_label -> base_msg_en=%s", base_msg_en) if src_lang and src_lang != "en": logger.info("[chat] non_sql_label -> final_msg(translated)=%s", final_msg) # 3) Prepare minimal 'out' so the UI renders a friendly message table out = { "intent": ("neutral", non_sql_label), "sentiment": sentiment, "text": final_msg, # localized via _tx_safe "text_en": base_msg_en, "queries": [], # <-- nothing to render in grid "plan": {}, "secured_sql": None, "cards": [] } # 4) Log chat to DB so feedback works try: chat_id = insert_chat_history( user_id=auth["user_id"], role=auth["role"], customer_id=auth.get("customer_id"), user_input=q, # original text (could be Indic) llm_plan=out["plan"], secured_sql=[], # no SQL for routed replies sentiment=sentiment, intent=intent, llm_time=0.0, ) st.session_state.last_chat_id = chat_id except Exception as e: logger.warning("failed to log routed chat: %s", e) st.session_state.last_chat_id = None # 5) Render and stop before the SQL path runs st.session_state['last_chat_out'] = out st.session_state['last_query_text'] = q st.session_state.last_sentiment = sentiment st.session_state.last_intent = intent render_chat_output(out) render_feedback_controls(st.session_state.get("last_chat_id"), key_ns="render") # clear any previous SQL state that could leak into the UI st.session_state["last_sql"] = None st.session_state["last_result"] = None st.session_state["last_plan"] = None st.session_state["bypass_main_logic"] = True logger.info("[chat] Set bypass_main_logic to True") # Append to transcript and clear input on next run (non-SQL path) try: #_asst = (out.get('plan') or {}).get('explanation_display') or (out.get('plan') or {}).get('explanation') st.session_state.setdefault('transcript', []) st.session_state["transcript"].append({"user": q, "assistant": final_msg, "ts": time.time()}) #_append_transcript_once(q, out, explicit_text=_asst) except Exception: pass st.session_state['__clear_input__'] = True st.rerun() logger.info("[chat] non_sql_label -> Post render_feedback_controls") if is_forbidden_all_tables_post_tx(q_for_llm) and not bypass_main_logic: blocked_en = "Requests to list or dump data across all tables are restricted." # Optional: translate the message back for UI; if translation fails, we still show English try: blocked_msg = _tx_from_en(blocked_en, src_lang) if src_lang != "en" else blocked_en except Exception: blocked_msg = blocked_en st.warning(blocked_msg) blocked = True logger.info("[guard] blocked all-tables request (post-translation) en='%s'", _norm_guard_text(q_for_llm)) # If you log chat_history here, mark it blocked; then exit before planner: if not blocked and not bypass_main_logic: logger.info(">> in not blocked and not bypass_main_logic condition <<") with timed_step("Handle prompt", "meta"): out = handle_user_prompt(q_for_llm, auth["role"], auth.get("customer_id"), schema_registry, source_lang=src_lang) expl = out.get("plan", {}).get("explanation_display") if expl: logger.debug("[chat] explanation_display=%s", _sample(expl, 200)) with timed_step("Classify sentiment/intent", "meta"): sentiment, intent = classify_sentiment_and_intent(get_llm_client(LLM_CHOICE), q_for_llm) with timed_step("Insert chat_history", "meta"): chat_id = insert_chat_history( user_id=auth["user_id"], role=auth["role"], customer_id=auth.get("customer_id"), user_input=q, # store original (may be Indic) llm_plan=out["plan"], secured_sql=[c["secured"] for c in out["queries"] if c.get("secured")], sentiment=sentiment, intent=intent, llm_time=out["llm_time"] ) out["sentiment"] = sentiment out["intent"] = intent st.session_state.last_chat_out = out st.session_state.last_sentiment = sentiment st.session_state.last_intent = intent st.session_state.last_query_text = q st.session_state.last_chat_id = chat_id render_chat_output(out) render_feedback_controls(chat_id, key_ns="notblock") # Append to transcript and clear input on next run (SQL path) try: _asst = 'Pls find below the data you requested' st.session_state.setdefault('transcript', []) _append_transcript_once(q, out, explicit_text=_asst) except Exception: pass st.session_state['__clear_input__'] = True st.rerun() else: logger.info(">>in Else Loop<<") if st.session_state.get("last_chat_out") is None: st.info("Ask a question to see results.") # <-- no rendering here # Ensure transcript has latest turn appended (once per turn) _out = st.session_state.get('last_chat_out') _last_user = st.session_state.get('last_query_text') if _out is not None and _last_user: _t = st.session_state.setdefault('transcript', []) if (not _t) or (_t[-1].get('user') != _last_user): try: _asst = 'Pls find below the data you requested' _t.append({'user': _last_user, 'assistant': _asst, 'ts': time.time()}) except Exception: pass # Render conversation history (earlier only) when toggled if st.session_state.get('show_history') and st.session_state.get('transcript'): st.subheader('Conversation history (earlier)') _items = st.session_state['transcript'][:-1] if len(st.session_state['transcript']) > 1 else [] for _it in reversed(_items): if _it.get('user'): with st.chat_message('user'): st.write(_it.get('user','')) if _it.get('assistant'): with st.chat_message('assistant'): st.write(_it.get('assistant')) # ---- MIS ---- with tab_mis: st.header("📊 MIS Dashboard") timeframe = st.selectbox("Timeframe", ["Last 7 days", "Last 30 days", "Last 90 days", "All"]) days_map = {"Last 7 days": 7, "Last 30 days": 30, "Last 90 days": 90, "All": None} days = days_map[timeframe] if auth["role"] == "admin": df = load_chat_history(days=days) st.subheader("Overview") total = len(df) unique_users = df["user_id"].nunique() if not df.empty else 0 avg_llm = float(df["llm_response_time"].mean()) if ("llm_response_time" in df and not df.empty) else 0.0 execable = sum(bool(_json_list(x)) for x in (df["secured_sql"] if "secured_sql" in df else [])) repaired = sum(_auto_repaired(row) for _, row in df.iterrows()) if total else 0 k1, k2, k3, k4 = st.columns(4) with k1: _kpi_number("Total chats", total) with k2: _kpi_number("Unique users", unique_users) with k3: _kpi_number("Exec-able rate", f"{_safe_pct(execable, total):.1f}%", "Has secured SQL") with k4: _kpi_number("Auto-repair rate", f"{_safe_pct(repaired, total):.1f}%", "Validator changed SQL") st.caption(f"🤖 Avg LLM planning time: {avg_llm:.3f}s") st.markdown("### Intent & Sentiment") c1, c2 = st.columns(2) with c1: if "intent" in df and not df.empty: st.bar_chart(df["intent"].value_counts().head(10)) else: st.info("No intent data yet.") with c2: if "sentiment" in df and not df.empty: st.bar_chart(df["sentiment"].value_counts()) else: st.info("No sentiment data yet.") st.markdown("### Top Tables & Joins") table_counts = {} if not df.empty and "secured_sql" in df: for sqllist in df["secured_sql"]: for sql in _json_list(sqllist): for t in _extract_tables_from_sql(sql): table_counts[t] = table_counts.get(t, 0) + 1 if table_counts: top_tbl = pd.DataFrame(sorted(table_counts.items(), key=lambda x: x[1], reverse=True), columns=["table", "mentions"]).head(15) st.dataframe(top_tbl, use_container_width=True) else: st.info("No table usage yet.") st.markdown("### Activity Over Time") if not df.empty and "created_at" in df: tmp = df.copy() tmp["date"] = pd.to_datetime(tmp["created_at"], errors="coerce").dt.date tmp = tmp.dropna(subset=["date"]) if not tmp.empty: daily = tmp.groupby("date").size().reset_index(name="chats").set_index("date") if not daily.empty: st.line_chart(daily) else: st.info("No activity yet.") else: st.info("No activity yet.") else: st.info("No chat history yet.") st.markdown("### Recent Chats") show_cols = ["created_at", "email", "user_id", "role", "user_input", "intent", "sentiment", "llm_response_time", "feedback"] try: with get_conn() as conn: users_df = pd.read_sql_query("SELECT user_id, email FROM users", conn) df2 = df.merge(users_df, on="user_id", how="left") except Exception: df2 = df.copy() for c in show_cols: if c not in df2.columns: df2[c] = None st.dataframe(df2[show_cols].head(200), use_container_width=True) csv = df2.to_csv(index=False).encode("utf-8") st.download_button("⬇️ Export CSV", csv, file_name="chat_history_export.csv", mime="text/csv") else: df = load_chat_history(days=days, only_user_id=auth["user_id"]) st.subheader("Your Summary") total = len(df) stats = _feedback_stats_for_user(auth["user_id"], days) helpful = stats["helpful_count"] avg_llm = float(df["llm_response_time"].mean()) if ("llm_response_time" in df and not df.empty) else 0.0 k1, k2, k3 = st.columns(3) with k1: _kpi_number("Your chats", total) with k2: _kpi_number("👍 Helpful", helpful) with k3: _kpi_number("🤖 Avg plan time", f"{avg_llm:.3f}s") st.markdown("### Your Top Intents") if "intent" in df and not df.empty: vc = df["intent"].value_counts() if not vc.empty: st.bar_chart(vc.head(10)) else: st.info("No intent data yet.") else: st.info("No intent data yet.") st.markdown("### Recent (last 50)") show_cols = ["created_at", "user_input", "intent", "sentiment", "llm_response_time", "feedback"] for c in show_cols: if c not in df.columns: df[c] = None st.dataframe(df[show_cols].head(50), use_container_width=True) csv = df[show_cols].to_csv(index=False).encode("utf-8") st.download_button("⬇️ Download your history (CSV)", csv, file_name="my_chat_history.csv", mime="text/csv") # ---- Rate Earlier (user-only) ---- if auth["role"] != "admin": with tab_rate: st.header("⭐ Rate Earlier Conversations") # Flash any success/error message from the previous submit _flash = st.session_state.pop('rate_flash', None) if _flash: _kind, _msg = _flash try: (st.success if _kind=='success' else st.error)(_msg) st.toast(_msg) except Exception: pass fb_logger.info("[rate-tab] opened user_id=%s role=%s", auth["user_id"], auth["role"]) # 1) Load recent and keep only UNRATED (feedback is None/NaN) my_recent = get_recent_chats_for_user(auth["user_id"], limit=100) fb_logger.info("[rate-tab] fetched recent=%d", len(my_recent)) if "feedback" not in my_recent.columns: fb_logger.error("[rate-tab] feedback column missing in my_recent columns=%s", list(my_recent.columns)) st.error("Internal error: feedback column missing.") st.stop() unrated = my_recent[my_recent["feedback"].isna()].copy() fb_logger.info("[rate-tab] unrated=%d rated=%d", len(unrated), len(my_recent) - len(unrated)) if unrated.empty: st.success("No conversations pending feedback. 🙌") st.caption("Rated conversations are hidden from this list.") st.stop() unrated = unrated.reset_index(drop=True) def _label(row): text = (row["user_input"] or "").strip().replace("\n", " ") if len(text) > 70: text = text[:67] + "…" ts = row.get("created_at", "") fid = int(row["chat_id"]) return f"#{fid} • {ts} • {text}" options = list(unrated.index) labels = {i: _label(unrated.loc[i]) for i in options} idx = st.selectbox( "Pick a conversation to rate:", options=options, format_func=lambda i: labels[i], key="rate_prev_select", ) sel = unrated.loc[idx] chat_id_prev = int(sel["chat_id"]) st.caption(f"Selected chat_id: {chat_id_prev}") fb_logger.info("[rate-tab] selected chat_id=%s", chat_id_prev) with st.expander("Show original question"): st.write(sel["user_input"]) # ✅ Keep this INSIDE the tab block (indented) preview_clicked = st.button("🔎 Preview result (validate + LIMIT 10 + execute)", key=f"preview_{chat_id_prev}") if preview_clicked: fb_logger.info("[rate-tab] preview clicked chat_id=%s", chat_id_prev) try: fb_logger.info("[rate-tab] calling _json_list for chat_id=%s", chat_id_prev) sqls = _json_list(sel.get("secured_sql")) # Fallback to llm_plan if needed if not sqls: st.info("No SQL was recorded for this conversation.") else: if not SHOW_DETAILS: st.subheader("Preview result (executed)") else: st.subheader("SQL Preview (executed)") for i, raw_sql in enumerate(sqls[:2], start=1): if SHOW_DETAILS: st.markdown(f"**Query {i}**") try: fb_logger.debug("[rate-tab] validating q#%s", i) user_text_for_guard = (sel.get("user_input") or "").strip() validated = validate_sql( raw_sql, auth["role"], auth.get("customer_id"), schema_registry, user_text_for_guard, ) secured = _ensure_sql_string(validated) try: if not isinstance(validated, str): fb_logger.debug("[rate-tab] validate_sql returned %s; coerced to string len=%d", type(validated).__name__, len(secured)) except Exception: pass secured_limited = _apply_preview_limit(secured, limit=10) fb_logger.debug("[rate-tab] executing q#%s", i) df, rows, kind = run_sql_generic(secured_limited) if SHOW_DETAILS: st.code(secured_limited, language="sql") if kind == "select": try: import pandas as pd except Exception: pass df_to_show = df if 'pd' in globals() and isinstance(df, pd.DataFrame) else _as_dataframe_safely(df) st.dataframe(df_to_show, use_container_width=True) try: st.caption(f"Returned {len(df_to_show)} row(s).") except Exception: pass else: st.info(f"Non-SELECT statement; rows affected: {rows}") except SqlTimeoutError as te: fb_logger.warning("[rate-tab] timeout q#%s chat_id=%s err=%s", i, chat_id_prev, te) st.error(f"Timed out while executing Query {i}: {te}") except Exception as e: fb_logger.exception("[rate-tab] error executing q#%s chat_id=%s", i, chat_id_prev) st.error(f"Error while executing Query {i}.") st.exception(e) except SqlTimeoutError as te: fb_logger.warning("[rate-tab] preview timeout chat_id=%s err=%s", chat_id_prev, te) st.error(f"Timed out while preparing preview: {te}") except Exception as e: fb_logger.exception("[rate-tab] preview failed chat_id=%s", chat_id_prev) st.error("Preview failed due to an error.") st.exception(e) existing = get_chat_feedback(chat_id_prev) fb_logger.info("[rate-tab] existing feedback for chat_id=%s -> %s", chat_id_prev, existing) c1, c2 = st.columns(2) with c1: up = st.button("👍 Mark helpful", key=f"fb_prev_up_{chat_id_prev}", disabled=(existing is not None)) with c2: dn = st.button("👎 Mark not helpful", key=f"fb_prev_dn_{chat_id_prev}", disabled=(existing is not None)) if existing is not None: st.info("Feedback already recorded for this conversation." + (" (👍 Helpful)" if existing == 1 else " (👎 Not helpful)")) else: if up or dn: value = 1 if up else 0 fb_logger.info("[rate-tab] submit feedback chat_id=%s value=%s", chat_id_prev, value) ok, msg = update_chat_feedback_if_owner(chat_id_prev, auth["user_id"], value) fb_logger.info("[rate-tab] update_chat_feedback_if_owner -> ok=%s msg=%s", ok, msg) # Show message on next render and rerun to refresh lists st.session_state['rate_flash'] = ('success' if ok else 'error', msg) # Clear caches so the just-rated chat disappears immediately try: get_recent_chats_for_user.clear() fb_logger.debug("[rate-tab] cache cleared for get_recent_chats_for_user") except Exception: try: st.cache_data.clear() fb_logger.debug("[rate-tab] global cache cleared") except Exception: pass st.rerun() # ---- Admin ---- if auth["role"] == "admin": with tab_admin: st.header("Admin Utilities") tab_cu, tab_sc, tab_rd, tab_audit, tab_idx , tab_eda = st.tabs( ["👤 Create User", "🏅 Seller Scorecards", "Repopulate Tables from CSV", "🔐 Login Audit", "⚡ Create Recommended Indexes","📈 EDA"] ) # --- Create User --- with tab_cu: st.subheader("Create User") try: with sqlite3.connect(DB_PATH) as conn: custs = pd.read_sql_query( "SELECT customer_id FROM customers LIMIT 200", conn )["customer_id"].tolist() chosen = st.selectbox("Customer ID", options=custs) if custs else None email = st.text_input("Email", value="demo@example.com") pw = st.text_input("Password", type="password", value="demo123") role = st.selectbox("Role", ["user", "admin"], index=0) if st.button("➕ Create user"): if not email or not pw: st.error("Email and password are required.") elif role == "user" and not chosen: st.error("Select a valid customer_id for normal users.") else: create_user(email, pw, chosen if role == "user" else None, role) st.success(f"User created: {email} (role={role})") except Exception as e: st.error("Could not create user.") st.exception(e) with tab_sc: # app.py (Admin → Seller Scorecard tab) with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn: df = seller_scorecard_df( conn=conn, seller_id=st.text_input("Seller ID (optional)").strip() or None, date_from=st.date_input("From", value=None) and str(st.session_state["From"]), date_to=st.date_input("To", value=None) and str(st.session_state["To"]), delivered_only=st.toggle("Delivered only", value=True), limit=50 ) if df.empty: st.warning("No data for the chosen filters. Try widening the date range or remove the seller filter.") else: st.dataframe(df, use_container_width=True) with tab_rd: csv_map = get_table_csv_map() all_tables = sorted(csv_map.keys(), key=lambda t: LOAD_ORDER.index(t) if t in LOAD_ORDER else 999) st.info( "This will **DELETE** existing rows in the selected tables and reload data from the original CSVs. " "Foreign-key checks are temporarily disabled during the load, and data is loaded in a safe parent→child order." ) c1, c2 = st.columns([1, 3]) with c1: select_all = st.checkbox("Select all tables", value=False) with c2: default_sel = all_tables if select_all else [] selected = st.multiselect("Pick tables to repopulate", options=all_tables, default=default_sel) # Show current row counts for selected tables if selected: try: with sqlite3.connect(DB_PATH) as conn: before = get_row_counts(conn, selected) df_counts = pd.DataFrame( [{"table": t, "rows_before": before.get(t)} for t in selected] ) st.dataframe(df_counts, use_container_width=True, hide_index=True) except Exception as e: st.warning(f"Could not fetch current counts: {e}") # Confirmation gate st.markdown("**Type `RELOAD` to confirm:**") confirm = st.text_input("Confirmation", value="", placeholder="RELOAD") # Optional: show the CSV mapping table for transparency with st.expander("Show CSV file mapping"): st.table(pd.DataFrame([{"table": t, "csv": csv_map[t]} for t in all_tables])) # Run button run = st.button("🚨 Repopulate selected tables", type="primary", disabled=not selected) if run: if (confirm or "").strip().upper() != "RELOAD": st.error("Please type `RELOAD` to proceed.") else: st.info("Starting repopulation…") ok, msg, post = repopulate_tables_from_csv(selected, logger) if ok: st.success(msg) with sqlite3.connect(DB_PATH) as conn: after = post or get_row_counts(conn, selected) df_after = pd.DataFrame( [{"table": t, "rows_after": after.get(t)} for t in selected] ) st.dataframe(df_after, use_container_width=True, hide_index=True) else: st.error(msg) # --- Login Audit --- with tab_audit: st.subheader("Login Audit (last 500)") # Keep page state per filter set if "audit_page" not in st.session_state: st.session_state.audit_page = 1 if "audit_from" not in st.session_state: st.session_state.audit_from = ist_today() if "audit_to" not in st.session_state: st.session_state.audit_to = ist_today() c1, c2, c3 = st.columns([1,1,1], vertical_alignment="bottom") with c1: d_from = st.date_input("From date", value=st.session_state.audit_from, format="YYYY-MM-DD") with c2: d_to = st.date_input("To date", value=st.session_state.audit_to, format="YYYY-MM-DD") with c3: apply = st.button("Apply Filters", use_container_width=True) # Normalize & guard if d_to < d_from: d_from, d_to = d_to, d_from # swap silently # Reset pagination if filters changed or Apply clicked if apply or (d_from != st.session_state.audit_from) or (d_to != st.session_state.audit_to): st.session_state.audit_from = d_from st.session_state.audit_to = d_to st.session_state.audit_page = 1 # Enforce <= 5 days window delta_days = (st.session_state.audit_to - st.session_state.audit_from).days if delta_days > 5: st.error("Please select a range of **5 days or fewer**.") st.stop() # Pagination calc PAGE_SIZE = 10 page = st.session_state.audit_page offset = (page - 1) * PAGE_SIZE # Fetch counts & page total = count_login_audit_range(st.session_state.audit_from, st.session_state.audit_to) df_audit = get_login_audit_range(st.session_state.audit_from, st.session_state.audit_to, offset=offset, limit=PAGE_SIZE) # Header line left, right = st.columns([3,2]) with left: st.caption(f"Showing {min(total, offset+1)}–{min(total, offset+len(df_audit))} of {total} " f"for {st.session_state.audit_from} → {st.session_state.audit_to}") with right: # Pager controls total_pages = max(1, (total + PAGE_SIZE - 1) // PAGE_SIZE) prev_disabled = page <= 1 next_disabled = page >= total_pages b1, _, b2 = st.columns([1,3,1]) with b1: if st.button("← Prev", disabled=prev_disabled, key="audit_prev"): st.session_state.audit_page = max(1, page - 1) st.rerun() with b2: if st.button("Next →", disabled=next_disabled, key="audit_next"): st.session_state.audit_page = min(total_pages, page + 1) st.rerun() st.caption(f"Page {page} / {total_pages}") # Grid if df_audit.empty: st.info("No login events in the selected range.") else: # Optional: format columns for readability if "success" in df_audit.columns: df_audit["success"] = df_audit["success"].map({1: "✅ success", 0: "❌ failed"}).fillna(df_audit["success"]) st.dataframe(df_audit, use_container_width=True, hide_index=True) # --- Index creation --- with tab_idx: st.subheader("Create Recommended Indexes") st.write("These indexes speed up common Olist joins and scoping.") if st.button("⚡ Create / Ensure Indexes"): try: with sqlite3.connect(DB_PATH) as conn: stmts = ensure_performance_indexes(conn) st.success("Indexes ensured.") st.code("\n".join(stmts), language="sql") except Exception as e: st.error("Index creation failed.") st.exception(e) # --- Admin > EDA ------------------------------------------------------------- with tab_eda: # --- EDA Mode --- eda_mode = st.radio("EDA Mode", ["Summary", "Interactive"], index=0, horizontal=True, key="eda_mode_toggle") if eda_mode == "Interactive": eda.render_eda() st.stop() else: eda.render_summary() with tab_rag: st.header("RAG Publisher") try: reg = schema_registry except Exception as e: logger.exception("Failed load schema registry : %s", e) reg = load_schema_registry(os.path.join(DATA_DIR, SCHEMA_FILE)) render_rag_publisher()