`scripts/report.py`

229 lines
#!/usr/bin/env python3
"""Write REPORT.md (+ data/exports/summary.json). `--gaps` also prints the research-gap analysis."""
import json, sys, pathlib
from collections import Counter

ROOT = pathlib.Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT / "src"))
from conrad import config, db, exports  # noqa: E402

conn = db.connect()
db.init_db(conn)
q = lambda sql, *a: conn.execute(sql, a).fetchone()[0]  # noqa: E731

S = {
    "total_canonical_item_level": q("SELECT COUNT(*) FROM cartoons WHERE granularity='item' AND merged_into IS NULL"),
    "total_box_range_slots": q("SELECT COUNT(*) FROM cartoons WHERE granularity='box_range'"),
    "total_folder_records": q("SELECT COUNT(*) FROM cartoons WHERE granularity='folder' AND merged_into IS NULL"),
    "total_source_records": q("SELECT COUNT(*) FROM cartoon_sources"),
    "total_source_records_live": q("SELECT COUNT(*) FROM cartoon_sources WHERE source_id NOT LIKE 'seed%'"),
    "total_repositories_with_records": q("SELECT COUNT(DISTINCT repository) FROM cartoon_sources WHERE repository NOT IN ('Secondary citation')"),
    "total_sources_registered": q("SELECT COUNT(*) FROM sources"),
    "earliest_item": conn.execute("SELECT COALESCE(date_exact, date_start || '..' || date_end), title, canonical_id FROM cartoons "
                                  "WHERE granularity='item' AND merged_into IS NULL AND date_start IS NOT NULL ORDER BY date_start LIMIT 1").fetchone(),
    "earliest_item_exact": conn.execute("SELECT date_exact, title, canonical_id FROM cartoons WHERE granularity='item' "
                                        "AND merged_into IS NULL AND date_exact IS NOT NULL ORDER BY date_exact LIMIT 1").fetchone(),
    "latest_item": conn.execute("SELECT COALESCE(date_exact, date_start || '..' || date_end), title, canonical_id FROM cartoons "
                                "WHERE granularity='item' AND merged_into IS NULL AND date_end IS NOT NULL ORDER BY date_end DESC LIMIT 1").fetchone(),
    "latest_item_exact": conn.execute("SELECT date_exact, title, canonical_id FROM cartoons WHERE granularity='item' "
                                      "AND merged_into IS NULL AND date_exact IS NOT NULL ORDER BY date_exact DESC LIMIT 1").fetchone(),
    "earliest_any": conn.execute("SELECT MIN(date_start) FROM cartoons WHERE merged_into IS NULL").fetchone(),
    "latest_any": conn.execute("SELECT MAX(date_end) FROM cartoons WHERE merged_into IS NULL AND date_end <= '2010-12-31'").fetchone(),
    "auto_linked_duplicates": q("SELECT COUNT(*) FROM cartoon_links WHERE relation='duplicate_of'"),
    "possible_duplicates_for_review": q("SELECT COUNT(*) FROM cartoon_links WHERE relation='possible_duplicate'"),
    "contained_in_candidates": q("SELECT COUNT(*) FROM cartoon_links WHERE relation='contained_in_candidate'"),
    "canonical_with_public_image_link": q("""SELECT COUNT(DISTINCT COALESCE(c.merged_into,c.id)) FROM cartoon_sources cs
        JOIN cartoons c ON c.id=cs.cartoon_id WHERE cs.access_level='online_image'"""),
    "canonical_requiring_archive_access": q("""SELECT COUNT(DISTINCT COALESCE(c.merged_into,c.id)) FROM cartoons c
        WHERE COALESCE(c.merged_into,c.id) NOT IN (SELECT COALESCE(c2.merged_into,c2.id) FROM cartoon_sources cs JOIN cartoons c2
        ON c2.id=cs.cartoon_id WHERE cs.access_level IN ('online_image'))
        AND c.id IN (SELECT cartoon_id FROM cartoon_sources WHERE access_level='archive_visit')"""),
    "archive_access_items": q("""SELECT COUNT(*) FROM cartoons c WHERE c.granularity='item' AND c.merged_into IS NULL
        AND c.id NOT IN (SELECT COALESCE(k.merged_into,k.id) FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id
                         WHERE x.access_level='online_image')
        AND c.id IN (SELECT COALESCE(k.merged_into,k.id) FROM cartoon_sources x JOIN cartoons k ON k.id=x.cartoon_id
                     WHERE x.access_level='archive_visit')"""),
    "local_images_stored": q("SELECT COUNT(*) FROM cartoon_sources WHERE local_image IS NOT NULL"),
}
S["source_rows_by_acquisition_method"] = dict(conn.execute(
    "SELECT COALESCE(acquisition_method,'NULL'), COUNT(*) FROM cartoon_sources GROUP BY 1 ORDER BY 2 DESC").fetchall())
