PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS litters (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  mother_id INTEGER,
  father_id INTEGER,
  birth_date TEXT,
  notes TEXT,
  created_at TEXT NOT NULL DEFAULT (datetime('now')),
  FOREIGN KEY(mother_id) REFERENCES animals(id) ON DELETE SET NULL,
  FOREIGN KEY(father_id) REFERENCES animals(id) ON DELETE SET NULL,
  CHECK (mother_id IS NULL OR father_id IS NULL OR mother_id != father_id)
);

CREATE TABLE IF NOT EXISTS litter_kittens (
  litter_id INTEGER NOT NULL,
  animal_id INTEGER NOT NULL UNIQUE,
  position INTEGER NOT NULL DEFAULT 1,
  PRIMARY KEY(litter_id, animal_id),
  FOREIGN KEY(litter_id) REFERENCES litters(id) ON DELETE CASCADE,
  FOREIGN KEY(animal_id) REFERENCES animals(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_litters_mother ON litters(mother_id);
CREATE INDEX IF NOT EXISTS idx_litters_father ON litters(father_id);
CREATE INDEX IF NOT EXISTS idx_litter_kittens_litter ON litter_kittens(litter_id, position);