Skip to content

armgit / peach

Public Imported from GitHub
0

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

46.0 KB
Raw Blame
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
r"""同一个人被记成两条实体的去重(跨 kind 与同 kind 两类)。

`entity` 的唯一约束是 `(kind, normalized_name)`,所以同名但 kind 不同的两条记录
可以并存。实际发生了 35 组:卡片上的头像和名字会一个跳 `/performers/x`、另一个跳
`/creators/x`,统计、筛选和资料页也各算各的。

第三类是名录页重复:两条实体各按自己的名字检索,链接安装时落到同一个 minnano-av
名录页,那就是站方认为的同一个人。判据见 `_directory_identity_plan`。

第四类是艺名重复:一个名字已经作为别名登记在另一条实体上,它却还自己占着一条实体。
判据与闸门见 `_stage_name_plan`。

两条来路很清楚:

    creator   ← source='legacy:asset',从目录名投影出来(ADR-0013:目录名只是候选证据)
    performer ← source='r18:performer' / 'javbus:performer'(真实发行元数据)
                或 'javinizer:<站点>:performer'(刮削链新建,同样是发行元数据)
                或 'performer'(Stash 扁平模型)

除了完全同名,还会处理一类高置信变体:两边关联的非空作品集合完全相同,且 performer
别名等于 creator 名,或 creator 名由 performer 本名和账号别名拼成。名字相似但作品集合
不同的不会自动合并。

**保留哪一边只看 provenance,不看数量**:

- performer 侧带 `r18:performer` / `javbus:performer` / `javinizer:<站点>:performer` 的保留
  performer。那是发行元数据,creator 侧只是
  网盘目录名恰好等于艺名,正是 ADR-0013 要防的那种误投影。
- 否则保留 creator。剩下的 performer 断言全部来自 Stash 的扁平 performer 模型——
  `docs/STASH.md` 已把它判为已知缺陷(把上传者、合集和演员塞进同一个平面)——而
  creator 侧往往对应一个真实的本地目录。

按数量投票会翻车:`小桃` 的 performer 侧有 151 条、creator 侧只有 15 条,但它是
本地 `media/创作者/小桃` 的上传者,不是女优。

## 同 kind 的目录名投影

跨 kind 那一轮合并完成后,`R:\Media\<本名 + 账号名>` 这类目录还会各自留下一条**同为
creator** 的实体,`(kind, normalized_name)` 唯一约束拦不住它:`小桃` / `小桃 shixiaotaone`
和 `猫猫碎冰冰` / `猫猫碎冰冰 & 趣趣` 就是这样在详情页和 `/creators` 索引页并排出现的。

判据要求四项同时成立,缺一项就留给人工,不自动合并:

1. 两条同 kind,被丢弃方的作品集合非空且**完全落在**保留方的作品集合里;
2. 被丢弃方的名字词集**真包含**保留方的名字词集,多出来的词(去掉 `&` 之类的连接符后)
   全部是保留方的已登记别名;
3. 被丢弃方只有 `legacy:asset` 断言,且没有别名、没有外部引用——只是目录名投影;
4. 该投影只匹配到唯一一个保留方。

保留方是带别名和外部引用的规范实体,被丢弃的名字降为别名,扁平 `asset.creator` 一并
改写成保留名(ADR-0005:兼容投影跟着规范关系走)。

合并不可逆。默认只产出复核 CSV,`--apply` 才写 ledger 且必须给 `--backup`。
"""
from __future__ import annotations

import argparse
import json
import re
import sqlite3
import sys
from collections import Counter
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 (
    canonicalize_entity_name,
    collapse_repeated_entity_name,
    merge_entity,
    normalize_entity_name,
)
from peach.review_csv import write_rows
from peach.scripting import add_ledger_write_args, counts_of, open_for_write, verify_after_write


#: 真实发行元数据的来源标记。只有这些能把一条断言判成「这是女优,不是上传者」。
RELEASE_SOURCES = frozenset({"r18:performer", "javbus:performer"})

#: 刮削链新建 performer 时写的来源整族:`javinizer:javdb:performer`、
#: `javinizer:r18dev:performer`、`javinizer:dmm:performer` …… 站点还会增加,所以按形状认
#: 而不是逐个登记。它们和 `r18:performer` 是同一类东西——发行方元数据,不是 Stash 那套
#: 把上传者和演员塞进一个平面的扁平模型;漏掉这一族会让刚刮出来的 performer 被当成扁平
#: 断言,合并时保留目录名投影出来的 creator 侧。
RELEASE_SOURCE_PREFIX = "javinizer:"
RELEASE_SOURCE_SUFFIX = ":performer"
MERGE_ALIAS_SOURCE = "merge:duplicate-identity"

#: 目录名里把两个称呼拼在一起的连接符。它们本身不是名字的一部分。
JOINER_TOKENS = frozenset({"&", "&", "+", "+", "/", "/", "、", ",", ","})

#: 目录名投影的唯一来源标记:没有别名、没有外部引用,只有从路径投影出来的断言。
PROJECTION_SOURCES = frozenset({"legacy:asset"})

#: 链接安装把名录页号写进了自由文本证据里(`entity_link.metadata_json` 的 `evidence`)。
#: minnano-av 每位女优一页,页号就是身份——比名字强,也比别名强:两条实体各按自己的
#: 名字检索、落到同一页,说明站方认为这是同一个人。页号本身该升成 `entity_external_ref`,
#: 那样唯一约束会先一步拦住重复;在那之前只能从这段文本里读回来。
MINNANO_ACTRESS = re.compile(r"minnano-av\s+actress(\d+)")

