#!/bin/bash
# 数据库日常维护脚本

echo "数据库日常维护 - $(date)"
echo "========================================"

DB_DIR="/root/data/disk/system/database"
LOG_DIR="/root/data/disk/system/logs/database"

# 备份数据库
echo "1. 备份数据库..."
for db in agents.db projects.db analytics.db; do
    if [ -f "$DB_DIR/$db" ]; then
        backup_file="$DB_DIR/backup/${db}.$(date +%Y%m%d_%H%M%S).bak"
        mkdir -p "$DB_DIR/backup"
        cp "$DB_DIR/$db" "$backup_file"
        echo "  ✅ 备份 $db -> $backup_file"
    fi
done

# 清理旧备份 (保留最近7天)
echo "2. 清理旧备份..."
find "$DB_DIR/backup" -name "*.bak" -mtime +7 -delete 2>/dev/null || true

# 执行VACUUM
echo "3. 执行数据库优化..."
python3 -c "
import sqlite3
import os

db_files = [
    '/root/data/disk/system/database/agents.db',
    '/root/data/disk/system/database/projects.db',
    '/root/data/disk/system/database/analytics.db'
]

for db_file in db_files:
    if os.path.exists(db_file):
        try:
            conn = sqlite3.connect(db_file)
            cursor = conn.cursor()
            cursor.execute('VACUUM;')
            conn.commit()
            conn.close()
            print(f'✅ 优化完成: {db_file}')
        except Exception as e:
            print(f'❌ 优化失败 {db_file}: {e}')
"

# 清理日志
echo "4. 清理旧日志..."
find "$LOG_DIR" -name "*.log" -mtime +30 -delete 2>/dev/null || true

echo "日常维护完成!"
echo "========================================"