# retoor <retoor@molodetz.nl>

from __future__ import annotations

import json
import logging
import re
from dataclasses import dataclass, field

from devplacepy import stealth
from devplacepy.config import INTERNAL_GATEWAY_URL, INTERNAL_MODEL
from devplacepy.database import get_setting, internal_gateway_key

from . import crud
from .policy import soft_delete_aware
from .validate import validate_select

logger = logging.getLogger(__name__)

GATEWAY_TIMEOUT_SECONDS = 60.0
MAX_TOKENS = 600
FENCE = re.compile(r"```(?:sql)?\s*(.+?)```", re.IGNORECASE | re.DOTALL)


@dataclass
class Design:
    question: str
    table: str
    sql: str = ""
    valid: bool = False
    attempts: int = 0
    dialect: str = "sqlite"
    applied_soft_delete: bool = False
    suspicious: list[str] = field(default_factory=list)
    tables: list[str] = field(default_factory=list)
    error: str | None = None

    def as_dict(self) -> dict:
        return {
            "question": self.question,
            "table": self.table,
            "sql": self.sql,
            "valid": self.valid,
            "attempts": self.attempts,
            "dialect": self.dialect,
            "applied_soft_delete": self.applied_soft_delete,
            "suspicious": self.suspicious,
            "tables": self.tables,
            "error": self.error,
        }


def _extract_sql(text: str) -> str:
    match = FENCE.search(text or "")
    sql = match.group(1) if match else (text or "")
    return sql.strip().rstrip(";").strip()


def _system_prompt(table: str, apply_soft_delete: bool, dialect: str) -> str:
    schema = crud.schema(table)
    columns = ", ".join(f"{c['name']} ({c['type']})" for c in schema["columns"])
    examples, _ = crud.list_rows(table, limit=5)
    sample = json.dumps(examples, ensure_ascii=False, default=str)[:3000]
    preamble = get_setting("dbapi_nl_system_preamble", "").strip()
    soft_rule = ""
    if apply_soft_delete and soft_delete_aware(table):
        soft_rule = (
            f"The {table} table uses soft deletes. ALWAYS add `deleted_at IS NULL` to the "
            "WHERE clause so deleted rows are excluded, UNLESS the user explicitly asks for "
            "deleted or all rows.\n"
        )
    lines = [
        preamble,
        "You translate a natural-language request into ONE read-only SQL SELECT statement "
        f"for a SQLite database (dialect: {dialect}).",
        f"Target table: {table}",
        f"Columns: {columns}",
        f"Example rows (JSON): {sample}",
        soft_rule,
        "Rules: return ONLY the SQL, no prose, no explanation, no markdown. Exactly one "
        "SELECT statement. Never write INSERT, UPDATE, DELETE, DROP, PRAGMA, or ATTACH. "
        "Prefer an explicit LIMIT when the user does not ask for everything. Dates are ISO "
        "8601 strings; compare them lexicographically.",
    ]
    return "\n".join(line for line in lines if line)


async def _complete(messages: list[dict], api_key: str, model: str) -> str:
    payload = {
        "model": model,
        "messages": messages,
        "max_tokens": MAX_TOKENS,
        "temperature": 0.0,
    }
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
        "X-App-Reference": "devplace-dbapi-v-1-0-0",
    }
    async with stealth.stealth_async_client(timeout=GATEWAY_TIMEOUT_SECONDS) as client:
        response = await client.post(INTERNAL_GATEWAY_URL, json=payload, headers=headers)
    if response.status_code >= 400:
        raise RuntimeError(f"AI gateway returned {response.status_code}")
    data = response.json()
    return (data.get("choices") or [{}])[0].get("message", {}).get("content") or ""


async def design_query(
    question: str,
    table: str,
    *,
    apply_soft_delete: bool = True,
    dialect: str = "sqlite",
    max_attempts: int = 3,
    api_key: str = "",
) -> Design:
    design = Design(question=question, table=table, dialect=dialect)
    key = api_key or internal_gateway_key()
    model = get_setting("dbapi_nl_model", "") or INTERNAL_MODEL
    messages = [
        {"role": "system", "content": _system_prompt(table, apply_soft_delete, dialect)},
        {"role": "user", "content": question},
    ]
    for attempt in range(1, max(1, max_attempts) + 1):
        design.attempts = attempt
        try:
            raw = await _complete(messages, key, model)
        except Exception as exc:
            design.error = f"AI gateway error: {exc}"
            return design
        sql = _extract_sql(raw)
        verdict = validate_select(sql, dialect=dialect)
        design.sql = verdict.sql
        design.suspicious = verdict.suspicious
        design.tables = verdict.tables
        design.applied_soft_delete = "deleted_at" in verdict.sql.lower()
        if verdict.valid:
            design.valid = True
            design.error = None
            return design
        design.error = verdict.error
        messages.append({"role": "assistant", "content": sql})
        messages.append(
            {
                "role": "user",
                "content": (
                    f"That query is invalid: {verdict.error}. Return a corrected single "
                    "SELECT statement only, no prose."
                ),
            }
        )
    return design
