Alternative to Vivino: a wine cellar and tasting log with ChatGPT

Set up a wine cellar and tasting log for me in databasedmcp, in one go.

1. Create a database called cellar, described as "Wine cellar: the bottles I own, where they are in the rack, and my tastings".
2. Create three tables:
   - wines: id, producer (required), name (the cuvée, optional), grape, region, vintage (a year).
   - bottles: id, wine_id (references wines), slot (where it is in the rack, like B3), bought_on (date), drink_from and drink_by (years), opened_on (NULL until I open it). One row per bottle.
   - tastings: id, wine_id (references wines), bottle_id (references bottles, empty if it wasn't one of mine), tasted_on (date, defaults to today), rating (1 to 5, optional), notes (in my words), occasion.
3. Write the database a guide that says what each table is for, to look wines up by producer and vintage before adding them so there are no duplicates, that a bottle stays in the rack until opened_on is set, and the queries for "what's in the rack and where", "what should I drink soon" and "which grapes do I rate highest".

From then on: when I buy bottles, add the wine if it's new and one bottles row per bottle with its slot. When I open one, set opened_on and log a tasting with my rating and notes. When I taste something elsewhere, log a tasting with no bottle. Show me the tables when you're finished.

CREATE TABLE wines (
  id INTEGER PRIMARY KEY,
  producer TEXT NOT NULL,
  name TEXT,
  grape TEXT,
  region TEXT,
  vintage INTEGER
);

CREATE TABLE bottles (
  id INTEGER PRIMARY KEY,
  wine_id INTEGER NOT NULL REFERENCES wines(id),
  slot TEXT,
  bought_on TEXT,
  drink_from INTEGER,
  drink_by INTEGER,
  opened_on TEXT
);

CREATE TABLE tastings (
  id INTEGER PRIMARY KEY,
  wine_id INTEGER NOT NULL REFERENCES wines(id),
  bottle_id INTEGER REFERENCES bottles(id),
  tasted_on TEXT NOT NULL DEFAULT (date('now')),
  rating REAL CHECK (rating BETWEEN 1 AND 5),
  notes TEXT,
  occasion TEXT
);

# cellar: wine rack and tasting log

- **wines**: one row per wine and vintage. Look it up by producer and vintage before adding.
- **bottles**: one row per physical bottle. `slot` is its place in the rack. A bottle is in the rack while `opened_on` is NULL.
- **tastings**: what I thought, rated 1 to 5. `bottle_id` is set when it was one of mine.

In the rack: `SELECT b.slot, w.producer, w.name, w.vintage, b.drink_by FROM bottles b JOIN wines w ON w.id = b.wine_id WHERE b.opened_on IS NULL ORDER BY b.slot`

Drink soon (window ends by next year): `SELECT b.slot, w.producer, w.vintage, b.drink_by FROM bottles b JOIN wines w ON w.id = b.wine_id WHERE b.opened_on IS NULL AND b.drink_by <= CAST(strftime('%Y', 'now') AS INTEGER) + 1 ORDER BY b.drink_by`

Best grapes: `SELECT w.grape, count(*) AS tastings, round(avg(t.rating), 1) AS avg_rating FROM tastings t JOIN wines w ON w.id = t.wine_id GROUP BY w.grape ORDER BY avg_rating DESC`

INSERT INTO wines (producer, name, grape, region, vintage) VALUES (?, ?, ?, ?, ?) RETURNING id
-- params: ["Clos Verlaine","Les Pierres","Pinot Noir","Burgundy",2019]

INSERT INTO bottles (wine_id, slot, bought_on, drink_from, drink_by) VALUES (?, ?, date('now'), ?, ?), (?, ?, date('now'), ?, ?), (?, ?, date('now'), ?, ?)
-- params: [1,"B1",2026,2030,1,"B2",2026,2030,1,"B3",2026,2030]

UPDATE bottles SET opened_on = date('now') WHERE slot = ? AND opened_on IS NULL
-- params: ["B2"]

INSERT INTO tastings (wine_id, bottle_id, rating, notes, occasion) VALUES (?, ?, ?, ?, ?)
-- params: [1,2,4,"Cherries and a bit of earth. Still young; the other two can wait.","Sam's birthday dinner"]

SELECT b.slot, w.producer, w.name, w.vintage, b.drink_by FROM bottles b JOIN wines w ON w.id = b.wine_id WHERE b.opened_on IS NULL ORDER BY b.slot

SELECT b.slot, w.producer, w.vintage, b.drink_by FROM bottles b JOIN wines w ON w.id = b.wine_id WHERE b.opened_on IS NULL AND b.drink_by <= CAST(strftime('%Y', 'now') AS INTEGER) + 1 ORDER BY b.drink_by

SELECT w.grape, count(*) AS tastings, round(avg(t.rating), 1) AS avg_rating FROM tastings t JOIN wines w ON w.id = t.wine_id GROUP BY w.grape ORDER BY avg_rating DESC