#!/bin/bash
# ============================================================
# backup_db.sh — quality_results.db 双冗余备份
# ============================================================
# 本地备份：~/gov_crawler/backups/quality_results_YYYY-MM-DDTHHMM.db
# 远程备份：root@1.94.217.116:/root/backups/quality_results_YYYY-MM-DDTHHMM.db.gz
# 保留策略：本地保留最近 7 份，远程保留全部
# ============================================================

set -euo pipefail

DB_DIR="$HOME/gov_crawler/data"
DB_FILE="quality_results.db"
BACKUP_DIR="$HOME/gov_crawler/backups"
REMOTE="root@1.94.217.116"
REMOTE_DIR="/root/backups"
TIMESTAMP=$(date +%Y-%m-%dT%H%M)
LOGFILE="$HOME/gov_crawler/backups/backup.log"

log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOGFILE"; }

# 检查源 DB
SRC="$DB_DIR/$DB_FILE"
if [ ! -f "$SRC" ]; then
    log "❌ 源 DB 不存在: $SRC"
    exit 1
fi

mkdir -p "$BACKUP_DIR"

# ─── 1. 本地备份 ───
LOCAL_BACKUP="$BACKUP_DIR/${DB_FILE%.db}_$TIMESTAMP.db"
# 使用 backup 命令，必须没有其他连接
sqlite3 "$SRC" "VACUUM INTO '$LOCAL_BACKUP'"
log "✅ 本地备份: $LOCAL_BACKUP ($(du -h "$LOCAL_BACKUP" | cut -f1))"

# 删除 7 天前的本地旧备份
find "$BACKUP_DIR" -name "${DB_FILE%.db}_*.db" -mtime +7 -delete
log "🧹 清理 7 天前的本地旧备份"

# ─── 2. 远程冗余备份 ───
REMOTE_BACKUP="${DB_FILE%.db}_$TIMESTAMP.db.gz"
gzip -c "$LOCAL_BACKUP" > "/tmp/$REMOTE_BACKUP"

if ssh -o ConnectTimeout=10 -o StrictHostKeyChecking=no "$REMOTE" \
    "mkdir -p $REMOTE_DIR" 2>/dev/null; then
    scp -o ConnectTimeout=15 -o StrictHostKeyChecking=no \
        -q "/tmp/$REMOTE_BACKUP" "$REMOTE:$REMOTE_DIR/$REMOTE_BACKUP" 2>/dev/null && \
    log "✅ 远程备份: $REMOTE:$REMOTE_DIR/$REMOTE_BACKUP" || \
    log "⚠️ 远程备份失败"
else
    log "⚠️ 远程服务器不可达，跳过远程备份"
fi

rm -f "/tmp/$REMOTE_BACKUP"

log "📊 备份完成"