"""Optional AI data assistant (phase 6): natural language → *validated* read-only SQL over the catalog views.

Guarantees: the model never executes anything — it only proposes SQL; FinData validates it with the same sandbox as
the playground (single SELECT, allow-listed directories, timeout, row cap), shows the SQL + EXPLAIN plan, cites the
dataset versions used, and refuses questions about data that is not in the catalog. Server-side BYOK (Anthropic)
only; status is `pending_configuration` until ANTHROPIC_API_KEY is set and the capability test has been recorded.
"""
from __future__ import annotations

import json
import time
from typing import Any

from fastapi import APIRouter, Depends, Request
from findata_core.config import get_settings
from findata_core.db import connection
from findata_ingest.reader import dataset_views, run_sql
from pydantic import BaseModel, Field

from findata_api import auth as A
from findata_api.errors import ApiError

router = APIRouter(prefix="/assistant", tags=["assistant"])
MODEL = "claude-opus-5-5"


class AskIn(BaseModel):
    question: str = Field(..., min_length=3, max_length=2000)
    max_rows: int = Field(200, ge=1, le=5000)
    execute: bool = True


def _catalog_context() -> tuple[str, dict[str, Any]]:
    with connection() as conn:
        ds = conn.execute("SELECT id, name, interval, adjustment, status, current_version, timestamp_convention, timezone FROM datasets WHERE kind IN ('market_bars','derived') ORDER BY id").fetchall()
        cov = conn.execute("SELECT dataset_id, min(session_date) d0, max(session_date) d1, count(DISTINCT symbol) syms FROM coverage GROUP BY 1").fetchall()
    covm = {c["dataset_id"]: c for c in cov}
    lines = ["Available DuckDB views (one per dataset; columns: instrument_id, symbol_as_reported, asset_class, interval, timestamp_utc (bar OPEN, UTC), session_date, open, high, low, close, volume, currency, trade_count, vwap, adjustment_type, source_id, ingested_at, quality_flags, revision_id):"]
    versions = {}
    for d in ds:
        c = covm.get(d["id"])
        v = f"view {d['id'].replace('.', '_')}: {d['name']} — interval {d['interval']}, adjustment {d['adjustment']}, status {d['status']}, version {d['current_version']}"
        if c:
            v += f", sessions {c['d0']}→{c['d1']}, {c['syms']} symbols"
        else:
            v += ", NO DATA LOADED"
        lines.append(v)
        versions[d["id"]] = d["current_version"]
    lines.append("Fundamentals are NOT available through SQL views (use the fundamentals endpoint); say so if asked.")
    return "\n".join(lines), versions


SYSTEM = """You translate research questions about a private market-data lake into ONE read-only DuckDB SQL statement.
Rules: SELECT/WITH only; use only the listed views and columns; never invent datasets, symbols, dates or columns that are not listed; if the question needs data that is not available (e.g. a dataset with NO DATA LOADED, fundamentals, options, ticks, a date outside coverage), set "sql" to null and explain in "refusal". Prices are raw (unadjusted) unless the view says otherwise. timestamp_utc marks the bar open; convert with timezone 'America/New_York' when the user talks about Eastern time. Always LIMIT results reasonably.
Respond with JSON only: {"sql": string|null, "explanation": string, "assumptions": [string], "refusal": string|null}."""


