"""Run a registered evidence query and freeze the result.

The service is deliberately thin: validate the request against the
catalogue, call the handler, persist what came back, write an audit row.
All the judgement lives in the handler (which facts) and the catalogue
(which questions exist).

Three properties this layer is responsible for:

* **Frozen, not live.** Rows are persisted, never recomputed on read.
  A bundle an external auditor opens in November must show what it
  showed in August.
* **Every row cites something.** A row without provenance is rejected
  here rather than stored, because an unciteable line in an evidence
  pack is indistinguishable from a checkable one to the reader.
* **Asking is itself audited.** Running an evidence query writes an
  ``auditor.evidence_query.run`` audit row. An access-forensics tool
  that leaves no trace of who searched for whom is a governance hole in
  its own right.
"""
from __future__ import annotations

import time
import uuid
from datetime import UTC, date, datetime, timedelta
from typing import Any

from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession

from app.audit.service import AuditService
from app.core.exceptions import AppException
from app.domains.auditor.evidence_query.catalog import get_query, public_catalog
from app.domains.auditor.evidence_query.contracts import (
    EvidenceQueryDefinition,
    EvidenceResult,
    EvidenceRow,
    canonical_params,
    content_hash,
)
from app.domains.auditor.models import (
    AuditorEvidenceBundle,
    AuditorEvidenceBundleRow,
)

# An evidence query is a read of Helix's own ledgers, but an unbounded
# one can still pull a whole domain into memory. The cap is high enough
# that no legitimate roster at this company's size approaches it, and
# exceeding it is reported as a hard error rather than a silent
# truncation — a truncated roster reads exactly like a complete one.
MAX_EVIDENCE_ROWS = 20000

GENERATED_VIA_MANUAL = "manual"
GENERATED_VIA_SCHEDULED = "scheduled"


def list_catalog() -> list[dict[str, Any]]:
    return public_catalog()


def _is_date_like(value: Any) -> bool:
    """Does this parse as a date or an ISO timestamp?"""
    if isinstance(value, (datetime, date)):
        return True
    text = str(value)
    try:
        datetime.fromisoformat(text)
        return True
    except ValueError:
        pass
    try:
        datetime.strptime(text, "%Y-%m-%d")
        return True
    except ValueError:
        return False


def _validate_params(
    definition: EvidenceQueryDefinition, params: dict[str, Any],
) -> dict[str, Any]:
    """Reject unknown and missing parameters before touching the DB.

    Unknown keys are refused rather than ignored: a typo'd parameter
    that is silently dropped produces a *wider* roster than the auditor
    asked for, and nothing in the output would reveal it.
    """
    declared = {p.name: p for p in definition.params}
    unknown = sorted(set(params) - set(declared))
    if unknown:
        raise AppException(
            code="EVIDENCE_QUERY_UNKNOWN_PARAM",
            message=(
                f"Unknown parameter(s) for {definition.key!r}: "
                f"{', '.join(unknown)}"
            ),
            status_code=400,
        )
    # Date-shaped parameters are validated HERE, before any handler
    # touches them. ``_as_datetime`` falls through to ``strptime`` with
    # no guard, so "2026-13-45" escaped as an uncaught ValueError from
    # inside the handler — a 500 for what is plainly a bad request, and
    # one an auditor could trigger by typing.
    bad_dates = sorted(
        name for name, spec in declared.items()
        if spec.kind == "date" and params.get(name) not in (None, "")
        and not _is_date_like(params[name])
    )
    if bad_dates:
        raise AppException(
            code="EVIDENCE_QUERY_BAD_DATE",
            message=(
                f"Parameter(s) for {definition.key!r} are not a valid "
                f"date or ISO-8601 timestamp: {', '.join(bad_dates)}"
            ),
            status_code=400,
        )
    missing = sorted(
        name for name, spec in declared.items()
        if spec.required and params.get(name) in (None, "")
    )
    if missing:
        raise AppException(
            code="EVIDENCE_QUERY_MISSING_PARAM",
            message=(
                f"Missing required parameter(s) for {definition.key!r}: "
                f"{', '.join(missing)}"
            ),
            status_code=400,
        )
    clean = {k: v for k, v in params.items() if v not in (None, "")}
    # Materialise omitted upper bounds. "Who holds this role" with no
    # explicit ``at`` means "as of now", and leaving that implicit would
    # make two runs of identical params hash differently for a perfectly
    # ordinary reason — destroying the signal that a hash difference is
    # supposed to carry.
    run_time = _run_time_upper_bound().isoformat()
    for name, spec in declared.items():
        if spec.fill_with_run_time and name not in clean:
            clean[name] = run_time
    return clean


