#!/usr/bin/env python3
"""Import all 3 sites into the production search DB"""
import json, sqlite3, os, sys, re

DB_PATH = '/root/search.db'

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

data_sets = [
    {
        'name': '祥云股份-招标公告',
        'jsonl': '/root/gov_crawler/output/harvin.jsonl',
        'url_pattern': 'https://www.harvin.cn/news/%',
        'source': 'harvin',
        'group': '企业'
    },
    {
        'name': '内丘县人民政府-公告公示',
        'jsonl': '/root/gov_crawler/output/hbnq_gsgg.jsonl',
        'url_pattern': 'https://www.hbnq.gov.cn/content/11049/%',
        'source': 'hbnq',
        'group': '内丘县'
    },
    {
        'name': '潜江市人民政府-建设项目环境影响评价公示',
        'jsonl': '/root/gov_crawler/output/hbqj_eia.jsonl',
        'url_pattern': 'https://www.hbqj.gov.cn/xxgk/xxgkml/szfxxgkml/gysyjs/hjbh/jsxmhjyxpjgs/%',
        'source': 'hbqj',
        'group': '潜江市'
    }
]

total_inserted = 0
total_skipped = 0

for ds in data_sets:
    path = ds['jsonl']
    if not os.path.exists(path):
        print("SKIP: %s not found" % path)
        continue
    
    with open(path, 'r', encoding='utf-8') as f:
        results = [json.loads(line) for line in f]
    
    print("\n=== %s (%d records) ===" % (ds['name'], len(results)))
    
    # Delete existing records
    cursor.execute("DELETE FROM gov_raw WHERE page_url LIKE ?", (ds['url_pattern'],))
    deleted = cursor.rowcount
    print("Deleted %d existing records" % deleted)
    
    inserted = 0
    skipped = 0
    batch = 0
    for r in results:
        title = r['title']
        content = r.get('content', '') or ''
        page_url = r['link']
        publish_date = r.get('date', '') or ''
        site_name = r['site_name']
        source = r.get('group', ds['group'])
        attachments_json = json.dumps(r.get('attachments', []), ensure_ascii=False)
        
        # Summary from first 200 chars of content
        summary = re.sub(r'<[^>]+>', '', content)[:200] if content else title[:200]
        summary = summary.strip().replace('\n', ' ')
        
        # Build the INSERT
        try:
            cursor.execute("""
                INSERT INTO gov_raw (site_name, source_url, page_url, title, publish_date, 
                                     summary, status, category, content, tags, industry, attachments)
                VALUES (?, ?, ?, ?, ?, ?, 'published', ?, ?, '', 'other', ?)
            """, (
                site_name,
                page_url,
                page_url,
                title,
                publish_date,
                summary,
                ds['group'],
                content,
                attachments_json
            ))
            inserted += 1
        except sqlite3.IntegrityError:
            skipped += 1
        
        # Commit every 20 records to avoid timeout
        if inserted % 20 == 0:
            conn.commit()
            batch += 1
            print("  Batch %d: %d inserted..." % (batch, inserted))
    
    conn.commit()
    total_inserted += inserted
    total_skipped += skipped
    print("Inserted: %d, Skipped: %d" % (inserted, skipped))

print("\n=== DONE ===")
print("Total inserted: %d" % total_inserted)
print("Total skipped: %d" % total_skipped)
conn.close()