-- Grow journal (#52): time-stamped notes on gardens, beds and plantings.
--
-- There is already free-text `notes` on gardens, objects and plants, but it is a
-- single mutable field: writing "powdery mildew on the west bed" overwrites what
-- you wrote in June. The distinction worth keeping is that `notes` is WHAT THIS
-- THING IS, while a journal entry is WHAT HAPPENED, AND WHEN. Both stay.
--
-- garden_id is NOT NULL even when the entry is about a bed or a single plop, and
-- that is the whole trick: permission checks reuse requireGardenRole unchanged
-- and no new ACL path is invented. object_id/planting_id narrow the target; the
-- garden always anchors it.
--
-- observed_at is deliberately distinct from created_at — you write up Saturday's
-- observations on Sunday, and the date that matters is Saturday's.
CREATE TABLE journal_entries (
    id          INTEGER PRIMARY KEY,
    garden_id   INTEGER NOT NULL REFERENCES gardens (id)        ON DELETE CASCADE,
    object_id   INTEGER          REFERENCES garden_objects (id) ON DELETE CASCADE,
    planting_id INTEGER          REFERENCES plantings (id)      ON DELETE CASCADE,
    author_id   INTEGER NOT NULL REFERENCES users (id)          ON DELETE CASCADE,
    body        TEXT    NOT NULL,
    observed_at TEXT    NOT NULL,                               -- 'YYYY-MM-DD'
    version     INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
    updated_at  TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%SZ', 'now'))
);

-- The journal's only list query: one garden, most recently observed first.
CREATE INDEX idx_journal_garden ON journal_entries (garden_id, observed_at DESC);

-- Narrowing to one bed or one plop.
CREATE INDEX idx_journal_object ON journal_entries (object_id) WHERE object_id IS NOT NULL;
CREATE INDEX idx_journal_planting ON journal_entries (planting_id) WHERE planting_id IS NOT NULL;