Alternative to Letterboxd: a film diary with Grok

Set up a film diary for me in databasedmcp, in one go.

1. Create a database called films, described as "Film diary: what I've watched, my ratings and my watchlist".
2. Create three tables:
   - films: id, title (required), year, director, added_at (defaults to now). One row per film, however many times I watch it.
   - viewings: id, film_id (references films), watched_on (date, defaults to today), rating (0.5 to 5 in half stars, optional), rewatch (1 if I'd seen it before, else 0), where_watched (cinema, home, plane), notes (a line or two in my words).
   - watchlist: id, film_id (references films, one row per film at most), added_on (date, defaults to today), suggested_by, note.
3. Write the database a guide that says what each table is for, to look films up by title and year before adding them so there are no duplicates, to set rewatch from whether an earlier viewing exists, to take a film off the watchlist once I've watched it, and the queries for "what did I watch this month", "what do I rewatch most" and "what's oldest on my watchlist".

From then on: when I say I watched something, log a viewing (adding the film if it's new) with my rating and anything I said about it. When I want to see something later, add it to the watchlist with who suggested it. Show me the tables when you're finished.

CREATE TABLE films (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  year INTEGER,
  director TEXT,
  added_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE viewings (
  id INTEGER PRIMARY KEY,
  film_id INTEGER NOT NULL REFERENCES films(id),
  watched_on TEXT NOT NULL DEFAULT (date('now')),
  rating REAL CHECK (rating BETWEEN 0.5 AND 5),
  rewatch INTEGER NOT NULL DEFAULT 0 CHECK (rewatch IN (0, 1)),
  where_watched TEXT,
  notes TEXT
);

CREATE TABLE watchlist (
  id INTEGER PRIMARY KEY,
  film_id INTEGER NOT NULL UNIQUE REFERENCES films(id),
  added_on TEXT NOT NULL DEFAULT (date('now')),
  suggested_by TEXT,
  note TEXT
);

# films: a film diary

- **films**: one row per film. Look it up by title (LIKE) and year before adding, to avoid duplicates.
- **viewings**: one row each time I watch a film. `rating` is 0.5 to 5 in half stars. Set `rewatch` to 1 when an earlier viewing exists.
- **watchlist**: films I want to see, with who suggested them. Delete the row once I've watched the film.

This month: `SELECT v.watched_on, f.title, v.rating, v.rewatch FROM viewings v JOIN films f ON f.id = v.film_id WHERE v.watched_on >= date('now', 'start of month') ORDER BY v.watched_on`

Rewatched most: `SELECT f.title, count(*) AS times, round(avg(v.rating), 1) AS avg_rating FROM viewings v JOIN films f ON f.id = v.film_id GROUP BY f.id HAVING times > 1 ORDER BY times DESC`

Oldest on the watchlist: `SELECT f.title, f.year, w.suggested_by, w.added_on FROM watchlist w JOIN films f ON f.id = w.film_id ORDER BY w.added_on LIMIT 5`

INSERT INTO films (title, year) VALUES (?, ?) RETURNING id
-- params: ["Paris, Texas",1984]

INSERT INTO watchlist (film_id, suggested_by) VALUES (?, ?)
-- params: [1,"Noor"]

INSERT INTO viewings (film_id, rating, where_watched, notes) VALUES (?, ?, ?, ?)
-- params: [1,4.5,"Rialto cinema","The last scene through the glass. Slow in the best way."]

DELETE FROM watchlist WHERE film_id = ?
-- params: [1]

INSERT INTO viewings (film_id, rating, where_watched, rewatch) SELECT ?, ?, ?, EXISTS (SELECT 1 FROM viewings WHERE film_id = ?)
-- params: [1,4.5,"Home, with Sam",1]

SELECT v.watched_on, f.title, v.rating, v.rewatch FROM viewings v JOIN films f ON f.id = v.film_id WHERE v.watched_on >= date('now', 'start of month') ORDER BY v.watched_on

SELECT f.title, count(*) AS times, round(avg(v.rating), 1) AS avg_rating FROM viewings v JOIN films f ON f.id = v.film_id GROUP BY f.id HAVING times > 1 ORDER BY times DESC

SELECT f.title, f.year, w.suggested_by, w.added_on FROM watchlist w JOIN films f ON f.id = w.film_id ORDER BY w.added_on LIMIT 5