352 lines
11 KiB
Dart
352 lines
11 KiB
Dart
import 'package:sqflite/sqflite.dart';
|
|
import 'package:path/path.dart' as p;
|
|
import '../models/stop.dart';
|
|
import '../models/delivery_history.dart';
|
|
|
|
class DatabaseService {
|
|
static final DatabaseService _instance = DatabaseService._internal();
|
|
factory DatabaseService() => _instance;
|
|
DatabaseService._internal();
|
|
|
|
Database? _database;
|
|
|
|
Future<Database> get database async {
|
|
if (_database != null && _database!.isOpen) return _database!;
|
|
_database = await _initDatabase();
|
|
return _database!;
|
|
}
|
|
|
|
Future<Database> _initDatabase() async {
|
|
final dbPath = await getDatabasesPath();
|
|
return await openDatabase(
|
|
p.join(dbPath, 'delivery.db'),
|
|
version: 3,
|
|
onCreate: (db, version) async {
|
|
await _createTables(db);
|
|
},
|
|
onUpgrade: (db, oldVersion, newVersion) async {
|
|
if (oldVersion < 2) {
|
|
await db.execute('ALTER TABLE stops ADD COLUMN notes TEXT DEFAULT \'\'');
|
|
}
|
|
if (oldVersion < 3) {
|
|
await _createDeliveryHistoryTable(db);
|
|
await _createSettingsTable(db);
|
|
}
|
|
},
|
|
);
|
|
}
|
|
|
|
Future<void> _createTables(Database db) async {
|
|
await db.execute('''
|
|
CREATE TABLE routes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT,
|
|
created_at TEXT
|
|
)
|
|
''');
|
|
await db.execute('''
|
|
CREATE TABLE stops (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
route_id INTEGER,
|
|
street TEXT,
|
|
house_number TEXT,
|
|
newspapers TEXT,
|
|
lat REAL,
|
|
lng REAL,
|
|
delivered INTEGER DEFAULT 0,
|
|
sequence INTEGER DEFAULT 0,
|
|
notes TEXT DEFAULT '',
|
|
FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE
|
|
)
|
|
''');
|
|
await _createDeliveryHistoryTable(db);
|
|
await _createSettingsTable(db);
|
|
}
|
|
|
|
Future<void> _createDeliveryHistoryTable(Database db) async {
|
|
await db.execute('''
|
|
CREATE TABLE IF NOT EXISTS delivery_history (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
route_id INTEGER,
|
|
stop_id INTEGER,
|
|
date TEXT,
|
|
delivered_at TEXT,
|
|
FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (stop_id) REFERENCES stops(id) ON DELETE CASCADE
|
|
)
|
|
''');
|
|
await db.execute(
|
|
'CREATE INDEX IF NOT EXISTS idx_delivery_history_date ON delivery_history(date)',
|
|
);
|
|
await db.execute(
|
|
'CREATE INDEX IF NOT EXISTS idx_delivery_history_route ON delivery_history(route_id)',
|
|
);
|
|
}
|
|
|
|
Future<void> _createSettingsTable(Database db) async {
|
|
await db.execute('''
|
|
CREATE TABLE IF NOT EXISTS settings (
|
|
key TEXT PRIMARY KEY,
|
|
value TEXT
|
|
)
|
|
''');
|
|
}
|
|
|
|
// ── Settings ────────────────────────────────────────────
|
|
|
|
Future<T> getSetting<T>(String key, T defaultValue) async {
|
|
final db = await database;
|
|
final rows = await db.query('settings', where: 'key = ?', whereArgs: [key]);
|
|
if (rows.isEmpty) return defaultValue;
|
|
final raw = rows.first['value'] as String;
|
|
if (T == bool) return (raw == 'true') as T;
|
|
if (T == int) return int.parse(raw) as T;
|
|
if (T == double) return double.parse(raw) as T;
|
|
return raw as T;
|
|
}
|
|
|
|
Future<void> setSetting(String key, dynamic value) async {
|
|
final db = await database;
|
|
await db.insert(
|
|
'settings',
|
|
{'key': key, 'value': value.toString()},
|
|
conflictAlgorithm: ConflictAlgorithm.replace,
|
|
);
|
|
}
|
|
|
|
// ── Routes ──────────────────────────────────────────────
|
|
|
|
Future<List<Map<String, dynamic>>> getRoutes() async {
|
|
final db = await database;
|
|
return db.query('routes', orderBy: 'created_at DESC');
|
|
}
|
|
|
|
Future<int> insertRoute(String name) async {
|
|
final db = await database;
|
|
return db.insert('routes', {
|
|
'name': name,
|
|
'created_at': DateTime.now().toIso8601String(),
|
|
});
|
|
}
|
|
|
|
Future<void> deleteRoute(int routeId) async {
|
|
final db = await database;
|
|
await db.delete('delivery_history', where: 'route_id = ?', whereArgs: [routeId]);
|
|
await db.delete('stops', where: 'route_id = ?', whereArgs: [routeId]);
|
|
await db.delete('routes', where: 'id = ?', whereArgs: [routeId]);
|
|
}
|
|
|
|
// ── Stops ───────────────────────────────────────────────
|
|
|
|
Future<List<Stop>> getStops(int routeId) async {
|
|
final db = await database;
|
|
final maps = await db.query(
|
|
'stops',
|
|
where: 'route_id = ?',
|
|
whereArgs: [routeId],
|
|
orderBy: 'sequence',
|
|
);
|
|
return maps.map(Stop.fromMap).toList();
|
|
}
|
|
|
|
Future<int> insertStop(Stop stop, int routeId) async {
|
|
final db = await database;
|
|
final map = stop.toMap();
|
|
map['route_id'] = routeId;
|
|
return db.insert('stops', map);
|
|
}
|
|
|
|
Future<void> updateStop(Stop stop) async {
|
|
final db = await database;
|
|
await db.update('stops', stop.toMap(), where: 'id = ?', whereArgs: [stop.id]);
|
|
}
|
|
|
|
Future<void> deleteStop(int stopId) async {
|
|
final db = await database;
|
|
await db.delete('delivery_history', where: 'stop_id = ?', whereArgs: [stopId]);
|
|
await db.delete('stops', where: 'id = ?', whereArgs: [stopId]);
|
|
}
|
|
|
|
Future<void> updateStopSequence(int stopId, int sequence) async {
|
|
final db = await database;
|
|
await db.update('stops', {'sequence': sequence}, where: 'id = ?', whereArgs: [stopId]);
|
|
}
|
|
|
|
Future<void> updateStopDelivered(int stopId, bool delivered) async {
|
|
final db = await database;
|
|
await db.update('stops', {'delivered': delivered ? 1 : 0}, where: 'id = ?', whereArgs: [stopId]);
|
|
}
|
|
|
|
Future<void> updateStopNotes(int stopId, String notes) async {
|
|
final db = await database;
|
|
await db.update('stops', {'notes': notes}, where: 'id = ?', whereArgs: [stopId]);
|
|
}
|
|
|
|
Future<void> updateStopCoords(int stopId, double lat, double lng) async {
|
|
final db = await database;
|
|
await db.update('stops', {'lat': lat, 'lng': lng}, where: 'id = ?', whereArgs: [stopId]);
|
|
}
|
|
|
|
Future<int?> getMaxSequence(int routeId) async {
|
|
final db = await database;
|
|
final result = await db.rawQuery(
|
|
'SELECT MAX(sequence) as m FROM stops WHERE route_id = ?',
|
|
[routeId],
|
|
);
|
|
return result.first['m'] as int?;
|
|
}
|
|
|
|
Future<void> reorderStops(List<Stop> stops) async {
|
|
final db = await database;
|
|
final batch = db.batch();
|
|
for (var i = 0; i < stops.length; i++) {
|
|
batch.update('stops', {'sequence': i}, where: 'id = ?', whereArgs: [stops[i].id]);
|
|
}
|
|
await batch.commit(noResult: true);
|
|
}
|
|
|
|
// ── Delivery History ────────────────────────────────────
|
|
|
|
String _today() {
|
|
final now = DateTime.now();
|
|
return '${now.year}-${now.month.toString().padLeft(2, '0')}-${now.day.toString().padLeft(2, '0')}';
|
|
}
|
|
|
|
Future<void> recordDelivery(int routeId, int stopId) async {
|
|
final db = await database;
|
|
// Check if already recorded today
|
|
final existing = await db.query(
|
|
'delivery_history',
|
|
where: 'route_id = ? AND stop_id = ? AND date = ?',
|
|
whereArgs: [routeId, stopId, _today()],
|
|
);
|
|
if (existing.isEmpty) {
|
|
await db.insert('delivery_history', {
|
|
'route_id': routeId,
|
|
'stop_id': stopId,
|
|
'date': _today(),
|
|
'delivered_at': DateTime.now().toIso8601String(),
|
|
});
|
|
}
|
|
}
|
|
|
|
Future<void> removeDeliveryRecord(int routeId, int stopId) async {
|
|
final db = await database;
|
|
await db.delete(
|
|
'delivery_history',
|
|
where: 'route_id = ? AND stop_id = ? AND date = ?',
|
|
whereArgs: [routeId, stopId, _today()],
|
|
);
|
|
}
|
|
|
|
/// Get the set of stop IDs delivered today for a route.
|
|
Future<Set<int>> getTodayDeliveredStopIds(int routeId) async {
|
|
final db = await database;
|
|
final rows = await db.query(
|
|
'delivery_history',
|
|
columns: ['stop_id'],
|
|
where: 'route_id = ? AND date = ?',
|
|
whereArgs: [routeId, _today()],
|
|
);
|
|
return rows.map((r) => r['stop_id'] as int).toSet();
|
|
}
|
|
|
|
/// Reset today's deliveries for a route (mark all stops undelivered).
|
|
Future<void> resetToday(int routeId) async {
|
|
final db = await database;
|
|
await db.delete(
|
|
'delivery_history',
|
|
where: 'route_id = ? AND date = ?',
|
|
whereArgs: [routeId, _today()],
|
|
);
|
|
await db.update('stops', {'delivered': 0}, where: 'route_id = ?', whereArgs: [routeId]);
|
|
}
|
|
|
|
/// Get all unique delivery dates for a route.
|
|
Future<List<String>> getDeliveryDates(int routeId) async {
|
|
final db = await database;
|
|
final rows = await db.rawQuery(
|
|
'SELECT DISTINCT date FROM delivery_history WHERE route_id = ? ORDER BY date DESC',
|
|
[routeId],
|
|
);
|
|
return rows.map((r) => r['date'] as String).toList();
|
|
}
|
|
|
|
/// Get delivery count for a specific date and route.
|
|
Future<int> getDeliveryCountForDate(int routeId, String date) async {
|
|
final db = await database;
|
|
final rows = await db.rawQuery(
|
|
'SELECT COUNT(*) as cnt FROM delivery_history WHERE route_id = ? AND date = ?',
|
|
[routeId, date],
|
|
);
|
|
return rows.first['cnt'] as int;
|
|
}
|
|
|
|
/// Get delivery stats for a date range.
|
|
Future<List<DailyStats>> getDeliveryStats(int routeId, {int days = 30}) async {
|
|
final db = await database;
|
|
final now = DateTime.now();
|
|
final startDate = now.subtract(Duration(days: days));
|
|
final startStr =
|
|
'${startDate.year}-${startDate.month.toString().padLeft(2, '0')}-${startDate.day.toString().padLeft(2, '0')}';
|
|
|
|
// Get total stops for this route
|
|
final totalRows = await db.rawQuery(
|
|
'SELECT COUNT(*) as cnt FROM stops WHERE route_id = ?',
|
|
[routeId],
|
|
);
|
|
final totalStops = totalRows.first['cnt'] as int;
|
|
|
|
final rows = await db.rawQuery(
|
|
'''SELECT date, COUNT(*) as cnt
|
|
FROM delivery_history
|
|
WHERE route_id = ? AND date >= ?
|
|
GROUP BY date
|
|
ORDER BY date DESC''',
|
|
[routeId, startStr],
|
|
);
|
|
|
|
return rows
|
|
.map((r) => DailyStats(
|
|
date: r['date'] as String,
|
|
deliveredCount: r['cnt'] as int,
|
|
totalCount: totalStops,
|
|
))
|
|
.toList();
|
|
}
|
|
|
|
/// Get total deliveries ever for a route.
|
|
Future<int> getTotalDeliveries(int routeId) async {
|
|
final db = await database;
|
|
final rows = await db.rawQuery(
|
|
'SELECT COUNT(*) as cnt FROM delivery_history WHERE route_id = ?',
|
|
[routeId],
|
|
);
|
|
return rows.first['cnt'] as int;
|
|
}
|
|
|
|
/// Get delivery count for current week (Mon-Sun).
|
|
Future<int> getWeeklyDeliveryCount(int routeId) async {
|
|
final db = await database;
|
|
final now = DateTime.now();
|
|
// Find Monday of this week
|
|
final monday = now.subtract(Duration(days: now.weekday - 1));
|
|
final mondayStr =
|
|
'${monday.year}-${monday.month.toString().padLeft(2, '0')}-${monday.day.toString().padLeft(2, '0')}';
|
|
|
|
final rows = await db.rawQuery(
|
|
'SELECT COUNT(*) as cnt FROM delivery_history WHERE route_id = ? AND date >= ?',
|
|
[routeId, mondayStr],
|
|
);
|
|
return rows.first['cnt'] as int;
|
|
}
|
|
|
|
Future<void> close() async {
|
|
final db = _database;
|
|
if (db != null && db.isOpen) {
|
|
await db.close();
|
|
_database = null;
|
|
}
|
|
}
|
|
}
|