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

from pydantic import BaseModel, ConfigDict, Field, field_validator

ReportSource = Literal["workflow_instances", "form_submissions", "approval_log", "sla_compliance"]
ReportFrequency = Literal["one_time", "daily", "weekly", "monthly"]
ConnectorType = Literal["email_smtp", "email_imap", "realtime_notification", "webhook", "sms", "payment_gateway", "rpa", "database", "file_transfer", "google", "firebase"]
ConnectorStatus = Literal["enabled", "disabled"]
# "query" = a Data Query (app/crud/data_queries.py); source_id is its id.
DatasetSourceType = Literal["master", "form", "query"]
DatasetSortDir = Literal["asc", "desc"]
DatasetOperator = Literal[
    "equals", "not_equals", "gt", "gte", "lt", "lte", "in", "contains", "is_empty", "is_not_empty", "between",
    "not_in", "not_contains", "starts_with", "ends_with", "is_true", "is_false",
    # A relative date: value is a preset ("today", "this_month"…) or
    # {"preset": "last_n_days", "n": 7} — see app/crud/filter_tree.py.
    "date_is",
]
DatasetAggregateFn = Literal["count", "sum", "avg", "min", "max"]
DatasetDateBucket = Literal["none", "day", "week", "month"]


class ReportCreate(BaseModel):
    name: str
    source: ReportSource
    columns: list[str] = []
    filters: dict = {}
    schedule_frequency: ReportFrequency | None = None
    schedule_recipients: list[str] | None = None


class ReportUpdate(BaseModel):
    name: str | None = None
    columns: list[str] | None = None
    filters: dict | None = None
    schedule_frequency: ReportFrequency | None = None
    schedule_recipients: list[str] | None = None


class ReportRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: uuid.UUID
    tenant_id: uuid.UUID
    name: str
    source: ReportSource
    columns: list[str]
    filters: dict
    schedule_frequency: ReportFrequency | None
    schedule_recipients: list[str] | None
    created_by: uuid.UUID | None
    last_run_at: datetime | None
    created_at: datetime
    updated_at: datetime


class ConnectorCreate(BaseModel):
    name: str
    type: ConnectorType
    configuration: dict = {}
    # New connectors start disabled unless the creator switches them on.
    status: ConnectorStatus = "disabled"


class ConnectorUpdate(BaseModel):
    name: str | None = None
    configuration: dict | None = None
    status: ConnectorStatus | None = None


class ConnectorRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: uuid.UUID
    tenant_id: uuid.UUID
    name: str
    type: ConnectorType
    configuration: dict
    status: ConnectorStatus
    last_tested_at: datetime | None
    last_test_result: str | None
    created_at: datetime
    updated_at: datetime


class ConnectorTestResult(BaseModel):
    success: bool
    detail: str


class DatasetFilter(BaseModel):
    field: str
    operator: DatasetOperator
    value: Any = None


class DatasetFilterGroup(BaseModel):
    """Conditions joined by AND ("all") or OR ("any"); groups nest. A filter
    list itself is "all" — see app/crud/filter_tree.py."""

    match: Literal["all", "any"] = "all"
    filters: list["DatasetFilterItem"] = Field(default_factory=list)


DatasetFilterItem = Union[DatasetFilter, DatasetFilterGroup]
DatasetFilterGroup.model_rebuild()


class DatasetJoin(BaseModel):
    """Another datasource's or form's records joined onto each row: rows whose
    `on_field` matches the joined record's `join_field` ("id" — the record
    itself — by default) get its `columns` as "<alias>.<column>". A "left"
    join keeps rows with no match (their joined columns blank); "inner"
    drops them. match "first" takes the earliest matching record; "all"
    repeats the row for each. Text matches without case or surrounding
    spaces. The joined records are read with the viewer's own access, and
    `filters` narrow them first. Joins run in order, so a later one can match
    on an earlier one's column ("office.district_id")."""

    alias: str = Field(pattern=r"^[A-Za-z_][A-Za-z0-9_]{0,39}$")
    source_type: Literal["master", "form"]
    source_id: uuid.UUID
    on_field: str = Field(min_length=1, max_length=200)
    join_field: str = Field(default="id", min_length=1, max_length=200)
    type: Literal["left", "inner"] = "left"
    match: Literal["first", "all"] = "first"
    columns: list[str] = []
    filters: list["DatasetFilterItem"] = []


class DatasetComputedColumn(BaseModel):
    """A calculated column: an expression (app/core/expr.py) worked out for
    each row from its other columns — "{amount} * 1.18", "DATEDIFF({due},
    TODAY())", or a linked record's field as {application_id.office}. It can
    be shown, filtered, sorted and grouped by like any other column."""

    name: str = Field(pattern=r"^[A-Za-z_][A-Za-z0-9_]{0,59}$")
    expression: str = Field(min_length=1, max_length=2000)

    @field_validator("expression")
    @classmethod
    def _valid(cls, v: str) -> str:
        from app.core.expr import check_expression

        check_expression(v)
        return v