_canon = [r for r in exports.canonical_rows(conn) if r["granularity"] == "item"]
S["item_level_pending_direct_verification"] = sum("pending_direct_verification" in r["provenance_flags"] for r in _canon)
S["item_level_secondary_citation_only"] = sum("secondary_citation_only" in r["provenance_flags"] for r in _canon)
table = exports.per_year(conn)
S["years_with_no_item_records"] = [y for y, t in table.items() if not t["item"]]
S["years_with_no_records_at_all"] = [y for y, t in table.items() if not (t["item"] or t["box_range"] or t["folder"])]
S["per_source"] = exports.source_coverage(conn)
(config.EXPORT_DIR / "summary.json").write_text(json.dumps(S, indent=1, default=lambda o: list(o) if o is not None else None))


def rng(ys):
    out, start, prev = [], None, None
    for y in ys:
        if start is None:
            start = prev = y
        elif y == prev + 1:
            prev = y
        else:
            out.append(f"{start}" if start == prev else f"{start}-{prev}")
            start = prev = y
    if start is not None:
        out.append(f"{start}" if start == prev else f"{start}-{prev}")
    return ", ".join(out) or "none"


L = []
A = L.append
A("# Paul Conrad Master Archive — REPORT\n")
A(f"_Generated {db.now()} by `scripts/report.py`. Metadata-only index; no Conrad image is stored or displayed._\n")
A("> **Not complete, and not claimed to be.** Item-level records are the only ones that name an individual cartoon. "
  "Huntington *box-range slots* are one per CON folder number with the BOX's date span (dates interpolated, flagged "
  "`date_is_estimate`); Syracuse records are *folder-level*. An unmeasured source (blocked / paywalled / not attempted) is "
  "listed as such, never as covered.\n")
A("## Headline numbers\n")
A("| metric | value |\n|---|---|")
e, l = S["earliest_item"], S["latest_item"]
rows = [
    ("TOTAL CANONICAL CARTOONS (item-level)", S["total_canonical_item_level"]),
    ("TOTAL BOX-RANGE SLOTS (Huntington CON numbers, range-level)", S["total_box_range_slots"]),
    ("Folder-level records (Syracuse folders, LOC proof set, etc.)", S["total_folder_records"]),
    ("TOTAL SOURCE RECORDS (cartoon_sources rows, seed + live)", f"{S['total_source_records']} (live-verified rows: {S['total_source_records_live']})"),
    ("TOTAL REPOSITORIES with records", S["total_repositories_with_records"]),
    ("Sources registered / probed (data/sources.json)", S["total_sources_registered"]),
    ("EARLIEST CARTOON (item-level)", f"{e[0]} — {e[1]} ({e[2]})" if e else "n/a"),
    ("LATEST CARTOON (item-level)", f"{l[0]} — {l[1]} ({l[2]})" if l else "n/a"),
    ("Earliest / latest item with an EXACT date", f"{S['earliest_item_exact'][0]} {S['earliest_item_exact'][1]} / "
                                                  f"{S['latest_item_exact'][0]} {S['latest_item_exact'][1]}"),
    ("Earliest / latest date on ANY record (incl. ranges)", f"{S['earliest_any'][0]} / {S['latest_any'][0]}"),
    ("YEARS WITH GAPS (no item-level record, 1945-2010)", f"{len(S['years_with_no_item_records'])}: {rng(S['years_with_no_item_records'])}"),
    ("Years with NO record of any granularity", rng(S["years_with_no_records_at_all"])),
    ("POSSIBLE DUPLICATES (manual review)", S["possible_duplicates_for_review"]),
    ("Auto-linked duplicates (merged_into, never deleted)", S["auto_linked_duplicates"]),
    ("contained_in candidates (item -> box/folder)", S["contained_in_candidates"]),
    ("RECORDS WITH PUBLIC IMAGE LINKS (canonical, link-out only)", S["canonical_with_public_image_link"]),
    ("RECORDS REQUIRING ARCHIVE ACCESS (canonical, no online image)",
     f"{S['canonical_requiring_archive_access']} (item-level: {S['archive_access_items']}; rest are box-range slots / folders)"),
    ("Local images stored (must be 0)", S["local_images_stored"]),
    ("Source rows per acquisition_method", ", ".join(f"{k}={v}" for k, v in S["source_rows_by_acquisition_method"].items())),
    ("Item-level canonical records acquired ONLY via reader bypass (pending direct verification)",
     S["item_level_pending_direct_verification"]),
    ("Item-level canonical records known ONLY from a secondary citation", S["item_level_secondary_citation_only"]),
    ("Item-level canonical records with a quote-verified web citation (`secondary_web`, cycle 3)",
     conn.execute("""SELECT COUNT(DISTINCT COALESCE(k.merged_into,k.id)) FROM cartoon_sources x JOIN cartoons k
                     ON k.id=x.cartoon_id WHERE x.source_id='secondary_web'""").fetchone()[0]),
]
for k, v in rows:
    A(f"| {k} | {v} |")
