DB-cleanup/merge-output/phase1_merge.py

"""
Phase 1: copy saved article text from reported_K&R into the exported JSON.

Deterministic and verbatim. No model in the loop, so every write is checkable:
each copied field is hashed at source and destination and the two must agree.

Inputs are never modified. Everything is written to merge-output/.
"""

import hashlib
import json
import re
import unicodedata
from collections import defaultdict
from pathlib import Path

import openpyxl

BASE = Path(__file__).resolve().parent
SRC = BASE.parent
XLSX = SRC / "KR-reports_analysis_Sofi.xlsx"
JSONF = SRC / "attacks-export-Gart-website.json"

MERGED = BASE / "attacks-merged.json"
MANIFEST = BASE / "verification-manifest.json"
EXCEPTIONS = BASE / "exceptions-for-review.json"

ARTICLE_COLS = [("Articles", "article_1"), ("Article 2", "article_2"), ("Article 3", "article_3")]
MIN_LEN = 200  # below this a cell is a note or a stray URL, not an article body


def norm(s):
    if s is None:
        return ""
    s = unicodedata.normalize("NFKD", str(s))
    s = "".join(c for c in s if not unicodedata.combining(c))
    s = re.sub(r"[^a-z0-9 ]", " ", s.lower())
    return re.sub(r"\s+", " ", s).strip()


def sha(text):
    return hashlib.sha256(text.encode("utf-8")).hexdigest()


def year_of(v):
    try:
        return int(float(v))
    except (TypeError, ValueError):
        return None


def load_sheet():
    wb = openpyxl.load_workbook(XLSX, data_only=True, read_only=True)
    ws = wb["reported_K&R"]
    rows = list(ws.iter_rows(values_only=True))
    idx = {h: i for i, h in enumerate(rows[0]) if h is not None}
    out = []
    for n, r in enumerate(rows[1:], start=2):
        if not (r[idx["Victim"]] or r[idx["Date"]]):
            continue
        rec = {"_row": n}
        for h, i in idx.items():
            rec[h] = r[i]
        out.append(rec)
    wb.close()
    return out


def articles_in(row):
    """Substantive article bodies present on this sheet row."""
    found = {}
    for col, dest in ARTICLE_COLS:
        v = row.get(col)
        if isinstance(v, str) and len(v.strip()) >= MIN_LEN:
            found[dest] = v
    return found


def main():
    sheet = load_sheet()
    records = json.loads(JSONF.read_text(encoding="utf-8"))

    # Key on normalised victim name + year. Only 1:1 keys are trusted;
    # anything ambiguous is deliberately left for human adjudication.
    s_by_key = defaultdict(list)
    for row in sheet:
        s_by_key[(norm(row.get("Victim")), year_of(row.get("Year")))].append(row)

    j_by_key = defaultdict(list)
    for rec in records:
        j_by_key[(norm(rec.get("victim")), rec.get("year"))].append(rec)

    unambiguous = {
        k for k in set(s_by_key) & set(j_by_key)
        if len(s_by_key[k]) == 1 and len(j_by_key[k]) == 1
    }

    manifest = []
    exceptions = []
    copied_fields = 0
    copied_chars = 0
    matched_rows = 0

    for row in sheet:
        arts = articles_in(row)
        if not arts:
            continue

        key = (norm(row.get("Victim")), year_of(row.get("Year")))
        if key not in unambiguous:
            # Give the adjudicator everything it needs to decide, plus candidates.
            cands = []
            for rec in records:
                same_name = norm(rec.get("victim")) == key[0]
                same_slot = rec.get("year") == key[1] and norm(rec.get("country")) == norm(row.get("Country"))
                if same_name or same_slot:
                    cands.append({
                        "id": rec["id"],
                        "victim": rec.get("victim"),
                        "date": rec.get("date"),
                        "country": rec.get("country"),
                        "location": rec.get("location"),
                        "scenario": rec.get("scenario"),
                        "description": rec.get("description"),
                        "url": rec.get("url"),
                    })
            exceptions.append({
                "sheet_row": row["_row"],
                "victim": row.get("Victim"),
                "date": str(row.get("Date")),
                "year": key[1],
                "country": row.get("Country"),
                "location": row.get("Location"),
                "scenario": row.get("Scenario"),
                "description": row.get("Description"),
                "url": row.get("URL"),
                "reports": row.get("REPORTS"),
                "article_fields_waiting": sorted(arts),
                "article_chars": {k: len(v) for k, v in arts.items()},
                "candidates": cands,
                "reason": "ambiguous or absent 1:1 match on (victim, year)",
            })
            continue

        rec = j_by_key[key][0]
        matched_rows += 1
        for dest, text in arts.items():
            rec[dest] = text
            copied_fields += 1
            copied_chars += len(text)
            manifest.append({
                "json_id": rec["id"],
                "field": dest,
                "sheet_row": row["_row"],
                "sheet_column": next(c for c, d in ARTICLE_COLS if d == dest),
                "chars": len(text),
                "sha256_source": sha(text),
            })

        # Provenance. article_1 came from the row whose URL column is recorded;
        # article_2 / article_3 have no URL stored in the sheet.
        rec["article_provenance"] = {
            "sheet": "reported_K&R",
            "row": row["_row"],
            "sheet_url": row.get("URL"),
            "sheet_reports": row.get("REPORTS"),
            "fields": sorted(arts),
        }

    # Verify against what actually landed in the output objects.
    by_id = {r["id"]: r for r in records}
    mismatches = []
    for m in manifest:
        got = by_id[m["json_id"]].get(m["field"])
        if got is None or sha(got) != m["sha256_source"]:
            mismatches.append(m)

    MERGED.write_text(json.dumps(records, ensure_ascii=False, indent=2), encoding="utf-8")

    summary = {
        "sheet_rows_with_articles": sum(1 for r in sheet if articles_in(r)),
        "sheet_rows_merged": matched_rows,
        "sheet_rows_sent_to_review": len(exceptions),
        "article_fields_copied": copied_fields,
        "characters_copied": copied_chars,
        "json_records_total": len(records),
        "json_records_with_articles": sum(1 for r in records if "article_1" in r),
        "hash_mismatches": len(mismatches),
    }
    MANIFEST.write_text(
        json.dumps({"summary": summary, "mismatches": mismatches, "fields": manifest},
                   ensure_ascii=False, indent=2),
        encoding="utf-8")
    EXCEPTIONS.write_text(json.dumps(exceptions, ensure_ascii=False, indent=2), encoding="utf-8")

    for k, v in summary.items():
        print(f"{k:34} {v:,}" if isinstance(v, int) else f"{k:34} {v}")
    print("\nVERIFICATION:", "PASS - every copied field matches its source hash"
          if not mismatches else f"FAIL - {len(mismatches)} mismatched fields")


if __name__ == "__main__":
    main()