def _ask_model(question: str, context: str) -> dict[str, Any]:
    import anthropic
    s = get_settings()
    client = anthropic.Anthropic(api_key=s.anthropic_api_key.get_secret_value(), max_retries=1, timeout=90.0)
    try:
        resp = client.messages.create(
            model=MODEL, max_tokens=4000, output_config={"effort": "medium"},
            system=[{"type": "text", "text": SYSTEM, "cache_control": {"type": "ephemeral"}}, {"type": "text", "text": context}],
            messages=[{"role": "user", "content": question}],
        )
    except anthropic.AuthenticationError:
        raise ApiError(502, "provider_auth", "Anthropic rejected the configured key") from None
    except anthropic.RateLimitError:
        raise ApiError(503, "provider_rate_limited", "Anthropic rate limit; retry later") from None
    except anthropic.APIStatusError as e:
        raise ApiError(502, "provider_error", f"Anthropic error {e.status_code}") from None
    except anthropic.APIConnectionError:
        raise ApiError(502, "provider_unreachable", "Could not reach Anthropic") from None
    if resp.stop_reason == "refusal":
        return {"sql": None, "explanation": "", "assumptions": [], "refusal": "The model declined this request."}
    text = "".join(b.text for b in resp.content if b.type == "text").strip()
    if text.startswith("```"):
        text = text.strip("`").split("\n", 1)[1] if "\n" in text else text.strip("`")
        text = text.rsplit("```", 1)[0]
    try:
        out = json.loads(text)
    except json.JSONDecodeError:
        return {"sql": None, "explanation": text[:500], "assumptions": [], "refusal": "Model output was not valid JSON"}
    out["_usage"] = {"input_tokens": resp.usage.input_tokens, "output_tokens": resp.usage.output_tokens,
                     "cache_read_input_tokens": getattr(resp.usage, "cache_read_input_tokens", None), "model": resp.model}
    return out


@router.get("/status")
def status(p: A.Principal = Depends(A.require("query"))):
    s = get_settings()
    configured = bool(s.anthropic_api_key.get_secret_value())
    return {"status": "active" if configured else "pending_configuration", "provider": "anthropic" if configured else None, "model": MODEL if configured else None,
            "note": None if configured else "Set ANTHROPIC_API_KEY in the node .env (server-side BYOK) and run the capability/cost test described in docs/STATUS.md.",
            "guarantees": ["model proposes SQL only; FinData validates and executes in the read-only sandbox", "generated SQL and EXPLAIN plan are always shown",
                           "dataset versions used are cited", "questions about data not in the catalog are refused"]}


@router.post("")
def ask(body: AskIn, request: Request, p: A.Principal = Depends(A.require("query"))):
    s = get_settings()
    if not s.anthropic_api_key.get_secret_value():
        raise ApiError(409, "pending_configuration", "AI assistant is not configured (ANTHROPIC_API_KEY missing on the server)")
    context, versions = _catalog_context()
    t0 = time.monotonic()
    proposal = _ask_model(body.question, context)
    out: dict[str, Any] = {"question": body.question, "sql": proposal.get("sql"), "explanation": proposal.get("explanation"),
                           "assumptions": proposal.get("assumptions") or [], "refusal": proposal.get("refusal"),
                           "dataset_versions": versions, "model_usage": proposal.get("_usage"), "executed": False}
    if not proposal.get("sql"):
        A.audit(request, "assistant.ask", None, refused=True, question=body.question[:300])
        return out
    views = dataset_views()
    try:
        plan, _ = run_sql(f"EXPLAIN {proposal['sql']}", 50, 10, views)
        out["plan"] = "\n".join(str(r.get("explain_value") or r) for r in plan.to_pylist())
    except Exception as e:
        out["refusal"] = f"Generated SQL rejected by the sandbox: {str(e)[:300]}"
        A.audit(request, "assistant.ask", None, rejected=True, question=body.question[:300], sql=proposal["sql"][:500])
        return out
    if body.execute:
        try:
            tbl, meta = run_sql(proposal["sql"], min(body.max_rows, s.query_max_rows), s.query_timeout_seconds, views)
        except Exception as e:
            out["refusal"] = f"Execution failed: {str(e)[:300]}"
            return out
        rows = tbl.to_pylist()
        for r in rows:
            for k, v in r.items():
                if hasattr(v, "isoformat"):
                    r[k] = v.isoformat()
        out.update({"executed": True, "columns": [{"name": f.name, "type": str(f.type)} for f in tbl.schema], "rows": rows,
                    "truncated": meta.get("truncated"), "elapsed_ms": int((time.monotonic() - t0) * 1000)})
    A.audit(request, "assistant.ask", None, question=body.question[:300], sql=proposal["sql"][:500], rows=len(out.get("rows", [])))
    return out