A("\n## Per-source status\n")
A("| source | classification | status | rows | item | folder | box_range | public img | notes |\n|---|---|---|---|---|---|---|---|---|")
for c in S["per_source"]:
    A(f"| `{c['source_id']}` {c['name']} | {c['classification']} | **{c['status']}** | {c['rows']} | {c['item']} | "
      f"{c['folder']} | {c['box_range']} | {c['public_image']} | {(c['notes'] or '').replace('|', '/')[:220]} |")
A("\n## Per-year table, 1945-2010\n")
A("Columns: item = item-level canonical cartoons; slots = Huntington box-range slots (by interpolated year); folder = "
  "folder-level records; repository columns count canonical records of any granularity with a source there; LA Times / "
  "Denver Post = publication attribution; missing = item records lacking a title or an exact date.\n")
cols = ["item", "box_range", "folder", "Huntington", "Syracuse", "LOC", "Ohio State", "Wichita", "LA Times", "Denver Post",
        "Iowa", "other", "missing_meta"]
A("| year | " + " | ".join(["item", "slots", "folder", "Huntington", "Syracuse", "LOC", "Ohio St", "Wichita", "LA Times",
                            "Denver Post", "Iowa", "other", "missing"]) + " |")
A("|" + "---|" * (len(cols) + 1))
tot = Counter()
for y, t in table.items():
    A(f"| {y} | " + " | ".join(str(t[c]) for c in cols) + " |")
    tot.update({c: t[c] for c in cols})
A("| **total** | " + " | ".join(f"**{tot[c]}**" for c in cols) + " |")
A("\n## Books (bibliography)\n")
A("| title | year | ISBN | OCLC | Internet Archive | access | verified |\n|---|---|---|---|---|---|---|")
for b in conn.execute("SELECT * FROM books ORDER BY COALESCE(year,9999)"):
    A(f"| {b['title']} | {b['year'] or ''} | {b['isbn'] or ''} | {b['oclc'] or ''} | {b['ia_identifier'] or ''} | "
      f"{b['ia_access'] or ''} | {'yes' if b['verified'] else 'NO'} |")
