"""Privacy-safe, structured task trace projection for the ARR portal."""

from __future__ import annotations

from datetime import date, datetime, timezone
from typing import Any, Dict, Iterable, List, Mapping, Optional, Sequence


ACTIVE_RUN_STATUSES = {"received", "queued", "running", "validating", "awaiting_review"}

_CURRENT_STAGE = {
    "received": "upload",
    "queued": "processor",
    "running": "processor",
    "validating": "validation",
    "awaiting_review": "review",
    "accepted": "database",
    "rejected": "validation",
    "failed": "processor",
    "cancelled": "processor",
}

_CURRENT_COPY = {
    "received": ("当前：文件已接收", "等待创建程序化处理任务。"),
    "queued": ("当前：等待程序处理", "任务已进入 ARR 处理队列。"),
    "running": ("当前：ARR 正在处理", "固定处理器正在生成日报和完整结构化结果。"),
    "validating": ("当前：正在独立验收", "ARR 正在复核文件身份、业务结果并准备数据库提交。"),
    "awaiting_review": ("当前：等待人工定价", "请完成全部缺价项后确认生成正式日报。"),
}

_LOG_ORDER = {
    "UPLOAD_REGISTERED": 10,
    "ATTEMPT_QUEUED": 20,
    "PROCESSOR_STARTED": 30,
    "ARTIFACT_RESULT_RECEIVED": 40,
    "DIRECT_RESULT_RECEIVED": 40,
    "ARTIFACT_RESULT_VALIDATED": 50,
    "ARTIFACT_RESULT_RECORDED_FOR_REVIEW": 55,
    "PRICE_REVIEW_REQUIRED": 56,
    "PRICE_REVIEW_UPDATED": 57,
    "PRICE_REVIEW_FINALIZED": 58,
    "PRICE_REVIEW_GENERATION_FAILED": 59,
    "PRICE_REVIEW_FAILED": 60,
    "PRICE_REVIEW_CANCELLED": 61,
    "DIRECT_VALIDATION_STARTED": 50,
    "FINANCE_VERSION_CREATED": 60,
    "FINANCE_VERSION_VALIDATED": 70,
    "FINANCE_VERSION_ACTIVATED": 80,
    "ARTIFACT_RESULT_COMMITTED": 85,
    "DIRECT_RESULT_COMMITTED": 85,
    "DIRECT_RESULT_ALREADY_COMMITTED": 85,
    "ATTEMPT_SUCCEEDED": 90,
    "JOB_SUCCEEDED": 95,
    "DOWNSTREAM_FAILURE_QUEUED": 100,
    "DOWNSTREAM_EVENT_QUEUED": 100,
    "DOWNSTREAM_EVENT_PUBLISHED": 110,
}


def _iso(value: Any) -> Optional[str]:
    if value is None:
        return None
    if isinstance(value, (date, datetime)):
        return value.isoformat()
    return str(value)


def _safe_text(value: Any, maximum: int = 500) -> Optional[str]:
    if value is None:
        return None
    normalized = " ".join(str(value).split())
    return normalized[:maximum] if normalized else None


def _safe_details(values: Mapping[str, Any]) -> Dict[str, Any]:
    output: Dict[str, Any] = {}
    for key, value in values.items():
        if value is None or value == "":
            continue
        if isinstance(value, (date, datetime)):
            output[key] = value.isoformat()
        elif isinstance(value, str):
            output[key] = _safe_text(value, 256)
        elif isinstance(value, (bool, int, float)):
            output[key] = value
    return output


def _timestamp_key(value: str) -> float:
    try:
        parsed = datetime.fromisoformat(value.replace("Z", "+00:00"))
        if parsed.tzinfo is None:
            parsed = parsed.replace(tzinfo=timezone.utc)
        return parsed.astimezone(timezone.utc).timestamp()
    except (TypeError, ValueError):
        return 0.0


