botnew / app.py
SuhasGholkar's picture
Create app.py
8c30a4e verified
Raw History Blame Contribute Delete
312 kB
from __future__ import annotations import logging import re as _re # ---- BEGIN RAG Publisher SQL logging (idempotent, single-pre/post; chat_history only) ---- # --- RAG Publisher SQL logging (safe wrapper; no C-type monkey patching) --- # === Prompt & SQL Loggers === # === Redaction & Logging Helpers === # === Prompt & SQL Loggers === prompt_logger = logging.getLogger("prompt") sql_logger = logging.getLogger("sql") for _lg in (prompt_logger, sql_logger): if not _lg.handlers: _h = logging.StreamHandler() _h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")) _lg.addHandler(_h) _lg.setLevel(logging.INFO) # === End Prompt & SQL Loggers === # === Redaction & Logging Helpers === import re as _re from typing import Any as _Any _REDACTIONS = [ (_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"), (_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"), (_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"), ] def _redact(text: str) -> str: if not text: return text out = text for _rx, _repl in _REDACTIONS: out = _rx.sub(_repl, out) return out def _truncate(text: str, max_chars: int = 4000) -> str: if not text: return text return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>" def _j(_obj: _Any) -> str: try: import json as _json return _json.dumps(_obj, ensure_ascii=False, default=str) except Exception: return str(_obj) def log_llm_inputs(event: str, user_id: str | None, chat_id: str | None, model: str, messages: list[dict], context: str | None = None, exemplars: list[dict] | None = None, plan: dict | None = None, extra: dict | None = None): payload = { "event": event, "user_id": user_id, "chat_id": chat_id, "model": model, "context": _truncate(_redact(context or ""), 3000), "exemplars": exemplars or [], "plan": plan or {}, "messages": [], "extra": extra or {} } for m in messages or []: payload["messages"].append({ "role": m.get("role"), "name": m.get("name"), "content": _truncate(_redact(str(m.get("content", ""))), 3000) }) try: prompt_logger.info(_j(payload)) except Exception as _e: try: prompt_logger.warning("log_llm_inputs failed: %s", _e) except Exception: pass def log_secured_sql(chat_id: str | None, user_id: str | None, secured_sql: str | None, sql_params: dict | list | None = None, table_guard: dict | None = None): if not secured_sql: return payload = { "event": "secured_sql_ready", "chat_id": chat_id, "user_id": user_id, "sql": _truncate(_redact(secured_sql), 4000), "params": sql_params or {}, "guards": table_guard or {}, } try: sql_logger.info(_j(payload)) except Exception as _e: try: sql_logger.warning("log_secured_sql failed: %s", _e) except Exception: pass def log_router_decision(chat_id: str | None, user_id: str | None, text: str, label: str | None, lang: str | None, route: str): try: prompt_logger.info(_j({ "event": "router_decision", "chat_id": chat_id, "user_id": user_id, "lang": lang, "route": route, "label": label, "text": _truncate(_redact(text), 2000), })) except Exception as _e: try: prompt_logger.warning("log_router_decision failed: %s", _e) except Exception: pass # === End Helpers === import re as _re from typing import Any as _Any _REDACTIONS = [ (_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"), (_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"), (_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"), ] def _redact(text: str) -> str: if not text: return text out = text for _rx, _repl in _REDACTIONS: out = _rx.sub(_repl, out) return out def _truncate(text: str, max_chars: int = 4000) -> str: if not text: return text return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>" def _j(_obj: _Any) -> str: try: import json as _json return _json.dumps(_obj, ensure_ascii=False, default=str) except Exception: return str(_obj) def log_llm_inputs(event: str, user_id: str | None, chat_id: str | None, model: str, messages: list[dict], context: str | None = None, exemplars: list[dict] | None = None, plan: dict | None = None, extra: dict | None = None): payload = { "event": event, "user_id": user_id, "chat_id": chat_id, "model": model, "context": _truncate(_redact(context or ""), 3000), "exemplars": exemplars or [], "plan": plan or {}, "messages": [], "extra": extra or {} } for m in messages or []: payload["messages"].append({ "role": m.get("role"), "name": m.get("name"), "content": _truncate(_redact(str(m.get("content", ""))), 3000) }) try: prompt_logger.info(_j(payload)) except Exception as _e: try: prompt_logger.warning("log_llm_inputs failed: %s", _e) except Exception: pass def log_secured_sql(chat_id: str | None, user_id: str | None, secured_sql: str | None, sql_params: dict | list | None = None, table_guard: dict | None = None): if not secured_sql: return payload = { "event": "secured_sql_ready", "chat_id": chat_id, "user_id": user_id, "sql": _truncate(_redact(secured_sql), 4000), "params": sql_params or {}, "guards": table_guard or {}, } try: sql_logger.info(_j(payload)) except Exception as _e: try: sql_logger.warning("log_secured_sql failed: %s", _e) except Exception: pass def log_router_decision(chat_id: str | None, user_id: str | None, text: str, label: str | None, lang: str | None, route: str): try: prompt_logger.info(_j({ "event": "router_decision", "chat_id": chat_id, "user_id": user_id, "lang": lang, "route": route, "label": label, "text": _truncate(_redact(text), 2000), })) except Exception as _e: try: prompt_logger.warning("log_router_decision failed: %s", _e) except Exception: pass # === End Helpers === prompt_logger = logging.getLogger("prompt") sql_logger = logging.getLogger("sql") for _lg in (prompt_logger, sql_logger): if not _lg.handlers: _h = logging.StreamHandler() _h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")) _lg.addHandler(_h) _lg.setLevel(logging.INFO) # === End Prompt & SQL Loggers === # === Redaction & Logging Helpers === import re as _re from typing import Any as _Any _REDACTIONS = [ (_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"), (_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"), (_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"), ] def _redact(text: str) -> str: if not text: return text out = text for _rx, _repl in _REDACTIONS: out = _rx.sub(_repl, out) return out def _truncate(text: str, max_chars: int = 4000) -> str: if not text: return text return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>" def _j(_obj: _Any) -> str: try: import json as _json return _json.dumps(_obj, ensure_ascii=False, default=str) except Exception: return str(_obj) def log_llm_inputs(event: str, user_id: str | None, chat_id: str | None, model: str, messages: list[dict], context: str | None = None, exemplars: list[dict] | None = None, plan: dict | None = None, extra: dict | None = None): payload = { "event": event, "user_id": user_id, "chat_id": chat_id, "model": model, "context": _truncate(_redact(context or ""), 3000), "exemplars": exemplars or [], "plan": plan or {}, "messages": [], "extra": extra or {} } for m in messages or []: payload["messages"].append({ "role": m.get("role"), "name": m.get("name"), "content": _truncate(_redact(str(m.get("content", ""))), 3000) }) try: prompt_logger.info(_j(payload)) except Exception as _e: try: prompt_logger.warning("log_llm_inputs failed: %s", _e) except Exception: pass def log_secured_sql(chat_id: str | None, user_id: str | None, secured_sql: str | None, sql_params: dict | list | None = None, table_guard: dict | None = None): if not secured_sql: return payload = { "event": "secured_sql_ready", "chat_id": chat_id, "user_id": user_id, "sql": _truncate(_redact(secured_sql), 4000), "params": sql_params or {}, "guards": table_guard or {}, } try: sql_logger.info(_j(payload)) except Exception as _e: try: sql_logger.warning("log_secured_sql failed: %s", _e) except Exception: pass def log_router_decision(chat_id: str | None, user_id: str | None, text: str, label: str | None, lang: str | None, route: str): try: prompt_logger.info(_j({ "event": "router_decision", "chat_id": chat_id, "user_id": user_id, "lang": lang, "route": route, "label": label, "text": _truncate(_redact(text), 2000), })) except Exception as _e: try: prompt_logger.warning("log_router_decision failed: %s", _e) except Exception: pass # === End Helpers === def _install_ragpub_sql_logging() -> None: """ Wrap sqlite3.connect to return logging proxies that: - Only log SELECTs that touch the chat_history table - Log the *final fetched row count* (when fetchone/fetchmany/fetchall is called) - Never alter underlying behavior or consume results early This avoids trying to set attributes on immutable C types. """ import sqlite3 as _sqlite3 import re as _re import functools as norm log = logging.getLogger("olist") # Already installed? if getattr(_install_ragpub_sql_logging, "_installed", False): return # Save the original connect so we can delegate _orig_connect = _sqlite3.connect # Detect whether a SQL statement reads from chat_history _chat_pat = _re.compile(r"\b(from|join)\s+chat_history\b", _re.IGNORECASE) def _touches_chat_history(sql: str) -> bool: try: if not sql: return False # Only care about SELECT-like queries s = sql.lstrip().lower() if not (s.startswith("select") or s.startswith("with")): return False return _chat_pat.search(s) is not None except Exception: return False class _LoggedCursor: """ Thin cursor proxy that logs row counts for SELECTs on chat_history. We only log *after* the caller fetches so we don't consume results. """ def __init__(self, inner, logger): self._c = inner self._logger = logger self._last_sql = None self._touches = False # Execute variants def execute(self, sql, params=()): self._last_sql = sql self._touches = _touches_chat_history(sql) return self.__class__(self._c.execute(sql, params), self._logger) def executemany(self, sql, seq_of_params): self._last_sql = sql self._touches = _touches_chat_history(sql) return self.__class__(self._c.executemany(sql, seq_of_params), self._logger) def executescript(self, sql_script): # Treat script as non-loggable; scripts are unusual for our paths self._last_sql = sql_script self._touches = False return self._c.executescript(sql_script) # Fetch methods (where we can safely know row counts) def fetchall(self): rows = self._c.fetchall() if self._touches: try: self._logger.info("[ragpub] rows: %d", len(rows)) except Exception: pass return rows def fetchone(self): row = self._c.fetchone() if self._touches: try: self._logger.info("[ragpub] rows: %d", 1 if row is not None else 0) except Exception: pass return row def fetchmany(self, size=None): rows = self._c.fetchmany(size if size is not None else 0) if self._touches: try: self._logger.info("[ragpub] rows: %d", len(rows)) except Exception: pass return rows # Delegate any other attribute/method transparently def __getattr__(self, name): return getattr(self._c, name) # Context manager passthrough def __enter__(self): self._c.__enter__() return self def __exit__(self, exc_type, exc, tb): return self._c.__exit__(exc_type, exc, tb) class _LoggedConnection: """ Connection proxy that wraps .execute()/.cursor() to attach logging cursors. """ def __init__(self, inner, logger): self._conn = inner self._logger = logger # Connection-level convenience execute returns a cursor — wrap it. def execute(self, sql, params=()): if _touches_chat_history(sql): try: self._logger.info("[ragpub] read_sql: %s", sql) except Exception: pass cur = self._conn.execute(sql, params) return _LoggedCursor(cur, self._logger) def executemany(self, sql, seq_of_params): if _touches_chat_history(sql): try: self._logger.info("[ragpub] read_sql: %s", sql) except Exception: pass cur = self._conn.executemany(sql, seq_of_params) return _LoggedCursor(cur, self._logger) def executescript(self, sql_script): # Scripts: no special logging here return self._conn.executescript(sql_script) def cursor(self): # Every regular cursor becomes a logging cursor cur = self._conn.cursor() return _LoggedCursor(cur, self._logger) # Delegate everything else (commit, close, etc.) def __getattr__(self, name): return getattr(self._conn, name) # Context manager passthrough def __enter__(self): self._conn.__enter__() return self def __exit__(self, exc_type, exc, tb): return self._conn.__exit__(exc_type, exc, tb) @_functools.wraps(_sqlite3.connect) def _logged_connect(*args, **kwargs): # Keep caller’s factory if they intentionally set one if "factory" in kwargs and kwargs["factory"] is not None: return _orig_connect(*args, **kwargs) conn = _orig_connect(*args, **kwargs) return _LoggedConnection(conn, log) # Install wrapper once _sqlite3.connect = _logged_connect _install_ragpub_sql_logging._installed = True def _rag_fetch_exemplars_for_query( query_text: str, k: int = 4, min_relevance: float = 0.0, ) -> list[dict]: """ Retrieve top-k exemplar items (docs + metadata) for the given query from Chroma. Returns a list of dicts: {"user_input","llm_plan","secured_sql","distance","doc","id"}. """ try: import os as _os import chromadb from chromadb.utils import embedding_functions # Embedder must match publisher (OpenAI or ONNX) openai_key = _os.environ.get("OPENAI_API_KEY") or _os.environ.get("OPENAI_APIKEY") ef = None ef_dim = None if openai_key: embed_model = _os.environ.get("OPENAI_EMBED_MODEL", "text-embedding-3-small") ef = embedding_functions.OpenAIEmbeddingFunction(api_key=openai_key, model_name=embed_model) ef_dim = OPENAI_EMBED_DIM else: try: from chromadb.utils.embedding_functions.onnx_mini_lm_l6_v2 import ONNXMiniLM_L6_V2 ef = ONNXMiniLM_L6_V2() ef_dim = ONNX_EMBED_DIM except Exception: ef = None ef_dim = None chroma_path = _os.environ.get("CHROMA_PATH", _os.environ.get("RAG_DIR", "/data/rag_store")) coll_name = _os.environ.get("RAG_COLLECTION", "olist_sql_exemplars") lg = logging.getLogger("olist") lg.info("[rag-read] chroma client init | path=%s | collection=%s", chroma_path, coll_name) client = chromadb.PersistentClient(path=chroma_path) try: coll = _get_or_migrate_chroma_collection(client, coll_name, ef, (ef_dim or OPENAI_EMBED_DIM), lg) except Exception: coll = client.get_or_create_collection(coll_name, embedding_function=ef) if not (query_text or "").strip(): return [] res = coll.query(query_texts=[query_text], n_results=max(1, int(k))) # Shape depends on Chroma version; handle defensively. out = [] docs = (res.get("documents") or [[]])[0] metas = (res.get("metadatas") or [[]])[0] ids = (res.get("ids") or [[]])[0] dists = (res.get("distances") or [[]])[0] or (res.get("embeddings") and [None] * len(docs)) or [None] * len(docs) for i, doc in enumerate(docs): md = metas[i] if i < len(metas) else {} row = { "id": ids[i] if i < len(ids) else None, "doc": doc, "distance": dists[i] if i < len(dists) else None, "user_input": (md or {}).get("user_input"), "llm_plan": (md or {}).get("llm_plan"), "secured_sql": (md or {}).get("secured_sql"), "tag": (md or {}).get("tag"), "source": (md or {}).get("source"), "chat_id": (md or {}).get("chat_id"), "user_id": (md or {}).get("user_id"), } out.append(row) # Optional relevance filter if min_relevance is not None and out: def _ok(x): d = x.get("distance") return True if d is None else (d <= (1.0 - min_relevance)) # adjust to your distance/score semantics out = [x for x in out if _ok(x)] # Log a compact sample try: samp = out[0] if out else None logging.getLogger("olist").info("[rag-read] hits=%s | sample=%s", len(out), { "id": samp and samp.get("id"), "tag": samp and samp.get("tag"), "distance": samp and samp.get("distance"), "has_sql": bool(samp and (samp.get("secured_sql") or "").strip()), }) except Exception: pass return out except Exception as e: logging.getLogger("olist").warning("[rag-read] failed: %s", e) return [] def _rewrite_limit_and(sql: str) -> str: s = (sql or "").strip() # Fix pattern like: ... WHERE ... LIMIT 1 /*AND-misplaced*/ AND <cond> m = _re.search(r"(?is)\bLIMIT\s+\d+\s+AND\s+(.+)$", s) if m: # Split into 'before limit' and trailing cond parts = _re.split(r"(?is)\bLIMIT\s+\d+\s+AND\s+", s, maxsplit=1) if len(parts) == 2: before_limit = parts[0] after_cond = parts[1] # Extract the LIMIT value from original mlim = _re.search(r"(?is)(.*)\bLIMIT\s+(\d+)", before_limit) if mlim: head = mlim.group(1) limit_val = mlim.group(2) fixed_head = _inject_where(head, after_cond) return _cleanup_where_and(f"{fixed_head} LIMIT {limit_val}") return sql import pandas as pd # noqa: F401 import os # --- begin: quiet Torch/Transformers imports --- def _silence_torch_noise(): """ Prepare a reusable stderr redirection context that doesn't close the buffer. - Do NOT touch TORCH_LOGS (new PyTorch rejects values like 'off') - Keep C++ logs quiet and avoid GPU probing - Provide _TorchNoiseStderrShield to mute stderr during heavy imports """ try: import os, io, contextlib # Leave TORCH_LOGS unset; if present and invalid, remove it if os.environ.get("TORCH_LOGS") == "off": try: del os.environ["TORCH_LOGS"] except Exception: pass os.environ.setdefault("TORCH_CPP_LOG_LEVEL", "ERROR") os.environ.setdefault("CUDA_VISIBLE_DEVICES", "") class _StderrShield: """Redirects stderr to an in-memory buffer WITHOUT closing it on exit.""" def __init__(self): import io, contextlib self._buf = io.StringIO() self._cm = contextlib.redirect_stderr(self._buf) def __enter__(self): return self._cm.__enter__() def __exit__(self, exc_type, exc, tb): # Restore original stderr but DO NOT close the buffer. # Keeping it alive avoids 'I/O operation on closed file' self._cm.__exit__(exc_type, exc, tb) # Export the shield for use in other code paths globals()["_TorchNoiseStderrShield"] = _StderrShield except Exception: pass _silence_torch_noise() # --- end: quiet Torch/Transformers imports --- # Hard-coded collection for RAG Publisher (not shown to end users) RAG_COLLECTION = os.getenv("RAG_COLLECTION", "olist_sql_exemplars") # keep env override if you want RAG_PUBLISH_COLLECTION = RAG_COLLECTION # single source of truth for publisher OPENAI_EMBED_MODEL = os.getenv("OPENAI_EMBED_MODEL", "text-embedding-3-small") OPENAI_EMBED_DIM = 1536 # text-embedding-3-small & large are 1536 ONNX_EMBED_DIM = 384 # MiniLM-L6-v2 def _get_or_migrate_chroma_collection(client, base_name: str, ef, ef_dim: int, logger): """ Get a Chroma collection bound to the given embedding function. If an existing collection on disk has a different embedding dimension, transparently fall back to a dimension-suffixed collection name (e.g., '<base>_d1536') to avoid InvalidArgumentError. """ name_try = base_name try: return client.get_or_create_collection(name_try, embedding_function=ef) except Exception as e: msg = str(e) # If mismatch, Chroma typically throws InvalidArgumentError about dimensions. if "dimension" in msg.lower() or "expecting embedding with dimension" in msg: name_try = f"{base_name}_d{ef_dim}" try: lg = logger or logging.getLogger("olist") lg.warning("Chroma dimension mismatch on '%s'. Using '%s' instead.", base_name, name_try) except Exception: pass return client.get_or_create_collection(name_try, embedding_function=ef) raise # --- RAG Publisher SQL logging shim (back-compat, chat_history-only) --- def _install_rag_sql_logging(): """ Legacy shim that used to log all pd.read_sql* calls. Now: - If the new chat_history-only shim already wrapped pandas, this is a no-op. - Otherwise, we wrap read_sql/read_sql_query but ONLY log when the SQL actually touches the chat_history table. """ try: import pandas as _pd import logging as _lg import time as _time import re as _re except Exception: return # If the new shim already wrapped pandas, bail out (avoid double wrapping). if getattr(_pd, "_RAGPUB_SQL_WRAPPED_V3", False): return # If this legacy shim already wrapped once, don't wrap again. if getattr(_pd, "_RAGPUB_SQL_WRAPPED_V2", False): return _olist_logger = _lg.getLogger("olist") def _norm_sql(sql): try: s = str(sql) except Exception: return None s = s.replace("\n", " ").replace("\r", " ") s = _re.sub(r"\s+", " ", s).strip() return s def _touches_chat_history(sql_text: str) -> bool: try: return bool(sql_text) and ("chat_history" in sql_text.lower()) except Exception: return False def _wrap(fn_name: str): real = getattr(_pd, fn_name, None) if real is None or getattr(real, "_olist_rag_wrap", False): return def _wrapped(*args, **kwargs): # Figure out the SQL string from args/kwargs sql = kwargs.get("sql", args[0] if args else None) sql_str = _norm_sql(sql) do_log = _touches_chat_history(sql_str) t0 = _time.perf_counter() if do_log: _olist_logger.info("[ragpub] read_sql: %s", sql_str) df = real(*args, **kwargs) if do_log: try: n = getattr(df, "shape", (None, None))[0] except Exception: n = None _olist_logger.info("[ragpub] rows: %s", n) return df _wrapped._olist_rag_wrap = True _wrapped.__wrapped__ = real setattr(_pd, fn_name, _wrapped) for _name in ("read_sql", "read_sql_query"): _wrap(_name) # mark legacy wrapper installed so we don't wrap again _pd._RAGPUB_SQL_WRAPPED_V2 = True # --- END legacy shim --- # Install the shim as soon as pandas is available try: import pandas as _pd_check # noqa if _pd_check is not None: _install_rag_sql_logging() except Exception: pass 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: """ Ensure a logger exists with one console handler and a unique marker so we don't add handlers again on Streamlit reruns. If `name` is None, returns the root logger. """ lg = logging.getLogger(name) if name else logging.getLogger() # Avoid duplicate handlers across reruns by looking for our marker have_marker = any(getattr(h, "_olist_marker", False) for h in lg.handlers) if not have_marker: fmt = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s") ch = logging.StreamHandler() ch.setLevel(level) ch.setFormatter(fmt) # mark this handler so we don't add another one on rerun setattr(ch, "_olist_marker", True) lg.addHandler(ch) # If you also want a file handler in /data, uncomment: # try: # import os # os.makedirs("/data", exist_ok=True) # fh = logging.FileHandler("/data/app.log", encoding="utf-8") # fh.setLevel(level) # fh.setFormatter(fmt) # setattr(fh, "_olist_marker", True) # lg.addHandler(fh) # except Exception as e: # # fall back silently if not writable # pass # Don’t propagate to parents to avoid duplicate prints lg.propagate = False # Always enforce desired level (safe if already set) lg.setLevel(level) 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 --- # Unify DB logging: both names point to the same "olist" logger _olist_logger = logging.getLogger("olist") # Ensure both symbols exist so older/newer code paths work try: db_logger except NameError: db_logger = _olist_logger try: _db_logger except NameError: _db_logger = _olist_logger # 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 # --- 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 logging.info("[chroma] dir=%s exists=%s ls=%s", RAG_DIR, os.path.exists(RAG_DIR), os.listdir(RAG_DIR) if os.path.exists(RAG_DIR) else None) # --- 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: _shield = globals().get("_TorchNoiseStderrShield") if _shield: with _shield(): import chromadb # may transitively import onnx/torch pieces from chromadb.utils import embedding_functions as cef else: import chromadb from chromadb.utils import embedding_functions as cef except Exception: chromadb = None cef = 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 (SHOW_EXPLAIN_PLAN and plan.get("explanation_display")): return str((SHOW_EXPLAIN_PLAN and plan.get("explanation_display"))) if (SHOW_EXPLAIN_PLAN and plan.get("explanation")): return str((SHOW_EXPLAIN_PLAN and 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") 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 logging.info("[ex] count=%s", col.count()) res = col.get( where={"tag": "exemplar"}, # or: where={"tags": {"$contains": "exemplar"}} include=["metadatas", "documents", "ids"], ) logging.info("[ex] tagged_exemplars ids=%s", res.get("ids", [])[:5]) 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)) # --- feedback instrumentation helper (added) --- try: _FB_DIAG_ENABLED # type: ignore except NameError: _FB_DIAG_ENABLED = True def _log_feedback_event(phase: str, **fields): try: import json, logging lg = globals().get("fb_logger") or logging.getLogger("feedback") lg.info("[fb-diag] %s %s", phase, json.dumps(fields, ensure_ascii=False)) except Exception: # never crash the app due to logging pass # --- end helper --- 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"<style>{css_path.read_text(encoding='utf-8')}</style>", unsafe_allow_html=True) else: st.markdown(""" <style> .oa-banner{ border-radius:14px; padding:14px 16px; color:#fff; background:linear-gradient(90deg,#2563eb,#4f8df6); display:flex; align-items:center; gap:12px; box-shadow:0 8px 24px rgba(37,99,235,.18); margin-bottom:12px; } .oa-logo{ width:36px;height:36px;border-radius:10px; background:rgba(255,255,255,.16); display:grid; place-items:center; font-weight:800; } .oa-title{ font-weight:700; font-size:18px; line-height:1.1; margin:0; } .oa-sub{ opacity:.95; font-size:13px; margin-top:2px; } .oa-chips{ margin-left:auto; display:flex; gap:8px; align-items:center; flex-wrap:wrap; } .oa-chip{ background:rgba(255,255,255,.16); padding:6px 10px; border-radius:999px; font-size:12px; backdrop-filter: blur(4px); } .oa-user{ background:#fff; color:#0f172a; } .oa-card{ background:#fff; border:1px solid #e7e9ee; border-radius:12px; padding:12px; box-shadow:0 6px 18px rgba(2,6,23,.05); } .oa-card + .oa-card{ margin-top:12px; } .oa-cardhead{ display:flex; align-items:center; gap:10px; margin-bottom:8px; } .oa-ttl{ font-weight:700; font-size:15px; } .oa-pill{ font-size:12px; border:1px solid #e7e9ee; background:#f3f6fb; border-radius:999px; padding:3px 8px; color:#334155; margin-left:auto; } .oa-sql pre{ font-size:12.5px !important; border-radius:10px !important; } .oa-note{ background:#eef5ff; border:1px solid #dbe8ff; color:#1e3a8a; border-radius:10px; padding:10px 12px; font-size:13px; } </style> """, 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'<span class="oa-chip oa-user">{who} • {role}</span>' st.markdown(f""" <div class="oa-banner"> <div class="oa-logo">SA</div> <div> <div class="oa-title">SmartAssist</div> <div class="oa-sub">AI powered BOT</div> </div> <div class="oa-chips"> <span class="oa-chip">Enterprise</span> <span class="oa-chip">Secure</span> {user_chip} </div> </div> """, unsafe_allow_html=True) def card_open(title=None, right=None): st.markdown('<div class="oa-card">', unsafe_allow_html=True) if title or right: st.markdown('<div class="oa-cardhead">', unsafe_allow_html=True) if title: st.markdown(f'<div class="oa-ttl">{title}</div>', unsafe_allow_html=True) if right: st.markdown(f'<div class="oa-pill">{right}</div>', unsafe_allow_html=True) st.markdown('</div>', unsafe_allow_html=True) def card_close(): st.markdown('</div>', 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+", "<num>", 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 <context>: %s", e) if not lst: return with st.container(): st.markdown( """ <style> .top-enq-title{font-size:1.15rem;font-weight:700;margin:0.25rem 0 0.5rem 0;} </style> """, unsafe_allow_html=True ) st.markdown('<div class="top-enq-title">Top Enquiries this week</div>', 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_password_column(conn: sqlite3.Connection): # add password_hash if missing cols = {r[1] for r in conn.execute("PRAGMA table_info(users)").fetchall()} if "password_hash" not in cols: db_logger.info("[init] adding column users.password_hash") conn.execute("ALTER TABLE USERS ADD COLUMN password_hash TEXT") conn.commit() def _create_user_if_absent( conn: sqlite3.Connection, *, user_id: str, email: str, role: str, password_plain: str, customer_id: str ): # does the user already exist? row = conn.execute("SELECT user_id FROM users WHERE user_id = ? OR email = ? LIMIT 1", (user_id, email)).fetchone() if row: db_logger.info("[init] user exists: %s (%s) – not modifying", user_id, email) return False # hash the password; bcrypt is salted so the hash will differ per run, but check will pass pw_hash = bcrypt.hashpw(password_plain.encode("utf-8"), bcrypt.gensalt(rounds=12)).decode("utf-8") conn.execute( """ INSERT INTO users (user_id, email, role, customer_id, password_hash) VALUES (?, ?, ?, ?, ?) """, (user_id, email, role, customer_id, pw_hash), ) db_logger.info("[init] created %s user_id=%s email=%s", role, user_id, email) return True def seed_default_users(conn: sqlite3.Connection): # Allow overrides via env (so you don't hardcode creds in repo) admin_email = os.getenv("ADMIN_EMAIL", "admin@example.com") admin_pass = os.getenv("ADMIN_PASSWORD", "admin123") user_email = os.getenv("USER_EMAIL", "test@test.com") user_pass = os.getenv("USER_PASSWORD", "Strongpass#123") _create_user_if_absent( conn, user_id="admin1", email=admin_email, role="admin", password_plain=admin_pass, customer_id= '' ) _create_user_if_absent( conn, user_id="U_user01", email=user_email, role="user", password_plain=user_pass, customer_id='06b8999e2fba1a1fbc88172c00ba8bc7' ) conn.commit() 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): stmts = [ "CREATE INDEX IF NOT EXISTS idx_orders_customer_time ON orders(customer_id, order_purchase_timestamp)", "CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(order_status)", "CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id)", "CREATE INDEX IF NOT EXISTS idx_order_items_seller ON order_items(seller_id)", "CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id)", "CREATE INDEX IF NOT EXISTS idx_payments_order ON order_payments(order_id)", "CREATE INDEX IF NOT EXISTS idx_payments_type ON order_payments(payment_type)", "CREATE INDEX IF NOT EXISTS idx_reviews_order ON order_reviews(order_id)", "CREATE INDEX IF NOT EXISTS idx_reviews_score ON order_reviews(review_score)", "CREATE INDEX IF NOT EXISTS idx_products_category ON products(product_category_name)", "CREATE INDEX IF NOT EXISTS idx_sellers_city ON sellers(seller_city)", "CREATE INDEX IF NOT EXISTS idx_sellers_state ON sellers(seller_state)", "CREATE INDEX IF NOT EXISTS idx_geo_zip ON geolocation(geolocation_zip_code_prefix)", "CREATE INDEX IF NOT EXISTS idx_sessions_user ON user_sessions(user_id)", "CREATE INDEX IF NOT EXISTS idx_chat_user_time ON chat_history(user_id, created_at)", "CREATE INDEX IF NOT EXISTS idx_chat_feedback ON chat_history(feedback)", "CREATE INDEX IF NOT EXISTS idx_rag_docs_coll ON rag_docs(collection)", "CREATE INDEX IF NOT EXISTS idx_rag_chunks_doc ON rag_chunks(doc_id)", "CREATE INDEX IF NOT EXISTS idx_rag_chunks_coll ON rag_chunks(collection)", "CREATE INDEX IF NOT EXISTS idx_rag_log_coll_time ON rag_publish_log(collection, created_at)" ] logger = globals().get("db_logger") or globals().get("_db_logger") executed = 0 for s in stmts: ok, note = _safe_exec(conn, s) if not ok and logger: logger.warning("[init] index failed sql=%s err=%s", s, note) elif ok: executed += 1 if executed: conn.commit() try: conn.execute("CREATE INDEX IF NOT EXISTS idx_prod_cat_trans_name ON product_category_name_translation(product_category_name)") conn.execute("CREATE INDEX IF NOT EXISTS idx_prod_cat_trans_en ON product_category_name_translation(product_category_name_english)") except Exception: pass def ensure_performance_indexes_old (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 _db_logger = logging.getLogger("olist") def _table_exists(conn: sqlite3.Connection, name: str) -> bool: """Return True if a table exists in this SQLite connection.""" try: cur = conn.execute( "SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1", (name,), ) return cur.fetchone() is not None except Exception as e: try: _db_logger.warning("[init] _table_exists(%s) failed: %s", name, e) except Exception: pass return False def _safe_exec(conn: sqlite3.Connection, sql: str) -> tuple[bool, str | None]: """ Execute a single SQL statement safely. Returns (ok, note). Treats 'already exists' as ok (idempotent). """ try: conn.execute(sql) return True, None except sqlite3.OperationalError as e: msg = str(e) if "already exists" in msg.lower(): return True, "exists" return False, msg except Exception as e: return False, str(e) # --- end helper block --- def _ensure_translation_table(conn: sqlite3.Connection) -> None: """Create the optional Olist translation table if it doesn't exist.""" try: conn.execute( "CREATE TABLE IF NOT EXISTS product_category_name_translation (" "product_category_name TEXT, " "product_category_name_english TEXT)" ) conn.commit() except Exception: # best-effort; never block startup on this pass 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 create_base_tables_if_missing(conn: sqlite3.Connection): """ Ensure ALL required base tables exist. This version does not return early; it creates each table IF NOT EXISTS. """ db_logger = globals().get("db_logger") or logging.getLogger("olist") if db_logger: db_logger.info("[init] ensuring base tables exist") conn.executescript(""" PRAGMA foreign_keys = ON; CREATE TABLE IF NOT EXISTS customers ( customer_id TEXT PRIMARY KEY, customer_unique_id TEXT, customer_zip_code_prefix INTEGER, customer_city TEXT, customer_state TEXT ); CREATE TABLE IF NOT EXISTS orders ( order_id TEXT PRIMARY KEY, customer_id TEXT NOT NULL, order_status TEXT, order_purchase_timestamp TEXT, order_approved_at TEXT, order_delivered_carrier_date TEXT, order_delivered_customer_date TEXT, order_estimated_delivery_date TEXT ); CREATE TABLE IF NOT EXISTS order_items ( order_id TEXT NOT NULL, order_item_id INTEGER NOT NULL, product_id TEXT, seller_id TEXT, shipping_limit_date TEXT, price REAL, freight_value REAL, PRIMARY KEY(order_id, order_item_id) ); CREATE TABLE IF NOT EXISTS order_payments ( order_id TEXT NOT NULL, payment_sequential INTEGER, payment_type TEXT, payment_installments INTEGER, payment_value REAL ); CREATE TABLE IF NOT EXISTS order_reviews ( review_id TEXT PRIMARY KEY, order_id TEXT, review_score INTEGER, review_comment_title TEXT, review_comment_message TEXT, review_creation_date TEXT, review_answer_timestamp TEXT ); CREATE TABLE IF NOT EXISTS products ( product_id TEXT PRIMARY KEY, product_category_name TEXT, product_name_length REAL, product_description_length REAL, product_photos_qty REAL, product_weight_g REAL, product_length_cm REAL, product_height_cm REAL, product_width_cm REAL ); CREATE TABLE IF NOT EXISTS sellers ( seller_id TEXT PRIMARY KEY, seller_zip_code_prefix INTEGER, seller_city TEXT, seller_state TEXT ); CREATE TABLE IF NOT EXISTS geolocation ( geolocation_zip_code_prefix INTEGER, geolocation_lat REAL, geolocation_lng REAL, geolocation_city TEXT, geolocation_state TEXT ); CREATE TABLE IF NOT EXISTS users ( user_id TEXT PRIMARY KEY, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, customer_id TEXT, role TEXT CHECK(role IN ('user','admin')) NOT NULL ); CREATE TABLE IF NOT EXISTS user_sessions ( session_id TEXT PRIMARY KEY, user_id TEXT NOT NULL, created_at TEXT DEFAULT CURRENT_TIMESTAMP, last_seen_at TEXT, FOREIGN KEY(user_id) REFERENCES users(user_id) ); CREATE TABLE IF NOT EXISTS chat_history ( chat_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id TEXT NOT NULL, role TEXT NOT NULL, user_input TEXT, llm_response TEXT, explanation_display TEXT, sql_generated TEXT, df_rowcount INTEGER, llm_response_time REAL, intent TEXT, sentiment TEXT, feedback INTEGER, created_at TEXT DEFAULT CURRENT_TIMESTAMP, customer_id TEXT, FOREIGN KEY(user_id) REFERENCES users(user_id) ); CREATE TABLE IF NOT EXISTS rag_docs ( doc_id TEXT PRIMARY KEY, collection TEXT NOT NULL, title TEXT, source_uri TEXT, meta_json TEXT, created_by TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(created_by) REFERENCES users(user_id) ); CREATE TABLE IF NOT EXISTS rag_chunks ( chunk_id TEXT PRIMARY KEY, doc_id TEXT NOT NULL, collection TEXT NOT NULL, chunk_index INTEGER, text TEXT, embedding BLOB, meta_json TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(doc_id) REFERENCES rag_docs(doc_id) ); CREATE TABLE IF NOT EXISTS rag_publish_log ( id INTEGER PRIMARY KEY AUTOINCREMENT, doc_id TEXT, collection TEXT NOT NULL, action TEXT, actor_user_id TEXT, msg TEXT, created_at TEXT DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS product_category_name_translation ( product_category_name TEXT, product_category_name_english TEXT ); """) conn.commit() def initialize_db(quiet: bool = True) -> dict: #_ensure_translation_table(sqlite3.connect(DB_PATH)) 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 create_base_tables_if_missing(conn) _ensure_password_column(conn) _ensure_translation_table(conn) seed_default_users(conn) 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: exists = conn.execute( "SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1", (t,), ).fetchone() is not None if not exists: out[t] = 0 continue out[t] = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] except Exception as e: out[t] = 0 try: (globals().get('logger') or logging.getLogger('olist')).info("Row count skipped for %s: %s", t, e) except Exception: pass 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). Emits structured logs for each step. """ import time, os, sqlite3, pandas as pd, json, logging lg = logger or (globals().get("_REPOP_LG") or logging.getLogger("olist")) lg.debug("[reload] ENTER repopulate tables=%s", tables) start = time.perf_counter() if not tables: lg.warning("[reload] No tables selected.") return False, "No tables selected.", {} csv_map = get_table_csv_map() lg.info("[reload] csv_map = %s", json.dumps(csv_map, ensure_ascii=False)) # Partition into known/unknown requested = list(dict.fromkeys(tables)) known = [t for t in requested if t in csv_map] unknown = [t for t in requested if t not in csv_map] if unknown: lg.warning("[reload] skipping unknown tables (no CSV mapping): %s", unknown) to_process = _sorted_by_load_order(known) lg.info("[reload] tables (ordered) => %s", to_process) timeout = globals().get("SQL_TIMEOUT", 60.0) summary = {"tables": {}, "duration_sec": None, "errors": []} try: with sqlite3.connect(DB_PATH, timeout=timeout) as conn: try: conn.execute("PRAGMA journal_mode=WAL;") conn.execute("PRAGMA synchronous=NORMAL;") conn.execute("PRAGMA foreign_keys=OFF;") lg.debug("[reload] PRAGMA set ok") except Exception as e: lg.warning("[reload] PRAGMA set failed: %s", e) if not to_process: summary["duration_sec"] = round(time.perf_counter() - start, 3) lg.info("[reload] nothing to process (requested=%s, unknown=%s)", requested, unknown) lg.info("[reload] summary: %s", json.dumps(summary, ensure_ascii=False)) return True, "Nothing to process.", {} for t in to_process: t_start = time.perf_counter() entry = {"csv": None, "deleted_rows": None, "chunks": 0, "rows_appended": 0, "elapsed_sec": None} summary["tables"][t] = entry try: path = csv_map[t] entry["csv"] = path if not os.path.exists(path): msg = f"CSV not found: {path}" lg.error("[reload] %s", msg) summary["errors"].append({"table": t, "error": msg}) continue # DELETE try: pre = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] except Exception: pre = None if pre is not None: lg.info("[reload] %s: deleting old rows (pre=%s)", t, pre) else: lg.info("[reload] %s: deleting old rows (pre=unknown)", t) conn.execute(f"DELETE FROM {t};") try: post_del = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] except Exception: post_del = None entry["deleted_rows"] = pre if pre is not None else "unknown" if post_del not in (0, None): lg.warning("[reload] %s: expected 0 after delete, got %s", t, post_del) # LOAD lg.info("[reload] %s: loading from %s", t, path) chunk_idx = 0 total_appended = 0 for chunk in pd.read_csv(path, chunksize=50000): chunk_idx += 1 entry["chunks"] = chunk_idx # Products: fix Olist CSV typos if t == "products": try: rename_map = { "product_name_lenght": "product_name_length", "product_description_lenght": "product_description_length", } cols_lower = {c.lower(): c for c in chunk.columns} for bad, good in rename_map.items(): if bad in cols_lower: real = cols_lower[bad] chunk = chunk.rename(columns={real: good}) lg.debug("[reload] %s chunk#%d: rename shim applied", t, chunk_idx) except Exception as e: lg.warning("[reload] %s chunk#%d: rename shim failed: %s", t, chunk_idx, e) # Translation CSV coercion if t == "product_category_name_translation": for col in ["product_category_name", "product_category_name_english"]: if col in chunk.columns: try: chunk[col] = chunk[col].astype(str) except Exception as e: lg.warning("[reload] %s chunk#%d: cast %s->str failed: %s", t, chunk_idx, col, e) try: chunk.to_sql(t, conn, if_exists="append", index=False) total_appended += len(chunk) lg.debug( "[reload] %s chunk#%d: appended rows=%d (total=%d) cols=%s", t, chunk_idx, len(chunk), total_appended, list(chunk.columns) ) except Exception as e: lg.exception("[reload] %s chunk#%d: to_sql failed", t, chunk_idx) summary["errors"].append({"table": t, "chunk": chunk_idx, "error": str(e)}) break try: conn.commit() except Exception as e: lg.exception("[reload] %s: commit failed", t) summary["errors"].append({"table": t, "error": f"commit failed: {e}"}) try: final_cnt = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0] except Exception as e: final_cnt = None lg.warning("[reload] %s: COUNT(*) failed: %s", t, e) entry["rows_appended"] = total_appended entry["elapsed_sec"] = round(time.perf_counter() - t_start, 3) lg.info("[reload] %s: done in %.3fs (appended=%s, final_count=%s)", t, entry["elapsed_sec"], total_appended, final_cnt) except Exception as e: entry["elapsed_sec"] = round(time.perf_counter() - t_start, 3) lg.exception("[reload] %s: failed at table-level", t) summary["errors"].append({"table": t, "error": f"{e}"}) try: conn.execute("PRAGMA foreign_keys=ON;") except Exception as e: lg.warning("[reload] foreign_keys=ON failed at end: %s", e) try: post_counts = get_row_counts(conn, to_process) except Exception as e: lg.warning("[reload] get_row_counts failed: %s", e) post_counts = {} summary["duration_sec"] = round(time.perf_counter() - start, 3) lg.info("[reload] summary: %s", json.dumps(summary, ensure_ascii=False)) msg = "Repopulation completed." if summary["errors"]: msg = "Repopulation completed with warnings; check logs." lg.info("[reload] EXIT ok msg=%s post_counts=%s", msg, post_counts) return True, msg, post_counts except Exception as e: summary["duration_sec"] = round(time.perf_counter() - start, 3) try: lg.exception("[reload] fatal failure") lg.info("[reload] summary at failure: %s", json.dumps(summary, ensure_ascii=False)) except Exception: pass lg.info("[reload] EXIT not-ok") return False, f"Repopulation failed: {e}", {} 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(): _log_feedback_event('rate_tab.submit.call', chat_id=sel_chat_id, value=rating_value) insert_chat_feedback(chat_id, val) elif "save_chat_feedback" in globals(): _log_feedback_event('rate_tab.submit.call', chat_id=sel_chat_id, value=rating_value) save_chat_feedback(chat_id, val) elif "upsert_chat_feedback" in globals(): _log_feedback_event('rate_tab.submit.call', chat_id=sel_chat_id, value=rating_value) 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): _log_feedback_event('get_recent_chats_for_user.begin', locals=locals().copy()) 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") _log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy()) return "greeting" if pat_thanks.search(tv): logger.info("Matching label is : thanks") _log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy()) return "thanks" if pat_smalltalk.search(tv): logger.info("Matching label is : smalltalk") _log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy()) return "smalltalk" if pat_complaint.search(tv): logger.info("Matching label is : complaint") _log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy()) return "complaint" _log_feedback_event('get_recent_chats_for_user.end', locals=locals().copy()) 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 <context>: %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 <context>: %s", e) def simple_login_screen(): # Ensure captcha tuple exists if "captcha_simple" not in st.session_state or not isinstance(st.session_state.captcha_simple, tuple) or len(st.session_state.captcha_simple) != 2: try: st.session_state.captcha_simple = _make_captcha() except Exception: import random a, b = random.randint(1, 9), random.randint(1, 9) st.session_state.captcha_simple = (a, b) """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 <context>: %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): try: log_llm_inputs("pre_llm_invoke", (locals().get("auth") or {}).get("user_id") if "auth" in locals() else None, locals().get("chat_id"), str(locals().get("OPENAI_MODEL") or locals().get("ANTHROPIC_MODEL") or "model"), locals().get("messages") or []) except Exception: pass 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): import json as _json schema_json = _json.dumps({t:list(schema_registry[t]["columns"].keys()) for t in schema_registry}) # === RAG retrieval (1212) — your original quick exemplars === 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_text_side = RAG.query(ex_query, k=3, where={"schema_fp": schema_fp} if RAG_BACKEND=="CHROMA" else None) ex_blocks = [] for ex in exemplars_text_side: 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 = "" # === Additional exemplar few-shots (prepended system/name=exemplar messages) === ex_msgs = [] try: exemplars = [] try: # this helper should exist in your codebase (or be added as discussed earlier) exemplars = _rag_fetch_exemplars_for_query(user_text, k=4) or [] except Exception: exemplars = [] for ex in exemplars: uq = (ex.get("user_input") or "").strip() plan = (ex.get("llm_plan") or "").strip() sql = (ex.get("secured_sql") or "").strip() if not (uq and sql): # Fallback: try to parse from a published "doc" line format doc = (ex.get("doc") or "") try: import re as _re _uq = _re.search(r"(?mi)^Q:\s*(.+)$", doc) _plan = _re.search(r"(?mi)^Plan:\s*(.+)$", doc) _sql = _re.search(r"(?mi)^SQL:\s*(.+)$", doc) uq = uq or (_uq and _uq.group(1).strip()) or "" plan = plan or (_plan and _plan.group(1).strip()) or "" sql = sql or (_sql and _sql.group(1).strip()) or "" except Exception: pass if uq and sql: ex_msgs.append({ "role": "system", "name": "exemplar", "content": f"EXEMPLAR\nQ: {uq}\nSQL: {sql}\n{('PLAN: ' + plan) if plan else ''}" }) except Exception: # swallow exemplar assembly errors ex_msgs = [] 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}""" # Build planner messages: system -> (prepended exemplars) -> user planner_messages = [{"role": "system", "content": sys_prompt}] if ex_msgs: planner_messages.extend(ex_msgs) planner_messages.append({"role": "user", "content": user_block}) # Telemetry: include trimmed exemplar summary in logs try: _ex_tele = [] for m in ex_msgs[:4]: try: lines = (m.get("content") or "").splitlines() qline = next((ln for ln in lines if ln.strip().startswith("Q:")), "") _ex_tele.append({"q": qline[2:].strip()[:120] if qline else "", "has_sql": any(ln.strip().startswith("SQL:") for ln in lines)}) except Exception: _ex_tele.append({"q": "", "has_sql": False}) # pull session user/chat if available session_user_id = None session_chat_id = None try: import streamlit as st _auth = st.session_state.get("auth") or {} session_user_id = _auth.get("user_id") session_chat_id = st.session_state.get("chat_id") except Exception: pass try: # safe call if your codebase has this logger log_llm_inputs( event="pre_llm_invoke", user_id=session_user_id, chat_id=session_chat_id, model="model", messages=planner_messages, context="", exemplars=_ex_tele, plan={}, extra={} ) except Exception: pass except Exception: pass out = llm.chat(planner_messages) try: return _json.loads(out) except Exception: return {"queries":[str(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<alias>[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<sub>.*?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<alias>[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"} try: _LANG_TO_TAG # type: ignore except NameError: _LANG_TO_TAG = { "en": "eng_Latn", "hi": "hin_Deva", "mr": "mar_Deva", "bn": "ben_Beng", "ta": "tam_Taml", "te": "tel_Telu", "gu": "guj_Gujr", "pa": "pan_Guru", "kn": "kan_Knda", "ml": "mal_Mlym", "or": "ory_Orya", "as": "asm_Beng", "ur": "urd_Arab", "ne": "npi_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, src_tag: str) -> str: """ Ensure `text` begins with a valid IndicTrans language tag (e.g., "eng_Latn "), not the older ">>eng_Latn<<" bracketed form. If already present, return as-is. """ try: s = (text or "").lstrip() # Already prefixed with eng_Latn / en_Latn / <xxx>_Latn pattern at the start? if re.match(r"^[A-Za-z]{2,3}_[A-Za-z]{4}\s", s): return s # Also accept bracketed tags and normalize them away m = re.match(r"^>>([A-Za-z]{2,3}_[A-Za-z]{4})<<\s*", s) if m: s = re.sub(r"^>>([A-Za-z]{2,3}_[A-Za-z]{4})<<\s*", "", s) tag = (src_tag or "eng_Latn").strip() if not re.match(r"^[A-Za-z]{2,3}_[A-Za-z]{4}$", tag): tag = "eng_Latn" return f"{tag} " + s except Exception: return f"{src_tag or 'eng_Latn'} " + (text or "") def _norm_lang(lang: str | None) -> str: try: s = (lang or "").strip().lower().replace("-", "_") return s except Exception: return "en" def _indic_tag(lang: str, default: str = "eng_Latn") -> str: """Return IndicTrans language tag for a given language code. - If `lang` already looks like a tag (contains '_'), return as-is. - Else normalize and map via _LANG_TO_TAG. """ base = _norm_lang(lang) if "_" in base: return base return _LANG_TO_TAG.get(base, default) def _fix_tokenizer_lang(tok, src_tag: str, tgt_tag: str) -> None: """Set tokenizer src/tgt language attributes regardless of version quirks.""" try: tok.src_lang = src_tag tok.tgt_lang = tgt_tag except Exception: setattr(tok, "src_lang", src_tag) setattr(tok, "tgt_lang", tgt_tag) def _tx_normalize_target_code(lang: str) -> str | None: """Return an IndicTrans2 tag for the given short language code, or None.""" short = (lang or "en").split("-")[0].lower() return _LANG_TO_TAG.get(short) def _sanitize_lang_tag(val: str | None) -> str | None: """Strip accidental >>...<< and whitespace from a lang tag, return None if empty.""" if not val: return val v = str(val).strip() if v.startswith(">>") and v.endswith("<<"): v = v[2:-2].strip() return v or None def _ensure_tag_prefixed(text: str, tag: str) -> str: """Ensure text is prefixed with >>{tag}<< (required by IndicTrans2).""" prefix = f">>{tag}<< " t = (text or "").lstrip() return t if t.startswith(prefix) else prefix + t # ---- Translation cache / accessors (place near top-level globals) ---- import threading _TX = None _TX_lock = threading.Lock() @st.cache_resource(show_spinner=False) def _load_tx_models_cached(): # Reuse your existing loader; it returns {"ready": bool, ...} on success/failure return _load_tx_models() def _get_tx(): """Return a ready translation bundle or a stub with ready=False.""" global _TX if _TX and _TX.get("ready"): return _TX try: with _TX_lock: if _TX and _TX.get("ready"): return _TX _TX = _load_tx_models_cached() return _TX or {"ready": False, "error": "no result from loader"} except Exception as e: logger.warning("[tx] _get_tx failed: %s", e, exc_info=True) return {"ready": False, "error": str(e)} def _tx_translate_en_to(text: str, tgt_lang: str) -> str: """Translate EN -> tgt_lang using the cached IndicTrans2 bundle.""" try: info = _get_tx() # accessor around the cached _TX if not info.get("ready"): logger.warning("[tx] translate skipped: backend not ready (%s)", info.get("error")) return text tok = info.get("en_indic_tok") mod = info.get("en_indic_mod") if not tok or not mod: logger.warning("[tx] translate skipped: missing tokenizer/model in bundle") return text src_tag = _indic_tag("en") # -> 'eng_Latn' tgt_tag = _indic_tag(tgt_lang, "hin_Deva") # -> 'hin_Deva' for Hindi (default) _fix_tokenizer_lang(tok, src_tag, tgt_tag) # set attributes best-effort # *** CRITICAL *** Always prefix with the source tag so the tokenizer never # tries to treat the first word as a tag. This fixes "Invalid source language tag: Pls". tagged = f">>{src_tag}<< " + str(text) logger.info("[tx] _tx_translate_en_to: tokenize start | src=%s tgt=%s", src_tag, tgt_tag) # Do NOT pass src_lang/tgt_lang via kwargs (some builds ignore/override oddly); # rely on the prefixed text + attributes we set above. enc = tok( [tagged], return_tensors="pt", padding=True, truncation=True, ) # Move to model device if available try: import torch enc = {k: v.to(mod.device) for k, v in enc.items()} infer_ctx = torch.inference_mode() except Exception: class _Null: def __enter__(self): return None def __exit__(self, *a): return False infer_ctx = _Null() # If tokenizer exposes get_lang_id, force BOS to target language gen_kwargs = dict(max_new_tokens=256, num_beams=4, early_stopping=True) try: forced_bos = tok.get_lang_id(tgt_tag) if forced_bos is not None: gen_kwargs["forced_bos_token_id"] = forced_bos except Exception: pass with infer_ctx: out_ids = mod.generate(**enc, **gen_kwargs) out = tok.batch_decode(out_ids, skip_special_tokens=True) result = (out[0] if out else "").strip() logger.info("[tx] _tx_translate_en_to: decode ok | len=%d", len(result)) return result or text except Exception: logger.exception("[tx] _tx_translate_en_to failed; returning original") return text def _ensure_tx_loaded() -> bool: """Ensure the translation models are in memory (_TX ready).""" try: global _TX except NameError: _TX = None # if not defined, create it try: if not _TX: # If you have @st.cache_resource on _load_tx_models, this will still be cheap. if "streamlit" in globals(): # Accessing via globals avoids import cycles in some layouts pass if not _TX or not isinstance(_TX, dict) or not _TX.get("ready"): # Try to call your loader if it exists loader = globals().get("_load_tx_models") if callable(loader): _TX = loader() return bool(_TX and _TX.get("ready")) except Exception: return False def _localize_msg(msg: str, tgt_lang: str) -> str: """Localize an English UI string to the target language (no-op if English).""" try: logger.info("[tx] _localize_msg: msg=%s | tgt_lang=%s", msg, tgt_lang) if (tgt_lang or "en").lower().startswith("en"): return msg return _tx_translate_en_to(msg, tgt_lang) except Exception: logger.exception("[tx] _localize_msg failed; returning original") return msg def _safe_localize(msg: str, tgt_lang: str) -> str: try: return _localize_msg(msg, tgt_lang) or msg except Exception: return msg # ----------------------------------------------------------------------- # 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. """ logger.info("[tx] _tx_safe: text is =%s tgt_lang is =%s", text, tgt_lang) 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 _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 # Translation loader (IndicTrans2) – safe, cached, and quiet @st.cache_resource(show_spinner=False) def _load_tx_models(): if not TRANSLATION_ENABLED: return {"ready": False, "error": "Translation disabled"} try: # define nullcontext early try: from contextlib import nullcontext except Exception: class nullcontext: def __enter__(self): return None def __exit__(self, *a): return False # reduce HF warnings import warnings warnings.simplefilter("ignore", category=UserWarning) Shield = globals().get("_TorchNoiseStderrShield") cm = Shield() if Shield else nullcontext() with cm: from transformers import AutoModelForSeq2SeqLM, AutoTokenizer import torch cache_dir = os.getenv("TRANSFORMERS_CACHE", "/data/hf-cache/transformers") device = "cuda" if torch.cuda.is_available() else "cpu" logger.info("[tx] loading IndicTrans2 (device=%s)", device) with (Shield() if Shield else nullcontext()): # 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 (device=%s, trust_remote_code=True)", device) 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 logger.debug("_tx_from_en_old calling _load_tx_models") 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 '<src> <tgt> ' + 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 <tgt> ' + 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" s = _rewrite_limit_and(s) 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" s = _rewrite_limit_and(s) 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" s = _rewrite_limit_and(s) 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" s = _rewrite_limit_and(s) 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" s = _rewrite_limit_and(s) 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) s = _rewrite_limit_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" text = _inject_where(text, f"{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.", } # Tailor message if this is a customers table block and user provided an order-id-like token _suggest = None if reason == "customers_user_block": try: import re as _re_hint _m = _re_hint.search(r"\b[0-9a-f]{32}\b", (user_text or ""), _re_hint.IGNORECASE) if _m: _oid = _m.group(0) _suggest = f"Did you want the status of an order? If yes, please type: pls provide details of my order {_oid}" except Exception: _suggest = None _msg = _suggest or msg_map.get(reason, msg_map["blocked"]) entry["df"] = pd.DataFrame({"message": [_tx_safe(_msg, 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 = (SHOW_EXPLAIN_PLAN and 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: # Start with the base SELECT (use triple quotes for multi-line string) q = """ SELECT chat_id, created_at, user_id, role, user_input, intent, sentiment, llm_response_time, secured_sql, COALESCE(feedback_value, feedback) AS feedback FROM chat_history """ conds, params = [], [] 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 datetime(created_at) DESC LIMIT 50" 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 _log_feedback_event('rate_tab.submit.call', hint='update_chat_feedback_if_owner\\s*\\') 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): _log_feedback_event('update_chat_feedback_if_owner.begin', locals=locals().copy()) 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. 🙌") _log_feedback_event('update_chat_feedback_if_owner.end', locals=locals().copy()) 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 "" _log_feedback_event('update_chat_feedback_if_owner.end', locals=locals().copy()) 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(_safe_localize("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""" <span style=" display:inline-block; padding:6px 10px; border-radius:999px; font-weight:600; font-size:0.9rem; color:{fg}; background:{bg}; border:1px solid rgba(0,0,0,0.05); ">{text}</span> """ 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 render_chat_output(out): # BEGIN: hide explain plan unless explicitly enabled try: if not SHOW_EXPLAIN_PLAN: _plan = None try: _plan = (out.get("plan") or {}) if isinstance(out, dict) else None except Exception: _plan = None if isinstance(_plan, dict): _plan["explanation_display"] = None _plan["explanation"] = None out["plan"] = _plan if isinstance(out, dict): if "explanation_display" in out: out["explanation_display"] = None if "explanation" in out: out["explanation"] = None except Exception: pass # END: hide explain plan 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" tgt_lang = st.session_state.get("last_user_lang", "en") logger.info("[tx] about to localize assistant bubble: tgt_lang=%s", tgt_lang) with st.chat_message("assistant"): st.write(_safe_localize(_assistant_msg, tgt_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('<div class="oa-sql">', unsafe_allow_html=True) st.code(sql_text if sql_text else "-- no SQL generated --", language="sql") st.markdown('</div>', unsafe_allow_html=True) br = card.get("blocked_reason") if br == "seller_revenue_user_block": st.warning(_safe_localize("🔒 Seller revenue queries are not available to normal users. Please contact an admin.",tgt_lang )) if br == "product_revenue_user_block": st.warning(_safe_localize("🔒 Product revenue queries are not available to normal users. Please contact an admin.",tgt_lang )) if br == "platform_payments_user_block": st.warning(_safe_localize("🔒 Platform-wide payments are restricted to admins.",tgt_lang )) if br == "rowcounts_all_tables_block": st.warning(_safe_localize("🔒 Requests for row counts across all tables / each table are restricted.",tgt_lang )) if br == "write_block": st.warning(_safe_localize("🔒 Write/DDL operations are disabled.",tgt_lang )) if br == "introspection_user_block": st.warning(_safe_localize("🔒 Listing tables/indexes/schema is admin-only.",tgt_lang )) if br == "dump_all_block": st.warning(_safe_localize("🔒 Requests to list/dump all database data are restricted.",tgt_lang )) if br == "count_user_block": st.warning(_safe_localize("🔒 Row-count (COUNT) queries are restricted for normal users.",tgt_lang )) if br == "customers_user_block": st.warning(_safe_localize("🔒 Access to the **customers** table is restricted for normal users.",tgt_lang )) if br == "destructive_request_block": st.warning(_safe_localize("🔒 Destructive actions (delete/update/drop) are not allowed.",tgt_lang )) if card.get("policy_note"): st.info(_safe_localize(str(card["policy_note"]), tgt_lang)) if card.get("error"): st.error(_safe_localize(str(card["error"]), tgt_lang)) else: df_to_show = card.get("df", None) if isinstance(df_to_show, pd.DataFrame): if card.get("limit_enforced"): st.markdown( '<div class="oa-note">For performance, results are limited to <b>50</b> rows. ' 'Include the word <b>“All”</b> in your question to bypass this cap (may be slower).</div>', unsafe_allow_html=True ) if df_to_show.empty: st.info(_safe_localize("No rows returned.",tgt_lang )) 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 _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 # --- Visibility of explain/plan in chat output --- SHOW_EXPLAIN_PLAN = False # set True to show, default hides def _ensure_repopulate_logger(): """Dedicated logger for table repopulation with no duplicate handlers on rerun.""" lg = logging.getLogger("olist.repopulate") if not getattr(lg, "_initialized", False): lg.setLevel(logging.DEBUG) fmt = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s") ch = logging.StreamHandler(stream=sys.stdout) ch.setLevel(logging.DEBUG) ch.setFormatter(fmt) setattr(ch, "_olist_marker", True) lg.addHandler(ch) try: os.makedirs("/data", exist_ok=True) fh = logging.FileHandler("/data/repopulate.log", encoding="utf-8") fh.setLevel(logging.DEBUG) fh.setFormatter(fmt) setattr(fh, "_olist_marker", True) lg.addHandler(fh) except Exception as e: logging.getLogger("olist").warning("file logging disabled: %s", e) lg.propagate = False lg._initialized = True return lg def _ensure_translation_table(conn): """Create the optional Olist translation table if it doesn't exist.""" try: logger.info("_ensure_translation_table starts") conn.execute( "CREATE TABLE IF NOT EXISTS product_category_name_translation (" "product_category_name TEXT, " "product_category_name_english TEXT" ")" ) conn.commit() logger.info("_ensure_translation_table Completed successfully") except Exception: # best-effort; never block startup pass 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 = { "tag": "exemplar", "intent": intent, # str "user_id": user_id, "chat_id": chat_id, "schema_fp": schema_fp, # str "tables_used": tables_used_str, # <-- now str "policy_version": policy_version, # str "created_at": datetime.utcnow().isoformat(), # str } logger.info("[rag-publish] target dir=%s col=%s", registry.get("chroma_dir"), registry.get("chroma_collection")) logger.info("[rag-publish] about to upsert ids=%s meta=%s", [ex_id], metadata) RAG.upsert_exemplar(ex_id, ex_text, metadata) logger.info("[rag-publish] upserted exemplar id=%s", ex_id) 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,"chat_id", intent_col, question_col,"llm_plan", 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 logger.info("[rag-publish] CLICKED publish button") logger.info("[rag-publish] edited shape=%s selected_rows=%s", edited.shape, int(edited.get("Select", pd.Series()).sum() if "Select" in edited else 0)) # Map selection back to the original df using pub_key if "pub_key" not in view.columns: st.error("Internal error: 'pub_key' not present in grid.") st.stop() selected_rows = edited[edited["Select"]] selected_keys = set(selected_rows["pub_key"].astype(str).tolist()) logger.info("[rag-publish] selected_keys=%s", list(selected_keys)[:10]) if not selected_keys: st.info("No rows selected.") st.stop() # Ensure df has pub_key as string for matching df["_pub_key_str"] = df["pub_key"].astype(str) publish_df = df[df["_pub_key_str"].isin(selected_keys)].copy() for _, row in publish_df.iterrows(): # Resolve ids safely chat_id_val = str(row.get("chat_id") or row.get("pub_key") or "") user_id_val = str(row.get(user_id_col)) if (user_id_col and user_id_col in row) else "" payload = { "chat_id": chat_id_val, "user_id": user_id_val, "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: # If your publisher supports meta=..., prefer passing meta explicitly: meta = { "tag": "exemplar", "source": "admin_rag_publisher/positive_chats", "user_id": user_id_val, "chat_id": chat_id_val, "pub_key": str(row.get("pub_key") or ""), "created_at": str(row.get(ts_col) or ""), "intent": payload.get("intent", ""), } ex_id = publish_exemplar_from_row(payload, registry, meta=meta) if ex_id: ok += 1 client = registry.get("chroma_client") or get_chroma_client() col = client.get_or_create_collection(registry.get("chroma_collection", "olist_exemplars")) try: logger.info("[rag-publish] collection count after upsert: %s", col.count()) except Exception as e: logger.warning("[rag-publish] count() failed: %s", e) mark_published(str(row.get("pub_key") or "")) 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(): import os import json import datetime import sqlite3 import pandas as pd try: logging.getLogger("olist").info( "[rag-publish] render start | DB=%s | CHROMA_PATH=%s | RAG_COLLECTION=%s", os.environ.get("DB_PATH", "/data/olist.sqlite"), os.environ.get("CHROMA_PATH", os.environ.get("RAG_DIR", "/data/rag_store")), os.environ.get("RAG_COLLECTION", "olist_sql_exemplars"), ) except Exception: pass # Streamlit is optional at import time in some environments try: import streamlit as st except Exception: # pragma: no cover return # 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 open 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 # mark-published table 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) has_fb = "feedback" in cols has_fb_val = "feedback_value" in cols role_col = next((c for c in ["user_role", "role"] if c in cols), None) intent_col = next((c for c in ["intent", "intent_label", "non_sql_label", "label"] if c in cols), None) 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 (has_fb or has_fb_val): st.error("`chat_history` must have a primary key and at least one feedback column (feedback / feedback_value).") conn.close() return # positive feedback predicate if has_fb and has_fb_val: POS_FEEDBACK = ["(COALESCE(feedback,0)=1 OR COALESCE(feedback_value,0)=1)"] elif has_fb: POS_FEEDBACK = ["(feedback = 1)"] else: POS_FEEDBACK = ["(feedback_value = 1)"] # UI 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 include_published = True # default behavior in your baseline disable_intent_exclusion = False default_excluded = {"greeting", "thanks", "smalltalk", "complaint", "others"} # build WHERE where = [] params = [] where += POS_FEEDBACK if role_col: where.append(f"({role_col} IS NULL OR LOWER({role_col})='user')") if created: where.append(f"{created} BETWEEN ? AND ?") params += [f"{start_date} 00:00:00", f"{end_date} 23:59:59"] if not include_published: where.append(f"{pk} NOT IN (SELECT chat_id FROM rag_published)") # intent (optional) intent_params = [] intent_clause = None if intent_col and not disable_intent_exclusion: ex = sorted(default_excluded) placeholders = ",".join(["?"] * len(ex)) intent_clause = 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" # small helper to debug counts 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 base_pos = _count(" AND ".join(POS_FEEDBACK), []) base_role = _count(" AND ".join(POS_FEEDBACK + ([f"({role_col} IS NULL OR LOWER({role_col})='user')"] if role_col else [])), []) base_date = None if created: base_date = _count( " AND ".join( POS_FEEDBACK + ([f"({role_col} IS NULL OR LOWER({role_col})='user')"] if role_col 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 = POS_FEEDBACK[:] if role_col: _w.append(f"({role_col} IS NULL OR LOWER({role_col})='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 = POS_FEEDBACK[:] if role_col: _w.append(f"({role_col} IS NULL OR LOWER({role_col})='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_where = where[:] final_params = params[:] if intent_clause and (base_intent is None or base_intent > 0): final_where.append(intent_clause) final_params += intent_params sql = f""" SELECT {", ".join(select_cols)} FROM chat_history WHERE {" AND ".join(final_where)} {order_by} LIMIT ? """ final_params += [int(max_fetch)] # query rows try: rows = conn.execute(sql, final_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 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", } ) 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 or "") return s if len(s) <= 300 else s[:300] + "…" 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)] logging.getLogger("olist").info( "[rag-publish] grid rows=%s | selected_ids(sample)=%s", len(df), selected_ids[:1] ) 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_name = os.environ.get("RAG_COLLECTION", "olist_sql_exemplars") coll = coll_name # keep a short alias used later push = st.button("Publish selected to RAG") if push: lg = logging.getLogger("olist") lg.info("[rag-publish] CLICKED | selected_total=%s", len(selected_ids)) if not selected_ids: st.warning("Please select at least one row.") conn.close() return if intent_col: _intent_exclude = ("help_greeting", "thanks", "farewell", "smalltalk", "joke", "chitchat") _intent_filter_sql = f"AND COALESCE(LOWER({intent_col}), '') NOT IN ({','.join(['?']*len(_intent_exclude))})" else: _intent_exclude = () _intent_filter_sql = "" _plan_req = (plan_col or "NULL") _sql_req = (sql_col or ("sql_generated" if "sql_generated" in cols else None)) _text_not_empty = f"AND TRIM({_plan_req}) <> ''" if plan_col else "" _sql_not_empty = f"AND TRIM({_sql_req}) <> ''" if _sql_req else "" 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, {role_col if role_col else "NULL"} AS role, COALESCE(user_id, NULL) AS user_id FROM chat_history WHERE {pk} IN ({qmarks}) {_intent_filter_sql} {_text_not_empty} {_sql_not_empty} {order_by} """ _params = list(selected_ids) if intent_col: _params += [s.lower() for s in _intent_exclude] lg.info( "[rag-publish] fetch_selected | ids=%s | intent_col=%s | plan_col=%s | sql_col=%s", selected_ids, intent_col, plan_col, sql_col, ) try: chosen = conn.execute(sql2, _params).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 or "") filtered = [] for r in chosen[:50]: plan_txt = _plan_text(r["llm_plan"]) sql_txt = (r["secured_sql"] or "").strip() if plan_txt.strip() and sql_txt.strip(): filtered.append(r) lg.info("[rag-publish] eligible_to_publish=%s (from %s chosen)", len(filtered), len(chosen)) if not filtered: st.warning("Nothing eligible to publish: either intent excluded or plan/SQL empty.") conn.close() return # Build docs + metadata for upsert docs, ids, metas = [], [], [] for r in filtered: cid = int(r["chat_id"]) uq = (r["user_input"] or "").strip() plan_txt = _plan_text(r["llm_plan"]) doc = f"Q: {uq}\nPlan: {plan_txt}\nSQL: {(r['secured_sql'] or '').strip()}" docs.append(doc) ids.append(f"chat_{cid}") metas.append( { "tag": "exemplar", "user_id": (r["user_id"] or None), "chat_id": str(cid), "source": "feedback_curated", "secured_sql": (r["secured_sql"] or None), "user_input": uq or None, "llm_plan": plan_txt or None, } ) # RAG push try: import chromadb from chromadb.utils import embedding_functions # choose embedding function to match your reader path openai_key = os.environ.get("OPENAI_API_KEY") or os.environ.get("OPENAI_APIKEY") ef = None ef_dim = None if openai_key: embed_model = os.environ.get("OPENAI_EMBED_MODEL", "text-embedding-3-small") ef = embedding_functions.OpenAIEmbeddingFunction(api_key=openai_key, model_name=embed_model) ef_dim = OPENAI_EMBED_DIM else: try: from chromadb.utils.embedding_functions.onnx_mini_lm_l6_v2 import ONNXMiniLM_L6_V2 ef = ONNXMiniLM_L6_V2() ef_dim = ONNX_EMBED_DIM except Exception: ef = None ef_dim = None chroma_path = os.environ.get("CHROMA_PATH", os.environ.get("RAG_DIR", "/data/rag_store")) lg.info("[rag-publish] chroma client init | path=%s | collection=%s", chroma_path, coll) client = chromadb.PersistentClient(path=chroma_path) try: rag = _get_or_migrate_chroma_collection(client, coll, ef, (ef_dim or OPENAI_EMBED_DIM), lg) except Exception: rag = client.get_or_create_collection(coll, embedding_function=ef) # log an example meta row if ids: lg.info( "[rag-publish] upsert begin | docs=%s | count_before=%s", len(docs), getattr(rag, "count", lambda: None)(), ) lg.info("[rag-publish] sample id=%s | meta=%s", ids[0], {k: metas[0].get(k) for k in ("tag", "user_id", "chat_id", "source")}) if docs: rag.upsert(documents=docs, ids=ids, metadatas=metas) lg.info("[rag-publish] upsert done | pushed=%s | count_after=%s", len(docs), getattr(rag, "count", lambda: None)()) 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: lg.exception("RAG push failed | coll=%s | chroma_path=%s", coll, os.environ.get("CHROMA_PATH", os.environ.get("RAG_DIR", "/data/rag_store"))) except Exception: pass # Mark published try: conn.executemany( "INSERT OR IGNORE INTO rag_published (chat_id, pushed_at, collection) VALUES (?, datetime('now'), ?)", [(int(r["chat_id"]), coll) for r in filtered], ) conn.commit() lg.info("[rag-publish] marked published | ids=%s | coll=%s", [int(r["chat_id"]) for r in filtered], coll) except Exception: pass # 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("""<style> div[data-testid=stVegaLiteChart] {height: 220px !important;} div[data-testid=stPlotlyChart] {height: 220px !important;} .css-1kyxreq, .css-1ht1j8u {font-size: 0.95rem !important;} </style>""", 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' try: st.session_state["last_user_lang"] = src_lang except Exception: pass logger.info("[chat] _detect_lang 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) try: log_router_decision(locals().get("chat_id"), (locals().get("auth") or {}).get("user_id") if "auth" in locals() else None, locals().get("q_for_llm") or locals().get("user_text") or "", locals().get("non_sql_label"), locals().get("src_lang") or locals().get("lang") or None, "non-sql" if locals().get("non_sql_label") else "sql") except Exception: pass 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 #_log_feedback_event('rate_tab.submit.call', chat_id=chat_id_prev, value=rating_value) 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() def _debug_csv_map_snapshot(): try: m = get_table_csv_map() return json.dumps(m, ensure_ascii=False, indent=2) except Exception as e: return f"<error getting map: {e}>" _REPOP_LG = _ensure_repopulate_logger() # ---- RAG Publisher: narrow SQL logging to chat_history only (idempotent) ---- # ---- BEGIN RAG Publisher SQL logging (idempotent; chat_history-only) ---- def _install_ragpub_sql_logging(): """ Install an idempotent sqlite3 execute() shim that logs ONLY statements which touch the `chat_history` table. Also prints the COUNT value when a COUNT(*) is detected so row-count probes are understandable in logs. """ import sqlite3 as _sqlite3 import time as _time import re as _re global _RAGPUB_SHIM_INSTALLED try: _RAGPUB_SHIM_INSTALLED except NameError: _RAGPUB_SHIM_INSTALLED = False if _RAGPUB_SHIM_INSTALLED: return logger = _get_logger("olist") def _touches_chat_history(sql_text: str) -> bool: try: s = (sql_text or "").lower() except Exception: return False # be tolerant to quoting / aliasing return bool(_re.search(r"\bchat_history\b", s)) _orig_execute = _sqlite3.Connection.execute _orig_executescript = _sqlite3.Connection.executescript def _wrap_execute(self, sql, params=()): sql_str = sql if isinstance(sql, str) else str(sql) if not _touches_chat_history(sql_str): return _orig_execute(self, sql, params) start = _time.perf_counter() try: cur = _orig_execute(self, sql, params) rows = cur.fetchall() if cur.description else [] elapsed = _time.perf_counter() - start cols = [d[0] for d in (cur.description or [])] # If it's a COUNT probe, pull the scalar for clarity sql_lc = (sql_str or "").lower() count_note = "" try: if "count(" in sql_lc: count_val = None if rows: first = rows[0] try: # mapping-like then positional if hasattr(first, "keys"): for k in ("n", "count", "count_1", "total", "cnt"): if k in first.keys(): count_val = first[k]; break if count_val is None and isinstance(first, (list, tuple)) and first: count_val = first[0] except Exception: pass if count_val is not None: count_note = f" (COUNT={count_val})" except Exception: pass logger.info("[ragpub] read_sql: %s", sql_str) logger.info("[ragpub] result rows=%s cols=%s (%.3fs)%s", len(rows), cols, elapsed, count_note) # Return a lightweight cursor-like facade compatible with pandas.read_sql usage # In practice, our code paths call sqlite3 directly for COUNT; others # consume DataFrames already, so returning the raw rows is fine. return rows except Exception as e: elapsed = _time.perf_counter() - start logger.exception("[ragpub] SQL error after %.3fs: %s", elapsed, e) raise def _wrap_executescript(self, sql_script): return _orig_executescript(self, sql_script) _sqlite3.Connection.execute = _wrap_execute _sqlite3.Connection.executescript = _wrap_executescript _RAGPUB_SHIM_INSTALLED = True # Install once (safe no-op on reruns) try: _install_ragpub_sql_logging() except Exception: pass # ---- END RAG Publisher SQL logging ----
from __future__ import annotations
import logging
import re as _re
# ---- BEGIN RAG Publisher SQL logging (idempotent, single-pre/post; chat_history only) ----
# --- RAG Publisher SQL logging (safe wrapper; no C-type monkey patching) ---
# === Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
# === Prompt & SQL Loggers ===
prompt_logger = logging.getLogger("prompt")
sql_logger = logging.getLogger("sql")
for _lg in (prompt_logger, sql_logger):
if not _lg.handlers:
_h = logging.StreamHandler()
_h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
_lg.addHandler(_h)
_lg.setLevel(logging.INFO)
# === End Prompt & SQL Loggers ===
# === Redaction & Logging Helpers ===
import re as _re
from typing import Any as _Any
_REDACTIONS = [
(_re.compile(r"sk-[A-Za-z0-9]{10,}"), "sk-***REDACTED***"),
(_re.compile(r"(?i)api[-_ ]?key[:=]\s*[\w-]{8,}"), "API_KEY=***REDACTED***"),
(_re.compile(r"[\w\.-]+@[\w\.-]+\.\w+"), "<email>"),
]
def _redact(text: str) -> str:
if not text:
return text
out = text
for _rx, _repl in _REDACTIONS:
out = _rx.sub(_repl, out)
return out
def _truncate(text: str, max_chars: int = 4000) -> str:
if not text:
return text
return text if len(text) <= max_chars else text[:max_chars] + " ...<truncated>"
def _j(_obj: _Any) -> str:
try:
import json as _json
return _json.dumps(_obj, ensure_ascii=False, default=str)
except Exception:
return str(_obj)
def log_llm_inputs(event: str,
user_id: str | None,
chat_id: str | None,
model: str,
messages: list[dict],
context: str | None = None,
exemplars: list[dict] | None = None,
plan: dict | None = None,
extra: dict | None = None):
payload = {
"event": event,
"user_id": user_id,
"chat_id": chat_id,
"model": model,
"context": _truncate(_redact(context or ""), 3000),
"exemplars": exemplars or [],
"plan": plan or {},
"messages": [],
"extra": extra or {}
}
for m in messages or []:
payload["messages"].append({
"role": m.get("role"),
"name": m.get("name"),
"content": _truncate(_redact(str(m.get("content", ""))), 3000)
})
try:
prompt_logger.info(_j(payload))
except Exception as _e:
try:
prompt_logger.warning("log_llm_inputs failed: %s", _e)
except Exception:
pass
def log_secured_sql(chat_id: str | None,
user_id: str | None,
secured_sql: str | None,
sql_params: dict | list | None = None,
table_guard: dict | None = None):
if not secured_sql:
return
payload = {
"event": "secured_sql_ready",
"chat_id": chat_id,
"user_id": user_id,
"sql": _truncate(_redact(secured_sql), 4000),
"params": sql_params or {},
"guards": table_guard or {},