Notes

def load_account_data():
"""Fetch accounts dataset from Snowflake using external SQL file."""
try:
logger.info("Loading account data from Snowflake...")
token = get_token()
if not token:
raise ValueError("No token retrieved from Azure AD")
secrets = get_secrets()
conn = snowflake.connector.connect(
account=secrets["snowflake_account"],
authenticator="oauth",
token=token,
role=secrets["snowflake_role"],
warehouse=secrets["snowflake_warehouse"],
database=secrets["snowflake_database"],
schema=secrets["snowflake_schema"],
)
logger.info("Connected to Snowflake for data loading")
# Read SQL query from file
query_path = Path("account_data_query.sql")
with open(query_path, "r", encoding="utf-8") as f:
query = f.read()
# Execute query and fetch data
df = pd.read_sql(query, conn)
logger.info(f"Loaded {len(df)} rows from Snowflake")
conn.close()
return df
except Exception as e:
logger.error(f"Error loading account data: {e}")
return pd.DataFrame()


# Global data store (initialized once)
APP_DATA = {}


def img_to_base64(image_path):
"""Converts an image to a Base64 string for embedding in HTML."""
try:
from pathlib import Path
import base64

img_bytes = Path(image_path).read_bytes()
encoded = base64.b64encode(img_bytes).decode()
return f"data:image/png;base64,{encoded}"
except FileNotFoundError:
logger.warning(f"Image not found: {image_path}")
return ""
except Exception as e:
logger.error(f"Error encoding image {image_path}: {e}")
return ""


# ------- TOKEN FETCH -------
def get_token() -> str:
"""Fetch OAuth 2.0 token from Azure AD for Snowflake using Client Credentials flow.
This is hardened to avoid raising unexpected exceptions that would break the Dash callback.
"""
try:
# Values from your Client App Registration
tenant_id = "de08c407-19b9-427d-9fe8-edf254300ca7"
client_app_client_id = "ad127379-3e50-407b-9869-1e789bc80df5"
client_app_client_secret = "5c.8Q~jOk2-y6IxWaPmdJLJCVrl1NtWsdSRdMde5"

# Application ID URI (must match Snowflake's AUDIENCE config)
resource_app_id_uri = "api://82851444-b770-46dc-a787-7018785dd780"

# Token endpoint
url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"

# Scope must be Resource App URI + /.default
payload = {
"grant_type": "client_credentials",
"client_id": client_app_client_id,
"client_secret": client_app_client_secret,
"scope": f"{resource_app_id_uri}/.default",
}

headers = {"Content-Type": "application/x-www-form-urlencoded"}

response = requests.post(url, data=payload, headers=headers, timeout=15)

if response.status_code != 200:
logger.error(
f"Failed to fetch token: status={response.status_code} response={response.text}"
)
return ""

token = response.json().get("access_token")
if not token:
logger.error(f"No access_token in token response: {response.text}")
return ""

# Try to decode token for debugging but don't fail if decode fails
try:
decoded = jwt.decode(token, options={"verify_signature": False})
logger.debug(f"Decoded token claims: {decoded}")
except Exception as de:
logger.debug(f"Token decode skipped or failed: {de}")

return token

except Exception as e:
logger.error(f"Error while fetching OAuth token: {e}")
logger.debug(traceback.format_exc())
return ""


def initialize_resources():
"""Initialize Snowflake connection and load all data"""
try:
logger.info("Initializing all resources...")
# 1. Load account data
account_data = load_account_data()
if account_data.empty:
logger.error("Failed to load account data")
return {}
# 2. Initialize LLM client
llm_client = llama_model_client
# 3. Initialize Audit Manager
audit_manager = AuditManager()
# 4. Prepare filter data from actual account data
filter_data = {}
column_to_label = {}
for config in FILTER_CONFIG:
column = config["column"]
label = config["label"]
column_to_label[column] = label
if "options" in config:
# Use predefined options
filter_data[label] = config["options"]
elif column in account_data.columns:
# Get unique values from actual data
unique_vals = account_data[column].dropna().unique().tolist()
filter_data[label] = sorted([str(v) for v in unique_vals if v != ""])
else:
filter_data[label] = []
logger.warning(f"Column {column} not found in account data")
data = {
"account_data": account_data, # Store the actual data
"filter_data": filter_data,
"llm_client": llm_client,
"audit_manager": audit_manager,
"column_to_label": column_to_label,
"filter_config": FILTER_CONFIG,
}
logger.info("All resources initialized successfully")
return data
except Exception as e:
logger.error(f"Error initializing resources: {e}")
return {}


