# 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

DUCKDB_PATH = os.getenv("DUCKDB_PATH", "alm.duckdb")

sql_tool = SQLTool(DUCKDB_PATH)
RESOLVED_PATH = sql_tool.get_full_table_path()  # e.g., my_db.main.masterdataset_v or main.masterdataset_v

INTRO = f"""
### ALM LLM — Demo

Connected to **DuckDB** at `{DUCKDB_PATH}`  
Using table **{RESOLVED_PATH}** (auto-resolved).
"""

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:
        cf, gap = build_timeseries(df)
    except Exception:
        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", lines=2)
        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", interactive=True)
        cf_out  = gr.Dataframe(label="Projected Cash-Flows (if applicable)", interactive=True)
        gap_out = gr.Dataframe(label="Liquidity Gap (monthly)", interactive=True)
        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 {RESOLVED_PATH} LIMIT 20;")
        btn2 = gr.Button("Execute")
        df2   = gr.Dataframe(label="Result", interactive=True)
        status = gr.Textbox(label="Status", interactive=False)
        cf2    = gr.Dataframe(label="Projected Cash-Flows (if applicable)", interactive=True)
        gap2   = gr.Dataframe(label="Liquidity Gap (monthly)", interactive=True)
        btn2.click(fn=run_sql, inputs=[sql_in], outputs=[df2, status, cf2, gap2])

if __name__ == "__main__":
    demo.launch(server_name="0.0.0.0", server_port=int(os.environ.get("PORT", 7860)))