Skip to content

armgit / peach

Public Imported from GitHub
0

Peach: Local-first video metadata scraper and media manager.

13.5 KB
Raw Blame
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""用 r18dev 同一响应中的官方日文系列名修正 Peach 规范实体。

输入是 ``scrape_codes.py`` 生成的字段候选 CSV。脚本会回读每条候选引用的原始
快照,只接受 r18dev 响应 ``translations`` 中明确标为日文的系列名。默认只输出
审计 CSV;真实写入必须同时使用 ``--apply --backup``。证据冲突、兼容投影不一致
或缺少日文原文的实体一律跳过。
"""
from __future__ import annotations

import argparse
import json
import re
import sqlite3
import sys
from collections import Counter, defaultdict
from datetime import datetime, timezone
from pathlib import Path

PROJECT_ROOT = Path(__file__).resolve().parent.parent
SRC_DIR = PROJECT_ROOT / "src"
if str(SRC_DIR) not in sys.path:
    sys.path.insert(0, str(SRC_DIR))

from peach.config import GENERATED_DIR
from peach.entities import merge_entity, normalize_entity_name
from peach.metadata import collapse_repeated_phrase
from peach.review_csv import read_rows, write_rows
from peach.scripting import add_ledger_write_args, counts_of, open_for_write, verify_after_write


SOURCE = "r18dev:series-localization"
MERGE_SOURCE = "merge:series-localization"
FIELDS = (
    "entity_id", "current_name", "target_name", "assets", "codes",
    "evidence_codes", "raw_snapshots", "action", "reason", "merge_target_id",
    "revision",
)


def _contains_latin(value: str) -> bool:
    return bool(re.search(r"[A-Za-z]", value or ""))


def _truthy(value: object) -> bool:
    return value is True or str(value).strip().lower() in {"1", "true", "yes"}


def _code_key(value: str) -> str:
    return re.sub(r"[^A-Z0-9]", "", str(value or "").upper())


def _snapshot_japanese_series(path: Path) -> str:
    try:
        wrapper = json.loads(path.read_text(encoding="utf-8"))
    except (OSError, UnicodeError, ValueError, TypeError):
        return ""
    if str(wrapper.get("source") or "") != "r18dev":
        return ""
    payload = wrapper.get("result")
    if not isinstance(payload, dict):
        return ""
    for translation in payload.get("translations") or []:
        if not isinstance(translation, dict):
            continue
        if not str(translation.get("language") or "").lower().startswith("ja"):
            continue
        value, _ = collapse_repeated_phrase(str(translation.get("series") or ""))
        return value.strip()
    return ""


def read_evidence(path: Path) -> dict[str, list[dict[str, str]]]:
    """回读候选及原始快照,返回按规范番号分组的可验证日文系列名。"""
    by_code: dict[str, list[dict[str, str]]] = defaultdict(list)
    for row in read_rows(path):
            if row.get("field") != "series":
                continue
            query = _code_key(str(row.get("query") or row.get("code") or ""))
            try:
                candidates = json.loads(row.get("candidates_json") or "[]")
            except (TypeError, ValueError):
                continue
            for candidate in candidates if isinstance(candidates, list) else []:
                if not isinstance(candidate, dict):
                    continue
                if (candidate.get("source") != "r18dev"
                        or candidate.get("source_kind") != "official_mirror"
                        or not _truthy(candidate.get("official"))):
                    continue
                raw_value = str(candidate.get("raw_snapshot") or "").strip()
                if not raw_value:
                    continue
                snapshot = Path(raw_value)
                if not snapshot.is_absolute():
                    snapshot = path.parent / snapshot
                japanese = _snapshot_japanese_series(snapshot)
                candidate_value, _ = collapse_repeated_phrase(
                    str(candidate.get("value") or ""))
                if not japanese or japanese != candidate_value.strip():
                    continue
                by_code[query].append({
                    "target": japanese,
                    "snapshot": str(snapshot.resolve()),
                    "source_url": str(candidate.get("source_url") or ""),
                })
    return dict(by_code)


def collect(
    connection: sqlite3.Connection,
    evidence: dict[str, list[dict[str, str]]],
    revision: str,
) -> list[dict[str, object]]:
    connection.row_factory = sqlite3.Row
    entity_rows = connection.execute(
        "SELECT id,canonical_name,normalized_name FROM entity "
        "WHERE kind='series' ORDER BY id"
    ).fetchall()
    owners = {str(row["normalized_name"]): int(row["id"]) for row in entity_rows}
    rows: list[dict[str, object]] = []

    for entity in entity_rows:
        entity_id = int(entity["id"])
        assets = connection.execute(
            "SELECT DISTINCT a.id,a.code,a.series FROM asset a "
            "JOIN asset_entity ae ON ae.asset_id=a.id "
            "WHERE ae.entity_id=? AND ae.role='series' AND a.medium='video' "
            "ORDER BY a.id",
            (entity_id,),
        ).fetchall()
        codes = sorted({_code_key(str(row["code"] or "")) for row in assets
                        if str(row["code"] or "").strip()})
        mismatched_assets = [
            int(row["id"]) for row in assets
            if str(row["series"] or "").strip()
            and normalize_entity_name(str(row["series"])) != str(entity["normalized_name"])
        ]
        proofs = [proof for code in codes for proof in evidence.get(code, [])]
        targets = sorted({proof["target"] for proof in proofs}, key=str.casefold)
        base: dict[str, object] = {
            "entity_id": entity_id,
            "current_name": str(entity["canonical_name"]),
            "target_name": "",
            "assets": len(assets),
            "codes": "|".join(codes),
            "evidence_codes": "|".join(sorted(
                code for code in codes if evidence.get(code))),
            "raw_snapshots": "|".join(sorted({proof["snapshot"] for proof in proofs})),
            "action": "",
            "reason": "",
            "merge_target_id": "",
            "revision": revision,
            "_proofs": proofs,
        }
        if mismatched_assets:
            base.update(
                action="skip-projection-mismatch",
                reason="asset.series 与规范系列关系不一致:" +
                       ",".join(map(str, mismatched_assets)),
            )
        elif not _contains_latin(str(entity["canonical_name"])):
            base.update(
                target_name=str(entity["canonical_name"]),
                action="keep-localized", reason="规范名已无拉丁字母",
            )
        elif not targets:
            base.update(action="skip-no-japanese-evidence", reason="没有可验证的官方日文系列名")
        elif len(targets) != 1:
            base.update(
                action="skip-evidence-conflict",
                reason="同一系列关联番号返回多个日文名:" + " | ".join(targets),
            )
        else:
            target = targets[0]
            normalized = normalize_entity_name(target)
            base["target_name"] = target
            if normalized == str(entity["normalized_name"]):
                base.update(action="keep-source-same", reason="官方日文原文与当前规范名相同")
            elif normalized in owners and owners[normalized] != entity_id:
                base.update(
                    action="merge-into-existing", reason="日文规范实体已存在",
                    merge_target_id=owners[normalized],
                )
            else:
                base.update(action="rename", reason="官方日文翻译证据唯一")
        rows.append(base)
    return rows


def _insert_alias(
    connection: sqlite3.Connection, entity_id: int, alias: str, revision: str,
) -> int:
    normalized = normalize_entity_name(alias)
    canonical = connection.execute(
        "SELECT normalized_name FROM entity WHERE id=?", (entity_id,)
    ).fetchone()[0]
    if not normalized or normalized == canonical:
        return 0
    connection.execute(
        "INSERT OR IGNORE INTO entity_alias(entity_id,alias,normalized_alias,source,confidence) "
        "VALUES(?,?,?,?,1.0)",
        (entity_id, alias, normalized, f"{SOURCE}@{revision[:12]}"),
    )
    return int(connection.execute("SELECT changes()").fetchone()[0])


def _write_provenance(
    connection: sqlite3.Connection, entity_id: int, row: dict[str, object], revision: str,
) -> None:
    raw = connection.execute(
        "SELECT metadata_json FROM entity WHERE id=?", (entity_id,)
    ).fetchone()[0]
    try:
        metadata = json.loads(raw or "{}")
    except (TypeError, ValueError):
        metadata = {}
    metadata["series_name_localization"] = {
        "source": "r18dev",
        "source_kind": "official_mirror",
        "revision": revision,
        "previous_name": str(row["current_name"]),
        "japanese_name": str(row["target_name"]),
        "evidence_codes": str(row["evidence_codes"]).split("|")
        if row["evidence_codes"] else [],
        "raw_snapshots": str(row["raw_snapshots"]).split("|")
        if row["raw_snapshots"] else [],
    }
    connection.execute(
        "UPDATE entity SET metadata_json=? WHERE id=?",
        (json.dumps(metadata, ensure_ascii=False, separators=(",", ":")), entity_id),
    )


def _sync_projection(connection: sqlite3.Connection, entity_id: int, target: str) -> int:
    connection.execute(
        "UPDATE asset SET series=? WHERE id IN ("
        "SELECT asset_id FROM asset_entity WHERE entity_id=? AND role='series'"
        ") AND COALESCE(series,'')<>?",
        (target, entity_id, target),
    )
    return int(connection.execute("SELECT changes()").fetchone()[0])


def apply_rows(
    connection: sqlite3.Connection, rows: list[dict[str, object]], revision: str,
) -> dict[str, int]:
    counts = Counter()
    stamp = datetime.now(timezone.utc).isoformat(timespec="seconds")
    for row in rows:
        action = str(row["action"])
        if action not in {"rename", "merge-into-existing"}:
            continue
        entity_id = int(row["entity_id"])
        target = str(row["target_name"])
        if action == "merge-into-existing":
            target_id = int(row["merge_target_id"])
            moved = merge_entity(
                connection, target_id=target_id, source_id=entity_id,
                source_name=str(row["current_name"]), alias_source=MERGE_SOURCE,
            )
            counts["merged"] += 1
            counts["relations_moved"] += moved["assets"]
            counts["dropped_refs"] += moved["dropped_refs"]
            counts["aliases"] += _insert_alias(
                connection, target_id, str(row["current_name"]), revision)
            counts["projections"] += _sync_projection(connection, target_id, target)
            _write_provenance(connection, target_id, row, revision)
            counts["provenance"] += 1
            continue

        connection.execute(
            "UPDATE entity SET canonical_name=?,normalized_name=?,updated_at=? WHERE id=?",
            (target, normalize_entity_name(target), stamp, entity_id),
        )
        counts["renamed"] += int(connection.execute("SELECT changes()").fetchone()[0])
        counts["aliases"] += _insert_alias(
            connection, entity_id, str(row["current_name"]), revision)
        counts["projections"] += _sync_projection(connection, entity_id, target)
        _write_provenance(connection, entity_id, row, revision)
        counts["provenance"] += 1
    return dict(counts)


def write_csv(path: Path, rows: list[dict[str, object]]) -> None:
    write_rows(path, FIELDS, rows, fill_missing=True)


#: 本脚本自己关心的口径;基础计数由 `scripting.counts_of` 给。
EXTRA_COUNTS = {
    "series": "SELECT count(*) FROM entity WHERE kind='series'",
    "latin_series": "SELECT count(*) FROM entity WHERE kind='series' "
                    "AND canonical_name GLOB '*[A-Za-z]*'",
}


def build_parser() -> argparse.ArgumentParser:
    parser = argparse.ArgumentParser(description="用官方日文证据本地化系列规范名")
    add_ledger_write_args(parser)
    parser.add_argument("--candidates", type=Path, required=True)
    parser.add_argument("--revision", required=True)
    parser.add_argument(
        "--audit-csv", type=Path,
        default=GENERATED_DIR / "series-name-localization.csv",
    )
    return parser


def run(args: argparse.Namespace) -> int:
    evidence = read_evidence(args.candidates)
    connection = open_for_write(args)
    connection.execute("PRAGMA foreign_keys=ON")
    try:
        rows = collect(connection, evidence, args.revision)
        write_csv(args.audit_csv, rows)
        print(f"已审计 series {len(rows)} 个;审计 CSV:{args.audit_csv}")
        print("  动作分布:", dict(Counter(str(row["action"]) for row in rows)))
        if not args.apply:
            print("  未写 ledger(加 --apply --backup 才写)")
            return 0

        print(f"  已备份到 {args.backup}")
        before = counts_of(connection, EXTRA_COUNTS)
        with connection:
            changed = apply_rows(connection, rows, args.revision)
        after = counts_of(connection, EXTRA_COUNTS)
        integrity, foreign_keys = verify_after_write(connection)
        print("  写入结果:", changed)
        for key in before:
            print(f"    {key}: {before[key]} -> {after[key]}")
        print(f"  integrity_check={integrity};foreign_key_check={foreign_keys}")
        return 1 if integrity != "ok" or foreign_keys else 0
    finally:
        connection.close()


def main(argv: list[str] | None = None) -> int:
    return run(build_parser().parse_args(argv))


if __name__ == "__main__":
    raise SystemExit(main())