Need complex queries (joins, aggregates, GROUP BY)?
├── Yes → drift
└── No
    ├── Need cross-platform including web?
    │   ├── Yes → drift or hive
    │   └── No → objectbox (fastest raw throughput) or isar
    └── Just key-value pairs?
        └── shared_preferences (tiny data) or hive (structured)

import 'package:drift/drift.dart';

part 'database.g.dart';

// 1. Define tables
class Orders extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get title => text().withLength(min: 1, max: 200)();
  RealColumn get total => real()();
  IntColumn get status => intEnum<OrderStatus>()();
  DateTimeColumn get createdAt => dateTime().withDefault(currentDateAndTime)();
  DateTimeColumn get updatedAt => dateTime().withDefault(currentDateAndTime)();
  TextColumn get syncStatus => textEnum<SyncStatus>().withDefault(
    Constant(SyncStatus.pending.name),
  )();
}

enum OrderStatus { draft, confirmed, shipped, delivered }
enum SyncStatus { pending, syncing, synced, failed }

// 2. Define the database
@DriftDatabase(tables: [Orders])
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor e) : super(e);

  @override
  int get schemaVersion => 1;

  // Type-safe query: all orders with a given status
  Future<List<Order>> ordersByStatus(OrderStatus status) {
    return (select(orders)
      ..where((o) => o.status.equalsValue(status))
      ..orderBy([(o) => OrderingTerm.desc(o.createdAt)])
    ).get();
  }

  // Reactive stream: emits new list whenever the table changes
  Stream<List<Order>> watchPendingSync() {
    return (select(orders)
      ..where((o) => o.syncStatus.equalsValue(SyncStatus.pending))
    ).watch();
  }

  // Upsert with sync tracking
  Future<void> upsertOrder(OrdersCompanion entry) {
    return into(orders).insertOnConflictUpdate(entry);
  }
}

class OrderRepository {
  final AppDatabase _db;
  final OrderApi _api;
  final ConnectivityService _connectivity;

  OrderRepository(this._db, this._api, this._connectivity);

  // Always returns local data immediately
  Stream<List<Order>> watchOrders() => _db.watchAllOrders();

  // Background refresh — does not block callers
  Future<void> refreshFromServer() async {
    if (!await _connectivity.isOnline) return;
    try {
      final remote = await _api.fetchOrders();
      for (final order in remote) {
        await _db.upsertOrder(order.toCompanion());
      }
    } catch (e) {
      // Log but do not throw — local data is still valid
      debugPrint('Background refresh failed: $e');
    }
  }
}

Future<void> createOrder(Order order) async {
  // 1. Write locally with pending sync status
  await _db.upsertOrder(
    OrdersCompanion(
      id: Value(order.id),
      title: Value(order.title),
      total: Value(order.total),
      status: Value(order.status),
      syncStatus: const Value(SyncStatus.pending),
    ),
  );

  // 2. Enqueue for background sync
  await _syncQueue.enqueue(SyncOperation(
    type: SyncOpType.create,
    table: 'orders',
    recordId: order.id.toString(),
    payload: order.toJson(),
  ));
}

import 'package:connectivity_plus/connectivity_plus.dart';

class ConnectivityService {
  final Connectivity _connectivity = Connectivity();

  Stream<bool> get onlineStream =>
      _connectivity.onConnectivityChanged.map(
        (results) => results.any((r) => r != ConnectivityResult.none),
      );

  Future<bool> get isOnline async {
    final results = await _connectivity.checkConnectivity();
    return results.any((r) => r != ConnectivityResult.none);
  }
}

// In your sync service initialization
connectivityService.onlineStream.listen((online) {
  if (online) {
    syncEngine.processPendingQueue();
  }
});

class SyncQueue {
  final AppDatabase _db;

  Future<void> enqueue(SyncOperation op) async {
    await _db.insertSyncOperation(SyncOperationsCompanion(
      id: Value(const Uuid().v4()),
      type: Value(op.type.name),
      table: Value(op.table),
      recordId: Value(op.recordId),
      payload: Value(jsonEncode(op.payload)),
      createdAt: Value(DateTime.now()),
      retryCount: const Value(0),
      status: const Value('pending'),
    ));
  }

  Future<List<SyncOperation>> pendingOperations() {
    return _db.getPendingSyncOps();
  }

