scripts/diag_ai_transparency_db.py

from __future__ import annotations

import json
import sqlite3
from pathlib import Path


def main() -> None:
    db_path = Path("dev.db")
    if not db_path.is_file():
        raise SystemExit("dev.db not found in cwd")

    con = sqlite3.connect(str(db_path))
    cur = con.cursor()
    cur.execute("select report_id, section_code, provenance from report_sections")
    rows = cur.fetchall()

    counts: dict[int, int] = {}
    examples: dict[int, tuple[str, str]] = {}

    for report_id, section_code, prov in rows:
        try:
            raw = json.loads(prov or "null")
        except Exception:
            continue
        meta = raw.get("meta", {}) if isinstance(raw, dict) else {}
        trx = meta.get("ai_transparency", {}) if isinstance(meta.get("ai_transparency"), dict) else {}
        v = trx.get("ai_involvement_percent", None)
        if not isinstance(v, int):
            continue
        counts[v] = counts.get(v, 0) + 1
        examples.setdefault(v, (str(report_id), str(section_code)))

    print("ai_involvement_percent histogram (from persisted meta.ai_transparency):")
    for k in sorted(counts):
        ex = examples.get(k)
        print(f"  {k:>3}%: {counts[k]:>4} rows  (e.g. report={ex[0]} section={ex[1]})")


if __name__ == "__main__":
    main()