1  from fastapi import APIRouter
  2  import traceback
  3  import uuid
  4  from unidecode import unidecode
  5  from fastapi import Depends, HTTPException, Path, Request
  6  import re
  7  import logging
  8  from datetime import timedelta
  9  import secrets
 10  from fastapi.responses import RedirectResponse
 11  from restai import config
 12  from restai.models.models import (
 13      ApiKeyCreate,
 14      ApiKeyCreatedResponse,
 15      ApiKeyResponse,
 16      ApiKeyUpdate,
 17      TOTPSetupResponse,
 18      TOTPEnableRequest,
 19      TOTPDisableRequest,
 20      User,
 21      UserCreate,
 22      UserLogin,
 23      UserUpdate,
 24      UsersResponse,
 25      LimitedUser,
 26  )
 27  from restai.database import get_db_wrapper, DBWrapper
 28  from restai.models.databasemodels import UserDatabase, ProjectDatabase, TeamDatabase, users_projects, teams_users, teams_admins
 29  from restai.auth import (
 30      create_access_token,
 31      get_current_username,
 32      get_current_username_admin,
 33      get_current_username_user,
 34  )
 35  from ssl import CERT_REQUIRED, PROTOCOL_TLS
 36  from ldap3 import Server, Connection, NONE, Tls
 37  from ldap3.utils.conv import escape_filter_chars
 38  from restai.utils.crypto import encrypt_api_key, hash_api_key, encrypt_totp_secret, decrypt_totp_secret, generate_recovery_codes, hash_recovery_code
 39  
 40  router = APIRouter()
 41  
 42  
 43  @router.post("/ldap")
 44  async def ldap_auth(request: Request, form_data: UserLogin, db_wrapper: DBWrapper = Depends(get_db_wrapper)):
 45      """Authenticate via LDAP and create session."""
 46      ENABLE_LDAP = config.ENABLE_LDAP
 47      LDAP_SERVER_HOST = config.LDAP_SERVER_HOST
 48      LDAP_SERVER_PORT = config.LDAP_SERVER_PORT
 49      LDAP_ATTRIBUTE_FOR_MAIL = config.LDAP_ATTRIBUTE_FOR_MAIL
 50      LDAP_ATTRIBUTE_FOR_USERNAME = config.LDAP_ATTRIBUTE_FOR_USERNAME
 51      LDAP_SEARCH_BASE = config.LDAP_SEARCH_BASE
 52      LDAP_SEARCH_FILTERS = config.LDAP_SEARCH_FILTERS
 53      LDAP_APP_DN = config.LDAP_APP_DN
 54      LDAP_APP_PASSWORD = config.LDAP_APP_PASSWORD
 55      LDAP_USE_TLS = config.LDAP_USE_TLS
 56      LDAP_CA_CERT_FILE = config.LDAP_CA_CERT_FILE
 57      LDAP_CIPHERS = (
 58          config.LDAP_CIPHERS
 59          if config.LDAP_CIPHERS
 60          else "ALL"
 61      )
 62  
 63      if not ENABLE_LDAP:
 64          raise HTTPException(400, detail="LDAP authentication is not enabled")
 65  
 66      try:
 67          tls = Tls(
 68              validate=CERT_REQUIRED,
 69              version=PROTOCOL_TLS,
 70              ca_certs_file=LDAP_CA_CERT_FILE,
 71              ciphers=LDAP_CIPHERS,
 72          )
 73      except Exception as e:
 74          if isinstance(e, HTTPException):
 75              raise e
 76          logging.exception(e)
 77          raise HTTPException(400, detail="LDAP authentication failed")
 78  
 79      try:
 80          server = Server(
 81              host=LDAP_SERVER_HOST,
 82              port=LDAP_SERVER_PORT,
 83              get_info=NONE,
 84              use_ssl=LDAP_USE_TLS,
 85              tls=tls,
 86          )
 87          connection_app = Connection(
 88              server,
 89              LDAP_APP_DN,
 90              LDAP_APP_PASSWORD,
 91              auto_bind="NONE",
 92              authentication="SIMPLE",
 93          )
 94          if not connection_app.bind():
 95              raise HTTPException(400, detail="Application account bind failed")
 96  
 97          search_success = connection_app.search(
 98              search_base=LDAP_SEARCH_BASE,
 99              search_filter=f"(&({LDAP_ATTRIBUTE_FOR_USERNAME}={escape_filter_chars(form_data.user.lower())}){LDAP_SEARCH_FILTERS})",
100              attributes=[
101                  f"{LDAP_ATTRIBUTE_FOR_USERNAME}",
102                  f"{LDAP_ATTRIBUTE_FOR_MAIL}",
103                  "cn",
104              ],
105          )
106  
107          if not search_success:
108              raise HTTPException(400, detail="User not found in the LDAP server")
109  
110          entry = connection_app.entries[0]
111          username = str(entry[f"{LDAP_ATTRIBUTE_FOR_USERNAME}"]).lower()
112          mail = str(entry[f"{LDAP_ATTRIBUTE_FOR_MAIL}"])
113          if not mail or mail == "" or mail == "[]":
114              raise HTTPException(400, f"User {form_data.user} does not have mail.")
115          cn = str(entry["cn"])
116          user_dn = entry.entry_dn
117  
118          if username == form_data.user.lower():
119              connection_user = Connection(
120                  server,
121                  user_dn,
122                  form_data.password,
123                  auto_bind="NONE",
124                  authentication="SIMPLE",
125              )
126              if not connection_user.bind():
127                  raise HTTPException(400, f"Authentication failed for {form_data.user}")
128  
129  
130              user = db_wrapper.get_user_by_username(mail)
131              if user is None:
132                  sso_restricted = db_wrapper.get_setting_value("sso_auto_restricted", "true").lower() in ("true", "1")
133                  user = db_wrapper.create_user(mail, None, False, False, restricted=sso_restricted)
134                  db_wrapper.db.commit()
135                  sso_team_id = db_wrapper.get_setting_value("sso_auto_team_id", "")
136                  if sso_team_id:
137                      try:
138                          team = db_wrapper.get_team_by_id(int(sso_team_id))
139                          if team:
140                              db_wrapper.add_user_to_team(team, user)
141                      except (ValueError, TypeError):
142                          pass
143  
144              new_token = create_access_token(
145                  data={"username": user.username}, expires_delta=timedelta(minutes=1440)
146              )
147  
148              response = RedirectResponse("./admin")
149              response.set_cookie(
150                  key="restai_token", value=new_token, samesite="strict",
151                  expires=86400, httponly=True,
152              )
153  
154              return response
155          else:
156              raise HTTPException(
157                  400,
158                  f"User {form_data.user} does not match the record. Search result: {str(entry[f'{LDAP_ATTRIBUTE_FOR_USERNAME}'])}",
159              )
160      except Exception as e:
161          if isinstance(e, HTTPException):
162              raise e
163          logging.exception(e)
164          raise HTTPException(400, detail="LDAP authentication failed")
165  
166  
167  @router.get("/users/{username}", response_model=User)
168  async def route_get_user_details(
169      username: str = Path(description="Username"),
170      _: User = Depends(get_current_username_user),
171      db_wrapper: DBWrapper = Depends(get_db_wrapper),
172  ):
173      """Get user details by username."""
174      try:
175          user_db = db_wrapper.get_user_by_username(username)
176          user_model = User.model_validate(user_db)
177          return user_model
178      except Exception as e:
179          if isinstance(e, HTTPException):
180              raise e
181          logging.exception(e)
182          raise HTTPException(status_code=404, detail="User not found")
183  
184  
185  @router.post("/users/{username}/apikeys", response_model=ApiKeyCreatedResponse, status_code=201)
186  async def route_create_user_apikey(
187      username: str = Path(description="Username"),
188      body: ApiKeyCreate = ApiKeyCreate(),
189      _: User = Depends(get_current_username_user),
190      db_wrapper: DBWrapper = Depends(get_db_wrapper),
191  ):
192      """Create a new API key for a user."""
193      try:
194          user = db_wrapper.get_user_by_username(username)
195          if user is None:
196              raise HTTPException(status_code=404, detail="User not found")
197  
198          plaintext = uuid.uuid4().hex + secrets.token_urlsafe(32)
199          encrypted = encrypt_api_key(plaintext)
200          key_hash = hash_api_key(plaintext)
201          key_prefix = plaintext[:8]
202  
203          allowed_projects_json = None
204          if body.allowed_projects is not None:
205              import json
206              # Validate the requesting user has access to all scoped projects
207              caller = db_wrapper.get_user_by_username(_.username)
208              for pid in body.allowed_projects:
209                  if not _.is_admin:
210                      has_access = any(p.id == pid for p in (caller.projects if caller else []))
211                      if not has_access and _.admin_teams:
212                          project_db = db_wrapper.get_project_by_id(pid)
213                          has_access = project_db and project_db.team_id and any(
214                              t.id == project_db.team_id for t in _.admin_teams
215                          )
216                      if not has_access:
217                          raise HTTPException(
218                              status_code=403,
219                              detail=f"Cannot scope API key to project {pid}: access denied",
220                          )
221              allowed_projects_json = json.dumps(body.allowed_projects)
222  
223          api_key_row = db_wrapper.create_api_key(
224              user_id=user.id,
225              encrypted_key=encrypted,
226              key_hash=key_hash,
227              key_prefix=key_prefix,
228              description=body.description,
229              allowed_projects=allowed_projects_json,
230              read_only=body.read_only,
231          )
232          return ApiKeyCreatedResponse(
233              id=api_key_row.id,
234              api_key=plaintext,
235              key_prefix=key_prefix,
236              description=api_key_row.description,
237              created_at=api_key_row.created_at,
238              allowed_projects=body.allowed_projects,
239              read_only=body.read_only,
240          )
241      except Exception as e:
242          if isinstance(e, HTTPException):
243              raise e
244          logging.exception(e)
245          raise HTTPException(status_code=500, detail="Internal server error")
246  
247  
248  @router.get("/users/{username}/apikeys", response_model=list[ApiKeyResponse])
249  async def route_list_user_apikeys(
250      username: str = Path(description="Username"),
251      _: User = Depends(get_current_username_user),
252      db_wrapper: DBWrapper = Depends(get_db_wrapper),
253  ):
254      """List all API keys for a user."""
255      try:
256          user = db_wrapper.get_user_by_username(username)
257          if user is None:
258              raise HTTPException(status_code=404, detail="User not found")
259          keys = db_wrapper.get_api_keys_for_user(user.id)
260          return [ApiKeyResponse.model_validate(k) for k in keys]
261      except Exception as e:
262          if isinstance(e, HTTPException):
263              raise e
264          logging.exception(e)
265          raise HTTPException(status_code=500, detail="Internal server error")
266  
267  
268  @router.patch("/users/{username}/apikeys/{key_id}", response_model=ApiKeyResponse)
269  async def route_update_user_apikey(
270      body: ApiKeyUpdate,
271      username: str = Path(description="Username"),
272      key_id: int = Path(description="API key ID"),
273      _: User = Depends(get_current_username_user),
274      db_wrapper: DBWrapper = Depends(get_db_wrapper),
275  ):
276      """Update an API key's description or monthly token quota. Setting
277      ``token_quota_monthly`` to ``0`` or ``null`` clears the cap
278      (unlimited). ``reset_usage=true`` zeros the current-month counter
279      and pushes ``quota_reset_at`` forward one calendar month."""
280      from restai.models.databasemodels import ApiKeyDatabase
281      from restai.budget import _first_of_next_month
282      user = db_wrapper.get_user_by_username(username)
283      if user is None:
284          raise HTTPException(status_code=404, detail="User not found")
285      key = (
286          db_wrapper.db.query(ApiKeyDatabase)
287          .filter(ApiKeyDatabase.id == key_id, ApiKeyDatabase.user_id == user.id)
288          .first()
289      )
290      if key is None:
291          raise HTTPException(status_code=404, detail="API key not found")
292  
293      if body.description is not None:
294          key.description = body.description
295      if body.token_quota_monthly is not None:
296          # Zero or explicit null → clear the cap (unlimited).
297          key.token_quota_monthly = body.token_quota_monthly or None
298      if body.reset_usage:
299          from datetime import datetime, timezone
300          key.tokens_used_this_month = 0
301          key.quota_reset_at = _first_of_next_month(datetime.now(timezone.utc))
302  
303      db_wrapper.db.commit()
304      db_wrapper.db.refresh(key)
305      return ApiKeyResponse.model_validate(key)
306  
307  
308  @router.delete("/users/{username}/apikeys/{key_id}")
309  async def route_delete_user_apikey(
310      username: str = Path(description="Username"),
311      key_id: int = Path(description="API key ID"),
312      _: User = Depends(get_current_username_user),
313      db_wrapper: DBWrapper = Depends(get_db_wrapper),
314  ):
315      """Delete an API key."""
316      try:
317          user = db_wrapper.get_user_by_username(username)
318          if user is None:
319              raise HTTPException(status_code=404, detail="User not found")
320          deleted = db_wrapper.delete_api_key(key_id, user.id)
321          if not deleted:
322              raise HTTPException(status_code=404, detail="API key not found")
323          return {"deleted": key_id}
324      except Exception as e:
325          if isinstance(e, HTTPException):
326              raise e
327          logging.exception(e)
328          raise HTTPException(status_code=500, detail="Internal server error")
329  
330  
331  @router.get("/users", response_model=UsersResponse)
332  async def route_get_users(
333      user: User = Depends(get_current_username),
334      db_wrapper: DBWrapper = Depends(get_db_wrapper),
335  ):
336      """List all users. Admins see all, others see team members only."""
337      # If user is admin, return all users
338      if user.is_admin:
339          users = db_wrapper.get_users()
340          users_final = [User.model_validate(user_obj) for user_obj in users]
341      # For regular users, only return users from their teams
342      else:
343          # Get the set of unique users from all teams the current user belongs to
344          team_users = set()
345          for team in user.teams:
346              # Add all users from this team to our set
347              for team_user in team.users:
348                  team_users.add(team_user.id)
349          
350          # Get all users and filter by the team user IDs
351          users = db_wrapper.get_users()
352          team_users_list = []
353          for user_obj in users:
354              if user_obj.id in team_users:
355                  team_users_list.append(user_obj)
356          
357          # Convert to LimitedUser objects
358          users_final = []
359          for user_obj in team_users_list:
360              user_model = User.model_validate(user_obj)
361              limited_user = LimitedUser(
362                  id=user_model.id,
363                  username=user_model.username
364              )
365              users_final.append(limited_user)
366  
367      return {"users": users_final}
368  
369  
370  @router.post("/users", status_code=201)
371  async def route_create_user(
372      user_create: UserCreate,
373      _: User = Depends(get_current_username_admin),
374      db_wrapper: DBWrapper = Depends(get_db_wrapper),
375  ):
376      """Create a new user (admin only)."""
377      try:
378          user_create.username = unidecode(
379              user_create.username.strip().lower().replace(" ", ".")
380          )
381          user_create.username = re.sub(r"[^\w\-.@]+", "", user_create.username)
382  
383          db_wrapper.create_user(
384              user_create.username,
385              user_create.password,
386              user_create.is_admin,
387              user_create.is_private,
388              user_create.is_restricted,
389          )
390          return {"username": user_create.username}
391      except Exception as e:
392          logging.error(e)
393          traceback.print_tb(e.__traceback__)
394          raise HTTPException(
395              status_code=500, detail="Failed to create user " + user_create.username
396          )
397  
398  
399  @router.patch("/users/{username}", response_model=User)
400  async def route_update_user(
401      username: str = Path(description="Username"),
402      user_update: UserUpdate = ...,
403      user: User = Depends(get_current_username_user),
404      db_wrapper: DBWrapper = Depends(get_db_wrapper),
405  ):
406      """Update user properties."""
407      try:
408          if user.is_restricted and not user.is_admin:
409              raise HTTPException(status_code=403, detail="Restricted users cannot edit their profile")
410  
411          user_to_update = db_wrapper.get_user_by_username(username)
412          if user_to_update is None:
413              raise HTTPException(status_code=404, detail="User not found")
414  
415          if not user.is_admin and user_update.is_admin is True:
416              raise HTTPException(status_code=403, detail="Insuficient permissions")
417  
418          if user_update.is_private is not None and user_update.is_private != user_to_update.is_private:
419              can_change = user.is_admin
420              if not can_change:
421                  caller_db = db_wrapper.get_user_by_username(user.username)
422                  for team in caller_db.admin_teams:
423                      if user_to_update in team.users or user_to_update in team.admins:
424                          can_change = True
425                          break
426              if not can_change:
427                  raise HTTPException(status_code=403, detail="Only platform admins or team admins can modify user privacy setting")
428  
429          if not user.is_admin and user_update.is_restricted is not None:
430              raise HTTPException(status_code=403, detail="Only admins can modify restriction settings")
431  
432          if not user.is_admin and user_update.projects is not None:
433              raise HTTPException(status_code=403, detail="Only admins can modify project assignments")
434  
435          # If changing password and user has 2FA enabled, require TOTP code
436          if user_update.password and user_to_update.totp_enabled:
437              if not user_update.totp_code:
438                  raise HTTPException(status_code=400, detail="TOTP code required to change password when 2FA is enabled")
439              import pyotp
440              secret = decrypt_totp_secret(user_to_update.totp_secret)
441              if not pyotp.TOTP(secret).verify(user_update.totp_code, valid_window=1):
442                  raise HTTPException(status_code=400, detail="Invalid TOTP code")
443  
444          db_wrapper.update_user(user_to_update, user_update)
445  
446          if user_update.projects is not None:
447              user_to_update.projects = []
448  
449              for project in user_update.projects:
450                  project_db = db_wrapper.get_project_by_name(project)
451                  if project_db is not None:
452                      user_to_update.projects.append(project_db)
453              db_wrapper.db.commit()
454          return User.model_validate(user_to_update)
455      except Exception as e:
456          if isinstance(e, HTTPException):
457              raise e
458          logging.exception(e)
459          raise HTTPException(status_code=500, detail="Internal server error")
460  
461  
462  @router.delete("/users/{username}")
463  async def route_delete_user(
464      username: str = Path(description="Username"),
465      _: User = Depends(get_current_username_admin),
466      db_wrapper: DBWrapper = Depends(get_db_wrapper),
467  ):
468      """Delete a user (admin only)."""
469      try:
470          user_link = db_wrapper.get_user_by_username(username)
471          if user_link is None:
472              raise HTTPException(status_code=404, detail="User not found")
473          db_wrapper.delete_user(user_link)
474          return {"deleted": username}
475      except Exception as e:
476          if isinstance(e, HTTPException):
477              raise e
478          logging.exception(e)
479          raise HTTPException(status_code=500, detail="Internal server error")
480  
481  
482  # --- TOTP 2FA Endpoints ---
483  
484  @router.get("/users/{username}/totp/status")
485  async def totp_status(
486      username: str = Path(description="Username"),
487      user: User = Depends(get_current_username),
488      db_wrapper: DBWrapper = Depends(get_db_wrapper),
489  ):
490      """Check if 2FA is enabled for a user."""
491      if not user.is_admin and user.username != username:
492          raise HTTPException(status_code=403, detail="Access denied")
493      user_db = db_wrapper.get_user_by_username(username)
494      if user_db is None:
495          raise HTTPException(status_code=404, detail="User not found")
496      return {
497          "enabled": bool(user_db.totp_enabled),
498          "enforced": db_wrapper.get_setting_value("enforce_2fa", "false").lower() in ("true", "1"),
499      }
500  
501  
502  @router.post("/users/{username}/totp/setup", response_model=TOTPSetupResponse)
503  async def totp_setup(
504      username: str = Path(description="Username"),
505      user: User = Depends(get_current_username),
506      db_wrapper: DBWrapper = Depends(get_db_wrapper),
507  ):
508      """Generate a new TOTP secret and recovery codes. Does NOT enable 2FA yet — call /enable with a valid code to activate."""
509      import pyotp
510      import json
511  
512      if not user.is_admin and user.username != username:
513          raise HTTPException(status_code=403, detail="Access denied")
514      user_db = db_wrapper.get_user_by_username(username)
515      if user_db is None:
516          raise HTTPException(status_code=404, detail="User not found")
517  
518      # Generate secret and recovery codes
519      secret = pyotp.random_base32()
520      app_name = getattr(config, "RESTAI_NAME", "RESTai") or "RESTai"
521      provisioning_uri = pyotp.TOTP(secret).provisioning_uri(username, issuer_name=app_name)
522      recovery_codes = generate_recovery_codes()
523  
524      # Store encrypted secret and hashed recovery codes (but don't enable yet)
525      user_db.totp_secret = encrypt_totp_secret(secret)
526      user_db.totp_recovery_codes = json.dumps([hash_recovery_code(c) for c in recovery_codes])
527      db_wrapper.db.commit()
528  
529      return TOTPSetupResponse(
530          secret=secret,
531          provisioning_uri=provisioning_uri,
532          recovery_codes=recovery_codes,
533      )
534  
535  
536  @router.post("/users/{username}/totp/enable")
537  async def totp_enable(
538      body: TOTPEnableRequest,
539      username: str = Path(description="Username"),
540      user: User = Depends(get_current_username),
541      db_wrapper: DBWrapper = Depends(get_db_wrapper),
542  ):
543      """Activate 2FA by confirming a valid TOTP code and password."""
544      import pyotp
545      from restai.database import verify_password
546  
547      if not user.is_admin and user.username != username:
548          raise HTTPException(status_code=403, detail="Access denied")
549      user_db = db_wrapper.get_user_by_username(username)
550      if user_db is None:
551          raise HTTPException(status_code=404, detail="User not found")
552      if not user_db.totp_secret:
553          raise HTTPException(status_code=400, detail="Run /totp/setup first")
554  
555      if not verify_password(body.password, user_db.hashed_password):
556          raise HTTPException(status_code=403, detail="Invalid password")
557  
558      secret = decrypt_totp_secret(user_db.totp_secret)
559      totp = pyotp.TOTP(secret)
560      if not totp.verify(body.code, valid_window=1):
561          raise HTTPException(status_code=400, detail="Invalid TOTP code")
562  
563      user_db.totp_enabled = True
564      db_wrapper.db.commit()
565      return {"message": "2FA enabled successfully."}
566  
567  
568  @router.post("/users/{username}/totp/disable")
569  async def totp_disable(
570      body: TOTPDisableRequest,
571      username: str = Path(description="Username"),
572      user: User = Depends(get_current_username),
573      db_wrapper: DBWrapper = Depends(get_db_wrapper),
574  ):
575      """Disable 2FA. Requires password confirmation. Blocked if admin enforces 2FA."""
576      from restai.database import verify_password
577  
578      if not user.is_admin and user.username != username:
579          raise HTTPException(status_code=403, detail="Access denied")
580  
581      if db_wrapper.get_setting_value("enforce_2fa", "false").lower() in ("true", "1"):
582          raise HTTPException(status_code=403, detail="2FA is enforced by the administrator and cannot be disabled")
583  
584      user_db = db_wrapper.get_user_by_username(username)
585      if user_db is None:
586          raise HTTPException(status_code=404, detail="User not found")
587  
588      if not verify_password(body.password, user_db.hashed_password):
589          raise HTTPException(status_code=403, detail="Invalid password")
590  
591      user_db.totp_enabled = False
592      user_db.totp_secret = None
593      user_db.totp_recovery_codes = None
594      db_wrapper.db.commit()
595      return {"message": "2FA disabled successfully."}
596  
597  
598  @router.get("/permissions/matrix", tags=["Admin"])
599  async def get_permission_matrix(
600      user: User = Depends(get_current_username),
601      db_wrapper: DBWrapper = Depends(get_db_wrapper),
602  ):
603      """Return the users x projects permission matrix.
604  
605      Admins see everything. Team leaders see only users and projects
606      belonging to their teams. Regular users get 403.
607      """
608      admin_team_ids = {t.id for t in (user.admin_teams or [])}
609  
610      if not user.is_admin and not admin_team_ids:
611          raise HTTPException(status_code=403, detail="Forbidden")
612  
613      if user.is_admin:
614          all_users = (
615              db_wrapper.db.query(UserDatabase)
616              .order_by(UserDatabase.username)
617              .all()
618          )
619          all_projects = (
620              db_wrapper.db.query(ProjectDatabase)
621              .outerjoin(TeamDatabase, ProjectDatabase.team_id == TeamDatabase.id)
622              .order_by(ProjectDatabase.name)
623              .all()
624          )
625          rows = db_wrapper.db.query(users_projects).all()
626      else:
627          # Team leader: filter to their teams
628          all_projects = (
629              db_wrapper.db.query(ProjectDatabase)
630              .outerjoin(TeamDatabase, ProjectDatabase.team_id == TeamDatabase.id)
631              .filter(ProjectDatabase.team_id.in_(admin_team_ids))
632              .order_by(ProjectDatabase.name)
633              .all()
634          )
635          project_ids = {p.id for p in all_projects}
636  
637          # Users who belong to those teams (members + admins, single query via union)
638          from sqlalchemy import union
639          members_q = db_wrapper.db.query(teams_users.c.user_id).filter(teams_users.c.team_id.in_(admin_team_ids))
640          admins_q = db_wrapper.db.query(teams_admins.c.user_id).filter(teams_admins.c.team_id.in_(admin_team_ids))
641          team_user_ids = {r[0] for r in members_q.union(admins_q).all()}
642  
643          all_users = (
644              db_wrapper.db.query(UserDatabase)
645              .filter(UserDatabase.id.in_(team_user_ids))
646              .order_by(UserDatabase.username)
647              .all()
648          )
649  
650          rows = (
651              db_wrapper.db.query(users_projects)
652              .filter(users_projects.c.project_id.in_(project_ids))
653              .filter(users_projects.c.user_id.in_(team_user_ids))
654              .all()
655          )
656  
657      return {
658          "users": [
659              {
660                  "id": u.id,
661                  "username": u.username,
662                  "is_admin": bool(u.is_admin),
663                  "is_restricted": bool(getattr(u, "is_restricted", False)),
664              }
665              for u in all_users
666          ],
667          "projects": [
668              {
669                  "id": p.id,
670                  "name": p.name,
671                  "team_id": p.team_id,
672                  "team_name": p.team.name if p.team else None,
673              }
674              for p in all_projects
675          ],
676          "assignments": [
677              {"user_id": row.user_id, "project_id": row.project_id} for row in rows
678          ],
679      }