AI_Watch_Platform/backend/app/update_db_schema.py

import sys
import os

# Add parent directory to path so we can import app modules
sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

from sqlalchemy import text
from backend.app.core.database import SessionLocal, engine

def add_is_superuser_column():
    print(f"Connecting to database at: {engine.url}")
    db = SessionLocal()
    try:
        # Check if column exists
        result = db.execute(text("SHOW COLUMNS FROM users LIKE 'is_superuser'"))
        if result.fetchone():
            print("Column 'is_superuser' already exists.")
        else:
            print("Adding 'is_superuser' column...")
            db.execute(text("ALTER TABLE users ADD COLUMN is_superuser BOOLEAN DEFAULT FALSE"))
            # Make the first user (likely admin) a superuser
            db.execute(text("UPDATE users SET is_superuser = TRUE WHERE id = 1"))
            db.commit()
            print("Column added and admin updated.")
    except Exception as e:
        print(f"Error: {e}")
        db.rollback()
    finally:
        db.close()

if __name__ == "__main__":
    add_is_superuser_column()