"""
Migration script to refactor Artists and link Songs.
1. Add new columns to Artist (slug, bio, image_url).
2. Add artist_id to Song.
3. Migrate 'original_artist' string to Artist records.
"""
from sqlmodel import Session, select, text
from database import engine
from models import Artist, Song
from slugify import generate_slug as slugify

def migrate_artists():
    with Session(engine) as session:
        print("Running Artist & Covers Migration...")
        
        # 1. Schema Changes (Idempotent ALTER TABLE)
        # Artist columns
        try:
            session.exec(text("ALTER TABLE artist ADD COLUMN slug VARCHAR UNIQUE"))
            print("Added artist.slug")
        except Exception as e:
            print("artist.slug already exists or error:", e)
            session.rollback()
            
        try:
            session.exec(text("ALTER TABLE artist ADD COLUMN bio VARCHAR"))
            print("Added artist.bio")
        except Exception as e:
            session.rollback()

        try:
            session.exec(text("ALTER TABLE artist ADD COLUMN image_url VARCHAR"))
            print("Added artist.image_url")
        except Exception as e:
            session.rollback()
            
        # Song artist_id
        try:
            session.exec(text("ALTER TABLE song ADD COLUMN artist_id INTEGER REFERENCES artist(id)"))
            print("Added song.artist_id")
        except Exception as e:
            print("song.artist_id already exists or error:", e)
            session.rollback()
        
        session.commit()
        
        # 2. Data Migration
        songs = session.exec(select(Song).where(Song.original_artist != None)).all()
        print(f"Found {len(songs)} songs with original_artist string.")
        
        created_count = 0
        linked_count = 0
        
        for song in songs:
            if not song.original_artist or song.artist_id:
                continue
                
            artist_name = song.original_artist.strip()
            if not artist_name:
                continue
                
            # Clean up name (e.g., "The Beatles" vs "Beatles")
            # For now, just trust the string but ensure consistent slug
            artist_slug = slugify(artist_name)
            
            # Find or Create Artist
            artist = session.exec(select(Artist).where(Artist.slug == artist_slug)).first()
            if not artist:
                artist = Artist(name=artist_name, slug=artist_slug)
                session.add(artist)
                session.commit()
                session.refresh(artist)
                created_count += 1
                print(f"Created Artist: {artist_name} ({artist_slug})")
            
            # Link Song
            song.artist_id = artist.id
            session.add(song)
            linked_count += 1
            
        session.commit()
        print(f"Migration Complete: Created {created_count} artists, linked {linked_count} songs.")

if __name__ == "__main__":
    migrate_artists()