def encode_password(password: str) -> str:
"""Encodes the password using Base64."""
return base64.b64encode(password.encode()).decode()


def login_user(employee_id: str, password: str):
"""Send credentials to login API and return (success flag, message, emp_id)."""
payload = {
"emp_id": employee_id,
"password": encode_password(password),
"module": "Audience Builder",
}
try:
response = requests.post(LOGIN_API_URL, json=payload)
if response.status_code == 200:
data = response.json()
status = data.get("status", "")
if status.lower() == "failure":
return False, "Invalid credentials. Please try again.", None
else:
emp_id = data.get("emp_id", employee_id)
return True, "Login successful!", emp_id
else:
return False, f"Login failed (HTTP {response.status_code}).", None
except Exception as e:
logger.error(f"Exception in login_user for employee_id={employee_id}: {e}")
return False, f"Error connecting to server: {e}", None


def reset_password(employee_id: str, current_password: str, new_password: str):
"""Send reset password request and return (success flag, message)."""
payload = {
"emp_id": employee_id,
"current_password": encode_password(current_password),
"new_password": encode_password(new_password),
}
try:
response = requests.post(RESET_PASSWORD_API_URL, json=payload)
data = response.json() if response.status_code == 200 else None
if response.status_code == 200 and data:
status_msg = data.get("status", "")
if any(
word in status_msg.lower() for word in ["success", "updated", "changed"]
):
return True, "Password updated successfully!"
else:
return False, status_msg or "Password reset failed."
else:
return False, f"Failed to reset password (HTTP {response.status_code})."
except Exception as e:
logger.error(f"Unexpected error in reset_password: {e}")
return False, f"Error: {str(e)}"


def process_nlq_query(query_text: str, conn, llm_client, audit_manager, emp_id: str):
"""Process natural language query and return results"""
try:
logger.info(f"Processing NLQ query: {query_text}")
# Sanitize and convert NLQ to SQL
sanitized_query = nlq_sanitization(query_text)
sql_query = llamacall(llm_client, sanitized_query)
logger.info(f"Generated SQL: {sql_query}")
# Execute query
results_df = pd.read_sql(sql_query, conn)
# Log audit
audit_manager.log_query(
emp_id=emp_id,
query_text=query_text,
sql_query=sql_query,
result_count=len(results_df),
)
logger.info(f"Query returned {len(results_df)} results")
return results_df, sql_query, None
except Exception as e:
logger.error(f"Error processing NLQ query: {e}")
return None, None, str(e)


def process_manual_filters(filters: dict, conn, audit_manager, emp_id: str):
"""Process manual filters and return results"""
try:
logger.info(f"Processing manual filters: {filters}")
# Build SQL query from filters
sql_query = filter_logic.build_filter_query(filters)
logger.info(f"Generated SQL from filters: {sql_query}")
# Execute query
results_df = pd.read_sql(sql_query, conn)
# Log audit
filter_summary = ", ".join([f"{k}={v}" for k, v in filters.items() if v])
audit_manager.log_query(
emp_id=emp_id,
query_text=f"Manual Filters: {filter_summary}",
sql_query=sql_query,
result_count=len(results_df),
)
logger.info(f"Filters returned {len(results_df)} results")
return results_df, sql_query, None
except Exception as e:
logger.error(f"Error processing manual filters: {e}")
return None, None, str(e)


def generate_insights(results_df: pd.DataFrame, llm_client):
"""Generate AI insights from results"""
try:
# Prepare summary statistics
summary_stats = {
"total_accounts": len(results_df),
"industries": (
results_df["INDUSTRY"].value_counts().head(5).to_dict()
if "INDUSTRY" in results_df.columns
else {}
),
"geographies": (
results_df["GEOGRAPHY"].value_counts().head(5).to_dict()
if "GEOGRAPHY" in results_df.columns
else {}
),
"avg_revenue": (
results_df["TOTALTCV"].mean() if "TOTALTCV" in results_df.columns else 0
),
}
# Generate insights using LLM
prompt = f"""
Based on the following account data summary, provide 3-5 key insights:
- Total Accounts: {summary_stats['total_accounts']}
- Top Industries: {summary_stats['industries']}
- Top Geographies: {summary_stats['geographies']}
- Average Revenue: ${summary_stats['avg_revenue']:,.0f}
Provide actionable insights for a sales team.
"""
insights = llamacall(llm_client, prompt)
return insights
except Exception as e:
logger.error(f"Error generating insights: {e}")
return "Unable to generate insights at this time."


