import json, sqlite3

DB = '/root/search.db'
JSONL = '/root/gov_crawler/output/guazhou.jsonl'

with open(JSONL, 'r', encoding='utf-8') as f:
    records = [json.loads(line) for line in f if line.strip()]

print('Total records:', len(records))

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

cur.execute('SELECT page_url FROM gov_raw')
existing = {row[0] for row in cur.fetchall()}
print('Existing:', len(existing))

new_records = [r for r in records if r['url'] not in existing]
print('New:', len(new_records))

imported = 0
for r in new_records:
    try:
        cur.execute(
            'INSERT INTO gov_raw (title, page_url, source_url, publish_date, content, summary, site_name, attachments) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            (r['title'], r['url'], r['url'], r['date'], r['content'],
             r['summary'], r['site_name'], r.get('attachments', ''))
        )
        imported += 1
    except Exception as e:
        print('ERROR:', e)

conn.commit()
print('Imported:', imported)

site = '瓜州县人民政府'
cur.execute(
    'INSERT OR IGNORE INTO gov_search(rowid, title, site_name, summary) '
    'SELECT gr.id, gr.title, gr.site_name, gr.summary '
    'FROM gov_raw gr LEFT JOIN gov_search gs ON gs.rowid=gr.id '
    'WHERE gs.rowid IS NULL AND gr.site_name=?', (site,))
print('Synced search:', cur.rowcount)

# Sync v3
cur.execute(
    'INSERT OR IGNORE INTO gov_search_v3(rowid, title, content, source_url, publish_date, site_name) '
    'SELECT gr.id, gr.title, gr.content, gr.page_url, gr.publish_date, gr.site_name '
    'FROM gov_raw gr LEFT JOIN gov_search_v3 gs ON gs.rowid=gr.id '
    'WHERE gs.rowid IS NULL AND gr.site_name=?', (site,))
print('Synced v3:', cur.rowcount)

conn.commit()
conn.close()