  Future<void> markCompleted(String opId) {
    return _db.updateSyncOpStatus(opId, 'completed');
  }

  Future<void> markFailed(String opId, String error) {
    return _db.updateSyncOpFailed(opId, error);
  }
}

@riverpod
class OrderList extends _$OrderList {
  @override
  Future<List<Order>> build() async {
    final db = ref.read(databaseProvider);
    return db.watchAllOrders().first;
  }

  Future<void> updateOrderTitle(int orderId, String newTitle) async {
    final previousState = state.valueOrNull ?? [];

    // 1. Optimistically update local state
    state = AsyncData(
      previousState.map((o) {
        return o.id == orderId ? o.copyWith(title: newTitle) : o;
      }).toList(),
    );

    try {
      // 2. Persist locally
      await ref.read(databaseProvider).updateOrderTitle(orderId, newTitle);

      // 3. Attempt server sync
      await ref.read(orderApiProvider).updateOrder(orderId, title: newTitle);
    } catch (e) {
      // 4. Rollback on failure
      state = AsyncData(previousState);
      ref.read(snackbarProvider).show('Update failed. Change reverted.');
    }
  }
}

class OrderBloc extends Bloc<OrderEvent, OrderState> {
  final OrderRepository _repo;

  OrderBloc(this._repo) : super(const OrderState.initial()) {
    on<UpdateOrderTitle>(_onUpdateTitle);
  }

  Future<void> _onUpdateTitle(
    UpdateOrderTitle event,
    Emitter<OrderState> emit,
  ) async {
    final previous = state.orders;

    // Optimistic update
    final updated = state.orders.map((o) {
      return o.id == event.orderId ? o.copyWith(title: event.newTitle) : o;
    }).toList();
    emit(state.copyWith(orders: updated));

    try {
      await _repo.updateOrderTitle(event.orderId, event.newTitle);
    } catch (e) {
      // Rollback
      emit(state.copyWith(
        orders: previous,
        error: 'Failed to update order. Reverted.',
      ));
    }
  }
}

class ConflictResolver {
  /// Returns the winning version based on timestamp comparison.
  Order resolveOrder(Order local, Order remote) {
    if (local.updatedAt.isAfter(remote.updatedAt)) {
      return local;
    }
    return remote;
  }
}

class FieldLevelMerger {
  /// Merges local and remote orders against a common ancestor (base).
  /// Fields changed only on one side take that value.
  /// Fields changed on both sides use the remote value (server-wins for conflicts).
  Order mergeOrder(Order base, Order local, Order remote) {
    return Order(
      id: base.id,
      title: local.title != base.title && remote.title == base.title
          ? local.title
          : remote.title,
      total: local.total != base.total && remote.total == base.total
          ? local.total
          : remote.total,
      status: local.status != base.status && remote.status == base.status
          ? local.status
          : remote.status,
      updatedAt: remote.updatedAt,
    );
  }
}

class VectorClock {
  final Map<String, int> _clock;

  VectorClock([Map<String, int>? initial]) : _clock = Map.from(initial ?? {});

  void increment(String nodeId) {
    _clock[nodeId] = (_clock[nodeId] ?? 0) + 1;
  }

  /// Returns true if this clock is causally before [other].
  bool isBefore(VectorClock other) {
    for (final key in _clock.keys) {
      if ((_clock[key] ?? 0) > (other._clock[key] ?? 0)) return false;
    }
    return _clock.entries.any(
      (e) => e.value < (other._clock[e.key] ?? 0),
    );
  }

  /// Returns true if neither clock is before the other — a true conflict.
  bool isConcurrentWith(VectorClock other) {
    return !isBefore(other) && !other.isBefore(this);
  }

  VectorClock merge(VectorClock other) {
    final merged = Map<String, int>.from(_clock);
    for (final entry in other._clock.entries) {
      merged[entry.key] = max(merged[entry.key] ?? 0, entry.value);
    }
    return VectorClock(merged);
  }
}

class SyncEngine {
  final AppDatabase _db;
  final OrderApi _api;
  final SyncQueue _queue;

  SyncEngine(this._db, this._api, this._queue);

