#!/usr/bin/env python3
"""
import_to_server.py — 将 JSONL 文件写入服务器 search.db
用法:
  python3 import_to_server.py huanpingbao.jsonl
"""
import sys, json, os, subprocess, tempfile

if len(sys.argv) < 2:
    print("用法: python3 import_to_server.py <jsonl文件>")
    sys.exit(1)

jsonl_path = sys.argv[1]
if not os.path.exists(jsonl_path):
    print(f"文件不存在: {jsonl_path}")
    sys.exit(1)

# 读取 JSONL
items = []
with open(jsonl_path, "r", encoding="utf-8") as f:
    for line in f:
        line = line.strip()
        if line:
            items.append(json.loads(line))

print(f"📖 读取 {len(items)} 条数据")

SERVER = "root@1.94.217.116"
PASSWORD = ""   # 2026-09-10 改用 SSH 密钥认证(~/.ssh/config: hw-backup), 明文已移除

# 1. SCP JSONL 到服务器临时目录
remote_path = f"/tmp/huanpingbao_{os.getpid()}.jsonl"
scp_cmd = f'sshpass -p \'{PASSWORD}\' scp -o StrictHostKeyChecking=no "{jsonl_path}" {SERVER}:{remote_path}'
print(f"📤 上传到服务器...")
subprocess.run(scp_cmd, shell=True, check=True, timeout=30)

# 2. 在服务器上运行 Python 脚本导入
import_script = f"""
import json, sqlite3

db = sqlite3.connect(os.getenv("SEARCH_DB", "/root/search.db"))
db.execute("PRAGMA journal_mode=WAL")
db.execute("PRAGMA synchronous=NORMAL")

items = []
with open('{remote_path}', 'r', encoding='utf-8') as f:
    for line in f:
        line = line.strip()
        if line:
            items.append(json.loads(line))

ok, skip = 0, 0
for item in items:
    try:
        db.execute(\"""
            INSERT OR IGNORE INTO gov_raw
                (site_name, source_url, page_url, title, publish_date,
                 summary, content, status, category, tags)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        \""", (
            item.get('site_name',''),
            item.get('source_url',''),
            item.get('page_url',''),
            (item.get('title','') or '')[:500],
            item.get('publish_date',''),
            (item.get('summary','') or '')[:500],
            item.get('content',''),
            item.get('status','active'),
            item.get('category',''),
            item.get('tags',''),
        ))
        if db.total_changes > 0:
            ok += 1
        else:
            skip += 1
    except Exception as e:
        skip += 1

db.commit()
db.close()

import os
os.remove('{remote_path}')

print(f'OK:新增{{ok}},跳过{{skip}}')
"""

# 写入临时脚本到服务器
remote_script = f"/tmp/import_{os.getpid()}.py"
with tempfile.NamedTemporaryFile(mode='w', suffix='.py', delete=False, encoding='utf-8') as f:
    f.write(import_script)
    local_script = f.name

# SCP 脚本到服务器
subprocess.run(
    f'sshpass -p \'{PASSWORD}\' scp -o StrictHostKeyChecking=no "{local_script}" {SERVER}:{remote_script}',
    shell=True, check=True, timeout=30
)
os.unlink(local_script)  # 删除本地临时文件

# 在服务器上运行脚本
print(f"💾 写入 search.db...")
result = subprocess.run(
    f'sshpass -p \'{PASSWORD}\' ssh -o StrictHostKeyChecking=no {SERVER} "python3 {remote_script} && rm -f {remote_script}"',
    shell=True, capture_output=True, text=True, timeout=120
)

print(result.stdout.strip())

# 验证
verify = subprocess.run(
    f'sshpass -p \'{PASSWORD}\' ssh -o StrictHostKeyChecking=no {SERVER} "sqlite3 /root/search.db \\"SELECT site_name, COUNT(*) FROM gov_raw GROUP BY site_name ORDER BY COUNT(*) DESC LIMIT 5\\""',
    shell=True, capture_output=True, text=True, timeout=10
)
print(f"\n📊 search.db 最新统计:")
print(verify.stdout.strip())