-- Migration 003: grow event log (backs calendar and harvest history)

CREATE TABLE IF NOT EXISTS grow_events (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    plant_id    INTEGER NOT NULL REFERENCES plants(id) ON DELETE CASCADE,
    event_type  TEXT    NOT NULL
                        CHECK (event_type IN (
                            'planted', 'watered', 'fed', 'pruned', 'sprayed',
                            'observation', 'harvest', 'repotted', 'note'
                        )),
    event_date  TEXT    NOT NULL,
    value_num   REAL,
    value_unit  TEXT,
    note        TEXT,
    photo_path  TEXT,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_grow_events_plant_id   ON grow_events (plant_id);
CREATE INDEX IF NOT EXISTS idx_grow_events_event_date ON grow_events (event_date DESC);
CREATE INDEX IF NOT EXISTS idx_grow_events_type       ON grow_events (event_type);