const createVehiclesTableSql = '''
  CREATE TABLE vehicles (
    id TEXT PRIMARY KEY,
    vin TEXT NOT NULL,
    nickname TEXT,
    updated_at INTEGER NOT NULL,
    deleted_at INTEGER,
    dirty INTEGER NOT NULL DEFAULT 1
  )
''';

const createFuelEntriesTableSql = '''
  CREATE TABLE fuel_entries (
    id TEXT PRIMARY KEY,
    vehicle_id TEXT NOT NULL,
    date INTEGER NOT NULL,
    gallons REAL NOT NULL,
    price_per_gallon REAL NOT NULL,
    total_cost REAL NOT NULL,
    receipt_image_path TEXT,
    receipt_drive_file_id TEXT,
    updated_at INTEGER NOT NULL,
    deleted_at INTEGER,
    dirty INTEGER NOT NULL DEFAULT 1
  )
''';

const createFuelEntriesIndexSql =
    'CREATE INDEX idx_fuel_entries_vehicle_id ON fuel_entries(vehicle_id)';

/// Selects fuel entries whose receipt photo still needs uploading to
/// Drive. Deliberately requires `receipt_drive_file_id IS NULL`, not just
/// "has a local path": once a row is uploaded, its local copy may still be
/// kept around (see the "keep photos on this phone" setting) — matching on
/// the local path alone would re-upload that same photo as a duplicate
/// Drive file on every subsequent sync.
const pendingReceiptUploadWhereClause = 'dirty = 1 AND receipt_image_path IS NOT NULL '
    'AND receipt_drive_file_id IS NULL AND deleted_at IS NULL';

/// Merges attached `remote_db` rows into `main` (the live local database):
/// any remote row that's new to us, or newer than our copy, replaces ours.
/// Rows this doesn't touch — including our own not-yet-pushed edits — are
/// left alone, since the WHERE clause only matches rows remote should win;
/// no separate "keep local" statement is needed.
///
/// Merges on `id` (the hidden, immutable identifier), not `vin` — VIN is
/// user-editable, so it can't be relied on as a stable merge key. VIN
/// uniqueness among active vehicles is enforced at the app layer instead
/// (see `DatabaseService.vinExists`), not by a database constraint here,
/// since two devices could in principle each independently add a vehicle
/// with the same VIN while offline; that's an accepted rare-conflict edge
/// case (see the "field-level conflicts aren't merged" caveat in the
/// README) rather than something this merge statement tries to resolve.
const mergeVehiclesSql = '''
  INSERT OR REPLACE INTO main.vehicles
    (id, vin, nickname, updated_at, deleted_at, dirty)
  SELECT r.id, r.vin, r.nickname, r.updated_at, r.deleted_at, 0
  FROM remote_db.vehicles r
  LEFT JOIN main.vehicles l ON l.id = r.id
  WHERE l.id IS NULL OR r.updated_at > l.updated_at
''';

/// Same rule as [mergeVehiclesSql]. `receipt_image_path` is always forced
/// to NULL on the merged-in copy — it's a local filesystem path, meaningless
/// (and never valid) on another device, and rows are only ever pushed to
/// Drive once their pending receipt has already been uploaded there, so a
/// remote row should never legitimately have one anyway.
const mergeFuelEntriesSql = '''
  INSERT OR REPLACE INTO main.fuel_entries
    (id, vehicle_id, date, gallons, price_per_gallon, total_cost,
     receipt_image_path, receipt_drive_file_id, updated_at, deleted_at, dirty)
  SELECT r.id, r.vehicle_id, r.date, r.gallons, r.price_per_gallon, r.total_cost,
         NULL, r.receipt_drive_file_id, r.updated_at, r.deleted_at, 0
  FROM remote_db.fuel_entries r
  LEFT JOIN main.fuel_entries l ON l.id = r.id
  WHERE l.id IS NULL OR r.updated_at > l.updated_at
''';