class DatasetCreate(BaseModel):
    name: str
    description: str | None = None
    source_type: DatasetSourceType
    source_id: uuid.UUID
    columns: list[str] = []
    filters: list[DatasetFilterItem] = []
    sort_field: str | None = None
    sort_dir: DatasetSortDir = "asc"
    group_by: str | None = None
    aggregate_field: str | None = None  # ignored when aggregate_fn is "count"
    aggregate_fn: DatasetAggregateFn | None = None
    computed: list[DatasetComputedColumn] = []
    joins: list[DatasetJoin] = []


class DatasetUpdate(BaseModel):
    name: str | None = None
    description: str | None = None
    columns: list[str] | None = None
    filters: list[DatasetFilterItem] | None = None
    sort_field: str | None = None
    sort_dir: DatasetSortDir | None = None
    group_by: str | None = None
    aggregate_field: str | None = None
    aggregate_fn: DatasetAggregateFn | None = None
    computed: list[DatasetComputedColumn] | None = None
    joins: list[DatasetJoin] | None = None


class DatasetRead(BaseModel):
    model_config = ConfigDict(from_attributes=True)
    id: uuid.UUID
    tenant_id: uuid.UUID
    name: str
    description: str | None
    source_type: DatasetSourceType
    source_id: uuid.UUID
    columns: list[str]
    filters: list[DatasetFilterItem]
    sort_field: str | None
    sort_dir: DatasetSortDir
    group_by: str | None
    aggregate_field: str | None
    aggregate_fn: DatasetAggregateFn | None
    computed: list[DatasetComputedColumn] = []
    joins: list[DatasetJoin] = []
    created_by: uuid.UUID | None
    created_at: datetime
    updated_at: datetime


class DatasetQueryRequest(BaseModel):
    """An unsaved, ad-hoc run of a query — the Data Explorer's "Run" button,
    before the user decides to save it as a Dataset."""

    source_type: DatasetSourceType
    source_id: uuid.UUID
    columns: list[str] = []
    filters: list[DatasetFilterItem] = []
    sort_field: str | None = None
    sort_dir: DatasetSortDir = "asc"
    group_by: str | None = None
    aggregate_field: str | None = None
    aggregate_fn: DatasetAggregateFn | None = None
    limit: int = Field(200, ge=0, le=50_000)  # record_sql.MAX_PYTHON_ROWS
    # Custom-expression KPI value (FR-DSH custom expressions, Phase 4) — when
    # set, takes priority over group_by/aggregate_fn: evaluated once against
    # the whole filtered row set, see app/crud/dataset_query.py::resolve_rows.
    expression: str | None = None
    computed: list[DatasetComputedColumn] = []
    joins: list[DatasetJoin] = []


class DatasetQueryResult(BaseModel):
    columns: list[str]
    rows: list[dict]
    total: int  # matched-row count before `limit` truncation


class DatasetSeriesDef(BaseModel):
    field: str | None = None
    aggregate: DatasetAggregateFn | None = None
    label: str | None = None
    expression: str | None = None


class DatasetSeriesQueryRequest(BaseModel):
    """A Dashboard/Page chart widget's axis-configured query (Phase 1) — one
    X field (optionally date-bucketed) and one or more Y series, each a
    field+aggregate or a custom expression. See
    app/crud/dataset_query.py::resolve_grouped_series."""

    source_type: DatasetSourceType
    source_id: uuid.UUID
    filters: list[DatasetFilterItem] = []
    x_field: str
    x_bucket: DatasetDateBucket = "none"
    series: list[DatasetSeriesDef]
    limit: int = Field(200, ge=0, le=50_000)  # record_sql.MAX_PYTHON_ROWS
    computed: list[DatasetComputedColumn] = []
    joins: list[DatasetJoin] = []


class DatasetSeriesQueryResult(BaseModel):
    rows: list[dict]  # [{"x": <bucket label>, "<series label>": <number>, ...}, ...]
    series: list[str]  # series labels, in the same order as the request's `series`


class AiQueryRequest(BaseModel):
    prompt: str


class AiQueryResult(BaseModel):
    """Result of the Data Explorer's AI Query assistant: the AI picks a
    table from the tenant's actual Masters/Forms and drafts a query against
    it — this both reports what it built (so the Data Explorer's builder can
    be pre-filled and further edited) and the resolved rows (so results show
    immediately, without a second round trip)."""

    message: str
    source_type: DatasetSourceType | None = None
    source_id: uuid.UUID | None = None
    source_name: str | None = None
    columns: list[str] = []
    filters: list[DatasetFilterItem] = []
    sort_field: str | None = None
    sort_dir: DatasetSortDir = "asc"
    group_by: str | None = None
    aggregate_field: str | None = None
    aggregate_fn: DatasetAggregateFn | None = None
    suggested_name: str | None = None
    rows: list[dict] = []
    result_columns: list[str] = []
    total: int = 0
    error: str | None = None  # set when the AI couldn't map the request to any table
    provider_ok: bool = True  # false only when the LLM provider itself was unreachable/malformed —
    # a legitimate "couldn't find a matching table" reply still means the provider is healthy

DatasetJoin.model_rebuild()