#!/bin/bash
# 数据库错误恢复脚本

echo "数据库错误恢复 - $(date)"
echo "========================================"

DB_FILE="/root/data/disk/system/database/agents.db"
BACKUP_DIR="/root/data/disk/system/database/backup"

# 检查数据库状态
echo "1. 检查数据库状态..."
if [ ! -f "$DB_FILE" ]; then
    echo "❌ 数据库文件不存在: $DB_FILE"
    
    # 尝试从备份恢复
    echo "尝试从备份恢复..."
    latest_backup=$(find "$BACKUP_DIR" -name "agents.db.*.bak" -type f | sort -r | head -1)
    
    if [ -n "$latest_backup" ]; then
        echo "恢复备份: $latest_backup"
        cp "$latest_backup" "$DB_FILE"
        echo "✅ 数据库恢复成功"
    else
        echo "❌ 没有找到备份文件"
        exit 1
    fi
fi

# 检查数据库完整性
echo "2. 检查数据库完整性..."
python3 -c "
import sqlite3

try:
    conn = sqlite3.connect('$DB_FILE', timeout=10)
    cursor = conn.cursor()
    
    # 检查表
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = [row[0] for row in cursor.fetchall()]
    
    required_tables = ['agent_status', 'agent_tasks', 'projects', 'workflows']
    missing_tables = [t for t in required_tables if t not in tables]
    
    if missing_tables:
        print(f'❌ 缺失表: {missing_tables}')
        
        # 尝试修复
        for table in missing_tables:
            if table == 'agent_status':
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS agent_status (
                    agent_id TEXT PRIMARY KEY,
                    status TEXT NOT NULL,
                    last_heartbeat TIMESTAMP,
                    current_task_id TEXT,
                    performance_metrics TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                )
                """)
                print(f'✅ 创建表: {table}')
            
            elif table == 'agent_tasks':
                cursor.execute("""
                CREATE TABLE IF NOT EXISTS agent_tasks (
                    task_id TEXT PRIMARY KEY,
                    project_id TEXT NOT NULL,
                    agent_type TEXT NOT NULL,
                    task_type TEXT NOT NULL,
                    status TEXT DEFAULT 'pending',
                    priority INTEGER DEFAULT 1,
                    dependencies TEXT,
                    input_data TEXT,
                    output_data TEXT,
                    error_message TEXT,
                    retry_count INTEGER DEFAULT 0,
                    max_retries INTEGER DEFAULT 3,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    started_at TIMESTAMP,
                    completed_at TIMESTAMP
                )
                """)
                print(f'✅ 创建表: {table}')
        
        conn.commit()
    
    else:
        print('✅ 所有必需表都存在')
    
    conn.close()
    
except Exception as e:
    print(f'❌ 数据库检查失败: {e}')
"

# 修复锁问题
echo "3. 修复锁问题..."
lock_files=("$DB_FILE-shm" "$DB_FILE-wal" "$DB_FILE-journal")
for lock_file in "${lock_files[@]}"; do
    if [ -f "$lock_file" ]; then
        echo "清理锁文件: $lock_file"
        rm -f "$lock_file"
    fi
done

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

try:
    conn = sqlite3.connect('$DB_FILE', timeout=30)
    cursor = conn.cursor()
    
    # 修复可能的数据不一致
    cursor.execute("PRAGMA integrity_check;")
    integrity = cursor.fetchone()[0]
    print(f'完整性检查: {integrity}')
    
    if integrity != 'ok':
        print('⚠️  数据库完整性有问题，尝试修复...')
        cursor.execute("REINDEX;")
        cursor.execute("ANALYZE;")
        print('✅ 修复完成')
    
    conn.commit()
    conn.close()
    print('✅ 数据库优化完成')
    
except Exception as e:
    print(f'❌ 优化失败: {e}')
"

echo "错误恢复完成!"
echo "========================================"