"""Data Queries — REST / SQL sources for pages, dashboards, datasets and form
choice fields. Managing them is a Connectors permission; running one is
governed by the query's own roles (app/crud/data_queries.py)."""

import uuid

from fastapi import APIRouter, Depends, HTTPException, status
from pydantic import ValidationError
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.core.database import get_db
from app.core.permissions import deny_vendor, require_permission
from app.core.security import AuthContext, get_auth_context
from app.crud import data_queries
from app.crud.audit import write_audit_log
from app.crud.change_log import check_fresh
from app.models.data_query import DataQuery
from app.schemas.data_query import (
    DataQueryCreate,
    DataQueryOption,
    DataQueryRead,
    DataQueryRunRequest,
    DataQueryRunResult,
    DataQueryTest,
    DataQueryUpdate,
    _QueryBody,
)

router = APIRouter(prefix="/data-queries", tags=["data-queries"])


async def _get(db: AsyncSession, tenant_id: uuid.UUID, query_id: uuid.UUID) -> DataQuery:
    query = await data_queries.load(db, tenant_id, query_id)
    if query is None:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="Data query not found.")
    return query


async def _unique_name(db: AsyncSession, tenant_id: uuid.UUID, name: str, own_id: uuid.UUID | None = None) -> None:
    stmt = select(DataQuery.id).where(DataQuery.tenant_id == tenant_id, DataQuery.name == name)
    found = (await db.execute(stmt)).scalar_one_or_none()
    if found is not None and found != own_id:
        raise HTTPException(status_code=status.HTTP_409_CONFLICT, detail=f"A data query named {name} already exists.")


def _failed(exc: data_queries.DataQueryError) -> HTTPException:
    return HTTPException(status_code=422, detail=str(exc))


@router.get("", response_model=list[DataQueryRead])
async def list_queries(
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "view")),
    __: AuthContext = Depends(deny_vendor()),
) -> list[DataQuery]:
    return list((await db.execute(select(DataQuery).where(DataQuery.tenant_id == ctx.tenant_id).order_by(DataQuery.name))).scalars().all())


@router.get("/options", response_model=list[DataQueryOption])
async def list_query_options(
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    __: AuthContext = Depends(deny_vendor()),
) -> list[DataQuery]:
    """Names, columns and parameters only — for the page, dashboard and form
    designers' pickers, without the URL, headers or SQL."""
    return list((await db.execute(select(DataQuery).where(DataQuery.tenant_id == ctx.tenant_id).order_by(DataQuery.name))).scalars().all())


@router.get("/{query_id}", response_model=DataQueryRead)
async def get_query(
    query_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "view")),
    __: AuthContext = Depends(deny_vendor()),
) -> DataQuery:
    return await _get(db, ctx.tenant_id, query_id)


@router.post("", response_model=DataQueryRead, status_code=status.HTTP_201_CREATED)
async def create_query(
    payload: DataQueryCreate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "create")),
    __: AuthContext = Depends(deny_vendor()),
) -> DataQuery:
    name = payload.name.strip()
    await _unique_name(db, ctx.tenant_id, name)
    query = DataQuery(
        tenant_id=ctx.tenant_id,
        name=name,
        description=(payload.description or "").strip() or None,
        kind=payload.kind,
        connector_id=payload.connector_id,
        definition=payload.definition,
        parameters=[p.model_dump() for p in payload.parameters],
        columns=payload.columns,
        cache_seconds=payload.cache_seconds,
        role_ids=[str(r) for r in payload.role_ids],
        created_by=ctx.user_id,
        updated_by=ctx.user_id,
    )
    db.add(query)
    await db.flush()
    await db.refresh(query)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "data_query.create", "data_query", query.id, {"name": query.name})
    return query


@router.patch("/{query_id}", response_model=DataQueryRead)
async def update_query(
    query_id: uuid.UUID,
    payload: DataQueryUpdate,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "edit")),
    __: AuthContext = Depends(deny_vendor()),
) -> DataQuery:
    query = await _get(db, ctx.tenant_id, query_id)
    check_fresh(query, payload.expected_updated_at)
    fields = payload.model_fields_set - {"expected_updated_at"}
    if {"kind", "connector_id", "definition", "parameters"} & fields:
        try:
            body = _QueryBody(
                kind=payload.kind or query.kind,
                connector_id=payload.connector_id if "connector_id" in fields else query.connector_id,
                definition=payload.definition if payload.definition is not None else query.definition,
                parameters=payload.parameters if payload.parameters is not None else query.parameters,
            )
        except ValidationError as exc:
            raise HTTPException(status_code=422, detail=exc.errors(include_url=False, include_context=False)) from exc
        query.kind, query.connector_id, query.definition = body.kind, body.connector_id, body.definition
        query.parameters = [p.model_dump() for p in body.parameters]
    if payload.name is not None:
        await _unique_name(db, ctx.tenant_id, payload.name.strip(), query.id)
        query.name = payload.name.strip()
    if "description" in fields:
        query.description = (payload.description or "").strip() or None
    if payload.cache_seconds is not None:
        query.cache_seconds = payload.cache_seconds
    if payload.role_ids is not None:
        query.role_ids = [str(r) for r in payload.role_ids]
    if payload.columns is not None:
        query.columns = payload.columns
    query.updated_by = ctx.user_id
    await db.flush()
    await db.refresh(query)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "data_query.update", "data_query", query.id, {"name": query.name, "changed": sorted(fields)})
    return query


@router.delete("/{query_id}", status_code=status.HTTP_204_NO_CONTENT)
async def delete_query(
    query_id: uuid.UUID,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "delete")),
    __: AuthContext = Depends(deny_vendor()),
) -> None:
    query = await _get(db, ctx.tenant_id, query_id)
    await db.delete(query)
    await write_audit_log(db, ctx.tenant_id, ctx.user_id, "data_query.delete", "data_query", query.id, {"name": query.name})


@router.post("/test", response_model=DataQueryRunResult)
async def test_query(
    payload: DataQueryTest,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
    _: AuthContext = Depends(require_permission("connectors", "view")),
    __: AuthContext = Depends(deny_vendor()),
) -> DataQueryRunResult:
    """Runs an unsaved definition once (never cached) — first 50 rows."""
    try:
        result = await data_queries.execute(
            db, ctx.tenant_id, payload.kind, payload.definition, payload.connector_id, [p.model_dump() for p in payload.parameters], payload.params
        )
    except data_queries.DataQueryError as exc:
        raise _failed(exc) from exc
    return DataQueryRunResult(columns=result.columns, rows=result.rows[:50], total=len(result.rows))


@router.post("/{query_id}/run", response_model=DataQueryRunResult)
async def run_query(
    query_id: uuid.UUID,
    payload: DataQueryRunRequest,
    ctx: AuthContext = Depends(get_auth_context),
    db: AsyncSession = Depends(get_db),
) -> DataQueryRunResult:
    query = await _get(db, ctx.tenant_id, query_id)
    if not await data_queries.can_run(db, ctx.tenant_id, ctx.user_id, query, vendor=ctx.vendor_id is not None):
        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Your role can't run this data query.")
    try:
        result = await data_queries.run(db, query, payload.params)
    except data_queries.DataQueryError as exc:
        raise _failed(exc) from exc
    return DataQueryRunResult(columns=result.columns, rows=result.rows[: payload.limit], total=len(result.rows), cached=result.cached)