User Management API

GET /api/v1/users/me
tags: [User Management]
summary: Get current authenticated user
operationId: getCurrentUser
description: |
  Retrieve the profile of the currently authenticated user.

  **Resource Expansion**:
  - ?include=company: Expands company details
  - ?include=preferences: Includes user preferences
  - ?include=settings: Includes account settings
  - ?include=roles: Includes role information
  - ?include=activity: Includes recent activity history

  **Performance**: <100ms (P95)

security:
  - BearerAuth: []
parameters:
  - name: include
    in: query
    description: Comma-separated list of resources to include
    required: false
    schema:
      type: string
      example: 'company,preferences,settings'
responses:
  '200':
    description: User profile retrieved successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserResponse'
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

PUT /api/v1/users/me
tags: [User Management]
summary: Update current user profile
operationId: updateCurrentUser
description: |
  Update the profile of the currently authenticated user.

  **Validation Rules**:
  - Email must be valid format and unique
  - Names: 2-100 characters
  - Bio: max 2000 characters

  **Database Only**: User updates stored in PostgreSQL only (per ADR-001, NO Cognito sync)

  **Events Published**: UserUpdatedEvent to EventBridge

  **Cache Invalidation**: All user caches cleared on update

security:
  - BearerAuth: []
requestBody:
  required: true
  content:
    application/json:
      schema:
        $ref: '#/components/schemas/UpdateUserRequest'
responses:
  '200':
    description: User updated successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserResponse'
  '400':
    description: Bad request - validation error
  '401':
    description: Unauthorized
  '409':
    description: Conflict - email already exists
  '500':
    description: Internal server error

GET /api/v1/users
tags: [User Management]
summary: List users with advanced query support
operationId: listUsers
description: |
  Retrieve a paginated list of users with optional filtering, sorting,
  field selection, and resource expansion.

  **Authorization**: ORGANIZER role required

  **Filter Syntax Examples**:
  - Single filter: {"role":"SPEAKER"}
  - Multiple fields: {"role":"SPEAKER","isActive":true}
  - Company filter: {"companyName":"GoogleZH"}
  - Logical operators: {"$or":[{"role":"SPEAKER"},{"role":"PARTNER"}]}

  **Sort Syntax**:
  - Ascending: lastName or +lastName
  - Descending: -createdAt
  - Multiple fields: lastName,-createdAt

  **Field Selection**:
  - Specific fields: ?fields=id,email,firstName,lastName,roles
  - All fields: omit fields parameter

  **Resource Expansion**:
  - ?include=company: Expands company details
  - ?include=roles: Includes full role information
  - ?include=preferences: Includes user preferences

  **Performance**:
  - Basic query: <100ms (P95)
  - With all includes: <150ms (P95)

security:
  - BearerAuth: []
parameters:
  - name: filter
    in: query
    description: MongoDB-style JSON filter criteria
    required: false
    schema:
      type: string
      example: '{"role":"SPEAKER","isActive":true}'
  - name: sort
    in: query
    description: Sort fields (comma-separated, prefix with - for descending)
    required: false
    schema:
      type: string
      example: '-createdAt,lastName'
  - name: page
    in: query
    description: Page number (1-indexed)
    required: false
    schema:
      type: integer
      minimum: 1
      default: 1
  - name: limit
    in: query
    description: Items per page (max 100)
    required: false
    schema:
      type: integer
      minimum: 1
      maximum: 100
      default: 20
  - name: fields
    in: query
    description: Comma-separated field names for sparse fieldsets
    required: false
    schema:
      type: string
      example: 'id,email,firstName,lastName,roles'
  - name: include
    in: query
    description: Comma-separated list of resources to include
    required: false
    schema:
      type: string
      example: 'company,roles,preferences'
  - name: role
    in: query
    description: Filter by specific role (deprecated, use filter parameter)
    required: false
    schema:
      type: string
      enum: [ORGANIZER, SPEAKER, PARTNER, ATTENDEE]
  - name: company
    in: query
    description: Filter by company ID (deprecated, use filter parameter)
    required: false
    schema:
      type: string

      description: Meaningful identifier (see ADR-003)
