#!/bin/bash
# 统一同步：提取各本地数据库 → JSONL → SCP → 服务器导入
BASE_DIR="$HOME/Nutstore Files/我的坚果云/Crawler/gov_crawler"
PASS=""
SERVER="root@1.94.217.116"

echo "========================================"
echo "📤 同步所有本地数据到服务器 search.db"
echo "========================================"

# 先传一个导入脚本到服务器
sshpass -p "$PASS" scp -o StrictHostKeyChecking=no -q "$BASE_DIR/import_jsonl.py" "$SERVER:/tmp/import_jsonl.py"

sync_source() {
    local label="$1"
    local db_path="$2"
    local table="$3"
    local site_name="${4:-}"
    local col_page_url="${5:-url}"
    
    if [ ! -f "$db_path" ] || [ ! -s "$db_path" ]; then
        echo "  ⏭️  $label: 文件不存在或为空"
        return
    fi
    
    local tag="sync_$(date +%s)_$$"
    local jsonl_path="/tmp/${tag}.jsonl"
    
    # 用Python提取数据
    python3 -c "
import json, sqlite3, sys, os, re
db_path = '$db_path'
table = '$table'
site = '$site_name' or None
col_url = '$col_page_url'
db = sqlite3.connect(db_path)
try:
    cols = [c[1] for c in db.execute(f'PRAGMA table_info({table})').fetchall()]
    sel = ['title']
    # 找URL列
    if 'page_url' in cols: sel.append('page_url')
    elif 'url' in cols: sel.append('url')
    else: sel.append('')
    # content
    if 'content' in cols: sel.append('content')
    else: sel.append('')
    # publish_date
    if 'publish_date' in cols: sel.append('publish_date')
    else: sel.append('')
    # site_name
    if 'site_name' in cols and not site: sel.append('site_name')
    elif site: sel.append(\"'$site'\") 
    else: sel.append('')
    
    if len(sel) < 5:
        db.close()
        sys.exit(0)
    
    sql = f'SELECT DISTINCT {sel[0]},{sel[1]},{sel[2]},{sel[3]},{sel[4]} FROM {table}'
    cur = db.execute(sql)
    items = []
    for row in cur.fetchall():
        url = str(row[1] or '')
        if not url: continue
        pub = str(row[3] or '')
        m = re.search(r'(\d{4})-(\d{1,2})-(\d{1,2})', pub)
        if m: pub = f\"{m.group(1)}-{int(m.group(2)):02d}-{int(m.group(3)):02d}\"
        else:
            m2 = re.search(r'(\d{4})年(\d{1,2})月(\d{1,2})日', pub)
            if m2: pub = f\"{m2.group(1)}-{int(m2.group(2)):02d}-{int(m2.group(3)):02d}\"
        sn = site if site else str(row[4] or '')
        items.append({'title': str(row[0] or ''), 'page_url': url, 'content': str(row[2] or ''),
                       'publish_date': pub, 'site_name': sn})
    db.close()
    with open('$jsonl_path', 'w') as f:
        for it in items:
            f.write(json.dumps(it, ensure_ascii=False) + '\n')
    print(len(items))
" 2>/dev/null
    
    local count=$(wc -l < "$jsonl_path" 2>/dev/null | tr -d ' ')
    if [ -z "$count" ] || [ "$count" = "0" ]; then
        rm -f "$jsonl_path"
        echo "  ⏭️  $label: 无数据"
        return
    fi
    
    # SCP到服务器
    local remote_path="/tmp/${tag}.jsonl"
    if sshpass -p "$PASS" scp -o StrictHostKeyChecking=no -q "$jsonl_path" "$SERVER:$remote_path" 2>/dev/null; then
        # 导入
        local result=$(sshpass -p "$PASS" ssh -o StrictHostKeyChecking=no "$SERVER" \
            "python3 /tmp/import_jsonl.py $remote_path '$label' 2>&1")
        echo "  $result"
    else
        echo "  ✗ $label: SCP失败"
    fi
    rm -f "$jsonl_path"
}

# 1. 本地共享 search.db
echo ""
echo "--- 1. 本地 search.db (gov_raw) ---"
sync_source "search.db" "$BASE_DIR/search.db" "gov_raw" "" ""

# 2. quality_results.db (crawl_results表)
echo ""
echo "--- 2. quality_results.db (crawl_results) ---"
sync_source "crawl_results" "$BASE_DIR/quality_results.db" "crawl_results" "" "url"

# 3. 各 xxx_results.db
echo ""
echo "--- 3. 站点结果库 ---"
sync_source "淮安工业园区" "$BASE_DIR/hipac_results.db" "crawl_results" "淮安工业园区" "url"
sync_source "工程建设验收公示网" "$BASE_DIR/yanshougs_results.db" "crawl_results" "工程建设验收公示网" "url"
sync_source "环评云" "$BASE_DIR/eiacloud_results.db" "crawl_results" "环评云" "url"
sync_source "徐州市生态环境局" "$BASE_DIR/sthj_xz_results.db" "crawl_results" "徐州市生态环境局" "url"
sync_source "延长中煤" "$BASE_DIR/ylnh_results.db" "crawl_results" "延长中煤榆林能源化工" "url"
sync_source "泰兴市" "$BASE_DIR/taixing_results.db" "crawl_results" "泰兴市生态环境局" "url"
sync_source "青岛市" "$BASE_DIR/qingdao_results.db" "crawl_results" "青岛市生态环境局" "url"
sync_source "新会区" "$BASE_DIR/xinhui_results.db" "crawl_results" "新会区-征集调查" "url"
sync_source "德宝环境" "$BASE_DIR/debaoenv_results.db" "crawl_results" "德宝环境" "url"
sync_source "郴州市" "$BASE_DIR/cz_sthjj_results.db" "crawl_results" "郴州市生态环境局" "url"

# 4. SQL文件直接导入（荆州、新疆环协）
echo ""
echo "--- 4. 大SQL文件（荆州+新疆环协）---"
for sql_file in "jzssthjj.zwgk.jingzhou.gov.cn_import.sql" "www.xjhbcy.cn_import.sql"; do
    local_path="$BASE_DIR/$sql_file"
    if [ -f "$local_path" ] && [ -s "$local_path" ]; then
        local tag="sql_$(date +%s)_$$"
        remote_path="/tmp/${tag}.sql"
        local size=$(du -h "$local_path" | cut -f1)
        echo "  传输 $sql_file (${size})..."
        if sshpass -p "$PASS" scp -o StrictHostKeyChecking=no -q "$local_path" "$SERVER:$remote_path"; then
            result=$(sshpass -p "$PASS" ssh -o StrictHostKeyChecking=no "$SERVER" \
                "cd /root && sqlite3 search.db < $remote_path 2>&1 && echo 'SQL导入成功' && rm -f $remote_path")
            echo "  $sql_file: $result"
        fi
    fi
done

# 5. 重建FTS索引
echo ""
echo "--- 5. 重建FTS索引 ---"
sshpass -p "$PASS" ssh -o StrictHostKeyChecking=no "$SERVER" \
    "sqlite3 /root/search.db 'DELETE FROM gov_search'; \
     sqlite3 /root/search.db 'INSERT INTO gov_search(rowid,title,site_name,summary) SELECT rowid,title,site_name,substr(content,1,500) FROM gov_raw WHERE content IS NOT NULL AND content!=\"\"'; \
     echo FTS重建完成; \
     total=\$(sqlite3 /root/search.db 'SELECT COUNT(*) FROM gov_raw'); \
     echo \"search.db累计: \$total 条\""

echo ""
echo "========================================"
echo "✅ 同步完成！"
echo "========================================"