  /// Pull: fetch changes from server since last sync
  Future<void> pullChanges() async {
    final cursor = await _db.getLastSyncCursor('orders');

    var page = 0;
    bool hasMore = true;

    while (hasMore) {
      final response = await _api.fetchOrdersSince(
        since: cursor,
        page: page,
        pageSize: 100,
      );

      for (final remote in response.orders) {
        final local = await _db.getOrderById(remote.id);
        if (local == null) {
          await _db.upsertOrder(remote.toCompanion(synced: true));
        } else {
          final resolved = _conflictResolver.resolveOrder(local, remote);
          await _db.upsertOrder(resolved.toCompanion(synced: true));
        }
      }

      hasMore = response.hasMore;
      page++;
    }

    await _db.updateSyncCursor('orders', DateTime.now());
  }

  /// Push: send pending local changes to server
  Future<void> pushChanges() async {
    final pending = await _queue.pendingOperations();

    for (final op in pending) {
      try {
        await _queue.markStatus(op.id, 'syncing');
        await _api.sendOperation(op);
        await _queue.markCompleted(op.id);
        await _db.updateSyncStatus(op.recordId, SyncStatus.synced);
      } catch (e) {
        await _queue.markFailed(op.id, e.toString());
        await _retryScheduler.scheduleRetry(op);
      }
    }
  }

  /// Full sync cycle: push first, then pull
  Future<void> sync() async {
    await pushChanges();  // push first to reduce conflicts
    await pullChanges();
  }
}

// Client: include a client-generated idempotency key
Future<void> sendOperation(SyncOperation op) async {
  await _dio.post(
    '/api/orders',
    data: op.payload,
    options: Options(headers: {
      'Idempotency-Key': op.id,  // UUID generated at enqueue time
    }),
  );
}

enum SyncStatus { pending, syncing, synced, failed }

// In the UI
Widget buildOrderTile(Order order) {
  return ListTile(
    title: Text(order.title),
    trailing: switch (order.syncStatus) {
      SyncStatus.pending => const Icon(Icons.cloud_queue, color: Colors.orange),
      SyncStatus.syncing => const SizedBox(
          width: 16, height: 16,
          child: CircularProgressIndicator(strokeWidth: 2),
        ),
      SyncStatus.synced  => const Icon(Icons.cloud_done, color: Colors.green),
      SyncStatus.failed  => const Icon(Icons.cloud_off, color: Colors.red),
    },
  );
}

import 'package:workmanager/workmanager.dart';

const syncTaskName = 'com.app.backgroundSync';

@pragma('vm:entry-point')
void callbackDispatcher() {
  Workmanager().executeTask((task, inputData) async {
    // This runs in a separate Isolate — you must re-initialize dependencies
    final db = await openDatabase();
    final api = OrderApi(Dio());
    final queue = SyncQueue(db);
    final engine = SyncEngine(db, api, queue);

    try {
      await engine.sync();
      return true;  // success
    } catch (e) {
      debugPrint('Background sync failed: $e');
      return false; // retry according to backoff policy
    }
  });
}

Future<void> initBackgroundSync() async {
  await Workmanager().initialize(callbackDispatcher, isInDebugMode: false);
  await Workmanager().registerPeriodicTask(
    'sync-periodic',
    syncTaskName,
    frequency: const Duration(minutes: 15),
    constraints: Constraints(
      networkType: NetworkType.connected,
      requiresBatteryNotLow: true,
    ),
    backoffPolicy: BackoffPolicy.exponential,
    backoffPolicyDelay: const Duration(minutes: 1),
  );
}

Future<void> pullChangesTransactional(List<Order> batch) async {
  await _db.transaction(() async {
    for (final order in batch) {
      await _db.upsertOrder(order.toCompanion());
    }
    await _db.updateSyncCursor('orders', batch.last.updatedAt);
  });
}

class RetryScheduler {
  static const _maxRetries = 8;
  static const _baseDelay = Duration(seconds: 1);

  Duration delayForAttempt(int attempt) {
    final exponential = _baseDelay * pow(2, attempt).toInt();
    final capped = exponential > const Duration(minutes: 5)
        ? const Duration(minutes: 5)
        : exponential;
    // Add jitter: +/- 25%
    final jitter = capped * (0.75 + Random().nextDouble() * 0.5);
    return jitter;
  }

  Future<void> scheduleRetry(SyncOperation op) async {
    if (op.retryCount >= _maxRetries) {
      await _deadLetterQueue.add(op);
      return;
    }
    final delay = delayForAttempt(op.retryCount);
    Future.delayed(delay, () => _syncEngine.retrySingleOp(op));
  }
}

