Spaces:
Paused
Paused
Download app_2.py from SuhasGholkar/bot: direct link, hf CLI and curl.
- Browser
- Download file 261 kB
-
https://huggingface.co/spaces/SuhasGholkar/bot/resolve/main/app_2.py
- Command line
-
hf download hf://spaces/SuhasGholkar/bot/app_2.py
-
curl -L -o app_2.py https://huggingface.co/spaces/SuhasGholkar/bot/resolve/main/app_2.py
261 kB
# --- FIX v2: logger dedupe + run-once guards (26-Sep-2025) ---
# === LLM compatibility helpers ===
# =====================
# Chat LLM compatibility shims (robust)
# =====================
class _LLMChatAdapter:
"""Adapter that exposes a .chat(messages) method for any supported LLM spec."""
def __init__(self, spec):
self._spec = spec
def chat(self, messages):
return _llm_chat(self._spec, messages)
class _DictChatShim(dict):
"""A dict that also supports .chat(messages) by delegating to _llm_chat."""
def chat(self, messages):
return _llm_chat(dict(self), messages)
# === PATCH: robust LLM wrappers (override previous definitions) ========================
# ---- BEGIN ROBUST LLM WRAPPERS (drop-in) ------------------------------------
def _wrap_text_response(text: str):
"""Normalize plain text into an OpenAI-like chat response dict."""
return {
"choices": [
{
"message": {
"role": "assistant",
"content": text if text is not None else ""
}
}
]
}
def _extract_content_from_response(resp):
"""
Try to extract assistant text from many possible response shapes:
- OpenAI ChatCompletion (dict-like): resp["choices"][0]["message"]["content"]
- OpenAI Responses API: resp.output_text or resp["output_text"]
- Plain string
- Objects with .choices[0].message.content
Always return a string.
"""
try:
if resp is None:
return ""
# Plain string
if isinstance(resp, str):
return resp
# Dict-like
if isinstance(resp, dict):
# Chat Completions shape
ch = resp.get("choices")
if ch and isinstance(ch, list):
first = ch[0] if ch else {}
msg = first.get("message") if isinstance(first, dict) else None
if isinstance(msg, dict):
return msg.get("content", "") or ""
# Some providers put text directly under 'text'
if "text" in first:
return first.get("text") or ""
# Responses API (aggregated)
if "output_text" in resp:
return resp.get("output_text") or ""
# Some providers: {"content": "..."}
if "content" in resp and isinstance(resp["content"], str):
return resp["content"]
# Objects (OpenAI SDK v1 style or other providers)
# - responses.create(...) returns a Response object with .output_text
txt = getattr(resp, "output_text", None)
if isinstance(txt, str) and txt:
return txt
# - chat.completions.create(...) returns an object with .choices[0].message.content
choices = getattr(resp, "choices", None)
if isinstance(choices, list) and choices:
message = getattr(choices[0], "message", None)
if message is not None:
content = getattr(message, "content", None)
if isinstance(content, str):
return content
# sometimes text is in choices[0].text
text_attr = getattr(choices[0], "text", None)
if isinstance(text_attr, str):
return text_attr
# Fallback: try resp.content
content = getattr(resp, "content", None)
if isinstance(content, str):
return content
except Exception:
pass
return ""
def _llm_chat(llm, messages, **kwargs):
"""
Universal chat invoker. NEVER raises. Tries many backends:
1) llm.chat(messages, **kwargs)
2) llm(messages, **kwargs) if callable and returns text/dict
3) llm.client.chat.completions.create(model=?, messages=?, **kwargs) # OpenAI Chat API
4) llm.client.responses.create(model=?, input=messages, **kwargs) # OpenAI Responses API
5) dict-shaped llm {"client": <openai_client>, "model": "..."} # supported
If nothing works, returns a friendly wrapped text response (so downstream never breaks).
"""
try:
# 1) Direct chat method
if hasattr(llm, "chat") and callable(getattr(llm, "chat")):
resp = llm.chat(messages, **kwargs)
# If a provider already returns the dict shape, just pass it through
if isinstance(resp, dict):
return resp
# Try to normalize
content = _extract_content_from_response(resp)
return _wrap_text_response(content)
# 2) Callable model (e.g., function or partial)
if callable(llm):
resp = llm(messages, **kwargs)
if isinstance(resp, dict):
return resp
return _wrap_text_response(_extract_content_from_response(resp))
# 3/4/5) Client-based shapes
# Accept dict-shaped llm {"client": ..., "model": "..."} as well as objects with .client
client = None
model = None
if isinstance(llm, dict):
client = llm.get("client", None)
model = llm.get("model", None)
else:
client = getattr(llm, "client", None)
model = getattr(llm, "model", None)
if client is not None:
# Try Chat Completions first
try:
chat_api = getattr(client, "chat", None)
if chat_api is not None:
comps = getattr(chat_api, "completions", None)
if comps is not None:
create_fn = getattr(comps, "create", None)
if callable(create_fn):
# Model may be required by provider; best-effort fetch
payload = dict(messages=messages, **kwargs)
if model is not None:
payload["model"] = model
resp = create_fn(**payload)
if isinstance(resp, dict):
return resp
return _wrap_text_response(_extract_content_from_response(resp))
except Exception:
pass
# Try Responses API next
try:
responses_api = getattr(client, "responses", None)
if responses_api is not None:
create_fn = getattr(responses_api, "create", None)
if callable(create_fn):
# Consolidate input for Responses API:
# If messages is already a list of dicts, send as-is; otherwise cast to string.
input_payload = messages
if not isinstance(messages, list):
input_payload = str(messages)
# Model may be required
payload = dict(input=input_payload, **kwargs)
if model is not None:
payload["model"] = model
resp = create_fn(**payload)
if isinstance(resp, dict):
return resp
return _wrap_text_response(_extract_content_from_response(resp))
except Exception:
pass
# If we get here, nothing matched — return a gentle fallback
return _wrap_text_response(
"I couldn't access a chat backend. Please attach an LLM with either "
"`llm.chat(messages=...)`, a callable `llm(messages)`, or an OpenAI-like "
"`llm.client` (chat.completions or responses) and an optional `llm.model`."
)
except Exception as e:
# Never break the app; surface the error in a friendly message
return _wrap_text_response(f"LLM call failed: {e}")
# ---- END ROBUST LLM WRAPPERS -------------------------------------------------
def _ensure_llm_chat(llm):
"""
Return an object with a .chat(messages, **kwargs) method.
If `llm` already supports .chat, return as-is. Else wrap it in a thin adapter
that forwards to _llm_chat which tolerates multiple SDK shapes.
"""
try:
if hasattr(llm, "chat") and callable(getattr(llm, "chat")):
return llm
class _LLMAdapter:
def __init__(self, inner):
self._inner = inner
def chat(self, messages, **kwargs):
return _llm_chat(self._inner, messages, **kwargs)
return _LLMAdapter(llm)
except Exception:
# Very defensive: if anything goes wrong just return a noop adapter so older call-sites don't explode
class _NoopAdapter:
def __init__(self, inner):
self._inner = inner
def chat(self, messages, **kwargs):
# Best-effort: call the universal chat wrapper
return _llm_chat(self._inner, messages, **kwargs)
return _NoopAdapter(llm)
def _llm_chat(llm, messages, **kwargs):
"""
Universal chat invoker that tolerates:
- objects with llm.chat(messages, **kwargs)
- objects with llm.client.chat.completions.create(...)
- new Responses API via llm.client.responses.create(...)
- plain callables returning text
- dicts like {'client': <openai_client>}
Always returns an object shaped like {"choices":[{"message":{"content": "..."}}, ...]}
"""
import logging
logger = logging.getLogger("olist")
try:
# 1) Already has .chat -> use it directly
if hasattr(llm, "chat") and callable(getattr(llm, "chat")):
resp = llm.chat(messages, **kwargs)
# If it's a plain string, normalize
if isinstance(resp, str):
return _wrap_text_response(resp)
# If it's an object with "choices", assume correct
if isinstance(resp, dict) and "choices" in resp:
return resp
# Try to extract text-ish content
content = None
try:
choices = getattr(resp, "choices", None)
if choices and len(choices) > 0:
msg = getattr(choices[0], "message", None)
if isinstance(msg, dict):
content = msg.get("content")
else:
content = getattr(msg, "content", None)
except Exception:
content = None
return _wrap_text_response(content if content is not None else str(resp))
# 2) Try OpenAI client (old-style chat.completions)
client = None
if hasattr(llm, "client"):
client = llm.client
elif isinstance(llm, dict) and "client" in llm:
client = llm["client"]
if client is not None:
chat_obj = getattr(getattr(client, "chat", None), "completions", None)
if chat_obj is not None and hasattr(chat_obj, "create"):
resp = chat_obj.create(messages=messages, **{k: v for k, v in kwargs.items()})
return resp if isinstance(resp, dict) else resp # Downstream already knows how to read this
# 3) New Responses API
responses = getattr(client, "responses", None)
if responses is not None and hasattr(responses, "create"):
# Flatten messages to a single prompt. This is imperfect but robust.
prompt = []
for m in messages:
if isinstance(m, dict):
role = m.get("role", "")
content = m.get("content", "")
prompt.append(f"{role}: {content}")
else:
prompt.append(str(m))
prompt = "\n".join(prompt)
r = responses.create(input=prompt, **{k: v for k, v in kwargs.items() if k != "messages"})
# Best-effort normalization
text = None
# Some SDKs expose output_text attribute or property
try:
text = getattr(r, "output_text", None)
if callable(text):
text = text()
except Exception:
text = None
if not text:
# Try choices[0].message.content
try:
ch = getattr(r, "choices", None) or []
if ch:
msg = getattr(ch[0], "message", None)
if isinstance(msg, dict):
text = msg.get("content")
else:
text = getattr(msg, "content", None)
except Exception:
text = None
if text is None:
text = str(r)
return _wrap_text_response(text)
# 4) Plain callable
if callable(llm):
try:
# Try callable(messages=...)
resp = llm(messages=messages, **kwargs)
except TypeError:
# Fallback to positional
resp = llm(messages)
return resp if isinstance(resp, dict) and "choices" in resp else _wrap_text_response(str(resp))
# 5) Dict shim -> recurse via client if present
if isinstance(llm, dict):
return _llm_chat(type("Shim", (), {"client": llm.get("client", None)})(), messages, **kwargs)
raise AttributeError("LLM object does not support chat()")
except Exception as e:
logger.debug(f"[llm] universal chat wrapper failed: {e}")
# Last-resort: try to stringify the object
try:
return _wrap_text_response(str(getattr(e, "message", None) or str(e)))
except Exception:
return _wrap_text_response("")
# === END PATCH ========================================================================
def _ensure_llm_chat_1(llm):
"""
Accepts any of:
- dict {'client': <OpenAI>, 'model': 'gpt-4.1-mini', 'kwargs': {...}}
- object with .chat(messages) method
- raw OpenAI client object with .chat.completions.create(...)
Returns an object that has .chat(messages)->str
"""
try:
# If it's already a chat-capable wrapper (and not a plain dict), keep it.
if hasattr(llm, "chat") and not isinstance(llm, dict):
return llm
if isinstance(llm, dict):
return _DictChatShim(llm)
# Fallback to adapter for any other client types
return _LLMChatAdapter(llm)
except Exception:
# As a last resort, wrap in adapter so call-sites don't crash
return _LLMChatAdapter(llm)
def _llm_chat_1(llm, messages):
"""
Unified call that returns assistant text.
Supports: dict spec, wrapper with .chat, or raw OpenAI client.
"""
# dict spec
if isinstance(llm, dict):
client = llm.get("client") or llm.get("openai") or llm.get("client_obj")
model = llm.get("model") or llm.get("model_name") or os.getenv("OPENAI_MODEL", "gpt-4.1-mini")
kwargs = llm.get("kwargs") or {}
if client is not None:
# OpenAI (new)
try:
chat_api = getattr(client, "chat", None)
if chat_api is not None and hasattr(chat_api, "completions"):
resp = chat_api.completions.create(model=model, messages=messages, **kwargs)
choice = resp.choices[0]
# New SDK: choice.message.content
content = getattr(choice.message, "content", None)
if content is None and isinstance(choice.message, dict):
content = choice.message.get("content")
return content
except Exception:
pass
# OpenAI (older fallback)
try:
legacy = getattr(client, "chat_completions", None)
if callable(getattr(legacy, "create", None)):
resp = legacy.create(model=model, messages=messages, **kwargs)
choice = resp.choices[0]
msg = getattr(choice, "message", None)
if isinstance(msg, dict):
return msg.get("content")
return getattr(msg, "content", None)
except Exception:
pass
# If no client or calls failed but llm is dict with a nested wrapper
inner = llm.get("wrapper") or llm.get("llm")
if inner is not None and hasattr(inner, "chat"):
return inner.chat(messages)
raise AttributeError("LLM dict spec is missing a usable client.")
# wrapper with .chat
if hasattr(llm, "chat") and callable(getattr(llm, "chat")):
return llm.chat(messages)
# raw OpenAI client (very old pattern)
chat_api = getattr(llm, "chat", None)
if chat_api is not None and hasattr(chat_api, "completions"):
resp = chat_api.completions.create(model=os.getenv("OPENAI_MODEL", "gpt-4.1-mini"), messages=messages)
return resp.choices[0].message.content
raise AttributeError("LLM object is not chat compatible.")
def _ensure_llm_chat_2(llm):
"""
Return an object with a .chat(messages, **kwargs) method.
Accepts:
- objects that already expose .chat
- dicts with a callable at ['chat']
- dicts with {'client', 'model'} resembling OpenAI clients (responses/chat.completions)
- plain callables: assumed signature fn(messages, **kwargs)
"""
try:
# If object already has chat, keep as is
if hasattr(llm, "chat") and callable(getattr(llm, "chat")):
return llm
# If a dict wrapper
if isinstance(llm, dict):
if "chat" in llm and callable(llm["chat"]):
class _LLMProxy:
def __init__(self, d): self._d = d
def chat(self, messages, **kwargs):
return self._d["chat"](messages, **kwargs)
return _LLMProxy(llm)
# Try common OpenAI client shapes
client = llm.get("client")
model = llm.get("model")
if client is not None and model is not None:
# Prefer the new Responses API if present
if hasattr(client, "responses"):
class _LLMProxy:
def __init__(self, c, m): self.client, self.model = c, m
def chat(self, messages, **kwargs):
return self.client.responses.create(model=self.model, input=messages, **kwargs)
return _LLMProxy(client, model)
# Fall back to legacy chat.completions
if hasattr(getattr(client, "chat", None), "completions"):
class _LLMProxy:
def __init__(self, c, m): self.client, self.model = c, m
def chat(self, messages, **kwargs):
return self.client.chat.completions.create(model=self.model, messages=messages, **kwargs)
return _LLMProxy(client, model)
# If it's a bare callable, assume chat-like signature
if callable(llm):
class _LLMProxy:
def __init__(self, fn): self.fn = fn
def chat(self, messages, **kwargs):
return self.fn(messages, **kwargs)
return _LLMProxy(llm)
except Exception as e:
try:
olist_logger.info(f"[mem] _ensure_llm_chat failed: {e}")
except Exception:
pass
return llm
def _llm_chat_1(llm, messages, **kwargs):
"""Best-effort chat invoker that works across llm shapes."""
llm = _ensure_llm_chat(llm)
# Try typical call paths
if hasattr(llm, "chat"):
return llm.chat(messages, **kwargs)
# As a last resort, if user passed a function or dict with 'call'
if callable(llm):
return llm(messages, **kwargs)
if isinstance(llm, dict) and callable(llm.get("call")):
return llm["call"](messages, **kwargs)
raise AttributeError("LLM object does not support chat()")
# === End LLM compatibility helpers ===
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
# Make matplotlib happy in containers (already helped in your logs)
os.environ.setdefault("MPLCONFIGDIR", "/tmp/mplconfig")
try:
os.makedirs(os.environ["MPLCONFIGDIR"], exist_ok=True)
except Exception:
pass
# Simple per-process once registry
__ONCE_FLAGS = set()
def run_once(key: str) -> bool:
"""Return True only the first time a given key is seen in this process."""
if key in __ONCE_FLAGS:
return False
__ONCE_FLAGS.add(key)
return True
# Idempotent, non-propagating logger setup with a marker on our handler
_FMT = logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s")
def _ensure_logger(name: Optional[str], level: int = logging.INFO) -> logging.Logger:
lg = logging.getLogger(name) if name else logging.getLogger()
# If we already have our own handler, don't add another
have_marker = any(getattr(h, "_olist_marker", False) for h in lg.handlers)
if not have_marker:
# Remove any default handlers installed by framework reloaders
for h in list(lg.handlers):
# Keep other handlers if you really want them; usually best to clear
lg.removeHandler(h)
h = logging.StreamHandler(sys.stdout)
h.setFormatter(_FMT)
h._olist_marker = True # marker so we don't re-add
lg.addHandler(h)
lg.setLevel(level)
lg.propagate = False # don't bubble to root and duplicate
return lg
# Prepare our named loggers exactly once per process
if run_once("loggers_configured"):
_ensure_logger(None, logging.INFO) # root
olist_logger = _ensure_logger("olist", logging.DEBUG)
feedback_logger = _ensure_logger("feedback", logging.DEBUG)
# Only announce readiness once per process; guards prevent dupes on re-import
if run_once("olist_ready"):
olist_logger.info("olist logger ready (level=DEBUG)")
if run_once("feedback_ready"):
feedback_logger.info("feedback logger ready (level=DEBUG)")
# --- END FIX v2 ---
# Quiet down very chatty third‑party libs unless explicitly opted in
if _olist_os.environ.get("OLIST_VERBOSE_LIBS") not in ("1", "true", "True"):
for _name in (
"watchdog.observers.inotify_buffer",
"PIL.PngImagePlugin",
"matplotlib.font_manager",
"matplotlib.pyplot",
"httpcore.connection",
"httpcore.http11",
"openai._base_client",
):
_olist_logging.getLogger(_name).setLevel(_olist_logging.WARNING)
# --- END FIX ---
# Heuristic: 'my' + (order|orders|payment|payment type) should go to SQL, not small-talk
_MY_ACCOUNT_PAT = re.compile(r"\bmy\b.*\b(order|orders|payment|payment\s*type)\b|\b(order|orders|payment)\b.*\bmy\b", re.IGNORECASE)
def _is_my_account_query(text: str) -> bool:
try:
return bool(_MY_ACCOUNT_PAT.search(text or ''))
except Exception as e:
return False
# Detect destructive requests (delete/update/drop...) and block them early for safety
_DESTRUCTIVE_PAT = re.compile(
r"""(?xi)
\b
(?:delete|remove|erase|drop|update|modify)\b
[\s\W]*(?:the|my|this|that)?[\s\W]*
(?:order(?:s)?|order\s+data|data)
(?:[\s\W]*(?:for|of|from))?
[\s\W]*(?:my|the|this|that)?[\s\W]*
(?:last|latest)?
[\s\W]*(?:order)?
|
\b(cancel|void)\b[\s\W]*(?:the|my)?[\s\W]*(?:order)(?:s)?
""",
re.IGNORECASE,
)
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
# app.py — Olist Analytics Assistant (LLM→SQL) with Indic (hi/mr) translation
# ---------------------------------------------------------------------
import os
import re
import time
import hashlib
import json
# ==== RAG imports (1212) ====
try:
import chromadb
from chromadb.utils import embedding_functions as cef
except Exception:
chromadb = None
cef = None
import math
import bcrypt
import sqlite3
import pandas as pd
import yaml
import streamlit as st
import eda
from streamlit.runtime.scriptrunner import StopException
# ---- Ensure page config is set before any other Streamlit calls ----
try:
_pcfg = st.session_state.get("_page_config_set", False)
except Exception:
_pcfg = False
if not _pcfg:
try:
st.set_page_config(page_title="SmartAssist BOT", layout="wide", initial_sidebar_state="collapsed")
except Exception:
pass # ignore if already set
try:
st.session_state["_page_config_set"] = True
except Exception:
pass
# -------------------------------------------------------------------
# --- UI sanitizer for React-safe tables ---
def _ui_safe_cell(x):
# Convert dict/list/other objects to JSON or string so Streamlit/React won’t choke
import json
try:
if isinstance(x, (dict, list)):
return json.dumps(x, ensure_ascii=False)
return str(x)
except Exception:
return str(x)
# --- Safe display helper (prevents Streamlit 'function is not iterable' when a DB cursor sneaks in) ---
def _as_dataframe_safely(obj):
"""Return a pandas DataFrame from many possible SQL result shapes.
Handles: pandas.DataFrame, sqlite3.Cursor (attr or callable description), list[tuple].
Never raises; falls back to empty DataFrame.
"""
try:
import pandas as pd
# Already a DataFrame
if isinstance(obj, pd.DataFrame):
return obj
# sqlite3.Cursor (or lookalike): has .fetchall and .description
if hasattr(obj, "fetchall") and hasattr(obj, "description"):
try:
rows = obj.fetchall()
except Exception:
rows = []
desc = getattr(obj, "description", None)
try:
cols = [d[0] for d in (desc() if callable(desc) else (desc or []))]
except Exception:
cols = None
try:
return pd.DataFrame(rows, columns=cols)
except Exception:
return pd.DataFrame(rows)
# Plain Python sequence of rows
if isinstance(obj, (list, tuple)):
try:
import pandas as pd
return pd.DataFrame(list(obj))
except Exception:
pass
except Exception:
pass
# ultimate fallback
try:
import pandas as pd
return pd.DataFrame()
except Exception:
return obj
def _assistant_text_for_transcript(out: dict, default: str = "") -> str:
try:
if out.get("text"):
return out["text"]
plan = out.get("plan") or {}
if plan.get("explanation_display"):
return str(plan.get("explanation_display"))
if plan.get("explanation"):
return str(plan.get("explanation"))
qs = out.get("queries") or []
for q in qs:
if q.get("kind") == "non_sql":
df = q.get("df")
if df is not None:
try:
import pandas as pd
if isinstance(df, pd.DataFrame) and not df.empty and "message" in df.columns:
return str(df.iloc[0]["message"])
except Exception:
pass
return default
except Exception:
return default
def _begin_turn(user_text: str = ""):
try:
st.session_state['_transcript_done'] = False
st.session_state['last_query_text'] = user_text or st.session_state.get('last_query_text') or ''
except Exception:
pass
def _append_transcript_once(user_text: str, out: dict | None = None, explicit_text: str | None = None):
try:
if st.session_state.get('_transcript_done'):
return
asst = explicit_text if explicit_text is not None else _assistant_text_for_transcript(out or {}, default="")
st.session_state.setdefault('transcript', [])
st.session_state['transcript'].append({'user': user_text, 'assistant': asst, 'ts': time.time()})
st.session_state['_transcript_done'] = True
except Exception:
pass
import logging, sys
from datetime import datetime, timedelta
from contextlib import contextmanager
from collections import deque
from typing import Optional, Tuple, List, Dict, Any
import matplotlib.pyplot as plt
from mis import seller_scorecard_df, render_customer_360, render_seller_scorecards, ensure_mis_indexes
# -----------------------------
# Global Settings / Flags
# -----------------------------
DATA_DIR = "data"
SCHEMA_FILE = "olist_schema_registry.yaml"
DB_DIR = "/data" # persistent volume
DB_PATH = os.path.join(DB_DIR, "olist.sqlite")
# --- RAG config ---
RAG_BACKEND = os.getenv("RAG_BACKEND", "CHROMA").upper() # CHROMA | TFIDF
RAG_DIR = os.getenv("RAG_DIR", "/data/rag_store")
RAG_COLLECTION = os.getenv("RAG_COLLECTION", "olist_sql_exemplars")
OPENAI_EMBED_MODEL = os.getenv("OPENAI_EMBED_MODEL", "text-embedding-3-small")
EMBED_DIM = 1536
def _schema_fingerprint(registry: dict) -> str:
try:
parts = []
for t in sorted(registry.keys()):
cols = ",".join(sorted(registry[t]["columns"].keys()))
parts.append(f"{t}({cols})")
return ";".join(parts)
except Exception:
return "unknown"
class RAGStore:
def __init__(self):
self.backend = RAG_BACKEND
self.chroma = None
self.col = None
# Lazy init
def _ensure(self):
if self.backend == "CHROMA":
if chromadb is None:
raise RuntimeError("chromadb not installed")
os.makedirs(RAG_DIR, exist_ok=True)
client = chromadb.PersistentClient(path=RAG_DIR)
# Use OpenAI embeddings if available, else default (may be slow)
ef = None
try:
if cef is not None and os.getenv("OPENAI_API_KEY"):
ef = cef.OpenAIEmbeddingFunction(api_key=os.getenv("OPENAI_API_KEY"), model_name=OPENAI_EMBED_MODEL)
except Exception:
ef = None
self.col = client.get_or_create_collection(RAG_COLLECTION, embedding_function=ef)
self.chroma = client
else:
# Simple in-memory TFIDF fallback
from collections import defaultdict
self.docs = {} # id -> text
self.meta = {} # id -> dict
self._tf = defaultdict(dict)
def upsert_exemplar(self, ex_id: str, text: str, metadata: dict):
self._ensure()
if self.backend == "CHROMA":
self.col.upsert(ids=[ex_id], documents=[text], metadatas=[metadata])
else:
# naive store; search is linear cosine over TfidfVectorizer on demand
self.docs[ex_id] = text
self.meta[ex_id] = metadata
def query(self, text: str, k: int = 3, where: dict | None = None):
self._ensure()
if self.backend == "CHROMA":
where = where or {}
res = self.col.query(query_texts=[text], n_results=k, where=where)
out = []
for i in range(len(res.get("ids",[[]])[0])):
out.append({
"id": res["ids"][0][i],
"text": res["documents"][0][i],
"metadata": res["metadatas"][0][i]
})
return out
else:
# simple cosine via scikit-learn if available; else return best substring matches
try:
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
ids = list(self.docs.keys())
docs = [self.docs[i] for i in ids]
vec = TfidfVectorizer().fit_transform([text] + docs)
sims = cosine_similarity(vec[0:1], vec[1:]).ravel()
order = sims.argsort()[::-1][:k]
return [{"id": ids[i], "text": docs[i], "metadata": self.meta[ids[i]]} for i in order]
except Exception:
# fallback: naive substring rank
items = [(i, self.docs[i]) for i in self.docs]
items.sort(key=lambda p: p[1].count(text[:10]), reverse=True)
return [{"id": i, "text": t, "metadata": self.meta[i]} for i,t in items[:k]]
RAG = RAGStore()
def canonicalize_sql(sql: str) -> str:
return sql.strip()
def exemplar_text(intent: str, question: str, schema_fp: str, plan: str, sql: str) -> str:
plan_short = (plan or "").strip()
if len(plan_short) > 400:
plan_short = plan_short[:400] + "…"
sql_canon = canonicalize_sql(sql or "")
return f"[INTENT]={intent}\nQUESTION={question}\nSCHEMA={schema_fp}\nPLAN={plan_short}\nSQL={sql_canon}"
LLM_CHOICE = os.getenv("LLM_CHOICE", "OPENAI")
REVENUE_USER_MODE = os.getenv("REVENUE_USER_MODE", "WARN")
SELLER_REVENUE_USER_MODE = os.getenv("SELLER_REVENUE_USER_MODE", "BLOCK")
PRODUCT_REVENUE_USER_MODE = os.getenv("PRODUCT_REVENUE_USER_MODE", "BLOCK")
PAYMENTS_USER_MODE = os.getenv("PAYMENTS_USER_MODE", "SCOPE").upper()
# Feedback enforcement mode: OPTIONAL | SOFT_REQUIRED | HARD_REQUIRED
FEEDBACK_MODE = os.getenv("FEEDBACK_MODE", "SOFT_REQUIRED").upper()
DB_TIMEOUT = int(os.getenv("DB_TIMEOUT", "30"))
BUSY_TIMEOUT_MS = int(os.getenv("BUSY_TIMEOUT_MS", "5000"))
SQL_TIMEOUT = float(os.getenv("SQL_TIMEOUT", "30")) # default 30s
NON_SQL_ROUTER = os.getenv("NON_SQL_ROUTER", "ON").upper() == "ON"
# --- Tracing & timeouts ---
TRACE_LOGGING = os.getenv("TRACE_LOGGING", "1") == "1"
SQL_HARD_TIMEOUT_SEC = float(os.getenv("SQL_HARD_TIMEOUT_SEC", "15"))
SQL_VM_BUDGET = int(os.getenv("SQL_VM_BUDGET", "4000000"))
SQL_PROGRESS_NSTEPS = int(os.getenv("SQL_PROGRESS_NSTEPS", "1000"))
# --- Rate limiting (role-aware) ---
RATE_LIMIT_WINDOW_SEC = int(os.getenv("RATE_LIMIT_WINDOW_SEC", "60"))
RATE_LIMIT_MAX_REQUESTS_USER = int(os.getenv("RATE_LIMIT_MAX_REQUESTS_USER", "8"))
RATE_LIMIT_MAX_REQUESTS_ADMIN = int(os.getenv("RATE_LIMIT_MAX_REQUESTS_ADMIN", "30"))
# --- Visibility toggle for detailed sections ---
DISPLAY_DETAIL = os.getenv("DISPLAY_DETAIL", "YES").strip().upper()
SHOW_DETAILS = (DISPLAY_DETAIL != "NO") # True unless explicitly set to NO
# UI brand flag
USE_BRANDING = True
DEBUG_UI = os.getenv("DEBUG_UI", "0") == "1"
LOG_LEVEL = os.getenv("LOG_LEVEL", "INFO").upper()
def setup_logging():
# Root logger -> stdout once
root = logging.getLogger()
if not root.handlers:
h = logging.StreamHandler(sys.stdout)
h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
root.addHandler(h)
root.setLevel(getattr(logging, LOG_LEVEL, logging.INFO))
# Quiet very chatty libs
logging.getLogger("httpx").setLevel(logging.WARNING)
logging.getLogger("urllib3").setLevel(logging.WARNING)
return root
root_logger = setup_logging()
def _ensure_logger(name: str, level=logging.INFO):
lg = logging.getLogger(name)
lg.setLevel(level)
# Attach its own handler to guarantee visibility (and avoid relying on propagation)
if not lg.handlers:
h = logging.StreamHandler(sys.stdout)
h.setFormatter(logging.Formatter("%(asctime)s | %(levelname)s | %(name)s | %(message)s"))
lg.addHandler(h)
# When a named logger has its own handler, set propagate False to avoid duplicates
lg.propagate = False
return lg
logger = _ensure_logger("olist", getattr(logging, LOG_LEVEL, logging.INFO))
fb_logger = _ensure_logger("feedback", getattr(logging, LOG_LEVEL, logging.INFO))
logger.info("olist logger ready (level=%s)", LOG_LEVEL)
fb_logger.info("feedback logger ready (level=%s)", LOG_LEVEL)
def dbg(msg: str):
if DEBUG_UI:
st.sidebar.write(f"🧪 {msg}")
# ---- Hugging Face cache fix ----
HF_CACHE_BASE = os.getenv("HF_CACHE_BASE", "/data/hf-cache")
def _init_hf_cache():
import pathlib, time, os
base = pathlib.Path(HF_CACHE_BASE)
hub = base / "hub"
trsf = base / "transformers"
for d in (base, hub, trsf):
d.mkdir(parents=True, exist_ok=True)
# ensure transformers uses /data (not /.cache)
os.environ.setdefault("HF_HOME", str(base))
os.environ.setdefault("HF_HUB_CACHE", str(hub))
os.environ.setdefault("TRANSFORMERS_CACHE", str(trsf))
os.environ.setdefault("HF_HUB_DISABLE_SYMLINKS_WARNING", "1")
# Optional: clear stale lock files (>20 min old)
now = time.time()
for root, _, files in os.walk(str(base)):
for f in files:
if f.endswith(".lock"):
p = os.path.join(root, f)
try:
if now - os.path.getmtime(p) > 1200: # 20 min
os.remove(p)
except Exception:
pass
_init_hf_cache()
# -----------------------------
# Scoped UI helpers (safe & minimal)
# -----------------------------
def _inject_brand_css():
"""Loads assets/brand.css if present; otherwise falls back to embedded styles."""
import pathlib
css_path = pathlib.Path("assets/brand.css")
if css_path.exists():
st.markdown(f"<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">OA</div>
<div>
<div class="oa-title">SmartAssist BOT</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_chat_history_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS chat_history (
chat_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
role TEXT NOT NULL,
customer_id TEXT,
user_input TEXT NOT NULL,
llm_plan JSON,
secured_sql JSON,
sentiment TEXT,
intent TEXT,
llm_response_time REAL,
feedback INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
conn.commit()
def ensure_kv_store_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS kv_store (
k TEXT PRIMARY KEY,
v TEXT
);
""")
conn.commit()
def ensure_login_audit_schema(conn: sqlite3.Connection):
conn.execute("""
CREATE TABLE IF NOT EXISTS login_audit (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT,
email TEXT,
success INTEGER NOT NULL,
reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
""")
conn.execute("CREATE INDEX IF NOT EXISTS idx_login_audit_created_at ON login_audit(created_at);")
conn.execute("CREATE INDEX IF NOT EXISTS idx_login_audit_user ON login_audit(user_id);")
conn.commit()
def ensure_performance_indexes(conn: sqlite3.Connection) -> list[str]:
stmts = [
"CREATE INDEX IF NOT EXISTS idx_orders_order_id ON orders(order_id);",
"CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);",
"CREATE INDEX IF NOT EXISTS idx_orders_customer_order ON orders(customer_id, order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_items_order ON order_items(order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_payments_order ON order_payments(order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_reviews_order ON order_reviews(order_id);",
"CREATE INDEX IF NOT EXISTS idx_order_items_product ON order_items(product_id);",
"CREATE INDEX IF NOT EXISTS idx_products_product ON products(product_id);",
"CREATE INDEX IF NOT EXISTS idx_sellers_seller ON sellers(seller_id);",
"CREATE INDEX IF NOT EXISTS idx_login_audit_created ON login_audit(created_at);"
]
executed = []
for s in stmts:
conn.execute(s); executed.append(s)
conn.execute("ANALYZE;")
conn.commit()
return executed
def _col_exists(conn, table, col) -> bool:
cur = conn.execute(f"PRAGMA table_info({table})")
return any(r[1] == col for r in cur.fetchall())
def _chat_history_pk_name(conn) -> str | None:
"""Return the primary key column name for chat_history ('id' or 'chat_id')."""
cols = [r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()]
if "id" in cols:
return "id"
if "chat_id" in cols:
return "chat_id"
return None
def ensure_feedback_cols(conn):
# make sure chat_history exists
conn.execute("""
CREATE TABLE IF NOT EXISTS chat_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT,
role TEXT,
conversation_id TEXT,
input_text TEXT,
response_text TEXT,
sentiment TEXT,
intent TEXT,
secured_sql TEXT,
llm_plan TEXT,
llm_time REAL,
sql_time REAL,
created_at TEXT
)""")
def has(col: str) -> bool:
return any(r[1] == col for r in conn.execute("PRAGMA table_info(chat_history)").fetchall())
changed = False
if not has("feedback_value"):
conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_value INTEGER"); changed = True
if not has("feedback_text"):
conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_text TEXT"); changed = True
if not has("feedback_at"):
conn.execute("ALTER TABLE chat_history ADD COLUMN feedback_at TEXT"); changed = True
if changed:
conn.commit()
def has_feedback_for_conversation(get_conn, *, user_id: int, conversation_id: str) -> bool:
with get_conn() as conn:
cur = conn.execute(
"""
SELECT 1
FROM chat_history
WHERE user_id = ? AND conversation_id = ? AND feedback_value IS NOT NULL
LIMIT 1
""",
(user_id, conversation_id),
)
row = cur.fetchone()
fb_logger.debug("has_feedback_for_conversation user=%s cid=%s -> %s", user_id, conversation_id, bool(row))
return bool(row)
# at top
SCHEMA_VERSION = 1 # bump when you add migrations
def _set_pragmas(conn: sqlite3.Connection) -> None:
# All PRAGMAs must be literals (no ? bindings)
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute(f"PRAGMA busy_timeout={int(SQL_TIMEOUT*1000)};")
conn.execute("PRAGMA temp_store=MEMORY;")
def initialize_db(quiet: bool = True) -> dict:
os.makedirs(DB_DIR, exist_ok=True)
created = not os.path.exists(DB_PATH)
actions = []
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
_set_pragmas(conn)
row = conn.execute("PRAGMA user_version;").fetchone()
prior_version = int(row[0]) if row and row[0] is not None else 0
ensure_chat_history_schema(conn)
ensure_login_audit_schema(conn)
ensure_performance_indexes(conn)
ensure_feedback_cols(conn)
if prior_version != SCHEMA_VERSION:
conn.execute(f"PRAGMA user_version={int(SCHEMA_VERSION)};")
actions.append(f"set_user_version:{SCHEMA_VERSION}")
conn.commit()
status = {
"created": created,
"prior_version": prior_version,
"new_version": SCHEMA_VERSION,
"actions": actions,
"db_path": DB_PATH,
}
if not quiet:
st.success(f"DB initialized (user_version {prior_version} → {SCHEMA_VERSION})")
return status
@st.cache_resource(show_spinner = False)
def bootstrap_db_once() -> dict:
return initialize_db(quiet = True)
# Call it after functions are defined:
_init_status = bootstrap_db_once()
# Log only once per browser session (not on every rerun)
if "init_logged" not in st.session_state:
logger.info("[init] bootstrap_db_once => %s", _init_status)
st.session_state.init_logged = True
def initialize_db_old (quiet: bool = True) -> bool:
"""
Create/upgrade DB if needed. Returns True if rebuild/migration happened.
Never writes to Streamlit UI; use logging instead.
"""
os.makedirs(DB_DIR, exist_ok=True)
rebuilt = False
# Fast existence & schema check
need_create = not os.path.exists(DB_PATH)
try:
with sqlite3.connect(DB_PATH) as c:
c.execute("PRAGMA journal_mode=WAL;")
c.execute("PRAGMA busy_timeout = 5000;")
# Store schema version in user_version
cur = c.execute("PRAGMA user_version;").fetchone()
current_ver = int(cur[0]) if cur and cur[0] is not None else 0
except Exception as e:
need_create = True
current_ver = 0
if need_create:
# your current CSV → tables loader goes here...
# ensure_chat_history_schema(c), ensure_login_audit_schema(c), etc.
with sqlite3.connect(DB_PATH) as c:
c.execute("PRAGMA user_version = ?", (SCHEMA_VERSION,))
rebuilt = True
logger.info("[init] fresh database created")
else:
# migrations if schema version is older
if current_ver < SCHEMA_VERSION:
with sqlite3.connect(DB_PATH) as c:
# run your ALTERs / ensures here
ensure_chat_history_schema(c)
ensure_login_audit_schema(c)
ensure_performance_indexes(c)
ensure_feedback_cols(c)
c.execute("PRAGMA user_version = ?", (SCHEMA_VERSION,))
c.commit()
rebuilt = True
logger.info("[init] migrated database from v%s to v%s", current_ver, SCHEMA_VERSION)
else:
# still ensure additive bits without noise
with sqlite3.connect(DB_PATH) as c:
ensure_chat_history_schema(c)
ensure_login_audit_schema(c)
ensure_performance_indexes(c)
ensure_feedback_cols(c)
c.commit()
return rebuilt
# --- Admin data reload helpers ---------------------------------------------
LOAD_ORDER = [
# parents → children (recommended load order)
"geolocation",
"customers",
"sellers",
"products",
"product_category_name_translation",
"orders",
"order_items",
"order_payments",
"order_reviews",
]
def get_table_csv_map() -> dict:
return {
"orders": f"{DATA_DIR}/olist_orders_dataset.csv",
"order_items": f"{DATA_DIR}/olist_order_items_dataset.csv",
"order_payments": f"{DATA_DIR}/olist_order_payments_dataset.csv",
"order_reviews": f"{DATA_DIR}/olist_order_reviews_dataset.csv",
"customers": f"{DATA_DIR}/olist_customers_dataset.csv",
"products": f"{DATA_DIR}/olist_products_dataset.csv",
"sellers": f"{DATA_DIR}/olist_sellers_dataset.csv",
"geolocation": f"{DATA_DIR}/olist_geolocation_dataset.csv",
"product_category_name_translation": f"{DATA_DIR}/product_category_name_translation.csv",
}
def _sorted_by_load_order(tables: list[str]) -> list[str]:
order_index = {t: i for i, t in enumerate(LOAD_ORDER)}
return sorted(tables, key=lambda t: order_index.get(t, 999))
def get_row_counts(conn: sqlite3.Connection, tables: list[str]) -> dict[str, int]:
out = {}
for t in tables:
try:
out[t] = conn.execute(f"SELECT COUNT(*) FROM {t}").fetchone()[0]
except Exception as e:
out[t] = None
if isinstance(e, StopException):
raise # don't swallow st.stop()
logger.exception("Unhandled exception in <context>: %s", e)
return out
def repopulate_tables_from_csv(tables: list[str], logger=None) -> tuple[bool, str, dict]:
"""
DELETE FROM selected tables, then reload from CSVs.
Returns (ok, message, post_counts).
"""
if not tables:
return False, "No tables selected.", {}
csv_map = get_table_csv_map()
missing = [t for t in tables if t not in csv_map]
if missing:
return False, f"No CSV mapping for: {', '.join(missing)}", {}
tables = _sorted_by_load_order(tables)
# Use a generous timeout in case of big loads
timeout = 60.0 if 'SQL_TIMEOUT' not in globals() else SQL_TIMEOUT
try:
with sqlite3.connect(DB_PATH, timeout=timeout) as conn:
conn.execute("PRAGMA journal_mode=WAL;")
conn.execute("PRAGMA synchronous=NORMAL;")
conn.execute("PRAGMA foreign_keys=OFF;")
try:
for t in tables:
path = csv_map[t]
if not os.path.exists(path):
raise FileNotFoundError(f"CSV not found: {path}")
if logger: logger.info("[reload] %s: deleting old rows", t)
conn.execute(f"DELETE FROM {t};")
conn.commit()
if logger: logger.info("[reload] %s: loading from %s", t, path)
# Chunked load to keep memory reasonable
for chunk in pd.read_csv(path, chunksize=50000):
chunk.to_sql(t, conn, if_exists="append", index=False)
conn.commit()
finally:
conn.execute("PRAGMA foreign_keys=ON;")
post = get_row_counts(conn, tables)
return True, "Repopulation completed.", post
except Exception as e:
if logger: logger.exception("[reload] failed")
return False, f"Repopulation failed: {e}", {}
#--- Admin data reload helpers ---------------------------------------------
def load_schema_registry():
if not os.path.exists(SCHEMA_FILE):
raise FileNotFoundError(f"Missing schema file: {SCHEMA_FILE}")
with open(SCHEMA_FILE, "r") as f:
return yaml.safe_load(f)
def render_feedback_controls(chat_id: int):
if not chat_id:
return
existing = get_chat_feedback(chat_id)
cols = st.columns(3)
with cols[0]:
st.caption("Was this response helpful?")
with cols[1]:
btn_up = st.button("👍 Yes", key=f"fb_up_{chat_id}", disabled=(existing is not None))
with cols[2]:
btn_dn = st.button("👎 No", key=f"fb_dn_{chat_id}", disabled=(existing is not None))
if existing is not None:
st.info("Feedback recorded: " + ("👍 Helpful" if existing == 1 else "👎 Not helpful"))
return
if btn_up:
try:
update_chat_feedback(chat_id, 1)
st.success("Thanks for the feedback! Recorded as 👍 Helpful.")
except Exception as e:
st.error("Could not save feedback."); st.exception(e)
elif btn_dn:
try:
update_chat_feedback(chat_id, 0)
st.success(_localize_msg("Thanks for the feedback! Recorded as 👎 Not helpful.",src_lang))
except Exception as e:
st.error(_localize_msg("Could not save feedback.",src_lang)); st.exception(e)
def insert_chat_history(*args, **kwargs):
"""
Backward/forward compatible insert for chat_history.
Supports **old** signature:
insert_chat_history(conn, user_id, role, text, intent, sentiment, chat_id=None, turn=None, lang=None)
and **new** keyword signature (no conn parameter; we open it):
insert_chat_history(
user_id=..., role=..., customer_id=..., user_input=...,
sentiment=..., intent=..., llm_plan=..., secured_sql=...,
llm_time=..., sql_time=..., chat_id=..., turn=..., lang=...
)
Returns the inserted row id (chat_id / id) if available; otherwise True.
"""
# --- Normalize inputs into a dict of fields we might write ---
conn = None
use_old = False
if args and hasattr(args[0], "execute"):
# old-style: first arg is a sqlite3.Connection
use_old = True
conn = args[0]
try:
user_id, role, text, intent, sentiment = args[1], args[2], args[3], args[4], args[5]
except Exception:
# If args are incomplete, fall back to kwargs (best effort)
user_id = kwargs.get("user_id")
role = kwargs.get("role")
text = kwargs.get("text") or kwargs.get("user_input")
intent = kwargs.get("intent")
sentiment = kwargs.get("sentiment")
chat_id = kwargs.get("chat_id", None)
turn = kwargs.get("turn", None)
lang = kwargs.get("lang", None)
customer_id = kwargs.get("customer_id")
user_input = text
llm_plan = kwargs.get("llm_plan")
secured_sql = kwargs.get("secured_sql")
llm_time = kwargs.get("llm_time")
sql_time = kwargs.get("sql_time")
else:
# new-style: only kwargs
user_id = kwargs.get("user_id")
role = kwargs.get("role")
customer_id = kwargs.get("customer_id")
user_input = kwargs.get("user_input") or kwargs.get("text")
sentiment = kwargs.get("sentiment")
intent = kwargs.get("intent")
llm_plan = kwargs.get("llm_plan")
secured_sql = kwargs.get("secured_sql")
llm_time = kwargs.get("llm_time")
sql_time = kwargs.get("sql_time")
chat_id = kwargs.get("chat_id")
turn = kwargs.get("turn")
lang = kwargs.get("lang")
# Fallbacks for safety
if role is None: role = "user"
if intent is None: intent = ""
if sentiment is None: sentiment = ""
# Open connection if not provided
if conn is None:
try:
with get_conn() as _conn:
return _insert_chat_history_impl(
_conn, user_id, role, customer_id, user_input,
sentiment, intent, llm_plan, secured_sql,
llm_time, sql_time, chat_id, turn, lang
)
except Exception as e:
logger.info("[trace=meta] insert_chat_history (open) failed: %s", e)
return False
else:
try:
return _insert_chat_history_impl(
conn, user_id, role, customer_id, user_input,
sentiment, intent, llm_plan, secured_sql,
llm_time, sql_time, chat_id, turn, lang
)
except Exception as e:
logger.info("[trace=meta] insert_chat_history (impl) failed: %s", e)
return False
def _insert_chat_history_impl(conn, user_id, role, customer_id, user_input,
sentiment, intent, llm_plan, secured_sql,
llm_time, sql_time, chat_id, turn, lang):
cols = _detect_columns(conn, "chat_history")
if not cols:
# Create minimal schema if truly missing (defensive)
conn.execute("""
CREATE TABLE IF NOT EXISTS chat_history(
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT, role TEXT, input_text TEXT,
sentiment TEXT, intent TEXT, created_at TEXT
)
""")
conn.commit()
cols = _detect_columns(conn, "chat_history")
# Figure out actual column names for common fields
pk_name = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None)
user_text_col = _first_existing(cols, "user_input", "input_text", "question", "prompt", "user_text", "input")
role_col = _first_existing(cols, "role")
user_id_col = _first_existing(cols, "user_id")
intent_col = _first_existing(cols, "intent")
sent_col = _first_existing(cols, "sentiment")
created_col = _first_existing(cols, "created_at", "ts")
conv_col = _first_existing(cols, "conversation_id") # we'll map customer_id here if available
# Optional advanced columns
plan_col = "llm_plan" if "llm_plan" in cols else None
secured_col = "secured_sql" if "secured_sql" in cols else None
llm_time_col = "llm_time" if "llm_time" in cols else None
sql_time_col = "sql_time" if "sql_time" in cols else None
turn_col = "turn" if "turn" in cols else None
lang_col = "lang" if "lang" in cols else None
chat_id_col = "chat_id" if "chat_id" in cols else None
insert_cols, insert_vals = [], []
def add(colname, value):
if colname and (colname in cols) and (value is not None):
insert_cols.append(colname)
insert_vals.append(value)
add(user_id_col, str(user_id) if user_id is not None else None)
add(role_col, role)
add(user_text_col, user_input)
add(sent_col, sentiment)
add(intent_col, intent)
if conv_col and customer_id:
add(conv_col, str(customer_id))
if plan_col and llm_plan is not None:
try:
add(plan_col, json.dumps(llm_plan, ensure_ascii=False))
except Exception:
add(plan_col, str(llm_plan))
if secured_col and secured_sql is not None:
try:
add(secured_col, json.dumps(secured_sql, ensure_ascii=False))
except Exception:
add(secured_col, str(secured_sql))
if llm_time_col and llm_time is not None:
add(llm_time_col, float(llm_time))
if sql_time_col and sql_time is not None:
add(sql_time_col, float(sql_time))
if turn_col and turn is not None:
add(turn_col, int(turn))
if lang_col and lang is not None:
add(lang_col, str(lang))
# If your schema has a chat_id column and the caller provided one, include it
if chat_id_col and chat_id is not None:
add(chat_id_col, chat_id)
# Always include created_at/ts if present
if created_col:
add(created_col, datetime.utcnow().isoformat(timespec="seconds"))
if not insert_cols:
# Nothing to insert? Do nothing.
return False
placeholders = ",".join("?" for _ in insert_cols)
sql = f"INSERT INTO chat_history ({','.join(insert_cols)}) VALUES ({placeholders})"
cur = conn.cursor()
cur.execute(sql, insert_vals)
conn.commit()
# Return the row id we just created (works for id or chat_id autoincrement)
try:
return cur.lastrowid
except Exception:
return True
def _insert_chat_turn_output(conn, chat_id, turn, role, assistant_text, raw_json=None):
try:
cur = conn.cursor()
cols = ["chat_id", "turn", "role", "assistant_text"]
vals = [chat_id, turn, role, assistant_text]
if raw_json is not None:
cols.append("raw_json")
vals.append(raw_json)
q = f"INSERT INTO chat_turn_outputs ({','.join(cols)}) VALUES ({','.join(['?']*len(cols))})"
cur.execute(q, vals)
conn.commit()
return True
except Exception as e:
logger.debug("[mem] insert chat_turn_outputs failed: %s", e)
return False
@st.cache_data(ttl=30, show_spinner=False)
def get_recent_chats_for_user(user_id: str, limit: int = 100) -> pd.DataFrame:
with get_conn() as conn:
cols = {r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()}
# Detect PK and key columns
pk = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None)
created = "created_at" if "created_at" in cols else ( "ts" if "ts" in cols else None )
inp_col = "user_input" if "user_input" in cols else ( "input_text" if "input_text" in cols else None )
secured = "secured_sql" if "secured_sql" in cols else None
llm_plan = "llm_plan" if "llm_plan" in cols else None
feedback = "feedback_value" if "feedback_value" in cols else ( "feedback" if "feedback" in cols else None )
if not pk:
raise RuntimeError("chat_history has no id/chat_id column")
# Build SELECT list, substituting NULL for missing columns
select_parts = [f"{pk} AS chat_id"]
select_parts += [f"{created} AS created_at"] if created else ["NULL AS created_at"]
select_parts += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"]
select_parts += [f"{secured} AS secured_sql"] if secured else ["NULL AS secured_sql"]
select_parts += [f"{llm_plan} AS llm_plan"] if llm_plan else ["NULL AS llm_plan"]
select_parts += [f"{feedback} AS feedback"] if feedback else ["NULL AS feedback"]
order_clause = f" ORDER BY datetime({created}) DESC" if created else ""
sql = f"""
SELECT {", ".join(select_parts)}
FROM chat_history
WHERE user_id = ?
{order_clause}
LIMIT ?
"""
return pd.read_sql_query(sql, conn, params=(user_id, limit))
@st.cache_data(ttl=30, show_spinner=False)
def get_unrated_chats_for_user(user_id: str, limit: int = 100) -> pd.DataFrame:
with get_conn() as conn:
cols = {r[1] for r in conn.execute("PRAGMA table_info(chat_history)").fetchall()}
pk = "id" if "id" in cols else ("chat_id" if "chat_id" in cols else None)
created = "created_at" if "created_at" in cols else None
inp_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else None)
secured = "secured_sql" if "secured_sql" in cols else None
llm_plan = "llm_plan" if "llm_plan" in cols else None
fb_col = "feedback_value" if "feedback_value" in cols else ("feedback" if "feedback" in cols else None)
if not pk:
raise RuntimeError("chat_history has no id/chat_id column")
select_parts = [f"{pk} AS chat_id"]
select_parts += [f"{created} AS created_at"] if created else ["NULL AS created_at"]
select_parts += [f"{inp_col} AS user_input"] if inp_col else ["NULL AS user_input"]
select_parts += [f"{secured} AS secured_sql"] if secured else ["NULL AS secured_sql"]
select_parts += [f"{llm_plan} AS llm_plan"] if llm_plan else ["NULL AS llm_plan"]
select_parts += [f"{fb_col} AS feedback"] if fb_col else ["NULL AS feedback"]
where_fb = f" AND {fb_col} IS NULL" if fb_col else ""
order_clause = f" ORDER BY datetime({created}) DESC" if created else ""
sql = f"""
SELECT {", ".join(select_parts)}
FROM chat_history
WHERE user_id = ? {where_fb}
{order_clause}
LIMIT ?
"""
return pd.read_sql_query(sql, conn, params=(user_id, limit))
@st.cache_data(ttl=30)
def get_recent_chats_admin(limit: int = 200, days: int | None = None, user_id: str | None = None) -> pd.DataFrame:
"""
Admin view: optionally filter by user_id; otherwise show all.
"""
sql = """
SELECT chat_id, user_id, role, customer_id, created_at,
intent, sentiment, feedback, user_input, llm_response_time
FROM chat_history
WHERE 1=1
"""
params = []
if user_id:
sql += " AND user_id = ?"
params.append(user_id)
if days is not None:
since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S")
sql += " AND created_at >= ?"
params.append(since)
sql += " ORDER BY datetime(created_at) DESC LIMIT ?"
params.append(int(limit))
with get_conn() as conn:
df = pd.read_sql_query(sql, conn, params=params)
return df
import re
def detect_non_sql_intent(q: str):
"""
Heuristic non‑SQL intent detector that is bilingual:
- Checks patterns against the original text.
- If the language is not English, also translates to English via _tx_to_en and checks again.
Returns one of: "greeting", "thanks", "smalltalk", "complaint", or None.
"""
try:
src = _detect_lang(q)
except Exception:
src = "en"
import streamlit as st
st.session_state["src_lang"] = src_lang # current turn
st.session_state["last_detected_lang"] = src_lang # optional alias
text_variants = []
q0 = (q or "").strip()
qlc = q0.lower()
text_variants.append(qlc)
# Translate non‑English to English for pattern matching
if src != "en":
try:
q_en = _tx_to_en(q0, src)
if q_en and isinstance(q_en, str):
text_variants.append(q_en.strip().lower())
except Exception as e:
logger.debug("detect_non_sql_intent: _tx_to_en failed: %s", e)
# Patterns
pat_greet = re.compile(r"\b(hi|hello|hey|greetings|namaste|namaskar|नमस्ते|नमस्कार|सलाम|\u0939\u093e\u0932\s*चाल|हालचाल|हाल चाल|आप कैसे हैं|क्या हाल है)\b", re.I)
pat_thanks = re.compile(r"\b(thanks|thank you|shukriya|धन्यवाद|शुक्रिया)\b", re.I)
pat_smalltalk = re.compile(r"\b(how\s+are\s+you|what\'?s\s+up|how\s+is\s+it\s+going|कैसे\s*हैं|क्या\s*हाल\s*है|हालचाल|हाल\s*चाल)\b", re.I)
pat_complaint = re.compile(r"\b(complain|not working|issue|problem|error|फिर\s*से\s*नहीं|काम\s*नहीं|समस्या|त्रुटि)\b", re.I)
for tv in text_variants:
if not tv:
continue
if pat_greet.search(tv):
logger.info("Matching label is : greeting")
return "greeting"
if pat_thanks.search(tv):
logger.info("Matching label is : thanks")
return "thanks"
if pat_smalltalk.search(tv):
logger.info("Matching label is : smalltalk")
return "smalltalk"
if pat_complaint.search(tv):
logger.info("Matching label is : complaint")
return "complaint"
return None
def classify_destructive_intent_with_llm(llm, query: str) -> bool:
# Normalize if the planner context was prefixed (case-insensitive)
q = (query or "")
m = re.search(r'question:\s*(.*)', q, flags=re.I | re.S) # grab everything after QUESTION:
if m:
q = m.group(1).strip()
sys_msg = (
"You are a strict binary classifier.\n"
"Return exactly one token: Destructive or Non-Destructive."
)
user_msg = (
"Classify the intent of this user query as either Destructive or Non-Destructive.\n"
f'Query: "{q}"\n'
"Answer with only the single word.\n"
"Examples of Destructive intent are:\n"
"- delete data\n"
"- delete my data\n"
"- erase data\n"
"- erase my data\n"
"- erase data from table\n"
"- drop data\n"
"- drop table\n"
"- modify data\n"
"- update data\n"
"- update table\n"
"- cancel my order\n"
"- void the order"
)
try:
llm = _ensure_llm_chat(llm) # injected shim
out = _llm_chat(llm, [
{"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):
llm = _ensure_llm_chat(llm)
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."""
llm = _ensure_llm_chat(llm) # injected shim
out = _llm_chat(llm, [
{"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():
"""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):
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):
llm = _ensure_llm_chat(llm)
schema_json = json.dumps({t:list(schema_registry[t]["columns"].keys()) for t in schema_registry})
# === RAG retrieval (1212) ===
try:
# simple heuristic to guess intent quickly using existing classifier if present
intent_guess = None
try:
intent_guess = detect_non_sql_intent(user_text) or None
except Exception:
intent_guess = None
schema_fp = _schema_fingerprint(schema_registry)
ex_query = "\n".join([
"[INTENT]={}".format(intent_guess or ""),
"QUESTION={}".format(user_text),
"SCHEMA={}".format(schema_fp),
])
exemplars = RAG.query(ex_query, k=3, where={"schema_fp": schema_fp} if RAG_BACKEND=="CHROMA" else None)
ex_blocks = []
for ex in exemplars:
ex_blocks.append(ex.get("text",""))
sep = "\n\n--- EXEMPLAR ---\n" # safe, single-line
ex_text = sep.join(ex_blocks) if ex_blocks else ""
except Exception:
ex_text = ""
sys_prompt = """You are a SQL planner for SQLite Olist dataset.
Guidelines:
- Use only valid tables/columns from SCHEMA.
- Prefer a safe LIMIT 50-100 unless user explicitly asks for all rows.
- Align date ranges with the user's wording.
- Outputs must be pure JSON with this shape:
{"queries": ["SQL1","SQL2?"], "explanation":"..."}
If EXEMPLARS are provided, learn patterns but generate fresh SQL that matches the new QUESTION.
"""
user_block = f"""USER QUESTION:
{user_text}
SCHEMA:
{schema_json}"""
if ex_text:
user_block += f"""
EXEMPLARS:
{ex_text}"""
llm = _ensure_llm_chat(llm) # injected shim
out = _llm_chat(llm, [
{"role":"system","content":sys_prompt},
{"role":"user","content":user_block}
])
try:
return json.loads(out)
except Exception:
return {"queries":[_extract_content_from_response(out).strip() if isinstance(_extract_content_from_response(out), str) else str(_extract_content_from_response(out))],"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"}
def _norm_guard_text(t: str) -> str:
"""Lowercase, strip punctuation to make intent regex robust."""
if not t:
return ""
t = t.lower()
t = re.sub(r"[^\w\s]", " ", t) # remove punctuation like leading dots
t = re.sub(r"\s+", " ", t).strip()
return t
# English patterns for “dump/list across all tables”
_ALL_TABLES_EN_PATTERNS = [
r"(?:list|show|dump|export|fetch|display|print|get|provide)\s+(?:all\s+)?(?:data|records|rows|everything)\s+(?:from|in|across)\s+(?:all|every)\s+tables?",
r"(?:list|show|dump|export|fetch|display|print|get|provide)\s+(?:data|records|rows|everything)\s+(?:from|in|across)\s+(?:all|every)\s+tables?",
r"(?:all|every)\s+tables?\s+(?:data|records|rows|entries)",
r"(?:across|from|in)\s+(?:all|every)\s+tables?",
r"(?:for|from)\s+each\s+table(?:\s+counts?| data| records)?", # “count from each table”, etc.
r"list\s+(?:all\s+)?tables?\s+data", # “list all tables data”
r"dump\s+(?:the\s+)?database", # generic dump
]
def is_forbidden_all_tables_post_tx(en_text: str) -> bool:
"""Run on the translated-to-English query BEFORE planning."""
t = _norm_guard_text(en_text)
return any(re.search(p, t) for p in _ALL_TABLES_EN_PATTERNS)
def _sample(s: str, n: int = 120) -> str:
if s is None: return ""
s = str(s)
return s if len(s) <= n else (s[:n] + "…")
def _strip_existing_tag(text: str) -> str:
m = re.match(r"^\s*>>([A-Za-z_]+)<<\s*", text or "")
if m and m.group(1) in _LANG_TAGS:
return (text or "")[m.end():]
return text or ""
def _ensure_tag_prefixed(text: str, tag: str) -> str:
core = _strip_existing_tag(text or "")
return f">>{tag}<< " + core.lstrip()
# Safe EN→target translation wrapper for UI strings (errors, notices, etc.)
def _tx_safe(text: str, tgt_lang: str | None) -> str:
"""
Best-effort translate an English message to `tgt_lang`.
Falls back to original English if translation is disabled or fails.
"""
if not text:
return ""
tgt = (tgt_lang or "").strip().lower()
# Pass-through for English/auto/blank
if tgt in ("", "en", "auto"):
return text
try:
# Respect feature flag if present (defaults to enabled)
if not globals().get("TRANSLATION_ENABLED", True):
return text
# Use your existing translator (should accept short codes like 'hi','mr')
out = _tx_from_en(text, tgt)
return text if out is None else str(out)
except Exception as e:
try:
logger.warning("[tx-safe] translate failed tgt=%s: %s", tgt, e)
except Exception:
pass
return text
def _localize_msg(msg_en: str, user_lang: str) -> str:
"""Translate an English UI message to the user's language via _tx_safe."""
try:
if not user_lang or user_lang.startswith("en"):
return msg_en
# _tx_safe internally handles fast-fail/cache; we call it explicitly here
return _tx_safe(msg_en, src="en", tgt=user_lang)
except Exception:
# If translation fails for any reason, fall back to English
return msg_en
def _get_lang_token_id(tok, tag: str) -> int | None:
"""
Resolve token id for a language tag; prefer >>tag<< then fall back to plain.
Return None if unresolved — we'll skip decoder forcing in that case.
"""
unk = getattr(tok, "unk_token_id", None)
for cand in (f">>{tag}<<", tag, f"<<{tag}>>"):
try:
tid = tok.convert_tokens_to_ids(cand)
if isinstance(tid, int) and tid != -1 and (unk is None or tid != unk):
logger.debug("[tx] lang token '%s' → id=%s", cand, tid)
return tid
except Exception:
pass
logger.warning("[tx] cannot resolve token id for '%s'; will skip decoder forcing", tag)
return None
# Ensure cache envs are set earlier as we discussed
@st.cache_resource(show_spinner=False)
def _load_tx_models():
if not TRANSLATION_ENABLED:
return {"ready": False, "error": "Translation disabled"}
try:
import torch
from transformers import AutoModelForSeq2SeqLM, AutoTokenizer
cache_dir = os.getenv("TRANSFORMERS_CACHE", "/data/hf-cache/transformers")
device = "cuda" if torch.cuda.is_available() else "cpu"
logger.debug("[tx] cache_dir=%s device=%s", cache_dir, device)
# Indic → English
indic_en_tok = AutoTokenizer.from_pretrained(
"ai4bharat/indictrans2-indic-en-dist-200M",
use_fast=False, trust_remote_code=True, cache_dir=cache_dir
)
indic_en_mod = AutoModelForSeq2SeqLM.from_pretrained(
"ai4bharat/indictrans2-indic-en-dist-200M",
trust_remote_code=True, cache_dir=cache_dir
).to(device)
# English → Indic
en_indic_tok = AutoTokenizer.from_pretrained(
"ai4bharat/indictrans2-en-indic-dist-200M",
use_fast=False, trust_remote_code=True, cache_dir=cache_dir
)
en_indic_mod = AutoModelForSeq2SeqLM.from_pretrained(
"ai4bharat/indictrans2-en-indic-dist-200M",
trust_remote_code=True, cache_dir=cache_dir
).to(device)
logger.info("[tx] IndicTrans2 models loaded (trust_remote_code=True)")
return {
"ready": True, "device": device,
"indic_en_tok": indic_en_tok, "indic_en_mod": indic_en_mod,
"en_indic_tok": en_indic_tok, "en_indic_mod": en_indic_mod,
}
except Exception as e:
logger.error("[tx] load failed: %s", e, exc_info=True)
return {"ready": False, "error": str(e)}
# --- Language detection helpers (backend) ---
# Optional: langid
try:
import langid
_HAS_LANGID = True
try: langid.set_languages(['en','hi','mr'])
except Exception: pass
except Exception:
_HAS_LANGID = False
_DEVANAGARI_RANGE = ('\u0900', '\u097F')
_MAR_GLYPHS = set("ळऴऱय़ॲॅॉॐॠॡ")
_MAR_WORDS = {"आणि","माझ्या","यादी","कुठे","कधी","विक्री","ऑर्डर","दाखवा","एकूण"}
_HIN_WORDS = {"है","हूँ","सूची","कहाँ","कब","विक्रय","आदेश","दिखाइए","कुल"}
def _has_devanagari(text: str) -> bool:
return any(_DEVANAGARI_RANGE[0] <= ch <= _DEVANAGARI_RANGE[1] for ch in (text or ""))
def _score_words(text: str, lex: set[str]) -> int:
t = (text or "").lower()
return sum(1 for w in lex if w in t)
def _heuristic_hi_mr(text: str) -> str:
if not _has_devanagari(text):
return "en"
mar_score = sum(text.count(ch) for ch in _MAR_GLYPHS) + 2*_score_words(text,_MAR_WORDS)
hin_score = 2*_score_words(text,_HIN_WORDS)
if mar_score > hin_score: return "mr"
if hin_score > mar_score: return "hi"
return "hi"
def _detect_lang(text: str) -> str:
# 1) UI override
override = st.session_state.get("ui_lang_override")
if override in ("en","hi","mr"):
logger.debug("[lang] override=%s text=%s", override, _sample(text))
return override
# 2) langid
if _HAS_LANGID and (text or "").strip():
try:
lang, _ = langid.classify(text)
logger.debug("[lang] langid=%s text=%s", lang, _sample(text))
if lang in ("hi","mr"): return lang
if lang == "en" and not _has_devanagari(text): return "en"
except Exception as e:
logger.debug("[lang] langid error: %s", e)
# 3) heuristic
res = _heuristic_hi_mr(text)
logger.debug("[lang] heuristic=%s text=%s", res, _sample(text))
return res
def _tx_to_en_old(text: str, src_short: str) -> str:
"""Indic → English (prefix source tag + forced_bos to eng_Latn), with debug logs."""
if not text or not TRANSLATION_ENABLED or src_short == "en":
logger.debug("[tx→en] bypass (src=%s) text=%s", src_short, _sample(text))
return text
tx = _load_tx_models()
if not tx.get("ready"):
logger.warning("[tx→en] backend not ready: %s", tx.get("error"))
return text
import torch
tok = tx["indic_en_tok"]; mod = tx["indic_en_mod"]; device = tx["device"]
src_tag = LANG_CODE_MAP.get(src_short, "hin_Deva")
inp = _ensure_tag_prefixed(text, src_tag)
logger.debug("[tx→en] src_tag=%s device=%s inp=%s", src_tag, device, _sample(inp, 160))
try:
enc = tok([inp], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod = mod.to(device)
forced_bos = tok.get_lang_id("eng_Latn")
with torch.no_grad():
out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx→en] ok translated=%s", _sample(result, 200))
return result
except AssertionError as e:
logger.error("[tx→en] tokenizer assertion: %s | inp=%s", e, _sample(inp, 200), exc_info=True)
# hard fallback: try again with raw text (in case tag parsing misfired)
try:
raw = f">>{src_tag}<< " + (text or "").lstrip()
enc = tok([raw], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod = mod.to(device)
forced_bos = tok.get_lang_id("eng_Latn")
with torch.no_grad():
out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx→en] retry translated=%s", _sample(result, 200))
return result
except Exception as e2:
logger.error("[tx→en] retry failed: %s", e2, exc_info=True)
return text
except Exception as e:
logger.error("[tx→en] error: %s | inp=%s", e, _sample(inp, 200), exc_info=True)
return text
def _tx_from_en_old(text: str, tgt_short: str) -> str:
"""English → Indic (prefix eng_Latn + forced_bos to target), with debug logs."""
if not text or not TRANSLATION_ENABLED or tgt_short == "en":
logger.debug("[tx en→] bypass (tgt=%s) text=%s", tgt_short, _sample(text))
return text
tx = _load_tx_models()
if not tx.get("ready"):
logger.warning("[tx en→] backend not ready: %s", tx.get("error"))
return text
import torch
tok = tx["en_indic_tok"]; mod = tx["en_indic_mod"]; device = tx["device"]
tgt_tag = LANG_CODE_MAP.get(tgt_short, "hin_Deva")
inp = _ensure_tag_prefixed(text, "eng_Latn")
logger.debug("[tx en→] tgt_tag=%s device=%s inp=%s", tgt_tag, device, _sample(inp, 160))
try:
enc = tok([inp], return_tensors="pt", padding=True, truncation=True)
if device == "cuda":
enc = {k: v.to(device) for k, v in enc.items()}
mod = mod.to(device)
forced_bos = tok.get_lang_id(tgt_tag)
with torch.no_grad():
out = mod.generate(**enc, forced_bos_token_id=forced_bos, num_beams=4, max_new_tokens=256)
detok = tok.batch_decode(out, skip_special_tokens=True)
result = detok[0] if detok else text
logger.debug("[tx en→] ok translated=%s", _sample(result, 200))
return result
except Exception as e:
logger.error("[tx en→] error: %s | inp=%s", e, _sample(inp, 200), exc_info=True)
return text
def _tx_to_en(text: str, src_short: str) -> str:
"""
Indic → English using TWO-PLAIN-TAGS '<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
# ==== Conversation Memory Helpers (appended by 1807.py patch) ====
try:
import streamlit as st # used for session user_id
except Exception:
st = None # fallback if running headless
USE_CONVO_MEMORY: bool = True # toggle if needed
def _ensure_chat_turn_outputs_table():
import sqlite3
with sqlite3.connect(DB_PATH) as _conn:
_conn.execute(
"""
CREATE TABLE IF NOT EXISTS chat_turn_outputs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
chat_id INTEGER NOT NULL,
kind TEXT NOT NULL, -- 'sql', 'non_sql', etc
preview TEXT, -- short text preview of the output
rowcount INTEGER, -- number of rows if tabular
sample_json TEXT, -- small JSON sample of the output
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
)
_conn.commit()
def insert_turn_output(chat_id: int, kind: str, preview: str = None, rowcount: int = None, sample_json: str = None):
"""Store a summary of a turn's output without altering existing schema."""
import sqlite3
try:
_ensure_chat_turn_outputs_table()
with sqlite3.connect(DB_PATH) as _conn:
_conn.execute(
"""
INSERT INTO chat_turn_outputs (chat_id, kind, preview, rowcount, sample_json)
VALUES (?,?,?,?,?)
""",
(chat_id, kind, preview, rowcount, sample_json),
)
_conn.commit()
except Exception as _e:
try:
logger.debug("[mem] insert_turn_output failed: %s", _e)
except Exception:
pass
def _get_user_id_for_memory() -> str:
# Prefer session_state value, else fallback to anonymous
try:
if st and "user_id" in st.session_state and st.session_state.user_id:
return str(st.session_state.user_id)
except Exception:
pass
# Fallback: use logged-in customer_id value if present in session
try:
if st and "customer_id" in st.session_state and st.session_state.customer_id:
return str(st.session_state.customer_id)
except Exception:
pass
return "anonymous"
def _fetch_recent_history_messages(user_id: str, k: int = 10):
"""Return a list of OpenAI-style messages built from last k user/assistant turns."""
import sqlite3, json
msgs = []
try:
with sqlite3.connect(DB_PATH) as _conn:
_conn.row_factory = sqlite3.Row
rows = _conn.execute(
"""
SELECT id, role, user_input
FROM chat_history
WHERE user_id = ? AND role IN ('user','assistant')
ORDER BY id DESC
LIMIT ?
""",
(user_id, k * 2),
).fetchall()
# reverse to chronological
for r in reversed(rows):
role = r["role"]
text = r["user_input"] or ""
if role not in ("user", "assistant"):
continue
msgs.append({"role": role, "content": text})
except Exception as _e:
try:
logger.debug("[mem] fetch history failed: %s", _e)
except Exception:
pass
return msgs
# ==== End Conversation Memory Helpers ====
# =======================
# Memory & History Patch
# (Appended by assistant for 1943.py)
# =======================
def _detect_columns(conn, table_name: str) -> set[str]:
try:
cur = conn.execute(f"PRAGMA table_info({table_name})")
return {row[1] for row in cur.fetchall()}
except Exception:
return set()
def _first_existing(cols: set[str], *candidates: str) -> str | None:
for c in candidates:
if c in cols:
return c
return None
def _table_exists(conn, table_name: str) -> bool:
try:
cur = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name=?;", (table_name,))
return cur.fetchone() is not None
except Exception:
return False
def _columns_of(conn, table_name: str):
try:
cur = conn.execute(f"PRAGMA table_info({table_name})")
return [r[1] for r in cur.fetchall()]
except Exception:
return []
def _format_history_for_llm(history):
"""
history: list of {'role': 'user'|'assistant', 'text': str, 'ts': ...}
Returns a compact string block for the prompt.
"""
if not history:
return ""
lines = []
# reverse chronological -> chronological for readability
for turn in reversed(history):
role = "User" if turn.get("role") == "user" else "Assistant"
txt = (turn.get("text") or "").strip()
if not txt:
continue
# keep it shortish; LLM has token limits
if len(txt) > 1200:
txt = txt[:1200] + " …"
lines.append(f"{role}: {txt}")
return "\n".join(lines)
def _detect_chat_history_pk(conn) -> str:
cols = _columns_of(conn, "chat_history")
# Prefer common PK names
for k in ("id", "chat_id", "pk", "rowid"):
if k in cols:
return k
# fallback to the first column if nothing matches
return cols[0] if cols else "id"
def _safe_pick(d: dict, keys, default=None):
for k in keys:
if k in d and d[k] is not None:
v = d[k]
# Coerce bytes to str for safety
if isinstance(v, (bytes, bytearray)):
try:
v = v.decode("utf-8", "ignore")
except Exception:
v = str(v)
return str(v)
return default
def _row_dicts(cursor, rows):
names = [c[0] for c in cursor.description] if cursor.description else []
out = []
for r in rows:
if isinstance(r, dict):
out.append(r)
else:
d = {}
for i, name in enumerate(names):
d[name] = r[i] if i < len(r) else None
out.append(d)
return out
# =======================
# End Memory & History Patch
# =======================
# === Memory helpers (2019 fix) =============================================
def _fetch_recent_chat_messages(conn, user_id, limit_pairs=10):
"""
Returns [{"role": "user"/"assistant", "content": "..."}] built from
chat_history (+ optionally chat_turn_outputs).
Robust to column name differences and missing assistant rows in chat_history.
"""
try:
cur = conn.cursor()
# try to discover column names safely
# fallbacks if your schema uses different names
# (you can simplify if your columns are fixed)
cols = {c: c for c in [
"id","chat_id","turn","user_id","role","text","assistant_text","content","message",
"intent","sentiment","created_at","ts","lang"
]}
# Chat history (user+assistant in the same table)
q1 = """
SELECT
COALESCE(chat_id, 0) AS chat_id,
COALESCE(turn, 0) AS turn,
role AS role,
COALESCE(text, content, message, '') AS msg
FROM chat_history
WHERE user_id = ?
ORDER BY COALESCE(chat_id, 0) DESC, COALESCE(turn, 0) DESC
LIMIT ?
"""
# Assistant outputs from the outputs table (if it exists)
# Constant role label is provided via parameter `?` as a column value.
q2 = """
SELECT
COALESCE(chat_id, 0) AS chat_id,
COALESCE(turn, 0) AS turn,
? AS role,
COALESCE(assistant_text, '') AS msg
FROM chat_turn_outputs
ORDER BY COALESCE(chat_id, 0) DESC, COALESCE(turn, 0) DESC
LIMIT ?
"""
messages = []
# Fetch user+assistant rows from chat_history
try:
cur.execute(q1, (user_id, limit_pairs * 2))
rows1 = cur.fetchall()
except Exception as e:
logger.debug("[mem] fetch chat_history failed: %s", e)
rows1 = []
# Fetch assistant rows from chat_turn_outputs (optional table)
rows2 = []
try:
cur.execute("SELECT 1 FROM chat_turn_outputs LIMIT 1")
cur.execute(q2, ("assistant", limit_pairs * 2))
rows2 = cur.fetchall()
except Exception as e:
# table might not exist; that's fine
logger.debug("[mem] fetch chat_turn_outputs skipped: %s", e)
# Merge and sort by (chat_id, turn)
rows = []
rows.extend(rows1)
rows.extend(rows2)
try:
rows.sort(key=lambda r: (r[0] or 0, r[1] or 0), reverse=True)
except Exception:
pass
# Build role/content messages
for r in rows[: limit_pairs * 2]:
role = (r[2] or "").strip().lower()
text = (r[3] or "").strip()
if not text:
continue
if role not in ("user", "assistant"):
# fall back if chat_history stored only user messages here
role = "user"
messages.append({"role": role, "content": text})
# reverse to chronological order (oldest -> newest)
messages.reverse()
return messages
except Exception as e:
logger.debug("[mem] fetch history failed (outer): %s", e)
return []
def _inject_memory(messages: list, user_id: str, max_turns: int = 10):
"""Insert last chat history before the current turn."""
hist = _fetch_recent_chat_messages(user_id=user_id, limit=max_turns*2)
# preserve initial system messages
prefix = []
i = 0
while i < len(messages) and isinstance(messages[i], dict) and messages[i].get("role") == "system":
prefix.append(messages[i])
i += 1
new_msgs = prefix + hist + messages[i:]
try:
injected_chars = sum(len(m.get("content","")) for m in hist if isinstance(m, dict))
logger.debug("[mem] injected history chars=%s", injected_chars)
except Exception:
pass
return new_msgs
def _wrap_llm_with_memory(llm, messages=None, user_id=None, max_turns: int = 10, **kwargs):
llm = _ensure_llm_chat(llm)
"""
Universal wrapper that tolerates older call-sites.
Usage patterns supported:
- _wrap_llm_with_memory(llm, messages=[...], user_id="U_xxx")
- _wrap_llm_with_memory(llm, user_id="U_xxx", messages=messages)
Falls back gracefully if anything fails.
"""
try:
if messages is None:
messages = kwargs.get("messages") or []
if user_id is None:
user_id = kwargs.get("user_id")
if user_id:
messages = _inject_memory(messages, user_id, max_turns=max_turns)
return llm.chat(messages)
except Exception as e:
try:
logger.debug("[mem] wrap failed: %s", e)
except Exception:
pass
# Fallback: direct call with original messages if provided
try:
return llm.chat(messages if messages is not None else kwargs.get("messages", []))
except Exception:
# Last-resort: return minimal structure
return {"content": "Internal error calling LLM."}
# === End Memory helpers (2019 fix) =========================================
# -----------------------------
# Core validation / scoping (continued)
# -----------------------------
def _extract_tables_from_sql(sql: str) -> list[str]:
if not isinstance(sql, str): return []
tables = []
for kw in ("FROM", "JOIN"):
for m in re.finditer(rf"\b{kw}\s+([A-Za-z_][A-Za-z0-9_+]*)", sql, flags=re.IGNORECASE):
tables.append(m.group(1).lower())
return tables
def _scope_sql_to_customer(sql: str, customer_id: str) -> str:
"""
Applies customer scope to orders/order_items/order_payments/order_reviews if referenced.
"""
s = _strip_trailing_semicolon(sql)
has_orders = re.search(r"(?:FROM|JOIN)\s+orders\b", s, re.IGNORECASE) is not None
has_oi = re.search(r"(?:FROM|JOIN)\s+order_items\b", s, re.IGNORECASE) is not None
has_op = re.search(r"(?:FROM|JOIN)\s+order_payments\b", s, re.IGNORECASE) is not None
has_or = re.search(r"(?:FROM|JOIN)\s+order_reviews\b", s, re.IGNORECASE) is not None
if not (has_orders or has_oi or has_op or has_or):
return s
# prefer alias if present
def ref_for(tbl):
alias, ok = _find_table_alias(s, tbl)
return alias if ok else tbl
o_ref, has_o_top = _top_level_alias(s, "orders")
oi_ref, has_oi_top = _top_level_alias(s, "order_items")
op_ref, has_op_top = _top_level_alias(s, "order_payments")
or_ref, has_or_top = _top_level_alias(s, "order_reviews")
# if orders table is present, scope directly
if has_o_top and o_ref:
cond = f"{o_ref}.customer_id = '{customer_id}'"
return _cleanup_where_and(_inject_where(s, cond))
# otherwise scope through EXISTS join back to orders
child_ref = None
if has_oi_top: child_ref = oi_ref
elif has_op_top: child_ref = op_ref
elif has_or_top: child_ref = or_ref
if child_ref:
if re.search(r"EXISTS\s*\(\s*SELECT\s+1\s+FROM\s+orders\s+\w+\s+WHERE", s, flags=re.IGNORECASE):
return s
cond = f"EXISTS (SELECT 1 FROM orders o WHERE o.order_id = {child_ref}.order_id AND o.customer_id = '{customer_id}')"
return _inject_where(s, cond)
return s
def _apply_policy(sql: str, role: str, user_text: str, customer_id: str | None) -> tuple[str | None, dict]:
"""
Validates, scopes, and enforces policy. Returns (secured_sql, meta_flags)
If blocked, secured_sql is None and meta_flags contain 'blocked_reason'/'policy_note'.
"""
s = (sql or "").strip()
flags = {}
# Hard blocks: write/DDL
if _is_mutating_sql(s):
flags["blocked_reason"] = "write_block"
return None, flags
# Hard block: schema introspection for normal users
if role == "user" and (_is_schema_introspection_sql(s) or _intent_schema_introspection(user_text)):
flags["blocked_reason"] = "introspection_user_block"
return None, flags
# Hard block: "dump all data" intents for normal users
if role == "user" and _intent_dump_all_data(user_text):
flags["blocked_reason"] = "dump_all_block"
return None, flags
# Hard block: row counts of ALL tables / EACH table
if _intent_rowcounts_all_tables(user_text):
flags["blocked_reason"] = "rowcounts_all_tables_block"
return None, flags
# Hard block: normal user cannot access 'customers' table directly
if role == "user":
tables = set(_extract_tables_from_sql(s))
if "customers" in tables:
flags["blocked_reason"] = "customers_user_block"
return None, flags
# Seller/Product revenue restrictions for normal user
if role == "user":
if SELLER_REVENUE_USER_MODE.upper() == "BLOCK" and _is_seller_revenue_sql(s):
flags["blocked_reason"] = "seller_revenue_user_block"; return None, flags
if PRODUCT_REVENUE_USER_MODE.upper() == "BLOCK" and _is_product_revenue_sql(s):
flags["blocked_reason"] = "product_revenue_user_block"; return None, flags
# Platform-wide payments: block or scope
if _is_payments_sql(s):
if role == "user" and PAYMENTS_USER_MODE == "BLOCK":
flags["blocked_reason"] = "platform_payments_user_block"; return None, flags
if role == "user" and PAYMENTS_USER_MODE == "SCOPE":
# scope to customer's payments even if query was platform-wide
s = _scope_sql_to_customer(s, customer_id)
# Row-count on any single table? For normal user, block.
if role == "user" and _is_user_count_query(s):
flags["blocked_reason"] = "count_user_block"; return None, flags
# Scope to current customer where applicable (orders/oi/op/or)
if customer_id and role == "user":
s = _scope_sql_to_customer(s, customer_id)
# Products/Sellers queries do not need customer scope (as per requirement)
s = _cleanup_where_and(s)
return s, flags
# Put this once near the top if not already present
_SCOPE_EXISTS_RE = re.compile(
r"""\b(?:o\.)?customer_id\s*=\s*(?:\?|['"][^'"]+['"])""",
re.IGNORECASE,
)
# --- add once near your other helpers (top-level is fine) ---
def _extract_queries_from_plan(plan_obj) -> list[str]:
"""
Normalize the plan["queries"] into a flat list of SQL strings.
Supports both list[str] and list[dict] with common keys.
"""
out: list[str] = []
if not isinstance(plan_obj, dict):
return out
raw = plan_obj.get("queries", [])
if not isinstance(raw, list):
return out
for item in raw:
if isinstance(item, str):
s = item.strip()
if s:
out.append(s)
elif isinstance(item, dict):
cand = (
item.get("secured")
or item.get("sql")
or item.get("original")
or item.get("text")
)
if isinstance(cand, str) and cand.strip():
out.append(cand.strip())
return out
def validate_sql(
sql: str,
role: str,
customer_id: Optional[str],
registry: dict,
user_text: str,
**_unused
) -> tuple[Optional[str], dict]:
# Work on a single string variable; don't overwrite later.
text = (sql or "").strip()
flags: dict = {}
params: list = []
# Scope normal users to their own orders if the query touches orders and isn't already scoped
touches_orders = bool(re.search(r'\b(from|join)\s+orders\b', text, re.I))
if role == "user" and touches_orders and not _SCOPE_EXISTS_RE.search(text):
# Choose alias safely
alias = "o" if re.search(r'\borders\s+o\b', text, re.I) else "orders"
if re.search(r'\bwhere\b', text, re.I):
text += f" AND {alias}.customer_id = ?"
else:
text += f" WHERE {alias}.customer_id = ?"
if customer_id:
params.append(customer_id)
flags["scoped_orders"] = True
# Pagination guardrails
ut = (user_text or "")
wants_all = _wants_all(ut)
if _looks_like_select(text):
if role != "admin":
# enforce preview limit unless user explicitly asked for "all"
if not wants_all:
text = _cap_limit(text, 50)
else:
if not wants_all:
text = _cap_limit(text, 50)
# Optional product NOT IN clean-up (uses your existing helpers)
if _mentions_product(text):
alias_found, ok = _find_table_alias(text, "products")
if ok:
text = _remove_unscoped_notin_products(text, alias_found)
# Apply your higher-level policy last
secured, policy_flags = _apply_policy(text, role, ut, customer_id)
flags.update(policy_flags or {})
if secured is None:
return None, {"blocked": True, **policy_flags}
# Note if LIMIT present
if _has_limit_clause(secured):
policy_flags["limit_enforced"] = True
# Attach bound params so executor can use them without changing your return shape
if params:
flags["params"] = params
return secured, flags
# -----------------------------
# Planner + Runner
# -----------------------------
def handle_user_prompt(user_text: str, role: str, customer_id: str, registry: dict, source_lang: str = "en"):
"""
user_text is already English (translated if needed). source_lang is the user's original language ('en'|'hi'|'mr')
"""
trace_id = f"{int(time.time()*1000)}"
if TRACE_LOGGING:
logger.info("[trace=%s] user=%s role=%s text=%r lang=%s", trace_id, customer_id, role, user_text, source_lang)
# Early block: platform-wide payments
if role == "user" and PAYMENTS_USER_MODE == "BLOCK" and _intent_platform_wide_payments(user_text):
if TRACE_LOGGING:
logger.info("[trace=%s] Early BLOCK: platform-wide payments intent detected", trace_id)
plan = {"queries": [], "explanation": "Platform-wide payments are restricted to admins."}
# Translate explanation back if needed
plan["explanation_display"] = _tx_from_en(plan["explanation"], source_lang)
return {
"plan": plan,
"queries": [{"kind": "non_sql", "original": user_text, "secured": None,
"blocked_reason": "platform_payments_user_block",
"df": pd.DataFrame({"message": [
_tx_from_en("Platform-wide payments are restricted to admins. Try: 'Show my payments'.", source_lang)
]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
# Early block: schema introspection ("list tables", "show schema", etc.) is admin-only
if role == "user" and _intent_schema_introspection(user_text):
if TRACE_LOGGING:
logger.info("[trace=%s] Early BLOCK: schema-introspection intent detected", trace_id)
plan = {"queries": [], "explanation": "Listing tables/indexes/schema is admin-only."}
plan["explanation_display"] = _tx_from_en(plan["explanation"], source_lang)
return {
"plan": plan,
"queries": [{
"original": user_text, "secured": None,
"blocked_reason": "introspection_user_block",
"df": pd.DataFrame({"message": [_tx_from_en(plan["explanation"], source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
# Early block: row counts across all tables / each table
if _intent_rowcounts_all_tables(user_text):
msg = "Requests for row counts across all tables / each table are restricted and not supported."
plan = {"queries": [], "explanation": msg, "explanation_display": _tx_from_en(msg, source_lang)}
return {
"plan": plan,
"queries": [{
"original": user_text,
"secured": None,
"blocked_reason": "rowcounts_all_tables_block",
"df": pd.DataFrame({"message": [_tx_from_en(msg, source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
# Early block: dump-all-data for normal users
if role == "user" and _intent_dump_all_data(user_text):
msg = "Requests to list/dump data across all tables are restricted."
plan = {"queries": [], "explanation": msg, "explanation_display": _tx_from_en(msg, source_lang)}
return {
"plan": plan,
"queries": [{
"original": user_text, "secured": None,
"blocked_reason": "dump_all_block",
"df": pd.DataFrame({"message": [_tx_from_en(msg, source_lang)]}),
"error": None, "sql_time": None
}],
"llm_time": 0.0
}
llm = get_llm_client(LLM_CHOICE)
# Inject last-10-turn conversational memory into all LLM calls
try:
llm = _wrap_llm_with_memory(llm)
except Exception as _e:
logger.debug("[mem] wrap failed: %s", _e)
# 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()
llm = _ensure_llm_chat(llm) # injected shim
plan = plan_sql(llm, user_text, registry)
# NEW: extract and cap queries for execution
queries = _extract_queries_from_plan(plan)
llm_elapsed = time.perf_counter() - t0
# Prepare output structure
out_queries = []
for q in queries[:2]:
entry = {"original": q, "secured": None, "error": None, "df": None, "sql_time": None}
try:
secured, flags = validate_sql(q, role, customer_id, registry, user_text)
params = flags.get("params")
# If there’s a “?” in the SQL but no params surfaced (edge case), use customer_id defensively:
if params is None and "?" in (secured or "") and customer_id:
params = [customer_id]
# --- policy blocks / early exit ---
if secured is None:
entry.update(flags)
reason = flags.get("blocked_reason") or "blocked"
msg_map = {
"seller_revenue_user_block": "Seller revenue queries are restricted to admins.",
"product_revenue_user_block": "Product revenue queries are restricted to admins.",
"introspection_user_block": "Listing tables/indexes/schema is admin-only.",
"dump_all_block": "Requests to list/dump all database data are restricted.",
"rowcounts_all_tables_block": "Requests for row counts across all tables / each table are restricted.",
"count_user_block": "Row-count (COUNT) queries are restricted for normal users.",
"customers_user_block": "Access to the customers table is restricted for normal users.",
"platform_payments_user_block": "Platform-wide payments are restricted to admins.",
"blocked": "This request is restricted by policy.",
}
entry["df"] = pd.DataFrame({"message": [_tx_safe(msg_map.get(reason, msg_map["blocked"]), source_lang)]})
out_queries.append(entry)
continue
# --- allowed path ---
entry.update(flags)
entry["secured"] = secured
sql_for_exec = (secured, params) if params else secured
t1 = time.perf_counter()
df, rows, kind = run_sql_generic(sql_for_exec)
entry["sql_time"] = time.perf_counter() - t1
entry["df"] = df if kind == "select" else pd.DataFrame({"rows_affected": [rows]})
# optional warn note
revenue_mode = (os.getenv("REVENUE_USER_MODE") or os.getenv("SELLER_REVENUE_USER_MODE", "BLOCK")).upper()
if role == "user" and _is_seller_revenue_sql(secured) and revenue_mode == "WARN":
entry["policy_note"] = _tx_safe(
"Note: revenue-style queries for sellers/products are limited for normal users.",
source_lang
)
except SqlTimeoutError as e:
entry["error"] = _tx_safe(str(e), source_lang)
except Exception as e:
entry["error"] = _tx_safe(str(e), source_lang)
out_queries.append(entry)
# plan explanation (safe)
expl = plan.get("explanation")
plan["explanation_display"] = _tx_safe(expl, source_lang) if isinstance(expl, str) and expl.strip() else None
return {"plan": plan, "queries": out_queries, "llm_time": llm_elapsed}
# -----------------------------
# Classification, history, feedback, MIS (unchanged logic)
# -----------------------------
@st.cache_data(ttl=30)
def load_chat_history(days: int | None = 30, only_user_id: str | None = None) -> pd.DataFrame:
q = "SELECT * FROM chat_history"
params, conds = [], []
if days is not None:
since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S")
conds.append("created_at >= ?"); params.append(since)
if only_user_id:
conds.append("user_id = ?"); params.append(only_user_id)
if conds:
q += " WHERE " + " AND ".join(conds)
q += " ORDER BY created_at DESC"
with get_conn() as conn:
df = pd.read_sql_query(q, conn, params=params)
return df
@st.cache_data(ttl=30, show_spinner=False)
def _feedback_stats_for_user(user_id: str | None, days: int | None):
"""
Returns a dict with helpful_count (feedback=1), not_helpful_count (feedback=0),
total_feedback, helpful_pct — filtered by user and timeframe.
"""
q = """
SELECT
COUNT(*) AS total_feedback,
SUM(CASE WHEN feedback = 1 THEN 1 ELSE 0 END) AS helpful_count,
SUM(CASE WHEN feedback = 0 THEN 1 ELSE 0 END) AS not_helpful_count,
ROUND(100.0 * SUM(CASE WHEN feedback = 1 THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 1) AS helpful_pct
FROM chat_history
WHERE (role = 'user' OR role IS NULL)
"""
params = []
if user_id:
q += " AND user_id = ?"
params.append(user_id)
if days is not None:
# inclusive lower bound by timestamp
since = (datetime.utcnow() - timedelta(days=days)).strftime("%Y-%m-%d %H:%M:%S")
q += " AND created_at >= ?"
params.append(since)
with get_conn() as conn:
row = pd.read_sql_query(q, conn, params=params).iloc[0].to_dict()
# normalize Nones
for k in ("total_feedback", "helpful_count", "not_helpful_count"):
row[k] = int(row.get(k) or 0)
row["helpful_pct"] = float(row.get("helpful_pct") or 0.0)
return row
@st.cache_data(show_spinner=False, ttl=30)
def get_unrated_conversations_for_user(user_id: int, limit: int = 100):
sql = """
WITH user_convos AS (
SELECT conversation_id, MAX(created_at) AS last_time
FROM chat_history
WHERE user_id = ? AND conversation_id IS NOT NULL
GROUP BY conversation_id
),
rated AS (
SELECT DISTINCT conversation_id
FROM chat_history
WHERE user_id = ? AND conversation_id IS NOT NULL AND feedback_value IS NOT NULL
)
SELECT uc.conversation_id, uc.last_time
FROM user_convos uc
WHERE uc.conversation_id NOT IN (SELECT conversation_id FROM rated)
ORDER BY uc.last_time DESC
LIMIT ?
"""
with get_conn() as conn:
df = pd.read_sql_query(sql, conn, params=[user_id, user_id, limit])
return df
def record_conversation_feedback(get_conn, *, user_id: int, conversation_id: str, value: int, text: str | None = None) -> int:
"""
Insert a single feedback row. Returns inserted row id.
value: +1 for thumbs up, -1 for thumbs down
"""
now = datetime.utcnow().isoformat(timespec="seconds")
fb_logger.info("record_conversation_feedback called user=%s cid=%s value=%s text=%s",
user_id, conversation_id, value, (text or ""))
with get_conn() as conn:
# Safety: if already rated, log & short-circuit
cur_check = conn.execute(
"""
SELECT id, feedback_value, feedback_at
FROM chat_history
WHERE user_id = ? AND conversation_id = ? AND feedback_value IS NOT NULL
ORDER BY feedback_at DESC
LIMIT 1
""",
(user_id, conversation_id),
)
existing = cur_check.fetchone()
if existing:
fb_logger.warning("Feedback already exists for user=%s cid=%s -> row=%s", user_id, conversation_id, existing)
return existing[0]
sql = """
INSERT INTO chat_history (
user_id, role, conversation_id,
input_text, response_text,
sentiment, intent,
feedback_value, feedback_text, feedback_at,
created_at
) VALUES (?,?,?,?,?,?,?,?,?,?,?)
"""
params = [
user_id, "user", conversation_id,
"[feedback]", None,
"neutral", "feedback",
value, text, now,
now,
]
fb_logger.debug("About to INSERT feedback: %s", params)
cur = conn.execute(sql, params)
conn.commit()
rowid = cur.lastrowid
fb_logger.info("Feedback INSERT committed rowid=%s", rowid)
# Verify readback
cur2 = conn.execute("SELECT conversation_id, feedback_value, feedback_at FROM chat_history WHERE id=?", (rowid,))
row2 = cur2.fetchone()
fb_logger.debug("Readback row: %s", row2)
return rowid
import sqlite3
from datetime import datetime
def _ensure_sql_string(x):
"""Coerce validator output into a SQL string."""
if x is None:
return ""
if isinstance(x, str):
return x
# common shapes: (sql, reason) or [sql, reason]
if isinstance(x, (tuple, list)) and x:
return _ensure_sql_string(x[0])
# dict shapes from some planners/validators
if isinstance(x, dict):
for k in ("secured", "sql", "text", "query", "original"):
v = x.get(k)
if isinstance(v, str):
return v
# last resort: stringify
return str(x)
def update_chat_feedback(chat_id: int, feedback_value: int) -> Tuple[bool, str]:
"""
Update feedback for the most recent chat (current session) regardless of owner.
Returns (ok, msg).
"""
fb_logger.info("update_chat_feedback -- start chat_id=%s value=%s", chat_id, feedback_value)
try:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
cur = conn.execute(
"""
UPDATE chat_history
SET feedback_value = ?, feedback_at = CURRENT_TIMESTAMP
WHERE chat_id = ?
""",
(feedback_value, chat_id),
)
conn.commit()
if cur.rowcount == 0:
fb_logger.warning("update_chat_feedback -- no row updated chat_id=%s", chat_id)
return False, "Could not record feedback (chat not found)."
fb_logger.info("update_chat_feedback -- success chat_id=%s value=%s", chat_id, feedback_value)
return True, "Thanks for the feedback!"
except sqlite3.OperationalError as e:
fb_logger.error("sqlite operational error on feedback update chat_id=%s err=%s", chat_id, e)
return False, "Database busy—please try again."
except Exception as e:
fb_logger.exception("unexpected error on feedback update chat_id=%s", chat_id)
return False, "Failed to record feedback."
def get_chat_feedback(chat_id: int) -> Optional[int]:
"""Return 1/0 if feedback exists, else None."""
try:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
cur = conn.execute(
"SELECT feedback_value FROM chat_history WHERE chat_id = ?",
(chat_id,),
)
row = cur.fetchone()
if not row:
return None
return row[0] if row[0] is not None else None
except Exception as e:
fb_logger.error("get_chat_feedback failed chat_id=%s err=%s", chat_id, e)
return None
def update_chat_feedback_if_owner(chat_id: int, user_id: str, feedback_value: int) -> Tuple[bool, str]:
"""
Update feedback only if the given user owns the chat row.
Used in 'Rate Earlier Conversations'.
"""
fb_logger.info("update_chat_feedback_if_owner called chat_id=%s user_id=%s value=%s",
chat_id, user_id, feedback_value)
try:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
cur = conn.execute(
"""
UPDATE chat_history
SET feedback_value = ?, feedback_at = CURRENT_TIMESTAMP
WHERE chat_id = ? AND user_id = ?
""",
(feedback_value, chat_id, user_id),
)
conn.commit()
if cur.rowcount == 0:
fb_logger.warning("update_chat_feedback_if_owner -- no row updated chat_id=%s user_id=%s",
chat_id, user_id)
return False, "You can only rate your own conversations."
fb_logger.info("update_chat_feedback_if_owner -- success chat_id=%s value=%s", chat_id, feedback_value)
return True, "Thanks for the feedback!"
except sqlite3.OperationalError as e:
fb_logger.error("sqlite operational error on feedback update chat_id=%s err=%s", chat_id, e)
return False, "Database busy—please try again."
except Exception as e:
fb_logger.exception("unexpected error on owner feedback update chat_id=%s user_id=%s", chat_id, user_id)
return False, "Failed to record feedback."
def render_rate_earlier_conversation_tab(auth):
st.subheader("Rate Earlier Conversations")
fb_logger.info("Rate tab opened for user_id=%s", auth["user_id"])
unrated = get_unrated_conversations_for_user(auth["user_id"], limit=200)
fb_logger.info("Unrated conversations fetched: %d", len(unrated))
if unrated.empty:
st.success("No pending conversations to rate. 🙌")
return
# Use the actual conversation_id as the select value; label just for display.
conv_ids = unrated["conversation_id"].tolist()
def _conv_label(cid: str) -> str:
with get_conn() as conn:
row = conn.execute(
"""
SELECT created_at, input_text
FROM chat_history
WHERE conversation_id = ? AND user_id = ?
ORDER BY created_at DESC
LIMIT 1
""",
(cid, auth["user_id"]),
).fetchone()
ts = row[0] if row else ""
prev = (row[1][:60] + "…") if row and row[1] else ""
return f"#{cid} • {ts} • {prev}"
selected_conversation_id = st.selectbox(
"Pick a conversation to preview & rate:",
options=conv_ids,
format_func=_conv_label,
index=0,
key="select_conv_to_rate",
)
fb_logger.debug("Selected conversation_id=%s", selected_conversation_id)
if st.button("Preview Result", key="preview_conv"):
fb_logger.info("Preview clicked for cid=%s", selected_conversation_id)
with get_conn() as conn:
sel = pd.read_sql_query(
"""
SELECT *
FROM chat_history
WHERE conversation_id = ? AND user_id = ?
ORDER BY created_at DESC
LIMIT 10
""",
conn, params=[selected_conversation_id, auth["user_id"]]
)
st.markdown("**Recent Messages**")
if sel.empty:
st.info("No rows found to preview for this conversation.")
else:
for _, r in sel.iterrows():
who = r.get("role", "user")
txt = r.get("response_text") if who != "user" else r.get("input_text")
st.markdown(f"**{who.capitalize()}** — {r.get('created_at','')}")
st.code(str(txt or "").strip()[:2000], language="markdown")
# (Optional) add your SQL preview here as before
cols = st.columns([1,1,3])
with cols[0]:
up = st.button("👍 Thumbs Up", use_container_width=True, key="fb_up")
with cols[1]:
down = st.button("👎 Thumbs Down", use_container_width=True, key="fb_down")
with cols[2]:
fb_txt = st.text_input("Optional note", key="fb_note")
if up or down:
value = 1 if up else -1
fb_logger.info("Feedback button pressed: value=%s cid=%s note=%s", value, selected_conversation_id, fb_txt)
try:
rowid = record_conversation_feedback(
get_conn,
user_id=auth["user_id"],
conversation_id=selected_conversation_id,
value=value,
text=(fb_txt or None),
)
st.success(_localize_msg("Thanks! Your feedback has been recorded.", src_lang))
fb_logger.info("Feedback saved rowid=%s; clearing caches and rerunning", rowid)
# Clear caches so the just-rated convo disappears immediately
try:
get_unrated_conversations_for_user.clear()
fb_logger.debug("Cleared cache for get_unrated_conversations_for_user")
except Exception as e:
st.cache_data.clear()
fb_logger.warning("Fallback cache clear used: %s", e)
st.rerun()
except Exception as e:
fb_logger.exception("Feedback insert failed for cid=%s", selected_conversation_id)
st.error("Failed to save feedback. Please try again.")
def _kpi_number(label, value, help_text=None):
c1 = st.container(); c1.metric(label, value)
if help_text: c1.caption(help_text)
def _safe_pct(n, d): return (100.0 * n / d) if d else 0.0
def _json_obj(x):
"""Return Python object if x is JSON (or already a py obj); else None."""
if x is None:
return None
if isinstance(x, (dict, list)):
return x
if isinstance(x, str):
s = x.strip()
if not s:
return None
try:
return json.loads(s)
except Exception:
return None
return None
def _json_list(x):
"""
Normalize x into a list.
- If x is JSON list -> return it
- If x is JSON dict -> try common keys
- If x is plain string -> split on ; / newlines
- Else -> []
"""
obj = _json_obj(x)
if obj is None:
if isinstance(x, str):
parts = [p.strip() for p in re.split(r"[;\n]+", x) if p.strip()]
return parts
return []
if isinstance(obj, list):
return obj
if isinstance(obj, dict):
for k in ("secured_sql", "sql", "queries", "items"):
v = obj.get(k)
if isinstance(v, list):
return v
return []
def _json_plan_queries(x):
try:
obj = json.loads(x) if isinstance(x, str) else (x or {})
qs = obj.get("queries", [])
return qs if isinstance(qs, list) else []
except Exception:
return []
def _norm_sql(s: str) -> str:
if not isinstance(s, str): return ""
return re.sub(r"\s+", " ", s).strip().upper()
def _auto_repaired(row) -> bool:
plan_qs = _json_plan_queries(row.get("llm_plan"))
sec_qs = _json_list(row.get("secured_sql"))
if len(plan_qs) != len(sec_qs):
return True
for a, b in zip(plan_qs, sec_qs):
if _norm_sql(a) != _norm_sql(b):
return True
return False
def _pill(text: str, tone: str = "neutral"):
"""Render a small rounded pill; tone in {'positive','neutral','negative'}."""
palette = {
"positive": ("#065f46", "#d1fae5"), # dark text, light bg
"neutral": ("#374151", "#e5e7eb"),
"negative": ("#7f1d1d", "#fee2e2"),
}
fg, bg = palette.get(tone, palette["neutral"])
return f"""
<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_chat_output(out):
import streamlit as st
import pandas as pd
# Normalize objects
out = out or {}
plan = out.get("plan") or {}
queries = out.get("queries") or []
if out.get("src_lang"):
src_lang = out.get("src_lang")
else:
src_lang = "en"
# Prefer a simple chat bubble if 'text' is present
if out.get("text"):
st.chat_message("assistant").write(out["text"])
return # <-- IMPORTANT: stop here, do not render tables/cards
# Detect policy/guard conditions
blocked = bool(plan.get("blocked") or out.get("blocked") or any((q or {}).get("blocked_reason") for q in queries))
# Prefer explicit display message if present
display_msg = plan.get("explanation_display") or out.get("msg") or plan.get("explanation")
# If this is a hard block, show the guardrail and EXIT early
if blocked:
st.warning(display_msg or "This action is restricted.")
# Also render any specific per-card warnings, if present
for q in queries:
br = (q or {}).get("blocked_reason")
if br == "seller_revenue_user_block":
st.warning("🔒 Seller revenue queries are not available to normal users. Please contact an admin.")
if br == "product_revenue_user_block":
st.warning("🔒 Product revenue queries are not available to normal users. Please contact an admin.")
if br == "platform_payments_user_block":
st.warning("🔒 Platform-wide payments are restricted to admins.")
if br == "rowcounts_all_tables_block":
st.warning("🔒 Requests for row counts across all tables / each table are restricted.")
if br == "write_block":
st.warning("🔒 Write/DDL operations are disabled.")
if br == "introspection_user_block":
st.warning("🔒 Listing tables/indexes/schema is admin-only.")
if br == "dump_all_block":
st.warning("🔒 Requests to list/dump all database data are restricted.")
if br == "count_user_block":
st.warning("🔒 Row-count (COUNT) queries are restricted for normal users.")
if br == "customers_user_block":
st.warning("🔒 Access to the **customers** table is restricted for normal users.")
if br == "destructive_request_block":
st.warning("🔒 Destructive actions (delete/update/drop) are not allowed.")
if (q or {}).get("policy_note"):
st.info(q["policy_note"])
return
# Not blocked: optionally show any informative message, but DO NOT exit early
if display_msg:
# Demote to info so results still render below
st.info(display_msg)
# ---------------------------------------------------------------------
# ✅ Reconciled UI block (as requested): renders tables/queries/results
# ---------------------------------------------------------------------
_assistant_msg = "Pls find below the data for the input you provided"
with st.chat_message("assistant"):
st.write(_localize_msg(_assistant_msg, src_lang))
cols = st.columns(max(1, len(queries)))
for i, card in enumerate(queries):
card = card or {}
with cols[i]:
pill = "Limited to 50" if card.get("limit_enforced") else None
if SHOW_DETAILS:
card_open(f"Query {i+1}", pill)
sql_text = (card.get("secured") or card.get("original") or "").strip()
st.markdown('<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("🔒 Seller revenue queries are not available to normal users. Please contact an admin.")
if br == "product_revenue_user_block":
st.warning("🔒 Product revenue queries are not available to normal users. Please contact an admin.")
if br == "platform_payments_user_block":
st.warning("🔒 Platform-wide payments are restricted to admins.")
if br == "rowcounts_all_tables_block":
st.warning("🔒 Requests for row counts across all tables / each table are restricted.")
if br == "write_block":
st.warning("🔒 Write/DDL operations are disabled.")
if br == "introspection_user_block":
st.warning("🔒 Listing tables/indexes/schema is admin-only.")
if br == "dump_all_block":
st.warning("🔒 Requests to list/dump all database data are restricted.")
if br == "count_user_block":
st.warning("🔒 Row-count (COUNT) queries are restricted for normal users.")
if br == "customers_user_block":
st.warning("🔒 Access to the **customers** table is restricted for normal users.")
if br == "destructive_request_block":
st.warning("🔒 Destructive actions (delete/update/drop) are not allowed.")
if card.get("policy_note"):
st.info(card["policy_note"])
if card.get("error"):
st.error(card["error"])
else:
df_to_show = card.get("df", None)
if isinstance(df_to_show, pd.DataFrame):
if card.get("limit_enforced"):
st.markdown(
'<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("No rows returned.")
else:
render_df_with_pagination(df_to_show, key_prefix=f"q{i}", page_size=10)
else:
st.info("No tabular result to display.")
if isinstance(card.get("sql_time"), (int, float)):
st.caption(f"⏱️ SQL time: {card['sql_time']:.3f}s")
def render_df_with_pagination(df: pd.DataFrame, key_prefix: str, page_size: int = 10):
total = len(df)
if total <= page_size:
st.dataframe(df, use_container_width=True)
st.caption(f"Showing 1–{total} of {total} rows.")
return
total_pages = math.ceil(total / page_size)
page = st.number_input(
"Page",
min_value=1, max_value=total_pages, value=1, step=1,
key=f"{key_prefix}_page"
)
start = (page - 1) * page_size
end = min(start + page_size, total)
st.dataframe(df.iloc[start:end], use_container_width=True)
st.caption(f"Showing rows {start+1}–{end} of {total} (page {page}/{total_pages}).")
def _apply_preview_limit(sql: str, limit: int = 10) -> str:
"""
Ensure the SQL has LIMIT <= limit for SELECT previews.
If non-SELECT, return as-is.
"""
s = (sql or "").strip().rstrip(";")
if not re.match(r"^\s*select\b", s, flags=re.I):
return s
m = re.search(r"\blimit\s+(\d+)\b", s, flags=re.I)
if m:
try:
current = int(m.group(1))
if current > limit:
s = re.sub(r"\blimit\s+\d+\b", f"LIMIT {limit}", s, flags=re.I)
except Exception:
s = f"{s} LIMIT {limit}"
else:
s = f"{s} LIMIT {limit}"
return s
# --- IST date helpers & audit DAO -------------------------------------------
from datetime import datetime, date
try:
from zoneinfo import ZoneInfo
except ImportError:
from backports.zoneinfo import ZoneInfo # only if needed
IST = ZoneInfo("Asia/Kolkata")
def ist_today() -> date:
return datetime.now(IST).date()
def _date_yyyymmdd(d: date) -> str:
return d.strftime("%Y-%m-%d")
def count_login_audit_range(start_d: date, end_d: date) -> int:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
_apply_busy_timeout(conn)
cur = conn.execute(
"""
SELECT COUNT(*)
FROM login_audit
WHERE date(created_at) BETWEEN ? AND ?
""",
(_date_yyyymmdd(start_d), _date_yyyymmdd(end_d)),
)
row = cur.fetchone()
return int(row[0] if row else 0)
def get_login_audit_range(start_d: date, end_d: date, offset: int = 0, limit: int = 10) -> pd.DataFrame:
with sqlite3.connect(DB_PATH, timeout=SQL_TIMEOUT) as conn:
_apply_busy_timeout(conn)
sql = """
SELECT
id AS audit_id,
created_at AS ts,
email,
user_id,
success,
reason
FROM login_audit
WHERE date(created_at) BETWEEN ? AND ?
ORDER BY datetime(created_at) DESC
LIMIT ? OFFSET ?;
"""
return pd.read_sql_query(
sql, conn,
params=(_date_yyyymmdd(start_d), _date_yyyymmdd(end_d), int(limit), int(offset))
)
# --- You already have: classify_sentiment_and_intent, insert_chat_history,
# render_feedback_controls, admin utils, etc. Keeping that logic intact.
# (Your existing functions are below, unchanged.)
# -----------------------------
# Startup
# -----------------------------
try:
dbg("Starting bootstrap_db_once()")
_init_status = bootstrap_db_once() # cached, runs once per process
dbg(f"DB init bootstrap status: {_init_status}")
schema_registry = load_schema_registry()
dbg("Schema loaded")
except Exception as e:
st.error("Startup failed.")
st.exception(e)
st.stop()
# -----------------------------
# Auth Gate
# -----------------------------
if "auth" not in st.session_state:
st.session_state["auth"] = None
if st.session_state.get("auth") is None:
if USE_BRANDING:
ui_header(None)
st.info("Login required")
auth_obj = simple_login_screen()
if auth_obj:
st.session_state["auth"] = auth_obj
st.rerun()
st.stop()
auth = st.session_state["auth"]
if USE_BRANDING:
ui_header(auth)
with st.sidebar:
st.success(f"Signed in as {auth['email']} ({auth['role']})")
if auth["role"] == "user":
st.caption(f"customer_id: {auth['customer_id']}")
if st.button("Sign out"):
st.session_state["auth"] = None
st.rerun()
def ensure_rag_published_schema(conn: sqlite3.Connection):
# Minimal ledger to prevent re-publishing the same chat row to RAG.
# Stores an arbitrary primary key string for the source row.
conn.execute("""
CREATE TABLE IF NOT EXISTS rag_published (
pub_key TEXT PRIMARY KEY,
created_at TEXT DEFAULT CURRENT_TIMESTAMP
);
""")
try:
conn.execute("CREATE INDEX IF NOT EXISTS idx_rag_pub_created ON rag_published(created_at);")
except Exception:
pass
conn.commit()
def mark_published(pub_key: str):
if not pub_key:
return
try:
with sqlite3.connect(DB_PATH, timeout=DB_TIMEOUT, check_same_thread=False) as _conn:
_conn.execute("INSERT OR IGNORE INTO rag_published (pub_key) VALUES (?)", (str(pub_key),))
_conn.commit()
except Exception as e:
logger.warning("mark_published failed for key=%s: %s", pub_key, e)
def publish_exemplar_from_row(row: dict, registry: dict, policy_version: str = "v1"):
try:
intent = row.get("intent") or "unknown"
question = row.get("user_input") or ""
plan_after = row.get("plan_after") or row.get("llm_plan") or ""
sql_after = row.get("sql_after") or row.get("secured_sql") or ""
sql_after = _ensure_sql_string(sql_after)
schema_fp = _schema_fingerprint(registry)
# make a simple, deterministic tables_used string
sql_lc = (sql_after or "").lower()
used = sorted([t for t in registry.keys() if t.lower() in sql_lc])
tables_used_str = ",".join(used) # <-- string, not list
ex_text = exemplar_text(
intent,
question,
schema_fp,
plan_after if isinstance(plan_after, str) else json.dumps(plan_after, ensure_ascii=False),
sql_after
)
ex_id = hashlib.sha256(
(intent + "|" + canonicalize_sql(sql_after) + "|" + schema_fp).encode("utf-8")
).hexdigest()[:24]
metadata = {
"intent": intent, # str
"schema_fp": schema_fp, # str
"tables_used": tables_used_str, # <-- now str
"policy_version": policy_version, # str
"created_at": datetime.utcnow().isoformat(), # str
}
RAG.upsert_exemplar(ex_id, ex_text, metadata)
return ex_id
except Exception as e:
logger.exception("Failed to publish exemplar: %s", e)
return None
def admin_rag_publisher(registry: dict):
#st.header("RAG Publisher (Admin)")
st.caption("Publish vetted exemplars into the retrieval store to improve future SQL generation.")
with st.expander("Build from recent positive chats", expanded=True):
try:
STOP_INTENTS = {"help_greeting", "thanks", "farewell", "smalltalk", "joke", "chitchat"}
# ---------- 1) Discover chat_history schema ----------
with get_conn() as conn:
cols_df = pd.read_sql_query("PRAGMA table_info(chat_history);", conn)
# Ensure rag_published exists
ensure_rag_published_schema(conn)
cols = set(c.lower() for c in cols_df["name"].astype(str))
# Core columns (fallbacks)
if "id" in cols:
id_col_expr = "id"
elif "chat_id" in cols:
id_col_expr = "chat_id"
else:
id_col_expr = "rowid"
id_select = f"CAST({id_col_expr} AS TEXT) AS pub_key"
ts_candidates = ["created_at", "createdAt", "timestamp", "ts", "time"]
ts_col = next((c for c in ts_candidates if c.lower() in cols), None)
fb_col = "feedback_value" if "feedback_value" in cols else ("feedback" if "feedback" in cols else None)
if fb_col is None:
st.warning("No feedback column found in chat_history; cannot filter positives.")
st.stop()
# Only positives
fb_pred = f"{fb_col} = 1" if fb_col == "feedback_value" else f"LOWER({fb_col}) IN ('up','thumbs_up','positive','1','true','yes')"
# SQL column
sql_col = "secured_sql" if "secured_sql" in cols else ("sql" if "sql" in cols else None)
if sql_col is None:
st.warning("No SQL column (secured_sql/sql) found in chat_history.")
st.stop()
# Optional columns
user_id_col = "user_id" if "user_id" in cols else None
intent_col = "intent" if "intent" in cols else None
question_col = "user_input" if "user_input" in cols else ("input_text" if "input_text" in cols else ("question" if "question" in cols else None))
# ---------- 2) Filters ----------
colA, colB, colC = st.columns([1,1,1])
with colA:
end_date = st.date_input("End date", datetime.utcnow().date())
with colB:
start_date = st.date_input("Start date", end_date - timedelta(days=30))
with colC:
max_rows = st.number_input("Max rows", min_value=10, max_value=1000, value=200, step=10)
intent_like = st.text_input("Filter by intent (optional, substring match)", "")
# ---------- 3) Build SELECT + WHERE (exclude empty SQL & chit-chat intents & already-published) ----------
select_cols = [id_select]
if ts_col: select_cols.append(ts_col)
if user_id_col: select_cols.append(user_id_col)
if intent_col: select_cols.append(intent_col)
if question_col: select_cols.append(question_col)
select_cols.append(sql_col)
select_list = ", ".join(select_cols)
where = [fb_pred]
# Exclude empty/[]/null SQL
where.append(f"TRIM(COALESCE({sql_col}, '')) <> ''")
where.append(f"LOWER(TRIM(COALESCE({sql_col}, ''))) NOT IN ('[]','null','none')")
params = []
if ts_col:
where.append(f"{ts_col} BETWEEN ? AND ?")
params.extend([f"{start_date} 00:00:00", f"{end_date} 23:59:59"])
if intent_col:
placeholders = ",".join(["?"] * len(STOP_INTENTS))
where.append(f"LOWER({intent_col}) NOT IN ({placeholders})")
params.extend([s.lower() for s in STOP_INTENTS])
if intent_like.strip() and intent_col:
where.append(f"{intent_col} LIKE ?")
params.append(f"%{intent_like.strip()}%")
where_sql = " AND ".join(where)
order_sql = f"ORDER BY {ts_col} DESC" if ts_col else "ORDER BY rowid DESC"
base_sql = f"""
SELECT {select_list}
FROM chat_history ch
WHERE {where_sql}
AND NOT EXISTS (
SELECT 1 FROM rag_published rp
WHERE rp.pub_key = CAST({id_col_expr} AS TEXT)
)
{order_sql}
LIMIT ?
"""
params.append(int(max_rows))
with get_conn() as conn:
df = pd.read_sql_query(base_sql, conn, params=params)
# ---------- 4) Extra Python-side safety ----------
if not df.empty:
def _has_sql(x: str) -> bool:
s = str(x or "").strip().lower()
return len(s) > 0 and s not in {"[]", "null", "none"}
df = df[df[sql_col].map(_has_sql)]
if intent_col and intent_col in df.columns:
df = df[~df[intent_col].astype(str).str.lower().isin(STOP_INTENTS)]
if df.empty:
st.success("No eligible positive chats to publish (after filters and dedup).")
st.stop()
# ---------- 5) Selectable grid (max 50) ----------
preview_cols = []
for c in ["pub_key", ts_col, user_id_col, intent_col, question_col, sql_col]:
if c and c in df.columns:
preview_cols.append(c)
view = df[preview_cols].copy()
def _ui_safe_cell_local(x):
import json
try:
if isinstance(x, (dict, list)):
return json.dumps(x, ensure_ascii=False)
return str(x)
except Exception:
return str(x)
view = view.applymap(_ui_safe_cell_local)
view.insert(0, "Select", False)
st.caption("Tick up to 50 rows to publish.")
edited = st.data_editor(
view.head(200),
use_container_width=True,
hide_index=True,
key="rag_pos_editor",
column_config={"Select": st.column_config.CheckboxColumn(required=False)},
)
selected_rows = edited[edited["Select"]]
sel_count = int(len(selected_rows))
st.caption(f"Selected: {sel_count} (max 50)")
disabled = sel_count == 0 or sel_count > 50
if st.button("Publish selected → RAG", key="btn_pub_selected", type="primary", disabled=disabled, use_container_width=True):
ok = 0
skipped = 0
for _, row in selected_rows.iterrows():
payload = {
"intent": row.get(intent_col, "") if intent_col else "",
"user_input": row.get(question_col, "") if question_col else "",
"llm_plan": row.get("llm_plan", ""),
"plan_after": row.get("llm_plan", ""),
"secured_sql": row.get(sql_col, ""),
"sql_after": row.get(sql_col, ""),
}
# Ensure plain string SQL
sql_after = payload.get("sql_after") or ""
if not isinstance(sql_after, str):
try:
import json as _json
sql_after = _json.dumps(sql_after, ensure_ascii=False)
except Exception:
sql_after = str(sql_after)
payload["sql_after"] = sql_after
try:
ex_id = publish_exemplar_from_row(payload, registry)
if ex_id:
ok += 1
pk = str(row.get("pub_key") or "")
mark_published(pk)
else:
skipped += 1
except Exception as e:
skipped += 1
logger.exception("publish error: %s", e)
st.success(f"Published {ok} exemplars to RAG. Skipped {skipped}.")
st.rerun()
except Exception as e:
st.warning(f"Render error in 'positive chats' panel: {e}")
with st.expander("Manual publish from a specific chat row", expanded=False):
try:
# Discover columns
with get_conn() as conn:
cols_df = pd.read_sql_query("PRAGMA table_info(chat_history);", conn)
cols = set(str(n).lower() for n in cols_df["name"].astype(str))
# Candidate lookup keys, in priority order
candidates = ["conversation_id", "conv_id", "session_id", "thread_id", "message_id", "turn_id", "id"]
avail_keys = [c for c in candidates if c in cols]
# rowid fallback if none of the above exist
use_rowid = False
if not avail_keys:
avail_keys = ["rowid"]
use_rowid = True
# Pick a key and input value
key_col = st.selectbox("Lookup key column", options=avail_keys, index=0)
key_val = st.text_input("Key value (exact match)")
# Timestamp / ordering column (optional)
ts_candidates = ["created_at", "createdAt", "timestamp", "ts", "time"]
ts_col = next((c for c in ts_candidates if c.lower() in cols), None)
order_sql = f"ORDER BY {ts_col} DESC" if ts_col else "ORDER BY rowid DESC"
if st.button("Fetch & Publish exemplar", key="btn_pub_one_row", disabled=not key_val):
# Build query safely
where_col = "rowid" if (use_rowid and key_col == "rowid") else key_col
select_list = "*"
base_sql = f"SELECT {select_list} FROM chat_history WHERE {where_col} = ? {order_sql} LIMIT 1"
with get_conn() as conn:
df_one = pd.read_sql_query(base_sql, conn, params=[key_val])
if df_one.empty:
st.warning("No row found for that key/value.")
else:
row = df_one.iloc[0].to_dict()
# Prepare payload for publisher (flexible keys)
payload = {
"intent": row.get("intent", ""),
"user_input": row.get("user_input", row.get("question", "")),
"llm_plan": row.get("llm_plan", ""),
"plan_after": row.get("llm_plan", ""),
"secured_sql": row.get("secured_sql", row.get("sql", "")),
"sql_after": row.get("secured_sql", row.get("sql", "")),
}
ex_id = publish_exemplar_from_row(payload, registry)
if ex_id:
st.success(f"Published exemplar {ex_id}")
else:
st.error("Failed to publish exemplar.")
except Exception as e:
st.warning(f"Manual publish error: {e}")
# -----------------------------
# 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'))
if ask_clicked:
st.session_state["__clear_input__"] = True
limited, rl_msg = check_rate_limit(auth["user_id"], auth["role"])
if limited:
st.warning(rl_msg)
else:
st.caption(f"Rate limit status: {rl_msg}")
# Detect & translate input to English (if Indic)
src_lang = _detect_lang(q) # 'en'|'hi'|'mr'
logger.info("[chat] src_lang=%s user_text=%s", src_lang, _sample(q, 200))
q_for_llm = _tx_to_en(q, src_lang)
st.session_state['last_chat_out'] = None
llm = get_llm_client(LLM_CHOICE)
# ---- chat memory: fetch last 10 turns and inject into prompt ----
try:
history = _fetch_recent_chat_messages(conn, user_id, limit=10)
st.session_state["__last_history__"] = history # optional: for debugging
logger.debug(f"[mem] using {len(history) if history else 0} history turns")
except Exception as e:
history = []
logger.debug(f"[mem] fetch history failed at call-site: {e}")
history_block = _format_history_for_llm(history)
# Inject last-10-turn conversational memory into all LLM calls
try:
llm = _wrap_llm_with_memory(llm)
except Exception as _e:
logger.debug("[mem] wrap failed: %s", _e)
# >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
# 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"))
# 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'+ (f"\nCHAT HISTORY (most recent first shows last):\n{history_block}\n" if history_block else "") + q_for_llm
logger.debug(f"[mem] injected history chars={len(history_block)}")
logger.info("[chat] src_lang=%s user_text=%s", src_lang, _sample(q, 200))
logger.info("[chat] Value of NON_SQL_ROUTER=%s ", NON_SQL_ROUTER)
logger.info("[chat] Value of bypass_main_logic=%s", bypass_main_logic)
logger.info("[chat] Value of q is =%s", q)
logger.info("[chat] Value of _is_my_account_query(q)=%s", _is_my_account_query(q))
if NON_SQL_ROUTER and not _is_my_account_query(q) and not bypass_main_logic:
non_sql_label = detect_non_sql_intent(q)
logger.info("[chat] non_sql_label=%s", non_sql_label)
if non_sql_label:
# 1) sentiment/intent (kept for MIS)
try:
sentiment, intent = classify_sentiment_and_intent(get_llm_client(LLM_CHOICE), q)
except Exception:
sentiment, intent = ("neutral", non_sql_label)
# 2) Build an English base message, then translate safely if needed
base_msg_en = respond_non_sql(non_sql_label, src_lang="en")
final_msg = _tx_safe(base_msg_en, src_lang) # translate only once, safely
logger.info("[chat] non_sql_label -> base_msg_en=%s", base_msg_en)
if src_lang and src_lang != "en":
logger.info("[chat] non_sql_label -> final_msg(translated)=%s", final_msg)
# 3) Prepare minimal 'out' so the UI renders a friendly message table
out = {
"intent": ("neutral", non_sql_label),
"sentiment": sentiment,
"text": final_msg, # localized via _tx_safe
"text_en": base_msg_en,
"queries": [], # <-- nothing to render in grid
"plan": {},
"secured_sql": None,
"cards": []
}
# 4) Log chat to DB so feedback works
try:
chat_id = insert_chat_history(
user_id=auth["user_id"],
role=auth["role"],
customer_id=auth.get("customer_id"),
user_input=q, # original text (could be Indic)
llm_plan=out["plan"],
secured_sql=[], # no SQL for routed replies
sentiment=sentiment,
intent=intent,
llm_time=0.0,
)
st.session_state.last_chat_id = chat_id
except Exception as e:
logger.warning("failed to log routed chat: %s", e)
st.session_state.last_chat_id = None
# 5) Render and stop before the SQL path runs
st.session_state['last_chat_out'] = out
st.session_state['last_query_text'] = q
st.session_state.last_sentiment = sentiment
st.session_state.last_intent = intent
render_chat_output(out)
render_feedback_controls(st.session_state.get("last_chat_id"))
# 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)
# 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")
fb_logger.info("[rate-tab] opened user_id=%s role=%s", auth["user_id"], auth["role"])
# 1) Load recent and keep only UNRATED (feedback is None/NaN)
my_recent = get_recent_chats_for_user(auth["user_id"], limit=100)
fb_logger.info("[rate-tab] fetched recent=%d", len(my_recent))
if "feedback" not in my_recent.columns:
fb_logger.error("[rate-tab] feedback column missing in my_recent columns=%s", list(my_recent.columns))
st.error("Internal error: feedback column missing.")
st.stop()
unrated = my_recent[my_recent["feedback"].isna()].copy()
fb_logger.info("[rate-tab] unrated=%d rated=%d", len(unrated), len(my_recent) - len(unrated))
if unrated.empty:
st.success("No conversations pending feedback. 🙌")
st.caption("Rated conversations are hidden from this list.")
st.stop()
unrated = unrated.reset_index(drop=True)
def _label(row):
text = (row["user_input"] or "").strip().replace("\n", " ")
if len(text) > 70:
text = text[:67] + "…"
ts = row.get("created_at", "")
fid = int(row["chat_id"])
return f"#{fid} • {ts} • {text}"
options = list(unrated.index)
labels = {i: _label(unrated.loc[i]) for i in options}
idx = st.selectbox(
"Pick a conversation to rate:",
options=options,
format_func=lambda i: labels[i],
key="rate_prev_select",
)
sel = unrated.loc[idx]
chat_id_prev = int(sel["chat_id"])
st.caption(f"Selected chat_id: {chat_id_prev}")
fb_logger.info("[rate-tab] selected chat_id=%s", chat_id_prev)
with st.expander("Show original question"):
st.write(sel["user_input"])
# ✅ Keep this INSIDE the tab block (indented)
preview_clicked = st.button("🔎 Preview result (validate + LIMIT 10 + execute)",
key=f"preview_{chat_id_prev}")
if preview_clicked:
fb_logger.info("[rate-tab] preview clicked chat_id=%s", chat_id_prev)
try:
fb_logger.info("[rate-tab] calling _json_list for chat_id=%s", chat_id_prev)
sqls = _json_list(sel.get("secured_sql"))
# Fallback to llm_plan if needed
if not sqls:
st.info("No SQL was recorded for this conversation.")
else:
if not SHOW_DETAILS:
st.subheader("Preview result (executed)")
else:
st.subheader("SQL Preview (executed)")
for i, raw_sql in enumerate(sqls[:2], start=1):
if SHOW_DETAILS:
st.markdown(f"**Query {i}**")
try:
fb_logger.debug("[rate-tab] validating q#%s", i)
user_text_for_guard = (sel.get("user_input") or "").strip()
validated = validate_sql(
raw_sql,
auth["role"],
auth.get("customer_id"),
schema_registry,
user_text_for_guard,
)
secured = _ensure_sql_string(validated)
try:
if not isinstance(validated, str):
fb_logger.debug("[rate-tab] validate_sql returned %s; coerced to string len=%d",
type(validated).__name__, len(secured))
except Exception:
pass
secured_limited = _apply_preview_limit(secured, limit=10)
fb_logger.debug("[rate-tab] executing q#%s", i)
df, rows, kind = run_sql_generic(secured_limited)
if SHOW_DETAILS:
st.code(secured_limited, language="sql")
if kind == "select":
try:
import pandas as pd
except Exception:
pass
df_to_show = df if 'pd' in globals() and isinstance(df, pd.DataFrame) else _as_dataframe_safely(df)
st.dataframe(df_to_show, use_container_width=True)
try:
st.caption(f"Returned {len(df_to_show)} row(s).")
except Exception:
pass
else:
st.info(f"Non-SELECT statement; rows affected: {rows}")
except SqlTimeoutError as te:
fb_logger.warning("[rate-tab] timeout q#%s chat_id=%s err=%s", i, chat_id_prev, te)
st.error(f"Timed out while executing Query {i}: {te}")
except Exception as e:
fb_logger.exception("[rate-tab] error executing q#%s chat_id=%s", i, chat_id_prev)
st.error(f"Error while executing Query {i}.")
st.exception(e)
except SqlTimeoutError as te:
fb_logger.warning("[rate-tab] preview timeout chat_id=%s err=%s", chat_id_prev, te)
st.error(f"Timed out while preparing preview: {te}")
except Exception as e:
fb_logger.exception("[rate-tab] preview failed chat_id=%s", chat_id_prev)
st.error("Preview failed due to an error.")
st.exception(e)
existing = get_chat_feedback(chat_id_prev)
fb_logger.info("[rate-tab] existing feedback for chat_id=%s -> %s", chat_id_prev, existing)
c1, c2 = st.columns(2)
with c1:
up = st.button("👍 Mark helpful", key=f"fb_prev_up_{chat_id_prev}", disabled=(existing is not None))
with c2:
dn = st.button("👎 Mark not helpful", key=f"fb_prev_dn_{chat_id_prev}", disabled=(existing is not None))
if existing is not None:
st.info("Feedback already recorded for this conversation."
+ (" (👍 Helpful)" if existing == 1 else " (👎 Not helpful)"))
else:
if up or dn:
value = 1 if up else 0
fb_logger.info("[rate-tab] submit feedback chat_id=%s value=%s", chat_id_prev, value)
ok, msg = update_chat_feedback_if_owner(chat_id_prev, auth["user_id"], value)
fb_logger.info("[rate-tab] update_chat_feedback_if_owner -> ok=%s msg=%s", ok, msg)
(st.success if ok else st.error)(msg)
# Clear caches so the just-rated chat disappears immediately
try:
get_recent_chats_for_user.clear() # if cached via st.cache_data
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:
logger.exception("Failed load schema registry : %s", e)
reg = load_schema_registry(os.path.join(DATA_DIR, SCHEMA_FILE))
admin_rag_publisher(reg)
# ==============================
# POST-PATCH HELPERS (non-invasive)
# These are appended to preserve all existing logic and only add safety + compatibility.
# ==============================
def _ensure_llm_chat(llm):
"""
Adapter that ensures the returned object exposes a .chat(messages=[...]) method.
Supported shapes:
- An object that already has .chat(messages)
- A callable LLM: llm(messages) -> str or dict
- OpenAI 'client' with .chat.completions.create(...) or .responses.create(...)
"""
try:
# If llm already has .chat, just return it.
if hasattr(llm, "chat") and callable(getattr(llm, "chat")):
return llm
class _LLMChatAdapter:
def __init__(self, base):
self._base = base
def chat(self, messages):
# 1) If base is callable -> call directly
if callable(self._base):
out = self._base(messages)
# Normalize
if isinstance(out, str):
return {"choices":[{"message":{"content":out}}]}
if isinstance(out, dict):
return out
return {"choices":[{"message":{"content":str(out)}}]}
# 2) If looks like OpenAI client v1 with chat.completions.create
chat_api = getattr(getattr(self._base, "chat", None), "completions", None)
if chat_api and hasattr(chat_api, "create"):
try:
resp = chat_api.create(model=getattr(self._base, "model", None) or "gpt-4o-mini", messages=messages)
# Try to normalize to OpenAI-like dict
if isinstance(resp, dict):
return resp
# SDK objects typically have .choices[0].message.content
msg = None
try:
msg = resp.choices[0].message.content
except Exception:
pass
return {"choices":[{"message":{"content": msg or ""}}]}
except Exception as e:
return {"choices":[{"message":{"content": f"ERROR: {e}"}}]}
# 3) If looks like OpenAI 'responses' API
responses_api = getattr(self._base, "responses", None)
if responses_api and hasattr(responses_api, "create"):
try:
prompt = ""
for m in messages or []:
role = m.get("role","user")
content = m.get("content","")
prompt += f"{role.upper()}: {content}\n"
resp = responses_api.create(model=getattr(self._base, "model", None) or "gpt-4o-mini", input=prompt.strip())
# Try to normalize
text = None
try:
text = resp.output_text # openai>=1.0
except Exception:
pass
if text is None:
try:
# fallback through content parts
text = ""
for p in (getattr(resp, "output", None) or []):
if getattr(p, "type", "") == "message":
for b in getattr(p, "content", []):
if getattr(b, "type", "") == "text":
text += getattr(getattr(b, "text", None), "value", "") or ""
except Exception:
pass
return {"choices":[{"message":{"content": text or ""}}]}
except Exception as e:
return {"choices":[{"message":{"content": f"ERROR: {e}"}}]}
# Unknown shape
return {"choices":[{"message":{"content":"ERROR: LLM object does not support chat()"}}]}
return _LLMChatAdapter(llm)
except Exception as e:
class _FallbackChat:
def chat(self, messages):
return {"choices":[{"message":{"content": f"ERROR: {e}"}}]}
return _FallbackChat()
def _extract_message_text(resp):
"""Extract 'assistant' text from OpenAI-like responses safely."""
try:
if isinstance(resp, str):
return resp
if isinstance(resp, dict):
# OpenAI chat
ch = resp.get("choices", [])
if ch:
msg = ch[0].get("message") or {}
if isinstance(msg, dict):
return msg.get("content","") or ""
# Fallback
return resp.get("text","") or ""
except Exception:
pass
return ""
def _llm_chat(llm, messages):
"""
Universal chat invocation that normalizes outputs.
Returns an OpenAI-like dict: {"choices":[{"message":{"content": "<text>"}}]}
"""
try:
llm2 = _ensure_llm_chat(llm)
return llm2.chat(messages)
except Exception as e:
return {"choices":[{"message":{"content": f"ERROR: {e}"}}]}
def _fetch_recent_chat_messages(conn, user_id, limit=10):
"""
Returns [{"role":"user"/"assistant","content":"..."}] built from chat_history (+ optionally chat_turn_outputs).
Robust to column name differences; if something is missing, it skips gracefully.
"""
out = []
try:
cur = conn.cursor()
# Try to locate likely column names
# chat_history
cur.execute("PRAGMA table_info(chat_history)")
cols = [r[1] for r in cur.fetchall()]
user_col = "user_id" if "user_id" in cols else ("uid" if "uid" in cols else None)
role_col = "role" if "role" in cols else None
text_col = "user_text" if "user_text" in cols else ("text" if "text" in cols else None)
resp_col = "explanation_display" if "explanation_display" in cols else ("assistant_text" if "assistant_text" in cols else None)
created_col = "created_at" if "created_at" in cols else ("ts" if "ts" in cols else None)
if user_col and (text_col or resp_col):
q = f"SELECT {role_col or 'NULL as role'}, {text_col or 'NULL as user_text'}, {resp_col or 'NULL as assistant_text'} FROM chat_history WHERE {user_col} = ? ORDER BY {created_col or 'rowid'} DESC LIMIT ?"
for role, user_text, assistant_text in cur.execute(q, (user_id, limit)):
if user_text:
out.append({"role": "user", "content": str(user_text)})
if assistant_text:
out.append({"role": "assistant", "content": str(assistant_text)})
# chat_turn_outputs (optional, add a few more assistant messages)
cur.execute("PRAGMA table_info(chat_turn_outputs)")
cols2 = [r[1] for r in cur.fetchall()]
if cols2:
cid_col = "chat_id" if "chat_id" in cols2 else None
out_col = "assistant_text" if "assistant_text" in cols2 else ("output_text" if "output_text" in cols2 else None)
if out_col:
q2 = f"SELECT {out_col} FROM chat_turn_outputs ORDER BY rowid DESC LIMIT ?"
for (assistant_text,) in cur.execute(q2, (max(0, limit//2),)):
if assistant_text:
out.append({"role":"assistant","content":str(assistant_text)})
except Exception as e:
# Log-friendly but non-fatal
try:
print(f"[mem] fetch history failed: {e}")
except Exception:
pass
# Keep only the last `limit*2` messages to avoid blowing prompt
return out[-limit*2:]
def _wrap_llm_with_memory(llm, *, user_id=None, messages=None, conn=None, limit=10):
"""
Universal wrapper that tolerates older call-sites.
_wrap_llm_with_memory(llm, messages=[...], user_id="U_xxx")
_wrap_llm_with_memory(llm, user_id="U_xxx", messages=messages)
Falls back gracefully if anything fails.
"""
try:
msgs = list(messages or [])
if user_id and conn is not None:
history = _fetch_recent_chat_messages(conn, user_id, limit=limit) or []
# prepend history if current messages look empty
if not msgs:
msgs = history
else:
msgs = history + msgs
# Ensure we never pass empty messages to an LLM
if not msgs:
msgs = [{"role":"user","content":"Hello"}]
return _llm_chat(llm, msgs)
except Exception as e:
return {"choices":[{"message":{"content": f"ERROR: {e}"}}]}
def classify_destructive_intent_with_llm(llm, text):
"""Safe version: never crashes and returns False if unsure."""
try:
prompt = [
{"role":"system","content":"You label whether the user's request is destructive or attempts schema/infra access."},
{"role":"user","content": f"Text:\n{text}\n\nAnswer with one word only: ALLOW or BLOCK."}
]
resp = _llm_chat(llm, prompt)
label = (_extract_message_text(resp) or "").strip().splitlines()[-1].strip().lower()
return label == "block"
except Exception as e:
try:
print(f"[llm] destructive-intent classifier failed: {e}")
except Exception:
pass
return False
# (Optional) tiny SQL sanitizer to avoid executing accidental non-SQL strings
def _sanitize_sql(sql_text: str) -> str:
try:
t = (sql_text or "").strip().lower()
if not t:
return "SELECT 1 WHERE 0"
# If looks obviously not-SQL or an error message, neutralize
suspicious = ["error:", "llm object does not support chat", "invalid 'messages'", "context:", "openaierror"]
if any(s in t for s in suspicious):
return "SELECT 1 WHERE 0"
except Exception:
return "SELECT 1 WHERE 0"
return sql_text
# --- FIX v2: logger dedupe + run-once guards (26-Sep-2025) ---
# === LLM compatibility helpers ===
# =====================
# Chat LLM compatibility shims (robust)
# =====================
class _LLMChatAdapter:
"""Adapter that exposes a .chat(messages) method for any supported LLM spec."""
def __init__(self, spec):
self._spec = spec
def chat(self, messages):
return _llm_chat(self._spec, messages)
class _DictChatShim(dict):
"""A dict that also supports .chat(messages) by delegating to _llm_chat."""
def chat(self, messages):
return _llm_chat(dict(self), messages)
# === PATCH: robust LLM wrappers (override previous definitions) ========================
# ---- BEGIN ROBUST LLM WRAPPERS (drop-in) ------------------------------------
def _wrap_text_response(text: str):
"""Normalize plain text into an OpenAI-like chat response dict."""
return {
"choices": [
{
"message": {
"role": "assistant",
"content": text if text is not None else ""
}
}
]
}
def _extract_content_from_response(resp):
"""
Try to extract assistant text from many possible response shapes:
- OpenAI ChatCompletion (dict-like): resp["choices"][0]["message"]["content"]
- OpenAI Responses API: resp.output_text or resp["output_text"]
- Plain string
- Objects with .choices[0].message.content
Always return a string.
"""
try:
if resp is None:
return ""
# Plain string
if isinstance(resp, str):
return resp
# Dict-like
if isinstance(resp, dict):
# Chat Completions shape
ch = resp.get("choices")
if ch and isinstance(ch, list):
first = ch[0] if ch else {}
msg = first.get("message") if isinstance(first, dict) else None
if isinstance(msg, dict):
return msg.get("content", "") or ""
# Some providers put text directly under 'text'
if "text" in first:
return first.get("text") or ""
# Responses API (aggregated)
if "output_text" in resp:
return resp.get("output_text") or ""
# Some providers: {"content": "..."}
if "content" in resp and isinstance(resp["content"], str):
return resp["content"]
# Objects (OpenAI SDK v1 style or other providers)
# - responses.create(...) returns a Response object with .output_text
txt = getattr(resp, "output_text", None)
if isinstance(txt, str) and txt:
return txt
# - chat.completions.create(...) returns an object with .choices[0].message.content
choices = getattr(resp, "choices", None)
if isinstance(choices, list) and choices:
message = getattr(choices[0], "message", None)
if message is not None:
content = getattr(message, "content", None)
if isinstance(content, str):
return content
# sometimes text is in choices[0].text
text_attr = getattr(choices[0], "text", None)
if isinstance(text_attr, str):
return text_attr
# Fallback: try resp.content
content = getattr(resp, "content", None)
if isinstance(content, str):
return content
except Exception:
pass
return ""
def _llm_chat(llm, messages, **kwargs):
"""
Universal chat invoker. NEVER raises. Tries many backends: