app.py

# app.py
import os
import pandas as pd
import gradio as gr

from tools.sql_tool import SQLTool
from tools.ts_preprocess import build_timeseries

# Ensure DB path & defaults (you can set these in Space Settings → Variables)
DUCKDB_PATH = os.getenv("DUCKDB_PATH", "alm.duckdb")
DEFAULT_SCHEMA = os.getenv("SQL_DEFAULT_SCHEMA", "main")
DEFAULT_TABLE  = os.getenv("SQL_DEFAULT_TABLE",  "masterdataset_v")

sql_tool = SQLTool(DUCKDB_PATH)

INTRO = f"""
### ALM LLM — Demo  
Connected to **DuckDB** at `{DUCKDB_PATH}` using table **{DEFAULT_SCHEMA}.{DEFAULT_TABLE}**.

**Try:**  
- *"show me the top 10 fds by portfolio value"*  
- *"top 10 assets by portfolio value"*  
- *"sum portfolio value by currency"*  
"""

def run_nl(nl_query: str):
    if not nl_query or not nl_query.strip():
        return pd.DataFrame(), "", "Please enter a query.", pd.DataFrame(), pd.DataFrame()

    try:
        df, sql, why = sql_tool.query_from_nl(nl_query)
    except Exception as e:
        return pd.DataFrame(), "", f"Error: {e}", pd.DataFrame(), pd.DataFrame()

    # Try to build timeseries cashflows + gap if columns match masterdataset_v
    try:
        cf, gap = build_timeseries(df)
    except Exception as e:
        cf, gap = pd.DataFrame(), pd.DataFrame()

    return df, sql.strip(), why, cf, gap

def run_sql(sql_text: str):
    if not sql_text or not sql_text.strip():
        return pd.DataFrame(), "Please paste a SQL statement.", pd.DataFrame(), pd.DataFrame()

    try:
        df = sql_tool.run_sql(sql_text)
    except Exception as e:
        return pd.DataFrame(), f"Error: {e}", pd.DataFrame(), pd.DataFrame()

    try:
        cf, gap = build_timeseries(df)
    except Exception:
        cf, gap = pd.DataFrame(), pd.DataFrame()
    return df, "OK", cf, gap

with gr.Blocks(title="ALM LLM") as demo:
    gr.Markdown(INTRO)

    with gr.Tab("Ask in Natural Language"):
        nl = gr.Textbox(label="Ask a question", placeholder="e.g., show me the top 10 fds by portfolio value")
        btn = gr.Button("Run")
        sql_out = gr.Textbox(label="Generated SQL", interactive=False)
        why_out = gr.Textbox(label="Reasoning", interactive=False)
        df_out  = gr.Dataframe(label="Query Result", wrap=True)
        cf_out  = gr.Dataframe(label="Projected Cash-Flows (if applicable)", wrap=True, height=250)
        gap_out = gr.Dataframe(label="Liquidity Gap (monthly)", wrap=True, height=200)

        btn.click(fn=run_nl, inputs=[nl], outputs=[df_out, sql_out, why_out, cf_out, gap_out])

    with gr.Tab("Run Raw SQL"):
        sql_in = gr.Code(label="SQL", language="sql", value=f"SELECT * FROM {DEFAULT_SCHEMA}.{DEFAULT_TABLE} LIMIT 20;")
        btn2 = gr.Button("Execute")
        df2  = gr.Dataframe(label="Result", wrap=True)
        status = gr.Textbox(label="Status", interactive=False)
        cf2   = gr.Dataframe(label="Projected Cash-Flows (if applicable)", wrap=True, height=250)
        gap2  = gr.Dataframe(label="Liquidity Gap (monthly)", wrap=True, height=200)

        btn2.click(fn=run_sql, inputs=[sql_in], outputs=[df2, status, cf2, gap2])

if __name__ == "__main__":
    # Spaces set PORT automatically; otherwise, Gradio defaults are fine.
    demo.launch(server_name="0.0.0.0", server_port=int(os.environ.get("PORT", 7860)))