#!/usr/bin/env python3
"""Import harvin data into the gov DB"""
import json
import sqlite3
import os
import sys

# Read the JSONL output
jsonl_path = '/root/gov_crawler/output/harvin.jsonl'
results = []
with open(jsonl_path, 'r', encoding='utf-8') as f:
    for line in f:
        results.append(json.loads(line))

print(f"Loaded {len(results)} records")

# Connect to the DB
db_path = '/root/gov_crawler/search.db'
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# First delete existing records from this site
cursor.execute("DELETE FROM gov_raw WHERE url LIKE 'https://www.harvin.cn/news/%'")
deleted = cursor.rowcount
print(f"Deleted {deleted} existing records")

# Insert new records
inserted = 0
for r in results:
    url = r['link']
    title = r['title']
    content = r.get('content', '')
    publish_date = r['date']
    site_name = r['site_name']
    source = r.get('group', '企业')
    attachments = json.dumps(r.get('attachments', []), ensure_ascii=False)
    
    try:
        cursor.execute(
            "INSERT INTO gov_raw (title, content, publish_date, url, site_name, source, attachments) VALUES (?, ?, ?, ?, ?, ?, ?)",
            (title, content, publish_date, url, site_name, source, attachments)
        )
        inserted += 1
    except sqlite3.IntegrityError:
        print(f"  SKIP (duplicate): {url}")
        pass

conn.commit()
print(f"Inserted {inserted} records into gov_raw")

# Sync to gov_search FTS: delete existing then re-insert
cursor.execute("""
    DELETE FROM gov_search WHERE rowid IN (
        SELECT r.id FROM gov_raw r WHERE r.url LIKE 'https://www.harvin.cn/news/%'
    )
""")

cursor.execute("""
    INSERT INTO gov_search (rowid, title, content, site_name)
    SELECT id, title, content, site_name
    FROM gov_raw
    WHERE url LIKE 'https://www.harvin.cn/news/%'
""")
conn.commit()

# Verify
cursor.execute("SELECT COUNT(*) FROM gov_raw WHERE url LIKE 'https://www.harvin.cn/news/%'")
count = cursor.fetchone()[0]
print(f"Total in gov_raw: {count}")

cursor.execute("SELECT COUNT(*) FROM gov_search WHERE rowid IN (SELECT id FROM gov_raw WHERE url LIKE 'https://www.harvin.cn/news/%')")
fts_count = cursor.fetchone()[0]
print(f"Total in gov_search FTS: {fts_count}")

conn.close()
print("Import complete!")