def register_callbacks(app):
"""Register all callbacks for the application"""
# Initialize resources on app start
global APP_DATA
if not APP_DATA:
APP_DATA = initialize_resources()

# ==================== PAGE ROUTING ====================
@app.callback(
Output("page-content", "children"),
[Input("url", "pathname"), Input("auth-store", "data")],
[State("employee-id-store", "data")],
)
def display_page(pathname, auth_data, emp_data):
"""Route between login and main page based on authentication"""
# Check if user is authenticated
is_authenticated = (
auth_data
and auth_data.get("authenticated")
and emp_data
and emp_data.get("emp_id")
)
if is_authenticated:
return create_main_layout()
else:
return create_login_layout()

# ==================== LOGIN CALLBACKS ====================
@app.callback(
[
Output("auth-store", "data"),
Output("employee-id-store", "data"),
Output("login-message", "children"),
],
[Input("login-button", "n_clicks")],
[State("login-employee-id", "value"), State("login-password", "value")],
)
def handle_login(n_clicks, employee_id, password):
"""Handle login button click"""
if n_clicks == 0:
raise PreventUpdate
if not employee_id or not password:
return (
no_update,
no_update,
dbc.Alert(
"Please enter both Employee ID and Password", color="warning"
),
)
success, message, emp_id = login_user(employee_id, password)
if success:
return (
{"authenticated": True},
{"emp_id": emp_id},
dbc.Alert(message, color="success"),
)
else:
return (no_update, no_update, dbc.Alert(message, color="danger"))

# ==================== RESET PASSWORD CALLBACKS ====================
@app.callback(
Output("reset-password-modal", "is_open"),
[
Input("show-reset-password", "n_clicks"),
Input("reset-cancel", "n_clicks"),
Input("reset-submit", "n_clicks"),
],
[State("reset-password-modal", "is_open")],
)
def toggle_reset_modal(show_clicks, cancel_clicks, submit_clicks, is_open):
"""Toggle reset password modal"""
ctx = callback_context
if not ctx.triggered:
return is_open
button_id = ctx.triggered[0]["prop_id"].split(".")[0]
if button_id == "show-reset-password":
return not is_open
elif button_id in ["reset-cancel", "reset-submit"]:
return False
return is_open

@app.callback(
Output("reset-message", "children"),
[Input("reset-submit", "n_clicks")],
[
State("reset-employee-id", "value"),
State("reset-current-password", "value"),
State("reset-new-password", "value"),
State("reset-confirm-password", "value"),
],
)
def handle_password_reset(n_clicks, emp_id, current_pwd, new_pwd, confirm_pwd):
"""Handle password reset submission"""
if n_clicks == 0:
raise PreventUpdate
if not all([emp_id, current_pwd, new_pwd, confirm_pwd]):
return dbc.Alert("All fields are required", color="warning")
if new_pwd != confirm_pwd:
return dbc.Alert("New passwords do not match", color="warning")
success, message = reset_password(emp_id, current_pwd, new_pwd)
if success:
return dbc.Alert(message, color="success")
else:
return dbc.Alert(message, color="danger")

