CREATE TABLE IF NOT EXISTS animal_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  animal_id INTEGER NOT NULL,
  action TEXT NOT NULL,          -- created, updated, status_changed, note_added...
  field TEXT,                    -- name, status, etc (optionnel)
  old_value TEXT,
  new_value TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  meta TEXT                      -- JSON léger optionnel (ex: {"route":"..."} )
);

CREATE INDEX IF NOT EXISTS idx_animal_history_animal ON animal_history(animal_id, created_at DESC);