#!/usr/bin/env python3
"""Audit a kanban wave for handoff defects, BEFORE the downstream card consumes it.

Why this exists (16 Oct 2026, aphasia chain): four workers completed and re-posted
their structured metadata as a binary comment because the lifecycle call dropped it.
Two of the four stored no structured metadata at all. Anything reading completion
metadata alone would have silently dropped every lane, and the synthesis would have
produced a page with nothing behind it. The product's own stop-gate only checks that
a terminal kanban tool was CALLED, not that its payload was complete, so the
handoff has to be verified here.

Usage:
    python3 wave-audit.py <card_id> [<card_id> ...]
    python3 wave-audit.py --chain-key aphasia-uk-provision     # every card with that key prefix

Checks per card (skips cards still running or in review):
    METADATA-EMPTY    run metadata holds no structured fields beyond worker_session_id
    NO-ARTEFACT       no attachment rows and no declared artefact on disk
    ARTEFACT-MISSING  a path declared in a comment's JSON block does not exist
    NO-COMMENT        no comment at all, so the comment fallback carries nothing

Exit 1 if any card is flagged, so it can gate a wave. Print the table either way.
"""

from __future__ import annotations

import json
import os
import re
import sqlite3
import sys

DB = os.environ.get("HERMES_KANBAN_DB", os.path.expanduser("~/.hermes/kanban.db"))
SKIP_STATUS = {"running", "review", "ready", "todo", "scheduled", "triage", "blocked"}
TRIVIAL_KEYS = {"worker_session_id"}


def rows(conn, sql, args=()):
    cur = conn.execute(sql, args)
    cols = [c[0] for c in cur.description]
    return [dict(zip(cols, r)) for r in cur.fetchall()]


def metadata_substance(raw: str | None) -> tuple[int, list[str]]:
    try:
        d = json.loads(raw) if raw else {}
    except Exception:
        return 0, []
    if not isinstance(d, dict):
        return 0, []
    keys = [k for k in d if k not in TRIVIAL_KEYS]
    return len(keys), sorted(keys)


def declared_paths(text: str) -> list[str]:
    """Absolute paths that look like artefacts, from fenced JSON or bare prose."""
    out: list[str] = []
    for m in re.finditer(r'"(/[^"]+\.(?:md|json|csv|html|txt|xlsx|docx|png|jpg|pdf))"', text):
        out.append(m.group(1))
    for m in re.finditer(r'(?<![\w"])/(?:home|tmp)/[\w./-]+\.(?:md|json|csv|html|txt|xlsx|docx|png|jpg|pdf)', text):
        out.append(m.group(0))
    seen, uniq = set(), []
    for p in out:
        if p not in seen:
            seen.add(p)
            uniq.append(p)
    return uniq


def main(argv: list[str]) -> int:
    if not argv:
        print(__doc__)
        return 2

    conn = sqlite3.connect(DB)
    if argv[0] == "--chain-key":
        key = f"{argv[1]}-%"
        ids = [r["id"] for r in rows(conn, "select id from tasks where idempotency_key like ? order by created_at", (key,))]
    else:
        ids = argv
    if not ids:
        print("no cards matched")
        return 2

    flagged = 0
    print(f"auditing {len(ids)} card(s) against {DB}\n")
    for cid in ids:
        t = rows(conn, "select id, status, assignee, title from tasks where id=?", (cid,))
        if not t:
            print(f"{cid}: NOT FOUND")
            flagged += 1
            continue
        t = t[0]
        runs = rows(conn, "select metadata, outcome from task_runs where task_id=? order by id desc limit 1", (cid,))
        nkeys, keys = metadata_substance(runs[0]["metadata"] if runs else None)
        att = rows(conn, "select filename, stored_path from task_attachments where task_id=?", (cid,))
        cm = rows(conn, "select body from task_comments where task_id=? order by rowid", (cid,))

        paths: list[str] = []
        for c in cm:
            paths += declared_paths(c["body"] or "")
        missing = [p for p in paths if not os.path.exists(p)]

        flags = []
        if t["status"] not in SKIP_STATUS:
            if nkeys == 0:
                flags.append("METADATA-EMPTY")
            if not att and not paths:
                flags.append("NO-ARTEFACT")
            if missing:
                flags.append("ARTEFACT-MISSING")
            if not cm:
                flags.append("NO-COMMENT")
        flagged += 1 if flags else 0

        print(f"{cid}  {t['status']:<9} {t['assignee']:<17} meta_keys={nkeys:<3} attachments={len(att):<3} comments={len(cm):<3} "
              f"declared={len(paths):<3} missing={len(missing):<3} {' '.join(flags)}")
        if nkeys and keys:
            print(f"        metadata fields: {', '.join(keys[:12])}")
        if missing:
            for p in missing[:4]:
                print(f"        MISSING: {p}")

    print()
    if flagged:
        print(f"RESULT: {flagged} card(s) flagged. Fix the handoff before the downstream card runs:")
        print("  - METADATA-EMPTY: read the card's comment thread; the worker usually re-posted the JSON there.")
        print("    Have the next worker read comments as well as metadata, and tell the lane to pass it properly.")
        print("  - NO-ARTEFACT / ARTEFACT-MISSING: the deliverable is not recoverable from the board.")
        print("    Re-dispatch or copy the file out of the scratch workspace before it is pruned.")
        return 1
    print("RESULT: clean. Every card carries structured metadata, a recoverable artefact and a comment trail.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main(sys.argv[1:]))