#!/usr/bin/env python3
"""同步 sthj_xz_results.db 到服务器（文件base64方式，解决参数过长问题）"""
import sqlite3, os, subprocess, base64

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "sthj_xz_results.db")
SITE_NAME = "徐州市生态环境局"
SERVER_SSH = "root@1.94.217.116"
SERVER_SEARCH_DB = "/root/search.db"

conn = sqlite3.connect(DB_PATH)
rows = conn.execute(
    "SELECT title, url, content, publish_date, summary FROM crawl_results WHERE site_id=1 ORDER BY id"
).fetchall()
conn.close()

print(f"📋 共 {len(rows)} 条待同步")

def esc(v):
    if v is None: return "''"
    return "'" + str(v).replace("'", "''") + "'"

sql_lines = ['PRAGMA busy_timeout=30000;']
for title, page_url, content, pub_date, summary in rows:
    content = (content or '')[:50000]
    summary = (summary or '')[:500]
    sql_lines.append(
        f"INSERT OR IGNORE INTO gov_raw "
        f"(title, page_url, content, publish_date, summary, site_name, tags) "
        f"VALUES ({esc(title)},{esc(page_url)},{esc(content)},{esc(pub_date or '')},{esc(summary)},'{SITE_NAME}','环评阶段');"
    )
sql_lines.append("INSERT INTO gov_search(gov_search) VALUES('rebuild');")
sql = '\n'.join(sql_lines)

tmpfile = '/tmp/sthj_xz_sync.sql'
with open(tmpfile, 'w', encoding='utf-8') as f:
    f.write(sql)
print(f"📝 SQL 生成 ({len(sql)} chars)")

# base64 上传
r1 = subprocess.run(
    f'base64 -i {tmpfile} | ssh {SERVER_SSH} "base64 -d > /tmp/sthj_xz_sync.sql"',
    shell=True, capture_output=True, text=True, timeout=60
)
if r1.returncode != 0:
    print(f"✗ 上传失败: {r1.stderr[:200]}")
    exit(1)

# 执行
r2 = subprocess.run(
    f'ssh {SERVER_SSH} "sqlite3 {SERVER_SEARCH_DB} < /tmp/sthj_xz_sync.sql && echo OK; rm -f /tmp/sthj_xz_sync.sql"',
    shell=True, capture_output=True, text=True, timeout=180
)
if "OK" in (r2.stdout or ""):
    print(f"✅ {len(rows)} 条同步成功!")
else:
    err = (r2.stderr or r2.stdout or "no output")[:300]
    print(f"✗ 执行失败: {err}")