"""
Database Migration Script: Add MOC Support
This script adds MOC (My Own Creation) fields to the Set model.

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

import sqlite3
import os

def migrate_database(db_path='lego_instructions.db'):
    """Add MOC fields 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 columns already exist
        cursor.execute("PRAGMA table_info(sets)")
        columns = [column[1] for column in cursor.fetchall()]
        
        changes_made = False
        
        # Add is_moc column if it doesn't exist
        if 'is_moc' not in columns:
            print("Adding 'is_moc' column...")
            cursor.execute("""
                ALTER TABLE sets 
                ADD COLUMN is_moc BOOLEAN DEFAULT 0 NOT NULL
            """)
            changes_made = True
        else:
            print("Column 'is_moc' already exists - skipping")
        
        # Add moc_designer column if it doesn't exist
        if 'moc_designer' not in columns:
            print("Adding 'moc_designer' column...")
            cursor.execute("""
                ALTER TABLE sets 
                ADD COLUMN moc_designer VARCHAR(100)
            """)
            changes_made = True
        else:
            print("Column 'moc_designer' already exists - skipping")
        
        # Add moc_description column if it doesn't exist
        if 'moc_description' not in columns:
            print("Adding 'moc_description' column...")
            cursor.execute("""
                ALTER TABLE sets 
                ADD COLUMN moc_description TEXT
            """)
            changes_made = True
        else:
            print("Column 'moc_description' already exists - skipping")
        
        if changes_made:
            # Create index on is_moc for better query performance
            print("Creating index on 'is_moc' column...")
            try:
                cursor.execute("""
                    CREATE INDEX IF NOT EXISTS idx_sets_is_moc 
                    ON sets(is_moc)
                """)
            except sqlite3.OperationalError:
                print("Index already exists - skipping")
            
            conn.commit()
            print("\n✅ Migration completed successfully!")
            print("MOC 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 MOC (My Own Creation) Support")
    print("="*60)
    print()
    
    # Try to find the database file
    db_paths = [
        '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 MOC support when you run the application.")
    
    print()
    print("="*60)
    print("Next steps:")
    print("1. Restart your Flask application")
    print("2. You can now add MOCs when creating new sets")
    print("3. Edit existing sets to mark them as MOCs")
    print("="*60)