MO-Fuel-Tax-Back/lib/services/database_service.dart

import 'dart:io';

import 'package:path/path.dart' as p;
import 'package:path_provider/path_provider.dart';
import 'package:sqflite/sqflite.dart';
import 'package:uuid/uuid.dart';

import '../models/fuel_entry.dart';
import '../models/vehicle.dart';
import 'db_schema.dart';

const dbFileName = 'fuel_tax_tracker.db';

/// OCR-scanned VINs are a fixed alphanumeric charset, but manual entry in
/// the vehicle form doesn't enforce that — so anything outside
/// `[A-Za-z0-9_-]` is replaced before a VIN is used as a local directory
/// name or cloud folder name, to keep it a safe single path segment (no
/// `/`, `..`, etc.).
String sanitizedPathSegment(String value) => value.trim().replaceAll(RegExp(r'[^A-Za-z0-9_-]'), '_');

/// `yyyy.mm` for the given (local) date — the subfolder a receipt is filed
/// under within its vehicle's folder, e.g. `2026.08`. Based on the fuel
/// entry's purchase date ([FuelEntry.date]), not when the photo happened to
/// be taken or synced.
String monthFolderName(DateTime date) =>
    '${date.year.toString().padLeft(4, '0')}.${date.month.toString().padLeft(2, '0')}';

/// Name of the *local* on-device staging folder for not-yet-uploaded
/// receipt photos — distinct from (though coincidentally the same string
/// as) `receiptsFolderName` in `cloud/cloud_storage_provider.dart`, which
/// names the corresponding subfolder inside the shared cloud app folder.
const localReceiptsFolderName = 'receipts';

/// Owns the local SQLite database (vehicles + fuel_entries) and the
/// `receipts/` folder of not-yet-uploaded receipt photos, both under
/// `ApplicationDocumentsDirectory/FuelTaxTracker`.
///
/// Every row carries `updated_at` (for newest-wins merging), `deleted_at`
/// (a soft-delete tombstone — see [Vehicle.deletedAt]/[FuelEntry.deletedAt]
/// for why deletes aren't real `DELETE`s), and `dirty` (local-only: "not
/// yet pushed to the cloud"). `dirty` is intentionally not exposed on the
/// domain model classes — it's sync bookkeeping the UI layer never needs to
/// know about; only [CloudSyncService] reads/clears it.
class DatabaseService {
  late Directory _rootDirectory;
  late Database _db;

  Directory get rootDirectory => _rootDirectory;

  Directory get receiptsDirectory =>
      Directory(p.join(_rootDirectory.path, localReceiptsFolderName));

  /// Raw handle for [CloudSyncService], which needs to run ATTACH-based
  /// merge SQL and VACUUM INTO snapshots that go beyond simple CRUD.
  Database get rawDb => _db;

  String get databasePath => _db.path;

