src/migrate_sqlite_to_supabase.py

"""
Migration script to move data from SQLite to Supabase PostgreSQL
Run this once to migrate existing data
"""

import os
import sys
from datetime import datetime

# Add src to path
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))

def migrate_data():
    """Migrate all data from SQLite to Supabase"""
    
    # Check if Supabase is configured
    if not os.environ.get('SUPABASE_DB_URL'):
        print("❌ SUPABASE_DB_URL not set. Please set it first.")
        print("   export SUPABASE_DB_URL='postgresql://postgres:Adminhamza%402019@db.jkwtddcqbkpfqmuwxazm.supabase.co:5432/postgres'")
        return False
    
    try:
        from database import Database as SQLiteDB
        from database_supabase import SupabaseDatabase
    except ImportError as e:
        print(f"❌ Import error: {e}")
        return False
    
    # Initialize databases
    print("📦 Initializing databases...")
    sqlite_db = SQLiteDB(db_path=os.path.join('..', 'data', 'app.db'))
    supabase_db = SupabaseDatabase()
    
    print("✅ Databases initialized")
    
    # Migrate users
    print("\n👥 Migrating users...")
    try:
        users = sqlite_db.get_all_users()
        migrated_users = 0
        for user in users:
            try:
                # Check if user already exists
                existing = supabase_db.get_user(user['user_id'])
                if existing:
                    print(f"   ⏭️  User {user['user_id']} already exists, skipping")
                    continue
                
                supabase_db.create_user(user)
                migrated_users += 1
            except Exception as e:
                print(f"   ⚠️  Failed to migrate user {user.get('user_id', 'unknown')}: {e}")
        
        print(f"✅ Migrated {migrated_users} users")
    except Exception as e:
        print(f"⚠️  Error migrating users: {e}")
    
    # Migrate predictions
    print("\n📊 Migrating predictions...")
    try:
        # SQLite doesn't have a get_all_predictions method, so we'll read directly
        with sqlite_db.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM predictions')
            predictions = [dict(row) for row in cursor.fetchall()]
        
        migrated_predictions = 0
        for pred in predictions:
            try:
                # Check if prediction already exists
                # We'll just try to insert and let PostgreSQL handle duplicates
                pred_data = {
                    'user_id': pred.get('user_id'),
                    'prediction': pred.get('prediction'),
                    'confidence': pred.get('confidence', 0),
                    'audio_id': pred.get('audio_id'),
                    'model_type': pred.get('model_type'),
                    'timestamp': pred.get('timestamp', datetime.now().isoformat())
                }
                supabase_db.create_prediction(pred_data)
                migrated_predictions += 1
            except Exception as e:
                # Likely duplicate, skip
                pass
        
        print(f"✅ Migrated {migrated_predictions} predictions")
    except Exception as e:
        print(f"⚠️  Error migrating predictions: {e}")
    
    # Migrate feedback
    print("\n💬 Migrating feedback...")
    try:
        with sqlite_db.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute('SELECT * FROM feedback')
            feedbacks = [dict(row) for row in cursor.fetchall()]
        
        migrated_feedback = 0
        for fb in feedbacks:
            try:
                fb_data = {
                    'audio_id': fb.get('audio_id'),
                    'user_id': fb.get('user_id'),
                    'predicted_label': fb.get('predicted_label'),
                    'correct_label': fb.get('correct_label'),
                    'is_correct': bool(fb.get('is_correct', 0)),
                    'confidence': fb.get('confidence'),
                    'timestamp': fb.get('timestamp', datetime.now().isoformat())
                }
                supabase_db.create_feedback(fb_data)
                migrated_feedback += 1
            except Exception as e:
                # Likely duplicate, skip
                pass
        
        print(f"✅ Migrated {migrated_feedback} feedback entries")
    except Exception as e:
        print(f"⚠️  Error migrating feedback: {e}")
    
    # Verify migration
    print("\n🔍 Verifying migration...")
    sqlite_stats = sqlite_db.get_user_stats()
    supabase_stats = supabase_db.get_user_stats()
    
    print(f"SQLite users: {sqlite_stats['total_users']}")
    print(f"Supabase users: {supabase_stats['total_users']}")
    
    if supabase_stats['total_users'] >= sqlite_stats['total_users']:
        print("✅ Migration appears successful!")
    else:
        print("⚠️  Some data may not have been migrated")
    
    return True

if __name__ == '__main__':
    print("🚀 Starting SQLite to Supabase migration...")
    print("=" * 60)
    success = migrate_data()
    print("=" * 60)
    if success:
        print("✅ Migration completed!")
    else:
        print("❌ Migration failed. Check errors above.")