# retoor <retoor@molodetz.nl>

from fastapi import APIRouter, Request
from fastapi.responses import JSONResponse

from devplacepy.database import get_int_setting
from devplacepy.schemas import NlQueryOut
from devplacepy.services.dbapi import nl2sql
from devplacepy.services.dbapi.validate import run_select

from ._shared import assert_table, error, read_body, require_dbapi_caller

router = APIRouter()

DEFAULT_MAX_ROWS = 5000


def _truthy(value) -> bool:
    return str(value).strip().lower() in ("1", "true", "yes", "on")


@router.post("/nl")
async def dbapi_nl(request: Request):
    caller = require_dbapi_caller(request)
    body = await read_body(request)
    question = str(body.get("question", "")).strip()
    table = str(body.get("table", "")).strip()
    if not question or not table:
        return error(400, "Provide both 'question' and 'table'.")
    assert_table(table)
    apply_soft_delete = _truthy(body.get("apply_soft_delete", True))
    dialect = str(body.get("dialect", "sqlite")).strip() or "sqlite"
    execute = _truthy(body.get("execute", False))

    design = await nl2sql.design_query(
        question,
        table,
        apply_soft_delete=apply_soft_delete,
        dialect=dialect,
        api_key=caller.api_key,
    )
    out = NlQueryOut(**design.as_dict())

    if execute and design.valid:
        max_rows = max(1, get_int_setting("dbapi_max_rows", DEFAULT_MAX_ROWS))
        rows, truncated = run_select(design.sql, max_rows)
        out.executed = True
        out.rows = rows
        out.row_count = len(rows)
        out.truncated = truncated

    _audit_nl(request, caller, table, design.sql, design.valid)
    return JSONResponse(out.model_dump(mode="json"))


def _audit_nl(request, caller, table, sql, valid):
    from devplacepy.services.audit import record as audit

    audit.record(
        request,
        "database.nl.design",
        target_type=table,
        summary=f"{caller.username or caller.kind} designed a query on {table}",
        metadata={"table": table, "sql": sql[:500], "valid": valid, "caller": caller.kind},
    )