responses:
  '200':
    description: Successful response with paginated users
    headers:
      X-Cache-Status:
        description: Cache hit status (HIT or MISS)
        schema:
          type: string
          enum: [HIT, MISS]
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/PaginatedUserResponse'
  '400':
    description: Bad request - invalid filter syntax or parameters
  '401':
    description: Unauthorized
  '403':
    description: Forbidden - requires ORGANIZER role
  '500':
    description: Internal server error

GET /api/v1/users/{id}
tags: [User Management]
summary: Get user details
operationId: getUserById
description: |
  Retrieve detailed information about a specific user.

  **Resource Expansion**: Supports ?include parameter

  **Performance**: <150ms (P95)

security:
  - BearerAuth: []
parameters:
  - name: id
    in: path
    description: User UUID
    required: true
    schema:
      type: string

      description: Meaningful identifier (see ADR-003)
  - name: include
    in: query
    description: Comma-separated list of resources to include
    required: false
    schema:
      type: string
      example: 'company,roles'
responses:
  '200':
    description: User details retrieved successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserResponse'
  '401':
    description: Unauthorized
  '404':
    description: User not found
  '500':
    description: Internal server error

GET /api/v1/users/search
tags: [User Search]
summary: Search users with autocomplete
operationId: searchUsers
description: |
  Search users by name or email with autocomplete functionality.

  **Caching**:
  - Caffeine in-memory cache with 10-minute TTL
  - Cache key includes query, role filter, and limit
  - Automatic cache invalidation on user updates

  **Performance**:
  - Cached response: <50ms (P95)
  - Cache miss: <100ms (P95)

  **Default Results**: 20 users (configurable via limit parameter)

security:
  - BearerAuth: []
parameters:
  - name: query
    in: query
    description: Search query (minimum 1 character)
    required: true
    schema:
      type: string
      minLength: 1
      example: 'John'
  - name: role
    in: query
    description: Filter by specific role
    required: false
    schema:
      type: string
      enum: [ORGANIZER, SPEAKER, PARTNER, ATTENDEE]
  - name: limit
    in: query
    description: Maximum number of results (default 20)
    required: false
    schema:
      type: integer
      minimum: 1
      maximum: 100
      default: 20
responses:
  '200':
    description: Search results returned successfully
    headers:
      X-Cache-Status:
        description: Cache hit status (HIT or MISS)
        schema:
          type: string
          enum: [HIT, MISS]
    content:
      application/json:
        schema:
          type: array
          items:
            $ref: '#/components/schemas/UserSearchResponse'
  '400':
    description: Bad request - query too short
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

GET /api/v1/users/me/preferences
tags: [User Preferences]
summary: Get current user preferences
operationId: getUserPreferences
description: |
  Retrieve preferences for the currently authenticated user.

  Includes: theme, language, notification settings, quiet hours

security:
  - BearerAuth: []
responses:
  '200':
    description: Preferences retrieved successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserPreferences'
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

PUT /api/v1/users/me/preferences
tags: [User Preferences]
summary: Update current user preferences
operationId: updateUserPreferences
description: |
  Update preferences for the currently authenticated user.

  **Validation**: All preference values validated against allowed enums/formats

security:
  - BearerAuth: []
requestBody:
  required: true
  content:
    application/json:
      schema:
        $ref: '#/components/schemas/UserPreferences'
responses:
  '200':
    description: Preferences updated successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserPreferences'
  '400':
    description: Bad request - validation error
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

GET /api/v1/users/me/settings
tags: [User Settings]
summary: Get current user settings
operationId: getUserSettings
description: |
  Retrieve account settings for the currently authenticated user.

  Includes: privacy controls, account preferences, security settings

security:
  - BearerAuth: []
responses:
  '200':
    description: Settings retrieved successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserSettings'
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

PUT /api/v1/users/me/settings
tags: [User Settings]
summary: Update current user settings
operationId: updateUserSettings
description: |
  Update account settings for the currently authenticated user.

  **Privacy Controls**: Profile visibility, contact information display
  **Security Settings**: Two-factor authentication enablement

security:
  - BearerAuth: []
requestBody:
  required: true
  content:
    application/json:
      schema:
        $ref: '#/components/schemas/UserSettings'
