import uuid
from datetime import datetime
from typing import Any, Literal

from pydantic import BaseModel, ConfigDict, Field, model_validator

DataQueryKind = Literal["rest", "sql"]
_NAME = r"^[A-Za-z_][A-Za-z0-9_]{0,59}$"


class DataQueryParam(BaseModel):
    """Filled into the query as {{param.NAME}} (REST) or :NAME (SQL)."""

    name: str = Field(pattern=_NAME)
    label: str | None = Field(default=None, max_length=100)
    default: Any = None
    required: bool = False


class RestDefinition(BaseModel):
    method: Literal["GET", "POST"] = "GET"
    url: str = Field(min_length=1, max_length=2000)
    headers: dict[str, str] = {}
    body: str | None = Field(default=None, max_length=20_000)
    # Where the rows are in the JSON response, e.g. "data.items"; blank = the whole response.
    rows_path: str | None = Field(default=None, max_length=200)


class SqlDefinition(BaseModel):
    sql: str = Field(min_length=1, max_length=50_000)


class _QueryBody(BaseModel):
    kind: DataQueryKind
    connector_id: uuid.UUID | None = None
    definition: dict = {}
    parameters: list[DataQueryParam] = []

    @model_validator(mode="after")
    def _check(self):
        if self.kind == "rest":
            self.definition = RestDefinition(**self.definition).model_dump()
        else:
            if self.connector_id is None:
                raise ValueError("A SQL query needs a Database connector.")
            self.definition = SqlDefinition(**self.definition).model_dump()
        names = [p.name for p in self.parameters]
        if len(names) != len(set(names)):
            raise ValueError("Parameter names must be unique.")
        return self


class DataQueryCreate(_QueryBody):
    name: str = Field(min_length=1, max_length=200)
    description: str | None = Field(default=None, max_length=500)
    cache_seconds: int = Field(default=60, ge=0, le=86_400)
    role_ids: list[uuid.UUID] = []
    columns: list[str] = []


class DataQueryUpdate(BaseModel):
    name: str | None = Field(default=None, min_length=1, max_length=200)
    description: str | None = Field(default=None, max_length=500)
    kind: DataQueryKind | None = None
    connector_id: uuid.UUID | None = None
    definition: dict | None = None
    parameters: list[DataQueryParam] | None = None
    cache_seconds: int | None = Field(default=None, ge=0, le=86_400)
    role_ids: list[uuid.UUID] | None = None
    columns: list[str] | None = None
    # Refused (409) if someone else saved the query after this was loaded.
    expected_updated_at: datetime | None = None


class DataQueryTest(_QueryBody):
    """Run an unsaved query — the editor's "Test" button."""

    params: dict[str, Any] = {}


class DataQueryRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: uuid.UUID
    name: str
    description: str | None
    kind: DataQueryKind
    connector_id: uuid.UUID | None
    definition: dict
    parameters: list[DataQueryParam]
    columns: list[str]
    cache_seconds: int
    role_ids: list[uuid.UUID]
    created_at: datetime
    updated_at: datetime


class DataQueryOption(BaseModel):
    """What a page, dashboard or form designer needs to bind to a query."""

    model_config = ConfigDict(from_attributes=True)
    id: uuid.UUID
    name: str
    kind: DataQueryKind
    columns: list[str]
    parameters: list[DataQueryParam]


class DataQueryRunRequest(BaseModel):
    params: dict[str, Any] = {}
    limit: int = Field(default=500, ge=1, le=5000)


class DataQueryRunResult(BaseModel):
    columns: list[str]
    rows: list[dict]
    total: int
    cached: bool = False