# ==================== SIMPLE PASSWORD TOGGLE ====================
@app.callback(
[
Output("login-password", "type"),
Output("password-toggle-icon", "src"),
Output("reset-current-password", "type"),
Output("current-password-toggle-icon", "src"),
Output("reset-new-password", "type"),
Output("new-password-toggle-icon", "src"),
Output("reset-confirm-password", "type"),
Output("confirm-password-toggle-icon", "src"),
],
[
Input("toggle-password", "n_clicks"),
Input("toggle-current-password", "n_clicks"),
Input("toggle-new-password", "n_clicks"),
Input("toggle-confirm-password", "n_clicks"),
],
[
State("login-password", "type"),
State("reset-current-password", "type"),
State("reset-new-password", "type"),
State("reset-confirm-password", "type"),
],
)
def toggle_all_passwords(
login_clicks,
current_clicks,
new_clicks,
confirm_clicks,
login_type,
current_type,
new_type,
confirm_type,
):
"""Toggle password visibility for all password fields"""
ctx = callback_context
if not ctx.triggered:
raise PreventUpdate
# Convert icons
eye_open_icon = img_to_base64("assets/eye-open.png")
eye_closed_icon = img_to_base64("assets/eye-closed.png")
button_id = ctx.triggered[0]["prop_id"].split(".")[0]
# Initialize all outputs
login_output_type = login_type
login_output_icon = (
eye_closed_icon if login_type == "password" else eye_open_icon
)
current_output_type = current_type
current_output_icon = (
eye_closed_icon if current_type == "password" else eye_open_icon
)
new_output_type = new_type
new_output_icon = eye_closed_icon if new_type == "password" else eye_open_icon
confirm_output_type = confirm_type
confirm_output_icon = (
eye_closed_icon if confirm_type == "password" else eye_open_icon
)
# Toggle based on which button was clicked
if button_id == "toggle-password":
login_output_type = "text" if login_type == "password" else "password"
login_output_icon = (
eye_open_icon if login_output_type == "text" else eye_closed_icon
)
elif button_id == "toggle-current-password":
current_output_type = "text" if current_type == "password" else "password"
current_output_icon = (
eye_open_icon if current_output_type == "text" else eye_closed_icon
)
elif button_id == "toggle-new-password":
new_output_type = "text" if new_type == "password" else "password"
new_output_icon = (
eye_open_icon if new_output_type == "text" else eye_closed_icon
)
elif button_id == "toggle-confirm-password":
confirm_output_type = "text" if confirm_type == "password" else "password"
confirm_output_icon = (
eye_open_icon if confirm_output_type == "text" else eye_closed_icon
)
return (
login_output_type,
login_output_icon,
current_output_type,
current_output_icon,
new_output_type,
new_output_icon,
confirm_output_type,
confirm_output_icon,
)

# ==================== LOGOUT CALLBACK ====================
@app.callback(
Output("url", "pathname", allow_duplicate=True),
[Input("logout-button", "n_clicks")],
prevent_initial_call=True,
)
def handle_logout(n_clicks):
"""Handle logout button click"""
if n_clicks:
# Clear auth store would happen automatically with page refresh
return "/"
raise PreventUpdate

# ==================== INPUT METHOD SELECTOR ====================
@app.callback(
[Output("sidebar", "style"), Output("nlq-interface", "style")],
[Input("input-method-selector", "value")],
)
def toggle_input_method(input_method):
"""Show/hide sidebar and NLQ interface based on selection"""
if input_method == "manual":
sidebar_style = {
"transform": "translateX(0)",
"transition": "all 0.4s ease-in-out",
}
nlq_style = {"display": "none"}
else:
sidebar_style = {
"transform": "translateX(-100%)",
"transition": "all 0.4s ease-in-out",
}
nlq_style = {"display": "block"}
return sidebar_style, nlq_style

# ==================== FILTER DATA LOADING ====================
@app.callback(
[
Output(f"filter-{config['column'].replace('_', '-').lower()}", "options")
for config in FILTER_CONFIG
if config["widget"] == "multiselect"
]
+ [
Output("filter-tcv", "min"),
Output("filter-tcv", "max"),
Output("filter-tcv", "value"),
Output("filter-tcv", "marks"),
],
[Input("page-content", "children")],
)
def load_filter_options(page_content):
"""Load filter dropdown options from actual data"""
if not page_content or not APP_DATA.get("account_data"):
raise PreventUpdate
account_data = APP_DATA["account_data"]
# Prepare multiselect options
outputs = []
for config in FILTER_CONFIG:
if config["widget"] == "multiselect":
column = config["column"]
if column in account_data.columns:
# Get unique non-null values
options = account_data[column].dropna().unique().tolist()
# Convert to strings and sort
options = sorted([str(opt) for opt in options if str(opt).strip()])
outputs.append([{"label": opt, "value": opt} for opt in options])
else:
outputs.append([])
logger.warning(f"Column {column} not found in data for filter")
# Calculate TCV range
if "TOTALTCV" in account_data.columns:
tcv_series = pd.to_numeric(
account_data["TOTALTCV"], errors="coerce"
).dropna()
if not tcv_series.empty:
min_tcv = int(tcv_series.min())
max_tcv = int(tcv_series.max())
# Create marks for the slider
marks = {min_tcv: f"${min_tcv:,.0f}", max_tcv: f"${max_tcv:,.0f}"}
outputs.extend([min_tcv, max_tcv, [min_tcv, max_tcv], marks])
else:
outputs.extend([0, 1000000, [0, 1000000], {}])
else:
outputs.extend([0, 1000000, [0, 1000000], {}])
return outputs