responses:
  '200':
    description: Settings updated successfully
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserSettings'
  '400':
    description: Bad request - validation error
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

GET /api/v1/users/{id}/roles
tags: [Role Management]
summary: Get user roles
operationId: getUserRoles
description: |
  Retrieve roles for a specific user.

  **Authorization**: ORGANIZER role required

security:
  - BearerAuth: []
parameters:
  - name: id
    in: path
    required: true
    schema:
      type: string

      description: Meaningful identifier (see ADR-003)
responses:
  '200':
    description: Roles retrieved successfully
    content:
      application/json:
        schema:
          type: object
          properties:
            username:
              type: string

              description: Meaningful identifier (see ADR-003)
            roles:
              type: array
              items:
                type: string
                enum: [ORGANIZER, SPEAKER, PARTNER, ATTENDEE]
  '401':
    description: Unauthorized
  '403':
    description: Forbidden - requires ORGANIZER role
  '404':
    description: User not found
  '500':
    description: Internal server error

PUT /api/v1/users/{id}/roles
tags: [Role Management]
summary: Update user roles
operationId: updateUserRoles
description: |
  Update roles for a specific user.

  **Authorization**: ORGANIZER role required

  **Business Rules**:
  - System must maintain at least 2 active ORGANIZER role users
  - Removing ORGANIZER role from user requires approval if only 2 organizers remain

  **Events Published**: UserRoleChangedEvent to EventBridge

  **Cache Invalidation**: All user caches cleared on role update

security:
  - BearerAuth: []
parameters:
  - name: id
    in: path
    required: true
    schema:
      type: string

      description: Meaningful identifier (see ADR-003)
requestBody:
  required: true
  content:
    application/json:
      schema:
        type: object
        required:
          - roles
        properties:
          roles:
            type: array
            items:
              type: string
              enum: [ORGANIZER, SPEAKER, PARTNER, ATTENDEE]
            minItems: 1
responses:
  '200':
    description: Roles updated successfully
    content:
      application/json:
        schema:
          type: object
          properties:
            username:
              type: string

              description: Meaningful identifier (see ADR-003)
            roles:
              type: array
              items:
                type: string
  '400':
    description: Bad request - validation error or business rule violation
  '401':
    description: Unauthorized
  '403':
    description: Forbidden - requires ORGANIZER role
  '404':
    description: User not found
  '409':
    description: Conflict - would violate minimum organizers rule
  '500':
    description: Internal server error

GET /api/v1/users/{id}/activity
tags: [Activity History]
summary: Get user activity history
operationId: getUserActivity
description: |
  Retrieve paginated activity history for a specific user.

  **Activity Types**: event_registered, session_attended, topic_voted, content_viewed, etc.

  **Timeframe Filtering**: Support for date range queries

security:
  - BearerAuth: []
parameters:
  - name: id
    in: path
    required: true
    schema:
      type: string

      description: Meaningful identifier (see ADR-003)
  - name: timeframe
    in: query
    description: Filter by timeframe
    required: false
    schema:
      type: string
      enum: [24h, 7d, 30d, 90d, all]
      default: 30d
  - name: activityType
    in: query
    description: Filter by activity type
    required: false
    schema:
      type: string
  - name: page
    in: query
    required: false
    schema:
      type: integer
      minimum: 1
      default: 1
  - name: limit
    in: query
    required: false
    schema:
      type: integer
      minimum: 1
      maximum: 100
      default: 20
responses:
  '200':
    description: Activity history retrieved successfully
    content:
      application/json:
        schema:
          type: object
          properties:
            data:
              type: array
              items:
                $ref: '#/components/schemas/ActivityHistory'
            pagination:
              $ref: '#/components/schemas/PaginationMetadata'
  '401':
    description: Unauthorized
  '403':
    description: Forbidden - can only view own activity unless ORGANIZER
  '404':
    description: User not found
  '500':
    description: Internal server error

