"""
Database Migration Script: Add Cover Image Upload Support
This script adds the cover_image field to the Set model for uploaded images.

Run this AFTER updating your models and BEFORE running the application.
"""

import sqlite3
import os

def migrate_database(db_path='lego_instructions.db'):
    """Add cover_image field to the sets table."""
    
    if not os.path.exists(db_path):
        print(f"Database file '{db_path}' not found.")
        print("This is normal for a new installation - no migration needed.")
        return
    
    print("Starting database migration...")
    print(f"Database: {db_path}")
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        # Check if column already exists
        cursor.execute("PRAGMA table_info(sets)")
        columns = [column[1] for column in cursor.fetchall()]
        
        changes_made = False
        
        # Add cover_image column if it doesn't exist
        if 'cover_image' not in columns:
            print("Adding 'cover_image' column...")
            cursor.execute("""
                ALTER TABLE sets 
                ADD COLUMN cover_image VARCHAR(500)
            """)
            changes_made = True
        else:
            print("Column 'cover_image' already exists - skipping")
        
        if changes_made:
            conn.commit()
            print("\n✅ Migration completed successfully!")
            print("Cover image upload support has been added to your database.")
        else:
            print("\n✅ Database is already up to date - no changes needed.")
        
        conn.close()
        
    except sqlite3.Error as e:
        print(f"\n❌ Migration failed: {e}")
        print("Please backup your database and try again.")
        return False
    
    return True

if __name__ == "__main__":
    print("="*60)
    print("LEGO Instructions Manager - Database Migration")
    print("Adding Cover Image Upload Support")
    print("="*60)
    print()
    
    # Try to find the database file
    db_paths = [
        'lego_instructions.db',
        'instance/lego_instructions.db',
        '../lego_instructions.db',
        '../../lego_instructions.db',
    ]
    
    db_found = False
    for db_path in db_paths:
        if os.path.exists(db_path):
            print(f"Found database at: {db_path}")
            if migrate_database(db_path):
                db_found = True
                break
    
    if not db_found:
        print("\nNo existing database found.")
        print("If this is a new installation, the database will be created")
        print("with cover image support when you run the application.")
    
    print()
    print("="*60)
    print("Next steps:")
    print("1. Restart your Flask application")
    print("2. You can now upload cover images for sets and MOCs")
    print("3. Edit existing sets to add cover images")
    print("="*60)