CREATE TABLE IF NOT EXISTS fuel_full_tank_readings (
  id TEXT PRIMARY KEY,
  asset_id TEXT NOT NULL,
  fill_date TEXT NOT NULL,
  entry_type TEXT NOT NULL DEFAULT 'FULL',
  odometer_km INTEGER NOT NULL,
  fuel_milliliters INTEGER NOT NULL,
  total_cost_cents INTEGER NOT NULL DEFAULT 0,
  unit_price_millimes INTEGER NOT NULL DEFAULT 0,
  previous_reading_id TEXT NOT NULL DEFAULT '',
  distance_km INTEGER NOT NULL DEFAULT 0,
  consumption_milliliters_per_100km INTEGER NOT NULL DEFAULT 0,
  anomaly_code TEXT NOT NULL DEFAULT '',
  anomaly_message TEXT NOT NULL DEFAULT '',
  note TEXT NOT NULL DEFAULT '',
  created_by TEXT NOT NULL,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (asset_id) REFERENCES fleet_assets(id) ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_full_tank_asset_date
  ON fuel_full_tank_readings(asset_id, fill_date);
CREATE UNIQUE INDEX IF NOT EXISTS idx_full_tank_asset_odometer
  ON fuel_full_tank_readings(asset_id, odometer_km);
CREATE INDEX IF NOT EXISTS idx_full_tank_asset_chronology
  ON fuel_full_tank_readings(asset_id, fill_date, odometer_km);