lib/core/storage/local_storage.dartDart
import 'package:flutter/foundation.dart';
import 'package:path/path.dart' as path;
import 'package:shared_preferences/shared_preferences.dart';
import 'package:sqflite/sqflite.dart';
class LocalStorage {
LocalStorage._();
static final instance = LocalStorage._();
SharedPreferences? _preferences;
Database? _database;
bool get usesSqlite => _database != null;
/// True on the mobile platforms where the production local database exists.
/// Web deliberately keeps using browser storage until its IndexedDB migration.
bool get usesRelationalStore => _database != null;
Future<void> initialize({bool forcePreferences = false}) async {
_preferences = await SharedPreferences.getInstance();
final mobile =
!forcePreferences &&
!kIsWeb &&
(defaultTargetPlatform == TargetPlatform.android ||
defaultTargetPlatform == TargetPlatform.iOS);
if (!mobile) return;
final databasePath = await getDatabasesPath();
_database = await openDatabase(
path.join(databasePath, 'machweis.db'),
version: 4,
onCreate: (database, _) async {
await _createV1(database);
await _createV2(database);
await _createV3(database);
await _createV4(database);
},
onUpgrade: (database, oldVersion, _) async {
if (oldVersion < 2) await _createV2(database);
if (oldVersion < 3) await _createV3(database);
if (oldVersion < 4) await _createV4(database);
},
);
}
Future<void> _createV1(Database database) => database.execute(
'CREATE TABLE local_values (storage_key TEXT PRIMARY KEY, storage_value TEXT NOT NULL, updated_at TEXT NOT NULL)',
);
Future<void> _createV2(Database database) async {
await database.execute(
'CREATE TABLE IF NOT EXISTS customers (id TEXT PRIMARY KEY, name TEXT NOT NULL, contact TEXT NOT NULL, email TEXT NOT NULL, phone TEXT NOT NULL, address TEXT NOT NULL)',
);
await database.execute(
'CREATE TABLE IF NOT EXISTS sites (id TEXT PRIMARY KEY, customer_id TEXT, name TEXT NOT NULL, address TEXT NOT NULL)',
);
await database.execute(
'CREATE TABLE IF NOT EXISTS jobs (id TEXT PRIMARY KEY, number TEXT NOT NULL, customer_id TEXT, site_id TEXT, customer_name TEXT NOT NULL, site_name TEXT NOT NULL, address TEXT NOT NULL)',
);
await database.execute(
'CREATE TABLE IF NOT EXISTS reports (id TEXT PRIMARY KEY, customer_id TEXT, site_id TEXT, job_id TEXT, payload TEXT NOT NULL)',
);
await database.execute(
'CREATE TABLE IF NOT EXISTS report_audit (id TEXT PRIMARY KEY, report_id TEXT NOT NULL, timestamp TEXT NOT NULL, payload TEXT NOT NULL)',
);
await database.execute(
'CREATE TABLE IF NOT EXISTS report_photos (id TEXT PRIMARY KEY, report_id TEXT NOT NULL, file_name TEXT NOT NULL, mime_type TEXT NOT NULL, created_at TEXT NOT NULL)',
);
await database.execute(
'CREATE TABLE IF NOT EXISTS report_photo_blobs (photo_id TEXT PRIMARY KEY, bytes BLOB NOT NULL, FOREIGN KEY(photo_id) REFERENCES report_photos(id) ON DELETE CASCADE)',
);
}
Future<void> _createV3(Database database) async {
for (final definition in const {
'customers': [
'tenant_id TEXT',
'row_version INTEGER NOT NULL DEFAULT 0',
"sync_status TEXT NOT NULL DEFAULT 'local_only'",
'sync_error TEXT',
'deleted_at TEXT',
"updated_at TEXT NOT NULL DEFAULT ''",
],
'sites': [
'tenant_id TEXT',
'row_version INTEGER NOT NULL DEFAULT 0',
"sync_status TEXT NOT NULL DEFAULT 'local_only'",
'sync_error TEXT',
'deleted_at TEXT',
"updated_at TEXT NOT NULL DEFAULT ''",
],
'jobs': [
'tenant_id TEXT',
"title TEXT NOT NULL DEFAULT ''",
"description TEXT NOT NULL DEFAULT ''",
"status TEXT NOT NULL DEFAULT 'open'",
'row_version INTEGER NOT NULL DEFAULT 0',
"sync_status TEXT NOT NULL DEFAULT 'local_only'",
'sync_error TEXT',
'deleted_at TEXT',
"updated_at TEXT NOT NULL DEFAULT ''",
],
}.entries) {
for (final column in definition.value) {
await _addColumn(database, definition.key, column);
}
}
await database.execute(
"CREATE TABLE IF NOT EXISTS employee_profiles (id TEXT PRIMARY KEY, tenant_id TEXT, employee_number TEXT NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL, phone TEXT NOT NULL, role TEXT NOT NULL DEFAULT 'technician', is_active INTEGER NOT NULL DEFAULT 1, row_version INTEGER NOT NULL DEFAULT 0, sync_status TEXT NOT NULL DEFAULT 'local_only', sync_error TEXT, deleted_at TEXT, updated_at TEXT NOT NULL)",
);
await database.execute(
"CREATE TABLE IF NOT EXISTS job_assignments (id TEXT PRIMARY KEY, tenant_id TEXT, job_id TEXT NOT NULL, employee_id TEXT NOT NULL, assigned_from TEXT, assigned_until TEXT, row_version INTEGER NOT NULL DEFAULT 0, sync_status TEXT NOT NULL DEFAULT 'local_only', sync_error TEXT, deleted_at TEXT, updated_at TEXT NOT NULL)",
);
await database.execute(
"CREATE TABLE IF NOT EXISTS master_data_outbox (operation_id TEXT PRIMARY KEY, tenant_id TEXT NOT NULL, idempotency_key TEXT NOT NULL UNIQUE, entity_type TEXT NOT NULL, entity_id TEXT NOT NULL, action TEXT NOT NULL, base_revision INTEGER NOT NULL, payload TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'pending', attempts INTEGER NOT NULL DEFAULT 0, last_error TEXT, next_attempt_at TEXT, created_at TEXT NOT NULL, UNIQUE(tenant_id, entity_type, entity_id, action))",
);
await database.execute(
'CREATE INDEX IF NOT EXISTS master_data_outbox_due ON master_data_outbox(state, next_attempt_at, created_at)',
);
await database.execute(
"CREATE TABLE IF NOT EXISTS master_data_sync_state (tenant_id TEXT PRIMARY KEY, cursor INTEGER NOT NULL DEFAULT 0, initial_decision TEXT, last_sync_at TEXT, last_error TEXT)",
);
}
Future<void> _createV4(Database database) async {
for (final table in const ['reports', 'report_audit', 'report_photos']) {
await _addColumn(database, table, 'tenant_id TEXT');
}
}
Future<void> _addColumn(
Database database,
String table,
String definition,
) async {
final name = definition.split(' ').first;
final columns = await database.rawQuery('PRAGMA table_info($table)');
if (columns.any((column) => column['name'] == name)) return;
await database.execute('ALTER TABLE $table ADD COLUMN $definition');
}
Future<T> mobileTransaction<T>(
Future<T> Function(DatabaseExecutor database) action,
) async {
final database = _database;
if (database == null) {
throw UnsupportedError('Relational storage is only available on mobile.');
}
return database.transaction(action);
}
Future<List<Map<String, Object?>>> mobileQuery(
String table, {
String? where,
List<Object?>? whereArgs,
String? orderBy,
}) async {
final database = _database;
if (database == null) return const [];
return database.query(
table,
where: where,
whereArgs: whereArgs,
orderBy: orderBy,
);
}
Future<List<Map<String, Object?>>> rows(String table) async {
final database = _database;
if (database == null) return const [];
return database.query(table);
}
Future<void> replaceRow(String table, Map<String, Object?> values) async {
final database = _database;
if (database == null) return;
await database.insert(
table,
values,
conflictAlgorithm: ConflictAlgorithm.replace,
);
}
Future<void> deleteRow(String table, String id) async {
final database = _database;
if (database == null) return;
await database.delete(table, where: 'id = ?', whereArgs: [id]);
}
Future<void> deleteRows(
String table, {
required String where,
required List<Object?> whereArgs,
}) async {
final database = _database;
if (database == null) return;
await database.delete(table, where: where, whereArgs: whereArgs);
}
Future<void> replacePhotoBytes(String photoId, Uint8List bytes) async {
final database = _database;
if (database == null) return;
await database.insert('report_photo_blobs', {
'photo_id': photoId,
'bytes': bytes,
}, conflictAlgorithm: ConflictAlgorithm.replace);
}
Future<Uint8List?> photoBytes(String photoId) async {
final database = _database;
if (database == null) return null;
final rows = await database.query(
'report_photo_blobs',
columns: ['bytes'],
where: 'photo_id = ?',
whereArgs: [photoId],
limit: 1,
);
if (rows.isEmpty) return null;
return rows.first['bytes'] as Uint8List;
}
Future<String?> getString(String key) async {
final database = _database;
if (database == null) return _preferences?.getString(key);
final rows = await database.query(
'local_values',
columns: ['storage_value'],
where: 'storage_key = ?',
whereArgs: [key],
limit: 1,
);
if (rows.isNotEmpty) return rows.first['storage_value'] as String;
final legacyValue = _preferences?.getString(key);
if (legacyValue != null) await setString(key, legacyValue);
return legacyValue;
}
Future<void> setString(String key, String value) async {
final database = _database;
if (database == null) {
await _preferences?.setString(key, value);
return;
}
await database.insert('local_values', {
'storage_key': key,
'storage_value': value,
'updated_at': DateTime.now().toUtc().toIso8601String(),
}, conflictAlgorithm: ConflictAlgorithm.replace);
}
Future<void> remove(String key) async {
final database = _database;
if (database != null) {
await database.delete(
'local_values',
where: 'storage_key = ?',
whereArgs: [key],
);
}
await _preferences?.remove(key);
}
}