@app.callback(Output("tcv-display", "children"), [Input("filter-tcv", "value")])
def update_tcv_display(tcv_range):
"""Update TCV range display"""
if tcv_range:
return html.Div(
f"TCV Range: ${tcv_range[0]:,.0f} - ${tcv_range[1]:,.0f}",
style={"margin-top": "5px", "font-size": "12px", "color": "#666"},
)
return ""

# ==================== NLQ SEARCH ====================
@app.callback(
[
Output("results-store", "data"),
Output("nlq-message", "children"),
Output("nlq-loading-output", "children"),
],
[Input("nlq-search-button", "n_clicks")],
[State("nlq-query-input", "value"), State("employee-id-store", "data")],
)
def handle_nlq_search(n_clicks, query_text, emp_data):
"""Handle NLQ search button click"""
if n_clicks == 0:
raise PreventUpdate
if not query_text:
return no_update, dbc.Alert("Please enter a query", color="warning"), ""
emp_id = emp_data.get("emp_id") if emp_data else "unknown"
# Process query
results_df, sql_query, error = process_nlq_query(
query_text,
APP_DATA["conn"],
APP_DATA["llm_client"],
APP_DATA["audit_manager"],
emp_id,
)
if error:
return no_update, dbc.Alert(f"Error: {error}", color="danger"), ""
if results_df is not None and not results_df.empty:
# Store results
results_data = {
"data": results_df.to_json(orient="split"),
"query": query_text,
"sql": sql_query,
"count": len(results_df),
}
return (
results_data,
dbc.Alert(f"Found {len(results_df)} results", color="success"),
"",
)
else:
return (no_update, dbc.Alert("No results found", color="info"), "")

# ==================== NLQ CLEAR ====================
@app.callback(
[
Output("nlq-query-input", "value"),
Output("results-store", "data", allow_duplicate=True),
],
[Input("nlq-clear-button", "n_clicks")],
prevent_initial_call=True,
)
def handle_nlq_clear(n_clicks):
"""Clear NLQ input and results"""
if n_clicks:
return "", None
raise PreventUpdate

