Alternative to Drivvo: a car maintenance and fuel log with Grok

Set up a car maintenance and fuel log for me in databasedmcp, in one go.

1. Create a database called car, described as "Car log: fill-ups, services and what's due".
2. Create three tables:
   - cars: id, name (required, what I call it), make_model, year.
   - fillups: id, car_id (references cars), filled_on (date, defaults to today), odometer_km (required), litres (required), total_cost, station. I always fill the tank.
   - services: id, car_id (references cars), done_on (date, defaults to today), odometer_km, what (required, like "Oil and filter change"), cost, garage, next_due_km and next_due_on (when it's due again, either or both).
3. Write the database a guide that says what each table is for, that consumption comes from the distance between one full tank and the next, that the latest odometer reading across fillups and services is the car's current mileage, and the queries for "my consumption", "what's due" and "what I've spent this year".

From then on: when I tell you about a fill-up, log it with the odometer, litres and cost. When I have the car serviced, log what was done and when it's next due. Show me the tables when you're finished.

CREATE TABLE cars (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  make_model TEXT,
  year INTEGER
);

CREATE TABLE fillups (
  id INTEGER PRIMARY KEY,
  car_id INTEGER NOT NULL REFERENCES cars(id),
  filled_on TEXT NOT NULL DEFAULT (date('now')),
  odometer_km INTEGER NOT NULL,
  litres REAL NOT NULL,
  total_cost REAL,
  station TEXT
);

CREATE TABLE services (
  id INTEGER PRIMARY KEY,
  car_id INTEGER NOT NULL REFERENCES cars(id),
  done_on TEXT NOT NULL DEFAULT (date('now')),
  odometer_km INTEGER,
  what TEXT NOT NULL,
  cost REAL,
  garage TEXT,
  next_due_km INTEGER,
  next_due_on TEXT
);

# car: fill-ups, services, what's due

- **cars**: one row per car.
- **fillups**: one per fill-up, always a full tank. Consumption for a fill-up is its litres over the distance since the one before.
- **services**: what was done, at what mileage, and when it's due again (`next_due_km`, `next_due_on`, either or both).

The car's current mileage is the highest `odometer_km` in fillups and services.

Consumption: `WITH f AS (SELECT filled_on, odometer_km - lag(odometer_km) OVER (ORDER BY odometer_km) AS km, litres FROM fillups WHERE car_id = ?) SELECT filled_on, km, litres, round(litres * 100.0 / km, 1) AS l_per_100km FROM f WHERE km IS NOT NULL ORDER BY filled_on DESC`

What's due: the latest service of each kind, with `next_due_km` minus the current mileage and the days until `next_due_on`.

Spent this year: sum of `total_cost` in fillups plus `cost` in services since the start of the year.

INSERT INTO cars (name, make_model, year) VALUES (?, ?, ?) RETURNING id
-- params: ["The Golf","Volkswagen Golf",2018]

INSERT INTO fillups (car_id, filled_on, odometer_km, litres, total_cost, station) VALUES (?, date('now', '-12 days'), ?, ?, ?, ?)
-- params: [1,84210,41.3,72.4,"Kestrel, Ring Road"]

INSERT INTO fillups (car_id, odometer_km, litres, total_cost) VALUES (?, ?, ?, ?)
-- params: [1,84850,38.9,68.1]

INSERT INTO services (car_id, odometer_km, what, cost, garage, next_due_km, next_due_on) VALUES (?, ?, ?, ?, ?, ?, date('now', '+1 year'))
-- params: [1,84900,"Oil and filter change",145,"Lindgren Motors",99900]

WITH f AS (SELECT filled_on, odometer_km - lag(odometer_km) OVER (ORDER BY odometer_km) AS km, litres FROM fillups WHERE car_id = ?) SELECT filled_on, km, litres, round(litres * 100.0 / km, 1) AS l_per_100km FROM f WHERE km IS NOT NULL ORDER BY filled_on DESC
-- params: [1]

WITH now_km AS (SELECT max(odometer_km) AS km FROM (SELECT odometer_km FROM fillups WHERE car_id = ? UNION ALL SELECT odometer_km FROM services WHERE car_id = ?)) SELECT s.what, s.next_due_km, s.next_due_km - now_km.km AS km_left, s.next_due_on, round(julianday(s.next_due_on) - julianday('now')) AS days_left FROM services s, now_km WHERE s.car_id = ? AND s.id IN (SELECT max(id) FROM services WHERE car_id = ? GROUP BY what) ORDER BY km_left
-- params: [1,1,1,1]

SELECT 'fuel' AS what, round(sum(total_cost), 2) AS spent FROM fillups WHERE car_id = ? AND filled_on >= date('now', 'start of year') UNION ALL SELECT 'services', round(sum(cost), 2) FROM services WHERE car_id = ? AND done_on >= date('now', 'start of year')
-- params: [1,1]