"""Pending-review queue router.

Wraps the on-disk pending file at ``~/.amx/pending_metadata.json``
that :mod:`amx.pending_review` reads/writes. AMX Studio's
``/pending`` page lists, edits, deletes, clears, and applies the
queue without the user touching the filesystem.

Apply spawns the same worker as :mod:`amx.web.routers.runs` so SSE
progress events look identical regardless of which page the user
triggered the apply from.
"""

from __future__ import annotations

import threading
from typing import Any

from fastapi import APIRouter, Depends, HTTPException, status
from pydantic import BaseModel, ConfigDict, Field

from amx.agents.base import Confidence
from amx.agents.orchestrator import ReviewResult
from amx.config import AMXConfig
from amx.pending_review import clear_pending, load_pending, save_pending
from amx.storage.sqlite_store import history_store
from amx.web.deps import get_cfg, get_jobs
from amx.web.jobs import JobRegistry
from amx.web.routers.runs import ApplyRequest, _apply_worker

router = APIRouter(prefix="/api/pending", tags=["pending"])


class PendingPatch(BaseModel):
    """Body for ``PATCH /api/pending/{idx}`` — every field optional so
    the SPA can update one column at a time."""

    model_config = ConfigDict(populate_by_name=True)

    schema_: str | None = Field(default=None, alias="schema")
    table: str | None = None
    column: str | None = None
    final_description: str | None = None
    confidence: str | None = None
    asset_kind: str | None = None


def _serialize(
    rr: ReviewResult,
    idx: int,
    *,
    enrichment: dict[int, dict[str, Any]] | None = None,
) -> dict[str, Any]:
    """Serialize one ReviewResult for the SPA.

    ``enrichment`` is an optional ``{result_id: run_results_row}`` map
    that backfills fields ``load_pending()`` can't reconstruct from the
    flat JSON file (alternatives_json, logprob_score, source). The SPA
    needs the alternatives so the user can swap which one is the
    chosen description without leaving the page.
    """
    extra = enrichment.get(rr.result_id) if enrichment and rr.result_id is not None else None
    alternatives = list(rr.alternatives or [])
    logprob = rr.logprob_score
    if extra is not None:
        # The pending JSON file caches only the user's last chosen text;
        # the full alternatives list lives on ``run_results.alternatives_json``
        # and must replace any cached value on every GET so the SPA can
        # still show A / B / C buttons after the user clicks B. (The
        # previous guard ``and not alternatives`` kept the stale single-
        # entry pending list and made the other alternatives vanish from
        # the carousel as soon as one was picked — regression caught
        # 2026-05-14 during the single-signal pivot.)
        ext_alts = extra.get("alternatives_json")
        if isinstance(ext_alts, list) and ext_alts:
            alternatives = ext_alts
        if logprob is None and extra.get("logprob_score") is not None:
            logprob = extra.get("logprob_score")
    return {
        "idx": idx,
        "schema": rr.schema,
        "table": rr.table,
        "column": rr.column,
        "final_description": rr.final_description,
        "confidence": (
            rr.confidence.name.lower() if hasattr(rr.confidence, "name") else str(rr.confidence)
        ),
        "source": rr.source,
        "applied": bool(rr.applied),
        "asset_kind": rr.asset_kind,
        "result_id": rr.result_id,
        "alternatives": alternatives,
        "logprob_score": logprob,
    }