def _run_time_upper_bound() -> datetime:
    """"Now", rounded UP to the next whole second.

    Most lifecycle timestamps live in second-granular ``DATETIME``
    columns, and MySQL/TiDB **round** rather than truncate on insert:
    a seat allocated at ``12:00:01.618`` is stored as ``12:00:02``. An
    upper bound of the exact current instant would therefore exclude a
    grant that was just made — "the row I created a second ago is
    missing from the roster" is precisely the kind of result that makes
    an auditor stop trusting the tool, and it would have surfaced as an
    intermittent test failure rather than an obvious bug.

    Rounding the bound up costs at most one second of look-ahead, which
    cannot include anything that has not already happened.
    """
    now = datetime.now(UTC)
    if now.microsecond:
        now = now.replace(microsecond=0) + timedelta(seconds=1)
    return now


def _period_bounds(
    params: dict[str, Any],
) -> tuple[datetime | None, datetime | None]:
    """Lift the window out of params onto the bundle header.

    Denormalised so the evidence-pack builder and the control-mapping
    cron can filter bundles by period without parsing each query's own
    parameter vocabulary.
    """
    def _coerce(value: Any) -> datetime | None:
        """Always UTC-aware. Naive means UTC; aware is CONVERTED to it.

        The period columns are ``DATETIME``, which stores a wall clock
        and no offset — so an aware ``+08:00`` value written straight
        through lands eight hours off and every later comparison, the
        evidence-pack window included, is against the wrong instant. The
        web UI never produced one, but MCP, the CLI and curl all accept
        an ISO string with an offset, so preserving it here was a
        silent trap rather than a nicety.
        """
        if value is None:
            return None
        if isinstance(value, datetime):
            return (
                value.astimezone(UTC) if value.tzinfo
                else value.replace(tzinfo=UTC)
            )
        if isinstance(value, date):
            return datetime.combine(value, datetime.min.time(), tzinfo=UTC)
        try:
            parsed = datetime.fromisoformat(str(value))
        except ValueError:
            return None
        return (
            parsed.astimezone(UTC) if parsed.tzinfo
            else parsed.replace(tzinfo=UTC)
        )

    start = _coerce(params.get("period_start"))
    end = _coerce(params.get("period_end"))
    if start is None and end is None:
        at = _coerce(params.get("at"))
        if at is not None:
            return at, at
        # An offboarding archive is person-scoped, so it declares no
        # period — but it DOES know which departure it belongs to.
        # Without this the bundle has no coverage axis at all and falls
        # back to when it was written, which puts a 29 May leaver whose
        # offboarding completed on 3 June into the JUNE evidence pack.
        # The cycle is the leaver's last day: the month the evidence is
        # actually about.
        cycle = _coerce(params.get("offboarding_cycle"))
        if cycle is not None:
            return cycle, cycle
    return start, end


