__doc__ = """
This FastAPI app uses gradio components with SQL code input
and HTML table output.  The query is executed using DuckDB.
The query results are shown in an iframe where the table
is styled and made interactive using Datatables.net scripts.

"""

import gradio as gr
import pandas as pd
from fastapi import FastAPI
from fastapi.responses import HTMLResponse, RedirectResponse
from itables import to_html_datatable

from sql import Q

app = FastAPI()

def query_from_request(query, request: gr.Request):
    """Process query from input block or from initial request.

    https://github.com/gradio-app/gradio/issues/7464#issuecomment-1960161591
    """
    if not query:
        query_params = request.query_params
        base64query = dict(query_params).get("q")
    else:
        base64query = Q(query).base64
    if base64query in (None, "example"):
        decoded = Q("""SELECT 42 AS answer, 'LU & E' AS question""")
        base64query = decoded.base64
    else:
        decoded = Q.from_base64(base64query)

    _host = request.headers.get("Host")
    if "huggingface.co/spaces" in _host:
        # modify URL to access endpoints that aren't available on default app landing page
        split_url = _host.rsplit("/", maxsplit=2)
        hf_user, hf_space = split_url[1], split_url[2]
        host = f"https://{hf_user}-{hf_space}.hf.space"
    else:
        host = _host
    editor_url = f"{host}/sql/?q={base64query}"
    query_url = f"{host}/q/{base64query}"
    result = f"""
    <div id="resultContainer">
    <iframe src="/q/{base64query}" width="90%" height="90%"></iframe>
    </div>"""
    return (decoded, editor_url, query_url, result)

@app.get("/q/{base64query}", response_class=HTMLResponse)
def query_db(base64query: str|None = None):
    decoded = Q.from_base64(base64query)
    df = decoded.df()
    html = to_html_datatable(df, classes="display compact cell-border")
    return f"""
        <h3>{decoded}</h3>
        <div>{html}</div>
    """

with gr.Blocks(
    title="Gradio DuckDB Editor",
    css="#resultsContainer {height: 75vh;}"
) as gradio_sql_interface:
    with gr.Row():
        with gr.Column(scale=1):
            header = gr.Markdown("# SQL Editor")
            sql_code = gr.Code(language="sql", label="SQL Query", lines=32, interactive=True)
            button = gr.Button("run")
            editor_url = gr.Code(label="Share Editor URL", lines=1)
            query_url = gr.Code(label="Share Editor URL", lines=1)
        with gr.Column(scale=2):
            markdown = gr.Markdown("# RESULTS")
            results = gr.HTML()
    button.click(query_from_request, [sql_code], [sql_code, editor_url, query_url, results]) 
    gradio_sql_interface.load(query_from_request, [sql_code], [sql_code, editor_url, query_url, results])

app = gr.mount_gradio_app(app, gradio_sql_interface, path="/sql")

@app.get("/")
@app.get("/sql")
@app.get("/sql/")
def redirect_to_example():
    return RedirectResponse("/sql/?q=example")