database/
├── lib/
│   ├── database.dart              # Main Database class
│   └── src/
│       ├── entity.dart            # Base entity class & annotations
│       ├── query_builder.dart     # SQL query builder
│       ├── database_manager_query.dart  # CRUD manager
│       ├── relationship_manager.dart    # Relationship queries
│       ├── managers.dart          # Pre-configured managers
│       ├── query_helpers.dart     # Legacy helpers (backward compat)
│       ├── entities/              # Database entities (internal use)
│       │   ├── user_entity.dart
│       │   ├── user_information.dart
│       │   ├── organization.dart
│       │   ├── organization_member.dart
│       │   ├── function_entity.dart
│       │   ├── function_deployment_entity.dart
│       │   ├── function_log_entity.dart
│       │   └── function_invocation_entity.dart
│       ├── dto/                   # Data Transfer Objects (API responses)
│       │   ├── user_dto.dart
│       │   └── organization_dto.dart
│       └── managers/              # Relationship managers
│           ├── user_relationships.dart
│           └── organization_relationships.dart
└── test/
    ├── query_builder_test.dart    # 50+ tests
    ├── entity_test.dart           # 30+ tests
    └── database_manager_query_test.dart  # 40+ tests

// Owner request
final dto = OrganizationWithMembersDto.fromEntities(
  organization: org,
  members: members,
  requesterId: ownerId, // Matches organization.ownerId
);
// Result: members[].uuid = "550e8400-..."

// Non-owner request
final dto = OrganizationWithMembersDto.fromEntities(
  organization: org,
  members: members,
  requesterId: nonOwnerId, // Different from organization.ownerId
);
// Result: members[].uuid = null (omitted from JSON)

import 'package:database/database.dart';

// Find by UUID
final user = await DatabaseManagers.users.findByUuid('user-uuid');

// Find with conditions
final functions = await DatabaseManagers.functions.findAll(
  where: {'user_id': userId, 'status': 'active'},
  orderBy: 'created_at',
  orderDirection: 'DESC',
  limit: 10,
);

// Insert
final newFunction = await DatabaseManagers.functions.insert({
  'user_id': userId,
  'name': 'my-function',
  'status': 'active',
});

// Update
await DatabaseManagers.functions.updateById(
  functionId,
  {'status': 'inactive'},
);

// Delete
await DatabaseManagers.functions.deleteById(functionId);

// Count
final count = await DatabaseManagers.functions.count(
  where: {'status': 'active'},
);

// Complex query with joins
final query = DatabaseManagers.functions.query()
  .select(['f.*', 'u.email', 'COUNT(fd.id) as deployment_count'])
  .join('users u', 'f.user_id', 'u.id')
  .leftJoin('function_deployments fd', 'f.id', 'fd.function_id')
  .where('f.status', 'active')
  .where('f.created_at', cutoffDate, operator: '>')
  .groupBy('f.id, u.email')
  .orderBy('f.created_at', direction: 'DESC')
  .limit(20);

final results = await DatabaseManagers.functions.executeQuery(query);

// One-to-Many: Get all deployments for a function
final deployments = await DatabaseManagers.functionDeployments.hasMany(
  relatedTable: 'function_deployments',
  foreignKey: 'function_id',
  parentId: functionId,
  orderBy: 'version',
  orderDirection: 'DESC',
);

// Belongs-To: Get user for a function
final user = await DatabaseManagers.users.findById(function.userId);

// Complex join query
final results = await DatabaseManagers.functions.joinQuery(
  joinTable: 'users',
  joinCondition: 'functions.user_id = users.id',
  select: ['functions.*', 'users.email'],
  where: {'functions.status': 'active'},
);

// Using Database class
final results = await Database.rawQueryAll(
  '''
  SELECT f.*, COUNT(fd.id) as deployment_count
  FROM functions f
  LEFT JOIN function_deployments fd ON f.id = fd.function_id
  WHERE f.user_id = @user_id
  GROUP BY f.id
  HAVING COUNT(fd.id) > @min_deployments
  ''',
  parameters: {
    'user_id': userId,
    'min_deployments': 5,
  },
);

await Database.transaction((connection) async {
  // Create function
  final functionResult = await connection.execute(
    Sql.named('INSERT INTO functions ...'),
    parameters: {...},
  );

  // Create initial deployment
  await connection.execute(
    Sql.named('INSERT INTO function_deployments ...'),
    parameters: {...},
  );

  // All or nothing - automatic rollback on error
});

