#!/usr/bin/env python3
"""修复C类脚本问题：批量入库"""

import json, sqlite3, os, sys, re, html, hashlib

DB = '/root/search.db'

def strip_html(text):
    if not text:
        return ''
    text = html.unescape(text)
    text = re.sub(r'</?(?:p|div|h[1-6]|li|tr|blockquote|section|article|table|br\s*/?)[^>]*>', '\n', text, flags=re.IGNORECASE)
    text = re.sub(r'<[^>]+>', '', text)
    text = re.sub(r'[ \t]+', ' ', text)
    text = re.sub(r'\n{3,}', '\n\n', text)
    return text.strip()

def import_jsonl(path, site_name):
    if not os.path.exists(path):
        return f'❌ 文件不存在'
    if os.path.getsize(path) == 0:
        return f'⚠️ 空文件'

    conn = sqlite3.connect(DB)
    c = conn.cursor()
    added, skipped, errors = 0, 0, 0

    with open(path) as f:
        for line in f:
            line = line.strip()
            if not line:
                continue
            # Skip SQL comment lines
            if line.startswith('--'):
                continue
            try:
                item = json.loads(line)
            except:
                errors += 1
                continue

            title = (item.get('title') or '')[:500]
            url = item.get('page_url') or item.get('url') or item.get('link') or ''
            content = item.get('content') or ''
            date = item.get('publish_date') or item.get('date') or ''
            sname = site_name
            group = item.get('group') or item.get('category') or ''
            att = item.get('attachments') or []
            att_str = json.dumps(att, ensure_ascii=False) if att else ''
            summary = (item.get('summary') or strip_html(content)[:500])[:500]

            if not url:
                errors += 1
                continue

            try:
                c.execute('''INSERT OR IGNORE INTO gov_raw 
                    (title, page_url, content, publish_date, site_name, source_url, status, attachments, group_name, summary)
                    VALUES (?, ?, ?, ?, ?, ?, 'synced', ?, ?, ?)''',
                    (title, url, content, date, sname, url, att_str, group, summary))
                if c.rowcount > 0:
                    added += 1
                else:
                    skipped += 1
            except Exception as e:
                print(f'  DB error: {e}')
                errors += 1

    conn.commit()
    conn.close()
    return f'+{added} skip={skipped} err={errors}'

def migrate_temp_db():
    """迁移溪湖 temp_search.db 数据到 search.db"""
    temp_db = '/root/temp_search.db'
    if not os.path.exists(temp_db):
        return f'❌ temp_search.db 不存在'

    tconn = sqlite3.connect(temp_db)
    rows = tconn.execute('SELECT title, content, source_url, site_name, pub_date FROM gov_raw').fetchall()
    tconn.close()

    if not rows:
        return '⚠️ temp_search.db 无数据'

    # Map short site_names to config names
    sname_map = {
        'xihu_pzjg': '溪湖区-批准结果信息',
        'xihu_pzfw': '溪湖区-批准服务信息',
    }

    conn = sqlite3.connect(DB)
    c = conn.cursor()
    added = 0
    for title, content, source_url, short_sname, pub_date in rows:
        sname = sname_map.get(short_sname, short_sname)
        try:
            c.execute('''INSERT OR IGNORE INTO gov_raw 
                (title, page_url, content, publish_date, site_name, source_url, status)
                VALUES (?, ?, ?, ?, ?, ?, 'synced')''',
                (title, source_url, content, pub_date, sname, source_url))
            if c.rowcount > 0:
                added += 1
        except Exception as e:
            pass
    conn.commit()
    conn.close()
    return f'+{added} 条从 temp_search.db 迁移'

# === Main ===
print('=' * 60)
print('C类脚本批量修复')
print('=' * 60)

# 1. 溪湖 temp DB 迁移
print('\n[1/4] 溪湖区 temp_search.db 迁移')
print(migrate_temp_db())

# 2. 祥云 JSONL 导入
print('\n[2/4] 祥云股份-招标公告')
print(import_jsonl('/root/gov_crawler/output/harvin.jsonl', '祥云股份-招标公告'))

# 3. 长寿 JSONL 导入
print('\n[3/4] 长寿区人民政府-部门街镇')
print(import_jsonl('/root/gov_crawler/output/cqcs_bmjz.jsonl', '长寿区人民政府-部门街镇'))

# 4. 总体验证
conn = sqlite3.connect(DB)
total = conn.execute('SELECT COUNT(*) FROM gov_raw').fetchone()[0]
for site in ['溪湖区-批准结果信息', '溪湖区-批准服务信息', '祥云股份-招标公告', '长寿区人民政府-部门街镇', '看福清-企业动态', '看福清-福清企业动态']:
    cnt = conn.execute('SELECT COUNT(*) FROM gov_raw WHERE site_name=?', (site,)).fetchone()[0]
    if cnt > 0:
        print(f'  {site}: {cnt}条')
conn.close()

print(f'\n📊 总入库: {total} 条')