PATCH /api/v1/users/me
tags: [User Management, Profile Picture]
summary: Update user profile (including profile picture)
operationId: updateCurrentUser
description: |
  Update user profile fields including profile picture association.

  **Profile Picture (ADR-002)**:
  - Set profilePictureFileId to uploadId from Generic File Upload Service
  - User service will associate logo, copy to final location, update profilePictureUrl
  - To remove: set profilePictureFileId to null

  **Fields**:
  - firstName, lastName: User's name (ADR-004: single source of truth)
  - bio: Biography (ADR-004: single source of truth for Speaker, Attendee, etc.)
  - profilePictureFileId: Logo uploadId from ADR-002 upload flow
  - termsAccepted: ToS/Privacy consent (Story 12.11) — WRITE-ONCE: `true` with no
    consent on record stamps terms_accepted_at with the SERVER clock; repeat `true`
    is a no-op; `false`/absent never changes or revokes recorded consent. GET
    /users/me returns `termsAcceptedAt` unconditionally (null = no consent → the
    frontend onboarding gate redirects to /profile?onboarding=1). See
    06b-user-lifecycle-sync.md "Pattern C".

security:
  - BearerAuth: []
requestBody:
  required: true
  content:
    application/json:
      schema:
        type: object
        properties:
          firstName:
            type: string
            minLength: 2
            maxLength: 100
          lastName:
            type: string
            minLength: 2
            maxLength: 100
          bio:
            type: string
            maxLength: 2000
          profilePictureFileId:
            type: string
            description: Logo uploadId from Generic File Upload Service (ADR-002)
            example: abc123-def456
          termsAccepted:
            type: boolean
            description: Write-once ToS/Privacy consent (Story 12.11) — server clock; never revocable via API
            example: true
      example:
        firstName: Anna
        lastName: Müller
        bio: Passionate about distributed systems and cloud architecture.
        profilePictureFileId: abc123-def456
responses:
  '200':
    description: User updated successfully, profile picture associated
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/UserResponse'
        example:
          username: anna.mueller
          email: anna.mueller@example.com
          firstName: Anna
          lastName: Müller
          bio: Passionate about distributed systems and cloud architecture.
          profilePictureUrl: https://cdn.batbern.ch/logos/2025/users/anna.mueller/logo-abc123.jpg
          profilePictureS3Key: logos/2025/users/anna.mueller/logo-abc123.jpg
          profilePictureFileId: abc123-def456
  '400':
    description: Bad request - validation error or invalid uploadId
  '401':
    description: Unauthorized
  '404':
    description: Logo not found or not in CONFIRMED status
  '500':
    description: Internal server error

DELETE /api/v1/users/me/picture
tags: [Profile Picture]
summary: Remove profile picture
operationId: removeProfilePicture
description: |
  Remove user's profile picture (reverts to default avatar).

  **Action**:
  - Removes profilePictureUrl, profilePictureS3Key, profilePictureFileId from user
  - Marks associated logo as deleted (soft delete)
  - S3 file cleanup handled by scheduled job

security:
  - BearerAuth: []
responses:
  '204':
    description: Profile picture removed successfully
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

DELETE /api/v1/users/{id}
tags: [GDPR Compliance]
summary: Delete user and all associated data
operationId: deleteUser
description: |
  Delete a user and cascade deletion across all domain services.

  **Authorization**: ORGANIZER role or self-deletion

  **Business Rules**:
  - Cannot delete last ORGANIZER user
  - Cascade deletion across Event, Speaker, Partner, Attendee services
  - Audit logging for GDPR compliance

  **Events Published**: UserDeletedEvent to EventBridge

  **Deletion Scope**:
  - User profile and authentication
  - Activity history
  - Preferences and settings
  - Profile picture (S3)
  - Associated domain data (events, sessions, registrations)

security:
  - BearerAuth: []
parameters:
  - name: id
    in: path
    required: true
    schema:
      type: string

      description: Meaningful identifier (see ADR-003)
responses:
  '204':
    description: User deleted successfully
  '401':
    description: Unauthorized
  '403':
    description: Forbidden - cannot delete last organizer or insufficient permissions
  '404':
    description: User not found
  '500':
    description: Internal server error

