"""Cloud-sync observability — per-capability latest-status diagnosis.

The sync engine already records rich per-capability detail into
``CmdbSyncJob.capability_results`` (status / reason / counts / per-leg
partial_failures / skipped_legs / timeout / forced-sweep meta), and the
job-detail endpoint already returns it. What was missing is the
*diagnostic roll-up*: "for THIS cloud account, what is the latest
outcome of EACH capability, and which ones are currently failing?" —
the question an admin actually asks when a sync looks wrong.

This module answers that by walking the integration's recent sync jobs
newest-first and, for each capability the integration is configured to
sync, surfacing the most recent job that touched it. It also powers the
``cloud_sync`` lane of the automation-health dashboard.

Read-only: no provider calls, no mutations — pure aggregation over
``cmdb_sync_jobs`` rows.
"""
from __future__ import annotations

from typing import Any

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

from app.domains.cmdb.models import CmdbSyncJob
from app.domains.cmdb.scheduled_jobs_provisioning import resolve_sync_capabilities

# Recent jobs to scan when resolving the latest per-capability outcome.
# Per-capability cron rows fire hourly, so 60 covers ~the last few hours
# across all capabilities even on a busy multi-capability account.
_SCAN_LIMIT = 60

# Sync-job statuses that indicate a problem worth surfacing.
_UNHEALTHY_JOB_STATUSES = ("partial_failure", "failed")
_UNHEALTHY_CAP_STATUSES = ("failed", "partial_failure")


def _cap_outcome(job: CmdbSyncJob, cap: str) -> dict[str, Any] | None:
    """Extract one capability's result from a job, or None if absent."""
    results = job.capability_results or {}
    entry = results.get(cap)
    if not isinstance(entry, dict):
        return None
    partial = entry.get("partial_failures") or []
    skipped = entry.get("skipped_legs") or []
    return {
        "status": entry.get("status"),
        "reason": entry.get("reason"),
        "error": entry.get("error"),
        "upserted": entry.get("upserted"),
        "deleted": entry.get("deleted"),
        "partial_failure_count": len(partial) if isinstance(partial, list) else 0,
        "skipped_leg_count": (
            entry.get("skipped_leg_count")
            if entry.get("skipped_leg_count") is not None
            else (len(skipped) if isinstance(skipped, list) else 0)
        ),
        "timed_out": bool(entry.get("timed_out")),
        "forced_sweep": bool(entry.get("forced_sweep")),
        "job_id": str(job.id),
        "job_status": job.status,
        "triggered_by": job.triggered_by,
        "finished_at": job.finished_at.isoformat() if job.finished_at else None,
        "queued_at": job.queued_at.isoformat() if job.queued_at else None,
    }


async def build_capability_status(
    db: AsyncSession,
    integration: Any,
) -> dict[str, Any]:
    """Latest sync outcome per capability for one integration.

    ``integration`` is an ``Integration`` ORM row. Returns a row per
    configured capability — including ones that have NEVER synced
    (``status='never'``) so a silent gap is visible, not just absent.
    """
    capabilities = resolve_sync_capabilities(integration)
    jobs = (
        await db.execute(
            select(CmdbSyncJob)
            .where(CmdbSyncJob.integration_id == str(integration.id))
            .order_by(CmdbSyncJob.queued_at.desc())
            .limit(_SCAN_LIMIT)
        )
    ).scalars().all()

    rows: list[dict[str, Any]] = []
    failing = 0
    stale = 0
    for cap in capabilities:
        outcome: dict[str, Any] | None = None
        for job in jobs:
            o = _cap_outcome(job, cap)
            if o is not None:
                outcome = o
                break
        if outcome is None:
            rows.append({"capability": cap, "status": "never", "finished_at": None})
            stale += 1
            continue
        rows.append({"capability": cap, **outcome})
        if outcome.get("status") in _UNHEALTHY_CAP_STATUSES:
            failing += 1

    # Most-recent job overall (for "last sync" + in-flight indication).
    latest = jobs[0] if jobs else None
    return {
        "integration_id": str(integration.id),
        "provider": getattr(integration, "provider", None),
        "capabilities": rows,
        "summary": {
            "configured": len(capabilities),
            "failing": failing,
            "never_synced": stale,
            "last_job_id": str(latest.id) if latest else None,
            "last_job_status": latest.status if latest else None,
            "last_finished_at": (
                latest.finished_at.isoformat()
                if latest and latest.finished_at else None
            ),
        },
    }


async def build_cloud_sync_health(db: AsyncSession, *, since) -> dict[str, Any]:
    """Automation-health ``cloud_sync`` lane: recent failing/partial sync
    jobs in-window, with the worst offenders surfaced for drill-in.

    ``since`` is a tz-aware datetime lower bound (matches the other lanes'
    rolling window). Counts jobs whose status is failed/partial_failure
    and rolls up the per-(integration, capability) failures so the admin
    sees WHICH account+capability is broken, not just a total.
    """
    jobs = (
        await db.execute(
            select(CmdbSyncJob)
            .where(CmdbSyncJob.queued_at >= since)
            .where(CmdbSyncJob.status.in_(_UNHEALTHY_JOB_STATUSES))
            .order_by(CmdbSyncJob.queued_at.desc())
            .limit(500)
        )
    ).scalars().all()

    by_pair: dict[tuple[str, str], dict[str, Any]] = {}
    for job in jobs:
        results = job.capability_results or {}
        for cap, entry in results.items():
            if not isinstance(entry, dict):
                continue
            if entry.get("status") not in _UNHEALTHY_CAP_STATUSES:
                continue
            key = (str(job.integration_id), cap)
            slot = by_pair.setdefault(key, {
                "integration_id": str(job.integration_id),
                "capability": cap,
                "count": 0,
                "last_reason": None,
                "last_finished_at": None,
            })
            slot["count"] += 1
            if slot["last_reason"] is None:
                slot["last_reason"] = (
                    entry.get("error") or entry.get("reason")
                    or (f"{len(entry.get('partial_failures') or [])} leg failures")
                )
                slot["last_finished_at"] = (
                    job.finished_at.isoformat() if job.finished_at else None
                )

    top = sorted(by_pair.values(), key=lambda x: x["count"], reverse=True)[:10]
    return {
        "failed_jobs": len(jobs),
        "failing_capabilities": len(by_pair),
        "top_failing": top,
    }