def _failure_stage(code: Any) -> str:
    normalized = str(code or "").upper()
    if normalized in {"PROCESSING_RESULT_MISSING", "SUBMISSION_EXPIRED"}:
        return "processor"
    if any(token in normalized for token in ("SUBMISSION", "CALLBACK", "DELIVERY")):
        return "validation"
    if any(
        token in normalized
        for token in ("VALIDATION", "CONTRACT", "SCHEMA", "MISMATCH", "SIGNATURE")
    ):
        return "validation"
    if "DATABASE" in normalized:
        return "database"
    if any(token in normalized for token in ("UPLOAD", "OBJECT_STORE", "SOURCE")):
        return "upload"
    if any(token in normalized for token in ("DISPATCH", "TRANSPORT", "REMOTE", "AGENT", "PROCESSOR")):
        return "processor"
    return "processor"


def _execution_metadata(delivery_mode: Any) -> Dict[str, str]:
    """Describe ARR execution without exposing host or network location."""
    normalized = str(delivery_mode or "").strip().lower()
    if normalized == "artifact_callback":
        return {
            "execution_scope": "arr_runtime",
            "processor_mode": "fixed_processor",
            "remote_dispatch": "none",
        }
    if normalized == "direct_mcp":
        return {
            "execution_scope": "remote_agent",
            "processor_mode": "legacy_direct_mcp",
            "remote_dispatch": "mcp",
        }
    return {
        "execution_scope": "unknown",
        "processor_mode": "unknown",
        "remote_dispatch": "unknown",
    }


def _append_log(
    logs: List[Dict[str, Any]],
    *,
    log_id: str,
    timestamp: Any,
    stage: str,
    level: str,
    code: str,
    title: str,
    message: str,
    details: Optional[Mapping[str, Any]] = None,
) -> None:
    resolved_timestamp = _iso(timestamp)
    if not resolved_timestamp:
        return
    item: Dict[str, Any] = {
        "id": log_id,
        "timestamp": resolved_timestamp,
        "stage": stage,
        "level": level,
        "code": code,
        "title": title,
        "message": _safe_text(message, 1_200) or "",
        "_order": _LOG_ORDER.get(code, 85),
        "_sequence": len(logs),
    }
    safe_details = _safe_details(details or {})
    if safe_details:
        item["details"] = safe_details
    logs.append(item)


def _latest(values: Sequence[Mapping[str, Any]], key: str) -> Optional[Mapping[str, Any]]:
    if not values:
        return None
    return max(values, key=lambda item: int(item.get(key) or 0))


def _attempt_logs(
    logs: List[Dict[str, Any]],
    attempts: Sequence[Mapping[str, Any]],
    review_case: Optional[Mapping[str, Any]],
) -> None:
    for attempt in attempts:
        number = int(attempt.get("attempt_no") or 0)
        details = {
            "attempt_no": number,
            "attempt_status": attempt.get("attempt_status"),
            "remote_run_id": attempt.get("remote_run_id"),
        }
        _append_log(
            logs,
            log_id=f"attempt.{number}.queued",
            timestamp=attempt.get("created_at"),
            stage="processor",
            level="info",
            code="ATTEMPT_QUEUED",
            title=f"处理尝试 #{number} 已创建",
            message="任务已登记到 ARR 程序化处理队列。",
            details=details,
        )
        if attempt.get("started_at"):
            _append_log(
                logs,
                log_id=f"attempt.{number}.started",
                timestamp=attempt.get("started_at"),
                stage="processor",
                level="info",
                code="PROCESSOR_STARTED",
                title="固定处理器已启动",
                message="ARR 已开始处理已登记的源 XML。",
                details=details,
            )
        status = str(attempt.get("attempt_status") or "")
        retryable_review_failure = (
            status == "failed"
            and str((review_case or {}).get("case_status") or "")
            == "generation_failed"
        )
        if retryable_review_failure:
            # The review audit event records this as a retryable warning.  It is
            # deliberately not presented as a terminal processing failure.
            continue
        if status in {"failed", "cancelled"} and attempt.get("finished_at"):
            failure_code = attempt.get("failure_code")
            _append_log(
                logs,
                log_id=f"attempt.{number}.{status}",
                timestamp=attempt.get("finished_at"),
                stage=_failure_stage(failure_code),
                level="error" if status == "failed" else "warning",
                code=str(failure_code or f"ATTEMPT_{status.upper()}"),
                title="程序化处理失败" if status == "failed" else "程序化处理已取消",
                message=(
                    _safe_text(attempt.get("failure_message"))
                    or "程序化处理尝试未完成。"
                ),
                details={**details, "failure_code": failure_code},
            )
        elif status == "succeeded" and attempt.get("finished_at"):
            _append_log(
                logs,
                log_id=f"attempt.{number}.succeeded",
                timestamp=attempt.get("finished_at"),
                stage="database",
                level="success",
                code="ATTEMPT_SUCCEEDED",
                title="处理尝试已闭环",
                message="ARR 固定处理器输出已通过验收并完成业务提交。",
                details=details,
            )