# ==================== MANUAL FILTERS ====================
@app.callback(
[
Output("results-store", "data", allow_duplicate=True),
Output("manual-loading-output", "children"),
],
[Input("apply-filters-button", "n_clicks")],
[
State("filter-account-name", "value"),
State("filter-technology", "value"),
State("filter-tcv", "value"),
*[
State(f"filter-{config['column'].replace('_', '-').lower()}", "value")
for config in FILTER_CONFIG
],
State("employee-id-store", "data"),
],
prevent_initial_call=True,
)
def handle_apply_filters(
n_clicks, account_name, technology, tcv_range, *filter_values, emp_data
):
"""Handle apply filters button click"""
if n_clicks == 0:
raise PreventUpdate
if not APP_DATA.get("account_data"):
return no_update, dbc.Alert("No data available", color="danger")
# Get the base data
filtered_df = APP_DATA["account_data"].copy()
# Apply filters
# 1. Account name filter
if account_name:
filtered_df = filtered_df[
filtered_df["ACCOUNT_NAME"]
.astype(str)
.str.contains(account_name, case=False, na=False)
]
# 2. Technology filter
if technology:
filtered_df = filtered_df[
filtered_df["TECHNOLOGY_LIST"]
.astype(str)
.str.contains(technology, case=False, na=False)
]
# 3. TCV filter
if tcv_range and "TOTALTCV" in filtered_df.columns:
filtered_df["TOTALTCV"] = pd.to_numeric(
filtered_df["TOTALTCV"], errors="coerce"
)
filtered_df = filtered_df[
(filtered_df["TOTALTCV"] >= tcv_range[0])
& (filtered_df["TOTALTCV"] <= tcv_range[1])
]
# 4. Apply other filters from FILTER_CONFIG
filter_values_list = list(filter_values)
for i, config in enumerate(FILTER_CONFIG):
column = config["column"]
values = filter_values_list[i]
if values:
if config["widget"] == "multiselect":
filtered_df = filtered_df[filtered_df[column].isin(values)]
elif config["widget"] == "selectbox" and values != "Any":
# Handle boolean columns
if column in [
"ISSTRATEGICGROWTH",
"ISGOVERNMENTOWNED_STATE_OWNED_ENTERPRISE",
"ISPARTNER",
]:
filtered_df = filtered_df[
filtered_df[column] == (values == "Yes")
]
elif column == "ISACTIVEOPPORTUNITY":
# ISACTIVEOPPORTUNITY might be 1/0 in your data
filtered_df = filtered_df[
filtered_df[column] == (1 if values == "Yes" else 0)
]
else:
filtered_df = filtered_df[filtered_df[column] == values]
# Store results
if not filtered_df.empty:
results_data = {
"data": filtered_df.to_json(orient="split"),
"filters": "Manual filters applied",
"count": len(filtered_df),
}
# Log audit
emp_id = emp_data.get("emp_id") if emp_data else "unknown"
APP_DATA["audit_manager"].log_query(
emp_id=emp_id,
query_text="Manual Filters Applied",
sql_query="Applied via UI filters",
result_count=len(filtered_df),
)
return results_data, dbc.Alert(
f"Found {len(filtered_df)} results", color="success"
)
else:
return no_update, dbc.Alert(
"No results found with selected filters", color="info"
)

# ==================== RESET FILTERS ====================
@app.callback(
[
Output("filter-account-name", "value"),
Output("filter-technology", "value"),
Output("filter-tcv", "value"),
*[
Output(f"filter-{config['column']}", "value")
for config in FILTER_CONFIG
],
],
[Input("reset-filters-button", "n_clicks")],
)
def handle_reset_filters(n_clicks):
"""Reset all filter selections"""
if n_clicks:
# Get TCV range from data
if (
APP_DATA.get("account_data")
and "TOTALTCV" in APP_DATA["account_data"].columns
):
tcv_series = pd.to_numeric(
APP_DATA["account_data"]["TOTALTCV"], errors="coerce"
).dropna()
min_tcv = int(tcv_series.min()) if not tcv_series.empty else 0
max_tcv = int(tcv_series.max()) if not tcv_series.empty else 1000000
tcv_value = [min_tcv, max_tcv]
else:
tcv_value = [0, 1000000]
# Return reset values
return [""] * 2 + [tcv_value] + [None] * len(FILTER_CONFIG)
raise PreventUpdate

