moto-adv-website/migrations/add_category_to_chat_rooms.py

"""
Database migration to add category field to chat_rooms table
"""

from app.extensions import db

def upgrade():
    """Add category field to chat_rooms table"""
    try:
        # Add the category column
        db.engine.execute("""
            ALTER TABLE chat_rooms 
            ADD COLUMN category VARCHAR(50) DEFAULT 'general'
        """)
        
        # Update existing rooms to have category based on room_type
        db.engine.execute("""
            UPDATE chat_rooms 
            SET category = CASE 
                WHEN room_type = 'general' THEN 'general'
                WHEN room_type = 'post_discussion' THEN 'general'
                WHEN room_type = 'admin_support' THEN 'technical'
                WHEN room_type = 'password_reset' THEN 'technical'
                ELSE 'general'
            END
        """)
        
        print("✅ Successfully added category field to chat_rooms table")
        
    except Exception as e:
        print(f"❌ Error adding category field: {e}")
        # If column already exists, that's fine
        if "duplicate column name" in str(e).lower() or "already exists" in str(e).lower():
            print("ℹ️  Category column already exists")
        else:
            raise

if __name__ == "__main__":
    upgrade()