// Batch insert logs
final logsData = [
  {'function_id': funcId, 'level': 'info', 'message': 'Log 1'},
  {'function_id': funcId, 'level': 'error', 'message': 'Log 2'},
  {'function_id': funcId, 'level': 'warn', 'message': 'Log 3'},
];

await DatabaseManagers.functionLogs.batchInsert(logsData);

class UserEntity extends Entity {
  final int? id;
  final String? uuid;
  final String email;
  final String? passwordHash;
  final DateTime? createdAt;
  final DateTime? updatedAt;

  // Methods: toMap(), fromMap(), copyWith()
}

class FunctionEntity extends Entity {
  final int? id;
  final String? uuid;
  final int? userId;
  final String name;
  final String? status;
  final int? activeDeploymentId;
  final Map<String, dynamic>? analysisResult;
  final DateTime? createdAt;
  final DateTime? updatedAt;

  // Methods: toMap(), fromMap(), copyWith()
}

DatabaseManagers.users              // UserEntity
DatabaseManagers.functions          // FunctionEntity
DatabaseManagers.functionDeployments // FunctionDeploymentEntity
DatabaseManagers.functionLogs       // FunctionLogEntity
DatabaseManagers.functionInvocations // FunctionInvocationEntity

// ❌ Bad: No limit, SELECT *
final all = await DatabaseManagers.functions.findAll();

// ✅ Good: Limited, specific columns, indexed WHERE
final query = DatabaseManagers.functions.query()
  .select(['id', 'uuid', 'name', 'status'])
  .where('user_id', userId)  // Indexed column
  .where('status', 'active')
  .orderBy('created_at', direction: 'DESC')
  .limit(20)
  .offset(0);

// ✅ Safe: Parameters are bound
.where('email', userInput)
// Generates: WHERE email = @param_0
// Parameters: {'param_0': userInput}

// ❌ Never do this:
// .whereRaw("email = '$userInput'")  // Vulnerable!

cd dart_cloud_backend/packages/database
dart test

# With coverage
./test_runner.sh coverage

# Specific suite
dart test test/query_builder_test.dart

// Old API (still works)
final user = await QueryHelpers.getUserByUuid(uuid);

// New API (recommended)
final user = await DatabaseManagers.users.findByUuid(uuid);

Future<FunctionWithRelations?> getFunctionDetails(String uuid) async {
  final function = await DatabaseManagers.functions.findByUuid(uuid);
  if (function == null) return null;

  final user = await DatabaseManagers.users.findById(function.userId!);
  final deployments = await DatabaseManagers.functionDeployments.findAll(
    where: {'function_id': function.id},
    orderBy: 'version',
    orderDirection: 'DESC',
  );

  return FunctionWithRelations(
    function: function,
    user: user!,
    deployments: deployments,
  );
}

Future<PaginatedResult<FunctionEntity>> getFunctionsPaginated({
  required int page,
  required int pageSize,
  Map<String, dynamic>? filters,
}) async {
  final offset = (page - 1) * pageSize;

  final total = await DatabaseManagers.functions.count(where: filters);
  final items = await DatabaseManagers.functions.findAll(
    where: filters,
    orderBy: 'created_at',
    orderDirection: 'DESC',
    limit: pageSize,
    offset: offset,
  );

  return PaginatedResult(
    items: items,
    total: total,
    page: page,
    pageSize: pageSize,
    hasMore: (page * pageSize) < total,
  );
}

Future<FunctionStats> getFunctionStats(int functionId) async {
  final result = await Database.rawQuerySingle(
    '''
    SELECT
      COUNT(*) as total_invocations,
      COUNT(CASE WHEN status = 'success' THEN 1 END) as successful,
      AVG(duration_ms) as avg_duration,
      MAX(duration_ms) as max_duration
    FROM function_invocations
    WHERE function_id = @function_id
    AND timestamp >= @since
    ''',
    parameters: {
      'function_id': functionId,
      'since': DateTime.now().subtract(Duration(days: 30)),
    },
  );

  return FunctionStats.fromMap(result!);
}

// Check: Are you using the right ID type?
// ❌ Wrong: Using UUID where internal ID expected
.where('user_id', userUuid)  // userUuid is string

// ✅ Correct: Use internal ID
final user = await DatabaseManagers.users.findByUuid(userUuid);
.where('user_id', user.id)  // user.id is int

// Add indexes, use LIMIT, select specific columns
// Check EXPLAIN output for query plan

// Ensure all operations use the connection parameter
await Database.transaction((connection) async {
  // ✅ Use connection
  await connection.execute(...);

  // ❌ Don't use Database.connection directly
  // await Database.connection.execute(...);
});