#: 能证明「这个名字是那个人的另一个艺名」的别名来源。本地化、简繁转换和系列译名
#: 写的别名说的是同一个名字的另一种写法,不是另一个艺名,不能拿来判同人。
STAGE_NAME_ALIAS_SOURCES = ("r18:performer", "javbus:performer", "avdb-actor-mapping")


def _fold(value: str) -> str:
    """与 entity 规范化一致,并忽略账号名前缀和多余空白。"""
    return " ".join(part.lstrip("@").casefold() for part in str(value).split())


def _fuzzy_evidence(
    creator_name: str, performer_name: str, performer_aliases: list[str],
) -> str | None:
    """返回可自动合并的名字证据;仅由作品集合相同的调用方使用。"""
    creator = _fold(creator_name)
    performer = _fold(performer_name)
    aliases = {_fold(alias) for alias in performer_aliases if _fold(alias)}
    if creator in aliases:
        return "performer 别名等于 creator 名"
    if creator != performer and performer in creator:
        account_aliases = {
            alias for alias in aliases
            if alias != performer and len(re.sub(r"\s+", "", alias)) >= 3
        }
        if any(alias in creator for alias in account_aliases):
            return "creator 名由 performer 本名与账号别名组成"
    return None


def _asset_relation(
    creator_assets: frozenset[int], performer_assets: frozenset[int],
) -> str | None:
    """两边作品集合的包含关系;没有包含关系就不是同一批文件,返回 None。

    真子集也算包含关系。只认「完全相同」的话,`哆米`(7 部) 与 `哆米 Dolmi24`(6 部)
    这种一侧多出一两部的目录投影永远落在判据外——多出的那几部恰恰是只被 Stash 认成
    performer、没有对应本地目录的作品,属于常态而不是反证。放宽的证据强度由名字
    那一重(别名等于 creator 名,或 creator 名由本名与账号别名拼成)兜底,
    并把包含方向和数量写进复核 CSV,交给人看。
    """
    if not creator_assets or not performer_assets:
        return None
    if creator_assets == performer_assets:
        return f"作品集合完全相同(各 {len(creator_assets)} 部)"
    if creator_assets < performer_assets:
        return (f"creator 作品集合真包含于 performer"
                f"({len(creator_assets)}/{len(performer_assets)} 部)")
    if performer_assets < creator_assets:
        return (f"performer 作品集合真包含于 creator"
                f"({len(performer_assets)}/{len(creator_assets)} 部)")
    return None


def _release_backed(sources: str) -> bool:
    present = {source for source in str(sources or "").split(",") if source}
    if RELEASE_SOURCES.intersection(present):
        return True
    return any(source.startswith(RELEASE_SOURCE_PREFIX)
               and source.endswith(RELEASE_SOURCE_SUFFIX) for source in present)


def _name_tokens(value: str) -> frozenset[str]:
    """名字按空白切成词集;空词和纯连接符不算名字的一部分。"""
    return frozenset(
        token for token in (_fold(part) for part in str(value).split())
        if token and token not in JOINER_TOKENS
    )


def _projection_only(sources: str, aliases: int, refs: int) -> bool:
    """只有路径投影出来的断言,没有别名也没有外部引用。"""
    present = {source for source in str(sources or "").split(",") if source}
    return bool(present) and present <= PROJECTION_SOURCES and not aliases and not refs


def _variant_evidence(
    keep_name: str, drop_name: str, keep_aliases: list[str],
) -> str | None:
    """返回同 kind 目录名投影可自动合并的名字证据。

    仅由「作品集合被完全包含」的调用方使用:只有名字证据不足以判定同人,
    `猫猫碎冰冰 & 趣趣` 这种拼名目录必须靠「同一批文件」加「多出的词是已登记别名」
    两重证据才成立。
    """
    keep_tokens = _name_tokens(keep_name)
    drop_tokens = _name_tokens(drop_name)
    if not keep_tokens or not keep_tokens < drop_tokens:
        return None
    extra = drop_tokens - keep_tokens
    aliases = {_fold(alias) for alias in keep_aliases if _fold(alias)}
    if not extra or not extra <= aliases:
        return None
    return "同 kind 目录名投影:多出的词是保留方别名"


