perf(mobile): optimized album sorting

mobile/lib/domain/services/remote_album.service.dart

@@ -171,16 +171,8 @@ class RemoteAlbumService {
 
   Future<List<RemoteAlbum>> _sortByNewestAsset(List<RemoteAlbum> albums) async {
     // map album IDs to their newest asset dates
-    final Map<String, Future<DateTime?>> assetTimestampFutures = {};
-    for (final album in albums) {
-      assetTimestampFutures[album.id] = _repository.getNewestAssetTimestamp(album.id);
-    }
-
-    // await all database queries
-    final entries = await Future.wait(
-      assetTimestampFutures.entries.map((entry) async => MapEntry(entry.key, await entry.value)),
-    );
-    final assetTimestamps = Map.fromEntries(entries);
+    final albumIds = albums.map((e) => e.id).toList();
+    final assetTimestamps = await _repository.getNewestAssetTimestampForAlbums(albumIds);
 
     final sorted = albums.sorted((a, b) {
       final aDate = assetTimestamps[a.id] ?? DateTime.fromMillisecondsSinceEpoch(0);
@@ -193,15 +185,8 @@ class RemoteAlbumService {
 
   Future<List<RemoteAlbum>> _sortByOldestAsset(List<RemoteAlbum> albums) async {
     // map album IDs to their oldest asset dates
-    final Map<String, Future<DateTime?>> assetTimestampFutures = {
-      for (final album in albums) album.id: _repository.getOldestAssetTimestamp(album.id),
-    };
-
-    // await all database queries
-    final entries = await Future.wait(
-      assetTimestampFutures.entries.map((entry) async => MapEntry(entry.key, await entry.value)),
-    );
-    final assetTimestamps = Map.fromEntries(entries);
+    final albumIds = albums.map((e) => e.id).toList();
+    final assetTimestamps = await _repository.getOldestAssetTimestampForAlbums(albumIds);
 
     final sorted = albums.sorted((a, b) {
       final aDate = assetTimestamps[a.id] ?? DateTime.fromMillisecondsSinceEpoch(0);

mobile/lib/infrastructure/repositories/remote_album.repository.dart

@@ -321,26 +321,64 @@ class DriftRemoteAlbumRepository extends DriftDatabaseRepository {
     }).watchSingleOrNull();
   }
 
-  Future<DateTime?> getNewestAssetTimestamp(String albumId) {
-    final query = _db.remoteAlbumAssetEntity.selectOnly()
-      ..where(_db.remoteAlbumAssetEntity.albumId.equals(albumId))
-      ..addColumns([_db.remoteAssetEntity.localDateTime.max()])
-      ..join([
-        innerJoin(_db.remoteAssetEntity, _db.remoteAssetEntity.id.equalsExp(_db.remoteAlbumAssetEntity.assetId)),
-      ]);
+  Future<Map<String, DateTime?>> getNewestAssetTimestampForAlbums(List<String> albumIds) async {
+    if (albumIds.isEmpty) {
+      return {};
+    }
 
-    return query.map((row) => row.read(_db.remoteAssetEntity.localDateTime.max())).getSingleOrNull();
+    final results = <String, DateTime?>{};
+
+    // Chunk calls to avoid SQLite limit (default 999 variables)
+    const chunkSize = 900;
+    for (var i = 0; i < albumIds.length; i += chunkSize) {
+      final end = (i + chunkSize < albumIds.length) ? i + chunkSize : albumIds.length;
+      final subList = albumIds.sublist(i, end);
+
+      final query = _db.remoteAlbumAssetEntity.selectOnly()
+        ..where(_db.remoteAlbumAssetEntity.albumId.isIn(subList))
+        ..addColumns([_db.remoteAlbumAssetEntity.albumId, _db.remoteAssetEntity.localDateTime.max()])
+        ..join([
+          innerJoin(_db.remoteAssetEntity, _db.remoteAssetEntity.id.equalsExp(_db.remoteAlbumAssetEntity.assetId)),
+        ])
+        ..groupBy([_db.remoteAlbumAssetEntity.albumId]);
+
+      final rows = await query.get();
+      for (final row in rows) {
+        results[row.read(_db.remoteAlbumAssetEntity.albumId)!] = row.read(_db.remoteAssetEntity.localDateTime.max());
+      }
+    }
+
+    return results;
   }
 
-  Future<DateTime?> getOldestAssetTimestamp(String albumId) {
-    final query = _db.remoteAlbumAssetEntity.selectOnly()
-      ..where(_db.remoteAlbumAssetEntity.albumId.equals(albumId))
-      ..addColumns([_db.remoteAssetEntity.localDateTime.min()])
-      ..join([
-        innerJoin(_db.remoteAssetEntity, _db.remoteAssetEntity.id.equalsExp(_db.remoteAlbumAssetEntity.assetId)),
-      ]);
+  Future<Map<String, DateTime?>> getOldestAssetTimestampForAlbums(List<String> albumIds) async {
+    if (albumIds.isEmpty) {
+      return {};
+    }
 
-    return query.map((row) => row.read(_db.remoteAssetEntity.localDateTime.min())).getSingleOrNull();
+    final results = <String, DateTime?>{};
+
+    // Chunk calls to avoid SQLite limit (default 999 variables)
+    const chunkSize = 900;
+    for (var i = 0; i < albumIds.length; i += chunkSize) {
+      final end = (i + chunkSize < albumIds.length) ? i + chunkSize : albumIds.length;
+      final subList = albumIds.sublist(i, end);
+
+      final query = _db.remoteAlbumAssetEntity.selectOnly()
+        ..where(_db.remoteAlbumAssetEntity.albumId.isIn(subList))
+        ..addColumns([_db.remoteAlbumAssetEntity.albumId, _db.remoteAssetEntity.localDateTime.min()])
+        ..join([
+          innerJoin(_db.remoteAssetEntity, _db.remoteAssetEntity.id.equalsExp(_db.remoteAlbumAssetEntity.assetId)),
+        ])
+        ..groupBy([_db.remoteAlbumAssetEntity.albumId]);
+
+      final rows = await query.get();
+      for (final row in rows) {
+        results[row.read(_db.remoteAlbumAssetEntity.albumId)!] = row.read(_db.remoteAssetEntity.localDateTime.min());
+      }
+    }
+
+    return results;
   }
 
   Future<int> getCount() {

mobile/test/domain/services/album.service_test.dart

@@ -18,30 +18,34 @@ void main() {
     mockAlbumApiRepo = MockDriftAlbumApiRepository();
     sut = RemoteAlbumService(mockRemoteAlbumRepo, mockAlbumApiRepo);
 
-    when(() => mockRemoteAlbumRepo.getNewestAssetTimestamp(any())).thenAnswer((invocation) {
-      // Simulate a timestamp for the newest asset in the album
-      final albumID = invocation.positionalArguments[0] as String;
-
-      if (albumID == '1') {
-        return Future.value(DateTime(2023, 1, 1));
-      } else if (albumID == '2') {
-        return Future.value(DateTime(2023, 2, 1));
+    when(() => mockRemoteAlbumRepo.getNewestAssetTimestampForAlbums(any())).thenAnswer((invocation) async {
+      final albumIds = invocation.positionalArguments[0] as List<String>;
+      final result = <String, DateTime?>{};
+      for (final id in albumIds) {
+        if (id == '1') {
+          result[id] = DateTime(2023, 1, 1);
+        } else if (id == '2') {
+          result[id] = DateTime(2023, 2, 1);
+        } else {
+          result[id] = DateTime.fromMillisecondsSinceEpoch(0);
+        }
       }
-
-      return Future.value(DateTime.fromMillisecondsSinceEpoch(0));
+      return result;
     });
 
-    when(() => mockRemoteAlbumRepo.getOldestAssetTimestamp(any())).thenAnswer((invocation) {
-      // Simulate a timestamp for the oldest asset in the album
-      final albumID = invocation.positionalArguments[0] as String;
-
-      if (albumID == '1') {
-        return Future.value(DateTime(2019, 1, 1));
-      } else if (albumID == '2') {
-        return Future.value(DateTime(2019, 2, 1));
+    when(() => mockRemoteAlbumRepo.getOldestAssetTimestampForAlbums(any())).thenAnswer((invocation) async {
+      final albumIds = invocation.positionalArguments[0] as List<String>;
+      final result = <String, DateTime?>{};
+      for (final id in albumIds) {
+        if (id == '1') {
+          result[id] = DateTime(2019, 1, 1);
+        } else if (id == '2') {
+          result[id] = DateTime(2019, 2, 1);
+        } else {
+          result[id] = DateTime.fromMillisecondsSinceEpoch(0);
+        }
       }
-
-      return Future.value(DateTime.fromMillisecondsSinceEpoch(0));
+      return result;
     });
   });