sync_db.sh

#!/bin/bash

# 设置颜色
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
RED='\033[0;31m'
NC='\033[0m' # No Color

# 日志函数
log() {
  echo -e "${GREEN}[$(date '+%Y-%m-%d %H:%M:%S')] $1${NC}"
}

log "数据库同步脚本启动"

# 检查环境变量
if [ -z "$WEBDAV_URL" ] || [ -z "$WEBDAV_USERNAME" ] || [ -z "$WEBDAV_PASSWORD" ]; then
  echo -e "${YELLOW}WebDAV 配置不完整，跳过同步${NC}"
  exit 0
fi

# 同步间隔（秒）
INTERVAL=${SYNC_INTERVAL:-600}
log "同步间隔设置为 $INTERVAL 秒"

# 数据库路径
DB_PATH=${DB_PATH:-/app/data/video-player.db}
log "数据库路径: $DB_PATH"

# 确保数据目录存在
mkdir -p $(dirname $DB_PATH)

# 使用 TMPDIR 环境变量
TEMP_DIR=${TMPDIR:-"/tmp"}
DB_DOWNLOAD_PATH="${TEMP_DIR}/video-player.db.download"
BACKUP_DIR="${TEMP_DIR}/backup"

# 设置备份路径
WEBDAV_BACKUP_PATH=${WEBDAV_BACKUP_PATH:-"video-player-data"}
FULL_WEBDAV_URL="${WEBDAV_URL}"

# 确保 WEBDAV_URL 以斜杠结尾
if [ "${WEBDAV_URL: -1}" != "/" ]; then
    WEBDAV_URL="${WEBDAV_URL}/"
fi

if [ -n "$WEBDAV_BACKUP_PATH" ]; then
    FULL_WEBDAV_URL="${WEBDAV_URL}${WEBDAV_BACKUP_PATH}"
    
    # 确保 FULL_WEBDAV_URL 以斜杠结尾
    if [ "${FULL_WEBDAV_URL: -1}" != "/" ]; then
        FULL_WEBDAV_URL="${FULL_WEBDAV_URL}/"
    fi
fi

log "WebDAV 同步已配置:"
log "  URL: ${FULL_WEBDAV_URL}"
log "  同步间隔: ${INTERVAL}秒"

# 检查 WebDAV 连接和权限
check_webdav_connection() {
    log "检查 WebDAV 连接和权限..."
    
    # 尝试列出根目录
    HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -X PROPFIND -H "Depth: 1" "${FULL_WEBDAV_URL}/")
    
    if [ "$HTTP_CODE" = "207" ] || [ "$HTTP_CODE" = "200" ]; then
        log "WebDAV 连接成功"
        return 0
    elif [ "$HTTP_CODE" = "404" ]; then
        echo -e "${YELLOW}WebDAV 路径不存在，尝试创建...${NC}"
        
        # 尝试创建主目录
        create_main_dir
        return $?
    elif [ "$HTTP_CODE" = "401" ]; then
        echo -e "${RED}WebDAV 认证失败，请检查用户名和密码${NC}"
        return 1
    elif [ "$HTTP_CODE" = "403" ]; then
        echo -e "${RED}WebDAV 权限不足，无法访问${NC}"
        return 1
    else
        echo -e "${RED}WebDAV 连接失败: HTTP 状态码 ${HTTP_CODE}${NC}"
        return 1
    fi
}

# 创建主目录
create_main_dir() {
    # 如果路径包含多级目录，需要逐级创建
    IFS='/' read -ra DIRS <<< "$WEBDAV_BACKUP_PATH"
    CURRENT_PATH=""
    
    for DIR in "${DIRS[@]}"; do
        if [ -z "$DIR" ]; then
            continue
        fi
        
        if [ -z "$CURRENT_PATH" ]; then
            CURRENT_PATH="$DIR"
        else
            CURRENT_PATH="$CURRENT_PATH/$DIR"
        fi
        
        HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -X MKCOL "${WEBDAV_URL}${CURRENT_PATH}/")
        
        if [ "$HTTP_CODE" = "201" ]; then
            log "创建目录成功: ${CURRENT_PATH}"
        elif [ "$HTTP_CODE" = "405" ]; then
            echo -e "${YELLOW}目录已存在: ${CURRENT_PATH}${NC}"
        elif [ "$HTTP_CODE" = "409" ]; then
            echo -e "${YELLOW}父目录不存在: ${CURRENT_PATH}${NC}"
            return 1
        elif [ "$HTTP_CODE" = "403" ]; then
            echo -e "${RED}无权限创建目录: ${CURRENT_PATH}${NC}"
            return 1
        else
            echo -e "${RED}创建目录失败: ${CURRENT_PATH}, HTTP 状态码 ${HTTP_CODE}${NC}"
            return 1
        fi
    done
    
    # 创建备份子目录
    HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -X MKCOL "${FULL_WEBDAV_URL}backups/")
    
    if [ "$HTTP_CODE" = "201" ]; then
        log "创建备份目录成功"
        return 0
    elif [ "$HTTP_CODE" = "405" ]; then
        echo -e "${YELLOW}备份目录已存在${NC}"
        return 0
    elif [ "$HTTP_CODE" = "403" ]; then
        echo -e "${RED}无权限创建备份目录${NC}"
        return 1
    else
        echo -e "${RED}创建备份目录失败: HTTP 状态码 ${HTTP_CODE}${NC}"
        return 1
    fi
}