def _delivery_logs(
    logs: List[Dict[str, Any]], deliveries: Sequence[Mapping[str, Any]]
) -> None:
    for index, delivery in enumerate(deliveries, 1):
        identity = delivery.get("delivery_key") or index
        details = {
            "delivery_id": delivery.get("delivery_key"),
            "attempt_no": delivery.get("attempt_no"),
            "delivery_status": delivery.get("delivery_status"),
            "result_status": delivery.get("result_status"),
            "processor_version": delivery.get("processor_version"),
            "business_date": delivery.get("business_date"),
            "daily_version_id": delivery.get("daily_version_id"),
        }
        _append_log(
            logs,
            log_id=f"delivery.{identity}.received",
            timestamp=delivery.get("received_at"),
            stage="validation",
            level="success",
            code="ARTIFACT_RESULT_RECEIVED",
            title="程序输出制品已登记",
            message="ARR 已登记固定处理器输出制品并开始独立验收。",
            details=details,
        )
        if delivery.get("validated_at"):
            _append_log(
                logs,
                log_id=f"delivery.{identity}.validated",
                timestamp=delivery.get("validated_at"),
                stage="validation",
                level="success",
                code="ARTIFACT_RESULT_VALIDATED",
                title="结果制品已通过验收",
                message="处理器版本、规则、文件身份与业务结果已完成复核。",
                details=details,
            )
        status = str(delivery.get("delivery_status") or "")
        if delivery.get("committed_at"):
            review_recorded = (
                status == "recorded_review"
                and str(delivery.get("result_status") or "") == "review_required"
            )
            failed = status in {"recorded_failure", "rejected"}
            failure_code = delivery.get("failure_code")
            _append_log(
                logs,
                log_id=f"delivery.{identity}.finished",
                timestamp=delivery.get("committed_at"),
                stage="validation" if failed else "review" if review_recorded else "database",
                level="error" if failed else "info" if review_recorded else "success",
                code=str(
                    failure_code
                    or (
                        "ARTIFACT_RESULT_REJECTED"
                        if failed
                        else "ARTIFACT_RESULT_RECORDED_FOR_REVIEW"
                        if review_recorded
                        else "ARTIFACT_RESULT_COMMITTED"
                    )
                ),
                title=(
                    "结果制品未通过验收"
                    if failed
                    else "待人工定价已登记"
                    if review_recorded
                    else "结果制品已提交数据库"
                ),
                message=(
                    _safe_text(delivery.get("failure_message"))
                    if failed
                    else "缺价项已登记，尚未创建 Finance 日报版本。"
                    if review_recorded
                    else "验收结果已原子提交到 Finance 数据库。"
                )
                or "结果制品未通过验收。",
                details={**details, "failure_code": failure_code},
            )