A("""
## How to get more

Every source below is blocked or unmeasured **by rule, not by effort** (no WAF/CAPTCHA/paywall bypass, no reader proxy,
robots.txt honoured per path). Each unlocks with one human action:

| blocked / unmeasured source | what it holds | human action that unlocks it |
|---|---|---|
| Huntington Library — Paul Conrad Papers | ~9,500 originals / 12,360 CON pieces; item titles + dates only in the Huntington's internal database (public aid = 8,628 box-range slots); huntington.org answers this crawler with HTTP 429 | Send the **export request** in `docs/huntington-request-DRAFT.md` (CSV: CON number, title/caption, date, publication) to Huntington Manuscripts reference; ask for a rate-limit allowance if a crawl is preferred |
| Syracuse University SCRC — Paul Conrad Cartoons | 65 folders; item lists behind an AWS WAF challenge (library.syracuse.edu, Empire ADC) | Ask SCRC reference for **permission / an allow-listed User-Agent**, or a copy of the finding aid (EAD/PDF); current Syracuse data is the r.jina.ai seed, pending direct verification |
| Wichita State Special Collections — MS 90-18 | 207 item records known only via the r.jina.ai seed; the ArchivesSpace PUI WAF-challenges bots | Ask Special Collections for **permission or an EAD/CSV export** of MS 90-18 so the 207 seed items can be verified directly |
| Ohio State — Billy Ireland Cartoon Library & Museum | unmeasured; the PastPerfect host 403s even robots.txt | Request **permission / a catalog export** of Conrad holdings from the Billy Ireland reading room |
| The Daily Iowan 1946-1950 (Conrad's first cartoons) | 1,845 public issue PDFs with no text layer; this project may not download or OCR scans | A human pages the issues in `data/exports/daily_iowan_issues_1945_1950.csv` (the **PDF review list**; 1,539 issues in Conrad's UI years) and records cartoon captions + dates |
| LA Times / Denver Post back files | the cartoons as published (paywalled: ProQuest, newspapers.com, NewsBank) | A subscriber exports citation lists (date, page, caption) — no scans needed |
| NYT (5 Sep 2010) and Washington Post (6 Sep 2010) obituaries | name further dated cartoons | Live pages are WAF-blocked / robots-disallowed, so their Wayback copies are **deliberately not used**; a reader with access can confirm and add them |
| Wikidata / Wikimedia Commons structured queries | 'creator = Paul Conrad' reverse lookups | MediaWiki API + SPARQL are robots-disallowed for generic agents; a human can run the query in the Wikidata Query Service UI (Commons' Paul Conrad category holds 1 photo, 0 cartoons) |
| Nixon Library / Nixon Foundation / other presidential libraries | originals sent to presidents (unverified lead) | Site searches returned nothing citable; an archivist inquiry to each reference desk |

## Method, provenance caveats, and what is NOT covered

1. **Huntington** — the public EAD (`cinco-prd.s3.amazonaws.com/media/ead/conrad.xml`, the file OAC renders) was re-fetched
   and parsed live: 206 original-drawing boxes, CON ranges -> 8,628 slots, identical to the seed. The Huntington describes
   ~9,500 originals / 12,360 CON pieces; the ~900-slot difference (and everything 2004-2010) is not itemised in the public
   aid. Two CON-range typos in the aid were repaired (logged in crawl notes). `catalog.huntington.org/record=` is
   robots-disallowed; the Verso article returned HTTP 429 (not retried aggressively). Slot titles require the Huntington's
   internal database — see `docs/huntington-request-DRAFT.md`.
2. **Library of Congress** — `robots.txt` (Crawl-delay 5) disallows `/search`, `/pictures/search` and `/pictures/related`;
   none were paged. Deep enumeration (TK-12199) used only permitted JSON: `/photos/`, `/manuscripts/`, `/books/`, `/maps/`
   and `/collections/cartoon-drawings/` (`fa=contributor:conrad, paul` + keyword queries, `sp=` pagination) — together they
   expose only 17 digitized Conrad ids (manuscripts/books/maps: 0 item hits). Every known id (seed + format endpoints +
   `2010634752`, a Conrad lot whose creator field is empty but whose notes say "Creators include: Paul Conrad") was verified
   via `/pictures/item/<id>/?fo=json`, and the P&P control numbers +/-5 around every Conrad id were probed through the same
   permitted endpoint (130 ids probed, 0 new Conrad items). The seed ids came from a cached `/pictures/search` response made
   by TK-12179 (a robots-disallowed path — not repeated here). **Group/lot records** (folder-level): `2010632868` (83 LA Times
   proofs), `2010634752` (21 drawings), `2010634813` (7 drawings), `2010635027` (24 drawings) are all "Unprocessed" with no
   digitized components and no child item records; the only component listing is the "neighbors" view under
   `/pictures/related/`, which robots.txt disallows — so their ~135 components are **not individually addressable** and no
   item records were fabricated for them. LOC items outside these ids are unmeasured.
3. **Wichita State** — the legacy static finding aid now redirects to the ArchivesSpace home page; the ArchivesSpace PUI
   answers non-browser clients with an AWS WAF JS challenge (HTTP 202, empty body). No bypass was used: source marked
   blocked/REQUIRES_PERMISSION. **Caveat:** the Wichita seed records (140 detail pages + 67 sibling-listing entries) were
   fetched earlier by TK-12179 through `r.jina.ai`, which renders past that challenge; they are used as-is with provenance.
4. **Syracuse** — same WAF situation (HTTP 202) for `library.syracuse.edu`; Empire ADC robots unreadable. The 65 folder
   records + 388 index headings come from the TK-12179 `r.jina.ai` cache (same caveat). Folder-level only.
5. **Ohio State (Billy Ireland)** — PastPerfect host returns 403 even for `robots.txt`: treated as disallowed, not crawled.
   Unmeasured — the OSU holding may be substantial.
6. **LA Times / Denver Post** — archives are paywalled (ProQuest, newspapers.com, NewsBank); nothing fetched. Their columns
   count publication attributions found in other repositories only.
7. **Daily Iowan** — 1,845 issue PDFs for 1945-1950 are public (robots `Allow: /`), but the archive exposes **no separate
   text/OCR layer**: per-issue `.txt` / ALTO `.xml` / `.hocr` / `.html` sidecars all 404, the `DI/<year>/` directory is
   403, the site "search" is a Google CSE embed (`cse.google.com` robots `Disallow: /` — not used), and the Iowa Digital
   Library host 403s even `robots.txt`. It is not in Chronicling America and has no Internet Archive run. Any OCR text
   lives only inside the PDFs, which this project may not download, and scans are never OCR'd — so **0 Iowa cartoon
   records** were added (none inferred). The full issue list (date, weekday, PDF URL; 1,539 issues in Conrad's UI years
   1946-1950) is in `data/exports/daily_iowan_issues_1945_1950.csv` for manual page review.
8. **Internet Archive** — book metadata only (lending-library scans, not opened). Name-authority conflations (other
   "Paul Conrad"s, incl. one mis-tagged 1924-2010) were detected and excluded.
9. **Discovery** — DPLA (key present) and Smithsonian Open Access (key present) queried; Calisphere and Empire ADC (WAF) and
   HathiTrust (robots unreadable) blocked; WorldCat and presidential libraries listed as UNVERIFIED leads only.
10. **Dedupe** — metadata scoring (exact date +35, title fuzz +25, publication +10, caption +15, subjects +5, people +5).
    Near-identical titles in the same year auto-link; everything else -> `duplicates.csv`. Records are never deleted
    (`merged_into`). Box-range/folder records are never merged into items — they get `contained_in_candidate` links.
    **Perceptual-hash dedupe is skipped** because the copyright rule forbids downloading Conrad images.
11. **Copyright** — `cartoon_sources.local_image` has a `CHECK (local_image IS NULL)` constraint; image/thumbnail URLs are
    stored as metadata and the viewer renders only "View at <repository>" link-outs (enforced by a negative test).
12. **Cost** — $0: only free public endpoints, no paid APIs.
13. **Secondary citations, cycle 3** (`secondary_web`, `crawlers/secondary_citations.py`) — item records only for cartoons
    that a fetched page names with a title/caption AND a year: PBS Independent Lens "Conrad Gallery" (27 captioned cartoons;
    Wayback copies of the retired pbs.org pages whose origin robots.txt allows the path; only page HTML + `<img alt>` text
    read, never an image), the LA Times 2010 obituary (free archive page), the LOC Information Bulletin (Oct 1999),
    Wikipedia, National Catholic Reporter (2001) and Canyon News (2010). Every citation carries a verbatim quote that must
    be present on the fetched page or the record is rejected; notes hold the <=200-char quote. Year-only dates are
    `date_is_estimate=1`. Source-internal inconsistencies (e.g. PBS dating a 'kinder, gentler' cartoon 1984) are kept as
    stated and flagged in notes. Records believed to match seed-catalog entries reuse the seed title so dedupe links them
    (same year) or queues them for review (different year). Pulitzer prize pages name no single cartoon (year-of-work
    awards) -> 0 records; `Http.get` now refuses any image URL before I/O.
""")
(ROOT / "REPORT.md").write_text("\n".join(L) + "\n")
print(f"wrote REPORT.md; item={S['total_canonical_item_level']} slots={S['total_box_range_slots']}")

if "--gaps" in sys.argv:
    gaps = exports.research_gaps(conn)
    by = Counter(g[0] for g in gaps)
    print(json.dumps(by, indent=1))
    for kind in by:
        print(f"\n## {kind}")
        for g in [g for g in gaps if g[0] == kind][:15]:
            print(f"  {g[1]}: {g[2]}")