mirror of
https://github.com/vleeuwenmenno/supplements.git
synced 2025-09-11 18:29:12 +02:00
760 lines
24 KiB
Dart
760 lines
24 KiB
Dart
import 'dart:convert';
|
|
import 'dart:io';
|
|
|
|
import 'package:path/path.dart';
|
|
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
|
|
|
|
import '../models/supplement.dart';
|
|
import '../models/supplement_intake.dart';
|
|
import 'database_sync_service.dart';
|
|
|
|
class DatabaseHelper {
|
|
static const _databaseName = 'supplements.db';
|
|
static const _databaseVersion = 6; // Increment version for sync support
|
|
|
|
static const supplementsTable = 'supplements';
|
|
static const intakesTable = 'supplement_intakes';
|
|
static const notificationTrackingTable = 'notification_tracking';
|
|
static const syncMetadataTable = 'sync_metadata';
|
|
static const deviceInfoTable = 'device_info';
|
|
|
|
DatabaseHelper._privateConstructor();
|
|
static final DatabaseHelper instance = DatabaseHelper._privateConstructor();
|
|
|
|
static Database? _database;
|
|
static bool _initialized = false;
|
|
|
|
static void _initializeDatabaseFactory() {
|
|
if (!_initialized) {
|
|
// Initialize for desktop platforms
|
|
if (Platform.isLinux || Platform.isWindows || Platform.isMacOS) {
|
|
sqfliteFfiInit();
|
|
databaseFactory = databaseFactoryFfi;
|
|
}
|
|
_initialized = true;
|
|
}
|
|
}
|
|
|
|
Future<Database> get database async {
|
|
_initializeDatabaseFactory();
|
|
_database ??= await _initDatabase();
|
|
return _database!;
|
|
}
|
|
|
|
Future<Database> _initDatabase() async {
|
|
String path = join(await getDatabasesPath(), _databaseName);
|
|
return await openDatabase(
|
|
path,
|
|
version: _databaseVersion,
|
|
onCreate: _onCreate,
|
|
onUpgrade: _onUpgrade,
|
|
);
|
|
}
|
|
|
|
Future<void> _onCreate(Database db, int version) async {
|
|
await db.execute('''
|
|
CREATE TABLE $supplementsTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
brand TEXT,
|
|
ingredients TEXT NOT NULL DEFAULT '[]',
|
|
numberOfUnits INTEGER NOT NULL DEFAULT 1,
|
|
unitType TEXT NOT NULL DEFAULT 'units',
|
|
frequencyPerDay INTEGER NOT NULL,
|
|
reminderTimes TEXT NOT NULL,
|
|
notes TEXT,
|
|
createdAt TEXT NOT NULL,
|
|
isActive INTEGER NOT NULL DEFAULT 1,
|
|
syncId TEXT NOT NULL UNIQUE,
|
|
lastModified TEXT NOT NULL,
|
|
syncStatus TEXT NOT NULL DEFAULT 'pending',
|
|
isDeleted INTEGER NOT NULL DEFAULT 0
|
|
)
|
|
''');
|
|
|
|
await db.execute('''
|
|
CREATE TABLE $intakesTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
supplementId INTEGER NOT NULL,
|
|
takenAt TEXT NOT NULL,
|
|
dosageTaken REAL NOT NULL,
|
|
unitsTaken REAL NOT NULL DEFAULT 1,
|
|
notes TEXT,
|
|
syncId TEXT NOT NULL UNIQUE,
|
|
lastModified TEXT NOT NULL,
|
|
syncStatus TEXT NOT NULL DEFAULT 'pending',
|
|
isDeleted INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (supplementId) REFERENCES $supplementsTable (id)
|
|
)
|
|
''');
|
|
|
|
await db.execute('''
|
|
CREATE TABLE $notificationTrackingTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
notificationId INTEGER NOT NULL UNIQUE,
|
|
supplementId INTEGER NOT NULL,
|
|
scheduledTime TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
retryCount INTEGER NOT NULL DEFAULT 0,
|
|
lastRetryTime TEXT,
|
|
createdAt TEXT NOT NULL,
|
|
FOREIGN KEY (supplementId) REFERENCES $supplementsTable (id)
|
|
)
|
|
''');
|
|
|
|
// Sync metadata table for tracking sync operations
|
|
await db.execute('''
|
|
CREATE TABLE $syncMetadataTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT NOT NULL UNIQUE,
|
|
value TEXT NOT NULL,
|
|
lastUpdated TEXT NOT NULL
|
|
)
|
|
''');
|
|
|
|
// Device info table for conflict resolution
|
|
await db.execute('''
|
|
CREATE TABLE $deviceInfoTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
deviceId TEXT NOT NULL UNIQUE,
|
|
deviceName TEXT NOT NULL,
|
|
lastSyncTime TEXT,
|
|
createdAt TEXT NOT NULL
|
|
)
|
|
''');
|
|
}
|
|
|
|
Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
|
|
if (oldVersion < 2) {
|
|
// First, add new columns
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN dosageAmount REAL DEFAULT 0');
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN numberOfUnits INTEGER DEFAULT 1');
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN unitType TEXT DEFAULT "units"');
|
|
await db.execute('ALTER TABLE $intakesTable ADD COLUMN unitsTaken REAL DEFAULT 1');
|
|
|
|
// Migrate existing data from old dosage column to new dosageAmount column
|
|
await db.execute('''
|
|
UPDATE $supplementsTable
|
|
SET dosageAmount = COALESCE(dosage, 0),
|
|
numberOfUnits = 1,
|
|
unitType = 'units'
|
|
WHERE dosageAmount = 0
|
|
''');
|
|
|
|
// Create new table with correct schema
|
|
await db.execute('''
|
|
CREATE TABLE ${supplementsTable}_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
brand TEXT,
|
|
dosageAmount REAL NOT NULL,
|
|
numberOfUnits INTEGER NOT NULL DEFAULT 1,
|
|
unit TEXT NOT NULL,
|
|
unitType TEXT NOT NULL DEFAULT 'units',
|
|
frequencyPerDay INTEGER NOT NULL,
|
|
reminderTimes TEXT NOT NULL,
|
|
notes TEXT,
|
|
createdAt TEXT NOT NULL,
|
|
isActive INTEGER NOT NULL DEFAULT 1
|
|
)
|
|
''');
|
|
|
|
// Copy data to new table
|
|
await db.execute('''
|
|
INSERT INTO ${supplementsTable}_new
|
|
(id, name, brand, dosageAmount, numberOfUnits, unit, unitType, frequencyPerDay, reminderTimes, notes, createdAt, isActive)
|
|
SELECT id, name, NULL as brand, dosageAmount, numberOfUnits, unit, unitType, frequencyPerDay, reminderTimes, notes, createdAt, isActive
|
|
FROM $supplementsTable
|
|
''');
|
|
|
|
// Drop old table and rename new table
|
|
await db.execute('DROP TABLE $supplementsTable');
|
|
await db.execute('ALTER TABLE ${supplementsTable}_new RENAME TO $supplementsTable');
|
|
}
|
|
|
|
if (oldVersion < 3) {
|
|
// Add brand column for version 3
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN brand TEXT');
|
|
}
|
|
|
|
if (oldVersion < 4) {
|
|
// Complete migration to new ingredient-based schema
|
|
// Add ingredients column and migrate old data
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN ingredients TEXT DEFAULT "[]"');
|
|
|
|
// Migrate existing supplements to use ingredients format
|
|
final supplements = await db.query(supplementsTable);
|
|
for (final supplement in supplements) {
|
|
final dosageAmount = supplement['dosageAmount'] as double?;
|
|
final unit = supplement['unit'] as String?;
|
|
final name = supplement['name'] as String;
|
|
|
|
if (dosageAmount != null && unit != null && dosageAmount > 0) {
|
|
// Create a single ingredient from the old dosage data
|
|
final ingredient = {
|
|
'name': name,
|
|
'amount': dosageAmount,
|
|
'unit': unit,
|
|
};
|
|
final ingredientsJson = jsonEncode([ingredient]);
|
|
|
|
await db.update(
|
|
supplementsTable,
|
|
{'ingredients': ingredientsJson},
|
|
where: 'id = ?',
|
|
whereArgs: [supplement['id']],
|
|
);
|
|
}
|
|
}
|
|
|
|
// Remove old columns
|
|
await db.execute('''
|
|
CREATE TABLE ${supplementsTable}_new (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
brand TEXT,
|
|
ingredients TEXT NOT NULL DEFAULT '[]',
|
|
numberOfUnits INTEGER NOT NULL DEFAULT 1,
|
|
unitType TEXT NOT NULL DEFAULT 'units',
|
|
frequencyPerDay INTEGER NOT NULL,
|
|
reminderTimes TEXT NOT NULL,
|
|
notes TEXT,
|
|
createdAt TEXT NOT NULL,
|
|
isActive INTEGER NOT NULL DEFAULT 1
|
|
)
|
|
''');
|
|
|
|
await db.execute('''
|
|
INSERT INTO ${supplementsTable}_new
|
|
(id, name, brand, ingredients, numberOfUnits, unitType, frequencyPerDay, reminderTimes, notes, createdAt, isActive)
|
|
SELECT id, name, brand, ingredients, numberOfUnits, unitType, frequencyPerDay, reminderTimes, notes, createdAt, isActive
|
|
FROM $supplementsTable
|
|
''');
|
|
|
|
await db.execute('DROP TABLE $supplementsTable');
|
|
await db.execute('ALTER TABLE ${supplementsTable}_new RENAME TO $supplementsTable');
|
|
}
|
|
|
|
if (oldVersion < 5) {
|
|
// Add notification tracking table
|
|
await db.execute('''
|
|
CREATE TABLE $notificationTrackingTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
notificationId INTEGER NOT NULL UNIQUE,
|
|
supplementId INTEGER NOT NULL,
|
|
scheduledTime TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
retryCount INTEGER NOT NULL DEFAULT 0,
|
|
lastRetryTime TEXT,
|
|
createdAt TEXT NOT NULL,
|
|
FOREIGN KEY (supplementId) REFERENCES $supplementsTable (id)
|
|
)
|
|
''');
|
|
}
|
|
|
|
if (oldVersion < 6) {
|
|
// Add sync columns to existing tables
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN syncId TEXT');
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN lastModified TEXT');
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN syncStatus TEXT DEFAULT "pending"');
|
|
await db.execute('ALTER TABLE $supplementsTable ADD COLUMN isDeleted INTEGER DEFAULT 0');
|
|
|
|
await db.execute('ALTER TABLE $intakesTable ADD COLUMN syncId TEXT');
|
|
await db.execute('ALTER TABLE $intakesTable ADD COLUMN lastModified TEXT');
|
|
await db.execute('ALTER TABLE $intakesTable ADD COLUMN syncStatus TEXT DEFAULT "pending"');
|
|
await db.execute('ALTER TABLE $intakesTable ADD COLUMN isDeleted INTEGER DEFAULT 0');
|
|
|
|
// Generate sync IDs and timestamps for existing records
|
|
final supplements = await db.query(supplementsTable);
|
|
for (final supplement in supplements) {
|
|
if (supplement['syncId'] == null) {
|
|
final now = DateTime.now().toIso8601String();
|
|
await db.update(
|
|
supplementsTable,
|
|
{
|
|
'syncId': 'sync-${supplement['id']}-${DateTime.now().millisecondsSinceEpoch}',
|
|
'lastModified': now,
|
|
'syncStatus': 'pending',
|
|
'isDeleted': 0,
|
|
},
|
|
where: 'id = ?',
|
|
whereArgs: [supplement['id']],
|
|
);
|
|
}
|
|
}
|
|
|
|
final intakes = await db.query(intakesTable);
|
|
for (final intake in intakes) {
|
|
if (intake['syncId'] == null) {
|
|
final now = DateTime.now().toIso8601String();
|
|
await db.update(
|
|
intakesTable,
|
|
{
|
|
'syncId': 'sync-${intake['id']}-${DateTime.now().millisecondsSinceEpoch}',
|
|
'lastModified': now,
|
|
'syncStatus': 'pending',
|
|
'isDeleted': 0,
|
|
},
|
|
where: 'id = ?',
|
|
whereArgs: [intake['id']],
|
|
);
|
|
}
|
|
}
|
|
|
|
// Create sync metadata table
|
|
await db.execute('''
|
|
CREATE TABLE $syncMetadataTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
key TEXT NOT NULL UNIQUE,
|
|
value TEXT NOT NULL,
|
|
lastUpdated TEXT NOT NULL
|
|
)
|
|
''');
|
|
|
|
// Create device info table
|
|
await db.execute('''
|
|
CREATE TABLE $deviceInfoTable (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
deviceId TEXT NOT NULL UNIQUE,
|
|
deviceName TEXT NOT NULL,
|
|
lastSyncTime TEXT,
|
|
createdAt TEXT NOT NULL
|
|
)
|
|
''');
|
|
}
|
|
}
|
|
|
|
// Supplement CRUD operations
|
|
Future<int> insertSupplement(Supplement supplement) async {
|
|
Database db = await database;
|
|
return await db.insert(supplementsTable, supplement.toMap());
|
|
}
|
|
|
|
Future<List<Supplement>> getAllSupplements() async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
supplementsTable,
|
|
where: 'isActive = ? AND isDeleted = ?',
|
|
whereArgs: [1, 0],
|
|
orderBy: 'name ASC',
|
|
);
|
|
return List.generate(maps.length, (i) => Supplement.fromMap(maps[i]));
|
|
}
|
|
|
|
Future<List<Supplement>> getArchivedSupplements() async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
supplementsTable,
|
|
where: 'isActive = ? AND isDeleted = ?',
|
|
whereArgs: [0, 0],
|
|
orderBy: 'name ASC',
|
|
);
|
|
return List.generate(maps.length, (i) => Supplement.fromMap(maps[i]));
|
|
}
|
|
|
|
Future<void> archiveSupplement(int id) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
supplementsTable,
|
|
{'isActive': 0},
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
}
|
|
|
|
Future<void> unarchiveSupplement(int id) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
supplementsTable,
|
|
{'isActive': 1},
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
}
|
|
|
|
Future<Supplement?> getSupplement(int id) async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
supplementsTable,
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
if (maps.isNotEmpty) {
|
|
return Supplement.fromMap(maps.first);
|
|
}
|
|
return null;
|
|
}
|
|
|
|
Future<int> updateSupplement(Supplement supplement) async {
|
|
Database db = await database;
|
|
return await db.update(
|
|
supplementsTable,
|
|
supplement.toMap(),
|
|
where: 'id = ?',
|
|
whereArgs: [supplement.id],
|
|
);
|
|
}
|
|
|
|
Future<int> deleteSupplement(int id) async {
|
|
Database db = await database;
|
|
return await db.update(
|
|
supplementsTable,
|
|
{
|
|
'isActive': 0,
|
|
'isDeleted': 1,
|
|
'lastModified': DateTime.now().toIso8601String(),
|
|
},
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
}
|
|
|
|
Future<int> permanentlyDeleteSupplement(int id) async {
|
|
Database db = await database;
|
|
|
|
// For sync compatibility, we should mark as deleted rather than completely removing
|
|
// This prevents the supplement from reappearing during sync
|
|
|
|
// First mark all related intakes as deleted
|
|
await db.update(
|
|
intakesTable,
|
|
{
|
|
'isDeleted': 1,
|
|
'lastModified': DateTime.now().toIso8601String(),
|
|
'syncStatus': RecordSyncStatus.modified.name,
|
|
},
|
|
where: 'supplementId = ? AND isDeleted = ?',
|
|
whereArgs: [id, 0],
|
|
);
|
|
|
|
// Then mark the supplement as deleted instead of removing it completely
|
|
return await db.update(
|
|
supplementsTable,
|
|
{
|
|
'isDeleted': 1,
|
|
'isActive': 0, // Also ensure it's archived
|
|
'lastModified': DateTime.now().toIso8601String(),
|
|
'syncStatus': RecordSyncStatus.modified.name,
|
|
},
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
}
|
|
|
|
// Supplement Intake CRUD operations
|
|
Future<int> insertIntake(SupplementIntake intake) async {
|
|
Database db = await database;
|
|
return await db.insert(intakesTable, intake.toMap());
|
|
}
|
|
|
|
Future<int> deleteIntake(int id) async {
|
|
Database db = await database;
|
|
return await db.update(
|
|
intakesTable,
|
|
{
|
|
'isDeleted': 1,
|
|
'lastModified': DateTime.now().toIso8601String(),
|
|
},
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
}
|
|
|
|
Future<int> permanentlyDeleteIntake(int id) async {
|
|
Database db = await database;
|
|
|
|
// For sync compatibility, mark as deleted rather than completely removing
|
|
// This prevents the intake from reappearing during sync
|
|
return await db.update(
|
|
intakesTable,
|
|
{
|
|
'isDeleted': 1,
|
|
'lastModified': DateTime.now().toIso8601String(),
|
|
'syncStatus': RecordSyncStatus.modified.name,
|
|
},
|
|
where: 'id = ?',
|
|
whereArgs: [id],
|
|
);
|
|
}
|
|
|
|
Future<List<SupplementIntake>> getIntakesForDate(DateTime date) async {
|
|
Database db = await database;
|
|
String startDate = DateTime(date.year, date.month, date.day).toIso8601String();
|
|
String endDate = DateTime(date.year, date.month, date.day, 23, 59, 59).toIso8601String();
|
|
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
intakesTable,
|
|
where: 'takenAt >= ? AND takenAt <= ? AND isDeleted = ?',
|
|
whereArgs: [startDate, endDate, 0],
|
|
orderBy: 'takenAt DESC',
|
|
);
|
|
return List.generate(maps.length, (i) => SupplementIntake.fromMap(maps[i]));
|
|
}
|
|
|
|
Future<List<SupplementIntake>> getIntakesForMonth(int year, int month) async {
|
|
Database db = await database;
|
|
String startDate = DateTime(year, month, 1).toIso8601String();
|
|
String endDate = DateTime(year, month + 1, 0, 23, 59, 59).toIso8601String();
|
|
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
intakesTable,
|
|
where: 'takenAt >= ? AND takenAt <= ? AND isDeleted = ?',
|
|
whereArgs: [startDate, endDate, 0],
|
|
orderBy: 'takenAt DESC',
|
|
);
|
|
return List.generate(maps.length, (i) => SupplementIntake.fromMap(maps[i]));
|
|
}
|
|
|
|
Future<List<Map<String, dynamic>>> getIntakesWithSupplementsForDate(DateTime date) async {
|
|
Database db = await database;
|
|
String startDate = DateTime(date.year, date.month, date.day).toIso8601String();
|
|
String endDate = DateTime(date.year, date.month, date.day, 23, 59, 59).toIso8601String();
|
|
|
|
List<Map<String, dynamic>> result = await db.rawQuery('''
|
|
SELECT i.*,
|
|
i.supplementId as supplement_id,
|
|
s.name as supplementName,
|
|
s.unitType as supplementUnitType
|
|
FROM $intakesTable i
|
|
JOIN $supplementsTable s ON i.supplementId = s.id
|
|
WHERE i.takenAt >= ? AND i.takenAt <= ? AND i.isDeleted = ?
|
|
ORDER BY i.takenAt DESC
|
|
''', [startDate, endDate, 0]);
|
|
|
|
return result;
|
|
}
|
|
|
|
Future<List<Map<String, dynamic>>> getIntakesWithSupplementsForMonth(int year, int month) async {
|
|
Database db = await database;
|
|
String startDate = DateTime(year, month, 1).toIso8601String();
|
|
String endDate = DateTime(year, month + 1, 0, 23, 59, 59).toIso8601String();
|
|
|
|
List<Map<String, dynamic>> result = await db.rawQuery('''
|
|
SELECT i.*,
|
|
i.supplementId as supplement_id,
|
|
s.name as supplementName,
|
|
s.unitType as supplementUnitType
|
|
FROM $intakesTable i
|
|
JOIN $supplementsTable s ON i.supplementId = s.id
|
|
WHERE i.takenAt >= ? AND i.takenAt <= ? AND i.isDeleted = ?
|
|
ORDER BY i.takenAt DESC
|
|
''', [startDate, endDate, 0]);
|
|
|
|
return result;
|
|
}
|
|
|
|
// Notification tracking methods
|
|
Future<int> trackNotification({
|
|
required int notificationId,
|
|
required int supplementId,
|
|
required DateTime scheduledTime,
|
|
}) async {
|
|
Database db = await database;
|
|
|
|
// Use INSERT OR REPLACE to handle both new and existing notifications
|
|
await db.rawInsert('''
|
|
INSERT OR REPLACE INTO $notificationTrackingTable
|
|
(notificationId, supplementId, scheduledTime, status, retryCount, lastRetryTime, createdAt)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?)
|
|
''', [
|
|
notificationId,
|
|
supplementId,
|
|
scheduledTime.toIso8601String(),
|
|
'pending',
|
|
0,
|
|
null,
|
|
DateTime.now().toIso8601String(),
|
|
]);
|
|
|
|
return notificationId;
|
|
}
|
|
|
|
Future<void> markNotificationTaken(int notificationId) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
notificationTrackingTable,
|
|
{'status': 'taken'},
|
|
where: 'notificationId = ?',
|
|
whereArgs: [notificationId],
|
|
);
|
|
}
|
|
|
|
Future<void> incrementRetryCount(int notificationId) async {
|
|
Database db = await database;
|
|
await db.rawUpdate('''
|
|
UPDATE $notificationTrackingTable
|
|
SET retryCount = retryCount + 1,
|
|
lastRetryTime = ?,
|
|
status = 'retrying'
|
|
WHERE notificationId = ?
|
|
''', [DateTime.now().toIso8601String(), notificationId]);
|
|
}
|
|
|
|
Future<List<Map<String, dynamic>>> getPendingNotifications() async {
|
|
Database db = await database;
|
|
return await db.rawQuery('''
|
|
SELECT nt.*, s.name as supplementName
|
|
FROM $notificationTrackingTable nt
|
|
LEFT JOIN $supplementsTable s ON nt.supplementId = s.id
|
|
WHERE nt.status IN (?, ?)
|
|
ORDER BY nt.scheduledTime ASC
|
|
''', ['pending', 'retrying']);
|
|
}
|
|
|
|
Future<void> markNotificationExpired(int notificationId) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
notificationTrackingTable,
|
|
{'status': 'expired'},
|
|
where: 'notificationId = ?',
|
|
whereArgs: [notificationId],
|
|
);
|
|
}
|
|
|
|
Future<void> cleanupOldNotificationTracking() async {
|
|
Database db = await database;
|
|
// Remove tracking records older than 7 days
|
|
final cutoffDate = DateTime.now().subtract(const Duration(days: 7)).toIso8601String();
|
|
await db.delete(
|
|
notificationTrackingTable,
|
|
where: 'createdAt < ?',
|
|
whereArgs: [cutoffDate],
|
|
);
|
|
}
|
|
|
|
Future<void> clearNotificationTracking(int supplementId) async {
|
|
Database db = await database;
|
|
await db.delete(
|
|
notificationTrackingTable,
|
|
where: 'supplementId = ?',
|
|
whereArgs: [supplementId],
|
|
);
|
|
}
|
|
|
|
// Sync metadata operations
|
|
Future<void> setSyncMetadata(String key, String value) async {
|
|
Database db = await database;
|
|
await db.rawInsert('''
|
|
INSERT OR REPLACE INTO $syncMetadataTable (key, value, lastUpdated)
|
|
VALUES (?, ?, ?)
|
|
''', [key, value, DateTime.now().toIso8601String()]);
|
|
}
|
|
|
|
Future<String?> getSyncMetadata(String key) async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> result = await db.query(
|
|
syncMetadataTable,
|
|
where: 'key = ?',
|
|
whereArgs: [key],
|
|
);
|
|
return result.isNotEmpty ? result.first['value'] : null;
|
|
}
|
|
|
|
Future<void> deleteSyncMetadata(String key) async {
|
|
Database db = await database;
|
|
await db.delete(
|
|
syncMetadataTable,
|
|
where: 'key = ?',
|
|
whereArgs: [key],
|
|
);
|
|
}
|
|
|
|
// Device info operations
|
|
Future<void> setDeviceInfo(String deviceId, String deviceName) async {
|
|
Database db = await database;
|
|
await db.rawInsert('''
|
|
INSERT OR REPLACE INTO $deviceInfoTable (deviceId, deviceName, lastSyncTime, createdAt)
|
|
VALUES (?, ?, ?, ?)
|
|
''', [deviceId, deviceName, null, DateTime.now().toIso8601String()]);
|
|
}
|
|
|
|
Future<void> updateLastSyncTime(String deviceId) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
deviceInfoTable,
|
|
{'lastSyncTime': DateTime.now().toIso8601String()},
|
|
where: 'deviceId = ?',
|
|
whereArgs: [deviceId],
|
|
);
|
|
}
|
|
|
|
Future<Map<String, dynamic>?> getDeviceInfo(String deviceId) async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> result = await db.query(
|
|
deviceInfoTable,
|
|
where: 'deviceId = ?',
|
|
whereArgs: [deviceId],
|
|
);
|
|
return result.isNotEmpty ? result.first : null;
|
|
}
|
|
|
|
// Sync-specific queries
|
|
Future<List<Supplement>> getModifiedSupplements() async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
supplementsTable,
|
|
where: 'syncStatus IN (?, ?)',
|
|
whereArgs: [RecordSyncStatus.pending.name, RecordSyncStatus.modified.name],
|
|
orderBy: 'lastModified ASC',
|
|
);
|
|
return List.generate(maps.length, (i) => Supplement.fromMap(maps[i]));
|
|
}
|
|
|
|
Future<List<SupplementIntake>> getModifiedIntakes() async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
intakesTable,
|
|
where: 'syncStatus IN (?, ?)',
|
|
whereArgs: [RecordSyncStatus.pending.name, RecordSyncStatus.modified.name],
|
|
orderBy: 'lastModified ASC',
|
|
);
|
|
return List.generate(maps.length, (i) => SupplementIntake.fromMap(maps[i]));
|
|
}
|
|
|
|
Future<void> markSupplementAsSynced(String syncId) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
supplementsTable,
|
|
{'syncStatus': RecordSyncStatus.synced.name},
|
|
where: 'syncId = ?',
|
|
whereArgs: [syncId],
|
|
);
|
|
}
|
|
|
|
Future<void> markIntakeAsSynced(String syncId) async {
|
|
Database db = await database;
|
|
await db.update(
|
|
intakesTable,
|
|
{'syncStatus': RecordSyncStatus.synced.name},
|
|
where: 'syncId = ?',
|
|
whereArgs: [syncId],
|
|
);
|
|
}
|
|
|
|
Future<Supplement?> getSupplementBySyncId(String syncId) async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
supplementsTable,
|
|
where: 'syncId = ?',
|
|
whereArgs: [syncId],
|
|
);
|
|
if (maps.isNotEmpty) {
|
|
return Supplement.fromMap(maps.first);
|
|
}
|
|
return null;
|
|
}
|
|
|
|
Future<SupplementIntake?> getIntakeBySyncId(String syncId) async {
|
|
Database db = await database;
|
|
List<Map<String, dynamic>> maps = await db.query(
|
|
intakesTable,
|
|
where: 'syncId = ?',
|
|
whereArgs: [syncId],
|
|
);
|
|
if (maps.isNotEmpty) {
|
|
return SupplementIntake.fromMap(maps.first);
|
|
}
|
|
return null;
|
|
}
|
|
}
|