def collect(connection: sqlite3.Connection) -> list[dict[str, object]]:
    """找出高置信跨 kind 重复实体,并按 provenance 定出保留哪一边。"""
    connection.row_factory = sqlite3.Row
    entity_rows = connection.execute(
        """
        SELECT e.id, e.kind, e.canonical_name, e.normalized_name,
               (SELECT count(*) FROM asset_entity ae WHERE ae.entity_id=e.id) AS links,
               (SELECT group_concat(DISTINCT ae.source) FROM asset_entity ae
                 WHERE ae.entity_id=e.id) AS sources,
               (SELECT count(*) FROM entity_alias a WHERE a.entity_id=e.id) AS alias_count,
               (SELECT count(*) FROM entity_external_ref r WHERE r.entity_id=e.id) AS ref_count
        FROM entity e
        WHERE e.kind IN ('creator','performer')
        ORDER BY e.id
        """
    ).fetchall()

    aliases: dict[int, list[str]] = {}
    for row in connection.execute(
        "SELECT entity_id,alias FROM entity_alias ORDER BY entity_id,alias"
    ):
        aliases.setdefault(int(row["entity_id"]), []).append(str(row["alias"]))
    asset_members: dict[int, set[int]] = {}
    for row in connection.execute(
        "SELECT entity_id,asset_id FROM asset_entity ORDER BY entity_id,asset_id"
    ):
        asset_members.setdefault(int(row["entity_id"]), set()).add(int(row["asset_id"]))
    assets = {
        entity_id: frozenset(asset_ids)
        for entity_id, asset_ids in asset_members.items()
    }

    creators = [row for row in entity_rows if row["kind"] == "creator"]
    performers = [row for row in entity_rows if row["kind"] == "performer"]
    by_normalized_creator = {row["normalized_name"]: row for row in creators}
    by_normalized_performer = {row["normalized_name"]: row for row in performers}

    candidates: dict[tuple[int, int], tuple[sqlite3.Row, sqlite3.Row, str]] = {}
    for normalized in sorted(set(by_normalized_creator) & set(by_normalized_performer)):
        creator = by_normalized_creator[normalized]
        performer = by_normalized_performer[normalized]
        candidates[(int(creator["id"]), int(performer["id"]))] = (
            creator, performer, "规范名完全相同")

    performer_ids_by_name: dict[str, set[int]] = {}
    performer_by_id = {int(row["id"]): row for row in performers}
    for performer in performers:
        performer_id = int(performer["id"])
        values = [str(performer["canonical_name"]), *aliases.get(performer_id, [])]
        for value in values:
            performer_ids_by_name.setdefault(_fold(value), set()).add(performer_id)
    for creator in creators:
        repeated = collapse_repeated_entity_name(str(creator["canonical_name"]))
        if repeated == str(creator["canonical_name"]).strip():
            continue
        matched_ids = performer_ids_by_name.get(_fold(repeated), set())
        release_ids = {
            entity_id for entity_id in matched_ids
            if _release_backed(str(performer_by_id[entity_id]["sources"] or ""))
        }
        if len(release_ids) != 1:
            continue
        performer = performer_by_id[next(iter(release_ids))]
        candidates[(int(creator["id"]), int(performer["id"]))] = (
            creator, performer, "creator 规范名是完整重复串",
        )

    performers_by_asset: dict[int, set[int]] = {}
    for performer in performers:
        for asset_id in assets.get(int(performer["id"]), frozenset()):
            performers_by_asset.setdefault(asset_id, set()).add(int(performer["id"]))

    fuzzy: list[tuple[sqlite3.Row, sqlite3.Row, str]] = []
    for creator in creators:
        creator_assets = assets.get(int(creator["id"]), frozenset())
        if not creator_assets:
            continue
        nearby = {
            performer_id
            for asset_id in creator_assets
            for performer_id in performers_by_asset.get(asset_id, ())
        }
        for performer_id in sorted(nearby):
            performer = performer_by_id[performer_id]
            key = (int(creator["id"]), performer_id)
            if key in candidates:
                continue
            relation = _asset_relation(creator_assets, assets.get(performer_id, frozenset()))
            if not relation:
                continue
            evidence = _fuzzy_evidence(
                str(creator["canonical_name"]), str(performer["canonical_name"]),
                aliases.get(performer_id, []),
            )
            if evidence:
                fuzzy.append((creator, performer, f"{evidence};{relation}"))

    # 一方若同时命中多个候选,就留给人工复核,避免同作品集合内的连锁误并。
    fuzzy_counts = Counter(
        (side, entity_id)
        for creator, performer, _ in fuzzy
        for side, entity_id in (("creator", int(creator["id"])),
                                ("performer", int(performer["id"])))
    )
    for creator, performer, evidence in fuzzy:
        if (fuzzy_counts[("creator", int(creator["id"]))] == 1
                and fuzzy_counts[("performer", int(performer["id"]))] == 1):
            candidates[(int(creator["id"]), int(performer["id"]))] = (
                creator, performer, evidence)

    plan: list[dict[str, object]] = []
    for _, (creator, performer, match_evidence) in sorted(candidates.items()):
        release_backed = _release_backed(performer["sources"] or "")
        keep, drop = (performer, creator) if release_backed else (creator, performer)
        plan.append({
            "normalized_name": creator["normalized_name"],
            "match_evidence": match_evidence,
            "keep_kind": keep["kind"],
            "keep_id": keep["id"],
            "keep_name": keep["canonical_name"],
            "keep_links": keep["links"],
            "drop_kind": drop["kind"],
            "drop_id": drop["id"],
            "drop_name": drop["canonical_name"],
            "drop_links": drop["links"],
            "keep_sources": keep["sources"] or "",
            "drop_sources": drop["sources"] or "",
            "evidence": "发行元数据" if release_backed else "目录身份 + Stash 扁平 performer",
        })
    # 跨 kind 已经排进计划的实体不再参与同 kind 那一趟:先合并会把 id 删掉,
    # 第二趟再引用同一个 id 就是合并进一条不存在的实体。
    engaged = {int(row["keep_id"]) for row in plan} | {int(row["drop_id"]) for row in plan}
    plan.extend(_variant_plan(entity_rows, aliases, assets, engaged))
    engaged |= {int(row["keep_id"]) for row in plan} | {int(row["drop_id"]) for row in plan}
    stage_aliases = [
        (int(row["entity_id"]), str(row["alias"]), str(row["normalized_alias"]),
         str(row["source"] or ""))
        for row in connection.execute(
            "SELECT entity_id,alias,normalized_alias,source FROM entity_alias"
            " ORDER BY entity_id,alias")
    ]
    refs: dict[int, dict[str, str]] = {}
    for row in connection.execute(
        "SELECT entity_id,provider,external_id FROM entity_external_ref ORDER BY entity_id"
    ):
        refs.setdefault(int(row["entity_id"]), {})[str(row["provider"])] = str(
            row["external_id"])
    directory = _directory_identity_plan(entity_rows, connection, engaged)
    plan.extend(directory)
    engaged |= {int(row["keep_id"]) for row in directory}
    engaged |= {int(row["drop_id"]) for row in directory}
    plan.extend(_stage_name_plan(entity_rows, aliases, stage_aliases, refs, engaged))
    return plan


