#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把 schema（含触发器/索引）复制到测试库，复现 constraint failed，并逐个拆触发器定位。"""
import os
import sqlite3

SRC = "/root/search.db"
TDB = "/tmp/_govraw_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:
    try:
        t.execute(sql)
    except Exception as e:
        print("  建 %s 失败: %s (%s)" % (name, e, sql[:60]))
t.commit()
print("测试库已建：%d 个对象" % len(ddl))
trg = [n for ty, n, _ in ddl if ty == "trigger"]
print("触发器: %s" % ", ".join(trg))
print()

vals = {"title": "__DIAG__", "summary": "s", "content": "c" * 300,
        "page_url": "https://example.invalid/__diag__",
        "source_url": "https://example.invalid/__diag__",
        "publish_date": "2026-09-22", "site_name": "diag",
        "script_name": "diag.py", "group_name": "diag"}
q = "INSERT INTO gov_raw (%s) VALUES (%s)" % (",".join(vals), ",".join("?" * len(vals)))


def try_ins(label):
    t.execute("SAVEPOINT sp")
    try:
        t.execute(q, tuple(vals.values()))
        t.execute("ROLLBACK TO sp"); t.execute("RELEASE sp")
        print("  %-46s ✅ 成功" % label)
        return True
    except Exception as e:
        t.execute("ROLLBACK TO sp"); t.execute("RELEASE sp")
        print("  %-46s ❌ %s: %s" % (label, type(e).__name__, e))
        return False


print("=== 复现 ===")
try_ins("原样（全部触发器都在）")
print()
print("=== 逐个拆掉触发器 ===")
for name in trg:
    t.execute("DROP TRIGGER IF EXISTS %s" % name)
    t.commit()
    ok = try_ins("拆掉 %s" % name)
    if ok:
        print()
        print("  ★★★ 元凶 = %s ★★★" % name)
        break
    # 复原
    for ty, n, sql in ddl:
        if n == name:
            t.execute(sql)
    t.commit()
print()
print("=== 索引情况 ===")
for r in t.execute("PRAGMA index_list(gov_raw)"):
    if r[2]:
        cols = [x[2] for x in t.execute("PRAGMA index_info(%s)" % r[1])]
        print("  UNIQUE %-28s %s" % (r[1], cols))
t.close()