#!/usr/bin/env python3
"""Delete old bad content for njghes and re-run the crawler to fix paragraphs"""
import sqlite3
import subprocess
import sys

DB_PATH = "/root/search.db"

conn = sqlite3.connect(DB_PATH)
c = conn.cursor()

# Check old records
c.execute("SELECT COUNT(*) FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py'")
old_count = c.fetchone()[0]
print(f"旧数据: {old_count}条")

if old_count == 0:
    print("无旧数据，直接运行爬虫")
    conn.close()
    subprocess.run(
        ["python3", "/root/gov_crawler/crawl_njghes_tzgg.py", "--pages=4"],
        check=True
    )
    sys.exit(0)

# Show first record content to confirm it's the bad version
c.execute("SELECT id, length(content), substr(content,1,100) FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py' LIMIT 1")
row = c.fetchone()
if row:
    print(f"示例 id={row[0]}: {row[1]}chars, snippet={row[2]}")

# Delete old records AND their FTS entries
print("删除旧数据...")
c.execute("DELETE FROM gov_search WHERE rowid IN (SELECT id FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py')")
deleted_fts = c.rowcount
print(f"删除FTS: {deleted_fts}条")

c.execute("DELETE FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py'")
deleted_raw = c.rowcount
print(f"删除原始数据: {deleted_raw}条")
conn.commit()
conn.close()

print("运行爬虫...")
result = subprocess.run(
    ["python3", "/root/gov_crawler/crawl_njghes_tzgg.py", "--pages=4"],
    capture_output=True, text=True, timeout=120
)
print(result.stdout)
if result.stderr:
    print(f"STDERR: {result.stderr[:500]}")

# Verify
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
c.execute("SELECT COUNT(*) FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py'")
new_count = c.fetchone()[0]
c.execute("SELECT COUNT(*) FROM gov_search WHERE rowid IN (SELECT id FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py')")
fts_count = c.fetchone()[0]
print(f"\n验证: 新入库{new_count}条, FTS{fts_count}条")

# Show first record content (should be paragraph-structured)
c.execute("SELECT id, length(content), substr(content,1,200) FROM gov_raw WHERE script_name='crawl_njghes_tzgg.py' LIMIT 1")
row = c.fetchone()
if row:
    content_sample = row[2].replace('\n', '¶')
    print(f"示例 id={row[0]}: {row[1]}chars")
    print(f"内容: {content_sample}")

conn.close()