def _directory_identity_plan(
    entity_rows: list[sqlite3.Row],
    connection: sqlite3.Connection,
    engaged: set[int],
) -> list[dict[str, object]]:
    """两条实体的官网链接是按同一个 minnano-av 名录页装上的,那就是同一个人。

    实测 `白石亚子`(#8204) 与 `Ako Shiraishi`(#8435):前者按「白石あこ」检索、后者按
    「平沢すず」检索,两条都落到 actress37250。艺名换过一次,账本就各存了一条。

    这条证据压过 `_stage_name_plan` 的「别名互不相识」那道闸——那道闸防的是规范名写错人,
    而名录页号恰恰能分清「写错了人」和「同一个人的两个艺名」:写错人时两侧不会落到同一页。

    保留规范名已经本地化的那一侧(用户 2026-09-04 的口径:用更知名的名字,其余留作别名),
    两侧都本地化或都没有时保留作品多的一侧。同一页挂三条以上一律交人工:那要先决定
    并的顺序,不是脚本该替人做的。
    """
    by_id = {int(row["id"]): row for row in entity_rows}
    owners: dict[str, set[int]] = {}
    for entity_id, payload in connection.execute(
        "SELECT entity_id,metadata_json FROM entity_link ORDER BY entity_id"
    ):
        if int(entity_id) not in by_id:
            continue
        try:
            evidence = str(json.loads(payload or "{}").get("evidence") or "")
        except (TypeError, ValueError):
            continue
        found = MINNANO_ACTRESS.search(evidence)
        if found:
            owners.setdefault(found.group(1), set()).add(int(entity_id))

    plan: list[dict[str, object]] = []
    for actress, ids in sorted(owners.items()):
        usable = sorted(ids - engaged)
        if len(usable) != 2:
            continue
        first, second = (by_id[entity_id] for entity_id in usable)
        localized = [row for row in (first, second)
                     if not str(row["canonical_name"]).isascii()]
        if len(localized) == 1:
            keep = localized[0]
            why = "保留已本地化的规范名"
        else:
            keep = max((first, second), key=lambda row: (int(row["links"]), -int(row["id"])))
            why = "两侧规范名同为本地化或同为罗马字,保留作品多的一侧"
        drop = second if keep is first else first
        plan.append({
            "normalized_name": drop["normalized_name"],
            "match_evidence": f"两侧官网链接按同一个 minnano-av 名录页 actress{actress} 装上;{why}",
            "keep_kind": keep["kind"],
            "keep_id": keep["id"],
            "keep_name": keep["canonical_name"],
            "keep_links": keep["links"],
            "drop_kind": drop["kind"],
            "drop_id": drop["id"],
            "drop_name": drop["canonical_name"],
            "drop_links": drop["links"],
            "keep_sources": keep["sources"] or "",
            "drop_sources": drop["sources"] or "",
            "evidence": "名录页号",
        })
    return plan


def _stage_name_plan(
    entity_rows: list[sqlite3.Row],
    aliases: dict[int, list[str]],
    stage_aliases: list[tuple[int, str, str, str]],
    refs: dict[int, dict[str, str]],
    engaged: set[int],
) -> list[dict[str, object]]:
    """艺名已经登记成另一条实体的别名,那个名字却还自己占着一条实体。

    实测 `飯岡かなこ`(1 部)与 `森泽佳奈`(2 部):r18 早已把前者记成后者的别名,
    `/performers/飯岡かなこ` 却还在。`link_entity` 只在建实体那一刻查别名,两条都是
    Stash 那次批量导入建的,别名是之后才由 r18 补上的——建的时候没有可查的东西。

    三道闸都是为了排掉实测里的假阳性,缺一条就会把两个人并成一个:

    1. **别名要出自发行元数据。** 本地化与简繁转换也往别名表里写(`綾乃梓` 之于
       `绫乃梓`),它们说的是同一个名字的另一种写法,证不了「这是那个人的另一个艺名」。
       系列译名同样落在别名表里(`Night Safari` 写进 `クロロホルムレ●プ`),虽然这一趟
       只扫 person,判据本身不该依赖扫描范围。
    2. **被丢弃方自己的别名必须全在保留方的名字集里。** `Ako Shiraishi` 这条实体的
       每一个别名和 r18 引用都指向 平沢すず,规范名却写着另一个人——它是错标,
       并进 `白石亚子` 等于把两位女优搅在一起。
    3. **同一个 provider 的外部引用不能各指一个 ID。** `NOZOMI`(stash 610)撞上
       `绫乃梓`(stash 538)的别名 `Nozomi`,那是素人企划里常见的通名撞上罗马字读音。

    命中两条以上保留方同样交人工:这条判据唯一的身份保证就是那一条别名。
    """
    by_id = {int(row["id"]): row for row in entity_rows}
    owners: dict[tuple[str, str], set[int]] = {}
    alias_note: dict[tuple[str, int], tuple[str, str]] = {}
    for entity_id, alias, normalized, source in stage_aliases:
        row = by_id.get(entity_id)
        if row is None or not normalized:
            continue
        if not source.startswith(STAGE_NAME_ALIAS_SOURCES):
            continue
        owners.setdefault((str(row["kind"]), normalized), set()).add(entity_id)
        alias_note[(normalized, entity_id)] = (alias, source)

    plan: list[dict[str, object]] = []
    for drop_id, drop in sorted(by_id.items()):
        if drop_id in engaged:
            continue
        normalized = str(drop["normalized_name"])
        found = sorted(owners.get((str(drop["kind"]), normalized), set()) - {drop_id}
                       - engaged)
        if len(found) != 1:
            continue
        keep_id = found[0]
        keep = by_id[keep_id]
        keep_names = {_fold(str(keep["canonical_name"]))}
        keep_names |= {_fold(value) for value in aliases.get(keep_id, [])}
        stray = [value for value in aliases.get(drop_id, [])
                 if _fold(value) and _fold(value) not in keep_names]
        if stray:
            continue
        drop_refs, keep_refs = refs.get(drop_id, {}), refs.get(keep_id, {})
        if any(keep_refs[provider] != value for provider, value in drop_refs.items()
               if provider in keep_refs):
            continue
        alias, source = alias_note[(normalized, keep_id)]
        plan.append({
            "normalized_name": normalized,
            "match_evidence": f"艺名已登记为保留方别名「{alias}」({source})",
            "keep_kind": keep["kind"],
            "keep_id": keep["id"],
            "keep_name": keep["canonical_name"],
            "keep_links": keep["links"],
            "drop_kind": drop["kind"],
            "drop_id": drop["id"],
            "drop_name": drop["canonical_name"],
            "drop_links": drop["links"],
            "keep_sources": keep["sources"] or "",
            "drop_sources": drop["sources"] or "",
            "evidence": "发行元数据登记的艺名",
        })
    return plan