def _submission_logs(
    logs: List[Dict[str, Any]], submissions: Sequence[Mapping[str, Any]]
) -> None:
    for index, submission in enumerate(submissions, 1):
        identity = submission.get("submission_key") or index
        details = {
            "submission_id": submission.get("submission_key"),
            "attempt_no": submission.get("attempt_no"),
            "submission_status": submission.get("submission_status"),
            "processor_version": submission.get("processor_version"),
            "business_date": submission.get("business_date"),
            "record_count": submission.get("record_count"),
            "daily_version_id": submission.get("daily_version_id"),
        }
        _append_log(
            logs,
            log_id=f"submission.{identity}.received",
            timestamp=submission.get("created_at"),
            stage="validation",
            level="success",
            code="DIRECT_RESULT_RECEIVED",
            title="兼容结构化结果已接收",
            message="ARR 已接收历史兼容链路提交的结构化结果。",
            details=details,
        )
        if submission.get("validation_started_at"):
            _append_log(
                logs,
                log_id=f"submission.{identity}.validating",
                timestamp=submission.get("validation_started_at"),
                stage="validation",
                level="info",
                code="DIRECT_VALIDATION_STARTED",
                title="独立结果验收已开始",
                message="ARR 正在重放确定性规则并比对业务事实。",
                details=details,
            )
        status = str(submission.get("submission_status") or "")
        if submission.get("finished_at"):
            succeeded = status in {"committed", "already_committed"}
            failure_code = submission.get("failure_code")
            _append_log(
                logs,
                log_id=f"submission.{identity}.finished",
                timestamp=submission.get("finished_at"),
                stage="database" if succeeded else _failure_stage(failure_code),
                level="success" if succeeded else "error",
                code=str(
                    "DIRECT_RESULT_ALREADY_COMMITTED"
                    if status == "already_committed"
                    else "DIRECT_RESULT_COMMITTED"
                    if succeeded
                    else failure_code or f"DIRECT_RESULT_{status.upper()}"
                ),
                title=(
                    "相同结果已提交过"
                    if status == "already_committed"
                    else "结构化结果已提交数据库"
                    if succeeded
                    else "结构化结果未通过验收"
                ),
                message=(
                    "已返回同一任务的既有数据库回执。"
                    if status == "already_committed"
                    else "验证通过的业务事实已原子提交到 Finance 数据库。"
                    if succeeded
                    else _safe_text(submission.get("failure_message"))
                    or "结构化结果未通过 ARR 独立验收。"
                ),
                details={**details, "failure_code": failure_code},
            )


def _version_logs(logs: List[Dict[str, Any]], versions: Sequence[Mapping[str, Any]]) -> None:
    for version in versions:
        identity = version.get("daily_version_id")
        details = {
            "daily_version_id": identity,
            "business_date": version.get("business_date"),
            "version_no": version.get("version_no"),
            "version_status": version.get("version_status"),
            "delivery_mode": version.get("result_delivery_mode"),
            "source_rows": version.get("source_rows"),
            "retained_rows": version.get("retained_rows"),
            "excluded_rate_code_rows": version.get("excluded_rate_code_rows"),
            "duplicate_rows": version.get("duplicate_rows"),
            "validation_failed_rows": version.get("validation_failed_rows"),
            "price_unmatched_rows": version.get("price_unmatched_rows"),
            "review_case_id": version.get("review_case_id"),
            "manually_priced_rows": version.get("manually_priced_rows"),
            "manual_override_sha256": version.get("manual_override_sha256"),
        }
        _append_log(
            logs,
            log_id=f"version.{identity}.created",
            timestamp=version.get("created_at"),
            stage="database",
            level="info",
            code="FINANCE_VERSION_CREATED",
            title="Finance 日报版本已创建",
            message="业务结果已形成可追溯的日报版本。",
            details=details,
        )
        if version.get("validated_at"):
            failed = str(version.get("version_status") or "") == "rejected"
            _append_log(
                logs,
                log_id=f"version.{identity}.validated",
                timestamp=version.get("validated_at"),
                stage="validation" if failed else "database",
                level="error" if failed else "success",
                code=str(
                    version.get("failure_code")
                    or ("FINANCE_VERSION_REJECTED" if failed else "FINANCE_VERSION_VALIDATED")
                ),
                title="Finance 版本已拒绝" if failed else "Finance 版本已验证",
                message=(
                    _safe_text(version.get("failure_message"))
                    if failed
                    else "行数对账、定价与版本约束已通过。"
                )
                or "Finance 版本未通过验证。",
                details={**details, "failure_code": version.get("failure_code")},
            )
        if version.get("activated_at"):
            _append_log(
                logs,
                log_id=f"version.{identity}.activated",
                timestamp=version.get("activated_at"),
                stage="database",
                level="success",
                code="FINANCE_VERSION_ACTIVATED",
                title="Finance 当前版本已激活",
                message="当前业务日期已指向本次提交的日报事实。",
                details=details,
            )


