# 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

# ==========================================================
#   CONFIG
# ==========================================================
DUCKDB_PATH = os.getenv("DUCKDB_PATH", "alm.duckdb")
DEFAULT_SCHEMA = os.getenv("SQL_DEFAULT_SCHEMA", "my_db")
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"*
"""

# ==========================================================
#   BACKEND HANDLERS
# ==========================================================
def run_nl(nl_query: str):
    """Handle natural-language queries."""
    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):
    """Handle raw SQL execution."""
    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


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

    # ---- Tab 1: Natural language ----
    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],
        )

    # ---- Tab 2: Raw SQL ----
    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", 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],
        )

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