Alternative to Untappd: a beer log with Grok

Set up a beer log for me in databasedmcp, in one go.

1. Create a database called beers, described as "Beer log: breweries, beers and every one I've tried".
2. Create three tables:
   - breweries: id, name (required), city, country.
   - beers: id, brewery_id (references breweries), name (required), style (like Hazy IPA or Dry stout), abv (a number, like 6.2).
   - tastings: id, beer_id (references beers), tasted_on (date, defaults to today), rating (0.25 to 5, optional), place (the bar, shop or "home"), serving (draft, can or bottle), notes (a line in my words).
3. Write the database a guide that says what each table is for, to look breweries and beers up by name before adding them so there are no duplicates, and the queries for "which styles do I rate highest", "have I had this one before" and "my best breweries".

From then on: when I tell you what I'm drinking, log a tasting (adding the brewery and beer if they're new) with my rating, where I am and how it was served. When I ask whether I've had something, check the tastings first. Show me the tables when you're finished.

CREATE TABLE breweries (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT,
  country TEXT
);

CREATE TABLE beers (
  id INTEGER PRIMARY KEY,
  brewery_id INTEGER NOT NULL REFERENCES breweries(id),
  name TEXT NOT NULL,
  style TEXT,
  abv REAL
);

CREATE TABLE tastings (
  id INTEGER PRIMARY KEY,
  beer_id INTEGER NOT NULL REFERENCES beers(id),
  tasted_on TEXT NOT NULL DEFAULT (date('now')),
  rating REAL CHECK (rating BETWEEN 0.25 AND 5),
  place TEXT,
  serving TEXT CHECK (serving IN ('draft', 'can', 'bottle')),
  notes TEXT
);

# beers: a beer log

- **breweries**: one row per brewery. Look it up by name (LIKE) before adding.
- **beers**: one row per beer, with its brewery, style and ABV. Look it up by name before adding.
- **tastings**: one row each time I drink one. `rating` is 0.25 to 5. `serving` is draft, can or bottle.

Best styles: `SELECT b.style, count(*) AS tastings, round(avg(t.rating), 2) AS avg_rating FROM tastings t JOIN beers b ON b.id = t.beer_id GROUP BY b.style ORDER BY avg_rating DESC`

Had it before?: `SELECT t.tasted_on, t.place, t.serving, t.rating, t.notes FROM tastings t JOIN beers b ON b.id = t.beer_id WHERE b.name LIKE ? ORDER BY t.tasted_on DESC`

Best breweries (3 or more beers tried): `SELECT br.name, count(DISTINCT b.id) AS beers, round(avg(t.rating), 2) AS avg_rating FROM tastings t JOIN beers b ON b.id = t.beer_id JOIN breweries br ON br.id = b.brewery_id GROUP BY br.id HAVING beers >= 3 ORDER BY avg_rating DESC`

INSERT INTO breweries (name, city, country) VALUES (?, ?, ?) RETURNING id
-- params: ["Harrow Lane Brewing","Leeds","UK"]

INSERT INTO beers (brewery_id, name, style, abv) VALUES (?, ?, ?, ?) RETURNING id
-- params: [1,"Fog Lamp","Hazy IPA",6.2]

INSERT INTO tastings (beer_id, rating, place, serving, notes) VALUES (?, ?, ?, ?, ?)
-- params: [1,4,"The Copper Kettle","draft","Soft, lots of mango, a bit sweet."]

INSERT INTO tastings (beer_id, rating, place, serving, notes) VALUES (?, ?, ?, ?, ?)
-- params: [1,3.5,"home","can","Flatter than on draft."]

SELECT b.style, count(*) AS tastings, round(avg(t.rating), 2) AS avg_rating FROM tastings t JOIN beers b ON b.id = t.beer_id GROUP BY b.style ORDER BY avg_rating DESC

SELECT t.tasted_on, t.place, t.serving, t.rating, t.notes FROM tastings t JOIN beers b ON b.id = t.beer_id WHERE b.name LIKE ? ORDER BY t.tasted_on DESC
-- params: ["%Fog Lamp%"]

SELECT br.name, count(DISTINCT b.id) AS beers, round(avg(t.rating), 2) AS avg_rating FROM tastings t JOIN beers b ON b.id = t.beer_id JOIN breweries br ON br.id = b.brewery_id GROUP BY br.id HAVING beers >= 3 ORDER BY avg_rating DESC