class DeadLetterQueue {
  final AppDatabase _db;

  Future<void> add(SyncOperation op) async {
    await _db.insertDeadLetter(DeadLetterCompanion(
      operationId: Value(op.id),
      table: Value(op.table),
      recordId: Value(op.recordId),
      payload: Value(jsonEncode(op.payload)),
      error: Value(op.lastError ?? 'Max retries exceeded'),
      failedAt: Value(DateTime.now()),
    ));
    await _db.deleteSyncOperation(op.id);
  }

  Stream<List<DeadLetter>> watchFailed() => _db.watchDeadLetters();
}

Future<void> showConflictDialog(
  BuildContext context,
  Order localVersion,
  Order serverVersion,
) async {
  final choice = await showDialog<ConflictChoice>(
    context: context,
    barrierDismissible: false,
    builder: (_) => AlertDialog(
      title: const Text('Sync Conflict'),
      content: Column(
        mainAxisSize: MainAxisSize.min,
        children: [
          const Text('This order was modified on another device.'),
          const SizedBox(height: 16),
          _buildComparisonTable(localVersion, serverVersion),
        ],
      ),
      actions: [
        TextButton(
          onPressed: () => Navigator.pop(context, ConflictChoice.keepLocal),
          child: const Text('Keep My Version'),
        ),
        TextButton(
          onPressed: () => Navigator.pop(context, ConflictChoice.keepRemote),
          child: const Text('Use Server Version'),
        ),
      ],
    ),
  );

  if (choice == ConflictChoice.keepLocal) {
    await _forceUpload(localVersion);
  } else {
    await _db.upsertOrder(serverVersion.toCompanion());
  }
}

@DriftDatabase(tables: [Orders, SyncOperations])
class AppDatabase extends _$AppDatabase {
  AppDatabase(QueryExecutor e) : super(e);

  @override
  int get schemaVersion => 3;

  @override
  MigrationStrategy get migration => MigrationStrategy(
    onCreate: (m) => m.createAll(),
    onUpgrade: (m, from, to) async {
      if (from < 2) {
        // v1 → v2: add syncStatus column
        await m.addColumn(orders, orders.syncStatus);
      }
      if (from < 3) {
        // v2 → v3: add index on updatedAt for sync queries
        await customStatement(
          'CREATE INDEX idx_orders_updated ON orders(updated_at)',
        );
      }
    },
    beforeOpen: (details) async {
      // Enable WAL mode for better concurrent read/write
      await customStatement('PRAGMA journal_mode=WAL');
      await customStatement('PRAGMA foreign_keys=ON');
    },
  );
}

onUpgrade: (m, from, to) async {
  try {
    if (from < 2) {
      await m.addColumn(orders, orders.syncStatus);
    }
  } catch (e) {
    // Log the failure and report to crash analytics
    debugPrint('Migration from v$from to v$to failed: $e');

    // Fallback: recreate the database (last resort — loses data)
    // Only do this if the data can be re-synced from the server
    await m.createAll();
  }
},

class MockConnectivityService implements ConnectivityService {
  bool _online;
  final _controller = StreamController<bool>.broadcast();

  MockConnectivityService({bool online = true}) : _online = online;

  @override
  Stream<bool> get onlineStream => _controller.stream;

  @override
  Future<bool> get isOnline async => _online;

  void setOnline(bool value) {
    _online = value;
    _controller.add(value);
  }
}

test('enqueues writes when offline', () async {
  final connectivity = MockConnectivityService(online: false);
  final db = await openTestDatabase();
  final repo = OrderRepository(db, MockOrderApi(), connectivity);

  await repo.createOrder(testOrder);

  final pending = await db.getPendingSyncOps();
  expect(pending, hasLength(1));
  expect(pending.first.recordId, testOrder.id.toString());
});

class MockOrderApi implements OrderApi {
  final List<Order> _serverOrders = [];
  bool shouldFail = false;

  @override
  Future<SyncResponse> fetchOrdersSince({DateTime? since, int page = 0, int pageSize = 100}) async {
    if (shouldFail) throw DioException(requestOptions: RequestOptions());
    final filtered = since != null
        ? _serverOrders.where((o) => o.updatedAt.isAfter(since)).toList()
        : _serverOrders;
    return SyncResponse(orders: filtered, hasMore: false);
  }