# 从 WebDAV 下载数据库
download_db() {
    log "从 WebDAV 下载数据库..."
    
    # 使用 curl 下载数据库文件
    HTTP_CODE=$(curl -s -o "${DB_DOWNLOAD_PATH}" -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" "${FULL_WEBDAV_URL}video-player.db")
    
    if [ "$HTTP_CODE" = "200" ]; then
        # 检查文件是否为有效的 SQLite 数据库
        if [ -s "${DB_DOWNLOAD_PATH}" ]; then
            log "成功下载数据库文件"
            
            # 如果当前没有数据库或下载的数据库更新，则使用下载的数据库
            if [ ! -f "$DB_PATH" ] || [ $(stat -c %Y "${DB_DOWNLOAD_PATH}" 2>/dev/null || echo 0) -gt $(stat -c %Y "$DB_PATH" 2>/dev/null || echo 0) ]; then
                cp "${DB_DOWNLOAD_PATH}" "$DB_PATH"
                log "已更新本地数据库"
            else
                echo -e "${YELLOW}本地数据库已是最新版本${NC}"
                rm "${DB_DOWNLOAD_PATH}"
            fi
        else
            echo -e "${RED}下载的文件不是有效的文件${NC}"
            rm "${DB_DOWNLOAD_PATH}"
        fi
    elif [ "$HTTP_CODE" = "404" ]; then
        echo -e "${YELLOW}WebDAV 上不存在数据库文件，将使用本地数据库${NC}"
        rm -f "${DB_DOWNLOAD_PATH}"
    else
        echo -e "${RED}下载数据库失败: HTTP 状态码 ${HTTP_CODE}${NC}"
        rm -f "${DB_DOWNLOAD_PATH}"
    fi
}

# 上传数据库到 WebDAV
upload_db() {
    log "上传数据库到 WebDAV..."
    
    # 确保数据库文件存在
    if [ ! -f "$DB_PATH" ]; then
        echo -e "${YELLOW}本地数据库文件不存在，跳过上传${NC}"
        return
    fi
    
    # 确保备份目录存在
    log "确保备份目录存在..."
    HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -X PROPFIND -H "Depth: 1" "${FULL_WEBDAV_URL}backups/")
    
    if [ "$HTTP_CODE" != "207" ] && [ "$HTTP_CODE" != "200" ]; then
        log "备份目录不存在，尝试创建..."
        HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -X MKCOL "${FULL_WEBDAV_URL}backups/")
        
        if [ "$HTTP_CODE" != "201" ] && [ "$HTTP_CODE" != "405" ]; then
            echo -e "${RED}创建备份目录失败: HTTP 状态码 ${HTTP_CODE}${NC}"
            echo -e "${YELLOW}将跳过备份上传，但会继续上传主数据库文件${NC}"
        else
            log "备份目录创建成功"
        fi
    else
        log "备份目录已存在"
    fi
    
    # 创建备份目录（如果不存在）
    mkdir -p "${BACKUP_DIR}"
    
    # 创建带时间戳的备份
    TIMESTAMP=$(date +%Y%m%d_%H%M%S)
    cp "$DB_PATH" "${BACKUP_DIR}/video-player_${TIMESTAMP}.db"
    
    # 上传当前数据库
    HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -T "$DB_PATH" "${FULL_WEBDAV_URL}video-player.db")
    
    if [ "$HTTP_CODE" = "201" ] || [ "$HTTP_CODE" = "204" ]; then
        log "成功上传数据库"
        
        # 尝试上传到备份目录
        HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -T "${BACKUP_DIR}/video-player_${TIMESTAMP}.db" "${FULL_WEBDAV_URL}backups/video-player_${TIMESTAMP}.db")
        
        if [ "$HTTP_CODE" = "201" ] || [ "$HTTP_CODE" = "204" ]; then
            log "成功上传数据库备份 video-player_${TIMESTAMP}.db"
        elif [ "$HTTP_CODE" = "403" ] || [ "$HTTP_CODE" = "405" ]; then
            log "备份目录上传失败，尝试直接上传到主目录..."
            # 尝试直接上传到主目录
            HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -T "${BACKUP_DIR}/video-player_${TIMESTAMP}.db" "${FULL_WEBDAV_URL}video-player_backup_${TIMESTAMP}.db")
            
            if [ "$HTTP_CODE" = "201" ] || [ "$HTTP_CODE" = "204" ]; then
                log "成功上传数据库备份到主目录 video-player_backup_${TIMESTAMP}.db"
            else
                echo -e "${YELLOW}上传数据库备份到主目录也失败: HTTP 状态码 ${HTTP_CODE}${NC}"
                echo -e "${YELLOW}备份文件路径: ${BACKUP_DIR}/video-player_${TIMESTAMP}.db${NC}"
                echo -e "${YELLOW}目标 URL: ${FULL_WEBDAV_URL}video-player_backup_${TIMESTAMP}.db${NC}"
            fi
        else
            echo -e "${YELLOW}上传数据库备份失败: HTTP 状态码 ${HTTP_CODE}${NC}"
            echo -e "${YELLOW}备份文件路径: ${BACKUP_DIR}/video-player_${TIMESTAMP}.db${NC}"
            echo -e "${YELLOW}目标 URL: ${FULL_WEBDAV_URL}backups/video-player_${TIMESTAMP}.db${NC}"
        fi
    else
        echo -e "${RED}上传数据库失败: HTTP 状态码 ${HTTP_CODE}${NC}"
    fi
    
    # 清理临时文件
    rm -rf "${BACKUP_DIR}"
}

