#!/usr/bin/env python3
"""补处理 merge+skipped 中 merge_authors=1 的 60 条记录。

暂存表 matched_song_id 存的是 source_song_id，通过 source_song_id 找到
目标库实际记录，再做作者字段增量合并。
"""

from __future__ import annotations

import os
import sys
from pathlib import Path

import pymysql
from dotenv import load_dotenv

sys.path.insert(0, str(Path(__file__).resolve().parent))
from import_hk_songs import _merge_author_field

load_dotenv()

TARGET_DB_CONFIG = {
    "host": os.getenv("TARGET_DB_HOST"),
    "port": int(os.getenv("TARGET_DB_PORT", 3306)),
    "user": os.getenv("TARGET_DB_USER"),
    "password": os.getenv("TARGET_DB_PASSWORD"),
    "database": os.getenv("TARGET_DB_NAME"),
    "charset": "utf8mb4",
    "cursorclass": pymysql.cursors.DictCursor,
}
TABLE = os.getenv("TARGET_TABLE_NAME", "hk_songs")
STAGING = "hk_songs_import_staging"


def main(dry_run: bool = False) -> None:
    conn = pymysql.connect(**TARGET_DB_CONFIG)
    try:
        # 1. 拉取所有需要补作者合并的暂存记录
        with conn.cursor() as cur:
            cur.execute(f"""
                SELECT staging_id, matched_song_id, lyricist, composer
                FROM `{STAGING}`
                WHERE dedup_action = 'merge'
                  AND staging_status = 'skipped'
                  AND merge_authors = 1
                ORDER BY staging_id
            """)
            staging_rows = cur.fetchall()

        print(f"待处理记录数: {len(staging_rows)}")

        # 2. 批量查目标库（按 source_song_id）
        source_ids = [r["matched_song_id"] for r in staging_rows if r["matched_song_id"]]
        ph = ",".join(["%s"] * len(source_ids))
        with conn.cursor() as cur:
            cur.execute(
                f"SELECT id, name, lyricist, composer, source_song_id FROM `{TABLE}` WHERE source_song_id IN ({ph})",
                source_ids,
            )
            target_rows = {str(r["source_song_id"]): r for r in cur.fetchall()}

        print(f"目标库命中: {len(target_rows)} / {len(source_ids)}")

        # 3. 计算合并结果
        updates: list[dict] = []
        skipped: list[dict] = []

        for s in staging_rows:
            sid = s["matched_song_id"]
            target = target_rows.get(str(sid))
            if not target:
                skipped.append({"staging_id": s["staging_id"], "source_song_id": sid, "reason": "目标库未找到"})
                continue

            merged_lyricist = _merge_author_field(target["lyricist"], s["lyricist"])
            merged_composer = _merge_author_field(target["composer"], s["composer"])

            lyricist_changed = bool(merged_lyricist) and merged_lyricist != (target["lyricist"] or "")
            composer_changed = bool(merged_composer) and merged_composer != (target["composer"] or "")

            if not lyricist_changed and not composer_changed:
                skipped.append({"staging_id": s["staging_id"], "source_song_id": sid, "reason": "合并后无变化"})
                continue

            updates.append({
                "target_id": target["id"],
                "name": target["name"],
                "source_song_id": sid,
                "staging_id": s["staging_id"],
                "orig_lyricist": target["lyricist"],
                "orig_composer": target["composer"],
                "new_lyricist": merged_lyricist if lyricist_changed else None,
                "new_composer": merged_composer if composer_changed else None,
                "staging_lyricist": s["lyricist"],
                "staging_composer": s["composer"],
            })

        print(f"\n需要 UPDATE: {len(updates)} 条")
        print(f"无需操作:    {len(skipped)} 条")

        if dry_run:
            print("\n[dry-run] 预览变更（前 20 条）:")
            for u in updates[:20]:
                print(f"  id={u['target_id']} name={u['name']}")
                if u["new_lyricist"] is not None:
                    print(f"    lyricist: {u['orig_lyricist']!r} -> {u['new_lyricist']!r}")
                if u["new_composer"] is not None:
                    print(f"    composer: {u['orig_composer']!r} -> {u['new_composer']!r}")
            return

        # 4. 执行 UPDATE
        updated = 0
        errors = 0
        with conn.cursor() as cur:
            for u in updates:
                try:
                    if u["new_lyricist"] is not None:
                        cur.execute(
                            f"UPDATE `{TABLE}` SET lyricist = %s WHERE id = %s",
                            (u["new_lyricist"], u["target_id"]),
                        )
                    if u["new_composer"] is not None:
                        cur.execute(
                            f"UPDATE `{TABLE}` SET composer = %s WHERE id = %s",
                            (u["new_composer"], u["target_id"]),
                        )
                    updated += 1
                except Exception as e:
                    errors += 1
                    print(f"  [错误] target_id={u['target_id']}: {e}")

        conn.commit()
        print(f"\n完成: 已更新 {updated} 条，错误 {errors} 条")

        if skipped:
            print(f"\n无需操作的 {len(skipped)} 条:")
            for s in skipped:
                print(f"  staging_id={s['staging_id']} source_song_id={s['source_song_id']} 原因={s['reason']}")

    finally:
        conn.close()


if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser(description="补处理 merge+skipped 作者合并")
    parser.add_argument("--dry-run", action="store_true", help="只预览，不写库")
    args = parser.parse_args()
    main(dry_run=args.dry_run)