def _build_enrichment_map(rows: list[ReviewResult]) -> dict[int, dict[str, Any]]:
    """Fetch ``run_results`` rows for every pending entry's ``result_id``.

    The pending JSON file only stores ``final_description`` (the chosen
    one). Alternatives + logprob live in the run_results SQLite table,
    keyed by id. We do ONE iteration over the history to build a
    {result_id: row} index so /api/pending stays a single GET.
    """
    needed = {int(r.result_id) for r in rows if r.result_id is not None}
    if not needed:
        return {}
    hs = history_store()
    if hs is None:
        return {}
    out: dict[int, dict[str, Any]] = {}
    # Walk recent runs until we've covered every needed result_id.
    # Cheap bound: pending entries always come from a recent run, so
    # 50 runs is enough headroom; bump if real workloads outgrow it.
    # ``command_filter=None`` so re-run rows (command="rerun") are
    # included — without this, a row generated by /api/runs/rerun-item
    # never had its alternatives_json enriched and the SPA dropped
    # every alternative the moment the user picked one.
    try:
        recent = hs.list_recent_runs(limit=50, command_filter=None)
    except Exception:
        return {}
    for run in recent:
        run_id = run.get("id") if isinstance(run, dict) else getattr(run, "id", None)
        if run_id is None:
            continue
        try:
            run_rows = hs.get_run_results(int(run_id))
        except Exception:
            continue
        for row in run_rows:
            rid = row.get("id")
            if isinstance(rid, int) and rid in needed:
                out[rid] = row
        if needed.issubset(out.keys()):
            break
    return out


@router.get("")
def list_pending() -> dict[str, Any]:
    """Return the on-disk pending queue with stable ``idx`` markers
    so PATCH/DELETE callers can target rows by position."""
    rows = list(load_pending())
    enrichment = _build_enrichment_map(rows)
    return {
        "pending": [_serialize(r, i, enrichment=enrichment) for i, r in enumerate(rows)],
        "count": len(rows),
    }


@router.patch("/{idx}")
def patch_pending(idx: int, body: PendingPatch) -> dict[str, Any]:
    """Update one row in place. The SPA's inline-edit modal posts
    only the changed fields."""
    rows = list(load_pending())
    if not 0 <= idx < len(rows):
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"No pending entry at index {idx}.",
        )
    target = rows[idx]
    if body.schema_ is not None:
        target.schema = body.schema_
    if body.table is not None:
        target.table = body.table
    if body.column is not None:
        target.column = body.column or None
    if body.final_description is not None:
        target.final_description = body.final_description
    if body.asset_kind is not None:
        target.asset_kind = body.asset_kind
    if body.confidence is not None:
        try:
            target.confidence = Confidence[body.confidence.upper()]
        except KeyError as exc:
            raise HTTPException(
                status_code=status.HTTP_400_BAD_REQUEST,
                detail=f"Invalid confidence '{body.confidence}'. Use high/medium/low.",
            ) from exc
    save_pending(rows)
    return _serialize(target, idx)


@router.delete("/{idx}")
def remove_pending(idx: int) -> dict[str, Any]:
    rows = list(load_pending())
    if not 0 <= idx < len(rows):
        raise HTTPException(
            status_code=status.HTTP_404_NOT_FOUND,
            detail=f"No pending entry at index {idx}.",
        )
    removed = rows.pop(idx)
    save_pending(rows)
    return {"ok": True, "removed": _serialize(removed, idx), "remaining": len(rows)}


@router.post("/clear")
def clear() -> dict[str, Any]:
    clear_pending()
    return {"ok": True}


class BulkRequest(BaseModel):
    """Body for ``POST /api/pending/bulk`` — PR B bulk-action endpoint.

    The SPA's review-mode toolbar fires this when the user clicks
    ``[Accept selected]`` / ``[Skip selected]`` / ``[Apply selected]`` (the
    apply branch is handled by ``/api/pending/apply`` after we mark targets
    in-queue). ``ids`` is the list of zero-indexed pending idx markers (NOT
    result_id) to act on — matches the SPA's already-rendered ``idx`` field.

    ``action`` is one of ``accept`` / ``skip``. ``accept`` is a no-op on rows
    already in the queue (it's the queue's whole point). ``skip`` deletes
    the rows. ``apply`` is a deliberate omission — callers should hit
    ``/api/pending/apply`` directly so the existing scope/override semantics
    are honoured.
    """

    model_config = ConfigDict(populate_by_name=True)

    ids: list[int]
    action: str