# 清理旧备份
cleanup_old_backups() {
    log "清理旧备份..."
    
    # 首先尝试列出备份目录中的备份
    # 使用 curl 列出目录内容
    BACKUPS=$(curl -s -X PROPFIND -H "Depth: 1" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" "${FULL_WEBDAV_URL}backups/" | grep -o "backups/video-player_[0-9]\{8\}_[0-9]\{6\}.db" | sort)
    
    # 如果备份目录中没有备份，尝试列出主目录中的备份
    if [ -z "$BACKUPS" ]; then
        log "备份目录中没有找到备份，尝试在主目录中查找..."
        BACKUPS=$(curl -s -X PROPFIND -H "Depth: 1" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" "${FULL_WEBDAV_URL}" | grep -o "video-player_backup_[0-9]\{8\}_[0-9]\{6\}.db" | sort)
    fi
    
    # 计算要删除的备份数量（保留最新的5个）
    KEEP_COUNT=${BACKUP_KEEP_COUNT:-5}
    BACKUP_COUNT=$(echo "$BACKUPS" | wc -l)
    DELETE_COUNT=$((BACKUP_COUNT - KEEP_COUNT))
    
    if [ $DELETE_COUNT -gt 0 ]; then
        log "找到 ${BACKUP_COUNT} 个备份，将删除最旧的 ${DELETE_COUNT} 个"
        
        # 获取要删除的备份列表
        TO_DELETE=$(echo "$BACKUPS" | head -n $DELETE_COUNT)
        
        # 删除旧备份
        for BACKUP in $TO_DELETE; do
            HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" -u "$WEBDAV_USERNAME:$WEBDAV_PASSWORD" -X DELETE "${FULL_WEBDAV_URL}${BACKUP}")
            
            if [ "$HTTP_CODE" = "204" ] || [ "$HTTP_CODE" = "200" ]; then
                log "已删除旧备份: ${BACKUP}"
            else
                echo -e "${YELLOW}删除旧备份失败: ${BACKUP}, HTTP 状态码 ${HTTP_CODE}${NC}"
            fi
        done
    else
        log "备份数量不超过5个，无需清理"
    fi
}

# 首次启动时检查 WebDAV 连接并下载数据库
if check_webdav_connection; then
    download_db
else
    echo -e "${YELLOW}WebDAV 连接或权限问题，将使用本地数据库${NC}"
fi

# 主循环
while true; do
    log "开始同步过程: $(date)"
    
    # 检查 WebDAV 连接
    if check_webdav_connection; then
        # 上传数据库
        upload_db
        
        # 清理旧备份
        cleanup_old_backups
    else
        echo -e "${RED}WebDAV 连接或权限问题，跳过本次同步${NC}"
    fi
    
    log "同步完成，下次同步将在 ${INTERVAL} 秒后进行..."
    sleep $INTERVAL
done