POST /api/v1/users/get-or-create
tags: [Domain Integration]
summary: Get or create user (for domain services)
operationId: getOrCreateUser
description: |
  Idempotent endpoint for domain services to get existing user or create new user.

  **Use Cases**:
  - Speaker Service: Create user account when speaker registers
  - Partner Service: Create user account for partner contact
  - Attendee Service: Create user account during event registration

  **Idempotency**: Safe to call multiple times with same email

  **Database Only**: Creates user_profiles record only (per ADR-001, users register in Cognito separately via Story 1.2.3)

  **Performance**: <200ms (P95)

security:
  - BearerAuth: []
requestBody:
  required: true
  content:
    application/json:
      schema:
        $ref: '#/components/schemas/GetOrCreateUserRequest'
responses:
  '200':
    description: User retrieved or created
    content:
      application/json:
        schema:
          $ref: '#/components/schemas/GetOrCreateUserResponse'
  '400':
    description: Bad request - validation error
  '401':
    description: Unauthorized
  '500':
    description: Internal server error

UserResponse:
  type: object
  required:
    - id
    - email
    - firstName
    - lastName
    - roles
    - isActive
    - createdAt
    - updatedAt
  properties:
    id:
      type: string

      description: Meaningful identifier (see ADR-003)
      example: john.doe
    cognitoUserId:
      type: string
      example: cognito-user-123
    email:
      type: string
      format: email
      example: john.doe@example.com
    firstName:
      type: string
      example: John
    lastName:
      type: string
      example: Doe
    bio:
      type: string
      example: Software engineer passionate about cloud architecture
    companyName:
      type: string

      description: Meaningful identifier (see ADR-003)
      example: GoogleZH
    roles:
      type: array
      items:
        type: string
        enum: [ORGANIZER, SPEAKER, PARTNER, ATTENDEE]
    profilePictureUrl:
      type: string
      format: uri
      example: https://cdn.batbern.ch/profile-pictures/550e8400.jpg
    isActive:
      type: boolean
      example: true
    createdAt:
      type: string
      format: date-time
      example: 2025-01-15T10:00:00Z
    updatedAt:
      type: string
      format: date-time
      example: 2025-01-15T10:00:00Z
    lastLoginAt:
      type: string
      format: date-time
      example: 2025-01-20T14:30:00Z
    company:
      $ref: '#/components/schemas/Company'
    preferences:
      $ref: '#/components/schemas/UserPreferences'
    settings:
      $ref: '#/components/schemas/UserSettings'

UserPreferences:
  type: object
  properties:
    theme:
      type: string
      enum: [light, dark, auto]
      default: auto
    language:
      type: string
      enum: [de, en, fr, it]
      default: de
    emailNotifications:
      type: boolean
      default: true
    inAppNotifications:
      type: boolean
      default: true
    pushNotifications:
      type: boolean
      default: false
    notificationFrequency:
      type: string
      enum: [immediate, daily_digest, weekly_digest]
      default: immediate
    quietHoursStart:
      type: string
      pattern: '^([0-1]?[0-9]|2[0-3]):[0-5][0-9]$'
      example: '22:00'
    quietHoursEnd:
      type: string
      pattern: '^([0-1]?[0-9]|2[0-3]):[0-5][0-9]$'
      example: '07:00'

UserSettings:
  type: object
  properties:
    profileVisibility:
      type: string
      enum: [public, members_only, private]
      default: members_only
    showEmail:
      type: boolean
      default: false
    showCompany:
      type: boolean
      default: true
    showActivityHistory:
      type: boolean
      default: false
    allowMessaging:
      type: boolean
      default: true
    allowCalendarSync:
      type: boolean
      default: false
    timezone:
      type: string
      example: Europe/Zurich
    twoFactorEnabled:
      type: boolean
      default: false

UpdateUserRequest:
  type: object
  properties:
    firstName:
      type: string
      minLength: 2
      maxLength: 100
    lastName:
      type: string
      minLength: 2
      maxLength: 100
    email:
      type: string
      format: email
    bio:
      type: string
      maxLength: 2000

UserSearchResponse:
  type: object
  required:
    - id
    - email
    - firstName
    - lastName
  properties:
    id:
      type: string

      description: Meaningful identifier (see ADR-003)
    email:
      type: string
    firstName:
      type: string
    lastName:
      type: string
    companyName:
      type: string

      description: Meaningful identifier (see ADR-003)
    roles:
      type: array
      items:
        type: string
    profilePictureUrl:
      type: string
      format: uri

