"""
Migration script from MongoDB/SQLite to PostgreSQL
This script migrates all data to the new PostgreSQL database
"""
import os
import json
from datetime import datetime
from dotenv import load_dotenv

load_dotenv()

# Import PostgreSQL models
from postgresql_models import (
    get_db, init_db,
    Song, Profile, Plan, ProfileSong, PlanSong
)

def migrate_from_json(json_path='data.json'):
    """Migrate data from data.json backup file"""
    print(f"📁 Looking for {json_path}...")
    
    if not os.path.exists(json_path):
        backend_path = os.path.join('backend', json_path)
        if os.path.exists(backend_path):
            json_path = backend_path
        else:
            print(f"❌ {json_path} not found")
            return False
    
    print(f"✅ Found {json_path}")
    
    # Initialize database
    print("🔧 Initializing PostgreSQL database...")
    init_db()
    db = get_db()
    
    try:
        with open(json_path, 'r', encoding='utf-8') as f:
            data = json.load(f)
        
        print(f"📊 Loaded data from {json_path}")
        
        # Migrate profiles
        profiles = data.get('profiles', [])
        print(f"\n👥 Migrating {len(profiles)} profiles...")
        
        profile_id_map = {}  # Old ID -> New ID mapping
        for p in profiles:
            name = p.get('name', '').strip()
            if not name:
                fname = p.get('first_name', '').strip()
                lname = p.get('last_name', '').strip()
                name = f"{fname} {lname}".strip()
            
            if not name:
                continue
            
            # Check if profile already exists
            existing = db.query(Profile).filter(Profile.name == name).first()
            if existing:
                print(f"  ⏭️  Profile '{name}' already exists (ID: {existing.id})")
                profile_id_map[str(p.get('id', name))] = existing.id
                continue
            
            profile = Profile(
                name=name,
                email=p.get('email', ''),
                phone=p.get('contact_number', ''),
                role=p.get('role', 'Worship Leader'),
                notes=p.get('notes', '')
            )
            db.add(profile)
            db.flush()  # Get the ID without committing
            
            old_id = str(p.get('id', name))
            profile_id_map[old_id] = profile.id
            print(f"  ✅ Created profile: {name} (ID: {profile.id})")
        
        db.commit()
        print(f"✅ Migrated {len(profile_id_map)} profiles")
        
        # Migrate songs
        songs = data.get('songs', [])
        print(f"\n🎵 Migrating {len(songs)} songs...")
        
        song_id_map = {}  # Old ID -> New ID mapping
        for s in songs:
            title = s.get('title', '').strip()
            if not title:
                continue
            
            # Check if song already exists
            existing = db.query(Song).filter(Song.title == title).first()
            if existing:
                print(f"  ⏭️  Song '{title}' already exists (ID: {existing.id})")
                song_id_map[str(s.get('id', title))] = existing.id
                continue
            
            song = Song(
                title=title,
                artist=s.get('artist') or s.get('singer') or s.get('band') or 'Unknown',
                source=s.get('source', 'Manual'),
                lyrics=s.get('lyrics') or s.get('content') or '',
                chords=s.get('chords', ''),
                key=s.get('key', ''),
                tempo=s.get('tempo', ''),
                time_signature=s.get('time_signature', ''),
                notes=s.get('notes', ''),
                tags=s.get('tags', '')
            )
            db.add(song)
            db.flush()
            
            old_id = str(s.get('id', title))
            song_id_map[old_id] = song.id
            print(f"  ✅ Created song: {title} (ID: {song.id})")
        
        db.commit()
        print(f"✅ Migrated {len(song_id_map)} songs")
        
        # Migrate profile songs (if they exist in data)
        profile_songs = data.get('profile_songs', [])
        if profile_songs:
            print(f"\n⭐ Migrating {len(profile_songs)} profile-song links...")
            for ps in profile_songs:
                old_profile_id = str(ps.get('profile_id'))
                old_song_id = str(ps.get('song_id'))
                
                if old_profile_id in profile_id_map and old_song_id in song_id_map:
                    profile_song = ProfileSong(
                        profile_id=profile_id_map[old_profile_id],
                        song_id=song_id_map[old_song_id]
                    )
                    db.add(profile_song)
            db.commit()
            print(f"✅ Migrated {len(profile_songs)} profile-song links")
        
        print("\n" + "="*50)
        print("✅ Migration completed successfully!")
        print("="*50)
        print(f"  Profiles: {len(profile_id_map)}")
        print(f"  Songs: {len(song_id_map)}")
        print("="*50)
        
        return True
        
    except Exception as e:
        print(f"\n❌ Migration failed: {e}")
        import traceback
        traceback.print_exc()
        db.rollback()
        return False
    finally:
        db.close()

def migrate_from_mongodb():
    """Migrate data from existing MongoDB database"""
    try:
        from mongodb_models import get_db as get_mongo_db
        print("🔄 Attempting to migrate from MongoDB...")
        
        mongo_db = get_mongo_db()
        pg_db = get_db()
        
        # Migrate profiles
        mongo_profiles = list(mongo_db.profiles.find())
        print(f"\n👥 Migrating {len(mongo_profiles)} profiles from MongoDB...")
        
        profile_id_map = {}
        for mp in mongo_profiles:
            existing = pg_db.query(Profile).filter(Profile.name == mp.get('name')).first()
            if existing:
                profile_id_map[mp['_id']] = existing.id
                continue
            
            profile = Profile(
                name=mp.get('name', 'Unknown'),
                email=mp.get('email', ''),
                phone=mp.get('contact_number', ''),
                notes=mp.get('notes', '')
            )
            pg_db.add(profile)
            pg_db.flush()
            profile_id_map[mp['_id']] = profile.id
            print(f"  ✅ {profile.name}")
        
        pg_db.commit()
        
        # Migrate songs
        mongo_songs = list(mongo_db.songs.find())
        print(f"\n🎵 Migrating {len(mongo_songs)} songs from MongoDB...")
        
        song_id_map = {}
        for ms in mongo_songs:
            existing = pg_db.query(Song).filter(Song.title == ms.get('title')).first()
            if existing:
                song_id_map[ms['_id']] = existing.id
                continue
            
            song = Song(
                title=ms.get('title', 'Untitled'),
                artist=ms.get('artist') or ms.get('singer') or ms.get('band') or 'Unknown',
                lyrics=ms.get('lyrics', ''),
                chords=ms.get('chords', '')
            )
            pg_db.add(song)
            pg_db.flush()
            song_id_map[ms['_id']] = song.id
            print(f"  ✅ {song.title}")
        
        pg_db.commit()
        
        print("\n✅ MongoDB migration completed!")
        pg_db.close()
        return True
        
    except ImportError:
        print("⚠️  MongoDB not available, skipping MongoDB migration")
        return False
    except Exception as e:
        print(f"❌ MongoDB migration failed: {e}")
        return False

if __name__ == '__main__':
    print("="*50)
    print("PostgreSQL Migration Script")
    print("="*50)
    print()
    
    # Try JSON migration first
    if migrate_from_json():
        print("\n✅ Migration from JSON successful!")
    else:
        print("\n⚠️  JSON migration skipped or failed")
        
        # Try MongoDB migration
        if migrate_from_mongodb():
            print("\n✅ Migration from MongoDB successful!")
        else:
            print("\n⚠️  No data sources available for migration")
    
    print("\n💡 Next steps:")
    print("  1. Verify data in PostgreSQL")
    print("  2. Update backend/.env with PostgreSQL connection string")
    print("  3. Start the Flask app with: python app.py")