"""
Database Migration Script v1.3.0
Adds:
- thumbnail_path to Instructions table (for PDF thumbnails)
- is_admin to Users table (for admin panel)
"""

import sqlite3
import os

def migrate_database(db_path='lego_instructions.db'):
    """Add new fields for v1.3.0 features."""
    
    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("="*70)
    print("LEGO Instructions Manager - Database Migration v1.3.0")
    print("Adding PDF Thumbnails + Admin Panel Support")
    print("="*70)
    print()
    print(f"Database: {db_path}")
    print()
    
    try:
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()
        
        changes_made = False
        
        # Check Instructions table
        cursor.execute("PRAGMA table_info(instructions)")
        instruction_columns = [column[1] for column in cursor.fetchall()]
        
        # Add thumbnail_path to instructions
        if 'thumbnail_path' not in instruction_columns:
            print("📄 Adding 'thumbnail_path' to instructions table...")
            cursor.execute("""
                ALTER TABLE instructions 
                ADD COLUMN thumbnail_path VARCHAR(500)
            """)
            changes_made = True
            print("   ✅ Added thumbnail_path column")
        else:
            print("   ℹ️  thumbnail_path already exists")
        
        # Check Users table
        cursor.execute("PRAGMA table_info(users)")
        user_columns = [column[1] for column in cursor.fetchall()]
        
        # Add is_admin to users
        if 'is_admin' not in user_columns:
            print("🛡️  Adding 'is_admin' to users table...")
            cursor.execute("""
                ALTER TABLE users 
                ADD COLUMN is_admin BOOLEAN DEFAULT 0 NOT NULL
            """)
            changes_made = True
            print("   ✅ Added is_admin column")
            
            # Create index for performance
            print("   Creating index on is_admin...")
            try:
                cursor.execute("""
                    CREATE INDEX idx_users_is_admin ON users(is_admin)
                """)
                print("   ✅ Created index")
            except sqlite3.OperationalError:
                print("   ℹ️  Index already exists")
        else:
            print("   ℹ️  is_admin already exists")
        
        if changes_made:
            conn.commit()
            print()
            print("="*70)
            print("✅ Migration completed successfully!")
            print("="*70)
            print()
            print("New Features Available:")
            print("  📄 PDF Thumbnail Generation")
            print("  🛡️  Admin Panel")
            print()
        else:
            print()
            print("="*70)
            print("✅ Database is already up to date!")
            print("="*70)
            print()
        
        conn.close()
        
    except sqlite3.Error as e:
        print()
        print("="*70)
        print(f"❌ Migration failed: {e}")
        print("="*70)
        print()
        print("Please backup your database and try again.")
        return False
    
    return True

if __name__ == "__main__":
    print()
    
    # Try to find the database file
    db_paths = [
        'lego_instructions.db',
        'instance/lego_instructions.db',
        '../lego_instructions.db',
    ]
    
    db_found = False
    for db_path in db_paths:
        if os.path.exists(db_path):
            if migrate_database(db_path):
                db_found = True
                break
    
    if not db_found:
        print("="*70)
        print("No existing database found.")
        print("="*70)
        print()
        print("If this is a new installation:")
        print("  The database will be created with these features when you")
        print("  run the application for the first time.")
        print()
    
    print("="*70)
    print("Next Steps:")
    print("="*70)
    print()
    print("1. Install required package:")
    print("   pip install PyMuPDF --break-system-packages")
    print()
    print("2. Make your first admin user:")
    print("   python")
    print("   >>> from app import create_app, db")
    print("   >>> from app.models.user import User")
    print("   >>> app = create_app()")
    print("   >>> with app.app_context():")
    print("   ...     user = User.query.filter_by(username='YOUR_USERNAME').first()")
    print("   ...     user.is_admin = True")
    print("   ...     db.session.commit()")
    print()
    print("3. Restart Flask:")
    print("   python run.py")
    print()
    print("4. Access admin panel:")
    print("   http://localhost:5000/admin/")
    print()
    print("="*70)