import json, sqlite3, glob, os
os.chdir('/root/gov_crawler')
files = sorted(glob.glob('/root/gov_crawler/output/szepia_swxx_*.jsonl'))
jsonl_file = files[-1]
with open(jsonl_file) as f:
    records = [json.loads(line) for line in f]
print(f'Records: {len(records)} from {jsonl_file}')

db_path = '/mnt/data/search.db'
conn = sqlite3.connect(db_path)
c = conn.cursor()
SITE_NAME = '苏州市环保产业协会'
inserted = 0
skipped = 0
for r in records:
    url = r['url']
    c.execute('SELECT COUNT(*) FROM gov_raw WHERE page_url=?', (url,))
    if c.fetchone()[0] > 0:
        skipped += 1
        continue
    c.execute(
        'INSERT INTO gov_raw (page_url, title, content, publish_date, site_name, summary, attachments, source_url) '
        'VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
        (url, r['title'], r['content'], r['pub_date'], SITE_NAME,
         r['content'][:200] if r['content'] else '', r['attachments'], r['source'])
    )
    inserted += 1
conn.commit()
print(f'Inserted: {inserted}, Skipped: {skipped}')
conn.close()