def _variant_plan(
    entity_rows: list[sqlite3.Row],
    aliases: dict[int, list[str]],
    assets: dict[int, frozenset[int]],
    engaged: set[int],
) -> list[dict[str, object]]:
    """同 kind 的目录名投影:`小桃 shixiaotaone` 并回 `小桃`。

    先按别名反查候选保留方——多出来的词必须是别名,所以至少有一个词能命中——再逐项
    核对作品集合、词集和 provenance。命中多个保留方的投影一律不合并。
    """
    by_id = {int(row["id"]): row for row in entity_rows}
    alias_owners: dict[tuple[str, str], set[int]] = {}
    for entity_id, values in aliases.items():
        row = by_id.get(entity_id)
        if row is None:
            continue
        for alias in values:
            folded = _fold(alias)
            if folded:
                alias_owners.setdefault((str(row["kind"]), folded), set()).add(entity_id)

    matches: dict[int, list[tuple[sqlite3.Row, str]]] = {}
    for drop_id, drop in by_id.items():
        drop_assets = assets.get(drop_id, frozenset())
        if drop_id in engaged or not drop_assets or not _projection_only(
            drop["sources"], int(drop["alias_count"]), int(drop["ref_count"])
        ):
            continue
        keep_ids = {
            keep_id
            for token in _name_tokens(str(drop["canonical_name"]))
            for keep_id in alias_owners.get((str(drop["kind"]), token), set())
            if keep_id != drop_id and keep_id not in engaged
        }
        for keep_id in sorted(keep_ids):
            keep = by_id[keep_id]
            if not drop_assets <= assets.get(keep_id, frozenset()):
                continue
            evidence = _variant_evidence(
                str(keep["canonical_name"]), str(drop["canonical_name"]),
                aliases.get(keep_id, []),
            )
            if evidence:
                matches.setdefault(drop_id, []).append((keep, evidence))

    plan: list[dict[str, object]] = []
    for drop_id, found in sorted(matches.items()):
        if len(found) != 1:
            continue
        keep, evidence = found[0]
        drop = by_id[drop_id]
        plan.append({
            "normalized_name": drop["normalized_name"],
            "match_evidence": evidence,
            "keep_kind": keep["kind"],
            "keep_id": keep["id"],
            "keep_name": keep["canonical_name"],
            "keep_links": keep["links"],
            "drop_kind": drop["kind"],
            "drop_id": drop["id"],
            "drop_name": drop["canonical_name"],
            "drop_links": drop["links"],
            "keep_sources": keep["sources"] or "",
            "drop_sources": drop["sources"] or "",
            "evidence": "规范实体 + 目录名投影",
        })
    return plan


def apply_rows(
    connection: sqlite3.Connection, rows: list[dict[str, object]],
) -> dict[str, int]:
    """逐对合并,并同步兼容投影 `asset.creator` / `演员:` 标签。

    扁平字段必须跟着规范关系走(ADR-0005),否则资料页和卡片会各说各话:

    - 保留 creator:被并进来的资产可能没有 creator,补上;已有别的值不覆盖。
    - 保留 creator 且丢弃方也是 creator:扁平字段里写的就是被丢弃的目录名,改写成保留名。
      只填空不改写会留下一个没有实体的名字,详情页的归属兜底和按 creator 的查询都还认它。
    - 保留 performer:creator 实体没了,等值的扁平字段一并清掉。
    """
    counts = Counter()
    for row in rows:
        keep_id, drop_id = int(row["keep_id"]), int(row["drop_id"])
        moving = [
            int(item[0]) for item in connection.execute(
                "SELECT asset_id FROM asset_entity WHERE entity_id=?", (drop_id,))
        ]
        moved = merge_entity(
            connection, target_id=keep_id, source_id=drop_id,
            source_name=str(row["drop_name"]), alias_source=MERGE_ALIAS_SOURCE,
        )
        counts["merged"] += 1
        counts["assets"] += moved["assets"]
        counts["aliases"] += moved["aliases"]
        counts["dropped_refs"] += moved["dropped_refs"]

        if row["keep_kind"] == "creator":
            if row["drop_kind"] == "creator":
                connection.execute(
                    "UPDATE asset SET creator=? WHERE creator=?",
                    (row["keep_name"], row["drop_name"]))
                counts["flat_rewritten"] += connection.execute(
                    "SELECT changes()").fetchone()[0]
            for asset_id in moving:
                connection.execute(
                    "UPDATE asset SET creator=? WHERE id=? AND (creator IS NULL OR creator='')",
                    (row["keep_name"], asset_id))
                counts["flat_filled"] += connection.execute("SELECT changes()").fetchone()[0]
                connection.execute(
                    "DELETE FROM asset_tag WHERE asset_id=? AND tag=?",
                    (asset_id, f"演员:{row['drop_name']}"))
                counts["actor_tags_removed"] += connection.execute(
                    "SELECT changes()").fetchone()[0]
        elif row["keep_kind"] == "series":
            # 扁平 `asset.series` 也得跟着规范关系走(ADR-0005)。只搬关系不改这一列,
            # 卡片上还写着被丢弃的系列名,按它查也还查得到——资料页和卡片各说各话。
            connection.execute("UPDATE asset SET series=? WHERE series=?",
                               (row["keep_name"], row["drop_name"]))
            counts["flat_rewritten"] += connection.execute("SELECT changes()").fetchone()[0]
        else:
            connection.execute(
                "UPDATE asset SET creator=NULL WHERE creator=?", (row["drop_name"],))
            counts["flat_cleared"] += connection.execute("SELECT changes()").fetchone()[0]
            old_tag, new_tag = f"演员:{row['drop_name']}", f"演员:{row['keep_name']}"
            connection.execute(
                "INSERT OR IGNORE INTO asset_tag(asset_id,tag,confidence,source) "
                "SELECT asset_id,?,confidence,source FROM asset_tag WHERE tag=?",
                (new_tag, old_tag),
            )
            counts["actor_tags_inserted"] += connection.execute(
                "SELECT changes()"
            ).fetchone()[0]
            connection.execute("DELETE FROM asset_tag WHERE tag=?", (old_tag,))
            counts["actor_tags_rewritten"] += connection.execute(
                "SELECT changes()"
            ).fetchone()[0]
            if collapse_repeated_entity_name(str(row["drop_name"])) != str(row["drop_name"]):
                connection.execute(
                    "DELETE FROM entity_alias WHERE entity_id=? AND normalized_alias=?",
                    (keep_id, normalize_entity_name(str(row["drop_name"]))),
                )
                counts["bad_aliases_removed"] += connection.execute(
                    "SELECT changes()"
                ).fetchone()[0]
    return dict(counts)


