scripts/check_orphaned_users.py

"""
Check for orphaned user references in project_team table
"""
from sqlalchemy import create_engine, text
from app.config import settings

def check_orphaned_users():
    """Find project_team records with missing or deleted users"""
    engine = create_engine(settings.DATABASE_URL)
    
    with engine.connect() as conn:
        # Find project_team records where user doesn't exist or is deleted
        query = text("""
            SELECT 
                pt.id as team_id,
                pt.user_id,
                pt.project_id,
                u.email,
                u.deleted_at,
                u.is_active
            FROM project_team pt
            LEFT JOIN users u ON pt.user_id = u.id
            WHERE pt.deleted_at IS NULL 
              AND pt.removed_at IS NULL
              AND (u.id IS NULL OR u.deleted_at IS NOT NULL)
            ORDER BY pt.created_at DESC
        """)
        
        result = conn.execute(query)
        orphaned = result.fetchall()
        
        if orphaned:
            print(f"\n🔍 Found {len(orphaned)} orphaned team member records:\n")
            for row in orphaned:
                print(f"  Team ID: {row.team_id}")
                print(f"  User ID: {row.user_id}")
                print(f"  Project ID: {row.project_id}")
                print(f"  Email: {row.email or 'N/A'}")
                print(f"  Deleted: {row.deleted_at or 'N/A'}")
                print(f"  Active: {row.is_active if row.is_active is not None else 'N/A'}")
                print("  ---")
        else:
            print("\n✅ No orphaned team member records found")
        
        # Check specifically for the problematic user
        query2 = text("""
            SELECT id, email, deleted_at, is_active
            FROM users
            WHERE id = '129753d3-fdcd-4c28-9c10-b6e9f58891e0'
        """)
        
        result2 = conn.execute(query2)
        user = result2.fetchone()
        
        print(f"\n🔍 Checking specific user 129753d3-fdcd-4c28-9c10-b6e9f58891e0:")
        if user:
            print(f"  ✓ User exists")
            print(f"  Email: {user.email}")
            print(f"  Deleted: {user.deleted_at}")
            print(f"  Active: {user.is_active}")
        else:
            print(f"  ✗ User does NOT exist in database")
        
        # Find where this user is referenced
        query3 = text("""
            SELECT 'project_team' as table_name, COUNT(*) as count
            FROM project_team
            WHERE user_id = '129753d3-fdcd-4c28-9c10-b6e9f58891e0'
              AND deleted_at IS NULL
              AND removed_at IS NULL
            UNION ALL
            SELECT 'asset_assignments', COUNT(*)
            FROM asset_assignments
            WHERE user_id = '129753d3-fdcd-4c28-9c10-b6e9f58891e0'
              AND deleted_at IS NULL
        """)
        
        result3 = conn.execute(query3)
        references = result3.fetchall()
        
        print(f"\n📊 References to this user:")
        for ref in references:
            if ref.count > 0:
                print(f"  {ref.table_name}: {ref.count} records")

if __name__ == "__main__":
    check_orphaned_users()