  Future<void> init() async {
    final docsDir = await getApplicationDocumentsDirectory();
    _rootDirectory = Directory(p.join(docsDir.path, 'FuelTaxTracker'));
    await _rootDirectory.create(recursive: true);
    await receiptsDirectory.create(recursive: true);

    final dbPath = p.join(_rootDirectory.path, dbFileName);
    _db = await openDatabase(
      dbPath,
      version: 6,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    await db.execute(createVehiclesTableSql);
    await db.execute(createFuelEntriesTableSql);
    await db.execute(createFuelEntriesIndexSql);
    await db.execute(createAdFreeEntitlementTableSql);
    await db.execute(createAppUsageTableSql);
  }

  /// Versions 2/3 (VIN as primary key, then dropping license plate) were
  /// rebuilt fresh on upgrade rather than migrated, since at the time that
  /// only affected local, not-yet-synced test data. Version 4 (VIN becomes
  /// editable, with a new hidden `id` taking over as the actual primary
  /// key/merge key) is the first schema change made after real usage had
  /// likely accumulated, so this one preserves existing rows instead of
  /// dropping them: each vehicle gets a freshly generated `id`, and
  /// `fuel_entries.vehicle_id` is repointed from the old `vehicle_vin` via
  /// that mapping.
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 4) {
      await _migrateToVehicleIdSchema(db);
    }
    if (oldVersion < 5) {
      await db.execute(createAdFreeEntitlementTableSql);
    }
    if (oldVersion < 6) {
      await db.execute(createAppUsageTableSql);
    }
  }

  Future<void> _migrateToVehicleIdSchema(Database db) async {
    const uuid = Uuid();

    final oldVehicles = await db.query('vehicles');
    final vinToId = <String, String>{};

    await db.execute('ALTER TABLE vehicles RENAME TO vehicles_old');
    await db.execute(createVehiclesTableSql);
    for (final row in oldVehicles) {
      final vin = row['vin'] as String;
      // An already-upgraded-then-reverted-then-upgraded-again edge case
      // could in theory produce duplicate vin rows pre-migration; keep the
      // first id assigned per vin so fuel_entries below has a single,
      // unambiguous target.
      final id = vinToId.putIfAbsent(vin, uuid.v4);
      await db.insert('vehicles', {
        'id': id,
        'vin': vin,
        'nickname': row['nickname'],
        'updated_at': row['updated_at'],
        'deleted_at': row['deleted_at'],
        // Force a re-push under the new schema — the shape of what's on
        // the cloud (if anything's been synced yet) still reflects the old
        // schema and needs to be overwritten with this one.
        'dirty': 1,
      });
    }
    await db.execute('DROP TABLE vehicles_old');

    final oldFuelEntries = await db.query('fuel_entries');
    await db.execute('ALTER TABLE fuel_entries RENAME TO fuel_entries_old');
    await db.execute(createFuelEntriesTableSql);
    await db.execute(createFuelEntriesIndexSql);
    for (final row in oldFuelEntries) {
      final vehicleId = vinToId[row['vehicle_vin'] as String];
      // No matching vehicle row (shouldn't normally happen) — drop rather
      // than insert a fuel entry with a dangling reference.
      if (vehicleId == null) continue;
      await db.insert('fuel_entries', {
        'id': row['id'],
        'vehicle_id': vehicleId,
        'date': row['date'],
        'gallons': row['gallons'],
        'price_per_gallon': row['price_per_gallon'],
        'total_cost': row['total_cost'],
        'receipt_image_path': row['receipt_image_path'],
        'receipt_drive_file_id': row['receipt_drive_file_id'],
        'updated_at': row['updated_at'],
        'deleted_at': row['deleted_at'],
        'dirty': 1,
      });
    }
    await db.execute('DROP TABLE fuel_entries_old');
  }

  Future<List<Vehicle>> getVehicles() async {
    final rows = await _db.query('vehicles', where: 'deleted_at IS NULL');
    return rows.map(Vehicle.fromMap).toList();
  }

  Future<List<FuelEntry>> getFuelEntries() async {
    final rows = await _db.query('fuel_entries', where: 'deleted_at IS NULL');
    return rows.map(FuelEntry.fromMap).toList();
  }

  /// The furthest-known "ads disabled until" date — null if no purchase
  /// has ever been recorded (locally or merged in from another synced
  /// device). See [createAdFreeEntitlementTableSql] for why this is its
  /// own small synced table rather than a local-only preference.
  Future<DateTime?> getAdFreeUntil() async {
    final rows = await _db.query('ad_free_entitlement', where: 'id = 1', limit: 1);
    if (rows.isEmpty) return null;
    final millis = rows.single['ad_free_until'] as int?;
    return millis == null ? null : DateTime.fromMillisecondsSinceEpoch(millis, isUtc: true);
  }

  /// Records a purchase's grant (or extension) of ad-free time. [until] is
  /// intentionally not compared against any existing value here — the
  /// caller (a fresh purchase) always means "now plus a year", which is
  /// always further out than whatever was there before.
  Future<void> setAdFreeUntil(DateTime until, DateTime updatedAt) async {
    await _db.insert(
      'ad_free_entitlement',
      {
        'id': 1,
        'ad_free_until': until.toUtc().millisecondsSinceEpoch,
        'updated_at': updatedAt.toUtc().millisecondsSinceEpoch,
      },
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// The earliest-known moment this app was ever used, across every device
  /// this account has synced from — see [createAppUsageTableSql]. Recorded
  /// once, the first time this is ever called with no existing row (a
  /// fresh install with nothing to merge down yet); reinstalling *after*
  /// cloud sync was connected instead pulls the original value back down
  /// via [mergeAppUsageSql] before this is next called, so the new-user ad
  /// grace period can't be replayed by reinstalling.
  Future<DateTime> getOrCreateFirstUsedAt() async {
    final rows = await _db.query('app_usage', where: 'id = 1', limit: 1);
    if (rows.isNotEmpty) {
      return DateTime.fromMillisecondsSinceEpoch(rows.single['first_used_at'] as int, isUtc: true);
    }
    final now = DateTime.now().toUtc();
    await _db.insert('app_usage', {'id': 1, 'first_used_at': now.millisecondsSinceEpoch});
    return now;
  }

  /// True if any vehicle or fuel entry row has local changes not yet
  /// pushed to the cloud (see the `dirty` column note on this class).
  /// Exposed only as this one yes/no signal, never the raw flag — it
  /// drives [AppState.hasUnsyncedChanges], which the "not backed up" icon
  /// reads.
  Future<bool> hasDirtyRows() async {
    final vehicleRows = await _db.query('vehicles', where: 'dirty = 1', limit: 1);
    if (vehicleRows.isNotEmpty) return true;
    final entryRows = await _db.query('fuel_entries', where: 'dirty = 1', limit: 1);
    return entryRows.isNotEmpty;
  }

  /// True if an active (non-deleted) vehicle other than [excludeId] already
  /// has this VIN. VIN must stay unique even though it's editable, so
  /// callers adding a new vehicle (no [excludeId]) or changing an existing
  /// one's VIN (passing its own [id][Vehicle.id] as [excludeId], so it
  /// doesn't collide with itself) should check this first.
  Future<bool> vinExists(String vin, {String? excludeId}) async {
    final where = StringBuffer('vin = ? AND deleted_at IS NULL');
    final whereArgs = <Object?>[vin];
    if (excludeId != null) {
      where.write(' AND id != ?');
      whereArgs.add(excludeId);
    }
    final rows = await _db.query(
      'vehicles',
      where: where.toString(),
      whereArgs: whereArgs,
      limit: 1,
    );
    return rows.isNotEmpty;
  }

  /// Inserts or fully overwrites a vehicle row and marks it dirty (pending
  /// push to Drive).
  Future<void> saveVehicle(Vehicle vehicle) async {
    final map = Map<String, Object?>.from(vehicle.toMap())..['dirty'] = 1;
    await _db.insert('vehicles', map, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<void> saveFuelEntry(FuelEntry entry) async {
    final map = Map<String, Object?>.from(entry.toMap())..['dirty'] = 1;
    await _db.insert('fuel_entries', map, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<void> softDeleteVehicle(String id, DateTime deletedAt) async {
    await _db.update(
      'vehicles',
      {
        'deleted_at': deletedAt.millisecondsSinceEpoch,
        'updated_at': deletedAt.millisecondsSinceEpoch,
        'dirty': 1,
      },
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  Future<void> softDeleteFuelEntry(String id, DateTime deletedAt) async {
    await _db.update(
      'fuel_entries',
      {
        'deleted_at': deletedAt.millisecondsSinceEpoch,
        'updated_at': deletedAt.millisecondsSinceEpoch,
        'dirty': 1,
      },
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// Soft-deletes every active fuel entry for [vehicleId] (cascade for a
  /// vehicle deletion) and returns their local receipt image paths, if
  /// any, so the caller can clean those files up too.
  Future<List<String>> softDeleteFuelEntriesForVehicle(
    String vehicleId,
    DateTime deletedAt,
  ) async {
    final rows = await _db.query(
      'fuel_entries',
      where: 'vehicle_id = ? AND deleted_at IS NULL',
      whereArgs: [vehicleId],
    );
    final localPaths =
        rows.map((r) => r['receipt_image_path'] as String?).whereType<String>().toList();

    await _db.update(
      'fuel_entries',
      {
        'deleted_at': deletedAt.millisecondsSinceEpoch,
        'updated_at': deletedAt.millisecondsSinceEpoch,
        'dirty': 1,
      },
      where: 'vehicle_id = ? AND deleted_at IS NULL',
      whereArgs: [vehicleId],
    );

    return localPaths;
  }

  /// Stores under `receipts/<vin>/<yyyy.mm>/`, mirroring the per-vehicle,
  /// per-month folder structure used on the cloud side (see
  /// [CloudSyncService]'s `_receiptFolderFor`), so a receipt's local
  /// staging path already shows which vehicle and month it belongs to.
  Future<String> storeReceiptImage(
    File sourceImage,
    String fuelEntryId,
    String vin,
    DateTime date,
  ) async {
    final ext = p.extension(sourceImage.path);
    final entryDir = Directory(p.join(
      receiptsDirectory.path,
      sanitizedPathSegment(vin),
      monthFolderName(date),
    ));
    await entryDir.create(recursive: true);
    final destPath = p.join(entryDir.path, '$fuelEntryId$ext');
    final copied = await sourceImage.copy(destPath);
    return copied.path;
  }

  /// Soft-deletes every active vehicle and fuel entry — as if the user had
  /// deleted each one individually (see [softDeleteVehicle]/
  /// [softDeleteFuelEntry] for why these aren't real `DELETE`s: the
  /// tombstones are what let the deletion propagate to other synced devices
  /// on the next sync, rather than the rows just being silently re-imported
  /// from the cloud copy). Also empties the local receipts folder outright
  /// rather than deleting file-by-file, to catch any orphaned file a
  /// tracked path might have missed.
  Future<void> purgeAllData(DateTime deletedAt) async {
    final ts = deletedAt.millisecondsSinceEpoch;
    await _db.update(
      'fuel_entries',
      {'deleted_at': ts, 'updated_at': ts, 'dirty': 1},
      where: 'deleted_at IS NULL',
    );
    await _db.update(
      'vehicles',
      {'deleted_at': ts, 'updated_at': ts, 'dirty': 1},
      where: 'deleted_at IS NULL',
    );

    if (await receiptsDirectory.exists()) {
      await receiptsDirectory.delete(recursive: true);
    }
    await receiptsDirectory.create(recursive: true);
  }

  /// Soft-deletes every active fuel entry (across all vehicles) whose
  /// [FuelEntry.date] falls within [start]–[end] inclusive, and returns
  /// their local receipt image paths so the caller can clean those files up
  /// too. Vehicles are left untouched — a date range only makes sense
  /// against the fuel entries logged against them, not the vehicles
  /// themselves.
  Future<List<String>> purgeFuelEntriesInRange(
    DateTime start,
    DateTime end,
    DateTime deletedAt,
  ) async {
    final rangeStart = DateTime(start.year, start.month, start.day).millisecondsSinceEpoch;
    final rangeEndExclusive =
        DateTime(end.year, end.month, end.day).add(const Duration(days: 1)).millisecondsSinceEpoch;
    final ts = deletedAt.millisecondsSinceEpoch;

    final rows = await _db.query(
      'fuel_entries',
      where: 'deleted_at IS NULL AND date >= ? AND date < ?',
      whereArgs: [rangeStart, rangeEndExclusive],
    );
    final localPaths =
        rows.map((r) => r['receipt_image_path'] as String?).whereType<String>().toList();

    await _db.update(
      'fuel_entries',
      {'deleted_at': ts, 'updated_at': ts, 'dirty': 1},
      where: 'deleted_at IS NULL AND date >= ? AND date < ?',
      whereArgs: [rangeStart, rangeEndExclusive],
    );

    return localPaths;
  }

  Future<void> deleteReceiptImageFile(String? path) async {
    if (path == null) return;
    final file = File(path);
    if (await file.exists()) {
      await file.delete();
    }
  }
}