def collect_repeated_projections(
    connection: sqlite3.Connection,
) -> list[dict[str, object]]:
    """审计 person 名称的完整重复串及其所有兼容投影。"""
    connection.row_factory = sqlite3.Row
    names: set[str] = set()
    names.update(str(row[0]) for row in connection.execute(
        "SELECT DISTINCT creator FROM asset WHERE creator IS NOT NULL AND creator<>''"
    ))
    names.update(str(row[0])[3:] for row in connection.execute(
        "SELECT DISTINCT tag FROM asset_tag WHERE tag LIKE '演员:%'"
    ))
    names.update(str(row[0]) for row in connection.execute(
        "SELECT DISTINCT alias FROM entity_alias a JOIN entity e ON e.id=a.entity_id "
        "WHERE e.kind IN ('creator','performer')"
    ))
    names.update(str(row[0]) for row in connection.execute(
        "SELECT canonical_name FROM entity WHERE kind IN ('creator','performer')"
    ))

    plan: list[dict[str, object]] = []
    for bad_name in sorted(names, key=str.casefold):
        collapsed = collapse_repeated_entity_name(bad_name)
        cleaned = canonicalize_entity_name("performer", bad_name)
        if collapsed == bad_name.strip() and cleaned:
            continue
        normalized = {normalize_entity_name(bad_name)}
        if cleaned:
            normalized.add(normalize_entity_name(cleaned))
        marks = ",".join("?" * len(normalized))
        targets = connection.execute(
            "SELECT e.id,e.canonical_name,"
            "(SELECT group_concat(DISTINCT ae.source) FROM asset_entity ae "
            " WHERE ae.entity_id=e.id) sources "
            "FROM entity e WHERE e.kind='performer' AND (e.normalized_name IN (" + marks + ") "
            "OR e.id IN (SELECT a.entity_id FROM entity_alias a "
            "WHERE a.normalized_alias IN (" + marks + "))) GROUP BY e.id ORDER BY e.id",
            tuple(normalized) + tuple(normalized),
        ).fetchall()
        release_targets = [row for row in targets if _release_backed(str(row["sources"] or ""))]
        asset_ids = {
            int(row[0]) for row in connection.execute(
                "SELECT id FROM asset WHERE creator=? UNION "
                "SELECT asset_id FROM asset_tag WHERE tag=?",
                (bad_name, f"演员:{bad_name}"),
            )
        }
        linked_target_ids = {
            int(row["id"]) for row in release_targets
            if asset_ids and connection.execute(
                "SELECT 1 FROM asset_entity WHERE entity_id=? AND asset_id IN ("
                + ",".join("?" * len(asset_ids)) + ") LIMIT 1",
                (int(row["id"]), *sorted(asset_ids)),
            ).fetchone()
        }
        alias_owner_ids = {
            int(row[0]) for row in connection.execute(
                "SELECT DISTINCT e.id FROM entity_alias a JOIN entity e ON e.id=a.entity_id "
                "WHERE e.kind='performer' AND a.alias=?",
                (bad_name,),
            )
            if any(int(target["id"]) == int(row[0]) for target in release_targets)
        }
        chosen = None
        if len(alias_owner_ids) == 1:
            chosen_id = next(iter(alias_owner_ids))
            chosen = next(row for row in release_targets if int(row["id"]) == chosen_id)
        elif len(linked_target_ids) == 1:
            chosen_id = next(iter(linked_target_ids))
            chosen = next(row for row in release_targets if int(row["id"]) == chosen_id)
        elif len(release_targets) == 1:
            chosen = release_targets[0]
        action = "remove-invalid" if not cleaned else "review"
        if chosen is not None:
            action = "use-performer"
        plan.append({
            "bad_name": bad_name,
            "collapsed_name": cleaned,
            "action": action,
            "target_id": int(chosen["id"]) if chosen else "",
            "target_name": str(chosen["canonical_name"]) if chosen else "",
            "flat_assets": connection.execute(
                "SELECT count(*) FROM asset WHERE creator=?", (bad_name,)
            ).fetchone()[0],
            "actor_tags": connection.execute(
                "SELECT count(*) FROM asset_tag WHERE tag=?", (f"演员:{bad_name}",)
            ).fetchone()[0],
            "aliases": connection.execute(
                "SELECT count(*) FROM entity_alias WHERE alias=?", (bad_name,)
            ).fetchone()[0],
            "canonical_entities": connection.execute(
                "SELECT count(*) FROM entity WHERE canonical_name=? AND kind IN ('creator','performer')",
                (bad_name,),
            ).fetchone()[0],
            "evidence": (
                "重复别名所属 performer" if alias_owner_ids
                else "已关联的发行 performer" if linked_target_ids
                else "唯一发行 performer" if chosen
                else "已知页面控件文字" if not cleaned
                else "目标不唯一或未取得"
            ),
        })
    return plan