def _outbox_logs(logs: List[Dict[str, Any]], events: Sequence[Mapping[str, Any]]) -> None:
    for index, event in enumerate(events, 1):
        identity = event.get("event_key") or index
        event_type = str(event.get("event_type") or "")
        is_failure = event_type == "arr.processing_failed"
        details = {
            "outbox_event": event_type,
            "publish_status": event.get("publish_status"),
            "publish_attempts": event.get("publish_attempts"),
            "last_error_code": event.get("last_error_code"),
        }
        _append_log(
            logs,
            log_id=f"outbox.{identity}.created",
            timestamp=event.get("created_at"),
            stage="downstream",
            level="warning" if is_failure else "info",
            code="DOWNSTREAM_FAILURE_QUEUED" if is_failure else "DOWNSTREAM_EVENT_QUEUED",
            title="失败通知已进入下游队列" if is_failure else "下游处理通知已入队",
            message=(
                "任务失败事实已写入可靠事件队列。"
                if is_failure
                else "Finance 数据库提交已完成；事件已写入可靠队列，等待下游消费者。"
            ),
            details=details,
        )
        status = str(event.get("publish_status") or "")
        if event.get("published_at"):
            _append_log(
                logs,
                log_id=f"outbox.{identity}.published",
                timestamp=event.get("published_at"),
                stage="downstream",
                level="success",
                code="DOWNSTREAM_EVENT_PUBLISHED",
                title="下游处理通知已发布",
                message="事件已交付给下游消费边界；这不等同于报表已生成。",
                details=details,
            )
        elif status == "dead":
            _append_log(
                logs,
                log_id=f"outbox.{identity}.dead",
                timestamp=event.get("available_at") or event.get("created_at"),
                stage="downstream",
                level="error",
                code=str(event.get("last_error_code") or "DOWNSTREAM_EVENT_DEAD"),
                title="下游处理通知投递失败",
                message="可靠事件已进入 dead 状态，需要检查消费者或投递器。",
                details=details,
            )
        elif int(event.get("publish_attempts") or 0) > 0 and event.get("last_error_code"):
            _append_log(
                logs,
                log_id=f"outbox.{identity}.retrying",
                timestamp=event.get("available_at") or event.get("created_at"),
                stage="downstream",
                level="warning",
                code=str(event.get("last_error_code")),
                title="下游处理通知正在重试",
                message="最近一次投递未完成，事件仍保留在可靠队列中。",
                details=details,
            )


def _review_logs(
    logs: List[Dict[str, Any]],
    events: Sequence[Mapping[str, Any]],
) -> None:
    event_copy = {
        "PRICE_REVIEW_REQUIRED": (
            "info",
            "人工价格复核已创建",
            "仅缺少固定价的键已登记；正式日报和 Finance 版本尚未创建。",
        ),
        "PRICE_REVIEW_UPDATED": (
            "info",
            "人工价格已暂存",
            "一项缺价键已由受控会话更新，待全部完成后统一确认。",
        ),
        "PRICE_REVIEW_FINALIZED": (
            "info",
            "人工价格清单已冻结",
            "ARR 将用原始 XML、固定价表和同一冻结清单重新生成并独立验收日报。",
        ),
        "PRICE_REVIEW_GENERATION_FAILED": (
            "warning",
            "正式日报生成可重试",
            "技术生成未完成；冻结的人工价格清单已保留，可再次确认重试。",
        ),
        "PRICE_REVIEW_FAILED": (
            "error",
            "人工价格复核生成失败",
            "最终重放未通过确定性业务验收，任务未形成可接受的日报版本。",
        ),
        "PRICE_REVIEW_CANCELLED": (
            "warning",
            "人工价格复核已取消",
            "复核记录保留审计痕迹；如需更正，请重新上传原始 XML。",
        ),
    }
    for index, event in enumerate(events, 1):
        code = str(event.get("event_type") or "")
        level, title, message = event_copy.get(
            code,
            ("info", "人工价格复核事件", "复核状态已更新。"),
        )
        _append_log(
            logs,
            log_id=f"review.{index}.{code or 'event'}",
            timestamp=event.get("created_at"),
            stage="validation" if code == "PRICE_REVIEW_FAILED" else "review",
            level=level,
            code=code or "PRICE_REVIEW_EVENT",
            title=title,
            message=message,
            details={
                "review_revision": event.get("revision"),
                "actor_username": event.get("actor_username"),
            },
        )