@router.post("/bulk")
def patch_pending_bulk(body: BulkRequest) -> dict[str, Any]:
    """Iterate the requested ``idx`` list and apply ``action`` to each.

    Returns ``{ok, action, processed: [{idx, status}], remaining}``. Per-row
    status is ``"ok"`` on a successful op, ``"not_found"`` when the idx is
    out of range, ``"already_applied"`` for accept on an applied row.
    """
    action = (body.action or "").strip().lower()
    if action not in {"accept", "skip"}:
        raise HTTPException(
            status_code=status.HTTP_400_BAD_REQUEST,
            detail=f"Invalid action '{body.action}'. Use 'accept' or 'skip'.",
        )

    rows = list(load_pending())
    processed: list[dict[str, Any]] = []
    if action == "accept":
        # ``accept`` is idempotent: rows are already in the pending queue.
        # The endpoint stamps ``applied=True`` (so /api/pending/apply will
        # write them) without removing anything.
        for idx in body.ids:
            if not 0 <= idx < len(rows):
                processed.append({"idx": idx, "status": "not_found"})
                continue
            rows[idx].applied = True
            processed.append({"idx": idx, "status": "ok"})
        save_pending(rows)
        return {
            "ok": True,
            "action": action,
            "processed": processed,
            "remaining": len(rows),
        }

    # action == "skip" — drop the targeted rows. Sort descending so the
    # idx values remain stable as we pop.
    seen: set[int] = set()
    targets = []
    for idx in body.ids:
        if idx in seen:
            continue
        seen.add(idx)
        if not 0 <= idx < len(rows):
            processed.append({"idx": idx, "status": "not_found"})
            continue
        targets.append(idx)
    for idx in sorted(targets, reverse=True):
        rows.pop(idx)
        processed.append({"idx": idx, "status": "ok"})
    save_pending(rows)
    return {
        "ok": True,
        "action": action,
        "processed": processed,
        "remaining": len(rows),
    }


class RestoreRequest(BaseModel):
    """Body for ``POST /api/pending/restore`` — re-add a previously
    skipped row to the pending queue. The SPA already has the full
    ReviewResult shape locally (the run-detail page rendered it),
    so we accept it verbatim instead of re-querying the history
    store."""

    model_config = ConfigDict(populate_by_name=True)

    result_id: int | None = None
    schema_: str = Field(..., alias="schema")
    table: str
    column: str | None = None
    final_description: str
    confidence: str = "medium"
    source: str = "user_restore"
    asset_kind: str = "table"
    alternatives: list[str] = Field(default_factory=list)
    logprob_score: float | None = None


@router.post("/restore")
def restore(body: RestoreRequest) -> dict[str, Any]:
    """Re-add a previously-skipped run_result back to the pending
    queue. Idempotent — if a pending row with the same ``result_id``
    is already present, the existing idx is returned and no row is
    appended."""
    try:
        confidence = Confidence[body.confidence.upper()]
    except (KeyError, AttributeError):
        confidence = Confidence.MEDIUM
    rows = load_pending()
    if body.result_id is not None:
        for i, existing in enumerate(rows):
            if existing.result_id == body.result_id:
                return {"ok": True, "idx": i, "count": len(rows), "already_present": True}
    rr = ReviewResult(
        schema=body.schema_,
        table=body.table,
        column=body.column,
        final_description=body.final_description,
        confidence=confidence,
        source=body.source,
        asset_kind=body.asset_kind,
        # ReviewResult.applied defaults to False, but save_pending() drops
        # any row with applied=False on the way out — without this the
        # restore looked successful (200 OK + "Restored" toast) yet the
        # pending file silently lost the new row, so the SPA's "Apply
        # pending queue" button stayed at "Nothing to apply".
        applied=True,
        result_id=body.result_id,
        alternatives=list(body.alternatives or []),
        logprob_score=body.logprob_score,
    )
    rows.append(rr)
    save_pending(rows)
    return {"ok": True, "idx": len(rows) - 1, "count": len(rows), "already_present": False}