def apply_repeated_projections(
    connection: sqlite3.Connection, rows: list[dict[str, object]],
) -> dict[str, int]:
    counts = Counter()
    for row in rows:
        if row["action"] == "review":
            counts["review_left"] += 1
            continue
        bad_name = str(row["bad_name"])
        connection.execute("UPDATE asset SET creator=NULL WHERE creator=?", (bad_name,))
        counts["flat_cleared"] += connection.execute("SELECT changes()").fetchone()[0]
        old_tag = f"演员:{bad_name}"
        if row["action"] == "use-performer":
            new_tag = f"演员:{row['target_name']}"
            connection.execute(
                "INSERT OR IGNORE INTO asset_tag(asset_id,tag,confidence,source) "
                "SELECT asset_id,?,confidence,source FROM asset_tag WHERE tag=?",
                (new_tag, old_tag),
            )
            counts["actor_tags_inserted"] += connection.execute(
                "SELECT changes()"
            ).fetchone()[0]
        connection.execute("DELETE FROM asset_tag WHERE tag=?", (old_tag,))
        counts["actor_tags_removed"] += connection.execute("SELECT changes()").fetchone()[0]

        alias_rows = connection.execute(
            "SELECT a.entity_id,a.source,a.confidence,e.normalized_name "
            "FROM entity_alias a JOIN entity e ON e.id=a.entity_id WHERE a.alias=?",
            (bad_name,),
        ).fetchall()
        for alias in alias_rows:
            cleaned = str(row["collapsed_name"] or "")
            if cleaned and normalize_entity_name(cleaned) != str(alias["normalized_name"]):
                connection.execute(
                    "INSERT OR IGNORE INTO entity_alias("
                    "entity_id,alias,normalized_alias,source,confidence) VALUES(?,?,?,?,?)",
                    (int(alias["entity_id"]), cleaned, normalize_entity_name(cleaned),
                     str(alias["source"]), float(alias["confidence"])),
                )
                counts["aliases_inserted"] += connection.execute(
                    "SELECT changes()"
                ).fetchone()[0]
            connection.execute(
                "DELETE FROM entity_alias WHERE entity_id=? AND normalized_alias=? AND source=?",
                (int(alias["entity_id"]), normalize_entity_name(bad_name), str(alias["source"])),
            )
            counts["aliases_removed"] += connection.execute("SELECT changes()").fetchone()[0]
    return dict(counts)


def write_projection_csv(path: Path, rows: list[dict[str, object]]) -> None:
    fields = ["bad_name", "collapsed_name", "action", "target_id", "target_name",
              "flat_assets", "actor_tags", "aliases", "canonical_entities", "evidence"]
    write_rows(path, fields, rows)