def build_job_trace(
    run: Mapping[str, Any],
    *,
    attempts: Sequence[Mapping[str, Any]] = (),
    deliveries: Sequence[Mapping[str, Any]] = (),
    submissions: Sequence[Mapping[str, Any]] = (),
    versions: Sequence[Mapping[str, Any]] = (),
    outbox_events: Sequence[Mapping[str, Any]] = (),
    review_case: Optional[Mapping[str, Any]] = None,
    review_events: Sequence[Mapping[str, Any]] = (),
    refreshed_at: Optional[datetime] = None,
) -> Dict[str, Any]:
    """Build one complete trace using only explicitly allowlisted facts."""

    logs: List[Dict[str, Any]] = []
    execution = _execution_metadata(run.get("delivery_mode"))
    _append_log(
        logs,
        log_id="run.received",
        timestamp=run.get("created_at"),
        stage="upload",
        level="success",
        code="UPLOAD_REGISTERED",
        title="上传已安全登记",
        message="XML 对象身份和任务身份已写入受控处理链路。",
        details={
            "job_id": run.get("job_id"),
            "filename": run.get("filename"),
            "source_byte_size": run.get("byte_size"),
            "source_sha256": run.get("source_sha256"),
        },
    )
    _attempt_logs(logs, attempts, review_case)
    _delivery_logs(logs, deliveries)
    _submission_logs(logs, submissions)
    _version_logs(logs, versions)
    _review_logs(logs, review_events)
    _outbox_logs(logs, outbox_events)

    run_status = str(run.get("run_status") or "")
    public_status = str(run.get("public_status") or run_status)
    failure_code = run.get("failure_code")
    if run_status in {"failed", "rejected", "cancelled"} and run.get("finished_at"):
        cancelled = run_status == "cancelled"
        _append_log(
            logs,
            log_id=f"run.{run_status}",
            timestamp=run.get("finished_at"),
            stage=_failure_stage(failure_code),
            level="warning" if cancelled else "error",
            code=str(failure_code or f"JOB_{run_status.upper()}"),
            title="任务已取消" if cancelled else "业务任务处理失败",
            message=(
                _safe_text(run.get("failure_message"))
                or (
                    "任务在收到可提交结果前被取消。"
                    if cancelled
                    else "任务未形成可接受的数据库提交。"
                )
            ),
            details={
                "job_id": run.get("job_id"),
                "run_status": run_status,
                "failure_code": failure_code,
                "delivery_mode": run.get("delivery_mode"),
            },
        )
    elif run_status == "accepted" and run.get("finished_at"):
        _append_log(
            logs,
            log_id="run.accepted",
            timestamp=run.get("finished_at"),
            stage="database",
            level="success",
            code="JOB_SUCCEEDED",
            title="业务任务已完成",
            message="只有通过验收并提交 Finance 数据库后，任务才会进入此状态。",
            details={
                "job_id": run.get("job_id"),
                "business_date": run.get("business_date"),
                "delivery_mode": run.get("delivery_mode"),
            },
        )
    elif run_status == "awaiting_review":
        review_status = str((review_case or {}).get("case_status") or "open")
        if review_status == "processing":
            title = "当前：正在生成正式日报"
            message = "ARR 正在用冻结人工价格清单重放原始 XML 并进行独立验收。"
        elif review_status == "generation_failed":
            title = "当前：正式日报生成可重试"
            message = "冻结价格清单已保留；可再次确认以重试技术生成。"
        else:
            title, message = _CURRENT_COPY["awaiting_review"]
        _append_log(
            logs,
            log_id="run.current.awaiting_review",
            timestamp=run.get("updated_at") or run.get("created_at"),
            stage="review",
            level="info",
            code="CURRENT_AWAITING_REVIEW",
            title=title,
            message=message,
            details={
                "job_id": run.get("job_id"),
                "review_case_id": (review_case or {}).get("case_id"),
                "review_status": review_status,
                "completed_items": (review_case or {}).get("completed_items"),
                "total_items": (review_case or {}).get("total_items"),
            },
        )
    elif run_status in ACTIVE_RUN_STATUSES:
        title, message = _CURRENT_COPY[run_status]
        _append_log(
            logs,
            log_id=f"run.current.{run_status}",
            timestamp=run.get("updated_at") or run.get("created_at"),
            stage=_CURRENT_STAGE[run_status],
            level="info",
            code=f"CURRENT_{run_status.upper()}",
            title=title,
            message=message,
            details={
                "job_id": run.get("job_id"),
                "run_status": run_status,
                "delivery_mode": run.get("delivery_mode"),
            },
        )

    logs.sort(
        key=lambda item: (
            _timestamp_key(str(item["timestamp"])),
            int(item["_order"]),
            int(item["_sequence"]),
        )
    )
    for item in logs:
        item.pop("_order", None)
        item.pop("_sequence", None)

    latest_attempt = _latest(attempts, "attempt_no")
    latest_version = _latest(versions, "version_no")
    current_stage = (
        "review"
        if run_status == "awaiting_review"
        else _failure_stage(failure_code)
        if failure_code
        else _CURRENT_STAGE.get(run_status, logs[-1]["stage"] if logs else "upload")
    )
    if run_status == "accepted" and outbox_events:
        current_stage = "downstream"
    last_log_at = logs[-1]["timestamp"] if logs else _iso(run.get("updated_at"))
    resolved_refreshed_at = refreshed_at or datetime.now(timezone.utc)

    return {
        "trace_version": "arr-job-trace-4",
        "job": {
            "job_id": str(run.get("job_id") or ""),
            "status": public_status,
            "raw_status": run_status,
            "active": run_status in ACTIVE_RUN_STATUSES,
            "current_stage": current_stage,
            "filename": _safe_text(run.get("filename"), 255),
            "byte_size": int(run.get("byte_size") or 0),
            "source_sha256": _safe_text(run.get("source_sha256"), 64),
            "business_date": _iso(run.get("business_date")),
            "delivery_mode": _safe_text(run.get("delivery_mode"), 32),
            "execution_scope": execution["execution_scope"],
            "processor_mode": execution["processor_mode"],
            "remote_dispatch": execution["remote_dispatch"],
            "attempt_no": (
                int(latest_attempt.get("attempt_no") or 0)
                if latest_attempt is not None
                else None
            ),
            "remote_run_id": (
                _safe_text(latest_attempt.get("remote_run_id"), 128)
                if latest_attempt is not None
                else None
            ),
            "version_no": (
                int(latest_version.get("version_no") or 0)
                if latest_version is not None
                and latest_version.get("version_no") is not None
                else None
            ),
            "output_rows": (
                int(latest_version.get("retained_rows") or 0)
                if latest_version is not None
                else 0
            ),
            "created_at": _iso(run.get("created_at")),
            "updated_at": _iso(run.get("updated_at")),
            "finished_at": _iso(run.get("finished_at")),
            "last_log_at": last_log_at,
            "failure": (
                {
                    "code": _safe_text(failure_code, 128),
                    "message": _safe_text(run.get("failure_message")),
                }
                if failure_code
                else None
            ),
            "requested_processor_version": _safe_text(
                run.get("requested_processor_version"), 64
            ),
            "delivered_processor_version": _safe_text(
                run.get("delivered_processor_version"), 64
            ),
            "review": (
                {
                    "case_id": _safe_text(review_case.get("case_id"), 64),
                    "status": _safe_text(review_case.get("case_status"), 64),
                    "revision": int(review_case.get("revision") or 0),
                    "completed_items": int(review_case.get("completed_items") or 0),
                    "total_items": int(review_case.get("total_items") or 0),
                    "manual_override_sha256": _safe_text(
                        review_case.get("manual_override_sha256"), 64
                    ),
                    "frozen_at": _iso(review_case.get("frozen_at")),
                    "completed_at": _iso(review_case.get("completed_at")),
                    "cancelled_at": _iso(review_case.get("cancelled_at")),
                }
                if review_case is not None
                else None
            ),
        },
        "evidence": {
            "attempts": len(attempts),
            "artifact_deliveries": len(deliveries),
            "legacy_direct_submissions": len(submissions),
            "finance_versions": len(versions),
            "outbox_events": len(outbox_events),
            "review_events": len(review_events),
        },
        "logs": logs,
        "refreshed_at": resolved_refreshed_at.isoformat(),
    }
