clear_database.py

#!/usr/bin/env python3
"""
Database cleanup script for GridBot
This script clears corrupted bot state data to fix issues with excessive JSON escaping.
"""

import os
import sys
from pathlib import Path

# Add the project root to the Python path
project_root = Path(__file__).parent
sys.path.insert(0, str(project_root))

from utils.db import GridBotDB

def clear_corrupted_database():
    """Clear the corrupted database and start fresh"""
    db_path = 'gridbot.db'
    
    if not os.path.exists(db_path):
        print("✅ No database file found. Nothing to clean up.")
        return
    
    print("🔍 Found database file. Checking for corruption...")
    
    try:
        # Initialize database
        db = GridBotDB()
        
        # Check if bot_state table exists
        cursor = db.conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='bot_state'")
        if not cursor.fetchone():
            print("✅ No bot_state table found. Nothing to clean up.")
            db.close()
            return
        
        # Check for corrupted grids data
        cursor = db.conn.execute("SELECT id, bot_instance_id, grids, last_updated FROM bot_state")
        rows = cursor.fetchall()
        
        if not rows:
            print("✅ No bot state records found. Nothing to clean up.")
            db.close()
            return
        
        print(f"🔍 Found {len(rows)} bot state records")
        
        corrupted_count = 0
        for row in rows:
            record_id, bot_id, grids, last_updated = row
            if grids and isinstance(grids, str):
                # Check if grids string is excessively long (indicating corruption)
                if len(grids) > 1000:  # Normal grids JSON should be much shorter
                    corrupted_count += 1
                    print(f"⚠️  Found corrupted record ID {record_id} (bot: {bot_id})")
                    print(f"    Grids length: {len(grids)} characters")
                    print(f"    Last updated: {last_updated}")
                    print(f"    Sample: {grids[:100]}...")
                    print()
        
        if corrupted_count == 0:
            print("✅ No corrupted records found. Database appears to be clean.")
            db.close()
            return
        
        print(f"⚠️  Found {corrupted_count} corrupted records")
        
        # Ask user for confirmation
        response = input("Do you want to clear all bot state data to fix corruption? (y/N): ")
        if response.lower() != 'y':
            print("❌ Operation cancelled.")
            db.close()
            return
        
        # Try to clean up corrupted data first
        print("🔧 Attempting to clean up corrupted data...")
        cleaned_count = db.cleanup_corrupted_grids()
        
        if cleaned_count > 0:
            print(f"✅ Successfully cleaned up {cleaned_count} corrupted records")
        else:
            # If cleaning fails, clear all bot state data but preserve user preferences
            print("🧹 Cleaning failed, clearing all bot state data...")
            
            # Save user preferences before clearing
            user_prefs = db.get_all_user_preferences()
            if user_prefs:
                print(f"💾 Preserving {len(user_prefs)} user preferences:")
                for key, value in user_prefs.items():
                    print(f"   • {key}: {value}")
            
            # Clear bot state but keep user preferences
            db.clear_bot_state()
            print("✅ Cleared all bot state data from database")
            print("✅ User preferences (including account name) preserved")
        
        print("🔄 You can now restart GridBot with a clean database")
        
        db.close()
        
    except Exception as e:
        print(f"❌ Error accessing database: {str(e)}")
        print("💡 Try deleting the database file manually and restarting GridBot")

if __name__ == "__main__":
    print("🧹 GridBot Database Cleanup Tool")
    print("=" * 40)
    clear_corrupted_database()
    print("\n✨ Cleanup complete!")