def named_pairs(connection: sqlite3.Connection, pairs: list[str],
                engaged: set[int] | None = None) -> list[dict[str, object]]:
    """命令行点名的一对。用于证据在站外、本地探测不出来的同一人。

    `五十嵐星蘭` 与 `蘭々` 在账本里没有一处相交:外部 id 不同、别名互不相识、
    作品也不重合。判定它们是同一人靠的是 javdb 资料页把五个艺名列在一起,那份证据
    进不了自动判据,只能由人带进来——所以每一对都必须自带 `证据` 一段话,它会原样
    落进复核 CSV,是这次合并唯一说得清来由的东西。

    写法 `保留id:丢弃id:证据`。方向不自动挑:作品数多的一侧不一定是名字对的一侧。

    保留侧允许在一批里重复出现,丢弃侧不允许:一个人有几个旧艺名就有几条要并进来,
    `香椎美铃` 同时收走 `みすず` 和 `加山優衣` 是常态。被丢弃的那条会被删掉,所以它
    一旦出现过,后面无论摆在哪一侧都是引用一条已经不存在的实体——包括「先丢后留」这种
    顺序写反的情形,一批十几对时肉眼看不出来。
    """
    connection.row_factory = sqlite3.Row
    engaged = engaged or set()
    kept: set[int] = set()
    dropped: set[int] = set()
    plan: list[dict[str, object]] = []
    for pair in pairs:
        parts = pair.split(":", 2)
        if len(parts) != 3 or not parts[2].strip():
            raise SystemExit(f"--pair 要写成 保留id:丢弃id:证据,收到 {pair!r}")
        keep_id, drop_id, why = int(parts[0]), int(parts[1]), parts[2].strip()
        if keep_id == drop_id:
            raise SystemExit(f"--pair {pair}:两侧是同一条实体 {keep_id},不合并")
        if keep_id in dropped:
            raise SystemExit(
                f"--pair {pair}:保留侧 {keep_id} 在前面的 --pair 里已经被丢弃")
        if drop_id in dropped:
            raise SystemExit(f"--pair {pair}:丢弃侧 {drop_id} 在前面的 --pair 里已经被丢弃")
        if drop_id in kept:
            raise SystemExit(
                f"--pair {pair}:丢弃侧 {drop_id} 在前面的 --pair 里是保留侧")
        sides = {}
        for name, entity_id in (("keep", keep_id), ("drop", drop_id)):
            row = connection.execute(
                "SELECT id,kind,canonical_name,normalized_name,"
                " (SELECT count(*) FROM asset_entity ae WHERE ae.entity_id=e.id) AS links,"
                " (SELECT group_concat(DISTINCT ae.source) FROM asset_entity ae"
                "   WHERE ae.entity_id=e.id) AS sources"
                " FROM entity e WHERE e.id=?", (entity_id,)).fetchone()
            if row is None:
                raise SystemExit(f"--pair {pair}:实体 {entity_id} 不存在")
            if entity_id in engaged:
                raise SystemExit(f"--pair {pair}:实体 {entity_id} 已经在自动判据的计划里")
            sides[name] = row
        if sides["keep"]["kind"] != sides["drop"]["kind"]:
            raise SystemExit(f"--pair {pair}:两侧 kind 不同,不合并")
        kept.add(keep_id)
        dropped.add(drop_id)
        plan.append({
            "normalized_name": sides["drop"]["normalized_name"],
            "match_evidence": why,
            "keep_kind": sides["keep"]["kind"],
            "keep_id": keep_id,
            "keep_name": sides["keep"]["canonical_name"],
            "keep_links": sides["keep"]["links"],
            "drop_kind": sides["drop"]["kind"],
            "drop_id": drop_id,
            "drop_name": sides["drop"]["canonical_name"],
            "drop_links": sides["drop"]["links"],
            "keep_sources": sides["keep"]["sources"] or "",
            "drop_sources": sides["drop"]["sources"] or "",
            "evidence": "人工指定",
        })
    return plan


def write_csv(path: Path, rows: list[dict[str, object]]) -> None:
    fields = ["normalized_name", "match_evidence", "keep_kind", "keep_name", "keep_id", "keep_links",
              "drop_kind", "drop_name", "drop_id", "drop_links",
              "keep_sources", "drop_sources", "evidence"]
    write_rows(path, fields, rows)


#: 本脚本自己关心的口径;基础计数由 `scripting.counts_of` 给。
EXTRA_COUNTS = {
    "creator_entities": "SELECT count(*) FROM entity WHERE kind='creator'",
    "performer_entities": "SELECT count(*) FROM entity WHERE kind='performer'",
    "asset_tag": "SELECT count(*) FROM asset_tag",
}


def build_parser() -> argparse.ArgumentParser:
    parser = argparse.ArgumentParser(description="合并 creator / performer 的重复身份(跨 kind 同名与同 kind 目录名投影)")
    add_ledger_write_args(parser)
    parser.add_argument("--review-csv", type=Path,
                        default=GENERATED_DIR / "duplicate-identity-merge.csv")
    parser.add_argument("--projection-review-csv", type=Path,
                        default=GENERATED_DIR / "repeated-identity-name-repair.csv")
    parser.add_argument("--pair", action="append", default=[], metavar="保留id:丢弃id:证据",
                        help="人工指定的一对,用于证据在站外、本地探测不出来的同一人")
    parser.add_argument("--pairs-only", action="store_true",
                        help="只处理 --pair 点名的对子,跳过全部自动判据")
    return parser


def run(args: argparse.Namespace) -> int:
    # 自动判据和人工点名各自成立,但一次只想执行一边:自动那几组的保留方常常另有争议,
    # 人工那批却已经定下来了,捆在一起就只能连着争议一起写,或者一起不写。
    if args.pairs_only and not args.pair:
        raise SystemExit("--pairs-only 必须至少给一个 --pair")
    connection = open_for_write(args)
    try:
        rows = [] if args.pairs_only else collect(connection)
        engaged = {int(row["keep_id"]) for row in rows} | {int(row["drop_id"]) for row in rows}
        rows.extend(named_pairs(connection, args.pair, engaged))
        projection_rows = [] if args.pairs_only else collect_repeated_projections(connection)
        write_csv(args.review_csv, rows)
        write_projection_csv(args.projection_review_csv, projection_rows)
        print(f"高置信重复身份 {len(rows)} 组,复核 CSV:{args.review_csv}")
        print("  保留方分布:", dict(Counter(str(row["keep_kind"]) for row in rows)))
        print("  匹配判据:", dict(Counter(str(row["match_evidence"]) for row in rows)))
        print(f"重复 person 名称/投影 {len(projection_rows)} 组,复核 CSV:"
              f"{args.projection_review_csv}")
        print("  处理动作:", dict(Counter(str(row["action"]) for row in projection_rows)))
        if not args.apply:
            print("  未写 ledger(加 --apply --backup 才写)")
            return 0

        print(f"  已备份到 {args.backup}")
        before = counts_of(connection, EXTRA_COUNTS)
        with connection:
            moved = apply_rows(connection, rows)
            projections = apply_repeated_projections(connection, projection_rows)
        after = counts_of(connection, EXTRA_COUNTS)
        _integrity, violations = verify_after_write(connection)

        print("  合并结果:", moved)
        print("  重复名称投影:", projections)
        for key in before:
            mark = "" if before[key] == after[key] else "  <-- 变化"
            print(f"    {key}: {before[key]} -> {after[key]}{mark}")
        print(f"  foreign_key_check 违规 {violations} 条")
        return 1 if violations else 0
    finally:
        connection.close()


def main() -> int:
    return run(build_parser().parse_args())


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