#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""在副本库上验证三种修法，找出能恢复普通 INSERT 的方案。"""
import os
import sqlite3

SRC = "/root/search.db"
TDB = "/tmp/_fix_test.db"
if os.path.exists(TDB):
    os.remove(TDB)

s = sqlite3.connect(SRC, timeout=60)
ddl = []
for typ, name, sql in s.execute("""SELECT type, name, sql FROM sqlite_master
                                   WHERE sql IS NOT NULL AND (
                                     name IN ('gov_raw','gov_search')
                                     OR (type='trigger' AND tbl_name='gov_raw')
                                     OR (type='index' AND tbl_name='gov_raw'))"""):
    ddl.append((typ, name, sql))
s.close()

t = sqlite3.connect(TDB)
for typ, name, sql in ddl:
    t.execute(sql)
t.commit()

V = {"title": "__FIX__", "summary": "s", "content": "c" * 300,
     "page_url": "https://example.invalid/__fix__",
     "source_url": "https://example.invalid/__fix__",
     "publish_date": "2026-09-22", "site_name": "fix", "script_name": "fix.py", "group_name": "fix"}
Q = "INSERT INTO gov_raw (%s) VALUES (%s)" % (",".join(V), ",".join("?" * len(V)))


def test(label):
    # 每次用不同 URL 避免上一次的残留
    import random
    V["page_url"] = V["source_url"] = "https://example.invalid/__fix__%d" % random.randint(1, 10**9)
    t.execute("SAVEPOINT sp")
    try:
        t.execute(Q, tuple(V.values()))
        rid = t.execute("SELECT last_insert_rowid()").fetchone()[0]
        n = t.execute("SELECT COUNT(*) FROM gov_search WHERE rowid=?", (rid,)).fetchone()[0]
        t.execute("ROLLBACK TO sp"); t.execute("RELEASE sp")
        print("  %-42s ✅ 成功（FTS 里有 %d 行）" % (label, n))
        return True
    except Exception as e:
        t.execute("ROLLBACK TO sp"); t.execute("RELEASE sp")
        print("  %-42s ❌ %s: %s" % (label, type(e).__name__, e))
        return False


print("=== 基线 ===")
test("现状（不改）")

print()
print("=== 方案 A：fts_ins / fts_upd 改成 INSERT OR REPLACE ===")
t.execute("DROP TRIGGER trg_gov_raw_fts_ins")
t.execute("DROP TRIGGER trg_gov_raw_fts_upd")
t.execute("""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""")
t.execute("""CREATE TRIGGER trg_gov_raw_fts_upd AFTER UPDATE 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""")
t.commit()
test("方案 A")

print()
print("=== 方案 A + 删掉我加的冗余 trg_gov_raw_ins_ts ===")
t.execute("DROP TRIGGER IF EXISTS trg_gov_raw_ins_ts")
t.commit()
test("方案 A（去冗余触发器）")

print()
print("=== 方案 B：保留 OR REPLACE，另给 fts_upd 加 WHEN 守卫（文本没变就不动 FTS）===")
t.execute("DROP TRIGGER trg_gov_raw_fts_upd")
t.execute("""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""")
t.commit()
test("方案 B")
t.close()
print()
print("（全部在副本库 /tmp/_fix_test.db 上验证，生产库未改动）")