  @override
  Future<void> sendOperation(SyncOperation op) async {
    if (shouldFail) throw DioException(requestOptions: RequestOptions());
    _serverOrders.add(Order.fromJson(op.payload));
  }

  void seedServerData(List<Order> orders) => _serverOrders.addAll(orders);
}

test('sync engine pulls and merges remote changes', () async {
  final db = await openTestDatabase();
  final api = MockOrderApi();
  final engine = SyncEngine(db, api, SyncQueue(db));

  // Seed server with an order
  api.seedServerData([Order(id: 1, title: 'Remote Order', updatedAt: DateTime.now())]);

  await engine.pullChanges();

  final local = await db.getOrderById(1);
  expect(local, isNotNull);
  expect(local!.title, 'Remote Order');
});

test('field-level merge preserves non-conflicting changes', () {
  final base   = Order(id: 1, title: 'Original',  total: 100.0, status: OrderStatus.draft);
  final local  = Order(id: 1, title: 'Local Edit', total: 100.0, status: OrderStatus.draft);
  final remote = Order(id: 1, title: 'Original',   total: 150.0, status: OrderStatus.confirmed);

  final merger = FieldLevelMerger();
  final result = merger.mergeOrder(base, local, remote);

  expect(result.title, 'Local Edit');         // changed locally only
  expect(result.total, 150.0);                // changed remotely only
  expect(result.status, OrderStatus.confirmed); // changed remotely only
});

import 'package:drift/native.dart';

AppDatabase openTestDatabase() {
  return AppDatabase(NativeDatabase.memory());
}

test('full offline cycle: create, go offline, queue, go online, sync', () async {
  final db = openTestDatabase();
  final api = MockOrderApi();
  final connectivity = MockConnectivityService(online: true);
  final queue = SyncQueue(db);
  final repo = OrderRepository(db, api, connectivity);
  final engine = SyncEngine(db, api, queue);

  // Create an order while online
  await repo.createOrder(Order(id: 1, title: 'Test', total: 50.0));
  expect(await queue.pendingOperations(), hasLength(1));

  // Sync pushes it to the server
  await engine.pushChanges();
  expect(await queue.pendingOperations(), isEmpty);

  // Go offline, create another order
  connectivity.setOnline(false);
  await repo.createOrder(Order(id: 2, title: 'Offline Order', total: 75.0));
  expect(await queue.pendingOperations(), hasLength(1));

  // Come back online, sync again
  connectivity.setOnline(true);
  await engine.pushChanges();
  expect(await queue.pendingOperations(), isEmpty);
});

// Anti-pattern: clearing everything
Future<void> clearCache() async {
  await _db.deleteAll();  // Destroys unsynced user data
}

// Correct: only clear synced data
Future<void> clearSyncedData() async {
  await (_db.delete(_db.orders)
    ..where((o) => o.syncStatus.equalsValue(SyncStatus.synced))
  ).go();
}

// Anti-pattern: blocking the UI on a network check
Future<Order?> getOrder(int id) async {
  if (await isOnline()) {          // blocks, flaky, slow
    return await api.fetchOrder(id);
  }
  return await db.getOrder(id);
}

// Correct: always read local, sync in background
Stream<Order?> watchOrder(int id) => db.watchOrderById(id);

// Process in batches, not all at once
Future<void> pushChanges({int batchSize = 50}) async {
  while (true) {
    final batch = await _queue.pendingOperations(limit: batchSize);
    if (batch.isEmpty) break;
    for (final op in batch) {
      await _processSingleOp(op);
    }
  }
}

// Anti-pattern: retry the whole batch
Future<void> syncBatch(List<SyncOperation> ops) async {
  try {
    await api.sendBatch(ops);         // if this fails at op 51...
    await markAllCompleted(ops);      // ...none get marked
  } catch (e) {
    await markAllFailed(ops);         // ops 1-50 already succeeded on server
  }
}

// Correct: track per-operation
Future<void> syncBatch(List<SyncOperation> ops) async {
  for (final op in ops) {
    try {
      await api.sendOperation(op);
      await _queue.markCompleted(op.id);
    } catch (e) {
      await _queue.markFailed(op.id, e.toString());
    }
  }
}