#!/bin/bash
# ============================================
# 风七六本地生活平台 - 数据库备份与恢复脚本
# 符合报告建议的数据库备份演练要求
# ============================================

# 配置变量
DB_HOST="mariadb"
DB_PORT="3306"
DB_USER="fzx_user"
DB_PASSWORD="${DB_PASSWORD:-prod_secure_password}"
DB_NAME="fzx_prod"
BACKUP_DIR="/backup/database"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

# 创建备份目录
mkdir -p ${BACKUP_DIR}

# ============================================
# 函数 1: 完整数据库备份
# ============================================
backup_database() {
    echo "[$(date)] 开始完整数据库备份: ${DB_NAME}"
    
    BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"
    
    # 使用mysqldump备份（兼容mariadb）
    mysqldump -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} \
        --single-transaction \
        --routines \
        --triggers \
        --events \
        --default-character-set=utf8mb4 \
        ${DB_NAME} | gzip > ${BACKUP_FILE}
    
    if [ $? -eq 0 ]; then
        echo "[$(date)] 备份成功: ${BACKUP_FILE}"
        echo "[$(date)] 备份文件大小: $(du -h ${BACKUP_FILE} | awk '{print $1}')"
        return 0
    else
        echo "[$(date)] 备份失败!"
        return 1
    fi
}

# ============================================
# 函数 2: 数据库恢复（用于演练）
# ============================================
restore_database() {
    local BACKUP_FILE=$1
    local TARGET_DB=${2:-fzx_restore_test}
    
    echo "[$(date)] 开始恢复数据库: ${BACKUP_FILE} -> ${TARGET_DB}"
    
    # 解压并恢复
    gunzip < ${BACKUP_FILE} | mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} ${TARGET_DB}
    
    if [ $? -eq 0 ]; then
        echo "[$(date)] 恢复成功!"
        
        # 验证恢复的数据
        echo "[$(date)] 验证恢复的数据..."
        mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -e \
            "USE ${TARGET_DB}; SHOW TABLES; SELECT COUNT(*) FROM user;"
        
        return 0
    else
        echo "[$(date)] 恢复失败!"
        return 1
    fi
}

# ============================================
# 函数 3: 清理过期备份
# ============================================
cleanup_old_backups() {
    echo "[$(date)] 清理 ${RETENTION_DAYS} 天前的备份..."
    
    find ${BACKUP_DIR} -name "${DB_NAME}_*.sql.gz" -mtime +${RETENTION_DAYS} -delete
    
    echo "[$(date)] 清理完成"
}

# ============================================
# 函数 4: 备份验证演练（主要用于演练）
# ============================================
backup_drill() {
    echo "=========================================="
    echo "  风七六平台 - 数据库备份演练"
    echo "=========================================="
    
    # 1. 执行备份
    echo -e "\n[1/4] 执行完整备份..."
    if ! backup_database; then
        echo "演练失败！备份步骤出错"
        return 1
    fi
    
    # 2. 查找最新备份文件
    local LATEST_BACKUP=$(ls -t ${BACKUP_DIR}/${DB_NAME}_*.sql.gz | head -1)
    echo -e "\n[2/4] 最新备份文件: ${LATEST_BACKUP}"
    
    # 3. 创建测试数据库并恢复
    echo -e "\n[3/4] 执行恢复演练..."
    local TEST_DB="fzx_restore_test_${DATE}"
    mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -e \
        "CREATE DATABASE IF NOT EXISTS ${TEST_DB} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    
    if ! restore_database ${LATEST_BACKUP} ${TEST_DB}; then
        echo "演练失败！恢复步骤出错"
        return 1
    fi
    
    # 4. 数据一致性检查
    echo -e "\n[4/4] 数据一致性检查..."
    local PROD_COUNT=$(mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -s -e \
        "USE ${DB_NAME}; SELECT COUNT(*) FROM user;")
    local TEST_COUNT=$(mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -s -e \
        "USE ${TEST_DB}; SELECT COUNT(*) FROM user;")
    
    echo "生产用户数: ${PROD_COUNT}"
    echo "恢复用户数: ${TEST_COUNT}"
    
    if [ "${PROD_COUNT}" = "${TEST_COUNT}" ]; then
        echo -e "\n✅ 数据一致性检查通过！"
        echo -e "✅ 备份演练成功完成！"
    else
        echo -e "\n❌ 数据一致性检查失败！"
        return 1
    fi
    
    # 清理测试数据库
    echo -e "\n[清理] 清理测试数据库..."
    mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -e \
        "DROP DATABASE IF EXISTS ${TEST_DB};"
    
    return 0
}

# ============================================
# 函数 5: 增量备份（可选）
# ============================================
incremental_backup() {
    echo "[$(date)] 执行增量备份（基于binlog）..."
    
    # 刷新binlog
    mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -e "FLUSH LOGS;"
    
    # 复制最新的binlog
    local CURRENT_BINLOG=$(mysql -h ${DB_HOST} -P ${DB_PORT} -u ${DB_USER} -p${DB_PASSWORD} -s -e \
        "SHOW MASTER STATUS\G" | grep "File:" | awk '{print $2}')
    
    cp /var/lib/mysql/${CURRENT_BINLOG} ${BACKUP_DIR}/binlog_${DATE}.log
    gzip ${BACKUP_DIR}/binlog_${DATE}.log
    
    echo "[$(date)] 增量备份完成: binlog_${DATE}.log.gz"
}

# ============================================
# 主程序入口
# ============================================
case "$1" in
    backup)
        backup_database
        cleanup_old_backups
        ;;
    restore)
        restore_database $2 $3
        ;;
    drill)
        backup_drill
        ;;
    incremental)
        incremental_backup
        ;;
    cleanup)
        cleanup_old_backups
        ;;
    *)
        echo "使用方法: $0 {backup|restore|drill|incremental|cleanup}"
        echo ""
        echo "命令说明:"
        echo "  backup    - 完整备份数据库"
        echo "  restore   - 恢复数据库: $0 restore <备份文件> [目标数据库]"
        echo "  drill     - 执行备份演练（备份+恢复+验证）"
        echo "  incremental - 增量备份"
        echo "  cleanup   - 清理过期备份"
        exit 1
        ;;
esac