GetOrCreateUserRequest:
  type: object
  required:
    - email
    - firstName
    - lastName
  properties:
    email:
      type: string
      format: email
    firstName:
      type: string
    lastName:
      type: string
    companyName:
      type: string

      description: Meaningful identifier (see ADR-003)
    createIfMissing:
      type: boolean
      default: true
    cognitoSync:
      type: boolean
      default: true

GetOrCreateUserResponse:
  type: object
  required:
    - userId
    - created
    - user
  properties:
    username:
      type: string

      description: Meaningful identifier (see ADR-003)
    created:
      type: boolean
      description: True if user was created, false if existing
    cognitoUserId:
      type: string
      description: Cognito user ID (only if created)
    user:
      $ref: '#/components/schemas/UserResponse'

ActivityHistory:
  type: object
  properties:
    id:
      type: string

      description: Meaningful identifier (see ADR-003)
    username:
      type: string

      description: Meaningful identifier (see ADR-003)
    activityType:
      type: string
      example: event_registered
    entityType:
      type: string
      example: event
    entityId:
      type: string
      example: evt-123
    description:
      type: string
      example: Registered for BATbern Developer Meetup
    metadata:
      type: object
      additionalProperties: true
    timestamp:
      type: string
      format: date-time

PaginatedUserResponse:
  type: object
  required:
    - data
    - pagination
  properties:
    data:
      type: array
      items:
        $ref: '#/components/schemas/UserResponse'
    pagination:
      $ref: '#/components/schemas/PaginationMetadata'

PaginationMetadata:
  type: object
  required:
    - page
    - limit
    - totalItems
    - totalPages
    - hasNext
    - hasPrev
  properties:
    page:
      type: integer
      description: Current page (1-indexed)
      example: 1
    limit:
      type: integer
      description: Items per page
      example: 20
    totalItems:
      type: integer
      description: Total number of items
      example: 150
    totalPages:
      type: integer
      description: Total number of pages
      example: 8
    hasNext:
      type: boolean
      description: Whether there is a next page
      example: true
    hasPrev:
      type: boolean
      description: Whether there is a previous page
      example: false

{
  "username": "john.doe",
  "email": "john.doe@example.com",
  "roles": ["SPEAKER", "ATTENDEE"],
  "companyName": "GoogleZH",
  "eventTimestamp": "2025-01-15T10:00:00Z"
}

{
  "role": "SPEAKER",
  "isActive": true,
  "companyName": "GoogleZH"
}

CREATE TABLE user_profiles (
    id UUID PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,       -- meaningful ID (ADR-003)
    cognito_user_id VARCHAR(255) UNIQUE,         -- NULL until first login for pre-invited users (JIT links it)
    email VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    bio TEXT,
    company_id VARCHAR(36),
    profile_picture_url VARCHAR(2048),
    profile_picture_s3_key VARCHAR(500),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    terms_accepted_at TIMESTAMP WITH TIME ZONE,         -- Story 12.11 (V17): write-once ToS consent; NULL = onboarding gate
    picture_import_attempted_at TIMESTAMP WITH TIME ZONE, -- Story 12.12 (V18/V19): one-time federated avatar import claim
    created_at TIMESTAMP NOT NULL,
    updated_at TIMESTAMP NOT NULL,
    last_login_at TIMESTAMP,
    deleted_at TIMESTAMP
);

-- Performance Indexes
CREATE INDEX idx_user_email ON user_profiles(email);
CREATE INDEX idx_user_company ON user_profiles(company_id);
CREATE INDEX idx_user_active ON user_profiles(is_active);
CREATE INDEX idx_cognito_user_id ON user_profiles(cognito_user_id);

-- Full-text search index for name/email autocomplete
CREATE INDEX idx_user_search ON user_profiles
    USING gin(to_tsvector('english', first_name || ' ' || last_name || ' ' || email));

CREATE TABLE role_assignments (
    user_id UUID NOT NULL,
    role VARCHAR(50) NOT NULL,
    PRIMARY KEY (user_id, role),
    FOREIGN KEY (user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
);

CREATE INDEX idx_role_assignments_role ON role_assignments(role);