# ==================== RESULTS TABLE ====================
@app.callback(
[
Output("results-table", "columns"),
Output("results-table", "data"),
Output("page-info", "children"),
Output("page-number-input", "max"),
],
[
Input("results-store", "data"),
Input("page-number-input", "value"),
Input("page-size-selector", "value"),
],
)
def update_results_table(results_data, page_number, page_size):
"""Update results table with pagination"""
if not results_data:
return [], [], "", 1
# Load DataFrame from stored JSON
results_df = pd.read_json(results_data["data"], orient="split")
# Calculate pagination
total_rows = len(results_df)
total_pages = max(1, (total_rows + page_size - 1) // page_size)
# Ensure page_number is valid
if page_number is None or page_number < 1:
page_number = 1
elif page_number > total_pages:
page_number = total_pages
# Slice data for current page
start_idx = (page_number - 1) * page_size
end_idx = min(start_idx + page_size, total_rows)
page_df = results_df.iloc[start_idx:end_idx]
# Format currency columns
if "TOTALTCV" in page_df.columns:
page_df["TOTALTCV"] = pd.to_numeric(page_df["TOTALTCV"], errors="coerce")
page_df["TOTALTCV"] = page_df["TOTALTCV"].apply(
lambda x: f"${x:,.0f}" if pd.notnull(x) else "-"
)
# Create columns definition
columns = [{"name": col, "id": col} for col in page_df.columns]
# Create page info text
page_info = f"Showing rows {start_idx + 1:,} – {end_idx:,} of {total_rows:,} | Page {page_number} of {total_pages}"
return columns, page_df.to_dict("records"), page_info, total_pages

# ==================== SUMMARY STATS ====================
@app.callback(Output("summary-stats", "children"), [Input("results-store", "data")])
def update_summary_stats(results_data):
"""Update summary statistics display"""
if not results_data:
return []
results_df = pd.read_json(results_data["data"], orient="split")
total_accounts = len(results_df)
# Calculate statistics
stats = [
html.Div(
className="stat-card",
children=[html.H3(f"{total_accounts:,}"), html.P("Total Accounts")],
)
]
# Add more stats based on available columns
if "INDUSTRY" in results_df.columns:
unique_industries = results_df["INDUSTRY"].nunique()
stats.append(
html.Div(
className="stat-card",
children=[html.H3(f"{unique_industries:,}"), html.P("Industries")],
)
)
if "GEOGRAPHY" in results_df.columns:
unique_geo = results_df["GEOGRAPHY"].nunique()
stats.append(
html.Div(
className="stat-card",
children=[html.H3(f"{unique_geo:,}"), html.P("Locations")],
)
)
if "TOTALTCV" in results_df.columns:
total_revenue = pd.to_numeric(results_df["TOTALTCV"], errors="coerce").sum()
stats.append(
html.Div(
className="stat-card",
children=[
html.H3(f"${total_revenue / 1000000:.1f}M"),
html.P("Total Revenue"),
],
)
)
return html.Div(className="stats-container", children=stats)

# ==================== CHARTS ====================
@app.callback(
Output("charts-section", "children"), [Input("results-store", "data")]
)
def update_charts(results_data):
"""Generate and display charts"""
if not results_data:
return []
try:
results_df = pd.read_json(results_data["data"], orient="split")
charts = create_summary_charts(results_df)
if charts:
# Create columns for charts
cols = []
for chart_name, fig in charts.items():
cols.append(
html.Div(
className="chart-container",
children=[
dcc.Graph(figure=fig, config={"displayModeBar": False})
],
)
)
return html.Div(className="charts-grid", children=cols)
else:
return html.Div(
"No charts available for this data", className="text-muted"
)
except Exception as e:
logger.error(f"Error creating charts: {e}")
return html.Div(f"Error creating charts: {str(e)}", className="text-danger")

# ==================== AI INSIGHTS ====================
@app.callback(Output("ai-insights", "children"), [Input("results-store", "data")])
def update_insights(results_data):
"""Generate and display AI insights"""
if not results_data:
return []
try:
results_df = pd.read_json(results_data["data"], orient="split")
insights_text = generate_insights(results_df, APP_DATA["llm_client"])
return html.Div(
className="insights-card",
children=[html.H3("🤖 AI Insights"), html.P(insights_text)],
)
except Exception as e:
logger.error(f"Error generating insights: {e}")
return []

# ==================== DOWNLOAD ====================
@app.callback(
Output("download-dataframe", "data"),
[Input("download-button", "n_clicks")],
[State("results-store", "data")],
)
def download_results(n_clicks, results_data):
"""Handle download button click"""
if not n_clicks or not results_data:
raise PreventUpdate
results_df = pd.read_json(results_data["data"], orient="split")
# Create Excel file with metadata
output = BytesIO()
with pd.ExcelWriter(output, engine="xlsxwriter") as writer:
workbook = writer.book
worksheet = workbook.add_worksheet("Results")
writer.sheets["Results"] = worksheet
# Add header with query/filter info
header_info = f"# Query: {results_data.get('query', 'Manual Filters')}"
num_cols = len(results_df.columns)
merge_range = f"A1:{chr(65 + num_cols - 1)}1"
header_format = workbook.add_format(
{"bold": True, "align": "left", "valign": "vcenter"}
)
worksheet.merge_range(merge_range, header_info, header_format)
# Write DataFrame
results_df.to_excel(writer, index=False, sheet_name="Results", startrow=1)
return dcc.send_bytes(output.getvalue(), "target_accounts.xlsx")