#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""修复 gov_raw 的 FTS 触发器链（核心库 schema 变更）。

问题：trg_gov_raw_ins(AFTER INSERT) 会 UPDATE gov_raw → 触发 trg_gov_raw_fts_upd
      写一次 gov_search(rowid)；随后 trg_gov_raw_fts_ins(AFTER INSERT) 又写同一 rowid
      → IntegrityError: constraint failed → **整个 INSERT 失败**。
      脚本把该异常当作「重复」吞掉 → 报成功、报 new=0，实际一条没入库。

修法（方案 B，已在副本库验证）：
  · fts_ins / fts_upd 改用 INSERT OR REPLACE（幂等）
  · fts_upd 加 WHEN 守卫：title/site_name/summary 没变就不动 FTS
  · 删掉我 2026-09-21 加的冗余 trg_gov_raw_ins_ts（trg_gov_raw_ins 已做同样的事）

用法: python3 fix_fts_trigger_chain.py [--dry-run]
"""
import io
import os
import sqlite3
import sys

DB = "/root/search.db"
BAK = "/root/gov_crawler/Archive/fts_triggers_20260922.sql"
DRY = "--dry-run" in sys.argv

c = sqlite3.connect(DB, timeout=120)
c.execute("PRAGMA busy_timeout=120000")
cur = c.cursor()

# ① 备份现有 DDL
old = list(cur.execute("""SELECT name, sql FROM sqlite_master
                          WHERE type='trigger' AND tbl_name='gov_raw'"""))
os.makedirs(os.path.dirname(BAK), exist_ok=True)
with io.open(BAK, "w", encoding="utf-8") as f:
    for n, s in old:
        f.write("-- %s\n%s;\n\n" % (n, s))
print("① 已备份 %d 个触发器的 DDL → %s" % (len(old), BAK))

NEW = [
    ("""CREATE TRIGGER trg_gov_raw_fts_ins AFTER INSERT ON gov_raw BEGIN
  INSERT OR REPLACE INTO gov_search(rowid, title, site_name, summary)
  VALUES (new.id, new.title, new.site_name, new.summary);
END""", "trg_gov_raw_fts_ins"),
    ("""CREATE TRIGGER trg_gov_raw_fts_upd AFTER UPDATE ON gov_raw
WHEN new.title IS NOT old.title OR new.site_name IS NOT old.site_name OR new.summary IS NOT old.summary
BEGIN
  INSERT OR REPLACE INTO gov_search(rowid, title, site_name, summary)
  VALUES (new.id, new.title, new.site_name, new.summary);
END""", "trg_gov_raw_fts_upd"),
]

if DRY:
    print("② [DRY-RUN] 将重建：%s" % ", ".join(n for _, n in NEW))
    print("   [DRY-RUN] 将删除：trg_gov_raw_ins_ts")
else:
    for sql, n in NEW:
        cur.execute("DROP TRIGGER IF EXISTS %s" % n)
        cur.execute(sql)
        print("② 已重建 %s" % n)
    cur.execute("DROP TRIGGER IF EXISTS trg_gov_raw_ins_ts")
    print("③ 已删除冗余触发器 trg_gov_raw_ins_ts")

c.commit()

# ③ 自测：普通 INSERT 必须成功
V = {"title": "__SELFTEST__", "summary": "s", "content": "c" * 300,
     "page_url": "https://example.invalid/__selftest_final__",
     "source_url": "https://example.invalid/__selftest_final__",
     "publish_date": "2026-09-22", "site_name": "selftest",
     "script_name": "selftest.py", "group_name": "selftest"}
cur.execute("SAVEPOINT st")
try:
    cur.execute("INSERT INTO gov_raw (%s) VALUES (%s)" % (",".join(V), ",".join("?" * len(V))),
                tuple(V.values()))
    rid = cur.execute("SELECT last_insert_rowid()").fetchone()[0]
    nf = cur.execute("SELECT COUNT(*) FROM gov_search WHERE rowid=?", (rid,)).fetchone()[0]
    ins = cur.execute("SELECT inserted_at FROM gov_raw WHERE id=?", (rid,)).fetchone()[0]
    print("④ 自测：普通 INSERT ✅ 成功  id=%s  FTS 行数=%d  inserted_at=%s" % (rid, nf, ins))
    ok = True
except Exception as e:
    print("④ 自测：❌ 失败 %s: %s" % (type(e).__name__, e))
    ok = False
finally:
    cur.execute("ROLLBACK TO st")
    cur.execute("RELEASE st")
    c.commit()

print()
print("=== 当前 gov_raw 上的触发器 ===")
for n, s in cur.execute("SELECT name, sql FROM sqlite_master WHERE type='trigger' AND tbl_name='gov_raw'"):
    print("  · %s" % n)
c.close()
print()
print("结果：%s" % ("✅ 修复完成，普通 INSERT 已恢复" if ok else "❌ 未恢复，请检查"))