Keep a tracker document current from several sources

class Cell:
    """One field of one row, with who last wrote it and when.

    Per-field provenance is what makes the rest of this example possible. Without it there is no
    way to tell a value the last run wrote from a value somebody typed after a phone call, and a
    tracker that cannot tell those apart will eventually overwrite the second with the first.
    """

    value: str
    written_by: str  # "code" | "person"
    updated: str  # ISO 8601

def _check_quotes(proposals: Sequence[Proposal], body: str) -> tuple[list[Proposal], list[str]]:
    """Keep the proposals whose quote is really a piece of the message, and name the rest.

    A whitespace-normalized substring comparison, because a model re-wraps a sentence's line
    breaks even when it copies the words correctly. It catches a quote the model wrote rather
    than copied. It does not catch a real sentence read to mean something it does not say, which
    is a different mistake and is a person's to catch.
    """
    haystack = _normalize(body)
    kept: list[Proposal] = []
    dropped: list[str] = []
    for proposal in proposals:
        if _normalize(proposal.quote) and _normalize(proposal.quote) in haystack:
            kept.append(proposal)
        else:
            dropped.append(
                f"{proposal.message_id} proposed {proposal.project}.{proposal.field} on a quote "
                f"that is not in the message"
            )
    return kept, dropped

def reconcile(
    document: Document,
    sources: Sources,
    proposals: Sequence[Proposal],
    *,
    as_of: str,
    last_seen: dict[str, str],
) -> Reconciliation:
    """Everything after the reading, and none of it is a judgment.

    Four kinds of outcome, and the fourth is the one worth naming: a field is updated, or queued
    for a person, or left alone, or nobody confirmed it this week and the run says so.
    """
    stale = tuple(
        name
        for name, as_of_now in (
            ("project tracker", sources.tracker_as_of),
            ("time spreadsheet", sources.hours_as_of),
            ("shared inbox", sources.inbox_as_of),
        )
        if as_of_now <= last_seen.get(name, "")
    )
    tracker_by_project = {r.project: r for r in sources.tracker} if "project tracker" not in stale else {}
    hours_by_project = {r.project: r for r in sources.hours} if "time spreadsheet" not in stale else {}

    applied: list[Change] = []
    pending: list[Pending] = []
    aging: list[Aging] = []
    dropped: list[str] = []
    rows: list[Row] = []

    for row in document.rows:
        cells = dict(row.cells)
        record = tracker_by_project.get(row.project)
        hours = hours_by_project.get(row.project)
        incoming: dict[str, str] = {}
        if record is not None:
            incoming.update(
                status=record.status,
                next_milestone=record.next_milestone,
                milestone_date=record.milestone_date,
            )
        if hours is not None:
            incoming["hours_used"] = hours.hours_used

        for field, value in incoming.items():
            cell = cells[field]
            if cell.value == value:
                # Confirmed this week: the value did not move and now has a date saying a source
                # still agrees with it. That date is what `aging` below is measured from.
                cells[field] = replace(cell, updated=as_of)
                continue
            if cell.written_by == "person":
                pending.append(
                    Pending(
                        id=f"P-{len(pending) + 1:02d}",
                        project=row.project,
                        field=field,
                        old=cell.value,
                        new=value,
                        why=f"the {FIELD_SOURCE[field]} disagrees with a value a person typed in",
                    )
                )
                continue
            applied.append(Change(row.project, field, cell.value, value, FIELD_SOURCE[field]))
            cells[field] = Cell(value=value, written_by="code", updated=as_of)

        for field, source_name in FIELD_SOURCE.items():
            if field in incoming:
                continue
            reason = (
                f"the {source_name} has not been refreshed since the last run"
                if source_name in stale
                else f"the {source_name} no longer lists this project"
            )
            age = _days(as_of, cells[field].updated)
            if age >= AGING_DAYS:
                aging.append(Aging(row.project, field, age, reason))

        rows.append(Row(project=row.project, cells=cells))

    known = {row.project for row in document.rows}
    for record in sources.tracker:
        if record.project not in known:
            pending.append(
                Pending(
                    id=f"P-{len(pending) + 1:02d}",
                    project=record.project,
                    field="(new row)",
                    old="",
                    new=f"{record.status}, {record.next_milestone} {record.milestone_date}",
                    why="a project no row covers yet, and a new row needs an owner",
                )
            )

    absent = tuple(
        row.project
        for row in document.rows
        if "project tracker" not in stale and row.project not in tracker_by_project
    )

    for proposal in proposals:
        if proposal.project not in known:
            dropped.append(f"{proposal.message_id} named a project no row covers: {proposal.project}")
            continue
        if proposal.field not in FIELDS:
            dropped.append(f"{proposal.message_id} named a field the tracker does not have: {proposal.field}")
            continue
        current = next(r for r in document.rows if r.project == proposal.project).cells[proposal.field]
        if current.value == proposal.value:
            dropped.append(f"{proposal.message_id} proposed {proposal.project}.{proposal.field}, which already says that")
            continue
        pending.append(
            Pending(
                id=f"P-{len(pending) + 1:02d}",
                project=proposal.project,
                field=proposal.field,
                old=current.value,
                new=proposal.value,
                why=f"read out of {proposal.message_id}, which is a claim and not a record",
                quote=proposal.quote,
            )
        )

    return Reconciliation(
        document=Document(as_of=as_of, rows=tuple(rows)),
        applied=tuple(applied),
        pending=tuple(pending),
        # Every cell this run did not write. On a tracker that is almost all of them, and the
        # number is here because leaving a field alone is the outcome this recipe exists to
        # protect, not the absence of an outcome.
        unchanged=sum(len(row.cells) for row in document.rows) - len(applied),
        stale_sources=stale,
        absent_rows=absent,
        aging=tuple(aging),
        dropped=tuple(dropped),
    )

def approve(reconciliation: Reconciliation, accepted: Sequence[str], *, as_of: str) -> Document:
    """The second half, called separately once a person has actually looked.

    `accepted` is the ids of the pending changes they approved. Everything else in the queue stays
    out of the document. A cell written here is marked `written_by="person"`, because it was: the
    person decided it, and next week's run must not overwrite it without asking again.

    Two things this function refuses to do. Approving two changes to the same cell raises rather
    than letting the later one win, because the queue can legitimately hold two different answers
    for one field and picking between them is the decision being approved. And a "(new row)" item
    is not applied here: a new row needs an owner, and nothing in this file can supply one.
    """
    wanted = set(accepted)
    keep = {item.id for item in reconciliation.pending if item.id in wanted}
    cells_touched: dict[tuple[str, str], str] = {}
    for item in reconciliation.pending:
        if item.id not in keep or item.field == "(new row)":
            continue
        key = (item.project, item.field)
        if key in cells_touched:
            raise ValueError(
                f"{cells_touched[key]} and {item.id} both change {item.project}.{item.field}; "
                f"approve one of them"
            )
        cells_touched[key] = item.id
    rows: list[Row] = []
    for row in reconciliation.document.rows:
        cells = dict(row.cells)
        for item in reconciliation.pending:
            if item.id in keep and item.project == row.project and item.field in cells:
                cells[item.field] = Cell(value=item.new, written_by="person", updated=as_of)
        rows.append(Row(project=row.project, cells=cells))
    return Document(as_of=as_of, rows=tuple(rows))