CREATE TABLE IF NOT EXISTS animal_health_conditions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  animal_id INTEGER NOT NULL,
  name TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'resolved')),
  diagnosed_at TEXT,
  resolved_at TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  updated_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY(animal_id) REFERENCES animals(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_health_conditions_animal_status
ON animal_health_conditions(animal_id, status);