async def run_evidence_query(
    db: AsyncSession,
    *,
    query_key: str,
    params: dict[str, Any],
    actor_user_id: uuid.UUID | None,
    trace_id: str | uuid.UUID | None = None,
    generated_via: str = GENERATED_VIA_MANUAL,
) -> AuditorEvidenceBundle:
    """Execute one catalogued query and persist an immutable bundle."""
    definition = get_query(query_key)
    if definition is None:
        raise AppException(
            code="EVIDENCE_QUERY_NOT_FOUND",
            message=f"No evidence query registered under {query_key!r}",
            status_code=404,
        )

    clean_params = _validate_params(definition, params)
    started = time.monotonic()
    result = await definition.handler(db, clean_params)
    if not isinstance(result, EvidenceResult):  # pragma: no cover - guard
        raise AppException(
            code="EVIDENCE_QUERY_BAD_HANDLER",
            message=(
                f"Handler for {query_key!r} returned "
                f"{type(result).__name__}, expected EvidenceResult"
            ),
            status_code=500,
        )
    duration_ms = int((time.monotonic() - started) * 1000)

    if len(result.rows) > MAX_EVIDENCE_ROWS:
        raise AppException(
            code="EVIDENCE_QUERY_TOO_LARGE",
            message=(
                f"{query_key!r} produced {len(result.rows)} rows, above the "
                f"{MAX_EVIDENCE_ROWS} cap. Narrow the period or the subject "
                "— a truncated roster is indistinguishable from a complete "
                "one and will not be stored."
            ),
            status_code=400,
        )

    uncited = [
        index for index, row in enumerate(result.rows)
        if not row.has_provenance()
    ]
    if uncited:
        raise AppException(
            code="EVIDENCE_ROW_WITHOUT_PROVENANCE",
            message=(
                f"Handler for {query_key!r} emitted {len(uncited)} row(s) "
                "with neither a source table/pk nor an audit log id. Every "
                "evidence row must be traceable to a record."
            ),
            status_code=500,
        )

    canonical = canonical_params(clean_params)
    period_start, period_end = _period_bounds(clean_params)
    trace_uuid = _coerce_uuid(trace_id)

    bundle = AuditorEvidenceBundle(
        query_key=definition.key,
        query_version=definition.version,
        params=canonical,
        period_start=period_start,
        period_end=period_end,
        row_count=len(result.rows),
        content_hash=content_hash(result.rows),
        data_gaps=[gap.as_dict() for gap in result.data_gaps] or None,
        generated_by_person_id=actor_user_id,
        generated_via=generated_via,
        trace_id=trace_uuid,
        duration_ms=duration_ms,
    )
    db.add(bundle)
    await db.flush()

    for seq, row in enumerate(result.rows):
        db.add(_persist_row(bundle.id, seq, row))
    await db.flush()

    await AuditService(db).log(
        trace_id=str(trace_uuid) if trace_uuid else str(uuid.uuid4()),
        actor_type="user" if actor_user_id else "system",
        actor_user_id=actor_user_id,
        action="auditor.evidence_query.run",
        status="success",
        resource_type="auditor_evidence_bundle",
        resource_id=str(bundle.id),
        detail={
            "query_key": definition.key,
            "query_version": definition.version,
            "params": canonical,
            "row_count": len(result.rows),
            "content_hash": bundle.content_hash,
            "generated_via": generated_via,
            "data_gaps": [gap.code for gap in result.data_gaps],
        },
    )
    return bundle


def _coerce_uuid(value: str | uuid.UUID | None) -> uuid.UUID | None:
    if value is None:
        return None
    if isinstance(value, uuid.UUID):
        return value
    try:
        return uuid.UUID(str(value))
    except (TypeError, ValueError):
        return None


def _persist_row(
    bundle_id: uuid.UUID, seq: int, row: EvidenceRow,
) -> AuditorEvidenceBundleRow:
    return AuditorEvidenceBundleRow(
        bundle_id=bundle_id,
        seq=seq,
        subject_type=row.subject_type,
        subject_id=(str(row.subject_id)[:64] if row.subject_id else None),
        subject_label=(
            str(row.subject_label)[:255] if row.subject_label else None
        ),
        source_table=row.source_table,
        source_pk=(str(row.source_pk)[:64] if row.source_pk else None),
        audit_log_id=_coerce_uuid(row.audit_log_id),
        source_trace_id=_coerce_uuid(row.source_trace_id),
        occurred_at=row.occurred_at,
        payload=row.payload,
    )


async def get_bundle(
    db: AsyncSession, bundle_id: uuid.UUID,
) -> AuditorEvidenceBundle | None:
    return await db.get(AuditorEvidenceBundle, bundle_id)


async def list_bundle_rows(
    db: AsyncSession,
    bundle_id: uuid.UUID,
    *,
    page: int = 1,
    page_size: int = 50,
) -> tuple[list[AuditorEvidenceBundleRow], int]:
    total = (
        await db.execute(
            select(func.count())
            .select_from(AuditorEvidenceBundleRow)
            .where(AuditorEvidenceBundleRow.bundle_id == bundle_id)
        )
    ).scalar_one()
    rows = (
        await db.execute(
            select(AuditorEvidenceBundleRow)
            .where(AuditorEvidenceBundleRow.bundle_id == bundle_id)
            .order_by(AuditorEvidenceBundleRow.seq)
            .offset(max(page - 1, 0) * page_size)
            .limit(page_size)
        )
    ).scalars().all()
    return list(rows), int(total)


__all__ = [
    "GENERATED_VIA_MANUAL",
    "GENERATED_VIA_SCHEDULED",
    "MAX_EVIDENCE_ROWS",
    "get_bundle",
    "list_bundle_rows",
    "list_catalog",
    "run_evidence_query",
]