#!/usr/bin/env python3
"""Fix FTS table: update site_names to match config names."""
import sqlite3

DB = '/root/search.db'

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

# Sites to fix (old_site_name -> new_site_name)
FIXES = {
    '盘锦辽滨沿海经济技术开发区': None,  # delete old entries
    '广东环科技术咨询有限公司': '广东环科-公示公告',
    '瓜州县人民政府': '瓜州县人民政府-公示公告',
    '新邱区人民政府': '新邱区-通知公告',
    '恒申集团': '恒申集团-信息公示',
}

# Step 1: Delete old FTS entries for all affected sites
c.execute('''DELETE FROM gov_search WHERE rowid IN (
    SELECT rowid FROM gov_raw WHERE site_name IN ({})
)'''.format(','.join('?' for _ in FIXES)), list(FIXES.keys()))

# Also delete any residual entries with old names
for old_name in FIXES:
    if old_name:
        c.execute("DELETE FROM gov_search WHERE site_name = ?", (old_name,))

# Step 2: Re-insert correct FTS entries
c.execute('''INSERT INTO gov_search(rowid, title, site_name, summary)
    SELECT rowid, title, site_name, COALESCE(NULLIF(summary, ''), substr(content, 1, 500), title, '')
    FROM gov_raw WHERE site_name IN ({})'''.format(','.join('?' for _ in FIXES)), list(FIXES.values()))

# Also fix 内丘 and 盘锦 entries that were already correct
c.execute('''INSERT OR IGNORE INTO gov_search(rowid, title, site_name, summary)
    SELECT rowid, title, site_name, COALESCE(NULLIF(summary, ''), substr(content, 1, 500), title, '')
    FROM gov_raw WHERE site_name IN ('内丘县人民政府-公告公示', 
        '盘锦辽滨沿海经济技术开发区-通知公告',
        '盘锦辽滨沿海经济技术开发区-环境保护',
        '广东省国际工程咨询有限公司-公示公告')''')

conn.commit()

# Verify
print("=== FTS verification ===")
for row in c.execute('''SELECT site_name, COUNT(*) FROM gov_search 
    WHERE site_name IN ('内丘县人民政府-公告公示',
        '瓜州县人民政府-公示公告',
        '盘锦辽滨沿海经济技术开发区-通知公告',
        '盘锦辽滨沿海经济技术开发区-环境保护',
        '广东环科-公示公告',
        '新邱区-通知公告',
        '恒申集团-信息公示',
        '广东省国际工程咨询有限公司-公示公告')
    GROUP BY site_name ORDER BY site_name'''):
    print(f"  {row[0]}: {row[1]}")

# Verify total
total_raw = c.execute("SELECT COUNT(*) FROM gov_raw").fetchone()[0]
total_fts = c.execute("SELECT COUNT(*) FROM gov_search").fetchone()[0]
total_configs_raw = c.execute("SELECT COUNT(DISTINCT site_name) FROM gov_search").fetchone()[0]
print(f"\nTotal raw: {total_raw}, Total FTS: {total_fts}, Unique sites: {total_configs_raw}")

conn.close()