#!/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'
if not os.path.exists(db_path):
    print(f"DB not found at {db_path}")
    sys.exit(1)

conn = sqlite3.connect(db_path)
cursor = conn.cursor()

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

# Insert new records
for r in results:
    content = r.get('content', '')
    attachments = json.dumps(r.get('attachments', []), ensure_ascii=False)
    
    cursor.execute(
        "INSERT INTO gov_raw (title, link, date, content, attachments, site_name, \"group\") VALUES (?, ?, ?, ?, ?, ?, ?)",
        (r['title'], r['link'], r['date'], content, attachments, r['site_name'], r['group'])
    )

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

# Sync to gov_search (FTS)
cursor.execute("""
    INSERT OR IGNORE INTO gov_search (rowid_col, title, site_name, summary, link, date, content, attachments, \"group\")
    SELECT rowid, title, site_name, substr(content, 1, 200), link, date, content, attachments, \"group\"
    FROM gov_raw
    WHERE link LIKE 'https://www.harvin.cn/news/%'
""")
conn.commit()
print(f"Synced to gov_search")

# Sync to gov_search_v3
cursor.execute("""
    INSERT OR IGNORE INTO gov_search_v3 (rowid_col, title, site_name, summary, link, date, content, attachments, \"group\")
    SELECT rowid, title, site_name, substr(content, 1, 200), link, date, content, attachments, \"group\"
    FROM gov_raw
    WHERE link LIKE 'https://www.harvin.cn/news/%'
""")
conn.commit()
print(f"Synced to gov_search_v3")

# Verify
cursor.execute("SELECT COUNT(*) FROM gov_raw WHERE site_name = '祥云股份-招标公告'")
count = cursor.fetchone()[0]
print(f"Total in gov_raw: {count}")

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