class PendingApplyBody(BaseModel):
    """Optional scope override for ``POST /api/pending/apply``.

    The pending queue itself doesn't carry a per-row scope, so when
    the user is reviewing a run rooted in a non-active DB profile or
    on a non-default database/catalog, the apply path needs an
    explicit hint to route the COMMENT statements at the right
    target. Without it, the worker fell back to ``cfg.active_db_profile``
    + the profile's pinned database, which produced
    ``schema "X" does not exist`` errors when the active profile
    pointed at a different database than the run was scoped to.

    All fields are optional — omit to keep the legacy active-profile
    fallback (CLI-driven sessions and single-profile setups).
    """

    model_config = ConfigDict(populate_by_name=True)

    db_profile: str | None = None
    database: str | None = None
    catalog: str | None = None


@router.post("/apply")
def apply_pending(
    body: PendingApplyBody | None = None,
    cfg: AMXConfig = Depends(get_cfg),
    jobs: JobRegistry = Depends(get_jobs),
) -> dict[str, Any]:
    """Spawn an apply job that writes every entry in the pending
    queue. Re-uses :func:`amx.web.routers.runs._apply_worker` so the
    SSE event stream is bit-identical to ``POST /api/apply``.

    Pass ``db_profile`` / ``database`` / ``catalog`` in the body to
    pin the apply to the same scope the run was rooted in — Studio's
    run-detail page sends these from ``run.data`` so the COMMENTs land
    in the database the user actually scoped, not the active profile's
    default."""
    body = body or PendingApplyBody()
    job = jobs.new_job("apply")
    thread = threading.Thread(
        target=_apply_worker,
        args=(
            cfg,
            job,
            ApplyRequest(
                results=None,
                db_profile=body.db_profile,
                database=body.database,
                catalog=body.catalog,
            ),
        ),
        name=f"amx-studio-pending-apply-{job.id}",
        daemon=True,
    )
    thread.start()
    return {"job_id": job.id, "status": job.status}


@router.post("/preview")
def preview_pending(cfg: AMXConfig = Depends(get_cfg)) -> dict[str, Any]:
    """Dry-run preview of the pending queue.

    Loads the same pending entries ``POST /api/pending/apply`` would
    write, asks the active connector for the SQL template each
    asset would execute, and returns the list synchronously. No
    database connection is opened mutably and the pending file is
    untouched, so the caller (Studio's Pending page) can render a
    "Show SQL preview" modal without committing anything.

    Each item carries either ``sql_template`` (the rendered template
    with the ``:cmt`` placeholder) or ``skipped_reason`` when the
    backend cannot accept a comment for the asset kind. Errors from
    a misbehaving adapter are surfaced as ``error`` strings on the
    individual item — partial preview is still useful.
    """
    from amx.agents.orchestrator import apply_review_results_to_db
    from amx.db.connector import DatabaseConnector

    rows = load_pending()
    if not rows:
        return {"events": [], "count": 0}

    db = DatabaseConnector(cfg.db)

    events: list[dict[str, Any]] = []

    def _on_progress(
        result: ReviewResult,
        status_label: str,
        idx: int,
        total: int,
        detail: str,
    ) -> None:
        item: dict[str, Any] = {
            "idx": idx,
            "schema": result.schema,
            "table": result.table or "",
            "column": result.column,
            "asset_kind": result.asset_kind or "table",
            "new_comment": result.final_description or "",
        }
        if status_label == "preview":
            if "unsupported" in detail.lower():
                item["skipped_reason"] = detail
            else:
                item["sql_template"] = detail
        elif status_label == "preview_failed":
            item["error"] = detail
        events.append(item)

    apply_review_results_to_db(
        db,
        rows,
        on_progress=_on_progress,
        dry_run=True,
    )
    return {"events": events, "count": len(events)}
